oracleapps4u: 2009

SQL * Loader :
==================
It is one of the Oracle Tool will be used to transfer the data from Flat files into
Oracle Base tables.
When we are developing the Interfaces or Conversion program in Oracle Applications
then we will be using the SQL Loader Programs.

while working with SQL * Loader we will come across 5 Files.

1)Flat File OR data File:

Data file contains the data in the specific format which we will receive from the
client. this file will be generated by the legacy system(Other Systems) program.
It may be either .txt format
.dat
.csv(Comma Separated View) Excell sheet.
Most of the times we will receive in the (.csv) format

2)Control File :
-----------------
Is nothing but a SQL*Loader Program will be used to load the data from file into table.
we will develop the control File
Execute the Control File.
While developing the Control file we will specify follwoing things.
1)Data File path
2)Database Table Name
3)Column Mapping.
Control file Extension is (.ctl)

3)Bad File:
-----------
when we execute the COntrol file if any records are rejected by SQL *Loader then we
will find those rejecteds record in the Bad file.
SQl loader recjects the records if there are any datatype mismatch
Internal Errors
Data file Format problem
Bad File Extension is (.bad)

4)Discard File:
---------------
when we execute the COntrol file if any records are rejected by Control File then we
will find those rejecteds record in the Discard file.
Control file Recjects the record when the record is not satisfying the record which
are not satisfied the condition specified in the Control file.
Discard File Extension is (.dis)

5)Log File :
-------------

It contains the Log information like Program start time,End Time
No of succesfully records upload
Rejected Records into the Bad file
Rejected records into the Discard file
Any errors are there in the Control file those error message details we will find.
Log file Extension is (.log)

Control File Syntax:
----------------------

LOAD DATA
INFILE 'Data File Path'
Insert into table
FIELDS TERMINATED by ',' OPTIONALLY ENCLOSED BY'"'
TRAILING NULLCOLS
(Column1 ,
column2 ,
column3 ,......)

Execution of Control File :
-----------------------------

Goto Command Prompt where the SQl loader is Installed.

D:Oracle\Proddb\8.1.7\bin\ SQLLDR Username/Password@PROD
Control = Control file Path.

PO -Setups

Item Creation:
====================
1)Attach the Responsibility called "Inventory Vision Operations (USA)"
2)Open the Items form
Items=>Master Item
3)Select the Organization name - Vision Operations
4)Enter the Item Name , Item Description
goto Inventory tab check the checkbox called Inventory
goto purchasing tab check the check box called Purchasing
5)Save
6)Goto Tools Menu => Organization Assignment option to assign for the multiple
organizations.

Supplier Creation:
========================
1)Attach the Responsibility called "Purchasing Vision Operations (USA)"
2)Goto the Supplier Form
Supply Base=>Suppliers
3)Enter the Supplier Name . Save supplier number will be created automatically.
4)Select the Sites button enter the supplier site address and other details
5)Goto the Contacts tab enter the Contact details
Name
Phno
Postion and so on.....

Buyer Creation:
==================

1)attach the Responsibility called "US HRMS Manager"
2)Create Employee
People => Enter and Maintain=>Select New button
3)Enter Emp name
select action option select "create Employement" select the optiona s "Buyer"
4)enter Data of Birth
save => Ok = > Empoyee number will be generated.
5)Goto System Administrator open the User form create or query user
select the Person field attach the emp name (Which we have created)
6)Save.
7)Goto Purchasing Responsibility
open the Buyers form
enter the employee name and save the transactions.
Setup => Personnal =>Buyers

Ex:

Suppliers Information Report

From Date FND_DATE
To Date FND_DATE
From Supplierno TABLE
To SupplierNo TABLE
Title 100 CHARACTERS

Sno Sname Cdate SiteCode Cdate ConctactPerson Phno
=== ==== ==== ======= ===== ============= ====

No of Sites : 2

Grand Totals : Total No of Suppliers
Total no of Sites
Toatl No of Contacts

Validation:

All parameters are optional if user not enter parameter then we have to retrieve all the
records.

PO Module

PO Application will be used to capture the purchasing information.
Oracle is developed pre defined forms and as well as Concurrent Programs and other
related programs.Client Directley can use those forms and Programs or client
can customize the existing objects(Forms,Reports,Programs)

During the PO application flow we can find the three types of people
1)Requestor : Employee who require the materials
2)Preparer : Employee who is going to prepare the Document
3)Buyer : Employee who is having the authority to purchase the materials from the
Suppliers.

Requisition: is one of the purchasing document will be prepared by the employee when
ever he required the materials or Services or Training and so on.
we have two types of Requisitions 1)Internal
2)Purchase

Internal requisition will be created if materials are receiving from another Inventory
inside of the organization.
Purchase requisition will be created while purchasing the materials from the Suppliers.

Requisitions=>Requisitions

We will enter the Requisition at three level 1)Header
2)Line
3)Distributions.

Open the Requisition form enter the Reqno and select the type at Header level
Enter the Items information at line level like Item name,qty,unitprice,tax and so on
select Distributions button enter the Distributions details.
Save
Select the Button called Approve button to go for approving the Requisition Document
Open the Requisition summary form.
Enter the Reqno select find button we can find the Requisition status wether it is
approved or not.
select Tools menu => View Action History to find the history details
Select Tools Menu =>Control option to Cancel the requisition.

RFQ Document:(Request For Quotation)
=============

Once the Requisition is Approved Buyer will prepare thre RFQ document which will be
delivered to the supplier. Supplier will respond for that with quotation.
we have Three types of RFQ documents

BID RFQ:This will be prepared for the secific fixed quantity and there won't be any
PriceBraeaks(Discounts).

catalog RFQ: This will be create for te materials which we will purchase from the
suppliers regularley , and large number of quantity. Here we can specify the
Price Breaks.

