[Home] [Help]
PACKAGE BODY: APPS.CSP_AUTO_ASLMSL_PVT
Source
1 PACKAGE BODY CSP_AUTO_ASLMSL_PVT as
2 /* $Header: cspvaslb.pls 120.12 2011/12/22 19:23:24 hhaugeru ship $ */
3 -- Start of Comments
4 -- Package name : CSP_AUTO_ASLMSL_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSP_AUTO_ASLMSL_PVT';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'cspvaslb.pls';
13
14 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
15 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
16 G_TOP_NODE_ID NUMBER := 0;
17 G_PRODUCT_ORGANIZATION NUMBER := Fnd_Profile.Value('ASO_PRODUCT_ORGANIZATION_ID');
18 G_USAGE_DATA NUMBER;
19 G_FORECAST_DATA NUMBER;
20 G_TOP_NODE_USAGE_DATA NUMBER;
21 G_ORG_USAGE_DATA NUMBER;
22 G_PROCESS_STATUS_OPEN VARCHAR2(10);
23 G_DAYS NUMBER;
24 G_NO_LEAD_TIME_MSG VARCHAR2(2000);
25 G_WAREHOUSE VARCHAR2(1) := 'W';
26 G_FIELD_ENGINEER VARCHAR2(1) := 'F';
27 G_HIST_DATES_TABLE CSP_AUTO_ASLMSL_PVT.CSP_Date_Tbl_Type;
28 G_OTHER_USAGE_DATA NUMBER ;
29 G_ORG_OTHER_USAGE_DATA NUMBER ;
30 G_DEFAULT NUMBER := -1;
31 G_TXN_TYPE_ID NUMBER := 93;
32 G_FIELD_SERVICE VARCHAR2(1) := 'F';
33 G_MATERIAL_TRANSACTION VARCHAR2(1) := 'M';
34 G_FORECAST_RULE_ID NUMBER;
35 G_HISTORY_PERIODS NUMBER;
36 G_PERIOD_SIZE NUMBER;
37 G_LAST_RUN_DATE DATE;
38
39
40 PROCEDURE Initialize IS
41 Cursor c_Lookup_Code(p_Lookup_Type Varchar2,p_Meaning Varchar2) Is
42 Select LOOKUP_CODE
43 From FND_LOOKUPS
44 Where LOOKUP_TYPE = p_Lookup_type
45 And MEANING = p_Meaning;
46
47 Cursor c_get_Message Is
48 Select MESSAGE_TEXT
49 From FND_NEW_MESSAGES
50 Where APPLICATION_ID = 523
51 And MESSAGE_NAME = 'CSP_NO_LEAD_TIME';
52
53 Cursor c_Period_Size Is
54 Select HISTORY_PERIODS,
55 PERIOD_SIZE,
56 cfrb.FORECAST_RULE_ID
57 From CSP_PLANNING_PARAMETERS cpp,
58 CSP_FORECAST_RULES_B cfrb
59 Where cpp.ORGANIZATION_ID IS NULL
60 And cpp.SECONDARY_INVENTORY IS NULL
61 And cpp.FORECAST_RULE_ID = cfrb.FORECAST_RULE_ID;
62
63 l_api_name CONSTANT VARCHAR2(30) := 'Initialize';
64 l_api_version_number CONSTANT NUMBER := 1.0;
65 l_return_status_full VARCHAR2(1);
66 l_sqlcode NUMBER;
67 l_sqlerrm Varchar2(2000);
68
69 l_String VARCHAR2(2000);
70 l_Rollback VARCHAR2(1) := 'Y';
71
72 l_Msg_Count NUMBER;
73 l_Msg_Data Varchar2(2000);
74
75 X_Return_Status VARCHAR2(1);
76 X_Msg_Count NUMBER;
77 X_Msg_Data VARCHAR2(2000);
78
79 l_Init_Msg_List VARCHAR2(1) := FND_API.G_TRUE;
80 l_Commit VARCHAR2(1) := FND_API.G_TRUE;
81 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
82
83 BEGIN
84 -- Alter session
85 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
86 -- Get Lookup Values
87 -- Usage Data
88 Open c_lookup_code('CSP_HISTORY_DATA_TYPE','USAGE');
89 Fetch c_lookup_code INTO G_USAGE_DATA;
90 Close c_Lookup_Code;
91
92 -- Forecast Data
93 Open c_lookup_code('CSP_HISTORY_DATA_TYPE','FORECAST');
94 Fetch c_lookup_code INTO G_FORECAST_DATA;
95 Close c_Lookup_Code;
96
97 -- Top Node Usage Data
98 Open c_lookup_code('CSP_HISTORY_DATA_TYPE','TOP_NODE_USAGE');
99 Fetch c_lookup_code INTO G_TOP_NODE_USAGE_DATA;
100 Close c_Lookup_Code;
101
102 -- Org Usage Data
103 Open c_lookup_code('CSP_HISTORY_DATA_TYPE','ORGANIZATION_USAGE');
104 Fetch c_lookup_code INTO G_ORG_USAGE_DATA;
105 Close c_Lookup_Code;
106
107 -- Other Usage Data
108 Open c_lookup_code('CSP_HISTORY_DATA_TYPE','OTHER_USAGE_DATA');
109 Fetch c_lookup_code INTO G_OTHER_USAGE_DATA;
110 Close c_Lookup_Code;
111
112 -- Org Other Usage Data
113 Open c_lookup_code('CSP_HISTORY_DATA_TYPE','ORG_OTHER_USAGE_DATA');
114 Fetch c_lookup_code INTO G_ORG_OTHER_USAGE_DATA;
115 Close c_Lookup_Code;
116
117 --- Process Status
118 Open c_lookup_code('CSP_PLANNING_PROCESS_STATUS','Open');
119 Fetch c_lookup_code INTO G_PROCESS_STATUS_OPEN;
120 Close c_lookup_code;
121
122 -- Period Type
123 Open c_lookup_code('CSP_PERIOD_TYPE','Days');
124 Fetch c_lookup_code INTO G_DAYS;
125 Close c_lookup_code;
126
127 --- Get message for no lead time
128 Open c_Get_Message;
129 Fetch c_Get_Message INTO G_NO_LEAD_TIME_MSG;
130 Close c_Get_Message;
131
132 -- Initialize API return status to SUCCESS
133 x_return_status := FND_API.G_RET_STS_SUCCESS;
134
135
136 -- Standard call to get message count and if count is 1, get message info.
137 FND_MSG_PUB.Count_And_Get
138 ( p_count => x_msg_count,
139 p_data => x_msg_data
140 );
141
142 EXCEPTION
143 WHEN FND_API.G_EXC_ERROR THEN
144 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
145 P_API_NAME => L_API_NAME
146 ,P_PKG_NAME => G_PKG_NAME
147 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
148 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
149 ,P_ROLLBACK_FLAG => l_Rollback
150 ,X_MSG_COUNT => X_MSG_COUNT
151 ,X_MSG_DATA => X_MSG_DATA
152 ,X_RETURN_STATUS => X_RETURN_STATUS);
153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
155 P_API_NAME => L_API_NAME
156 ,P_PKG_NAME => G_PKG_NAME
157 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
158 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
159 ,P_ROLLBACK_FLAG => l_Rollback
160 ,X_MSG_COUNT => X_MSG_COUNT
161 ,X_MSG_DATA => X_MSG_DATA
162 ,X_RETURN_STATUS => X_RETURN_STATUS);
163 WHEN OTHERS THEN
164 l_sqlcode := SQLCODE;
165 l_sqlerrm := SQLERRM;
166 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
167 P_API_NAME => L_API_NAME
168 ,P_PKG_NAME => G_PKG_NAME
169 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
170 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
171 ,P_SQLCODE => l_sqlcode
172 ,P_SQLERRM => l_sqlerrm
173 ,P_ROLLBACK_FLAG => l_Rollback
174 ,X_MSG_COUNT => X_MSG_COUNT
175 ,X_MSG_DATA => X_MSG_DATA
176 ,X_RETURN_STATUS => X_RETURN_STATUS);
177 End Initialize;
178
179 Procedure Add_Err_Msg Is
180 l_msg_index_out NUMBER;
181 x_msg_data_temp Varchar2(2000);
182 x_msg_data Varchar2(4000);
183 Begin
184 If fnd_msg_pub.count_msg > 0 Then
185 FOR i IN REVERSE 1..fnd_msg_pub.count_msg Loop
186 fnd_msg_pub.get(p_msg_index => i,
187 p_encoded => 'F',
188 p_data => x_msg_data_temp,
189 p_msg_index_out => l_msg_index_out);
190 x_msg_data := x_msg_data || x_msg_data_temp;
191 End Loop;
192 FND_FILE.put_line(FND_FILE.log,x_msg_data);
193 fnd_msg_pub.delete_msg;
194 End if;
195 End;
196
197 PROCEDURE Generate_Recommendations (
198 retcode OUT NOCOPY NUMBER,
199 errbuf OUT NOCOPY VARCHAR2,
200 p_Api_Version_Number IN NUMBER,
201 p_level_id IN VARCHAR2
202 ) IS
203
204 Cursor c_get_Message Is
205 Select MESSAGE_TEXT
206 From FND_NEW_MESSAGES
207 Where APPLICATION_ID = 523
208 And MESSAGE_NAME = 'CSP_NO_LEAD_TIME';
209
210 Cursor c_recommend_method is
211 Select sum(decode(cpp.recommend_method,'PNORM',0,'TNORM',0,1)),
212 sum(decode(cpp.recommend_method,'PNORM',1,'USAGE_PNORM',1,0)),
213 sum(decode(cpp.recommend_method,'TNORM',1,'USAGE_TNORM',1,0))
214 from csp_planning_parameters cpp
215 where level_id like p_level_id || '%'
216 and node_type in ('ORGANIZATION_WH','SUBINVENTORY');
217
218
219 l_api_name CONSTANT VARCHAR2(30) := 'Generate_Recommendations';
220 l_api_version_number CONSTANT NUMBER := 1.0;
221 l_return_status_full VARCHAR2(1);
222 l_sqlcode NUMBER;
223 l_sqlerrm Varchar2(2000);
224 l_usg_count Number := 0;
225 l_pnorm_count Number := 0;
226 l_tnorm_count Number := 0;
227
228 l_String VARCHAR2(2000);
229 l_Rollback VARCHAR2(1) := 'Y';
230
231 l_Msg_Count NUMBER;
232 l_Msg_Data Varchar2(2000);
233
234 X_Return_Status VARCHAR2(1);
235 X_Msg_Count NUMBER;
236 X_Msg_Data VARCHAR2(2000);
237
238 l_Init_Msg_List VARCHAR2(1) := FND_API.G_TRUE;
239 l_Commit VARCHAR2(1) := FND_API.G_TRUE;
240 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
241
242 l_get_app_info boolean;
243 l_status varchar2(1);
244 l_industry varchar2(1);
245 l_oracle_schema varchar2(30);
246
247 BEGIN
248
249 -- Alter session
250 if p_api_version_number <> 2.0 then
251 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
252 end if;
253 l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
254 If p_Level_Id = '1' Then
255 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_HEADERS';
256 Else
257 Delete from csp_usage_headers cuh
258 Where (cuh.organization_id,cuh.secondary_inventory) in
259 (select cpp.organization_id ,nvl(cpp.secondary_inventory,'-')
260 from csp_planning_parameters cpp
261 Where cpp.level_id like p_Level_Id || '%' And cpp.node_type in ('ORGANIZATION_WH','SUBINVENTORY'));
262 End If;
263 commit;
264 If p_Level_Id = '1' Then
265 DELETE FROM csp_usage_histories
266 WHERE history_data_type IN (2,5);
267 Else
268 Delete from csp_usage_histories cuh
269 Where history_data_type in (2,5)
270 And (cuh.organization_id,cuh.subinventory_code) in
271 (select cpp.organization_id ,nvl(cpp.secondary_inventory,'-')
272 from csp_planning_parameters cpp
273 Where cpp.level_id like p_Level_Id || '%' And cpp.node_type in ('ORGANIZATION_WH','SUBINVENTORY'));
274 End If;
275 commit;
276 -- Standard Start of API savepoint
277 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
278
279 if p_api_version_number <> 2.0 then
280 -- Standard call to check for call compatibility.
281 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
282 p_api_version_number,
283 l_api_name,
284 G_PKG_NAME) THEN
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286 END IF;
287 end if;
288
289
290 -- Initialize message list if p_init_msg_list is set to TRUE.
291 IF FND_API.to_Boolean( l_init_msg_list )
292 THEN
293 FND_MSG_PUB.initialize;
294 END IF;
295
296
297 -- Initialize API return status to SUCCESS
298 x_return_status := FND_API.G_RET_STS_SUCCESS;
299
300 --
301 -- API body
302 --
303 --- Get message for no lead time
304 Open c_Get_Message;
305 Fetch c_Get_Message INTO G_NO_LEAD_TIME_MSG;
306 Close c_Get_Message;
307
308 --- Get recommendation methods count
309 Open c_recommend_method;
310 Fetch c_recommend_method into l_usg_count,l_pnorm_count,l_tnorm_count;
311 close c_recommend_method;
312
313 -- Generate ASL
314 -- If l_usg_count > 0 Then
315 Calculate_Forecast (
316 P_Api_Version_Number => 1.0,
317 P_Init_Msg_List => FND_API.G_FALSE,
318 P_Commit => FND_API.G_TRUE,
319 P_validation_level => FND_API.G_VALID_LEVEL_FULL,
320 P_Level_Id => P_Level_id,
321 P_reason_code => 'RECM',
322 X_Return_Status => X_Return_Status,
323 X_Msg_Count => X_Msg_Count,
324 X_Msg_Data => X_Msg_Data );
325 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
326 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
327 End If;
328 -- End If;
329 fnd_message.set_name('CSP','CSP_ASL_MSG');
330 fnd_message.set_token('VALUE','calculate_forecast');
331 fnd_msg_pub.add;
332 Add_Err_Msg;
333 -- Calculate New Product Planning
334 -- Re-establish Savepoint
335 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
336 Calculate_New_Product_Planning (
337 P_Api_Version_Number => 1.0,
338 P_Init_Msg_List => FND_API.G_FALSE,
339 P_Commit => FND_API.G_TRUE,
340 P_validation_level => FND_API.G_VALID_LEVEL_FULL,
341 P_Level_Id => P_Level_id,
342 X_Return_Status => X_Return_Status,
343 X_Msg_Count => X_Msg_Count,
344 X_Msg_Data => X_Msg_Data );
345 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
346 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
347 End If;
348 fnd_message.set_name('CSP','CSP_ASL_MSG');
349 fnd_message.set_token('VALUE','calculate_new_product_planning');
350 fnd_msg_pub.add;
351 Add_Err_Msg;
352 -- Calculate Product Norm
353 If l_pnorm_count > 0 Then
354 -- Re-establish Savepoint
355 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
356 Calculate_Product_Norm (
357 P_Api_Version_Number => 1.0,
358 P_Init_Msg_List => FND_API.G_FALSE,
359 P_Commit => FND_API.G_TRUE,
360 P_validation_level => FND_API.G_VALID_LEVEL_FULL,
361 P_Level_Id => P_Level_id,
362 X_Return_Status => X_Return_Status,
363 X_Msg_Count => X_Msg_Count,
364 X_Msg_Data => X_Msg_Data );
365 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
366 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
367 End If;
368 End If;
369 fnd_message.set_name('CSP','CSP_ASL_MSG');
370 fnd_message.set_token('VALUE','calculate_product_norm');
371 fnd_msg_pub.add;
372 Add_Err_Msg;
373 -- Calculate Territory Norm
374 If l_tnorm_count > 0 Then
375 -- Re-establish Savepoint
376 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
377 Calculate_Territory_Norm (
378 P_Api_Version_Number => 1.0,
379 P_Init_Msg_List => FND_API.G_FALSE,
380 P_Commit => FND_API.G_TRUE,
381 P_validation_level => FND_API.G_VALID_LEVEL_FULL,
382 P_Level_Id => P_Level_id,
383 X_Return_Status => X_Return_Status,
384 X_Msg_Count => X_Msg_Count,
385 X_Msg_Data => X_Msg_Data );
386 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
387 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
388 End If;
389 End If;
390 fnd_message.set_name('CSP','CSP_ASL_MSG');
391 fnd_message.set_token('VALUE','calculate_territory_norm');
392 fnd_msg_pub.add;
393 Add_Err_Msg;
394 -- Re-establish Savepoint
395 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
396 --- Create header records for Stock list items at Subinventory Level
397 INSERT INTO CSP_USAGE_HEADERS
398 (USAGE_HEADER_ID,
399 INVENTORY_ITEM_ID,
400 ORGANIZATION_ID,
401 SECONDARY_INVENTORY,
402 HEADER_DATA_TYPE,
403 RAW_AWU,
404 AWU,
405 ITEM_COST,
406 LEAD_TIME,
407 STANDARD_DEVIATION,
408 PROCESS_STATUS,
409 EXTERNAL_DATA,
410 CREATION_DATE,
411 CREATED_BY,
412 LAST_UPDATE_DATE,
413 LAST_UPDATED_BY,
414 LAST_UPDATE_LOGIN)
415 SELECT NULL,
416 mis.inventory_item_id,
417 mis.organization_id,
418 mis.secondary_inventory,
419 10,
420 NULL,
421 NULL,
422 cic.item_cost,
423 NULL,
424 NULL,
425 'O',
426 'N',
427 sysdate,
428 fnd_global.user_id,
429 sysdate,
430 fnd_global.user_id,
431 fnd_global.conc_login_id
432 From CSP_PLANNING_PARAMETERS cpp,
433 MTL_ITEM_SUB_INVENTORIES mis,
434 CST_ITEM_COSTS cic,
435 MTL_PARAMETERS mp
436 where cpp.node_type = 'SUBINVENTORY'
437 and mis.organization_id = cpp.organization_id
438 and mis.secondary_inventory = cpp.secondary_inventory
439 and mp.organization_id = cpp.organization_id
440 and cic.inventory_item_id = mis.inventory_item_id
441 And cic.organization_id = mis.organization_id
442 And cic.cost_type_id = mp.primary_cost_method
443 and (mis.min_minmax_quantity > 0 OR mis.max_minmax_quantity > 0) ;
444
445 COMMIT;
446 fnd_message.set_name('CSP','CSP_ASL_MSG');
447 fnd_message.set_token('VALUE','Stock List Update');
448 fnd_msg_pub.add;
449 Add_Err_Msg;
450
451 -- Re-establish Savepoint
452 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
453 --- Create header records for Stock list items at Organization Level
454 INSERT INTO CSP_USAGE_HEADERS
455 (USAGE_HEADER_ID,
456 INVENTORY_ITEM_ID,
457 ORGANIZATION_ID,
458 SECONDARY_INVENTORY,
459 HEADER_DATA_TYPE,
460 RAW_AWU,
461 AWU,
462 ITEM_COST,
463 LEAD_TIME,
464 STANDARD_DEVIATION,
465 PROCESS_STATUS,
466 EXTERNAL_DATA,
467 CREATION_DATE,
468 CREATED_BY,
469 LAST_UPDATE_DATE,
470 LAST_UPDATED_BY,
471 LAST_UPDATE_LOGIN)
472 SELECT NULL,
473 msi.inventory_item_id,
474 msi.organization_id,
475 '-',
476 11,
477 NULL,
478 NULL,
479 cic.item_cost,
480 NULL,
481 NULL,
482 'O',
483 'N',
484 sysdate,
485 fnd_global.user_id,
486 sysdate,
487 fnd_global.user_id,
488 fnd_global.conc_login_id
489 From CSP_PLANNING_PARAMETERS cpp,
490 MTL_SYSTEM_ITEMS msi,
491 CST_ITEM_COSTS cic,
492 MTL_PARAMETERS mp
493 where cpp.node_type = 'ORGANIZATION_WH'
494 and mp.organization_id = cpp.organization_id
495 and msi.organization_id = cpp.organization_id
496 and msi.inventory_planning_code = 2
497 and cic.inventory_item_id = msi.inventory_item_id
498 And cic.organization_id = msi.organization_id
499 And cic.cost_type_id = mp.primary_cost_method
500 and (msi.min_minmax_quantity > 0 OR msi.max_minmax_quantity > 0) ;
501
502 COMMIT;
503
504 fnd_message.set_name('CSP','CSP_ASL_MSG');
505 fnd_message.set_token('VALUE','Insert Usage Headers Update');
506 fnd_msg_pub.add;
507 Add_Err_Msg;
508 -- Re-establish Savepoint
509 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
510 INSERT INTO CSP_USAGE_HEADERS
511 (USAGE_HEADER_ID,
512 INVENTORY_ITEM_ID,
513 ORGANIZATION_ID,
514 SECONDARY_INVENTORY,
515 HEADER_DATA_TYPE,
516 RAW_AWU,
517 AWU,
518 ITEM_COST,
519 LEAD_TIME,
520 STANDARD_DEVIATION,
521 PROCESS_STATUS,
522 EXTERNAL_DATA,
523 PLANNING_PARAMETERS_ID,
524 CREATION_DATE,
525 CREATED_BY,
526 LAST_UPDATE_DATE,
527 LAST_UPDATED_BY,
528 LAST_UPDATE_LOGIN)
529 Select NULL,
530 cuh.inventory_item_id,
531 cuh.organization_id,
532 cuh.secondary_inventory,
533 decode(nvl(cuh.secondary_inventory,'-'),'-',4,1),
534 NULL,
535 decode(sign(
536 sum(decode(cpp.recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,5,cuh.awu,6,cuh.awu,0))) -
537 sum(decode(cpp.recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,7,cuh.awu,9,cuh.awu,0)))),
538 1,
539 decode(sum(decode(cpp.recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,7,nvl(cuh.awu,0),9,nvl(cuh.awu,0),0))),0,
540 sum(decode(recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,5,cuh.awu * cpp.usage_weight4,6,cuh.awu * cpp.usage_weight4,8,cuh.awu,0))),
541 sum(decode(recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,5,cuh.awu * cpp.usage_weight1,6,cuh.awu * cpp.usage_weight1,8,cuh.awu,cuh.awu * (1 - cpp.usage_weight1))))) ,
542 -1,
543 decode(nvl(sum(decode(cpp.recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,5,cuh.awu,6,cuh.awu,0))),0),0,
544 sum(decode(recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,5,0,6,0,8,cuh.awu,cuh.awu * (1- usage_weight3)))),
545 sum(decode(recommend_method,'PNORM',0,'TNORM',0,'USAGE',0,decode(cuh.header_data_type,5,cuh.awu * cpp.usage_weight2,6,cuh.awu * cpp.usage_weight2,8,cuh.awu,cuh.awu * (1 - cpp.usage_weight2))))),
546 sum(decode(recommend_method,
547 'PNORM',decode(cuh.header_data_type,7,cuh.awu,8,cuh.awu,0),
548 'TNORM',decode(cuh.header_data_type,9,cuh.awu,8,cuh.awu,0),
549 'USAGE',decode(cuh.header_data_type,5,cuh.awu,6,cuh.awu,8,cuh.awu,0),
550 decode(cuh.header_data_type,5,cuh.awu * cpp.usage_weight1,6,cuh.awu * cpp.usage_weight1,8,cuh.awu,cuh.awu * (1- cpp.usage_weight1))))),
551 nvl(cic.item_cost,0),
552 nvl(nvl(mism1.intransit_time,nvl(mism2.intransit_time,nvl(mism3.intransit_time,mism4.intransit_time))),
553 decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
554 decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0,
555 nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0), nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)),
556 nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))),
557 sum(nvl(cuh.standard_deviation,0)),
558 'O',
559 'N',
560 cpp.planning_parameters_id,
561 sysdate,
562 fnd_global.user_id,
563 sysdate,
564 fnd_global.user_id,
565 fnd_global.conc_login_id
566 from csp_planning_parameters cpp,
567 csp_usage_headers cuh,
568 cst_item_costs cic,
569 mtl_parameters mp,
570 mtl_system_items_b msib,
571 mtl_item_sub_inventories misi,
572 mtl_secondary_inventories msi,
573 mtl_interorg_ship_methods mism1,
574 mtl_interorg_ship_methods mism2,
575 mtl_interorg_ship_methods mism3,
576 mtl_interorg_ship_methods mism4
577 where cpp.level_id like p_level_id || '%'
578 and cpp.node_type in ('ORGANIZATION_WH','SUBINVENTORY')
579 and cpp.recommend_method in ('USAGE','USAGE_PNORM','USAGE_TNORM','PNORM','TNORM')
580 and cuh.organization_id = cpp.organization_id
581 and cuh.secondary_inventory = nvl(cpp.secondary_inventory,'-')
582 and cuh.header_data_type in (5,6,7,8,9,10,11)
583 and cic.inventory_item_id = cuh.inventory_item_id
584 And cic.organization_id = cuh.organization_id
585 And cic.cost_type_id = mp.primary_cost_method
586 and mp.organization_id = cuh.organization_id
587 and misi.organization_id (+) = cuh.organization_id
588 and misi.inventory_item_id (+) = cuh.inventory_item_id
589 and misi.secondary_inventory (+) = cuh.secondary_inventory
590 and mism4.to_organization_id (+) = mp.organization_id
591 and mism4.from_organization_id (+) = decode(mp.source_type,1,mp.source_organization_id,3,mp.source_organization_id,-1)
592 and mism4.default_flag (+) = 1
593 and mism3.to_organization_id (+) = msi.organization_id
594 and mism3.from_organization_id (+) = decode(msi.source_type,1,msi.source_organization_id,3,msi.source_organization_id,-1)
595 and mism3.default_flag (+) = 1
596 and mism2.to_organization_id (+) = msib.organization_id
597 and mism2.from_organization_id (+) = decode(msib.source_type,1,msib.source_organization_id,3,msib.source_organization_id,-1)
598 and mism2.default_flag (+) = 1
599 and mism1.to_organization_id (+) = misi.organization_id
600 and mism1.from_organization_id (+) = decode(misi.source_type,1,misi.source_organization_id,3,misi.source_organization_id,-1)
601 and mism1.default_flag (+) = 1
602 and msib.organization_id = cuh.organization_id
603 and msib.inventory_item_id = cuh.inventory_item_id
604 and msi.organization_id(+) = cuh.organization_id
605 and msi.secondary_inventory_name(+) = cuh.secondary_inventory
606 Group by cuh.inventory_item_id,
607 cuh.organization_id,
608 cuh.secondary_inventory,
609 cpp.planning_parameters_id,
610 nvl(cic.item_cost,0),
611 nvl(nvl(mism1.intransit_time,nvl(mism2.intransit_time,nvl(mism3.intransit_time,mism4.intransit_time))),
612 decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
613 decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0,
614 nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0), nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)),
615 nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))) ;
616
617 COMMIT;
618 fnd_message.set_name('CSP','CSP_ASL_MSG');
619 fnd_message.set_token('VALUE','Insert Usage Headers Update2');
620 fnd_msg_pub.add;
621 Add_Err_Msg;
622
623 -- Re-establish Savepoint
624 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
625 --- Update csp_usage_headers for excluded items
626 update csp_usage_headers cuh
627 set process_status = 'E'
628 Where (cuh.inventory_item_id,cuh.organization_id,cuh.secondary_inventory) in
629 (select mic.inventory_item_id,mic.organization_id,nvl(cpp.secondary_inventory,'-')
630 from csp_planning_parameters cpp,
631 mtl_item_categories mic
632 where cpp.node_type in ('ORGANIZATION_WH','SUBINVENTORY')
633 and mic.organization_id = cpp.organization_id
634 and mic.category_set_id = cpp.category_set_id
635 and mic.category_id = nvl(cpp.category_id,mic.category_id));
636
637 COMMIT;
638 fnd_message.set_name('CSP','CSP_ASL_MSG');
639 fnd_message.set_token('VALUE','Excluded Items');
640 fnd_msg_pub.add;
641 Add_Err_Msg;
642
643 -- Re-establish Savepoint
644 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
645 UPDATE CSP_USAGE_HEADERS usg_headers
646 SET (recommended_min_quantity,recommended_max_quantity) =
647 (SELECT decode(sq.MAX_QUANTITY,0,0,greatest(1,sq.MIN_QUANTITY)),
648 sq.MAX_QUANTITY
649 FROM (Select cuh.Inventory_Item_Id,
650 cuh.Organization_Id,
651 cuh.Secondary_Inventory,
652 ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time + DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0)) Min_quantity,
653 ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time +
654 DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0) +
655 DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,
656 DECODE(cpp.edq_factor,0,0,ROUND(cpp.Edq_Factor * (SQRT(52 * cuh.Awu * cuh.Item_Cost)/cuh.Item_Cost),4))))) max_quantity
657 from CSP_USAGE_HEADERS cuh,
658 CSP_PLANNING_PARAMETERS cpp,
659 CSP_SAFETY_FACTORS csf
660 Where cuh.header_data_type = 1
661 And cuh.process_status = 'O'
662 And cpp.organization_id = cuh.organization_id
663 And cpp.secondary_inventory = cuh.secondary_inventory
664 And cpp.node_type = 'SUBINVENTORY'
665 And csf.service_level = cpp.service_level
666 And csf.exposures = GREATEST(3,DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,DECODE(cpp.edq_factor,0,0,
667 LEAST(ROUND( cuh.AWU *52/ROUND(cpp.Edq_Factor *
668 (SQRT(52 * cuh.AWU * cuh.Item_Cost)/cuh.Item_Cost),4)),52)))))) sq
669 WHERE usg_headers.INVENTORY_ITEM_ID = sq.INVENTORY_ITEM_ID
670 AND usg_headers.ORGANIZATION_ID = sq.ORGANIZATION_ID
671 AND usg_headers.SECONDARY_INVENTORY = sq.SECONDARY_INVENTORY)
672 WHERE usg_headers.header_data_type = 1
673 and usg_headers.process_status = 'O';
674
675 COMMIT;
676 fnd_message.set_name('CSP','CSP_ASL_MSG');
677 fnd_message.set_token('VALUE','Min Max');
678 fnd_msg_pub.add;
679 Add_Err_Msg;
680
681 -- Re-establish Savepoint
682 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
683 UPDATE csp_usage_headers usg_headers
684 Set (recommended_min_quantity,recommended_max_quantity) =
685 (SELECT decode(sq.MAX_QUANTITY,0,0,greatest(1,sq.MIN_QUANTITY)),
686 sq.MAX_QUANTITY
687 FROM (Select cuh.inventory_item_id,
688 cuh.organization_id,
689 ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time +
690 DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0)) min_quantity,
691 ROUND(DECODE(SIGN(AWU),-1,0,ROUND(AWU,4))/7 * cuh.lead_time +
692 DECODE(cpp.safety_stock_flag,'Y',ROUND(csf.Safety_Factor * nvl(cuh.Standard_Deviation,0),4),0) +
693 DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,
694 DECODE(cpp.edq_factor,0,0,ROUND(cpp.Edq_Factor * (SQRT(52 * cuh.Awu * cuh.Item_Cost)/cuh.Item_Cost),4))))) max_quantity
695 from CSP_USAGE_HEADERS cuh,
696 CSP_PLANNING_PARAMETERS cpp,
697 CSP_SAFETY_FACTORS csf
698 Where cuh.header_data_type = 4
699 And cuh.process_status = 'O'
700 And cpp.organization_id = cuh.organization_id
701 And cpp.node_type = 'ORGANIZATION_WH'
702 And csf.service_level = cpp.service_level
703 And csf.exposures = GREATEST(3,DECODE(DECODE(SIGN(cuh.AWU),-1,0,cuh.AWU),0,0,DECODE(cuh.item_cost,0,0,DECODE(cpp.edq_factor,0,0,
704 LEAST(ROUND( cuh.AWU *52/ROUND(cpp.Edq_Factor *
705 (SQRT(52 * cuh.AWU * cuh.Item_Cost)/cuh.Item_Cost),4)),52)))))) sq
706 WHERE sq.inventory_item_id = usg_headers.inventory_item_id
707 AND sq.organization_id = usg_headers.organization_id)
708 WHERE usg_headers.header_data_type = 4
709 and usg_headers.process_status = 'O';
710
711 COMMIT;
712 fnd_message.set_name('CSP','CSP_ASL_MSG');
713 fnd_message.set_token('VALUE','Min Max');
714 fnd_msg_pub.add;
715 Add_Err_Msg;
716
717 -- Re-establish Savepoint
718 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
719 -- Calculate Tracking Signal for Subinventories
720 Calculate_Forecast (
721 P_Api_Version_Number => 1.0,
722 P_Init_Msg_List => FND_API.G_FALSE,
723 P_Commit => FND_API.G_TRUE,
724 P_validation_level => FND_API.G_VALID_LEVEL_FULL,
725 P_Level_Id => P_Level_id,
726 P_reason_code => 'TS',
727 X_Return_Status => X_Return_Status,
728 X_Msg_Count => X_Msg_Count,
729 X_Msg_Data => X_Msg_Data );
730 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
731 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
732 End If;
733
734 -- Re-establish Savepoint
735 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
736
737 -- Calculate Tracking Signal for Subinventory
738 update csp_usage_headers cuh
739 set tracking_signal =
740 (select round(decode(a.forecast_periods - 1,0,0, sum(a.diff)/
741 sqrt((sum(a.diff * a.diff) - (sum(a.diff) * sum(a.diff)/a.forecast_periods)) / (a.forecast_periods - 1))),4)
742 from (
743 select cuh_fcst.inventory_item_id,
744 cuh_fcst.organization_id,
745 cuh_fcst.subinventory_code,
746 cuh_fcst.quantity - sum(cuh_usg.quantity) diff,
747 cfrb.forecast_periods
748 from csp_usage_histories cuh_fcst,
749 csp_planning_parameters cpp,
750 csp_forecast_rules_b cfrb,
751 csp_usage_histories cuh_usg
752 where cuh_fcst.history_data_type = 5
753 and cuh_fcst.period_start_date between (trunc(sysdate) - cfrb.forecast_periods * cfrb.period_size * cfrb.tracking_signal_cycle) and trunc(sysdate)
754 and cuh_fcst.organization_id = cpp.organization_id
755 and cuh_fcst.subinventory_code = cpp.secondary_inventory
756 and cpp.recommend_method in ('USAGE','USAGE_PNORM','USAGE_TNORM')
757 and cpp.node_type = 'SUBINVENTORY'
758 and cfrb.forecast_rule_id = cpp.forecast_rule_id
759 and cuh_usg.period_start_date between cuh_fcst.period_start_date and
760 cuh_fcst.period_start_date + cfrb.period_size
761 and cuh_usg.organization_id = cuh_fcst.organization_id
762 and cuh_usg.subinventory_code = cuh_fcst.subinventory_code
763 and cuh_usg.inventory_item_id = cuh_fcst.inventory_item_id
764 and cuh_usg.history_data_type = 1
765 group by cuh_fcst.inventory_item_id,cuh_fcst.organization_id,
766 cuh_fcst.subinventory_code,cuh_fcst.quantity,
767 cfrb.forecast_periods) a
768 where a.inventory_item_id = cuh.inventory_item_id
769 and a.organization_id = cuh.organization_id
770 and a.subinventory_code = cuh.secondary_inventory
771 group by a.inventory_item_id,a.organization_id,a.subinventory_code,
772 a.forecast_periods)
773 where cuh.header_data_type = 1
774 and process_status = 'O';
775 COMMIT;
776 fnd_message.set_name('CSP','CSP_ASL_MSG');
777 fnd_message.set_token('VALUE','Tracking signal');
778 fnd_msg_pub.add;
779 Add_Err_Msg;
780
781 -- Re-establish Savepoint
782 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
783 -- Calculate Tracking Signal for Organization
784 update csp_usage_headers cuh
785 set tracking_signal =
786 (select round(decode(a.forecast_periods - 1,0,0, sum(a.diff)/
787 sqrt((sum(a.diff * a.diff) - (sum(a.diff) * sum(a.diff)/a.forecast_periods)) / (a.forecast_periods - 1))),4)
788 from (
789 select cuh_fcst.inventory_item_id,
790 cuh_fcst.organization_id,
791 cuh_fcst.quantity - sum(cuh_usg.quantity) diff,
792 cfrb.forecast_periods
793 from csp_usage_histories cuh_fcst,
794 csp_planning_parameters cpp,
795 csp_forecast_rules_b cfrb,
796 csp_usage_orgs cuh_usg
797 where cuh_fcst.history_data_type = 5
798 and cuh_fcst.period_start_date between (trunc(sysdate) - cfrb.forecast_periods * cfrb.period_size * cfrb.tracking_signal_cycle) and trunc(sysdate)
799 and cpp.organization_id = cuh_fcst.organization_id
800 and cpp.recommend_method in ('USAGE','USAGE_PNORM','USAGE_TNORM')
801 and cpp.node_type = 'ORGANIZATION_WH'
802 and cfrb.forecast_rule_id = cpp.forecast_rule_id
803 and cuh_usg.period_start_date between cuh_fcst.period_start_date and
804 cuh_fcst.period_start_date + cfrb.period_size
805 and cuh_usg.organization_id = cuh_fcst.organization_id
806 and cuh_usg.inventory_item_id = cuh_fcst.inventory_item_id
807 group by cuh_fcst.inventory_item_id,cuh_fcst.organization_id,
808 cuh_fcst.quantity, cfrb.forecast_periods) a
809 where a.inventory_item_id = cuh.inventory_item_id
810 and a.organization_id = cuh.organization_id
811 group by a.inventory_item_id,a.organization_id, a.forecast_periods)
812 where header_data_type = 4
813 and process_status = 'O';
814
815 COMMIT;
816
817 -- Re-establish Savepoint
818 SAVEPOINT GENERATE_RECOMMENDATIONS_PVT;
819 if p_api_version_number <> 2.0 then
820 Apply_Business_Rules (
821 P_Api_Version_Number => 1.0,
822 P_Init_Msg_List => FND_API.G_FALSE,
823 P_Commit => FND_API.G_TRUE,
824 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
825 X_Return_Status => x_return_status,
826 X_Msg_Count => x_msg_count,
827 X_Msg_Data => x_msg_data);
828 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
829 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
830 End If;
831
832 fnd_message.set_name('CSP','CSP_ASL_MSG');
833 fnd_message.set_token('VALUE','Apply Business Rules');
834 fnd_msg_pub.add;
835 Add_Err_Msg;
836 end if;
837 --
838 -- End of API body
839 --
840
841 -- Standard check for p_commit
842 IF FND_API.to_Boolean( l_commit )
843 THEN
844 COMMIT WORK;
845 END IF;
846
847 -- Standard call to get message count and if count is 1, get message info.
848 FND_MSG_PUB.Count_And_Get
849 ( p_count => x_msg_count,
850 p_data => x_msg_data
851 );
852 retcode := 0;
853 EXCEPTION
854 WHEN FND_API.G_EXC_ERROR THEN
855 Add_Err_Msg;
856 retcode := 2;
857 errbuf := X_Msg_Data;
858 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
859 P_API_NAME => L_API_NAME
860 ,P_PKG_NAME => G_PKG_NAME
861 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
862 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
863 ,P_ROLLBACK_FLAG => l_Rollback
864 ,X_MSG_COUNT => X_MSG_COUNT
865 ,X_MSG_DATA => X_MSG_DATA
866 ,X_RETURN_STATUS => X_RETURN_STATUS);
867 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
868 Add_Err_Msg;
869 retcode := 2;
870 errbuf := X_Msg_Data;
871 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
872 P_API_NAME => L_API_NAME
873 ,P_PKG_NAME => G_PKG_NAME
874 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
875 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
876 ,P_ROLLBACK_FLAG => l_Rollback
877 ,X_MSG_COUNT => X_MSG_COUNT
878 ,X_MSG_DATA => X_MSG_DATA
879 ,X_RETURN_STATUS => X_RETURN_STATUS);
880 Add_Err_Msg;
881 WHEN OTHERS THEN
882 l_sqlcode := SQLCODE;
883 l_sqlerrm := SQLERRM;
884 retcode := 2;
885 errbuf := SQLERRM;
886 fnd_message.set_name('CSP','CSP_ASL_MSG');
887 fnd_message.set_token('VALUE',l_sqlcode || ' ' || l_sqlerrm);
888 fnd_msg_pub.add;
889 Add_Err_Msg;
890 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
891 P_API_NAME => L_API_NAME
892 ,P_PKG_NAME => G_PKG_NAME
893 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
894 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
895 ,P_SQLCODE => l_sqlcode
896 ,P_SQLERRM => l_sqlerrm
897 ,P_ROLLBACK_FLAG => l_Rollback
898 ,X_MSG_COUNT => X_MSG_COUNT
899 ,X_MSG_DATA => X_MSG_DATA
900 ,X_RETURN_STATUS => X_RETURN_STATUS);
901 errbuf := sqlerrm;
902 retcode := 2;
903 Add_Err_Msg;
904 End Generate_Recommendations;
905
906
907 PROCEDURE Purge_Planning_Data (
908 P_Api_Version_Number IN NUMBER,
909 P_Init_Msg_List IN VARCHAR2,
910 P_Commit IN VARCHAR2,
911 P_validation_level IN NUMBER ,
912 X_Return_Status OUT NOCOPY VARCHAR2,
913 X_Msg_Count OUT NOCOPY NUMBER,
914 X_Msg_Data OUT NOCOPY VARCHAR2) IS
915
916 l_api_name CONSTANT VARCHAR2(30) := 'Purge_Planning_data' ;
917 l_api_version_number CONSTANT NUMBER := 1.0;
918 l_return_status_full VARCHAR2(1);
919 l_Sqlcode NUMBER;
920 l_Sqlerrm Varchar2(2000);
921
922 l_String VARCHAR2(2000);
923
924 l_Msg_Count NUMBER;
925 l_Msg_Data Varchar2(2000);
926 l_get_app_info boolean;
927 l_status varchar2(1);
928 l_industry varchar2(1);
929 l_oracle_schema varchar2(30);
930 BEGIN
931 -- Standard Start of API savepoint
932 SAVEPOINT PURGE_DATA_PVT;
933
934 -- Standard call to check for call compatibility.
935 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
936 p_api_version_number,
937 l_api_name,
938 G_PKG_NAME)
939 THEN
940 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
941 END IF;
942
943
944 -- Initialize message list if p_init_msg_list is set to TRUE.
945 IF FND_API.to_Boolean( p_init_msg_list )
946 THEN
947 FND_MSG_PUB.initialize;
948 END IF;
949
950
951 -- Initialize API return status to SUCCESS
952 x_return_status := FND_API.G_RET_STS_SUCCESS;
953
954 --
955 -- API body
956 --
957 l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
958 -- Clean up the tables
959
960 -- Delete from Csp_Usage_Headers
961 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_HEADERS';
962
963
964 -- Delete from Csp_Usage_Histories
965 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_HISTORIES';
966
967 -- Delete from Csp_Supply_Chain
968 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
969
970
971
972 IF FND_API.to_Boolean( p_commit )
973 THEN
974 COMMIT WORK;
975 END IF;
976
977 --
978 -- End of API body
979 --
980
981 -- Standard check for p_commit
982 IF FND_API.to_Boolean( p_commit )
983 THEN
984 COMMIT WORK;
985 END IF;
986
987
988
989 -- Standard call to get message count and if count is 1, get message info.
990 FND_MSG_PUB.Count_And_Get
991 ( p_count => x_msg_count,
992 p_data => x_msg_data
993 );
994
995 EXCEPTION
996 WHEN FND_API.G_EXC_ERROR THEN
997 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
998 P_API_NAME => L_API_NAME
999 ,P_PKG_NAME => G_PKG_NAME
1000 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1001 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1002 ,P_ROLLBACK_FLAG => 'N'
1003 ,X_MSG_COUNT => X_MSG_COUNT
1004 ,X_MSG_DATA => X_MSG_DATA
1005 ,X_RETURN_STATUS => X_RETURN_STATUS);
1006
1007 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1008 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1009 P_API_NAME => L_API_NAME
1010 ,P_PKG_NAME => G_PKG_NAME
1011 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1012 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1013 ,P_ROLLBACK_FLAG => 'N'
1014 ,X_MSG_COUNT => X_MSG_COUNT
1015 ,X_MSG_DATA => X_MSG_DATA
1016 ,X_RETURN_STATUS => X_RETURN_STATUS);
1017
1018 WHEN OTHERS THEN
1019 l_Sqlcode := SQLCODE;
1020 l_sqlerrm := SQLERRM;
1021 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1022 P_API_NAME => L_API_NAME
1023 ,P_PKG_NAME => G_PKG_NAME
1024 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1025 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1026 ,P_ROLLBACK_FLAG => 'N'
1027 ,P_SQLCODE => l_Sqlcode
1028 ,P_SQLERRM => l_Sqlerrm
1029 ,X_MSG_COUNT => X_MSG_COUNT
1030 ,X_MSG_DATA => X_MSG_DATA
1031 ,X_RETURN_STATUS => X_RETURN_STATUS);
1032 End Purge_Planning_Data;
1033
1034 PROCEDURE Create_Usage (
1035 retcode OUT NOCOPY NUMBER,
1036 errbuf OUT NOCOPY VARCHAR2,
1037 P_Api_Version_Number IN NUMBER
1038 ) IS
1039 l_api_name CONSTANT VARCHAR2(30) := 'Create_Usage';
1040 l_api_version_number CONSTANT NUMBER := 1.0;
1041 l_return_status_full VARCHAR2(1);
1042 l_Sqlcode NUMBER;
1043 l_Sqlerrm Varchar2(2000);
1044 l_stmt Varchar2(2000);
1045
1046
1047 l_Msg_Count NUMBER;
1048 l_Msg_Data Varchar2(2000);
1049
1050 X_Return_Status VARCHAR2(1);
1051 X_Msg_Count NUMBER;
1052 X_Msg_Data VARCHAR2(2000);
1053
1054 l_Init_Msg_List VARCHAR2(1) := FND_API.G_TRUE;
1055 l_Commit VARCHAR2(1) := FND_API.G_TRUE;
1056 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
1057
1058 l_get_app_info boolean;
1059 l_status varchar2(1);
1060 l_industry varchar2(1);
1061 l_oracle_schema varchar2(30);
1062 BEGIN
1063 -- Alter session
1064 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
1065 -- Standard call to check for call compatibility.
1066 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1067 p_api_version_number,
1068 l_api_name,
1069 G_PKG_NAME)
1070 THEN
1071 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1072 END IF;
1073
1074
1075 -- Initialize message list if p_init_msg_list is set to TRUE.
1076 IF FND_API.to_Boolean( l_init_msg_list )
1077 THEN
1078 FND_MSG_PUB.initialize;
1079 END IF;
1080
1081
1082
1083 -- Initialize API return status to SUCCESS
1084 x_return_status := FND_API.G_RET_STS_SUCCESS;
1085
1086 --
1087 -- API body
1088 --
1089
1090 --G_LAST_RUN_DATE := fnd_profile.Value('CSP_USAGE_RUN_DATE');
1091 Begin
1092 select PROFILE_OPTION_VALUE
1093 into G_LAST_RUN_DATE
1094 from fnd_profile_option_values
1095 where APPLICATION_ID = 523
1096 and PROFILE_OPTION_ID in
1097 (select profile_option_id
1098 from fnd_profile_options
1099 where profile_option_name = ('CSP_USAGE_RUN_DATE'));
1100 Exception
1101 When others then
1102 G_LAST_RUN_DATE := NULL;
1103 End;
1104
1105 G_LAST_RUN_DATE := NVL(G_LAST_RUN_DATE,FND_API.G_MISS_DATE);
1106 If G_LAST_RUN_DATE < trunc(sysdate) - 1 Then
1107 l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
1108 -- Delete from CSP_SUPPLY_CHAIN
1109 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
1110 -- Standard Start of API savepoint
1111 SAVEPOINT CREATE_USAGE_PVT;
1112 -- Build Supply Chain
1113 Create_Supply_Chain (
1114 P_Api_Version_Number => 1.0,
1115 P_Init_Msg_List => FND_API.G_FALSE,
1116 P_Commit => FND_API.G_TRUE,
1117 P_validation_level => FND_API.G_VALID_LEVEL_FULL,
1118 X_Return_Status => X_Return_Status,
1119 X_Msg_Count => X_Msg_Count,
1120 X_Msg_Data => X_Msg_Data );
1121 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
1122 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1123 End If;
1124
1125 -- Re-establish Savepoint
1126 SAVEPOINT CREATE_USAGE_PVT;
1127
1128 --- Get Usage
1129 Create_Usage_History (
1130 P_Api_Version_Number => 1.0,
1131 P_Init_Msg_List => FND_API.G_FALSE,
1132 P_Commit => FND_API.G_TRUE,
1133 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1134 X_Return_Status => X_Return_Status,
1135 X_Msg_Count => X_Msg_Count,
1136 X_Msg_Data => X_Msg_Data
1137 );
1138 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
1139 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1140 End If;
1141 -- Re-establish Savepoint
1142 SAVEPOINT CREATE_USAGE_PVT;
1143 if not fnd_profile.save('CSP_USAGE_RUN_DATE',trunc(sysdate) -1,'SITE') Then
1144 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1145 End If;
1146 COMMIT;
1147 -- Re-establish Savepoint
1148 SAVEPOINT CREATE_USAGE_PVT;
1149 --- Refresh Organization rollup snapshot
1150 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_ORGS';
1151
1152 insert into csp_usage_orgs(
1153 organization_id,
1154 inventory_item_id,
1155 period_start_date,
1156 quantity)
1157 select cuh.organization_id, cuh.inventory_item_id,
1158 cuh.period_start_date,
1159 sum(cuh.quantity)
1160 from csp_usage_histories cuh ,
1161 csp_planning_parameters cpp
1162 where cuh.History_Data_type = 1
1163 and cpp.organization_id = cuh.organization_id
1164 and cpp.secondary_inventory is null
1165 and cpp.organization_type = 'W'
1166 and cuh.transaction_type_id in
1167 (select transaction_type_id
1168 from csp_usg_transaction_types cutt
1169 where cutt.forecast_rule_id = cpp.forecast_rule_id)
1170 group by cuh.organization_id,
1171 cuh.inventory_item_id,
1172 cuh.period_start_date;
1173
1174 --- Refresh other rollup snapshot
1175 Create_Usage_Rollup ( P_Api_Version_Number => 1.0,
1176 retcode => retcode,
1177 errbuf => errbuf);
1178 End If;
1179
1180
1181 --
1182 -- End of API body
1183 --
1184 -- Standard call to get message count and if count is 1, get message info.
1185 FND_MSG_PUB.Count_And_Get
1186 ( p_count => x_msg_count,
1187 p_data => x_msg_data
1188 );
1189 retcode := 0;
1190 EXCEPTION
1191 WHEN FND_API.G_EXC_ERROR THEN
1192 retcode := 2;
1193 errbuf := X_Msg_Data;
1194 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1195 P_API_NAME => L_API_NAME
1196 ,P_PKG_NAME => G_PKG_NAME
1197 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1198 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1199 ,P_ROLLBACK_FLAG => 'Y'
1200 ,X_MSG_COUNT => X_MSG_COUNT
1201 ,X_MSG_DATA => X_MSG_DATA
1202 ,X_RETURN_STATUS => X_RETURN_STATUS);
1203 Add_Err_Msg;
1204 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1205 retcode := 2;
1206 errbuf := X_Msg_Data;
1207 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1208 P_API_NAME => L_API_NAME
1209 ,P_PKG_NAME => G_PKG_NAME
1210 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1211 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1212 ,P_ROLLBACK_FLAG => 'Y'
1213 ,X_MSG_COUNT => X_MSG_COUNT
1214 ,X_MSG_DATA => X_MSG_DATA
1215 ,X_RETURN_STATUS => X_RETURN_STATUS);
1216
1217 Add_Err_Msg;
1218 WHEN OTHERS THEN
1219 l_Sqlcode := SQLCODE;
1220 l_Sqlerrm := SQLERRM;
1221 retcode := 2;
1222 errbuf := SQLERRM;
1223 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1224 P_API_NAME => L_API_NAME
1225 ,P_PKG_NAME => G_PKG_NAME
1226 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1227 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1228 ,P_ROLLBACK_FLAG => 'Y'
1229 ,P_SQLCODE => l_Sqlcode
1230 ,P_SQLERRM => l_Sqlerrm
1231 ,X_MSG_COUNT => X_MSG_COUNT
1232 ,X_MSG_DATA => X_MSG_DATA
1233 ,X_RETURN_STATUS => X_RETURN_STATUS);
1234 Add_Err_Msg;
1235 END Create_Usage;
1236
1237 PROCEDURE Calculate_Forecast (
1238 P_Api_Version_Number IN NUMBER,
1239 P_Init_Msg_List IN VARCHAR2,
1240 P_Commit IN VARCHAR2 ,
1241 P_validation_level IN NUMBER ,
1242 P_Level_Id IN VARCHAR2,
1243 P_Reason_Code IN VARCHAR2,
1244 X_Return_Status OUT NOCOPY VARCHAR2,
1245 X_Msg_Count OUT NOCOPY NUMBER,
1246 X_Msg_Data OUT NOCOPY VARCHAR2
1247 )
1248 IS
1249
1250
1251 l_ts_cycle NUMBER := 0;
1252 l_max_ts_cycle NUMBER := 0;
1253
1254 Cursor c_Usage_Details IS
1255 Select nvl(csi.item_supplied,cuh.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,
1256 cuh.ORGANIZATION_ID,
1257 cuh.SUBINVENTORY_CODE,
1258 (trunc(sysdate)- cfrb.forecast_periods * cfrb.period_size * l_ts_cycle)
1259 - ROUND((TO_NUMBER((trunc(sysdate)- cfrb.forecast_periods* cfrb.period_size) -
1260 cuh.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size PERIOD_START_DATE,
1261 sum(cuh.QUANTITY) QUANTITY,
1262 5 HISTORY_DATA_TYPE,
1263 cfrb.HISTORY_PERIODS,
1264 cfrb.forecast_rule_id,
1265 DECODE(cfrb.FORECAST_METHOD,4,cfrb.FORECAST_PERIODS,1) FORECAST_PERIODS ,
1266 cfrb.FORECAST_METHOD,
1267 cfrb.PERIOD_SIZE,
1268 cfrb.ALPHA,
1269 cfrb.BETA,
1270 cfrb.WEIGHTED_AVG_PERIOD1,
1271 cfrb.WEIGHTED_AVG_PERIOD2,
1272 cfrb.WEIGHTED_AVG_PERIOD3,
1273 cfrb.WEIGHTED_AVG_PERIOD4,
1274 cfrb.WEIGHTED_AVG_PERIOD5,
1275 cfrb.WEIGHTED_AVG_PERIOD6,
1276 cfrb.WEIGHTED_AVG_PERIOD7,
1277 cfrb.WEIGHTED_AVG_PERIOD8,
1278 cfrb.WEIGHTED_AVG_PERIOD9,
1279 cfrb.WEIGHTED_AVG_PERIOD10,
1280 cfrb.WEIGHTED_AVG_PERIOD11,
1281 cfrb.WEIGHTED_AVG_PERIOD12,
1282 cpp.RECOMMEND_METHOD
1283 From CSP_PLANNING_PARAMETERS cpp,
1284 CSP_USAGE_HISTORIES cuh,
1285 CSP_SUPERSEDE_ITEMS csi,
1286 CSP_FORECAST_RULES_B cfrb
1287 Where cpp.level_id like P_Level_Id || '%'
1288 And cpp.node_type = 'SUBINVENTORY'
1289 And cpp.recommend_method in ('USAGE','USAGE_PNORM','USAGE_TNORM','PNORM','TNORM')
1290 And cuh.organization_id = cpp.organization_id
1291 And cuh.subinventory_code = cpp.secondary_inventory
1292 And cuh.HISTORY_DATA_TYPE = 1
1293 And (cuh.PERIOD_START_DATE BETWEEN (trunc(sysdate) - cfrb.forecast_periods
1294 * cfrb.period_size * l_ts_cycle) - cfrb.period_size * cfrb.history_periods - (cfrb.period_size - 1)
1295 AND (trunc(sysdate) - cfrb.forecast_periods * period_size * l_ts_cycle))
1296 And cuh.transaction_type_id in (select transaction_type_id
1297 from csp_usg_transaction_types cutt
1298 where cutt.forecast_rule_id = cpp.forecast_rule_id)
1299 And csi.inventory_item_id(+) = cuh.inventory_item_id
1300 And csi.organization_id (+) = cuh.organization_id
1301 And csi.sub_inventory_code(+) = cuh.subinventory_code
1302 AND cfrb.FORECAST_RULE_ID = cpp.FORECAST_RULE_ID
1303 AND cfrb.tracking_signal_cycle >= l_ts_cycle
1304 Group By nvl(csi.item_supplied,cuh.INVENTORY_ITEM_ID) ,
1305 cuh.ORGANIZATION_ID,
1306 cuh.SUBINVENTORY_CODE,
1307 (trunc(sysdate)- cfrb.forecast_periods * cfrb.period_size * l_ts_cycle)
1308 - ROUND((TO_NUMBER((trunc(sysdate)- cfrb.forecast_periods* cfrb.period_size) -
1309 cuh.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size,
1310 cfrb.HISTORY_PERIODS,
1311 cfrb.forecast_rule_id,
1312 DECODE(cfrb.FORECAST_METHOD,4,cfrb.FORECAST_PERIODS,1) ,
1313 cfrb.FORECAST_METHOD,
1314 cfrb.PERIOD_SIZE,
1315 cfrb.ALPHA,
1316 cfrb.BETA,
1317 cfrb.WEIGHTED_AVG_PERIOD1,
1318 cfrb.WEIGHTED_AVG_PERIOD2,
1319 cfrb.WEIGHTED_AVG_PERIOD3,
1320 cfrb.WEIGHTED_AVG_PERIOD4,
1321 cfrb.WEIGHTED_AVG_PERIOD5,
1322 cfrb.WEIGHTED_AVG_PERIOD6,
1323 cfrb.WEIGHTED_AVG_PERIOD7,
1324 cfrb.WEIGHTED_AVG_PERIOD8,
1325 cfrb.WEIGHTED_AVG_PERIOD9,
1326 cfrb.WEIGHTED_AVG_PERIOD10,
1327 cfrb.WEIGHTED_AVG_PERIOD11,
1328 cfrb.WEIGHTED_AVG_PERIOD12,
1329 cpp.RECOMMEND_METHOD
1330 UNION ALL
1331 Select nvl(csi.item_supplied,cuom.INVENTORY_ITEM_ID),
1332 cuom.ORGANIZATION_ID,
1333 '-' SUBINVENTORY_CODE ,
1334 (trunc(sysdate)- cfrb.forecast_periods * cfrb.period_size * l_ts_cycle)
1335 - ROUND((TO_NUMBER((trunc(sysdate)- cfrb.forecast_periods* cfrb.period_size) -
1336 cuom.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size PERIOD_START_DATE,
1337 sum(cuom.QUANTITY) QUANTITY,
1338 6 HISTORY_DATA_TYPE,
1339 cfrb.HISTORY_PERIODS,
1340 cfrb.forecast_rule_id,
1341 DECODE(cfrb.FORECAST_METHOD,4,cfrb.FORECAST_PERIODS,1) FORECAST_PERIODS ,
1342 cfrb.FORECAST_METHOD,
1343 cfrb.PERIOD_SIZE,
1344 cfrb.ALPHA,
1345 cfrb.BETA,
1346 cfrb.WEIGHTED_AVG_PERIOD1,
1347 cfrb.WEIGHTED_AVG_PERIOD2,
1348 cfrb.WEIGHTED_AVG_PERIOD3,
1349 cfrb.WEIGHTED_AVG_PERIOD4,
1350 cfrb.WEIGHTED_AVG_PERIOD5,
1351 cfrb.WEIGHTED_AVG_PERIOD6,
1352 cfrb.WEIGHTED_AVG_PERIOD7,
1353 cfrb.WEIGHTED_AVG_PERIOD8,
1354 cfrb.WEIGHTED_AVG_PERIOD9,
1355 cfrb.WEIGHTED_AVG_PERIOD10,
1356 cfrb.WEIGHTED_AVG_PERIOD11,
1357 cfrb.WEIGHTED_AVG_PERIOD12,
1358 cpp.RECOMMEND_METHOD
1359 From CSP_PLANNING_PARAMETERS cpp,
1360 CSP_USAGE_ORGS cuom,
1361 CSP_SUPERSEDE_ITEMS csi,
1362 CSP_FORECAST_RULES_B cfrb
1363 Where cpp.level_id like P_Level_Id || '%'
1364 And cpp.node_type = 'ORGANIZATION_WH'
1365 And cpp.recommend_method in ('USAGE','USAGE_PNORM','USAGE_TNORM','PNORM','TNORM')
1366 And (cuom.PERIOD_START_DATE BETWEEN (trunc(sysdate) - cfrb.forecast_periods
1367 * cfrb.period_size * l_ts_cycle) - cfrb.period_size * cfrb.history_periods - (cfrb.period_size - 1)
1368 AND (trunc(sysdate) - cfrb.forecast_periods * period_size * l_ts_cycle))
1369 And cuom.ORGANIZATION_ID = cpp.ORGANIZATION_ID
1370 And csi.inventory_item_id(+) = cuom.inventory_item_id
1371 And csi.organization_id (+) = cuom.organization_id
1372 And csi.sub_inventory_code(+) = '-'
1373 AND cfrb.FORECAST_RULE_ID = cpp.FORECAST_RULE_ID
1374 Group By nvl(csi.item_supplied,cuom.INVENTORY_ITEM_ID),
1375 cuom.ORGANIZATION_ID,
1376 (trunc(sysdate)- cfrb.forecast_periods * cfrb.period_size * l_ts_cycle)
1377 - ROUND((TO_NUMBER((trunc(sysdate)- cfrb.forecast_periods* cfrb.period_size) -
1378 cuom.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size,
1379 cfrb.HISTORY_PERIODS,
1380 cfrb.forecast_rule_id,
1381 DECODE(cfrb.FORECAST_METHOD,4,cfrb.FORECAST_PERIODS,1) ,
1382 cfrb.FORECAST_METHOD,
1383 cfrb.PERIOD_SIZE,
1384 cfrb.ALPHA,
1385 cfrb.BETA,
1386 cfrb.WEIGHTED_AVG_PERIOD1,
1387 cfrb.WEIGHTED_AVG_PERIOD2,
1388 cfrb.WEIGHTED_AVG_PERIOD3,
1389 cfrb.WEIGHTED_AVG_PERIOD4,
1390 cfrb.WEIGHTED_AVG_PERIOD5,
1391 cfrb.WEIGHTED_AVG_PERIOD6,
1392 cfrb.WEIGHTED_AVG_PERIOD7,
1393 cfrb.WEIGHTED_AVG_PERIOD8,
1394 cfrb.WEIGHTED_AVG_PERIOD9,
1395 cfrb.WEIGHTED_AVG_PERIOD10,
1396 cfrb.WEIGHTED_AVG_PERIOD11,
1397 cfrb.WEIGHTED_AVG_PERIOD12,
1398 cpp.RECOMMEND_METHOD
1399 Order By 1,2,3,4;
1400
1401 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_Forecast';
1402 l_api_version_number CONSTANT NUMBER := 1.0;
1403 l_return_status_full VARCHAR2(1);
1404 l_Sqlcode NUMBER;
1405 l_sqlerrm Varchar2(2000);
1406 l_Start_Date DATE;
1407
1408 l_usage_Details_Rec c_Usage_Details%ROWTYPE;
1409 l_prev_Rec c_Usage_Details%ROWTYPE;
1410 l_Dates_Table CSP_AUTO_ASLMSL_PVT.CSP_Date_Tbl_Type;
1411 l_Usage_Qty_Tbl Csp_Forecast_Pvt.T_NUMBER_TABLE;
1412 l_Forecast_Qty_Tbl Csp_Forecast_Pvt.T_NUMBER_TABLE;
1413 l_Weighted_Avg_Tbl Csp_Forecast_Pvt.T_NUMBER_TABLE;
1414
1415 l_Usage_Quantity NUMBER := 0;
1416 l_Forecast_Quantity NUMBER := 0;
1417 l_Usage_Data_Count NUMBER := 0;
1418 l_Fcst_Period_Count NUMBER := 0;
1419 l_Usage_Qty_Sum NUMBER := 0;
1420 l_weeks NUMBER := 0;
1421 l_Awu NUMBER := 0;
1422 l_Variance NUMBER := 0;
1423 l_Standard_Deviation NUMBER := 0;
1424
1425 l_Usage_Id Number;
1426 l_index Number := 0;
1427 l_count Number := 0;
1428 l_i Number;
1429
1430 l_Msg_Count NUMBER;
1431 l_Msg_Data Varchar2(2000);
1432
1433 BEGIN
1434 -- Standard Start of API savepoint
1435 SAVEPOINT CALCULATE_FORECAST_PVT;
1436
1437 -- Standard call to check for call compatibility.
1438 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1439 p_api_version_number,
1440 l_api_name,
1441 G_PKG_NAME)
1442 THEN
1443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1444 END IF;
1445
1446
1447 -- Initialize message list if p_init_msg_list is set to TRUE.
1448 IF FND_API.to_Boolean( p_init_msg_list )
1449 THEN
1450 FND_MSG_PUB.initialize;
1451 END IF;
1452
1453
1454 -- Initialize API return status to SUCCESS
1455 x_return_status := FND_API.G_RET_STS_SUCCESS;
1456
1457 --
1458 -- API body
1459 --
1460 IF p_reason_code = 'TS' Then
1461 SELECT nvl(max(tracking_signal_cycle),0)
1462 INTO l_max_ts_cycle
1463 FROM csp_forecast_rules_b;
1464 l_ts_cycle := 1;
1465 Else
1466 l_ts_cycle := 0;
1467 l_max_ts_cycle := 0;
1468 END IF;
1469 WHILE l_ts_cycle <= l_max_ts_cycle LOOP
1470 Open c_Usage_Details;
1471 LOOP
1472 Fetch c_Usage_Details INTO l_Usage_Details_Rec;
1473 If (c_Usage_Details%ROWCOUNT > 1 AND
1474 (((l_Usage_Details_rec.Inventory_Item_Id <> l_prev_rec.inventory_Item_Id)OR
1475 (l_Usage_Details_rec.Organization_id <> l_prev_rec.Organization_id) OR
1476 (l_Usage_Details_rec.Subinventory_Code <>
1477 l_prev_rec.Subinventory_code)))) OR (c_Usage_Details%NOTFOUND OR c_Usage_Details%NOTFOUND IS NULL ) Then
1478 For l_i in l_Usage_Qty_Tbl.COUNT..nvl(l_prev_rec.History_Periods,0) Loop
1479 l_Usage_Qty_Tbl(l_i) := 0;
1480 End Loop;
1481 If l_prev_rec.Forecast_Method = 1 Then
1482 CSP_Forecast_Pvt.Simple_Average
1483 (P_Usage_History => l_Usage_Qty_Tbl,
1484 P_History_Periods => l_prev_rec.History_Periods,
1485 P_Forecast_Periods => l_prev_rec.Forecast_Periods,
1486 X_Forecast_Quantities => l_Forecast_Qty_Tbl);
1487 Elsif l_prev_rec.Forecast_Method = 2 Then
1488 l_Weighted_Avg_Tbl(1) := NVL(l_Prev_Rec.Weighted_Avg_Period1,0);
1489 l_Weighted_Avg_Tbl(2) := NVL(l_Prev_Rec.Weighted_Avg_Period2,0);
1490 l_Weighted_Avg_Tbl(3) := NVL(l_Prev_Rec.Weighted_Avg_Period3,0);
1491 l_Weighted_Avg_Tbl(4) := NVL(l_Prev_Rec.Weighted_Avg_Period4,0);
1492 l_Weighted_Avg_Tbl(5) := NVL(l_Prev_Rec.Weighted_Avg_Period5,0);
1493 l_Weighted_Avg_Tbl(6) := NVL(l_Prev_Rec.Weighted_Avg_Period6,0);
1494 l_Weighted_Avg_Tbl(7) := NVL(l_Prev_Rec.Weighted_Avg_Period7,0);
1495 l_Weighted_Avg_Tbl(8) := NVL(l_Prev_Rec.Weighted_Avg_Period8,0);
1496 l_Weighted_Avg_Tbl(9) := NVL(l_Prev_Rec.Weighted_Avg_Period9,0);
1497 l_Weighted_Avg_Tbl(10) := NVL(l_Prev_Rec.Weighted_Avg_Period10,0);
1498 l_Weighted_Avg_Tbl(11) := NVL(l_Prev_Rec.Weighted_Avg_Period11,0);
1499 l_Weighted_Avg_Tbl(12) := NVL(l_Prev_Rec.Weighted_Avg_Period12,0);
1500 For l_i in 13..l_Prev_Rec.History_Periods
1501 Loop
1502
1503 l_Weighted_Avg_Tbl(l_i) := 0;
1504 End Loop;
1505 CSP_Forecast_Pvt.Weighted_Average
1506 (P_Usage_History =>l_Usage_Qty_Tbl,
1507 P_History_Periods =>l_Prev_Rec.History_Periods,
1508 P_Forecast_Periods =>l_Prev_Rec.Forecast_Periods,
1509 P_Weighted_Avg =>l_Weighted_Avg_Tbl,
1510 X_Forecast_Quantities =>l_Forecast_Qty_Tbl);
1511 Elsif l_Prev_Rec.Forecast_Method = 3 Then
1512 CSP_Forecast_Pvt.Exponential_Smoothing
1513 (P_Usage_History =>l_Usage_Qty_Tbl,
1514 P_History_Periods =>l_Prev_Rec.History_Periods,
1515 P_Forecast_Periods => l_Prev_Rec.Forecast_Periods,
1516 P_Alpha =>l_Prev_Rec.Alpha,
1517 X_Forecast_Quantities =>l_Forecast_Qty_Tbl);
1518
1519 Elsif l_prev_rec.Forecast_Method = 4 Then
1520 CSP_Forecast_Pvt.Trend_Enhanced
1521 (P_Usage_History =>l_Usage_Qty_Tbl,
1522 P_History_Periods =>l_Prev_Rec.History_Periods,
1523 P_Forecast_Periods =>l_Prev_Rec.Forecast_Periods,
1524 P_Alpha =>l_Prev_Rec.Alpha,
1525 P_Beta =>l_Prev_Rec.Beta,
1526 X_Forecast_Quantities =>l_Forecast_Qty_Tbl);
1527
1528 End If;
1529 FOR l_Index in 1..l_Forecast_Qty_Tbl.COUNT LOOP
1530 INSERT INTO CSP_USAGE_HISTORIES (Usage_Id,
1531 created_by,
1532 creation_date,
1533 last_updated_by,
1534 last_update_date,
1535 inventory_item_id,
1536 organization_id,
1537 subinventory_code,
1538 period_type,
1539 period_start_date,
1540 quantity,
1541 history_data_type)
1542 VALUES ( csp_usage_histories_s1.nextval,
1543 fnd_global.user_id, sysdate,
1544 fnd_global.user_id,sysdate,
1545 l_Prev_Rec.Inventory_Item_id,
1546 l_Prev_Rec.Organization_id,
1547 l_Prev_Rec.Subinventory_code,
1548 3,
1549 (trunc(sysdate) - l_prev_rec.forecast_periods * l_prev_rec.period_size * l_ts_cycle) +
1550 (l_prev_rec.period_size * (l_Index -1)),
1551 ROUND(decode(sign(l_Forecast_Qty_Tbl(l_Index)),-1,0,l_Forecast_Qty_Tbl(l_Index)),4),
1552 decode(p_reason_code,'RECM',2,'TS',5));
1553 If l_Forecast_Qty_Tbl(l_Index) > 0 Then
1554 l_Forecast_Quantity := l_Forecast_Quantity + ROUND(l_Forecast_Qty_Tbl(l_Index),4);
1555 End If;
1556 END LOOP;
1557 If l_prev_rec.recommend_method not in ('PNORM','TNORM') AND (p_reason_code = 'RECM') Then
1558 l_Usage_Data_Count := l_Usage_Qty_Tbl.COUNT;
1559 l_weeks := (l_Prev_Rec.Period_Size * l_Prev_Rec.Forecast_Periods)/7;
1560 l_Awu := ROUND((l_Forecast_Quantity/l_weeks),4);
1561
1562 -- Calculate Standard Deviation
1563 l_Variance := 0;
1564 If l_Usage_Data_Count > 1 Then
1565 l_Variance :=
1566 (l_Usage_Qty_Sum - ((l_Usage_Quantity * l_Usage_Quantity) /l_Usage_Data_Count))/(l_Usage_Data_Count - 1);
1567 End If;
1568 l_Standard_Deviation := ROUND(SQRT(NVL(l_Variance,0)),4);
1569 INSERT INTO CSP_USAGE_HEADERS
1570 (USAGE_HEADER_ID,
1571 INVENTORY_ITEM_ID,
1572 ORGANIZATION_ID,
1573 SECONDARY_INVENTORY,
1574 HEADER_DATA_TYPE,
1575 RAW_AWU,
1576 AWU,
1577 STANDARD_DEVIATION,
1578 LEAD_TIME,
1579 PROCESS_STATUS,
1580 EXTERNAL_DATA,
1581 COMMENTS,
1582 ITEM_COST,
1583 CREATION_DATE,
1584 CREATED_BY,
1585 LAST_UPDATE_DATE,
1586 LAST_UPDATED_BY,
1587 LAST_UPDATE_LOGIN)
1588 VALUES (NULL,
1589 l_prev_Rec.inventory_Item_Id,
1590 l_prev_rec.Organization_Id,
1591 l_prev_rec.Subinventory_code,
1592 l_prev_rec.History_Data_Type,
1593 NULL,
1594 l_Awu,
1595 l_Standard_Deviation,
1596 NULL,
1597 'O',
1598 'N',
1599 NULL,
1600 NULL,
1601 SYSDATE,
1602 fnd_global.user_id,
1603 SYSDATE,
1604 fnd_global.user_id,
1605 fnd_global.conc_login_id);
1606 End If;
1607 l_count := 0;
1608 l_Usage_Quantity := 0;
1609 l_Forecast_Quantity := 0;
1610 l_Usage_Data_Count := 0;
1611 l_Fcst_Period_Count := 0;
1612 l_Usage_Qty_Sum := 0;
1613 l_weeks := 0;
1614 l_Awu := 0;
1615 l_Variance := 0;
1616 l_Standard_Deviation := 0;
1617 l_Usage_Qty_Tbl.Delete;
1618 l_Weighted_Avg_Tbl.Delete;
1619 END IF;
1620 l_Usage_Qty_Tbl(l_count) := l_Usage_Details_Rec.Quantity;
1621 l_count := l_count + 1;
1622 l_prev_rec := l_Usage_Details_Rec;
1623 l_Usage_Quantity := l_Usage_Quantity + l_Usage_Details_Rec.Quantity;
1624 l_Usage_Qty_sum := l_Usage_Qty_Sum + (l_Usage_Details_Rec.Quantity * l_Usage_Details_Rec.Quantity);
1625 EXIT WHEN c_Usage_Details%NOTFOUND;
1626 END LOOP;
1627 close c_usage_details;
1628 l_ts_cycle := l_ts_cycle + 1;
1629 END LOOP; --- While clause
1630
1631
1632 --
1633 -- End of API body
1634 --
1635
1636 -- Standard check for p_commit
1637 IF FND_API.to_Boolean( p_commit )
1638 THEN
1639 COMMIT WORK;
1640 END IF;
1641
1642
1643 -- Standard call to get message count and if count is 1, get message info.
1644 FND_MSG_PUB.Count_And_Get
1645 ( p_count => x_msg_count,
1646 p_data => x_msg_data
1647 );
1648
1649 EXCEPTION
1650 WHEN FND_API.G_EXC_ERROR THEN
1651 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1652 P_API_NAME => L_API_NAME
1653 ,P_PKG_NAME => G_PKG_NAME
1654 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1655 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1656 ,P_ROLLBACK_FLAG => 'N'
1657 ,X_MSG_COUNT => X_MSG_COUNT
1658 ,X_MSG_DATA => X_MSG_DATA
1659 ,X_RETURN_STATUS => X_RETURN_STATUS);
1660
1661 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1662 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1663 P_API_NAME => L_API_NAME
1664 ,P_PKG_NAME => G_PKG_NAME
1665 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1666 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1667 ,P_ROLLBACK_FLAG => 'N'
1668 ,X_MSG_COUNT => X_MSG_COUNT
1669 ,X_MSG_DATA => X_MSG_DATA
1670 ,X_RETURN_STATUS => X_RETURN_STATUS);
1671
1672 WHEN OTHERS THEN
1673 l_sqlcode := SQLCODE;
1674 l_sqlerrm := SQLERRM;
1675 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1676 P_API_NAME => L_API_NAME
1677 ,P_PKG_NAME => G_PKG_NAME
1678 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1679 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1680 ,P_SQLCODE => l_sqlcode
1681 ,P_SQLERRM => l_sqlerrm
1682 ,P_ROLLBACK_FLAG => 'N'
1683 ,X_MSG_COUNT => X_MSG_COUNT
1684 ,X_MSG_DATA => X_MSG_DATA
1685 ,X_RETURN_STATUS => X_RETURN_STATUS);
1686 END Calculate_Forecast;
1687
1688 PROCEDURE Calculate_Product_Norm (
1689 P_Api_Version_Number IN NUMBER,
1690 P_Init_Msg_List IN VARCHAR2,
1691 P_Commit IN VARCHAR2,
1692 P_validation_level IN NUMBER,
1693 P_Level_Id IN VARCHAR2,
1694 X_Return_Status OUT NOCOPY VARCHAR2,
1695 X_Msg_Count OUT NOCOPY NUMBER,
1696 X_Msg_Data OUT NOCOPY VARCHAR2
1697 )
1698 IS
1699
1700 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_PNorm';
1701 l_api_version_number CONSTANT NUMBER := 1.0;
1702 l_return_status_full VARCHAR2(1);
1703 l_Sqlcode NUMBER;
1704 l_sqlerrm Varchar2(2000);
1705
1706 l_Msg_Count NUMBER;
1707 l_Msg_Data Varchar2(2000);
1708
1709 BEGIN
1710 -- Standard Start of API savepoint
1711 SAVEPOINT CALCULATE_PNORM_PVT;
1712
1713 -- Standard call to check for call compatibility.
1714 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1715 p_api_version_number,
1716 l_api_name,
1717 G_PKG_NAME)
1718 THEN
1719 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1720 END IF;
1721
1722
1723 -- Initialize message list if p_init_msg_list is set to TRUE.
1724 IF FND_API.to_Boolean( p_init_msg_list )
1725 THEN
1726 FND_MSG_PUB.initialize;
1727 END IF;
1728
1729 INSERT INTO CSP_USAGE_HEADERS
1730 (USAGE_HEADER_ID,
1731 INVENTORY_ITEM_ID,
1732 ORGANIZATION_ID,
1733 SECONDARY_INVENTORY,
1734 HEADER_DATA_TYPE,
1735 RAW_AWU,
1736 AWU,
1737 PROCESS_STATUS,
1738 EXTERNAL_DATA,
1739 CREATION_DATE,
1740 CREATED_BY,
1741 LAST_UPDATE_DATE,
1742 LAST_UPDATED_BY,
1743 LAST_UPDATE_LOGIN)
1744 select NULL,
1745 nvl(csi.item_supplied,cppf.inventory_item_id),
1746 cppf.organization_id,
1747 nvl(cppf.secondary_inventory,'-'),
1748 7, -- Product Norm Usage
1749 NULL,
1750 cppf.current_population *
1751 nvl(manual_failure_rate,calculated_failure_rate),
1752 'O',
1753 'N',
1754 sysdate,
1755 fnd_global.user_id,
1756 sysdate,
1757 fnd_global.user_id,
1758 fnd_global.conc_login_id
1759 from csp_product_populations_fr_v cppf,
1760 csp_supersede_items csi
1761 where cppf.level_id like p_level_id || '%'
1762 and cppf.node_type in ('ORGANIZATION_WH','SUBINVENTORY')
1763 and csi.inventory_item_id(+) = cppf.inventory_item_id
1764 and csi.organization_id (+) = cppf.organization_id
1765 and csi.sub_inventory_code (+) = cppf.secondary_inventory;
1766
1767
1768 -- Standard check for p_commit
1769 IF FND_API.to_Boolean( p_commit )
1770 THEN
1771 COMMIT WORK;
1772 END IF;
1773
1774
1775 -- Standard call to get message count and if count is 1, get message info.
1776 FND_MSG_PUB.Count_And_Get
1777 ( p_count => x_msg_count,
1778 p_data => x_msg_data
1779 );
1780
1781 EXCEPTION
1782 WHEN FND_API.G_EXC_ERROR THEN
1783 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1784 P_API_NAME => L_API_NAME
1785 ,P_PKG_NAME => G_PKG_NAME
1786 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1787 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1788 ,P_ROLLBACK_FLAG => 'N'
1789 ,X_MSG_COUNT => X_MSG_COUNT
1790 ,X_MSG_DATA => X_MSG_DATA
1791 ,X_RETURN_STATUS => X_RETURN_STATUS);
1792
1793 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1794 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1795 P_API_NAME => L_API_NAME
1796 ,P_PKG_NAME => G_PKG_NAME
1797 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1798 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1799 ,P_ROLLBACK_FLAG => 'N'
1800 ,X_MSG_COUNT => X_MSG_COUNT
1801 ,X_MSG_DATA => X_MSG_DATA
1802 ,X_RETURN_STATUS => X_RETURN_STATUS);
1803
1804 WHEN OTHERS THEN
1805 l_sqlcode := SQLCODE;
1806 l_sqlerrm := SQLERRM;
1807 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1808 P_API_NAME => L_API_NAME
1809 ,P_PKG_NAME => G_PKG_NAME
1810 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1811 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1812 ,P_SQLCODE => l_sqlcode
1813 ,P_SQLERRM => l_sqlerrm
1814 ,P_ROLLBACK_FLAG => 'N'
1815 ,X_MSG_COUNT => X_MSG_COUNT
1816 ,X_MSG_DATA => X_MSG_DATA
1817 ,X_RETURN_STATUS => X_RETURN_STATUS);
1818 End Calculate_Product_Norm;
1819
1820 PROCEDURE calculate_new_product_planning (
1821 P_Api_Version_Number IN NUMBER,
1822 P_Init_Msg_List IN VARCHAR2,
1823 P_Commit IN VARCHAR2,
1824 P_validation_level IN NUMBER ,
1825 P_Level_Id IN VARCHAR2,
1826 X_Return_Status OUT NOCOPY VARCHAR2,
1827 X_Msg_Count OUT NOCOPY NUMBER,
1828 X_Msg_Data OUT NOCOPY VARCHAR2
1829 )
1830 IS
1831
1832 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_Nprod';
1833 l_api_version_number CONSTANT NUMBER := 1.0;
1834 l_return_status_full VARCHAR2(1);
1835 l_Sqlcode NUMBER;
1836 l_sqlerrm Varchar2(2000);
1837
1838 l_Msg_Count NUMBER;
1839 l_Msg_Data Varchar2(2000);
1840
1841 BEGIN
1842 -- Standard Start of API savepoint
1843 SAVEPOINT CALCULATE_NPROD_PVT;
1844
1845 -- Standard call to check for call compatibility.
1846 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1847 p_api_version_number,
1848 l_api_name,
1849 G_PKG_NAME)
1850 THEN
1851 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1852 END IF;
1853
1854
1855 -- Initialize message list if p_init_msg_list is set to TRUE.
1856 IF FND_API.to_Boolean( p_init_msg_list )
1857 THEN
1858 FND_MSG_PUB.initialize;
1859 END IF;
1860
1861 INSERT INTO CSP_USAGE_HEADERS
1862 (USAGE_HEADER_ID,
1863 INVENTORY_ITEM_ID,
1864 ORGANIZATION_ID,
1865 SECONDARY_INVENTORY,
1866 HEADER_DATA_TYPE,
1867 RAW_AWU,
1868 AWU,
1869 PROCESS_STATUS,
1870 EXTERNAL_DATA,
1871 CREATION_DATE,
1872 CREATED_BY,
1873 LAST_UPDATE_DATE,
1874 LAST_UPDATED_BY,
1875 LAST_UPDATE_LOGIN)
1876 select NULL,
1877 nvl(csi.item_supplied,cnpp.inventory_item_id),
1878 cnpp.organization_id,
1879 cnpp.secondary_inventory,
1880 8, -- New Product planning
1881 NULL,
1882 cnpp.population_change *
1883 nvl(cnpp.manual_failure_rate,
1884 cnpp.calculated_failure_rate),
1885 'O',
1886 'N',
1887 sysdate,
1888 fnd_global.user_id,
1889 sysdate,
1890 fnd_global.user_id,
1891 fnd_global.conc_login_id
1892 from csp_new_product_planning_v cnpp,
1893 csp_supersede_items csi
1894 where cnpp.level_id like p_level_id || '%'
1895 and csi.inventory_item_id(+) = cnpp.inventory_item_id
1896 and csi.organization_id (+) = cnpp.organization_id
1897 and csi.sub_inventory_code (+) = cnpp.secondary_inventory;
1898
1899 -- Standard check for p_commit
1900 IF FND_API.to_Boolean( p_commit )
1901 THEN
1902 COMMIT WORK;
1903 END IF;
1904
1905
1906 -- Standard call to get message count and if count is 1, get message info.
1907 FND_MSG_PUB.Count_And_Get
1908 ( p_count => x_msg_count,
1909 p_data => x_msg_data
1910 );
1911
1912 EXCEPTION
1913 WHEN FND_API.G_EXC_ERROR THEN
1914 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1915 P_API_NAME => L_API_NAME
1916 ,P_PKG_NAME => G_PKG_NAME
1917 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1918 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1919 ,P_ROLLBACK_FLAG => 'N'
1920 ,X_MSG_COUNT => X_MSG_COUNT
1921 ,X_MSG_DATA => X_MSG_DATA
1922 ,X_RETURN_STATUS => X_RETURN_STATUS);
1923
1924 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1925 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1926 P_API_NAME => L_API_NAME
1927 ,P_PKG_NAME => G_PKG_NAME
1928 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1929 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1930 ,P_ROLLBACK_FLAG => 'N'
1931 ,X_MSG_COUNT => X_MSG_COUNT
1932 ,X_MSG_DATA => X_MSG_DATA
1933 ,X_RETURN_STATUS => X_RETURN_STATUS);
1934
1935 WHEN OTHERS THEN
1936 l_sqlcode := SQLCODE;
1937 l_sqlerrm := SQLERRM;
1938 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1939 P_API_NAME => L_API_NAME
1940 ,P_PKG_NAME => G_PKG_NAME
1941 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1942 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1943 ,P_SQLCODE => l_sqlcode
1944 ,P_SQLERRM => l_sqlerrm
1945 ,P_ROLLBACK_FLAG => 'N'
1946 ,X_MSG_COUNT => X_MSG_COUNT
1947 ,X_MSG_DATA => X_MSG_DATA
1948 ,X_RETURN_STATUS => X_RETURN_STATUS);
1949 End Calculate_New_Product_Planning;
1950
1951 PROCEDURE calculate_territory_norm (
1952 P_Api_Version_Number IN NUMBER,
1953 P_Init_Msg_List IN VARCHAR2,
1954 P_Commit IN VARCHAR2,
1955 P_validation_level IN NUMBER,
1956 P_Level_Id IN VARCHAR2,
1957 X_Return_Status OUT NOCOPY VARCHAR2,
1958 X_Msg_Count OUT NOCOPY NUMBER,
1959 X_Msg_Data OUT NOCOPY VARCHAR2
1960 )
1961 IS
1962
1963 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_TNorm';
1964 l_api_version_number CONSTANT NUMBER := 1.0;
1965 l_return_status_full VARCHAR2(1);
1966 l_Sqlcode NUMBER;
1967 l_sqlerrm Varchar2(2000);
1968
1969 l_Msg_Count NUMBER;
1970 l_Msg_Data Varchar2(2000);
1971
1972 BEGIN
1973 -- Standard Start of API savepoint
1974 SAVEPOINT CALCULATE_TNORM_PVT;
1975
1976 -- Standard call to check for call compatibility.
1977 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1978 p_api_version_number,
1979 l_api_name,
1980 G_PKG_NAME)
1981 THEN
1982 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1983 END IF;
1984
1985
1986 -- Initialize message list if p_init_msg_list is set to TRUE.
1987 IF FND_API.to_Boolean( p_init_msg_list )
1988 THEN
1989 FND_MSG_PUB.initialize;
1990 END IF;
1991
1992 INSERT INTO CSP_USAGE_HEADERS
1993 (USAGE_HEADER_ID,
1994 INVENTORY_ITEM_ID,
1995 ORGANIZATION_ID,
1996 SECONDARY_INVENTORY,
1997 HEADER_DATA_TYPE,
1998 RAW_AWU,
1999 AWU,
2000 PROCESS_STATUS,
2001 EXTERNAL_DATA,
2002 CREATION_DATE,
2003 CREATED_BY,
2004 LAST_UPDATE_DATE,
2005 LAST_UPDATED_BY,
2006 LAST_UPDATE_LOGIN)
2007 select NULL,
2008 nvl(csi.item_supplied,curos.inventory_item_id),
2009 curos.organization_id,
2010 curos.secondary_inventory,
2011 9, -- Territory Norm
2012 NULL,
2013 curos.awu,
2014 'O',
2015 'N',
2016 sysdate,
2017 fnd_global.user_id,
2018 sysdate,
2019 fnd_global.user_id,
2020 fnd_global.conc_login_id
2021 from csp_usage_reg_org_subinv_v curos,
2022 csp_supersede_items csi
2023 where curos.level_id like p_level_id || '%'
2024 and csi.inventory_item_id (+) = curos.inventory_item_id
2025 and csi.organization_id (+) = curos.organization_id
2026 and csi.sub_inventory_code(+) = curos.secondary_inventory
2027 group by nvl(csi.item_supplied,curos.inventory_item_id),
2028 curos.organization_id,curos.secondary_inventory,curos.awu;
2029
2030 -- Standard check for p_commit
2031 IF FND_API.to_Boolean( p_commit )
2032 THEN
2033 COMMIT WORK;
2034 END IF;
2035
2036
2037 -- Standard call to get message count and if count is 1, get message info.
2038 FND_MSG_PUB.Count_And_Get
2039 ( p_count => x_msg_count,
2040 p_data => x_msg_data
2041 );
2042
2043 EXCEPTION
2044 WHEN FND_API.G_EXC_ERROR THEN
2045 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2046 P_API_NAME => L_API_NAME
2047 ,P_PKG_NAME => G_PKG_NAME
2048 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2049 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2050 ,P_ROLLBACK_FLAG => 'N'
2051 ,X_MSG_COUNT => X_MSG_COUNT
2052 ,X_MSG_DATA => X_MSG_DATA
2053 ,X_RETURN_STATUS => X_RETURN_STATUS);
2054
2055 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2056 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2057 P_API_NAME => L_API_NAME
2058 ,P_PKG_NAME => G_PKG_NAME
2059 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2060 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2061 ,P_ROLLBACK_FLAG => 'N'
2062 ,X_MSG_COUNT => X_MSG_COUNT
2063 ,X_MSG_DATA => X_MSG_DATA
2064 ,X_RETURN_STATUS => X_RETURN_STATUS);
2065
2066 WHEN OTHERS THEN
2067 l_sqlcode := SQLCODE;
2068 l_sqlerrm := SQLERRM;
2069 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2070 P_API_NAME => L_API_NAME
2071 ,P_PKG_NAME => G_PKG_NAME
2072 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
2073 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2074 ,P_SQLCODE => l_sqlcode
2075 ,P_SQLERRM => l_sqlerrm
2076 ,P_ROLLBACK_FLAG => 'N'
2077 ,X_MSG_COUNT => X_MSG_COUNT
2078 ,X_MSG_DATA => X_MSG_DATA
2079 ,X_RETURN_STATUS => X_RETURN_STATUS);
2080 End Calculate_Territory_Norm;
2081
2082 PROCEDURE Create_supply_chain (
2083 P_Api_Version_Number IN NUMBER,
2084 P_Init_Msg_List IN VARCHAR2 ,
2085 P_Commit IN VARCHAR2 ,
2086 P_validation_level IN NUMBER ,
2087 P_Level_id IN VARCHAR2,
2088 X_Return_Status OUT NOCOPY VARCHAR2,
2089 X_Msg_Count OUT NOCOPY NUMBER,
2090 X_Msg_Data OUT NOCOPY VARCHAR2
2091 )
2092 IS
2093
2094 cursor c_parameters(p_level_id varchar2) is
2095 select node_type
2096 from csp_planning_parameters
2097 where level_id = p_level_id;
2098
2099 l_api_name constant varchar2(30) := 'Create_Supply_Chain';
2100 l_api_version_number constant number := 1.0;
2101 l_return_status_full varchar2(1);
2102 l_Sqlcode number;
2103 l_Sqlerrm varchar2(2000);
2104
2105
2106 l_supply_level number := 1;
2107 l_string varchar2(2000);
2108 g_txn_type_id number := 93;
2109 g_txn_action_id number := 1;
2110 g_txn_source_type_id number := 13;
2111 g_total_period number := 1;
2112 g_source_type number := 1;
2113 g_default_flag number := 1;
2114 l_period_size number := 0;
2115 l_level_id varchar2(2000);
2116 l_node_type varchar2(20);
2117 l_get_app_info boolean;
2118 l_status varchar2(1);
2119 l_industry varchar2(1);
2120 l_oracle_schema varchar2(30);
2121
2122 BEGIN
2123 -- Standard Start of API savepoint
2124 SAVEPOINT CREATE_SUPPLY_CHAIN_PVT;
2125
2126 -- Standard call to check for call compatibility.
2127 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2128 p_api_version_number,
2129 l_api_name,
2130 G_PKG_NAME)
2131 THEN
2132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2133 END IF;
2134
2135
2136 -- Initialize message list if p_init_msg_list is set to TRUE.
2137 IF FND_API.to_Boolean( p_init_msg_list )
2138 THEN
2139 FND_MSG_PUB.initialize;
2140 END IF;
2141
2142
2143 -- Initialize API return status to SUCCESS
2144 x_return_status := FND_API.G_RET_STS_SUCCESS;
2145
2146 --
2147 -- API body
2148 --
2149 l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
2150 --- Delete from Supply Chain
2151 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN' ;
2152
2153 open c_parameters(p_level_id);
2154 Fetch c_parameters into l_node_type;
2155 close c_parameters;
2156 l_level_id := p_level_id;
2157 If l_node_type in ('ORGANIZATON_WH','SUBINVENTORY') then
2158 l_level_id := substr(p_level_id,1,instr(p_level_id,'.',-1,1) - 1);
2159 End If;
2160
2161 INSERT INTO CSP_SUPPLY_CHAIN (
2162 source_type,
2163 source_organization_id,
2164 source_subinventory,
2165 organization_id,
2166 secondary_inventory,
2167 inventory_item_id,
2168 lead_time,
2169 supply_level,
2170 creation_date,
2171 created_by,
2172 last_updated_by,
2173 last_update_date)
2174 select /*+ parallel(MSIB,8) parallel(MISI,8) parallel(MSI,8)
2175 parallel(CSC,8) */
2176 nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) source_type,
2177 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) source_organization,
2178 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) source_subinventory,
2179 cri.organization_id,
2180 cri.secondary_inventory,
2181 cri.inventory_item_id,
2182 NULL,
2183 1, -- supply_level
2184 sysdate,
2185 fnd_global.user_id,
2186 fnd_global.user_id,
2187 sysdate
2188 from csp_region_items_v cri,
2189 mtl_related_items mri,
2190 mtl_parameters mp,
2191 mtl_system_items_b msib,
2192 mtl_item_sub_inventories misi,
2193 mtl_secondary_inventories msi
2194 where cri.level_id like l_level_id || '%'
2195 and mp.organization_id = cri.organization_id
2196 and mri.organization_id = mp.master_organization_id
2197 and mri.inventory_item_id = cri.inventory_item_id
2198 and misi.organization_id (+) = cri.organization_id
2199 and misi.inventory_item_id (+) = cri.inventory_item_id
2200 and misi.secondary_inventory (+) = cri.secondary_inventory
2201 and msib.organization_id = cri.organization_id
2202 and msib.inventory_item_id = cri.inventory_item_id
2203 and msi.organization_id = cri.organization_id
2204 and msi.secondary_inventory_name = cri.secondary_inventory
2205 Group By nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) ,
2206 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) ,
2207 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) ,
2208 cri.organization_id,
2209 cri.secondary_inventory,
2210 cri.inventory_item_id;
2211
2212 loop
2213 l_supply_level := l_supply_level + 1;
2214 insert into csp_supply_chain(
2215 source_type,
2216 source_organization_id,
2217 source_subinventory,
2218 organization_id,
2219 secondary_inventory,
2220 inventory_item_id,
2221 lead_time,
2222 supply_level,
2223 creation_date,
2224 created_by,
2225 last_updated_by,
2226 last_update_date)
2227 select
2228 nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) source_type,
2229 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) source_organization,
2230 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) source_subinventory,
2231 csc.source_organization_id organization_id,
2232 nvl(csc.source_subinventory,'-') subinventory_code,
2233 csc.inventory_item_id,
2234 NULL,
2235 l_supply_level,
2236 sysdate,
2237 fnd_global.user_id,
2238 fnd_global.user_id,
2239 sysdate
2240 from mtl_parameters mp,
2241 mtl_system_items_b msib,
2242 mtl_item_sub_inventories misi,
2243 mtl_secondary_inventories msi,
2244 csp_supply_chain csc
2245 where mp.organization_id = csc.source_organization_id
2246 and misi.organization_id (+) = csc.source_organization_id
2247 and misi.inventory_item_id (+) = csc.inventory_item_id
2248 and misi.secondary_inventory (+) = csc.source_subinventory
2249 and msib.organization_id = csc.source_organization_id
2250 and msib.inventory_item_id = csc.inventory_item_id
2251 and msi.organization_id (+) = csc.source_organization_id
2252 and msi.secondary_inventory_name (+) = csc.source_subinventory
2253 and csc.supply_level = l_supply_level - 1
2254 and csc.source_type IN (1,3)
2255 Group By
2256 nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) ,
2257 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) ,
2258 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) ,
2259 csc.source_organization_id ,
2260 nvl(csc.source_subinventory,'-') ,
2261 csc.inventory_item_id;
2262
2263 if sql%notfound then
2264 exit;
2265 end if;
2266 end loop;
2267
2268 --
2269 -- End of API body
2270 --
2271
2272 -- Standard check for p_commit
2273 IF FND_API.to_Boolean( p_commit )
2274 THEN
2275 COMMIT WORK;
2276 END IF;
2277
2278
2279 -- Standard call to get message count and if count is 1, get message info.
2280 FND_MSG_PUB.Count_And_Get
2281 ( p_count => x_msg_count,
2282 p_data => x_msg_data
2283 );
2284
2285 EXCEPTION
2286 WHEN FND_API.G_EXC_ERROR THEN
2287 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2288 P_API_NAME => L_API_NAME
2289 ,P_PKG_NAME => G_PKG_NAME
2290 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2291 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2292 ,P_ROLLBACK_FLAG => 'N'
2293 ,X_MSG_COUNT => X_MSG_COUNT
2294 ,X_MSG_DATA => X_MSG_DATA
2295 ,X_RETURN_STATUS => X_RETURN_STATUS);
2296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2297 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2298 P_API_NAME => L_API_NAME
2299 ,P_PKG_NAME => G_PKG_NAME
2300 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2301 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2302 ,P_ROLLBACK_FLAG => 'N'
2303 ,X_MSG_COUNT => X_MSG_COUNT
2304 ,X_MSG_DATA => X_MSG_DATA
2305 ,X_RETURN_STATUS => X_RETURN_STATUS);
2306 WHEN OTHERS THEN
2307 l_sqlcode := SQLCODE;
2308 l_sqlerrm := SQLERRM;
2309 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2310 P_API_NAME => L_API_NAME
2311 ,P_PKG_NAME => G_PKG_NAME
2312 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
2313 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2314 ,P_ROLLBACK_FLAG => 'N'
2315 ,P_SQLCODE => l_Sqlcode
2316 ,P_SQLERRM => l_Sqlerrm
2317 ,X_MSG_COUNT => X_MSG_COUNT
2318 ,X_MSG_DATA => X_MSG_DATA
2319 ,X_RETURN_STATUS => X_RETURN_STATUS);
2320 End Create_Supply_Chain;
2321
2322 PROCEDURE Create_Supply_Chain (
2323 P_Api_Version_Number IN NUMBER,
2324 P_Init_Msg_List IN VARCHAR2 ,
2325 P_Commit IN VARCHAR2 ,
2326 P_validation_level IN NUMBER ,
2327 X_Return_Status OUT NOCOPY VARCHAR2,
2328 X_Msg_Count OUT NOCOPY NUMBER,
2329 X_Msg_Data OUT NOCOPY VARCHAR2
2330 )
2331 IS
2332
2333 Cursor c_Period_Size Is
2334 Select MAX(HISTORY_PERIODS * PERIOD_SIZE) PERIOD_SIZE
2335 From CSP_PLANNING_PARAMETERS cpp,
2336 CSP_FORECAST_RULES_B cfrb
2337 Where cpp.FORECAST_RULE_ID = cfrb.FORECAST_RULE_ID;
2338
2339
2340 l_api_name constant varchar2(30) := 'Create_Supply_Chain';
2341 l_api_version_number constant number := 1.0;
2342 l_return_status_full varchar2(1);
2343 l_Sqlcode number;
2344 l_Sqlerrm varchar2(2000);
2345
2346
2347 l_supply_level number := 1;
2348 l_string varchar2(2000);
2349 g_txn_type_id number := 93;
2350 g_txn_action_id number := 1;
2351 g_txn_source_type_id number := 13;
2352 g_total_period number := 1;
2353 g_source_type number := 1;
2354 g_default_flag number := 1;
2355 l_period_size number := 0;
2356
2357 BEGIN
2358 -- Standard Start of API savepoint
2359 SAVEPOINT CREATE_SUPPLY_CHAIN_PVT;
2360
2361 -- Standard call to check for call compatibility.
2362 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2363 p_api_version_number,
2364 l_api_name,
2365 G_PKG_NAME)
2366 THEN
2367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2368 END IF;
2369
2370
2371 -- Initialize message list if p_init_msg_list is set to TRUE.
2372 IF FND_API.to_Boolean( p_init_msg_list )
2373 THEN
2374 FND_MSG_PUB.initialize;
2375 END IF;
2376
2377
2378 -- Initialize API return status to SUCCESS
2379 x_return_status := FND_API.G_RET_STS_SUCCESS;
2380
2381 --
2382 -- API body
2383 --
2384 Open c_Period_Size;
2385 Fetch c_Period_size into l_period_size;
2386 Close c_Period_size;
2387
2388 INSERT INTO CSP_SUPPLY_CHAIN (
2389 source_type,
2390 source_organization_id,
2391 source_subinventory,
2392 organization_id,
2393 secondary_inventory,
2394 inventory_item_id,
2395 lead_time,
2396 supply_level,
2397 creation_date,
2398 created_by,
2399 last_updated_by,
2400 last_update_date)
2401 select /*+ parallel(MSIB,8) parallel(MISI,8) parallel(MSI,8)
2402 parallel(CSC,8) */
2403 nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) source_type,
2404 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) source_organization,
2405 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) source_subinventory,
2406 mmt.organization_id,
2407 mmt.subinventory_code,
2408 mmt.inventory_item_id,
2409 nvl(mism.intransit_time,decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
2410 decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0,
2411 nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0), nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)),
2412 nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))) lead_time,
2413 1, -- supply_level
2414 sysdate,
2415 fnd_global.user_id,
2416 fnd_global.user_id,
2417 sysdate
2418 from mtl_parameters mp,
2419 mtl_system_items_b msib,
2420 mtl_item_sub_inventories misi,
2421 mtl_secondary_inventories msi,
2422 mtl_material_transactions mmt,
2423 mtl_interorg_ship_methods mism
2424 where mp.organization_id = mmt.organization_id
2425 and misi.organization_id (+) = mmt.organization_id
2426 and misi.inventory_item_id (+) = mmt.inventory_item_id
2427 and misi.secondary_inventory (+) = mmt.subinventory_code
2428 and mism.to_organization_id (+) = mp.organization_id
2429 and mism.from_organization_id (+) = decode(mp.source_type,1,mp.source_organization_id,3,mp.source_organization_id,-1)
2430 and mism.default_flag (+) = 1
2431 and msib.organization_id = mmt.organization_id
2432 and msib.inventory_item_id = mmt.inventory_item_id
2433 and msi.organization_id = mmt.organization_id
2434 and msi.secondary_inventory_name = mmt.subinventory_code
2435 and mmt.transaction_action_id = g_txn_action_id
2436 and (mmt.transaction_date > (trunc(sysdate) - l_period_size - 1) and
2437 mmt.transaction_date < trunc(sysdate))
2438 Group By nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) ,
2439 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) ,
2440 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) ,
2441 mmt.organization_id,
2442 mmt.subinventory_code,
2443 mmt.inventory_item_id,
2444 nvl(mism.intransit_time,decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
2445 decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0,
2446 nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0), nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)),
2447 nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))) ,
2448 1,
2449 sysdate,
2450 fnd_global.user_id,
2451 fnd_global.user_id,
2452 sysdate;
2453
2454 loop
2455 l_supply_level := l_supply_level + 1;
2456 insert into csp_supply_chain(
2457 source_type,
2458 source_organization_id,
2459 source_subinventory,
2460 organization_id,
2461 secondary_inventory,
2462 inventory_item_id,
2463 lead_time,
2464 supply_level,
2465 creation_date,
2466 created_by,
2467 last_updated_by,
2468 last_update_date)
2469 select
2470 nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) source_type,
2471 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) source_organization,
2472 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) source_subinventory,
2473 csc.source_organization_id organization_id,
2474 nvl(csc.source_subinventory,'-') subinventory_code,
2475 csc.inventory_item_id,
2476 nvl(mism.intransit_time,decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
2477 decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0, nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0),
2478 nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)), nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))) lead_time,
2479 l_supply_level,
2480 sysdate,
2481 fnd_global.user_id,
2482 fnd_global.user_id,
2483 sysdate
2484 from mtl_parameters mp,
2485 mtl_system_items_b msib,
2486 mtl_item_sub_inventories misi,
2487 mtl_secondary_inventories msi,
2488 csp_supply_chain csc,
2489 mtl_interorg_ship_methods mism
2490 where mp.organization_id = csc.source_organization_id
2491 and misi.organization_id (+) = csc.source_organization_id
2492 and misi.inventory_item_id (+) = csc.inventory_item_id
2493 and misi.secondary_inventory (+) = csc.source_subinventory
2494 and mism.to_organization_id (+) = mp.organization_id
2495 and mism.from_organization_id (+) = decode(mp.source_type,1,mp.source_organization_id,3,mp.source_organization_id,-1)
2496 and mism.default_flag (+) = g_default_flag
2497 and msib.organization_id = csc.source_organization_id
2498 and msib.inventory_item_id = csc.inventory_item_id
2499 and msi.organization_id (+) = csc.source_organization_id
2500 and msi.secondary_inventory_name (+) = csc.source_subinventory
2501 and csc.supply_level = l_supply_level - 1
2502 and csc.source_type IN (1,3)
2503 Group By
2504 nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) ,
2505 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_organization_id,msi.source_organization_id),msib.source_organization_id),misi.source_organization_id) ,
2506 decode(misi.source_type,NULL,decode(msib.source_type,NULL,decode(msi.source_type,NULL,mp.source_subinventory,msi.source_subinventory),msib.source_subinventory),misi.source_subinventory) ,
2507 csc.source_organization_id ,
2508 nvl(csc.source_subinventory,'-') ,
2509 csc.inventory_item_id,
2510 nvl(mism.intransit_time,decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
2511 decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0, nvl(msi.preprocessing_lead_time,0) + nvl(msi.processing_lead_time,0) + nvl(msi.postprocessing_lead_time,0),
2512 nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0)), nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))) ,
2513 l_supply_level,
2514 sysdate,
2515 fnd_global.user_id,
2516 fnd_global.user_id,
2517 sysdate;
2518
2519 if sql%notfound then
2520 exit;
2521 end if;
2522 end loop;
2523
2524 --
2525 -- End of API body
2526 --
2527
2528 -- Standard check for p_commit
2529 IF FND_API.to_Boolean( p_commit )
2530 THEN
2531 COMMIT WORK;
2532 END IF;
2533
2534
2535 -- Standard call to get message count and if count is 1, get message info.
2536 FND_MSG_PUB.Count_And_Get
2537 ( p_count => x_msg_count,
2538 p_data => x_msg_data
2539 );
2540
2541 EXCEPTION
2542 WHEN FND_API.G_EXC_ERROR THEN
2543 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2544 P_API_NAME => L_API_NAME
2545 ,P_PKG_NAME => G_PKG_NAME
2546 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2547 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2548 ,P_ROLLBACK_FLAG => 'N'
2549 ,X_MSG_COUNT => X_MSG_COUNT
2550 ,X_MSG_DATA => X_MSG_DATA
2551 ,X_RETURN_STATUS => X_RETURN_STATUS);
2552
2553 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2554 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2555 P_API_NAME => L_API_NAME
2556 ,P_PKG_NAME => G_PKG_NAME
2557 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2558 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2559 ,P_ROLLBACK_FLAG => 'N'
2560 ,X_MSG_COUNT => X_MSG_COUNT
2561 ,X_MSG_DATA => X_MSG_DATA
2562 ,X_RETURN_STATUS => X_RETURN_STATUS);
2563
2564 WHEN OTHERS THEN
2565 l_sqlcode := SQLCODE;
2566 l_sqlerrm := SQLERRM;
2567 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2568 P_API_NAME => L_API_NAME
2569 ,P_PKG_NAME => G_PKG_NAME
2570 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
2571 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2572 ,P_ROLLBACK_FLAG => 'N'
2573 ,P_SQLCODE => l_Sqlcode
2574 ,P_SQLERRM => l_Sqlerrm
2575 ,X_MSG_COUNT => X_MSG_COUNT
2576 ,X_MSG_DATA => X_MSG_DATA
2577 ,X_RETURN_STATUS => X_RETURN_STATUS);
2578
2579 End Create_Supply_Chain;
2580
2581
2582 PROCEDURE Create_Usage_History (
2583 P_Api_Version_Number IN NUMBER,
2584 P_Init_Msg_List IN VARCHAR2,
2585 P_Commit IN VARCHAR2,
2586 p_validation_level IN NUMBER,
2587 X_Return_Status OUT NOCOPY VARCHAR2,
2588 X_Msg_Count OUT NOCOPY NUMBER,
2589 X_Msg_Data OUT NOCOPY VARCHAR2
2590 )
2591
2592 IS
2593
2594 l_api_name constant varchar2(30) := 'Create_Usage_Hist';
2595 l_api_version_number CONSTANT NUMBER := 1.0;
2596 l_return_status_full VARCHAR2(1);
2597 l_sqlcode NUMBER;
2598 l_sqlerrm Varchar2(2000);
2599
2600 l_string varchar2(2000);
2601 g_txn_type_id number := 93;
2602 g_txn_action_id number := 1;
2603 g_txn_source_type_id number := 13;
2604 l_supply_level number := 0;
2605 --Period Size and Number of periods changed to be
2606 --1 to calculate usage history on daily basis for
2607 --1158
2608 l_period_size number := 1; -- G_PERIOD_SIZE;
2609 l_number_of_periods number := 1; -- G_HISTORY_PERIODS;
2610 l_usage_id Number;
2611
2612 l_Msg_Count NUMBER;
2613 l_Msg_Data Varchar2(2000);
2614
2615 BEGIN
2616 -- Standard Start of API savepoint
2617 SAVEPOINT CREATE_USAGE_HIST_PVT;
2618
2619
2620 -- Initialize API return status to SUCCESS
2621 x_return_status := FND_API.G_RET_STS_SUCCESS;
2622
2623 --
2624 -- API body
2625 --
2626 -- Create Usage History for Engineering subinventories
2627 insert into csp_usage_histories(
2628 organization_id,
2629 subinventory_code,
2630 inventory_item_id,
2631 period_start_date,
2632 transaction_type_id,
2633 quantity,
2634 history_data_type,
2635 period_type,
2636 created_by,
2637 creation_date,
2638 last_updated_by,
2639 last_update_date)
2640 select mmt.organization_id,
2641 mmt.subinventory_code,
2642 mmt.inventory_item_id,
2643 trunc(transaction_date),
2644 transaction_type_id,
2645 sum(mmt.primary_quantity) * -1 primary_quantity,
2646 1, -- History data type
2647 3, -- Period type
2648 fnd_global.user_id,
2649 sysdate,
2650 fnd_global.user_id,
2651 sysdate
2652 from mtl_material_transactions mmt
2653 where mmt.creation_date >
2654 decode(G_LAST_RUN_DATE,fnd_api.g_miss_date,G_LAST_RUN_DATE,
2655 to_date(to_char(G_LAST_RUN_DATE,'dd/mm/yy') || ' 23:59:59','dd/mm/yy hh24:mi:ss')) And mmt.creation_date < trunc(sysdate)
2656 and mmt.transaction_action_id = g_txn_action_id
2657 group by
2658 mmt.organization_id,
2659 mmt.subinventory_code,
2660 mmt.inventory_item_id,
2661 trunc(transaction_date),
2662 transaction_type_id,
2663 1, -- History data type
2664 3, -- Period type
2665 fnd_global.user_id,
2666 sysdate,
2667 fnd_global.user_id,
2668 sysdate;
2669
2670 -- Rollup Usage History through Supply Chain
2671 loop
2672 l_supply_level := l_supply_level + 1;
2673 insert into csp_usage_histories(
2674 organization_id,
2675 subinventory_code,
2676 inventory_item_id,
2677 period_start_date,
2678 transaction_type_id,
2679 quantity,
2680 history_data_type,
2681 period_type,
2682 created_by,
2683 creation_date,
2684 last_updated_by,
2685 last_update_date)
2686 select /*+ ORDERED */
2687 nvl(csc.source_organization_id,-1),
2688 nvl(csc.source_subinventory,'-'),
2689 csc.inventory_item_id,
2690 trunc(cuh.period_start_date),
2691 transaction_type_id,
2692 sum(cuh.quantity),
2693 1,--heh decode(csc.source_type,2,3,1), -- History data type
2694 3, -- Period type
2695 fnd_global.user_id,
2696 sysdate,
2697 fnd_global.user_id,
2698 sysdate
2699 from csp_supply_chain csc,
2700 csp_usage_histories cuh
2701 where cuh.history_data_type = 1
2702 and cuh.period_start_date > G_LAST_RUN_DATE
2703 and cuh.organization_id = csc.organization_id
2704 and cuh.subinventory_code = csc.secondary_inventory
2705 and cuh.inventory_item_id = csc.inventory_item_id
2706 and csc.supply_level = l_supply_level
2707 group by
2708 nvl(csc.source_organization_id,-1),
2709 nvl(csc.source_subinventory,'-'),
2710 csc.inventory_item_id,
2711 trunc(cuh.period_start_date),
2712 transaction_type_id,
2713 1,--heh decode(csc.source_type,2,3,1), -- History data type
2714 3, -- Period type
2715 fnd_global.user_id,
2716 sysdate,
2717 fnd_global.user_id,
2718 sysdate;
2719 if sql%notfound then
2720 exit;
2721 end if;
2722 exit;
2723 end loop;
2724
2725 --
2726 -- End of API body
2727 --
2728
2729 -- Standard check for p_commit
2730 IF FND_API.to_Boolean( p_commit )
2731 THEN
2732 COMMIT WORK;
2733 END IF;
2734
2735
2736 -- Standard call to get message count and if count is 1, get message info.
2737 FND_MSG_PUB.Count_And_Get
2738 ( p_count => x_msg_count,
2739 p_data => x_msg_data
2740 );
2741
2742 EXCEPTION
2743 WHEN FND_API.G_EXC_ERROR THEN
2744 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2745 P_API_NAME => L_API_NAME
2746 ,P_PKG_NAME => G_PKG_NAME
2747 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2748 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2749 ,P_ROLLBACK_FLAG => 'N'
2750 ,X_MSG_COUNT => X_MSG_COUNT
2751 ,X_MSG_DATA => X_MSG_DATA
2752 ,X_RETURN_STATUS => X_RETURN_STATUS);
2753
2754 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2755 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2756 P_API_NAME => L_API_NAME
2757 ,P_PKG_NAME => G_PKG_NAME
2758 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2759 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2760 ,P_ROLLBACK_FLAG => 'N'
2761 ,X_MSG_COUNT => X_MSG_COUNT
2762 ,X_MSG_DATA => X_MSG_DATA
2763 ,X_RETURN_STATUS => X_RETURN_STATUS);
2764
2765 WHEN OTHERS THEN
2766 l_sqlcode := SQLCODE;
2767 l_sqlerrm := SQLERRM;
2768 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2769 P_API_NAME => L_API_NAME
2770 ,P_PKG_NAME => G_PKG_NAME
2771 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
2772 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2773 ,P_ROLLBACK_FLAG => 'N'
2774 ,P_SQLCODE => l_sqlcode
2775 ,P_SQLERRM => l_sqlerrm
2776 ,X_MSG_COUNT => X_MSG_COUNT
2777 ,X_MSG_DATA => X_MSG_DATA
2778 ,X_RETURN_STATUS => X_RETURN_STATUS);
2779 End Create_Usage_History;
2780
2781
2782 PROCEDURE Apply_Business_Rules (
2783 P_Api_Version_Number IN NUMBER,
2784 P_Init_Msg_List IN VARCHAR2,
2785 P_Commit IN VARCHAR2,
2786 p_validation_level IN NUMBER,
2787 X_Return_Status OUT NOCOPY VARCHAR2,
2788 X_Msg_Count OUT NOCOPY NUMBER,
2789 X_Msg_Data OUT NOCOPY VARCHAR2
2790 ) IS
2791
2792 Cursor c_usg_hdr_subinv Is
2793 Select decode(cuh.RECOMMENDED_MAX_QUANTITY,0,0,greatest(1,cuh.RECOMMENDED_MIN_QUANTITY)) RECOMMENDED_MIN_QUANTITY,
2794 cuh.RECOMMENDED_MAX_QUANTITY,
2795 cuh.INVENTORY_ITEM_ID,
2796 cuh.ORGANIZATION_ID,
2797 cuh.SECONDARY_INVENTORY
2798 From csp_usage_headers cuh,
2799 csp_planning_parameters cpp,
2800 mtl_item_sub_inventories misi,
2801 csp_business_rules_b cbrb
2802 Where header_data_type = 1
2803 and process_status = 'O'
2804 and cuh.planning_parameters_id = cpp.planning_parameters_id
2805 and cpp.node_type = 'SUBINVENTORY'
2806 and misi.organization_id(+) = cuh.organization_id
2807 and misi.inventory_item_id(+) = cuh.inventory_item_id
2808 and misi.secondary_inventory(+) = cuh.secondary_inventory
2809 And cbrb.business_rule_id = cpp.recommendation_rule_id
2810 And nvl(cbrb.business_rule_value3,1) >
2811 abs(decode(cbrb.business_rule_value3,null,0,
2812 nvl(cuh.recommended_max_quantity * cuh.item_cost,0) -
2813 nvl(misi.MAX_MINMAX_QUANTITY * cuh.item_cost,0)))
2814 And nvl(cbrb.business_rule_value4,1) >
2815 abs(decode(cbrb.business_rule_value4,null,0,
2816 ROUND((nvl((cuh.recommended_max_quantity - misi.MAX_MINMAX_QUANTITY) *
2817 cuh.item_cost,0)/
2818 DECODE(nvl(misi.MAX_MINMAX_QUANTITY,0) *
2819 nvl(cuh.item_cost ,0),0,1,misi.MAX_MINMAX_QUANTITY*cuh.item_cost)) * 100,2)))
2820 And nvl(cbrb.business_rule_value5,1) >
2821 abs(decode(cbrb.business_rule_value5,null,0,
2822 nvl(cuh.RECOMMENDED_MAX_QUANTITY - misi.MAX_MINMAX_QUANTITY,0)))
2823 And nvl(cbrb.business_rule_value6,1) >
2824 abs(decode(cbrb.business_rule_value6,null,0,
2825 ROUND((nvl(cuh.recommended_max_quantity - misi.MAX_MINMAX_QUANTITY,0)/
2826 DECODE(nvl(misi.MAX_MINMAX_QUANTITY,0),0,1,
2827 misi.MAX_MINMAX_QUANTITY)) * 100,2)))
2828 And (nvl(cuh.tracking_signal,0) >= decode(cpp.recommend_method,'PNORM',0,
2829 'TNORM',0,nvl(cbrb.business_rule_value1,nvl(cuh.tracking_signal,0)))
2830 And nvl(cuh.tracking_signal,0) <= decode(cpp.recommend_method,'PNORM',0,
2831 'TNORM',0,nvl(cbrb.business_rule_value2,nvl(cuh.tracking_signal,0))));
2832
2833 Cursor c_usg_hdr_item Is
2834 Select cuh.inventory_item_id,
2835 cuh.organization_id,
2836 cuh.recommended_min_quantity,
2837 cuh.recommended_max_quantity
2838 from CSP_USAGE_HEADERS cuh,
2839 CSP_PLANNING_PARAMETERS cpp,
2840 MTL_SYSTEM_ITEMS_B msib,
2841 CSP_BUSINESS_RULES_B cbrb
2842 Where msib.INVENTORY_ITEM_ID = cuh.INVENTORY_ITEM_ID
2843 And msib.ORGANIZATION_ID = cuh.ORGANIZATION_ID
2844 And cuh.header_data_type = 4
2845 And cuh.process_status = 'O'
2846 And cpp.node_type = 'ORGANIZATION_WH'
2847 And cpp.organization_id = cuh.organization_id
2848 And cbrb.business_rule_id = cpp.recommendation_rule_id
2849 And nvl(cbrb.business_rule_value3,1) >
2850 abs(decode(cbrb.business_rule_value3,null,0,
2851 nvl(cuh.recommended_max_quantity * cuh.item_cost,0) -
2852 nvl(msib.MAX_MINMAX_QUANTITY * cuh.item_cost,0)))
2853 And nvl(cbrb.business_rule_value4,1) >
2854 abs(decode(cbrb.business_rule_value4,null,0,
2855 ROUND((nvl((cuh.recommended_max_quantity - msib.MAX_MINMAX_QUANTITY) *
2856 cuh.item_cost,0)/
2857 DECODE(nvl(msib.MAX_MINMAX_QUANTITY,0) *
2858 nvl(cuh.item_cost ,0),0,1,msib.MAX_MINMAX_QUANTITY*cuh.item_cost)) * 100,2)))
2859 And nvl(cbrb.business_rule_value5,1) >
2860 abs(decode(cbrb.business_rule_value5,null,0,
2861 nvl(cuh.RECOMMENDED_MAX_QUANTITY - msib.MAX_MINMAX_QUANTITY,0)))
2862 And nvl(cbrb.business_rule_value6,1) >
2863 abs(decode(cbrb.business_rule_value6,null,0,
2864 ROUND((nvl(cuh.recommended_max_quantity - msib.MAX_MINMAX_QUANTITY,0)/
2865 DECODE(nvl(msib.MAX_MINMAX_QUANTITY,0),0,1,
2866 msib.MAX_MINMAX_QUANTITY)) * 100,2)))
2867 And (nvl(cuh.tracking_signal,0) >= decode(cpp.recommend_method,'PNORM',0,
2868 'TNORM',0,nvl(cbrb.business_rule_value1,nvl(cuh.tracking_signal,0)))
2869 And nvl(cuh.tracking_signal,0) <= decode(cpp.recommend_method,'PNORM',0,
2870 'TNORM',0,nvl(cbrb.business_rule_value2,nvl(cuh.tracking_signal,0))));
2871 Begin
2872 -- Update Subinventory Min/Max values
2873
2874 -- Removed for database version compatibility
2875 -- issues. Replaced with UPDATE/INSERT
2876 /*
2877 MERGE INTO MTL_ITEM_SUB_INVENTORIES item_subinv USING
2878 (Select cuh.Inventory_Item_Id,
2879 cuh.Organization_Id,
2880 cuh.secondary_inventory,
2881 cuh.recommended_min_quantity,
2882 cuh.recommended_max_quantity
2883 From csp_usage_headers cuh,
2884 csp_planning_parameters cpp,
2885 mtl_item_sub_inventories misi,
2886 CSP.csp_business_rules_b cbrb
2887 Where cuh.planning_parameters_id = cpp.planning_parameters_id
2888 and misi.organization_id(+) = cuh.organization_id
2889 and misi.inventory_item_id(+) = cuh.inventory_item_id
2890 and misi.secondary_inventory(+) = cuh.secondary_inventory
2891 And cbrb.business_rule_id = cpp.recommendation_rule_id
2892 And nvl(cbrb.business_rule_value3,1) >
2893 decode(cbrb.business_rule_value3,null,0,nvl(cuh.recommended_max_quantity * cuh.item_cost,0) -
2894 nvl(misi.MAX_MINMAX_QUANTITY * cuh.item_cost,0))
2895 And nvl(cbrb.business_rule_value4,1) >
2896 decode(cbrb.business_rule_value4,null,0,
2897 ROUND((nvl(cuh.recommended_max_quantity - misi.MAX_MINMAX_QUANTITY * cuh.item_cost,0)/
2898 DECODE(nvl(misi.MAX_MINMAX_QUANTITY,0) * nvl(cuh.item_cost ,0),0,1,misi.MAX_MINMAX_QUANTITY)) * 100,2))
2899 And nvl(cbrb.business_rule_value5,1) >
2900 decode(cbrb.business_rule_value5,null,0,nvl(cuh.RECOMMENDED_MAX_QUANTITY - misi.MAX_MINMAX_QUANTITY,0))
2901 And nvl(cbrb.business_rule_value6,1) >
2902 decode(cbrb.business_rule_value6,null,0,
2903 ROUND((nvl(cuh.recommended_max_quantity - misi.MAX_MINMAX_QUANTITY,0)/DECODE(nvl(misi.MAX_MINMAX_QUANTITY,0),0,1,
2904 misi.MAX_MINMAX_QUANTITY)) * 100,2))
2905 And (nvl(cuh.tracking_signal,0) >=
2906 decode(cpp.recommend_method,'PNORM',0,'TNORM',0,nvl(cbrb.business_rule_value1,nvl(cuh.tracking_signal,0)))
2907 And nvl(cuh.tracking_signal,0) <=
2908 decode(cpp.recommend_method,'PNORM',0,'TNORM',0,nvl(cbrb.business_rule_value2,nvl(cuh.tracking_signal,0))))) sq
2909 ON (item_subinv.INVENTORY_ITEM_ID = sq.INVENTORY_ITEM_ID
2910 AND item_subinv.ORGANIZATION_ID = sq.ORGANIZATION_ID
2911 AND item_subinv.SECONDARY_INVENTORY = sq.SECONDARY_INVENTORY)
2912 WHEN MATCHED THEN UPDATE SET item_subinv.MIN_MINMAX_QUANTITY = decode(sq.RECOMMENDED_MAX_QUANTITY,0,0,greatest(1,sq.RECOMMENDED_MIN_QUANTITY)),
2913 item_subinv.MAX_MINMAX_QUANTITY = sq.RECOMMENDED_MAX_QUANTITY,
2914 item_subinv.INVENTORY_PLANNING_CODE = 2
2915 WHEN NOT MATCHED THEN INSERT(item_subinv.INVENTORY_ITEM_ID,
2916 item_subinv.ORGANIZATION_ID,
2917 item_subinv.SECONDARY_INVENTORY,
2918 item_subinv.LAST_UPDATE_DATE,
2919 item_subinv.LAST_UPDATED_BY,
2920 item_subinv.CREATION_DATE,
2921 item_subinv.CREATED_BY,
2922 item_subinv.LAST_UPDATE_LOGIN,
2923 item_subinv.MIN_MINMAX_QUANTITY,
2924 item_subinv.MAX_MINMAX_QUANTITY,
2925 item_subinv.INVENTORY_PLANNING_CODE)
2926 VALUES (sq.INVENTORY_ITEM_ID,
2927 sq.ORGANIZATION_ID,
2928 sq.SECONDARY_INVENTORY,
2929 sysdate,
2930 FND_GLOBAL.user_id,
2931 sysdate,
2932 FND_GLOBAL.user_id,
2933 FND_GLOBAL.conc_login_id,
2934 sq.RECOMMENDED_MIN_QUANTITY,
2935 sq.RECOMMENDED_MAX_QUANTITY,2);
2936 COMMIT;
2937 */
2938 FOR usg_hdr_rec in c_usg_hdr_subinv LOOP
2939 UPDATE MTL_ITEM_SUB_INVENTORIES item_subinv
2940 SET MIN_MINMAX_QUANTITY = usg_hdr_rec.RECOMMENDED_MIN_QUANTITY,
2941 MAX_MINMAX_QUANTITY = usg_hdr_rec.RECOMMENDED_MAX_QUANTITY,
2942 INVENTORY_PLANNING_CODE = 2
2943 WHERE item_subinv.INVENTORY_ITEM_ID = usg_hdr_rec.INVENTORY_ITEM_ID
2944 and item_subinv.ORGANIZATION_ID = usg_hdr_rec.ORGANIZATION_ID
2945 and item_subinv.SECONDARY_INVENTORY = usg_hdr_rec.SECONDARY_INVENTORY;
2946 IF SQL%NOTFOUND THEN
2947 INSERT INTO MTL_ITEM_SUB_INVENTORIES
2948 (INVENTORY_ITEM_ID,
2949 ORGANIZATION_ID,
2950 SECONDARY_INVENTORY,
2951 LAST_UPDATE_DATE,
2952 LAST_UPDATED_BY,
2953 CREATION_DATE,
2954 CREATED_BY,
2955 LAST_UPDATE_LOGIN,
2956 MIN_MINMAX_QUANTITY,
2957 MAX_MINMAX_QUANTITY,
2958 INVENTORY_PLANNING_CODE)
2959 VALUES (usg_hdr_rec.INVENTORY_ITEM_ID,
2960 usg_hdr_rec.ORGANIZATION_ID,
2961 usg_hdr_rec.SECONDARY_INVENTORY,
2962 sysdate,
2963 FND_GLOBAL.user_id,
2964 sysdate,
2965 FND_GLOBAL.user_id,
2966 FND_GLOBAL.conc_login_id,
2967 usg_hdr_rec.RECOMMENDED_MIN_QUANTITY,
2968 usg_hdr_rec.RECOMMENDED_MAX_QUANTITY,2);
2969 END IF;
2970
2971 UPDATE CSP_USAGE_HEADERS
2972 SET PROCESS_STATUS = 'C'
2973 WHERE INVENTORY_ITEM_ID = usg_hdr_rec.INVENTORY_ITEM_ID
2974 AND ORGANIZATION_ID = usg_hdr_rec.ORGANIZATION_ID
2975 AND SECONDARY_INVENTORY = usg_hdr_rec.SECONDARY_INVENTORY
2976 AND HEADER_DATA_TYPE = 1
2977 AND PROCESS_STATUS = 'O';
2978
2979 END LOOP;
2980
2981 COMMIT;
2982
2983 -- Update Item/Organization Min/Max values
2984 /* To fix R12 BUG 5548326 Modified the whole Apply_Business_Rules procedure to same as 115.10 version */
2985 FOR usg_hdr_rec in c_usg_hdr_item LOOP
2986 UPDATE mtl_system_items_b mtl_items
2987 Set min_minmax_quantity = usg_hdr_rec.recommended_min_quantity,
2988 max_minmax_quantity = usg_hdr_rec.recommended_max_quantity,
2989 mtl_items.inventory_planning_code = 2
2990 WHERE INVENTORY_ITEM_ID = usg_hdr_rec.INVENTORY_ITEM_ID
2991 AND ORGANIZATION_ID = usg_hdr_rec.ORGANIZATION_ID;
2992
2993 UPDATE CSP_USAGE_HEADERS
2994 SET PROCESS_STATUS = 'C'
2995 WHERE INVENTORY_ITEM_ID = usg_hdr_rec.INVENTORY_ITEM_ID
2996 AND ORGANIZATION_ID = usg_hdr_rec.ORGANIZATION_ID
2997 AND HEADER_DATA_TYPE = 4
2998 AND PROCESS_STATUS = 'O';
2999 END LOOP;
3000
3001 COMMIT;
3002 End Apply_Business_Rules;
3003
3004 PROCEDURE Create_Usage_rollup (
3005 retcode OUT NOCOPY NUMBER,
3006 errbuf OUT NOCOPY VARCHAR2,
3007 P_Api_Version_Number IN NUMBER
3008 )
3009 IS
3010
3011 l_api_name constant varchar2(30) := 'Create_Rollup';
3012 l_api_version_number CONSTANT NUMBER := 1.0;
3013 l_return_status_full VARCHAR2(1);
3014 l_sqlcode NUMBER;
3015 l_sqlerrm Varchar2(2000);
3016
3017 l_Msg_Count NUMBER;
3018 l_Msg_Data Varchar2(2000);
3019
3020 l_Init_Msg_List VARCHAR2(1) := FND_API.G_TRUE;
3021 l_Commit VARCHAR2(1) := FND_API.G_TRUE;
3022 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
3023
3024 l_get_app_info boolean;
3025 l_status varchar2(1);
3026 l_industry varchar2(1);
3027 l_oracle_schema varchar2(30);
3028
3029 X_Return_Status VARCHAR2(1);
3030 X_Msg_Count NUMBER;
3031 X_Msg_Data VARCHAR2(2000);
3032
3033
3034 BEGIN
3035 -- Standard Start of API savepoint
3036 SAVEPOINT CREATE_ROLLUP_PVT;
3037
3038 l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
3039
3040 --
3041 -- API body
3042 --
3043
3044 --- Refresh Organization and Hierarchy usage Snapshots
3045 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_REGS';
3046
3047 insert into csp_usage_regs(
3048 planning_parameters_id,
3049 inventory_item_id,
3050 quantity,
3051 period_start_date,
3052 level_id)
3053 select cpp.parent_node_id,
3054 cuh.inventory_item_id,
3055 sum(cuh.quantity),
3056 cuh.period_start_date,
3057 substr(cpp.level_id,1,instr(cpp.level_id,'.',-1,1) - 1)
3058 from csp_planning_parameters cpp,
3059 csp_usage_histories cuh
3060 Where cpp.node_type = 'SUBINVENTORY'
3061 and cuh.history_data_type = 1
3062 and cuh.organization_id = cpp.organization_id
3063 and cuh.subinventory_code = cpp.secondary_inventory
3064 group by cpp.parent_node_id,cuh.inventory_item_id,cuh.period_start_date,
3065 substr(cpp.level_id,1,instr(cpp.level_id,'.',-1,1) - 1);
3066
3067 retcode := 0;
3068 --
3069 -- End of API body
3070 --
3071
3072 -- Standard check for p_commit
3073 IF FND_API.to_Boolean( l_commit )
3074 THEN
3075 COMMIT WORK;
3076 END IF;
3077
3078 -- Standard call to get message count and if count is 1, get message info.
3079 FND_MSG_PUB.Count_And_Get
3080 ( p_count => x_msg_count,
3081 p_data => x_msg_data
3082 );
3083 EXCEPTION
3084 WHEN FND_API.G_EXC_ERROR THEN
3085 retcode := 2;
3086 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3087 P_API_NAME => L_API_NAME
3088 ,P_PKG_NAME => G_PKG_NAME
3089 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3090 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
3091 ,P_ROLLBACK_FLAG => 'N'
3092 ,X_MSG_COUNT => X_MSG_COUNT
3093 ,X_MSG_DATA => X_MSG_DATA
3094 ,X_RETURN_STATUS => X_RETURN_STATUS);
3095
3096 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3097 retcode := 2;
3098 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3099 P_API_NAME => L_API_NAME
3100 ,P_PKG_NAME => G_PKG_NAME
3101 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3102 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
3103 ,P_ROLLBACK_FLAG => 'N'
3104 ,X_MSG_COUNT => X_MSG_COUNT
3105 ,X_MSG_DATA => X_MSG_DATA
3106 ,X_RETURN_STATUS => X_RETURN_STATUS);
3107
3108 WHEN OTHERS THEN
3109 l_sqlcode := SQLCODE;
3110 l_sqlerrm := SQLERRM;
3111 retcode := 2;
3112 errbuf := SQLERRM;
3113 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3114 P_API_NAME => L_API_NAME
3115 ,P_PKG_NAME => G_PKG_NAME
3116 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
3117 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
3118 ,P_ROLLBACK_FLAG => 'N'
3119 ,P_SQLCODE => l_sqlcode
3120 ,P_SQLERRM => l_sqlerrm
3121 ,X_MSG_COUNT => X_MSG_COUNT
3122 ,X_MSG_DATA => X_MSG_DATA
3123 ,X_RETURN_STATUS => X_RETURN_STATUS);
3124 End Create_Usage_Rollup;
3125
3126 PROCEDURE Calculate_Needby_date (
3127 P_Api_Version_Number IN NUMBER,
3128 P_Init_Msg_List IN VARCHAR2,
3129 P_Commit IN VARCHAR2,
3130 P_validation_level IN NUMBER,
3131 P_inventory_item_id IN NUMBER,
3132 P_Organization_id IN NUMBER,
3133 P_Onhand_Quantity IN NUMBER,
3134 X_Needby_date OUT NOCOPY DATE,
3135 X_Return_Status OUT NOCOPY VARCHAR2,
3136 X_Msg_Count OUT NOCOPY NUMBER,
3137 X_Msg_Data OUT NOCOPY VARCHAR2
3138 ) IS
3139 cursor c_usage_headers(p_item_id number,p_org_id number) is
3140 select nvl(cuh.standard_deviation,0) standard_deviation,
3141 cuh.awu ,
3142 cuh.item_cost,
3143 cuh.lead_time,
3144 cpp.edq_factor,
3145 cpp.service_level
3146 from csp_usage_headers cuh,
3147 csp_planning_parameters cpp
3148 where cuh.inventory_item_id = p_item_id
3149 and cuh.organization_id = p_org_id
3150 and cuh.secondary_inventory = '-'
3151 and cuh.header_data_type = 4
3152 and cpp.organization_id = p_org_id
3153 and cpp.secondary_inventory is null;
3154
3155 l_usage_headers_rec c_usage_headers%ROWTYPE;
3156
3157 l_api_name constant varchar2(30) := 'calculate_needby';
3158 l_api_version_number CONSTANT NUMBER := 1.0;
3159 l_return_status_full VARCHAR2(1);
3160 l_return_status NUMBER;
3161 l_sqlcode NUMBER;
3162 l_sqlerrm Varchar2(2000);
3163 l_safety_stock Number;
3164
3165 l_Msg_Count NUMBER;
3166 l_Msg_Data Varchar2(2000);
3167
3168 l_Init_Msg_List VARCHAR2(1) := FND_API.G_TRUE;
3169 l_Commit VARCHAR2(1) := FND_API.G_TRUE;
3170 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
3171
3172
3173 BEGIN
3174 -- Standard Start of API savepoint
3175 SAVEPOINT CALCULATE_NEEDBY_PVT;
3176
3177 -- Standard call to check for call compatibility.
3178 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3179 p_api_version_number,
3180 l_api_name,
3181 G_PKG_NAME)
3182 THEN
3183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3184 END IF;
3185
3186
3187 -- Initialize message list if p_init_msg_list is set to TRUE.
3188 IF FND_API.to_Boolean( p_init_msg_list )
3189 THEN
3190 FND_MSG_PUB.initialize;
3191 END IF;
3192
3193 -- Initialize API return status to SUCCESS
3194 x_return_status := FND_API.G_RET_STS_SUCCESS;
3195
3196 --
3197 -- API body
3198 --
3199 Open c_usage_headers(p_inventory_item_id,p_organization_id);
3200 Fetch c_usage_headers into l_usage_headers_rec;
3201 Close c_usage_headers;
3202
3203 l_safety_stock := csp_pick_utils.get_safety_stock(
3204 p_subinventory => null,
3205 p_organization_id => null,
3206 p_edq_factor => l_usage_headers_rec.edq_factor,
3207 p_service_level => l_usage_headers_rec.service_level,
3208 p_item_cost => l_usage_headers_rec.item_cost,
3209 p_awu => l_usage_headers_rec.awu,
3210 p_lead_time => l_usage_headers_rec.lead_time,
3211 p_standard_deviation => l_usage_headers_rec.standard_deviation,
3212 p_safety_stock_flag => 'Y',
3213 p_asl_flag => NULL);
3214 If nvl(l_usage_headers_rec.awu,0) > 0 Then
3215 X_needby_date := trunc(sysdate) +
3216 ((p_onhand_quantity - l_safety_stock)/
3217 l_usage_headers_rec.awu) * 7;
3218 If x_needby_date < trunc(sysdate) Then
3219 x_needby_date := trunc(sysdate);
3220 end if;
3221 end if;
3222
3223 --
3224 -- End of API body
3225 --
3226
3227 -- Standard check for p_commit
3228 IF FND_API.to_Boolean( l_commit )
3229 THEN
3230 COMMIT WORK;
3231 END IF;
3232
3233 -- Standard call to get message count and if count is 1, get message info.
3234 FND_MSG_PUB.Count_And_Get
3235 ( p_count => x_msg_count,
3236 p_data => x_msg_data
3237 );
3238
3239 EXCEPTION
3240 WHEN FND_API.G_EXC_ERROR THEN
3241 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3242 P_API_NAME => L_API_NAME
3243 ,P_PKG_NAME => G_PKG_NAME
3244 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3245 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
3246 ,P_ROLLBACK_FLAG => 'N'
3247 ,X_MSG_COUNT => X_MSG_COUNT
3248 ,X_MSG_DATA => X_MSG_DATA
3249 ,X_RETURN_STATUS => X_RETURN_STATUS);
3250
3251 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3252 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3253 P_API_NAME => L_API_NAME
3254 ,P_PKG_NAME => G_PKG_NAME
3255 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3256 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
3257 ,P_ROLLBACK_FLAG => 'N'
3258 ,X_MSG_COUNT => X_MSG_COUNT
3259 ,X_MSG_DATA => X_MSG_DATA
3260 ,X_RETURN_STATUS => X_RETURN_STATUS);
3261
3262 WHEN OTHERS THEN
3263 l_sqlcode := SQLCODE;
3264 l_sqlerrm := SQLERRM;
3265 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3266 P_API_NAME => L_API_NAME
3267 ,P_PKG_NAME => G_PKG_NAME
3268 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
3269 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
3270 ,P_ROLLBACK_FLAG => 'N'
3271 ,P_SQLCODE => l_sqlcode
3272 ,P_SQLERRM => l_sqlerrm
3273 ,X_MSG_COUNT => X_MSG_COUNT
3274 ,X_MSG_DATA => X_MSG_DATA
3275 ,X_RETURN_STATUS => X_RETURN_STATUS);
3276 End Calculate_needby_date;
3277
3278 End CSP_AUTO_ASLMSL_PVT;