收款 借贷

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

 

转载于:https://www.cnblogs.com/benio/archive/2011/11/01/2231399.html