Oracle Apps: Query to check Value Set Values

Below is Query to check Valueset Values in Oracle Apps

SELECT ffvs.flex_value_set_id ,
ffvs.flex_value_set_name ,
ffvs.description set_description ,
ffvs.validation_type,
ffv.flex_value,
ffvt.description value_description,
ffv.enabled_flag,
ffv.last_update_date,
ffv.last_updated_by,
ffv.attribute1,
ffv.attribute2,
ffv.attribute3–Include attribute values based on DFF segments
FROM fnd_flex_value_sets ffvs ,
fnd_flex_values ffv ,
fnd_flex_values_tl ffvt
WHERE
ffvs.flex_value_set_id = ffv.flex_value_set_id
and ffv.flex_value_id = ffvt.flex_value_id
AND ffvt.language = USERENV(‘LANG’)
and flex_value_set_name like <Valueset>
ORDER BY flex_value asc

Oracle Apps General Ledger Tables: Oracle GL Tables and Common GL Queries

Below are Common Oracle Apps GL Tables

GL_SETS_OF_BOOKS
Stores information about the sets of books you define in your Oracle General Ledger application. Each row includes the set of books name, description, functional currency, and other information. This table corresponds to the Set of Books form.

GL_IMPORT_REFERENCES
Stores individual transactions from subledgers that have been summarized into Oracle General Ledger journal entry lines through the Journal Import process. You can specify the journal entry sources for which you want to maintain your transaction’s origin by entering ’Yes’ in the Import Journal References field of the Journal Sources form.
For each source that has Import Journal References set to ’Yes’, Oracle General Ledger will populate GL_IMPORT_REFERENCES with one record for each transaction in your feeder system.

GL_DAILY_RATES
Stores the daily conversion rates for foreign currency transactions. It replaces the GL_DAILY_CONVERSION_RATES table. It stores the rate to use when converting between two currencies for a given conversion date and conversion type. Each row in this table has a corresponding inverse row in which the from and to currencies are switched.

GL_JE_LINES
 Stores the journal entry lines that you enter in the Enter Journals form. There is a one–to–many relationship between journal entries and journal entry lines. Each row in this table stores the associated journal entry header ID, the line number, the associated code combination ID, and the debits or credits associated with the journal line. STATUS is ’U’ for unposted or ’P’ for posted.

GL_PERIODS
Stores information about the accounting periods you define using the Accounting Calendar form. Each row includes the start date and end date of the period, the period type, the fiscal year, the period number, and other information. There is a one–to–many relationship between a row in the GL_PERIOD_SETS table and rows inthis table.

GL_JE_HEADERS
Stores journal entries. There is a one–to–many relationship between journal entry batches and journal entries. Each row in this table includes the associated batch ID, the journal entry name and description, and other information about the journal entry. This table corresponds to the Journals window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted.

GL_JE_BATCHES
Stores journal entry batches. Each row includes the batch name, description, status, running total debits and credits, and other information. This table corresponds to the Batch window of the Enter Journals form. STATUS is ’U’ for unposted, ’P’ for posted, ’S’ for selected, ’I’ for in the process of being posted. Other values of status indicate an error condition. STATUS_VERIFIED is ’N’ when you create or modify an unposted journal entry batch.

GL_BALANCES
Stores actual, budget, and encumbrance balances for detail and summary accounts. This table stores functional currency, foreign currency, and statistical balances for each accounting period that has ever been opened.
ACTUAL_FLAG is either ’A’, ’B’, or ’E’ for actual, budget, or encumbrance balances, respectively. If ACTUAL_FLAG is ’B’, then BUDGET_VERSION_ID is required. If ACTUAL_FLAG is ’E’, then ENCUMBRANCE_TYPE_ID is required.

GL_CODE_COMBINATIONS
Stores valid account combinations foreach Accounting Flexfield structure within your Oracle General Ledger application. Associated with each account are certain codes and flags, including whether the account is enabled, whether detail posting ordetail budgeting is allowed, and others.

