收款 借贷

Post subject: AR Receipts accounting extract

SELECT (SELECT NAME
          FROM hr_operating_units
         WHERE organization_id = rc.org_id) OU_NAME,
       rc.trx_number invoice_number,
       cr.receipt_number,
       cr.receipt_date,
       (SELECT NAME
          FROM apps.RA_CUST_TRX_TYPES_ALL
         WHERE CUST_TRX_TYPE_ID = rc.CUST_TRX_TYPE_ID
           AND rc.org_id = org_id) TRX_TYPE,
       ACCOUNT_CLASS,
       line_type,
       (SELECT vat.tax_code
          FROM apps.RA_CUSTOMER_TRX_LINES_all CTL_INV_LINE,
               apps.RA_CUSTOMER_TRX_LINES_all CTL_INV_TAX,
               apps.AR_VAT_TAX_all            INV_VAT,
               apps.RA_CUSTOMER_TRX_LINES_all CTL_LINE,
               apps.RA_CUSTOMER_TRX_LINES_all CTL_TAX,
               apps.AR_VAT_TAX_all            VAT
         WHERE CTL_TAX.LINK_TO_CUST_TRX_LINE_ID = CTL_LINE.CUSTOMER_TRX_LINE_ID
           AND CTL_TAX.LINE_TYPE = 'TAX'
           AND CTL_TAX.VAT_TAX_ID = VAT.VAT_TAX_ID(+)
           AND CTL_TAX.PREVIOUS_CUSTOMER_TRX_LINE_ID =
               CTL_INV_TAX.CUSTOMER_TRX_LINE_ID(+)
           AND CTL_INV_TAX.LINK_TO_CUST_TRX_LINE_ID =
               CTL_INV_LINE.CUSTOMER_TRX_LINE_ID(+)
           AND CTL_INV_TAX.VAT_TAX_ID = INV_VAT.VAT_TAX_ID(+)
              --and ctl_tax.link_to_cust_trx_line_id 
           AND ctl_tax.customer_trx_line_id = ctl.customer_trx_line_id
           AND ctl_tax.link_to_cust_trx_line_id = ctl.link_to_cust_trx_line_id --2011804
        ) tax_code,
       TO_NUMBER(DECODE(ctlgd.account_class,
                        'REC',
                        DECODE(SIGN(NVL(ctlgd.amount, 0)),
                               -1,
                               NULL,
                               NVL(ctlgd.acctd_amount, 0)),
                        DECODE(SIGN(NVL(ctlgd.amount, 0)),
                               -1,
                               -NVL(ctlgd.acctd_amount, 0),
                               NULL))) INV_ACCOUNTED_DR,
       TO_NUMBER(DECODE(ctlgd.account_class,
                        'REC',
                        DECODE(SIGN(NVL(ctlgd.amount, 0)),
                               -1,
                               -NVL(ctlgd.acctd_amount, 0),
                               NULL),
                        DECODE(SIGN(NVL(ctlgd.amount, 0)),
                               -1,
                               NULL,
                               NVL(ctlgd.acctd_amount, 0)))) INV_ACCOUNTED_CR,
       (SELECT segment1 || '-' || segment2 || '-' || segment3 || '-' || segment4 || '-' ||
               segment5 || '-' || segment6 || '-' || segment7 || '-' || segment8
          FROM gl_code_combinations
         WHERE code_combination_id = ctlgd.code_combination_id) inv_account
       --,APP.RECEIVABLE_APPLICATION_ID
      ,
       (SELECT segment1 || '-' || segment2 || '-' || segment3 || '-' || segment4 || '-' ||
               segment5 || '-' || segment6 || '-' || segment7 || '-' || segment8
          FROM gl_code_combinations
         WHERE code_combination_id = aev.code_combination_id) Receipt_account,
       aev.acct_line_type_name,
       aev.ENTERED_DR,
       aev.ENTERED_CR,
       aev.ACCOUNTED_DR,
       aev.ACCOUNTED_CR
--,aev.applied_date
  FROM apps.ra_customer_trx_all rc,
       apps.AR_PAYMENT_SCHEDULES_all ps,
       apps.AR_CASH_RECEIPTS_all CR,
       apps.ra_cust_trx_line_gl_dist_all CTLGD,
       apps.ra_customer_trx_lines_all CTL,
       apps.AR_AEL_SL_REC_V AEV,
       (SELECT *
          FROM apps.AR_RECEIVABLE_APPLICATIONS_all APP --WHERE app.display = 'Y'
        ) APP
 WHERE 1 = 1
   AND Rc.customer_trx_id = CTLGD.customer_trx_id
   AND NVL(Rc.org_id, -99) = NVL(CTLGD.org_id, -99)
   AND CTLGD.account_set_flag = 'N'
   AND CTLGD.customer_trx_line_id = CTL.customer_trx_line_id(+)
   AND NVL(CTLGD.org_id, -99) = NVL(CTL.org_id(+), -99)
   AND APP.CASH_RECEIPT_ID = CR.CASH_RECEIPT_ID(+)
   AND APP.APPLIED_CUSTOMER_TRX_ID = rc.CUSTOMER_TRX_ID(+)
   AND APP.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
      --AND trunc(cr.creation_date) >= to_date(NVL(:p_date,'07-SEP-2009'),'DD-MON-YYYY')
   AND trunc(cr.creation_date) >= to_date('07-SEP-2009', 'DD-MON-YYYY')
   AND APP.RECEIVABLE_APPLICATION_ID = AEV.source_id
--and receipt_number = 'IBS-38166'
--order by 1,2
UNION
SELECT (SELECT NAME
          FROM hr_operating_units
         WHERE organization_id = cr.org_id) OU_NAME,
       NULL invoice_number,
       cr.receipt_number,
       cr.receipt_date,
       NULL TRX_TYPE,
       NULL ACCOUNT_CLASS,
       NULL line_type,
       NULL tax_code,
       NULL INV_ACCOUNTED_DR,
       NULL INV_ACCOUNTED_CR,
       NULL inv_account
       --,APP.RECEIVABLE_APPLICATION_ID
      ,
       (SELECT segment1 || '-' || segment2 || '-' || segment3 || '-' || segment4 || '-' ||
               segment5 || '-' || segment6 || '-' || segment7 || '-' || segment8
          FROM gl_code_combinations
         WHERE code_combination_id = aev.code_combination_id) Receipt_account,
       aev.acct_line_type_name,
       aev.ENTERED_DR,
       aev.ENTERED_CR,
       aev.ACCOUNTED_DR,
       aev.ACCOUNTED_CR
--,aev.applied_date
  FROM apps.AR_CASH_RECEIPTS_all CR,
       apps.AR_AEL_SL_REC_V      AEV
--, (SELECT * FROM apps.AR_RECEIVABLE_APPLICATIONS_all APP --WHERE app.display = 'Y'
--) APP 
 WHERE 1 = 1
   AND trunc(cr.creation_date) >= to_date('07-SEP-2009', 'DD-MON-YYYY')
      --AND APP.RECEIVABLE_APPLICATION_ID = AEV.source_id
      --AND app.status <> 'APP'
   AND cr.receipt_number = AEV.trx_number_c
--and receipt_number = 'IBS-38166'
 ORDER BY 1,
          3