--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