Wednesday, June 6, 2018

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 ACCOUNTSOURCE TABLE NAME
Autoinvoice ClearingSalesreps
Sites
Standard Lines
Transaction Types
Bills ReceivableTransaction Types
Sites
Factored Bills ReceivableRemittance Banks
Sites
Transaction Types
FreightSalesreps
Sites
Standard Lines
Transaction Types
ReceivableSalesreps
Sites
Transaction Types
Remitted Bills ReceivableRemittance Banks
Sites
Transaction Types
RevenueSalesreps
Sites
Standard Lines
Transaction Types
TaxSalesreps
Sites
Standard Lines
Taxes
Transaction Types
Unbilled ReceivableSalesreps
Sites
Standard Lines
Transaction Types
Unearned RevenueSalesreps
Sites
Standard Lines
Transaction Types
Unpaid Bills ReceivableSites
Transaction Types


Refrence # http://alloracleapps.com

Sunday, June 3, 2018

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.

Thursday, March 29, 2018

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