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