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

Pages

Wednesday, 11 May 2016

Query to Get Drilled-down 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"
       ctla.line_number,                                          --"Payment Due Date"
       ctla.description,                                          --"Description"
       ctla.uom_code,                                             --"UOM"
       ctla.quantity_invoiced,                                    --"Quantity"
       ctla.unit_selling_price,                                   --"Unit Price"
       (ctla.quantity_invoiced * ctla.unit_selling_price) amount  --"Amount"
  FROM hz_parties hp,
       hz_cust_accounts ca,
       ra_cust_trx_types_all ctta,
       ra_customer_trx_all cta,
       ra_customer_trx_lines_all ctla
 WHERE hp.party_id               = ca.party_id
       AND ca.cust_account_id    = cta.bill_to_customer_id
       AND ctta.cust_trx_type_id = cta.cust_trx_type_id
       AND cta.customer_trx_id   = ctla.customer_trx_id
--       AND hp.party_name         LIKE 'S%'
       --AND cta.trx_number        = '55443'

 ORDER BY ctla.line_number;
Share this article :

0 comments:

Post a Comment