[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO cst_comp_snapshot
(transaction_id,
wip_entity_id,
operation_seq_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
new_operation_flag,
primary_quantity,
quantity_completed,
prior_completion_quantity,
prior_scrap_quantity,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT
l_txn_id,
codt.wip_entity_id,
codt.operation_seq_num,
sysdate,
-1,
sysdate,
-1,
-1,
codt.new_operation_flag,
codt.primary_quantity,
codt.quantity_completed,
codt.prior_completion_quantity,
codt.prior_scrap_quantity,
-1,
-1,
-1,
sysdate
FROM
cst_comp_snap_temp codt
WHERE
transaction_temp_id = l_txn_temp_id;
Delete temporary info from cst_comp_snap_temp
*/
DELETE FROM cst_comp_snap_temp
WHERE
transaction_temp_id = l_txn_temp_id;
Update prior_completion_quantity and prior_scrap_quantity
as the sum of previous primary_quantity of
all the previous transactions of the same job and
same operation
*/
UPDATE cst_comp_snapshot cocd1
SET
(prior_completion_quantity, prior_scrap_quantity) =
(SELECT
NVL
(SUM(
DECODE(mmt.transaction_action_id,
30,0,
cocd2.primary_quantity)
),
0),
NVL(SUM(
DECODE(mmt.transaction_action_id,
31,0,
32,0,
cocd2.primary_quantity)
),
0)
FROM
cst_comp_snapshot cocd2,
mtl_material_transactions mmt
WHERE
cocd2.transaction_id < cocd1.transaction_id
AND cocd2.transaction_id = mmt.transaction_id
AND cocd2.wip_entity_id = cocd1.wip_entity_id
AND cocd2.operation_seq_num = cocd1.operation_seq_num
GROUP BY
cocd2.operation_seq_num
)
WHERE
cocd1.transaction_id = l_txn_id
AND
EXISTS
(
SELECT 'x'
FROM cst_comp_snapshot cocd3
WHERE
cocd3.transaction_id < l_txn_id
AND cocd3.wip_entity_id = cocd1.wip_entity_id
AND cocd3.operation_seq_num = cocd1.operation_seq_num
);
* Update cst_comp_snapshot
* by setting new_operation_flag to 1
* if it is a new operation
* ie. operation_seq_num is smaller than the latter ones
* but the quantity_completed is also smaller.
*/
UPDATE cst_comp_snapshot cocd1
SET
new_operation_flag = 1
WHERE
cocd1.transaction_id = l_txn_id
AND cocd1.operation_seq_num IN
(SELECT cocd2.operation_seq_num
FROM
cst_comp_snapshot cocd2,
cst_comp_snapshot cocd3
WHERE
cocd2.transaction_id = l_txn_id
AND cocd2.transaction_id = cocd3.transaction_id
AND cocd2.operation_seq_num < cocd3.operation_seq_num
AND cocd2.quantity_completed < cocd3.quantity_completed
);
SELECT
operation_seq_num
FROM wip_operations
WHERE wip_entity_id = i_wip_entity_id;
SELECT
transaction_source_id,
l_primary_quantity,
transaction_action_id
FROM mtl_transactions_interface
WHERE transaction_interface_id = l_txn_interface_id;
SELECT
wip_entity_id,
primary_quantity,
to_operation_seq_num,
decode(transaction_type,1,30,2,31,3,32)
FROM wip_move_txn_interface
WHERE transaction_id = l_txn_interface_id;
SELECT count(*)
INTO l_wop_count
FROM wip_operations
WHERE wip_entity_id = i_wip_entity_id;
SELECT 'x'
INTO dummy
FROM cst_comp_snap_interface codt
WHERE transaction_interface_id = l_txn_interface_id
AND rownum = 1;
SELECT 'x'
INTO dummy
FROM cst_comp_snap_interface codt
WHERE transaction_interface_id = l_txn_interface_id
AND wip_entity_id = i_wip_entity_id
AND rownum = 1;
SELECT 'x'
INTO dummy
FROM cst_comp_snap_interface codt
WHERE
codt.transaction_interface_id = l_txn_interface_id
AND codt.wip_entity_id = i_wip_entity_id
AND codt.operation_seq_num = v_operation_seq_num;
SELECT count(*)
INTO v_count1
FROM cst_comp_snap_interface
WHERE transaction_interface_id = l_txn_interface_id
AND wip_entity_id = i_wip_entity_id
AND operation_seq_num <= i_operation_seq_num;
SELECT count(*)
INTO v_count2
FROM cst_comp_snap_interface
WHERE transaction_interface_id = l_txn_interface_id
AND wip_entity_id = i_wip_entity_id
AND operation_seq_num <= i_operation_seq_num
AND primary_quantity = i_primary_quantity;
SELECT count(*)
INTO v_count1
FROM cst_comp_snap_interface
WHERE transaction_interface_id = l_txn_interface_id
AND wip_entity_id = i_wip_entity_id;
SELECT count(*)
INTO v_count2
FROM cst_comp_snap_interface
WHERE transaction_interface_id = l_txn_interface_id
AND wip_entity_id = i_wip_entity_id
AND primary_quantity = i_primary_quantity;
UPDATE cst_comp_snap_interface
SET primary_quantity =
decode(i_transaction_action_id,
30,
decode(sign(operation_seq_num - i_operation_seq_num),
1,0,
i_primary_quantity),
i_primary_quantity)
WHERE transaction_interface_id = l_txn_interface_id
AND wip_entity_id = i_wip_entity_id;
SELECT
transaction_action_id
FROM mtl_transactions_interface
WHERE transaction_interface_id = l_txn_interface_id;
SELECT
to_operation_seq_num,
decode(transaction_type,1,30,2,31,3,32)
FROM wip_move_txn_interface
WHERE transaction_id = l_txn_interface_id;
INSERT INTO cst_comp_snap_temp
(transaction_temp_id,
wip_entity_id,
operation_seq_num,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
new_operation_flag,
primary_quantity,
quantity_completed,
prior_completion_quantity,
prior_scrap_quantity,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT
l_txn_temp_id,
codt.wip_entity_id,
codt.operation_seq_num,
sysdate,
-1,
sysdate,
-1,
-1,
--codt.new_operation_flag,
-- we assume this is not new operation flag
-- the following package will change it if necessary.
2,
--
-- Bug 608310
-- If the transaction is a scrap transaction
-- Operation after the scrap operation should be
-- primary_quantity of zero.
--
decode(i_transaction_action_id,
30,
decode(sign(operation_seq_num - i_operation_seq_num),
1,0,
codt.primary_quantity),
codt.primary_quantity),
codt.quantity_completed,
codt.prior_completion_quantity,
codt.prior_scrap_quantity,
-1,
-1,
-1,
sysdate
FROM
cst_comp_snap_interface codt
WHERE
transaction_interface_id = l_txn_interface_id;
Delete temporary info from cst_comp_snap_interface
*/
DELETE FROM cst_comp_snap_interface
WHERE
transaction_interface_id = l_txn_interface_id;