ERP2

56

INTERFACES:

It is nothing but a program; it will be used to transfer the data from flat file to data

base table or from data base table to flat file. Two types of interfaces given below.

1. Outbound Interface

2. Inbound Interface

Outbound Interface: it will be used to transfer the data from oracle apps base table to

legacy system flat file. We will develop a concurrent program, which is pl/sql procedure.

Inside of that we will use UTL_FILE package to generate the flat file.

UTL_FILE: it is one of the pl/sql package, which will be used to transfer the data from

oracle data base table to flat files or from flat files to oracle data base tables. But we will

use sql loader to transfer data from flat file to data base tables .

By using following 3 functions we will generate flat file

1. Declare the file pointer to find out file location.

UTL_FILE.FILE_TYPE;

2. To open or create file.

UTL_FILE.FOPEN(‘PATH’,’FILENAME’,’W’);

3. To transfer data into file

UTL_FILE.PUT_LINE(FILE POINTER, COLUMN NAME);

4. To close file

UTL_FILE.FCLOSE(FILE POINTER);

STEPS:

1. Develop the pl/sql program.

2. Define the cursor to get the data from the data base tables.

3. Open the cursor and open the file by using UTL_FILE package.

4. Transfer the data from cursor variable into flat files by using UTL_FILE.

5. Close the file by using UTL_FILE package.

6. Register the program as concurrent program.

7. This program is scheduled at SRS window.

Ex: Create or replace procedure inventoryout (errbuf out varchar2,

Retcode out varchar2) as

Cursor cur_inv is select inventory_item_id,organization_id,segment1,

description from mtl_system_items_b;

Outpointer utl_file.file_type;

Begin

Outpointer:=utl_file.fopen('c:\temp','inventoryflat.txt','w');

-- To get the path:

-- Select value from V$parameter where name like ‘utl_file_dir’;

For c in cur_inv

57

loop

Utl_file.put_line(outpointer,c.inventory_item_id||'-'||c.organization_id||'-'||

c.segment1 ||'-'||c.description);

end loop;

utl_file.fclose(Outpointer);

exception

when others then

fnd_file.put_line(fnd_file.log,'error while generating flat file');

end inventoryout;

UTL_FILE EXCEPTIONS:

1. UTL_FILE.INVALID_OPERATION

2. UTL_FILE.INVALID_PATH

3. UTL_FILE.INVALID_MODE

4. UTL_FILE.INVALID_FILEHANDLE

5. UTL_FILE.READ_ERROR

6. UTL_FILE.INTERNAL_ERROR

INIT.ORA: This file contains the accessible directories list.

Inbound Interface or Conversion: it will be used to upload the data from legacy

system flat file to oracle apps base table. For this we will use SQL LOADER program and

pl/sql program.

Ex: -- To load data from flat file to Tables using SQLLDR

-- Writing the Control File CS_CTL.CTL

LOAD DATA

INFILE ‘C:\CHAITU_CS.CSV’

BADFILE ‘CS_BAD.DAT’ -- Naming the BAD FILE

DISFILE ‘CS_DISC.DAT’ -- Naming the DISCARD FILE

INSERT INTO TABLE CS_0016

FIELDS TERMINATED BY ‘,’

TRAILING NULCOLS

(

COL1,

COL2,

COL3

)

-- SQLLDR SYNTAX

CMD> SQLLDR APPS/APPS@PROD CONTROL = CS_CTL

58

SQL Loader: This is a CUI (Character User Interface) Tool, which is used to load data

from flat files to tables. We have to feed control file to the SQL Loader this control file

contains information about the flat file and into which table needs to be loaded.

Syntax:

LOAD DATA

INFILE <FILENAME>

[INSERT / REPLACE / TRUNCATE / APPEND]

INTO TABLE <TABLENAME>

FIELDS TERMINATED BY ‘ , ’

OPTIONALLY ENCLOSED ‘ “ ‘

TRIALING NULLCOLLS

WHERE ---[CONDITION]

Filler Clause: The filler clause is used to skip one column data in the flat file.

Ex: -- Example using all functions in the control file

-- Create a Table ABC with 4 cols

Create table ABC(Col1 number,

Col2 varchar2(10)

Col3 number,

Col4 varchar2(10),

Col5 varchar2(10),

Col6 date);

-- Table Created

-- Create the Control File with the name mycontrol.ctl

LOAD DATA

INFILE ‘C:\NEW.CSV’

INSERT

INTO TABLE ABC

(COL1, Sequencename.nextval, _ getting the sequence number

COL2, “upper(:col2)”

COL3, constant ‘ABC’ _ it fills the each row with the constant value

COL4, NULLIF COL4= ‘EX’ _ if there is no value then it is filled with the

COL5, FILLER, _ to skip the column

COL6, “to-date(:col4, ‘dd-mm-yyyy’)); _ to print the date.

After completion of the control file save it as .ctl extension and to execute the control file

go to the command prompt in UNIX or WINDOWS use the following syntax.

59

Syntax:

Prompt> SQLLDR USERNAME/PASSWORD@HOSTNAME

CONTROL = CONTROL FILE NAME

[DIRECT = TRUE / FALSE [DEFAULT]

SKIP = NUMBER

ROWS = NUMBER

ERRORS = NUMBER]

Ex: -- Executing the control file mycontrol.ctl

--Prompt> SQLLDR USERNAME/PASSWORD@HOSTNAME

CONTROL = mycontrol.ctl

In the execution there are more options, which are stated below. And those options

we give after the CONTROL in the SQLLDR are optional according to our requirement we

will use them.

Conventional Path: It is the default path which will create insert statements and it will

insert data into table method is very slow. Here the auto commit will be carried for every

64 records.

Direct Path: This will be used when ever the volume of data is high. It will make the

entire data into set of blocks and it will create one insert statement for each block and

insert data into tables. This method is very fast also parallel loading will be working while

working with direct path. By default it will be false.

Skip: Used to skip the rows from flat file while loading the data into tables.

Rows: In conventional path the commit will happen for every 64 records by default if we

want to make it to 1000 EX: ROWS = 1000

Errors: This indicates that how many no of errors can be tolerated while loading data

into tables. The default value is 50.

As a part of the execution the SQLLDR creates three files according to the output type

those are given below.

1. Bad File: Which will be created whenever there are records, which are rejected

while inserting into the table from the flat file?

2. Discard File: It will be created whenever there are records which are not

satisfying the filtering criteria specified in the where clause of the control file.

