Script to load or convert Notes/Attachments associtaed with items

Script to load or convert Notes/Attachments associtaed with items

-- Script to load or convert Notes/Attachments associated with items.
DECLARE
l_doc_category_id NUMBER;
l_document_id NUMBER;
l_attached_document_id NUMBER;
ll_media_id NUMBER;
l_fnd_user_id NUMBER;
l_short_datatype_id NUMBER;
BEGIN
-- Select User_id
SELECT user_id
INTO l_fnd_user_id
FROM apps.fnd_user
WHERE user_name ='XXX';
-- Get Data type id for Short Text types of attachments
SELECT datatype_id
INTO l_short_datatype_id
FROM apps.fnd_document_datatypes
WHERE name ='SHORT_TEXT';
-- Select Category id for "Vendor/To Supplier" Attachments
SELECT category_id
INTO l_doc_category_id
FROM apps.fnd_document_categories
WHERE name = 'Vendor';
-- Select nexvalues of document id, attached document id and
-- l_media_id
SELECT apps.fnd_documents_s.NEXTVAL,
apps.fnd_attached_documents_s.NEXTVAL,
apps.fnd_documents_short_text_s.NEXTVAL
INTO l_document_id,
l_attached_document_id,
l_media_id
FROM DUAL;
INSERT INTO apps.fnd_documents
(document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
datatype_id,
category_id,
security_type,
security_id,
publish_flag,
usage_type
)
VALUES
(l_document_id,
SYSDATE,
l_fnd_user_id,
SYSDATE,
l_fnd_user_id,
l_short_datatype_id, -- Datatype for 'SHORT_TEXT'
l_doc_category_id, -- Category_id
1, -- 'Organization' Level Security
352, -- Organization id for Inventory Item Master Org
'Y', -- Publish_flag
'O' -- Usage_type of 'One Time'
);
INSERT INTO apps.fnd_documents_tl
(document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
language,
description,
media_id,
translated
)
VALUES
(l_document_id,
SYSDATE,
l_fnd_user_id,
SYSDATE,
l_fnd_user_id,
'AMERICAN', -- language
'EXTENDED DESCRIPTION', -- description
l_media_id, -- media_id
'Y' -- translated
);
INSERT INTO apps.fnd_attached_documents
(attached_document_id,
document_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
seq_num,
entity_name,
pk1_value,
pk2_value,
automatically_added_flag
)
VALUES
(l_attached_document_id,
l_document_id,
SYSDATE,
lcl_fnd_user_id,
SYSDATE,
lcl_fnd_user_id,
20, -- Sequence Number of attachment.
'MTS_SYSTEM_ITEMS', -- Entity_name Table Name assoicated with attachment
352, -- Organization id for Inventory Item Master Org
567, -- Inventory Item Id
'N' -- Automatically_added_flag
);

INSERT INTO apps.fnd_documents_short_text
(media_id,
short_text
)
VALUES
(lcl_media_id,
'Write your Short Text Here' -- Notes/Attachments text
);
COMMIT;
END;
/

AutoInvoice Overview in Receivables

AutoInvoice is a program that can be used to import and validate transaction data from other financial systems from which one can create invoices, debit memos, credit memos, and on-account credits. It rejects transactions with invalid information to insure the integrity of the data. This fits well with in Oracle ERP or to integrate with any third party application.

Top 10 reasons for using Auto Invoice

1. Powerful Interface Tool
2. Supports Oracle & Non-Oracle Systems
3. Import Large Amount of Data
4. Calculate or Import Tax
5. Group Lines & Invoices
6. Online Error Correction
7 .Lines Validation
8. Derive GL Date
9 .Import Flex fields
10.Import or Derive Accounting Info

What is inside AutoInvoice
AutoInvoice is a program set consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.
Master (RAXMTR)
Import (RAXTRX)
Purge (RAXDEL)

1. Auto Invoice Master program RAXMTR
Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.
•Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables
•Marks interface records for processing by marking request_id
•Submits multiple workers for Parallel Processing by creating instances for request.

2. Auto Invoice Import Program
Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which you can view by clicking the View Report button in the Requests window.Working of Auto invoice , Validates data, Inserts records, Deletes interface data Only when system option purge set to ‘Y’

3. Auto Invoice Purge Program
Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run,and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported.Deletes all rows where interface_status =‘P’
• Ra_interface_lines
• Ra_interface_distributions
• Ra_interface_salescredits

Oracle Receivable’s Auto Invoice program will be used to import and validate Invoices. A custom feeder program is required to transfer data from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL).If there is need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table. When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report. Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

How Autoinvoice Execution works
Normally, Auto Invoice can be divided into three major phases, Pre-grouping: here the validates all of the line level data takes place, Grouping: groups lines based on the grouping rules and validates header level data, Transfer :validates information that exists in Receivables tables

What happen when AutoInvoice run?
Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options.Line, accounting, and sales credit information for each line populates 3 interface tables
Lines are ordered and grouped
Tax is calculated
GL date is determined
GL accounts are assigned using Auto Accounting
Tax, freight, commitments, and credit memos are linked to transaction lines
All transactions are batched
Validated lines are used to create the transaction

How Data is flowing?
Select, insert and update and delete take place on certain tables once it is logged out.
Selects
– RA_INTERFACE_LINES_ALL
– RA_INTERFACE_DISTRIBUTIONS_ALL
– RA_INTERFACE_SALESCREDITS_ALL

Updates/Insert
– RA_INTERFACE_ERRORS_ALL
– RA_CUSTOMER_TRX_ALL
– RA_CUSTOMER_TRX_LINES_ALL
– AR_PAYMENT_SCHEDULES_ALL
– AR_RECEIVABLE_APPLICATIONS_ALL

Inserts
– RA_INTERFACE_ERRORS_ALL

AutoInvoice Exception Handling
Records that fail validation are called ‘Exceptions’. Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL. Errors can be corrected in the Exception Handling window. Once corrections are made, Auto invoice must be resubmitted. Records that pass validation get transferred to Receivables tables

AutoInvoice Exception Handling Windows
-Interface Exception window displays exception messages associated with all invalid records
-Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors
-The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window
-Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error
-Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button
-Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to

AR Adjustments Explanation

An Adjustment in AR is an amount added or subtracted from the amount due of an Invoice, Debit Memo, Chargeback, Deposit, or Guarantee.

Oracle AR lets you create Automatic or Manual Adjustments.

Receivables lets you make either Positive or Negative Adjustments to your Invoices, Debit Memos, Chargebacks, On–account Credits, Deposits, and Guarantees.

You can approve Adjustments that are within your approval limits and give pending statuses to Adjustments that are outside your approval limits.

You can also automatically write off debit items that meet your selection criteria.

Business need for Adjustment

Its very similar to write-off. If there is any unapplied balance in invoice after application of receipt in small value for rounding off purpose , that will be adjusted while making receipt application.

Or sometime, when Invoice amount is wrongly entered you can also add a line by adding or reducing amount.

These are very common across all industry.Telecom, Health care and retail sectors the need is very high for such functionality.

In R12 what is MO_GLOBAL.INIT

Purpose of mo_global.init :-

It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used.
If the new MO security profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in table mo_glob_org_access_tmp

When & from where is mo_global.init called ?

This package procedure will be called as soon as you login or as soon as you switch responsibility. Just like
FND_GLOBAL.INITIALIZE is called. It is safe to assume that Oracle will invoke MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE

From SQL*Plus, you can simulate login to a specific responsibility by calling
a.Call FND_GLOBAL.INITIALIZE
This will set your responsibility id, user_id etc

b. call MO_GLOBAL.INIT
This will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.

Introduction to LINUX

Introduction to LINUX

Typical Installation of Linux is as simple as installing windows operating systems. Custom installation requires some expertise in understanding the Linux directory structure. Linux always takes the unformatted partition to load operating system

Directory structure
/
root bin etc usr home dev lib boot .. d01

‘/’ is the starting point, under this various default, user-defined subdirectories like d01 in which oracle applications is created

Logging to Linux server using telnet will take the user to user’s home directory.Eg. For user1 the home directory is /home/user1

Using Telnet

Telnet provides user support for the Telnet protocol, a remote access protocol you can use to log on to a remote computer , network device, or private TCP/IP network.
• To open Telnet, click Start, click Run, and then type telnet. You can also type telnet at the
command prompt.
• Using Telnet, you can make a remote connection.
• To use Telnet, you must have the TCP/IP protocol installed and configured on your computer and you must have a user account established on a remote host.
• To display help for Telnet, type help at the command prompt.

Linux Commands

ls - list directory contents

ls [OPTION]... [FILE]...
-a displays all the files including starting with .
-A displays all the files excluding implicit “.” “..”
-t sort by modification time
-r reverse order
-R recursively displays the files , subdirectories and it’s contents
-s print the size of each file, in blocks
-S sort by file size
-l displays long format

