1 PACKAGE BODY AHL_VWP_PRICE_PVT AS
2 /* $Header: AHLVVPRB.pls 120.5 2006/09/18 14:28:19 anraj noship $ */
3
4 -- Define global internal variables
5 G_PKG_NAME VARCHAR2(30) := 'AHL_VWP_PRICE_PVT';
6
7 /* $Header: AHLVVPRB.pls 120.5 2006/09/18 14:28:19 anraj noship $ */
8 -----------------------------------------------------------
9 -- PACKAGE
10 -- AHL_VWP_PRICE_PVT
11 --
12 -- PURPOSE
13 -- This package specification is a Private API for managing
14 -- APIs that integrate with Advanced Pricing
15 -- in Complex Maintainance, Repair and Overhauling(CMRO).
16 --
17 -- It defines the following APIs
18 --
19 -- Calculate_Totle_Price (see below for specification)
20 --
21 -- NOTES
22 --
23 --
24 -- HISTORY
25 -- 02-Sep-2003 yazhou Created.
26 --------------------------------------------------------------------
27
28 PROCEDURE Calculate_Total_Price
29 (p_item_tbl IN AHL_VWP_RULES_PVT.Item_Tbl_Type,
30 p_price_list_id IN NUMBER,
31 p_customer_id IN NUMBER,
32 p_currency_code IN VARCHAR2,
33
34 x_total_price OUT NOCOPY NUMBER,
35 x_return_status OUT NOCOPY VARCHAR2
36 );
37
38 PROCEDURE Check_UOM_Class
39 (p_UOM_Code IN VARCHAR2,
40 x_time_type_flag OUT NOCOPY VARCHAR2
41 );
42
43 --------------------------------------------------------------------
44 -- Procedure name : Get_Task_Estimated_Price
45 -- Purpose : To return estimated price for a given task.
46 --------------------------------------------------------------------
47 PROCEDURE Get_Task_Estimated_Price
48 (p_visit_task_id IN NUMBER,
49 p_route_id IN NUMBER,
50 p_price_list_id IN NUMBER,
51 p_customer_id IN NUMBER,
52 p_currency_code IN VARCHAR2,
53 p_effective_date IN DATE,
54 p_organization_id IN NUMBER,
55
56 x_estimated_price OUT NOCOPY NUMBER,
57 x_return_status OUT NOCOPY VARCHAR2
58 )
59 IS
60 -- Define local variables
61 l_x_return_status VARCHAR2(240);
62 l_total_price NUMBER := 0; -- total price
63
64 l_res_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
65 l_mat_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
66 l_x_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
67
68 i NUMBER := 0; -- table index
69 l_api_name CONSTANT VARCHAR2(30) := 'Get_Task_Estimated_Price';
70
71
72 BEGIN
73
74 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
75 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
76 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
77 'At the start of the procedure');
78 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
79 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
80 'Visit Task ID: '|| p_visit_task_id);
81 END IF;
82
83 --Initialize API return status to success
84 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
85
86 -- Get the items from Resource Requirements of the route
87
88 Check_Item_for_Rt_Res_Req
89 (p_visit_task_id =>p_visit_task_id ,
90 p_route_id =>p_route_id ,
91 p_organization_id =>p_organization_id ,
92 p_effective_date =>p_effective_date,
93
94 x_item_tbl =>l_res_item_tbl ,
95 x_return_status =>l_x_return_status
96 );
97
98 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
99 RAISE Fnd_Api.G_EXC_ERROR;
100 END IF;
101
102 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
103 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
104 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Checking Items for Resource Requirements',
105 'Return Status is: '|| l_x_return_status);
106 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
107 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Checking Items for Resource Requirements',
108 'Number of Items: '||l_mat_item_tbl.COUNT );
109 END IF;
110
111 -- Get the items from Material Requirements of the route
112
113 Check_Item_for_Rt_Mat_Req
114 (p_visit_task_id =>p_visit_task_id ,
115 p_route_id =>p_route_id ,
116 p_effective_date =>p_effective_date,
117
118 x_item_tbl =>l_mat_item_tbl ,
119 x_return_status =>l_x_return_status
120 );
121
122 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
123 RAISE Fnd_Api.G_EXC_ERROR;
124 END IF;
125
126 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
127 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
128 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Checking Items for Material Requirements',
129 'Return Status is: '|| l_x_return_status);
130 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
131 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Checking Items for Material Requirements',
132 'Number of Items: '||l_mat_item_tbl.COUNT );
133 END IF;
134
135 -- Merge the two Item table to get the item table
136 -- that the same task, item and UOM combination appear only once
137
138
139 AHL_VWP_RULES_PVT.Merge_for_Unique_Items
140 (p_item_tbl1 =>l_res_item_tbl ,
141 p_item_tbl2 =>l_mat_item_tbl ,
142
143 x_item_tbl =>l_x_item_tbl
144 );
145
146
147 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
148 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
149 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Merge Items',
150 'Number of Items: '||l_x_item_tbl.COUNT );
151 END IF;
152
153 -- Calculate the price for the items required by the route
154 IF l_x_item_tbl.count > 0 THEN
155
156 Calculate_Total_Price
157 (p_item_tbl =>l_x_item_tbl,
158 p_price_list_id =>p_price_list_id ,
159 p_customer_id =>p_customer_id ,
160 p_currency_code =>p_currency_code ,
161
162 x_total_price =>l_total_price ,
163 x_return_status =>l_x_return_status
164 );
165
166 END IF;
167
168 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
169 RAISE Fnd_Api.G_EXC_ERROR;
170 END IF;
171
172 -- Assign return status and Item without Price table
173 x_return_status:=l_x_return_status;
174 x_estimated_price := l_total_price;
175
176 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
177 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
178 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
179 'Return Status is: '|| x_return_status);
180 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
181 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
182 'Total Price: '|| x_estimated_price);
183 END IF;
184
185
186 END Get_Task_Estimated_Price;
187
188 --------------------------------------------------------------------
189 -- Procedure name : Get_Job_Estimated_Price
190 -- Purpose : To return estimated price for the job of a given task.
191 --------------------------------------------------------------------
192 PROCEDURE Get_Job_Estimated_Price
193 (p_visit_task_id IN NUMBER,
194 p_price_list_id IN NUMBER,
195 p_customer_id IN NUMBER,
196 p_currency_code IN VARCHAR2,
197
198 x_estimated_price OUT NOCOPY NUMBER,
199 x_return_status OUT NOCOPY VARCHAR2
200 )
201 IS
202 -- Define local variables
203 l_x_return_status VARCHAR2(240);
204 l_total_price NUMBER := 0; -- total price
205
206 l_res_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
207 l_mat_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
208 l_x_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
209
210 i NUMBER := 0; -- table index
211 l_api_name CONSTANT VARCHAR2(30) := 'Get_Job_Estimated_Price';
212
213
214 BEGIN
215
216 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
217 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
218 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
219 'At the start of the procedure');
220 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
221 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
222 'Visit Task ID: '|| p_visit_task_id);
223 END IF;
224
225 --Initialize API return status to success
226 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
227
228 -- Get the items from Resource Requirements of the route
229
230 Check_Item_for_Prod_Res_Req
231 (p_visit_task_id =>p_visit_task_id ,
232
233 x_item_tbl =>l_res_item_tbl ,
234 x_return_status =>l_x_return_status
235 );
236
237 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
238 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
239 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Checking Items for Resource Requirements',
240 'Return Status is: '|| l_x_return_status);
241 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
242 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Checking Items for Resource Requirements',
243 'Number of Items: '||l_mat_item_tbl.COUNT );
244 END IF;
245
246 -- Get the items from Material Requirements of the route
247
248 Check_Item_for_Prod_Mat_Req
249 (p_visit_task_id =>p_visit_task_id ,
250
251 x_item_tbl =>l_mat_item_tbl ,
252 x_return_status =>l_x_return_status
253 );
254
255 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
256 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
257 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Checking Items for Material Requirements',
258 'Return Status is: '|| l_x_return_status);
259 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
260 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Checking Items for Material Requirements',
261 'Number of Items: '||l_mat_item_tbl.COUNT );
262 END IF;
263
264 -- Merge the two Item table to get the item table
265 -- that the same task, item and UOM combination appear only once
266
267 AHL_VWP_RULES_PVT.Merge_for_Unique_Items
268 (p_item_tbl1 =>l_res_item_tbl ,
269 p_item_tbl2 =>l_mat_item_tbl ,
270
271 x_item_tbl =>l_x_item_tbl
272 );
273
274
275 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
276 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
277 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Merge Items',
278 'Number of Items: '||l_x_item_tbl.COUNT );
279 END IF;
280
281 -- Calculate the price for the items required by the route
282
283 IF l_x_item_tbl.count > 0 THEN
284
285 Calculate_Total_Price
286 (p_item_tbl =>l_x_item_tbl,
287 p_price_list_id =>p_price_list_id ,
288 p_customer_id =>p_customer_id ,
289 p_currency_code =>p_currency_code ,
290
291 x_total_price =>l_total_price ,
292 x_return_status =>l_x_return_status
293 );
294
295 END IF;
296
297 -- Assign return status and Item without Price table
298 x_return_status:=l_x_return_status;
299 x_estimated_price := l_total_price;
300
301 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
302 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
303 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
304 'Return Status is: '|| x_return_status);
305 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
306 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
307 'Total Price: '|| x_estimated_price);
308 END IF;
309
310
311 END Get_Job_Estimated_Price;
312
313 --------------------------------------------------------------------
314 -- Procedure name : Get_Job_Actual_Price
315 -- Purpose : To return actual price for the job of a given task.
316 --------------------------------------------------------------------
317 PROCEDURE Get_Job_Actual_Price
318 (p_visit_task_id IN NUMBER,
319 p_price_list_id IN NUMBER,
320 p_customer_id IN NUMBER,
321 p_currency_code IN VARCHAR2,
322
323 x_actual_price OUT NOCOPY NUMBER,
324 x_return_status OUT NOCOPY VARCHAR2
325 )
326 IS
327 -- Define local variables
328 l_x_return_status VARCHAR2(240);
329 l_total_price NUMBER := 0; -- total price
330
331 l_res_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
332 l_mat_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
333 l_x_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
334
335 i NUMBER := 0; -- table index
336 l_api_name CONSTANT VARCHAR2(30) := 'Get_Task_Actual_Price';
337
338
339 BEGIN
340
341 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
342 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
343 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
344 'At the start of the procedure');
345 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
346 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
347 'Visit Task ID: '|| p_visit_task_id);
348 END IF;
349
350 --Initialize API return status to success
351 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
352
353 -- Get the items from Resource Transactions of the WIP job of this task
354
355 Check_Item_for_Resource_Trans
356 (p_visit_task_id =>p_visit_task_id ,
357
358 x_item_tbl =>l_res_item_tbl ,
359 x_return_status =>l_x_return_status
360 );
361
362 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
363 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
364 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Checking Items for Resource Transactions',
365 'Return Status is: '|| l_x_return_status);
366 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
367 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Checking Items for Resource Transactions',
368 'Number of Items: '||l_mat_item_tbl.COUNT );
369 END IF;
370
371 -- Get the items from Material Transactions of the WIP job of this task
372
373 Check_Item_for_Materials_Trans
374 (p_visit_task_id =>p_visit_task_id ,
375
376 x_item_tbl =>l_mat_item_tbl ,
377 x_return_status =>l_x_return_status
378 );
379
380 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
381 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
382 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Checking Items for Material Transactions',
383 'Return Status is: '|| l_x_return_status);
384 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
385 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Checking Items for Material Transactions',
386 'Number of Items: '||l_mat_item_tbl.COUNT );
387 END IF;
388
389 -- Merge the two Item table to get the item table
390 -- that the same task, item and UOM combination appear only once
391
392 AHL_VWP_RULES_PVT.Merge_for_Unique_Items
393 (p_item_tbl1 =>l_res_item_tbl ,
394 p_item_tbl2 =>l_mat_item_tbl ,
395
396 x_item_tbl =>l_x_item_tbl
397 );
398
399
400 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
401 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
402 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Merge Items',
403 'Number of Items: '||l_x_item_tbl.COUNT );
404 END IF;
405
406 -- Calculate the price for the items required by the WIP job
407
408 IF l_x_item_tbl.count > 0 THEN
409
410 Calculate_Total_Price
411 (p_item_tbl =>l_x_item_tbl,
412 p_price_list_id =>p_price_list_id ,
413 p_customer_id =>p_customer_id ,
414 p_currency_code =>p_currency_code ,
415
416 x_total_price =>l_total_price ,
417 x_return_status =>l_x_return_status
418 );
419
420 END IF;
421
422 -- Assign return status and Item without Price table
423 x_return_status:=l_x_return_status;
424 x_actual_price := l_total_price;
425
426 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
427 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
428 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
429 'Return Status is: '|| x_return_status);
430 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
431 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
432 'Total Price: '|| x_actual_price);
433 END IF;
434
435 END Get_Job_Actual_Price;
436
437 --------------------------------------------------------------------
438 -- Procedure name : Get_Item_Price
439 -- Purpose : To return price for a given item.
440 --------------------------------------------------------------------
441 PROCEDURE Get_Item_Price
442 (p_item_id IN NUMBER,
443 p_price_list_id IN NUMBER,
444 p_customer_id IN NUMBER,
445 p_duration IN NUMBER := 1,
446 p_currency_code IN VARCHAR2,
447 p_effective_date IN DATE,
448 p_UOM_code IN VARCHAR2,
449
450 x_item_price OUT NOCOPY NUMBER,
451 x_return_status OUT NOCOPY VARCHAR2
452 )
453 IS
454
455 -- Define local variables
456
457 l_p_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
458 l_p_qual_tbl QP_PREQ_GRP.QUAL_TBL_TYPE;
459 l_p_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
460 l_p_LINE_DETAIL_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
461 l_p_LINE_DETAIL_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
462 l_p_LINE_DETAIL_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
463 l_p_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
464 l_p_control_rec QP_PREQ_GRP.CONTROL_RECORD_TYPE;
465 l_x_line_tbl QP_PREQ_GRP.LINE_TBL_TYPE;
466 l_x_line_qual QP_PREQ_GRP.QUAL_TBL_TYPE;
467 l_x_line_attr_tbl QP_PREQ_GRP.LINE_ATTR_TBL_TYPE;
468 l_x_line_detail_tbl QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
469 l_x_line_detail_qual_tbl QP_PREQ_GRP.LINE_DETAIL_QUAL_TBL_TYPE;
470 l_x_line_detail_attr_tbl QP_PREQ_GRP.LINE_DETAIL_ATTR_TBL_TYPE;
471 l_x_related_lines_tbl QP_PREQ_GRP.RELATED_LINES_TBL_TYPE;
472 l_x_return_status VARCHAR2(240);
473 l_x_return_status_text VARCHAR2(240);
474 l_qual_rec QP_PREQ_GRP.QUAL_REC_TYPE;
475 l_line_attr_rec QP_PREQ_GRP.LINE_ATTR_REC_TYPE;
476 l_line_rec QP_PREQ_GRP.LINE_REC_TYPE;
477
478 l_duration NUMBER :=1; -- duration is default to 1
479 i NUMBER :=0; -- loop index
480 l_api_name CONSTANT VARCHAR2(30) := 'Get_Item_Price';
481
482 BEGIN
483
484 -- Standard Start of API savepoint
485 SAVEPOINT Get_Item_Price;
486
487 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
488 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
489 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
490 'At the start of the procedure');
491 END IF;
492
493 --Initialize API return status to success
494 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
495
496 -- If duration passed in is null then default to 1
497 IF p_duration is not NULL AND p_duration <> FND_API.G_MISS_NUM THEN
498 l_duration := p_duration;
499 END IF;
500
501 -- Passing Information to the Pricing Engine
502
503 -- Setting up the control record variables
504
505 l_p_control_rec.pricing_event := 'LINE';
506 l_p_control_rec.calculate_flag := 'Y';
507 l_p_control_rec.simulation_flag := 'N';
508 l_p_control_rec.use_multi_currency := 'Y';
509 --Jerry added on 09/27/05 for MOAC change
510 l_p_control_rec.org_id := MO_GLOBAL.get_current_org_id;
511
512 -- Request Line (Order Line) Information
513 l_line_rec.request_type_code :='AHL';
514 l_line_rec.line_id :=p_item_id; -- Order Line Id. This can be any thing for this script
515 l_line_rec.line_Index :='1'; -- Request Line Index
516 l_line_rec.line_type_code := 'LINE'; -- LINE or ORDER(Summary Line)
517 l_line_rec.pricing_effective_date := p_effective_date; -- Pricing as of what date ?
518 l_line_rec.line_quantity := 1; -- Ordered Quantity
519 l_line_rec.line_uom_code := p_UOM_code; -- Ordered UOM Code
520 l_line_rec.currency_code := p_currency_code; -- Currency Code
521 l_line_rec.price_flag := 'Y'; -- Price Flag can have 'Y' , 'N'(No pricing) , 'P'(Phase)
522 l_p_line_tbl(1) := l_line_rec;
523
524 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
525 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
526 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Request Line',
527 'Line ID: '||l_p_line_tbl(1).line_id );
528 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
529 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Request Line',
530 'Effective Dates: '||l_p_line_tbl(1).pricing_effective_date );
531 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
532 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Request Line',
533 'UOM Code: '||l_p_line_tbl(1).line_uom_code );
534 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
535 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Request Line',
536 'Currency: '||l_p_line_tbl(1).currency_code );
537 END IF;
538 -- Pricing Attributes Passed In
539 --- Item
540 l_line_attr_rec.LINE_INDEX := 1; -- Attributes for the above line. Attributes are attached with the line index
541 l_line_attr_rec.PRICING_CONTEXT :='ITEM';
542 l_line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE1';
543 l_line_attr_rec.PRICING_ATTR_VALUE_FROM := p_item_id; -- Inventory Item Id
544 l_line_attr_rec.VALIDATED_FLAG :='N';
545 l_p_line_attr_tbl(1):= l_line_attr_rec;
546
547 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
548 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
549 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Pricing Attributes',
550 'Item ID: '||l_p_line_attr_tbl(1).PRICING_ATTR_VALUE_FROM );
551 END IF;
552
553 --- Duration (pricing attribute required by formula)
554 -- If no formula is associated for the given item then unit price will be derived
555
556 l_line_attr_rec.LINE_INDEX := 1; -- Attributes for the above line. Attributes are attached with the line index
557 l_line_attr_rec.PRICING_CONTEXT :='AHL_PRICING'; -- CMRO set up
558 l_line_attr_rec.PRICING_ATTRIBUTE :='PRICING_ATTRIBUTE1';
559 l_line_attr_rec.PRICING_ATTR_VALUE_FROM := l_duration; -- resource duration
560 l_line_attr_rec.VALIDATED_FLAG :='N';
561 l_p_line_attr_tbl(2):= l_line_attr_rec;
562
563 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
564 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
565 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Pricing Attributes',
566 'Duration: '||l_p_line_attr_tbl(2).PRICING_ATTR_VALUE_FROM );
567 END IF;
568
569 -- Qualifiers Passed In
570 --- Price List
571 l_qual_rec.LINE_INDEX := 1; -- Attributes for the above line. Attributes are attached with the line index
572 l_qual_rec.QUALIFIER_CONTEXT :='MODLIST';
573 l_qual_rec.QUALIFIER_ATTRIBUTE :='QUALIFIER_ATTRIBUTE4';
574 l_qual_rec.QUALIFIER_ATTR_VALUE_FROM :=p_price_list_id; -- Price List Id
575 l_qual_rec.COMPARISON_OPERATOR_CODE := '=';
576 l_qual_rec.VALIDATED_FLAG :='N';
577 l_p_qual_tbl(1):= l_qual_rec;
578
579 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
580 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
581 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Qualifiers',
582 'Price List ID: '||l_p_qual_tbl(1).QUALIFIER_ATTR_VALUE_FROM );
583 END IF;
584 --- Customer
585 l_qual_rec.LINE_INDEX := 1; -- Attributes for the above line. Attributes are attached with the line index
586 l_qual_rec.QUALIFIER_CONTEXT :='CUSTOMER';
587 l_qual_rec.QUALIFIER_ATTRIBUTE :='QUALIFIER_ATTRIBUTE16';
588 l_qual_rec.QUALIFIER_ATTR_VALUE_FROM :=p_customer_id; -- Customer Id
589 l_qual_rec.COMPARISON_OPERATOR_CODE := '=';
590 l_qual_rec.VALIDATED_FLAG :='N';
591 l_p_qual_tbl(2):= l_qual_rec;
592
593 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
594 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
595 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Qualifiers',
596 'Customer ID: '||l_p_qual_tbl(2).QUALIFIER_ATTR_VALUE_FROM );
597 END IF;
598 -- Actual Call to the Pricing Engine
599 QP_PREQ_PUB.PRICE_REQUEST
600 (l_p_line_tbl,
601 l_p_qual_tbl,
602 l_p_line_attr_tbl,
603 l_p_line_detail_tbl,
604 l_p_line_detail_qual_tbl,
605 l_p_line_detail_attr_tbl,
606 l_p_related_lines_tbl,
607 l_p_control_rec,
608 l_x_line_tbl,
609 l_x_line_qual,
610 l_x_line_attr_tbl,
611 l_x_line_detail_tbl,
612 l_x_line_detail_qual_tbl,
613 l_x_line_detail_attr_tbl,
614 l_x_related_lines_tbl,
615 l_x_return_status,
616 l_x_return_status_text);
617
618 -- Return Status Information ..
619 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
620 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
621 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Pricing API call',
622 'Return Status: '||l_x_return_status );
623 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
624 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Pricing API call',
625 'Return Status text: '||l_x_return_status_text );
626 END IF;
627
628
629 IF (l_x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
630 FND_MESSAGE.Set_Name('AHL','AHL_VWP_PRICE_API_ERROR');
631 FND_MESSAGE.Set_Token('ERROR', l_x_return_status_text);
632 FND_MSG_PUB.Add;
633 RAISE FND_API.G_EXC_ERROR;
634 END IF;
635
636
637 -- Unit Price returned
638
639 I := l_x_line_tbl.FIRST;
640 IF I IS NOT NULL THEN
641 LOOP
642
643 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
644 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
645 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Pricing API call',
646 'Line Index: '||l_x_line_tbl(I).line_index );
647 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
648 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Pricing API call',
649 'Unit Price: '||l_x_line_tbl(I).unit_price );
650 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
651 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Pricing API call',
652 'Adjusted Unit Price: '||l_x_line_tbl(I).adjusted_unit_price );
653 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
654 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Pricing API call',
655 'Line Status Code: '||l_x_line_tbl(I).status_code );
656 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
657 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After Pricing API call',
658 'Line Status Text: '||l_x_line_tbl(I).status_text );
659 END IF;
660
661 x_item_price := l_x_line_tbl(I).adjusted_unit_price;
662
663 EXIT WHEN I = l_x_line_tbl.LAST;
664 I := l_x_line_tbl.NEXT(I);
665 END LOOP;
666 END IF;
667
668 -- Assign return status
669 x_return_status:=l_x_return_status;
670
671 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
672 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
673 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
674 'Return Status is: '|| x_return_status);
675 END IF;
676
677 EXCEPTION
678 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
679 ROLLBACK TO Get_Item_Price;
680 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
681
682 WHEN FND_API.G_EXC_ERROR THEN
683 ROLLBACK TO Get_Item_Price;
684 X_return_status := FND_API.G_RET_STS_ERROR;
685
686 WHEN OTHERS THEN
687 ROLLBACK TO Get_Item_Price;
688 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
689
690 END Get_Item_Price;
691
692 --------------------------------------------------------------------
693 -- Procedure name : Get_Items_without_Price
694 -- Purpose : To return items which are not set up in price list.
695 --------------------------------------------------------------------
696 PROCEDURE Get_Items_without_Price
697 (p_item_tbl IN AHL_VWP_RULES_PVT.Item_Tbl_Type,
698 p_price_list_id IN NUMBER,
699 p_customer_id IN NUMBER,
700 p_currency_code IN VARCHAR2,
701
702 x_item_tbl OUT NOCOPY AHL_VWP_RULES_PVT.Item_Tbl_Type,
703 x_return_status OUT NOCOPY VARCHAR2
704 )
705 IS
706
707 -- Define local variables
708 l_x_return_status VARCHAR2(240);
709 l_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
710 l_x_item_price NUMBER := 0;
711
712 i NUMBER :=0; -- loop index
713 j NUMBER :=0; -- loop index
714
715 l_api_name CONSTANT VARCHAR2(30) := 'Get_Items_without_Price';
716
717 BEGIN
718
719 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
720 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
721 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
722 'At the start of the procedure');
723 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
724 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
725 'Number of Items to check: '|| p_item_tbl.count);
726 END IF;
727
728 --Initialize API return status to success
729 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
730
731 -- For each item in p_item_tbl, check for price
732 -- If price is not set up in price list
733 -- Add this item to output item table
734
735 I := p_item_tbl.FIRST;
736 IF I IS NOT NULL THEN
737 LOOP
738
739 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
740 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
741 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': In Loop',
742 'Loop Index: '||I );
743 END IF;
744
745 -- Check the price of the item
746 Get_Item_Price
747 (p_item_id =>p_item_tbl(I).Item_Id ,
748 p_price_list_id =>p_price_list_id ,
749 p_customer_id =>p_customer_id ,
750 p_duration =>p_item_tbl(I).Duration ,
751 p_currency_code =>p_currency_code ,
752 p_effective_date =>p_item_tbl(I).Effective_Date ,
753 p_UOM_code =>p_item_tbl(I).UOM_Code ,
754
755 x_item_price =>l_x_item_price ,
756 x_return_status =>l_x_return_status
757 );
758
759 -- Add the item to output table if returned item price is null
760 IF (l_x_item_price IS NULL) THEN
761
762 l_item_tbl(J) := p_item_tbl(I);
763
764 J:= J+1;
765 END IF;
766
767 EXIT WHEN I = p_item_tbl.LAST;
768 I := p_item_tbl.NEXT(I);
769 END LOOP;
770 END IF;
771
772 -- Assign return status and Item without Price table
773 x_return_status:=l_x_return_status;
774 x_item_tbl:= l_item_tbl;
775
776 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
777 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
778 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
779 'Return Status is: '|| x_return_status);
780 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
781 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
782 'Number of Items without Price: '|| x_item_tbl.count);
783 END IF;
784
785 END Get_Items_without_Price;
786
787 --------------------------------------------------------------------
788 -- Procedure name : Check_Item_for_Prod_Mat_Req
789 -- Purpose : To return items required for Material requirements of a given task.
790 -- when job is not in draft status
791 --------------------------------------------------------------------
792 PROCEDURE Check_Item_for_Prod_Mat_Req
793 (p_visit_task_id IN NUMBER,
794
795 x_item_tbl OUT NOCOPY AHL_VWP_RULES_PVT.Item_Tbl_Type,
796 x_return_status OUT NOCOPY VARCHAR2
797 )
798 IS
799 -- Define local Cursors
800
801 -- Get job material requirements
802
803 CURSOR get_prod_materials_csr (c_visit_task_id NUMBER)
804 IS
805 /*
806 SELECT MAT.inventory_item_id,
807 MAT.requested_date,
808 SUM(NVL(MAT.requested_quantity,0)) quantity,
809 MAT.uom_code
810 FROM AHL_SCHEDULE_MATERIALS_V MAT
811 WHERE MAT.visit_task_id =c_visit_task_id
812 AND MAT.JOB_STATUS_CODE <> 22 -- 'deleted' status
813 AND MAT.JOB_STATUS_CODE <> 7 -- 'cancelled' status
814 AND MAT.inventory_item_id is not null
815 AND MAT.uom_code is not null
816 GROUP BY MAT.inventory_item_id,
817 MAT.requested_date,
818 MAT.uom_code;
819 */
820 -- AnRaj: Changed query for fixing the perf issue #2, bug:4919487
821 -- AnRaj: 5532023, added further WHERE clauses which were missed out during fixing 4919487
822 SELECT asm.inventory_item_id,
823 wiro.DATE_REQUIRED requested_date,
824 sum(nvl(wiro.REQUIRED_QUANTITY,0)) quantity,
825 mtl.primary_uom_code uom_code
826 FROM ahl_workorders awo,
827 ahl_schedule_materials asm,
828 wip_requirement_operations wiro,
829 mtl_system_items_b mtl
830 WHERE awo.visit_task_id = c_visit_task_id
831 AND awo.visit_task_id = asm.visit_task_id
832 AND asm.inventory_item_id = mtl.inventory_item_id
833 AND asm.organization_id = mtl.organization_id
834 AND awo.wip_entity_id = wiro.wip_entity_id
835 AND awo.STATUS_CODE NOT IN (22,7)
836 AND ASM.inventory_item_id is not null
837 AND mtl.primary_uom_code is not null
838 AND ASM.OPERATION_SEQUENCE = WIRO.OPERATION_SEQ_NUM
839 AND ASM.INVENTORY_ITEM_ID = WIRO.INVENTORY_ITEM_ID
840 AND ASM.ORGANIZATION_ID = WIRO.ORGANIZATION_ID
841 GROUP BY asm.inventory_item_id,
842 wiro.DATE_REQUIRED,
843 mtl.primary_uom_code;
844 l_prod_material_req_rec get_prod_materials_csr%ROWTYPE;
845
846
847 -- Define local variables
848 l_x_return_status VARCHAR2(240);
849 l_x_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
850
851 i NUMBER :=0; -- loop index
852
853 l_api_name CONSTANT VARCHAR2(30) := 'Check_Item_for_Prod_Mat_Req';
854
855 BEGIN
856
857 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
858 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
859 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
860 'At the start of the procedure');
861 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
862 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
863 'Visit Task ID: '|| p_visit_task_id);
864
865 END IF;
866
867 --Initialize API return status to success
868 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
869
870 -- Get all material requirements for the job
871
872 Open get_prod_materials_csr(p_visit_task_id);
873
874 LOOP
875 FETCH get_prod_materials_csr INTO l_prod_material_req_rec;
876 EXIT WHEN get_prod_materials_csr%NOTFOUND;
877
878 l_x_item_tbl(I).item_id :=l_prod_material_req_rec.inventory_item_id;
879 l_x_item_tbl(I).quantity :=l_prod_material_req_rec.quantity;
880 l_x_item_tbl(I).UOM_code :=l_prod_material_req_rec.uom_code;
881 l_x_item_tbl(I).effective_date :=l_prod_material_req_rec.requested_date;
882 l_x_item_tbl(I).Visit_Task_Id :=p_visit_task_id;
883
884 I := I + 1;
885
886 END LOOP;
887
888 CLOSE get_prod_materials_csr;
889
890
891 -- Assign return status and Item without Price table
892 x_return_status:=l_x_return_status;
893 x_item_tbl:= l_x_item_tbl;
894
895 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
896 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
897 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
898 'Return Status is: '|| x_return_status);
899 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
900 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
901 'Number of Items for material requirements: '|| x_item_tbl.count);
902 END IF;
903
904 END Check_Item_for_Prod_Mat_Req;
905
906 --------------------------------------------------------------------
907 -- Procedure name : Check_Item_for_Prod_Res_Req
908 -- Purpose : To return items required for Resource requirements of a given task
909 -- when job is not in draft status
910 --------------------------------------------------------------------
911 PROCEDURE Check_Item_for_Prod_Res_Req
912 (p_visit_task_id IN NUMBER,
913
914 x_item_tbl OUT NOCOPY AHL_VWP_RULES_PVT.Item_Tbl_Type,
915 x_return_status OUT NOCOPY VARCHAR2
916 )
917 IS
918 -- Define local Cursors
919
920 -- Get job resource requirements
921
922 CURSOR get_prod_res_req_csr (c_visit_task_id NUMBER)
923 IS
924 SELECT sum(NVL(AR.TOTAL_REQUIRED,0)) duration,
925 -- sum(NVL(AR.quantity,0)) quantity,
926 -- sum(NVL(AR.TOTAL_REQUIRED,0)) total_quantity,
927 BR.BILLABLE_ITEM_ID,
928 AR.REQUIRED_START_DATE,
929 MSIV.concatenated_segments item_name,
930 MSIV.primary_uom_code uom_code
931 FROM AHL_PP_REQUIREMENT_V AR,
932 MTL_SYSTEM_ITEMS_VL MSIV,
933 AHL_VISITS_B V,
934 AHL_VISIT_TASKS_B VT,
935 BOM_RESOURCES BR
936 WHERE BR.resource_id=AR.resource_id
937 AND BR.BILLABLE_ITEM_ID is not null
938 AND AR.visit_task_id = c_visit_task_id
939 AND V.visit_id = VT.visit_id
940 AND VT.visit_task_id = c_visit_task_id
941 AND MSIV.inventory_item_id = BR.BILLABLE_ITEM_ID
942 AND MSIV.organization_id = V.organization_id
943 AND AR.JOB_STATUS_CODE <> 22 -- 'deleted' status
944 AND AR.JOB_STATUS_CODE <> 7 -- 'cancelled' status
945 Group By BR.BILLABLE_ITEM_ID,
946 MSIV.concatenated_segments,
947 MSIV.primary_uom_code,
948 AR.REQUIRED_START_DATE;
949
950 l_prod_res_req_rec get_prod_res_req_csr%ROWTYPE;
951
952
953 -- Define local variables
954 l_x_return_status VARCHAR2(240);
955 l_x_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
956
957 l_x_time_type_flag VARCHAR2(1);
958 i NUMBER :=0; -- loop index
959
960 l_api_name CONSTANT VARCHAR2(30) := 'Check_Item_for_Prod_Res_Req';
961
962 BEGIN
963
964 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
965 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
966 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
967 'At the start of the procedure');
968 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
969 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
970 'Visit Task ID: '|| p_visit_task_id);
971
972 END IF;
973
974 --Initialize API return status to success
975 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
976
977 -- Get all material requirements for the job
978
979 Open get_prod_res_req_csr(p_visit_task_id);
980
981 LOOP
982 FETCH get_prod_res_req_csr INTO l_prod_res_req_rec;
983 EXIT WHEN get_prod_res_req_csr%NOTFOUND;
984
985 -- Verify to see if the Item UOM is of Time type
986 Check_UOM_Class
987 (p_UOM_code =>l_prod_res_req_rec.uom_code,
988 x_time_type_flag =>l_x_time_type_flag
989 );
990
991 IF l_x_time_type_flag <>'Y' THEN
992
993 Fnd_Message.SET_NAME('AHL','AHL_VWP_WRONG_ITEM_UOM');
994 Fnd_Message.SET_TOKEN('INV_ITEM',l_prod_res_req_rec.item_name);
995 Fnd_Msg_Pub.ADD;
996 CLOSE get_prod_res_req_csr;
997 RAISE Fnd_Api.G_EXC_ERROR;
998
999 END IF;
1000
1001 l_x_item_tbl(I).item_id :=l_prod_res_req_rec.BILLABLE_ITEM_ID;
1002 l_x_item_tbl(I).quantity :=1;
1003 l_x_item_tbl(I).duration :=l_prod_res_req_rec.duration;
1004 l_x_item_tbl(I).UOM_code :=l_prod_res_req_rec.uom_code;
1005 l_x_item_tbl(I).effective_date :=l_prod_res_req_rec.REQUIRED_START_DATE;
1006 l_x_item_tbl(I).Visit_Task_Id :=p_visit_task_id;
1007
1008 I := I + 1;
1009
1010 END LOOP;
1011
1012 CLOSE get_prod_res_req_csr;
1013
1014 -- Assign return status and Item without Price table
1015 x_return_status:=l_x_return_status;
1016 x_item_tbl:= l_x_item_tbl;
1017
1018 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1019 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1020 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1021 'Return Status is: '|| x_return_status);
1022 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1023 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1024 'Number of Items for resource requirements: '|| x_item_tbl.count);
1025 END IF;
1026
1027 END Check_Item_for_Prod_Res_Req;
1028
1029
1030 --------------------------------------------------------------------
1031 -- Procedure name : Check_Item_for_Rt_Mat_Req
1032 -- Purpose : To return items required for Material requirements of a given task.
1033 -- when job is in draft status
1034 --------------------------------------------------------------------
1035 PROCEDURE Check_Item_for_Rt_Mat_Req
1036 (p_visit_task_id IN NUMBER,
1037 p_route_id IN NUMBER,
1038 p_effective_date IN DATE,
1039
1040 x_item_tbl OUT NOCOPY AHL_VWP_RULES_PVT.Item_Tbl_Type,
1041 x_return_status OUT NOCOPY VARCHAR2
1042 )
1043 IS
1044 -- Define local Cursors
1045 -- Get route material requirements
1046
1047 CURSOR get_rt_materials_csr (c_route_id NUMBER, c_visit_task_id NUMBER)
1048 IS
1049 SELECT MAT.rt_oper_material_id,
1050 MAT.inventory_item_id,
1051 MAT.quantity,
1052 MSIV.primary_uom_code uom_code
1053 -- MAT.uom_code
1054 FROM AHL_RT_OPER_MATERIALS MAT,
1055 AHL_VISITS_B V,
1056 AHL_VISIT_TASKS_B VT,
1057 MTL_SYSTEM_ITEMS_VL MSIV
1058 WHERE MAT.association_type_code='ROUTE'
1059 AND MSIV.organization_id = V.organization_id
1060 AND V.visit_id = VT.visit_id
1061 AND VT.visit_task_id = c_visit_task_id
1062 AND MAT.inventory_item_id = MSIV.inventory_item_id
1063 AND MAT.object_id=c_route_id;
1064
1065 l_rt_material_req_rec get_rt_materials_csr%ROWTYPE;
1066
1067 -- Get operation material requirements
1068
1069 CURSOR get_oper_materials_csr (c_route_id NUMBER, c_visit_task_id NUMBER)
1070 IS
1071 SELECT MAT.inventory_item_id,
1072 sum(NVL(MAT.quantity,0)) quantity,
1073 MSIV.primary_uom_code uom_code
1074 -- MAT.uom_code
1075 FROM MTL_SYSTEM_ITEMS_VL MSIV,
1076 AHL_VISITS_B V,
1077 AHL_VISIT_TASKS_B VT,
1078 AHL_RT_OPER_MATERIALS MAT
1079 WHERE MAT.association_type_code='OPERATION'
1080 -- AND MAT.inventory_item_id is not null
1081 AND MSIV.organization_id = V.organization_id
1082 AND V.visit_id = VT.visit_id
1083 AND VT.visit_task_id = c_visit_task_id
1084 AND MSIV.inventory_item_id = MAT.inventory_item_id
1085 AND MAT.object_id in
1086 ( SELECT RO.operation_id
1087 FROM AHL_OPERATIONS_VL OP,
1088 AHL_ROUTE_OPERATIONS RO
1089 WHERE OP.operation_id=RO.operation_id
1090 AND OP.revision_status_code='COMPLETE'
1091 AND RO.route_id=c_route_id
1092 AND OP.revision_number IN
1093 ( SELECT MAX(revision_number)
1094 FROM AHL_OPERATIONS_B_KFV
1095 WHERE concatenated_segments=OP.concatenated_segments
1096 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
1097 TRUNC(NVL(end_date_active,SYSDATE+1))
1098 )
1099 )
1100 Group By MAT.inventory_item_id,
1101 MSIV.primary_uom_code;
1102 -- MAT.uom_code;
1103
1104 l_oper_material_req_rec get_oper_materials_csr%ROWTYPE;
1105
1106 -- Define local variables
1107 l_x_return_status VARCHAR2(240);
1108 l_x_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
1109
1110 l_route_mat_found BOOLEAN := FALSE;
1111
1112 i NUMBER :=0; -- loop index
1113
1114 l_api_name CONSTANT VARCHAR2(30) := 'Check_Item_for_Rt_Mat_Req';
1115
1116 BEGIN
1117
1118 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1119 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1120 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1121 'At the start of the procedure');
1122 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1123 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1124 'Visit Task ID: '|| p_visit_task_id);
1125 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1126 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1127 'Route ID: '|| p_route_id);
1128 END IF;
1129
1130 --Initialize API return status to success
1131 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1132
1133 -- Check to see if material requirements are defined at route level
1134
1135 Open get_rt_materials_csr(p_route_id,p_visit_task_id);
1136
1137 LOOP
1138 FETCH get_rt_materials_csr INTO l_rt_material_req_rec;
1139 EXIT WHEN get_rt_materials_csr%NOTFOUND;
1140
1141 -- Atleast One Material Requirement defined for the Route
1142 l_route_mat_found := TRUE;
1143
1144 -- The Material Requirement is based on an Item
1145 -- Ignore if requirement is based on item group
1146 -- Do not consider alternate items
1147
1148 IF ( l_rt_material_req_rec.inventory_item_id IS NOT NULL ) THEN
1149
1150 l_x_item_tbl(I).item_id :=l_rt_material_req_rec.inventory_item_id;
1151 l_x_item_tbl(I).quantity :=l_rt_material_req_rec.quantity;
1152 l_x_item_tbl(I).UOM_code :=l_rt_material_req_rec.uom_code;
1153 l_x_item_tbl(I).effective_date :=p_effective_date;
1154 l_x_item_tbl(I).Visit_Task_Id :=p_visit_task_id;
1155
1156 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1157 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1158 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': In Loop',
1159 'Item Quantity: '||I||'...'||l_x_item_tbl(I).quantity );
1160 END IF;
1161
1162 I := I + 1;
1163
1164 End IF;
1165
1166 END LOOP;
1167
1168 CLOSE get_rt_materials_csr;
1169
1170 -- If material requirements are not defined at route level
1171 -- Then sum up the requirements for associated operations
1172 IF ( l_route_mat_found = FALSE ) THEN
1173
1174 Open get_oper_materials_csr(p_route_id,p_visit_task_id);
1175
1176 LOOP
1177 FETCH get_oper_materials_csr INTO l_oper_material_req_rec;
1178 EXIT WHEN get_oper_materials_csr%NOTFOUND;
1179
1180
1181 -- The Material Requirement is based on an Item
1182 -- Ignore if requirement is based on item group
1183 -- Do not consider alternate items
1184
1185 IF ( l_oper_material_req_rec.inventory_item_id IS NOT NULL ) THEN
1186
1187 l_x_item_tbl(I).item_id :=l_oper_material_req_rec.inventory_item_id;
1188 l_x_item_tbl(I).quantity :=l_oper_material_req_rec.quantity;
1189 l_x_item_tbl(I).UOM_code :=l_oper_material_req_rec.uom_code;
1190 l_x_item_tbl(I).effective_date :=p_effective_date;
1191 l_x_item_tbl(I).Visit_Task_Id :=p_visit_task_id;
1192
1193 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1194 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1195 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': In Loop',
1196 'Item Quantity: '||I||'...'||l_x_item_tbl(I).quantity );
1197 END IF;
1198
1199 I := I + 1;
1200
1201 End IF;
1202
1203 END LOOP;
1204
1205 CLOSE get_oper_materials_csr;
1206
1207 END IF;
1208
1209 -- Assign return status and Item without Price table
1210 x_return_status:=l_x_return_status;
1211 x_item_tbl:= l_x_item_tbl;
1212
1213 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1214 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1215 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1216 'Return Status is: '|| x_return_status);
1217 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1218 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1219 'Number of Items for material requirements: '|| x_item_tbl.count);
1220 END IF;
1221
1222
1223 END Check_Item_for_Rt_Mat_Req;
1224
1225 --------------------------------------------------------------------
1226 -- Procedure name : Check_Item_for_Rt_Res_Req
1227 -- Purpose : To return items required for Resource requirements of a given task
1228 -- when job is in draft status
1229 --------------------------------------------------------------------
1230 PROCEDURE Check_Item_for_Rt_Res_Req
1231 (p_visit_task_id IN NUMBER,
1232 p_route_id IN NUMBER,
1233 p_organization_id IN NUMBER,
1234 p_effective_date IN DATE,
1235
1236 x_item_tbl OUT NOCOPY AHL_VWP_RULES_PVT.Item_Tbl_Type,
1237 x_return_status OUT NOCOPY VARCHAR2
1238 )
1239 IS
1240 -- Define local Cursors
1241 -- For checking if Resource Requirements are defined for Route
1242 CURSOR check_rt_resources_csr(c_route_id NUMBER)
1243 IS
1244 SELECT AR.rt_oper_resource_id
1245 FROM AHL_RT_OPER_RESOURCES AR
1246 WHERE AR.association_type_code='ROUTE'
1247 AND AR.object_id=c_route_id;
1248
1249 -- Bug # : 5532390, regression issues caused by fix for bug number 5258334
1250 --
1251 -- For Getting the BOM Resource for the ASO Resource and the Visit's
1252 -- Organization. Merge rows with same item defined.
1253 CURSOR get_rt_resources_csr(c_route_id NUMBER,
1254 c_org_id NUMBER)
1255 IS
1256 SELECT sum(NVL(AR.duration,0)* NVL(AR.quantity,0)) duration,
1257 BR.BILLABLE_ITEM_ID,
1258 MSIV.concatenated_segments item_name,
1259 MSIV.primary_uom_code uom_code
1260 FROM AHL_RT_OPER_RESOURCES AR,
1261 BOM_RESOURCES BR,
1262 MTL_SYSTEM_ITEMS_VL MSIV,
1263 AHL_RESOURCE_MAPPINGS MAP
1264 WHERE AR.association_type_code='ROUTE'
1265 AND BR.resource_id=MAP.bom_resource_id
1266 AND AR.object_id=c_route_id
1267 AND BR.organization_id=c_org_id
1268 AND MAP.aso_resource_id=AR.aso_resource_id
1269 AND BR.BILLABLE_ITEM_ID is not null
1270 AND MSIV.organization_id = c_org_id
1271 AND MSIV.inventory_item_id = BR.BILLABLE_ITEM_ID
1272 Group By BR.BILLABLE_ITEM_ID,
1273 MSIV.concatenated_segments,
1274 MSIV.primary_uom_code;
1275
1276 l_rt_resource_rec get_rt_resources_csr%ROWTYPE;
1277
1278
1279 -- Get operation material requirements
1280 -- Bug # : 5532390, regression issues caused by fix for bug number 5258334
1281 CURSOR get_oper_resources_csr (c_route_id NUMBER,c_org_id NUMBER)
1282 IS
1283 /* SELECT sum(NVL(AR.duration,0)* NVL(AR.quantity,0)) duration,
1284 MSIV.primary_uom_code uom_code,
1285 MSIV.concatenated_segments item_name,
1286 BR.BILLABLE_ITEM_ID
1287 FROM AHL_RT_OPER_RESOURCES AR,
1288 BOM_RESOURCES BR,
1289 MTL_SYSTEM_ITEMS_VL MSIV,
1290 AHL_RESOURCE_MAPPINGS MAP
1291 WHERE AR.association_type_code='OPERATION'
1292 AND BR.resource_id=MAP.bom_resource_id
1293 AND BR.organization_id=c_org_id
1294 AND MAP.aso_resource_id=AR.aso_resource_id
1295 AND BR.BILLABLE_ITEM_ID is not null
1296 AND MSIV.inventory_item_id = BR.BILLABLE_ITEM_ID
1297 AND MSIV.organization_id = c_org_id
1298 AND AR.object_id in
1299 ( SELECT RO.operation_id
1300 FROM AHL_OPERATIONS_VL OP,
1301 AHL_ROUTE_OPERATIONS RO
1302 WHERE OP.operation_id=RO.operation_id
1303 AND OP.revision_status_code='COMPLETE'
1304 AND RO.route_id=c_route_id
1305 AND OP.revision_number IN
1306 ( SELECT MAX(revision_number)
1307 FROM AHL_OPERATIONS_B_KFV
1308 WHERE concatenated_segments=OP.concatenated_segments
1309 AND TRUNC(SYSDATE) BETWEEN TRUNC(start_date_active) AND
1310 TRUNC(NVL(end_date_active,SYSDATE+1))
1311 )
1312 )
1313 Group By BR.BILLABLE_ITEM_ID,
1314 MSIV.concatenated_segments,
1315 MSIV.primary_uom_code;
1316 */
1317 select sum(nvl(ar.duration,0)* nvl(ar.quantity,0)) duration,
1318 msiv.primary_uom_code uom_code,
1319 msiv.concatenated_segments item_name,
1320 br.billable_item_id
1321 from ahl_rt_oper_resources ar,
1322 bom_resources br,
1323 mtl_system_items_kfv msiv,
1324 ahl_resource_mappings map
1325 where ar.association_type_code='OPERATION'
1326 and br.resource_id=map.bom_resource_id
1327 and br.organization_id=c_org_id
1328 and map.aso_resource_id=ar.aso_resource_id
1329 and br.billable_item_id is not null
1330 and msiv.inventory_item_id = br.billable_item_id
1331 and msiv.organization_id = c_org_id
1332 and ar.object_id in
1333 ( select ro.operation_id
1334 from ahl_operations_b_kfv op,
1335 ahl_route_operations ro
1336 where op.operation_id=ro.operation_id
1337 and op.revision_status_code='COMPLETE'
1338 and ro.route_id=c_route_id
1339 and op.revision_number in
1340 ( select max(revision_number)
1341 from ahl_operations_b_kfv
1342 where concatenated_segments=op.concatenated_segments
1343 and trunc(sysdate) between trunc(start_date_active)
1344 and trunc(nvl(end_date_active,sysdate+1))
1345 )
1346 )
1347 group by br.billable_item_id,
1348 msiv.concatenated_segments,
1349 msiv.primary_uom_code;
1350
1351 l_oper_resource_req_rec get_oper_resources_csr%ROWTYPE;
1352
1353 -- Define local variables
1354 l_x_return_status VARCHAR2(240);
1355 l_x_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
1356 l_x_time_type_flag VARCHAR2(1);
1357
1358 l_rt_resource_id NUMBER;
1359
1360 i NUMBER :=0; -- loop index
1361
1362 l_api_name CONSTANT VARCHAR2(30) := 'Check_Item_for_Rt_Res_Req';
1363
1364 BEGIN
1365
1366 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1367 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1368 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1369 'At the start of the procedure');
1370 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1371 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1372 'Visit Task ID: '|| p_visit_task_id);
1373 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1374 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1375 'Route ID: '|| p_route_id);
1376 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1377 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1378 'Organization ID: '|| p_organization_id);
1379 END IF;
1380
1381 --Initialize API return status to success
1382 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1383
1384 -- Check to see if resource requirements are defined at route level
1385
1386 Open check_rt_resources_csr(p_route_id);
1387
1388 FETCH check_rt_resources_csr INTO l_rt_resource_id;
1389
1390 IF check_rt_resources_csr%NOTFOUND THEN -- no resource requirements defined for route
1391
1392 Open get_oper_resources_csr(p_route_id,p_organization_id);
1393
1394 LOOP
1395 FETCH get_oper_resources_csr INTO l_oper_resource_req_rec;
1396 EXIT WHEN get_oper_resources_csr%NOTFOUND;
1397
1398 -- The Material Requirement is based on an Item
1399 -- Ignore if requirement is based on item group
1400 -- Do not consider alternate items
1401
1402 IF ( l_oper_resource_req_rec.BILLABLE_ITEM_ID IS NOT NULL ) AND
1403 ( l_oper_resource_req_rec.uom_code IS NOT NULL ) THEN
1404
1405 -- Verify to see if the Item UOM is of Time type
1406 Check_UOM_Class
1407 (p_UOM_code =>l_oper_resource_req_rec.uom_code,
1408 x_time_type_flag =>l_x_time_type_flag
1409 );
1410
1411 IF l_x_time_type_flag <>'Y' THEN
1412
1413 Fnd_Message.SET_NAME('AHL','AHL_VWP_WRONG_ITEM_UOM');
1414 Fnd_Message.SET_TOKEN('INV_ITEM',l_oper_resource_req_rec.item_name);
1415 Fnd_Msg_Pub.ADD;
1416 CLOSE get_oper_resources_csr;
1417 CLOSE check_rt_resources_csr;
1418 RAISE Fnd_Api.G_EXC_ERROR;
1419
1420 END IF;
1421
1422 l_x_item_tbl(I).item_id :=l_oper_resource_req_rec.BILLABLE_ITEM_ID;
1423 l_x_item_tbl(I).quantity :=1;
1424 l_x_item_tbl(I).duration :=l_oper_resource_req_rec.duration;
1425 l_x_item_tbl(I).UOM_code :=l_oper_resource_req_rec.uom_code;
1426 l_x_item_tbl(I).effective_date :=p_effective_date;
1427 l_x_item_tbl(I).Visit_Task_Id :=p_visit_task_id;
1428
1429 I := I + 1;
1430 End IF;
1431
1432 END LOOP;
1433
1434 CLOSE get_oper_resources_csr;
1435
1436 ELSE ---- resource requirements are defined for route
1437
1438 Open get_rt_resources_csr(p_route_id,p_organization_id);
1439
1440 LOOP
1441 FETCH get_rt_resources_csr INTO l_rt_resource_rec;
1442 EXIT WHEN get_rt_resources_csr%NOTFOUND;
1443
1444 -- The Material Requirement is based on an Item
1445 -- Ignore if requirement is based on item group
1446 -- Do not consider alternate items
1447
1448 IF ( l_rt_resource_rec.BILLABLE_ITEM_ID IS NOT NULL ) AND
1449 ( l_rt_resource_rec.uom_code IS NOT NULL ) THEN
1450
1451 -- Verify to see if the Item UOM is of Time type
1452 Check_UOM_Class
1453 (p_UOM_code =>l_rt_resource_rec.uom_code,
1454 x_time_type_flag =>l_x_time_type_flag
1455 );
1456
1457 IF l_x_time_type_flag <>'Y' THEN
1458
1459 Fnd_Message.SET_NAME('AHL','AHL_VWP_WRONG_ITEM_UOM');
1460 Fnd_Message.SET_TOKEN('INV_ITEM',l_rt_resource_rec.item_name);
1461 Fnd_Msg_Pub.ADD;
1462 CLOSE get_rt_resources_csr;
1463 CLOSE check_rt_resources_csr;
1464 RAISE Fnd_Api.G_EXC_ERROR;
1465
1466 END IF;
1467
1468 l_x_item_tbl(I).item_id :=l_rt_resource_rec.BILLABLE_ITEM_ID;
1469 l_x_item_tbl(I).quantity :=1;
1470 l_x_item_tbl(I).duration :=l_rt_resource_rec.duration;
1471 l_x_item_tbl(I).UOM_code :=l_rt_resource_rec.uom_code;
1472 l_x_item_tbl(I).effective_date :=p_effective_date;
1473 l_x_item_tbl(I).Visit_Task_Id :=p_visit_task_id;
1474
1475 I := I + 1;
1476 End IF;
1477
1478 END LOOP;
1479
1480 CLOSE get_rt_resources_csr;
1481
1482 END IF;
1483
1484 CLOSE check_rt_resources_csr;
1485
1486
1487 -- Assign return status and Item without Price table
1488 x_return_status:=l_x_return_status;
1489 x_item_tbl:= l_x_item_tbl;
1490
1491 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1492 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1493 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1494 'Return Status is: '|| x_return_status);
1495 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1496 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1497 'Number of Items for resource requirements: '|| x_item_tbl.count);
1498 END IF;
1499
1500 END Check_Item_for_Rt_Res_Req;
1501
1502
1503 --------------------------------------------------------------------
1504 -- Procedure name : Check_Item_for_Resource_Trans
1505 -- Purpose : To return items required for Resource transactions of a given task.
1506 --------------------------------------------------------------------
1507 PROCEDURE Check_Item_for_Resource_Trans
1508 (p_visit_task_id IN NUMBER,
1509
1510 x_item_tbl OUT NOCOPY AHL_VWP_RULES_PVT.Item_Tbl_Type,
1511 x_return_status OUT NOCOPY VARCHAR2
1512 )
1513 IS
1514 -- Define local Cursors
1515
1516 -- Get job resource requirements
1517
1518 CURSOR get_resource_trans_csr (c_visit_task_id NUMBER)
1519 IS
1520 SELECT sum(NVL(AR.USAGE_RATE_OR_AMOUNT,0)) duration,
1521 --sum(NVL(AR.QUANTITY,0)) quantity,
1522 -- AR.primary_uom uom_code,
1523 BR.BILLABLE_ITEM_ID,
1524 AR.TRANSACTION_DATE,
1525 MSIV.concatenated_segments item_name,
1526 MSIV.primary_uom_code uom_code
1527 FROM AHL_WIP_RESOURCE_TXNS_V AR,
1528 MTL_SYSTEM_ITEMS_VL MSIV,
1529 AHL_VISITS_B V,
1530 AHL_VISIT_TASKS_B VT,
1531 BOM_RESOURCES BR
1532 WHERE BR.resource_id=AR.resource_id
1533 AND BR.BILLABLE_ITEM_ID is not null
1534 AND AR.visit_task_id = c_visit_task_id
1535 AND MSIV.organization_id = V.organization_id
1536 AND V.visit_id = VT.visit_id
1537 AND VT.visit_task_id = c_visit_task_id
1538 AND MSIV.inventory_item_id = BR.BILLABLE_ITEM_ID
1539 AND AR.JOB_STATUS_CODE <> 22 -- 'deleted' status
1540 AND AR.JOB_STATUS_CODE <> 7 -- 'cancelled' status
1541 Group By BR.BILLABLE_ITEM_ID,
1542 MSIV.concatenated_segments,
1543 MSIV.primary_uom_code,
1544 AR.TRANSACTION_DATE;
1545
1546 l_resource_trans_rec get_resource_trans_csr%ROWTYPE;
1547
1548
1549 -- Define local variables
1550 l_x_return_status VARCHAR2(240);
1551 l_x_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
1552
1553 l_x_time_type_flag VARCHAR2(1);
1554 i NUMBER :=0; -- loop index
1555
1556 l_api_name CONSTANT VARCHAR2(30) := 'Check_Item_for_Resource_Trans';
1557
1558 BEGIN
1559
1560 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1561 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1562 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1563 'At the start of the procedure');
1564 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1565 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1566 'Visit Task ID: '|| p_visit_task_id);
1567
1568 END IF;
1569
1570 --Initialize API return status to success
1571 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1572
1573 -- Get all material requirements for the job
1574
1575 Open get_resource_trans_csr(p_visit_task_id);
1576
1577 LOOP
1578 FETCH get_resource_trans_csr INTO l_resource_trans_rec;
1579 EXIT WHEN get_resource_trans_csr%NOTFOUND;
1580
1581 l_x_item_tbl(I).item_id :=l_resource_trans_rec.BILLABLE_ITEM_ID;
1582 l_x_item_tbl(I).quantity :=1;
1583 l_x_item_tbl(I).duration :=l_resource_trans_rec.duration;
1584 l_x_item_tbl(I).UOM_code :=l_resource_trans_rec.uom_code;
1585 l_x_item_tbl(I).effective_date :=l_resource_trans_rec.TRANSACTION_DATE;
1586 l_x_item_tbl(I).Visit_Task_Id :=p_visit_task_id;
1587
1588 I := I + 1;
1589
1590 END LOOP;
1591
1592 CLOSE get_resource_trans_csr;
1593
1594 -- Assign return status and Item without Price table
1595 x_return_status:=l_x_return_status;
1596 x_item_tbl:= l_x_item_tbl;
1597
1598 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1599 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1600 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1601 'Return Status is: '|| x_return_status);
1602 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1603 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1604 'Number of Items for resource transactions: '|| x_item_tbl.count);
1605 END IF;
1606
1607 END Check_Item_for_Resource_Trans;
1608
1609 --------------------------------------------------------------------
1610 -- Procedure name : Check_Item_for_Material_Trans
1611 -- Purpose : To return items required for Mateiral transactions of a given task.
1612 --------------------------------------------------------------------
1613 PROCEDURE Check_Item_for_Materials_Trans
1614 (p_visit_task_id IN NUMBER,
1615
1616 x_item_tbl OUT NOCOPY AHL_VWP_RULES_PVT.Item_Tbl_Type,
1617 x_return_status OUT NOCOPY VARCHAR2
1618 )
1619 IS
1620 -- Define local Cursors
1621
1622 -- Get job material requirements
1623
1624 CURSOR get_materials_trans_csr (c_visit_task_id NUMBER)
1625 IS
1626 /*
1627 SELECT MAT.inventory_item_id,
1628 MAT.creation_date,
1629 SUM(NVL(MAT.quantity,0)) quantity,
1630 MSIV.primary_uom_code uom_code
1631 -- MAT.uom uom_code
1632 FROM AHL_WORKORDER_MTL_TXNS MAT,
1633 AHL_WORKORDERS_V AWOS,
1634 MTL_SYSTEM_ITEMS_VL MSIV,
1635 AHL_VISITS_B V,
1636 AHL_VISIT_TASKS_B VT,
1637 AHL_WORKORDER_OPERATIONS_V AWOP
1638 WHERE AWOP.WORKORDER_ID = AWOS.WORKORDER_ID
1639 AND AWOP.WORKORDER_OPERATION_ID = MAT.WORKORDER_OPERATION_ID
1640 AND MAT.inventory_item_id = MSIV.inventory_item_id
1641 AND MSIV.organization_id = V.organization_id
1642 AND V.visit_id = VT.visit_id
1643 AND VT.visit_task_id = c_visit_task_id
1644 AND AWOS.visit_task_id = c_visit_task_id
1645 AND AWOS.job_status_code <> 22 -- 'deleted' status
1646 AND AWOS.JOB_STATUS_CODE <> 7 -- 'cancelled' status
1647 GROUP BY MAT.inventory_item_id,
1648 MAT.creation_date,
1649 MSIV.primary_uom_code;
1650 -- MAT.uom;
1651 */
1652 -- AnRaj: Changed query for fixing the perf issue #1, bug:4919487
1653 SELECT MAT.inventory_item_id,
1654 MAT.creation_date,
1655 SUM(NVL(MAT.quantity,0)) quantity,
1656 MSIV.primary_uom_code uom_code
1657 FROM AHL_WORKORDER_MTL_TXNS MAT,
1658 AHL_WORKORDERS AWOS,
1659 MTL_SYSTEM_ITEMS_B MSIV,
1660 AHL_VISITS_B V,
1661 AHL_VISIT_TASKS_B VT,
1662 AHL_WORKORDER_OPERATIONS_V AWOP
1663 WHERE AWOP.WORKORDER_ID = AWOS.WORKORDER_ID
1664 AND AWOP.WORKORDER_OPERATION_ID = MAT.WORKORDER_OPERATION_ID
1665 AND MAT.inventory_item_id = MSIV.inventory_item_id
1666 AND MSIV.organization_id = V.organization_id
1667 AND V.visit_id = VT.visit_id
1668 AND VT.visit_task_id = c_visit_task_id
1669 AND AWOS.visit_task_id = c_visit_task_id
1670 AND AWOS.status_code <> 22 -- 'deleted' status
1671 AND AWOS.STATUS_CODE <> 7 -- 'cancelled' status
1672 GROUP BY MAT.inventory_item_id,
1673 MAT.creation_date,
1674 MSIV.primary_uom_code;
1675
1676 l_materials_trans_rec get_materials_trans_csr%ROWTYPE;
1677
1678
1679 -- Define local variables
1680 l_x_return_status VARCHAR2(240);
1681 l_x_item_tbl AHL_VWP_RULES_PVT.Item_Tbl_Type;
1682
1683 i NUMBER :=0; -- loop index
1684
1685 l_api_name CONSTANT VARCHAR2(30) := 'Check_Item_for_Prod_Mat_Req';
1686
1687 BEGIN
1688
1689 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1690 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1691 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1692 'At the start of the procedure');
1693 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1694 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1695 'Visit Task ID: '|| p_visit_task_id);
1696
1697 END IF;
1698
1699 --Initialize API return status to success
1700 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1701
1702 -- Get all material requirements for the job
1703
1704 Open get_materials_trans_csr(p_visit_task_id);
1705
1706 LOOP
1707 FETCH get_materials_trans_csr INTO l_materials_trans_rec;
1708 EXIT WHEN get_materials_trans_csr%NOTFOUND;
1709
1710 l_x_item_tbl(I).item_id :=l_materials_trans_rec.inventory_item_id;
1711 l_x_item_tbl(I).quantity :=l_materials_trans_rec.quantity;
1712 l_x_item_tbl(I).UOM_code :=l_materials_trans_rec.uom_code;
1713 l_x_item_tbl(I).effective_date :=l_materials_trans_rec.creation_date;
1714 l_x_item_tbl(I).Visit_Task_Id :=p_visit_task_id;
1715
1716 I := I + 1;
1717
1718 END LOOP;
1719
1720 CLOSE get_materials_trans_csr;
1721
1722
1723 -- Assign return status and Item without Price table
1724 x_return_status:=l_x_return_status;
1725 x_item_tbl:= l_x_item_tbl;
1726
1727 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1728 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1729 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1730 'Return Status is: '|| x_return_status);
1731 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1732 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1733 'Number of Items for material transactions: '|| x_item_tbl.count);
1734 END IF;
1735
1736 END Check_Item_for_Materials_Trans;
1737
1738
1739 --------------------------------------------------------------------
1740 -- Procedure name : Calculate_Total_Price
1741 -- Purpose : To return total price of the item table with quantity.
1742 --------------------------------------------------------------------
1743 PROCEDURE Calculate_Total_Price
1744 (p_item_tbl IN AHL_VWP_RULES_PVT.Item_Tbl_Type,
1745 p_price_list_id IN NUMBER,
1746 p_customer_id IN NUMBER,
1747 p_currency_code IN VARCHAR2,
1748
1749 x_total_price OUT NOCOPY NUMBER,
1750 x_return_status OUT NOCOPY VARCHAR2
1751 )
1752 IS
1753 -- Define local variables
1754 l_x_return_status VARCHAR2(240);
1755 l_x_item_price NUMBER := 0;
1756 i NUMBER := 0; -- table index
1757 l_total_price NUMBER := 0; -- total price
1758 l_api_name CONSTANT VARCHAR2(30) := 'Calculate_Total_Price';
1759
1760
1761 BEGIN
1762
1763 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1764 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1765 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1766 'At the start of the procedure');
1767 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1768 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1769 'Number of Items to calculate: '|| p_item_tbl.count);
1770 END IF;
1771
1772 --Initialize API return status to success
1773 l_x_return_status := FND_API.G_RET_STS_SUCCESS;
1774
1775 -- For each item in p_item_tbl, check for price
1776
1777 I := p_item_tbl.FIRST;
1778 IF I IS NOT NULL THEN
1779 LOOP
1780
1781 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1782 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1783 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': In Loop',
1784 'Loop Index: '||I );
1785 END IF;
1786
1787 -- Check the price of the item
1788 Get_Item_Price
1789 (p_item_id =>p_item_tbl(I).Item_Id ,
1790 p_price_list_id =>p_price_list_id ,
1791 p_customer_id =>p_customer_id ,
1792 p_duration =>p_item_tbl(I).Duration ,
1793 p_currency_code =>p_currency_code ,
1794 p_effective_date =>p_item_tbl(I).Effective_Date ,
1795 p_UOM_code =>p_item_tbl(I).UOM_Code ,
1796
1797 x_item_price =>l_x_item_price ,
1798 x_return_status =>l_x_return_status
1799 );
1800
1801 -- Multiply the item price with quantity
1802 -- And add the result to total price
1803 -- Consider the item price as 0 if item is not set up in price list
1804 IF (l_x_item_price IS NOT NULL) THEN
1805
1806 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1807 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1808 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': In Loop',
1809 'Item Quantity: '||p_item_tbl(I).quantity );
1810 END IF;
1811
1812 l_total_price := l_total_price + l_x_item_price * p_item_tbl(I).quantity;
1813
1814 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1815 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1816 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': In Loop',
1817 'Current Total Price: '||l_total_price );
1818 END IF;
1819 END IF;
1820
1821 EXIT WHEN I = p_item_tbl.LAST;
1822 I := p_item_tbl.NEXT(I);
1823 END LOOP;
1824 END IF;
1825
1826 -- Assign return status and Item without Price table
1827 x_return_status:=l_x_return_status;
1828 x_total_price := l_total_price;
1829
1830 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1831 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1832 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1833 'Return Status is: '|| x_return_status);
1834 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1835 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1836 'Total Price: '|| x_total_price);
1837 END IF;
1838 END Calculate_Total_Price;
1839
1840
1841 --------------------------------------------------------------------
1842 -- Procedure name : Check_UOM_Class
1843 -- Purpose : To check if the Item UOM is of type TIME.
1844 --------------------------------------------------------------------
1845
1846 PROCEDURE Check_UOM_Class
1847 (p_UOM_code IN VARCHAR2,
1848 x_time_type_flag OUT NOCOPY VARCHAR2
1849 )
1850 IS
1851 -- Define local Cursors
1852
1853 -- Get job material requirements
1854
1855 CURSOR get_uom_csr (c_UOM_code VARCHAR2)
1856 IS
1857 SELECT UOM_CODE
1858 FROM mtl_units_of_measure_vl
1859 WHERE UOM_CLASS = 'Time'
1860 AND UOM_CODE = c_UOM_code;
1861
1862 -- Define local variables
1863 l_UOM_code VARCHAR2(3);
1864
1865 l_api_name CONSTANT VARCHAR2(30) := 'Check_UOM_Class';
1866
1867 BEGIN
1868
1869 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1870 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1871 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1872 'At the start of the procedure');
1873 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1874 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
1875 'UOM Code: '|| p_UOM_code);
1876
1877 END IF;
1878
1879 -- Check to see if the uom code passed in is of Time type
1880
1881 Open get_uom_csr(p_UOM_code);
1882
1883 FETCH get_uom_csr INTO l_UOM_code;
1884
1885 IF get_uom_csr%NOTFOUND THEN
1886 x_time_type_flag := 'N';
1887 ELSE
1888 x_time_type_flag := 'Y';
1889 END IF;
1890
1891 CLOSE get_uom_csr;
1892
1893 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1894 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1895 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': End API',
1896 'Time Type flag is: '|| x_time_type_flag);
1897 END IF;
1898
1899 END Check_UOM_Class;
1900
1901
1902 END AHL_VWP_PRICE_PVT;
1903
[Home] [Help]