Thursday, October 19, 2017

OM INTERFACES ORDER IMPORT API

Order Import Interface (Sales Order Conversion)

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

Pre-requisites:
Order Type
Line Type
Items
Customers
Ship Method/ Freight Carrier
Sales Person
Sales Territories
Customer Order Holds
Sub Inventory/ Locations
On hand Quantity

Interface tables:
OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL
OE_ACTIONS_IFACE_ALL
OE_ORDER_CUST_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL
OE_PRICE_ATTS_IFACE_ALL

Base tables:
OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
Pricing tables: QP_PRICING_ATTRIBUTES

Concurrent Program:
Order Import

Validations:
Check for sold_to_org_id. 
If does not exist, create new customer by calling create_new_cust_info API.
Check for sales_rep_id. Should exist for a booked order.
Ordered_date should exist (header level)
Delivery_lead_time should exist (line level)
Earliest_acceptable_date should exist.
Freight_terms should exist

Notes:
During import of orders, shipping tables are not populated.
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.
Orders can be categorized based on their status:
1. Entered orders 2. Booked orders 3. Closed orders

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

STAGING TABLES
CREATE TABLE ST_OE_HEADERS_IFACE_ALL (
ORDER_SOURCE_ID NUMBER,
ORIG_SYS_DOCUMENT_REF VARCHAR2 (50),
ORG_ID NUMBER,
ORDERED_DATE DATE,
ORDER_TYPE_ID NUMBER,
PRICE_LIST_ID NUMBER,
TRANSACTIONAL_CURR_CODE VARCHAR2 (15),
SALESREP_ID NUMBER,
PAYMENT_TERM_ID NUMBER,
SOLD_TO_ORG_ID NUMBER,
SHIP_FROM_ORG_ID NUMBER,
SHIP_TO_ORG_ID NUMBER,
INVOICE_TO_ORG_ID NUMBER,
CUSTOMER_ID NUMBER,
CREATED_BY NUMBER NOT NULL,
CREATION_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL,
REQUEST_DATE DATE,
SOLD_FROM_ORG_ID NUMBER);
CREATE TABLE ST_OE_LINES_IFACE_ALL (
ORDER_SOURCE_ID NUMBER,
ORIG_SYS_DOCUMENT_REF VARCHAR2 (50),
ORIG_SYS_LINE_REF VARCHAR2 (50),
ORG_ID NUMBER,
LINE_TYPE_ID NUMBER,
INVENTORY_ITEM_ID NUMBER,
SCHEDULE_SHIP_DATE DATE,
ORDERED_QUANTITY NUMBER,
ORDER_QUANTITY_UOM VARCHAR2 (3),
SOLD_TO_ORG_ID NUMBER,
SHIP_FROM_ORG_ID NUMBER,
SHIP_TO_ORG_ID NUMBER,
INVOICE_TO_ORG_ID NUMBER,
PRICE_LIST_ID NUMBER,
UNIT_LIST_PRICE NUMBER,
UNIT_SELLING_PRICE NUMBER,
PAYMENT_TERM_ID NUMBER,
SALESREP_ID NUMBER,
CREATED_BY NUMBER NOT NULL,
CREATION_DATE DATE NOT NULL,
LAST_UPDATED_BY NUMBER NOT NULL,
LAST_UPDATE_DATE DATE NOT NULL,
REQUEST_DATE DATE,
SOLD_FROM_ORG_ID NUMBER,
LINES_NUMBER NUMBER);
create table ST_OE_ACTIONS_IFACE_ALL (ORDER_SOURCE_ID NUMBER,
ORIG_SYS_DOCUMENT_REF VARCHAR2(50),
ORG_ID NUMBER,OPERATION_CODE VARCHAR2(50));