example: when you use ls -ls
4 -rw-r--r-- 1 sdaggupa users 803 Apr 1 10:10 test.c
4 drwxr-xr-x 3 sdaggupa users 4096 Apr 7 11:00 conv
[number of operating system blocks; permissions (d- directory,- file); unknown; username; usergroup; size in bytes; month; date; time; file or directory name]

clear – Clears the screen

more -

mkdir - Create the DIRECTORY(ies), if they do not already exist.
mkdir [OPTION] DIRECTORY...
-m, --mode=MODE set permission mode (as in chmod), not rwxrwxrwx - umask
-p, --parents no error if existing, make parent directories as needed

cd - change directory
cd [ or or ] relative path : path from the working directory absolute path : complete path from “/” virtual path : path defined in the current environment

cd -changes to user’s home directory

cp - copy files and directories

cp [OPTION]... SOURCE DEST
cp [OPTION]... SOURCE... DIRECTORY

OPTION -R copies the source (including subdirectories and its contents) recursively.
-u copy only when the file is newer than the destination, and when destination file is missing.

mv - move (rename) files
mv [OPTION]... SOURCE DEST
mv [OPTION]... SOURCE... DIRECTORY
Rename SOURCE to DEST, or move SOURCE(s) to DIRECTORY.
-f, --force never prompt before overwriting
-i, --interactive prompt before overwriteNote: Practice well before using otherwise you may not get back your file some times

rm - remove files or directories

rm [OPTION]... FILE...
This manual page documents the GNU version of rm. rm removes each specified file. By default, it does not remove directories.
-f, --force ignore nonexistent files, never prompt
-i, --interactive prompt before any removal
-r, -R, --recursive remove the contents of directories recursively

rmdir - remove empty directory

chmod - change file access permissions
chmod [OPTION]... MODE[,MODE]... FILE...
-R to change the mode recursively if it is directory

ex: $chmod 755 test.cafter executing the above command test.c will have rwx permissions for the user, r-x permissions for group, r-x permissions for Other users

chgrp - chgrp - change group ownership
chgrp [OPTION]... GROUP FILE...
-R to change the group ownership recursively if it is Directory

Mandatory Form and Libraries(PLLs) for Customization

Mandatory Form and Libraries

· In order to develop a form for Customization first we have to install the Oracle Forms Developer Version 6i on our local system.

· FTP the Mandatory forms i.e., TEMPLATE.fmb, APPSTAND.fmb and required Pll’s from Vision Instance to the local machine.

· The TEMPLATE.fmb and APPSTAND.fmb files would be under the following path:
/d01/oracle/visappl/au/11.5.0/forms/US/

· The PLL’s would be under the following path:
/d01/oracle/visappl/au/11.5.0/resource/

· The PLL’s are:

APPCORE.pll
APPCORE2.pll
APPDAYPK.pll
APPFLDR.pll
CUSTOM.pll
FNDSQF.pll
FV.pll
GHR.pll
GLOBE.pll
GMS.pll
HRKPI.pll
IGILUTIL.pll
IGILUTIL2.pll
JA.pll
JE.pll
JL.pll
OPM.pll
PQH_GEN.pll
PSA.pll
PSAC.pll
PSB.pll
VERT.pll
VERT1.pll
VERT2.pll
VERT3.pll
VERT4.pll
VERT5.pll

For Oracle 11.5.9 Applications we require the following PLL’s, along with the above mentioned PLL’s.

IGI_CBC.pll
IGI_CC.pll
IGI_CIS.pll
IGI_DOS.pll
IGI_EXP.pll
IGI_IAC.pll
IGI_MHC.pll
IGI_SIA.pll
IGI_STP.pll

Generating the “.fmx” file

Generating the “.fmx” file from FMB

Logon To target instance using Telnet.
Give proper username and password
Change Directory ( cd /d01/oracle/visappl )
Invoke the Environment variable by issuing this command:
. APPSORA.env
Switch to your folder where “.fmb” file is present by issuing this command: cd
Change the mode for the “.fmb” file by issuing this command
chmod 755 FORMNAME.fmb

Run the following command before generating the “.fmx” file
FORMS60_PATH=$FORMS60_PATH:$AU_TOP/forms/US

In order to Generate the “.Fmx “ file for the “.Fmb” file, issue the following command

f60gen module=FORMNAME.fmb userid=apps/apps output_file=FORMNAME.fmx module_type=form batch=no compile_all=special

Note: Type this command in a single line.

Now change the mode for the fmx file.
chmod 755 FORMNAME.fmx

Now copy this “.fmb” and “.fmx” files to the US directory.
For this we have to give the command as
cp FORMNAME.* /d01/oracle/visappl/cust/1.0/forms/US

We have successfully generated the “.fmx” file for the “.fmb” file, and also we have copied both these files in the US directory.

Setting up Forms60_Path in Registry

Setting up Forms60_Path in Registry

Copy all these PL/SQL libraries and the Template form in one folder.
Now we have to set the form60 path in the registry.
Go to Start -->Run -->Regedit -->HKEY_LOCAL_MACHINE -->Software -->Oracle
Here we can find the oracle homes. In these homes check for FORMS60_PATH.

Append the path of your folder where all the libraries and the form is present, prefixed with a semicolon(;).

E.g.: Before Appending “C:\forms\TOOLS\OPEN60\PLSQLLIB”
After Appending “C:\forms\TOOLS\OPEN60\PLSQLLIB;c:\forms\libraries”

Steps for enabling folder functionality for Custom Form

Steps for enabling folder functionality for Custom Form:

(1)Add the library APPFLDR.pll to the form. It is found in AU_TOP/resource

(2)Add the STANDARD_FOLDER object group to the form by copying it from APPSTAND.fmb

(3)Create a parameter ex: blockname_par of type number with initial value=2

(4)Create two canvases one of type stacked (ex: blockname_stacked) and the other of type content (ex: blockname_content). Display the fields in the main data block in the stacked canvas.
Display the two canvases in the same window (ex: blockname_window)

(5) Create the following 9 Triggers at Main Data Block Level

(i) WHEN-NEW-BLOCK-INSTANCE: app_folder.event('WHEN-NEW-BLOCK-INSTANCE');
(ii)KEY-PREV-ITEM if (:parameter.blockname_par = 1) then previous_item; else app_folder.event('KEY-PREV-ITEM'); end if;
(iii)KEY-NEXT-ITEM if (:parameter.blockname_par = 1) then next_item; else app_folder.event('KEY-NEXT-ITEM'); end if;
(iv)PRE-BLOCK app_folder.event(‘PRE-BLOCK’);
(v)POST-BLOCK app_folder.event(‘POST-BLOCK’);
(vi)PRE-QUERY app_folder.event(‘PRE-QUERY’);
(vii)POST-QUERY app_folder.event(‘POST-QUERY’);
(viii)KEY-EXEQRY app_folder.event(‘KEY-EXEQRY’);
(ix)FOLDER-RETURN-ACTION If (:global.folder_action ='CONFIRM-HIDE-FIELD') then If :global.folder_field in ('FIELD1','FIELD2',etc..) then :global.folder_action_allowed := 'FALSE'; End if; End if;


(6) Create FOLDER_SWITCHCreate an item FOLDER_SWITCH on folder block (main data block) with subclass property ‘Switcher’.

(7)Create Prompt Block
Create prompt items for the items in the main data block with the same names and these prompt items must also be on the stacked canvas. All these items must have the following properties

Item type: DISPLAY ITEMSubclass Information: FOLDER_PROMPT_MULTIROWCanvas: STACKED CANVAS.

(8)Add the following items to the prompt block
FOLDER_OPENItem Type: Push ButtonSubclass: FOLDER_OPENCanvas: Content CanvasFOLDER_DUMMYItem Type: Text ItemSubclass: FOLDER_DUMMYCanvas: TOOLBARFOLDER_TITLEItem Type: Display ItemSubclass: DYNAMIC_TITLECanvas: Content CanvasORDER_BY1Item type: Push ButtonSubclass: FOLDER_ORDERBYCanvas: Content CanvasORDER_BY2Item type: Push ButtonSubclass: FOLDER_ORDERBYCanvas: Content CanvasORDER_BY3Item type: Push ButtonSubclass: FOLDER_ORDERBYCanvas: Content Canvas
(9)Add the following form level triggers
FOLDER ACTION triggerapp_folder.event(:global.folder_action);

WHEN-NEW-FORM-INSTANCE trigger
app_folder.define_folder_block
('XX_FORM', --- Form Name
'XX_DATA_BLOCK', --- Database block Name where folder function needs to be enabled
'XX_PROMPT', --- Block name which is going to control the Folder Block.
'MAIN_STACK_CANVAS', --- Stacked canvas name on which the database block resides 'MAIN_WINDOW', --- Window in which the Content/Stacked Canvas reside
NULL --- Disabled Fuction );

Best Practices in Report Development

