------------------------------------------- Item Cross Reference Interface --------------------------------------------
List Of Files
-------------
1. MD-70 (Hitachi_Conversion_Open_Sales_Order_CV060 - v1.0)
2. XXHCC_SO_STAGING.sql
--Script for creating staging table,SYNONYM and Sequence
3. XXHCC_FIN_CONV_ERROR.sql
--Script for creating Error table and SYNONYM
4. XXHCC_CONV_SO_LOAD.csv
-- Data File
5. XXHCC_CONV_SO_LOAD.ctl
--Program to load the data to staging table
6. XXHCC_OE_CONV_SALESORDER_PKG.pks
-- Package Specification and body
-- Package to validate and import data to Oracle
7. MD-120(Please refer Installation Requirements-12 in MD70 Doc)
-- Installation Steps
8. LDT
--HCUSOECNV.ldt -- LDT for staging table data load
--HCUSOEINTLOAD.ldt -- LDT for Interface Program
--HCUSOEINTLOAD_REG -- LDT for Interface Program to request group
--HCUSOECNV_REG -- LDT for Interface Program to request group
Program Name : ‘XXHCC Inventory Cross Reference Sql Loader Program’ , 'XXHCC Inventory Item Cross Reference Program'
Responsibility name : Inventory, Vision Operations(USA)
Functional Execution Steps:
1. Connect to server using FTP tool
2. Fill the relevant information in the data file(XXHCC_CONV_SO_LOAD.csv) and move to $XXHCC_TOP/bin directory
3. Connect to Oracle Application and navigate to SRS window and submit ‘HCUS Open Sales Order Load Staging Data’ Loader program. This will load data to stage table.
4. Records will be inserted into XXHCC_OE_ORDERS_IFACE_STG table with status as "N"
5. Run ‘HCUS Open Sales Orders Load' program in Validate mode to validate.
6. All the validated records will have their status = "L"
7. Once all the validations are successful user can run the ‘Order Import' program to loda the data into base tables.
6. The error records and successful records in the standard interface program will also be written in the Output file.
-- ---------------------------------------------------------------
-- This script creates XXHCC_OE_ORDERS_IFACE_STG table --
-- ---------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE XXHCC.XXHCC_OE_ORDERS_IFACE_STG';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
Create Table XXHCC.XXHCC_OE_ORDERS_IFACE_STG
(
SOURCE VARCHAR2(40) ,
ORDER_SOURCE VARCHAR2(240) ,
ORIG_SYS_DOCUMENT_REF VARCHAR2(50) ,
ORG_NAME VARCHAR2(50) ,
CUSTOMER_PO_NUMBER VARCHAR2(50) ,
ORDERED_DATE DATE ,
ORDER_TYPE VARCHAR2(40) ,
OMNI_PRICE Varchar2(80) ,
TRANSACTIONAL_CURR VARCHAR2(80) ,
CONVERSION_RATE_DATE DATE ,
CONVERSION_TYPE_CODE VARCHAR2(40) ,
ORIG_SYS_CUSTOMER_NAME VARCHAR2(240) ,
ORIG_SHIP_ADDRESS_REF VARCHAR2(50) ,
TAX_EXEMPT_FLAG VARCHAR2(1) ,
FOB VARCHAR2(240) ,
SHIP_FROM_ORG VARCHAR2(240) ,
LINE_NUMBER NUMBER ,
INVENTORY_ITEM VARCHAR2(240) ,
ITEM_TYPE_CODE VARCHAR2(40) ,
ORDERED_QUANTITY NUMBER ,
ORDERED_QUANTITY_UOM VARCHAR2(40) ,
REQUEST_DATE DATE ,
PROMISE_DATE DATE ,
UNIT_SELLING_PRICE NUMBER ,
ORIG_SYS_LINE_DOCUMENT_REF NUMBER ,
TAX_EXEMPT_REASON VARCHAR2(240) ,
ORIG_BILL_ADDRESS_REF VARCHAR2(50) ,
SOLD_TO_ORG VARCHAR2(240) ,
SOLD_FROM_ORG VARCHAR2(240) ,
SHIP_TO_ORG VARCHAR2(240) ,
INVOICE_TO_ORG VARCHAR2(240) ,
BOOKED_FLAG VARCHAR2(1) ,
ORDER_CATEGORY VARCHAR2(240) ,
CALCULATE_PRICE_FLAG VARCHAR2(1) ,
SALESREP VARCHAR2(240) ,
TAX_CODE VARCHAR2(50) ,
ORG_ID NUMBER ,
ORDER_SOURCE_ID NUMBER ,
ORDER_TYPE_ID NUMBER ,
LINE_TYPE_ID NUMBER ,
PRICE_LIST_ID NUMBER ,
SALESREP_ID NUMBER ,
PAYMENT_TERM_ID NUMBER ,
SOLD_TO_ORG_ID NUMBER ,
SOLD_FROM_ORG_ID NUMBER ,
SHIP_FROM_ORG_ID NUMBER ,
SHIP_TO_ORG_ID NUMBER ,
INVOICE_TO_ORG_ID NUMBER ,
LAST_UPDATED_BY NUMBER ,
LAST_UPDATE_DATE DATE ,
CREATED_BY NUMBER ,
CREATION_DATE DATE ,
XXHCC_TRANSACTION_ID NUMBER ,
BATCH_NAME VARCHAR2(80) ,
LOAD_STATUS VARCHAR2(1)
);
BEGIN
EXECUTE IMMEDIATE 'DROP SYNONYM APPS.XXHCC_OE_ORDERS_IFACE_STG';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE SYNONYM APPS.XXHCC_OE_ORDERS_IFACE_STG FOR XXHCC.XXHCC_OE_ORDERS_IFACE_STG;
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE APPS.XXHCC_OE_ORDERS_IFACE_SEQ';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE SEQUENCE APPS.XXHCC_OE_ORDERS_IFACE_SEQ
MINVALUE 1 MAXVALUE 999999999999999999999999999
INCREMENT BY 1
START WITH 401
CACHE 20 NOORDER NOCYCLE;
Control file
---------------------
OPTIONS (BINDSIZE =125653,SKIP=1)
LOAD DATA
INFILE '$XXHCC_TOP/bin/XXHCC_CONV_SO_LOAD.csv'
TRUNCATE
INTO table APPS.XXHCC_OE_ORDERS_IFACE_STG
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
order_source "trim(:order_source)",
orig_sys_document_ref "trim(:orig_sys_document_ref)",
org_name "trim(:org_name)",
customer_po_number "trim(:customer_po_number)",
ordered_date "TO_DATE(TRIM(:ordered_date),'DD/MM/YYYY')",
order_type "trim(:order_type)",
OMNI_PRICE "trim(:OMNI_PRICE)",
transactional_curr "trim(:transactional_curr)",
orig_sys_customer_name "trim(:orig_sys_customer_name)",
orig_ship_address_ref "trim(:orig_ship_address_ref)",
tax_exempt_flag "trim(:tax_exempt_flag)",
fob "trim(:fob)",
ship_from_org "trim(nvl(:ship_from_org,'HCC'))",
line_number "trim(:line_number)",
inventory_item "trim(:inventory_item)",
item_type_code "trim(UPPER(:item_type_code))",
Ordered_quantity "trim(:Ordered_quantity)",
Ordered_quantity_uom "trim(:Ordered_quantity_uom)",
request_date "TO_DATE(TRIM(:request_date),'DD/MM/YYYY')",
promise_date "TO_DATE(TRIM(:promise_date),'DD/MM/YYYY')",
unit_selling_price "trim(:unit_selling_price)",
orig_sys_line_document_ref "trim(:orig_sys_line_document_ref)",
tax_exempt_reason "trim(:tax_exempt_reason)",
orig_bill_address_ref "trim(:orig_bill_address_ref)",
sold_to_org "trim(:sold_to_org)",
sold_from_org "trim(:sold_from_org)",
ship_TO_org "trim(:ship_TO_org)",
invoice_TO_org "trim(:invoice_TO_org)",
booked_flag "trim(:booked_flag)",
order_category "trim(:order_category)",
calculate_price_flag "trim(:calculate_price_flag)",
salesrep "trim(:salesrep)",
tax_code "trim(:tax_code)",
LAST_UPDATED_BY "FND_GLOBAL.USER_ID",
LAST_UPDATE_DATE SYSDATE,
CREATED_BY "FND_GLOBAL.USER_ID",
CREATION_DATE SYSDATE,
XXhcc_TRANSACTION_ID "APPS.XXHCC_oe_orders_iface_seq.NEXTVAL",
batch_name "SYSDATE||'-'||1",
LOAD_STATUS CONSTANT "N"
)
-- ---------------------------------------------------------------
-- This script creates XXHCC_FIN_CONV_ERROR table --
-- ---------------------------------------------------------------
BEGIN
--null;
EXECUTE IMMEDIATE 'DROP TABLE XXHCC.XXHCC_FIN_CONV_ERROR';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE TABLE XXHCC.XXHCC_FIN_CONV_ERROR
( "INTERFACE_ERROR_ID" NUMBER NOT NULL ENABLE,
"PACKAGE_NAME" VARCHAR2(30 BYTE),
"PROCEDURE_NAME" VARCHAR2(30 BYTE),
"INTERFACE_TABLE_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"XXHCC_HDR_TRANSACTION_ID" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"XXHCC_TRANSACTION_ID" VARCHAR2(100 BYTE),
"XXHCC_BATCH_NAME" VARCHAR2(80 BYTE),
"XXHCC_SOURCE" VARCHAR2(30 BYTE),
"COLUMN_NAME" VARCHAR2(250 BYTE),
"COLUMN_VALUE" VARCHAR2(250 BYTE),
"ERROR_CODE" VARCHAR2(250 BYTE),
"ERROR_MESSAGE" VARCHAR2(1000 BYTE),
"ERROR_MESSAGE_DISP" VARCHAR2(250 BYTE),
"PROGRESS" VARCHAR2(10 BYTE),
"REQUEST_ID" NUMBER(15,0),
"CREATION_DATE" DATE NOT NULL ENABLE,
"CREATED_BY" NUMBER(15,0) NOT NULL ENABLE,
"LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
"LAST_UPDATED_BY" NUMBER(15,0) NOT NULL ENABLE
) ;
BEGIN
EXECUTE IMMEDIATE 'DROP SYNONYM APPS.XXHCC_FIN_CONV_ERROR';
--null;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE OR REPLACE SYNONYM APPS.XXHCC_FIN_CONV_ERROR FOR XXHCC.XXHCC_FIN_CONV_ERROR;
CREATE OR REPLACE PACKAGE XXHCC_OE_CONV_SALESORDER_PKG
/*===============================================================================================================*/
--| |
--|Project : |
--|Task : |
--|Application : Oracle Sales Orders |
--|Script : XXHCC_OE_CONV_SALESORDER_PKG.pks |
--|Created By : Reuse Team |
--|Date Created : 10-SEP-2012 |
--|Description : Creates the Package : XXHCC_OE_CONV_SALESORDER_PKG and Run this script from the APPS schema. |
--|Parameters : |
--|Inputs : |
--|Outputs : |
--+============================================================================================== |
--|Modification History |
--|Person Date Version Comments |
--+=========== ====== ============= ====================================== |
--|Erry Swamy 10-Sep-2012 1.0 |
--+=================================================================================================================
IS
c_package_name VARCHAR2 (50);
c_procedure_name VARCHAR2 (50);
-- columns for error trapping
c_error_code VARCHAR2 (1000);
c_error_message VARCHAR2 (1000);
c_progress VARCHAR2 (10) := NULL;
/*=======================================================================
GLOBAL variables and programs for:
dbms or FND message logging
=======================================================================*/
c_log_dest VARCHAR2 (30) := --'DBMS_OUTPUT';
'CONCURRENT_PROGRAM';
-- possible values: DBMS_OUTPUT, CONCURRENT_PROGRAM
c_debug_level NUMBER := 0;
c_progress VARCHAR2 (10) := NULL;
TYPE g_error_rec IS RECORD (
c_package_name VARCHAR2 (30),
c_procedure_name VARCHAR2 (30),
c_interface_table_name VARCHAR2 (100),
c_XXHCC_hdr_transaction_id VARCHAR2 (100),
c_XXHCC_transaction_id VARCHAR2 (100),
c_XXHCC_batch_name VARCHAR2 (80),
c_XXHCC_source VARCHAR2 (30),
c_column_name VARCHAR2 (250),
c_column_value VARCHAR2 (250)
);
PROCEDURE xx_load_main (p_errbuf OUT VARCHAR2, p_retcode OUT VARCHAR2,p_batch_id in varchar2);
PROCEDURE xx_map_data(p_batch_id in varchar2);
PROCEDURE xx_validate_data(p_batch_id in varchar2);
PROCEDURE xx_load_data(p_batch_id in varchar2);
PROCEDURE xx_org_assign (p_org_id out number);
PROCEDURE xx_logit(
p_debug_level NUMBER,
p_des VARCHAR2,
p_source VARCHAR2,
p_message VARCHAR2);
PROCEDURE xx_outit (p_des VARCHAR2, p_message VARCHAR2);
PROCEDURE xx_show_error (
p_error_code IN VARCHAR2,
p_error_message IN VARCHAR2,
p_progress IN VARCHAR2,
p_package_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_err_msg_disp IN VARCHAR2);
PROCEDURE xx_trap_error (
p_error_rec IN g_error_rec,
p_error_code IN VARCHAR2,
p_error_message IN VARCHAR2,
p_progress IN VARCHAR2,
p_err_msg_disp IN VARCHAR2
);
PROCEDURE xx_error_rep (p_batch_id in varchar2);
END XXHCC_OE_CONV_SALESORDER_PKG;
/
show errors;
CREATE OR REPLACE
PACKAGE BODY XXHCC_OE_CONV_SALESORDER_PKG
IS
/*===============================================================================================================*/
--| |
--|Project : |
--|Task : |
--|Application : Oracle Sales Orders |
--|Script : XXHCC_OE_CONV_SALESORDER_PKG.pkb |
--|Created By : Reuse Team |
--|Date Created : 10-SEP-2012 |
--|Description : Creates the Package : XXHCC_OE_CONV_SALESORDER_PKG AND Run this script FROM the APPS schema. |
--|Parameters : |
--|Inputs : |
--|Outputs : |
--+============================================================================================== |
--|ModIFication History |
--|Person Date Version Comments |
--+=========== ====== ============= ====================================== |
--|Erry Swamy 10-Sep-2012 1.0 |
--+================================================================================================================
/*=======================================================================
OBJECT NAME: xx_load_main
OBJECT TYPE: Public Procedure
DESCRIPTION:
PARAMETERS:
<<Name>> <<Data Type>> <<Description>>
RETURNS: <<Only required for Functions>>
<<Data Type>> <<Description>>
VARCHAR2 Error message
VARCHAR2 Error Code
=====================================================================*/
PROCEDURE xx_load_main(
p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY VARCHAR2,
p_batch_id IN VARCHAR2)
IS
c_error_rec G_ERROR_REC;
c_progress VARCHAR2 (10);
c_org_id NUMBER:=0;
BEGIN
----------------------------------------------------------------------
---General Declarations
----------------------------------------------------------------------
c_error_rec.c_package_name := 'XXHCC_OE_CONV_SALESORDER_PKG';
c_error_rec.c_procedure_name := 'xx_load_main';
c_error_rec.c_XXHCC_batch_name := 'OE_SALESORDERS';
--
c_progress := '001';
xx_org_assign (c_org_id);
xx_map_data (P_BATCH_ID);
xx_validate_data (P_BATCH_ID);
xx_load_data (P_BATCH_ID);
xx_error_rep (P_BATCH_ID);
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
fnd_file.put_line(fnd_file.LOG,'Main Exception:'||'SQLCODE=>'||SQLCODE||'--'||'SQLERRM=>'||SQLERRM);
END xx_load_main; --END of Load Main
/*=======================================================================
OBJECT NAME: xx_map_data
OBJECT TYPE: Public Procedure
DESCRIPTION:
PARAMETERS:
<<Name>> <<Data Type>> <<Description>>
RETURNS: <<Only required for Functions>>
<<Data Type>> <<Description>>
=====================================================================*/
PROCEDURE xx_map_data(p_batch_id IN VARCHAR2)
IS
------------------------------------------------------
---------------------------------------------------------------------------
---Cursor to fetch Line records of Sales Orders
---FROM staging table XXHCC_OE_ORDERS_IFACE_STG
---------------------------------------------------------------------------
CURSOR cur_oe_order_lines_IFace ( p_batch_name IN XXHCC_oe_orders_IFace_stg.batch_name%TYPE )
IS
SELECT *
FROM XXHCC_oe_orders_IFace_stg
WHERE load_status = 'N'
AND batch_name = p_batch_name
ORDER BY orig_sys_document_ref ,
orig_sys_line_document_ref ;
-------------------------------------------------------------
-- Cursor to get the error record count---
-------------------------------------------------------------
CURSOR cur_err_rec_count (P_XXHCC_TRANSACTION_ID VARCHAR2)
IS
SELECT COUNT (*)
FROM XXHCC_fin_conv_error
WHERE XXHCC_TRANSACTION_ID = P_XXHCC_TRANSACTION_ID
AND package_name LIKE 'XXHCC_OE_CONV_SALESORDER_PKG'
AND request_id = fnd_global.conc_request_id;
c_error_rec G_ERROR_REC;
c_progress VARCHAR2 (10);
c_error_rec_count NUMBER;
c_operating_unit_name hr_operating_units.NAME%TYPE;
c_inventory_org_ship_FROM_org org_organization_definitions.organization_name%TYPE;
c_inventory_org_sold_FROM_org org_organization_definitions.organization_name%TYPE;
c_SET_of_books_id hr_operating_units.SET_of_books_id%TYPE;
c_functionac_curr gl_SETs_of_books.currency_code%TYPE;
c_order_type oe_transaction_types_tl.NAME%TYPE;
c_oc_payment_term ra_terms.NAME%TYPE;
c_oh_payment_term ra_terms.NAME%TYPE;
c_category_code hz_parties.category_code%TYPE;
c_item_id NUMBER;
d_Payment_term NUMBER;
d_customer_ref NUMBER;
c_err_rec_count NUMBER;
c_status VARCHAR2(1);
c_unit_of_measure VARCHAR2(20);
c_Currency_code_chk VARCHAR2(4);
c_org_id NUMBER:=0;
BEGIN
----------------------------------------------------------------------
---General Declarations
----------------------------------------------------------------------
c_error_rec.c_package_name := 'XXHCC_OE_CONV_SALESORDER_PKG';
c_error_rec.c_procedure_name := 'xx_map_data';
c_progress := '2000';
xx_org_assign (c_org_id);
--Start of Header Level FOR Loop
--Start of Line Level FOR Loop
FOR recur_oe_order_lines_IFace IN cur_oe_order_lines_IFace (p_batch_id)
LOOP
c_error_rec.c_XXHCC_transaction_id :=recur_oe_order_lines_IFace.orig_sys_line_document_ref;
c_error_rec.c_interface_table_name := 'XXHCC_OE_ORDERS_IFACE_STG';
c_error_rec.c_XXHCC_hdr_transaction_id:=recur_oe_order_lines_IFace.orig_sys_document_ref;
c_error_rec.c_XXHCC_transaction_id :=recur_oe_order_lines_IFace.XXHCC_TRANSACTION_ID;
c_error_rec.c_XXHCC_source :=recur_oe_order_lines_IFace.source;
c_error_rec.c_XXHCC_batch_name := p_batch_id;
BEGIN
c_operating_unit_name := NULL;
c_inventory_org_ship_FROM_org := NULL;
c_inventory_org_sold_FROM_org := NULL;
c_SET_of_books_id := NULL;
c_functionac_curr := NULL;
c_order_type := NULL;
c_oc_payment_term := NULL;
c_oh_payment_term := NULL;
----------------------------------------------------
--- To get the Customer Details
----------------------------------------------------
BEGIN
d_Payment_term := NULL;
d_customer_ref := NULL;
c_progress := '2010';
c_error_rec.c_column_name := 'Customer Name';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.orig_sys_customer_name;
BEGIN
SELECT c.cust_account_id
INTO d_customer_ref
FROM HZ_PARTIES p,
hz_cust_accounts c
WHERE p.party_id = c.party_id
AND upper(p.party_name) = Upper(recur_oe_order_lines_IFace.orig_sys_customer_name)
AND c.status = 'A'
AND rownum = 1
ORDER BY p.party_id DESC;
EXCEPTION WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'No Customer Defined' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'No Customer Defined');
END;
SELECT site_uses.Payment_Term_id
INTO d_Payment_term
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_uses ,
hz_cust_accounts cust_acct
WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.cust_account_id = cust_acct.cust_account_id
AND site_uses.site_use_code = 'BILL_TO'
AND cust_acct.cust_account_id = d_customer_ref
AND acct_site.status = 'A'
AND site_uses.status = 'A'
AND cust_acct.status = 'A'
AND acct_site.bill_to_flag = 'P'
AND site_uses.org_id = c_org_id
AND acct_site.org_id = c_org_id;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
--------------------------------------------------------------------------------------------------
--- Validating Item
--------------------------------------------------------------------------------------------------
c_progress := '2020';
c_error_rec.c_column_name := 'Inventory Item AND UOM';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.Inventory_Item;
recur_oe_order_lines_IFace.org_id := c_org_id;
BEGIN
c_item_id := 0;
SELECT msi.inventory_item_id
INTO c_item_id
FROM mtl_System_items msi ,
org_organization_definitions ood
WHERE UPPER(segment1) = recur_oe_order_lines_IFace.Inventory_Item
AND msi.organization_id =ood.organization_id
AND ood.operating_unit =recur_oe_order_lines_IFace.org_id
AND enabled_flag ='Y'
AND upper(Primary_uom_code) = upper(recur_oe_order_lines_IFace.Ordered_quantity_uom)
AND TRUNC(NVL(start_date_active,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(END_date_active,SYSDATE)) >= TRUNC(SYSDATE)
AND msi.organization_id = 204;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
---------------------------------------------------------------------------------------
--Get Value INTO ORG ID
--To fetch the oracle value for Org ID depENDing on the value of org_name
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2030';
c_error_rec.c_column_name := 'ORG ID';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.org_name;
recur_oe_order_lines_IFace.org_id := c_org_id;
---------------------------------------------------------------------------------------
--Get Value INTO Order Source ID
--To fetch the oracle value for Order Source ID depENDing on the value of Order Source
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2040';
c_error_rec.c_column_name := 'order_source';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.order_source;
recur_oe_order_lines_IFace.order_source_id := 0;
------------------------------------------------------------------------------------
--get order type id-------
---------------------------------------------------------------------------------
c_progress := '2050';
c_error_rec.c_column_name := 'Order Type Name';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.order_type;
BEGIN
SELECT otta.transaction_type_id
INTO recur_oe_order_lines_IFace.order_type_id
FROM oe_transaction_types_tl ottt,
oe_transaction_types_all otta
WHERE otta.transaction_type_id = ottt.transaction_type_id
AND otta.transaction_type_code = 'ORDER'
AND ottt.NAME = recur_oe_order_lines_IFace.order_type
AND ottt.language = userenv('LANG')
AND TRUNC (NVL (otta.start_date_active, SYSDATE)) <= TRUNC (SYSDATE)
AND TRUNC (NVL (otta.end_date_active, SYSDATE)) >= TRUNC (SYSDATE)
AND otta.org_id = recur_oe_order_lines_IFace.org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'NO_DATA_FOUND' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'NO_DATA_FOUND' );
WHEN TOO_MANY_ROWS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'Order Type - Too many rows' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'Order Type - Too many rows');
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'Order Type - Others' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'Order Type - Others');
END;
------------------------------------------------------------------------------------
--get Line type id-------
---------------------------------------------------------------------------------
c_progress := '2060';
c_error_rec.c_column_name := 'Line Type Name';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.Line_type_id;
IF recur_oe_order_lines_IFace.order_type_id IS NOT NULL THEN
BEGIN
SELECT default_outbound_line_type_id
INTO recur_oe_order_lines_IFace.Line_type_id
FROM oe_transaction_types_all
WHERE org_id = recur_oe_order_lines_IFace.org_id
AND transaction_type_id = recur_oe_order_lines_IFace.order_type_id;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
END IF;
----------------------------------------------------------------------------------
--MAP SALESREP NAME
--To fetch the oracle value for Sales rep ID depENDing on the value of Sales Rep
----------------------------------------------------------------------------------
c_progress := '2070';
c_error_rec.c_column_name := 'Sales Rep Name';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.salesrep;
recur_oe_order_lines_IFace.salesrep_id := -3;
----------------------------------------------------------------------------------
c_progress := '2080';
c_error_rec.c_column_name := 'payment_term Name';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.payment_term_id;
recur_oe_order_lines_IFace.payment_term_id := d_Payment_term;
-----------------------------------------------------------------------------------
--Get CUSTOMER ID(Sold to org ID)
--To fetch the oracle value for Customer ID depENDing on the value of Customer reference
-----------------------------------------------------------------------------------
c_progress := '2090';
c_error_rec.c_column_name := 'Customer Reference';
c_error_rec.c_column_value :=recur_oe_order_lines_IFace.sold_to_org;
recur_oe_order_lines_IFace.sold_to_org_id := d_customer_ref;
---------------------------------------------------------------------------------------
--Get Value INTO SOLD FROM ORG ID
--To fetch the oracle value for Sold FROM Org ID depENDing on the value of Organization
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2100';
c_error_rec.c_column_name := 'SOLD FROM ORG ID';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.sold_FROM_org;
recur_oe_order_lines_IFace.sold_FROM_org_id := c_org_id;
---------------------------------------------------------------------------------------
--Get Value INTO SHIP FROM ORG ID
--To fetch the oracle value for SHIP FROM Org ID depENDing on the value of Organization
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2110';
c_error_rec.c_column_name := 'SHIP FROM ORG ID';
recur_oe_order_lines_IFace.ship_FROM_org_id := c_org_id;
---------------------------------------------------------------------------------------
--Get Value INTO Pricelist_id
--To fetch the oracle value for Pricelist ID depENDing on the value of Customer
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2120';
c_error_rec.c_column_name := 'Pricelist ID';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.price_list_id;
BEGIN
SELECT site_uses.price_list_id
INTO recur_oe_order_lines_IFace.price_list_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_uses ,
hz_cust_accounts cust_acct
WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.cust_account_id = cust_acct.cust_account_id
AND site_uses.site_use_code = 'SHIP_TO'
AND cust_acct.cust_account_id = d_customer_ref
AND acct_site.status = 'A'
AND site_uses.status = 'A'
AND cust_acct.status = 'A'
AND acct_site.ship_to_flag = 'P'
AND site_uses.org_id = recur_oe_order_lines_IFace.org_id
AND acct_site.org_id = recur_oe_order_lines_IFace.org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'NO_DATA_FOUND-price_list_id' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'NO_DATA_FOUND- price_list_id' );
WHEN TOO_MANY_ROWS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'price_list_id- Too many rows' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'price_list_id - Too many rows');
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'price_list_id - Others' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'price_list_id - Others');
END ;
IF recur_oe_order_lines_IFace.price_list_id IS NULL THEN
BEGIN
SELECT Price_list_id
INTO recur_oe_order_lines_IFace.price_list_id
FROM oe_transaction_types_all
WHERE org_id = recur_oe_order_lines_IFace.org_id
AND transaction_type_id = recur_oe_order_lines_IFace.order_type_id;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
END IF;
IF recur_oe_order_lines_IFace.price_list_id IS NOT NULL THEN
c_Currency_code_chk := NULL;
BEGIN
SELECT Currency_code
INTO c_Currency_code_chk
FROM QP_PRICE_LISTS_V
WHERE price_list_id = recur_oe_order_lines_IFace.price_list_id
AND Currency_code = recur_oe_order_lines_IFace.TRANSACTIONAL_CURR;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'Pricelist Currency dIFferent than SO Currency' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'Pricelist Currency dIFferent than SO Currency' );
END;
END IF;
---------------------------------------------------------------------------------------
--Get Value INTO SHIP TO ORG ID
--To fetch the oracle value for SHIP TO Org ID depENDing on the value of Organization
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2130';
c_error_rec.c_column_name := 'SHIP TO ORG ID';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.ship_TO_org;
BEGIN
SELECT site_uses.site_use_id
INTO recur_oe_order_lines_IFace.ship_TO_org_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_uses ,
hz_cust_accounts cust_acct
WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.cust_account_id = cust_acct.cust_account_id
AND site_uses.site_use_code = 'SHIP_TO'
AND cust_acct.cust_account_id = d_customer_ref--
-- AND acct_site.orig_system_reference = p_ship_to_address_ref
AND acct_site.status = 'A'
AND site_uses.status = 'A'
AND cust_acct.status = 'A'
AND acct_site.ship_to_flag = 'P'
AND site_uses.org_id = recur_oe_order_lines_IFace.org_id
AND acct_site.org_id = recur_oe_order_lines_IFace.org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'NO_DATA_FOUND-ship_TO_org' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'NO_DATA_FOUND- ship_TO_org' );
WHEN TOO_MANY_ROWS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'ship_TO_org- Too many rows' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'ship_TO_org - Too many rows');
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'ship_TO_org - Others' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'ship_TO_org - Others');
END ;
---------------------------------------------------------------------------------------
--Get Value INTO invoice TO ORG ID
--To fetch the oracle value for invoice TO Org ID depENDing on the value of Organization
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2140';
c_error_rec.c_column_name := 'invoice TO ORG ID';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.invoice_TO_org;
BEGIN
SELECT site_uses.site_use_id
INTO recur_oe_order_lines_IFace.invoice_TO_org_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_uses ,
hz_cust_accounts cust_acct
WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.cust_account_id = cust_acct.cust_account_id
AND site_uses.site_use_code = 'BILL_TO'
AND cust_acct.cust_account_id = d_customer_ref
--- AND acct_site.orig_system_reference = p_bill_to_address_ref
AND acct_site.status = 'A'
AND site_uses.status = 'A'
AND cust_acct.status = 'A'
AND acct_site.bill_to_flag = 'P'
AND site_uses.org_id = recur_oe_order_lines_IFace.org_id
AND acct_site.org_id = recur_oe_order_lines_IFace.org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'NO_DATA_FOUND-invoice_TO_org' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'NO_DATA_FOUND- invoice_TO_org' );
WHEN TOO_MANY_ROWS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'invoice_TO_org- Too many rows' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'invoice_TO_org - Too many rows');
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'invoice_TO_org - Others' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'invoice_TO_org - Others');
END ;
-----------------------------------------------------------------------------------
--Need to move above the main header for loop
--MAP TRANSACTIONAL_CURR
--To fetch the values for Conversion Rate, Conversion Rate Date AND Conversion Type Code
--depENDing on non-equality of Transactional Curr AND Functional Currency Code
-----------------------------------------------------------------------------------
BEGIN
c_progress := '2150';
SELECT SET_of_books_id
INTO c_SET_of_books_id
FROM hr_operating_units
WHERE organization_id = recur_oe_order_lines_IFace.org_id;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
c_functionac_curr := 'GBP';
c_progress := '2160';
c_error_rec.c_column_name := 'Transactional Curr';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.TRANSACTIONAL_CURR;
IF (recur_oe_order_lines_IFace.TRANSACTIONAL_CURR <> c_functionac_curr ) THEN
recur_oe_order_lines_IFace.conversion_rate_date := TRUNC(sysdate);
recur_oe_order_lines_IFace.conversion_type_code := 'Corporate';
END IF;
-----------------------------------------------------------------------------------
OPEN cur_err_rec_count (recur_oe_order_lines_IFace.XXHCC_TRANSACTION_ID);
FETCH cur_err_rec_count INTO c_err_rec_count;
CLOSE cur_err_rec_count;
IF c_err_rec_count > 0 THEN
c_status := 'E';
ELSE
c_status := 'M';
END IF;
BEGIN
UPDATE XXHCC_oe_orders_IFace_stg
SET org_id = recur_oe_order_lines_IFace.org_id,
order_source_id = recur_oe_order_lines_IFace.order_source_id,
order_type_id = recur_oe_order_lines_IFace.order_type_id,
Line_type_id = recur_oe_order_lines_IFace.Line_type_id,
price_list_id = recur_oe_order_lines_IFace.price_list_id,
salesrep_id = recur_oe_order_lines_IFace.salesrep_id,
payment_term_id = recur_oe_order_lines_IFace.payment_term_id,
sold_to_org_id = recur_oe_order_lines_IFace.sold_to_org_id,
sold_FROM_org_id = recur_oe_order_lines_IFace.sold_FROM_org_id,
ship_FROM_org_id = recur_oe_order_lines_IFace.ship_FROM_org_id,
ship_TO_org_id = recur_oe_order_lines_IFace.ship_TO_org_id,
invoice_TO_org_id = recur_oe_order_lines_IFace.invoice_TO_org_id,
load_status = c_status
WHERE orig_sys_document_ref = recur_oe_order_lines_IFace.orig_sys_document_ref
AND orig_sys_line_document_ref = recur_oe_order_lines_IFace.orig_sys_line_document_ref
AND batch_name = recur_oe_order_lines_IFace.batch_name
AND org_name = recur_oe_order_lines_IFace.org_name;
EXCEPTION WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
BEGIN
UPDATE XXHCC_oe_orders_IFace_stg
SET conversion_rate_date = recur_oe_order_lines_IFace.conversion_rate_date,
conversion_type_code = recur_oe_order_lines_IFace.conversion_type_code,
last_UPDATE_date = SYSDATE,
last_UPDATEd_by = fnd_global.user_id
WHERE orig_sys_document_ref = recur_oe_order_lines_IFace.orig_sys_document_ref
AND orig_sys_line_document_ref = recur_oe_order_lines_IFace.orig_sys_line_document_ref
AND org_name = recur_oe_order_lines_IFace.org_name
AND batch_name = recur_oe_order_lines_IFace.batch_name
AND recur_oe_order_lines_IFace.TRANSACTIONAL_CURR <> c_functionac_curr;
EXCEPTION WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
EXCEPTION
WHEN OTHERS THEN
--Updating Status Flag to 'E' IF errors
UPDATE XXHCC_oe_orders_IFace_stg
SET load_status = 'E',
last_UPDATE_date = SYSDATE,
last_UPDATEd_by = fnd_global.user_id
WHERE orig_sys_line_document_ref = recur_oe_order_lines_IFace.orig_sys_line_document_ref;
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
END LOOP;
--END of Line Level Loop
--END of Header Level Loop
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END xx_map_data; --END of Map Data Procedure
/*=======================================================================
OBJECT NAME: xx_validate_data
OBJECT TYPE: <<Public/Private>> <<Function/Procedure>>
DESCRIPTION:
PARAMETERS:
<<Name>> <<Data Type>> <<Description>>
RETURNS: <<Only required for Functions>>
<<Data Type>> <<Description>>
=====================================================================*/
PROCEDURE xx_validate_data(
p_batch_id IN VARCHAR2)
IS
------------------------------------------------------
---Cursor to fetch Header records of Sales Orders
---FROM staging table XXHCC_OE_ORDERS_IFACE_STG
------------------------------------------------------
---------------------------------------------------------------------------
---Cursor to fetch Line records of Sales Orders
---FROM staging table XXHCC_OE_ORDERS_IFACE_STG
---------------------------------------------------------------------------
CURSOR cur_oe_order_lines_IFace ( p_batch_id IN VARCHAR2 )
IS
SELECT *
FROM XXHCC_oe_orders_IFace_stg
WHERE load_status = 'M'
AND batch_name = p_batch_id
ORDER BY orig_sys_document_ref ,
orig_sys_line_document_ref ;
-------------------------------------------------------------
-- Cursor to get the error record count---
-------------------------------------------------------------
CURSOR cur_err_rec_count (p_XXHCC_transaction_id VARCHAR2)
IS
SELECT COUNT (*)
FROM XXHCC_fin_conv_error
WHERE XXHCC_TRANSACTION_ID = p_XXHCC_transaction_id
AND package_name LIKE 'XXHCC_OE_CONV_SALESORDER_PKG'
AND request_id = fnd_global.conc_request_id;
c_error_rec G_ERROR_REC;
c_progress VARCHAR2 (10);
c_error_rec_count NUMBER;
c_sqlcode VARCHAR2 (2000);
c_sqlerrm VARCHAR2 (2000);
c_err_rec_count NUMBER;
c_status VARCHAR2(1);
c_order_ref_base oe_order_headers_all.orig_sys_document_ref%TYPE;
c_order_ref_iface oe_headers_iface_all.orig_sys_document_ref%TYPE;
c_org_id NUMBER:=0;
l_flag NUMBER (1);
l_count NUMBER (15);
BEGIN
/*----------------------------------------------------------------------
---General Declarations
----------------------------------------------------------------------*/
c_error_rec.c_package_name := 'XXHCC_OE_CONV_SALESORDER_PKG';
c_error_rec.c_procedure_name := 'xx_validate_data';
c_progress := '3000';
xx_org_assign (c_org_id);
--Start of Line Level Loop
FOR recur_oe_order_lines_IFace IN cur_oe_order_lines_IFace (p_batch_id)
LOOP
c_error_rec.c_XXHCC_transaction_id :=recur_oe_order_lines_IFace.ORIG_SYS_LINE_DOCUMENT_REF;
c_error_rec.c_interface_table_name := 'XXHCC_OE_ORDERS_IFACE_STG';
c_error_rec.c_XXHCC_hdr_transaction_id:=recur_oe_order_lines_IFace.orig_sys_document_ref;
c_error_rec.c_XXHCC_source :=recur_oe_order_lines_IFace.source;
c_error_rec.c_XXHCC_batch_name := p_batch_id;
----------------------------------
--Validate Duplicate ORDER NUMBER
----------------------------------
c_progress := '3010';
c_error_rec.c_column_name := 'ORDER NUMBER REFERENCE';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.orig_sys_document_ref;
BEGIN
SELECT orig_sys_document_ref
INTO c_order_ref_base
FROM oe_order_headers_all
WHERE orig_sys_document_ref = recur_oe_order_lines_IFace.orig_sys_document_ref
AND org_id = recur_oe_order_lines_IFace.org_id
AND order_type_id = recur_oe_order_lines_IFace.order_type_id;
EXCEPTION
WHEN OTHERS THEN
l_flag := 1;
END;
IF (c_order_ref_base = recur_oe_order_lines_IFace.orig_sys_document_ref) THEN
c_sqlcode := '0';
c_sqlcode := 'Order Reference already exists in Oracle';
/*xx_show_error (p_error_code => c_sqlcode,
p_error_message => c_sqlcode,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.g_XXXXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'Duplicate Order Reference - Base Tables' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => c_sqlcode,
p_error_message => c_sqlcode,
p_progress => c_progress,
p_err_msg_disp => 'Duplicate Order Reference - Base Tables' );*/
END IF;
BEGIN
SELECT orig_sys_document_ref
INTO c_order_ref_base
FROM oe_headers_iface_all
WHERE orig_sys_document_ref = recur_oe_order_lines_IFace.orig_sys_document_ref
AND org_id = recur_oe_order_lines_IFace.org_id
AND order_type_id = recur_oe_order_lines_IFace.order_type_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
IF (c_order_ref_base = recur_oe_order_lines_IFace.orig_sys_document_ref) THEN
c_sqlcode := '0';
c_sqlcode := 'Order Reference already exists in Interface Tables';
/*
xx_show_error (p_error_code => c_sqlcode, p_error_message => c_sqlcode, p_progress => p_progress, p_package_name => p_error_rec.c_package_name || '.' || p_error_rec.c_procedure_name, p_column_name => p_error_rec.g_XXXXHCC_hdr_transaction_id || '/' || p_error_rec.c_column_name || '/' || p_error_rec.c_column_value, p_err_msg_disp => 'Duplicate Order Reference - Interface Tables' );
xx_trap_error (p_error_rec => p_error_rec, p_error_code => c_sqlcode, p_error_message => c_sqlcode, p_progress => p_progress, p_err_msg_disp => 'Duplicate Order Reference - Interface Tables' );
*/
END IF;
OPEN cur_err_rec_count (recur_oe_order_lines_IFace.XXHCC_TRANSACTION_ID);
FETCH cur_err_rec_count INTO c_err_rec_count;
CLOSE cur_err_rec_count;
IF c_err_rec_count > 0 THEN
c_status := 'E';
ELSE
c_status := 'V';
END IF;
-- updating the status flag to 'E' IF not valid record
UPDATE XXHCC_oe_orders_IFace_stg
SET load_status = c_status,
last_UPDATE_date = SYSDATE,
last_UPDATEd_by = fnd_global.user_id
WHERE orig_sys_document_ref = recur_oe_order_lines_IFace.orig_sys_document_ref
AND orig_sys_line_document_ref = recur_oe_order_lines_IFace.orig_sys_line_document_ref
AND org_name = recur_oe_order_lines_IFace.org_name
AND batch_name = recur_oe_order_lines_IFace.batch_name;
END LOOP; --END of Line Level Loop
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END xx_validate_data; --END of Validate Data Procedure
/*=======================================================================
OBJECT NAME: xx_load_data
OBJECT TYPE: <<Public/Private>> <<Function/Procedure>>
DESCRIPTION:
PARAMETERS:
<<Name>> <<Data Type>> <<Description>>
RETURNS: <<Only required for Functions>>
<<Data Type>> <<Description>>
=====================================================================*/
PROCEDURE xx_load_data(
p_batch_id IN VARCHAR2)
IS
------------------------------------------------------
---Cursor to fetch Header records of Sales Orders
---FROM staging table XXHCC_OE_ORDERS_IFACE_STG
------------------------------------------------------
CURSOR cur_oe_order_headers_IFace( p_batch_id IN VARCHAR2)
IS
SELECT DISTINCT order_source_id, -- PEUS Open Sales Order Migration
orig_sys_document_ref,
org_id,
ordered_date,
order_type_id,
price_list_id,
salesrep_id,
customer_po_number,
sold_to_org_id ,
ship_FROM_org_id ,
ship_to_org_id ,
invoice_to_org_id ,
order_category ,
sold_FROM_org_id,
booked_flag,
TRANSACTIONAL_CURR,
tax_exempt_flag,
tax_exempt_reason
FROM XXHCC_oe_orders_IFace_stg
WHERE load_status = 'V'
AND batch_name = p_batch_id
ORDER BY orig_sys_document_ref;
---------------------------------------------------------------------------
---Cursor to fetch Line records of Sales Orders
---FROM staging table XXHCC_OE_ORDERS_IFACE_STG
---------------------------------------------------------------------------
CURSOR cur_oe_order_lines_IFace ( p_oh_orig_sys_document_ref IN XXHCC_oe_orders_IFace_stg.orig_sys_document_ref%TYPE, p_batch_id IN VARCHAR2 )
IS
SELECT DISTINCT orig_sys_document_ref,
orig_sys_line_document_ref,
line_type_id,
Ordered_quantity,
Ordered_quantity_uom,
request_date,
promise_date,
unit_selling_price,
calculate_price_flag,
org_id,
item_type_code,
inventory_item,
order_source_id,
TRANSACTIONAL_CURR,
FOB
FROM XXHCC_oe_orders_IFace_stg
WHERE load_status = 'V'
AND ORIG_SYS_DOCUMENT_REF = p_oh_orig_sys_document_ref
AND batch_name = p_batch_id;
-------------------------------------------------------------
-- Cursor to get the error record count---
-------------------------------------------------------------
CURSOR cur_err_rec_count (p_XXHCC_hdr_transaction_id VARCHAR2)
IS
SELECT COUNT (*)
FROM XXHCC_fin_conv_error
WHERE XXHCC_HDR_TRANSACTION_ID = p_XXHCC_hdr_transaction_id
AND package_name LIKE 'XXHCC_OE_CONV_SALESORDER_PKG'
AND request_id = fnd_global.conc_request_id;
c_error_rec G_ERROR_REC;
c_progress VARCHAR2 (10);
c_error_rec_count NUMBER;
c_calculate_price_flag oe_lines_IFace_all.calculate_price_flag%TYPE;
c_precision NUMBER;
c_unit_list_price NUMBER;
c_error_occured_at_line VARCHAR2(1):='N';
c_unit_selling_price NUMBER;
c_err_rec_count NUMBER;
c_status VARCHAR2(1);
BEGIN
----------------------------------------------------------------------
---General Declarations
----------------------------------------------------------------------
c_error_rec.c_package_name := 'XXHCC_OE_CONV_SALESORDER_PKG';
c_error_rec.c_procedure_name := 'xx_load_data';
-- DELETE FROM OE_LINES_IFACE_ALL WHERE created_by = fnd_global.user_id;
-- DELETE FROM OE_Headers_IFACE_ALL WHERE created_by = fnd_global.user_id;
COMMIT;
c_progress := '4000';
--Start of Header Level Loop
FOR recur_oe_order_headers_IFace IN cur_oe_order_headers_IFace(p_batch_id)
LOOP
c_error_occured_at_line :='N';
c_error_rec.c_interface_table_name := 'XXHCC_OE_ORDERS_IFACE_STG';
c_error_rec.c_XXHCC_hdr_transaction_id:=recur_oe_order_headers_IFace.orig_sys_document_ref;
c_error_rec.c_XXHCC_batch_name := p_batch_id;
BEGIN
--Start of Line Level Loop
FOR recur_oe_order_lines_IFace IN cur_oe_order_lines_IFace (recur_oe_order_headers_IFace.orig_sys_document_ref,p_batch_id)
LOOP
c_error_rec.c_XXHCC_transaction_id :=recur_oe_order_lines_IFace.orig_sys_line_document_ref;
c_progress := '4010';
BEGIN
--Get Currency Precision
--------------------------
BEGIN
SELECT PRECISION
INTO c_precision
FROM fnd_currencies
WHERE currency_code = recur_oe_order_lines_IFace.TRANSACTIONAL_CURR;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
c_unit_selling_price := ROUND (recur_oe_order_lines_IFace.unit_selling_price, c_precision );
c_calculate_price_flag :=recur_oe_order_lines_IFace.calculate_price_flag;
IF c_error_occured_at_line = 'N' THEN -- IF error occured at least at one of lines no other line should be loaded in that order
--insert stmts for lines
INSERT
INTO OE_LINES_IFACE_ALL
(
ORIG_SYS_DOCUMENT_REF ,
ORIG_SYS_LINE_REF ,
LINE_TYPE_ID ,
ordered_quantity,
ORDER_QUANTITY_UOM ,
REQUEST_DATE ,
PROMISE_DATE ,
UNIT_LIST_PRICE ,
UNIT_SELLING_PRICE ,
SHIPMENT_NUMBER ,
CALCULATE_PRICE_FLAG,
operation_code,
created_by,
creation_date,
last_UPDATEd_by,
last_UPDATE_date,
org_id,
item_type_code,
INVENTORY_ITEM ,
order_source_id,
link_to_line_ref,
--top_modec_line_ref,
option_flag,
option_number,
FOB_POINT
)
VALUES
(
recur_oe_order_lines_IFace.orig_sys_document_ref,
recur_oe_order_lines_IFace.orig_sys_line_document_ref,
recur_oe_order_lines_IFace.line_type_id,
recur_oe_order_lines_IFace.Ordered_quantity,
recur_oe_order_lines_IFace.Ordered_quantity_uom,
recur_oe_order_lines_IFace.request_date,
recur_oe_order_lines_IFace.promise_date,
c_unit_selling_price,
c_unit_selling_price,
NULL,
NVL(recur_oe_order_lines_IFace.calculate_price_flag,'N'),
'INSERT',
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate ,
recur_oe_order_lines_IFace.org_id,
recur_oe_order_lines_IFace.item_type_code,
recur_oe_order_lines_IFace.inventory_item,
recur_oe_order_lines_IFace.order_source_id,
NULL,
--NULL,
NULL,
NULL,
recur_oe_order_lines_IFace.FOB
);
END IF;
EXCEPTION
WHEN OTHERS THEN
c_error_occured_at_line := 'Y';
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
END LOOP; --END of Line Level Loop
c_progress := '4050';
-- line look block ENDed AND header look block continues
--inserting header interface table
IF c_error_occured_at_line = 'N' THEN
INSERT
INTO oe_headers_IFace_all
(
order_source_id ,
orig_sys_document_ref,
org_id ,
ordered_date ,
order_type_id ,
price_list_id ,
salesrep_id ,
customer_po_number ,
sold_to_org_id ,
ship_FROM_org_id ,
ship_to_org_id ,
invoice_to_org_id ,
created_by ,
creation_date ,
last_UPDATEd_by ,
last_UPDATE_date ,
operation_code ,
order_category ,
sold_FROM_org_id ,
QUOTE_NUMBER ,
quote_date,
booked_flag,
TRANSACTIONAL_CURR_CODE,
TAX_EXEMPT_FLAG,
tax_exempt_reason
)
VALUES
(
recur_oe_order_headers_IFace.order_source_id, -- PEUS Open Sales Order Migration
recur_oe_order_headers_IFace.orig_sys_document_ref,
recur_oe_order_headers_IFace.org_id,
recur_oe_order_headers_IFace.ordered_date,
recur_oe_order_headers_IFace.order_type_id,
recur_oe_order_headers_IFace.price_list_id,
recur_oe_order_headers_IFace.salesrep_id,
recur_oe_order_headers_IFace.customer_po_number,
recur_oe_order_headers_IFace.sold_to_org_id ,
recur_oe_order_headers_IFace.ship_FROM_org_id ,
recur_oe_order_headers_IFace.ship_to_org_id ,
recur_oe_order_headers_IFace.invoice_to_org_id,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
'INSERT',
recur_oe_order_headers_IFace.order_category ,
recur_oe_order_headers_IFace.sold_FROM_org_id,
NULL ,
NULL,
NVL(recur_oe_order_headers_IFace.booked_flag,'Y'),
recur_oe_order_headers_IFace.TRANSACTIONAL_CURR,
recur_oe_order_headers_IFace.tax_exempt_flag,
recur_oe_order_headers_IFace.tax_exempt_reason
);
OPEN cur_err_rec_count (recur_oe_order_headers_IFace.orig_sys_document_ref);
FETCH cur_err_rec_count INTO c_err_rec_count;
CLOSE cur_err_rec_count;
IF c_err_rec_count > 0 THEN
ROLLBACK;
c_status := 'E';
ELSE
c_status := 'L';
END IF;
--Updating the status flag to 'L' upon successful insertion INTO interface table
UPDATE XXHCC_oe_orders_IFace_stg
SET load_status = c_status,
last_UPDATE_date = SYSDATE,
last_UPDATEd_by = fnd_global.user_id
WHERE orig_sys_document_ref = recur_oe_order_headers_IFace.orig_sys_document_ref
AND batch_name = p_batch_id
AND org_id = recur_oe_order_headers_IFace.org_id;
ELSE
ROLLBACK;
UPDATE XXHCC_oe_orders_IFace_stg
SET load_status = 'E',
last_UPDATE_date = SYSDATE,
last_UPDATEd_by = fnd_global.user_id
WHERE orig_sys_document_ref = recur_oe_order_headers_IFace.orig_sys_document_ref
AND batch_name = p_batch_id
AND org_id = recur_oe_order_headers_IFace.org_id;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
UPDATE XXHCC_oe_orders_IFace_stg
SET load_status = 'E',
last_UPDATE_date = SYSDATE,
last_UPDATEd_by = fnd_global.user_id
WHERE orig_sys_document_ref = recur_oe_order_headers_IFace.orig_sys_document_ref
AND batch_name = p_batch_id
AND org_id = recur_oe_order_headers_IFace.org_id;
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
END LOOP; --END of Header Level Loop
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END xx_load_data;
PROCEDURE xx_show_error(
p_error_code IN VARCHAR2,
p_error_message IN VARCHAR2,
p_progress IN VARCHAR2,
p_package_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_err_msg_disp IN VARCHAR2 )
/*============================================================================
PROCEDURE NAME: xx_show_error
DESCRIPTION: sends messages to the concurrent request LOG or SQL*Plus
output depending on the global variable: c_log_dest.
============================================================================*/
IS
BEGIN
xx_logit (c_debug_level,
c_log_dest,
p_package_name, 'Error at location: ' || p_progress || ', Error message: ' || p_error_message || ', Error Code: '
|| p_error_code || ', Value: ' || RTRIM (SUBSTR (p_column_name, 1, 50)) );
DBMS_OUTPUT.put_line (c_debug_level);
EXCEPTION
WHEN OTHERS THEN
xx_logit (c_debug_level, c_log_dest, p_package_name, SUBSTR ('Error showing errors:' || SQLERRM, 1, 250) );
NULL;
END xx_show_error;
PROCEDURE xx_trap_error(
p_error_rec IN g_ERROR_REC,
p_error_code IN VARCHAR2,
p_error_message IN VARCHAR2,
p_progress IN VARCHAR2,
p_err_msg_disp IN VARCHAR2 )
/*============================================================================
PROCEDURE NAME: xx_trap_error
DESCRIPTION: sends messages to the concurrent request LOG or SQL*Plus
output depending on the global variable: c_log_dest.
============================================================================*/
IS
PRAGMA AUTONOMOUS_TRANSACTION;
c_request_id NUMBER := fnd_global.conc_request_id;
c_user_id NUMBER := fnd_global.user_id;
BEGIN
INSERT
INTO APPS.XXHCC_fin_conv_error
(
interface_error_id,
package_name,
procedure_name,
interface_table_name,
XXHCC_hdr_transaction_id,
XXHCC_transaction_id,
XXHCC_batch_name,
XXHCC_source,
column_name,
column_value,
ERROR_CODE,
error_message,
progress,
error_message_disp,
request_id,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES
(
APPS.XXhCC_fin_conv_error_seq.NEXTVAL,
p_error_rec.c_package_name,
p_error_rec.c_procedure_name,
p_error_rec.c_interface_table_name,
p_error_rec.c_XXHCC_hdr_transaction_id,
p_error_rec.c_XXHCC_transaction_id ,
p_error_rec.c_XXHCC_batch_name,
p_error_rec.c_XXHCC_source,
p_error_rec.c_column_name,
p_error_rec.c_column_value,
p_error_code,
p_error_message,
p_progress,
p_err_msg_disp,
c_request_id,
SYSDATE,
NVL (c_user_id, -1),
SYSDATE,
NVL (c_user_id, -1)
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xx_logit (c_debug_level, c_log_dest, p_error_rec.c_package_name, 'Error inserting data into XXHCC_FIN_CONV_ERROR' );
NULL;
END xx_trap_error;
PROCEDURE xx_logit
(
p_debug_level NUMBER,
p_des VARCHAR2,
p_source VARCHAR2,
p_message VARCHAR2
)
/*=======================================================================
PROCEDURE NAME: logit
DESCRIPTION: sends messages to the concurrent request LOG or SQL*Plus
output depending on the global variable: c_log_dest.
PARAMETERS:
p_debug_level:
1 = If the global variable: c_debug_level = 1,
then this message will appear in the LOG file
0 = Always writes the message to the LOG file
p_des:
DBMS_OUTPUT = writes output to std out
CONCURRENT_PROGRAM = writes output to concurrent request log
If this value is NULL, program uses global variable: g_log_des.
p_source:
Can be any 'CODE' value: progress point in the application,
program name where error occurred.
p_message:
Free-form message text of log mesages.
=======================================================================*/
IS
l_message VARCHAR2 (4000);
l_des VARCHAR2 (30);
BEGIN
l_des := NVL (p_des, c_log_dest);
-- IF p_debug_level = 0 OR (p_debug_level = 1 AND c_debug_level = 1)
IF p_debug_level = 1 THEN
IF p_source IS NULL THEN
l_message := p_message;
ELSE
l_message := p_source || ': ' || p_message;
END IF;
IF l_des = 'DBMS_OUTPUT' THEN
DBMS_OUTPUT.put_line (SUBSTR (l_message, 1, 255));
ELSIF l_des = 'CONCURRENT_PROGRAM' THEN
apps.fnd_file.put_line (apps.fnd_file.LOG, l_message);
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END xx_logit;
-----------------------------------------------------------------------------------------------
PROCEDURE xx_outit
(
p_des VARCHAR2,
p_message VARCHAR2
)
/*=======================================================================
PROCEDURE NAME: outit
DESCRIPTION: sends messages to the concurrent request OUTPUT or SQL*Plus
output depending on the global variable: c_log_dest.
PARAMETERS:
p_des:
DBMS_OUTPUT = writes output to std out
CONCURRENT_PROGRAM = writes output to concurrent request log
If this value is NULL, program uses global variable: g_log_des.
p_message:
Free-form message text of log mesages.
=======================================================================*/
IS
c_message VARCHAR2 (4000);
c_des VARCHAR2 (30);
BEGIN
c_des := NVL (p_des, c_log_dest);
c_message := p_message;
IF c_des = 'DBMS_OUTPUT' THEN
DBMS_OUTPUT.put_line (SUBSTR (c_message, 1, 255));
ELSIF c_des = 'CONCURRENT_PROGRAM' THEN
apps.fnd_file.put_line (apps.fnd_file.output, c_message);
END IF;
END xx_outit;
PROCEDURE xx_org_assign
(
p_org_id OUT NUMBER
)
IS
c_org_id NUMBER := fnd_global.org_id;
c_user_id NUMBER := fnd_global.user_id;
c_resp_id NUMBER := fnd_global.resp_id;
c_resp_ap_id NUMBER := fnd_global.resp_appl_id;
BEGIN
--fnd_profile.value('org_id');
fnd_global.apps_initialize (c_user_id, c_resp_id, c_resp_ap_id);
p_org_id:=c_org_id;
-- fnd_file.put_line (fnd_file.LOG, 'Org_id->'||p_org_id);
END xx_org_assign;
PROCEDURE xx_error_rep(
p_batch_id VARCHAR2)
IS
CURSOR cur_err_rep(c_batch_id VARCHAR2)
IS
SELECT * FROM XXHCC_fin_conv_error WHERE XXHCC_BATCH_NAME = c_batch_id;
BEGIN
fnd_file.put_line (fnd_file.LOG, '================================================================ ERROR RECORDS ======================================================');
fnd_file.put_line (fnd_file.LOG, 'INTERFACE_ERROR_ID'||':'||'PACKAGE_NAME:'||' '||'PROCEDURE_NAME:'' '||'INTERFACE_TABLE_NAME:'||' ' ||'XXHCC_HDR_TRANSACTION_ID:'||' '||'XXHCC_TRANSACTION_ID:'||' '||'COLUMN_NAME:'||' '||'COLUMN_VALUE:'||' '||'ERROR_CODE:'||' '||'ERROR_MESSAGE:' ||' '||'ERROR_MESSAGE_DISP:'||' '||'PROGRESS');
FOR i IN cur_err_rep(p_batch_id)
LOOP
fnd_file.put_line (fnd_file.LOG,i.INTERFACE_ERROR_ID||':'' '||i.PACKAGE_NAME||' '||i.PROCEDURE_NAME||' '||i.INTERFACE_TABLE_NAME||' '||i.XXHCC_HDR_TRANSACTION_ID ||' '||i.XXHCC_TRANSACTION_ID||' '||i.COLUMN_NAME||' '||i.COLUMN_VALUE||' '||i.ERROR_CODE ||' '||i.ERROR_MESSAGE||' '||i.ERROR_MESSAGE_DISP ||' '||i.PROGRESS );
END LOOP;
fnd_file.put_line (fnd_file.LOG, '================================================================ END ERROR RECORDS ==================================================');
END xx_error_rep;
--END of Load Data Procedure
END XXHCC_OE_CONV_SALESORDER_PKG;
/
show errors;
List Of Files
-------------
1. MD-70 (Hitachi_Conversion_Open_Sales_Order_CV060 - v1.0)
2. XXHCC_SO_STAGING.sql
--Script for creating staging table,SYNONYM and Sequence
3. XXHCC_FIN_CONV_ERROR.sql
--Script for creating Error table and SYNONYM
4. XXHCC_CONV_SO_LOAD.csv
-- Data File
5. XXHCC_CONV_SO_LOAD.ctl
--Program to load the data to staging table
6. XXHCC_OE_CONV_SALESORDER_PKG.pks
-- Package Specification and body
-- Package to validate and import data to Oracle
7. MD-120(Please refer Installation Requirements-12 in MD70 Doc)
-- Installation Steps
8. LDT
--HCUSOECNV.ldt -- LDT for staging table data load
--HCUSOEINTLOAD.ldt -- LDT for Interface Program
--HCUSOEINTLOAD_REG -- LDT for Interface Program to request group
--HCUSOECNV_REG -- LDT for Interface Program to request group
Program Name : ‘XXHCC Inventory Cross Reference Sql Loader Program’ , 'XXHCC Inventory Item Cross Reference Program'
Responsibility name : Inventory, Vision Operations(USA)
Functional Execution Steps:
1. Connect to server using FTP tool
2. Fill the relevant information in the data file(XXHCC_CONV_SO_LOAD.csv) and move to $XXHCC_TOP/bin directory
3. Connect to Oracle Application and navigate to SRS window and submit ‘HCUS Open Sales Order Load Staging Data’ Loader program. This will load data to stage table.
4. Records will be inserted into XXHCC_OE_ORDERS_IFACE_STG table with status as "N"
5. Run ‘HCUS Open Sales Orders Load' program in Validate mode to validate.
6. All the validated records will have their status = "L"
7. Once all the validations are successful user can run the ‘Order Import' program to loda the data into base tables.
6. The error records and successful records in the standard interface program will also be written in the Output file.
-- ---------------------------------------------------------------
-- This script creates XXHCC_OE_ORDERS_IFACE_STG table --
-- ---------------------------------------------------------------
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE XXHCC.XXHCC_OE_ORDERS_IFACE_STG';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
Create Table XXHCC.XXHCC_OE_ORDERS_IFACE_STG
(
SOURCE VARCHAR2(40) ,
ORDER_SOURCE VARCHAR2(240) ,
ORIG_SYS_DOCUMENT_REF VARCHAR2(50) ,
ORG_NAME VARCHAR2(50) ,
CUSTOMER_PO_NUMBER VARCHAR2(50) ,
ORDERED_DATE DATE ,
ORDER_TYPE VARCHAR2(40) ,
OMNI_PRICE Varchar2(80) ,
TRANSACTIONAL_CURR VARCHAR2(80) ,
CONVERSION_RATE_DATE DATE ,
CONVERSION_TYPE_CODE VARCHAR2(40) ,
ORIG_SYS_CUSTOMER_NAME VARCHAR2(240) ,
ORIG_SHIP_ADDRESS_REF VARCHAR2(50) ,
TAX_EXEMPT_FLAG VARCHAR2(1) ,
FOB VARCHAR2(240) ,
SHIP_FROM_ORG VARCHAR2(240) ,
LINE_NUMBER NUMBER ,
INVENTORY_ITEM VARCHAR2(240) ,
ITEM_TYPE_CODE VARCHAR2(40) ,
ORDERED_QUANTITY NUMBER ,
ORDERED_QUANTITY_UOM VARCHAR2(40) ,
REQUEST_DATE DATE ,
PROMISE_DATE DATE ,
UNIT_SELLING_PRICE NUMBER ,
ORIG_SYS_LINE_DOCUMENT_REF NUMBER ,
TAX_EXEMPT_REASON VARCHAR2(240) ,
ORIG_BILL_ADDRESS_REF VARCHAR2(50) ,
SOLD_TO_ORG VARCHAR2(240) ,
SOLD_FROM_ORG VARCHAR2(240) ,
SHIP_TO_ORG VARCHAR2(240) ,
INVOICE_TO_ORG VARCHAR2(240) ,
BOOKED_FLAG VARCHAR2(1) ,
ORDER_CATEGORY VARCHAR2(240) ,
CALCULATE_PRICE_FLAG VARCHAR2(1) ,
SALESREP VARCHAR2(240) ,
TAX_CODE VARCHAR2(50) ,
ORG_ID NUMBER ,
ORDER_SOURCE_ID NUMBER ,
ORDER_TYPE_ID NUMBER ,
LINE_TYPE_ID NUMBER ,
PRICE_LIST_ID NUMBER ,
SALESREP_ID NUMBER ,
PAYMENT_TERM_ID NUMBER ,
SOLD_TO_ORG_ID NUMBER ,
SOLD_FROM_ORG_ID NUMBER ,
SHIP_FROM_ORG_ID NUMBER ,
SHIP_TO_ORG_ID NUMBER ,
INVOICE_TO_ORG_ID NUMBER ,
LAST_UPDATED_BY NUMBER ,
LAST_UPDATE_DATE DATE ,
CREATED_BY NUMBER ,
CREATION_DATE DATE ,
XXHCC_TRANSACTION_ID NUMBER ,
BATCH_NAME VARCHAR2(80) ,
LOAD_STATUS VARCHAR2(1)
);
BEGIN
EXECUTE IMMEDIATE 'DROP SYNONYM APPS.XXHCC_OE_ORDERS_IFACE_STG';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE SYNONYM APPS.XXHCC_OE_ORDERS_IFACE_STG FOR XXHCC.XXHCC_OE_ORDERS_IFACE_STG;
BEGIN
EXECUTE IMMEDIATE 'DROP SEQUENCE APPS.XXHCC_OE_ORDERS_IFACE_SEQ';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE SEQUENCE APPS.XXHCC_OE_ORDERS_IFACE_SEQ
MINVALUE 1 MAXVALUE 999999999999999999999999999
INCREMENT BY 1
START WITH 401
CACHE 20 NOORDER NOCYCLE;
Control file
---------------------
OPTIONS (BINDSIZE =125653,SKIP=1)
LOAD DATA
INFILE '$XXHCC_TOP/bin/XXHCC_CONV_SO_LOAD.csv'
TRUNCATE
INTO table APPS.XXHCC_OE_ORDERS_IFACE_STG
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
order_source "trim(:order_source)",
orig_sys_document_ref "trim(:orig_sys_document_ref)",
org_name "trim(:org_name)",
customer_po_number "trim(:customer_po_number)",
ordered_date "TO_DATE(TRIM(:ordered_date),'DD/MM/YYYY')",
order_type "trim(:order_type)",
OMNI_PRICE "trim(:OMNI_PRICE)",
transactional_curr "trim(:transactional_curr)",
orig_sys_customer_name "trim(:orig_sys_customer_name)",
orig_ship_address_ref "trim(:orig_ship_address_ref)",
tax_exempt_flag "trim(:tax_exempt_flag)",
fob "trim(:fob)",
ship_from_org "trim(nvl(:ship_from_org,'HCC'))",
line_number "trim(:line_number)",
inventory_item "trim(:inventory_item)",
item_type_code "trim(UPPER(:item_type_code))",
Ordered_quantity "trim(:Ordered_quantity)",
Ordered_quantity_uom "trim(:Ordered_quantity_uom)",
request_date "TO_DATE(TRIM(:request_date),'DD/MM/YYYY')",
promise_date "TO_DATE(TRIM(:promise_date),'DD/MM/YYYY')",
unit_selling_price "trim(:unit_selling_price)",
orig_sys_line_document_ref "trim(:orig_sys_line_document_ref)",
tax_exempt_reason "trim(:tax_exempt_reason)",
orig_bill_address_ref "trim(:orig_bill_address_ref)",
sold_to_org "trim(:sold_to_org)",
sold_from_org "trim(:sold_from_org)",
ship_TO_org "trim(:ship_TO_org)",
invoice_TO_org "trim(:invoice_TO_org)",
booked_flag "trim(:booked_flag)",
order_category "trim(:order_category)",
calculate_price_flag "trim(:calculate_price_flag)",
salesrep "trim(:salesrep)",
tax_code "trim(:tax_code)",
LAST_UPDATED_BY "FND_GLOBAL.USER_ID",
LAST_UPDATE_DATE SYSDATE,
CREATED_BY "FND_GLOBAL.USER_ID",
CREATION_DATE SYSDATE,
XXhcc_TRANSACTION_ID "APPS.XXHCC_oe_orders_iface_seq.NEXTVAL",
batch_name "SYSDATE||'-'||1",
LOAD_STATUS CONSTANT "N"
)
-- ---------------------------------------------------------------
-- This script creates XXHCC_FIN_CONV_ERROR table --
-- ---------------------------------------------------------------
BEGIN
--null;
EXECUTE IMMEDIATE 'DROP TABLE XXHCC.XXHCC_FIN_CONV_ERROR';
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE TABLE XXHCC.XXHCC_FIN_CONV_ERROR
( "INTERFACE_ERROR_ID" NUMBER NOT NULL ENABLE,
"PACKAGE_NAME" VARCHAR2(30 BYTE),
"PROCEDURE_NAME" VARCHAR2(30 BYTE),
"INTERFACE_TABLE_NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"XXHCC_HDR_TRANSACTION_ID" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"XXHCC_TRANSACTION_ID" VARCHAR2(100 BYTE),
"XXHCC_BATCH_NAME" VARCHAR2(80 BYTE),
"XXHCC_SOURCE" VARCHAR2(30 BYTE),
"COLUMN_NAME" VARCHAR2(250 BYTE),
"COLUMN_VALUE" VARCHAR2(250 BYTE),
"ERROR_CODE" VARCHAR2(250 BYTE),
"ERROR_MESSAGE" VARCHAR2(1000 BYTE),
"ERROR_MESSAGE_DISP" VARCHAR2(250 BYTE),
"PROGRESS" VARCHAR2(10 BYTE),
"REQUEST_ID" NUMBER(15,0),
"CREATION_DATE" DATE NOT NULL ENABLE,
"CREATED_BY" NUMBER(15,0) NOT NULL ENABLE,
"LAST_UPDATE_DATE" DATE NOT NULL ENABLE,
"LAST_UPDATED_BY" NUMBER(15,0) NOT NULL ENABLE
) ;
BEGIN
EXECUTE IMMEDIATE 'DROP SYNONYM APPS.XXHCC_FIN_CONV_ERROR';
--null;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
CREATE OR REPLACE SYNONYM APPS.XXHCC_FIN_CONV_ERROR FOR XXHCC.XXHCC_FIN_CONV_ERROR;
CREATE OR REPLACE PACKAGE XXHCC_OE_CONV_SALESORDER_PKG
/*===============================================================================================================*/
--| |
--|Project : |
--|Task : |
--|Application : Oracle Sales Orders |
--|Script : XXHCC_OE_CONV_SALESORDER_PKG.pks |
--|Created By : Reuse Team |
--|Date Created : 10-SEP-2012 |
--|Description : Creates the Package : XXHCC_OE_CONV_SALESORDER_PKG and Run this script from the APPS schema. |
--|Parameters : |
--|Inputs : |
--|Outputs : |
--+============================================================================================== |
--|Modification History |
--|Person Date Version Comments |
--+=========== ====== ============= ====================================== |
--|Erry Swamy 10-Sep-2012 1.0 |
--+=================================================================================================================
IS
c_package_name VARCHAR2 (50);
c_procedure_name VARCHAR2 (50);
-- columns for error trapping
c_error_code VARCHAR2 (1000);
c_error_message VARCHAR2 (1000);
c_progress VARCHAR2 (10) := NULL;
/*=======================================================================
GLOBAL variables and programs for:
dbms or FND message logging
=======================================================================*/
c_log_dest VARCHAR2 (30) := --'DBMS_OUTPUT';
'CONCURRENT_PROGRAM';
-- possible values: DBMS_OUTPUT, CONCURRENT_PROGRAM
c_debug_level NUMBER := 0;
c_progress VARCHAR2 (10) := NULL;
TYPE g_error_rec IS RECORD (
c_package_name VARCHAR2 (30),
c_procedure_name VARCHAR2 (30),
c_interface_table_name VARCHAR2 (100),
c_XXHCC_hdr_transaction_id VARCHAR2 (100),
c_XXHCC_transaction_id VARCHAR2 (100),
c_XXHCC_batch_name VARCHAR2 (80),
c_XXHCC_source VARCHAR2 (30),
c_column_name VARCHAR2 (250),
c_column_value VARCHAR2 (250)
);
PROCEDURE xx_load_main (p_errbuf OUT VARCHAR2, p_retcode OUT VARCHAR2,p_batch_id in varchar2);
PROCEDURE xx_map_data(p_batch_id in varchar2);
PROCEDURE xx_validate_data(p_batch_id in varchar2);
PROCEDURE xx_load_data(p_batch_id in varchar2);
PROCEDURE xx_org_assign (p_org_id out number);
PROCEDURE xx_logit(
p_debug_level NUMBER,
p_des VARCHAR2,
p_source VARCHAR2,
p_message VARCHAR2);
PROCEDURE xx_outit (p_des VARCHAR2, p_message VARCHAR2);
PROCEDURE xx_show_error (
p_error_code IN VARCHAR2,
p_error_message IN VARCHAR2,
p_progress IN VARCHAR2,
p_package_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_err_msg_disp IN VARCHAR2);
PROCEDURE xx_trap_error (
p_error_rec IN g_error_rec,
p_error_code IN VARCHAR2,
p_error_message IN VARCHAR2,
p_progress IN VARCHAR2,
p_err_msg_disp IN VARCHAR2
);
PROCEDURE xx_error_rep (p_batch_id in varchar2);
END XXHCC_OE_CONV_SALESORDER_PKG;
/
show errors;
CREATE OR REPLACE
PACKAGE BODY XXHCC_OE_CONV_SALESORDER_PKG
IS
/*===============================================================================================================*/
--| |
--|Project : |
--|Task : |
--|Application : Oracle Sales Orders |
--|Script : XXHCC_OE_CONV_SALESORDER_PKG.pkb |
--|Created By : Reuse Team |
--|Date Created : 10-SEP-2012 |
--|Description : Creates the Package : XXHCC_OE_CONV_SALESORDER_PKG AND Run this script FROM the APPS schema. |
--|Parameters : |
--|Inputs : |
--|Outputs : |
--+============================================================================================== |
--|ModIFication History |
--|Person Date Version Comments |
--+=========== ====== ============= ====================================== |
--|Erry Swamy 10-Sep-2012 1.0 |
--+================================================================================================================
/*=======================================================================
OBJECT NAME: xx_load_main
OBJECT TYPE: Public Procedure
DESCRIPTION:
PARAMETERS:
<<Name>> <<Data Type>> <<Description>>
RETURNS: <<Only required for Functions>>
<<Data Type>> <<Description>>
VARCHAR2 Error message
VARCHAR2 Error Code
=====================================================================*/
PROCEDURE xx_load_main(
p_errbuf OUT NOCOPY VARCHAR2,
p_retcode OUT NOCOPY VARCHAR2,
p_batch_id IN VARCHAR2)
IS
c_error_rec G_ERROR_REC;
c_progress VARCHAR2 (10);
c_org_id NUMBER:=0;
BEGIN
----------------------------------------------------------------------
---General Declarations
----------------------------------------------------------------------
c_error_rec.c_package_name := 'XXHCC_OE_CONV_SALESORDER_PKG';
c_error_rec.c_procedure_name := 'xx_load_main';
c_error_rec.c_XXHCC_batch_name := 'OE_SALESORDERS';
--
c_progress := '001';
xx_org_assign (c_org_id);
xx_map_data (P_BATCH_ID);
xx_validate_data (P_BATCH_ID);
xx_load_data (P_BATCH_ID);
xx_error_rep (P_BATCH_ID);
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
fnd_file.put_line(fnd_file.LOG,'Main Exception:'||'SQLCODE=>'||SQLCODE||'--'||'SQLERRM=>'||SQLERRM);
END xx_load_main; --END of Load Main
/*=======================================================================
OBJECT NAME: xx_map_data
OBJECT TYPE: Public Procedure
DESCRIPTION:
PARAMETERS:
<<Name>> <<Data Type>> <<Description>>
RETURNS: <<Only required for Functions>>
<<Data Type>> <<Description>>
=====================================================================*/
PROCEDURE xx_map_data(p_batch_id IN VARCHAR2)
IS
------------------------------------------------------
---------------------------------------------------------------------------
---Cursor to fetch Line records of Sales Orders
---FROM staging table XXHCC_OE_ORDERS_IFACE_STG
---------------------------------------------------------------------------
CURSOR cur_oe_order_lines_IFace ( p_batch_name IN XXHCC_oe_orders_IFace_stg.batch_name%TYPE )
IS
SELECT *
FROM XXHCC_oe_orders_IFace_stg
WHERE load_status = 'N'
AND batch_name = p_batch_name
ORDER BY orig_sys_document_ref ,
orig_sys_line_document_ref ;
-------------------------------------------------------------
-- Cursor to get the error record count---
-------------------------------------------------------------
CURSOR cur_err_rec_count (P_XXHCC_TRANSACTION_ID VARCHAR2)
IS
SELECT COUNT (*)
FROM XXHCC_fin_conv_error
WHERE XXHCC_TRANSACTION_ID = P_XXHCC_TRANSACTION_ID
AND package_name LIKE 'XXHCC_OE_CONV_SALESORDER_PKG'
AND request_id = fnd_global.conc_request_id;
c_error_rec G_ERROR_REC;
c_progress VARCHAR2 (10);
c_error_rec_count NUMBER;
c_operating_unit_name hr_operating_units.NAME%TYPE;
c_inventory_org_ship_FROM_org org_organization_definitions.organization_name%TYPE;
c_inventory_org_sold_FROM_org org_organization_definitions.organization_name%TYPE;
c_SET_of_books_id hr_operating_units.SET_of_books_id%TYPE;
c_functionac_curr gl_SETs_of_books.currency_code%TYPE;
c_order_type oe_transaction_types_tl.NAME%TYPE;
c_oc_payment_term ra_terms.NAME%TYPE;
c_oh_payment_term ra_terms.NAME%TYPE;
c_category_code hz_parties.category_code%TYPE;
c_item_id NUMBER;
d_Payment_term NUMBER;
d_customer_ref NUMBER;
c_err_rec_count NUMBER;
c_status VARCHAR2(1);
c_unit_of_measure VARCHAR2(20);
c_Currency_code_chk VARCHAR2(4);
c_org_id NUMBER:=0;
BEGIN
----------------------------------------------------------------------
---General Declarations
----------------------------------------------------------------------
c_error_rec.c_package_name := 'XXHCC_OE_CONV_SALESORDER_PKG';
c_error_rec.c_procedure_name := 'xx_map_data';
c_progress := '2000';
xx_org_assign (c_org_id);
--Start of Header Level FOR Loop
--Start of Line Level FOR Loop
FOR recur_oe_order_lines_IFace IN cur_oe_order_lines_IFace (p_batch_id)
LOOP
c_error_rec.c_XXHCC_transaction_id :=recur_oe_order_lines_IFace.orig_sys_line_document_ref;
c_error_rec.c_interface_table_name := 'XXHCC_OE_ORDERS_IFACE_STG';
c_error_rec.c_XXHCC_hdr_transaction_id:=recur_oe_order_lines_IFace.orig_sys_document_ref;
c_error_rec.c_XXHCC_transaction_id :=recur_oe_order_lines_IFace.XXHCC_TRANSACTION_ID;
c_error_rec.c_XXHCC_source :=recur_oe_order_lines_IFace.source;
c_error_rec.c_XXHCC_batch_name := p_batch_id;
BEGIN
c_operating_unit_name := NULL;
c_inventory_org_ship_FROM_org := NULL;
c_inventory_org_sold_FROM_org := NULL;
c_SET_of_books_id := NULL;
c_functionac_curr := NULL;
c_order_type := NULL;
c_oc_payment_term := NULL;
c_oh_payment_term := NULL;
----------------------------------------------------
--- To get the Customer Details
----------------------------------------------------
BEGIN
d_Payment_term := NULL;
d_customer_ref := NULL;
c_progress := '2010';
c_error_rec.c_column_name := 'Customer Name';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.orig_sys_customer_name;
BEGIN
SELECT c.cust_account_id
INTO d_customer_ref
FROM HZ_PARTIES p,
hz_cust_accounts c
WHERE p.party_id = c.party_id
AND upper(p.party_name) = Upper(recur_oe_order_lines_IFace.orig_sys_customer_name)
AND c.status = 'A'
AND rownum = 1
ORDER BY p.party_id DESC;
EXCEPTION WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'No Customer Defined' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'No Customer Defined');
END;
SELECT site_uses.Payment_Term_id
INTO d_Payment_term
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_uses ,
hz_cust_accounts cust_acct
WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.cust_account_id = cust_acct.cust_account_id
AND site_uses.site_use_code = 'BILL_TO'
AND cust_acct.cust_account_id = d_customer_ref
AND acct_site.status = 'A'
AND site_uses.status = 'A'
AND cust_acct.status = 'A'
AND acct_site.bill_to_flag = 'P'
AND site_uses.org_id = c_org_id
AND acct_site.org_id = c_org_id;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
--------------------------------------------------------------------------------------------------
--- Validating Item
--------------------------------------------------------------------------------------------------
c_progress := '2020';
c_error_rec.c_column_name := 'Inventory Item AND UOM';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.Inventory_Item;
recur_oe_order_lines_IFace.org_id := c_org_id;
BEGIN
c_item_id := 0;
SELECT msi.inventory_item_id
INTO c_item_id
FROM mtl_System_items msi ,
org_organization_definitions ood
WHERE UPPER(segment1) = recur_oe_order_lines_IFace.Inventory_Item
AND msi.organization_id =ood.organization_id
AND ood.operating_unit =recur_oe_order_lines_IFace.org_id
AND enabled_flag ='Y'
AND upper(Primary_uom_code) = upper(recur_oe_order_lines_IFace.Ordered_quantity_uom)
AND TRUNC(NVL(start_date_active,SYSDATE)) <= TRUNC(SYSDATE)
AND TRUNC(NVL(END_date_active,SYSDATE)) >= TRUNC(SYSDATE)
AND msi.organization_id = 204;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
---------------------------------------------------------------------------------------
--Get Value INTO ORG ID
--To fetch the oracle value for Org ID depENDing on the value of org_name
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2030';
c_error_rec.c_column_name := 'ORG ID';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.org_name;
recur_oe_order_lines_IFace.org_id := c_org_id;
---------------------------------------------------------------------------------------
--Get Value INTO Order Source ID
--To fetch the oracle value for Order Source ID depENDing on the value of Order Source
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2040';
c_error_rec.c_column_name := 'order_source';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.order_source;
recur_oe_order_lines_IFace.order_source_id := 0;
------------------------------------------------------------------------------------
--get order type id-------
---------------------------------------------------------------------------------
c_progress := '2050';
c_error_rec.c_column_name := 'Order Type Name';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.order_type;
BEGIN
SELECT otta.transaction_type_id
INTO recur_oe_order_lines_IFace.order_type_id
FROM oe_transaction_types_tl ottt,
oe_transaction_types_all otta
WHERE otta.transaction_type_id = ottt.transaction_type_id
AND otta.transaction_type_code = 'ORDER'
AND ottt.NAME = recur_oe_order_lines_IFace.order_type
AND ottt.language = userenv('LANG')
AND TRUNC (NVL (otta.start_date_active, SYSDATE)) <= TRUNC (SYSDATE)
AND TRUNC (NVL (otta.end_date_active, SYSDATE)) >= TRUNC (SYSDATE)
AND otta.org_id = recur_oe_order_lines_IFace.org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'NO_DATA_FOUND' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'NO_DATA_FOUND' );
WHEN TOO_MANY_ROWS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'Order Type - Too many rows' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'Order Type - Too many rows');
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'Order Type - Others' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'Order Type - Others');
END;
------------------------------------------------------------------------------------
--get Line type id-------
---------------------------------------------------------------------------------
c_progress := '2060';
c_error_rec.c_column_name := 'Line Type Name';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.Line_type_id;
IF recur_oe_order_lines_IFace.order_type_id IS NOT NULL THEN
BEGIN
SELECT default_outbound_line_type_id
INTO recur_oe_order_lines_IFace.Line_type_id
FROM oe_transaction_types_all
WHERE org_id = recur_oe_order_lines_IFace.org_id
AND transaction_type_id = recur_oe_order_lines_IFace.order_type_id;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
END IF;
----------------------------------------------------------------------------------
--MAP SALESREP NAME
--To fetch the oracle value for Sales rep ID depENDing on the value of Sales Rep
----------------------------------------------------------------------------------
c_progress := '2070';
c_error_rec.c_column_name := 'Sales Rep Name';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.salesrep;
recur_oe_order_lines_IFace.salesrep_id := -3;
----------------------------------------------------------------------------------
c_progress := '2080';
c_error_rec.c_column_name := 'payment_term Name';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.payment_term_id;
recur_oe_order_lines_IFace.payment_term_id := d_Payment_term;
-----------------------------------------------------------------------------------
--Get CUSTOMER ID(Sold to org ID)
--To fetch the oracle value for Customer ID depENDing on the value of Customer reference
-----------------------------------------------------------------------------------
c_progress := '2090';
c_error_rec.c_column_name := 'Customer Reference';
c_error_rec.c_column_value :=recur_oe_order_lines_IFace.sold_to_org;
recur_oe_order_lines_IFace.sold_to_org_id := d_customer_ref;
---------------------------------------------------------------------------------------
--Get Value INTO SOLD FROM ORG ID
--To fetch the oracle value for Sold FROM Org ID depENDing on the value of Organization
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2100';
c_error_rec.c_column_name := 'SOLD FROM ORG ID';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.sold_FROM_org;
recur_oe_order_lines_IFace.sold_FROM_org_id := c_org_id;
---------------------------------------------------------------------------------------
--Get Value INTO SHIP FROM ORG ID
--To fetch the oracle value for SHIP FROM Org ID depENDing on the value of Organization
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2110';
c_error_rec.c_column_name := 'SHIP FROM ORG ID';
recur_oe_order_lines_IFace.ship_FROM_org_id := c_org_id;
---------------------------------------------------------------------------------------
--Get Value INTO Pricelist_id
--To fetch the oracle value for Pricelist ID depENDing on the value of Customer
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2120';
c_error_rec.c_column_name := 'Pricelist ID';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.price_list_id;
BEGIN
SELECT site_uses.price_list_id
INTO recur_oe_order_lines_IFace.price_list_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_uses ,
hz_cust_accounts cust_acct
WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.cust_account_id = cust_acct.cust_account_id
AND site_uses.site_use_code = 'SHIP_TO'
AND cust_acct.cust_account_id = d_customer_ref
AND acct_site.status = 'A'
AND site_uses.status = 'A'
AND cust_acct.status = 'A'
AND acct_site.ship_to_flag = 'P'
AND site_uses.org_id = recur_oe_order_lines_IFace.org_id
AND acct_site.org_id = recur_oe_order_lines_IFace.org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'NO_DATA_FOUND-price_list_id' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'NO_DATA_FOUND- price_list_id' );
WHEN TOO_MANY_ROWS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'price_list_id- Too many rows' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'price_list_id - Too many rows');
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'price_list_id - Others' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'price_list_id - Others');
END ;
IF recur_oe_order_lines_IFace.price_list_id IS NULL THEN
BEGIN
SELECT Price_list_id
INTO recur_oe_order_lines_IFace.price_list_id
FROM oe_transaction_types_all
WHERE org_id = recur_oe_order_lines_IFace.org_id
AND transaction_type_id = recur_oe_order_lines_IFace.order_type_id;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
END IF;
IF recur_oe_order_lines_IFace.price_list_id IS NOT NULL THEN
c_Currency_code_chk := NULL;
BEGIN
SELECT Currency_code
INTO c_Currency_code_chk
FROM QP_PRICE_LISTS_V
WHERE price_list_id = recur_oe_order_lines_IFace.price_list_id
AND Currency_code = recur_oe_order_lines_IFace.TRANSACTIONAL_CURR;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'Pricelist Currency dIFferent than SO Currency' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'Pricelist Currency dIFferent than SO Currency' );
END;
END IF;
---------------------------------------------------------------------------------------
--Get Value INTO SHIP TO ORG ID
--To fetch the oracle value for SHIP TO Org ID depENDing on the value of Organization
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2130';
c_error_rec.c_column_name := 'SHIP TO ORG ID';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.ship_TO_org;
BEGIN
SELECT site_uses.site_use_id
INTO recur_oe_order_lines_IFace.ship_TO_org_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_uses ,
hz_cust_accounts cust_acct
WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.cust_account_id = cust_acct.cust_account_id
AND site_uses.site_use_code = 'SHIP_TO'
AND cust_acct.cust_account_id = d_customer_ref--
-- AND acct_site.orig_system_reference = p_ship_to_address_ref
AND acct_site.status = 'A'
AND site_uses.status = 'A'
AND cust_acct.status = 'A'
AND acct_site.ship_to_flag = 'P'
AND site_uses.org_id = recur_oe_order_lines_IFace.org_id
AND acct_site.org_id = recur_oe_order_lines_IFace.org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'NO_DATA_FOUND-ship_TO_org' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'NO_DATA_FOUND- ship_TO_org' );
WHEN TOO_MANY_ROWS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'ship_TO_org- Too many rows' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'ship_TO_org - Too many rows');
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'ship_TO_org - Others' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'ship_TO_org - Others');
END ;
---------------------------------------------------------------------------------------
--Get Value INTO invoice TO ORG ID
--To fetch the oracle value for invoice TO Org ID depENDing on the value of Organization
--obtained FROM above map function
---------------------------------------------------------------------------------------
c_progress := '2140';
c_error_rec.c_column_name := 'invoice TO ORG ID';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.invoice_TO_org;
BEGIN
SELECT site_uses.site_use_id
INTO recur_oe_order_lines_IFace.invoice_TO_org_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_uses ,
hz_cust_accounts cust_acct
WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.cust_account_id = cust_acct.cust_account_id
AND site_uses.site_use_code = 'BILL_TO'
AND cust_acct.cust_account_id = d_customer_ref
--- AND acct_site.orig_system_reference = p_bill_to_address_ref
AND acct_site.status = 'A'
AND site_uses.status = 'A'
AND cust_acct.status = 'A'
AND acct_site.bill_to_flag = 'P'
AND site_uses.org_id = recur_oe_order_lines_IFace.org_id
AND acct_site.org_id = recur_oe_order_lines_IFace.org_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'NO_DATA_FOUND-invoice_TO_org' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'NO_DATA_FOUND- invoice_TO_org' );
WHEN TOO_MANY_ROWS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'invoice_TO_org- Too many rows' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'invoice_TO_org - Too many rows');
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'invoice_TO_org - Others' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'invoice_TO_org - Others');
END ;
-----------------------------------------------------------------------------------
--Need to move above the main header for loop
--MAP TRANSACTIONAL_CURR
--To fetch the values for Conversion Rate, Conversion Rate Date AND Conversion Type Code
--depENDing on non-equality of Transactional Curr AND Functional Currency Code
-----------------------------------------------------------------------------------
BEGIN
c_progress := '2150';
SELECT SET_of_books_id
INTO c_SET_of_books_id
FROM hr_operating_units
WHERE organization_id = recur_oe_order_lines_IFace.org_id;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value ,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
c_functionac_curr := 'GBP';
c_progress := '2160';
c_error_rec.c_column_name := 'Transactional Curr';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.TRANSACTIONAL_CURR;
IF (recur_oe_order_lines_IFace.TRANSACTIONAL_CURR <> c_functionac_curr ) THEN
recur_oe_order_lines_IFace.conversion_rate_date := TRUNC(sysdate);
recur_oe_order_lines_IFace.conversion_type_code := 'Corporate';
END IF;
-----------------------------------------------------------------------------------
OPEN cur_err_rec_count (recur_oe_order_lines_IFace.XXHCC_TRANSACTION_ID);
FETCH cur_err_rec_count INTO c_err_rec_count;
CLOSE cur_err_rec_count;
IF c_err_rec_count > 0 THEN
c_status := 'E';
ELSE
c_status := 'M';
END IF;
BEGIN
UPDATE XXHCC_oe_orders_IFace_stg
SET org_id = recur_oe_order_lines_IFace.org_id,
order_source_id = recur_oe_order_lines_IFace.order_source_id,
order_type_id = recur_oe_order_lines_IFace.order_type_id,
Line_type_id = recur_oe_order_lines_IFace.Line_type_id,
price_list_id = recur_oe_order_lines_IFace.price_list_id,
salesrep_id = recur_oe_order_lines_IFace.salesrep_id,
payment_term_id = recur_oe_order_lines_IFace.payment_term_id,
sold_to_org_id = recur_oe_order_lines_IFace.sold_to_org_id,
sold_FROM_org_id = recur_oe_order_lines_IFace.sold_FROM_org_id,
ship_FROM_org_id = recur_oe_order_lines_IFace.ship_FROM_org_id,
ship_TO_org_id = recur_oe_order_lines_IFace.ship_TO_org_id,
invoice_TO_org_id = recur_oe_order_lines_IFace.invoice_TO_org_id,
load_status = c_status
WHERE orig_sys_document_ref = recur_oe_order_lines_IFace.orig_sys_document_ref
AND orig_sys_line_document_ref = recur_oe_order_lines_IFace.orig_sys_line_document_ref
AND batch_name = recur_oe_order_lines_IFace.batch_name
AND org_name = recur_oe_order_lines_IFace.org_name;
EXCEPTION WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
BEGIN
UPDATE XXHCC_oe_orders_IFace_stg
SET conversion_rate_date = recur_oe_order_lines_IFace.conversion_rate_date,
conversion_type_code = recur_oe_order_lines_IFace.conversion_type_code,
last_UPDATE_date = SYSDATE,
last_UPDATEd_by = fnd_global.user_id
WHERE orig_sys_document_ref = recur_oe_order_lines_IFace.orig_sys_document_ref
AND orig_sys_line_document_ref = recur_oe_order_lines_IFace.orig_sys_line_document_ref
AND org_name = recur_oe_order_lines_IFace.org_name
AND batch_name = recur_oe_order_lines_IFace.batch_name
AND recur_oe_order_lines_IFace.TRANSACTIONAL_CURR <> c_functionac_curr;
EXCEPTION WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
EXCEPTION
WHEN OTHERS THEN
--Updating Status Flag to 'E' IF errors
UPDATE XXHCC_oe_orders_IFace_stg
SET load_status = 'E',
last_UPDATE_date = SYSDATE,
last_UPDATEd_by = fnd_global.user_id
WHERE orig_sys_line_document_ref = recur_oe_order_lines_IFace.orig_sys_line_document_ref;
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
END LOOP;
--END of Line Level Loop
--END of Header Level Loop
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END xx_map_data; --END of Map Data Procedure
/*=======================================================================
OBJECT NAME: xx_validate_data
OBJECT TYPE: <<Public/Private>> <<Function/Procedure>>
DESCRIPTION:
PARAMETERS:
<<Name>> <<Data Type>> <<Description>>
RETURNS: <<Only required for Functions>>
<<Data Type>> <<Description>>
=====================================================================*/
PROCEDURE xx_validate_data(
p_batch_id IN VARCHAR2)
IS
------------------------------------------------------
---Cursor to fetch Header records of Sales Orders
---FROM staging table XXHCC_OE_ORDERS_IFACE_STG
------------------------------------------------------
---------------------------------------------------------------------------
---Cursor to fetch Line records of Sales Orders
---FROM staging table XXHCC_OE_ORDERS_IFACE_STG
---------------------------------------------------------------------------
CURSOR cur_oe_order_lines_IFace ( p_batch_id IN VARCHAR2 )
IS
SELECT *
FROM XXHCC_oe_orders_IFace_stg
WHERE load_status = 'M'
AND batch_name = p_batch_id
ORDER BY orig_sys_document_ref ,
orig_sys_line_document_ref ;
-------------------------------------------------------------
-- Cursor to get the error record count---
-------------------------------------------------------------
CURSOR cur_err_rec_count (p_XXHCC_transaction_id VARCHAR2)
IS
SELECT COUNT (*)
FROM XXHCC_fin_conv_error
WHERE XXHCC_TRANSACTION_ID = p_XXHCC_transaction_id
AND package_name LIKE 'XXHCC_OE_CONV_SALESORDER_PKG'
AND request_id = fnd_global.conc_request_id;
c_error_rec G_ERROR_REC;
c_progress VARCHAR2 (10);
c_error_rec_count NUMBER;
c_sqlcode VARCHAR2 (2000);
c_sqlerrm VARCHAR2 (2000);
c_err_rec_count NUMBER;
c_status VARCHAR2(1);
c_order_ref_base oe_order_headers_all.orig_sys_document_ref%TYPE;
c_order_ref_iface oe_headers_iface_all.orig_sys_document_ref%TYPE;
c_org_id NUMBER:=0;
l_flag NUMBER (1);
l_count NUMBER (15);
BEGIN
/*----------------------------------------------------------------------
---General Declarations
----------------------------------------------------------------------*/
c_error_rec.c_package_name := 'XXHCC_OE_CONV_SALESORDER_PKG';
c_error_rec.c_procedure_name := 'xx_validate_data';
c_progress := '3000';
xx_org_assign (c_org_id);
--Start of Line Level Loop
FOR recur_oe_order_lines_IFace IN cur_oe_order_lines_IFace (p_batch_id)
LOOP
c_error_rec.c_XXHCC_transaction_id :=recur_oe_order_lines_IFace.ORIG_SYS_LINE_DOCUMENT_REF;
c_error_rec.c_interface_table_name := 'XXHCC_OE_ORDERS_IFACE_STG';
c_error_rec.c_XXHCC_hdr_transaction_id:=recur_oe_order_lines_IFace.orig_sys_document_ref;
c_error_rec.c_XXHCC_source :=recur_oe_order_lines_IFace.source;
c_error_rec.c_XXHCC_batch_name := p_batch_id;
----------------------------------
--Validate Duplicate ORDER NUMBER
----------------------------------
c_progress := '3010';
c_error_rec.c_column_name := 'ORDER NUMBER REFERENCE';
c_error_rec.c_column_value := recur_oe_order_lines_IFace.orig_sys_document_ref;
BEGIN
SELECT orig_sys_document_ref
INTO c_order_ref_base
FROM oe_order_headers_all
WHERE orig_sys_document_ref = recur_oe_order_lines_IFace.orig_sys_document_ref
AND org_id = recur_oe_order_lines_IFace.org_id
AND order_type_id = recur_oe_order_lines_IFace.order_type_id;
EXCEPTION
WHEN OTHERS THEN
l_flag := 1;
END;
IF (c_order_ref_base = recur_oe_order_lines_IFace.orig_sys_document_ref) THEN
c_sqlcode := '0';
c_sqlcode := 'Order Reference already exists in Oracle';
/*xx_show_error (p_error_code => c_sqlcode,
p_error_message => c_sqlcode,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.g_XXXXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'Duplicate Order Reference - Base Tables' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => c_sqlcode,
p_error_message => c_sqlcode,
p_progress => c_progress,
p_err_msg_disp => 'Duplicate Order Reference - Base Tables' );*/
END IF;
BEGIN
SELECT orig_sys_document_ref
INTO c_order_ref_base
FROM oe_headers_iface_all
WHERE orig_sys_document_ref = recur_oe_order_lines_IFace.orig_sys_document_ref
AND org_id = recur_oe_order_lines_IFace.org_id
AND order_type_id = recur_oe_order_lines_IFace.order_type_id;
EXCEPTION
WHEN OTHERS THEN
null;
END;
IF (c_order_ref_base = recur_oe_order_lines_IFace.orig_sys_document_ref) THEN
c_sqlcode := '0';
c_sqlcode := 'Order Reference already exists in Interface Tables';
/*
xx_show_error (p_error_code => c_sqlcode, p_error_message => c_sqlcode, p_progress => p_progress, p_package_name => p_error_rec.c_package_name || '.' || p_error_rec.c_procedure_name, p_column_name => p_error_rec.g_XXXXHCC_hdr_transaction_id || '/' || p_error_rec.c_column_name || '/' || p_error_rec.c_column_value, p_err_msg_disp => 'Duplicate Order Reference - Interface Tables' );
xx_trap_error (p_error_rec => p_error_rec, p_error_code => c_sqlcode, p_error_message => c_sqlcode, p_progress => p_progress, p_err_msg_disp => 'Duplicate Order Reference - Interface Tables' );
*/
END IF;
OPEN cur_err_rec_count (recur_oe_order_lines_IFace.XXHCC_TRANSACTION_ID);
FETCH cur_err_rec_count INTO c_err_rec_count;
CLOSE cur_err_rec_count;
IF c_err_rec_count > 0 THEN
c_status := 'E';
ELSE
c_status := 'V';
END IF;
-- updating the status flag to 'E' IF not valid record
UPDATE XXHCC_oe_orders_IFace_stg
SET load_status = c_status,
last_UPDATE_date = SYSDATE,
last_UPDATEd_by = fnd_global.user_id
WHERE orig_sys_document_ref = recur_oe_order_lines_IFace.orig_sys_document_ref
AND orig_sys_line_document_ref = recur_oe_order_lines_IFace.orig_sys_line_document_ref
AND org_name = recur_oe_order_lines_IFace.org_name
AND batch_name = recur_oe_order_lines_IFace.batch_name;
END LOOP; --END of Line Level Loop
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END xx_validate_data; --END of Validate Data Procedure
/*=======================================================================
OBJECT NAME: xx_load_data
OBJECT TYPE: <<Public/Private>> <<Function/Procedure>>
DESCRIPTION:
PARAMETERS:
<<Name>> <<Data Type>> <<Description>>
RETURNS: <<Only required for Functions>>
<<Data Type>> <<Description>>
=====================================================================*/
PROCEDURE xx_load_data(
p_batch_id IN VARCHAR2)
IS
------------------------------------------------------
---Cursor to fetch Header records of Sales Orders
---FROM staging table XXHCC_OE_ORDERS_IFACE_STG
------------------------------------------------------
CURSOR cur_oe_order_headers_IFace( p_batch_id IN VARCHAR2)
IS
SELECT DISTINCT order_source_id, -- PEUS Open Sales Order Migration
orig_sys_document_ref,
org_id,
ordered_date,
order_type_id,
price_list_id,
salesrep_id,
customer_po_number,
sold_to_org_id ,
ship_FROM_org_id ,
ship_to_org_id ,
invoice_to_org_id ,
order_category ,
sold_FROM_org_id,
booked_flag,
TRANSACTIONAL_CURR,
tax_exempt_flag,
tax_exempt_reason
FROM XXHCC_oe_orders_IFace_stg
WHERE load_status = 'V'
AND batch_name = p_batch_id
ORDER BY orig_sys_document_ref;
---------------------------------------------------------------------------
---Cursor to fetch Line records of Sales Orders
---FROM staging table XXHCC_OE_ORDERS_IFACE_STG
---------------------------------------------------------------------------
CURSOR cur_oe_order_lines_IFace ( p_oh_orig_sys_document_ref IN XXHCC_oe_orders_IFace_stg.orig_sys_document_ref%TYPE, p_batch_id IN VARCHAR2 )
IS
SELECT DISTINCT orig_sys_document_ref,
orig_sys_line_document_ref,
line_type_id,
Ordered_quantity,
Ordered_quantity_uom,
request_date,
promise_date,
unit_selling_price,
calculate_price_flag,
org_id,
item_type_code,
inventory_item,
order_source_id,
TRANSACTIONAL_CURR,
FOB
FROM XXHCC_oe_orders_IFace_stg
WHERE load_status = 'V'
AND ORIG_SYS_DOCUMENT_REF = p_oh_orig_sys_document_ref
AND batch_name = p_batch_id;
-------------------------------------------------------------
-- Cursor to get the error record count---
-------------------------------------------------------------
CURSOR cur_err_rec_count (p_XXHCC_hdr_transaction_id VARCHAR2)
IS
SELECT COUNT (*)
FROM XXHCC_fin_conv_error
WHERE XXHCC_HDR_TRANSACTION_ID = p_XXHCC_hdr_transaction_id
AND package_name LIKE 'XXHCC_OE_CONV_SALESORDER_PKG'
AND request_id = fnd_global.conc_request_id;
c_error_rec G_ERROR_REC;
c_progress VARCHAR2 (10);
c_error_rec_count NUMBER;
c_calculate_price_flag oe_lines_IFace_all.calculate_price_flag%TYPE;
c_precision NUMBER;
c_unit_list_price NUMBER;
c_error_occured_at_line VARCHAR2(1):='N';
c_unit_selling_price NUMBER;
c_err_rec_count NUMBER;
c_status VARCHAR2(1);
BEGIN
----------------------------------------------------------------------
---General Declarations
----------------------------------------------------------------------
c_error_rec.c_package_name := 'XXHCC_OE_CONV_SALESORDER_PKG';
c_error_rec.c_procedure_name := 'xx_load_data';
-- DELETE FROM OE_LINES_IFACE_ALL WHERE created_by = fnd_global.user_id;
-- DELETE FROM OE_Headers_IFACE_ALL WHERE created_by = fnd_global.user_id;
COMMIT;
c_progress := '4000';
--Start of Header Level Loop
FOR recur_oe_order_headers_IFace IN cur_oe_order_headers_IFace(p_batch_id)
LOOP
c_error_occured_at_line :='N';
c_error_rec.c_interface_table_name := 'XXHCC_OE_ORDERS_IFACE_STG';
c_error_rec.c_XXHCC_hdr_transaction_id:=recur_oe_order_headers_IFace.orig_sys_document_ref;
c_error_rec.c_XXHCC_batch_name := p_batch_id;
BEGIN
--Start of Line Level Loop
FOR recur_oe_order_lines_IFace IN cur_oe_order_lines_IFace (recur_oe_order_headers_IFace.orig_sys_document_ref,p_batch_id)
LOOP
c_error_rec.c_XXHCC_transaction_id :=recur_oe_order_lines_IFace.orig_sys_line_document_ref;
c_progress := '4010';
BEGIN
--Get Currency Precision
--------------------------
BEGIN
SELECT PRECISION
INTO c_precision
FROM fnd_currencies
WHERE currency_code = recur_oe_order_lines_IFace.TRANSACTIONAL_CURR;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
c_unit_selling_price := ROUND (recur_oe_order_lines_IFace.unit_selling_price, c_precision );
c_calculate_price_flag :=recur_oe_order_lines_IFace.calculate_price_flag;
IF c_error_occured_at_line = 'N' THEN -- IF error occured at least at one of lines no other line should be loaded in that order
--insert stmts for lines
INSERT
INTO OE_LINES_IFACE_ALL
(
ORIG_SYS_DOCUMENT_REF ,
ORIG_SYS_LINE_REF ,
LINE_TYPE_ID ,
ordered_quantity,
ORDER_QUANTITY_UOM ,
REQUEST_DATE ,
PROMISE_DATE ,
UNIT_LIST_PRICE ,
UNIT_SELLING_PRICE ,
SHIPMENT_NUMBER ,
CALCULATE_PRICE_FLAG,
operation_code,
created_by,
creation_date,
last_UPDATEd_by,
last_UPDATE_date,
org_id,
item_type_code,
INVENTORY_ITEM ,
order_source_id,
link_to_line_ref,
--top_modec_line_ref,
option_flag,
option_number,
FOB_POINT
)
VALUES
(
recur_oe_order_lines_IFace.orig_sys_document_ref,
recur_oe_order_lines_IFace.orig_sys_line_document_ref,
recur_oe_order_lines_IFace.line_type_id,
recur_oe_order_lines_IFace.Ordered_quantity,
recur_oe_order_lines_IFace.Ordered_quantity_uom,
recur_oe_order_lines_IFace.request_date,
recur_oe_order_lines_IFace.promise_date,
c_unit_selling_price,
c_unit_selling_price,
NULL,
NVL(recur_oe_order_lines_IFace.calculate_price_flag,'N'),
'INSERT',
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate ,
recur_oe_order_lines_IFace.org_id,
recur_oe_order_lines_IFace.item_type_code,
recur_oe_order_lines_IFace.inventory_item,
recur_oe_order_lines_IFace.order_source_id,
NULL,
--NULL,
NULL,
NULL,
recur_oe_order_lines_IFace.FOB
);
END IF;
EXCEPTION
WHEN OTHERS THEN
c_error_occured_at_line := 'Y';
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
END LOOP; --END of Line Level Loop
c_progress := '4050';
-- line look block ENDed AND header look block continues
--inserting header interface table
IF c_error_occured_at_line = 'N' THEN
INSERT
INTO oe_headers_IFace_all
(
order_source_id ,
orig_sys_document_ref,
org_id ,
ordered_date ,
order_type_id ,
price_list_id ,
salesrep_id ,
customer_po_number ,
sold_to_org_id ,
ship_FROM_org_id ,
ship_to_org_id ,
invoice_to_org_id ,
created_by ,
creation_date ,
last_UPDATEd_by ,
last_UPDATE_date ,
operation_code ,
order_category ,
sold_FROM_org_id ,
QUOTE_NUMBER ,
quote_date,
booked_flag,
TRANSACTIONAL_CURR_CODE,
TAX_EXEMPT_FLAG,
tax_exempt_reason
)
VALUES
(
recur_oe_order_headers_IFace.order_source_id, -- PEUS Open Sales Order Migration
recur_oe_order_headers_IFace.orig_sys_document_ref,
recur_oe_order_headers_IFace.org_id,
recur_oe_order_headers_IFace.ordered_date,
recur_oe_order_headers_IFace.order_type_id,
recur_oe_order_headers_IFace.price_list_id,
recur_oe_order_headers_IFace.salesrep_id,
recur_oe_order_headers_IFace.customer_po_number,
recur_oe_order_headers_IFace.sold_to_org_id ,
recur_oe_order_headers_IFace.ship_FROM_org_id ,
recur_oe_order_headers_IFace.ship_to_org_id ,
recur_oe_order_headers_IFace.invoice_to_org_id,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
sysdate,
'INSERT',
recur_oe_order_headers_IFace.order_category ,
recur_oe_order_headers_IFace.sold_FROM_org_id,
NULL ,
NULL,
NVL(recur_oe_order_headers_IFace.booked_flag,'Y'),
recur_oe_order_headers_IFace.TRANSACTIONAL_CURR,
recur_oe_order_headers_IFace.tax_exempt_flag,
recur_oe_order_headers_IFace.tax_exempt_reason
);
OPEN cur_err_rec_count (recur_oe_order_headers_IFace.orig_sys_document_ref);
FETCH cur_err_rec_count INTO c_err_rec_count;
CLOSE cur_err_rec_count;
IF c_err_rec_count > 0 THEN
ROLLBACK;
c_status := 'E';
ELSE
c_status := 'L';
END IF;
--Updating the status flag to 'L' upon successful insertion INTO interface table
UPDATE XXHCC_oe_orders_IFace_stg
SET load_status = c_status,
last_UPDATE_date = SYSDATE,
last_UPDATEd_by = fnd_global.user_id
WHERE orig_sys_document_ref = recur_oe_order_headers_IFace.orig_sys_document_ref
AND batch_name = p_batch_id
AND org_id = recur_oe_order_headers_IFace.org_id;
ELSE
ROLLBACK;
UPDATE XXHCC_oe_orders_IFace_stg
SET load_status = 'E',
last_UPDATE_date = SYSDATE,
last_UPDATEd_by = fnd_global.user_id
WHERE orig_sys_document_ref = recur_oe_order_headers_IFace.orig_sys_document_ref
AND batch_name = p_batch_id
AND org_id = recur_oe_order_headers_IFace.org_id;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
UPDATE XXHCC_oe_orders_IFace_stg
SET load_status = 'E',
last_UPDATE_date = SYSDATE,
last_UPDATEd_by = fnd_global.user_id
WHERE orig_sys_document_ref = recur_oe_order_headers_IFace.orig_sys_document_ref
AND batch_name = p_batch_id
AND org_id = recur_oe_order_headers_IFace.org_id;
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END;
END LOOP; --END of Header Level Loop
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xx_show_error (p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_package_name => c_error_rec.c_package_name || '.' || c_error_rec.c_procedure_name,
p_column_name => c_error_rec.c_XXHCC_hdr_transaction_id || '/' || c_error_rec.c_column_name || '/' || c_error_rec.c_column_value,
p_err_msg_disp => 'User Defined EXCEPTION' );
xx_trap_error (p_error_rec => c_error_rec,
p_error_code => SQLCODE,
p_error_message => SQLERRM,
p_progress => c_progress,
p_err_msg_disp => 'User Defined EXCEPTION' );
END xx_load_data;
PROCEDURE xx_show_error(
p_error_code IN VARCHAR2,
p_error_message IN VARCHAR2,
p_progress IN VARCHAR2,
p_package_name IN VARCHAR2,
p_column_name IN VARCHAR2,
p_err_msg_disp IN VARCHAR2 )
/*============================================================================
PROCEDURE NAME: xx_show_error
DESCRIPTION: sends messages to the concurrent request LOG or SQL*Plus
output depending on the global variable: c_log_dest.
============================================================================*/
IS
BEGIN
xx_logit (c_debug_level,
c_log_dest,
p_package_name, 'Error at location: ' || p_progress || ', Error message: ' || p_error_message || ', Error Code: '
|| p_error_code || ', Value: ' || RTRIM (SUBSTR (p_column_name, 1, 50)) );
DBMS_OUTPUT.put_line (c_debug_level);
EXCEPTION
WHEN OTHERS THEN
xx_logit (c_debug_level, c_log_dest, p_package_name, SUBSTR ('Error showing errors:' || SQLERRM, 1, 250) );
NULL;
END xx_show_error;
PROCEDURE xx_trap_error(
p_error_rec IN g_ERROR_REC,
p_error_code IN VARCHAR2,
p_error_message IN VARCHAR2,
p_progress IN VARCHAR2,
p_err_msg_disp IN VARCHAR2 )
/*============================================================================
PROCEDURE NAME: xx_trap_error
DESCRIPTION: sends messages to the concurrent request LOG or SQL*Plus
output depending on the global variable: c_log_dest.
============================================================================*/
IS
PRAGMA AUTONOMOUS_TRANSACTION;
c_request_id NUMBER := fnd_global.conc_request_id;
c_user_id NUMBER := fnd_global.user_id;
BEGIN
INSERT
INTO APPS.XXHCC_fin_conv_error
(
interface_error_id,
package_name,
procedure_name,
interface_table_name,
XXHCC_hdr_transaction_id,
XXHCC_transaction_id,
XXHCC_batch_name,
XXHCC_source,
column_name,
column_value,
ERROR_CODE,
error_message,
progress,
error_message_disp,
request_id,
creation_date,
created_by,
last_update_date,
last_updated_by
)
VALUES
(
APPS.XXhCC_fin_conv_error_seq.NEXTVAL,
p_error_rec.c_package_name,
p_error_rec.c_procedure_name,
p_error_rec.c_interface_table_name,
p_error_rec.c_XXHCC_hdr_transaction_id,
p_error_rec.c_XXHCC_transaction_id ,
p_error_rec.c_XXHCC_batch_name,
p_error_rec.c_XXHCC_source,
p_error_rec.c_column_name,
p_error_rec.c_column_value,
p_error_code,
p_error_message,
p_progress,
p_err_msg_disp,
c_request_id,
SYSDATE,
NVL (c_user_id, -1),
SYSDATE,
NVL (c_user_id, -1)
);
COMMIT;
EXCEPTION
WHEN OTHERS THEN
xx_logit (c_debug_level, c_log_dest, p_error_rec.c_package_name, 'Error inserting data into XXHCC_FIN_CONV_ERROR' );
NULL;
END xx_trap_error;
PROCEDURE xx_logit
(
p_debug_level NUMBER,
p_des VARCHAR2,
p_source VARCHAR2,
p_message VARCHAR2
)
/*=======================================================================
PROCEDURE NAME: logit
DESCRIPTION: sends messages to the concurrent request LOG or SQL*Plus
output depending on the global variable: c_log_dest.
PARAMETERS:
p_debug_level:
1 = If the global variable: c_debug_level = 1,
then this message will appear in the LOG file
0 = Always writes the message to the LOG file
p_des:
DBMS_OUTPUT = writes output to std out
CONCURRENT_PROGRAM = writes output to concurrent request log
If this value is NULL, program uses global variable: g_log_des.
p_source:
Can be any 'CODE' value: progress point in the application,
program name where error occurred.
p_message:
Free-form message text of log mesages.
=======================================================================*/
IS
l_message VARCHAR2 (4000);
l_des VARCHAR2 (30);
BEGIN
l_des := NVL (p_des, c_log_dest);
-- IF p_debug_level = 0 OR (p_debug_level = 1 AND c_debug_level = 1)
IF p_debug_level = 1 THEN
IF p_source IS NULL THEN
l_message := p_message;
ELSE
l_message := p_source || ': ' || p_message;
END IF;
IF l_des = 'DBMS_OUTPUT' THEN
DBMS_OUTPUT.put_line (SUBSTR (l_message, 1, 255));
ELSIF l_des = 'CONCURRENT_PROGRAM' THEN
apps.fnd_file.put_line (apps.fnd_file.LOG, l_message);
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END xx_logit;
-----------------------------------------------------------------------------------------------
PROCEDURE xx_outit
(
p_des VARCHAR2,
p_message VARCHAR2
)
/*=======================================================================
PROCEDURE NAME: outit
DESCRIPTION: sends messages to the concurrent request OUTPUT or SQL*Plus
output depending on the global variable: c_log_dest.
PARAMETERS:
p_des:
DBMS_OUTPUT = writes output to std out
CONCURRENT_PROGRAM = writes output to concurrent request log
If this value is NULL, program uses global variable: g_log_des.
p_message:
Free-form message text of log mesages.
=======================================================================*/
IS
c_message VARCHAR2 (4000);
c_des VARCHAR2 (30);
BEGIN
c_des := NVL (p_des, c_log_dest);
c_message := p_message;
IF c_des = 'DBMS_OUTPUT' THEN
DBMS_OUTPUT.put_line (SUBSTR (c_message, 1, 255));
ELSIF c_des = 'CONCURRENT_PROGRAM' THEN
apps.fnd_file.put_line (apps.fnd_file.output, c_message);
END IF;
END xx_outit;
PROCEDURE xx_org_assign
(
p_org_id OUT NUMBER
)
IS
c_org_id NUMBER := fnd_global.org_id;
c_user_id NUMBER := fnd_global.user_id;
c_resp_id NUMBER := fnd_global.resp_id;
c_resp_ap_id NUMBER := fnd_global.resp_appl_id;
BEGIN
--fnd_profile.value('org_id');
fnd_global.apps_initialize (c_user_id, c_resp_id, c_resp_ap_id);
p_org_id:=c_org_id;
-- fnd_file.put_line (fnd_file.LOG, 'Org_id->'||p_org_id);
END xx_org_assign;
PROCEDURE xx_error_rep(
p_batch_id VARCHAR2)
IS
CURSOR cur_err_rep(c_batch_id VARCHAR2)
IS
SELECT * FROM XXHCC_fin_conv_error WHERE XXHCC_BATCH_NAME = c_batch_id;
BEGIN
fnd_file.put_line (fnd_file.LOG, '================================================================ ERROR RECORDS ======================================================');
fnd_file.put_line (fnd_file.LOG, 'INTERFACE_ERROR_ID'||':'||'PACKAGE_NAME:'||' '||'PROCEDURE_NAME:'' '||'INTERFACE_TABLE_NAME:'||' ' ||'XXHCC_HDR_TRANSACTION_ID:'||' '||'XXHCC_TRANSACTION_ID:'||' '||'COLUMN_NAME:'||' '||'COLUMN_VALUE:'||' '||'ERROR_CODE:'||' '||'ERROR_MESSAGE:' ||' '||'ERROR_MESSAGE_DISP:'||' '||'PROGRESS');
FOR i IN cur_err_rep(p_batch_id)
LOOP
fnd_file.put_line (fnd_file.LOG,i.INTERFACE_ERROR_ID||':'' '||i.PACKAGE_NAME||' '||i.PROCEDURE_NAME||' '||i.INTERFACE_TABLE_NAME||' '||i.XXHCC_HDR_TRANSACTION_ID ||' '||i.XXHCC_TRANSACTION_ID||' '||i.COLUMN_NAME||' '||i.COLUMN_VALUE||' '||i.ERROR_CODE ||' '||i.ERROR_MESSAGE||' '||i.ERROR_MESSAGE_DISP ||' '||i.PROGRESS );
END LOOP;
fnd_file.put_line (fnd_file.LOG, '================================================================ END ERROR RECORDS ==================================================');
END xx_error_rep;
--END of Load Data Procedure
END XXHCC_OE_CONV_SALESORDER_PKG;
/
show errors;
No comments:
Post a Comment