Mostly asked questions in Account Payables Modules


Hello, Some of the very much interview oriented, one of mostly asked questions in Account Payables Modules. here they are 

Questions: How many types of Invoices in Oracle Account Payables
1)Standard Invoice
2)Debit Memo
3)Credit Memo
4)Withholding tax invoice5)PO Default
6)Prepayment Invoice
7)Expense Reports
8)Recoring Invoices
9)mixed Invoices

 

  
 
 

 

 

 

Prepayment Invoice: Whenever we want make payments to the suppiler in advace that time,we create this prepayment invoice and we will make the payment.

Credit Memo,Debit Memo:Both invoices are got -ve amount,and adjusted against standard invoice.
Credit memo will be created, when ever suppiler giving discount.
Debit Memo will be created,if buyer is going to deduct the amount.
  

 

 
 
 
 

 

 

 

 

 

Question :How many key flexfields are there in Payables?

Answer: 0 (No key flexfields in PO,AP)

 

Question: Name few Account Payables Tables

AP_INVOICES_ALL
AP_INVOICE_DISTRUBUTIONS_ALL
AP_PAYMENTS_ALL
AP_PAYMENT_SCHEDULES_ALL
AP_INVOICE_PAYMENTS_ALLAP_TERMS_ALL
AP_TERM_LINES_ALL
AP_CHECKS_ALL
AP_CHECK_FORMATS
AP_HOLDS_ALL

 

 

 
 
 
 

 

 

 

 

 

Question: What is 2 way , 3 way and 4 way matching?

While creating the purchase order,we will mention the match approval level at shippments
we will have 3 types
1)two way: PO & Invoice quantities must match with in the tolerance before the corresponding invoice can be paid.
2)three way: PO, Receipt and invoice quantities must match with in the tolerance before the corresponding invoice can be paid.3)four way: PO, Receipt, Inspection and invoice quantities must match with in the tolerance before the corresponding invoice can be paid
 
 

 

 
 
 
 

 

 

 

 

 

What is a Hold? Explain the types of Hold

Invoice holds:If invoice is not approve then that invoice will be keeping under hold status.By selecting holds button in invoice form,we can see the hold details.
Select * from ap_hold_all
 
 Which interface tables are used for Invoice Import , give the important columns?
BASE TABLES:
AP_INVOICES_ALL
AP_INVOICE_PAYMENTS_ALL
AP_PAYMENT_SCHEDULES_ALL
AP_INVOICE_DISTRUBUTIONS_ALLAP_INVOICES_INTERFACE COLUMNS:
Invoice_id
Invoice_num
Po_number
vendor_id

vendor_num

vendor_site_id

vendor_site_code

invoice_amount

Ap_invoice_lines_interface columns:
Invoice_id
Invoice_line_id
line_number
line_type_lookup_codeamount
po_header_id
po_number
po_line_id
po_line_number

po_line_location_id

po_shipment_num

po_distrubution_id

po_distrubution_num

Inventory_item_id

 

 

 
 
 
 
 

 

 

 

 

 

Thanks – Shivmohan Purohit

 

 

 

Advertisements

Setting up Invoice Approval Workflow


Typical Business Requirements while setting up Invoice Approval Workflow

