Process Steps:
1)Create the Staging tables
2)Develop the Control files and register as concurrent program
3)Develop the PL/SQL Program and write the validations and insert into interface table
4)run the standard program called Import Standard Purchase orders from PO Reponsibility
Parameter : Default Buyer :null
Create or update items :No
PO Status :APPROVED
Batch ID :13 (We can get from headers interface table)
5)Take the Request ID execute following query we can get the PO numbers
select segment1 POnumber
from po_headers_all
where request _id = 145233;
6)Go to the PO Application and Query the PO from as per the PO number.
Pre - Requisitions:
====================
1)Distributions Accounts should be done.
2)Supplier,Site,Contact information should be entered
3)Locations Data should be entered
4)Curency,Organization ,Terms and Condisition data should be entered.
po_interface:
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.
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
PO_DISTRIBUTIONS_INTERFACE:
---------------------------
interface_header_id
interface_line_id
interface_distribution_id
set_of_books_id :valid Set of Books ID : Gl_sets_of_books
org_id :Valid OrgID : HR_OPERATING_UNITS
destination_organization_id : Valid Organization ID : ORG_ORGANIZATION_ID
quantity_ordered : Positive Numer (as per the shippment level total)
Control file creation :
Headers
load data
infile *
TRUNCATE into table xx_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","ABC","ABCSITE","H1- Detroit","V1- New York City","APPROVED","UPS","Origin","Due"
2,13,"ORIGINAL",204,"STANDARD","USD","Stock, Ms. Pat","SAMSUNG","SAMSUNGSITE","H1- Detroit","V1- New York City","APPROVED","UPS","Origin","Due"
3,13,"ORIGINAL",204,"STANDARD","USD","Stock, Ms. Pat","Bnq Computers","STAR GATE - BUY","H1- Detroit","V1- New York City","APPROVED","UPS","Origin","Due"
Lines:
load data
infile *
truncate into table xx_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,21,3,2,"Goods","AS54999","Sentinel Standard Desktop - Rugged",2155,"Ea",10,120,"M1","Adelaide",45
2,22,2,1,"Goods","AS54999","Sentinel Standard Desktop - Rugged",2155,"Ea",222,234,"M1","Adelaide",105
Distributions :
load data
infile *
TRUNCATE into table xx_dist
fields terminated by "," optionally enclosed by '"'
TRAILING NULLCOLS
(INTERFACE_HEADER_ID ,
INTERFACE_LINE_ID ,
INTERFACE_DISTRIBUTION_ID ,
SET_OF_BOOKS_ID ,
DESTINATION_ORGANIZATION_ID ,
ORG_ID ,
QUANTITY_ORDERED )
Begindata
1,21,1,1,207,204,10
2,22,2,1,207,204,222
========Creation of procedure===
CREATE OR REPLACE PROCEDURE PO_Int12(Errbuf OUT VARCHAR2,
Retcode OUT VARCHAR2) AS
CURSOR c1 IS SELECT * FROM XX_HEADERS;
CURSOR c2 IS SELECT * FROM XX_LINES;
CURSOR C3 IS SELECT *FROM XX_DIST;
l_vendor_id number(10);
l_item varchar2(150);
l_flag varchar2(4) default 'A';
l_msg varchar2(200);
l_site_code varchar2(100);
l_curr_code varchar2(10);
l_org_id number(6);
BEGIN
DELETE FROM PO_HEADERS_INTERFACE;
DELETE FROM PO_LINES_INTERFACE;
DELETE FROM PO_DISTRIBUTIONS_INTERFACE;
COMMIT;
FOR x1 IN c1 LOOP
BEGIN
SELECT vendor_id
INTO l_vendor_id
FROM po_vendors
WHERE vendor_name = x1.VENDOR_NAME;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := 'Vendor id is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
--Vendor Site code Validation
begin
select vendor_site_code
into l_site_code
from po_vendor_sites_all
where vendor_site_code = x1.vendor_site_code;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := 'Vendor Site Code is Not in SYSTEM';
Fnd_FIle.Put_line(Fnd_File.log,'Error Occured'||l_msg);
END;
--End of Site Code Validation
--Currency Code Validation
Begin
select currency_code
into l_curr_code
from fnd_currencies
where currency_code = x1.CURRENCY_CODE;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := 'Currency Code is Invalid';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
--End of te Currency Validation
--Operating Unit ID Validation
Begin
select organization_id
into l_org_id
from hr_operating_units
where organization_id = x1.org_id;
EXCEPTION
WHEN OTHERS THEN
l_flag := 'E';
l_msg := 'Invalid Organization ID';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
END;
--End of the ORG ID Validation
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_TERMS
)
VALUES
(
x1.INTERFACE_HEADER_ID
,x1.batch_id
,x1.action
,x1.org_id
,x1.document_type_code
,x1.CURRENCY_CODE
,x1.AGENT_NAME
,x1.VENDOR_NAME
,x1.VENDOR_SITE_CODE
,x1.SHIP_TO_LOCATION
,x1.BILL_TO_LOCATION
,SYSDATE-10
,x1.APPROVAL_STATUS
,SYSDATE
,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_msg := 'Item is not valid Item';
Fnd_FIle.Put_line(Fnd_File.Log,'Error Occured'||l_msg);
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,
sysdate,
X2.LIST_PRICE_PER_UNIT);
END IF;
END LOOP;
FOR x3 IN c3 LOOP
l_flag := 'A';
if l_flag != 'E' then
INSERT INTO PO_DISTRIBUTIONS_INTERFACE
(
INTERFACE_HEADER_ID ,
INTERFACE_LINE_ID ,
INTERFACE_DISTRIBUTION_ID ,
SET_OF_BOOKS_ID ,
DESTINATION_ORGANIZATION_ID ,
ORG_ID ,
QUANTITY_ORDERED
)
VALUES
(
X3.INTERFACE_HEADER_ID ,
X3.INTERFACE_LINE_ID ,
X3.INTERFACE_DISTRIBUTION_ID ,
X3.SET_OF_BOOKS_ID ,
X3.DESTINATION_ORGANIZATION_ID ,
X3.ORG_ID ,
X3.QUANTITY_ORDERED
);
END IF;
END LOOP;
COMMIT;
END PO_INT12;
/
==end of procedure====
No comments:
Post a Comment