PO Conversion

-----------------------------------------------------------------------------
STAGING TABLES
-----------------------------------------------------------------------------
**********************************************************
create table xx_po_headers(
INTERFACE_HEADER_ID number(10)
,BATCH_ID varchar2(25)
,ACTION varchar2(25)
,ORG_CODE VARCHAR2(25)
,DOCUMENT_TYPE_CODE VARCHAR2(50)
,CURRENCY_CODE VARCHAR2(25)
,AGENT_NAME VARCHAR2(25)
,VENDOR_NAME VARCHAR2(50)
,VENDOR_SITE_CODE VARCHAR2(50)
,SHIP_TO_LOCATION VARCHAR2(50)
,BILL_TO_LOCATION VARCHAR2(50)
,APPROVAL_STATUS VARCHAR2(50)
,FREIGHT_CARRIER VARCHAR2(50)
,FOB VARCHAR2(50)
,FREIGHT_TERMS VARCHAR2(50)
,STATUS_FLAG VARCHAR2(3)
,ERR_MSG VARCHAR2(1000));
***********************************************************

CREATE TABLE XX_PO_LINES(
interface_header_id NUMBER(10)
,interface_line_id NUMBER(10)
,LINE_NUM NUMBER(10)
,ACTION varchar2(25)
,SHIPMENT_NUM NUMBER(10)
,LINE_TYPE VARCHAR2(50)
,ITEM VARCHAR2(50)
,ITEM_DESCRIPTION VARCHAR2(100)
,item_id NUMBER(10)
,UOM_CODE VARCHAR2(50)
,QUANTITY NUMBER(10)
,UNIT_PRICE NUMBER(10)
,SHIP_TO_ORGANIZATION_CODE VARCHAR2(50)
,SHIP_TO_LOCATION VARCHAR2(50)
,list_price_per_unit NUMBER(10),
NEED_BY_DATE DATE,
PROMISED_DATE DATE,
CATEGORY_ID VARCHAR2(50),
STATUS_FLAG VARCHAR2(3),
ERR_MSG VARCHAR2(1000));
************************************************************

create table xx_po_distributions(
interface_header_id NUMBER(10)
,interface_line_id NUMBER(10),
interface_distribution_id number(10),
DIS_NUM number(10),
QUANTITY number(10),
DES_ORG varchar2(30),
DES_TYPE varchar2(50),
SUBINV VARCHAR2(50),
DELEIVER_TO VARCHAR2(50),
REQUESTOR VARCHAR2(100),
STATUS_FLAG VARCHAR2(3),
ERR_MSG VARCHAR2(1000));

***************************************************************
CREATE OR REPLACE PROCEDURE ABC (Errbuf OUT
VARCHAR2,
Retcode OUT VARCHAR2)
AS
i number := 1;
j number :=1;
K number :=1;

CURSOR C1 IS select mp.organization_code
ORG_CODE,pha.TYPE_LOOKUP_CODE
DOCUMENT_TYPE_CODE ,pha.CURRENCY_CODE
CURRENCY_CODE,ppf.FULL_NAME AGENT_NAME
,pv.VENDOR_name
VENDOR_NAME,pvs.VENDOR_SITE_CODE
VENDOR_SITE_CODE ,
hr1.LOCATION_CODE ship_to,hr2.LOCATION_CODE
bill_to,pha.SHIP_VIA_LOOKUP_CODE
FREIGHT_CARRIER,PHA.PO_HEADER_ID
PO_HEADER_ID1,
pha.FOB_LOOKUP_CODE FOB
,pha.FREIGHT_TERMS_LOOKUP_CODE
FREIGHT_TERMS
from po_headers_all pha ,per_all_people_f
ppf,po_vendors pv,po_vendor_sites_all pvs
,hr_locations_all hr1,
hr_locations_all hr2,mtl_parameters mp
where pha.segment1 IN (4491,4475)
and pha.AGENT_ID=ppf.PERSON_ID
and pha.VENDOR_ID=pv.VENDOR_ID
and pha.VENDOR_SITE_ID=pvs.VENDOR_SITE_ID
and pha.SHIP_TO_LOCATION_ID=hr1.LOCATION_ID
and pha.BILL_TO_LOCATION_ID=hr2.LOCATION_ID
and pha.ORG_ID=mp.ORGANIZATION_ID;


