• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

Oracle Applications

Here Resource means 5 m’s
1. Money
2. Man power
3. Machinery
4. Material and
5. Management
ERP Purpose: Planning for proper utilization of resources for a business organization .
ERP as a Product : all objects(i.e. forms, reports )and all other programs are developed and throwly tested and those will be provided with these ERP.

Methodology : the process used to implement ERP Product for a business Organization.

AIM (APPLICATION IMPLEMENTATION METHODOLOGY):
Methodology for Oracle Apps is AIM(Applications Implementation Methodology)
Phases in Aim Methodology:
1. Definition Phase
2. Operational Analysis Phase
3. Solution Design Phase
4. Build Phase
5. Transition Phase
6. Production Phase

1. Definition Phase: Here the functional consultants prepare the sample structure (Blue Print) for the real business by gathering the information.
2. Operational Analysis phase: In this the functional consultant prepare BR100 (Business Requirement).
3. Solution Design Phase: here the functional consultant will prepare MD50 by using BR100. MD50 is the functional document.
4. Build Phase: here the technical consultant converts MD50 to MD70. MD70 is the technical document. Then the technical consultant prepare the final object and go with testing, i.e. Unit Testing.
5. Transition Phase: here SIT(System Integration Testing) and UAT(User Acceptance Testing) is done. Functional consultant does SIT. After this the functional consultant will train the user in oracle apps product and user does UAT. If it is satisfied by the user, it is sign off and product will be given to the user.
6. Production phase: loading the project in the client place. Where the server of
oracle apps in the client place.

Introduction:The Oracle Applications is an ERP, which was developed by the Oracle Corp and was used by the several companies to utilize several resources contained with them.

Business Flow of Oracle Applications:

Screenshot_3

Oracle Applications is designed for the Financial and Manufacturing clients. The categories in the modules are given below.

Financial Modules: Account Payables, Account Receivables, General Ledger and Fixed Assets.

Manufacturing Modules: Purchasing, Inventory, Bill Of Materials, Work in Progress and Order Management. In HRMS we maintain all the details of the employee as well as the organization details.

Module: It is a collection of forms and reports which are related to particular business process. Purchase order module has the forms and reports which are related to the purchasing business process. It contains nearly 50,000 tables they are accessed by only purchase order module.

 Oracle Applications Database Structure:

Screenshot_1

Schema: Schema is a place in database which contains the database objects like tables, indexes, sequences, views and synonyms.
In oracle applications database we have individual schemas for each module application. These schemas contain the database objects of only that particular module. When we connect any database schema we can access only that schema database objects. We can’t access other database objects. For data integrity purpose the oracle has deigned one more schema called Apps schema which contains only synonyms and this schema have the rights to access any other module database objects.
Note: Apps Schema will not contain the tables it contain only synonyms.
Projects in Oracle Applications:
 Implementation Projects: Implementation means we will develop the forms, reports from the scratch. In this project we will be work          in the areas of the forms, reports, interface, customizations and interfaces.
 Customization Projects: In this we customize some standard reports.
 Migration / Up gradation Projects: This will be moving from the Old version to the New version.
 Support / Maintenance Projects: Solving the issues which are raised by the end
user while entering the data.
 Post Implementation Projects: This will be done at free of cost. It’s a real time testing.

Types of Docs in Oracle Apps:
      MD 050 — Module design by the functional consultant.
      MD 070 — Technical Document designed by the technical consultant.
      MD 020 — Technical document designed by the functional document.
      MD 0120 — Migration and user training document by the technical consultant.
      CV 040 — Conversion of the functional document by the functional consultant.
      CV 060 — Conversion of the technical document by the technical consultant.
Note: Conversion means moving the data from the legacy system to the apps system.

Oracle Apps File Architecture:

Screenshot_2

We will have Linux/Unix server for oracle apps we will be connecting to the server by using the FTP/TOAD/FILEZILLA/WINSEP3.

File Architecture:

Screenshot_4

The Server first top will be application top “Appl-top” we can change the name according to the client wish. Under that we have the product top. Each module has go it individual top.

Note: We will move the (download/upload) forms and reports from the local machine to the server always in binary mode. We save any information only on the server not in the local machine.
Entering the Oracle Applications: First it will prompt for the username and password and then click on the connect button then it will give us responsibilities form. Select to which responsibility you want to login then you will enter to the oracle applications.

TekSlateTemplate

We get the screen as given below when we enter into the system administrator responsibility.

Screenshot_5

User Creation: Connect to the oracle applications 11i as we shown above and enter into the system administrator responsibility. The navigation to create the user is Security ->User ->Define. The window is given below. Enter the username, password and assign responsibilities what ever you want and save the user will be created.

TekSlateTemplate

 Note: when ever we login next time we can enter the user name and password that what we created now.

Steps to Register a Report:
1. Develop the report in 6i according to the client requirements.
2. Transfer the object from the local system to the server and placing that in the  appropriate directory.
3. Creating the executable and set the execution method as oracle reports as we are registering the report. Set the executable file  name as the object name which we modified or developed.
4. Create the concurrent program and set the executable to the concurrent program. If your program has any parameters then go with the parameters button and submit the parameter tokens.
5. Set the concurrent program to the request group and the request group to the responsibilities.
6. User having that responsibility will only can run that request or submit the  request of the report.

Note: Every form contains 3 types of fields those are
1. Yellow Colored fields — Mandatory. With out entering these mandatory fields we can’t save the form.
2. Green Colored fields — Read only.
3. White Colored fields — Optional.
4. Blue colored indicates the form in the query mode. We enter into the query mode by pressing F11 and enter data which was                       already saved to retrieve the record press CTRL+F11.

