NALAM Oracle Apps Technical : oracle apps faq

Showing posts with label oracle apps faq. Show all posts

Friday, 24 January 2014

for intilizing before calling any public or private API
====================================================================
select fnd.user_id,
       fresp.responsibility_id,
       fresp.application_id
from
       fnd_user fnd,
       fnd_responsibility_tl fresp
where
       fnd.user_name = 'OPERATIONS'
AND fresp.responsibility_name ='Payables, Vision Operations (USA)'

Run the following query to find out who are the Oracle Apps Users currently Logged into the application.
========================================================================================================

SELECT DISTINCT icx.session_id,
                  icx.user_id,
                  fu.user_name,
                  fu.description
    FROM icx_sessions icx,
         fnd_user fu
   WHERE
             disabled_flag != 'Y'
         AND icx.pseudo_flag = 'N'
         AND (last_connect +
              DECODE (fnd_profile.VALUE ('ICX_SESSION_TIMEOUT'),
                      NULL, limit_time,
                      0   , limit_time,
                      fnd_profile.VALUE ('ICX_SESSION_TIMEOUT')/60) / 24) > SYSDATE
         AND icx.counter < limit_connects
         AND icx.user_id = fu.user_id;

Query to find all responsibilities of a user
==================================================================

SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
WHERE furg.user_id             = fu.user_id
   AND furg.responsibility_id   = frt.responsibility_id
   AND fr.responsibility_id     = frt.responsibility_id
   AND fa.application_id        = fat.application_id
   AND fr.application_id        = fat.application_id
   AND frt.language             = USERENV('LANG')
   AND UPPER(fu.user_name)      = UPPER('sandeep') -- <change it>
ORDER BY frt.responsibility_name;

List of users who were granted particular function in oracle apps R12 db
=====================================================================================

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

Supplier Information Query for particular org_id
=================================================

select
        asp.vendor_name "Supplier Name" ,
        ass.vendor_site_code "site name" ,
        ass.address_line1 ,
        ass.country ,
        person.person_last_name ,
        pty_rel.primary_phone_number ,
        pty_rel.email_address
FROM ap_suppliers asp ,
        ap_supplier_sites_all ass ,
        ap_supplier_contacts apsc ,
        hz_parties person ,
        hz_parties pty_rel,
        hr_operating_units hou
WHERE
       ass.vendor_id = asp.vendor_id
AND apsc.per_party_id = person.party_id
AND apsc.rel_party_id = pty_rel.party_id
AND ass.org_id = hou.organization_id
AND apsc.org_party_site_id = ass.party_site_id
AND asp.vendor_name = nvl(:P_SUPPLIER_NAME,asp.VENDOR_NAME)
and   hou.organization_id = fnd_profile.value('org_id');

=========================================================================
SELECT   NAME "WF_NAME",
            DISPLAY_NAME,
            CUSTOM_LEVEL
FROM
            WF_ITEM_TYPES_TL
WHERE
           CUSTOM_LEVEL>0
AND      PROTECT_LEVEL>0
ORDER BY custom_level
=========================================================================

select distinct hbg.BUSINESS_GROUP_NAME "BGNAME",
       hbg.DATE_FROM "BGST.DATE",
       hbg.BUSINESS_GROUP_ID "bugid",
       hl.ADDRESS_LINE_1 "DOO RAND LINE ",
       hl.TOWN_OR_CITY "CITY",
       hou.NAME "opuname",
       hou.DATE_FROM "startdate",
       gl.NAME "glname",
       gl.CURRENCY_CODE "currency",
       gpt.USER_PERIOD_TYPE "claender",
       xep.NAME "LENAME",
       ood1.ORGANIZATION_NAME "INV Name",
       ood1.USER_DEFINITION_ENABLE_DATE "start date",
       msi.SECONDARY_INVENTORY_NAME "sub INV name",
       mil.SEGMENT1 "row",
       mil.SEGMENT2 "RACK",
       mil.SEGMENT3 "box"
from   HRFV_BUSINESS_GROUPS hbg,
       HR_LOCATIONS hl,
       HR_OPERATING_UNITS hou,
       GL_LEDGERS gl,
       GL_PERIOD_TYPES gpt,
       XLE_ENTITY_PROFILES xep,
       MTL_PARAMETERS mp,
       org_organization_definitions ood,
       org_organization_definitions ood1,
       MTL_SECONDARY_INVENTORIES msi,
       MTL_ITEM_LOCATIONS mil
WHERE hbg.BUSINESS_GROUP_NAME=:BUSINESS_GROUP_NAME
and   hl.LOCATION_ID=hbg.LOCATION_ID
and   hou.BUSINESS_GROUP_ID=hbg.BUSINESS_GROUP_ID
and   gl.LEDGER_ID=hou.SET_OF_BOOKS_ID
and   gpt.PERIOD_TYPE=gl.ACCOUNTED_PERIOD_TYPE
and   xep.LEGAL_ENTITY_ID=hou.DEFAULT_LEGAL_CONTEXT_ID
and   ood.ORGANIZATION_ID=hbg.BUSINESS_GROUP_ID
and   mp.ORGANIZATION_ID=ood.ORGANIZATION_ID
and   ood1.ORGANIZATION_ID=mp.MASTER_ORGANIZATION_ID
and   msi.ORGANIZATION_ID=ood.ORGANIZATION_ID;
AND   mil.ORGANIZATION_ID=msi.ORGANIZATION_ID;
--and   ood.ORGANIZATION_ID=ml.ORGANIZATION_ID
--and   fifs.ID_FLEX_NUM=gl.CHART_OF_ACCOUNTS_ID
gcck.CONCATENATED_SEGMENTS "COA"
       --fifs.ID_FLEX_CODE "COA"
gl_code_combinations_kfv gcck
       --FND_ID_FLEX_STRUCTURES fifs
gcck.CHART_OF_ACCOUNTS_ID=gl.CHART_OF_ACCOUNTS_ID;

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

