Oracle Inventory Transactions and Cost processing

This blog provide basic understanding about Oracle Inventory Transactions and Cost
processing procedure.


SCOPE & APPLICATION
-------------------
Oracle manufacturing customers, support, and consulting.
  
Transactions - Unprocessed Transactions/Closing INV Accounting Period
(TRXN-STK)

This document will be used to explain the flow of transactions
from the MTL_TRANSACTIONS_INTERFACE to the GL_INTERFACE.
The aim of this document is to help and assist in resolution of
Interface related issues.


CONTENTS
--------
Q1.  What is the MTL_TRANSACTIONS_INTERFACE table used for?
Q2.  What is the MTL_MATERIAL_TRANSACTIONS_TEMP table used for?
Q3.  What type of issues will you see with the MTL__TRANSACTIONS_INTERFACE and
     MTL_MATERIAL_TRANSACTIONS_TEMP tables?
Q4.  What type of issues will you see with the MTL_MATERIAL_TRANSACTIONS table?
Q5.  What type of issues will you see with the MTL_TRANSACTION_ACCOUNTS table?
Q6.  Where do you find Pending/Errored Inventory Transactions?
Q7.  Where do you find Pending/Errored WIP Move and WIP Resource Transactions?
Q8.  What are the major tables involved in Material Movement for Inventory and
     WIP Material, Move, and Resource Transactions?
Q9.  How do you resolve Pending Material Transactions?
Q10. How do you resolve Pending WIP Cost Transactions?
Q11. When attempting to load legacy data into the MTL_TRANSACTIONS_INTERFACE
     table, into what column is the PO number input?
Q12. Why is the distribution account ID required in MTL_TRANSACTIONS_INTERFACE?


FAQ: Frequent Asked Questions
=============================
Q1. What is the MTL_TRANSACTIONS_INTERFACE table used for?

A1. This is the Interface between non-Inventory applications and the Inventory
Transactions module.

In other words any other module other than Inventory that wants to update
Inventory has to come through this table. Modules such as WIP (Work In Progress)
and OE (Order Entry) first pass their records to the
MTL_TRANSACTIONS_INTERFACE (MTI)for validation.

There is an Interface Manager called the Transactions Manager (INCTCM) which
reads records from this table, validates them and moves the successful
transactions onto MTL_MATERIAL_TRANSACTIONS_TEMP, and submits Transaction
workers (sub-processes - INCTCW) which then processes these records through
inventory. This process consists of data derivation, validation,
and transfer of records from MTL_TRANSCTIONS_INTERFACE,
MTL_TRANSACTIONS_LOTS_INTERFACE and MTL_SERIAL_NUMBERS_INTERFACE
into their respective TEMP (temporary) tables from where the transactions
processor processes them.

Both the Lots and Serial number tables above are used when items being
updated are under 'Lot' or 'Serial' number control. Example :- In the case
of an Sales Order, the item is being shipped to a client so the lot and serial
number if being used, this needs to be updated to show that it is no
longer available in Inventory stock.

It is important to note that in general the processors will not move the
transactions from this table if the following fields are not set as follows.

LOCK_FLAG = 2
PROCESS_FLAG = 1
transaction_mode = 3


Q2.  What is the MTL_MATERIAL_TRANSACTIONS_TEMP table used for?

A2.  Once the transactions have been passed to this table after initial
     validation by the Transactions Manager (INCTCM) from the
     MTL_TRANSACTIONS_INTERFACE a job id is attached and a Transactions
     Worker (INCTCW) is submitted by the INCTCM process in order tothe
     get the records processed and moved to the MTL_MATERIAL_TRANSACTONS
     table.

     This table is also used by Inventory Module and Purchasing module which
     writes directly onto this table for any transactions entered within
     itself and each transaction in turn through a process of strict
     validation.     

     Inventory Module Forms like Miscellaneous transactions writes directly
     into this table. The transactions which are done through these form. are
     on-line processing. It is from here that the inventory quantities finally
     get updated,serial/lot numbers get marked as being used.


Q3.  What type of issues will you see with the MTL__TRANSACTIONS_INTERFACE and
     MTL_MATERIAL_TRANSACTIONS_TEMP tables?

A3.  The issues can be related to stuck transactions in these tables.

The following is the list of errors that you may see in error_code,
error_explanation columns in the above tables:

NOTE: In Release 11 the APP-XXX errors do not show any more.