Standard RFQ: This will be prepared for the Items which we will purchase only once
not very often,Here we can include the Discounts information at different
auantity levels.
RFQ Information will be entered at 3 Level
1)Headers
2)Lines
3)Price Breaks(CATALOG,STANDARD) or Shippments (Only for Bid RFQ)

Terms And Conditions:
While creation of the RFQ documents we will select the Terms button and we will enter
the terms abd condition details.

Payment Terms: When Organization is going to make the payment and Interest rates
Fright Terms: Who is going to Bear the Tansportation chargers wether Buyer or Supplier
FOB(FreeOnBoard): If any materials damage or any missing quantity is there then the
the responsiboility of those materials.
Carrier : In which Transportation Company Organization Required Materials
Transportation company Name.

Open the RFQ Form

RFQ and Quotations=>RFQ's
select TYpe and Dates and so on
enter the Items details at line level
select terms button enter the Terms and Condition Details
Select the Price Braks button enter the Price break details
Save
Select the suppliers button enter the suplier details (Who are receiving this Document)
Select the Button called Add from List to Include the supplier list automatically.

Buyer Name : TABLE (Internally buyer ID should pass) - Optional

RFQNo Shipto
Type Billto
Due date Curr
Close date Total
creation date User(created_by)

Lineno Item UOM Price Shipno Qty Price Discount
-----

Quotations:
===========
Quotation is another purchasing document we will receive from the Supplier which
contains the supplier quote details , Price, Payment terms and so on.

Whatever the quotations we have received from the supplier we will enter in the system
through form.

We have three types of Quotations 1)Bid 2)Catalog 3)Standard

For Bid RFQ we will receive Bid quotation from the Supplier
For Catalog RFQ we will receive Catalog quotation from the Supplier
For Standard RFQ we will receive Standard quotation from the Supplier.

After enter all the quotations in the system management will do quote analysis as per
that one best quotation will be elected as Purchase Order.

Quotation Report

Item Name (Table Value set MTL_SYSTEM_ITEMS_B Segment1)

QuoteNo Type Cdate Supplier Site ContactPerson Buyer Created(UserName)

po_headers_all
po_lines_all
mtl_system_items_b
po_vendors
po_vendor_sites_all
po_vendor_contacts
per_all_people_f
fnd_user

Auto Create:
============
It is one of the Purchasing feature to create the RFQ and PO documents automatically
by using requisition lines.

1)Create Requisition and approve
2)Open the AutoCreate form
3)Select Clear button enter the RequisitionNO
4)Select find button which will shows all the requisition lines
select the lines whatever we want to include into the RFQ
5)select Action = Create to create new RFQ
AddTo to add lines to exisiting to RFQ
6)Select DocumentType = RFQ
7)select Automatic button which will create RFQ document automatically .

Purchase Order :
================
PO is one of the Main document which will be prepared and approved by the buyer and
send it to the supplier. which contains the following information
terms and Conditions
Items deails
Qty,Price
Distiribution and Shipment Details and so on.

We have four types of Purchase Order 1)STANDARD
2)PLANNED
3)BLANKET
4)CONTRACT

Purchase Orders=> Purchase Orders
Open the PO form enter the Inforamtion at header level select line level inforamtion
enter the items and quantity,price details
select shippments button enter the shippment details select the Distributions button
enter the Distribution Detauils.
Save
Select the Button called Approve (Uncheck Email Check Box) , Document will be submitted
for approval.
open the Purchase Order summary form enter PO number Select Find button we can find
the status of the Purchase order.
Goto Tools menu
Action History => We can find who hs submitted for Approve /Reject /Cancel details
Copy Document => To Create Another PO based on this PO
Control => To Close the Purchase Order or to cancel the Purchase Order.

Purchase Order Report
=====================

POno : Buyer:
Potype : Supplier:
ShipTo : Supplier Site:
BillTo : Contact :
Cdate : Status : POTotal :

payment Terms: Fright Charges: FOB: Carrier:

Lineno Item Desc Qty price Shipno ShiptoLoc ShipToOrg Qty Distno Distqty Requestor
----- ---- ---- --- ----- ------ -------- --------- --- ----- ------ ------

Receipt : Purchasing document will be created while receiving the items from the supplier

1)Direct Delivery
2)Standard
3)Inspection Required

Sunday, August 16, 2009

AP Module

1)Open the Invoice Form
Invoices => Enter=>Invoices

2)Enter the Supplier details,invoicetyps,data,amount
Save

3)Select Distributions button enter the Distribution Details.
Distamount and account and so on.
Save
Validate wether InvoiceAmount = Distribution Total Amount or not

4)Select Actions button check the Check box called Approve
press OK button for invoice approvals.

5)Select actions button again check the check box called Create Accounting to create
the accounting transaction

6)Select the Tools menu select the option called View Accounting option which will
displays the Accounting Transactions.

Transfer Invoice Data to GL:
============================

1)Once the Invoice is Validated
Approved
Accounted we can transfer to GL application

2)Goto SRS Window select the program called "Payables ransfer to General Ledger"
Enter Invoice From Date
To Date
Submit Journal Import = Yes
Transfer to GL interface = In Detail
PressOk

3)System will transfer the data from AP tables into GL Interface tables then
Journal Import program will be submitted automatically which will transfer the data
from Interface tables into the Base tables.

4)Copy the Request ID of Journal Import goto GL Application open the Journal form
Journal=>Entry
enter the %RequestID% in the Batch name field =>Selec Find button

5)Select the Review journal button will shows the Details Transactions
select MoreActions =>Post button to post the Journals.
Distrobution Set:
=================

Distribution set will be used to Distribute the Invoice amount automatically for the
different accounts .
1)Create the Distirbution Set
Setup=>Invoice=>Distribution Set