Approving AP Invoices using Oracle Approval Management (OAM) is a fast and easy way of maintaining transparency and accountability in Accounts Payables department. However most of the time OAM setup needs to be modified and workflow customized depending upon the business scenario.
One of the most common business requirement is that organizations do not need approval of invoices that are matched to PO Receipts. Anticipating this request, Oracle has already given this as an Attribute under the Transation Type ‘Payables Invoice Approval’. But, apart from this there are several other common requirements which one faces. I have listed down some such requirements. Some of these requirements can be very easily applied using the standard OAM functionalities, while some others require a bit of customization.
Some common requirements include the following:
1)Approver forwarding the invoice to another employee for approval.
2)Approval of an invoice based on criteria like invoice amount, cost centre, account segment, etc.
3)Adding new lines like ‘Voucher Number’, ‘Cost Centre’ details, etc., in the approval notification email.
4)Considering scenarios when the Approver is on leave, so that the invoice moves to another person for approval
after some time period.
5)Approver is able to delegate his/her approving right to some other person while going on leave.
6)Control on the email notifications, which tend to fill the mailboxes of approvers.
7)Mixed and Prepayment type of invoices do not require approval.
8)Invoices should go directly to the final approver and not through any hierarchy.
9)Invoice entry clerk should be able to choose the approver from a LOV (list of value). Sometimes the reverse scenario also exists where the Invoice entry clerk should not have a say in selection of Invoice Approver.
It would be great if readers can also add further to the above list so that we can have a comprehensive list of common requirements that come up while setting up Invoice Approval Workflow.
Thanks – Shivmohan

Form Personalization in Oracle Applications


These days Form personalization is keyword and almost all developer and analyst working in oracle application must be aware and conversant with feature and how-to use this. I recommend to have good amount of practice on this. here i am presenting very brief overview and like to know if it useful need kind of tutorial kind of article. 

Form Personalization in Oracle Applications

The Form Personalization feature allows you to declaratively alter the behavior of Forms-based screens, including changing properties, executing builtins, displaying messages, and adding menu entries.

For each function (a form running in a particular context based on parameters passed to it), you can specify one or more Rules. Each Rule consists of an Event, an optional Condition, the Scope for which it applies, and one or more Actions to perform. An Event is a trigger point within a form, such as startup (WHEN-NEW-FORM-INSTANCE), or when focus moves to a new record (WHEN-NEW-RECORD-INSTANCE). There are standard events that almost every form sends, and certain forms send additional product-specific events.The Scope is evaluated based on the current runtime context to determine if a Rule should be processed or not. The Scope can be at the Site, Responsibility, User, or Industry level. Each Rule can have one or more Scopes associated with it.

 

The Condition is an optional SQL code fragment that is evaluated when the Event occurs; if it evaluates to TRUE then the Actions are processed.

Each Action consists of one of the following:

·   setting a Property, such as making a field Required or hiding a Tab page

·   executing a Builtin, such as GO_BLOCK, DO_KEY or FND_FUNCTION.EXECUTE

·   displaying a Message

·   enabling a Special menu entry

Once Rules are defined, when the target function is run then the Rules are automatically applied as events occur within that form. Although the Form Personalization feature is declarative, the intended audience is a person familiar with Oracle Forms including the PL/SQL programming language, and the Oracle Applications Development Guide. Additionally, any change made could interfere with the base code of a form (the code that Oracle ships), thus the Support statements discussed later in this chapter must be followed diligently.

Using the Personalization Form

 

To create personalizations for a particular function, first invoke that function from the Navigation menu. While in the form, choose Help->Diagnostics->Custom Code-> Personalize from the pulldown menu. This menu entry is secured by the FND_HIDE_DIAGNOSTICS (Hide Diagnostics menu entry) and DIAGNOSTICS (Utilities:Diagnostics) profiles, as are most other entries on the Diagnostics menu.

 

The Personalization form will open and automatically query existing Rules for that function. After making changes, Save them then close and re-run the function to have them take effect. You can also Validate or Apply certain changes immediately to test them without having to re-run the target form by pressing the ‘Validate’ or ‘Apply Now’ buttons.

 

 Why personalization?

Ø      Oracle Supports personalization unlike customization

Ø      Personalization are stored in tables rather than files

Ø      Will not have a bigger impact when you upgrade or apply patches to the environment

Ø      Can be moved easily through FNDLOAD from one instance to other

Ø      Can be restricted at site/responsibility/user level

Ø      Easy to disable/enable with click of a button.

Ø      Personalization will store who columns with which we have the ability to track who created/modified it where as in CUSTOM.PLL we don’t have that ability.

Ø      Can be applied to new responsibilities/users easily.

Ø      Can be restricted to function or form.

 

What can be done through personalization?

