[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_last_updated_by NUMBER;
the select to nvl(firm_date, new_dock_date) and also
the where cond. to nvl(firm_date, new_dock_date)
between x_horizon_start_date and x_horizon_end_date
*/
CURSOR C1 is select transaction_id,
nvl(firm_date,new_dock_date),
NVL(firm_quantity, new_order_quantity) -
(NVL(quantity_in_process,0) + nvl(implemented_quantity,0) )
from mrp_recommendations mrp
where mrp.organization_id = x_organization_id
and mrp.source_vendor_id = x_vendor_id
and x_vendor_site_id =
(select distinct pvs.vendor_site_id
from po_vendor_sites_all pvsa,
po_vendor_sites pvs
where pvsa.vendor_site_id = mrp.source_vendor_site_id and
pvsa.vendor_id = x_vendor_id and
pvs.vendor_site_code = pvsa.vendor_site_code and
pvs.vendor_id = x_vendor_id)
and mrp.inventory_item_id = x_item_id
and mrp.transaction_id = mrp.disposition_id
and mrp.order_type = 5
and mrp.compile_designator = x_plan_designator
and mrp.disposition_id = mrp.transaction_id
and nvl(mrp.firm_date,mrp.new_dock_date) between
x_horizon_start_date and
x_horizon_end_date ;
X_last_updated_by := fnd_global.user_id;
insert into chv_item_orders(schedule_id,
schedule_item_id,
schedule_order_id,
supply_document_type,
order_quantity,
order_quantity_primary,
purchasing_unit_of_measure,
primary_unit_of_measure,
due_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
values(x_schedule_id,
x_schedule_item_id,
CHV_ITEM_ORDERS_S.nextval,
'PLANNED_ORDER',
nvl(x_ord_qty * x_conversion_rate,0),
nvl(x_ord_qty,0),
x_purchasing_unit_of_measure,
x_primary_unit_of_measure,
x_new_dock_date,
sysdate,
X_last_updated_by,
sysdate,
X_last_updated_by,
X_login_id) ;
X_last_updated_by NUMBER;
cursor C1 is select prh.requisition_header_id,
prl.requisition_line_id,
ms.to_org_primary_quantity,
prl.need_by_date
from po_requisition_headers prh,
po_requisition_lines prl,
mtl_supply ms,
po_vendors pov,
po_vendor_sites pvs
where ms.to_organization_id = x_organization_id
and ms.supply_type_code = 'REQ'
and ms.req_header_id = prh.requisition_header_id
and ms.req_line_id = prl.requisition_line_id
and ms.quantity <> 0
and prl.suggested_vendor_name
= pov.vendor_name
and pov.vendor_id = x_vendor_id
and prl.suggested_vendor_location
= pvs.vendor_site_code
and pvs.vendor_site_id = x_vendor_site_id
and prl.item_id = x_item_id
and prl.source_type_code = 'VENDOR'
and prl.need_by_date
between x_horizon_start_date and
x_horizon_end_date ;
X_last_updated_by := fnd_global.user_id;
insert into chv_item_orders(schedule_id,
schedule_item_id,
schedule_order_id,
supply_document_type,
order_quantity,
order_quantity_primary,
purchasing_unit_of_measure,
primary_unit_of_measure,
due_date,
document_header_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
document_line_id,
document_shipment_id)
values(x_schedule_id,
x_schedule_item_id,
CHV_ITEM_ORDERS_S.nextval,
'REQUISITION',
nvl(X_to_org_purch_quantity,
X_to_org_primary_quantity),
X_to_org_primary_quantity,
X_purchasing_unit_of_measure,
X_primary_unit_of_measure,
X_need_by_date,
X_requisition_header_id,
sysdate,
X_last_updated_by,
sysdate,
X_last_updated_by,
X_login_id,
X_requisition_line_id,
null);
X_last_updated_by NUMBER;
CURSOR C1 IS select ms.po_header_id,
ms.po_line_id,
ms.po_line_location_id,
ms.to_org_primary_quantity,
ms.to_org_primary_uom,
ms.need_by_date
from po_headers poh,
mtl_supply ms
where ms.to_organization_id = x_organization_id
and ms.supply_type_code in ('PO','SHIPMENT')
and ms.po_header_id = poh.po_header_id
and ms.item_id = x_item_id
and ms.quantity <> 0
and poh.type_lookup_code = 'BLANKET'
and poh.vendor_id +0 = x_vendor_id
and poh.vendor_site_id = x_vendor_site_id
and poh.supply_agreement_flag = 'Y'
and ((nvl(x_include_future_rel_flag,'N') = 'N'
and x_only_past_due_flag = 'Y'
and ms.need_by_date < x_horizon_start_date + 1)
OR
(nvl(x_include_future_rel_flag,'N') = 'N'
and x_only_past_due_flag = 'N'
and ms.need_by_date < x_horizon_end_date + 1)
OR
(x_include_future_rel_flag = 'Y'
and x_only_past_due_flag = 'N'
and ms.need_by_date is NOT NULL)
);
all of the records. We could of done a straight insert
as select from. We should modify in the future to improve
performance */
BEGIN
X_login_id := fnd_global.login_id;
X_last_updated_by := fnd_global.user_id;
insert into chv_item_orders(schedule_id,
schedule_item_id,
schedule_order_id,
supply_document_type,
order_quantity,
order_quantity_primary,
purchasing_unit_of_measure,
primary_unit_of_measure,
due_date,
document_header_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
document_line_id,
document_shipment_id)
values(x_schedule_id,
x_schedule_item_id,
CHV_ITEM_ORDERS_S.nextval,
'RELEASE',
x_conversion_rate *
X_to_org_primary_quantity,
X_to_org_primary_quantity,
x_purchasing_unit_of_measure,
X_to_org_primary_uom,
X_need_by_date,
X_po_header_id,
sysdate,
X_last_updated_by,
sysdate,
X_last_updated_by,
X_login_id,
X_po_line_id,
X_line_location_id) ;