3. Log File: This file contains the complete information of data it will be created at

all times when ever we fire SQLLDR.

60

Conversion: Conversion is a preimplementation load which will transfer the data from

the legacy system to the oracle apps system. After the completion of load we will drop

all the objects which we have used for the conversion.

Conversion Process:

1. We will get the flat file to convert, based on the flat file we will generate the

temporary file and by using SQLLDR we transfer data from flat file to temporary

table.

2. Create staging table, which is replica of interface table provided by oracle.

3. Develop a package which have different program units for validation and moving to

interface tables

4. Run the validation procedure to validate the data which has been loaded into

staging table. Use extra cols in a staging table ERRFLAG and MESSAGETEXT that

are used to identify valid and invalid records with respective error messages.

5. We transfer the successful records into interface table and change the ERRFLAG

status from V(Validated) to M(Moved)

· ERRFLAG _ E _ ERROR

· ERRFLAG _ V _ VALIDATED

· ERRFLAG _ M _ MOVED

6. As we are working with open interface we will run the import program provided by

oracle used to pull the data from interface table and load into base table.

7. If any records got failed while importing these will exist under oracle defined errors

tables.

Conversion Process (Brief):

1. Based on the flat file provided write the control file to transfer the data into staging

tables.

2. Validate the data that has been loaded into staging tables.

3. Insert the validated records into interface tables.

4. This is an open interface we run the import program to import the data into base

tables.

Interface: Interface is a post implementation load which will be helpful to import the

data after golive.

EX: ARLOCKBOX interface

The object that we have developed for this interface will be registered in the oracle

applications.

61

Types of Interfaces: There are two types of interfaces.

1. Open Interface.

2. Custom Interface.

Open Interface: Open interface is nothing but it provides interface tables and import

programs to import data into base tables.

Customer Interface: Whenever oracle has not provided any import programs or API to

import data into base tables we call it as a custom interface.

Interface Process:

1. Based on the flat file w e will develop the control file and insert the data into

staging table.

Note: Place the control file in the bin directory of your custom application directory.

2. Register that in oracle 11i applications by taking execution method as

SQL * LOADER we develop the packages which will be having procedures like

validate the staging table, moving the validated data to the interface tables, run

import program after that register that package in oracle 11i applications

Note: While transferring the data into base tables we pass three extra cols those are given

below.

_ Process_flag (Number)

_ Transaction_type (Varchar2)

_ Set_process_id

Process_flag: To check the status of the row which we fetched into the table. We pass

different values that indicate the status of the row those are given below.

_ 1 _ To be process

_ 2 _ Import Success

_ 3 _ Error

_ 4 _ Validation Under Process

_ 7 _ Validation Successful.

Transaction_type: This is used to identify which transaction was done those are CREATE,

UDATE.

Set_process_id: It is used to split the no. of becords to use memory correctly without

getting any SGA MEMORY error.

Note: We can’t load more than one organization records at a time. First we have to load the

master organization and then child organization.

62

Item Conversion:

1. We transfer the data from flat file to the staging table.

2. Validate the data, which has been loaded into the staging table.

_ Check the ITEM_NUMBER is existing or not.

_ Check whether the ORG_CODE is existing or not.

_ Description should not be NULL

_ Template name should be validated from MTL_ITEM_TEMPLATES table.

_ Validate the PLANNERS_CODE from MTL_PLANNERS table.

_ Validate the HAZARDS_CLASS from PO_HAZARD_CLASSES table.

3. After doing the above validation if the record got successfully validated we will set

the record status to ‘V’ else ‘E’ with appropriate error message.

4. Transfer the validated data from staging to interface table.

(MTL_SYSTEMS_INTERFACE).

5. As we are working with open interface we will run the standard concurrent program

to import the data from interface table to base table. Before running this import

program we have to set the organization to which we are loading the items. The

program will ask for six parameters. The navigation is Import _ Import Items in

the Inventory responsibility.

All Organizations: If we go with YES it will load the same item into all organizations else

it will load into a specific organization.

63

Validate Items: If this is YES then import program revalidate the data existing in the

interface table.

Process Items: If this is YES after validation it will load the validated records into

MTL_SYSTEM_ITEMS_B table.

Delete Processed Rows: If this is yes the successfully imported records will be deleted

from the interface table.

Process Set(NULL for ALL): This is the only parameter which is not mandatory. What

ever value will be provided compared with the SET_PROCESS_ID column in the

MTL_SYSTEMS_ITEM_INTERFACE table. The main purpose of this table is to make or

spread the data into batch which makes import easy. If we provide NULL for the

irrespective of SET_PROCESS_ID then it will pull all the records for the process.

Create or Update Items: If it is 1 it will check the records in the interface table whose

transaction type is CREATE and pull those records for import. If we provide 2 it means

we are trying to UPDATE the existing items. So it will pull the records whose transaction

type is UPDATE.

_ After the submission of the request and when it got completed to identify the load

status of records check with the PROCESS_FLAG column in the

MTL_SYSTEM_ITEMS_INTERFACE. We discuss the different status of the process

flag in the above discussion.

General Errors:

_ The general error that occur when we are dealing with the Item Conversions that

is Master-Child Conflict this error will occur when ever we are trying to load

child org with out loading to the master.

_ Items cannot have pick the components = “YES” when ever the MRP planning

method is not defined.

Note:

_ To find the records which got erroredout and their proper messages oracle provided

MTL_INTERFACE_ERRORS table. The join between this interface table and error table is

TRANSACTION_ID.

_ To set the master items to the child organizations also then we have to go with the

Master Items then retrieve the item and go with the Organization Assignment button on

the top left side.

64

Conversion Script for Item Load:

/*-------------------------------------------------------------------------------------------------

BIAS TECHNOLOGIES INC, INDIA

File Name : CS_item_load_structures.sql

Author : PHANI BHUSHAN REDDY

Created Date : 03-june-06

Version : 1

Purpose : The Purpose of this script is to create the temporary table(s)

Version Modified By Modified Date Purpose

---------------------------------------------------------------------------------------------------

1.1

-------------------------------------------------------------------------------------------------*/

-- Temporary Table

DROP TABLE CS_ITEM_LOAD_TEMP;

CREATE TABLE CS_ITEM_LOAD_TEMP

(

ITEM_NUMBER VARCHAR2(30)

,DESCRIPTION VARCHAR2(240)

,TEMPLATE_NAME VARCHAR2(30)

,ORG_CODE VARCHAR2(10)

);

