[Home] [Help]
PACKAGE BODY: APPS.EGO_UI_ITEM_PUB
Source
1 PACKAGE BODY EGO_UI_ITEM_PUB AS
2 /* $Header: EGOITUIB.pls 115.40 2004/06/07 00:11:16 absinha noship $ */
3
4 G_FILE_NAME CONSTANT VARCHAR2(12) := 'EGOITUIB.pls';
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_UI_ITEM_PUB';
6 G_CREATE_TRANSACTION_TYPE CONSTANT VARCHAR2(10) := 'CREATE';
7 G_UPDATE_TRANSACTION_TYPE CONSTANT VARCHAR2(10) := 'UPDATE';
8 G_COPY_TRANSACTION_TYPE CONSTANT VARCHAR2(10) := 'COPY';
9 G_OBJECT_NAME CONSTANT VARCHAR2(10) := 'EGO_ITEM';
10
11 -- =============================================================================
12 -- Package variables and cursors
13 -- =============================================================================
14
15 g_USER_ID NUMBER := FND_GLOBAL.User_Id;
16 g_LOGIN_ID NUMBER := FND_GLOBAL.Conc_Login_Id;
17
18 -- the below variables are used for copy item
19 g_in_item_tbl EGO_ITEM_PUB.ITEM_TBL_TYPE;
20 g_out_item_tbl EGO_ITEM_PUB.ITEM_TBL_TYPE;
21 -- end of global variables for copy_item_functionality
22
23 --g_MISS_CHAR VARCHAR2(1) := FND_API.g_MISS_CHAR;
24 --g_MISS_NUM NUMBER := FND_API.g_MISS_NUM;
25 --g_MISS_DATE DATE := FND_API.g_MISS_DATE;
26
27 -- =============================================================================
28 -- Procedures
29 -- =============================================================================
30
31 --
32 -- Capture the sysdate at once for the whole process. During the process we use
33 -- sysdate in many places for compare, insert and update. It is essential that
34 -- we deal with the same sysdate value. Date will be assigned in the entry procedure.
35 --
36 G_Sysdate DATE;
37
38 CURSOR org_item_exists_cur
39 ( p_inventory_item_id NUMBER
40 , p_organization_id NUMBER
41 ) IS
42 SELECT 'x'
43 FROM mtl_system_items_b
44 WHERE
45 inventory_item_id = p_inventory_item_id
46 AND organization_id = p_organization_id;
47
48 -- Developer debugging
49 -- Should be set to false when arcing in.
50 msg_line_no NUMBER := -1000;
51
52
53 PROCEDURE developer_debug (p_msg IN VARCHAR2) IS
54 --Modified for Debug purpose for Bug 2960442
55 BEGIN
56 -- msg_line_no := msg_line_no + 1;
57 -- INSERT INTO IDC_ITEM_DEBUG VALUES (msg_line_no||p_msg||TO_CHAR(SYSDATE,'DD-MON-YY HH24:MI:SS'));
58 -- debug (p_msg);
59 RETURN;
60 EXCEPTION
61 WHEN OTHERS THEN
62 NULL;
63 END;
64
65
66 PROCEDURE initialize_item_info (p_inventory_item_id IN NUMBER
67 ,p_organization_id IN NUMBER
68 ,x_return_status OUT NOCOPY VARCHAR2
69 ,x_msg_count OUT NOCOPY NUMBER
70 ) IS
71 ----------------------------------------------------------------------------
72 -- Start OF comments
73 -- API name : Initialize_item_info
74 -- TYPE : Private
75 -- Pre-reqs : None
76 -- FUNCTION : Initialize the Item record with the values of the
77 -- item_id (p_inventory_item_id) and Org Id (p_Organization_id)
78 --
79 -- Parameters:
80 -- IN : p_inventory_item_id IN NUMBER (required)
81 -- : p_organization_id IN NUMBER (required)
82 --
83 --
84 -- OUT : x_return_status OUT VARCHAR2
85 -- x_msg_count OUT NUMBER
86 --
87 ----------------------------------------------------------------------------
88
89 CURSOR c_copy_item_info (cp_inventory_item_id IN NUMBER
90 ,cp_organization_id IN NUMBER ) IS
91 SELECT *
92 FROM mtl_system_items_b
93 WHERE inventory_item_id = cp_inventory_item_id
94 AND organization_id = cp_organization_id;
95
96 l_orig_item_rec MTL_SYSTEM_ITEMS_B%ROWTYPE;
97
98 BEGIN
99 developer_debug ('::2960442::initialize_item_info::start');
100 OPEN c_copy_item_info (cp_inventory_item_id => p_inventory_item_id
101 ,cp_organization_id => p_organization_id);
102 FETCH c_copy_item_info into l_orig_item_rec;
103 IF c_copy_item_info%NOTFOUND THEN
104 developer_debug (' Invalid item informatin passed');
105 -- no items found to copy from
106 EGO_Item_Msg.Add_Error_Message
107 (p_entity_index => 1
108 ,p_application_short_name => 'EGO'
109 ,p_message_name => 'EGO_IPI_INVALID_ITEM'
110 ,p_token_name1 => 'ITEM'
111 ,p_token_value1 => p_inventory_item_id
112 ,p_translate1 => FALSE
113 ,p_token_name2 => 'ORGANIZATION'
114 ,p_token_value2 => p_organization_id
115 ,p_translate2 => FALSE
116 ,p_token_name3 => NULL
117 ,p_token_value3 => NULL
118 ,p_translate3 => FALSE
119 );
120 x_return_status := G_RET_STS_ERROR;
121 RETURN;
122 END IF;
123 -- TODO get inputs from Murthy regarding which columns are not available in FORMS
124 -- and reset them to
125 -- all the item info available in the record
126 g_in_item_tbl(0).transaction_type := NULL;
127 g_in_item_tbl(0).Return_Status := NULL;
128 g_in_item_tbl(0).transaction_type := NULL;
129 g_in_item_tbl(0).Language_Code := NULL;
130 g_in_item_tbl(0).Template_Id := NULL;
131 g_in_item_tbl(0).Template_Name := NULL;
132 g_in_item_tbl(0).Inventory_Item_Id := NULL;
133 g_in_item_tbl(0).Item_Number := NULL;
134 g_in_item_tbl(0).segment1 := NULL;
135 g_in_item_tbl(0).segment2 := NULL;
136 g_in_item_tbl(0).segment3 := NULL;
137 g_in_item_tbl(0).segment4 := NULL;
138 g_in_item_tbl(0).segment5 := NULL;
139 g_in_item_tbl(0).segment6 := NULL;
140 g_in_item_tbl(0).segment7 := NULL;
141 g_in_item_tbl(0).segment8 := NULL;
142 g_in_item_tbl(0).segment9 := NULL;
143 g_in_item_tbl(0).segment10 := NULL;
144 g_in_item_tbl(0).segment11 := NULL;
145 g_in_item_tbl(0).segment12 := NULL;
146 g_in_item_tbl(0).segment13 := NULL;
147 g_in_item_tbl(0).segment14 := NULL;
148 g_in_item_tbl(0).segment15 := NULL;
149 g_in_item_tbl(0).segment16 := NULL;
150 g_in_item_tbl(0).segment17 := NULL;
151 g_in_item_tbl(0).segment18 := NULL;
152 g_in_item_tbl(0).segment19 := NULL;
153 g_in_item_tbl(0).segment20 := NULL;
154 g_in_item_tbl(0).summary_flag := l_orig_item_rec.SUMMARY_FLAG;
155 g_in_item_tbl(0).Organization_Id := NULL;
156 g_in_item_tbl(0).Organization_Code := NULL;
157 g_in_item_tbl(0).Item_Catalog_Group_Id := NULL;
158 g_in_item_tbl(0).Catalog_Status_Flag := NULL;
159 g_in_item_tbl(0).Lifecycle_Id := NULL;
160 g_in_item_tbl(0).Current_Phase_Id := NULL;
161 g_in_item_tbl(0).Description := NULL;
162 g_in_item_tbl(0).Long_Description := NULL;
163 g_in_item_tbl(0).Primary_Uom_Code := NULL;
164 g_in_item_tbl(0).ALLOWED_UNITS_LOOKUP_CODE := l_orig_item_rec.allowed_units_lookup_code;
165 g_in_item_tbl(0).Inventory_Item_Status_Code := NULL;
166 g_in_item_tbl(0).DUAL_UOM_CONTROL := l_orig_item_rec.DUAL_UOM_CONTROL;
167 g_in_item_tbl(0).SECONDARY_UOM_CODE := l_orig_item_rec.SECONDARY_UOM_CODE;
168 g_in_item_tbl(0).DUAL_UOM_DEVIATION_HIGH := l_orig_item_rec.DUAL_UOM_DEVIATION_HIGH;
169 g_in_item_tbl(0).DUAL_UOM_DEVIATION_LOW := l_orig_item_rec.DUAL_UOM_DEVIATION_LOW;
170 g_in_item_tbl(0).ITEM_TYPE := l_orig_item_rec.ITEM_TYPE;
171 -- Inventory
172 g_in_item_tbl(0).INVENTORY_ITEM_FLAG := l_orig_item_rec.INVENTORY_ITEM_FLAG;
173 g_in_item_tbl(0).STOCK_ENABLED_FLAG := l_orig_item_rec.STOCK_ENABLED_FLAG;
174 g_in_item_tbl(0).MTL_TRANSACTIONS_ENABLED_FLAG := l_orig_item_rec.MTL_TRANSACTIONS_ENABLED_FLAG;
175 g_in_item_tbl(0).REVISION_QTY_CONTROL_CODE := l_orig_item_rec.REVISION_QTY_CONTROL_CODE;
176 g_in_item_tbl(0).LOT_CONTROL_CODE := l_orig_item_rec.LOT_CONTROL_CODE;
177 g_in_item_tbl(0).AUTO_LOT_ALPHA_PREFIX := l_orig_item_rec.AUTO_LOT_ALPHA_PREFIX;
178 g_in_item_tbl(0).START_AUTO_LOT_NUMBER := l_orig_item_rec.START_AUTO_LOT_NUMBER;
179 g_in_item_tbl(0).SERIAL_NUMBER_CONTROL_CODE := l_orig_item_rec.SERIAL_NUMBER_CONTROL_CODE;
180 g_in_item_tbl(0).AUTO_SERIAL_ALPHA_PREFIX := l_orig_item_rec.AUTO_SERIAL_ALPHA_PREFIX;
181 g_in_item_tbl(0).START_AUTO_SERIAL_NUMBER := l_orig_item_rec.START_AUTO_SERIAL_NUMBER;
182 g_in_item_tbl(0).SHELF_LIFE_CODE := l_orig_item_rec.SHELF_LIFE_CODE;
183 g_in_item_tbl(0).SHELF_LIFE_DAYS := l_orig_item_rec.SHELF_LIFE_DAYS;
184 g_in_item_tbl(0).RESTRICT_SUBINVENTORIES_CODE := l_orig_item_rec.RESTRICT_SUBINVENTORIES_CODE;
185 g_in_item_tbl(0).LOCATION_CONTROL_CODE := l_orig_item_rec.LOCATION_CONTROL_CODE;
186 g_in_item_tbl(0).RESTRICT_LOCATORS_CODE := l_orig_item_rec.RESTRICT_LOCATORS_CODE;
187 g_in_item_tbl(0).RESERVABLE_TYPE := l_orig_item_rec.RESERVABLE_TYPE;
188 g_in_item_tbl(0).CYCLE_COUNT_ENABLED_FLAG := l_orig_item_rec.CYCLE_COUNT_ENABLED_FLAG;
189 g_in_item_tbl(0).NEGATIVE_MEASUREMENT_ERROR := l_orig_item_rec.NEGATIVE_MEASUREMENT_ERROR;
190 g_in_item_tbl(0).POSITIVE_MEASUREMENT_ERROR := l_orig_item_rec.POSITIVE_MEASUREMENT_ERROR;
191 g_in_item_tbl(0).CHECK_SHORTAGES_FLAG := l_orig_item_rec.CHECK_SHORTAGES_FLAG;
192 g_in_item_tbl(0).LOT_STATUS_ENABLED := l_orig_item_rec.LOT_STATUS_ENABLED;
193 g_in_item_tbl(0).DEFAULT_LOT_STATUS_ID := l_orig_item_rec.DEFAULT_LOT_STATUS_ID;
194 g_in_item_tbl(0).SERIAL_STATUS_ENABLED := l_orig_item_rec.SERIAL_STATUS_ENABLED;
195 g_in_item_tbl(0).DEFAULT_SERIAL_STATUS_ID := l_orig_item_rec.DEFAULT_SERIAL_STATUS_ID;
196 g_in_item_tbl(0).LOT_SPLIT_ENABLED := l_orig_item_rec.LOT_SPLIT_ENABLED;
197 g_in_item_tbl(0).LOT_MERGE_ENABLED := l_orig_item_rec.LOT_MERGE_ENABLED;
198 g_in_item_tbl(0).LOT_TRANSLATE_ENABLED := l_orig_item_rec.LOT_TRANSLATE_ENABLED;
199 g_in_item_tbl(0).BULK_PICKED_FLAG := l_orig_item_rec.BULK_PICKED_FLAG;
200 g_in_item_tbl(0).LOT_SUBSTITUTION_ENABLED := l_orig_item_rec.LOT_SUBSTITUTION_ENABLED;
201 -- Bills of Material
202 g_in_item_tbl(0).BOM_ITEM_TYPE := l_orig_item_rec.BOM_ITEM_TYPE;
203 g_in_item_tbl(0).BOM_ENABLED_FLAG := l_orig_item_rec.BOM_ENABLED_FLAG;
204 g_in_item_tbl(0).BASE_ITEM_ID := l_orig_item_rec.BASE_ITEM_ID;
205 g_in_item_tbl(0).ENG_ITEM_FLAG := l_orig_item_rec.ENG_ITEM_FLAG;
206 g_in_item_tbl(0).ENGINEERING_ITEM_ID := l_orig_item_rec.ENGINEERING_ITEM_ID;
207 g_in_item_tbl(0).ENGINEERING_ECN_CODE := l_orig_item_rec.ENGINEERING_ECN_CODE;
208 g_in_item_tbl(0).ENGINEERING_DATE := l_orig_item_rec.ENGINEERING_DATE;
209 g_in_item_tbl(0).EFFECTIVITY_CONTROL := l_orig_item_rec.EFFECTIVITY_CONTROL;
210 -- already commented out --, CONFIG_MODEL_TYPE VARCHAR2(30) := FND_API.g_MISS_CHAR
211 g_in_item_tbl(0).Product_Family_Item_Id := l_orig_item_rec.Product_Family_Item_Id;
212 -- Costing
213 g_in_item_tbl(0).COSTING_ENABLED_FLAG := l_orig_item_rec.COSTING_ENABLED_FLAG;
214 g_in_item_tbl(0).INVENTORY_ASSET_FLAG := l_orig_item_rec.INVENTORY_ASSET_FLAG;
215 g_in_item_tbl(0).COST_OF_SALES_ACCOUNT := l_orig_item_rec.COST_OF_SALES_ACCOUNT;
216 g_in_item_tbl(0).DEFAULT_INCLUDE_IN_ROLLUP_FLAG := l_orig_item_rec.DEFAULT_INCLUDE_IN_ROLLUP_FLAG;
217 g_in_item_tbl(0).STD_LOT_SIZE := l_orig_item_rec.STD_LOT_SIZE;
218 g_in_item_tbl(0).CONFIG_MODEL_TYPE := l_orig_item_rec.CONFIG_MODEL_TYPE;
219 -- Enterprise Asset Management
220 g_in_item_tbl(0).EAM_ITEM_TYPE := l_orig_item_rec.EAM_ITEM_TYPE;
221 g_in_item_tbl(0).EAM_ACTIVITY_TYPE_CODE := l_orig_item_rec.EAM_ACTIVITY_TYPE_CODE;
222 g_in_item_tbl(0).EAM_ACTIVITY_CAUSE_CODE := l_orig_item_rec.EAM_ACTIVITY_CAUSE_CODE;
223 g_in_item_tbl(0).EAM_ACT_SHUTDOWN_STATUS := l_orig_item_rec.EAM_ACT_SHUTDOWN_STATUS;
224 g_in_item_tbl(0).EAM_ACT_NOTIFICATION_FLAG := l_orig_item_rec.EAM_ACT_NOTIFICATION_FLAG;
225 g_in_item_tbl(0).EAM_ACTIVITY_SOURCE_CODE := l_orig_item_rec.EAM_ACTIVITY_SOURCE_CODE;
226 -- Purchasing
227 g_in_item_tbl(0).PURCHASING_ITEM_FLAG := l_orig_item_rec.PURCHASING_ITEM_FLAG;
228 g_in_item_tbl(0).PURCHASING_ENABLED_FLAG := l_orig_item_rec.PURCHASING_ENABLED_FLAG;
229 g_in_item_tbl(0).BUYER_ID := l_orig_item_rec.BUYER_ID;
230 g_in_item_tbl(0).MUST_USE_APPROVED_VENDOR_FLAG := l_orig_item_rec.MUST_USE_APPROVED_VENDOR_FLAG;
231 g_in_item_tbl(0).PURCHASING_TAX_CODE := l_orig_item_rec.PURCHASING_TAX_CODE;
232 g_in_item_tbl(0).TAXABLE_FLAG := l_orig_item_rec.TAXABLE_FLAG;
233 g_in_item_tbl(0).RECEIVE_CLOSE_TOLERANCE := l_orig_item_rec.RECEIVE_CLOSE_TOLERANCE;
234 g_in_item_tbl(0).ALLOW_ITEM_DESC_UPDATE_FLAG := l_orig_item_rec.ALLOW_ITEM_DESC_UPDATE_FLAG;
235 g_in_item_tbl(0).INSPECTION_REQUIRED_FLAG := l_orig_item_rec.INSPECTION_REQUIRED_FLAG;
236 g_in_item_tbl(0).RECEIPT_REQUIRED_FLAG := l_orig_item_rec.RECEIPT_REQUIRED_FLAG;
237 g_in_item_tbl(0).MARKET_PRICE := l_orig_item_rec.MARKET_PRICE;
238 g_in_item_tbl(0).UN_NUMBER_ID := l_orig_item_rec.UN_NUMBER_ID;
239 g_in_item_tbl(0).HAZARD_CLASS_ID := l_orig_item_rec.HAZARD_CLASS_ID;
240 g_in_item_tbl(0).RFQ_REQUIRED_FLAG := l_orig_item_rec.RFQ_REQUIRED_FLAG;
241 g_in_item_tbl(0).LIST_PRICE_PER_UNIT := l_orig_item_rec.LIST_PRICE_PER_UNIT;
242 g_in_item_tbl(0).PRICE_TOLERANCE_PERCENT := l_orig_item_rec.PRICE_TOLERANCE_PERCENT;
243 g_in_item_tbl(0).ASSET_CATEGORY_ID := l_orig_item_rec.ASSET_CATEGORY_ID;
244 g_in_item_tbl(0).ROUNDING_FACTOR := l_orig_item_rec.ROUNDING_FACTOR;
245 g_in_item_tbl(0).UNIT_OF_ISSUE := l_orig_item_rec.UNIT_OF_ISSUE;
246 g_in_item_tbl(0).OUTSIDE_OPERATION_FLAG := l_orig_item_rec.OUTSIDE_OPERATION_FLAG;
247 g_in_item_tbl(0).OUTSIDE_OPERATION_UOM_TYPE := l_orig_item_rec.OUTSIDE_OPERATION_UOM_TYPE;
248 g_in_item_tbl(0).INVOICE_CLOSE_TOLERANCE := l_orig_item_rec.INVOICE_CLOSE_TOLERANCE;
249 g_in_item_tbl(0).ENCUMBRANCE_ACCOUNT := l_orig_item_rec.ENCUMBRANCE_ACCOUNT;
250 g_in_item_tbl(0).EXPENSE_ACCOUNT := l_orig_item_rec.EXPENSE_ACCOUNT;
251 g_in_item_tbl(0).QTY_RCV_EXCEPTION_CODE := l_orig_item_rec.QTY_RCV_EXCEPTION_CODE;
252 g_in_item_tbl(0).RECEIVING_ROUTING_ID := l_orig_item_rec.RECEIVING_ROUTING_ID;
253 g_in_item_tbl(0).QTY_RCV_TOLERANCE := l_orig_item_rec.QTY_RCV_TOLERANCE;
254 g_in_item_tbl(0).ENFORCE_SHIP_TO_LOCATION_CODE := l_orig_item_rec.ENFORCE_SHIP_TO_LOCATION_CODE;
255 g_in_item_tbl(0).ALLOW_SUBSTITUTE_RECEIPTS_FLAG := l_orig_item_rec.ALLOW_SUBSTITUTE_RECEIPTS_FLAG;
256 g_in_item_tbl(0).ALLOW_UNORDERED_RECEIPTS_FLAG := l_orig_item_rec.ALLOW_UNORDERED_RECEIPTS_FLAG;
257 g_in_item_tbl(0).ALLOW_EXPRESS_DELIVERY_FLAG := l_orig_item_rec.ALLOW_EXPRESS_DELIVERY_FLAG;
258 g_in_item_tbl(0).DAYS_EARLY_RECEIPT_ALLOWED := l_orig_item_rec.DAYS_EARLY_RECEIPT_ALLOWED;
259 g_in_item_tbl(0).DAYS_LATE_RECEIPT_ALLOWED := l_orig_item_rec.DAYS_LATE_RECEIPT_ALLOWED;
260 g_in_item_tbl(0).RECEIPT_DAYS_EXCEPTION_CODE := l_orig_item_rec.RECEIPT_DAYS_EXCEPTION_CODE;
261 -- Physical
262 g_in_item_tbl(0).WEIGHT_UOM_CODE := l_orig_item_rec.WEIGHT_UOM_CODE;
263 g_in_item_tbl(0).UNIT_WEIGHT := l_orig_item_rec.UNIT_WEIGHT;
264 g_in_item_tbl(0).VOLUME_UOM_CODE := l_orig_item_rec.VOLUME_UOM_CODE;
265 g_in_item_tbl(0).UNIT_VOLUME := l_orig_item_rec.UNIT_VOLUME;
266 g_in_item_tbl(0).CONTAINER_ITEM_FLAG := l_orig_item_rec.CONTAINER_ITEM_FLAG;
267 g_in_item_tbl(0).VEHICLE_ITEM_FLAG := l_orig_item_rec.VEHICLE_ITEM_FLAG;
268 g_in_item_tbl(0).MAXIMUM_LOAD_WEIGHT := l_orig_item_rec.MAXIMUM_LOAD_WEIGHT;
269 g_in_item_tbl(0).MINIMUM_FILL_PERCENT := l_orig_item_rec.MINIMUM_FILL_PERCENT;
270 g_in_item_tbl(0).INTERNAL_VOLUME := l_orig_item_rec.INTERNAL_VOLUME;
271 g_in_item_tbl(0).CONTAINER_TYPE_CODE := l_orig_item_rec.CONTAINER_TYPE_CODE;
272 g_in_item_tbl(0).COLLATERAL_FLAG := l_orig_item_rec.COLLATERAL_FLAG;
273 g_in_item_tbl(0).EVENT_FLAG := l_orig_item_rec.EVENT_FLAG;
274 g_in_item_tbl(0).EQUIPMENT_TYPE := l_orig_item_rec.EQUIPMENT_TYPE;
275 g_in_item_tbl(0).ELECTRONIC_FLAG := l_orig_item_rec.ELECTRONIC_FLAG;
276 g_in_item_tbl(0).DOWNLOADABLE_FLAG := l_orig_item_rec.DOWNLOADABLE_FLAG;
277 g_in_item_tbl(0).INDIVISIBLE_FLAG := l_orig_item_rec.INDIVISIBLE_FLAG;
278 g_in_item_tbl(0).DIMENSION_UOM_CODE := l_orig_item_rec.DIMENSION_UOM_CODE;
279 g_in_item_tbl(0).UNIT_LENGTH := l_orig_item_rec.UNIT_LENGTH;
280 g_in_item_tbl(0).UNIT_WIDTH := l_orig_item_rec.UNIT_WIDTH;
281 g_in_item_tbl(0).UNIT_HEIGHT := l_orig_item_rec.UNIT_HEIGHT;
282 --
283 g_in_item_tbl(0).INVENTORY_PLANNING_CODE := l_orig_item_rec.INVENTORY_PLANNING_CODE;
284 g_in_item_tbl(0).PLANNER_CODE := l_orig_item_rec.PLANNER_CODE;
285 g_in_item_tbl(0).PLANNING_MAKE_BUY_CODE := l_orig_item_rec.PLANNING_MAKE_BUY_CODE;
286 g_in_item_tbl(0).MIN_MINMAX_QUANTITY := l_orig_item_rec.MIN_MINMAX_QUANTITY;
287 g_in_item_tbl(0).MAX_MINMAX_QUANTITY := l_orig_item_rec.MAX_MINMAX_QUANTITY;
288 g_in_item_tbl(0).SAFETY_STOCK_BUCKET_DAYS := l_orig_item_rec.SAFETY_STOCK_BUCKET_DAYS;
289 g_in_item_tbl(0).CARRYING_COST := FND_API.G_MISS_NUM;
290 g_in_item_tbl(0).ORDER_COST := FND_API.G_MISS_NUM;
291 g_in_item_tbl(0).MRP_SAFETY_STOCK_PERCENT := l_orig_item_rec.MRP_SAFETY_STOCK_PERCENT;
292 g_in_item_tbl(0).MRP_SAFETY_STOCK_CODE := l_orig_item_rec.MRP_SAFETY_STOCK_CODE;
293 g_in_item_tbl(0).FIXED_ORDER_QUANTITY := l_orig_item_rec.FIXED_ORDER_QUANTITY;
294 g_in_item_tbl(0).FIXED_DAYS_SUPPLY := l_orig_item_rec.FIXED_DAYS_SUPPLY;
295 g_in_item_tbl(0).MINIMUM_ORDER_QUANTITY := l_orig_item_rec.MINIMUM_ORDER_QUANTITY;
296 g_in_item_tbl(0).MAXIMUM_ORDER_QUANTITY := l_orig_item_rec.MAXIMUM_ORDER_QUANTITY;
297 g_in_item_tbl(0).FIXED_LOT_MULTIPLIER := l_orig_item_rec.FIXED_LOT_MULTIPLIER;
298 g_in_item_tbl(0).SOURCE_TYPE := l_orig_item_rec.SOURCE_TYPE;
299 g_in_item_tbl(0).SOURCE_ORGANIZATION_ID := l_orig_item_rec.SOURCE_ORGANIZATION_ID;
300 g_in_item_tbl(0).SOURCE_SUBINVENTORY := l_orig_item_rec.SOURCE_SUBINVENTORY;
301 g_in_item_tbl(0).MRP_PLANNING_CODE := l_orig_item_rec.MRP_PLANNING_CODE;
302 g_in_item_tbl(0).ATO_FORECAST_CONTROL := l_orig_item_rec.ATO_FORECAST_CONTROL;
303 g_in_item_tbl(0).PLANNING_EXCEPTION_SET := l_orig_item_rec.PLANNING_EXCEPTION_SET;
304 g_in_item_tbl(0).SHRINKAGE_RATE := l_orig_item_rec.SHRINKAGE_RATE;
305 g_in_item_tbl(0).END_ASSEMBLY_PEGGING_FLAG := l_orig_item_rec.END_ASSEMBLY_PEGGING_FLAG;
306 g_in_item_tbl(0).ROUNDING_CONTROL_TYPE := l_orig_item_rec.ROUNDING_CONTROL_TYPE;
307 g_in_item_tbl(0).PLANNED_INV_POINT_FLAG := l_orig_item_rec.PLANNED_INV_POINT_FLAG;
308 g_in_item_tbl(0).CREATE_SUPPLY_FLAG := l_orig_item_rec.CREATE_SUPPLY_FLAG;
309 g_in_item_tbl(0).ACCEPTABLE_EARLY_DAYS := l_orig_item_rec.ACCEPTABLE_EARLY_DAYS;
310 g_in_item_tbl(0).MRP_CALCULATE_ATP_FLAG := l_orig_item_rec.MRP_CALCULATE_ATP_FLAG;
311 g_in_item_tbl(0).AUTO_REDUCE_MPS := l_orig_item_rec.AUTO_REDUCE_MPS;
312 g_in_item_tbl(0).REPETITIVE_PLANNING_FLAG := l_orig_item_rec.REPETITIVE_PLANNING_FLAG;
313 g_in_item_tbl(0).OVERRUN_PERCENTAGE := l_orig_item_rec.OVERRUN_PERCENTAGE;
314 g_in_item_tbl(0).ACCEPTABLE_RATE_DECREASE := l_orig_item_rec.ACCEPTABLE_RATE_DECREASE;
315 g_in_item_tbl(0).ACCEPTABLE_RATE_INCREASE := l_orig_item_rec.ACCEPTABLE_RATE_INCREASE;
316 g_in_item_tbl(0).PLANNING_TIME_FENCE_CODE := l_orig_item_rec.PLANNING_TIME_FENCE_CODE;
317 g_in_item_tbl(0).PLANNING_TIME_FENCE_DAYS := l_orig_item_rec.PLANNING_TIME_FENCE_DAYS;
318 g_in_item_tbl(0).DEMAND_TIME_FENCE_CODE := l_orig_item_rec.DEMAND_TIME_FENCE_CODE;
319 g_in_item_tbl(0).DEMAND_TIME_FENCE_DAYS := l_orig_item_rec.DEMAND_TIME_FENCE_DAYS;
320 g_in_item_tbl(0).RELEASE_TIME_FENCE_CODE := l_orig_item_rec.RELEASE_TIME_FENCE_CODE;
321 g_in_item_tbl(0).RELEASE_TIME_FENCE_DAYS := l_orig_item_rec.RELEASE_TIME_FENCE_DAYS;
322 g_in_item_tbl(0).SUBSTITUTION_WINDOW_CODE := l_orig_item_rec.SUBSTITUTION_WINDOW_CODE;
323 g_in_item_tbl(0).SUBSTITUTION_WINDOW_DAYS := l_orig_item_rec.SUBSTITUTION_WINDOW_DAYS;
324 -- Lead Times
325 g_in_item_tbl(0).PREPROCESSING_LEAD_TIME := l_orig_item_rec.PREPROCESSING_LEAD_TIME;
326 g_in_item_tbl(0).FULL_LEAD_TIME := l_orig_item_rec.FULL_LEAD_TIME;
327 g_in_item_tbl(0).POSTPROCESSING_LEAD_TIME := l_orig_item_rec.POSTPROCESSING_LEAD_TIME;
328 g_in_item_tbl(0).FIXED_LEAD_TIME := l_orig_item_rec.FIXED_LEAD_TIME;
329 g_in_item_tbl(0).VARIABLE_LEAD_TIME := l_orig_item_rec.VARIABLE_LEAD_TIME;
330 g_in_item_tbl(0).CUM_MANUFACTURING_LEAD_TIME := l_orig_item_rec.CUM_MANUFACTURING_LEAD_TIME;
331 g_in_item_tbl(0).CUMULATIVE_TOTAL_LEAD_TIME := l_orig_item_rec.CUMULATIVE_TOTAL_LEAD_TIME;
332 g_in_item_tbl(0).LEAD_TIME_LOT_SIZE := l_orig_item_rec.LEAD_TIME_LOT_SIZE;
333 -- WIP
334 g_in_item_tbl(0).BUILD_IN_WIP_FLAG := l_orig_item_rec.BUILD_IN_WIP_FLAG;
335 g_in_item_tbl(0).WIP_SUPPLY_TYPE := l_orig_item_rec.WIP_SUPPLY_TYPE;
336 g_in_item_tbl(0).WIP_SUPPLY_SUBINVENTORY := l_orig_item_rec.WIP_SUPPLY_SUBINVENTORY;
337 g_in_item_tbl(0).WIP_SUPPLY_LOCATOR_ID := l_orig_item_rec.WIP_SUPPLY_LOCATOR_ID;
338 g_in_item_tbl(0).OVERCOMPLETION_TOLERANCE_TYPE := l_orig_item_rec.OVERCOMPLETION_TOLERANCE_TYPE;
339 g_in_item_tbl(0).OVERCOMPLETION_TOLERANCE_VALUE := l_orig_item_rec.OVERCOMPLETION_TOLERANCE_VALUE;
340 g_in_item_tbl(0).INVENTORY_CARRY_PENALTY := l_orig_item_rec.INVENTORY_CARRY_PENALTY;
341 g_in_item_tbl(0).OPERATION_SLACK_PENALTY := l_orig_item_rec.OPERATION_SLACK_PENALTY;
342 -- Order Management
343 g_in_item_tbl(0).CUSTOMER_ORDER_FLAG := l_orig_item_rec.CUSTOMER_ORDER_FLAG;
344 g_in_item_tbl(0).CUSTOMER_ORDER_ENABLED_FLAG := l_orig_item_rec.CUSTOMER_ORDER_ENABLED_FLAG;
345 g_in_item_tbl(0).INTERNAL_ORDER_FLAG := l_orig_item_rec.INTERNAL_ORDER_FLAG;
346 g_in_item_tbl(0).INTERNAL_ORDER_ENABLED_FLAG := l_orig_item_rec.INTERNAL_ORDER_ENABLED_FLAG;
347 g_in_item_tbl(0).SHIPPABLE_ITEM_FLAG := l_orig_item_rec.SHIPPABLE_ITEM_FLAG;
348 g_in_item_tbl(0).SO_TRANSACTIONS_FLAG := l_orig_item_rec.SO_TRANSACTIONS_FLAG;
349 g_in_item_tbl(0).PICKING_RULE_ID := l_orig_item_rec.PICKING_RULE_ID;
350 g_in_item_tbl(0).PICK_COMPONENTS_FLAG := l_orig_item_rec.PICK_COMPONENTS_FLAG;
351 g_in_item_tbl(0).REPLENISH_TO_ORDER_FLAG := l_orig_item_rec.REPLENISH_TO_ORDER_FLAG;
352 g_in_item_tbl(0).ATP_FLAG := l_orig_item_rec.ATP_FLAG;
353 g_in_item_tbl(0).ATP_COMPONENTS_FLAG := l_orig_item_rec.ATP_COMPONENTS_FLAG;
354 g_in_item_tbl(0).ATP_RULE_ID := l_orig_item_rec.ATP_RULE_ID;
355 g_in_item_tbl(0).SHIP_MODEL_COMPLETE_FLAG := l_orig_item_rec.SHIP_MODEL_COMPLETE_FLAG;
356 g_in_item_tbl(0).DEFAULT_SHIPPING_ORG := l_orig_item_rec.DEFAULT_SHIPPING_ORG;
357 g_in_item_tbl(0).DEFAULT_SO_SOURCE_TYPE := l_orig_item_rec.DEFAULT_SO_SOURCE_TYPE;
358 g_in_item_tbl(0).RETURNABLE_FLAG := l_orig_item_rec.RETURNABLE_FLAG;
359 g_in_item_tbl(0).RETURN_INSPECTION_REQUIREMENT := l_orig_item_rec.RETURN_INSPECTION_REQUIREMENT;
360 g_in_item_tbl(0).OVER_SHIPMENT_TOLERANCE := l_orig_item_rec.OVER_SHIPMENT_TOLERANCE;
361 g_in_item_tbl(0).UNDER_SHIPMENT_TOLERANCE := l_orig_item_rec.UNDER_SHIPMENT_TOLERANCE;
362 g_in_item_tbl(0).OVER_RETURN_TOLERANCE := l_orig_item_rec.OVER_RETURN_TOLERANCE;
363 g_in_item_tbl(0).UNDER_RETURN_TOLERANCE := l_orig_item_rec.UNDER_RETURN_TOLERANCE;
364 g_in_item_tbl(0).FINANCING_ALLOWED_FLAG := l_orig_item_rec.FINANCING_ALLOWED_FLAG;
365 g_in_item_tbl(0).VOL_DISCOUNT_EXEMPT_FLAG := l_orig_item_rec.VOL_DISCOUNT_EXEMPT_FLAG;
366 g_in_item_tbl(0).COUPON_EXEMPT_FLAG := l_orig_item_rec.COUPON_EXEMPT_FLAG;
367 g_in_item_tbl(0).INVOICEABLE_ITEM_FLAG := l_orig_item_rec.INVOICEABLE_ITEM_FLAG;
368 g_in_item_tbl(0).INVOICE_ENABLED_FLAG := l_orig_item_rec.INVOICE_ENABLED_FLAG;
369 g_in_item_tbl(0).ACCOUNTING_RULE_ID := l_orig_item_rec.ACCOUNTING_RULE_ID;
370 g_in_item_tbl(0).INVOICING_RULE_ID := l_orig_item_rec.INVOICING_RULE_ID;
371 g_in_item_tbl(0).TAX_CODE := l_orig_item_rec.TAX_CODE;
372 g_in_item_tbl(0).SALES_ACCOUNT := l_orig_item_rec.SALES_ACCOUNT;
373 g_in_item_tbl(0).PAYMENT_TERMS_ID := l_orig_item_rec.PAYMENT_TERMS_ID;
374 -- Service
375 g_in_item_tbl(0).CONTRACT_ITEM_TYPE_CODE := l_orig_item_rec.CONTRACT_ITEM_TYPE_CODE;
376 g_in_item_tbl(0).SERVICE_DURATION_PERIOD_CODE := l_orig_item_rec.SERVICE_DURATION_PERIOD_CODE;
377 g_in_item_tbl(0).SERVICE_DURATION := l_orig_item_rec.SERVICE_DURATION;
378 g_in_item_tbl(0).COVERAGE_SCHEDULE_ID := l_orig_item_rec.COVERAGE_SCHEDULE_ID;
379 g_in_item_tbl(0).SUBSCRIPTION_DEPEND_FLAG := l_orig_item_rec.SUBSCRIPTION_DEPEND_FLAG;
380 g_in_item_tbl(0).SERV_IMPORTANCE_LEVEL := l_orig_item_rec.SERV_IMPORTANCE_LEVEL;
381 g_in_item_tbl(0).SERV_REQ_ENABLED_CODE := l_orig_item_rec.SERV_REQ_ENABLED_CODE;
382 g_in_item_tbl(0).COMMS_ACTIVATION_REQD_FLAG := l_orig_item_rec.COMMS_ACTIVATION_REQD_FLAG;
383 g_in_item_tbl(0).SERVICEABLE_PRODUCT_FLAG := l_orig_item_rec.SERVICEABLE_PRODUCT_FLAG;
384 g_in_item_tbl(0).MATERIAL_BILLABLE_FLAG := l_orig_item_rec.MATERIAL_BILLABLE_FLAG;
385 g_in_item_tbl(0).SERV_BILLING_ENABLED_FLAG := l_orig_item_rec.SERV_BILLING_ENABLED_FLAG;
386 g_in_item_tbl(0).DEFECT_TRACKING_ON_FLAG := l_orig_item_rec.DEFECT_TRACKING_ON_FLAG;
387 g_in_item_tbl(0).RECOVERED_PART_DISP_CODE := l_orig_item_rec.RECOVERED_PART_DISP_CODE;
388 g_in_item_tbl(0).COMMS_NL_TRACKABLE_FLAG := l_orig_item_rec.COMMS_NL_TRACKABLE_FLAG;
389 g_in_item_tbl(0).ASSET_CREATION_CODE := l_orig_item_rec.ASSET_CREATION_CODE;
390 -- already commented out --, IB_ITEM_INSTANCE_CLASS VARCHAR2(30) := FND_API.g_MISS_CHAR
391 g_in_item_tbl(0).IB_ITEM_INSTANCE_CLASS := l_orig_item_rec.IB_ITEM_INSTANCE_CLASS;
392 g_in_item_tbl(0).SERVICE_STARTING_DELAY := l_orig_item_rec.SERVICE_STARTING_DELAY;
393 -- Web Option
394 g_in_item_tbl(0).WEB_STATUS := l_orig_item_rec.WEB_STATUS;
395 g_in_item_tbl(0).ORDERABLE_ON_WEB_FLAG := l_orig_item_rec.ORDERABLE_ON_WEB_FLAG;
396 g_in_item_tbl(0).BACK_ORDERABLE_FLAG := l_orig_item_rec.BACK_ORDERABLE_FLAG;
397 g_in_item_tbl(0).MINIMUM_LICENSE_QUANTITY := l_orig_item_rec.MINIMUM_LICENSE_QUANTITY;
398 -- Descriptive flex
399 g_in_item_tbl(0).Attribute_Category := l_orig_item_rec.Attribute_Category;
400 g_in_item_tbl(0).Attribute1 := l_orig_item_rec.Attribute1;
401 g_in_item_tbl(0).Attribute2 := l_orig_item_rec.Attribute2;
402 g_in_item_tbl(0).Attribute3 := l_orig_item_rec.Attribute3;
403 g_in_item_tbl(0).Attribute4 := l_orig_item_rec.Attribute4;
404 g_in_item_tbl(0).Attribute5 := l_orig_item_rec.Attribute5;
405 g_in_item_tbl(0).Attribute6 := l_orig_item_rec.Attribute6;
406 g_in_item_tbl(0).Attribute7 := l_orig_item_rec.Attribute7;
407 g_in_item_tbl(0).Attribute8 := l_orig_item_rec.Attribute8;
408 g_in_item_tbl(0).Attribute9 := l_orig_item_rec.Attribute9;
409 g_in_item_tbl(0).Attribute10 := l_orig_item_rec.Attribute10;
410 g_in_item_tbl(0).Attribute11 := l_orig_item_rec.Attribute11;
411 g_in_item_tbl(0).Attribute12 := l_orig_item_rec.Attribute12;
412 g_in_item_tbl(0).Attribute13 := l_orig_item_rec.Attribute13;
413 g_in_item_tbl(0).Attribute14 := l_orig_item_rec.Attribute14;
414 g_in_item_tbl(0).Attribute15 := l_orig_item_rec.Attribute15;
415 -- Global Descriptive flex
416 g_in_item_tbl(0).Global_Attribute_Category := l_orig_item_rec.Global_Attribute_Category;
417 g_in_item_tbl(0).Global_Attribute1 := l_orig_item_rec.Global_Attribute1;
418 g_in_item_tbl(0).Global_Attribute2 := l_orig_item_rec.Global_Attribute2;
419 g_in_item_tbl(0).Global_Attribute3 := l_orig_item_rec.Global_Attribute3;
420 g_in_item_tbl(0).Global_Attribute4 := l_orig_item_rec.Global_Attribute4;
421 g_in_item_tbl(0).Global_Attribute5 := l_orig_item_rec.Global_Attribute5;
422 g_in_item_tbl(0).Global_Attribute6 := l_orig_item_rec.Global_Attribute6;
423 g_in_item_tbl(0).Global_Attribute7 := l_orig_item_rec.Global_Attribute7;
424 g_in_item_tbl(0).Global_Attribute8 := l_orig_item_rec.Global_Attribute8;
425 g_in_item_tbl(0).Global_Attribute9 := l_orig_item_rec.Global_Attribute9;
426 g_in_item_tbl(0).Global_Attribute10 := l_orig_item_rec.Global_Attribute10;
427
428 x_return_status := G_RET_STS_SUCCESS;
429 developer_debug ('::2960442::initialize_item_info::end');
430 EXCEPTION
431 WHEN OTHERS THEN
432 IF c_copy_item_info%ISOPEN THEN
433 CLOSE c_copy_item_info;
434 END IF;
435 RAISE;
436 END initialize_item_info;
437
438
439 PROCEDURE initialize_template_info (p_template_id IN NUMBER
440 ,p_template_name IN VARCHAR2
441 ,p_organization_id IN NUMBER
442 ,p_organization_code IN VARCHAR2
443 ,x_return_status OUT NOCOPY VARCHAR2
444 ,x_msg_count OUT NOCOPY NUMBER
445 ) IS
446 ----------------------------------------------------------------------------
447 -- Start OF comments
448 -- API name : initialize_template_info
449 -- TYPE : Private
450 -- Pre-reqs : None
451 -- FUNCTION : Initialize the Item record with the Template values of the
452 -- template_id specified
453 --
454 -- Parameters:
455 -- IN : p_template_id IN NUMBER (required)
456 -- : p_inventory_item_id IN NUMBER (required)
457 -- : p_organization_id IN NUMBER (required)
458 --
459 --
460 -- OUT : x_return_status OUT VARCHAR2
461 -- x_msg_count OUT NUMBER
462 --
463 ----------------------------------------------------------------------------
464 CURSOR c_get_context_org (cp_template_id IN NUMBER) IS
465 SELECT context_organization_id
466 FROM mtl_item_templates mit
467 WHERE mit.template_id = cp_template_id;
468
469 CURSOR c_get_template_attributes (cp_template_id IN NUMBER) IS
470 SELECT attribute_name, attribute_value
471 FROM mtl_item_templ_attributes
472 WHERE template_id = cp_template_id
473 AND enabled_flag = 'Y'
474 AND attribute_name IN
475 ( SELECT a.attribute_name
476 FROM mtl_item_attributes a
477 WHERE NVL(a.status_control_code, 3) <> 1
478 AND a.control_level IN (1, 2)
479 AND a.attribute_group_id_gui IS NOT NULL
480 AND a.attribute_name NOT IN
481 ('MTL_SYSTEM_ITEMS.BASE_ITEM_ID',
482 'MTL_SYSTEM_ITEMS.WIP_SUPPLY_LOCATOR_ID',
483 'MTL_SYSTEM_ITEMS.WIP_SUPPLY_SUBINVENTORY',
484 'MTL_SYSTEM_ITEMS.BASE_WARRANTY_SERVICE_ID',
485 'MTL_SYSTEM_ITEMS.PLANNER_CODE',
486 'MTL_SYSTEM_ITEMS.ENCUMBRANCE_ACCOUNT',
487 'MTL_SYSTEM_ITEMS.EXPENSE_ACCOUNT',
488 'MTL_SYSTEM_ITEMS.SALES_ACCOUNT',
489 'MTL_SYSTEM_ITEMS.COST_OF_SALES_ACCOUNT',
490 'MTL_SYSTEM_ITEMS.PLANNING_EXCEPTION_SET')
491 );
492
493 --
494 -- Attributes that can be applied only through the Org Specific templates.
495 --
496 CURSOR c_get_org_template_attributes (cp_template_id IN NUMBER) IS
497 SELECT attribute_name, attribute_value
498 FROM mtl_item_templ_attributes
499 WHERE template_id = cp_template_id
500 AND enabled_flag = 'Y'
501 AND attribute_name IN
502 ( SELECT a.attribute_name
503 FROM mtl_item_attributes a
504 WHERE NVL(a.status_control_code, 3) <> 1
505 AND a.control_level IN (1, 2)
506 AND a.attribute_group_id_gui IS NOT NULL
507 AND a.attribute_name IN
508 ('MTL_SYSTEM_ITEMS.BASE_ITEM_ID',
509 'MTL_SYSTEM_ITEMS.WIP_SUPPLY_LOCATOR_ID',
510 'MTL_SYSTEM_ITEMS.WIP_SUPPLY_SUBINVENTORY',
511 'MTL_SYSTEM_ITEMS.BASE_WARRANTY_SERVICE_ID',
512 'MTL_SYSTEM_ITEMS.PLANNER_CODE',
513 'MTL_SYSTEM_ITEMS.ENCUMBRANCE_ACCOUNT',
514 'MTL_SYSTEM_ITEMS.EXPENSE_ACCOUNT',
515 'MTL_SYSTEM_ITEMS.SALES_ACCOUNT',
516 'MTL_SYSTEM_ITEMS.COST_OF_SALES_ACCOUNT',
517 'MTL_SYSTEM_ITEMS.PLANNING_EXCEPTION_SET')
518 );
519
520 CURSOR c_get_global_flex_fields (cp_template_id IN NUMBER) IS
521 SELECT GLOBAL_ATTRIBUTE_CATEGORY,
522 GLOBAL_ATTRIBUTE1,
523 GLOBAL_ATTRIBUTE2,
524 GLOBAL_ATTRIBUTE3,
525 GLOBAL_ATTRIBUTE4,
526 GLOBAL_ATTRIBUTE5,
527 GLOBAL_ATTRIBUTE6,
528 GLOBAL_ATTRIBUTE7,
529 GLOBAL_ATTRIBUTE8,
530 GLOBAL_ATTRIBUTE9,
531 GLOBAL_ATTRIBUTE10
532 FROM MTL_ITEM_TEMPLATES MIT
533 WHERE MIT.template_id = cp_template_id;
534
535 l_org_id mtl_item_templates.context_organization_id%TYPE;
536
537 BEGIN
538 developer_debug ('::2960442::initialize_template_info::start');
539 l_org_id := NULL;
540 OPEN c_get_context_org (cp_template_id => p_template_id);
541 FETCH c_get_context_org INTO l_org_id;
542 CLOSE c_get_context_org;
543 IF ( (l_org_id is NOT NULL) AND (l_org_id <> p_organization_id) ) THEN
544 EGO_Item_Msg.Add_Error_Message
545 (p_entity_index => 1
546 ,p_application_short_name => 'EGO'
547 ,p_message_name => 'EGO_INVALID_TEMPLATE_ORG'
548 ,p_token_name1 => 'TEMPLATE_NAME'
549 ,p_token_value1 => p_template_name
550 ,p_translate1 => FALSE
551 ,p_token_name2 => 'ORGANIZATION_CODE'
552 ,p_token_value2 => p_organization_code
553 ,p_translate2 => FALSE
554 ,p_token_name3 => NULL
555 ,p_token_value3 => NULL
556 ,p_translate3 => FALSE
557 );
558 x_return_status := G_RET_STS_ERROR;
559 RETURN;
560 END IF; -- c_get_context_org%NOTFOUND
561
562 ------------------------------------
563 -- Set item record attribute values
564 ------------------------------------
565 FOR cr IN c_get_template_attributes (cp_template_id => p_template_id) LOOP
566 developer_debug(' Setting item template Attribute Name ' || cr.attribute_name || ' attribute value ' || cr.attribute_value );
567 IF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ACCEPTABLE_EARLY_DAYS' THEN
568 g_in_item_tbl(0).ACCEPTABLE_EARLY_DAYS := cr.ATTRIBUTE_VALUE;
569 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ACCEPTABLE_RATE_DECREASE' THEN
570 g_in_item_tbl(0).ACCEPTABLE_RATE_DECREASE := cr.ATTRIBUTE_VALUE;
571 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ACCEPTABLE_RATE_INCREASE' THEN
572 g_in_item_tbl(0).ACCEPTABLE_RATE_INCREASE := cr.ATTRIBUTE_VALUE;
573 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ACCOUNTING_RULE_ID' THEN
574 g_in_item_tbl(0).ACCOUNTING_RULE_ID := cr.ATTRIBUTE_VALUE;
575 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ALLOWED_UNITS_LOOKUP_CODE' THEN
576 g_in_item_tbl(0).ALLOWED_UNITS_LOOKUP_CODE := cr.ATTRIBUTE_VALUE;
577 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ALLOW_EXPRESS_DELIVERY_FLAG' THEN
578 g_in_item_tbl(0).ALLOW_EXPRESS_DELIVERY_FLAG := cr.ATTRIBUTE_VALUE;
579 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ALLOW_ITEM_DESC_UPDATE_FLAG' THEN
580 g_in_item_tbl(0).ALLOW_ITEM_DESC_UPDATE_FLAG := cr.ATTRIBUTE_VALUE;
581 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ALLOW_SUBSTITUTE_RECEIPTS_FLAG' THEN
582 g_in_item_tbl(0).ALLOW_SUBSTITUTE_RECEIPTS_FLAG := cr.ATTRIBUTE_VALUE;
583 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ALLOW_UNORDERED_RECEIPTS_FLAG' THEN
584 g_in_item_tbl(0).ALLOW_UNORDERED_RECEIPTS_FLAG := cr.ATTRIBUTE_VALUE;
585 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ASSET_CATEGORY_ID' THEN
586 g_in_item_tbl(0).ASSET_CATEGORY_ID := cr.ATTRIBUTE_VALUE;
587 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ATP_COMPONENTS_FLAG' THEN
588 g_in_item_tbl(0).ATP_COMPONENTS_FLAG := cr.ATTRIBUTE_VALUE;
589 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ATP_FLAG' THEN
590 g_in_item_tbl(0).ATP_FLAG := cr.ATTRIBUTE_VALUE;
591 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ATP_RULE_ID' THEN
592 g_in_item_tbl(0).ATP_RULE_ID := cr.ATTRIBUTE_VALUE;
593 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.AUTO_LOT_ALPHA_PREFIX' THEN
594 g_in_item_tbl(0).AUTO_LOT_ALPHA_PREFIX := cr.ATTRIBUTE_VALUE;
595 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.AUTO_REDUCE_MPS' THEN
596 g_in_item_tbl(0).AUTO_REDUCE_MPS := cr.ATTRIBUTE_VALUE;
597 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.AUTO_SERIAL_ALPHA_PREFIX' THEN
598 g_in_item_tbl(0).AUTO_SERIAL_ALPHA_PREFIX := cr.ATTRIBUTE_VALUE;
599 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.BOM_ENABLED_FLAG' THEN
600 g_in_item_tbl(0).BOM_ENABLED_FLAG := cr.ATTRIBUTE_VALUE;
601 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.BOM_ITEM_TYPE' THEN
602 g_in_item_tbl(0).BOM_ITEM_TYPE := cr.ATTRIBUTE_VALUE;
603 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.BUILD_IN_WIP_FLAG' THEN
604 g_in_item_tbl(0).BUILD_IN_WIP_FLAG := cr.ATTRIBUTE_VALUE;
605 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.BUYER_ID' THEN
606 g_in_item_tbl(0).BUYER_ID := cr.ATTRIBUTE_VALUE;
607 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.CARRYING_COST' THEN
608 g_in_item_tbl(0).CARRYING_COST := cr.ATTRIBUTE_VALUE;
609 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.COLLATERAL_FLAG' THEN
610 g_in_item_tbl(0).COLLATERAL_FLAG := cr.ATTRIBUTE_VALUE;
611 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.COSTING_ENABLED_FLAG' THEN
612 g_in_item_tbl(0).COSTING_ENABLED_FLAG := cr.ATTRIBUTE_VALUE;
613 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.COVERAGE_SCHEDULE_ID' THEN
614 g_in_item_tbl(0).COVERAGE_SCHEDULE_ID := cr.ATTRIBUTE_VALUE;
615 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.CUMULATIVE_TOTAL_LEAD_TIME' THEN
616 g_in_item_tbl(0).CUMULATIVE_TOTAL_LEAD_TIME := cr.ATTRIBUTE_VALUE;
617 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.CUM_MANUFACTURING_LEAD_TIME' THEN
618 g_in_item_tbl(0).CUM_MANUFACTURING_LEAD_TIME := cr.ATTRIBUTE_VALUE;
619 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.CUSTOMER_ORDER_ENABLED_FLAG' THEN
620 g_in_item_tbl(0).CUSTOMER_ORDER_ENABLED_FLAG := cr.ATTRIBUTE_VALUE;
621 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.CUSTOMER_ORDER_FLAG' THEN
622 g_in_item_tbl(0).CUSTOMER_ORDER_FLAG := cr.ATTRIBUTE_VALUE;
623 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.CYCLE_COUNT_ENABLED_FLAG' THEN
624 g_in_item_tbl(0).CYCLE_COUNT_ENABLED_FLAG := cr.ATTRIBUTE_VALUE;
625 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DAYS_EARLY_RECEIPT_ALLOWED' THEN
626 g_in_item_tbl(0).DAYS_EARLY_RECEIPT_ALLOWED := cr.ATTRIBUTE_VALUE;
627 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DAYS_LATE_RECEIPT_ALLOWED' THEN
628 g_in_item_tbl(0).DAYS_LATE_RECEIPT_ALLOWED := cr.ATTRIBUTE_VALUE;
629 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DEFAULT_INCLUDE_IN_ROLLUP_FLAG' THEN
630 g_in_item_tbl(0).DEFAULT_INCLUDE_IN_ROLLUP_FLAG := cr.ATTRIBUTE_VALUE;
631 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DEFAULT_SHIPPING_ORG' THEN
632 g_in_item_tbl(0).DEFAULT_SHIPPING_ORG := cr.ATTRIBUTE_VALUE;
633 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DEMAND_TIME_FENCE_CODE' THEN
634 g_in_item_tbl(0).DEMAND_TIME_FENCE_CODE := cr.ATTRIBUTE_VALUE;
635 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DEMAND_TIME_FENCE_DAYS' THEN
636 g_in_item_tbl(0).DEMAND_TIME_FENCE_DAYS := cr.ATTRIBUTE_VALUE;
637 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.END_ASSEMBLY_PEGGING_FLAG' THEN
638 g_in_item_tbl(0).END_ASSEMBLY_PEGGING_FLAG := cr.ATTRIBUTE_VALUE;
639 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ENFORCE_SHIP_TO_LOCATION_CODE' THEN
640 g_in_item_tbl(0).ENFORCE_SHIP_TO_LOCATION_CODE := cr.ATTRIBUTE_VALUE;
641 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.EXPENSE_ACCOUNT' THEN
642 g_in_item_tbl(0).EXPENSE_ACCOUNT := cr.ATTRIBUTE_VALUE;
643 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.EXPENSE_BILLABLE_FLAG' THEN
644 -- g_in_item_tbl(0).EXPENSE_BILLABLE_FLAG := cr.ATTRIBUTE_VALUE;
645 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.FIXED_DAYS_SUPPLY' THEN
646 g_in_item_tbl(0).FIXED_DAYS_SUPPLY := cr.ATTRIBUTE_VALUE;
647 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.FIXED_LEAD_TIME' THEN
648 g_in_item_tbl(0).FIXED_LEAD_TIME := cr.ATTRIBUTE_VALUE;
649 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.FIXED_LOT_MULTIPLIER' THEN
650 g_in_item_tbl(0).FIXED_LOT_MULTIPLIER := cr.ATTRIBUTE_VALUE;
651 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.FIXED_ORDER_QUANTITY' THEN
652 g_in_item_tbl(0).FIXED_ORDER_QUANTITY := cr.ATTRIBUTE_VALUE;
653 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.FULL_LEAD_TIME' THEN
654 g_in_item_tbl(0).FULL_LEAD_TIME := cr.ATTRIBUTE_VALUE;
655 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.HAZARD_CLASS_ID' THEN
656 g_in_item_tbl(0).HAZARD_CLASS_ID := cr.ATTRIBUTE_VALUE;
657 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INSPECTION_REQUIRED_FLAG' THEN
658 g_in_item_tbl(0).INSPECTION_REQUIRED_FLAG := cr.ATTRIBUTE_VALUE;
659 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INTERNAL_ORDER_ENABLED_FLAG' THEN
660 g_in_item_tbl(0).INTERNAL_ORDER_ENABLED_FLAG := cr.ATTRIBUTE_VALUE;
661 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INTERNAL_ORDER_FLAG' THEN
662 g_in_item_tbl(0).INTERNAL_ORDER_FLAG := cr.ATTRIBUTE_VALUE;
663 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_ASSET_FLAG' THEN
664 g_in_item_tbl(0).INVENTORY_ASSET_FLAG := cr.ATTRIBUTE_VALUE;
665 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_FLAG' THEN
666 g_in_item_tbl(0).INVENTORY_ITEM_FLAG := cr.ATTRIBUTE_VALUE;
667 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE' THEN
668 g_in_item_tbl(0).INVENTORY_ITEM_STATUS_CODE := cr.ATTRIBUTE_VALUE;
669 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_PLANNING_CODE' THEN
670 g_in_item_tbl(0).INVENTORY_PLANNING_CODE := cr.ATTRIBUTE_VALUE;
671 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVOICEABLE_ITEM_FLAG' THEN
672 g_in_item_tbl(0).INVOICEABLE_ITEM_FLAG := cr.ATTRIBUTE_VALUE;
673 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVOICE_CLOSE_TOLERANCE' THEN
674 g_in_item_tbl(0).INVOICE_CLOSE_TOLERANCE := cr.ATTRIBUTE_VALUE;
675 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVOICE_ENABLED_FLAG' THEN
676 g_in_item_tbl(0).INVOICE_ENABLED_FLAG := cr.ATTRIBUTE_VALUE;
677 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVOICING_RULE_ID' THEN
678 g_in_item_tbl(0).INVOICING_RULE_ID := cr.ATTRIBUTE_VALUE;
679 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ITEM_TYPE' THEN
680 g_in_item_tbl(0).ITEM_TYPE := cr.ATTRIBUTE_VALUE;
681 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.LEAD_TIME_LOT_SIZE' THEN
682 g_in_item_tbl(0).LEAD_TIME_LOT_SIZE := cr.ATTRIBUTE_VALUE;
683 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.LIST_PRICE_PER_UNIT' THEN
684 g_in_item_tbl(0).LIST_PRICE_PER_UNIT := cr.ATTRIBUTE_VALUE;
685 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.LOCATION_CONTROL_CODE' THEN
686 g_in_item_tbl(0).LOCATION_CONTROL_CODE := cr.ATTRIBUTE_VALUE;
687 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.LOT_CONTROL_CODE' THEN
688 g_in_item_tbl(0).LOT_CONTROL_CODE := cr.ATTRIBUTE_VALUE;
689 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MARKET_PRICE' THEN
690 g_in_item_tbl(0).MARKET_PRICE := cr.ATTRIBUTE_VALUE;
691 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MATERIAL_BILLABLE_FLAG' THEN
692 g_in_item_tbl(0).MATERIAL_BILLABLE_FLAG := cr.ATTRIBUTE_VALUE;
693 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MAXIMUM_ORDER_QUANTITY' THEN
694 g_in_item_tbl(0).MAXIMUM_ORDER_QUANTITY := cr.ATTRIBUTE_VALUE;
695 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MAX_MINMAX_QUANTITY' THEN
696 g_in_item_tbl(0).MAX_MINMAX_QUANTITY := cr.ATTRIBUTE_VALUE;
697 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MAX_WARRANTY_AMOUNT' THEN
698 -- g_in_item_tbl(0).MAX_WARRANTY_AMOUNT := cr.ATTRIBUTE_VALUE;
699 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MINIMUM_ORDER_QUANTITY' THEN
700 g_in_item_tbl(0).MINIMUM_ORDER_QUANTITY := cr.ATTRIBUTE_VALUE;
701 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MIN_MINMAX_QUANTITY' THEN
702 g_in_item_tbl(0).MIN_MINMAX_QUANTITY := cr.ATTRIBUTE_VALUE;
703 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MRP_CALCULATE_ATP_FLAG' THEN
704 g_in_item_tbl(0).MRP_CALCULATE_ATP_FLAG := cr.ATTRIBUTE_VALUE;
705 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MRP_PLANNING_CODE' THEN
706 g_in_item_tbl(0).MRP_PLANNING_CODE := cr.ATTRIBUTE_VALUE;
707 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MRP_SAFETY_STOCK_CODE' THEN
708 g_in_item_tbl(0).MRP_SAFETY_STOCK_CODE := cr.ATTRIBUTE_VALUE;
709 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MRP_SAFETY_STOCK_PERCENT' THEN
710 g_in_item_tbl(0).MRP_SAFETY_STOCK_PERCENT := cr.ATTRIBUTE_VALUE;
711 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MTL_TRANSACTIONS_ENABLED_FLAG' THEN
712 g_in_item_tbl(0).MTL_TRANSACTIONS_ENABLED_FLAG := cr.ATTRIBUTE_VALUE;
713 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MUST_USE_APPROVED_VENDOR_FLAG' THEN
714 g_in_item_tbl(0).MUST_USE_APPROVED_VENDOR_FLAG := cr.ATTRIBUTE_VALUE;
715 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.NEGATIVE_MEASUREMENT_ERROR' THEN
716 g_in_item_tbl(0).NEGATIVE_MEASUREMENT_ERROR := cr.ATTRIBUTE_VALUE;
717 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.NEW_REVISION_CODE' THEN
718 -- g_in_item_tbl(0).NEW_REVISION_CODE := cr.ATTRIBUTE_VALUE;
719 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ORDER_COST' THEN
720 g_in_item_tbl(0).ORDER_COST := cr.ATTRIBUTE_VALUE;
721 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.OUTSIDE_OPERATION_FLAG' THEN
722 g_in_item_tbl(0).OUTSIDE_OPERATION_FLAG := cr.ATTRIBUTE_VALUE;
723 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.OUTSIDE_OPERATION_UOM_TYPE' THEN
724 g_in_item_tbl(0).OUTSIDE_OPERATION_UOM_TYPE := cr.ATTRIBUTE_VALUE;
725 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.OVERRUN_PERCENTAGE' THEN
726 g_in_item_tbl(0).OVERRUN_PERCENTAGE := cr.ATTRIBUTE_VALUE;
727 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PAYMENT_TERMS_ID' THEN
728 g_in_item_tbl(0).PAYMENT_TERMS_ID := cr.ATTRIBUTE_VALUE;
729 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PICKING_RULE_ID' THEN
730 g_in_item_tbl(0).PICKING_RULE_ID := cr.ATTRIBUTE_VALUE;
731 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PICK_COMPONENTS_FLAG' THEN
732 g_in_item_tbl(0).PICK_COMPONENTS_FLAG := cr.ATTRIBUTE_VALUE;
733 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PLANNING_MAKE_BUY_CODE' THEN
734 g_in_item_tbl(0).PLANNING_MAKE_BUY_CODE := cr.ATTRIBUTE_VALUE;
735 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PLANNING_TIME_FENCE_CODE' THEN
736 g_in_item_tbl(0).PLANNING_TIME_FENCE_CODE := cr.ATTRIBUTE_VALUE;
737 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PLANNING_TIME_FENCE_DAYS' THEN
738 g_in_item_tbl(0).PLANNING_TIME_FENCE_DAYS := cr.ATTRIBUTE_VALUE;
739 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.POSITIVE_MEASUREMENT_ERROR' THEN
740 g_in_item_tbl(0).POSITIVE_MEASUREMENT_ERROR := cr.ATTRIBUTE_VALUE;
741 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.POSTPROCESSING_LEAD_TIME' THEN
742 g_in_item_tbl(0).POSTPROCESSING_LEAD_TIME := cr.ATTRIBUTE_VALUE;
743 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PREPROCESSING_LEAD_TIME' THEN
744 g_in_item_tbl(0).PREPROCESSING_LEAD_TIME := cr.ATTRIBUTE_VALUE;
745 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PREVENTIVE_MAINTENANCE_FLAG' THEN
746 -- g_in_item_tbl(0).PREVENTIVE_MAINTENANCE_FLAG := cr.ATTRIBUTE_VALUE;
747 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PRICE_TOLERANCE_PERCENT' THEN
748 g_in_item_tbl(0).PRICE_TOLERANCE_PERCENT := cr.ATTRIBUTE_VALUE;
749 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PRIMARY_SPECIALIST_ID' THEN
750 -- g_in_item_tbl(0).PRIMARY_SPECIALIST_ID := cr.ATTRIBUTE_VALUE;
751 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PRORATE_SERVICE_FLAG' THEN
752 -- g_in_item_tbl(0).PRORATE_SERVICE_FLAG := cr.ATTRIBUTE_VALUE;
753 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PURCHASING_ENABLED_FLAG' THEN
754 g_in_item_tbl(0).PURCHASING_ENABLED_FLAG := cr.ATTRIBUTE_VALUE;
755 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PURCHASING_ITEM_FLAG' THEN
756 g_in_item_tbl(0).PURCHASING_ITEM_FLAG := cr.ATTRIBUTE_VALUE;
757 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.QTY_RCV_EXCEPTION_CODE' THEN
758 g_in_item_tbl(0).QTY_RCV_EXCEPTION_CODE := cr.ATTRIBUTE_VALUE;
759 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.QTY_RCV_TOLERANCE' THEN
760 g_in_item_tbl(0).QTY_RCV_TOLERANCE := cr.ATTRIBUTE_VALUE;
761 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RECEIPT_DAYS_EXCEPTION_CODE' THEN
762 g_in_item_tbl(0).RECEIPT_DAYS_EXCEPTION_CODE := cr.ATTRIBUTE_VALUE;
763 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RECEIPT_REQUIRED_FLAG' THEN
764 g_in_item_tbl(0).RECEIPT_REQUIRED_FLAG := cr.ATTRIBUTE_VALUE;
765 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RECEIVE_CLOSE_TOLERANCE' THEN
766 g_in_item_tbl(0).RECEIVE_CLOSE_TOLERANCE := cr.ATTRIBUTE_VALUE;
767 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RECEIVING_ROUTING_ID' THEN
768 g_in_item_tbl(0).RECEIVING_ROUTING_ID := cr.ATTRIBUTE_VALUE;
769 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.REPETITIVE_PLANNING_FLAG' THEN
770 g_in_item_tbl(0).REPETITIVE_PLANNING_FLAG := cr.ATTRIBUTE_VALUE;
771 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.REPLENISH_TO_ORDER_FLAG' THEN
772 g_in_item_tbl(0).REPLENISH_TO_ORDER_FLAG := cr.ATTRIBUTE_VALUE;
773 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RESERVABLE_TYPE' THEN
774 g_in_item_tbl(0).RESERVABLE_TYPE := cr.ATTRIBUTE_VALUE;
775 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RESPONSE_TIME_PERIOD_CODE' THEN
776 -- g_in_item_tbl(0).RESPONSE_TIME_PERIOD_CODE := cr.ATTRIBUTE_VALUE;
777 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RESPONSE_TIME_VALUE' THEN
778 -- g_in_item_tbl(0).RESPONSE_TIME_VALUE := cr.ATTRIBUTE_VALUE;
779 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RESTRICT_LOCATORS_CODE' THEN
780 g_in_item_tbl(0).RESTRICT_LOCATORS_CODE := cr.ATTRIBUTE_VALUE;
781 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RESTRICT_SUBINVENTORIES_CODE' THEN
782 g_in_item_tbl(0).RESTRICT_SUBINVENTORIES_CODE := cr.ATTRIBUTE_VALUE;
783 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RETURNABLE_FLAG' THEN
784 g_in_item_tbl(0).RETURNABLE_FLAG := cr.ATTRIBUTE_VALUE;
785 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RETURN_INSPECTION_REQUIREMENT' THEN
786 g_in_item_tbl(0).RETURN_INSPECTION_REQUIREMENT := cr.ATTRIBUTE_VALUE;
787 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.REVISION_QTY_CONTROL_CODE' THEN
788 g_in_item_tbl(0).REVISION_QTY_CONTROL_CODE := cr.ATTRIBUTE_VALUE;
789 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RFQ_REQUIRED_FLAG' THEN
790 g_in_item_tbl(0).RFQ_REQUIRED_FLAG := cr.ATTRIBUTE_VALUE;
791 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ROUNDING_CONTROL_TYPE' THEN
792 g_in_item_tbl(0).ROUNDING_CONTROL_TYPE := cr.ATTRIBUTE_VALUE;
793 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ROUNDING_FACTOR' THEN
794 g_in_item_tbl(0).ROUNDING_FACTOR := cr.ATTRIBUTE_VALUE;
795 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SAFETY_STOCK_BUCKET_DAYS' THEN
796 g_in_item_tbl(0).SAFETY_STOCK_BUCKET_DAYS := cr.ATTRIBUTE_VALUE;
797 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SECONDARY_SPECIALIST_ID' THEN
798 -- g_in_item_tbl(0).SECONDARY_SPECIALIST_ID := cr.ATTRIBUTE_VALUE;
799 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SERIAL_NUMBER_CONTROL_CODE' THEN
800 g_in_item_tbl(0).SERIAL_NUMBER_CONTROL_CODE := cr.ATTRIBUTE_VALUE;
801 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SERVICEABLE_COMPONENT_FLAG' THEN
802 -- g_in_item_tbl(0).SERVICEABLE_COMPONENT_FLAG := cr.ATTRIBUTE_VALUE;
803 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SERVICEABLE_ITEM_CLASS_ID' THEN
804 -- g_in_item_tbl(0).SERVICEABLE_ITEM_CLASS_ID := cr.ATTRIBUTE_VALUE;
805 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SERVICEABLE_PRODUCT_FLAG' THEN
806 g_in_item_tbl(0).SERVICEABLE_PRODUCT_FLAG := cr.ATTRIBUTE_VALUE;
807 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SERVICE_DURATION' THEN
808 g_in_item_tbl(0).SERVICE_DURATION := cr.ATTRIBUTE_VALUE;
809 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SERVICE_DURATION_PERIOD_CODE' THEN
810 g_in_item_tbl(0).SERVICE_DURATION_PERIOD_CODE := cr.ATTRIBUTE_VALUE;
811 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SERVICE_STARTING_DELAY' THEN
812 g_in_item_tbl(0).SERVICE_STARTING_DELAY := cr.ATTRIBUTE_VALUE;
813 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SHELF_LIFE_CODE' THEN
814 g_in_item_tbl(0).SHELF_LIFE_CODE := cr.ATTRIBUTE_VALUE;
815 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SHELF_LIFE_DAYS' THEN
816 g_in_item_tbl(0).SHELF_LIFE_DAYS := cr.ATTRIBUTE_VALUE;
817 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SHIPPABLE_ITEM_FLAG' THEN
818 g_in_item_tbl(0).SHIPPABLE_ITEM_FLAG := cr.ATTRIBUTE_VALUE;
819 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SHIP_MODEL_COMPLETE_FLAG' THEN
820 g_in_item_tbl(0).SHIP_MODEL_COMPLETE_FLAG := cr.ATTRIBUTE_VALUE;
821 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SHRINKAGE_RATE' THEN
822 g_in_item_tbl(0).SHRINKAGE_RATE := cr.ATTRIBUTE_VALUE;
823 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SOURCE_ORGANIZATION_ID' THEN
824 g_in_item_tbl(0).SOURCE_ORGANIZATION_ID := cr.ATTRIBUTE_VALUE;
825 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SOURCE_SUBINVENTORY' THEN
826 g_in_item_tbl(0).SOURCE_SUBINVENTORY := cr.ATTRIBUTE_VALUE;
827 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SOURCE_TYPE' THEN
828 g_in_item_tbl(0).SOURCE_TYPE := cr.ATTRIBUTE_VALUE;
829 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SO_TRANSACTIONS_FLAG' THEN
830 g_in_item_tbl(0).SO_TRANSACTIONS_FLAG := cr.ATTRIBUTE_VALUE;
831 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.START_AUTO_LOT_NUMBER' THEN
832 g_in_item_tbl(0).START_AUTO_LOT_NUMBER := cr.ATTRIBUTE_VALUE;
833 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.START_AUTO_SERIAL_NUMBER' THEN
834 g_in_item_tbl(0).START_AUTO_SERIAL_NUMBER := cr.ATTRIBUTE_VALUE;
835 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.STD_LOT_SIZE' THEN
836 g_in_item_tbl(0).STD_LOT_SIZE := cr.ATTRIBUTE_VALUE;
837 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.STOCK_ENABLED_FLAG' THEN
838 g_in_item_tbl(0).STOCK_ENABLED_FLAG := cr.ATTRIBUTE_VALUE;
839 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.TAXABLE_FLAG' THEN
840 g_in_item_tbl(0).TAXABLE_FLAG := cr.ATTRIBUTE_VALUE;
841 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PURCHASING_TAX_CODE' THEN
842 g_in_item_tbl(0).PURCHASING_TAX_CODE := cr.ATTRIBUTE_VALUE;
843 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.TAX_CODE' THEN
844 g_in_item_tbl(0).TAX_CODE := cr.ATTRIBUTE_VALUE;
845 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.TIME_BILLABLE_FLAG' THEN
846 -- g_in_item_tbl(0).TIME_BILLABLE_FLAG := cr.ATTRIBUTE_VALUE;
847 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.UNIT_OF_ISSUE' THEN
848 g_in_item_tbl(0).UNIT_OF_ISSUE := cr.ATTRIBUTE_VALUE;
849 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.UNIT_VOLUME' THEN
850 g_in_item_tbl(0).UNIT_VOLUME := cr.ATTRIBUTE_VALUE;
851 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.UNIT_WEIGHT' THEN
852 g_in_item_tbl(0).UNIT_WEIGHT := cr.ATTRIBUTE_VALUE;
853 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.UN_NUMBER_ID' THEN
854 g_in_item_tbl(0).UN_NUMBER_ID := cr.ATTRIBUTE_VALUE;
855 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.VARIABLE_LEAD_TIME' THEN
856 g_in_item_tbl(0).VARIABLE_LEAD_TIME := cr.ATTRIBUTE_VALUE;
857 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.VOLUME_UOM_CODE' THEN
858 g_in_item_tbl(0).VOLUME_UOM_CODE := cr.ATTRIBUTE_VALUE;
859 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.WARRANTY_VENDOR_ID' THEN
860 -- g_in_item_tbl(0).WARRANTY_VENDOR_ID := cr.ATTRIBUTE_VALUE;
861 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.WEIGHT_UOM_CODE' THEN
862 g_in_item_tbl(0).WEIGHT_UOM_CODE := cr.ATTRIBUTE_VALUE;
863 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.WIP_SUPPLY_TYPE' THEN
864 g_in_item_tbl(0).WIP_SUPPLY_TYPE := cr.ATTRIBUTE_VALUE;
865 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ATO_FORECAST_CONTROL' THEN
866 g_in_item_tbl(0).ATO_FORECAST_CONTROL := cr.ATTRIBUTE_VALUE;
867 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DESCRIPTION' THEN
868 g_in_item_tbl(0).DESCRIPTION := cr.ATTRIBUTE_VALUE;
869 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RELEASE_TIME_FENCE_CODE' THEN
870 g_in_item_tbl(0).RELEASE_TIME_FENCE_CODE := cr.ATTRIBUTE_VALUE;
871 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RELEASE_TIME_FENCE_DAYS' THEN
872 g_in_item_tbl(0).RELEASE_TIME_FENCE_DAYS := cr.ATTRIBUTE_VALUE;
873 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.CONTAINER_ITEM_FLAG' THEN
874 g_in_item_tbl(0).CONTAINER_ITEM_FLAG := cr.ATTRIBUTE_VALUE;
875 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.CONTAINER_TYPE_CODE' THEN
876 g_in_item_tbl(0).CONTAINER_TYPE_CODE := cr.ATTRIBUTE_VALUE;
877 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INTERNAL_VOLUME' THEN
878 g_in_item_tbl(0).INTERNAL_VOLUME := cr.ATTRIBUTE_VALUE;
879 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MAXIMUM_LOAD_WEIGHT' THEN
880 g_in_item_tbl(0).MAXIMUM_LOAD_WEIGHT := cr.ATTRIBUTE_VALUE;
881 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MINIMUM_FILL_PERCENT' THEN
882 g_in_item_tbl(0).MINIMUM_FILL_PERCENT := cr.ATTRIBUTE_VALUE;
883 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.VEHICLE_ITEM_FLAG' THEN
884 g_in_item_tbl(0).VEHICLE_ITEM_FLAG := cr.ATTRIBUTE_VALUE;
885 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.CHECK_SHORTAGES_FLAG' THEN
886 g_in_item_tbl(0).CHECK_SHORTAGES_FLAG := cr.ATTRIBUTE_VALUE;
887 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.EFFECTIVITY_CONTROL' THEN
888 g_in_item_tbl(0).EFFECTIVITY_CONTROL := cr.ATTRIBUTE_VALUE;
889 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.OVERCOMPLETION_TOLERANCE_TYPE' THEN
890 g_in_item_tbl(0).OVERCOMPLETION_TOLERANCE_TYPE := cr.ATTRIBUTE_VALUE;
891 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.OVERCOMPLETION_TOLERANCE_VALUE' THEN
892 g_in_item_tbl(0).OVERCOMPLETION_TOLERANCE_VALUE := cr.ATTRIBUTE_VALUE;
893 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.OVER_SHIPMENT_TOLERANCE' THEN
894 g_in_item_tbl(0).OVER_SHIPMENT_TOLERANCE := cr.ATTRIBUTE_VALUE;
895 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.UNDER_SHIPMENT_TOLERANCE' THEN
896 g_in_item_tbl(0).UNDER_SHIPMENT_TOLERANCE := cr.ATTRIBUTE_VALUE;
897 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.OVER_RETURN_TOLERANCE' THEN
898 g_in_item_tbl(0).OVER_RETURN_TOLERANCE := cr.ATTRIBUTE_VALUE;
899 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.UNDER_RETURN_TOLERANCE' THEN
900 g_in_item_tbl(0).UNDER_RETURN_TOLERANCE := cr.ATTRIBUTE_VALUE;
901 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.EQUIPMENT_TYPE' THEN
902 g_in_item_tbl(0).EQUIPMENT_TYPE := cr.ATTRIBUTE_VALUE;
903 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.RECOVERED_PART_DISP_CODE' THEN
904 g_in_item_tbl(0).RECOVERED_PART_DISP_CODE := cr.ATTRIBUTE_VALUE;
905 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DEFECT_TRACKING_ON_FLAG' THEN
906 g_in_item_tbl(0).DEFECT_TRACKING_ON_FLAG := cr.ATTRIBUTE_VALUE;
907 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.EVENT_FLAG' THEN
908 g_in_item_tbl(0).EVENT_FLAG := cr.ATTRIBUTE_VALUE;
909 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ELECTRONIC_FLAG' THEN
910 g_in_item_tbl(0).ELECTRONIC_FLAG := cr.ATTRIBUTE_VALUE;
911 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DOWNLOADABLE_FLAG' THEN
912 g_in_item_tbl(0).DOWNLOADABLE_FLAG := cr.ATTRIBUTE_VALUE;
913 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.VOL_DISCOUNT_EXEMPT_FLAG' THEN
914 g_in_item_tbl(0).VOL_DISCOUNT_EXEMPT_FLAG := cr.ATTRIBUTE_VALUE;
915 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.COUPON_EXEMPT_FLAG' THEN
916 g_in_item_tbl(0).COUPON_EXEMPT_FLAG := cr.ATTRIBUTE_VALUE;
917 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.COMMS_NL_TRACKABLE_FLAG' THEN
918 g_in_item_tbl(0).COMMS_NL_TRACKABLE_FLAG := cr.ATTRIBUTE_VALUE;
919 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ASSET_CREATION_CODE' THEN
920 g_in_item_tbl(0).ASSET_CREATION_CODE := cr.ATTRIBUTE_VALUE;
921 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.COMMS_ACTIVATION_REQD_FLAG' THEN
922 g_in_item_tbl(0).COMMS_ACTIVATION_REQD_FLAG := cr.ATTRIBUTE_VALUE;
923 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ORDERABLE_ON_WEB_FLAG' THEN
924 g_in_item_tbl(0).ORDERABLE_ON_WEB_FLAG := cr.ATTRIBUTE_VALUE;
925 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.BACK_ORDERABLE_FLAG' THEN
926 g_in_item_tbl(0).BACK_ORDERABLE_FLAG := cr.ATTRIBUTE_VALUE;
927 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.WEB_STATUS' THEN
928 g_in_item_tbl(0).WEB_STATUS := cr.ATTRIBUTE_VALUE;
929 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INDIVISIBLE_FLAG' THEN
930 g_in_item_tbl(0).INDIVISIBLE_FLAG := cr.ATTRIBUTE_VALUE;
931 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DIMENSION_UOM_CODE' THEN
932 g_in_item_tbl(0).DIMENSION_UOM_CODE := cr.ATTRIBUTE_VALUE;
933 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.UNIT_LENGTH' THEN
934 g_in_item_tbl(0).UNIT_LENGTH := cr.ATTRIBUTE_VALUE;
935 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.UNIT_WIDTH' THEN
936 g_in_item_tbl(0).UNIT_WIDTH := cr.ATTRIBUTE_VALUE;
937 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.UNIT_HEIGHT' THEN
938 g_in_item_tbl(0).UNIT_HEIGHT := cr.ATTRIBUTE_VALUE;
939 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.BULK_PICKED_FLAG' THEN
940 g_in_item_tbl(0).BULK_PICKED_FLAG := cr.ATTRIBUTE_VALUE;
941 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.LOT_STATUS_ENABLED' THEN
942 g_in_item_tbl(0).LOT_STATUS_ENABLED := cr.ATTRIBUTE_VALUE;
943 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DEFAULT_LOT_STATUS_ID' THEN
944 g_in_item_tbl(0).DEFAULT_LOT_STATUS_ID := cr.ATTRIBUTE_VALUE;
945 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SERIAL_STATUS_ENABLED' THEN
946 g_in_item_tbl(0).SERIAL_STATUS_ENABLED := cr.ATTRIBUTE_VALUE;
947 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DEFAULT_SERIAL_STATUS_ID' THEN
948 g_in_item_tbl(0).DEFAULT_SERIAL_STATUS_ID := cr.ATTRIBUTE_VALUE;
949 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.LOT_SPLIT_ENABLED' THEN
950 g_in_item_tbl(0).LOT_SPLIT_ENABLED := cr.ATTRIBUTE_VALUE;
951 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.LOT_MERGE_ENABLED' THEN
952 g_in_item_tbl(0).LOT_MERGE_ENABLED := cr.ATTRIBUTE_VALUE;
953 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.INVENTORY_CARRY_PENALTY' THEN
954 g_in_item_tbl(0).INVENTORY_CARRY_PENALTY := cr.ATTRIBUTE_VALUE;
955 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.OPERATION_SLACK_PENALTY' THEN
956 g_in_item_tbl(0).OPERATION_SLACK_PENALTY := cr.ATTRIBUTE_VALUE;
957 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.FINANCING_ALLOWED_FLAG' THEN
958 g_in_item_tbl(0).FINANCING_ALLOWED_FLAG := cr.ATTRIBUTE_VALUE;
959 -- Primary Unit of Measure is now maintained via the PRIMARY_UOM_CODE column.
960 --
961 --IF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PRIMARY_UNIT_OF_MEASURE' then g_in_item_tbl(0).PRIMARY_UNIT_OF_MEASURE := cr.ATTRIBUTE_VALUE; END IF;
962 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PRIMARY_UOM_CODE' THEN
963 g_in_item_tbl(0).PRIMARY_UOM_CODE := cr.ATTRIBUTE_VALUE;
964 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.EAM_ITEM_TYPE' THEN
965 g_in_item_tbl(0).EAM_ITEM_TYPE := cr.ATTRIBUTE_VALUE;
966 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.EAM_ACTIVITY_TYPE_CODE' THEN
967 g_in_item_tbl(0).EAM_ACTIVITY_TYPE_CODE := cr.ATTRIBUTE_VALUE;
968 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.EAM_ACTIVITY_CAUSE_CODE' THEN
969 g_in_item_tbl(0).EAM_ACTIVITY_CAUSE_CODE := cr.ATTRIBUTE_VALUE;
970 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.EAM_ACT_NOTIFICATION_FLAG' THEN
971 g_in_item_tbl(0).EAM_ACT_NOTIFICATION_FLAG := cr.ATTRIBUTE_VALUE;
972 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.EAM_ACT_SHUTDOWN_STATUS' THEN
973 g_in_item_tbl(0).EAM_ACT_SHUTDOWN_STATUS := cr.ATTRIBUTE_VALUE;
974 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DUAL_UOM_CONTROL' THEN
975 g_in_item_tbl(0).DUAL_UOM_CONTROL := cr.ATTRIBUTE_VALUE;
976 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SECONDARY_UOM_CODE' THEN
977 g_in_item_tbl(0).SECONDARY_UOM_CODE := cr.ATTRIBUTE_VALUE;
978 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DUAL_UOM_DEVIATION_HIGH' THEN
979 g_in_item_tbl(0).DUAL_UOM_DEVIATION_HIGH := cr.ATTRIBUTE_VALUE;
980 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DUAL_UOM_DEVIATION_LOW' THEN
981 g_in_item_tbl(0).DUAL_UOM_DEVIATION_LOW := cr.ATTRIBUTE_VALUE;
982 --
983 -- Service Item flag attribute is no longer supported for DML.
984 --
985 --IF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SERVICE_ITEM_FLAG' THEN g_in_item_tbl(0).SERVICE_ITEM_FLAG := cr.ATTRIBUTE_VALUE; END IF;
986 --IF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.VENDOR_WARRANTY_FLAG' THEN g_in_item_tbl(0).VENDOR_WARRANTY_FLAG := cr.ATTRIBUTE_VALUE; END IF;
987 --IF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.USAGE_ITEM_FLAG' THEN g_in_item_tbl(0).USAGE_ITEM_FLAG := cr.ATTRIBUTE_VALUE; END IF;
988 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.CONTRACT_ITEM_TYPE_CODE' THEN
989 g_in_item_tbl(0).CONTRACT_ITEM_TYPE_CODE := cr.ATTRIBUTE_VALUE;
990 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SUBSCRIPTION_DEPEND_FLAG' THEN
991 g_in_item_tbl(0).SUBSCRIPTION_DEPEND_FLAG := cr.ATTRIBUTE_VALUE;
992 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SERV_REQ_ENABLED_CODE' THEN
993 g_in_item_tbl(0).SERV_REQ_ENABLED_CODE := cr.ATTRIBUTE_VALUE;
994 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SERV_BILLING_ENABLED_FLAG' THEN
995 g_in_item_tbl(0).SERV_BILLING_ENABLED_FLAG := cr.ATTRIBUTE_VALUE;
996 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SERV_IMPORTANCE_LEVEL' THEN
997 g_in_item_tbl(0).SERV_IMPORTANCE_LEVEL := cr.ATTRIBUTE_VALUE;
998 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PLANNED_INV_POINT_FLAG' THEN
999 g_in_item_tbl(0).PLANNED_INV_POINT_FLAG := cr.ATTRIBUTE_VALUE;
1000 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.LOT_TRANSLATE_ENABLED' THEN
1001 g_in_item_tbl(0).LOT_TRANSLATE_ENABLED := cr.ATTRIBUTE_VALUE;
1002 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.DEFAULT_SO_SOURCE_TYPE' THEN
1003 g_in_item_tbl(0).DEFAULT_SO_SOURCE_TYPE := cr.ATTRIBUTE_VALUE;
1004 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.CREATE_SUPPLY_FLAG' THEN
1005 g_in_item_tbl(0).CREATE_SUPPLY_FLAG := cr.ATTRIBUTE_VALUE;
1006 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SUBSTITUTION_WINDOW_CODE' THEN
1007 g_in_item_tbl(0).SUBSTITUTION_WINDOW_CODE := cr.ATTRIBUTE_VALUE;
1008 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.LOT_SUBSTITUTION_ENABLED' THEN
1009 g_in_item_tbl(0).LOT_SUBSTITUTION_ENABLED := cr.ATTRIBUTE_VALUE;
1010 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.MINIMUM_LICENSE_QUANTITY' THEN
1011 g_in_item_tbl(0).MINIMUM_LICENSE_QUANTITY := cr.ATTRIBUTE_VALUE;
1012 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.EAM_ACTIVITY_SOURCE_CODE' THEN
1013 g_in_item_tbl(0).EAM_ACTIVITY_SOURCE_CODE := cr.ATTRIBUTE_VALUE;
1014 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.IB_ITEM_INSTANCE_CLASS' THEN
1015 g_in_item_tbl(0).IB_ITEM_INSTANCE_CLASS := cr.ATTRIBUTE_VALUE;
1016 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.CONFIG_MODEL_TYPE' THEN
1017 g_in_item_tbl(0).CONFIG_MODEL_TYPE := cr.ATTRIBUTE_VALUE;
1018 END IF; -- cr.ATTRIBUTE_NAME
1019 END LOOP; -- cursor c_get_template_attributes
1020
1021 IF ( (l_org_id is NOT NULL) AND (l_org_id = p_organization_id) ) THEN
1022 FOR cr IN c_get_org_template_attributes (cp_template_id => p_template_id) LOOP
1023 developer_debug(' Setting ORG template Attribute Name ' || cr.attribute_name || ' attribute value ' || cr.attribute_value );
1024 IF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.BASE_ITEM_ID' THEN
1025 g_in_item_tbl(0).BASE_ITEM_ID := cr.ATTRIBUTE_VALUE;
1026 -- ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.BASE_WARRANTY_SERVICE_ID' THEN
1027 -- g_in_item_tbl(0).BASE_WARRANTY_SERVICE_ID := cr.ATTRIBUTE_VALUE;
1028 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.COST_OF_SALES_ACCOUNT' THEN
1029 g_in_item_tbl(0).COST_OF_SALES_ACCOUNT := cr.ATTRIBUTE_VALUE;
1030 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.ENCUMBRANCE_ACCOUNT' THEN
1031 g_in_item_tbl(0).ENCUMBRANCE_ACCOUNT := cr.ATTRIBUTE_VALUE;
1032 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.EXPENSE_ACCOUNT' THEN
1033 g_in_item_tbl(0).EXPENSE_ACCOUNT := cr.ATTRIBUTE_VALUE;
1034 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PLANNER_CODE' THEN
1035 g_in_item_tbl(0).PLANNER_CODE := cr.ATTRIBUTE_VALUE;
1036 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.PLANNING_EXCEPTION_SET' THEN
1037 g_in_item_tbl(0).PLANNING_EXCEPTION_SET := cr.ATTRIBUTE_VALUE;
1038 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.SALES_ACCOUNT' THEN
1039 g_in_item_tbl(0).SALES_ACCOUNT := cr.ATTRIBUTE_VALUE;
1040 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.WIP_SUPPLY_LOCATOR_ID' THEN
1041 g_in_item_tbl(0).WIP_SUPPLY_LOCATOR_ID := cr.ATTRIBUTE_VALUE;
1042 ELSIF cr.ATTRIBUTE_NAME = 'MTL_SYSTEM_ITEMS.WIP_SUPPLY_SUBINVENTORY' THEN
1043 g_in_item_tbl(0).WIP_SUPPLY_SUBINVENTORY := cr.ATTRIBUTE_VALUE;
1044 END IF;
1045 END LOOP; -- cursor c_get_org_template_attributes
1046 END IF; -- cursor c_get_org_template_attributes
1047 /***
1048 -- this is the forms logic, handled by IOI??
1049 IF ( g_in_item_tbl(0).CONTRACT_ITEM_TYPE_CODE = 'SERVICE' ) THEN
1050 g_in_item_tbl(0).SERVICE_ITEM_FLAG := 'Y';
1051 g_in_item_tbl(0).VENDOR_WARRANTY_FLAG := 'N';
1052 g_in_item_tbl(0).USAGE_ITEM_FLAG := NULL;
1053 ELSIF ( g_in_item_tbl(0).CONTRACT_ITEM_TYPE_CODE = 'WARRANTY' ) THEN
1054 g_in_item_tbl(0).SERVICE_ITEM_FLAG := 'Y';
1055 g_in_item_tbl(0).VENDOR_WARRANTY_FLAG := 'Y';
1056 g_in_item_tbl(0).USAGE_ITEM_FLAG := NULL;
1057 ELSIF ( g_in_item_tbl(0).CONTRACT_ITEM_TYPE_CODE = 'USAGE' ) THEN
1058 g_in_item_tbl(0).SERVICE_ITEM_FLAG := 'N';
1059 g_in_item_tbl(0).VENDOR_WARRANTY_FLAG := 'N';
1060 g_in_item_tbl(0).USAGE_ITEM_FLAG := 'Y';
1061 ELSE
1062 g_in_item_tbl(0).SERVICE_ITEM_FLAG := 'N';
1063 g_in_item_tbl(0).VENDOR_WARRANTY_FLAG := 'N';
1064 g_in_item_tbl(0).USAGE_ITEM_FLAG := NULL;
1065 END IF;
1066 ***/
1067 -- setting the flexible attributes here.
1068 FOR cr IN c_get_global_flex_fields (cp_template_id => p_template_id) LOOP
1069 g_in_item_tbl(0).Global_Attribute_Category := NVL(cr.Global_Attribute_Category,g_in_item_tbl(0).Global_Attribute_Category);
1070 g_in_item_tbl(0).Global_Attribute1 := NVL(cr.Global_Attribute1,g_in_item_tbl(0).Global_Attribute1);
1071 g_in_item_tbl(0).Global_Attribute2 := NVL(cr.Global_Attribute2,g_in_item_tbl(0).Global_Attribute2);
1072 g_in_item_tbl(0).Global_Attribute3 := NVL(cr.Global_Attribute3,g_in_item_tbl(0).Global_Attribute3);
1073 g_in_item_tbl(0).Global_Attribute4 := NVL(cr.Global_Attribute4,g_in_item_tbl(0).Global_Attribute4);
1074 g_in_item_tbl(0).Global_Attribute5 := NVL(cr.Global_Attribute5,g_in_item_tbl(0).Global_Attribute5);
1075 g_in_item_tbl(0).Global_Attribute6 := NVL(cr.Global_Attribute6,g_in_item_tbl(0).Global_Attribute6);
1076 g_in_item_tbl(0).Global_Attribute7 := NVL(cr.Global_Attribute7,g_in_item_tbl(0).Global_Attribute7);
1077 g_in_item_tbl(0).Global_Attribute8 := NVL(cr.Global_Attribute8,g_in_item_tbl(0).Global_Attribute8);
1078 g_in_item_tbl(0).Global_Attribute9 := NVL(cr.Global_Attribute9,g_in_item_tbl(0).Global_Attribute9);
1079 g_in_item_tbl(0).Global_Attribute10 := NVL(cr.Global_Attribute10,g_in_item_tbl(0).Global_Attribute10);
1080 END LOOP;
1081 x_return_status := G_RET_STS_SUCCESS;
1082 developer_debug ('::2960442::initialize_template_info::end');
1083 EXCEPTION
1084 WHEN OTHERS THEN
1085 IF c_get_context_org%ISOPEN THEN
1086 CLOSE c_get_context_org;
1087 END IF;
1088 IF c_get_template_attributes%ISOPEN THEN
1089 CLOSE c_get_template_attributes;
1090 END IF;
1091 IF c_get_org_template_attributes%ISOPEN THEN
1092 CLOSE c_get_org_template_attributes;
1093 END IF;
1094 IF c_get_global_flex_fields%ISOPEN THEN
1095 CLOSE c_get_global_flex_fields;
1096 END IF;
1097 RAISE;
1098 END initialize_template_info;
1099
1100
1101 -- -----------------------------------------------------------------------------
1102 -- API Name: Update_Item_Lifecycle
1103 -- -----------------------------------------------------------------------------
1104
1105 Procedure Process_Item_Lifecycle(
1106 P_API_VERSION IN NUMBER,
1107 P_INIT_MSG_LIST IN VARCHAR2,
1108 P_COMMIT IN VARCHAR2,
1109 P_INVENTORY_ITEM_ID IN NUMBER,
1110 P_ORGANIZATION_ID IN NUMBER,
1111 P_CATALOG_GROUP_ID IN NUMBER,
1112 P_LIFECYCLE_ID IN NUMBER,
1113 P_CURRENT_PHASE_ID IN NUMBER,
1114 P_ITEM_STATUS IN VARCHAR2,
1115 P_TRANSACTION_TYPE IN VARCHAR2,
1116 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1117 X_MSG_COUNT OUT NOCOPY NUMBER
1118 )
1119 IS
1120 BEGIN
1121 SAVEPOINT Process_Item_Lifecycle;
1122 developer_debug ('::2960442::Process_Item_Lifecycle::start');
1123 X_RETURN_STATUS := FND_API.g_RET_STS_SUCCESS;
1124 X_MSG_COUNT := 0;
1125
1126 IF (P_TRANSACTION_TYPE = G_CREATE_TRANSACTION_TYPE) THEN
1127 Create_Item_Lifecycle(
1128 P_API_VERSION => P_API_VERSION,
1129 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
1130 P_COMMIT => P_COMMIT,
1131 P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
1132 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
1133 P_LIFECYCLE_ID => P_LIFECYCLE_ID,
1134 P_CURRENT_PHASE_ID => P_CURRENT_PHASE_ID,
1135 P_ITEM_STATUS => P_ITEM_STATUS,
1136 X_RETURN_STATUS => X_RETURN_STATUS,
1137 X_MSG_COUNT => X_MSG_COUNT
1138 );
1139 ELSIF (P_TRANSACTION_TYPE = G_UPDATE_TRANSACTION_TYPE) THEN
1140 Update_Item_Lifecycle(
1141 P_API_VERSION => P_API_VERSION,
1142 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
1143 P_COMMIT => P_COMMIT,
1144 P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
1145 P_ORGANIZATION_ID => P_ORGANIZATION_ID,
1146 P_CATALOG_GROUP_ID => P_CATALOG_GROUP_ID,
1147 P_LIFECYCLE_ID => P_LIFECYCLE_ID,
1148 P_CURRENT_PHASE_ID => P_CURRENT_PHASE_ID,
1149 P_ITEM_STATUS => P_ITEM_STATUS,
1150 X_RETURN_STATUS => X_RETURN_STATUS,
1151 X_MSG_COUNT => X_MSG_COUNT
1152 );
1153 END IF;
1154 developer_debug ('::2960442::Process_Item_Lifecycle::end');
1155 EXCEPTION
1156 WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
1157 ROLLBACK TO Process_Item_Lifecycle;
1158 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1159
1160 WHEN others THEN
1161 ROLLBACK TO Process_Item_Lifecycle;
1162 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1163
1164 END Process_Item_Lifecycle;
1165
1166 Procedure Create_Item_Lifecycle(
1167 P_API_VERSION IN NUMBER,
1168 P_INIT_MSG_LIST IN VARCHAR2,
1169 P_COMMIT IN VARCHAR2,
1170 P_INVENTORY_ITEM_ID IN NUMBER,
1171 P_ORGANIZATION_ID IN NUMBER,
1172 P_LIFECYCLE_ID IN NUMBER,
1173 P_CURRENT_PHASE_ID IN NUMBER,
1174 P_ITEM_STATUS IN VARCHAR2,
1175 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1176 X_MSG_COUNT OUT NOCOPY NUMBER
1177 )
1178 IS
1179 BEGIN
1180 SAVEPOINT Create_Item_Lifecycle;
1181 developer_debug ('::2960442::Create_Item_Lifecycle::start');
1182 X_RETURN_STATUS := FND_API.g_RET_STS_SUCCESS;
1183 X_MSG_COUNT := 0;
1184
1185 UPDATE MTL_SYSTEM_ITEMS_B SET LIFECYCLE_ID = P_LIFECYCLE_ID
1186 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
1187 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
1188
1189 UPDATE MTL_SYSTEM_ITEMS_B SET CURRENT_PHASE_ID = P_CURRENT_PHASE_ID
1190 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
1191 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
1192
1193 UPDATE MTL_SYSTEM_ITEMS_B SET INVENTORY_ITEM_STATUS_CODE = P_ITEM_STATUS
1194 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
1195 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
1196
1197 UPDATE MTL_PENDING_ITEM_STATUS SET LIFECYCLE_ID = P_LIFECYCLE_ID
1198 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
1199 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
1200
1201 UPDATE MTL_PENDING_ITEM_STATUS SET PHASE_ID = P_CURRENT_PHASE_ID
1202 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
1203 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
1204
1205 UPDATE MTL_PENDING_ITEM_STATUS SET STATUS_CODE = P_ITEM_STATUS
1206 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
1207 AND ORGANIZATION_ID = P_ORGANIZATION_ID;
1208 developer_debug ('::2960442::Create_Item_Lifecycle::end');
1209 COMMIT;
1210
1211
1212 EXCEPTION
1213 WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
1214 ROLLBACK TO Create_Item_Lifecycle;
1215 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1216
1217 WHEN others THEN
1218 ROLLBACK TO Create_Item_Lifecycle;
1219 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1220 END Create_Item_Lifecycle;
1221
1222 Procedure Update_Item_Lifecycle(
1223 P_API_VERSION IN NUMBER,
1224 P_INIT_MSG_LIST IN VARCHAR2,
1225 P_COMMIT IN VARCHAR2,
1226 P_INVENTORY_ITEM_ID IN NUMBER,
1227 P_ORGANIZATION_ID IN NUMBER,
1228 P_CATALOG_GROUP_ID IN NUMBER,
1229 P_LIFECYCLE_ID IN NUMBER,
1230 P_CURRENT_PHASE_ID IN NUMBER,
1231 P_ITEM_STATUS IN VARCHAR2,
1232 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1233 X_MSG_COUNT OUT NOCOPY NUMBER
1234 )
1235 IS
1236 CURSOR ego_item_assigned_org_csr
1237 (
1238 v_inventory_item_id IN MTL_SYSTEM_ITEMS_B.INVENTORY_ITEM_ID%TYPE,
1239 v_master_organization_id IN MTL_SYSTEM_ITEMS_B.ORGANIZATION_ID%TYPE
1240 ) IS
1241 SELECT ORGANIZATION_ID
1242 FROM MTL_SYSTEM_ITEMS_VL
1243 WHERE INVENTORY_ITEM_ID = v_inventory_item_id
1244 AND ORGANIZATION_ID <> v_master_organization_id;
1245
1246 L_SYSDATE DATE := Sysdate;
1247 L_LIFECYCLE_ID NUMBER;
1248 L_CURRENT_PHASE_ID NUMBER;
1249 L_MASTER_ORGANIZATION_ID NUMBER;
1250 L_ORGANIZATION_ID NUMBER;
1251 L_ITEM_ASSIGNED_ORG_REC ego_item_assigned_org_csr%ROWTYPE;
1252 L_CONTROL_LEVEL NUMBER;
1253
1254 BEGIN
1255 SAVEPOINT Update_Item_Lifecycle;
1256 developer_debug ('::2960442::Update_Item_Lifecycle::start');
1257 X_RETURN_STATUS := FND_API.g_RET_STS_SUCCESS;
1258 X_MSG_COUNT := 0;
1259
1260 L_MASTER_ORGANIZATION_ID := EGO_UI_ITEM_PUB.Get_Master_Organization_Id(P_ORGANIZATION_ID => P_ORGANIZATION_ID);
1261
1262 IF (P_ORGANIZATION_ID = L_MASTER_ORGANIZATION_ID) THEN
1263 IF (P_CATALOG_GROUP_ID IS NULL) THEN
1264 UPDATE MTL_SYSTEM_ITEMS_B SET LIFECYCLE_ID = NULL
1265 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
1266
1267 UPDATE MTL_SYSTEM_ITEMS_B SET CURRENT_PHASE_ID = NULL
1268 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
1269 ELSE
1270 L_CONTROL_LEVEL := EGO_UI_ITEM_PUB.Get_Item_Attr_Control_Level(P_ITEM_ATTRIBUTE => 'MTL_SYSTEM_ITEMS.INVENTORY_ITEM_STATUS_CODE');
1271
1272 -- Insert a row to Mtl_Pending_Item_Status table with master organization id
1273 -- and pending_flag = N.
1274 INSERT INTO MTL_PENDING_ITEM_STATUS
1275 (
1276 INVENTORY_ITEM_ID,
1277 ORGANIZATION_ID,
1278 EFFECTIVE_DATE,
1279 IMPLEMENTED_DATE,
1280 PENDING_FLAG,
1281 LAST_UPDATE_DATE,
1282 LAST_UPDATED_BY,
1283 CREATION_DATE,
1284 CREATED_BY,
1285 LIFECYCLE_ID,
1286 PHASE_ID,
1287 STATUS_CODE
1288 )
1289 VALUES
1290 (
1291 P_INVENTORY_ITEM_ID,
1292 L_MASTER_ORGANIZATION_ID,
1293 L_SYSDATE,
1294 L_SYSDATE,
1295 'N',
1296 L_SYSDATE,
1297 g_USER_ID,
1298 L_SYSDATE,
1299 g_USER_ID,
1300 P_LIFECYCLE_ID,
1301 P_CURRENT_PHASE_ID,
1302 P_ITEM_STATUS
1303 );
1304
1305 IF (L_CONTROL_LEVEL = 2) THEN
1306 -- Org Control
1307 UPDATE MTL_SYSTEM_ITEMS_B SET LIFECYCLE_ID = P_LIFECYCLE_ID
1308 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
1309 AND ORGANIZATION_ID = L_MASTER_ORGANIZATION_ID;
1310
1311 UPDATE MTL_SYSTEM_ITEMS_B SET CURRENT_PHASE_ID = P_CURRENT_PHASE_ID
1312 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID
1313 AND ORGANIZATION_ID = L_MASTER_ORGANIZATION_ID;
1314
1315 OPEN ego_item_assigned_org_csr (v_inventory_item_id => P_INVENTORY_ITEM_ID,
1316 v_master_organization_id => L_MASTER_ORGANIZATION_ID);
1317 LOOP
1318 FETCH ego_item_assigned_org_csr INTO L_ITEM_ASSIGNED_ORG_REC;
1319 EXIT WHEN ego_item_assigned_org_csr%NOTFOUND;
1320
1321 INSERT INTO MTL_PENDING_ITEM_STATUS
1322 (
1323 INVENTORY_ITEM_ID,
1324 ORGANIZATION_ID,
1325 EFFECTIVE_DATE,
1326 PENDING_FLAG,
1327 LAST_UPDATE_DATE,
1328 LAST_UPDATED_BY,
1329 CREATION_DATE,
1330 CREATED_BY,
1331 LIFECYCLE_ID,
1332 PHASE_ID,
1333 STATUS_CODE
1334 )
1335 VALUES
1336 (
1337 P_INVENTORY_ITEM_ID,
1338 L_ITEM_ASSIGNED_ORG_REC.ORGANIZATION_ID,
1339 L_SYSDATE,
1340 'Y',
1341 L_SYSDATE,
1342 g_USER_ID,
1343 L_SYSDATE,
1344 g_USER_ID,
1345 P_LIFECYCLE_ID,
1346 P_CURRENT_PHASE_ID,
1347 P_ITEM_STATUS
1348 );
1349
1350 END LOOP;
1351 CLOSE ego_item_assigned_org_csr;
1352 ELSE
1353 -- Master Control
1354 UPDATE MTL_SYSTEM_ITEMS_B SET LIFECYCLE_ID = P_LIFECYCLE_ID
1355 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
1356
1357 UPDATE MTL_SYSTEM_ITEMS_B SET CURRENT_PHASE_ID = P_CURRENT_PHASE_ID
1358 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
1359
1360 OPEN ego_item_assigned_org_csr (v_inventory_item_id => P_INVENTORY_ITEM_ID,
1361 v_master_organization_id => L_MASTER_ORGANIZATION_ID);
1362 LOOP
1363 FETCH ego_item_assigned_org_csr INTO L_ITEM_ASSIGNED_ORG_REC;
1364 EXIT WHEN ego_item_assigned_org_csr%NOTFOUND;
1365
1366 INSERT INTO MTL_PENDING_ITEM_STATUS
1367 (
1368 INVENTORY_ITEM_ID,
1369 ORGANIZATION_ID,
1370 EFFECTIVE_DATE,
1371 IMPLEMENTED_DATE,
1372 PENDING_FLAG,
1373 LAST_UPDATE_DATE,
1374 LAST_UPDATED_BY,
1375 CREATION_DATE,
1376 CREATED_BY,
1377 LIFECYCLE_ID,
1378 PHASE_ID,
1379 STATUS_CODE
1380 )
1381 VALUES
1382 (
1383 P_INVENTORY_ITEM_ID,
1384 L_ITEM_ASSIGNED_ORG_REC.ORGANIZATION_ID,
1385 L_SYSDATE,
1386 L_SYSDATE,
1387 'N',
1388 L_SYSDATE,
1389 g_USER_ID,
1390 L_SYSDATE,
1391 g_USER_ID,
1392 P_LIFECYCLE_ID,
1393 P_CURRENT_PHASE_ID,
1394 P_ITEM_STATUS
1395 );
1396
1397 END LOOP;
1398 CLOSE ego_item_assigned_org_csr;
1399
1400 END IF;
1401 END IF;
1402 END IF;
1403 developer_debug ('::2960442::Update_Item_Attr_Ext::start');
1404 Update_Item_Attr_Ext(P_API_VERSION => P_API_VERSION,
1405 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
1406 P_COMMIT => P_COMMIT,
1407 P_INVENTORY_ITEM_ID => P_INVENTORY_ITEM_ID,
1408 P_ITEM_CATALOG_GROUP_ID => P_CATALOG_GROUP_ID,
1409 X_RETURN_STATUS => X_RETURN_STATUS,
1410 X_MSG_COUNT => X_MSG_COUNT);
1411 developer_debug ('::2960442::Update_Item_Attr_Ext::end');
1412 developer_debug ('::2960442::Update_Item_Lifecycle::end');
1413 COMMIT;
1414
1415 EXCEPTION
1416 WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
1417 ROLLBACK TO Update_Item_Lifecycle;
1418 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1419
1420 WHEN others THEN
1421 ROLLBACK TO Update_Item_Lifecycle;
1422 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1423
1424 END Update_Item_Lifecycle;
1425
1426 -- -----------------------------------------------------------------------------
1427 -- API Name: Update_Item_Attr_Ext
1428 -- -----------------------------------------------------------------------------
1429
1430 Procedure Update_Item_Attr_Ext(
1431 P_API_VERSION IN NUMBER,
1432 P_INIT_MSG_LIST IN VARCHAR2,
1433 P_COMMIT IN VARCHAR2,
1434 P_INVENTORY_ITEM_ID IN NUMBER,
1435 P_ITEM_CATALOG_GROUP_ID IN NUMBER,
1436 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1437 X_MSG_COUNT OUT NOCOPY NUMBER
1438 )
1439 IS
1440 BEGIN
1441 SAVEPOINT Update_Item_Attr_Ext;
1442 developer_debug ('::2960442::Update_Item_Attr_Ext::start');
1443 X_RETURN_STATUS := FND_API.g_RET_STS_SUCCESS;
1444 X_MSG_COUNT := 0;
1445
1446 IF (P_ITEM_CATALOG_GROUP_ID IS NULL) THEN
1447 DELETE FROM EGO_MTL_SY_ITEMS_EXT_B WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
1448 DELETE FROM EGO_MTL_SY_ITEMS_EXT_TL WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
1449 ELSE
1450 UPDATE EGO_MTL_SY_ITEMS_EXT_B SET ITEM_CATALOG_GROUP_ID = P_ITEM_CATALOG_GROUP_ID
1451 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
1452
1453 UPDATE EGO_MTL_SY_ITEMS_EXT_TL SET ITEM_CATALOG_GROUP_ID = P_ITEM_CATALOG_GROUP_ID
1454 WHERE INVENTORY_ITEM_ID = P_INVENTORY_ITEM_ID;
1455 END IF;
1456 developer_debug ('::2960442::Update_Item_Attr_Ext::end');
1457 COMMIT;
1458
1459 EXCEPTION
1460 WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
1461 ROLLBACK TO Update_Item_Attr_Ext;
1462 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1463
1464 WHEN others THEN
1465 ROLLBACK TO Update_Item_Attr_Ext;
1466 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
1467
1468 END Update_Item_Attr_Ext;
1469
1470
1471 /******************************************************************
1472 ** Procedure: Get_Master_Organization_Id (unexposed)
1473 ********************************************************************/
1474
1475 FUNCTION Get_Master_Organization_Id(
1476 P_ORGANIZATION_ID IN NUMBER
1477 ) RETURN NUMBER
1478 IS
1479 L_MASTER_ORGANIZATION_ID NUMBER;
1480 BEGIN
1481 SELECT MP.MASTER_ORGANIZATION_ID INTO L_MASTER_ORGANIZATION_ID
1482 FROM MTL_PARAMETERS MP
1483 WHERE MP.ORGANIZATION_ID = P_ORGANIZATION_ID;
1484
1485 RETURN L_MASTER_ORGANIZATION_ID;
1486 END Get_Master_Organization_Id;
1487
1488 /******************************************************************
1489 ** Procedure: Get_Item_Attr_Control_Level (unexposed)
1490 ********************************************************************/
1491
1492 FUNCTION Get_Item_Attr_Control_Level(
1493 P_ITEM_ATTRIBUTE IN VARCHAR2
1494 ) RETURN NUMBER
1495 IS
1496 L_CONTROL_LEVEL NUMBER;
1497 BEGIN
1498 SELECT LOOKUP_CODE2 INTO L_CONTROL_LEVEL
1499 FROM MTL_ITEM_ATTRIBUTES_V
1500 WHERE ATTRIBUTE_NAME = P_ITEM_ATTRIBUTE;
1501
1502 RETURN L_CONTROL_LEVEL;
1503 END Get_Item_Attr_Control_Level;
1504
1505 -- -----------------------------------------------------------------------------
1506 -- API Name: Set_Debug_Parameters
1507 -- -----------------------------------------------------------------------------
1508
1509 /******************************************************************
1510 ** Procedure: Set_Debug_Parameters (unexposed)
1511 ** Purpose: Will take input as the debug parameters and check if
1512 ** a debug session needs to be eastablished. If yes, the it will
1513 ** open a debug session file and all developer messages will be
1514 ** logged into a debug error file. File name will be the parameter
1515 ** debug_file_name_<session_id>
1516 ********************************************************************/
1517 Procedure Set_Debug_Parameters(
1518 P_debug_flag IN VARCHAR2
1519 , P_output_dir IN VARCHAR2
1520 , P_debug_filename IN VARCHAR2
1521 )
1522 IS
1523 l_Mesg_Token_tbl Error_Handler.Mesg_Token_Tbl_Type;
1524 l_token_Tbl Error_Handler.Token_Tbl_Type;
1525 l_return_status VARCHAR2(1);
1526 l_Debug_Flag VARCHAR2(1) := p_debug_flag;
1527 BEGIN
1528
1529 IF p_debug_flag = 'Y'
1530 THEN
1531 -- dbms_output.put_line('Debug is Yes ' );
1532
1533 IF trim(p_output_dir) IS NULL OR trim(p_output_dir) = ''
1534 THEN
1535 -- If debug is Y then out dir must be
1536 -- specified
1537 Error_Handler.Add_Error_Token
1538 ( p_Message_text =>
1539 ' Debug is set to Y so an output directory' ||
1540 ' must be specified. Debug will be turned' ||
1541 ' off since no directory is specified'
1542 , p_Mesg_Token_Tbl => l_mesg_token_tbl
1543 , x_Mesg_Token_Tbl => l_mesg_token_tbl
1544 , p_Token_Tbl => l_token_tbl
1545 );
1546
1547 Ego_Catalog_Group_Err_Handler.Log_Error
1548 ( p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1549 , p_error_status => 'W'
1550 , p_error_level => Error_Handler.G_BO_LEVEL
1551 );
1552 l_debug_flag := 'N';
1553
1554 -- dbms_output.put_line('Reverting debug to N ' );
1555 END IF;
1556
1557 IF trim(p_debug_filename) IS NULL OR trim(p_debug_filename) = ''
1558 THEN
1559
1560 Error_Handler.Add_Error_Token
1561 ( p_Message_text =>
1562 'Debug is set to Y so an output filename' ||
1563 ' must be specified. Debug will be turned' ||
1564 ' off since no filename is specified'
1565 , p_Mesg_Token_Tbl => l_mesg_token_tbl
1566 , x_Mesg_Token_Tbl => l_mesg_token_tbl
1567 , p_Token_Tbl => l_token_tbl
1568 );
1569
1570 Ego_Catalog_Group_Err_Handler.Log_Error
1571 ( p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1572 , p_error_status => 'W'
1573 , p_error_level => Error_Handler.G_BO_LEVEL
1574 );
1575 l_debug_flag := 'N';
1576
1577
1578 -- dbms_output.put_line('Reverting debug to N ' );
1579
1580 END IF;
1581 Error_Handler.Set_Debug(l_debug_flag);
1582
1583 IF p_debug_flag = 'Y'
1584 THEN
1585 Error_Handler.Open_Debug_Session
1586 ( p_debug_filename => p_debug_filename
1587 , p_output_dir => p_output_dir
1588 , x_return_status => l_return_status
1589 , p_mesg_token_tbl => l_mesg_token_tbl
1590 , x_mesg_token_tbl => l_mesg_token_tbl
1591 );
1592 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1593 Error_Handler.Set_Debug('N');
1594 END IF;
1595 END IF;
1596 END IF;
1597 END Set_Debug_Parameters;
1598
1599
1600 -- -----------------------------------------------------------------------------
1601 -- API Name: Delete_Extra_Item_Long_Desc_AG
1602 --
1603 -- Description:
1604 -- A private helper function for use with Seed_Item_Long_Desc_Attr_Group;
1605 -- After a call to Copy_User_Attrs_Data in item creation from copy, it will
1606 -- delete all but one Item Long Desc AG row. See bug 3023736 for details.
1607 -- -----------------------------------------------------------------------------
1608
1609 PROCEDURE Delete_Extra_Item_Long_Desc_AG (
1610 p_inventory_item_id IN NUMBER
1611 ,p_organization_id IN NUMBER
1612 ) IS
1613
1614 l_ext_id_to_delete_list VARCHAR2(200);
1615 l_dynamic_sql VARCHAR2(1000);
1616
1617 CURSOR Ext_Id_To_Delete_Cursor (
1618 cp_inventory_item_id IN NUMBER
1619 ,cp_organization_id IN NUMBER
1620 ) IS
1621 SELECT EXTENSION_ID
1622 FROM EGO_MTL_SY_ITEMS_EXT_VL
1623 WHERE INVENTORY_ITEM_ID = cp_inventory_item_id
1624 AND ORGANIZATION_ID = cp_organization_id
1625 AND ATTR_GROUP_ID = (SELECT ATTR_GROUP_ID
1626 FROM EGO_FND_DSC_FLX_CTX_EXT
1627 WHERE APPLICATION_ID = 431
1628 AND DESCRIPTIVE_FLEXFIELD_NAME = 'EGO_ITEMMGMT_GROUP'
1629 AND DESCRIPTIVE_FLEX_CONTEXT_CODE = 'ItemDetailDesc')
1630 AND C_EXT_ATTR1 = 'D';
1631
1632 BEGIN
1633
1634 FOR ext_rec IN Ext_Id_To_Delete_Cursor(p_inventory_item_id, p_organization_id)
1635 LOOP
1636
1637 IF (Ext_Id_To_Delete_Cursor%ROWCOUNT > 1) THEN
1638 l_ext_id_to_delete_list := l_ext_id_to_delete_list || ext_rec.EXTENSION_ID || ',';
1639 END IF;
1640
1641 END LOOP;
1642
1643 IF (LENGTH(l_ext_id_to_delete_list) > 0) THEN
1644 -----------------------------------------------
1645 -- ...trim the trailing ',' from the list... --
1646 -----------------------------------------------
1647 l_ext_id_to_delete_list := SUBSTR(l_ext_id_to_delete_list, 1, LENGTH(l_ext_id_to_delete_list) - LENGTH(','));
1648
1649 ----------------------------------------------------------------------
1650 -- ...and then delete all rows in the list from the B and TL tables --
1651 ----------------------------------------------------------------------
1652 l_dynamic_sql := ' DELETE FROM EGO_MTL_SY_ITEMS_EXT_B'||
1653 ' WHERE EXTENSION_ID IN ('||l_ext_id_to_delete_list||')';
1654 EXECUTE IMMEDIATE l_dynamic_sql;
1655
1656 l_dynamic_sql := ' DELETE FROM EGO_MTL_SY_ITEMS_EXT_TL'||
1657 ' WHERE EXTENSION_ID IN ('||l_ext_id_to_delete_list||')';
1658 EXECUTE IMMEDIATE l_dynamic_sql;
1659 END IF;
1660
1661 END Delete_Extra_Item_Long_Desc_AG;
1662
1663
1664 -- -----------------------------------------------------------------------------
1665 -- API Name: Process_Item
1666 -- -----------------------------------------------------------------------------
1667
1668 PROCEDURE Process_Item
1669 (
1670 p_api_version IN NUMBER
1671 , p_init_msg_list IN VARCHAR2
1672 , p_commit IN VARCHAR2
1673 -- Transaction data
1674 , p_Transaction_Type IN VARCHAR2
1675 , p_Language_Code IN VARCHAR2
1676 -- Organization
1677 , p_Organization_Id IN NUMBER
1678 , p_Organization_Code IN VARCHAR2
1679 -- Item catalog group
1680 , p_Item_Catalog_Group_Id IN NUMBER
1681 , p_Catalog_Status_Flag IN VARCHAR2
1682 -- Copy item from
1683 , p_Template_Id IN NUMBER
1684 , p_Template_Name IN VARCHAR2
1685 -- Item identifier
1686 , p_Inventory_Item_Id IN NUMBER
1687 , p_Item_Number IN VARCHAR2
1688 , p_Segment1 IN VARCHAR2
1689 , p_Segment2 IN VARCHAR2
1690 , p_Segment3 IN VARCHAR2
1691 , p_Segment4 IN VARCHAR2
1692 , p_Segment5 IN VARCHAR2
1693 , p_Segment6 IN VARCHAR2
1694 , p_Segment7 IN VARCHAR2
1695 , p_Segment8 IN VARCHAR2
1696 , p_Segment9 IN VARCHAR2
1697 , p_Segment10 IN VARCHAR2
1698 , p_Segment11 IN VARCHAR2
1699 , p_Segment12 IN VARCHAR2
1700 , p_Segment13 IN VARCHAR2
1701 , p_Segment14 IN VARCHAR2
1702 , p_Segment15 IN VARCHAR2
1703 , p_Segment16 IN VARCHAR2
1704 , p_Segment17 IN VARCHAR2
1705 , p_Segment18 IN VARCHAR2
1706 , p_Segment19 IN VARCHAR2
1707 , p_Segment20 IN VARCHAR2
1708 , p_Object_Version_Number IN NUMBER
1709 -- Lifecycle
1710 , p_Lifecycle_Id IN NUMBER
1711 , p_Current_Phase_Id IN NUMBER
1712 -- Main attributes
1713 , p_Description IN VARCHAR2
1714 , p_Long_Description IN VARCHAR2
1715 , p_Primary_Uom_Code IN VARCHAR2
1716 , p_Inventory_Item_Status_Code IN VARCHAR2
1717 -- BoM/Eng
1718 , p_Bom_Enabled_Flag IN VARCHAR2
1719 , p_Eng_Item_Flag IN VARCHAR2
1720 -- Role Grant
1721 , p_Role_Id IN NUMBER
1722 , p_Role_Name IN VARCHAR2
1723 , p_Grantee_Party_Type IN VARCHAR2
1724 , p_Grantee_Party_Id IN NUMBER
1725 , p_Grantee_Party_Name IN VARCHAR2
1726 , p_Grant_Start_Date IN DATE
1727 , p_Grant_End_Date IN DATE
1728 -- Returned item id
1729 , x_Inventory_Item_Id OUT NOCOPY NUMBER
1730 , x_Organization_Id OUT NOCOPY NUMBER
1731 --
1732 , x_return_status OUT NOCOPY VARCHAR2
1733 , x_msg_count OUT NOCOPY NUMBER
1734 )
1735 IS
1736 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item';
1737 l_api_version CONSTANT NUMBER := 1.0;
1738
1739 indx BINARY_INTEGER := 1;
1740
1741 CURSOR c_fnd_object_id(cp_object_name IN VARCHAR2) IS
1742 SELECT object_id
1743 FROM fnd_objects
1744 WHERE obj_name = cp_object_name;
1745
1746 CURSOR c_get_application_id IS
1747 SELECT application_id
1748 FROM fnd_application
1749 WHERE application_short_name = 'EGO';
1750
1751 CURSOR c_get_orig_item_rev_details (cp_inventory_item_id IN NUMBER
1752 ,cp_organization_id IN NUMBER) IS
1753 SELECT revision, revision_id
1754 FROM MTL_ITEM_REVISIONS_B
1755 WHERE inventory_item_id = cp_inventory_item_id
1756 AND organization_id = cp_organization_id
1757 AND effectivity_date <= SYSDATE
1758 ORDER BY effectivity_date desc;
1759
1760
1761 l_object_id FND_OBJECTS.object_id%TYPE;
1762 l_return_status VARCHAR2(10);
1763 l_error_code NUMBER;
1764 l_msg_data VARCHAR2(9999);
1765 l_application_id fnd_application.application_id%TYPE;
1766 l_orig_item_pk_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1767 l_new_item_pk_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1768 l_orig_item_rev_pk_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1769 l_new_item_rev_pk_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1770 l_new_cc_col_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
1771 l_item_rev_id mtl_item_revisions_b.revision_id%TYPE;
1772 l_item_rev_code mtl_item_revisions_b.revision%TYPE;
1773 l_commit VARCHAR2(20);
1774
1775 BEGIN
1776
1777 developer_debug ('::2960442::Process_Item::start');
1778 -------------------------------------------------------------
1779 -- TODO:
1780 -- the following parameters are never used in this procedure
1781 -- are these really required??
1782 -------------------------------------------------------------
1783 --, p_Object_Version_Number IN NUMBER
1784 -- BoM/Eng
1785 --, p_Bom_Enabled_Flag IN VARCHAR2
1786 --, p_Eng_Item_Flag IN VARCHAR2
1787 -- Role Grant
1788 --, p_Role_Id IN NUMBER
1789 --, p_Role_Name IN VARCHAR2
1790 --, p_Grantee_Party_Type IN VARCHAR2
1791 --, p_Grantee_Party_Id IN NUMBER
1792 --, p_Grantee_Party_Name IN VARCHAR2
1793 --, p_Grant_Start_Date IN DATE
1794 --, p_Grant_End_Date IN DATE
1795
1796 developer_debug (' Entered EGO_UI_ITEM_PUB.Process_item Input Parameters ' );
1797 developer_debug (' p_api_version '||to_char(p_api_version));
1798 developer_debug (' p_init_msg_list '|| p_init_msg_list);
1799 developer_debug (' p_commit '|| p_commit);
1800 developer_debug (' p_Transaction_Type '||p_transaction_type);
1801 developer_debug (' p_Language_Code '||p_language_code);
1802 developer_debug (' p_Organization_Id '||to_char(p_organization_id));
1803 developer_debug (' p_Organization_Code '||p_organization_code);
1804 developer_debug (' p_Item_Catalog_Group_Id '||to_char(p_item_catalog_group_id));
1805 developer_debug (' p_Catalog_Status_Flag '|| p_Catalog_Status_Flag);
1806 developer_debug (' p_Template_Id '|| to_char(p_template_id));
1807 developer_debug (' p_Template_Name '||p_template_name);
1808 developer_debug (' p_Inventory_Item_Id '||to_char(p_inventory_item_id));
1809 developer_debug (' p_Item_Number '|| p_item_number);
1810 developer_debug (' p_Segment1 '|| p_segment1);
1811 developer_debug (' p_Segment2 '|| p_segment2);
1812 developer_debug (' p_Segment3 '|| p_segment3);
1813 developer_debug (' p_Segment4 '|| p_segment4);
1814 developer_debug (' p_Segment5 '|| p_segment5);
1815 developer_debug (' p_Segment6 '|| p_segment6);
1816 developer_debug (' p_Segment7 '|| p_segment7);
1817 developer_debug (' p_Segment8 '|| p_segment8);
1818 developer_debug (' p_Segment9 '|| p_segment9);
1819 developer_debug (' p_Segment10 '|| p_segment10);
1820 developer_debug (' p_Segment11 '|| p_segment11);
1821 developer_debug (' p_Segment12 '|| p_segment12);
1822 developer_debug (' p_Segment13 '|| p_segment13);
1823 developer_debug (' p_Segment14 '|| p_segment14);
1824 developer_debug (' p_Segment15 '|| p_segment15);
1825 developer_debug (' p_Segment16 '|| p_segment16);
1826 developer_debug (' p_Segment17 '|| p_segment17);
1827 developer_debug (' p_Segment18 '|| p_segment18);
1828 developer_debug (' p_Segment19 '|| p_segment19);
1829 developer_debug (' p_Segment20 '|| p_segment20);
1830 developer_debug (' p_Object_Version_Number '||to_char(p_Object_Version_Number));
1831 developer_debug (' p_Lifecycle_Id '||to_char(p_Lifecycle_Id));
1832 developer_debug (' p_Current_Phase_Id '||to_char(p_Current_Phase_Id));
1833 developer_debug (' p_Description '|| p_description);
1834 developer_debug (' p_Long_Description '|| p_long_description);
1835 developer_debug (' p_Primary_Uom_Code '|| p_Primary_Uom_Code);
1836 developer_debug (' p_Inventory_Item_Status_Code '|| p_inventory_item_status_code);
1837 developer_debug (' p_Bom_Enabled_Flag '|| p_bom_enabled_flag);
1838 developer_debug (' p_Eng_Item_Flag '|| p_eng_item_flag);
1839 developer_debug (' p_Role_Id '||to_char(p_Role_Id));
1840 developer_debug (' p_Role_Name '|| p_role_name);
1841 developer_debug (' p_Grantee_Party_Type '|| p_grantee_party_type);
1842 developer_debug (' p_Grantee_Party_Id '||to_char(p_Grantee_Party_Id));
1843 developer_debug (' p_Grantee_Party_Name '|| p_grantee_party_name);
1844 developer_debug (' p_Grant_Start_Date '||to_char(p_grant_start_date,'DD-MON-YYYY'));
1845 developer_debug (' p_Grant_End_Date '||to_char(p_grant_end_date,'DD-MON-YYYY'));
1846
1847 -- need to add code here
1848 -- check if the correct signature is passed.
1849 IF p_api_version <> l_api_version THEN
1850 -- invalid api version, return back immediately.
1851 EGO_Item_Msg.Add_Error_Message
1852 (p_entity_index => indx
1853 ,p_application_short_name => 'EGO'
1854 ,p_message_name => 'EGO_PKG_INVALID_API_VER'
1855 ,p_token_name1 => 'PACKAGE'
1856 ,p_token_value1 => G_PKG_NAME
1857 ,p_translate1 => FALSE
1858 ,p_token_name2 => 'PROCEDURE'
1859 ,p_token_value2 => l_api_name
1860 ,p_translate2 => FALSE
1861 ,p_token_name3 => 'API_VERSION'
1862 ,p_token_value3 => TO_CHAR(p_api_version)
1863 ,p_translate3 => FALSE
1864 );
1865 x_return_status := G_RET_STS_ERROR;
1866 RETURN;
1867 END IF;
1868 developer_debug (' Correct api version passed');
1869 IF p_transaction_type = G_COPY_TRANSACTION_TYPE THEN
1870 -- collect all the parameters from the parent item (p_Inventory_item_id)
1871 IF p_inventory_item_id IS NOT NULL AND p_organization_id IS NOT NULL THEN
1872 -- initialize with the item id passed values
1873 initialize_item_info (p_inventory_item_id => p_inventory_item_id
1874 ,p_organization_id => p_organization_id
1875 ,x_return_status => x_return_status
1876 ,x_msg_count => x_msg_count
1877 );
1878 IF x_return_status <> G_RET_STS_SUCCESS THEN
1879 RETURN;
1880 ELSE
1881 -- get the template info now
1882 initialize_template_info (p_template_id => p_template_id
1883 ,p_template_name => p_template_name
1884 ,p_organization_id => p_organization_id
1885 ,p_organization_code => p_organization_code
1886 ,x_return_status => x_return_status
1887 ,x_msg_count => x_msg_count
1888 );
1889 IF x_return_status <> G_RET_STS_SUCCESS THEN
1890 RETURN;
1891 END IF; -- x-return_status fro initialize_template_info
1892 END IF; -- x-return_status fro initialize_item_info
1893 ELSE
1894 developer_debug (' No inventory item id OR organization id passed ');
1895 EGO_Item_Msg.Add_Error_Message
1896 (p_entity_index => indx
1897 ,p_application_short_name => 'EGO'
1898 ,p_message_name => 'EGO_PKG_MAND_VALUES_MISS'
1899 ,p_token_name1 => 'PACKAGE'
1900 ,p_token_value1 => G_PKG_NAME||'.'||l_api_name
1901 ,p_translate1 => FALSE
1902 ,p_token_name2 => 'VALUE1'
1903 ,p_token_value2 => 'INVENTORY_ITEM_ID'
1904 ,p_translate2 => FALSE
1905 ,p_token_name3 => 'VALUE2'
1906 ,p_token_value3 => 'ORGANIZATION_ID'
1907 ,p_translate3 => FALSE
1908 );
1909 x_return_status := G_RET_STS_ERROR;
1910 RETURN;
1911 END IF; -- p_inventory_item_id IS NOT NULL / p_organization_id IS NOT NULL
1912 developer_debug (' Correct api and Inventory Item information passed');
1913 -- all the item info available in the UI
1914 g_in_item_tbl(0).transaction_type := G_CREATE_TRANSACTION_TYPE;
1915 g_in_item_tbl(0).Language_Code := p_language_code;
1916 g_in_item_tbl(0).Template_Id := NULL;
1917 g_in_item_tbl(0).Template_Name := NULL;
1918 g_in_item_tbl(0).Item_Number := p_item_number;
1919 g_in_item_tbl(0).segment1 := p_segment1;
1920 g_in_item_tbl(0).segment2 := p_segment2;
1921 g_in_item_tbl(0).segment3 := p_segment3;
1922 g_in_item_tbl(0).segment4 := p_segment4;
1923 g_in_item_tbl(0).segment5 := p_segment5;
1924 g_in_item_tbl(0).segment6 := p_segment6;
1925 g_in_item_tbl(0).segment7 := p_segment7;
1926 g_in_item_tbl(0).segment8 := p_segment8;
1927 g_in_item_tbl(0).segment9 := p_segment9;
1928 g_in_item_tbl(0).segment10 := p_segment10;
1929 g_in_item_tbl(0).segment11 := p_segment11;
1930 g_in_item_tbl(0).segment12 := p_segment12;
1931 g_in_item_tbl(0).segment13 := p_segment13;
1932 g_in_item_tbl(0).segment14 := p_segment14;
1933 g_in_item_tbl(0).segment15 := p_segment15;
1934 g_in_item_tbl(0).segment16 := p_segment16;
1935 g_in_item_tbl(0).segment17 := p_segment17;
1936 g_in_item_tbl(0).segment18 := p_segment18;
1937 g_in_item_tbl(0).segment19 := p_segment19;
1938 g_in_item_tbl(0).segment20 := p_segment20;
1939 g_in_item_tbl(0).Organization_Id := p_organization_id;
1940 g_in_item_tbl(0).Organization_Code := p_organization_code;
1941 g_in_item_tbl(0).Item_Catalog_Group_Id := p_item_catalog_group_id;
1942 g_in_item_tbl(0).Catalog_Status_Flag := p_catalog_status_flag;
1943 g_in_item_tbl(0).Lifecycle_Id := p_lifecycle_id;
1944 g_in_item_tbl(0).Current_Phase_Id := p_current_phase_id;
1945 g_in_item_tbl(0).Description := p_description;
1946 g_in_item_tbl(0).Long_Description := p_long_description;
1947 g_in_item_tbl(0).Primary_Uom_Code := p_primary_uom_code;
1948 g_in_item_tbl(0).Inventory_Item_Status_Code := p_Inventory_Item_Status_Code;
1949
1950 developer_debug (' Before calling ego_item_pub.process_items');
1951 EGO_ITEM_PUB.Process_Items
1952 (p_api_version => 1.0
1953 ,p_init_msg_list => FND_API.g_FALSE
1954 ,p_commit => FND_API.g_FALSE
1955 ,p_Item_Tbl => g_in_item_tbl
1956 ,p_Role_Grant_Tbl => EGO_ITEM_PUB.G_MISS_ROLE_GRANT_TBL
1957 ,x_Item_Tbl => g_out_item_tbl
1958 ,x_return_status => x_return_status
1959 ,x_msg_count => x_msg_count
1960 );
1961 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1962 -- item created successfully
1963 x_inventory_item_id := g_out_item_tbl(0).inventory_item_id;
1964 x_organization_id := g_out_item_tbl(0).organization_id;
1965 ELSE
1966 -- messages already logged by EGO_ITEM_PUB
1967 RETURN;
1968 END IF; -- x_return_status = FND_API.G_RET_STS_SUCCESS
1969 OPEN c_fnd_object_id (cp_object_name => G_OBJECT_NAME);
1970 FETCH c_fnd_object_id INTO l_object_id;
1971 IF c_fnd_object_id%NOTFOUND THEN
1972 l_object_id := -1;
1973 END IF;
1974 developer_debug(' Object Information passed ');
1975 CLOSE c_fnd_object_id;
1976 OPEN c_get_application_id;
1977 FETCH c_get_application_id INTO l_application_id;
1978 IF c_get_application_id%NOTFOUND THEN
1979 l_application_id := -1;
1980 END IF;
1981 CLOSE c_get_application_id;
1982 developer_debug (' Original Item Id ' || to_char(p_inventory_item_id));
1983 developer_debug (' Original Org Id ' || to_char(p_organization_id));
1984 developer_debug (' Object Id ' || to_char(l_object_id));
1985 developer_debug (' Application Id ' || to_char(l_application_id));
1986 developer_debug (' New Item Id ' || to_char(x_inventory_item_id));
1987 developer_debug (' New Org Id ' || to_char(x_organization_id));
1988 -- call the user attributes code for item level
1989 l_orig_item_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1990 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', p_inventory_item_id),
1991 EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', p_organization_id));
1992 l_new_item_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1993 EGO_COL_NAME_VALUE_PAIR_OBJ('INVENTORY_ITEM_ID', x_inventory_item_id),
1994 EGO_COL_NAME_VALUE_PAIR_OBJ('ORGANIZATION_ID', x_organization_id));
1995
1996 l_orig_item_rev_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1997 -- EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION', NULL),
1998 EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID', NULL));
1999 l_new_item_rev_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
2000 -- EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION', NULL),
2001 EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID', NULL));
2002 l_new_cc_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
2003 EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID', p_Item_Catalog_Group_Id));
2004 developer_debug(' Before calling EGO_USER_ATTRS_DATA_PUB.Copy_User_Attrs_Data ');
2005 EGO_USER_ATTRS_DATA_PUB.Copy_User_Attrs_Data (
2006 p_api_version => 1.0
2007 ,p_application_id => l_application_id
2008 ,p_object_id => l_object_id
2009 ,p_object_name => G_OBJECT_NAME
2010 ,p_old_pk_col_value_pairs => l_orig_item_pk_value_pairs
2011 ,p_old_dtlevel_col_value_pairs => l_orig_item_rev_pk_value_pairs
2012 ,p_new_pk_col_value_pairs => l_new_item_pk_value_pairs
2013 ,p_new_dtlevel_col_value_pairs => l_new_item_rev_pk_value_pairs
2014 ,p_new_cc_col_value_pairs => l_new_cc_col_value_pairs
2015 ,p_commit => FND_API.G_FALSE
2016 ,x_return_status => x_return_status
2017 ,x_errorcode => l_error_code
2018 ,x_msg_count => x_msg_count
2019 ,x_msg_data => l_msg_data
2020 );
2021 developer_debug(' 20 Returning from EGO_USER_ATTRS_DATA_PUB.Copy_User_Attrs_Data ' );
2022 IF x_return_status = fnd_api.g_miss_char THEN
2023 developer_debug(' 21 ');
2024 ELSIF x_return_status IS NULL THEN
2025 developer_debug(' 22 ');
2026 ELSE
2027 developer_debug(' 23 '||x_return_status);
2028 END IF;
2029
2030 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2031 -- error handled in Ego_user_attrs_data_pub
2032 RETURN;
2033 END IF;
2034 -- call the user attributes code for revision level
2035 developer_debug(' 30 ');
2036 OPEN c_get_orig_item_rev_details (cp_inventory_item_id => p_inventory_item_id
2037 ,cp_organization_id => p_organization_id);
2038 FETCH c_get_orig_item_rev_details INTO l_item_rev_code, l_item_rev_id;
2039 developer_debug(' 40 ');
2040 IF c_get_orig_item_rev_details%FOUND THEN
2041 CLOSE c_get_orig_item_rev_details;
2042 developer_debug(' 50 ');
2043 l_orig_item_rev_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
2044 EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID', l_item_rev_id));
2045 developer_debug(' 55 -- orig revision ' || l_item_rev_code);
2046 -- fetch the item revision of the newly created item.
2047 developer_debug(' 60 ');
2048 OPEN c_get_orig_item_rev_details (cp_inventory_item_id => x_inventory_item_id
2049 ,cp_organization_id => x_organization_id);
2050 FETCH c_get_orig_item_rev_details INTO l_item_rev_code, l_item_rev_id;
2051 IF c_get_orig_item_rev_details%FOUND THEN
2052 developer_debug(' 70 ');
2053 l_new_item_rev_pk_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
2054 EGO_COL_NAME_VALUE_PAIR_OBJ('REVISION_ID', l_item_rev_id));
2055 developer_debug(' 75 -- new revision ' || l_item_rev_code);
2056 ELSE
2057 l_new_item_rev_pk_value_pairs := NULL;
2058 END IF;
2059 developer_debug(' 80 ');
2060 CLOSE c_get_orig_item_rev_details;
2061 l_new_cc_col_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
2062 EGO_COL_NAME_VALUE_PAIR_OBJ('ITEM_CATALOG_GROUP_ID', p_Item_Catalog_Group_Id));
2063 EGO_USER_ATTRS_DATA_PUB.Copy_User_Attrs_Data (
2064 p_api_version => 1.0
2065 ,p_application_id => l_application_id
2066 ,p_object_id => l_object_id
2067 ,p_object_name => G_OBJECT_NAME
2068 ,p_old_pk_col_value_pairs => l_orig_item_pk_value_pairs
2069 ,p_old_dtlevel_col_value_pairs => l_orig_item_rev_pk_value_pairs
2070 ,p_new_pk_col_value_pairs => l_new_item_pk_value_pairs
2071 ,p_new_dtlevel_col_value_pairs => l_new_item_rev_pk_value_pairs
2072 ,p_new_cc_col_value_pairs => l_new_cc_col_value_pairs
2073 ,p_commit => FND_API.G_FALSE
2074 ,x_return_status => x_return_status
2075 ,x_errorcode => l_error_code
2076 ,x_msg_count => x_msg_count
2077 ,x_msg_data => l_msg_data
2078 );
2079
2080
2081 -----------------------------------------------------------------------
2082 -- Dylan added this clean-up procedure to deal with duplicate
2083 -- Item Long Desc AG rows from the above calls to Copy_User_Attrs_Data
2084 -- in conjunction with the call that's made to
2085 -- EGO_ITEM_PUB.Seed_Item_Long_Desc_Attr_Group
2086 -- from
2087 -- INVPOPIF.inopinp_OI_process_create
2088 -----------------------------------------------------------------------
2089 Delete_Extra_Item_Long_Desc_AG(x_inventory_item_id, x_organization_id);
2090
2091 developer_debug(' 90 Returning from EGO_USER_ATTRS_DATA_PUB.Copy_User_Attrs_Data -- Revision Part ' );
2092 IF x_return_status = fnd_api.g_miss_char THEN
2093 developer_debug(' 91 ');
2094 ELSIF x_return_status IS NULL THEN
2095 developer_debug(' 92 ');
2096 ELSE
2097 developer_debug(' 93 '||x_return_status);
2098 END IF;
2099 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2100 -- problem with Copy Revision Attrs Data for item
2101 RETURN;
2102 END IF;
2103 ELSE
2104 developer_debug(' 100 ');
2105 CLOSE c_get_orig_item_rev_details;
2106 END IF; -- c_get_orig_item_rev_details%FOUND
2107 -- item revision also created successfully.
2108 -- create the lifecycle details now
2109 process_item_lifecycle
2110 (p_api_version => 1.0
2111 ,p_init_msg_list => p_init_msg_list
2112 ,p_commit => FND_API.G_FALSE
2113 ,p_inventory_item_id => x_inventory_item_id
2114 ,p_organization_id => x_organization_id
2115 ,p_catalog_group_id => p_item_catalog_group_id
2116 ,p_lifecycle_id => p_lifecycle_id
2117 ,p_current_phase_id => p_current_phase_id
2118 ,p_item_status => p_inventory_item_status_code
2119 ,p_transaction_type => G_CREATE_TRANSACTION_TYPE
2120 ,x_return_status => x_return_status
2121 ,x_msg_count => x_msg_count
2122 );
2123
2124 developer_debug(' 110 Returning from Process Item Lifecycle ' );
2125 IF x_return_status = fnd_api.g_miss_char THEN
2126 developer_debug(' 111 ');
2127 ELSIF x_return_status IS NULL THEN
2128 developer_debug(' 112 ');
2129 ELSE
2130 developer_debug(' 113 '||x_return_status);
2131 END IF;
2132 IF NOT (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2133 -- problem with item lifecycle processing for item
2134 RETURN;
2135 END IF;
2136 developer_debug(' 120 ');
2137 ELSE
2138 developer_debug(' 130 Sent TRANSACTIIOY_TYPE which is not COPY ');
2139 x_return_status := G_RET_STS_ERROR;
2140 RETURN;
2141 END IF; -- p_transaction_type = G_COPY_TRANSACTION_TYPE
2142 developer_debug(' 140 ');
2143
2144 IF FND_API.To_Boolean( p_commit ) THEN
2145 COMMIT WORK;
2146 END IF;
2147 x_return_status := G_RET_STS_SUCCESS;
2148 developer_debug ('::2960442::Process_Item::end');
2149 EXCEPTION
2150
2151 WHEN others THEN
2152 IF c_fnd_object_id%ISOPEN THEN
2153 CLOSE c_fnd_object_id;
2154 END IF;
2155 IF c_get_application_id%ISOPEN THEN
2156 CLOSE c_get_application_id;
2157 END IF;
2158 IF c_get_orig_item_rev_details%ISOPEN THEN
2159 CLOSE c_get_orig_item_rev_details;
2160 END IF;
2161 x_return_status := G_RET_STS_UNEXP_ERROR;
2162 EGO_Item_Msg.Add_Error_Message ( indx, 'EGO', 'EGO_PKG_UNEXPECTED_ERROR',
2163 'PACKAGE', G_PKG_NAME, FALSE,
2164 'PROCEDURE', l_api_name, FALSE,
2165 'ERROR_TEXT', SQLERRM, FALSE );
2166
2167 END Process_Item;
2168
2169 -- -----------------------------------------------------------------------------
2170 -- API Name: Get_Item_Count
2171 -- -----------------------------------------------------------------------------
2172
2173 /**************************************************************************
2174 ** Function: Get_Item_Count
2175 ** Purpose: Will take input as the organizationId and the cataloggroupId
2176 ** and return the count of items in that organization for that
2177 ** particular catalog group.
2178 ** Added a parameter p_item_type for the Item Count to be Session Specific(Bug 3536404)
2179 **************************************************************************/
2180
2181 FUNCTION Get_Item_Count(
2182 p_catalog_group_id IN NUMBER
2183 ,p_organization_id IN NUMBER
2184 ,p_item_type IN VARCHAR2 DEFAULT NULL)
2185 RETURN NUMBER
2186 IS
2187
2188 l_total_count NUMBER :=0;
2189
2190 BEGIN
2191 IF(p_item_type IS NOT NULL) THEN
2192 select count(*) INTO l_total_count
2193 from mtl_system_items_b a
2194 where item_catalog_group_id
2195 in(
2196 select item_catalog_group_id
2197 from mtl_item_catalog_groups_b b
2198 connect by prior item_catalog_group_id = parent_catalog_group_id
2199 start with b.item_catalog_group_id =p_catalog_group_id
2200 )
2201 and a.organization_id = p_organization_id
2202 and a.eng_item_flag = p_item_type;
2203 ELSE
2204 select count(*) INTO l_total_count
2205 from mtl_system_items_b a
2206 where item_catalog_group_id
2207 in(
2208 select item_catalog_group_id
2209 from mtl_item_catalog_groups_b b
2210 connect by prior item_catalog_group_id = parent_catalog_group_id
2211 start with b.item_catalog_group_id =p_catalog_group_id
2212 )
2213 and a.organization_id = p_organization_id;
2214 END IF;
2215
2216 return l_total_count;
2217 EXCEPTION
2218 WHEN OTHERS THEN
2219 NULL;
2220
2221 END get_item_count;
2222
2223
2224
2225 /******************************************************************
2226 ** Function: Get_Category_Item_Count
2227 ** Purpose: Will take input as the organizationId and the cataloggroupId
2228 ** and return the count of items in that organization for that
2229 ** particular category.
2230 ** Added a parameter p_item_type for the Item Count to be Session Specific(Bug 3536404)
2231 ********************************************************************/
2232
2233 FUNCTION Get_Category_Item_Count(
2234 P_CATEGORY_SET_ID IN NUMBER,
2235 P_CATEGORY_ID IN NUMBER,
2236 P_ORGANIZATION_ID IN NUMBER,
2237 P_ITEM_TYPE IN VARCHAR2 DEFAULT NULL
2238 )
2239 RETURN NUMBER
2240 IS
2241
2242 l_total_count NUMBER := 0;
2243
2244 BEGIN
2245 IF (P_CATEGORY_ID <> -1) THEN
2246 IF(P_ITEM_TYPE IS NOT NULL) THEN
2247 select count(*) into l_total_count
2248 from mtl_item_categories a , mtl_system_items_b b
2249 where category_id in (
2250 select category_id
2251 from mtl_category_set_valid_cats
2252 start with category_id = P_CATEGORY_ID
2253 and category_set_id = P_CATEGORY_SET_ID --Corrected the connect clause in count query
2254 connect by prior category_id = parent_category_id
2255 and category_set_id = P_CATEGORY_SET_ID
2256 )
2257 and a.organization_id = P_ORGANIZATION_ID
2258 and a.category_set_id = P_CATEGORY_SET_ID
2259 and a.inventory_item_id = b.inventory_item_id
2260 and a.organization_id = b.organization_id
2261 and b.eng_item_flag = P_ITEM_TYPE;
2262 ELSE
2263 select count(*) into l_total_count
2264 from mtl_item_categories a , mtl_system_items_b b
2265 where category_id in (
2266 select category_id
2267 from mtl_category_set_valid_cats
2268 start with category_id = P_CATEGORY_ID
2269 and category_set_id = P_CATEGORY_SET_ID --Corrected the connect clause in count query
2270 connect by prior category_id = parent_category_id
2271 and category_set_id = P_CATEGORY_SET_ID
2272 )
2273 and a.organization_id = P_ORGANIZATION_ID
2274 and a.category_set_id = P_CATEGORY_SET_ID
2275 and a.inventory_item_id = b.inventory_item_id
2276 and a.organization_id = b.organization_id;
2277 END IF;
2278 ELSE
2279 IF(P_ITEM_TYPE IS NOT NULL) THEN
2280 select count(*) into l_total_count
2281 from mtl_item_categories a , mtl_system_items_b b,mtl_category_set_valid_cats c
2282 where a.organization_id = P_ORGANIZATION_ID
2283 and a.category_set_id = P_CATEGORY_SET_ID
2284 and c.category_set_id = P_CATEGORY_SET_ID
2285 and c.category_id = a.category_id
2286 and a.inventory_item_id = b.inventory_item_id
2287 and a.organization_id = b.organization_id
2288 and b.eng_item_flag = P_ITEM_TYPE;
2289 ELSE
2290 select count(*) into l_total_count
2291 from mtl_item_categories a , mtl_system_items_b b,mtl_category_set_valid_cats c
2292 where a.organization_id = P_ORGANIZATION_ID
2293 and a.category_set_id = P_CATEGORY_SET_ID
2294 and c.category_set_id = P_CATEGORY_SET_ID
2295 and c.category_id = a.category_id
2296 and a.inventory_item_id = b.inventory_item_id
2297 and a.organization_id = b.organization_id;
2298 END IF;
2299 END IF;
2300
2301 return l_total_count;
2302 EXCEPTION
2303 WHEN OTHERS THEN
2304 NULL;
2305
2306 END Get_Category_Item_Count;
2307
2308 FUNCTION Get_Category_Hierarchy_Names(
2309 P_CATEGORY_SET_ID IN NUMBER,
2310 P_CATEGORY_ID IN NUMBER
2311 )
2312 RETURN VARCHAR2
2313 IS
2314
2315 CURSOR get_parent_category_id_csr (p_category_set_id IN NUMBER,
2316 p_category_id IN NUMBER ) IS
2317 SELECT IC.CATEGORY_ID,
2318 IC.PARENT_CATEGORY_ID
2319 FROM MTL_CATEGORY_SET_VALID_CATS IC
2320 START WITH CATEGORY_ID = p_category_id --3030474
2321 AND CATEGORY_SET_ID = p_category_set_id
2322 CONNECT BY PRIOR PARENT_CATEGORY_ID = CATEGORY_ID
2323 AND CATEGORY_SET_ID = p_category_set_id;
2324
2325 l_parent_categories get_parent_category_id_csr%ROWTYPE;
2326 l_category_set_name VARCHAR2(30);
2327 l_category_name VARCHAR2(122);
2328 l_category_hierarchy_names VARCHAR2(1000);
2329 l_tmp_names VARCHAR2(1000);
2330
2331 BEGIN
2332 SELECT CATEGORY_SET_NAME into l_category_set_name
2333 FROM MTL_CATEGORY_SETS_VL
2334 WHERE CATEGORY_SET_ID = P_CATEGORY_SET_ID;
2335
2336 OPEN get_parent_category_id_csr(p_category_set_id => P_CATEGORY_SET_ID,
2337 p_category_id => P_CATEGORY_ID);
2338 LOOP
2339 FETCH get_parent_category_id_csr into l_parent_categories;
2340 EXIT WHEN get_parent_category_id_csr%NOTFOUND;
2341
2342 SELECT C.CONCATENATED_SEGMENTS into l_category_name
2343 FROM MTL_CATEGORIES_KFV C
2344 WHERE C.CATEGORY_ID = l_parent_categories.CATEGORY_ID;
2345
2346 l_tmp_names := l_category_hierarchy_names;
2347 IF (l_tmp_names IS NULL) THEN
2348 l_category_hierarchy_names := l_category_name;
2349 ELSE
2350 l_category_hierarchy_names := l_category_name || ' > ' || l_tmp_names;
2351 END IF;
2352 END LOOP;
2353 CLOSE get_parent_category_id_csr;
2354
2355 l_tmp_names := l_category_hierarchy_names;
2356 --Bug: 3018903 Added If condition
2357 IF l_tmp_names IS NOT NULL THEN
2358 l_category_hierarchy_names := l_category_set_name || ' > ' || l_tmp_names;
2359 ELSE
2360 SELECT C.CONCATENATED_SEGMENTS into l_category_hierarchy_names
2361 FROM MTL_CATEGORIES_KFV C
2362 WHERE C.CATEGORY_ID = p_category_id;
2363 END IF;
2364 RETURN l_category_hierarchy_names;
2365
2366 END Get_Category_Hierarchy_Names;
2367
2368 -- -----------------------------------------------------------------------------
2369 -- API Name: Process_Item
2370 -- -----------------------------------------------------------------------------
2371
2372 PROCEDURE Process_Item
2373 (
2374 p_api_version IN NUMBER
2375 ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE
2376 ,p_commit IN VARCHAR2 DEFAULT G_FALSE
2377 -- Transaction data
2378 ,p_Transaction_Type IN VARCHAR2 DEFAULT G_MISS_CHAR
2379 ,p_Language_Code IN VARCHAR2 DEFAULT G_MISS_CHAR
2380 -- Copy item from template
2381 ,p_Template_Id IN NUMBER DEFAULT NULL
2382 ,p_Template_Name IN VARCHAR2 DEFAULT NULL
2383 -- Copy item from another item
2384 ,p_copy_inventory_item_Id IN NUMBER DEFAULT G_MISS_NUM
2385 -- Base Attributes
2386 ,p_inventory_item_id IN NUMBER DEFAULT G_MISS_NUM
2387 ,p_organization_id IN NUMBER DEFAULT G_MISS_NUM
2388 ,p_master_organization_id IN NUMBER DEFAULT G_MISS_NUM
2389 ,p_description IN VARCHAR2 DEFAULT G_MISS_CHAR
2390 ,p_long_description IN VARCHAR2 DEFAULT G_MISS_CHAR
2391 ,p_primary_uom_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2392 ,p_primary_unit_of_measure IN VARCHAR2 DEFAULT G_MISS_CHAR
2393 ,p_item_type IN VARCHAR2 DEFAULT G_MISS_CHAR
2394 ,p_inventory_item_status_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2395 ,p_allowed_units_lookup_code IN NUMBER DEFAULT G_MISS_NUM
2396 ,p_item_catalog_group_id IN NUMBER DEFAULT G_MISS_NUM
2397 ,p_catalog_status_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2398 ,p_inventory_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2399 ,p_stock_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2400 ,p_mtl_transactions_enabled_fl IN VARCHAR2 DEFAULT G_MISS_CHAR
2401 ,p_check_shortages_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2402 ,p_revision_qty_control_code IN NUMBER DEFAULT G_MISS_NUM
2403 ,p_reservable_type IN NUMBER DEFAULT G_MISS_NUM
2404 ,p_shelf_life_code IN NUMBER DEFAULT G_MISS_NUM
2405 ,p_shelf_life_days IN NUMBER DEFAULT G_MISS_NUM
2406 ,p_cycle_count_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2407 ,p_negative_measurement_error IN NUMBER DEFAULT G_MISS_NUM
2408 ,p_positive_measurement_error IN NUMBER DEFAULT G_MISS_NUM
2409 ,p_lot_control_code IN NUMBER DEFAULT G_MISS_NUM
2410 ,p_auto_lot_alpha_prefix IN VARCHAR2 DEFAULT G_MISS_CHAR
2411 ,p_start_auto_lot_number IN VARCHAR2 DEFAULT G_MISS_CHAR
2412 ,p_serial_number_control_code IN NUMBER DEFAULT G_MISS_NUM
2413 ,p_auto_serial_alpha_prefix IN VARCHAR2 DEFAULT G_MISS_CHAR
2414 ,p_start_auto_serial_number IN VARCHAR2 DEFAULT G_MISS_CHAR
2415 ,p_location_control_code IN NUMBER DEFAULT G_MISS_NUM
2416 ,p_restrict_subinventories_cod IN NUMBER DEFAULT G_MISS_NUM
2417 ,p_restrict_locators_code IN NUMBER DEFAULT G_MISS_NUM
2418 ,p_bom_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2419 ,p_bom_item_type IN NUMBER DEFAULT G_MISS_NUM
2420 ,p_base_item_id IN NUMBER DEFAULT G_MISS_NUM
2421 ,p_effectivity_control IN NUMBER DEFAULT G_MISS_NUM
2422 ,p_eng_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2423 ,p_engineering_ecn_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2424 ,p_engineering_item_id IN NUMBER DEFAULT G_MISS_NUM
2425 ,p_engineering_date IN DATE DEFAULT G_MISS_DATE
2426 ,p_product_family_item_id IN NUMBER DEFAULT G_MISS_NUM
2427 ,p_auto_created_config_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2428 ,p_model_config_clause_name IN VARCHAR2 DEFAULT G_MISS_CHAR
2429 -- attribute not in the form
2430 ,p_new_revision_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2431 ,p_costing_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2432 ,p_inventory_asset_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2433 ,p_default_include_in_rollup_f IN VARCHAR2 DEFAULT G_MISS_CHAR
2434 ,p_cost_of_sales_account IN NUMBER DEFAULT G_MISS_NUM
2435 ,p_std_lot_size IN NUMBER DEFAULT G_MISS_NUM
2436 ,p_purchasing_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2437 ,p_purchasing_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2438 ,p_must_use_approved_vendor_fl IN VARCHAR2 DEFAULT G_MISS_CHAR
2439 ,p_allow_item_desc_update_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2440 ,p_rfq_required_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2441 ,p_outside_operation_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2442 ,p_outside_operation_uom_type IN VARCHAR2 DEFAULT G_MISS_CHAR
2443 ,p_taxable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2444 ,p_purchasing_tax_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2445 ,p_receipt_required_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2446 ,p_inspection_required_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2447 ,p_buyer_id IN NUMBER DEFAULT G_MISS_NUM
2448 ,p_unit_of_issue IN VARCHAR2 DEFAULT G_MISS_CHAR
2449 ,p_receive_close_tolerance IN NUMBER DEFAULT G_MISS_NUM
2450 ,p_invoice_close_tolerance IN NUMBER DEFAULT G_MISS_NUM
2451 ,p_un_number_id IN NUMBER DEFAULT G_MISS_NUM
2452 ,p_hazard_class_id IN NUMBER DEFAULT G_MISS_NUM
2453 ,p_list_price_per_unit IN NUMBER DEFAULT G_MISS_NUM
2454 ,p_market_price IN NUMBER DEFAULT G_MISS_NUM
2455 ,p_price_tolerance_percent IN NUMBER DEFAULT G_MISS_NUM
2456 ,p_rounding_factor IN NUMBER DEFAULT G_MISS_NUM
2457 ,p_encumbrance_account IN NUMBER DEFAULT G_MISS_NUM
2458 ,p_expense_account IN NUMBER DEFAULT G_MISS_NUM
2459 ,p_expense_billable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2460 ,p_asset_category_id IN NUMBER DEFAULT G_MISS_NUM
2461 ,p_receipt_days_exception_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2462 ,p_days_early_receipt_allowed IN NUMBER DEFAULT G_MISS_NUM
2463 ,p_days_late_receipt_allowed IN NUMBER DEFAULT G_MISS_NUM
2464 ,p_allow_substitute_receipts_f IN VARCHAR2 DEFAULT G_MISS_CHAR
2465 ,p_allow_unordered_receipts_fl IN VARCHAR2 DEFAULT G_MISS_CHAR
2466 ,p_allow_express_delivery_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2467 ,p_qty_rcv_exception_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2468 ,p_qty_rcv_tolerance IN NUMBER DEFAULT G_MISS_NUM
2469 ,p_receiving_routing_id IN NUMBER DEFAULT G_MISS_NUM
2470 ,p_enforce_ship_to_location_c IN VARCHAR2 DEFAULT G_MISS_CHAR
2471 ,p_weight_uom_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2472 ,p_unit_weight IN NUMBER DEFAULT G_MISS_NUM
2473 ,p_volume_uom_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2474 ,p_unit_volume IN NUMBER DEFAULT G_MISS_NUM
2475 ,p_container_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2476 ,p_vehicle_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2477 ,p_container_type_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2478 ,p_internal_volume IN NUMBER DEFAULT G_MISS_NUM
2479 ,p_maximum_load_weight IN NUMBER DEFAULT G_MISS_NUM
2480 ,p_minimum_fill_percent IN NUMBER DEFAULT G_MISS_NUM
2481 ,p_inventory_planning_code IN NUMBER DEFAULT G_MISS_NUM
2482 ,p_planner_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2483 ,p_planning_make_buy_code IN NUMBER DEFAULT G_MISS_NUM
2484 ,p_min_minmax_quantity IN NUMBER DEFAULT G_MISS_NUM
2485 ,p_max_minmax_quantity IN NUMBER DEFAULT G_MISS_NUM
2486 ,p_minimum_order_quantity IN NUMBER DEFAULT G_MISS_NUM
2487 ,p_maximum_order_quantity IN NUMBER DEFAULT G_MISS_NUM
2488 ,p_order_cost IN NUMBER DEFAULT G_MISS_NUM
2489 ,p_carrying_cost IN NUMBER DEFAULT G_MISS_NUM
2490 ,p_source_type IN NUMBER DEFAULT G_MISS_NUM
2491 ,p_source_organization_id IN NUMBER DEFAULT G_MISS_NUM
2492 ,p_source_subinventory IN VARCHAR2 DEFAULT G_MISS_CHAR
2493 ,p_mrp_safety_stock_code IN NUMBER DEFAULT G_MISS_NUM
2494 ,p_safety_stock_bucket_days IN NUMBER DEFAULT G_MISS_NUM
2495 ,p_mrp_safety_stock_percent IN NUMBER DEFAULT G_MISS_NUM
2496 ,p_fixed_order_quantity IN NUMBER DEFAULT G_MISS_NUM
2497 ,p_fixed_days_supply IN NUMBER DEFAULT G_MISS_NUM
2498 ,p_fixed_lot_multiplier IN NUMBER DEFAULT G_MISS_NUM
2499 ,p_mrp_planning_code IN NUMBER DEFAULT G_MISS_NUM
2500 ,p_ato_forecast_control IN NUMBER DEFAULT G_MISS_NUM
2501 ,p_planning_exception_set IN VARCHAR2 DEFAULT G_MISS_CHAR
2502 ,p_end_assembly_pegging_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2503 ,p_shrinkage_rate IN NUMBER DEFAULT G_MISS_NUM
2504 ,p_rounding_control_type IN NUMBER DEFAULT G_MISS_NUM
2505 ,p_acceptable_early_days IN NUMBER DEFAULT G_MISS_NUM
2506 ,p_repetitive_planning_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2507 ,p_overrun_percentage IN NUMBER DEFAULT G_MISS_NUM
2508 ,p_acceptable_rate_increase IN NUMBER DEFAULT G_MISS_NUM
2509 ,p_acceptable_rate_decrease IN NUMBER DEFAULT G_MISS_NUM
2510 ,p_mrp_calculate_atp_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2511 ,p_auto_reduce_mps IN NUMBER DEFAULT G_MISS_NUM
2512 ,p_planning_time_fence_code IN NUMBER DEFAULT G_MISS_NUM
2513 ,p_planning_time_fence_days IN NUMBER DEFAULT G_MISS_NUM
2514 ,p_demand_time_fence_code IN NUMBER DEFAULT G_MISS_NUM
2515 ,p_demand_time_fence_days IN NUMBER DEFAULT G_MISS_NUM
2516 ,p_release_time_fence_code IN NUMBER DEFAULT G_MISS_NUM
2517 ,p_release_time_fence_days IN NUMBER DEFAULT G_MISS_NUM
2518 ,p_preprocessing_lead_time IN NUMBER DEFAULT G_MISS_NUM
2519 ,p_full_lead_time IN NUMBER DEFAULT G_MISS_NUM
2520 ,p_postprocessing_lead_time IN NUMBER DEFAULT G_MISS_NUM
2521 ,p_fixed_lead_time IN NUMBER DEFAULT G_MISS_NUM
2522 ,p_variable_lead_time IN NUMBER DEFAULT G_MISS_NUM
2523 ,p_cum_manufacturing_lead_time IN NUMBER DEFAULT G_MISS_NUM
2524 ,p_cumulative_total_lead_time IN NUMBER DEFAULT G_MISS_NUM
2525 ,p_lead_time_lot_size IN NUMBER DEFAULT G_MISS_NUM
2526 ,p_build_in_wip_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2527 ,p_wip_supply_type IN NUMBER DEFAULT G_MISS_NUM
2528 ,p_wip_supply_subinventory IN VARCHAR2 DEFAULT G_MISS_CHAR
2529 ,p_wip_supply_locator_id IN NUMBER DEFAULT G_MISS_NUM
2530 ,p_overcompletion_tolerance_ty IN NUMBER DEFAULT G_MISS_NUM
2531 ,p_overcompletion_tolerance_va IN NUMBER DEFAULT G_MISS_NUM
2532 ,p_customer_order_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2533 ,p_customer_order_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2534 ,p_shippable_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2535 ,p_internal_order_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2536 ,p_internal_order_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2537 ,p_so_transactions_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2538 ,p_pick_components_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2539 ,p_atp_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2540 ,p_replenish_to_order_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2541 ,p_atp_rule_id IN NUMBER DEFAULT G_MISS_NUM
2542 ,p_atp_components_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2543 ,p_ship_model_complete_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2544 ,p_picking_rule_id IN NUMBER DEFAULT G_MISS_NUM
2545 ,p_collateral_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2546 ,p_default_shipping_org IN NUMBER DEFAULT G_MISS_NUM
2547 ,p_returnable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2548 ,p_return_inspection_requireme IN NUMBER DEFAULT G_MISS_NUM
2549 ,p_over_shipment_tolerance IN NUMBER DEFAULT G_MISS_NUM
2550 ,p_under_shipment_tolerance IN NUMBER DEFAULT G_MISS_NUM
2551 ,p_over_return_tolerance IN NUMBER DEFAULT G_MISS_NUM
2552 ,p_under_return_tolerance IN NUMBER DEFAULT G_MISS_NUM
2553 ,p_invoiceable_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2554 ,p_invoice_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2555 ,p_accounting_rule_id IN NUMBER DEFAULT G_MISS_NUM
2556 ,p_invoicing_rule_id IN NUMBER DEFAULT G_MISS_NUM
2557 ,p_tax_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2558 ,p_sales_account IN NUMBER DEFAULT G_MISS_NUM
2559 ,p_payment_terms_id IN NUMBER DEFAULT G_MISS_NUM
2560 ,p_coverage_schedule_id IN NUMBER DEFAULT G_MISS_NUM
2561 ,p_service_duration IN NUMBER DEFAULT G_MISS_NUM
2562 ,p_service_duration_period_cod IN VARCHAR2 DEFAULT G_MISS_CHAR
2563 ,p_serviceable_product_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2564 ,p_service_starting_delay IN NUMBER DEFAULT G_MISS_NUM
2565 ,p_material_billable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2566 ,p_serviceable_component_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2567 ,p_preventive_maintenance_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2568 ,p_prorate_service_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2569 -- attribute not in the form
2570 ,p_serviceable_item_class_id IN NUMBER DEFAULT G_MISS_NUM
2571 -- attribute not in the form
2572 ,p_base_warranty_service_id IN NUMBER DEFAULT G_MISS_NUM
2573 -- attribute not in the form
2574 ,p_warranty_vendor_id IN NUMBER DEFAULT G_MISS_NUM
2575 -- attribute not in the form
2576 ,p_max_warranty_amount IN NUMBER DEFAULT G_MISS_NUM
2577 -- attribute not in the form
2578 ,p_response_time_period_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2579 -- attribute not in the form
2580 ,p_response_time_value IN NUMBER DEFAULT G_MISS_NUM
2581 -- attribute not in the form
2582 ,p_primary_specialist_id IN NUMBER DEFAULT G_MISS_NUM
2583 -- attribute not in the form
2584 ,p_secondary_specialist_id IN NUMBER DEFAULT G_MISS_NUM
2585 ,p_wh_update_date IN DATE DEFAULT G_MISS_DATE
2586 ,p_equipment_type IN NUMBER DEFAULT G_MISS_NUM
2587 ,p_recovered_part_disp_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2588 ,p_defect_tracking_on_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2589 ,p_event_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2590 ,p_electronic_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2591 ,p_downloadable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2592 ,p_vol_discount_exempt_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2593 ,p_coupon_exempt_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2594 ,p_comms_nl_trackable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2595 ,p_asset_creation_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2596 ,p_comms_activation_reqd_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2597 ,p_orderable_on_web_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2598 ,p_back_orderable_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2599 ,p_web_status IN VARCHAR2 DEFAULT G_MISS_CHAR
2600 ,p_indivisible_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2601 ,p_dimension_uom_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2602 ,p_unit_length IN NUMBER DEFAULT G_MISS_NUM
2603 ,p_unit_width IN NUMBER DEFAULT G_MISS_NUM
2604 ,p_unit_height IN NUMBER DEFAULT G_MISS_NUM
2605 ,p_bulk_picked_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2606 ,p_lot_status_enabled IN VARCHAR2 DEFAULT G_MISS_CHAR
2607 ,p_default_lot_status_id IN NUMBER DEFAULT G_MISS_NUM
2608 ,p_serial_status_enabled IN VARCHAR2 DEFAULT G_MISS_CHAR
2609 ,p_default_serial_status_id IN NUMBER DEFAULT G_MISS_NUM
2610 ,p_lot_split_enabled IN VARCHAR2 DEFAULT G_MISS_CHAR
2611 ,p_lot_merge_enabled IN VARCHAR2 DEFAULT G_MISS_CHAR
2612 ,p_inventory_carry_penalty IN NUMBER DEFAULT G_MISS_NUM
2613 ,p_operation_slack_penalty IN NUMBER DEFAULT G_MISS_NUM
2614 ,p_financing_allowed_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2615 ,p_eam_item_type IN NUMBER DEFAULT G_MISS_NUM
2616 ,p_eam_activity_type_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2617 ,p_eam_activity_cause_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2618 ,p_eam_act_notification_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2619 ,p_eam_act_shutdown_status IN VARCHAR2 DEFAULT G_MISS_CHAR
2620 ,p_dual_uom_control IN NUMBER DEFAULT G_MISS_NUM
2621 ,p_secondary_uom_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2622 ,p_dual_uom_deviation_high IN NUMBER DEFAULT G_MISS_NUM
2623 ,p_dual_uom_deviation_low IN NUMBER DEFAULT G_MISS_NUM
2624 -- derived attributes
2625 --,p_service_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2626 --,p_vendor_warranty_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2627 --,p_usage_item_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2628 ,p_contract_item_type_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2629 ,p_subscription_depend_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2630 ,p_serv_req_enabled_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2631 ,p_serv_billing_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2632 ,p_serv_importance_level IN NUMBER DEFAULT G_MISS_NUM
2633 ,p_planned_inv_point_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2634 ,p_lot_translate_enabled IN VARCHAR2 DEFAULT G_MISS_CHAR
2635 ,p_default_so_source_type IN VARCHAR2 DEFAULT G_MISS_CHAR
2636 ,p_create_supply_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2637 ,p_substitution_window_code IN NUMBER DEFAULT G_MISS_NUM
2638 ,p_substitution_window_days IN NUMBER DEFAULT G_MISS_NUM
2639 ,p_ib_item_instance_class IN VARCHAR2 DEFAULT G_MISS_CHAR
2640 ,p_config_model_type IN VARCHAR2 DEFAULT G_MISS_CHAR
2641 --added for 11.5.9 enh
2642 ,p_lot_substitution_enabled IN VARCHAR2 DEFAULT G_MISS_CHAR
2643 ,p_minimum_license_quantity IN NUMBER DEFAULT G_MISS_NUM
2644 ,p_eam_activity_source_code IN VARCHAR2 DEFAULT G_MISS_CHAR
2645 --added for 11.5.10 enh
2646 ,p_tracking_quantity_ind IN VARCHAR2 DEFAULT G_MISS_CHAR
2647 ,p_ont_pricing_qty_source IN VARCHAR2 DEFAULT G_MISS_CHAR
2648 ,p_secondary_default_ind IN VARCHAR2 DEFAULT G_MISS_CHAR
2649 ,p_option_specific_sourced IN NUMBER DEFAULT G_MISS_NUM
2650 ,p_approval_status IN VARCHAR2 DEFAULT G_MISS_CHAR
2651 --
2652 ,p_Item_Number IN VARCHAR2 DEFAULT G_MISS_CHAR
2653 ,p_segment1 IN VARCHAR2 DEFAULT G_MISS_CHAR
2654 ,p_segment2 IN VARCHAR2 DEFAULT G_MISS_CHAR
2655 ,p_segment3 IN VARCHAR2 DEFAULT G_MISS_CHAR
2656 ,p_segment4 IN VARCHAR2 DEFAULT G_MISS_CHAR
2657 ,p_segment5 IN VARCHAR2 DEFAULT G_MISS_CHAR
2658 ,p_segment6 IN VARCHAR2 DEFAULT G_MISS_CHAR
2659 ,p_segment7 IN VARCHAR2 DEFAULT G_MISS_CHAR
2660 ,p_segment8 IN VARCHAR2 DEFAULT G_MISS_CHAR
2661 ,p_segment9 IN VARCHAR2 DEFAULT G_MISS_CHAR
2662 ,p_segment10 IN VARCHAR2 DEFAULT G_MISS_CHAR
2663 ,p_segment11 IN VARCHAR2 DEFAULT G_MISS_CHAR
2664 ,p_segment12 IN VARCHAR2 DEFAULT G_MISS_CHAR
2665 ,p_segment13 IN VARCHAR2 DEFAULT G_MISS_CHAR
2666 ,p_segment14 IN VARCHAR2 DEFAULT G_MISS_CHAR
2667 ,p_segment15 IN VARCHAR2 DEFAULT G_MISS_CHAR
2668 ,p_segment16 IN VARCHAR2 DEFAULT G_MISS_CHAR
2669 ,p_segment17 IN VARCHAR2 DEFAULT G_MISS_CHAR
2670 ,p_segment18 IN VARCHAR2 DEFAULT G_MISS_CHAR
2671 ,p_segment19 IN VARCHAR2 DEFAULT G_MISS_CHAR
2672 ,p_segment20 IN VARCHAR2 DEFAULT G_MISS_CHAR
2673 ,p_summary_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2674 ,p_enabled_flag IN VARCHAR2 DEFAULT G_MISS_CHAR
2675 ,p_start_date_active IN DATE DEFAULT G_MISS_DATE
2676 ,p_end_date_active IN DATE DEFAULT G_MISS_DATE
2677 ,p_attribute_category IN VARCHAR2 DEFAULT G_MISS_CHAR
2678 ,p_attribute1 IN VARCHAR2 DEFAULT G_MISS_CHAR
2679 ,p_attribute2 IN VARCHAR2 DEFAULT G_MISS_CHAR
2680 ,p_attribute3 IN VARCHAR2 DEFAULT G_MISS_CHAR
2681 ,p_attribute4 IN VARCHAR2 DEFAULT G_MISS_CHAR
2682 ,p_attribute5 IN VARCHAR2 DEFAULT G_MISS_CHAR
2683 ,p_attribute6 IN VARCHAR2 DEFAULT G_MISS_CHAR
2684 ,p_attribute7 IN VARCHAR2 DEFAULT G_MISS_CHAR
2685 ,p_attribute8 IN VARCHAR2 DEFAULT G_MISS_CHAR
2686 ,p_attribute9 IN VARCHAR2 DEFAULT G_MISS_CHAR
2687 ,p_attribute10 IN VARCHAR2 DEFAULT G_MISS_CHAR
2688 ,p_attribute11 IN VARCHAR2 DEFAULT G_MISS_CHAR
2689 ,p_attribute12 IN VARCHAR2 DEFAULT G_MISS_CHAR
2690 ,p_attribute13 IN VARCHAR2 DEFAULT G_MISS_CHAR
2691 ,p_attribute14 IN VARCHAR2 DEFAULT G_MISS_CHAR
2692 ,p_attribute15 IN VARCHAR2 DEFAULT G_MISS_CHAR
2693 ,p_global_attribute_category IN VARCHAR2 DEFAULT G_MISS_CHAR
2694 ,p_global_attribute1 IN VARCHAR2 DEFAULT G_MISS_CHAR
2695 ,p_global_attribute2 IN VARCHAR2 DEFAULT G_MISS_CHAR
2696 ,p_global_attribute3 IN VARCHAR2 DEFAULT G_MISS_CHAR
2697 ,p_global_attribute4 IN VARCHAR2 DEFAULT G_MISS_CHAR
2698 ,p_global_attribute5 IN VARCHAR2 DEFAULT G_MISS_CHAR
2699 ,p_global_attribute6 IN VARCHAR2 DEFAULT G_MISS_CHAR
2700 ,p_global_attribute7 IN VARCHAR2 DEFAULT G_MISS_CHAR
2701 ,p_global_attribute8 IN VARCHAR2 DEFAULT G_MISS_CHAR
2702 ,p_global_attribute9 IN VARCHAR2 DEFAULT G_MISS_CHAR
2703 ,p_global_attribute10 IN VARCHAR2 DEFAULT G_MISS_CHAR
2704 ,p_creation_date IN DATE DEFAULT G_MISS_DATE
2705 ,p_created_by IN NUMBER DEFAULT G_MISS_NUM
2706 ,p_last_update_date IN DATE DEFAULT G_MISS_DATE
2707 ,p_last_updated_by IN NUMBER DEFAULT G_MISS_NUM
2708 ,p_last_update_login IN NUMBER DEFAULT G_MISS_NUM
2709 ,p_request_id IN NUMBER DEFAULT G_MISS_NUM
2710 ,p_program_application_id IN NUMBER DEFAULT G_MISS_NUM
2711 ,p_program_id IN NUMBER DEFAULT G_MISS_NUM
2712 ,p_program_update_date IN DATE DEFAULT G_MISS_DATE
2713 ,p_lifecycle_id IN NUMBER DEFAULT G_MISS_NUM
2714 ,p_current_phase_id IN NUMBER DEFAULT G_MISS_NUM
2715 -- Returned item id
2716 ,x_Inventory_Item_Id OUT NOCOPY NUMBER
2717 ,x_Organization_Id OUT NOCOPY NUMBER
2718 ,x_return_status OUT NOCOPY VARCHAR2
2719 ,x_msg_count OUT NOCOPY NUMBER
2720 ,x_msg_data OUT NOCOPY VARCHAR2
2721 ) IS
2722 ------------------------------------------------------------------
2723 -- Start Of comments
2724 --
2725 -- Function name : Process_Item
2726 -- Type : Public
2727 -- Pre-reqs : IOI should be functional
2728 -- Functionality : Process (CREATE/UPDATE) one item using IOI
2729 -- Notes : Scalar Signature to Process Item
2730 --
2731 --
2732 -- History :
2733 -- 23-SEP-2003 Sridhar Rajaparthi Creation (bug 3143834)
2734 --
2735 -- END OF comments
2736 ------------------------------------------------------------------
2737
2738 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Scalar';
2739 l_api_version CONSTANT NUMBER := 1.0;
2740
2741 indx BINARY_INTEGER := 1;
2742 l_item_tbl EGO_ITEM_PUB.Item_Tbl_Type;
2743 l_item_created_tbl EGO_ITEM_PUB.Item_Tbl_Type;
2744
2745 BEGIN
2746 developer_debug (' ISS: Started Item Scalar Signature ');
2747
2748 -- standard check for API validation
2749 IF NOT FND_API.Compatible_API_Call (l_api_version,
2750 p_api_version,
2751 l_api_name,
2752 G_PKG_NAME) THEN
2753 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2754 END IF;
2755
2756 developer_debug (' ISS: API version valid ');
2757 -- create save point
2758 IF FND_API.To_Boolean(p_commit) THEN
2759 developer_debug (' ISS: Save Point Created ');
2760 SAVEPOINT Process_Item_Scalar;
2761 END IF;
2762
2763 -- Initialize message list
2764 IF FND_API.To_Boolean(p_init_msg_list) THEN
2765 developer_debug (' ISS: Message list initialized ');
2766 FND_MSG_PUB.Initialize;
2767 END IF;
2768
2769 developer_debug (' ISS: processing for transaction type ' || p_transaction_type);
2770 -- todo validate the parameters
2771 IF p_transaction_type IN (G_CREATE_TRANSACTION_TYPE, G_UPDATE_TRANSACTION_TYPE) THEN
2772 --
2773 -- copy the passed values into the table.
2774 -- create the table to store all the values.
2775 --
2776 -- pre IOI processing
2777 --
2778 IF p_transaction_type = G_CREATE_TRANSACTION_TYPE THEN
2779 l_item_tbl(indx).item_number := p_item_number;
2780 l_item_tbl(indx).segment1 := p_segment1;
2781 l_item_tbl(indx).segment2 := p_segment2;
2782 l_item_tbl(indx).segment3 := p_segment3;
2783 l_item_tbl(indx).segment4 := p_segment4;
2784 l_item_tbl(indx).segment5 := p_segment5;
2785 l_item_tbl(indx).segment6 := p_segment6;
2786 l_item_tbl(indx).segment7 := p_segment7;
2787 l_item_tbl(indx).segment8 := p_segment8;
2788 l_item_tbl(indx).segment9 := p_segment9;
2789 l_item_tbl(indx).segment10 := p_segment10;
2790 l_item_tbl(indx).segment11 := p_segment11;
2791 l_item_tbl(indx).segment12 := p_segment12;
2792 l_item_tbl(indx).segment13 := p_segment13;
2793 l_item_tbl(indx).segment14 := p_segment14;
2794 l_item_tbl(indx).segment15 := p_segment15;
2795 l_item_tbl(indx).segment16 := p_segment16;
2796 l_item_tbl(indx).segment17 := p_segment17;
2797 l_item_tbl(indx).segment18 := p_segment18;
2798 l_item_tbl(indx).segment19 := p_segment19;
2799 l_item_tbl(indx).segment20 := p_segment20;
2800 ELSE
2801 --
2802 -- do not set segment1..20 as IOI cross checks
2803 -- the inventory_item_id and the segments passed
2804 --
2805 NULL;
2806 END IF;
2807 l_item_tbl(indx).transaction_type := p_transaction_type;
2808 -- not passed do leave as it is
2809 -- l_item_tbl(indx).return_status := NULL;
2810 l_item_tbl(indx).language_code := p_language_code;
2811 l_item_tbl(indx).template_id := p_template_id;
2812 l_item_tbl(indx).template_name := p_template_name;
2813 --
2814 -- item identifier
2815 --
2816 l_item_tbl(indx).inventory_item_id := p_inventory_item_id;
2817 l_item_tbl(indx).summary_flag := p_summary_flag;
2818 l_item_tbl(indx).enabled_flag := p_enabled_flag;
2819 l_item_tbl(indx).start_date_active := p_start_date_active;
2820 l_item_tbl(indx).end_date_active := p_end_date_active;
2821 --
2822 -- organization
2823 --
2824 l_item_tbl(indx).organization_id := p_organization_id;
2825 -- not passed do leave as it is
2826 -- l_item_tbl(indx).organization_code := NULL;
2827 --
2828 -- item catalog group (user item type)
2829 --
2830 l_item_tbl(indx).item_catalog_group_id := p_item_catalog_group_id;
2831 l_item_tbl(indx).catalog_status_flag := p_catalog_status_flag;
2832 --
2833 -- lifecycle
2834 --
2835 l_item_tbl(indx).lifecycle_id := p_lifecycle_id;
2836 l_item_tbl(indx).current_phase_id := p_current_phase_id;
2837 --
2838 -- main attributes
2839 --
2840 l_item_tbl(indx).description := p_description;
2841 l_item_tbl(indx).long_description := p_long_description;
2842 l_item_tbl(indx).primary_uom_code := p_primary_uom_code;
2843 l_item_tbl(indx).allowed_units_lookup_code := p_allowed_units_lookup_code;
2844 l_item_tbl(indx).inventory_item_status_code := p_inventory_item_status_code;
2845 l_item_tbl(indx).dual_uom_control := p_dual_uom_control;
2846 l_item_tbl(indx).secondary_uom_code := p_secondary_uom_code;
2847 l_item_tbl(indx).dual_uom_deviation_high := p_dual_uom_deviation_high;
2848 l_item_tbl(indx).dual_uom_deviation_low := p_dual_uom_deviation_low;
2849 l_item_tbl(indx).item_type := p_item_type;
2850 -- inventory
2851 l_item_tbl(indx).inventory_item_flag := p_inventory_item_flag;
2852 l_item_tbl(indx).stock_enabled_flag := p_stock_enabled_flag;
2853 l_item_tbl(indx).mtl_transactions_enabled_flag := p_mtl_transactions_enabled_fl;
2854 l_item_tbl(indx).revision_qty_control_code := p_revision_qty_control_code;
2855 l_item_tbl(indx).lot_control_code := p_lot_control_code;
2856 l_item_tbl(indx).auto_lot_alpha_prefix := p_auto_lot_alpha_prefix;
2857 l_item_tbl(indx).start_auto_lot_number := p_start_auto_lot_number;
2858 l_item_tbl(indx).serial_number_control_code := p_serial_number_control_code;
2859 l_item_tbl(indx).auto_serial_alpha_prefix := p_auto_serial_alpha_prefix;
2860 l_item_tbl(indx).start_auto_serial_number := p_start_auto_serial_number;
2861 l_item_tbl(indx).shelf_life_code := p_shelf_life_code;
2862 l_item_tbl(indx).shelf_life_days := p_shelf_life_days;
2863 l_item_tbl(indx).restrict_subinventories_code := p_restrict_subinventories_cod;
2864 l_item_tbl(indx).location_control_code := p_location_control_code;
2865 l_item_tbl(indx).restrict_locators_code := p_restrict_locators_code;
2866 l_item_tbl(indx).reservable_type := p_reservable_type;
2867 l_item_tbl(indx).cycle_count_enabled_flag := p_cycle_count_enabled_flag;
2868 l_item_tbl(indx).negative_measurement_error := p_negative_measurement_error;
2869 l_item_tbl(indx).positive_measurement_error := p_positive_measurement_error;
2870 l_item_tbl(indx).check_shortages_flag := p_check_shortages_flag;
2871 l_item_tbl(indx).lot_status_enabled := p_lot_status_enabled;
2872 l_item_tbl(indx).default_lot_status_id := p_default_lot_status_id;
2873 l_item_tbl(indx).serial_status_enabled := p_serial_status_enabled;
2874 l_item_tbl(indx).default_serial_status_id := p_default_serial_status_id;
2875 l_item_tbl(indx).lot_split_enabled := p_lot_split_enabled;
2876 l_item_tbl(indx).lot_merge_enabled := p_lot_merge_enabled;
2877 l_item_tbl(indx).lot_translate_enabled := p_lot_translate_enabled;
2878 l_item_tbl(indx).lot_substitution_enabled := p_lot_substitution_enabled;
2879 l_item_tbl(indx).bulk_picked_flag := p_bulk_picked_flag;
2880 -- bills of material
2881 l_item_tbl(indx).bom_item_type := p_bom_item_type;
2882 l_item_tbl(indx).bom_enabled_flag := p_bom_enabled_flag;
2883 l_item_tbl(indx).base_item_id := p_base_item_id;
2884 l_item_tbl(indx).eng_item_flag := p_eng_item_flag;
2885 l_item_tbl(indx).engineering_item_id := p_engineering_item_id;
2886 l_item_tbl(indx).engineering_ecn_code := p_engineering_ecn_code;
2887 l_item_tbl(indx).engineering_date := p_engineering_date;
2888 l_item_tbl(indx).effectivity_control := p_effectivity_control;
2889 l_item_tbl(indx).config_model_type := p_config_model_type;
2890 l_item_tbl(indx).product_family_item_id := p_product_family_item_id;
2891 -- costing
2892 l_item_tbl(indx).costing_enabled_flag := p_costing_enabled_flag;
2893 l_item_tbl(indx).inventory_asset_flag := p_inventory_asset_flag;
2894 l_item_tbl(indx).cost_of_sales_account := p_cost_of_sales_account;
2895 l_item_tbl(indx).default_include_in_rollup_flag := p_default_include_in_rollup_f;
2896 l_item_tbl(indx).std_lot_size := p_std_lot_size;
2897 -- enterprise asset management
2898 l_item_tbl(indx).eam_item_type := p_eam_item_type;
2899 l_item_tbl(indx).eam_activity_type_code := p_eam_activity_type_code;
2900 l_item_tbl(indx).eam_activity_cause_code := p_eam_activity_cause_code;
2901 l_item_tbl(indx).eam_activity_source_code := p_eam_activity_source_code;
2902 l_item_tbl(indx).eam_act_shutdown_status := p_eam_act_shutdown_status;
2903 l_item_tbl(indx).eam_act_notification_flag := p_eam_act_notification_flag;
2904 -- purchasing
2905 l_item_tbl(indx).purchasing_item_flag := p_purchasing_item_flag;
2906 l_item_tbl(indx).purchasing_enabled_flag := p_purchasing_enabled_flag;
2907 l_item_tbl(indx).buyer_id := p_buyer_id;
2908 l_item_tbl(indx).must_use_approved_vendor_flag := p_must_use_approved_vendor_fl;
2909 l_item_tbl(indx).purchasing_tax_code := p_purchasing_tax_code;
2910 l_item_tbl(indx).taxable_flag := p_taxable_flag;
2911 l_item_tbl(indx).receive_close_tolerance := p_receive_close_tolerance;
2912 l_item_tbl(indx).allow_item_desc_update_flag := p_allow_item_desc_update_flag;
2913 l_item_tbl(indx).inspection_required_flag := p_inspection_required_flag;
2914 l_item_tbl(indx).receipt_required_flag := p_receipt_required_flag;
2915 l_item_tbl(indx).market_price := p_market_price;
2916 l_item_tbl(indx).un_number_id := p_un_number_id;
2917 l_item_tbl(indx).hazard_class_id := p_hazard_class_id;
2918 l_item_tbl(indx).rfq_required_flag := p_rfq_required_flag;
2919 l_item_tbl(indx).list_price_per_unit := p_list_price_per_unit;
2920 l_item_tbl(indx).price_tolerance_percent := p_price_tolerance_percent;
2921 l_item_tbl(indx).asset_category_id := p_asset_category_id;
2922 l_item_tbl(indx).rounding_factor := p_rounding_factor;
2923 l_item_tbl(indx).unit_of_issue := p_unit_of_issue;
2924 l_item_tbl(indx).outside_operation_flag := p_outside_operation_flag;
2925 l_item_tbl(indx).outside_operation_uom_type := p_outside_operation_uom_type;
2926 l_item_tbl(indx).invoice_close_tolerance := p_invoice_close_tolerance;
2927 l_item_tbl(indx).encumbrance_account := p_encumbrance_account;
2928 l_item_tbl(indx).expense_account := p_expense_account;
2929 -- old db column used for backword compatability only. not used currently
2930 -- l_item_tbl(indx).expense_billable_flag := p_expense_billable_flag;
2931 l_item_tbl(indx).qty_rcv_exception_code := p_qty_rcv_exception_code;
2932 l_item_tbl(indx).receiving_routing_id := p_receiving_routing_id;
2933 l_item_tbl(indx).qty_rcv_tolerance := p_qty_rcv_tolerance;
2934 l_item_tbl(indx).enforce_ship_to_location_code := p_enforce_ship_to_location_c;
2935 l_item_tbl(indx).allow_substitute_receipts_flag := p_allow_substitute_receipts_f;
2936 l_item_tbl(indx).allow_unordered_receipts_flag := p_allow_unordered_receipts_fl;
2937 l_item_tbl(indx).allow_express_delivery_flag := p_allow_express_delivery_flag;
2938 l_item_tbl(indx).days_early_receipt_allowed := p_days_early_receipt_allowed;
2939 l_item_tbl(indx).days_late_receipt_allowed := p_days_late_receipt_allowed;
2940 l_item_tbl(indx).receipt_days_exception_code := p_receipt_days_exception_code;
2941 -- physical
2942 l_item_tbl(indx).weight_uom_code := p_weight_uom_code;
2943 l_item_tbl(indx).unit_weight := p_unit_weight;
2944 l_item_tbl(indx).volume_uom_code := p_volume_uom_code;
2945 l_item_tbl(indx).unit_volume := p_unit_volume;
2946 l_item_tbl(indx).container_item_flag := p_container_item_flag;
2947 l_item_tbl(indx).vehicle_item_flag := p_vehicle_item_flag;
2948 l_item_tbl(indx).maximum_load_weight := p_maximum_load_weight;
2949 l_item_tbl(indx).minimum_fill_percent := p_minimum_fill_percent;
2950 l_item_tbl(indx).internal_volume := p_internal_volume;
2951 l_item_tbl(indx).container_type_code := p_container_type_code;
2952 l_item_tbl(indx).collateral_flag := p_collateral_flag;
2953 l_item_tbl(indx).event_flag := p_event_flag;
2954 l_item_tbl(indx).equipment_type := p_equipment_type;
2955 l_item_tbl(indx).electronic_flag := p_electronic_flag;
2956 l_item_tbl(indx).downloadable_flag := p_downloadable_flag;
2957 l_item_tbl(indx).indivisible_flag := p_indivisible_flag;
2958 l_item_tbl(indx).dimension_uom_code := p_dimension_uom_code;
2959 l_item_tbl(indx).unit_length := p_unit_length;
2960 l_item_tbl(indx).unit_width := p_unit_width;
2961 l_item_tbl(indx).unit_height := p_unit_height;
2962 --
2963 l_item_tbl(indx).inventory_planning_code := p_inventory_planning_code;
2964 l_item_tbl(indx).planner_code := p_planner_code;
2965 l_item_tbl(indx).planning_make_buy_code := p_planning_make_buy_code;
2966 l_item_tbl(indx).min_minmax_quantity := p_min_minmax_quantity;
2967 l_item_tbl(indx).max_minmax_quantity := p_max_minmax_quantity;
2968 l_item_tbl(indx).safety_stock_bucket_days := p_safety_stock_bucket_days;
2969 l_item_tbl(indx).carrying_cost := p_carrying_cost;
2970 l_item_tbl(indx).order_cost := p_order_cost;
2971 l_item_tbl(indx).mrp_safety_stock_percent := p_mrp_safety_stock_percent;
2972 l_item_tbl(indx).mrp_safety_stock_code := p_mrp_safety_stock_code;
2973 l_item_tbl(indx).fixed_order_quantity := p_fixed_order_quantity;
2974 l_item_tbl(indx).fixed_days_supply := p_fixed_days_supply;
2975 l_item_tbl(indx).minimum_order_quantity := p_minimum_order_quantity;
2976 l_item_tbl(indx).maximum_order_quantity := p_maximum_order_quantity;
2977 l_item_tbl(indx).fixed_lot_multiplier := p_fixed_lot_multiplier;
2978 l_item_tbl(indx).source_type := p_source_type;
2979 l_item_tbl(indx).source_organization_id := p_source_organization_id;
2980 l_item_tbl(indx).source_subinventory := p_source_subinventory;
2981 l_item_tbl(indx).mrp_planning_code := p_mrp_planning_code;
2982 l_item_tbl(indx).ato_forecast_control := p_ato_forecast_control;
2983 l_item_tbl(indx).planning_exception_set := p_planning_exception_set;
2984 l_item_tbl(indx).shrinkage_rate := p_shrinkage_rate;
2985 l_item_tbl(indx).end_assembly_pegging_flag := p_end_assembly_pegging_flag;
2986 l_item_tbl(indx).rounding_control_type := p_rounding_control_type;
2987 l_item_tbl(indx).planned_inv_point_flag := p_planned_inv_point_flag;
2988 l_item_tbl(indx).create_supply_flag := p_create_supply_flag;
2989 l_item_tbl(indx).acceptable_early_days := p_acceptable_early_days;
2990 l_item_tbl(indx).mrp_calculate_atp_flag := p_mrp_calculate_atp_flag;
2991 l_item_tbl(indx).auto_reduce_mps := p_auto_reduce_mps;
2992 l_item_tbl(indx).repetitive_planning_flag := p_repetitive_planning_flag;
2993 l_item_tbl(indx).overrun_percentage := p_overrun_percentage;
2994 l_item_tbl(indx).acceptable_rate_decrease := p_acceptable_rate_decrease;
2995 l_item_tbl(indx).acceptable_rate_increase := p_acceptable_rate_increase;
2996 l_item_tbl(indx).planning_time_fence_code := p_planning_time_fence_code;
2997 l_item_tbl(indx).planning_time_fence_days := p_planning_time_fence_days;
2998 l_item_tbl(indx).demand_time_fence_code := p_demand_time_fence_code;
2999 l_item_tbl(indx).demand_time_fence_days := p_demand_time_fence_days;
3000 l_item_tbl(indx).release_time_fence_code := p_release_time_fence_code;
3001 l_item_tbl(indx).release_time_fence_days := p_release_time_fence_days;
3002 l_item_tbl(indx).substitution_window_code := p_substitution_window_code;
3003 l_item_tbl(indx).substitution_window_days := p_substitution_window_days;
3004 -- lead times
3005 l_item_tbl(indx).preprocessing_lead_time := p_preprocessing_lead_time;
3006 l_item_tbl(indx).full_lead_time := p_full_lead_time;
3007 l_item_tbl(indx).postprocessing_lead_time := p_postprocessing_lead_time;
3008 l_item_tbl(indx).fixed_lead_time := p_fixed_lead_time;
3009 l_item_tbl(indx).variable_lead_time := p_variable_lead_time;
3010 l_item_tbl(indx).cum_manufacturing_lead_time := p_cum_manufacturing_lead_time;
3011 l_item_tbl(indx).cumulative_total_lead_time := p_cumulative_total_lead_time;
3012 l_item_tbl(indx).lead_time_lot_size := p_lead_time_lot_size;
3013 -- wip
3014 l_item_tbl(indx).build_in_wip_flag := p_build_in_wip_flag;
3015 l_item_tbl(indx).wip_supply_type := p_wip_supply_type;
3016 l_item_tbl(indx).wip_supply_subinventory := p_wip_supply_subinventory;
3017 l_item_tbl(indx).wip_supply_locator_id := p_wip_supply_locator_id;
3018 l_item_tbl(indx).overcompletion_tolerance_type := p_overcompletion_tolerance_ty;
3019 l_item_tbl(indx).overcompletion_tolerance_value := p_overcompletion_tolerance_va;
3020 l_item_tbl(indx).inventory_carry_penalty := p_inventory_carry_penalty;
3021 l_item_tbl(indx).operation_slack_penalty := p_operation_slack_penalty;
3022 -- order management
3023 l_item_tbl(indx).customer_order_flag := p_customer_order_flag;
3024 l_item_tbl(indx).customer_order_enabled_flag := p_customer_order_enabled_flag;
3025 l_item_tbl(indx).internal_order_flag := p_internal_order_flag;
3026 l_item_tbl(indx).internal_order_enabled_flag := p_internal_order_enabled_flag;
3027 l_item_tbl(indx).shippable_item_flag := p_shippable_item_flag;
3028 l_item_tbl(indx).so_transactions_flag := p_so_transactions_flag;
3029 l_item_tbl(indx).picking_rule_id := p_picking_rule_id;
3030 l_item_tbl(indx).pick_components_flag := p_pick_components_flag;
3031 l_item_tbl(indx).replenish_to_order_flag := p_replenish_to_order_flag;
3032 l_item_tbl(indx).atp_flag := p_atp_flag;
3033 l_item_tbl(indx).atp_components_flag := p_atp_components_flag;
3034 l_item_tbl(indx).atp_rule_id := p_atp_rule_id;
3035 l_item_tbl(indx).ship_model_complete_flag := p_ship_model_complete_flag;
3036 l_item_tbl(indx).default_shipping_org := p_default_shipping_org;
3037 l_item_tbl(indx).default_so_source_type := p_default_so_source_type;
3038 l_item_tbl(indx).returnable_flag := p_returnable_flag;
3039 l_item_tbl(indx).return_inspection_requirement := p_return_inspection_requireme;
3040 l_item_tbl(indx).over_shipment_tolerance := p_over_shipment_tolerance;
3041 l_item_tbl(indx).under_shipment_tolerance := p_under_shipment_tolerance;
3042 l_item_tbl(indx).over_return_tolerance := p_over_return_tolerance;
3043 l_item_tbl(indx).under_return_tolerance := p_under_return_tolerance;
3044 l_item_tbl(indx).financing_allowed_flag := p_financing_allowed_flag;
3045 l_item_tbl(indx).vol_discount_exempt_flag := p_vol_discount_exempt_flag;
3046 l_item_tbl(indx).coupon_exempt_flag := p_coupon_exempt_flag;
3047 l_item_tbl(indx).invoiceable_item_flag := p_invoiceable_item_flag;
3048 l_item_tbl(indx).invoice_enabled_flag := p_invoice_enabled_flag;
3049 l_item_tbl(indx).accounting_rule_id := p_accounting_rule_id;
3050 l_item_tbl(indx).invoicing_rule_id := p_invoicing_rule_id;
3051 l_item_tbl(indx).tax_code := p_tax_code;
3052 l_item_tbl(indx).sales_account := p_sales_account;
3053 l_item_tbl(indx).payment_terms_id := p_payment_terms_id;
3054 -- service
3055 l_item_tbl(indx).contract_item_type_code := p_contract_item_type_code;
3056 l_item_tbl(indx).service_duration_period_code := p_service_duration_period_cod;
3057 l_item_tbl(indx).service_duration := p_service_duration;
3058 l_item_tbl(indx).coverage_schedule_id := p_coverage_schedule_id;
3059 l_item_tbl(indx).subscription_depend_flag := p_subscription_depend_flag;
3060 l_item_tbl(indx).serv_importance_level := p_serv_importance_level;
3061 l_item_tbl(indx).serv_req_enabled_code := p_serv_req_enabled_code;
3062 l_item_tbl(indx).comms_activation_reqd_flag := p_comms_activation_reqd_flag;
3063 l_item_tbl(indx).serviceable_product_flag := p_serviceable_product_flag;
3064 l_item_tbl(indx).material_billable_flag := p_material_billable_flag;
3065 l_item_tbl(indx).serv_billing_enabled_flag := p_serv_billing_enabled_flag;
3066 l_item_tbl(indx).defect_tracking_on_flag := p_defect_tracking_on_flag;
3067 l_item_tbl(indx).recovered_part_disp_code := p_recovered_part_disp_code;
3068 l_item_tbl(indx).comms_nl_trackable_flag := p_comms_nl_trackable_flag;
3069 l_item_tbl(indx).asset_creation_code := p_asset_creation_code;
3070 l_item_tbl(indx).ib_item_instance_class := p_ib_item_instance_class;
3071 l_item_tbl(indx).service_starting_delay := p_service_starting_delay;
3072 -- web option
3073 l_item_tbl(indx).web_status := p_web_status;
3074 l_item_tbl(indx).orderable_on_web_flag := p_orderable_on_web_flag;
3075 l_item_tbl(indx).back_orderable_flag := p_back_orderable_flag;
3076 l_item_tbl(indx).minimum_license_quantity := p_minimum_license_quantity;
3077 -- descriptive flex
3078 l_item_tbl(indx).attribute_category := p_attribute_category;
3079 l_item_tbl(indx).attribute1 := p_attribute1;
3080 l_item_tbl(indx).attribute2 := p_attribute2;
3081 l_item_tbl(indx).attribute3 := p_attribute3;
3082 l_item_tbl(indx).attribute4 := p_attribute4;
3083 l_item_tbl(indx).attribute5 := p_attribute5;
3084 l_item_tbl(indx).attribute6 := p_attribute6;
3085 l_item_tbl(indx).attribute7 := p_attribute7;
3086 l_item_tbl(indx).attribute8 := p_attribute8;
3087 l_item_tbl(indx).attribute9 := p_attribute9;
3088 l_item_tbl(indx).attribute10 := p_attribute10;
3089 l_item_tbl(indx).attribute11 := p_attribute11;
3090 l_item_tbl(indx).attribute12 := p_attribute12;
3091 l_item_tbl(indx).attribute13 := p_attribute13;
3092 l_item_tbl(indx).attribute14 := p_attribute14;
3093 l_item_tbl(indx).attribute15 := p_attribute15;
3094 -- global descriptive flex
3095 l_item_tbl(indx).global_attribute_category := p_global_attribute_category;
3096 l_item_tbl(indx).global_attribute1 := p_global_attribute1;
3097 l_item_tbl(indx).global_attribute2 := p_global_attribute2;
3098 l_item_tbl(indx).global_attribute3 := p_global_attribute3;
3099 l_item_tbl(indx).global_attribute4 := p_global_attribute4;
3100 l_item_tbl(indx).global_attribute5 := p_global_attribute5;
3101 l_item_tbl(indx).global_attribute6 := p_global_attribute6;
3102 l_item_tbl(indx).global_attribute7 := p_global_attribute7;
3103 l_item_tbl(indx).global_attribute8 := p_global_attribute8;
3104 l_item_tbl(indx).global_attribute9 := p_global_attribute9;
3105 l_item_tbl(indx).global_attribute10 := p_global_attribute10;
3106 -- who / maintenance columns are not required
3107 -- l_item_tbl(indx).object_version_number := p_object_version_number;
3108 -- l_item_tbl(indx).creation_date := p_creation_date;
3109 -- l_item_tbl(indx).created_by := p_created_by;
3110 -- l_item_tbl(indx).last_update_date := p_last_update_date;
3111 -- l_item_tbl(indx).last_updated_by := p_last_updated_by;
3112 -- l_item_tbl(indx).last_update_login := p_last_update_login;
3113
3114 developer_debug (' ISS: table created for PRE IOI Processing ');
3115
3116 EGO_Item_PVT.G_Item_Tbl := l_item_tbl;
3117 -----------------------------------------------------------------------------
3118 -- Call the Private API to process items table.
3119 -----------------------------------------------------------------------------
3120 developer_debug (' ISS: calling EGO_ITEM_PVT.Process_Items ');
3121 EGO_Item_PVT.Process_Items (
3122 p_commit => p_commit
3123 ,x_return_status => x_return_status
3124 ,x_msg_count => x_msg_count);
3125 developer_debug (' ISS: returned from EGO_ITEM_PVT.Process_Items -> ' || x_return_status);
3126 -----------------------------------------------------------------------------
3127 -- Return items data from the re-populated global table
3128 -----------------------------------------------------------------------------
3129 l_item_created_tbl := EGO_Item_PVT.G_Item_Tbl;
3130 developer_debug (' ISS: copied the item to created table ');
3131
3132 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3133 -- item created successfully
3134 x_inventory_item_id := l_item_created_tbl(indx).inventory_item_id;
3135 x_organization_id := l_item_created_tbl(indx).organization_id;
3136 developer_debug (' ISS: copied the item created values ');
3137 --
3138 -- post IOI process
3139 --
3140 IF p_transaction_type = G_CREATE_TRANSACTION_TYPE THEN
3141 --
3142 -- transaction type = 'CREATE'
3143 -- do specific validations here
3144 --
3145 NULL;
3146 ELSIF p_transaction_type = G_UPDATE_TRANSACTION_TYPE THEN
3147 --
3148 -- transaction type = 'UPDATE'
3149 --
3150 developer_debug (' ISS: calling Update_item_number ');
3151 EGO_ITEM_PUB.Update_Item_Number (
3152 p_Inventory_Item_Id => x_inventory_item_id
3153 ,p_Item_Number => p_Item_Number
3154 ,p_Segment1 => NULL
3155 ,p_Segment2 => NULL
3156 ,p_Segment3 => NULL
3157 ,p_Segment4 => NULL
3158 ,p_Segment5 => NULL
3159 ,p_Segment6 => NULL
3160 ,p_Segment7 => NULL
3161 ,p_Segment8 => NULL
3162 ,p_Segment9 => NULL
3163 ,p_Segment10 => NULL
3164 ,p_Segment11 => NULL
3165 ,p_Segment12 => NULL
3166 ,p_Segment13 => NULL
3167 ,p_Segment14 => NULL
3168 ,p_Segment15 => NULL
3169 ,p_Segment16 => NULL
3170 ,p_Segment17 => NULL
3171 ,p_Segment18 => NULL
3172 ,p_Segment19 => NULL
3173 ,p_Segment20 => NULL
3174 ,p_New_Segment1 => p_Segment1
3175 ,p_New_Segment2 => p_Segment2
3176 ,p_New_Segment3 => p_Segment3
3177 ,p_New_Segment4 => p_Segment4
3178 ,p_New_Segment5 => p_Segment5
3179 ,p_New_Segment6 => p_Segment6
3180 ,p_New_Segment7 => p_Segment7
3181 ,p_New_Segment8 => p_Segment8
3182 ,p_New_Segment9 => p_Segment9
3183 ,p_New_Segment10 => p_Segment10
3184 ,p_New_Segment11 => p_Segment11
3185 ,p_New_Segment12 => p_Segment12
3186 ,p_New_Segment13 => p_Segment13
3187 ,p_New_Segment14 => p_Segment14
3188 ,p_New_Segment15 => p_Segment15
3189 ,p_New_Segment16 => p_Segment16
3190 ,p_New_Segment17 => p_Segment17
3191 ,p_New_Segment18 => p_Segment18
3192 ,p_New_Segment19 => p_Segment19
3193 ,p_New_Segment20 => p_Segment20
3194 ,x_Item_Tbl => l_item_created_tbl
3195 ,x_return_status => x_return_status
3196 );
3197 developer_debug (' ISS: returned from Update_Item_Number -> ' || x_return_status);
3198 IF x_return_status = G_RET_STS_ERROR THEN
3199 RAISE FND_API.G_EXC_ERROR;
3200 ELSIF x_return_status = G_RET_STS_UNEXP_ERROR THEN
3201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3202 END IF;
3203 END IF;
3204 -- common processing after update
3205 -- call the update approval status routine now
3206 developer_debug (' ISS: calling EGO_ITEM_PUB.Update_Item_Approval_Status ');
3207 developer_debug (' ISS: inventory_item_id -> '||to_char(x_inventory_item_id));
3208 developer_debug (' ISS: organization_id -> '||to_char(x_organization_id));
3209 developer_debug (' ISS: approval_status -> '||p_approval_status);
3210 EGO_ITEM_PUB.Update_Item_Approval_Status (
3211 p_inventory_item_id => x_inventory_item_id
3212 ,p_organization_id => x_organization_id
3213 ,p_approval_status => p_approval_status
3214 );
3215 ELSE
3216 -- messages already logged by EGO_ITEM_PVT
3217 RETURN;
3218 END IF; -- x_return_status = FND_API.G_RET_STS_SUCCESS from IOI
3219
3220 ELSIF p_transaction_type = G_COPY_TRANSACTION_TYPE THEN
3221 -- transaction type = 'COPY'
3222 -- to be implemented
3223 FND_MESSAGE.Set_Name ('EGO', 'EGO_PROGRAM_NOT_IMPLEMENTED');
3224 FND_MSG_PUB.Add;
3225 RAISE FND_API.G_EXC_ERROR;
3226 END IF;
3227
3228 IF FND_API.To_Boolean(p_commit) THEN
3229 COMMIT WORK;
3230 END IF;
3231 x_return_status := G_RET_STS_SUCCESS;
3232 developer_debug (' ISS: DONE with status -> '||x_return_status);
3233
3234 EXCEPTION
3235 WHEN FND_API.G_EXC_ERROR THEN
3236 IF FND_API.To_Boolean(p_commit) THEN
3237 ROLLBACK TO Process_Item_Scalar;
3238 END IF;
3239 x_return_status := G_RET_STS_ERROR;
3240 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3241 ,p_count => x_msg_count
3242 ,p_data => x_msg_data);
3243 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3244 IF FND_API.To_Boolean(p_commit) THEN
3245 ROLLBACK TO Process_Item_Scalar;
3246 END IF;
3247 x_RETURN_STATUS := G_RET_STS_UNEXP_ERROR;
3248 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3249 ,p_count => x_msg_count
3250 ,p_data => x_msg_data);
3251 WHEN OTHERS THEN
3252 IF FND_API.To_Boolean(p_commit) THEN
3253 ROLLBACK TO Process_Item_Scalar;
3254 END IF;
3255 x_return_status := G_RET_STS_UNEXP_ERROR;
3256 -- for PL/SQL errors
3257 FND_MESSAGE.Set_Name('EGO', 'EGO_PLSQL_ERR');
3258 FND_MESSAGE.Set_Token('PKG_NAME', G_PKG_NAME);
3259 FND_MESSAGE.Set_Token('API_NAME', l_api_name);
3260 FND_MESSAGE.Set_Token('SQL_ERR_MSG', SQLERRM);
3261 FND_MSG_PUB.Add;
3262 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE
3263 ,p_count => x_msg_count
3264 ,p_data => x_msg_data);
3265 END Process_Item;
3266
3267
3268 END EGO_UI_ITEM_PUB;