/* Sales Order Conversion */
-- Table Script---------
/*******************************************************************************
* $Header$
*
*
* Program Name : XXCSC_OM_CUSTOM_TABLES
* Program Type : SQL Scripts
* Purpose : To create header & lines staging tables,and custom error table
* To give grants on those tables to Apps
*
*
**********************************************************************************/
DROP TABLE xxhr.xxcsc_om_headers_import_stg; -- Drop the header staging table if exists
/
CREATE TABLE xxhr.xxcsc_om_headers_import_stg
(
ORDER_SOURCE_ID NUMBER,
ORIG_SYS_DOCUMENT_REF VARCHAR2(50) NOT NULL,
ORDER_SOURCE VARCHAR2(240) ,
ORG_ID NUMBER ,
OPERATING_UNIT VARCHAR2(240),
HEADER_ID NUMBER,
ORDER_NUMBER NUMBER,
ORDERED_DATE DATE ,
ORDER_TYPE_ID NUMBER,
ORDER_TYPE VARCHAR2(40) ,
PRICE_LIST_ID NUMBER,
PRICE_LIST VARCHAR2(240) ,
TRANSACTIONAL_CURR_CODE VARCHAR2(20),
SALESREP_ID NUMBER,
SALESREP VARCHAR2(240) ,
PAYMENT_TERM_ID NUMBER,
PAYMENT_TERM VARCHAR2(30) ,
SHIPMENT_PRIORITY VARCHAR2(80),
SHIPPING_METHOD_CODE VARCHAR2(20),
SHIPPING_METHOD VARCHAR2(80) ,
FREIGHT_TERMS_CODE VARCHAR2(20),
FREIGHT_TERMS VARCHAR2(80) ,
FOB_POINT_CODE VARCHAR2(20),
FOB_POINT VARCHAR2(80) ,
CUSTOMER_PO_NUMBER VARCHAR2(50),
SHIP_FROM_ORG VARCHAR2(240) ,
SHIP_FROM_ORG_ID NUMBER,
SHIP_TO_ORG VARCHAR2(50),
SHIP_TO_ORG_ID NUMBER ,
INVOICE_TO_ORG VARCHAR2(50),
INVOICE_TO_ORG_ID NUMBER ,
CUSTOMER_ID NUMBER,
CUSTOMER_NAME VARCHAR2(240) ,
CUSTOMER_NUMBER VARCHAR2(30),
SOLD_TO_ORG_ID NUMBER,
PRICING_DATE DATE,
REQUEST_DATE DATE,
CREATED_BY NUMBER(1),
CREATION_DATE DATE,
LAST_UPDATED_BY NUMBER(1),
LAST_UPDATE_DATE DATE,
REQUEST_ID NUMBER,
CROSS_REF_FLAG VARCHAR2(1),
VALIDATED_FLAG VARCHAR2(1),
PROCESSED_FLAG VARCHAR2(1)
)
/
DROP TABLE xxhr.xxcsc_om_lines_import_stg; -- Drop the line staging table if exists
/
CREATE TABLE xxhr.xxcsc_om_lines_import_stg
(
ORIG_SYS_DOCUMENT_REF VARCHAR2(50) NOT NULL,
ORDER_SOURCE_ID NUMBER,
ORIG_SYS_LINE_REF VARCHAR2(50) ,
ORG_ID NUMBER ,
OPERATING_UNIT VARCHAR2(240),
LINE_NUMBER NUMBER,
SHIPMENT_NUMBER NUMBER,
LINE_ID NUMBER,
INVENTORY_ITEM_ID NUMBER,
INVENTORY_ITEM VARCHAR2(30) ,
ORDERED_QUANTITY NUMBER,
ORDER_QUANTITY_UOM VARCHAR2(3),
SOLD_TO_ORG VARCHAR2(240),
SHIP_FROM_ORG VARCHAR2(50) ,
SHIP_FROM_ORG_ID NUMBER,
SHIP_TO_ORG VARCHAR2(50),
SHIP_TO_ORG_ID NUMBER ,
INVOICE_TO_ORG VARCHAR2(50),
INVOICE_TO_ORG_ID NUMBER ,
PRICE_LIST_ID NUMBER,
PRICE_LIST VARCHAR2(360) ,
UNIT_LIST_PRICE NUMBER,
UNIT_SELLING_PRICE NUMBER,
PAYMENT_TERM VARCHAR2(50) ,
PAYMENT_TERM_ID NUMBER,
SHIPMENT_PRIORITY VARCHAR2(50),
SHIPPING_METHOD VARCHAR2(50) ,
SHIPPING_METHOD_CODE VARCHAR2(20),
FREIGHT_TERMS VARCHAR2(80) ,
FREIGHT_TERMS_CODE VARCHAR2(20),
FOB_POINT VARCHAR2(80),
FOB_POINT_CODE VARCHAR2(20),
SALESREP VARCHAR2(80) ,
SALESREP_ID NUMBER,
CUSTOMER_PO_NUMBER NUMBER,
SOLD_FROM_ORG VARCHAR2(30),
SOLD_FROM_ORG_ID NUMBER,
SCHEDULE_SHIP_DATE DATE,
PRICING_DATE DATE,
PROMISE_DATE DATE,
REQUEST_DATE DATE,
CALCULATE_PRICE_FLAG VARCHAR2(1),
CREATED_BY NUMBER(1),
CREATION_DATE DATE,
LAST_UPDATED_BY NUMBER(1),
LAST_UPDATE_DATE DATE,
REQUEST_ID NUMBER,
CUSTOMER_NAME VARCHAR2(1000),
RETURN_REASON_CODE VARCHAR2(1000),
CROSS_REF_FLAG VARCHAR2(1),
VALIDATED_FLAG VARCHAR2(1),
PROCESSED_FLAG VARCHAR2(1)
)
/
DROP TABLE xxhr.xxcsc_order_import_errors; -- Drop the error table if exists
/
CREATE TABLE xxhr.xxcsc_order_import_errors ( Line_Number NUMBER ,
Request_id NUMBER,
validation VARCHAR2(2000),
table_name VARCHAR2(2000),
column_name VARCHAR2(2000),
error_code NUMBER,
error_desc VARCHAR2(2000) )
/
/***********************************************************************************/
-- Grant Scripts
/***********************************************************************************/
GRANT ALL ON xxhr.xxcsc_om_headers_import_stg TO APPS;
/
GRANT ALL ON xxhr.xxcsc_om_lines_import_stg TO APPS;
/
GRANT ALL ON xxhr.xxcsc_order_import_errors TO APPS;
/
---- Package----
/*******************************************************************************
* $Header$
*
*
* Program Name : XXCSC_ORDER_IMPORT_PKG
* Program Type : Package Specification
* Purpose : To declare global variable & procedures used in package body
*
*
*
*******************************************************************************/
CREATE OR REPLACE PACKAGE xxcsc_order_import_pkg
AS
PROCEDURE xxcsc_order_import_main (
Errbuf OUT VARCHAR2,
Retcode OUT NUMBER,
p_validate_flag IN VARCHAR2,
p_insert_flag IN VARCHAR2
);
PROCEDURE xxcsc_error_insert ( p_line_number NUMBER ,
p_request_id NUMBER ,
p_validation VARCHAR2 ,
p_table_name VARCHAR2 ,
p_column_name VARCHAR2 ,
p_error_code VARCHAR2 ,
p_error_desc VARCHAR2
);
END xxcsc_order_import_pkg;
/
/*******************************************************************************
* $Header$
*
*
* Program Name : XXCSC_ORDER_IMPORT_PKG
* Program Type : Package body
* Purpose : To validate the staging table data and insert the validated data
* into interface tables
*
*
* History :
*
* Date Owner Activity
* --------------------------------------------------------------------------
*
*******************************************************************************/
CREATE OR REPLACE PACKAGE BODY xxcsc_order_import_pkg
AS
-- Global variables for errorcode
g_message VARCHAR2 (1000); -- To store user defined message
g_error_code VARCHAR2 (1000); -- To store standard error code
g_error_message VARCHAR2 (2000); -- To store standard error message
--g_user_id apps.fnd_user.user_id%TYPE := apps.fnd_global.user_id;
g_request_id NUMBER := apps.fnd_global.conc_request_id;
/*******************************************************************************
-- Procedure to capture user defined errors into LOG file
*********************************************************************************/
PROCEDURE write_to_log_file (p_message VARCHAR2)
IS
BEGIN
apps.fnd_file.put_line (apps.fnd_file.log, p_message);
END write_to_log_file;
/*******************************************************************************
-- To insert error validations with details into custom error table
*********************************************************************************/
PROCEDURE xxcsc_error_insert ( p_line_number NUMBER ,
p_request_id NUMBER ,
p_validation VARCHAR2,
p_table_name VARCHAR2,
p_column_name VARCHAR2,
p_error_code VARCHAR2,
p_error_desc VARCHAR2 )
IS
BEGIN
INSERT INTO XXCSC_ORDER_IMPORT_ERRORS ( line_number,
request_id,
validation,
table_name,
column_name,
error_code,
error_desc)
VALUES ( p_line_number,
p_request_id ,
p_validation,
p_table_name,
p_column_name,
p_error_code,
p_error_desc);
EXCEPTION WHEN OTHERS THEN
write_to_log_file ('Error occured while inserting error records into XXCSC_ORDER_IMPORT_ERRORS');
END xxcsc_error_insert;
/*******************************************************************************
--VALIDATION PROCEDURES STARTS HERE
-- Validation procedure for operating unit
*********************************************************************************/
PROCEDURE validate_operating_unit
IS
v_org_id NUMBER;
v_line_number NUMBER :=0;
CURSOR cur_operating_unit
IS
SELECT DISTINCT (operating_unit),orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
BEGIN
FOR rec_operating_unit IN cur_operating_unit
LOOP
v_org_id :=NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(organization_id)
INTO v_org_id
FROM apps.hr_all_organization_units
WHERE UPPER(name) = UPPER(rec_operating_unit.operating_unit);
UPDATE xxcsc_om_headers_import_stg
SET org_id = v_org_id
WHERE UPPER(operating_unit) = UPPER(rec_operating_unit.operating_unit);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(operating_unit) = UPPER(rec_operating_unit.operating_unit);
g_error_code := SQLCODE;
g_message := 'Operating Unit : " '||rec_operating_unit.operating_unit||' " does not exists';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the operating unit',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'operating_unit',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(operating_unit) = UPPER(rec_operating_unit.operating_unit);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
g_message := 'Operating Unit : " '||rec_operating_unit.operating_unit||' " failed in validation';
write_to_log_file (g_message);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the operating unit',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'operating_unit',
p_error_code => g_error_code,
p_error_desc => g_error_message||'- Operating Unit : '||rec_operating_unit.operating_unit
);
END;
END LOOP;
COMMIT;
END validate_operating_unit;
/*******************************************************************************
-- Validation procedure for Order Source
*********************************************************************************/
PROCEDURE validate_source
AS
CURSOR cur_source
IS
SELECT DISTINCT (order_source),orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_order_source_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_source IN cur_source
LOOP
v_order_source_id :=NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(order_source_id)
INTO v_order_source_id
FROM apps.oe_order_sources
WHERE UPPER (NAME) = UPPER (rec_source.order_source);
UPDATE xxcsc_om_headers_import_stg
SET order_source_id = v_order_source_id
WHERE UPPER(order_source) = UPPER(rec_source.order_source);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(order_source) = UPPER(rec_source.order_source);
g_error_code := SQLCODE;
g_message := 'Order Source : " '||rec_source.order_source||' " does not exists';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Order Source',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Order Source',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(order_source) = UPPER(rec_source.order_source);
g_message := 'Order Source : " '||rec_source.order_source||' " failed in validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Order Source',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Order Source',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Order Source : '||rec_source.order_source
);
END;
END LOOP;
COMMIT;
END validate_source;
/*******************************************************************************
-- Validation procedure for Transaction type
*********************************************************************************/
PROCEDURE validate_transaction_type
AS
CURSOR cur_transaction_type
IS
SELECT DISTINCT (order_type),orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_order_type_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_transaction_type IN cur_transaction_type
LOOP
v_order_type_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(t.transaction_type_id)
INTO v_order_type_id
FROM apps.oe_transaction_types_tl t
, apps.oe_transaction_types_all b
WHERE UPPER (t.name) = UPPER(rec_transaction_type.order_type)
AND b.transaction_type_id = t.transaction_type_id
AND NVL (b.end_date_active, SYSDATE + 1) > SYSDATE;
UPDATE xxcsc_om_headers_import_stg
SET order_type_id = v_order_type_id
WHERE UPPER(order_type) = UPPER(rec_transaction_type.order_type);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(order_type) = UPPER(rec_transaction_type.order_type);
g_error_code := SQLCODE;
g_message := 'Order Type : " '||rec_transaction_type.order_type||' " does not exists';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Order Type',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Order Type',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(order_type) = UPPER(rec_transaction_type.order_type);
g_message := 'Order Type : " '||rec_transaction_type.order_type||' " failed in validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Order Type',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Order Type',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Order Type : '||rec_transaction_type.order_type
);
END;
END LOOP;
COMMIT;
END validate_transaction_type;
/*******************************************************************************
-- Validation procedure for Header Price List
*********************************************************************************/
PROCEDURE validate_header_price_list
AS
CURSOR cur_price_list
IS
SELECT DISTINCT (price_list),orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_price_list_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_price_list IN cur_price_list
LOOP
v_price_list_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(list_header_id)
INTO v_price_list_id
FROM apps.qp_list_headers_all
WHERE UPPER (NAME) = UPPER (rec_price_list.price_list);
UPDATE xxcsc_om_headers_import_stg
SET price_list_id = v_price_list_id
WHERE UPPER(price_list) = UPPER(rec_price_list.price_list);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(price_list) = UPPER(rec_price_list.price_list);
g_error_code := SQLCODE;
g_message := 'Price List : " '||rec_price_list.price_list||' " does not exists';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Price List',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Price List',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(price_list) = UPPER(rec_price_list.price_list);
g_message := 'Price List : " '||rec_price_list.price_list||' " failed in validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Price List',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Price List',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Order Type : '||rec_price_list.price_list
);
END;
END LOOP;
COMMIT;
END validate_header_price_list;
/*******************************************************************************
-- Validation procedure for Line Price List
*********************************************************************************/
PROCEDURE validate_line_price_list
AS
CURSOR cur_price_list_line
IS
SELECT DISTINCT (price_list),orig_sys_document_ref
FROM xxcsc_om_lines_import_stg
ORDER BY orig_sys_document_ref;
v_price_list_line_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_price_list_line IN cur_price_list_line
LOOP
v_price_list_line_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(list_header_id)
INTO v_price_list_line_id
FROM apps.qp_list_headers_all
WHERE UPPER (NAME) = UPPER (rec_price_list_line.price_list);
UPDATE xxcsc_om_lines_import_stg
SET price_list_id = v_price_list_line_id
WHERE UPPER(price_list) = UPPER(rec_price_list_line.price_list);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(price_list) = UPPER(rec_price_list_line.price_list);
g_error_code := SQLCODE;
g_message := 'Price List : " '||rec_price_list_line.price_list||' " does not exists in Line level';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Price List',
p_table_name => 'XXCSC_OM_LINES_IMPORT_STG',
p_column_name => 'Price List',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(price_list) = UPPER(rec_price_list_line.price_list);
g_message := 'Price List : " '||rec_price_list_line.price_list||' " failed in Line validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Price List',
p_table_name => 'XXCSC_OM_LINES_IMPORT_STG',
p_column_name => 'Price List',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Price List : '||rec_price_list_line.price_list
);
END;
END LOOP;
COMMIT;
END validate_line_price_list;
/*******************************************************************************
-- Validation procedure for Inventory Item
*********************************************************************************/
PROCEDURE validate_item
AS
CURSOR cur_inventory_item
IS
SELECT DISTINCT (inventory_item),ship_from_org,orig_sys_document_ref
FROM xxcsc_om_lines_import_stg
ORDER BY orig_sys_document_ref;
v_inventory_item_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_inventory_item IN cur_inventory_item
LOOP
v_inventory_item_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(inventory_item_id)
INTO v_inventory_item_id
FROM apps.mtl_system_items_b msi
,apps.mtl_parameters mp
WHERE msi.organization_id = mp.organization_id
AND UPPER(msi.segment1) = UPPER(rec_inventory_item.inventory_item)
AND UPPER(mp.organization_code) = UPPER(rec_inventory_item.ship_from_org);
UPDATE xxcsc_om_lines_import_stg
SET inventory_item_id = v_inventory_item_id
WHERE UPPER(inventory_item) = UPPER(rec_inventory_item.inventory_item);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(inventory_item) = UPPER(rec_inventory_item.inventory_item);
g_error_code := SQLCODE;
g_message := 'Inventory Item : "'||rec_inventory_item.inventory_item||' " does not exists';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Inventory Item',
p_table_name => 'XXCSC_OM_LINES_IMPORT_STG',
p_column_name => 'Inventory item',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(inventory_item) = UPPER(rec_inventory_item.inventory_item);
g_message := 'Inventory Item : "'||rec_inventory_item.inventory_item||' " failed in validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Inventory Item',
p_table_name => 'XXCSC_OM_LINES_IMPORT_STG',
p_column_name => 'Inventory Item',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Inventory Item : '||rec_inventory_item.inventory_item
);
END;
END LOOP;
COMMIT;
END validate_item;
/*******************************************************************************
-- Validation procedure for Header Warehouse
*********************************************************************************/
PROCEDURE validate_header_warehouse
AS
CURSOR cur_warehouse
IS
SELECT DISTINCT (ship_from_org),orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_ship_from_org_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_warehouse IN cur_warehouse
LOOP
v_ship_from_org_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(organization_id)
INTO v_ship_from_org_id
FROM apps.mtl_parameters
WHERE UPPER (organization_code) = UPPER (rec_warehouse.ship_from_org);
UPDATE xxcsc_om_headers_import_stg
SET ship_from_org_id = v_ship_from_org_id
WHERE UPPER(ship_from_org) = UPPER(rec_warehouse.ship_from_org);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(ship_from_org) = UPPER(rec_warehouse.ship_from_org);
g_error_code := SQLCODE;
g_message := 'Warehouse : " '||rec_warehouse.ship_from_org||' " does not exists in header level';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Warehouse in Header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Warehouse',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(ship_from_org) = UPPER(rec_warehouse.ship_from_org);
g_message := 'Warehouse : " '||rec_warehouse.ship_from_org||' " failed in header validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Warehouse in Header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Warehouse',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Warehouse : '||rec_warehouse.ship_from_org
);
END;
END LOOP;
COMMIT;
END validate_header_warehouse;
/*****************************************************************************
-- Validation procedure for Line Warehouse
*******************************************************************************/
PROCEDURE validate_line_warehouse
AS
CURSOR cur_warehouse_line
IS
SELECT DISTINCT (ship_from_org),orig_sys_document_ref
FROM xxcsc_om_lines_import_stg
ORDER BY orig_sys_document_ref;
v_ship_from_org_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_warehouse_line IN cur_warehouse_line
LOOP
v_ship_from_org_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(organization_id)
INTO v_ship_from_org_id
FROM apps.mtl_parameters
WHERE UPPER (organization_code) = UPPER (rec_warehouse_line.ship_from_org);
UPDATE xxcsc_om_lines_import_stg
SET ship_from_org_id = v_ship_from_org_id
WHERE UPPER(ship_from_org) = UPPER(rec_warehouse_line.ship_from_org);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(ship_from_org) = UPPER(rec_warehouse_line.ship_from_org);
g_error_code := SQLCODE;
g_message := 'Warehouse : " '||rec_warehouse_line.ship_from_org||' " does not exists in line level';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Warehouse in Line level',
p_table_name => 'XXCSC_OM_LINES_IMPORT_STG',
p_column_name => 'Warehouse',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(ship_from_org) = UPPER(rec_warehouse_line.ship_from_org);
g_message := 'Warehouse : " '||rec_warehouse_line.ship_from_org||' " failed in line validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Warehouse in Line level',
p_table_name => 'XXCSC_OM_LINES_IMPORT_STG',
p_column_name => 'Warehouse',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Wareshouse :'||rec_warehouse_line.ship_from_org
);
END;
END LOOP;
COMMIT;
END validate_line_warehouse;
/*******************************************************************************
-- Validation procedure for Line Salesrep
*********************************************************************************/
PROCEDURE validate_line_salesrep
AS
CURSOR cur_salesrep
IS
SELECT DISTINCT (salesrep),orig_sys_document_ref
FROM xxcsc_om_lines_import_stg
ORDER BY orig_sys_document_ref;
v_salesrep_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_salesrep IN cur_salesrep
LOOP
v_salesrep_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(salesrep_id)
INTO v_salesrep_id
FROM apps.ra_salesreps_all
WHERE UPPER(name) = UPPER(LTRIM(RTRIM(rec_salesrep.salesrep)))
AND org_id=apps.fnd_profile.VALUE ('ORG_ID');
UPDATE xxcsc_om_lines_import_stg
SET salesrep_id = v_salesrep_id
WHERE UPPER(salesrep) = UPPER(rec_salesrep.salesrep);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(salesrep) = UPPER(rec_salesrep.salesrep);
g_error_code := SQLCODE;
g_message := 'Salesrep : " '||rec_salesrep.salesrep||' " does not exists in line level';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the salesrep in Line level',
p_table_name => 'XXCSC_OM_LINES_IMPORT_STG',
p_column_name => 'Salesrep',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(salesrep) = UPPER(rec_salesrep.salesrep);
g_message := 'Salesrep : " '||rec_salesrep.salesrep||' " failed in line validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the salesrep in Line level',
p_table_name => 'XXCSC_OM_LINES_IMPORT_STG',
p_column_name => 'Salesrep',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Salesrep : '||rec_salesrep.salesrep
);
END;
END LOOP;
COMMIT;
END validate_line_salesrep;
/*******************************************************************************
-- Validation procedure for Header Salesrep
*********************************************************************************/
PROCEDURE validate_header_salesrep
AS
CURSOR cur_salesrep
IS
SELECT DISTINCT (salesrep),orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_salesrep_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_salesrep IN cur_salesrep
LOOP
v_salesrep_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(salesrep_id)
INTO v_salesrep_id
FROM apps.ra_salesreps_all
WHERE UPPER(name) = UPPER(LTRIM(RTRIM(rec_salesrep.salesrep)))
AND org_id=apps.fnd_profile.VALUE ('ORG_ID');
UPDATE xxcsc_om_headers_import_stg
SET salesrep_id = v_salesrep_id
WHERE UPPER(salesrep) = UPPER(rec_salesrep.salesrep);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(salesrep) = UPPER(rec_salesrep.salesrep);
g_error_code := SQLCODE;
g_message := 'Salesrep : " '||rec_salesrep.salesrep||' " does not exists in header level';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the salesrep in header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Salesrep',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(salesrep) = UPPER(rec_salesrep.salesrep);
g_message := 'Salesrep : " '||rec_salesrep.salesrep||' " failed in header validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the salesrep in header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Salesrep',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Salesrep : '||rec_salesrep.salesrep
);
END;
END LOOP;
COMMIT;
END validate_header_salesrep;
/*******************************************************************************
-- Validation procedure for Header Payment Term
*********************************************************************************/
PROCEDURE validate_header_payment_term
AS
CURSOR cur_payment_term
IS
SELECT DISTINCT (payment_term),orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_term_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_payment_term IN cur_payment_term
LOOP
v_term_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(term_id)
INTO v_term_id
FROM apps.ra_terms
WHERE name = rec_payment_term.payment_term;
UPDATE xxcsc_om_headers_import_stg
SET payment_term_id = v_term_id
WHERE payment_term = rec_payment_term.payment_term;
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE payment_term = rec_payment_term.payment_term;
g_error_code := SQLCODE;
g_message := 'Payment term : " '||rec_payment_term.payment_term||' " does not exists in header level';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the payment Term in header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Payment Term',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE payment_term = rec_payment_term.payment_term;
g_message := 'Payment term : " '||rec_payment_term.payment_term||' " failed in header validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the payment term in header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Payment term',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Payment Term : '||rec_payment_term.payment_term
);
END;
END LOOP;
COMMIT;
END validate_header_payment_term;
/*******************************************************************************
-- Validation procedure for Line Payment Term
*********************************************************************************/
PROCEDURE validate_line_payment_term
AS
CURSOR cur_payment_term
IS
SELECT DISTINCT (payment_term),orig_sys_document_ref
FROM xxcsc_om_lines_import_stg
ORDER BY orig_sys_document_ref;
v_term_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_payment_term IN cur_payment_term
LOOP
v_term_id :=NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT term_id
INTO v_term_id
FROM apps.ra_terms
WHERE name = rec_payment_term.payment_term;
UPDATE xxcsc_om_lines_import_stg
SET payment_term_id = v_term_id
WHERE payment_term = rec_payment_term.payment_term;
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE payment_term = rec_payment_term.payment_term;
g_error_code := SQLCODE;
g_message := 'Payment term : " '||rec_payment_term.payment_term||' " does not exists in line level';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the payment in line level',
p_table_name => 'XXCSC_OM_LINES_IMPORT_STG',
p_column_name => 'Payment',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE payment_term = rec_payment_term.payment_term;
g_message := 'Payment term : " '||rec_payment_term.payment_term||' " failed in line validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the payment term in line level',
p_table_name => 'XXCSC_OM_LINES_IMPORT_STG',
p_column_name => 'Payment term',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Payment Term : '||rec_payment_term.payment_term
);
END;
END LOOP;
COMMIT;
END validate_line_payment_term;
/*******************************************************************************
-- Validation procedure for Line Ship Method
*********************************************************************************/
PROCEDURE validate_line_ship_method
IS
CURSOR cur_ship_method
IS
SELECT DISTINCT (shipping_method),orig_sys_document_ref
FROM xxcsc_om_lines_import_stg
ORDER BY orig_sys_document_ref;
v_ship_method VARCHAR2(50);
v_line_number NUMBER :=0;
BEGIN
FOR rec_ship_method IN cur_ship_method
LOOP
v_ship_method := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT lookup_code
INTO v_ship_method
FROM apps.fnd_lookup_values_vl
WHERE lookup_type = 'SHIP_METHOD'
AND UPPER(MEANING) = UPPER(rec_ship_method.shipping_method);
UPDATE xxcsc_om_lines_import_stg
SET shipping_method_code = v_ship_method
WHERE UPPER(shipping_method) = UPPER(rec_ship_method.shipping_method);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(shipping_method) = UPPER(rec_ship_method.shipping_method);
g_error_code := SQLCODE;
g_message := 'Shipping Method : " '||rec_ship_method.shipping_method||' " does not exists in line level';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Shipping Method in line level',
p_table_name => 'XXCSC_OM_LINES_IMPORT_STG',
p_column_name => 'Shipping Method',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(shipping_method) = UPPER(rec_ship_method.shipping_method);
g_message := 'Shipping Method : " '||rec_ship_method.shipping_method||' " failed in line validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Shipping Method in line level',
p_table_name => 'XXCSC_OM_LINES_IMPORT_STG',
p_column_name => 'Shipping Method',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Shipping Method : '||rec_ship_method.shipping_method
);
END;
END LOOP;
COMMIT;
END validate_line_ship_method;
/*******************************************************************************
-- Validation procedure for Header Ship Method
*********************************************************************************/
PROCEDURE validate_header_ship_method
IS
CURSOR cur_ship_method
IS
SELECT DISTINCT (shipping_method),orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_ship_method VARCHAR2(50);
v_line_number NUMBER :=0;
BEGIN
FOR rec_ship_method IN cur_ship_method
LOOP
v_ship_method := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT lookup_code
INTO v_ship_method
FROM apps.fnd_lookup_values_vl
WHERE lookup_type = 'SHIP_METHOD'
AND UPPER(MEANING) = UPPER(rec_ship_method.shipping_method);
UPDATE xxcsc_om_headers_import_stg
SET shipping_method_code = v_ship_method
WHERE UPPER(shipping_method) = UPPER(rec_ship_method.shipping_method);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(shipping_method) = UPPER(rec_ship_method.shipping_method);
g_error_code := SQLCODE;
g_message := 'Shipping Method : " '||rec_ship_method.shipping_method||' " does not exists in header level';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Shipping Method in line level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Shipping Method',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(shipping_method) = UPPER(rec_ship_method.shipping_method);
g_message := 'Shipping Method : " '||rec_ship_method.shipping_method||' " failed in header validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Shipping Method in header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Shipping Method',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Shipping Method : '||rec_ship_method.shipping_method
);
END;
END LOOP;
COMMIT;
END validate_header_ship_method;
/*******************************************************************************
-- Validation procedure for Header Freight Term
*********************************************************************************/
PROCEDURE validate_header_freight_term
IS
CURSOR cur_freight_term
IS
SELECT DISTINCT (freight_terms),orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_freight_terms VARCHAR2(20) := 'Paid';
BEGIN
FOR rec_freight_term IN cur_freight_term
LOOP
UPDATE xxcsc_om_headers_import_stg
SET freight_terms_code = v_freight_terms
WHERE UPPER(freight_terms) = UPPER(rec_freight_term.freight_terms);
END LOOP;
END validate_header_freight_term;
/*******************************************************************************
-- Validation procedure for Line Freight Term
*********************************************************************************/
PROCEDURE validate_line_freight_term
IS
CURSOR cur_freight_term
IS
SELECT DISTINCT (freight_terms),orig_sys_document_ref
FROM xxcsc_om_lines_import_stg
ORDER BY orig_sys_document_ref;
v_freight_terms VARCHAR2(20) := 'Paid';
BEGIN
FOR rec_freight_term IN cur_freight_term
LOOP
UPDATE xxcsc_om_lines_import_stg
SET freight_terms_code = v_freight_terms
WHERE UPPER(freight_terms) = UPPER(rec_freight_term.freight_terms);
END LOOP;
END validate_line_freight_term;
/*********************************************************************************
-- Validation procedure for Header FOB
*********************************************************************************/
PROCEDURE validate_header_fob
IS
CURSOR cur_fob
IS
SELECT DISTINCT (fob_point),orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_fob VARCHAR2(20) := 'Destination';
BEGIN
FOR rec_fob IN cur_fob
LOOP
UPDATE xxcsc_om_headers_import_stg
SET fob_point_code = v_fob
WHERE UPPER(fob_point) = UPPER(rec_fob.fob_point);
END LOOP;
END validate_header_fob;
/*******************************************************************************
-- Validation procedure for Line FOB
*********************************************************************************/
PROCEDURE validate_line_fob
IS
CURSOR cur_fob
IS
SELECT DISTINCT (fob_point),orig_sys_document_ref
FROM xxcsc_om_lines_import_stg
ORDER BY orig_sys_document_ref;
v_fob VARCHAR2(20) := 'Destination';
BEGIN
FOR rec_fob IN cur_fob
LOOP
UPDATE xxcsc_om_lines_import_stg
SET fob_point_code = v_fob
WHERE UPPER(fob_point) = UPPER(rec_fob.fob_point);
END LOOP;
END validate_line_fob;
/*******************************************************************************
-- Validation procedure for UOM
*********************************************************************************/
PROCEDURE validate_uom
IS
CURSOR cur_uom
IS
SELECT DISTINCT (order_quantity_uom),orig_sys_document_ref
FROM xxcsc_om_lines_import_stg
ORDER BY orig_sys_document_ref;
v_uom VARCHAR2(2000);
BEGIN
FOR rec_uom IN cur_uom
LOOP
v_uom := NULL;
IF SUBSTR(rec_uom.order_quantity_uom,1,1)='C' THEN
v_uom := 'MTH';
ELSIF SUBSTR(rec_uom.order_quantity_uom,1,1)='D' THEN
v_uom := 'YR';
ELSE
v_uom := 'Ea';
END IF;
UPDATE xxcsc_om_lines_import_stg
SET order_quantity_uom = v_uom
WHERE order_quantity_uom = rec_uom.order_quantity_uom;
END LOOP;
END validate_uom;
/*******************************************************************************
-- Validation procedure for Customer
*********************************************************************************/
PROCEDURE validate_customer
IS
CURSOR cur_customer
IS
SELECT DISTINCT (customer_name),orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_customer_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_customer IN cur_customer
LOOP
v_customer_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(party_id)
INTO v_customer_id
FROM apps.hz_parties
WHERE UPPER (party_name) = UPPER (LTRIM(RTRIM(rec_customer.customer_name)))
AND status = 'A';
UPDATE xxcsc_om_headers_import_stg
SET customer_id = v_customer_id
WHERE UPPER(customer_name) = UPPER(rec_customer.customer_name);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(customer_name) = UPPER(rec_customer.customer_name);
g_error_code := SQLCODE;
g_message := 'Customer : " '||rec_customer.customer_name||' " does not exists';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Customer',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Customer',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(customer_name) = UPPER(rec_customer.customer_name);
g_message := 'Customer : " '||rec_customer.customer_name||' " failed in validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Customer',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Customer',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Customer Name : '||rec_customer.customer_name
);
END;
END LOOP;
COMMIT;
END validate_customer;
/*********** ********************************************************************
-- Validation procedure for Ship to Org
*********************************************************************************/
PROCEDURE validate_ship_to_org
IS
CURSOR cur_ship_to_org
IS
SELECT DISTINCT (ship_to_org), customer_name,orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_ship_to_org_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_ship_to_org IN cur_ship_to_org
LOOP
v_ship_to_org_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(hzs.site_use_id)
INTO v_ship_to_org_id
FROM apps.hz_cust_accounts hza,
apps.hz_cust_site_uses_all hzs,
apps.hz_cust_acct_sites_all hzas,
apps.hz_parties hp,
apps.hz_party_sites hps
WHERE UPPER(hzs.location) = UPPER(rec_ship_to_org.ship_to_org)
AND UPPER (hp.party_name) = UPPER (rec_ship_to_org.customer_name)
AND hzs.site_use_code = 'SHIP_TO'
AND hp.party_id = hps.party_id
AND hza.party_id = hp.party_id
AND hza.cust_account_id = hzas.cust_account_id
AND hzas.cust_acct_site_id = hzs.cust_acct_site_id
AND hzs.status = 'A'
AND hzs.primary_flag = 'Y'
AND hzas.org_id = apps.fnd_profile.VALUE ('ORG_ID')
AND hzs.org_id = apps.fnd_profile.VALUE ('ORG_ID');
UPDATE xxcsc_om_headers_import_stg
SET ship_to_org_id = v_ship_to_org_id
WHERE UPPER(ship_to_org) = UPPER(rec_ship_to_org.ship_to_org);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(ship_to_org) = UPPER(rec_ship_to_org.ship_to_org);
g_error_code := SQLCODE;
g_message := 'Ship to Org : " '||rec_ship_to_org.ship_to_org||' " does not exists in header level';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Ship to Org in header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Ship to Org',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(ship_to_org) = UPPER(rec_ship_to_org.ship_to_org);
g_message := 'Ship to Org : " '||rec_ship_to_org.ship_to_org||' " failed in header validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Ship to Org in header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Ship to Org',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Ship to Org : '|| rec_ship_to_org.ship_to_org
);
END;
END LOOP;
COMMIT;
END validate_ship_to_org;
/*******************************************************************************
-- Validation procedure for Bill to Org
*********************************************************************************/
PROCEDURE validate_bill_to_org
IS
CURSOR cur_bill_to_org
IS
SELECT DISTINCT (invoice_to_org), customer_name,orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_bill_to_org_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_bill_to_org IN cur_bill_to_org
LOOP
v_bill_to_org_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(hzs.site_use_id)
INTO v_bill_to_org_id
FROM apps.hz_cust_accounts hza,
apps.hz_cust_site_uses_all hzs,
apps.hz_cust_acct_sites_all hzas,
apps.hz_parties hp,
apps.hz_party_sites hps
WHERE UPPER(hzs.location) = UPPER(rec_bill_to_org.invoice_to_org)
AND UPPER (hp.party_name) = UPPER (rec_bill_to_org.customer_name)
AND hzs.site_use_code = 'BILL_TO'
AND hp.party_id = hps.party_id
AND hza.party_id = hp.party_id
AND hza.cust_account_id = hzas.cust_account_id
AND hzas.cust_acct_site_id = hzs.cust_acct_site_id
AND hzs.status = 'A'
AND hzs.primary_flag = 'Y'
AND hzas.org_id = apps.fnd_profile.VALUE ('ORG_ID')
AND hzs.org_id = apps.fnd_profile.VALUE ('ORG_ID');
UPDATE xxcsc_om_headers_import_stg
SET invoice_to_org_id = v_bill_to_org_id
WHERE UPPER(invoice_to_org) = UPPER(rec_bill_to_org.invoice_to_org);
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(invoice_to_org) = UPPER(rec_bill_to_org.invoice_to_org);
g_error_code := SQLCODE;
g_message := 'Bill/Invoice to Org : " '||rec_bill_to_org.invoice_to_org||' " does not exists in header level';
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Bill/Invoice to Org in header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Bill/Invoice to Org',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE UPPER(invoice_to_org) = UPPER(rec_bill_to_org.invoice_to_org);
g_message := 'Bill/Invoice to Org : " '||rec_bill_to_org.invoice_to_org||' " failed in header validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Bill/Invoice to Org in header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Bill/Invoice to Org',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Bill/Invoice to Org : '||rec_bill_to_org.invoice_to_org
);
END;
END LOOP;
COMMIT;
END validate_bill_to_org;
/*******************************************************************************
-- Validation procedure for Sold to Org ID
*********************************************************************************/
PROCEDURE validate_sold_to_org
IS
CURSOR cur_sold_to_org
IS
SELECT DISTINCT (customer_name),orig_sys_document_ref
FROM xxcsc_om_headers_import_stg
ORDER BY orig_sys_document_ref;
v_sold_to_org_id NUMBER;
v_line_number NUMBER :=0;
BEGIN
FOR rec_sold_to_org IN cur_sold_to_org
LOOP
v_sold_to_org_id := NULL;
v_line_number := v_line_number + 1;
BEGIN
SELECT DISTINCT(hza.cust_account_id)
INTO v_sold_to_org_id
FROM apps.hz_cust_accounts hza,
apps.hz_cust_site_uses_all hzs,
apps.hz_cust_acct_sites_all hzas,
apps.hz_parties hp,
apps.hz_party_sites hps
WHERE hp.party_name = rec_sold_to_org.customer_name
AND hp.party_id = hps.party_id
AND hza.party_id = hp.party_id
AND hza.cust_account_id = hzas.cust_account_id
AND hzas.cust_acct_site_id = hzs.cust_acct_site_id
AND hzs.status = 'A'
AND hzs.primary_flag = 'Y'
AND hzas.org_id = apps.fnd_profile.VALUE ('ORG_ID')
AND hzs.org_id = apps.fnd_profile.VALUE ('ORG_ID');
UPDATE xxcsc_om_headers_import_stg
SET sold_to_org_id = v_sold_to_org_id
WHERE customer_name = rec_sold_to_org.customer_name;
EXCEPTION WHEN NO_DATA_FOUND THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE customer_name = rec_sold_to_org.customer_name;
g_error_code := SQLCODE;
g_message := 'Sold to Org does not exists for the customer '||rec_sold_to_org.customer_name;
write_to_log_file (g_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the sold to Org in header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Sold to Org',
p_error_code => g_error_code,
p_error_desc => g_message
);
WHEN OTHERS THEN
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'N',
cross_ref_flag = 'N'
WHERE customer_name = rec_sold_to_org.customer_name;
g_message := 'Sold to Org failed in header validation';
write_to_log_file (g_message);
g_error_code := SQLCODE;
g_error_message := SUBSTR (SQLERRM, 1, 2000);
write_to_log_file (g_error_code||' -- '||g_error_message);
-- Inserting errors into custom error table
XXCSC_ERROR_INSERT ( p_line_number => v_line_number,
p_request_id => g_request_id,
p_validation => 'Checking the Sold to Org in header level',
p_table_name => 'XXCSC_OM_HEADERS_IMPORT_STG',
p_column_name => 'Sold to Org',
p_error_code => g_error_code,
p_error_desc => g_error_message||' - Sold to Org '
);
END;
END LOOP;
COMMIT;
END validate_sold_to_org;
/*********************************************************************************************************
-- PROCEDURE TO CALL ALL VALIDATION PROCEDURES,TO UPDATE THE STG TABLE ID VALUES AND TO COUNT THE RECORDS
**********************************************************************************************************/
PROCEDURE xxcsc_validate_legacy_data
IS
v_header_count NUMBER := 0; -- To capture the header record count in staging table
v_line_count NUMBER := 0; -- To capture the line record count in staging table
v_validated_header NUMBER := 0; -- To capture the processed records in header staging table
v_validated_lines NUMBER := 0; -- To capture the processed records in line staging table
v_err_count_header NUMBER := 0; -- To capture the error records in header staging table
v_err_count_lines NUMBER := 0; -- To capture the error records in line staging table
v_valid_operating_unit NUMBER;
v_program_name VARCHAR2(1000); -- To capture the submitted concurrent program name
v_responsibility_name VARCHAR2(1000); -- To capture the responsibility from where the program is submitted
v_actual_start_Date DATE;
v_actual_completion_Date DATE;
v_user_name VARCHAR2(1000);
v_operating_unit VARCHAR2(1000);
BEGIN
BEGIN
---by using this statement we will get number of records in header table
SELECT COUNT (*)
INTO v_header_count
FROM xxcsc_om_headers_import_stg
WHERE validated_flag IS NULL
AND cross_ref_flag IS NULL
AND processed_flag IS NULL;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write_to_log_file ('No Records exists in headers table');
WHEN OTHERS
THEN
NULL;
END;
BEGIN
---by using this statement we will get number of records in lines table
SELECT COUNT (*)
INTO v_line_count
FROM xxcsc_om_lines_import_stg
WHERE validated_flag IS NULL
AND cross_ref_flag IS NULL
AND processed_flag IS NULL;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write_to_log_file ('No Records exists in lines table');
WHEN OTHERS
THEN
NULL;
END;
-- Update the staging tables with request ID
UPDATE xxcsc_om_headers_import_stg
SET request_id=g_request_id
WHERE validated_flag IS NULL
AND cross_ref_flag IS NULL
AND processed_flag IS NULL;
UPDATE xxcsc_om_lines_import_stg
SET request_id=g_request_id
WHERE validated_flag IS NULL
AND cross_ref_flag IS NULL
AND processed_flag IS NULL;
COMMIT;
-- Validating the data from legacy (i.e staging table's data) using following procedures
validate_operating_unit;
validate_source;
validate_transaction_type;
validate_header_price_list;
-- validate_line_price_list;
validate_uom;
validate_item;
validate_header_warehouse;
validate_line_warehouse;
validate_header_salesrep;
validate_line_salesrep;
validate_header_payment_term;
validate_line_payment_term;
--validate_header_ship_method;
--validate_line_ship_method;
validate_header_freight_term;
validate_line_freight_term;
validate_header_fob;
validate_line_fob;
validate_customer;
validate_ship_to_org;
validate_bill_to_org;
validate_sold_to_org;
--After completion of validations update the staging tables
UPDATE xxcsc_om_headers_import_stg
SET validated_flag = 'Y',
cross_ref_flag = 'Y',
request_id = g_request_id
WHERE validated_flag IS NULL
AND cross_ref_flag IS NULL;
UPDATE xxcsc_om_lines_import_stg
SET validated_flag = 'Y',
cross_ref_flag = 'Y',
request_id = g_request_id
WHERE validated_flag IS NULL
AND cross_ref_flag IS NULL;
COMMIT;
BEGIN
-- To get error records in header staging table
SELECT COUNT (*)
INTO v_err_count_header
FROM xxcsc_om_headers_import_stg
WHERE validated_flag = 'N'
AND request_id = g_request_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write_to_log_file ('No Error records found in header staging table');
WHEN OTHERS
THEN
NULL;
END;
BEGIN
-- To get error records in line staging table
SELECT COUNT (*)
INTO v_err_count_lines
FROM xxcsc_om_lines_import_stg
WHERE validated_flag = 'N'
AND request_id = g_request_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write_to_log_file ('No Error records found in line staging table');
WHEN OTHERS
THEN
NULL;
END;
v_validated_header := v_header_count - v_err_count_header;
v_validated_lines := v_line_count - v_err_count_lines;
BEGIN
SELECT a.user_concurrent_program_name
, c.responsibility_name
, b.actual_start_Date
, b.actual_completion_Date
, apps.fnd_global.user_name
, d.name
INTO v_program_name
, v_responsibility_name
, v_actual_start_Date
, v_actual_completion_Date
, v_user_name
, v_operating_unit
FROM apps.fnd_concurrent_programs_vl a
, apps.fnd_concurrent_requests b
, apps.fnd_responsibility_vl c
, apps.hr_operating_units d
WHERE a.concurrent_program_id = b.concurrent_program_id
AND b.responsibility_id = c.responsibility_id
AND b.request_id = g_request_id
AND d.organization_id=apps.fnd_profile.value('org_id');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
write_to_log_file ('There is no request submitted with the request ID :'||g_request_id);
WHEN OTHERS
THEN
write_to_log_file ('Others exception while trying to retreive concurrent program history');
END;
write_to_log_file ('Program Name : '||v_program_name);
write_to_log_file ('Request id : '||g_request_id ||' User Name : '||v_user_name||' Resposibility Name : '||v_responsibility_name);
write_to_log_file ('Request Date : '||to_char(v_actual_start_Date,'DD-MON-YYYY HH:MI:SS')||' Operating Unit : '||v_operating_unit||' Completion Date : '||to_char(v_actual_completion_Date,'DD-MON-YYYY HH:MI:SS'));
write_to_log_file ('******************************************************************************************************');
write_to_log_file ('Order Header Details Order line details ');
write_to_log_file ('******************************************************************************************************');
write_to_log_file ('Number of records Selected :'|| v_header_count|| ' Number of records Selected :'||v_line_count);
write_to_log_file ('Number of Validated Records :'|| v_validated_header||' Number of Validated Records :'||v_validated_lines);
write_to_log_file ('Number of error records :'|| v_err_count_header||' Number of error records :'||v_err_count_lines);
write_to_log_file ('------------------------------------------------------------------------------------------------------');
END xxcsc_validate_legacy_data;
/*******************************************************************************
-- PROCEDURE TO INSERT THE VALIDATED DATA FROM STAGING TABLE TO INTERFACE TABLE
*******************************************************************************/
PROCEDURE xxcsc_insert_data
AS
-- Cursor to get validated records from header staging table
CURSOR cur_header
IS
SELECT *
FROM xxcsc_om_headers_import_stg
WHERE validated_flag = 'Y'
AND cross_ref_flag = 'Y'
AND processed_flag IS NULL;
-- Cursor to get validated records from line staging table
CURSOR cur_line(p_orig_sys varchar2)
IS
SELECT *
FROM xxcsc_om_lines_import_stg
WHERE validated_flag = 'Y'
AND cross_ref_flag = 'Y'
AND processed_flag IS NULL
AND orig_sys_document_ref= p_orig_sys ;
v_processed_header NUMBER :=0;
v_processed_lines NUMBER :=0;
-- INSERT THE DATA INTO INTERFACE TABLE
BEGIN
FOR rec_header IN cur_header
LOOP
BEGIN
INSERT INTO apps.oe_headers_iface_all ( order_source_id,
orig_sys_document_ref,
org_id ,
header_id,
order_number,
ordered_date ,
order_type_id ,
price_list_id ,
transactional_curr_code ,
salesrep_id ,
payment_term_id ,
shipment_priority,
shipping_method_code,
freight_terms_code,
fob_point_code ,
customer_po_number ,
ship_from_org_id,
ship_to_org_id,
invoice_to_org_id ,
customer_id ,
customer_number,
sold_to_org_id ,
pricing_date ,
created_by ,
creation_date ,
last_updated_by ,
last_update_date
)
VALUES ( rec_header.order_source_id ,
rec_header.orig_sys_document_ref,
rec_header.org_id ,
rec_header.header_id,
rec_header.order_number,
rec_header.ordered_date ,
rec_header.order_type_id ,
rec_header.price_list_id ,
rec_header.transactional_curr_code ,
rec_header.salesrep_id ,
rec_header.payment_term_id ,
rec_header.shipment_priority,
rec_header.shipping_method_code,
rec_header.freight_terms_code,
rec_header.fob_point_code ,
rec_header.customer_po_number ,
rec_header.ship_from_org_id,
rec_header.ship_to_org_id,
rec_header.invoice_to_org_id ,
rec_header.customer_id ,
rec_header.customer_number,
rec_header.sold_to_org_id ,
rec_header.pricing_date ,
-1 ,
SYSDATE ,
-1 ,
SYSDATE
);
UPDATE xxcsc_om_headers_import_stg
SET processed_flag = 'Y'
WHERE orig_sys_document_ref = rec_header.orig_sys_document_ref;
v_processed_header := v_processed_header + 1;
EXCEPTION WHEN OTHERS THEN
write_to_log_file ('Error occured when inserting data into headers interface table');
END;
FOR rec_line IN cur_line(rec_header.orig_sys_document_ref)
LOOP
BEGIN
INSERT INTO apps.oe_lines_iface_all ( order_source_id,
orig_sys_document_ref,
orig_sys_line_ref ,
line_number ,
shipment_number,
line_id,
inventory_item_id,
ordered_quantity,
order_quantity_uom,
ship_from_org_id,
ship_to_org_id,
invoice_to_org_id,
price_list_id,
unit_list_price ,
unit_selling_price,
payment_term_id ,
shipping_method_code,
freight_terms_code ,
fob_point_code,
salesrep_id ,
customer_po_number,
sold_from_org_id ,
schedule_ship_date,
pricing_date,
promise_date,
calculate_price_flag,
created_by,
creation_date ,
last_updated_by,
last_update_date,
return_reason_code
)
VALUES ( rec_header.order_source_id,
rec_line.orig_sys_document_ref,
rec_line.orig_sys_line_ref ,
rec_line.line_number ,
rec_line.shipment_number,
rec_line.line_id,
rec_line.inventory_item_id,
rec_line.ordered_quantity,
rec_line.order_quantity_uom,
rec_line.ship_from_org_id,
rec_line.ship_to_org_id,
rec_line.invoice_to_org_id,
rec_line.price_list_id,
rec_line.unit_list_price ,
rec_line.unit_selling_price,
rec_line.payment_term_id ,
rec_line.shipping_method_code,
rec_line.freight_terms_code ,
rec_line.fob_point_code,
rec_line.salesrep_id ,
rec_line.customer_po_number,
rec_line.sold_from_org_id ,
rec_line.schedule_ship_date,
rec_line.pricing_date,
rec_line.promise_date,
'N',
-1,
SYSDATE ,
-1,
SYSDATE,
rec_line.return_reason_code
);
UPDATE xxcsc_om_lines_import_stg
SET processed_flag = 'Y'
WHERE orig_sys_document_ref = rec_header.orig_sys_document_ref;
v_processed_lines := v_processed_lines + 1;
EXCEPTION WHEN OTHERS THEN
write_to_log_file ('Error occured when inserting data into lines interface table');
END;
END LOOP; -- Line cursor
END LOOP; -- Header cursor
write_to_log_file ('No.of Processed Header Records :'|| v_processed_header||' No.of processed Line Records :'||v_processed_lines);
write_to_log_file ('*********************************************************************************************************');
COMMIT;
END xxcsc_insert_data;
/********************************************************************************
*********************************************************************************/
/*********************************************************************************
-- THIS IS THE MAIN PROCEDURE WE ARE GOING TO REGISTER IN APPS (R12)
-- IT WILL CALL THE VALIDATE AND INSERT PROCEDURES
*********************************************************************************/
PROCEDURE xxcsc_order_import_main (
Errbuf OUT VARCHAR2,
Retcode OUT NUMBER,
p_validate_flag IN VARCHAR2,
p_insert_flag IN VARCHAR2
)
AS
BEGIN
IF p_validate_flag = 'YES' -- OR p_validate_flag ='YES' OR p_validate_flag ='Y'
THEN
--calling validation procedure
xxcsc_validate_legacy_data;
END IF;
IF p_insert_flag = 'YES' -- OR p_insert_flag ='YES' OR p_insert_flag ='Y'
THEN
--calling insert procedure
xxcsc_insert_data;
END IF;
END xxcsc_order_import_main;
END XXCSC_ORDER_IMPORT_PKG;
/
/***********************************************************************************/
-- After compiling package run the following script to grant the permissions to APPS
/***********************************************************************************/
GRANT ALL ON xxhr.xxcsc_order_import_pkg TO APPS;
/
