JA Cash Flow Statement Program - Cash Flow Detail

➜ Back to Library
Description
Categories: BI Publisher
Application: Asia/Pacific Localizations
Source: Cash Flow Statement Program - Cash Flow Detail Report
Short Name: JACNCFSD
DB package: JA_CN_CFS_REPORT_PKG 
      --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
JA_CN_SRS_RGXROWD 
 
 LOV Oracle
Report Name 
 
Id Flex Code 
 
RG_SRS_INSTALLATION 
 
RG_SRS_ADHOC_REPORT_PREFIX 
 
 LOV Oracle
JA_CN_COA_ID 
 
 LOV Oracle
Ledger Name 
 
P_LEGAL_ENTITY 
 
Legal Entity Id 
 
 Number
Balance Segment Value 
 
 LOV Oracle
Source 
 
 LOV Oracle
Period To 
 
 LOV Oracle
Period From 
 
 LOV Oracle
Row Description 
 
Row Name 
 
 LOV Oracle
Report 
 
 LOV Oracle
Ledger 
 
 LOV Oracle
Download
Related Blitz Reports
JA India - Open Transactions Report for Prior-GST (XML Publisher)
JA Third Party Detail Trial Balance with Contra Accounts
AR Open Balances Revaluation
JA India - Cash/Bank Book - draft
JA India - Creditors Ledger - draft
JA India - RG23C Part I Report - draft - draft
AP Invoice History - draft
AP Prepayment Balance Report - Functional Currency (CZech)
AP Advances in Selected Currency