CURSOR C2(HEADER_ID VARCHAR2) IS select
PLL.PO_LINE_ID
PO_LINE_ID1,PLA.LINE_LOCATION_ID
LINE_LOCATION_ID1,pll.LINE_NUM
line_num,pla.SHIPMENT_NUM
SHIP_NUM,plt.PURCHASE_BASIS LINE_TYPE
,msi.SEGMENT1
ITEM,pll.ITEM_DESCRIPTION DESP,
MSI.PRIMARY_UOM_CODE
UOM_CODE,plA.QUANTITY
QUANTITY,pll.LIST_PRICE_PER_UNIT
LIST_PRICE,pll.UNIT_PRICE UNIT_PRICE,
mp.ORGANIZATION_CODE
SHIP_TO_ORGANIZATION_CODE
,hr.LOCATION_CODE SHIP_TO_LOCATION,
PLL.CATEGORY_ID CATEGORY_ID
from po_lines_all pll,PO_LINE_TYPES_B
plt,mtl_system_items_b msi,po_line_locations_all
pla,mtl_parameters mp,
hr_locations hr
where PLA.PO_HEADER_ID=HEADER_ID
AND Pll.LINE_TYPE_ID=plt.LINE_TYPE_ID
and pll.ITEM_ID=msi.INVENTORY_ITEM_ID
and pll.ORG_ID=msi.ORGANIZATION_ID
and pll.PO_LINE_ID=pla.PO_LINE_ID
and pll.PO_HEADER_ID=PLA.PO_HEADER_ID
and
pla.SHIP_TO_ORGANIZATION_ID=mp.ORGANIZATION_ID
and pla.SHIP_TO_LOCATION_ID=hr.LOCATION_ID;


cursor c3 (header_id varchar2,line_id
varchar2,L_LOCATION_ID VARCHAR2) is select
pda.DISTRIBUTION_NUM
dis_num,pda.QUANTITY_ORDERED
qUANTITY,mp.ORGANIZATION_CODE
des_org,pda.DESTINATION_TYPE_CODE
des_type,pda.DESTINATION_SUBINVENTORY subinv,
hr.LOCATION_CODE deliver_to,ppf.FULL_NAME
requestor
from po_distributions_all pda,hr_locations_all hr
,per_all_people_f ppf,mtl_parameters mp
where pda.po_header_id =header_id
and pda.po_line_id=line_id
AND PDA.LINE_LOCATION_ID=L_LOCATION_ID
and
pda.DELIVER_TO_LOCATION_ID=hr.LOCATION_ID(
+)
and
pda.DELIVER_TO_PERSON_ID=ppf.PERSON_ID(+)
and pda.DESTINATION_ORGANIZATION_ID
=mp.ORGANIZATION_ID;

BEGIN

DELETE FROM xx_po_headers;
DELETE FROM XX_PO_LINES;
DELETE FROM xx_po_distributions;

COMMIT;

FOR REC_C1 IN C1 LOOP

INSERT INTO
xx_po_headers(INTERFACE_HEADER_ID,BATCH_ID
,ACTION,ORG_CODE,DOCUMENT_TYPE_CODE,CURRENCY_CODE,AGENT_NAME,VENDOR_NAME,
VENDOR_SITE_CODE
,SHIP_TO_LOCATION,BILL_TO_LOCATION,APPROVAL_STATUS
,FREIGHT_CARRIER ,FOB
,FREIGHT_TERMS,STATUS_FLAG ) VALUES
(i,100,'ORIGINAL',REC_C1.ORG_CODE,REC_C1.DOCUMENT_TYPE_CODE ,
REC_C1.CURRENCY_CODE,REC_C1.AGENT_NAME
,REC_C1.VENDOR_NAME,REC_C1.VENDOR_SITE_CODE
,REC_C1.ship_to,REC_C1.bill_to,'APPROVED',REC_C1.FREIGHT_CARRIER,REC_C1.FOB
,REC_C1.FREIGHT_TERMS,'N');