-- Staging Table

CREATE TABLE CS_ITEM_LOAD_STG

AS

SELECT *

FROM MTL_SYSTEM_ITEMS_INTERFACE

WHERE 1=0;

ALTER TABLE CS_ITEM_LOAD_STG

ADD

(VALID_STATUS VARCHAR2(3)

,ERR_MSG VARCHAR2(4000)

);

65

/*-------------------------------------------------------------------------------------------------

BIAS TECHNOLOGIES INC, INDIA

File Name : CS_item_load.ctl

Author : PHANI BHUSHAN REDDY

Created Date : 03-june-06

Version : 1

Purpose : The Purpose of this control file is to import

Data from flat file to table.

Version Modified By Modified Date Purpose

---------------------------------------------------------------------------------------------------

1.1

-------------------------------------------------------------------------------------------------*/

LOAD DATA

INFILE 'C:\ITEM_DATA.CSV'

INSERT INTO TABLE CS_ITEM_LOAD_TEMP

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED '"'

TRAILING NULLCOLS

(ITEM_NUMBER,

DESCRIPTION,

TEMPLATE_NAME,

ORGANIZATION_CODE

)

Note: This is the Control file using the SQLLDR we execute the control file to move the data from

flat file to staging table.

66

/* ------------------------------------------------------------------------------------------------

BIAS TECHNOLOGIES INC, INDIA

File Name : CS_item_load_pkg.sql

Author : PHANI BHUSHAN REDDY

Created Date : 03-june-06

Version : 1

Purpose : the puruose of this package is to import items data.

Move_to_staging

this procedure is used to insert records

from temp table into staging table

Validate_staging

This procedure is used to validate the date which has

been moved to staging table. The valid records

having the valid_status to 'V' other wise 'E'.

Move_to_iface

The record which through with the validations

(valid_status ='V') will be transferred to interface

table(MTL_SYSTEM_ITEMS_INTERFACE).

Version Modified By Modified Date Purpose

---------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------*/

--Package Specification

CREATE OR REPLACE PACKAGE CS_item_load_pkg

AS

PROCEDURE move_to_staging;

PROCDURE validate_staging;

PROCEDURE move_to_iface;

END CS_item_load_pkg;

67

--Package Body

CREATE OR REPLACE PACKAGE BODY CS_item_load_pkg

AS

-- Procedure to move_to_staging

PROCEDURE move_to_staging

IS

i number;

BEGIN

-- Inserting data into staging table

INSERT INTO CS_item_load_stg

(

segment1 -- item_number

,description -- description

,template_name -- template_name

,organization_code -- org_code

,set_process_id

)

SELECT

item_number

,description

,template_name

,org_code

,1

FROM

CS_item_load_temp;

DBMS_OUTPUT.PUT_LINE(' Total Reocrd(s) inserted '|| SQL%ROWCOUNT );

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(' ERROR => ' || SQLERRM);

END move_to_staging;

--Procedure to validate the data

PROCEDURE validate_staging

AS

l_err_flag number:=0;

l_err_msg varchar2(1000);

l_count number;

CURSOR c is select a.rowid row_id , a.SEGMENT1, A.DESCRIPTION, a.template_name ,

a.organization_code

from CS_item_load_stg a

where nvl(VALID_STATUS,'T') in ('T','E');

BEGIN

FOR c_rec in c

LOOP

BEGIN

--- Initialize the variables

l_err_flag := 0;

l_err_msg := NULL;

68

--Validate the item number

l_count := 0;

select count(*) into l_count

from MTL_SYSTEM_ITEMS_B

where segment1 = c_rec.segment1;

IF l_count > 0 THEN

l_err_flag := 1;

l_err_msg := '**Invalid Item number ';

END IF;

-- Validate the Description

if c_rec.description is null then

l_err_flag := 1;

l_err_msg := l_err_msg || '**Invalid Description ';

END IF;

--Validate the Template Name

l_count := 0;

select count(1) into l_count

from mtl_item_templates

where template_name = c_rec.template_name;

IF l_count = 0 then

l_err_flag := 1;

l_err_msg := l_err_msg || '**Invalid Template name ';

END IF;

--Validate the Organization Code

l_count := 0;

select count(1) into l_count from org_organization_definitions

where organization_code = c_rec.organization_code;

if l_count = 0 then

l_err_flag := 1;

l_err_msg := l_err_msg || '**Invalid Organization code ';

end if;

-- check the valid status to update the row status

if l_err_flag = 1 then

update CS_item_load_stg

set valid_status ='E'

,err_msg = l_err_msg

where

rowid = c_rec.row_id;

else

update CS_item_load_stg

set valid_status ='V'

where

69

rowid = c_rec.row_id;

end if;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END LOOP;

dbms_output.put_line(' Validatin completed successfully ');

END validate_staging;

--PROCEDURE

PROCEDURE move_to_iface

AS

i number;

BEGIN

insert into mtl_system_items_interface

(

segment1

,description

,template_name

,organization_code

,process_flag -- hard coding to 1

,transaction_type -- hard code to CREATE

,set_process_id

)

SELECT

segment1

,description

,template_name

,organization_code

,1

,'CREATE'

,1

from CS_item_load_stg

where valid_status ='V';

dbms_output.put_line(' Total Record(s) inserted ' || sql%rowcount);

exception

when others then

dbms_output.put_line( ' Error => '|| sqlerrm);

END move_to_iface;

END CS_item_load_pkg;

70

Item Categories:

1. Convert the data from flat file to staging table.

2. Validate the data which has been loaded into staging.

_ Validate the ITEM_NUMBER whether it exists or not from

MTL_SYSTEM_ITEMS_B table.

_ Validate the organization from ORG_ORGANIZATION_DEFINITIONS

_ Validate the CATEGORY_SET from MTL_CATEGORY_SETS_B and

MTL_CATEGORY_SET_TL.

_ Validate the CATEGORY_CODE from MTL_ITEM_CATEGORIES_INTERFACE

table.

3. As we are working with open interface we have to run this import

ITEM_CATEGORY_ASSIGNMENTS program to import assignment data from

interface table (MTL_ITEM_CATEGORIES).

Transaction Types:

Create: Create used to create new item category assignment.

Delete: Delete used to delete the default category assignments.

Update: This is a new transaction type come up with the 11.5.10 version of oracle

applications. Instead of delete or create the new assignments we can update default

category value or category code.

Note: The validation process for the item categories script was given below as the step by step

process.

Customer Items: Customer items are the items how the customer will identify our