Common Oracle Apps General Ledger (GL Queries)
--Journal header summary
SELECT sob.SHORT_NAME"Book"
,   gjh.STATUS
,   gjh.POSTED_DATE
,   gjh.CREATION_DATE
,   gls.user_je_source_name"Source"
,   glc.user_je_category_name"Category"
,   gjh.PERIOD_NAME"Period"
,   gjb.NAME"Batch Name"
,   gjh.NAME"Journal Name"
,   gjh.currency_code"Currency"
FROM GL_JE_BATCHES gjb, GL_JE_HEADERS gjh,GL_SETS_OF_BOOKS sob,
GL_JE_SOURCES gls, GL_JE_CATEGORIES glc
WHERE gjb.JE_BATCH_ID = gjh.JE_BATCH_ID
AND gjh.SET_OF_BOOKS_ID = sob.set_of_books_id
AND gls.JE_SOURCE_NAME = gjh.JE_SOURCE
AND  glc.JE_CATEGORY_NAME = gjh.JE_CATEGORY
--and gjh.NAME = 'QUV-DECLARATION TVA 11/04'    -- JOURNAL NAME  
--and gls.user_je_source_name like '%Mass%'  -- JOURNAL SOURCE
--AND glc.user_je_category_name= 'Adjustment'  -- JOURNAL CATEGORY
--and gjh.PERIOD_NAME in ('MAY-06')    -- JOURNAL PERIOD
and (trunc(gjh.creation_date) >= to_date('01/07/2002','dd/mm/yyyy')
or trunc(gjh.posted_date) >= to_date('01/07/2002','dd/mm/yyyy'))
--and substr(sob.short_name,1,2) in ('DE')
order by 1,2 desc,3,4,5,7

--Interface Details
SELECT SOB.SHORT_NAME "BOOK"
,    GLI.SET_OF_BOOKS_ID "SOB ID"
, TRUNC(GLI.ACCOUNTING_DATE) "GL DATE"
, GLI.CURRENCY_CODE "CUR"
, GLI.USER_JE_CATEGORY_NAME "JE CATEGOTY"
, GLI.USER_JE_SOURCE_NAME "JE SOURCE"
, GLI.ENTERED_DR "ENT DR"
, GLI.ENTERED_CR "ENT CR"
, GLI.ACCOUNTED_DR "ACC DR"
, GLI.ACCOUNTED_CR "ACC CR"
, GLI.SEGMENT1||'.'||GLI.SEGMENT2||'.'||GLI.SEGMENT3||'.'||GLI.SEGMENT4||'.'||GLI.SEGMENT5
||'.'||GLI.SEGMENT6||'.'||GLI.SEGMENT7||'.'||GLI.SEGMENT8||'.'||GLI.SEGMENT9||'.'||GLI.SEGMENT10 "ACCOUNT COMB."
, GLI.REFERENCE1 "REF 1"
, GLI.REFERENCE2 "REF 2"
, GLI.REFERENCE4 "REF 4"
, GLI.REFERENCE7 "REF 7"
, GLI.REFERENCE10 "REF 10"
, GLI.WARNING_CODE
, GLI.STATUS_DESCRIPTION
, GLI.STATUS
--SELECT GLI.REFERENCE10 "REF 10"
--SELECT DISTINCT GLI.SEGMENT4--,GLI.SEGMENT2, GLI.SEGMENT3, SOB.SHORT_NAME, GLI.SET_OF_BOOKS_ID
FROM GL_INTERFACE GLI, GL_SETS_OF_BOOKS SOB
WHERE SOB.SET_OF_BOOKS_ID(+) = GLI.SET_OF_BOOKS_ID 
--AND GLI.WARNING_CODE IS NOT NULL
--AND GLI.STATUS <> 'P'
and GLI.USER_JE_SOURCE_NAME = 'Payables'
--and trunc(GLI.DATE_CREATED) > '01-DEC-2005'
--and GLI.CURRENCY_CODE  = 'GBP'
--and (GLI.ENTERED_DR <> GLI.ACCOUNTED_DR
-- or GLI.ENTERED_CR <> GLI.ACCOUNTED_CR)
--and GLI.USER_JE_CATEGORY_NAME = 'Bill'
--and substr(SOB.SHORT_NAME,1,2) in ('BE')
--and GLI.SEGMENT3 = '8181'
--AND GLI.STATUS_DESCRIPTION IS NOT NULL
order by 3

--Journal line details

SELECT SOB.SHORT_NAME

,   SOB.NAME

,   GJH.NAME

,   GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9 "ACCOUNT"

,   GJH.CURRENCY_CODE

,   SUM(GJL.ACCOUNTED_DR)"DR"

,   SUM(GJL.ACCOUNTED_CR)"CR"

,   SUM( NVL(GJL.ACCOUNTED_DR,0) - NVL(GJL.ACCOUNTED_CR,0))"END BALANCE"