Creating the Concurrent Program Executable: Before we start the registration process we have to create a report according to the client requirements and place the report in the appropriate directory (Custom top). The navigation for this form is
Concurrent ->Program ->Executable. The given below is the executable window.

TekSlateTemplate

Window help:
Executable: Set the executable name as we like here we entered the name as per the program.
Short Name: Set the short name of the executable as related to the executable because we have to remember them.
Application: Select the Appropriate Application from the list of applications here we selected the Oracle Receivables because we saved our report (valueset.RDF) in the AR (Receivables) folder only.

Description: This field is not a mandatory field if we want to describe the concurrent program executable we use this field.
Execution Method: There are eleven execution methods we can choose what ever we want as per the requirements. Those are stated below.
Host: The execution file is a host script. When ever we have an UNIX scripting programs then we use this execution method.
Immediate: The execution file is a program written to run as a subroutine of the concurrent manager. We recommend against defining new immediate concurrent programs, and suggest you use either a PL/SQL Stored Procedure or a Spawned C Program instead.
Java Stored Procedure: The execution file is a Java stored procedure.
Java Concurrent Program: The execution file is a program written in Java.
Multi Language Function: The execution file is a function (MLS function) that supports running concurrent programs in multiple languages.
Oracle Reports: The execution file is an Oracle Reports file. To register a report which we done in report6i we use this method.

PL/SQL Stored Procedure: The execution file is a PL/SQL stored procedure.
Spawned: The execution file is a C or Pro*C program.
SQL * Loader: The execution file is a SQL script.
SQL * Plus: The execution file is a SQL*Plus script.

Note:

1. According to the requirement of the user and the type of the report we created the execution method will depend.

Execution File Name: We have to set the executable filename as our report name. Here we create the report name as valueset so; we set the name as valueset. Subroutine Name: Only immediate programs or spawned programs using the Unified C API use the subroutine field. Staging function Parameters (Button): The Stage Function Parameters button opens a window that allows you to enter parameters for the Request Set Stage Function. This button is only enabled when you select Request Set Stage Function as your Execution Method.

After entering all the fields save the form and go to the Concurrent Programs Form. The Navigation for this is Concurrent ->Program ->Define.

Window Help:
Program: Enter the Program name as we like it is not standard we gave here as cs_value_prg as per our program.

Enabled (Check Box): Disabled programs do not show up in users’ lists, and do not appear in any concurrent manager queues. You cannot delete a concurrent program because its information helps to provide an audit trail.
Short Name: The short name is also like the short name in the executable form. We have to give the name as per the program name.
Application Name: Select the application name as Oracle Receivables. We have to select the application as what we select in the Executable form.
Description: This field is not a mandatory field if we want to describe the concurrent program we use this field.

Slide1

Executable:
Name: Set the executable name as the short name of the executable which we give in the previous Executable form.
Method: when we enter the executable name there in the name field it was automatically set which we saved in the executable form.
Priority: you can assign this program it own priority. The concurrent managers process
requests for this program at the priority you assign here.

Request:
Type: If you want to associate your program with a predefined request type, enter the name of the request type here.
Incrementor: For use by Oracle Applications internal developers only. The Incrementor function is shown here.
MLS Function: The Multilingual Concurrent Request feature allows a user to submit a request once to be run multiple times, each time in a different language. If this program utilizes this feature the MLS function determines which installed languages are needed
for the request Use in SRS (Check Box): Check this box to indicate that users can submit a request to run this program from a Standard Request Submission window.
Run Alone (Check Box): If you check the Use in SRS box, you can also check this box to allow a user to enter disabled or outdated values as parameter values.
Enable Trace (Check Box): Turns on SQL tracing when program runs.
Allow Disabled Values (Check Box): If you check the Use in SRS box, you can also check this box to allow a user to enter disabled or outdated values as parameter values. Restart on system failure Run Alone (Check Box): Use this option to indicate that this concurrent program should automatically be restarted when the concurrent manager is restored after a system failure. NLS Complaint (Check Box): This box is checked if the program allows for a user to submit a request of this program that will reflect a language and territory that are different from the language and territory that the users are operating in.
Output:
Format: There are several formats as per the requirements in the report we use this format here we use the TEXT format.
Save (Check Box): Indicate whether to automatically save the output from this program to an operating system file when it is run. This value becomes the default for all requests submitted for this program.
Print (Check Box): If you enter No, your concurrent program’s output is never sent to the printer.
Columns/Rows: Enter the minimum column and row length for this program’s report output. Oracle Applications uses this information to determine which print styles can accommodate your report.
Style: There are several styles which we can use A4, Landscape, BACS and other styles.
Style Required (Check Box): If your program requires a specific print style (for example, a check writing report), use this check box to enforce that print style.
Printer: If you want to restrict your program’s output to a single printer, enter the name of the printer to which you want to send your output.

Buttons:
Copy to (Button): Create another concurrent program using the same executable, request and report information as the current program. You can optionally copy the incompatibility and parameter details information as well.
Session Control (Button): Use this window to specify options for the database session of
the concurrent program when it is executed.
Incompatibilities (Button): Identify programs that should not run simultaneously with your concurrent program because they might interfere with its execution. You can specify your program as being incompatible with itself.
Parameters (Button): If there are any parameters for our report then we go with the parameters button. We get the window called parameters when we go with the button.
Note: The window help of the Parameter window will be given in the next steps.