items.

Items Relationship: Item relationship which is used to maintain some relationships

with item, with relationship types, with related substitute and service.

71

Conversion Script for Item Categories:

/*-------------------------------------------------------------------------------------------------

BIAS TECHNOLOGIES INC, INDIA

File Name : CS_create_structures.sql

Author : PHANI BHUSHAN REDDY

Created Date : 03-june-06

Version : 1

Purpose :

Used to create the table structures.

Version Modified by Modified Date Purpose

---------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------*/

DROP TABLE CS_ITEM_CAT_LOAD_TEMP;

CREATE TABLE CS_ITEM_CAT_LOAD_TEMP

(

ITEM_NUMBER VARCHAR2(30)

,ORG_CODE VARCHAR2(10)

,CAT_SET_NAME VARCHAR2(30)

,CAT_CODE VARCHAR2(20)

);

DROP TABLE CS_ITEM_CAT_LOAD_STG;

CREATE TABLE CS_ITEM_CAT_LOAD_STG

AS

SELECT * FROM MTL_ITEM_CATEGORIES_INTERFACE

WHERE 1=0 ;

ALTER TABLE CS_ITEM_CAT_LOAD_STG

ADD

( VALID_STATUS VARCHAR2(10)

, ERR_MSG VARCHAR2(2000)

);

72

/*-------------------------------------------------------------------------------------------------

BIAS TECHNOLOGIES INC, INDIA

File Name : CS_sqlldr.ctl

Author : PHANI BHUSHAN REDDY

Created Date : 03-june-06

Version : 1

Purpose :

Used to create the table structures.

Version Modified by Modified Date Purpose

---------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------*/

LOAD DATA

INFILE ‘C:\ITEM_CAT_DATA.CSV'

INSERT

INTO TABLE CS_ITEM_CAT_LOAD_TEMP

FIELDS TERMINATED BY ','

(

ITEM_NUMBER

,ORG_CODE

,CAT_SET_NAME

,CAT_CODE

)

Note: This is the Control file using the SQLLDR we execute the control file to move the data from

flat file to staging table.

73

/*-------------------------------------------------------------------------------------------------

BIAS TECHNOLOGIES INC, INDIA

File Name : CS_ITEM_CAT_LOAD_PKG.pks

Author : PHANI BHUSHAN REDDY

Created Date : 03-june-06

Version : 1

Purpose :

Package Specification

-------------------------------------------------------------------------------------------------*/

CREATE OR REPLACE PACKAGE CS_ITEM_CAT_LOAD_PKG

AS

-- Procedure to transfer the data from Temp table to Staging table

PROCEDURE move_to_staging;

-- Procedure to validate the data

PROCEDURE validate_data;

-- Procedure to Transfer the validated data from staging to interface table

PROCEDURE move_to_iface;

END CS_ITEM_CAT_LOAD_PKG;

--Package Body

CREATE OR REPLACE PACKAGE BODY CS_ITEM_CAT_LOAD_PKG

AS

-- Procedure to transfer the data from Temp table to Staging table

PROCEDURE move_to_staging

IS

BEGIN

insert into CS_item_cat_load_stg

(ITEM_NUMBER -- ITEM NUMBER

,ORGANIZATION_CODE -- ORG_CODE

,CATEGORY_SET_NAME -- CAT_SET_NAME

,CATEGORY_NAME -- CAT_CODE

,SET_PROCESS_ID -- HARDCODE TO 1

)

SELECT

ITEM_NUMBER

,ORG_CODE

,CAT_SET_NAME

,CAT_CODE

,1

FROM CS_ITEM_CAT_LOAD_TEMP;

DBMS_OUTPUT.PUT_LINE(' Total Number of Record(s) Inserted '

||sql%rowcount);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(' ERROR => '|| SQLERRM);

END move_to_staging;

74

--Procedure to Validate the Data

PROCEDURE validate_data

IS

l_err_flag number ;

l_err_msg varchar2(2000);

l_count number;

Cursor c is select a.rowid row_id

,a.item_number

,a.category_set_name

,a.category_name

,a.organization_code

from

CS_item_cat_load_stg a

where

nvl(valid_status,'T') in ('T','E');

BEGIN

FOR c_rec in C

loop

l_err_flag := 0;

l_err_msg := NULL;

l_count := 0;

-- Validate the Item Number

select count(1) into l_count

from mtl_system_items_b msi,

ORG_ORGANIZATION_DEFINITIONS ood

where

msi.organization_id = ood.organization_id

and ood.organization_code = c_rec.organization_code

and msi.segment1 = c_rec.item_number;

if l_count = 0 then

l_err_flag := 1;

l_err_msg := ' ** Item not existed ';

end if;

-- Validate the category set name

l_count := 0;

select count(1) into l_count

from mtl_category_sets

where category_set_name = c_rec.category_set_name;

if l_count = 0 then

l_err_flag := 1;

l_err_msg := l_err_msg || ' ** Category Set name is not valid ';

end if;

--Validate the Category Code

l_count := 0;

select count(1) into l_count

from mtl_categories mc,

mtl_category_sets mcs

where

mcs.category_set_name = c_rec.category_set_name

and mc.segment1 = c_rec.category_name;

if l_count = 0 then

75

l_err_flag := 1;

l_err_msg := l_err_msg || ' ** Category name is not valid ';

end if;

if l_err_flag = 1 then

update CS_item_cat_load_stg

set err_msg = l_err_msg,

valid_status = 'E'

where rowid = c_rec.row_id;

else

update CS_item_cat_load_stg

set valid_status ='V'

where rowid = c_rec.row_id;

end if;

end loop;

DBMS_OUTPUT.PUT_LINE(' Total Number of Record(s) Validated '

||sql%rowcount);

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(' Error => '|| sqlerrm);

END validate_data;

-- Procedure to Transfer the validated data from staging to interface table

PROCEDURE move_to_iface

is

begin

insert into mtl_item_categories_interface

(

item_number

,organization_code

,category_set_name

,category_name

,set_process_id

,process_flag

,transaction_type

)

select

item_number

,organization_code

,category_set_name

,category_name

,1

,1

,'CREATE'

from

CS_item_cat_load_stg

where valid_status ='V';

DBMS_OUTPUT.PUT_LINE(' Total Record(s) inserted '|| Sql%rowcount);

exception

when others then

dbms_output.put_line(' error => ' || sqlerrm);

end move_to_iface;

END CS_ITEM_CAT_LOAD_PKG;

