TECHNICAL DATA FLOW IN ORDER TO CASH

STEP 1:

Enter order received from customer in Order Management (Sales Order)
Also enter the line level information of items ordered for. On saving the information, the data will be inserted in OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL Order_number will be automatically generated, Header and line Flow_status_code = ‘ENTERED’

OE_ORDER_HEADERS_ALL

select header_id,
order_number,
request_date,
transactional_curr_code,
shipping_method_code,
ship_from_org_id,
ship_to_org_id,
org_id,
flow_status_code,
salesrep_id,
booked_date
from OE_ORDER_HEADERS_ALL
where flow_status_code = 'CLOSED'
and order_number = '56706'

OE_ORDER_LINES_ALL

select header_id,
        line_id,
        line_number,
        ordered_item,
        inventory_item_id ,
        request_date,
        promise_date,
        schedule_ship_date,
        pricing_quantity,
        ordered_quantity,
        cancelled_quantity,
        shipped_quantity,
        invoiced_quantity,
        payment_term_id,
        visible_demand_flag,
        actual_shipment_date,
        unit_selling_price,
        tax_value,
        cancelled_flag,
        flow_status_code
from OE_ORDER_LINES_ALL
where header_id = 94256

STEP 2:

Book the order. Header Flow_status_code = ‘BOOKED’
Line Flow_status_code = ‘AWAITING SHIPPING’
The data is captured in WSH_DELIVERY_DETAILS. 
WSH_DLIVERY_ASSIGNMENTS is the intermediate table between WSH_DELIVERY_DETAIL and WSH_NEW_DELIVERIES.

WSH_DELIVERY_DETAILS
select delivery_detail_id,
        source_header_id,
        source_line_id,
        source_code,
        customer_id,
        inventory_item_id,
        item_description,
        ship_from_location_id,
        ship_to_location_id,
        move_order_line_id,
        requested_quantity,
        shipped_quantity,
        subinventory,
        released_status,
        ship_method_code,
        carrier_id,
        net_weight,
        unit_weight,
        unit_volume,
        unit_price,
        inspection_flag,
        source_header_number,
        batch_id
from WSH_DELIVERY_DETAILS
where source_header_id = 94256


WSH_DELIVERY_ASSIGNMENTS

select delivery_detail_id ,
        delivery_id,
        parent_delivery_detail_id,
        creation_date
from WSH_DELIVERY_ASSIGNMENTS
where delivery_detail_id in (216151, 216152, 216153) -- from above query


WSH_NEW_DELIVERIES

select delivery_id,
        name,
        status_code,
        customer_id ,
        gross_weight,
        net_weight,
        weight_uom_code,
        confirm_date,
        delivery_type,
        source_header_id,
        hash_string
from WSH_NEW_DELIVERIES 
where delivery_id in (65881, 65882) -- from above query


STEP 3:
You can enter, view and update the Sales order information using Sales Order window.
Release the sales Order 
Once the order is released, the data is updated in WSH_DELIVERY_DETAILS.The released_status becomes ‘Y’ and MOVE ORDER is generated in MTL_TXN_REQUEST_HEADERS and LINES. The move_order_line_id is also populated in WSH_DELIVERY_DETAILS

STEP 4:
Ship Confirm will update the data in WSH_DELIVERY_DETAILS. Released_status = ‘C’ and Shipped_quantity gets updated.
If you choose Tools >> Workflow Status then you will see that the workflow activity is in Deferred stage.
The concurrent program Workflow Background Engine needs to be executed to proceed further. 

MTL_TXN_REQUEST_LINES
select line_id move_order_line_id,
        header_id move_order_header_id,
        line_number,
        inventory_item_id,
        organization_id,
        quantity_delivered,
        line_status,
        txn_source_id,
        transaction_type_id,
        pick_slip_date
from MTL_TXN_REQUEST_LINES
where line_id in (174779, 174780, 174781)


MTL_TXN_REQUEST_HEADERS
select header_id move_order_header_id,
        request_number move_order_number,
        move_order_type,
        organization_id,
        header_status move_order_status
from MTL_TXN_REQUEST_HEADERS
where header_id in (55272, 55273)


