AR AutoAccounting Rules

AutoAccounting enables to create default accounts for revenue, receivable, freight, tax, unearned revenue, unbilled receivable, late charges, bills receivables accounts, and AutoInvoice clearing (suspense) accounts. Based on the setup the receivable account is defaulted on the Invoice line Distributions. It help as an account generator.

Accounts which can be setup to use AutoAccounting

• Autoinvoice Clearing – Formerly called Suspense Account,  is used to specify the clearing account for your imported transactions. This account holds any difference between the specified revenue amount and the selling price times the quantity for imported invoice lines. This account will be used if you enable Create Clearing, in the Autoinvoice Options tab for the batch source of your imported transactions.
• Bills Receivable – This account is used to record the Receivable amount for a completed or accepted Bills Receivable
• Factored Bills Receivable – This account is used to record the Receivable amount for Bills
  Receivable transactions remitted as Factored with Recourse
• Freight – This account will be used to record Freight amounts, it cannot be based on tax code.

Note: If you choose standard item as a source, you will not be able to import invoices with header level freight though Autoinvoice.

• Receivable – This account  is used to record the Receivable amounts for transactions, it cannot be based on your inventory item or tax code
• Remitted Bills Receivable – This account is used to record the Receivable amounts when remitting a Bills Receivable with Standard Remittance
• Revenue – This account is used to record the Revenue amounts, it cannot be based on tax code.
• Tax – This account is used to record the Tax amounts.
• Unbilled Receivable – This account is used for transactions which use the Bill in Arrears invoicing rule, it cannot be based on tax code.
• Unearned Revenue– This account is used for transactions which use the Bill in Advance invoicing rule, it cannot be based on tax code.
• Unpaid Bills Receivable– This account is used to record the Receivable amount when you mark a Bills Receivable as Unpaid.

Sources for AutoAccounting

Depending upon which of the above accounts you are setting up, the value for the source of each segment can come from various Table Sources as shown in the following table.

GL ACCOUNT	SOURCE TABLE NAME
Autoinvoice Clearing	Salesreps Sites Standard Lines Transaction Types
Bills Receivable	Transaction Types Sites
Factored Bills Receivable	Remittance Banks Sites Transaction Types
Freight	Salesreps Sites Standard Lines Transaction Types
Receivable	Salesreps Sites Transaction Types
Remitted Bills Receivable	Remittance Banks Sites Transaction Types
Revenue	Salesreps Sites Standard Lines Transaction Types
Tax	Salesreps Sites Standard Lines Taxes Transaction Types
Unbilled Receivable	Salesreps Sites Standard Lines Transaction Types
Unearned Revenue	Salesreps Sites Standard Lines Transaction Types
Unpaid Bills Receivable	Sites Transaction Types

Refrence # http://alloracleapps.com

Interface Trip Stop Functionality

There are typically four interface program get triggered at the time of ship confirm. They are 

• Interface Trip stop
• Packing Slip Report
• Bill of Lading
• Invoice

Interface Trip Stop is one of the main interface programs which can be triggered at the time of ship confirm or later as Interface Trip stop - SRS concurrent request.

Interface trip stop has two parts

• Order Management Interface (First part)
• Inventory Interface (Second part)

Order Management Interface part update the order management tables like OE_ORDER_LINES_All (Update Shipped_Quantity, Shipping_Quantity, Actual_Shipment_Date) and also WSH_DELIVERY_DETAILS (make Released_status, OE_INTERFACED_FLAG = ‘Y’) table.

The Inv_Interfaced_Flag remains = X (Pending) or N which will changes to Y after execution of second part

The second part i.e. Inventory Interface will be triggered only if the value of OE_INTERFACED_FLAG = ‘Y’ otherwise second part will not be triggered. Even if you try to run this from SRS, these lines will not be picked for processing. 

Inventory Interface part move data from MTL_TRANSACTIONS_INTERFACE to MTL_MATERIAL_TRANACTIONS and MTL_MATERIAL_TRANSACTIONS is updated with Sales Order Issue transaction. Also data is deleted from MTL_DEMAND, MTL_RESERVATIONS and WSH_NEW_DELIVERIES. Item quantity reduced from MTL_ONHAND_QUANTITIES and 

