Oracle apps

Form personalization - Number

2011-11-15T19:52:00.001-08:00

Numeric validation in form personalization

Trigegr event: WHEN-VALIDATE-RECORD

Trigger object : ACCOUNTS

DECODE(TRANSLATE(:ACCOUNTS.BANK_ACCOUNT_NUM,'1234567890','0000000000'),LPAD('0',LENGTH(:ACCOUNTS.BANK_ACCOUNT_NUM),'0'),'NUMBER','NOT NUMBER')='NOT NUMBER'

ex: if 33123-33 is entered, then decode convers the code as 00000-00 and LPAD function returns 0000000 , hense mismatchin and not Number.

How to use regexp_substr

2011-08-10T03:44:00.001-07:00

regexp_substr cabe used to transpose the column values as rows

In the below example PRODUCT_TAB is a table ename and product are columns

PRODUCT_TAB

===========

ename product

B 1,2,3

Product column has data like that only separated by commas

Output should be like this.

Ename Product

A 1

A 2

A 3

B 1

B 2

B 3

If you want the above desired output please use the below solution

SELECT DISTINCT EMP,regexp_substr (product, '[^,]+', 1, level)
product
FROM product_tab
CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (product, '[^,]+')) + 1
ORDER BY 1;

Thanks

Mohana Sivakumar varma

Creating softlink and registering Host concurrent program

2011-07-24T23:55:00.000-07:00

1. Create a host file in required TOP

Ex: $XXGL_TOP/bin/XXGL_EMAIL.prog (the extension can be any thing)

2. change permission using the below command

chmod 755 XXGL_EMAIL.prog

3. Create a softlink using the below command (execute this command in the same path $XXGL_TOP/bin) * without extension i.e. .prog

ln -s $FND_TOP/bin/fndcpesr XXGL_EMAIL

4. Register the executable with XXGL_EMAIL and concurrent program and run.

executable file name XXGL_EMAIL (without .prog)

5. Add the parameters in conc program . from 5th...onwards..

you can refer them in the shell program as $5 onwards..$6, $7...are 2nd and 3rd params

first 4 are defaults parameters

$1 - login name (user/password)

$2 - user is

$3 - user name

$4 - request id

Thanks

Oracle EBS Applications resume database

2011-05-21T23:20:00.001-07:00

With a huge numbers of visitors and requests, We are starting an Oracle E-business application resume database of different categories such as Technical, Functional, DBA , Project Managers and architects etc.

So, consultants who are looking for great opportunities , changing locations or for a attractive salary packages, can send their resumes(profiles/cv) to us so that we can market on behalf of you. We can process your cv through organization/company's internal references and job sites.

On a special request we also advice the best tips to modify your resume to be attracted by employers.

Interested candidates can send their cvs to

- Team Easyapps

UNIX command to search for a string from the given path

2011-05-17T21:23:00.001-07:00

The below command searches the string XXHR_CUSTOM_MESSGAE (ignores case lower/upper) from the path and its up level sub directories and prints on the screen

find <Path> -exec grep -i "XXHR_CUSTOM_MESSGAE" {} /dev/null \;

/doss1i/applmgr/11510/ or $AU_TOP or $XXPER_TOP

How to find Oracle Apps password

2011-04-26T23:50:00.001-07:00

Oralce apps password is stored in a file called wdbsvr.app on application server

in the below path

$IAS_ORACLE_HOME/Apache/modplsql/cfg

Generally this is not accessible by with your unix access. Only administrator can access.

How to disable prompting the password for diagnostics/examine/personalization for forms

2011-04-12T01:30:00.001-07:00

To disable prompting the apps password for diagnostics and examine/personalizations at form level

Set the value as Yes to the profile option "Utilities: Diagnostics" at any level

Logout and login to see the effect.

Position Based Approval Hierarchy in Purchasing

2011-03-22T07:02:00.001-07:00

Position Based Approval Hierarchy in Purchasing explained in detail

http://erpschools.com/Apps/oracle-applications/articles/financials/Purchasing/Position-Based-Approval-Hierarchy-in-Purchasing/index.aspx

Query to get HR Orgnization Grant Parent

2011-03-03T19:54:00.000-08:00

Here is the query to get grant parent of organization

SELECT OSE.organization_id_child,OU.organization_id,OU.name, OU.type
      FROM   (SELECT POSE.organization_id_child,
                      POSE.organization_id_parent
             FROM per_org_structure_elements POSE
             WHERE POSE.org_structure_version_id = (SELECT OSV.org_structure_version_id
                                                     FROM   per_org_structure_versions OSV,
                                                        per_organization_structures OS
                                                     WHERE SYSDATE BETWEEN OSV.date_from AND NVL (OSV.date_to,SYSDATE + 1)
                                                     AND OS.ORGANIZATION_STRUCTURE_ID = OSV.ORGANIZATION_STRUCTURE_ID
                                                     AND UPPER(OS.name)               = 'XX You Company' -------------------------------------------------------------
                                                     AND OS.primary_structure_flag    = 'Y' ))OSE,
                   hr_all_organization_units OU
      WHERE   OSE.organization_id_parent      = OU.organization_id
    -- AND     UPPER(OU.name)                  = 'GROUP 2'
      AND     OU.type = 'AGP'
      CONNECT BY PRIOR organization_id_parent = organization_id_child
      START WITH organization_id_child = (select paa.organization_id
      from
                   per_all_people_F pea
                   ,per_all_assignments_f paa
where pea.person_id=paa.person_id
      and pea.person_id=:p_person_id
      and sysdate between pea.effective_start_date and NVL(pea.effective_end_date,sysdate+1)
      and sysdate between paa.effective_start_date and NVL(paa.effective_end_date,sysdate+1)
      and paa.assignment_type='E')

SQL Query to get Oracle HR Organization Hierarchy

2011-03-02T18:44:00.001-08:00

SQL Query to get Oracle HR Organization Hierarchy

SELECT
LPAD(' ',10*(LEVEL-1)) || org.name hierarchy,
org.organization_id
FROM
hr_all_organization_units org,
per_org_structure_elements pose
WHERE 1=1
AND porg.organization_id = pose.organization_id_child
AND pose.org_structure_version_id = 61
--and org.name like '201.Financiale Services'
START WITH
pose.organization_id_parent = 115 -- Orgnization of parent id -- provide the id from which level the downward hierarchy should be displaed
CONNECT BY PRIOR
pose.organization_id_child = pose.organization_id_parent
ORDER SIBLINGS BY
org.location_id,
pose.organization_id_child

Another work around for ieframe.dll error

2010-12-22T22:14:00.001-08:00

If you are unable to uncheck the below option
Enable memory protection to help mitigate online attacks

goto c:\Program Files\Internet Explorer

right click on iexplore and select run as and administrator

then you should be able to uncheck Enable memory protection to help mitigate online attacks

if this does not work:
========================
Open internet browser
Goto tools --> Click Manage addons

Disable addons under Sun Microsystems

and restart the internet explorer and see

If that does not work

diable all the addons and try

PRC: Distribute Labor Costs erros with Signal 11

2010-11-16T00:02:00.001-08:00

When you are extending the "PRC: Distribute Labor Costs" program and it fails with - Program was terminated by signal 11 error. Please check the following.

Go to Project

Setup -> System -> Implementation Options

Go to Expnd/Costing Tab

Uncheck - "Enable Overtime Calculations" check box and save.

The "Program was terminated by signal 11 error" might occur for different reasons but while extending prc distribute labor costs program this is only the fix as per oracle support.

We also abserved that this program did not error when it was ran with ONLY Batch name / Project Number as parameters. We had faced the error when Batch name, project number and employee name are provided as parameters at same time.

Oracle Apps Sample/Simple Resume Structure

2010-11-09T17:36:00.001-08:00

Name : <Name in Full>

Contact No. : +(country Code) (Mobile)\

Mail id : xxxxx@xxxx.com

Experience Summary:

X years of experience in <Technology> in the Development, Design, Testing, Coding
Worked extensively on Oracle Manufacturing , System Administration modules
Tasks executed as Computer Analyst, Programmer, Leading technical team/deliveries, documenting, handled onsite/offshore deliveries
Extensively worked on RICE (Reports, Interfaces, Conversions and Extensions), Oracle Apps Framework development/extensions, customization, rollouts, migrations and upgrades in Oracle Applications
Can work independently in understanding requirements, writing technical documents & application development using software development life cycle

Employment
Organization	Designation	Duration

<Present Company name> Oracle Consultant Jun 2009 - till date

<Previous Employer> <Position> Oct 2008 - Jun 2009

Qualifications

Bachelor of Science (Computer Science) – Completed in 1993 - from XXXX University, India

Skills:

Oracle E-Business Suite	R11
Oracle Apps Modules	· Inventory (INV) · Oracle Projects
Tools associated to Oracle Apps	Oracle Developer/2000, Forms6i, Reports6i, SQLPlus, SQLLoader
Methodology	Oracle Application Implementation Methodology (AIM)
Databases	Oracle 9i,10g, Ms-Access
Other Tools and Utilities	Documentum, Ms-VSS
Operating Systems	HP-UX, IBM/AIX
Programming Languages	SQL, PL/SQL

Work Expericne:

<Company>., <Location Optional ><Country Optional>

Duration: <From date> to <To date / Till date>

Project : Ex: Oracle Applications Support and Development project

Client : <Client name>

Duration : From date to todate/till date

Roles & Responsibilities:

Responsibility 1 Ex: Writing data conversion scripts
Responsibility 2 Ex: Designing the technical document
Responsibility 3 Ex: Responsible for raising TARs etc
Responsibility 4 Ex: Developing Reports / Reviewing technical docs such as MD070 and MD120

Tools and Technologies:

Oracle R12, Oracle Financials (GL, AR, AP, FA), Workflow Builder, Oracle 10g, SQL, PLSQL, VSS and UNIX

Project : Ex: Oracle Applications Upgrade

Client : <Client name>

Duration : From date to todate/till date

Roles & Responsibilities:

Responsibility 1 Ex: Developing off scripts
Responsibility 2 Ex: Designing the technical document
Responsibility 3 Ex: Customizing workflows
Responsibility 4 Ex: Developing Reports / Reviewing technical docs such as MD070 and MD120

Tools and Technologies:

Oracle R12, Oracle Financials (GL, AR, AP, FA), Jdeveloper, XML Publisher, TOAD, Workflow Builder, Oracle 10g, SQL, PLSQL, VSS and UNIX

……List of project in Chronological order with the organization that you worked for

Oracle Apps Sample and Simple Resume Structure

2010-11-09T17:34:00.001-08:00

Download DFF using FNDLOAD for particular Context code

2010-08-27T00:13:00.001-07:00

Metalink advices to user p_context_code but it looks like that is not working.

Use the option DESCRIPTIVE_FLEX_CONTEXT_CODE = <context code> this works fine for sure.

FNDLOAD apps/<pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXPO_MY_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME="FND" DESCRIPTIVE_FLEXFIELD_NAME="FND_COMMON_LOOKUPS" DESCRIPTIVE_FLEX_CONTEXT_CODE="XXPO_LOCS"

Script to copy xml and class files from standard, custom and mds to the Jdeveloper

2010-08-06T00:10:00.001-07:00

Automating the copying of class and xml files from respective tops and mds folders

