AIM

MD.070 Application Extension Technical Design

GE Energy

AP – Supplier Import Interface

Author:                         Mastanvali

Creation Date:            November 29, 2005

Last Updated:             December 16, 2008

Document Ref:           MD050

Version:                        DRAFT 1A

Note:Title, Subject, Last Updated Date, Reference Number, and Version are marked by a Word Bookmark so that they can be easily reproduced in the header and footer of documents.  When you change any of these values, be careful not to accidentally delete the bookmark.  You can make bookmarks visible by selecting Tools->Options…View and checking the Bookmarks option in the Show region.

Approvals:

<Approver 1>
<Approver 2>

Note:To add additional approval lines, press [Tab] from the last cell in the table above.

Copy Number    _____

Note:You can delete any elements of this cover page that you do not need for your document.  For example, Copy Number is only required if this is a controlled document and you need to track each copy that you distribute.

Document Control

Change Record

4

Date	Author	Version	Change Reference
29-Nov-05	Mastanvali	Draft 1a	No Previous Document

Reviewers

Name	Position

Distribution

Copy No.	Name	Location
1	Library Master	Project Library
2		Project Manager
3
4

Note:The copy numbers referenced above should be written into the Copy Number space on the cover of each distributed copy.  If the document is not controlled, you can delete this table, the Note To Holders, and the Copy Number label from the cover page.

Note To Holders:

If you receive an electronic copy of this document and print it out, please write your name on the equivalent of the cover page, for document control purposes.

If you receive a hard copy of this document, please write your name on the front cover, for document control purposes.

Contents

Document Control................................................................................................................................ ii

Technical Overview – Supplier Import........................................................................................... 1

Module List..................................................................................................................................... 3

Parameters to the program:................................................................................................................ 4

Block Relationship Diagram...................................................................................................... 4

Table and View Usage................................................................................................................. 4

Profram Logic......................................................................................................................................... 5

Business Data Mapping.............................................................................................................. 5

Pseudo-Code................................................................................................................................... 7

SQL Statements.............................................................................................................................. 8

Validation Logic............................................................................................................................ 8

Incompatibility............................................................................................................................... 9

Performance Considerations..................................................................................................... 9

Other Considerations................................................................................................................... 9

Integration Issues................................................................................................................................ 10

Changes Required....................................................................................................................... 10

Shared Components................................................................................................................... 10

Alert conditions........................................................................................................................... 10

Incompatibilities......................................................................................................................... 10

Performance Issues..................................................................................................................... 10

Database Design.................................................................................................................................. 11

Desired Table Changes.............................................................................................................. 11

New/Updated Seed Data......................................................................................................... 11

Descriptive Flexfields................................................................................................................. 11

Value Sets...................................................................................................................................... 11

Grants/Synonyms...................................................................................................................... 12

Archiving....................................................................................................................................... 12

Database Diagram...................................................................................................................... 12

Tables, Indexes, Sequences....................................................................................................... 12

Installation Requirements................................................................................................................ 13

Implementation Notes....................................................................................................................... 14

Design Summary........................................................................................................................ 14

Coding Summary........................................................................................................................ 14

Testing Summary........................................................................................................................ 14

Installation.................................................................................................................................... 15

Open and Closed Issues for this Deliverable.............................................................................. 16

Open Issues................................................................................................................................... 16

Closed Issues................................................................................................................................ 16

Note:To update the table of contents, put the cursor anywhere in the table and press [F9].  To change the number of levels displayed, select the menu option Insert‑>Index and Tables, make sure the Table of Contents tab is active, and change the Number of Levels to a new value.

Technical Overview – Supplier Import

Solution Design       

This section describes in detail the various stages that occur during the interface of Supplier Information Conversion into Oracle Applications R11i, including the information such as program logic, program and executable registration, table and view usage, compatibility and performance, value set, and flexfield usage.

The validation program will validate data present in the Pre-Interface tables and derive the default data wherever mapping provided and populate the data in the Oracle Open Interface Tables

Standard Concurrent Programs, will be used to import the data from Oracle Open Interface Tables into Oracle Account Payables Supplier base Tables

Approach      

·         You will get Supplier Master Data File from the Client 

·         Write a SQL*Loader program to import data into staging tables

·         The Package xxge_vendor_omport_pkg should retrieve data from custom tables, validates the data and inserts into Vendor  Interface Tables

