Oracle R12 GL Opening Debit Credit Closing Query

---GL Opening Debit Credit Closing balance 

 Select bb.ACCOUNT_CODE,bb.ACCOUNT_DECS,bb.GL_DATE,aa.open_balance,bb.ACCOUNTED_DR, bb.ACCOUNTED_CR,(aa.open_balance-bb.net_balance)Closing_bal

FROM

(SELECT  

 gcc.segment2 account_code,

       sum(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0) ) open_balance

  FROM gl_je_lines gjl,

       gl_je_headers gjh,

       gl_je_batches gjb,

       gl_code_combinations gcc

 WHERE 1=1 

   AND gjl.je_header_id = gjh.je_header_id

   AND gjl.code_combination_id = gcc.code_combination_id

   AND gjl.status = 'P'

   AND gjh.LEDGER_ID =2042

   AND gjb.je_batch_id = gjh.je_batch_id

   AND TRUNC (gjl.effective_date) < :p_start_date

   --AND TRUNC (gjl.effective_date) <= :p_end_date

Group By gcc.segment2)aa

,(SELECT  --gjb.name batch_name, gjh.je_source, gjh.je_category, 

 gcc.segment2 account_code,TRUNC (gjl.effective_date) GL_DATE,

(SELECT  A4.DESCRIPTION   DECS

from fnd_flex_values_vl A4

WHERE  a4.flex_value=gcc.segment2

And a4.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID

                            from fnd_id_flex_segments

                           WHERE application_id = 101 AND id_flex_code = 'GL#' and enabled_flag = 'Y'

                           and application_column_name='SEGMENT2'))account_DECS,                         

       sum(NVL(gjl.accounted_dr,0)) accounted_dr, sum(NVL(gjl.accounted_cr,0)) accounted_cr,

       sum(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0) ) net_balance

  FROM gl_je_lines gjl,

       gl_je_headers gjh,

       gl_je_batches gjb,

       gl_code_combinations gcc

 WHERE 1=1 

   AND gjl.je_header_id = gjh.je_header_id

   AND gjl.code_combination_id = gcc.code_combination_id

   AND gjl.status = 'P'

   AND gjh.LEDGER_ID =2042

   AND gjb.je_batch_id = gjh.je_batch_id

   AND TRUNC (gjl.effective_date) >= :p_start_date

   AND TRUNC (gjl.effective_date) <= :p_end_date

Group By gcc.segment2,TRUNC (gjl.effective_date)) bb

Where aa.ACCOUNT_CODE=bb.ACCOUNT_CODE

--AND bb.ACCOUNT_CODE='732183'

Order By 1;

Oracle EBS R12 Trial Balance Query

--Oracle EBS R12 GL Trial Balance Script

SELECT 

BRANCH,BRANCH_NAME,

ACCOUNT_CODE,ACCOUNT_CODE_DESC,

LOB_CODE, LOB_DESC, 

PRODUCT_CODE, PRODUCT_DESC, 

CONCATENATED_SEGMENTS,

LEDGER_NAME,

TO_CHAR(SYSDATE,'DD-MON-RRRR') RUN_DATE,

SUM(BEGIN_BALANCE) OP_BAL,

SUM(PERIOD_DR) ACCOUNTED_DR,

SUM(PERIOD_CR) ACCOUNTED_CR,

SUM(BEGIN_BALANCE + PERIOD_DR - PERIOD_CR) CL_BAL

FROM

