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