Setting the Concurrent Program to the Request Group: To set the concurrent program to the request group we have to go to the responsibilities screen. The navigation is Security ->Responsibility ->Define. Then we get the window as shown below.
Note: Here if we want we can create new responsibility or we can use the existing one. Here we use the existing one retrieved the existing responsibility name using query mode (F11) as we discussed earlier.

Slide2

Now copy the request group name and go to the navigation Security ->Responsibility ->Request. Then Request Groups window will be opened as shown below. By entering the query mode we have to paste the Request Group name at the Group field and press CTRL+F11. This form is case sensitive. After that click on new button and enter the concurrent program name at the name column and go with save button.

ffff

Submitting the Request: Now switch to the appropriate responsibility then only we can run the request. Here as per our example we have to enter into the Receivable Vision Operations. After enter into the responsibility go with the navigation. Menu ->View ->Requests then we get the window find request go with Submit a New Request button. It will prompt for the Single Request or Request Set. As our program is the single we go with the Single option. After that we get another window.

Slide1

Slide2

Enter the concurrent program name at the name field and go with the submit button. Then we get another window as shown below.

Slide11

To view the outputs of your program then go with the output button. To view any error messages you go with the View log button.

Process to Register a PL/SQL Stored Procedure: This is the PL/SQL Stored Procedure using the Table Valuesets.
Creating the Procedure:
EX:
SQL> create or replace procedure chaitu_proc(errbuf out varchar2, retcode out varchar2, p_dno in number, p_dname in         varchar2) as
cursor test_cursor is select e.ename, d.deptno, d.dname from scott.emp e,
scott.dept d where e.deptno=p_dno and d.dname=p_dname;
begin
for i in test_cursor
loop
dbms_output.put_line(i.ename || ‘ ‘ || i.deptno ||’ ‘|| i.dname);
end loop;
end chaitu_proc;
Procedure Created.
Note: Here we have to use fnd_file.put_line to view the output in Oracle apps, because dbms_output.put_line will not work in Oracle Apps.Here in the Procedure there are two mandatory out parameters those are errbuf and retcode.
Errbuf: Used to write the error message to log or request file.
Retcode: if the RETCODE is set to 0 – Then concurrent program will Completed NORMAL.
1 – Then concurrent program will Completed WARNINGS.
2 – Then concurrent program will Completed ERROR.
Creating the Concurrent Program Executable: Before we start the registration process we have to create a package or Procedure according to the client requirements. The navigation for this form is Concurrent ->Program ->Executable. The given below
is the Navigator window.

Slide4

When we go with the above navigation the Executable form will be open then we have to enter the mandatory fields.

 

Slide5

Executable: Set the executable name as we like here we entered the name as per the program.
Short Name: Set the short name of the executable as related to the executable because we have to remember them.
Application: Select the application name from the list of applications here we selected the Oracle Receivables as per our requirements.
Description: This field is not a mandatory field if we want to describe the program we use this field.
Execution Method: We have to select the PL/SQL Stored Procedure as execution method to register the myproc1 procedure.
Execution File Name: We have to set the executable filename as procedure name. Here we our procedure name is mrproc1 so; we set the name as myproc1.
After entering all the fields save the form and go to the Concurrent Programs Form. The Navigation for this is Concurrent  Program  Define.

Slide6

Enter all the mandatory fields and save the form.
Program: Enter the Program name as we like it is not standard we gave here as cs_value_prg as per our program.
Short Name: The short name is also like the short name in the executable form. We have to give the name as per the program name.
Application Name: Select the application name as Oracle Receivables. We have to select the application as what we select in the Executable form.
Executable Name: Set the executable name as the short name of the executable which we give in the previous Executable form.
Format: There are several formats as per the requirements in the report we use this format here we use the TEXT format.
Style: There are several styles which we can use A4, Landscape, BACS and other styles.Here we are having the two parameters go with Parameters button in the Screen. The Concurrent Program Parameters window will show below.

Slide7

Note: There is no token required where we are working with the PL/SQL STORED PROCEDURE and observe that the token field in the above window is grayed it won’t allow any values into that field.
Window Help:
Seq: Choose the sequence numbers that specify the order in which your program receives parameter values from the concurrent manager.
Parameter: We enter the parameter name that we are passing. Same was passed to the prompt when we press tab.
Description: This is the optional field to describe the input parameter.
Enabled (Check Box): Disabled parameters do not display at request submission time and are not passed to your execution file.

Validation:

Valueset: Enter the name of the value set you want your parameter to use for validation.You can only select from independent, table, and non-validated value sets. As per our example we defined two value sets for two parameters and set the two value sets respectively.

Default Type: If you want to set a default value for this parameter, identify the type of value you need.
Required (Check Box): If the program executable file requires an argument, you should require it for your concurrent program.
Enable Security (Check Box): If the value set for this parameter does not allow security rules, then this field is display only. Otherwise you can elect to apply any security rules defined for this value set to affect your parameter list.
Display:
Display Size: Enter the field length in characters for this parameter. The user sees and fills in the field in the Parameters window of the Submit Requests window. Concatenated Description Size: Enter the display length in characters for the parameter value description. The user sees the parameter value in the Parameter Description field of the Submit Requests and View Requests forms. The Parameter Description field concatenates all the parameter values for the concurrent program.

Note: We recommend that you set the Concatenated Description Size for each of your parameters so that the total Concatenated Description Size for your program is 80 or less, since most video screens are 80 characters wide.
Prompt: Enter the message that will prompt for the input at the submit request form.
Token: For a parameter in an Oracle Reports program, the keyword or parameter appears here. The value is case insensitive. For other types of programs, you can skip this field.

