Srikanth's Oracle Apps : March 2014

Blanket Purchase Agreement Conversion

Table....

-------------------------------------------------------------------------------------------

-- Table Name : XXAK_BPA_CONV_HDR_STG

-- Author's name : Srikanth Yarram

-- Date written :

-- RICE Object id : C4

-- Description : Staging table creation Script for Blanket PO's Data

-- Program Style :

-- Maintenance History :

-- Date Name Remarks

-- ----------- ---------------------- ------------------------------------------

-- 06-08-2013 Srikanth Yarram Initial version

-------------------------------------------------------------------------------------------

CREATE TABLE XXAK_BPA_CONV_HDR_STG

( BATCH_NAME VARCHAR2(100),

DOCUMENT_NUM VARCHAR2(20),

REVISION_NUM NUMBER,

VENDOR_NAME VARCHAR2(240),

VENDOR_SITE VARCHAR2(60),

VENDOR_CONTACT VARCHAR2(240),

SHIP_TO_LOCATION VARCHAR2(60),

BILL_TO_LOCATION VARCHAR2(60),

AGENT_BUYER_NAME VARCHAR2(240),

COMMENTS VARCHAR2(240),

FREIGHT_TERMS VARCHAR2(25),

FREIGHT_CARRIER VARCHAR2(60),

FOB VARCHAR2(50),

PAY_ON_RECEIPT VARCHAR2(1),

NOTE_TO_VENDOR VARCHAR2(480),

NOTE_TO_RECEIVER VARCHAR2(480),

START_DATE DATE,

END_DATE DATE,

AMT_LIMIT NUMBER,

CURRENCY VARCHAR2(10),

DOCUMENT_TYPE_CODE VARCHAR2(50),

VENDOR_ID NUMBER,

VENDOR_SITE_ID NUMBER,

VENDOR_CONTACT_ID NUMBER,

SHIP_TO_LOCATION_ID NUMBER,

BILL_TO_LOCATION_ID NUMBER,

AGENT_ID NUMBER,

FREIGHT_CODE VARCHAR2(20),

PAYMENT_TERMS VARCHAR2(50),

ORG_NAME VARCHAR2(20),

ORG_ID NUMBER,

TERM_ID VARCHAR2(50),

ACTION VARCHAR2(50),

TERMS_ID VARCHAR2(50),

REQUEST_ID NUMBER,

ERROR_CODE NUMBER,

ERROR_DESC VARCHAR2(1000),

PROCESS_CODE VARCHAR2(30),

CREATION_DATE DATE,

CREATED_BY NUMBER,

LAST_UPDATED_DATE DATE,

LAST_UPDATED_BY NUMBER,

LAST_UPDATE_LOGIN NUMBER

)

GRANT ALL ON xxak.XXAK_BPA_CONV_HDR_STG TO APPS

-------------------------------------------------------------------------------------------

-- Table Name : XXAK_BPA_CONV_LINE_STG

-- Author's name : Srikanth Yarram

-- Date written :

-- RICE Object id : C4

-- Description : Staging table creation Script for Blanket PO's Data

-- Program Style :

-- Maintenance History :

-- Date Name Remarks

-- ----------- ---------------------- ------------------------------------------

-- 06-08-2013 Srikanth Yarram Initial version

-------------------------------------------------------------------------------------------

CREATE TABLE XXAK_BPA_CONV_LINE_STG

(

BATCH_NAME VARCHAR2(100),

DOCUMENT_NUM VARCHAR2(20),

LINE_NUM NUMBER,

LINE_TYPE VARCHAR2(25),

ITEM VARCHAR2(40),

ITEM_DESCRIPTION VARCHAR2(400),

CATEGORY_SEGMENT VARCHAR2(40),

UOM_CODE VARCHAR2(3),

UNIT_PRICE NUMBER,

SUPPLIER_ITEM VARCHAR(50),

NOTE_TO_VENDOR VARCHAR2(480),

MIN_RELEASE_AMOUNT NUMBER,

QUANTITY_COMMITTED NUMBER,

COMMITTED_AMOUNT NUMBER,

PRICE_BREAK_SHIP_TO_ORG VARCHAR2(60),

PRICE_BREAK_SHIP_TO_LOCATION VARCHAR2(60),

PRICE_BREAK_QUANTITY NUMBER,

PRICE_BREAK_START_DATE DATE,

PRICE_BREAK_END_DATE DATE,

OVERRIDE_PRICE NUMBER,

PRICE_DISCOUNT NUMBER,

SHIPMENT_NUM NUMBER,

LINE_TYPE_ID NUMBER,

INVENTORY_ITEM_ID NUMBER,

PRICE_BREAK_SHIP_TO_ORG_ID NUMBER,

PRICE_BREAK_SHIP_TO_LOC_ID NUMBER,

UNIT_OF_MEASURE VARCHAR2(20),

CATEGORY_ID VARCHAR2(50),

ITEM_ID NUMBER,

FREIGHT_CARRIER VARCHAR2(30),

TERMS_ID VARCHAR2(50),

REQUEST_ID NUMBER,

ERROR_CODE NUMBER,

ERROR_DESC VARCHAR2(400),

PROCESS_CODE VARCHAR2(30),

CREATION_DATE DATE,

CREATED_BY NUMBER,

LAST_UPDATE_DATE DATE,

LAST_UPDATED_BY NUMBER,

LAST_UPDATE_LOGIN NUMBER

)

GRANT ALL ON xxak.XXAK_BPA_CONV_LINE_STG TO APPS

-------------------------------------------------------------------------------------------

-- Table Name : XXAK_BPA_CONV_HDR_ARCH

-- Author's name : Srikanth Yarram

-- Date written :

-- RICE Object id : C4

-- Description : Archive table creation Script for Blanket PO's Header and Contract PO's Header Data

-- Program Style :

-- Maintenance History :

-- Date Name Remarks

-- ----------- ---------------------- ------------------------------------------

-- 06-08-2013 Srikanth Yarram Initial version

-------------------------------------------------------------------------------------------

CREATE TABLE XXAK_BPA_CONV_HDR_ARCH

( BATCH_NAME VARCHAR2(100),

DOCUMENT_NUM VARCHAR2(20),

REVISION_NUM NUMBER,

VENDOR_NAME VARCHAR2(240),

VENDOR_SITE VARCHAR2(60),

VENDOR_CONTACT VARCHAR2(240),

SHIP_TO_LOCATION VARCHAR2(60),

BILL_TO_LOCATION VARCHAR2(60),

AGENT_BUYER_NAME VARCHAR2(240),

COMMENTS VARCHAR2(240),

FREIGHT_TERMS VARCHAR2(25),

FREIGHT_CARRIER VARCHAR2(60),

FOB VARCHAR2(50),

PAY_ON_RECEIPT VARCHAR2(1),

NOTE_TO_VENDOR VARCHAR2(480),

NOTE_TO_RECEIVER VARCHAR2(480),

START_DATE DATE,

END_DATE DATE,

AMT_LIMIT NUMBER,

CURRENCY VARCHAR2(10),

DOCUMENT_TYPE_CODE VARCHAR2(50),

VENDOR_ID NUMBER,

VENDOR_SITE_ID NUMBER,

VENDOR_CONTACT_ID NUMBER,

SHIP_TO_LOCATION_ID NUMBER,

BILL_TO_LOCATION_ID NUMBER,

AGENT_ID NUMBER,

FREIGHT_CODE VARCHAR2(20),

PAYMENT_TERMS VARCHAR2(50),

ORG_NAME VARCHAR2(20),

ORG_ID NUMBER,

TERM_ID VARCHAR2(50),

ACTION VARCHAR2(50),

TERMS_ID VARCHAR2(50),

REQUEST_ID NUMBER,

ERROR_CODE NUMBER,

ERROR_DESC VARCHAR2(1000),

PROCESS_CODE VARCHAR2(30),

CREATION_DATE DATE,

CREATED_BY NUMBER,

LAST_UPDATED_DATE DATE,

LAST_UPDATED_BY NUMBER,

LAST_UPDATE_LOGIN NUMBER

)

GRANT ALL ON xxak.XXAK_BPA_CONV_HDR_ARCH TO APPS

-------------------------------------------------------------------------------------------

-- Table Name : XXAK_BPA_CONV_LINE_ARCH

-- Author's name : Srikanth Yarram

-- Date written :

-- RICE Object id : C4

-- Description : Archive table creation Script for Blanket PO's Line Data

-- Program Style :

-- Maintenance History :

-- Date Name Remarks

-- ----------- ---------------------- ------------------------------------------

-- 06-08-2013 Srikanth Yarram Initial version

-------------------------------------------------------------------------------------------

CREATE TABLE XXAK_BPA_CONV_LINE_ARCH

(

BATCH_NAME VARCHAR2(100),

DOCUMENT_NUM VARCHAR2(20),

LINE_NUM NUMBER,

LINE_TYPE VARCHAR2(25),

ITEM VARCHAR2(40),

ITEM_DESCRIPTION VARCHAR2(400),

CATEGORY_SEGMENT VARCHAR2(40),

UOM_CODE VARCHAR2(3),

UNIT_PRICE NUMBER,

SUPPLIER_ITEM VARCHAR(50),

NOTE_TO_VENDOR VARCHAR2(480),

MIN_RELEASE_AMOUNT NUMBER,

QUANTITY_COMMITTED NUMBER,

COMMITTED_AMOUNT NUMBER,

PRICE_BREAK_SHIP_TO_ORG VARCHAR2(60),

PRICE_BREAK_SHIP_TO_LOCATION VARCHAR2(60),

PRICE_BREAK_QUANTITY NUMBER,

PRICE_BREAK_START_DATE DATE,

PRICE_BREAK_END_DATE DATE,

OVERRIDE_PRICE NUMBER,

PRICE_DISCOUNT NUMBER,

SHIPMENT_NUM NUMBER,

LINE_TYPE_ID NUMBER,

INVENTORY_ITEM_ID NUMBER,

PRICE_BREAK_SHIP_TO_ORG_ID NUMBER,

PRICE_BREAK_SHIP_TO_LOC_ID NUMBER,

UNIT_OF_MEASURE VARCHAR2(20),

CATEGORY_ID VARCHAR2(50),

ITEM_ID NUMBER,

FREIGHT_CARRIER VARCHAR2(30),

TERMS_ID VARCHAR2(50),

REQUEST_ID NUMBER,

ERROR_CODE NUMBER,

ERROR_DESC VARCHAR2(400),

PROCESS_CODE VARCHAR2(30),

CREATION_DATE DATE,

CREATED_BY NUMBER,

LAST_UPDATE_DATE DATE,

LAST_UPDATED_BY NUMBER,

LAST_UPDATE_LOGIN NUMBER

)

GRANT ALL ON xxak.XXAK_BPA_CONV_LINE_ARCH TO APPS

======================================

Control File...

Headers:

OPTIONS (SKIP=1)

LOAD DATA

INFILE '&1'

---TRUNCATE

REPLACE

INTO TABLE XXAK_BPA_CONV_HDR_STG

FIELDS TERMINATED BY ","

OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

BATCH_NAME "TRIM(:BATCH_NAME)"

,DOCUMENT_NUM "TRIM(:DOCUMENT_NUM)"

,REVISION_NUM "TRIM(:REVISION_NUM)"

,VENDOR_NAME "TRIM(:VENDOR_NAME)"

,VENDOR_SITE "TRIM(:VENDOR_SITE)"

,VENDOR_CONTACT "TRIM(:VENDOR_CONTACT)"

,SHIP_TO_LOCATION "TRIM(:SHIP_TO_LOCATION)"

,BILL_TO_LOCATION "TRIM(:BILL_TO_LOCATION)"

,AGENT_BUYER_NAME "TRIM(:AGENT_BUYER_NAME)"

,COMMENTS "TRIM(:COMMENTS)"

,FREIGHT_TERMS "TRIM(:FREIGHT_TERMS)"

,FREIGHT_CARRIER "TRIM(:FREIGHT_CARRIER)"

,FOB "TRIM(:FOB)"

,PAY_ON_RECEIPT "TRIM(:PAY_ON_RECEIPT)"

,NOTE_TO_VENDOR "TRIM(:NOTE_TO_VENDOR)"

,NOTE_TO_RECEIVER "TRIM(:NOTE_TO_RECEIVER)"

,START_DATE "TO_DATE(TRIM(:START_DATE),'YYYY/MM/DD')"

,END_DATE "TO_DATE(TRIM(:END_DATE),'YYYY/MM/DD')"

,AMT_LIMIT "TRIM(:AMT_LIMIT)"

--,CURRENCY "TRIM(:CURRENCY)"

,DOCUMENT_TYPE_CODE CONSTANT 'BLANKET'

,CREATION_DATE SYSDATE

,CREATED_BY "FND_GLOBAL.USER_ID"

,LAST_UPDATED_DATE SYSDATE

,LAST_UPDATED_BY "FND_GLOBAL.USER_ID"

,LAST_UPDATE_LOGIN "FND_GLOBAL.USER_ID"

)

Lines:

OPTIONS (SKIP=1)

LOAD DATA

INFILE '&1'

---TRUNCATE

REPLACE

INTO TABLE XXAK_BPA_CONV_LINE_STG

FIELDS TERMINATED BY ","

OPTIONALLY ENCLOSED BY '"'

TRAILING NULLCOLS