The Information drawn from some of standard documents and Development Experience.
These Tips are helpful to the Developers to maintain standards during OracleApplication Reports Development.

Before Starting Report:
The following points to be considered before starting report development:
Mode: Report Development can be done by two modes
i. Character Mode
ii. Bit Mapped.

To Set the Character Mode: Yes in Reports Property
If it is Bitmapped report then the above should be NO.
In System Parameter the Mode Parameter, initial value should be made as character orBitmapped.

Report Style
Portrait: if it is Portrait then the size would be like (80 * 66)
Landscape: 132 * 66
Land wide: 180 * 66.

The initial value should be set at system parameters ‘Orientation’.

Using Triggers in Reports:
There are 5 main triggers in Report and they can be used for different purposesas per the sequence of fire we have to write logic on that :

Before Report Trigger
For initializing environment or using User Exits we can use this trigger
As SRW.USER_EXIT (‘FND SRWINIT’)
It will set the profile option values.It can also be used to select the organization automatically.

After Report Trigger
SRW.USER_EXIT (‘FND SRWEXIT’)
This will ensure that memory allocated for AOL user exit is freed up now.

Naming Conventions
Query and Group Names
The query and group name should be meaningful as :Q_Headers ,Q_Lines, G_Headers etc.
This practice will help us to recognizeThe contents or purpose of the query.

Fields, Repeating frames, Boiler Texts
In the layout fields name should be similar to database column names.And repeating frames must reflect the relevant groups. This is useful torecognize the field or boiler palette or tracking the error of layout.

Parameters
Bind Parameters Bind parameters should start by ‘P’
Lexical Parameters should start by ‘LP’.