SELECT hbg.BUSINESS_GROUP_NAME "BGNAME",
       hbg.DATE_FROM "BGST.DATE",
       hl.ADDRESS_LINE_1 "DOO RAND LINE ",
       hl.TOWN_OR_CITY "CITY"
       --gl.CURRENCY_CODE "CURRENCY",
       --gl.NAME "LEDGERNAME"
FROM HRFV_BUSINESS_GROUPS hbg,
     HR_LOCATIONS hl
     --GL_LEDGERS gl,
     --HR_OPERATING_UNITS hou
WHERE hbg.BUSINESS_GROUP_NAME=:BUSINESS_GROUP_NAME
AND   hl.LOCATION_ID=hbg.LOCATION_ID;
--AND   hou.BUSINESS_GROUP_ID=hbg.BUSINESS_GROUP_ID;
--AND   gl.LEDGER_ID=hou.SET_OF_BOOKS_ID;

PARTICULAR USER INFORMATION DETAILS
======================================================================

SELECT distinct fu.USER_NAME "username",
       fu.DESCRIPTION "user DESCR",
       TO_CHAR(fu.START_DATE,'DD-MON-YYYY') "startdate",
       TO_CHAR(nvl(fu.END_DATE,'31-DEC-2099')) "ENDINGDATE",
       frt.RESPONSIBILITY_NAME "res name",
       fat.APPLICATION_NAME "APP NAME",
       fm.MENU_NAME "menugroupname",
       fdg.DATA_GROUP_NAME "datagroupname",
       frg.REQUEST_GROUP_NAME "requestgroupname",
       TO_CHAR(furgd.START_DATE,'DD-MON-YYYY') "userressdate",
       TO_CHAR(nvl(furgd.END_DATE,'31-DEC-2099')) "userresenddate"
FROM   FND_USER fu,
       FND_MENUS fm,
       FND_DATA_GROUPS fdg,
       FND_REQUEST_GROUPS frg,
       FND_USER_RESP_GROUPS_DIRECT furgd,
       FND_APPLICATION_TL fat,
       FND_APPLICATION fa,
       FND_RESPONSIBILITY fr,
       FND_RESPONSIBILITY_TL frt
WHERE fu.USER_NAME=nvl('&username',fu.USER_NAME)
AND    fu.USER_ID=furgd.USER_ID
AND    frt.RESPONSIBILITY_ID=furgd.RESPONSIBILITY_ID
AND    fat.APPLICATION_ID=frt.APPLICATION_ID
AND    fr.RESPONSIBILITY_ID=frt.RESPONSIBILITY_ID
AND    fm.MENU_ID=fr.MENU_ID
AND    fdg.DATA_GROUP_ID=fr.DATA_GROUP_ID
AND    frg.REQUEST_GROUP_ID=fr.REQUEST_GROUP_ID;

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

SELECT hl.LOCATION_CODE "LOCCODE",
       hl.DESCRIPTION   "DESCN",
       ft.NLS_TERRITORY "COUNTRY",
       hl.ADDRESS_LINE_1 "DOORNO",
       hl.ADDRESS_LINE_2 "COLONY",
       hl.LOC_INFORMATION16 "STATE",
       hl.LOC_INFORMATION15 "DISTRIC",
       hl.POSTAL_CODE        "PINCODE",
         CASE
         WHEN hl.INACTIVE_DATE is null then
         'ACTIVE'
         WHEN hl.INACTIVE_DATE > sysdate then
         'ACTIVE'
         ELSE
         'INACTIVE'
         END
         "ACTIVE STATUS"
FROM HR_LOCATIONS hl,
      FND_TERRITORIES ft
WHERE hl.LOCATION_CODE='HR_AP'
AND   ft.TERRITORY_CODE=hl.COUNTRY;

CASE & DECODE
================================================

SELECT DECODE(DEPTNO,10,'TEN',20,'TWENTY','OTHERS') DEPTNO,DNAME,LOC   FROM DEPT D

SELECT
CASE
WHEN DEPTNO <=10 THEN
'TEN'
WHEN DEPTNO>= 20 THEN
'TWENTY'
ELSE
'OTHERS'
END "DEPTNO",DNAME,LOC
FROM DEPT

PARTICULAR USER HAVE RESPONSIBILTY ASSIGN OR NOT
====================================================================

select   usr.user_id,
         usr.user_name,
         res.RESPONSIBILITY_ID,
         res.RESPONSIBILITY_NAME
from
        apps.FND_USER usr,
        apps.FND_RESPONSIBILITY_TL res,
        apps.FND_USER_RESP_GROUPS grp
where
     upper(res.RESPONSIBILITY_NAME) like upper('%' || NVL('&EnterRespName', 'INV')||'%')
     and upper(res.RESPONSIBILITY_NAME) NOT like '%AX%'
     and upper(res.RESPONSIBILITY_NAME) NOT like '%OPM%'
     and grp.responsibility_id = res.responsibility_id
     and grp.user_id = usr.user_id
     and user_name ='OPERATIONS'

query for various output of concurrent managers
============================================================
PROMPT Programs and Managers
PROMPT Provide various output of concurrent managers related to a specific program.
PROMPT In this case using default of Item Supply/Demand program.
SELECT
fcq.processor_application_id, fcp.concurrent_program_name,
fr.responsibility_id, fr.responsibility_key, fr.data_group_id, fr.request_group_id,
fr.application_id, fa.application_short_name,
fcq.concurrent_queue_id, fcq.CONCURRENT_QUEUE_NAME,
fcq.MIN_PROCESSES, fcq.TARGET_PROCESSES, fcq.TARGET_NODE, fcq.SLEEP_SECONDS, fcq.CONTROL_CODE, fcq.DIAGNOSTIC_LEVEL,
fcpr.*
FROM fnd_application fa,
fnd_concurrent_programs fcp,
fnd_conc_processor_programs fcpp,
fnd_responsibility fr,
fnd_concurrent_queues fcq,
fnd_concurrent_processes fcpr
WHERE fcq.processor_application_id = fcpp.processor_application_id
AND fcq.concurrent_processor_id = fcpp.concurrent_processor_id
AND fcpp.concurrent_program_id = fcp.concurrent_program_id
AND fcpp.program_application_id = fcp.application_id
AND fcp.application_id = fa.application_id
AND fcp.concurrent_program_name = NVL('&EnterProgramShortName', 'INXDSD')
AND fr.application_id = 401
AND fr.data_group_id = fcq.data_group_id
AND fcq.manager_type = '3'
AND fcpr.concurrent_queue_id = fcq.concurrent_queue_id
AND fcpr.queue_application_id = fcq.application_id
-- AND fcpr.process_status_code = 'A'
AND fcpr.instance_number = userenv('instance')
ORDER BY dbms_random.random;