FOR REC_C2 IN C2(REC_C1.PO_HEADER_ID1) LOOP

INSERT INTO XX_PO_LINES(interface_header_id
,interface_line_id,LINE_NUM,ACTION,SHIPMENT_NUM,LINE_TYPE ,ITEM,ITEM_DESCRIPTION ,UOM_CODE,QUANTITY,UNIT_PRICE,SHIP_TO_ORGANIZATION_CODE,SHIP_TO_LOCATION,list_price_per_unit,NEED_BY_DATE
,PROMISED_DATE,STATUS_FLAG,CATEGORY_ID )
VALUES (i,J,REC_C2.line_num,'ORIGINAL',
REC_C2.SHIP_NUM,REC_C2.LINE_TYPE,REC_C2.ITEM
,REC_C2.DESP,REC_C2.UOM_CODE
,REC_C2.QUANTITY,
REC_C2.UNIT_PRICE,REC_C2.SHIP_TO_ORGANIZATION_CODE
,REC_C2.SHIP_TO_LOCATION,REC_C2.LIST_PRICE,SYSDATE+10,SYSDATE+10,'N',REC_C2.CATEGORY_ID
);


FOR REC_C3 IN
C3(REC_C1.PO_HEADER_ID1,REC_C2.PO_LINE_ID1,
REC_C2.LINE_LOCATION_ID1) LOOP

INSERT INTO
xx_po_distributions(interface_header_id,interface_line_id ,interface_distribution_id,DIS_NUM,
QUANTITY, DES_ORG,
DES_TYPE,SUBINV,DELEIVER_TO,REQUESTOR,STATUS_FLAG)
values
(i,j,k,rec_c3.dis_num,rec_c3.qUANTITY,rec_c3.des_org,rec_c3.des_type,rec_c3.subinv,
rec_c3.deliver_to,rec_c3.requestor,'N');
k:=k+1;
end loop;
j:=j+1;
END LOOP;

commit;
i:=i+1;
END LOOP;
commit;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,SQLERRM);
RETCODE:=1;
END;


*************************************
VALIDATIN NOT DONE FOR SHIP_TO_LOCATION,CATEGORY_ID IN LINES AND DES_TYPE,SUBINV
******************************************

********************************************************************************
CREATE OR REPLACE procedure abc_validate1(Errbuf OUT
VARCHAR2,
Retcode OUT VARCHAR2)
AS
cursor c1 is select A.*,ROWID from xx_po_headers A WHERE A.STATUS_FLAG='N';

CURSOR C2(I_HEADER_ID NUMBER) IS select B.*,ROWID from XX_PO_LINES B
WHERE B.INTERFACE_HEADER_ID=I_HEADER_ID AND B.STATUS_FLAG='N';

cursor c3(I_HEADER_ID NUMBER,I_LINE_ID NUMBER) is select c.*,ROWID FROM
xx_po_distributions C WHERE C.INTERFACE_HEADER_ID=I_HEADER_ID AND C.INTERFACE_LINE_ID=I_LINE_ID
AND C.STATUS_FLAG='N';

CURSOR V_c1 is select A.*,ROWID from xx_po_headers A WHERE A.STATUS_FLAG='P';

CURSOR V_C2(I_HEADER_ID NUMBER) IS select B.*,ROWID from XX_PO_LINES B
WHERE B.INTERFACE_HEADER_ID=I_HEADER_ID ;

cursor V_c3(I_HEADER_ID NUMBER,I_LINE_ID NUMBER) is select c.*,ROWID FROM
xx_po_distributions C WHERE C.INTERFACE_HEADER_ID=I_HEADER_ID AND C.INTERFACE_LINE_ID=I_LINE_ID;