2)Goto Invoice form and create Invoice attach the Distribution set at header level
Save
Invoice amount will be distributed automatically to the diff accounts.

3)Select Distrtibutions button we can find the Transactions

4)Select Action button for Approval and For Create Accounting Transactions.

Payments:
=========

once the Invoice Create and Approved,Accounted are generated we can make
the Payments to the supplier by using Payments form

We have three types of Payments

Manuval
Quick
Refund

Manuval: If Organzation is going to pay the amount manuvally (by cheque or cash)
then it will be captured under the manuval payment type.

Quick : We will Give the Invoice number System will automatically genrate the
Payment transaction as per the Invoice number we have provided.
System will automatically generates the Cheque number also.

Refund : If any refundable amount we have to pay to the supplier or customer or emp
then those payments will be select as Refund method.

1)Copy the Invoice number

2)Goto the Payments form
Payments=>Entry=>Payments

3)Select Payment type,Bank Account number,Supplier details
select the button called Enter/Adjust Invoices
it will shows the invoices whcih are created for selected supplier
Select the Invoice number
Save

4)Select actions button check the check box called Create Accounting
will create Accountign transactions => Tools menu =View accounting

5)Select Actions button to print the Check as per the selecvted check Format.

6) We can cancel the payments by using the Check box called "Void".

Invoice Holds and Releases:
===========================

If Invoice is not approved (Ex : invoiceamt not equal to Distribution amount
Supplier Credit Limit is crossed)
then invoice will be on the hold

select Holds button which will shows the Invoice holds and release details

Tuesday, July 28, 2009

flexfields

FLEXFIELDS:
Flexfield are made up with Attribute columns or Segment columns . which are more flexible than the normal fields. we have two type of flexfields 1)DFF (Descriptive Flex Field) 2)KFF (Key Flex Field) DFF: It will be used to capture the Extra information from the end user without change the code in the form and without Alter the DB object. ATTRIBUTE Columns will be used to Capture the DFF data. KFF: it will be used to Capture the Key information from the User in code language for every code there will be a specific meaning. SEGMENT Columns will be used Capture the KFF Data We can find all the flexfield details in Application Developer Responsibility Flexfield=>Descriptive=>register=> CTRL+F11 for all DFF Flexfield=>Key =>register=> CTRL+F11 for all KFF We will Use the Segment form to Customize the DFF.

Value Set

Value Set: Value set is nothing but list of values with validations which will be
used to to restrict the user without entering the invalid data in the Parameters

we will use value sets in two locations.
1)Concurrent Progam parameters
2)Flexfields
NONE:
—-
We are not providing any LOV, we can apply some format conditions as per that
conditions user should enter the data

Notes: 1)Once we create the Value set we can not Delete if we would like to delete
we have to release the value set from the all the concurrent program
parameters then only we can delete.
2)Value set name is case sensitive
3)Once we create Value set we can use for multiple Program parameters.

Navigation:
———–
System administrator => Application=>Validation=>set=>
Enter value set name
format type
max size
Select validation type = “None” to create None type of Value sets.

Independent:
————

When we would like to provide list of values to the user then we will go for selection
of Independent value set.where we will provide LOV.
User must select the Value from the list otherwise values are not accepted.

Open the Value set form create value set by selecting the validation type=Independnent
Goto Values screen enter the value set name , Select Find Buttion
enter the values whatever we would like to display as LOV.
attach the value set to the Parameter.

Note:1)Once we enter the values we can not delete instead of that we can disable by
selecting the Enabled check box
or Effective Dates.
Dependent value Set:
====================
This is another LOV which will be used to displays the
list of values which are depending on the previous parameter value.

Before going to create Dependent first we have to create Independent
then we have to create Dependent

First parameter will be Independent
Second parameter will be Dependent.

Note:Without Independent we can not create Dependent Value set.

Country IND
US
UK
City Banglore Chennai Delhi Mumbai Pune
Chikago California Anderson
London Hungrant

1)We have to create Independent value set and enter the values.
2)Create Dependent value set attach independent and then enter values.

Job Manager
Developer
Programmer

Position Delivery Manager Project manager Financce manager
Software Developer Test Developer
Trainee Fresher
Navigation:
==========
1)Open the Value set form create Value set by selecting the validation type =Independent
2)Open the Values screen enter the VAlues .
3)Open the value set form enter Dependent value set by select validation type=Dependent
Select the Button called Edit Information button enter the Independent value set
4)open the values form enter the Dependent value set=>Find
enter the values based on the Independent values.

Table Value set :
=================

Table value set will be used to displays the list of values from the
oracle apps base tables.
we have to give the table name and column name which will automatically
displays the values.

Note: If values are not stored in the database table then we have to
go for Independent value set.
If values are there in the table then we will create table value
set.

1.Open the value set form Select validation type as table select the
button called Edit Information enter table name and column name
in the value field
2.Use where/Order By clause to implement Where/Order By clause.
3.Use Additional Columns field to displays extra columns for reference
purpose.
4.Use the ID column to pass the ineternally other columns data
for ex displaying username to the user and pass userID internally.
5.If multiple tables are required then enter the table names in the
table name field with alias name and enter the Join Condition in the
Where clause field.

6.If we know the table name we can find the Table application name from
Application Developer responsibility
Application Developer => Application => Database => table
Query the records based on the table Name.

Note: If we are displaying additional Columns we are suppose to give the Alias Name

Translated Independent and Translated Dependent:
================================================
Both value sets will work like Independent and Dependent value sets
will be used to displays the transalation values which will be enabled
if there is multilanguage implementation.

Special and Pair:
=================
Both Value sets will be used to displays the Flexfield data as LOV to
the User.

Link from Anil Passi:
http://oracle.anilpassi.com/value-set-basics-in-oracle-apps.html

Qry Find