·         Standard Oracle Programs will transfer the data from Interface tables to Oracle base tables   

     

Dataflow

 

 

Module List

Forms

None

Reports

None

Concurrent Programs

Supplier Interface includes the following concurrent programs:

<XXGE_VEN_IMPORT>              XXGE Vendor Interface Program

Database Triggers

None

Note:Add other components above as required

Parameters to the program:

Block Relationship Diagram

Not Applicable.

Note:The diagram above is just an example—Double click on it to edit it as an embedded object.  Select it and choose Edit->VISIO Object->Open to open it in the full Visio application.

You can also use Oracle Developer to draw your diagram and define most of the information in this section.

Table and View Usage

 

Table Name	Select	Insert	Update	Delete
	X
APPS.FINANCIALS_SYSTEM_PARAMS_ALL	Y	N	N	N
APPS. AP_SYSTEM_PARAMETERS_ALL	Y	N	N	N
APPS. AP_SUPPLIERS_INT	N	Y	N	N
APPS. AP_SUPPLIER_SITES_INT	Y	N	N	N
APPS.AP_SUP_SITE_CONTACT_INT	N	Y	Y	N
XXGE_VENDOR_INFO	Y	Y	Y	N
XXGE_VEN_SITE_INFO	Y	Y	Y	N
XXGE_VEN_SITE_CON_INFO	Y	Y	Y	N

Profram Logic

Business Data Mapping

Source System	GE SMF
Target System	Oracle Account Payables
Objective	To conversion the GE Supplier Master data from GE system to Account Payables Module

AP_SUPPLIERS_INT

Srl No.	Target Table /View AP_SUPPLIERS_INT	Required (Y/N)	Source Table	Field Name	Translation  / Derivation/ Default Value
	Field Names
1.	VENDOR_INTERFACE_ID	Y		Vendor Interface Id	Generated by AP_SUPPLIERS_INT_S Sequence.
2.	VENDOR_NAME	Y	XXGE_VENDOR_INFO	Supplier Name	No Validation Required
3.	SEGMENT1	N	NA	Supplier Unique Number	No Validation Required.
4.	ENABLED_FLAG	Y	XXGE_VENDOR_INFO	Supplier Enabled	No Validation Required
5.	VENDOR_TYPE_LOOKUP_CODE	Y	NA		The default value is ‘VENDOR’
6.	TERMS_ID	Y			Derived from the default setups of AP
7.	SET_OF_BOOKS_ID	Y			Derived from the default setups of AP
8.	ACCTS_PAY_CODE_COMBINATION_ID	Y			Derived from the default setups of AP
9.	CREATED_BY	Y			USER_ID
10.	CREATION_DATE	Y			SYSDATE
11.	LAST_UPDATE_DATE	Y			USER_ID
12.	LAST_UPDATED_BY	Y			USER_ID
13.	PREPAY_CODE_COMBINATION_ID	Y			Derived from the default setups of AP
14.	INVOICE_CURRENCY_CODE	Y			Validated against FND_CURRENCIES
15.	PAYMENT_CURRENCY_CODE	Y			Validated against FND_CURRENCIES

AP_SUPPLIER_SITES_INT

Srl No.	Target Table /View OE_LINES_IFACE_ALL	Required (Y/N)	Source Table	Field Name	Translation  / Derivation/ Default Value
	Field Names
1.	VENDOR_INTERFACE_ID	Y	XXGE_VEN_SITE_INFO		No Validation Required
2.	VENDOR_SITE_CODE	Y	XXGE_VEN_SITE_INFO		No Validation Required
3.	ADDRESS_LINE1	Y	XXGE_VEN_SITE_INFO		No Validation Required
4.	ADDRESS_LINE1	Y	XXGE_VEN_SITE_INFO		No Validation Required
5.	ADDRESS_LINE1	Y	XXGE_VEN_SITE_INFO		Validate against the mtl_Units_of_Measure table
6.	CITY	Y	XXGE_VEN_SITE_INFO		Derive the Inventory Organization ID from ACT_WAREHOUSE_CODE vaue
7.	STATE	Y	XXGE_VEN_SITE_INFO		Same as the Customer ID derived in at the header level
8.	ZIP	N	XXGE_VEN_SITE_INFO		Derived Based On ACT_BILL_TO_LOCATION_CODE On Order LINE and Party Number derived from the customer number passed in Pre-Interface table
9	COUNTRY	N	XXGE_VEN_SITE_INFO		Derived Based On ACT_SHIP_TO_LOCATION_CODE On Order Line and Party Number derived from the customer number passed in Pre-Interface table
10	CREATED_BY	Y			USER_ID
11	CREATION_DATE	Y			SYSDATE
12	LAST_UPDATE_DATE	Y			USER_ID
13	LAST_UPDATED_BY	Y			USER_ID