SQL * LOADER
LOAD DATA INFILE *
INTO TABLE ST_OE_ACTIONS_IFACE_ALL
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(ORDER_SOURCE_ID,ORIG_SYS_DOCUMENT_REF,ORG_ID,OPERATION_CODE)
INTO TABLE ST_OE_HEADERS_IFACE_ALL
FIELDS TERMINATED BY ","
TRAILING NULLCOLS
(ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORG_ID,
ORDERED_DATE "TO_DATE(SYSDATE)",
ORDER_TYPE_ID,
PRICE_LIST_ID,
TRANSACTIONAL_CURR_CODE,
SALESREP_ID,
PAYMENT_TERM_ID,
SOLD_TO_ORG_ID,
SHIP_FROM_ORG_ID,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID,
CUSTOMER_ID,
CREATED_BY,
CREATION_DATE "TO_DATE(SYSDATE)",
LAST_UPDATED_BY,
LAST_UPDATE_DATE "TO_DATE(SYSDATE)",
REQUEST_DATE "TO_DATE(SYSDATE)",
SOLD_FROM_ORG_ID)
INTO TABLE ST_OE_LINES_IFACE_ALL
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
ORG_ID,
LINE_TYPE_ID,
INVENTORY_ITEM_ID,
SCHEDULE_SHIP_DATE "TO_DATE(SYSDATE)",
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
SOLD_TO_ORG_ID,
SHIP_FROM_ORG_ID,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID,
PRICE_LIST_ID,
UNIT_LIST_PRICE,
UNIT_SELLING_PRICE,
PAYMENT_TERM_ID,
SALESREP_ID,
CREATED_BY ,
CREATION_DATE "TO_DATE(SYSDATE)",
LAST_UPDATED_BY,
LAST_UPDATE_DATE "TO_DATE(SYSDATE)" ,
REQUEST_DATE "TO_DATE(SYSDATE)",
SOLD_FROM_ORG_ID,
LINES_NUMBER)
CREATE OR REPLACE PROCEDURE MOT_OM_CONV_PROC(ERRBUF OUT VARCHAR2,
RETCODE OUT vARCHAR2) IS
CURSOR CUR_HEADERs IS SELECT * FROM ST_OE_HEADERS_IFACE_ALL;
CURSOR CUR_LINES IS SELECT * FROM ST_OE_LINES_IFACE_ALL;
err_control EXCEPTION;
err_msg varchar2(250);
err_flag number;
ID NUMBER;
VERROR_FLAG OE_HEADERS_IFACE_ALL.ERROR_FLAG%TYPE;
VINTERFACE_STATUS OE_HEADERS_IFACE_ALL.INTERFACE_STATUS%TYPE;
VCREATION_DATE OE_HEADERS_IFACE_ALL.CREATION_DATE%TYPE;
VLAST_UPDATE_DATE OE_HEADERS_IFACE_ALL.LAST_UPDATE_DATE%TYPE;
VREQUEST_ID OE_HEADERS_IFACE_ALL.REQUEST_ID%TYPE;
VORIG_SYS_DOCUMENT_REF OE_HEADERS_IFACE_ALL.ORIG_SYS_DOCUMENT_REF%TYPE;
VORIG_SYS_LINE_REF OE_LINES_IFACE_ALL.ORIG_SYS_LINE_REF%TYPE;
VORDER_SOURCE_ID OE_ORDER_SOURCES.ORDER_SOURCE_ID%TYPE;
BEGIN
ID :=FND_GLOBAL.USER_ID;
VERROR_FLAG :=NULL;
VINTERFACE_STATUS :=NULL;
VCREATION_DATE :=SYSDATE;
VLAST_UPDATE_DATE :=SYSDATE;
VORIG_SYS_DOCUMENT_REF :='Order1';
VORIG_SYS_LINE_REF :='Line1';
ERR_MSG :='';
err_flag :=0;
VORDER_SOURCE_ID :=1047;
FOR V1 IN CUR_MOT_HEADERs
LOOP
BEGIN
IF order_valid(v1.ORDER_TYPE,v1.ORDER_TYPE_ID,'ORDER')='ERROR' THEN
err_msg:='Order_type or order_type_id must be valid';
err_flag:=1;
end if ;
IF price_item_validate(V1.PRICE_LIST_ID,NULL,'H')='ERROR' THEN
IF err_flag=1 then
err_msg:=err_msg||'&'||'Price list must be valid';
else
err_msg:='Price list must be valid';
err_flag:=1;
end if ;
end if;
IF payment_term_validate(V1.PAYMENT_TERM_id)='ERROR' THEN
if err_flag=1 then
err_msg:=err_msg||'&'||'Invalid payment term';
else
err_msg:='Invalid payment term';
err_flag:=1;
end if ;
end if ;
IF validate_customer(V1.CUSTOMER_NAME,v1.CUSTOMER_ID,v1.SOLD_TO_ORG_ID)='ERROR' then
IF err_flag=1 then
err_msg:=err_msg||'&'||' iNVALID CUSTOMER or sold to org id ';
else
err_msg:=' iNVALID CUSTOMER or sold to org id ';
err_flag:=1;
end if ;
end if;
if invoice_to_ship_to(v1.SHIP_TO_ORG_ID,v1.invoice_to_org_id)='ERROR' THEN
IF err_flag=1 then
err_msg:=err_msg||'&'||'INVALID SHIP TO OR INVOICE TO';
else
err_msg:='INVALID SHIP TO OR INVOICE TO';
err_flag:=1;
end if ;
end if;
-- raise exception --
IF err_flag=1 then -- RAISE ERROR MSG --
dbms_output.put_line('raising error');
raise err_control;
ELSE -- INSERT INTO INTERFCAE TABLES TABLE --
INSERT INTO OE_HEADERS_IFACE_ALL
(
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORDER_SOURCE,
ORDERED_DATE,
ORDER_TYPE,
ORDER_TYPE_ID,
PRICE_LIST_ID,
SALESREP_ID,
PAYMENT_TERM_id,
CUSTOMER_NAME,
CUSTOMER_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
REQUEST_ID,
OPERATION_CODE,
ERROR_FLAG,
INTERFACE_STATUS,
SOLD_TO_ORG_ID,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID
)
VALUES
(
VORDER_SOURCE_ID,
VORIG_SYS_DOCUMENT_REF,
v1.ORDER_SOURCE,
v1.ORDERED_DATE,
v1.ORDER_TYPE,
v1.ORDER_TYPE_ID,
v1.PRICE_LIST_ID,
v1.SALESREP_ID,
v1.PAYMENT_TERM_id,
v1.CUSTOMER_NAME,
v1.CUSTOMER_ID,
ID,
VCREATION_DATE,
ID,
vLAST_UPDATE_DATE,
v1.REQUEST_ID,
v1.OPERATION_CODE,
VERROR_FLAG,
VINTERFACE_STATUS,
v1.SOLD_TO_ORG_ID,
v1.SHIP_TO_ORG_ID,
v1.INVOICE_TO_ORG_ID
);
INSERT INTO OE_ACTIONS_IFACE_ALL
(
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
OPERATION_CODE
)
VALUES
(
VORDER_SOURCE_ID,
VORIG_SYS_DOCUMENT_REF,
'BOOK_ORDER'
);
END IF ;
EXCEPTION
WHEN err_control THEN
update MOT_stage_HEADER set error_msg=err_msg;
WHEN NO_DATA_FOUND THEN
EXIT;
WHEN OTHERS THEN
ERR_MSG:=SUBSTR(SQLERRM,1,25);
UPDATE MOT_stage_HEADER set error_msg=err_msg;
END;
END LOOP ;
--- end of header processing ------------------
err_flag:=0;
FOR V2 in CUR_MOT_LINES
LOOP
begin
IF order_valid(v2.LINE_type,v2.LINE_TYPE_ID,'LINE')='ERROR' THEN
err_msg:='Order_type or order_type_id must be valid';
err_flag:=1;
END IF ;
dbms_output.put_line('price_item_validate('||V2.PRICE_LIST_ID||','||v2.inventory_item_id||',L');
IF price_item_validate(V2.PRICE_LIST_ID,v2.inventory_item_id,'L')='ERROR' THEN
IF err_flag=1 then
err_msg:=err_msg||'&'||'Price list must be valid';
else
err_msg:='Price list must be valid';
err_flag:=1;
END IF ;
END IF ;
IF payment_term_validate(V2.PAYMENT_TERM_id)='ERROR' THEN
IF err_flag=1 then
err_msg:=err_msg||'&'||'Invalid payment term';
ELSE
err_msg:='Invalid payment term';
err_flag:=1;
end if ;
end if ;
IF invoice_to_ship_to(v2.SHIP_TO_ORG_ID,v2.invoice_to_org_id)='ERROR' THEN
IF err_flag =1 then
err_msg:=err_msg||'&'||'INVALID SHIP TO OR INVOICE TO';
else
err_msg:='INVALID SHIP TO OR INVOICE TO';
err_flag:=1;
end if ;
end if;
IF uom_validate(V2.order_quantity_uom)='ERROR' THEN
IF err_flag =1 then
err_msg:=err_msg||'&'||'INVALID UNIT OF MEASURE';
else
err_msg:='INVALID UNIT OF MEASURE';
err_flag:=1;
end if ;
end if;
-- raise exception --
IF err_flag=1 then -- RAISE ERROR FLAG --
raise err_control;
ELSE -- INSERT TO INTERFACE TABLES --
INSERT INTO OE_LINES_IFACE_ALL
(
ORDER_SOURCE_ID,
ORIG_SYS_DOCUMENT_REF,
ORIG_SYS_LINE_REF,
LINE_TYPE,
LINE_TYPE_ID,
INVENTORY_ITEM_ID,
PAYMENT_TERM_id,
PRICE_LIST_ID,
ORDERED_QUANTITY,
ORDER_QUANTITY_UOM,
REQUEST_DATE,
SALESREP_ID,
UNIT_LIST_PRICE,
UNIT_SELLING_PRICE,
CALCULATE_PRICE_FLAG,
SHIP_TO_ORG_ID,
INVOICE_TO_ORG_ID,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
OPERATION_CODE,
ERROR_FLAG,
INTERFACE_STATUS,
REQUEST_ID
)
VALUES
(
V2.ORDER_SOURCE_ID,
VORIG_SYS_DOCUMENT_REF,
VORIG_SYS_LINE_REF,
V2.LINE_TYPE,
V2.LINE_TYPE_ID,
V2.INVENTORY_ITEM_ID,
V2.PAYMENT_TERM_id,
V2.PRICE_LIST_ID,
V2.ORDERED_QUANTITY,
V2.ORDER_QUANTITY_UOM,
V2.REQUEST_DATE,
V2.SALESREP_ID,
V2.UNIT_LIST_PRICE,
V2.UNIT_SELLING_PRICE,
V2.CALCULATE_PRICE_FLAG,
V2.SHIP_TO_ORG_ID,
V2.INVOICE_TO_ORG_ID,
ID,
VCREATION_DATE,
ID,
VLAST_UPDATE_DATE,
V2.OPERATION_CODE,
VERROR_FLAG,
VINTERFACE_STATUS,
V2.REQUEST_ID
);
END IF ;
EXCEPTION
WHEN err_control THEN
update MOT_stage_lines set error_msg=err_msg;
WHEN NO_DATA_FOUND THEN
EXIT;
WHEN OTHERS THEN
ERR_MSG:=SUBSTR(SQLERRM,1,25);
UPDATE MOT_stage_lines set error_msg=err_msg;
END;
END LOOP ;
END;
/

