[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.transaction_id
, a.organization_id
, a.inventory_item_id
, nvl(a.transaction_source_id , 0) transaction_source_id
, a.transaction_source_type_id
, a.trx_source_delivery_id
, a.trx_source_line_id
, a.revision
, a.subinventory_code
, a.locator_id
, a.primary_quantity
, a.transaction_quantity
, a.transaction_source_name
, a.transaction_date
, d.mps_transaction_id
, d.quantity
, d.project_id
, d.task_id
, d.unit_number
FROM mtl_system_items c
, mtl_material_transactions a
, oke_k_deliverables_b d
, mrp_schedule_dates m
WHERE a.source_code = 'OKE'
AND a.organization_id = c.organization_id
AND a.inventory_item_id = c.inventory_item_id
AND a.primary_quantity < 0
AND a.transaction_source_type_id = 16
AND a.transaction_source_id = d.k_header_id
AND d.deliverable_id = a.source_line_id
And m.mps_transaction_id = d.mps_transaction_id
And m.schedule_level = 2
And m.supply_demand_type = 1
And m.schedule_quantity > 0
AND a.transaction_id > nvl( d.po_ref_3 , 0 )
ORDER BY a.transaction_id ASC;
x_mds_tbl.DELETE;
SELECT schedule_quantity, original_schedule_quantity, schedule_date
FROM Mrp_Schedule_Dates
WHERE mps_transaction_id = p_transaction_id
AND Schedule_Level = 2
AND supply_demand_type = 1
AND Schedule_Quantity > 0
FOR UPDATE OF schedule_quantity;
SELECT d.mps_transaction_id
, d.quantity
, d.project_id
, d.task_id
, d.unit_number
, m.schedule_quantity
, m.schedule_date
FROM okc_k_lines_b a
, oke_k_deliverables_b d
, mrp_schedule_dates m
, okc_statuses_b sts
WHERE d.k_line_id = a.id
And m.mps_transaction_id = d.mps_transaction_id
And m.schedule_level = 2
And m.supply_demand_type = 1
And m.schedule_quantity > 0
And sts.code =a.sts_code
And sts.ste_code in ( 'CANCELLED','TERMINATED');
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 (
l_mds_tbl(i).mps_transaction_id,
1, -- MDS_RELIEF 1, MPS_RELIEF 2
3, -- R_WORK_ORDER 1, R_PURCH_ORDER 2, R_SALES_ORDER 3
NULL, -- l_mds_tbl(i).mtl_transaction_id,
NULL, -- No order line_num
l_now,
l_user_id,
l_now,
l_user_id,
l_login_id,
l_conc_request_id,
l_prog_appl_id,
l_conc_program_id,
l_now,
l_mds_tbl(i).transaction_date,
l_mds_tbl(i).order_quantity,
-l_mds_tbl(i).primary_quantity,
l_schedule_date
);
SELECT Greatest(Nvl(l_mds_tbl(i).order_quantity,0)-Nvl(SUM(RELIEF_QUANTITY),0),0)
INTO l_qty
FROM mrp_schedule_consumptions mc
where mc.transaction_id = l_mds_tbl(i).mps_transaction_id
AND mc.relief_type=1 AND mc.disposition_type=3
;
UPDATE mrp_schedule_dates d
SET schedule_quantity = l_qty
, original_schedule_quantity = l_mds_tbl(i).order_quantity
, last_update_date = l_now
, last_updated_by = l_user_id
, request_id = l_conc_request_id
, program_application_id = l_prog_appl_id
, program_id = l_conc_program_id
, program_update_date = l_now
WHERE CURRENT OF qty_c;
write_log( 'MDS entry updated' );
UPDATE oke_k_deliverables_b d
SET PO_REF_3 = l_mds_tbl(i).mtl_transaction_id
WHERE mps_transaction_id = l_mds_tbl(i).mps_transaction_id;
write_log( 'DLV 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 (
cancel_rec.mps_transaction_id,
1, -- MDS_RELIEF 1, MPS_RELIEF 2
3, -- R_WORK_ORDER 1, R_PURCH_ORDER 2, R_SALES_ORDER 3
NULL, -- l_mds_tbl(i).mtl_transaction_id,
NULL, -- No order line_num
l_now,
l_user_id,
l_now,
l_user_id,
l_login_id,
l_conc_request_id,
l_prog_appl_id,
l_conc_program_id,
l_now,
sysdate,
cancel_rec.quantity,
cancel_rec.schedule_quantity,
cancel_rec.schedule_date
);
SELECT Greatest(Nvl(cancel_rec.quantity,0)-Nvl(SUM(RELIEF_QUANTITY),0),0)
INTO l_qty
FROM mrp_schedule_consumptions mc
where mc.transaction_id = cancel_rec.mps_transaction_id
AND mc.relief_type=1 AND mc.disposition_type=3
;
UPDATE mrp_schedule_dates d
SET schedule_quantity = l_qty
, original_schedule_quantity = cancel_rec.quantity
, last_update_date = l_now
, last_updated_by = l_user_id
, request_id = l_conc_request_id
, program_application_id = l_prog_appl_id
, program_id = l_conc_program_id
, program_update_date = l_now
WHERE mps_transaction_id =cancel_rec.mps_transaction_id;