MTL_TRANSACTION_ACCOUNTS is updated with accounting information.

ENABLE TRACE FOR CONCURRENT PROGRAMS IN ORACLE APPS R12

Tracing the Concurrent program is very good option to Debug any issue while running the concurrent program.

Goal :
To enable the Trace for Concurrent Programs
To Debug the issues in Concurrent Programs or its dependent objects.

Solution :
1. Goto Concurrent Program Definition

Application Developer –> Concurrent –> Program –> check Enable Trace

Query your concurrent program and check the Enable Trace check box at the bottom of the screen.

2. Set the Profile Concurrent: Allow Debugging to YES.

3. Navigate to the Responsibility from where you are running the program.

4. Before Submitting the Concurrent Program in Submit Request screen there will be an option like Debug Options click on that button.

5. In the Debug Options select the SQL Trace and enable Trace with binds.

6. Submit the Concurrent Program and note down the Request id.

7.Trace file name will always be post fixed with oracle_process_id.To get the oracle_process_id use the below SQL
Select oracle_process_id from fnd_concurrent_requests where request_id= &Request_id;

8.And the Trace file path can be derived using the below SQL
Select * from v$parameter where name=’user_dump_dest’;

9. Get the trace file to your local machine.Understanding the raw trace is very complex so use TKPROF utility to make the Trace file readable.

10. Open the Command Prompt and run the below command
TKPROF < Trace File_Name.trc> <Output_File_Name.out> SORT=fchela

11.A new outfile will be generated with the name given in the above command.Analyse the Output file to know the answers for your problem.

Sql queries to find the log files by Request id

select req.oracle_process_id tracename, req.request_id, req.requested_by,usr.user_name, prog.user_concurrent_program_name, req.request_date, req.phase_code, req.status_code, req.logfile_name , req.outfile_name, dest.value as user_dump_dest from apps. fnd_concurrent_requests req, gv$parameter dest, apps.fnd_concurrent_programs_vl prog, apps.fnd_user usr

where  req. concurrent_program_id = prog.concurrent_program_id

and req.requested_by = usr.user_id

–and request_id like ‘6013239’

and dest.name= ‘user_dump_dest’

and request_id like & Request_id;

Sql query find the log files by username

select req.oracle_process_id tracename, req.request_id, req.requested_by,usr.user_name, prog.user_concurrent_program_name, req.request_date,  req.phase_code, req.status_code, req.logfile_name , req.outfile_name, dest.value as user_dump_dest 

from apps. fnd_concurrent_requests req, gv$parameter dest, apps.fnd_concurrent_programs_vl prog, apps.fnd_user usr

where  req. concurrent_program_id = prog.concurrent_program_id

and req.requested_by = usr.user_id

–and request_id like ‘6013239’

and dest.name= ‘user_dump_dest’

and usr.user_name like ‘Ramesh%G%’

order by request_date desc

Select oracle_process_id from fnd_concurrent_requests where request_id = &Request_id;

 

select * from apps. fnd_concurrent_requests req,gv$parameter dest , apps .fnd_concurrent_programs_vl prog where  req. concurrent_program_id = prog.concurrent_program_id and request_id like ‘601300%’

and dest.name= ‘user_dump_dest’

How to import Sales Order using Interface / API

rder Import Interface (Sales Order Conversion)

Order Import enables you to import Sales Orders into Oracle Applications instead of manually entering them.

Pre-requisites

1. Order Type

2. Line Type

3. Items

4. Customers

5. Ship Method/ Freight Carrier

6. Sales Person

7. Sales Territories

8. Customer Order Holds

9. Sub Inventory/ Locations

10. On hand Quantity

Interface tables

1. OE_HEADERS_IFACE_ALL

2. OE_LINES_IFACE_ALL

3. OE_ACTIONS_IFACE_ALL

4. OE_ORDER_CUST_IFACE_ALL