Ø      Zoom from one form to another

Ø      Pass data from one form to another through global variables

Ø      Change LOV values dynamically

Ø      Enable/Disable/Hide fields dynamically

Ø      Display user friendly messages when required

Ø      Launch URL directly from oracle form

Ø      Execute PL/SQL programs through FORM_DDL package

Ø      Call custom libraries dynamically

 

Personalization Tables:

FND_FORM_CUSTOM_RULES

FND_FORM_CUSTOM_ACTIONS

FND_FORM_CUSTOM_SCOPES

FND_FORM_CUSTOM_PARAMS

FND_FORM_CUSTOM_PROP_LIST

FND_FORM_CUSTOM_PROP_VALUES  

 

 Thanks – Shivmohan Purohit

Oracle System Administration – Oracle Apps DBA – Application Developer


 

Hello Friends, a long awaited , here are some of Questions – for Application DBA role, also if you like to know more about oracle application technical insight, this will be useful, do share your thought on whether these are simple , medium or complex questions so i can work out to find more … keep readking

 

What is US directory in $AD_TOP or under various product TOP’s .

US directory is defauly language directory in Oracle Applications. If you have multiple languages Installed in your Applications then you will see other languages directories besides US, that directory will contain reports, fmx and other code in that respective directory like FR for France, AR for arabic, simplifies chinese or spanish.

What are main concurrent Manager types.

ICM – Internal Concurrent Manager which manages concurrent Managers

Standard Managers – Which Manage processesing of requests.

CRM – Conflict Resolution Managers , resolve conflicts in case of incompatibility.

 

What is difference between ICM, Standard Managers & CRM in Concurrent Manager?

ICM stand for Internal Concurrent Manager, which controls other managers. If it finds other managers down , it checks & try to restart them. You can say it as administrator to other concurrent managers. It has other tasks as well.

Standard Manager These are normal managers which control/action on the requests & does batch or single request processing.

CRM acronym for Conflict Resolution Manager is used to resolve conflicts between managers & request. If a request is submitted whose execution is clashing or it is defined not to run while a particular type of request is running then such requests are actioned/assigned to CRM for Incompatibilities & Conflict resolution.

 

Where is Concurrent Manager log file location.

By default standard location is $APPLCSF/$APPLLOG, in some cases it can go to $FND_TOP/log as well

 

What is multi node system?

Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database; Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.

ICM stand for Internal Concurrent Manager, which controls other managers. If it finds other managers down , it checks & try to restart them. You can say it as administrator to other concurrent managers. It has other tasks as well.

Standard Manager These are normal managers which control/action on the requests & does batch or single request processing.

CRM acronym for Conflict Resolution Manager is used to resolve conflicts between managers & request. If a request is submitted whose execution is clashing or it is defined not to run while a particular type of request is running then such requests are actioned/assigned to CRM for Incompatibilities & Conflict resolution.

 

Where is Concurrent Manager log file location.

By default standard location is $APPLCSF/$APPLLOG, in some cases it can go to $FND_TOP/log as well

 

What is multi node system?

Multi Node System in Oracle Applications 11i means you have Applications 11i Component on more than one system. Typical example is Database; Concurrent Manager on one machine and forms, Web Server on second machine is example of Two Node System.

 

 

What is .dbc file, where its stored, what use of .dbc file?

dbc as name says is database connect descriptor file which stores database connection information used by application tier to connect to database. This file is in directory $FND_TOP/secure also called as FND_SECURE

How to determine Oracle Apps 11i Version?

Select RELEASE_NAME from fnd_product_groups;
You should see output like

RELEASE_NAME

———————–

11.5.9 or 11.5.10.2

 

 

Whats is TWO_TASK in Oracle Database?

TWO_TASK mocks your tns alias which you are going to use to connect to database. Lets assume you have database client with tns alias defined as PROD to connect to Database PROD on machine teachmeoracle.com listening on port 1521. Then usual way to connect is sqlplus username/passwd@PROD ; now if you don’t want to use @PROD then you set TWO_TASK=PROD and then can simply use sqlplus username/passwd then sql will check that it has to connect to tnsalias define by value PROD i.e. TWO_TASK

 