the query to list concurrent program name with its parameter, values set and default value/type
==========================================================
SELECT fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , par.column_seq_num
      , par.end_user_column_name
      , par.form_left_prompt prompt
      , par.enabled_flag
      , par.required_flag
      , par.display_flag
      , par.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , par.DEFAULT_VALUE
FROM   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl par
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND    fcpl.user_concurrent_program_name = :conc_prg_name
AND    fcpl.LANGUAGE = 'US'
AND    par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND    ffvs.flex_value_set_id = par.flex_value_set_id
AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND    flv.lookup_code(+) = par.default_type
AND    flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY par.column_seq_num

find the list of responsibilities to which the programs is attached to
==============================================================
SELECT frt.responsibility_name,
       frg.request_group_name,
       frgu.request_unit_type,
       frgu.request_unit_id,
       fcpt.user_concurrent_program_name
FROM
      fnd_Responsibility fr,
      fnd_responsibility_tl frt,
      fnd_request_groups frg,
      fnd_request_group_units frgu,
      fnd_concurrent_programs_tl fcpt
WHERE frt.responsibility_id = fr.responsibility_id
AND frg.request_group_id = fr.request_group_id
AND frgu.request_group_id = frg.request_group_id
AND fcpt.concurrent_program_id = frgu.request_unit_id
AND frt.language = USERENV('LANG')
AND fcpt.language = USERENV('LANG')
AND fcpt.user_concurrent_program_name = :conc_prg_name
ORDER BY 1,2,3,4

Query to find Request Set
==========================================
SELECT frt.responsibility_name,
       frg.request_group_name,
       frgu.request_unit_type,
       frgu.request_unit_id,
       fcpt.user_request_set_name
FROM
       apps.FND_RESPONSIBILITY fr,
       apps.FND_RESPONSIBILITY_TL frt,
       apps.FND_REQUEST_GROUPS frg,
       apps.FND_REQUEST_GROUP_UNITS frgu,
       apps.FND_REQUEST_SETS_TL fcpt
WHERE
       frt.responsibility_id = fr.responsibility_id
AND    frg.request_group_id = fr.request_group_id
AND    frgu.request_group_id = frg.request_group_id
AND    fcpt.request_set_id = frgu.request_unit_id
AND    frt.LANGUAGE = USERENV('LANG')
AND    fcpt.LANGUAGE = USERENV('LANG')
AND    fcpt.user_request_set_name = :request_set_name
ORDER BY 1,2,3,4

-- Query to derive responsibility/responsibilities associated to an Application(FND) user/users

Parameters:-

1. Application(FND) User Name
2. Responsibility Name

SELECT u.user_name,
              u.start_date user_start_date,
              u.end_date user_end_date,
              u.email_address user_email_address,
              r.responsibility_name,
              g.start_date resp_assign_start_date,
              g.end_date resp_assign_end_date,
              g.user_id,
              r.responsibility_id
    FROM apps.fnd_user u,
             apps.fnd_user_resp_groups g,
             apps.fnd_responsibility_tl r
   WHERE     1 = 1
        AND r.responsibility_id = g.responsibility_id
        AND g.user_id = u.user_id
        AND u.user_name = NVL(:p_user_name, u.user_name)
        AND r.responsibility_name = NVL(:p_responsibility_name, r.responsibility_name)
ORDER BY u.user_name, r.responsibility_name DESC;

Verifying Profile Option Values
SELECT DECODE (fpov.level_id,
               10001, 'Site',
               10002, 'Appl',
               10003, 'Resp',
               10004, 'User',
               'Unkown'
              ) "Level",
       DECODE (fpov.level_id,
               10002, fa.application_name,
               10003, fr.responsibility_name,
               10004, fu.user_name,
               '-'
              ) "Location",
       fpov.profile_option_value "Value"
FROM apps.fnd_profile_option_values fpov,
       apps.fnd_profile_options fpo,
       apps.fnd_profile_options_tl fpot,
       apps.fnd_responsibility_tl fr,
       apps.fnd_user fu,
       apps.fnd_application_tl fa
WHERE fpov.profile_option_id = fpo.profile_option_id
   AND fpo.profile_option_name = fpot.profile_option_name
   AND fpov.level_value = fr.responsibility_id(+)
   AND fpov.level_value = fu.user_id(+)
   AND fpov.level_value = fa.application_id(+)
   AND fpot.user_profile_option_name = '<Profile Option Name>';

Identifying XML Report Lobs
SELECT b.*
FROM apps.xdo_lobs l, apps.xdo_templates_b b
WHERE l.application_short_name = '<appl short name>'
AND l.lob_code IN ('<DATA DEFINITION SHORT CODE>')
AND l.application_short_name = b.application_short_name
AND l.lob_code = b.template_code
Setting Org Context

Setting the Multi Org Context :

METHOD 1:

begin
    MO_GLOBAL.SET_POLICY_CONTEXT(ACCESS_MODE,ORG_ID);
end;

Example:

begin
    MO_GLOBAL.SET_POLICY_CONTEXT('S',101);
end;

S - Denotes that the current session will work for Single Org_id (101)

M - Denotes that the current session will work for Multiple Org_id

METHOD 2:

begin
mo_global.init (<APPLICATION SHORT NAME>);
end;

