OUTBOUND_INTERFACE

REQUIREMENT:

Oracle Inventory

3    Topical Essay

GE Infrastructure Sensing needs to procure Inventory details from the Inv for payment details which we have done for particular Supplier. Inv Outbound interface program will be developed to transfer the data from oracle INV base tables into the OFM (Legacy) flat file.

3.1   Basic Business Needs

Company has to make all the Transaction in OFM (Legacy), GE needs a data file which is going to store in the legacy database.

This Program should bring the data from Inventory which has been entered between the Two Organizations.

And Item should be Purchasable item.

3.1.1   Assumptions

This design assumes that the following information will be maintained in Chinese language:

1.       Invorganization ID

2.       Invorganization Name

3.       ItemID

4.       Item

5.       Itemdescription

6.       Catetgory

7.       ItemUOM

________________________________________________________________________________

3.2   Inv Outbound output file

Report output should show the following information in the Output file.

No Of Records Transferred into file   : No of Records which are transferred to the Flat file.

User Name                                         : Name of the User Who has submitted

Resonsibility                                     : Name of the Responsibility from which report has submitted

Date of submitted                                                  : Request submission Date

3.2.1   Report Parameters

You run the ‘Inv Outbound Interface from the Standard Report Submission form. 

Enter the following parameters.

Sq	Parameter	Description	Value set	Default Type	Default Value	Required	Displayed	Prompt	Token
1	Org_ID From	Org_ID	None	Profile	Current Organization	Y	Y	ORG ID	P_org_id
2	Org_ID To	Org_ID	None	Profile	Current Organization	Y	Y	ORG ID	P_org_id

1.1.1    Configuration Dependencies (Optional)

Configuration	Configuration Setting
Register Concurrent Program	_Inv Outbound Interfaces
Register Concurrent Executable	XX_GE_INV
Responsibilities	RESP
Request Security Group	XX_GE_Payments
Data Group	Standard

3.2.2   Report Headings

4    Technical Overview

Use this section to describe the technical approach, high-level algorithms, process flows, etc.  Discuss possible solutions to technical challenges.  The detailed designs for forms and programs will provide the details for the general overview presented here.

Used the following tables to get the Customer and the invoive, item details.

MTL_SYSTEM_ITEMS_B

ORG_ORGANIZATION_DEFINITIONS

MTL_CATEGORIES

MTL_ITEM_CATEGORIES

5    Considerations

If any Oracle Applications tables change in future versions of Oracle Purchasing, components of this customization may also require changes to work correctly.  Evaluating the impact of such changes on custom programming is the responsibility of the company, although additional consulting services can be contracted to assist prior to upgrading.
Open and Closed Issues for this Deliverable

5.1.1   Open Issues

ID	Issue	Resolution	Responsibility	Target Date	Impact Date

5.1.2   Closed Issues

ID	Issue	Resolution	Responsibility	Target Date	Impact Date
1	Chinesse output	Developer can develop program as like English It will generate Chinese output Automatically , Since we have implemented UTF Instance.	Developer

CREATE OR REPLACE procedure XX_INV_Out1(Errbuf OUT varchar2,
                                       Retcode ouT varchar2,
            f_id    in number,
            t_id    in varchar2) as
cursor c1 is select
    msi.segment1 item,
    msi.inventory_item_id Itemid,
    msi.description  itemdesc,
    msi.primary_uom_code Uom,
    ood.organization_name name,
    ood.organization_id   id,
    mc . segment1||','||mc.segment2 Category
    from
    mtl_system_items_b           msi,
    org_organization_definitions ood,
    mtl_item_categories          mic,
    mtl_categories               mc
    where
    msi.organization_id       = ood.organization_id
    and msi.inventory_item_id = mic.inventory_item_id
    and msi.organization_id   = mic.organization_id
    and mic.category_id       = mc.category_id
    and msi.purchasing_item_flag = 'Y'
    and msi.organization_id between f_id and t_id;
x_id     utl_file.file_type;
l_count  number(5) default 0;
begin
          x_id:=utl_file.fopen('d:\oracle\proddb\8.1.7\plsql\temp','invoutdata.dat','W');
           --select * from v$parameter where name like '%utl_file%'
for x1 in c1 loop
l_count:=l_count+1;
utl_file.put_line(x_id,x1.item    ||'-'||
        x1.itemid  ||'-'||
        x1.itemdesc||'-'||
        x1.uom   ||'-'||
        x1.name   ||'-'||
        x1.id   ||'-'||
        x1.category   );
end loop;
utl_file.fclose(x_id);
Fnd_file.Put_line(Fnd_file.output,'No of Records transfered to the data file :'||l_count);
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted User name  '||Fnd_Profile.Value('USERNAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted Responsibility name '||Fnd_profile.value('RESP_NAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submission Date :'|| SYSDATE);
Exception
WHEN utl_file.invalid_operation THEN
  fnd_file.put_line(fnd_File.log,'invalid operation');
  utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
  fnd_file.put_line(fnd_File.log,'invalid path');
  utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
  fnd_file.put_line(fnd_File.log,'invalid mode');
  utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
  fnd_file.put_line(fnd_File.log,'invalid filehandle');

  utl_file.fclose_all;
WHEN utl_file.read_error THEN
  fnd_file.put_line(fnd_File.log,'read error');
  utl_file.fclose_all;
WHEN utl_file.internal_error THEN
  fnd_file.put_line(fnd_File.log,'internal error');
  utl_file.fclose_all;
WHEN OTHERS THEN
  fnd_file.put_line(fnd_File.log,'other error');
  utl_file.fclose_all;
End XX_INV_Out1;

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====