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

    dates divide by number of records


      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



      trunc((apply_days)/cnt) aa







      --.party_name customer_name




      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