ERROR_CODE                                 ERROR EXPLANATION

APP-05367                                  Invalid Item
APP-05341                                  Invalid sub-inventory
APP-05343                                  Invalid transaction date
APP-05345                                  Invalid transaction UOM
APP-05354                                  Invalid locator segments
APP-05365                                  Invalid distribution account segments
APP-05372                                  Transaction processor error
APP-05526                                  Invalid locator
APP-05518                                  Negative balances not allowed
APP-05064                                  Quantity must be less than or
                                           equal to available to transact
                                           for that item at its specific
                                           control level
APP-05520                                  No open period found for date entered
Account period
Lot records
Serial records


APP-05367 Invalid Item
    Check the attributes against the set required for the transaction type.
    i.e. Order entry, PO etc.

APP-05341 Invalid Subinventory
    Check and make sure that this record does not already exist on the
    MTL_MATERIAL_TRANSACTIONS_TEMP table as sometimes the process does not
    clear some of the records out of  MTL_TRANSACTIONS_INTERFACE correctly.
    If this is the case it is a simple matter of deleting the records from
    the  MTL_TRANSACTIONS_INTERFACE.

    Check the subinventory against the available MTL_SECONDARY_INVENTORIES
    on the system for the  given ORGANIZATION_ID against this transaction.
    If the subinventory exists, check the TRANSACTION_TYPE_ID, for example:
    If it is a Purchase Order then you need to check the MTL_SUPPLY to
    ensure that the supply exists for this transaction.

APP-05343 Invalid transaction date
    In certain circumstances and depending on the transaction type
    that the future period is not open. There is no issue in changing
    the date to the current date and resubmitting it through the system.
         
APP-05345 Invalid transaction UOM
    Check the UOM measure being used and find out if valid for the
    organization source. If not set up the UOM. It may also be a case
    where the conversion classes have not been set-up.

APP-05354 Invalid locator segments
    APP-05355 The Locator segments are invalid for the given sub inventory,
    organization Combination.

    Check under Inventory Responsibility -> set-up ->  flexfeilds ->  key ->  
    segments  'stock locators',check each segment has been enabled.

APP-05365 Invalid distribution account segments
    The distribution account segments has its base in the Account Key Flexfield
    set-up. It is this Flexfield structure that determines how many segments
    should be populated into the segments are MTL_TRANSACTION_INTERFACE table.
    Basically in order to check if the distribution account correct we need to
    join the details back to the GL_CODE_COMBINATION table to validate the
    account code being used for the transaction.Inquiry on the error_explanation
    field as this may identify what segment is actually incorrect.This might
    return result APP-1756 Value 'XXXX' is not defined.

    Need to check that the segments point to a valid row in
    GL_CODE_COMBINATIONS.

APP-05372 Transaction processor error
    For this error you really need to get the ERROR_EXPLANATION to
    find out the exact message as to why it is possibly failing.
    These can usually have their three flags reset and re-submitted
    as this error is usually attached to those records in a group
    where one of the records has been erred with one of the more
    severe errors.

APP-05526 Invalid locator
    There is a list of checks that you need to make to ensure that
    the above error is correct.

           1.Extract Organization_id and cross check tables to see if the
             org is under locator control.
           2.Are the locator details filled incorrectly on the transaction
           3.Has the Organisation set-up been modified.

APP-05518 Negative balances not allowed.
    This message will be prompted if there is not enouhg on hand quantity
    to satisfy all the transactions within a batch. Therefore, ensure
    that the transaction quantity is enough to satisfy the sum of all
    the transaction quantities for a particular batch.
     
   APP-05064 Quantity must be less than or equal to Available to
             transact for that item at its specific control level.
      (For Example: subinventory,locator,lot,serial)
   1. If you specify a subinventory/locator while entering the order,
      it means we just wants to ship it out from that
      subinventory/locator and if there is not enough available to
      reserve/transact in that particular subinventory/locator the
      inventory program will error out with the above errors.  However, if
      there are no subinventories/locators specified while
      entering the order, we will pick up from where its available
      in that Organization.

   2. Check onhand and availabe quantity for the errored items.

   3. The records would have errored out due to some other row in the
      batch not having enough stock, so the entire batch would fail,  
      find the row that has error explanation populated. Resubmit all
      the other rows except the one which has error explanation , if
      the same error occurs for other rows, do the same process once
      again excluding that row.

   4. Please note that Available Quantity = On Hand Quantity - Reservations
      (Soft and Hard Reservations).
              