(SELECT

       (SELECT NAME FROM GL_LEDGERS

        WHERE LEDGER_ID = BAL.LEDGER_ID)LEDGER_NAME, 

BAL.LEDGER_ID,

GCC.SEGMENT1 BRANCH,

(SELECT DESCRIPTION FROM FND_LOOKUP_VALUES

  WHERE LOOKUP_TYPE = 'BBL_GL_FC_BRANCH_MAP'

  AND MEANING = GCC.SEGMENT1) BRANCH_NAME, 

GCC.SEGMENT2 ACCOUNT_CODE,

(SELECT ffvl.DESCRIPTION

  FROM apps.FND_FLEX_VALUES_VL FFVL,

apps.FND_FLEX_VALUE_SETS FFVS

  WHERE FFVL.FLEX_VALUE        =gcc.segment2

  AND FFVL.FLEX_VALUE_SET_ID   =FFVS.FLEX_VALUE_SET_ID

  AND FFVS.FLEX_VALUE_SET_NAME = 'JG_Natural Account'

  ) ACCOUNT_CODE_DESC,   

SEGMENT4 LOB_CODE,

(SELECT ffvl.DESCRIPTION

  FROM apps.FND_FLEX_VALUES_VL FFVL,

apps.FND_FLEX_VALUE_SETS FFVS

  WHERE FFVL.FLEX_VALUE        = gcc.SEGMENT4

  AND FFVL.FLEX_VALUE_SET_ID   = FFVS.FLEX_VALUE_SET_ID

  AND FFVS.FLEX_VALUE_SET_NAME = 'JG_Line of Business'

  ) LOB_DESC,   

SEGMENT5 PRODUCT_CODE,

(SELECT ffvl.DESCRIPTION

  FROM apps.FND_FLEX_VALUES_VL FFVL,

apps.FND_FLEX_VALUE_SETS FFVS

  WHERE FFVL.FLEX_VALUE        = gcc.segment5

  AND FFVL.FLEX_VALUE_SET_ID   = FFVS.FLEX_VALUE_SET_ID

  AND FFVS.FLEX_VALUE_SET_NAME = 'JG_Product'

  ) PRODUCT_DESC,   

  GCC.CONCATENATED_SEGMENTS,

GCC.CHART_OF_ACCOUNTS_ID,

BAL.PERIOD_NAME,

    decode ( :P_PERIOD_TYPE , 'PTD' , SUM(DECODE(:P_CURRENCY_TYPE, 

                                          'T', NVL(BEGIN_BALANCE_DR,0) - NVL(BEGIN_BALANCE_CR,0),

                                          'S', NVL(BEGIN_BALANCE_DR,0) - NVL(BEGIN_BALANCE_CR,0),                                            

                                          'E', DECODE(BAL.TRANSLATED_FLAG,

                                                     'R', NVL(BEGIN_BALANCE_DR,0) - NVL(BEGIN_BALANCE_CR,0),                                    

                                                     NVL(BEGIN_BALANCE_DR_BEQ,0) - NVL(BEGIN_BALANCE_CR_BEQ,0)))),

                  'PJTD', SUM(DECODE( :P_CURRENCY_TYPE, 'T', 0,

                                                                                                'S', 0,

                                                                                                'E', 0)),

                  'YTD', DECODE(:P_CURRENCY_TYPE,

                                      'T', SUM(DECODE(BAL.PERIOD_NAME, (SELECT  a.period_name 

                                                        FROM    gl_period_statuses a, gl_period_statuses b

                                                        WHERE   a.application_id = 101

                                                        AND     b.application_id = 101

                                                        AND     a.ledger_id = BAL.ledger_id

                                                        AND     b.ledger_id = BAL.ledger_id

                                                        AND     a.period_type = b.period_type

                                                        AND     a.period_year = b.period_year

                                                        AND     b.period_name = :P_PERIOD_NAME

                                                        AND     a.period_num =

                                                               (SELECT min(c.period_num)

                                                                  FROM gl_period_statuses c

                                                                 WHERE c.application_id = 101

                                                                   AND c.ledger_id = BAL.ledger_id

                                                                   AND c.period_year = a.period_year

                                                                   AND c.period_type = a.period_type

                                                              GROUP BY c.period_year)),       

                                                 NVL(BEGIN_BALANCE_DR,0) - NVL(BEGIN_BALANCE_CR,0),0)),

                                      'S', SUM(DECODE(BAL.PERIOD_NAME, (SELECT  a.period_name 

    FROM    gl_period_statuses a, gl_period_statuses b

    WHERE   a.application_id = 101

    AND     b.application_id = 101

    AND     a.ledger_id = BAL.ledger_id

    AND     b.ledger_id = BAL.ledger_id

    AND     a.period_type = b.period_type

    AND     a.period_year = b.period_year

    AND     b.period_name = :P_PERIOD_NAME

    AND     a.period_num =

           (SELECT min(c.period_num)

              FROM gl_period_statuses c

             WHERE c.application_id = 101

               AND c.ledger_id =BAL.ledger_id

               AND c.period_year = a.period_year

               AND c.period_type = a.period_type

          GROUP BY c.period_year)), 

                                                 NVL(BEGIN_BALANCE_DR,0) - NVL(BEGIN_BALANCE_CR,0),0)),

                                      'E', SUM(DECODE(BAL.TRANSLATED_FLAG,

                                                       'R',DECODE(BAL.PERIOD_NAME, (SELECT  a.period_name 

    FROM    gl_period_statuses a, gl_period_statuses b

    WHERE   a.application_id = 101

    AND     b.application_id = 101

    AND     a.ledger_id = BAL.ledger_id

    AND     b.ledger_id = BAL.ledger_id

    AND     a.period_type = b.period_type

    AND     a.period_year = b.period_year

    AND     b.period_name = :P_PERIOD_NAME

    AND     a.period_num =

           (SELECT min(c.period_num)

              FROM gl_period_statuses c

             WHERE c.application_id = 101

               AND c.ledger_id =BAL.ledger_id

               AND c.period_year = a.period_year

               AND c.period_type = a.period_type

          GROUP BY c.period_year)),

                                 NVL(BEGIN_BALANCE_DR,0) - NVL(BEGIN_BALANCE_CR,0),0),

                                                            DECODE(BAL.PERIOD_NAME, (SELECT  a.period_name 

FROM    gl_period_statuses a, gl_period_statuses b

WHERE   a.application_id = 101

AND     b.application_id = 101

AND     a.ledger_id = BAL.ledger_id

AND     b.ledger_id = BAL.ledger_id

AND     a.period_type = b.period_type

AND     a.period_year = b.period_year

AND     b.period_name = :P_PERIOD_NAME

AND     a.period_num =

   (SELECT min(c.period_num)

  FROM gl_period_statuses c

WHERE c.application_id = 101

   AND c.ledger_id =BAL.ledger_id

   AND c.period_year = a.period_year

   AND c.period_type = a.period_type

  GROUP BY c.period_year)),

                                                 NVL(BEGIN_BALANCE_DR_BEQ,0) - NVL(BEGIN_BALANCE_CR_BEQ,0),0)))))                    

              BEGIN_BALANCE,

DECODE( :P_PERIOD_TYPE, 'PTD', SUM(DECODE(:P_CURRENCY_TYPE,

                                        'T', NVL(PERIOD_NET_DR,0),

                                        'S', NVL(PERIOD_NET_DR,0),

                                        'E', DECODE(BAL.TRANSLATED_FLAG, 

                                                    'R', NVL(PERIOD_NET_DR,0),

                                                          NVL(PERIOD_NET_DR_BEQ,0)))),

                  'PJTD', SUM(DECODE(:P_CURRENCY_TYPE,

                                        'T', NVL(PROJECT_TO_DATE_DR,0) + NVL(PERIOD_NET_DR,0),

                                        'S', NVL(PROJECT_TO_DATE_DR,0) + NVL(PERIOD_NET_DR,0),

                                        'E', DECODE(BAL.TRANSLATED_FLAG, 

                                                    'R', NVL(PROJECT_TO_DATE_DR,0) + NVL(PERIOD_NET_DR,0),

                                                          NVL(PROJECT_TO_DATE_DR_BEQ,0) + NVL(PERIOD_NET_DR_BEQ, 0)))),

                'YTD', DECODE(:P_CURRENCY_TYPE, 

                                    'T', SUM(DECODE(BAL.PERIOD_NAME, :P_PERIOD_NAME, 

                                               NVL(BEGIN_BALANCE_DR,0) + NVL(PERIOD_NET_DR,0),0)

                                  - DECODE(BAL.PERIOD_NAME, (SELECT  a.period_name 

    FROM    gl_period_statuses a, gl_period_statuses b

    WHERE   a.application_id = 101

    AND     b.application_id = 101

    AND     a.ledger_id = BAL.ledger_id

    AND     b.ledger_id = BAL.ledger_id

    AND     a.period_type = b.period_type

    AND     a.period_year = b.period_year

    AND     b.period_name = :P_PERIOD_NAME

    AND     a.period_num =

           (SELECT min(c.period_num)

              FROM gl_period_statuses c

             WHERE c.application_id = 101

               AND c.ledger_id =BAL.ledger_id

               AND c.period_year = a.period_year

               AND c.period_type = a.period_type

          GROUP BY c.period_year)), NVL(BEGIN_BALANCE_DR,0), 0)),

                                    'S', SUM(DECODE(BAL.PERIOD_NAME, :P_PERIOD_NAME,

                                               NVL(BEGIN_BALANCE_DR,0) + NVL(PERIOD_NET_DR,0), 0)

                                          - DECODE(BAL.PERIOD_NAME, (SELECT  a.period_name 

    FROM    gl_period_statuses a, gl_period_statuses b

    WHERE   a.application_id = 101

    AND     b.application_id = 101

    AND     a.ledger_id = BAL.ledger_id

    AND     b.ledger_id = BAL.ledger_id

    AND     a.period_type = b.period_type

    AND     a.period_year = b.period_year

    AND     b.period_name = :P_PERIOD_NAME

    AND     a.period_num =

           (SELECT min(c.period_num)

              FROM gl_period_statuses c

             WHERE c.application_id = 101

               AND c.ledger_id =BAL.ledger_id

               AND c.period_year = a.period_year

               AND c.period_type = a.period_type

          GROUP BY c.period_year)), NVL(BEGIN_BALANCE_DR,0),0)),

                                    'E', SUM(DECODE(BAL.TRANSLATED_FLAG,

                                                     'R', DECODE(BAL.PERIOD_NAME, :P_PERIOD_NAME, 

                                                           NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR,0),0)

                                                           - DECODE(BAL.PERIOD_NAME, (SELECT  a.period_name 

    FROM    gl_period_statuses a, gl_period_statuses b

    WHERE   a.application_id = 101

    AND     b.application_id = 101

    AND     a.ledger_id = BAL.ledger_id

    AND     b.ledger_id = BAL.ledger_id

    AND     a.period_type = b.period_type

    AND     a.period_year = b.period_year

    AND     b.period_name = :P_PERIOD_NAME

    AND     a.period_num =

           (SELECT min(c.period_num)

              FROM gl_period_statuses c

             WHERE c.application_id = 101

               AND c.ledger_id =BAL.ledger_id

               AND c.period_year = a.period_year

               AND c.period_type = a.period_type

          GROUP BY c.period_year)), 

                                                                    NVL(BEGIN_BALANCE_DR, 0), 0),

                                                         DECODE(BAL.PERIOD_NAME, :P_PERIOD_NAME,

                                                           NVL(BEGIN_BALANCE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ,0),0)

                                                           - DECODE(BAL.PERIOD_NAME, (SELECT  a.period_name 

    FROM    gl_period_statuses a, gl_period_statuses b

    WHERE   a.application_id = 101

    AND     b.application_id = 101

    AND     a.ledger_id = BAL.ledger_id

    AND     b.ledger_id = BAL.ledger_id

    AND     a.period_type = b.period_type

    AND     a.period_year = b.period_year

    AND     b.period_name = :P_PERIOD_NAME

    AND     a.period_num =

           (SELECT min(c.period_num)

              FROM gl_period_statuses c

             WHERE c.application_id = 101

               AND c.ledger_id =BAL.ledger_id

               AND c.period_year = a.period_year

               AND c.period_type = a.period_type

          GROUP BY c.period_year)), 

                                                                    NVL(BEGIN_BALANCE_DR_BEQ,0), 0)))))

                        PERIOD_DR,

