[Home] [Help]
PACKAGE BODY: APPS.CSP_NOTIFICATIONS_PVT
Source
1 PACKAGE BODY CSP_NOTIFICATIONS_PVT AS
2 /* $Header: cspvpnob.pls 120.0 2005/05/25 11:25:56 appldev noship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30):='CSP_NOTIFICATIONS_PVT';
4 PROCEDURE calculate_loop
5 ( p_api_version IN NUMBER,
6 p_parts_loop_id IN NUMBER,
7 p_inventory_item_id IN NUMBER,
8 p_include_intransit_mo VARCHAR2 ,
9 p_include_interorg_transfers VARCHAR2 ,
10 p_include_sales_orders VARCHAR2 ,
11 p_include_move_orders VARCHAR2 ,
12 p_include_requisitions VARCHAR2 ,
13 p_include_purchase_orders VARCHAR2 ,
14 p_include_work_orders VARCHAR2 ,
15 p_include_onhand_good VARCHAR2 ,
16 p_include_onhand_bad VARCHAR2 ,
17 p_tolerance_percent NUMBER ,
18 x_above OUT NOCOPY NUMBER,
19 x_below OUT NOCOPY NUMBER,
20 x_not_enough_good_parts OUT NOCOPY NUMBER,
21 x_quantity_level OUT NOCOPY NUMBER,
22 x_onhand_good OUT NOCOPY NUMBER,
23 x_min_good OUT NOCOPY NUMBER,
24 x_total_loop_quantity OUT NOCOPY NUMBER,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2
28 )
29 IS
30 l_api_name CONSTANT VARCHAR2(30) := 'calculate_loop';
31 l_api_version CONSTANT NUMBER := 1.0;
32 l_quantity_level NUMBER;
33 l_above NUMBER;
34 l_below NUMBER;
35 l_negp NUMBER;
36 l_onhand_good NUMBER;
37 l_min_good NUMBER;
38 l_total_loop_quantity NUMBER;
39 l_include_intransit_mo csp_loop_calc_rules_b.include_intransit_move_orders%TYPE;
40 l_include_move_orders csp_loop_calc_rules_b.include_move_orders%TYPE;
41 l_include_work_orders csp_loop_calc_rules_b.include_work_orders%TYPE;
42 l_include_purchase_orders csp_loop_calc_rules_b.include_purchase_orders%TYPE;
43 l_include_requisitions csp_loop_calc_rules_b.include_requisitions%TYPE;
44 l_include_interorg_transfers csp_loop_calc_rules_b.include_interorg_transfers%TYPE;
45 l_include_onhand_good csp_loop_calc_rules_b.include_onhand_good%TYPE;
46 l_include_onhand_bad csp_loop_calc_rules_b.include_onhand_bad%TYPE;
47 l_include_sales_orders csp_loop_calc_rules_b.include_sales_orders%TYPE;
48 l_tolerance_percent csp_loop_calc_rules_b.tolerance_percent%TYPE;
49
50 CURSOR c_calculation_rule IS
51 SELECT clcrb.include_sales_orders
52 , clcrb.include_move_orders
53 , clcrb.include_work_orders
54 , clcrb.include_purchase_orders
55 , clcrb.include_requisitions
56 , clcrb.include_interorg_transfers
57 , clcrb.include_onhand_good
58 , clcrb.include_onhand_bad
59 , clcrb.include_intransit_move_orders
60 , clcrb.tolerance_percent
61 FROM csp_loop_calc_rules_b clcrb
62 , csp_parts_loops_b cplb
63 WHERE cplb.parts_loop_id = p_parts_loop_id
64 AND cplb.calculation_rule_id = clcrb.calculation_rule_id;
65
66 /*CURSOR c_supply_demand IS
67 SELECT decode(l_include_intransit_mo,'Y',nvl(ccsds.intransit_move_orders,0),0)
68 + decode(l_include_interorg_transfers,'Y',nvl(ccsds.open_interorg_transf_in,0)-nvl(ccsds.open_interorg_transf_out,0),0)
69 + decode(l_include_sales_orders,'Y',nvl(ccsds.open_sales_orders,0),0)
70 + decode(l_include_move_orders,'Y',nvl(ccsds.open_move_orders_in,0)-nvl(ccsds.open_move_orders_out,0),0)
71 + decode(l_include_requisitions,'Y',nvl(ccsds.open_requisitions,0),0)
72 + decode(l_include_purchase_orders,'Y',nvl(ccsds.open_purchase_orders,0),0)
73 + decode(l_include_work_orders,'Y',nvl(ccsds.open_work_orders,0),0)
74 + decode(l_include_onhand_good,'Y',nvl(ccsds.onhand_good,0),0)
75 + decode(l_include_onhand_bad,'Y',nvl(ccsds.onhand_bad,0),0) quantity_level
76 , ccsds.onhand_good
77 , cmsi.total_loop_min_good_quantity
78 , cmsi.total_loop_quantity
79 FROM csp_mstrstck_lists_itms cmsi
80 , csp_curr_sup_dem_sums ccsds
81 WHERE parts_loop_id = p_parts_loop_id
82 AND subinventory_code is null
83 AND ccsds.parts_loop_id = cmsi.parts_loops_id
84 AND ccsds.inventory_item_id = cmsi.inventory_item_id
85 AND ccsds.inventory_item_id = nvl(p_inventory_item_id,ccsds.inventory_item_id);*/
86 /*
87 CURSOR c_supply_demand IS
88 SELECT decode(l_include_intransit_mo,'Y',nvl(ccsds.intransit_move_orders,0),0)
89 + decode(l_include_interorg_transfers,'Y',nvl(ccsds.open_interorg_transf_in,0)-nvl(ccsds.open_interorg_transf_out,0),0)
90 + decode(l_include_sales_orders,'Y',nvl(ccsds.open_sales_orders,0),0)
91 + decode(l_include_move_orders,'Y',nvl(ccsds.open_move_orders_in,0)-nvl(ccsds.open_move_orders_out,0),0)
92 + decode(l_include_requisitions,'Y',nvl(ccsds.open_requisitions,0),0)
93 + decode(l_include_purchase_orders,'Y',nvl(ccsds.open_purchase_orders,0),0)
94 + decode(l_include_work_orders,'Y',nvl(ccsds.open_work_orders,0),0)
95 + decode(l_include_onhand_good,'Y',nvl(ccsds.onhand_good,0),0)
96 + decode(l_include_onhand_bad,'Y',nvl(ccsds.onhand_bad,0),0) quantity_level
97 , nvl(ccsds.onhand_good,0)
98 , nvl(cmsi.total_loop_min_good_quantity,0)
99 , nvl(cmsi.total_loop_quantity,0)
100 FROM csp_mstrstck_lists_itms cmsi
101 , csp_curr_sup_dem_sums ccsds
102 WHERE cmsi.parts_loops_id = p_parts_loop_id
103 AND ccsds.parts_loop_id(+) = cmsi.parts_loops_id
104 AND ccsds.subinventory_code(+) is null
105 AND cmsi.inventory_item_id = p_inventory_item_id
106 AND ccsds.inventory_item_id(+) = cmsi.inventory_item_id;
107 */
108 CURSOR c_supply_demand IS
109 SELECT decode(l_include_intransit_mo,'Y',nvl(ccsds.intransit_move_orders,0),0)
110 + decode(l_include_interorg_transfers,'Y',nvl(ccsds.interorg_transf_in,0)-nvl(ccsds.interorg_transf_out,0),0)
111 + decode(l_include_sales_orders,'Y',nvl(ccsds.sales_orders,0),0)
112 + decode(l_include_move_orders,'Y',nvl(ccsds.move_orders_in,0)-nvl(ccsds.move_orders_out,0),0)
113 + decode(l_include_requisitions,'Y',nvl(ccsds.requisitions,0),0)
114 + decode(l_include_purchase_orders,'Y',nvl(ccsds.purchase_orders,0),0)
115 + decode(l_include_work_orders,'Y',nvl(ccsds.work_orders,0),0)
116 + decode(l_include_onhand_good,'Y',nvl(ccsds.onhand_good,0),0)
117 + decode(l_include_onhand_bad,'Y',nvl(ccsds.onhand_bad,0),0) quantity_level
118 , nvl(ccsds.onhand_good,0)
119 , nvl(cmsi.total_loop_min_good_quantity,0)
120 , nvl(cmsi.total_loop_quantity,0)
121 FROM csp_mstrstck_lists_itms cmsi
122 , CSP_SUP_DEM_PL_MV ccsds
123 WHERE cmsi.parts_loops_id = p_parts_loop_id
124 AND ccsds.parts_loop_id(+) = cmsi.parts_loops_id
125 AND cmsi.inventory_item_id = p_inventory_item_id
126 AND ccsds.inventory_item_id(+) = cmsi.inventory_item_id;
127
128 BEGIN
129
130 -- Standard Start of API savepoint
131 SAVEPOINT calculate_loop_pvt;
132 -- Standard call to check for call compatibility.
133 IF NOT FND_API.Compatible_API_Call (l_api_version ,
134 p_api_version ,
135 l_api_name ,
136 G_PKG_NAME )
137 THEN
138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
139 END IF;
140 -- Initialize API return status to success
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142
143 IF p_include_move_orders is null THEN
144 OPEN c_calculation_rule;
145 FETCH c_calculation_rule into
146 l_include_sales_orders
147 , l_include_move_orders
148 , l_include_work_orders
149 , l_include_purchase_orders
150 , l_include_requisitions
151 , l_include_interorg_transfers
152 , l_include_onhand_good
153 , l_include_onhand_bad
154 , l_include_intransit_mo
155 , l_tolerance_percent;
156 CLOSE c_calculation_rule;
157 ELSE
158 l_include_sales_orders := p_include_sales_orders;
159 l_include_move_orders := p_include_move_orders;
160 l_include_work_orders := p_include_work_orders;
161 l_include_purchase_orders := p_include_purchase_orders;
162 l_include_requisitions := p_include_requisitions;
163 l_include_interorg_transfers := p_include_interorg_transfers;
164 l_include_onhand_good := p_include_onhand_good;
165 l_include_onhand_bad := p_include_onhand_bad;
166 l_include_intransit_mo := p_include_intransit_mo;
167 l_tolerance_percent := p_tolerance_percent;
168 END IF;
169
170 OPEN c_supply_demand;
171 FETCH c_supply_demand into l_quantity_level, l_onhand_good, l_min_good, l_total_loop_quantity;
172 CLOSE c_supply_demand;
173
174 x_above := l_quantity_level - (l_total_loop_quantity*(1+nvl(l_tolerance_percent,0)/100));
175 x_below := l_total_loop_quantity - (l_total_loop_quantity*nvl(l_tolerance_percent,0)/100) - l_quantity_level;
176 x_not_enough_good_parts := l_min_good - l_onhand_good;
177 x_quantity_level := l_quantity_level;
178 x_onhand_good := l_onhand_good;
179 x_min_good := l_min_good;
180 x_total_loop_quantity := l_total_loop_quantity;
181
182 /*dbms_output.put_line(p_inventory_item_id || ' ' || 'x_above' || x_above || 'x_below ' || x_below ||
183 x_not_enough_good_parts || ' ' || x_quantity_level || ' ' || x_onhand_good);*/
184
185 -- Standard call to get message count and if count is 1, get message info.
186 FND_MSG_PUB.Count_And_Get
187 (p_count => x_msg_count ,
188 p_data => x_msg_data
189 );
190 EXCEPTION
191 WHEN FND_API.G_EXC_ERROR THEN
192 ROLLBACK TO calculate_loop_pvt;
193 x_return_status := FND_API.G_RET_STS_ERROR ;
194 FND_MSG_PUB.Count_And_Get
195 (p_count => x_msg_count ,
196 p_data => x_msg_data
197 );
198 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
199 ROLLBACK TO calculate_loop_pvt;
200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
201 FND_MSG_PUB.Count_And_Get
202 (p_count => x_msg_count ,
203 p_data => x_msg_data
204 );
205 WHEN OTHERS THEN
206 ROLLBACK TO calculate_loop_pvt;
207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
208 IF FND_MSG_PUB.Check_Msg_Level
209 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
210 THEN
211 FND_MSG_PUB.Add_Exc_Msg
212 ( G_PKG_NAME ,
213 l_api_name
214 );
215 END IF;
216 FND_MSG_PUB.Count_And_Get
217 (p_count => x_msg_count ,
218 p_data => x_msg_data
219 );
220 END calculate_loop;
221
222 PROCEDURE create_notifications
223 ( errbuf OUT NOCOPY VARCHAR2,
224 retcode OUT NOCOPY NUMBER,
225 p_api_version IN NUMBER,
226 p_organization_id IN NUMBER
227 )
228 IS
229 l_api_name CONSTANT VARCHAR2(30) := 'create_notifications';
230 l_api_version CONSTANT NUMBER := 1.0;
231 l_parts_loop_id csp_parts_loops_b.parts_loop_id%TYPE;
232 l_above NUMBER;
233 l_below NUMBER;
234 l_not_enough_good_parts NUMBER;
235 l_quantity_level NUMBER;
236 l_onhand_good NUMBER;
237 l_min_good NUMBER;
238 l_total_loop_quantity NUMBER;
239 l_return_status VARCHAR2(1);
240 l_msg_count NUMBER;
241 l_msg_data VARCHAR2(2000);
242 l_notification_id NUMBER;
243 l_cursor NUMBER;
244 l_ddl_string VARCHAR2(100);
245 l_planner_code VARCHAR2(10);
246 l_temp_inv_item_id NUMBER;
247
248 CURSOR c_parts_loops IS
249 SELECT cplb.parts_loop_id
250 , cplb.planner_code
251 , cplb.organization_id
252 , clcrb.include_sales_orders
253 , clcrb.include_move_orders
254 , clcrb.include_work_orders
255 , clcrb.include_purchase_orders
256 , clcrb.include_requisitions
257 , clcrb.include_interorg_transfers
258 , clcrb.include_onhand_good
259 , clcrb.include_onhand_bad
260 , clcrb.include_intransit_move_orders
261 , clcrb.tolerance_percent
262 FROM csp_loop_calc_rules_b clcrb
263 , csp_parts_loops_b cplb
264 WHERE cplb.calculation_rule_id = clcrb.calculation_rule_id;
265
266 /*CURSOR c_items(c_parts_loop_id NUMBER) IS
267 SELECT ccsds.inventory_item_id
268 FROM csp_curr_sup_dem_sums ccsds
269 WHERE parts_loop_id = c_parts_loop_id
270 AND subinventory_code is null;*/
271
272 CURSOR c_items(c_parts_loop_id NUMBER) IS
273 SELECT INVENTORY_ITEM_ID inventory_item_id
274 FROM CSP_MSTRSTCK_LISTS_ITMS
275 WHERE PARTS_LOOPS_ID = c_parts_loop_id;
276
277 CURSOR get_planner(c_parts_loop_id number,
278 c_inventory_item_id number) IS
279 SELECT planner_code
280 FROM csp_mstrstck_lists_itms
281 WHERE parts_loops_id = c_parts_loop_id
282 AND inventory_item_id = c_inventory_item_id;
283 l_get_app_info boolean;
284 l_status varchar2(1);
285 l_industry varchar2(1);
286 l_oracle_schema varchar2(30);
287 BEGIN
288 l_get_app_info := fnd_installation.get_app_info('CSP',l_status,l_industry, l_oracle_schema);
289 l_ddl_string := 'truncate table '||l_oracle_schema||'.csp_notifications';
290 l_cursor := dbms_sql.open_cursor;
291 dbms_sql.parse(l_cursor,l_ddl_string,dbms_sql.native);
292 dbms_sql.close_cursor(l_cursor);
293
294 -- Standard Start of API savepoint
295 SAVEPOINT create_notifications_pvt;
296 -- Standard call to check for call compatibility.
297 IF NOT FND_API.Compatible_API_Call (l_api_version ,
298 p_api_version ,
299 l_api_name ,
300 G_PKG_NAME )
301 THEN
302 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
303 END IF;
304 -- Initialize API return status to success
305 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
306
307 FOR cr IN c_parts_loops LOOP
308 FOR curs IN c_items(cr.parts_loop_id) LOOP
309
310 open get_planner(cr.parts_loop_id,curs.inventory_item_id);
311 fetch get_planner into l_planner_code;
312 close get_planner;
313
314 csp_notifications_pvt.calculate_loop(
315 p_api_version => 1.0,
316 p_parts_loop_id => cr.parts_loop_id,
317 p_inventory_item_id => curs.inventory_item_id,
318 p_include_intransit_mo => cr.include_intransit_move_orders,
319 p_include_interorg_transfers => cr.include_interorg_transfers,
320 p_include_sales_orders => cr.include_sales_orders,
321 p_include_move_orders => cr.include_move_orders,
322 p_include_requisitions => cr.include_requisitions,
323 p_include_purchase_orders => cr.include_purchase_orders,
324 p_include_work_orders => cr.include_work_orders,
325 p_include_onhand_good => cr.include_onhand_good,
326 p_include_onhand_bad => cr.include_onhand_bad,
327 p_tolerance_percent => cr.tolerance_percent,
328 x_above => l_above,
329 x_below => l_below,
330 x_not_enough_good_parts => l_not_enough_good_parts,
331 x_quantity_level => l_quantity_level,
332 x_onhand_good => l_onhand_good,
336 x_msg_count => l_msg_count,
333 x_min_good => l_min_good,
334 x_total_loop_quantity => l_total_loop_quantity,
335 x_return_status => l_return_status,
337 x_msg_data => l_msg_data
338 );
339
340 IF l_above > 0 THEN
341 l_notification_id := null;
342 csp_notifications_pkg.insert_row(
343 px_notification_id => l_notification_id,
344 p_created_by => fnd_global.user_id,
345 p_creation_date => sysdate,
346 p_last_updated_by => fnd_global.user_id,
347 p_last_update_date => sysdate,
348 p_last_update_login => null,
349 p_planner_code => l_planner_code,
350 p_parts_loop_id => cr.parts_loop_id,
351 p_organization_id => cr.organization_id,
352 p_inventory_item_id => curs.inventory_item_id,
353 p_notification_date => sysdate,
354 p_reason => 'A',
355 p_status => '1',
356 p_quantity => l_quantity_level - l_total_loop_quantity,
357 p_attribute_category=> null,
358 p_attribute1 => null,
359 p_attribute2 => null,
360 p_attribute3 => null,
361 p_attribute4 => null,
362 p_attribute5 => null,
363 p_attribute6 => null,
364 p_attribute7 => null,
365 p_attribute8 => null,
366 p_attribute9 => null,
367 p_attribute10 => null,
368 p_attribute11 => null,
369 p_attribute12 => null,
370 p_attribute13 => null,
371 p_attribute14 => null,
372 p_attribute15 => null);
373 END IF;
374 IF l_below > 0 THEN
375 l_notification_id := null;
376 csp_notifications_pkg.insert_row(
377 px_notification_id => l_notification_id,
378 p_created_by => fnd_global.user_id,
379 p_creation_date => sysdate,
380 p_last_updated_by => fnd_global.user_id,
381 p_last_update_date => sysdate,
382 p_last_update_login => null,
383 p_planner_code => l_planner_code,
384 p_parts_loop_id => cr.parts_loop_id,
385 p_organization_id => cr.organization_id,
386 p_inventory_item_id => curs.inventory_item_id,
387 p_notification_date => sysdate,
388 p_reason => 'B',
389 p_status => '1',
390 p_quantity => l_total_loop_quantity - l_quantity_level,
391 p_attribute_category=> null,
392 p_attribute1 => null,
393 p_attribute2 => null,
394 p_attribute3 => null,
395 p_attribute4 => null,
396 p_attribute5 => null,
397 p_attribute6 => null,
398 p_attribute7 => null,
399 p_attribute8 => null,
400 p_attribute9 => null,
401 p_attribute10 => null,
402 p_attribute11 => null,
403 p_attribute12 => null,
404 p_attribute13 => null,
405 p_attribute14 => null,
406 p_attribute15 => null);
407 END IF;
408 IF l_not_enough_good_parts > 0 THEN
409 l_notification_id := null;
410 csp_notifications_pkg.insert_row(
411 px_notification_id => l_notification_id,
412 p_created_by => fnd_global.user_id,
413 p_creation_date => sysdate,
414 p_last_updated_by => fnd_global.user_id,
415 p_last_update_date => sysdate,
416 p_last_update_login => null,
417 p_planner_code => l_planner_code,
418 p_parts_loop_id => cr.parts_loop_id,
419 p_organization_id => cr.organization_id,
420 p_inventory_item_id => curs.inventory_item_id,
421 p_notification_date => sysdate,
422 p_reason => 'N',
423 p_status => '1',
424 p_quantity => l_not_enough_good_parts,
425 p_attribute_category=> null,
426 p_attribute1 => null,
427 p_attribute2 => null,
428 p_attribute3 => null,
429 p_attribute4 => null,
430 p_attribute5 => null,
431 p_attribute6 => null,
432 p_attribute7 => null,
433 p_attribute8 => null,
434 p_attribute9 => null,
435 p_attribute10 => null,
436 p_attribute11 => null,
437 p_attribute12 => null,
438 p_attribute13 => null,
439 p_attribute14 => null,
440 p_attribute15 => null);
441 END IF;
442 END LOOP;
443 END LOOP;
444
445 -- Standard call to get message count and if count is 1, get message info.
446 -- FND_MSG_PUB.Count_And_Get
447 -- (p_count => x_msg_count ,
448 -- p_data => x_msg_data
449 -- );
450 EXCEPTION
451 WHEN FND_API.G_EXC_ERROR THEN
452 ROLLBACK TO create_notifications_pvt;
453 -- x_return_status := FND_API.G_RET_STS_ERROR ;
454 -- FND_MSG_PUB.Count_And_Get
455 -- (p_count => x_msg_count ,
456 -- p_data => x_msg_data
457 -- );
458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
459 ROLLBACK TO create_notifications_pvt;
460 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
461 -- FND_MSG_PUB.Count_And_Get
462 -- (p_count => x_msg_count ,
463 -- p_data => x_msg_data
464 -- );
465 WHEN OTHERS THEN
466 ROLLBACK TO create_notifications_pvt;
467 -- x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
468 IF FND_MSG_PUB.Check_Msg_Level
469 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
470 THEN
471 FND_MSG_PUB.Add_Exc_Msg
472 ( G_PKG_NAME ,
473 l_api_name
474 );
475 END IF;
476 -- FND_MSG_PUB.Count_And_Get
477 -- (p_count => x_msg_count ,
478 -- p_data => x_msg_data
479 -- );
480 END create_notifications;
481 END csp_notifications_pvt;