Example :

begin
mo_global.init ('AR');
end;

Query :

select Application_short_name , application_name
from   fnd_application fapp,
       fnd_application_tl fappt
where fapp.APPLICATION_ID = fappt.application_id
and    fappt.language = 'US'
and    application_name = 'General Ledger'

-----------------------------------------------------------------

Setting the Application Context :

METHOD 1:

begin
fnd_global.apps_initialize(p_user_id, p_resp_id, p_resp_appl_id);
end;

Example :

begin
      fnd_global.APPS_INITIALIZE(200131258,20004,51710);
end;
begin
      fnd_global.APPS_INITIALIZE
      (user_id       => 200131258,
       resp_id       => 20004,
       resp_appl_id => 51710
      );
end;

Query to find resp_is , resp_appl_id and user_id

select responsibility_id
       ,application_id
       ,responsibility_name
from   fnd_responsibility_tl
where upper(responsibility_name) IN ( upper('Receivables Manager'), upper('Application Developer' ) )
and    language = 'US';

select user_id
from    fnd_user
where upper(user_name) = 'SAIF';

SELECT fnd_profile.value (‘RESP_ID’) FROM dual

SELECT fnd_profile.value (‘USER_ID’) FROM dual

SELECT fnd_profile.value (‘APPLICATION_ID’) FROM dual

SELECT TO_NUMBER (FND_PROFILE.VALUE( ‘LOGIN_ID ‘)) FROM dual

SELECT FND_PROFILE.VALUE(‘ORG_ID’) FROM dual

SELECT FND_PROFILE.VALUE(‘SO_ORGANIZATION_ID’) FROM dual

SELECT FND_PROFILE.VALUE(‘USERNAME’) FROM dual

SELECT FND_PROFILE.VALUE(‘GL_SET_OF_BKS_ID’) FROM dual

METHOD 2 :

begin
dbms_application_info.set_client_info('<org id>');
end;

Example

begin
dbms_application_info.set_client_info('101');
end;

API
=========
DECLARE
        uid NUMBER;
        rid NUMBER;
        rad NUMBER;
        sgid NUMBER;
BEGIN
SELECT
        USER_ID,RESPONSIBILITY_ID,RESPONSIBILITY_APPLICATION_ID,SECURITY_GROUP_ID
INTO
        uid, rid, rad, sgid
FROM
        FND_USER_RESP_GROUPS
WHERE
USER_ID = (SELECT USER_ID FROM FND_USER WHERE USER_NAME = 'SYSADMIN')
AND RESPONSIBILITY_ID =
(SELECT RESPONSIBILITY_ID FROM FND_RESPONSIBILITY_VL WHERE RESPONSIBILITY_KEY = 'SYSTEM_ADMINISTRATOR');
FND_GLOBAL.apps_initialize (uid, rid, rad, sgid);
ego_p4t_upgrade_pvt.upgrade_to_pim4telco(null);
END;

Key FND Tables in Oracle Application

FND_LOBS:
Table contains all the attachments which were attached by users in all the modules of E-Business Suite, since the instance was created.

FND_DOCUMENTS_LONG_RAW:
Stores images and OLE Objects, such as Word Documents and Excel spreadsheets, in the database.

FND_DOCUMENTS_LONG_TEXT:
Stores information about long text documents.

FND_DOCUMENTS_SHORT_TEXT:
Stores information about short text documents.

FND_APPLICATION:
Stores applications registered with Oracle Application Object Library.

FND_APPLICATION_TL:
Stores translated information about all the applications registered with Oracle Application Object Library.

FND_APP_SERVERS:
This table will track the servers used by the E-Business Suite system.

FND_ATTACHED_DOCUMENTS:
Stores information relating a document to an application entity.

FND_CONCURRENT_PROCESSES:
Stores information about concurrent managers.

FND_CONCURRENT_PROCESSORS:
Stores information about immediate (subroutine) concurrent program libraries.

FND_CONCURRENT_PROGRAMS:
Stores information about concurrent programs. Each row includes a name and description of the concurrent program.

FND_CONCURRENT_PROGRAMS_TL:
Stores translated information about concurrent programs in each of the installed languages.

FND_CONCURRENT_QUEUES:
Stores information about concurrent managers.

FND_CONCURRENT_QUEUE_SIZE:
Stores information about the number of requests a concurrent manager can process at once, according to its work shift.

FND_CONCURRENT_REQUESTS:
Stores information about individual concurrent requests.

FND_CONCURRENT_REQUEST_CLASS:
Stores information about concurrent request types.

FND_CONC_REQ_OUTPUTS:
This table stores output files created by Concurrent Request.

FND_CURRENCIES:
Stores information about currencies.

FND_DATABASES:
It tracks the databases employed by the eBusiness suite. This table stores information about the database that is not instance specific.

FND_DATABASE_INSTANCES:
Stores instance specific information. Every database has one or more instance.

FND_DESCRIPTIVE_FLEXS:
Stores setup information about descriptive flexfields.

FND_DESCRIPTIVE_FLEXS_TL:
Stores translated setup information about descriptive flexfields.

FND_DOCUMENTS:
Stores language-independent information about a document.

FND_EXECUTABLES:
Stores information about concurrent program executables.

FND_FLEX_VALUES:
Stores valid values for key and descriptive flexfield segments.

FND_FLEX_VALUE_SETS:
Stores information about the value sets used by both key and descriptive flexfields.

FND_LANGUAGES:
Stores information regarding languages and dialects.

FND_MENUS:
It lists the menus that appear in the Navigate Window, as determined by the System Administrator when defining responsibilities for function security.

FND_MENUS_TL:
Stores translated information about the menus in FND_MENUS.

FND_MENU_ENTRIES:
Stores information about individual entries in the menus in FND_MENUS.

FND_PROFILE_OPTIONS:
Stores information about user profile options.

FND_REQUEST_GROUPS:
Stores information about report security groups.

FND_REQUEST_SETS:
Stores information about report sets.