APP-05520 No open period found for date entered
           Period is not currently open for the transaction to be costed
           against later.
           1.Check that the period is open
           2.Check that the date is a date between the open and close period.


Q4.  What type of issues will you see with the MTL_MATERIAL_TRANSACTIONS table?
        
A4.  Transaction is valued (unit cost*quantity = $ amount)
     Once data gets here the transaction is almost complete except for
     the fact that it needs to have the costing details finally updated
     and written to the Transaction Accounts table for transfer to
     General Ledger. This is a transactional historical table. However,
     it is the holding point for transactions to be cost.

     The Cost Manager is a concurrent job that runs in the background.
     (CMCTCM)

     COSTED_FLAG = 'N'(Not Processed)  This means record is pending.
                   If it is NULL then the transaction has already been
                   processed.
                   If it is set to 'E' then it has erred for some
                   reason.
        

Q5. What type of issues will you see with the MTL_TRANSACTION_ACCOUNTS table?
        
A5. The details of the transactions are usually written into this table
    as Debits and Credits and from here ct can kick off Transfer to GL
    Navigation Path -> Inventory Responsibility -> Accounting Close Cycle
    -> General Ledger Transfers) as a concurrent request takes the
    transactions with a GL_BATCH_ID of -1 and passes them to the
    GL_INTERFACE for processing. When it passes the transactions to the
    GL_INTERFACE it is at this time that the batch is given it's proper
    batch number.

    Now there is a new functionality introduced. When transfer to GL is done,
    there is a column called GROUP_ID which gets populated in gl_interface
    table with gl_batch_id. GL BATCH identifier is used in the GROUP ID field,
    only the one batch which is in error would be prevented from being imported
    and posted. Also, adding a GROUP ID would allow for a more definitive
    level of control not only across locations but within a location.

    Patches which gives this functionality:
    REL 10.7: 853450
    REL 11.0: 1114961
   

Q6.   Where do you find Pending/Errored Inventory Transactions?

A6.1. Navigate to Inventory Responsibility -> Accounting Close Cycle ->
      Inventory Accounting Periods
      -- Select Pending Button related to an OPEN period.
      -- In the top region -- Resolution Required
      -- If it show pending records, These must be fixed prior to
         trying to close the period.
      -- form. only shows a count of pending records against the period.

A6.2. Navigate Inventory Responsibility -> Transactions -> Transaction
      Open Interface
      -- This form. queries the MTL_TRANSACTIONS_INTERFACE table
      -- Records can be resubmitted from this form.
         
A6.3. Navigate Inventory Responsibility -> Transactions ->
      Pending Transactions
      -- This form. queries the MTL_MATERIAL_TRANSACTIONS_TEMP table
      -- Records can only be resubmitted via the form.
      -- Records must be processed out of this form. in order to close
         inventory accounting periods.
      
A6.4. Navigate Inventory Responsibility -> Transactions ->
      Material Transactions
      -- Records can be viewed from this form. to know whether the
         transactions has costed flag as 'YES' or 'No', also other
         related columns under Alternate Region -> 'Reason/Reference'.
      -- We re-submit the records only through SQL.


Q7.  Where do you find Pending/Errored WIP Move and WIP Resource Transactions?

A8.1. Navigate Work in Process Responsibility -> Move Transactions ->
      Pending Move Transactions
      -- This form. queries the WIP_MOVE_TXN_INTERFACE table
      -- Records can be updated, deleted, and resubmitted via the form.

A8.2. Navigate Work in Process Responsibility -> Resource Transactions ->
      Pending Resource Transactions
      -- This form. queries the WIP_COST_TXN_INTERFACE table
      -- Records can be updated, deleted, and resubmitted via the form.


Q8.  What are the major tables involved in Material Movement for Inventory and
     WIP Material, Move, and Resource Transactions?

A8.  See below:

     MTL_TRANSACTIONS_INTERFACE
        -- INCTCM is the Transaction Manager for this Table.

     MTL_MATERIAL_TRANSACTIONS_TEMP
        -- INCTCM is the Transaction Manager for this Table

     MTL_MATERIAL_TRANSACTIONS
        -- CMCTCM is the Cost Manager for the records to be costed in this
            table.
        -- CMCCCM is the Cost Collection Manager for the records to be
           imported to Project Mfg.

     WIP_MOVE_TXN_INTERFACE
        -- Wip Move Transaction Worker processes records in this table
           (WICTCM)
     WIP_MOVE_TRANSACTIONS
        -- Must be viewed and resubmitted via SQL with the scripts below

     WIP_COST_TXN_INTERFACE
