Oracle Apps R12 HRMS Payroll Security (Menu fnd_sessions will not available and LOV's will not work)

Add the below functions without prompt to your HR Menu

else fnd_sessions will not available and LOV's will not work

1. HRMS Create Document

2. Salary Administration:Approve

3.Navigator: Disable Multiform

4. Desktop Integration - Create Document

oracle ebs r12 19c database query running slow solution ex: cst_cg_cost_history_v view

 if query running very slow then add this /*+ optimizer_features_enable('12.1.0.2') */ 
ex: 
select /*+ optimizer_features_enable('12.1.0.2') */  * from dual;
SELECT /*+ optimizer_features_enable('12.1.0.2') */ ACTUAL_COST FROM cst_cg_cost_history_v cst
where cst.inventory_item_id = MMT.INVENTORY_ITEM_ID
AND TRUNC(cst.TRANSACTION_DATE) = TRUNC(MMT.TRANSACTION_DATE)
and cst.organization_id = mmt.organization_id

EBS R12 Custom Form to add search Query

 https://www.youtube.com/watch?v=hLP4fGmy4Bc (check from 9min)
https://erpschools.com/erps/forms/query_find-form-in-oracle-apps
1. open APPSTAND.fmb form.
2. from object_groups drag QUERY_FIND to your form module(name of your custom form) and drop with ctl key pressed,
  once asked for confirmation select sub-class.
3. new QUERY_FIND OBJECTs will be created with same name under DataBlocks, Canvases, Object Groups of your custom form.
4. QUERY_FIND data block
Button New
replace the code with your block ex: DEPT
app_find.new('DEPT');
5. Button Find
:parameter.g_query_find := 'TRUE';
app_find.find('DEPT');
:parameter.g_query_find := 'FALSE';
6. open the canvas QUERY_FIND
drag one text field and give name ex: EMP_NUMBER_Q
7. Drag Trigger named QUERY_FIND from Appstand.fmb (STANDARD_OBJECTS) from main triggers section
and drop it to under your block ex: DEPT
8. open the code of this trigger we just dropped, modify it
--APPS_STANDARD.EVENT('QUERY_FIND'); --comment this
app_find.query_find('DEPT_WIND', --your main block window
'QUERY_FIND',
'QUERY_FIND');
9. under DEPT block new trigger pre-query:
if :parameter.g_query_find = 'TRUE' then
copy(:QUERY_FIND.EMP_NUMBER_Q,'DEPT.EMPNO');
:parameter.g_query_find := 'TRUE';
end if;

EBS R12 KFF in Custom Form

 1. update form level trigger WHEN-NEW-FORM-INSTANCE

declare
cursor get_cao is        
select  to_char(id_flex_num)        
from    fnd_id_flex_structures        
where   id_flex_structure_code = 'XX COA'               
 and id_flex_code = 'GL#';     
 v_cao    varchar2(1000);            
 begin    
  open get_cao;    
 fetch get_cao into v_cao;    
 if get_cao%notfound then        
 v_cao := '101';
     end if;    
     close get_cao;       
app_standard.event('WHEN-NEW-FORM-INSTANCE');    
 fnd_key_flex.define(      
 BLOCK=>'SFF_MO_HEADER_V',       --SFF_MO_HEADER_V block SFF_MOVE_ORDER_CAN in canvas
 FIELD=>'DESTINATION_ACCOUNT_SEGMENTS',  --field for LOV should be not database   
  APPL_SHORT_NAME=>'SQLGL',     
   CODE=>'GL#',      
   NUM=>v_cao,      
   ID=>'DESTINATION_ACCOUNT_CCID',--to show/store the CCID      
   DESCRIPTION=>'',
   TITLE=>'Test Title',      
   VALIDATE=>'FULL',      
   QBE_IN=>'Y',      
   DERIVE_ALWAYS=>'Y',      
   updateable => '',      
   --VRULE=>'\nSUMMARY_FLAG\nI\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN',      
   --vrule => 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED \nE\nAPPL='||'"'||'SQLGL'||'"'||'; name=Parent Values are not allowed\nN'
   where_clause => 'summary_flag !=''Y''',      
   QUERY_SECURITY =>'Y');  
   end ;--initialize;     

2. Field Properties
DESTINATION_ACCOUNT_SEGMENTS,  --field for LOV should be not database   
Database Item: No
Query Only: No
List of Values: Enable List Lamp
Validate from List: No

3. field DESTINATION_ACCOUNT_CCID,--to show/store the CCID      

Triggers:

3.Invoke Flexfield functionality by calling FND_Flex.Event(event) from Block Level:

• PRE-QUERY --  FND_FLEX.EVENT('PRE-QUERY' );
• POST-QUERY -- FND_FLEX.EVENT('POST-QUERY');
• PRE-INSERT -- 
• PRE-UPDATE -- 
• WHEN-VALIDATE-RECORD  (item Level down)
• WHEN-NEW-ITEM-INSTANCE (item Level down)
• WHEN-VALIDATE-ITEM (item Level down)

3. Write an item level trigger WHEN-NEW-ITEM-INSTANCE on DESTINATION_ACCOUNT_SEGMENTS as

FND_FLEX.EVENT(‘WHEN-NEW-ITEM-INSTANCE’)

4. Write an item level trigger KEY-EDIT on BTL_KFF as

FND_FLEX.EVENT(‘KEY-EDIT’);

5. Write an item level trigger WHEN-VALIDATE-ITEM on DESTINATION_ACCOUNT_SEGMENTS as

FND_FLEX.EVENT(‘WHEN-VALIDATE-ITEM’);

IF :XX_BLOCK.DESTINATION_ACCOUNT_CCID = -1 THEN
FND_MESSAGE.SET_STRING(‘You Have Selected An Undefined Code Combination !’);
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
END IF;

DFF in Custom form and register in EBS

 custom table with attributes  attribute_category  VARCHAR2(150),
                            attribute1  VARCHAR2(240),
                            attribute2  VARCHAR2(240),
                            attribute3  VARCHAR2(240),
                            attribute4  VARCHAR2(240),
                            attribute5  VARCHAR2(240);

Register Table:
Declare
v_appl_short_name   VARCHAR2 (40) := 'XXCUST';
   v_tab_name          VARCHAR2 (32) := 'XX_MO_LINES'; 
   v_tab_type          VARCHAR2 (50) := 'T';
   v_next_extent       NUMBER        := 512;
   v_pct_free          NUMBER;
   v_pct_used          NUMBER;
BEGIN
  -- — Unregister the custom table if it exists
   ad_dd.delete_table (p_appl_short_name             => 'XXCUST', p_tab_name => v_tab_name);
   --— Register the custom tabl
   FOR tab_details IN (SELECT table_name, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent
                         FROM dba_tables
                        WHERE table_name = v_tab_name)
   LOOP
      ad_dd.register_table (p_appl_short_name             => v_appl_short_name,
                            p_tab_name                    => tab_details.table_name,
                            p_tab_type                    => v_tab_type,
                            p_next_extent                 => NVL (tab_details.next_extent, 512),
                            p_pct_free                    => NVL (tab_details.pct_free, 10),
                            p_pct_used                    => NVL (tab_details.pct_used, 70)
                           );
   END LOOP;
   --— Register the columns of custom table
   FOR all_tab_cols IN (SELECT column_name, column_id, data_type, data_length, nullable
                          FROM all_tab_columns
                         WHERE table_name = v_tab_name)
   LOOP
      ad_dd.register_column (p_appl_short_name             => v_appl_short_name,
                             p_tab_name                    => v_tab_name,
                             p_col_name                    => all_tab_cols.column_name,
                             p_col_seq                     => all_tab_cols.column_id,
                             p_col_type                    => all_tab_cols.data_type,
                             p_col_width                   => all_tab_cols.data_length,
                             p_nullable                    => all_tab_cols.nullable,
                             p_translate                   => 'N',
                             p_precision                   => NULL,
                             p_scale                       => NULL
                            );
   END LOOP;
   FOR all_keys IN (SELECT constraint_name, table_name, constraint_type
                      FROM all_constraints
                     WHERE constraint_type = 'P' AND table_name = v_tab_name)
   LOOP
      ad_dd.register_primary_key (p_appl_short_name             => v_appl_short_name,
                                  p_key_name                    => all_keys.constraint_name,
                                  p_tab_name                    => all_keys.table_name,
                                  p_description                 => 'Register primary key',
                                  p_key_type                    => 'S',
                                  p_audit_flag                  => 'N',
                                  p_enabled_flag                => 'Y'
                                 );
      FOR all_columns IN (SELECT column_name, POSITION
                            FROM dba_cons_columns
                           WHERE table_name = all_keys.table_name AND
                           constraint_name = all_keys.constraint_name)
      LOOP
         ad_dd.register_primary_key_column (p_appl_short_name             => v_appl_short_name,
                                            p_key_name                    => all_keys.constraint_name,
                                            p_tab_name                    => all_keys.table_name,
                                            p_col_name                    => all_columns.column_name,
                                            p_col_sequence                => all_columns.POSITION
                                           );
      END LOOP;
   END LOOP;
   COMMIT;
END;

Step 3: Register the DFF in Oracle Apps
Responsibility: Application Developer
Navigation: Flexfield > Descriptive > Register

Give Name, Title, Description and TableName all as same.
Structure Column : ATTRIBUTE_CATEGORY

4. 

Go to Segments to create new DFF fields.

5.

In Custom Form, create one non-database field and name eg: DFF
Subclass Information: TEXT_ITEM_DESC_FLEX
Required: No
Canvas: <Set the name of the Canvas manually since the item was created manually>
Database Item: No
Insert Allowed: Yes
Update Allowed: Yes
List of Values: ENABLE_LIST_LAMP
Validate From List: No

6. create procedure under the Program Units

PROCEDURE XX_MO_LINES_V_DFF_P (event VARCHAR2)

IS

BEGIN

   IF (event = 'WHEN-NEW-FORM-INSTANCE')

   THEN

      fnd_descr_flex.define (BLOCK                         => 'SFF_MO_LINES_V',-- — BLOCK

                             FIELD                         => 'DFF', --NON DB FIELD

                             appl_short_name               => 'XXCUST',

                             desc_flex_name                => 'SFF_MO_LINES' --DFF FLEX NAME

                            );

   ELSE

      NULL;

   END IF;

END;

Triggers Form Level:

Open the WHEN-NEW-FORM-INSTANCE trigger.

XX_MO_LINES_V_DFF_P('WHEN-NEW-FORM-INSTANCE');

Note:

Oracle apps provides the API named, FND_FLEX, for Descriptive flexfield events. You can to write all the block level triggers to have consistent normal behaviour of the descriptive flexfield.

WHEN-VALIDATE-ITEM: FND_FLEX.EVENT(‘WHEN-VALIDATE-ITEM ‘);

PRE-QUERY: FND_FLEX.EVENT(‘PRE-QUERY’);

POST-QUERY: FND_FLEX.EVENT(‘POST-QUERY’);

WHEN-VALIDATE-ITEM: FND_FLEX.EVENT(‘WHEN-VALIDATE-RECORD’);

WHEN-NEW-ITEM-INSTANCE: FND_FLEX.EVENT(‘WHEN-NEW-ITEM-INSTANCE’);

PRE-INSERT: FND_FLEX.EVENT(‘PRE-INSERT’);

PRE-UPDATE: FND_FLEX.EVENT(‘PRE-UPDATE’);

Oracle EBS R12 Query XLA, GL Tables and GL Balances

0. GL_Balances
1. GL to AR Receipts.
2. GL to AP Payments
3. GL to COGS AR/SO (Sales Order/AR Trx)
4. GL to Move Order Details.
5. GL to WIP(Job Number)
6. GL to Inventory(Material Txn)
7. GL to Receipts (Inv RCV)

GL_Balances table will have only Posted GL txns.
GL>Inquiry>Accounts> Jan-24 to Feb-24, 
                                    ex: Jan-24 10000 (Closing Balance)
                                         Feb-24 50000 (Closing Balance)
Opening Balance for Jan-24 will be Closing Balance of ADJ-23 Period.
Opening Balance for Feb-24 will be Closing Balance of Jan-24 Period.

SELECT
SUM (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0))
beginning_bal,
SUM(  NVL (gb.begin_balance_dr, 0)
- NVL (gb.begin_balance_cr, 0)
+ (NVL (gb.period_net_Dr, 0) - NVL (gb.period_net_cr, 0)))
end_bal,
SUM(
(NVL (gb.period_net_Dr, 0) - NVL (gb.period_net_cr, 0))
)PERIOD_TXN
,gb.currency_code 
FROM   gl_balances gb, gl_code_combinations_kfv gcc
WHERE   gb.code_combination_id = gcc.code_combination_id
AND GCc.SEGMENT4 = :P_ACCOUNT_NUM
AND gb.ledger_id             = 2021 --— Enter the Ledger
--AND gb.Actual_flag = 'E'--–Encumbrance
AND gb.period_name = 'JAN-24'--                    –Enter the Period
--AND gb.currency_code = 'AED'
GROUP BY gb.currency_code 
;