FND_RESPONSIBILITY:
Stores information about responsibilities. Each row includes the name and description of the responsibility, the application it belongs to, and values that identify the main menu, and the first form that it uses.

FND_RESPONSIBILITY_TL:
Stores translated information about responsibilities.

FND_RESP_FUNCTIONS:
Stores security exclusion rules for function security menus. Security exclusion rules are lists of functions and menus inaccessible to a particular responsibility.

FND_SECURITY_GROUPS:
Stores information about security groups used to partition data in a Service Bureau architecture.

FND_SEQUENCES:
Stores information about the registered sequences in your applications.

FND_TABLES:
Stores information about the registered tables in your applications.

FND_VIEWS:
Stores information about the registered views in your applications.

FND_TERRITORIES:
Stores information for countries, alternatively known as territories.

FND_USER:
Stores information about application users.

====================================================
Key Metadata Tables/Views In Oracle Applications

The following are some views a PL/SQL developer is most likely to find useful:

USER_DEPENDENCIES
Stores the dependencies to and from the objects a current user owns. This view is mostly used by Oracle to mark objects INVALID when necessary, and also by IDEs to display the dependency information in their object browsers.

USER_ERRORS
Stores the current set of errors for all stored objects a current user owns. This view is accessed by the SHOW ERRORS SQL*Plus command.

USER_OBJECTS
Displays the objects owned by the current user. You can, for instance, use this view to see if an object is marked INVALID, find all the packages that have “DEPT” in their names, etc.

USER_OBJECT_SIZE
Displays the size of the objects owned by the current user. Actually, this view will show you the source, parsed, and compile sizes for your code. Use it to identify the large programs in your environment, good candidates for pinning into the SGA.

USER_PLSQL_OBJECT_SETTINGS
(Introduced in Oracle Database 10g Release 1) Shows Information about the characteristics of a PL/SQL object that can be modified through the ALTER and SET DDL commands, such as the optimization level, debug settings, and more.

USER_PROCEDURES
(Introduced in Oracle9i Database Release 1) Shows Information about stored programs, such as the AUTHID setting, whether the program was defined as DETERMINISTIC, and so on.

USER_SOURCE
Shows the text source code for all objects you own (in Oracle9i Database and above, including database triggers and Java source). This is a very handy view, because you can run all sorts of analysis of the source code against it using SQL and, in particular, Oracle Text.

USER_ARGUMENTS
Shows the arguments (parameters) in all the procedures and functions in your schema.

USER_TRIGGERS and USER_TRIG_COLUMNS
Displays the database triggers owned by current user, and any columns identified with the triggers. You can write programs against this view to enable or disable triggers for a particular table.

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

FND QUERIES FOR AOL

Wednesday, 31 August 2011

oracle apps FAQ

FAQ

What is the interface?
Interface Table is a table which is used as medium for transfer of data between two systems.

What is INBOUND and OUT BOUND? (Different types of interfaces)

Inbound Interface:
For inbound interfaces, where these products are the destination, interface tables as well as supporting validation, processing, and maintenance programs are provided.

Outbound Interface:For outbound interfaces, where these products are the source, database views are provided and the destination application should provide the validation, processing, and maintenance programs

What is multi org?
“Legal entity has more than one operating unit is called as multi org”
a) Business group --- Human resources information is secured by
Business group
b) Legal entity. --- inter-company and fiscal/tax reporting.
Security responsibility operating unit.
c) Operating unit --- secures AR, OE, AP, PA and PO Information.
d) Organizations --- is a specialize unit of work at particular locations
What are the User PARAMETERS in the Reports?
P_CONC_REQUEST_ID
P_FLEX_VALUE
FND USER EXITS:-FND SRWINIT sets your profile option values, multiple organizations and allows Oracle Application Object
Library user exits to detect that they have been called by an Oracle Reports program.FND SRWEXIT ensures that all the memory allocated for AOL user exits have been freed up properly.FND FLEXIDVAL are used to display flex field information like prompt, value etcFND FLEXSQL these user exits allow you to use flex fields in your reportsFND FORMAT_CURRENCY is used to print currency in various formats by using formula column
What are the requests groups?a) Single request: - this allows you to submit an individual request.
b) Request set : - this allows you to submit a pre-defined set of requests.
Difference between Bind and Lexical parameters?BIND VARIABLE :
are used to replace a single value in sql, pl/sql bind variable may be used to replace expressions in select, where, group, order by, having, connect by, start with cause of queries.
bind reference may not be referenced in FROM clause (or) in place of reserved words or clauses.
LEXICAL REFERENCE:
you can use lexical reference to replace the clauses appearing AFTER select,
from, group by, having, connect by, start with. you can’t make lexical reference in a pl/sql statmetns.
update clause:
1) use explicit locking to deny access for the duration of a transaction
2) lock the rows before update or delete
Ex : select …….
From…….
For update[ of column ref] [no_wait]where current of clause?
1) use cursor to update or delete the current row
Where current of <>
What is the package?
Group logically related pl/sql types, items and subprograms.
1. package specification
2. package bodyAdvantages of a package:A. Modularity
B. Easier Application Design
C.Information HidingOverloadingYou cannot overload:
•Two subprograms if their formal parameters differ only in name or parameter mode. (datatype and their
total number is same).
•Two subprograms if their formal parameters differ only in datatype and the different datatypes are in the
same family (number and decimal belong to the same family)
•Two subprograms if their formal parameters differ only in subtype and the different subtypes are based
on types in the same family (VARCHAR and STRING are subtypes of VARCHAR2)
•Two functions that differ only in return type, even if the types are in different families.
What are triggers?
triggers are similar to procedures, in that they are the named pl/sql blocks with declarative,executable and exception-handling sections, how ever a procedure is executed explicitly from another block via a procedure call, which can also pass arguments.
A trigger is executed implicitly when ever a particular event task places. And is nothing but a event.
The triggering event is a DML (insert, update, delete) operations on a data base table
fires whenever a data event(such as DML) or system event(such as login or shutdown) occurs on a schema or databaseTrigger timing :
1) before
2) after
3) instead of ( this is used for views)
events :
1) insert
2)update
3) deleteTrigger type :
1) statement level
2) row level.Firing sequence of database triggers
1. before statement trigger
2. before row trigger
3. after row trigger
4. after statement trigger
LOCKS?
Is to reduce concurrency1) share lock
it allows the other users for only reading not to insert2) exclusive lock
only one user can have the privileges of insert or
others can only read.3) update lock
multiple user can read, update deleteLock levels :1) table level 2) table space 3) data base level.
What are ad-hoc reports?
Ans.: Ad-hoc Report is made to meet one-time reporting needs. Concerned with or formed for a particular purpose. For example, ad hoc tax codes or an ad hoc database query