After modifying the parameter form, save the program. Now go the Request Group form and the set the concurrent program to the appropriate request group. The navigation for this is Security ->Responsibility -> Request. Then Request Groups form will open as shown below. Enter into Query mode by pressing F11 then enter the Request Group Name and press CTRL+F11. This form is case sensitive. After that click on new button and enter the concurrent program name at the name column and go with save button.
Ex:– Some Request Group Names
For Receivables : Receivables All
For Inventory : All Inclusive GUI
For Order Management : OM Concurrent Programs
For Purchase Order : All Reports

Slide8

If we don’t know the Request group name then we have to go for the navigation Security ->Responsibility ->Define we already explained in the above example.Switch to the appropriate responsibility and run the request. When we enter the name of the program in the run request window then parameter window will be open.

Slide9

Then you will get the parameter screen enter the required parameters and go with ok button.

Slide10

After entering both the parameters then press ok and then submit the request. After that submit the request to and go with find button to view the status of the request. After it completed normal we can view the output from the output button. If the Status of the program completed error then go with View Log button.

Slide11Out Put:

Screenshot_6

XML Publisher Registration Steps:
Purpose: For CFD(Customer Facing Document)
1. Develop a report according to the client requirements and register the concurrent program.
Ex: The Concurrent program name is CS_XML_REP
Note: The only difference is we have to set the format type as XML in the concurrent program window while registering
2. Create a Template with specified formats like rtf, pdf… using the appropriate tools.

Slide13

3. Then go with the XML Publisher Administrator Responsibility and click on the Data definition.

Slide14

4. Then you will get the screen now go with the Create Data definition button in the screen.

Slide15

5. The Create Data Definition screen will appear enter all the fields and click on apply button.

Slide16

Window Help
Name: This name is your data definition name, which you are going to create now.
Application: We have to select the appropriate application, which our concurrent program has been registered
Code: The code must match the concurrent program short name, which we registered earlier.

Start Date: It specifies the start date of your application.
Description: To describe your Data Definition enter the data.
6. Now go with Template tab in the same screen.
7. Click on the Create template button then the following screen will appear.

 

Slide17

Window Help:
Name: This is the Template Name, which we are going to create.
Application: We have to select the appropriate application, which our concurrent program has been registered
Type: Select the format type of your template, which you created earlier.
Code: Enter the code for the template that is short name.
Data Definition: Enter the data definition name, which was created earlier.
Start Date: It specifies the start date of your application.
Description: To describe your Template enter the data.
File: Click on Browse and select the template file, which you have created.
Language: Choose a specific language according to the Customer Specifications
Territory: Territory specifies the language belongs to which nation. Then go with Apply button.

8. Now Switch to the appropriate responsibility and run the CS_XML_REP concurrent program, which we created earlier.
9. Then copy the Request_id in the Requests window.

Slide18

10. Switch the responsibility to the XML Publisher Responsibility and run the XML Publisher Report. You will get the following Input Screen.

Slide19
Output:  After completion of the request go with view output window Button.

Slide20

Report Code: — This code will appear on the After parameter form in our report
function AfterReport return boolean is
l_request number;
l_appl NUMBER;
begin
SELECT FCP.APPLICATION_ID
INTO l_appl
FROM FND_CONCURRENT_PROGRAMS FCP,FND_CONCURRENT_REQUESTS R
WHERE FCP.CONCURRENT_PROGRAM_ID = R.CONCURRENT_PROGRAM_ID
AND R.REQUEST_ID = :P_CONC_REQUEST_ID;
l_request := fnd_request.submit_request( ‘XDO’, ‘XDOREPPB’, null, null, FALSE,
:P_CONC_REQUEST_ID,’xmlreporttemp’,l_appl,’US’,’N’,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,
NULL,NULL,NULL,NULL,NULL,NULL,NULL);
srw.message(100, ‘ Request submitted ‘ || l_request);
SRW.USER_EXIT(‘FND SRWEXIT’);
return (TRUE);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
end;

Table Registration in APPS:
1. Create a table .
Ex: Sql> Crete table wip_item(ITEM VARCHAR2(15),
ITEMDESCRIPTION VARCHAR2(100),
ITEMCOST NUMBER(8),
CATEGORYK VARCHAR2(100),
CREATION_DATE DATE,
CREATED_BY NUMBER(7),
LAST_UPDATE_DATE DATE,
LAST_UPDATED_BY NUMBER(7),
ATTRIBUTE_CAT VARCHAR2(100),
ATTRIBUTE1 VARCHAR2(100),
ATTRIBUTE2 VARCHAR2(100),
ATTRIBUTE3 VARCHAR2(100),
ATTRIBUTE4 VARCHAR2(100),
ATTRIBUTE5 VARCHAR2(100));

2. If you are in different user for Ex: scott/tiger then you have to grant permission to APPS.
Ex: Grant all on wip_item to apps;
3. Connect apps/apps@PROD.
4. Create synonym for the table which we are going to register in APPS.
Ex: Create public synonym wip_item for wip.wip_item.
5. Registering the table by using AD_DD Package.

Syntax: Exec AD_DD.REGISTER_TABLE
(p_appl_short_name,
p_tab_name, –table name
p_tab_type,
p_next_extent, — Enter the initial and next extent sizes in kilobytes for your
table. You must enter values greater than 0.
p_pct_free,–free storage.
p_pct_used);– used storage.

EXEC AD_DD.REGISTER_TABLE(‘WIP’,’WIP_ITEM’,’T’,8,10,90);