DECODE( :P_PERIOD_TYPE, 'PTD', SUM(DECODE(:P_CURRENCY_TYPE,

                                        'T', NVL(PERIOD_NET_CR,0),

                                        'S', NVL(PERIOD_NET_CR,0),

                                        'E', DECODE(BAL.TRANSLATED_FLAG,

                                                'R', NVL(PERIOD_NET_CR,0),

                                                          NVL(PERIOD_NET_CR_BEQ,0)))),

                'PJTD', SUM(DECODE(:P_CURRENCY_TYPE,

                                        'T', NVL(PROJECT_TO_DATE_CR,0) + NVL(PERIOD_NET_CR,0),

                                        'S', NVL(PROJECT_TO_DATE_CR,0) + NVL(PERIOD_NET_CR,0),

                                        'E', DECODE(BAL.TRANSLATED_FLAG,

                                                    'R', NVL(PROJECT_TO_DATE_CR,0) + NVL(PERIOD_NET_CR,0),

                                                          NVL(PROJECT_TO_DATE_CR_BEQ,0) + NVL(PERIOD_NET_CR_BEQ, 0)))),

                'YTD', DECODE(:P_CURRENCY_TYPE,

                                    'T', SUM(DECODE(BAL.PERIOD_NAME,  :P_PERIOD_NAME,

                                               NVL(BEGIN_BALANCE_CR,0) + NVL(PERIOD_NET_CR, 0), 0)

                                         - DECODE(BAL.PERIOD_NAME, (SELECT  a.period_name 

    FROM    gl_period_statuses a, gl_period_statuses b

    WHERE   a.application_id = 101

    AND     b.application_id = 101

    AND     a.ledger_id = BAL.ledger_id

    AND     b.ledger_id = BAL.ledger_id

    AND     a.period_type = b.period_type

    AND     a.period_year = b.period_year

    AND     b.period_name = :P_PERIOD_NAME

    AND     a.period_num =

           (SELECT min(c.period_num)

              FROM gl_period_statuses c

             WHERE c.application_id = 101

               AND c.ledger_id =BAL.ledger_id

               AND c.period_year = a.period_year

               AND c.period_type = a.period_type

          GROUP BY c.period_year)), NVL(BEGIN_BALANCE_CR,0), 0)),

                                    'S', SUM(DECODE(BAL.PERIOD_NAME, :P_PERIOD_NAME,

                                               NVL(BEGIN_BALANCE_CR,0) + NVL(PERIOD_NET_CR, 0), 0)

                                        - DECODE(BAL.PERIOD_NAME, (SELECT  a.period_name 

    FROM    gl_period_statuses a, gl_period_statuses b

    WHERE   a.application_id = 101

    AND     b.application_id = 101

    AND     a.ledger_id = BAL.ledger_id

    AND     b.ledger_id = BAL.ledger_id

    AND     a.period_type = b.period_type

    AND     a.period_year = b.period_year

    AND     b.period_name = :P_PERIOD_NAME

    AND     a.period_num =

           (SELECT min(c.period_num)

              FROM gl_period_statuses c

             WHERE c.application_id = 101

               AND c.ledger_id =BAL.ledger_id

               AND c.period_year = a.period_year

               AND c.period_type = a.period_type

          GROUP BY c.period_year)), NVL(BEGIN_BALANCE_CR, 0), 0)),

                                    'E', SUM(DECODE(BAL.TRANSLATED_FLAG,

                                                    'R', DECODE(BAL.PERIOD_NAME, :P_PERIOD_NAME,

                                                           NVL(BEGIN_BALANCE_CR,0) + NVL(PERIOD_NET_CR, 0), 0)

                                                           - DECODE(BAL.PERIOD_NAME, (SELECT  a.period_name 

    FROM    gl_period_statuses a, gl_period_statuses b

    WHERE   a.application_id = 101

    AND     b.application_id = 101

    AND     a.ledger_id = BAL.ledger_id

    AND     b.ledger_id = BAL.ledger_id

    AND     a.period_type = b.period_type

    AND     a.period_year = b.period_year

    AND     b.period_name = :P_PERIOD_NAME

    AND     a.period_num =

           (SELECT min(c.period_num)

              FROM gl_period_statuses c

             WHERE c.application_id = 101

               AND c.ledger_id =BAL.ledger_id

               AND c.period_year = a.period_year

               AND c.period_type = a.period_type

          GROUP BY c.period_year)),

                                                                    NVL(BEGIN_BALANCE_CR, 0), 0),

                                                         DECODE(BAL.PERIOD_NAME, :P_PERIOD_NAME, 

                                                           NVL(BEGIN_BALANCE_CR_BEQ,0) + NVL(PERIOD_NET_CR_BEQ, 0), 0)

                                                           - DECODE(BAL.PERIOD_NAME, (SELECT  a.period_name 

    FROM    gl_period_statuses a, gl_period_statuses b

    WHERE   a.application_id = 101

    AND     b.application_id = 101

    AND     a.ledger_id = BAL.ledger_id

    AND     b.ledger_id = BAL.ledger_id

    AND     a.period_type = b.period_type

    AND     a.period_year = b.period_year

    AND     b.period_name = :P_PERIOD_NAME

    AND     a.period_num =

           (SELECT min(c.period_num)

              FROM gl_period_statuses c

             WHERE c.application_id = 101

               AND c.ledger_id =BAL.ledger_id

               AND c.period_year = a.period_year

               AND c.period_type = a.period_type

          GROUP BY c.period_year)),

        NVL(BEGIN_BALANCE_CR_BEQ,0),0)))))

                        PERIOD_CR