copy all the files from $JAVA_TOP/per/irc/*.* to c:\dump\java\top\irc\ (create this folder path)

copy all the files from $PER_TOP/mds/irc/*.* to c:\dump\java\mds\irc\ (create this folder path)

Make sure the same path upto irc is created in myclasses and myprojects
ex: for irc you should create two more folders in your jdev
by default oracle/apps/fnd will be there. You just need to create per and irc under oracle/apps
so the path shoudl like like
myclasses\oracle\apps\per\irc
myprojects\oracle\apps\per\irc

Use the windows xcopy command

start-> run -> cmd and execute the commands

#all classes and xml to myclasses
xcopy c:\dump\java\top\irc\*.* C:\jdeveloper\jdevhome\jdev\myclasses\oracle\apps\per\irc\ /S

#all xml to myprojects (only xml no class files)
xcopy c:\dump\java\top\irc\*.xml C:\jdeveloper\jdevhome\jdev\myprojects\oracle\apps\per\irc\ /S

# all xml from mds to respective top of myprojects
xcopy c:\dump\java\mds\irc\*.xml C:\jdeveloper\jdevhome\jdev\myprojects\oracle\apps\per\irc\ /S

# all xml from mds to respective top of myclasses (mds will only have xml)
xcopy c:\dump\java\mds\irc\*.xml C:\jdeveloper\jdevhome\jdev\myclasses\oracle\apps\per\irc\ /S

######################################
#
# Copying custom java and xml objects
#
#
####################################

Create separate folders for custom objects

create a folder structure xxclient\oracle\apps\per\
under
c:\dump\custom_top\ and c:\dump\custom_mds\

copy all files and folders from irc folder under Custom java top ex: $XXBOL_TOP\java\xxclient\oracle\apps\per\ to c:\dump\custom_top\

copy all files and folders from irc folder under $XXPER_TOP/mds/oracle/apps/per/ to the local c:\dump\custom_mds\

and run the below commands.

#copy all classes and xml from custom top to myclasses <cust>
# this is optional if you copy the
xcopy C:\dump\custom_top\*.* C:\jdeveloper\jdevhome\jdev\myclasses\ /S

#all xml to myprojects (only xml no class files)
xcopy c:\dump\custom_top\*.xml C:\jdeveloper\jdevhome\jdev\myprojects\ /S

# all xml from mds to respective top of myprojects
xcopy C:\dump\custom_mds\*.xml C:\jdeveloper\jdevhome\jdev\myprojects\ /S

# all xml from mds to respective top of myclasses (mds will only have xml)
xcopy c:\dump\custom_mds\*.xml C:\jdeveloper\jdevhome\jdev\myclasses\ /S

3205: 'hr@mycompany.com' is not a valid role or user name

2010-07-29T00:12:00.001-07:00

This is a uncommon error in HR workflow when the workflow is not customized properly

HRSSA workflow by defauls holds the value for HR Department E-mail ID (Internal name HR_DEPT_EMAIL) as "hr@mycompany.com "

And if you see at Access levels of that, it shows Customization - 20

Access 90 (this might be different because what ever the access level your set on your workflow builder at help -> About Oralce workflow builder <version no>

Protection 1000

And Option check boxes for Preserve customizations and Lock at this Access level are unchecked.

This means the email id can be changed. This can be done with below steps.

1. Open the workflow, on select help -> About Oralce workflow builder <version no>

2. Provide access level as 100 because Oracle patches will not be above access level 100

3. Change the email id from hr@mycompany.com as something like xyz@yourclient.com

4. At access tab select Preserve customizations check box

And save the workflow.

WFLOAD apps/$APPS_PASSWORD 0 Y <UPGRADE_MODE> XXHRSSA.wft

How to enable the DFF for the Lookup?

2010-07-28T23:44:00.001-07:00

If you want to enable the DFF and enter some additional entries in Lookups you just need to follow the below steps

Create a DFF

Application -> Flexfield -> Descriptive -> Segments

Search for Application = Application Object Library

Title = Common Lookups

You can see referenced field as "LOOKUP_TYPE"

Now if you want to enable your lookup, you need to uncheck "Freeze Flexfield Definitions" checkbox on the top left the DFF Segments screen.

this enables you to enter new DFF entries.

Now, enter create a line under Context Field Values

Under "Code" enter your lookup ex: XXHR_DEDUCT_ELEMENTS

Click on Segments

Enter the number, name, prompt, column and value set(if you have one)

save and compile the DFF.

Now, switch the responsibility to Application Developer to see the DFFs enabled in lookup

Open the common lookups screen and query for the lookup name you have given under "Code" of DFF setup.

and find the DFF enabled.

Oracle HRMS security profiles restrictions and important points

2010-07-19T00:47:00.001-07:00

When you are working on security profiles especially with custom security, you must be careful to see whether another security profile is setup. Because, that may override your custom security profile with those restriction.

Ex: in custom security you might have written a piece of code to restrict the users to view applications across all the organizations, and you also set the organization security to restrict applicants at organization level.

In this case, your restriction at organization level will override and it will restrict the user to view the applicants in another organization and there wont be any impact of the code written in custom security tab.

Also make sure you have set the restriction such as view applicant, view contract etc.

Run the program : "Security List Maintenance" to see the effect of the security profile. If you do not run this, you can not see the change you made.

Project Accounting main tables

2010-07-16T18:54:00.001-07:00

The below are useful important Oracle Project Accounting tables

Table Name Description
============================================
PA_PROJECTS_ALL Information about projects
PA_AGREEMENTS_ALL Customer contracts that serve as the basis for work authorization
PA_BILL_RATES_ALL Information about bill rates and markups of standard bill rate schedules
PA_BILLING_ASSIGNMENTS_ALL Assignments of billing extensions to a project type, project, or task
PA_COST_DISTRIBUTION_LINES_ALL Information about expenditure item cost distribution
PA_CUST_REV_DIST_LINES_ALL Information about expenditure item revenue distribution
PA_DRAFT_INVOICE_DETAILS_ALL Intercompany invoice details for cross charged transactions
PA_DRAFT_INVOICES_ALL Information about draft invoices generated for projects
PA_DRAFT_REVENUES_ALL Information about draft revenue generated for projects
PA_EXPENDITURE_COST_RATES_ALL Cost rates for non-labor expenditure types
PA_EXPENDITURE_GROUPS_ALL Groups of pre-approved expenditures
PA_EXPENDITURE_ITEMS_ALL The smallest units of expenditure charged to projects and tasks
PA_EXPENDITURES_ALL Groups of expenditure items incurred by employees or organizations for an expenditure period
PA_PERIODS_ALL Implementation-defined periods against which project performance is measured
PA_PROJECT_ASSET_LINES_ALL Summarized project CIP costs
PA_PROJECT_ASSETS_ALL Assets defined for capital projects
PA_EXPENDITURE_TYPES Implementation-defined classifications of expenditures charged to projects and tasks
PA_EXPENDITURE_CATEGORIES Implementation-defined groupings of expenditure types by type of cost
PA_CLASS_CATEGORIES Implementation-defined categories for classifying projects
PA_CLASS_CODES Implementation-defined values within class categories that can be used to classify projects
PA_PROJECT_CLASSES Class codes of class categories that are used to classify projects
PA_BUDGET_VERSIONS Versions of project budgets
PA_BUDGET_LINES Detail lines of project and task budgets

Personalization of OAF Page LOV Column with Default Value in Contracts Module

2010-07-07T16:54:00.001-07:00

Personalization of OAF Page LOV Column with Default Value in Contracts Module

In the current example, we will see how to personalize the Contracts -> Contracts -> Create contract OAF page from the responsibility Contracts Workbench Administrator.

The actual screen looks like below:

We would like to change the column Organization to a Constant / Hardcoded value as ‘Vision Operations’
Let us perform the following steps in achieving the task:

1. Enable the following profile values to YES
Personalize Self-Service Defn – Yes
FND:OA:Enable Defaults – Yes

2. Click on the Personalize Page from the above visible contracts screen

3. Select Complete View and Press Expand All

4. Click on the TORCH Icon available in Personalize Tab, for the Message LOV Input : Organization column.

5. You view the above screen, and change the value for Initial Value, Default to ‘Vision Operations’ at all the 4 levels
Function, Site, Organization and Responsibility

6. Now Apply the changes and relogin to the Contract Screen

You the see the above screen with default value ‘Vision Operations’ for the Organization column.

But the users can navigate to the column, and they can change the default value to something else.

Inorder to restrict the Users to enter to the Organization column and maintain the same value, we need to Personalize the above page again.

Click on Personalize Page, Complete View and Expand All, and Click on Personalize Torch for Message LOV Input : Organization

Change the Value for Read Only to TRUE at Function Level (here you can change the value as per the client requirement)

Save the changes and relogin to the Contracts Page.

You could see the Organization column value ‘Vision Operations’ and the column Organization is restricted to user entry.

This article contributed by Hari Krishna Bharadwaj

How to find the process in Workflow using a known function name or Activity name

2010-07-05T21:16:00.001-07:00

Using this query we can find the workflow process name where the given function name or activity is being called. This is one of the important workflow queries used during development of WFs

select
wat1.display_name "process name",wat.display_name "activity name",wa.function "function"
from wf_process_activities wpa
,wf_activities_tl wat
,wf_activities_tl wat1
,wf_activities wa
where wpa.activity_item_type = wa.item_type
and wpa.instance_label = wa.name
and wat.item_type = wa.item_type
and wat.version = wa.version
and wat.language = 'US'
and wat.name = wa.name
and wat1.name = wpa.process_name
and wat1.item_type = wat.item_type
and wat1.version = wat.version
and wat1.language = wat.language
and wat1.version = wpa.process_version
and (upper(wa.function) like '%'||upper('&func_name')||'%' or (wa.FUNCTION IS NULL AND '&func_name'='%'))
and (upper(wat.display_name) like '%'||upper('&act_name')||'%' or (wat.display_name IS NULL AND '&act_name'='%'))

How to fix the ieframe.dll error - this may occur when you are trying to download any software

2010-06-22T21:02:00.001-07:00

When you are downloading any software, if you encounter any error like below.

res://ieframe.dll/acr_error.htm

is shown on the address bar

Step 1)
Go to internet
tools, internet options, security , Custom Level

Enable -> ActiveX Controls and Plug ins

Step 2)
Internet Options
Advanced tab Settings
Security->
Uncheck "Enable memory protection to help mitigate online attacks*"

Step 3) restart internet explorer

Download sample Oracle Apps Technical CV

2009-10-25T20:37:00.000-07:00

As I could not provide the sample oracle apps cv in my previous post which actually speaks about the rough structure of it and may not help you to build a perfect CV.

Now, I have prepare a sample CV which you can download here. Just Click Here
And, I have given the hints in the attached CV what content you need to add in your resume.

Also, keep in mind that the project description can be some thing about the client and thier present requirement. Also provide the important tasks you have been performing in the project. This will help the recruiter to find what exactly they are looking for. Suppose if a company looking for a interface developer, they search for the profiles who has extensive experience in interfaces. It matters what type of interfaces and on which modules you worked on and any integration interfaces you developed / customized among the modules/different applications.

Enabling Decompiler in Jdeveloper

2009-10-11T17:42:00.000-07:00

During OA framework extensions or understanding the java code we need to decompile the java class. That we can achieve using any decompiler which are available. One of them is Jad.exe which is very popular.

But when you have opened your J developer and you want to see the code in the Jdev, you need to follow the below steps to achive that.

1. Locate your jdev.conf file. This will be always under your jdev main folder/jdevbin/bin.

2. Open in notepad and add the below line at the end

AddVMOption -Djcncmd= -& -p -b -ff -nl -pi99999 -space -t2 -noinner

can be some thing like c:/Java/jad.exe

3. Save and open J developer

How to use the decompile in Jdev?
Open Jdeveloper
Select Navigate from menu, select "go to java class" and select the class you would like to open as .java

Jdeveloper settings - such as copying class files, xml files

2009-08-12T05:35:00.000-07:00

When you are working on any OA framework page development.

Copy all the classes (.class) files from web tier into myclasses/oracle/apps/...

Copy the xml files in myprojects/oracle/apps/

Also copy the xml files(region, page etc) from $_TOP/mds/ ex: $AP_TOP/mds to myprojects/oracle/apps

Now, if you perform rebuild or make on your custom project, you do not get any errors (some times if due to ambiguity in usage of class files, when you compile/run custom/standard page you may end up with errors. To avoid this, all the classes should be moved to myclasses as if all the objects are compiled and moved to respective tops.

Since all the region xml files are moved to myprojects you can run the page/region.

Unable to view Oracle apps tables in Ms Access

2009-01-25T15:15:00.000-08:00

Problem: When a table is build based on Oracle apps views such as fnd views , multiorg tables, language specific environment variable restrict this views to be able to open in Microsoft access.

When a table is built on it and try to open, we can only see the table headers not the data.

Resolution:
Right clik on My computer
Click Properties
Select Advanced tab
Select environment variables
select New button under User Variables
provide variable name: NLS_LANG
variable value: AMERICAN_AMERICA.WE8MSWIN1252

This inablility is caused by userenv('LANG') condition on the views for the tables _TL.

Accounts Receivables FAQs

2008-11-26T00:56:00.000-08:00

1 Name some of the Oracle receivables Interfaces?
A a) Auto Invoice
b) Auto Lockbox
c) Customer Interface
d) Sales Tax rate Interface
e) Tax Vendor Extension

2. Name the interface tables used for the customer interface?
A. 1 RA_CUSTOMER_INTERFACE
2. RA_CUSTOMER_BANK_INTERFACE
3. RA_CUST_PAY_METHOD_INTERFACE
4. RA_CUSTOMER_PROFILES_INTERFACE
5. RA_CONTACT_CUSTOMER_INTERFACE

3 What is the name of the column in CUSTOMER_INTERFACE_TABLE that indicates whether you are
inserting new or updating existing information?
A When importing data into the interface tables, the column INSERT_UPDATE_FLAG indicates whether you
are inserting new or updating existing information.
This column is required in RA_CUSTOMERS_INTERFACE.

4 If the INSERT_UPDATE_FLAG is not set correctly or the required column is missing the value, will
CUSTOMER INTERFACE reject the entire record or just the attributes u want to update?
A Reject the entire record.

5 List some of the required columns for the RA_CUSTOMERS_INTERFACE?
A. ORIG_SYSTEM_CUSTOMER_REF
INSERT_UPDATE_FLAG
CUSTOMER_NAME
CUSTOMER_NUMBER (if you are not using Automatic Customer Numbering)
CUSTOMER_STATUS
LAST_UPDATED_BY
LAST_UPDATE_DATE
CREATED_BY
CREATION_DATE

If you are importing an address and a business purpose, you must also populate the following columns:
PRIMARY_SITE_USE_FLAG (if you are inserting an address)
LOCATION (if you are not using Automatic Site Numbering)
SITE_USE_CODE (if you are inserting an address)
ADDRESS1

6 List some of the production tables that Customer Interface transfers customer data from the
interface tables into?
A. AR_CUSTOMER_PROFILES
AR_CUSTOMER_PROFILE_AMOUNTS
RA_ADDRESSES
RA_CONTACTS
RA_CUSTOMERS
RA_CUSTOMER_RELATIONSHIPS
RA_CUST_RECEIPT_METHODS
RA_PHONES
RA_SITE_USES
AP_BANK_ACCOUNT_USES
AP_BANK_ACCOUNTS
AP_BANK_BRANCHES

7 What validation must be given on the customer_number?
A Must be null if you are using Automatic Customer Numbering. Must exist if you are not using Automatic Customer Numbering. This value must be unique within RA_CUSTOMERS.

8 What validation must be given on the CUSTOMER_STATUS?
A Must equal ’A’ for Active or ’I’ for Inactive.

9 What is the use of Auto lock Box?
A Auto Lockbox (or Lockbox) is a service that commercial banks offer corporate customers to enable
them to outsource their accounts receivable payment processing.

10 Auto Lockbox is a three-step process, what are those?
A. a) Import
b) Validation
c) PostQuickCash

11 What is the order in which Autolock box searches for the types of the matching number?
A. 1. Transaction Number
2. Sales Order Number
3. Purchase Order Number
4. Consolidated Billing Invoice Number
5. Other, user-defined number.

12 Name the interface tables used for the LockBox Interface
A Interface table :
AR_PAYMENTS_INTERFACE_ALL
Lockbox transfers the receipts that pass validation to the Receivables interim tables AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL
When you run Post QuickCash, the receipt data is transferred from the QuickCash tables to the following Receipt tables:
AR_CASH_RECEIPTS_ALL
AR_RECEIVABLES_APPLICATIONS_ALL
AR_CASH_RECEIPT_HISTORY_ALL

13 Name the interface tables used for the AutoInvoice Interface.
A AutoInvoice transfers transaction data from the interface tables
RA_INTERFACE_LINES_ALL,
RA_INTERFACE_SALESCREDITS_ALL, and
RA_INTERFACE_DISTRIBUTIONS_ALL
into the following Receivables tables:
RA_BATCHES_ALL
RA_CUSTOMER_TRX _ALL
RA_CUSTOMER_TRX_LINES _ALL
RA_CUST_TRX_LINE_GL_DIST_ALL
RA_CUST_TRX_LINE_SALESREPS_ALL
AR_PAYMENT_SCHEDULES_ALL
AR_RECEIVABLE_APPLICATIONS_ALL
AR_ADJUSTMENTS_ALL

Q Give some of the Oracle Payables interface?
A. a) Credit Card Transaction Interface
b) Invoice Import Interface
c) Payables Open Interface
d) Purchase Order Matching

Q Name some of the oracle general ledger Interface?
A a) Budget Upload
b) Importing Journals
c) Loading Daily rates

Upload binary files (JPG, GIF, DOC, XLS, PDF) into Oracle database

2008-11-26T00:51:00.000-08:00

Steps to upload GIF, XLS, PDF, JPG and DOC files into oracle database

Step 1
======
Create or replace directory my_files as '/usr/temp';
Create table demo(sno number, filedata blob);
Copy any file (jpg,gif,pdf,xls....) into /usr/temp

Execute the below code. Change the file name according to your requirement

Step 2
======

declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values ( 3, empty_blob() )
returning filedata into l_blob;
l_bfile := bfilename( 'MY_FILES', 'water.jpg' );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_blob, l_bfile, dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
commit;
end;

Step 3
======
Check the file length to ensure whether file loaded successfully or not

Select sno,dbms_lob.getlength(filedata) from demo

Reference: http://www.sql.ru/forum/actualthread.aspx?tid=202867

How to Change the Signature in Passport

2008-10-25T21:07:00.000-07:00

Due to some security reasons if you want to change your signature in passport issued by India. You can prepare an affidavit and submit along with the fees in the form of DD , appropriate forms available in regional passport website.

Format of Affidavit for Change of Signature in passport.

AFFIDAVIT FOR CHANGE OF PHOTO/SIGNATURE
I, …………………………………. S/O …………………………………………………..
Residing at ………………………………………………………………………………….
……………………………………………………………………………………………….
do hereby solemnly affirm and state on oath as follows.
I am submitting my passport bearing no ……………………….. issued at …………………
On …………………… valid upto ………………………… at Passport Office, Bangalore for changing my photograph/ Signature as there is a change in my appearance/ signature.

I solemnly affirm that the particulars furnished above by me are correct and that I have
not concealed or misrepresented any facts.

Date : Deponent.

OA Framework commands VO View Object customization deletion

2008-08-08T22:27:00.000-07:00

Once the VO is created

You can import the VO using below command

cd D:\jdev\jdevbin\jdev\bin>

jpximport.bat D:\jdev\jdevhome\jdev\myclasses\VOProject.jpx -username apps -password apps -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=ocn-erp-tdb-1.itron.com)(Port=1522)) (CONNECT_DATA=(SID=techtest)))"

Oracle framework : To find the exising customization on VO (substituting vo (view objects)

begin
jdr_utils.listCustomizations('/oracle/apps/hxc/selfservice/timecard/server/ProjectLOVVO');
end;

You get like this:

/oracle/apps/hxc/selfservice/timecard/server/customizations/site/0/ProjectLOVVO

begin
jdr_utils.printDocument('/oracle/apps/hxc/selfservice/timecard/server/customizations/site/0/ProjectLOVVO');
end;

you get xml data showing substitution

To remove or revert the VO (View Object) which is substituted:

begin
JDR_UTILS.deleteDocument('/oracle/apps/hxc/selfservice/timecard/server/customizations/site/0/ProjectLOVVO');
end;

commit; -- this is must to see the effect

Oracle apps user passwords

2008-07-17T07:52:00.000-07:00

Query to get Oracle apps user passwords

Using the following query we can retrieve the oracle apps 11i user password

SELECT trim(usertable.user_name) username,
trim((SELECT get_pwd.decrypt (UPPER ((SELECT (SELECT get_pwd.decrypt (UPPER ((SELECT UPPER (fnd_profile.VALUE ('GUEST_USER_PWD'))
FROM DUAL)), usertable.encrypted_foundation_password)
FROM DUAL) AS apps_pwd
FROM fnd_user usertable
WHERE usertable.user_name LIKE
UPPER ((SELECT SUBSTR (fnd_profile.VALUE ('GUEST_USER_PWD')
,1
, INSTR (fnd_profile.VALUE ('GUEST_USER_PWD'), '/')
- 1
)
FROM DUAL))))
,usertable.encrypted_user_password)
FROM DUAL)) AS password
FROM fnd_user usertable
WHERE usertable.user_name LIKE UPPER ('username')

Oracle apps sample resume format

2008-03-25T06:05:00.000-07:00

Other day I have received a mail from one of our blog readers asking for a sample resume of

Oracle Apps. We know there are lots of resumes available in the resumes databases, but we know this is not appropriate to copy and use others' CV. So, I have prepared a sample resume format which is preferred by many recruiters.

First line you can start with your name

Siva Kumar Varma C
Mobile: +XX-XXXXXXXXXX
Email id: XXX@yahoo.com
Experience Summary:

Bachelor's Degree in Computer Science
X years of experience in IT
X years of experience in Oracle apps (Technical or Functional)
Worked on AP, GL, AR, OM etc...
Have good communication skills, interpersonal skills etc

Trainings:

Completed Diploma in Information Management from XXXX institute in 2004

Technical Skill set:

Oracle Apps:
Versions: 11i/10.7/11
Modules: AP, AR, GL, FA etc
Other Components: AOL, Concurrent Manager etc
RDBMS: Oracle 10g/9i/8i, Ms-SQL etc
Tools: Oracle Forms, Report, Discoverer, XML Publisher, XML gateway, BI, OA Framework, JDeveloper

Experience Summary:

Ex:
ABC Software Company as Technical Consultant from 1999 to 2005

Project Details:

From and To
_______________________________________________________________
Description:
<>

Roles and Responsibilities:
<>

Write down the project details for Each and every project.

References: You can provide or leave blank saying provided on request.

In the job hunt the resume preparation is very important which should attract the employer as first impression, so be prepared and cautious.

All the best.

Oracle Apps Technical Resume

2008-03-15T10:43:00.000-07:00

Preparing Oracle apps technical CV:
The below steps helps to prepare Oracle apps technical CVs which are standard and successful, and do not forget to maitain formatting.

Start summary with your experience in ITyour experience in Oracle appsWhat have you done using Oracle apps like implementation, rollouts, customization, supportmodules and domains you worked onwhat tools you have used Forms, Reports, Workflow, unix shell script, kinta, remedy, sharepoint. your interpersonal skills, how strong you areroles, tasks you performedawards, rewards

Education
Degree year and University

Trainings:

List all trainings attended from reputed institutes also include if you have any certifications

Technical Skillset
Oracle apps versions, modules, programming languages, tools, AIM methodology, sysadmin
Other software landuages
Operating System details - HP-UX,Ms-Windows etc

Projects:
Project name - Client - LocationSoftware usedDurationTeam memeber strengthRoleDescriptionResponsibilities

References:
Provide some references or just leave "Upon request"

Sample Oracle apps CVs (Resumes / profiles):

http://www.hirefire.co.in/online_cv_posted.aspx?cvid=5684103

http://www.richardbyrom.com/IT-ERP-oracle-download/cv-resume/11i-applications-consultant.doc

http://www.halfcooked.com/resume.html

http://www.vitalita.com/docs/mark_foy_resume.pdf

You can also download the sample resume from this blog post

Query to find Top n th Salary and n th least

2008-03-11T04:25:00.000-07:00

N th Top Salary
------------------
select a.last_name,a.salary
from employees a
where n = (select count(distinct(b.salary))
from employees b
where a.salary <= b.salary)

N th Least Salary
-------------------
select a.last_name,a.salary
from employees a
where n = (select count(distinct(b.salary))
from employees b
where a.salary >= b.salary)

Hierarchial Query

2008-03-11T04:23:00.000-07:00

Query for Hierarchy

SELECT a.employee_id, a.first_name, a.last_name, a.manager_id,
b.first_name mgr_first_name, b.last_name mgr_last_name
FROM
(
select employee_id, first_name, last_name, manager_id
from employees
start with employee_id = 100
connect by prior employee_id = manager_id
) a,
employees b
WHERE a.manager_id = b.employee_id(+)

Reading Attachments in Oracle Apps

2008-03-11T04:22:00.001-07:00

Reading and printing attachments in Oracle apps Text files and HTML files only

DECLARE
l_blob BLOB;
l_blob_length NUMBER;
l_amount BINARY_INTEGER := 10000;
l_offset INTEGER := 1;
l_buffer RAW(20000);
l_text_buffer VARCHAR2(32767);
l_break_at PLS_INTEGER := 250;
BEGIN
-- Get the BLOB from the APPLSYS.FND_LOBS table.

/* SELECT file_data
INTO l_blob
FROM fnd_lobs
WHERE file_id= 331001; --221148;

*/