Exception Handling
In Programming units and formula columns we should handle possible
Exceptions as ‘NO DATA FOUND’ (one of them).
In Numeric formula Columns always handle Zero Divide error and use NVL
As
Valmont := (:quantity * :rate) / :xyz
Here if :xyz is Zero then we will get Zero Divide Error.
We should implement above logic as :
-----> V_Amount := (NVL(:qty,0) * NVL(:rate,0) / NVL(:xyz,1);

Using %type
While defining the data types in program units using %type is better practice,
function F_E MPFormula return Number is
V_EMPNO EMP.EMPNO%TYPE
Instead of :-
function F_E MPFormula return Number is
V_EMPNO number (10);

Debugging Reports
We can use srw.message to debug the report.
The output of it we can see at log file of that report.

No Data Found
For displaying NO DATA FOUND in the Report if the query is not returns anyrows. Make a text field “NO DATA FOUND” in the layout of the Report, Make asummary column that will give count of any not null field in the report , and in theformat trigger the logic should bre like if count >0 thenreturm true else return false .

END OF REPORT
To display END OF REPORT at the last line of the report just place an Text “END OFREPORT”, aligned center in the layout of the report.

Tips in report customizations:

Tips in report customizations:
1. Setup client info if views are involved, setup organization context if inventory items are involved.
2. Ensure that report builder/designer is in character mode.
3. Ensure constraints are off for stretching, grid is on. , snap to grid is on/off
4. use srw.message frequently
5. Port the rdf in Binary Mode.
6. Set the trace on for the concurrent program to actually see the sql code executed.
7. Bring the query out and changes should be tested in simple SQLs
8. Set the trace on if there are &lexical parameters if you need to check the query that is changing
dynamically.
9. Use srw.message to print the value of lexical parameters after their evaluation
10. Use of Concsub to submit your report concurrent program from UNIX faster.

To track Tables and Columns from Backend

To track Tables and Columns from Backend

To know the Object Name (Object can be anything like Package, Procedure, Function, Table, View or synonym name etc), to check whether that object exist in the database or not. If exist then, is it active or what is the owner of object etc.
All the details can be found from the following select statement.

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = 'your object name';

To know the table name and from backend and to know the column name, data_type, date_length etc of the given table. use the following Query.

SELECT column_name,data_type,data_length FROM dba_tab_columns
WHERE table_name = 'Your Table Name';

How to set Org_id at back-end

How to set Org_id at back-end
We have a API to set the org_id from the backend.

begin
fnd_client_info.set_org_context();
end;

Note:- Org_id indicates the "Operating Unit" in the Business Structure.
How to I know what Org_id is set at back-end
We can use userenv function to know the Org_id set at backend.
select userenv('CLIENT_INFO') from dual;

Why we need to set org_id at back-end
We usually, set org_id at back-end. So that we can get the org_id records from the Multi Organization views.

Above is applicable only till 11i version. It is different in Release 12.

Table backup

How can we take backup of table

CREATE TABLE backup_table_name AS SELECT * FROM table_to_backup;

Package compilation commands

Which version of 'alter package' compiles just the header? the body? both?

ALTER PACKAGE package_name COMPILE - compiles the header and the body
ALTER PACKAGE package_name COMPILE PACKAGE - compiles just the header
ALTER PACKAGE package_name COMPILE BODY - compiles just the body

fnd tables

Useful Fnd tables

fnd_concurrent_queues
fnd_concurrent_programs
fnd_concurrent_requests
fnd_concurrent_processes
fnd_concurrent_queue_size
fnd_appl_tops
fnd_logins
fnd_user
fnd_user_resp_groups
fnd_executables
fnd_lookups
fnd_dm_nodes
fnd_tns_aliases
fnd_nodes
fnd_responsibility
fnd_databases
fnd_unsuccessful_logins
fnd_languages
fnd_application
fnd_profile_option_values

Base tables for Procure To Pay

Requisition
po_requisition_headers_all
po_requisition_lines_all ( segment1 ========== requisition number)
po_req_distributions_all

Purchase order
po_headers_all
po_lines_all
po_distributions_all
po_line_locations_all

Receipt
rcv_shipment_headers
rcv_shipment_lines
rcv_transactions
quality result stored in qa_results table

Note: shipment_header_id is the link between rcv_shipment_headers and rcv_shipment_linespo_header_id is the link between rcv_shipment_headers and rcv_transaction

Invoices
ap_invoices_all
ap_invoice_distributions_all

Payments
ap_checks_all
ap_invoice_payments_all
ap_payment_schedules_all

Payment Reconcillation
ap_banks
ap_bank_branches

GL Transfer
gl_periods
gl_period_status
gl_set_of_books
glfv_charts_of_accounts

Interface Tables of GL, AP, AR,FA,INV,PO,BOMS,WIP,OM

GL INTERFACE TABLES

GL_BUDGET_INTERFACE
GL_DAILY_RATES_INTERFACE
GL_IEA_INTERFACE
GL_INTERFACE
GL_INTERFACE_CONTROL
GL_INTERFACE_HISTORY

AP INTERFACE TABLES
AP_INTERFACE_CONTROLS
AP_INTERFACE_REJECTIONS
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

AR INTERFACE TABLES
AR_PAYMENTS_INTERFACE_ALL
AR_TAX_INTERFACE
HZ_DQM_SYNC_INTERFACE
HZ_PARTY_INTERFACE
HZ_PARTY_INTERFACE_ERRORS
RA_CUSTOMERS_INTERFACE_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_ERRORS_ALL
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS_ALL

FA INTERFACE TABLES
FA_BUDGET_INTERFACE
FA_INV_INTERFACE
FA_PRODUCTION_INTERFACE
FA_TAX_INTERFACE

INVENTORY INTERFACE TABLES
MTL_CC_ENTRIES_INTERFACE
MTL_CC_INTERFACE_ERRORS
MTL_CI_INTERFACE
MTL_CI_XREFS_INTERFACE
MTL_COPY_ORG_INTERFACE
MTL_CROSS_REFERENCES_INTERFACE
MTL_DEMAND_INTERFACE
MTL_DESC_ELEM_VAL_INTERFACE
MTL_EAM_ASSET_NUM_INTERFACE
MTL_EAM_ATTR_VAL_INTERFACE
MTL_INTERFACE_ERRORS
MTL_INTERFACE_PROC_CONTROLS
MTL_ITEM_CATEGORIES_INTERFACE
MTL_ITEM_CHILD_INFO_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE
MTL_ITEM_SUB_INVS_INTERFACE
MTL_OBJECT_GENEALOGY_INTERFACE
MTL_RELATED_ITEMS_INTERFACE
MTL_RESERVATIONS_INTERFACE
MTL_RTG_ITEM_REVS_INTERFACE
MTL_SECONDARY_LOCS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE
MTL_SO_RMA_INTERFACE
MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_TXN_COST_DET_INTERFACE

PO INTERFACE TABLES
PO_DISTRIBUTIONS_INTERFACE
PO_HEADERS_INTERFACE
PO_INTERFACE_ERRORS
PO_LINES_INTERFACE
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_RESCHEDULE_INTERFACE
RCV_HEADERS_INTERFACE
RCV_LOTS_INTERFACE
RCV_SERIALS_INTERFACE
RCV_TRANSACTIONS_INTERFACE


BOM INTERFACE TABLES
BOM_BILL_OF_MTLS_INTERFACE
BOM_INTERFACE_DELETE_GROUPS
BOM_INVENTORY_COMPS_INTERFACE
BOM_OP_RESOURCES_INTERFACE
BOM_OP_ROUTINGS_INTERFACE
BOM_OP_SEQUENCES_INTERFACE
BOM_REF_DESGS_INTERFACE
BOM_SUB_COMPS_INTERFACE
CST_COMP_SNAP_INTERFACE
CST_INTERFACE_ERRORS
CST_ITEM_COSTS_INTERFACE
CST_ITEM_CST_DTLS_INTERFACE
CST_PC_COST_DET_INTERFACE
CST_PC_ITEM_COST_INTERFACE

WIP INTERFACE TABLES
WIP_COST_TXN_INTERFACE
WIP_INTERFACE_ERRORS
WIP_JOB_DTLS_INTERFACE
WIP_JOB_SCHEDULE_INTERFACE
WIP_MOVE_TXN_INTERFACE
WIP_SCHEDULING_INTERFACE
WIP_TXN_INTERFACE_ERRORS TABLE

ORDER MANAGEMENT INTERFACE TABLES
SO_CONFIGURATIONS_INTERFACE
SO_HEADERS_INTERFACE_ALL
SO_HEADER_ATTRIBUTES_INTERFACE
SO_LINES_INTERFACE_ALL
SO_LINE_ATTRIBUTES_INTERFACE
SO_LINE_DETAILS_INTERFACE
SO_PRICE_ADJUSTMENTS_INTERFACE
SO_SALES_CREDITS_INTERFACE
SO_SERVICE_DETAILS_INTERFACE
WSH_DELIVERIES_INTERFACE
WSH_FREIGHT_CHARGES_INTERFACE
WSH_PACKED_CONTAINER_INTERFACE

10 Tips for Project Success

10 Tips for Project Success

Starting out: Make sure that when you start out your customer defines their requirements in depth. You need to know exactly what it is that must be delivered, to who and when. Make it specific, write it up formally and get them to sign it off. This document will become the basis upon which to measure your success.

Customers: Involve your customers throughout the entire project life cycle. Get them involved in the analysis and planning, as well as execution. You don't have to seek their approval, just keep them informed. The more you involve them, the greater their level of buy-in and the easier it is to manage their expectations.

Timeframes: Keep your delivery timeframes short and realistic. Never agree to lengthy timeframes. Split the project into "mini-projects" if you need to. Keep each mini-project to less than 6 months. This keeps everyone motivated and focused.

Milestones: Break your project timeframe into "Milestones" which are manageable pieces of work. Add delivery deadlines to your milestones and try to deliver on every deadline, no matter what. If you're late, tell your customer about it as early as possible.

Communications: Make sure you keep everyone informed by providing the right information at the right time. Produce Weekly Status Reports and run regular team meetings.

Scope: Only authorize changes to your project scope if there is no impact on the timeline. Get your customers approval to important scope changes first and then get their buy-in to extend the delivery dates if you need to.

Quality: Keep the quality of your deliverables as high as possible. Constantly review quality and never let it slip. Implement "peer reviews" so that team members can review each others deliverables. Then put in place external reviews to ensure that the quality of the solution meets your customer's needs.

Issues: Jump on risks and issues as soon as they are identified. Prioritize and resolve them before they impact on your project. Take pride in keeping risks and issues to a minimum.

Deliverables: As each deliverable is complete, hand it formally over to your customer. Get them to sign an Acceptance Form to say that it meets their expectations. Only then can you mark each deliverable off as 100% complete.

Your team: Great projects are run by great teams. Hire the best people you can afford. Spend the time to find the right people. It will save you time down the track. Remember, good people are easy to motivate. Show them the vision and how they can make it happen. Trust and believe in them. Make them feel valued. They will work wonders.

And that's it. With these 10 tips you can boost your project success. And here's one last tip that will save you time and effort - use Project Management Templates to produce deliverables faster and easier.

Report Questions

1. What is SRW Package?
Ans: The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors
Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views, etc,
SRW.SET_FIELD_NUM
SRW. SET_FIELD_CHAR
SRW. SET FIELD _DATE

2. What are Lexical Parameters and bind parameters?
Lexical Parameter is a Simple text string that to replace any part of a SELECT statement. Column names, the from clause, where clause or the order by clause. To create a lexical reference in a query we prefix the parameter name with an ampersand (ex. &.dname,)

3. What is User Parameters?
A parameter, which is created by user. For to restrict values with where clause in select statement.
Data type, width, input mask, initial value, validation trigger, list of values
We can use Lovs in use in user parameter with static and Dynamic Select Statement.

4. What is System Parameters: These are built-in parameters by corporation.
BACKGROUND: Is whether the report should run in the foreground or the background.
COPIES Is the number of report copies that should be made when the report is printed.
CURRENCY Is the symbol for the currency indicator (e.g., "$").

Ans: The Report builder Built in package know as SRW Package (Sql Report Writer) This package extends reports, Control report execution, output message at runtime, Initialize layout fields, Perform DDL statements used to create or Drop temporary table, Call User Exit, to format width of the columns, to page break the column, to set the colors
Ex: SRW.DO_SQL, It’s like DDL command, we can create table, views, etc,
SRW.SET_FIELD_NUM
SRW. SET_FIELD_CHAR
SRW. SET FIELD _DATE
2. What are Lexical Parameters and bind parameters?
Lexical Parameter is a Simple text string that to replace any part of a SELECT statement. Column names, the from clause, where clause or the order by clause. To create a lexical reference in a query we prefix the parameter name with an ampersand (ex. &.dname,)
3. What is User Parameters?
A parameter, which is created by user. For to restrict values with where clause in select statement.
Data type, width, input mask, initial value, validation trigger, list of values
We can use Lovs in use in user parameter with static and Dynamic Select Statement.
4. What is System Parameters: These are built-in parameters by corporation.
BACKGROUND: Is whether the report should run in the foreground or the background.
COPIES Is the number of report copies that should be made when the report is printed.
CURRENCY Is the symbol for the currency indicator (e.g., " $?).
DECIMAL Is the symbol for the decimal indicator (e.g., ".").
DESFORMAT Is the definition of the output device's format (e.g., landscape mode for a printer). This parameter is used when running a report in a character-mode environment, and when sending a bitmap report to a file (e.g. to create PDF or HTML output).
DESNAME Is the name of the output device (e.g., the file name, printer's name, mail userid).
DESTYPE Is the type of device to which to send the report output (screen, file, mail, printer, or
Screen using PostScript format).
MODE Is whether the report should run in character mode or bitmap.
ORIENTATION Is the print direction for the report (landscape, portrait, default).
PRINTJOB Is whether the Print Job dialog box should appear before the report is run.
THOUSANDS Is the symbol for the thousand's indicator (e.g., ",").

5. How many Types of Reports available in Reports
Tabular form-like form – letter Group left
Group above matrix Matrix with group Mailing label
Matrix Report: Simple, Group above, Nested
Simple Matrix Report required 4 groups
1. Cross Product Group
2. Row and Column Group
3. Cell Group
4. Cell column is the source of a cross product summary that becomes the cell content.
Frames: 1.Repeating frame for rows (down direction)
2. Repeating frame for columns (Across)
3. Matrix object the intersection of the two repeating frames

6. What Types of Triggers are Available in Reports.
Report level Triggers
Data Model Triggers
Layout Model Triggers
Report Level Triggers
Before parameter form: If u want take parameters passed to the report and manipulate them so that they appear differently in the parameter form., this is where modification can be done for ex: when u want pass a deptno but show the dname selected , use a before parameter form trigger.
After parameter form & Before Report: These two triggers are fired one after the other. No event occurs in between them. However the way the way that the reports product behaves when the triggers fail is quite different. If the After Parameter trigger fails the report will be put back into the parameter form. It’s useful to place code here to check whether values in your parameter form are valid. Even though the Before Report trigger is executed before the query runs, if this trigger fails it won’t fail until reports tries to display the first page of the report. This means that even if something goes wrong in the before report trigger (meaning that you may not want to run the query at all) It will run anyway
Between pages: This Trigger fires before all pages except first page one. It will not fire after the last page of a report. If a report only has one page it will not fire at all. You can use this trigger to send specific control to the change the paper orientation or to do double sided printing
After report: This trigger fires the report has printed or in the case of a screen report, after the report is closed following viewing. This trigger can be used to update a global variable if u r returning the number of pages in a report. It is also used to delete temporary table used to print the report
Data Model Triggers
Formula Column, Group Filter, Parameter values
Layout Model Triggers

7. What are Format triggers?
Format triggers enable you to modify the display of objects dynamically at run time or to suppress display altogether
For Headings, for repeating frames, for field, for boilerplate object
To format a column based on certain criteria for example
i) To format the max (Sal) for particular department.
ii) To format the Sal column with a Dollar ($) prefix.
iii) To format Date formats….etc