(

BATCH_NAME "TRIM(:BATCH_NAME)"

,DOCUMENT_NUM "TRIM(:DOCUMENT_NUM)"

,LINE_NUM "TRIM(:LINE_NUM)"

,LINE_TYPE "TRIM(:LINE_TYPE)"

,ITEM "TRIM(:ITEM)"

,ITEM_DESCRIPTION "TRIM(:ITEM_DESCRIPTION)"

,CATEGORY_SEGMENT "TRIM(:CATEGORY_SEGMENT)"

,UOM_CODE "TRIM(:UOM_CODE)"

,UNIT_PRICE "TRIM(:UNIT_PRICE)"

,SUPPLIER_ITEM "TRIM(:SUPPLIER_ITEM)"

,NOTE_TO_VENDOR "TRIM(:NOTE_TO_VENDOR)"

,MIN_RELEASE_AMOUNT "TRIM(:MIN_RELEASE_AMOUNT)"

,QUANTITY_COMMITTED "TRIM(:QUANTITY_COMMITTED)"

,COMMITTED_AMOUNT "TRIM(:COMMITTED_AMOUNT)"

,PRICE_BREAK_SHIP_TO_ORG "TRIM(:PRICE_BREAK_SHIP_TO_ORG)"

,PRICE_BREAK_SHIP_TO_LOCATION "TRIM(:PRICE_BREAK_SHIP_TO_LOCATION)"

,PRICE_BREAK_QUANTITY "TRIM(:PRICE_BREAK_QUANTITY)"

,PRICE_BREAK_START_DATE "TO_DATE(TRIM(:PRICE_BREAK_START_DATE),'RRRR/MM/DD')"

,PRICE_BREAK_END_DATE "TO_DATE(TRIM(:PRICE_BREAK_END_DATE),'RRRR/MM/DD')"

,OVERRIDE_PRICE "TRIM(:OVERRIDE_PRICE)"

,PRICE_DISCOUNT "TRIM(:PRICE_DISCOUNT)"

,CREATION_DATE SYSDATE

,CREATED_BY "FND_GLOBAL.USER_ID"

,LAST_UPDATE_DATE SYSDATE

,LAST_UPDATED_BY "FND_GLOBAL.USER_ID"

,LAST_UPDATE_LOGIN "FND_GLOBAL.USER_ID"

)

Package:

create or replace

PACKAGE XXAK_PO_CNV_04_IMPORT_BPA_PKG

g_stage VARCHAR2 (2000);

g_batch_name VARCHAR2 (20);

g_request_id NUMBER;

g_vendor_id NUMBER;

g_operating_unit NUMBER := 126;

c_freight_lookup_type CONSTANT VARCHAR2 (200) := 'FREIGHT TERMS';

c_std_doc_type_code CONSTANT VARCHAR2 (200) := 'STANDARD';

c_shipment_type CONSTANT VARCHAR2 (200) := 'PRICE BREAK';

c_enabled_flag CONSTANT VARCHAR2 (200) := 'Y';

c_fob CONSTANT VARCHAR2 (200) := 'FOB';

c_appl_short_name CONSTANT VARCHAR2 (200) := 'PO';

c_po_num_exist CONSTANT VARCHAR2 (200) := '~ PO Number Already Exists';

c_header_int_name CONSTANT VARCHAR2 (200) := 'PO_HEADERS_INTERFACE';

c_line_int_name CONSTANT VARCHAR2 (200) := 'PO_LINES_INTERFACE';

c_dist_int_name CONSTANT VARCHAR2 (200) := 'PO_DISTRIBUTIONS_INTERFACE';

c_reject_process_code CONSTANT VARCHAR2 (200) := 'REJECTED';

c_val_upl_flag CONSTANT VARCHAR2 (200) := 'U';

c_revision_number NUMBER := 0;

PROCEDURE mark_records_for_processing (

p_batch_name IN VARCHAR2);

PROCEDURE set_stage (

p_stage IN VARCHAR2);

FUNCTION pre_validations (

p_batch_name IN VARCHAR2)

RETURN NUMBER;

FUNCTION data_val_headers (

px_po_header_rec IN OUT xxak_bpa_conv_hdr_stg%ROWTYPE)

RETURN NUMBER;

PROCEDURE update_header_records (

p_po_header_tab IN xxak_bpa_conv_hdr_stg%ROWTYPE);

FUNCTION data_val_lines (

px_po_lines_rec IN OUT xxak_bpa_conv_line_stg%ROWTYPE)

RETURN NUMBER;

PROCEDURE update_lines_records (

p_po_lines_rec IN xxak_bpa_conv_line_stg%ROWTYPE

, p_line_no IN NUMBER);

/*FUNCTION data_val_distributions (

px_distributions_rec IN OUT xxpo_po_distributions_stg%ROWTYPE)

RETURN NUMBER;

PROCEDURE update_distribution_records (

p_distributions_rec IN xxpo_po_distributions_stg%ROWTYPE);*/

FUNCTION update_preval_stg_rec (

p_batch_name IN VARCHAR2

, p_status IN VARCHAR2)

RETURN NUMBER;

PROCEDURE update_header_status (

p_batch_name IN VARCHAR2);

FUNCTION process_data

RETURN VARCHAR2;

PROCEDURE mark_records_complete (

p_process_code IN VARCHAR2);

PROCEDURE data_stats_err_msg_report (

p_batch_name IN VARCHAR2

, p_val_upl_flag IN VARCHAR2);

PROCEDURE main (

p_errbuf OUT VARCHAR2

, p_retcode OUT VARCHAR2

, p_batch_name IN VARCHAR2

, p_val_upl_flag IN VARCHAR2);

/*FUNCTION delete_stg_tbl (

p_batch_name IN NUMBER)

RETURN VARCHAR2;

FUNCTION insert_arch_tbl (

p_batch_name IN NUMBER)

RETURN VARCHAR2;*/

TYPE po_headers_tbl IS TABLE OF xxak_bpa_conv_hdr_stg%ROWTYPE

INDEX BY BINARY_INTEGER;

TYPE po_lines_tbl IS TABLE OF xxak_bpa_conv_line_stg%ROWTYPE

INDEX BY BINARY_INTEGER;

END XXAK_PO_CNV_04_IMPORT_BPA_PKG;

Body:

create or replace

PACKAGE BODY XXAK_PO_CNV_04_IMPORT_BPA_PKG

PROCEDURE main(

p_errbuf OUT VARCHAR2 ,

p_retcode OUT VARCHAR2 ,

p_batch_name IN VARCHAR2 ,

p_val_upl_flag IN VARCHAR2)

-------------------------------------------------------------------------------------

-- Created By : Srikanth Yarram

-- Creation Date : 06-AUG-2013

-- RICE ID : C4

-- File Name : XXAK_PO_CNV_04_IMPORT_BPA_PKG.pks

-- Description : This is the Main Wrapper Program

-- Latest Version : 1.0

-- Input Parameters description :

-- Batch Name : Batch Name

-- Revision History:

--====================================================================================

--Date Version# Name Remarks

--====================================================================================

--06-AUG-2013 1.0 Srikanth Yarram Initial development.

--------------------------------------------------------------------------------------

x_error_code VARCHAR2 (1) := xxak_cnv_common_pkg.c_success;

--------------------------------------------------

-- Cursor for Staging Table of Formula Conversion

--------------------------------------------------

CURSOR cur_xxak_bpa_headers ( cp_process_status VARCHAR2)

SELECT *

FROM xxak_bpa_conv_hdr_stg

WHERE batch_name = g_batch_name

AND request_id = g_request_id

AND process_code = cp_process_status

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn)

ORDER BY document_num;

CURSOR cur_xxak_bpa_lines_staging ( cp_process_status VARCHAR2

--, cp_document_num NUMBER)

, cp_document_num VARCHAR2)

SELECT *

FROM xxak_bpa_conv_line_stg

WHERE batch_name = g_batch_name

AND process_code = cp_process_status

AND request_id = g_request_id

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn)

AND document_num = cp_document_num -- need to add line num

ORDER BY document_num --header_record_number

, line_num;

--, shipment_num;

x_po_header_tab po_headers_tbl;

x_po_line_tab po_lines_tbl;

--x_po_distributions_tab po_distributions_tbl;

x_step NUMBER;

x_dist_qty NUMBER;

BEGIN

g_batch_name := p_batch_name;

g_request_id := fnd_global.conc_request_id;

---------------------------------------------------------------------------------------

-- Updates ERROR_CODE = null error_desc = null process_code = new in all staging tables

---------------------------------------------------------------------------------------

mark_records_for_processing (p_batch_name);

-------------------

-- Pre-Validations

-------------------

--------------------------

-- Set g_stage as Pre-Val

--------------------------

set_stage (xxak_cnv_common_pkg.c_preval);

x_error_code := pre_validations (p_batch_name);

x_error_code := update_preval_stg_rec (p_batch_name , g_stage);

----------------------------

-- Pre-Validations ends here

----------------------------

------------------------------------------

-- Data Validation/Derivations Starts here

------------------------------------------

set_stage (xxak_cnv_common_pkg.c_valid);

OPEN cur_xxak_bpa_headers (xxak_cnv_common_pkg.c_preval);

LOOP

FETCH cur_xxak_bpa_headers BULK COLLECT INTO x_po_header_tab LIMIT 500;

FOR i IN 1 .. x_po_header_tab.COUNT

LOOP

x_error_code := data_val_headers (x_po_header_tab (i) );

update_header_records (x_po_header_tab (i) );

OPEN cur_xxak_bpa_lines_staging (xxak_cnv_common_pkg.c_preval , x_po_header_tab (i).document_num);--record_number);

LOOP

FETCH cur_xxak_bpa_lines_staging BULK COLLECT INTO x_po_line_tab;

FOR i IN 1 .. x_po_line_tab.COUNT

LOOP

x_error_code := data_val_lines (x_po_line_tab (i) );

update_lines_records (x_po_line_tab (i) , i);

END LOOP; -- End loop for x_po_line_tab.COUNT

x_po_line_tab.DELETE;

EXIT

WHEN cur_xxak_bpa_lines_staging%NOTFOUND;

END LOOP; -- End loop for cur_xxpo_po_lines_staging

CLOSE cur_xxak_bpa_lines_staging;

END LOOP; -- End Loop for x_po_header_tab.COUNT

COMMIT;

x_po_header_tab.DELETE;

EXIT

WHEN cur_xxak_bpa_headers%NOTFOUND;

END LOOP; -- End loop for cur_xxpo_po_headers

CLOSE cur_xxak_bpa_headers;

x_error_code := update_preval_stg_rec (p_batch_name , g_stage);

update_header_status (g_batch_name);

fnd_file.put_line (fnd_file.LOG , 'Data Validation/Derivations ends here');

-------------------------------------------

-- Data Validation/Derivations Ends here...

-------------------------------------------

set_stage (xxak_cnv_common_pkg.c_process_data);

IF UPPER (p_val_upl_flag) = c_val_upl_flag THEN

x_error_code := process_data;

fnd_file.put_line (fnd_file.LOG , 'Process Data ends here');

x_error_code := update_preval_stg_rec (p_batch_name , g_stage);

fnd_file.put_line (fnd_file.LOG , 'Process Data Updated here');

mark_records_complete (xxak_cnv_common_pkg.c_process_data);

fnd_file.put_line (fnd_file.LOG , 'Mark Records ends here');

data_stats_err_msg_report (p_batch_name , p_val_upl_flag);

--x_error_code := insert_arch_tbl (p_batch_name);

--IF x_error_code = xxak_cnv_common_pkg.c_success THEN

--x_error_code := delete_stg_tbl (p_batch_name);

--END IF;

ELSE

data_stats_err_msg_report (p_batch_name , p_val_upl_flag);

END IF;

EXCEPTION

WHEN OTHERS THEN

p_errbuf := SQLERRM;

fnd_file.put_line (fnd_file.LOG , 'Error:' || SQLERRM);

END main;

PROCEDURE mark_records_for_processing(

p_batch_name VARCHAR2)

-----------------------------------------------------------------------------------

-- Created By :

-- Creation Date :

-- RICE ID :

-- Description :

-- Latest Version : 1.0

-- Input Parameters description :

-- Batch ID : Batch ID

-- Revision History:

--==================================================================================

--Date Version# Name Remarks

--==================================================================================

------------------------------------------------------------------------------------

BEGIN

--DELETE FROM po_headers_interface;

--DELETE FROM po_lines_interface;

--DELETE FROM po_distributions_interface;

--DELETE FROM po_line_locations_interface;

--DELETE FROM po_interface_errors;

BEGIN

g_batch_name := p_batch_name;

UPDATE xxak_bpa_conv_hdr_stg

SET ERROR_CODE = xxak_cnv_common_pkg.c_null ,

error_desc = xxak_cnv_common_pkg.c_null ,

request_id = g_request_id ,

process_code = xxak_cnv_common_pkg.c_new

WHERE batch_name = g_batch_name;

UPDATE xxak_bpa_conv_line_stg

SET ERROR_CODE = xxak_cnv_common_pkg.c_null ,

error_desc = xxak_cnv_common_pkg.c_null ,

request_id = g_request_id ,

process_code = xxak_cnv_common_pkg.c_new

WHERE batch_name = g_batch_name;

COMMIT;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG , 'Unhandled exception inside mark record for processing routine : ');

END;

END mark_records_for_processing;

PROCEDURE set_stage(

p_stage VARCHAR2)

-----------------------------------------------------------------------------------

-- Created By :

-- Creation Date :

-- RICE ID :

-- Description :

-- Latest Version :

-- Input Parameters description :

-- Batch ID : Batch ID

-- Revision History:

--==================================================================================

--Date Version# Name Remarks

--==================================================================================

BEGIN

g_stage := p_stage;

END set_stage;

FUNCTION pre_validations(

p_batch_name VARCHAR2)

RETURN NUMBER

x_error_code VARCHAR2 (1) := xxak_cnv_common_pkg.c_success;

x_dtl_cnt NUMBER := 0;

x_dtl_created_cnt NUMBER := 0;

x_error_code_temp NUMBER;

BEGIN

fnd_file.put_line (fnd_file.LOG , 'Inside pre_validations');

----------------------------

-- Header Level pre Validations

----------------------------

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.process_code = xxak_cnv_common_pkg.c_preval ,

xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc || '~PO Number Already Exists'

WHERE xphs.batch_name = p_batch_name

AND EXISTS

(SELECT 1

FROM po_headers_all pha ,

hr_operating_units hou

WHERE UPPER (hou.NAME) = UPPER (xphs.org_name)

AND TO_CHAR (xphs.document_num) = pha.segment1

AND pha.org_id = hou.organization_id

);

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.process_code = xxak_cnv_common_pkg.c_preval ,

xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc

|| '~Ship To Location is Null'

WHERE xphs.batch_name = p_batch_name

AND xphs.ship_to_location IS NULL;

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.error_desc = xphs.error_desc

||'~Duplicate Document Numbers in Header Staging Table' ,

ERROR_CODE = xxak_cnv_common_pkg.c_rec_err

WHERE batch_name=p_batch_name

AND EXISTS

(SELECT 1

FROM xxak_bpa_conv_hdr_stg

WHERE document_num = xphs.document_num

AND batch_name =p_batch_name

AND ROWID < xphs.ROWID

);

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.process_code = xxak_cnv_common_pkg.c_preval ,

xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc

|| '~Bill To Location is Null'

WHERE xphs.batch_name = p_batch_name

AND xphs.bill_to_location IS NULL;

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.process_code = xxak_cnv_common_pkg.c_preval ,

xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc

|| '~Buyer is Null'

WHERE xphs.batch_name = p_batch_name

AND (xphs.agent_buyer_name IS NULL);

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.process_code = xxak_cnv_common_pkg.c_preval ,

xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc

|| '~Vendor SIte Code is Null'

WHERE xphs.batch_name = p_batch_name

AND xphs.vendor_site IS NULL;

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.process_code = xxak_cnv_common_pkg.c_preval ,

xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc

|| '~Dcoument Num is Null'

WHERE xphs.batch_name = p_batch_name

AND xphs.document_num IS NULL;

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.process_code = xxak_cnv_common_pkg.c_preval ,

xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc

|| '~Start Date is Null'

WHERE xphs.batch_name = p_batch_name

AND xphs.start_date IS NULL;

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.process_code = xxak_cnv_common_pkg.c_preval ,

xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc

|| '~End Date is Null'

WHERE xphs.batch_name = p_batch_name

AND xphs.end_date IS NULL;

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.process_code = xxak_cnv_common_pkg.c_preval ,

xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc

|| '~Amt is Null'

WHERE xphs.batch_name = p_batch_name

AND xphs.amt_limit IS NULL;

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.process_code = xxak_cnv_common_pkg.c_preval ,

xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc

|| '~Revision Num is Null'

WHERE xphs.batch_name = p_batch_name

AND xphs.revision_num IS NULL;

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.process_code = xxak_cnv_common_pkg.c_preval ,

xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc

|| '~FOB is Null'

WHERE xphs.batch_name = p_batch_name

AND xphs.fob IS NULL;

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.process_code = xxak_cnv_common_pkg.c_preval ,

xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc

|| '~FREIGHT CARRIER is Null'

WHERE xphs.batch_name = p_batch_name

AND xphs.freight_carrier IS NULL;

-----------------------------

-- Line Level pre Validations

-----------------------------

--------------------------------------

-- Check for Duplicate Line Records

--------------------------------------

UPDATE xxak_bpa_conv_line_stg xpl

SET xpl.process_code = g_stage ,

xpl.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xpl.error_desc = '~Duplicate Line Records in Line staging table'

WHERE xpl.batch_name = p_batch_name

AND ROWID NOT IN

(SELECT MAX (ROWID)

FROM xxak_bpa_conv_line_stg xpls

WHERE xpls.batch_name = xpl.batch_name

--AND xpls.record_number = xpl.record_number

--AND xpls.header_record_number = xpl.header_record_number

AND xpls.document_num = xpl.document_num

AND xpls.line_num = xpl.line_num

);

UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval ,

xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xpls.error_desc = xpls.error_desc

|| '~No Header Records for the Line: '

||xpls.document_num

WHERE xpls.batch_name = p_batch_name

AND NOT EXISTS

(SELECT 1

FROM xxak_bpa_conv_hdr_stg xphs

WHERE xphs.batch_name = xpls.batch_name

AND xphs.document_num = xpls.document_num

);

UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval ,

xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xpls.error_desc = xpls.error_desc

|| '~Line Number is Null'

WHERE xpls.batch_name = p_batch_name

AND xpls.line_num IS NULL;

UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval ,

xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xpls.error_desc = xpls.error_desc

|| '~Line Type is Null'

WHERE xpls.batch_name = p_batch_name

AND xpls.line_type IS NULL;

UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval ,

xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xpls.error_desc = xpls.error_desc

|| '~Item Number and Item Category is Null but either of them should have a value '

WHERE xpls.batch_name = p_batch_name

AND xpls.item IS NULL

AND xpls.category_segment IS NULL;

UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval ,

xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xpls.error_desc = xpls.error_desc

|| '~Line Document Number is Null'

WHERE xpls.batch_name = p_batch_name

AND xpls.document_num IS NULL;

/*UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval

, xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err

, xpls.error_desc = xpls.error_desc || '~Brice Break Ship to Organization is Null'

WHERE xpls.batch_name = p_batch_name

AND xpls.price_break_ship_to_org IS NULL;

UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval

, xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err

, xpls.error_desc = xpls.error_desc || '~Price Break Ship to Location is Null'

WHERE xpls.batch_name = p_batch_name

AND xpls.price_break_ship_to_location IS NULL;

UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval

, xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err

, xpls.error_desc = xpls.error_desc || '~Price Break Start Date is Null'

WHERE xpls.batch_name = p_batch_name

AND xpls.price_break_start_date IS NULL;

UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval

, xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err

, xpls.error_desc = xpls.error_desc || '~Price Break End Date is Null'

WHERE xpls.batch_name = p_batch_name

AND xpls.price_break_end_date IS NULL;

UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval

, xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err

, xpls.error_desc = xpls.error_desc || '~Override Price is Null'

WHERE xpls.batch_name = p_batch_name

AND xpls.override_price IS NULL;

UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval

, xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err

, xpls.error_desc = xpls.error_desc || '~Price Discount is Null'

WHERE xpls.batch_name = p_batch_name

AND xpls.price_discount IS NULL;*/

UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval ,

xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xpls.error_desc = xpls.error_desc

|| '~UOM is Null'

WHERE xpls.batch_name = p_batch_name

AND xpls.uom_code IS NULL;

UPDATE xxak_bpa_conv_line_stg xpls

SET xpls.process_code = xxak_cnv_common_pkg.c_preval ,

xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xpls.error_desc = xpls.error_desc

|| '~Unit Price is Null'

WHERE xpls.batch_name = p_batch_name

AND xpls.unit_price IS NULL;

COMMIT;

UPDATE xxak_bpa_conv_hdr_stg sfg

SET sfg.process_code = xxak_cnv_common_pkg.c_preval ,

sfg.ERROR_CODE = NVL (sfg.ERROR_CODE , xxak_cnv_common_pkg.c_success)

WHERE sfg.batch_name = p_batch_name

AND sfg.process_code = xxak_cnv_common_pkg.c_new;

COMMIT;

UPDATE xxak_bpa_conv_line_stg sfg

SET sfg.process_code = xxak_cnv_common_pkg.c_preval ,

sfg.ERROR_CODE = NVL (sfg.ERROR_CODE , xxak_cnv_common_pkg.c_success)

WHERE sfg.batch_name = p_batch_name

AND sfg.process_code = xxak_cnv_common_pkg.c_new;

COMMIT;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG , 'Inside Unhandlede Exception Pre-Validations PO Conversion...');

RETURN xxak_cnv_common_pkg.c_prc_err;

END pre_validations;

FUNCTION data_val_headers(

px_po_header_rec IN OUT xxak_bpa_conv_hdr_stg%ROWTYPE)

RETURN NUMBER

-----------------------------------------------------------------------------------

-- Created By : Srikanth Yarram

-- Creation Date : 06-AUG-2013

-- RICE ID : 109

-- Description : Header Record validations

-- Latest Version : 1.0

-- Input Parameters description :

-- px_po_header_rec : Recode with the same structure of xxak_bpa_conv_hdr_stg

-- Output Parameters Description

-- Return : Returns the Error code

-- Revision History:

--==================================================================================

--Date Version# Name Remarks

--==================================================================================

--06-AUG-2013 1.0 Srikanth Yarram Initial development.

------------------------------------------------------------------------------------

x_error_code VARCHAR2 (1) := xxak_cnv_common_pkg.c_success;

x_error_code_temp VARCHAR2 (1) := xxak_cnv_common_pkg.c_success;

x_org_id NUMBER;

x_error_message VARCHAR2 (4000);

x_inventory_item_no VARCHAR2 (150);

x_inv_org_id NUMBER;

x_agent_id NUMBER;

x_vendor_id NUMBER;

X_Vname VARCHAR2(200);

x_vendor_site_id NUMBER;

x_ref_vendor_site VARCHAR2(200);

x_ship_location_id NUMBER;

x_bill_location_id NUMBER;

--x_term_id NUMBER;

x_freight_code VARCHAR2 (40);

x_fob_lookup_code VARCHAR2 (50);

x_ref_fob VARCHAR2 (50);

x_freight_term VARCHAR2 (20);

x_ref_freight_terms VARCHAR2 (50);

x_ref_freight_carrier VARCHAR2 (100);

x_person_id NUMBER;

x_ref_person VARCHAR2 (200);

x_vendor_contact_id NUMBER;

x_vendor_contact VARCHAR2 (240);

x_freight_lookup_code VARCHAR2 (240);

x_ou_code VARCHAR2 (240);

x_term_id VARCHAR2 (50);

x_currency_code VARCHAR2 (10);

BEGIN

x_term_id := NULL;

x_org_id := NULL;

x_ship_location_id := NULL;

x_bill_location_id := NULL;

x_agent_id := NULL;

x_vendor_id := NULL;

x_vendor_site_id := NULL;

x_freight_code := NULL;

x_person_id := NULL;

x_freight_lookup_code := NULL;

x_ou_code := NULL;

-- Derivation of Org ID

BEGIN

SELECT fnd_profile.value('org_id') INTO x_org_id FROM dual;

IF x_org_id IS NOT NULL THEN

px_po_header_rec.org_id := x_org_id;

g_operating_unit := x_org_id;

END IF;

END;

-- Currency

x_error_code_temp := xxak_cnv_common_pkg.get_currency_code(px_po_header_rec.currency);

-- Derivation of Agent Id

IF px_po_header_rec.agent_buyer_name IS NOT NULL THEN

BEGIN

SELECT first_name

||' '

||last_name

INTO x_ref_person

FROM xxak_xref_buyers

WHERE buyer_id = px_po_header_rec.agent_buyer_name;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Buyer number : ' || px_po_header_rec.agent_buyer_name || ' is Invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Buyer number : ' || px_po_header_rec.agent_buyer_name || ' Exception: ' || SQLERRM;

END;

END IF;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

IF x_ref_person IS NOT NULL THEN

BEGIN

SELECT ppf.person_id ,

pa.agent_id

INTO x_person_id ,

x_agent_id

FROM per_all_people_f ppf ,

po_agents pa

WHERE 1 =1

--AND ppf.full_name = px_po_header_rec.agent_buyer_name

AND ppf.first_name

||' '

||last_name = x_ref_person

AND ppf.business_group_id =

(SELECT business_group_id

FROM org_organization_definitions

WHERE operating_unit = g_operating_unit

AND ROWNUM < 2

)

AND ppf.person_id = pa.agent_id;

IF (x_person_id = x_agent_id) THEN

px_po_header_rec.agent_id := x_agent_id;

ELSE

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Buyer: ' || x_ref_person || ' is invalid';

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Buyer name: ' || x_ref_person || ' is Invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Buyer name: ' || x_ref_person || ' Exception: ' || SQLERRM;

END;

END IF;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

-- Derivation of Vendor ID

IF px_po_header_rec.vendor_name IS NOT NULL THEN

BEGIN

x_error_code_temp := xxak_cnv_common_pkg.get_vendor_id_frm_num (NULL , px_po_header_rec.vendor_name , x_vendor_id);

IF x_error_code_temp = xxak_cnv_common_pkg.c_success THEN

px_po_header_rec.vendor_id := x_vendor_id;

g_vendor_id := x_vendor_id;

ELSE

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Vendor Name: ' || px_po_header_rec.vendor_name || ' is invalid';

END IF;

EXCEPTION

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~vendor Name: ' || px_po_header_rec.vendor_name || ' Exception: ' || SQLERRM;

END;

ELSE ------ USING CROSS RFERENCE TABLES

BEGIN

SELECT av.vendor_id

INTO x_vendor_id

FROM xxak_xref_sup_sites xsusit,

ap_suppliers av

WHERE xsusit.LEGACY_SITE_CODE = px_po_header_rec.vendor_site

AND xsusit.vendor =av.vendor_name;

SELECT xsusit.vendor

INTO x_vname

FROM xxak_xref_sup_sites xsusit

WHERE xsusit.LEGACY_SITE_CODE = px_po_header_rec.vendor_site;

IF x_vendor_id IS NOT NULL THEN

x_error_code_temp := xxak_cnv_common_pkg.c_success;

px_po_header_rec.vendor_id := x_vendor_id;

g_vendor_id := x_vendor_id;

ELSE

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Vendor Name: ' || x_vname || ' is invalid';

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~vendor Name: ' || x_vname || ' is Invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~vendor Name: ' || x_vname || ' Exception: ' || SQLERRM;

END;

END IF;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

-- Derivation of vendor site id

IF px_po_header_rec.vendor_site IS NOT NULL THEN

BEGIN

SELECT vendor_site

INTO x_ref_vendor_site

FROM xxak_xref_sup_sites

WHERE legacy_site_code = px_po_header_rec.vendor_site;

IF x_ref_vendor_site IS NOT NULL THEN

x_error_code_temp := xxak_cnv_common_pkg.c_success;

--px_po_header_rec.vendor_site_id := x_vendor_site_id;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~vendor site code : ' || px_po_header_rec.vendor_site || ' is Invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~vendor site code : ' || px_po_header_rec.vendor_site || ' Exception: ' || SQLERRM;

END;

END IF;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

IF x_ref_vendor_site IS NOT NULL THEN

BEGIN

SELECT vendor_site_id

INTO x_vendor_site_id

FROM ap_suppliers pv ,

ap_supplier_sites_all pvs

WHERE pv.vendor_id = pvs.vendor_id

AND pvs.org_id = px_po_header_rec.org_id --126 x_org_id

AND pv.vendor_id = x_vendor_id

AND pvs.vendor_site_code = x_ref_vendor_site --px_po_header_rec.vendor_site

AND (pvs.inactive_date IS NULL

OR pvs.inactive_date > SYSDATE);

IF x_vendor_site_id IS NOT NULL THEN

x_error_code_temp := xxak_cnv_common_pkg.c_success;

px_po_header_rec.vendor_site_id := x_vendor_site_id;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~vendor site code: ' || px_po_header_rec.vendor_site || ' is Invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~vendor site code: ' || px_po_header_rec.vendor_site || ' Exception: ' || SQLERRM;

END;

END IF;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

-- -- Derivation of payment term id