AOL features in OM
Order Management uses the following AOL features
Document Sequences - Order Entry used Order Number Sources for order
numbering. It let you define sequences that were used to automatically
number Orders and Returns. You assigned an Order Number source to an
Order Type.

Order Management uses the AOL Document Sequences feature to meet
Order Numbering requirements. This lets you number Order and Returns
using Manual, Automatic or Gapless sequences.You can define Document Sequences using the AOL Define Document Sequences form. When you define an Order Transaction Type, the application automatically creates a Document Sequence Category of the same name. You can assign a pre-defined sequence to one or more Document Sequence Categories, using the AOL Sequence Assignments
form. When an Order is created, Order Management calls AOL Document
Sequence APIs to number the Order.

Attachments - In Order Entry you could define Notes and addition rules regarding when they were attached to an Order or Line. You had to manually choose to add the eligible Notes to the Order or Line. You could also define how the Notes were printed on various Reports. Database triggers were used to duplicate Note definition data in AOL.

Order Management drives off the AOL Attachment functionality enabling you to attach images and web pages (in addition to short or long text). It also offers multi-lingual Document capability. Attachment definition and usage data is stored only in AOL. Automatic Addition rule definitions are stored in Order Management (OE_ATTACHMENT_RULES,OE_ATTACHMENT_RULE_ELEMENTS).

In addition to the attributes
(Customer, Ship-to, Invoice-to, Order Type, Item, PO #) that were previously available, you can now define rules based on the Order Category,Line Category and Line Type.
Oracle Order Management - Sales Order
The Profile Option ‘OM: Apply Automatic Attachments’ determines
whether rule based attachments are automatically applied

How to Download Etrm PDF format files
Use the following :
http://appsre.us.oracle.com/11idoc/11.5.1/html/trmset.html

Information Templates In PO

Oracle Internet Procurement 11i uses information templates to pass necessary order processing information to suppliers. You may set up information templatesto gather additional informationWhen an information template is assigned to a category or item, Internet Procurement 11i prompts users to provide the information specified in the template

To define an information template:

1. Navigate to the Define Information Template window. From the Oracle
Purchasing menu, select Setup>Information Templates.2. Enter an attribute name and description. The attribute name is the actual
field prompt that is displayed in Internet Procurement 11i.
3. Optionally, enter a default value to automatically appear in the field.
4. Indicate whether the field is mandatory for Internet Procurement 11i users.
If the field is mandatory, users will be prompted to enter a value in the field
before proceeding to complete the requisition.
5. Indicate whether to activate the attribute to actually display on Self
Service Purchasing pages. In certain circumstances, you may want to define an
attribute, but delay enabling it for display to Internet Procurement 11i users.
6. Choose Associate Template to associate the template with an item or an item
category. The Information Template Association window appears.7. Select the type of association (item number or item category) you want to
associate with the template.

8. If you selected Item Number in the previous step, enter the number. If you
selected Item Category, enter the category.

General Ledger FAQ

What is Journal Import?

A) Journal import is an interface used to bring journal entries from legacy systems and other modules into the General Ledger.(Specifically Journal Import gets entries from legacy data into the GL base tables.

The tables populated during journal Import are

GL_JE_BATCHES,

GL_JE_HEADERS,

GL_JE_LINES,

GL_IMPORT_REFERENCES

What is the use of GL_Interface?

A) Gl_Interface is the primary interface table of General ledger. It acts as an interface between data originating from other modules such as AP,AR, Legacy data and the Gl Base tables.

What is Actual Flag?

A) Actual flag represents the Journal type.

A-Actual

B-Budget

E- Encumbrance.

What is Encumbrance?

A) It is a process of Reservation of funds for anticipated expenditure from a budget. Encumbrance integrates GL, Purchasing and Payables modules.

How many Key Flex Fields are there in General Ledger?

A)One. Accounting Key Flex Field.

How many types of Budgets are there?
A)Two Types.

Expenditure Budgets

Revenue Budgets.

What are Spot Rate, Corporate Rate, Transaction Calendar and Accounting Calendar?

Spot Rate:

An exchange rate which you enter to perform conversion based on the rate on a specific date. It applies to the immediate delivery of currency.Corporate Rate:
An Exchange rate that we define to standardize rates for our company. This rate is the standard market rate determined by the senior financial management for use through out the organization.User Rate:
Conversion rate that is defined by the user.
EMU Fixed Rate: An exchange rate that is provided automatically by the General Ledger while entering journals. It uses a foreign currency that has a fixed relationship with the euro.Transaction Calendar: Defines the business days and holidays for any calendar.Accounting Calendar: Defines different types of calendars namely Fiscal, Federal Fiscal, Month etc.

What is Security Rule?

Security Rules are defined to control the access of a flexfield segment value (Financial information) at a responsibility level.

What are Cross Validation & ADI?

CVS – Cross validate segments – Allows only valid code combinations.
ADI – Allow dynamic inserts. – Allows any code combination irrespective of validity.
ADI would prevail if both of CVS and ADI are checked.
What is Translation?

A) Translation is a process used to convert functional currency to other reporting currencies at the account balances level.

What is Revaluation?