8. What is Data Model?
Data Model is logically group of the Report Objects through query and Data model tools. Once query is compiled report automatically generates group. The queries build the groups ant then Groups are used to populate the report. The only function of queries in report is to create the groups. The Report Editor's Data Model view enables you to define and modify the data model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships. To create the query objects for your data model, you can use the Report Wizard, Data Wizard, or the Query tools in the tool palette.

9. What is Layout model?
Layout Model is to physically arrange Data model group objects on the Report. The Report Editor's Layout Model view enables you to define and modify the layout model objects for a report. In this view, objects and their property settings are represented symbolically to highlight their types and relationships.

10 What is Live Previewer?
Ans: The Live Previewer is a work area in which you can preview your report and manipulate the actual or live data at the same time. In the Live Previewer you can customize reports interactively, meaning that you can see the results immediately as you make each change.
To activate buttons in the Live Previewer, you must display the report output in the Runtime Previewer. In order to edit your report, such as changing column size, move columns, align columns insert page numbers, edit text, change colors, change fonts set format masks, insert field the Live Previewer must be in Flex Mode.

Access
Title
Viewing region
Rulers
Grid
Toolbar
Style bar
Tool palette
Status bar

Miscellaneous Transactions - Overview

Types of Miscellaneous Transactions

There are six types:

Miscellaneous receipt
Miscellaneous issue
Account receipt
Account issue
Account alias receipt
Account alias issue

Use of Miscellaneous Transactions

With a miscellaneous transaction you can issue material to or receive material from general ledger accounts in your current organization. This allows you to issue material to groups that are not inventory, receiving, or work in process such as a R&D group or an accounting department.

You can also make manual adjustments to the general ledger by receiving material from one account to inventory, and then issuing that material from inventory to another account.

You can use your user-defined transaction types. You can use this feature to issue items to individuals, departments, or projects; or to issue damaged items to expense accounts such as scrap Inventory.

You can perform the receipts for items that were acquired by means other than a purchase order from a supplier.

You can also use this feature to load all item on-hand quantities when you start implementing Oracle.

P2P and O2C: Procure to Pay and Order to Cash Cycles

Oracle has developed this ERP solution which truly covers these both cycles as well as many others. Oracle EBS comprises of the Standard Core Business Management

applications like General Ledger, Payables, Receivables, Purchasing, Order Management, Inventory, Discrete Manufacturing, Process Manufacturing , HRMS and many

more. The application mentioned are so integrated that it handles the beginning to end of both Assets and Liabilities. When refering to Assets its referring to applications like

Order Management and Receivables, and when refering to Liabilities its referring to Purchasing and Payables and both of these Assets and Liabilities are finally pushed and

calculated in Oracle General Ledger.

The base or the heart of Oracle EBS is Oracle General Ledger. we can say GL an intrinsic.

Procure to Pay:
Procure to Pay means Procuring Raw Materials required to manufacture the final or finished Goods to Paying the Supplier from whom the material was purchased. But this

is not just two steps. It involves many steps. Let’s see the steps and Oracle Application involved in performing those steps.

Oracle Purchasing: You enter Suppliers of different materials and products you want to purchase to manufacture a finished good that your organization plans to sell.
Oracle Purchasing: You prepare a Request for Quotation (RFQ) and send it to different suppliers to get the best and/or economical price for the product.
Oracle Purchasing: Suppliers sends their quotations and you upload those quotations in Oracle Purchasing to get the best three quotes and further to get the one best

quote.
Oracle Purchasing: You prepare a Purchase Order(PO) against the best RFQ to buy the goods from the supplier who quoted the suitable price and sends the PO to that

supplier
Oracle Purchasing: The supplier receives the confirmation of purchase from PO and ships the ordered goods. You receive the goods enter a Goods Received Note (GRN) in

Oracle Purchasing.

Oracle Inventory / Oracle Assets: It’s up to us whether we want to receive the goods at our head office or Inventory directly. In either case we move the received

goods to different Raw Material Inventory from Oracle Purchasing to Oracle Inventory and the Item Count increases. If the item is Asset Type then it will move to Oracle

Assets at the time of Invoice creation in Oracle Payables.
Oracle General Ledger: Once we move the goods to Oracle Inventory, it sends the Material Accounting to Oracle General Ledger.
Oracle Payables: After this the supplier sends the invoice for the purchased goods and we Enter or Match the invoice against the PO from Oracle Purchasing in Oracle

Payables. As said before, if the item is Asset in nature then it will move to Oracle Asset.
Oracle General Ledger: When we enter the invoice it means that we have created a Liability against that supplier and also we have recorded the expense incurred or asset

purchased. Oracle Payables sends the invoice accounting to Oracle General Ledger.
Oracle Payables: pay the invoice and settle the Liability.
Oracle General Ledger: The liability is settled and our cash movement account is updated.
Oracle Cash Management: As we pay the invoice Oracle Payables sends the payment information to Oracle Cash Management for Bank Reconciliation. Once reconciled,

Oracle Cash Management sends the updated Bank/Cash accounting entry to Oracle General Ledger.
Oracle General Ledger: our cash at bank is updated with actual balance.
Oracle Process Manufacturing(OPM) / Oracle Discrete Manufacturing(ODM): we start the manufacturing of our final product. Both OPM or ODM requests the different raw

materials from our inventory organizations and manufactures a finished good.
Oracle Inventory: As the raw materials are issued to OPM and ODM the inventory sends the issuing material accounting to General Ledger and decreases the Item Count

from the Raw Material Store. As the finished good is prepared, Oracle Inventory receives the finished good in Finished Good Store and increase the Item Count.
Now the final product is ready to be sold in the market and from here the O2C cycle starts.

Order to Cash Cycle:
Order to Cash means Customer’s Order Placing to Vendor’s Cash Receiving. When your final product is ready to be sold, you market it. The customer gets fascinated with

the marketing campaign and decides to buy your product and from here starts the O2C cycle.

Oracle Order Management: Customer places the order.
Oracle Order Management: You enter the customer order
Oracle Inventory: Check the available unit and the quantity ordered by the customer.
Oracle Order Management: You ship the product to customer site and decreases the Finished Goods inventory.
Oracle Receivables: The customer receives the product and you invoice the customer.
Oracle General Ledger: You record your revenue and receivables.
Oracle Receivables: The customer pays and you receive the cash/check.
Oracle Cash Management: Oracle Receivables sends the customer receipt for Bank Reconciliation. After reconciliation, Oracle Cash Management send the actual bank

balance or Oracle General Ledger.
Oracle General Ledger: You have the actual bank balance.
This is how the P2P and O2C cycle works, but this is not the only way, obviously there are many other applications with different cycles. This is one of them.

Post from Know oracle

Oracle Applications - A Simple Way

The below example explains a few of the important terms and concepts used in the Oracle E-Business Suite. This would be a good starting point for the beginners to better understand the concepts behind Oracle Applications.

Say Harry is the owner of a wholesale fruit shop. He buys various fruits like apples, oranges, mangos and grapes etc from farmers directly and sells them to retail shop owners and also to the direct customers.

The farmers are referred to as VENDORS/SUPPLIERS in Oracle Applications. Harry keeps track of all his vendors’ information like addresses, bank account and the amount he owes to them for the fruits that he bought etc, in a book named PAYABLES.

Harry gets an order from a retail shop owner of Fruit Mart, for a shipment of 11 bags of apples, 25 bags of oranges and 32 kgs of grapes. In Oracle Apps, bags and kgs are referred to as UOM (unit of measure), Fruit Mart is called CUSTOMER and the order is referred to as SALES ORDER. Harry maintains a book called ORDER MANAGEMENT where he writes down all the details of the SALES ORDERS that he gets from his customers.

Say the fruits have been shipped to the customer Fruit Mart. Harry now sends him the details like cost of each bag/fruit, the total amount that the customer has to pay etc on a piece of paper which is called INVOICE / TRANSACTION. Once the INVOICE has been sent over, the customer then validates this against the actual quantity of fruits that he received and will process the payments accordingly. The invoice amount could be paid as a single amount or could be paid in installments. Harry’s customer, Fruit Mart pays him in installments (partial payments). So Harry has to make a note of the details like date received, amount received, amount remaining, amount received for what goods/shipments/invoice etc, when Harry receives the payments. This detail is called RECEIPT, which will be compared to the invoice by Harry to find how much Fruit Mart has paid to him and how much has to be paid yet. This information is maintained in a book named RECEIVABLES to keep track of all the customers, their addresses (to ship the items), what and how much he has shipped to his customers and the amount his customers owe him etc.

Harry’s fruit business has begun to improve and has attracted more and more customers. As a result, Harry decided to buy a cold storage unit where he could stock more fruits. In Apps, this cold storage unit is known as WAREHOUSE and all the fruits are referred to as INVENTORY. Due to increase in customers, Harry needs to hire more people to help him out in his business without any hiccups. These workers are called EMPLOYEES. At the end of every month, Harry pays the salary for all his employees through Checks. These checks are nothing but PAYROLL in Apps.