IF (x_vendor_site_id) IS NOT NULL AND x_vendor_id IS NOT NULL THEN

BEGIN

SELECT DISTINCT term_id

INTO x_term_id

FROM ap_terms term,

ap_suppliers apsup,

ap_supplier_sites_all apsits

WHERE apsup.vendor_id = x_vendor_id

AND term.term_id = apsits.terms_id

AND apsits.vendor_site_id = x_vendor_site_id;

px_po_header_rec.terms_id := x_term_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~payment term: ' || px_po_header_rec.terms_id || ' is Invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~payment term: ' || px_po_header_rec.terms_id || ' Exception: ' || SQLERRM;

END;

END IF;

-- Derivation of vendor contact

IF (x_vendor_site_id IS NOT NULL AND x_vendor_id IS NOT NULL AND px_po_header_rec.vendor_contact IS NOT NULL) THEN

BEGIN

SELECT vendor_contact_id ,

first_name

|| ' '

|| last_name

INTO x_vendor_contact_id ,

x_vendor_contact

FROM ap_supplier_contacts

WHERE 1 = 1 --vendor_id = x_vendor_id

AND vendor_site_id = x_vendor_site_id

AND ROWNUM < 2;

px_po_header_rec.vendor_contact_id := x_vendor_contact_id;

px_po_header_rec.vendor_contact := x_vendor_contact;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~vendor Contact: ' || px_po_header_rec.vendor_contact || ' is Invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~vendor Contact: ' || px_po_header_rec.vendor_contact || ' Exception: ' || SQLERRM;

END;

END IF;

-- Derivation of ship to location ID

IF px_po_header_rec.ship_to_location IS NOT NULL THEN

BEGIN

SELECT location_id

INTO x_ship_location_id

FROM hr_locations

WHERE location_code = px_po_header_rec.ship_to_location

AND ship_to_site_flag = 'Y'

AND NVL (inactive_date , SYSDATE + 1) > SYSDATE;

px_po_header_rec.ship_to_location_id := x_ship_location_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Ship to Location: ' || px_po_header_rec.ship_to_location || ' is Invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Ship to Location: ' || px_po_header_rec.ship_to_location || ' Exception: ' || SQLERRM;

END;

END IF;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

-- Derivation of bill_to_location_id ID

IF px_po_header_rec.bill_to_location IS NOT NULL THEN

BEGIN

SELECT location_id

INTO x_bill_location_id

FROM hr_locations

WHERE location_code = px_po_header_rec.bill_to_location

AND bill_to_site_flag = 'Y'

AND NVL (inactive_date , SYSDATE + 1) > SYSDATE;

px_po_header_rec.bill_to_location_id := x_bill_location_id;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Bill to Location: ' || px_po_header_rec.bill_to_location || ' is Invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Bill to Location: ' || px_po_header_rec.bill_to_location || ' Exception: ' || SQLERRM;

END;

END IF;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

IF px_po_header_rec.freight_carrier IS NOT NULL THEN

BEGIN

SELECT oracle_code

INTO x_ref_freight_carrier

FROM xxak_xref_ft_carriers

WHERE orig_code = px_po_header_rec.freight_carrier;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Freight Carrier : ' || px_po_header_rec.freight_carrier || ' is invalid in Ref Table :xxak_xref_ft_carriers ';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~freight carrier : ' || px_po_header_rec.freight_carrier || ' Exception: ' || SQLERRM;

END;

END IF;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

IF x_ref_freight_carrier IS NOT NULL THEN

BEGIN

SELECT freight_code

INTO x_freight_code

FROM wsh_carriers

WHERE freight_code = x_ref_freight_carrier; --px_po_header_rec.freight_carrier;

px_po_header_rec.freight_carrier := x_freight_code;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Freight Carrier: ' || px_po_header_rec.freight_carrier || ' is invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~freight carrier: ' || px_po_header_rec.freight_carrier || ' Exception: ' || SQLERRM;

END;

END IF;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

-- Validation for Freight Terms

IF px_po_header_rec.freight_terms IS NOT NULL THEN

BEGIN

SELECT oracle_code

INTO x_ref_freight_terms

FROM xxak_xref_ft_terms

WHERE orig_code = px_po_header_rec.freight_terms;

--px_po_header_rec.freight_terms := x_ref_freight_terms;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Freight Terms: ' || px_po_header_rec.freight_terms || ' is invalid in Ref Table : xxak_xref_ft_terms ';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Freight Terms: ' || px_po_header_rec.freight_terms || ' Exception: ' || SQLERRM;

END;

END IF;

IF x_ref_freight_terms IS NOT NULL THEN

BEGIN

SELECT lookup_code

INTO x_freight_term

FROM po_lookup_codes

WHERE lookup_type = c_freight_lookup_type --'FREIGHT TERMS'

AND lookup_code = x_ref_freight_terms;

px_po_header_rec.freight_terms := x_freight_term;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Freight Terms: ' || px_po_header_rec.freight_terms || ' is invalid ';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Freight Terms: ' || px_po_header_rec.freight_terms || ' Exception: ' || SQLERRM;

END;

END IF;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

-- Validation for FOB

IF px_po_header_rec.fob IS NOT NULL THEN

BEGIN

SELECT oracle_code

INTO x_ref_fob

FROM xxak_xref_fobs

WHERE orig_code = px_po_header_rec.fob;

--px_po_header_rec.fob := x_ref_fob;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~FOB: ' || px_po_header_rec.fob || ' is invalid in Ref Table : xxak_xref_fobs ';

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG , 'Unhandled exception' || SQLERRM);

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~FOB: ' || px_po_header_rec.fob || ' Exception: ' || SQLERRM;

END;

END IF;

IF x_ref_fob IS NOT NULL THEN

BEGIN

SELECT lookup_code

INTO x_fob_lookup_code

FROM po_lookup_codes

WHERE lookup_type = c_fob

AND lookup_code = x_ref_fob;

px_po_header_rec.fob := x_fob_lookup_code;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~FOB: ' || px_po_header_rec.fob || ' is invalid';

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG , 'Unhandled exception' || SQLERRM);

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~FOB: ' || px_po_header_rec.fob || ' Exception: ' || SQLERRM;

END;

END IF;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

px_po_header_rec.ERROR_CODE := x_error_code;

px_po_header_rec.error_desc := x_error_message;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

x_error_message := ' Unhandled Exceiption in data_val_for_headers: ' || x_error_message || ' ' || SQLERRM;

RETURN x_error_code;

END data_val_headers;

PROCEDURE update_header_records(

p_po_header_tab IN xxak_bpa_conv_hdr_stg%ROWTYPE)

PRAGMA AUTONOMOUS_TRANSACTION;

-----------------------------------------------------------------------------------

-- Created By : Srikanth Yarram

-- Creation Date : 06-AUG-2013

-- RICE ID : C4

-- Description : Update the header staging fields with derived values

-- Latest Version : 1.0

-- Input Parameters description :

-- p_po_header_rec : Recode with the same structure of xxak_bpa_conv_hdr_stg

-- Revision History:

--==================================================================================

--Date Version# Name Remarks

--==================================================================================

--06-AUG-2013 1.0 Srikanth Yarram Initial development.

------------------------------------------------------------------------------------

BEGIN

UPDATE xxak_bpa_conv_hdr_stg

SET org_id = p_po_header_tab.org_id ,

agent_id = p_po_header_tab.agent_id ,

vendor_id = p_po_header_tab.vendor_id ,

vendor_site_id = p_po_header_tab.vendor_site_id ,

bill_to_location_id = p_po_header_tab.bill_to_location_id ,

vendor_contact = p_po_header_tab.vendor_contact ,

terms_id = p_po_header_tab.terms_id ,

currency = p_po_header_tab.currency ,

ship_to_location_id = p_po_header_tab.ship_to_location_id ,

ERROR_CODE = p_po_header_tab.ERROR_CODE ,

error_desc = p_po_header_tab.error_desc ,

process_code = g_stage

WHERE document_num = p_po_header_tab.document_num; --record_number = p_po_header_tab.record_number;

COMMIT;

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.DOCUMENT_TYPE_CODE = 'CONTRACT'

WHERE 1 =1

AND NOT EXISTS

(SELECT 1

FROM xxak_bpa_conv_line_stg xpls

WHERE xpls.batch_name = xphs.batch_name

AND xpls.document_num = xphs.document_num

);

COMMIT;

END update_header_records;

FUNCTION data_val_lines(

px_po_lines_rec IN OUT xxak_bpa_conv_line_stg%ROWTYPE)

RETURN NUMBER

-----------------------------------------------------------------------------------

-- Created By : Srikanth Yarram

-- Creation Date : 06-AUG-2013

-- RICE ID : c4

-- Description : Line Record validations

-- Latest Version : 1.0

-- Input Parameters description :

-- px_po_header_rec : Recode with the same structure of xxak_bpa_conv_line_stg

-- Output Parameters Description

-- Return : Returns the Error code

-- Revision History:

--==================================================================================

--Date Version# Name Remarks

--==================================================================================

--06-AUG-2013 1.0 Srikanth Yarram Initial development.

------------------------------------------------------------------------------------

x_error_code VARCHAR2 (1) := xxak_cnv_common_pkg.c_success;

x_error_code_temp VARCHAR2 (1) := xxak_cnv_common_pkg.c_success;

x_org_id NUMBER;

x_error_message VARCHAR2 (4000);

x_inventory_item_no VARCHAR2 (150);

x_inv_org_id NUMBER;

x_ship_to_loc_id NUMBER;

x_ship_location_id NUMBER;

x_term_id NUMBER;

x_item_id NUMBER;

x_category_id VARCHAR2 (50);

x_ref_category_segment VARCHAR2 (50);

x_item_desc VARCHAR2 (4000);

x_asl_count NUMBER;

x_ship_cnt NUMBER;

x_item_uom_class VARCHAR2 (50);

x_uom_class VARCHAR2 (50);

x_item_uom_code VARCHAR2 (50);

x_ship_to_org_id NUMBER;

x_receive_routing_id NUMBER;

x_line_type_id NUMBER;

x_uom_code VARCHAR2 (50);

x_outside_operation NUMBER;

x_purchase_enabled NUMBER;

x_units_of_measure VARCHAR2 (50);

x_xuom_code VARCHAR2 (50);

BEGIN

x_term_id := NULL;

x_org_id := NULL;

x_item_id := NULL;

x_ship_to_loc_id := NULL;

x_asl_count := NULL;

x_ship_cnt := NULL;

x_line_type_id := NULL;

x_uom_code := NULL;

x_outside_operation := NULL;

x_purchase_enabled := NULL;

x_units_of_measure := NULL;

x_xuom_code := NULL;

-- Derivation of Line Type Id

IF px_po_lines_rec.line_type IS NOT NULL THEN

BEGIN

SELECT line_type_id

INTO x_line_type_id

FROM po_line_types_tl

WHERE LANGUAGE = USERENV ('LANG')

AND line_type = px_po_lines_rec.line_type;

IF x_line_type_id IS NOT NULL THEN

x_error_code_temp := xxak_cnv_common_pkg.c_success;

px_po_lines_rec.line_type_id := x_line_type_id;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Line Type: ' || px_po_lines_rec.line_type || ' is invalid';

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG , ' Unhandled exception' || SQLERRM);

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Line Type: ' || px_po_lines_rec.line_type || ' Exception: ' || SQLERRM;

END;

END IF;

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

-- Derivation of Ship to location id

IF px_po_lines_rec.price_break_ship_to_location IS NOT NULL THEN

BEGIN

x_error_code_temp := xxak_cnv_common_pkg.get_location_id (px_po_lines_rec.price_break_ship_to_location , x_ship_to_loc_id);

IF x_error_code_temp = xxak_cnv_common_pkg.c_success THEN

px_po_lines_rec.price_break_ship_to_loc_id := x_ship_to_loc_id;

ELSE

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Price Break Ship To Location: ' || px_po_lines_rec.price_break_ship_to_location || ' is invalid';

END IF;

EXCEPTION

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Price Break Ship To Location: ' || px_po_lines_rec.price_break_ship_to_location || ' Exception: ' || SQLERRM;

END;

END IF;

-- Validation of Price Break Ship to organization

/*IF px_po_lines_rec.price_break_ship_to_org IS NOT NULL THEN

BEGIN

SELECT od.organization_id

INTO x_ship_to_org_id

FROM org_organization_definitions od

, hr_operating_units hou

WHERE hou.organization_id = g_operating_unit

AND od.organization_code = px_po_lines_rec.price_break_ship_to_org

AND od.operating_unit = hou.organization_id;

IF x_ship_to_org_id IS NULL THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message :=

x_error_message || '~Price Break Ship To Organization : ' || px_po_lines_rec.price_break_ship_to_org

|| ' is not defined for the operating unit ' || g_operating_unit;

ELSE

px_po_lines_rec.price_break_ship_to_org_id := x_ship_to_org_id;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message :=

x_error_message || '~Price Break Ship To Organization Code: ' || px_po_lines_rec.price_break_ship_to_org

|| ' is invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message :=

x_error_message || '~Price Break Ship To Organization Code: ' || px_po_lines_rec.price_break_ship_to_org

|| ' Exception' || SQLERRM;

END;

END IF;

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code

, x_error_code_temp);*/

-- Derivation of item id

IF px_po_lines_rec.item IS NOT NULL THEN

BEGIN

SELECT DISTINCT msi.inventory_item_id

INTO x_item_id

FROM mtl_system_items_b msi

WHERE msi.segment1 = px_po_lines_rec.item;

--AND msi.organization_id = x_ship_to_org_id;

IF x_item_id IS NOT NULL THEN

SELECT COUNT (1)

INTO x_purchase_enabled

FROM mtl_system_items_b msi

WHERE msi.purchasing_enabled_flag = 'Y'

AND msi.inventory_item_id = x_item_id;

--AND msi.organization_id = x_ship_to_org_id;

IF x_purchase_enabled > 0 THEN

x_error_code_temp := xxak_cnv_common_pkg.c_success;

px_po_lines_rec.item_id := x_item_id;

ELSE

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Item: ' || px_po_lines_rec.item || ' is not a Purchasable Item';

END IF;

ELSE

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Item: ' || px_po_lines_rec.item ||' is invalid';

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Item: ' || px_po_lines_rec.item || ' is invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Item: ' || px_po_lines_rec.item || ' Exception: ' || SQLERRM;