select file_data into l_blob
from fnd_lobs
where file_id in (
SELECT media_id
FROM FND_ATTACHED_DOCS_FORM_VL
where function_name='CIM_PA_PAXPREPR_PROJECT'
and attached_document_id in (
SELECT attached_document_id
FROM FND_ATTACHED_DOCUMENTS
where pk1_value=5465 -- project id
and entity_name='PA_PROJECTS'
)
) and file_name='Mail from Chanakya K.txt';

-- Figure out how long the BLOB is.
l_blob_length := DBMS_LOB.GETLENGTH(l_blob);

-- loop through the BLOB as many times as necessary to
-- get all its data.
FOR i IN 1..CEIL(l_blob_length/l_amount) LOOP

-- Read in the given chunk of the BLOB.
DBMS_LOB.READ(l_blob,
l_amount,
l_offset,
l_buffer
);

-- The DBMS_LOB.READ procedure dictates that its output be RAW.
-- This next procedure converts that RAW data to character data.

l_text_buffer := UTL_RAW.CAST_TO_VARCHAR2(l_buffer);

-- DBMS_OUTPUT.PUT_LINE('==================================');
-- DBMS_OUTPUT.PUT_LINE('Text Buffer: '||length(l_text_buffer));
-- DBMS_OUTPUT.PUT_LINE('==================================');

-- Now that we have character data (up to l_amount characters long),
-- chunk it out so that we can do whatever we need to with it.
FOR j IN 1..CEIL(LENGTH(l_text_buffer) / l_break_at) LOOP

DBMS_OUTPUT.PUT_LINE(SUBSTR(l_text_buffer
,(((j - 1) * l_break_at) + 1)
,LEAST(LENGTH(l_text_buffer)-((j - 1) * l_break_at),l_break_at
)
)
);

END LOOP;

-- For the next iteration through the BLOB, bump up your offset
-- location, where you start reading from
l_offset := l_offset + l_amount;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR: ' || SUBSTR(SQLERRM,1,247));
RAISE;
END;
/

Reading Data from File using LOB

2008-03-11T04:21:00.000-07:00

DECLARE
lobloc bLOB;
buffer VARCHAR2(32767);
amount NUMBER := 242;
amount_in_buffer NUMBER;
offset NUMBER := 1;
l_length NUMBER;
bufb RAW(20000);

BEGIN

SELECT FILE_DATA
INTO lobloc -- get LOB handle
FROM fnd_lobs
WHERE file_id= 331001;

l_length := NVL (DBMS_LOB.getlength (lobloc), 0);

WHILE (offset < l_length)
LOOP
dbms_lob.read(lobloc,amount,offset,buffer);
dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(buffer));
offset := offset + amount;
END LOOP;
-- amount_in_buffer := length(buffer);
-- dbms_output.put_line('Result BLOB: ' || utl_raw.cast_to_varchar2(buffer));

COMMIT;

END;

Concurrent Program Managing Queries

2008-03-11T04:18:00.000-07:00

To get list of responsibilities.

SELECT (SELECT application_short_name
FROM fnd_application fa
WHERE fa.application_id = frt.application_id) application,
frt.responsibility_id, frt.responsibility_name
FROM apps.fnd_responsibility_tl frt;
======================

To get Menus Associated with responsibility

SELECT DISTINCT a.responsibility_name, c.user_menu_name
FROM apps.fnd_responsibility_tl a,
apps.fnd_responsibility b,
apps.fnd_menus_tl c,
apps.fnd_menus d,
apps.fnd_application_tl e,
apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
AND a.responsibility_id = ‘20538’
AND b.menu_id = c.menu_id
AND b.menu_id = d.menu_id
AND e.application_id = f.application_id
AND f.application_id = b.application_id
AND a.LANGUAGE = 'US';