FROM

GL_BALANCES               BAL,

GL_CODE_COMBINATIONS_KFV  GCC,

GL_LEDGERS                GL

WHERE 1=1

AND BAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

AND BAL.LEDGER_ID = GL.LEDGER_ID

AND BAL.ACTUAL_FLAG = 'A'

AND BAL.TEMPLATE_ID IS NULL

AND BAL.ledger_id = nvl(:p_ledger, BAL.ledger_id) --2063

    AND BAL.currency_code = nvl(:p_currency, BAL.currency_code)

     AND BAL.PERIOD_NAME IN (:P_PERIOD_NAME,

                              DECODE(:P_PERIOD_TYPE, 'PTD', :P_PERIOD_NAME, 

                                                    'PJTD', :P_PERIOD_NAME,

                                                    'YTD', (SELECT  a.period_name 

FROM    gl_period_statuses a, gl_period_statuses b

WHERE   a.application_id = 101

AND     b.application_id = 101

AND     a.ledger_id = BAL.ledger_id

AND     b.ledger_id = BAL.ledger_id

AND     a.period_type = b.period_type

AND     a.period_year = b.period_year

AND     b.period_name = :P_PERIOD_NAME

AND     a.period_num =

   (SELECT min(c.period_num)

  FROM gl_period_statuses c

WHERE c.application_id = 101

   AND c.ledger_id =BAL.ledger_id

   AND c.period_year = a.period_year

   AND c.period_type = a.period_type

  GROUP BY c.period_year)) ))

