[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rsl.quantity_received,
rsl.unit_of_measure,
rsl.primary_unit_of_measure,
rct.transaction_id
FROM rcv_shipment_lines rsl,
po_headers poh,
po_lines pol,
rcv_transactions rct
WHERE rct.shipment_line_id = rsl.shipment_line_id
AND rct.transaction_type = 'RECEIVE'
AND rsl.po_header_id = poh.po_header_id
AND rsl.po_line_id = pol.po_line_id
AND poh.vendor_id = X_vendor_id
AND poh.vendor_site_id = X_vendor_site_id
AND rsl.to_organization_id = X_organization_id
AND poh.supply_agreement_flag = 'Y'
AND pol.item_id = X_item_id
AND rct.transaction_date between X_cum_period_start
and nvl(X_cum_period_end,rct.transaction_date+1)
/* Bug#3067808 Added the following retrictive coindition to the SQL so that
** the correct value for transaction_id is retrived from receiving tables
** only for which the ASL entries exists.
*/
AND EXISTS (select '1'
from po_asl_attributes_val_v paa,
po_asl_documents pad
WHERE paa.vendor_id = x_vendor_id
AND paa.vendor_site_id = x_vendor_site_id
AND paa.item_id = x_item_id
AND paa.using_organization_id =
(SELECT MAX(paa2.using_organization_id)
FROM po_asl_attributes_val_v paa2
WHERE decode(paa2.using_organization_id, -1,
x_organization_id,
paa2.using_organization_id) =
x_organization_id
AND paa2.vendor_id = x_vendor_id
AND paa2.vendor_site_id = x_vendor_site_id
AND paa2.item_id = x_item_id)
AND paa.asl_id = pad.asl_id
AND pad.document_header_id = poh.po_header_id);
SELECT rct.primary_quantity,
rct.transaction_id
FROM rcv_transactions rct
WHERE rct.transaction_type = 'RETURN TO VENDOR'
AND rct.parent_transaction_id = X_transaction_id;
SELECT rsl.quantity_received,
rsl.unit_of_measure,
rsl.primary_unit_of_measure
FROM rcv_shipment_lines rsl,
po_headers poh,
po_lines pol
WHERE pol.item_id = X_item_id
AND rsl.po_line_id = pol.po_line_id
AND pol.po_header_id = poh.po_header_id
AND poh.vendor_id = X_vendor_id
AND poh.vendor_site_id = X_vendor_site_id
AND rsl.to_organization_id = X_organization_id
AND poh.supply_agreement_flag = 'Y'
AND exists
(select 1
from rcv_transactions rct
where rct.transaction_date between x_cum_period_start
and nvl(x_cum_period_end,
rct.transaction_date+1)
and rct.shipment_line_id = rsl.shipment_line_id
and rct.transaction_type = 'RECEIVE')
/* Bug#3067808 Added the following retrictive coindition to the SQL so that
** the correct value for transaction_id is retrived from receiving tables
** only for which the ASL entries exists.
*/
AND EXISTS (select '1'
from po_asl_attributes_val_v paa,
po_asl_documents pad
WHERE paa.vendor_id = x_vendor_id
AND paa.vendor_site_id = x_vendor_site_id
AND paa.item_id = x_item_id
AND paa.using_organization_id =
(SELECT MAX(paa2.using_organization_id)
FROM po_asl_attributes_val_v paa2
WHERE decode(paa2.using_organization_id, -1,
x_organization_id,
paa2.using_organization_id) =
x_organization_id
AND paa2.vendor_id = x_vendor_id
AND paa2.vendor_site_id = x_vendor_site_id
AND paa2.item_id = x_item_id)
AND paa.asl_id = pad.asl_id
AND pad.document_header_id = poh.po_header_id);
SELECT uom_code
INTO X_transaction_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = X_unit_of_measure;
SELECT uom_code
INTO X_purchasing_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = X_purchasing_unit_of_measure;
SELECT uom_code
INTO X_primary_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = X_primary_unit_of_measure;
select sum(adjustment_quantity)
into x_adjustment_quantity
from chv_cum_adjustments cha,
chv_cum_periods ccp
where cha.organization_id = X_organization_id
and cha.vendor_id = X_vendor_id
and cha.vendor_site_id = X_vendor_site_id
and cha.item_id = X_item_id
and cha.cum_period_id = ccp.cum_period_id
and ccp.cum_period_start_date = X_cum_period_start
and ccp.cum_period_end_date >= X_cum_period_end
and ccp.organization_id = cha.organization_id;
SELECT primary_uom_code
INTO X_primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = X_item_id
AND organization_id = X_organization_id;
SELECT uom_code
INTO X_purchasing_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = X_purchasing_unit_of_measure;
SELECT uom_code
INTO X_transaction_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = X_unit_of_measure;
SELECT uom_code
INTO X_primary_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = X_primary_unit_of_measure;
SELECT uom_code
INTO X_purchasing_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = X_purchasing_unit_of_measure;
SELECT sum(rct.primary_quantity)
INTO X_corrtv_primary_quantity
FROM rcv_transactions rct
WHERE rct.transaction_type = 'CORRECT'
AND rct.parent_transaction_id = X_rtv_transaction_id;
select sum(adjustment_quantity)
into x_adjustment_quantity
from chv_cum_adjustments cha,
chv_cum_periods ccp
where cha.organization_id = X_organization_id
and cha.vendor_id = X_vendor_id
and cha.vendor_site_id = X_vendor_site_id
and cha.item_id = X_item_id
and cha.cum_period_id = ccp.cum_period_id
and ccp.cum_period_start_date = X_cum_period_start
and ccp.cum_period_end_date >= X_cum_period_end
and ccp.organization_id = cha.organization_id;
SELECT primary_uom_code
INTO X_primary_uom_code
FROM mtl_system_items
WHERE inventory_item_id = X_item_id
AND organization_id = X_organization_id;
SELECT uom_code
INTO X_purchasing_uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = X_purchasing_unit_of_measure;