Oracle Applications11i-introduction

ERPs available market o o o SAP Oracle Applications People soft Siebel JD Edwards

Oracle Applications are used to capture Business functionality information of the `Organization Instances o Development Development o o Development sage 1 Development state 2 Testing Testing Production Client testing

Involved Personalities of Oracle Applications and their roles and responsibilities in brief

1) DBA- Database Administrator a. Installation of Oracle Application b. Applying patches c. Maintaining Multiple instances 2) Functional Consultant a. Interaction with the client b. Gathering the requirements for development/ customization c. About oracle applications what available and what wasnt available. d. Example invoice form fields - Or develop a invoice form from scratch e. Preparation of FDD(Functional Design Document) 3) Technical Consultant Input FDD a. Go through the FDD b. Prepare TDD (Technical Design Document) Logic, Tables, Procedures, Forms, Menus, Packages-approval c. Development of Component (Triggers, Procedures, Function--) d. Test the Component e. Deliver to the client All these sessions will go trough the below mentioned components

RICE Components for development 70% R I C Reports Interface Conversion Reports 6i Programs, SQL, PL/SQL, Programs, SQL, PL/SQL Forms 6i

E Extension

Function Knowledge 30% Purchase Order Module Accounts Payable Module Order Management Module Inventory Module

CLASS 2

I)

Types of Projects

a. Implementation Project: Client will be using another software to maintain the business solutions. Now the client wants to have Oracle Applications package to capture the data. That time we will start setups. Data conversions, gap analysis, functions document preparation from the scratch first time

b. Customization Project: Client was already using Oracle Applications, now the client would like to have few more modules to cover the business, for that we will customize new modules and integrate with existing modules.

c. Migration or Up-gradation Project: Client was already using Oracle Applications older version now client would like to move new version of Oracle Applications that time we will migrate older version objects in to new version.

d. Support or Maintenance: Once the implementation, customization and migration are over, then supporting project will be started where we will give supporting for the objects which were already developed, customized and migrated.

II)

Types of Modules

Module: Module is nothing but one application it contains forms, reports and programs which are related for specific business functionality. Example: PO Module This will be used to capture purchasing information like suppliers, their contact details, supplying material, unit price and discounts and so on. ERP Financial AP - Accounts Payable AR Accounts Receivables CM Cash Management GL General Ledger PA Project BOM Bills of Materials OM Order Management Production OTL Oracles Time & Labour Mobile Process Manufacturing PO Purchase Order Inv Invoicing HRMS People CRM Customer Relationship and Management Payroll Tele Services

Accounting MRP Schedules Enquiring

III)

Modules Integration:

CLASS- 3

4) Types of Documents: The methodology of behind is AIM Application Implementation Methodology developed by Oracle Corporation

NOTE: Migration Document is two types 1) Installation Process: Installation Stages are three types development, testing, production. If any of the form or report is developed, when it is moving from one instance to another instance, to be moved all the components of that. Example functions, procedure, packages etc. This process will be done by DBA.

2) Execution Process: In this, the execution of the form or report with a screen shoot to be specified along with the functionality.

CR Document: The document tell about the request made by the client, it could be very small change like single field to be added or deleted to the form or procedure to be deleted.

5) Oracle Applications File Architecture

Custom Top: Custom top will be created by (client) for the custom development and customizations. At least one custom top is required for every client. We can have multiple custom tops also as per the requirements

NOTE:

1) We are not suppose to develop are customize the product tops like AP, AR, GL, PO. 2) If oracle applies the patches (scripts) against the application the script will over right both developments and customization whatever we have done. That is the reason we will use custom top. Oracle will not touch the custom top and it will not provide any type of support.

US Folder: This folder is language specific by default American English language will be implemented. If we want to have multiple languages we can implement in Oracle Applications by specifying different folders. This folder is only applicable for Forms and Reports because both are GUI objects. Select * from FND_LANGUAGE where installed_flag in (B,I) Flag B Base language, Flad D Disable language

Flag I - Installed language,

11.5.0 Folder: This oracle application release name based on the release it will be specified

CLASS 4

Oracle Applications Database Design Predefined database Oracle 9i Schema Diagram

User connect only one schema at time Total approximately 4000 schemas available APPS Schema contains only synonyms Per ever database object we will find synonym in APPS Schema Table name and synonym name should be same No module to cover the Share Market Business New Module New Schema New Top

APPS Schema: This one of the Schema in Oracle Applications database it contains only synonyms. It has got access other schema database object.

PO Schema: It contains only PO Objects like PO Tables Indexes, Sequences, and views and materialized views and so on. It will connect to PO Schema we can access only PO Objects. We cant access other schema database objects.

NOTE We are not suppose to create tables in apps schema, we will create the tables in custom schema then we will provide grants to apps schema. While development of forms, reports, programs we will always connect to apps schema in oracle applications. AOL Application Object Library

After objects development / customization the objects to be registered with this. This has got the two responsibilities. 1) System Administrator: Which has got the functions like 1) users, 2) Responsibilities 3)Reports development / customization 4) Procedure 5) Printer Installation 6) Admin activities Server Monitoring, Request submission

2) Application Developer: Which has got the functions like 1) Forms 2) Menus 3) Messages 4) Flexi fields 5) Profiles

After installation of Oracle Applications the default User details as follows User Name : OPERATIONS Password: WELCOME

And connect and select System Administrator responsibility

To create New User Security User Define Enter

To move in the form some shortcut keys 1) If you want to query all the record Ctrl + F11 2) If you want to query few records for form a. Open the form b. Press F11 Query mode c. Search criteria by using % d. Press Ctrl + F11 3) To close the form F4 To connect to database or Bank End User ID: APPS Password: APPS Host string: PRODUCTIONS Creations of New User Front End

1) Connect to the application 2) Enter application User Name : OPERATION and Password: WELCOME 3) Select the Responsibility called System Administrator 4) Open the user Form Security User Define and enter 5) Enter Username and password and attach Responsibility 6) Save the Transaction. 7) Once user is created we cant delete

We can find all the user details in FND_USER where user-name= 20user

Note: we can find the table names from front end by using help menu Help Recort History option

If open a form we can three types of fields

Yellow Color Mandatory Fields, White Color Optional Fields, Gray Color Read Only Effective Dates : From To Some of the records we cant delete in Oracle applications instead of the we will effective dates From and To to enable or diseable the record.

Who column in Oracle Applications

Ever Database table contains following Colums.

CRETED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATED_DATE LAST_LOGIN_DATE

User Id System date User ID System Date System date When we Update Login system date When we create

When we go to Record History option we can find all the who columns Help Record History CLASS 5

Executables .rdf .sh .sql .proC .ctl .prl Report Unix Shell Script SQL ProC Sql * loader Perl

Concurrent Programe

Is nothing but instances of execution file along with parameter nad incompatible programs We can register 11 types of executable as Concurrent Programe We can change the Concurrent Programe output based on employee profile We can submit only Request We can schedule the Concurrent Programe as per client requirement.

Report Registration

1) Develop the report (.rdf) as per client requirement by using Reports 6i Builder 2) Move the report from the local machine in to the server a. CUS_TOP\11.5.0\report\US .rdf (or)

b. PO_TOP\11.5.0\reports\US .rdf 3) Select System Administrator a. Create Executable b. Executable name c. Application Name d. Execution Method e. Report (.rdf) file name 4) Create Concurrent Programe and attach a. Executable b. Parameters c. Incompatibilities 5) Create Request group and attach Concurrent Programe 6) Create Responsibility a. Request Group Concurrent Programe b. Data Group Collection of User IDs c. Menu Collection of forms 7) Create user attach Responsibility to the user 8) User will select the responsibility and to to SRS (Standard Request Submission)

Executable: => Concurrent Programme => Request Group => User => SRS

Note: For Single Executable we can have different concurrent programs with different parameters

To create simple report with below SQL statement Select User_ID; User_name, Trunc(creation_date) from fnd_user CLASS 6

Executable: After moving .rdf in to the server, we will create executable by specifying execution name execution method and application name.

Concurrent Programe: After creation of executable we will create concurrent Programe by attaching executable, if parameters are there we will attach parameter if incompatibility programs are there we will add

those programs, for single executable we create multiple concurrent programs with different parameters.

Request Group: After creation of concurrent program we must add the programme to the request group. Request Group is nothing but collection of Concurrent Programs and Reports.

Responsibility: It is level of authority where we will combine Data Group, Request Group and Menu. Request Group is optional, menu and data group is mandatory.

Data Group: Data Group is nothing but collection of functions (forms) and submenus.

Create a simple report using below query Select * from PO_VENDORS Vendor_ID, Vendor_Name, Creation_Date

Navigation Executable Window Concurrent=> Programme => Executable Concurrent Programem Window - Concurrent=> Programme=>Define Request Group - Security=>Responsibility=>Request Responsibility - Secutiy=>Responsibility=>Define SRS Window View Menu => Requests

To find out output file path and log file path we will write the below select statement Select logfile_name, outfile_name from fnd_concurrent_requests Where Request_ID= CLASS 7

Reports with Parameters Data Model Select * from FND_USER

USER_ID, USER_NAME, CRATION_DATE Parameters From USER_ID, To USER_ID

Layout Model

Designing of layout

User Parameter

P_From P_To P_Tiltle Data Model Change the SQL Query Select * from FND_USER WHERE user_id between :P_From :P_To Comple and save To register the parameter with applications Concurrent Programe window to be opened.

Note: we can change the sequence based on the requirement.

If report is having the parameter then we have to register those parameters at the time of creation Concurrent Programe Value Set: values set is nothing but list values. It will be used to validate values while entering the parametes. Token: Token is one the field wilc be used to map aoncurrent Programe parameters with report builder bind variable. We will enter trhe bind variable name in the token field so that parameters will be passed to the bind variable Enabled Check Box: By using this we can enable or disable the parameter. Display Check Bos: By using this we can hide or display thje parameters in SRS window. Range Option: While defining the from and to parameters if we wanted to accept values in accession order menus from values is low and to valueis high we will select the options called low and high. Default Types: if we are hiding theparametes user cant enter the values that timewe can pass default values by using default type and default value. SRS Window Copy Button: This will be used to find out recent Concurrent Programe list with parameters in SRS Window.

CLASS 8 Default Types

When we are hiding the paremeter in the SRS windows user cant enter the value that time we can press values as internally by using defaults types. 1)Constant: If we want to pass constant values as default then we will select default type constant and we will specify the values in default value field.

2) Current Date: System Date 3) Current Time: System Time 4) Profile: By using the profile option we can pass user profile values as default 5) SQL Statement: When we want to pass select statement to rest as default values that time we will select default types as SQL statement and write the select statement in the default values filed. Select statement should not return more then one value. (If returns the first value will be taken/ error is not shown). 6) Segment When we wanted to pass previous paramenter values as default to the next parameter then we will use segment, select default type as segment give the paramenter name in the default value field.

Report Builders Data Model Select User_ID, User_name, Trunc(creation_date) from fnd_user, Layout Model Object Navigator User Parameters 1) P_From_Date Date Input Mask 2) P_To_Date - Date Input Mask 3) P_Title Character 100

Date Model Select User_ID, User_name, Trunch(creation_date) from find_user where trunc( creation_date) between P_From_Date and P_To_Date

Create a Sumary Column Count of UserIDs Oracle Applications To register the parameter P_From_Date-Values Set-FIND-DATE Default Values. P_From_Date Current Date P_To_Date Current Date P_Title- Constant To hide parameter uncheck the Display Check box in bottom of the form To write SQL Statement Min and Max creation Date Select MIN(TRUNCH(CREATION_DATE) FROM FND_USER

Select MAX(TRUNCH(CREATION_DATE) FROM FIND_USER Default values type SQL Statement option Default Values field SQL Statement Segment Default Typ as Segment Option Default Values Specificy the previous paramenter VALUES SETS- Application Validation Set [ ResSystem Administrator (or) onsibility Application Developer]

Value set is nothing but list of values with validation. It will restrict the user to enter valid values. These are 8 types. 1) Note: When we write maintain some formal conditioning NO LOV Emp No: => Only nos. 0-9 => 35 to 7856 =>45 to 0045 2) INDEPENDENT: Yes LOV use must select values from the list 3) DESPENDENT: YesLOV The value which will depend upon the previous parameter

Country Code:

City Code:

i.e. based on the selection of country code lov city code lov will change

4) TABLE: Combination functionally of independent and Dependent to provide database table name and column name. 5) TRANSLATED: Independent Multi language values

6) TRANSLATED DEPENDENT: Dependent Multi language values 7) Special: To Display Flexi field data 8) Pair: To Display Flexi field data.

CLASS-9 1) NONEa. When we want to restrict the use to enter the values based on some conditions then we will use NONE type. Here no list of values user manually will enter the value based on the conditions values will be accepted. b. Enter the values set name select format type enter the minimum size select validation type as NONE. c. Once the value set is created we can use it for any concurrent program either single time or multiple times. d. Once the value set is created we cant delete if value set is being used by concurrent program if we want to delete release from the concurrent program the we can delete by using delete option.

2) INDEPENDENTa. Provide the values to the user either number or character creates the independent value set to enter the values for the value set. Application-Validation-Values. b. If we want to provide list values to the user we will go for selecting independent values set type user must select the values from the list. c. Open the value set for an enter value set name select format type and select validation type as Independent. d. Copy the value set name go the values screen enter the values set name and click Find enter the values in the values field save the transaction attach value set concurrent program. e. Once the values are inserted in to the list we cant delete instead to deletion we can disable by using enable check box or effective data from and to

3) DEPENDENT a. Dependent value set is nothing but nothing LOV but values will be changing based on the previous independent value set. b. When we are creating dependent we must have 2 parameters 1) Independent 2) Dependent c. Open the value set form create Independent value set by selecting d. Open the value set form create dependent value set by selecting e. Select edit information button attach independent value set what ever we have created. f. Copy the value set name go to values screen click on find button enter the values based on the Independent values. Independent defendant values

CLASS -10 4) TABLES Select user__name, user_id, creation_date From FND-USER Where row num < 20 Order by User_name desc a. If values are available in data table then we will go for using table value set, we will give the table name and column name system will retrieve the values from the database table. b. Open the value set form and give the value set name select validation type is table click edit information button. c. Enter the table click edit information button. d. Enter the where clause order by clause in the text item called where order by e. Use the additional column field to display extra columns data in LOV, we have to use allas name while specifying additional columns.

f.

Table Application. It is as optional field based on the table name we can find out table application name.

g. Go to Application Developer Responsibility Application=>Database=>Table query the records based on table name. h. Select User_ID, USER_name from FND_USER From front end user name should be available and internally user_id to be passed. i. j. Id Column in Value set form if we want to display one column to the user in the front end and pass another column value internally we will use ID column. Meaning Column This will be used to display the extra columns like additional columns it will work like additional columns.

k. Display the values from multiple tables i. ii. iii. iv. At the time of giving the table name enter table names with alias name by specifying. ( , ) At the time of selecting give the column name by giving the alias name, column name. Join condition is mandatory in where / order by clause. PO_VENDORS, PO _VENDORS_SITE_ALL.

Select pv.vendor_name, pvs.vendor_site_code From po_vendor pv, po_vendors_sites_all pvs Where pv.vendor_id = pvs.vendor_id

5) TRANSLATED INDEPENDENT & TRANSLATED DEPENDENT a. These two value set will work like independent and dependent value set but these two value set will be used to display translation values (other thane English language values) if application is installed for mlti language there we will create these two value sets.

Select * from FND_LANGUAGES I Installed languages B Base language D Disabled language 6) SPECIAL & PAIR a. These two value sets will be used to display flexi filed data. CLASS 11

Installed_Flag -

Develop the below report with parameters CPLISTREP.RDF From Date To Date Title : P_From_Date : P_To_Date : P_Title

CP Name

C Date

Application Name

Executable

Executable Method

When the width of the report is more we have chose the style of the output in Concurrent program window BACS instead of A4.

Query Select fcp.user_concurrent_program_name, Trunk(fcp.creation_date) C Date.

fav.application_name. fev.executable_name. fl.meaning from fnd_concurrent_programs_vl fcp, fnd_application_vl fnd_executables_form_v fnd_lookups where and and and fcp.application_id fcp.EXECUTABLE_ID fl.lookup_type fav, fev, fl = = fav.application_id fav.EXECUTABLE_ID

=CP_EXECUTION_METHOD_CODE fl.lookup_code&p_lexical

fcp.execution_method_code =

Parameters: Lexical parameter to change the query dynamically. Bind parameter to be register before Oracle applications.

If title parameter is null i.e. if user doesnt pass the value to the title parameter

Format trigger. Begin

If : P_Title is NULL then Return (True) Else Return (False) End if

If parameters were not passed to the :P_From_Date and :P_To_Date report should display all the records.

After Parameters Form Trigger Begin If: P_From_Date is NULL then Else :P_lexical:= and Trunc(fcp.creation_data) Between :P_From_Date and :P_To_Date End if

End ================= Exercise From User ID, To User ID, From Date, To Date Title User_ID User_Name CDate Responsibility Application Security Name group From Date To Date

CLASS 12

Query prepared by SIR in class Master Level

SELECT USER_ID, USER_NAME, TRUNC(CREATION_DATE) CDATE FROM FND_USER WHERE USER_ID BETWEEN :P_FROM_ID AND :P_TO_DATE

Detail Level

SELECT FU.USER_ID, FRV.RESPONSIBILITY_NAME, FAV.APPLICAITON_NAME, FSG.SECURITY_GROUP_KEY, TRUNC(FRG.START_DATE) FROMDATE, TRUNC(FRG.END_DATE) TODATE FROM FND_USER FU, FND_USER_RESP_GROUPS FND_REPONSIBILITY_VL FND_APPLICATION_VL FND_SECURITY_GROUPS_VL WHERE FU.USER_ID AND FRG.RESPONSIBILITY_ID AND FRV.APPLICATION_ID = = = FRG, FRV, FAV, FSG FRG.USER_ID FRV.RESPONSIBILITY_ID FAV.APPLICATION_ID FSG.SECURITY_GROUP_ID

AND FRG.SECURITY_GROUP_ID =

Steps to develop a report Data Model Data link to be given between both the quarries. Adjust the margin and comeback to main page Report width to be increased 13 135 Select the main from and other frames expand variable Place all the text fields. Take the repeating frame and source to master query o Put all fields which are related to master query and what ever are needed.

Take another repeating frame and place that on the first repeating frame and source to detail query. Put all the fields and no fill and no line

Select all the fields and no fill and no line Define the User parameters P_From_ID and P_To_ID Applications o Create the table value set for select the parameters

Standards to develop a report in Oracle Applications 3 STANDARD STEPS.

1) Define the mandatory parameter called P_CONC_RQUEST_ID This is one of the bind variable when ever we submit a request is SRS window that request ID will passed to this bind variable, based on this request id we can execute concurrent request process. Without this find variable we cant use userexits. 2) Call the userexit in Before Report Trigger SRW.USER_EXIT(FND SRWINIT) 3) Call the userexit in after report trigger SRW.USER_EXIT(FND SRWEXIT)

Report Trigger

1) Before Parameter Form Trigger 2) After Parameter Form Trigger 3) Before Report Trigger Before retrieving the data from database 4) Between Pages Trigger when ever courser goes between pages at first time. 5) After Report Trigger After out is reached the destination i.e. printer, file, email.

USER EXIT

It is one of the predefined program in reports 6i will be used for stop the report execution process for sometime and transfer the control to the 3rd Generation Language get the data and completes the remain execution process.

We have 5 types o User Exits available in Oracle Applications.

1) FND SRWINIT: We will use this user exit in the before report trigger. It will initialize user profile values according to that profile values data will be retrieved from database. SRW.USER_EXIT(FND SRWINIT);

2) FND SRWEXIT: We will call this user exit from after report rigger to freeze the memory which is occupied by user profile values. SRW.USER_EXIT (FND SRWEXIT)

3) FND FLEX SQL 4) FND FLEXIDVAL 5) FND FORMATCURRENCY CLASS 13

Purchase Order Application: Group of Forms, Reports and Programs

Before going to start PO application we have to define following things 1. Items Creation 2. Suppliers Creation 3. Buyer Creation

1) Item Creation a. Open the user from in System Administrator Responsibility attach the responsibility called Inventory, Vision Operations (USA) b. Open the Items from Items Master Item enter, system will show the organization list, select the organization vision operations, enter the item name and description. c. Go to Inventory tab check the check box called Inventory Item. d. Go to purchasing tab check the check box called purchased and save the transactions. e. We can assign the item to the multiple organization by selecting tool menu Organization assignment option check the check box called assigned. Select * from ORG.ORGANIZATION_DEFINETIONS -Organization_ID is primary key column

Select * from MTL_SYSTEM_ITEM_B Where sgment1=keyboard -Inventory_Item_ID -Organization_ID - Composite Primary Key

Select MSI.SEGMENT ITEM OOD.ORGANIZATION_NAME From MTL_SYSTEM_ITEM_B MSI ORG_ORGANIZATION_DEFINETIONS OOD Where MSI.ORGANIZATION_ID And MSI.SEGMENT1 = = ODD.ORGANIZATION_ID KEYBOARD

2) Supplier Creation a. Select the responsibility called Purchasing, Vision Operations(USA) b. Open the suppliers form Supply Base Supplies press enter c. Enter the suppliers name and save the transaction and we will get supplier number select status button enter site address select contact tab enter contact details and save

Select * from PO_VENDORS where SEGMENT1=5080 _VENDOR_ID Primary Key Select * from PO_VENDOR_SITES_ALL where VENDOR_ID=1010 _VENDOR_SITE_ID Primary Key Select * from PO_VENDOR_CONTACTS Where VENDOR_SITE_ID Primary Key CLASS 13

3) Buyer Creation: a) Attach the Responsibility called HEMS Management; Open the Employee form (N) HRMS Manager People _ Enter and Maintain. b) Select new button enter employee name and Date of Birth, select action as creation employment chose the option called buyer and save the transaction.

Select * from PER_ALL_PEOPLE_F Where EMPLOYEE_NUMBER = 893

PERSON_ID is Primary Key column

c) Go to system administrator create user name attach employee name in the filed called Person and save the transaction Select FU.USER_NAME PPF.FULL_NAME From FND_USER PER_ALL_PEOPLE_F Where FU.USER_NAME And FU.EMPLOYEE_ID FU. PPF = 20USER = PPF.PERSON_ID

d) Copy the employee name go to purchasing application open the Buyers form attach to the Buyers list Setup Personnel Buyers and enter. Select * from PO_AGENTS This contains only Buyer list and primary key is AGEN_ID

PO PROCESS

1) Requisition: It is one of the purchasing document will be crated by employers when ever they required goods or services or training, we will find 2 types of requisitions. a) Internal Requisition: This will be created, when we receiving the materials from other organizations (Branches) b) Purchase Requisition: This will be created, when we are receiving the materials from outside means suppliers. c) We will enter the requisition information at 3 levels i.e. Headers, Lines, Distributions. d) One header will be there at least one line multiple line we create for every line at least one distribution or multiple distributions will be there. e) Open the requisitions form Requisitions requisitions and enter. f) Select the requisition type at header level enter item details at like level (item Name, Quantity, Price and need by date) select distributions button enter distribution details like quantity and account details and save the transactions.

g) Select approve button press OK system will send the document approval. h) Go to requisition number select find button, it will show requisition approved status. Select* from PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1 = 1656 Primary key REQUISITION_HEAD_ID

i)

Cancel the Requisition: Open the requisition summery form enter requisition number select find button go to tools menu select option called Control and chose the option as cancel requisition.

Select AUTHORIZATION_STATUS from PO_REQUISITION_HEADER_ALL

j)

We can find out requisition history by using tools menu option called view action history. Select * from PO-ACTION_HISTORY Where OBJECT_ID = REQUISITION_HEADER_ID Select * PAH.ACTION_CODE PDF.FULL_NAME From PO_ACTION_HISTORY PER_ALL_PEOPLE_F Where OBJECT_ID = 11459 And PAH.EMPLOYEE_ID = PPF.PERSON_ID PAH PPF

k) Enter manual requisition Number. Setup Organization Purchasing Options and enter go to numbering tab select requisition number entry as manual type. CLASS 14 2) RFQ REQUEST FOR QUOTATION: It is one of the purchasing document after requisition is approved. We will go for creation of RFQ we have 3 types of RFQs. a. Bid: If company is going to purchase large number of items which are expensive. We will create bid RFQ where we will specify Headers, lines and shipments, where we are not specify any price breaks in Bid RFQ. b. Catalog: If company is purchasing materials regularly fixed quantity location and date, then we will select catalog regularly we can include price breaks at different levels. c. Standard: This will be created for items we need only once or not regularly, we can include price breaks at different quality levels.

3) QUOTATIONS: After creation of RFQ document we will send this document to the different suppliers who are going to supply the materials, next we will receive quotations from the suppliers either by email or fax or by phone. a. Again quotation are 3 types i.e. Bid, Catalog and Standard b. What ever the quotation we are received from the suppliers we will enter those quotation details in the system for future purpose.

c. If we are sending bid RFQ to the suppliers we will receive the bid quotation for catalog RFQ we will receive standard quotation. d. RFQ Terms and conditions. i. Payment Terms At the time of creating RFQ document we will specifying the payment terms like due date, interest rates and so on. ii. Fright Terms: It is nothing by at transportation charges whether supplier will bear it or buyer. iii. FOB(Food on Board): It is nothing by at transportation of material damage or material missing during the transportation. iv. Carrier: We will give the transportation company name so that supplier will supply the materials by this transportation company. e. Navigation Open the RFQ form RFQs and Quotation RFQs and enter select RFQ type from header level enter ship to location, Bill to location details select line level enter item details select price breaks conditions details, select suppliers button and enter supplier name who are going to receive RFQ document Select * from PO_HEADER_ALL Where segment1=306 Primary Key PO_HEADER_ID Select * from PO_LINES_ALL Where PO_HEADERS_ID = 1845 Primary Key-PO_LINE_ID Select *from PO_LINE_LOCATIONS_ALL where PO_LINE_ID 12233 Primary Key LINE_LOCATION_ID Select *from FND_CURRIENCIES Primary Key CURRENCY_CODE Select * from AP_TERMS Primary Key-TERM_ID Select * from AP_TERMS Primary Key TERM_ID Select * from AP_TERMS_LINES Select * from HR_LOCATIONS Primary Key- LOCATION_ID

f. i.

AUTO CREAT OPTION: This is one of the feature application to create RFQ or PO documents automatically based on the approved requisition document.

Create requisition and approve it

ii. Open the auto create form select clear button enter requisition number select find button which display requisition then details.

Iii Select the line by the checking the checkbox select action as create document types as RFQ iv. Select automatic button click the create button which will create RFQ document will selected requisition lines and displays RFQ No. g. QUOTATION:- RFQs and Quotation Quotation and enter i. We will receive the quotations from suppliers against the RFQ either by fax or email we will enter those quotation details manually in the system to make the quote analysis and future purpose.

ii. Quotation tabes: One we create the quotation from front end data will be stored in the RFQs tables only by column type_lookup-code=quotation. Select * from PO_HEADER_ALL WHERE AND SEGMENT1 =500 TYPE_LOOKUP_CODE =QUOTATION

CLASS-15 4. Purchase Order : It is one of the purchasing document at the time of purchasing from the supplier we will create this document by specifying terms and conditions and shipping details distribution details and so on. We have 4 types of Purchase Order a. Standard Purchase order b. Planned Purchase order c. Blanked Purchase Agreement d. Contract Purchase Agreement

Purchase Order Type Summery

Standard Purchase Order When we require the materials from suppliers we will create standard PO by specifying terms and conditions price, quantity and so on.

Select * from PO_HEADER_ALL Where segment 1=3445 and type_lookup_ld = STANDARD Select * from PO_lines_all where PO_Header_id = 11858 Select * from PO_Line_locations_all where Po_line_id=12216 Primary Key LINE_LOCATION_ID Select * from po_distributions_all where line_location_id Primary Key PO_DISTRIBUTION_ID

(N) Open the purchase order form Purchase Order-Purchase Order and enter select PO type and supplies information enter the line level details like Items quantity price and so on. Select shipments button enter shipping location details quantity promice date, need by date and so on select distribution button enter distribution details and save the transactions. Select approve button system will submit the document for approvals. Copy the PO number and go to purchase or summary form enter PO number select find button. To cancel the purchase order to the tools menu control option. Tools menu copy document to create the same document. Auto Create o By using Auto Create option we can create purchase orders automatically from approved requisition by selecting document type as Purchase order.

SELECT PHA SEGMENT1 PONUM. PHA. TYPE_LOOKUP_CODE POTYPE, TRUNC(PHA, CREATION_DATE) CDATE, PV. VENDOR_NAME PVS.VENDOR_SITE_CODE (PVC.FIRST_NAME ||,||pvc. LAST_NAME) HL1, LOCATION_CODE HL2, LOCATION_CODE PHA, CURRENCY_CODE PPF, FULL_NAME PHA AUTHORIZATION_STATUS SUM(PLA.QUANTITYPLA UNIT_PRICE)) PHA, COMMENTS FROM PO_HEADERS_ALL PO_VENDORS PHA, PV, SUPPLIER SUPPLIERSITE Contact Ship TO, BillTo, Currency, Buyer, PoStatus LineLevelPrice, PODesc

PO_VENDOR_SITES_ALL PVS, PO_VENDOR_CONTACTS PVC, HR_LOCATIONS HR_LOCATIONS PER_ALL_PEOPLE_F PO_LINES_ALL WHERE PHA SEGMENT1 AND PHASE_VENDOR_ID HL1 HL2 PPF PLA =3449 = PV VENDOR_ID

AND AND AND AND AND

PHASE_VENDOR_SITE_ID = PVS VENDOR_SITE_ID PHASE VENDOR_CONTACT_ID =PVC VENDOR_CONTACT_ID PHASE SHIP_TO_LOCATION ID =HL1.LOCATION_ID PHA. BILL_TO_LOCATION_ID PHA.AGENT_ID =HL2.LOCATION_ID =PLA.PO_HEADER_ID

GROUP BY PHA. SEGMENT1 PHA.TYPE_LOOKUP_CODE TRUNC(PHA.CREATION_DATE) PV.VENDOR_SITE_CODE (PVC,FIRST_NAME ||,||PVC.LAST_NAME), HL1. LOCATION_CODE HL2. LOCATION_CODE PHA. CURRENCY_CODE PHA,FULL_NAME PHA. AUTHORIZATION_STATUS PHA.COMMENTS

CLASS -16 Blanket Purchase Order When ever company would to have the agreement with supplier that time first we will create Blanket Agreement, when ever we require materials we will go for releasing the purchase order. Open the purchase order form select Blanket Purchase agreement and enter the details and approve the purchase order. Select release form either blanket PO Number and Item quantity details select distribution button enter distribution details, select approve button for approvals.

Match Approval 2 way 3 way 4 way PO Qty-100 PO Qty-100 PO Qty -100 Receipt Qty 80 Receipt Qty 80 Invoice Qty 50 Invoice Qty 80 Invoice Qty 80 Invoice Qty 50

At the time of creation Purchase Order in the shipments, release we will specify the Match approval option. Two way matching is nothing but, company rcg 2 document purchase order quantity, price with invoice price.

Three way matching is nothing but comparing 3 document Purchase Order, Receipt and Invoice. Four ways Matching is nothing but company PO,Receipt, inspection and invoice documents. (2 docs) 2 Way PO_(invoice) 100(items) | 100 invoice for 100 item 2 Way (3 docs) PO_RECEIPT_INVOICE 100 (items)|80 (item Received) |80 (invoice for 80 item) 4 Way (4 docs) PO_Reciept_Inspection_Invoice 100(items)|80(items received)|50 (food)30(bad) & rejected|50(invoice for items)

Receipt Documents

1. Standard 2. Direct Delivery

3 Way 2 Way 4 Way

3. Inspection Required

It is one of the purchasing document will be created while receiving the materials form the suppliers we have 3 types of receipt. Navigation Receiving Receipts and enter. Create the purchase order and approve it open the receipts from enter PO Number, select find button, system will show the PO Line details, check the check box save to transactions, system write automatically generate Receipt number

Select * from RCV_SHIPMENT_HEADERS SELECT * FROM RCV_SHIPPMENT_LINES SELECT * FRO RCV_TRANSACTIONS SELECT * FORM ORG_ORGANIZATIONS_DEFINITIONS.

Create PO write 3 lines o Go to receipt generate the receipt for only one Item and enter the PO Number again create another receipt until all the Items over.

Select Receipt_Num From RCV_shippment_headers Where shipment_header_id IN (Select shipment_header_id From rcv_shpment_lines Where PO_header_id IN (Select Po_Header_ID

From PO_header_all Where segment 1 =3452)) CLASS 17 MD050 Functional Design Document Based on above develop Quotation Report Query

MASTER QUERY SELECT PHA.PO_HEADER_ID PHA.SEGMENT1QUOTENO, PHA.QUOTE_TYPE_LOOKUP_CODE TRUNC(PHA.CREATION_DATE) CDATE, PPF.FULL_NAME H1.LOCATION_CODE BUYER, SHIPTO

PHA.QUOTE_VENDOR_QUOTE_NUMBER QUOTEAMT, H2.LOCATION_CODE PHA.CURRENCY_CODE PV.VENDOR_NAME, PVS.ADDRESS_LINE 1, PVS.CITY, PVS.ZIP BILLTO,

FROM

PO_HEADERS_ALL PHA, PER_ALL_PEOPLE_F PPF HR_LOCATIONS HR_LOCATIONS H2, H1

PO_VENDORS_SITES_ALL PHA, PO_VENDORS WHERE TYPE_LOOKUP_CODE AND PHA.VENDOR_ID AND PHA.AGENT_ID AND PHA.BILL_TO_LOCATION_ID AND PHA.VENDOR_SITE_ID AND PHA.VENDOR_ID PV = = = = = = QUOTATION :P_VENDOR_ID PPF.PERSON_ID H1.LOCATION_ID H2.LOCATION_ID PV.VENDOR_ID

LINE QUERY

SELECT PLA.PO_HEADER_ID MSI.SEGMENT1ITEM, MSI.DESCRIPTION ITEMDESC, MC.SEGMENT1 II,II MC.SEGMENT2 CATEGORY, PLA.UNIT_PRICE FROM PO_LINES_ALL PLA, MSI, PRICE

MTL_SYSTEM_ITEMS_B MTL_CATEGORIES WHERE PLA. ITEM_ID AND AND PLA.ORG_ID

= MSI.INVENTORY_ITEM_ID = MSI.ORGANIZATION_ID

PLA.CATEGORY_ID = MC.CATEGORY_ID

Purchase Order Interview Question.

1) What is the Flow of Purchasing Module 2) Where the types of Requisition and table 3) Types of Purchase orders and tables 4) What is the different between standard PO and Blanket PO 5) We have created Item but which is not available in the PO what will be the problem 6) What are the types of Match approvals 7) What are the Receipt types 8) When we enter a Blanket Release data where it will be stored 9) If give the requisition no how to find out corresponding PO Numbers 10) If give the PO Number how to find out corresponding Receipt Numbers 11) What is Auto create and advantage 12) In which table we can find out i) ii) iii) Shipped Quantity Received Quantity Canceled Quantity

13) Can we create Purchase order directly without i) ii) iii) Requisition, RFQ Quotation

14) Can we have the Receipt without Purchase order 15) What are the tables for RFQ, Quotation, Purchase Order CLASS 18 PL/SQL Procedure registration with Oracle APPS.

Create or Replace <Procedure Name>

Errorbuff OUT varchar2(10), Retcode OUT Varchar2(20),,,,,,)

Local Variable Declaration Cursor Declaration Collection declaration

Begin Space for Control Statements like IF

LOOP s Procedure, Function and Package Calling

DBMS_OUTPTU_LINE - ------------> we will not use this function. FND_FILE.PUT_LINE(FND_FILE.LOG, Write message II Variable name) FND_FILE.PUT_LINE(FND_FILE.OUT, Write message II Variable name)

Exception When Others then FND_FILE.PUT_LINE(FND_FILE.LOG, Write message II Variable name)

End <Procedure Name>

ErrorBuff: While registering the procedure in concurrent Programe we are suppose define errorbuf out parameter, which will give the error messages in the log file if any occurs in the procedure.

Retcode: It returns the status of concurrent program either 0-Normal, 1-Warnining, 2-Error

API Application Programe Interface FND_file: API is nothing but Application Programming Interface, instead of DBMS_OUTPUT package we will use FND_FILE.LOG to write in the log file, FND_FILE.OUTPUT to write in the output file.

NOTE: We can register procedure or package procedure as concurrent Programe to do some DML validation and some other programming logic, we cant register function as concurrent Programe.

Registration Steps

Develop the procedure and compile, specification of the procedure will be stored in the Database Select System Administrator Responsibility Crate executable with execution method as PL/SQL Stored Procedure Create concurrent Programe and attach Executable, Parameters, and Incompatibilities. Create the Request Group and the Concurrent Programe Attach the request Group to the Responsibility Attach Responsibility to the User User submit Request from SRS window

Simple Example

Create or Replace Procedure EX20(Errorbuf out Varchar2, Retcode out Varchar2) as L_no number (8) default 1000, L_Name Varchar2(100) Default Mr. Rajan Bhatnagar, Begin FND_FILE.PUT_LINE(FND_FILE.LOG, Write message II L_no) DND_FILE.PUT_LINE(FND_FILE.LOG, Write message II L-Name)

Exception When Other then FND_FILE.PUT_LINE(FND_FILE.LOG, Error has occurred during the execution) End EX20

Procedure with parameters

Develop a Procedure which will take two parameters 1. Vendor ID 2. New Vendor Name System should find weather vendor ID is exist or not if it exists it should update the Vendor Name.

Create or Replace Procedure VEN_UPDATE20(

Errorbuf OUT Varchar2, Retcode OUT Varchar2, V_ID IN Number, V_Name IN Varchar2) as

L_Flag Varchar2 (10) Default Y, L_Name PO_VENDORS.VENDOR_NAME%TYPE;

Begin Select Vendor_name INTO L_name from PO_vendors where vendor_ID=V_ID;

Exception When NO_DATA_FOUND then FND_FILE.PUT_LINE(FND_FILE.LOG, There no records in the Table) L_Flag:=E,

When TOO_MENY_ROWS then FND_FILE.PUT_LINE(FND_FILE.LOG. There multiple records in the tables) L_Flag: = E. End, If L_flag:= Y then Update PO_VENDORS SET VENDOR_NAME=V_NAME Where Vendor_id=V_ID;

Commit FND_FILE.PUT_LINE(FND_FILE.OUTPUT,vendor name changed) Endif; Exception WHEN OTHERS then FND_FILE.PUT_LINE(FND_FILE.LOG. Error occurred while updationg)

End VEN_UPDATE20;

While registering the procedure, if we have user defined parameter we are suppose to define those parameter at the time of registering concurrent program, select parameter button enter sequence number parameter name attach value sets if required attach default types. Token filed will be disabled for procedures, it is enabled for only reports because report builder find variable may or may not be in the sequence order but procedures variable or permits will be always in the same sequence. First parameter values will be passed to the first variable second parameter values will be second variable and so on

Exercise

Develop a procedure to transfer the following details in to the Temporary Table. Create Table Quotation_temp( Quotations Number(8),

Total amount Number(8) );

Create Table Quotation_Details(

Qno Qtype Cdate Buyer BillTo ShippTo Currency

Number(8), Varchar2(10), Date Varchar2(100), Varchar2(100), Varchar2(100), Number);

Program will accept one parameter supplier name LOV Vendor Name Internally Vendor_ID should pass Based on the Vendor_id find the Quotation Details and load in the temp tables;

NOTE: 1. CREATE Two table 2. Define the cursor based on Vendor_ID 3. Transfer the data in to quotation temp and quotation details 4. Define local variable to count to calculation and transfer to quotation_temp CLASS 19 SQL* LOADER Oracle Tool

It is one of the Oracles tool will be used to upload the data from flat files in to oracle database tables.

1. Flat file or Data file: It contains the data in specific format it will be either text file (.txt) or excel sheet. (.txt, dat, csv Comma Separated Value) 2. Control File: It contains SQL * Loader program contains data file path, database table name and column mapping and soon, once we develop the control file we will execute this, that time data will be transfer from file to Oracle Database table, during this data transfer, the following files we be created. The extension of the control files is. Ctl. 3. Badfiles: it contains the rejected records which are rejected by SQL *Loader, because of bad format or data type mismatch and so on. The extension of the bad file. Bad. 4. Discard file: It contains the rejected records which are rejected by control file if we have specified any condition in the control file if record is not satisfying the condition the complete record will be inserted in the discard file. The extension of file is . dis. 5. Lof file: It contains information about control file execution like, execution start time and end time, successful records count, bad records count, if there are any errors in the control file, those error message will be available in segments, the extension of file . log.

The Syntax of line control file is a follows:

Load Data Infile Data file Path Insert into table <Oracle Database Table Name> (Column1, Column2, Column3,..); And save as ctl file For execution Command for ctl file. SQLLDER username/password@hoststring

Control = <Control file Pateh> Class Exercise

Connect to Scott Scheme and create temporary table empno Ename JDate Deptno Number(8), Varchar2(100) Date, Number(8)

Create table emp_details (

Sale Comm Tax Select * from emp_details

Number(8) Number(8) Number(8)

Prepare data file and save in the local machine. Develop the control file like as follows and save it with extension .ctl

Lod Data Infile C:\20load\emp.txt Insert into table emp_details Fields terminated by . Empno, Ename, JDate, Deptno, Sale, Comm, Tax );

Go to Command Prompt select following path o D:\Oracle\proddb\8.1.7\bin>

Executive the control file with SQLLDR Command SQLLDER SCOTT/TIGER@PROD Control = C:\20\LOAD\EMP.CTL

To find path to executive the SQLLDR command is Host Command from SQL prompt which will shows the path where we can executive control file

Select * from v$parameter

CLASS -20 Control File INSERT Table should be empty-New records will be inserted. APPEND New records will be add for existing records TRUNCATE It will delete existing records and inserts new records from the file. Csv file (Comma Separated Valu) If data in excel sheet we can save that file as csv file

If any string contains commas we will enclosed this will in double quotation in the control file we will specify the following statement syntax. Fields Terminated by , optionally enclosed by If one of the field is missing for the database column we can generate sequences and also we can use the some functions like tochar, NVL, Decode, Instr, Rtrim, Trim and so on Training Nullcols Inserts NULL columns if no data is there, we can not use any DDL, DML or TCL command in control file.

LOAD DATA Infile C\20loademp.txt. Truncate into Table emp_details Fields Terminated by , optionally enclose ( Empno emp_seq.nextval. Ename upper(:ename). Jdate Sysdate, Deptno, Sal, Commn, Tax )

Fiexed format control file exampled Sometime data may found in fixed format for example Empno -5, Emname 10, Jdate 11, Deptno2, Sal5, Comm--3, Tax-3 If data is in the fixed format the control file will be developed in different format

LOAD DATA Infile C:20\20\LOAD|FIXED.TXT Truncate Into table amp_details Empno Ename Jdate Deptno Sat Commn Tax position(1:5) position(6.15) position(16:26) position(27:28) position(29:33) position(34:36), position(37:39))

In control file itself we can specify the data and at the time of insertion we specify the specify the condition and we can also specify the discard file pat in control file it self for

LOAD DATA Infile * Discardfile C:\emp_rejected.dis Truncate Into table amp_details When deptno=20 Empno Ename Jdate Deptno Sat Commn Tax position(1:5) position(6.15) position(16:26) position(27:28) position(29:33) position(34:36), position(37:39))

Begain Data Fsfsd fgdfdsf Fsfsfsf fdfdsfsd gsfsdfg gdgd Along with the SQLLDR command we can also use the few command like as bellow LOAD-Will theused to load the first N records SQLLDER username/password@hoststring Load=10 SKIP-TO skip the first n records SQLLDER usename/password@Hoststring skip=10 ROWS It will be used to issue the commit after insert N records for every 10 records internally auto commit will happen SQLLDER username/password@Hoststring rows=10

INIT ORA file contains the default values for the commit point

CLASS 21

Data Uplaoding in to two tables Create Table em1 ( EmpnoNumbwe(5), Ename Varchar2(30)

Job

Varchar2(3))

Create Table dept1( DeptnoNumber(2), Dname Varchar2(3)); Positions of the data in datafile Emp no5, Ename 10, Job1, Deptno2, dname 7 DECODE Statement to be used to Code the A for Anlyst M for Manager and Multile data file cane also be called in single control file

Example control file

Load data Infile C:\20load\multiple.txt Truncate Into table emp1 (Empno Position(1:5), Ename Position (6:15), Job Position(16:16) Decode (:Job, A, Analyst , M , Manager()

Insert Into table dept1 When deptno:= ( Deptno Position(17:18), Dname Position(19:25)) Register Control file as Concurrent Program 1. Develop the control fie and move the both data file and control file in the server 2. CUS_TOP\11.5\BIN\.CTL 3. Create executable by seleting the execution method as SQL *Loader 4. Create concurrent Program attach executable, Parameters 5. Attach the concurrent program to the Responsibility 6. Attach the Request group to the Responsibility 7. Attach Responsibility to the user will submit the request form SRS window.

Example Create the table in APPS Schme Create table MTL_ITEMS( Item ItemDesc ItemCose ItemTax BasicCost Varchar2(10), Varchar2(100), Number(8), Number(8), Number(8))

Basic Cost to the calculated field in the Control file i.e. ItemCost itemTax

Development of Control file

Load Data Infile * Insert into Table MTL_ITEMS Fields Terminated by , Optionally Enclosed by Trading NULLcols ( Item, Item Desc, ItemCost, ItemTax, BasicCost (:Itemcost Itemtax))

Being Data Fdfdsf sgdfg fgdgdgds dgdgdgd gfgdgd hgdghdg Fdfdsf sgdfg fgdgdgds dgdgdgd gfgfdgd hgdghdg

Move the ctl file in the Inventory top i.e. BIN Directory And register the control file with applications. Go to System Administrator and register with the applications and concurrent program and method should be SQL * Loader Data file pat can changed dynamically o o o Infile &1 We muse use the 1 degit We are allowed to submit only one parameter that should be data file path

SQL * Loader with Parameters

At the time of defining concurrent programe select parameter butiton define the parameter specify the &1 in the control file so that what ever the values we have passed that will be replaced in the control file.

Interview Question

1. How to issue the commit in the control file a. By suing ROWS Command 2. What are the types loading we have a. Direct Method Load b. Conventional method load (Default) 3. How to upload the data in to multiple table at with time a. By using WHEN command 4. Can we have the data in Fixed format and how to upload a. Yes, we can have fixed format that time we will use POSITION keyword 5. What is the different between Bad file and Discard file a. Both file contains rejected records Bad file contains records which are rejected by SQL*Loader and Discard file contains which are rejected by Control file.

6. What are the SQL functions we can use in the control file a. TO_CAHR, TO_DATE, INSTER, SUBSTER, RTRIM, LTRIM, DECODE, NVL and so on. 7. Can we use user defined functions a. NO We cant user defined functions 8. If control file rejected more than 10 records error the I would like to stop the process how to do it a. We have a file called INIT.ORA file where we will set parent called maximum errors allowed and max auto commit other parameter also. 9. How to skip specific field data a. By using FILLER command In this position column will not accept the data and it inserts the NULL values

For example ( Empno Ename JDate FILLER Deptno); CLASS 22 SQL * Plus 1. Develop the SQL Program (.sql) 2. Move the file form local machine to sever in to respective path a. CUST_TOP\11.5.0\SQL\ .sql 3. Create executable execution method as SQL*PLUS

4. Create concurrent program and attach executable and parameters 5. Attach concurrent program to the request group 6. Attach request group to the responsibility 7. Attach responsibility to the user 8. Submit request from SRS Window

SQL * Plus Program

Column Column Column Column

User_id User_name

Format Format

9999999999 A25 A11 A50

Cration_Date Format &3 Format

Prompt SQL Report with User Details Select User_ID, User_Name, Creation_Date, &3 from fnd_user Where User_id between &1 and &2

Save as the file.sql and create the parameters in Oracle apps systems administrator and column can be added dynamically

NOTE We can define Maximum 100 parameter We are suppose to use & 1,&2, &3 We are not suppose to skip the sequence We can use same parameter values in multiple places in the script AQL * PLUS concurrent program to execute DDL and Connect to remote database and executes the scripts and so on.

Submission of Concurrent Program from Back End because, sometimes we dont have the access to the front end.

Executable Concurrent Program Request Group SRS We need to use the Application Programe Interface details in 115devg.pdf 21 chapter

FND_PROGRAM.EXECUTABLE FND_PROGRAM.REQUEST FND_PROGRAM.PARAMETER FND_PROGRAM.REQUEST_GROUP FND_PROGRAM.ADD_TO_GROUP

Executable Concurrent Program Attach Parameter Create Request Group Add Concurrent Program R.G

Submission of CP from Back End

SQL PL/SQL Report Report Triggers Forms Form Trigger ) It will be used to submit the Request from Back

FND_REQUEST.SUBMIT_REQUEST ( end.

Sample Program

Declare I_request_id number (10); Begin Find_global.apps_initialize ( user_id, Resp_id Resp_appl_id); I_request_id;= Fnd_request.submit_request ( PO Users Information Script. User Desc , FALSE. 1000, 1200, Email_address), Commit; If I_request_id is !=0

Fnd_file.Put_line (Fnd_file.log Program successfully submitted Request ID is II Else Fnd_file.Put_line(Fnd_file.log Program not successfully submitted); Endif; Exception When other then Fnd_file.Put_line (Fnd_file.log Error occurred during the program submission) End;

NOTE: If we are submitting concurrent program from the Form Trigger we are suppose to pass the 100 parameter. If parameter are not defined, still we are suppose to pass NUL values

Fnd_user Fnd_responsibility_vl Fnd_application_vI responsibility_key application_name

Scheduling the Concurrent program

We can submit the Concurrent program future date by using the schedule button in SRS window 1. As soon as possible: This is default option whenever we submit the request it will submit the as soon as possible 2. Once: It will submit the rest only once for future date. 3. Periodically: We can specify the from_date and to_date to submit program periodically no of. Days months, hours, minutes and so on. 4. Specific Days: If we want submit concurrent program in the specific days we write select this option 5. Save this Schedule: This check box will be used to save the schedule and apply same schedule to other concurrent programs by selecting the button called Apply save schedule NOTE: After schedule the Concurrent program we can also cancel by selecting the cancelbutton CLASS 23 PROFILES A user profile is a set of changeable options that effects the way the application looks & behaves. Profile is one the changeable option it will change the way of application execution, when we crate user in oracle applications we will design the profile values for ever user by selecting any of this level

Profile Levels

User<Responsibility<Application<Site

1. Site Level: Site is the lowest profile level when we install Oracle Applications by default values will be assigned at this level, site level values will be applicable for all the users at installations site. 2. Application Level: Which affects all users working under responsibilities owned by a particular application. If we want to assign profile value to the specific application users then we will select this level specify the application name and assign the value, application level profile value, application level profile value will over write site level values. 3. Responsibility Level: We will select the responsibility name assign the profile value, this is applicable only for the user, who are having access to the specified responsibility. Responsibility will overwrite the both application and site level values. Most of the profile will setup at responsibility level because it will be creating responsibility for ever position in the organization we will also assign the profile values based on position. (which affects are the users working under a specific responsibility). 4. User Level: We will select the user name and assign the profit value this is only for specific user which we have specified at will over write responsibility, application and site level values. (affects a unique application user).

Definition

Application: It is collection of forms, reports and programs which are related for specific business functionality Responsibility: It is also collection of forms, reports and programs which are related for specific position in the organization.

Navigation Application Developer Profile press enter

We will find profile name and validation access levels, weather we can visible the values or we can update the values. Based on the access level we will go to system administrator open the profile form assign the profile values. System Administrator Profile System Press Enter

Navigation

Select profile name and profile level by default site select find button, it will shows profile values at 4 levels

Important Profiles:

1. GL Set o Books Name: It is one of the GL application profile, set of books is nothing but collection of currency, calendar, and chart of accounts. We will assign this profile values at responsibility level based on this currency and calendar and accounts will get change. 2. MO: Operating Unit: This will be used to setup user operating unit name which operating unit user is working, when ever user login to the system by using this profile. System will understand the user operating unit (Brach Name). 3. HR: Business Group: This profile will give the user business group which group user is working. 4. HR: User Type: This profile will give the user employment type which user is permanent, Contract or consultant will be identified. <0> MFG_ORGANIZATION_ID(NAME) PO:MFG ORGANIZATION_ID (USER PROFILE NAME.

5. USER_ID 6. USER_NAME 7. RESP_NAME 8. RESP_ID 9. RESP_APPL_ID

All these are user personal profile values for user will be there whenever we create it contains the values. We cannot change the values. CLASS 24 GL:St of Books: A set of books is a company or a group of companies within oracle applications that share a common account code, calendar and functional currency. If profile values are changed for any user at any level the changes will take affect as soon as users logon or change responsibility.

Currency Calendar Chart of Account

Select Currency_code

Currency

Period_set_name Chatof_account_id Name From gl_set_of_books

Calendar Accounts SOBName

18User 19User 20User

GL:Set of Books Name GL:Set of Books Name GL:Set of Books Name

Vision Operations_USA) Vision Swedan Vsision Itely

USD SEK ITL

Go to systems Administrator and open the profile form and select the profile name and assign the values.

When we assign set of books to the user based on the that set of book name currency, calendar and accounts structure will be changed Create there different users go to systems administration assign the profile values, select user level enter user name and profile name select find futon and assign the user level values. Login with the user name select GL responsibility open the journals form, Journal Entry and press enter we will find the different currencies in currency filed.

Personal Profile To find (N) Help Diagnosis Examine and press enter

Select block as profile we will find profile names in the filed Profile values in the values field

Technical Consultant Job Get Procedure Value Function 2 APIs to be used

FND_PROFILE.GET(Profilename, Local Variable); Local Variable:= FND_PROFILE.VALUE(PROFILE Name);

APIs will be used to retrieve the profile values form Backend like SQL, PL/SQL, Forms, and Reports.

NOTE: Here get() is a procedure and Values() is a function both will be used to get a profile value from the backend Example: In Select clause we cant use procedure that time we can use function.

Simple PO Report

User Name

Responsibility Name : Set Of Books Name : Supplier ID

PO Query

Type

Date

Currency

Select PHA.SEGMENT1 PHA.TYPE_LOOKUP_CODE TRUNC(PHA.CREATION_DATE) PHA.CURRENCY_CODE From PO_HEADERS_ALL PHA Where VUNDER_ID=&PO_VENDOR_ID

PONUMBER TYPE, CDATE, CURRENCY

Report Development Process

Lexical Parameters P_CONC_REQUEST_ID P_USER_ID P_RESP_ID P_SOB_NAME SRW.USEREXIT(SRWINIT) Before Report Trigger SRW.USEREXIT(SRWEXIT) After Report Trigger

And adjust the margin and design the layout

Before Report Trigger :P_RESP_ID:= FND_PROFILE.Value(Resp_name) User Name Username Resp_name responsibility name i.e. personnel profiles GL_SET_BOOK_ID MO: Operating Unit ORG_ID

Access the Profile values from the reports Define find variable to store the profile values, go before report trigger call the following APIs, then use the find variable either in the layout or in the report builder FND_PROFILE.GET(USER_NAME,:P_USER_ID) FND_PROFILE.GET(RESP_NAME,:PRESP_ID) :P_SOBNAME:= FND_PROFILE.VALUE(GL:SET_OF_BOOKS_NAME) PL/SQL Procedure as Concurrent Program

Temp Table - Perfomr_taart User_name Resp_name SOBName ORGName Varchar2(50) Varchar2(50) Varchar2(50) Varchar2(50)

Develop the procedure and register as Concurrent program and user submit the request form SRW sindow system should capture the dynamically and inserts in to the temp table :X=FND_PROFILE.VALUE(ORG)ID)

Select name from HR_OPERATING_UNITS Where organization_id=:x

Chapter 15, 115devg.pdf

FAQs

1. What is profile 2. What are the profiles you have come across in yuour experience 3. Profile levels in sequential order 4. How to get profile value from Backend 5. How to pass profile values as default in parameter Default Types Constant Segment SQL Profile -Concurrent Programe window Parameter At the time of defining parameter select default type as profile and give the profile name in the default values field.

CLASS 25

Multi Org

Multi Org is nothing but implementing multiple organization under single oracle APPS implementation. We will define the multi org levels like follows: Business Group Top level org where employee will be define

Set Books

- Currency, Calendar, is going to create income Tax Documents to Government

Operating Unit -- Where company is going to create income Tax Document to Governments

Operating Unit

-- Where Company will procure, sell, pay, receive, stock

Inventory Organization - Where company will store the product

Sub Inventories

[Inventory <> setup => Organization => sub-inventories]

Stock Locations

Under the Inventory it will come

Items

[Inventor <> setup => organizations => Stock location items

Business Group: It represents the highest level in the organization structure, it can be a major division or operating company or consolidated enterprises, Business Group secure human recourses information. Example: When we request list of employees assigned to the business group where organization is a part.

Set Of Books: it is a financial reporting entity it uses particular functional currency, chart of accounts and accounting calendar, Oracle General Ledger application secure this transaction information like journal entries, balances, we will use the Set of Books to capture these financial information. Legal Entity: it is a legal company for which we prepare physical or tax reports and we will submit company flanking sheet to Government,

Operating Unit: It is an organization where company is going to do the main business operations like sales purchasing payments, receiving, and son, it may be a sales office Or division of department, operating unit information will be secured by following applications like PO,AP,AR,OM,CM Inventory Organization: it is an organization for which we will track inventory transactions and balances, inventory Organization means warehouse, distribution information by inventory organization

-Oracle inventory - Bills of Material - Work in process - Engineering - Capacity - MRP and so on

Again we will define Inventory Organization as 2 levels

1) Sub Inventory 2) Stock location inside of the location we will keep the items. Advantages of Major functions

Data Secure: We can assign users to the particular organization when ever use logon to the application then we can get the data access, which is related to their organization

Multiple Orgnizations Reporting: We can setup Oracle applications implementation to allow the reporting across operating units we can allow the reports either at SOB level or legal entities level or operating unit level We can sell the products from one legal entity and ship the products from another level entity that time we can easily create financial transactions. We can receive the good from the suppliers in any Inventory organization Example: A branch employee will create the PO the we can rce4ive the material in B branch inventory organization but these two organization should be defined under same set of books. When employee creates internal requisitions in one organization thesewe can ship the material from another organization with corrent inter company Inventory.

115multiorg pdf- Implementing Multiple Organizations Chapter1 and 4 multiorg.xls.

CLASS 26 Multi Org Tables PO_HEADER_ALL PO_LINES_ALL Column ORG_ID ORG_ID Text Select *from po_header_all Select *from po_header_all Where org_id=client_info AP_INVOICES_ALL ORG_ID Select *from ap_invoices_all Where org_id=client_info AP_CHECKS_ALL ORG_id Select * from ap_checks_all Where org_id-client_info OE_ORDER_HEADERS_ALL ORG_ID Select * from oe_order_header_all Where org_id=client_info Multi Org table contains multiples organization data. This table names are goint to end with_all and this table contains common column called org_id Multi Org views Theses are created based on, multi org table having the where clause like where org-id=client_info. If we are using multi org views in the RICE components. Ex: PO_HEADER, PO_LINES, AP_INVOICES, AP_CHECKS, OE_ORDER_HEADERS CLIENT INFO: It is one of the RDBMS variable, it contains user operating until code (org_id) whenever user login to the application o Select * from hr_operating_units (you can get all the branch code) To create Operating units navigation o HRMS Responsibility Work Structures Organization -- Description In before report trigger we will write SRW, USER_EXIT (FND SRWINIT)- it will intilialize the user profile value (MO:OPERATING UNIT)

Create 3 users, assign to the different branches by using MO;Operating Unite profile and develop the report, use the multi org tables and register it as concurrentprogram and submit from SRS windows form 3 users we will get all the operating units data in the output instead of multi table, use multi org view. Submit request from SRS. It will display theoutput which is related for user organization. If we are not using the user exist (FND SRWINIT) system will not initalize user profile values including MO operating Unit In SQL & PL/SQL We need to user API SQL>select count(*) from PO_HEADERS_ALL 2777 Pos are created form multiple organizations SQL> select count (*) from PO_HEADERS output 0 becoz system doesnot know the branch working for which branch SQL> Begin FND_CLIENT_INFO.SET_ORG_CONTEXT(204) END; SQL> select count(*) from PO_HEADERS Now it is showing the 1645 Pos fro branch 204 Begin FND_CLIENT_INFO. SET_ORG_CONTEXT(FND_PROFILE.VALUE(ORG_ID) END It will retrieve User ORG_ID Value Table shows details about the Multi Org structure table Organizati on Name Business Group Set Of Books Legal Entity Operating Unit Inventory Organizatio n SubInvento ry Stock Locations HRFV_BUSINESS_GROUPS GL_SET_OF_BOOKS HR_LEGAL ENTITIES HR_OPERATING_UNITS ORG_ORGANIZATION_DEFINI TIONS MTL_SECONDARY_INVENTOR IES MTL_ITEM_LOCATIONSQ MO:Operating Unit MFG_ORGANIZATIO N_ID ORG_ID ORGANIZATION _ID HR: Business Group ID GL:Set of Books Name BUSINESS_GR OUP Table Profile Column

Items

MTL_SYATEM_ITEMS Requestions RFQ Quotations PO PO Receipts

CLASS 27 While developing or customizing the RICE component we will be using Multoryu view. We are not supposed to use the Multi org tables. By using the following select statement we can find whether multi org is implemented successfully or not

Select multi_org_flag, release name from fnd_product_groups

Multi_org_flag Releasename 115.7 Y-Implemeted successfully (Business Group, I.E., Set of Books, operating Unit, Inventory organization) Business_Group_ID: This at Business group level we can find them common column in all the table, whichb are in HRMS tables like o o o o PER_ALL_PEOPLE_F PER_JOBS PER_GRADES PER_POSITIONS

ORG_ID: It is at operating until level it we will find this common column in all the table at operating unit level like o o o PO_REQUESTION_HEADER_ALL PO_HEADERS_ALL PO_DISTRIBUTION_ALL

NOTE: But PO receipt functionality will come at inventory organization level. Because we are crating receipt means we are receiving the goods from supplier. We will be receiving

the material at inventories (stories, gowdans) only. Example: RCV_SHIPMENT_HEADERS table is not having the _ALLit is not having ORG_ID column ORGANIZATION_ID: it is at Inventory Organization level we will find all the manufacturing tables column this ORGANIZATION_ID column including PO Receipts Tables o o o o

MTL_SYSTEM_ITESM_B WIP_ENTITITES BOM_RESOURCES BOM_DEPARTMENTS is not having _ALL _ALL

PO_VENDORS

PO_VENDORS_SITES_ALL is having

Supplier is global we can access from any organization Suppliers sites is organization specific and we create site based on the user ORG_ID sites will retrieved.

FAQs 1. What is application hierarchy or Multi org flow? 2. What is Business Group? 3. What is legal entity and operating until 4. What is meant by multi org table 5. What are module will come at operating unit level 6. Will PO Receipt will come tat Operating Unit level or Inventory Organization level? 7. What is difference between OR_ID and ORGANIZATION_ID? 8. Why there is no_ALL for PO_VENDORS tabe? 9. How to populate ORG_ID from SQL prompt? 10. How to populate ORG_ID from SQL prompt? 11. What is meant by Client_info? 12. When user login in the system how the system will identify user operating unit? CLASS -28 Flex Fields Flex Field is nothing but a normal field but it is a ore flexible in the application either to extend the form functionality or to capture the key information We have 2 types of flex fields in oracle applications 1. Descriptive Flex Fields, 2. Key Flex Fields

1. Description Flex Fields: DFF will be used to capture extra information from the end user without changing form code and without altering database table a. We will use the attribute column while working with DFF b. We will find all the DFF in Application Developer Responsibility i. N-Flex Filed _ Descriptive Register c. To find DFF in form PO Requisition Form] d. Customization of DFF i Open the form in application (PO, AP,) we will find the fields which are enclosed with [ ] it is nothing but a DCF we can enable this DFF by using Application Developer Responsibility ii. Find the DFF title from following navigation 1. Help Menu Diagnostics Examine and enter password as apps and Blocki name $Descriptive Flex field$ we will find the list of DFFs in the fields select Field: PO_REQ_HDR_DESCRIPTIVE_F4 we will find the DFF in the value fields Value: Requisition Header (Oracle Purchasing) iii. Copy the value go to Application Developer 1. N Flex field Descriptive Segments and enter and press F11 enter the title Ctrl + F11 Iv. Un check the checkbox called Freeze Flex Field Definition select segments button enter the column names select attribute column and value set and save the transactions.

vi. Check the check box called Freeze Flex Field Definition click on compile button internally system will submit concurrent program (Flex field view Generation) to compile flex field it will generate one flex field view to capture the data.

Vii Go to the form where DFF is attached, please the cursor, it will open DFF form enter the data save it go to backand query the records. Select Attribute3 Attribute9 Attribute13 Buyertype Buyerlocation, Last Date

From PO_REQUISITION_HEADER_ALL Where segment=15467 Viii Create the value set in system administrator; attach the value set at the time of creating DFF column name in the value set field. Ix We can make the DFF column is optional or mandatory by selecting required check box in the segment form

CLASS 29 x. Context Field: While creation of DFF we can define multiple structures based on the user selection we can change the DFF structure. While defining the context field we will enter context field values for every context value there will be a structure here we can use the entire attribute column from attribute 1 to 15.

1. NOTE : One we use the attributes in one structure we can use the same attributes in another structure, because user can enter only one structure details at a time. 2. NOTE: Once we enter the value in the context field it will be stored in the Database Column called Attribute Category

xi. Global Data Elements: While defining the context field we will enter the context field values based on the value we can change the DFF structure if we want to define any field globally we will select option called Global Data elements, selects se4gments enter the details. 1. One we use the attribute column in Global Data elements it will not be available for the context field values.

2. Key Flex Field: These will be used capture key information which is the code language for every code there will be a specific meaning in the application we will find all the KFFs in the Application Developer Responsibility Navigation

Flex field Key Register and enter we can find the structure in the segments form Navigation Flex Field Key Segments and press enter when we enter the data

Accounting Key Flex Field:

This is one of the built in KFF available in GL application to capture accounting transactions Accounting KFF is nothing but chart of accounts in the set of books when ever we assign the profile (GL: Set of Books Name) to the user based on the this profile system will assign Accounting KFF to the User

********** Set of Books Currency Calendar Chat of Account

********** Select Name, Currency_code Period_set_name Currency Calendar

Chat_of_account_id ChartOfA/C From GL_SET_OF_BOOKS ********** 16USER 17UDER 18USER ********** GL:SET OF BOOKS GL:SET OF BOOKS GL:SET OF BOOKS Vision Operation(USA) Vision Italy Vision Belgium USD ITL BEL 101(5) 50173(4) 50714(6)

Structure Column While defining the KFF we will find structure column field if it is null value then KFF has got only one structure, if there is a structure column that will find multiple structure, open the segments form query the records based on KFF title, we will find structure details For Accounting KFF structure column is CHART_OF_ACCOUNTS_ID And we will get all the structure details from this table GL_CHARTS_OF_ACCOUNTS. All the accounting KFF transaction will be stored in the data based table called GL_CODE_COMBINATIONS

Developing the Reports on KFF Process

1) Call the user ext from before report trigger SRW.USER_EXIT ( FND SRWINIT); FND SRWEXIT);

2) Call another User Exit from After Report Trigger SRW.USER_EXIT (

3) Define the following parameters 1. P_CONC_REQUEST_ID 2. P_FLEXDATA 3. P_STRUCT_NUM segment1 II\nIIsegment2IIsegment3IInII.II\nIIsegment30

4) Call another user exit from before report trigger SRW.USER_EXIT (FND FLEXSQL CODE NUM APPL_SHORT_NAME OUTPUT MODE DISPLAY =GL# =:P_STRUCT_NUM =SQLGL =P_FLEXDATA =SELECT =ALL);

5) Define the Query like follows SELECT &P_FLEXDATA FROM KFF Table WHERE KFF Structure Column = :P_STRUCT_NUM;

6) Define Formula Column and CALL another User Exit from formula column SRW.USER_EXIT (FND FLEXIDVAL CODE NUM APPL_SHORT_NAME DATA VALUE MODE DISPLAY =GL# =:P_STRUCT_NUM =SQLGL =Column Name where the data is available =to get KFF Values we will give formula column Name =SELECT =ALL);

CLASS 31

Flex Fields Reports development

P_FLEXDATA: It is one of the lexical parameter having the default value it is a contribution of all the segment columns, we will use this lexical parameter in the select statement to retrieve the data from database

P_STRUCT_NUM: This is a bind variable will be used to capture KFF structure number

FND FLEX SQL: It is one of the User Exit we will define in the before report trigger to retrieve KFF segments data

FND FLEXIDVAL: This is another user exit will called from formula column to display the KFF data in the output.

1) Call the user ext from before report trigger SRW.USER_EXIT ( FND SRWINIT): 2) Call another User Exit from After Report Trigger SRW.USER_EXIT (FND SRWEXIT); 3) Define the following parameters 1. P_CONC_REQUEST_ID 2. P_FLEXDATA 3. P_STRUCT_NUM 4) Before Report Trigger Function Before Report return boolean is I_NAME Begin SRW.USER_EXIT( FND SRWINIT); I_name :=fnd_profile.value(GL_SET_OF_BKS_NAME); Select chart_of_accounts_id Into :P_STRUCT_NUM VARCHAR@(100);

FROM gl_sets_of_books Where name = I_name ; SRW.REFERENCE(:P_STRUCT_NUM); SRW.USER_EXIT(FND FLEXSQL

CODE NUM APPL_SHORT_NAME OUTPUT MODE DISPLAY

=GL# =:P_STRUCT_NUM =SQLGL =:P_FLEXDATA =SELECT =ALL);

Return (TRUE); End;

5) Go to data model select query SELECT &P_FLEXDATA C_FLEXDATA. GCC.LAST_UPDATE_DATE FROM WHERE GL_CODE_COMBINATIONS GCC CHART_OF_ACCOUNTS_ID =:P_STRUCT_NUM:

6) Take the Formula column and place it in query and edit the PL/SQ editor Function CF_DATAFormula return Chart is begin

SRW,REFERENCE(:P_STRUCT_NUM); SRW.REFERENCE(:C_FLEXDATA); SRS. USER_EXIT(FND FLEXIDVAL CODE NUM DATA VALUE MODE DISPLAY IDISPLAY =GL# = P_STRUCT_NUM = SQLGL =C_FLEXDATA =SELECT =ALL =ALL);

RETURN(:CF_DATA); End;

7) Go to layout model design the report and register with Oracle Applications and submit from the user SRS form

SRW.REFERENCE( ): it we are using bind variable in source column the user exit we will refer to get the latest values for variable and source column Changing the structure number dynamically: KFF structure number is nothing but chat of accounts ID, if we know the user set of books name we can find out chart of Accounts ID. Set of books name is one of the user profile by using FND profile API we can get from report triggers Go to before report trigger declare the local variable write the following API o o L_name varchar2 (100) L_name:=fnd_profile,value(GL_SET_OFBKS_NAME);

Write the following select statement to get the structure number based on the set of books name

Select INTO From Where CLASS-32

Chart_of_account_id :P-STRUCT_NUM GL_SET_OF_BOOKS name=L_name

Develop the report on PO module and display the charge account in PO Distributions Form Line Level ReqNO Type Cdate Lineno Item Dist Level ItemDesc DistNo ChargeA/C

FAQ in Flex Fields 1. What is FLEX FIELD? 2. What is KFF 3. In you experience what are the KFF you have com across? 4. Have u created any KFF? NO 5. Have u created any DF? Yes 6. What is FND FLEX SQL and FND IDVAL? 7. What is SRW REFERENCE? 8. What are parameters we suppose to pass for FND FLEXIDVAL? 9. How to change structure number dynamically? 10. What are the KFF are there in inventory module? 11. Is there any KFF in PO? NO 12. Where the KFF data will be stored? Segments

13. Where the DFF data will be stored? Attributes 14. What in Attribute_Category? CLASS 33

Forms Development Process

1)

We will download the TEMPLETE.fmb from AU_TCP\Resource Folder Application Utilities

2)

Open with Forms6i builder and delete the existing Block Canvas

3) 4)

Create New Bloc, Canvas, Window attach oracle apps Properties Develop the from as per client requirement

Note: We will change the Property called First Navigation Data Block as Datablock Name in the Module Properties. II) Customize folooiwing two trigger 1) Pre-Form -Name Windows Name

2) WHEN-NEW-FORM-INSTANCE-New form Name 3) APP_Custome Package 5) Save the form we will get (.fmb) 6) compile the form we will get (.fmx) 7) Move the .fmb into au_top\11.50\Forms\US\.fmb 8) Move the fmx intc CuS_top\1.50\Forms\US\.fmx 9) Goto Application Developer and create Form 10) Create Function attach form to the function 11) Create Menu attach function menu 12) Attach Menu to responsibility, Responsibility will be attached to the user so that user can open the form and entry/Query the Data. -New Window Name

Develop the User Form

First Form User ID User Name Creation Date Second Form Supplier ID Supplier Name Supplier Creation Date

Template. Fmb: It is a built in form available in AU_TOP resource folder, it has got 21 libraries to support menu functionality standard toolbar functionalities and other oracle aps function like who columns, protiles, fields, multi org and so on.

Form Registration: After moving the .fmb and .fmx in to the server we will take the imx name select application developer responsibility register the form Navigation Application Developer Application Form and enter Enter the .fmx name and application name where the .fmx is located enter user form name Copy the user form name attach to the function

Function: It is nothing but instance of form along with parameter navigation Application Function and enter

 Enter the function name and user function name go to properties tab select type as form, go to form tab attach user form name what ever we have created Copy the user function name attach to menu

Menu: Group of submenus and functions, enter menu name sequence no, prompt, attach user function name in the function field, copy user menu name, go to responsibility form in system administrator attach in the menu field Select the responsibility we will find theform in object navigator window.

Form Setup First step

AU/11.5.0/FORM/US Copy the files in to resource folder like Templet.fmb and appstand.fmb

Second Step Start menu Run- Regedit Hkey_local_machine -> software -> Oracle Forms60_path ------------------------------given the template.fnd file path

CLASS-34 Table registration Note: connect to custom top (wip) wip/wip@prod

1) Create table custom_vendors 2) Grant all on custom_vendors to apps; 3) Conn apps/apps@prod 4) Create public synonym custom_vendors for po.custom_vendors; 5) Exec ad_add.register_table(po,custom_vendors , t, 8,10, 90);

Exec Ad_dd, register_table( P_appl_short_name

P_tab_name P_tab_type P_next_extent P_pct_free P_pct_used 6) Exec ad_dd. Register_column (po ,custom_vendors,vendor_id, 1, number , 10,n,y); Exec Adadd. Register_column( P_appl_short_name P_col_name P_col_seq P_col_type P_col_wodtj P_nullable

P_translate P_pracision P_scale Note: register all columns 7) Commit; default null, default null)

Value sets Who columns Flexfield (dff,kff)

Note: If we are registering table by using AD_DD package we can utilize following function 1) Who columns 2) Flex fields 3) Multi org 4) Profile, Table Value set creation and so on Create Table wip_item_details(

Item ItemDesc ItemDate

Varchar2(50), Varchar2(100), Date

ItemLoc Item Category ItemCost Created_By Creation_Date Last_update_date Attribute_Category Attribute1 Attribute2 Attribute3 Attribute4 Attribute5

Varchar2(50), Varchar2(50), Number(9), Number(9), Date, Varchar2(100), Varchar2(100), Varchar2(100), Varchar2(100), Varchar2(100), Varchar3(100), Varchar2(100),

Grant all on WIP_ITEM_DETAILS to APPS Conn APPS/APPS@PROD; Create Public Synonym WIP_ITEM_DETAILS for WIP_ITEM_DETAIS Table type contains three values T-Table, V-View, S-Synonym Exec AD_DD REGISTER_TABLE (WIP,WIP_ITEM_DETAILS,T,0, 10,90) EXEC ADD_DD REGISTER_COLUMN (WIP,WIP_ITEM_DETAILS,ITEM,ITEM,1.VARCHAR2,50N,Y); All the columns to be registered

To know the table status about registration

Navigation Application -> Database -. Table and press enter And query for the required table.

Implementing who columns

Create two triggers Pre_Insert and Pre_update at Block level Call the following API from the both the triggers. o Fnd_standard.set_who(); This API is available in the library called FNDSQF.PLL

Implementing the calendar to the data field Go to the field properties, change the property called list of values Enable_List_Lamp

Create the trigger called KeyListVal at item level call the following API o Calendar .show () It is available in the library called APPDAYPK,PLL

Attaching the list to the field Record Group->LOV-. Field Create Record Group by enter list of value or by enter select statement Create LOV attach record group Attach LOV to field property called list of values

Reference: 115devg.pdf -24th Chapter

CLASS-35 Development of Master Details Form

Master Form PO-VENDORS Vendor_ID, Vendor_name, Creation_Date Details Form PO_VENDORS_SITES_ALL Vendor_D, Vendor_site_code, Site_Creation_Date, Address1, Address2, City. Develop the form with master table using template,fmb (Primary Key must be selected) Crate detailed data block after selecting the column click next futon, can check the check box called Auto join data blocks, select the button called create relationship, select radio button called based on join condition. Select Primary Key from Master Item, reference key from details item, system will automatically create join condition and select finish button. At the time of selection layout style select tabular format and select number of records to be displayed in the layout. Attach property classes and saves the .Ffmb generate .FMX move in to the custom top.

Practice development

PO_HEADER_ALL PO_header_id,PO_NO, type, Creation_Date POLINE_ALL PO_header_id, item_desc, quantity, Unit_price, Line_total(quantity*unitprice)

Manual Development of Form Create a table in WIP Item, Item_Desc, Item_date,Item_loc. Create new data block by select the manual option Change the following the properties o o o Subclass: Block Database source Name: Table Name Database Column Name: Column Names

Go to canvas create and change the property clas to Frame_Rect Define the Text Item attach following properties o Subclass: Tex_Item o Column Name: DB column

Register the column and compile and generate .FMX

CLASS 36

Descriptive Flex Field Form Development Process

Table should contain attribute column and also it should be registered

Go to application developer open the DFF register form navigation Flex Field Descriptive Register and enter Create New DFF by giving database table name Copy the DFF table go to segments form query based on table, select segments button, enter DFF structure details. Open the temple.fmb select all the attribute column at the time of data block creation Define the field in the Canvas change the field properly database items as no Define the package like follows.

Package Specification: ---------------------PACKAGE DFF_PKG IS PROCEDURE DFF_PROC(EVEN VARCHAR2); END; Package Body: ------------------PACKAGE BODY DFF_PKG IS PROCEDURE DFF_PROC(EVENT VARCHAR2) AS BEGIN IF (EVENT= WHEN-NEW-FORM-INSTANCE FND_DESCR_FLEX.DEFINE( BLOCK FIELD APPL_SHORT_NAME DESC_FLEX_NAME => WIP_ITEMS 10, => => DFF_TEXT. WIPDFF ); => WIP, ) THEN

END IF; END DFF_PROC; END

Call this Package from WHEN-NEW-FORM-INSTANCE Trigger o o Packagename.Procedure name (WHEN-NEW-FORM-INSTANCE); DFF_PKG.DFF_PROC(WHEN-NEW-FORM-INSTANCE); Fnd_flex.event(WHEN-NEW-ITEM-INSTANCE); It will Populate the DFF in the Form.

Call another API from WHEN-NEW-ITEM-ISNATCE Trigger o o

Note : FNDSQF library will support for all the flex field APIS like FND_DESC_FLEX.DEFINE FND_FLEX.EVENT FND_KEY_FLEX Function Security:

 For single form creation of multiple functions at the time of creation function passing the parameter which will change the form functionality. Development a form register in application developer at the time of creation function pass in the parameter called QUERYONLY=YES

Attach the functions to menu and menu to responsibility and responsibility to the user.

NOTE: At the time of crating function select form tab pass the parameter in the field called parameter

Calling another form from the existing form Form 6i feature APPS OPEN_FORM built-in Opens the indicated form. Use OPEN_FORM to create multiple-form application, that is, applications that open more than one form at the same time. NEW_FORM built in Exits the current form and enters the indicated form. The calling form is terminated as the parent form. If the calling form had been called by a higher form, Form Builder release memory (such as database cursors) that the terminated form was using. Form Builder runs the new form with the same Runform options as the parent Form Builder runs the new form with the same options as the parent form. Call_Form, New_form, Open_form FND_FUNCTION. EXECUTE()

CALL_FORM built-in Run an indicated form while keeping the parent form active. Form Builder runs the called form with the same Runform preference as the parent form. When the called form is exited Form Builder processing resume in the calling form at the point from which you initiated the call of CALL_FORM.

CLASS-37 1) Develop both forms and register in application. 2) Place the Button in first form 3) Write the following code in WHEN-BUTTON-PRESSED Trigger.

Find_Function. Execute ( Function_name Open_flag => Second from function Name, => Y

OTHER_PARAMS => P1 =il:PO_VENDORS,VENDOR_ID);

P1 is Second form parameter 4) Open the Second form and define the parameter called P1 5) Go to PRE-QUERY trigger and write the following code to change the query dynamtically. :PO_VENDOR_SITES_ALL. VENDOR_ID :=PARAMETER.P1 --:Blockname .filedname :=PARAMETER, Parametername

6) Goto the WHEN-NEW-FORM-INSTANCE Trigger at from level. Write the following code:

GO-BLOCK(PO_VENDOR_SITES_All;): EXECUTE_QUERY: Sub Menus

Sub menus is nothing but is collection of function and menus we will create the submenus just like menus, then we will attach to main menu by using the field called submenu.

Calling the SRS window from Menu

Go to the function form in application developer (N) Application function

Create new function for the user form called Run Reports, copy user function name attach to menu.

Adding Multiple Request Groups to the Responsibility

Create Request Group is System Administrator Copy Request Group name application short and and Request group code.

Go to application developer create function for the form called Run Report and pass the following parameters in the parameter field o REQUEST_GROUP_CODE = 20 SUPPLIER_CODE o REQUEST_GROUP_APPL_SHORT_NAME=PO o TITLE = 20SUPPLIERS

Hide the menus and function at responsibility level (Menu Exclusions) At the time of creating responsibility we will attach menu by default all the submenus and functions will be available if we want to hid we will go to Menu Exclusion tab in Responsibility form select type as function or menu and give the function name or menu name in the name field.

CLASS-38 Forms Customization

Two Ways 1) Download .fmb from au_top\11.5.0\Forms\resource\US 2) Customs.pll a. When_new_form_instance b. When-new-block-insance c. When-new-record-instance d. When-new-item-instance e. When-Validate-record f. Special g. Zoom

If we want to customize standard oracle forms will going to use custom. Pll by using this we can customize few events in the form. This custom.pll is available in the au_top- resource folder 1) Hiding the fields 2) Chance the prompt 3) Change visual attributes 4) Make field is mandatory 5) Attaching the menu options.

Before going to customization the form should know following details Form Name Block Name Field Name What Customization

When we would like to do

Data capture

Form Name Block Name Minimum Hide the field

:Help About Oracle Applications :Help Diagnostics examine : Amount

When form is opened (when-new-form-instance)

Forms Customization Steps: 1) Download the CUSTOM.pll from AU_TOP\11.50\Resource folder. 2) Open with forms 6i 3) Write the Following Code in the place of Real code Start Here Form_name varchar2(30) :=name_in(system.current_form); Block_name varchar2(30) =name_in(system.cursor_block); Begin If(even_name =WHEN-NEW-FORM-INSTANCE) then if (form _name =APXWCARD and block_name =CARDS) then CARDS.DEPARTMENT_NAME , -BlockName. FieldName PROPERTY_OFF); APP_ITEM_PROPERTY2.SET_PROPERTY( PO_HEADERS.SEGMENT1, PPROMPT_TEXT, PO Number); AP_ITEM_PROPERTY2.SET_PROPERTY( REGIONS.REGION_LONG_NAME, CASE_RESTRICTION, UPPERCASE); End if; End if; (attach the Library called APPCORE2.pll)

4) Compile the Library we can get. Pll (Program link Library) Execute the Library we wll get . pix (Program Link Executable) (File ->Administration-> Compile File) 5) Transfer both. Pll and .plx files into the Resource folder in the AU top. (Note: before going to copy into the server close the complete Application)

CLASS-39 Zoom Trigger

Zoom event will be used to call the another forms Oracle standard forms, by default zoom will be disabled for the form, first we have to enable, the we will go for execution. Open the Custom.pll got the function call zoom available and creat the following code in the place of Real Code Starts here For_name varchar2(30):=name_in(system, current_form); Block_name varchar2(30):=name_in(system, cursor_block); Begin If(form_name =POXROERQ and block_name =PO_REQ_HDR) then return TRUE; Else Returm FALSE; End if; Go to the event procedure and write the following code in the place of Real Code Starts Here Form_name varchar2(30) :=name_in(system.ccurrent_form); Block_name varchar2(30) :=name_in(system.cursor_block); Begin If (event_name = ZOOM) then If (form_name =POXRQERQ and block_name = PO_REQ_HDR) then fnd_function.execute (function_name=>PO_POXRQVRQ Open_flat => Y) End if; Endi if; Ave the custom. Pll and compile this execute the custom. Pll Copy borh .pll and.plx to the resource folder (Close the applications before copying in to the respective top) Special Trigger Total 45 are available. We will use the special trigger to attach the menu options to the form.

FAQs:

!) What are the steps we will follow for the form development 2) What are the libraries are available in TEMPLETE.FMB? 3) How to register table and primary key?

4) Why do we need to register the table 5) How to implement who columns? 6) How to attach a calendar? 7) How to call another form from the standard form? 8) How to implement DFF in the forms? 9) What are the events we can use in custom. Pll? 10) What is meant by zoom? 11) We cant copy the CUSTOM.PL in to the server when applications is opened? a. In oracle application all the forms are developed by TEMPLETE.FMB including Navigator. So that time CUSTOM.PLL IS being used by the application we can not update the library. 12) When we are working in the project we are not able to close all the users application that time how to copy in to server. a. We will be having the link file (crated by DBA), we will copy in to the path only we have to logout and login then we can find the customization effects. 13) How to generate .fmx and .plx in LNIX operating system a. In windows we will use CTRL + T b. In linx we will use F60GEN command F60GEN MODULE=TEST.FMB USENAME/PASSWORD@HOST

F60GEN MODULE=CUSTOM.PLL TYPE=LIBRARY USERNAME/PASSWORD@HOTS 14) In which top we will execute this F60GEN command a. IN AU_TOP\11.50\forms\us CLASS 40 INTERFACE

Interface is nothing but a program will be used to transfer the data from flat files to database table or from database table to flat files with validations. WE have two types of Interfaces like inbound interface Outbound interface.

Inbound Interface It will be used to upload the data from legacy system into Oracle Applications base table

Note: Legacy system is nothing but other applications which is used by client like SAP JAVA, Mainframe so on.

Example: In implementation project we can implement forms and report so that users can enter the data after implementation but the previous data will be available in the legacy system we need to bring that data in the Oracle Applications with validations.

Usage: SQL *Loader, txt, .ctl, dls, bad, log Outbound Inteface It will be used to extract the data from Oracle Applications base tables in to flat files

Example: Client is using to application one is for financial and another one is for manufacturing, we have to transfer manufacture data in to financial applications, so that we can generate the Invoices in financial applications.

Usage: UTL_FILE_PACKAGE UTL_FILE,FOPEN() UTL_FILE,PUT_LINE() -Crate/Open file -Transfer the data in to file

UTL_FILE,FCLOSE() Close file Exercise OUTBOUND INTERFACE We can generate flat file only in specified directory to find directory list Select * from V$PARAMETER Where NAME like %UTL_% INIT.ORA file contains the information about active directory list.

Flat file Requirement PO Number, Type, Creation date, Buyer Name, PO Type STANDARD Purchase order only

Process Steps 1) Develop a procedure or package and register it as Concurrent Program 2) Write the cursor to retrieve the data from database 3) Define the file by using UTL_FILE.FOPEN() 4) Open the cursor for loop and transfer the data in the file by using UTL_FILE.PUT_LINE() 5) Close the cursor and close the file by using UTL_FILE.ECLOSED()

Create or Replace PO_Information ( Errbuf OUT vachar2 Retcode ouT varchar2,) as Cursor c1 is Select pha.segment1 PoNum,

Pha.type_lookup-code POType, Trunch(pha.creation_date) CDate, Ppf.full_name From po-headers_all PHA Buyer

Per_all_people_f PHF Where PHA.type_lookup_code = STANDARD AND I-id Begin L_id = UTL_FILEPOEN(d\....................,PO_ORDERS. TXT W) For c2 in c 1loop Utl_file.Put_line (l_id, c2, PoNum ||$4|| C2, POType||$|| C2, CDate ||$|| End Loop; UTL_FILE,FCLOSE(L_ID); End PO_Information; Inventory Outbound Interface Requirement Fields Items, itemid, Itemdesc, uom, name, id, category CREATER OR REPLACE procedure INV_Out( Errbuf OUT varchar2, Retcode ouT varchar2, F_id in number, T_id in varchar2) as Cursor c1 is select Msi.segment1 itcm, msi.inventory__itemdesc, msi.description itemdesc, msi.primary_uom_code Uom, ood.organization_name name, ood.organization_id id, mc. segment1|| , ||mc.segment2 Category from mtl_system_items_b nlsi, PHA. Agent_id = ppf.person_id;

UTL_FILE.FILE_TYPE

org_organization_definitions ood, mtl_categories mtl_categories mic mc

where msi.orgaization_id = ood.organization_id

and msi.inventory_item_id = mic. Inventory_item_id and msi organization_id = mic. Organization_id and mic category_id = mc.category_id and msi purchasing_item_flat =Y and msi organization_id between f_id and t_id; x_id ul_file.file_type; I_court number(5) default0: Begin X_id;=utl_file fopen(d:\oracle\proddbe\8.17\plsq\temp,invoultdata.data,W) ..selected from v$parameter where name like %utl_file% For x1 in c1 loop I_count =I count+1 Utl_file. Put_line(x_id,x1.item ||,|| X1 itemidesc || , || X1 ulom || , || X1 name || , || X1 id || , || X1 category End loop; End loop; Utl_file.fclose (x_id); Fnd_file.Put_line(Find_file.output,No of Records transfered to the data file :||L_count); Fnd_file.Put_line(fnd_File.Output, ) Fnd File.Put_line(Fnd.Output,Submitted User name ||Fnd_Profile.Value(USERNAME)); Fnd_file.Put_line(fnd_File.Output, ) Fnd_File.Put_line(Fnd_File.Output, Submitted Responsibility name ||Fnd_profile value(RESP_NAME)); Fnd_File.Put_line(fnd_File,Output, ) Fnd_File.Put_line(fnd_File.Output,Submission Date:|| SYSDATE); Exception WHEN utl_file.invalied_operation THEN Fnd_file.put_line(fnd_File.log.invalied operation); Utl_file.fclose_all: );

WHEN utl_file.invalied_filehandle THEN Fnd_file.putline(fnd_File.log,invalid fieldhandle); Utl_file.fclose_all WHEN utl_file.read_error THEN Fnd_file.put_line(fnd_File.log.read error) Utl_File.fclose_ali; WHEN OTHERS THEN Fnd_file.put_line(fnd_File.log,other error); Utl_file. Fclose_all End INV_Out; CLASS 41 Inbound Interface

Process Steps: 1) State Table Cration 2) Control file development 3) PL/SQL Program 4) Submit Standard Program

 Inbound interface will be used to upload the data from legacy system in to Oracle applications base tables We will receive the flat file from the client then we will create staging table upload the data from flat file in to stage table. Develop the PL/SQL program to validate the data weather it is valid or not, if it is valid we will insert in to interface table, if it is not valid we will insert in to error table. Once the data is a available in interface table submit the standard program from SRS windows, and we will transfer the data from interface table to base

Note o o Staging table is required because the flat file structure may or may not be compatibles with base table structure Interface table is required to make the pre validation and populate dynamic records.

GL Jounals Currency Conversi On Daily Conversi On Rates

PO

AP

AR * Customers invoices Receipts

OM * Sales Orders *

* Requisitions * Invoices * RFQ * Quotations * Purchase Orders * Payments

GL Interface

CLASS - 42

GL_INTERFACE_TABLE STATUS: This column will accept any string but we will always insert standards string called new it indicates that we are bring new data in to General Ledge Applications.

SET_OF_BOOKS_ID: We have to enter the appropriate set of books ID, it should be valid set of books id is available in GL_SETS_BOOKS table it is valid, otherwise i8t is invalid.

USER_JE_SOURCES_NAME: We have to enter the journal sources name for the transaction we can findall the valid sources name in the tabled called GL_JE_SOURCES.

USER_JE_CATEGORY_NAME WE have to find out weather journal category is available in the GL_JE_CATEGORIES table. It is available then we will insert, otherwise we will reject.

CURRENCY_CODE: We have to enter the valid currency code in FND_CURREINCES table we can find out weather it is valid or not

ACCOUNTING_DATE and CREATION_DATE: Both columns will accept valid date but that date should be less than or equal to System date.

CREATED_BY: WE have to enter valid user_id from FND_USER table we can identify weather it is valid user_id or not.

PERIOD_NAME: We have to enter valid period name and period should bein theopen status from GL_PERIODS table we can find out weather it is valid period or not, from GL_PERIOD_STATUS table we can find out period is the open status or not. ENTERED_DR and ENTERED_CR: Both columns will accept positive number Debit and credit amount, both debit and credit should be equal otherwise account will be imported as suspense account.

GROUP_ID: WE will enter unique group number while importing from interface table to base table it will be used as parameter. CTUAL_FLAG: This column will accept single character either A or B or E a-Actual amounts, B-Budget Amounts, E-Encumbrance Amounts.

Reference 1 Reference 2 Reference 3 Reference 4 Reference 5 Reference 11 to 20 Chart_of_accounts_id Transactionc_date Je_Batch_id Je_header_id

- Batch Name - Batch Description - Dont Enter any value - Journal Entry Name - Journal Entry Description - Dont enter any values. - Dont enter any values. - Dont enter any values. - Dont enter any values - Dont enter any values.

Reference PDF : 115glup.pdf -Page no.153 Pre requisites for GL Interface 1) Set of books should be defined (Currency, Calender, Chart of Accounts)

2) 2) Currency Conversion Rates needs to be defined. 3) Accounting Period should be defined and also opened 4) Source name and as well as category name should be defined. Process Steps: 1) We have received flat fire from client 2) We have created Staging table as per flat file structure 3) Developed Control file and uploaded data 4) Developed PL/SQL Program to upload the data from stage into interface table i. declare Cusor ii. open cursor iii. Validate each record iv If no invalid record then insert into interface table. 5) Run the jounal import from GL=> Journal => Import => Run i. Give the two parameters 1) Source 2) Group ID 6) Open the Output if status is SUCEESS then take Request ID. 7) Open Journal Enter screen Query the records based on the %requestid% As batch Name Select Review Journal button we can see the journal detailed transation 8) If we want correct the journals we can correct from Journal=> Import=> Correct 9) If we want delete the journals we can delete from Journal=> Import=> Delete Temporary Table Creation

Create table GL_INT_TEMP ( STATUS Varchar2(10)

SET_OF_BOOKS_ID Number(8), ACCOUNTING_DATE, CURRENCY DATE_CREATED CREATED_BY ACTUAL_FLAG CATEGORY SOURCE Date,

Number(8) Date, Number(8), Varchar2(1), Varchar2(10), Varchar2(10),

CURR_CONVERSION Number(8), SEGMENT1 SEGMENT2 Varchar2(100), Varchar2(100),

SEGMENT3 SEGMENT4 SEGMENT5 ENTERED_DR ENTERED_CR ACCOUNTED_DR ACCOUNTED_CR GROUP_ID

Varchar2(100), Varchar2(100), Varchar2(100), Number(8) Number(8), Number(8), Number(8),

Number(8) );

Control File Creation To transfer the data from flat file to staging table.

LOAD DATA INFILE * INSERT INTO TABLE GL_INT_TEMP FIELD TERMINATED BY , OPTIONALLY ENCLOSED BY TRAILING NULLCOLS (STATUS, SET_OF_BOOKS_ID, ACCOUNTING_DATE CURRENCY, DATE_CRATED, CREATED_BY, ACTUAL_FLAG, CATEGORY, SOURCE, CURR_CONVERSION, SEGMENT1, SEGMENT2, SEGMENT3, SEGMENT4, ENTERED_DR, ENTERED_CR, ACCOUNTED_DR, ACCOUNTED_CR, GROUP_ID)

BEGINDATA NEW,1,11-AUG-2001, USD, 11-AUG2001, 1318, A, Inventory, JETFORMS , Corporate , 01 , 000,1410,0000,000,,55 5,555,555,555,66 NEW,1,11-AUG-2002,USD,11-AUG2002,1318,A ,Inventory,JETFORMS, Corporate,01,000,1410,0000,000,55 4,554,554,554,66 NEW,1,11-AUG-2002, USD, 11-AUG2002,1318,A ,Inventory,JETFORMS, Corporate,01,000,1410,0000,000,32 1,321,321,321,66 NEW,1,11-AUG-2002, USD, 11-AUG2002,1318,A ,Inventory,JETFORMS, Corporate,01,000,1410,0000,000,43 1,431,431,43166 NEW,1,11-AUG-2002, USD, 11-AUG2002,1318,A ,Inventory,JETFORMS, Corporate,01,000,1410,0000,000,15 00,1500, 1500, 1500,66 NEW,1.11-AUG-2002, EUR, 11-AUG2002, 1318,a, Inventory,JETFOMRS, Corporate,01,000,141C,0000,000,1600,1600,1600,1600,66 Creation of PL/SQL procedure to transfer the data from staging table to interface table after validation.

CREATE OR REPLACE PROCEDURE GL_IN_PRO(

Errbuf OUT VARCHAR2, Retcode OUT VARCHAR2)

IS --cusor declaration CURSOR gl_cur IS

SELECT Status Set_of_books_id Accounting_data., Currency , ,

Data_created, Created_by, Actual_flag Source, Curr_conversion, Segment1 Segment2 Segment3 Segment4 Segment5 Entered_dr Entered_dr , , , , , , , ,

Accounted_dr , Accounted_dr , Group_id FROMGL_INT_TEMP;

I_currencycodeI_set_of_books_id I_set_of_books_id I_flag I_error_msg I_err_flag I_category L_USERID BEGIN DELETE FROM gl_interface; COMMIT; FOR rec_cur IN gl_cur LOOP I_flgar:=A; I_err_flag:=A;

VARCHAR2(25); NUMBER(3); VARCHAR2(2); VARCHAR2(100); VARCHAR2(10); VARCHAR2(100); NUMBER(10);

--This PL/SQL Block will do the currency validation --end of the currency validation --Category Column Validation BEGIN SELECT USER_JE_CATEGORY_NAME INTO I_CATEGORY

FROM GL_JE_CATEGORIES WHERE USER_JE_CATEGORY_NAME = REC_CUR Category, EXCEPTION WHEN OTHER THEN I_category:=NULL; I_flag:=E; I_error_msg:=Category does not exist; END; Fnd_File.put_line (Fnd_File.LOG,Inserting data into the Interface TABLEIIL_FLAG); --End Category Column Validation --User ID column validation BEGIN SELECT USER_ID INTO L_USERID FROM FND_USER WHERE USER_ID=REC_CUR.created_by; EXCEPTION WHENOTHERS THEN L_userid:=NULL; I_flag:=E I_error_msg:User ID does not exist;

END; Fnd_File.put_line (Fnd_File.LOG,Inserting data into the Interface TABLEIIL_FLAG); --End of Created_by OR UserID column Validation --Set of books Validation BEGIN SELECT set_of_books_id INTO I_set_of_books_id FROM GL_SETS_OF_BOOKS WHERE set_of_books_id:=rec_cur.set_of_books_id; EXCEPTION WHEN OTHERS THEN I_set_of_books_id=NULL; I_flag:=E; I_error_msg:=set of Books ID does not exist;END;

Fnd_File.put_line (Fnd_File.LOG,Inserting data into the Interface TABLEIIL_FLAG); --End Set of books Validation --Status Column validation /*BEGIN IF rec_cur.status = NEW THEN I_flag:=A; ELSE I_flag:=E;fnd_file.put_line (fnd_File.LOG,Status column has got invalid data); END IF; END;*/ BEGIN SELECT currency_code

INTO I_currencycode FROM fnd_currencies WHERE currency_code=rec_cur.currency AND currency_code=USD; EXCEPTION WHEN OTHERS THEN I_currencycode:=NULL; I_flag:=E; I_error_msg:= currency code does not exists;

END; Fnd_File.put_line (Fnd_File.LOG,Inserting data into the Interface TABLE IIL_FLAG); Fnd_File.put_line (Fnd_File.LOG,Inserting data into the Interface TABLE IIL_FLAG); --End of Actual Flag Column validation IF i_flag!=E THEN Fnd_File.put_line (Fnd_File.LOG,Inserting data into the Interface taBLE); INSERT INTO gl_interface(status, Set_of_books_id, Accounting_data, Currency_code, Date_created, Created_by, Actual_flag,

User_je_category_name, User_je_source_name, User_currency_conversion_type, Segment1, Segment2, Segment3, Segment4, Segment5, Entered_dr, Entered_dr, Group_id) VALUES (res_cur.status Rec_cur.set_of_books_id Rec_cur.accounting_date Rec_cur.currency Rec_cur.date_created Rec_cur.created_by Rec_cur.actual_flag , Rec_cur.category Rec_cur.source Rec_cur.curr_conversion Rec_cur.segment1 , , , , , ,

Rec_cur. Segment2 , Rec_cur. Segment3 , Rec_cur. Segment4 , Rec_cur. Segment5 , Rec_cur.entered_dr , Rec_cur.entered_cr , , ,

Rec_cur.accounted_dr Rec_cur.accounted_cr

END IF; I_flag:=NULL; I_error_msg:NULL; END LOOP;

COMMIT; END GL_IN_PRO; CLASS 43

Purchase Order Inbound Interface

CLASS 44

PO_HEADERS_INTEFACE