76

Item Attachments:

Flat file contains the data like item_number, organization_id, document_category,

datatype, sequence, attachment.

Validations:

_ Validate the Item is exists with respective organization or not.

_ Validate the document category from FND_DOCUMENT_CATEGORIES_TL.

_ Validate the datatype from FND_DOCUMENT_DATATYPES.

_ Validate the Sequence which should not be NULL

As there are no interface tables we will load the validated data to the base tables

directly. For that we have to write the procedure.

77

/*-------------------------------------------------------------------------------------------------

BIAS TECHNOLOGIES INC, INDIA

File Name : BIAS_ITEM_ATTCH.pks

Author : PHANI BHUSHAN REDDY

Created Date : 03-june-06

Version : 1

Purpose :

Package Specification and Package Body.

-------------------------------------------------------------------------------------------------*/

-- PACKAGE SPECIFICATIONS: BIAS_ITEM_ATTCH

CREATE OR REPLACE PACKAGE BIAS_ITEM_ATTCH AS

PROCEDURE MOVE_TO_STAGING;

PROCEDURE VALIDATE_STAGING;

PROCEDURE LOAD_TO_BASE;

END BIAS_ITEM_ATTCH;

-- PACKAGE BODY: BIAS_ITEM_ATTCH

CREATE OR REPLACE PACKAGE BODY BIAS_ITEM_ATTCH AS

--PROCEDURE MOVE_TO_STAGING

PROCEDURE MOVE_TO_STAGING

AS

BEGIN

INSERT INTO BIAS_ITEM_ATTCH_STG

(

ITEM_NUMBER,

ORGANIZATION,

DOCUMENT_CATEGORY,

DATA_TYPE,

ATTACHMENT

)

SELECT

ITEM_NUMBER,

ORGANIZATION,

DOCUMENT_CATEGORY,

DATA_TYPE,

ATTACHMENT

FROM

BIAS_ITEM_ATTCH_TEMP A

WHERE

NOT EXISTS(

SELECT 1 FROM BIAS_ITEM_ATTCH_STG B

WHERE

A.ITEM_NUMBER = B.ITEM_NUMBER AND

A.ORGANIZATION = B.ORGANIZATION);

78

DBMS_OUTPUT.PUT_LINE('ROW(S) INSERTED'|| SQL%ROWCOUNT);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR: '|| SQLERRM);

END MOVE_TO_STAGING;

-- PROCEDURE TO VALIDATE_STAGING

PROCEDURE VALIDATE_STAGING

AS

L_VALID_ROW NUMBER;

L_ERR_MSG VARCHAR2(1000);

L_COUNT NUMBER;

L_ITEM_ID NUMBER;

L_ORG_ID NUMBER;

L_CATEGORY_ID NUMBER;

L_DATATYPE_ID NUMBER;

CURSOR C1 IS SELECT A.ROWID ROW_ID, A.* FROM

BIAS_ITEM_ATTCH_STG A WHERE NVL(A.STATUS,'T')='T';

BEGIN

FOR C IN C1

LOOP

L_VALID_ROW:=0;

L_ERR_MSG:=NULL;

-- VALIDATE ITEM TO ORGANIZATION

BEGIN

SELECT A.INVENTORY_ITEM_ID, B.ORGANIZATION_ID

INTO L_ITEM_ID, L_ORG_ID

FROM MTL_SYSTEM_ITEMS_B A,

ORG_ORGANIZATION_DEFINITIONS B

WHERE

A.ORGANIZATION_ID =B.ORGANIZATION_ID AND

B.SEGMENT1 = C.ITEM_NUMBER AND

B.ORGANIZATION_CODE = C.ORGANIZATION;

EXCEPTION

WHEN OTHERS THEN

L_VALID_ROW:=1;

L_ERR_MSG:='INVALID ITEM NUMBER TO ORGANIZATION';

END;

---VALIDATE DOCUMENT_CATEGORY

BEGIN

SELECT CATEGORY_ID INTO L_CATEGORY_ID

FROM FND_DOCUMENT_CATEGORIES_TL

WHERE USER_NAME = C.DOCUMENT_CATEGORY;

EXCEPTION

WHEN OTHERS THEN

L_VALID_ROW:=1;

L_ERR_MSG:=L_ERR_MSG || ' INVALID DOCUMENT_CATEGORY';

END;

79

--VALIDATE DATATYPE

BEGIN

SELECT DATATYPE_ID INTO L_DATATYPE_ID

FROM FND_DOCUMENT_DATATYPES

WHERE USER_NAME=C.DATA_TYPE;

EXCEPTION

WHEN OTHERS THEN

L_VALID_ROW:=1;

L_ERR_MSG:=L_ERR_MSG || ' INVALID DATA_TYPE';

END;

IF L_VALID_ROW=1 THEN

UPDATE BIAS_ITEM_ATTCH_STG

SET STATUS='E',

ERR_MSG=L_ERR_MSG

WHERE ROWID=C.ROW_ID;

ELSE

UPDATE BIAS_ITEM_ATTCH_STG

SET STATUS='V',

CATEGORY_ID=L_CATEGORY_ID,

DATA_TYPE_ID=L_DATATYPE_ID,

ITEM_ID=L_ITEM_ID,

ORGANIZATION_ID=L_ORG_ID,

WHERE ROWID=C.ROW_ID;

END IF;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);

END VALIDATE_STAGING;

---PROCEDURE LOAD_TO_BASE

PROCEDURE LOAD_TO_BASE(P_ITEM_NUMBER IN VARCAHR2 DEFAULT NULL)

AS

CURSOR C1 IS SELECT A.ROWID ROW_ID, A.* FROM BIAS_ITEM_ATTCH_STG A

WHERE STATUS='V' AND

A.ITEM_NUMBER=NVL(P_ITEM_NUMBER,A.ITEM_NUMBER);

L_DOC_ID NUMBER;

L_ATT_DOC_ID NUMBER;

L_MEDIA_ID NUMBER;

L_ERR_MSG VARCHAR2(255);

BEGIN

FOR C IN C1

LOOP

BEGIN

SELECT FND_DOCUMENTS_S.NEXTVAL INTO L_DOC_ID FROM DUAL;

INSERT INTO FND_DOCUMENTS

(

DOCUMENT_ID,

DATATYPE_ID,

CATEGORY_ID,

CREATION_DATE,

CREATED_BY,

LAST_UPDATED_DATE,

LAST_UPDATED_BY,

SECURITY_TYPE,

80

PUBLISH_FLAG,

USAGE_TYPE

)

