select PERIOD_NAME,
SEGMENT3,
SEGMENT3_DESC,
sum(case
when SEGMENT2 = '1011' then
ACCOUNTED_ACCRUAL
else
0
end) caiwu, --财务部
sum(case
when SEGMENT2 = '1021' then
ACCOUNTED_ACCRUAL
else
0
end) RENLI, --人力资源与行政
sum(case
when SEGMENT2 = '1075' then
ACCOUNTED_ACCRUAL
else
0
end) YUNZUO, --运作支持部
sum(case
when SEGMENT2 = '1047' then
ACCOUNTED_ACCRUAL
else
0
end) SHICHANG, --市场总部
sum(case
when SEGMENT2 = '1042' then
ACCOUNTED_ACCRUAL
else
0
end) SHANGWUBU, --商务部
sum(case
when SEGMENT2 = '1049' then
ACCOUNTED_ACCRUAL
else
0
end) XIANSIDIANYUAN, --显示电源市场部
sum(case
when SEGMENT2 = '1050' then
ACCOUNTED_ACCRUAL
else
0
end) BIANPINJIADIAN, --变频家电市场部
sum(case
when SEGMENT2 = '1051' then
ACCOUNTED_ACCRUAL
else
0
end) GONGYEDIANYUAN, --工业电源市场部
sum(case
when SEGMENT2 = '1052' then
ACCOUNTED_ACCRUAL
else
0
end) YILIAODIANYUAN, --医疗电源市场部
sum(case
when SEGMENT2 = '1048' then
ACCOUNTED_ACCRUAL
else
0
end) GUANGFUSHICHANG, --光伏市场部
sum(case
when SEGMENT2 = '1043' then
ACCOUNTED_ACCRUAL
else
0
end) HANJISHICHANG, --工业电源市场部
sum(case
when SEGMENT2 = '1636' then
ACCOUNTED_ACCRUAL
else
0
end) SHOUHOUFUWU, --医疗电源市场部
sum(case
when SEGMENT2 = '1053' then
ACCOUNTED_ACCRUAL
else
0
end) ZHINENGWEIYU, --光伏市场部
CURRENCY_CODE
from (select PERIOD_NAME,
SEGMENT3,
SEGMENT3_DESC,
EFFECTIVE_DATE,
SEGMENT2,
SEGMENT2_DESC,
sum(ACCOUNTED_ACCRUAL) ACCOUNTED_ACCRUAL,
CURRENCY_CODE
from (SELECT TO_CHAR(EFFECTIVE_DATE, 'YYYY-MM-DD') EFFECTIVE_DATE,
GCC.SEGMENT2,
GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID,
2,
GCC.SEGMENT2) SEGMENT2_DESC, --部门描述
GCC.SEGMENT3,
GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID,
3,
GCC.SEGMENT3) SEGMENT3_DESC, --科目描述
(NVL(NVL(XAL.ACCOUNTED_DR, GJL.ACCOUNTED_DR), 0) -
NVL(NVL(XAL.ACCOUNTED_CR, GJL.ACCOUNTED_CR), 0)) ACCOUNTED_ACCRUAL,
GJH.PERIOD_NAME,
GJH.CURRENCY_CODE
FROM GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_CODE_COMBINATIONS GCC,
AP_INVOICES_ALL AIA,
AP_SUPPLIERS ASS,
GL_PERIODS GP,
GL_IMPORT_REFERENCES GIR,
XLA_AE_HEADERS XAH,
XLA_AE_LINES XAL
WHERE GJH.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND XAH.DOC_SEQUENCE_VALUE = AIA.DOC_SEQUENCE_VALUE(+)
AND XAH.LEDGER_ID = AIA.SET_OF_BOOKS_ID(+)
AND AIA.VENDOR_ID = ASS.VENDOR_ID(+)
AND GJH.PERIOD_NAME = GP.PERIOD_NAME
AND UPPER(GP.PERIOD_SET_NAME) = UPPER('MEG_Month')
AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID(+)
AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM(+)
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID(+)
AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE(+)
AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID(+)
AND GJH.LEDGER_ID = :P_LEDGER_ID
AND GP.START_DATE >=
TO_DATE(:CP_START_DATE_MIN, 'YYYY-MM-DD')
AND GP.START_DATE <=
TO_DATE(:CP_START_DATE_MAX, 'YYYY-MM-DD')
AND GJH.CURRENCY_CODE = :P_CURRENCY_CODE--币别
AND GP.START_DATE >= TO_DATE('2019-06-01', 'YYYY-MM-DD')
AND GP.START_DATE <= TO_DATE('2019-06-30', 'YYYY-MM-DD')
and GCC.SEGMENT3 >= '60010002'
and GCC.SEGMENT3 <= '60020004'
AND GP.START_DATE >= TO_DATE('2019-06-01', 'YYYY-MM-DD')
AND GP.START_DATE <= TO_DATE('2019-06-30', 'YYYY-MM-DD')
AND GJH.CURRENCY_CODE = 'CNY'
AND GJH.LEDGER_ID = 2021)
group by EFFECTIVE_DATE,
SEGMENT2,
SEGMENT2_DESC,
SEGMENT3,
SEGMENT3_DESC,
PERIOD_NAME,
CURRENCY_CODE)
group by PERIOD_NAME, SEGMENT3, SEGMENT3_DESC, CURRENCY_CODE