END;

END IF;

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

-- Item category

IF px_po_lines_rec.category_segment IS NOT NULL THEN

BEGIN

IF px_po_lines_rec.batch_name LIKE '%PMIS%' THEN

SELECT DISTINCT ORACLE_CATEGORY

INTO x_ref_category_segment

FROM XXAK_XREF_CATEGORTY_SEG

WHERE ORIG_CODE=px_po_lines_rec.category_segment;

ELSE

x_ref_category_segment := px_po_lines_rec.category_segment;

END IF;

SELECT DISTINCT mcb.category_id

INTO x_category_id

FROM mtl_categories_b mcb

WHERE mcb.segment1 = x_ref_category_segment;

IF x_category_id IS NOT NULL THEN

px_po_lines_rec.category_id := x_category_id;

ELSE

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Category: ' || px_po_lines_rec.category_segment || ' is invalid';

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~category: ' || px_po_lines_rec.category_segment || ' is invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Category: ' || px_po_lines_rec.category_segment || ' Exception: ' || SQLERRM;

END;

END IF;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

-- If Line Type equal to 'Goods', then Item should not be have outside Processing flag as 'Y'

IF px_po_lines_rec.line_type = 'Goods' AND x_item_id IS NOT NULL THEN

SELECT COUNT (1)

INTO x_outside_operation

FROM mtl_system_items_b msi

WHERE msi.outside_operation_flag = 'N'

AND msi.inventory_item_id = x_item_id;

--AND msi.organization_id = x_ship_to_org_id;

IF x_outside_operation > 0 THEN

x_error_code_temp := xxak_cnv_common_pkg.c_success;

ELSE

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Item :' || px_po_lines_rec.item || ' is OSP Enabled';

END IF;

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

-- If Line Type equal to 'Outside processing', then Item should be have outside operation flag as 'Y'

IF px_po_lines_rec.line_type = 'Outside processing' AND x_item_id IS NOT NULL THEN

SELECT COUNT (1)

INTO x_outside_operation

FROM mtl_system_items_b msi

WHERE msi.outside_operation_flag = 'Y'

AND msi.inventory_item_id = x_item_id;

--AND msi.organization_id = x_ship_to_org_id;

IF x_outside_operation > 0 THEN

x_error_code_temp := xxak_cnv_common_pkg.c_success;

ELSE

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~Item :' || px_po_lines_rec.item || ' is not OSP Enabled';

END IF;

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

-- Validation for UOM

IF x_item_id IS NOT NULL THEN

--AND x_ship_to_org_id IS NOT NULL) THEN

BEGIN

SELECT DISTINCT unit_of_measure

INTO x_units_of_measure

FROM mtl_units_of_measure

WHERE uom_code = px_po_lines_rec.uom_code;

px_po_lines_rec.unit_of_measure := x_units_of_measure;

--END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~UOM: ' || px_po_lines_rec.uom_code || ' is invalid';

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

x_error_message := x_error_message || '~UOM: ' || px_po_lines_rec.uom_code || SQLERRM;

END;

END IF;

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

px_po_lines_rec.ERROR_CODE := x_error_code;

px_po_lines_rec.error_desc := x_error_message;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

x_error_code_temp := xxak_cnv_common_pkg.c_rec_err;

-- Find Out Max Error Code

x_error_code := xxak_cnv_common_pkg.find_max (x_error_code , x_error_code_temp);

x_error_message := ' Unhandled Exception in data_val_for_lines: ' || x_error_message || ' ' || SQLERRM;

RETURN x_error_code;

END data_val_lines;

PROCEDURE update_lines_records(

p_po_lines_rec IN xxak_bpa_conv_line_stg%ROWTYPE ,

p_line_no IN NUMBER)

PRAGMA AUTONOMOUS_TRANSACTION;

-----------------------------------------------------------------------------------

-- Created By : Srikanth Yarram

-- Creation Date : 06-AUG-2013

-- RICE ID : 109

-- Description : Update the line staging fields with derived values

-- Latest Version : 1.0

-- Input Parameters description :

-- p_po_lines_rec : Update the header staging fields with derived values

-- Revision History:

--==================================================================================

--Date Version# Name Remarks

--==================================================================================

--06-AUG-2013 1.0 Srikanth Yarram Initial development.

BEGIN

UPDATE xxak_bpa_conv_line_stg

SET item_id = p_po_lines_rec.item_id ,

price_break_ship_to_org_id = p_po_lines_rec.price_break_ship_to_org_id ,

terms_id = p_po_lines_rec.terms_id ,

price_break_ship_to_loc_id = p_po_lines_rec.price_break_ship_to_loc_id ,

freight_carrier = p_po_lines_rec.freight_carrier,

line_type_id = p_po_lines_rec.line_type_id ,

unit_of_measure = p_po_lines_rec.unit_of_measure ,

ERROR_CODE = p_po_lines_rec.ERROR_CODE ,

error_desc = p_po_lines_rec.error_desc ,

process_code = g_stage

WHERE document_num = p_po_lines_rec.document_num

AND line_num = p_po_lines_rec.line_num;

COMMIT;

END update_lines_records;

FUNCTION update_preval_stg_rec(

p_batch_name VARCHAR2 ,

p_status VARCHAR2)

RETURN NUMBER

x_error_code NUMBER;

BEGIN

UPDATE xxak_bpa_conv_hdr_stg xphs

SET xphs.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

xphs.error_desc = xphs.error_desc

|| '~Error in line record' ,

xphs.process_code = p_status

WHERE EXISTS

(SELECT 1

FROM xxak_bpa_conv_line_stg xpls

WHERE xpls.batch_name = xphs.batch_name

--AND xpls.header_record_number = xphs.record_number

AND xpls.Document_num = xphs.document_num

AND xpls.ERROR_CODE = xxak_cnv_common_pkg.c_rec_err

AND xpls.process_code = p_status

AND xpls.batch_name = p_batch_name

);

COMMIT;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG , 'Unhandled Exception In update_preval_stg_rec PO Conversion...' || SQLERRM);

RETURN xxak_cnv_common_pkg.c_prc_err;

END;

PROCEDURE update_header_status(

p_batch_name IN VARCHAR2)

CURSOR cur_header

SELECT xph.document_num

FROM xxak_bpa_conv_hdr_stg xph

WHERE xph.batch_name = p_batch_name

AND RTRIM (LTRIM (xph.error_desc) ) = c_po_num_exist

ORDER BY xph.document_num;

CURSOR cur_line (

--cp_hdr_num NUMBER)

cp_doc_num NUMBER)

SELECT

xpl.line_num ,

xpl.document_num

FROM xxak_bpa_conv_line_stg xpl

WHERE xpl.document_num = cp_doc_num --xpl.header_record_number = cp_hdr_num

AND xpl.batch_name = p_batch_name

AND xpl.error_desc IS NULL

ORDER BY document_num;

x_status VARCHAR2 (10);

BEGIN

FOR fetch_header IN cur_header

LOOP

x_status := NULL;

FOR fetch_line IN cur_line (fetch_header.document_num) --fetch_header.record_number)

LOOP

BEGIN

x_status := 'UPDATE';

END;

END LOOP;

IF x_status IS NOT NULL THEN

UPDATE xxak_bpa_conv_hdr_stg xph

SET xph.action = x_status ,

xph.ERROR_CODE = xxak_cnv_common_pkg.c_success ,

xph.process_code = g_stage

WHERE xph.batch_name = p_batch_name

--AND xph.record_number = fetch_header.record_number;

AND xph.document_num = fetch_header.document_num;

END IF;

END LOOP;

COMMIT;

END update_header_status;

FUNCTION process_data

RETURN VARCHAR2

-----------------------------------------------------------------------------------

-- Created By : Srikanth Yarram

-- Creation Date : 06-AUG-2013

-- RICE ID : C4

-- Description : MProcessing data and load the data into base table using API

-- Latest Version : 1.0

-- Input Parameters description :

-- NONE

-- Output Parameters Description

-- Return : Returns the Error code

-- Revision History:

--==================================================================================

--Date Version# Name Remarks

--==================================================================================

--06-AUG-2013 1.0 Srikanth Yarram Initial development.

------------------------------------------------------------------------------------

CURSOR cur_get_header_err_mess

SELECT phi.attribute8 ,

phi.document_num ,

pie.error_message

FROM po_headers_interface phi ,

po_interface_errors pie

WHERE phi.interface_header_id = pie.interface_header_id

AND pie.table_name IN (c_header_int_name)

AND process_code = c_reject_process_code;

CURSOR cur_get_lines_err_mess

SELECT pl.line_attribute8 ,

pl.document_num ,

pl.line_num ,

pie.error_message

FROM po_lines_interface pl ,

po_interface_errors pie

WHERE pl.interface_line_id = pie.interface_line_id

AND pie.table_name IN (c_line_int_name)

AND process_code = c_reject_process_code;

CURSOR cur_po_header_dtl_Con

SELECT *

FROM xxak_bpa_conv_hdr_stg

WHERE batch_name = g_batch_name

AND request_id = g_request_id

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn)

AND process_code = xxak_cnv_common_pkg.c_valid

AND DOCUMENT_TYPE_CODE = 'CONTRACT';

CURSOR cur_po_header_dtl

SELECT *

FROM xxak_bpa_conv_hdr_stg

WHERE batch_name = g_batch_name

AND request_id = g_request_id

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn)

AND process_code = xxak_cnv_common_pkg.c_valid

AND DOCUMENT_TYPE_CODE = 'BLANKET';

CURSOR cur_po_lines_dtl (

cp_po_doc_num IN VARCHAR2)

SELECT *

FROM xxak_bpa_conv_line_stg

WHERE batch_name = g_batch_name

AND request_id = g_request_id

--AND header_record_number = cp_po_hdr_rec

AND document_num = cp_po_doc_num

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn)

AND process_code = xxak_cnv_common_pkg.c_valid;

x_error_code VARCHAR2 (1) := xxak_cnv_common_pkg.c_success;

x_request_id NUMBER;

x_wait BOOLEAN;

x_phase VARCHAR2 (50);

x_dex_phase VARCHAR2 (50);

x_interval NUMBER := 30;

x_max_wait NUMBER := 1800;

x_status VARCHAR2 (2000) := '';

x_del_status VARCHAR2 (2000) := '';

x_message VARCHAR2 (2000) := '';

x_val NUMBER;

x_val_line NUMBER;

x_step NUMBER;

x_org_id NUMBER;

x_header_id NUMBER;

x_rowid NUMBER;

x_summary_flag VARCHAR2(1) :='N';----- These are common flag's for loading data

x_enabled_flag VARCHAR2(1) :='Y';

x_approved_flag VARCHAR2(1) :='Y';

x_confirming_order_flag VARCHAR2(1) :='N';

x_acceptance_required_flag VARCHAR2(1) :='N';

x_cancel_flag VARCHAR2(1) :='N';

x_firm_status_lookup_code VARCHAR2(1) :='N';

x_frozen_flag VARCHAR2(1) :='N';

x_supply_agreement_flag VARCHAR2(1) :='N';

x_print_count NUMBER :=0;

X_return_status VARCHAR2(10);

X_error_msg VARCHAR2(200);

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

DELETE

FROM po_headers_interface;

DELETE FROM po_lines_interface;

DELETE FROM po_interface_errors;

COMMIT;

FOR fetch_hdr_rec_Con IN cur_po_header_dtl_Con

LOOP

BEGIN