VALUES

(

L_DOC_ID,

L_DATATYPE_ID,

L_CATEGORY_ID,

SYSDATE,

1007919,

SYSDATE,

1007919,

1,

'Y',

'O'

);

SELECT FND_DOCUMENTS_SHORT_TEXT_S.NEXTVAL INTO L_MEDIA_ID FROM DUAL;

INSERT INTO FND_DOCUMENTS_TL

(

DOCUMENT_ID,

CREATION_DATE,

LAST_UPDATE_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_LOGIN,

LANGUAGE,

SOURCE_LANG,

MEDIA_ID

)

VALUES

(

L_DOC_ID,

SYSDATE,

-1,

SYSDATE,

-1,

-1,

'US',

'US',

L_MEDIA_ID

);

SELECT FND_ATTACHED_DOCUMENTS_S.NEXTVAL INTO L_ATT_DOC_ID FROM DUAL;

INSERT INTO FND_ATTACHED_DOCUMENTS

(

ATTACHED_DOCUMENT_ID,

DOCUMENT_ID,

CREATION_DATE,

CREATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATED_BY,

LAST_UPDATE_LOGIN,

SEQ_NUM,

ENTITY_NAME,

AUTOMATICALLY_ADDED_FLAG,

PK1_VALUE,

PK2_VALUE

)

VALUES

81

(

L_ATT_DOC_ID,

L_DOC_ID,

SYSDATE,

-1,

SYSDATE,

-1,

-1,

2,

'MTL_SYSTEM_ITEMS',

'N',

C.ORGANIZATION_ID,

C.ITEM_ID

);

INSERT INTO FND_DOCUMENTS_SHORT_TEXT

(

MEDIA_ID,

SHORT_TEXT

)

VALUES

(

L_MEDIA_ID,

C.ATTACHEMENT

);

COMMIT;

UPDATE BIAS_ITEM_ATTCH_STG

SET STATUS='P'

WHERE

ROWID=C.ROW_ID;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);

ROLLBACK;

L_ERR_MSG:='ERROR: '||SQLERRM;

UPDATE BIAS_ITEM_ATTCH_STG

SET STATUS='E',

ERR_MSG=L_ERR_MSG

WHERE

ROWID=C.ROW_ID;

END;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR: ' || SQLERRM);

END LOAD_TO_BASE;

END BIAS_ITEM_ATTCH;

82

Some Important Tables in Different Modules: Inventory (INV)

MTL_SYSTEM_ITEMS_B MTL_CATEGORY_SETS_B

MTL_ITEM_CATEGORIES MTL_SECONDARY_LOCATORS

MTL_RELATED_ITEMS MTL_ONHAND_QUANTITIES

CST_ITEM_COST MTL_PARAMETERS

Order Management (OM):

OE_ORDER_HEADERS_ALL OE_ORDER_LINES_ALL

OE_ORDER_HOLDS_ALL OE_TRANSACTION_TYPES_TL

WSH_DELEVERY_DETAILS WSH_NEW_DELEVERIES

WSH_DELEVERY_ASSIGNMENTS

Account Receivables (AR):

RA_CUSTOMER_TRX_ALL RA_CUSTOMER_TRX_LINES_ALL

RA_CUSTOMER_TRX_TYPE_ALL RA_CUSTOMER_TRX_LINE_GL_DIST_ALL

RA_CUSTOMERS RA_TERMS

HZ_PARTIES HZ_PARTY_SITES

HZ_CUST_ACCOUNTS HZ_CUST_ACCOUNT_SITES_ALL

HZ_LOCATIONS HZ_CUST_SITE_USES_ALL

HZ_CONTACT_POINTS HZ_CUST_CONTACT_POINTS

Quality Pricing (QP):

QP_LIST_HEADERS_ALL QP_LIST_LINES

QP_PRICING_ATTRIBUTES QP_QUALIFIERS

QP_PRICING_PHASES

Purchase Order (PO):

PO_REQUISITION_HEADERS_ALL PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL PO_HEADERS_ALL

PO_LINES_ALL PO_DISTRIBUTIONS_ALL

PO_VENDORS PO_VENDOR_SITES_ALL

PO_VENDOR_CONTACTS PER_ALL_PEOPLE_F

General Ledger(GL):

GL_PERIODS GL_JE_BATCHES

GL_INTERFACE GL_JESOURCES

GL_JE_HEADERS GL_SET_OF_BOOKS

GL_JE_LINES

Note: These are some of the tables we use regularly in the modules and there are many more to

view them all according to the module vise. Apply this query in SQL or TOAD.

SQL> SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE ‘OE%ALL’

_ This query retrieves the tables from the OM module start with OE If we want to retrieve from

the other modules apply the same query changing the where condition.

83

84

85

86

87

88

Purchase Order Flow: Whenever employee requires materials he will create the

requisition. After requisition is approval RFQ(Request For Quotation) will be created. This

RFQ will be sent to multiple suppliers. After that we will receiving the quotation from

supplier then company will do the quote analysis, one quotation will be selected as

purchase order will send this PO to the supplier, as per PO terms & conditions supplier

will supply the material while receiving the material we will create the document called

the receipt. After the creation of receipt AP & Inventory Interfaces will be affected, which

are going to update both AP & Inventory Modules. The given below is the representation

of the PO Flow.

Requisition: We have two types of requisitions

_ Internal and

_ Purchase

Internal Requisition: Internal Requisition will be created whenever we are receiving

the material from another organization in the company.

Purchase Requisition: Purchase Requisition will be created whenever company is

purchasing material from suppliers.

Requisition information will be entered in 3 Levels

1. Headers

2. Lines and

3. Distributions

Creating Requisition: The navigation for the Requisition screen is given below.

1. Go With Purchasing, Vision Operations(USA) Responsibility.

2. Go with Requisitions _ Requisitions Navigation.

3. Then we will get the screen as given below. Enter the fields item, quantity that

represent how much you required to purchase and what you want to purchase.

4. This is one-to-many Relationships, after completion of the document, select

Approve Button, then requisition will be sent for the approval.

89

Catalog Screen: By using this Catalog Button we can attach the predefined Requisition

templates from the requisition template list.

Select Catalog Button, select requisition template and Select Find Button and list

of items which are required and click the button select.

90

Distributions: When ever we click on the Distributions button on the Requisitions

window then we will get this distributions window

Approval Screen: When we click on the Approve button on the Requisition Screen we

