[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_insert_ind NUMBER;
SELECT
wip_entity_id,
organization_id,
inventory_item_id,
operation_seq_num,
wip_supply_type
FROM
wip_requirement_operations wro
WHERE
--
-- Exclude bulk, supplier, phantom
--
wro.wip_supply_type not in (4,5,6) AND
wro.wip_entity_id = i_wip_entity_id AND
wro.organization_id = i_org_id AND
wro.quantity_per_assembly <> 0;
SELECT
wip_entity_id,
organization_id,
inventory_item_id,
operation_seq_num,
wip_supply_type
FROM
wip_requirement_operations wro
WHERE
wro.wip_entity_id = i_wip_entity_id AND
wro.organization_id = i_org_id ;
SELECT
cost_element_id,
sum(
nvl(applied_matl_value,0) -
nvl(relieved_matl_completion_value,0) -
nvl(relieved_variance_value,0) -
nvl(relieved_matl_scrap_value,0)
)
FROM
wip_req_operation_cost_details
WHERE
wip_entity_id = i_wip_entity_id
GROUP BY
cost_element_id;
UPDATE wip_req_operation_cost_details
SET temp_relieved_value = 0
WHERE
WIP_ENTITY_ID = i_wip_entity_id AND
ORGANIZATION_ID = i_org_id;
UPDATE WIP_OPERATION_RESOURCES
SET temp_relieved_value = 0
WHERE
WIP_ENTITY_ID = i_wip_entity_id AND
ORGANIZATION_ID = i_org_id;
UPDATE WIP_OPERATION_OVERHEADS
SET temp_relieved_value = 0
WHERE
WIP_ENTITY_ID = i_wip_entity_id AND
ORGANIZATION_ID = i_org_id;
select wac.completion_cost_source, nvl(wac.cost_type_id,-1),
wdj.start_quantity,nvl(wac.SYSTEM_OPTION_ID,-1)
into l_comp_cost_source,l_c_cost_type_id,l_lot_size,l_system_option_id
from
wip_accounting_classes wac,
wip_discrete_jobs wdj
where
wdj.wip_entity_id = i_wip_entity_id and
wdj.organization_id = i_org_id and
wdj.class_code = wac.class_code and
wdj.organization_id = wac.organization_id;
SELECT
decode(job_type,
1,decode(bom_revision,
NULL,decode(routing_revision,NULL,-1,1),
1),
3,decode(bom_reference_id,
NULL,decode(routing_reference_id,NULL,-1,1),
1),
1)
into
l_use_val_cost_type
from
WIP_DISCRETE_JOBS
WHERE
WIP_ENTITY_ID = i_wip_entity_id AND
ORGANIZATION_ID = i_org_id;
select count(*)
into l_qty_per_assy
from wip_requirement_operations
where wip_entity_id = i_wip_entity_id
and quantity_per_assembly <>0;
SELECT count(1)
INTO l_qty_per_assy
FROM dual
WHERE EXISTS ( SELECT NULL
FROM wip_requirement_operations wro
WHERE wro.wip_entity_id = i_wip_entity_id
AND wro.quantity_per_assembly <>0
UNION ALL
SELECT NULL
FROM wip_operation_resources wor
WHERE wor.wip_entity_id = i_wip_entity_id
AND wor.usage_rate_or_amount <>0
);
| Initialize insert indicator : This will indicate to us if we
| need to insert a row into cst_txn_cost_details in the last
| step. There are cases where the algorithm inserts into
| cst_txn_cost_details without updating the detailed wip
| tables. In these cases
| we will directly insert into cst_txn_cost_details
| and so we need to skip the insert stmt in
| the end.
|--------------------------------------------------------------*/
l_insert_ind := 0;
completion also.Update of WRO is now in loop */
FOR wro_rec IN c_wip_final_req_op LOOP
l_future_issued_qty := 0;
SELECT nvl(sum(primary_quantity),0)
INTO l_future_issued_qty
FROM mtl_material_transactions
WHERE organization_id = wro_rec.organization_id
AND inventory_item_id = wro_rec.inventory_item_id
AND operation_seq_num = wro_rec.operation_seq_num
AND transaction_source_id = wro_rec.wip_entity_id
AND ( (transaction_date > i_txn_date) or
(transaction_date = i_txn_date and transaction_id > i_trx_id) )
AND costed_flag IS NOT NULL
AND nvl(completion_transaction_id,-999) <>
( Select nvl(completion_transaction_id,-999)
from mtl_material_transactions
where transaction_id = i_trx_id);
UPDATE wip_requirement_operations w
SET
relieved_matl_completion_qty =
(SELECT
nvl(quantity_issued,0)-
nvl(relieved_matl_final_comp_qty,0)-
nvl(relieved_matl_scrap_quantity,0) +
l_future_issued_qty /* for bug 4246122 */
FROM wip_requirement_operations w2
WHERE
w.wip_entity_id = w2.wip_entity_id AND
w.organization_id = w2.organization_id AND
w.inventory_item_id = w2.inventory_item_id AND
w.operation_seq_num = w2.operation_seq_num
)
WHERE
w.wip_entity_id = i_wip_entity_id AND
w.organization_id = i_org_id AND
w.inventory_item_id = wro_rec.inventory_item_id AND /*added for bug 4246122 */
w.operation_seq_num = wro_rec.operation_seq_num /*added for bug 4246122 */
AND exists (
SELECT 'x'
FROM wip_req_operation_cost_details wrocd
WHERE wrocd.wip_entity_id = w.wip_entity_id
AND wrocd.organization_id = w.organization_id
GROUP BY
wrocd.wip_entity_id,
wrocd.organization_id,
wrocd.cost_element_id
HAVING sum(nvl(applied_matl_value,0) -
nvl(relieved_matl_completion_value,0) -
nvl(relieved_variance_value,0) -
nvl(relieved_matl_scrap_value,0)) >= 0
);
UPDATE wip_requirement_operations w
SET
relieved_matl_final_comp_qty =
(SELECT
nvl(quantity_issued,0)-
nvl(relieved_matl_completion_qty,0)-
nvl(relieved_matl_scrap_quantity,0) +
l_future_issued_qty /* for bug 4246122 */
FROM wip_requirement_operations w2
WHERE
w.wip_entity_id = w2.wip_entity_id AND
w.organization_id = w2.organization_id AND
w.inventory_item_id = w2.inventory_item_id AND
w.operation_seq_num = w2.operation_seq_num
)
WHERE
w.wip_entity_id = i_wip_entity_id AND
w.organization_id = i_org_id AND
w.inventory_item_id = wro_rec.inventory_item_id AND /* added for bug 4246122 */
w.operation_seq_num = wro_rec.operation_seq_num /*added for bug 4246122 */
AND not exists (
SELECT 'x'
FROM wip_req_operation_cost_details wrocd
WHERE wrocd.wip_entity_id = w.wip_entity_id
AND wrocd.organization_id = w.organization_id
GROUP BY
wrocd.wip_entity_id,
wrocd.organization_id,
wrocd.cost_element_id
HAVING sum(nvl(applied_matl_value,0) -
nvl(relieved_matl_completion_value,0) -
nvl(relieved_variance_value,0) -
nvl(relieved_matl_scrap_value,0)) >= 0
);
UPDATE wip_req_operation_cost_details w
SET relieved_matl_completion_value =
nvl(applied_matl_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0),
temp_relieved_value =
nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)
WHERE w.wip_entity_id = i_wip_entity_id AND
w.cost_element_id = l_cost_element;
UPDATE wip_req_operation_cost_details w
SET relieved_variance_value =
nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_matl_scrap_value,0),
temp_relieved_value =
nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)
WHERE w.wip_entity_id = i_wip_entity_id AND
w.cost_element_id = l_cost_element;
UPDATE wip_operation_resources w
SET
(relieved_res_completion_units,
relieved_res_completion_value,
temp_relieved_value
) = (
SELECT
---
--- relieved_res_completion_units
---
nvl(applied_resource_units,0)-
nvl(relieved_res_final_comp_units,0)-
nvl(relieved_res_scrap_units,0),
---
--- relieved_res_completion_value
---
nvl(applied_resource_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0),
---
--- temp_relieved_value
---
nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)
FROM wip_operation_resources w2
WHERE
w.wip_entity_id = w2.wip_entity_id and
w.organization_id = w2.organization_id and
w.operation_seq_num = w2.operation_seq_num and
w.resource_seq_num = w2.resource_seq_num
)
WHERE w.wip_entity_id = i_wip_entity_id
AND w.organization_id = i_org_id
AND EXISTS
(SELECT null
FROM wip_operation_resources wor,
bom_resources br
WHERE wor.wip_entity_id = i_wip_entity_id
AND wor.organization_id = i_org_id
AND wor.organization_id = br.organization_id
AND wor.resource_id = br.resource_id
AND EXISTS
(SELECT null
FROM wip_operation_resources w3,
bom_resources br3
WHERE w3.wip_entity_id = i_wip_entity_id
AND w3.organization_id = i_org_id
AND w3.resource_seq_num = w.resource_seq_num
AND w3.operation_seq_num = w.operation_seq_num
AND w3.resource_id = br3.resource_id
AND w3.organization_id = br3.organization_id
AND br3.cost_element_id = br.cost_element_id)
GROUP BY br.cost_element_id
HAVING sum(nvl(applied_resource_value,0) -
nvl(relieved_res_completion_value,0) -
nvl(relieved_variance_value,0) -
nvl(relieved_res_scrap_value,0)) >= 0);
if stmt 120 updates wor rows*/
if (SQL%ROWCOUNT = 0) then
stmt_num := 121;
UPDATE wip_operation_resources w
SET
(relieved_res_final_comp_units,
relieved_variance_value,
temp_relieved_value
) = (
SELECT
---
--- relieved_res_final_comp_units
---
nvl(applied_resource_units,0)-
nvl(relieved_res_completion_units,0)-
nvl(relieved_res_scrap_units,0),
---
--- relieved_variance_value
---
nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_res_scrap_value,0),
---
--- temp_relieved_value
---
nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)
FROM wip_operation_resources w2
WHERE
w.wip_entity_id = w2.wip_entity_id and
w.organization_id = w2.organization_id and
w.operation_seq_num = w2.operation_seq_num and
w.resource_seq_num = w2.resource_seq_num
)
WHERE w.wip_entity_id = i_wip_entity_id
AND w.organization_id = i_org_id
AND EXISTS
(SELECT null
FROM wip_operation_resources wor,
bom_resources br
WHERE wor.wip_entity_id = i_wip_entity_id
AND wor.organization_id = i_org_id
AND wor.organization_id = br.organization_id
AND wor.resource_id = br.resource_id
AND EXISTS
(SELECT null
FROM wip_operation_resources w3,
bom_resources br3
WHERE w3.wip_entity_id = i_wip_entity_id
AND w3.organization_id = i_org_id
AND w3.resource_seq_num = w.resource_seq_num
AND w3.operation_seq_num = w.operation_seq_num
AND w3.resource_id = br3.resource_id
AND w3.organization_id = br3.organization_id
AND br3.cost_element_id = br.cost_element_id)
GROUP BY br.cost_element_id
HAVING sum(nvl(applied_resource_value,0) -
nvl(relieved_res_completion_value,0) -
nvl(relieved_variance_value,0) -
nvl(relieved_res_scrap_value,0)) < 0);
UPDATE wip_operation_overheads w
set (relieved_ovhd_completion_units,
relieved_ovhd_completion_value,
temp_relieved_value) =
(SELECT
---
--- relieved_ovhd_completion_units
---
nvl(applied_ovhd_units,0)-
nvl(relieved_ovhd_scrap_units,0) -
nvl(relieved_ovhd_final_comp_units,0),
---
--- relieved_ovhd_completion_value
---
nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_scrap_value,0) -
nvl(relieved_variance_value,0),
---
--- temp_relieved_value
---
nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)
FROM
wip_operation_overheads w2
where
w.wip_entity_id = w2.wip_entity_id AND
w.organization_id = w2.organization_id AND
w.operation_seq_num = w2.operation_seq_num AND
w.resource_seq_num = w2.resource_seq_num AND
w.overhead_id = w2.overhead_id AND
w.basis_type = w2.basis_type
)
WHERE
w.wip_entity_id = i_wip_entity_id AND
w.organization_id = i_org_id
AND exists (
SELECT 'x'
FROM wip_operation_overheads woo
WHERE woo.wip_entity_id = w.wip_entity_id
AND woo.organization_id = w.organization_id
HAVING sum(nvl(applied_ovhd_value,0) -
nvl(relieved_ovhd_completion_value,0) -
nvl(relieved_variance_value,0) -
nvl(relieved_ovhd_scrap_value,0)) >= 0
);
if stmt 132 updates woo rows*/
if (SQL%ROWCOUNT = 0) then
stmt_num := 133;
UPDATE wip_operation_overheads w
set (relieved_ovhd_final_comp_units,
relieved_variance_value,
temp_relieved_value) =
(SELECT
---
--- relieved_ovhd_final_comp_units
---
nvl(applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_scrap_units,0),
---
--- relieved_variance_value
---
nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_ovhd_scrap_value,0),
---
--- temp_relieved_value
---
nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)
FROM
wip_operation_overheads w2
where
w.wip_entity_id = w2.wip_entity_id AND
w.organization_id = w2.organization_id AND
w.operation_seq_num = w2.operation_seq_num AND
w.resource_seq_num = w2.resource_seq_num AND
w.overhead_id = w2.overhead_id AND
w.basis_type = w2.basis_type
)
WHERE
w.wip_entity_id = i_wip_entity_id AND
w.organization_id = i_org_id
AND exists (
SELECT 'x'
FROM wip_operation_overheads woo
WHERE woo.wip_entity_id = w.wip_entity_id
AND woo.organization_id = w.organization_id
HAVING sum(nvl(applied_ovhd_value,0) -
nvl(relieved_ovhd_completion_value,0) -
nvl(relieved_variance_value,0) -
nvl(relieved_ovhd_scrap_value,0)) < 0
);
| Set the insert indicator to ensure that we skip the insert
| into cst_txn_cst_details at the end of the file.
| Then insert into mtl_cst_txn_cost_details in 2 passes,
| one for PL costs and one for TL costs ...
|-----------------------------------------------------------*/
/*-------------------------------------------------------
| TL MO should never be inserted - it will be earned by
| the Cost processor, so weed out cost_element_id = 2
|______________________________________________________*/
l_insert_ind := 1;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
cce.cost_element_id,
1,
decode(cce.cost_element_id,
1,sum(0 - nvl(tl_material_out,0)-nvl(tl_material_var,0)),
2,sum(0 - nvl(tl_material_overhead_out,0)-nvl(tl_material_overhead_var,0)),
3,sum(nvl(tl_resource_in,0)-nvl(tl_resource_out,0)-nvl(tl_resource_var,0)),
4,sum(nvl(tl_outside_processing_in,0)-nvl(tl_outside_processing_out,0)-nvl(tl_outside_processing_var,0)),
5,sum(nvl(tl_overhead_in,0)-nvl(tl_overhead_out,0)-nvl(tl_overhead_var,0)))/i_txn_qty,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
CST_COST_ELEMENTS CCE,
WIP_PERIOD_BALANCES WPB
WHERE
WPB.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
WPB.ORGANIZATION_ID = I_ORG_ID AND
CCE.COST_ELEMENT_ID <> 2
GROUP BY CCE.COST_ELEMENT_ID, WPB.WIP_ENTITY_ID
HAVING
decode(cce.cost_element_id,
1,sum(0 - nvl(tl_material_out,0)-nvl(tl_material_var,0)),
2,sum(0 - nvl(tl_material_overhead_out,0)-nvl(tl_material_overhead_var,0)),
3,sum(nvl(tl_resource_in,0)-nvl(tl_resource_out,0)-nvl(tl_resource_var,0)),
4,sum(nvl(tl_outside_processing_in,0)-nvl(tl_outside_processing_out,0)-nvl(tl_outside_processing_var,0)),
5,sum(nvl(tl_overhead_in,0)-nvl(tl_overhead_out,0)-nvl(tl_overhead_var,0))) > 0;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
cce.cost_element_id,
2,
decode(cce.cost_element_id,
1,sum(nvl(pl_material_in,0) - nvl(pl_material_out,0)-nvl(pl_material_var,0)),
2,sum(nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)-nvl(pl_material_overhead_var,0)),
3,sum(nvl(pl_resource_in,0)-nvl(pl_resource_out,0)-nvl(pl_resource_var,0)),
4,sum(nvl(pl_outside_processing_in,0)-nvl(pl_outside_processing_out,0)-nvl(pl_outside_processing_var,0)),
5,sum(nvl(pl_overhead_in,0)-nvl(pl_overhead_out,0)-nvl(pl_overhead_var,0)))/i_txn_qty,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
CST_COST_ELEMENTS CCE,
WIP_PERIOD_BALANCES WPB
WHERE
WPB.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
WPB.ORGANIZATION_ID = I_ORG_ID
GROUP BY CCE.COST_ELEMENT_ID, WPB.WIP_ENTITY_ID
HAVING
decode(cce.cost_element_id,
1,sum(nvl(pl_material_in,0) - nvl(pl_material_out,0)-nvl(pl_material_var,0)),
2,sum(nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)-nvl(pl_material_overhead_var,0)),
3,sum(nvl(pl_resource_in,0)-nvl(pl_resource_out,0)-nvl(pl_resource_var,0)),
4,sum(nvl(pl_outside_processing_in,0)-nvl(pl_outside_processing_out,0)-nvl(pl_outside_processing_var,0)),
5,sum(nvl(pl_overhead_in,0)-nvl(pl_overhead_out,0)-nvl(pl_overhead_var,0))) > 0;
SELECT nvl(sum(primary_quantity),0)
INTO l_future_issued_qty
FROM mtl_material_transactions
WHERE organization_id = wro_rec.organization_id
AND inventory_item_id = wro_rec.inventory_item_id
AND operation_seq_num = wro_rec.operation_seq_num
AND transaction_source_id = wro_rec.wip_entity_id
AND ( (transaction_date > i_txn_date) or
(transaction_date = i_txn_date and transaction_id > i_trx_id) )
AND costed_flag IS NOT NULL
AND nvl(completion_transaction_id,-999) <>
( Select nvl(completion_transaction_id,-999)
from mtl_material_transactions
where transaction_id = i_trx_id);
UPDATE wip_requirement_operations w
SET
relieved_matl_completion_qty =
(SELECT
nvl(quantity_issued,0)-
nvl(relieved_matl_final_comp_qty,0)-
nvl(relieved_matl_scrap_quantity,0)
+ l_future_issued_qty
FROM wip_requirement_operations w2
WHERE
w.wip_entity_id = w2.wip_entity_id AND
w.organization_id = w2.organization_id AND
w.inventory_item_id = w2.inventory_item_id AND
w.operation_seq_num = w2.operation_seq_num
)
WHERE
w.wip_entity_id = i_wip_entity_id AND
w.organization_id = i_org_id AND
w.inventory_item_id = wro_rec.inventory_item_id AND
w.operation_seq_num = wro_rec.operation_seq_num
AND exists (
SELECT 'x'
FROM wip_req_operation_cost_details wrocd
WHERE wrocd.wip_entity_id = w.wip_entity_id
AND wrocd.organization_id = w.organization_id
GROUP BY
wrocd.wip_entity_id,
wrocd.organization_id,
wrocd.cost_element_id
HAVING sum(nvl(applied_matl_value,0) -
nvl(relieved_matl_completion_value,0) -
nvl(relieved_variance_value,0) -
nvl(relieved_matl_scrap_value,0)) >= 0
);
UPDATE wip_req_operation_cost_details w
SET relieved_matl_completion_value =
nvl(applied_matl_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0),
temp_relieved_value =
nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)
WHERE w.wip_entity_id = i_wip_entity_id AND
w.cost_element_id = l_cost_element;
UPDATE wip_operation_resources w
SET
(relieved_res_completion_units,
relieved_res_completion_value,
temp_relieved_value
) = (
SELECT
---
--- relieved_res_completion_units
---
nvl(applied_resource_units,0)-
nvl(relieved_res_final_comp_units,0)-
nvl(relieved_res_scrap_units,0),
---
--- relieved_res_completion_value
---
nvl(applied_resource_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0),
---
--- temp_relieved_value
---
nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)
FROM wip_operation_resources w2
WHERE
w.wip_entity_id = w2.wip_entity_id and
w.organization_id = w2.organization_id and
w.operation_seq_num = w2.operation_seq_num and
w.resource_seq_num = w2.resource_seq_num
)
WHERE w.wip_entity_id = i_wip_entity_id
AND w.organization_id = i_org_id
AND EXISTS
(SELECT null
FROM wip_operation_resources wor,
bom_resources br
WHERE wor.wip_entity_id = i_wip_entity_id
AND wor.organization_id = i_org_id
AND wor.organization_id = br.organization_id
AND wor.resource_id = br.resource_id
AND EXISTS
(SELECT null
FROM wip_operation_resources w3,
bom_resources br3
WHERE w3.wip_entity_id = i_wip_entity_id
AND w3.organization_id = i_org_id
AND w3.resource_seq_num = w.resource_seq_num
AND w3.operation_seq_num = w.operation_seq_num
AND w3.resource_id = br3.resource_id
AND w3.organization_id = br3.organization_id
AND br3.cost_element_id = br.cost_element_id)
GROUP BY br.cost_element_id
HAVING sum(nvl(applied_resource_value,0) -
nvl(relieved_res_completion_value,0) -
nvl(relieved_variance_value,0) -
nvl(relieved_res_scrap_value,0)) >= 0);
UPDATE wip_operation_overheads w
set (relieved_ovhd_completion_units,
relieved_ovhd_completion_value,
temp_relieved_value) =
(SELECT
---
--- relieved_ovhd_completion_units
---
nvl(applied_ovhd_units,0)-
nvl(relieved_ovhd_scrap_units,0) -
nvl(relieved_ovhd_final_comp_units,0),
---
--- relieved_ovhd_completion_value
---
nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_scrap_value,0) -
nvl(relieved_variance_value,0),
---
--- temp_relieved_value
---
nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)
FROM
wip_operation_overheads w2
where
w.wip_entity_id = w2.wip_entity_id AND
w.organization_id = w2.organization_id AND
w.operation_seq_num = w2.operation_seq_num AND
w.resource_seq_num = w2.resource_seq_num AND
w.overhead_id = w2.overhead_id AND
w.basis_type = w2.basis_type
)
WHERE
w.wip_entity_id = i_wip_entity_id AND
w.organization_id = i_org_id
AND exists (
SELECT 'x'
FROM wip_operation_overheads woo
WHERE woo.wip_entity_id = w.wip_entity_id
AND woo.organization_id = w.organization_id
HAVING sum(nvl(applied_ovhd_value,0) -
nvl(relieved_ovhd_completion_value,0) -
nvl(relieved_variance_value,0) -
nvl(relieved_ovhd_scrap_value,0)) >= 0
);
l_insert_ind := 1;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
COST_ELEMENT_ID,
LEVEL_TYPE,
SUM(ITEM_COST),
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
CST_ITEM_COST_DETAILS
WHERE
INVENTORY_ITEM_ID = I_INV_ITEM_ID AND
ORGANIZATION_ID = I_ORG_ID AND
COST_TYPE_ID = L_C_COST_TYPE_ID AND
NOT (COST_ELEMENT_ID = 2 AND
LEVEL_TYPE = 1)
GROUP BY COST_ELEMENT_ID,LEVEL_TYPE
HAVING SUM(ITEM_COST) <> 0;
l_insert_ind := 1;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
COST_ELEMENT_ID,
LEVEL_TYPE,
ITEM_COST,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
CST_LAYER_COST_DETAILS
WHERE
LAYER_ID = i_layer_id AND
NOT (COST_ELEMENT_ID = 2 AND
LEVEL_TYPE = 1);
INSERT INTO WIP_REQ_OPERATION_COST_DETAILS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
APPLIED_MATL_VALUE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_wip_entity_id,
wro.operation_seq_num,
i_org_id,
wro.inventory_item_id,
clcd.cost_element_id,
0,
i_user_id,
SYSDATE,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_id,
i_prog_appl_id,
SYSDATE
from
WIP_REQUIREMENT_OPERATIONS WRO,
CST_LAYER_COST_DETAILS CLCD,
CST_QUANTITY_LAYERS CQL
WHERE
WRO.WIP_ENTITY_ID = i_wip_entity_id AND
WRO.INVENTORY_ITEM_ID = CQL.INVENTORY_ITEM_ID AND
WRO.ORGANIZATION_ID = CQL.ORGANIZATION_ID AND
CQL.COST_GROUP_ID = I_COST_GROUP_ID AND
CQL.LAYER_ID = CLCD.LAYER_ID AND
not EXISTS
(SELECT
'X'
FROM
WIP_REQ_OPERATION_COST_DETAILS WROCD
WHERE
WROCD.WIP_ENTITY_ID = i_wip_entity_id AND
WROCD.INVENTORY_ITEM_ID= WRO.INVENTORY_ITEM_ID AND
WROCD.OPERATION_SEQ_NUM= WRO.OPERATION_SEQ_NUM AND
WROCD.COST_ELEMENT_ID = CLCD.COST_ELEMENT_ID)
GROUP BY CLCD.COST_ELEMENT_ID,wro.operation_seq_num,
wro.inventory_item_id;
SELECT nvl(include_component_yield, 1)
INTO l_include_comp_yield
FROM wip_parameters
WHERE organization_id = i_org_id;
SELECT nvl(sum(primary_quantity),0)
INTO l_future_issued_qty
FROM mtl_material_transactions
WHERE organization_id = wro_rec.organization_id
AND inventory_item_id = wro_rec.inventory_item_id
AND operation_seq_num = wro_rec.operation_seq_num
AND transaction_source_id = wro_rec.wip_entity_id
/* Bug 3715567: use txn_date to determine the future issued qty */
AND ( (transaction_date > i_txn_date) or
(transaction_date = i_txn_date and transaction_id > i_trx_id) )
AND costed_flag IS NOT NULL
/* Applied nvl for bug 2391936 */
AND nvl(completion_transaction_id,-999) <>
( Select nvl(completion_transaction_id,-999)
from mtl_material_transactions
where transaction_id = i_trx_id);
UPDATE WIP_REQ_OPERATION_COST_DETAILS w1
SET (temp_relieved_value,
relieved_matl_completion_value) =
(SELECT
decode(SIGN(nvl(wro.quantity_issued,0)-
nvl(wro.relieved_matl_completion_qty,0)-
nvl(wro.relieved_matl_final_comp_qty,0)-
nvl(wro.relieved_matl_scrap_quantity,0)-
/* LBM project Changes */
i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/l_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1)) + l_future_issued_qty), /* Added l_future_issued_qty for bug 4259782 */
SIGN(wro.quantity_per_assembly),
/* LBM project Changes */
i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/l_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))*
decode(SIGN(nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)),
/* Bug 3479419: AVTR = 0 Start*/
0, 0,
/* Bug 3479419: AVTR = 0 End*/
SIGN(wro.quantity_per_assembly),
( nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0))/
(wro.quantity_issued-
nvl(wro.relieved_matl_completion_qty,0)-
nvl(wro.relieved_matl_final_comp_qty,0)-
nvl(wro.relieved_matl_scrap_quantity,0)+
l_future_issued_qty), /* Fix for bug 2158763 */
nvl(decode(cost_element_id,
1,cql.material_cost,
2,cql.material_overhead_cost,
3,cql.resource_cost,
4,cql.outside_processing_cost,
5,cql.overhead_cost),0)),
0,
decode(SIGN(nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)),
/* Bug 3479419: AVTR = 0 Start*/
0, 0,
/* Bug 3479419: AVTR = 0 End*/
SIGN(wro.quantity_per_assembly),
(nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)),
/* LBM project Changes */
i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/l_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))*
nvl(decode(cost_element_id,
1,cql.material_cost,
2,cql.material_overhead_cost,
3,cql.resource_cost,
4,cql.outside_processing_cost,
5,cql.overhead_cost),0)),
-1*SIGN(wro.quantity_per_assembly),
decode(SIGN(nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)),
/* Bug 3479419: AVTR = 0 Start*/
/* LBM project Changes */
0, (i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/l_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))-
(wro.quantity_issued -
nvl(wro.relieved_matl_completion_qty,0) -
nvl(wro.relieved_matl_final_comp_qty,0) -
nvl(wro.relieved_matl_scrap_quantity,0) +
l_future_issued_qty))* /* Added l_future_issued_qty for bug 4259782 */
nvl(decode(cost_element_id,
1,cql.material_cost,
2,cql.material_overhead_cost,
3,cql.resource_cost,
4,cql.outside_processing_cost,
5,cql.overhead_cost),0),
/* Bug 3479419: AVTR = 0 End*/
SIGN(wro.quantity_per_assembly),
(nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)+
/* LBM project Changes */
(i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/l_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))-
(wro.quantity_issued -
nvl(wro.relieved_matl_completion_qty,0) -
nvl(wro.relieved_matl_final_comp_qty,0) -
nvl(wro.relieved_matl_scrap_quantity,0) +
l_future_issued_qty))* /* Added l_future_issued_qty for bug 4259782 */
nvl(decode(cost_element_id,
1,cql.material_cost,
2,cql.material_overhead_cost,
3,cql.resource_cost,
4,cql.outside_processing_cost,
5,cql.overhead_cost),0)),
/* LBM project Changes */
i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/l_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))*
nvl(decode(cost_element_id,
1,cql.material_cost,
2,cql.material_overhead_cost,
3,cql.resource_cost,
4,cql.outside_processing_cost,
5,cql.overhead_cost),0))),
nvl(w1.relieved_matl_completion_value,0)+
/* LBM project Changes */
decode(SIGN(nvl(wro.quantity_issued,0)-
nvl(wro.relieved_matl_completion_qty,0)-
nvl(wro.relieved_matl_final_comp_qty,0)-
nvl(wro.relieved_matl_scrap_quantity,0)-
/* LBM project Changes */
i_txn_qty*(decode(wro.basis_type, 2, wro.quantity_per_assembly/l_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1)) + l_future_issued_qty), /* Added l_future_issued_qty for bug 4259782 */
SIGN(wro.quantity_per_assembly),
/* LBM project Changes */
i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/l_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))*
decode(SIGN(nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)),
/* Bug 3479419: AVTR = 0 Start*/
0, 0,
/* Bug 3479419: AVTR = 0 End*/
SIGN(wro.quantity_per_assembly),
( nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0))
/(wro.quantity_issued-
nvl(wro.relieved_matl_completion_qty,0)-
nvl(wro.relieved_matl_final_comp_qty,0)-
nvl(wro.RELIEVED_MATL_SCRAP_QUANTITY,0)+
l_future_issued_qty), /* Fix for bug 2158763 */
nvl(decode(cost_element_id,
1,cql.material_cost,
2,cql.material_overhead_cost,
3,cql.resource_cost,
4,cql.outside_processing_cost,
5,cql.overhead_cost),0)),
0,
decode(SIGN(nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)),
/* Bug 3479419: AVTR = 0 Start*/
0, 0,
/* Bug 3479419: AVTR = 0 End*/
SIGN(wro.quantity_per_assembly),
(nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)),
/* LBM project Changes */
i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/l_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))*
nvl(decode(cost_element_id,
1,cql.material_cost,
2,cql.material_overhead_cost,
3,cql.resource_cost,
4,cql.outside_processing_cost,
5,cql.overhead_cost),0)),
-1*SIGN(wro.quantity_per_assembly),
decode(SIGN(nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)),
/* Bug 3479419: AVTR = 0 Start*/
/* LBM project Changes */
0, (i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/l_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1)) -
(wro.quantity_issued -
nvl(wro.relieved_matl_completion_qty,0) -
nvl(wro.relieved_matl_final_comp_qty,0) -
nvl(wro.relieved_matl_scrap_quantity,0) +
l_future_issued_qty))* /* Added l_future_issued_qty for bug 4259782 */
nvl(decode(cost_element_id,
1,cql.material_cost,
2,cql.material_overhead_cost,
3,cql.resource_cost,
4,cql.outside_processing_cost,
5,cql.overhead_cost),0),
/* Bug 3479419: AVTR = 0 End*/
SIGN(wro.quantity_per_assembly),
(nvl(applied_matl_value,0)-
nvl(relieved_matl_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_matl_scrap_value,0)+
/* LBM project Changes */
(i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/l_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))-
(wro.quantity_issued -
nvl(wro.relieved_matl_completion_qty,0) -
nvl(wro.relieved_matl_final_comp_qty,0) -
nvl(wro.relieved_matl_scrap_quantity,0) +
l_future_issued_qty))* /* Added l_future_issued_qty for bug 4259782 */
nvl(decode(cost_element_id,
1,cql.material_cost,
2,cql.material_overhead_cost,
3,cql.resource_cost,
4,cql.outside_processing_cost,
5,cql.overhead_cost),0)),
/* LBM project Changes */
i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/l_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))*
nvl(decode(cost_element_id,
1,cql.material_cost,
2,cql.material_overhead_cost,
3,cql.resource_cost,
4,cql.outside_processing_cost,
5,cql.overhead_cost),0)))
FROM
wip_req_operation_cost_details w2,
wip_requirement_operations wro,
cst_quantity_layers cql
WHERE
w2.wip_entity_id = w1.wip_entity_id AND
w2.organization_id = w1.organization_id AND
w2.inventory_item_id = w1.inventory_item_id AND
w2.operation_seq_num = w1.operation_seq_num AND
w2.cost_element_id = w1.cost_element_id AND
w2.wip_entity_id = wro.wip_entity_id AND
w2.organization_id = wro.organization_id AND
w2.inventory_item_id = wro.inventory_item_id AND
w2.operation_seq_num = wro.operation_seq_num AND
i_cost_group_id = cql.cost_group_id(+) AND
wro.inventory_item_id = cql.inventory_item_id(+) AND
wro.organization_id = cql.organization_id(+))
WHERE
w1.wip_entity_id = wro_rec.wip_entity_id AND
w1.organization_id = wro_rec.organization_id AND
w1.inventory_item_id = wro_rec.inventory_item_id AND
w1.operation_seq_num = wro_rec.operation_seq_num;
| Qty must be updated after value ...
|--------------------------------------------------*/
stmt_num := 270;
UPDATE wip_requirement_operations w1
SET
relieved_matl_completion_qty =
(SELECT
nvl(w1.relieved_matl_completion_qty,0) +
/* LBM project Changes */
i_txn_qty*(decode(basis_type, 2,
quantity_per_assembly/l_lot_size,
quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(component_yield_factor,1),
1))
FROM
wip_requirement_operations w2
WHERE
w1.wip_entity_id = w2.wip_entity_id AND
w1.organization_id = w2.organization_id AND
w1.inventory_item_id = w2.inventory_item_id AND
w1.operation_seq_num = w2.operation_seq_num)
WHERE
--
-- Exclude bulk, supplier, phantom
--
w1.wip_supply_type not in (4,5,6) AND
w1.wip_entity_id = i_wip_entity_id AND
w1.organization_id = i_org_id AND
w1.quantity_per_assembly <> 0;
UPDATE wip_operation_resources w1
SET
(relieved_res_completion_units,
temp_relieved_value,
relieved_res_completion_value) =
(SELECT
nvl(w1.relieved_res_completion_units,0) +
decode(sign(applied_resource_units -
nvl(relieved_res_completion_units,0)-
nvl(relieved_res_final_comp_units,0)-
nvl(relieved_res_scrap_units,0)),
1,
(applied_resource_units -
nvl(relieved_res_completion_units,0)-
nvl(relieved_res_final_comp_units,0)-
nvl(relieved_res_scrap_units,0))*
--
-- new to solve divided by zero and over relieved
-- when txn_qty/completed - prior_completion - prior_scrap
-- is greater than or equal to one, set it to one
-- ie. flush out 1*value remain in the job 1/30/98
--
decode(sign(i_txn_qty - (cocd.quantity_completed -
nvl(prior_completion_quantity,0) -
nvl(prior_scrap_quantity,0))),
-1,i_txn_qty/(cocd.quantity_completed -
nvl(prior_completion_quantity,0) -
nvl(prior_scrap_quantity,0)),
1),
0),
decode(sign(applied_resource_value -
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)),
1,
(applied_resource_value -
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0))*
--
-- new to solve divided by zero and over relieved
--
decode(sign(i_txn_qty - (cocd.quantity_completed -
nvl(prior_completion_quantity,0) -
nvl(prior_scrap_quantity,0))),
-1,i_txn_qty/(cocd.quantity_completed -
nvl(prior_completion_quantity,0) -
nvl(prior_scrap_quantity,0)),
1),
0),
nvl(w1.relieved_res_completion_value,0) +
decode(sign(applied_resource_value -
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)),
1,
(applied_resource_value -
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0))*
--
-- new to solve divided by zero and over relieved
--
decode(sign(i_txn_qty - (cocd.quantity_completed -
nvl(prior_completion_quantity,0) -
nvl(prior_scrap_quantity,0))),
-1,i_txn_qty/(cocd.quantity_completed -
nvl(prior_completion_quantity,0) -
nvl(prior_scrap_quantity,0)),
1),
0)
FROM
wip_operation_resources w2,
cst_comp_snapshot cocd
WHERE
w1.wip_entity_id = w2.wip_entity_id AND
w1.operation_seq_num = w2.operation_seq_num AND
w1.resource_seq_num = w2.resource_seq_num AND
w1.organization_id = w2.organization_id AND
w2.wip_entity_id = cocd.wip_entity_id AND -- Added for FP: bug#4608231
w2.operation_seq_num = cocd.operation_seq_num AND
cocd.new_operation_flag = 2 AND
cocd.transaction_id = i_trx_id)
WHERE
w1.wip_entity_id = i_wip_entity_id AND
w1.organization_id = i_org_id;
UPDATE wip_operation_overheads w1
SET
(relieved_ovhd_completion_units,
temp_relieved_value,
relieved_ovhd_completion_value) =
(SELECT
NVL(w1.relieved_ovhd_completion_units,0) +
decode(sign(applied_ovhd_units -
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)),
1,
(applied_ovhd_units -
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0))*
--
-- new to solve divided by zero and over relieved
--
decode(sign(i_txn_qty - (cocd.quantity_completed -
nvl(prior_completion_quantity,0) -
nvl(prior_scrap_quantity,0))),
-1,i_txn_qty/(cocd.quantity_completed -
nvl(prior_completion_quantity,0) -
nvl(prior_scrap_quantity,0)),
1),
0),
decode(sign(applied_ovhd_value -
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
1,
(applied_ovhd_value -
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0))*
--
-- new to solve divided by zero and over relieved
--
decode(sign(i_txn_qty - (cocd.quantity_completed -
nvl(prior_completion_quantity,0) -
nvl(prior_scrap_quantity,0))),
-1,i_txn_qty/(cocd.quantity_completed -
nvl(prior_completion_quantity,0) -
nvl(prior_scrap_quantity,0)),
1),
0),
nvl(w1.relieved_ovhd_completion_value,0) +
decode(sign(applied_ovhd_value -
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
1,
(applied_ovhd_value -
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0))*
--
-- new to solve divided by zero and over relieved
--
decode(sign(i_txn_qty - (cocd.quantity_completed -
nvl(prior_completion_quantity,0) -
nvl(prior_scrap_quantity,0))),
-1,i_txn_qty/(cocd.quantity_completed -
nvl(prior_completion_quantity,0) -
nvl(prior_scrap_quantity,0)),
1),
0)
FROM
wip_operation_overheads w2,
cst_comp_snapshot cocd
WHERE
w1.wip_entity_id = w2.wip_entity_id AND
w1.operation_seq_num = w2.operation_seq_num AND
w1.resource_seq_num = w2.resource_seq_num AND
w1.overhead_id = w2.overhead_id AND
w1.organization_id = w2.organization_id AND
w2.wip_entity_id = cocd.wip_entity_id AND -- Added for FP: bug#4608231
w1.basis_type = w2.basis_type AND
w2.operation_seq_num = cocd.operation_seq_num AND
cocd.new_operation_flag = 2 AND
cocd.transaction_id = i_trx_id)
WHERE
w1.wip_entity_id = i_wip_entity_id AND
w1.organization_id = i_org_id;
UPDATE wip_operation_resources w1
SET
(relieved_res_completion_units,
temp_relieved_value,
relieved_res_completion_value) =
(SELECT
nvl(w1.relieved_res_completion_units,0)+
decode(basis_type,
1,i_txn_qty*usage_rate_or_amount,
2,i_txn_qty*usage_rate_or_amount/l_lot_size,
i_txn_qty*usage_rate_or_amount),
decode(SIGN(applied_resource_units-
nvl(relieved_res_completion_units,0)-
nvl(relieved_res_final_comp_units,0)-
nvl(relieved_res_scrap_units,0)-
i_txn_qty*decode(basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount)),
SIGN(usage_rate_or_amount),
i_txn_qty*decode(basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount)*
decode(SIGN(nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)),
SIGN(usage_rate_or_amount),
decode(basis_type,
1,((nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0))
/(applied_resource_units-
nvl(relieved_res_completion_units,0)-
nvl(relieved_res_final_comp_units,0)-
nvl(relieved_res_scrap_units,0))),
2,nvl(applied_resource_value,0)/
decode(applied_resource_units,
0,1,applied_resource_units),
((nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0))
/(applied_resource_units-
nvl(relieved_res_completion_units,0)-
nvl(relieved_res_final_comp_units,0)-
nvl(relieved_res_scrap_units,0)))),
crc.resource_rate),
0,
decode(SIGN(nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)),
SIGN(usage_rate_or_amount),
(nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)),
i_txn_qty*decode(basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount)*
crc.resource_rate),
-1*SIGN(usage_rate_or_amount),
decode(SIGN(nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)),
SIGN(usage_rate_or_amount),
(nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)+
(i_txn_qty*
decode(basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount) -
(applied_resource_units -
nvl(relieved_res_completion_units,0) -
nvl(relieved_res_final_comp_units,0) -
nvl(relieved_res_scrap_units,0)))*
crc.resource_rate),
i_txn_qty*
decode(basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount)*
crc.resource_rate)),
nvl(w1.relieved_res_completion_value,0) +
decode(SIGN(applied_resource_units-
nvl(relieved_res_completion_units,0)-
nvl(relieved_res_final_comp_units,0)-
nvl(relieved_res_scrap_units,0)-
i_txn_qty*decode(basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount)),
SIGN(usage_rate_or_amount),
i_txn_qty*decode(basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount)*
decode(SIGN(nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)),
SIGN(usage_rate_or_amount),
decode(basis_type,
1,((nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0))
/(applied_resource_units-
nvl(relieved_res_completion_units,0)-
nvl(relieved_res_final_comp_units,0)-
nvl(relieved_res_scrap_units,0))),
2,nvl(applied_resource_value,0)/
decode(applied_resource_units,
0,1,applied_resource_units),
((nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0))
/(applied_resource_units-
nvl(relieved_res_completion_units,0)-
nvl(relieved_res_final_comp_units,0)-
nvl(relieved_res_scrap_units,0)))),
crc.resource_rate),
0,
decode(SIGN(nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)),
SIGN(usage_rate_or_amount),
(nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)),
i_txn_qty*decode(basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount)*
crc.resource_rate),
-1*SIGN(usage_rate_or_amount),
decode(SIGN(nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)),
SIGN(usage_rate_or_amount),
(nvl(applied_resource_value,0)-
nvl(relieved_res_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_res_scrap_value,0)+
(i_txn_qty*
decode(basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount) -
(applied_resource_units -
nvl(relieved_res_completion_units,0) -
nvl(relieved_res_final_comp_units,0) -
nvl(relieved_res_scrap_units,0)))*
crc.resource_rate),
i_txn_qty*
decode(basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount)*
crc.resource_rate))
FROM
wip_operation_resources w2,
cst_resource_costs crc
WHERE
w2.wip_entity_id = w1.wip_entity_id AND
w2.operation_seq_num = w1.operation_seq_num AND
w2.resource_seq_num = w1.resource_seq_num AND
w2.organization_id = w2.organization_id AND
w2.resource_id = crc.resource_id AND
w2.organization_id = crc.organization_id AND
crc.cost_type_id = i_res_cost_type_id)
WHERE
w1.wip_entity_id = i_wip_entity_id AND
w1.organization_id = i_org_id AND
w1.usage_rate_or_amount <> 0;
INSERT INTO WIP_OPERATION_OVERHEADS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
OVERHEAD_ID,
BASIS_TYPE,
APPLIED_OVHD_UNITS,
APPLIED_OVHD_VALUE,
RELIEVED_OVHD_COMPLETION_UNITS,
RELIEVED_OVHD_SCRAP_UNITS,
RELIEVED_OVHD_COMPLETION_VALUE,
RELIEVED_OVHD_SCRAP_VALUE,
TEMP_RELIEVED_VALUE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE)
SELECT
i_wip_entity_id,
wo.operation_seq_num,
-1,
i_org_id,
cdo.overhead_id,
cdo.basis_type,
0,
0,
0,
0,
0,
0,
0,
-1,
SYSDATE,
-1,
-1,
-1,
-1,
-1,
SYSDATE,
SYSDATE
FROM
WIP_OPERATIONS WO,
CST_DEPARTMENT_OVERHEADS CDO
WHERE
WO.WIP_ENTITY_ID = i_wip_entity_id AND
WO.DEPARTMENT_ID = CDO.DEPARTMENT_ID AND
CDO.COST_TYPE_ID = i_res_cost_type_id AND
CDO.BASIS_TYPE IN (1,2) AND
NOT EXISTS
(SELECT 'X'
FROM
WIP_OPERATION_OVERHEADS WOO
where
WOO.WIP_ENTITY_ID = i_wip_entity_id AND
WOO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM AND
WOO.OVERHEAD_ID = CDO.OVERHEAD_ID AND
WOO.BASIS_TYPE = CDO.BASIS_TYPE AND
WOO.RESOURCE_SEQ_NUM = -1);
UPDATE wip_operation_overheads w1
SET
(relieved_ovhd_completion_units,
temp_relieved_value,
relieved_ovhd_completion_value) =
(SELECT
nvl(w1.relieved_ovhd_completion_units,0)+
decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size),
decode(SIGN(nvl(w2.applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)-
decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size)),
1,
decode(SIGN(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
1,
decode(w2.basis_type,
2,nvl(applied_ovhd_value,0),
(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0))
/(nvl(applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)))*
decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size),
cdo.rate_or_amount*
decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size)),
0,
decode(SIGN(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
1,
(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
cdo.rate_or_amount*
decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size)),
-1,
decode(SIGN(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
1,
(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)+
(decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size)-
(nvl(w2.applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)))*
cdo.rate_or_amount),
cdo.rate_or_amount*
decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size))),
nvl(w1.relieved_ovhd_completion_value,0) +
decode(SIGN(nvl(w2.applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)-
decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size)),
1,
decode(SIGN(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
1,
decode(w2.basis_type,
2,nvl(applied_ovhd_value,0),
(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0))
/(nvl(applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)))*
decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size),
cdo.rate_or_amount*
decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size)),
0,
decode(SIGN(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
1,
(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
cdo.rate_or_amount*
decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size)),
-1,
decode(SIGN(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
1,
(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0) +
(decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size)-
(nvl(w2.applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)))*
cdo.rate_or_amount),
cdo.rate_or_amount*
decode(w2.basis_type,
1,i_txn_qty,
2,i_txn_qty/l_lot_size)))
FROM
wip_operation_overheads w2,
cst_department_overheads cdo,
wip_operations wo
WHERE
w2.wip_entity_id = w1.wip_entity_id AND
w2.organization_id = w1.organization_id AND
w2.operation_seq_num = w1.operation_seq_num AND
w2.overhead_id = w1.overhead_id AND
w2.basis_type = w1.basis_type AND
w2.wip_entity_id = wo.wip_entity_id AND
w2.organization_id = wo.organization_id AND
w2.operation_seq_num = wo.operation_seq_num AND
cdo.department_id = wo.department_id AND
cdo.overhead_id = w2.overhead_id AND
cdo.basis_type = w2.basis_type AND
cdo.cost_type_id = i_res_cost_type_id)
WHERE
w1.wip_entity_id = i_wip_entity_id AND
w1.organization_id = i_org_id AND
w1.basis_type IN (1,2) AND
EXISTS
(
SELECT 'X'
FROM
cst_department_overheads cdo2,
wip_operations wo2
WHERE
wo2.wip_entity_id = w1.wip_entity_id AND
wo2.organization_id = w1.organization_id AND
wo2.operation_seq_num = w1.operation_seq_num AND
wo2.department_id = cdo2.department_id AND
w1.overhead_id = cdo2.overhead_id AND
w1.basis_type = cdo2.basis_type AND
cdo2.cost_type_id = i_res_cost_type_id);
INSERT INTO WIP_OPERATION_OVERHEADS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
OVERHEAD_ID,
BASIS_TYPE,
APPLIED_OVHD_UNITS,
APPLIED_OVHD_VALUE,
RELIEVED_OVHD_COMPLETION_UNITS,
RELIEVED_OVHD_SCRAP_UNITS,
RELIEVED_OVHD_COMPLETION_VALUE,
RELIEVED_OVHD_SCRAP_VALUE,
TEMP_RELIEVED_VALUE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
LAST_UPDATE_DATE)
SELECT
i_wip_entity_id,
wo.operation_seq_num,
wor.resource_seq_num,
i_org_id,
cdo.overhead_id,
cdo.basis_type,
0,
0,
0,
0,
0,
0,
0,
-1,
SYSDATE,
-1,
-1,
-1,
-1,
-1,
SYSDATE,
SYSDATE
FROM
WIP_OPERATIONS WO,
WIP_OPERATION_RESOURCES WOR,
CST_DEPARTMENT_OVERHEADS CDO,
CST_RESOURCE_OVERHEADS CRO
WHERE
WO.WIP_ENTITY_ID = i_wip_entity_id AND
WO.OPERATION_SEQ_NUM = WOR.OPERATION_SEQ_NUM AND
WO.WIP_ENTITY_ID = WOR.WIP_ENTITY_ID AND
WO.DEPARTMENT_ID = CDO.DEPARTMENT_ID AND
CDO.COST_TYPE_ID = i_res_cost_type_id AND
CDO.BASIS_TYPE IN (3,4) AND
CRO.COST_TYPE_ID = i_res_cost_type_id AND
CRO.RESOURCE_ID = WOR.RESOURCE_ID AND
CRO.OVERHEAD_ID = CDO.OVERHEAD_ID AND
NOT EXISTS
(SELECT 'X'
FROM
WIP_OPERATION_OVERHEADS WOO
WHERE
WOO.WIP_ENTITY_ID = i_wip_entity_id AND
WOO.OPERATION_SEQ_NUM = WO.OPERATION_SEQ_NUM AND
WOO.RESOURCE_SEQ_NUM = WOR.RESOURCE_SEQ_NUM AND
WOO.OVERHEAD_ID = CDO.OVERHEAD_ID AND
WOO.BASIS_TYPE = CDO.BASIS_TYPE);
UPDATE wip_operation_overheads w1
SET
(relieved_ovhd_completion_units,
temp_relieved_value,
relieved_ovhd_completion_value) =
(SELECT
nvl(w1.relieved_ovhd_completion_units,0)+
decode(w2.basis_type,
3,i_txn_qty*decode(wor.basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount),
4,wor.temp_relieved_value),
decode(SIGN(nvl(w2.applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)-
decode(w2.basis_type,
3,i_txn_qty*decode(wor.basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount),
4,wor.temp_relieved_value)),
SIGN(wor.usage_rate_or_amount),
decode(SIGN(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(w2.relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
SIGN(wor.usage_rate_or_amount),
((nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(w2.relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0))
/(nvl(applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)))*
decode(w2.basis_type,
3,i_txn_qty*
decode(wor.basis_type,
1,wor.usage_rate_or_amount,
2,wor.usage_rate_or_amount/l_lot_size),
4,nvl(wor.temp_relieved_value,0)),
nvl(cdo.rate_or_amount,0)*
decode(w2.basis_type,
3,i_txn_qty*
decode(wor.basis_type,
1,wor.usage_rate_or_amount,
2,wor.usage_rate_or_amount/l_lot_size),
4,nvl(wor.temp_relieved_value,0))),
0,
decode(SIGN(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(w2.relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
SIGN(wor.usage_rate_or_amount),
(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(w2.relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
nvl(cdo.rate_or_amount,0)*
decode(w2.basis_type,
3,i_txn_qty*
decode(wor.basis_type,
1,wor.usage_rate_or_amount,
2,wor.usage_rate_or_amount/l_lot_size),
4,nvl(wor.temp_relieved_value,0))),
-1*SIGN(wor.usage_rate_or_amount),
decode(SIGN(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(w2.relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
SIGN(wor.usage_rate_or_amount),
(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(w2.relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)+
(decode(w2.basis_type,
3,i_txn_qty*decode(wor.basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount),
4,wor.temp_relieved_value)-
(nvl(w2.applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)))*
nvl(cdo.rate_or_amount,0)),
nvl(cdo.rate_or_amount,0)*
decode(w2.basis_type,
3,i_txn_qty*
decode(wor.basis_type,
1,wor.usage_rate_or_amount,
2,wor.usage_rate_or_amount/l_lot_size),
4,nvl(wor.temp_relieved_value,0)))),
nvl(w1.relieved_ovhd_completion_value,0) +
decode(SIGN(nvl(w2.applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)-
decode(w2.basis_type,
3,i_txn_qty*decode(wor.basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount),
4,wor.temp_relieved_value)),
SIGN(wor.usage_rate_or_amount),
decode(SIGN(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(w2.relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
SIGN(wor.usage_rate_or_amount),
((nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(w2.relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0))
/(nvl(applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)))*
decode(w2.basis_type,
3,i_txn_qty*
decode(wor.basis_type,
1,wor.usage_rate_or_amount,
2,wor.usage_rate_or_amount/l_lot_size),
4,nvl(wor.temp_relieved_value,0)),
nvl(cdo.rate_or_amount,0)*
decode(w2.basis_type,
3,i_txn_qty*
decode(wor.basis_type,
1,wor.usage_rate_or_amount,
2,wor.usage_rate_or_amount/l_lot_size),
4,nvl(wor.temp_relieved_value,0))),
0,
decode(SIGN(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(w2.relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
SIGN(wor.usage_rate_or_amount),
(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(w2.relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
nvl(cdo.rate_or_amount,0)*
decode(w2.basis_type,
3,i_txn_qty*
decode(wor.basis_type,
1,wor.usage_rate_or_amount,
2,wor.usage_rate_or_amount/l_lot_size),
4,nvl(wor.temp_relieved_value,0))),
-1*SIGN(wor.usage_rate_or_amount),
decode(SIGN(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(w2.relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)),
SIGN(wor.usage_rate_or_amount),
(nvl(applied_ovhd_value,0)-
nvl(relieved_ovhd_completion_value,0)-
nvl(w2.relieved_variance_value,0)-
nvl(relieved_ovhd_scrap_value,0)+
(decode(w2.basis_type,
3,i_txn_qty*decode(wor.basis_type,
1,usage_rate_or_amount,
2,usage_rate_or_amount/l_lot_size,
usage_rate_or_amount),
4,wor.temp_relieved_value)-
(nvl(w2.applied_ovhd_units,0)-
nvl(relieved_ovhd_completion_units,0)-
nvl(relieved_ovhd_final_comp_units,0)-
nvl(relieved_ovhd_scrap_units,0)))*
nvl(cdo.rate_or_amount,0)),
nvl(cdo.rate_or_amount,0)*
decode(w2.basis_type,
3,i_txn_qty*
decode(wor.basis_type,
1,wor.usage_rate_or_amount,
2,wor.usage_rate_or_amount/l_lot_size),
4,nvl(wor.temp_relieved_value,0))))
FROM
wip_operation_overheads w2,
cst_department_overheads cdo,
wip_operations wo,
wip_operation_resources wor,
cst_resource_overheads cro
WHERE
w2.wip_entity_id = w1.wip_entity_id AND
w2.organization_id = w1.organization_id AND
w2.operation_seq_num = w1.operation_seq_num AND
w2.overhead_id = w1.overhead_id AND
w2.basis_type = w1.basis_type AND
w2.resource_seq_num = w1.resource_seq_num AND
w2.wip_entity_id = wo.wip_entity_id AND
w2.organization_id = wo.organization_id AND
w2.operation_seq_num = wo.operation_seq_num AND
w2.wip_entity_id = wor.wip_entity_id AND
w2.organization_id = wor.organization_id AND
w2.operation_seq_num = wor.operation_seq_num AND
w2.resource_seq_num = wor.resource_seq_num AND
wo.department_id = cdo.department_id AND
cdo.overhead_id = w2.overhead_id AND
cdo.basis_type = w2.basis_type AND
cdo.cost_type_id = i_res_cost_type_id AND
cro.overhead_id = cdo.overhead_id AND
cro.resource_id = wor.resource_id AND
cro.cost_type_id = i_res_cost_type_id)
WHERE
w1.wip_entity_id = i_wip_entity_id AND
w1.organization_id = i_org_id AND
w1.basis_type IN (3,4) AND
EXISTS
(
SELECT 'X'
FROM
cst_department_overheads cdo2,
wip_operations wo2,
cst_resource_overheads cro2,
wip_operation_resources wor2
WHERE
w1.wip_entity_id = wo2.wip_entity_id AND
w1.organization_id = wo2.organization_id AND
w1.operation_seq_num = wo2.operation_seq_num AND
w1.wip_entity_id = wor2.wip_entity_id AND
w1.organization_id = wor2.organization_id AND
w1.operation_seq_num = wor2.operation_seq_num AND
w1.resource_seq_num = wor2.resource_seq_num AND
wor2.usage_rate_or_amount <> 0 AND
wo2.department_id = cdo2.department_id AND
cdo2.overhead_id = w1.overhead_id AND
cdo2.basis_type = w1.basis_type AND
cdo2.cost_type_id = i_res_cost_type_id AND
cdo2.overhead_id = cro2.overhead_id AND
cro2.resource_id = wor2.resource_id AND
cro2.cost_type_id = i_res_cost_type_id);
* Insert into mtl_cst_txn_cost_details now that the *
* Costs have been computed ... *
* 3 statements are required --> one each for PL costs *
* , TL Res/OSP costs and TL ovhd costs. *
* Remember - the cst_txn_cost_detail tables stores unit *
* cost - but the wip tables store the value in the *
* temp_relieved_value column - so we have to divide by the *
* txn_qty to arrive at the unit cost. *
************************************************************/
IF(l_insert_ind <> 1) THEN
/*BUG 7346225: For Final completion the MCTCD should be populated from
WPB since this one has rounded values not like WROCD, WOR or WOO and
this is prefered since Final completion should relieve the accounted
value */
IF (i_final_comp_flag='Y') THEN
stmt_num := 350;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
cce.cost_element_id,
1,
decode(cce.cost_element_id,
1,sum(0 - nvl(tl_material_out,0)-nvl(tl_material_var,0)),
2,sum(0 - nvl(tl_material_overhead_out,0)-nvl(tl_material_overhead_var,0)),
3,sum(nvl(tl_resource_in,0)-nvl(tl_resource_out,0)-nvl(tl_resource_var,0)),
4,sum(nvl(tl_outside_processing_in,0)-nvl(tl_outside_processing_out,0)-nvl(tl_outside_processing_var,0)),
5,sum(nvl(tl_overhead_in,0)-nvl(tl_overhead_out,0)-nvl(tl_overhead_var,0)))/i_txn_qty,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
CST_COST_ELEMENTS CCE,
WIP_PERIOD_BALANCES WPB
WHERE
WPB.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
WPB.ORGANIZATION_ID = I_ORG_ID AND
CCE.COST_ELEMENT_ID <> 2
GROUP BY CCE.COST_ELEMENT_ID, WPB.WIP_ENTITY_ID;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
cce.cost_element_id,
2,
decode(cce.cost_element_id,
1,sum(nvl(pl_material_in,0) - nvl(pl_material_out,0)-nvl(pl_material_var,0)),
2,sum(nvl(pl_material_overhead_in,0) - nvl(pl_material_overhead_out,0)-nvl(pl_material_overhead_var,0)),
3,sum(nvl(pl_resource_in,0)-nvl(pl_resource_out,0)-nvl(pl_resource_var,0)),
4,sum(nvl(pl_outside_processing_in,0)-nvl(pl_outside_processing_out,0)-nvl(pl_outside_processing_var,0)),
5,sum(nvl(pl_overhead_in,0)-nvl(pl_overhead_out,0)-nvl(pl_overhead_var,0)))/i_txn_qty,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
CST_COST_ELEMENTS CCE,
WIP_PERIOD_BALANCES WPB
WHERE
WPB.WIP_ENTITY_ID = I_WIP_ENTITY_ID AND
WPB.ORGANIZATION_ID = I_ORG_ID
GROUP BY CCE.COST_ELEMENT_ID, WPB.WIP_ENTITY_ID;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
wrocd.cost_element_id,
2,
sum(nvl(wrocd.temp_relieved_value,0))/i_txn_qty,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
WIP_REQ_OPERATION_COST_DETAILS wrocd
where
WIP_ENTITY_ID = i_wip_entity_id AND
ORGANIZATION_ID = i_org_id
GROUP BY wrocd.cost_element_id
HAVING sum(nvl(wrocd.temp_relieved_value,0)) <> 0;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
br.cost_element_id,
1,
sum(nvl(wor.temp_relieved_value,0))/i_txn_qty,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
BOM_RESOURCES BR,
WIP_OPERATION_RESOURCES WOR
WHERE
WOR.RESOURCE_ID = BR.RESOURCE_ID AND
WOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND
WOR.WIP_ENTITY_ID = i_wip_entity_id AND
WOR.ORGANIZATION_ID = i_org_id
GROUP BY BR.COST_ELEMENT_ID
HAVING sum(nvl(wor.temp_relieved_value,0)) <> 0;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
5,
1,
SUM(nvl(temp_relieved_value,0))/i_txn_qty,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
WIP_OPERATION_OVERHEADS
WHERE
WIP_ENTITY_ID = i_wip_entity_id AND
ORGANIZATION_ID = i_org_id
HAVING
SUM(nvl(temp_relieved_value,0)) <> 0;
SELECT count(*)
INTO l_count
FROM mtl_cst_txn_cost_details mctcd,
mtl_material_transactions mmt
WHERE mctcd.transaction_id = mmt.transaction_id
AND mctcd.transaction_id = i_trx_id
AND mctcd.transaction_cost < 0;
SELECT wip_transactions_s.nextval
INTO l_wcti_txn_id
FROM dual;
INSERT INTO wip_cost_txn_interface
(transaction_id,
acct_period_id,
process_status,
process_phase,
transaction_type,
organization_id,
wip_entity_id,
wip_entity_name,
entity_type,
transaction_date,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT
l_wcti_txn_id,
i_acct_period_id,
2,
3,
7, -- new transaction_type for final completion variance
i_org_id,
i_wip_entity_id,
w.wip_entity_name,
1,
i_txn_date,
sysdate,
i_user_id,
i_login_id,
sysdate,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate
FROM
wip_entities w
WHERE
w.wip_entity_id = i_wip_entity_id
AND w.organization_id = i_org_id;
SELECT set_of_books_id
INTO l_sob_id
/*FROM org_organization_definitions*/
FROM cst_organization_definitions
WHERE organization_id = i_org_id;
SELECT currency_code
INTO l_pri_curr
FROM gl_sets_of_books
WHERE set_of_books_id = l_sob_id;
INSERT INTO wip_transactions
(transaction_id,
acct_period_id,
transaction_type,
organization_id,
wip_entity_id,
transaction_date,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date)
SELECT
wcti.transaction_id,
wcti.acct_period_id,
wcti.transaction_type,
wcti.organization_id,
wcti.wip_entity_id,
wcti.transaction_date,
sysdate,
i_user_id,
i_login_id,
sysdate,
i_user_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate
FROM wip_cost_txn_interface wcti
WHERE transaction_id = i_wcti_txn_id;
INSERT INTO wip_transaction_accounts
(transaction_id, reference_account,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, organization_id,
transaction_date, wip_entity_id,
repetitive_schedule_id, accounting_line_type,
transaction_value, base_transaction_value,
contra_set_id, primary_quantity,
rate_or_amount, basis_type,
resource_id, cost_element_id,
activity_id, currency_code,
currency_conversion_date, currency_conversion_type,
currency_conversion_rate,
request_id, program_application_id,
program_id, program_update_date)
SELECT
i_wcti_txn_id,
decode(mctcd.cost_element_id,
1, wdj.material_account,
2, wdj.material_overhead_account,
3, wdj.resource_account,
4, wdj.outside_processing_account,
5, wdj.overhead_account),
sysdate, i_user_id,
sysdate, i_user_id,
i_login_id, i_org_id,
i_txn_date, i_wip_entity_id,
NULL, 7,
NULL,
decode(c1.minimum_accountable_unit,
NULL, round(-SUM(mctcd.transaction_cost)*i_txn_qty,c1.precision),
round(-SUM(mctcd.transaction_cost)*i_txn_qty/c1.minimum_accountable_unit)
* c1.minimum_accountable_unit),
NULL, NULL,
NULL, NULL,
NULL, mctcd.cost_element_id,
NULL, NULL,
NULL, NULL,
NULL,
i_request_id, i_prog_appl_id,
i_prog_id, sysdate
FROM mtl_cst_txn_cost_details mctcd,
mtl_material_transactions mmt,
wip_discrete_jobs wdj,
fnd_currencies c1
WHERE mctcd.transaction_id = mmt.transaction_id
AND mmt.transaction_source_id = wdj.wip_entity_id
AND mctcd.transaction_id = i_trx_id
AND mctcd.transaction_cost < 0
AND c1.currency_code = l_pri_curr
GROUP BY
decode(mctcd.cost_element_id,
1, wdj.material_account,
2, wdj.material_overhead_account,
3, wdj.resource_account,
4, wdj.outside_processing_account,
5, wdj.overhead_account),
mctcd.cost_element_id,
c1.minimum_accountable_unit,
c1.precision;
INSERT INTO wip_transaction_accounts
(transaction_id, reference_account,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, organization_id,
transaction_date, wip_entity_id,
repetitive_schedule_id, accounting_line_type,
transaction_value, base_transaction_value,
contra_set_id, primary_quantity,
rate_or_amount, basis_type,
resource_id, cost_element_id,
activity_id, currency_code,
currency_conversion_date, currency_conversion_type,
currency_conversion_rate,
request_id, program_application_id,
program_id, program_update_date)
SELECT
i_wcti_txn_id,
wdj.material_variance_account,
sysdate, i_user_id,
sysdate, i_user_id,
i_login_id, i_org_id,
i_txn_date, i_wip_entity_id,
NULL, 8,
NULL,
/* decode(c1.minimum_accountable_unit,
NULL, round(SUM(mctcd.transaction_cost)*i_txn_qty,c1.precision),
round(SUM(mctcd.transaction_cost)*i_txn_qty/c1.minimum_accountable_unit)
* c1.minimum_accountable_unit), */
decode(c1.minimum_accountable_unit,
NULL, SUM(round((mctcd.transaction_cost*i_txn_qty),c1.precision)),
sum(round((mctcd.transaction_cost*i_txn_qty)/c1.minimum_accountable_unit)
* c1.minimum_accountable_unit)),
NULL, NULL,
NULL, NULL,
NULL, 1,
NULL, NULL,
NULL, NULL,
NULL,
i_request_id, i_prog_appl_id,
i_prog_id, sysdate
FROM mtl_cst_txn_cost_details mctcd,
mtl_material_transactions mmt,
wip_discrete_jobs wdj,
fnd_currencies c1
WHERE mctcd.transaction_id = mmt.transaction_id
AND mmt.transaction_source_id = wdj.wip_entity_id
AND mctcd.transaction_id = i_trx_id
AND mctcd.transaction_cost < 0
AND mctcd.level_type = 2
AND c1.currency_code = l_pri_curr
GROUP BY
wdj.material_variance_account,
c1.minimum_accountable_unit,
c1.precision;
INSERT INTO wip_transaction_accounts
(transaction_id, reference_account,
last_update_date, last_updated_by,
creation_date, created_by,
last_update_login, organization_id,
transaction_date, wip_entity_id,
repetitive_schedule_id, accounting_line_type,
transaction_value, base_transaction_value,
contra_set_id, primary_quantity,
rate_or_amount, basis_type,
resource_id, cost_element_id,
activity_id, currency_code,
currency_conversion_date, currency_conversion_type,
currency_conversion_rate,
request_id, program_application_id,
program_id, program_update_date)
SELECT
i_wcti_txn_id,
decode(mctcd.cost_element_id,
3, wdj.resource_variance_account,
4, wdj.outside_proc_variance_account,
5, wdj.overhead_variance_account),
sysdate, i_user_id,
sysdate, i_user_id,
i_login_id, i_org_id,
i_txn_date, i_wip_entity_id,
NULL, 8,
NULL,
decode(c1.minimum_accountable_unit,
NULL, round(mctcd.transaction_cost*i_txn_qty,c1.precision),
round(mctcd.transaction_cost*i_txn_qty/c1.minimum_accountable_unit)
* c1.minimum_accountable_unit),
NULL, NULL,
NULL, NULL,
NULL, mctcd.cost_element_id,
NULL, NULL,
NULL, NULL,
NULL,
i_request_id, i_prog_appl_id,
i_prog_id, sysdate
FROM mtl_cst_txn_cost_details mctcd,
mtl_material_transactions mmt,
wip_discrete_jobs wdj,
fnd_currencies c1
WHERE mctcd.transaction_id = mmt.transaction_id
AND mmt.transaction_source_id = wdj.wip_entity_id
AND mctcd.transaction_id = i_trx_id
AND mctcd.transaction_cost < 0
AND mctcd.level_type = 1
AND mctcd.cost_element_id in (3,4,5)
AND c1.currency_code = l_pri_curr;
UPDATE WIP_TRANSACTION_ACCOUNTS
SET WIP_SUB_LEDGER_ID = CST_WIP_SUB_LEDGER_ID_S.NEXTVAL
WHERE TRANSACTION_ID = i_wcti_txn_id;
DELETE wip_cost_txn_interface
WHERE transaction_id = i_wcti_txn_id;
UPDATE wip_period_balances wpb
SET
(last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
pl_material_var,
pl_material_overhead_var,
pl_resource_var,
pl_outside_processing_var,
pl_overhead_var,
tl_material_var,
tl_material_overhead_var,
tl_resource_var,
tl_outside_processing_var,
tl_overhead_var) =
(SELECT
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_id,
i_prog_appl_id,
sysdate,
pl_material_var + decode(c1.minimum_accountable_unit,
NULL, round(i_txn_qty*sum(decode(level_type,
2,decode(cost_element_id,
1,nvl(transaction_cost,0)
,0),0)),c1.precision),
round((i_txn_qty*sum(decode(level_type,
2,decode(cost_element_id,
1,nvl(transaction_cost,0)
,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
pl_material_overhead_var + decode(c1.minimum_accountable_unit,
NULL, round(i_txn_qty*sum(decode(level_type,
2,decode(cost_element_id,
2,nvl(transaction_cost,0)
,0),0)),c1.precision),
round((i_txn_qty*sum(decode(level_type,
2,decode(cost_element_id,
2,nvl(transaction_cost,0)
,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
pl_resource_var + decode(c1.minimum_accountable_unit,
NULL, round(i_txn_qty*sum(decode(level_type,
2,decode(cost_element_id,
3,nvl(transaction_cost,0)
,0),0)),c1.precision),
round((i_txn_qty*sum(decode(level_type,
2,decode(cost_element_id,
3,nvl(transaction_cost,0)
,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
pl_outside_processing_var + decode(c1.minimum_accountable_unit,
NULL, round(i_txn_qty*sum(decode(level_type,
2,decode(cost_element_id,
4,nvl(transaction_cost,0)
,0),0)),c1.precision),
round((i_txn_qty*sum(decode(level_type,
2,decode(cost_element_id,
4,nvl(transaction_cost,0)
,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
pl_overhead_var + decode(c1.minimum_accountable_unit,
NULL, round(i_txn_qty*sum(decode(level_type,
2,decode(cost_element_id,
5,nvl(transaction_cost,0)
,0),0)),c1.precision),
round((i_txn_qty*sum(decode(level_type,
2,decode(cost_element_id,
5,nvl(transaction_cost,0)
,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
tl_material_var + decode(c1.minimum_accountable_unit,
NULL, round(i_txn_qty* sum(decode(level_type,
1,decode(cost_element_id,
1,nvl(transaction_cost,0)
,0),0)),c1.precision),
round((i_txn_qty* sum(decode(level_type,
1,decode(cost_element_id,
1,nvl(transaction_cost,0)
,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
tl_material_overhead_var + 0, /* The TL MO never gets Cr to the Job*/
tl_resource_var + decode(c1.minimum_accountable_unit,
NULL, round(i_txn_qty* sum(decode(level_type,
1,decode(cost_element_id,
3,nvl(transaction_cost,0)
,0),0)),c1.precision),
round((i_txn_qty* sum(decode(level_type,
1,decode(cost_element_id,
3,nvl(transaction_cost,0)
,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
tl_outside_processing_var + decode(c1.minimum_accountable_unit,
NULL, round(i_txn_qty* sum(decode(level_type,
1,decode(cost_element_id,
4,nvl(transaction_cost,0)
,0),0)),c1.precision),
round((i_txn_qty* sum(decode(level_type,
1,decode(cost_element_id,
4,nvl(transaction_cost,0)
,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit),
tl_overhead_var + decode(c1.minimum_accountable_unit,
NULL, round(i_txn_qty* sum(decode(level_type,
1,decode(cost_element_id,
5,nvl(transaction_cost,0)
,0),0)),c1.precision),
round((i_txn_qty* sum(decode(level_type,
1,decode(cost_element_id,
5,nvl(transaction_cost,0)
,0),0)))/c1.minimum_accountable_unit)*c1.minimum_accountable_unit)
FROM
mtl_cst_txn_cost_details mctcd,
fnd_currencies c1
WHERE transaction_id = i_trx_id
AND transaction_cost < 0
AND c1.currency_code = l_pri_curr
GROUP BY c1.minimum_accountable_unit, c1.precision)
WHERE
wip_entity_id = i_wip_entity_id AND
organization_id = i_org_id AND
acct_period_id = i_acct_period_id;
UPDATE mtl_cst_txn_cost_details
SET transaction_cost = 0
WHERE transaction_cost < 0
AND transaction_id = i_trx_id;
l_insert_ind NUMBER;
* Update temp_relieved_value to zero in all tables *
***************************************************/
stmt_num := 10;
UPDATE WIP_REQ_OPERATION_COST_DETAILS
SET temp_relieved_value = 0
where
WIP_ENTITY_ID = i_wip_entity_id;
UPDATE WIP_OPERATION_RESOURCES
SET temp_relieved_value = 0
where
WIP_ENTITY_ID = i_wip_entity_id;
UPDATE WIP_OPERATION_OVERHEADS
SET temp_relieved_value = 0
where
WIP_ENTITY_ID = i_wip_entity_id;
select wac.completion_cost_source, nvl(wac.cost_type_id,-1),
wdj.start_quantity,nvl(system_option_id,-1)
into l_comp_cost_source,l_c_cost_type_id,i_lot_size,
l_system_option_id
from
wip_accounting_classes wac,
wip_discrete_jobs wdj
where
wdj.wip_entity_id = i_wip_entity_id and
wdj.organization_id = i_org_id and
wdj.class_code = wac.class_code and
wdj.organization_id = wac.organization_id;
l_insert_ind := 0;
SELECT
decode(job_type,
1,decode(bom_revision,
NULL,decode(routing_revision,NULL,-1,1),
1),
3,decode(bom_reference_id,
NULL,decode(routing_reference_id,NULL,-1,1),
1),
1)
into
l_use_val_cost_type
from
WIP_DISCRETE_JOBS
WHERE
WIP_ENTITY_ID = i_wip_entity_id AND
ORGANIZATION_ID = i_org_id;
select count(*)
into l_qty_per_assy
from wip_requirement_operations
where wip_entity_id = i_wip_entity_id
and quantity_per_assembly <>0;
SELECT COUNT(1)
INTO l_qty_per_assy
FROM dual
WHERE EXISTS ( SELECT NULL
FROM wip_requirement_operations wro
WHERE wro.wip_entity_id = i_wip_entity_id
AND wro.quantity_per_assembly <>0
UNION ALL
SELECT NULL
FROM wip_operation_resources wor
WHERE wor.wip_entity_id = i_wip_entity_id
AND wor.usage_rate_or_amount <>0
);
l_insert_ind := 1;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
COST_ELEMENT_ID,
LEVEL_TYPE,
SUM(ITEM_COST),
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
CST_ITEM_COST_DETAILS
WHERE
INVENTORY_ITEM_ID = I_INV_ITEM_ID AND
ORGANIZATION_ID = I_ORG_ID AND
COST_TYPE_ID = L_C_COST_TYPE_ID AND
NOT (COST_ELEMENT_ID = 2 AND
LEVEL_TYPE = 1)
GROUP BY COST_ELEMENT_ID,LEVEL_TYPE
HAVING SUM(ITEM_COST) <> 0;
l_insert_ind := 1;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
COST_ELEMENT_ID,
LEVEL_TYPE,
ITEM_COST,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
CST_LAYER_COST_DETAILS
WHERE
LAYER_ID = i_layer_id AND
NOT (COST_ELEMENT_ID = 2 AND
LEVEL_TYPE = 1);
select count(1)
into l_routing_check
from wip_operations wo
where wo.wip_entity_id = i_wip_entity_id;
UPDATE wip_req_operation_cost_details w1
SET
(temp_relieved_value,
relieved_matl_completion_value) =
(SELECT
--
-- temp_relieved_value
--
decode(SIGN(w2.relieved_matl_completion_value),1,
nvl(W2.relieved_matl_completion_value,0)*
decode(abs(i_txn_qty),
prior_completion_quantity,-1,
i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
prior_completion_quantity)),
0),
---
--- relieved_matl_completion_value
---
nvl(w1.relieved_matl_completion_value,0)+
decode(SIGN(w2.relieved_matl_completion_value),1,
nvl(w2.relieved_matl_completion_value,0)*
decode(abs(i_txn_qty),
prior_completion_quantity,-1,
i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
prior_completion_quantity)),
0)
FROM
wip_req_operation_cost_details w2,
cst_comp_snapshot cocd
WHERE
w1.wip_entity_id = w2.wip_entity_id AND
w1.organization_id = w2.organization_id AND
w1.operation_seq_num = w2.operation_seq_num AND
w1.inventory_item_id = w2.inventory_item_id AND
w1.cost_element_id = w2.cost_element_id AND
w2.wip_entity_id = cocd.wip_entity_id AND
w2.operation_seq_num = cocd.operation_seq_num AND
cocd.new_operation_flag = 2 AND
cocd.transaction_id = i_trx_id
)
WHERE
w1.wip_entity_id = i_wip_entity_id AND
w1.organization_id = i_org_id;
UPDATE wip_requirement_operations w1
SET
relieved_matl_completion_qty =
(SELECT
--
-- relieved_matl_completion_qty
--
nvl(w1.relieved_matl_completion_qty,0)+
decode(SIGN(SUM(nvl(wrocd.relieved_matl_completion_value - wrocd.temp_relieved_value,0))),1,
nvl(w2.relieved_matl_completion_qty,0)*
decode(abs(i_txn_qty),
prior_completion_quantity,-1,
i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
prior_completion_quantity)),
0)
FROM
wip_req_operation_cost_details wrocd,
wip_requirement_operations w2,
cst_comp_snapshot cocd
WHERE
w1.wip_entity_id = w2.wip_entity_id AND
w1.inventory_item_id = w2.inventory_item_id AND
w1.operation_seq_num = w2.operation_seq_num AND
w1.organization_id = w2.organization_id AND
w2.wip_entity_id = wrocd.wip_entity_id AND
w2.organization_id = wrocd.organization_id AND
w2.operation_seq_num = wrocd.operation_seq_num AND
w2.inventory_item_id = wrocd.inventory_item_id AND
w2.wip_entity_id = cocd.wip_entity_id AND
w2.operation_seq_num = cocd.operation_seq_num AND
cocd.new_operation_flag = 2 AND
cocd.transaction_id = i_trx_id
GROUP BY
w2.wip_entity_id,
w2.organization_id,
w2.inventory_item_id,
w2.operation_seq_num,
prior_completion_quantity,
w2.relieved_matl_completion_qty
)
WHERE
w1.wip_entity_id = i_wip_entity_id AND
w1.organization_id = i_org_id;
SELECT nvl(include_component_yield, 1)
INTO l_include_comp_yield
FROM wip_parameters
WHERE organization_id = i_org_id;
UPDATE wip_req_operation_cost_details w1
SET
(temp_relieved_value,
relieved_matl_completion_value) =
(SELECT
---
--- temp_relieved_value
---
DECODE(wro.relieved_matl_completion_qty,0,
0,
NULL,
0,
DECODE(w1.relieved_matl_completion_value,0,
0,
NULL,
0,
DECODE(SIGN(wro.relieved_matl_completion_qty),
SIGN(w1.relieved_matl_completion_value),
DECODE(SIGN(wro.relieved_matl_completion_qty-
/* LBM project Changes */
ABS(i_txn_qty)*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/i_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))),
SIGN(wro.quantity_per_assembly),
/* LBM project Changes */
i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/i_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))*
relieved_matl_completion_value/
wro.relieved_matl_completion_qty,
0,
-1*relieved_matl_completion_value,
DECODE(SIGN(wro.relieved_matl_completion_qty),
SIGN(wro.quantity_per_assembly),
-1*relieved_matl_completion_value,
0)),
0))),
---
--- relieved_matl_completion_value
---
NVL(relieved_matl_completion_value,0)+
DECODE(wro.relieved_matl_completion_qty,0,
0,
NULL,
0,
DECODE(w1.relieved_matl_completion_value,0,
0,
NULL,
0,
DECODE(SIGN(wro.relieved_matl_completion_qty),
SIGN(w1.relieved_matl_completion_value),
DECODE(SIGN(wro.relieved_matl_completion_qty-
/* LBM project Changes */
ABS(i_txn_qty)*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/i_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))),
SIGN(wro.quantity_per_assembly),
/* LBM project Changes */
i_txn_qty*(decode(wro.basis_type, 2,
wro.quantity_per_assembly/i_lot_size,
wro.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(wro.component_yield_factor,1),
1))*
relieved_matl_completion_value/
wro.relieved_matl_completion_qty,
0,
-1*relieved_matl_completion_value,
DECODE(SIGN(wro.relieved_matl_completion_qty),
SIGN(wro.quantity_per_assembly),
-1*relieved_matl_completion_value,
0)),
0)))
FROM
wip_req_operation_cost_details w2,
wip_requirement_operations wro
WHERE
w1.wip_entity_id = w2.wip_entity_id AND
w1.organization_id = w2.organization_id AND
w1.operation_seq_num = w2.operation_seq_num AND
w1.inventory_item_id = w2.inventory_item_id AND
w1.cost_element_id = w2.cost_element_id AND
w2.wip_entity_id = wro.wip_entity_id AND
w2.organization_id = wro.organization_id AND
w2.operation_seq_num = wro.operation_seq_num AND
w2.inventory_item_id = wro.inventory_item_id
)
WHERE
(w1.wip_entity_id, w1.organization_id,
w1.inventory_item_id, w1.operation_seq_num) IN
(SELECT
wip_entity_id, organization_id,
inventory_item_id,operation_seq_num
FROM
wip_requirement_operations wro2
WHERE
wro2.wip_entity_id = i_wip_entity_id AND
wro2.organization_id = i_org_id AND
wro2.quantity_per_assembly <> 0);
UPDATE wip_requirement_operations w
SET relieved_matl_completion_qty =
(SELECT
NVL(w.relieved_matl_completion_qty,0)+
DECODE(w.relieved_matl_completion_qty,0,
0,
NULL,
0,
DECODE(SUM(nvl(wrocd.relieved_matl_completion_value - wrocd.temp_relieved_value,0)),0,
0,
NULL,
0,
DECODE(SIGN(w.relieved_matl_completion_qty),
SIGN(SUM(nvl(wrocd.relieved_matl_completion_value - wrocd.temp_relieved_value,0))),
DECODE(SIGN(w.relieved_matl_completion_qty-
/* LBM project Changes */
ABS(i_txn_qty)*(decode(w.basis_type, 2,
w.quantity_per_assembly/i_lot_size,
w.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(w.component_yield_factor,1),
1))),
SIGN(w.quantity_per_assembly),
/* LBM project Changes */
i_txn_qty*(decode(w.basis_type, 2,
w.quantity_per_assembly/i_lot_size,
w.quantity_per_assembly)/
decode(l_include_comp_yield,
1, nvl(w.component_yield_factor,1),
1)),
0,
-1*relieved_matl_completion_qty,
DECODE(SIGN(w.relieved_matl_completion_qty),
SIGN(w.quantity_per_assembly),
-1*relieved_matl_completion_qty,
0)),
0)))
FROM
wip_req_operation_cost_details wrocd,
wip_requirement_operations w2
WHERE
w.wip_entity_id = w2.wip_entity_id AND
w.inventory_item_id = w2.inventory_item_id AND
w.operation_seq_num = w2.operation_seq_num AND
w.organization_id = w2.organization_id AND
w2.wip_entity_id = wrocd.wip_entity_id AND
w2.organization_id = wrocd.organization_id AND
w2.operation_seq_num = wrocd.operation_seq_num AND
w2.inventory_item_id = wrocd.inventory_item_id
GROUP BY
w2.wip_entity_id,
w2.organization_id,
w2.inventory_item_id,
w2.operation_seq_num,
w2.quantity_per_assembly,
w2.relieved_matl_completion_qty
)
WHERE
w.wip_entity_id = i_wip_entity_id AND
w.organization_id = i_org_id AND
w.quantity_per_assembly <> 0;
UPDATE wip_operation_resources w1
SET
(relieved_res_completion_units,
temp_relieved_value,
relieved_res_completion_value) =
(SELECT
--
-- relieved_res_completion_units
--
nvl(w1.relieved_res_completion_units,0)+
decode(SIGN(w2.relieved_res_completion_value),1,
nvl(w2.relieved_res_completion_units,0)*
decode(abs(i_txn_qty),
prior_completion_quantity,-1,
i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
prior_completion_quantity)),
0),
--
-- temp_relieved_value
--
decode(SIGN(w2.relieved_res_completion_value),1,
nvl(W2.relieved_res_completion_value,0)*
decode(abs(i_txn_qty),
prior_completion_quantity,-1,
i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
prior_completion_quantity)),
0),
---
--- relieved_res_completion_value
---
nvl(w1.relieved_res_completion_value,0)+
decode(SIGN(w2.relieved_res_completion_value),1,
nvl(w2.relieved_res_completion_value,0)*
decode(abs(i_txn_qty),
prior_completion_quantity,-1,
i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
prior_completion_quantity)),
0)
FROM
wip_operation_resources w2,
cst_comp_snapshot cocd
WHERE
w2.wip_entity_id = w1.wip_entity_id AND
w2.organization_id = w1.organization_id AND
w2.operation_seq_num = w1.operation_seq_num AND
w2.resource_seq_num = w1.resource_seq_num AND
w2.wip_entity_id = cocd.wip_entity_id AND
w2.operation_seq_num = cocd.operation_seq_num AND
cocd.new_operation_flag = 2 AND
cocd.transaction_id = i_trx_id)
WHERE
w1.wip_entity_id = i_wip_entity_id AND
w1.organization_id = i_org_id;
UPDATE wip_operation_overheads w1
SET
(relieved_ovhd_completion_units,
temp_relieved_value,
relieved_ovhd_completion_value) =
(SELECT
---
--- relieved_ovhd_completion_units
---
nvl(w1.relieved_ovhd_completion_units,0)+
decode(SIGN(w2.relieved_ovhd_completion_value),1,
nvl(W2.relieved_ovhd_completion_units,0)*
decode(abs(i_txn_qty),
prior_completion_quantity,-1,
i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
prior_completion_quantity)),
0),
---
--- temp_relieved_value
---
decode(SIGN(w2.relieved_ovhd_completion_value),1,
nvl(w2.relieved_ovhd_completion_value,0)*
decode(abs(i_txn_qty),
prior_completion_quantity,-1,
i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
prior_completion_quantity)),
0),
---
--- relieved_ovhd_completion_value
---
nvl(w1.relieved_ovhd_completion_value,0)+
decode(SIGN(w2.relieved_ovhd_completion_value),1,
nvl(w2.relieved_ovhd_completion_value,0)*
decode(abs(i_txn_qty),
prior_completion_quantity,-1,
i_txn_qty/decode(prior_completion_quantity,null,1,0,1,
prior_completion_quantity)),
0)
FROM
wip_operation_overheads w2,
cst_comp_snapshot cocd
WHERE
w2.wip_entity_id = w1.wip_entity_id AND
w2.organization_id = w1.organization_id AND
w2.operation_seq_num = w1.operation_seq_num AND
w2.resource_seq_num = w1.resource_seq_num AND
w2.overhead_id = w1.overhead_id AND
w2.basis_type = w1.basis_type AND
w2.wip_entity_id = cocd.wip_entity_id AND
w2.operation_seq_num = cocd.operation_seq_num AND
cocd.new_operation_flag = 2 AND
cocd.transaction_id = i_trx_id)
WHERE
w1.wip_entity_id = i_wip_entity_id AND
w1.organization_id = i_org_id;
* Insert into mtl_cst_txn_cost_details now that the *
* Costs have been computed ... *
* 3 statements are required --> one each for PL costs *
* , TL Res/OSP costs and TL ovhd costs. *
* Remember - the cst_txn_cost_detail tables stores unit *
* cost - but the wip tables store the value in the *
* temp_relieved_value column - so we have to divide by the *
* txn_qty to arrive at the unit cost. *
* Also, this insert should only be performed if the indicat *
* or is <> 1.
************************************************************/
IF (l_insert_ind <>1) THEN
stmt_num := 270;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
wrocd.cost_element_id,
2,
sum(nvl(wrocd.temp_relieved_value,0))/i_txn_qty,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
WIP_REQ_OPERATION_COST_DETAILS wrocd
where
WIP_ENTITY_ID = i_wip_entity_id AND
ORGANIZATION_ID = i_org_id
GROUP BY wrocd.cost_element_id
HAVING sum(nvl(wrocd.temp_relieved_value,0)) <> 0;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
br.cost_element_id,
1,
sum(nvl(wor.temp_relieved_value,0))/i_txn_qty,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
BOM_RESOURCES BR,
WIP_OPERATION_RESOURCES WOR
WHERE
WOR.RESOURCE_ID = BR.RESOURCE_ID AND
WOR.ORGANIZATION_ID = BR.ORGANIZATION_ID AND
WOR.WIP_ENTITY_ID = i_wip_entity_id AND
WOR.ORGANIZATION_ID = i_org_id
GROUP BY BR.COST_ELEMENT_ID
HAVING sum(nvl(wor.temp_relieved_value,0)) <> 0;
INSERT INTO mtl_cst_txn_cost_details
(
TRANSACTION_ID,
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
COST_ELEMENT_ID,
LEVEL_TYPE,
TRANSACTION_COST,
NEW_AVERAGE_COST,
PERCENTAGE_CHANGE,
VALUE_CHANGE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE)
SELECT
i_trx_id,
i_org_id,
i_inv_item_id,
5,
1,
SUM(nvl(temp_relieved_value,0))/i_txn_qty,
NULL,
NULL,
NULL,
SYSDATE,
i_user_id,
SYSDATE,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
SYSDATE
FROM
WIP_OPERATION_OVERHEADS
WHERE
WIP_ENTITY_ID = i_wip_entity_id AND
ORGANIZATION_ID = i_org_id
HAVING
SUM(nvl(temp_relieved_value,0)) <> 0;