AND EXISTS (

SELECT 1 

FROM apps.gl_code_combinations_kfv gcck

WHERE gcc.code_combination_id = gcck.code_combination_id

AND segment1 BETWEEN substr(:P_MIN_FLEX, 1, 7) AND substr(:P_MAX_FLEX, 1, 7)

AND segment2 BETWEEN substr(:P_MIN_FLEX, 9, 6) AND substr(:P_MAX_FLEX, 9, 6)

AND segment3 BETWEEN substr(:P_MIN_FLEX, 16, 3) AND substr(:P_MAX_FLEX, 16, 3)

AND segment4 BETWEEN substr(:P_MIN_FLEX, 20, 3) AND substr(:P_MAX_FLEX, 20, 3)

AND segment5 BETWEEN substr(:P_MIN_FLEX, 24, 4) AND substr(:P_MAX_FLEX, 24, 4)

AND segment6 BETWEEN substr(:P_MIN_FLEX, 29, 3) AND substr(:P_MAX_FLEX, 29, 3)

AND segment7 BETWEEN substr(:P_MIN_FLEX, 33, 7) AND substr(:P_MAX_FLEX, 33, 7)

AND segment8 BETWEEN substr(:P_MIN_FLEX, 41, 2) AND substr(:P_MAX_FLEX, 41, 2)

AND segment9 BETWEEN substr(:P_MIN_FLEX, 44, 3) AND substr(:P_MAX_FLEX, 44, 3)

AND segment10 BETWEEN substr(:P_MIN_FLEX, 48, 4) AND substr(:P_MAX_FLEX, 48, 4)

AND segment11 BETWEEN substr(:P_MIN_FLEX, 53, 5) AND substr(:P_MAX_FLEX, 53, 5)

            )

