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;
/
3 comments:
hi prasatth,
it is chandra insitute code.correct..it wouldnot be helpful boss...better post good articles..
Hi Prasanth,
Please upload the code for distributions.conditions are
1)one shipment multiplw distributions
2)one Quantity multiple Shipments,
please explain the exception part and
why you are taking vendor_id before inserting into po_header_interface
Post a Comment