/* Formatted on 11/16/2016 1:21:22 PM (QP5 v5.114.809.3010) */
SELECT GCC.SEGMENT5 "ACCOUNT",
FND.DESCRIPTION "DESCRIPTION",
GJH.JE_SOURCE "SOURCE",
(SELECT SUM (NVL (GJL2.ACCOUNTED_DR, 0))
- SUM (NVL (GJL2.ACCOUNTED_CR, 0))
FROM GL_JE_LINES GJL2, GL_JE_HEADERS GJH2
WHERE 1 = 1
AND GJL2.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL2.LEDGER_ID = 2021
AND TRUNC (GJL2.EFFECTIVE_DATE) <
(SELECT TRUNC (START_DATE)
FROM GL_PERIODS
WHERE PERIOD_NAME = 'Apr-16')
AND GJL2.JE_HEADER_ID = GJH2.JE_HEADER_ID
AND GJH2.ACTUAL_FLAG = 'A'
AND GJH2.JE_SOURCE != 'Consolidation'
AND GJH2.LEDGER_ID = GJL2.LEDGER_ID
AND GJH2.JE_SOURCE = GJH.JE_SOURCE
AND GJL2.STATUS = 'P')
"BEGIN_BALANCE",
( SELECT SUM (NVL (GJL1.ACCOUNTED_DR, 0))
FROM GL_JE_LINES GJL1, GL_JE_HEADERS GJH1
WHERE 1 = 1
AND GJL1.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL1.LEDGER_ID = 2021
AND GJH1.LEDGER_ID = GJL1.LEDGER_ID
AND GJH1.ACTUAL_FLAG = 'A'
AND GJH1.JE_SOURCE != 'Consolidation'
AND GJH1.JE_HEADER_ID = GJL1.JE_HEADER_ID
AND TRUNC (GJL1.EFFECTIVE_DATE) BETWEEN (SELECT TRUNC(START_DATE)
FROM GL_PERIODS
WHERE PERIOD_SET_NAME =
'CORPORATE'
AND PERIOD_NAME =
'Apr-16')
AND (SELECT TRUNC(END_DATE)
FROM GL_PERIODS
WHERE PERIOD_SET_NAME =
'CORPORATE'
AND PERIOD_NAME =
'Apr-16')
AND GJH1.JE_SOURCE = GJH.JE_SOURCE
AND GJL1.STATUS = 'P'
GROUP BY GCC.SEGMENT5, FND.DESCRIPTION, GJH.JE_SOURCE)
"DEBIT",
( SELECT SUM (NVL (GJL1.ACCOUNTED_CR, 0))
FROM GL_JE_LINES GJL1, GL_JE_HEADERS GJH1
WHERE 1 = 1
AND GJL1.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL1.LEDGER_ID = 2021
AND GJH1.LEDGER_ID = GJL1.LEDGER_ID
AND GJH1.ACTUAL_FLAG = 'A'
AND GJH1.JE_SOURCE != 'Consolidation'
AND GJH1.JE_HEADER_ID = GJL1.JE_HEADER_ID
AND TRUNC (GJL1.EFFECTIVE_DATE) BETWEEN (SELECT TRUNC(START_DATE)
FROM GL_PERIODS
WHERE PERIOD_SET_NAME =
'CORPORATE'
AND PERIOD_NAME =
'Apr-16')
AND (SELECT TRUNC(END_DATE)
FROM GL_PERIODS
WHERE PERIOD_SET_NAME =
'CORPORATE'
AND PERIOD_NAME =
'Apr-16')
AND GJH1.JE_SOURCE = GJH.JE_SOURCE
AND GJL1.STATUS = 'P'
GROUP BY GCC.SEGMENT5, FND.DESCRIPTION, GJH.JE_SOURCE)
"CREDIT",
(SELECT SUM (NVL (GJL2.ACCOUNTED_DR, 0))
- SUM (NVL (GJL2.ACCOUNTED_CR, 0))
FROM GL_JE_LINES GJL2, GL_JE_HEADERS GJH2
WHERE 1 = 1
AND GJL2.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL2.LEDGER_ID = 2021
AND UPPER (GJL2.EFFECTIVE_DATE) <=
(SELECT TRUNC (END_DATE)
FROM GL_PERIODS
WHERE PERIOD_NAME = 'Apr-16')
AND GJL2.JE_HEADER_ID = GJH2.JE_HEADER_ID
AND GJH2.ACTUAL_FLAG = 'A'
AND GJH2.JE_SOURCE != 'Consolidation'
AND GJH2.LEDGER_ID = GJL2.LEDGER_ID
AND GJH2.JE_SOURCE = GJH.JE_SOURCE
AND GJL2.STATUS = 'P')
"END_BALANCE"
FROM GL_CODE_COMBINATIONS GCC,
FND_FLEX_VALUES_VL FND,
GL_JE_LINES GJL,
GL_JE_HEADERS GJH
WHERE 1 = 1
AND GCC.CHART_OF_ACCOUNTS_ID = 50420
-- AND GCC.SEGMENT1 BETWEEN P_SEGMENT1_LOW AND P_SEGMENT1_HIGH
AND GCC.SUMMARY_FLAG = 'N'
AND GCC.TEMPLATE_ID IS NULL
AND FND.FLEX_VALUE = GCC.SEGMENT5
AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL.LEDGER_ID = 2021
AND GJL.STATUS = 'P'
AND GJH.CURRENCY_CODE = 'USD'
AND GJH.LEDGER_ID = GJL.LEDGER_ID
AND GJH.ACTUAL_FLAG = 'A'
AND GJH.JE_SOURCE != 'Consolidation'
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
--AND NVL2(P_SOURCE,GJH.JE_SOURCE,1) = NVL2(P_SOURCE,P_SOURCE,1)
--ADDED ADDITIONAL LOGIC FOR RESTRICTING ADDITIONAL ACCOUNTS WHICH ARE NOT OCCURING IN TRIAL BALANCE REPORT
AND ( (SELECT SUM (BEGIN_BALANCE_DR) - SUM (BEGIN_BALANCE_CR)
FROM GL_BALANCES
WHERE CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND LEDGER_ID = GJL.LEDGER_ID
AND CURRENCY_CODE = 'USD'
AND PERIOD_NAME IN ('Apr-16')) != 0
OR (SELECT SUM (PERIOD_NET_DR)
FROM GL_BALANCES
WHERE CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND LEDGER_ID = GJL.LEDGER_ID
AND CURRENCY_CODE = 'USD'
AND PERIOD_NAME IN ('Apr-16')) != 0
OR (SELECT SUM (PERIOD_NET_CR)
FROM GL_BALANCES
WHERE CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND LEDGER_ID = GJL.LEDGER_ID
AND CURRENCY_CODE = 'USD'
AND PERIOD_NAME IN ('Apr-16')) != 0)
---------------------END OF ADDITIONAL LOGIC
GROUP BY GCC.SEGMENT5,
FND.DESCRIPTION,
GJH.JE_SOURCE,
GCC.CODE_COMBINATION_ID
ORDER BY 1;
SELECT GCC.SEGMENT5 "ACCOUNT",
FND.DESCRIPTION "DESCRIPTION",
GJH.JE_SOURCE "SOURCE",
(SELECT SUM (NVL (GJL2.ACCOUNTED_DR, 0))
- SUM (NVL (GJL2.ACCOUNTED_CR, 0))
FROM GL_JE_LINES GJL2, GL_JE_HEADERS GJH2
WHERE 1 = 1
AND GJL2.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL2.LEDGER_ID = 2021
AND TRUNC (GJL2.EFFECTIVE_DATE) <
(SELECT TRUNC (START_DATE)
FROM GL_PERIODS
WHERE PERIOD_NAME = 'Apr-16')
AND GJL2.JE_HEADER_ID = GJH2.JE_HEADER_ID
AND GJH2.ACTUAL_FLAG = 'A'
AND GJH2.JE_SOURCE != 'Consolidation'
AND GJH2.LEDGER_ID = GJL2.LEDGER_ID
AND GJH2.JE_SOURCE = GJH.JE_SOURCE
AND GJL2.STATUS = 'P')
"BEGIN_BALANCE",
( SELECT SUM (NVL (GJL1.ACCOUNTED_DR, 0))
FROM GL_JE_LINES GJL1, GL_JE_HEADERS GJH1
WHERE 1 = 1
AND GJL1.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL1.LEDGER_ID = 2021
AND GJH1.LEDGER_ID = GJL1.LEDGER_ID
AND GJH1.ACTUAL_FLAG = 'A'
AND GJH1.JE_SOURCE != 'Consolidation'
AND GJH1.JE_HEADER_ID = GJL1.JE_HEADER_ID
AND TRUNC (GJL1.EFFECTIVE_DATE) BETWEEN (SELECT TRUNC(START_DATE)
FROM GL_PERIODS
WHERE PERIOD_SET_NAME =
'CORPORATE'
AND PERIOD_NAME =
'Apr-16')
AND (SELECT TRUNC(END_DATE)
FROM GL_PERIODS
WHERE PERIOD_SET_NAME =
'CORPORATE'
AND PERIOD_NAME =
'Apr-16')
AND GJH1.JE_SOURCE = GJH.JE_SOURCE
AND GJL1.STATUS = 'P'
GROUP BY GCC.SEGMENT5, FND.DESCRIPTION, GJH.JE_SOURCE)
"DEBIT",
( SELECT SUM (NVL (GJL1.ACCOUNTED_CR, 0))
FROM GL_JE_LINES GJL1, GL_JE_HEADERS GJH1
WHERE 1 = 1
AND GJL1.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL1.LEDGER_ID = 2021
AND GJH1.LEDGER_ID = GJL1.LEDGER_ID
AND GJH1.ACTUAL_FLAG = 'A'
AND GJH1.JE_SOURCE != 'Consolidation'
AND GJH1.JE_HEADER_ID = GJL1.JE_HEADER_ID
AND TRUNC (GJL1.EFFECTIVE_DATE) BETWEEN (SELECT TRUNC(START_DATE)
FROM GL_PERIODS
WHERE PERIOD_SET_NAME =
'CORPORATE'
AND PERIOD_NAME =
'Apr-16')
AND (SELECT TRUNC(END_DATE)
FROM GL_PERIODS
WHERE PERIOD_SET_NAME =
'CORPORATE'
AND PERIOD_NAME =
'Apr-16')
AND GJH1.JE_SOURCE = GJH.JE_SOURCE
AND GJL1.STATUS = 'P'
GROUP BY GCC.SEGMENT5, FND.DESCRIPTION, GJH.JE_SOURCE)
"CREDIT",
(SELECT SUM (NVL (GJL2.ACCOUNTED_DR, 0))
- SUM (NVL (GJL2.ACCOUNTED_CR, 0))
FROM GL_JE_LINES GJL2, GL_JE_HEADERS GJH2
WHERE 1 = 1
AND GJL2.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL2.LEDGER_ID = 2021
AND UPPER (GJL2.EFFECTIVE_DATE) <=
(SELECT TRUNC (END_DATE)
FROM GL_PERIODS
WHERE PERIOD_NAME = 'Apr-16')
AND GJL2.JE_HEADER_ID = GJH2.JE_HEADER_ID
AND GJH2.ACTUAL_FLAG = 'A'
AND GJH2.JE_SOURCE != 'Consolidation'
AND GJH2.LEDGER_ID = GJL2.LEDGER_ID
AND GJH2.JE_SOURCE = GJH.JE_SOURCE
AND GJL2.STATUS = 'P')
"END_BALANCE"
FROM GL_CODE_COMBINATIONS GCC,
FND_FLEX_VALUES_VL FND,
GL_JE_LINES GJL,
GL_JE_HEADERS GJH
WHERE 1 = 1
AND GCC.CHART_OF_ACCOUNTS_ID = 50420
-- AND GCC.SEGMENT1 BETWEEN P_SEGMENT1_LOW AND P_SEGMENT1_HIGH
AND GCC.SUMMARY_FLAG = 'N'
AND GCC.TEMPLATE_ID IS NULL
AND FND.FLEX_VALUE = GCC.SEGMENT5
AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJL.LEDGER_ID = 2021
AND GJL.STATUS = 'P'
AND GJH.CURRENCY_CODE = 'USD'
AND GJH.LEDGER_ID = GJL.LEDGER_ID
AND GJH.ACTUAL_FLAG = 'A'
AND GJH.JE_SOURCE != 'Consolidation'
AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
--AND NVL2(P_SOURCE,GJH.JE_SOURCE,1) = NVL2(P_SOURCE,P_SOURCE,1)
--ADDED ADDITIONAL LOGIC FOR RESTRICTING ADDITIONAL ACCOUNTS WHICH ARE NOT OCCURING IN TRIAL BALANCE REPORT
AND ( (SELECT SUM (BEGIN_BALANCE_DR) - SUM (BEGIN_BALANCE_CR)
FROM GL_BALANCES
WHERE CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND LEDGER_ID = GJL.LEDGER_ID
AND CURRENCY_CODE = 'USD'
AND PERIOD_NAME IN ('Apr-16')) != 0
OR (SELECT SUM (PERIOD_NET_DR)
FROM GL_BALANCES
WHERE CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND LEDGER_ID = GJL.LEDGER_ID
AND CURRENCY_CODE = 'USD'
AND PERIOD_NAME IN ('Apr-16')) != 0
OR (SELECT SUM (PERIOD_NET_CR)
FROM GL_BALANCES
WHERE CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND LEDGER_ID = GJL.LEDGER_ID
AND CURRENCY_CODE = 'USD'
AND PERIOD_NAME IN ('Apr-16')) != 0)
---------------------END OF ADDITIONAL LOGIC
GROUP BY GCC.SEGMENT5,
FND.DESCRIPTION,
GJH.JE_SOURCE,
GCC.CODE_COMBINATION_ID
ORDER BY 1;
Hi all, Oracle technical and fusion cloud SCM online classes, if you are interested please whatsapp to this number +91 7382582893, thank you.
ReplyDelete