AP, AR, GL, INV, PA, FA, PO, TCA, Workflow - Interface and Base table in Oracle Apps R12

Main Interface and Base table in Oracle Apps R12



GL Module (General Ledger Module tables in Oracle Apps R12)
Interface Table
Base Table
gl_interface
gl_budget_interface
gl_je_batches
gl_je_headers
gl_je_lines
gl_je_sources
gl_je_categories
gl_sets_of_books
gl_daily_rates
gl_balances
gl_periods
gl_period_sets
gl_code_conbinations
AR Module (Account Receivable Module tables in Oracle Apps R12)
Interface Table
Base Table
ra_customers_interface_all  
ra_contact_phones_int_all
ra_customer_profiles_int_all
hz_parties hz_cust_accounts
hz_cust_acct_sites_all
hz_cust_sit_use_all
hz_party_sites
hz_locations
hz_party_site_uses
hz_customer_profiles
hz_organization_profiles
hz_person_profiles

ra_interface_lines_all
ra_interface_distributions_all
ra_interface_salescredits_all
ra_customer_trx_all
ra_customer_trx_lines_all
ra_cust_trx_line_gl_dist_all
ra_cust_trx_types_all
ar_payment_schedules_all
ra_batch_sources_All
ar_vat_tax_all
ra_terms ar_periods
ar_period_types
OM Module (Order Management Module tables in Oracle Apps R12)
Interface Table
Base Table
oe_headers_iface_all
oe_lines_iface_all
oe_actions_iface_all
oe_order_headers_all
oe_order_lines_all
oe_order_sources
oe_transaction_types_tl
oe_transaction_types_all
INV Module (Inventory Module tables in Oracle Apps R12)
Interface Table
Base Table
mtl_system_items_interface
mtl_item_categories_interface
mtl_item_revisions_interface
mtl_interface_errors
mtl_system_items_b
mtl_system_items_tl
mtl_item_locations
mtl_item_categories
mtl_item_revisions_b
mtl_parameters
hr_all_organizations_units
cst_item_costs
qp_list_headers
jft_rs_salesreps
Workflow Tables tables in Oracle Apps R12
Interface Table
Base Table