5. OE_PRICE_ADJS_IFACE_ALL

6. OE_PRICE_ATTS_IFACE_ALL

Base tables:

1. OE_ORDER_HEADERS_ALL

2. OE_ORDER_LINES_ALL

3. Pricing tables: QP_PRICING_ATTRIBUTES

Concurrent Program

1. Order Import

Validations

1. Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.

2. Check for sales_rep_id. Should exist for a booked order.

3. Ordered_date should exist (header level)

4. Delivery_lead_time should exist (line level)

5. Earliest_acceptable_date should exist.

6. Freight_terms should exist

Notes

1. During import of orders, shipping tables are not populated.

2. If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL has to be populated and the base tables are HZ_PARTIES, HZ_LOCATIONS.

3. Orders can be categorized based on their status: 1. Entered orders 2. Booked orders 3. Closed orders

4. Order Import API OE_ORDER_PUB.GET_ORDER and PROCESS_ORDER can also be used to import orders

Some important columns that need to populated in the OE_HEADERS_IFACE_ALL tables

1. ORIG_SYS_DOCUMENT_REF

2. ORDER_SOURCE

3. CONVERSION_RATE

4. ORG_ID

5. ORDER_TYPE_ID

6. PRICE_LIST

7. SOLD_FROM_ORG_ID

8. SOLD_TO_ORG_ID

9. SHIP_TO_ORG_ID

10. SHIP_FROM_ORG_ID

11. CUSTOMER_NAME

12. INVOICE_TO_ORG_ID

13. OPERATION_CODE

Some important columns that need to populated in the OE_LINES_IFACE_ALL tables

1. ORDER_SOURCE_ID

2. ORIG_SYS_DOCUMENT_REF

3. ORIG_SYS_LINE_REF

4. ORIG_SYS_SHIPMENT_REF

5. INVENTORY_ITEM_ID

6. LINK_TO_LINE_REF

7. REQUEST_DATE

8. DELIVERY_LEAD_TIME

9. DELIVERY_ID

10. ORDERED_QUANTITY

11. ORDER_QUANTITY_UOM

12. SHIPPING_QUANTITY

13. PRICING_QUANTITY

14. PRICING_QUANTITY_UOM

15. SOLD_FROM_ORG_ID

16. SOLD_TO_ORG_ID

17. INVOICE_TO_ ORG_ID

18. SHIP_TO_ORG_ID

19. PRICE_LIST_ID

20. PAYMENT_TERM_ID

Sample Script:

DECLARE

   l_api_version_number           NUMBER := 1;

   l_return_status                VARCHAR2 (2000);

   l_msg_count                    NUMBER;

   l_msg_data                     VARCHAR2 (2000);

   -- PARAMETERS

   l_debug_level                  NUMBER := 5;       -- OM DEBUG LEVEL (MAX 5)

   l_org                          NUMBER := 308;             -- OPERATING UNIT

   l_no_orders                    NUMBER := 1;                 -- NO OF ORDERS

   -- INPUT VARIABLES FOR PROCESS_ORDER API

   l_header_rec                   oe_order_pub.header_rec_type;

   l_line_tbl                     oe_order_pub.line_tbl_type;

   l_action_request_tbl           oe_order_pub.request_tbl_type;

   -- OUT VARIABLES FOR PROCESS_ORDER API

   l_header_rec_out               oe_order_pub.header_rec_type;

   l_header_val_rec_out           oe_order_pub.header_val_rec_type;

   l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;

   l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;

   l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;

   l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;

   l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;

   l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;

   l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;

   l_line_tbl_out                 oe_order_pub.line_tbl_type;

   l_line_val_tbl_out             oe_order_pub.line_val_tbl_type;

   l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;

   l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;

   l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;

   l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;

   l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;

   l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;

   l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;

   l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;

   l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;

   l_action_request_tbl_out       oe_order_pub.request_tbl_type;

   l_msg_index                    NUMBER;

   l_data                         VARCHAR2 (2000);

   l_loop_count                   NUMBER;

   l_debug_file                   VARCHAR2 (200);