--Function


FUNCTION 
F_GET_BEGIN_CLOSE_BAL
(P_LEDGER_ID NUMBER,
P_PERIOD_NUM VARCHAR2, --'20240001'
P_SEGMENT4 VARCHAR2, --'1101051001'
P_CURRENCY VARCHAR2, --'AED'
P_RET_TYPE VARCHAR2) --'BEGIN','END'
RETURN NUMBER AS
L_beginning_bal NUMBER;
L_end_bal NUMBER;
L_PERIOD_TXN NUMBER;
L_RETURN NUMBER;
BEGIN
SELECT
--gcc.segment4,
--gb.period_name,
SUM (NVL (gb.begin_balance_dr, 0) - NVL (gb.begin_balance_cr, 0))
beginning_bal,
SUM(  NVL (gb.begin_balance_dr, 0)
- NVL (gb.begin_balance_cr, 0)
+ (NVL (gb.period_net_Dr, 0) - NVL (gb.period_net_cr, 0)))
end_bal,
SUM(
(NVL (gb.period_net_Dr, 0) - NVL (gb.period_net_cr, 0))
)PERIOD_TXN
--,gb.currency_code 
INTO L_beginning_bal,L_end_bal,L_PERIOD_TXN
FROM   gl_balances gb, gl_code_combinations_kfv gcc
,gl_period_statuses gps
WHERE   gb.code_combination_id = gcc.code_combination_id
AND GCc.SEGMENT4 = P_SEGMENT4--'1101051001'--:P_ACCOUNT_NUM
AND gb.ledger_id             = P_LEDGER_ID--2021 --— Enter the Ledger
--AND gb.Actual_flag = 'E'--–Encumbrance
--AND gb.period_name = 'JAN-24'--                    –Enter the Period
AND gb.currency_code = P_CURRENCY--'AED'
AND GPS.PERIOD_NAME = GB.PERIOD_NAME
AND gps.LEDGER_ID = gb.LEDGER_ID 
AND GPS.EFFECTIVE_PERIOD_NUM = P_PERIOD_NUM--'20240001'
AND NVL(gb.translated_flag,'X') = 'X'
AND gps.APPLICATION_ID = 101
GROUP BY gb.currency_code,gcc.segment4
,gb.period_name
;
CASE WHEN P_RET_TYPE = 'BEGIN' THEN
L_RETURN := L_beginning_bal;
WHEN P_RET_TYPE = 'END' THEN
L_RETURN := L_end_bal;
WHEN P_RET_TYPE = 'L_PERIOD_TXN' THEN
L_RETURN := L_PERIOD_TXN;
ELSE
L_RETURN := 0;
END CASE;
RETURN L_RETURN;
EXCEPTION WHEN OTHERS THEN RETURN 0;
END;
SELECT F_GET_BEGIN_CLOSE_BAL(2021,'20240001','1101051001','AED','BEGIN') FROM DUAL; --opening balance 
SELECT F_GET_BEGIN_CLOSE_BAL(2021,'20240005','1101051001','AED','END') FROM DUAL; --closing balance

