Oracle Applications -- Uday. S

Wednesday, June 25, 2014

Value set(DFF/KFF) Upload with Qualifier and child level data

DECLARE
l_enabled_flag VARCHAR2 (2);
l_summary_flag VARCHAR2 (2);
l_who_type fnd_flex_loader_apis.who_type;
l_user_id NUMBER := fnd_global.user_id;
l_login_id NUMBER := fnd_global.login_id;
l_value_set_name fnd_flex_value_sets.flex_value_set_name%TYPE;
l_value_set_value fnd_flex_values.flex_value%TYPE;
l_flex_value_meaning VARCHAR2 (250);
l_description VARCHAR2 (250);
l_child_low VARCHAR2 (240);
l_child_high VARCHAR2 (240);
l_posting_allowed VARCHAR2 (1);
l_budget_allowed VARCHAR2 (1);
BEGIN
l_value_set_name := 'XXXXXXX';
l_value_set_value := '111';
l_flex_value_meaning := '111';
l_description := 'Test1';
l_child_low := '123';
l_child_high := '124';
l_enabled_flag := 'Y';
l_summary_flag := 'Y'; -- enables parent
l_budget_allowed := 'Y'; --QUALIFIER 1
l_posting_allowed := 'Y'; --QUALIFIER 2
l_who_type.created_by := l_user_id;
l_who_type.creation_date := SYSDATE;
l_who_type.last_updated_by := l_user_id;
l_who_type.last_update_date := SYSDATE;
l_who_type.last_update_login := l_login_id;

fnd_flex_loader_apis.
up_value_set_value (
p_upload_phase => 'BEGIN',
p_upload_mode => NULL,
p_custom_mode => 'FORCE',
p_flex_value_set_name => l_value_set_name,
p_parent_flex_value_low => NULL,
p_flex_value => l_value_set_value,
p_owner => NULL,
p_last_update_date => TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
p_enabled_flag => l_enabled_flag,
p_summary_flag => l_summary_flag,
p_start_date_active => TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
p_end_date_active => NULL,
p_parent_flex_value_high => NULL,
p_rollup_flex_value_set_name => NULL,
p_rollup_hierarchy_code => NULL,
p_hierarchy_level => NULL,
p_compiled_value_attributes => 'NN',
p_value_category => NULL,
p_attribute1 => NULL,
p_attribute2 => NULL,
p_attribute3 => NULL,
p_attribute4 => NULL,
p_attribute5 => NULL,
p_attribute6 => NULL,
p_attribute7 => NULL,
p_attribute8 => NULL,
p_attribute9 => NULL,
p_attribute10 => NULL,
p_attribute11 => NULL,
p_attribute12 => NULL,
p_attribute13 => NULL,
p_attribute14 => NULL,
p_attribute15 => NULL,
p_attribute16 => NULL,
p_attribute17 => NULL,
p_attribute18 => NULL,
p_attribute19 => NULL,
p_attribute20 => NULL,
p_attribute21 => NULL,
p_attribute22 => NULL,
p_attribute23 => NULL,
p_attribute24 => NULL,
p_attribute25 => NULL,
p_attribute26 => NULL,
p_attribute27 => NULL,
p_attribute28 => NULL,
p_attribute29 => NULL,
p_attribute30 => NULL,
p_attribute31 => NULL,
p_attribute32 => NULL,
p_attribute33 => NULL,
p_attribute34 => NULL,
p_attribute35 => NULL,
p_attribute36 => NULL,
p_attribute37 => NULL,
p_attribute38 => NULL,
p_attribute39 => NULL,
p_attribute40 => NULL,
p_attribute41 => NULL,
p_attribute42 => NULL,
p_attribute43 => NULL,
p_attribute44 => NULL,
p_attribute45 => NULL,
p_attribute46 => NULL,
p_attribute47 => NULL,
p_attribute48 => NULL,
p_attribute49 => NULL,
p_attribute50 => NULL,
p_flex_value_meaning => l_flex_value_meaning,
p_description => l_description);

fnd_flex_loader_apis.
up_val_qual_value (
p_upload_phase => 'BEGIN',
p_upload_mode => NULL,
p_custom_mode => 'FORCE',
p_flex_value_set_name => l_value_set_name,
p_parent_flex_value_low => NULL, --
p_flex_value => l_value_set_value,
p_id_flex_application_short_na => 'SQLGL',
p_id_flex_code => 'GL#',
p_segment_attribute_type => 'GL_GLOBAL',
p_value_attribute_type => 'DETAIL_BUDGETING_ALLOWED',
p_owner => NULL,
p_last_update_date => TO_CHAR (SYSDATE,
'YYYY/MM/DD HH24:MI:SS'),
p_assignment_order => NULL,
p_assignment_date => NULL,
p_compiled_value_attribute_val => l_budget_allowed);

