adding dynamic where/and condition to the cursor
672589Oct 21 2009 — edited Oct 21 2009Hi I have a cursor as below
SELECT ihev.invoice_id, MAX (ihev.custname) custname, COUNT (*)
invlncnt,
NVL (SUM (i.itemtotalservices), 0) invamt,
MAX (i.itemcurrency) invcur
FROM invoiceitems i,invoice_header_ebs_v ihev
,invoicetracking it
WHERE ihev.invoicestartdt = '01-FEB-2009'
AND ihev.reporting_region = 'N. America'
AND ihev.invoice_type ! = 'N/A'
AND i.invoice_id = ihev.invoice_id
AND ihev.invoice_id = it.invoice_id
AND it.arloaded = 0
AND ihev.ready_to_print_flg = 1
AND i.status = 1
AND 1 = (SELECT COUNT (DISTINCT i2.itemcurrency)
FROM invoiceitems i2
WHERE i2.invoice_id = ihev.invoice_id AND i2.status = 1)
GROUP BY ihev.invoice_id
HAVING SUM (i.itemtotalservices) > 0
ORDER BY 1
this is used in an interface code.We are using the same code for 2 different programs 1 for loading invoices into stage table and 2 for loading invoices into stage table and publish the details as a report.
the requirement is when we want to use for step 1 it should have the where clause like
AND it.arloaded = 0(posiible values are 0 and 1)
AND ihev.ready_to_print_flg = 1(posiible values are 0 and 1)
when we use for report then it shuld have the where clause as
--AND it.arloaded = 0 this condition not to be included
AND ihev.ready_to_print_flg = 1 this condition not to be included
is there any way to achieve the requirement.
Thanks,
Raghu