--for other queries
Scripts for Checking Closing to Opening Balances on the GL_BALANCES table (Doc ID 212082.1)


 SELECT gcc.concatenated_segments    ACCOUNTING_CODE_COMBINATION,
       --           xla_report_utility_pkg.get_ccid_desc (glg.chart_of_accounts_id,
       --                                                 ael.code_combination_id)
       --               CODE_COMBINATION_DESCRIPTION ,
       xte.transaction_number       TRANSACTION_NUMBER,
       xle.transaction_date         TRANSACTION_DATE,
       ael.currency_code            ENTERED_CURRENCY,
       aeh.description              HEADER_DESCRIPTION,
       aeh.event_type_code          EVENT_TYPE_CODE,
       aeh.doc_sequence_value       DOCUMENT_SEQUENCE_NUMBER,
       gjh.DEFAULT_EFFECTIVE_DATE   GL_DATE,
                 ael.entered_dr
               ENTERED_DR,
           ael.entered_cr
               ENTERED_CR 
                         ,
           ael.accounted_dr
               ACCOUNTED_DR,
           ael.accounted_cr
               ACCOUNTED_CR, 
       CASE
           WHEN gjh.STATUS = 'P' THEN 'Posted'
           WHEN gjh.STATUS = 'U' THEN 'UnPosted'
           ELSE gjh.STATUS
       END                          POST_STATUS   
  FROM xla.xla_transaction_entities  xte,
       xla_ae_headers                aeh,
       xla_ae_lines                  ael,
       gl.gl_import_references       gir,
       gl_je_lines                   gjl,
       gl_je_headers                 gjh,
       gl_je_batches                 gjb,
       gl_code_combinations_kfv      gcc,
       xla.xla_events                xle
 WHERE     1 = 1
       AND xle.application_id = aeh.application_id
       AND xle.event_id = aeh.event_id
       AND gjb.je_batch_id = gjh.je_batch_id
       AND xte.entity_id = aeh.entity_id
       AND aeh.ae_header_id = ael.ae_header_id
       AND ael.gl_sl_link_id = gir.gl_sl_link_id
       AND ael.gl_sl_link_table = gir.gl_sl_link_table
       AND gir.je_header_id = gjl.je_header_id
       AND gir.je_line_num = gjl.je_line_num
       AND gjl.je_header_id = gjh.je_header_id
       AND gcc.code_combination_id = gjl.code_combination_id
       AND GCC.segment4 = '1101051001'
       AND gjh.ledger_id = 2021
       AND gjh.DEFAULT_EFFECTIVE_DATE BETWEEN TO_DATE ('01-JAN-2024')
                                          AND TO_DATE ('31-JAN-2024');