BEGIN

   -- INITIALIZATION REQUIRED FOR R12

   mo_global.set_policy_context ('S', l_org);

   mo_global.init ('ONT');

   -- INITIALIZE DEBUG INFO

   IF (l_debug_level > 0)

   THEN

      l_debug_file := oe_debug_pub.set_debug_mode ('FILE');

      oe_debug_pub.initialize;

      oe_debug_pub.setdebuglevel (l_debug_level);

      oe_msg_pub.initialize;

   END IF;

   -- INITIALIZE ENVIRONMENT

   fnd_global.

    apps_initialize (user_id => 2083, resp_id => 21623, resp_appl_id => 660);

   -- INITIALIZE HEADER RECORD

   l_header_rec := oe_order_pub.g_miss_header_rec;

   -- POPULATE REQUIRED ATTRIBUTES

   l_header_rec.operation := oe_globals.g_opr_create;

   l_header_rec.transactional_curr_code := 'AUD';

   l_header_rec.pricing_date := SYSDATE;

   l_header_rec.cust_po_number := 'TSTPO30';

   l_header_rec.sold_to_org_id := 1006685;

   l_header_rec.price_list_id := 33019;

   l_header_rec.ordered_date := SYSDATE;

   l_header_rec.shipping_method_code := '000001_Toll IPEC_T_2T5DGRD';

   l_header_rec.sold_from_org_id := 308;

   l_header_rec.ship_from_org_id := 381;

   l_header_rec.ship_to_org_id := 2005460;

   l_header_rec.salesrep_id := 100000069;

   l_header_rec.flow_status_code := 'ENTERED';

   l_header_rec.order_type_id := 5389;

   -- REQUIRED HEADER DFF INFORMATIONS

   l_header_rec.attribute1 := 193;                          -- Entering Branch

   l_header_rec.attribute3 := 'Y';                    -- Indexation applicable

   l_header_rec.attribute5 := '2.5';        -- Indexation Tolerance percentage

   l_header_rec.attribute7 := 100000045;         -- Field Sales representative

   l_header_rec.attribute11 := '100';              -- Indexation Applicability

   -- INITIALIZE ACTION REQUEST RECORD

   l_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;

   -- INITIALIZE LINE RECORD

   l_line_tbl (1) := oe_order_pub.g_miss_line_rec;

   l_line_tbl (1).operation := oe_globals.g_opr_create; -- Mandatory Operation to Pass

   l_line_tbl (1).inventory_item_id := 102775;

   l_line_tbl (1).ordered_quantity := 1;

   l_line_tbl (1).ship_from_org_id := 381;

   l_line_tbl (1).subinventory := 'SELLABLE';

   -- REQUIRED LINE DFF INFORMATIONS

   l_line_tbl (1).attribute2 := '20.99998';                    -- Gross Margin

   l_line_tbl (1).attribute3 := '2.493288';                   -- Business Cost

   l_line_tbl (1).attribute10 := '1000';        -- Original Cust Requested Qty

   l_line_tbl (1).attribute11 := '662.772';                 -- Baseline Margin

   l_line_tbl (1).attribute16 := 'DBP';                     -- Buy Price Basis

   FOR i IN 1 .. l_no_orders

   LOOP                                                          -- BEGIN LOOP

      -- CALLTO PROCESS ORDER API

      oe_order_pub.

       process_order (

         p_org_id                   => l_org,

         p_operating_unit           => NULL,

         p_api_version_number       => l_api_version_number,

         p_header_rec               => l_header_rec,

         p_line_tbl                 => l_line_tbl,

         p_action_request_tbl       => l_action_request_tbl,

         -- OUT variables

         x_header_rec               => l_header_rec_out,

         x_header_val_rec           => l_header_val_rec_out,

         x_header_adj_tbl           => l_header_adj_tbl_out,

         x_header_adj_val_tbl       => l_header_adj_val_tbl_out,

         x_header_price_att_tbl     => l_header_price_att_tbl_out,

         x_header_adj_att_tbl       => l_header_adj_att_tbl_out,

         x_header_adj_assoc_tbl     => l_header_adj_assoc_tbl_out,

         x_header_scredit_tbl       => l_header_scredit_tbl_out,

         x_header_scredit_val_tbl   => l_header_scredit_val_tbl_out,

         x_line_tbl                 => l_line_tbl_out,

         x_line_val_tbl             => l_line_val_tbl_out,

         x_line_adj_tbl             => l_line_adj_tbl_out,

         x_line_adj_val_tbl         => l_line_adj_val_tbl_out,

         x_line_price_att_tbl       => l_line_price_att_tbl_out,

         x_line_adj_att_tbl         => l_line_adj_att_tbl_out,

         x_line_adj_assoc_tbl       => l_line_adj_assoc_tbl_out,

         x_line_scredit_tbl         => l_line_scredit_tbl_out,

         x_line_scredit_val_tbl     => l_line_scredit_val_tbl_out,

         x_lot_serial_tbl           => l_lot_serial_tbl_out,

         x_lot_serial_val_tbl       => l_lot_serial_val_tbl_out,

         x_action_request_tbl       => l_action_request_tbl_out,

         x_return_status            => l_return_status,

         x_msg_count                => l_msg_count,

         x_msg_data                 => l_msg_data);

      -- CHECK RETURN STATUS

      IF l_return_status = fnd_api.g_ret_sts_success

      THEN

         IF (l_debug_level > 0)

         THEN

            DBMS_OUTPUT.put_line ('Sales Order Successfully Created');

         END IF;

         COMMIT;

      ELSE

         IF (l_debug_level > 0)

         THEN

            DBMS_OUTPUT.put_line ('Failed to Create Sales Order');

         END IF;

         ROLLBACK;

      END IF;

   END LOOP;

   -- DISPLAY RETURN STATUS FLAGS

   IF (l_debug_level > 0)

   THEN

      DBMS_OUTPUT.

       put_line (

         'Process Order Return Status is: ========>' || l_return_status);

      DBMS_OUTPUT.

       put_line ('Process Order msg data is: ===========>' || l_msg_data);

      DBMS_OUTPUT.

       put_line ('Process Order Message Count is:=======>' || l_msg_count);

      DBMS_OUTPUT.

       put_line (

         'Sales Order Created is:===============>'

         || TO_CHAR (l_header_rec_out.order_number));

      DBMS_OUTPUT.

       put_line (

         'Booked Flag for the Sales Order is:======>'

         || l_header_rec_out.booked_flag);

      DBMS_OUTPUT.

       put_line (

         'Header_id for the Sales Order is:========>'

         || l_header_rec_out.header_id);

      DBMS_OUTPUT.

       put_line (

         'Flow_Status_Code For the Sales Order is=>:'

         || l_header_rec_out.flow_status_code);

   END IF;

   -- DISPLAY ERROR MSGS

   IF (l_debug_level > 0)

   THEN

      FOR i IN 1 .. l_msg_count

      LOOP

         oe_msg_pub.get (p_msg_index       => i,

                         p_encoded         => fnd_api.g_false,

                         p_data            => l_data,

                         p_msg_index_out   => l_msg_index);

         DBMS_OUTPUT.put_line ('message is:' || l_data);

         DBMS_OUTPUT.put_line ('message index is:' || l_msg_index);

      END LOOP;

   END IF;

   IF (l_debug_level > 0)

   THEN

      DBMS_OUTPUT.put_line ('Debug = ' || oe_debug_pub.g_debug);

      DBMS_OUTPUT.

       put_line ('Debug Level = ' || TO_CHAR (oe_debug_pub.g_debug_level));

      DBMS_OUTPUT.

       put_line (

         'Debug File =' || oe_debug_pub.g_dir || '/' || oe_debug_pub.g_file);

      oe_debug_pub.debug_off;

   END IF;

END;

Source: http://blog.csdn.net/pan_tian/article/details/8266466

http://oracletechnofunctional.webs.com/apps/blog/show/4689245-how-to-import-sales-order-using-interface-api