fnd_flex_loader_apis.
up_val_qual_value (
p_upload_phase => 'BEGIN',
p_upload_mode => NULL,
p_custom_mode => 'FORCE',
p_flex_value_set_name => l_value_set_name,
p_parent_flex_value_low => NULL,
p_flex_value => l_value_set_value,
p_id_flex_application_short_na => 'SQLGL',
p_id_flex_code => 'GL#',
p_segment_attribute_type => 'GL_GLOBAL',
p_value_attribute_type => 'DETAIL_POSTING_ALLOWED',
p_owner => NULL,
p_last_update_date => TO_CHAR (SYSDATE,
'YYYY/MM/DD HH24:MI:SS'),
p_assignment_order => NULL,
p_assignment_date => NULL,
p_compiled_value_attribute_val => l_posting_allowed);

IF l_summary_flag = 'Y'
THEN
fnd_flex_loader_apis.
up_val_norm_hierarchy (
p_upload_phase => 'BEGIN',
p_upload_mode => NULL,
p_custom_mode => 'FORCE',
p_flex_value_set_name => l_value_set_name,
p_parent_flex_value => l_value_set_value,
p_range_attribute => 'C',
p_child_flex_value_low => l_child_low,
p_child_flex_value_high => l_child_high,
p_owner => NULL,
p_last_update_date => TO_CHAR (SYSDATE,
'YYYY/MM/DD HH24:MI:SS'),
p_start_date_active => TO_CHAR (SYSDATE,
'YYYY/MM/DD HH24:MI:SS'),
p_end_date_active => NULL);
END IF;

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Error is ' || SUBSTR (SQLERRM, 1, 1000));
END;

Thursday, September 19, 2013

Registering a custom table in Oracle Apps.

CREATE TABLE emp_details

(

eno NUMBER (6) CONSTRAINT eno_pk PRIMARY KEY,

ename VARCHAR2 (15) NOT NULL,

dept VARCHAR2 (15),

jdate DATE,

salary NUMBER,

created_by NUMBER (15),

creation_date DATE,

last_updated_by NUMBER (15),

last_update_date DATE,

Last_update_login VARCHAR2 (50),

attribute1 VARCHAR2 (50),

attribute2 VARCHAR2 (50),

attribute3 VARCHAR2 (50),

attribute4 VARCHAR2 (50),

attribute5 VARCHAR2 (50),

attribute_category VARCHAR2 (100)

);

Grant all on emp_details to Apps;

COMMIT;

EXECUTE AD_DD.Register_Table('SQLGL','emp_details','T',8,10,90);

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','empno',1,'Number',6,'N','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','ename',2,'Varchar2',15,'N','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','dept',3,'Varchar2',15,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','jdate',4,'Date',11,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','salary',5,'Number',38,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','created_by',6,'Number',15,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','creation_date',7,'Date',11,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','last_updated_by',8,'Number',15,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','last_update_date',9,'Date',11,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute1',10,'Varchar2',50,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute2',11,'Varchar2',50,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute3',12,'Varchar2',50,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute4',13,'Varchar2',50,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute5',14,'Varchar2',50,'Y','Y');

EXECUTE AD_DD.Register_Column('SQLGL','emp_details','attribute_category',15,'Varchar2',100,'Y','Y');

COMMIT;

SELECT * FROM emp_details;

for checking in front end

go to appication developer >> applications >> database >> TABLE

Monday, July 16, 2012

Oracle Purchasing Tables & API

