Pre requisites
---------------
1) Item Exists for Master Org.
SET SERVEROUTPUT ON
CREATE OR REPLACE PROCEDURE ProcessItmOrgAsg(p_organization_code IN VARCHAR2)
AS
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2(2) := FND_API.G_TRUE;
l_commit VARCHAR2(2) := FND_API.G_FALSE;
l_item_org_assignment_tbl EGO_ITEM_PUB.ITEM_ORG_ASSIGNMENT_TBL_TYPE;
x_message_list Error_Handler.Error_Tbl_Type;
x_return_status VARCHAR2(2);
x_msg_count NUMBER := 0;
l_user_id NUMBER := -1;
l_resp_id NUMBER := -1;
l_application_id NUMBER := -1;
l_rowcnt NUMBER := 1;
l_user_name VARCHAR2(30) := 'MGRPLM';
l_resp_name VARCHAR2(30) := 'EGO_DEVELOPMENT_MANAGER';
CURSOR csr_org_items IS
SELECT inventory_item_id, segment1, primary_uom_code
FROM mtl_system_items_b
WHERE segment1 = 'D10001';
BEGIN
-- Get the user_id
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = l_user_name;
-- Get the application_id and responsibility_id
SELECT application_id, responsibility_id
INTO l_application_id, l_resp_id
FROM fnd_responsibility
WHERE responsibility_key = l_resp_name;
FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id, l_application_id); -- MGRPLM / Development Manager / EGO
dbms_output.put_line('Initialized applications context: '|| l_user_id || ' '|| l_resp_id ||' '|| l_application_id );
-- call API to assign Items
DBMS_OUTPUT.PUT_LINE('===========================================');
DBMS_OUTPUT.PUT_LINE('Calling EGO_ITEM_PUB.Process_Item_Org_Assignment API');
FOR itm IN csr_org_items LOOP
l_item_org_assignment_tbl(l_rowcnt).INVENTORY_ITEM_ID := itm.inventory_item_id;
l_item_org_assignment_tbl(l_rowcnt).ITEM_NUMBER := itm.segment1;
SELECT organization_id
INTO l_item_org_assignment_tbl(l_rowcnt).ORGANIZATION_ID
FROM mtl_parameters
WHERE organization_code = p_organization_code;
l_item_org_assignment_tbl(l_rowcnt).ORGANIZATION_CODE := p_organization_code;
l_item_org_assignment_tbl(l_rowcnt).PRIMARY_UOM_CODE := itm.primary_uom_code;
END LOOP;
EGO_ITEM_PUB.PROCESS_ITEM_ORG_ASSIGNMENTS(
P_API_VERSION => l_api_version
, P_INIT_MSG_LIST => l_init_msg_list
, P_COMMIT => l_commit
, P_ITEM_ORG_ASSIGNMENT_TBL => l_item_org_assignment_tbl
, X_RETURN_STATUS => x_return_status
, X_MSG_COUNT => x_msg_count
);
DBMS_OUTPUT.PUT_LINE('=========================================');
DBMS_OUTPUT.PUT_LINE('Return Status: '||x_return_status);
IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
DBMS_OUTPUT.PUT_LINE('Error Messages :');
Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list);
FOR i IN 1..x_message_list.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(x_message_list(i).message_text);
END LOOP;
END IF;
DBMS_OUTPUT.PUT_LINE('=========================================');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Exception Occured :');
DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
DBMS_OUTPUT.PUT_LINE('========================================');
END;
/
Reference:
http://tkandhati.blogspot.com/2010/12/item-organization-assignment.html
http://techoracleapps.blogspot.com/2010/03/api-to-assign-item-to-child-org.html
Friday, November 15, 2013
Thursday, August 29, 2013
Order to Cash Tables and Data Flow
--ORDER MAIN TABLES
select flow_status_code, booked_flag, a.* from oe_order_headers_all a where order_number = '4025794';
select flow_status_code, a.* from oe_order_lines_all a where header_id = 795291;
select * from oe_price_adjustments where header_id = 795291 and line_id = 2440080;
select * from oe_order_price_attribs where header_id = 795291 and line_id = 2440080;
select * from oe_order_holds_all where header_id = 795291 and line_id = 2440080;
--DELIVERY TABLES
select * from wsh_delivery_details where source_header_id = 795291 and source_line_id = 2440080;
select * from wsh_delivery_assignments where delivery_detail_id = 4537129;
--PICK RELEASE (from SHIPPING tab)
--Concurrent Program "Pick Selection List Generation" kicks off after releasing order from SHIPPING.
--This in turn kicks off "Pick Slip Report" and "Auto Ship Confirm Report (Auto Ship Confirm Report)"
select * from wsh_new_deliveries where delivery_id = 653581;
select * from wsh_picking_batches where name = '653581';
select * from wsh_serial_numbers where delivery_detail_id = 4537129;
--MOVE ORDER
select * from mtl_txn_request_headers where request_number = '689455';
select * from mtl_txn_request_lines where header_id = 690521;
--SHIP CONFIRM
--Shipping Transaction for after entering serial numbers.
--Kicks off "Interface Trip Stop" very imp program that affects Inventory.
select * from mtl_material_transactions where source_code = 'ORDER ENTRY' and transaction_reference = 795291 and trx_source_line_id = 2440080;
select * from mtl_unit_transactions where inventory_item_id = 379603 and serial_number in ('SUMIT00','SUMIT01');
select * from mtl_serial_numbers where inventory_item_id = 379603 and upper(serial_number) in ('SUMIT00','SUMIT01');
--Run "Workflow Background Process" for "OM Order Line"
select * from ra_interface_lines_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
select * from ra_interface_salescredits_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
select * from ra_interface_distributions_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
--Run "Auto Invoice Master Program" and that will kick "Auto Invoice Import" program that will create invoice for the order
-- "Prepayment Matching Program (Prepayments Matching Program)"
select * from ra_customer_trx_all where interface_header_attribute1 = '4025794' and creation_date >= trunc(sysdate);
select * from ra_customer_trx_lines_all where interface_line_attribute1 = '4025794' and creation_date >= trunc(sysdate);
select * from ra_cust_trx_line_salesreps_all where customer_trx_line_id = 5758780;
select * from ra_cust_trx_line_gl_dist_all where customer_trx_line_id = 5758780;
select * from ar_payment_schedules_all where trx_number = '50133102';
--Receipt against invoice
select * from ar_cash_receipts_all where receipt_number like '12345%' and creation_date >= trunc(sysdate);
select * from ar_receivable_applications where cash_receipt_id in (130168,130169);
--Run "General Ledge Transfer Program"
--This kicks off "Revenue Recognition" and "Revenue Contingency Analyzer" and "Update Posting Control" program
select * from gl_interface where date_created >= '10-JUN-2011' and user_je_category_name = 'Sales Invoices' and user_je_source_name = 'Receivables'
and reference10 like '%50133102%' and reference24 = '50133102' and reference23 = '42596305';
--Run "Journal Import"
select * from gl_je_batches where creation_date >= '13-JUN-2011' order by je_batch_id desc;
select * from gl_je_headers where je_source = 'Receivables' and je_category = 'Sales Invoices' and je_batch_id = 1592931;
select * from gl_je_lines where je_header_id = 1750124;
--Post Journals "Posting"
select * from gl_balances where last_update_date >= '13-JUN-2011' ;
select flow_status_code, booked_flag, a.* from oe_order_headers_all a where order_number = '4025794';
select flow_status_code, a.* from oe_order_lines_all a where header_id = 795291;
select * from oe_price_adjustments where header_id = 795291 and line_id = 2440080;
select * from oe_order_price_attribs where header_id = 795291 and line_id = 2440080;
select * from oe_order_holds_all where header_id = 795291 and line_id = 2440080;
--DELIVERY TABLES
select * from wsh_delivery_details where source_header_id = 795291 and source_line_id = 2440080;
select * from wsh_delivery_assignments where delivery_detail_id = 4537129;
--PICK RELEASE (from SHIPPING tab)
--Concurrent Program "Pick Selection List Generation" kicks off after releasing order from SHIPPING.
--This in turn kicks off "Pick Slip Report" and "Auto Ship Confirm Report (Auto Ship Confirm Report)"
select * from wsh_new_deliveries where delivery_id = 653581;
select * from wsh_picking_batches where name = '653581';
select * from wsh_serial_numbers where delivery_detail_id = 4537129;
--MOVE ORDER
select * from mtl_txn_request_headers where request_number = '689455';
select * from mtl_txn_request_lines where header_id = 690521;
--SHIP CONFIRM
--Shipping Transaction for after entering serial numbers.
--Kicks off "Interface Trip Stop" very imp program that affects Inventory.
select * from mtl_material_transactions where source_code = 'ORDER ENTRY' and transaction_reference = 795291 and trx_source_line_id = 2440080;
select * from mtl_unit_transactions where inventory_item_id = 379603 and serial_number in ('SUMIT00','SUMIT01');
select * from mtl_serial_numbers where inventory_item_id = 379603 and upper(serial_number) in ('SUMIT00','SUMIT01');
--Run "Workflow Background Process" for "OM Order Line"
select * from ra_interface_lines_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
select * from ra_interface_salescredits_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
select * from ra_interface_distributions_all where interface_line_context = 'ORDER ENTRY' and interface_line_attribute1 = '4025794';
--Run "Auto Invoice Master Program" and that will kick "Auto Invoice Import" program that will create invoice for the order
-- "Prepayment Matching Program (Prepayments Matching Program)"
select * from ra_customer_trx_all where interface_header_attribute1 = '4025794' and creation_date >= trunc(sysdate);
select * from ra_customer_trx_lines_all where interface_line_attribute1 = '4025794' and creation_date >= trunc(sysdate);
select * from ra_cust_trx_line_salesreps_all where customer_trx_line_id = 5758780;
select * from ra_cust_trx_line_gl_dist_all where customer_trx_line_id = 5758780;
select * from ar_payment_schedules_all where trx_number = '50133102';
--Receipt against invoice
select * from ar_cash_receipts_all where receipt_number like '12345%' and creation_date >= trunc(sysdate);
select * from ar_receivable_applications where cash_receipt_id in (130168,130169);
--Run "General Ledge Transfer Program"
--This kicks off "Revenue Recognition" and "Revenue Contingency Analyzer" and "Update Posting Control" program
select * from gl_interface where date_created >= '10-JUN-2011' and user_je_category_name = 'Sales Invoices' and user_je_source_name = 'Receivables'
and reference10 like '%50133102%' and reference24 = '50133102' and reference23 = '42596305';
--Run "Journal Import"
select * from gl_je_batches where creation_date >= '13-JUN-2011' order by je_batch_id desc;
select * from gl_je_headers where je_source = 'Receivables' and je_category = 'Sales Invoices' and je_batch_id = 1592931;
select * from gl_je_lines where je_header_id = 1750124;
--Post Journals "Posting"
select * from gl_balances where last_update_date >= '13-JUN-2011' ;
Wednesday, August 28, 2013
Generate XML Data with SQL query using XML tags
<?xml
version="1.0" encoding="WINDOWS-1252" ?>
<parameter name="P_ORDER_HEADER_ID"
dataType="NUMBER" />
</parameters>
<![CDATA[
select ooh.CUST_PO_NUMBER
,ooh.ORDER_NUMBER
,TO_CHAR(ooh.ORDERED_DATE, 'MM/DD/YYYY') ORDERED_DA from OE_ORDER_HEADERS_ALL ooh
where ooh.header_id = :P_ORDER_HEADER_ID]]>
</sqlStatement>
</dataQuery>
</dataTemplate>
Wednesday, August 14, 2013
All About Output Post Processor (OPP) in Oracle Applications
The integration of XML Publisher within Concurrent Processing is done by means of a specialized concurrent manager called the Output Post Processor (OPP). If a request is submitted which has an XML Publisher template specified as a layout for the output, then after the concurrent manager finishes running the concurrent program, it will contact the OPP to apply the XML Publisher template and create the final output.
An overview of the actions involved:
1. An application user submits an XML Publisher based report.
2. The standard concurrent manager processes the request.
3. The XML data file is generated by the standard concurrent manager. This can be done by various methods:
o Oracle Reports - Report Definition File (RDF)
o XML Publisher Data Template - XML data template linked to the Data Definition
o Any other process that produces XML output
4. A post processing action defines that the output needs to be generated by the Output Post Processor hence it is triggered by the standard manager.
5. The Output Post Processor generates the final report and informs the standard concurrent manager whether that was successful.
6. The standard concurrent manager finalizes the concurrent request.
Processes in OPP:
There should always be at least one OPP process active in the system. If no OPP service is available to process concurrent requests, completed requests that require OPP post-processing will complete with a status of Warning.
One service instance of the OPP service is seeded by default. This seeded OPP service instance has one workshift with one process.
A concurrent manager contacts an available OPP process when a running concurrent request needs an OPP post-processing action. Concurrent managers use a local OPP process (on the same node) by default, but will choose a remote OPP if no local OPP process is available.
Threads in OPP:
The OPP Service is multi-threaded and will start a new thread for each concurrent request it processes. You can control the number of simultaneous threads for an OPP Service Instance by adjusting the Threads per Process parameter for the instance.
If all the OPP process has reached their respective maximum number of threads, the requests waiting to be processed remain in a queue to be processed as soon as threads become available. If request throughput has become slow, you may want to increase the number of Threads per Process for the OPP.
The number of concurrent requests that the Output Post Processor can handle in parallel depends upon:
· the number of Processes
· the number of Threads Per Process
The default values are 2 Processes and 5 Threads per Process so a total of 10 reports can be processed in parallel.
Issues with OPP:
1. Timeout issue with OPP:
Example:
There is 1 OPP process with 2 threads. Hence 4 reports can be processed at any time.
- In case there are other concurrent requests running which have already invoked the OPP then it might happen that no additional requests can be picked up for a period of time. The pending request will be picked up as soon as one of the running jobs completes.
By default a timeout will occur if it takes longer than 120 seconds (2 min.) for the Output Post Processor to pick up the request from the concurrent manager process. In that case, the concurrent request will complete with status Warning.
-Once the Output Post Processor picks up the request, the BI Publisher engine is invoked to generate the final output file. The time that this takes will depends on various elements such as:
· size of the XML Data File
· complexity of the template
· performance of the server
By default a timeout will occur if it takes longer than 300 seconds (5 min.) for the BI Publisher engine to generate the output file. The concurrent request will complete with status Warning
Solution:
There are 2 new profiles options that can be used to control the timeouts.
Profile Option : Concurrent:OPP Response Timeout
Internal Name : CONC_PP_RESPONSE_TIMEOUT
Description : Specifies the amount of time a manager waits for OPP to respond to its request for post processing.
Profile Option : Concurrent:OPP Process Timeout
Internal Name : CONC_PP_PROCESS_TIMEOUT
Description : Specifies the amount of time the manager waits for the OPP to actually process the request.
The value for the above profile options can be increased to avoid timeouts.
The number of processes/threads for OPP can also be increased; however the concurrent manager has to be restarted for the changes to take effect.
2. Output Post Processing Fails Due To java.lang.ThreadDeath
-Increase the value of the Concurrent:OPP Timeout profile option to 10800 seconds.
-Enable the scalability feature of XML Publisher:
a. Login as SYSADMIN
b. Responsibility: XML Publisher Administrator
c. Function: Administration
d. Set the following properties:
e. Temporary Directory
f. Use XML Publisher's XSLT processor: True
g. Enable scalable feature of XSLT processor: True
h. Enable XSLT runtime optimization: True
- Restart the Concurrent Managers so that changes take effect
3. Output Post Processor is Down with Actual Process is 0 And Target Process is 1
This can happen on a cloned instance.
- FNDSVC should exist under FND_TOP/bin
- Bring down all application services and relink the FNDSVC through adadmin or using the below command:
adrelink.sh force=y ranlib=y "FND FNDSVC"
- Restart all applications services and restest the issue.
4. Output Post Processor (OPP) Log Contains Error "java.lang.OutOfMemoryError: Java heap space
- Determine what the heap size per OPP process is currently:
select DEVELOPER_PARAMETERS from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
- The default should be:
J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m
- Increase the Heap Space per Process to 1024:
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
- Bring the Concurrent managers down.
-Run cmclean.sql script from Note 134007.1 - CMCLEAN.SQL Non-Destructive Script to Clean Concurrent Manager Tables.
-Bring the managers up again.
OR
-Log into applications with the System Administrator responsibility.
- Navigate to Concurrent -> Program -> Define
-Query the XML Publisher Template Re-Generator program
-Set the following value for the Executable Options: -Xmx1024m
- Save changes.
- Retest the program.
Reference: Oracle Metalink
An overview of the actions involved:
1. An application user submits an XML Publisher based report.
2. The standard concurrent manager processes the request.
3. The XML data file is generated by the standard concurrent manager. This can be done by various methods:
o Oracle Reports - Report Definition File (RDF)
o XML Publisher Data Template - XML data template linked to the Data Definition
o Any other process that produces XML output
4. A post processing action defines that the output needs to be generated by the Output Post Processor hence it is triggered by the standard manager.
5. The Output Post Processor generates the final report and informs the standard concurrent manager whether that was successful.
6. The standard concurrent manager finalizes the concurrent request.
Processes in OPP:
There should always be at least one OPP process active in the system. If no OPP service is available to process concurrent requests, completed requests that require OPP post-processing will complete with a status of Warning.
One service instance of the OPP service is seeded by default. This seeded OPP service instance has one workshift with one process.
A concurrent manager contacts an available OPP process when a running concurrent request needs an OPP post-processing action. Concurrent managers use a local OPP process (on the same node) by default, but will choose a remote OPP if no local OPP process is available.
Threads in OPP:
The OPP Service is multi-threaded and will start a new thread for each concurrent request it processes. You can control the number of simultaneous threads for an OPP Service Instance by adjusting the Threads per Process parameter for the instance.
If all the OPP process has reached their respective maximum number of threads, the requests waiting to be processed remain in a queue to be processed as soon as threads become available. If request throughput has become slow, you may want to increase the number of Threads per Process for the OPP.
The number of concurrent requests that the Output Post Processor can handle in parallel depends upon:
· the number of Processes
· the number of Threads Per Process
The default values are 2 Processes and 5 Threads per Process so a total of 10 reports can be processed in parallel.
Issues with OPP:
1. Timeout issue with OPP:
Example:
There is 1 OPP process with 2 threads. Hence 4 reports can be processed at any time.
- In case there are other concurrent requests running which have already invoked the OPP then it might happen that no additional requests can be picked up for a period of time. The pending request will be picked up as soon as one of the running jobs completes.
By default a timeout will occur if it takes longer than 120 seconds (2 min.) for the Output Post Processor to pick up the request from the concurrent manager process. In that case, the concurrent request will complete with status Warning.
-Once the Output Post Processor picks up the request, the BI Publisher engine is invoked to generate the final output file. The time that this takes will depends on various elements such as:
· size of the XML Data File
· complexity of the template
· performance of the server
By default a timeout will occur if it takes longer than 300 seconds (5 min.) for the BI Publisher engine to generate the output file. The concurrent request will complete with status Warning
Solution:
There are 2 new profiles options that can be used to control the timeouts.
Profile Option : Concurrent:OPP Response Timeout
Internal Name : CONC_PP_RESPONSE_TIMEOUT
Description : Specifies the amount of time a manager waits for OPP to respond to its request for post processing.
Profile Option : Concurrent:OPP Process Timeout
Internal Name : CONC_PP_PROCESS_TIMEOUT
Description : Specifies the amount of time the manager waits for the OPP to actually process the request.
The value for the above profile options can be increased to avoid timeouts.
The number of processes/threads for OPP can also be increased; however the concurrent manager has to be restarted for the changes to take effect.
2. Output Post Processing Fails Due To java.lang.ThreadDeath
-Increase the value of the Concurrent:OPP Timeout profile option to 10800 seconds.
-Enable the scalability feature of XML Publisher:
a. Login as SYSADMIN
b. Responsibility: XML Publisher Administrator
c. Function: Administration
d. Set the following properties:
e. Temporary Directory
f. Use XML Publisher's XSLT processor: True
g. Enable scalable feature of XSLT processor: True
h. Enable XSLT runtime optimization: True
- Restart the Concurrent Managers so that changes take effect
3. Output Post Processor is Down with Actual Process is 0 And Target Process is 1
This can happen on a cloned instance.
- FNDSVC should exist under FND_TOP/bin
- Bring down all application services and relink the FNDSVC through adadmin or using the below command:
adrelink.sh force=y ranlib=y "FND FNDSVC"
- Restart all applications services and restest the issue.
4. Output Post Processor (OPP) Log Contains Error "java.lang.OutOfMemoryError: Java heap space
- Determine what the heap size per OPP process is currently:
select DEVELOPER_PARAMETERS from FND_CP_SERVICES
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
- The default should be:
J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx512m
- Increase the Heap Space per Process to 1024:
update FND_CP_SERVICES
set DEVELOPER_PARAMETERS =
'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx1024m'
where SERVICE_ID = (select MANAGER_TYPE from FND_CONCURRENT_QUEUES
where CONCURRENT_QUEUE_NAME = 'FNDCPOPP');
- Bring the Concurrent managers down.
-Run cmclean.sql script from Note 134007.1 - CMCLEAN.SQL Non-Destructive Script to Clean Concurrent Manager Tables.
-Bring the managers up again.
OR
-Log into applications with the System Administrator responsibility.
- Navigate to Concurrent -> Program -> Define
-Query the XML Publisher Template Re-Generator program
-Set the following value for the Executable Options: -Xmx1024m
- Save changes.
- Retest the program.
Reference: Oracle Metalink
Monday, August 12, 2013
How to Create Code Combination ID by API
--Create Function
CREATE OR REPLACE FUNCTION APPS.XXX_CREATE_CCID
( P_CONCAT_SEGS IN VARCHAR2
) RETURN VARCHAR2
IS
L_STATUS BOOLEAN;
L_COA_ID NUMBER;
BEGIN
SELECT CHART_OF_ACCOUNTS_ID
INTO L_COA_ID
FROM GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = 2021; –UPDATE THIS WITH SET OF BOOKS ID
CREATE OR REPLACE FUNCTION APPS.XXX_CREATE_CCID
( P_CONCAT_SEGS IN VARCHAR2
) RETURN VARCHAR2
IS
L_STATUS BOOLEAN;
L_COA_ID NUMBER;
BEGIN
SELECT CHART_OF_ACCOUNTS_ID
INTO L_COA_ID
FROM GL_SETS_OF_BOOKS
WHERE SET_OF_BOOKS_ID = 2021; –UPDATE THIS WITH SET OF BOOKS ID
L_STATUS := FND_FLEX_KEYVAL.VALIDATE_SEGS(
‘CREATE_COMBINATION’,
‘SQLGL’,
‘GL#’,
L_COA_ID,
P_CONCAT_SEGS,
‘V’,
SYSDATE,
‘ALL’, NULL, NULL, NULL, NULL,
FALSE,FALSE, NULL, NULL, NULL);
IF L_STATUS THEN
RETURN ‘S’;
ELSE
RETURN ‘F’;
END IF;
END ;
/
‘CREATE_COMBINATION’,
‘SQLGL’,
‘GL#’,
L_COA_ID,
P_CONCAT_SEGS,
‘V’,
SYSDATE,
‘ALL’, NULL, NULL, NULL, NULL,
FALSE,FALSE, NULL, NULL, NULL);
IF L_STATUS THEN
RETURN ‘S’;
ELSE
RETURN ‘F’;
END IF;
END ;
/
--EXECUTE FUNCTION
DECLARE
RETVAL VARCHAR2(200);
P_CONCAT_SEGS VARCHAR2(200); /* ’10.2001.2211101.987872.001.0000′ THIS COMBINATION I WANT TO CREATE */
BEGIN
RETVAL := APPS.XXX_CREATE_CCID ( P_CONCAT_SEGS );
COMMIT;
END;
DECLARE
RETVAL VARCHAR2(200);
P_CONCAT_SEGS VARCHAR2(200); /* ’10.2001.2211101.987872.001.0000′ THIS COMBINATION I WANT TO CREATE */
BEGIN
RETVAL := APPS.XXX_CREATE_CCID ( P_CONCAT_SEGS );
COMMIT;
END;
Tuesday, July 30, 2013
How to find error message comming in oracle API's
After the calling of API give the following
IF (fnd_msg_pub.count_msg > 0)THEN
FOR i IN 1..fnd_msg_pub.count_msg
LOOP
fnd_msg_pub.get
( p_msg_index => i,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => X_msg_count
);
DBMS_OUTPUT.PUT_LINE('API ERROR: ' || x_msg_data);
END LOOP;
dbms_output.put_line(x_jtf_note_id ||'--'|| x_return_status ||'--'|| X_msg_count ||'--'|| x_msg_data);
ELSE
DBMS_OUTPUT.PUT_LINE('Updated sr : ' || to_char(ChildIncId));
End if;
http://programmerslounge.blogspot.com/2009/04/how-to-find-error-message-comming-in.html
IF (fnd_msg_pub.count_msg > 0)THEN
FOR i IN 1..fnd_msg_pub.count_msg
LOOP
fnd_msg_pub.get
( p_msg_index => i,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => X_msg_count
);
DBMS_OUTPUT.PUT_LINE('API ERROR: ' || x_msg_data);
END LOOP;
dbms_output.put_line(x_jtf_note_id ||'--'|| x_return_status ||'--'|| X_msg_count ||'--'|| x_msg_data);
ELSE
DBMS_OUTPUT.PUT_LINE('Updated sr : ' || to_char(ChildIncId));
End if;
http://programmerslounge.blogspot.com/2009/04/how-to-find-error-message-comming-in.html
Wednesday, July 24, 2013
INSTALL BASE ALL MAIN TABLES
Instance
Records the Item Instance Details of an IB instance
Table: CSI_ITEM_INSTANCES
Transaction
Install Base Transaction Log
Table: CSI_TRANSACTIONS
Csi_transactions
Transaction_id
Transaction_type_id
Csi_txn_types
Transaction_type_id
Csi_item_instances_h
Instance_history_id
Instance_id
Transaction_id
Csi_item_instances
Instance_id
Inventory_item_id
Serial_number
Instance_status_id
Location_id
Instance Relationship
Defines the instance to instance relationships
Table: CSI_II_RELATIONSHIPS
Csi_item_instances
Instance_id
Csi_ii_relationships
Relationship_id
Subject_id
Relationship_type_code
Object_id
Csi_ii_relation_types
Relationship_type_code
Csi_ii_relationships_h
Relationship_history_id
Relationship_id
Transaction_Id
Csi_transactions
Transaction_id
Instance Party relationship
Defines the relationship between Instance and party
Table: CSI_II_PARTIES
Instance Party accounts
Defines the Instance to account (party account) association
Table: CSI_IP_ACCOUNTS
Csi_item_instances
Instance_id
Csi_ip_accounts_h
Ip_account_history_id
Ip_account_id
Transaction_id
Csi_ip_accounts
Ip_account_id
Instance_party_id
Party_account_id
Csi_i_parties
Instance_party_id
Relationship_type_code
Instance_id
Party_id
Csi_transactions
Transaction_id
Csi_i_parties_h
Instance_party_history_id
Instance_party_id
Transaction_id
API's
CSI_ITEM_INSTANCE_PUB
Create Item Instance
Update Item Instance
Expire Item Instance
Get Item Instance
Get Item Instance Details
Copy Item Instance
CSI_II_RELATIONSHIPS_PUB
Create Relationship
Update Relationship
Expire Relationship
CSI_SYSTEMS_PUB
Create System
Update System
Expire System
Get System
CRM CONTRACTS TABLES INFO
OKC_ASSENTS
Indicates if an Operation is to be performed for a Subclass of Contract while in a Status
Assent works with Operations (OKC_OPERATIONS_B), Status (OKC_STATUSES_B), and Subclass (OKC_SUBCLASSES_B) to allow flexibility as to what operations can be performed on a different types of Contracts in different statuses.
Subclasses define different types of Contracts (see OKC_SUBCLASSES_B for more information).
Statuses define different states a Contract may be in (see OKC_STATUSES_B for more information).
Operations define different operations a user, or the application may perform on or because of the Contract (see OKC_OPERATIONS_B for more information).
Assent is at the intersection of all three, defining if a specific Operation is allowed or disallowed for a Subclass of Contract while it has a specific Status.
For example, assume that for an implementation we have defined the Status 'Bill Hold'. If the ALLOWED_YN column is set to 'N' for the Operation 'Bill' for the Subclass 'Service' for the Status 'Bill Hold', then billing will not to be performed for 'Service' contracts in the 'Bill Hold' status.
ID Unique identifier for assent defined..
STS_CODE Status for which this assent is defined
OPN_CODE Operation for this assent is defined
STE_CODE Status type for which this assent is defined
SCS_CODE Subclass for which this assent is defined.
OKC_OPERATIONS_B
Set of processes performed by the application to, or as a result of, a contract.
OKC_OPERATIONS defines a set of processes performed by the application to, or as a result of, a contract.
Some operations may be performed on the contract, such as update on line or update via change request. Others are performed as the result of a contract line, such as entitle or bill.
Along with OKC_ASSENTS, this provides information to the application as to what it can do and what it should allow the user to do.
CODE CODE for operations defined at contract header/contract line level.
OPN_TYPE Type of operation (contract or line).
OBJECT_VERSION_NUMBER Sequential number set at 1 on insert and incremented on update. Used by APIs to ensure current record is passed.
OKC_STATUSES_B
User defined values that define a contract's status.
STATUS is a user defined value that defines a contract's status.
Each user-defined status must be in one of six STATUS TYPES, which are seeded values in FND_LOOKUPS. The six status types are:
Entered
Cancelled
Active
Hold
Expired
Terminated
Within each status type, users may define as many statuses as they need. Along with OPERATIONS, SUBCLASS, and ASSENT, status helps drive what the system can or cannot do and what the system allows users to do. For example, it may be allowed to delete a contract in a Cancelled status but not in an Active status.
CODE Status code as defined in FND_LOOKUP_VALUES.
STE_CODE Status Type to which this Status Code belongs to. For example, Status Type "ENTERED'' can have ''ENTERED'', ''SUBMITTED FOR APPROVAL'' as status codes.
DEFAULT_YN Indicates if a status code is the default status code for the given status type.
START_DATE The beginning of the active period, one second after midnight on the date indicated.
END_DATE The end of the active period, one second before midnight on the date indicated.
OKC_PROCESS_DEFS_B
This table stores information of PL/SQL processes or workflows within the application which are used as OUTCOME, CONTRACT PROCESS, QA PROCESS, or FUNCTION in a CONDITION LINE.
This table stores information of PL/SQL processes or workflows registered with the application to be used as OUTCOME, CONTRACT PROCESS, QA PROCESS, or FUNCTION in a CONDITION LINE. Along with OKC_PROCESS_DEF_PARMS, this table provides the information necessary for the application to invoke the process or workflow. The usage of these processes is recorded in OKC_OUTCOMES, OKC_K_PROCESSES, OKC_CONDITION_LINES, and OKC_QA_LIST_PROCESSES.
ID System generated Unique Identifier. Generated from sequence 'OKC_PROCESS_DEFS_S1'. Also the Primary key for the table.
PDF_TYPE Process definition type. Valid values are ALERT, SCRIPT, PPS, WPS.
OBJECT_VERSION_NUMBER Sequential number set at 1 on insert and incremented on update. Used by APIs to ensure current record is passed.
CREATED_BY Standard Who column.
USAGE Usage of Process Definition. Valid values are Approve, Auto Numbering, Approve Change Request, Function, Outcome and Quality Assurance. Refers to LOOKUP_CODE in FND_LOOKUPS where LOOKUP_TYPE= 'OKC_PROCESS_USAGE_TYPES'.
NAME
OKC_QA_CHECK_LISTS_B
Associates a list of quality assurance processes with a specific contract or contract template
Provides a "header" that associates a list of quality assurance processes with a specific contract or contract template.
Because it can take a long time to collect and enter the information about a contract, it is not possible to enforce all data integrity rules or business rules during data entry. The purpose of the QA check list is to assemble a set of routines (defined in OKC_PROCESS_DEFS) that will run against the contract to validate that all such rules have been met. Rules about required data integrity are "hardcoded", and are always run. Others are intended to be supplied by the client as independent routines assembled into the check list.
OKS_BILLING_PROFILES_B
Contains profile information for a customer.
This table holds Billing profile information for a customer. Billing profiles include Information about customer account, customer billing address, invoicing rule, accounting rule, billing level, billing type, billing interval, interface offset and invoiving offset. This table is populated through Billing Profile setup UI. Users can use the billing profile to overwrite any existing line level billing schedule information on the contract authoring form by selecting it in the Cascade Attributes form. Upon renewal, users can also default billing profile information onto the contract by associate a billing profile template to a customer in the Global Contract Defaults form.
OKS_SERV_AVAILS
Stores availability information for a service.
This table stores information about general service availability for service item and populated through the header part of Service Availability setup UI. This table is closely related to the table OKS_SERV_AVAIL_EXCEPTS where the exception of service availability is stored. These two tables are combined together to get the information of available services for service programs, warranties, and extended warranties. Service Availability inclusion and exclusion rules are honored while selling services in upstream applications like quoting, Order Management. When user enters a service item, by default generally available check box if checked for party and product and on saving the default entry, two records are saved in this table meaning service is generally available for party and product. User can specify affectivity of this service availability also by entering values in START_DATE_ACTIVE and END_DATE_ACTIVE columns. If generally available check box is unchecked for party or customer, general_yn flag will be set to 'N' meaning service is not available generally for party or product.
ID Internal Unique Identifier.
OBJECT1_ID1 Item id used for service.
OBJECT1_ID2 Not used.
JTOT_OBJECT1_CODE Foreign key to JTF_OBJECTS_B. JTF Object code identifying the OKX view for service item. Identifies the Source Object (Table/View/Object) that contains the item.
OKS_COV_TYPES_B
Contains the coverage type code and importance of each coverage type.
OKS_COV_TYPES_B is populated by Coverage Types setup form. This table contains the coverage type and importance level definition. E.g., G (Gold), S (Silver) and B (Bronze). The Coverage types defined here are used in the Coverage form.
CODE Unique Identifier code for records in OKS_COV_TYPES_B
IMPORTANCE_LEVEL Importance level associated to the Coverage type. Used by Service Application to prioritize their task or service.
ENABLED_FLAG Flag to indicate that the coverage type record is enabled to be used
START_DATE_ACTIVE Specifies the date when the coverage type is allowed to be used
END_DATE_ACTIVE Specifies the date when the coverage type is no longer effective
CRM PROFILE VALUES
OKC: View Contracts By Organization
OKS: Contact Center Date Range
OKS: Intangible Subscription Method
OKS: Transfer Party Relationship
OKS: Update Contract with Install Base Quantity
OKS: Warranty Consolidation
OKS: Full Credit for Product Return
OKS: Transfer Status
OKS: Credit Card Display Privileges
OKS: Intangible Subscription Pricing Method
OKS: Minimum Service Duration
OKS: Reprice Warning Message
OKS: Enable Negative Invoicing
OKS: Usage Billing Calculation
OKS: Payment Method for AR Interface
OKS: Default Order Type for Subscriptions
OKS: Category for Order Management Originated Contracts
OKS: Raise Credit Memo for Install Base Transactions
OKS: Notify User of Install Base Integration Notifications
OKS: Vendor Contact Role
OKS: Credit Card Validation Level
OKS: Credit Card Minimum Authorized Amount
OKS: Use Advanced Pricing for Manual Adjustment
OKS: Notify Contract Administrator
OKS: Notify Sales Administrator
OKS: Revenue Type for Sales Credits
OKS: Revenue Type Distribution for Sales Credit
OKS: Default Sales Person for Renewal
OKS: Use Territories to Default Sales Person
OKS: Wallet Path
IB TABLES
CSI_ITEM_INSTANCES
CSI_ITEM_INSTANCES_H
CSI_TRANSATIONS
CSI_TXN_TYPES
CSI_II_REALATIONSSHIPS
CSI_II_RALATION_TYPES
CSI_II_REALATIONSSHIPS_H
CSI_I_PARTIES
CSI_IP_ACCOUNTS
CSI_IP_ACCOUNTS_H
CSI_I_PARTIES_H
CSI_SYSTEMS_B
CSI_INSTANCE_STATUSES
Core Contracts
OKC_K_HEADERS_B
OKC_K_LINES_B
OKC_K_ITEMS
OKC_K_SALES_CREDITS
OKC_ROLES_B
OKC_PARTY_ROLES
OKC_STATUSES_B
OKC_ITEM_PARTYS_B
OKC_CONTACTS
OKC_RULES_B_AS
OKC_K_PARTY_ROLES_B
OKC_PRICE_ADJUSTMENTS
Service Contracts
OKS_K_HEADERS_B
OKS_K_LINES_B
OKS_K_ORDER_DETAILS
OKS_K_SALES_CREDITS
Service Billing
OKS_BILLRATE_SCHEDULES
OKS_BILL_CONT_LINES
OKS_BILL_SUB_LINES
OKS_BILL_SUB_LINES_DTLS
OKS_BILL_TRANSACTIONS
OKS_BILL_TXN_LINES
TCA
HZ_PARTIES
HZ_LOCATIONS
HZ_PARTY_SITES
HZ_PARTY_SITE_USES
HZ_PARTY_RELATIONSHIPS
HZ_CUST_ACCOUNTS
HZ_CUST_ACCOUNT_ROLES
HZ_ROLE_RESPONSIBILITY
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CONTACT_POINTS
HZ_CUST_CONTACT_PONTS
HZ_ORG_CONTACTS
HZ_ORG_CONTAC_ROLES
Subscribe to:
Posts (Atom)