/* Formatted on 11/16/2016 1:42:07 PM (QP5 v5.114.809.3010) */
SELECT SOB.NAME,
GB.PERIOD_NAME,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
GCC.SEGMENT9,
GCC.SEGMENT10,
(CASE
WHEN SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0)) >=
0
THEN
(SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0)))
ELSE
0
END)
"DEBIT",
(CASE
WHEN SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0)) <=
0
THEN
(SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0))
* -1)
ELSE
0
END)
"CREDIT"
FROM GL_BALANCES GB, GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB
WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
AND GB.ACTUAL_FLAG = 'A'
AND GB.PERIOD_NAME = 'Apr-16'
AND GB.CURRENCY_CODE = SOB.CURRENCY_CODE
AND SUBSTR (SOB.SHORT_NAME, 1, 2) IN ('HK', 'JP', 'TH', 'SG', 'CN')
AND GB.TEMPLATE_ID IS NULL
AND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
GROUP BY SOB.NAME,
GB.ACTUAL_FLAG,
GB.PERIOD_NAME,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
GCC.SEGMENT9,
GCC.SEGMENT10,
NVL (GB.PERIOD_NET_DR, 0),
NVL (GB.PERIOD_NET_CR, 0)
HAVING SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0)) <> 0
ORDER BY 1,
2,
3,
4,
5,
6,
7,
8,
9
SELECT SOB.NAME,
GB.PERIOD_NAME,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
GCC.SEGMENT9,
GCC.SEGMENT10,
(CASE
WHEN SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0)) >=
0
THEN
(SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0)))
ELSE
0
END)
"DEBIT",
(CASE
WHEN SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0)) <=
0
THEN
(SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0))
* -1)
ELSE
0
END)
"CREDIT"
FROM GL_BALANCES GB, GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB
WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
AND GB.ACTUAL_FLAG = 'A'
AND GB.PERIOD_NAME = 'Apr-16'
AND GB.CURRENCY_CODE = SOB.CURRENCY_CODE
AND SUBSTR (SOB.SHORT_NAME, 1, 2) IN ('HK', 'JP', 'TH', 'SG', 'CN')
AND GB.TEMPLATE_ID IS NULL
AND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
GROUP BY SOB.NAME,
GB.ACTUAL_FLAG,
GB.PERIOD_NAME,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
GCC.SEGMENT9,
GCC.SEGMENT10,
NVL (GB.PERIOD_NET_DR, 0),
NVL (GB.PERIOD_NET_CR, 0)
HAVING SUM (NVL (GB.PERIOD_NET_DR, 0) - NVL (GB.PERIOD_NET_CR, 0)) <> 0
ORDER BY 1,
2,
3,
4,
5,
6,
7,
8,
9
No comments:
Post a Comment