1.CREATE A TABLE IN PARTICULAR MODULE. 2.GRANT THE TABLE TO APPS. 3.CREATE SYNONYM FOR THE TABLE. 4.DOWNLOAD TEMPLATE.FMB AND APPSTAND.FMB FROM AU_TOP/RESOURCES TO LOCAL MACHINE USING FTP. 5.RENAME TEMPLATE.FMB AND OPEN IT IN FORMBUILDER. 6.OPEN APPSTAND.FMB IN THE SAME FORM. 7.COPY QUERY-FIND FROM APPSTAND OBJECT GROUP TO TEMPLATE OBJECT GROUP. 8.DELETE BLOCKNAME IN CANVAS,WINDOWS,DATABLOCK LEVEL. 9.CREATE NEW CANVAS,WINDOW,DATABLOCK USING CREATE BUTTON. 10. (i)SELECT QUERY_FIND DATABLOCK.SELECT NEW BUTTON TRIGGER –>CHANGE BLOCK NAME (ii)SELECT FIND BUTTON TRIGGER –>CHANGE BLOCK NAME (iii)GO TO MAIN DATABLOCK SELECT ITEM TO QUERY PASTE IT IN QUERY_FIND BLOCK ITEMS (iv)SELECT COPIED ITEM PROPERTIES CHANGE CANVAS NAME. 11.SELECT MAIN_BLOCK TRIGGERS CREATE PRE_QUERY_TRIGGER INSIDE THE TRIGGER TYPE if :parameter.g_query_find = ‘true’ then copy(:query_find_block.item,’main_block.item’); :parameter.g_query_find := ‘false’; end if; 12.CREATE ONE MORE TRIGGER AT SAME BLOCKLEVEL TRIGGERNAME IS USER_NAMED INSIDE THE TRIGGER TYPE app_find.query_find(’main_window’,'query_find_window’, ‘query_find_block’); 13.SELECT LOV BLOCK CREATE LOV. 14.ATTACH LOV TO QUERY_FIND ITEM IN QUERY_FIND_DATABLOCK. 15.SELECT MODULE LEVEL TRIGERS CUSTOMIZE PRE-FORM,WHEN-NEW-FORM-INSTANCE TRIGGER AND APP_CUST PACKAGE_BODY 16.EXECUTE USERNAMED TRIGGER IN WHEN-NEW-FORM-INSTANCE TRIGGER BY USING EXECUTE_TRIGGER(’TRIGGER_NAME’); 17.SAVE THE FORM AND TRANFER FMB TO CUSTOM_TOP. 18.GENERATE FORMEXECUTABLE(FMX) USING THE UNIX COMMAND F60GEN MODULE = FORMNAME.FMB USERNAME/PASSWORD. 19.TRANSFER FMX TO PARTICULAR MODULE_TOP/FORMS FOLDER. 20.LOG ON TO ORACLE APPS SELECT APPLICATIONDEVELOPER RESP. 21.CREATE FORM. 22.CREATE FUNCTION,ATTACH FORM TO FUNCTION. 23.CREATE MENU ,ATTACH FUNCTION TO MENU. 24.DEFINE RESPONSIBILITY,ATTACH MENU TO RESP. 25.ATTACH RESP TO USER.

profile Description

July 28, 2009 by amreshblog

note: In the Projects most of the profile values will be assigned at the Responsibility
level.

Diff Between Application and Responsibility:
=========== ===============

Applciation is nothing Colletion of Forms,Reports and Program which are related for
specific business functionality.

Responsibility is nothing but Colletion of Forms,Reports and Program which are related for
specific Position in the Organization.

For Ex : We have to create One Responsibility For the Clerk. Which is accesable by all
the Clerks.
It Contains the Forms and Reports which are required for the Clerk.

We have to Create new Responsibility for the Manager,Which is accesable by all the
Managers.
It COntains the Forms and Reports which are required for the manager.

Where as Application includes all the Forms,Reports and Programs.If we assign the
application to the user he will access all the forms and Reports.
Intead of that we will create the responsibility and we will assign to the User.

Common Profiles:
================

Gl:Set of Books: Which is Financial Profile option will be uset to assin SetofBooks
HR:Business Group : Which will used to assign the Business Group
MO:Operating Unit : To assign the Operating Unit (Branch) to the users.
MFG_ORGANIZATION_ID: Will Be used to assign the Manufacturing Organization ID.

USER_ID
USERNAME
RESP_NAME and so on………

We can find all the Profile details in Application Developer Responsibility.
We can assign the Profile values in System Administrator Responsibility.

Application Developer=>Profile =>Press CTRL+F11 we can find all the profiles.

System administrator=>profile=>System=> Select Profilename,Level =>Find button
then assign the Profile value.

Set Of Books :SOB is nothing but collection of Currency
Calendar
Chart of Accounts.
We will assign the SOB as a profile value to the user as per the Profile value system
will automatically change the application running.

Base on the SOB name we can find the change in the currency and calendar and accounts

SELECT NAME,
CURRENCY_CODE,
PERIOD_SET_NAME,
CHART_OF_ACCOUNTS_ID
FROM GL_SETS_OF_BOOKS

SELECT * FROM GLFV_CHARTS_OF_ACCOUNTS WHERE CHART_OF_ACCOUNTS_ID = 50713

22USER GL:Set Of Books Vision Operations (USA) USD
23USER GL:Set Of Books Vision Korea KRW
24USER GL:Set Of Books Vision Italy ITL

Create Three users
Assign Profile values from System administrator (Profile=>System)
open the GL Form and verify the curency values (GL=>Journal=>Enter=>new Journal)

Note: Most of the profile values will be assigned at Responsibility Level.

Retrieve the Profile Value from Backend:(SQL,PL/SQL,Forms6i,Reports6i)
======================================

Fnd_Profile.Get(’ProfileName’,
local Variable);

local Variable:= Fnd_Profile.Value(’Profile Name’);

Both API’s will be used to retrieve the Profile value frombackend

Get() is Procedure
Value() is Function

Oracle Has provided both Procedure and Function becuase in some of the areas we can not
use procedure then we can use function.

For Ex: in SELECT clause we can not use procedure we have to go for using the Function.

1)We would like to display the Set of Books name
User name
Respname in the first page of the report.