Only GL Tables
SELECT B.*,
--:P_ORG1,
CASE WHEN AED_CR = 0 THEN NULL ELSE AED_CR END AED_CR, 
CASE WHEN AED_DR = 0 THEN NULL ELSE AED_DR END AED_DR,
CASE WHEN RunningAgeTotal < 0 THEN ABS(RunningAgeTotal) ELSE NULL END CREDIT_BALANCE,
CASE WHEN RunningAgeTotal > 0 THEN RunningAgeTotal ELSE NULL END DEBIT_BALANCE
FROM (
SELECT A.* 
,SUM (NVL(AED_DR,0) - NVL(AED_CR,0))OVER (ORDER BY JE_header_id,JE_LINE_NUM) AS RunningAgeTotal
FROM
(
SELECT    GLV.CONCATENATED_SEGMENTS
,GLH.DEFAULT_EFFECTIVE_DATE TXN_DATE
,GLH.DOC_SEQUENCE_VALUE TXN_NUMBER
,GLH.CURRENCY_CODE
,NVL(GLL.ENTERED_DR,GLL.ENTERED_CR)ORIGINAL_AMOUNT
,GLH.DOC_SEQUENCE_VALUE 
,GLH.DESCRIPTION
,GLH.DEFAULT_EFFECTIVE_DATE GL_DATE
,GLL.ACCOUNTED_DR AED_DR
,GLL.ACCOUNTED_CR AED_CR
,GLH.JE_HEADER_ID
,GLL.JE_LINE_NUM
,CASE
           WHEN GLH.STATUS = 'P' THEN 'Posted'
           WHEN GLH.STATUS = 'U' THEN 'UnPosted'
           ELSE GLH.STATUS
       END                           POST_STATUS
,GLH.JE_CATEGORY
,GLH.JE_SOURCE
  FROM GL_JE_HEADERS GLH, GL_JE_LINES GLL, GL_CODE_COMBINATIONS_KFV GLV
  ,GL_PERIOD_STATUSES GLPS
 WHERE     GLH.JE_HEADER_ID = GLL.JE_HEADER_ID
       AND GLL.CODE_COMBINATION_ID = GLV.CODE_COMBINATION_ID
       AND GLH.LEDGER_ID = :P_LEDGER_ID--2021
       AND GLV.SEGMENT4 BETWEEN :P_ACC_NUM_SEG4 AND  :P_ACC_NUM_SEG4_2--'1101032002'--:P_ACC_NUM_SEG4
       and GLH.period_name = GLPS.PERIOD_NAME--'FEB-24'
       AND GLPS.LEDGER_ID = GLH.LEDGER_ID
       AND GLPS.APPLICATION_ID = 101
       AND GLPS.EFFECTIVE_PERIOD_NUM BETWEEN :P_FROM_PERIOD AND :P_TO_PERIOD
       AND GLH.JE_SOURCE = NVL(:P_SOURCE,GLH.JE_SOURCE)
       AND GLH.STATUS = 'P'
UNION ALL
SELECT NULL CONCATENATED_SEGMENTS
,NULL  TXN_DATE
,NULL  TXN_NUMBER
,NULL CURRENCY_CODE
,NULL ORIGINAL_AMOUNT
,NULL DOC_SEQUENCE_VALUE 
,'Opening Balance' DESCRIPTION
,NULL GL_DATE
,CASE WHEN
GET_OPEN_GL_BALANCE_F(:P_LEDGER_ID,:P_ACC_NUM_SEG4, :P_FROM_PERIOD,:P_SOURCE) > 0 THEN
GET_OPEN_GL_BALANCE_F(:P_LEDGER_ID,:P_ACC_NUM_SEG4, :P_FROM_PERIOD,:P_SOURCE) ELSE NULL END DEBIT_BALANCE
,CASE WHEN 
GET_OPEN_GL_BALANCE_F(:P_LEDGER_ID,:P_ACC_NUM_SEG4, :P_FROM_PERIOD,:P_SOURCE) < 0 THEN
ABS(GET_OPEN_GL_BALANCE_F(:P_LEDGER_ID,:P_ACC_NUM_SEG4, :P_FROM_PERIOD,:P_SOURCE)) ELSE NULL END CREDIT_BALANCE
,1 JE_HEADER_ID
,1 JE_LINE_NUM
,'P'  POST_STATUS
,'Manual' JE_CATEGORY
,'Manual' JE_SOURCE
FROM DUAL       
ORDER BY JE_header_id,JE_LINE_NUM
)A
)B