Usual Oracle Applications PO Tables
PO_HEADERS_ALL: Purchase Order information with Supplier, Site and status
PO_LINES_ALL: PO Lines with Item Information and quantity
PO_LINE_LOCATIONS_ALL: PO Information on Shipments Destination
PO_DISTRIBUTIONS_ALL: PO Distribution where the accounts are
PO_LINE_LOCATIONS_ARCHIVE_ALL: table updated for history on Shipment/Destination Location Information
PO_LINE_TYPES_B: PO Types used in the PO_LINES_ALL
PO_LINES_TYPES_TL: PO Line Types used in MLS
PO_RELEASES_ALL: Stores revision number for the PO
PO_HEADERS_ARCHIVE_ALL: table updated for the history on the status and PO Header changes, Lines, Location and PO Accounts Distribution
PO_LINES_ARCHIVE_ALL: table updated for the history on the lines
PO_DISTRIBUTIONS_ARCHIVE_ALL: table updated for the history on the account distribution
PO_AGENTS_ALL_V: Buyer
PO_VENDORS: Supplier Table
PO_VENDOR_SITES: Supplier Sites
PO_VENDOR_CONTACTS: Supplier Contacts
PO_HAZARD_CLASSES: contains code and description for hazardous items which gets automatically printed into purchase order, RFQ and Receipt Travelers
PO_REQUISITION_LINES_ALL: Requisition Lines
PO_REQUISITION_HEADERS_ALL: Requisition Headers
PO_REQ_DISTRIBUTIONS_ALL: Distribution Lines of Requisition where accounts are
PO_APPROVED_SUPPLIER_LIST: Supplier List for Auto-Sourcing
PO_ASL_DOCUMENTS: Advanced Shipment Documents
PO_APPROVAL_LIST_HEADERS: PO Approval Path
PO_APPROVAL_LIST_LINES: PO Approval Path

OTHER RELATED:
MRP_SOURCING_RULES: Used for Auto-Sourcing Rules
MRP_SR_RECEIPT_ORG: Used for Auto-Sourcing Rules
MRP_SR_SOURCE_ORG: Used for Auto-Sourcing Rules
MRP_ASSIGNMENT_SETS: Used for Auto-Sourcing Rules
MRP_SR_ASSIGNMENTS: Used for Auto-Sourcing Rules
RCV_SHIPMENT_HEADERS: Shipment Table Header with grouping information
RCV_SHIPMENT_LINES: Shipment Table lines with item information
RCV_TRANSACTIONS: PO Lines or Requisition received in destination or transit
RCV_ACCOUNTING_EVENTS: Receiving information on accounts
RCV_RECEIVING_SUB_LEDGER: Accounting entries generated for the receiving transactions
RCV_SUB_LEDGER_DETAILS: Detail accounting entries generated for the receiving transactions
RCV_LOT_SUPPLY: Parent for RCV_LOT_TRANSACTIONS
RCV_LOT_TRANSACTIONS: Table housing the information what lot the item is received

INTERFACES:
PO_HEADERS_INTERFACE: Used for creating PO
PO_LINES_INTERFACE: Used for creating PO
PO_DISTRIBUTIONS_INTERFACE: Used for creating PO
PO_REQUISITIONS_INTERFACE: Used for creating Requisition
PO_REQ_DIST_INTERFACE: Used for creating Requisition
PO_INTERFACE_ERRORS: Error created during the processing of the PO or requisition
RCV_INTERFACE: Used for creating Received Items
RCV_HEADERS_INTERFACE: Used for creating Received Group of items
RCV_LOT_INTERFACE: Used for receiving item into a particular lot

API:
Following API can be used to modify
a) Need by Date
b) Promise Date
c) Quantity
d) Price

po_change_api1_s.update_po
(x_po_number => c1_rec.po_number
,x_release_number => NULL
,x_revision_number => v_REVISION_NUM
,x_line_number => c1_rec.line_num
,x_shipment_number => NULL
,new_quantity => NULL
,new_price => c1_rec.new_price
,new_promised_date => NULL
,new_need_by_date => NULL
,launch_approvals_flag => l_chr_lch_appr_flg
,update_source => NULL
,VERSION => '1.0'
,x_override_date => NULL
,x_api_errors => l_rec_out_error
,p_buyer_name => c1_rec.buyer
,p_secondary_quantity => NULL
,p_preferred_grade => NULL
,p_org_id => c1_rec.org_id
);

2) Following is to call the PO workflow.

po_reqapproval_init1.
start_wf_process (itemtype => 'POAPPRV',
itemkey => l_num_item_key,
workflowprocess => '<<<<>>>>>>>>>',
actionoriginatedfrom => 'PO_FORM',
documentid => i.po_header_id,
documentnumber => i.segment1,
preparerid => i.agent_id,
documenttypecode => 'PO',
documentsubtype => 'STANDARD',
submitteraction => 'APPROVE',
forwardtoid => NULL,
forwardfromid => NULL,
defaultapprovalpathid => NULL,
note => NULL,
printflag => 'N',
faxflag => 'N',
faxnumber => NULL,
emailflag => 'N',
emailaddress => NULL,
createsourcingrule => 'N',
releasegenmethod => 'N',
updatesourcingrule => 'N',
massupdatereleases => 'N',
retroactivepricechange => 'N',
orgassignchange => 'N',
communicatepricechange => 'N',
p_background_flag => 'N',
p_initiator => NULL,
p_xml_flag => NULL,
fpdsngflag => 'N',
p_source_type_code => NULL);