22USER
23USER
24USER

Ans)

1)Define the Local Variable
2)Goto before Report Trigger write the follwoing API

_SOBNAME:= Fnd_Profile.value(’GL_SET_OF_BKS_NAME’);
:USERNAME := Fnd_Profile.value(’USERNAME’);
Fnd_Profile.Get(’RESP_NAME’,
:RESPNAME);
3)Goto Layout model Header section and Display the Variable Name.
4)Submit from Diff Users and test the Output we can find the Difference.

2)Develop the PL/SQL Program for vendor Name updation. Vendor name should be updated
if “OPERATIONS” user submit the Program for other users should not get update.

Parameters are VendorID
VendorName

Create Or Replace Procedure ven_update(Errbuf OUT varchar2,
Retcode OUT varchar2,
v_id IN number,
v_name IN varchar2) as
l_name varchar2(100);
begin
l_name:=Fnd_Profile.value(’USERNAME’);
If l_name = ‘OPERATIONS’ then
UPDATE PO_VENDORS
SET VENDOR_NAME = v_name
WHERE VENDOR_ID =v_id;
commit;
Fnd_File.Put_line(Fnd_File.Output,’vendorname has updated succesfully’);
Else
Fnd_File.Put_line(Fnd_File.Output,’Access Denied for updateion’);
End If;
End;

Note: We can pass the profile value as default value by using Profile default type.
Select Default type = profile
Default Value= Profile Name
When we are passing Profile value as default we are suppose to hide the Parameter
because profile is confidential Information we are not suppose to give permission for
modifications.

User Creation

Creation of New User:

1)Open the internet Explorer connect to Oracle Applications
2)Enter the User Name :OPERATIONS
Password :WELCOME
3)Select the Responsibility called ‘System Administrator’
4)Open the User form.
Security => User =>Define

5)Enter User Name and Password attach the Responsibilities whatever we required
for ex System Administrator
Application Developer

6)Exit from the Appication
File => Exit Oracle Applications

7)Connect to Oracle apps enter new user name password system will shows the message
like ‘Password Has Expired’

8)Enter the New Password Press Ok Button

Short Cuts:
===========

1)To Query All the Records Press CTRL+F11
2)To Query Specific Records
i)Open the Form
ii)Press F11 (Form will comes into Query mode)
iii)Enter Search Criteria in any field
iV)Press CTRL+F11
3)To Close Form = F4
5)To Save the Records CTRL+S

Effective Date From and To:
===========================

In most of the Oracle Application forms we will find two field like
Effective Date From
Effective Date To

In some of the forms once we create records and save. We can not delete from database
that time we can go for Disable/Enable the record by using these two fields

Finding Table NameS/Column Names:
=================================

1)Help => Record History which will shows the Table Name
2)Help Menu=>Diagnastics=>Examine=>Enter the Password(APPS)=>We can find the Column Name

WHO Columns:
=============

WHO Column Will be used to find out the History of the record
we can find from front End Also
Help=>Record History

CREATED_BY – Which User has created the Record(Userid)
CREATION_DATE – at what time user has created (SYSDATE)
LAST_UPATED_BY -Which User has updated recentley(UserID)
LAST_UPDATE_DATE -at what time user has Updated (SYSDATE)

LAST_LOGON_DATE – At what time user last Login Time

Find the Login UserName:
==========================
Help Menu=>About Oracle Application

Rollback Segment

A Rollback Segment is a database object containing before-images of data written to the database.

Rollback segments are used to:

Undo changes when a transaction is rolled back
Ensure other transactions do not see uncommitted changes made to the database
Recover the database to a consistent state in case of failures

tablespace

- A tablespace is a logical group of data files in a database. A database typically contains at least one tablespace, and usually two or more. Within the database, the tablespace plays a role similar to that of a folder on the hard drive of a computer.

Some database programs, such as Oracle, automatically create a tablespace called SYSTEM that contains general information about the structure and contents of the database. A small database can be entirely contained in the SYSTEM tablespace, but in most cases, user data is placed in other tablespaces.

The files in a tablespace usually share a common characteristic. For example, a database for a wholesale distributor might consist of tablespaces entitled ACCOUNTING, ORDERS, SHIPPING, SERVICE, and SYSTEM. Each tablespace might contain only one data file, or thousands of files, or anything in between. Tablespaces can be created, deleted, and merged, just as can the folders on the hard drive of a computer.

Reports with Perameters

Reports With Parameters:
========================

1)If WE have parameters in the report we are suppose to define those parameters
while defining the Concurrent Program

2)Select Parameters button enter the following Fields

Seqno:Any +ve number no duplicates are allowed
ParameterName:Any User defined parameter name
Value set:10/NUMBER or 100 Charachters or FND_DATE
Token:Enter the Bind Variable name

Token : It is one of the field in parameter form to map the Concurrent Program
parameter with Report bind Variable.

Value Set: Is nothing but list of values with validations which will be used to
restrict the user without entering the invalid data.

Required CheckBox: When we want to make the parameter as mandatory or Optional we will
use this check box. If we enable it will be mandatory
disable it will be optional.

Display CheckBox: When we want to hide the parameter we will use this check box
user can not find the parameter at SRS window.
we can pass default values internally by using Default types.

Enabled CheckBox: We can delete the parameter.
instead of deletion we can also disable the parameter. Whenever we required again
we can enable