,   GJL.PERIOD_NAME

FROM GL_JE_LINES GJL

, GL_JE_HEADERS GJH

, GL_CODE_COMBINATIONS GCC

, GL_SETS_OF_BOOKS SOB

WHERE GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID

AND GJL.SET_OF_BOOKS_ID = GJH.SET_OF_BOOKS_ID

AND SOB.SET_OF_BOOKS_ID = GJH.SET_OF_BOOKS_ID

AND SOB.SET_OF_BOOKS_ID = GJL.SET_OF_BOOKS_ID

AND GJL.PERIOD_NAME = 'JUL-03'

--AND SOB.SHORT_NAME = 'GBMAN'

--AND GJH.NAME LIKE '%PPL%'

--AND GCC.SEGMENT1 = '85'

--AND GCC.SEGMENT2 = '70'

--AND GCC.SEGMENT3 = '0000'

--AND GCC.SEGMENT4 = '88165'

--AND GJH.STATUS = 'P'

--AND GJL.EFFECTIVE_DATE >= TO_DATE('06/04/2002','DD/MM/YYYY')

--AND GJL.EFFECTIVE_DATE <= TO_DATE('30/11/2002','DD/MM/YYYY')

GROUP BY SOB.SHORT_NAME, SOB.NAME, GJH.NAME

, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9

,GJH.CURRENCY_CODE, GJL.PERIOD_NAME

Oracle Apps Web ADI: Modify Default upload Parameters

To change the default value of the Oracle Applications Web ADI Journal Upload parameters for the Descriptive FlexField Import and Validation.

1. From the Menu Associated with Responsibility, note the Function value for the menu item you use to         create your document with. Close the form after noting the name. For R12 GL it will be GL_ADI_CREATE_DOCUMENT function.

2. In system Administrator Navigate to Application, and click Function. Search for the form function name you noted 

3. Change the Parameter value for your form function to the required value

For example, it might be set to:
 bne:page=BneCreateDoc&bne:importFlex=IMPORTNOVALIDATION

The parameter bne:importFlex can have the values:
– IMPORTVALIDATION (import DFF with validation)
– IMPORTNOVALIDATION (import DFF without validation)
– NOIMPORT (no import DFF)

4. Save your form function, then close the form.

5. Select “Requests” from the “View” menu in forms – and monitor the “Compile Security” request to ensure your menus are recompiled. Exit out Oracle Applications.

NOTE:344780.1 – WebADI – Is It Possible to Change the Upload Parameter Values that Default When Creating Document and Uploading to GL?

BUG:5247233 – WEB ADI – JOURNAL IMPORT (JOURNAL IMPORT) NOT IMPORTING DESCRIPTIVE FLEXFIELDS

Web ADI, Oracle Applications, DFF, Descriptive Flex Field, Journal import

Oracle Forms Personalization: when validate record fires multiple times

In Oracle Apps if your doing a personalization on Oracle Forms and requirement is to Display a Message on WHEN-VALIDATE-RECORD. Its typical that WHEN-VALIDATE-RECORD fires multiple times and the Message will be displayed multiple times.

The way to handle this situation is simply to check the RECORD STATUS is in NEW CHANGED. Include the below condition in your personlization

:SYSTEM.RECORD_STATUS IN ('NEW','CHANGED')

Oracle Apps documentation: R12 docs and 11i Docs

Oracle provides several User and implementation guides for each module, below are the important links that provide them


R12:
http://docs.oracle.com/cd/B34956_01/current/html/docset.html


11i:
http://docs.oracle.com/cd/B25284_01/current/html/docset.html

Oracle Etrm link:

https://etrm.oracle.com/

Oracle Global Temporary Tables

Global Temporary Tables (GTT)
Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables. From Oracle 8i onward, the maintenance and management of temporary tables can be delegated to the server by using Global Temporary Tables.

Creation of Global Temporary Tables
The data in a global temporary table is private, such that data inserted by a session can only be accessed by that session. The session-specific rows in a global temporary table can be preserved for the whole session, or just for the current transaction. The ON COMMIT DELETE ROWS clause indicates that the data should be deleted at the end of the transaction.

CREATE GLOBAL TEMPORARY TABLE XXX_temp_table (
  COL1  NUMBER,
  COL2  NUMBER
) ON COMMIT DELETE ROWS;