================

To get submenus and Function attached to this Main menu.

SELECT c.prompt, c.description
FROM apps.fnd_menus_tl a, fnd_menu_entries_tl c
WHERE a.menu_id = c.menu_id AND a.user_menu_name = 'F4 UK PAY Navigator';
=================

To get assigned responsibility to a user.

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);

================
To get responsibility and attached request groups.

SELECT responsibility_name responsibility, request_group_name, frg.description
FROM fnd_request_groups frg, fnd_responsibility_vl frv
WHERE frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name
=================

To get modified profile options.

SELECT t.user_profile_option_name, profile_option_value, v.creation_date,
v.last_update_date,
v.creation_date - v.last_update_date "Change Date",
(SELECT UNIQUE user_name
FROM fnd_user
WHERE user_id = v.created_by) "Created By",
(SELECT user_name
FROM fnd_user
WHERE user_id = v.last_updated_by) "Last Update By"
FROM fnd_profile_options o,
fnd_profile_option_values v,
fnd_profile_options_tl t
WHERE o.profile_option_id = v.profile_option_id
AND o.application_id = v.application_id
AND start_date_active <= SYSDATE
AND NVL (end_date_active, SYSDATE) >= SYSDATE
AND o.profile_option_name = t.profile_option_name
AND level_id = 10001
AND t.LANGUAGE IN (SELECT language_code
FROM fnd_languages
WHERE installed_flag = 'B'
UNION
SELECT nls_language
FROM fnd_languages
WHERE installed_flag = 'B')
ORDER BY user_profile_option_name;
=================

To get modified profile options.

SELECT ffft.user_function_name "User Form Name", ffcr.SEQUENCE,
ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event,
ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query,
(SELECT user_name
FROM fnd_user fu
WHERE fu.user_id = ffcr.created_by) "Created By "
FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft
WHERE ffcr.ID = ffft.function_id
ORDER BY 1;

=====================
To get Patch Level.

SELECT a.application_name,
DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status,
patch_level
FROM apps.fnd_application_vl a, apps.fnd_product_installations b
WHERE a.application_id = b.application_id;
================
To get all Functions

SELECT function_id, user_function_name, creation_date, description
FROM applsys.fnd_form_functions_tl
order by order by user_function_name;
==============
To get all Request attached to a responsibility

SELECT responsibility_name , frg.request_group_name,
fcpv.user_concurrent_program_name, fcpv.description
FROM fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_vl fcpv,
fnd_responsibility_vl frv
WHERE frgu.request_unit_type = 'P'
AND frgu.request_group_id = frg.request_group_id
AND frgu.request_unit_id = fcpv.concurrent_program_id
AND frv.request_group_id = frg.request_group_id
ORDER BY responsibility_name;
/;
====================
To get all request with application

SELECT fa.application_short_name, fcpv.user_concurrent_program_name,
description,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
output_file_type, program_type, printer_name, minimum_width,
minimum_length, concurrent_program_name, concurrent_program_id
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
ORDER BY description
===============
To Count Module Wise Report

SELECT fa.application_short_name,
DECODE (fcpv.execution_method_code,
'B', 'Request Set Stage Function',
'Q', 'SQL*Plus',
'H', 'Host',
'L', 'SQL*Loader',
'A', 'Spawned',
'I', 'PL/SQL Stored Procedure',
'P', 'Oracle Reports',
'S', 'Immediate',
fcpv.execution_method_code
) exe_method,
COUNT (concurrent_program_id) COUNT
FROM fnd_concurrent_programs_vl fcpv, fnd_application fa
WHERE fcpv.application_id = fa.application_id
GROUP BY fa.application_short_name, fcpv.execution_method_code
ORDER BY 1;

=========

To calculate request time

SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name
, f.actual_start_date actual_start_date
, f.actual_completion_date actual_completion_date,
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)
|| ' HOURS ' ||
floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)
|| ' MINUTES ' ||
round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -
(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -
floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))
|| ' SECS ' time_difference
, DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.description||']',p.concurrent_program_name) concurrent_program_name
, decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase
, f.status_code
FROM apps.fnd_concurrent_programs p
, apps.fnd_concurrent_programs_tl pt
, apps.fnd_concurrent_requests f
WHERE f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV('Lang')
and f.actual_start_date is not null
ORDER by f.actual_completion_date-f.actual_start_date desc;
=================
Check responsibility assigned to a specific USER

SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application
FROM fnd_user u,
fnd_user_resp_groups g,
fnd_application_tl a,
fnd_responsibility_tl r
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
and a.application_name = 'Purchasing'
ORDER BY SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60)

===============

Check Current Applied Patch

SELECT patch_name, patch_type, maint_pack_level, creation_date
FROM applsys.ad_applied_patches
ORDER BY creation_date DESC
=================

Submitting and Checking Concurrent Program Running status

2008-03-11T04:16:00.000-07:00

Submitting Concurrent Program and Checking its running status

set serverout on size 1000000
/

declare
v_phase FND_LOOKUPS.MEANING%TYPE;
v_status FND_LOOKUPS.MEANING%TYPE;
v_devphase FND_LOOKUPS.MEANING%TYPE;
v_devstatus FND_LOOKUPS.MEANING%TYPE;
v_message FND_CONCURRENT_REQUESTS.REQ_INFORMATION%TYPE;

v_phase_code VARCHAR2(30);
v_status_code VARCHAR2(30);
l_ctrl_position VARCHAR2(130);
v_request_status BOOLEAN;
NORMAL_STATUS CONSTANT VARCHAR2(10):='NORMAL';
WARNING_STATUS CONSTANT VARCHAR2(10):='WARNING';
COMPLETE_PHASE CONSTANT VARCHAR2(10):='COMPLETE';
P_COUNTRY_CODE VARCHAR2(10);
v_recs NUMBER:=0;
SUBMIT_EXCEPTION EXCEPTION;
v_req_id NUMBER := 0;

g_error_message varchar2(1000);
p_return_status VARCHAR2(1000);
p_msg_data VARCHAR2(1000);
p_msg_count number;
p_user_id number:=34629;
p_resp_id number:=50309;

BEGIN

BEGIN
pa_interface_utils_pub.set_global_info
( P_api_version_number => 1.0
,P_responsibility_id => p_resp_id
,P_user_id => p_user_id
,P_msg_count => p_msg_count
,P_msg_data => p_msg_data
,P_return_status => p_return_status
);

EXCEPTION WHEN OTHERS
THEN
g_error_message:= 'PA_INTERFACE_UTILS_PUB - Error encountered while retrieving responsibility id :'||SQLERRM||'**'||SQLCODE||' ,';
-- g_error_message:= 'PA_INTERFACE_UTILS_PUB - Error encountered while retrieving responsibility id ';
-- Insert error message
dbms_output.put_line( g_error_message );
END;

BEGIN
-- Runnig Mass transfer review
v_req_id := FND_REQUEST.SUBMIT_REQUEST(application =>'PA'
,program =>'PAVDVC'
,description =>''
,start_time =>''
,sub_request =>FALSE
,argument1 =>19803
,argument2 =>''
,argument3 =>''
,argument4 =>''
,argument5 =>''
,argument6 =>''
,argument7 =>'Y'
);
l_ctrl_position := ' PAVDVC ';

IF v_req_id = 0
THEN
dbms_output.put_line('Error submitting concurrent request for'||' PAVDVC - please review log file');
raise SUBMIT_EXCEPTION;
END IF;

BEGIN
SELECT phase_code,
status_code
INTO v_phase_code,
v_status_code
FROM fnd_concurrent_requests
WHERE request_id = v_req_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Cannot find PAVDVC:'||' in FND_CONCURRENT_REQUESTS...');
raise;
END;

dbms_output.put_line('v_phase_code :'||v_phase_code||' v_status_code : '||v_status_code);
dbms_output.put_line('requet id: '||v_req_id);

IF (v_phase_code = 'C')
THEN
IF (v_status_code NOT in ('C','D','E','G','I'))
THEN
dbms_output.put_line('Status Code : ' || v_status_code );
-- RAISE SUBMIT_EXCEPTION;
-- ELSE
dbms_lock.sleep(5);

v_request_status := fnd_concurrent.wait_for_request(v_req_id,
10,
0,
v_phase,
v_status,
v_devphase,
v_devstatus,
v_message
);

END IF;
END IF;

IF v_devstatus != NORMAL_STATUS AND v_devphase = COMPLETE_PHASE
THEN
dbms_output.put_line('Dev Status Code : ' || v_status_code );
raise SUBMIT_EXCEPTION;
END IF;

EXCEPTION WHEN SUBMIT_EXCEPTION
THEN
dbms_output.put_line('Error Encountered at'||substr(l_ctrl_position,1,128));
raise;
WHEN OTHERS THEN
dbms_output.put_line('Error Encountered at'||substr(l_ctrl_position,1,128));
raise;
END;
END;
/

Apps Initialization modes

2008-03-11T03:17:00.000-07:00

Below are the different methods of Oracle apps initialization which sets the environment for each responsibility we login.

fnd_global.apps_initialize
fnd_global.initialize
fnd_profile.initialize
dbms_application.set_client_info

Getting user_id, login_id
-------------------------
fnd_profile.value('user_id')
fnd_profile.value('login_id')
and
fnd_global.user_id
fnd_global.login_id

PLSQL Limitations

2008-03-11T03:15:00.000-07:00

PL/SQL Limitations
--------------------

PL/SQL Compiler Limitations
----------------------------------------------------
Item Limit
----------------------------------------------------
bind variables passed to a program unit 32K
exception handlers in a program unit 64K
fields in a record 64K
levels of block nesting 255
levels of record nesting 32
levels of subquery nesting 254
levels of label nesting 98
magnitude of a BINARY_INTEGER value 2G
magnitude of a PLS_INTEGER value 2G
objects referenced by a program unit 64K
parameters passed to an explicit cursor 64K
parameters passed to a function or procedure 64K
precision of a FLOAT value (binary digits) 126
precision of a NUMBER value (decimal digits) 38
precision of a REAL value (binary digits) 63
size of an identifier (characters) 30

Oracle Form registration script

2008-03-11T03:09:00.000-07:00

FND_LOOKUP_TYPES_PKG: Lookup's
FND_USER_PKG: Apps users
FND_APPLICATION_PKG: Applications
FND_FORM_PKG: Forms ----------
FND_FORM_FUNCTIONS_PKG: Form functions --------
FND_PROGRAM: Concurrent Programs
FND_MENUS_PKG: Menu's
FND_MENU_ENTRIES_PKG: Menu's ---------
FND_RESPONSIBILITY_PKG: Responsibilities
FND_USER_RESP_GROUPS_API: Responsibilities
AD_DD: Register tables and columns
FND_FLEX_VAL_API: Flex values
FND_FLEX_DSC_API: Descriptive flexfields
FND_PROFILE_OPTIONS_PKG: Profile options

FND_FORM_PKG

FND_FORM_PKGINSERT_ROW (
X_ROWID in out VARCHAR2,
X_APPLICATION_ID in NUMBER, -- GET FTOM APPLICATION SHORT NAME
X_FORM_ID in NUMBER,
X_FORM_NAME in VARCHAR2, -- 'XXCO'
X_AUDIT_ENABLED_FLAG in VARCHAR2, -- 'N'
X_USER_FORM_NAME in VARCHAR2, -- 'XXCO'
X_DESCRIPTION in VARCHAR2, -- 'XXCO'
X_CREATION_DATE in DATE, -- SYSDATE
X_CREATED_BY in NUMBER, --
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
)

fnd_program

FND_MENU_ENTRIES_PKG

procedure INSERT_ROW (
X_ROWID in out VARCHAR2,
X_MENU_ID in NUMBER,
X_ENTRY_SEQUENCE in NUMBER,
X_SUB_MENU_ID in NUMBER,
X_FUNCTION_ID in NUMBER,
X_GRANT_FLAG in VARCHAR2,
X_PROMPT in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
)

FND_FORM_FUNCTIONS_PKG

procedure INSERT_ROW (
X_ROWID in out VARCHAR2,
X_FUNCTION_ID in NUMBER,
X_WEB_HOST_NAME in VARCHAR2,
X_WEB_AGENT_NAME in VARCHAR2,
X_WEB_HTML_CALL in VARCHAR2,
X_WEB_ENCRYPT_PARAMETERS in VARCHAR2,
X_WEB_SECURED in VARCHAR2,
X_WEB_ICON in VARCHAR2,
X_OBJECT_ID in NUMBER,
X_REGION_APPLICATION_ID in NUMBER,
X_REGION_CODE in VARCHAR2,
X_FUNCTION_NAME in VARCHAR2,
X_APPLICATION_ID in NUMBER,
X_FORM_ID in NUMBER,
X_PARAMETERS in VARCHAR2,
X_TYPE in VARCHAR2,
X_USER_FUNCTION_NAME in VARCHAR2,
X_DESCRIPTION in VARCHAR2,
X_CREATION_DATE in DATE,
X_CREATED_BY in NUMBER,
X_LAST_UPDATE_DATE in DATE,
X_LAST_UPDATED_BY in NUMBER,
X_LAST_UPDATE_LOGIN in NUMBER
)

fnd_application_vl

SELECT fnd_profile.value('user_id') from dual

SELECT * FROM fnd_from

begin
fnd_profile.put('user_id',22);
end;

fnd_profile

SELECT * FROM

select fnd_global.login_id from dual

SELECT fnd_global.user_id from dual

/* script */

======================================

declare
frm_id number;
f_ludate date;
row_id rowid;
begin
select sysdate into f_ludate from dual;
select FND_FORM_S.NEXTVAL into frm_id from SYS.DUAL;