What is GWYUID ?

GWYUID , stands for Gateway User ID and password. Usually like APPLSYSPUB/PUB

Where GWYUID defined & what is its used in Oracle Applications?

GWYUID is defined in dbc i.e. Database Connect Descriptor file. It is used to connect to database by think clients

 

How to check number of forms users at any time?

Forms Connections initiate f60webmx connections so you can use
ps -ef | grep f60webmx | wc -l

 

 

What is 0 & Y in FNDCPASS, FNDLOAD or WFLOAD?

0 & Y are flags for FND Executable like FNDCPASS & FNDLOAD where
0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form.

‘Y’ indicates the method of invocation. i.e. it is directly invoked from the command-line not from the Submit Request Form.

 

 

If your system has more than one Jinitiator, how will the system know, which one to pick. ?

When client makes a forms connection in Oracle Applications, forms client session uses configuration file defined by environment variable FORMS60_WEB_CONFIG_FILE also called as appsweb config file. These days this file is of format appsweb_$CONTEXT.cfg The initiator version number defined by parameter jinit_ver_name in this file will be used.

 

What are .ldt & .lct files which you see in apps patch or with FNDLOAD?

ldt & .lct stands for Loader datafile & Loader configuration files, used frequently in migrating customization, profile options, configuration data, etc.. across Instances.

What is dev60cgi & f60cgi?

cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access forms server . Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi

What is difference between COMPILE_ALL=SPECIAL and COMPILE=ALL while compiling Forms?

Both the options will compile all the PL/SQL in the resultant .FMX, .PLX, or .MMX file but COMPILE_ALL=YES also changes the cached version in the source .FMB, .PLL, or .MMB file. This confuses version control and build tools (CVS, Subversion, make, scons); they believe you’ve made significant changes to the source. COMPILE_ALL=SPECIAL does not do this.
 

 

What is ps -ef or ps command in Unix ?

ps is unix/linux utility or executable to find status of process. Used mainly to find if services/process is running or not.

Thanks – Shivmohan Purohit

Oracle DBA Interview Questions

 

Oracle Technical Interview Questions and Answers : SQL based questions


 

Oracle Technical Interview Questions and Answers : SQL based questions

 

1. To see current user name

Sql> show user;

2. Change SQL prompt name

SQL> set sqlprompt “Start > “

Start >

 

3. Switch to DOS prompt

SQL> host

 

4. How do I eliminate the duplicate rows ?

SQL> delete from table_name where rowid not in (select max(rowid) from table group by

duplicate_values_field_name);

or

SQL> delete duplicate_values_field_name dv from table_name ta where rowid <(select min(rowid) from

table_name tb where ta.dv=tb.dv);

Example.

Table Emp

Empno Ename

101 Scott

102 Jiyo

103 Millor

104 Jiyo

105 Smith

delete ename from emp a where rowid < ( select min(rowid) from emp b where a.ename = b.ename);

The output like,

Empno Ename

101 Scott

102 Millor

103 Jiyo

104 Smith

 

5. How do I display row number with records?

To achive this use rownum pseudocolumn with query, like SQL> SQL> select rownum, ename from emp;

Output:

1 Scott

2 Millor

3 Jiyo

4 Smith

 

6. Display the records between two range

select rownum, empno, ename from emp where rowid in

(select rowid from emp where rownum <=&upto

minus

select rowid from emp where rownum<&Start);

Enter value for upto: 10

Enter value for Start: 7

ROWNUM EMPNO ENAME

——— ——— ———-

1 7782 CLARK

2 7788 SCOTT

3 7839 KING

4 7844 TURNER

 

7. I know the nvl function only allows the same data type(ie. number or char or date Nvl(comm, 0)), if commission is null then the text “Not Applicable” want to display, instead of blank space. How do I write the query?