Registering the Columns:
Syntax: EXEC AD_DD.REGISTER_COLUMN (p_appl_short_name,
p_tab_name,
p_col_name,
p_col_seq,
p_col_type,
p_col_width,
p_nullable,
p_translate,
p_precision default null,
p_scale default null);

Ex:
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’ITEM’,1,’VARCHAR2′,15,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’ITEMDESCRIPTION’,2,’VARCHAR2′,100,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’ITEMCOST’,3,’NUMBER’,8,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’CATEGORYK’,4,’VARCHAR2′,100,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’CREATION_DATE’,5,’DATE’,11,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’CREATED_BY’,6,’NUMBER’,7,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’LAST_UPDATE_DATE’,7,’DATE’,11,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’LAST_UPDATED_BY’,8,’NUMBER’,7,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’ATTRIBUTE_CAT’,9,’VARCHAR2′,100,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’ATTRIBUTE1′,10,’VARCHAR2′,100,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’ATTRIBUTE2′,11,’VARCHAR2′,100,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’ATTRIBUTE3′,12,’VARCHAR2′,100,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’ATTRIBUTE4′,13,’VARCHAR2′,100,’N’,’Y’);
EXEC AD_DD.REGISTER_COLUMN(‘WIP’,’WIP_ITEM’,’ATTRIBUTE5′,14,’VARCHAR2′,100,’N’,’Y’);

6. Check in APPS go to Application Developer Responsibility Application ->Database-> Table Then we will get Tables Screen as Shown below and press F11 then enter the table_name and press CTRL+F11 then we will get the data as shown below.

Slide1

Valuesets: Valueset is nothing but the list of values with validation. We will be using the valuesets whenever the concurrent programs have parameters and while defining the flex fields we attach this flex fields. The given below are the types of valuesets.None

  • Independen
  • Dependent
  • Table
  • Translatable Dependent
  • Translatable Independent
  • Special and
  • Pair.

None: It is used whenever we want to apply some format condition like max.length, numbers, and capital letters so on. There wont be any list of values in none valueset.
Independent: This valueset is used whenever we want to show some hard coded values to the end user that time we will create. Navigation to get the Valueset screen in Application ->Validation ->Set.

                                  Slide2

After that go with the Navigation Application ->Validation ->Values then we will get the screen given below. Enter the valueset name, which we created earlier.

                                Slide3 

Dependent: It is a list of values, which are depending on the Independent valueset. To create dependent valueset go with same process we mentioned above and set the validation type as Dependent and click on the Edit Information button then we will get
the Dependent valueset information screen enter the mandatory fields like independent valueset name which we created earlier.

                                  Slide4

Table: It will be used whenever we want to show the list of values from the table column name. When we select this option then the Edit Information in this window will become active. When we go with that Edit Information button we get the Validation
information window. That is shown below.
To define the valueset the Navigation is Application  Validation  Set. The Valueset form is shown below.

Slide5

Window Help:
Value Set Name: Define your own value set name.
Format Type: Select the appropriate datatype that what we are passing as parameter through this valueset. This field is mandatory.
Maximum Size: Enter the maximum size of the parameter that what we are passing to the report. This field is mandatory.
Validation Type: Select validation type as Table and go with the edit information button. then the Validation table information window will be open. Enter the mandatory fields as required.
Now go with the edit information button then we get the Validation Table Information window will appear as shown below.

     Slide6

Table Name: Here we can enter the table name or we can write a query. Query must be written in braces only.
Ex: (select distinct (empno) from scott.emp)

Value: Enter the column name which we want to display in the parameter window.
Meaning: Meaning is to provide the additional information along with the list of values.
ID: To column is used to pass a value to the parameter. The scenario of using this id will be if we want to pass a value to the parameter, which is associated with, the list of values select by the user. Where/Order By: To incorporate any filters in the list of values we can do that in where clause and order by is used to sort the list of values in ascending or descending order.
Additional Columns: when we want to display any other Additional columns in the output form we use this. We use these two as per the requirements.
Syntax: Column name “Alias Name” (Width)

Note: When we are working with table valueset only the edit information button is active. For other valuesets it is not active.
Using $FLEX$: $FLEX$ is used to hold the data which was selected by the previous valueset for that we have to define another table valueset with different name as per the above process. In the new valuset we used the $FLEX$ as given below screen.

                                                      Slide7

Note:
1.  Whenever you assign the valueset to any concurrent program then you are not allowed to modify the valueset. We have to remove       from the valueset from the concurrent program to modify the valueset, which was already created.
2. For more information you go to the example given below with detailed explanation of table valueset.
3. When ever we select the validation type as table then the edit information button in the valuesets window will be enabled. It will be         disabled for the other valuesets only for the table valueset.

Translatable Dependent & Translatable Independent: These two valuesets are used whenever you are working with multi-lingual implementation.
Special: Special valueset is used for display flexfields data as parameter values.
Pair: This valueset is used to display parameters in pairs.
EX: Date from & Date To…etc. (To specify a range in pair we use such type of valuesets).

Working with User Exits in Reports 6i: User Exits are 3GL programs used to transfer the control from the report builder to Oracle Applications or any and it will perform some actions and return to the report builder. There are five types of user exits those are
given below.
 FND SRWINIT
                FND SRWEXIT
               FND FORMAT_CURRENCY
               FND FLEXSQL
                FND FLEXIDVAL
  FND SRWINIT: Used to write in before report trigger. It is used to fetch the concurrent request information and also used to set the profile options.