AP_SUP_SITE_CONTACT_INT

Srl No.	FIELD_NAME	Required (Y/N)	Source Table	Field Name	Translation  / Derivation/ Default Value
1	FIRST_NAME	Y		FIRST_NAME
2	MIDDLE_NAME	Y		MIDDLE_NAME
3	LAST_NAME	Y		LAST_NAME
4	PREFIX	Y		PREFIX
5	PHONE	Y		PHONE
6	FAX	Y		FAX
7	EMAIL	Y		EMAIL
10	CREATED_BY	Y			USER_ID
11	CREATION_DATE	Y			SYSDATE
12	LAST_UPDATE_DATE	Y			USER_ID
13	LAST_UPDATED_BY	Y			USER_ID
14	VENDOR_SITE_CODE	Y		VENDOR_SITE_CODE
15	ORG_ID	Y		ORG_ID

Pseudo-Code

The Procedure XXGE_VEN_IMP_PRC should be created with the following code to import dat file Data

transferring the same data to the staging tables based on specified conditions. The logic as follows:-

To Import  dat file Data into main tables

For  importing data SQLLoader  is to be used.  The following process is to be followed..

1) Write a control file to import Flatfile data into staging tables

The same steps shall be followed to populate all tables.

Code Logic:

 CREATE OR REPLACE PROCEDURE XXGE_VEN_IMP_PRC 

 IS

    

          Get default values for  ship_to_location_id,  bill_to_location_id,  ship_via_lookup_code,

           freight_terms_lookup_code, fob_lookup_code, terms_id,  invoice_currency_code, payment_currency_code,

           accts_pay_code_combination_id ,  hold_unmatched_invoices_flag , exclusive_payment_flag,            

           disc_taken_code_combination_id,pre_code_combination_id   following cursor should be used

            using “SELECT STATEMENT 1                                     ------  CURSOR STATEMENT1

           get the default vaules from  ap_system_parameters_all the  using

         “SELECT STATEMENT 2”                                                  -----  CURSOR STATEMENT2

For each of the headers above validate Supplier Site Info and Supplier Contacts Pre-Interface Table

Loop For All Lines Data

     Insert into AP_SUPPLIER_SITES_INT

        And    AP_SUP_SITE_CONTACT_INT

End Loop For All Lines Data

                                          

End Loop For All Headers Data

Run Oracle Standard Program Order Import To Load Data From Oracle Open Interface Table

Into Oracle Application Base Tables

   

      END

                               

SQL Statements

1.            

SELECT 

           ship_to_location_id

,  bill_to_location_id

,  ship_via_lookup_code

,  freight_terms_lookup_code

,  fob_lookup_code

,  terms_id

,  NVL(always_take_disc_flag, 'N') take_disc_flag

,  invoice_currency_code           inv_curr_code

,  payment_currency_code           pay_curr_code

,  accts_pay_code_combination_id   acc_pay_ccid

,  hold_unmatched_invoices_flag    hun_inv_flag

,  exclusive_payment_flag      exc_pay_flag

,  disc_taken_code_combination_id  disc_take_ccid

,  prepay_code_combination_id      pre_pay_ccid

FROM    financials_system_params_all

WHERE   org_id = p_org_id;

                                  

2.

SELECT

         NVL(auto_calculate_interest_flag,'N') auto_cal_flag

         ,auto_tax_calc_flag              auto_tax_flag

FROM

        apps.ap_system_parameters_all

WHERE     org_id = p_org_id;

3.

SELECT

organization_id

FROM hr_operating_units

WHERE       organization_id = p_org_id;

Validation Logic

Error Conditions:

Warning Conditions:

Incompatibility

None

Performance Considerations

Other Considerations

Restart Strategy

Crash Recovery

Integration Issues

Changes Required

Within Product

Custom menus

Other Products

No changes required

Shared Components