wf_items
wf_process_activities
wf_item_activity_statuses
wf_item_types_tl
PO Module (Purchase Order tables in Oracle Apps R12)
Interface Table
Base Table
PO REQUISITIONS
po_requistitions_interface_all
po_req_dist_interface_all

PURCHASE ORDERS
po_headers_interface
po_lines_interface
po_distributions_interface


PO VENDORS
po_vendors
po_vendor_sites_all
po_vendor_contacts

PO REQUISITIONS
po_requisition_headers_all
po_requisition_lines_all
po_req_distributions_all


PURCHASE ORDERS
po_headers_all
po_lines_all
po_line_locations_all
po_distributions_all
AP Module (Account Payables tables in Oracle Apps R12)
Interface Table
Base Table
ap_invoices_interface
ap_invoice_lines_interface
ap_invoices_all
ap_invoice_distributions_all
ap_payments_schedules_all
FA Module (Fixed Asset tables in Oracle Apps R12)
Interface Table
Base Table
fa_inv_interface
fa_production_interface
fa_mass_additions
fa_additions_b
fa_additions_tl
fa_books
fa_deprn_summary
fa_deprn_details
fa_deprn_history
fa_retirements
PA  Module (Project Accounting in Oracle Apps R12)
Interface Table
Base Table

pa_tasks
pa_expenditure_items_all
pa_expenditures_all
pa_transaction_constrols
pa_projects_all
pa_budget_versions
pa_resource_assigments
TCA Architecture (Trading Community Architecture tables in Oracle Apps R12)
Interface Table
Base Table