FND SRWEXIT: We will write this in after report trigger. Used to free the memory which has been allocated by the other user exits
FND FORMAT_CURRENCY: This is used to convert amounts or currency from one currency to other currency values and also used to display currency amounts in formats.
EX: SRW.USER_EXIT(‘FND FORMAT_CURRENCY’,
Code = ‘currency_code’,
Display_width=’15’,
Amount = ‘:cf_feb’,
Display = ‘:cd_feb’);
Return (:cd_feb);
FND FLEXSQL: This is used to get the data from flex fields. We will use in formula columns.
FND FLEXIDVAL: This is used to get the data from flex fields. We will use them in formula columns.
Note:
1. We use the User Exits in the Report triggers like before report, after report, before parameter form, after parameter form.
2. We have to create a mandatory parameter called P_CONC_REQUEST_ID when we work with FND SRWINIT, FND SRWEXIT.
3. The user exits are case sensitive we have to write them in capital letters only otherwise they will raise an error.

Designing the Report: Create a new report manually. Go to data model and click on
the SQL Query then a dialog box SQL Query Statement will open write your query in
that.
Data Model:

                            Slide8

Ex:
SELECT E.EMPNO,E.ENAME,E.SAL,D.DNAME FROM
SCOTT.EMP E,SCOTT.DEPT D
WHERE
D.DEPTNO=E.DEPTNO order by e.deptno
Layout Model: Now go to the layout model and design your report view as per the requirements. See here in the below diagram we created P_CONC_REQUEST_ID in the user parameters.

                        Slide9

Report Triggers: Report triggers execute PL/SQL functions at specific times during the execution and formatting of your report. The types of report triggers are given below.
Before Parameter Form: If we want to pass any initial values to the parameter we use this trigger.
After Parameter Form: This trigger will fire after the entering the parameter values. To
validate the parameters and use to populate the lexical parameters, we use this trigger.
Before Report: In oracle 11i we use this trigger to initialize profile values.
Between Pages: This trigger is used to format the output and fires once per each page
except for the first page.
After Report: This trigger is used to free the memory or drop the temporary objects that
are created in the begining of the report or to send the output as mail.

Note:
1. In the report triggers we cant use any SQL statements directly there is package called srw(Standard Report Writer) using this we can      write any SQL statements.
Syntax: srw.do_sql(‘create sequence myseq
Increment by 1
Start with 1’);
2. To display any message box in the triggers or at any stage in reports6i we use another procedure in srw package.
Syntax: srw.message(1001,’Hai this is a Simple Message from CHAITU’);
Go to the before report trigger and use the FND SRWINIT which is used to fetch the concurrent request information and also to set the profile options.

                         Slide10

Ex: Code in the before report trigger

function BeforeReport return boolean is
begin
SRW.USER_EXIT(‘FND SRWINIT’);
return (TRUE);
end;
After writing the code then click on the compile button if there is no error then we will get the successfully compiled message at the down right corner.
Now go after report trigger and write the code as given below and compile it after successfully completion then close the window.

EX:
function AfterReport return boolean is
begin
SRW.USER_EXIT(‘FND SRWEXIT’);
SRW.message(100,’P_CONC_ID: ‘||:P_CONC_REQUEST_ID);
return (TRUE);
end;
Note: Now our report is ready, save it and register the report in the oracle applications. Then we will get the output as given below.

Output Window:               

Slide11

Profiles: Profiles are the changeable options which affect the way application runs. To get these profiles we use three API’s those are given below.
1. FND_PROFILE.GET(‘Name of the Profile’, variable name);
2. variable name := FND_PROFILE.VALUE(‘Name of the profile’);
3. FND_PROFILE.SET(‘Name of the profile’, value of the profile);
The 1st and 2nd are same but, the only difference is FND_PROFILE.GET is the procedure and FND_PROFILE.VALUE is the function so, it return a value. The Profile values will be set in different levels those are given below.
           Site
           Application
           Responsibility
            User
            Server
            Organization
Site: This field displays the current value, if set, for all users at the installation site.
Application: This field displays the current value, if set, for all users working under responsibilities owned by the application identified in the Find Profile Values block.
Responsibility: This field displays the current value, if set, for all users working under the responsibility identified in the Find Profile Values block.
User: This field displays the current value, if set, for the application user identified in the Find Profile Values block.
Profile: Enter the name of the profile option whose values you wish to display. You may search for profile options using character strings and the wildcard symbol (%). For example, to find all the profile options prefixed by “Concurrent:” you could enter
“Conc%” and press the Find button.
The navigation to get the system profile values information is Profile ->System.

                                                                Slide12

The profiles are of two types those are given below.
1. System Profile and
2. User Profile.
The above given window is the System Profile window and the user profile window is given below and the navigation is  Profile ->Personal.

Slide13

Note: To view personal profiles enter into the query mode and enter the profile name which we have already then we get profile value details.

Some important Profiles:
ORG_ID
MFG_ORGANIZATION_ID
GL_SET_OF_BKS_ID
USER_ID
RESP_ID
RESP_APPL_ID
Note: To get the RESP_ID or RESP_APPL_ID…. Go with the following navigation. Menu  Help  Diagnostics  Examine. Then it will prompt for the oracle password, enter that then we get the Examine and Variable Values window as shown below.

Slide14

In the same way we can get the ORG_ID, RESP_APPL_ID, USER_ID…., by entering what ever we want at the Field column in the above Examine window.

API’s To Register Concurrent Program:
FND_PROGRAM.EXECUTABLE: To Create executable and set the appropriate execution method.

