Function to calculate number of absence days in the year per month and calculation (Maternity Leave)

 number of days of given month and calc for maternity leave. 

for 45 + 45 days of maternity leave , employee is eligible for 52.5 days full Paid and then no paid. 

The leave periods are as follows:

First period: 19th Aug 2024 to 2nd Oct 2024 (45 days)

Second period: 3rd Oct 2024 to 16th Nov 2024 (45 days)

August month 13 days full paid., 0 days unpaid

September month 30 days full paid , 0 days unpaid

October month 2 days full paid, 15 days half paid. i.e., total fullPaid 9.5 days ( 2 + 15/2), unpaid 21.5 days

November month 0 days paid. i.e., 30 days unpaid

FUNCTION GET_MATERNITY_UNPAID_DAYS_F (p_assignment_id   NUMBER,
                                      P_PROC_START      DATE)
    RETURN NUMBER
AS
    l_days_taken    NUMBER := 0;
    l_days_unpaid   NUMBER := 0;
    p_month         NUMBER := TO_NUMBER (TO_CHAR (P_PROC_START, 'mm'));
    p_year          NUMBER := TO_NUMBER (TO_CHAR (P_PROC_START, 'yyyy'));
BEGIN
   <<outer_loop>>
    FOR rec
        IN (  SELECT date_start, date_end
                FROM per_absence_attendances
               WHERE     person_id = (SELECT person_id
                                        FROM per_assignments_x
                                       WHERE assignment_id = p_assignment_id)
                     AND ABSENCE_ATTENDANCE_TYPE_ID = 68
                     AND date_start >=
                         TO_DATE ('01-01-' || TO_CHAR (p_year), 'dd-mm-yyyy')
                     AND date_start <
                         TO_DATE ('01-01-' || TO_CHAR (p_year + 1),
                                  'dd-mm-yyyy')
                     AND date_start <=
                         ADD_MONTHS (
                             TO_DATE (
                                    '01-'
                                 || TO_CHAR (p_month)
                                 || '-'
                                 || TO_CHAR (p_year),
                                 'dd-mm-yyyy'),
                             1) --ignore anything that starts after month in question
            ORDER BY date_start)
    LOOP
        FOR rec2 IN (    SELECT rec.date_start + ROWNUM - 1     AS calc_date
                           FROM DUAL
                     CONNECT BY LEVEL <= rec.date_end - rec.date_start + 1)
        LOOP
            l_days_taken := l_days_taken + 1;

            IF TRUNC (rec2.calc_date, 'MM') =
               ADD_MONTHS (
                   TO_DATE ('01-' || p_month || '-' || p_year, 'dd-mm-yyyy'),
                   1)
            THEN
                EXIT outer_loop;          --gone past month in question, stop.
            ELSIF TRUNC (rec2.calc_date, 'MM') =
                  TO_DATE ('01-' || p_month || '-' || p_year, 'dd-mm-yyyy')
            THEN
                IF l_days_taken = 53
                THEN
                    --hit the end of the allocation here, so half day
                    l_days_unpaid := l_days_unpaid + 0.5;
                ELSIF l_days_taken >= 54
                THEN
                    --past the last half day, take a full day unpaid
                    l_days_unpaid := l_days_unpaid + 1;
                END IF;
            END IF;
        END LOOP;
    END LOOP;

    RETURN l_days_unpaid;
END; 

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)