hz_parties hz_cust_accounts
hz_cust_acct_sites_all
hz_cust_sit_use_all
hz_party_sites
hz_locations
hz_party_site_uses
hz_customer_profiles
hz_organization_profiles
hz_person_profiles




“A GUIDE FOR ORACLE E-BUSINESS SUITE” –A complete Solution and Learning platform For Oracle Apps R12/11i, Training, India Localization, Technical, P2P, O2C, Drop-shipment, AR/AP Cycle, Implementation, Bug or error in Oracle Apps R12/11i, Up-gradation, Support, Alert, Personalization, Discoverer management, OracleApps R12/11i Module, Finance, SCM, MRP, ASCP, WIP, BOM, FA, AP, AR, OM, INV, AOL, MOAC, TCA structure, Project Accounting, CM, Functional,Oracle DBA, ERP, Techno-functional, OAF, Oracle Apps Interview Question and Error in Oracle apps R12/11i. For More Information Visit on www.OracleAppsGuide.com Or Subscribe your email-id on OracleAppsGuide

Oracle Fusion - Cost Lines and Expenditure Item link in Projects

SELECT   ccd.transaction_id,ex.expenditure_item_id,cacat.serial_number FROM fusion.CST_INV_TRANSACTIONS cit,   fusion.cst_cost_distribution_...