-- Resource Cost Worker processes records in this table.


Q9.  How do you resolve Pending Material Transactions?

A9.  Resolving Pending Material Transactions is a process of determining
     and fixing what is preventing a record from being processed through
     the MTL_TRANSACTIONS_INTERFACE table. Details of pending transactions
     can be viewed through the application by navigating to the Transaction
     Open Interface form.
        
     Using the Transaction Open Interface window, you can view, edit,
     correct and resubmit transactions received through the open interface.
     There are five selections in the alternate region list of values: Error,
     Location, Source, Intransit and Other. These provide detailed information
     to help you resolve pending transactions. In the Error alternate region:

       a. The error code describes the error on the last attempt to process
          the line item.
       b. The error explanation gives a reason for the error.
       c. The process flag indicates whether the row has been processed by
          the concurrent manager.
          The process flag status codes are:

               1 = Pending
               2 = Running
               3 = Error

     Resubmitting Transactions for Processing:

     In the Transaction Open Interface window, check the Resubmit [] box
     next to the transaction you want to resubmit or choose Resubmit All
     from the Special menu. If you have many transactions  to resubmit,
     use the Resubmit All button to select all transactions for processing
     and then selectively deselect individual transactions you do not want
     to resubmit. Save your work to submit the transactions for processing.


Q10. How do you resolve Pending WIP Cost Transactions?
      
A10. Resolving Pending WIP Cost transactions is a process of determining
     and fixing what is preventing a record from being processed through
     the WIP_COST_TXN_INTERFACE table Pending transactions can be viewed
     through the application by navigating to the WIP Responsibility ->
     pending resource transaction form.

     In the Pending Resource Transaction window you can view, update,
     delete, and resubmit resource transactions that have failed validation
     and remain in the WIP_COST_TXN_INTERFACE table. You can also resubmit
     transactions whose concurrent process has failed and have a processing
     phase of Complete and process status of Error. There are eight
     selections in the alternative region list of values: Processing,
     Source, Concurrent Request, Job or Schedule Name, Operation, Resource,
     Transaction and Comments.

     Error transactions will have the Transaction ID and Group ID populated
     and the Status will be error. Pending transactions will have the
     Transaction Id and Group ID fields blank and the Status will be
     pending.

     To view error details for failed resource transactions, select the
     erred transaction and click on the [Error] button. The Pending
     Resource Transaction error window appears. Column indicates the
     name of the column in the resource transaction interface table
     (WIP_COST_TXN_INTERFACE)that failed validation. Message indicates
     why the transaction failed.

     To resubmit failed resource transactions:

            In the Pending Resource Transactions widow either check the
            transaction's Resubmit check box to resubmit one record or
            choose select All for Resubmit from the Special Menu then
            save your work.


Q11. When attempting to load legacy data into the MTL_TRANSACTIONS_INTERFACE
     table, into what column is the PO number input?

A11. There is not a direct column for PO number.In the Oracle Manufacturing
     Implementation Manual, locate the section on Open Transactions
     Interfaces.  This section describes the transaction type id's and
     there is not a PO number. Refer to the column of transaction_reference
     and this column will allow the po number. The column is optional and
     shows up on reports and transaction displays. Since a column for the
     PO number does not directly exist,a transaction_reference field can
     be used to input this information.
     In Release 10.7, we do a Miscellaneous Receipt (PO Receipts are not
     supported).
     In Release 11, Purchasing Module has a Receiving Interface and
     we do not see any data coming to this table anymore.

     Customers that currently have release 10.7 and do not have this
     functionality will need to upgrade to release 11.0 or higher,
     which contains the production version of this functionality.


Q12. Why is the distribution account ID required in MTL_TRANSACTIONS_INTERFACE?

A12. All issues and receipts of asset items to an asset subinventory and
     sales order shipments require the distribution account id that
     originates from GL code combinations. This account id is required to
     create a complete audit trail of the transaction.

References:
http://kishorecboracleapps.blogspot.com/2013/03/oracle-inventory-transactions-and-cost.html

ITEM ORGANIZATION ASSIGNMENT

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

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;