MTL_MATERIAL_TRANSACTIONS
select transaction_id,
        inventory_item_id,
        organization_id,
        subinventory_code,
        transaction_type_id,
        transaction_action_id,
        transaction_quantity,
        transaction_uom,
        transaction_date,
        distribution_account_id,
        invoiced_flag,
        shipment_number delvery_name
from MTL_MATERIAL_TRANSACTIONS
where shipment_number in ('65881', '65882')

Workflow Background Process executes Autoinvoice master, Autoinvoice Import Program.
The date is first transferred from Shipping to RA_INTERFACE_LINES_ALL.
Then the Autoinvoice Import program validates the records and populates the base table in Account Receivable. RA_CUSTOMER_TXN_ALL , RA_CUSTOMER_TXN_LINES_ALL

RA_CUSTOMER_TRX_ALL

select interface_header_attribute1,
        customer_trx_id,
        trx_number,
        cust_trx_type_id,
        complete_flag,
        ship_date_actual
from RA_CUSTOMER_TRX_ALL
where interface_header_attribute1 = '56706'

Format Customization in Oracle Payments using Extensibility Utility Package

From Oracle Accounts Payable, user submits the invoices to Oracle Payments as a Payment Process Request. Oracle Payments uses the invoice information submitted in the Payment Request to create Documents Payable and then groups them into Payments and Payment Instructions for processing payments. This processed information is then recorded in the database tables. The processed payment information is retrieved by Oracle Payments database views to generate the XML extract. The generated XML extract is used in conjunction with the RTF/ETEXT template by Business Intelligence Publisher to generate output.

Oracle Payments provides the IBY_FD_EXTRACT_EXT_PUB extensibility package to construct custom XML element structure that can be added to the payment XML extract generated by Oracle Payments. The package specification and body definition can be found in files ibyfdxes.pls and ibyfdxeb.pls respectively. These files are located in the $IBY_TOP/patch/115/sql directory. 

The package allows custom elements to be created at following levels.

• Instruction
• Payment
• Document Payable
• Document Payable Line
• Payment Process Request

You cannot customize the package specification, but package body contains stubbed functions that you can customize. 

The five functions are as follows:

FUNCTION Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at instruction level and run only once for the instruction.

FUNCTION Get_Pmt_Ext_Agg(p_payment_id IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at payment level and run once for each payment in the instruction.

FUNCTION Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at document payable level and run once for each document payable in the instruction.

FUNCTION Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at document payable line level and run once for each document payable line in the instruction.

FUNCTION Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER) RETURN XMLTYPE

This function allows XML element to be introduced at document payable level and run once for each payment process request. 

First determine which function within IBY_FD_EXTRACT_EXT_PUB should be modified. Then write a PL/SQL block similar to the structure given below into the package body function definition, replacing the lines “BEGIN” and “RETURN NULL”, and retaining all other lines:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

<local_xml_variable>  XMLTYPE;     CURSOR <cursor_name> (<cursor_parameter_name> IN NUMBER) IS   SELECT XMLConcat(            XMLElement("Extend",              XMLElement("Name", '<xml_tag_name1>'),              XMLElement("Value", <xml_tag_value2>)),            XMLElement("Extend",              XMLElement("Name", '<xml_tag_name2>'),              XMLElement("Value", < xml_tag_value2>))          )    FROM <data_table_name>  WHERE <table_identifier_column> = <cursor_parameter_name>;   BEGIN     OPEN <cursor_name> (<function_parameter_name>);   FETCH <cursor_name> INTO <local_xml_variable>;   CLOSE <cursor_name>;     RETURN <local_xml_variable>;

A sample xml file with extended tags will look like:

Then modify the eText template using the new custom elements. Use the following syntax to select the appropriate Name/Value pair.

…/Extend[Name='<xml_tag_name1>’]/Value

You can use BI Publisher Template Viewer to view the final eText Outputs.

Reference Articles in MOS:

• Format Customization in Oracle Payments for Oracle Applications Release 12 [ID 787467.1]
• R12: Can Users Customize XML Extract? [ID 457539.1]
• R12 Oracle Payments Processing ‘How To’ documents [ID 579132.1]
• R12: How To Assign/Modify XML Publisher Payment Templates [ID 414336.1]