CREATE OR REPLACE PACKAGE APPS.PO_IMPORT_PKG AS
PROCEDURE INSERT_ERROR_LOG(P_ORG_ID IN NUMBER,
-- P_RECORD_NUMBER IN NUMBER,
P_STAGING_TABLE IN VARCHAR2,
P_FIELD_NAME IN VARCHAR2,
P_FIELD_VALUE IN VARCHAR2,
P_ERROR_STATUS IN VARCHAR2,
P_ERROR_MESSAGE IN VARCHAR2);
PROCEDURE POPULATE_PO_INTERFACE(p_errbuf out NOCOPY VARCHAR2, p_return_code out NOCOPY VARCHAR2) ;
PROCEDURE ROLLBACK_PO(P_PO_NUM VARCHAR2);
PROCEDURE RESET_STATUS_ERRORS(p_errbuf out NOCOPY VARCHAR2, p_return_code out NOCOPY VARCHAR2);
END PO_IMPORT_PKG;
/
CREATE OR REPLACE PACKAGE BODY APPS.PO_IMPORT_PKG AS
PROCEDURE INSERT_ERROR_LOG(P_ORG_ID IN NUMBER,
-- P_RECORD_NUMBER IN NUMBER,
P_STAGING_TABLE IN VARCHAR2,
P_FIELD_NAME IN VARCHAR2,
P_FIELD_VALUE IN VARCHAR2,
P_ERROR_STATUS IN VARCHAR2,
P_ERROR_MESSAGE IN VARCHAR2) IS
BEGIN
INSERT INTO ZN_PO_INTERFACE_ERRORS(ORG_ID,
--RECORD_NUMBER,
STAGING_TABLE,
FIELD_NAME,
FIELD_VALUE,
STATUS,
ERROR,
CREATION_DATE)
VALUES (NVL(P_ORG_ID,-99), -- -99 MEANS THAT THE ORG_ID IS NOT VALID
--P_RECORD_NUMBER ,
P_STAGING_TABLE ,
P_FIELD_NAME ,
P_FIELD_VALUE ,
P_ERROR_STATUS ,
P_ERROR_MESSAGE ,
SYSDATE);
COMMIT;
END;
PROCEDURE POPULATE_PO_INTERFACE(p_errbuf out NOCOPY VARCHAR2, p_return_code out NOCOPY VARCHAR2) IS
--============================================================================
--Cursor C_POH for All NON Processed PO Headers
--============================================================================
CURSOR C_POH IS
SELECT *
FROM ZN_INV_GRN_HEADERS
WHERE PROCESS_STATUS IS NULL;
--============================================================================
--Cursor C_POL for All NON Processed PO Lines
--============================================================================
CURSOR C_POL(P_PO_NUM IN VARCHAR2) IS
SELECT *
FROM ZN_INV_GRN_LINES
WHERE PROCESS_STATUS IS NULL
AND PO_NUM = P_PO_NUM;
--============================================================================
--Cursor C_POD for All NON Processed PO Distributions
--============================================================================
CURSOR C_POD(P_PO_NUM IN VARCHAR2, P_PO_LINE_NUM IN NUMBER) IS
SELECT *
FROM ZN_INV_GRN_DIST PD
WHERE PROCESS_STATUS IS NULL
AND PO_NUM = P_PO_NUM
AND LINE_NUM = P_PO_LINE_NUM;
--============================================================================
--All other Variabled Declaration
--============================================================================
V_ORG_ID NUMBER;
V_INTERFACE_HEADER_ID NUMBER;
V_INTERFACE_LINE_ID NUMBER;
V_VENDOR_ID NUMBER;
V_VENDOR_SITE_ID NUMBER;
V_ERROR_HDR VARCHAR2(4000);
V_ERROR_LN VARCHAR2(4000);
V_ERROR_DIS VARCHAR2(4000);
V_AGENT_ID NUMBER;
V_TERM_ID NUMBER;
V_FREIGHT_TERMS VARCHAR2(50);
V_SHIP_TO_LOCATION_ID NUMBER;
V_SHIP_TO_LOCATION_CODE VARCHAR2(100);
V_BILL_TO_LOCATION_ID NUMBER;
V_BILL_TO_LOCATION_CODE VARCHAR2(100);
V_RATE_TYPE VARCHAR2(10);
V_CURRENCY_CODE VARCHAR2(10);
V_ITEM_ID NUMBER;
V_ITEM_DESCRIPTION VARCHAR(100);
V_CATEGORY_ID NUMBER;
V_LINE_TYPE_ID NUMBER;
V_UOM VARCHAR2(30);
V_TAX_CODE_ID NUMBER;
V_TAXABLE_FLAG VARCHAR(1);
V_PROCESS_STATUS_L VARCHAR2(1);
V_PROCESS_STATUS_D VARCHAR2(1);
V_INTERFACE_DISTRUBUTION_ID NUMBER;
V_CHARGE_ACCOUNT_ID NUMBER;
V_SHIP_TO_ORGANIZATION_CODE VARCHAR2(3);
V_DELIVER_TO_LOCATION_ID NUMBER;
V_DELIVER_TO_LOCATION_CODE VARCHAR2(100);
v_inv_org_id NUMBER;
V_BASE_CURRENCY VARCHAR2(10);
V_FUNCTIONAL_CURRENCY_FLAG VARCHAR2(1);
V_CONC_REQUEST_ID NUMBER;
V_LINE_SHIP_TO_LOCATION_CODE VARCHAR2(100);
V_LINE_SHIP_TO_LOCATION_ID NUMBER;
x_debug_point number;
x_item_err_details VARCHAR2(3000);
l_ccid Number;
x_code_combination VARCHAR2(181 byte);
l_error_msg1 varchar2(300);
l_error_msg2 varchar2(300);
x_count number :=0;
X_BATCH_ID NUMBER :=1000;
BEGIN
----============================BEGIN==========================================
--The concurrent Request ID is the Batch Id for importing the PO sets of date
SELECT FND_GLOBAL.CONC_REQUEST_ID INTO V_CONC_REQUEST_ID FROM DUAL;
/* OPEN CURSOR C_POH */
FOR I_POH IN C_POH LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Validation for PO Number '||I_POH.PO_NUM ||' Begins Here');
FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------');
--reset all variables
V_INTERFACE_HEADER_ID :=NULL;
V_VENDOR_ID :=NULL;
V_VENDOR_SITE_ID :=NULL;
V_AGENT_ID :=NULL;
V_TERM_ID :=NULL;
V_SHIP_TO_LOCATION_ID :=NULL;
V_BILL_TO_LOCATION_ID :=NULL;
V_SHIP_TO_LOCATION_CODE :=NULL;
V_BILL_TO_LOCATION_CODE :=NULL;
V_ERROR_HDR:=NULL;
V_INV_ORG_ID :=NULL;
V_BASE_CURRENCY :=NULL;
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P' --PENDING
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM);
COMMIT;
x_debug_point:=1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before OPERATING UNIT validation '|| ' at debug point : ' ||x_debug_point);
--=====================================================================
--Validate the OPERATING UNIT
--=====================================================================
BEGIN
/* SELECT DISTINCT H.ORGANIZATION_ID
INTO V_ORG_ID
FROM HR_ALL_ORGANIZATION_UNITS H
where UPPER(NAME) = UPPER(I_POH.ORG_NAME)
AND TYPE IS NULL;*/
SELECT DISTINCT H.ORGANIZATION_ID
INTO V_ORG_ID
FROM HR_OPERATING_UNITS H
where UPPER(NAME) = UPPER(I_POH.ORG_NAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_ERROR_HDR:='PO NUMBER: ' || I_POH.PO_NUM ||'- Error in Fetching ORG_ID FROM ORG_NAME ' || SQLERRM;
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POH.batch_name || ' ORG_ID is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS='E'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM)
AND ERROR_MESSAGE=V_ERROR_HDR;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'PO Number' ||I_POH.PO_NUM|| ' Will be Rollbacked');
p_return_code:=1;
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_HDR);
END;
x_debug_point:=2;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before VENDOR NAME validation '|| ' at debug point : ' ||x_debug_point);
--=====================================================================
--Validate the VENDOR NAME
--=====================================================================
BEGIN
SELECT VENDOR_ID
INTO V_VENDOR_ID
FROM PO_VENDORS
WHERE UPPER(VENDOR_NAME) = UPPER(I_POH.VENDOR_NAME);
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_ERROR_HDR:=V_ERROR_HDR||'PO NUMBER: ' || I_POH.PO_NUM ||'- Error in Fetching Vendor_id ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_HDR);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POH.batch_name || ' Vendor_id is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS='E'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM)
AND ERROR_MESSAGE=V_ERROR_HDR;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'PO Number' ||I_POH.PO_NUM|| ' Will be Rollbacked');
p_return_code:=2;
--fnd_file.put_line('fnd_file.log',V_ERROR_HDR);
END;
x_debug_point:=3;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before VENDOR SITE validation '|| ' at debug point : ' ||x_debug_point);
--=====================================================================
--Validate the VENDOR SITE
--=====================================================================
BEGIN
SELECT VENDOR_SITE_ID
INTO V_VENDOR_SITE_ID
FROM PO_VENDOR_SITES_ALL
WHERE UPPER(VENDOR_SITE_CODE)=UPPER(I_POH.VENDOR_SITE_CODE)
AND VENDOR_ID = V_VENDOR_ID
AND ORG_ID = V_ORG_ID;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
V_ERROR_HDR:=V_ERROR_HDR||' PO NUMBER: ' || I_POH.PO_NUM ||'- Error in Fetching Vendor_site_id ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_HDR);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POH.batch_name || ' Vendor_site_id is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS='E'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM)
AND ERROR_MESSAGE=V_ERROR_HDR;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'PO Number' ||I_POH.PO_NUM|| ' Will be Rollbacked');
p_return_code:=3;
--fnd_file.put_line('fnd_file.log',V_ERROR_HDR);
END;
x_debug_point:=4;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Buyer validation '|| ' at debug point : ' ||x_debug_point);
--=====================================================================
--Validate the Buyer
--=====================================================================
BEGIN
SELECT PERSON_ID
INTO V_AGENT_ID
FROM PER_ALL_PEOPLE_F
WHERE UPPER(FULL_NAME) LIKE UPPER(I_POH.AGENT_NAME)
AND SYSDATE BETWEEN effective_start_date AND nvl(effective_end_date,SYSDATE);
EXCEPTION
WHEN OTHERS THEN
V_ERROR_HDR:=V_ERROR_HDR||'PO NUMBER: ' || I_POH.PO_NUM ||'- Error in Fetching Agent ID: ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_HDR);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POH.batch_name || 'Agent ID is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS='E'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM)
AND ERROR_MESSAGE=V_ERROR_HDR;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'PO Number' ||I_POH.PO_NUM|| ' Will be Rollbacked');
p_return_code:=4;
--fnd_file.put_line('fnd_file.log',V_ERROR_HDR);
END;
x_debug_point:=5;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Payment Terms validation '|| ' at debug point : ' ||x_debug_point);
--==========================================================================
--Validate the Payment Terms
--==========================================================================
BEGIN
SELECT TERM_ID
INTO V_TERM_ID
FROM AP_TERMS
WHERE UPPER(NAME) = UPPER(I_POH.PAYMENT_TERMS);
EXCEPTION
WHEN OTHERS THEN
V_ERROR_HDR := 'PO NUMBER: ' || I_POH.PO_NUM ||
'- Error in Fetching Term ID: ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_HDR);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POH.batch_name || ' Term ID is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'PO Number' ||I_POH.PO_NUM|| ' Will be Rollbacked');
p_return_code:=5;
--fnd_file.put_line('fnd_file.log',V_ERROR_HDR);
END;
x_debug_point:=5.5;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Freight Terms validation '|| ' at debug point : ' ||x_debug_point);
--==========================================================================
--Validate the Freight Terms
--==========================================================================
BEGIN
select lookup_code
into V_FREIGHT_TERMS
from po_lookup_codes
where UPPER(displayed_field) = UPPER(I_POH.FREIGHT_TERMS);
EXCEPTION
WHEN OTHERS THEN
V_ERROR_HDR := 'PO NUMBER: ' || I_POH.PO_NUM ||
'- Error in Fetching Freight Terms: ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_HDR);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POH.batch_name || ' Freight Terms is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'PO Number' ||I_POH.PO_NUM|| ' Will be Rollbacked');
p_return_code:=5;
--fnd_file.put_line('fnd_file.log',V_ERROR_HDR);
END;
x_debug_point:=6;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before SHIP TO LOCATION validation '|| ' at debug point : ' ||x_debug_point);
--==========================================================================
--Validate the SHIP TO LOCATION
--==========================================================================
BEGIN
SELECT LOCATION_ID,LOCATION_CODE
INTO V_SHIP_TO_LOCATION_ID,V_SHIP_TO_LOCATION_CODE
FROM HR_LOCATIONS_ALL
WHERE UPPER(replace(LOCATION_CODE,' ','')) = UPPER(replace(I_POH.SHIP_TO_LOCATION,' ',''));
EXCEPTION
WHEN OTHERS THEN
V_ERROR_HDR := 'PO NUMBER: ' || I_POH.PO_NUM ||
'- Error in fetching Ship to Location ID: ' ||
SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_HDR);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POH.batch_name || ' Ship to Location ID is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'PO Number' ||I_POH.PO_NUM|| ' Will be Rollbacked');
p_return_code:=6;
END;
x_debug_point:=7;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before BILL TO LOCATION validation '|| ' at debug point : ' ||x_debug_point);
--==========================================================================
--Validate the BILL TO LOCATION
--==========================================================================
BEGIN
SELECT LOCATION_ID,LOCATION_CODE
INTO V_BILL_TO_LOCATION_ID,V_BILL_TO_LOCATION_CODE
FROM HR_LOCATIONS_ALL
WHERE trim(UPPER(replace(LOCATION_CODE,' ',''))) = UPPER('Airtel Head Office');
EXCEPTION
WHEN OTHERS THEN
V_ERROR_HDR := 'PO NUMBER: ' || I_POH.PO_NUM ||
'- Error in fetching Bill to Location ID: ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_HDR);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POH.batch_name || ' Bill to Location ID is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'PO Number' ||I_POH.PO_NUM|| ' Will be Rollbacked');
p_return_code:=7;
--fnd_file.put_line('fnd_file.log',V_ERROR_HDR);
END;
x_debug_point:=8;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Rate type validation '|| ' at debug point : ' ||x_debug_point);
--==========================================================================
--Validate the Rate type
--==========================================================================
IF I_POH.RATE_TYPE IS NOT NULL
THEN
BEGIN
SELECT USER_CONVERSION_TYPE
INTO V_RATE_TYPE
FROM GL_DAILY_CONVERSION_TYPES
WHERE UPPER(CONVERSION_TYPE)=UPPER(I_POH.RATE_TYPE);
EXCEPTION
WHEN OTHERS THEN
V_ERROR_HDR := 'PO NUMBER: ' || I_POH.PO_NUM ||
'- Error in fetching Rate Type: ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_HDR);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POH.batch_name || ' Rate Type is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'PO Number' ||I_POH.PO_NUM|| ' Will be Rollbacked');
p_return_code:=8;
--fnd_file.put_line('fnd_file.log',V_ERROR_HDR);
END;
END IF;
x_debug_point:=9;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Currency validation '|| ' at debug point : ' ||x_debug_point);
--==========================================================================
--Validate the Currency
--==========================================================================
BEGIN
SELECT CURRENCY_CODE
INTO V_CURRENCY_CODE
FROM gl_currencies v
WHERE UPPER(V.CURRENCY_CODE)=UPPER(I_POH.CURRENCY_CODE);
EXCEPTION
WHEN OTHERS THEN
V_ERROR_HDR := V_ERROR_HDR||'PO NUMBER: ' || I_POH.PO_NUM ||
'- Error in fetching Currency: ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_HDR);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POH.batch_name || ' Currency is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'PO Number' ||I_POH.PO_NUM|| ' Will be Rollbacked');
p_return_code:=9;
--fnd_file.put_line('fnd_file.log',V_ERROR_HDR);
END;
x_debug_point:=10;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before FUNCTIONAL CURRENCY validation '|| ' at debug point : ' ||x_debug_point);
--==========================================================================
---- CHECK IF FUNCTIONAL CURRENCY IS PROVIDED
--==========================================================================
BEGIN
SELECT CURRENCY_CODE
INTO V_BASE_CURRENCY
FROM GL_SETS_OF_BOOKS D
WHERE SET_OF_BOOKS_ID =
(SELECT F.SET_OF_BOOKS_ID
FROM FINANCIALS_SYSTEM_PARAMS_ALL F
WHERE F.ORG_ID = V_ORG_ID);
IF V_BASE_CURRENCY <> V_CURRENCY_CODE
THEN V_FUNCTIONAL_CURRENCY_FLAG:='Y';
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error Getting the functional currency');
END;
--=====================================================================
--Populate the PO_Headers Interface for validated Records
--=====================================================================
BEGIN
IF NVL(I_POH.PROCESS_STATUS,'?') <> 'E'
THEN
SELECT PO_HEADERS_INTERFACE_S.NEXTVAL
INTO V_INTERFACE_HEADER_ID
FROM DUAL;
x_debug_point:=11;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before inserting PO_HEADERS_INTERFACE '|| ' at debug point : ' ||x_debug_point||'PO Number');
/* Batch ID Validation */
x_count := x_count+1;
IF x_count=500
then
x_batch_id :=x_batch_id+1;
x_count :=0;
END IF;
INSERT INTO PO_HEADERS_INTERFACE
(INTERFACE_HEADER_ID,
PROCESS_CODE,
ACTION,
ORG_ID,
DOCUMENT_TYPE_CODE,
DOCUMENT_NUM,
CURRENCY_CODE,
AGENT_ID,
VENDOR_ID,
VENDOR_SITE_ID,
--SHIP_TO_LOCATION_ID,
SHIP_TO_LOCATION,
-- BILL_TO_LOCATION_ID,
BILL_TO_LOCATION,
TERMS_ID,
FREIGHT_TERMS,
APPROVAL_STATUS,
MIN_RELEASE_AMOUNT,
FIRM_FLAG,
FROZEN_FLAG,
CLOSED_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
COMMENTS,
ACCEPTANCE_REQUIRED_FLAG,
RATE_TYPE,
RATE,
RATE_DATE,
VENDOR_DOC_NUM,
BATCH_ID,
ATTRIBUTE15)
VALUES
(V_INTERFACE_HEADER_ID,
'PENDING',
'ORIGINAL',
V_ORG_ID,
'STANDARD',
I_POH.PO_NUM,
I_POH.CURRENCY_CODE,
V_AGENT_ID,
V_VENDOR_ID,
V_VENDOR_SITE_ID,
V_SHIP_TO_LOCATION_CODE,
V_BILL_TO_LOCATION_CODE,
V_TERM_ID,
V_FREIGHT_TERMS,
'APPROVED',
1,
'N',
'N',
'OPEN',
I_POH.CREATION_DATE,
1110,
(SYSDATE),
1110,
I_POH.COMMENTS,
I_POH.ACCEPTANCE_REQUIRED_FLAG,
DECODE(V_FUNCTIONAL_CURRENCY_FLAG,'Y',I_POH.RATE_TYPE,NULL),
TO_NUMBER(DECODE(V_FUNCTIONAL_CURRENCY_FLAG,'Y',TO_CHAR(I_POH.RATE),NULL)),
TO_DATE(DECODE(V_FUNCTIONAL_CURRENCY_FLAG,'Y',TO_CHAR(I_POH.RATE_DATE),NULL)),
I_POH.PO_NUM,
x_batch_id,
i_poh.batch_name
);
COMMIT;
x_debug_point:=12;
FND_FILE.PUT_LINE(FND_FILE.LOG,'After inserting PO_HEADERS_INTERFACE '|| ' at debug point : ' ||x_debug_point);
--====================================================
--Record inserted successfully
--====================================================
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'S'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM);
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Header for PO: '||I_POH.PO_NUM ||' Inserted Successfully');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Header has errors during validation');
END IF;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in Header Data Insertion');
END;
--=====================================================================
--All validations are completed for this record
--=====================================================================
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'C' --COMPLETED
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM);
COMMIT;
/* OPEN CURSOR C_POL */
FOR I_POL IN C_POL(I_POH.PO_NUM) LOOP
--reset all variables while looping
V_RATE_TYPE :=NULL;
V_CURRENCY_CODE :=NULL;
V_ITEM_ID :=NULL;
V_ITEM_DESCRIPTION :=NULL;
V_CATEGORY_ID :=NULL;
V_LINE_TYPE_ID :=NULL;
V_UOM :=NULL;
V_TAX_CODE_ID :=NULL;
V_TAXABLE_FLAG:=NULL;
--V_PROCESS_STATUS_L:=NULL;
V_SHIP_TO_ORGANIZATION_CODE :=NULL;
V_LINE_SHIP_TO_LOCATION_CODE :=NULL;
V_LINE_SHIP_TO_LOCATION_ID :=NULL;
V_ERROR_LN :=NULL;
x_debug_point:=13;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before SHIP TO LOCATION validation '|| ' at debug point : ' ||x_debug_point);
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------VALIDATION STARTS FOR PO_LINES---------------------');
--=========================================================================================
--VALIDATION STARTS FOR PO_LINES
--==========================================================================================
--==========================================================================
--Validate the SHIP TO LOCATION
--==========================================================================
BEGIN
SELECT LOCATION_ID,LOCATION_CODE
INTO V_LINE_SHIP_TO_LOCATION_ID,V_LINE_SHIP_TO_LOCATION_CODE
FROM HR_LOCATIONS_ALL
WHERE UPPER(replace(LOCATION_CODE,' ','')) = UPPER(replace(I_POL.SHIP_TO_LOCATION,' ',''));
EXCEPTION
WHEN OTHERS THEN
V_ERROR_LN := 'PO NUMBER: ' || I_POL.PO_NUM ||
'- Error in fetching Ship to Location ID: ' ||
SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_LN);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POL.batch_name || ' Ship to Location ID is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POL.PO_NUM);
COMMIT;
p_return_code:=20;
END;
x_debug_point:=14;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before SHIP TO ORGANIZATION CODE validation '|| ' at debug point : ' ||x_debug_point);
--=====================================================================
--Validate the SHIP TO ORGANIZATION CODE
--=====================================================================
BEGIN
IF I_POL.SHIP_TO_ORGANIZATION_CODE IS NOT NULL THEN
SELECT DISTINCT OOD.ORGANIZATION_CODE,ood.ORGANIZATION_ID
INTO V_SHIP_TO_ORGANIZATION_CODE,v_inv_org_id
FROM MTL_SYSTEM_ITEMS_KFV MSI,
ORG_ORGANIZATION_DEFINITIONS OOD,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE OOD.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID AND
OOD.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND
-- MSI.INVENTORY_ITEM_ID = V_ITEM_ID AND
MSI.PURCHASING_ENABLED_FLAG = 'Y' AND
SYSDATE < NVL(OOD.DISABLE_DATE,SYSDATE + 1) AND
NVL(MSI.OUTSIDE_OPERATION_FLAG,'N') = 'N' AND
UPPER(OOD.ORGANIZATION_CODE) = UPPER(I_POL.SHIP_TO_ORGANIZATION_CODE);
END IF;
EXCEPTION
WHEN OTHERS THEN
V_ERROR_LN := 'PO NUMBER: ' || I_POL.PO_NUM ||
'- Error in fetching SHIP_TO_ORGANIZATION_CODE: ' || SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_LN);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Item cannot be shipped to this Organization...');
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POL.batch_name || ' SHIP_TO_ORGANIZATION_CODE is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_LINES
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POL.PO_NUM)
AND LINE_NUM=I_POL.LINE_NUM
AND (SHIPMENT_NUM=I_POL.SHIPMENT_NUM);-- OR I_POL.SHIPMENT_NUM IS NULL) ;
ROLLBACK_PO(I_POH.PO_NUM);
COMMIT;
p_return_code:=10;
--fnd_file.put_line('fnd_file.log',V_ERROR_LN);
END;
x_debug_point:=15;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before INVENTORY ITEM ID validation '|| ' at debug point : ' ||x_debug_point);
--==========================================================================
--Validate the INVENTORY ITEM ID
--==========================================================================
-- ITEM_CODE IS PROVIDED , SO GETTING ITEM DESCRIPTION AND CATEGORY AS WELL
IF I_POL.ITEM IS NOT NULL THEN
BEGIN
SELECT DISTINCT INVENTORY_ITEM_ID
INTO V_ITEM_ID
FROM MTL_SYSTEM_ITEMS
WHERE UPPER(SEGMENT1) = UPPER(I_POL.ITEM);
-- AND ORGANIZATION_ID =v_inv_org_id;
EXCEPTION
WHEN OTHERS THEN
V_ERROR_LN := 'PO NUMBER: ' || I_POL.PO_NUM ||
'- Error in fetching Item ID: ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_LN);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POL.batch_name || ' Item ID is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_LINES
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POL.PO_NUM) AND
LINE_NUM = I_POL.LINE_NUM
AND (SHIPMENT_NUM=I_POL.SHIPMENT_NUM);-- OR I_POL.SHIPMENT_NUM IS NULL) ;
ROLLBACK_PO(I_POH.PO_NUM);
COMMIT;
p_return_code:=11;
--fnd_file.put_line('fnd_file.log',V_ERROR_LN);
x_debug_point:=16;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before ITEM Category population '|| ' at debug point : ' ||x_debug_point);
--Populate the ITEM Category
BEGIN
SELECT CATEGORY_ID
INTO V_CATEGORY_ID
FROM MTL_ITEM_CATEGORIES
WHERE INVENTORY_ITEM_ID = V_ITEM_ID AND
ORGANIZATION_ID = v_inv_org_id AND
CATEGORY_SET_ID =
(SELECT CATEGORY_SET_ID
FROM MTL_CATEGORY_SETS_TL
WHERE UPPER(CATEGORY_SET_NAME) = 'INVENTORY');
EXCEPTION
WHEN OTHERS THEN
V_ERROR_LN := 'PO NUMBER: ' || I_POL.PO_NUM ||
'- Error in fetching Category ID from item code: ' ||
SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_LN);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POL.batch_name || ' Category ID is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_LINES
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POL.PO_NUM) AND
LINE_NUM = I_POL.LINE_NUM
AND (SHIPMENT_NUM=I_POL.SHIPMENT_NUM);-- OR I_POL.SHIPMENT_NUM IS NULL) ;
ROLLBACK_PO(I_POH.PO_NUM);
COMMIT;
p_return_code:=12;
--fnd_file.put_line('fnd_file.log',V_ERROR_LN);
END;
END;
x_debug_point:=17;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before ITEM Category validation '|| ' at debug point : ' ||x_debug_point);
--==========================================================================
--Validate the ITEM Category
--==========================================================================
ELSE -- IF ITEM CODE IS NOT PROVIDED
BEGIN
SELECT DISTINCT MTLC.CATEGORY_ID,
MTLC.INVENTORY_ITEM_ID,
SI.SEGMENT1
INTO V_CATEGORY_ID,V_ITEM_ID,V_ITEM_DESCRIPTION
FROM MTL_ITEM_CATEGORIES MTLC,MTL_SYSTEM_ITEMS_B SI
WHERE MTLC.INVENTORY_ITEM_ID = SI.INVENTORY_ITEM_ID AND
MTLC.CATEGORY_ID IN
(SELECT MC.CATEGORY_ID
FROM MTL_CATEGORIES MC
WHERE UPPER(TRIM(MC.SEGMENT1) || '.' ||
TRIM(MC.SEGMENT2) || '.' ||
TRIM(MC.SEGMENT3) || '.' ||
TRIM(MC.SEGMENT4)) LIKE
UPPER(I_POL.CATEGORY)) AND
CATEGORY_SET_ID =
(SELECT T.CATEGORY_SET_ID
FROM MTL_CATEGORY_SETS_TL T
WHERE UPPER(T.CATEGORY_SET_NAME) = 'INVENTORY');
EXCEPTION
WHEN OTHERS THEN
V_ERROR_LN := 'PO NUMBER: ' || I_POL.PO_NUM ||
'- Error in fetching Category ID and item id: ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_LN);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POL.batch_name || ' Category ID is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_LINES
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POL.PO_NUM) AND
LINE_NUM = I_POL.LINE_NUM
AND (SHIPMENT_NUM=I_POL.SHIPMENT_NUM);-- OR I_POL.SHIPMENT_NUM IS NULL) ;
ROLLBACK_PO(I_POH.PO_NUM);
COMMIT;
p_return_code:=13;
--fnd_file.put_line('fnd_file.log',V_ERROR_LN);
END;
END IF; --- IF ITEM CODE IS NOT PROVIDED
x_debug_point:=18;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before LINE TYPE validation '|| ' at debug point : ' ||x_debug_point);
--==========================================================================
--Validate the LINE TYPE
--==========================================================================
BEGIN
SELECT LINE_TYPE_ID
INTO V_LINE_TYPE_ID
FROM PO_LINE_TYPES
WHERE UPPER(LINE_TYPE) = UPPER(I_POL.LINE_TYPE);
EXCEPTION
WHEN OTHERS THEN
V_ERROR_LN := 'PO NUMBER: ' || I_POL.PO_NUM ||
'- Error in fetching line Type: ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_LN);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POL.batch_name || ' line Type is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_LINES
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POL.PO_NUM)
AND LINE_NUM=I_POL.LINE_NUM
AND (SHIPMENT_NUM=I_POL.SHIPMENT_NUM);-- OR I_POL.SHIPMENT_NUM IS NULL) ;
ROLLBACK_PO(I_POH.PO_NUM);
COMMIT;
p_return_code:=14;
--fnd_file.put_line('fnd_file.log',V_ERROR_LN);
END;
x_debug_point:=19;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Unit of Measure validation '|| ' at debug point : ' ||x_debug_point);
--==========================================================================
--Validate the Unit of Measure
--==========================================================================
BEGIN
SELECT UOM_CODE
INTO V_UOM
FROM MTL_UNITS_OF_MEASURE
WHERE UPPER(UNIT_OF_MEASURE)= UPPER(I_POL.UNIT_OF_MEASURE);
EXCEPTION
WHEN OTHERS THEN
V_ERROR_LN := 'PO NUMBER: ' || I_POL.PO_NUM ||
'- Error in : ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_LN);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POL.batch_name || ' UOM Code is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_LINES
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POL.PO_NUM)
AND LINE_NUM=I_POL.LINE_NUM
AND (SHIPMENT_NUM=I_POL.SHIPMENT_NUM);-- OR I_POL.SHIPMENT_NUM IS NULL) ;
ROLLBACK_PO(I_POH.PO_NUM);
COMMIT;
p_return_code:=15;
--fnd_file.put_line('fnd_file.log',V_ERROR_LN);
END;
x_debug_point:=20;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before TAX code validation '|| ' at debug point : ' ||x_debug_point);
--==========================================================================
--Validate the TAX code
--==========================================================================
--Waiting ON Oracle Support SR.7202352.992 once the validation IS provided the next section should be un-commented.
BEGIN
IF I_POL.TAX_NAME IS NOT NULL THEN -- tax is not mandatory
V_TAXABLE_FLAG:='Y';
ELSE V_TAXABLE_FLAG:='N';----***
END IF;
END;
/* BEGIN
IF I_POL.TAX_NAME IS NOT NULL THEN -- tax is not mandatory
V_TAXABLE_FLAG:='Y';
SELECT TAX_ID
INTO V_TAX_CODE_ID
FROM AP_TAX_CODES_ALL
WHERE UPPER(NAME) LIKE UPPER(I_POL.TAX_NAME);
ELSE V_TAXABLE_FLAG:='Y';----***
END IF;
EXCEPTION
WHEN OTHERS THEN
V_ERROR_LN := 'PO NUMBER: ' || I_POL.PO_NUM ||
'- Error in fetching tax code: ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_LN);
INSERT_ERROR_LOG(V_ORG_ID,
x_debug_point,
'ZN_INV_GRN_LINES',
'TAX_NAME',
I_POL.TAX_NAME,
'E',
V_ERROR_LN);
UPDATE ZN_INV_GRN_LINES
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POL.PO_NUM)
AND LINE_NUM=I_POL.LINE_NUM
AND (SHIPMENT_NUM=I_POL.SHIPMENT_NUM);-- OR I_POL.SHIPMENT_NUM IS NULL) ;
ROLLBACK_PO(I_POH.PO_NUM);
COMMIT;
p_return_code:=16;
--fnd_file.put_line('fnd_file.log',V_ERROR_LN);
END;*/
x_debug_point:=21;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before SHIP TP ORGANIZATION CODE validation '|| ' at debug point : ' ||x_debug_point);
/* --=====================================================================
--Validate the SHIP TP ORGANIZATION CODE
--=====================================================================
BEGIN
IF I_POL.SHIP_TO_ORGANIZATION_CODE IS NOT NULL THEN
SELECT DISTINCT OOD.ORGANIZATION_CODE,ood.ORGANIZATION_ID
INTO V_SHIP_TO_ORGANIZATION_CODE,v_inv_org_id
FROM MTL_SYSTEM_ITEMS_KFV MSI,
ORG_ORGANIZATION_DEFINITIONS OOD,
FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE OOD.SET_OF_BOOKS_ID = FSP.SET_OF_BOOKS_ID AND
OOD.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND
MSI.INVENTORY_ITEM_ID = V_ITEM_ID AND
MSI.PURCHASING_ENABLED_FLAG = 'Y' AND
SYSDATE < NVL(OOD.DISABLE_DATE,SYSDATE + 1) AND
NVL(MSI.OUTSIDE_OPERATION_FLAG,'N') = 'N' AND
UPPER(OOD.ORGANIZATION_CODE) = UPPER(I_POL.SHIP_TO_ORGANIZATION_CODE);
END IF;
EXCEPTION
WHEN OTHERS THEN
V_ERROR_LN := 'PO NUMBER: ' || I_POL.PO_NUM ||
'- Error in fetching SHIP_TO_ORGANIZATION_CODE: ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_LN);
INSERT_ERROR_LOG(V_ORG_ID,
x_debug_point,
'ZN_INV_GRN_LINES',
'SHIP_TO_ORGANIZATION_CODE',
I_POL.SHIP_TO_ORGANIZATION_CODE,
'E',
V_ERROR_LN);
UPDATE ZN_INV_GRN_LINES
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POL.PO_NUM)
AND LINE_NUM=I_POL.LINE_NUM
AND (SHIPMENT_NUM=I_POL.SHIPMENT_NUM);-- OR I_POL.SHIPMENT_NUM IS NULL) ;
ROLLBACK_PO(I_POH.PO_NUM);
COMMIT;
p_return_code:=17;
--fnd_file.put_line('fnd_file.log',V_ERROR_LN);
END;
*/
x_debug_point:=22;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Populate the PO_Lines Interface '|| ' at debug point : ' ||x_debug_point);
--=====================================================================
--Populate the PO_Lines Interface for validated Records
--=====================================================================
BEGIN
SELECT PROCESS_STATUS
INTO V_PROCESS_STATUS_L
FROM ZN_INV_GRN_LINES
WHERE UPPER(PO_NUM) = UPPER(I_POL.PO_NUM)
AND LINE_NUM=I_POL.LINE_NUM
AND (SHIPMENT_NUM=I_POL.SHIPMENT_NUM);-- OR I_POL.SHIPMENT_NUM IS NULL) ;
IF NVL(V_PROCESS_STATUS_L,'A') <> 'E'
THEN
SELECT PO_LINES_INTERFACE_S.NEXTVAL
INTO V_INTERFACE_LINE_ID
FROM DUAL;
INSERT INTO PO_LINES_INTERFACE(
INTERFACE_LINE_ID,
INTERFACE_HEADER_ID,
LINE_NUM,
SHIPMENT_NUM,
LINE_TYPE_ID,
ITEM_ID,
-- ITEM_DESCRIPTION,
CATEGORY_ID,
UNIT_OF_MEASURE,
UNIT_PRICE,
QUANTITY,
-- AMOUNT,
INSPECTION_REQUIRED_FLAG,
RECEIPT_REQUIRED_FLAG,
MIN_RELEASE_AMOUNT,
CLOSED_CODE,
QTY_RCV_TOLERANCE,
OVER_TOLERANCE_ERROR_FLAG,
ORGANIZATION_ID,
NEED_BY_DATE,
PROMISED_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SHIP_TO_ORGANIZATION_CODE,
SHIP_TO_LOCATION,
SHIP_TO_ORGANIZATION_id,
SHIPMENT_TYPE,
TAXABLE_FLAG,
-- DOCUMENT_NUM,
--TAX_CODE_ID,
TAX_NAME,
LINE_ATTRIBUTE2,
LINE_ATTRIBUTE7,
ACCRUE_ON_RECEIPT_FLAG)
VALUES
(V_INTERFACE_LINE_ID,
V_INTERFACE_HEADER_ID,
I_POL.LINE_NUM,
I_POL.SHIPMENT_NUM,
V_LINE_TYPE_ID,
V_ITEM_ID,
-- I_POL.ITEM_DESCRIPTION,
I_POL.CATEGORY_ID,
I_POL.UNIT_OF_MEASURE,
I_POL.UNIT_PRICE,
I_POL.QUANTITY,
-- I_POL.AMOUNT,
'N',--INSPECTION_REQUIRED_FLAG,
NVL(I_POL.RECEIPT_REQUIRED_FLAG,'N'),--RECEIPT_REQUIRED_FLAG
1,--MIN_RELEASE_AMOUNT
'OPEN',--CLOSED_CODE
0,--QTY_RCV_TOLERANCE
'NONE',--OVER_TOLERANCE_ERROR_FLAG
v_org_id,--I_POL.ORGANIZATION_ID
I_POL.NEED_BY_DATE,
I_POL.PROMISED_DATE,
SYSDATE,
1110,
I_POH.CREATION_DATE,
1110,
V_SHIP_TO_ORGANIZATION_CODE,
V_LINE_SHIP_TO_LOCATION_CODE,
v_inv_org_id,
'STANDARD',
V_TAXABLE_FLAG,
-- I_POH.PO_NUM,
-- V_TAX_CODE_ID
I_POL.TAX_NAME,
I_POL.ATTRIBUTE2,
I_POL.ATTRIBUTE7,
I_POL.ACCRUE_ON_RECEIPT_FLAG);
COMMIT;
--====================================================
--Record inserted successfully
--====================================================
UPDATE ZN_INV_GRN_LINES
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'S'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM)
AND LINE_NUM=I_POL.LINE_NUM
AND (SHIPMENT_NUM=I_POL.SHIPMENT_NUM);-- OR I_POL.SHIPMENT_NUM IS NULL) ;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Line '||I_POL.LINE_NUM ||' For PO: '||I_POH.PO_NUM ||' Shipment_num '||I_POL.shipment_num||' Inserted Successfully');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Lines have errors during validation');
END IF;
EXCEPTION
WHEN OTHERS
THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error is'||Sqlerrm);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in Lines Data Insertion');
p_return_code:=18;
END;
--=====================================================================
--All validations are completed for this record
--=====================================================================
UPDATE ZN_INV_GRN_LINES
SET PROCESS_FLAG = 'C' --COMPLETED
WHERE UPPER(PO_NUM) = UPPER(I_POL.PO_NUM)
AND LINE_NUM=I_POL.LINE_NUM
AND (SHIPMENT_NUM=I_POL.SHIPMENT_NUM); --OR I_POL.SHIPMENT_NUM IS NULL) ;
COMMIT;
FOR I_POD IN C_POD(I_POH.PO_NUM,I_POL.LINE_NUM) LOOP
V_CHARGE_ACCOUNT_ID:=NULL;
V_DELIVER_TO_LOCATION_ID:=NULL;
V_PROCESS_STATUS_D:=NULL;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'-----------------VALIDATION STARTS FOR PO Distribution---------------------');
--=========================================================================================
--VALIDATION STARTS FOR PO DISTRIBUTION
--==========================================================================================
--=====================================================================
--Validate the deliver to location
--=====================================================================
x_debug_point:=23;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before deliver to location validation '|| ' at debug point : ' ||x_debug_point);
BEGIN
SELECT LOCATION_ID,LOCATION_CODE
INTO V_DELIVER_TO_LOCATION_ID,V_DELIVER_TO_LOCATION_CODE
FROM HR_LOCATIONS_ALL
WHERE UPPER(replace(LOCATION_CODE,' ','')) = UPPER(replace(I_POD.DELIVER_TO_LOCATION,' ',''));
EXCEPTION
WHEN OTHERS THEN
V_ERROR_DIS := 'PO NUMBER: ' || I_POH.PO_NUM ||
'- Error in fetching DELIVER_TO_LOCATION_ID: ' ||
SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_DIS);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POD.batch_name || ' DELIVER_TO_LOCATION_ID is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
/* UPDATE ZN_INV_GRN_DIST
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM)
AND LINE_NUM=I_POL.LINE_NUM
AND DISTRIBUTION_NUM=I_POD.DISTRIBUTION_NUM; */
p_return_code:=19;
-- ROLLBACK_PO(I_POD.PO_NUM);
-- COMMIT;
END;
--=====================================================================
--Validate the PO_CHARGE_ACCOUNT
--=====================================================================
x_debug_point:=24;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before PO_CHARGE_ACCOUNT validation '|| ' at debug point : ' ||x_debug_point);
BEGIN
IF I_POD.CHARGE_ACCOUNT IS NOT NULL THEN
SELECT CODE_COMBINATION_ID
INTO V_CHARGE_ACCOUNT_ID
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT1||'.'||SEGMENT2||'.'||SEGMENT3||'.'||SEGMENT4||'.'||SEGMENT5||'.'||SEGMENT6||'.'||SEGMENT7 =I_POD.CHARGE_ACCOUNT
and chart_of_accounts_id = 50445;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'enter exception for charge acc for '||I_POH.PO_NUM);
x_code_combination := I_POD.CHARGE_ACCOUNT;
airtel_create_ccid_item(x_code_combination,l_error_msg1,l_error_msg2,l_ccid);
V_CHARGE_ACCOUNT_ID := l_ccid;
FND_FILE.PUT_LINE(FND_FILE.LOG,'yes:Code Combination id -- '||V_CHARGE_ACCOUNT_ID);
FND_FILE.PUT_LINE(FND_FILE.LOG,'error1:Code Combination id -- '||l_error_msg1);
FND_FILE.PUT_LINE(FND_FILE.LOG,'errore2:Code Combination id -- '||l_error_msg2);
WHEN OTHERS
THEN
V_ERROR_DIS := 'PO NUMBER: ' || I_POH.PO_NUM ||'- Error in fetching CHARGE_ACCOUNT_ID: ' || SQLERRM;
-- INSERT error log
FND_FILE.PUT_LINE(FND_FILE.LOG,V_ERROR_DIS);
x_item_err_details := x_debug_point ||'Error in batch: ' || I_POD.batch_name || ' CHARGE_ACCOUNT_ID is not valid';
fnd_file.put_line (fnd_file.log, x_item_err_details);
UPDATE ZN_INV_GRN_DIST
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'E'
WHERE UPPER(PO_NUM) = UPPER(I_POH.PO_NUM)
AND LINE_NUM=I_POD.LINE_NUM
AND DISTRIBUTION_NUM=I_POD.DISTRIBUTION_NUM;
ROLLBACK_PO(I_POD.PO_NUM);
COMMIT;
p_return_code:=20;
END;
x_debug_point:=25;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before PO Distribution Interface population'|| ' at debug point : ' ||x_debug_point);
--=====================================================================
--Populate the PO Distribution Interface for validated Records
--=====================================================================
BEGIN
SELECT PROCESS_STATUS
INTO V_PROCESS_STATUS_D
FROM ZN_INV_GRN_DIST
WHERE UPPER(PO_NUM) = UPPER(I_POD.PO_NUM) AND
LINE_NUM = I_POD.LINE_NUM AND
DISTRIBUTION_NUM = I_POD.DISTRIBUTION_NUM;
IF NVL(V_PROCESS_STATUS_D,'A') <> 'E' THEN
SELECT PO_DISTRIBUTIONS_INTERFACE_S.NEXTVAL
INTO V_INTERFACE_DISTRUBUTION_ID
FROM DUAL;
INSERT INTO PO.PO_DISTRIBUTIONS_INTERFACE
(INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
DISTRIBUTION_NUM,
QUANTITY_ORDERED,
--DESTINATION_TYPE_CODE,
--DESTINATION_ORGANIZATION_ID,
--DESTINATION_SUBINVENTORY, no values available
DELIVER_TO_LOCATION,
CREATION_DATE,
ENCUMBERED_FLAG,
GL_ENCUMBERED_DATE,
GL_ENCUMBERED_PERIOD_NAME,
ACCRUE_ON_RECEIPT_FLAG,
CHARGE_ACCOUNT_ID)
VALUES
(V_INTERFACE_HEADER_ID,
V_INTERFACE_LINE_ID,
V_INTERFACE_DISTRUBUTION_ID,
I_POD.DISTRIBUTION_NUM,
I_POL.QUANTITY,
--'INVENTORY',
--v_inv_org_id,--V_ORG_ID,
--I_POD.DESTINATION_SUBINVENTORY,no values available
V_DELIVER_TO_LOCATION_CODE,
I_POH.CREATION_DATE,
'N',
'31-Dec-12',
'Dec-12',
DECODE(UPPER(I_POD.ACCRUE_ON_RECEIPT_FLAG), 'YES', 'Y', 'Y', 'Y', 'NO', 'N', 'N', 'N'),
V_CHARGE_ACCOUNT_ID);
COMMIT;
--====================================================
--Record inserted successfully
--====================================================
UPDATE ZN_INV_GRN_DIST
SET PROCESS_FLAG = 'P', --PENDING
PROCESS_STATUS = 'S'
WHERE UPPER(PO_NUM) = UPPER(I_POD.PO_NUM) AND
LINE_NUM = I_POD.LINE_NUM AND
DISTRIBUTION_NUM = I_POD.DISTRIBUTION_NUM;
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Distribution Line ' ||
I_POD.DISTRIBUTION_NUM ||
' For Line Number' || I_POL.LINE_NUM ||
' For PO: ' || I_POH.PO_NUM ||
' Shipment_num ' || I_POL.SHIPMENT_NUM ||
' Inserted Successfully');
ELSE
FND_FILE.PUT_LINE(FND_FILE.LOG,'Distribution have errors during validation');
END IF;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error is' || SQLERRM);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in Distribution Data Insertion');
END;
--=====================================================================
--All validations are completed for this record
--=====================================================================
UPDATE ZN_INV_GRN_DIST
SET PROCESS_FLAG = 'C' --COMPLETED
WHERE UPPER(PO_NUM) = UPPER(I_POD.PO_NUM) AND
LINE_NUM = I_POD.LINE_NUM AND
DISTRIBUTION_NUM = I_POD.DISTRIBUTION_NUM ;
COMMIT;
--**********************************************************************************************************************************
--**********************************************************************************************************************************
END LOOP; --C_POD
END LOOP; --C_POL
FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Validation for PO Number '||I_POH.PO_NUM ||' Ends Here');
FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------');
END LOOP; --C_POH
END;
--============================================================================
-- ROLLBACK_PO
--============================================================================
PROCEDURE ROLLBACK_PO(P_PO_NUM VARCHAR2) IS
BEGIN
--Delete the related po header and lines from the po interface
FND_FILE.PUT_LINE(FND_FILE.LOG,'PO Number' ||P_PO_NUM|| ' Will be Rollbacked');
DELETE FROM PO_HEADERS_INTERFACE
WHERE VENDOR_DOC_NUM = P_PO_NUM;
DELETE FROM PO_LINES_INTERFACE
WHERE INTERFACE_HEADER_ID =(
SELECT INTERFACE_HEADER_ID FROM PO_HEADERS_INTERFACE
WHERE VENDOR_DOC_NUM = P_PO_NUM);
DELETE FROM PO_distributions_INTERFACE
WHERE INTERFACE_HEADER_ID =(
SELECT INTERFACE_HEADER_ID FROM PO_HEADERS_INTERFACE
WHERE VENDOR_DOC_NUM = P_PO_NUM);
--FLAG THE HEADERS THAT ARE COMPLETED WITH ERROR
UPDATE ZN_INV_GRN_HEADERS
SET PROCESS_STATUS='X' --ABORT as lines or distributions has errors
WHERE UPPER(PO_NUM) = UPPER(P_PO_NUM);
UPDATE ZN_INV_GRN_LINES
SET PROCESS_STATUS='X' --ABORT as lines or distributions has errors
WHERE UPPER(PO_NUM) = UPPER(P_PO_NUM)
AND PROCESS_STATUS<>'E' ;
UPDATE ZN_INV_GRN_DIST
SET PROCESS_STATUS='X' --ABORT as lines or distributions has errors
WHERE UPPER(PO_NUM) = UPPER(P_PO_NUM)
AND PROCESS_STATUS<>'E' ;
COMMIT;
END;
--============================================================================
-- RESET_STATUS_ERROS
--============================================================================
PROCEDURE RESET_STATUS_ERRORS(p_errbuf out NOCOPY VARCHAR2, p_return_code out NOCOPY VARCHAR2) IS
BEGIN
DELETE FROM ZN_PO_INTERFACE_ERRORS WHERE staging_table IS NOT NULL;
UPDATE ZN_INV_GRN_HEADERS S
SET S.PROCESS_STATUS = NULL, S.PROCESS_FLAG = NULL
WHERE S.PROCESS_STATUS <> 'S';
UPDATE ZN_INV_GRN_LINES L
SET L.PROCESS_STATUS = NULL, L.PROCESS_FLAG = NULL
WHERE L.PROCESS_STATUS <> 'S';
UPDATE ZN_INV_GRN_DIST D
SET D.PROCESS_STATUS = NULL, D.PROCESS_FLAG = NULL
WHERE D.PROCESS_STATUS <> 'S';
UPDATE ZN_INV_GRN_LINES L
SET L.PROCESS_STATUS = NULL, L.PROCESS_FLAG = NULL
WHERE L.PO_NUM IN (SELECT H.PO_NUM
FROM ZN_INV_GRN_HEADERS H
WHERE H.PO_NUM = L.PO_NUM
AND NVL(H.PROCESS_STATUS,'NULL') <> 'S');
UPDATE ZN_INV_GRN_DIST D
SET D.PROCESS_STATUS = NULL, D.PROCESS_FLAG = NULL
WHERE D.PO_NUM IN (SELECT H.PO_NUM
FROM ZN_INV_GRN_HEADERS H
WHERE H.PO_NUM = D.PO_NUM
AND NVL(H.PROCESS_STATUS,'NULL') <> 'S');
p_return_code :='ok';
COMMIT;
EXCEPTION
WHEN OTHERS THEN p_errbuf:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error' ||SQLERRM);
END;
END PO_IMPORT_PKG;
/
No comments:
Post a Comment