Friday, November 11, 2011

Debugging the Pick - Pack -Shipping / OM related debugging

A To generate a debug file from the Shipping Transaction or Quick Ship forms in version 11.5.9 (Family pack I) or higher:
Set the following profile options:
OM: Debug Level - set to 5
WSH: Debug Enabled - set to Yes
WSH: Debug Level - set to Statement
WSH: Debug Log Directory - any directory that can be written to by the database

To check, run the following SQL statement:
Code:
select value from v$parameter where name = 'utl_file_dir'
Set profile option WSH: Debug Log Directory at the Site & Application Level.
In the Shipping form go to Tools and check the Debug box. This will print out a file name - NOTE down this file name.
Perform the action you wish to debug.
Go to Tools and uncheck Debug.
After you have completed generating the debug file, please set the value of the profile OM: Debug Level back to 0 and WSH: Debug Enabled set to No, otherwise there will be some performance impact. Retrieve the debug file from the directory specified in step 1.

B. To generate debug information for Pick Release in version 11.5.9 (Family pack I) or higher:
Set the following profile options:
OM: Debug Level - set to 5
INV: Debug Level - set to 10
WSH: Debug Enabled - set to Yes
WSH: Debug Level - set to Statement
In the Release Sales Order for Picking form go to Tools and check the Debug box.
Submit the pick release.
Provide the Pick Selection List Generation log file.

C. To generate debug information for Pick Release prior to version 11.5.9 (Family pack I):
Set the following profile options:
OM: Debug Level - set to 5
INV: Debug Level - set to 10
In the Release Sales Order for Picking form go to Tools and check the Debug box.
Submit the pick release.
Provide the Pick Selection List Generation log file.

D. To generate debug information for Interface Trip Stop - SRS in version 11.5.9 (Family pack I) or higher:
Set the following profile options:
OM: Debug Level - set to 5
INV: Debug Level - set to 10
WSH: Debug Enabled - set to Yes
WSH: Debug Level - set to Statement
Set the Debug Level parameter to 1 (Debugging ON) .
Submit the job.
Provide the log file.

E. To generate debug information for Interface Trip Stop - SRS prior to version 11.5.9 (Family pack I):
Set the following profile options:
OM: Debug Level - set to 5
INV: Debug Level - set to 10
Set the Log Level parameter to 1 (Debugging ON) .
Submit the job.
Provide the log file.

F. To generate debug information for an API in 11.5.9 (Family pack I) or higher:
Set the following profile options:
OM: Debug Level - set to 5
INV: Debug Level - set to 10
WSH: Debug Enabled - set to Yes
WSH: Debug Level - set to Statement
WSH: Debug Log Directory - set to a valid writeable directory path
Add the following line of code in the wrapper script which calls the API:
Code:
DECLARE l_file_name VARCHAR2(32767);
l_return_status VARCHAR2(32767);
l_msg_data VARCHAR2(32767);
l_msg_count NUMBER;
BEGIN
fnd_profile.put('WSH_DEBUG_MODULE','%');
fnd_profile.put('WSH_DEBUG_LEVEL',WSH_DEBUG_SV.C_STMT_LEVEL);
wsh_debug_sv.start_debugger(l_file_name,l_return_status,l_msg_data,l_msg_count);
Submit the job.
Provide the log file.

G. To generate debug information for an API prior to version 11.5.9 (Family pack I):
Set the following profile options:
OM: Debug Level - set to 5
INV: Debug Level - set to 10
Add the following lines of code in the wrapper script which calls the API:
Code:
oe_debug_pub.initialize; oe_debug_pub.SetDebugLevel(5);
DBMS_OUTPUT.PUT_LINE('Debug File = ' ||OE_DEBUG_PUB.G_DIR||'/'||OE_DEBUG_PUB.G_FILE);

Friday, July 8, 2011

On Hand Qty Query