----------------------------GL To Receipts and GL to Payables link-----------------
FUNCTION F_GET_XLA_GL_DET(P_JE_HEADER_ID NUMBER, P_JE_SOURCE VARCHAR2,P_LINE_NUMBER NUMBER,P_TYPE VARCHAR2)
RETURN VARCHAR2
AS
L_RETURN VARCHAR2(200);
L_TRANSACTION_NUMBER VARCHAR2(200);
L_EVENT_TYPE_CODE VARCHAR2(200);
L_TRANSACTION_DATE VARCHAR2(200);
L_APPLICATION_ID NUMBER;
BEGIN  

CASE WHEN P_JE_SOURCE = 'Manual' THEN
L_RETURN := 'Manual';
ELSE
SELECT APPLICATION_ID
INTO L_APPLICATION_ID 
FROM FND_APPLICATION_TL
WHERE APPLICATION_NAME = P_JE_SOURCE
AND ROWNUM = 1
;

SELECT --JE_HEADER_ID,XLA.TRANSACTION_NUMBER,AEH.EVENT_TYPE_CODE, TO_CHAR(TRANSACTION_DATE,'DD/MM/YYYY')TRANSACTION_DATE, EVENT_NUMBER
XLA.TRANSACTION_NUMBER,AEH.EVENT_TYPE_CODE, TO_CHAR(TRANSACTION_DATE,'DD/MM/YYYY')TRANSACTION_DATE INTO
L_TRANSACTION_NUMBER,L_EVENT_TYPE_CODE,L_TRANSACTION_DATE
                          FROM XLA_EVENTS XLAE
                          ,XLA.XLA_TRANSACTION_ENTITIES XLA
                          ,XLA_AE_HEADERS AEH
                          ,XLA_AE_LINES  AEL
                          ,GL_IMPORT_REFERENCES GLI
                         WHERE  
                         XLA.ENTITY_ID = XLAE.ENTITY_ID--293729
                         AND AEH.APPLICATION_ID = L_APPLICATION_ID--222 
                         AND AEH. AE_HEADER_ID = AEL. AE_HEADER_ID