GROUP BY

BAL.LEDGER_ID,

GCC.SEGMENT1,

GCC.SEGMENT2,

gcc.SEGMENT4,gcc.SEGMENT5,

GCC.CHART_OF_ACCOUNTS_ID,

BAL.PERIOD_NAME,

    GCC.CONCATENATED_SEGMENTS)

WHERE 1=1

GROUP BY 

BRANCH,BRANCH_NAME,

ACCOUNT_CODE,ACCOUNT_CODE_DESC,

LOB_CODE, LOB_DESC, 

PRODUCT_CODE, PRODUCT_DESC, 

CONCATENATED_SEGMENTS,

LEDGER_NAME

HAVING SUM(BEGIN_BALANCE + PERIOD_DR - PERIOD_CR)<>0 

ORDER BY 1,3

API to create Code Combination and get CCID

----API To Create Bulk GL Code Combinations

DECLARE    

l_ledger_name       VARCHAR2(300)DEFAULT 'JGTECH_INDIA_PRIMARY_LEDGER';

l_structure_number  NUMBER;

l_sucess            BOOLEAN;

  

  CURSOR CUR 

  IS

  

      SELECT a.rowid rid,

            a.emp_code,

            a.code_combination

        FROM xx_emp_code_comb a

        WHERE  code_comb_id is null;

BEGIN

  SELECT chart_of_accounts_id

    INTO l_structure_number

    FROM gl_ledgers

   WHERE name = l_ledger_name;

   

 FOR i IN cur LOOP

  lb_sucess := fnd_flex_keyval.validate_segs

            (

             operation           => 'CREATE_COMBINATION',

             appl_short_name     => 'SQLGL',

             key_flex_code       => 'GL#',

             structure_number    => l_structure_number,

             concat_segments     => i.code_combination, 

             validation_date     => SYSDATE

            );

            

  IF l_sucess

  THEN

    DBMS_OUTPUT.PUT_LINE('Successful. Code Combination ID: '||fnd_flex_keyval.combination_id());

    UPDATE xx_emp_code_comb aa

    SET code_comb_id=fnd_flex_keyval.combination_id()

    WHERE aa.emp_code=i.emp_code;

    

  ELSE

    DBMS_OUTPUT.PUT_LINE('Error creating a Code combination id for ' ||'/i.code_combination/'||' Error:'|| fnd_flex_keyval.error_message());

  END IF;

 END LOOP;

       COMMIT;

EXCEPTION

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Unable to create a code combination for ' ||'/'||' Error:'|| SQLERRM);

END;

Oracle GL Account Value Set with Qualifier Details script

Key Flexfields segments value set details (Values/Effective/Hierarchy/Qualifiers)

  SELECT ffvs.flex_value_set_name,

         ffv.flex_value,flex_value_meaning,

         ffvt.description,

         ffv.start_date_active,

         ffv.end_date_active,

         DECODE (ffv.enabled_flag, 'N', 'No', 'Yes') enabled,

         DECODE (ffv.summary_flag, 'N', 'No', 'Yes') parent,

         ffhv.hierarchy_code rollup_group,

         -- Qualifiers

         DECODE (SUBSTR (TO_CHAR (ffv.compiled_value_attributes), 1, 1),'N', 'No','Yes') budgeting_allowed,

         DECODE (SUBSTR (TO_CHAR (ffv.compiled_value_attributes), 3, 1), 'N', 'No', 'Yes')   posting_allowed,

         (SELECT acct_type_desc2   FROM gl_acct_typ gat  WHERE gat.acct_type_code = SUBSTR (TO_CHAR (ffv.compiled_value_attributes), 5, 1))

            account_type,

         DECODE (SUBSTR (TO_CHAR (ffv.compiled_value_attributes), 7, 1), 'N', 'No', 'Yes') third_party_control,

         DECODE (SUBSTR (TO_CHAR (ffv.compiled_value_attributes), 9, 1),  'N', 'No', 'Yes') reconcile,

         value_category

    FROM fnd_flex_values ffv,

         fnd_flex_values_tl ffvt,

         fnd_flex_value_sets ffvs,

         fnd_id_flex_segments fifs,

         fnd_flex_hierarchies_vl ffhv

   WHERE     ffv.flex_value_id = ffvt.flex_value_id

         AND ffv.flex_value_set_id = ffvs.flex_value_set_id

         AND fifs.flex_value_set_id = ffvs.flex_value_set_id

         AND fifs.id_flex_code = 'GL#'

         AND ffvs.flex_value_set_name = :P_GL_ACCOUNT

--         AND ffv.flex_value_set_id =  1016027

         AND ffv.structured_hierarchy_level = ffhv.hierarchy_id(+)

ORDER BY ffv.flex_value

Oracle R12 Check the GL Period open or close

