JA Cash Flow Statement Program - Cash Flow Detail
Description
Categories: BI Publisher
Imported from BI Publisher
Description: Cash Flow Statement Detail Report
Application: Asia/Pacific Localizations
Source: Cash Flow Statement Program - Cash Flow Detail Report
Short Name: JACNCFSD
DB package: JA_CN_CFS_REPORT_PKG
Description: Cash Flow Statement Detail Report
Application: Asia/Pacific Localizations
Source: Cash Flow Statement Program - Cash Flow Detail Report
Short Name: JACNCFSD
DB package: JA_CN_CFS_REPORT_PKG
Run
JA Cash Flow Statement Program - Cash Flow Detail and other Oracle EBS reports with Blitz Report™ on our demo environment
--Fix bug# 14664279 added by Jar.Wang 28/Sep/2012 --Fix bug# 10188478 added by riqi 13-Oct-2010 --BEGIN--- SELECT JCA.DETAILED_CFS_ITEM AS DETAILED_CFS_ITEM ,JCA.SOURCE AS SOURCE ,JCA.THIRD_PARTY_NAME AS THIRD_PARTY_NAME ,NULL AS RECEIPT_PAYMENT_NUMBER ,JCA.REFERENCE_NUMBER AS REFERENCE_NUMBER_ ,JCA.TRX_NUMBER AS TRANSACTION_NUMBER ,JCA.FUNC_AMOUNT AS TRANSACTION_AMOUNT ,JCA.BALANCING_SEGMENT AS BSV ,JCA.DESCRIPTION FROM JA_CN_CFS_ACTIVITIES_ALL JCA ,JA_CN_CFS_ASSIGNMENTS_ALL JCCA ,RG_REPORTS RG ,RG_REPORT_AXES RRA WHERE JCA.LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID AND JCA.PERIOD_NAME = :PERIOD_NAME --**** AND JCA.FUNC_AMOUNT <> 0 AND NOT (JCA.TRANSACTION_TYPE = 'SLA' AND JCA.SOURCE IN ('SQLAP', 'AR')) AND JCA.SOURCE = NVL(:P_SOURCE, JCA.SOURCE) AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM AND JCCA.CHART_OF_ACCOUNTS_ID = :P_CHART_OF_ACCOUNTS_ID AND JCCA.AXIS_SET_ID = RRA.AXIS_SET_ID AND JCCA.AXIS_SEQ = RRA.AXIS_SEQ AND RG.ROW_SET_ID = RRA.AXIS_SET_ID AND RG.REPORT_ID = NVL(:P_REPORT_ID, RG.REPORT_ID) AND JCA.LEDGER_ID = :P_LEDGER_ID AND RRA.AXIS_SEQ = :ROW_ID --**** AND ((:P_BSV IS NOT NULL AND JCA.BALANCING_SEGMENT = :P_BSV) OR (:P_BSV IS NULL AND JCA.BALANCING_SEGMENT IN (SELECT BAL_SEG_VALUE FROM JA_CN_LEDGER_LE_BSV_GT WHERE LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID AND LEDGER_ID = :P_LEDGER_ID))) UNION ALL --Fix bug# 10188478 added by riqi 13-Oct-2010 --END--- SELECT JCA.DETAILED_CFS_ITEM AS DETAILED_CFS_ITEM ,JCA.SOURCE AS SOURCE ,PV.VENDOR_NAME AS THIRD_PARTY_NAME ,TO_CHAR(ACA.CHECK_NUMBER) AS RECEIPT_PAYMENT_NUMBER ,JCA.REFERENCE_NUMBER AS REFERENCE_NUMBER_ ,NVL(API.INVOICE_NUM, '') AS TRANSACTION_NUMBER ,DECODE((SELECT XAH.EVENT_TYPE_CODE FROM XLA_AE_HEADERS XAH WHERE XAH.AE_HEADER_ID = JCA.CASH_TRX_ID AND ROWNUM = 1 ), 'PAYMENT UNCLEARED', DECODE(NVL(XDL.UNROUNDED_ACCOUNTED_DR, '-1'), '-1', -1 * XDL.UNROUNDED_ACCOUNTED_CR, XDL.UNROUNDED_ACCOUNTED_DR), DECODE(NVL(XDL.UNROUNDED_ACCOUNTED_DR, '-1'), '-1', XDL.UNROUNDED_ACCOUNTED_CR, -1 * XDL.UNROUNDED_ACCOUNTED_DR) ) AS TRANSACTION_AMOUNT ,JCA.BALANCING_SEGMENT AS BSV ,JCA.DESCRIPTION FROM JA_CN_CFS_ACTIVITIES_ALL JCA ,JA_CN_CFS_ASSIGNMENTS_ALL JCCA ,RG_REPORTS RG ,XLA_TRANSACTION_ENTITIES ENT ,AP_CHECKS_ALL ACA ,PO_VENDORS PV ,XLA_AE_HEADERS AEH ,XLA_DISTRIBUTION_LINKS XDL ,AP_INVOICES_ALL API WHERE JCA.LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID AND JCA.LEDGER_ID = :P_LEDGER_ID AND JCA.FUNC_AMOUNT <> 0 AND JCA.TRANSACTION_TYPE = 'SLA' AND JCA.SOURCE = 'SQLAP' AND JCA.SOURCE = NVL(:P_SOURCE, JCA.SOURCE) AND JCA.PERIOD_NAME = :PERIOD_NAME --**** AND ((:P_BSV IS NOT NULL AND JCA.BALANCING_SEGMENT = :P_BSV) OR (:P_BSV IS NULL AND JCA.BALANCING_SEGMENT IN (SELECT BAL_SEG_VALUE FROM JA_CN_LEDGER_LE_BSV_GT WHERE LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID AND LEDGER_ID = :P_LEDGER_ID))) AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM AND JCCA.CHART_OF_ACCOUNTS_ID = :P_CHART_OF_ACCOUNTS_ID AND JCCA.AXIS_SEQ = :ROW_ID --**** AND RG.ROW_SET_ID = JCCA.AXIS_SET_ID AND RG.REPORT_ID = NVL(:P_REPORT_ID, RG.REPORT_ID) AND ENT.APPLICATION_ID = 200 AND ENT.ENTITY_CODE = 'AP_PAYMENTS' AND ACA.CHECK_ID = ENT.SOURCE_ID_INT_1 AND PV.VENDOR_ID(+) = ACA.VENDOR_ID AND AEH.ENTITY_ID = ENT.ENTITY_ID AND AEH.AE_HEADER_ID = JCA.TRX_ID AND AEH.LEDGER_ID = :P_LEDGER_ID AND XDL.AE_HEADER_ID = JCA.TRX_ID AND XDL.AE_LINE_NUM = JCA.TRX_LINE_ID AND API.INVOICE_ID(+) = XDL.APPLIED_TO_SOURCE_ID_NUM_1 UNION ALL SELECT JCA.DETAILED_CFS_ITEM AS DETAILED_CFS_ITEM ,JCA.SOURCE AS SOURCE ,HP.PARTY_NAME AS THIRD_PARTY_NAME ,ACA.RECEIPT_NUMBER AS RECEIPT_PAYMENT_NUMBER ,JCA.REFERENCE_NUMBER AS REFERENCE_NUMBER_ ,NVL(RA.TRX_NUMBER, '') AS TRANSACTION_NUMBER ,DECODE((SELECT XAH.EVENT_TYPE_CODE FROM XLA_AE_HEADERS XAH WHERE XAH.AE_HEADER_ID = JCA.CASH_TRX_ID AND ROWNUM = 1 ), 'RECP_UPDATE', DECODE( --modified by Jixun for bug#16698143 begin /*(SELECT ACH.STATUS FROM AR_CASH_RECEIPT_HISTORY_ALL ACH, XLA_AE_HEADERS AH WHERE AH.AE_HEADER_ID = JCA.CASH_TRX_ID AND AH.EVENT_ID = ACH.EVENT_ID AND ACH.CURRENT_RECORD_FLAG = 'Y' --Fix bug#11777168 added by minwu 08-Mar-2011 AND ROWNUM = 1),*/ DECODE((SELECT COUNT(ACH.CASH_RECEIPT_HISTORY_ID) FROM AR_CASH_RECEIPT_HISTORY_ALL ACH, XLA_AE_HEADERS AH WHERE AH.AE_HEADER_ID = JCA.CASH_TRX_ID AND AH.EVENT_ID = ACH.EVENT_ID), 1, (SELECT ACH.STATUS FROM AR_CASH_RECEIPT_HISTORY_ALL ACH, XLA_AE_HEADERS AH WHERE AH.AE_HEADER_ID = JCA.CASH_TRX_ID AND AH.EVENT_ID = ACH.EVENT_ID), (SELECT ACH.STATUS FROM AR_CASH_RECEIPT_HISTORY_ALL ACH, XLA_AE_HEADERS AH WHERE AH.AE_HEADER_ID = JCA.CASH_TRX_ID AND AH.EVENT_ID = ACH.EVENT_ID AND ACH.CURRENT_RECORD_FLAG = 'Y')), --modified by Jixun for bug#16698143 end 'REMITTED', DECODE(NVL(XDL.UNROUNDED_ACCOUNTED_DR, '-1'), '-1', -1 * XDL.UNROUNDED_ACCOUNTED_CR, XDL.UNROUNDED_ACCOUNTED_DR), DECODE(NVL(XDL.UNROUNDED_ACCOUNTED_DR, '-1'), '-1', XDL.UNROUNDED_ACCOUNTED_CR, -1 * XDL.UNROUNDED_ACCOUNTED_DR) ), DECODE(NVL(XDL.UNROUNDED_ACCOUNTED_DR, '-1'), '-1', XDL.UNROUNDED_ACCOUNTED_CR, -1 * XDL.UNROUNDED_ACCOUNTED_DR) ) AS TRANSACTION_AMOUNT ,JCA.BALANCING_SEGMENT AS BSV ,JCA.DESCRIPTION FROM JA_CN_CFS_ACTIVITIES_ALL JCA ,JA_CN_CFS_ASSIGNMENTS_ALL JCCA ,RG_REPORTS RG ,XLA_TRANSACTION_ENTITIES ENT ,XLA_AE_HEADERS AEH ,AR_CASH_RECEIPTS_ALL ACA ,HZ_CUST_ACCOUNTS CUST ,HZ_PARTIES HP ,XLA_DISTRIBUTION_LINKS XDL ,RA_CUSTOMER_TRX_ALL RA WHERE JCA.LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID AND JCA.LEDGER_ID = :P_LEDGER_ID AND JCA.FUNC_AMOUNT <> 0 AND JCA.TRANSACTION_TYPE = 'SLA' AND JCA.SOURCE = 'AR' AND JCA.SOURCE = NVL(:P_SOURCE, JCA.SOURCE) AND JCA.PERIOD_NAME = :PERIOD_NAME --**** AND ((:P_BSV IS NOT NULL AND JCA.BALANCING_SEGMENT = :P_BSV) OR (:P_BSV IS NULL AND JCA.BALANCING_SEGMENT IN (SELECT BAL_SEG_VALUE FROM JA_CN_LEDGER_LE_BSV_GT WHERE LEGAL_ENTITY_ID = :P_LEGAL_ENTITY_ID AND LEDGER_ID = :P_LEDGER_ID))) AND JCCA.DETAILED_CFS_ITEM = JCA.DETAILED_CFS_ITEM AND JCCA.CHART_OF_ACCOUNTS_ID = :P_CHART_OF_ACCOUNTS_ID AND JCCA.AXIS_SEQ = :ROW_ID --**** AND RG.ROW_SET_ID = JCCA.AXIS_SET_ID AND RG.REPORT_ID = NVL(:P_REPORT_ID, RG.REPORT_ID) AND ENT.APPLICATION_ID = 222 AND ENT.ENTITY_CODE = 'RECEIPTS' AND ENT.ENTITY_ID = AEH.ENTITY_ID AND AEH.AE_HEADER_ID = JCA.TRX_ID AND AEH.LEDGER_ID = :P_LEDGER_ID AND ACA.CASH_RECEIPT_ID = ENT.SOURCE_ID_INT_1 AND ACA.PAY_FROM_CUSTOMER = CUST.CUST_ACCOUNT_ID(+) AND CUST.PARTY_ID = HP.PARTY_ID(+) AND XDL.AE_HEADER_ID = JCA.TRX_ID AND XDL.AE_LINE_NUM = JCA.TRX_LINE_ID AND RA.CUSTOMER_TRX_ID(+) = XDL.APPLIED_TO_SOURCE_ID_NUM_1 ORDER BY 2, 3, 5 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Ledger |
|
LOV Oracle | |
Report |
|
LOV Oracle | |
Row Name |
|
LOV Oracle | |
Row Description |
|
Char | |
Period From |
|
LOV Oracle | |
Period To |
|
LOV Oracle | |
Source |
|
LOV Oracle | |
Balance Segment Value |
|
LOV Oracle |