--                         AND EVENT_NUMBER = P_LINE_NUMBER  AND AEL.AE_LINE_NUM = P_LINE_NUMBER -- ADD line_number here 
                         AND EVENT_NUMBER = AEL.AE_LINE_NUM
                         AND AEH.EVENT_ID = XLAE.EVENT_ID 
                         AND GLI.GL_SL_LINK_ID = AEL.GL_SL_LINK_ID
                         --AND SOURCE_ID_INT_1 = 104085
                         AND JE_HEADER_ID = P_JE_HEADER_ID;
CASE WHEN
P_TYPE = 'TXN_DATE' THEN
L_RETURN := L_TRANSACTION_DATE;
WHEN P_TYPE = 'EVENT_TYPE' THEN
L_RETURN :=  L_EVENT_TYPE_CODE;
WHEN P_TYPE = 'TXN_NUMBER' THEN
L_RETURN :=  L_TRANSACTION_NUMBER;
ELSE
L_RETURN := '';
END CASE;
END CASE; --MAIN
RETURN L_RETURN;
EXCEPTION WHEN OTHERS THEN RETURN NULL;                              
END;

F_GET_XLA_GL_DET(GLH.JE_HEADER_ID,GLH.JE_SOURCE,GLL.JE_LINE_NUM,'TXN_DATE')TXN_DATE
F_GET_XLA_GL_DET(GLH.JE_HEADER_ID,GLH.JE_SOURCE,GLL.JE_LINE_NUM,'TXN_NUMBER') TXN_NUMBER
.F_GET_XLA_GL_DET(GLH.JE_HEADER_ID,GLH.JE_SOURCE,GLL.JE_LINE_NUM,'EVENT_TYPE') EVENT_TYPE

1. GL to AR Receipts.

SELECT DISTINCT HZCA.ACCOUNT_NUMBER
FROM apps.ar_cash_receipts_all acra,
xla.xla_transaction_entities xte,
xla.xla_ae_lines xal,
xla.xla_ae_headers xah,
apps.gl_code_combinations glcc,
apps.gl_import_references gir,
apps.gl_je_lines gjl,
apps.gl_je_headers gjh,
apps.gl_ledgers gl,
apps.gl_balances gb,
apps.ar_customers ac,
apps.gl_je_batches gjb,
APPS.HZ_CUST_ACCOUNTS_ALL HZCA
WHERE 1 = 1
AND HZCA.CUST_ACCOUNT_ID = ACRA.PAY_FROM_CUSTOMER
AND acra.customer_site_use_id = ac.customer_id(+)
AND xte.ledger_id = gl.ledger_id
AND xte.entity_code = 'RECEIPTS'
AND NVL (xte.source_id_int_1, -99) = acra.cash_receipt_id
AND xte.application_id = xal.application_id
AND xte.entity_id = xah.entity_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.application_id = xah.application_id
AND xal.code_combination_id = glcc.code_combination_id
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xal.gl_sl_link_table = gir.gl_sl_link_table
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjl.je_header_id = gjh.je_header_id
AND gjh.ledger_id = gl.ledger_id
AND gb.code_combination_id = glcc.code_combination_id
AND gb.period_name = gjh.period_name
AND gb.currency_code = gl.currency_code
AND gjh.je_batch_id = gjb.je_batch_id 
AND gjh.je_source = 'Receivables'
AND Gjh.je_header_id = P_JE_HEADER_ID
AND ROWNUM = 1;

2. GL to AP Payments

SELECT DISTINCT SEGMENT1
INTO L_RETURN
--,ACA.*
FROM ap_invoices_all aia,
ap_invoice_payments_all aipa,
ap_checks_all aca,
xla.xla_transaction_entities xte,
xla_ae_headers xah,
xla_ae_lines xal,
gl.gl_import_references gir,
gl_je_lines gjl,
gl_je_headers gjh,
AP_SUPPLIERS APS
WHERE 1 = 1
AND APS.VENDOR_ID = AIA.VENDOR_ID
AND aia.invoice_id = aipa.invoice_id
AND aipa.check_id = aca.check_id
AND aca.check_id = NVL (SOURCE_ID_INT_1, (-99))
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.application_id = 200
AND xte.entity_id = xah.entity_id
AND xah.ae_header_id = xal.ae_header_id
AND xal.gl_sl_link_id = gir.gl_sl_link_id
AND xal.gl_sl_link_table = gir.gl_sl_link_table
AND gir.je_header_id = gjl.je_header_id
AND gir.je_line_num = gjl.je_line_num
AND gjl.je_header_id = gjh.je_header_id
AND GJH.JE_HEADER_ID = P_JE_HEADER_ID
AND ROWNUM = 1

