Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Wednesday, 11 May 2016

Query to Get Open Transactions

SELECT hp.party_name,                                     --"Customer Name"
       ca.account_number,                                 --"Customer Number"
       ctta.name trx_type,                                --"Transaction Type"
       cta.trx_number,                                    --"Transaction Number"
       cta.trx_date,                                      --"Transaction Date"
       psa.due_date,                                      --"Payment Due Date"
       SUM(psa.amount_due_original) amount_due_original,  --"Original Amount Due"
       SUM(psa.amount_due_remaining) amount_due_remaining --"Remaining Amount Due"
  FROM hz_parties hp,
       hz_cust_accounts ca,
       ra_cust_trx_types_all ctta,
       ra_customer_trx_all cta,
       ar_payment_schedules_all psa
 WHERE psa.status                = 'OP'                   --Open status
       AND hp.party_id           = ca.party_id
       AND ca.cust_account_id    = cta.bill_to_customer_id
       AND cta.customer_trx_id   = psa.customer_trx_id
       AND ctta.cust_trx_type_id = cta.cust_trx_type_id
--       AND hp.party_name         LIKE 'A%'
--       AND cta.trx_number        = '60797'
 GROUP BY hp.party_name,
       ca.account_number,
       ctta.name,
       cta.trx_number,
       cta.trx_date,

       psa.due_date;
Share this article :

0 comments:

Post a Comment