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' ;

Generate XML Data with SQL query using XML tags

<?xml version="1.0" encoding="WINDOWS-1252" ?>

- <dataTemplate name="XXXXXSMSN" description="XXX Signup Notification" version="1.0">

- <parameters>

<parameter name="P_ORDER_HEADER_ID" dataType="NUMBER" />

</parameters>

- <dataQuery>

- <sqlStatement name="Q_MAIN">

- <![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>

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

http://knoworacleappsdba.blogspot.com/2012/07/all-about-output-post-processor-opp-in.html

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

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 ;
/

--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;