;

3. GL to COGS AR/SO (Sales Order/AR Trx)

SELECT DISTINCT HCAL.ACCOUNT_NUMBER
  /*SELECT * RCTA.* (CHECK INTERFACE HEADER_ ATTRIBUTES FOR SALES ORDER DETAILS)
  SELECT OEH.ORDER_NUMBER, rac.INTERFACE_LINE_ATTRIBUTE1||'-'||rac.INTERFACE_LINE_ATTRIBUTE2 ||'-'||INTERFACE_LINE_CONTEXT
  ,RAC.SALES_ORDER
  FROM  OE_ORDER_HEADERS_ALL OEH,
   RA_CUSTOMER_TRX_LINES_ALL RAC
  WHERE order_number = '20487' AND
  TO_CHAR(OEH.order_number) = RAC.interface_line_attribute1;
  */
  FROM RA_CUSTOMER_TRX_ALL           RCTA,
       RA_CUST_TRX_LINE_GL_DIST_ALL  RCTG,
       XLA.XLA_TRANSACTION_ENTITIES  XTE,
       XLA.XLA_EVENTS                XE,
       XLA.XLA_DISTRIBUTION_LINKS    XDL,
       XLA.XLA_AE_LINES              XAL,
       XLA_AE_HEADERS                XAH,
       GL_JE_LINES                   GJL,
       GL_IMPORT_REFERENCES          GIR,
       GL_JE_HEADERS                 GJH,
       HZ_CUST_ACCOUNTS_ALL          HCAL
 WHERE     RCTA.CUSTOMER_TRX_ID = RCTG.CUSTOMER_TRX_ID
       AND HCAL.CUST_ACCOUNT_ID = RCTA.BILL_TO_CUSTOMER_ID
       AND RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1
       AND XTE.ENTITY_ID = XE.ENTITY_ID
       AND RCTG.CUST_TRX_LINE_GL_DIST_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
       AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
       AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID
       --AND XAH.AE_HEADER_ID = 423102
       AND XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID
       AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
       AND GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
       AND GIR.JE_HEADER_ID = GJH.JE_HEADER_ID
       AND gjh.je_header_id = P_JE_HEADER_ID
       AND ROWNUM = 1;

to be more breakdown.

Query 1: For complete Transaction
SELECT *
FROM
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTG,
XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XE
WHERE
RCTA.CUSTOMER_TRX_ID = RCTG.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1
AND XTE.ENTITY_ID = XE.ENTITY_ID

Query 2: After Create accounting Run
SELECT *
FROM
RA_CUST_TRX_LINE_GL_DIST_ALL RCTG,
XLA_DISTRIBUTION_LINKS XDL,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH
WHERE
RCTG.CUST_TRX_LINE_GL_DIST_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID

Query 3 : After Run Transfer to GL
SELECT *
FROM
XLA_AE_LINES XAL,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH
WHERE
XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GIR.JE_HEADER_ID = GJH.JE_HEADER_ID

4. GL to Move Order Details.

select distinct mtrh.request_number  ,MTL.SUBINVENTORY_CODE, MTL.TRANSFER_SUBINVENTORY  FROM mtl_txn_request_headers mtrh,  mtl_txn_request_lines mtrl  , MTL_MATERIAL_TRANSACTIONS MTL WHERE mtrh.header_id = mtrl.header_id  AND mtrh.organization_id = mtrl.organization_id  AND mtrl.line_id = MTL.move_order_line_id   AND MTL.TRANSACTION_ID = :TXN_NUMBER;

5. GL to WIP(Job Number)

SELECT WIP_ENTITY_NAME   INTO L_RETURN FROM MTL_MATERIAL_TRANSACTIONS MMT  ,WIP_ENTITIES WIP WHERE WIP.WIP_ENTITY_ID = MMT.TRANSACTION_SOURCE_ID  AND TRANSACTION_ID = :TXN_NUMBER AND ROWNUM =1; 