Syntax:
FND_PROGRAM.EXECUTABLE(EXECUTABLE, APPLICATION, DESCRIPTION,
EXECUTION_METHOD, EXECUTION_FILE_NAME);
FND_PROGRAM.REGISTER: To create concurrent program
Syntax:
FND_PROGRAM.REGISTER(PROGRAM, APPLICATION, ENABLED, SHORT_NAME, DESCRIPTION,….);
FND_PROGRAM.PARAMETERS: To set the parameter and to pass appropriate tokens through the concurrent program
Syntax:
FND_PROGRAM.PARAMETER(PARAMETERNAME, VAlUESET, TOKEN, PROMPT, SIZE);
FND_PROGRAM.REQUEST_GROUP: To set the parameter and to pass appropriate tokens through the concurrent program
Syntax:
FND_PROGRAM.REQUEST_GROUP(REQUEST_GROUP_NAME, APPLICATION, CODE, DESCRIPTION,..);
FND_GLOBAL_APPS_INITIALIZE: When ever we are working with profiles in PL/SQL and submitting through the SQL we                 need to use this API the syntax for this API is given below.
Syntax:  FND_GLOBAL_APPS_INITIALIZE(user_id, resp_id, resp_appl_id,
[Security_group_id, server_id]);
Note: In this FND_GLOBAL_APPS_INITIALIZE the parameters user_id… and others we have to get them manually from apps the process we discussed above see. The navigation is Menu ->Help ->Diagnostics ->Examine.

EX: Declare
i number;
begin
FND_GLOBAL_APPS_INITIALIZE(1007899,20420,1);
i:=FND_PROFILE.VALUE(‘ORG_ID’);
dbms_output.put_line(‘Operation ID: ‘|| i); end;
O/p: Operation ID: 204

FND_CLIENT_INFO.SET_ORG_CONTEXT: This is the API used to set the ORG_ID value from the backend, so that system will retrieve the data from the database which is related to mention the organization.
Syntax: Begin
FND_CLIENT_INFO.SET_ORG_CONTEXT(ORG_ID);
end;
EX:    Begin
FND_CLIENT_INFO.SET_ORG_CONTEXT(204);
end;

 FND_REQUEST.SUBMIT_REQUEST: When ever we want to submit the request to the apps through the SQL we use this API. The syntax and a simple example were given below.
Syntax: FND_REQUEST.SUBMIT_REQUEST(
Application,  ->we have to pass the short name of the application concurrent
Program in which we registered (Ex: Oracle Receivables = AR).
Program ,      -> Pass the short name of the concurrent program
Description,  ->description of the concurrent program
Start-time, -> start time of the concurrent program
Sub-request, -> if there any other request, pass true other wise false. Arg-1,
– – > These arg1 to arg100 are used to pass the parameters to the concurrent program.

Arg100); ->Totally FND_REQUEST.SUBMIT_REQUEST API is having the 105 parameters.

EX: Declare
id number;
begin
FND_GLOBAL_APPS_INITIALIZE(1007899,20420,1);
Id:=FND_REQUEST.SUBMIT_REQUEST(‘AR’,CSPROC,NULL, NULL, FALSE, 20, RESEARCH);
dbms_output.put_line(‘Request ID:’ || id);
commit;
end;
O/P: Request ID: 2725363
FND_FILE.PUT_LINE: This API is used to send messages to output file or log file while
we are working with PL/SQL Stored procedures
 For sending messages to output file we use this syntax
Syntax: FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’OUTPUT MESSAGE’);
 For sending messages to log file we use this syntax
Syntax: FND_FILE.PUT_LINE(FND_FILE.LOG,’LOG MESSAGE’);
Difference between ORG_ID & ORGANIZATION_ID: ORG_ID will come under
operating unit in Multiorg. ORGANIZATION_ID will come under inventory organization.
ORG_ID is used to distinguish the data of different business organizations.
ORGANIZATION_ID is used to identify the items belongs to which inventory organization
it has assigned.

MULTI ORGANIZATION (MULTIORG): Multiorg is nothing but under single oracle application implementation maintaining multiple organizations and multiple set of books.
We will have Multiorg tables (table which is ending with _ALL). When ever we enter the
data, which will be stored in the Multiorg tables only.
Ex: OE_ORDER_HEADERS_ALL
OE_ORDER_LINES_ALL
OE_ORDER_HOLDS_ALL
PO_HEADER_ALL
PO_LINES_ALL

Multiorg Structure:

Slide15

Business Groups: The business groups represent the highest level in the organization structure like major divisions etc. business group secures human resources information .for example when we request a list of employees we can see the employees assign to
the business groups.
Set of books: It is a financial reporting entity uses a particular chart of accounts functional currency and accounting calendar. Oracle GL secures transaction information like general entries balances by set of books. When we assign to the oracle GL responsibilities there will be a set of books assignment. We can see the information forthat set of book only. The navigation in that responsibility is Setup ->Financials ->Books-> Define.

Legal Entities: It is a legal company for which we prepare physical or tax reports. We will assign tax identifiers and other legal information.