SQL> select nvl(to_char(comm.),’NA’) from emp;

Output :

NVL(TO_CHAR(COMM),’NA’)

———————–

NA

300

500

NA

1400

NA

NA

 

8. Oracle cursor : Implicit & Explicit cursors

Oracle uses work areas called private SQL areas to create SQL statements. PL/SQL construct to identify each and every work are used, is called as Cursor. For SQL queries returning a single row, PL/SQL declares all implicit cursors. For queries that returning more than one row, the cursor needs to be explicitly declared.

 

9. Explicit Cursor attributes

There are four cursor attributes used in Oracle cursor_name%Found, cursor_name%NOTFOUND, cursor_name%ROWCOUNT, cursor_name%ISOPEN

 

10. Implicit Cursor attributes

Same as explicit cursor but prefixed by the word SQL

SQL%Found, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN

Tips : 1. Here SQL%ISOPEN is false, because oracle automatically closed the implicit cursor after

executing SQL statements.

: 2. All are Boolean attributes.

 

11. Find out nth highest salary from emp table

SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B

WHERE a.sal<=b.sal);

Enter value for n: 2

SAL

———

3700

 

12. To view installed Oracle version information

SQL> select banner from v$version;

 

13. Display the number value in Words

SQL> select sal, (to_char(to_date(sal,’j’), ‘jsp’))

from emp;

the output like,

SAL (TO_CHAR(TO_DATE(SAL,’J’),’JSP’))

——— —————————————————–

800 eight hundred

1600 one thousand six hundred

1250 one thousand two hundred fifty

If you want to add some text like,

Rs. Three Thousand only.

SQL> select sal “Salary “,

(‘ Rs. ‘|| (to_char(to_date(sal,’j’), ‘Jsp’))|| ‘ only.’))

“Sal in Words” from emp

/

Salary Sal in Words

——- ——————————————————

800 Rs. Eight Hundred only.

1600 Rs. One Thousand Six Hundred only.

1250 Rs. One Thousand Two Hundred Fifty only.

 

14. Display Odd/ Even number of recordsOdd number of records:

 

select * from emp where (rowid,1) in (select rowid, mod(rownum,2) from emp);

1

3

5

Even number of records:

select * from emp where (rowid,0) in (select rowid, mod(rownum,2) from emp)

2

4

6

 

15. Which date function returns number value?

months_between

 

16. Any three PL/SQL Exceptions?Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

 

 

17. What are PL/SQL Cursor Exceptions?Cursor_Already_Open, Invalid_Cursor

 

 

18. Other way to replace query result null value with a text

SQL> Set NULL ‘N/A’

to reset SQL> Set NULL ‘’

 

19. What are the more common pseudo-columns?

SYSDATE, USER , UID, CURVAL, NEXTVAL, ROWID, ROWNUM

 

20. What is the output of SIGN function?1 for positive value,

 

0 for Zero,

-1 for Negative value.

 

21. What is the maximum number of triggers, can apply to a single table?

12 triggers.

 

Thanks – Shivmohan Purohit

Trace a Concurrent Request And Generate TKPROF File


Hello Friends, As in Oracle Application, there always need to tune programs , procedure and it is always difficult to make a perfect program, here i am trying to put a “How to” on tkprof and surely like to know if you find it useful.

How to Trace a Concurrent Request And Generate TKPROF File

 Enable Tracing For The Concurrent Manager  Program 

  • Responsibility: System Administrator
  • Navigate: Concurrent > Program > Define
  • Query Concurrent Program
  • Select the Enable Trace Checkbox 

Turn On Tracing

  • Responsibility: System Administrator
  • Navigate: Profiles > System
  • Query Profile Option Concurrent: Allow Debugging
  • Set profile to Yes

 Run Concurrent Program With Tracing Turned On

  • Logon to the Responsibility that runs the Concurrent Program 
  •  In the Submit Request Screen click on Debug Options (B)
  • Select the Checkbox for SQL Trace

 2. Find Trace File Name

  Run the following SQL to find out the Raw trace name and location for the concurrent program.  The SQL prompts the user for the request id

