[Home] [Help]
PACKAGE BODY: APPS.CSP_PLANNING_PARAMETERS_PVT
Source
1 package body csp_planning_parameters_pvt as
2 /* $Header: cspvpprb.pls 120.0.12020000.4 2013/03/21 04:06:45 sunarasi noship $ */
3 -- Start of Comments
4 -- Package name :csp_planning_parameters_pvt
5 -- Purpose :
6 -- History :
7 -- 01-Mar-13, sunarasi
8 --
9 -- NOTE :
10 -- End of Comments
11 PROCEDURE CREATE_PLANNING_PARAMETERS
12 (p_planning_parameters_id number
13 ,p_CREATED_BY NUMBER
14 ,p_CREATION_DATE DATE
15 ,p_LAST_UPDATED_BY NUMBER
16 ,p_LAST_UPDATE_DATE DATE
17 ,p_LAST_UPDATE_LOGIN NUMBER
18 ,p_FORECAST_RULE_ID NUMBER
19 ,p_ORGANIZATION_TYPE VARCHAR2
20 ,P_ORGANIZATION_ID NUMBER
21 ,p_SECONDARY_INVENTORY VARCHAR2
22 ,p_SERVICE_LEVEL NUMBER
23 ,p_EDQ_FACTOR NUMBER
24 ,p_PLANNER_CODE VARCHAR2
25 ,p_CONDITION_TYPE VARCHAR2
26 ,p_ASL_FLAG VARCHAR2
27 ,P_SAFETY_STOCK_FLAG VARCHAR2
28 ,P_AUTORECEIPT_FLAG VARCHAR2
29 ,p_EXCESS_RULE_ID NUMBER
30 ,p_NOTIFICATION_RULE_ID NUMBER
31 ,p_RECOMMENDATION_RULE_ID NUMBER
32 ,p_ATTRIBUTE_CATEGORY VARCHAR2
33 ,p_ATTRIBUTE1 VARCHAR2
34 ,p_ATTRIBUTE2 VARCHAR2
35 ,p_ATTRIBUTE3 VARCHAR2
36 ,p_ATTRIBUTE4 VARCHAR2
37 ,p_ATTRIBUTE5 VARCHAR2
38 ,p_ATTRIBUTE6 VARCHAR2
39 ,p_ATTRIBUTE7 VARCHAR2
40 ,p_ATTRIBUTE8 VARCHAR2
41 ,p_ATTRIBUTE9 VARCHAR2
42 ,p_ATTRIBUTE10 VARCHAR2
43 ,p_ATTRIBUTE11 VARCHAR2
44 ,p_ATTRIBUTE12 VARCHAR2
45 ,p_ATTRIBUTE13 VARCHAR2
46 ,p_ATTRIBUTE14 VARCHAR2
47 ,p_ATTRIBUTE15 VARCHAR2
48 ,p_CATEGORY_SET_ID NUMBER
49 ,p_CATEGORY_ID NUMBER
50 ,p_RECOMMEND_METHOD VARCHAR2
51 ,p_EDQ_MULTIPLE NUMBER
52 ,p_MINIMUM_VALUE NUMBER
53 ,p_USABLE_ASSIGNMENT_SET_ID NUMBER
54 ,p_DEFECTIVE_ASSIGNMENT_SET_ID NUMBER
55 ,p_REPAIR_ASSIGNMENT_SET_ID NUMBER
56 ,p_PRODUCT_NORM VARCHAR2
57 ,P_PRODUCT_NORM_NODE_ID NUMBER
58 ,P_USAGE_WEIGHT1 NUMBER
59 ,P_USAGE_WEIGHT2 NUMBER
60 ,P_USAGE_WEIGHT3 NUMBER
61 ,P_USAGE_WEIGHT4 NUMBER
62 ,p_DEFAULT_FORECAST_RULE_ID VARCHAR2
63 ,p_DEFAULT_SERVICE_LEVEL VARCHAR2
64 ,p_DEFAULT_EDQ_FACTOR VARCHAR2
65 ,p_DEFAULT_PLANNER_CODE VARCHAR2
66 ,p_DEFAULT_CONDITION_TYPE VARCHAR2
67 ,p_DEFAULT_ASL_FLAG VARCHAR2
68 ,p_DEFAULT_SAFETY_STOCK_FLAG VARCHAR2
69 ,p_DEFAULT_EXCESS_RULE_ID VARCHAR2
70 ,p_DEFAULT_CATEGORY_SET_ID VARCHAR2
71 ,p_DEFAULT_CATEGORY_ID VARCHAR2
72 ,p_DEFAULT_RECOMMEND_METHOD VARCHAR2
73 ,P_DEFAULT_PRODUCT_NORM_NODE_ID VARCHAR2
74 ,P_DEFAULT_USAGE_WEIGHT1 VARCHAR2
75 ,P_DEFAULT_USAGE_WEIGHT2 VARCHAR2
76 ,P_DEFAULT_USAGE_WEIGHT3 VARCHAR2
77 ,P_DEFAULT_USAGE_WEIGHT4 VARCHAR2
78 ,P_DEFAULT_REC_RULE_ID VARCHAR2
79 ,p_OVERRIDE_FORECAST_RULE_ID VARCHAR2
80 ,p_OVERRIDE_SERVICE_LEVEL VARCHAR2
81 ,p_OVERRIDE_EDQ_FACTOR VARCHAR2
82 ,p_OVERRIDE_PLANNER_CODE VARCHAR2
83 ,p_OVERRIDE_CONDITION_TYPE VARCHAR2
84 ,p_OVERRIDE_ASL_FLAG VARCHAR2
85 ,p_OVERRIDE_SAFETY_STOCK_FLAG VARCHAR2
86 ,p_OVERRIDE_EXCESS_RULE_ID VARCHAR2
87 ,p_OVERRIDE_CATEGORY_SET_ID VARCHAR2
88 ,p_OVERRIDE_CATEGORY_ID VARCHAR2
89 ,p_OVERRIDE_RECOMMEND_METHOD VARCHAR2
90 ,P_OVERRIDE_USAGE_WEIGHT1 VARCHAR2
91 ,P_OVERRIDE_USAGE_WEIGHT2 VARCHAR2
92 ,P_OVERRIDE_USAGE_WEIGHT3 VARCHAR2
93 ,P_OVERRIDE_USAGE_WEIGHT4 VARCHAR2
94 ,P_OVERRIDE_REC_RULE_ID VARCHAR2
95 ,p_NODE_TYPE VARCHAR2
96 ,p_NODE_NAME VARCHAR2
97 ,p_PARENT_NODE_ID NUMBER
98 ,p_LEVEL_ID VARCHAR2
99 ,p_EXCESS_EDQ_FACTOR VARCHAR2
100 ,p_REPAIR_EDQ_FACTOR VARCHAR2
101 ,p_NEWBUY_EDQ_FACTOR VARCHAR2
102 ,p_EXCESS_SERVICE_LEVEL VARCHAR2
103 ,p_REPAIR_SERVICE_LEVEL VARCHAR2
104 ,p_NEWBUY_SERVICE_LEVEL VARCHAR2
105 ,p_RESCHEDULE_RULE_ID NUMBER
106 ,p_WRP_RULE_ID NUMBER
107 ,P_STOCKING_SITE_TYPE VARCHAR2
108 ,P_CALENDAR_ID NUMBER
109 ,P_TIMEZONE_ID NUMBER
110 ,P_MANAGED_BY VARCHAR2
111 ,P_CONTACT_NAME VARCHAR2
112 ,P_CONTACT_PHONE VARCHAR2
113 ,P_AH_CONTACT_NAME VARCHAR2
114 ,P_AH_CONTACT_PHONE VARCHAR2
115 ,P_STOCKING_SITE_EXCL VARCHAR2
116 ,P_SPECIAL_INSTRUCTIONS VARCHAR2
117 ,p_hz_location_id number ) is
118 l_planning_parameters_id number;
119 l_creation_date date;
120 L_LAST_UPDATE_DATE DATE ;
121 l_node_type Varchar2(200);
122 l_node_value Varchar2(200);
123 l_Parent_node_value Varchar2(30);
124 l_viewby_value Varchar2(100);
125 L_ORGANIZATION_ID NUMBER;
126 L_ORGANIZATION_CODE VARCHAR2(200);
127 L_secondary_inventory VARCHAR2(2000);
128 l_sec_inv_id Number;
129 l_SECONDARY_INVENTORY_ID number;
130 L_LEVEL_ID Varchar2(200);
131 L_FLAG Varchar2(30);
132 x_return_status Varchar2(10);
133 x_msg_data Varchar2(2000);
134 x_msg_count Number;
135 L_condition_type Varchar2(200);
136 l_meaning Varchar2(2000);
137
138 CURSOR C_SEC_INV(L_ORGANIZATION_ID NUMBER) IS
139 Select secondary_inventory_name
140 from mtl_secondary_inventories
141 where organization_id = L_ORGANIZATION_ID
142 and nvl(Disable_date,Sysdate+1) > Sysdate
143 and secondary_inventory_name not in
144 (Select secondary_inventory from csp_planning_parameters
145 where organization_id = L_ORGANIZATION_ID)
146 and rownum =1;
147
148 CURSOR C_SEC_INV2(L_ORGANIZATION_ID NUMBER,P_SECONDARY_INVENTORY Varchar2) IS
149 Select secondary_inventory_name
150 from mtl_secondary_inventories
151 where organization_id = L_ORGANIZATION_ID
152 AND SECONDARY_INVENTORY_NAME <> P_SECONDARY_INVENTORY
153 and nvl(Disable_date,Sysdate+1) > Sysdate
154 and secondary_inventory_name not in
155 (Select secondary_inventory from csp_planning_parameters
156 where organization_id = L_ORGANIZATION_ID);
157
158 CURSOR C2 IS SELECT CSP_PLANNING_PARAMETERS_S1.nextval FROM sys.dual;
159
160 CURSOR C3(l_secondary_inventory Varchar2,l_organization_id Number) IS
161 SELECT SECONDARY_INVENTORY_ID
162 FROM CSP_SEC_INVENTORIES_V
163 WHERE organization_id = l_organization_id
164 AND secondary_inventory_name = l_secondary_inventory;
165
166 l_node_icon varchar2(200);
167 l_curr_node_type Varchar2(2000);
168
169 BEGIN
170 /*
171 Csp_Planning_Parameters_pkg.Insert_Row (
172 px_PLANNING_PARAMETERS_ID => l_Planning_Parameters_Id,
173 p_FORECAST_RULE_ID => p_Forecast_Rule_Id,
174 p_ORGANIZATION_ID => p_Organization_Id,
175 p_SECONDARY_INVENTORY => p_secondary_inventory,
176 p_SERVICE_LEVEL => p_service_level,
177 p_EDQ_FACTOR => p_edq_factor,
178 p_PLANNER_CODE => p_planner_code,
179 p_CONDITION_TYPE => p_condition_type,
180 p_ORGANIZATION_TYPE => p_organization_type,
181 p_ASL_FLAG => p_asl_flag,
182 p_safety_stock_flag => p_safety_stock_flag,
183 p_excess_rule_id => to_number(p_Excess_Rule_Id),
184 p_RECOMMENDATION_RULE_ID => p_Recommendation_Rule_Id,
185 P_NOTIFICATION_RULE_ID => P_NOTIFICATION_RULE_ID,
186 p_ATTRIBUTE1 => p_Attribute1,
187 p_ATTRIBUTE2 => p_Attribute2,
188 p_ATTRIBUTE3 => p_Attribute3,
189 p_ATTRIBUTE4 => p_Attribute4,
190 p_ATTRIBUTE5 => p_Attribute5,
191 p_ATTRIBUTE6 => p_Attribute6,
192 p_ATTRIBUTE7 => p_Attribute7,
193 p_ATTRIBUTE8 => p_Attribute8,
194 p_ATTRIBUTE9 => p_Attribute9,
195 p_ATTRIBUTE10 => p_Attribute10,
196 p_ATTRIBUTE11 => p_Attribute11,
197 p_ATTRIBUTE12 => p_Attribute12,
198 p_ATTRIBUTE13 => p_Attribute13,
199 p_ATTRIBUTE14 => p_Attribute14,
200 p_ATTRIBUTE15 => p_Attribute15,
201 P_ATTRIBUTE_CATEGORY => P_ATTRIBUTE_CATEGORY,
202 P_CREATED_BY => P_CREATED_BY,
203 p_CREATION_DATE => p_Creation_Date,
204 p_LAST_UPDATED_BY => p_Last_Updated_By,
205 p_LAST_UPDATE_DATE => p_Last_Update_Date,
206 p_LAST_UPDATE_LOGIN => p_Last_Update_Login
207 ,p_CATEGORY_SET_ID => p_CATEGORY_SET_ID
208 ,p_CATEGORY_ID => p_CATEGORY_ID
209 ,p_RECOMMEND_METHOD => p_RECOMMEND_METHOD
210 ,p_EDQ_MULTIPLE => p_EDQ_MULTIPLE
211 ,p_MINIMUM_VALUE => p_MINIMUM_VALUE
212 ,p_USABLE_ASSIGNMENT_SET_ID => p_USABLE_ASSIGNMENT_SET_ID
213 ,p_DEFECTIVE_ASSIGNMENT_SET_ID =>p_DEFECTIVE_ASSIGNMENT_SET_ID
214 ,p_REPAIR_ASSIGNMENT_SET_ID => p_REPAIR_ASSIGNMENT_SET_ID
215 ,p_PRODUCT_NORM => p_PRODUCT_NORM
216 ,p_PRODUCT_NORM_NODE_ID => p_PRODUCT_NORM_NODE_ID
217 ,p_USAGE_WEIGHT1 => p_USAGE_WEIGHT1
218 ,p_USAGE_WEIGHT2 => p_USAGE_WEIGHT2
219 ,p_USAGE_WEIGHT3 => p_USAGE_WEIGHT3
220 ,p_USAGE_WEIGHT4 => p_USAGE_WEIGHT4
221 ,p_DEFAULT_FORECAST_RULE_ID => p_DEFAULT_FORECAST_RULE_ID
222 ,p_DEFAULT_SERVICE_LEVEL => p_DEFAULT_SERVICE_LEVEL
223 ,p_DEFAULT_EDQ_FACTOR => p_DEFAULT_EDQ_FACTOR
224 ,p_DEFAULT_PLANNER_CODE => p_DEFAULT_PLANNER_CODE
225 ,p_DEFAULT_CONDITION_TYPE => p_DEFAULT_CONDITION_TYPE
226 ,p_DEFAULT_ASL_FLAG => p_DEFAULT_ASL_FLAG
227 ,p_DEFAULT_SAFETY_STOCK_FLAG => p_DEFAULT_SAFETY_STOCK_FLAG
228 ,p_DEFAULT_EXCESS_RULE_ID => p_DEFAULT_EXCESS_RULE_ID
229 ,p_DEFAULT_REC_RULE_ID => p_DEFAULT_REC_RULE_ID
230 ,p_DEFAULT_CATEGORY_SET_ID => p_DEFAULT_CATEGORY_SET_ID
231 ,p_DEFAULT_CATEGORY_ID => p_DEFAULT_CATEGORY_ID
232 ,p_DEFAULT_RECOMMEND_METHOD => p_DEFAULT_RECOMMEND_METHOD
233 ,p_DEFAULT_PRODUCT_NORM_NODE_ID => p_DEFAULT_PRODUCT_NORM_NODE_ID
234 ,p_DEFAULT_USAGE_WEIGHT1 => p_DEFAULT_USAGE_WEIGHT1
235 ,p_DEFAULT_USAGE_WEIGHT2 => p_DEFAULT_USAGE_WEIGHT2
236 ,p_DEFAULT_USAGE_WEIGHT3 => p_DEFAULT_USAGE_WEIGHT3
237 ,p_DEFAULT_USAGE_WEIGHT4 =>p_DEFAULT_USAGE_WEIGHT4
238 ,p_OVERRIDE_FORECAST_RULE_ID => p_OVERRIDE_FORECAST_RULE_ID
239 ,p_OVERRIDE_SERVICE_LEVEL => p_OVERRIDE_SERVICE_LEVEL
240 ,p_OVERRIDE_EDQ_FACTOR => p_OVERRIDE_EDQ_FACTOR
241 ,p_OVERRIDE_PLANNER_CODE => p_OVERRIDE_PLANNER_CODE
242 ,p_OVERRIDE_CONDITION_TYPE => p_OVERRIDE_CONDITION_TYPE
243 ,p_OVERRIDE_ASL_FLAG => p_OVERRIDE_ASL_FLAG
244 ,p_OVERRIDE_SAFETY_STOCK_FLAG => p_OVERRIDE_SAFETY_STOCK_FLAG
245 ,p_OVERRIDE_EXCESS_RULE_ID => p_OVERRIDE_EXCESS_RULE_ID
246 ,p_OVERRIDE_REC_RULE_ID => p_OVERRIDE_REC_RULE_ID
247 ,p_OVERRIDE_CATEGORY_SET_ID => p_OVERRIDE_CATEGORY_SET_ID
248 ,p_OVERRIDE_CATEGORY_ID => p_OVERRIDE_CATEGORY_ID
249 ,p_OVERRIDE_RECOMMEND_METHOD => p_OVERRIDE_RECOMMEND_METHOD
250 ,p_OVERRIDE_USAGE_WEIGHT1 => p_OVERRIDE_USAGE_WEIGHT1
251 ,p_OVERRIDE_USAGE_WEIGHT2 => p_OVERRIDE_USAGE_WEIGHT2
252 ,p_OVERRIDE_USAGE_WEIGHT3 => p_OVERRIDE_USAGE_WEIGHT3
253 ,p_OVERRIDE_USAGE_WEIGHT4 => p_OVERRIDE_USAGE_WEIGHT4
254 ,p_NODE_TYPE => p_Node_Type,
255 p_NODE_NAME => p_NODE_NAME,
256 p_PARENT_NODE_ID => p_PARENT_NODE_ID,
257 p_LEVEL_ID => p_LEVEL_ID,
258 p_EXCESS_SERVICE_LEVEL => p_excess_service_level,
259 p_REPAIR_SERVICE_LEVEL =>p_repair_service_level,
260 p_NEWBUY_SERVICE_LEVEL => p_newbuy_service_level,
261 p_EXCESS_EDQ_FACTOR => p_excess_edq_factor,
262 p_REPAIR_EDQ_FACTOR => p_repair_edq_factor,
263 p_NEWBUY_EDQ_FACTOR => p_newbuy_edq_factor,
264 p_RESCHEDULE_RULE_ID => p_reschedule_rule_id,
265 p_WRP_RULE_ID => p_wrp_rule_id,
266 p_stocking_site_type => p_stocking_site_type,
267 p_CALENDAR_ID => p_CALENDAR_ID,
268 p_TIMEZONE_ID => p_TIMEZONE_ID,
269 p_MANAGED_BY => p_MANAGED_BY,
270 p_CONTACT_NAME => p_CONTACT_NAME,
271 p_CONTACT_PHONE => p_CONTACT_PHONE,
272 p_AH_CONTACT_NAME => p_AH_CONTACT_NAME,
273 p_AH_CONTACT_PHONE => p_AH_CONTACT_PHONE,
274 p_STOCKING_SITE_EXCL => p_STOCKING_SITE_EXCL,
275 p_SPECIAL_INSTRUCTIONS => p_SPECIAL_INSTRUCTIONS,
276 p_HZ_LOCATION_ID => p_HZ_LOCATION_ID);
277 */
278
279 l_level_id := p_level_id;
280 l_organization_id := p_organization_id;
281 l_node_type := p_node_type;
282 L_SECONDARY_INVENTORY := P_SECONDARY_INVENTORY;
283 l_condition_type := p_condition_type;
284
285 If l_node_type = 'SUBINVENTORY' Then
286 Begin
287 UPDATE CSP_USAGE_HEADERS
288 set planning_parameters_id = l_planning_parameters_id
289 WHERE HEADER_DATA_TYPE = 1
290 AND SECONDARY_INVENTORY = L_SECONDARY_INVENTORY
291 And Organization_Id = l_Organization_Id;
292 Exception
293 When no_data_found then
294 Null;
295 END;
296
297 open c3(l_secondary_inventory,l_organization_id);
298 FETCH C3 INTO l_sec_inv_id;
299 CLOSE C3;
300
301 If l_Sec_Inv_Id is NULL Then
302 CSP_SEC_INVENTORIES_PKG.Insert_Row(
303 L_SECONDARY_INVENTORY_ID
304 ,p_created_by
305 ,p_CREATION_DATE
306 ,P_LAST_UPDATED_BY
307 ,p_LAST_UPDATE_DATE
308 ,P_LAST_UPDATE_LOGIN
309 ,p_ORGANIZATION_ID
310 ,NULL
311 ,NULL
312 ,L_SECONDARY_INVENTORY
313 ,NULL
314 ,P_CONDITION_TYPE
315 ,NVL(p_AUTORECEIPT_FLAG,'Y')
316 ,'Y'
317 ,NULL
318 ,null
319 ,NULL
320 ,NULL
321 ,NULL
322 ,NULL
323 ,NULL
324 ,NULL
325 ,NULL
326 ,NULL
327 ,NULL
328 ,NULL
329 ,NULL
330 ,NULL
331 ,NULL
332 ,NULL
333 ,NULL
334 ,NULL
335 ,NULL
336 ,NULL
337 ,NULL);
338 ELSE
339 UPDATE CSP_SEC_INVENTORIES SET CONDITION_TYPE = P_CONDITION_TYPE,
340 AUTORECEIPT_FLAG = NVL(p_AUTORECEIPT_FLAG,'Y')
341 Where SECONDARY_INVENTORY_NAME = L_SECONDARY_INVENTORY
342 And ORGANIZATION_ID = l_organization_id
343 AND SECONDARY_INVENTORY_ID = l_sec_inv_id;
344 End if;
345 ElsIf l_node_type = 'ORGANIZATION_WH' Then
346 Begin
347 UPDATE CSP_USAGE_HEADERS
348 set planning_parameters_id = l_planning_parameters_id
349 WHERE HEADER_DATA_TYPE IN (1,4)
350 And Organization_Id = l_Organization_Id
351 and Secondary_Inventory = '-';
352 Exception
353 When no_data_found then
354 Null;
355 end;
356 End If;
357
358 /*
359 If l_node_type in (planner.pln_org_node,planner.pln_subinv_node) then
360 CSP_SUPERSESSIONS_PVT.PROCESS_SUPERSESSIONS(l_level_id
361 ,CSP_PICK_UTILS.GET_TRUE
362 ,x_return_status
363 ,x_msg_data
364 ,x_msg_count);
365 End if;
366 */
367
368 end create_planning_parameters;
369
370 procedure update_planning_parameters(
371 p_PLANNING_PARAMETERS_ID NUMBER
372 ,p_CREATED_BY NUMBER
373 ,p_CREATION_DATE DATE
374 ,p_LAST_UPDATED_BY NUMBER
375 ,p_LAST_UPDATE_DATE DATE
376 ,p_LAST_UPDATE_LOGIN NUMBER
377 ,p_FORECAST_RULE_ID NUMBER
378 ,p_ORGANIZATION_TYPE VARCHAR2
379 ,p_ORGANIZATION_ID NUMBER
380 ,p_SECONDARY_INVENTORY VARCHAR2
381 ,p_SERVICE_LEVEL NUMBER
382 ,p_EDQ_FACTOR NUMBER
383 ,p_PLANNER_CODE VARCHAR2
384 ,p_CONDITION_TYPE VARCHAR2
385 ,p_ASL_FLAG VARCHAR2
386 ,P_SAFETY_STOCK_FLAG VARCHAR2
387 ,p_EXCESS_RULE_ID NUMBER
388 ,p_NOTIFICATION_RULE_ID NUMBER
389 ,p_RECOMMENDATION_RULE_ID NUMBER
390 ,p_ATTRIBUTE_CATEGORY VARCHAR2
391 ,p_ATTRIBUTE1 VARCHAR2
392 ,p_ATTRIBUTE2 VARCHAR2
393 ,p_ATTRIBUTE3 VARCHAR2
394 ,p_ATTRIBUTE4 VARCHAR2
395 ,p_ATTRIBUTE5 VARCHAR2
396 ,p_ATTRIBUTE6 VARCHAR2
397 ,p_ATTRIBUTE7 VARCHAR2
398 ,p_ATTRIBUTE8 VARCHAR2
399 ,p_ATTRIBUTE9 VARCHAR2
400 ,p_ATTRIBUTE10 VARCHAR2
401 ,p_ATTRIBUTE11 VARCHAR2
402 ,p_ATTRIBUTE12 VARCHAR2
403 ,p_ATTRIBUTE13 VARCHAR2
404 ,p_ATTRIBUTE14 VARCHAR2
405 ,p_ATTRIBUTE15 VARCHAR2
406 ,p_CATEGORY_SET_ID NUMBER
407 ,p_CATEGORY_ID NUMBER
408 ,p_RECOMMEND_METHOD VARCHAR2
409 ,p_EDQ_MULTIPLE NUMBER
410 ,p_MINIMUM_VALUE NUMBER
411 ,p_USABLE_ASSIGNMENT_SET_ID NUMBER
412 ,p_DEFECTIVE_ASSIGNMENT_SET_ID NUMBER
413 ,p_REPAIR_ASSIGNMENT_SET_ID NUMBER
414 ,p_PRODUCT_NORM VARCHAR2
415 ,P_PRODUCT_NORM_NODE_ID NUMBER
416 ,P_USAGE_WEIGHT1 NUMBER
417 ,P_USAGE_WEIGHT2 NUMBER
418 ,P_USAGE_WEIGHT3 NUMBER
419 ,P_USAGE_WEIGHT4 NUMBER
420 ,p_DEFAULT_FORECAST_RULE_ID VARCHAR2
421 ,p_DEFAULT_SERVICE_LEVEL VARCHAR2
422 ,p_DEFAULT_EDQ_FACTOR VARCHAR2
423 ,p_DEFAULT_PLANNER_CODE VARCHAR2
424 ,p_DEFAULT_CONDITION_TYPE VARCHAR2
425 ,p_DEFAULT_ASL_FLAG VARCHAR2
426 ,p_DEFAULT_SAFETY_STOCK_FLAG VARCHAR2
427 ,p_DEFAULT_EXCESS_RULE_ID VARCHAR2
428 ,p_DEFAULT_CATEGORY_SET_ID VARCHAR2
429 ,p_DEFAULT_CATEGORY_ID VARCHAR2
430 ,p_DEFAULT_RECOMMEND_METHOD VARCHAR2
431 ,P_DEFAULT_PRODUCT_NORM_NODE_ID VARCHAR2
432 ,P_DEFAULT_USAGE_WEIGHT1 VARCHAR2
433 ,P_DEFAULT_USAGE_WEIGHT2 VARCHAR2
434 ,P_DEFAULT_USAGE_WEIGHT3 VARCHAR2
435 ,P_DEFAULT_USAGE_WEIGHT4 VARCHAR2
436 ,P_DEFAULT_REC_RULE_ID VARCHAR2
437 ,p_OVERRIDE_FORECAST_RULE_ID VARCHAR2
438 ,p_OVERRIDE_SERVICE_LEVEL VARCHAR2
439 ,p_OVERRIDE_EDQ_FACTOR VARCHAR2
440 ,p_OVERRIDE_PLANNER_CODE VARCHAR2
441 ,p_OVERRIDE_CONDITION_TYPE VARCHAR2
442 ,p_OVERRIDE_ASL_FLAG VARCHAR2
443 ,p_OVERRIDE_SAFETY_STOCK_FLAG VARCHAR2
444 ,p_OVERRIDE_EXCESS_RULE_ID VARCHAR2
445 ,p_OVERRIDE_CATEGORY_SET_ID VARCHAR2
446 ,p_OVERRIDE_CATEGORY_ID VARCHAR2
447 ,p_OVERRIDE_RECOMMEND_METHOD VARCHAR2
448 ,P_OVERRIDE_USAGE_WEIGHT1 VARCHAR2
449 ,P_OVERRIDE_USAGE_WEIGHT2 VARCHAR2
450 ,P_OVERRIDE_USAGE_WEIGHT3 VARCHAR2
451 ,P_OVERRIDE_USAGE_WEIGHT4 VARCHAR2
452 ,P_OVERRIDE_REC_RULE_ID VARCHAR2
453 ,p_NODE_TYPE VARCHAR2
454 ,p_NODE_NAME VARCHAR2
455 ,p_PARENT_NODE_ID NUMBER
456 ,p_LEVEL_ID VARCHAR2
457 ,p_EXCESS_EDQ_FACTOR VARCHAR2
458 ,p_REPAIR_EDQ_FACTOR VARCHAR2
459 ,p_NEWBUY_EDQ_FACTOR VARCHAR2
460 ,p_EXCESS_SERVICE_LEVEL VARCHAR2
461 ,p_REPAIR_SERVICE_LEVEL VARCHAR2
462 ,p_NEWBUY_SERVICE_LEVEL VARCHAR2
463 ,p_RESCHEDULE_RULE_ID NUMBER
464 ,p_WRP_RULE_ID NUMBER
465 ,P_STOCKING_SITE_TYPE VARCHAR2
466 ,P_CALENDAR_ID NUMBER
467 ,P_TIMEZONE_ID NUMBER
468 ,P_MANAGED_BY VARCHAR2
469 ,P_CONTACT_NAME VARCHAR2
470 ,P_CONTACT_PHONE VARCHAR2
471 ,P_AH_CONTACT_NAME VARCHAR2
472 ,P_AH_CONTACT_PHONE VARCHAR2
473 ,P_STOCKING_SITE_EXCL VARCHAR2
474 ,P_SPECIAL_INSTRUCTIONS VARCHAR2
475 ,p_hz_location_id number) is
476 L_CREATED_BY NUMBER;
477 l_CREATION_DATE DATE;
478 L_LAST_UPDATED_BY NUMBER;
479 L_LAST_UPDATE_DATE DATE;
480 L_LAST_UPDATE_LOGIN NUMBER;
481 L_FORECAST_RULE_ID NUMBER;
482 L_ORGANIZATION_TYPE VARCHAR2(30);
483 L_ORGANIZATION_ID NUMBER;
484 L_SECONDARY_INVENTORY VARCHAR2(10);
485 L_SERVICE_LEVEL NUMBER;
486 L_EDQ_FACTOR NUMBER;
487 L_PLANNER_CODE VARCHAR2(10);
488 L_CONDITION_TYPE VARCHAR2(30);
489 L_ASL_FLAG VARCHAR2(1);
490 L_SAFETY_STOCK_FLAG VARCHAR2(1);
491 L_autoreceipt_FLAG VARCHAR2(1);
492 L_EXCESS_RULE_ID NUMBER;
493 l_NOTIFICATION_RULE_ID NUMBER;
494 L_RECOMMENDATION_RULE_ID NUMBER;
495 L_ATTRIBUTE_CATEGORY VARCHAR2(30);
496 L_ATTRIBUTE1 VARCHAR2(150);
497 L_ATTRIBUTE2 VARCHAR2(150);
498 L_ATTRIBUTE3 VARCHAR2(150);
499 L_ATTRIBUTE4 VARCHAR2(150);
500 l_ATTRIBUTE5 VARCHAR2(150);
501 L_ATTRIBUTE6 VARCHAR2(150);
502 L_ATTRIBUTE7 VARCHAR2(150);
503 L_ATTRIBUTE8 VARCHAR2(150);
504 L_ATTRIBUTE9 VARCHAR2(150);
505 L_ATTRIBUTE10 VARCHAR2(150);
506 L_ATTRIBUTE11 VARCHAR2(150);
507 L_ATTRIBUTE12 VARCHAR2(150);
508 L_ATTRIBUTE13 VARCHAR2(150);
509 L_ATTRIBUTE14 VARCHAR2(150);
510 L_ATTRIBUTE15 VARCHAR2(150);
511 L_CATEGORY_SET_ID NUMBER;
512 L_CATEGORY_ID NUMBER;
513 L_RECOMMEND_METHOD VARCHAR2(200);
514 L_EDQ_MULTIPLE NUMBER;
515 l_MINIMUM_VALUE NUMBER;
516 L_USABLE_ASSIGNMENT_SET_ID NUMBER;
517 L_DEFECTIVE_ASSIGNMENT_SET_ID NUMBER;
518 L_REPAIR_ASSIGNMENT_SET_ID NUMBER;
519 L_PRODUCT_NORM VARCHAR2(200);
520 L_PRODUCT_NORM_NODE_ID NUMBER;
521 l_USAGE_WEIGHT1 NUMBER;
522 L_USAGE_WEIGHT2 NUMBER;
523 l_USAGE_WEIGHT3 NUMBER;
524 L_USAGE_WEIGHT4 NUMBER;
525 L_DEFAULT_FORECAST_RULE_ID VARCHAR2(200);
526 L_DEFAULT_SERVICE_LEVEL VARCHAR2(200);
527 L_DEFAULT_EDQ_FACTOR VARCHAR2(200);
528 L_DEFAULT_PLANNER_CODE VARCHAR2(200);
529 L_DEFAULT_CONDITION_TYPE VARCHAR2(200);
530 L_DEFAULT_ASL_FLAG VARCHAR2(200);
531 L_DEFAULT_SAFETY_STOCK_FLAG VARCHAR2(200);
532 L_DEFAULT_EXCESS_RULE_ID VARCHAR2(200);
533 L_DEFAULT_CATEGORY_SET_ID VARCHAR2(200);
534 L_DEFAULT_CATEGORY_ID VARCHAR2(200);
535 L_DEFAULT_RECOMMEND_METHOD VARCHAR2(200);
536 L_DEFAULT_PRODUCT_NORM_NODE_ID VARCHAR2(200);
537 L_DEFAULT_USAGE_WEIGHT1 VARCHAR2(200);
538 L_DEFAULT_USAGE_WEIGHT2 VARCHAR2(200);
539 L_DEFAULT_USAGE_WEIGHT3 VARCHAR2(200);
540 L_DEFAULT_USAGE_WEIGHT4 VARCHAR2(200);
541 L_DEFAULT_REC_RULE_ID VARCHAR2(200);
542 L_OVERRIDE_FORECAST_RULE_ID VARCHAR2(200);
543 L_OVERRIDE_SERVICE_LEVEL VARCHAR2(200);
544 L_OVERRIDE_EDQ_FACTOR VARCHAR2(200);
545 L_OVERRIDE_PLANNER_CODE VARCHAR2(200);
546 L_OVERRIDE_CONDITION_TYPE VARCHAR2(200);
547 L_OVERRIDE_ASL_FLAG VARCHAR2(200);
548 L_OVERRIDE_SAFETY_STOCK_FLAG VARCHAR2(200);
549 L_OVERRIDE_EXCESS_RULE_ID VARCHAR2(200);
550 L_OVERRIDE_CATEGORY_SET_ID VARCHAR2(200);
551 L_OVERRIDE_CATEGORY_ID VARCHAR2(200);
552 L_OVERRIDE_RECOMMEND_METHOD VARCHAR2(200);
553 L_OVERRIDE_USAGE_WEIGHT1 VARCHAR2(200);
554 L_OVERRIDE_USAGE_WEIGHT2 VARCHAR2(200);
555 L_OVERRIDE_USAGE_WEIGHT3 VARCHAR2(200);
556 L_OVERRIDE_USAGE_WEIGHT4 VARCHAR2(200);
557 L_OVERRIDE_REC_RULE_ID VARCHAR2(200);
558 L_NODE_TYPE VARCHAR2(200);
559 L_NODE_NAME VARCHAR2(200);
560 L_PARENT_NODE_ID NUMBER;
561 L_LEVEL_ID VARCHAR2(200);
562 L_EXCESS_EDQ_FACTOR VARCHAR2(200);
563 L_REPAIR_EDQ_FACTOR VARCHAR2(200);
564 L_NEWBUY_EDQ_FACTOR VARCHAR2(200);
565 L_EXCESS_SERVICE_LEVEL VARCHAR2(200);
566 l_REPAIR_SERVICE_LEVEL VARCHAR2(200);
567 l_NEWBUY_SERVICE_LEVEL VARCHAR2(200);
568 L_RESCHEDULE_RULE_ID NUMBER;
569 L_WRP_RULE_ID NUMBER;
570 L_STOCKING_SITE_TYPE VARCHAR2(200);
571 L_CALENDAR_ID NUMBER;
572 L_TIMEZONE_ID NUMBER;
573 L_MANAGED_BY VARCHAR2(200);
574 L_CONTACT_NAME VARCHAR2(200);
575 L_CONTACT_PHONE VARCHAR2(200);
576 L_AH_CONTACT_NAME VARCHAR2(200);
577 l_AH_CONTACT_PHONE VARCHAR2(200);
578 L_STOCKING_SITE_EXCL VARCHAR2(200);
579 L_SPECIAL_INSTRUCTIONS VARCHAR2(200);
580 L_HZ_LOCATION_ID NUMBER;
581 l_parent_node_type VARCHAR2(200);
582 l_parent_node_value VARCHAR2(200);
583 L_COUNT NUMBER;
584 L_PLANNING_PARAMETERS_ID NUMBER;
585
586 Begin
587 l_planning_parameters_id := p_planning_parameters_id;
588 L_NODE_TYPE := P_NODE_TYPE;
589
590 Begin
591 Select product_norm
592 ,FORECAST_RULE_ID
593 ,EXCESS_RULE_ID
594 ,RECOMMENDATION_RULE_ID
595 ,SERVICE_LEVEL
596 ,EDQ_FACTOR
597 ,ASL_FLAG
598 ,SAFETY_STOCK_FLAG
599 ,CONDITION_TYPE
600 ,PLANNER_CODE
601 ,CATEGORY_SET_ID
602 ,CATEGORY_ID
603 ,RECOMMEND_METHOD
604 ,PRODUCT_NORM_NODE_ID
605 ,USAGE_WEIGHT1
606 ,USAGE_WEIGHT2
607 ,USAGE_WEIGHT3
608 ,USAGE_WEIGHT4
609 ,default_FORECAST_RULE_ID
610 ,default_EXCESS_RULE_ID
611 ,default_REC_RULE_ID
612 ,default_SERVICE_LEVEL
613 ,default_EDQ_FACTOR
614 ,default_ASL_FLAG
615 ,default_SAFETY_STOCK_FLAG
616 ,default_CONDITION_TYPE
617 ,default_PLANNER_CODE
618 ,default_CATEGORY_SET_ID
619 ,default_CATEGORY_ID
620 ,default_RECOMMEND_METHOD
621 ,DEFAULT_PRODUCT_NORM_NODE_ID
622 ,DEFAULT_USAGE_WEIGHT1
623 ,DEFAULT_USAGE_WEIGHT2
624 ,DEFAULT_USAGE_WEIGHT3
625 ,DEFAULT_USAGE_WEIGHT4
626 ,override_FORECAST_RULE_ID
627 ,override_EXCESS_RULE_ID
628 ,override_REC_RULE_ID
629 ,override_SERVICE_LEVEL
630 ,override_EDQ_FACTOR
631 ,override_ASL_FLAG
632 ,override_SAFETY_STOCK_FLAG
633 ,override_CONDITION_TYPE
634 ,override_PLANNER_CODE
635 ,override_CATEGORY_SET_ID
636 ,override_CATEGORY_ID
637 ,override_RECOMMEND_METHOD
638 ,OVERRIDE_USAGE_WEIGHT1
639 ,OVERRIDE_USAGE_WEIGHT2
640 ,OVERRIDE_USAGE_WEIGHT3
641 ,OVERRIDE_USAGE_WEIGHT4
642 ,NODE_NAME
643 into
644 L_PRODUCT_NORM
645 ,L_FORECAST_RULE_ID
646 ,l_EXCESS_RULE_ID
647 ,l_RECOMMENDATION_RULE_ID
648 ,l_SERVICE_LEVEL
649 ,l_EDQ_FACTOR
650 ,l_ASL_FLAG
651 ,L_SAFETY_STOCK_FLAG
652 ,l_CONDITION_TYPE
653 ,l_PLANNER_CODE
654 ,l_CATEGORY_SET_ID
655 ,l_CATEGORY_ID
656 ,l_RECOMMEND_METHOD
657 ,l_PRODUCT_NORM_NODE_ID
658 ,l_USAGE_WEIGHT1
659 ,l_USAGE_WEIGHT2
660 ,l_USAGE_WEIGHT3
661 ,l_USAGE_WEIGHT4
662 ,l_default_FORECAST_RULE_ID
663 ,l_default_EXCESS_RULE_ID
664 ,l_default_REC_RULE_ID
665 ,l_default_SERVICE_LEVEL
666 ,l_default_EDQ_FACTOR
667 ,l_default_ASL_FLAG
668 ,l_default_SAFETY_STOCK_FLAG
669 ,l_default_CONDITION_TYPE
670 ,l_default_PLANNER_CODE
671 ,l_default_CATEGORY_SET_ID
672 ,l_default_CATEGORY_ID
673 ,l_default_RECOMMEND_METHOD
674 ,l_DEFAULT_PRODUCT_NORM_NODE_ID
675 ,l_DEFAULT_USAGE_WEIGHT1
676 ,l_DEFAULT_USAGE_WEIGHT2
677 ,l_DEFAULT_USAGE_WEIGHT3
678 ,l_DEFAULT_USAGE_WEIGHT4
679 ,L_OVERRIDE_FORECAST_RULE_ID
680 ,l_override_EXCESS_RULE_ID
681 ,l_override_REC_RULE_ID
682 ,l_override_SERVICE_LEVEL
683 ,l_override_EDQ_FACTOR
684 ,l_override_ASL_FLAG
685 ,l_override_SAFETY_STOCK_FLAG
686 ,l_override_CONDITION_TYPE
687 ,l_override_PLANNER_CODE
688 ,l_override_CATEGORY_SET_ID
689 ,l_override_CATEGORY_ID
690 ,l_override_RECOMMEND_METHOD
691 ,l_OVERRIDE_USAGE_WEIGHT1
692 ,l_OVERRIDE_USAGE_WEIGHT2
693 ,l_OVERRIDE_USAGE_WEIGHT3
694 ,L_OVERRIDE_USAGE_WEIGHT4
695 ,l_node_name
696 From CSP_PLANNING_PARAMETERS
697 WHERE planning_parameters_id = l_planning_parameters_id;
698 Exception
699 When no_data_found then
700 Null;
701 END;
702
703 /*
704 Csp_Planning_Parameters_pkg.Update_Row (
705 p_PLANNING_PARAMETERS_ID => p_Planning_Parameters_Id,
706 p_FORECAST_RULE_ID => p_Forecast_Rule_Id,
707 p_ORGANIZATION_ID => p_Organization_Id,
708 p_SECONDARY_INVENTORY => p_secondary_inventory,
709 p_SERVICE_LEVEL => p_service_level,
710 p_EDQ_FACTOR => p_edq_factor,
711 p_PLANNER_CODE => p_planner_code,
712 p_CONDITION_TYPE => p_condition_type,
713 p_ORGANIZATION_TYPE => p_organization_type,
714 p_ASL_FLAG => p_asl_flag,
715 p_SAFETY_STOCK_FLAG => p_safety_stock_flag,
716 p_EXCESS_RULE_ID => p_Excess_Rule_Id,
717 p_recommendation_rule_id => p_recommendation_rule_id,
718 p_NOTIFICATION_RULE_ID => p_NOTIFICATION_RULE_ID,
719 p_ATTRIBUTE1 => p_Attribute1,
720 p_ATTRIBUTE2 => p_Attribute2,
721 p_ATTRIBUTE3 => p_Attribute3,
722 p_ATTRIBUTE4 => p_Attribute4,
723 p_ATTRIBUTE5 => p_Attribute5,
724 p_ATTRIBUTE6 => p_Attribute6,
725 p_ATTRIBUTE7 => p_Attribute7,
726 p_ATTRIBUTE8 => p_Attribute8,
727 p_ATTRIBUTE9 => p_Attribute9,
728 p_ATTRIBUTE10 => p_Attribute10,
729 p_ATTRIBUTE11 => p_Attribute11,
730 p_ATTRIBUTE12 => p_Attribute12,
731 p_ATTRIBUTE13 => p_Attribute13,
732 p_ATTRIBUTE14 => p_Attribute14,
733 p_ATTRIBUTE15 => p_Attribute15,
734 p_ATTRIBUTE_CATEGORY => p_Attribute_category,
735 p_CREATED_BY => p_Created_by,
736 p_CREATION_DATE => p_Creation_Date,
737 P_LAST_UPDATED_BY => P_LAST_UPDATED_BY,
738 p_LAST_UPDATE_DATE => p_Last_Update_Date,
739 p_LAST_UPDATE_LOGIN => p_Last_Update_Login
740 ,p_CATEGORY_SET_ID => p_CATEGORY_SET_ID
741 ,p_CATEGORY_ID => p_CATEGORY_ID
742 ,p_RECOMMEND_METHOD => p_RECOMMEND_METHOD
743 ,p_EDQ_MULTIPLE => p_EDQ_MULTIPLE
744 ,p_MINIMUM_VALUE => p_MINIMUM_VALUE
745 ,p_USABLE_ASSIGNMENT_SET_ID => p_USABLE_ASSIGNMENT_SET_ID
746 ,p_DEFECTIVE_ASSIGNMENT_SET_ID =>p_DEFECTIVE_ASSIGNMENT_SET_ID
747 ,p_REPAIR_ASSIGNMENT_SET_ID => p_REPAIR_ASSIGNMENT_SET_ID
748 ,p_PRODUCT_NORM => p_PRODUCT_NORM
749 ,p_PRODUCT_NORM_NODE_ID => p_PRODUCT_NORM_NODE_ID
750 ,p_USAGE_WEIGHT1 => p_USAGE_WEIGHT1
751 ,P_USAGE_WEIGHT2 => P_USAGE_WEIGHT2
752 ,p_USAGE_WEIGHT3 => p_USAGE_WEIGHT3
753 ,p_USAGE_WEIGHT4 => p_USAGE_WEIGHT4
754 ,p_DEFAULT_FORECAST_RULE_ID => p_DEFAULT_FORECAST_RULE_ID
755 ,p_DEFAULT_SERVICE_LEVEL => p_DEFAULT_SERVICE_LEVEL
756 ,p_DEFAULT_EDQ_FACTOR => p_DEFAULT_EDQ_FACTOR
757 ,p_DEFAULT_PLANNER_CODE => p_DEFAULT_PLANNER_CODE
758 ,p_DEFAULT_CONDITION_TYPE => p_DEFAULT_CONDITION_TYPE
759 ,p_DEFAULT_ASL_FLAG => p_DEFAULT_ASL_FLAG
760 ,p_DEFAULT_SAFETY_STOCK_FLAG => p_DEFAULT_SAFETY_STOCK_FLAG
761 ,p_DEFAULT_EXCESS_RULE_ID => p_DEFAULT_EXCESS_RULE_ID
762 ,p_DEFAULT_REC_RULE_ID => p_DEFAULT_REC_RULE_ID
763 ,p_DEFAULT_CATEGORY_SET_ID => p_DEFAULT_CATEGORY_SET_ID
764 ,p_DEFAULT_CATEGORY_ID => p_DEFAULT_CATEGORY_ID
765 ,p_DEFAULT_RECOMMEND_METHOD => p_DEFAULT_RECOMMEND_METHOD
766 ,p_DEFAULT_PRODUCT_NORM_NODE_ID => p_DEFAULT_PRODUCT_NORM_NODE_ID
767 ,p_DEFAULT_USAGE_WEIGHT1 => p_DEFAULT_USAGE_WEIGHT1
768 ,p_DEFAULT_USAGE_WEIGHT2 => p_DEFAULT_USAGE_WEIGHT2
769 ,p_DEFAULT_USAGE_WEIGHT3 => p_DEFAULT_USAGE_WEIGHT3
770 ,p_DEFAULT_USAGE_WEIGHT4 => p_DEFAULT_USAGE_WEIGHT4
771 ,p_OVERRIDE_FORECAST_RULE_ID => p_OVERRIDE_FORECAST_RULE_ID
772 ,p_OVERRIDE_SERVICE_LEVEL => p_OVERRIDE_SERVICE_LEVEL
773 ,p_OVERRIDE_EDQ_FACTOR => p_OVERRIDE_EDQ_FACTOR
774 ,p_OVERRIDE_PLANNER_CODE => p_OVERRIDE_PLANNER_CODE
775 ,p_OVERRIDE_CONDITION_TYPE => p_OVERRIDE_CONDITION_TYPE
776 ,p_OVERRIDE_ASL_FLAG => p_OVERRIDE_ASL_FLAG
777 ,p_OVERRIDE_SAFETY_STOCK_FLAG => p_OVERRIDE_SAFETY_STOCK_FLAG
778 ,p_OVERRIDE_EXCESS_RULE_ID => p_OVERRIDE_EXCESS_RULE_ID
779 ,p_OVERRIDE_REC_RULE_ID => p_OVERRIDE_REC_RULE_ID
780 ,p_OVERRIDE_CATEGORY_SET_ID => p_OVERRIDE_CATEGORY_SET_ID
781 ,p_OVERRIDE_CATEGORY_ID => p_OVERRIDE_CATEGORY_ID
782 ,p_OVERRIDE_RECOMMEND_METHOD => p_OVERRIDE_RECOMMEND_METHOD
783 ,p_OVERRIDE_USAGE_WEIGHT1 => p_OVERRIDE_USAGE_WEIGHT1
784 ,p_OVERRIDE_USAGE_WEIGHT2 => p_OVERRIDE_USAGE_WEIGHT2
785 ,p_OVERRIDE_USAGE_WEIGHT3 => p_OVERRIDE_USAGE_WEIGHT3
786 ,p_OVERRIDE_USAGE_WEIGHT4 => p_OVERRIDE_USAGE_WEIGHT4
787 ,p_NODE_TYPE => p_Node_Type,
788 p_NODE_NAME => p_NODE_NAME,
789 p_PARENT_NODE_ID => p_PARENT_NODE_ID,
790 p_LEVEL_ID => p_LEVEL_ID,
791 p_EXCESS_SERVICE_LEVEL => p_excess_service_level,
792 p_REPAIR_SERVICE_LEVEL =>p_repair_service_level,
793 p_NEWBUY_SERVICE_LEVEL => p_newbuy_service_level,
794 p_EXCESS_EDQ_FACTOR => p_excess_edq_factor,
795 p_REPAIR_EDQ_FACTOR => p_repair_edq_factor,
796 p_NEWBUY_EDQ_FACTOR => p_newbuy_edq_factor,
797 p_RESCHEDULE_RULE_ID => p_reschedule_rule_id,
798 p_WRP_RULE_ID => p_wrp_rule_id,
799 p_stocking_site_type => p_stocking_site_type,
800 p_CALENDAR_ID => p_CALENDAR_ID,
801 p_TIMEZONE_ID => p_TIMEZONE_ID,
802 p_MANAGED_BY => p_MANAGED_BY,
803 p_CONTACT_NAME => p_CONTACT_NAME,
804 p_CONTACT_PHONE => p_CONTACT_PHONE,
805 p_AH_CONTACT_NAME => p_AH_CONTACT_NAME,
806 p_AH_CONTACT_PHONE => p_AH_CONTACT_PHONE,
807 p_STOCKING_SITE_EXCL => p_STOCKING_SITE_EXCL,
808 p_SPECIAL_INSTRUCTIONS => p_SPECIAL_INSTRUCTIONS,
809 P_HZ_LOCATION_ID => P_HZ_LOCATION_ID );
810 */
811
812
813 IF (NVL(L_FORECAST_RULE_ID,0) <> NVL(P_FORECAST_RULE_ID,0) OR
814 NVL(L_EXCESS_RULE_ID,0) <> NVL(P_EXCESS_RULE_ID,0) OR
815 NVL(L_RECOMMENDATION_RULE_ID,0) <> NVL(P_RECOMMENDATION_RULE_ID,0) OR
816 NVL(L_SERVICE_LEVEL,0) <> NVL(P_SERVICE_LEVEL,0) OR
817 NVL(l_Edq_Factor,0) <> nvl(p_EDQ_FACTOR,0) OR
818 l_asl_flag <> p_ASL_FLAG OR
819 l_safety_stock_flag <> p_SAFETY_STOCK_FLAG OR
820 l_condition_type <> p_CONDITION_TYPE OR
821 l_planner_code <> p_PLANNER_CODE OR
822 NVL(l_CATEGORY_SET_ID,0) <> NVL(p_CATEGORY_SET_ID,0) OR
823 NVL(l_CATEGORY_ID,0) <> NVL(p_CATEGORY_ID,0) OR
824 l_RECOMMEND_METHOD <> p_RECOMMEND_METHOD OR
825 NVL(l_product_norm_node_id,0) <> nvl(p_PRODUCT_NORM_NODE_ID,0) OR
826 NVL(l_USAGE_WEIGHT1,0) <> nvl(p_USAGE_WEIGHT1,0) OR
827 NVL(l_USAGE_WEIGHT2,0) <> nvl(p_USAGE_WEIGHT2,0) OR
828 NVL(l_USAGE_WEIGHT3,0) <> nvl(p_USAGE_WEIGHT3,0) OR
829 NVL(l_USAGE_WEIGHT4,0) <> NVL(P_USAGE_WEIGHT4,0) OR
830 l_default_PRODUCT_NORM_NODE_ID <> P_default_PRODUCT_NORM_NODE_ID OR
831 l_default_FORECAST_RULE_ID <> P_default_FORECAST_RULE_ID OR
832 l_default_EXCESS_RULE_ID <> P_default_EXCESS_RULE_ID OR
833 l_default_REC_RULE_ID <> P_default_REC_RULE_ID OR
834 l_default_SERVICE_LEVEL <> P_default_SERVICE_LEVEL OR
835 l_default_EDQ_FACTOR <> P_default_EDQ_FACTOR OR
836 l_default_ASL_FLAG <> P_default_ASL_FLAG OR
837 l_default_SAFETY_STOCK_FLAG <> P_default_SAFETY_STOCK_FLAG OR
838 l_default_CONDITION_TYPE <> P_default_CONDITION_TYPE OR
839 l_default_PLANNER_CODE <> P_default_PLANNER_CODE OR
840 l_default_CATEGORY_SET_ID <> P_default_CATEGORY_SET_ID OR
841 l_default_CATEGORY_ID <> P_default_CATEGORY_ID OR
842 l_default_RECOMMEND_METHOD <> P_default_RECOMMEND_METHOD OR
843 l_DEFAULT_USAGE_WEIGHT1 <> p_DEFAULT_USAGE_WEIGHT1 OR
844 l_DEFAULT_USAGE_WEIGHT2 <> p_DEFAULT_USAGE_WEIGHT2 OR
845 l_DEFAULT_USAGE_WEIGHT3 <> p_DEFAULT_USAGE_WEIGHT3 OR
846 l_DEFAULT_USAGE_WEIGHT4 <> p_DEFAULT_USAGE_WEIGHT4 OR
847 NVL(l_override_FORECAST_RULE_ID,'Y') <> P_override_FORECAST_RULE_ID OR
848 NVL(l_override_EXCESS_RULE_ID,'Y') <> P_override_EXCESS_RULE_ID OR
849 NVL(l_override_REC_RULE_ID,'Y') <> P_override_REC_RULE_ID OR
850 NVL(l_override_SERVICE_LEVEL,'Y') <> P_override_SERVICE_LEVEL OR
851 NVL(l_override_EDQ_FACTOR,'Y') <> P_override_EDQ_FACTOR OR
852 NVL(l_override_ASL_FLAG,'Y') <> P_override_ASL_FLAG OR
853 NVL(l_override_SAFETY_STOCK_FLAG,'Y') <> P_override_SAFETY_STOCK_FLAG OR
854 NVL(l_override_CONDITION_TYPE,'Y') <> P_override_CONDITION_TYPE OR
855 NVL(l_override_PLANNER_CODE,'Y') <> P_override_PLANNER_CODE OR
856 NVL(l_override_CATEGORY_SET_ID,'Y') <> P_override_CATEGORY_SET_ID OR
857 NVL(l_override_CATEGORY_ID,'Y') <> P_override_CATEGORY_ID OR
858 NVL(l_override_RECOMMEND_METHOD,'Y') <> P_override_RECOMMEND_METHOD OR
859 NVL(l_OVERRIDE_USAGE_WEIGHT1,'Y') <> p_OVERRIDE_USAGE_WEIGHT1 OR
860 NVL(l_OVERRIDE_USAGE_WEIGHT2,'Y') <> p_OVERRIDE_USAGE_WEIGHT2 OR
861 NVL(L_OVERRIDE_USAGE_WEIGHT3,'Y') <> P_OVERRIDE_USAGE_WEIGHT3 OR
862 NVL(l_OVERRIDE_USAGE_WEIGHT4,'Y') <> p_OVERRIDE_USAGE_WEIGHT4 ) THEN
863 BEGIN
864 UPDATE CSP_PLANNING_PARAMETERS SET
865 PRODUCT_NORM_NODE_ID = Decode(DEFAULT_PRODUCT_NORM_NODE_ID,l_node_name,P_PRODUCT_NORM_NODE_ID,PRODUCT_NORM_NODE_ID)
866 ,DEFAULT_PRODUCT_NORM_NODE_ID = DECODE(DEFAULT_PRODUCT_NORM_NODE_ID,L_NODE_NAME,P_DEFAULT_PRODUCT_NORM_NODE_ID,DEFAULT_PRODUCT_NORM_NODE_ID)
867 ,FORECAST_RULE_ID = Decode(DEFAULT_FORECAST_RULE_ID,l_node_name,P_FORECAST_RULE_ID,FORECAST_RULE_ID)
868 ,DEFAULT_FORECAST_RULE_ID = Decode(DEFAULT_FORECAST_RULE_ID,l_node_name,P_DEFAULT_FORECAST_RULE_ID,DEFAULT_FORECAST_RULE_ID)
869 ,OVERRIDE_FORECAST_RULE_ID = DECODE(DEFAULT_FORECAST_RULE_ID,L_NODE_NAME,P_OVERRIDE_FORECAST_RULE_ID,OVERRIDE_FORECAST_RULE_ID)
870 ,EXCESS_RULE_ID = Decode(DEFAULT_EXCESS_RULE_ID,l_node_name,P_EXCESS_RULE_ID,EXCESS_RULE_ID)
871 ,DEFAULT_EXCESS_RULE_ID = Decode(DEFAULT_EXCESS_RULE_ID,l_node_name,P_DEFAULT_EXCESS_RULE_ID,DEFAULT_EXCESS_RULE_ID)
872 ,OVERRIDE_EXCESS_RULE_ID = DECODE(DEFAULT_EXCESS_RULE_ID,L_NODE_NAME,P_OVERRIDE_EXCESS_RULE_ID,OVERRIDE_EXCESS_RULE_ID)
873 ,RECOMMENDATION_RULE_ID = Decode(DEFAULT_REC_RULE_ID,l_node_name,P_RECOMMENDATION_RULE_ID,RECOMMENDATION_RULE_ID)
874 ,DEFAULT_REC_RULE_ID = Decode(DEFAULT_REC_RULE_ID,l_node_name,P_DEFAULT_REC_RULE_ID,DEFAULT_REC_RULE_ID)
875 ,OVERRIDE_REC_RULE_ID = DECODE(DEFAULT_REC_RULE_ID,L_NODE_NAME,P_OVERRIDE_REC_RULE_ID,OVERRIDE_REC_RULE_ID)
876 ,SERVICE_LEVEL = Decode(DEFAULT_SERVICE_LEVEL,l_node_name,P_SERVICE_LEVEL,SERVICE_LEVEL)
877 ,DEFAULT_SERVICE_LEVEL = Decode(DEFAULT_SERVICE_LEVEL,l_node_name,P_DEFAULT_SERVICE_LEVEL,DEFAULT_SERVICE_LEVEL)
878 ,OVERRIDE_SERVICE_LEVEL = DECODE(DEFAULT_SERVICE_LEVEL,L_NODE_NAME,P_OVERRIDE_SERVICE_LEVEL,OVERRIDE_SERVICE_LEVEL)
879 ,EDQ_FACTOR = Decode(DEFAULT_EDQ_FACTOR,l_node_name,P_Edq_Factor,EDQ_FACTOR)
880 ,DEFAULT_EDQ_FACTOR = Decode(DEFAULT_EDQ_FACTOR,l_node_name,P_DEFAULT_Edq_Factor,DEFAULT_EDQ_FACTOR)
881 ,OVERRIDE_EDQ_FACTOR = DECODE(DEFAULT_EDQ_FACTOR,L_NODE_NAME,P_OVERRIDE_EDQ_FACTOR,OVERRIDE_EDQ_FACTOR)
882 ,ASL_FLAG = Decode(DEFAULT_ASL_FLAG,l_node_name,P_Asl_Flag,ASL_FLAG)
883 ,DEFAULT_ASL_FLAG = Decode(DEFAULT_ASL_FLAG,l_node_name,P_DEFAULT_Asl_Flag,DEFAULT_ASL_FLAG)
884 ,OVERRIDE_ASL_FLAG = DECODE(DEFAULT_ASL_FLAG,L_NODE_NAME,P_OVERRIDE_ASL_FLAG,OVERRIDE_ASL_FLAG)
885 ,SAFETY_STOCK_FLAG = Decode(DEFAULT_SAFETY_STOCK_FLAG,l_node_name,P_Safety_Stock_Flag,SAFETY_STOCK_FLAG)
886 ,DEFAULT_SAFETY_STOCK_FLAG = Decode(DEFAULT_SAFETY_STOCK_FLAG,l_node_name,p_DEFAULT_Safety_Stock_Flag,DEFAULT_SAFETY_STOCK_FLAG)
887 ,OVERRIDE_SAFETY_STOCK_FLAG = DECODE(DEFAULT_SAFETY_STOCK_FLAG,L_NODE_NAME,P_OVERRIDE_SAFETY_STOCK_FLAG,OVERRIDE_SAFETY_STOCK_FLAG)
888 ,CONDITION_TYPE = Decode(DEFAULT_CONDITION_TYPE,l_node_name,P_Condition_Type,CONDITION_TYPE)
889 ,DEFAULT_CONDITION_TYPE = Decode(DEFAULT_CONDITION_TYPE,l_node_name,p_DEFAULT_Condition_Type,DEFAULT_CONDITION_TYPE)
890 ,OVERRIDE_CONDITION_TYPE = DECODE(DEFAULT_CONDITION_TYPE,L_NODE_NAME,P_OVERRIDE_CONDITION_TYPE,OVERRIDE_CONDITION_TYPE)
891 ,PLANNER_CODE = Decode(DEFAULT_PLANNER_CODE,l_node_name,P_Planner_Code,PLANNER_CODE)
892 ,DEFAULT_PLANNER_CODE = Decode(DEFAULT_PLANNER_CODE,l_node_name,p_DEFAULT_Planner_Code,DEFAULT_PLANNER_CODE)
893 ,OVERRIDE_PLANNER_CODE = Decode(DEFAULT_PLANNER_CODE,l_node_name,p_OVERRIDE_Planner_Code,OVERRIDE_PLANNER_CODE)
894 ,CATEGORY_SET_ID = Decode(DEFAULT_CATEGORY_SET_ID,l_node_name,P_CATEGORY_SET_ID,CATEGORY_SET_ID)
895 ,DEFAULT_CATEGORY_SET_ID = Decode(DEFAULT_CATEGORY_SET_ID,l_node_name,p_DEFAULT_CATEGORY_SET_ID,DEFAULT_CATEGORY_SET_ID)
896 ,OVERRIDE_CATEGORY_SET_ID = DECODE(DEFAULT_CATEGORY_SET_ID,L_NODE_NAME,P_OVERRIDE_CATEGORY_SET_ID,OVERRIDE_CATEGORY_SET_ID)
897 ,CATEGORY_ID = Decode(DEFAULT_CATEGORY_ID,l_node_name,P_CATEGORY_ID,CATEGORY_ID)
898 ,DEFAULT_CATEGORY_ID = Decode(DEFAULT_CATEGORY_ID,l_node_name,p_DEFAULT_CATEGORY_ID,DEFAULT_CATEGORY_ID)
899 ,OVERRIDE_CATEGORY_ID = DECODE(DEFAULT_CATEGORY_ID,L_NODE_NAME,P_OVERRIDE_CATEGORY_ID,OVERRIDE_CATEGORY_ID)
900 ,RECOMMEND_METHOD = Decode(DEFAULT_RECOMMEND_METHOD,l_node_name,P_RECOMMEND_METHOD,RECOMMEND_METHOD)
901 ,DEFAULT_RECOMMEND_METHOD = Decode(DEFAULT_RECOMMEND_METHOD,l_node_name,p_DEFAULT_RECOMMEND_METHOD,DEFAULT_RECOMMEND_METHOD)
902 ,OVERRIDE_RECOMMEND_METHOD = DECODE(DEFAULT_RECOMMEND_METHOD,L_NODE_NAME,P_OVERRIDE_RECOMMEND_METHOD,OVERRIDE_RECOMMEND_METHOD)
903 ,USAGE_WEIGHT1 = Decode(DEFAULT_USAGE_WEIGHT1,l_node_name,P_USAGE_WEIGHT1,USAGE_WEIGHT1)
904 ,DEFAULT_USAGE_WEIGHT1 = Decode(DEFAULT_USAGE_WEIGHT1,l_node_name,p_DEFAULT_USAGE_WEIGHT1,DEFAULT_USAGE_WEIGHT1)
905 ,OVERRIDE_USAGE_WEIGHT1 = DECODE(DEFAULT_USAGE_WEIGHT1,L_NODE_NAME,P_OVERRIDE_USAGE_WEIGHT1,OVERRIDE_USAGE_WEIGHT1)
906 ,USAGE_WEIGHT2 = Decode(DEFAULT_USAGE_WEIGHT2,l_node_name,P_USAGE_WEIGHT2,USAGE_WEIGHT2)
907 ,DEFAULT_USAGE_WEIGHT2 = Decode(DEFAULT_USAGE_WEIGHT2,l_node_name,p_DEFAULT_USAGE_WEIGHT2,DEFAULT_USAGE_WEIGHT2)
908 ,OVERRIDE_USAGE_WEIGHT2 = DECODE(DEFAULT_USAGE_WEIGHT2,L_NODE_NAME,P_OVERRIDE_USAGE_WEIGHT2,OVERRIDE_USAGE_WEIGHT2)
909 ,USAGE_WEIGHT3 = Decode(DEFAULT_USAGE_WEIGHT3,l_node_name,P_USAGE_WEIGHT3,USAGE_WEIGHT3)
910 ,DEFAULT_USAGE_WEIGHT3 = Decode(DEFAULT_USAGE_WEIGHT3,l_node_name,p_DEFAULT_USAGE_WEIGHT3,DEFAULT_USAGE_WEIGHT3)
911 ,OVERRIDE_USAGE_WEIGHT3 = DECODE(DEFAULT_USAGE_WEIGHT3,L_NODE_NAME,P_OVERRIDE_USAGE_WEIGHT3,OVERRIDE_USAGE_WEIGHT3)
912 ,USAGE_WEIGHT4 = Decode(DEFAULT_USAGE_WEIGHT4,l_node_name,P_USAGE_WEIGHT4,USAGE_WEIGHT4)
913 ,default_usage_weight4 = decode(default_usage_weight4,l_node_name,p_DEFAULT_USAGE_WEIGHT4,default_usage_weight4)
914 ,override_usage_weight4 = decode(default_usage_weight4,l_node_name,p_OVERRIDE_USAGE_WEIGHT4,override_usage_weight4)
915 where level_id like p_level_id || '.' || '%';
916 Exception
917 When no_data_found then
918 Null;
919 END;
920 End If;
921 End Update_Planning_Parameters;
922
923 Procedure Delete_Planning_Parameters(p_LEVEL_ID VARCHAR2) Is
924 l_level_id Varchar2(2000);
925 l_Planning_Parameters_Id Number;
926 l_node_value Varchar2(200);
927 Begin
928 IF L_LEVEL_ID IS NOT NULL THEN
929 Csp_Planning_Parameters_Pkg.Delete_Row (p_LEVEL_ID => p_level_id);
930 End if;
931
932 /*
933 If l_node_type in (planner.pln_org_node) then
934 Begin
935 DELETE FROM CSP_STOCK_LISTS
936 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
937 AND SUBINVENTORY_CODE IS NULL;
938 Exception
939 When no_data_found then
940 Null;
941 End ;
942
943 Begin
944 UPDATE MTL_SYSTEM_ITEMS
945 SET INVENTORY_PLANNING_CODE = 6
946 WHERE ORGANIZATION_ID = p_ORGANIZATION_ID
947 AND INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID;
948 Exception
949 When no_data_found then
950 Null;
951 End ;
952 Elsif l_node_type in (planner.pln_subinv_node) then
953
954 Begin
955 DELETE FROM CSP_STOCK_LISTS
956 WHERE ORGANIZATION_ID = P_ORGANIZATION_ID
957 AND NVL(SUBINVENTORY_CODE,'X') = NVL(P_SUBINVENTORY_CODE,'X');
958 Exception
959 When no_data_found then
960 Null;
961 End;
962
963 Begin
964 Update MTL_ITEM_SUB_INVENTORIES
965 SET INVENTORY_PLANNING_CODE = 6
966 WHERE INVENTORY_ITEM_ID = p_INVENTORY_ITEM_ID AND
967 ORGANIZATION_ID = p_ORGANIZATION_ID AND
968 SECONDARY_INVENTORY = p_SECONDARY_INVENTORY;
969 Exception
970 When no_data_found then
971 Null;
972 End ;
973 End if;
974 */
975 END DELETE_PLANNING_PARAMETERS;
976 FUNCTION VALIDATE_CONDITION_TYPE(p_org_id NUMBER,p_subinv VARCHAR2) RETURN VARCHAR2 IS
977 Cursor c_resource(p_org_id Number,p_subinv Varchar2) Is
978 Select csi.condition_type
979 From csp_inv_loc_assignments cila,
980 csp_sec_inventories csi
981 Where cila.organization_id = p_org_id
982 And cila.subinventory_code = p_subinv
983 --And nvl(cila.effective_date_end,sysdate) >= sysdate /* Forward port fix of 115.9 bug 4896449*/
984 And csi.secondary_inventory_name = cila.subinventory_code
985 And csi.organization_id = cila.organization_id;
986
987 Cursor c_return_subinv(p_Org_Id Number,p_subinv Varchar2) Is
988 Select csi.condition_type
989 from csp_sec_inventories csi
990 Where csi.return_organization_id = p_org_id
991 And csi.return_subinventory_name = p_subinv
992 Group by csi.condition_type;
993
994 l_return_condition_type Varchar2(1);
995 l_resource_condition_type Varchar2(1);
996 l_condition_type Varchar2(1);
997 L_CONDITION_TYPE_MEANING VARCHAR2(2000);
998 l_msg VARCHAR2(1000);
999
1000 BEGIN
1001
1002 Open c_resource(p_org_id,p_subinv);
1003 Fetch c_resource into l_resource_condition_type;
1004 Close c_resource;
1005
1006 Open c_return_subinv(p_org_id,p_subinv);
1007 Fetch c_return_subinv into l_return_condition_type;
1008 Close c_return_subinv;
1009
1010 L_CONDITION_TYPE := NVL(L_RESOURCE_CONDITION_TYPE,L_RETURN_CONDITION_TYPE);
1011 RETURN L_CONDITION_TYPE;
1012 END;
1013 end;