l_vendor_id number(10);
l_item varchar2(150);
l_flag varchar2(4) := NULL;
l_msg varchar2(1000):= NULL;
l_site_code varchar2(100);
l_curr_code varchar2(10);
l_org_id number(6);
l_USER_ID NUMBER(20);
s_loc_id number(20);
B_loc_id number(20);
I NUMBER:=1;
L_TERM VARCHAR2(40);
L_CARRIER VARCHAR2(40);
L_FOB VARCHAR2(20);
L_LINE_TYPE VARCHAR2(20);
L_UOM_CODE VARCHAR2(20);
L_SHIP_TO_ORGANIZATION_CODE VARCHAR2(10);
L_DELEIVER_TO VARCHAR2(40);
R_USER_ID VARCHAR2(20);
L_DOCUMENT_SUBTYPE VARCHAR2(30);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
DELETE FROM PO_DISTRIBUTIONS_INTERFACE;
COMMIT;
for rec_c1 in c1 loop
l_flag := null;
l_msg := null;
-- VALIDATION FOR ORGANIZATION
Begin
select organization_id
into l_org_id
from mtl_parameters
where ORGANIZATION_CODE = rec_c1.org_code;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'Invalid Organization ID';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
-- VALIDATION FOR CURRENCY
begin
select currency_code
into l_curr_code
from fnd_currencies
where currency_code = rec_c1.CURRENCY_CODE;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'Currency Code is Invalid';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
--VALIDATION FOR DOCUMENT TYPE
BEGIN
SELECT DOCUMENT_SUBTYPE INTO L_DOCUMENT_SUBTYPE FROM PO_DOCUMENT_TYPES_ALL_B
WHERE DOCUMENT_TYPE_CODE='PO' AND ORG_ID =FND_PROFILE.VALUE('ORG_ID')
AND DOCUMENT_SUBTYPE=REC_C1.DOCUMENT_TYPE_CODE;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'DOCUMENT TYPE is Invalid';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
-- VALIDATION FOR AGENT
BEGIN
SELECT PERSON_ID INTO l_USER_ID FROM per_all_people_f where full_name=REC_C1.AGENT_NAME;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'BUYER DOESNOT EXISTS ';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
-- VALIDATION FOR VENDOR
BEGIN
SELECT vendor_id
INTO l_vendor_id
FROM po_vendors
WHERE vendor_name = rec_c1.VENDOR_NAME;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'Vendor is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
-- VALIDATION FOR VENDOR SITE
begin
select vendor_site_code
into l_site_code
from po_vendor_sites_all
where vendor_site_code =
rec_c1.VENDOR_SITE_CODE
and VENDOR_ID=l_vendor_id
and ORG_ID=l_org_id;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'Vendor Site Code is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
-- VALIDATION FOR SHIP TO LOCATION
BEGIN
select LOCATION_ID INTO s_loc_id
from hr_locations_all where location_code=REC_C1.SHIP_TO_LOCATION;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'SHIP TO LOCATION is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
-- VALIDATION FOR BILL TO LOCATION
BEGIN
select LOCATION_ID INTO B_loc_id
from hr_locations_all where location_code=REC_C1.BILL_TO_LOCATION;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'BILL TO LOCATION is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
--VALIDATIION FOR FRIEGHT TERMS
BEGIN
SELECT MEANING INTO L_TERM FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='FREIGHT TERMS' AND MEANING=REC_C1.FREIGHT_TERMS;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'FREIGHT TERMS is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
--VALIDATION FOR FREIGHT_CARRIER
BEGIN
SELECT MEANING INTO L_CARRIER FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='FREIGHT_CARRIER' AND MEANING=REC_C1.FREIGHT_CARRIER;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'FREIGHT CARRIER is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
--VALIDATION FOR FOB
BEGIN
SELECT MEANING INTO L_FOB FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE='FOB' AND MEANING=REC_C1.FOB AND TAG='Y';
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'FOB is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
IF l_flag is Null THEN
UPDATE XX_PO_HEADERS SET STATUS_FLAG='P' WHERE ROWID=REC_C1.ROWID;
ELSE
UPDATE XX_PO_HEADERS SET STATUS_FLAG='E',
ERR_MSG=l_msg WHERE ROWID=REC_C1.ROWID;
UPDATE XX_PO_HEADERS SET STATUS_FLAG = 'E',
ERR_MSG=ERR_MSG||'ERROR IN HEADER NUMBER'||REC_C1.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C1.INTERFACE_HEADER_ID;
UPDATE XX_PO_LINES SET STATUS_FLAG='E',
ERR_MSG=ERR_MSG||'ERROR IN HEADER NUMBER'||REC_C1.INTERFACE_HEADER_ID WHERE INTERFACE_HEADER_ID=REC_C1.INTERFACE_HEADER_ID;
UPDATE XX_PO_DISTRIBUTIONS SET STATUS_FLAG = 'E',
ERR_MSG=ERR_MSG||'ERROR IN HEADER NUMBER'||REC_C1.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C1.INTERFACE_HEADER_ID;
RETCODE :=1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR IN HEADER NUMBER'||' : '||REC_C1.INTERFACE_HEADER_ID);
END IF;
COMMIT;
l_flag := null;
l_msg := null;
FOR REC_C2 IN C2(REC_C1.INTERFACE_HEADER_ID) LOOP
l_flag := null;
l_msg := null;
--Item,ITEM DISCRITION Validation
begin
select segment1
into l_item
from mtl_system_items_b
where segment1 = REC_C2.item
AND ORGANIZATION_ID
=FND_PROFILE.VALUE('ORG_ID')
AND DESCRIPTION=REC_C2.ITEM_DESCRIPTION;
exception
when others then
l_flag := 'E';
l_msg := l_MSG||'Item is not valid Item'||sqlerrm;
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;

