[Home] [Help]
PACKAGE BODY: APPS.CSP_AUTO_ASLMSL_PVT
Source
1 PACKAGE BODY CSP_AUTO_ASLMSL_PVT as
2 /* $Header: cspvaslb.pls 120.11 2008/05/07 15:59:45 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_org_mv 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 EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW CSP_USAGE_ORG_MV COMPILE';
848 EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW CSP_USAGE_REG_MV COMPILE';
849
850 -- Standard call to get message count and if count is 1, get message info.
851 FND_MSG_PUB.Count_And_Get
852 ( p_count => x_msg_count,
853 p_data => x_msg_data
854 );
855 retcode := 0;
856 EXCEPTION
857 WHEN FND_API.G_EXC_ERROR THEN
858 Add_Err_Msg;
859 retcode := 2;
860 errbuf := X_Msg_Data;
861 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
862 P_API_NAME => L_API_NAME
863 ,P_PKG_NAME => G_PKG_NAME
864 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
865 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
866 ,P_ROLLBACK_FLAG => l_Rollback
867 ,X_MSG_COUNT => X_MSG_COUNT
868 ,X_MSG_DATA => X_MSG_DATA
869 ,X_RETURN_STATUS => X_RETURN_STATUS);
870 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
871 Add_Err_Msg;
872 retcode := 2;
873 errbuf := X_Msg_Data;
874 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
875 P_API_NAME => L_API_NAME
876 ,P_PKG_NAME => G_PKG_NAME
877 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
878 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
879 ,P_ROLLBACK_FLAG => l_Rollback
880 ,X_MSG_COUNT => X_MSG_COUNT
881 ,X_MSG_DATA => X_MSG_DATA
882 ,X_RETURN_STATUS => X_RETURN_STATUS);
883 Add_Err_Msg;
884 WHEN OTHERS THEN
885 l_sqlcode := SQLCODE;
886 l_sqlerrm := SQLERRM;
887 retcode := 2;
888 errbuf := SQLERRM;
889 fnd_message.set_name('CSP','CSP_ASL_MSG');
890 fnd_message.set_token('VALUE',l_sqlcode || ' ' || l_sqlerrm);
891 fnd_msg_pub.add;
892 Add_Err_Msg;
893 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
894 P_API_NAME => L_API_NAME
895 ,P_PKG_NAME => G_PKG_NAME
896 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
897 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
898 ,P_SQLCODE => l_sqlcode
899 ,P_SQLERRM => l_sqlerrm
900 ,P_ROLLBACK_FLAG => l_Rollback
901 ,X_MSG_COUNT => X_MSG_COUNT
902 ,X_MSG_DATA => X_MSG_DATA
903 ,X_RETURN_STATUS => X_RETURN_STATUS);
904 errbuf := sqlerrm;
905 retcode := 2;
906 Add_Err_Msg;
907 End Generate_Recommendations;
908
909
910 PROCEDURE Purge_Planning_Data (
911 P_Api_Version_Number IN NUMBER,
912 P_Init_Msg_List IN VARCHAR2,
913 P_Commit IN VARCHAR2,
914 P_validation_level IN NUMBER ,
915 X_Return_Status OUT NOCOPY VARCHAR2,
916 X_Msg_Count OUT NOCOPY NUMBER,
917 X_Msg_Data OUT NOCOPY VARCHAR2) IS
918
919 l_api_name CONSTANT VARCHAR2(30) := 'Purge_Planning_data' ;
920 l_api_version_number CONSTANT NUMBER := 1.0;
921 l_return_status_full VARCHAR2(1);
922 l_Sqlcode NUMBER;
923 l_Sqlerrm Varchar2(2000);
924
925 l_String VARCHAR2(2000);
926
927 l_Msg_Count NUMBER;
928 l_Msg_Data Varchar2(2000);
929 l_get_app_info boolean;
930 l_status varchar2(1);
931 l_industry varchar2(1);
932 l_oracle_schema varchar2(30);
933 BEGIN
934 -- Standard Start of API savepoint
935 SAVEPOINT PURGE_DATA_PVT;
936
937 -- Standard call to check for call compatibility.
938 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
939 p_api_version_number,
940 l_api_name,
941 G_PKG_NAME)
942 THEN
943 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
944 END IF;
945
946
947 -- Initialize message list if p_init_msg_list is set to TRUE.
948 IF FND_API.to_Boolean( p_init_msg_list )
949 THEN
950 FND_MSG_PUB.initialize;
951 END IF;
952
953
954 -- Initialize API return status to SUCCESS
955 x_return_status := FND_API.G_RET_STS_SUCCESS;
956
957 --
958 -- API body
959 --
960 l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
961 -- Clean up the tables
962
963 -- Delete from Csp_Usage_Headers
964 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_HEADERS';
965
966
967 -- Delete from Csp_Usage_Histories
968 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_USAGE_HISTORIES';
969
970 -- Delete from Csp_Supply_Chain
971 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
972
973
974
975 IF FND_API.to_Boolean( p_commit )
976 THEN
977 COMMIT WORK;
978 END IF;
979
980 --
981 -- End of API body
982 --
983
984 -- Standard check for p_commit
985 IF FND_API.to_Boolean( p_commit )
986 THEN
987 COMMIT WORK;
988 END IF;
989
990
991
992 -- Standard call to get message count and if count is 1, get message info.
993 FND_MSG_PUB.Count_And_Get
994 ( p_count => x_msg_count,
995 p_data => x_msg_data
996 );
997
998 EXCEPTION
999 WHEN FND_API.G_EXC_ERROR THEN
1000 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1001 P_API_NAME => L_API_NAME
1002 ,P_PKG_NAME => G_PKG_NAME
1003 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1004 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1005 ,P_ROLLBACK_FLAG => 'N'
1006 ,X_MSG_COUNT => X_MSG_COUNT
1007 ,X_MSG_DATA => X_MSG_DATA
1008 ,X_RETURN_STATUS => X_RETURN_STATUS);
1009
1010 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1011 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1012 P_API_NAME => L_API_NAME
1013 ,P_PKG_NAME => G_PKG_NAME
1014 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1015 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1016 ,P_ROLLBACK_FLAG => 'N'
1017 ,X_MSG_COUNT => X_MSG_COUNT
1018 ,X_MSG_DATA => X_MSG_DATA
1019 ,X_RETURN_STATUS => X_RETURN_STATUS);
1020
1021 WHEN OTHERS THEN
1022 l_Sqlcode := SQLCODE;
1023 l_sqlerrm := SQLERRM;
1024 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1025 P_API_NAME => L_API_NAME
1026 ,P_PKG_NAME => G_PKG_NAME
1027 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1028 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1029 ,P_ROLLBACK_FLAG => 'N'
1030 ,P_SQLCODE => l_Sqlcode
1031 ,P_SQLERRM => l_Sqlerrm
1032 ,X_MSG_COUNT => X_MSG_COUNT
1033 ,X_MSG_DATA => X_MSG_DATA
1034 ,X_RETURN_STATUS => X_RETURN_STATUS);
1035 End Purge_Planning_Data;
1036
1037 PROCEDURE Create_Usage (
1038 retcode OUT NOCOPY NUMBER,
1039 errbuf OUT NOCOPY VARCHAR2,
1040 P_Api_Version_Number IN NUMBER
1041 ) IS
1042 l_api_name CONSTANT VARCHAR2(30) := 'Create_Usage';
1043 l_api_version_number CONSTANT NUMBER := 1.0;
1044 l_return_status_full VARCHAR2(1);
1045 l_Sqlcode NUMBER;
1046 l_Sqlerrm Varchar2(2000);
1047 l_stmt Varchar2(2000);
1048
1049
1050 l_Msg_Count NUMBER;
1051 l_Msg_Data Varchar2(2000);
1052
1053 X_Return_Status VARCHAR2(1);
1054 X_Msg_Count NUMBER;
1055 X_Msg_Data VARCHAR2(2000);
1056
1057 l_Init_Msg_List VARCHAR2(1) := FND_API.G_TRUE;
1058 l_Commit VARCHAR2(1) := FND_API.G_TRUE;
1059 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
1060
1061 l_get_app_info boolean;
1062 l_status varchar2(1);
1063 l_industry varchar2(1);
1064 l_oracle_schema varchar2(30);
1065 BEGIN
1066 -- Alter session
1067 EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
1068 -- Standard call to check for call compatibility.
1069 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1070 p_api_version_number,
1071 l_api_name,
1072 G_PKG_NAME)
1073 THEN
1074 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1075 END IF;
1076
1077
1078 -- Initialize message list if p_init_msg_list is set to TRUE.
1079 IF FND_API.to_Boolean( l_init_msg_list )
1080 THEN
1081 FND_MSG_PUB.initialize;
1082 END IF;
1083
1084
1085
1086 -- Initialize API return status to SUCCESS
1087 x_return_status := FND_API.G_RET_STS_SUCCESS;
1088
1089 --
1090 -- API body
1091 --
1092
1093 --G_LAST_RUN_DATE := fnd_profile.Value('CSP_USAGE_RUN_DATE');
1094 Begin
1095 select PROFILE_OPTION_VALUE
1096 into G_LAST_RUN_DATE
1097 from fnd_profile_option_values
1098 where APPLICATION_ID = 523
1099 and PROFILE_OPTION_ID in
1100 (select profile_option_id
1101 from fnd_profile_options
1102 where profile_option_name = ('CSP_USAGE_RUN_DATE'));
1103 Exception
1104 When others then
1105 G_LAST_RUN_DATE := NULL;
1106 End;
1107
1108 G_LAST_RUN_DATE := NVL(G_LAST_RUN_DATE,FND_API.G_MISS_DATE);
1109 If G_LAST_RUN_DATE < trunc(sysdate) - 1 Then
1110 l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
1111 -- Delete from CSP_SUPPLY_CHAIN
1112 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN';
1113 -- Standard Start of API savepoint
1114 SAVEPOINT CREATE_USAGE_PVT;
1115 -- Build Supply Chain
1116 Create_Supply_Chain (
1117 P_Api_Version_Number => 1.0,
1118 P_Init_Msg_List => FND_API.G_FALSE,
1119 P_Commit => FND_API.G_TRUE,
1120 P_validation_level => FND_API.G_VALID_LEVEL_FULL,
1121 X_Return_Status => X_Return_Status,
1122 X_Msg_Count => X_Msg_Count,
1123 X_Msg_Data => X_Msg_Data );
1124 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
1125 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1126 End If;
1127
1128 -- Re-establish Savepoint
1129 SAVEPOINT CREATE_USAGE_PVT;
1130
1131 --- Get Usage
1132 Create_Usage_History (
1133 P_Api_Version_Number => 1.0,
1134 P_Init_Msg_List => FND_API.G_FALSE,
1135 P_Commit => FND_API.G_TRUE,
1136 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1137 X_Return_Status => X_Return_Status,
1138 X_Msg_Count => X_Msg_Count,
1139 X_Msg_Data => X_Msg_Data
1140 );
1141 If X_Return_status <> FND_API.G_RET_STS_SUCCESS Then
1142 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1143 End If;
1144 -- Re-establish Savepoint
1145 SAVEPOINT CREATE_USAGE_PVT;
1146 if not fnd_profile.save('CSP_USAGE_RUN_DATE',trunc(sysdate) -1,'SITE') Then
1147 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1148 End If;
1149 COMMIT;
1150 -- Re-establish Savepoint
1151 SAVEPOINT CREATE_USAGE_PVT;
1152 --- Refresh Organization rollup snapshot
1153 DBMS_MVIEW.REFRESH('CSP_USAGE_ORG_MV','C');
1154 --- Refresh other rollup snapshot
1155 Create_Usage_Rollup ( P_Api_Version_Number => 1.0,
1156 retcode => retcode,
1157 errbuf => errbuf);
1158 End If;
1159
1160
1161 --
1162 -- End of API body
1163 --
1164 -- Standard call to get message count and if count is 1, get message info.
1165 FND_MSG_PUB.Count_And_Get
1166 ( p_count => x_msg_count,
1167 p_data => x_msg_data
1168 );
1169 retcode := 0;
1170 EXCEPTION
1171 WHEN FND_API.G_EXC_ERROR THEN
1172 retcode := 2;
1173 errbuf := X_Msg_Data;
1174 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1175 P_API_NAME => L_API_NAME
1176 ,P_PKG_NAME => G_PKG_NAME
1177 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1178 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1179 ,P_ROLLBACK_FLAG => 'Y'
1180 ,X_MSG_COUNT => X_MSG_COUNT
1181 ,X_MSG_DATA => X_MSG_DATA
1182 ,X_RETURN_STATUS => X_RETURN_STATUS);
1183 Add_Err_Msg;
1184 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1185 retcode := 2;
1186 errbuf := X_Msg_Data;
1187 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1188 P_API_NAME => L_API_NAME
1189 ,P_PKG_NAME => G_PKG_NAME
1190 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1191 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1192 ,P_ROLLBACK_FLAG => 'Y'
1193 ,X_MSG_COUNT => X_MSG_COUNT
1194 ,X_MSG_DATA => X_MSG_DATA
1195 ,X_RETURN_STATUS => X_RETURN_STATUS);
1196
1197 Add_Err_Msg;
1198 WHEN OTHERS THEN
1199 l_Sqlcode := SQLCODE;
1200 l_Sqlerrm := SQLERRM;
1201 retcode := 2;
1202 errbuf := SQLERRM;
1203 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1204 P_API_NAME => L_API_NAME
1205 ,P_PKG_NAME => G_PKG_NAME
1206 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1207 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1208 ,P_ROLLBACK_FLAG => 'Y'
1209 ,P_SQLCODE => l_Sqlcode
1210 ,P_SQLERRM => l_Sqlerrm
1211 ,X_MSG_COUNT => X_MSG_COUNT
1212 ,X_MSG_DATA => X_MSG_DATA
1213 ,X_RETURN_STATUS => X_RETURN_STATUS);
1214 Add_Err_Msg;
1215 END Create_Usage;
1216
1217 PROCEDURE Calculate_Forecast (
1218 P_Api_Version_Number IN NUMBER,
1219 P_Init_Msg_List IN VARCHAR2,
1220 P_Commit IN VARCHAR2 ,
1221 P_validation_level IN NUMBER ,
1222 P_Level_Id IN VARCHAR2,
1223 P_Reason_Code IN VARCHAR2,
1224 X_Return_Status OUT NOCOPY VARCHAR2,
1225 X_Msg_Count OUT NOCOPY NUMBER,
1226 X_Msg_Data OUT NOCOPY VARCHAR2
1227 )
1228 IS
1229
1230
1231 l_ts_cycle NUMBER := 0;
1232 l_max_ts_cycle NUMBER := 0;
1233
1234 Cursor c_Usage_Details IS
1235 Select nvl(csi.item_supplied,cuh.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,
1236 cuh.ORGANIZATION_ID,
1237 cuh.SUBINVENTORY_CODE,
1238 (trunc(sysdate)- cfrb.forecast_periods * cfrb.period_size * l_ts_cycle)
1239 - ROUND((TO_NUMBER((trunc(sysdate)- cfrb.forecast_periods* cfrb.period_size) -
1240 cuh.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size PERIOD_START_DATE,
1241 sum(cuh.QUANTITY) QUANTITY,
1242 5 HISTORY_DATA_TYPE,
1243 cfrb.HISTORY_PERIODS,
1244 cfrb.forecast_rule_id,
1245 DECODE(cfrb.FORECAST_METHOD,4,cfrb.FORECAST_PERIODS,1) FORECAST_PERIODS ,
1246 cfrb.FORECAST_METHOD,
1247 cfrb.PERIOD_SIZE,
1248 cfrb.ALPHA,
1249 cfrb.BETA,
1250 cfrb.WEIGHTED_AVG_PERIOD1,
1251 cfrb.WEIGHTED_AVG_PERIOD2,
1252 cfrb.WEIGHTED_AVG_PERIOD3,
1253 cfrb.WEIGHTED_AVG_PERIOD4,
1254 cfrb.WEIGHTED_AVG_PERIOD5,
1255 cfrb.WEIGHTED_AVG_PERIOD6,
1256 cfrb.WEIGHTED_AVG_PERIOD7,
1257 cfrb.WEIGHTED_AVG_PERIOD8,
1258 cfrb.WEIGHTED_AVG_PERIOD9,
1259 cfrb.WEIGHTED_AVG_PERIOD10,
1260 cfrb.WEIGHTED_AVG_PERIOD11,
1261 cfrb.WEIGHTED_AVG_PERIOD12,
1262 cpp.RECOMMEND_METHOD
1263 From CSP_PLANNING_PARAMETERS cpp,
1264 CSP_USAGE_HISTORIES cuh,
1265 CSP_SUPERSEDE_ITEMS csi,
1266 CSP_FORECAST_RULES_B cfrb
1267 Where cpp.level_id like P_Level_Id || '%'
1268 And cpp.node_type = 'SUBINVENTORY'
1269 And cpp.recommend_method in ('USAGE','USAGE_PNORM','USAGE_TNORM','PNORM','TNORM')
1270 And cuh.organization_id = cpp.organization_id
1271 And cuh.subinventory_code = cpp.secondary_inventory
1272 And cuh.HISTORY_DATA_TYPE = 1
1273 And (cuh.PERIOD_START_DATE BETWEEN (trunc(sysdate) - cfrb.forecast_periods
1274 * cfrb.period_size * l_ts_cycle) - cfrb.period_size * cfrb.history_periods - (cfrb.period_size - 1)
1275 AND (trunc(sysdate) - cfrb.forecast_periods * period_size * l_ts_cycle))
1276 And cuh.transaction_type_id in (select transaction_type_id
1277 from csp_usg_transaction_types cutt
1278 where cutt.forecast_rule_id = cpp.forecast_rule_id)
1279 And csi.inventory_item_id(+) = cuh.inventory_item_id
1280 And csi.organization_id (+) = cuh.organization_id
1281 And csi.sub_inventory_code(+) = cuh.subinventory_code
1282 AND cfrb.FORECAST_RULE_ID = cpp.FORECAST_RULE_ID
1283 AND cfrb.tracking_signal_cycle >= l_ts_cycle
1284 Group By nvl(csi.item_supplied,cuh.INVENTORY_ITEM_ID) ,
1285 cuh.ORGANIZATION_ID,
1286 cuh.SUBINVENTORY_CODE,
1287 (trunc(sysdate)- cfrb.forecast_periods * cfrb.period_size * l_ts_cycle)
1288 - ROUND((TO_NUMBER((trunc(sysdate)- cfrb.forecast_periods* cfrb.period_size) -
1289 cuh.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size,
1290 cfrb.HISTORY_PERIODS,
1291 cfrb.forecast_rule_id,
1292 DECODE(cfrb.FORECAST_METHOD,4,cfrb.FORECAST_PERIODS,1) ,
1293 cfrb.FORECAST_METHOD,
1294 cfrb.PERIOD_SIZE,
1295 cfrb.ALPHA,
1296 cfrb.BETA,
1297 cfrb.WEIGHTED_AVG_PERIOD1,
1298 cfrb.WEIGHTED_AVG_PERIOD2,
1299 cfrb.WEIGHTED_AVG_PERIOD3,
1300 cfrb.WEIGHTED_AVG_PERIOD4,
1301 cfrb.WEIGHTED_AVG_PERIOD5,
1302 cfrb.WEIGHTED_AVG_PERIOD6,
1303 cfrb.WEIGHTED_AVG_PERIOD7,
1304 cfrb.WEIGHTED_AVG_PERIOD8,
1305 cfrb.WEIGHTED_AVG_PERIOD9,
1306 cfrb.WEIGHTED_AVG_PERIOD10,
1307 cfrb.WEIGHTED_AVG_PERIOD11,
1308 cfrb.WEIGHTED_AVG_PERIOD12,
1309 cpp.RECOMMEND_METHOD
1310 UNION ALL
1311 Select nvl(csi.item_supplied,cuom.INVENTORY_ITEM_ID),
1312 cuom.ORGANIZATION_ID,
1313 '-' SUBINVENTORY_CODE ,
1314 (trunc(sysdate)- cfrb.forecast_periods * cfrb.period_size * l_ts_cycle)
1315 - ROUND((TO_NUMBER((trunc(sysdate)- cfrb.forecast_periods* cfrb.period_size) -
1316 cuom.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size PERIOD_START_DATE,
1317 sum(cuom.QUANTITY) QUANTITY,
1318 6 HISTORY_DATA_TYPE,
1319 cfrb.HISTORY_PERIODS,
1320 cfrb.forecast_rule_id,
1321 DECODE(cfrb.FORECAST_METHOD,4,cfrb.FORECAST_PERIODS,1) FORECAST_PERIODS ,
1322 cfrb.FORECAST_METHOD,
1323 cfrb.PERIOD_SIZE,
1324 cfrb.ALPHA,
1325 cfrb.BETA,
1326 cfrb.WEIGHTED_AVG_PERIOD1,
1327 cfrb.WEIGHTED_AVG_PERIOD2,
1328 cfrb.WEIGHTED_AVG_PERIOD3,
1329 cfrb.WEIGHTED_AVG_PERIOD4,
1330 cfrb.WEIGHTED_AVG_PERIOD5,
1331 cfrb.WEIGHTED_AVG_PERIOD6,
1332 cfrb.WEIGHTED_AVG_PERIOD7,
1333 cfrb.WEIGHTED_AVG_PERIOD8,
1334 cfrb.WEIGHTED_AVG_PERIOD9,
1335 cfrb.WEIGHTED_AVG_PERIOD10,
1336 cfrb.WEIGHTED_AVG_PERIOD11,
1337 cfrb.WEIGHTED_AVG_PERIOD12,
1338 cpp.RECOMMEND_METHOD
1339 From CSP_PLANNING_PARAMETERS cpp,
1340 CSP_USAGE_ORG_MV cuom,
1341 CSP_SUPERSEDE_ITEMS csi,
1342 CSP_FORECAST_RULES_B cfrb
1343 Where cpp.level_id like P_Level_Id || '%'
1344 And cpp.node_type = 'ORGANIZATION_WH'
1345 And cpp.recommend_method in ('USAGE','USAGE_PNORM','USAGE_TNORM','PNORM','TNORM')
1346 And (cuom.PERIOD_START_DATE BETWEEN (trunc(sysdate) - cfrb.forecast_periods
1347 * cfrb.period_size * l_ts_cycle) - cfrb.period_size * cfrb.history_periods - (cfrb.period_size - 1)
1348 AND (trunc(sysdate) - cfrb.forecast_periods * period_size * l_ts_cycle))
1349 And cuom.ORGANIZATION_ID = cpp.ORGANIZATION_ID
1350 And csi.inventory_item_id(+) = cuom.inventory_item_id
1351 And csi.organization_id (+) = cuom.organization_id
1352 And csi.sub_inventory_code(+) = '-'
1353 AND cfrb.FORECAST_RULE_ID = cpp.FORECAST_RULE_ID
1354 Group By nvl(csi.item_supplied,cuom.INVENTORY_ITEM_ID),
1355 cuom.ORGANIZATION_ID,
1356 (trunc(sysdate)- cfrb.forecast_periods * cfrb.period_size * l_ts_cycle)
1357 - ROUND((TO_NUMBER((trunc(sysdate)- cfrb.forecast_periods* cfrb.period_size) -
1358 cuom.period_start_date)/cfrb.period_size+0.5)) * cfrb.period_size,
1359 cfrb.HISTORY_PERIODS,
1360 cfrb.forecast_rule_id,
1361 DECODE(cfrb.FORECAST_METHOD,4,cfrb.FORECAST_PERIODS,1) ,
1362 cfrb.FORECAST_METHOD,
1363 cfrb.PERIOD_SIZE,
1364 cfrb.ALPHA,
1365 cfrb.BETA,
1366 cfrb.WEIGHTED_AVG_PERIOD1,
1367 cfrb.WEIGHTED_AVG_PERIOD2,
1368 cfrb.WEIGHTED_AVG_PERIOD3,
1369 cfrb.WEIGHTED_AVG_PERIOD4,
1370 cfrb.WEIGHTED_AVG_PERIOD5,
1371 cfrb.WEIGHTED_AVG_PERIOD6,
1372 cfrb.WEIGHTED_AVG_PERIOD7,
1373 cfrb.WEIGHTED_AVG_PERIOD8,
1374 cfrb.WEIGHTED_AVG_PERIOD9,
1375 cfrb.WEIGHTED_AVG_PERIOD10,
1376 cfrb.WEIGHTED_AVG_PERIOD11,
1377 cfrb.WEIGHTED_AVG_PERIOD12,
1378 cpp.RECOMMEND_METHOD
1379 Order By 1,2,3,4;
1380
1381 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_Forecast';
1382 l_api_version_number CONSTANT NUMBER := 1.0;
1383 l_return_status_full VARCHAR2(1);
1384 l_Sqlcode NUMBER;
1385 l_sqlerrm Varchar2(2000);
1386 l_Start_Date DATE;
1387
1388 l_usage_Details_Rec c_Usage_Details%ROWTYPE;
1389 l_prev_Rec c_Usage_Details%ROWTYPE;
1390 l_Dates_Table CSP_AUTO_ASLMSL_PVT.CSP_Date_Tbl_Type;
1391 l_Usage_Qty_Tbl Csp_Forecast_Pvt.T_NUMBER_TABLE;
1392 l_Forecast_Qty_Tbl Csp_Forecast_Pvt.T_NUMBER_TABLE;
1393 l_Weighted_Avg_Tbl Csp_Forecast_Pvt.T_NUMBER_TABLE;
1394
1395 l_Usage_Quantity NUMBER := 0;
1396 l_Forecast_Quantity NUMBER := 0;
1397 l_Usage_Data_Count NUMBER := 0;
1398 l_Fcst_Period_Count NUMBER := 0;
1399 l_Usage_Qty_Sum NUMBER := 0;
1400 l_weeks NUMBER := 0;
1401 l_Awu NUMBER := 0;
1402 l_Variance NUMBER := 0;
1403 l_Standard_Deviation NUMBER := 0;
1404
1405 l_Usage_Id Number;
1406 l_index Number := 0;
1407 l_count Number := 0;
1408 l_i Number;
1409
1410 l_Msg_Count NUMBER;
1411 l_Msg_Data Varchar2(2000);
1412
1413 BEGIN
1414 -- Standard Start of API savepoint
1415 SAVEPOINT CALCULATE_FORECAST_PVT;
1416
1417 -- Standard call to check for call compatibility.
1418 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1419 p_api_version_number,
1420 l_api_name,
1421 G_PKG_NAME)
1422 THEN
1423 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1424 END IF;
1425
1426
1427 -- Initialize message list if p_init_msg_list is set to TRUE.
1428 IF FND_API.to_Boolean( p_init_msg_list )
1429 THEN
1430 FND_MSG_PUB.initialize;
1431 END IF;
1432
1433
1434 -- Initialize API return status to SUCCESS
1435 x_return_status := FND_API.G_RET_STS_SUCCESS;
1436
1437 --
1438 -- API body
1439 --
1440 IF p_reason_code = 'TS' Then
1441 SELECT nvl(max(tracking_signal_cycle),0)
1442 INTO l_max_ts_cycle
1443 FROM csp_forecast_rules_b;
1444 l_ts_cycle := 1;
1445 Else
1446 l_ts_cycle := 0;
1447 l_max_ts_cycle := 0;
1448 END IF;
1449 WHILE l_ts_cycle <= l_max_ts_cycle LOOP
1450 Open c_Usage_Details;
1451 LOOP
1452 Fetch c_Usage_Details INTO l_Usage_Details_Rec;
1453 If (c_Usage_Details%ROWCOUNT > 1 AND
1454 (((l_Usage_Details_rec.Inventory_Item_Id <> l_prev_rec.inventory_Item_Id)OR
1455 (l_Usage_Details_rec.Organization_id <> l_prev_rec.Organization_id) OR
1456 (l_Usage_Details_rec.Subinventory_Code <>
1457 l_prev_rec.Subinventory_code)))) OR (c_Usage_Details%NOTFOUND OR c_Usage_Details%NOTFOUND IS NULL ) Then
1458 For l_i in l_Usage_Qty_Tbl.COUNT..nvl(l_prev_rec.History_Periods,0) Loop
1459 l_Usage_Qty_Tbl(l_i) := 0;
1460 End Loop;
1461 If l_prev_rec.Forecast_Method = 1 Then
1462 CSP_Forecast_Pvt.Simple_Average
1463 (P_Usage_History => l_Usage_Qty_Tbl,
1464 P_History_Periods => l_prev_rec.History_Periods,
1465 P_Forecast_Periods => l_prev_rec.Forecast_Periods,
1466 X_Forecast_Quantities => l_Forecast_Qty_Tbl);
1467 Elsif l_prev_rec.Forecast_Method = 2 Then
1468 l_Weighted_Avg_Tbl(1) := NVL(l_Prev_Rec.Weighted_Avg_Period1,0);
1469 l_Weighted_Avg_Tbl(2) := NVL(l_Prev_Rec.Weighted_Avg_Period2,0);
1470 l_Weighted_Avg_Tbl(3) := NVL(l_Prev_Rec.Weighted_Avg_Period3,0);
1471 l_Weighted_Avg_Tbl(4) := NVL(l_Prev_Rec.Weighted_Avg_Period4,0);
1472 l_Weighted_Avg_Tbl(5) := NVL(l_Prev_Rec.Weighted_Avg_Period5,0);
1473 l_Weighted_Avg_Tbl(6) := NVL(l_Prev_Rec.Weighted_Avg_Period6,0);
1474 l_Weighted_Avg_Tbl(7) := NVL(l_Prev_Rec.Weighted_Avg_Period7,0);
1475 l_Weighted_Avg_Tbl(8) := NVL(l_Prev_Rec.Weighted_Avg_Period8,0);
1476 l_Weighted_Avg_Tbl(9) := NVL(l_Prev_Rec.Weighted_Avg_Period9,0);
1477 l_Weighted_Avg_Tbl(10) := NVL(l_Prev_Rec.Weighted_Avg_Period10,0);
1478 l_Weighted_Avg_Tbl(11) := NVL(l_Prev_Rec.Weighted_Avg_Period11,0);
1479 l_Weighted_Avg_Tbl(12) := NVL(l_Prev_Rec.Weighted_Avg_Period12,0);
1480 For l_i in 13..l_Prev_Rec.History_Periods
1481 Loop
1482
1483 l_Weighted_Avg_Tbl(l_i) := 0;
1484 End Loop;
1485 CSP_Forecast_Pvt.Weighted_Average
1486 (P_Usage_History =>l_Usage_Qty_Tbl,
1487 P_History_Periods =>l_Prev_Rec.History_Periods,
1488 P_Forecast_Periods =>l_Prev_Rec.Forecast_Periods,
1489 P_Weighted_Avg =>l_Weighted_Avg_Tbl,
1490 X_Forecast_Quantities =>l_Forecast_Qty_Tbl);
1491 Elsif l_Prev_Rec.Forecast_Method = 3 Then
1492 CSP_Forecast_Pvt.Exponential_Smoothing
1493 (P_Usage_History =>l_Usage_Qty_Tbl,
1494 P_History_Periods =>l_Prev_Rec.History_Periods,
1495 P_Forecast_Periods => l_Prev_Rec.Forecast_Periods,
1496 P_Alpha =>l_Prev_Rec.Alpha,
1497 X_Forecast_Quantities =>l_Forecast_Qty_Tbl);
1498
1499 Elsif l_prev_rec.Forecast_Method = 4 Then
1500 CSP_Forecast_Pvt.Trend_Enhanced
1501 (P_Usage_History =>l_Usage_Qty_Tbl,
1502 P_History_Periods =>l_Prev_Rec.History_Periods,
1503 P_Forecast_Periods =>l_Prev_Rec.Forecast_Periods,
1504 P_Alpha =>l_Prev_Rec.Alpha,
1505 P_Beta =>l_Prev_Rec.Beta,
1506 X_Forecast_Quantities =>l_Forecast_Qty_Tbl);
1507
1508 End If;
1509 FOR l_Index in 1..l_Forecast_Qty_Tbl.COUNT LOOP
1510 INSERT INTO CSP_USAGE_HISTORIES (Usage_Id,
1511 created_by,
1512 creation_date,
1513 last_updated_by,
1514 last_update_date,
1515 inventory_item_id,
1516 organization_id,
1517 subinventory_code,
1518 period_type,
1519 period_start_date,
1520 quantity,
1521 history_data_type)
1522 VALUES ( csp_usage_histories_s1.nextval,
1523 fnd_global.user_id, sysdate,
1524 fnd_global.user_id,sysdate,
1525 l_Prev_Rec.Inventory_Item_id,
1526 l_Prev_Rec.Organization_id,
1527 l_Prev_Rec.Subinventory_code,
1528 3,
1529 (trunc(sysdate) - l_prev_rec.forecast_periods * l_prev_rec.period_size * l_ts_cycle) +
1530 (l_prev_rec.period_size * (l_Index -1)),
1531 ROUND(decode(sign(l_Forecast_Qty_Tbl(l_Index)),-1,0,l_Forecast_Qty_Tbl(l_Index)),4),
1532 decode(p_reason_code,'RECM',2,'TS',5));
1533 If l_Forecast_Qty_Tbl(l_Index) > 0 Then
1534 l_Forecast_Quantity := l_Forecast_Quantity + ROUND(l_Forecast_Qty_Tbl(l_Index),4);
1535 End If;
1536 END LOOP;
1537 If l_prev_rec.recommend_method not in ('PNORM','TNORM') AND (p_reason_code = 'RECM') Then
1538 l_Usage_Data_Count := l_Usage_Qty_Tbl.COUNT;
1539 l_weeks := (l_Prev_Rec.Period_Size * l_Prev_Rec.Forecast_Periods)/7;
1540 l_Awu := ROUND((l_Forecast_Quantity/l_weeks),4);
1541
1542 -- Calculate Standard Deviation
1543 l_Variance := 0;
1544 If l_Usage_Data_Count > 1 Then
1545 l_Variance :=
1546 (l_Usage_Qty_Sum - ((l_Usage_Quantity * l_Usage_Quantity) /l_Usage_Data_Count))/(l_Usage_Data_Count - 1);
1547 End If;
1548 l_Standard_Deviation := ROUND(SQRT(NVL(l_Variance,0)),4);
1549 INSERT INTO CSP_USAGE_HEADERS
1550 (USAGE_HEADER_ID,
1551 INVENTORY_ITEM_ID,
1552 ORGANIZATION_ID,
1553 SECONDARY_INVENTORY,
1554 HEADER_DATA_TYPE,
1555 RAW_AWU,
1556 AWU,
1557 STANDARD_DEVIATION,
1558 LEAD_TIME,
1559 PROCESS_STATUS,
1560 EXTERNAL_DATA,
1561 COMMENTS,
1562 ITEM_COST,
1563 CREATION_DATE,
1564 CREATED_BY,
1565 LAST_UPDATE_DATE,
1566 LAST_UPDATED_BY,
1567 LAST_UPDATE_LOGIN)
1568 VALUES (NULL,
1569 l_prev_Rec.inventory_Item_Id,
1570 l_prev_rec.Organization_Id,
1571 l_prev_rec.Subinventory_code,
1572 l_prev_rec.History_Data_Type,
1573 NULL,
1574 l_Awu,
1575 l_Standard_Deviation,
1576 NULL,
1577 'O',
1578 'N',
1579 NULL,
1580 NULL,
1581 SYSDATE,
1582 fnd_global.user_id,
1583 SYSDATE,
1584 fnd_global.user_id,
1585 fnd_global.conc_login_id);
1586 End If;
1587 l_count := 0;
1588 l_Usage_Quantity := 0;
1589 l_Forecast_Quantity := 0;
1590 l_Usage_Data_Count := 0;
1591 l_Fcst_Period_Count := 0;
1592 l_Usage_Qty_Sum := 0;
1593 l_weeks := 0;
1594 l_Awu := 0;
1595 l_Variance := 0;
1596 l_Standard_Deviation := 0;
1597 l_Usage_Qty_Tbl.Delete;
1598 l_Weighted_Avg_Tbl.Delete;
1599 END IF;
1600 l_Usage_Qty_Tbl(l_count) := l_Usage_Details_Rec.Quantity;
1601 l_count := l_count + 1;
1602 l_prev_rec := l_Usage_Details_Rec;
1603 l_Usage_Quantity := l_Usage_Quantity + l_Usage_Details_Rec.Quantity;
1604 l_Usage_Qty_sum := l_Usage_Qty_Sum + (l_Usage_Details_Rec.Quantity * l_Usage_Details_Rec.Quantity);
1605 EXIT WHEN c_Usage_Details%NOTFOUND;
1606 END LOOP;
1607 close c_usage_details;
1608 l_ts_cycle := l_ts_cycle + 1;
1609 END LOOP; --- While clause
1610
1611
1612 --
1613 -- End of API body
1614 --
1615
1616 -- Standard check for p_commit
1617 IF FND_API.to_Boolean( p_commit )
1618 THEN
1619 COMMIT WORK;
1620 END IF;
1621
1622
1623 -- Standard call to get message count and if count is 1, get message info.
1624 FND_MSG_PUB.Count_And_Get
1625 ( p_count => x_msg_count,
1626 p_data => x_msg_data
1627 );
1628
1629 EXCEPTION
1630 WHEN FND_API.G_EXC_ERROR THEN
1631 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1632 P_API_NAME => L_API_NAME
1633 ,P_PKG_NAME => G_PKG_NAME
1634 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1635 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1636 ,P_ROLLBACK_FLAG => 'N'
1637 ,X_MSG_COUNT => X_MSG_COUNT
1638 ,X_MSG_DATA => X_MSG_DATA
1639 ,X_RETURN_STATUS => X_RETURN_STATUS);
1640
1641 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1642 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1643 P_API_NAME => L_API_NAME
1644 ,P_PKG_NAME => G_PKG_NAME
1645 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1646 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1647 ,P_ROLLBACK_FLAG => 'N'
1648 ,X_MSG_COUNT => X_MSG_COUNT
1649 ,X_MSG_DATA => X_MSG_DATA
1650 ,X_RETURN_STATUS => X_RETURN_STATUS);
1651
1652 WHEN OTHERS THEN
1653 l_sqlcode := SQLCODE;
1654 l_sqlerrm := SQLERRM;
1655 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1656 P_API_NAME => L_API_NAME
1657 ,P_PKG_NAME => G_PKG_NAME
1658 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1659 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1660 ,P_SQLCODE => l_sqlcode
1661 ,P_SQLERRM => l_sqlerrm
1662 ,P_ROLLBACK_FLAG => 'N'
1663 ,X_MSG_COUNT => X_MSG_COUNT
1664 ,X_MSG_DATA => X_MSG_DATA
1665 ,X_RETURN_STATUS => X_RETURN_STATUS);
1666 END Calculate_Forecast;
1667
1668 PROCEDURE Calculate_Product_Norm (
1669 P_Api_Version_Number IN NUMBER,
1670 P_Init_Msg_List IN VARCHAR2,
1671 P_Commit IN VARCHAR2,
1672 P_validation_level IN NUMBER,
1673 P_Level_Id IN VARCHAR2,
1674 X_Return_Status OUT NOCOPY VARCHAR2,
1675 X_Msg_Count OUT NOCOPY NUMBER,
1676 X_Msg_Data OUT NOCOPY VARCHAR2
1677 )
1678 IS
1679
1680 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_PNorm';
1681 l_api_version_number CONSTANT NUMBER := 1.0;
1682 l_return_status_full VARCHAR2(1);
1683 l_Sqlcode NUMBER;
1684 l_sqlerrm Varchar2(2000);
1685
1686 l_Msg_Count NUMBER;
1687 l_Msg_Data Varchar2(2000);
1688
1689 BEGIN
1690 -- Standard Start of API savepoint
1691 SAVEPOINT CALCULATE_PNORM_PVT;
1692
1693 -- Standard call to check for call compatibility.
1694 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1695 p_api_version_number,
1696 l_api_name,
1697 G_PKG_NAME)
1698 THEN
1699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1700 END IF;
1701
1702
1703 -- Initialize message list if p_init_msg_list is set to TRUE.
1704 IF FND_API.to_Boolean( p_init_msg_list )
1705 THEN
1706 FND_MSG_PUB.initialize;
1707 END IF;
1708
1709 INSERT INTO CSP_USAGE_HEADERS
1710 (USAGE_HEADER_ID,
1711 INVENTORY_ITEM_ID,
1712 ORGANIZATION_ID,
1713 SECONDARY_INVENTORY,
1714 HEADER_DATA_TYPE,
1715 RAW_AWU,
1716 AWU,
1717 PROCESS_STATUS,
1718 EXTERNAL_DATA,
1719 CREATION_DATE,
1720 CREATED_BY,
1721 LAST_UPDATE_DATE,
1722 LAST_UPDATED_BY,
1723 LAST_UPDATE_LOGIN)
1724 select NULL,
1725 nvl(csi.item_supplied,cppf.inventory_item_id),
1726 cppf.organization_id,
1727 nvl(cppf.secondary_inventory,'-'),
1728 7, -- Product Norm Usage
1729 NULL,
1730 cppf.current_population *
1731 nvl(manual_failure_rate,calculated_failure_rate),
1732 'O',
1733 'N',
1734 sysdate,
1735 fnd_global.user_id,
1736 sysdate,
1737 fnd_global.user_id,
1738 fnd_global.conc_login_id
1739 from csp_product_populations_fr_v cppf,
1740 csp_supersede_items csi
1741 where cppf.level_id like p_level_id || '%'
1742 and cppf.node_type in ('ORGANIZATION_WH','SUBINVENTORY')
1743 and csi.inventory_item_id(+) = cppf.inventory_item_id
1744 and csi.organization_id (+) = cppf.organization_id
1745 and csi.sub_inventory_code (+) = cppf.secondary_inventory;
1746
1747
1748 -- Standard check for p_commit
1749 IF FND_API.to_Boolean( p_commit )
1750 THEN
1751 COMMIT WORK;
1752 END IF;
1753
1754
1755 -- Standard call to get message count and if count is 1, get message info.
1756 FND_MSG_PUB.Count_And_Get
1757 ( p_count => x_msg_count,
1758 p_data => x_msg_data
1759 );
1760
1761 EXCEPTION
1762 WHEN FND_API.G_EXC_ERROR THEN
1763 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1764 P_API_NAME => L_API_NAME
1765 ,P_PKG_NAME => G_PKG_NAME
1766 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1767 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1768 ,P_ROLLBACK_FLAG => 'N'
1769 ,X_MSG_COUNT => X_MSG_COUNT
1770 ,X_MSG_DATA => X_MSG_DATA
1771 ,X_RETURN_STATUS => X_RETURN_STATUS);
1772
1773 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1774 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1775 P_API_NAME => L_API_NAME
1776 ,P_PKG_NAME => G_PKG_NAME
1777 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1778 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1779 ,P_ROLLBACK_FLAG => 'N'
1780 ,X_MSG_COUNT => X_MSG_COUNT
1781 ,X_MSG_DATA => X_MSG_DATA
1782 ,X_RETURN_STATUS => X_RETURN_STATUS);
1783
1784 WHEN OTHERS THEN
1785 l_sqlcode := SQLCODE;
1786 l_sqlerrm := SQLERRM;
1787 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1788 P_API_NAME => L_API_NAME
1789 ,P_PKG_NAME => G_PKG_NAME
1790 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1791 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1792 ,P_SQLCODE => l_sqlcode
1793 ,P_SQLERRM => l_sqlerrm
1794 ,P_ROLLBACK_FLAG => 'N'
1795 ,X_MSG_COUNT => X_MSG_COUNT
1796 ,X_MSG_DATA => X_MSG_DATA
1797 ,X_RETURN_STATUS => X_RETURN_STATUS);
1798 End Calculate_Product_Norm;
1799
1800 PROCEDURE calculate_new_product_planning (
1801 P_Api_Version_Number IN NUMBER,
1802 P_Init_Msg_List IN VARCHAR2,
1803 P_Commit IN VARCHAR2,
1804 P_validation_level IN NUMBER ,
1805 P_Level_Id IN VARCHAR2,
1806 X_Return_Status OUT NOCOPY VARCHAR2,
1807 X_Msg_Count OUT NOCOPY NUMBER,
1808 X_Msg_Data OUT NOCOPY VARCHAR2
1809 )
1810 IS
1811
1812 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_Nprod';
1813 l_api_version_number CONSTANT NUMBER := 1.0;
1814 l_return_status_full VARCHAR2(1);
1815 l_Sqlcode NUMBER;
1816 l_sqlerrm Varchar2(2000);
1817
1818 l_Msg_Count NUMBER;
1819 l_Msg_Data Varchar2(2000);
1820
1821 BEGIN
1822 -- Standard Start of API savepoint
1823 SAVEPOINT CALCULATE_NPROD_PVT;
1824
1825 -- Standard call to check for call compatibility.
1826 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1827 p_api_version_number,
1828 l_api_name,
1829 G_PKG_NAME)
1830 THEN
1831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1832 END IF;
1833
1834
1835 -- Initialize message list if p_init_msg_list is set to TRUE.
1836 IF FND_API.to_Boolean( p_init_msg_list )
1837 THEN
1838 FND_MSG_PUB.initialize;
1839 END IF;
1840
1841 INSERT INTO CSP_USAGE_HEADERS
1842 (USAGE_HEADER_ID,
1843 INVENTORY_ITEM_ID,
1844 ORGANIZATION_ID,
1845 SECONDARY_INVENTORY,
1846 HEADER_DATA_TYPE,
1847 RAW_AWU,
1848 AWU,
1849 PROCESS_STATUS,
1850 EXTERNAL_DATA,
1851 CREATION_DATE,
1852 CREATED_BY,
1853 LAST_UPDATE_DATE,
1854 LAST_UPDATED_BY,
1855 LAST_UPDATE_LOGIN)
1856 select NULL,
1857 nvl(csi.item_supplied,cnpp.inventory_item_id),
1858 cnpp.organization_id,
1859 cnpp.secondary_inventory,
1860 8, -- New Product planning
1861 NULL,
1862 cnpp.population_change *
1863 nvl(cnpp.manual_failure_rate,
1864 cnpp.calculated_failure_rate),
1865 'O',
1866 'N',
1867 sysdate,
1868 fnd_global.user_id,
1869 sysdate,
1870 fnd_global.user_id,
1871 fnd_global.conc_login_id
1872 from csp_new_product_planning_v cnpp,
1873 csp_supersede_items csi
1874 where cnpp.level_id like p_level_id || '%'
1875 and csi.inventory_item_id(+) = cnpp.inventory_item_id
1876 and csi.organization_id (+) = cnpp.organization_id
1877 and csi.sub_inventory_code (+) = cnpp.secondary_inventory;
1878
1879 -- Standard check for p_commit
1880 IF FND_API.to_Boolean( p_commit )
1881 THEN
1882 COMMIT WORK;
1883 END IF;
1884
1885
1886 -- Standard call to get message count and if count is 1, get message info.
1887 FND_MSG_PUB.Count_And_Get
1888 ( p_count => x_msg_count,
1889 p_data => x_msg_data
1890 );
1891
1892 EXCEPTION
1893 WHEN FND_API.G_EXC_ERROR THEN
1894 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1895 P_API_NAME => L_API_NAME
1896 ,P_PKG_NAME => G_PKG_NAME
1897 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
1898 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1899 ,P_ROLLBACK_FLAG => 'N'
1900 ,X_MSG_COUNT => X_MSG_COUNT
1901 ,X_MSG_DATA => X_MSG_DATA
1902 ,X_RETURN_STATUS => X_RETURN_STATUS);
1903
1904 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1905 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1906 P_API_NAME => L_API_NAME
1907 ,P_PKG_NAME => G_PKG_NAME
1908 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
1909 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1910 ,P_ROLLBACK_FLAG => 'N'
1911 ,X_MSG_COUNT => X_MSG_COUNT
1912 ,X_MSG_DATA => X_MSG_DATA
1913 ,X_RETURN_STATUS => X_RETURN_STATUS);
1914
1915 WHEN OTHERS THEN
1916 l_sqlcode := SQLCODE;
1917 l_sqlerrm := SQLERRM;
1918 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
1919 P_API_NAME => L_API_NAME
1920 ,P_PKG_NAME => G_PKG_NAME
1921 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
1922 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
1923 ,P_SQLCODE => l_sqlcode
1924 ,P_SQLERRM => l_sqlerrm
1925 ,P_ROLLBACK_FLAG => 'N'
1926 ,X_MSG_COUNT => X_MSG_COUNT
1927 ,X_MSG_DATA => X_MSG_DATA
1928 ,X_RETURN_STATUS => X_RETURN_STATUS);
1929 End Calculate_New_Product_Planning;
1930
1931 PROCEDURE calculate_territory_norm (
1932 P_Api_Version_Number IN NUMBER,
1933 P_Init_Msg_List IN VARCHAR2,
1934 P_Commit IN VARCHAR2,
1935 P_validation_level IN NUMBER,
1936 P_Level_Id IN VARCHAR2,
1937 X_Return_Status OUT NOCOPY VARCHAR2,
1938 X_Msg_Count OUT NOCOPY NUMBER,
1939 X_Msg_Data OUT NOCOPY VARCHAR2
1940 )
1941 IS
1942
1943 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_TNorm';
1944 l_api_version_number CONSTANT NUMBER := 1.0;
1945 l_return_status_full VARCHAR2(1);
1946 l_Sqlcode NUMBER;
1947 l_sqlerrm Varchar2(2000);
1948
1949 l_Msg_Count NUMBER;
1950 l_Msg_Data Varchar2(2000);
1951
1952 BEGIN
1953 -- Standard Start of API savepoint
1954 SAVEPOINT CALCULATE_TNORM_PVT;
1955
1956 -- Standard call to check for call compatibility.
1957 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1958 p_api_version_number,
1959 l_api_name,
1960 G_PKG_NAME)
1961 THEN
1962 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1963 END IF;
1964
1965
1966 -- Initialize message list if p_init_msg_list is set to TRUE.
1967 IF FND_API.to_Boolean( p_init_msg_list )
1968 THEN
1969 FND_MSG_PUB.initialize;
1970 END IF;
1971
1972 INSERT INTO CSP_USAGE_HEADERS
1973 (USAGE_HEADER_ID,
1974 INVENTORY_ITEM_ID,
1975 ORGANIZATION_ID,
1976 SECONDARY_INVENTORY,
1977 HEADER_DATA_TYPE,
1978 RAW_AWU,
1979 AWU,
1980 PROCESS_STATUS,
1981 EXTERNAL_DATA,
1982 CREATION_DATE,
1983 CREATED_BY,
1984 LAST_UPDATE_DATE,
1985 LAST_UPDATED_BY,
1986 LAST_UPDATE_LOGIN)
1987 select NULL,
1988 nvl(csi.item_supplied,curos.inventory_item_id),
1989 curos.organization_id,
1990 curos.secondary_inventory,
1991 9, -- Territory Norm
1992 NULL,
1993 curos.awu,
1994 'O',
1995 'N',
1996 sysdate,
1997 fnd_global.user_id,
1998 sysdate,
1999 fnd_global.user_id,
2000 fnd_global.conc_login_id
2001 from csp_usage_reg_org_subinv_v curos,
2002 csp_supersede_items csi
2003 where curos.level_id like p_level_id || '%'
2004 and csi.inventory_item_id (+) = curos.inventory_item_id
2005 and csi.organization_id (+) = curos.organization_id
2006 and csi.sub_inventory_code(+) = curos.secondary_inventory
2007 group by nvl(csi.item_supplied,curos.inventory_item_id),
2008 curos.organization_id,curos.secondary_inventory,curos.awu;
2009
2010 -- Standard check for p_commit
2011 IF FND_API.to_Boolean( p_commit )
2012 THEN
2013 COMMIT WORK;
2014 END IF;
2015
2016
2017 -- Standard call to get message count and if count is 1, get message info.
2018 FND_MSG_PUB.Count_And_Get
2019 ( p_count => x_msg_count,
2020 p_data => x_msg_data
2021 );
2022
2023 EXCEPTION
2024 WHEN FND_API.G_EXC_ERROR THEN
2025 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2026 P_API_NAME => L_API_NAME
2027 ,P_PKG_NAME => G_PKG_NAME
2028 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2029 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2030 ,P_ROLLBACK_FLAG => 'N'
2031 ,X_MSG_COUNT => X_MSG_COUNT
2032 ,X_MSG_DATA => X_MSG_DATA
2033 ,X_RETURN_STATUS => X_RETURN_STATUS);
2034
2035 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2036 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2037 P_API_NAME => L_API_NAME
2038 ,P_PKG_NAME => G_PKG_NAME
2039 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2040 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2041 ,P_ROLLBACK_FLAG => 'N'
2042 ,X_MSG_COUNT => X_MSG_COUNT
2043 ,X_MSG_DATA => X_MSG_DATA
2044 ,X_RETURN_STATUS => X_RETURN_STATUS);
2045
2046 WHEN OTHERS THEN
2047 l_sqlcode := SQLCODE;
2048 l_sqlerrm := SQLERRM;
2049 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2050 P_API_NAME => L_API_NAME
2051 ,P_PKG_NAME => G_PKG_NAME
2052 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
2053 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2054 ,P_SQLCODE => l_sqlcode
2055 ,P_SQLERRM => l_sqlerrm
2056 ,P_ROLLBACK_FLAG => 'N'
2057 ,X_MSG_COUNT => X_MSG_COUNT
2058 ,X_MSG_DATA => X_MSG_DATA
2059 ,X_RETURN_STATUS => X_RETURN_STATUS);
2060 End Calculate_Territory_Norm;
2061
2062 PROCEDURE Create_supply_chain (
2063 P_Api_Version_Number IN NUMBER,
2064 P_Init_Msg_List IN VARCHAR2 ,
2065 P_Commit IN VARCHAR2 ,
2066 P_validation_level IN NUMBER ,
2067 P_Level_id IN VARCHAR2,
2068 X_Return_Status OUT NOCOPY VARCHAR2,
2069 X_Msg_Count OUT NOCOPY NUMBER,
2070 X_Msg_Data OUT NOCOPY VARCHAR2
2071 )
2072 IS
2073
2074 cursor c_parameters(p_level_id varchar2) is
2075 select node_type
2076 from csp_planning_parameters
2077 where level_id = p_level_id;
2078
2079 l_api_name constant varchar2(30) := 'Create_Supply_Chain';
2080 l_api_version_number constant number := 1.0;
2081 l_return_status_full varchar2(1);
2082 l_Sqlcode number;
2083 l_Sqlerrm varchar2(2000);
2084
2085
2086 l_supply_level number := 1;
2087 l_string varchar2(2000);
2088 g_txn_type_id number := 93;
2089 g_txn_action_id number := 1;
2090 g_txn_source_type_id number := 13;
2091 g_total_period number := 1;
2092 g_source_type number := 1;
2093 g_default_flag number := 1;
2094 l_period_size number := 0;
2095 l_level_id varchar2(2000);
2096 l_node_type varchar2(20);
2097 l_get_app_info boolean;
2098 l_status varchar2(1);
2099 l_industry varchar2(1);
2100 l_oracle_schema varchar2(30);
2101
2102 BEGIN
2103 -- Standard Start of API savepoint
2104 SAVEPOINT CREATE_SUPPLY_CHAIN_PVT;
2105
2106 -- Standard call to check for call compatibility.
2107 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2108 p_api_version_number,
2109 l_api_name,
2110 G_PKG_NAME)
2111 THEN
2112 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2113 END IF;
2114
2115
2116 -- Initialize message list if p_init_msg_list is set to TRUE.
2117 IF FND_API.to_Boolean( p_init_msg_list )
2118 THEN
2119 FND_MSG_PUB.initialize;
2120 END IF;
2121
2122
2123 -- Initialize API return status to SUCCESS
2124 x_return_status := FND_API.G_RET_STS_SUCCESS;
2125
2126 --
2127 -- API body
2128 --
2129 l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
2130 --- Delete from Supply Chain
2131 EXECUTE IMMEDIATE 'TRUNCATE TABLE '||l_oracle_schema||'.CSP_SUPPLY_CHAIN' ;
2132
2133 open c_parameters(p_level_id);
2134 Fetch c_parameters into l_node_type;
2135 close c_parameters;
2136 l_level_id := p_level_id;
2137 If l_node_type in ('ORGANIZATON_WH','SUBINVENTORY') then
2138 l_level_id := substr(p_level_id,1,instr(p_level_id,'.',-1,1) - 1);
2139 End If;
2140
2141 INSERT INTO CSP_SUPPLY_CHAIN (
2142 source_type,
2143 source_organization_id,
2144 source_subinventory,
2145 organization_id,
2146 secondary_inventory,
2147 inventory_item_id,
2148 lead_time,
2149 supply_level,
2150 creation_date,
2151 created_by,
2152 last_updated_by,
2153 last_update_date)
2154 select /*+ parallel(MSIB,8) parallel(MISI,8) parallel(MSI,8)
2155 parallel(CSC,8) */
2156 nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) source_type,
2157 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,
2158 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,
2159 cri.organization_id,
2160 cri.secondary_inventory,
2161 cri.inventory_item_id,
2162 NULL,
2163 1, -- supply_level
2164 sysdate,
2165 fnd_global.user_id,
2166 fnd_global.user_id,
2167 sysdate
2168 from csp_region_items_v cri,
2169 mtl_related_items mri,
2170 mtl_parameters mp,
2171 mtl_system_items_b msib,
2172 mtl_item_sub_inventories misi,
2173 mtl_secondary_inventories msi
2174 where cri.level_id like l_level_id || '%'
2175 and mp.organization_id = cri.organization_id
2176 and mri.organization_id = mp.master_organization_id
2177 and mri.inventory_item_id = cri.inventory_item_id
2178 and misi.organization_id (+) = cri.organization_id
2179 and misi.inventory_item_id (+) = cri.inventory_item_id
2180 and misi.secondary_inventory (+) = cri.secondary_inventory
2181 and msib.organization_id = cri.organization_id
2182 and msib.inventory_item_id = cri.inventory_item_id
2183 and msi.organization_id = cri.organization_id
2184 and msi.secondary_inventory_name = cri.secondary_inventory
2185 Group By nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) ,
2186 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) ,
2187 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) ,
2188 cri.organization_id,
2189 cri.secondary_inventory,
2190 cri.inventory_item_id;
2191
2192 loop
2193 l_supply_level := l_supply_level + 1;
2194 insert into csp_supply_chain(
2195 source_type,
2196 source_organization_id,
2197 source_subinventory,
2198 organization_id,
2199 secondary_inventory,
2200 inventory_item_id,
2201 lead_time,
2202 supply_level,
2203 creation_date,
2204 created_by,
2205 last_updated_by,
2206 last_update_date)
2207 select
2208 nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) source_type,
2209 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,
2210 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,
2211 csc.source_organization_id organization_id,
2212 nvl(csc.source_subinventory,'-') subinventory_code,
2213 csc.inventory_item_id,
2214 NULL,
2215 l_supply_level,
2216 sysdate,
2217 fnd_global.user_id,
2218 fnd_global.user_id,
2219 sysdate
2220 from mtl_parameters mp,
2221 mtl_system_items_b msib,
2222 mtl_item_sub_inventories misi,
2223 mtl_secondary_inventories msi,
2224 csp_supply_chain csc
2225 where mp.organization_id = csc.source_organization_id
2226 and misi.organization_id (+) = csc.source_organization_id
2227 and misi.inventory_item_id (+) = csc.inventory_item_id
2228 and misi.secondary_inventory (+) = csc.source_subinventory
2229 and msib.organization_id = csc.source_organization_id
2230 and msib.inventory_item_id = csc.inventory_item_id
2231 and msi.organization_id (+) = csc.source_organization_id
2232 and msi.secondary_inventory_name (+) = csc.source_subinventory
2233 and csc.supply_level = l_supply_level - 1
2234 and csc.source_type IN (1,3)
2235 Group By
2236 nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) ,
2237 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) ,
2238 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) ,
2239 csc.source_organization_id ,
2240 nvl(csc.source_subinventory,'-') ,
2241 csc.inventory_item_id;
2242
2243 if sql%notfound then
2244 exit;
2245 end if;
2246 end loop;
2247
2248 --
2249 -- End of API body
2250 --
2251
2252 -- Standard check for p_commit
2253 IF FND_API.to_Boolean( p_commit )
2254 THEN
2255 COMMIT WORK;
2256 END IF;
2257
2258
2259 -- Standard call to get message count and if count is 1, get message info.
2260 FND_MSG_PUB.Count_And_Get
2261 ( p_count => x_msg_count,
2262 p_data => x_msg_data
2263 );
2264
2265 EXCEPTION
2266 WHEN FND_API.G_EXC_ERROR THEN
2267 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2268 P_API_NAME => L_API_NAME
2269 ,P_PKG_NAME => G_PKG_NAME
2270 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2271 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2272 ,P_ROLLBACK_FLAG => 'N'
2273 ,X_MSG_COUNT => X_MSG_COUNT
2274 ,X_MSG_DATA => X_MSG_DATA
2275 ,X_RETURN_STATUS => X_RETURN_STATUS);
2276 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2277 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2278 P_API_NAME => L_API_NAME
2279 ,P_PKG_NAME => G_PKG_NAME
2280 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2281 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2282 ,P_ROLLBACK_FLAG => 'N'
2283 ,X_MSG_COUNT => X_MSG_COUNT
2284 ,X_MSG_DATA => X_MSG_DATA
2285 ,X_RETURN_STATUS => X_RETURN_STATUS);
2286 WHEN OTHERS THEN
2287 l_sqlcode := SQLCODE;
2288 l_sqlerrm := SQLERRM;
2289 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2290 P_API_NAME => L_API_NAME
2291 ,P_PKG_NAME => G_PKG_NAME
2292 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
2293 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2294 ,P_ROLLBACK_FLAG => 'N'
2295 ,P_SQLCODE => l_Sqlcode
2296 ,P_SQLERRM => l_Sqlerrm
2297 ,X_MSG_COUNT => X_MSG_COUNT
2298 ,X_MSG_DATA => X_MSG_DATA
2299 ,X_RETURN_STATUS => X_RETURN_STATUS);
2300 End Create_Supply_Chain;
2301
2302 PROCEDURE Create_Supply_Chain (
2303 P_Api_Version_Number IN NUMBER,
2304 P_Init_Msg_List IN VARCHAR2 ,
2305 P_Commit IN VARCHAR2 ,
2306 P_validation_level IN NUMBER ,
2307 X_Return_Status OUT NOCOPY VARCHAR2,
2308 X_Msg_Count OUT NOCOPY NUMBER,
2309 X_Msg_Data OUT NOCOPY VARCHAR2
2310 )
2311 IS
2312
2313 Cursor c_Period_Size Is
2314 Select MAX(HISTORY_PERIODS * PERIOD_SIZE) PERIOD_SIZE
2315 From CSP_PLANNING_PARAMETERS cpp,
2316 CSP_FORECAST_RULES_B cfrb
2317 Where cpp.FORECAST_RULE_ID = cfrb.FORECAST_RULE_ID;
2318
2319
2320 l_api_name constant varchar2(30) := 'Create_Supply_Chain';
2321 l_api_version_number constant number := 1.0;
2322 l_return_status_full varchar2(1);
2323 l_Sqlcode number;
2324 l_Sqlerrm varchar2(2000);
2325
2326
2327 l_supply_level number := 1;
2328 l_string varchar2(2000);
2329 g_txn_type_id number := 93;
2330 g_txn_action_id number := 1;
2331 g_txn_source_type_id number := 13;
2332 g_total_period number := 1;
2333 g_source_type number := 1;
2334 g_default_flag number := 1;
2335 l_period_size number := 0;
2336
2337 BEGIN
2338 -- Standard Start of API savepoint
2339 SAVEPOINT CREATE_SUPPLY_CHAIN_PVT;
2340
2341 -- Standard call to check for call compatibility.
2342 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2343 p_api_version_number,
2344 l_api_name,
2345 G_PKG_NAME)
2346 THEN
2347 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2348 END IF;
2349
2350
2351 -- Initialize message list if p_init_msg_list is set to TRUE.
2352 IF FND_API.to_Boolean( p_init_msg_list )
2353 THEN
2354 FND_MSG_PUB.initialize;
2355 END IF;
2356
2357
2358 -- Initialize API return status to SUCCESS
2359 x_return_status := FND_API.G_RET_STS_SUCCESS;
2360
2361 --
2362 -- API body
2363 --
2364 Open c_Period_Size;
2365 Fetch c_Period_size into l_period_size;
2366 Close c_Period_size;
2367
2368 INSERT INTO CSP_SUPPLY_CHAIN (
2369 source_type,
2370 source_organization_id,
2371 source_subinventory,
2372 organization_id,
2373 secondary_inventory,
2374 inventory_item_id,
2375 lead_time,
2376 supply_level,
2377 creation_date,
2378 created_by,
2379 last_updated_by,
2380 last_update_date)
2381 select /*+ parallel(MSIB,8) parallel(MISI,8) parallel(MSI,8)
2382 parallel(CSC,8) */
2383 nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) source_type,
2384 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,
2385 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,
2386 mmt.organization_id,
2387 mmt.subinventory_code,
2388 mmt.inventory_item_id,
2389 nvl(mism.intransit_time,decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
2390 decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0,
2391 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)),
2392 nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))) lead_time,
2393 1, -- supply_level
2394 sysdate,
2395 fnd_global.user_id,
2396 fnd_global.user_id,
2397 sysdate
2398 from mtl_parameters mp,
2399 mtl_system_items_b msib,
2400 mtl_item_sub_inventories misi,
2401 mtl_secondary_inventories msi,
2402 mtl_material_transactions mmt,
2403 mtl_interorg_ship_methods mism
2404 where mp.organization_id = mmt.organization_id
2405 and misi.organization_id (+) = mmt.organization_id
2406 and misi.inventory_item_id (+) = mmt.inventory_item_id
2407 and misi.secondary_inventory (+) = mmt.subinventory_code
2408 and mism.to_organization_id (+) = mp.organization_id
2409 and mism.from_organization_id (+) = decode(mp.source_type,1,mp.source_organization_id,3,mp.source_organization_id,-1)
2410 and mism.default_flag (+) = 1
2411 and msib.organization_id = mmt.organization_id
2412 and msib.inventory_item_id = mmt.inventory_item_id
2413 and msi.organization_id = mmt.organization_id
2414 and msi.secondary_inventory_name = mmt.subinventory_code
2415 and mmt.transaction_action_id = g_txn_action_id
2416 and (mmt.transaction_date > (trunc(sysdate) - l_period_size - 1) and
2417 mmt.transaction_date < trunc(sysdate))
2418 Group By nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) ,
2419 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) ,
2420 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) ,
2421 mmt.organization_id,
2422 mmt.subinventory_code,
2423 mmt.inventory_item_id,
2424 nvl(mism.intransit_time,decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
2425 decode(nvl(msib.preprocessing_lead_time,0) + nvl(msib.full_lead_time,0) + nvl(msib.postprocessing_lead_time,0),0,
2426 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)),
2427 nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0))) ,
2428 1,
2429 sysdate,
2430 fnd_global.user_id,
2431 fnd_global.user_id,
2432 sysdate;
2433
2434 loop
2435 l_supply_level := l_supply_level + 1;
2436 insert into csp_supply_chain(
2437 source_type,
2438 source_organization_id,
2439 source_subinventory,
2440 organization_id,
2441 secondary_inventory,
2442 inventory_item_id,
2443 lead_time,
2444 supply_level,
2445 creation_date,
2446 created_by,
2447 last_updated_by,
2448 last_update_date)
2449 select
2450 nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) source_type,
2451 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,
2452 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,
2453 csc.source_organization_id organization_id,
2454 nvl(csc.source_subinventory,'-') subinventory_code,
2455 csc.inventory_item_id,
2456 nvl(mism.intransit_time,decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
2457 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),
2458 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,
2459 l_supply_level,
2460 sysdate,
2461 fnd_global.user_id,
2462 fnd_global.user_id,
2463 sysdate
2464 from mtl_parameters mp,
2465 mtl_system_items_b msib,
2466 mtl_item_sub_inventories misi,
2467 mtl_secondary_inventories msi,
2468 csp_supply_chain csc,
2469 mtl_interorg_ship_methods mism
2470 where mp.organization_id = csc.source_organization_id
2471 and misi.organization_id (+) = csc.source_organization_id
2472 and misi.inventory_item_id (+) = csc.inventory_item_id
2473 and misi.secondary_inventory (+) = csc.source_subinventory
2474 and mism.to_organization_id (+) = mp.organization_id
2475 and mism.from_organization_id (+) = decode(mp.source_type,1,mp.source_organization_id,3,mp.source_organization_id,-1)
2476 and mism.default_flag (+) = g_default_flag
2477 and msib.organization_id = csc.source_organization_id
2478 and msib.inventory_item_id = csc.inventory_item_id
2479 and msi.organization_id (+) = csc.source_organization_id
2480 and msi.secondary_inventory_name (+) = csc.source_subinventory
2481 and csc.supply_level = l_supply_level - 1
2482 and csc.source_type IN (1,3)
2483 Group By
2484 nvl(nvl(misi.source_type,nvl(msib.source_type,nvl(msi.source_type,mp.source_type))),2) ,
2485 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) ,
2486 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) ,
2487 csc.source_organization_id ,
2488 nvl(csc.source_subinventory,'-') ,
2489 csc.inventory_item_id,
2490 nvl(mism.intransit_time,decode(nvl(misi.preprocessing_lead_time,0) + nvl(misi.processing_lead_time,0) + nvl(misi.postprocessing_lead_time,0),0,
2491 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),
2492 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))) ,
2493 l_supply_level,
2494 sysdate,
2495 fnd_global.user_id,
2496 fnd_global.user_id,
2497 sysdate;
2498
2499 if sql%notfound then
2500 exit;
2501 end if;
2502 end loop;
2503
2504 --
2505 -- End of API body
2506 --
2507
2508 -- Standard check for p_commit
2509 IF FND_API.to_Boolean( p_commit )
2510 THEN
2511 COMMIT WORK;
2512 END IF;
2513
2514
2515 -- Standard call to get message count and if count is 1, get message info.
2516 FND_MSG_PUB.Count_And_Get
2517 ( p_count => x_msg_count,
2518 p_data => x_msg_data
2519 );
2520
2521 EXCEPTION
2522 WHEN FND_API.G_EXC_ERROR THEN
2523 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2524 P_API_NAME => L_API_NAME
2525 ,P_PKG_NAME => G_PKG_NAME
2526 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2527 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2528 ,P_ROLLBACK_FLAG => 'N'
2529 ,X_MSG_COUNT => X_MSG_COUNT
2530 ,X_MSG_DATA => X_MSG_DATA
2531 ,X_RETURN_STATUS => X_RETURN_STATUS);
2532
2533 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2534 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2535 P_API_NAME => L_API_NAME
2536 ,P_PKG_NAME => G_PKG_NAME
2537 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2538 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2539 ,P_ROLLBACK_FLAG => 'N'
2540 ,X_MSG_COUNT => X_MSG_COUNT
2541 ,X_MSG_DATA => X_MSG_DATA
2542 ,X_RETURN_STATUS => X_RETURN_STATUS);
2543
2544 WHEN OTHERS THEN
2545 l_sqlcode := SQLCODE;
2546 l_sqlerrm := SQLERRM;
2547 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2548 P_API_NAME => L_API_NAME
2549 ,P_PKG_NAME => G_PKG_NAME
2550 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
2551 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2552 ,P_ROLLBACK_FLAG => 'N'
2553 ,P_SQLCODE => l_Sqlcode
2554 ,P_SQLERRM => l_Sqlerrm
2555 ,X_MSG_COUNT => X_MSG_COUNT
2556 ,X_MSG_DATA => X_MSG_DATA
2557 ,X_RETURN_STATUS => X_RETURN_STATUS);
2558
2559 End Create_Supply_Chain;
2560
2561
2562 PROCEDURE Create_Usage_History (
2563 P_Api_Version_Number IN NUMBER,
2564 P_Init_Msg_List IN VARCHAR2,
2565 P_Commit IN VARCHAR2,
2566 p_validation_level IN NUMBER,
2567 X_Return_Status OUT NOCOPY VARCHAR2,
2568 X_Msg_Count OUT NOCOPY NUMBER,
2569 X_Msg_Data OUT NOCOPY VARCHAR2
2570 )
2571
2572 IS
2573
2574 l_api_name constant varchar2(30) := 'Create_Usage_Hist';
2575 l_api_version_number CONSTANT NUMBER := 1.0;
2576 l_return_status_full VARCHAR2(1);
2577 l_sqlcode NUMBER;
2578 l_sqlerrm Varchar2(2000);
2579
2580 l_string varchar2(2000);
2581 g_txn_type_id number := 93;
2582 g_txn_action_id number := 1;
2583 g_txn_source_type_id number := 13;
2584 l_supply_level number := 0;
2585 --Period Size and Number of periods changed to be
2586 --1 to calculate usage history on daily basis for
2587 --1158
2588 l_period_size number := 1; -- G_PERIOD_SIZE;
2589 l_number_of_periods number := 1; -- G_HISTORY_PERIODS;
2590 l_usage_id Number;
2591
2592 l_Msg_Count NUMBER;
2593 l_Msg_Data Varchar2(2000);
2594
2595 BEGIN
2596 -- Standard Start of API savepoint
2597 SAVEPOINT CREATE_USAGE_HIST_PVT;
2598
2599
2600 -- Initialize API return status to SUCCESS
2601 x_return_status := FND_API.G_RET_STS_SUCCESS;
2602
2603 --
2604 -- API body
2605 --
2606 -- Create Usage History for Engineering subinventories
2607 insert into csp_usage_histories(
2608 organization_id,
2609 subinventory_code,
2610 inventory_item_id,
2611 period_start_date,
2612 transaction_type_id,
2613 quantity,
2614 history_data_type,
2615 period_type,
2616 created_by,
2617 creation_date,
2618 last_updated_by,
2619 last_update_date)
2620 select mmt.organization_id,
2621 mmt.subinventory_code,
2622 mmt.inventory_item_id,
2623 trunc(transaction_date),
2624 transaction_type_id,
2625 sum(mmt.primary_quantity) * -1 primary_quantity,
2626 1, -- History data type
2627 3, -- Period type
2628 fnd_global.user_id,
2629 sysdate,
2630 fnd_global.user_id,
2631 sysdate
2632 from mtl_material_transactions mmt
2633 where mmt.creation_date >
2634 decode(G_LAST_RUN_DATE,fnd_api.g_miss_date,G_LAST_RUN_DATE,
2635 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)
2636 and mmt.transaction_action_id = g_txn_action_id
2637 group by
2638 mmt.organization_id,
2639 mmt.subinventory_code,
2640 mmt.inventory_item_id,
2641 trunc(transaction_date),
2642 transaction_type_id,
2643 1, -- History data type
2644 3, -- Period type
2645 fnd_global.user_id,
2646 sysdate,
2647 fnd_global.user_id,
2648 sysdate;
2649
2650 -- Rollup Usage History through Supply Chain
2651 loop
2652 l_supply_level := l_supply_level + 1;
2653 insert into csp_usage_histories(
2654 organization_id,
2655 subinventory_code,
2656 inventory_item_id,
2657 period_start_date,
2658 transaction_type_id,
2659 quantity,
2660 history_data_type,
2661 period_type,
2662 created_by,
2663 creation_date,
2664 last_updated_by,
2665 last_update_date)
2666 select /*+ ORDERED */
2667 nvl(csc.source_organization_id,-1),
2668 nvl(csc.source_subinventory,'-'),
2669 csc.inventory_item_id,
2670 trunc(cuh.period_start_date),
2671 transaction_type_id,
2672 sum(cuh.quantity),
2673 1,--heh decode(csc.source_type,2,3,1), -- History data type
2674 3, -- Period type
2675 fnd_global.user_id,
2676 sysdate,
2677 fnd_global.user_id,
2678 sysdate
2679 from csp_supply_chain csc,
2680 csp_usage_histories cuh
2681 where cuh.history_data_type = 1
2682 and cuh.period_start_date > G_LAST_RUN_DATE
2683 and cuh.organization_id = csc.organization_id
2684 and cuh.subinventory_code = csc.secondary_inventory
2685 and cuh.inventory_item_id = csc.inventory_item_id
2686 and csc.supply_level = l_supply_level
2687 group by
2688 nvl(csc.source_organization_id,-1),
2689 nvl(csc.source_subinventory,'-'),
2690 csc.inventory_item_id,
2691 trunc(cuh.period_start_date),
2692 transaction_type_id,
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 if sql%notfound then
2700 exit;
2701 end if;
2702 exit;
2703 end loop;
2704
2705 --
2706 -- End of API body
2707 --
2708
2709 -- Standard check for p_commit
2710 IF FND_API.to_Boolean( p_commit )
2711 THEN
2712 COMMIT WORK;
2713 END IF;
2714
2715
2716 -- Standard call to get message count and if count is 1, get message info.
2717 FND_MSG_PUB.Count_And_Get
2718 ( p_count => x_msg_count,
2719 p_data => x_msg_data
2720 );
2721
2722 EXCEPTION
2723 WHEN FND_API.G_EXC_ERROR THEN
2724 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2725 P_API_NAME => L_API_NAME
2726 ,P_PKG_NAME => G_PKG_NAME
2727 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
2728 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2729 ,P_ROLLBACK_FLAG => 'N'
2730 ,X_MSG_COUNT => X_MSG_COUNT
2731 ,X_MSG_DATA => X_MSG_DATA
2732 ,X_RETURN_STATUS => X_RETURN_STATUS);
2733
2734 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2735 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2736 P_API_NAME => L_API_NAME
2737 ,P_PKG_NAME => G_PKG_NAME
2738 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
2739 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2740 ,P_ROLLBACK_FLAG => 'N'
2741 ,X_MSG_COUNT => X_MSG_COUNT
2742 ,X_MSG_DATA => X_MSG_DATA
2743 ,X_RETURN_STATUS => X_RETURN_STATUS);
2744
2745 WHEN OTHERS THEN
2746 l_sqlcode := SQLCODE;
2747 l_sqlerrm := SQLERRM;
2748 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
2749 P_API_NAME => L_API_NAME
2750 ,P_PKG_NAME => G_PKG_NAME
2751 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
2752 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
2753 ,P_ROLLBACK_FLAG => 'N'
2754 ,P_SQLCODE => l_sqlcode
2755 ,P_SQLERRM => l_sqlerrm
2756 ,X_MSG_COUNT => X_MSG_COUNT
2757 ,X_MSG_DATA => X_MSG_DATA
2758 ,X_RETURN_STATUS => X_RETURN_STATUS);
2759 End Create_Usage_History;
2760
2761
2762 PROCEDURE Apply_Business_Rules (
2763 P_Api_Version_Number IN NUMBER,
2764 P_Init_Msg_List IN VARCHAR2,
2765 P_Commit IN VARCHAR2,
2766 p_validation_level IN NUMBER,
2767 X_Return_Status OUT NOCOPY VARCHAR2,
2768 X_Msg_Count OUT NOCOPY NUMBER,
2769 X_Msg_Data OUT NOCOPY VARCHAR2
2770 ) IS
2771
2772 Cursor c_usg_hdr_subinv Is
2773 Select decode(cuh.RECOMMENDED_MAX_QUANTITY,0,0,greatest(1,cuh.RECOMMENDED_MIN_QUANTITY)) RECOMMENDED_MIN_QUANTITY,
2774 cuh.RECOMMENDED_MAX_QUANTITY,
2775 cuh.INVENTORY_ITEM_ID,
2776 cuh.ORGANIZATION_ID,
2777 cuh.SECONDARY_INVENTORY
2778 From csp_usage_headers cuh,
2779 csp_planning_parameters cpp,
2780 mtl_item_sub_inventories misi,
2781 csp_business_rules_b cbrb
2782 Where header_data_type = 1
2783 and process_status = 'O'
2784 and cuh.planning_parameters_id = cpp.planning_parameters_id
2785 and cpp.node_type = 'SUBINVENTORY'
2786 and misi.organization_id(+) = cuh.organization_id
2787 and misi.inventory_item_id(+) = cuh.inventory_item_id
2788 and misi.secondary_inventory(+) = cuh.secondary_inventory
2789 And cbrb.business_rule_id = cpp.recommendation_rule_id
2790 And nvl(cbrb.business_rule_value3,1) >
2791 abs(decode(cbrb.business_rule_value3,null,0,
2792 nvl(cuh.recommended_max_quantity * cuh.item_cost,0) -
2793 nvl(misi.MAX_MINMAX_QUANTITY * cuh.item_cost,0)))
2794 And nvl(cbrb.business_rule_value4,1) >
2795 abs(decode(cbrb.business_rule_value4,null,0,
2796 ROUND((nvl((cuh.recommended_max_quantity - misi.MAX_MINMAX_QUANTITY) *
2797 cuh.item_cost,0)/
2798 DECODE(nvl(misi.MAX_MINMAX_QUANTITY,0) *
2799 nvl(cuh.item_cost ,0),0,1,misi.MAX_MINMAX_QUANTITY*cuh.item_cost)) * 100,2)))
2800 And nvl(cbrb.business_rule_value5,1) >
2801 abs(decode(cbrb.business_rule_value5,null,0,
2802 nvl(cuh.RECOMMENDED_MAX_QUANTITY - misi.MAX_MINMAX_QUANTITY,0)))
2803 And nvl(cbrb.business_rule_value6,1) >
2804 abs(decode(cbrb.business_rule_value6,null,0,
2805 ROUND((nvl(cuh.recommended_max_quantity - misi.MAX_MINMAX_QUANTITY,0)/
2806 DECODE(nvl(misi.MAX_MINMAX_QUANTITY,0),0,1,
2807 misi.MAX_MINMAX_QUANTITY)) * 100,2)))
2808 And (nvl(cuh.tracking_signal,0) >= decode(cpp.recommend_method,'PNORM',0,
2809 'TNORM',0,nvl(cbrb.business_rule_value1,nvl(cuh.tracking_signal,0)))
2810 And nvl(cuh.tracking_signal,0) <= decode(cpp.recommend_method,'PNORM',0,
2811 'TNORM',0,nvl(cbrb.business_rule_value2,nvl(cuh.tracking_signal,0))));
2812
2813 Cursor c_usg_hdr_item Is
2814 Select cuh.inventory_item_id,
2815 cuh.organization_id,
2816 cuh.recommended_min_quantity,
2817 cuh.recommended_max_quantity
2818 from CSP_USAGE_HEADERS cuh,
2819 CSP_PLANNING_PARAMETERS cpp,
2820 MTL_SYSTEM_ITEMS_B msib,
2821 CSP_BUSINESS_RULES_B cbrb
2822 Where msib.INVENTORY_ITEM_ID = cuh.INVENTORY_ITEM_ID
2823 And msib.ORGANIZATION_ID = cuh.ORGANIZATION_ID
2824 And cuh.header_data_type = 4
2825 And cuh.process_status = 'O'
2826 And cpp.node_type = 'ORGANIZATION_WH'
2827 And cpp.organization_id = cuh.organization_id
2828 And cbrb.business_rule_id = cpp.recommendation_rule_id
2829 And nvl(cbrb.business_rule_value3,1) >
2830 abs(decode(cbrb.business_rule_value3,null,0,
2831 nvl(cuh.recommended_max_quantity * cuh.item_cost,0) -
2832 nvl(msib.MAX_MINMAX_QUANTITY * cuh.item_cost,0)))
2833 And nvl(cbrb.business_rule_value4,1) >
2834 abs(decode(cbrb.business_rule_value4,null,0,
2835 ROUND((nvl((cuh.recommended_max_quantity - msib.MAX_MINMAX_QUANTITY) *
2836 cuh.item_cost,0)/
2837 DECODE(nvl(msib.MAX_MINMAX_QUANTITY,0) *
2838 nvl(cuh.item_cost ,0),0,1,msib.MAX_MINMAX_QUANTITY*cuh.item_cost)) * 100,2)))
2839 And nvl(cbrb.business_rule_value5,1) >
2840 abs(decode(cbrb.business_rule_value5,null,0,
2841 nvl(cuh.RECOMMENDED_MAX_QUANTITY - msib.MAX_MINMAX_QUANTITY,0)))
2842 And nvl(cbrb.business_rule_value6,1) >
2843 abs(decode(cbrb.business_rule_value6,null,0,
2844 ROUND((nvl(cuh.recommended_max_quantity - msib.MAX_MINMAX_QUANTITY,0)/
2845 DECODE(nvl(msib.MAX_MINMAX_QUANTITY,0),0,1,
2846 msib.MAX_MINMAX_QUANTITY)) * 100,2)))
2847 And (nvl(cuh.tracking_signal,0) >= decode(cpp.recommend_method,'PNORM',0,
2848 'TNORM',0,nvl(cbrb.business_rule_value1,nvl(cuh.tracking_signal,0)))
2849 And nvl(cuh.tracking_signal,0) <= decode(cpp.recommend_method,'PNORM',0,
2850 'TNORM',0,nvl(cbrb.business_rule_value2,nvl(cuh.tracking_signal,0))));
2851 Begin
2852 -- Update Subinventory Min/Max values
2853
2854 -- Removed for database version compatibility
2855 -- issues. Replaced with UPDATE/INSERT
2856 /*
2857 MERGE INTO MTL_ITEM_SUB_INVENTORIES item_subinv USING
2858 (Select cuh.Inventory_Item_Id,
2859 cuh.Organization_Id,
2860 cuh.secondary_inventory,
2861 cuh.recommended_min_quantity,
2862 cuh.recommended_max_quantity
2863 From csp_usage_headers cuh,
2864 csp_planning_parameters cpp,
2865 mtl_item_sub_inventories misi,
2866 CSP.csp_business_rules_b cbrb
2867 Where cuh.planning_parameters_id = cpp.planning_parameters_id
2868 and misi.organization_id(+) = cuh.organization_id
2869 and misi.inventory_item_id(+) = cuh.inventory_item_id
2870 and misi.secondary_inventory(+) = cuh.secondary_inventory
2871 And cbrb.business_rule_id = cpp.recommendation_rule_id
2872 And nvl(cbrb.business_rule_value3,1) >
2873 decode(cbrb.business_rule_value3,null,0,nvl(cuh.recommended_max_quantity * cuh.item_cost,0) -
2874 nvl(misi.MAX_MINMAX_QUANTITY * cuh.item_cost,0))
2875 And nvl(cbrb.business_rule_value4,1) >
2876 decode(cbrb.business_rule_value4,null,0,
2877 ROUND((nvl(cuh.recommended_max_quantity - misi.MAX_MINMAX_QUANTITY * cuh.item_cost,0)/
2878 DECODE(nvl(misi.MAX_MINMAX_QUANTITY,0) * nvl(cuh.item_cost ,0),0,1,misi.MAX_MINMAX_QUANTITY)) * 100,2))
2879 And nvl(cbrb.business_rule_value5,1) >
2880 decode(cbrb.business_rule_value5,null,0,nvl(cuh.RECOMMENDED_MAX_QUANTITY - misi.MAX_MINMAX_QUANTITY,0))
2881 And nvl(cbrb.business_rule_value6,1) >
2882 decode(cbrb.business_rule_value6,null,0,
2883 ROUND((nvl(cuh.recommended_max_quantity - misi.MAX_MINMAX_QUANTITY,0)/DECODE(nvl(misi.MAX_MINMAX_QUANTITY,0),0,1,
2884 misi.MAX_MINMAX_QUANTITY)) * 100,2))
2885 And (nvl(cuh.tracking_signal,0) >=
2886 decode(cpp.recommend_method,'PNORM',0,'TNORM',0,nvl(cbrb.business_rule_value1,nvl(cuh.tracking_signal,0)))
2887 And nvl(cuh.tracking_signal,0) <=
2888 decode(cpp.recommend_method,'PNORM',0,'TNORM',0,nvl(cbrb.business_rule_value2,nvl(cuh.tracking_signal,0))))) sq
2889 ON (item_subinv.INVENTORY_ITEM_ID = sq.INVENTORY_ITEM_ID
2890 AND item_subinv.ORGANIZATION_ID = sq.ORGANIZATION_ID
2891 AND item_subinv.SECONDARY_INVENTORY = sq.SECONDARY_INVENTORY)
2892 WHEN MATCHED THEN UPDATE SET item_subinv.MIN_MINMAX_QUANTITY = decode(sq.RECOMMENDED_MAX_QUANTITY,0,0,greatest(1,sq.RECOMMENDED_MIN_QUANTITY)),
2893 item_subinv.MAX_MINMAX_QUANTITY = sq.RECOMMENDED_MAX_QUANTITY,
2894 item_subinv.INVENTORY_PLANNING_CODE = 2
2895 WHEN NOT MATCHED THEN INSERT(item_subinv.INVENTORY_ITEM_ID,
2896 item_subinv.ORGANIZATION_ID,
2897 item_subinv.SECONDARY_INVENTORY,
2898 item_subinv.LAST_UPDATE_DATE,
2899 item_subinv.LAST_UPDATED_BY,
2900 item_subinv.CREATION_DATE,
2901 item_subinv.CREATED_BY,
2902 item_subinv.LAST_UPDATE_LOGIN,
2903 item_subinv.MIN_MINMAX_QUANTITY,
2904 item_subinv.MAX_MINMAX_QUANTITY,
2905 item_subinv.INVENTORY_PLANNING_CODE)
2906 VALUES (sq.INVENTORY_ITEM_ID,
2907 sq.ORGANIZATION_ID,
2908 sq.SECONDARY_INVENTORY,
2909 sysdate,
2910 FND_GLOBAL.user_id,
2911 sysdate,
2912 FND_GLOBAL.user_id,
2913 FND_GLOBAL.conc_login_id,
2914 sq.RECOMMENDED_MIN_QUANTITY,
2915 sq.RECOMMENDED_MAX_QUANTITY,2);
2916 COMMIT;
2917 */
2918 FOR usg_hdr_rec in c_usg_hdr_subinv LOOP
2919 UPDATE MTL_ITEM_SUB_INVENTORIES item_subinv
2920 SET MIN_MINMAX_QUANTITY = usg_hdr_rec.RECOMMENDED_MIN_QUANTITY,
2921 MAX_MINMAX_QUANTITY = usg_hdr_rec.RECOMMENDED_MAX_QUANTITY,
2922 INVENTORY_PLANNING_CODE = 2
2923 WHERE item_subinv.INVENTORY_ITEM_ID = usg_hdr_rec.INVENTORY_ITEM_ID
2924 and item_subinv.ORGANIZATION_ID = usg_hdr_rec.ORGANIZATION_ID
2925 and item_subinv.SECONDARY_INVENTORY = usg_hdr_rec.SECONDARY_INVENTORY;
2926 IF SQL%NOTFOUND THEN
2927 INSERT INTO MTL_ITEM_SUB_INVENTORIES
2928 (INVENTORY_ITEM_ID,
2929 ORGANIZATION_ID,
2930 SECONDARY_INVENTORY,
2931 LAST_UPDATE_DATE,
2932 LAST_UPDATED_BY,
2933 CREATION_DATE,
2934 CREATED_BY,
2935 LAST_UPDATE_LOGIN,
2936 MIN_MINMAX_QUANTITY,
2937 MAX_MINMAX_QUANTITY,
2938 INVENTORY_PLANNING_CODE)
2939 VALUES (usg_hdr_rec.INVENTORY_ITEM_ID,
2940 usg_hdr_rec.ORGANIZATION_ID,
2941 usg_hdr_rec.SECONDARY_INVENTORY,
2942 sysdate,
2943 FND_GLOBAL.user_id,
2944 sysdate,
2945 FND_GLOBAL.user_id,
2946 FND_GLOBAL.conc_login_id,
2947 usg_hdr_rec.RECOMMENDED_MIN_QUANTITY,
2948 usg_hdr_rec.RECOMMENDED_MAX_QUANTITY,2);
2949 END IF;
2950
2951 UPDATE CSP_USAGE_HEADERS
2952 SET PROCESS_STATUS = 'C'
2953 WHERE INVENTORY_ITEM_ID = usg_hdr_rec.INVENTORY_ITEM_ID
2954 AND ORGANIZATION_ID = usg_hdr_rec.ORGANIZATION_ID
2955 AND SECONDARY_INVENTORY = usg_hdr_rec.SECONDARY_INVENTORY
2956 AND HEADER_DATA_TYPE = 1
2957 AND PROCESS_STATUS = 'O';
2958
2959 END LOOP;
2960
2961 COMMIT;
2962
2963 -- Update Item/Organization Min/Max values
2964 /* To fix R12 BUG 5548326 Modified the whole Apply_Business_Rules procedure to same as 115.10 version */
2965 FOR usg_hdr_rec in c_usg_hdr_item LOOP
2966 UPDATE mtl_system_items_b mtl_items
2967 Set min_minmax_quantity = usg_hdr_rec.recommended_min_quantity,
2968 max_minmax_quantity = usg_hdr_rec.recommended_max_quantity,
2969 mtl_items.inventory_planning_code = 2
2970 WHERE INVENTORY_ITEM_ID = usg_hdr_rec.INVENTORY_ITEM_ID
2971 AND ORGANIZATION_ID = usg_hdr_rec.ORGANIZATION_ID;
2972
2973 UPDATE CSP_USAGE_HEADERS
2974 SET PROCESS_STATUS = 'C'
2975 WHERE INVENTORY_ITEM_ID = usg_hdr_rec.INVENTORY_ITEM_ID
2976 AND ORGANIZATION_ID = usg_hdr_rec.ORGANIZATION_ID
2977 AND HEADER_DATA_TYPE = 4
2978 AND PROCESS_STATUS = 'O';
2979 END LOOP;
2980
2981 COMMIT;
2982 End Apply_Business_Rules;
2983
2984 PROCEDURE Create_Usage_rollup (
2985 retcode OUT NOCOPY NUMBER,
2986 errbuf OUT NOCOPY VARCHAR2,
2987 P_Api_Version_Number IN NUMBER
2988 )
2989 IS
2990
2991 l_api_name constant varchar2(30) := 'Create_Rollup';
2992 l_api_version_number CONSTANT NUMBER := 1.0;
2993 l_return_status_full VARCHAR2(1);
2994 l_sqlcode NUMBER;
2995 l_sqlerrm Varchar2(2000);
2996
2997 l_Msg_Count NUMBER;
2998 l_Msg_Data Varchar2(2000);
2999
3000 l_Init_Msg_List VARCHAR2(1) := FND_API.G_TRUE;
3001 l_Commit VARCHAR2(1) := FND_API.G_TRUE;
3002 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
3003
3004 X_Return_Status VARCHAR2(1);
3005 X_Msg_Count NUMBER;
3006 X_Msg_Data VARCHAR2(2000);
3007
3008 BEGIN
3009 -- Standard Start of API savepoint
3010 SAVEPOINT CREATE_ROLLUP_PVT;
3011
3012
3013 --
3014 -- API body
3015 --
3016
3017 --- Refresh Organization and Hierarchy usage Snapshots
3018 DBMS_MVIEW.REFRESH('CSP_USAGE_REG_MV','C');
3019
3020 retcode := 0;
3021 --
3022 -- End of API body
3023 --
3024
3025 -- Standard check for p_commit
3026 IF FND_API.to_Boolean( l_commit )
3027 THEN
3028 COMMIT WORK;
3029 END IF;
3030
3031 -- Standard call to get message count and if count is 1, get message info.
3032 FND_MSG_PUB.Count_And_Get
3033 ( p_count => x_msg_count,
3034 p_data => x_msg_data
3035 );
3036 EXCEPTION
3037 WHEN FND_API.G_EXC_ERROR THEN
3038 retcode := 2;
3039 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3040 P_API_NAME => L_API_NAME
3041 ,P_PKG_NAME => G_PKG_NAME
3042 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3043 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
3044 ,P_ROLLBACK_FLAG => 'N'
3045 ,X_MSG_COUNT => X_MSG_COUNT
3046 ,X_MSG_DATA => X_MSG_DATA
3047 ,X_RETURN_STATUS => X_RETURN_STATUS);
3048
3049 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3050 retcode := 2;
3051 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3052 P_API_NAME => L_API_NAME
3053 ,P_PKG_NAME => G_PKG_NAME
3054 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3055 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
3056 ,P_ROLLBACK_FLAG => 'N'
3057 ,X_MSG_COUNT => X_MSG_COUNT
3058 ,X_MSG_DATA => X_MSG_DATA
3059 ,X_RETURN_STATUS => X_RETURN_STATUS);
3060
3061 WHEN OTHERS THEN
3062 l_sqlcode := SQLCODE;
3063 l_sqlerrm := SQLERRM;
3064 retcode := 2;
3065 errbuf := SQLERRM;
3066 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3067 P_API_NAME => L_API_NAME
3068 ,P_PKG_NAME => G_PKG_NAME
3069 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
3070 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
3071 ,P_ROLLBACK_FLAG => 'N'
3072 ,P_SQLCODE => l_sqlcode
3073 ,P_SQLERRM => l_sqlerrm
3074 ,X_MSG_COUNT => X_MSG_COUNT
3075 ,X_MSG_DATA => X_MSG_DATA
3076 ,X_RETURN_STATUS => X_RETURN_STATUS);
3077 End Create_Usage_Rollup;
3078
3079 PROCEDURE Calculate_Needby_date (
3080 P_Api_Version_Number IN NUMBER,
3081 P_Init_Msg_List IN VARCHAR2,
3082 P_Commit IN VARCHAR2,
3083 P_validation_level IN NUMBER,
3084 P_inventory_item_id IN NUMBER,
3085 P_Organization_id IN NUMBER,
3086 P_Onhand_Quantity IN NUMBER,
3087 X_Needby_date OUT NOCOPY DATE,
3088 X_Return_Status OUT NOCOPY VARCHAR2,
3089 X_Msg_Count OUT NOCOPY NUMBER,
3090 X_Msg_Data OUT NOCOPY VARCHAR2
3091 ) IS
3092 cursor c_usage_headers(p_item_id number,p_org_id number) is
3093 select nvl(cuh.standard_deviation,0) standard_deviation,
3094 cuh.awu ,
3095 cuh.item_cost,
3096 cuh.lead_time,
3097 cpp.edq_factor,
3098 cpp.service_level
3099 from csp_usage_headers cuh,
3100 csp_planning_parameters cpp
3101 where cuh.inventory_item_id = p_item_id
3102 and cuh.organization_id = p_org_id
3103 and cuh.secondary_inventory = '-'
3104 and cuh.header_data_type = 4
3105 and cpp.organization_id = p_org_id
3106 and cpp.secondary_inventory is null;
3107
3108 l_usage_headers_rec c_usage_headers%ROWTYPE;
3109
3110 l_api_name constant varchar2(30) := 'calculate_needby';
3111 l_api_version_number CONSTANT NUMBER := 1.0;
3112 l_return_status_full VARCHAR2(1);
3113 l_return_status NUMBER;
3114 l_sqlcode NUMBER;
3115 l_sqlerrm Varchar2(2000);
3116 l_safety_stock Number;
3117
3118 l_Msg_Count NUMBER;
3119 l_Msg_Data Varchar2(2000);
3120
3121 l_Init_Msg_List VARCHAR2(1) := FND_API.G_TRUE;
3122 l_Commit VARCHAR2(1) := FND_API.G_TRUE;
3123 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
3124
3125
3126 BEGIN
3127 -- Standard Start of API savepoint
3128 SAVEPOINT CALCULATE_NEEDBY_PVT;
3129
3130 -- Standard call to check for call compatibility.
3131 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3132 p_api_version_number,
3133 l_api_name,
3134 G_PKG_NAME)
3135 THEN
3136 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3137 END IF;
3138
3139
3140 -- Initialize message list if p_init_msg_list is set to TRUE.
3141 IF FND_API.to_Boolean( p_init_msg_list )
3142 THEN
3143 FND_MSG_PUB.initialize;
3144 END IF;
3145
3146 -- Initialize API return status to SUCCESS
3147 x_return_status := FND_API.G_RET_STS_SUCCESS;
3148
3149 --
3150 -- API body
3151 --
3152 Open c_usage_headers(p_inventory_item_id,p_organization_id);
3153 Fetch c_usage_headers into l_usage_headers_rec;
3154 Close c_usage_headers;
3155
3156 l_safety_stock := csp_pick_utils.get_safety_stock(
3157 p_subinventory => null,
3158 p_organization_id => null,
3159 p_edq_factor => l_usage_headers_rec.edq_factor,
3160 p_service_level => l_usage_headers_rec.service_level,
3161 p_item_cost => l_usage_headers_rec.item_cost,
3162 p_awu => l_usage_headers_rec.awu,
3163 p_lead_time => l_usage_headers_rec.lead_time,
3164 p_standard_deviation => l_usage_headers_rec.standard_deviation,
3165 p_safety_stock_flag => 'Y',
3166 p_asl_flag => NULL);
3167 If nvl(l_usage_headers_rec.awu,0) > 0 Then
3168 X_needby_date := trunc(sysdate) +
3169 ((p_onhand_quantity - l_safety_stock)/
3170 l_usage_headers_rec.awu) * 7;
3171 If x_needby_date < trunc(sysdate) Then
3172 x_needby_date := trunc(sysdate);
3173 end if;
3174 end if;
3175
3176 --
3177 -- End of API body
3178 --
3179
3180 -- Standard check for p_commit
3181 IF FND_API.to_Boolean( l_commit )
3182 THEN
3183 COMMIT WORK;
3184 END IF;
3185
3186 -- Standard call to get message count and if count is 1, get message info.
3187 FND_MSG_PUB.Count_And_Get
3188 ( p_count => x_msg_count,
3189 p_data => x_msg_data
3190 );
3191
3192 EXCEPTION
3193 WHEN FND_API.G_EXC_ERROR THEN
3194 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3195 P_API_NAME => L_API_NAME
3196 ,P_PKG_NAME => G_PKG_NAME
3197 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
3198 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
3199 ,P_ROLLBACK_FLAG => 'N'
3200 ,X_MSG_COUNT => X_MSG_COUNT
3201 ,X_MSG_DATA => X_MSG_DATA
3202 ,X_RETURN_STATUS => X_RETURN_STATUS);
3203
3204 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3205 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3206 P_API_NAME => L_API_NAME
3207 ,P_PKG_NAME => G_PKG_NAME
3208 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
3209 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
3210 ,P_ROLLBACK_FLAG => 'N'
3211 ,X_MSG_COUNT => X_MSG_COUNT
3212 ,X_MSG_DATA => X_MSG_DATA
3213 ,X_RETURN_STATUS => X_RETURN_STATUS);
3214
3215 WHEN OTHERS THEN
3216 l_sqlcode := SQLCODE;
3217 l_sqlerrm := SQLERRM;
3218 JTF_PLSQL_API.HANDLE_EXCEPTIONS(
3219 P_API_NAME => L_API_NAME
3220 ,P_PKG_NAME => G_PKG_NAME
3221 ,P_EXCEPTION_LEVEL => JTF_PLSQL_API.G_EXC_OTHERS
3222 ,P_PACKAGE_TYPE => JTF_PLSQL_API.G_PVT
3223 ,P_ROLLBACK_FLAG => 'N'
3224 ,P_SQLCODE => l_sqlcode
3225 ,P_SQLERRM => l_sqlerrm
3226 ,X_MSG_COUNT => X_MSG_COUNT
3227 ,X_MSG_DATA => X_MSG_DATA
3228 ,X_RETURN_STATUS => X_RETURN_STATUS);
3229 End Calculate_needby_date;
3230
3231 End CSP_AUTO_ASLMSL_PVT;