In contrast, the ON COMMIT PRESERVE ROWS clause indicates that rows should be preserved until the end of the session.
CREATE GLOBAL TEMPORARY TABLE XXX_temp_table (
  COL1  NUMBER,
  COL2  NUMBER
) ON COMMIT PRESERVE ROWS;


Important points
If the TRUNCATE statement is issued against a temporary table, only the session specific data is truncated. There is no affect on the data of other sessions.

Data in temporary tables is stored in temp segments in the temp tablespace.

Data in temporary tables is automatically deleted at the end of the database session, even if it ends abnormally.

Indexes can be created on temporary tables. The content of the index and the scope of the index is the same as the database session.

Views can be created against temporary tables and combinations of temporary and permanent tables.
Temporary tables can have triggers associated with them.

Export and Import utilities can be used to transfer the table definitions, but no data rows are processed.

Statistics on temporary tables are common to all sessions. Oracle 12c allows session specific statistics.

There are a number of restrictions related to temporary tables but these are version specific.

Oracle Apps V$Session Tables: Find Session info

V$SESSION
This view lists session information for each current session.

V$PROCESS
This view contains information about the currently active processes. While the LATCHWAIT column indicates what latch a process is waiting for, the LATCHSPIN column indicates what latch a process is spinning on. On multi-processor machines, Oracle processes will spin on a latch before waiting on it.

V$SESSION_WAIT
V$SESSION_WAIT displays the current or last wait for each session.

V$SESSION_LONGOPS
This view displays the status of various operations that run for longer than 6 seconds (in absolute time).


Oracle standard scripts.
Session_waits.sql script uses the v$session_wait view to display a list of the events currently being waited on by active sessions.  This is useful when trying to discover what each active session is currently doing.


session_waits.sql

SET LINESIZE 200
SET PAGESIZE 1000

COLUMN username FORMAT A20
COLUMN event FORMAT A30
COLUMN wait_class FORMAT A15

SELECT NVL(s.username, '(oracle)') AS username,
       s.sid,
       s.serial#,
       sw.event,
       sw.wait_class,
       sw.wait_time,
       sw.seconds_in_wait,
       sw.state
FROM   v$session_wait sw,
       v$session s
WHERE  see code depot for full script
s.sid = sw.sid
ORDER BY sw.seconds_in_wait DESC;  

longops.sql
The use of the dbms_application_info package to register long operations was discussed in a previous section.  The longops.sql scripts uses the v$session_longops view to identify and monitor sessions performing long operations, including those registered using the dbms_application_info package.


COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE s.sid     = sl.sid
AND    s.serial# = sl.serial#;


Sessions.sql
The sessions.sql script uses the v$session view to provide information about all the sessions currently connected to the database instance.  In addition, the v$process view is used to provide the associated operating system process id (SPID) for each session.

SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s,
       v$process p
WHERE s.paddr = p.addr
ORDER BY s.username, s.osuser;

Oracle Apps: Query to fetch Profile option values

Query to fetch profile option for a Specific Responsibility

SELECT

substr(pro1.user_profile_option_name,1,35) Profile,

decode(pov.level_id,

10001,'Site',

10002,'Application',

10003,'Resp',

10004,'User') Option_Level,

decode(pov.level_id,

10001,'Site',

10002,appl.application_short_name,

10003,resp.responsibility_name,

10004,u.user_name) Level_Value,

nvl(pov.profile_option_value,'Is Null') Profile_option_Value

FROM 

fnd_profile_option_values pov,

fnd_responsibility_tl resp,

fnd_application appl,

fnd_user u,

fnd_profile_options pro,

fnd_profile_options_tl pro1

WHERE

pro1.user_profile_option_name like <PROFILE NAME>

and  pro.profile_option_name = pro1.profile_option_name

and  pro.profile_option_id = pov.profile_option_id

and  resp.responsibility_name like <RESP NAME>

and  pov.level_value = resp.responsibility_id (+)

and  pov.level_value = appl.application_id (+)

and  pov.level_value = u.user_id (+)

order by 1,2;

 

