/* 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;
AND invh_term_code LIKE ':r_terms'
AND NVL (invh_flex_05, 'N') LIKE ':r_status'
Open in new window