At the end of every month, Harry prepares a balance sheet in a book called GENERAL LEDGER to determine how much profit/loss he got and keeps track of the money going out and going in.

As the business grows, it becomes impossible to record everything on a paper. To make everybody’s life easier, we have very good tools in the market, which help the business men to keep track of everything. One such tool is Oracle E-Business Suite.

Oracle Applications is not a single application, but is a collection of integrated applications. Each application is referred to as a module and has it own functionality trying to serve a business purpose.

Few of the modules are Purchasing, Accounts Payables, Accounts Receivables, Inventory, Order Management, Human Resources, General Ledger, Fixed Assets etc.

Here is a high level business use of various modules:

Oracle Purchasing handles all the requisitions and purchase orders to the vendors.
Oracle Accounts Payables handles all the payments to the vendors.
Oracle Inventory deals with the items you maintain in stock, warehouse etc.
Order Management helps you collect all the information that your customers order.
Oracle Receivables help you collect the money for the orders that are delivered to the customers.
Oracle Human Resources helps maintain the Employee information, helps run paychecks etc.
Oracle General Ledger receives information from all the different transaction modules or sub ledgers and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc. For Example: when you pay your employees that payment is reported back to General Ledgers as cost i.e money going out, when you purchase inventory items and the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items into your inventory, it is transferred to GL as money coming in, when your customer sends payment, it is transferred to GL as money coming in. So all the different transaction modules report to GL (General Ledger) as either “money going in” or “money going out”, the net result will tell you if you are making a profit or loss.

All the equipment, shops, warehouses, computers can be termed as ASSETS and they are managed by Oracle Fixed Assets.

There is a lot more in Oracle applications. This is the very basic explanation just to give an idea of the flow in ERP for the beginners.
Invoice
Receipt
Customer
Vendor
Buyer
Supplier
Purchase Order
Requisition
ACH: Account Clearance House

Sales Order
Pack Slip
Pick Slip
Drop Ship
Back Order
ASN: Advance Shipping Notice
ASBN: Advance Shipping Billing Notice
ATP: Available to Promise
Lot/Serial Number
DFF: Descriptive Flex Fields
KFF: Key Flex Fields
Value Sets
Organization
Business Unit
Multi Org
Folders
WHO Columns


Oracle Reports
Oracle Form
Workflow Builder
Toad
SQL Developer
SQL Navigator
Discoverer Reports
XML/BI Publisher
ADI: Application Desktop Integrator
Winscp
Putty

KnowOracle

Differentiates between a Party and Customer

In AR (Account receivables ) or TCA ( Trading community architecture ) , we usually comes across two common terms, party and customer. though both link each other still there are always confusion, Below is the difference between party and customer , hope this will be helpful

Points differentiates a Party and Customer which is as follows

Party

a) Prospective Customer and more relevant for CRM Purposes
b) No Business Transactions involved (Sales Order, Sales Invoice, Debit Memo, Credit Memo, Receipt etc.,)
c) A Party does not have account but have Sites
d) A Party can exist without Customer Record
e) A Party Record will not have record in following tables

HZ_CUST_ACCOUNTS
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUST_ACCOUNT_ROLES
HZ_CUST_ACCT_RELATE_ALL

Customer

a) A Customer which is used both in CRM as well as in OM,Financials or any other module.Example (A Sales Order in OM or Invoice in Receivables cannot be created without creating a Customer record for the Party).
b) A Business Transaction like a Sales Order, Invoice,Debit Memo,Credit Memo,Receipt can be created.
c) A Customer will have account and as well as Sites.
d) A Party record is must to create a Customer Record linked through party_id.
e) A Customer Record will have records in following tables

HZ_CUST_ACCOUNTS
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUST_ACCOUNT_ROLES
HZ_CUST_ACCT_RELATE_ALL

with reference to party_id column.

Run the Party and Customer Diagnostic Report to know more about the table information.

Important Note:

For Example Party ‘A’ has ‘B’ and ‘C’ two Customer accounts and party ‘X’ has ‘Y’ and ‘Z’ two customer accounts. If you want to merge Customer Accounts ‘B’ and ‘C’ with ‘Y’ and ‘Z’, then first we need to perform Party merge and then perform the customer merge. It operates on the simple logic, First Parent records need to be merged before merging the child records

KnowOracle

SQL Script to list object privileges granted to a particular user

Login to database as sys / system / or any user who had dba privileges and run below command

select grantor 'granted' privilege 'on' table_name 'owned by' owner 'to' grantee from dba_tab_privs where grantee like upper('&GRANTEE_NAME') order by privilege;
where GRANTEE_NAME is the user you want to find out the privileges.

Even though the view name is dba_tab_privs, it lists privileges on all objects including tables, views, procedures, etc.,

Sample Output

APPS granted DELETE on FND_USER_RESP_GROUPS owned by APPS to XXXXXX
APPLSYS granted DELETE on FND_USER owned by APPLSYS to XXXXXX;

OraclePitStop

AR Receipts API Single Insert Creation Script

1.Creating a RECEIPT

DECLARE
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_cr_id NUMBER;

BEGIN
ar_receipt_api_pub.create_cash
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_currency_code => 'USD',
p_amount => 200,
p_receipt_number => 'XX_TEST1',
p_receipt_date => SYSDATE,
p_gl_date => SYSDATE,
p_customer_id => Customer id ,
p_customer_bank_account_id => ,
p_customer_site_use_id => ,
p_receipt_method_id => ,
p_cr_id => l_cr_id );

DBMS_OUTPUT.put_line ('Message count ' l_msg_count);
DBMS_OUTPUT.put_line ('Status ' l_return_status);

IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data);
ELSIF
l_msg_count > 1
THEN LOOP
l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL
THEN EXIT;
END IF;
DBMS_OUTPUT.put_line ('Message' '---' ' ---' l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
END;
/

2. Applying to an Invoice

DECLARE
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);

BEGIN
ar_receipt_api_pub.APPLY
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_false,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cash_receipt_id => ,
p_customer_trx_id => ,
p_applied_payment_schedule_id => ,
p_amount_applied => ,
p_show_closed_invoices => 'Y',
p_apply_date => SYSDATE,
p_apply_gl_date => SYSDATE,
p_line_number => 1 );

DBMS_OUTPUT.put_line ('Message count ' l_msg_count);
DBMS_OUTPUT.put_line ('Status ' l_return_status);

IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data);
ELSIF l_msg_count > 1
THEN LOOP
l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF l_msg_data IS NULL
THEN
EXIT;
END IF;

DBMS_OUTPUT.put_line ('Message' '---' ' ---' l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
END;

3. UNApplying a receipt to an Invoice

DECLARE
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_count NUMBER;
l_cash_receipt_id NUMBER;
l_msg_data_out VARCHAR2 (240);
l_mesg VARCHAR2 (240);
p_count NUMBER;

BEGIN
arp_standard.enable_debug;
arp_standard.enable_file_debug ('/usr/tmp', 'Api_Create.log');

ar_receipt_api_pub.unapply
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_cash_receipt_id => &cash_receipt_id,
p_applied_payment_schedule_id => &applied_payment_schedule_id,
p_reversal_gl_date => '&reversal_gl_date' );

DBMS_OUTPUT.put_line ('Message count ' l_msg_count);
DBMS_OUTPUT.put_line ('Cash Receipt ID ' l_cash_receipt_id);

IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data);
ELSIF l_msg_count > 1
THEN LOOP
p_count := p_count + 1;
l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

IF l_msg_data IS NULL
THEN EXIT;
END IF;

DBMS_OUTPUT.put_line ('Message' p_count ' ---' l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
END;
/

4.CREATE AND APPLY A Receipt

DECLARE
l_return_status VARCHAR2 (1);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (240);
l_count NUMBER;
l_cash_receipt_id NUMBER;
l_msg_data_out VARCHAR2 (240);
l_mesg VARCHAR2 (240);
p_count NUMBER;

BEGIN
DBMS_OUTPUT.put_line ('Start');
arp_standard.enable_debug;

ar_receipt_api_pub.create_and_apply
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
p_currency_code => 'USD',
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_amount => ,
p_receipt_number => ' ',
p_receipt_date => SYSDATE,
p_gl_date => SYSDATE,
p_customer_id => ,
p_customer_bank_account_id => ,
p_customer_site_use_id => ,
p_receipt_method_id => ,
p_trx_number => ' ',
p_cr_id => l_cash_receipt_id ); D

BMS_OUTPUT.put_line ('Return_status ' l_return_status);
DBMS_OUTPUT.put_line ('Message count ' l_msg_count);
DBMS_OUTPUT.put_line ('Cash Receipt ID ' l_cash_receipt_id);