-- VALIDATION FOR UOM
BEGIN
SELECT UOM_CODE INTO L_UOM_CODE from MTL_UNITS_OF_MEASURE_TL WHERE UOM_CODE=REC_C2.UOM_CODE;
exception
when others then
l_flag := 'E';
l_msg := l_MSG||'UOM IS NOT DEFINED'||sqlerrm;
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
--VALIDATION FOR SHIP_TO_ORGANIZATION_CODE
BEGIN
SELECT ORGANIZATION_CODE INTO L_SHIP_TO_ORGANIZATION_CODE FROM MTL_PARAMETERS WHERE ORGANIZATION_CODE=REC_C2.SHIP_TO_ORGANIZATION_CODE;
exception
when others then
l_flag := 'E';
l_msg := l_MSG||'SHIP TO ORGANIZATION CODE IS NOT DEFINED'||sqlerrm;
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;

IF l_flag is Null THEN
UPDATE XX_PO_LINES SET STATUS_FLAG='P' WHERE ROWID=REC_C2.ROWID;
ELSE
UPDATE XX_PO_LINES SET STATUS_FLAG='E',
ERR_MSG=l_msg WHERE ROWID=REC_C2.ROWID;
UPDATE XX_PO_HEADERS SET STATUS_FLAG = 'E',
ERR_MSG=ERR_MSG||'ERROR IN LINE NUMBER'||REC_C2.INTERFACE_LINE_ID||'FOR HEADER NUMBER'|| REC_C2.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C2.INTERFACE_HEADER_ID;
UPDATE XX_PO_LINES SET STATUS_FLAG='E',
ERR_MSG=ERR_MSG||'ERROR IN LINE NUMBER'||REC_C2.INTERFACE_LINE_ID||'FOR HEADER NUMBER'|| REC_C2.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C2.INTERFACE_HEADER_ID and ROWID <> REC_C2.ROWID;
UPDATE XX_PO_DISTRIBUTIONS SET STATUS_FLAG = 'E',
ERR_MSG=ERR_MSG||'ERROR IN LINE NUMBER'||REC_C2.INTERFACE_LINE_ID||'FOR HEADER NUMBER'|| REC_C2.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C2.INTERFACE_HEADER_ID;
RETCODE := 1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR IN LINE NUMBER'||' : '||REC_C2.INTERFACE_LINE_ID||'FOR HEADER NUMBER'||' : '|| REC_C2.INTERFACE_HEADER_ID);
END IF;
COMMIT;
l_flag := null;
l_msg := null;
FOR REC_C3 IN C3(REC_C2.INTERFACE_HEADER_ID,REC_C2.INTERFACE_LINE_ID) LOOP
l_flag := null;
l_msg := null;
--VALIDATION FOR DES_ORG
BEGIN
select organization_id
into l_org_id
from mtl_parameters
where ORGANIZATION_CODE = rec_c3.DES_ORG;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'Invalid Organization ID';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
--VALIDATION FOR DELIVER TO LOCATION