PO_HEADERS_SV11.INSERT_PO(

X_ROWID => x_header_id ,

X_PO_HEADER_ID => x_rowid ,

X_AGENT_ID =>fetch_hdr_rec_Con.AGENT_ID,

X_TYPE_LOOKUP_CODE => fetch_hdr_rec_Con.DOCUMENT_TYPE_CODE ,

X_LAST_UPDATE_DATE =>fetch_hdr_rec_Con.LAST_UPDATED_DATE,

X_LAST_UPDATED_BY =>fetch_hdr_rec_Con.LAST_UPDATED_BY ,

X_SEGMENT1 => fetch_hdr_rec_Con.DOCUMENT_NUM ,

X_SUMMARY_FLAG => x_summary_flag ,

X_ENABLED_FLAG => x_enabled_flag,

X_SEGMENT2 => NULL ,

X_SEGMENT3 => NULL,

X_SEGMENT4 => NULL,

X_SEGMENT5 => NULL,

X_START_DATE_ACTIVE => NULL,

X_END_DATE_ACTIVE => NULL,

X_LAST_UPDATE_LOGIN =>fetch_hdr_rec_Con.LAST_UPDATE_LOGIN ,

X_CREATION_DATE => fetch_hdr_rec_Con.CREATION_DATE,

X_CREATED_BY => fetch_hdr_rec_Con.CREATED_BY ,

X_VENDOR_ID => fetch_hdr_rec_Con.VENDOR_ID,

X_VENDOR_SITE_ID => fetch_hdr_rec_Con.VENDOR_SITE_ID,

X_VENDOR_CONTACT_ID => NULL,

X_SHIP_TO_LOCATION_ID => fetch_hdr_rec_Con.SHIP_TO_LOCATION_ID,

X_BILL_TO_LOCATION_ID => fetch_hdr_rec_Con.BILL_TO_LOCATION_ID ,

X_TERMS_ID => fetch_hdr_rec_Con.TERMS_ID ,

X_SHIP_VIA_LOOKUP_CODE => NULL,

X_FOB_LOOKUP_CODE => NULL,

X_PAY_ON_CODE => NULL,

X_FREIGHT_TERMS_LOOKUP_CODE => NULL,

X_STATUS_LOOKUP_CODE => NULL,

X_CURRENCY_CODE => fetch_hdr_rec_Con.currency ,

X_RATE_TYPE => NULL,

X_RATE_DATE => NULL,

X_RATE => NULL,

X_FROM_HEADER_ID => NULL,

X_FROM_TYPE_LOOKUP_CODE => NULL,

X_START_DATE => fetch_hdr_rec_Con.START_DATE,

X_END_DATE => fetch_hdr_rec_Con.END_DATE,

X_BLANKET_TOTAL_AMOUNT => fetch_hdr_rec_Con.AMT_LIMIT ,

X_AUTHORIZATION_STATUS => 'APPROVED',

X_REVISION_NUM => fetch_hdr_rec_Con.REVISION_NUM,

X_REVISED_DATE => NULL,

X_APPROVED_FLAG => x_approved_flag ,

X_APPROVED_DATE => NULL,

X_AMOUNT_LIMIT => fetch_hdr_rec_Con.AMT_LIMIT,

X_MIN_RELEASE_AMOUNT => NULL,

X_NOTE_TO_AUTHORIZER => NULL,

X_NOTE_TO_VENDOR => fetch_hdr_rec_Con.NOTE_TO_VENDOR,

X_NOTE_TO_RECEIVER => fetch_hdr_rec_Con.NOTE_TO_RECEIVER ,

X_PRINT_COUNT => x_print_count,

X_PRINTED_DATE => NULL,

X_VENDOR_ORDER_NUM => NULL,

X_CONFIRMING_ORDER_FLAG => x_confirming_order_flag ,

X_COMMENTS => fetch_hdr_rec_Con.COMMENTS,

X_REPLY_DATE => NULL,

X_REPLY_METHOD_LOOKUP_CODE => NULL,

X_RFQ_CLOSE_DATE => NULL,

X_QUOTE_TYPE_LOOKUP_CODE => NULL,

X_QUOTATION_CLASS_CODE => NULL,

X_QUOTE_WARNING_DELAY_UNIT => NULL,

X_QUOTE_WARNING_DELAY => NULL,

X_QUOTE_VENDOR_QUOTE_NUMBER => NULL,

X_ACCEPTANCE_REQUIRED_FLAG => x_acceptance_required_flag,

X_ACCEPTANCE_DUE_DATE => NULL,

X_CLOSED_DATE => NULL,

X_USER_HOLD_FLAG => NULL,

X_APPROVAL_REQUIRED_FLAG => NULL,

X_CANCEL_FLAG => x_cancel_flag,

X_FIRM_STATUS_LOOKUP_CODE => x_firm_status_lookup_code ,

X_FIRM_DATE => NULL,

X_FROZEN_FLAG => x_frozen_flag ,

X_SUPPLY_AGREEMENT_FLAG => x_supply_agreement_flag ,

X_GLOBAL_AGREEMENT_FLAG => NULL,

X_ATTRIBUTE_CATEGORY => NULL,

X_ATTRIBUTE1 => NULL,

X_ATTRIBUTE2 => NULL,

X_ATTRIBUTE3 => NULL,

X_ATTRIBUTE4 => NULL,

X_ATTRIBUTE5 => NULL,

X_ATTRIBUTE6 => NULL,

X_ATTRIBUTE7 => NULL,

X_ATTRIBUTE8 => NULL,

X_ATTRIBUTE9 => NULL,

X_ATTRIBUTE10 => NULL,

X_ATTRIBUTE11 => NULL,

X_ATTRIBUTE12 => NULL,

X_ATTRIBUTE13 => NULL,

X_ATTRIBUTE14 => NULL,

X_ATTRIBUTE15 => NULL,

X_CLOSED_CODE => NULL,

X_USSGL_TRANSACTION_CODE => NULL,

X_GOVERNMENT_CONTEXT => NULL,

X_GLOBAL_ATTRIBUTE_CATEGORY => NULL,

X_GLOBAL_ATTRIBUTE1 => NULL,

X_GLOBAL_ATTRIBUTE2 => NULL,

X_GLOBAL_ATTRIBUTE3 => NULL,

X_GLOBAL_ATTRIBUTE4 => NULL,

X_GLOBAL_ATTRIBUTE5 => NULL,

X_GLOBAL_ATTRIBUTE6 => NULL,

X_GLOBAL_ATTRIBUTE7 => NULL,

X_GLOBAL_ATTRIBUTE8 => NULL,

X_GLOBAL_ATTRIBUTE9 => NULL,

X_GLOBAL_ATTRIBUTE10 => NULL,

X_GLOBAL_ATTRIBUTE11 => NULL,

X_GLOBAL_ATTRIBUTE12 => NULL,

X_GLOBAL_ATTRIBUTE13 => NULL,

X_GLOBAL_ATTRIBUTE14 => NULL,

X_GLOBAL_ATTRIBUTE15 => NULL,

X_GLOBAL_ATTRIBUTE16 => NULL,

X_GLOBAL_ATTRIBUTE17 => NULL,

X_GLOBAL_ATTRIBUTE18 => NULL,

X_GLOBAL_ATTRIBUTE19 => NULL,

X_GLOBAL_ATTRIBUTE20 => NULL,

X_MANUAL => NULL,

X_PRICE_UPDATE_TOLERANCE => NULL,

P_SHIPPING_CONTROL => NULL,

P_ENCUMBRANCE_REQUIRED_FLAG => NULL,

P_ORG_ID => fetch_hdr_rec_Con.ORG_ID, -- p_org_id

p_enable_all_sites => NULL --p_enable_all_sites

);

EXCEPTION

WHEN OTHERS THEN

X_return_status :='E';

X_error_msg := X_error_msg || 'ERROR during Insert in PO_HEADERS.'||CHR(10)||SQLERRM;

fnd_file.put_line (fnd_file.LOG , 'Error in process data in Contract header record insert : ' || SQLERRM);

END;

COMMIT;

END LOOP;

FOR fetch_hdr_rec IN cur_po_header_dtl

LOOP

BEGIN

INSERT

INTO po_headers_interface

(

interface_header_id,

action ,

org_id ,

document_type_code,

document_num ,

currency_code ,

agent_id ,

vendor_id ,

vendor_site_id ,

vendor_contact_id ,

ship_to_location_id ,

bill_to_location_id ,

terms_id ,

freight_terms ,

approval_status ,

revision_num ,

note_to_vendor ,

note_to_receiver ,

comments,

creation_date ,

created_by ,

last_update_date ,

last_updated_by ,

last_update_login ,

attribute8,

attribute10 ,

amount_limit ,

effective_date ,

expiration_date ,

amount_agreed

)

VALUES

(

po_headers_interface_s.NEXTVAL,

'ORIGINAL' ,--fetch_hdr_rec.action

fetch_hdr_rec.org_id ,

fetch_hdr_rec.DOCUMENT_TYPE_CODE, --'BLANKET' --fetch_hdr_rec.document_type_code

TO_CHAR (fetch_hdr_rec.document_num) ,

fetch_hdr_rec.currency ,

fetch_hdr_rec.agent_id ,

fetch_hdr_rec.vendor_id ,

fetch_hdr_rec.vendor_site_id ,

fetch_hdr_rec.vendor_contact_id ,

fetch_hdr_rec.ship_to_location_id ,

fetch_hdr_rec.bill_to_location_id ,

fetch_hdr_rec.terms_id ,

fetch_hdr_rec.freight_terms ,

'APPROVED' ,-- fetch_hdr_rec.approval_status

c_revision_number , -----fetch_hdr_rec.revision_num

fetch_hdr_rec.note_to_vendor ,

fetch_hdr_rec.note_to_receiver ,

fetch_hdr_rec.comments ,

SYSDATE ,

fnd_global.user_id ,

SYSDATE ,

fnd_global.user_id ,

fnd_global.user_id , --, fnd_global.login_id

fetch_hdr_rec.batch_name,

fetch_hdr_rec.batch_name ,

fetch_hdr_rec.amt_limit ,

fetch_hdr_rec.start_date ,

fetch_hdr_rec.end_date ,

fetch_hdr_rec.amt_limit

);

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG , 'Error in process data in header record insert : ' || SQLERRM);

END;

x_step := 10;

x_step := 20;

SELECT po_headers_interface_s.CURRVAL

INTO x_val

FROM DUAL;

FOR fetch_po_line_rec IN cur_po_lines_dtl (fetch_hdr_rec.document_num) --(fetch_hdr_rec.record_number)

LOOP

BEGIN

INSERT

INTO po_lines_interface

(

interface_header_id ,

interface_line_id ,

line_num ,

document_num ,

line_type ,

item ,

unit_of_measure ,

unit_price ,

note_to_vendor ,

terms_id ,

line_type_id ,

ship_to_organization_id ,

ship_to_location_id ,

freight_carrier ,

creation_date ,

created_by ,

last_update_date ,

last_updated_by ,

last_update_login ,

line_attribute8 ,

line_attribute10 ,

item_id ,

item_description ,

Category

)

VALUES

(

x_val ,

po_lines_interface_s.NEXTVAL ,

fetch_po_line_rec.line_num ,

fetch_po_line_rec.document_num ,

fetch_po_line_rec.line_type ,

fetch_po_line_rec.item ,

fetch_po_line_rec.unit_of_measure ,

fetch_po_line_rec.unit_price ,

fetch_po_line_rec.note_to_vendor ,

fetch_po_line_rec.terms_id ,

fetch_po_line_rec.line_type_id ,

fetch_po_line_rec.price_break_ship_to_org_id ,

fetch_po_line_rec.price_break_ship_to_loc_id ,

fetch_po_line_rec.freight_carrier ,

SYSDATE ,

fnd_global.user_id ,

SYSDATE ,

fnd_global.user_id ,

fnd_global.user_id , --, fnd_global.login_id

fetch_po_line_rec.batch_name ,

fetch_po_line_rec.item_id ,

fetch_po_line_rec.item_description ,

fetch_po_line_rec.category_segment

);

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG , 'Error while inserting into po_lines_interface insert: ' || SQLERRM);

END;

END LOOP;

COMMIT;

BEGIN

/*FOR fetch_get_org_id IN cur_get_org_id----- The standard concurrent program submitted separately

LOOP

fnd_file.put_line (fnd_file.LOG

, 'Submitting Po Interface Program for Org id: ' || x_org_id);

x_request_id :=

fnd_request.submit_request (application => c_appl_short_name

, program => 'POXPOPDOI'

, description => NULL

, start_time => SYSDATE

, sub_request => FALSE

, argument1 => NULL

, argument2 => c_blanket_doc_type_code

, argument3 => NULL

, argument4 => 'N'

, argument5 => 'N'

, argument6 => 'APPROVED'

, argument7 => NULL

, argument8 => fetch_get_org_id.batch_name

, argument9 => fetch_get_org_id.org_id

, argument10 => 'N'

, argument11 => NULL

, argument12 => NULL

, argument13 => NULL

, argument14 => NULL);

COMMIT;

END LOOP;

-- Wait till the concurrent program is complete

x_phase := 'START';

x_dex_phase := 'START';

WHILE x_dex_phase <> 'COMPLETE'

LOOP

x_wait :=

fnd_concurrent.wait_for_request (x_request_id

, x_interval

, x_max_wait

, x_phase

, x_status

, x_dex_phase

, x_del_status

, x_message);

END LOOP;*/

FOR fetch_err_hdr IN cur_get_header_err_mess

LOOP

UPDATE xxak_bpa_conv_hdr_stg

SET error_desc = error_desc

|| fetch_err_hdr.error_message ,

ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

process_code = xxak_cnv_common_pkg.c_process_data

WHERE batch_name = g_batch_name

--AND record_number = fetch_err_hdr.attribute9

AND document_num = fetch_err_hdr.document_num

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn);

COMMIT;

END LOOP;

FOR fetch_err_lin IN cur_get_lines_err_mess

LOOP

UPDATE xxak_bpa_conv_line_stg

SET error_desc = error_desc

|| fetch_err_lin.error_message ,

ERROR_CODE = xxak_cnv_common_pkg.c_rec_err ,

process_code = xxak_cnv_common_pkg.c_process_data

WHERE batch_name = g_batch_name

--AND header_record_number || '~' || record_number = fetch_err_lin.line_attribute9

AND document_num = fetch_err_lin.document_num

AND line_num = fetch_err_lin.line_num

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn);

COMMIT;

END LOOP;

END;

COMMIT;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG , 'Unhandeled Exception in process data section ' || SQLERRM || x_step);

END process_data;

PROCEDURE mark_records_complete(

p_process_code IN VARCHAR2)

-----------------------------------------------------------------------------------

-- Created By : Srikanth Yarram

-- Creation Date : 06-AUG-2013

-- RICE ID : C4

-- Description : This procedure procedure is not applicable for data lodding through API

-- Latest Version : 1.0

-- Input Parameters description :

-- NO input Parameter

-- Output Parameters description :

-- NO output Parameter

-- Revision History:

--==================================================================================

--Date Version# Name Remarks

--==================================================================================

--06-AUG-2013 1.0 Srikanth Yarram Initial development.

------------------------------------------------------------------------------------

x_last_update_date DATE := SYSDATE;

x_last_update_by NUMBER := fnd_global.user_id;

x_last_updated_login NUMBER := fnd_global.login_id;

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

UPDATE xxak_bpa_conv_hdr_stg

SET process_code = p_process_code ,

ERROR_CODE = NVL (ERROR_CODE , xxak_cnv_common_pkg.c_success) ,

last_updated_by = x_last_update_by ,

last_updated_date = x_last_update_date ,

last_update_login = x_last_updated_login

WHERE batch_name = g_batch_name

AND request_id = g_request_id

AND process_code = DECODE (g_stage , xxak_cnv_common_pkg.c_process_data, xxak_cnv_common_pkg.c_valid , xxak_cnv_common_pkg.c_valid)

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn);

UPDATE xxak_bpa_conv_line_stg

SET process_code = p_process_code ,

ERROR_CODE = NVL (ERROR_CODE , xxak_cnv_common_pkg.c_success) ,

last_updated_by = x_last_update_by ,

last_update_date = x_last_update_date ,

last_update_login = x_last_updated_login

WHERE batch_name = g_batch_name

AND request_id = g_request_id

AND process_code = DECODE (g_stage , xxak_cnv_common_pkg.c_process_data, xxak_cnv_common_pkg.c_valid , xxak_cnv_common_pkg.c_valid)

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn);

COMMIT;

END mark_records_complete;

PROCEDURE data_stats_err_msg_report(

p_batch_name IN VARCHAR2 ,

p_val_upl_flag IN VARCHAR2)

-------------------------------------------------------------------------------------

-- Created By : Srikanth Yarram

-- Creation Date : 06-AUG-2013

-- RICE ID : C4

-- File Name : XXAKBPACNV.pks

-- Description : Summary of the whole process and the failed records details.

-- Latest Version : 1.0

-- Input Parameters description :

-- Batch ID : Batch ID

-- Revision History:

--====================================================================================

--Date Version# Name Remarks

--====================================================================================

