|
DECLARE
|
|
TYPE t_wip_entity_id IS TABLE OF work_order_qty_stg.wip_entity_id%TYPE;
|
|
TYPE t_op_seq_num IS TABLE OF work_order_qty_stg.operation_seq_num%TYPE;
|
|
vt_we_id t_wip_entity_id;
|
|
vt_op_seq t_op_seq_num;
|
|
v_upd_cnt PLS_INTEGER :=0;
|
|
BEGIN
|
|
SELECT wip_entity_id, operation_seq_num BULK COLLECT INTO vt_we_id, vt_op_seq
|
|
FROM work_order_qty_stg;
|
| |
|
FORALL idx IN vt_we_id.FIRST .. vt_we_id.LAST
|
| |
|
UPDATE ops_transaction_detail td
|
|
SET (td.op_quantity_queued,
|
|
td.op_quantity_wip,
|
|
td.op_quantity_rejected,
|
|
td.op_quantity_scrapped,
|
|
td.op_quantity_completed) =
|
|
(SELECT
|
|
u.op_quantity_queued,
|
|
u.op_quantity_wip,
|
|
u.op_quantity_rejected,
|
|
u.op_quantity_scrapped,
|
|
u.op_quantity_completed
|
|
FROM work_order_qty_stg u
|
|
WHERE td.operation_seq_num = u.operation_seq_num
|
|
AND td.wip_entity_id = u.wip_entity_id)
|
|
WHERE td.wip_entity_id = vt_we_id(idx)
|
|
AND td.operation_seq_num = vt_op_seq(idx);
|
| |
|
DBMS_OUTPUT.PUT_LINE('Staging Table Row Count -> '||vt_we_id.COUNT);
|
| |
|
FOR idx IN vt_we_id.FIRST .. vt_we_id.LAST LOOP
|
|
v_upd_cnt := v_upd_cnt + SQL%BULK_ROWCOUNT(idx);
|
|
END LOOP;
|
|
DBMS_OUTPUT.PUT_LINE('Transaction Table Update Count -> '||v_upd_cnt);
|
|
COMMIT;
|
|
END;
|
|
/
|