IF REC_C3.DELEIVER_TO IS NOT NULL THEN
BEGIN
SELECT LOCATION_CODE INTO L_DELEIVER_TO FROM HR_LOCATIONS WHERE LOCATION_CODE=REC_C3.DELEIVER_TO;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'Invalid DELIVER TO LOCATION';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
END IF;
-- VALIDATION FOR REQUESTOR
IF REC_C3.REQUESTOR IS NOT NULL THEN
BEGIN
SELECT PERSON_ID INTO R_USER_ID FROM per_all_people_f where full_name=REC_C3.REQUESTOR;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := l_msg ||'REQUESTOR DOESNOT EXISTS ';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
END IF;
IF l_flag is Null THEN
UPDATE XX_PO_DISTRIBUTIONS SET STATUS_FLAG='P' WHERE ROWID=REC_C3.ROWID;
ELSE
UPDATE XX_PO_DISTRIBUTIONS SET STATUS_FLAG='E',
ERR_MSG=l_msg WHERE ROWID=REC_C3.ROWID;
UPDATE XX_PO_HEADERS SET STATUS_FLAG = 'E',
ERR_MSG=ERR_MSG||'ERROR IN DISTRIBUTIONS NUMBER'||REC_C3.INTERFACE_DISTRIBUTION_ID ||'IN LINE NUMBER'||REC_C3.INTERFACE_LINE_ID||'FOR HEADER NUMBER'||REC_C3.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C3.INTERFACE_HEADER_ID;
UPDATE XX_PO_LINES SET STATUS_FLAG='E',
ERR_MSG=ERR_MSG||'ERROR IN DISTRIBUTIONS NUMBER'||REC_C3.INTERFACE_DISTRIBUTION_ID ||'IN LINE NUMBER'||REC_C3.INTERFACE_LINE_ID||'FOR HEADER NUMBER'||REC_C3.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C3.INTERFACE_HEADER_ID;
UPDATE XX_PO_DISTRIBUTIONS SET STATUS_FLAG = 'E',
ERR_MSG=ERR_MSG||'ERROR IN DISTRIBUTIONS NUMBER'||REC_C3.INTERFACE_DISTRIBUTION_ID||'IN LINE NUMBER'||REC_C3.INTERFACE_LINE_ID||'FOR HEADER NUMBER'||REC_C3.INTERFACE_HEADER_ID
WHERE INTERFACE_HEADER_ID=REC_C3.INTERFACE_HEADER_ID and ROWID <> REC_C3.ROWID;
RETCODE :=1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR IN DISTRIBUTIONS NUMBER'||' : '||REC_C3.INTERFACE_DISTRIBUTION_ID||'IN LINE NUMBER'||' : '||REC_C3.INTERFACE_LINE_ID||'FOR HEADER NUMBER'||' : '|| REC_C3.INTERFACE_HEADER_ID);
END IF;
COMMIT;
END LOOP;
l_flag := null;
l_msg := null;
END LOOP;
l_flag := null;
l_msg := null;
END LOOP;
For V_rec_c1 in V_c1 loop
select organization_id
into l_org_id
from mtl_parameters
where ORGANIZATION_CODE = V_rec_c1.org_code;
INSERT INTO po_headers_interface
(
INTERFACE_HEADER_ID
,BATCH_ID
,ACTION
,ORG_ID
,DOCUMENT_TYPE_CODE
,CURRENCY_CODE
,AGENT_NAME
,VENDOR_NAME
,VENDOR_SITE_CODE
,SHIP_TO_LOCATION
,BILL_TO_LOCATION
,creation_date
,APPROVAL_STATUS
,APPROVED_DATE
,FREIGHT_TERMS
,FREIGHT_CARRIER
,FOB
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
)
VALUES
(
V_rec_c1.INTERFACE_HEADER_ID
,V_rec_c1.BATCH_ID
,V_rec_c1.ACTION
,l_org_id
,V_rec_c1.DOCUMENT_TYPE_CODE
,V_rec_c1.CURRENCY_CODE
,V_rec_c1.AGENT_NAME
,V_rec_c1.VENDOR_NAME
,V_rec_c1.VENDOR_SITE_CODE
,'5_VIKAS_INV_LOCATION'
,V_rec_c1.BILL_TO_LOCATION
,SYSDATE
,V_rec_c1.APPROVAL_STATUS
,SYSDATE
,V_rec_c1.FREIGHT_TERMS
,V_rec_c1.FREIGHT_CARRIER,
V_REC_C1.FOB,
FND_PROFILE.VALUE('USER_ID'),
SYSDATE,
FND_PROFILE.VALUE('USER_ID'),
-1
);
FOR V_REC_C2 IN V_C2(V_REC_C1.INTERFACE_HEADER_ID) LOOP
INSERT INTO PO_LINES_INTERFACE
(
INTERFACE_LINE_ID
,ACTION
,INTERFACE_HEADER_ID
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,NEED_BY_DATE
,PROMISED_DATE
,list_price_per_unit
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,creation_date
,CATEGORY_ID
)
VALUES
(
V_REC_C2.INTERFACE_LINE_ID
,V_rec_c2.ACTION
,V_REC_C2.INTERFACE_HEADER_ID
,V_REC_C2.LINE_NUM
,V_REC_C2.SHIPMENT_NUM
,INITCAP(V_REC_C2.LINE_TYPE)
,V_REC_C2.ITEM
,V_REC_C2.ITEM_DESCRIPTION
,V_REC_C2.UOM_CODE
,V_REC_C2.QUANTITY,
V_REC_C2.UNIT_PRICE
,V_REC_C2.SHIP_TO_ORGANIZATION_CODE
,V_REC_C2.NEED_BY_DATE
,V_REC_C2.PROMISED_DATE
,V_REC_C2.LIST_PRICE_PER_UNIT
,FND_PROFILE.VALUE('USER_ID')
,SYSDATE
,FND_PROFILE.VALUE('USER_ID')
,-1
,SYSDATE
,V_REC_C2.CATEGORY_ID);
FOR V_REC_C3 IN V_C3(V_REC_C2.INTERFACE_HEADER_ID,V_REC_C2.INTERFACE_LINE_ID) LOOP
INSERT INTO PO_distributions_INTERFACE
(INTERFACE_HEADER_ID,
INTERFACE_LINE_ID,
INTERFACE_DISTRIBUTION_ID,
DISTRIBUTION_NUM,
ORG_ID,
QUANTITY_ORDERED,
DESTINATION_ORGANIZATION,
DESTINATION_TYPE_CODE,
DELIVER_TO_LOCATION,
DELIVER_TO_PERSON_FULL_NAME,
DESTINATION_SUBINVENTORY)
values
(V_REC_C3.INTERFACE_HEADER_ID,
V_REC_C3.INTERFACE_LINE_ID,
V_REC_C3.INTERFACE_DISTRIBUTION_ID,
V_REC_C3.DIS_NUM,
FND_PROFILE.VALUE('ORG_ID'),
V_REC_C3.QUANTITY,
V_REC_C3.DES_ORG,
V_REC_C3.DES_TYPE,
V_REC_C3.DELEIVER_TO,
V_REC_C3.REQUESTOR,
V_REC_C3.SUBINV);
END LOOP;
END LOOP;
COMMIT;
END LOOP;
END;