Range: This option will be used to set the values as Low and as well as High.

From Date
To Date
Title

UserID UserName Cdate RespName Applicationname Datagroupname StartDate Enddate
------ ------- ----- -------- --------------- ------------- --------- -------
1545 25USER 10-JAN-07 25ResponsibiltiyOracle Purchasing Standard 10-JAN-07
Systemadminist System admin STANDARDA 15-JAN-07

FND_USER
-USer_id
FND_USER_RESP_GROUPS

FND_RESPONSIBILITY_VL
FND_APPLICATION_VL
FND_DATA_GROUPS

From Date
To Date
Title

RespName Applicationname Respkey Menu Requestgroup Datagroup End date
======== =============== ======= ==== ============ ========= ========

FND_RESPONSIBILITY_VL
FND_APPLICATION_VL
FND_DATA_GROUPS
FND_MENUS_vl
FND_REQUEST_GROUPS

Default Types :
================

When we have the Parameters to the Concurrnet Program we would like to pass some
default values we will use the Default types we have 6 types of Default types.

Constant
Segment
Profile
Current Date
Current Time
SQL Statement

Note: While passing the default values normally we will hide the field by using Displayed
check box .So that User can not change the Value.

Attach the Concurrent Program to other Responsibilities:
=========================================================
1)Open the Responsibility Form Query Resp name , Copy the Request Group

2)Goto Request group Form query the Request group name . Attach the Concurrent Program
name.
3)Goto SRS Window and submit the Program.

Context field

Context Field:
==============

Context field will be used to define the Multiple Structures in the DFF.
when User place the cursor in the DFF It will open the DFF with Context field.It
Contains the Context field values select the values . Based on this Values DFF
structure will be populated.

Context field value will be stored in the column called 'ATTRIBUTE_CATEGORY'

1)Open the Segment Form Query records based on the DFF Title.
2)Uncheck the check box called 'Freeze Flexfield Defination'
3)Enter the Context field prompt , select Display,Required check box if required
4)Goto Context field values enter the value =>Segments Button =>Enter the fields
5)Save, Compile

Global Data Elements:
=====================

If we would like to disply the field globally (which will be displayed always
irrespective of Context field values.

Multiorg

Multiorg :
=============
Use a single installation of any Oracle Applications product to
support any number of organizations, even if those organizations
use different sets of books.

BusinessGroup (HRMS=>Workstructure=>Organization=Description)
Set of Books (GL=>Setup=>Financials=>Books=>Define)
Legal Entity
Operating Unit
Inventory Organization
Sub Inventory (Inventory=.Setup=>organizations=>SubInventories)
Stock Locations (Inventory=.Setup=>organizations=>Stock locatiors)
Items

Major Features
===============
1)Multiple Organizations in a Single Installation

2)Secure Access
You can assign users to particular organizations. This ensures accurate
transactions in the correct operating unit.

3)Multiple Organizations Reporting

You can set up your Oracle Applications implementation to allow
reporting across operating units by setting up the top reporting level.
You can run your reports at the set of books level, legal entity level, or
operating unit level

Business Group:
===============
The business group represents the highest level in the organization
structure, such as the consolidated enterprise, a major division, or an
operation company. The business group secures human resources
information. For example, when you request a list of employees, you
see all employees assigned to the business group of which your
organization is a part.

Set Of Books:
============
A financial reporting entity that uses a particular chart of accounts,
functional currency, and accounting calendar. Oracle General Ledger
secures transaction information (such as journal entries and balances)
by set of books. When you use Oracle General Ledger, you choose a
responsibility that specifies a set of books. You then see information for
that set of books only.

Legal Entity:
=============
A legal company for which you prepare fiscal or tax reports. You
assign tax identifiers and other legal entity information to this type of
organization.

Operating unit:
===============
An organization that uses Oracle Cash Management, Order
Management and Shipping Execution, Oracle Payables, Oracle
Purchasing, and Oracle Receivables. It may be a sales office, a division,
or a department. An operating unit is associated with a legal entity.
Information is secured by operating unit for these applications. Each
user sees information only for their operating unit.

Inventory Organization:
=======================
An organization for which you track inventory transactions and
balances, and/or an organization that manufactures or distributes
products. Examples include (but are not limited to) manufacturing
plants, warehouses, distribution centers, and sales offices. The
following applications secure information by inventory organization:
Oracle Inventory, Bills of Material, Engineering, Work in Process,
Master Scheduling/MRP, Capacity, and Purchasing receiving functions.

Subinventory:
=============
Which is another organization inside of the Inventory oganization will
be used to define the locations under these location items will be placed.

Multiorg Table:
===============
It is a table contains the data which is relted to multiple operating units
all the multiorg table names will be end with '_ALL'.

like PO_HEADER_ALL
PO_LINES_ALL
AP_CHECKS_ALL and so on

Note: In all these tables we will find one common column called "ORG_ID"
This column will be populated internally by the system as per the User Operating
Unit ID

Client_Info:
===========
It is one the RDBMS vaiabel which contains the User Operating Unit value (ORG_ID)

Multiorg View:
=================
It is a view which is created based on the Multiorg table which contains the WHERE
clause WHERE Org_ID = :Client_Info.

Note: While development of RICE Components we are suppose to Use Multiorg Views
not Multi Org Tables.

Because if we use Multiorg tables we will get all the operating units data
if we use multiorg view we will get the operating units data which is related for that
perticular user .

1)What is the Diff between ORG_ID and ORGANIZATION_ID?
Ans)ORG_ID is at Operating Unit Level
ORGANIZATION_ID is at Inventory Organization level

2)Why the PO Receipt functionality will come at Inventory organization level?
Ans)user is creating receipt means indirectley he will be receiving the materials from
suppliers.materials will be received at Gowdons ,Warehouses,manufcaturing plants
all these organizations will come at Inventory Organization level.

