Infolinks

Sunday 29 July 2012

PO_INTERFACE

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