/************************************************************************************************
HISTORY
Date Authur Version Description
---------- --------- ------- -------------------------------------------------------------
2023/11/22 Yulin.Chen v1.01 備份一下程式超難寫
************************************************************************************************/
SELECT '資產_折舊' TYPE, --完成驗證GL 391224
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
FAA.ATTRIBUTE1 AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
FA_ADDITIONS_V FAA,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL --20220627
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = FAA.ASSET_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 140
AND XTE.ENTITY_CODE = 'DEPRECIATION'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
UNION ALL
SELECT '資產_調整' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
FA_TRANSACTION_HEADERS FTH,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL --20220627
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = FTH.TRANSACTION_HEADER_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 140
AND XTE.ENTITY_CODE = 'TRANSACTIONS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
UNION ALL
SELECT '應收帳款_銷售商業發票與銷退折讓單' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
RCTA.DOC_SEQUENCE_VALUE DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
(SELECT DISTINCT RCTL.INTERFACE_LINE_ATTRIBUTE5
FROM RA_CUSTOMER_TRX_LINES_ALL RCTL
WHERE RCTL.INTERFACE_LINE_CONTEXT = 'eDCN Import'
AND RCTL.INTERFACE_LINE_ATTRIBUTE5 IS NOT NULL
AND RCTL.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID) AEG_PROJECT_CODE,
NULL GLL_NAME,
XEL.CURRENCY_CODE,
XDL.UNROUNDED_ENTERED_DR ENTERED_DR, --20220803
XDL.UNROUNDED_ENTERED_CR ENTERED_CR, --20220803
XDL.UNROUNDED_ACCOUNTED_DR ACCOUNTED_DR, --20220803
XDL.UNROUNDED_ACCOUNTED_CR ACCOUNTED_CR, --20220803
--XEL.ENTERED_DR,抓到總數,如果改成XDL還是不行要分開抓 TAX 與 LINE --20220803
--XEL.ENTERED_CR, --20220803
--XEL.ACCOUNTED_DR, --20220803
--XEL.ACCOUNTED_CR, --20220803
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
MSI.SEGMENT1 ITEM,
NULL CSTCATEGORY,
MIC.CATEGORY_CONCAT_SEGS INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
HP.PARTY_NAME CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
nvl(XEL.DESCRIPTION, rctla.description) SUB_DESCRIPTION --add by v1.02
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
XLA_DISTRIBUTION_LINKS XDL, --20220728
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTLG, --20220728
RA_CUSTOMER_TRX_LINES_ALL RCTLA, --20220728
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL, --20220728
MTL_SYSTEM_ITEMS_B MSI, --20220728
MTL_ITEM_CATEGORIES_V MIC, --20220728
HZ_CUST_ACCOUNTS_ALL HCAA, --20220810
HZ_PARTIES HP --20220810
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = RCTA.CUSTOMER_TRX_ID
AND XTE.LEDGER_ID = RCTA.SET_OF_BOOKS_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 222
AND XTE.ENTITY_CODE = 'TRANSACTIONS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220728
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220728
AND UPPER(GLH.JE_SOURCE) = 'RECEIVABLES' --20220728
AND XEL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XEL.AE_LINE_NUM = XDL.AE_LINE_NUM
AND XEL.APPLICATION_ID = XDL.APPLICATION_ID
AND RCTLG.CUST_TRX_LINE_GL_DIST_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1 --20220728
AND XDL.SOURCE_DISTRIBUTION_TYPE = 'RA_CUST_TRX_LINE_GL_DIST_ALL' --20220728
AND RCTLG.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID(+) --20220728
AND RCTLG.CUSTOMER_TRX_LINE_ID = RCTLA.CUSTOMER_TRX_LINE_ID(+) --20220728
--AND RCTA.TRX_NUMBER = '577229'
AND RCTLA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+) --20220728
AND RCTA.INTERFACE_HEADER_ATTRIBUTE10 = MSI.ORGANIZATION_ID(+) --20220728
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID(+) --20220728
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID(+) --20220728
AND (MIC.CATEGORY_SET_ID = 1100000021 OR MIC.CATEGORY_SET_ID IS NULL) --20220728
AND RCTA.BILL_TO_CUSTOMER_ID = HCAA.CUST_ACCOUNT_ID --20220810
AND HCAA.PARTY_ID = HP.PARTY_ID --20220810
UNION ALL
SELECT '應收帳款_收款' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
RCTA.DOC_SEQUENCE_VALUE DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
NULL GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
HP.PARTY_NAME CUSTOMER_NAME, ----Add v1.01
NULL VENDOR_NAME,
NULL GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
AR_CASH_RECEIPTS_ALL RCTA,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
--GL_JE_LINES GLL --20220627
HZ_PARTIES HP, --Add v1.01
HZ_CUST_ACCOUNTS_ALL HCA --Add v1.01
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = RCTA.CASH_RECEIPT_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 222
AND XTE.ENTITY_CODE = 'RECEIPTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
--AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
--AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
AND NVL(XEL.ENTERED_DR, -1) <> 0
AND NVL(XEL.ENTERED_CR, -1) <> 0
AND NVL(XEL.ACCOUNTED_DR, -1) <> 0
AND NVL(XEL.ACCOUNTED_CR, -1) <> 0
AND RCTA.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID(+) --Add v1.01
AND HCA.PARTY_ID = HP.PARTY_ID(+) --Add v1.01
UNION ALL
SELECT '應收帳款_調整' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
RCTA.DOC_SEQUENCE_VALUE DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
NULL GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
HP.PARTY_NAME CUSTOMER_NAME, ----Add v1.01
NULL VENDOR_NAME,
NULL GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
RA_CUSTOMER_TRX_ALL RCTA,
AR_ADJUSTMENTS_ALL ADJ,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
--GL_JE_LINES GLL --20220627
HZ_PARTIES HP, --Add v1.01
HZ_CUST_ACCOUNTS_ALL HCA --Add v1.01
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = ADJ.ADJUSTMENT_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 222
AND XTE.ENTITY_CODE = 'ADJUSTMENTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
--AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
--AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
AND NVL(XEL.ENTERED_DR, -1) <> 0
AND NVL(XEL.ENTERED_CR, -1) <> 0
AND NVL(XEL.ACCOUNTED_DR, -1) <> 0
AND NVL(XEL.ACCOUNTED_CR, -1) <> 0
AND ADJ.CUSTOMER_TRX_ID = RCTA.CUSTOMER_TRX_ID(+)
AND RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID(+) --Add v1.01
AND HCA.PARTY_ID = HP.PARTY_ID(+) --Add v1.01
UNION ALL
SELECT 'AP_付款' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
ACA.DOC_SEQUENCE_VALUE DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
ACA.VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
AP_CHECKS_ALL ACA,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL --20220627
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = ACA.CHECK_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 200
AND XTE.ENTITY_CODE = 'AP_PAYMENTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
UNION ALL
SELECT 'AP_採購商業發票' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
AIA.DOC_SEQUENCE_VALUE DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
(SELECT AILA.ATTRIBUTE15
FROM AP_INVOICE_LINES_ALL AILA
WHERE AILA.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND AILA.ATTRIBUTE15 IS NOT NULL
AND AILA.INVOICE_ID = AIA.INVOICE_ID
AND AILA.LINE_NUMBER = XEL.AE_LINE_NUM) AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
NULL ACCOUNTING_CLASS_CODE,
GCC.CODE_COMBINATION_ID,
NULL ORDER_NUMBER,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
PV.VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
AP_INVOICES_ALL AIA,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL, --20220627
PO_VENDORS PV --20220810
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = AIA.INVOICE_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 200
AND XTE.ENTITY_CODE = 'AP_INVOICES'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
AND AIA.VENDOR_ID = PV.VENDOR_ID --20220810
UNION ALL
SELECT '手動_ACETW_GL/FA 新增' TYPE,
GLH.LEDGER_ID,
GLH.DEFAULT_EFFECTIVE_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--NULL ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
NULL CURRENCY_CODE,
GLL.ENTERED_DR,
GLL.ENTERED_CR,
GLL.ACCOUNTED_DR,
GLL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
NULL ENTITY_CODE,
NULL GL_TRANSFER_DATE,
NULL JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
NULL ACCOUNTING_CLASS_CODE,
GCC.CODE_COMBINATION_ID,
NULL ORDER_NUMBER,
NULL TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
NULL SUB_DESCRIPTION
FROM GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL,
GL_CODE_COMBINATIONS GCC
WHERE GLB.JE_BATCH_ID = GLH.JE_BATCH_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GLH.LEDGER_ID = $param_SOB_ID$
AND UPPER(GLH.JE_SOURCE) IN ('MANUAL','AUTOCOPY')
AND TRUNC(GLH.DEFAULT_EFFECTIVE_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND GLL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
UNION ALL
SELECT '成本管理系統_收貨' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
NULL TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
MSI.SEGMENT1 ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
NULL TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
RCV_TRANSACTIONS RT,
PO_LINES_ALL PLA, --20220531
PO_HEADERS_ALL PHA,
MTL_SYSTEM_ITEMS_B MSI, --20220531
MTL_ITEM_CATEGORIES_V MIC, --20220728
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL --20220627
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = RT.TRANSACTION_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 707
AND XTE.ENTITY_CODE = 'RCV_ACCOUNTING_EVENTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND PLA.PO_LINE_ID = RT.PO_LINE_ID --20220531
AND PLA.ITEM_ID = MSI.INVENTORY_ITEM_ID --20220531
AND MSI.ORGANIZATION_ID = 105 --20220531
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID --20220628
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID --20220628
AND MIC.CATEGORY_SET_ID = 1 --20220628
AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND UPPER(GLH.JE_SOURCE) = 'COST MANAGEMENT'
AND UPPER(GLH.JE_CATEGORY) = 'RECEIVING'
UNION ALL
SELECT '成本管理系統_存貨' TYPE, --訂單
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
SUBSTR(OTTT.DESCRIPTION, 0, INSTR(OTTT.DESCRIPTION, '-') - 1) DEPT,
FLV.ATTRIBUTE14 ITEM_CATEGORY_ATT14,
JRS.NAME SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
REPLACE(TO_CHAR(OOHA.ORDER_NUMBER, 99999999999), ' ', NULL) ORDER_NUMBER,
OOHA.TRANSACTIONAL_CURR_CODE TRANSACTIONAL_CURRENCY,
MSI.SEGMENT1 ITEM,
MIC2.CATEGORY_CONCAT_SEGS CSTCATEGORY,
MIC.CATEGORY_CONCAT_SEGS INVCATEGORY,
OOLA.UNIT_SELLING_PRICE,
OOLA.UNIT_COST,
NULL RECEIPT_NUMBER,
MMT.TRANSACTION_DATE,
MTT.TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT, --20220725
OE_ORDER_HEADERS_ALL OOHA,
OE_TRANSACTION_TYPES_TL OTTT,
OE_ORDER_LINES_ALL OOLA,
MTL_SYSTEM_ITEMS_B MSI,
MTL_ITEM_CATEGORIES_V MIC,
MTL_ITEM_CATEGORIES_V MIC2,
FND_LOOKUP_VALUES FLV,
JTF_RS_SALESREPS JRS,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL --20220627
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = MMT.TRANSACTION_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 707
AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND MMT.TRX_SOURCE_LINE_ID = OOLA.LINE_ID
AND OOHA.HEADER_ID = OOLA.HEADER_ID
AND OOLA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = MMT.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND MIC.CATEGORY_CONCAT_SEGS = FLV.LOOKUP_CODE(+)
AND FLV.LANGUAGE = 'ZHT'
AND OOHA.ORDER_TYPE_ID = OTTT.TRANSACTION_TYPE_ID
AND OTTT.LANGUAGE = 'US'
AND OOLA.SALESREP_ID = JRS.SALESREP_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
AND MSI.INVENTORY_ITEM_ID = MIC2.INVENTORY_ITEM_ID --20220711
AND MSI.ORGANIZATION_ID = MIC2.ORGANIZATION_ID --20220711
AND MIC2.CATEGORY_SET_ID = 1100000021 --20220711
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID --20220725
AND UPPER(GLH.JE_SOURCE) = 'COST MANAGEMENT'
AND UPPER(GLH.JE_CATEGORY) = 'INVENTORY'
UNION ALL
SELECT '成本管理系統_存貨' TYPE, --非訂單
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME BATCH_NAME,
GLH.NAME,
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
MMT.TRANSACTION_SOURCE_NAME TRANSACTION_SOURCE_NAME,
NULL TRANSACTIONAL_CURRENCY,
MSI.SEGMENT1 ITEM,
MIC.CATEGORY_CONCAT_SEGS CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
MMT.TRANSACTION_DATE,
MTT.TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT, --20220725
MTL_SYSTEM_ITEMS_B MSI,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL, --20220627
MTL_ITEM_CATEGORIES_V MIC
WHERE XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID(+)
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID(+)
AND XTE.SOURCE_ID_INT_1 = MMT.TRANSACTION_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 707
AND XTE.ENTITY_CODE = 'MTL_ACCOUNTING_EVENTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND MMT.TRX_SOURCE_LINE_ID IS NULL
AND MMT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID(+) --20220627
--AND GLH.PERIOD_NAME = GLL.PERIOD_NAME --20220627
AND XEL.AE_LINE_NUM = GLL.JE_LINE_NUM(+) --20220627
AND MSI.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID --20220711
AND MSI.ORGANIZATION_ID = MIC.ORGANIZATION_ID --20220711
AND MIC.CATEGORY_SET_ID = 1100000021 --20220711
AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID --20220725
AND UPPER(GLH.JE_SOURCE) = 'COST MANAGEMENT'
AND UPPER(GLH.JE_CATEGORY) = 'INVENTORY'
UNION ALL
SELECT '成本管理系統_在製品' TYPE,
XTE.LEDGER_ID,
XE.EVENT_DATE,
GLH.JE_SOURCE,
GLH.JE_CATEGORY,
--XEL.ACCOUNTING_DATE, --mark v1.03
GLH.DOC_SEQUENCE_VALUE GL_DOC_NUMBER,
NULL DOC_NUMBER,
GCC.SEGMENT1,
GCC.SEGMENT2,
GCC.SEGMENT3,
GCC.SEGMENT4,
GCC.SEGMENT5,
GCC.SEGMENT6,
GCC.SEGMENT7,
GCC.SEGMENT8,
ACE_UTL_TOOLS.GET_GL_ACCOUNT_DES(GCC.CODE_COMBINATION_ID) GCC_DESC,
NULL AEG_PROJECT_CODE,
GLL.DESCRIPTION GLL_NAME,
XEL.CURRENCY_CODE,
XEL.ENTERED_DR,
XEL.ENTERED_CR,
XEL.ACCOUNTED_DR,
XEL.ACCOUNTED_CR,
/* --mark v1.03
NULL DEPT,
NULL ITEM_CATEGORY_ATT14,
NULL SALES_PERSON,
XTE.ENTITY_CODE,
XEH.GL_TRANSFER_DATE,
IR.JE_HEADER_ID,
GLB.NAME, --日记帐批
GLH.NAME, --日记帐名
XEL.ACCOUNTING_CLASS_CODE,
XEL.CODE_COMBINATION_ID,
WE.WIP_ENTITY_NAME TRANSACTION_SOURCE_NAME,
WT.CURRENCY_CODE TRANSACTIONAL_CURRENCY,
NULL ITEM,
NULL CSTCATEGORY,
NULL INVCATEGORY,
NULL UNIT_SELLING_PRICE,
NULL UNIT_COST,
NULL RECEIPT_NUMBER,
WT.TRANSACTION_DATE TRANSACTION_DATE,
NULL TRANSACTION_TYPE_NAME,
*/
NULL CUSTOMER_NAME,
NULL VENDOR_NAME,
GLL.DESCRIPTION GLL_DESCRIPTION,
XEL.DESCRIPTION SUB_DESCRIPTION
FROM XLA.XLA_TRANSACTION_ENTITIES XTE,
XLA.XLA_EVENTS XE,
XLA_AE_HEADERS XEH,
XLA_AE_LINES XEL,
XLA_DISTRIBUTION_LINKS XDL,
GL_CODE_COMBINATIONS GCC,
GL_IMPORT_REFERENCES IR,
WIP_TRANSACTIONS WT,
WIP_ENTITIES WE,
WIP_DISCRETE_JOBS WDJ,
GL_JE_BATCHES GLB,
GL_JE_HEADERS GLH,
GL_JE_LINES GLL
WHERE 1 = 1
AND XE.ENTITY_ID = XTE.ENTITY_ID
AND XTE.APPLICATION_ID = XE.APPLICATION_ID
AND XE.ENTITY_ID = XEH.ENTITY_ID(+)
AND XE.EVENT_ID = XEH.EVENT_ID(+)
AND XE.EVENT_TYPE_CODE = XEH.EVENT_TYPE_CODE(+)
AND XEH.AE_HEADER_ID = XEL.AE_HEADER_ID(+)
AND XEL.GL_SL_LINK_ID = IR.GL_SL_LINK_ID(+)
AND XEL.GL_SL_LINK_TABLE = IR.GL_SL_LINK_TABLE(+)
AND IR.JE_BATCH_ID = GLB.JE_BATCH_ID
AND IR.JE_HEADER_ID = GLH.JE_HEADER_ID
AND XTE.SOURCE_ID_INT_1 = WT.TRANSACTION_ID
AND WT.WIP_ENTITY_ID = WE.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
AND XTE.LEDGER_ID = $param_SOB_ID$
AND XE.APPLICATION_ID = 707
AND XTE.ENTITY_CODE = 'WIP_ACCOUNTING_EVENTS'
AND TRUNC(XE.EVENT_DATE) BETWEEN TO_DATE('{{$param_YYYYMMDD_S$}}', 'YYYY/MM/DD') AND TO_DATE('{{$param_YYYYMMDD_E$}}', 'YYYY/MM/DD')
AND XEL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GLB.JE_BATCH_ID = GLH.JE_BATCH_ID
AND GLH.JE_HEADER_ID = GLL.JE_HEADER_ID
AND GLL.JE_LINE_NUM = IR.JE_LINE_NUM
AND UPPER(GLH.JE_SOURCE) = 'COST MANAGEMENT'
AND GLH.JE_CATEGORY = 'WIP'
--AND XEH.APPLICATION_ID = XEL.APPLICATION_ID --20220725v1
--AND GLB.NAME = '成本管理系統 A 1470958 65619248'
--AND IR.JE_LINE_NUM = 7
--AND XEL.APPLICATION_ID = XDL.APPLICATION_ID --20220725v1
AND XEL.AE_HEADER_ID = XDL.AE_HEADER_ID
--AND WE.WIP_ENTITY_ID = 13957590
AND (NVL(XEL.ENTERED_DR, 0) <> 0 OR NVL(XEL.ENTERED_CR, 0) <> 0)
AND XEL.AE_LINE_NUM = XDL.AE_LINE_NUM
沒有留言:
張貼留言