[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_rtv_update_cum_flag VARCHAR2(1) := '';
SELECT /*+ FIRST_ROWS */ transaction_date
FROM rcv_transactions rct2,
rcv_shipment_lines rsl2
WHERE rct2.transaction_type = 'RECEIVE'
AND rct2.transaction_date between
x_cum_period_start_date - 1
and
nvl(x_cum_period_end_date,rct2.transaction_date+1) + 1
AND rsl2.item_id = x_item_id
AND rct2.vendor_id = x_vendor_id
AND rct2.vendor_site_id = x_vendor_site_id
AND rct2.organization_id = x_organization_id
AND rct2.shipment_line_id = rsl2.shipment_line_id
ORDER BY transaction_date desc;
SELECT transaction_id
FROM rcv_transactions rct,
rcv_shipment_lines rsl,
po_headers poh
WHERE transaction_date = x_max_trans_date
AND rct.transaction_type = 'RECEIVE'
AND rct.transaction_date between x_cum_period_start_date - 1
and nvl(x_cum_period_end_date,rct.transaction_date+1) + 1
AND rsl.item_id = x_item_id
AND rct.vendor_id = x_vendor_id
AND poh.vendor_site_id = x_vendor_site_id
AND rct.organization_id = x_organization_id
AND poh.po_header_id = rct.po_header_id
AND rct.shipment_line_id = rsl.shipment_line_id
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)
ORDER BY transaction_id DESC;
SELECT cum_period_id,
cum_period_start_date,
cum_period_end_date
INTO x_cum_period_id,
x_cum_period_start_date,
x_cum_period_end_date
FROM chv_cum_periods
WHERE organization_id = x_organization_id
AND x_horizon_start_date BETWEEN cum_period_start_date
AND nvl(cum_period_end_date,x_horizon_start_date+1);
SELECT rtv_update_cum_flag
INTO x_rtv_update_cum_flag
FROM chv_org_options
WHERE organization_id = x_organization_id;
SELECT max(transaction_date)
INTO x_max_trans_date
FROM rcv_transactions rct2,
po_headers poh2,
rcv_shipment_lines rsl2
WHERE rct2.transaction_type = 'RECEIVE'
AND rct2.transaction_date between
x_cum_period_start_date - 1
and
nvl(x_cum_period_end_date,rct2.transaction_date+1) + 1
AND rsl2.item_id = x_item_id
AND poh2.vendor_id = x_vendor_id
AND poh2.vendor_site_id = x_vendor_site_id
AND rct2.organization_id = x_organization_id
AND poh2.po_header_id = rct2.po_header_id
AND rct2.shipment_line_id = rsl2.shipment_line_id;*/
/* SELECT max(transaction_id)
INTO x_last_receipt_transaction_id
FROM rcv_transactions rct,
rcv_shipment_lines rsl,
po_headers poh
WHERE transaction_date = x_max_trans_date
(
SELECT max(transaction_date)
FROM rcv_transactions rct2,
po_headers poh2,
rcv_shipment_lines rsl2
WHERE rct2.transaction_type = 'RECEIVE'
AND rct2.transaction_date between
x_cum_period_start_date - 1
and
nvl(x_cum_period_end_date,rct2.transaction_date+1) + 1
AND rsl2.item_id = x_item_id
AND rct2.vendor_id = x_vendor_id
AND poh2.vendor_site_id = x_vendor_site_id
AND rct2.organization_id = x_organization_id
AND poh2.po_header_id = rct2.po_header_id
AND rct2.shipment_line_id = rsl2.shipment_line_id)
AND rct.transaction_type = 'RECEIVE'
AND rct.transaction_date between x_cum_period_start_date - 1
and nvl(x_cum_period_end_date,rct.transaction_date+1) + 1
AND rsl.item_id = x_item_id
AND rct.vendor_id = x_vendor_id
AND poh.vendor_site_id = x_vendor_site_id
AND rct.organization_id = x_organization_id
AND poh.po_header_id = rct.po_header_id
AND rct.shipment_line_id = rsl.shipment_line_id
Bug#3067808 Added the following retrictive condition 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);*/
x_rtv_update_cum_flag,
x_cum_period_start_date,
x_cum_period_end_date,
x_purchasing_unit_of_measure,
x_cum_quantity_received_prim,
x_cum_quantity_received);
SELECT count(*)
INTO x_number_records_cpi
FROM chv_cum_period_items cpi
WHERE cpi.cum_period_id = x_cum_period_id
AND cpi.vendor_id = x_vendor_id
AND cpi.vendor_site_id = x_vendor_site_id
AND cpi.organization_id = x_organization_id
AND cpi.item_id = x_item_id;
INSERT INTO chv_cum_period_items (cum_period_item_id,
cum_period_id,
organization_id,
vendor_id,
vendor_site_id,
item_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES (chv_cum_period_items_s.NEXTVAL,
x_cum_period_id,
x_organization_id,
x_vendor_id,
x_vendor_site_id,
x_item_id,
SYSDATE,
x_user_id,
SYSDATE,
x_user_id,
x_login_id);