[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT schedule_designator
FROM mrp_schedule_designators s
WHERE organization_id = P_mds_rec.organization_id
AND schedule_type = 1
AND nvl(disable_date , trunc(sysdate) + 1) > trunc(sysdate)
AND NOT EXISTS (
SELECT null
FROM mrp_schedule_dates
WHERE mps_transaction_id = P_mds_rec.mps_transaction_id
AND schedule_designator = s.schedule_designator )
/*
AND schedule_designator IN ('' , ')
*/
ORDER BY schedule_designator;
SELECT mps_transaction_id
, schedule_quantity
FROM mrp_schedule_dates
WHERE schedule_designator = X_schedule_designator
AND organization_id = P_mds_rec.organization_id
AND inventory_item_id = P_mds_rec.inventory_item_id
AND mps_transaction_id <> P_mds_rec.mps_transaction_id
AND schedule_date = P_mds_rec.transaction_date
AND project_id = P_mds_rec.project_id
AND task_id = P_mds_rec.task_id
AND end_item_unit_number = P_mds_rec.unit_number
AND schedule_level = 2
AND schedule_quantity > 0
ORDER BY schedule_date , mps_transaction_id
FOR UPDATE OF schedule_quantity;
update mrp_schedule_dates
set schedule_quantity = l_new_demand_qty
, last_update_date = sysdate
, last_updated_by = fnd_global.user_id
, request_id = fnd_global.conc_request_id
, program_application_id = fnd_global.prog_appl_id
, program_id = fnd_global.conc_program_id
, program_update_date = sysdate
where mps_transaction_id = erec.mps_transaction_id
and schedule_level = 2;
write_log( '+++ MDS entry updated' );
INSERT INTO mrp_schedule_consumptions
( transaction_id
, relief_type
, disposition_type
, disposition_id
, line_num
, last_update_date
, last_updated_by
, creation_date
, created_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, order_date
, order_quantity
, relief_quantity
, schedule_date
) VALUES
( erec.mps_transaction_id
, 1 -- MDS_RELIEF 1, MPS_RELIEF 2
, 3 -- R_WORK_ORDER 1, R_PURCH_ORDER 2, R_SALES_ORDER 3
, null
, null
, sysdate
, fnd_global.user_id
, sysdate
, fnd_global.user_id
, fnd_global.login_id
, fnd_global.conc_request_id
, fnd_global.prog_appl_id
, fnd_global.conc_program_id
, sysdate
, P_mds_rec.transaction_date
, P_mds_rec.order_quantity
, l_relief_qty
, P_mds_rec.transaction_date
);