Operating Units: It is one of the organizations where exactly company is being sales, purchasing payments and receiving and so on. It may be sales office or a division or a department. At operating unit level PO, AP, AR, OM modules will be implemented.
Inventory Organization: It is an organization where we are going to maintain manufacturing information and items information. It captures inventory transactions, balances. Oracle inventory, WIP, BOM will be implemented at inventory organization
level.
Sub Inventory: Sub inventory is nothing but stocks or goudons or warehouses, which will be define under inventory organization.
Locators: Stock Locators is nothing but the specific location inside the sub inventory where we will place the item.
Note:
· When ever we write a statement like SELECT * FROM OE_ORDER_HEADERS then it shows “NO ROWS RETURN” as an output.
· When ever we are working with the Multiorg views if we want to get the data from those views we have to set the ORG_ID of which           Operating unit it is belongs by using the API (FND_CLIENT_INFO.SET_ORG_CONTEXT(ORG_ID)). Then we get the required                        information from the view.
· We found the list of ORG_ID’s in the HR_OPERATING_UNITS.
· To find the system is in Multiorg or not the query is
EX: SELECT MULTI_ORG_FLAG FROM FND_PRODUCT_GROUPS;
O/p: Y  If it is a Multiorg system otherwise it is N.

Flex Fields: A flex field is a flexible data field that your organization can customize to your business needs without programming. Oracle Applications uses two types of flexfields, those are given below.
1. Key flexfields(KFF) and
2. Descriptive flexfields(DFF).
Key Flexfields: Key Flexfeilds are used to store the key information of data in segments. We can call these segments as intelligent keys. For KFF we define flexfeild qualifiers and segment qualifiers. The data of this key flexfeilds will store in segment cols
in a table.
EX:
1. Accounting Flexfeild (GL)
2. System Item(INV)
3. Item Catalog(INV)
4. Item Category(INV)
5. – –
Descriptive Flexfields: The descriptive flexfeilds are used to add the additional information to the existing data. These DFF values will store in attributes column intable. There is no need to define Flexfeild qualifiers and Segment qualifiers in DFF.· The table FND_FLEX_VALUES will help us to retrieve the flexfields data in our programs.
Existing DFF Customization:
 1.Go with the Application developer responsibility and in that go through the
navigation Flexfeilds ->Descriptive->Registers. Query the records as per the table name and retrieve.
2. Copy the Title and go to the Segments form the navigation is Flexfeilds ->Descriptive ->Segments. Query the records as per             the title.
3.Uncheck the Check box Freeze Flexfeild Definition.
 4.Now go with the Segments button and attach new column and we can attach valueset.
5. Click the open button and make that as field as required (or) optional by
checking / unchecking Required box.
 6.Even we can select default value type. We can also change the size and save it.
7. Now check the Freeze Field Definition.
 8.Click compile button. Save it and close.

Note: When ever we click compile button system will submit Flexfield View Generator concurrent program internally to create descriptive flexfeild view.

INTERFACES:
It is nothing but a program; it will be used to transfer the data from flat file to database 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

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

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.

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.

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.

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 records 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.

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. Theprogram will ask for six parameters. The navigation is Import ->Import Items in the Inventory responsibility.

Slide16

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

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 orspread the data into batch which makes import easy. If we provide NULL for theirrespective 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:
1. 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.
 2.Items cannot have pick the components = “YES” when ever the MRP planning method is not defined.

Note:
1.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.
2. 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.

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)
);

/*————————————————————————————————-
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.

/* ————————————————————————————————
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;

–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;

–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 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;

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.

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)
);

/*————————————————————————————————-
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.

/*————————————————————————————————-
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;

–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

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;

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.

/*————————————————————————————————-
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);

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;

–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,

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( 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;

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.

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.

Slide1

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 thatrepresent 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.

Slide2

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.

Slide3

Distributions: When ever we click on the Distributions button on the Requisitions window then we will get this distributions window.

Slide4

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.

Slide5

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.

Requisition Summary: To Check whether the requisition is approved or not we will gowith the navigation Requisitions ->Requisitions Summary then we will get the screen given below enter the requisition number and press find button.

Slide6

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.

Slide7

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.

Slide8

Creating Requisition Number Manually:
Go With navigation Set up ->Organizations ->Purchasing Options and go to Numbering Tab.

Slide9

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 createdand 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 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.

RFQ(Request For Quotation):

Slide10

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.

Slide11

The table for Currency Screen: FND_CURRENCIES

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

Slide12

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.

Slide13

The table affects this PO_LINE_LOCATIONS.

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

Slide14

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.

Slide15

Enter the requisition number, which we created earlier and go with the find button. Then we will get the screen given below.

Slide16

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:

 

Screenshot_8

 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.

Slide18

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

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.

Slide19

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.

Slide20

 DISTRIBUTIONS: When we click on the Distributions button in Shipment window we will get this window.Slide214. 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.

Slide22

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

Slide23

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.

Slide24

After entering the Po number then Click on the find button we will get the Purchase order Headers window.

Slide25

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

Slide26

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.

Slide27

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.

Slide28

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.

Slide29

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.

Slide30

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).

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.

Slide31

4. Go with Distributions Button and Enter distribution details.

Slide32

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.

Slide33

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

Go with Actions Button and check the check box Validate

Slide35

After this the Invoice Status will be change to Validated.

Slide36

Again Go with Actions Button check the check box Create Accounting

Slide37

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 upinvoicedistribution sets
1. Enter distribution set name, percentage and account transactions.
2. Attach this distribution set while creating invoice of header level.

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.

Slide38

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.

Slide39

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

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.

Slide41

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

Slide42

Select find button it will shows the payment details.

Slide43

The table’s affects GL are
1. GL_JE_HEADERS
2. GL_JE_LINES
3. GL_JE_BATCHES

Summary
Review Date
Reviewed Item
Oracle Applications
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Oracle Applications"

Leave a Message

Your email address will not be published. Required fields are marked *

Site Disclaimer, Copyright © 2016 - All Rights Reserved.