[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT
i.invoice_num,
id.distribution_line_number,
i.invoice_id,
i.vendor_id,
i.invoice_type_lookup_code,
i.approval_status,
id.parent_invoice_id,
pl.po_header_id,
pl.po_line_id,
pd.line_location_id,
id.line_type_lookup_code,
msi.segment1,
pl.item_description,
id.quantity_invoiced,
pl.unit_meas_lookup_code,
id.amount,
id.base_amount,
msi.list_price_per_unit,
id.unit_price,
i.invoice_currency_code,
sob.currency_code,
id.exchange_rate,
i.invoice_date,
i.gl_date,
id.creation_date,
id.created_by,
id.last_update_date,
id.last_updated_by,
i.cancelled_date
FROM
ap_invoices_all i,
ap_invoice_distributions_all id,
po_distributions_all pd,
cpg_oragems_mapping map,
po_lines_all pl,
mtl_system_items msi,
gl_sets_of_books sob
WHERE i.invoice_num = invoicenum
AND i.invoice_id LIKE NVL ( invoiceid, i.invoice_id)
AND i.vendor_id LIKE NVL ( vendorid, i.vendor_id)
AND i.invoice_type_lookup_code = invoicetype
AND i.invoice_id = id.invoice_id
AND id.distribution_line_number LIKE
NVL ( invoicelineno, id.distribution_line_number)
AND id.po_distribution_id = pd.po_distribution_id
AND id.line_type_lookup_code LIKE
NVL( linetype, id.line_type_lookup_code)
AND i.set_of_books_id = sob.set_of_books_id
AND pl.po_line_id = pd.po_line_id
AND pl.item_id = msi.inventory_item_id
AND map.po_line_location_id = pd.line_location_id
AND map.po_line_id = pd.po_line_id
AND map.po_header_id = pd.po_header_id
AND id.last_update_date BETWEEN
NVL( startdate, id.last_update_date) AND
NVL( enddate, id.last_update_date);
SELECT DISTINCT
i.invoice_num,
id.distribution_line_number,
i.invoice_id,
i.vendor_id,
i.invoice_type_lookup_code,
i.approval_status,
id.parent_invoice_id,
pl.po_header_id,
pl.po_line_id,
pd.line_location_id,
id.line_type_lookup_code,
msi.segment1,
pl.item_description,
id.quantity_invoiced,
pl.unit_meas_lookup_code,
id.amount,
id.base_amount,
msi.list_price_per_unit,
id.unit_price,
i.invoice_currency_code,
sob.currency_code,
id.exchange_rate,
i.invoice_date,
i.gl_date,
id.creation_date,
id.created_by,
id.last_update_date,
id.last_updated_by,
i.cancelled_date
FROM
ap_invoices_all i,
ap_invoice_distributions_all id,
po_distributions_all pd,
cpg_oragems_mapping map,
po_lines_all pl,
mtl_system_items msi,
gl_sets_of_books sob
WHERE i.invoice_num LIKE NVL( invoicenum, i.invoice_num)
AND i.invoice_id LIKE NVL ( invoiceid, i.invoice_id)
AND i.vendor_id LIKE NVL ( vendorid, i.vendor_id)
AND i.invoice_type_lookup_code = invoicetype
AND i.invoice_id = id.invoice_id
AND id.distribution_line_number LIKE
NVL ( invoicelineno, id.distribution_line_number)
AND id.po_distribution_id = pd.po_distribution_id
AND id.line_type_lookup_code LIKE
NVL( linetype, id.line_type_lookup_code)
AND i.set_of_books_id = sob.set_of_books_id
AND pl.po_line_id = pd.po_line_id
AND pl.item_id = msi.inventory_item_id
AND map.po_line_location_id = pd.line_location_id
AND map.po_line_id = pd.po_line_id
AND map.po_header_id = pd.po_header_id
AND id.last_update_date BETWEEN
NVL( startdate, id.last_update_date) AND
NVL( enddate, id.last_update_date);
last_update_date out nocopy date,
last_updated_by out nocopy number ,
t_cancelled_date in out nocopy date,
t_match_status_flag in out nocopy varchar2,
t_hold_count in out nocopy number,
approval out nocopy varchar2,
statuscode out nocopy number,
rowtofetch in out nocopy number) IS
/* created_by number;*/
/* last_updated_by number;*/
SELECT um_code from sy_uoms_mst
WHERE unit_of_measure = t_unit_of_measure ;
last_update_date,
last_updated_by,
t_cancelled_date;
last_update_date,
last_updated_by,
t_cancelled_date;
SELECT invoice_num
INTO previous_invoice_num
FROM ap_invoices_all
WHERE invoice_id = tmp_invoice_id;
/* modified_by := pkg_gl_get_currencies.get_name (last_updated_by);*/
select count(*)
into t_hold_count
from ap_holds_all aph, ap_invoices_all api
where
api.invoice_num = invoicenum
and api.invoice_id = aph.invoice_id
and aph.release_lookup_code is null;
select count(*)
into t_hold_count
from ap_holds_all aph, ap_invoices_all api
where
api.invoice_id = aph.invoice_id
and aph.release_lookup_code is null;
select min(decode(match_status_flag, 'N', '1N', 'T', '2T',
'A', '3A', '4'))
into t_match_status_flag
from ap_invoice_distributions_all
where invoice_id = invoiceid;
select min(decode(match_status_flag, 'N', '1N', 'T', '2T',
'A', '3A', '4'))
into t_match_status_flag
from ap_invoice_distributions_all;
select distinct '1N' /* Need just one value ONLY (if it exist)!!! */
into t_match_status_flag2
from ap_invoice_distributions_all
where invoice_id = invoiceid
and match_status_flag is null
and exists (select 'There are tested and untested lines'
from ap_invoice_distributions_all
where invoice_id = invoiceid
and match_status_flag in ('T', 'A'));
select distinct '1N' Need just one value ONLY (if it exist)!!!
into t_match_status_flag2
from ap_invoice_distributions_all
where match_status_flag is null
and exists (select 'There are tested and untested lines'
from ap_invoice_distributions_all
where match_status_flag in ('T', 'A')
and rownum < 2);