PURCHASE ORDER INTERFACE(CODE)
PURCHASE ORDER INTERFACE:-
HEADERS CTL FILE
-----------------
LOAD DATA
INFILE *
INSERT INTO TABLE xx_staging_headers
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( 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
,APPROVAL_STATUS
,FREIGHT_CARRIER
,FOB
,FREIGHT_TERMS
)
BEGINDATA
1,13,"ORIGINAL",204,"STANDARD","USD","Stock, Ms. Pat","3M Health Care","CORP HQ","H1- Detroit","V1- New York
City","APPROVED","UPS","Origin","Due"
2,13,"ORIGINAL",204,"STANDARD","USD","Stock, Ms. Pat","Star Gate Ltd","STAR GATE - BUY","H1- Detroit","V1- New York
City","APPROVED","UPS","Origin","Due"
***************************************************
LINES CTL FILE
----------------
load data
infile *
insert into table xx_staging_lines
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
( interface_header_id
,interface_line_id
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,item_id
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,SHIP_TO_LOCATION
,list_price_per_unit)
BEGINDATA
1,1,2,2,"Goods","AS54999","Sentinel Standard Desktop - Rugged",2155,"Ea",10,120,"M1","Adelaide",45
1,2,3,3,"Goods","75100005","Colour Cartidge",6076,"Ea",11,111,"M1","Adelaide",55
1,3,4,4,"Goods","AS54999","Sentinel Standard Desktop - Rugged",2155,"Ea",1,1400,"M1","Adelaide",65
2,4,5,1,"Goods","AS54999","Sentinel Standard Desktop - Rugged",2155,"Ea",222,234,"M1","Adelaide",105
1,5,6,5,"Goods","C1001","MOUSES",6960,"Ea",10,50,"M1","Adelaide",45
1)PO_HEADERS_INTERFACE
====================
1)DOCUMENT_TYPE_CODE = This column will accept any of the following string.
STANDARD
BLANKET
PLANNED CONTRACT
2)VENDOR_NAME =It will accept valid vendor name . by using PO_VENDORS Table
we can findout wether vendorname is valid or not.
4)VENDOR_SITE_NAME = PO_VENDOR_SITES_ALL
5)VENDOR_CONTACT_NAME = PO_VENDOR_CONTACTS
6)SHIPTO = HR_LOCATIONS table will be use to find the location is valid
or not.
7)BILLTO = HR_LOCATIONS table will be use to find the location is valid
or not.
8)CREATION_DATE =It should be in the Date Format.And also <=SYSDATE
9)AGENT_ID = it should be valid agentID(Buyer ID) .By using PO_AGENTS table
we can find out wether agent_id is valid or not.
10)ORG_ID =It should be Valid OrgID. By using hr_operating_units table we
can find wether it is valid or not.
11)AUTHORIZATION_STATUS = Valid status either APPROVED,INCOMPLETE,CANCELLED
12)CURRENCY_CODE = Valid Currency Code from FND_CURRENCIES table
we can find wether valid currency code or not.
2)PO_LINES_INTERFACE:
====================
LINE_NUM = Will accept only unique values.
LINE_TYPE = Should be a Valid Line type. By using PO_LINE_TYPES we can findout
wether it is valid or not.
ITEM =It should be a valid Item by using MTL_SYSTEM_ITEMS_B table we can
find wether valid Item or not.
ItemDesc = Item Desc also should be valid description
UOM_Code =Should be valid UOM by using MTL_UNITS_OF_MEASURES table we can find
wether it is valid or not.
QUANTItY =Will accept any Positive Number
unit_Price = Will accept any Positive Number
NEED_BY_DATE = date Format and >= PO creation Date(from PO Headers Interface table)
PROMISED_DATE = date Format and >= PO creation Date(from PO Headers Interface table)
ORG_ID =It should be Valid OrgID. By using hr_operating_units table we
can find wether it is valid or not.
SHIP_TO_ORG =Valid ORg_ID
ShipTo_Loc = Valid Shiping Location
----PROCEDURE FOR HEADERS AND LINES:
-------------------------------------
CREATE OR REPLACE PROCEDURE PO_Int(Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2) AS
CURSOR c1 IS SELECT * FROM xx_staging_headers;
CURSOR c2 IS SELECT * FROM xx_staging_lines;
l_vendor_id number(10);
l_item varchar2(150);
l_flag varchar2(4) default 'A';
l_msg varchar2(200);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
COMMIT;
FOR x1 IN c1 LOOP
BEGIN
SELECT vendor_id
INTO l_vendor_id
FROM po_vendors
WHERE vendor_name = x1.VENDOR_NAME;
-- AND ORG_ID = Fnd_Profile.Value('ORG_ID');
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_vendor_id := 0;
l_msg := 'Vendor id is Not in SYSTEM';
END;
IF l_flag != 'E' THEN
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_CARRIER
,FOB
,FREIGHT_TERMS
)
VALUES
(
x1.INTERFACE_HEADER_ID
,x1.batch_id
,x1.action
,x1.org_id --"Puffer-Sweiven"
,x1.document_type_code --done
,x1.CURRENCY_CODE --done
,x1.AGENT_NAME --done
,x1.VENDOR_NAME
,x1.VENDOR_SITE_CODE
,x1.SHIP_TO_LOCATION
,x1.BILL_TO_LOCATION
,SYSDATE-10 --done
,x1.APPROVAL_STATUS
,SYSDATE --done
,x1.FREIGHT_CARRIER
,x1.FOB
,x1.FREIGHT_TERMS
);
end if;
END LOOP;
FOR x2 IN c2
LOOP
l_flag := 'A';
--Item Validation
begin
select segment1
into l_item
from mtl_system_items_b
where segment1 = x2.item
AND ORGANIZATION_ID = fnd_profile.value('ORG_ID');
exception
when others then
l_flag := 'E';
l_vendor_id := 0;
l_msg := 'Item is not valid Item';
END;
--End of the Item Validation
if l_flag != 'E' then
INSERT INTO PO_LINES_INTERFACE
(
INTERFACE_LINE_ID
,INTERFACE_HEADER_ID
,LINE_NUM
,SHIPMENT_NUM
,LINE_TYPE
,ITEM
,ITEM_DESCRIPTION
,item_id
,UOM_CODE
,QUANTITY
,UNIT_PRICE
,SHIP_TO_ORGANIZATION_CODE
,SHIP_TO_LOCATION
,NEED_BY_DATE
,PROMISED_DATE
,list_price_per_unit
)
VALUES
(
x2.INTERFACE_LINE_ID
,x2.INTERFACE_HEADER_ID
,x2.LINE_NUM
,x2.SHIPMENT_NUM
,x2.LINE_TYPE
,x2.item
,x2.item_description
,x2.item_id
,x2.uom_code
,x2.quantity
,x2.unit_price
,x2.SHIP_TO_ORGANIZATION_CODE
,x2.ship_to_location
,SYSDATE --done
,SYSDATE --done
,x2.list_price_per_unit
);
end if;
END LOOP;
COMMIT;
END PO_Int;
/
No comments:
Post a Comment