will get this window. Here we will check the Submit for Approval check box then click on

Ok button.

5. After Completion of the approval we will select form called Requisition

Summary.

Then we will get the alert box as our requisition has been submitted. Then click on ok.

91

Requisition Summary: To Check whether the requisition is approved or not we will go

with the navigation Requisitions _ Requisitions Summary then we will get the

screen given below enter the requisition number and press find button.

Requisition Headers Summary: Whenever we press the find button we will get the

window given below. Here our current requisitions number 5646 Approval status is

approved.

Note: Once the Requisition is approved then only we will go for RFQ.

92

If you want to cancel the requisition, then select Tools from menubar the navigation is

Tools _ Control then select the option called Cancel Requisition and mentions the

reason for canceling.

Creating Requisition Number Manually:

Go With navigation Set up _ Organizations _ Purchasing Options and go to

Numbering Tab

93

The Tables that effect Requisitions are:

1. Header level Information -> PO_REQUISITION_HEADERS_ALL

Select * from PO_REQUISITION_HEADERS_ALL where segment1=’5646’

--requisition_header_id=56635.

Here segment1 is the Requisition Number. And REQUISITION_HEADER_ID.

2. Line Level Information-> PO_REQUISITION_LINES_ALL

Select * from PO_REQUISITION_LINES_ALL where

requisition_header_id=56635;

--requisition_line_id = 60546 60547

3. Distribution Level Information -> PO_REQ_DISTRIBUTIONS_ALL

select * from PO_REQ_DISTRIBUTIONS_ALL where requisition_line_id

in(60546,60547);

RFQ(Request For Quotation): Once the requisition has approved RFQ will be created

and this RFQ will be sent to suppliers if suppliers are agreed with the terms & conditions,

which we have specified in RFQ document, they will send the quotations to the company,

we have 3 types of RFQ’s

_ Bid

_ Catalog

_ Standard

The navigation for the RFQ is RFQ’s and Quotations _ RFQ’s then we will get the RFQ

Screen as shown below.

Bid: BID RFQ will be created for the large & expensive items where you will not find any

discount means no price breaks.

Catalog: Catalog RFQ will be created for the items, which we are purchasing frequently

where we can get the price breaks and terms & conditions.

Standard: This will be created for the items, which we will purchase only once not very

often. If we are sending Bid RFQ, to the suppliers then we will receive the bid quotation

from the suppliers for the catalog RFQ’s we will be receiving quotations and for standard

RFQ, standard quotation will be sent by the suppliers.

RFQ information will be entered at 3 levels

_ Headers

_ Lines &

_ Shipments

Headers: In Header level we will specify RFQ number, type & ship_to, bill_to location

Lines: In Line Level we will specify the items.

Shipment: At Shipment level we will specify discount details.

94

RFQ(Request For Quotation):

The table that effect this RFQ are

1. HEADER LEVEL: PO_HEADERS_ALL

Select * from PO_HEADERS_ALL where segment1=’312’

and type_lookup_code=’RFQ’ -- Po_header_id=32560

2. LINE LEVEL : PO_LINES_ALL

Select * from PO_LINES_ALL where po_header_id=32560

--po_line_id=37768 37769

3. SHIPMENT LEVEL (PRICE BREAKS): PO_LINE_LOCATIONS_ALL

Select * from PO_LINE_LOCATIONS_ALL where po_line_id=37768

Currency: When we click on the Currency button in RFQ’s window we will get the

window.

The table for Currency Screen: FND_CURRENCIES

95

Terms and Conditions: When we click on the Terms button in RFQ’s window we will get

the window.

The table that affects this is AP_TERMS.

Price Breaks: When we click on the Price Breaks button in RFQ’s window we will get the

window.

The table affects this PO_LINE_LOCATIONS.

96

Suppliers: When we click on the Suppliers button in RFQ’s window we will get the RFQ

Suppliers window.

THE TABLE AFFECT THIS SCREEN IS PO_VENDORS AND PO_VENDOR_SITES_ALL

Auto Create: Through this auto create option we can directly create either RFQ’s or

Purchase orders from the approved requisition. The navigation for auto create is in the

Purchasing Vision Operations responsibility Auto Create will be there directly. Whenever

we click on the Auto create then we will get the screen given below.

97

Enter the requisition number, which we created earlier and go with the find button. Then

we will get the screen given below.

PURCHASE ORDER:

After receiving the quotations from supplier then we will create the

purchase order. We will approve that purchase order will send po to the supplier. As per

the po document terms and conditions supplier will supply the material.

We have 4 types of Purchase Order

1. Standard PO

2. Planned PO

3. Blanket Purchase Agreement

4. Contract Purchase Agreement

Purchase Order Type Summary:

Standard PO Planned PO Blanket PA Contract PA

1. Terms & Conditions

Known

Yes Yes Yes Yes

2. Goods or Services

Known

Yes Yes Yes No

3. Pricing Known Yes Yes May be No

4. Quantity Known Yes Yes No No

5. Account Distributions

Known

Yes Yes No No

6. Delivery Schedule

Known

Yes May be No No

7. Can be Encumbered Yes Yes No No

8. Can encumber releases N/A Yes Yes N/A

98

Standard PO: For all the Purchase Orders we will use Standard PO as the PO type

where we will specify terms & conditions items, price, quantity, delivery Schedule and so

on.

Planned PO: if delivery schedule is not confirmed then we will create planned PO

Blanket Agreement: Supplier and Buyer will have the agreements where we will find

terms & conditions and items price may or may not. Whenever buyer is required

material he will release the order as per that supplier will supply material.

Contract PA: we will have only terms and conditions buyer can purchase any item from

the supplier.

Purchase order Information will be entered at 4 Levels:

1. Header level

2. Line Level

3. Shipments

4. Distributions

Purchase Order:

1. Go with Purchasing, Vision Operations(USA) Responsibility.

2. Go with Purchase Orders _ Purchase Orders.

The table that affects this screen is

1. HEADER LEVEL: PO_HEADERS_ALL

Select * from PO_HEADERS_ALL where segment1=4465

and type_lookup_code=’Standard’ -- po_header_id=32588

99

2. LINE LEVE: PO_LINES_ALL

Select * from PO_LINES_ALL where po_header_id=32588 –- po_line_id=37797

SHIPMENTS: When we click on the Shipments button in Purchase order window we will

get this Shipments window.

Here in the Shipments window we will click on more tab then we are going to select

