[Home] [Help]
24: O_RETURN_CODE := 9999;
25:
26: l_location := 1;
27:
28: DELETE FROM cst_item_costs CIC
29: WHERE CIC.organization_id = I_ORGANIZATION_ID
30: AND CIC.cost_type_id = I_COST_TYPE_ID
31: AND CIC.inventory_item_id in
32: (SELECT C2.inventory_item_id
30: AND CIC.cost_type_id = I_COST_TYPE_ID
31: AND CIC.inventory_item_id in
32: (SELECT C2.inventory_item_id
33: FROM cst_lists CL
34: , cst_item_costs C2
35: WHERE list_id = I_LIST_ID
36: AND C2.cost_type_id = I_FROM_COST_TYPE
37: AND C2.organization_id = I_ORGANIZATION_ID
38: AND C2.inventory_item_id = CL.entity_id
39: );
40:
41: l_location := 2;
42:
43: INSERT INTO cst_item_costs
44: (
45: inventory_item_id, organization_id, cost_type_id,
46: request_id, program_application_id,
47: program_id, program_update_date,
121: , CIC.attribute13
122: , CIC.attribute14
123: , CIC.attribute15
124: FROM cst_lists CL
125: , cst_item_costs CIC
126: WHERE CL.list_id = I_LIST_ID
127: AND CIC.inventory_item_id = CL.entity_id
128: AND CIC.cost_type_id = I_FROM_COST_TYPE
129: AND CIC.organization_id = I_ORGANIZATION_ID;
139: AND CICD.cost_type_id = I_COST_TYPE_ID
140: AND CICD.inventory_item_id in
141: (SELECT C2.inventory_item_id
142: FROM cst_lists CL
143: , cst_item_costs C2
144: WHERE list_id = I_LIST_ID
145: AND C2.cost_type_id = I_FROM_COST_TYPE
146: AND C2.organization_id = I_ORGANIZATION_ID
147: AND C2.inventory_item_id = CL.entity_id
286: O_RETURN_CODE OUT NOCOPY NUMBER) IS
287:
288:
289: l_location NUMBER;
290: TYPE l_table IS TABLE OF CST_ITEM_COSTS.INVENTORY_ITEM_ID%TYPE;
291: l_temp_table l_table;
292: CURSOR cur_list IS
293: (select entity_id
294: from cst_lists l
293: (select entity_id
294: from cst_lists l
295: where L.list_id = I_LIST_ID
296: and NOT EXISTS (select inventory_item_id
297: from cst_item_costs cic2
298: where cic2.inventory_item_id = l.entity_id
299: and cic2.organization_id = I_ORGANIZATION_ID
300: AND cic2.cost_type_id = I_COST_TYPE_ID)
301: );
310: LOOP
311: FETCH cur_list BULK COLLECT INTO l_temp_table LIMIT 1000;
312:
313: FORALL i IN l_temp_table.FIRST..l_temp_table.LAST
314: INSERT INTO cst_item_costs
315: ( inventory_item_id
316: , organization_id
317: , cost_type_id
318: , request_id
379: , CIC.attribute12
380: , CIC.attribute13
381: , CIC.attribute14
382: , CIC.attribute15
383: FROM cst_item_costs CIC
384: WHERE CIC.organization_id = I_ORGANIZATION_ID
385: AND CIC.cost_type_id in (1,2)
386: AND CIC.inventory_item_id = l_temp_table(i)
387: AND CIC.inventory_asset_flag = 1 ;
476: , CIC.attribute13
477: , CIC.attribute14
478: , CIC.attribute15
479: FROM cst_lists L
480: , cst_item_costs CIC
481: , bom_resources BR
482: WHERE CIC.organization_id = I_ORGANIZATION_ID
483: AND L.list_id = I_LIST_ID
484: AND BR.resource_id = I_RESOURCE_ID
611: END IF;
612:
613: END LOOP;
614: --
615: -- Update denormalized data in CST_ITEM_COSTS table
616: --
617: l_location := 1;
618: OPEN c_item_id;
619: LOOP
620: FETCH c_item_id BULK COLLECT INTO c_item_id_tbl
621: LIMIT 1000;
622:
623: FORALL i IN c_item_id_tbl.first..c_item_id_tbl.last
624: UPDATE cst_item_costs CIC
625: SET (
626: last_update_date,
627: last_updated_by,
628: pl_material,
710: -- PROCEDURE
711: -- set_cost_controls Invoked from the Mass Edit Menu as a concurrent
712: -- request. This function allows the user to set
713: -- the values of the following three fields in
714: -- cst_item_costs:
715: -- BASED_ON_ROLLUP_FLAG
716: -- DEFAULTED_FLAG
717: -- LOT_SIZE
718: --
785:
786: CONC_STATUS BOOLEAN; -- variable for SET_COMPLETION_STATUS
787:
788: -- the following datatype holds the BULK COLLECTed item list
789: TYPE ItemList IS TABLE OF cst_item_costs.inventory_item_id%TYPE;
790:
791: l_items ItemList; -- Collection of items that will generate warnings due to
792: -- shrinkage_rate/BOR conflicts and defaulted_flag conflicts
793: l_nonzero_shrinkage NUMBER; -- the number of such items
835: if (i_bor_flag <> 3 AND i_def_flag <> 3 AND i_lotsz_lov <> 2) then
836: -- Update CIC in the case where none of the fields are copied - avoids superfluous subquery in SET stmt
837:
838: l_stmt_num := 10;
839: UPDATE cst_item_costs cic
840: SET based_on_rollup_flag = decode(i_bor_flag, 1,1, 2,2, 4,cic.BASED_ON_ROLLUP_FLAG, NULL),
841: defaulted_flag = decode(i_def_flag, 1,1, 2,2, 4,cic.DEFAULTED_FLAG, NULL),
842: lot_size = decode(i_lotsz_lov, 1,nvl(i_lot_size,cic.LOT_SIZE), 3,cic.LOT_SIZE, NULL),
843: last_update_date = sysdate,
871: elsif (i_copy_option = 1) then
872: -- Update CIC where some fields are copied from MSI and others keep current settings
873:
874: l_stmt_num := 20;
875: UPDATE cst_item_costs cic
876: SET (based_on_rollup_flag,
877: defaulted_flag,
878: lot_size,
879: last_update_date,
922: elsif (i_copy_option = 2) then
923: -- Update CIC where some fields are copied from src cost type and others keep current settings
924:
925: l_stmt_num := 30;
926: UPDATE cst_item_costs cic
927: SET (based_on_rollup_flag,
928: defaulted_flag,
929: lot_size,
930: last_update_date,
946: l_request_id,
947: l_pgm_app_id,
948: l_pgm_id,
949: sysdate
950: FROM cst_item_costs cic1, cst_item_costs cic2
951: WHERE cic2.organization_id = cic.organization_id
952: AND cic2.inventory_item_id = cic.inventory_item_id
953: AND cic2.cost_type_id = cic.cost_type_id
954: AND cic1.organization_id (+) = cic2.organization_id
978: end if;
979:
980: l_num_CIC_rows := SQL%ROWCOUNT;
981: fnd_file.put_line(fnd_file.log,'');
982: fnd_file.put_line(fnd_file.log,'Updated '||to_char(l_num_CIC_rows)||' rows in cst_item_costs.');
983:
984: if (i_bor_flag = 1 OR i_bor_flag = 3) then
985: -- Based On Rollup may have been updated to YES, print a NOTE message
986: fnd_file.put_line(fnd_file.log,'');
1012: l_request_id,
1013: l_pgm_app_id,
1014: l_pgm_id,
1015: sysdate
1016: FROM cst_item_costs cic
1017: WHERE cic.organization_id = cicd.organization_id
1018: AND cic.cost_type_id = cicd.cost_type_id
1019: AND cic.inventory_item_id = cicd.inventory_item_id)
1020: WHERE cicd.cost_type_id = i_cost_type
1053: -- reset. Note that costs will not be changed for affected items. Instead, the
1054: -- usage_rate_or_amount in CICD will be adjusted to keep it consistent with the cost
1055: -- and the new net_yield_or_shrinkage_factor.
1056: l_stmt_num := 50;
1057: UPDATE cst_item_costs cic
1058: SET cic.shrinkage_rate = 0
1059: WHERE cic.cost_type_id = i_cost_type
1060: AND cic.organization_id = i_org_id
1061: AND (i_range = 1
1144: if l_items.EXISTS(1) then
1145: l_items.DELETE; -- reset the collection
1146: end if;
1147:
1148: UPDATE cst_item_costs cic
1149: SET cic.defaulted_flag = 2
1150: WHERE cic.cost_type_id = i_cost_type
1151: AND cic.organization_id = i_org_id
1152: AND (i_range = 1
1174: AND cicd.cost_type_id = cic.cost_type_id
1175: AND cicd.inventory_item_id = cic.inventory_item_id
1176: AND cicd.rollup_source_type = 1) -- user defined
1177: OR NOT EXISTS (SELECT 'X'
1178: FROM cst_item_costs cic1, cst_cost_types cct
1179: WHERE cic1.organization_id = cic.organization_id
1180: AND cic1.cost_type_id = cct.default_cost_type_id
1181: AND cct.cost_type_id = cic.cost_type_id
1182: AND cic1.inventory_item_id = cic.inventory_item_id))
1233: WHERE cicd.rollup_source_type = 2
1234: AND cicd.cost_type_id = i_cost_type
1235: AND cicd.organization_id = i_org_id
1236: AND EXISTS (SELECT 'X'
1237: FROM cst_item_costs cic
1238: WHERE cic.organization_id = cicd.organization_id
1239: AND cic.cost_type_id = cicd.cost_type_id
1240: AND cic.inventory_item_id = cicd.inventory_item_id
1241: AND cic.defaulted_flag = 2
1249: AND cicd.cost_type_id = i_cost_type
1250: AND cicd.organization_id = i_org_id
1251: AND cicd.inventory_item_id = i_specific_item
1252: AND EXISTS (SELECT 'X'
1253: FROM cst_item_costs cic
1254: WHERE cic.organization_id = cicd.organization_id
1255: AND cic.cost_type_id = cicd.cost_type_id
1256: AND cic.inventory_item_id = cicd.inventory_item_id
1257: AND cic.defaulted_flag = 2
1265: AND cicd.cost_type_id = i_cost_type
1266: AND cicd.organization_id = i_org_id
1267: AND cicd.inventory_item_id IN (SELECT msi1.inventory_item_id FROM mtl_system_items_kfv msi1 WHERE msi1.concatenated_segments BETWEEN i_item_from AND i_item_to)
1268: AND EXISTS (SELECT 'X'
1269: FROM cst_item_costs cic
1270: WHERE cic.organization_id = cicd.organization_id
1271: AND cic.cost_type_id = cicd.cost_type_id
1272: AND cic.inventory_item_id = cicd.inventory_item_id
1273: AND cic.defaulted_flag = 2
1286: AND mic.category_set_id = i_category_set
1287: AND mic.category_id = mc.category_id
1288: AND mc.concatenated_segments BETWEEN i_category_from AND i_category_to)
1289: AND EXISTS (SELECT 'X'
1290: FROM cst_item_costs cic
1291: WHERE cic.organization_id = cicd.organization_id
1292: AND cic.cost_type_id = cicd.cost_type_id
1293: AND cic.inventory_item_id = cicd.inventory_item_id
1294: AND cic.defaulted_flag = 2