IF l_msg_count = 1
THEN
DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data);
ELSIF
l_msg_count > 1
THEN LOOP
p_count := p_count + 1;

l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL
THEN
EXIT;
END IF;
DBMS_OUTPUT.put_line ('Message' p_count ' ---' l_msg_data);
END LOOP;
END IF;
arp_standard.disable_debug;
END;
/

5. Miscellanous receipt Creation

DECLARE

l_return_status VARCHAR2 (1);

l_msg_count NUMBER; l_msg_data VARCHAR2 (240); l_count NUMBER; l_cash_receipt_id NUMBER; l_msg_data_out VARCHAR2 (240); l_mesg VARCHAR2 (240); p_count NUMBER; l_receipt_number VARCHAR (10);BEGIN l_receipt_number := '123'; DBMS_OUTPUT.put_line ('Start'); arp_standard.enable_debug; arp_standard.enable_file_debug ('/usr/tmp', 'Api_Create.log'); ar_receipt_api_pub.create_misc (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_true, p_validation_level => fnd_api.g_valid_level_full, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_currency_code => 'USD', p_amount => &amount, p_receipt_date => '&receipt_date', p_gl_date => '&gl_date', p_receipt_method_id => &receipt_method_id, p_activity => 'Interest Income', p_misc_receipt_id => l_cash_receipt_id, p_receipt_number => l_receipt_number ); DBMS_OUTPUT.put_line ('Message count ' l_msg_count); DBMS_OUTPUT.put_line ('Cash Receipt ID ' l_cash_receipt_id); DBMS_OUTPUT.put_line ('Status ' l_return_status);
IF l_msg_count = 1 THEN DBMS_OUTPUT.put_line ('l_msg_data ' l_msg_data); ELSIF l_msg_count > 1 THEN LOOP p_count := p_count + 1; l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL THEN EXIT; END IF;
DBMS_OUTPUT.put_line ('Message' p_count ' ---' l_msg_data); END LOOP; END IF;
arp_standard.disable_debug;END;/

6.REVERSING a Receipt

DECLARE

l_return_status VARCHAR2 (1);

l_msg_count NUMBER; l_msg_data VARCHAR2 (240); l_count NUMBER; l_cash_receipt_id NUMBER; l_msg_data_out VARCHAR2 (240); l_mesg VARCHAR2 (240); p_count NUMBER;BEGIN arp_standard.enable_debug; arp_standard.enable_file_debug ('/usr/tmp', 'Api_Create.log'); ar_receipt_api_pub.REVERSE (p_api_version => 1.0, p_init_msg_list => fnd_api.g_true, p_commit => fnd_api.g_true, p_validation_level => fnd_api.g_valid_level_full, x_return_status => l_return_status, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_receipt_number => '&receipt_number', p_reversal_category_name => 'Reverse Payment', --values can be modified. p_reversal_reason_name => 'Nsf' -- values can be modified. ); DBMS_OUTPUT.put_line ('Message count ' l_msg_count); DBMS_OUTPUT.put_line ('Status ' l_return_status);
IF l_msg_count = 1 THEN DBMS_OUTPUT.put_line ( 'l_msg_data A ' RTRIM (LTRIM (l_msg_data)) '****' ); ELSIF l_msg_count > 1 THEN LOOP p_count := p_count + 1; l_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);
IF l_msg_data IS NULL THEN EXIT; END IF;
DBMS_OUTPUT.put_line ('Message' p_count ' ---' l_msg_data); END LOOP; END IF;
arp_standard.disable_debug;END;/

SQL Query to list running concurrent requests

select
sess.sid,sess.serial#,
oracle_process_id OS_PROCESS_ID,
fusr.description user_name ,
fcp.user_concurrent_program_name progName,
to_char(actual_Start_date,'DD-MON-YYYY HH24:MI:SS') StartDate,
request_id RequestId,
(sysdate - actual_start_date)*24*60*60 ETime
from
fnd_concurrent_requests fcr,
fnd_concurrent_programs_tl fcp,
fnd_user fusr,
v$session sess
where
fcp.concurrent_program_id = fcr.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.language = 'US'
and fcr.phase_code = 'R'
and fcr.status_code = 'R'
and fcr.requested_by = fusr.user_id
and fcr.oracle_session_id = sess.audsid (+)
order by 5 DESC

How to compile a single jsp file in Oracle Applications 11i/R12

You can follow below steps to compile a single jsp file (seeded or custom) in Oracle Applications 11i/R12.

1. Copy the jsp file to $COMMON_TOP/webapps/oacore/html (this location translates to $OA_HTML).

Placing the file under $OA_HTML enables end users to access the file using the url http://hostname.domain:port/OA_HTML/[jspfile.jsp]
2. export PATH=$PATH:$FND_TOP/patch/115/bin
3. ojspCompile.pl - -compile -s ‘[jspfile.jsp]‘ (no space between the two dashes before compile)

No need to bounce apache server after compiling the file to access the new content.

Below is the command line help for ojspCompile.pl command

ojspCompile.jsp command help

syntax: ./ojspCompile.pl COMMAND {ARGS}
COMMAND –compile update dependency, compile delta
–create rebuild entire dependency file
-delta.out update dependency, list delta to file
-dep.out update dependency, output heirarchy to file

ARGS -s matching condition for JSPs filenames
-p number of parallel compilations
-log to override logfile from ojspCompile.conf You are
recommended to set the log file location
outside of any network file system shared (NFS) area/drive.
-conf to override ojspCompile.conf
–retry retry previously failed compilation attempts
–flush forces recompilation of all parent JSPs
–quiet do not provide an actively running progress meter
–fast instantly fail jsps that are *possibly* invalid

example1: ojspCompile.pl –compile -s ‘jtf%’ -p 20 –retry
example2: ojspCompile.pl –compile -s ‘jtflogin.jsp,jtfavald.jsp’ –flush
example3: ojspCompile.pl –compile –fast –quiet

Post from OraclePitStop

SQLLOAD with multi-byte characters when data is in Excel

To SQLLOAD with multi-byte characters when data is in Excel

1.Add a header line at the top of the page (if one doesn’t exist)
2.Save as a Unicode text file
3.Open the saved Unicode file in Word
4.Remove any tabs. Edit->Replace (you may have to cut and paste a tab in from notepad to do
this) – Replace All
5.The delimiter used above may be in double quotes - remove the quotes. For instance,
Edit->Replace “,” , replace all
6.Save the file as plain text, then Unicode (UTF-8). Note: Saving as just Unicode will not work
7.Binary ftp the file to Unix only using command line
8.Make sure to specify Options (skip =1) in your ctl file to skip the header

Note: the reason for the ensuring there is a header is that some characters are inserted at the beginning of the first line. If we make it the header and then skip it anyway, we don’t need to worry about deleting those characters.

Sample datafile:

Site, Rep
1000,仕切値について,
1001,仕切値について,
1002,仕切値について,
1003,仕切値について,

Sample CTL file (characterset should be optional):

-------------------------------------------------------------------------
--
-- Description: SQL*Loader control file to load data from level 1
--
-- Date:
-------------------------------------------------------------------------
OPTIONS (skip = 1)
Load Data
--Infile 'load2.txt'
Characterset UTF8
REPLACE
INTO TABLE sims.LEVEL1_SALESREP_UPD
Fields terminated BY ','
optionally enclosed BY '"'
trailing nullcols
(
site_number,
lv1_rep
)

Sample load command: sqlldr userid=apps/ control=load.ctl data=load3.txt

General Sql Queries

select decode( sign( &num ), -1, 'Negative ', 0, 'Zero', NULL )
decode( sign( abs(&num) ), +1, to_char( to_date( abs(&num),'J'),'Jsp') )
from dual