the match approval whether 2-Way or 3-Way or 4-Way.

3. SHIPMENT LEVEL: PO_LINE_LOCATIONS_ALL

Select * from po_line_locations_all where po_line_id=37797

--line_location_id=72173

RECEIVING CONTROLS: When we click on the Receiving Controls button in Shipment

window we will get this window.

100

DISTRIBUTIONS: When we click on the Distributions button in Shipment window we

will get this window.

4. DISTRIBUTIONS LELVEL: PO_DISTRIBUTIONS_ALL

Select * from PO_DISTRIBUTIONS_ALL where line_location_id=72174

--po_distribution_id

SHIP TO BILL TO: HR_LOCATIONS

APPROVING THE PO: Then click on the Approve Button on the PO screen then PO

will be submitted for Approval.

101

After approving the PO, the Status will be changed to Approved.

To view whether the purchase order is approved or not we will go with the navigation

Purchase Orders _ Purchase Order Summary then we will get the screen given

below.

Enter the PO number and Select Find Button.

102

After entering the Po number then Click on the find button we will get the Purchase

order Headers window.

Go for Lines Button – it will display the line level details i.e items, price, quantity so on.

If you want to cancel PO or if you want to keep the PO on hold. Then go with menubar

the navigation is Tools _ Control you will get the following screen.

103

RECEIPTS: It is one of the purchasing document will be created while receiving the

material from supplier. While creating the purchase order we will have the option called

MATCH APPROVAL like

1. 2-way _PO, Invoice

2. 3-way _PO, Receipt, Invoice

3. 4-way _Po, Receipt, Inspection, Invoice

Receipt Types:

1. Standard Receipt (3 –Way).

2. Direct Delivery (2-Way)

3. Inspection Required (4 – Way)

Navigation:

1. Go with Receiving _ Receipts.

2. Enter the PO number and go with Find Button.

104

Enter the purchase order number and click on find button. Then we will get the receipts

window. Here in this window lines will be appear if we want receive that item(line) then

we have to check the check box which was bolded in the below figure.

If we want to get the headers screen then click on Headers button and drag on to the

lines tab then we will get receipt header window as shown below.

105

Here in this window the receipt date should be in the open period. If you want to know

the open period then go with the navigation

Setup _ Financials _ Accounting _ Open and Close periods.

Tables that affect these Receipts are

1. RCV_SHIPMENT_HEADERS

2. RCV_SHIPMENT_LINES

3. RCV_TRANSACTIONS

AP(ACCOUNT PAYABLES):

In this the company is paying amount to the supplier.

In this we are having 3 steps

1. Creation of Invoice. _ Approve invoice

2. Make the payment.

3. Transfer to GL (General Ledger).

106

Invoice Types:

1. Standard Invoice: For supplier Standard payment will select this standard invoice,

where we are going to create invoice with supplier name and supplier site name.

2. Credit Memo and Debit Memo: Both will come under negative amount.

Credit Memo will be created if supplier is giving discount.

Debit Memo will be created when buyer is going to deduct the amount.

3. Pre Payment Type: whenever suppler required advance payment that time we will

select prepayment amount.

4. PO Default: we will give the PO Number as per the PO amount invoice will be

generated.

5. Quick Match Invoice: we will match the invoice either with PO or invoice with

receipt, as the matching payments will be done.

6. Expense Reports: fro employee expenses we will select the invoice type as

expense reports.

7. Recurring Invoices: for fixed amount and fixed duration, we will select recurring

invoices. Like rent accounts and so on.

8. Mixed Type Invoice: for company miscellaneous expenses we will create mixed

type.

9. With Holding Tax: if suppler is not registered suppler on behalf of suppler,

company will make the income tax to the government.

Navigation:

1. Go with PAYABLES, VISION OPERATIONS(USA) Responsibility.

2. Go with Invoices _ Entry _Invoices.

3. Open the invoice form

Enter Supplier, Supplier num and site invoice amount.

107

4. Go with Distributions Button and Enter distribution details.

The invoice total and distribution total is equal. Then save the form.

5. After that go with Match Button you are matching your invoice with your PO then

go with Purchasing Order.

108

Go with Find Button then we will get the window given below.

Go with Actions Button and check the check box Validate

109

After this the Invoice Status will be change to Validated.

Again Go with Actions Button check the check box Create Accounting

110

It will create accounting transactions. We can see the account transactions from

Go with the menu Tools _ View Accounting.

The tables that affect Invoice are

1.AP_INVOICES_ALL –invoice_number, vendor_id, invoice_id

Distributions:

AP_INVOICE_DISTRIBUTIONS_ALL --invoice_id

Accounting Transactions:

1. AP_AE_HEADERS_ALL

2. AP_AE_LINES_ALL

Distribution Set:

If we want to create distribution transactions by using the system then we will go

for creation of distribution set.

Navigation:

Set up_invoice_distribution sets

1. Enter distribution set name, percentage and account transactions.

2. Attach this distribution set while creating invoice of header level.

111

Invoice Batches:

If we want to create multiple invoices by specifying batch name, then we will

select the option called invoice batches.

Invoice Gateway:

If we are retrieving invoice data from legacy system then we will use this invoice

gateway option.

PAYMENTS:

1. Go with Actions button check the check box Pay in full.

1. Select the payment type either

1. Manual

2. Quick

3. Refund

2. Enter bank accounts, document. System will automatically generate the document

number (check number) and enter the supplier details.

3. Go with Enter/Adjust invoice button

4. Give the invoice number and save the transaction.

5. System will automatically generate payment transactions

6. Select payment overview button where we can find out complete payment details.

112

7. Select the bank button where we can find out bank information and bank accounts.

113

TABLES:

1. AP_INVOICE_PAYMENTS_ALL

2. AP_CHECKS_ALL

3. AP_BANK_ACCOUNTS

4. AP_BANK_BRANCHES

5. AP_CHECK_FORMATS

AP TO GL TRANSFER:

1. Create invoice and approve it

2. Make payments and generate accounting transactions.

3. Go to SRS Window, select concurrent program called Payables Transfer to GL.

Enter invoice date in from date and to date parameters, system will submit the

program internally called Journal Import.

4. Copy the request id of journal import.

5. Go to the responsibility _ General Ledger Super user and go with the navigation

Journals _ Enter , paste the request id of Journal Import in the batch field

114

Select find button it will shows the payment details.

The table’s affects GL are

1. GL_JE_HEADERS

2. GL_JE_LINES

3. GL_JE_BATCHES