Query to fetch profile option values:

  SELECT fpo.profile_option_name SHORT_NAME,

         fpot.user_profile_option_name NAME,

         DECODE (fpov.level_id,

                 10001, 'Site',

                 10002, 'Application',

                 10003, 'Responsibility',

                 10004, 'User',

                 10005, 'Server',

                 'UnDef')

            LEVEL_SET,

         DECODE (TO_CHAR (fpov.level_id),

                 '10001', '',

                 '10002', fap.application_short_name,

                 '10003', frsp.responsibility_key,

                 '10005', fnod.node_name,

                 '10006', hou.name,

                 '10004', fu.user_name,

                 'UnDef')

            "CONTEXT",

         fpov.profile_option_value VALUE

    FROM fnd_profile_options fpo,

         fnd_profile_option_values fpov,

         fnd_profile_options_tl fpot,

         fnd_user fu,

         fnd_application fap,

         fnd_responsibility frsp,

         fnd_nodes fnod,

         hr_operating_units hou

   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)

         AND fpo.profile_option_name = fpot.profile_option_name

         AND fu.user_id(+) = fpov.level_value

         AND frsp.application_id(+) = fpov.level_value_application_id

         AND frsp.responsibility_id(+) = fpov.level_value

         AND fap.application_id(+) = fpov.level_value

         AND fnod.node_id(+) = fpov.level_value

         AND hou.organization_id(+) = fpov.level_value

         AND fpot.user_profile_option_name IN ('&User_Profile_Option_Name')

ORDER BY short_name;

Oracle Inventory: Internal Requisition v/s Inter Org Transfer

If you are moving serialized items setup with serialization control 'At Sales Order Issue' then Internal Requisitions (Internal Orders) are best because the item serial numbers are updated on shipment and receipt. Inter Org transfers are good to move non serial controlled items or items setup with serialization 'At Receipt'. 

We use both for 2 different orgs:

1. We have a resin plant a few miles away that supplies our manufacturing facility with raw resin. We use the inter-org transfer, because of the streamline nature of the transaction, and because there is virtually no in-transit time; they "Push" the inventory into our org.
2. We have a distribution facility in another state that sends us finished goods, and vice versa. We use internal requisitions and internal sales orders, because we can pick release and ship confirm an internal SO just like a regular customer SO; therefore, we automatically get a packing slip and BOL; it fits into our normal processes for filling orders. While the shipment is on the road, the inventory value is automatically held in an in-transit account on the balance sheet. The inventory has been decremented from the shipping facility, but is not in the receiving facility until a receiving transaction is performed against the IR when the shipment physically arrives; neither orgs perpetual inventory is artificially affected by quantities still physically on the road.

The main problem for both inte-org and IR cycles is that there is now RMA for IRs and no cancel shipment for inter-org so you are obliged to receive shippments sent by user mistakes causing a mess in items costs.

The inter-org cycle is more simple flow (few steps), more user friendly and no interfaces used so less headeche in troubleshooting interface errors.

The IR cycle usefull for more tracing, control (IR, SO), approvals and generating pick slips.  Internal Requisition is used to replenish the material from other inventory
organization to your inventory organization.
It's better to have difference between internal sales order Vs Inter-Org
transfer?
1) Internal sales order has sales document (i.e proforma invoice) where as
inter org transfer is just transfer between inventory orgs with document
sales document.
2) Approval process is possible in ISO but it is not possible in Inter-Org
transfer.

Oracle Apps Script to Add Responsibility to User

Below is Simple script that one can use to Add responsibility to Oracle Apps User

 DECLARE
   v_appl_shrt_name   VARCHAR2 (20);
   v_appl_name           VARCHAR2 (50);
   v_resp_key              VARCHAR2 (50);
   v_user_name            VARCHAR2 (20) := 'DUMUSER';
   v_req_resp_name    VARCHAR2 (50) := 'DUMRESP';
   v_description            VARCHAR2 (100) :='DUMSCRIPT';
BEGIN
   SELECT fav.application_short_name,
                  fav.application_name,
                  frv.responsibility_key
     INTO   v_appl_shrt_name,
                 v_appl_name,
                 v_resp_key
     FROM FND_APPLICATION_VL fav,
                 FND_RESPONSIBILITY_VL frv
    WHERE frv.application_id = fav.application_id
     AND     frv.responsibility_name = v_req_resp_name;

   fnd_user_pkg.addresp
                         (username         => v_user_name,
                          resp_app         => v_appl_shrt_name,
                         resp_key          => v_resp_key,
                         security_group  => 'STANDARD',
                         description        => v_description,
                         start_date          => SYSDATE,
                         end_date           => NULL);
   COMMIT;
   DBMS_OUTPUT.put_line ('Responsibility ' || v_req_resp_name || ' is added to the user ' || v_user_name);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ('Responsibility addition Failed: ' || SQLCODE || '; '
                                            || SUBSTR (SQLERRM, 1, 250));
      ROLLBACK;
END;