3)how the System Will Identify user is working for so and so operating Unit?
Ans ) By using following Profile called
MO:Operating Unit

4)What is Multiorg?
5)What is Client_info?
6)how to Implement Multiorg in Reports and at SQL prompt?
7)What is Business group, Legal Entity,Operating Unit,Inventory Organizations?
8)What are the Modules will come at operating Unit level?
9)What is the flow of Multiorg?
10)How to Identify the Multiorg Table?
11)Wat is the Diff between Multiorg Table and Multiorg View?
12)While Developing RICE Components we will use Multiorg Table or Multiorg View?
13)Why there is no _ALL for PO_VENDORS
and there is _ALL for PO_VENDOR_SITES_ALL?
Ans)Supplier is global
Supplier site is Org Specific.
14)How will you findout Multiorg Succesfully Implemented?
Ans)

SELECT MULTI_ORG_FLAG ,RELEASE_NAME FROM FND_PRODUCT_GROUPS

Y - Succesfully Implemented
N - Not Implemented Succesfully.

Interfaces:

Interfaces:

Interface is one of the Program which will be used to transfer the data from Oracle
database tables in to flat file (or)
Flat files into Database tables

We have two types of Interfaces.

1)Inbound Interface
2)outbound Interface

Outbound Interface will be used to extract the data from oracle Database tables into
the flat files.

Inbound Interface will be used to upload the data from legacy system (Flat files) into
Oracle Applications base tables.

While developing the outbound Interface we will use UTL_File to Extract the data.
While Developing the Inbound interface we will use SQL * loader to import the data
into base tables.

UTL_FILE Package :
==================

this is One of the PL/SQL Package which will be used to transfer the data
from table to files
from files to tables

But when we are working for file to table we will use SQl *Loader
to transfer from table to file we have no alternative we have to use UTL_FILE.

We will use following three functions to generate the file.

1)Utl_File.fopen = To open (or) Create the file
2)Utl_File.Put_line = To Transfer the data into the File.
3)Utl_File.fclose = To close the File after Data transfer.

outbound Interface Process:
=============================

1)Develop the PL/SQL Program (Either Procedure or Package)
2)Write the Cursor to retrieve the data from database tables.
3)Create file or Open the File by using UTL_File.fopen().
4)Open the Cursor
5)If any validations are there write the validations
6)Transfer the Data into File by using UTL_File.Put_Line().
7)Close the Cursor.
8)Close the File by using UTL_File.fclose();
9)Register the Procedure or Package as Concurrent Program and submit from SRS Window.

For Ex : We required the flat file with following Format.

Supplierno,Supliername,Cdate,Sitename,totalinvoice,TotalPo

Develop the Program to Transfer Supplier Purchase orders datainto file

Supplier No from
Supplier No To

PoNumber,POtype,Amount,Cdate,No,Name,Site

Validations:

1)If Parameters are null select all the supplier details
2)If 'from' is given 'To' is not given take till Last Supplier
3)If 'To' is given and 'from' is not given take from first.
4)If Purchase order amount is more then 3500 then only transfer into file.

DFF Process

1)define the field in the Canvas
change the field property database items as "no"

2)Define the Package like follows.

Package Specification:
----------------------
PACKAGE DFF_PKG IS
PROCEDURE DFF_PROC(EVENT VARCHAR2);
END;
Package Body:
-------------
PACKAGE BODY DFF_PKG IS
PROCEDURE DFF_PROC(EVENT VARCHAR2) AS
BEGIN
IF (EVENT = 'WHEN-NEW-FORM-INSTANCE' ) THEN

FND_DESCR_FLEX.DEFINE( BLOCK => 'WIP_ITEMS10',
FIELD => 'DFF_TEXT',
APPL_SHORT_NAME => 'WIP',
DESC_FLEX_NAME => 'WIPDFF');

END IF;
END DFF_PROC;

END;

3)Call this Package from WHEN-NEW-FORM-INSTANCE Trigger
packagename.Procedure name('WHEN-NEW-FORM-INSTANCE');
DFF_PKG.DFF_PROC('WHEN-NEW-FORM-INSTANCE');
4)Call another API from WHEN-NEW-ITEM-ISNATCE Trigger
fnd_flex.event('WHEN-NEW-ITEM-INSTANCE');
-It will Populate the DFF in the Form.

Procedure Registration

Procedure Registration Steps:

1)Develop the procedure and compile at SQL prompt
2)Connect application select system administrator Responsbility create
Executable by selecting the Execution Method as 'PL/SQL Stored Procedure'
3)Create Concurrent Program and attach Executable to the Program and add the
Parameters and incompatible programs.
4)Create Request group attach concurrent Progrtam
5)Attach Request group to the reponsibility
6)Responsiboility will be added to the user.
User will submit the request from SRS Window.
Syntax:

Create Or Replace Procedure (Errbuf OUT varchar2,
Retcode OUT varchar2,
P1 IN NUMBER,
P2 IN VARCHAR2,
P3 IN DATE) AS
Local variable,Cursor,Collections Declare;
Begin

If statement
For Loop
Procedure Calling
DBMS_OUTPUT.Put_Line - This will not be used instead of this Fnd_File API will be used

FND_FILE.PUT_LINE(Fnd_File.Log ,'Message'||Variable Name);
FND_FILE.PUT_LINE(Fnd_File.Output,'Message'||Variable Name);
Exception
When Other then
-Exception Statements;
End ;

PL/SQL Procedure with Parameter:
================================
If we have any user defined Parameters then we have to register these parameters at the
time of Creating the COncurrent Program by selecting the Parameter button
enter the Seqno
Parameter Name
Value Set
Note : Token Field will be disabled.
Here First Parameter value will be passed to the first variable
Second Parameter will be passed to the second variable and so on........