SELECT SUM (target_qty), item_id, subinv
FROM (SELECT moqv.subinventory_code subinv
,moqv.inventory_item_id item_id
,SUM (transaction_quantity) target_qty
FROM mtl_onhand_qty_cost_v moqv
WHERE moqv.organization_id = :org_id
AND moqv.inventory_item_id = :item_id
AND moqv.subinventory_code =
NVL (:subinventory_code, moqv.subinventory_code)
GROUP BY moqv.subinventory_code
,moqv.inventory_item_id
,moqv.item_cost
UNION ALL
SELECT mmt.subinventory_code subinv
,mmt.inventory_item_id item_id
,-SUM (primary_quantity) target_qty
FROM mtl_material_transactions mmt, mtl_txn_source_types mtst
WHERE mmt.organization_id = :org_id
AND transaction_date >=
NVL (TO_DATE (:hist_date), TRUNC (SYSDATE))
+ 1
AND mmt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mmt.inventory_item_id = :item_id
AND mmt.subinventory_code =
NVL (:subinventory_code, mmt.subinventory_code)
GROUP BY mmt.subinventory_code, mmt.inventory_item_id) oq
GROUP BY oq.item_id,subinv

Wednesday, November 24, 2010

FINALLY CLOSE -- A PO /PO Line

Following API Can be used to "FINALLY CLOSE" a Standard/Blanket PO at header/line level.

Given example is at Line level for blanket PO.
For header level PO close, P_lineid will be passed as null.
For standard PO , instead of PA use PO.
If blanket PO line is referred on any OPEN standard PO, then BPA line cannot be changed to FINALLY CLOSE status.

**************************************************

DECLARE
x_action CONSTANT VARCHAR2 (20) := 'FINALLY CLOSE';
-- Change this parameter as per requirement
x_calling_mode CONSTANT VARCHAR2 (2) := 'PO';
x_conc_flag CONSTANT VARCHAR2 (1) := 'N';
x_return_code_h VARCHAR2 (100);
x_auto_close CONSTANT VARCHAR2 (1) := 'N';
x_origin_doc_id NUMBER;
x_returned BOOLEAN;

CURSOR c_po_details
IS
SELECT pha.po_header_id, pha.org_id, pha.segment1, pha.agent_id
,pha.closed_code, pha.closed_date
FROM apps.po_headers_all pha
WHERE authorization_status = 'APPROVED'
AND pha.closed_code <> 'FINALLY CLOSED'
AND segment1 = '15000002252';
-- Enter the PO Number if one PO needs to be finally closed/Closed
BEGIN
fnd_global.apps_initialize (user_id => 1468
,resp_id => 20707
,resp_appl_id => 201
);

FOR po_head IN c_po_details
LOOP
mo_global.init ('PA');
mo_global.set_policy_context ('S', po_head.org_id);
DBMS_OUTPUT.put_line
( 'Calling PO_Actions.close_po for Closing/Finally Closing PO =>'
|| po_head.segment1
);
x_returned :=
po_actions.close_po (p_docid => po_head.po_header_id
,p_doctyp => 'PA' -- "PO" for standard PO
,p_docsubtyp => 'BLANKET' --'STANDARD' for standard PO
,p_lineid => 7885858 -- null if you want to FINALLY CLOSE at header level
,p_shipid => NULL
,p_action => x_action
,p_reason => NULL
,p_calling_mode => x_calling_mode
,p_conc_flag => x_conc_flag
,p_return_code => x_return_code_h
,p_auto_close => x_auto_close
,p_action_date => SYSDATE
,p_origin_doc_id => NULL
);

IF x_returned = TRUE
THEN
DBMS_OUTPUT.put_line
( 'Purchase Order which just got Closed/Finally Closed is '
|| po_head.segment1
);
COMMIT;
ELSE
DBMS_OUTPUT.put_line
('API Failed to Close/Finally Close the Purchase Order');
END IF;
END LOOP;
EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line

('Program failed with unexpected error');

END;

Wednesday, October 27, 2010

JDeveloper and OAF

How to find correct version of jDeveloper.

1) Login to EBS.
2) In Home page , lower left corner select About this page.

3) Select Technology component and in that we can see OA Framework version

4) For aboove version we can find the required jdeveloper patch in following metalink

416708.1 How to find the correct version of JDeveloper to use with eBusiness Suite 11i or Release 12.x

Profile Options:

1) getting personalization link on page set this profile option-*Personalize Self-Service Defn* to yes

2) for getting about link on page set this profile option-*FND: Diagnostics* to yes.

3) Set profile "Sign-On:Notification" to "No" at site level