--06-AUG-2013 1.0 Srikanth Yarram Initial development.

--------------------------------------------------------------------------------------

x_status CHAR (18);

x_record_number CHAR (15);

x_formula_no CHAR (17);

x_dist_no NUMBER;

x_line_no NUMBER;

x_dsp VARCHAR2 (4000);

x_dsp_Con VARCHAR2 (4000);

x_total_rec NUMBER := 0;

x_total_rec_BPA NUMBER := 0;

x_success_rec NUMBER := 0;

x_failed_rec NUMBER := 0;

x_total_rec_Con NUMBER := 0;

x_success_rec_Con NUMBER := 0;

x_failed_rec_Con NUMBER := 0;

CURSOR cur_header

SELECT DISTINCT xphs.process_code status ,

xphs.batch_name ,

xphs.document_num ,

REPLACE (SUBSTR (NVL (xphs.error_desc , '~Failed to pick by Process Data routine') , 2 , LENGTH (NVL (xphs.error_desc , '~Failed to pick by Process Data routine') ) ) , '~' , CHR (10)

|| RPAD (' ' , 66 , ' ') ) error_desc

FROM xxak_bpa_conv_hdr_stg xphs

WHERE 1 = 1

AND xphs.batch_name = p_batch_name

AND xphs.request_id = g_request_id

AND xphs.error_desc IS NOT NULL

AND xphs.DOCUMENT_TYPE_CODE = 'BLANKET'

ORDER BY document_num;

CURSOR cur_header_Con

SELECT DISTINCT xphs.process_code status ,

xphs.batch_name

--, xphs.record_number

xphs.document_num ,

REPLACE (SUBSTR (NVL (xphs.error_desc , '~Failed to pick by Process Data routine') , 2 , LENGTH (NVL (xphs.error_desc , '~Failed to pick by Process Data routine') ) ) , '~' , CHR (10)

|| RPAD (' ' , 66 , ' ') ) error_desc

FROM xxak_bpa_conv_hdr_stg xphs

WHERE 1 = 1

AND xphs.batch_name = p_batch_name

AND xphs.request_id = g_request_id

AND xphs.error_desc IS NOT NULL

AND xphs.DOCUMENT_TYPE_CODE = 'CONTRACT'

ORDER BY document_num;

CURSOR cur_line(p_document_num VARCHAR2)

SELECT DISTINCT xpls.process_code status ,

xpls.batch_name

--, xpls.record_number

--, xpls.header_record_number

xpls.document_num ,

xpls.line_num ,

REPLACE (LPAD (SUBSTR (xpls.error_desc , 2) , (LENGTH (xpls.error_desc) ) , ' ') , '~' , CHR (10)

|| RPAD (' ' , 81 , ' ') ) error_desc

FROM xxak_bpa_conv_line_stg xpls

WHERE 1 = 1

AND xpls.batch_name = p_batch_name

AND xpls.request_id = g_request_id

AND xpls.document_num = p_document_num

AND xpls.error_desc IS NOT NULL

--ORDER BY record_number;

ORDER BY document_num,

line_num;

BEGIN

SELECT COUNT (1)

INTO x_total_rec

FROM xxak_bpa_conv_hdr_stg

WHERE batch_name = p_batch_name;

SELECT COUNT (1)

INTO x_total_rec_BPA

FROM xxak_bpa_conv_hdr_stg

WHERE batch_name = p_batch_name

AND DOCUMENT_TYPE_CODE= 'BLANKET';

SELECT COUNT (1)

INTO x_success_rec

FROM xxak_bpa_conv_hdr_stg

WHERE 1 = 1

AND request_id = g_request_id

AND process_code = DECODE (p_val_upl_flag , c_val_upl_flag, xxak_cnv_common_pkg.c_process_data , xxak_cnv_common_pkg.c_valid)

AND ERROR_CODE = xxak_cnv_common_pkg.c_success

AND DOCUMENT_TYPE_CODE= 'BLANKET';

SELECT COUNT (1)

INTO x_failed_rec

FROM xxak_bpa_conv_hdr_stg

WHERE batch_name = p_batch_name

AND request_id = g_request_id

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_rec_err, xxak_cnv_common_pkg.c_rec_warn)

AND DOCUMENT_TYPE_CODE= 'BLANKET';

SELECT COUNT (1)

INTO x_total_rec_Con

FROM xxak_bpa_conv_hdr_stg

WHERE batch_name = p_batch_name

AND DOCUMENT_TYPE_CODE= 'CONTRACT';

SELECT COUNT (1)

INTO x_success_rec_Con

FROM xxak_bpa_conv_hdr_stg

WHERE 1 = 1

AND request_id = g_request_id

AND process_code = DECODE (p_val_upl_flag , c_val_upl_flag, xxak_cnv_common_pkg.c_process_data , xxak_cnv_common_pkg.c_valid)

AND ERROR_CODE = xxak_cnv_common_pkg.c_success

AND DOCUMENT_TYPE_CODE= 'CONTRACT';

SELECT COUNT (1)

INTO x_failed_rec_Con

FROM xxak_bpa_conv_hdr_stg

WHERE batch_name = p_batch_name

AND request_id = g_request_id

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_rec_err, xxak_cnv_common_pkg.c_rec_warn)

AND DOCUMENT_TYPE_CODE= 'CONTRACT';

fnd_file.put_line (fnd_file.output , 'Total Number Of Records :=>' || x_total_rec);

fnd_file.put_line (fnd_file.output , 'Total Number Of BPA Records :=>' || x_total_rec_BPA);

fnd_file.put_line (fnd_file.output , 'Total Number of BPA Successful Records :=>' || x_success_rec);

fnd_file.put_line (fnd_file.output , 'Total Number of BPA Failed Records :=>' || x_failed_rec);

fnd_file.put_line (fnd_file.output , 'Total Number Of CPA Records :=>' || x_total_rec_Con);

fnd_file.put_line (fnd_file.output , 'Total Number of CPA Successful Records :=>' || x_success_rec_Con);

fnd_file.put_line (fnd_file.output , 'Total Number of CPA Failed Records :=>' || x_failed_rec_Con);

fnd_file.put_line (fnd_file.output , ' ');

fnd_file.put_line (fnd_file.output , ' ***** Purchase Order Validation Status *****');

FOR fetch_header_rec IN cur_header

LOOP

SELECT RPAD (fetch_header_rec.status , 16)

|| RPAD (fetch_header_rec.batch_name , 22)

|| RPAD (fetch_header_rec.document_num , 24)

|| fetch_header_rec.error_desc

INTO x_dsp

FROM DUAL;

fnd_file.put_line (fnd_file.output , ' ');

fnd_file.put_line (fnd_file.output , 'BPA Header Level Error Details : ');

fnd_file.put_line (fnd_file.output , '----------------------------------');

fnd_file.put_line (fnd_file.output , 'Stage Batch Document Number Error Description ');

fnd_file.put_line (fnd_file.output , '----------- ------------------- ------------------ ------------------------');

fnd_file.put_line (fnd_file.output , '' || x_dsp);

fnd_file.put_line (fnd_file.output , ' ');

fnd_file.put_line (fnd_file.output , 'Line Level Error Details : ');

fnd_file.put_line (fnd_file.output , '----------------------------------');

fnd_file.put_line (fnd_file.output , 'Stage Batch Document Number Line number Error Description ');

fnd_file.put_line (fnd_file.output , '--------- -------------------- ----------------- ------------- ------------------');

--open cur_line(fetch_header_rec.document_num);

FOR fetch_lin_rec IN cur_line (fetch_header_rec.document_num)

LOOP

SELECT RPAD (fetch_lin_rec.status , 14)

|| RPAD (fetch_lin_rec.batch_name , 25)

|| RPAD (fetch_lin_rec.document_num , 26)

|| RPAD (fetch_lin_rec.line_num , 16)

|| fetch_lin_rec.error_desc

INTO x_dsp

FROM DUAL;

fnd_file.put_line (fnd_file.output , '' || x_dsp);

END LOOP;

END LOOP; -- Hdr loop

--close cur_line;

FOR fetch_header_rec IN cur_header_Con

LOOP

SELECT RPAD (fetch_header_rec.status , 16)

|| RPAD (fetch_header_rec.batch_name , 22)

|| RPAD (fetch_header_rec.document_num , 24)

|| fetch_header_rec.error_desc

INTO x_dsp_Con

FROM DUAL;

fnd_file.put_line (fnd_file.output , ' ');

fnd_file.put_line (fnd_file.output , 'CPA Error Details : ');

fnd_file.put_line (fnd_file.output , '----------------------------------');

fnd_file.put_line (fnd_file.output , 'Stage Batch Document Number Error Description ');

fnd_file.put_line (fnd_file.output , '----------- ------------------- ------------------ ------------------------');

fnd_file.put_line (fnd_file.output , '' || x_dsp_Con);

fnd_file.put_line (fnd_file.output , ' ');

END LOOP;

--------------------------------

fnd_file.put_line (fnd_file.output , ' ');

fnd_file.put_line (fnd_file.output , '----------------------------------');

fnd_file.put_line (fnd_file.output , ' ');

fnd_file.put_line (fnd_file.output , ' ***** End of PO Conversion Error Report *****');

END data_stats_err_msg_report;

/*FUNCTION insert_arch_tbl (

p_batch_name NUMBER)

RETURN VARCHAR2

PRAGMA AUTONOMOUS_TRANSACTION;

x_err_code VARCHAR2 (1) := xxak_cnv_common_pkg.c_success;

BEGIN

INSERT INTO xxak_bpa_conv_hdr_arch

SELECT *

FROM xxak_bpa_conv_hdr_stg

WHERE batch_name = p_batch_name

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn)

AND process_code = xxak_cnv_common_pkg.c_process_data

AND request_id = g_request_id;

INSERT INTO xxak_bpa_conv_line_arch

SELECT *

FROM xxak_bpa_conv_line_stg

WHERE batch_name = p_batch_name

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn)

AND process_code = xxak_cnv_common_pkg.c_process_data

AND request_id = g_request_id;

COMMIT;

RETURN x_err_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.output

, 'Unexpected Error in Inserting data into Archive Tables ' || SQLERRM);

RETURN xxak_cnv_common_pkg.c_rec_err;

END insert_arch_tbl;*/

/*FUNCTION delete_stg_tbl (

p_batch_name NUMBER)

RETURN VARCHAR2

PRAGMA AUTONOMOUS_TRANSACTION;

x_err_code VARCHAR2 (1) := xxak_cnv_common_pkg.c_success;

BEGIN

DELETE xxak_bpa_conv_hdr_stg

WHERE batch_name = p_batch_name

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn)

AND process_code = xxak_cnv_common_pkg.c_process_data

AND request_id = g_request_id;

DELETE xxak_bpa_conv_line_stg

WHERE batch_name = p_batch_name

AND ERROR_CODE IN (xxak_cnv_common_pkg.c_success, xxak_cnv_common_pkg.c_rec_warn)

AND process_code = xxak_cnv_common_pkg.c_process_data

AND request_id = g_request_id;

COMMIT;

RETURN x_err_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Unexpected Error in Deleting data from staging Tables ' || SQLERRM);

RETURN xxak_cnv_common_pkg.c_rec_err;

END delete_stg_tbl;*/

END XXAK_PO_CNV_04_IMPORT_BPA_PKG;

Common Package:

CREATE OR REPLACE PACKAGE xxak_cnv_common_pkg

-- Moved from common constant package

c_success CONSTANT VARCHAR2 (1) := '0';

c_rec_warn CONSTANT VARCHAR2 (1) := '1';

c_rec_err CONSTANT VARCHAR2 (1) := '2';

c_prc_err CONSTANT VARCHAR2 (1) := '3';

c_exp_unhand CONSTANT VARCHAR2 (42) := 'Unhandled exception in procedure/function ';

c_high CONSTANT NUMBER := 3;

c_medium CONSTANT NUMBER := 2;

c_low CONSTANT NUMBER := 1;

c_new CONSTANT VARCHAR2 (200) := 'New';

c_preval CONSTANT VARCHAR2 (200) := 'Pre-Val';

c_batchval CONSTANT VARCHAR2 (200) := 'Batch Val';

c_valid CONSTANT VARCHAR2 (200) := 'Data Val';

c_batchder CONSTANT VARCHAR2 (200) := 'Batch Derive';

c_derive CONSTANT VARCHAR2 (200) := 'Data Derive';

c_postval CONSTANT VARCHAR2 (200) := 'Post Val';

c_api CONSTANT VARCHAR2 (200) := 'API';

c_process_data CONSTANT VARCHAR2 (200) := 'Process Data';

c_null CONSTANT VARCHAR2 (200) := NULL;

c_yes VARCHAR2 (2) := 'Y';

c_no VARCHAR2 (2) := 'N';

FUNCTION find_max (

p_error_code1 VARCHAR2

, p_error_code2 VARCHAR2)

RETURN VARCHAR2;

FUNCTION get_new_operating_unit (

p_legacy_operating_unit_name IN VARCHAR2

, x_new_org_id OUT NUMBER)

RETURN NUMBER;

FUNCTION get_new_uom (

p_inventory_item_no IN mtl_system_items_b.segment1%TYPE DEFAULT NULL

, p_organization_id IN mtl_system_items_b.organization_id%TYPE DEFAULT NULL

, x_new_uom_code OUT VARCHAR2)

RETURN NUMBER;

FUNCTION get_inventory_item_id (

p_item_no IN VARCHAR2 DEFAULT NULL

, p_organization_id IN mtl_system_items_b.organization_id%TYPE DEFAULT NULL

, x_inventory_item_id OUT mtl_system_items_b.inventory_item_id%TYPE)

RETURN NUMBER;

FUNCTION get_vendor_id (

p_vendor_num IN VARCHAR2 DEFAULT NULL

, x_vendor_id OUT po_vendors.vendor_id%TYPE)

RETURN NUMBER;

FUNCTION get_location_id (

p_location IN VARCHAR2 DEFAULT NULL

, x_location_id OUT NUMBER)

RETURN NUMBER;

FUNCTION get_vendor_id_frm_num (

p_vendor_num IN VARCHAR2 DEFAULT NULL

, p_vendor_name IN VARCHAR2 DEFAULT NULL

, x_vendor_id OUT NUMBER)

RETURN NUMBER;