When we are registering the report as C.P then only we required TOKEN field.
Because report builder bind variables may or may not be in the sequence that's why
we have to map with Token field.

Where as in Procedure variables position is fixed then TOKEN field will be disabled.

profile

Profile :
Profile is one of the changable option it will change the way of
application execution.

When User Log in to the application and select the the resp or Appl
system will automatically captures all the profile value as per the
profile values application will run.

Ex: If client have three Organizations 1)Hyd
2)Ban
3)Chn
If "hyd" users connect to the Application system will retrive the
data from database which is related to the Hyderabad branch.
If user is working for 'CHN' brnach then chennai branch setups or data
will be retrieved.

For every user we will assign the Profile value

Ex: Operation

Position - Profile Name
Profile Values
--------------
Manager
Supervisior
Clerk
Operator
Trainess

When we want assign any profile value we have four levels
we have to select any one of the level.

Profile Level Profile Profile Value
------------- ------- --------------
User - OPERATIONS - Print - 10(This is for for Operations)
Responsibility - 22Responsi - Print - 5(This is for 22resp users)
Application - GL Applica - Print - 4(This is for GL App Users)
Site - --- - Print - 2(This is for ALL Users)

Site : this is lowest level to assign the Profile values site values
are applicable for all the users.when we install Application by default
site level values will be assigned.

Application: These values are applicable for the users who are having
the access for the application. If user is eligible for both
application and site level values then application level value will
override the site level value.

Responsibility:We will select the responsibility name assign the value
which is applicalbe only for the users who are having the access for
specified responsibility.

Responsibility level value will override both application and site
level values.

User: This is highest level in the profile option.
we will select the user name and assign the profile value which is
applicable only for this user.
User level value will override all other profile level values.

Diff between Application and Responsibility:
============================================
Both are Group of Forms(Menu)
Group of ConcurrentPrograms(Request Group)
Group of Users (Data group)
But Application as per the Business functionality requirement
Responsibility will group as per the position requirement.

Some of the Imp Profile Names:

GL:Set Of Books
MO:Operating Unit
Hr:Business Groups
MFG_ORGANIZATION_ID
USER_ID
RESP_ID
USERNAME
RESP_NAME and so on..........

Profile

Request sets

Request Set:
============

Colection of Concurrent Programs which will be used to submit the Cnocurrent Programs
either sequentially or Paraalley multiple programs

It is also like Request group but in Requuest group we can submit only one program at
a time from SRS Window.
where as in Request set we can submit multiple programs at a time.

1)Select the Programs which we would like to group in the set
Ex:
Active Responsibilities
Active Users
Compile Reports
2)Open the Request Set form Select the button called Request set wizard and
enter the concurrent Program list.
Concurrent =>Set
3)Open the Request group Form attach the Request set by selecting the Type = Set
and attach the Request set.
4)Goto the SRS Window select the Option called Request set instead of Single Request.

Incompatibility:
================
Incompatibility is nothing but not compatible with the current concurrent program
For Ex
If we have three program A B C
If A program is running in the server system should not run the B and C programs
that time we wii define the Incompatibility
While createion of the A Program
Select the button called Incompatibility and enter the B and C Programs.

Run Alone Check box :if we would like to make the Program is not compatible with all
other concurrent Program then we will check this check box.

Use in SRS Window: Be default this check box will be enabled we uncheck this we can
find the Program at SRS window we have to submit from backend by using fnd_request
API.

Copy To button: While customizing the Concurrent Programs(Reports,Package.....)
we are suppose to create new concurrent Program with diff name then we will
go for using the Copy To button.

note: By default every concurrent Program will be executed in the CBO(Cost Based
Optimizer) if we would like to execute in the RBO(Rule Bases Optimizer)mode then
we will use the Session control buttion we will set the Rule Option.

:$FLEX$
:$PROFILES$

These two are Oracle apps reserved words will be used in the Value set creation

:$FLEX$: This will be used to Retrieve the Previous parameter value whatever we have
selected.

Syntax : :$FLEX$.Previous Parameter VAlue set Name.

For Ex: We have two Parameters

Supplier Name : Table Value set
Supplier Site Code : Table VAlue set

based on the Suplier name we are suppose to get the Site codes in the Second parameter

SELECT VENDOR_NAME FROM PO_VENDORS - First VAlue set

SELECT VENDOR_SITE_CODE FROM PO_vendor_sites_All -Second Value Set
where vendor_name = Whatever user has selected in the First PArameter
(To get this value we will use :$FLEX$.Previous Value set name).

23SUPPLIER - First Value set Name

23SITE
WHERE VENDOR_ID IN(SELECT VENDOR_ID
FROM PO_VENDORS
WHERE VENDOR_NAME = :$FLEX$.23SUPPLIER)

:$PROFILES$: This will be used to Get the Profile value in the Table Value set or
from the front end.

To get Profile values from backend we are using Fnd_Profile.Value or Fnd_Profile.get()

Syntax : :$PROFILES$.Profile Name

SELECT SEGMENT1
FROM PO_HEADERS_ALL

22USER : 204
23USER : 887
24USER : 911

SELECT SEGMENT1
FROM PO_HEADER_ALL
WHERE ORG_ID = :$PROFILES$.ORG_ID

Ex: Display the PO's which are created by the current User

If 22user ope the LOV it has to display the PO's which are created by 22 user

SELECT SEGMENT1
FROM PO_HEADERS_ALL
WHERE CREATED_BY = :$PROFILES$.USER_ID

Oracle Apps

Tuesday, August 18, 2009

Monday, August 17, 2009

Sunday, August 16, 2009

Tuesday, July 28, 2009

Rollback Segment

Followers

About Me

Blog Archive

Weblinks

Oracle Videos

Labels