[Home] [Help]
640: end if;
641:
642: select count(*)
643: into l_mclacd_exists
644: from mtl_cst_layer_act_cost_details
645: where transaction_id = i_txn_id
646: and layer_id = i_layer_id
647: and inv_layer_id = i_cur_layer_id;
648:
647: and inv_layer_id = i_cur_layer_id;
648:
649: if (l_mclacd_exists > 0) then
650: l_stmt_num := 2;
651: update mtl_cst_layer_act_cost_details
652: set layer_quantity = nvl(layer_quantity,0) + i_qty,
653: variance_amount = nvl(variance_amount,0) + (nvl(actual_cost,0)-nvl(layer_cost,0))*i_qty
654: where transaction_id = i_txn_id
655: and layer_id = i_layer_id
699: if (l_debug = 'Y') then
700: FND_FILE.PUT_LINE(FND_FILE.LOG,'zero_cost_flag: '|| to_char(l_zero_cost_flag));
701: end if;
702:
703: insert into mtl_cst_layer_act_cost_details (
704: transaction_id,
705: organization_id,
706: layer_id,
707: inv_layer_id,
773: if (l_debug = 'Y') then
774: FND_FILE.PUT_LINE(FND_FILE.LOG,'zero_cost_flag: '|| to_char(l_zero_cost_flag));
775: end if;
776:
777: insert into mtl_cst_layer_act_cost_details (
778: transaction_id,
779: organization_id,
780: layer_id,
781: inv_layer_id,
823:
824: -- case 4 CREATE None None
825: elsif(i_mode = 'CREATE' and i_actual_cost_table = 'NONE' and i_layer_cost_table = 'NONE') then
826:
827: insert into mtl_cst_layer_act_cost_details (
828: transaction_id,
829: organization_id,
830: layer_id,
831: inv_layer_id,
870: from dual;
871: -- case 6 REPLENISH CILCD CILCD
872: elsif(i_mode = 'REPLENISH' and i_actual_cost_table = 'CILCD' and i_layer_cost_table = 'CILCD') then
873:
874: insert into mtl_cst_layer_act_cost_details (
875: transaction_id,
876: organization_id,
877: layer_id,
878: inv_layer_id,
954:
955: -- case 7 CONSUME MCACD CILCD
956: elsif(i_mode = 'CONSUME' and i_actual_cost_table = 'MCACD' and i_layer_cost_table = 'CILCD') then
957:
958: insert into mtl_cst_layer_act_cost_details (
959: transaction_id,
960: organization_id,
961: layer_id,
962: inv_layer_id,
1040:
1041:
1042: -- case 8 CONSUME MCTCD CILCD
1043: elsif(i_mode = 'CONSUME' and i_actual_cost_table = 'MCTCD' and i_layer_cost_table = 'CILCD') then
1044: insert into mtl_cst_layer_act_cost_details (
1045: transaction_id,
1046: organization_id,
1047: layer_id,
1048: inv_layer_id,
1151:
1152: if (l_debug = 'Y') then
1153: FND_FILE.PUT_LINE(FND_FILE.LOG,'zero_cost_flag: '|| to_char(l_zero_cost_flag));
1154: end if;
1155: insert into mtl_cst_layer_act_cost_details (
1156: transaction_id,
1157: organization_id,
1158: layer_id,
1159: inv_layer_id,
1226: end if;
1227:
1228: l_stmt_num := 20;
1229:
1230: insert into mtl_cst_layer_act_cost_details (
1231: transaction_id,
1232: organization_id,
1233: layer_id,
1234: inv_layer_id,
1796: /* Check the MOH rates of the two transactions */
1797: l_stmt_num := 80;
1798: SELECT nvl(SUM(actual_cost),0)
1799: INTO l_last_moh
1800: FROM mtl_cst_layer_act_cost_details
1801: WHERE transaction_id = l_last_txn_id
1802: AND organization_id = i_org_id
1803: AND layer_id = i_layer_id
1804: AND inv_layer_id = l_inv_layer_id
1811: l_stmt_num := 85;
1812:
1813: SELECT nvl(SUM(actual_cost),0)
1814: INTO l_moh
1815: FROM mtl_cst_layer_act_cost_details
1816: WHERE transaction_id = i_txn_id
1817: AND organization_id = i_org_id
1818: AND layer_id = i_layer_id
1819: AND inv_layer_id = l_inv_layer_id
1854:
1855:
1856: SELECT nvl(SUM(layer_cost),0)
1857: INTO l_layer_cost
1858: FROM mtl_cst_layer_act_cost_details
1859: WHERE transaction_id = i_txn_id
1860: AND organization_id = i_org_id
1861: AND layer_id = i_layer_id
1862: AND inv_layer_id = l_inv_layer_id
1939: END IF;
1940:
1941: /* Update MCLACD entries */
1942: l_stmt_num := 100;
1943: UPDATE mtl_cst_layer_act_cost_details
1944: SET inv_layer_id = l_inv_layer_id
1945: WHERE transaction_id = i_txn_id
1946: AND organization_id = i_org_id
1947: AND layer_id = i_layer_id;
2052: i_req_id,
2053: i_prg_appl_id,
2054: i_prg_id,
2055: sysdate
2056: FROM mtl_cst_layer_act_cost_details mclacd
2057: WHERE transaction_id = i_txn_id
2058: AND inv_layer_id = l_inv_layer_id
2059: AND organization_id = i_org_id
2060: GROUP
3651: l_stmt_num := 11;
3652:
3653: select count(*)
3654: into l_mat_ovhds
3655: from mtl_cst_layer_act_cost_details
3656: where transaction_id = i_txn_id
3657: and organization_id = i_org_id
3658: and layer_id = i_layer_id -- cost group layer
3659: and inv_layer_id = i_inv_layer_id -- inventory layer
3731: -- the material overhead of 'total value' basis (basis_type = 5)
3732:
3733: select nvl(sum(actual_cost),0)
3734: into l_item_cost
3735: from mtl_cst_layer_act_cost_details
3736: where transaction_id = i_txn_id
3737: and organization_id = i_org_id
3738: and layer_id = i_layer_id
3739: and inv_layer_id = i_inv_layer_id;
3957:
3958: -- check if there is data in MCLACD (material overhead) for this layer.
3959: select count(*)
3960: into l_mclacd_ovhd
3961: from mtl_cst_layer_act_cost_details mclacd
3962: where transaction_id = i_txn_id
3963: and organization_id = i_org_id
3964: and layer_id = i_layer_id
3965: and inv_layer_id = i_inv_layer_id
4012: if(i_interorg_rec = 1) then
4013:
4014: l_stmt_num := 75;
4015:
4016: update mtl_cst_layer_act_cost_details mclacd
4017: set mclacd.actual_cost = nvl(mclacd.actual_cost, 0) + l_ovhd_cost,
4018: mclacd.layer_cost = nvl(mclacd.layer_cost,0) + l_ovhd_cost,
4019: mclacd.variance_amount = 0,
4020: mclacd.payback_variance_amount = 0
4027:
4028: else
4029:
4030: l_stmt_num := 76;
4031: update mtl_cst_layer_act_cost_details mclacd
4032: set mclacd.actual_cost = nvl(mclacd.actual_cost, 0) + l_ovhd_cost,
4033: mclacd.variance_amount = decode(l_txn_type_id,68,0,
4034: (nvl(mclacd.actual_cost,0) + l_ovhd_cost
4035: - nvl(mclacd.layer_cost,0)) * layer_quantity ),
4054:
4055:
4056: else /* mclacd does not exist */
4057: l_stmt_num := 80;
4058: insert into mtl_cst_layer_act_cost_details(
4059: transaction_id,
4060: organization_id,
4061: inventory_item_id,
4062: cost_element_id,
4235:
4236: l_stmt_num := 6;
4237: select count(*)
4238: into l_count
4239: from mtl_cst_layer_act_cost_details
4240: where transaction_id = i_txn_id
4241: and organization_id = i_org_id;
4242:
4243: if (l_count = 0) then
4263: NULL, -- new cost
4264: NVL(sum(mclacd.variance_amount),0),
4265: NVL(sum(mclacd.payback_variance_amount)/abs(i_txn_qty),0), -- bugfix 1393484
4266: NVL(sum(mclacd.onhand_variance_amount),0)
4267: from mtl_cst_layer_act_cost_details mclacd
4268: where mclacd.transaction_id = i_txn_id
4269: and mclacd.organization_id = i_org_id
4270: and mclacd.layer_id = i_layer_id
4271: and mclacd.cost_element_id = mcacd.cost_element_id
4329: NVL(sum(mclacd.variance_amount),0),
4330: 'N',
4331: NVL(sum(mclacd.payback_variance_amount)/abs(i_txn_qty),0), -- bugfix 1393484
4332: NVL(sum(mclacd.onhand_variance_amount),0)
4333: from mtl_cst_layer_act_cost_details mclacd
4334: where mclacd.transaction_id = i_txn_id
4335: and mclacd.organization_id = i_org_id
4336: and mclacd.layer_id = i_layer_id
4337: and not exists
4475: * mcl.layer_quantity)
4476: - decode(i_txn_action_id, 24,
4477: 0,nvl(mcl.variance_amount,0))),0)
4478: ) / l_total_layer_qty
4479: from mtl_cst_layer_act_cost_details mcl
4480: where mcl.transaction_id = i_txn_id
4481: and mcl.layer_id = clcd.layer_id
4482: and mcl.level_type = clcd.level_type
4483: and mcl.cost_element_id = clcd.cost_element_id)
4482: and mcl.level_type = clcd.level_type
4483: and mcl.cost_element_id = clcd.cost_element_id)
4484: where clcd.layer_id = i_layer_id
4485: and exists (select 1
4486: from mtl_cst_layer_act_cost_details mclacd2
4487: where mclacd2.transaction_id = i_txn_id
4488: and mclacd2.layer_id = clcd.layer_id
4489: and mclacd2.level_type = clcd.level_type
4490: and mclacd2.cost_element_id = clcd.cost_element_id);
4519: sysdate,
4520: nvl(sum((decode(i_txn_action_id, 24, (nvl(mclacd.actual_cost,0) - nvl(mclacd.layer_cost,0)), nvl(mclacd.actual_cost,0))
4521: * mclacd.layer_quantity
4522: - decode(i_txn_action_id, 24, 0,nvl(mclacd.variance_amount,0))) / l_total_layer_qty),0)
4523: from mtl_cst_layer_act_cost_details mclacd
4524: where mclacd.transaction_id = i_txn_id
4525: and mclacd.layer_id = i_layer_id
4526: and not exists (select 1
4527: from cst_layer_cost_details clcd
4786: ** This function is called to update inventory layer cost. **
4787: ** It will determine the new elemental costs of the layer based **
4788: ** on user-enter values and compute the adjustment amounts to **
4789: ** inventory valuation. **
4790: ** MTL_CST_LAYER_ACT_COST_DETAILS will be populated and the other **
4791: ** cost tables (CILCD, CIL, CLCD, CQL, CICD, CIC) will be updated **
4792: ** accordingly with the new cost information. **
4793: ** This function is duplicated from CSTPAVCP.average_cost_update. **
4794: ** **
4866:
4867: l_stmt_num := 10;
4868:
4869: /*********************************************************
4870: ** Insert records into mtl_cst_layer_act_cost_details. **
4871: *********************************************************/
4872:
4873: insert into mtl_cst_layer_act_cost_details (
4874: transaction_id,
4869: /*********************************************************
4870: ** Insert records into mtl_cst_layer_act_cost_details. **
4871: *********************************************************/
4872:
4873: insert into mtl_cst_layer_act_cost_details (
4874: transaction_id,
4875: organization_id,
4876: layer_id,
4877: inv_layer_id,
4984: l_stmt_num := 20;
4985:
4986: /* select count(*)
4987: into l_neg_cost
4988: from mtl_cst_layer_act_cost_details
4989: where transaction_id = i_txn_id
4990: and organization_id = i_org_id
4991: and layer_id = i_layer_id
4992: and inv_layer_id = l_inv_layer_id
5035: i_req_id,
5036: i_prg_appl_id,
5037: i_prg_id,
5038: sysdate
5039: from mtl_cst_layer_act_cost_details mclacd
5040: where mclacd.transaction_id = i_txn_id
5041: and mclacd.organization_id = i_org_id
5042: and mclacd.layer_id = i_layer_id
5043: and mclacd.inv_layer_id = l_inv_layer_id;
6046:
6047: select (sum(actual_cost) - sum(layer_cost)),sum(variance_amount),
6048: sum(onhand_variance_amount)
6049: into l_cost,l_var,l_onhand_var
6050: from mtl_cst_layer_act_cost_details
6051: where transaction_id = i_txn_id
6052: and organization_id = i_org_id
6053: and cost_element_id = cost_element;
6054: