/* Formatted on 2017/10/30 08:46 (Formatter Plus v4.8.8) */
SELECT driver_name, TO_DATE (issue_date, 'dd-mm-yyyy') issue_date,
invh_cust_code, customer_name, invoice_date, invoice_no, do_no,
terms, remarks, amount, annotation
FROM (SELECT invh_flex_03 driver_name, invh_flex_04 issue_date,
invh_cust_code, invh_cust_name customer_name,
invh_dt invoice_date,
invh_txn_code || '-' || invh_no invoice_no,
invh_ref_txn_code || '-' || invh_ref_no do_no,
invh_term_code terms, ' ' remarks, amount,
invh_annotation annotation
FROM ot_invoice_head a
JOIN
(SELECT invi_invh_sys_id, SUM (invi_fc_val) amount
FROM ot_invoice_item
GROUP BY invi_invh_sys_id) b
ON a.invh_sys_id = b.invi_invh_sys_id
WHERE invh_dt >= TO_DATE ('03-09-2013', 'DD-MM-YYYY')
AND invh_flex_03 LIKE :r_till_cust_code
AND NVL (invh_ship_city_code, ' ') LIKE :r_city
AND invh_flex_01 LIKE :r_from_cust_code
AND invh_term_code LIKE :r_terms
AND NVL (invh_flex_05, 'N') LIKE :r_status
AND REGEXP_LIKE (invh_flex_04, '^[[:digit:]]+$')
AND ( invh_txn_code LIKE 'DT%'
OR invh_txn_code LIKE 'DV%'
OR invh_txn_code LIKE '%SER'
))
WHERE TO_DATE (issue_date, 'DD-MM-YYYY')
BETWEEN TO_DATE (:r_from_cust_main_acnt_code, 'DD/MM/RRRR')
AND TO_DATE (:r_till_cust_main_acnt_code, 'DD/MM/RRRR')
ORDER BY driver_name, invoice_date, issue_date;
Our community of experts have been thoroughly vetted for their expertise and industry experience.
This award recognizes a member of Experts Exchange who has made outstanding contributions to the community within their first year as an expert. The Rookie of the Year is awarded to a new expert who has the highest number of quality contributions.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.