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;