SELECT WIP_ENTITY_NAME JOB_NUMBER   FROM WIP_ENTITIES WIP  WHERE WIP.WIP_ENTITY_ID IN (SELECT * FROM WIP_TRANSACTIONS WHERE TRANSACTION_ID IN (SELECT SOURCE_ID_INT_1 FROM XLA_TRANSACTION_ENTITIES_UPG WHERE ENTITY_ID IN ( SELECT  ENTITY_ID FROM XLA_EVENTS WHERE EVENT_ID IN ( SELECT EVENT_ID FROM XLA_AE_HEADERS WHERE APPLICATION_ID=707 AND AE_HEADER_ID IN (SELECT AE_HEADER_ID  FROM XLA_AE_LINES WHERE APPLICATION_ID=707 AND GL_SL_LINK_ID IN (SELECT  GL_SL_LINK_ID FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID=&JE_HEADER_ID)))));

SELECT * FROM WIP_TRANSACTION_ACCOUNTS WHERE TRANSACTION_ID IN (SELECT SOURCE_ID_INT_1 FROM XLA_TRANSACTION_ENTITIES_UPG WHERE ENTITY_ID IN ( SELECT  ENTITY_ID FROM XLA_EVENTS WHERE EVENT_ID IN ( SELECT EVENT_ID FROM XLA_AE_HEADERS WHERE APPLICATION_ID=707 AND AE_HEADER_ID IN (SELECT AE_HEADER_ID  FROM XLA_AE_LINES WHERE APPLICATION_ID=707 AND GL_SL_LINK_ID IN (SELECT  GL_SL_LINK_ID FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID=&JE_HEADER_ID)))))

6. GL to Inventory(Material Txn)

SELECT * FROM MTL_MATERIAL_TRANSACTIONS WHERE TRANSACTION_ID IN (SELECT SOURCE_ID_INT_1 FROM XLA.XLA_TRANSACTION_ENTITIES WHERE ENTITY_ID IN ( SELECT  ENTITY_ID FROM XLA_EVENTS WHERE EVENT_ID IN ( SELECT EVENT_ID FROM XLA_AE_HEADERS WHERE APPLICATION_ID=707 AND AE_HEADER_ID IN (SELECT AE_HEADER_ID  FROM XLA_AE_LINES WHERE APPLICATION_ID=707 AND GL_SL_LINK_ID IN (SELECT  GL_SL_LINK_ID FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID=&JE_HEADER_ID)))))

SELECT * FROM MTL_TRANSACTION_ACCOUNTS WHERE TRANSACTION_ID IN (SELECT SOURCE_ID_INT_1 FROM XLA.XLA_TRANSACTION_ENTITIES WHERE ENTITY_ID IN ( SELECT  ENTITY_ID FROM XLA_EVENTS WHERE EVENT_ID IN ( SELECT EVENT_ID FROM XLA_AE_HEADERS WHERE APPLICATION_ID=707 AND AE_HEADER_ID IN (SELECT AE_HEADER_ID  FROM XLA_AE_LINES WHERE APPLICATION_ID=707 AND GL_SL_LINK_ID IN (SELECT  GL_SL_LINK_ID FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID=&JE_HEADER_ID)))))

7. GL to Receipts (Inv RCV)

select *  from rcv_transactions where transaction_id in(select source_id_int_1   from XLA_TRANSACTION_ENTITIES_UPG  WHERE ENTITY_ID IN  (SELECT ENTITY_ID  FROM XLA_EVENTS WHERE EVENT_ID IN (SELECT EVENT_ID    FROM XLA_AE_HEADERS   WHERE APPLICATION_ID = 707  AND AE_HEADER_ID IN      (SELECT AE_HEADER_ID   FROM XLA_AE_LINES  WHERE APPLICATION_ID = 707    AND GL_SL_LINK_ID IN (SELECT GL_SL_LINK_ID  FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID = &JE_HEADER_ID)))))

select *  from RCV_ACCOUNTING_EVENTS where RCV_TRANSACTION_ID in(select source_id_int_1   from XLA_TRANSACTION_ENTITIES_UPG  WHERE ENTITY_ID IN  (SELECT ENTITY_ID  FROM XLA_EVENTS WHERE EVENT_ID IN (SELECT EVENT_ID    FROM XLA_AE_HEADERS   WHERE APPLICATION_ID = 707  AND AE_HEADER_ID IN      (SELECT AE_HEADER_ID   FROM XLA_AE_LINES  WHERE APPLICATION_ID = 707    AND GL_SL_LINK_ID IN (SELECT GL_SL_LINK_ID  FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID = &JE_HEADER_ID)))))

SELECT * FROM RCV_RECEIVING_SUB_LEDGER WHERE RCV_TRANSACTION_ID IN (select source_id_int_1   from XLA_TRANSACTION_ENTITIES_UPG  WHERE ENTITY_ID IN  (SELECT ENTITY_ID  FROM XLA_EVENTS WHERE EVENT_ID IN (SELECT EVENT_ID    FROM XLA_AE_HEADERS   WHERE APPLICATION_ID = 707  AND AE_HEADER_ID IN      (SELECT AE_HEADER_ID   FROM XLA_AE_LINES  WHERE APPLICATION_ID = 707    AND GL_SL_LINK_ID IN (SELECT GL_SL_LINK_ID  FROM GL_IMPORT_REFERENCES WHERE JE_HEADER_ID = &JE_HEADER_ID)))))

Subledger Accounting Diagnostic Scripts for Cost Management diagnose flow between Inventory/WIP/Receiving and GL through SLA (Doc ID 1372269.1)

Oracle apps EBS Non-Recurring Element and Fast Formula

 

1. 1.       Define new element as below with effective date.

2. 2.       Element Link, attach to the payroll and costing information.

      

Fa.

3. 3. Fast Formula

DEFAULT FOR AMOUNT IS 0

INPUTS ARE AMOUNT

RESULT = AMOUNT

RETURN RESULT

  4. 4.       Formula Results.

4.

 

Add to the element entry of employee and run the query pay of any employees.

How the amount gets deducted auto form the net Salary?

Because its get added to default Net balance as Subtract.