0 Replies Latest reply on Sep 25, 2019 11:22 AM by user615979

    dates divide by number of records

    user615979

      Hi All,

       

        Working in R12 Version 12.1.3

       

      working in alert

       

      please see below query

       

      i have two dates and count number of records for a particular customer and divide by two dates to get average.

      the below query is working fine in toad but same when i apply in alert manager it is showing error inconsistent data types

       

      please i need help

       

      select

      trunc((apply_days)/cnt) aa

      --into

      --&aa

      from

      (

      select

      --party

      --.party_name customer_name

      --,app.apply_date

      --,ps_inv.trx_number

      --,ct.trx_date

      count(ps_inv.trx_number) cnt

      ,to_number(sum((to_date(app.apply_date,'DD-MM-YYYY' )) - TO_NUMBER(TO_DATE(ct.trx_date,'DD-MM-YYYY')))) apply_days

      from ar_receivable_applications_all app

      ,ar_cash_receipts_all cr

      ,ar_payment_schedules_all ps_inv

      ,hz_cust_accounts cust

      ,hz_parties party

      ,ra_customer_trx_all ct

      where app.display = 'Y'

      and app.cash_receipt_id = cr.cash_receipt_id

      and app.applied_payment_schedule_id = ps_inv.payment_schedule_id

      and cust.cust_account_id(+) = ps_inv.customer_id

      and cust.party_id = party.party_id(+)

      and ct.customer_trx_id(+) = ps_inv.customer_trx_id

      and cust.cust_account_id = ct.bill_to_customer_id

      and trunc (app.apply_date) <= :pdate and trunc (app.apply_date) >= :pdate-90 -- for last 3 months

      and cust_account_id = 10001

       

      )