Note:The Oracle Application product short names listed below are examples only.  Update the shared component information to reflect the requirements of your customization.

INV

None

BOM

None

ENG

None

MPS/MRP

None

WIP

None

Alert conditions

None.

Incompatibilities

None.

Performance Issues

None.

Database Design

This section summarized new and changed database objects and data required to support <Subject>.  However, the complete database design is documented in the Database Extensions Design document.

Desired Table Changes

None

New/Updated Seed Data

Rows added to <App Prefix>_LOOKUPS:

Lookup Type	Code	Meaning

Descriptive Flexfields

Application:
Form Name:
Base Table:

Segment Name	Prompt	Size	Value Set	Default Value

Application:
Form Name:
Base Table:

Segment Name	Prompt	Size	Value Set	Default Value

Value Sets

Value Set	Size	Type	Rqd	Validation/Values
<App Prefix>_ITEM_SELECT	15	Char	Yes	ALL        All Items ONE        Specific Item
<App Prefix>_SPECIAL_CODE	10	Char	No	Code       Meaning From <App Prefix>­_LOOKUPS Where lookup_type = 'SPECIAL'

Grants/Synonyms

Owner	Object	Grantees
<App Prefix>	<App Prefix>_LOOKUPS	MFG INV

Archiving

No need to archive.

Database Diagram

No new tables

Tables, Indexes, Sequences

No new tables

Installation Requirements

Installation scripts must be prepared to perform the following actions in an automated way:

1.            Create new tables.

2.            Insert seed data into <App Prefix>_LOOKUPS as described above.

3.            Run grant/synonym script.

4.            Define Value Sets and Validation Tables.

5.            Define Descriptive Flexfields.

6.            Define Help text.

7.            Define Message text.

8.            Register Forms.

9.            Register Concurrent Programs.

10.         Register Standard Report Submission parameters.

11.         Create Menus.

Note:Add to or modify this list as appropriate.  Provide additional details where necessary to facilitate the creation of the installation routines.

Implementation Notes

Note:Complete this section after completing the <Subject> customization.  Update the design to reflect any changes made in the course of development and describe how the customization was implemented below.

This document describes exactly how <Subject> was developed and implemented at <Company Long Name>.

Design Summary

Functional Design

Functional Design Approval

Technical Design

Design Review

Final Acceptance

Coding Summary

Development was done on <description of hardware>, to work with Release <Applications Release> of Oracle Applications.  All coding follows the standards defined in the Build Standards document for <Project Name>.

Program Files

The files required for this customization are as follows:

File	Description	Coded By

Testing Summary

The customizations were tested in <Company Short Name>'s test environment before being moved to production.  See the Link Test scripts for more information.

Installation

All modules are installed as if part of a separate product.  A UNIX directory structure is created under $APPL_TOP as shown in the following example:

                        $APPL_TOP

                             |

                           <Application Short Name>

                             |

                           1.0.0

       ___________________________|___________________________________

      |       |   |     |       |       |        |    |     |

     forms    bin    src    sql     log     out      srw     install   sql

The directories contain the components of the enhancement as shown below (directories not listed are empty).  All future customizations will also be stored in these directories.

                  forms/

                        <Formname>

                  bin/

                  src/

                  sql/

                  srw/

                  install/sql/

Note:Update the  example directory structure above as appropriate for your project.  Fill in the filenames under the appropriate directories.

The forms and concurrent programs are registered in Application Object Library under the <Application Short Name> application.

A new set of menus (owned by <Application Short Name>) is created to call the new forms as follows:

                        Navigate
                                        Sub-menu
                                                        Sub-menu

Note:Indicate the specific menu options added to access new forms.

Open and Closed Issues for this Deliverable

Note:Add open issues that you identify while writing or reviewing this document to the open issues section.  As you resolve issues, move them to the closed issues section and keep the issue ID the same.  Include an explanation of the resolution.

When this deliverable is complete, any open issues should be transferred to the project- or process-level Risk and Issue Log (PJM.CR.040) and managed using a project level Risk and Issue Form (PJM.CR.040).  In addition, the open items should remain in the open issues section of this deliverable, but flagged in the resolution column as being transferred.

Open Issues

ID	Issue	Resolution	Responsibility	Target Date	Impact Date

Closed Issues

ID	Issue	Resolution	Responsibility	Target Date	Impact Date