[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATED_SCHEDULE CONSTANT INTEGER := 2;
SELECT jobs.rowid,
jobs.wip_entity_id,
jobs.organization_id
FROM wip_requirement_operations ops,
wip_discrete_jobs jobs,
mrp_relief_interface mrp
WHERE ops.wip_entity_id (+) = jobs.wip_entity_id
AND ops.organization_id (+) = jobs.organization_id
AND mrp.disposition_type = R_WORK_ORDER
AND mrp.relief_type = MPS_RELIEF_TYPE
AND mrp.request_id = arg_request_id
AND mrp.process_status = IN_PROCESS
AND mrp.error_message is NULL
AND mrp.inventory_item_id =
DECODE(arg_item_id,NULL_VALUE,
mrp.inventory_item_id,
arg_item_id)
AND mrp.organization_id =
DECODE(arg_org_id,NULL_VALUE,mrp.organization_id,
arg_org_id)
AND jobs.primary_item_id = mrp.inventory_item_id
AND jobs.organization_id = mrp.organization_id
AND jobs.wip_entity_id = mrp.disposition_id
FOR UPDATE OF jobs.mps_net_quantity,
ops.mps_required_quantity
ORDER BY jobs.organization_id, jobs.wip_entity_id;
SELECT fs.rowid,
fs.wip_entity_id,
fs.organization_id
FROM wip_flow_schedules fs,
mrp_relief_interface mrp
WHERE mrp.disposition_type = R_FLOW_SCHEDULE
AND mrp.relief_type = MPS_RELIEF_TYPE
AND mrp.request_id = arg_request_id
AND mrp.process_status = IN_PROCESS
AND mrp.error_message is NULL
AND mrp.inventory_item_id =
DECODE(arg_item_id,NULL_VALUE,
mrp.inventory_item_id,
arg_item_id)
AND mrp.organization_id =
DECODE(arg_org_id,NULL_VALUE,mrp.organization_id,
arg_org_id)
AND fs.primary_item_id = mrp.inventory_item_id
AND fs.organization_id = mrp.organization_id
AND fs.wip_entity_id = mrp.disposition_id
FOR UPDATE OF fs.mps_net_quantity,
fs.mps_scheduled_completion_date
ORDER BY fs.organization_id, fs.wip_entity_id;
SELECT ms.rowid
FROM mtl_supply ms
where ms.rowid in (
SELECT /*+ INDEX(supply MTL_SUPPLY_N7) */
supply.rowid
FROM mtl_supply supply,
mrp_relief_interface mrp
WHERE mrp.disposition_type = R_PURCH_REQ
AND mrp.relief_type = MPS_RELIEF_TYPE
AND mrp.inventory_item_id =
DECODE(arg_item_id, NULL_VALUE, inventory_item_id,
arg_item_id)
AND mrp.organization_id =
DECODE(arg_org_id, NULL_VALUE, organization_id,
arg_org_id)
AND supply.item_id = mrp.inventory_item_id
AND supply.supply_type_code = 'REQ'
AND mrp.line_num = supply.req_line_id
AND supply.to_organization_id = mrp.organization_id
AND mrp.disposition_id= supply.req_header_id
AND supply.destination_type_code = 'INVENTORY'
AND mrp.error_message is NULL
AND mrp.process_status = IN_PROCESS
AND mrp.request_id = arg_request_id
UNION
SELECT /*+ INDEX(supply MTL_SUPPLY_N5) */
supply.rowid
FROM mtl_supply supply,
mrp_relief_interface mrp
WHERE mrp.disposition_type = R_PURCH_ORDER
AND mrp.relief_type = MPS_RELIEF_TYPE
AND mrp.inventory_item_id =
DECODE(arg_item_id, NULL_VALUE, inventory_item_id,
arg_item_id)
AND mrp.organization_id =
DECODE(arg_org_id, NULL_VALUE, organization_id,
arg_org_id)
AND supply.item_id = mrp.inventory_item_id
AND mrp.line_num = supply.po_line_id
AND supply.to_organization_id = mrp.organization_id
AND mrp.disposition_id= supply.po_header_id
AND supply.supply_type_code = 'PO'
AND supply.destination_type_code = 'INVENTORY'
AND mrp.error_message is NULL
AND mrp.request_id = arg_request_id
AND mrp.process_status = IN_PROCESS
UNION
SELECT /*+ INDEX(supply MTL_SUPPLY_N9) */
supply.rowid
FROM mtl_supply supply,
mrp_relief_interface mrp
WHERE mrp.disposition_type = R_SHIPMENT
AND mrp.relief_type = MPS_RELIEF_TYPE
AND mrp.inventory_item_id =
DECODE(arg_item_id, NULL_VALUE, inventory_item_id,
arg_item_id)
AND mrp.organization_id =
DECODE(arg_org_id, NULL_VALUE, organization_id,
arg_org_id)
AND supply.item_id = mrp.inventory_item_id
AND mrp.line_num = supply.shipment_line_id
AND supply.to_organization_id = mrp.organization_id
AND mrp.disposition_id= supply.shipment_header_id
AND supply.supply_type_code = 'SHIPMENT'
AND supply.destination_type_code = 'INVENTORY'
AND mrp.error_message is NULL
AND mrp.process_status = IN_PROCESS
AND mrp.request_id = arg_request_id
UNION
SELECT /*+ INDEX(supply MTL_SUPPLY_N5) */
supply.rowid
FROM mtl_supply supply,
mrp_relief_interface mrp
WHERE mrp.disposition_type = R_PO_RECV
AND mrp.relief_type = MPS_RELIEF_TYPE
AND mrp.inventory_item_id =
DECODE(arg_item_id, NULL_VALUE, inventory_item_id,
arg_item_id)
AND mrp.organization_id =
DECODE(arg_org_id, NULL_VALUE, organization_id,
arg_org_id)
AND supply.item_id = mrp.inventory_item_id
AND mrp.line_num = supply.po_line_id
AND supply.to_organization_id = mrp.organization_id
AND mrp.disposition_id= supply.po_header_id
AND supply.supply_type_code = 'RECEIVING'
AND supply.destination_type_code = 'INVENTORY'
AND mrp.error_message is NULL
AND mrp.process_status = IN_PROCESS
AND mrp.request_id = arg_request_id
UNION
SELECT /*+ INDEX(supply MTL_SUPPLY_N9) */
supply.rowid
FROM mtl_supply supply,
mrp_relief_interface mrp
WHERE mrp.disposition_type = R_SHIPMENT_RCV
AND mrp.relief_type = MPS_RELIEF_TYPE
AND mrp.inventory_item_id =
DECODE(arg_item_id, NULL_VALUE, inventory_item_id,
arg_item_id)
AND mrp.organization_id =
DECODE(arg_org_id, NULL_VALUE, organization_id,
arg_org_id)
AND mrp.error_message is NULL
AND supply.item_id = mrp.inventory_item_id
AND mrp.line_num = supply.shipment_line_id
AND supply.to_organization_id = mrp.organization_id
AND mrp.disposition_id= supply.shipment_header_id
AND supply.supply_type_code = 'RECEIVING'
AND supply.destination_type_code = 'INVENTORY'
AND mrp.process_status = IN_PROCESS
AND mrp.request_id = arg_request_id)
FOR UPDATE OF ms.mrp_expected_delivery_date ;
SELECT dates.rowid
FROM
mrp_schedule_dates dates
WHERE dates.schedule_quantity >= 0
AND dates.original_schedule_quantity >= 0
AND dates.schedule_level = PSEUDO_SCHEDULE
AND dates.schedule_origination_type = NULL_VALUE
AND dates.supply_demand_type = SCHEDULE_SUPPLY
AND (dates.organization_id,dates.schedule_designator) IN
(select
nvl(plans.planned_organization,
desig.organization_id),
desig.schedule_designator
from
mrp_schedule_designators desig,
mrp_plan_organizations_v plans
WHERE
NVL(desig.disable_date, TRUNC(SYSDATE)+1)>TRUNC(SYSDATE)
AND desig.mps_relief = 1
AND desig.schedule_type = 2
AND desig.schedule_designator =plans.compile_designator(+)
AND desig.organization_id = plans.organization_id (+)
AND nvl(plans.planned_organization,
desig.organization_id) in
(select distinct mrp.organization_id
from mrp_relief_interface mrp
where
mrp.relief_type = MPS_RELIEF_TYPE
AND mrp.error_message is NULL
AND mrp.process_status = IN_PROCESS
AND mrp.request_id = arg_request_id)
);
SELECT /* ORDERED
INDEX(recom MRP_RECOMMENDATIONS_N1)
INDEX(mrp MRP_RELIEF_INTERFACE_N1)
INDEX(plans MRP_PLANS_U1) */
recom.rowid
FROM mrp_recommendations recom
WHERE recom.new_order_quantity >= 0
AND recom.firm_planned_type = SYS_NO
AND recom.disposition_status_type = NULL_VALUE
AND recom.order_type = PSEUDO_PLANNED_ORDER
AND (recom.compile_designator,recom.organization_id)
IN
(select plans.compile_designator ,
plans.planned_organization
from
mrp_designators_view desig,
mrp_plan_organizations_v plans
where
NVL(desig.disable_date, TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
AND desig.organization_id = plans.organization_id
AND desig.designator = plans.compile_designator
AND plans.planned_organization in
(select distinct mrp.organization_id
from mrp_relief_interface mrp
where
mrp.relief_type = MPS_RELIEF_TYPE
AND mrp.error_message is NULL
AND mrp.process_status = IN_PROCESS
AND mrp.request_id = arg_request_id)
);
'GEN-deleted from table', arg_request_id, arg_user_id,
'TABLE', 'mrp_schedule_dates', 'N');
DELETE FROM mrp_schedule_dates dates
WHERE rowid = var_rowid;
'GEN-deleted from table', arg_request_id, arg_user_id,
'TABLE', 'mrp_recommendations', 'N');
DELETE FROM mrp_recommendations recom
WHERE rowid = var_rowid;
'GEN-updated', arg_request_id, arg_user_id, 'ENTITY',
'wip_discrete_jobs', 'N');
UPDATE wip_discrete_jobs jobs
SET mps_scheduled_completion_date = scheduled_completion_date,
mps_net_quantity = net_quantity,
last_update_date = SYSDATE,
last_updated_by = arg_user_id
WHERE rowid = var_rowid;
UPDATE wip_requirement_operations ops
SET ops.mps_required_quantity = ops.required_quantity,
ops.mps_date_required = ops.date_required,
ops.last_update_date = SYSDATE,
ops.last_updated_by = arg_user_id
WHERE wip_entity_id = var_wip_entity_id
AND organization_id = var_org_id;
'GEN-updated', arg_request_id, arg_user_id, 'ENTITY',
'wip_flow_schedules', 'N');
UPDATE wip_flow_schedules fs
SET mps_scheduled_completion_date = scheduled_completion_date,
mps_net_quantity = planned_quantity,
last_update_date = SYSDATE,
last_updated_by = arg_user_id
WHERE rowid = var_rowid;
'GEN-updated', arg_request_id, arg_user_id,
'ENTITY', 'mtl_supply', 'N');
UPDATE mtl_supply supply
SET mrp_expected_delivery_date = expected_delivery_date ,
mrp_primary_quantity = to_org_primary_quantity,
mrp_to_organization_id = to_organization_id,
mrp_destination_type_code = destination_type_code,
mrp_to_subinventory = to_subinventory,
last_update_date = SYSDATE,
last_updated_by = arg_user_id,
mrp_primary_uom =
(SELECT uom_code
FROM mtl_units_of_measure
WHERE unit_of_measure = supply.to_org_primary_uom)
WHERE rowid = var_rowid;
END mrp_update_mrp_cols;