FUNCTION get_emp_id_frm_nt_id (

p_nt_id IN VARCHAR2 DEFAULT NULL

, p_orgn_code IN VARCHAR2

, x_person_id OUT NUMBER)

RETURN NUMBER;

FUNCTION get_organization_code (

p_organization_id IN org_organization_definitions.organization_id%TYPE

, x_organization_code OUT org_organization_definitions.organization_code%TYPE)

RETURN NUMBER;

FUNCTION get_organization_id (

p_organization_code IN org_organization_definitions.organization_code%TYPE

, x_organization_id OUT org_organization_definitions.organization_id%TYPE)

RETURN NUMBER;

FUNCTION get_operatingunit_code (

p_operatingunit_code IN fnd_lookup_values.meaning%TYPE

, x_operatingunit_code OUT fnd_lookup_values.description%TYPE)

RETURN NUMBER;

FUNCTION get_uom_code (

p_uom_code IN fnd_lookup_values.meaning%TYPE

, x_uom_code OUT fnd_lookup_values.description%TYPE)

RETURN NUMBER;

FUNCTION get_customer_num (

p_customer_num IN fnd_lookup_values.meaning%TYPE

, x_customer_num OUT fnd_lookup_values.description%TYPE)

RETURN NUMBER;

FUNCTION get_customer_name (

p_customer_num IN fnd_lookup_values.meaning%TYPE

, x_customer_name OUT VARCHAR2)

RETURN NUMBER;

FUNCTION get_customer_billto (

p_legacy_custno IN VARCHAR2

, p_legacy_custbillto IN VARCHAR2 DEFAULT NULL

, p_legacy_org_id IN NUMBER

, x_cust_num OUT VARCHAR2

, x_cust_billto OUT VARCHAR2)

RETURN NUMBER;

END xxak_cnv_common_pkg;

Body:

CREATE OR REPLACE PACKAGE BODY xxak_cnv_common_pkg

FUNCTION find_max (

p_error_code1 VARCHAR2

, p_error_code2 VARCHAR2)

RETURN VARCHAR2

x_return_value VARCHAR2 (100);

BEGIN

SELECT MAX (ERROR_CODE)

INTO x_return_value

FROM (SELECT p_error_code1 ERROR_CODE

FROM DUAL

UNION

SELECT p_error_code2 ERROR_CODE

FROM DUAL);

RETURN x_return_value;

END find_max;

/*********************************************************************************

-- This function will return the new org id based on the either input parameters

-- provided(Org_id or operating name)

**********************************************************************************/

FUNCTION get_new_operating_unit (

p_legacy_operating_unit_name IN VARCHAR2

, x_new_org_id OUT NUMBER)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

--recinfo c_operating_unit%ROWTYPE;

x_org_id hr_operating_units.organization_id%TYPE;

BEGIN

SELECT organization_id org_id

INTO x_new_org_id

FROM hr_operating_units

WHERE NAME = p_legacy_operating_unit_name;

RETURN x_error_code;

EXCEPTION

WHEN NO_DATA_FOUND THEN

fnd_file.put_line (fnd_file.LOG

, 'Operating Unit Name is not valid ' || p_legacy_operating_unit_name);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving the new operating unit');

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_new_operating_unit;

FUNCTION get_new_uom (

p_inventory_item_no IN mtl_system_items_b.segment1%TYPE DEFAULT NULL

, p_organization_id IN mtl_system_items_b.organization_id%TYPE DEFAULT NULL

, x_new_uom_code OUT VARCHAR2)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

--p_new_uom_code mtl_system_items_b.primary_uom_code%TYPE;

BEGIN

SELECT primary_uom_code

INTO x_new_uom_code

FROM mtl_system_items_b

WHERE segment1 = p_inventory_item_no

AND organization_id = p_organization_id;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Exception while deriving UOM of Item ' || SQLCODE || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_new_uom;

-- get inventory_item_id

FUNCTION get_inventory_item_id (

p_item_no IN VARCHAR2 DEFAULT NULL

, p_organization_id IN mtl_system_items_b.organization_id%TYPE DEFAULT NULL

, x_inventory_item_id OUT mtl_system_items_b.inventory_item_id%TYPE)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

--x_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;

BEGIN

SELECT inventory_item_id

INTO x_inventory_item_id

FROM mtl_system_items_b

WHERE segment1 = p_item_no

AND organization_id = p_organization_id;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving Inventory Item id ' || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_inventory_item_id;

FUNCTION get_vendor_id (

p_vendor_num IN VARCHAR2 DEFAULT NULL

, x_vendor_id OUT po_vendors.vendor_id%TYPE)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

--x_vendor_id po_vendors.vendor_id%TYPE;

BEGIN

SELECT vendor_id

INTO x_vendor_id

FROM AP_SUPPLIERS ----po_vendors

WHERE segment1 = p_vendor_num

AND enabled_flag = xxak_cnv_common_pkg.c_yes

AND (end_date_active IS NULL

OR end_date_active > SYSDATE);

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving vendor_id ' || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_vendor_id;

FUNCTION get_location_id (

p_location IN VARCHAR2 DEFAULT NULL

, x_location_id OUT NUMBER)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

--x_location_id hr_locations.location_id%TYPE;

BEGIN

SELECT location_id

INTO x_location_id

FROM hr_locations

WHERE location_code = p_location

AND NVL (inactive_date

, SYSDATE + 1) > SYSDATE;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving location_id ' || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_location_id;

FUNCTION get_vendor_id_frm_num (

p_vendor_num IN VARCHAR2 DEFAULT NULL

, p_vendor_name IN VARCHAR2 DEFAULT NULL

, x_vendor_id OUT NUMBER)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

x_vendr_id po_vendors.vendor_id%TYPE;

BEGIN

SELECT vendor_id

INTO x_vendor_id

FROM AP_SUPPLIERS ---po_vendors

WHERE segment1 = NVL (p_vendor_num

, segment1)

AND vendor_name = NVL (p_vendor_name

, vendor_name)

AND enabled_flag = xxak_cnv_common_pkg.c_yes

AND (end_date_active IS NULL

OR end_date_active > SYSDATE);

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving location_id ' || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_vendor_id_frm_num;

FUNCTION get_emp_id_frm_nt_id (

p_nt_id IN VARCHAR2 DEFAULT NULL

, p_orgn_code IN VARCHAR2

, x_person_id OUT NUMBER)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

--l_person_id per_all_people_f.person_id%TYPE;

x_business_group_id NUMBER;

BEGIN

SELECT business_group_id

INTO x_business_group_id

FROM hr_organization_units

WHERE organization_id = (SELECT organization_id

FROM mtl_parameters

WHERE organization_code = p_orgn_code);

SELECT person_id

INTO x_person_id

FROM per_all_people_f

WHERE employee_number = p_nt_id

AND business_group_id = x_business_group_id

AND TRUNC (NVL (effective_end_date

, SYSDATE) ) >= TRUNC (SYSDATE);

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving person_id ' || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_emp_id_frm_nt_id;

FUNCTION get_organization_code (

p_organization_id IN org_organization_definitions.organization_id%TYPE

, x_organization_code OUT org_organization_definitions.organization_code%TYPE)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

BEGIN

SELECT organization_code

INTO x_organization_code

FROM org_organization_definitions

WHERE organization_id = p_organization_id;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving organization_id ' || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_organization_code;

FUNCTION get_organization_id (

p_organization_code IN org_organization_definitions.organization_code%TYPE

, x_organization_id OUT org_organization_definitions.organization_id%TYPE)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

BEGIN

SELECT organization_id

INTO x_organization_id

FROM org_organization_definitions

WHERE organization_code = p_organization_code;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving organization_id ' || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_organization_id;

FUNCTION get_operatingunit_code (

p_operatingunit_code IN fnd_lookup_values.meaning%TYPE

, x_operatingunit_code OUT fnd_lookup_values.description%TYPE)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

x_ou_cunt NUMBER := 0;

BEGIN

SELECT flv.description

INTO x_operatingunit_code

FROM fnd_lookup_values flv

WHERE flv.lookup_type = 'XXLSS_OU_XREF'

AND flv.LANGUAGE = USERENV ('LANG')

AND flv.meaning = p_operatingunit_code;

SELECT COUNT (1)

INTO x_ou_cunt

FROM hr_operating_units hou

WHERE hou.NAME = x_operatingunit_code

AND NVL (TRUNC (date_to)

, SYSDATE) >= TRUNC (SYSDATE);

IF x_ou_cunt <> 0 THEN

x_error_code := xxak_cnv_common_pkg.c_success;

ELSE

x_error_code := xxak_cnv_common_pkg.c_rec_err;

x_operatingunit_code := NULL;

END IF;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving Operating Unit ' || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_operatingunit_code;

FUNCTION get_uom_code (

p_uom_code IN fnd_lookup_values.meaning%TYPE

, x_uom_code OUT fnd_lookup_values.description%TYPE)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

x_uom_cunt NUMBER := 0;

x_uom_value VARCHAR2 (10) := NULL;

BEGIN

SELECT COUNT (1)

INTO x_uom_cunt

FROM fnd_lookup_values flv

WHERE flv.lookup_type = 'XXLSS_UOM_XREFERENCE'

AND flv.LANGUAGE = USERENV ('LANG')

AND flv.meaning = p_uom_code;

IF x_uom_cunt <> 0 THEN

SELECT flv.description

INTO x_uom_value

FROM fnd_lookup_values flv

WHERE flv.lookup_type = 'XXLSS_UOM_XREFERENCE'

AND flv.LANGUAGE = USERENV ('LANG')

AND flv.meaning = p_uom_code;

ELSE

SELECT uom_code

INTO x_uom_value

FROM mtl_units_of_measure muo

WHERE muo.LANGUAGE = USERENV ('LANG')

AND muo.uom_code = p_uom_code

AND NVL (TRUNC (disable_date)

, SYSDATE) >= TRUNC (SYSDATE);

END IF;

IF x_uom_value IS NOT NULL THEN

x_error_code := xxak_cnv_common_pkg.c_success;

x_uom_code := x_uom_value;

ELSE

x_error_code := xxak_cnv_common_pkg.c_rec_err;

x_uom_code := NULL;

END IF;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving UOM ' || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_uom_code;

FUNCTION get_customer_num (

p_customer_num IN fnd_lookup_values.meaning%TYPE

, x_customer_num OUT fnd_lookup_values.description%TYPE)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

x_party_cunt NUMBER := 0;

x_cust_cunt NUMBER := 0;

BEGIN

SELECT COUNT (1)

INTO x_cust_cunt

FROM hz_cust_accounts_all

WHERE account_number = p_customer_num;

IF x_cust_cunt <> 0 THEN

x_error_code := xxak_cnv_common_pkg.c_success;

x_customer_num := p_customer_num;

ELSE

SELECT NVL (flv.description

, flv.meaning)

INTO x_customer_num

FROM fnd_lookup_values flv

WHERE flv.lookup_type = 'XXLSS_CUSTOMER_XREF'

AND flv.LANGUAGE = USERENV ('LANG')

AND flv.meaning = p_customer_num;

SELECT COUNT (1)

INTO x_party_cunt

FROM hz_cust_accounts_all

WHERE account_number = x_customer_num;

IF x_party_cunt <> 0 THEN

x_error_code := xxak_cnv_common_pkg.c_success;

ELSE

x_error_code := xxak_cnv_common_pkg.c_rec_err;

x_customer_num := NULL;

END IF;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving R12 Customer Number' || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_customer_num;

FUNCTION get_customer_name (

p_customer_num IN fnd_lookup_values.meaning%TYPE

, x_customer_name OUT VARCHAR2)

RETURN NUMBER

x_customer_num VARCHAR2 (2000) := NULL;

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

BEGIN

x_error_code := xxak_cnv_common_pkg.get_customer_num (p_customer_num

, x_customer_num);

IF x_error_code = xxak_cnv_common_pkg.c_success THEN

SELECT hp.party_name

INTO x_customer_name

FROM hz_parties hp

, hz_cust_accounts_all hca

WHERE hp.party_id = hca.party_id

AND hca.account_number = x_customer_num;

ELSE

x_customer_name := NULL;

x_error_code := xxak_cnv_common_pkg.c_rec_err;

END IF;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving R12 Customer Name ' || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_customer_name;

FUNCTION get_customer_billto (

p_legacy_custno IN VARCHAR2

, p_legacy_custbillto IN VARCHAR2 DEFAULT NULL

, p_legacy_org_id IN NUMBER

, x_cust_num OUT VARCHAR2

, x_cust_billto OUT VARCHAR2)

RETURN NUMBER

x_error_code NUMBER := xxak_cnv_common_pkg.c_success;

x_cust_number VARCHAR2 (200) := NULL;

BEGIN

x_error_code := xxak_cnv_common_pkg.get_customer_num (p_legacy_custno

, x_cust_number);

IF x_error_code = xxak_cnv_common_pkg.c_success THEN

x_cust_num := x_cust_number;

SELECT hcs.LOCATION

INTO x_cust_billto

FROM hz_cust_accounts_all hca

, hz_cust_acct_sites_all hcas

, hz_cust_site_uses_all hcs

, hr_organization_units hou

WHERE hca.cust_account_id = hcas.cust_account_id

AND hcas.cust_acct_site_id = hcs.cust_acct_site_id

AND hcas.org_id = hou.organization_id

AND NVL (hou.date_to

, SYSDATE + 1) >= SYSDATE

AND hcs.site_use_code = 'BILL_TO'

AND hcs.primary_flag = 'Y'

AND account_number = x_cust_number

AND hcs.org_id = p_legacy_org_id;

ELSE

x_error_code := xxak_cnv_common_pkg.c_rec_err;

x_cust_billto := NULL;

x_cust_num := NULL;

END IF;

RETURN x_error_code;

EXCEPTION

WHEN OTHERS THEN

fnd_file.put_line (fnd_file.LOG

, 'Error Occured while deriving R12 Bill to' || SQLERRM);

x_error_code := xxak_cnv_common_pkg.c_rec_err;

RETURN x_error_code;

END get_customer_billto;

END xxak_cnv_common_pkg;

Srikanth's Oracle Apps

Tuesday, March 25, 2014

Blanket Purchase Agreement Conversion

Blanket Purchase Agreement Conversion

Table....

Control File...

Lines:

Package:

Oracle Apps Technical Codes