SELECT ‘Request id: ‘||request_id ,  ‘Trace id: ‘||oracle_Process_id,  ‘Trace Flag: ‘||req.enable_trace,  ‘Trace Name:  ‘||dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’,  ‘Prog. Name: ‘||prog.user_concurrent_program_name,  ‘File Name: ‘||execname.execution_file_name|| execname.subroutine_name ,  ‘Status : ‘||decode(phase_code,’R’,’Running’)  ||’-‘||decode(status_code,’R’,’Normal’),  ‘SID Serial: ‘||ses.sid||’,’|| ses.serial#,  ‘Module : ‘||ses.module  from fnd_concurrent_requests req, v$session ses, v$process proc,  v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,  fnd_executables execname  where req.request_id = &request  and req.oracle_process_id=proc.spid(+)  and proc.addr = ses.paddr(+)  and dest.name=’user_dump_dest’  and dbnm.name=’db_name’  and req.concurrent_program_id = prog.concurrent_program_id  and req.program_application_id = prog.application_id  and prog.application_id = execname.application_id  and prog.executable_id=execname.executable_id; 

 

 

 

 

3. TKPROF Trace File

Once you have obtained the Raw trace file you need to format the file using TKPROF.

$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no

Where: raw_trace_file.trc: Name of trace file

output_file: tkprof out file

explain: This option provides the explain plan for the sql                      statements

sort: his provides the sort criteria in which all sql statements will be sorted.  This will bring the bad sql at the top of the outputfile.

sys=no:Disables sql statements issued by user SYS 

Another example: To get (TKPROF) sorted by longest running queries first and limits the results to the “Top 10” long running queries

 

 

$ tkprof <filename.trc> <output_filename> sys=no explain=apps/<password> sort='(prsela,exeela,fchela)’ print=10 

 

 

 

 

Thanks – Shivmohan Purohit 

 

How To Open A Worksheet Directly From Oracle Applications Menu ?


Hello Friends, many of my past work involve integrating oracle discoverer with oracle applications, here there is one of the ways to leverage both to integrate within applications.

 How To Open A Worksheet Directly From Oracle Applications Menu ?

 

 If a workbook has 4 worksheets how to default to a particular worksheet. This article will help you to pass the worksheet identifier and parameter directly while opening a workbook. The worksheet identifier and the worksheet parameters for the workbook has to be specified in the form function with below mentioned guidlines.
 

Form Function  Definition

 

Function Name

Internal Function Name

Function User Name

Function name (as shown on the menu)

Type

WWW

Description

Function description

Parameters

workbook=workbookname
&PARAMETERS=sheetid~worksheet id*param_parameter name One~Parameter One Value*param_parameter name Two~Parameter Two Value*

HTML Call

OracleOasis.RunDiscoverer

 

 Parameters in the parameter list are delimited by the characters

“param_” at the beginning of each parameter and
“~” as delimiter between parameter name and value and
“*” at the end of each parameter

 ICX interprets the PARAMETER list and passes the parameters to Discoverer in the required URL format.

 Example 1

 If the workbook id is CAHRMS_ABSENCE_CALENDAR_REPORT and worksheet id is 1 then parameter will be as follows

 workbook=CAHRMS_ABSENCE_CALENDAR_REPORT&PARAMETERS=sheetid~1*

 Example 2

If the workbook id is CAHRMS_ABSENCE_CALENDAR_REPORT , worksheet id is 1 and the value for worksheet parameter “deptname”  to be passed is 10 then parameter will be as follows

workbook=CAHRMS_ABSENCE_CALENDAR_REPORT&PARAMETERS=sheetid~1*param_deptname~10*

 Example 3

If a workbook has two parameters “Company” and “City” you would specify in the Parameter list of the Form funtion:

param_Company~Oracle Corp.*param_City~Redwood Shores* 

Thanks – Shivmohan . do share your feedback