/*Manual Journals*/ SELECT 'GL1' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(GJH.DOC_SEQUENCE_VALUE) TO_CHAR(GJH.DOC_SEQUEN CE_VALUE) AS DOC_NUMBER , NULL PARTY_NAME , NULL TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , GJH.CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , GJL.DESCRIPTION AS NARATION , NULL EVENT_TYPE_CODE , GJL.ENTERED_DR , GJL.ENTERED_CR , GJL.ACCOUNTED_DR , GJL.ACCOUNTED_CR , NULL GL_SL_LINK_ID , GJL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_I GCC.CODE_COMBINATION_ID D AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.JE_SOURCE IN('Manual','AutoCopy' IN('Manual','AutoCopy') ) AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE /*Accounting of AP Invoices*/ UNION ALL SELECT 'AP1' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME
, GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(AIA.DOC_SEQUENCE_VALUE) TO_CHAR(AIA.DOC_SEQUEN CE_VALUE) AS DOC_NUMBER , POV.VENDOR_NAME AS PARTY_NAME , TO_CHAR(AIA.INVOICE_NUM) TO_CHAR(AIA.INVOICE_NU M) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , AIA.INVOICE_CURRENCY_CODE AIA.INVOICE_CURRENCY_C ODE CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , AIA.DESCRIPTION AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , (SELECT DISTINCT INVOICE_ID, ACCOUNTING_EVENT_ID, ORG_ID FROM AP_INVOICE _DISTRIBUTIONS_ALL) AIDA AIDA , AP_INVOICES_ALL AIA , PO_VENDORS POV WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_I GCC.CODE_COMBINATION_ID D AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND GJH.JE_SOURCE = 'Payables' AND GJH.JE_CATEGORY = 'Purchase Invoices' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.APPLICATION_ID = 200 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.EVENT_TYPE_CODE IN ('CREDIT MEMO CANCELLED', 'CREDIT MEMO VALIDATED', 'DEBIT MEMO CANCELLED', 'DEBIT MEMO VALIDATED', 'INVOICE CANCELLED',
'INVOICE VALIDATED', 'PREPAYMENT APPLIED', 'PREPAYMENT CANCELLED', 'PREPAYMENT U NAPPLIED', 'PREPAYMENT VALIDATED') AND XAH.EVENT_ID = AIDA.ACCOUNTING_EVENT AIDA.ACCOUNTING_EVENT_ID _ID AND AIDA.ORG_ID = AIA.ORG_ID AND AIDA.INVOICE_ID = AIA.INVOICE_ID AND AIA.VENDOR_ID = POV.VENDOR_ID /*Accounting of AP Payments*/ UNION ALL SELECT 'AP2' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(ACA.DOC_SEQUENCE_VALUE) TO_CHAR(ACA.DOC_SEQUEN CE_VALUE) AS DOC_NUMBER , ACA.VENDOR_NAME AS PARTY_NAME , TO_CHAR(ACA.CHECK_NUMBER) TO_CHAR(ACA.CHECK_NUMB ER) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , ACA.CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , ACA.DESCRIPTION AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , (SELECT DISTINCT ACCOUNTING_EVENT_ID, PAYMENT_HISTORY_ID FROM AP_PAYMENT _HIST_DISTS ) PHD , (SELECT DISTINCT PAYMENT_HISTORY_ID,CH PAYMENT_HISTORY_ID,CHECK_ID, ECK_ID, ORG_ID FROM AP_PAYMENT_HI STORY_ALL ) APH , AP_CHECKS_ALL ACA WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012
) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_I GCC.CODE_COMBINATION_ID D AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND GJH.JE_SOURCE = 'Payables' AND GJH.JE_CATEGORY IN ('Payments', 'Reconciled Payments') AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.APPLICATION_ID = 200 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.EVENT_TYPE_CODE IN ('PAYMENT CANCELLED', 'PAYMENT CLEARED', 'PAYMENT C REATED', 'PAYMENT UNCLEARED', 'REFUND CANCELLED', 'REFUND RECORDED' ) AND XAH.EVENT_ID = PHD.ACCOUNTING_EVENT_ PHD.ACCOUNTING_EVENT_ID ID AND PHD.PAYMENT_HISTORY_ID = APH.PAYMENT_HISTORY_ID AND APH.ORG_ID = ACA.ORG_ID AND APH.CHECK_ID = ACA.CHECK_ID /** Accounting of AR Receipt Creation **/ UNION ALL SELECT 'AR1' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(ACR.DOC_SEQUENCE_VALUE) TO_CHAR(ACR.DOC_SEQUEN CE_VALUE) AS DOC_NUMBER , ARC.CUSTOMER_NAME AS PARTY_NAME , TO_CHAR(ACR.RECEIPT_NUMBER) TO_CHAR(ACR.RECEIPT_NU MBER) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , ACR.CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , ACR.COMMENTS AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , (SELECT DISTINCT CASH_RECEIPT_ID, GL_DATE, EVENT_ID, ORG_ID FROM AR_CASH _RECEIPT_HISTORY_ALL) CRH , AR_CASH_RECEIPTS_ALL ACR , AR_CUSTOMERS ARC WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY
AND
SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2
AND AND
SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) SEGMENT4 = NVL(:P_COST_CENTER,GCC.SEGM NVL(:P_COST_CENTER,GCC.SEGM
AND AND AND
SEGMENT5 = :P_NATURAL_ACCT SEGMENT6 = NVL(:P_SUB_ACCT,SEGMENT6) NVL(:P_SUB_ACCT,SEGMENT6) SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT
AND
SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT
)-- ADD BY JAYANTA 11-MAY-2012 ENT4)--ADD BY JAYANTA 8-JUN-2012
7) --ADD BY JAYANTA 8-JUN-2012 8) --ADD BY JAYANTA 8-JUN-2012 --AND
SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G
MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_I GCC.CODE_COMBINATION_ID D AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.APPLICATION_ID = 222 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.EVENT_TYPE_CODE IN ('MISC_RECP_CREATE', 'MISC_RECP_REVERSE', 'MISC_REC P_UPDATE', 'RECP_UPDATE', 'CM_UPDATE', 'RECP_CREATE', 'RECP_REVERSE' ) AND XAH.EVENT_ID = CRH.EVENT_ID AND CRH.ORG_ID = ACR.ORG_ID AND CRH.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID AND ACR.PAY_FROM_CUSTOMER = ARC.CUSTOMER_ID (+) UNION ALL /** Accounting of Application of AR Transactions with AR Receipts **/ SELECT 'AR2' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(ACR.DOC_SEQUENCE_VALUE) TO_CHAR(ACR.DOC_SEQUEN CE_VALUE) AS DOC_NUMBER , ARC.CUSTOMER_NAME AS PARTY_NAME , TO_CHAR(ACR.RECEIPT_NUMBER) TO_CHAR(ACR.RECEIPT_NU MBER) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , ACR.CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , ACR.COMMENTS AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB
, , , ,
GL_IMPORT_REFERENCES GIR XLA_AE_LINES XAL XLA_AE_HEADERS XAH (SELECT DISTINCT CASH_RECEIPT_ID, GL_DATE, EVENT_ID, ORG_ID FROM AR_RECEIVABLE_APPLICATIONS_ALL AR_RECEIVABLE_APPLICATIONS_ALL WHERE EVENT_ID NOT IN (SELECT DISTIN CT EVENT_ID FROM AR_CASH_RECEIPT_HISTORY_A AR_CASH_RECEIPT_HISTORY_ALL) LL) AND STATUS != 'UNAPP'AND CASH_RECEIPT_ID IS NOT NULL ) RAA , AR_CASH_RECEIPTS_ALL ACR , AR_CUSTOMERS ARC WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_I GCC.CODE_COMBINATION_ID D AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.APPLICATION_ID = 222 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.EVENT_TYPE_CODE IN ('MISC_RECP_CREATE', 'MISC_RECP_REVERSE', 'MISC_REC P_UPDATE', 'RECP_UPDATE', 'CM_UPDATE', 'RECP_CREATE', 'RECP_REVERSE' ) AND XAH.EVENT_ID = RAA.EVENT_ID AND RAA.ORG_ID = ACR.ORG_ID AND RAA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID AND ACR.PAY_FROM_CUSTOMER = ARC.CUSTOMER_ID (+) UNION ALL /** Accounting of Application of AR Transactions with Credit Memos **/ SELECT 'AR3' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(RCT.DOC_SEQUENCE_VALUE) TO_CHAR(RCT.DOC_SEQUEN CE_VALUE) AS DOC_NUMBER , ARC.CUSTOMER_NAME AS PARTY_NAME , TO_CHAR(RCT.TRX_NUMBER) TO_CHAR(RCT.TRX_NUMBER ) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , RCT.INVOICE_CURRENCY_CODE RCT.INVOICE_CURRENCY_C ODE AS CURRENCY_CODE
, GCC.CONCATENATED_SEGMENTS , RCT.COMMENTS AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , (SELECT DISTINCT CUSTOMER_TRX_ID, GL_DATE, EVENT_ID, ORG_ID FROM AR_RECEIVABLE_APPLICATIONS_ALL AR_RECEIVABLE_APPLICATIONS_ALL WHERE EVENT_ID NOT IN (SELECT DISTIN CT EVENT_ID FROM AR_CASH_RECEIPT_HISTORY_A AR_CASH_RECEIPT_HISTORY_ALL) LL) AND STATUS != 'UNAPP' AND CASH_RECEIPT_ID IS NULL ) RAA , RA_CUSTOMER_TRX_ALL RCT , AR_CUSTOMERS ARC WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_I GCC.CODE_COMBINATION_ID D AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.APPLICATION_ID = 222 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.EVENT_TYPE_CODE IN ('MISC_RECP_CREATE', 'MISC_RECP_REVERSE', 'MISC_REC P_UPDATE', 'RECP_UPDATE', 'CM_UPDATE', 'RECP_CREATE', 'RECP_REVERSE' ) AND XAH.EVENT_ID = RAA.EVENT_ID AND RAA.ORG_ID = RCT.ORG_ID AND RAA.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID AND RCT.BILL_TO_CUSTOMER_ID = ARC.CUSTOMER_ID /** Accounting of AR Transactions **/ UNION ALL SELECT 'AR4' BLOCK_NUMBER
, GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR( RCT.DOC_SEQUENCE_VALUE RCT.DOC_SEQUENCE_VALUE) ) AS DOC_NUMBER , ARC.CUSTOMER_NAME AS PARTY_NAME , TO_CHAR(RCT.TRX_NUMBER) TO_CHAR(RCT.TRX_NUMBER ) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , RCT.INVOICE_CURRENCY_CODE RCT.INVOICE_CURRENCY_C ODE AS CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , RCT.COMMENTS AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , (SELECT DISTINCT CUSTOMER_TRX_ID, GL_DATE, EVENT_ID, ORG_ID FROM RA_CUST _TRX_LINE_GL_DIST_ALL ) CTGD , RA_CUSTOMER_TRX_ALL RCT , AR_CUSTOMERS ARC WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_I GCC.CODE_COMBINATION_ID D AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID
AND XAL.APPLICATION_ID = 222 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.EVENT_TYPE_CODE IN ('CM_CREATE', 'DEP_CREATE', 'DEP_UPDATE', 'DM_CREAT E', 'DM_UPDATE', 'INV_CREATE', 'INV_UPDATE' ) AND XAH.EVENT_ID = CTGD.EVENT_ID AND CTGD.ORG_ID = RCT.ORG_ID AND CTGD.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID AND RCT.BILL_TO_CUSTOMER_ID = ARC.CUSTOMER_ID /** Accounting of AR Transactions Adjustments **/ UNION ALL SELECT 'AR5' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(RCT.DOC_SEQUENCE_VALUE) TO_CHAR(RCT.DOC_SEQUEN CE_VALUE) AS DOC_NUMBER , ARC.CUSTOMER_NAME AS PARTY_NAME , TO_CHAR(RCT.TRX_NUMBER) TO_CHAR(RCT.TRX_NUMBER ) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , RCT.INVOICE_CURRENCY_CODE RCT.INVOICE_CURRENCY_C ODE AS CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , RCT.COMMENTS AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , AR_ADJUSTMENTS_ALL AAA , RA_CUSTOMER_TRX_ALL RCT , AR_CUSTOMERS ARC WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012
) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_I GCC.CODE_COMBINATION_ID D AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.APPLICATION_ID = 222 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.EVENT_TYPE_CODE IN ('ADJ_CREATE' ) AND XAH.EVENT_ID = AAA.EVENT_ID AND GCC.CODE_COMBINATION_ID = AAA.CODE_COMBINATION_I AAA.CODE_COMBINATION_ID D AND AAA.ORG_ID = RCT.ORG_ID AND AAA.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID AND RCT.BILL_TO_CUSTOMER_ID = ARC.CUSTOMER_ID /*Accounting of Cash Management Entries*/ UNION ALL SELECT 'CM1' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(CPT.BANK_TRXN_NUMBER) TO_CHAR(CPT.BANK_TRXN_ NUMBER) AS DOC_NUMBER , CPT.REPETITIVE_PAYMENT_CODE CPT.REPETITIVE_PAYMENT _CODE PARTY_NAME , TO_CHAR(CPT.TRXN_REFERENCE_NUMBER) TO_CHAR(CPT.TRXN_REFER ENCE_NUMBER) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , CPT.PAYMENT_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , CPT.TRANSACTION_DESCRIPTION CPT.TRANSACTION_DESCRI PTION AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , XLA_TRANSACTION_ENTITIES_UPG XLA_TRANSACTION_ENTITI ES_UPG XTE , CE_CASHFLOWS CEF , CE_PAYMENT_TRANSACTIONS CPT WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3)
AND
SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM
AND AND AND
SEGMENT5 = :P_NATURAL_ACCT SEGMENT6 = NVL(:P_SUB_ACCT,SEGMENT6) NVL(:P_SUB_ACCT,SEGMENT6) SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT
AND
SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT
ENT4)--ADD BY JAYANTA 8-JUN-2012
7) --ADD BY JAYANTA 8-JUN-2012 8) --ADD BY JAYANTA 8-JUN-2012 --AND
SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G
MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_I GCC.CODE_COMBINATION_ID D AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND XAL.APPLICATION_ID = 260 AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.ENTITY_ID = XTE.ENTITY_ID AND XTE.SOURCE_ID_INT_1 = CEF.CASHFLOW_ID AND CEF.TRXN_REFERENCE_NUMBER CEF.TRXN_REFERENCE_NUMBE R = CPT.TRXN_REFERENCE_NU CPT.TRXN_REFERENCE_NUMBER MBER /** Accounting of Inventory Transactions **/ UNION ALL SELECT 'INV1' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(MMT.TRANSACTION_ID) TO_CHAR(MMT.TRANSACTIO N_ID) AS DOC_NUMBER , OOD.ORGANIZATION_NAME AS PARTY_NAME , TO_CHAR(MMT.TRANSACTION_ID) TO_CHAR(MMT.TRANSACTIO N_ID) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , NULL INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , MTT.TRANSACTION_TYPE_NAME MTT.TRANSACTION_TYPE_N AME AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , XLA_TRANSACTION_ENTITIES_UPG XLA_TRANSACTION_ENTITI ES_UPG UPG , MTL_MATERIAL_TRANSACTIONS MTL_MATERIAL_TRANSACTI ONS MMT , MTL_TRANSACTION_TYPES MTT
,
ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS
OOD
WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND GJH.JE_SOURCE = 'Inventory' AND GJH.JE_CATEGORY <>'Receiving' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID --AND XAL.APPLICATION_ID in (707, 555) AND XAH.EVENT_TYPE_CODE NOT IN ('ACTCOSTADJ', 'GLCOSTALOC') -- A DDED BY KAUSIK 14-JUN-2012 AND XAL.APPLICATION_ID NOT IN (200,222,260) -- ADD BY JAYANTA 11-APR2012 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.EVENT_TYPE_CODE NOT IN ('RELE', 'CERT') AND XAH.ENTITY_ID = UPG.ENTITY_ID AND UPG.TRANSACTION_NUMBER = TO_CHAR(MMT.TRANSACTION_ TO_CHAR(MMT.TRANSACTION_ID) ID) --AND UPG.ENTITY_CODE != 'RCV_ACCOUNTING_EVENTS 'RCV_ACCOUNTING_EVENTS' ' -- ADD ON 10-APR-201 2 AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID AND MMT.ORGANIZATION_ID = OOD.ORGANIZATION_ID /** Accounting of India Localization of PO Receipts **/ UNION ALL SELECT 'JAI1' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , NULL DOC_NUMBER , NVL(PV.VENDOR_NAME,OOD.ORGANIZATION_NAME) NVL(PV.VENDOR_NAME,OOD .ORGANIZATION_NAME) AS PARTY_NAME , TO_CHAR(JRT.RECEIPT_NUM) TO_CHAR(JRT.RECEIPT_NU M) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , GJH.CURRENCY_CODE AS INVOICE_CURRENCY_CODE
, , , , , , , , , FROM , , , , , , , ,
GCC.CONCATENATED_SEGMENTS GJL.DESCRIPTION AS NARATION NULL EVENT_TYPE_CODE GJL.ENTERED_DR GJL.ENTERED_CR GJL.ACCOUNTED_DR GJL.ACCOUNTED_CR GIR.GL_SL_LINK_ID GJL.CODE_COMBINATION_ID GL_JE_LINES GL_JE_HEADERS GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GL_JE_BATCHES GL_IMPORT_REFERENCES JAI_RCV_TRANSACTIONS ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS RCV_SHIPMENT_HEADERS PO_VENDORS
GJL GJH GCC GJB GIR JRT OOD RSH PV
WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND GJH.JE_SOURCE = 'Purchasing India' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JRT.TRANSACTION_ TO_CHAR(JRT.TRANSACTION_ID) ID) AND JRT.ORGANIZATION_ID = OOD.ORGANIZATION_ID AND JRT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID AND RSH.VENDOR_ID = PV.VENDOR_ID(+) /** Accounting of India Localization of 'Register India' for Shipments**/ UNION ALL SELECT 'JAI2' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME
, , , , , , , , , , , , , , , , FROM , , , , , ,
GJH.PERIOD_NAME TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE NULL DOC_NUMBER CUSTOMER_NAME AS PARTY_NAME TO_CHAR(WND.DELIVERY_ID) TO_CHAR(WND.DELIVERY_I D) AS TRANSACTION_NUMBER GJH.CURRENCY_CODE AS GL_CURRENCY_CODE GJH.CURRENCY_CODE AS INVOICE_CURRENCY_CODE GCC.CONCATENATED_SEGMENTS GJL.DESCRIPTION AS NARATION NULL EVENT_TYPE_CODE GJL.ENTERED_DR GJL.ENTERED_CR GJL.ACCOUNTED_DR GJL.ACCOUNTED_CR GIR.GL_SL_LINK_ID GJL.CODE_COMBINATION_ID GL_JE_LINES GL_JE_HEADERS GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GL_JE_BATCHES GL_IMPORT_REFERENCES WSH_NEW_DELIVERIES AR_CUSTOMERS
GJL GJH GCC GJB GIR WND ARC
WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND GJH.JE_SOURCE = 'Register India' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_4 != 'ja_in_pla' AND GIR.REFERENCE_4 != 'ja_in_rg23_part_ii' AND GIR.REFERENCE_5 = TO_CHAR(WND.DELIVERY_ID) AND WND.CUSTOMER_ID = ARC.CUSTOMER_ID /** Accounting of Rcv Transactions PO Receipts of non-stockable material**/ UNION ALL SELECT 'CST1' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID
, , , , , , , --, , , , , , --, , , , , , , , , FROM , , , , , , , , , , , ,
GJL.JE_LINE_NUM GJH.JE_SOURCE GJH.JE_CATEGORY GJH.NAME AS JE_NAME GJH.PERIOD_NAME TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE TO_CHAR(RCT.TRANSACTION_ID) TO_CHAR(RCT.TRANSACTIO N_ID) AS DOC_NUMBER OOD.ORGANIZATION_NAME AS PARTY_NAME NVL(PV.VENDOR_NAME,OOD.ORGANIZATION_NAME) NVL(PV.VENDOR_NAME,OOD .ORGANIZATION_NAME) AS PARTY_NAME TO_CHAR(RSH.RECEIPT_NUM) TO_CHAR(RSH.RECEIPT_NU M) TRANSACTION_NUMBER GJH.CURRENCY_CODE AS GL_CURRENCY_CODE NULL INVOICE_CURRENCY_CODE GCC.CONCATENATED_SEGMENTS RCT.TRANSACTION_TYPE AS NARATION RSH.SHIPMENT_NUM AS NARATION XAH.EVENT_TYPE_CODE XAL.ENTERED_DR XAL.ENTERED_CR XAL.ACCOUNTED_DR XAL.ACCOUNTED_CR GIR.GL_SL_LINK_ID XAL.CODE_COMBINATION_ID GL_JE_LINES GJL GL_JE_HEADERS GJH GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC GL_JE_BATCHES GJB GL_IMPORT_REFERENCES GIR XLA_AE_LINES XAL XLA_AE_HEADERS XAH XLA_TRANSACTION_ENTITIES_UPG XLA_TRANSACTION_ENTITI ES_UPG UPG RCV_RECEIVING_SUB_LEDGER RCV_RECEIVING_SUB_LEDG ER RSL RCV_TRANSACTIONS RCT RCV_SHIPMENT_HEADERS RSH ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS OOD PO_VENDORS PV
WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.ENTITY_ID = UPG.ENTITY_ID AND XAL.APPLICATION_ID = 707 AND UPG.TRANSACTION_NUMBER = TO_CHAR(RSL.RCV_TRANSACT TO_CHAR(RSL.RCV_TRANSACTION_ID) ION_ID) AND XAL.CODE_COMBINATION_ID = RSL.CODE_COMBINATION_ID AND RSL.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID AND GJH.ACTUAL_FLAG = RSL.ACTUAL_FLAG AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID AND RSH.VENDOR_ID = PV.VENDOR_ID(+) /** Accounting of Rcv Transactions PO Receipts of stockable material**/ UNION ALL SELECT 'CST2' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , NULL DOC_NUMBER , OOD.ORGANIZATION_NAME AS PARTY_NAME , TO_CHAR(RSH.RECEIPT_NUM) TO_CHAR(RSH.RECEIPT_NU M) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , NULL INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , RCT.TRANSACTION_TYPE AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , XLA_TRANSACTION_ENTITIES_UPG XLA_TRANSACTION_ENTITI ES_UPG UPG , GMF_RCV_ACCOUNTING_TXNS GMF_RCV_ACCOUNTING_TXN S RAT , RCV_TRANSACTIONS RCT , RCV_SHIPMENT_HEADERS RSH , ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS OOD WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012
AND AND AND
SEGMENT5 = :P_NATURAL_ACCT SEGMENT6 = NVL(:P_SUB_ACCT,SEGMENT6) NVL(:P_SUB_ACCT,SEGMENT6) SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT
AND
SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT
7) --ADD BY JAYANTA 8-JUN-2012 8) --ADD BY JAYANTA 8-JUN-2012 --AND
SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G
MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.ENTITY_ID = UPG.ENTITY_ID AND XAL.APPLICATION_ID = 707 AND UPG.SOURCE_ID_INT_1 = TO_CHAR(RAT.ACCOUNTING_T TO_CHAR(RAT.ACCOUNTING_TXN_ID) XN_ID) AND RAT.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID /** Accounting of Delivery of Receipts of stockable material**/ UNION ALL SELECT 'CST3' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(MTA.TRANSACTION_ID) TO_CHAR(MTA.TRANSACTIO N_ID) AS DOC_NUMBER , NVL(PV.VENDOR_NAME,OOD.ORGANIZATION_NAME) NVL(PV.VENDOR_NAME,OOD .ORGANIZATION_NAME) AS PARTY_NAME --, OOD.ORGANIZATION_NAME AS PARTY_NAME , TO_CHAR(RSH.RECEIPT_NUM) TO_CHAR(RSH.RECEIPT_NU M) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , NULL INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , XAH.EVENT_TYPE_CODE||' - '||MSI.SEGMENT1 AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , XLA_TRANSACTION_ENTITIES_UPG XLA_TRANSACTION_ENTITI ES_UPG UPG , (SELECT DISTINCT TRANSACTION_ID
, ORGANIZATION_ID , INVENTORY_ITEM_ID FROM MTL_TRANSACTION_ACCOUNTS) MTA , MTL_SYSTEM_ITEMS_B MSI , ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS OOD , MTL_MATERIAL_TRANSACTIONS MTL_MATERIAL_TRANSACTI ONS MMT , RCV_TRANSACTIONS RCT , RCV_SHIPMENT_HEADERS RSH , PO_VENDORS PV WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.APPLICATION_ID = 707 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.EVENT_TYPE_CODE IN ('PO_DEL_INV','RET_RI_ ('PO_DEL_INV','RET_RI_INV') INV') AND XAH.ENTITY_ID = UPG.ENTITY_ID AND UPG.TRANSACTION_NUMBER = TO_CHAR(MTA.TRANSACTION_ TO_CHAR(MTA.TRANSACTION_ID) ID) AND UPG.SECURITY_ID_INT_1 = MTA.ORGANIZATION_ID AND MTA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MTA.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID AND MMT.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND MTA.ORGANIZATION_ID = OOD.ORGANIZATION_ID AND RSH.VENDOR_ID = PV.VENDOR_ID(+) /** Accounting of Cost Update **/ UNION ALL SELECT 'CST4' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE
, , --, , --, , , , , , , , , , , , FROM , , , , , , , ,
TO_CHAR(MTA.TRANSACTION_ID) AS DOC_NUMBER TO_CHAR(MTA.TRANSACTION_ID) NVL(PV.VENDOR_NAME,OOD.ORGANIZATION_NAME) NVL(PV.VENDOR_NAME,OOD .ORGANIZATION_NAME) AS PARTY_NAME OOD.ORGANIZATION_NAME AS PARTY_NAME TO_CHAR(RSH.RECEIPT_NUM) TO_CHAR(RSH.RECEIPT_NU M) AS TRANSACTION_NUMBER TO_CHAR(MTA.TRANSACTION_ID) TO_CHAR(MTA.TRANSACTION_ ID) AS TRANSACTION_NUMBER GJH.CURRENCY_CODE AS GL_CURRENCY_CODE NULL INVOICE_CURRENCY_CODE GCC.CONCATENATED_SEGMENTS XAH.EVENT_TYPE_CODE||' - '||MSI.SEGMENT1 AS NARATION XAH.EVENT_TYPE_CODE XAL.ENTERED_DR XAL.ENTERED_CR XAL.ACCOUNTED_DR XAL.ACCOUNTED_CR GIR.GL_SL_LINK_ID XAL.CODE_COMBINATION_ID GL_JE_LINES GJL GL_JE_HEADERS GJH GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC GL_JE_BATCHES GJB GL_IMPORT_REFERENCES GIR XLA_AE_LINES XAL XLA_AE_HEADERS XAH XLA_TRANSACTION_ENTITIES_UPG XLA_TRANSACTION_ENTITI ES_UPG UPG (SELECT DISTINCT TRANSACTION_ID , ORGANIZATION_ID , INVENTORY_ITEM_ID FROM MTL_TRANSACTION_ACCOUNTS) MTA , MTL_SYSTEM_ITEMS_B MSI , ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS OOD , MTL_MATERIAL_TRANSACTIONS MTL_MATERIAL_TRANSACTI ONS MMT , RCV_TRANSACTIONS RCT , RCV_SHIPMENT_HEADERS RSH , PO_VENDORS PV WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM
AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.APPLICATION_ID = 707 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.EVENT_TYPE_CODE = 'AVG_COST_UPD' AND XAH.ENTITY_ID = UPG.ENTITY_ID AND UPG.TRANSACTION_NUMBER = TO_CHAR(MTA.TRANSACTION_ TO_CHAR(MTA.TRANSACTION_ID) ID) AND UPG.SECURITY_ID_INT_1 = MTA.ORGANIZATION_ID AND MTA.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID AND MTA.ORGANIZATION_ID = MSI.ORGANIZATION_ID AND MTA.ORGANIZATION_ID = OOD.ORGANIZATION_ID AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID AND MMT.TRANSACTION_REFERENCE MMT.TRANSACTION_REFERENC E = RCT.TRANSACTION_ID AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RSH.VENDOR_ID = PV.VENDOR_ID(+) /** Accounting of WIP Transactions **/ UNION ALL SELECT 'WIP1' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(CSTD.TRANSACTION_ID) TO_CHAR(CSTD.TRANSACTI ON_ID) AS DOC_NUMBER , OOD.ORGANIZATION_NAME AS PARTY_NAME , TO_CHAR(CSTD.TRANSACTION_ID) TO_CHAR(CSTD.TRANSACTI ON_ID) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , NULL INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , CSTD.TRANSACTION_TYPE_NAME CSTD.TRANSACTION_TYPE_ NAME ||' - '||CSTD.WIP_ENTITY_NA '||CSTD.WIP_ENTITY_NAME ME AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , (SELECT DISTINCT ENTITY_ID, TRANSACTION_NUMBER FROM XLA_TRANSACTION_ENTITIES_ XLA_TRANSACTION_ENTITIES_UPG) UPG) UPG , (SELECT DISTINCT TRANSACTION_ID, WIP_ENTITY_ID, WIP_ENTITY_NAME, TRANSAC TION_TYPE_NAME, ORGANIZATION_ID FROM CST_DISTRIBUTION_V ) CSTD , ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS OOD WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3)
AND
SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM
AND AND AND
SEGMENT5 = :P_NATURAL_ACCT SEGMENT6 = NVL(:P_SUB_ACCT,SEGMENT6) NVL(:P_SUB_ACCT,SEGMENT6) SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT
AND
SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT
ENT4)--ADD BY JAYANTA 8-JUN-2012
7) --ADD BY JAYANTA 8-JUN-2012 8) --ADD BY JAYANTA 8-JUN-2012 --AND
SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G
MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.EVENT_TYPE_CODE IN ( 'RESOURCE_ABSORPTION' 'RESOURCE_ABSORPTION', , 'JOB_CLOSE_VARIANCE' , 'WIP_ASSEMBLY_COMPLETION 'WIP_ASSEMBLY_COMPLETION', ', 'WIP_COMP_ISSUE') AND XAH.ENTITY_ID = UPG.ENTITY_ID AND UPG.TRANSACTION_NUMBER = TO_CHAR(CSTD.TRANSACTION TO_CHAR(CSTD.TRANSACTION_ID) _ID) AND CSTD.ORGANIZATION_ID = OOD.ORGANIZATION_ID /** Accounting Accounting of VAT in Order Order Mangement Mangement **/ UNION ALL SELECT 'VAT1' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , NULL DOC_NUMBER , ARC.CUSTOMER_NAME AS PARTY_NAME , JWL.VAT_INVOICE_NO AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , JJE.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , 'DELIVERY_NAME'||'-'||(SELECT 'DELIVERY_NAME'||'-'|| (SELECT NAME FROM WSH_NEW_DELIVERIES WHERE DELIVER Y_ID = JWL.DELIVERY_ID) NARATION , JJE.SOURCE AS EVENT_TYPE_CODE , JJE.ACCOUNTED_DR AS ENTERED_DR , JJE.ACCOUNTED_CR AS ENTERED_CR , JJE.ACCOUNTED_DR , JJE.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , JJE.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_JOURNAL_ENTRIES JAI_CMN_JOURNAL_ENTRIE S JJE , JAI_OM_WSH_LINES_ALL JWL , AR_CUSTOMERS ARC
WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JJE.SOURCE_TRX_I TO_CHAR(JJE.SOURCE_TRX_ID) D) AND GCC.CODE_COMBINATION_ID = JJE.CODE_COMBINATION_ID AND GJL.PERIOD_NAME = JJE.PERIOD_NAME AND JJE.TAX_TYPE = 'VALUE ADDED TAX' AND JJE.SOURCE = 'WSH' AND JJE.SOURCE_TRX_ID = JWL.DELIVERY_DETAIL_ID AND JWL.CUSTOMER_ID = ARC.CUSTOMER_ID /** Accounting of VAT Manual Transaction Inward**/ UNION ALL SELECT 'VAT2' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , NULL DOC_NUMBER , POV.VENDOR_NAME AS PARTY_NAME , JMT.INVOICE_NUMBER AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , JJE.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , JJE.SOURCE||'-'||JMT.TRANSACTION_TYPE_CODE JJE.SOURCE||'-'||JMT.T RANSACTION_TYPE_CODE AS NARATION , JMT.TRANSACTION_TYPE_CODE JMT.TRANSACTION_TYPE_C ODE AS EVENT_TYPE_CODE , JJE.ACCOUNTED_DR AS ENTERED_DR , JJE.ACCOUNTED_CR AS ENTERED_CR , JJE.ACCOUNTED_DR , JJE.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , JJE.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH
, GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_JOURNAL_ENTRIES JAI_CMN_JOURNAL_ENTRIE S JJE , JAI_RGM_MANUAL_TRXS JMT , PO_VENDORS POV WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JJE.SOURCE_TRX_I TO_CHAR(JJE.SOURCE_TRX_ID) D) AND GCC.CODE_COMBINATION_ID = JJE.CODE_COMBINATION_ID AND GJL.PERIOD_NAME = JJE.PERIOD_NAME AND JJE.TAX_TYPE = 'VALUE ADDED TAX' AND JJE.SOURCE = 'MANUAL' AND JJE.SOURCE_TRX_ID = JMT.TRANSACTION_NUMBER AND JMT.PARTY_TYPE = 'VENDOR' AND JMT.PARTY_ID = POV.VENDOR_ID /** Accounting of VAT Manual Transaction Outward **/ UNION ALL SELECT 'VAT3' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , NULL DOC_NUMBER , ARC.CUSTOMER_NAME AS PARTY_NAME , JMT.INVOICE_NUMBER AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , JJE.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , JJE.SOURCE||'-'||JMT.TRANSACTION_TYPE_CODE JJE.SOURCE||'-'||JMT.T RANSACTION_TYPE_CODE AS NARATION , JMT.TRANSACTION_TYPE_CODE JMT.TRANSACTION_TYPE_C ODE AS EVENT_TYPE_CODE , JJE.ACCOUNTED_DR AS ENTERED_DR
, JJE.ACCOUNTED_CR AS ENTERED_CR , JJE.ACCOUNTED_DR , JJE.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , JJE.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_JOURNAL_ENTRIES JAI_CMN_JOURNAL_ENTRIE S JJE , JAI_RGM_MANUAL_TRXS JMT , AR_CUSTOMERS ARC WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JJE.SOURCE_TRX_I TO_CHAR(JJE.SOURCE_TRX_ID) D) AND GCC.CODE_COMBINATION_ID = JJE.CODE_COMBINATION_ID AND GJL.PERIOD_NAME = JJE.PERIOD_NAME AND JJE.TAX_TYPE = 'VALUE ADDED TAX' AND JJE.SOURCE = 'MANUAL' AND JJE.SOURCE_TRX_ID = JMT.TRANSACTION_NUMBER AND JMT.PARTY_TYPE = 'CUSTOMER' AND JMT.PARTY_ID = ARC.CUSTOMER_ID /** Accounting of VAT Attached with AR Transaction **/ UNION ALL SELECT 'VAT4' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(RCT.DOC_SEQUENCE_VALUE) TO_CHAR(RCT.DOC_SEQUEN CE_VALUE) AS DOC_NUMBER , ARC.CUSTOMER_NAME AS PARTY_NAME
, RCT.TRX_NUMBER AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , JJE.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , RCT.COMMENTS AS NARATION , 'AR TRANSACTIONS' AS EVENT_TYPE_CODE , JJE.ACCOUNTED_DR AS ENTERED_DR , JJE.ACCOUNTED_CR AS ENTERED_CR , JJE.ACCOUNTED_DR , JJE.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , JJE.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_JOURNAL_ENTRIES JAI_CMN_JOURNAL_ENTRIE S JJE , RA_CUSTOMER_TRX_LINES_ALL RA_CUSTOMER_TRX_LINES_ ALL CTL , RA_CUSTOMER_TRX_ALL RCT , AR_CUSTOMERS ARC WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JJE.SOURCE_TRX_I TO_CHAR(JJE.SOURCE_TRX_ID) D) AND GCC.CODE_COMBINATION_ID = JJE.CODE_COMBINATION_ID AND GJL.PERIOD_NAME = JJE.PERIOD_NAME AND JJE.TAX_TYPE = 'VALUE ADDED TAX' AND JJE.SOURCE = 'AR' AND JJE.SOURCE_TRX_ID = CTL.CUSTOMER_TRX_LINE_ID AND CTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID AND RCT.BILL_TO_CUSTOMER_ID = ARC.CUSTOMER_ID /** Accounting of VAT Settlement**/ UNION ALL SELECT 'VAT5' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID
, GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , NULL AS DOC_NUMBER , POV.VENDOR_NAME AS PARTY_NAME , JRS.CHALLAN_NO AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , JJE.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , GJL.DESCRIPTION AS NARATION , 'VAT_SETTLEMENT' AS EVENT_TYPE_CODE , JJE.ACCOUNTED_DR AS ENTERED_DR , JJE.ACCOUNTED_CR AS ENTERED_CR , JJE.ACCOUNTED_DR , JJE.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , JJE.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_JOURNAL_ENTRIES JAI_CMN_JOURNAL_ENTRIE S JJE , JAI_RGM_SETTLEMENTS JRS , PO_VENDORS POV WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JJE.SOURCE_TRX_I TO_CHAR(JJE.SOURCE_TRX_ID) D) AND GCC.CODE_COMBINATION_ID = JJE.CODE_COMBINATION_ID AND GJL.PERIOD_NAME = JJE.PERIOD_NAME AND JJE.TAX_TYPE = 'VALUE ADDED TAX' AND JJE.SOURCE = 'SETTLE_IN' AND JJE.SOURCE_TRX_ID = JRS.SETTLEMENT_ID
AND JRS.TAX_AUTHORITY_ID = POV.VENDOR_ID /** Accounting of Service Tax Credit (Payables)**/ UNION ALL SELECT 'STAP' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , (SELECT TO_CHAR(DOC_SEQUENCE_V TO_CHAR(DOC_SEQUENCE_VALUE) ALUE) FROM AP_INVOICES_ALL WHERE INVOICE_I D=AIP.INVOICE_ID) DOC_NUMBER , APC.VENDOR_NAME AS PARTY_NAME , (SELECT INVOICE_NUM FROM AP_INVOICES_ALL WHERE INVOICE_ID=AIP.INVOICE_I INVOICE_ID=AIP.INVOICE_ID D ) TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , JJE.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , GJL.DESCRIPTION AS NARATION , 'SERVICE' AS EVENT_TYPE_CODE , JJE.ACCOUNTED_DR AS ENTERED_DR , JJE.ACCOUNTED_CR AS ENTERED_CR , JJE.ACCOUNTED_DR , JJE.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , JJE.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_JOURNAL_ENTRIES JAI_CMN_JOURNAL_ENTRIE S JJE , AP_INVOICE_PAYMENTS_ALL AP_INVOICE_PAYMENTS_AL L AIP , AP_CHECKS_ALL APC WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID
AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JJE.SOURCE_TRX_I TO_CHAR(JJE.SOURCE_TRX_ID) D) AND GCC.CODE_COMBINATION_ID = JJE.CODE_COMBINATION_ID AND GJL.PERIOD_NAME = JJE.PERIOD_NAME AND JJE.REGIME_CODE = 'SERVICE' AND JJE.SOURCE = 'AP' AND JJE.SOURCE_TRX_ID = AIP.INVOICE_PAYMENT_ID AND AIP.CHECK_ID = APC.CHECK_ID /** Accounting Accounting of Service Service Tax Credit on Invoice(Payables)**/ Invoice(Payables)**/ UNION ALL SELECT 'STAP1' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , (SELECT TO_CHAR(DOC_SEQUENCE_V TO_CHAR(DOC_SEQUENCE_VALUE) ALUE) FROM AP_INVOICES_ALL WHERE INVOICE_I D=AIA.INVOICE_ID) DOC_NUMBER , POV.VENDOR_NAME AS PARTY_NAME , (SELECT INVOICE_NUM FROM AP_INVOICES_ALL WHERE INVOICE_ID=AIA.INVOICE_I INVOICE_ID=AIA.INVOICE_ID D ) TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , JJE.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , GJL.DESCRIPTION AS NARATION , 'SERVICE' AS EVENT_TYPE_CODE , JJE.ACCOUNTED_DR AS ENTERED_DR , JJE.ACCOUNTED_CR AS ENTERED_CR , JJE.ACCOUNTED_DR , JJE.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , JJE.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_JOURNAL_ENTRIES JAI_CMN_JOURNAL_ENTRIE S JJE , AP_INVOICE_DISTRIBUTIONS_ALL AP_INVOICE_DISTRIBUTIO NS_ALL AID , AP_INVOICES_ALL AIA , PO_VENDORS POV WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012
--AND
SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G
MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JJE.SOURCE_TRX_I TO_CHAR(JJE.SOURCE_TRX_ID) D) AND GCC.CODE_COMBINATION_ID = JJE.CODE_COMBINATION_ID AND GJL.PERIOD_NAME = JJE.PERIOD_NAME AND JJE.REGIME_CODE = 'SERVICE' AND JJE.SOURCE = 'AP' AND JJE.SOURCE_TRX_ID = AID.INVOICE_DISTRIBUTION AID.INVOICE_DISTRIBUTION_ID _ID AND AID.INVOICE_ID = AIA.INVOICE_ID AND AIA.VENDOR_ID = POV.VENDOR_ID /** Accounting of Service Tax Manual Transaction Inward**/ UNION ALL SELECT 'STMI' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , NULL DOC_NUMBER , POV.VENDOR_NAME AS PARTY_NAME , JMT.INVOICE_NUMBER AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , JJE.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , JJE.SOURCE||'-'||JMT.TRANSACTION_TYPE_CODE JJE.SOURCE||'-'||JMT.T RANSACTION_TYPE_CODE AS NARATION , JMT.TRANSACTION_TYPE_CODE JMT.TRANSACTION_TYPE_C ODE AS EVENT_TYPE_CODE , JJE.ACCOUNTED_DR AS ENTERED_DR , JJE.ACCOUNTED_CR AS ENTERED_CR , JJE.ACCOUNTED_DR , JJE.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , JJE.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_JOURNAL_ENTRIES JAI_CMN_JOURNAL_ENTRIE S JJE , JAI_RGM_MANUAL_TRXS JMT , PO_VENDORS POV WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM
ENT4)--ADD BY JAYANTA 8-JUN-2012 AND AND AND
SEGMENT5 = :P_NATURAL_ACCT SEGMENT6 = NVL(:P_SUB_ACCT,SEGMENT6) NVL(:P_SUB_ACCT,SEGMENT6) SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT
AND
SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT
7) --ADD BY JAYANTA 8-JUN-2012 8) --ADD BY JAYANTA 8-JUN-2012 --AND
SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G
MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JJE.SOURCE_TRX_I TO_CHAR(JJE.SOURCE_TRX_ID) D) AND GCC.CODE_COMBINATION_ID = JJE.CODE_COMBINATION_ID AND GJL.PERIOD_NAME = JJE.PERIOD_NAME AND JJE.REGIME_CODE = 'SERVICE' AND JJE.SOURCE = 'MANUAL' AND JJE.SOURCE_TRX_ID = JMT.TRANSACTION_NUMBER AND JMT.PARTY_TYPE = 'VENDOR' AND JMT.PARTY_ID = POV.VENDOR_ID /** Accounting of Service Tax Manual Transactions Outward **/ UNION ALL SELECT 'STM' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , NULL DOC_NUMBER , ARC.CUSTOMER_NAME AS PARTY_NAME , JMT.INVOICE_NUMBER AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , JJE.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , JJE.SOURCE||'-'||JMT.TRANSACTION_TYPE_CODE JJE.SOURCE||'-'||JMT.T RANSACTION_TYPE_CODE AS NARATION , JMT.TRANSACTION_TYPE_CODE JMT.TRANSACTION_TYPE_C ODE AS EVENT_TYPE_CODE , JJE.ACCOUNTED_DR AS ENTERED_DR , JJE.ACCOUNTED_CR AS ENTERED_CR , JJE.ACCOUNTED_DR , JJE.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , JJE.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_JOURNAL_ENTRIES JAI_CMN_JOURNAL_ENTRIE S JJE , JAI_RGM_MANUAL_TRXS JMT , AR_CUSTOMERS ARC WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID
AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JJE.SOURCE_TRX_I TO_CHAR(JJE.SOURCE_TRX_ID) D) AND GCC.CODE_COMBINATION_ID = JJE.CODE_COMBINATION_ID AND GJL.PERIOD_NAME = JJE.PERIOD_NAME AND JJE.REGIME_CODE = 'SERVICE' AND JJE.SOURCE = 'MANUAL' AND JJE.SOURCE_TRX_ID = JMT.TRANSACTION_NUMBER AND JMT.PARTY_TYPE = 'CUSTOMER' AND JMT.PARTY_ID = ARC.CUSTOMER_ID /** Accounting of Service Tax Distribution **/ UNION ALL SELECT 'STDT' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , NULL DOC_NUMBER , (SELECT NAME FROM HR_ALL_ORGANIZATION_UNIT HR_ALL_ORGANIZATION_UNITS S WHERE ORGANIZATION_ID = JTR. ORGANIZATION_ID) AS PARTY_NAME , NULL AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , JJE.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , JTR.TAX_TYPE||' - '||SOURCE_TRX_TYPE AS NARATION , JTR.SOURCE_TRX_TYPE AS EVENT_TYPE_CODE , JJE.ACCOUNTED_DR AS ENTERED_DR , JJE.ACCOUNTED_CR AS ENTERED_CR , JJE.ACCOUNTED_DR , JJE.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , JJE.CODE_COMBINATION_ID FROM GL_JE_LINES GJL
, GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_JOURNAL_ENTRIES JAI_CMN_JOURNAL_ENTRIE S JJE , JAI_RGM_TRX_RECORDS JTR WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JJE.SOURCE_TRX_I TO_CHAR(JJE.SOURCE_TRX_ID) D) AND GCC.CODE_COMBINATION_ID = JJE.CODE_COMBINATION_ID AND GJL.PERIOD_NAME = JJE.PERIOD_NAME AND JJE.REGIME_CODE = 'SERVICE' AND JJE.SOURCE = 'SERVICE_DISTRIBUTE_OUT' AND JJE.REPOSITORY_ID = JTR.REPOSITORY_ID /** Accounting of Service Tax Attached with AR Transaction **/ UNION ALL SELECT 'STAR' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(RCT.DOC_SEQUENCE_VALUE) TO_CHAR(RCT.DOC_SEQUEN CE_VALUE) AS DOC_NUMBER , ARC.CUSTOMER_NAME AS PARTY_NAME , RCT.TRX_NUMBER AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , JJE.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , RCT.COMMENTS AS NARATION , 'AR TRANSACTIONS' AS EVENT_TYPE_CODE , JJE.ACCOUNTED_DR AS ENTERED_DR , JJE.ACCOUNTED_CR AS ENTERED_CR , JJE.ACCOUNTED_DR
, JJE.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , JJE.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_JOURNAL_ENTRIES JAI_CMN_JOURNAL_ENTRIE S JJE , RA_CUSTOMER_TRX_LINES_ALL RA_CUSTOMER_TRX_LINES_ ALL CTL , RA_CUSTOMER_TRX_ALL RCT , AR_CUSTOMERS ARC WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JJE.SOURCE_TRX_I TO_CHAR(JJE.SOURCE_TRX_ID) D) AND GCC.CODE_COMBINATION_ID = JJE.CODE_COMBINATION_ID AND GJL.PERIOD_NAME = JJE.PERIOD_NAME AND JJE.REGIME_CODE = 'SERVICE' AND JJE.SOURCE = 'AR' AND JJE.SOURCE_TRX_ID = CTL.CUSTOMER_TRX_LINE_ID AND CTL.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID AND RCT.BILL_TO_CUSTOMER_ID = ARC.CUSTOMER_ID /** Accounting of Service Tax Settlement**/ UNION ALL SELECT 'STST' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , NULL AS DOC_NUMBER , POV.VENDOR_NAME AS PARTY_NAME , JRS.CHALLAN_NO AS TRANSACTION_NUMBER
, GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , JJE.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , GJL.DESCRIPTION AS NARATION , 'VAT_SETTLEMENT' AS EVENT_TYPE_CODE , JJE.ACCOUNTED_DR AS ENTERED_DR , JJE.ACCOUNTED_CR AS ENTERED_CR , JJE.ACCOUNTED_DR , JJE.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , JJE.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_JOURNAL_ENTRIES JAI_CMN_JOURNAL_ENTRIE S JJE , JAI_RGM_SETTLEMENTS JRS , PO_VENDORS POV WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_5 = TO_CHAR(JJE.SOURCE_TRX_I TO_CHAR(JJE.SOURCE_TRX_ID) D) AND GCC.CODE_COMBINATION_ID = JJE.CODE_COMBINATION_ID AND GJL.PERIOD_NAME = JJE.PERIOD_NAME AND JJE.REGIME_CODE = 'SERVICE' AND JJE.SOURCE = 'SETTLE_IN' AND JJE.SOURCE_TRX_ID = JRS.SETTLEMENT_ID AND JRS.TAX_AUTHORITY_ID = POV.VENDOR_ID /** Accounting of Rcv Transactions of PO Receipts for OPM **/ UNION ALL SELECT 'INV2' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY
, , , , , , , , , , , , , , , , , FROM , , , , , , , , , , ,
GJH.NAME AS JE_NAME GJH.PERIOD_NAME TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE NULL DOC_NUMBER OOD.ORGANIZATION_NAME AS PARTY_NAME TO_CHAR(RSH.RECEIPT_NUM) TO_CHAR(RSH.RECEIPT_NU M) AS TRANSACTION_NUMBER GJH.CURRENCY_CODE AS GL_CURRENCY_CODE NULL INVOICE_CURRENCY_CODE GCC.CONCATENATED_SEGMENTS RCT.TRANSACTION_TYPE AS NARATION XAH.EVENT_TYPE_CODE XAL.ENTERED_DR XAL.ENTERED_CR XAL.ACCOUNTED_DR XAL.ACCOUNTED_CR GIR.GL_SL_LINK_ID XAL.CODE_COMBINATION_ID GL_JE_LINES GL_JE_HEADERS GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GL_JE_BATCHES GL_IMPORT_REFERENCES XLA_AE_LINES XLA_AE_HEADERS XLA_TRANSACTION_ENTITIES_UPG XLA_TRANSACTION_ENTITI ES_UPG GMF_RCV_ACCOUNTING_TXNS GMF_RCV_ACCOUNTING_TXN S RCV_TRANSACTIONS RCV_SHIPMENT_HEADERS ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS
GJL GJH GCC GJB GIR XAL XAH UPG RAT RCT RSH OOD
WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND GJH.JE_SOURCE = 'Inventory' AND GJH.JE_CATEGORY = 'Receiving' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID
AND XAH.ENTITY_ID = UPG.ENTITY_ID AND XAL.APPLICATION_ID = 555 AND UPG.SOURCE_ID_CHAR_1 <> 'PAYABLES_INVOICE' AND UPG.TRANSACTION_NUMBER = TO_CHAR(RAT.ACCOUNTING_T TO_CHAR(RAT.ACCOUNTING_TXN_ID) XN_ID) AND UPG.SECURITY_ID_INT_1 = RCT.ORGANIZATION_ID AND RAT.RCV_TRANSACTION_ID = RCT.TRANSACTION_ID AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RSH.SHIP_TO_ORG_ID = OOD.ORGANIZATION_ID /** Accounting of ManualTransactions PLA Register**/ UNION ALL SELECT 'JAI3' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(PLA.SLNO) AS DOC_NUMBER , OOD.ORGANIZATION_NAME AS PARTY_NAME , TO_CHAR(PLA.DR_INVOICE_NO) TO_CHAR(PLA.DR_INVOICE _NO) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , GJH.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , GJL.DESCRIPTION AS NARATION , NULL EVENT_TYPE_CODE , GJL.ENTERED_DR , GJL.ENTERED_CR , GJL.ACCOUNTED_DR , GJL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , GJL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_RG_PLA_TRXS PLA , ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS OOD WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND GJH.JE_SOURCE = 'Register India' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_4 = 'ja_in_pla' AND SUBSTR(GIR.REFERENCE_5,1,(INSTR(GIR.REFERENCE_5, SUBSTR(GIR.REFERENCE_5,1 ,(INSTR(GIR.REFERENCE_5, ':',1)-1)) = TO_CHAR(PLA. REGISTER_ID) AND PLA.ORGANIZATION_ID = OOD.ORGANIZATION_ID /** Accounting of ManualTransactions in RG23 A and C P-II Register**/ UNION ALL SELECT 'JAI4' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(RGAC.SLNO) AS DOC_NUMBER , OOD.ORGANIZATION_NAME AS PARTY_NAME , TO_CHAR(RGAC.EXCISE_INVOICE_NO) TO_CHAR(RGAC.EXCISE_IN VOICE_NO) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , GJH.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , GJL.DESCRIPTION AS NARATION , NULL EVENT_TYPE_CODE , GJL.ENTERED_DR , GJL.ENTERED_CR , GJL.ACCOUNTED_DR , GJL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , GJL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_CMN_RG_23AC_II_TRXS JAI_CMN_RG_23AC_II_TRX S RGAC , ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS OOD WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012
--AND
SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G
MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND GJH.JE_SOURCE = 'Register India' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_4 = 'ja_in_rg23_part_ii' AND GIR.REFERENCE_5 = TO_CHAR(RGAC.REGISTER_ID TO_CHAR(RGAC.REGISTER_ID) ) AND RGAC.ORGANIZATION_ID = OOD.ORGANIZATION_ID /**Accounting of VAT Claim Add by 9-APR-2012**/ UNION ALL SELECT 'JAI5' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , NULL DOC_NUMBER , OOD.ORGANIZATION_NAME AS PARTY_NAME , TO_CHAR(JRT.RECEIPT_NUM) TO_CHAR(JRT.RECEIPT_NU M) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , GJH.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , GJL.DESCRIPTION AS NARATION , NULL EVENT_TYPE_CODE , GJL.ENTERED_DR , GJL.ENTERED_CR , GJL.ACCOUNTED_DR , GJL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , GJL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_RGM_TRX_RECORDS RTR , JAI_RCV_TRANSACTIONS JRT , ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS OOD WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6)
AND
SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT
AND
SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT
7) --ADD BY JAYANTA 8-JUN-2012 8) --ADD BY JAYANTA 8-JUN-2012 --AND
SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G
MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND GJH.JE_SOURCE = 'Purchasing India' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_6 = TO_CHAR(RTR.REPOSITORY_I TO_CHAR(RTR.REPOSITORY_ID) D) AND RTR.SOURCE_DOCUMENT_ID = JRT.TRANSACTION_ID AND JRT.ORGANIZATION_ID = OOD.ORGANIZATION_ID /** Accounting of India Localization of RTV Transactions**/ UNION ALL SELECT 'JAI6' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , NULL DOC_NUMBER , OOD.ORGANIZATION_NAME AS PARTY_NAME , TO_CHAR(JRT.RECEIPT_NUM) TO_CHAR(JRT.RECEIPT_NU M) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , GJH.CURRENCY_CODE AS INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , GJL.DESCRIPTION AS NARATION , NULL EVENT_TYPE_CODE , GJL.ENTERED_DR , GJL.ENTERED_CR , GJL.ACCOUNTED_DR , GJL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , GJL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , JAI_RCV_TRANSACTIONS JRT , ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS OOD WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM
ENT4)--ADD BY JAYANTA 8-JUN-2012 AND AND AND
SEGMENT5 = :P_NATURAL_ACCT SEGMENT6 = NVL(:P_SUB_ACCT,SEGMENT6) NVL(:P_SUB_ACCT,SEGMENT6) SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT
AND
SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT
7) --ADD BY JAYANTA 8-JUN-2012 8) --ADD BY JAYANTA 8-JUN-2012 --AND
SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G
MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND GJH.JE_SOURCE = 'Purchasing India' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.REFERENCE_6 = TO_CHAR(JRT.TRANSACTION_ TO_CHAR(JRT.TRANSACTION_ID) ID) AND JRT.ORGANIZATION_ID = OOD.ORGANIZATION_ID /** Accounting of OPM Production**/ UNION ALL SELECT 'INV3' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(GEH.TRANSACTION_ID) TO_CHAR(GEH.TRANSACTIO N_ID) AS DOC_NUMBER , OOD.ORGANIZATION_NAME AS PARTY_NAME , TO_CHAR(GEH.TRANSACTION_ID) TO_CHAR(GEH.TRANSACTIO N_ID) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , NULL INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , GJL.DESCRIPTION||' - '||GEH.EVENT_CLASS_CO '||GEH.EVENT_CLASS_CODE||' DE||' - '||GBH.BATCH_NO AS NAR ATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , XLA_TRANSACTION_ENTITIES_UPG XLA_TRANSACTION_ENTITI ES_UPG UPG , GMF_XLA_EXTRACT_HEADERS GMF_XLA_EXTRACT_HEADER S GEH , GME_BATCH_HEADER GBH , ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS OOD WHERE GJL.JE_HEADER_ID
= GJH.JE_HEADER_ID
AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.APPLICATION_ID = 555 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.EVENT_TYPE_CODE IN ('RELE', 'CERT') AND XAH.ENTITY_ID = UPG.ENTITY_ID AND UPG.ENTITY_CODE = 'PRODUCTION' AND UPG.TRANSACTION_NUMBER = TO_CHAR(GEH.TRANSACTION_ TO_CHAR(GEH.TRANSACTION_ID) ID) AND XAH.EVENT_ID = GEH.EVENT_ID AND UPG.SECURITY_ID_INT_1 = GBH.ORGANIZATION_ID AND GEH.SOURCE_DOCUMENT_ID = GBH.BATCH_ID AND GBH.ORGANIZATION_ID = OOD.ORGANIZATION_ID /** Accounting of OPM Cost Revaluation**/ UNION ALL SELECT 'INV4' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(GTV.TRANSACTION_ID) TO_CHAR(GTV.TRANSACTIO N_ID) AS DOC_NUMBER , OOD.ORGANIZATION_NAME AS PARTY_NAME , TO_CHAR(GTV.TRANSACTION_ID) TO_CHAR(GTV.TRANSACTIO N_ID) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , NULL INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , GJL.DESCRIPTION||' - '||GTV.TRANSACTION_SO '||GTV.TRANSACTION_SOURCE||' URCE||' - '||GTV.ITEM_NUMBER A S NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR
, , FROM , , , , , , , ,
GIR.GL_SL_LINK_ID XAL.CODE_COMBINATION_ID GL_JE_LINES GL_JE_HEADERS GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GL_JE_BATCHES GL_IMPORT_REFERENCES XLA_AE_LINES XLA_AE_HEADERS XLA_TRANSACTION_ENTITIES_UPG XLA_TRANSACTION_ENTITI ES_UPG (SELECT DISTINCT TRANSACTION_ID ,ORGANIZATION_ID ,INVENTORY_ITEM_ID , ITEM_NUMBER , ENTITY_CODE , EVENT_CLASS_CODE by Kausik on 15-JUN-2012 , TRANSACTION_SOURCE FROM GMF_TRANSACTION_VALUATION GMF_TRANSACTION_VALUATION) ) , ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS
GJL GJH GCC GJB GIR XAL XAH UPG
--Added GTV OOD
WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A' AND TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DATE ) BETWEEN :P_START_DATE AND :P_END_DATE AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID AND GJH.JE_BATCH_ID = GIR.JE_BATCH_ID AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID AND XAL.APPLICATION_ID = 555 AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID AND XAH.ENTITY_ID = UPG.ENTITY_ID AND UPG.ENTITY_CODE = 'REVALUATION' AND UPG.ENTITY_CODE = GTV.ENTITY_CODE AND UPG.TRANSACTION_NUMBER = TO_CHAR(GTV.TRANSACTION_ TO_CHAR(GTV.TRANSACTION_ID) ID) AND UPG.SECURITY_ID_INT_1 = GTV.ORGANIZATION_ID AND XAH.EVENT_TYPE_CODE = GTV.EVENT_CLASS_CODE -- Added by Kausik on 1 5-JUN-2012 AND GTV.ORGANIZATION_ID = OOD.ORGANIZATION_ID /** Accounting of Invoice Price Varience**/ UNION ALL
SELECT 'IPV' BLOCK_NUMBER , GJB.NAME AS JE_BATCH_NAME , GJL.JE_HEADER_ID , GJL.JE_LINE_NUM , GJH.JE_SOURCE , GJH.JE_CATEGORY , GJH.NAME AS JE_NAME , GJH.PERIOD_NAME , TRUNC(GJL.EFFECTIVE_DATE) TRUNC(GJL.EFFECTIVE_DA TE) AS GL_DATE , TO_CHAR(GEH.TRANSACTION_ID) TO_CHAR(GEH.TRANSACTIO N_ID) AS DOC_NUMBER , OOD.ORGANIZATION_NAME AS PARTY_NAME , TO_CHAR(AIA.INVOICE_NUM) TO_CHAR(AIA.INVOICE_NU M) AS TRANSACTION_NUMBER , GJH.CURRENCY_CODE AS GL_CURRENCY_CODE , NULL INVOICE_CURRENCY_CODE , GCC.CONCATENATED_SEGMENTS , GJL.DESCRIPTION||' - '||GEH.EVENT_CLASS_CO '||GEH.EVENT_CLASS_CODE||' DE||' - '||AIA.INVOICE_NUM AS NARATION , XAH.EVENT_TYPE_CODE , XAL.ENTERED_DR , XAL.ENTERED_CR , XAL.ACCOUNTED_DR , XAL.ACCOUNTED_CR , GIR.GL_SL_LINK_ID , XAL.CODE_COMBINATION_ID FROM GL_JE_LINES GJL , GL_JE_HEADERS GJH , GL_CODE_COMBINATIONS_KFV GL_CODE_COMBINATIONS_K FV GCC , GL_JE_BATCHES GJB , GL_IMPORT_REFERENCES GIR , XLA_AE_LINES XAL , XLA_AE_HEADERS XAH , XLA_TRANSACTION_ENTITIES_UPG XLA_TRANSACTION_ENTITI ES_UPG UPG , GMF_XLA_EXTRACT_HEADERS GMF_XLA_EXTRACT_HEADER S GEH , GMF_INVOICE_DISTRIBUTIONS GMF_INVOICE_DISTRIBUTI ONS GID , AP_INVOICE_DISTRIBUTIONS_ALL AP_INVOICE_DISTRIBUTIO NS_ALL AID , AP_INVOICES_ALL AIA , ORG_ORGANIZATION_DEFINITIONS ORG_ORGANIZATION_DEFIN ITIONS OOD WHERE GJL.JE_HEADER_ID = GJH.JE_HEADER_ID AND GCC.CODE_COMBINATION_ID IN (SELECT DISTINCT CODE_COMBINATION_ID FROM GL_CO DE_COMBINATIONS_KFV WHERE SEGMENT1 = :P_COMPANY AND SEGMENT2 = NVL(:P_LINE_OF_BUS,SEG NVL(:P_LINE_OF_BUS,SEGMENT2 MENT2 )-- ADD BY JAYANTA 11-MAY-2012 AND SEGMENT3 = NVL(:P_LOCATION,SEGMEN NVL(:P_LOCATION,SEGMENT3) T3) AND SEGMENT4 = NVL(:P_COST_CENTER,GCC NVL(:P_COST_CENTER,GCC.SEGM .SEGM ENT4)--ADD BY JAYANTA 8-JUN-2012 AND SEGMENT5 = :P_NATURAL_ACCT AND SEGMENT6 = NVL(:P_SUB_ACCT,SEGMEN NVL(:P_SUB_ACCT,SEGMENT6) T6) AND SEGMENT7 = NVL(:P_SEGMENT1,GCC.SE NVL(:P_SEGMENT1,GCC.SEGMENT GMENT 7) --ADD BY JAYANTA 8-JUN-2012 AND SEGMENT8 = NVL(:P_SEGMENT2,GCC.SE NVL(:P_SEGMENT2,GCC.SEGMENT GMENT 8) --ADD BY JAYANTA 8-JUN-2012 --AND SEGMENT9 = NVL(:P_INTER_COMP,GCC.SE NVL(:P_INTER_COMP,GCC.SEG G MENT9) --ADD BY JAYANTA 8-JUN-2012 ) AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID AND GJH.JE_BATCH_ID = GJB.JE_BATCH_ID AND GJL.STATUS = 'P' AND GJH.ACTUAL_FLAG = 'A'
AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND ORDER
TRUNC(GJL.EFFECTIVE_DATE) BETWEEN :P_START_DATE AND :P_END_DATE TRUNC(GJL.EFFECTIVE_DATE) GJL.JE_HEADER_ID = GIR.JE_HEADER_ID GJH.JE_BATCH_ID = GIR.JE_BATCH_ID GJL.JE_LINE_NUM = GIR.JE_LINE_NUM GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID XAL.APPLICATION_ID = 555 XAL.AE_HEADER_ID = XAH.AE_HEADER_ID XAH.EVENT_TYPE_CODE = 'INVOICE_IPV_ADJ' XAH.ENTITY_ID = UPG.ENTITY_ID GEH.EVENT_TYPE_CODE = 'INVOICE_IPV_ADJ' UPG.TRANSACTION_NUMBER = TO_CHAR(GEH.TRANSACTION_ TO_CHAR(GEH.TRANSACTION_ID) ID) XAH.EVENT_ID = GEH.EVENT_ID GEH.SOURCE_DOCUMENT_ID = GID.DISTRIBUTION_ID GID.INVOICE_DISTRIBUTION_ID GID.INVOICE_DISTRIBUTION _ID = AID.INVOICE_DISTRIBUTI AID.INVOICE_DISTRIBUTION_ID ON_ID AID.INVOICE_ID = AIA.INVOICE_ID GID.ORGANIZATION_ID = OOD.ORGANIZATION_ID BY GL_DATE, TRANSACTION_NUMBER;