DECLARE

v_gl_period_status     NUMBER;

v_err_msg              VARCHAR2(100);

BEGIN

   SELECT 1

     INTO v_gl_period_status

     FROM gl_period_statuses a, fnd_application_vl b

    WHERE     1 = 1

          AND a.set_of_books_id = :P_set_of_books_id

          AND b.application_short_name = 'SQLGL'

          AND a.application_id = b.application_id

          AND :p_invoice_date BETWEEN a.start_date

                                  AND NVL (a.end_date, SYSDATE)

          AND NVL (a.adjustment_period_flag, 'X') = 'N'

          AND NVL (a.closing_status, '1') = 'O';

EXCEPTION

   WHEN NO_DATA_FOUND

   THEN

      v_err_msg := 'GL PERIOD NOT OPEN';

      v_gl_period_status := 0;

      DBMS_OUTPUT.PUT_LINE (v_err_msg);

   WHEN OTHERS

   THEN

      v_err_msg := 'GL PERIOD ERR ' || SQLERRM;

      v_gl_period_status := 0;

      DBMS_OUTPUT.PUT_LINE (v_err_msg);

END;

GL Accounting of Inventory Transactions Query

 /** Accounting of Inventory Transactions **/

SELECT  'INV1'    BLOCK_NUMBER
,    GJB.NAME AS JE_BATCH_NAME
,       GJL.JE_HEADER_ID
,       GJL.JE_LINE_NUM
,       GJH.JE_SOURCE
,       GJH.JE_CATEGORY
,       GJH.NAME    AS JE_NAME
,       GJH.PERIOD_NAME
,       TRUNC(GJL.EFFECTIVE_DATE) AS GL_DATE
,       TO_CHAR(MMT.TRANSACTION_ID) AS DOC_NUMBER
,       OOD.ORGANIZATION_NAME  AS PARTY_NAME
,       TO_CHAR(MMT.TRANSACTION_ID)  AS TRANSACTION_NUMBER
,       GJH.CURRENCY_CODE  AS GL_CURRENCY_CODE
,       NULL INVOICE_CURRENCY_CODE
,       GCC.CONCATENATED_SEGMENTS
,       MTT.TRANSACTION_TYPE_NAME AS NARATION
,       XAH.EVENT_TYPE_CODE
,       XAL.ENTERED_DR
,       XAL.ENTERED_CR
,       XAL.ACCOUNTED_DR
,       XAL.ACCOUNTED_CR
,       GIR.GL_SL_LINK_ID
,       XAL.CODE_COMBINATION_ID
FROM    APPS.GL_JE_LINES                                         GJL
,       APPS.GL_JE_HEADERS                                       GJH
,       APPS.GL_CODE_COMBINATIONS_KFV                            GCC
,       APPS.GL_JE_BATCHES                                       GJB
,       APPS.GL_IMPORT_REFERENCES                                GIR
,       APPS.XLA_AE_LINES                                        XAL
,       APPS.XLA_AE_HEADERS                                      XAH
,       APPS.XLA_TRANSACTION_ENTITIES_UPG                        UPG  
,       APPS.MTL_MATERIAL_TRANSACTIONS                           MMT 
,       APPS.MTL_TRANSACTION_TYPES                               MTT
,       APPS.ORG_ORGANIZATION_DEFINITIONS                        OOD                                  
WHERE GJL.JE_HEADER_ID          = GJH.JE_HEADER_ID
AND   GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM APPS.GL_CODE_COMBINATIONS_KFV 
                                  WHERE SEGMENT4 = :ACCT_CD)
AND   GJL.CODE_COMBINATION_ID   = GCC.CODE_COMBINATION_ID
AND   GJH.JE_BATCH_ID           = GJB.JE_BATCH_ID
AND   GJL.STATUS                = 'P'
AND   GJH.ACTUAL_FLAG           = 'A' 
AND   GJH.JE_SOURCE             = 'Inventory'
AND   GJH.JE_CATEGORY           <>'Receiving'
AND   TRUNC(GJL.EFFECTIVE_DATE) BETWEEN :P_START_DATE AND :P_END_DATE
AND   GJL.JE_HEADER_ID          = GIR.JE_HEADER_ID  
AND   GJH.JE_BATCH_ID           = GIR.JE_BATCH_ID
AND   GJL.JE_LINE_NUM           = GIR.JE_LINE_NUM
AND   GIR.GL_SL_LINK_ID         = XAL.GL_SL_LINK_ID
--AND   XAL.APPLICATION_ID        in (707, 555)
AND   XAL.APPLICATION_ID        NOT IN (200,222,260) 
AND   XAL.AE_HEADER_ID          = XAH.AE_HEADER_ID
AND   XAH.EVENT_TYPE_CODE       NOT IN ('RELE', 'CERT')
AND   XAH.ENTITY_ID             = UPG.ENTITY_ID
AND   UPG.TRANSACTION_NUMBER    = TO_CHAR(MMT.TRANSACTION_ID)
--AND   UPG.ENTITY_CODE          != 'RCV_ACCOUNTING_EVENTS' 
AND   MMT.TRANSACTION_TYPE_ID   = MTT.TRANSACTION_TYPE_ID
AND   MMT.ORGANIZATION_ID       = OOD.ORGANIZATION_ID

