[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select max(rct.transaction_id)
into x_last_receipt_id
from rcv_transactions rct,
rcv_shipment_lines rsl,
po_headers poh
where rct.shipment_line_id = rsl.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and rct.transaction_date between
to_date(p_cum_period_start_date) and
to_date(p_cum_period_end_date)
and rsl.to_organization_id = p_org_id
and rsl.item_id = p_item_id
and rsl.po_header_id = poh.po_header_id
and poh.vendor_id = p_vendor_id
and poh.vendor_site_id = p_vendor_site_id
and poh.supply_agreement_flag = 'Y'
and rct.transaction_date in
(select max(rct2.transaction_date)
from rcv_transactions rct2,
rcv_shipment_lines rsl2,
po_headers poh2
where rct2.shipment_line_id = rsl2.shipment_line_id
and rct2.transaction_type = 'RECEIVE'
and rct2.transaction_date between
to_date(p_cum_period_start_date) and
to_date(p_cum_period_end_date)
and rsl2.to_organization_id = p_org_id
and rsl2.item_id = p_item_id
and rsl2.po_header_id = poh2.po_header_id
and poh2.vendor_id = p_vendor_id
and poh2.vendor_site_id = p_vendor_site_id
and poh2.supply_agreement_flag = 'Y');
select receipt_num
into x_last_receipt_num
from rcv_transactions rct,
rcv_shipment_headers rsh
where rct.transaction_id = x_last_receipt_id
and rct.shipment_header_id = rsh.shipment_header_id;
select max(rct.transaction_id)
into x_last_receipt_id
from rcv_transactions rct,
rcv_shipment_lines rsl,
po_headers poh
where rct.shipment_line_id = rsl.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and rct.transaction_date between
to_date(p_cum_period_start_date) and
to_date(p_cum_period_end_date)
and rsl.to_organization_id = p_org_id
and rsl.item_id = p_item_id
and rsl.po_header_id = poh.po_header_id
and poh.vendor_id = p_vendor_id
and poh.vendor_site_id = p_vendor_site_id
and poh.supply_agreement_flag = 'Y'
and rct.transaction_date in
(select max(rct2.transaction_date)
from rcv_transactions rct2,
rcv_shipment_lines rsl2,
po_headers poh2
where rct2.shipment_line_id = rsl2.shipment_line_id
and rct2.transaction_type = 'RECEIVE'
and rct2.transaction_date between
to_date(p_cum_period_start_date) and
to_date(p_cum_period_end_date)
and rsl2.to_organization_id = p_org_id
and rsl2.item_id = p_item_id
and rsl2.po_header_id = poh.po_header_id
and poh2.vendor_id = p_vendor_id
and poh2.vendor_site_id = p_vendor_site_id
and poh2.supply_agreement_flag = 'Y');
select transaction_date
into x_last_receipt_date
from rcv_transactions rct
where rct.transaction_id = x_last_receipt_id;
select max(rct.transaction_id)
into x_last_receipt_id
from rcv_transactions rct,
rcv_shipment_lines rsl,
po_headers poh
where rct.shipment_line_id = rsl.shipment_line_id
and rct.transaction_type = 'RECEIVE'
and rct.transaction_date between
to_date(p_cum_period_start_date) and
to_date(p_cum_period_end_date)
and rsl.to_organization_id = p_org_id
and rsl.item_id = p_item_id
and rsl.po_header_id = poh.po_header_id
and poh.vendor_id = p_vendor_id
and poh.vendor_site_id = p_vendor_site_id
and poh.supply_agreement_flag = 'Y'
and rct.transaction_date in
(select max(rct2.transaction_date)
from rcv_transactions rct2,
rcv_shipment_lines rsl2,
po_headers poh2
where rct2.shipment_line_id = rsl2.shipment_line_id
and rct2.transaction_type = 'RECEIVE'
and rct2.transaction_date between
to_date(p_cum_period_start_date) and
to_date(p_cum_period_end_date)
and rsl2.to_organization_id = p_org_id
and rsl2.item_id = p_item_id
and rsl2.po_header_id = poh2.po_header_id
and poh2.vendor_id = p_vendor_id
and poh2.vendor_site_id = p_vendor_site_id
and poh2.supply_agreement_flag = 'Y');
select primary_quantity,
primary_unit_of_measure
into x_primary_quantity,
x_primary_unit_of_measure
from rcv_transactions rct
where rct.transaction_id = x_last_receipt_id;
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 = p_purchasing_uom;
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 X_cum_period_end;
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 x_cum_period_end
and rct.shipment_line_id = rsl.shipment_line_id
and rct.transaction_type = 'RECEIVE');
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 paa.purchasing_unit_of_measure
INTO x_purchasing_unit_of_measure
FROM po_asl_attributes_val_v paa
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) ;
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;