A) It is process used to revalue assets and liabilities denominated in foreign currency into functional currency based on period end exchange rate we specify. Unrealized gains/losses are resulted because of exchange rate fluctuations which are recorded in unrealized gain/loss account in GL.

What is FSG (Financial Statement Generator)?

A) Financial statement generator feature helps us to generate reports such as balance sheets and income statements with out programming. It also provides a high degree of control on the rows, columns, contents and calculations on the report. Different components such as row set, column set, content set, row order, display set have to be defined before a statement is generated, of which row set and column set are mandatory.

What is Consolidation?

A) Consolidation is a period-end process of combining the financial results of separate business subsidiaries with the parent company to form a single combined statement of financial results.

At what level General Ledger data is secured?

A) GL data is secured at Set of Book level. Subledger module data is secured at Responsibility level (i.e., at Operating Unit Level).

Account Receivables FAQ

1) What is Autolockbox?
A) Auto lockbox is a service that commercial banks offer corporate customers to enable them to out source their account receivable payment processing. Auto lockbox can also be used to transfer receivables from previous accounting systems into current receivables. It eliminates manual data entry by automatically processing receipts that are sent directly to banks. It involves three steps
Import (Formats data from bank file and populates the Interface Table),
Validation(Validates the data and then Populates data into Interim Tables),
Post Quick Cash(Applies Receipts and updates Balances in BaseTables).
2)What is Transmission Format?A) Transmission Format specifies how data in the lockbox bank file should be organized such that it can be successfully imported into receivables interface tables. Example, Default, Convert, Cross Currency, Zengen are some of the standard formats provided by oracle.
3)What is Auto Invoice?A) Autoinvoice is a tool used to import and validate transaction data from other financial systems and create invoices, debit-memos, credit memos, and on account credits in Oracle receivables. Using Custom Feeder programs transaction data is imported into the autoinvoice interface tables.
Autoinvoice interface program then selects data from interface tables and creates transactions in receivables (Populates receivable base tables) . Transactions with invalid information are rejected by receivables and are stored in RA_INTERFACE_ERRORS_ALL interface table.
4) What are the Mandatory Interface Tables in Auto Invoice?
A)RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL.
5) What are the Set up required for Custom Conversion, Autolockbox and Auto Invoice?
A) Autoinvoice program Needs AutoAccounting to be defined prior to its execution.6) What is AutoAccounting?
A) By defining AutoAccounting we specify how the receivables should determine the general ledger accounts for transactions manually entered or imported using Autoinvoice. Receivables automatically creates default accounts(Accounting Flex field values) for revenue, tax, freight, financial charge, unbilled receivable, and unearned revenue accounts using the AutoAccounting information.
7) What are Autocash rules?
A) Autocash rules are used to determine how to apply the receipts to the customers outstanding debit items. Autocash Rule Sets are used to determine the sequence of Autocash rules that Post Quickcash uses to update the customers account balances.
8) What are Grouping Rules? (Used by Autoinvoice)
A) Grouping rules specify the attributes that must be identical for lines to appear on the same transaction. After the grouping rules are defined autoinvoice uses them to group revenues and credit transactions into invoices debit memos, and credit memos.
9) What are Line Ordering Rules? (Used by Autoinvoice)
A) Line ordering rules are used to order transaction lines when grouping the transactions into invoices, debit memos and credit memos by autoinvoice program. For instance if transactions are being imported from oracle order management , and an invoice line ordering rule for sales_order _line is created then the invoice lists the lines in the same order of lines in sales order.
10) In which table you can see the amount due of a customer?
A) AR_PAYMENT_SCHEDULES_ALL
11) How do you tie Credit Memo to the Invoice?
At table level, In RA_CUSTOMER_TRX_ALL, If you entered a credit memo, the PREVIOUS_CUSTOMER_TRX_ID column stores the customer transaction ID of the invoice that you credited. In the case of on-account credits, which are not related to any invoice when the credits are created, the PREVIOUS_CUSTOMER_TRX_ID column is null.
12)What are the available Key Flex Fields in Oracle Receivables?
A) Sales Tax Location Flex field, It’s used for sales tax calculations.
Territory Flex field is used for capturing address information.
13). What are Transaction types? Types of Transactions in AR?
A) Transaction types are used to define accounting for different transactions such as Debit Memo, Credit Memo, On-Account Credits, Charge Backs, Commitments and invoices.

14) What are the different statuses for Receipts?
A) Unidentified – Lack of Customer Information
Unapplied – Lack of Transaction/Invoice specific information (Ex- Invoice Number)
Applied – When all the required information is provided.
On-Account, Non-Sufficient Funds, Stop Payment, and Reversed receipt.

Customer Conversion:Interface Tables :

RA_CUSTOMERS_INTERFACE_ALL

RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL

Base Tables :

RA_CUSTOMERS

RA_ADDRESSES

RA_SITE_USES_ALL

RA_CUSTOMER_PROFILES_ALL

RA_PHONES

Auto Invoice:
Interface Tables :

RA_INTERFACE_LINES_ALL,

RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL,

RA_INTERFACE_ERRORS_ALL
Base Tables :

RA_CUSTOMER_TRX_ALL,

RA_CUSTOMER_TRX_LINES_ALL,
RA_CUST_TRX_LINE_GL_DIST_ALL,

RA_CUST_TRX_LINE_SALESREPS_ALL,

RA_CUST_TRX_TYPES_ALL
AutoLockBox:Interface Tables :

AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)
Interim tables :

AR_INTERIM_CASH_RECEIPTS_ALL (All Populated by Submit Validation)
AR_INTERIM_CASH_RCPT_LINES_ALL,
AR_INTERIM_POSTING
Base Tables :

AR_CASH_RECEIPTS_ALL,

AR_RECEIVABLE_APPLICATIONS_ALL,

AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)