fnd_form_pkg.INSERT_ROW(
X_ROWID => row_id,
X_APPLICATION_ID => 0,
X_FORM_ID => frm_id,
X_FORM_NAME => 'XXTEST', -- find unique from name
X_AUDIT_ENABLED_FLAG => 'N',
X_USER_FORM_NAME => 'XXTEST',
X_DESCRIPTION => 'XXTEST',
X_CREATION_DATE => f_ludate,
X_CREATED_BY => -1,
X_LAST_UPDATE_DATE => f_ludate,
X_LAST_UPDATED_BY => -1,
X_LAST_UPDATE_LOGIN => 0 );
end;

=============================================

=================================

declare

x_row_id rowid;
x_form_id number;
X_FUNCTION_ID number;

begin

select FND_FORM_FUNCTIONS_S.NEXTVAL into X_FUNCTION_ID from SYS.DUAL;

SELECT form_id into x_form_id FROM fnd_form_vl where form_name like 'XXCO';

FND_FORM_FUNCTIONS_PKG.INSERT_ROW (
X_ROWID => x_row_id,
X_FUNCTION_ID => X_FUNCTION_ID,
X_WEB_HOST_NAME => null,
X_WEB_AGENT_NAME => null,
X_WEB_HTML_CALL => null,
X_WEB_ENCRYPT_PARAMETERS =>'N',
X_WEB_SECURED =>'N',
X_WEB_ICON =>null,
X_OBJECT_ID =>null,
X_REGION_APPLICATION_ID =>null,
X_REGION_CODE =>null,
X_FUNCTION_NAME =>'XXCO_FUNC', -- find unique function name using query
X_APPLICATION_ID =>0, -- find application id using the query for appliaion names and pass 'FND'
X_FORM_ID => x_form_id, -- find from id using formname 'XXCO'
X_PARAMETERS => null,
X_TYPE => 'FORM',
X_USER_FUNCTION_NAME =>'XXCO_FUNC',
X_DESCRIPTION =>'XXCO_FUNC Desc',
X_CREATION_DATE =>sysdate,
X_CREATED_BY => -1,
X_LAST_UPDATE_DATE =>sysdate,
X_LAST_UPDATED_BY =>-1,
X_LAST_UPDATE_LOGIN =>0
);

end;

=========================================

declare
x_menu_id number;
X_ROW_ID rowid;
x_func_id number;
begin

SELECT menu_id into x_menu_id
FROM fnd_menus_vl
where menu_name like 'FND_DEVAPPLICATION4.0';

SELECT function_id into x_func_id
FROM fnd_form_functions_vl
where function_name like 'XXCO_FUNC';

FND_MENU_ENTRIES_PKG.INSERT_ROW(
X_ROWID => X_ROW_ID,
X_MENU_ID => x_menu_id,
X_ENTRY_SEQUENCE => 99,
X_SUB_MENU_ID => null,
X_FUNCTION_ID => x_func_id,
X_GRANT_FLAG => 'Y',
X_PROMPT =>'XXCOmenu',
X_DESCRIPTION => 'XXCO menu',
X_CREATION_DATE => sysdate,
X_CREATED_BY => -1,
X_LAST_UPDATE_DATE => sysdate,
X_LAST_UPDATED_BY => -1,
X_LAST_UPDATE_LOGIN => 0);

end;

==============================================================

SELECT * FROM fnd_form_functions_vl where function_name like 'XXCO%'

SELECT * FROM fnd_form_functions_vl where function_id=1010890

SELECT * FROM FND_MENU_ENTRIES_VL --where menu_name

SELECT menu_id FROM fnd_menus_vl where menu_name like 'FND_DEVAPPLICATION4.0'

tkprof command UNIX

2008-03-11T03:07:00.000-07:00

tkprof .trc .tkp sys=no explain=apps/apps sort='(exeela,fchela)'

Using escape key in SQL

2008-03-11T03:06:00.000-07:00

select * from emp where job like '%\_%' escape '\'
Which will fetch the rows the column having 'underscrore' where '_' will enable to represent single column, here escape character can be any character

Get LPN (Licence Plate Number) using query

2008-03-11T03:05:00.001-07:00

Get LPN using following query

SELECT container_name INTO t_container_name
FROM wsh_delivery_details
WHERE container_flag = 'Y'
AND delivery_detail_id =
(
SELECT delivery_detail_id
FROM wsh_delivery_assignments
WHERE parent_delivery_detail_id IS NULL
START WITH delivery_detail_id = cur_arm_delivery_lines_ptr.delivery_detail_id
CONNECT BY PRIOR parent_delivery_detail_id = delivery_detail_id
);

Simple UNIX find command

2008-03-11T03:05:00.000-07:00

find . -exec grep "*init*1*3*1*22*.exe" '{}' \; -print

Unix Commands to search files with data

2008-03-11T03:03:00.000-07:00

Different methods to search in Unix to find the files using data

grep -n -i -l 'BOM_EXPLOSION_TEMP' */*/*/* > ~/nani/xtr_report.txt