Upto 20% off on Personal Care Appliances

GL Base Query for Oracle EBS

/*GL Query*/

 SELECT  gjb.name batch_name, gjh.je_source, gjh.je_category, gcc.segment4 account_code,

(SELECT  A4.DESCRIPTION   DECS

from fnd_flex_values_vl A4

WHERE

   a4.flex_value=gcc.segment4

And a4.FLEX_VALUE_SET_ID in ( select FLEX_VALUE_SET_ID

                            from fnd_id_flex_segments

                           WHERE application_id = 101 AND id_flex_code = 'GL#' and enabled_flag = 'Y'

                           and application_column_name='SEGMENT4'))account_DECS,

       sum(NVL(gjl.accounted_dr,0)) accounted_dr, sum(NVL(gjl.accounted_cr,0)) accounted_cr,

       sum(NVL (gjl.accounted_dr, 0) - NVL (gjl.accounted_cr, 0) ) net_balance

  FROM gl_je_lines gjl,

       gl_je_headers gjh,

       gl_je_batches gjb,

       gl_code_combinations gcc

 WHERE 1=1--gjh.je_category = 'Receipts'

   AND gjl.je_header_id = gjh.je_header_id

   AND gjl.PERIOD_NAME NOT LIKE 'ADJ%' 

   AND EXISTS (

               SELECT 1

                 FROM gl_code_combinations gc

                WHERE gc.segment1 = :P_COMPANY

                  AND gc.segment4 = :P_ACCT_CD 

                  AND gc.code_combination_id= gjl.code_combination_id)

   AND gjl.code_combination_id = gcc.code_combination_id

   AND gjl.status = 'P'

   AND gjb.je_batch_id = gjh.je_batch_id

   --AND TRUNC (gjl.effective_date) >= :p_start_date

   AND TRUNC (gjl.effective_date) <= :p_end_date

Group By  gjb.name, gjh.je_source, gjh.je_category,gcc.segment4  

Order By 1

Oracle Apps R12 Legal Entity details

--Oracle R12 Legal Entity details Query

SELECT   xep.legal_entity_id, xep.party_id, xep.legal_entity_identifier,

         xep.name legal_entity, hp.party_name legal_entity_name

    FROM xle_entity_profiles xep, hz_parties hp

   WHERE xep.party_id = hp.party_id

ORDER BY hp.party_name

Oracle GL Journal Import Program Backend

DECLARE

   l_conc_id         NUMBER;

   l_int_run_id      NUMBER;

   l_access_set_id   NUMBER;

   l_org_id          NUMBER := 81;

   l_sob_id          NUMBER := 2021;

   l_user_id         NUMBER := FND_GLOBAL.USER_ID;

   l_resp_id         NUMBER := FND_GLOBAL.RESP_ID;

   l_resp_app_id     NUMBER := FND_GLOBAL.RESP_APPL_ID;

BEGIN

   fnd_global.apps_initialize (user_id        => l_user_id,   --User Id

                               resp_id        => l_resp_id,    

                                                        --Responsibility Id

                               resp_appl_id   => l_resp_app_id 

                                           --Responsibility Application Id

                                                              );

   mo_global.set_policy_context ('S', l_org_id);

   SELECT gl_journal_import_s.NEXTVAL INTO l_int_run_id FROM DUAL;

   SELECT access_set_id

     INTO l_access_set_id

     FROM gl_access_sets

    WHERE name = 'VISION OPERATIONS SET';

   INSERT INTO gl_interface_control (je_source_name,

                                     interface_run_id,

                                     status,

                                     set_of_books_id)

        VALUES ('Receivables',

                l_int_run_id,

                'S',

                l_sob_id);

   l_conc_id :=

      fnd_request.submit_request 

            (application   => 'SQLGL',

              program       => 'GLLEZL',

              description   => NULL,

              start_time    => SYSDATE,

              sub_request   => FALSE,

              argument1     => l_int_run_id, --interface run id

              argument2     => l_access_set_id, --data access set_id

              argument3     => 'N',      --post to suspense

              argument4     => NULL,            --from date

              argument5     => NULL,              --to date

              argument6     => 'N',          --summary mode

              argument7     => 'N',            --import DFF

              argument8     => 'Y'         --backward mode

             );

   COMMIT;

   DBMS_OUTPUT.PUT_LINE ('GL Import Submitted. Request Id : ' || l_conc_id);

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.PUT_LINE ('Error while submitting the GL Import Program.');

      DBMS_OUTPUT.PUT_LINE 

      ('Error : ' || SQLCODE || '-' || SUBSTR (SQLERRM, 1, 240));

END;