SELECT LPAD( MONTH, 20-(20-LENGTH(MONTH))/2 ) MONTH,"Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat"
FROM (SELECT TO_CHAR(dt,'fmMonthfm YYYY') MONTH,TO_CHAR(dt+1,'iw') week,
MAX(DECODE(TO_CHAR(dt,'d'),'1',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sun",
MAX(DECODE(TO_CHAR(dt,'d'),'2',LPAD(TO_CHAR(dt,'fmdd'),2))) "Mon",
MAX(DECODE(TO_CHAR(dt,'d'),'3',LPAD(TO_CHAR(dt,'fmdd'),2))) "Tue",
MAX(DECODE(TO_CHAR(dt,'d'),'4',LPAD(TO_CHAR(dt,'fmdd'),2))) "Wed",
MAX(DECODE(TO_CHAR(dt,'d'),'5',LPAD(TO_CHAR(dt,'fmdd'),2))) "Thu",
MAX(DECODE(TO_CHAR(dt,'d'),'6',LPAD(TO_CHAR(dt,'fmdd'),2))) "Fri",
MAX(DECODE(TO_CHAR(dt,'d'),'7',LPAD(TO_CHAR(dt,'fmdd'),2))) "Sat"
FROM ( SELECT TRUNC(SYSDATE,'y')-1+ROWNUM dt FROM all_objects WHERE
ROWNUM<= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y')) GROUP BY TO_CHAR(dt,'fmMonthfm YYYY'), TO_CHAR( dt+1, 'iw' )) ORDER BY TO_DATE( MONTH, 'Month YYYY' ), TO_NUMBER(week)

Counts

Provides a count of all (distinct) values in a particular column or table. The column can be either alpha or numeric. Null values in the column are included in the count.

SELECT catalog_nbr, COUNT (*) as Num_of_Stu
FROM XX_STUDENT
WHERE acad_group = 'TCHE'
AND acad_career = 'UGRD'
AND stdnt_enrl_status = 'E'
AND subject = 'FSOS'
GROUP BY catalog_nbr

Sums

Provides a sum of all (distinct) values in a particular column. The column must be numeric. Null values in the column are not included in the sum

SELECT acad_career, subject, SUM (unt_taken) units_taken
FROM XX_STUDENT
WHERE subject = 'SOIL'
GROUP BY acad_career, subject

Having

Use the HAVING clause to restrict which groups of rows defined by the GROUP BY clause are returned by the query

SELECT catalog_nbr, class_section, COUNT (*) AS num_of_stu
FROM XX_STUDENT
WHERE acad_group = 'TCHE'
AND acad_career = 'UGRD'
AND stdnt_enrl_status = 'E'
AND subject = 'FSOS'
AND component_main = 'DIS'
GROUP BY catalog_nbr, class_section
HAVING COUNT (*) > 50

Case/Decode/NVL

Case and Decode statements both perform procedural logic inside a SQL statement without having to use PL/SQL.

SELECT DISTINCT a.NAME,
CASE
WHEN a.emailid_2 IS NULL THEN a.emailid_1
ELSE a.emailid_2
END
email_add
FROM XX_STUDENT a, XX_STUDENT1 b
WHERE a.emplid = b.emplid AND b.acad_prog = '32UGR'

SELECT DISTINCT a.NAME,
DECODE (a.emailid_2, NULL, a.emailid_1, a.emailid_2) email_add
FROM XX_STUDENT a, XX_STUDENT1 b
WHERE a.emplid = b.emplid AND b.acad_prog = '32UGR'

SELECT DISTINCT a.NAME,
NVL (a.emailid_2, a.emailid_1) email_add
FROM XX_STUDENT a, XX_STUDENT1 b WHERE a.emplid = b.emplid AND b.acad_prog = '32UGR'

All of these queries will return a list of student names with there secondary email addresses unless they didn’t report a secondary address, then it will return their primary email address

It is best to use the CASE statement when comparing ranges or more complex logic.

SELECT a.NAME,
(CASE
WHEN a.vac_hrs_taken_ytd <= 40 THEN 'GET A LIFE' WHEN a.vac_hrs_taken_ytd BETWEEN 41 AND 100 THEN 'NEED A BREAK?' WHEN a.vac_hrs_taken_ytd >= 101 THEN 'WELL RESTED'
END) mental_wellbeing
FROM XX_STUDENT a
WHERE a.deptid = '831A'
AND a.fisc_yr = '2003'
AND a.pay_period = '06'
AND a.empl_status = 'A'
ORDER BY 2

Rollup

The use of a ROLLUP clause in the GROUP BY part of the SQL expression displays subtotals and grand totals depending on it’s use.

SELECT NVL (catalog_nbr, 'GRAND_TOTAL') catalog_nbr, class_section,
SUM (unt_taken)
total_units, COUNT (*) num_of_stu
FROM XX_STUDENT WHERE acad_group = 'TCHE'
AND acad_career = 'UGRD'
AND stdnt_enrl_status = 'E'
AND subject = 'FSOS'
GROUP BY ROLLUP (catalog_nbr, class_section)

Inline Views

You can use a SQL statement in the FROM clause of a SQL statement. This is called a inline view. Oracle treats the data set that is returned from the inline view as if it were a table.

SELECT a.NAME, a.office1_phone
FROM XX_STUDENT a,
(SELECT x.emplid
FROM XX_STUDENT1 x
WHERE x.deptid = '831A'
AND x.status_flg = 'C'
AND x.job_terminated ='N') b
WHERE a.emplid = b.emplid;

Round

Returns a number rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer

SELECT ROUND(AVG(ENG_ACT_SCORE), 4)
FROM XX_STUDENT
WHERE ENG_ACT_SCORE != 0

syntax how to set comments on a table or a column

syntax how to set comments on a table or a column

If we want to give a comment on a table :

COMMENT ON TABLE IS 'the comments should go here';

If we want to give a comment on a column : COMMENT ON TABLE COLUMN table.columnIS 'the comments should go here';

The entries of these comments on the table goes in the following tables:
For tables:
select * from all_tab_comments where table_name ='table_name'
(or)
select * from user_tab_comments where table_name = 'table_name'

For Columns

select * from all_col_commentswhere table_name = 'table_name'
(or)
select * from user_col_commentswhere table_name = 'table_name'

Few Database tables useful for Apps Developer

Few Database tables useful for Apps Developer

ad_bugs
ad_applied_patches
select * from V$SESSION where TERMINAL like 'XXXX'
select * from V$ACCESS where OBJECT like 'XX_%'
ALTER SYSTEM KILL SESSION 'Session_id,Serial'
select * from all_indexes

List all indexed columns for a given table

break on index_name skip 1
col index_name format a30
col uniuenes format a12
col column_name format a30
prompt Indexes for table: &&1
select c.index_name, i.uniqueness, c.column_namefrom user_indexes i, user_ind_columns cwhere i.index_name = c.index_name and i.table_name = upper('&&1')order by c.index_name, c.column_position
/

Print Oracle Apps versions

SELECT substr(a.application_short_name, 1, 5) code,substr(t.application_name, 1, 50) application_name, p.product_version versionFROM fnd_application a, fnd_application_tl t, fnd_product_installations pWHERE a.application_id = p.application_idAND a.application_id = t.application_idAND t.language = USERENV('LANG')

Delete concurrent program from backend

begin
fnd_program.delete_program('short name of the concurrent program to be deleted','Schema');
fnd_program.delete_executable('short name of the concurrent program to be deleted','Schema');
commit;
end;

Concurrent program Queries

Responsibilities to which assigned:

SELECT DISTINCT * FROM fnd_responsibility_tl
WHERE responsibility_id IN ( SELECT responsibility_id FROM fnd_responsibility_vl WHERE request_group_id IN ( SELECT request_group_id FROM fnd_request_group_units WHERE request_unit_id = (SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name = 'XXXX')) AND end_date IS NULL) AND "LANGUAGE" LIKE 'US' ORDER BY responsibility_name

Application name:

SELECT * FROM fnd_application "application name"
WHERE application_id IN (SELECT application_id FROM fnd_request_group_units WHERE request_unit_id=(SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name='XXX'))

concurrent program short name:

SELECT * FROM fnd_concurrent_programs
WHERE concurrent_program_id=(SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name='XXX')

Execution file name:

SELECT * FROM fnd_executables
WHERE executable_id=(SELECT executable_id FROM fnd_concurrent_programs WHERE concurrent_program_id=(SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name='XXX'))

Requests groups:

SELECT * FROM fnd_request_groups "requests groups"
WHERE request_group_id IN (SELECT request_group_id FROM fnd_request_group_units WHERE request_unit_id=(SELECT DISTINCT concurrent_program_id FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name='XXXX'))

Appliocation Menus , Submenus assigned to User

select r.responsibility_name,f.user_menu_name, me.prompt "Main_Prompt",me.description "Main_Desc", metl.prompt "sub_prompt", metl.description "sub_desc" from apps.fnd_user fu, apps.fnd_user_resp_groups urg, apps.fnd_menus_vl f, apps.fnd_responsibility fr, apps.fnd_responsibility_tl r, apps.fnd_menus fm, apps.fnd_menu_entries fme, apps.fnd_menu_entries_tl me, apps.fnd_menus fm2, apps.fnd_menu_entries fme2, apps.fnd_menu_entries_tl metl where 1=1and fr.menu_id=f.menu_id AND fu.user_id = urg.user_id and (urg.responsibility_id = fr.responsibility_id and urg.responsibility_application_id = fr.application_id) and (fr.responsibility_id = r.responsibility_id and fr.application_id = r.application_id) and (fme.menu_id = me.menu_id and fme.entry_sequence = me.entry_sequence) and fr.menu_id = fm.menu_id and fm.menu_id = fme.menu_id and fme.sub_menu_id = fm2.menu_id and fm2.menu_id = fme2.menu_id and (fme2.menu_id = metl.menu_id and fme2.entry_sequence = metl.entry_sequence) and fr.RESPONSIBILITY_ID=XXXXand metl.language='US'and me.language='US'order by urg.description