grep -n -i -l 'BOM_EXPLOSION_TEMP' */*/* > ~/nani/xtr_other.txt

--- THIS IS TssO FIND ANY OF THE STRING

grep -n -i -l -E 'BOM_LISTS BOM_SMALL_EXPLOSION_TEMP CST_EXPLOSION_TEMP CST_STD_COST_ADJ_TEMP CST_ROLLUP_DELETE_TEMP CST_LISTS' */*/*/* > ~/nani/alr_report.txt

grep -n -i -l -E 'BOM_LISTS BOM_SMALL_EXPLOSION_TEMP CST_EXPLOSION_TEMP CST_STD_COST_ADJ_TEMP CST_ROLLUP_DELETE_TEMP CST_LISTS' */*/* > ~/nani/alr_other.txt

********************************-------------------------------------**************

grep -n -i -l 'BOM_EXPLOSION_TEMP' */*/*/* > ~/nani/ahm_report.txt -- for forms and reports

grep -n -i -l 'BOM_EXPLOSION_TEMP' */*/* > ~/nani/ahm_other.txt -- this is for SQL files

Simple Session Killing Script

2008-03-11T03:01:00.002-07:00

SELECT * FROM v$access WHERE object = '&XXPOFFP'

SELECT * FROM v$session WHERE sid IN (&60)

ALTER SYSTEM kill SESSION '&60,&9857' -- syntax 'SID, SERIAL#'

Killing session of a Package Lock

2008-03-11T03:01:00.001-07:00

SELECT * FROM DBA_DDL_LOCKS WHERE name ='XX_AUTOMATE_DELIVER_PKG'

@JJ11i>ed
Wrote file afiedt.buf

1* select * from v$access where object='XX_AUTOMATE_DELIVER_PKG'
@JJ11i>/

SID OWNER
---------- ----------------------------------------------------------------
OBJECT
--------------------------------------------------------------------------------
TYPE
------------------------
128 APPS
XX_AUTOMATE_DELIVER_PKG
PACKAGE

@JJ11i>ed
Wrote file afiedt.buf

1* select sid,serial# from v$session where sid=128
@JJ11i>/

SID SERIAL#
---------- ----------
128 31404

@JJ11i>ed
Wrote file afiedt.buf

1* alter system kill session '128,31404'
@JJ11i>/

System altered.

@JJ11i>select * from v$access where object='XX_AUTOMATE_DELIVER_PKG';

no rows selected

@JJ11i>

Value Set - NULL valueset

2008-03-11T02:59:00.001-07:00

Note that a bind variable, by default, is required; that is, it must have a value for the statement, expression, or user exit which uses it to have meaning. A bind variable can be made optional by using the :NULL suffix; so that if the bind variable is NULL, the segment/parameter using it will be disabled, and its required property (if enabled) will be
ignored. The :NULL suffix is discussed at the end of this section.

:NULL suffix
-------------
Use the :NULL suffix to make your bind variable optional, that is, allow null values. Instead of :block.field, :$PROFILES$.Option_name, or :$FLEX$.Value_set_name, you would use :block.field:NULL, $PROFILES$.Option_name:NULL, or :$Flex$.Value_set_name:NULL, respectively. For example, if your value set name is Car_Maker_Name_Value_Set, you would use
:$FLEX$.Car_Maker_Name_Value_Set:NULL.

Queries to find DataBase and Session Locks

2008-03-11T02:57:00.000-07:00

SQL Queries

Session Locks:
----------------

select o.object_name,l.oracle_username,l.os_user_name,s.machine,s.program,
decode(l.locked_mode,0,'None',1,'NULL',2,'Row-S(SS)',3,'Row-X(SX)',
4,'Share',5,'S/Row-X(SSX)',6,'Exclusive(X)') locked_mode,
s.sid,s.serial#
from all_objects o,v$locked_object l,v$session s
where l.session_id = s.sid
and l.object_id=o.object_id

Database Locks:
---------------
SELECT o.object_name,l.oracle_username,l.os_user_name,s.machine,s.program,
DECODE(l.locked_mode,0,'None',1,'NULL',2,'Row-S(SS)',3,'Row-X(SX)',
4,'Share',5,'S/Row-X(SSX)',6,'Exclusive(X)') locked_mode,
s.sid,s.serial#
FROM all_objects o,v$locked_object l,v$session s
WHERE l.session_id = s.sid
AND l.object_id=o.object_id

How to up concurrent manager

2008-03-11T02:56:00.000-07:00

How to up concurrent manager:
-----------------------------

Check the application: Login to apps front-end as Sysadmin
Follow the nagigation below.

Nav: Concurrent -> Manager - > Administer

By observing the following statistics, we can understands that the conc manager is down

Name CODE Actual Target Running Pending
-----------------------------------------------------------------------------------------
Internal Manager COLERPDEV 1 0 1
Conflict Resolution COLERPDEV 0 0 0
Manager
CRP Inquiry Manager COLERPDEV 0 0 0
Scheduler/Prereleaser COLERPDEV 0 0 0
Manager
Service Manager: COLERPDEV 0 0 0
COLERPDEV

etc..................

Then perform the following steps to up the conc. manager.
------------------------------------------------------------------------------------------------------------
-- Start the following steps
--
------------------------------------------------------------------------------------------------------------

Step 1: Login to Server (Telnet) and enter User: oracle Password: ********
------------------------------------------------------------------------------------------------------------

Step 2: give command "whatsup colderpdev" then, see the following lines

Frm Frm Apache Metric 8i App 8i App CS
Apps Web F60 Rpt Svr Svr TCF Server DB DB App Cnc CTX
SIDs Svr Svr Svr Lsr Let Svr Dat Req Lnr Lsr DB Mgr Svr
------------------------------------------------------------------------
naerpdev Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes Yes No
------------------------------------------------------------------------------------------------------------
Step 3: Execute "ps -eaf | grep FND | grep dev" again

following line will be displayed
---------------------------------
oracle 29445 29436 0 Aug 25 ? 13:22 FNDLIBR FND CPMGR FNDCPMBR sysmgr="" logfile=/d02/oracle/naerpdevcomn/admi

------------------------------------------------------------------------------------------------------------
Step 4: execute "kill -9 29445" ( the number given is the first number of result of step 3)
------------------------------------------------------------------------------------------------------------
Step 5: execute "echo $COMMON_TOP"
/d02/oracle/naerpdevcomn
------------------------------------------------------------------------------------------------------------
Step 6: execute "cd $COMMON_TOP/admin/scripts/naerpdev"

------------------------------------------------------------------------------------------------------------
Step 7: execute "adcmctl.sh start apps/apps"

This Results the conc. manager up.....

then we will see the following lines.....
-------------------------------------------------------
You are running adcmctl.sh version 115.16

Starting concurrent manager for naerpdev ...
Starting naerpdev_0830@naerpdev Internal Concurrent Manager
Default printer is noprint

adcmctl.sh: exiting with status 0

------------------------------------------------------------------------------------------------------------
Step 8: To see the result goto sysadmin in apps frontend and
Nav: Concurrent -> Manager - > Administer

Then we see..

Name CODE Actual Target Running Pending Status
----------------------------------------------------------------------------------------------------------
Internal Manager COLERPDEV 1 1 0
Conflict Resolution COLERPDEV 1 1 4
Manager
CRP Inquiry Manager COLERPDEV 3 3
Scheduler/Prereleaser COLERPDEV 1 1
Manager
Service Manager: COLERPDEV 1 1 Deactivated
COLERPDEV

Returning a Table from a PL/SQL function

2007-11-21T04:56:00.000-08:00

1. Create an object type for the record

create or replace type emp_rec_t as object
(
employee_id number,
first_name varchar2(25),
salary number,
manager_id number,
department_id number
);

2. create a SQL table type object
create or replace type emp_tab as table of emp_rec_t;

3. Define a function in the following way.
create or replace
function demo_proc( p_start_row in number,
p_end_row in number )
return emp_tab
as
l_data emp_tab := emp_tab();
l_cnt number default 0;
begin
for x in ( select * from employee order by salary desc )
loop
l_cnt := l_cnt + 1;
if ( l_cnt >= p_start_row )
then
l_data.extend;
l_data(l_data.count) :=
emp_rec_t( x.employee_id,
x.first_name,
x.salary,
x.manager_id,
x.department_id);
end if;
exit when l_cnt = p_end_row;
end loop;

return l_data;
end;

4. you can query the data returned from the function as follows:
select * from the( select cast(demo_proc(2,6) as emp_tab ) from dual ) a;

Reference: http://www.khwaja.info

PLSQL Questions

2007-11-14T04:44:00.000-08:00

PLSQL Questions

Refferences:
http://hubpages.com/hub/Some_useful_concepts_of_Oracle_PLSQL-good_for_Interviews_too

1. What is a cursor ? How to control how many cursors are open ?
Ans: Name or handle to a private SQL area where Oracle parses and fetches query
results. Set OPEN_CURSORS parameter in initialization parameters.

2. What is the difference between anonymous blocks and stored procedures ?
Ans :- Anonymous block is compiled only when it is called.
- Stored procedure is compiled and stored in Database with the dependency
information as well.
- Former is PL/SQL Code directly called from an Application. Latter is stored
in the database.
- Former has declare Statement and latter doesn’t.

3. What are the advantages of procedures
Ans : - Loaded once and used many times.
- Performance better coz all the SQL statements are sent in one go from an
Application to the Database.
- Security ( no object privileges are given directly)
- Invokers right possible
- Data Integrity and Productivity.
4. How do you handle exceptions for bulk operations
Use FORALL instead of FOR, and use BULK COLLECT to avoid looping many times
- Tune SQL statements to avoid CPU overhead
- Use NOCOPY for OUT and IN OUT if the original value need not be retained. Overhead of keeping a copy of OUT is avoided.
- Reorder conditional tests to put least expensive ones first
- Minimize datatype conversions => Assign data to exact same type variables
- Use PLS_INTEGER for computation intensive code. NUMBER, INTEGER maintain precision and scale but not optimized for performance as additional checks are made to maintain precision and scale.
- Do not use subtypes like POSITIVE, NATURAL, INTEGER as they have additional checks
- Use BINARY_FLOAT, BINARY_DOUBLE
- EXECUTE IMMEDIATE is faster than DBMS_SQL

5. What is MERGE ?
Ans : Combination of INSERT and UPDATE

6. Tell some new features in PL/SQL in 10g
Ans :
- Regular expression functions REGEXP_LIKE , REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR
- Compile time warnings
- Conditional compilation
- Improvement to native compilation
- BINARY_INTEGER made similar to PLS_INTEGER
- INDICES OF , VALUES OF in FORALL lets you work on non-consecutive indices
- Quoting mechanism . Instead of quoting single quotes twice everytime, give your own delimiter to go on using single quotes.
Ex: q'!I'm a string, you're a string.!'
- Flashback Query functions. SCN_TO_TIMESTAMP, TIMESTAMP_TO_SCN
- Implicit conversion between CLOB and NCLOB
- Improved Overloading
- New datatypes BINARY_FLOAT, BINARY_DOUBLE
- Global optimization enabled
- PLS_INTEGER range increased to 32bit
- DYNAMIC WRAP using DBMS_DDL

7. What is a Pseudocolumns ?
Ans : They are not actual columns. They are like Functions without arguments.
- They typically give a different value for each row.
- Examples: ROWNUM, NEXTVAL, ROWID, VERSION_STARTTIME

8. Why use Truncate over Delete while deleting all rows ?
Ans : - Truncate is efficient. Triggers are not fired.
- It de allocates space (Unless REUSE STORAGE is given).

9. What is an inline view ?
Ans : It's not a schema object
- It's a subquery in the FROM clause with an alias that can be used as a view within the SQL statement.

10. What are Nested and Correlated subqueries ?
Ans : The subquery used in WHERE clause is a nested subquery.
- If this subquery refers to any column in the parent statement, it becomes a correlated subquery.

11. Name a few implicit cursor attributes ?
Ans : %FOUND, %ROWCOUNT, %NOTFOUND, %ISOPEN, %BULK_ROWCOUNT, %BULK_EXCEPTIONS

12. What is the difference between %TYPE and %ROWTYPE ?
Ans : Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database. If the datatype or precision of a column changes, the program automically picks up the new definition from the database without having to make any code changes.
The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

13. What is the result when compared NULL with a NULL ?
Ans : NULL is neither equal to NULL, nor it is not equal to NULL. Any comparison to NULL is evaluated to NULL. Look at this code example to convince yourself.
14. What is a Mutating and Constraint error ?
Ans : Mutating" means "changing". A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered "mutating" and raises an error since Oracle should not return data that has not yet reached its final state.
Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.
There are several restrictions in Oracle regarding triggers:
o A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger).
o A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.
15. Can one pass an Object/table as a Argument to a remote Procedure ?
Ans : The only way to reference an object type between databases is via a database link. Note that it is not enough to just use "similar" type definitions.

16. How do you create a hierarchical query ?
Ans : using connect by Prior Clause

17. What do you need before implementing a member function ?
Ans : Create a type

18. How do you run Native Dynamic SQL ?
Ans : use “Execute immediate”

19. What is an autonomous transaction ?
Ans : Identified by pragma autonomous. A child transaction separate from the parent that MUST be committed or rolled back.

20. Implicit Cursor Attributes

Name a few implicit cursor attributes.

%FOUND, %ROWCOUNT, %NOTFOUND, %ISOPEN, %BULK_ROWCOUNT, %BULK_EXCEPTIONS

21. What are Cursor Variables ?

- Also called REF CURSORS.

- They are not tied to a single SQL. They point to any SQL area dynamically.

- Advantage is : You can declare variables at Client side and open them Server side. You can thus centralize data retrieval.

Why use Cursor Variables?

- You can pass cursor RESULT SETS between PL/SQL stored programs and clients.

Oracle Apps HRMS Interview Questions

2007-09-07T00:45:00.000-07:00

1. Important Tables in HRMS

Per_all_people_F,
per_person_types,
per_person_type_usages,
per_addresses,
per_contact_Relationships,
per_periods_of_service
Per_all_assignments_f,
per_pay_groups,
per_jobs,
per_job_Definitions,
per_grades,
per_grade_definitions,
hr_all_positions,
hr_all_position_definitions,
hr_all_locations,
pay_all_payrolls_F
pay_element_entry_values_F,
pay_element_entries_F,
pay_elements_links_F,
pay_element_types_F

2. Key Flexfields (KFFs) in HRMS

Job KFF,
Grade KFF,
People Group KFF,
Position KFF,
Cost Allocation KFF,
Comptence KFF

3.What are Date Track Tables?

every update in the Table, we will save the change in the form of a Record to provide the facility to find the information at any point of time.

These tables are post fixed by _F

4. What are secured Views?

The Views which do not have the _all to be said as secured views.

Per_all_people_F
per_people_f
per_all_assignemtns_f
per_assignments_f
pay_all_payrolls_F
per_payrolls_f

5. The differece between both secured views and non secured views is :

1.Secured views display information only for the current period

2.Unsecured views is used to get the information from the entire rows

6. APIs in HRMS

API are used in HR to insert the data into the Base tables. As its very secured system, the user does nothave the facility to copy the data directly into the Base tables.When we write the inbound interfaces / use WebAdI, the systems will use the APIs to store the data into system.The API are published by oracle with number of parameters.The different types of parameters are IN / INOUT / OUT.Of these parameters few are mandatory, with out which the process wont complete.Generally when we use API we give data for : Object Version Number, Effective Date, P_Validate

HR_EMOYEE_api ex: hr_employee_api.create_employee
hr_PERSON_api
hr_organization_api Ex: hr_organization_api.create_organization
hr_applicant_apI
hr_assignment_api

7. What are the reports which you have done in HR?

HR PAYROLL COSTING :

This report is used to display the information about the employees, the assignements which were given to the employees along with the payroll details including the Hours Paid, Salary, NI, Pension, Car Allowances and Other Allowances.

Creating customer using APIs

2007-08-15T04:10:00.000-07:00

You can create customer using API's .. examples are given in this link API's
API s used in TCI architecture

Image FIle Attachment in Oracle APps 11i

2007-07-18T04:50:00.000-07:00

Reference: http://www.khwaja.info

Technical Details of Image File Attachment in Oracle Applications 11i.

Attaching an image file in Oracle Apps say, you attach an image of an Item.
For this you will click on the attachments icon(Paperclip Icon) of the toolbar in Apps Window and this will take you to Attachment Window.
Here You can specify Category, Description,Datatype.
If you select "File" as datatype, you will be taken to a webpage asking for uploading the image.
Once you upload the image successfully, You can view the image using the "Open Document" button on the "Attachment Window".

This is how you will be uploading the image.

Q. Now where did the image go and how can you access it??
A. When you attach a file say, "A380.jpg", you can find the document_id of that image using
query:

SELECT document_id
FROM fnd_documents_tl
WHERE file_name = 'A380.jpg'
output: document_id = 11807

Use this document_id to get the media_id of the file. Media_id is the actual file_id.
SELECT media_id
FROM fnd_documents_tl
WHERE document_id = 11807
output: media_id = 318949

Use the media_id in below query to get the file details.
SELECT file_id, file_name,file_content_type,file_data
FROM fnd_lobs
WHERE file_id = 318949

In this table, "FND_LOBS" you can find the BLOB column, "FILE_DATA" which stores the actual file.

If you want to show this file on a webpage, ie. if the user wants to see the image of the item
from a custom form for items,he can click a button on the form which will take him to a webpage
where he will be able to see the image.

For this, you can use the below query to get the url for the image:

SELECT fnd_gfm.construct_download_URL(fnd_web_config.gfm_agent,318929)
FROM dual

You can use this url say, "l_url" in fnd_utilities.open_url to open the webpage containing the image.
fnd_utilities.open_url (l_url)

If you want to show the image on the form itself then we need to add the file_data column of fnd_lobs table to the datablock in the form. and use it for displaying in the Form.

Oracle Apps using External Tables

2007-07-15T22:49:00.000-07:00

Attached document explains how to use Oracle's External Tables for loading data from CSV or Flat files.
External Tables are used in these situations
1). When we need to load the data from CSV or Flat file(s) using a PL/SQL program.
2). When we required to load data from multiple CSV or Flat file(s) to Oracle database in a single instance
Step 1:---------create directory to refer where the CSV/Flat files: ==================================================
CREATE OR REPLACE DIRECTORY extrn_data AS '/usr/temp';
Step 2:--------Place the CSV or Flat file(s) in '/usr/temp' folder
Step 3:
Execute the below Command to load the data from given CSV file(s)
Comma delimited Example***********************
-- Loading data from Multiple CSV file to External Table
drop table emp_extrn;CREATE TABLE emp_extrn ( empcode NUMBER(4), empname VARCHAR2(25), deptname VARCHAR2(25), hiredate date ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY extrn_data ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL ) LOCATION ('emp_extrn1.csv','emp_extrn2.csv') ) REJECT LIMIT UNLIMITED;
Step 4:
--Query Data / load the data from External Table to Base Table
select * from emp_extrnorinsert into base_tableselect * from emp_extrn

Fixed field length Example***************************
--Replace the below command in Step3
drop table emp_extrn;create table emp_extrn (id number,tot_pay number,tot_email number, newfld number)organization external(type oracle_loaderdefault directory extrn_dataaccess parameters(records delimited by newlinebadfile 'custview.bad'discardfile 'custview.dis'logfile 'custview.log'fields(ID position (1:10) integer external,tot_pay position (12:21) integer external,tot_email position (23:32) integer external, newfld position (23:23) integer external))location ('custview.dat'))reject limit unlimited;

How To check whether a table is External Table**********************************************
select * from USER_EXTERNAL_TABLES

For Additional Information**************************
http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96652/ch11.htm

Loading bitmap images (JPG, GIF, BMP) and PDF, XLS into Oracle database

2007-06-15T22:59:00.000-07:00

Follow the steps to Upload JPG, GIF, PDF and XLS files into oracle database

Step 1
---------
Create or replace directory my_files as '/usr/temp';

Create table demo(sno number, filedata blob);
copy any file (jpg,gif,pdf,xls....) into /usr/temp
Execute the below code. Change the file name according to your requirement
Step 2
--------
declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values ( 3, empty_blob() )
returning filedata into l_blob;
l_bfile := bfilename( 'MY_FILES', 'water.jpg' );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_blob, l_bfile,
dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
commit;
end;

Step-3
----------
Check the file length to ensure whether file loaded successfully or not

select sno,dbms_lob.getlength(filedata) from demo
Reference: http://www.sql.ru/forum/actualthread.aspx?tid=202867

Troubleshooting Yellow Bar

2007-06-14T04:15:00.000-07:00

I have got many queries from the Oracle apps developers regarding "Yellow Bar" while opening Oracle apps forms.

Issues like:

1. Copying the content from Apps to Any other application ex: Copy some text from Apps forms to notepad or excel and vice-versa.

Troubleshooting:
1. Delete cache : Delete all the files from Temporary Internet Files and temp table.
2. Uninstall the the Jinitiator from the system (Control Panel)
3. Open the Oracle application
Login to apps
Choose any responsibility
Appsbase.htm window will be opened(which initiates Applet in it).
It instals the latest version available on server.
And now you can see "Yellow Bar" is disabled and you can perform Copy and Paste

If you have problem while installing jinitiator.exe with the following errors.

============================================================
UnZipSFX 5.40 of 28 November 1998, by Info-ZIP (Zip-Bugs@lists.wku.edu).
extracting: adjbuild/appltop.cerextracting: adjbuild/jinit11816.
exeextracting: adjbuild/oajsetup.
exeextracting: autosetup.txt
extracting: oajsetup.bat
extracting: setup.bat
oajsetup: Jinitiator version number not valid, should be 1.1.x.x
Oracle Jinitiator Setup Utility
Copyright (c) Oracle Corp., 2000

Usage:
oajsetup [] []
The system cannot find the file specified.
The batch file cannot be found.
============================================================

It may happen because some times the version at server may be corrupted or so.

Then downlod the jinitiator.exe and version should be same as the one you have on server.

How to check the version of jinitiator on your sever?
When you login to apps and choose responsibility, you can see a appsbase.htm which starts applet, there you right click and view source there you can see the version of jinitiator.

Download the jinitiator from the above below path

http://www.oracle.com/technology/software/products/developer/htdocs/jinit.htm

Clear Cache, delete all the temporaty files from PC
Install jinitiator in your PC and check the Oracle apps again.

It should work now.

If you still have problem, there is another tip, copy the " identitydb.obj " from any of your network users who are using the same Oracle application and paste in your "Oracle" folder where jinitiator is installed the typical path is like c:\program files\oracle\identitydb.obj

It should work now, if you still have issues, talk to your DBA to look into this.

Oracle apps Inbound and Outbound Interfaces

2007-05-11T17:43:00.000-07:00

1. Inbound Interface:

Datafile (Legacy data) -> Temporary tables -> Validate data -> Load into Interface Tables -> Run Concurrent Program to load the data into Apps Base tables

Datafile (Legacy data) -> Temporary tables -> Validate data -> Run required API (application program interface) to load the data into Apps Base tables.

Datafile (Legacy data) -> Prepare delimited data file -> Create Control file with instructions to load the data into Apps Base tables.

Run SQLLDR from unix or DOS

sqlldr user/password@instance control=control_file.ctl data=data_file.dat bad=bad_file.dat

2. Outbound Interface:

Gather the data from Oracle apps using SQL->Use UTL_FILE to insert the data into files and store in appropriate server path.

Oracle Apps Inventory Functional Interview Questions

2007-05-11T17:34:00.000-07:00

1.What is item status? Name the related table.

INVENTORY_ITEM_STATUS_CODE is the Colum in table MTL_ITEM_STATUS will tell you the different status for item while you defining the item in item master form (INVIDITM). You can have different status as shown in below, also u can define your own custom status in status code form in item setups (INVSDOIS)like “easyapps”

Active
Inactive
Engineer
Obsolete
Phase-Out
Prototype
OPM
Lease
Production
Concept
Design
DEM_Status
Pending

2.Define steps of assigning items to an organization.

Create an item in Item master from and select the template to assign the corresponding item attributes from tools menu. Now save the item. After saving the item select the organizational assignment option from tools menu for that item and organizational assignment item form popups. Now u can select the organizations from the list by ticking in check box.save the work.

3.In which table subinventory related information for item is stored?

MTL_ITEM_SUB_INVENTORIES

4.What are the prerequisite set ups for interorganization transaction?

Prerequisites
Define an inventory item that is common to both organizations. See:Defining Items and Assigning Items to Organizations.Define at least two organizations, one of which is valid to receive material from the other. See: Creating an Organization, Oracle Human Resource Management Systems User’s Guide and Defining Organization Parameters.Set up inter–organization relationships and their corresponding accounts. See: Defining Inter–Organization Shipping Networks. For direct transfers, if the item to transfer is under serial number control, the item must have the same unit of measure in each organization. See: Defining Items.

5.Where to define interorganization transaction charges and what are the different options?

Depending on the inter–organization transfer charge that applies between the organizations, a percentage of the transaction value or a discrete amount that Oracle Inventory uses to compute transfer charges.

6.What are stock locators and how to define them?

Stock locators are an optional entity that may be used to represent physical locations within a subinventory. You may choose to use stock locators for selected subinventories or selected items within selected subinventories. If locators are used, subinventory and locator track on-hand balances. Therefore, if locators are defined to represent a shelf within a stockroom, on-hand balances on the system would show the item and quantity down to the physical location within the facility.U can derfine them in stocklocator from (INVSAFDH) in inventory setups. MTL_ITEM_LOCATIONS is the main base table for this locators.

7.Name the tables where the stock locator information is stored. At least two.

Refer above Q.One more table is MTL_ITEM_LOC_DEFAULTS

8.What is unit of measure classes? Give atleast two examples. Name the underlying table.

9.What is the prerequisite for difining unit of measure?

10.In which tables the transaction details stored? Atleast two.

11.What are centralized and decentralized level of control of item attributes?

12.Name all status attributes.

13.Differentiate between intra & inter uom class conversion with examples.

14.Is uom maintained at master level or organization level or both?

15.Mention the interface tables for item open interface and open transaction interface and also their respective production tables.

16.Name atleast five reports in oracle inventory related to transaction ?

17.What are the prerequsites for costing transactions?

18.Which is the table for storing organization information?

19.How to get the on hand quantity of an item across all organizations? Name the underlying tables.

20.What is the purpose of standard cost update?

21.Mention the navigational steps for abc analysis.

Implementing Zoom in Oracle Apps 11i

2007-05-11T04:55:00.000-07:00

In the custom.pll, modify the below given function and procedure:

function zoom_available return boolean is
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (form_name = 'POXPOEPO' and block_name = 'PO_LINES')
return TRUE;
else
return FALSE;
end if;
end zoom_available;

procedure event(event_name varchar2) is
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
param_to_pass1 VARCHAR2(10) := ' ';
param_to_pass2 VARCHAR2(10) := ' ';
Begin
if (event_name = 'ZOOM') then
if ((form_name = 'POXPOEPO' and block_name = 'PO_LINES')) then
FND_PROFILE.GET('MFG_ORGANIZATION_ID',param_to_pass1);
param_to_pass2 := name_in('PO_LINES.item_id');
fnd_function.execute
(
FUNCTION_NAME=>'XXONHAND',
OPEN_FLAG=>'Y',
SESSION_FLAG=>'Y',
OTHER_PARAMS=>'ORGANIZATION_ID="'||param_to_pass1||'"INVENTORY_ITEM_ID="'||param_to_pass2||'"'
);
end if;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
END;

Attach the form function 'XXONHAND' to the menu which is attached to the responsibility to which
we want the zoom function to be available.

Once this is done. we can see the zoom functionality on the form_name = 'POXPOEPO' and block_name = 'PO_LINES'

In the custom form XXONHAND, create a datablock using a view named, MTL_ONHAND_SUB_V
In the form, add two parameters: INVENTORY_ITEM_ID and ORGANIZATION_ID
and in the where clause of the datablock put the below string:
WHERE (:parameter.INVENTORY_ITEM_ID = MTL_ONHAND_SUB_V.INVENTORY_ITEM_ID)

By this we will be able to see the onhand availability of the item on the XXONHAND form.

Reference: http://www.khwaja.info

Oracle Project Accounting Support/Developers

2007-03-11T06:26:00.000-07:00

Reference: http://dag.interfree.it/oracle/P12892.html

Oracle Project Accounting Support/Developers

Oracle Payables Support/Developers
PA and AP Functional analysts
End-users and ...

Key Items for each integration point

Data flow during each process and tables involved
Transaction flow between PA and AP
Concurrent Manager Processes
Various problems encountered and error messages
Troubleshooting/Bug Info
Tips to overcome typical error messages
New features in 11i and set-up parameters
Entering Expense Reports in 11i
Entered in Oracle Projects through pre-approved batches window
Entered in Oracle Self -Service Expenses (Web Expenses/Internet Expenses) and imported into Oracle Payables and then interfaced to Oracle Projects.
Entered directly in Oracle Payables using the Invoice and the Distributions window in AP and then interfaced into Oracle Projects.
Imported into Oracle Projects from an external source and then processed through the Transaction Import Process.

No longer supported in 11i are Oracle Project Time and Expense and Oracle Personal Time and Expense (PTE)

Setting Up in Payables and Oracle Projects
• Select the Expense Reimbursement Address as Office/Home (AP, Setup->Financials->HR)
• Automatically Create Employee as Supplier(AP, Setup->Options->Payables->Expense Report)
• Accept or override the employee address (AP, Setup->Options->Payment->Allow Address Change)
• Define/Determine the Expense Report Cost Account (Auto accounting rules)
• Define/Determine the Expense Report Liability Account (Auto accounting rules)
• PA: Summarize Expense Report lines (SYSADMIN: Profile>System>Value–Application level)
• PA: Allow Override of PA Distributions in AP/PO (Site, Application, Responsibility and User)
• PA: Auto approve Expense Reports (Application level)
• PA: Default Expenditure Organization in AP/PO (Site Level)
• Invoice Batch Source = ”PA Invoices” (PA Setup/System/Implementation Option /Billing )

Submitting the streamline Processes
• Streamline processes submit two or more processes in one step. There are several streamline options available:
• DXES, DXEU, XES, XEU etc. Some processes use a lot of system resources (DXES, DXEU etc.).
• In Collective, We run the PRC: Distribute Expense Report Costs separately and then use XES – Interface Expense Report Costs to AP (Summarized) streamline option to submit PRC: Submit Interface Streamline process to complete the rest of the processes.
We may use any combination of streamline processes or perform individual functions to invoke these interface processes. But they must be invoked in the correct order for them to run successfully. It is always advisable to use Streamline Process instead of individual processes.

PA : AP Integration Processes Time to have some fun !!!

We have a web-based Custom Expense application (fully integrated with Oracle Projects) using which all the Collective employees fill up their expense reports online.

A Concurrent Program (SQL Script) is set up in Oracle Projects to execute weekly to transfer the ‘APPROVED’ expense reports from the web interface into the Project Accounting interface table (Pa_transaction_interface_all). The following info is transferred at the web interface during the expense download: Employee_number, expenditure_item_date, project_number, task_number, expenditure_type, quantity, transaction_status_code and expenditure_ending_date.

Ooops … some more to say

Once the expense information is transferred into the Oracle Projects Interface, the following concurrent processes are invoked in order to complete the transfer into Oracle Payables in the same sequential order as shown below (either using Streamline process or invoking them individually

PRC: Transaction Import
PRC: Distribute Expense Report Costs
PRC: Interface Expense Reports to Payables
AP: Payables Invoice Import
PRC: Tieback Expense Reports from Payables.
Okay, What do we do now ???

We are now going to look at the functionality of the
various concurrent processes and peek at the
problems faced by us (Collective) and …hold your
breath…tips to overcome those “Driving me crazy”
error messages and exceptions.

PRC: Transaction Import

Transaction import loads transactions as pre-approved expenditure items and expenditure batches are created as having a status of ‘Released’.
Data Transfer occurs from ?
pa_transaction_interface_all
(Project Interface Table)
? ? ?
pa_expenditure_batches_all, pa_expenditures_all, pa_expenditure_items_all
( Project Accounting Tables )

Data Flow from PA interface to PA

Just a little more info ...
Transaction Import process generates two reports:
(1) An exception report listing all rejected transactions
(2) A summary report showing the successfully imported transactions.
(( The exception report will provide rejection reasons for all rejected items. While only the items that are rejected appear on the exception report, Transaction Import process rejects the entire expenditure and updates all the items in that expenditure with the status of rejected (sets Transaction_status_code =’R’ in Pa_transaction_interface_all table). You must correct the rejected transactions and then re-run the process ))
From version 11 onwards, a new form called review transactions has been added in Oracle Projects. (Navigation is: Expenditures -> Transaction Import -> Review Transactions). This form is extremely useful in expediting minor additions to the expenditure batches. It can also be used to correct rejected transactions in the interface table using this form.

11i --> like a breath of fresh air …
Limitations of Transaction Import Process in previous versions
Until 11i Transaction Import process can only be used for importing the Expense reports and can not be used for loading supplier Invoices. We could enter supplier Invoices only from Oracle Payables and then interface the supplier Invoices from Oracle Payables to Oracle Projects using the Interface Supplier Invoices from Payables process provided by Oracle Projects.
But from 11i onwards, Supplier Invoices from Oracle Payables can also be interfaced via the Transaction Import process.
Transaction Import detects only one error per transaction each time you run the import process. If a single transaction has multiple errors, you will need to run Transaction Import more than once to discover all the errors.

11i --> It’s like dancing in the rain ...
Enhancements in Transaction Import Process in 11i
Transaction Import process now supports
(1) Multi-Currency Transactions (2) Supplier Invoices.
We can now import the following columns for transactions, depending on the options we specify for the Transaction Source:
(1) Employee Organization Override (2) User-Specified Billable flag
(3) User-Specified Expenditure Reference
(4) User-Specified Original Transaction Reference (for adjusting transactions)
Pre- and Post-Processing Extensions
Expenditure Item Descriptive Flexfield validations
Validation of Account Codes Provided for Accounted Transactions
Adjusting or Reversing Imported Transactions in Oracle Projects (depending on Transaction Source option)
Allow Adjustments option for accounted transactions

As promised, here are those “Driving me crazy” exceptions
Rejection Code : PA_EX_PROJECT_CLOSED
Description: This happens when the expenditure is assigned to a project which is no longer active.
Solution: Assign the expenses to a different Project .

Rejection Code : PA_EX_PROJECT_DATE
Description: This happens when the expenditure Item date is not within the active dates of the project.
Solution: Assign the expenses to a different Project which was active during the item date or
Change the project start date to an earlier date.

Rejection Code: EI_DATE_AFTER_END_DATE
Description: This happens when the expenditure item date is after expenditure ending dates.
Solution: Change the expenditure_item_date or the expenditure_ending_date.

Some more goodies ...
Rejection Code: INVALID_END_DATE
Description: The value for the expenditure ending date is not a valid week ending date.
Solution: Change the expenditure_ending_date to a valid week-ending date.

Rejection Code: INVALID_PROJECT
Description: No Project exists with project number specified.
Solution: Check to make sure if the project exists.

Rejection Code: INVALID_EMPLOYEE
Description: No employee exists with the employee number specified.
Solution: Check to make sure if the employee info exists in the projects.

Rejection Code: PA_EXP_TYPE_INACTIVE
Description: The expenditure item falls outside the effective dates of the expenditure type.
Solution: Change the expenditure item date, expenditure type, or expenditure types.

Is it christmas already ...
Rejection Code: PA_EXP_TASK_TC
Description: The transaction violates an expenditure control at the task level. If the transaction
control has overlapping assignments within this expenditure_item_date/expenditure_ending_date
the transaction gets rejected with the above code.
Solution: Make sure there are no overlaps in the pa_transaction_controls table for this task_id and
project_id.

Rejection Code: PA_EXP_TASK_EFF
Description: Expenditure item date is not within the active dates of the task.
Solution: Change the expense report task number to reflect the active task number.

Rejection Code: NO_ASSIGNMENT
Description: The employee doesn’t have a valid assigment.
Solution: This was one of the issues with our Import. Even though the assignment information existed in
all the HR tables, the assignment wouldn’t show up in the Applications. We kept getting the rejection code
‘NO ASSIGNMENT even after updating the member assignment in the forms. After doing some research
on this issue, we figured out that ………………..
This is like “ Buy 1 get 2 free “
The min(effective_start_date) for each employee in --> per_all_assignments_f (per_assigments_f inv10.7)
Should match ----->
effective_start_date in per_all_people_f (per_people_f in V 10.7) table and date_start column in
per_periods_of_service table.

Rejection Code: NO_MATCHING_ITEM
Description: No eligible expenditure item exists in PA for this adjustment.
Solution: If the transaction is an adjustment with a negative quantity, and the unmatched
negative flag is not set to Yes, an original, approved, un reversed expenditure item matching
the transaction’s employee/organization, item date, expenditure type, project, task, reversing
quantity, reversing cost and non-labor resource and non-labor organization must exist. Also,
the matching expenditure item must have been originally loaded from the same transaction
source.
Note: If the employee number is specified, transaction import ignores any value for the organization and derives the organization value based on the employee’s assignment. Transactions with any other expenditure type classes (other than Expense Reports) do not require an employee number.

PRC : Distribute Expense Costs …Oh Yeah
Costing = Calculate the Expenditure and Assign to Project
PRC: Distribute Expense Report Pre-requisite for the generation of revenue and Invoices
for expense report expenditure items.

Auto-accounting - Critical piece of project costing
When this distribute process runs, it invokes the
Auto accounting function ‘Expense Report Cost Account’
and populates the respective Debit CCID (dr_code_combination_id column) in the Pa_cost_distribution_lines_all table (cdl).

How PA Date is determined?
? PA Date is determined based on the first open or future period that is on or after the expenditure_item_date.

If what I said sounded like Greek ...
Distribution of cost data in PA
Don’t tell this to your boss ...
Common Exceptions Encountered

1. Rejection Code: KEY_FLEX_FAIL-Invalid Accounting Flexfield and
AA_NULL_CCID- Auto Accounting Error: null ccid
Description: The GL account returned by Auto Accounting does not pass validation rules defined for the gl account and the code combination id derived by AutoAccounting is invalid respectively.
Solution: Check to see if the project code, task code and Organization_id are correct. Find out which gl code the above combination will hit. Make sure you haven’t excluded this particular GL code in the Cross-validation rules and make sure dynamic inserts are turned on.

2. Rejection Code: AA_FAIL - Incomplete Autoaccounting Rule
Description: A GL account could not be determined for the cost distribution line, due to incomplete Auto Accounting setup
Solution: Check to see if the Project_code, task_code, incurred_by_organization_id are correct. Everything should be in sync in order to avoid this.

3. Rejection Code: NO_PA_DATE
Description: Project Accounting Period is not open
Solution: Make sure your PA Period is open.
PRC: Interface Expense Reports to Payables
PATTER process collects eligible Cost distributed Expense reports and sends them to Payables Interface tables
Invokes the auto accounting function ‘Expense Report Liability Account’ ( populates cr_code_combination_id in pa_cost_distribution_lines_all and accts_pay_code_combination_id in ap_expense_report_headers_all)
? PA Auto accounting rule takes precedence over the employee’s default expense account when determining the invoice liability account (and also it does not invoke Account Generator/ Flexbuilder).
How is GL Date determined?
In Oracle Projects, the GL Date for costs is the end date of the earliest open or future GL period that is on or after the latest PA Date of the cost distribution lines included in an expense report.
All cost distribution lines for an expense report are sent together to Payables and use the same GL date which becomes the GL date of the invoice in payables.
? Note:
If expense reports from any source fail to post to Payables, we may need to redistribute costs (using the PRC: Distribute Expense Report Costs process)
Typically, Auto-Accounting rules require occasional updates. Transactions may fail during distribution and interfacing if the Auto-accounting rules are incomplete.
Interfacing Expense data from PA to AP

Bugs and Exceptions?! Here you go
1. Problem: The process does not pick up expenditure items with transfer_status_code=’P’ (pending). No error messages appear.
Solution: Set Automatic Release to ‘YES’ for PRC: Distribute Expense Report Costs. (Note: 1068250.6, 1060896.6)

2. Problem: After running the PRC: Submit Interface Streamline Processes with Streamline Option: DXEU, the process completes with a status of “Error” and Rejection Reason – No Open Project Accounting Period or Could not create a valid GL Date.
Solution: Verify all the periods are open in PA, AP and GL. (Note: 1080222.6)

3. Problem: After running the PRC: Submit Interface Streamline Processes either individually or as a streamline, you get an error ‘Combination Missing’

Solution?!
Check if Auto accounting rules are setup
correctly

Open a TAR with Oracle

and then research the expense data based on the following:=)
Remember, the following information is provided for research purposes only.
Do not modify the data using SQL PLUS. Oracle will not support it.
Investigation Step No. I
i) select * from pa_cost_distribution_lines_all
where transfer_status_code like ‘X’
and transfer_rejection_reason like ‘Missing data on record to be transferred%’
and cr_code_combination_id is null;

or

select * from pa_cost_distribution_lines_all
where expenditure_item_id in (select expenditure_item_id
from pa_expenditure_items_all
where cost_distributed_flag like ‘S’)

*** If zero rows are returned, then do the following query. Otherwise go to ii)

select * from pa_cost_distribution_lines_all
where transfer_status_code like ‘T’;

If no rows are retrieved here also, then this fix may not help.
Investigation Step No. II

If a few rows are retrieved, then you can assume that PRC: Interface Expense Reports to Payables was successful and there were some problems with the next two processes.

ii) select * from ap_expense_report_headers_all
where vouchno=0
and accts_pay_code_combination_id is null;

*** get the report_header_id for the retrieved rows
If no rows are retrieved, then this error may be due to some other reason..
iii) select * from ap_expense_report_lines_all
where report_header_id=&report_header_id;

If you have retrieved at least one row for i) and ii) and none for iii) then following may be the fix for your problem.

Here you go…..

Solution :

select count(*) from ap_expense_report_headers_all
where vouchno=0
and accts_pay_code_combination_id is null;

If you get the count in multiples of 100, then this is due to bug #1551379
(Ref# TAR-13305123.6)
You have to delete one expense report and process the rest of the expense reports first and then process one expense report separately. This will resolve the issue.
This issue is finally resolved in 1701594 for versions 10.7, 11.0 and 11.5.

AP: Payables Invoice Import
creates invoices and invoice distribution lines (ap_invoices_all and ap_invoice_distributions_all) from Payables Interface tables (ap_expense_report_headers_all and ap_expense_report_lines_all).

? If you do not enable Automatically create Employee as Supplier, you must manually enter the employee as a supplier before submitting Payables Invoice Import.

Invoice Import Process at a glance

Typical Exceptions During Invoice Import
1. Rejection Code: Error Code: Addr Line [1-3] Too Large:
Description: Invoice Import attempted to create a supplier from an employee and the address line for the employee home address exceeds 35 characters. This happens all the time because, In PA module, (per_addresses table) the data type for address_line[1-3] have a data type of varchar2(60) and in AP (po_vendor_sites table) the data type for address_line[1-3] have a data type of varchar2(35). So anytime, the address_line[1-3] exceeds 35 characters, we will get this error.
Solution: Login to PA module, go to Setup, HR, Employees, Query the
employee for whom you have this error, then click on More, Addresses and
change the address line [1-3] and reduce the character length to 35 and do the
import. It should work fine.
Some more of those...
2. Rejection Code: Error Code: Create Duplicate Supplier:
Description: This may be due to any of the following reasons:
i) When you try to create an expense report for an employee name that is not already a supplier and a supplier name already exists for the same employee .
ii) Login to AP module, go to Suppliers screen and query the employee name and under Classification see if you have checked One Time Check box.
Solution: If i) is true, you know what to do.
If ii) is true then Remove the One Time Checkbox.

Here again...
3. Rejection Code: Error Code: Create EFT Site
Description: The Financials Option for payment method is Electronic. The
employee exists as a supplier in Oracle Payables, but is missing bank info in
Supplier site.
Solution: Enter the necessary bank information in the supplier site.
4. Rejection Code: Error code: Create EDI Site
Description: This is a Setup Problem. When we setup Supplier sites in Payables, the site name should be either ‘OFFICE’ or ‘HOME’. If not, then we will get this exception.
Solution: Create a new site called ‘HOME’ or ‘OFFICE’ for the supplier.

PRC: Tieback Expense Reports from Payables

Identifies Expense reports rejected by Payables Invoice Import

Reconciles Expense reports in Oracle Projects and the related invoices in payables

updates the purgeable_flag from ‘No’ to ‘Yes’ for each expense report in the ap_expense_report_headers_all table.

Adjusting Project Related Expense Reports
You can adjust an expense report in Oracle Projects at any time, but you cannot interface adjustments to Payables until an invoice exists in Payables and you have run the tieback process.

In order to process expense report adjustments, the following processes must be invoked:
PA: Distribute Supplier Invoice Adjustment Costs
PA: Interface Supplier Invoice Adjustment Costs to Payables

There is no need to run Payables Invoice Import for adjustments to expense reports already interfaced or tied back from Payables.
Epilogue

And so with a closing thought that although tremendous advances have and are being made in this subject area to handle the processes and tackle all arising problems, this paper strives to be a small step in that direction and hopes to be a part of that tide in bringing to shore a trove of vital information.
Acknowledgements

Teresa Myers -- Collective Technologies,
Ram Ganapathy – Dell Computer Corporation

Purchasing Interview Questions

2007-02-26T21:21:00.000-08:00

Oracle apps (Oracle 11i) purchasing (PO) functional interview questions. These are asked in different interviews HCL, Wipro, Accenture, Oracle corp, Zensar. Collected all these questions and answers. Few of these dont have answers we try to update very soon, I would appriciate if you can send the answers for those don't have answers to oraclerespository@gmail.com

1. If Purchase Invoice amount sent by the supplier is more than the PO amount and Invoice has to be passed by invoice amount only. How this can be achieved.

Ans: Manually Release the hold by going into the invoice hold option. Thus how we do it in Purchasing module of oracle applications.

2. How you will hide the button Site on the Supplier master form.

Ans: A restricted list of functions a user can perform. For example, two responsibilities may have access to the same window, but one responsibility’s window may have additional function buttons that the other responsibility’s window does not have.

3. When can you make payment for the supplier invoice weather at the time of receipt of material or after receiving
Ans: Invoice only.

4. Payment due days if 30 days means will it be from invoice date or material receiving date.

Ans: Due days calculation is based on what you set up in the PAYMENT tab window of Supplier Master form.

It has Four options:
1. Goods Received
2. Invoice
3. Invoice received
4. System

5. Where do you define matching option for purchasing.

Ans: This is also defined in the supplier master form under tab ‘Control’ window.Two options are given as Purchase Order or Receipts.
In receiving tab define whether it is 2 way, 3 way, 4 way match.

6. What is security type and Cross-validation rules.

Ans: There are two ways security can be defined. One at individual segment level and One for the entire code combination.
Security type with hierarchical security / non-hierarchical / no security is defined in the value set going to be attached with the individual segment. Here you exclude the segment value which user will not see at time of transaction capturing. Where are Security rule is defined including and excluding the entire code combination which will be seen at the time of transaction and if excluded combination is selected then user define message will appear. In both the case security rule is required to be defined.
Security defined for segment is assigned to your application and responsibilities.
Security rule defined for application, structure and flexfield title with inclusion and exclusion.

7. How many types of Purchase Order are there.

Ans: Following are types
i. Standard
ii. Blanket
iii. Contract
iv. Planned

8. What do you mean by planned purchase order.

Ans: A planned purchase order is a long-term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.

9. What do you mean by blanket purchase order.

Ans: You create blanket purchase agreements when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them.

10. Once these two purchase orders are prepared what is the next step.

Ans:You issue scheduled release and blanket release against a planned purchase order to place the actual orders.

11. What is approval hierarchy for Purchase Order.
Ans: Position and Employee. If HR Module is not installed then only employee option is used Set up hierarchy for Purchasing
1. Organization.
2. Financial and Payable.
3. Payment document or supplier site for payment accounting entries.
4. Supplier and Supplier Site.

Payment terms as per supplier site or purchase order if both are there then finally takes from purchase order.

12. Where do you define matching option for purchasing.

Ans: This is also defined in the supplier master form under tab ‘Control’ window.Two options are given as Purchase Order or Receipts.
In receiving tab define whether it is 2 way, 3 way, 4 way match.

13. What happen if price is varying in PO and Invoice how you will resolve it.

Ans: ====================================

14. What posting is done at time of Receipts of material or Purchase Order match directly with Invoice without the receipt consideration.

Ans: ======================================

15. Where you will define Matching options. Is it required to define for all the suppliers or it comes as default. From where it default the setting.

Ans: ===========================================

16. In purchasing Receipts of the inventory is stored at what level in Multi Org setup.

Ans: ===========================================

17. Can you match invoice directly with the purchase order without matching with receipts.

Ans: ===========================================