PURCHASE ORDER INTERFACE(CODE)

PURCHASE ORDER INTERFACE(CODE)


Interface Tables:

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE
PO_INTERFACE_ERRORS where interface_type = ‘PO_DOCS_OPEN_INTERFACE’ (Error Table)
Base Tables:

PO_HEADERS_ALL
PO_LINES_ALL
PO_DISTRIBUTIONS_ALL
PO_LINE_LOCATIONS_ALL
Concurrent Program:

Import Standard Purchase Orders (To import Standard Purchase Orders)
Import Price Catalogs(To import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements)




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;
/

Purchase Order Interface 

Purchase Order Interface -- PO_HEADERS_INTERFACE
Interface Tables Used

1)PO_HEADERS_INTERFACE
2)PO_LINES_INTERFACE
3)PO_DISTRIBUTIONS_INTERFACE

INSERT INTO po_headers_interface
(interface_header_id
,batch_id
,action
,org_id
,document_type_code
,vendor_id
,vendor_site_code
,vendor_site_id
,vendor_doc_num
,currency_code
,agent_id
,terms_id
,approval_status
,ship_to_location_id
,effective_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,attribute_category
,attribute9 --xtra information
,comments
)
VALUES (po_headers_interface_s.NEXTVAL
,po_headers_interface_s.CURRVAL
,'ORIGINAL'
,g_org_id
,'STANDARD'
,rec_get_header_info.vendor_id
,rec_get_header_info.vendor_site_code
,rec_get_header_info.vendor_site_id
,po_headers_interface_s.CURRVAL
,'USD'
,l_agent_id
,rec_get_header_info.terms_id
,'APPROVED'
,rec_get_header_info.deliver_to_location_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'DS Fields'
,rec_get_header_info.attribute9 --xtra information
,l_comments
)

INSERT INTO po_lines_interface
(interface_header_id
,interface_line_id
,requisition_line_id
,line_num
,shipment_num
,line_type_id
,item
,item_description
,category_id
,unit_of_measure
,quantity
,unit_price
,ship_to_organization_id
,line_location_id
,effective_date
,need_by_date
,creation_date
,created_by
,last_update_date
,last_updated_by
,taxable_flag
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.NEXTVAL
,rec_get_line_info.requisition_line_id
,l_line_num
,1
,rec_get_line_info.line_type_id
,rec_get_line_info.segment1
,rec_get_line_info.item_description
,rec_get_line_info.category_id
,rec_get_line_info.primary_unit_of_measure
,rec_get_line_info.quantity
,rec_get_line_info.unit_price
,rec_get_line_info.destination_organization_id
, po_line_locations_s.NEXTVAL
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,TO_DATE (rec_get_line_info.need_by_date, 'DD-MON-RRRR')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,'Y'
);


INSERT INTO po.po_distributions_interface
(interface_header_id
,interface_line_id
,interface_distribution_id
,req_distribution_id
,org_id
,distribution_num
,quantity_ordered
,destination_organization_id
,destination_type_code
,destination_subinventory
, deliver_to_location_id
,set_of_books_id
,charge_account_id
,budget_account_id
,accrual_account_id
,variance_account_id
,deliver_to_person_id
,creation_date
,created_by
,last_update_date
,last_updated_by
)
VALUES (po.po_headers_interface_s.CURRVAL
,po.po_lines_interface_s.CURRVAL
,po.po_distributions_interface_s.NEXTVAL
,rec_get_distrib_info.distribution_id
,g_org_id
,rec_get_distrib_info.distribution_num
,rec_get_distrib_info.req_line_quantity
,rec_get_distrib_info.destination_organization_id
,rec_get_distrib_info.destination_type_code
,l_destination_subinventory
, rec_get_line_info.deliver_to_location_id
,rec_get_distrib_info.set_of_books_id
,rec_get_distrib_info.code_combination_id
,rec_get_distrib_info.budget_account_id
,l_new_accrual_account_id
,rec_get_distrib_info.variance_account_id
,rec_get_line_info.to_person_id
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
,TO_DATE (SYSDATE, 'DD-MON-RRRR')
,fnd_profile.VALUE ('USER_ID')
);

Concurrent Program:
apps.fnd_request.submit_request (application => 'PO' --Application,
program => 'POXPOPDOI'--Program,
argument1 => ''--Buyer ID,
argument2 => 'STANDARD'--Document Type,
argument3 => ''--Document Subtype,
argument4 => 'N'--Process Items Flag,
argument5 => 'N'--Create Sourcing rule,
argument6 => ''--Approval Status,
argument7 => ''--Release Generation Method,
argument8 => ''--NULL,
argument9 => g_org_id--Operating Unit ID,
argument10 => ''--Global Agreement
);

Calling PO Approval workflow to approve POs

SELECT TO_CHAR (po_wf_itemkey_s.NEXTVAL)
INTO v_wf_seq
FROM SYS.DUAL;

v_itemkey := TO_CHAR (l_header.po_header_id)
|| '-'
|| v_wf_seq;
po_reqapproval_init1.start_wf_process (itemtype => 'POAPPRV'
,itemkey => v_itemkey
,workflowprocess => 'POAPPRV_TOP'
,actionoriginatedfrom => 'PO_FORM'
,documentid => l_header.po_header_id
,documentnumber => l_header.segment1
,preparerid => l_header.agent_id
,documenttypecode => 'PO'
,documentsubtype => 'STANDARD'
,submitteraction => 'APPROVE'
,forwardtoid => NULL
,forwardfromid => l_header.agent_id
,defaultapprovalpathid => 10
,note => NULL
,printflag => 'N'
,faxflag => NULL
,faxnumber => NULL
);