ORDER MANAGEMENT FAQ
Base Tables Vs Interface Tables
Base Tables : OE_ORDER_HEADERS_ALL: Order Header Information
OE_ORDER_LINES_ALL: Items Information
OE_PRICE_ADJUSTMENTS: Discounts Information
OE_SALES_CREDITS: Sales Representative Credits.
Shipping Tables :WSH_NEW_DELIVERIES
WSH_DELIVERY_DETAILS
WSH_DELIVERY_ASSIGNMENTS
WSH_DELIVERIES
Interface Tables : OE_HEADERS_IFACE_ALL,
OE_LINES_IFACE_ALL
OE_PRICE_ADJS_IFACE_ALL,
OE_ACTIONS_IFACE_ALL
OE_CREDITS_IFACE_ALL (Order holds like credit check holds etc)
What is Order Import and What are the Setup's involved in Order Import?

A) Order Import is an open interface that consists of open interface tables and a set of API’s. It imports New, updated, or changed sales orders from other applications such as Legacy systems. Order Import features include validations, Defaulting, Processing Constraints checks, Applying and releasing of order holds, scheduling of shipments, then ultimately inserting, updating or deleting orders from the OM base tables. Order management checks all the data during the import process to ensure its validity with OM. Valid Transactions are then converted into orders with lines, reservations ,price adjustments, and sales credits in the OM base tables.

B) Setups:
· Setup every aspect of order management that we want to use with imported orders, including customers, pricing, items, and bills.
· Define and enable the order import sources using the order import source window.
3) the Order Cycle?

i) Enter the Sales Orderii)Book the Sales Order(SO will not be processed until booked(Inventory confirmation))iii)Release sales order(Pickslip Report is generated and Deliveries are created)
(Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.iv)Transaction Move Order (creates reservations determines the source and transfers the inventory into the staging areas)v)Launch Pick Releasevi)Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))4)Order to Cash Flow?
I. Enter the Sales OrderII.Book the Sales Order(SO will not be processed until booked(Inventory confirmation))
III.Release sales order(Pickslip Report is generated and Deliveries are created)
(Deliveries – details about the delivery. Belongs to shipping module (wsh_deliveries, wsh_new_deliveries, wsh_delivery_assignments etc) they explain how many items are being shipped and such details.IV.Transaction Move Order (Selects the serial number of the product which has to be moved/ shipped)V. Launch Pick ReleaseVI.Ship Confirm (Shipping Documents(Pickslip report, Performa Invoice, Shipping Lables))VII. AutoInvoice (Creation of Invoice in Accounts Receivable Module)VIII. Autolockbox ( Appling Receipts to Invoices In AR)IX.Transfer to General Ledger ( Populates GL interface tables)
X. Journal Import ( Populates GL base tables)XI.Posting ( Account Balances Updated).
5)What are the Process Constraints?
A. Process Constraints prevent users from adding updating, deleting, splitting lines and canceling order or return information beyond certain points in the order cycle. Oracle has provided certain process constraints which prevent data integrity violations.
Process constraints are defined for entities and attributes. Entities include regions on the sales order window such as order, line, order price adjustments, line price adjustments, order sales credits and line sales credits. Attributes include individual fields (of a particular entity) such as warehouse, shit to location, or agreement.
6) What are different types of Holds?
1)GSA(General Services Administration) Violation Hold(Ensures that specific customers always get better pricing for example Govt. Customers)2)Credit Checking Hold( Used for credit checking feature Ex: Credit Limit)3)Configurator Validation Hold ( Cause: If we invalidate a configuration after booking)
7)What is Document Sequence?A) Document sequence is defined to automatically generate numbers for your orders or returns as you enter them. Single / multiple document sequences can be defined for different order types.
Document sequences can be defined as three types Automatic (Does not ensure that the numbers are contiguous), Gapless (Ensures that the numbering is contiguous), Manual Numbering. Order Management validates that the number specified is unique for order type.
8) What are Defaulting Rules?
A) A defaulting rule is a value that OM automatically places in an order field of the sales order window. Defaulting rules reduce the amount of information one must enter. A defaulting rule is a collection of defaulting sources for objects and their attributes.
It involves the following steps
· Defaulting Conditions - Conditions for Defaulting
· Sequence – Priority for search
· Source – Entity ,Attribute, Value
· Defaulting source/Value10. When an order cannot be cancelled?
A) An order cannot be cancelled if,
· It has been closed
· It has already been cancelled
· A work order is open for an ATO line
· Any part of the line has been shipped or invoiced
· Any return line has been returned or credited.
11. When an order cannot be deleted?
A) you cannot delete an order line until there is a need for recording reason.12. What is order type?
A) An order type is the classification of order. It controls the order work flow activity, order number sequence, credit check point and transaction type. Order Type is associated to a work flow process which drives the processing of the order.
13. What are primary and secondary price lists?
A) Every order is associated to a price list as each item on the order ought to have a price. A price list is contains basic list information and one or more pricing lines, pricing attributes, qualifiers, and secondary price lists. The price list that is primarily associated to an order is termed as Primary price list.
The pricing engine uses a Secondary Price list if it cannot determine the price of the item ordered in the Primary price list.14. What is pick slip? Types?
A) It is an internal shipping document that pickers use to locate items to ship for an order.
· Standard Pick Slip – Each order will have its own pick slip with in each picking batch.
· Consolidated Pickslip – Pick slip will have all the orders released in the each picking batch.15. What is packing slip?
A) It is an external shipping document that accompanies the shipment itemizing the contents of the shipment.16. What are picking rules?
A) Picking rules define the sources and prioritization of sub inventories, lots, revisions and locators when the item is pick released by order management. They are user defined set of rules to define the priorities order management must use when picking items from finished goods inventory to ship to a customer.
17. Where do you find the order status column?
A) In the base tables, Order Status is maintained both at the header and line level. The field that maintains the Order status is FLOW_STATUS_CODE. This field is available in both the OE_ORDER_HEADERS_ALL and OE_ORDER_LINES_ALL.18. When the order import program is run it validates and the errors occurred can be seen in?A) Responsibility: Order Management Super UserNavigation: Order, Returns > Import Orders > Corrections

Workflow Access Levels

0-9: Reserved for