[Home] [Help]
PACKAGE BODY: APPS.FTE_FPA_UTIL
Source
1 PACKAGE BODY FTE_FPA_UTIL AS
2 /* $Header: FTEFPUTB.pls 120.13 2011/03/23 11:15:41 suppal ship $ */
3
4
5
6 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_FPA_UTIL';
7
8
9 PROCEDURE GET_PAYMENT_METHOD(p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
10 p_invoice_header_id in NUMBER,
11 x_msg_data OUT NOCOPY VARCHAR2,
12 x_msg_count OUT NOCOPY NUMBER,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_payment_method OUT NOCOPY VARCHAR2)
15 IS
16
17 CURSOR C_GET_BILL_DETAILS(p_inv_head_id NUMBER)
18 IS
19 SELECT PV.PARTY_ID,PVS.PARTY_SITE_ID,
20 FH.SUPPLIER_SITE_ID,FH.ORG_ID,
21 NULL PAYMENT_METHOD_LOOKUP_CODE
22 FROM FTE_INVOICE_HEADERS FH,
23 PO_VENDORS PV,
24 PO_VENDOR_SITES_ALL PVS
25 WHERE
26 FH.SUPPLIER_ID = PV.VENDOR_ID
27 AND PV.VENDOR_ID = PVS.VENDOR_ID
28 AND FH.SUPPLIER_SITE_ID = PVS.VENDOR_SITE_ID
29 AND FH.INVOICE_HEADER_ID = p_inv_head_id;
30
31
32 l_organization_type CONSTANT VARCHAR2(20) := 'OPERATING_UNIT';
33 l_payment_function CONSTANT VARCHAR2(20) := 'PAYABLES_DISB';
34 l_default_pay_method CONSTANT VARCHAR2(100) := 'CHECK';
35
36 l_Trxn_Attributes_rec IBY_DISBURSEMENT_COMP_PUB.Trxn_Attributes_Rec_Type;
37 l_Default_Pmt_Attrs_rec IBY_DISBURSEMENT_COMP_PUB.Default_Pmt_Attrs_Rec_Type;
38 l_payment_method_rec IBY_DISBURSEMENT_COMP_PUB.Payment_Method_Rec_Type;
39
40 l_vendor_pay_method VARCHAR2(100);
41 l_pay_method VARCHAR2(100);
42
43
44 l_return_status VARCHAR2(1);
45 l_msg_data VARCHAR2(2000);
46 l_msg_count NUMBER;
47
48 l_file_name VARCHAR2(100);
49
50
51 l_debug_on BOOLEAN;
52 --
53 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'GET_PAYMENT_METHOD';
54
55
56 BEGIN
57
58 /*--- Test Code Begins -------------------------------------------------------------
59 WSH_DEBUG_INTERFACE.g_debug := TRUE;
60 WSH_DEBUG_SV.start_debugger
61 (x_file_name => l_file_name,
62 x_return_status => l_return_status,
63 x_msg_count => l_msg_count,
64 x_msg_data => l_msg_data);
65 --- Test Code Ends ---------------------------------------------------------------
66 */
67
68
69
70 --
71 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
72 --
73 IF l_debug_on IS NULL THEN
74 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
75 END IF;
76
77 IF l_debug_on THEN
78 WSH_DEBUG_SV.push(l_module_name);
79 WSH_DEBUG_SV.log(l_module_name,'p_invoice_header_id',p_invoice_header_id);
80 END IF;
81
82 IF FND_API.to_Boolean( p_init_msg_list )
83 THEN
84 FND_MSG_PUB.initialize;
85 END IF;
86 --
87 --
88 -- Initialize API return status to success
89 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
90 x_msg_count := 0;
91 x_msg_data := 0;
92
93
94 -- Start Of Code
95
96 OPEN C_GET_BILL_DETAILS(p_invoice_header_id);
97
98 FETCH C_GET_BILL_DETAILS INTO
99 l_Trxn_Attributes_rec.Payee_Party_Id,
100 l_Trxn_Attributes_rec.Payee_Party_Site_Id,
101 l_Trxn_Attributes_rec.Supplier_Site_Id,
102 l_Trxn_Attributes_rec.Payer_Org_Id,
103 l_vendor_pay_method;
104
105 CLOSE C_GET_BILL_DETAILS;
106
107 IF l_debug_on THEN
108 WSH_DEBUG_SV.log(l_module_name,'l_Trxn_Attributes_rec.Payee_Party_Id',l_Trxn_Attributes_rec.Payee_Party_Id);
109 WSH_DEBUG_SV.log(l_module_name,'l_Trxn_Attributes_rec.Payee_Party_Site_Id',l_Trxn_Attributes_rec.Payee_Party_Site_Id);
110 WSH_DEBUG_SV.log(l_module_name,'l_Trxn_Attributes_rec.Supplier_Site_Id',l_Trxn_Attributes_rec.Supplier_Site_Id);
111 WSH_DEBUG_SV.log(l_module_name,'l_Trxn_Attributes_rec.Payer_Org_Id',l_Trxn_Attributes_rec.Payer_Org_Id);
112 WSH_DEBUG_SV.log(l_module_name,'l_vendor_pay_method',l_vendor_pay_method);
113 END IF;
114
115
116 l_Trxn_Attributes_rec.Payer_Org_Type := l_organization_type;
117 l_Trxn_Attributes_rec.payment_function := l_payment_function;
118
119 IBY_DISBURSEMENT_COMP_PUB.Get_Default_Payment_Attributes(
120 p_api_version => 1.0,
121 p_init_msg_list => FND_API.G_FALSE,
122 p_ignore_payee_pref => NULL,
123 p_trxn_attributes_rec => l_Trxn_Attributes_rec,
124 x_return_status => l_return_status,
125 x_msg_count => l_msg_count,
126 x_msg_data => l_msg_data,
127 x_default_pmt_attrs_rec => l_Default_Pmt_Attrs_rec);
128
129 FND_MSG_PUB.Count_And_Get
130 (
131 p_count => x_msg_count,
132 p_data => x_msg_data,
133 p_encoded => FND_API.G_FALSE
134 );
135
136
137
138 l_payment_method_rec := l_Default_Pmt_Attrs_rec.Payment_Method;
139 l_pay_method := l_payment_method_rec.Payment_Method_Code;
140 x_payment_method := nvl(l_pay_method,nvl(l_vendor_pay_method,l_default_pay_method));
141
142 IF l_debug_on THEN
143 WSH_DEBUG_SV.log(l_module_name,'l_pay_method',l_pay_method);
144 WSH_DEBUG_SV.log(l_module_name,'l_vendor_pay_method',l_vendor_pay_method);
145 WSH_DEBUG_SV.log(l_module_name,'l_default_pay_method',l_default_pay_method);
146 WSH_DEBUG_SV.log(l_module_name,'x_payment_method',x_payment_method);
147 END IF;
148
149
150 -- End of Code
151
152 IF l_debug_on THEN
153 WSH_DEBUG_SV.pop(l_module_name);
154 END IF;
155
156 EXCEPTION
157
158 WHEN FND_API.G_EXC_ERROR THEN
159 x_return_status := FND_API.G_RET_STS_ERROR;
160 FND_MSG_PUB.Count_And_Get
161 (
162 p_count => x_msg_count,
163 p_data => x_msg_data,
164 p_encoded => FND_API.G_FALSE
165 );
166
167 IF l_debug_on THEN
168 WSH_DEBUG_SV.pop(l_module_name);
169 END IF;
170
171 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
173 FND_MSG_PUB.Count_And_Get
174 (
175 p_count => x_msg_count,
176 p_data => x_msg_data,
177 p_encoded => FND_API.G_FALSE
178 );
179
180 IF l_debug_on THEN
181 WSH_DEBUG_SV.pop(l_module_name);
182 END IF;
183
184 WHEN OTHERS THEN
185 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
186 FND_MSG_PUB.Count_And_Get
187 (
188 p_count => x_msg_count,
189 p_data => x_msg_data,
190 p_encoded => FND_API.G_FALSE
191 );
192
193
194 IF l_debug_on THEN
195 WSH_DEBUG_SV.pop(l_module_name);
196 END IF;
197
198
199 END GET_PAYMENT_METHOD;
200
201
202
203
204 FUNCTION GET_FREIGHT_COST_TRUCK (p_trip_id IN NUMBER,
205 p_delivery_detail_id IN NUMBER,
206 p_inventory_item_id IN NUMBER,
207 p_delivery_leg_id IN NUMBER,
208 p_bol IN VARCHAR2,
209 p_container_flag IN VARCHAR2,
210 p_gross_weight IN NUMBER,
211 p_weight_uom IN VARCHAR2,
212 g_currency_code IN VARCHAR2)
213
214 RETURN NUMBER
215 IS
216
217 -- 1 Gets the Total BOL Approved Amount
218
219 CURSOR C_APPROVED_AMOUNT(p_bol_no VARCHAR2)
220 IS
221 SELECT nvl(APPROVED_AMOUNT,0),
222 CURRENCY_CODE
223 FROM FTE_INVOICE_HEADERS
224 WHERE BOL = p_bol_no;
225
226 -- 2 Gets the Total Rated Amount for the Trip
227
228 CURSOR C_TOTAL_AMOUNT (p_trip NUMBER)
229 IS
230 SELECT
231 nvl(WFC.TOTAL_AMOUNT,0),
232 WFC.CURRENCY_CODE
233 FROM WSH_FREIGHT_COSTS WFC,
234 WSH_FREIGHT_COST_TYPES CT
235 WHERE
236 WFC.FREIGHT_COST_TYPE_ID = CT.FREIGHT_COST_TYPE_ID
237 AND WFC.LINE_TYPE_CODE = 'SUMMARY'
238 AND CT.NAME = 'SUMMARY'
239 AND WFC.TRIP_ID = p_trip;
240
241 -- 3 Gets the Total Rate Amount At Delivery Leg Level...
242
243 CURSOR C_DLEG_AMOUNT (p_trip NUMBER,p_dleg_id NUMBER)
244 IS
245 SELECT nvl(WFC.TOTAL_AMOUNT,0),
246 WFC.CURRENCY_CODE
247 FROM
248 WSH_DELIVERY_LEGS WDL ,
249 WSH_TRIP_STOPS WT,
250 WSH_FREIGHT_COSTS WFC
251 WHERE
252 WT.STOP_ID = WDL.PICK_UP_STOP_ID
253 AND WFC.DELIVERY_LEG_ID = WDL.DELIVERY_LEG_ID
254 AND WT.TRIP_ID = p_trip
255 AND WFC.DELIVERY_DETAIL_ID IS NULL
256 AND WFC.LINE_TYPE_CODE = 'SUMMARY'
257 AND WFC.DELIVERY_LEG_ID = p_dleg_id;
258
259 -- 4 Gets the Gross Weight of Delivery Detail Including Containers..
260
261 CURSOR C_GROSS_WEIGHT( p_detail_id NUMBER, g_wt_uom VARCHAR2)
262 IS
263 SELECT nvl(SUM(WSH_WV_UTILS.CONVERT_UOM(
264 WEIGHT_UOM_CODE,
265 g_wt_uom,
266 WDD.GROSS_WEIGHT,
267 WDD.INVENTORY_ITEM_ID)),0)
268 FROM WSH_DELIVERY_DETAILS WDD
269 WHERE WDD.DELIVERY_DETAIL_ID
270 IN (
271 SELECT DELIVERY_DETAIL_ID
272 FROM WSH_DELIVERY_ASSIGNMENTS
273 START WITH DELIVERY_DETAIL_ID = p_detail_id
274 CONNECT BY PRIOR PARENT_DELIVERY_DETAIL_ID= DELIVERY_DETAIL_ID
275 );
276
277
278 -- 5 Gets the Top Level Parent Delivery Detail..
279
280 CURSOR C_PARENT_DETAIL(p_detail_id NUMBER)
281 IS
282 SELECT DELIVERY_DETAIL_ID
283 FROM
284 (SELECT
285 DELIVERY_DETAIL_ID,
286 LEVEL
287 FROM WSH_DELIVERY_ASSIGNMENTS
288 START WITH DELIVERY_DETAIL_ID = p_detail_id
289 CONNECT BY PRIOR PARENT_DELIVERY_DETAIL_ID = DELIVERY_DETAIL_ID ORDER BY LEVEL DESC
290 ) A
291 WHERE ROWNUM = 1;
292
293 -- 6 Gets the Detail Rate Amount
294 CURSOR C_DETAIL_AMOUNT( p_detail_id NUMBER, p_dleg_id NUMBER,
295 p_cont_flag VARCHAR2, p_container_detail_id NUMBER)
296 IS
297 SELECT
298 nvl(WFC.TOTAL_AMOUNT,0) ,
299 WFC.CURRENCY_CODE
300 FROM
301 WSH_FREIGHT_COSTS WFC
302 WHERE
303 WFC.DELIVERY_LEG_ID = p_dleg_id
304 AND WFC.DELIVERY_DETAIL_ID = DECODE (p_cont_flag,'Y',p_container_detail_id,'N',p_detail_id)
305 AND WFC.LINE_TYPE_CODE = 'SUMMARY';
306
307
308 l_approved_amount NUMBER;
309 l_currency_code VARCHAR2(15);
310 l_total_amount NUMBER;
311 l_dleg_header_amount NUMBER;
312 l_dleg_approved_amount NUMBER;
313 l_container_flag VARCHAR2(1);
314 l_gross_weight NUMBER;
315 l_container_delivery_detail NUMBER;
316 l_detail_amount NUMBER;
317 l_detail_approved_amount NUMBER;
318 l_result_amount NUMBER;
319 --
320
321 l_debug_on BOOLEAN;
322 --
323 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'GET_FREIGHT_COST_TRUCK';
324
325
326
327
328 BEGIN
329 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
330
331 IF l_debug_on IS NULL THEN
332 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
333 END IF;
334
335
336 IF l_debug_on THEN
337 WSH_DEBUG_SV.push(l_module_name);
338 END IF;
339
340 IF l_debug_on THEN
341
342 WSH_DEBUG_SV.log(l_module_name,' *****************************.. ');
343 WSH_DEBUG_SV.log(l_module_name,' Printing Parameters Below... ');
344 WSH_DEBUG_SV.log(l_module_name,' *****************************.. ');
345
346 WSH_DEBUG_SV.log(l_module_name,' p_trip_id ',p_trip_id);
347 WSH_DEBUG_SV.log(l_module_name,' p_delivery_detail_id ',p_delivery_detail_id);
348 WSH_DEBUG_SV.log(l_module_name,' p_inventory_item_id ',p_inventory_item_id);
349 WSH_DEBUG_SV.log(l_module_name,' p_delivery_leg_id ',p_delivery_leg_id);
350 WSH_DEBUG_SV.log(l_module_name,' p_bol ',p_bol);
351 WSH_DEBUG_SV.log(l_module_name,' p_container_flag ',p_container_flag);
352 WSH_DEBUG_SV.log(l_module_name,' p_gross_weight ',p_gross_weight);
353 WSH_DEBUG_SV.log(l_module_name,' p_weight_uom ',p_weight_uom);
354 WSH_DEBUG_SV.log(l_module_name,' g_currency_code ',g_currency_code);
355
356 END IF;
357
358 OPEN C_APPROVED_AMOUNT (p_bol);
359 FETCH C_APPROVED_AMOUNT INTO l_approved_amount,l_currency_code;
360 CLOSE C_APPROVED_AMOUNT;
361
362 IF l_debug_on THEN
363 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_APPROVED_AMOUNT ');
364 WSH_DEBUG_SV.log(l_module_name,' l_approved_amount ',l_approved_amount);
365 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
366 END IF;
367
368
369 l_approved_amount :=GL_CURRENCY_API.convert_amount (
370 x_from_currency => l_currency_code,
371 x_to_currency => g_currency_code,
372 x_conversion_date => sysdate,
373 x_amount => l_approved_amount);
374
375 IF l_debug_on THEN
376 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_APPROVED_AMOUNT == After Currency Conversion ');
377 WSH_DEBUG_SV.log(l_module_name,' l_approved_amount ',l_approved_amount);
378 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',g_currency_code);
379 END IF;
380
381
382
383 OPEN C_TOTAL_AMOUNT (p_trip_id);
384 FETCH C_TOTAL_AMOUNT INTO l_total_amount,l_currency_code;
385 CLOSE C_TOTAL_AMOUNT;
386
387 IF l_debug_on THEN
388 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_TOTAL_AMOUNT ');
389 WSH_DEBUG_SV.log(l_module_name,' l_total_amount ',l_total_amount);
390 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
391 END IF;
392
393
394 l_total_amount := GL_CURRENCY_API.convert_amount (
395 x_from_currency => l_currency_code,
396 x_to_currency => g_currency_code,
397 x_conversion_date => sysdate,
398 x_amount => l_total_amount);
399
400 IF l_debug_on THEN
401 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_TOTAL_AMOUNT == After Currency Conversion ');
402 WSH_DEBUG_SV.log(l_module_name,' l_total_amount ',l_total_amount);
403 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',g_currency_code);
404 END IF;
405
406
407
408 OPEN C_DLEG_AMOUNT (p_trip_id,p_delivery_leg_id);
409 FETCH C_DLEG_AMOUNT INTO l_dleg_header_amount,l_currency_code;
410 CLOSE C_DLEG_AMOUNT;
411
412 IF l_debug_on THEN
413 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DLEG_AMOUNT ');
414 WSH_DEBUG_SV.log(l_module_name,' l_dleg_header_amount ',l_dleg_header_amount);
415 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
416 END IF;
417
418
419 l_dleg_header_amount := GL_CURRENCY_API.convert_amount (
420 x_from_currency => l_currency_code,
421 x_to_currency => g_currency_code,
422 x_conversion_date => sysdate,
423 x_amount => l_dleg_header_amount);
424
425 IF l_debug_on THEN
426 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DLEG_AMOUNT ');
427 WSH_DEBUG_SV.log(l_module_name,' l_dleg_header_amount ',l_dleg_header_amount);
428 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
429 END IF;
430
431
432 IF( l_total_amount = 0) THEN
433 l_dleg_approved_amount := 0;
434 ELSE
435 l_dleg_approved_amount := nvl((l_dleg_header_amount * l_approved_amount / l_total_amount),0);
436 END IF;
437
438
439 IF l_debug_on THEN
440 WSH_DEBUG_SV.log(l_module_name,' After Pro-Ration to Delivery Leg Level ');
441 WSH_DEBUG_SV.log(l_module_name,' l_dleg_approved_amount ',l_dleg_approved_amount);
442 END IF;
443
444
445 IF p_container_flag = 'Y' THEN
446
447 OPEN C_GROSS_WEIGHT(p_delivery_detail_id, p_weight_uom);
448 FETCH C_GROSS_WEIGHT into l_gross_weight;
449 CLOSE C_GROSS_WEIGHT;
450
451 IF l_debug_on THEN
452 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_GROSS_WEIGHT ');
453 WSH_DEBUG_SV.log(l_module_name,' l_gross_weight ',l_gross_weight);
454 END IF;
455
456 OPEN C_PARENT_DETAIL(p_delivery_detail_id);
457 FETCH C_PARENT_DETAIL INTO l_container_delivery_detail;
458 CLOSE C_PARENT_DETAIL;
459
460 IF l_debug_on THEN
461 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_PARENT_DETAIL ');
462 WSH_DEBUG_SV.log(l_module_name,' l_container_delivery_detail ',l_container_delivery_detail);
463 END IF;
464
465
466 IF p_delivery_detail_id <> l_container_delivery_detail THEN
467 l_container_flag := 'Y';
468 ELSE
469 l_container_flag := 'N';
470 END IF;
471
472 IF l_debug_on THEN
473 WSH_DEBUG_SV.log(l_module_name,' l_container_flag ',l_container_flag);
474 END IF;
475
476
477
478 END IF;
479
480 OPEN C_DETAIL_AMOUNT(p_delivery_detail_id,p_delivery_leg_id,l_container_flag, l_container_delivery_detail);
481 FETCH C_DETAIL_AMOUNT INTO l_detail_amount,l_currency_code;
482 CLOSE C_DETAIL_AMOUNT;
483
484 IF l_debug_on THEN
485 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DETAIL_AMOUNT ');
486 WSH_DEBUG_SV.log(l_module_name,' l_detail_amount ',l_detail_amount);
487 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
488 END IF;
489
490 l_detail_amount := GL_CURRENCY_API.convert_amount (
491 x_from_currency => l_currency_code,
492 x_to_currency => g_currency_code,
493 x_conversion_date => sysdate,
494 x_amount => l_detail_amount);
495
496 IF l_debug_on THEN
497 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DETAIL_AMOUNT ==> After Conversion ');
498 WSH_DEBUG_SV.log(l_module_name,' l_detail_amount ',l_detail_amount);
499 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',g_currency_code);
500 END IF;
501
502 IF (l_dleg_header_amount = 0 ) THEN
503 l_detail_approved_amount := 0;
504 ELSE
505 l_detail_approved_amount := NVL((l_detail_amount * l_dleg_approved_amount / l_dleg_header_amount),0);
506 END IF;
507
508 IF l_container_flag = 'Y' THEN
509 IF l_gross_weight = 0 THEN
510 l_detail_approved_amount := 0;
511 ELSE
512 l_detail_approved_amount :=NVL(( l_detail_approved_amount * p_gross_weight / l_gross_weight),0);
513 END IF ;
514 END IF;
515
516 IF l_debug_on THEN
517 WSH_DEBUG_SV.log(l_module_name,' After Pro-Ration to Delivery Detail Level ');
518 WSH_DEBUG_SV.log(l_module_name,' l_detail_approved_amount ',l_detail_approved_amount);
519 END IF;
520
521 IF l_debug_on THEN
522 WSH_DEBUG_SV.pop(l_module_name);
523 END IF;
524
525 RETURN l_detail_approved_amount;
526
527
528 END GET_FREIGHT_COST_TRUCK;
529
530
531
532
533 FUNCTION GET_FREIGHT_COST_LTL (p_delivery_leg_id IN NUMBER,
534 p_delivery_detail_id IN NUMBER,
535 p_commodity_category_id IN NUMBER,
536 p_bol IN VARCHAR2,
537 g_currency_code IN VARCHAR2,
538 p_invoice_header_id IN NUMBER)
539
540
541 RETURN NUMBER
542 IS
543
544
545 -- Get the total rate amount on the freight class
546
547 CURSOR C_TOTAL_SUMMARY_AMT
548 IS
549 SELECT
550 nvl( SUM(TOTAL_AMOUNT),0),
551 MAX(CURRENCY_CODE)
552 FROM WSH_FREIGHT_COSTS
553 WHERE
554 DELIVERY_LEG_ID = p_delivery_leg_id
555 AND LINE_TYPE_CODE = 'SUMMARY'
556 AND CHARGE_SOURCE_CODE= 'PRICING_ENGINE';
557
558
559 CURSOR C_TOTAL_DISCOUNT_AMT
560 IS
561 SELECT
562 nvl( SUM(TOTAL_AMOUNT),0),
563 MAX(CURRENCY_CODE)
564 FROM WSH_FREIGHT_COSTS
565 WHERE
566 DELIVERY_LEG_ID = p_delivery_leg_id
567 AND LINE_TYPE_CODE = 'DISCOUNT'
568 AND CHARGE_SOURCE_CODE= 'PRICING_ENGINE';
569
570
571 CURSOR C_TOTAL_SURCHARGE_AMT
572 IS
573 SELECT
574 nvl( SUM(TOTAL_AMOUNT),0),
575 MAX(CURRENCY_CODE)
576 FROM WSH_FREIGHT_COSTS
577 WHERE
578 DELIVERY_LEG_ID = p_delivery_leg_id
579 AND LINE_TYPE_CODE = 'CHARGE'
580 AND CHARGE_SOURCE_CODE= 'PRICING_ENGINE';
581
582 CURSOR C_TOTAL_AMT
583 IS
584 SELECT
585 nvl( SUM(TOTAL_AMOUNT),0),
586 MAX(CURRENCY_CODE)
587 FROM WSH_FREIGHT_COSTS
588 WHERE
589 DELIVERY_LEG_ID = p_delivery_leg_id
590 AND LINE_TYPE_CODE = 'PRICE'
591 AND CHARGE_SOURCE_CODE= 'PRICING_ENGINE'
592 AND COMMODITY_CATEGORY_ID = p_commodity_category_id;
593
594 -- Get the Total Approved Amount on the BOL for the Freight Class
595
596 CURSOR C_LINE_APPROVED_AMT
597 IS
598 SELECT
599 NVL( SUM(FL.APPROVED_AMOUNT),0 ),
600 MAX(FH.CURRENCY_CODE)
601 FROM FTE_INVOICE_HEADERS FH,
602 FTE_INVOICE_LINES FL,
603 MTL_CATEGORIES_KFV C,
604 MTL_CATEGORY_SETS S
605 WHERE FH.INVOICE_HEADER_ID = FL.INVOICE_HEADER_ID
606 AND FH.BOL = p_bol
607 AND FL.FREIGHT_CLASS = C.CONCATENATED_SEGMENTS
608 AND FH.BILL_STATUS <> 'OBSOLETE'
609 AND S.STRUCTURE_ID = C.STRUCTURE_ID
610 AND S.CATEGORY_SET_NAME = 'WSH_COMMODITY_CODE'
611 AND C.ENABLED_FLAG = 'Y'
612 AND C.CATEGORY_ID = p_commodity_category_id
613 AND FL.INVOICE_LINE_TYPE = 'LINE';
614
615 -- Approved discount
616 CURSOR C_TOTAL_APPROVED_DISCOUNT
617 IS
618 SELECT
619 NVL( SUM(FL.APPROVED_AMOUNT),0 ),
620 MAX(FH.CURRENCY_CODE)
621 FROM FTE_INVOICE_HEADERS FH,
622 FTE_INVOICE_LINES FL
623 WHERE FH.INVOICE_HEADER_ID = FL.INVOICE_HEADER_ID
624 AND FH.BOL = p_bol
625 AND FH.BILL_STATUS <> 'OBSOLETE'
626 AND FL.INVOICE_LINE_TYPE = 'DISCOUNT';
627
628 -- Approved charge
629 CURSOR C_TOTAL_APPROVED_SURCHARGE
630 IS
631 SELECT
632 NVL( SUM(FL.APPROVED_AMOUNT),0 ),
633 MAX(FH.CURRENCY_CODE)
634 FROM FTE_INVOICE_HEADERS FH,
635 FTE_INVOICE_LINES FL
636 WHERE FH.INVOICE_HEADER_ID = FL.INVOICE_HEADER_ID
637 AND FH.BOL = p_bol
638 AND FH.BILL_STATUS <> 'OBSOLETE'
639 AND FL.INVOICE_LINE_TYPE = 'SURCHARGE';
640
641
642 CURSOR C_LINE_APPROVED_DISCOUNT(p_dleg_id NUMBER,p_bol_no VARCHAR2,p_commodity_id NUMBER)
643 IS
644 SELECT
645 (SELECT SUM(TOTAL_AMOUNT) FROM WSH_FREIGHT_COSTS
646 WHERE DELIVERY_LEG_ID = p_dleg_id
647 AND LINE_TYPE_CODE = 'DISCOUNT'
648 AND CHARGE_SOURCE_CODE= 'PRICING_ENGINE'
649 AND COMMODITY_CATEGORY_ID = p_commodity_id
650 ) /
651 (SELECT SUM(TOTAL_AMOUNT) FROM WSH_FREIGHT_COSTS
652 WHERE DELIVERY_LEG_ID = p_dleg_id
653 AND LINE_TYPE_CODE = 'DISCOUNT'
654 AND CHARGE_SOURCE_CODE= 'PRICING_ENGINE'
655 ) * FL.APPROVED_AMOUNT FROM FTE_INVOICE_LINES FL ,FTE_INVOICE_HEADERS FH
656 WHERE FL.INVOICE_LINE_TYPE ='DISCOUNT'
657 AND FH.INVOICE_HEADER_ID = FL.INVOICE_HEADER_ID
658 AND FH.BOL = p_bol_no;
659
660
661 CURSOR C_LINE_APPROVED_SURCHARGE(p_dleg_id NUMBER,p_bol_no VARCHAR2,p_commodity_id NUMBER)
662 IS
663 SELECT
664 (SELECT SUM(TOTAL_AMOUNT) FROM WSH_FREIGHT_COSTS
665 WHERE DELIVERY_LEG_ID = p_dleg_id
666 AND LINE_TYPE_CODE = 'CHARGE'
667 AND CHARGE_SOURCE_CODE= 'PRICING_ENGINE'
668 AND COMMODITY_CATEGORY_ID = p_commodity_id
669 ) /
670 (SELECT SUM(TOTAL_AMOUNT) FROM WSH_FREIGHT_COSTS
671 WHERE DELIVERY_LEG_ID = p_dleg_id
672 AND LINE_TYPE_CODE = 'CHARGE'
673 AND CHARGE_SOURCE_CODE= 'PRICING_ENGINE'
674 ) * FL.APPROVED_AMOUNT FROM FTE_INVOICE_LINES FL ,FTE_INVOICE_HEADERS FH
675 WHERE FL.INVOICE_LINE_TYPE ='SURCHARGE'
676 AND FH.INVOICE_HEADER_ID = FL.INVOICE_HEADER_ID
677 AND FH.BOL = p_bol_no;
678
679
680 -- Get the Detail Amount
681
682 CURSOR C_DETAIL_AMOUNT
683 IS
684 SELECT nvl(total_amount,0),
685 currency_code
686 FROM wsh_freight_costs
687 WHERE DELIVERY_LEG_ID = p_delivery_leg_id
688 AND DELIVERY_DETAIL_ID = p_delivery_detail_id
689 AND LINE_TYPE_CODE = 'PRICE'
690 AND CHARGE_SOURCE_CODE = 'PRICING_ENGINE'
691 AND COMMODITY_CATEGORY_ID = p_commodity_category_id;
692
693 -- Detail discount amount
694
695 CURSOR C_DETAIL_DISCOUNT_AMOUNT
696 IS
697 SELECT nvl(total_amount,0),
698 currency_code
699 FROM wsh_freight_costs
700 WHERE DELIVERY_LEG_ID = p_delivery_leg_id
701 AND DELIVERY_DETAIL_ID = p_delivery_detail_id
702 AND LINE_TYPE_CODE = 'DISCOUNT'
703 AND CHARGE_SOURCE_CODE = 'PRICING_ENGINE'
704 AND COMMODITY_CATEGORY_ID = p_commodity_category_id;
705
706
707 CURSOR C_DETAIL_SURCHARGE_AMOUNT
708 IS
709 SELECT nvl(total_amount,0),
710 currency_code
711 FROM wsh_freight_costs
712 WHERE DELIVERY_LEG_ID = p_delivery_leg_id
713 AND DELIVERY_DETAIL_ID = p_delivery_detail_id
714 AND LINE_TYPE_CODE = 'CHARGE'
715 AND CHARGE_SOURCE_CODE = 'PRICING_ENGINE'
716 AND COMMODITY_CATEGORY_ID = p_commodity_category_id;
717
718
719 -- Get line level audit value
720
721 CURSOR C_GET_LINE_LEVEL_AUDIT_VALUE
722 IS
723 SELECT nvl(freight_audit_line_level,'N'),FTE_INVOICE_HEADERS.APPROVED_AMOUNT
724 FROM WSH_CARRIERS, FTE_INVOICE_HEADERS
725 WHERE WSH_CARRIERS.CARRIER_ID = FTE_INVOICE_HEADERS.CARRIER_ID
726 AND FTE_INVOICE_HEADERS.INVOICE_HEADER_ID = p_invoice_header_id;
727
728
729
730 l_summary_amount NUMBER;
731 l_currency_code VARCHAR2(15);
732 l_line_approved_amount NUMBER;
733 l_line_approved_discount NUMBER;
734 l_line_approved_surcharge NUMBER;
735 l_detail_approved_amount NUMBER;
736 l_total_amount NUMBER;
737
738
739 l_total_dleg_approved_amount NUMBER;
740 l_total_dleg_approved_discount NUMBER;
741 l_total_dleg_approved_charge NUMBER;
742 l_total_commd_approved_amount NUMBER;
743
744 l_line_audit_level VARCHAR2(1);
745
746 l_cal_dleg_amount NUMBER;
747 l_cal_detail_amount NUMBER;
748
749 l_cal_dleg_discount NUMBER;
750 l_cal_detail_discount NUMBER;
751
752 l_cal_dleg_charge NUMBER;
753 l_cal_detail_charge NUMBER;
754
755 l_cal_commodity_amount NUMBER;
756
757 l_prorated_detail_discount NUMBER;
758 l_prorated_detail_charge NUMBER;
759
760
761 l_debug_on BOOLEAN;
762 --
763 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'GET_FREIGHT_COST_LTL';
764
765
766 BEGIN
767
768 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
769
770 IF l_debug_on IS NULL THEN
771 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
772 END IF;
773
774
775
776 OPEN C_GET_LINE_LEVEL_AUDIT_VALUE;
777 FETCH C_GET_LINE_LEVEL_AUDIT_VALUE INTO l_line_audit_level,l_total_dleg_approved_amount;
778 CLOSE C_GET_LINE_LEVEL_AUDIT_VALUE;
779
780 l_detail_approved_amount := 0;
781
782 IF l_debug_on THEN
783 WSH_DEBUG_SV.push(l_module_name);
784 END IF;
785
786 IF l_debug_on THEN
787
788 WSH_DEBUG_SV.log(l_module_name,' *****************************.. ');
789 WSH_DEBUG_SV.log(l_module_name,' Printing Parameters Below... ');
790 WSH_DEBUG_SV.log(l_module_name,' *****************************.. ');
791 WSH_DEBUG_SV.log(l_module_name,' p_delivery_leg_id ',p_delivery_leg_id);
792 WSH_DEBUG_SV.log(l_module_name,' p_delivery_detail_id ',p_delivery_detail_id);
793 WSH_DEBUG_SV.log(l_module_name,' p_commodity_category_id ',p_commodity_category_id);
794 WSH_DEBUG_SV.log(l_module_name,' p_bol ',p_bol);
795 WSH_DEBUG_SV.log(l_module_name,' g_currency_code ',g_currency_code);
796 WSH_DEBUG_SV.log(l_module_name,' l_line_audit_level ',l_line_audit_level);
797
798 END IF;
799
800
801 IF (l_line_audit_level = 'N')
802 THEN
803 --{
804 -- There is not approved amount at line level so we have to prorate it
805 -- based on the ratio of rated delivery detail amount / total summary amount
806 -- to the total approved amount. Because both total summary and
807 -- approved amount consists of discount and surcharge
808 OPEN C_TOTAL_SUMMARY_AMT;
809 FETCH C_TOTAL_SUMMARY_AMT INTO l_cal_dleg_amount,l_currency_code;
810 CLOSE C_TOTAL_SUMMARY_AMT;
811
812 -- Convert currency
813 l_cal_dleg_amount := GL_CURRENCY_API.convert_amount (
814 x_from_currency => l_currency_code,
815 x_to_currency => g_currency_code,
816 x_conversion_date => sysdate,
817 x_amount => l_cal_dleg_amount);
818
819 -- Get the Detail Amount
820 OPEN C_DETAIL_AMOUNT;
821 FETCH C_DETAIL_AMOUNT INTO l_cal_detail_amount,l_currency_code;
822 CLOSE C_DETAIL_AMOUNT;
823
824 IF l_debug_on THEN
825 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DETAIL_AMOUNT ');
826 WSH_DEBUG_SV.log(l_module_name,' l_cal_detail_amount ',l_cal_detail_amount);
827 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
828 END IF;
829
830 IF (l_cal_detail_amount <> 0)
831 THEN
832
833 -- Convert delivery detail amount
834 l_cal_detail_amount := GL_CURRENCY_API.convert_amount (
835 x_from_currency => l_currency_code,
836 x_to_currency => g_currency_code,
837 x_conversion_date => sysdate,
838 x_amount => l_cal_detail_amount);
839 END IF;
840
841 IF (l_total_dleg_approved_amount <> 0)
842 THEN
843
844 -- Convert invoice amount
845 l_total_dleg_approved_amount := GL_CURRENCY_API.convert_amount (
846 x_from_currency => l_currency_code,
847 x_to_currency => g_currency_code,
848 x_conversion_date => sysdate,
849 x_amount => l_total_dleg_approved_amount);
850 END IF;
851
852 IF (l_cal_dleg_amount <> 0)
853 THEN
854 l_detail_approved_amount := nvl(((l_total_dleg_approved_amount * l_cal_detail_amount) / l_cal_dleg_amount),0);
855 END IF;
856
857 --}
858 ELSE -- Line level audit is Y
859 --{
860 -- We have to prorate delivery detail amount based on the
861 -- amount that is approved at commodity level and
862 -- also include discount and surchage prorate
863
864 -- Get the total rate amount on the freight class
865 --{ Initial detail calculation
866
867 OPEN C_TOTAL_AMT;
868 FETCH C_TOTAL_AMT INTO l_cal_commodity_amount,l_currency_code;
869 CLOSE C_TOTAL_AMT;
870
871 IF l_debug_on THEN
872 WSH_DEBUG_SV.log(l_module_name,' l_cal_commodity_amount ',l_cal_commodity_amount);
873 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
874 END IF;
875
876
877 l_cal_commodity_amount := GL_CURRENCY_API.convert_amount (
878 x_from_currency => l_currency_code,
879 x_to_currency => g_currency_code,
880 x_conversion_date => sysdate,
881 x_amount => l_cal_commodity_amount);
882
883 IF l_debug_on THEN
884 WSH_DEBUG_SV.log(l_module_name,' After Conversion ');
885 WSH_DEBUG_SV.log(l_module_name,' l_cal_commodity_amount ',l_cal_commodity_amount);
886 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',g_currency_code);
887 END IF;
888
889
890
891 -- Get the Detail Amount
892 OPEN C_DETAIL_AMOUNT;
893 FETCH C_DETAIL_AMOUNT INTO l_cal_detail_amount,l_currency_code;
894 CLOSE C_DETAIL_AMOUNT;
895
896 IF l_debug_on THEN
897 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DETAIL_AMOUNT ');
898 WSH_DEBUG_SV.log(l_module_name,' l_cal_detail_amount ',l_cal_detail_amount);
899 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
900 END IF;
901
902
903 l_cal_detail_amount := GL_CURRENCY_API.convert_amount (
904 x_from_currency => l_currency_code,
905 x_to_currency => g_currency_code,
906 x_conversion_date => sysdate,
907 x_amount => l_cal_detail_amount);
908
909 IF l_debug_on THEN
910 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DETAIL_AMOUNT ==> After Conversion ');
911 WSH_DEBUG_SV.log(l_module_name,' l_cal_detail_amount ',l_cal_detail_amount);
912 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',g_currency_code);
913 END IF;
914
915
916
917
918 -- Get the Total Approved Amount on the BOL for the Freight Class
919
920 -- Line Amount (Base Rate )
921 OPEN C_LINE_APPROVED_AMT;
922 FETCH C_LINE_APPROVED_AMT INTO l_total_commd_approved_amount,l_currency_code;
923 CLOSE C_LINE_APPROVED_AMT;
924
925 IF l_debug_on THEN
926 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_LINE_APPROVED_AMT ');
927 WSH_DEBUG_SV.log(l_module_name,' l_total_commd_approved_amount ',l_total_commd_approved_amount);
928 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
929 END IF;
930
931
932 l_total_commd_approved_amount := GL_CURRENCY_API.convert_amount (
933 x_from_currency => l_currency_code,
934 x_to_currency => g_currency_code,
935 x_conversion_date => sysdate,
936 x_amount => l_total_commd_approved_amount);
937
938 IF l_debug_on THEN
939 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_APPROVED_AMT ==> After Conversion ');
940 WSH_DEBUG_SV.log(l_module_name,' l_total_commd_approved_amount ',l_total_commd_approved_amount);
941 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',g_currency_code);
942 END IF;
943
944 -- Initial Calculation without discount
945 If (l_cal_commodity_amount <> 0)
946 THEN
947 l_detail_approved_amount := nvl(((l_total_commd_approved_amount * l_cal_detail_amount) / l_cal_commodity_amount),0);
948 END IF;
949 IF l_debug_on THEN
950 WSH_DEBUG_SV.log(l_module_name,'l_detail_approved_amount before discount / surcharge ' , l_detail_approved_amount);
951 END IF;
952
953
954 --} Initial detail calculation
955 -- Prorating Discount
956 --{ Discount Calculation
957
958 -- Get detail discount
959 OPEN C_DETAIL_DISCOUNT_AMOUNT;
960 FETCH C_DETAIL_DISCOUNT_AMOUNT INTO l_cal_detail_discount,l_currency_code;
961 CLOSE C_DETAIL_DISCOUNT_AMOUNT;
962
963 l_cal_detail_discount := nvl(l_cal_detail_discount,0);
964
965 IF l_debug_on THEN
966 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DETAIL_DISCOUNT_AMOUNT ');
967 WSH_DEBUG_SV.log(l_module_name,' l_cal_detail_discount ',l_cal_detail_discount);
968 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
969 END IF;
970
971
972 l_cal_detail_discount := GL_CURRENCY_API.convert_amount (
973 x_from_currency => l_currency_code,
974 x_to_currency => g_currency_code,
975 x_conversion_date => sysdate,
976 x_amount => l_cal_detail_discount);
977
978 IF l_debug_on THEN
979 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DETAIL_DISCOUNT_AMOUNT ');
980 WSH_DEBUG_SV.log(l_module_name,' l_cal_detail_discount ',l_cal_detail_discount);
981 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
982 END IF;
983
984
985 -- Get calcualted summary discount
986 OPEN C_TOTAL_DISCOUNT_AMT;
987 FETCH C_TOTAL_DISCOUNT_AMT INTO l_cal_dleg_discount,l_currency_code;
988 CLOSE C_TOTAL_DISCOUNT_AMT;
989
990 l_cal_dleg_discount := nvl(l_cal_dleg_discount,0);
991
992 IF l_debug_on THEN
993 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_TOTAL_DISCOUNT_AMT ');
994 WSH_DEBUG_SV.log(l_module_name,' l_cal_dleg_discount ',l_cal_dleg_discount);
995 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
996 END IF;
997
998 IF (l_cal_dleg_discount <> 0)
999 THEN
1000
1001 l_cal_dleg_discount := GL_CURRENCY_API.convert_amount (
1002 x_from_currency => l_currency_code,
1003 x_to_currency => g_currency_code,
1004 x_conversion_date => sysdate,
1005 x_amount => l_cal_dleg_discount);
1006 END IF;
1007
1008
1009 IF l_debug_on THEN
1010 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_TOTAL_DISCOUNT_AMT After conversion ');
1011 WSH_DEBUG_SV.log(l_module_name,' l_cal_dleg_discount ',l_cal_dleg_discount);
1012 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
1013 END IF;
1014
1015 -- Get approved discount
1016 OPEN C_TOTAL_APPROVED_DISCOUNT;
1017 FETCH C_TOTAL_APPROVED_DISCOUNT INTO l_total_dleg_approved_discount,l_currency_code ;
1018 CLOSE C_TOTAL_APPROVED_DISCOUNT;
1019
1020
1021 l_total_dleg_approved_discount := nvl(l_total_dleg_approved_discount,0);
1022
1023
1024 IF l_debug_on THEN
1025 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_TOTAL_APPROVED_DISCOUNT ');
1026 WSH_DEBUG_SV.log(l_module_name,' l_total_dleg_approved_discount ',l_total_dleg_approved_discount);
1027 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
1028 END IF;
1029
1030 IF (l_total_dleg_approved_discount <> 0)
1031 THEN
1032
1033 l_total_dleg_approved_discount := GL_CURRENCY_API.convert_amount (
1034 x_from_currency => l_currency_code,
1035 x_to_currency => g_currency_code,
1036 x_conversion_date => sysdate,
1037 x_amount => l_total_dleg_approved_discount);
1038 END IF;
1039
1040
1041 IF l_debug_on THEN
1042 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_TOTAL_APPROVED_DISCOUNT ');
1043 WSH_DEBUG_SV.log(l_module_name,' l_total_dleg_approved_discount ',l_total_dleg_approved_discount);
1044 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
1045 END IF;
1046
1047 IF (l_cal_dleg_discount <> 0)
1048 THEN
1049 -- Prorated Discount calculation
1050 l_prorated_detail_discount := nvl(((l_total_dleg_approved_discount * l_cal_detail_discount) / l_cal_dleg_discount),0);
1051 END IF;
1052 --} -- Discount Calculation
1053
1054 --{ Surcharge Calculation
1055
1056 -- Get detail discount
1057 OPEN C_DETAIL_SURCHARGE_AMOUNT;
1058 FETCH C_DETAIL_SURCHARGE_AMOUNT INTO l_cal_detail_charge,l_currency_code;
1059 CLOSE C_DETAIL_SURCHARGE_AMOUNT;
1060
1061
1062 IF l_debug_on THEN
1063 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DETAIL_SURCHARGE_AMOUNT ');
1064 WSH_DEBUG_SV.log(l_module_name,' l_cal_detail_charge ',l_cal_detail_charge);
1065 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
1066 END IF;
1067
1068 l_cal_detail_charge := nvl(l_cal_detail_charge,0);
1069
1070 IF (l_cal_detail_charge <> 0)
1071 THEN
1072
1073 l_cal_detail_charge := GL_CURRENCY_API.convert_amount (
1074 x_from_currency => l_currency_code,
1075 x_to_currency => g_currency_code,
1076 x_conversion_date => sysdate,
1077 x_amount => l_cal_detail_charge);
1078 END IF;
1079
1080 IF l_debug_on THEN
1081 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DETAIL_SURCHARGE_AMOUNT After Conversion ');
1082 WSH_DEBUG_SV.log(l_module_name,' l_cal_detail_discount ',l_cal_detail_charge);
1083 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
1084 END IF;
1085
1086
1087 -- Get calcualted summary charge
1088 OPEN C_TOTAL_SURCHARGE_AMT;
1089 FETCH C_TOTAL_SURCHARGE_AMT INTO l_cal_dleg_charge,l_currency_code ;
1090 CLOSE C_TOTAL_SURCHARGE_AMT;
1091
1092 l_cal_dleg_charge := nvl(l_cal_dleg_charge,0);
1093
1094
1095 IF l_debug_on THEN
1096 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DETAIL_SURCHARGE_AMOUNT ');
1097 WSH_DEBUG_SV.log(l_module_name,' l_cal_dleg_charge ',l_cal_dleg_charge);
1098 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
1099 END IF;
1100
1101 IF (l_cal_dleg_charge <> 0)
1102 THEN
1103
1104 l_cal_dleg_charge := GL_CURRENCY_API.convert_amount (
1105 x_from_currency => l_currency_code,
1106 x_to_currency => g_currency_code,
1107 x_conversion_date => sysdate,
1108 x_amount => l_cal_dleg_charge);
1109
1110 END IF;
1111
1112 IF l_debug_on THEN
1113 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_DETAIL_SURCHARGE_AMOUNT ');
1114 WSH_DEBUG_SV.log(l_module_name,' l_cal_dleg_charge ',l_cal_dleg_charge);
1115 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
1116 END IF;
1117
1118 -- Get approved discount
1119 OPEN C_TOTAL_APPROVED_SURCHARGE;
1120 FETCH C_TOTAL_APPROVED_SURCHARGE INTO l_total_dleg_approved_charge,l_currency_code ;
1121 CLOSE C_TOTAL_APPROVED_SURCHARGE;
1122
1123 l_total_dleg_approved_charge := nvl(l_total_dleg_approved_charge,0);
1124
1125 IF l_debug_on THEN
1126 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_TOTAL_APPROVED_SURCHARGE ');
1127 WSH_DEBUG_SV.log(l_module_name,' l_total_dleg_approved_charge ',l_total_dleg_approved_charge);
1128 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
1129 END IF;
1130
1131 IF (l_total_dleg_approved_charge <> 0)
1132 THEN
1133
1134 l_total_dleg_approved_charge := GL_CURRENCY_API.convert_amount (
1135 x_from_currency => l_currency_code,
1136 x_to_currency => g_currency_code,
1137 x_conversion_date => sysdate,
1138 x_amount => l_total_dleg_approved_charge);
1139 END IF;
1140 IF l_debug_on THEN
1141 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_TOTAL_APPROVED_SURCHARGE ');
1142 WSH_DEBUG_SV.log(l_module_name,' l_total_dleg_approved_charge ',l_total_dleg_approved_charge);
1143 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',l_currency_code);
1144 END IF;
1145
1146 IF (l_cal_dleg_charge <> 0)
1147 THEN
1148 -- Prorated Discount calculation
1149 l_prorated_detail_charge := nvl(((l_total_dleg_approved_charge * l_cal_detail_charge) / l_cal_dleg_charge),0);
1150
1151 END IF;
1152
1153 --} -- Surcharge Calculation
1154 IF l_debug_on THEN
1155 WSH_DEBUG_SV.log(l_module_name,' Initial l_detail_approved_amount ',l_detail_approved_amount);
1156 WSH_DEBUG_SV.log(l_module_name,' l_prorated_detail_discount ',l_prorated_detail_discount);
1157 WSH_DEBUG_SV.log(l_module_name,' l_prorated_detail_charge ',l_prorated_detail_charge);
1158 END IF;
1159
1160 l_detail_approved_amount := nvl(l_detail_approved_amount,0) - nvl(l_prorated_detail_discount,0) +
1161 nvl(l_prorated_detail_charge,0);
1162
1163 --}
1164 END IF;
1165
1166
1167
1168
1169 IF l_debug_on THEN
1170 WSH_DEBUG_SV.log(l_module_name,' After Pro-Ration at Delivery Detail Level ');
1171 WSH_DEBUG_SV.log(l_module_name,' l_detail_approved_amount ',l_detail_approved_amount);
1172 WSH_DEBUG_SV.log(l_module_name,' l_currency_code ',g_currency_code);
1173 END IF;
1174
1175 IF l_debug_on THEN
1176 WSH_DEBUG_SV.pop(l_module_name);
1177 END IF;
1178
1179
1180 RETURN l_detail_approved_amount;
1181
1182
1183 END GET_FREIGHT_COST_LTL;
1184
1185
1186 PROCEDURE CALCULATE_FREIGHT_FOR_LTL (p_bol IN VARCHAR2,
1187 p_invoice_header_id IN NUMBER,
1188 x_return_status OUT NOCOPY VARCHAR2,
1189 x_msg_data OUT NOCOPY VARCHAR2,
1190 x_msg_count OUT NOCOPY NUMBER)
1191 IS
1192
1193 CURSOR C_SOURCE_LINES ( p_bol_no VARCHAR2 )
1194 IS
1195 SELECT DISTINCT WDD.SOURCE_LINE_ID,
1196 WDD.CURRENCY_CODE,
1197 WDD.WEIGHT_UOM_CODE,
1198 WDD.RCV_SHIPMENT_LINE_ID
1199 FROM
1200 WSH_DELIVERY_DETAILS WDD,
1201 WSH_DELIVERY_ASSIGNMENTS WDA,
1202 WSH_DELIVERY_LEGS WDL,
1203 WSH_DOCUMENT_INSTANCES WDI
1204 WHERE
1205 WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1206 AND (WDA.DELIVERY_ID = WDL.DELIVERY_ID OR WDA.PARENT_DELIVERY_ID = WDL.DELIVERY_ID)
1207 AND WDD.LINE_DIRECTION = 'I'
1208 AND WDI.ENTITY_ID = WDL.DELIVERY_LEG_ID
1209 AND WDI.ENTITY_NAME = 'WSH_DELIVERY_LEGS'
1210 AND WDI.DOCUMENT_TYPE = 'BOL'
1211 AND WDI.SEQUENCE_NUMBER = p_bol_no;
1212
1213 CURSOR C_SOURCE_LINE_BOLS ( p_source_line_id NUMBER,p_bol VARCHAR2)
1214 IS
1215 SELECT SUM(A.bol_flag) FROM (
1216 SELECT distinct wdl.delivery_leg_id,
1217 decode( (select distinct WDI.sequence_number from wsh_document_instances WDI,FTE_INVOICE_HEADERS FH
1218 where
1219 WDI.ENTITY_ID = DECODE(WT.MODE_OF_TRANSPORT,'LTL',WDL.DELIVERY_LEG_ID,'TRUCK',WT.TRIP_ID)
1220 AND WDI.ENTITY_NAME = DECODE(WT.MODE_OF_TRANSPORT,'LTL','WSH_DELIVERY_LEGS','TRUCK','WSH_TRIPS')
1221 AND WDI.SEQUENCE_NUMBER = FH.BOL
1222 AND FH.BILL_STATUS <> 'OBSOLETE'
1223 AND FH.BILL_STATUS = decode(FH.BOL,p_bol,FH.BILL_STATUS,'APPROVED')
1224 ),NULL,1,0) bol_flag
1225 FROM
1226 WSH_DELIVERY_DETAILS WDD,
1227 WSH_DELIVERY_ASSIGNMENTS WDA,
1228 WSH_DELIVERY_LEGS WDL,
1229 WSH_TRIP_STOPS WTS,
1230 WSH_TRIPS WT
1231 WHERE
1232 WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1233 AND (WDA.DELIVERY_ID = WDL.DELIVERY_ID OR WDA.PARENT_DELIVERY_ID = WDL.DELIVERY_ID)
1234 AND WDL.PARENT_DELIVERY_LEG_ID IS NULL
1235 AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID
1236 AND WT.TRIP_ID = WTS.TRIP_ID
1237 AND WDD.LINE_DIRECTION = 'I'
1238 AND WDD.SOURCE_LINE_ID = p_source_line_id
1239 ) A;
1240
1241
1242
1243 CURSOR C_DETAIL_INFO (p_source_line_id NUMBER)
1244 IS
1245 SELECT
1246 DISTINCT WDL.DELIVERY_LEG_ID,
1247 WDD.DELIVERY_DETAIL_ID,
1248 WDD.INVENTORY_ITEM_ID,
1249 WT.MODE_OF_TRANSPORT,
1250 WT.TRIP_ID,
1251 MIC.CATEGORY_ID AS COMMODITY_CATEGORY_ID,
1252 DECODE(WDA.PARENT_DELIVERY_DETAIL_ID,NULL,'Y','N') AS CONTAINER_FLAG,
1253 WDD.GROSS_WEIGHT,
1254 WDD.WEIGHT_UOM_CODE,
1255 WDD.CURRENCY_CODE,
1256 (SELECT WDI.SEQUENCE_NUMBER FROM WSH_DOCUMENT_INSTANCES WDI
1257 WHERE WDI.ENTITY_ID = DECODE(WT.MODE_OF_TRANSPORT,'LTL',NVL(WDL.PARENT_DELIVERY_LEG_ID,WDL.DELIVERY_LEG_ID),'TRUCK',WT.TRIP_ID)
1258 AND WDI.ENTITY_NAME = DECODE(WT.MODE_OF_TRANSPORT,'LTL','WSH_DELIVERY_LEGS','TRUCK','WSH_TRIPS')
1259 ) SEQUENCE_NUMBER
1260 FROM
1261 WSH_DELIVERY_DETAILS WDD,
1262 WSH_DELIVERY_ASSIGNMENTS WDA,
1263 WSH_DELIVERY_LEGS WDL,
1264 WSH_TRIP_STOPS WTS,
1265 WSH_TRIPS WT,
1266 MTL_ITEM_CATEGORIES MIC,
1267 MTL_CATEGORIES_KFV C,
1268 MTL_CATEGORY_SETS S
1269 WHERE
1270 S.STRUCTURE_ID = C.STRUCTURE_ID
1271 AND S.CATEGORY_SET_NAME ='WSH_COMMODITY_CODE'
1272 AND C.ENABLED_FLAG ='Y'
1273 AND C.CATEGORY_ID = MIC.CATEGORY_ID
1274 AND MIC.INVENTORY_ITEM_ID = WDD.INVENTORY_ITEM_ID
1275 AND MIC.ORGANIZATION_ID = WDD.ORG_ID
1276 AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1277 AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
1278 AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
1279 AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID
1280 AND WT.TRIP_ID = WTS.TRIP_ID
1281 AND WDD.LINE_DIRECTION = 'I'
1282 AND WDD.SOURCE_LINE_ID = p_source_line_id;
1283
1284 CURSOR C_VENDOR_INFO(p_source_line_id NUMBER) IS
1285 SELECT VENDOR_ID,SHIP_FROM_SITE_ID
1286 FROM WSH_DELIVERY_DETAILS
1287 WHERE SOURCE_LINE_ID = p_source_line_id
1288 AND ROWNUM <= 1;
1289
1290
1291
1292 l_all_approved_line_cnt NUMBER DEFAULT 0;
1293 l_approved_amount NUMBER DEFAULT 0;
1294 l_wFItemKey NUMBER;
1295 l_parameter_list wf_parameter_list_t;
1296 l_current_amount NUMBER;
1297 i NUMBER;
1298 j NUMBER;
1299
1300 l_return_status VARCHAR2(1);
1301 l_msg_data VARCHAR2(2000);
1302 l_msg_count NUMBER;
1303 l_vendor_id NUMBER;
1304 l_vendor_site_id NUMBER;
1305
1306 l_po_rcv_charges po_rcv_charges%rowtype;
1307
1308 l_debug_on BOOLEAN;
1309 --
1310 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'CALCULATE_FREIGHT_FOR_LTL';
1311
1312 l_bill_type VARCHAR2(10);
1313 l_inc_parent_bol VARCHAR2(1000);
1314
1315 l_bill_status VARCHAR2(30);
1316
1317 BEGIN
1318
1319 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1320
1321 IF l_debug_on IS NULL THEN
1322 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1323 END IF;
1324
1325 -- Initialize API return status to success
1326 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1327 x_msg_count := 0;
1328 x_msg_data := '';
1329
1330
1331 IF l_debug_on THEN
1332 WSH_DEBUG_SV.push(l_module_name);
1333 END IF;
1334
1335 IF l_debug_on THEN
1336
1337 WSH_DEBUG_SV.log(l_module_name,' *****************************.. ');
1338 WSH_DEBUG_SV.log(l_module_name,' Printing Parameters Below... ');
1339 WSH_DEBUG_SV.log(l_module_name,' *****************************.. ');
1340 WSH_DEBUG_SV.log(l_module_name,' p_bol ',p_bol);
1341 WSH_DEBUG_SV.log(l_module_name,' p_invoice_header_id ',p_invoice_header_id);
1342
1343 END IF;
1344
1345 SELECT BILL_STATUS INTO l_bill_status
1346 FROM FTE_INVOICE_HEADERS
1347 WHERE invoice_header_id = p_invoice_header_id;
1348
1349
1350 IF l_debug_on THEN
1351
1352 WSH_DEBUG_SV.log(l_module_name,' Invoice Bill number ', l_bill_status);
1353 END IF;
1354
1355 IF (l_bill_status <> 'APPROVED')
1356 THEN
1357 IF l_debug_on THEN
1358
1359 WSH_DEBUG_SV.log(l_module_name,' Bill approved returning back ');
1360 END IF;
1361 RETURN;
1362 END IF;
1363
1364
1365 i := 0;
1366
1367 FOR src_line in C_SOURCE_LINES(p_bol) LOOP
1368
1369 i := i + 1;
1370
1371 IF l_debug_on THEN
1372 WSH_DEBUG_SV.log(l_module_name,' LOOP CURSOR C_SOURCE_LINES ==> Iteration ',to_char(i));
1373 -- WSH_DEBUG_SV.log(l_module_name,' src_line.SOURCE_LINE_ID ',src_line.SOURCE_LINE_ID);
1374 WSH_DEBUG_SV.log(l_module_name,' src_line.RCV_SHIPMENT_LINE_ID ',src_line.RCV_SHIPMENT_LINE_ID);
1375 END IF;
1376
1377
1378 OPEN C_SOURCE_LINE_BOLS(src_line.SOURCE_LINE_ID,p_bol);
1379 FETCH C_SOURCE_LINE_BOLS INTO l_all_approved_line_cnt;
1380 CLOSE C_SOURCE_LINE_BOLS;
1381
1382 IF l_debug_on THEN
1383 WSH_DEBUG_SV.log(l_module_name,' CURSOR ss C_SOURCE_LINE_BOLS ==> Iteration ',i);
1384 WSH_DEBUG_SV.log(l_module_name,' l_all_approved_line_cnt ',l_all_approved_line_cnt);
1385 END IF;
1386
1387
1388 IF l_all_approved_line_cnt = 0 THEN
1389
1390 l_approved_amount := 0;
1391 j := 0;
1392
1393 FOR src_det in C_DETAIL_INFO(src_line.SOURCE_LINE_ID) LOOP
1394
1395 j := j + 1;
1396 l_current_amount := 0;
1397
1398 IF l_debug_on THEN
1399 WSH_DEBUG_SV.log(l_module_name,' LOOP CURSOR C_DETAIL_INFO ==> Iteration ',j);
1400 WSH_DEBUG_SV.log(l_module_name,' src_det.MODE_OF_TRANSPORT ',src_det.MODE_OF_TRANSPORT);
1401 END IF;
1402
1403
1404 IF src_det.MODE_OF_TRANSPORT = 'LTL' THEN
1405
1406 SELECT BILL_TYPE, BOL INTO l_bill_type, l_inc_parent_bol
1407 FROM FTE_INVOICE_HEADERS
1408 WHERE INVOICE_HEADER_ID = p_invoice_header_id;
1409
1410
1411 IF l_debug_on THEN
1412 WSH_DEBUG_SV.log(l_module_name,' BILL type ',l_bill_type);
1413 WSH_DEBUG_SV.log(l_module_name,' l_inc_parent_bol ',l_inc_parent_bol);
1414 WSH_DEBUG_SV.log(l_module_name,' src_det.SEQUENCE_NUMBER ',src_det.SEQUENCE_NUMBER);
1415 END IF;
1416 -- Commented by Suresh to verify the fix Bug#4996996
1417 --IF (l_bill_type = 'INC' AND
1418 -- l_inc_parent_bol = src_det.SEQUENCE_NUMBER)
1419 --THEN
1420
1421 l_current_amount := GET_FREIGHT_COST_LTL
1422 (p_delivery_leg_id => src_det.DELIVERY_LEG_ID,
1423 p_delivery_detail_id => src_det.DELIVERY_DETAIL_ID ,
1424 p_commodity_category_id => src_det.COMMODITY_CATEGORY_ID,
1425 p_bol => src_det.SEQUENCE_NUMBER,
1426 g_currency_code => src_det.CURRENCY_CODE,
1427 p_invoice_header_id => p_invoice_header_id);
1428
1429
1430 IF l_debug_on THEN
1431 WSH_DEBUG_SV.log(l_module_name,' LTL approved_amount For Delivery Detail ',src_det.DELIVERY_DETAIL_ID);
1432 WSH_DEBUG_SV.log(l_module_name,' l_current_amount ',l_current_amount);
1433 END IF;
1434 --END IF;
1435
1436 ELSIF src_det.MODE_OF_TRANSPORT = 'TRUCK' THEN
1437
1438 l_current_amount := GET_FREIGHT_COST_TRUCK
1439 (p_trip_id => src_det.TRIP_ID ,
1440 p_delivery_detail_id => src_det.DELIVERY_DETAIL_ID,
1441 p_inventory_item_id => src_det.INVENTORY_ITEM_ID,
1442 p_delivery_leg_id => src_det.DELIVERY_LEG_ID ,
1443 p_bol => src_det.SEQUENCE_NUMBER,
1444 p_container_flag => src_det.CONTAINER_FLAG,
1445 p_gross_weight => src_det.GROSS_WEIGHT,
1446 p_weight_uom => src_det.WEIGHT_UOM_CODE,
1447 g_currency_code => src_det.CURRENCY_CODE);
1448
1449 IF l_debug_on THEN
1450 WSH_DEBUG_SV.log(l_module_name,' TRUCK approved_amount For Delivery Detail ',src_det.DELIVERY_DETAIL_ID);
1451 WSH_DEBUG_SV.log(l_module_name,' l_current_amount ',l_current_amount);
1452 END IF;
1453
1454
1455 END IF;
1456
1457 l_approved_amount := l_approved_amount + nvl(l_current_amount,0);
1458
1459 END LOOP; -- END of C_DETAIL_INFO
1460
1461 IF l_debug_on THEN
1462 -- WSH_DEBUG_SV.log(l_module_name,' TOTAL Approved Amount For RCV LINE ',src_line.RCV_SHIPMENT_LINE_ID);
1463 WSH_DEBUG_SV.log(l_module_name,' l_approved_amount ',l_approved_amount);
1464 END IF;
1465
1466
1467
1468
1469 OPEN C_VENDOR_INFO(src_line.SOURCE_LINE_ID);
1470 FETCH C_VENDOR_INFO into l_vendor_id,l_vendor_site_id;
1471 CLOSE C_VENDOR_INFO;
1472
1473
1474 IF l_debug_on THEN
1475 WSH_DEBUG_SV.log(l_module_name,' ************ API Parameters ********************* ');
1476 -- WSH_DEBUG_SV.log(l_module_name,' src_line.SOURCE_LINE_ID ',src_line.SOURCE_LINE_ID);
1477 WSH_DEBUG_SV.log(l_module_name,' src_line.RCV_SHIPMENT_LINE_ID ',src_line.RCV_SHIPMENT_LINE_ID);
1478 WSH_DEBUG_SV.log(l_module_name,' l_approved_amount ',l_approved_amount);
1479 WSH_DEBUG_SV.log(l_module_name,' src_line.CURRENCY_CODE ',src_line.CURRENCY_CODE);
1480 WSH_DEBUG_SV.log(l_module_name,' l_vendor_id ',l_vendor_id);
1481 WSH_DEBUG_SV.log(l_module_name,' l_vendor_site_id ',l_vendor_site_id);
1482 WSH_DEBUG_SV.log(l_module_name,' *************************************************** ');
1483 END IF;
1484
1485
1486 IF l_approved_amount <> 0 THEN
1487
1488 l_po_rcv_charges.SHIPMENT_LINE_ID := src_line.RCV_SHIPMENT_LINE_ID;
1489 l_po_rcv_charges.CURRENCY_CODE := src_line.CURRENCY_CODE;
1490 l_po_rcv_charges.ACTUAL_AMOUNT := l_approved_amount;
1491 l_po_rcv_charges.VENDOR_ID := l_vendor_id;
1492 l_po_rcv_charges.VENDOR_SITE_ID := l_vendor_site_id;
1493
1494
1495 PO_CHARGES_GRP.Capture_FTE_Actual_Charges(
1496 p_api_version => 1.0,
1497 p_init_msg_list => FND_API.G_FALSE,
1498 x_return_status => l_return_status,
1499 x_msg_count => l_msg_count,
1500 x_msg_data => l_msg_data,
1501 p_fte_actual_charge => l_po_rcv_charges);
1502
1503 -- END IF;
1504
1505
1506 IF l_debug_on THEN
1507 WSH_DEBUG_SV.log(l_module_name,' Called PO_CHARGES_GRP.Capture_FTE_Actual_Charges');
1508 WSH_DEBUG_SV.log(l_module_name,' l_return_status ',l_return_status);
1509 END IF;
1510
1511
1512 ELSE
1513 IF l_debug_on THEN
1514 WSH_DEBUG_SV.log(l_module_name,' Approved Amount is Zero , PO API Not called ');
1515 END IF;
1516
1517 END IF;
1518
1519
1520
1521
1522 END IF; -- End of Count 0 Check
1523
1524 END LOOP; -- End of C_SOURCE_LINES
1525
1526 IF l_debug_on THEN
1527 WSH_DEBUG_SV.pop(l_module_name);
1528 END IF;
1529
1530 EXCEPTION
1531 WHEN OTHERS THEN
1532 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1533 x_msg_data := ' Error Message = '||SQLERRM||' Code = '||SQLCODE;
1534
1535 IF l_debug_on THEN
1536 WSH_DEBUG_SV.log(l_module_name,x_msg_data);
1537 WSH_DEBUG_SV.pop(l_module_name);
1538 END IF;
1539
1540
1541
1542 END CALCULATE_FREIGHT_FOR_LTL;
1543
1544
1545 PROCEDURE CALCULATE_FREIGHT_FOR_TRUCK(p_bol IN VARCHAR2,
1546 p_invoice_header_id IN NUMBER,
1547 x_return_status OUT NOCOPY VARCHAR2,
1548 x_msg_data OUT NOCOPY VARCHAR2,
1549 x_msg_count OUT NOCOPY NUMBER)
1550 IS
1551
1552 CURSOR C_SOURCE_LINES ( p_bol_no VARCHAR2 )
1553 IS
1554 SELECT DISTINCT WDD.SOURCE_LINE_ID,
1555 WDD.CURRENCY_CODE,
1556 WDD.WEIGHT_UOM_CODE
1557 FROM
1558 WSH_DELIVERY_DETAILS WDD,
1559 WSH_DELIVERY_ASSIGNMENTS WDA,
1560 WSH_DELIVERY_LEGS WDL,
1561 WSH_TRIP_STOPS WTS,
1562 WSH_TRIPS WT,
1563 WSH_DOCUMENT_INSTANCES WDI
1564 WHERE
1565 WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1566 AND WDD.LINE_DIRECTION = 'I'
1567 AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
1568 AND WTS.STOP_ID = WDL.PICK_UP_STOP_ID
1569 AND WT.TRIP_ID = WTS.TRIP_ID
1570 AND WDI.ENTITY_ID = WT.TRIP_ID
1571 AND WDI.ENTITY_NAME = 'WSH_TRIPS'
1572 AND WDI.DOCUMENT_TYPE = 'MBOL'
1573 AND WDI.SEQUENCE_NUMBER = p_bol_no;
1574
1575
1576 CURSOR C_SOURCE_LINE_BOLS ( p_source_line_id NUMBER,p_bol VARCHAR2)
1577 IS
1578 SELECT SUM(A.bol_flag) FROM (
1579 SELECT distinct wdl.delivery_leg_id,
1580 decode( (select WDI.sequence_number from wsh_document_instances WDI,FTE_INVOICE_HEADERS FH
1581 where
1582 WDI.ENTITY_ID = DECODE(WT.MODE_OF_TRANSPORT,'LTL',WDL.DELIVERY_LEG_ID,'TRUCK',WT.TRIP_ID)
1583 AND WDI.ENTITY_NAME = DECODE(WT.MODE_OF_TRANSPORT,'LTL','WSH_DELIVERY_LEGS','TRUCK','WSH_TRIPS')
1584 AND WDI.SEQUENCE_NUMBER = FH.BOL
1585 AND FH.BILL_STATUS = decode(FH.BOL,p_bol,FH.BILL_STATUS,'APPROVED')
1586 ),NULL,1,0) bol_flag
1587 FROM
1588 WSH_DELIVERY_DETAILS WDD,
1589 WSH_DELIVERY_ASSIGNMENTS WDA,
1590 WSH_DELIVERY_LEGS WDL,
1591 WSH_TRIP_STOPS WTS,
1592 WSH_TRIPS WT
1593 WHERE
1594 WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1595 AND (WDA.DELIVERY_ID = WDL.DELIVERY_ID OR WDA.PARENT_DELIVERY_ID = WDL.DELIVERY_ID)
1596 AND WDL.PARENT_DELIVERY_LEG_ID IS NULL
1597 AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID
1598 AND WT.TRIP_ID = WTS.TRIP_ID
1599 AND WDD.LINE_DIRECTION = 'I'
1600 AND WDD.SOURCE_LINE_ID = p_source_line_id
1601 ) A;
1602
1603
1604
1605 CURSOR C_DETAIL_INFO (p_source_line_id NUMBER)
1606 IS
1607 SELECT
1608 DISTINCT WDL.DELIVERY_LEG_ID,
1609 WDD.DELIVERY_DETAIL_ID,
1610 WDD.INVENTORY_ITEM_ID,
1611 WT.MODE_OF_TRANSPORT,
1612 WT.TRIP_ID,
1613 MIC.CATEGORY_ID AS COMMODITY_CATEGORY_ID,
1614 DECODE(WDA.PARENT_DELIVERY_DETAIL_ID,NULL,'Y','N') AS CONTAINER_FLAG,
1615 WDD.GROSS_WEIGHT,
1616 WDD.WEIGHT_UOM_CODE,
1617 WDD.CURRENCY_CODE,
1618 (SELECT WDI.SEQUENCE_NUMBER FROM WSH_DOCUMENT_INSTANCES WDI
1619 WHERE WDI.ENTITY_ID = DECODE(WT.MODE_OF_TRANSPORT,'LTL',NVL(WDL.PARENT_DELIVERY_LEG_ID,WDL.DELIVERY_LEG_ID),'TRUCK',WT.TRIP_ID)
1620 AND WDI.ENTITY_NAME = DECODE(WT.MODE_OF_TRANSPORT,'LTL','WSH_DELIVERY_LEGS','TRUCK','WSH_TRIPS')
1621 ) SEQUENCE_NUMBER
1622 FROM
1623 WSH_DELIVERY_DETAILS WDD,
1624 WSH_DELIVERY_ASSIGNMENTS WDA,
1625 WSH_DELIVERY_LEGS WDL,
1626 WSH_TRIP_STOPS WTS,
1627 WSH_TRIPS WT,
1628 MTL_ITEM_CATEGORIES MIC,
1629 MTL_CATEGORIES_KFV C,
1630 MTL_CATEGORY_SETS S
1631 WHERE
1632 S.STRUCTURE_ID = C.STRUCTURE_ID
1633 AND S.CATEGORY_SET_NAME ='WSH_COMMODITY_CODE'
1634 AND C.ENABLED_FLAG ='Y'
1635 AND C.CATEGORY_ID = MIC.CATEGORY_ID
1636 AND MIC.INVENTORY_ITEM_ID = WDD.INVENTORY_ITEM_ID
1637 AND MIC.ORGANIZATION_ID = WDD.ORG_ID
1638 AND WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
1639 AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
1640 AND WDA.DELIVERY_ID = WDL.DELIVERY_ID
1641 AND WDL.PICK_UP_STOP_ID = WTS.STOP_ID
1642 AND WT.TRIP_ID = WTS.TRIP_ID
1643 AND WDD.LINE_DIRECTION = 'I'
1644 AND WDD.SOURCE_LINE_ID = p_source_line_id;
1645
1646
1647 CURSOR C_VENDOR_INFO(p_source_line_id NUMBER) IS
1648 SELECT VENDOR_ID,SHIP_FROM_SITE_ID
1649 FROM WSH_DELIVERY_DETAILS
1650 WHERE SOURCE_LINE_ID = p_source_line_id
1651 AND ROWNUM <= 1;
1652
1653
1654
1655 l_all_approved_line_cnt NUMBER DEFAULT 0;
1656 l_approved_amount NUMBER DEFAULT 0;
1657 l_wFItemKey NUMBER;
1658 l_parameter_list wf_parameter_list_t;
1659
1660 l_current_amount NUMBER;
1661 i NUMBER;
1662 j NUMBER;
1663
1664 l_return_status VARCHAR2(1);
1665 l_msg_data VARCHAR2(2000);
1666 l_msg_count NUMBER;
1667 l_vendor_id NUMBER;
1668 l_vendor_site_id NUMBER;
1669
1670 l_po_rcv_charges po_rcv_charges%rowtype;
1671
1672 l_debug_on BOOLEAN;
1673 --
1674 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'CALCULATE_FREIGHT_FOR_TRUCK';
1675
1676 l_inc_parent_bol VARCHAR2(2000);
1677 l_bill_type VARCHAR2(30);
1678
1679 BEGIN
1680
1681
1682 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1683
1684 IF l_debug_on IS NULL THEN
1685 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1686 END IF;
1687
1688 -- Initialize API return status to success
1689 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1690 x_msg_count := 0;
1691 x_msg_data := '';
1692
1693
1694 IF l_debug_on THEN
1695 WSH_DEBUG_SV.push(l_module_name);
1696 END IF;
1697
1698 IF l_debug_on THEN
1699
1700 WSH_DEBUG_SV.log(l_module_name,' *****************************.. ');
1701 WSH_DEBUG_SV.log(l_module_name,' Printing Parameters Below...... ');
1702 WSH_DEBUG_SV.log(l_module_name,' *****************************.. ');
1703 WSH_DEBUG_SV.log(l_module_name,' p_bol ',p_bol);
1704 WSH_DEBUG_SV.log(l_module_name,' p_invoice_header_id ',p_invoice_header_id);
1705
1706 END IF;
1707
1708
1709 i := 0;
1710
1711 FOR src_line in C_SOURCE_LINES(p_bol) LOOP
1712
1713 i := i + 1;
1714
1715 IF l_debug_on THEN
1716 WSH_DEBUG_SV.log(l_module_name,' LOOP CURSOR C_SOURCE_LINES ==> Iteration '||i);
1717 WSH_DEBUG_SV.log(l_module_name,' src_line.SOURCE_LINE_ID ',src_line.SOURCE_LINE_ID);
1718 END IF;
1719
1720
1721 OPEN C_SOURCE_LINE_BOLS(src_line.SOURCE_LINE_ID,p_bol);
1722 FETCH C_SOURCE_LINE_BOLS INTO l_all_approved_line_cnt;
1723 CLOSE C_SOURCE_LINE_BOLS;
1724
1725 IF l_debug_on THEN
1726 WSH_DEBUG_SV.log(l_module_name,' CURSOR C_SOURCE_LINE_BOLS ==> Iteration ',i);
1727 WSH_DEBUG_SV.log(l_module_name,' l_all_approved_line_cnt ',l_all_approved_line_cnt);
1728 END IF;
1729
1730
1731 IF l_all_approved_line_cnt = 0 THEN
1732
1733 l_approved_amount := 0;
1734 j := 0;
1735
1736
1737 FOR src_det in C_DETAIL_INFO(src_line.SOURCE_LINE_ID) LOOP
1738
1739 j := j + 1;
1740 l_current_amount := 0;
1741
1742 IF l_debug_on THEN
1743 WSH_DEBUG_SV.log(l_module_name,' LOOP CURSOR C_DETAIL_INFO ==> Iteration ',j);
1744 WSH_DEBUG_SV.log(l_module_name,' src_det.MODE_OF_TRANSPORT ',src_det.MODE_OF_TRANSPORT);
1745 END IF;
1746
1747
1748
1749 IF src_det.MODE_OF_TRANSPORT = 'LTL'
1750 THEN
1751
1752 SELECT BILL_TYPE, BOL INTO l_bill_type, l_inc_parent_bol
1753 FROM FTE_INVOICE_HEADERS
1754 WHERE INVOICE_HEADER_ID = p_invoice_header_id;
1755
1756
1757 IF l_debug_on THEN
1758 WSH_DEBUG_SV.log(l_module_name,' BILL type ',l_bill_type);
1759 WSH_DEBUG_SV.log(l_module_name,' l_inc_parent_bol ',l_inc_parent_bol);
1760 WSH_DEBUG_SV.log(l_module_name,' src_det.SEQUENCE_NUMBER ',src_det.SEQUENCE_NUMBER);
1761 END IF;
1762
1763 IF (l_bill_type = 'INC' AND
1764 l_inc_parent_bol = src_det.SEQUENCE_NUMBER)
1765 THEN
1766
1767 l_current_amount := GET_FREIGHT_COST_LTL
1768 (p_delivery_leg_id => src_det.DELIVERY_LEG_ID,
1769 p_delivery_detail_id => src_det.DELIVERY_DETAIL_ID ,
1770 p_commodity_category_id => src_det.COMMODITY_CATEGORY_ID,
1771 p_bol => src_det.SEQUENCE_NUMBER,
1772 g_currency_code => src_det.CURRENCY_CODE,
1773 p_invoice_header_id => p_invoice_header_id);
1774
1775
1776 IF l_debug_on THEN
1777 WSH_DEBUG_SV.log(l_module_name,' LTL approved_amount For Delivery Detail ',src_det.DELIVERY_DETAIL_ID);
1778 WSH_DEBUG_SV.log(l_module_name,' l_current_amount ',l_current_amount);
1779 END IF;
1780 END IF;
1781
1782 ELSIF src_det.MODE_OF_TRANSPORT = 'TRUCK' THEN
1783
1784 l_current_amount := GET_FREIGHT_COST_TRUCK
1785 (p_trip_id => src_det.TRIP_ID ,
1786 p_delivery_detail_id => src_det.DELIVERY_DETAIL_ID,
1787 p_inventory_item_id => src_det.INVENTORY_ITEM_ID,
1788 p_delivery_leg_id => src_det.DELIVERY_LEG_ID ,
1789 p_bol => src_det.SEQUENCE_NUMBER,
1790 p_container_flag => src_det.CONTAINER_FLAG,
1791 p_gross_weight => src_det.GROSS_WEIGHT,
1792 p_weight_uom => src_det.WEIGHT_UOM_CODE,
1793 g_currency_code => src_det.CURRENCY_CODE);
1794
1795 IF l_debug_on THEN
1796 WSH_DEBUG_SV.log(l_module_name,' TRUCK approved_amount For Delivery Detail ',src_det.DELIVERY_DETAIL_ID);
1797 WSH_DEBUG_SV.log(l_module_name,' l_current_amount ',l_current_amount);
1798 END IF;
1799
1800 END IF;
1801
1802 l_approved_amount := l_approved_amount + nvl(l_current_amount,0);
1803
1804 END LOOP; -- END of C_DETAIL_INFO
1805
1806
1807
1808 OPEN C_VENDOR_INFO(src_line.SOURCE_LINE_ID);
1809 FETCH C_VENDOR_INFO into l_vendor_id,l_vendor_site_id;
1810 CLOSE C_VENDOR_INFO;
1811
1812
1813 IF l_debug_on THEN
1814 WSH_DEBUG_SV.log(l_module_name,' ************ API Parameters ********************* ');
1815 WSH_DEBUG_SV.log(l_module_name,' src_line.SOURCE_LINE_ID ',src_line.SOURCE_LINE_ID);
1816 WSH_DEBUG_SV.log(l_module_name,' l_approved_amount ',l_approved_amount);
1817 WSH_DEBUG_SV.log(l_module_name,' src_line.CURRENCY_CODE ',src_line.CURRENCY_CODE);
1818 WSH_DEBUG_SV.log(l_module_name,' l_vendor_id ',l_vendor_id);
1819 WSH_DEBUG_SV.log(l_module_name,' l_vendor_site_id ',l_vendor_site_id);
1820 WSH_DEBUG_SV.log(l_module_name,' *************************************************** ');
1821 END IF;
1822
1823
1824 IF l_approved_amount <> 0 THEN
1825
1826 l_po_rcv_charges.SHIPMENT_LINE_ID := src_line.SOURCE_LINE_ID;
1827 l_po_rcv_charges.CURRENCY_CODE := src_line.CURRENCY_CODE;
1828 l_po_rcv_charges.ACTUAL_AMOUNT := l_approved_amount;
1829 l_po_rcv_charges.VENDOR_ID := l_vendor_id;
1830 l_po_rcv_charges.VENDOR_SITE_ID := l_vendor_site_id;
1831
1832
1833 PO_CHARGES_GRP.Capture_FTE_Actual_Charges(
1834 p_api_version => 1.0,
1835 p_init_msg_list => FND_API.G_FALSE,
1836 x_return_status => l_return_status,
1837 x_msg_count => l_msg_count,
1838 x_msg_data => l_msg_data,
1839 p_fte_actual_charge => l_po_rcv_charges);
1840
1841 IF l_debug_on THEN
1842 WSH_DEBUG_SV.log(l_module_name,' Called PO_CHARGES_GRP.Capture_FTE_Actual_Charges');
1843 WSH_DEBUG_SV.log(l_module_name,' l_return_status ',l_return_status);
1844 END IF;
1845
1846
1847 ELSE
1848 IF l_debug_on THEN
1849 WSH_DEBUG_SV.log(l_module_name,' Approved Amount is Zero , PO API Not called ');
1850 END IF;
1851
1852 END IF;
1853
1854
1855
1856 END IF; -- End of Count 0 Check
1857
1858 END LOOP; -- End of C_SOURCE_LINES
1859
1860 IF l_debug_on THEN
1861 WSH_DEBUG_SV.pop(l_module_name);
1862 END IF;
1863
1864
1865 EXCEPTION
1866
1867 WHEN OTHERS THEN
1868 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1869 x_msg_data := ' Error Message = '||SQLERRM||' Code = '||SQLCODE;
1870
1871 IF l_debug_on THEN
1872 WSH_DEBUG_SV.log(l_module_name,x_msg_data);
1873 WSH_DEBUG_SV.pop(l_module_name);
1874 END IF;
1875
1876
1877
1878 END CALCULATE_FREIGHT_FOR_TRUCK;
1879
1880
1881 PROCEDURE CALCULATE_PO_FREIGHT(p_bol_no IN VARCHAR2,
1882 p_inv_header_id IN NUMBER,
1883 p_mode_of_transport IN VARCHAR2)
1884 IS
1885
1886 l_file_name VARCHAR2(300);
1887 l_return_status VARCHAR2(1);
1888 l_msg_count NUMBER;
1889 l_msg_data VARCHAR2(2000);
1890
1891 l_debug_on BOOLEAN;
1892
1893 BEGIN
1894
1895
1896 -- Test Code
1897 wsh_debug_interface.g_Debug := TRUE;
1898 WSH_DEBUG_SV.start_debugger
1899 (x_file_name => l_file_name,
1900 x_return_status => l_return_status,
1901 x_msg_count => l_msg_count,
1902 x_msg_data => l_msg_data);
1903
1904 -- insert into dbg_value values(' File name = '||l_file_name);
1905
1906 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1907
1908 IF l_debug_on IS NULL THEN
1909 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1910 END IF;
1911
1912
1913 IF p_mode_of_transport = 'LTL' THEN
1914
1915 CALCULATE_FREIGHT_FOR_LTL (p_bol => p_bol_no,
1916 p_invoice_header_id => p_inv_header_id,
1917 x_return_status => l_return_status,
1918 x_msg_data => l_msg_data,
1919 x_msg_count => l_msg_count);
1920
1921 ELSIF p_mode_of_transport = 'TL' THEN
1922
1923 CALCULATE_FREIGHT_FOR_TRUCK (p_bol => p_bol_no,
1924 p_invoice_header_id => p_inv_header_id,
1925 x_return_status => l_return_status,
1926 x_msg_data => l_msg_data,
1927 x_msg_count => l_msg_count) ;
1928
1929
1930 END IF;
1931
1932 WSH_DEBUG_SV.stop_debugger;
1933
1934 END CALCULATE_PO_FREIGHT;
1935
1936 --Commenting the code for Obsoletion of DBI APIs in 12.2
1937 /*PROCEDURE callDBI
1938 (p_invoice_header_id IN NUMBER,
1939 p_dml_type IN VARCHAR2,
1940 p_return_status OUT NOCOPY VARCHAR2) IS
1941
1942 v_tab ISC_DBI_CHANGE_LOG_PKG.log_tab_type;
1943
1944 BEGIN
1945 v_tab(1) := p_invoice_header_id;
1946
1947 ISC_DBI_CHANGE_LOG_PKG.UPDATE_FTE_INVOICE_LOG(v_tab, p_dml_type, p_return_status);
1948 NULL;
1949 END;*/
1950
1951
1952 PROCEDURE Get_Legal_Entity(p_org_id IN NUMBER,
1953 x_legal_entity_id OUT NOCOPY NUMBER,
1954 x_return_status OUT NOCOPY VARCHAR2,
1955 x_msg_data OUT NOCOPY VARCHAR2,
1956 x_msg_count OUT NOCOPY NUMBER)
1957 IS
1958 /*BEGIN
1959 x_return_status := 'S';
1960 x_msg_data :=' ';
1961 x_msg_count := 0; */
1962
1963 l_inv_org_rec XLE_BUSINESSINFO_GRP.Inv_Org_Rec_Type;
1964
1965 --
1966 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
1967 --
1968 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'Get_Legal_Entity';
1969
1970 l_return_status VARCHAR2(32767);
1971 l_msg_count NUMBER;
1972 l_msg_data VARCHAR2(32767);
1973 l_number_of_warnings NUMBER;
1974 l_number_of_errors NUMBER;
1975
1976
1977 BEGIN
1978
1979 NULL;
1980
1981 SAVEPOINT GET_LEGAL_ENTITY_PUB;
1982
1983 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1984 x_msg_data := '';
1985 x_msg_count := 0;
1986 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1987 l_number_of_warnings := 0;
1988 l_number_of_errors := 0;
1989
1990
1991
1992 FND_MSG_PUB.initialize;
1993
1994 IF l_debug_on THEN
1995 wsh_debug_sv.push(l_module_name);
1996 END IF;
1997
1998
1999 XLE_BUSINESSINFO_GRP.Get_InvOrg_Info(
2000 x_return_status => l_return_status,
2001 x_msg_data => l_msg_data,
2002 P_InvOrg_ID => p_org_id,
2003 P_Le_ID => NULL,
2004 P_Party_ID => NULL,
2005 x_Inv_Le_info => l_inv_org_rec
2006 );
2007
2008 x_legal_entity_id := l_inv_org_rec(1).legal_entity_id;
2009
2010 wsh_util_core.api_post_call(
2011 p_return_status =>l_return_status,
2012 x_num_warnings =>l_number_of_warnings,
2013 x_num_errors =>l_number_of_errors,
2014 p_msg_data =>l_msg_data);
2015
2016
2017 IF l_number_of_errors > 0 THEN
2018 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2019 ELSIF l_number_of_warnings > 0 THEN
2020 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
2021 ELSE
2022 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2023 END IF;
2024
2025 FND_MSG_PUB.Count_And_Get
2026 (
2027 p_count => x_msg_count,
2028 p_data => x_msg_data,
2029 p_encoded => FND_API.G_FALSE
2030 );
2031
2032 IF l_debug_on THEN
2033 WSH_DEBUG_SV.pop(l_module_name);
2034 END IF;
2035
2036 EXCEPTION
2037
2038 WHEN FND_API.G_EXC_ERROR THEN
2039 ROLLBACK TO GET_LEGAL_ENTITY_PUB;
2040 x_return_status := FND_API.G_RET_STS_ERROR;
2041 FND_MSG_PUB.Count_And_Get
2042 (
2043 p_count => x_msg_count,
2044 p_data => x_msg_data,
2045 p_encoded => FND_API.G_FALSE
2046 );
2047
2048 IF l_debug_on THEN
2049 WSH_DEBUG_SV.pop(l_module_name);
2050 END IF;
2051
2052 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2053
2054 ROLLBACK TO GET_LEGAL_ENTITY_PUB;
2055 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2056 FND_MSG_PUB.Count_And_Get
2057 (
2058 p_count => x_msg_count,
2059 p_data => x_msg_data,
2060 p_encoded => FND_API.G_FALSE
2061 );
2062
2063 IF l_debug_on THEN
2064 WSH_DEBUG_SV.pop(l_module_name);
2065 END IF;
2066
2067 WHEN OTHERS THEN
2068 ROLLBACK TO GET_LEGAL_ENTITY_PUB;
2069 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2070 FND_MSG_PUB.Count_And_Get
2071 (
2072 p_count => x_msg_count,
2073 p_data => x_msg_data,
2074 p_encoded => FND_API.G_FALSE
2075 );
2076 IF l_debug_on THEN
2077 WSH_DEBUG_SV.pop(l_module_name);
2078 END IF;
2079
2080
2081 END Get_Legal_Entity;
2082
2083
2084 -- ------------------------------------------------------------------------------- --
2085 -- PROCEDURE --
2086 -- NAME: Update_Status --
2087 -- TYPE: PROCEDURE --
2088 -- PARAMETERS (IN): itemtype VARCHAR2 (wf item type's internal name) --
2089 -- itemkey VARCHAR2 (wf block instance label) --
2090 -- actid NUMBER (wf function/activity id) --
2091 -- funcmode VARCHAR (execution mode) --
2092 -- --
2093 -- PARAMETERS (OUT): resultout VARCHAR2 (completion status) --
2094 -- PARAMETERS (IN OUT): none --
2095 -- RETURN: none --
2096 -- DESCRIPTION: This procedure will update Invoice Payment Status In FTE --
2097 -- with the payment status retrieved from account payables. --
2098 -- This is procedure is invoked from PAYMENT_STATUS_UPDATE --
2099 -- Process of FTEPSUPD workflow item type. --
2100 -- CHANGE CONTROL LOG --
2101 -- ------------------ --
2102 -- --
2103 -- DATE VERSION BY BUG DESCRIPTION --
2104 -- ---------- ------- -------- ------- --------------------------------------- --
2105 -- 2003 11.5.1 SAMUTHUK Created --
2106 -- --
2107 -- ------------------------------------------------------------------------------- --
2108
2109
2110 PROCEDURE Update_Status(itemtype IN VARCHAR2,
2111 itemkey IN VARCHAR2,
2112 actid IN NUMBER,
2113 funcmode IN VARCHAR2,
2114 resultout OUT NOCOPY VARCHAR2) IS
2115
2116
2117 CURSOR C_INVOICE(p_check_id NUMBER) IS
2118 SELECT
2119 INV.INVOICE_NUM,
2120 INV.PAYMENT_STATUS_FLAG
2121 FROM
2122 AP_INVOICES_ALL INV,
2123 AP_INVOICE_PAYMENTS_ALL PAY,
2124 AP_CHECKS_ALL CHK
2125 WHERE
2126 PAY.INVOICE_ID = INV.INVOICE_ID
2127 AND CHK.CHECK_ID = PAY.CHECK_ID
2128 AND PAY.CHECK_ID = p_check_id;
2129
2130
2131
2132 l_check_id NUMBER;
2133 l_invoice_number VARCHAR2(30);
2134 l_payment_status VARCHAR2(1);
2135 l_error VARCHAR2(100);
2136 l_invoice_id NUMBER;
2137
2138 l_api_name CONSTANT VARCHAR2(30) := 'Update_Status';
2139 l_api_version CONSTANT NUMBER := 1.0;
2140 l_debug_on CONSTANT BOOLEAN := WSH_DEBUG_SV.is_debug_enabled;
2141
2142 l_userName VARCHAR2(100);
2143 returnStatus VARCHAR2(10);
2144
2145 l_role_name VARCHAR2(100);
2146 l_display_name VARCHAR2(1000);
2147 hz_party_display_name varchar2(1000);
2148 l_email_address VARCHAR2(1000);
2149 l_notif VARCHAR2(1000);
2150 l_lang VARCHAR2(100);
2151 l_ter VARCHAR2(1000);
2152
2153
2154
2155 BEGIN
2156 IF funcmode = 'RUN' THEN
2157
2158 IF l_debug_on THEN
2159 WSH_DEBUG_SV.push(l_api_name);
2160 END IF;
2161
2162
2163 l_payment_status := 'N';
2164
2165 WF_DIRECTORY.GETROLEINFO (
2166 role => FND_GLOBAL.USER_NAME,
2167 display_name => hz_party_display_name,
2168 email_address => l_email_address,
2169 notification_preference => l_notif,
2170 language => l_lang,
2171 territory => l_ter);
2172
2173 IF (hz_party_display_name IS NOT NULL) THEN
2174 wf_engine.SetItemOwner(itemtype,itemkey,FND_GLOBAL.USER_NAME);
2175 END IF;
2176
2177 l_check_id := wf_engine.GetItemAttrNumber(itemtype => itemtype,
2178 itemkey => itemkey,
2179 aname => 'CHECK_ID');
2180
2181 IF l_debug_on THEN
2182 WSH_DEBUG_SV.logmsg(l_api_name,to_char(l_check_id),WSH_DEBUG_SV.C_PROC_LEVEL);
2183 END IF;
2184
2185
2186 FOR get_c_invoice_rec IN c_invoice(l_check_id)
2187 LOOP
2188 --{
2189 l_invoice_number := get_c_invoice_rec.invoice_num;
2190 l_payment_status := get_c_invoice_rec.payment_status_flag;
2191
2192 SELECT invoice_header_id into l_invoice_id
2193 FROM fte_invoice_headers
2194 WHERE bill_number = l_invoice_number;
2195
2196 IF l_payment_status = 'Y' THEN
2197 UPDATE FTE_INVOICE_HEADERS
2198 SET BILL_STATUS = 'PAID'
2199 WHERE BILL_NUMBER = l_invoice_number;
2200 --Commenting the code for Obsoletion of DBI APIs in 12.2
2201 --callDBI(l_invoice_id, 'UPDATE', returnStatus); BUG#11852550
2202 ELSIF l_payment_status = 'P' THEN
2203 UPDATE FTE_INVOICE_HEADERS
2204 SET BILL_STATUS = 'PARTIAL_PAID'
2205 WHERE BILL_NUMBER = l_invoice_number;
2206 --Commenting the code for Obsoletion of DBI APIs in 12.2
2207 --callDBI(l_invoice_id, 'UPDATE', returnStatus); BUG#11852550
2208 END IF;
2209
2210 --}
2211 END LOOP;
2212
2213
2214 IF c_invoice%ISOPEN THEN
2215 CLOSE c_invoice;
2216 END IF;
2217
2218 /**
2219 OPEN C_INVOICE(l_check_id);
2220 FETCH C_INVOICE INTO l_invoice_number,l_payment_status;
2221
2222 IF C_INVOICE%NOTFOUND THEN
2223 resultout := 'COMPLETE:N';
2224 RETURN;
2225 END IF;
2226
2227 CLOSE C_INVOICE;
2228
2229 select invoice_header_id into l_invoice_id
2230 from fte_invoice_headers
2231 where bill_number = l_invoice_number;
2232
2233 IF l_payment_status = 'Y' THEN
2234 UPDATE FTE_INVOICE_HEADERS
2235 SET BILL_STATUS = 'PAID'
2236 WHERE BILL_NUMBER = l_invoice_number;
2237
2238 callDBI(l_invoice_id, 'UPDATE', returnStatus);
2239
2240 ELSIF l_payment_status = 'P' THEN
2241 UPDATE FTE_INVOICE_HEADERS
2242 SET BILL_STATUS = 'PARTIALLY PAID'
2243 WHERE BILL_NUMBER = l_invoice_number;
2244
2245 callDBI(l_invoice_id, 'UPDATE', returnStatus);
2246 END IF;
2247 */
2248
2249 END IF;
2250
2251 IF l_debug_on THEN
2252 WSH_DEBUG_SV.pop(l_api_name);
2253 END IF;
2254
2255
2256 resultout := 'COMPLETE:Y';
2257
2258 EXCEPTION
2259 WHEN OTHERS THEN
2260 resultout := 'COMPLETE:N';
2261
2262 wf_core.context('FTE_FPA_UTIL',
2263 'Update_Status',
2264 itemtype,
2265 itemkey,
2266 actid,
2267 funcmode);
2268 RAISE;
2269
2270 END Update_Status;
2271
2272 -- -------------------------------------------------------------------------- --
2273 -- --
2274 -- NAME: START_FRACCT_WF_PROCESS --
2275 -- TYPE: FUNCTION --
2276 -- PARAMETERS (IN): p_carrier_id NUMBER --
2277 -- p_ship_from_org_id NUMBER --
2278 -- PARAMETERS (OUT): x_return_ccid NUMBER --
2279 -- p_ship_to_org_id NUMBER --
2280 -- p_supplier_id NUMBER --
2281 -- p_supplier_site_id NUMBER --
2282 -- p_trip_id NUMBER --
2283 -- p_delivery_id NUMBER --
2284 -- x_concat_segs VARCHAR2 --
2285 -- x_concat_ids VARCHAR2 --
2286 -- x_concat_descrs VARCHAR2 --
2287 -- x_msg_count VARCHAR2 --
2288 -- x_msg_data VARCHAR2 --
2289 -- PARAMETERS (IN OUT): none --
2290 -- RETURN: VARCHAR2 --
2291 -- DESCRIPTION: This function is used to start the Workflow Process --
2292 -- for the Distribution Account Generator --
2293 -- --
2294 -- --
2295 -- -------------------------------------------------------------------------- --
2296
2297
2298
2299
2300 FUNCTION START_FRACCT_WF_PROCESS
2301 (
2302 p_carrier_id IN NUMBER,
2303 p_ship_from_org_id IN NUMBER,
2304 p_ship_to_org_id IN NUMBER,
2305 p_supplier_id IN NUMBER,
2306 p_supplier_site_id IN NUMBER,
2307 p_trip_id IN NUMBER,
2308 p_delivery_id IN NUMBER,
2309 x_return_ccid OUT NOCOPY NUMBER,
2310 x_concat_segs OUT NOCOPY VARCHAR2,
2311 x_concat_ids OUT NOCOPY VARCHAR2,
2312 x_concat_descrs OUT NOCOPY VARCHAR2,
2313 x_msg_count OUT NOCOPY NUMBER,
2314 x_msg_data OUT NOCOPY VARCHAR2)
2315 RETURN VARCHAR2
2316 IS
2317 l_chart_of_accounts_id NUMBER;
2318 l_itemkey VARCHAR2(38);
2319 l_itemtype VARCHAR2(30);
2320 lx_return_ccid NUMBER;
2321 lx_concat_segs VARCHAR2(1000);
2322 lx_concat_ids VARCHAR2(1000);
2323 lx_concat_descrs VARCHAR2(1000);
2324 lx_msg_count NUMBER;
2325 lx_msg_data VARCHAR2(1000);
2326 l_errmsg VARCHAR2(2000);
2327 l_result BOOLEAN;
2328 l_new_combination BOOLEAN;
2329 l_return_status VARCHAR2(1);
2330
2331 --
2332 l_debug_on BOOLEAN;
2333 --
2334 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'START_FRACCT_WF_PROCESS';
2335
2336 BEGIN
2337
2338 --
2339
2340 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2341 --
2342 IF l_debug_on IS NULL
2343 THEN
2344 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2345 END IF;
2346 --
2347 --
2348 -- Debug Statements
2349 --
2350 IF l_debug_on THEN
2351 WSH_DEBUG_SV.push(l_module_name);
2352 END IF;
2353
2354
2355 l_itemtype := 'FTEDIST';
2356 BEGIN
2357 SELECT GSOB.CHART_OF_ACCOUNTS_ID
2358 INTO l_chart_of_accounts_id
2359 FROM
2360 HR_ORGANIZATION_INFORMATION HOI2,
2361 GL_SETS_OF_BOOKS GSOB
2362 WHERE HOI2.ORG_INFORMATION1 = TO_CHAR(GSOB.SET_OF_BOOKS_ID)
2363 AND (HOI2.ORG_INFORMATION_CONTEXT || '') ='Accounting Information'
2364 AND HOI2.ORGANIZATION_ID = p_ship_from_org_id;
2365 EXCEPTION
2366 WHEN NO_DATA_FOUND THEN
2367 IF l_debug_on THEN
2368 WSH_DEBUG_SV.log(l_module_name,' Chart of Account is not defined for the Organization' , p_ship_from_org_id);
2369 END IF;
2370 END;
2371
2372 -- Bug # 3401364
2373 WF_ITEM.CLEARCACHE;
2374
2375 IF l_debug_on THEN
2376 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FND_FLEX_WORKFLOW.INITIALIZE',WSH_DEBUG_SV.C_PROC_LEVEL);
2377 END IF;
2378
2379 l_itemkey := FND_FLEX_WORKFLOW.INITIALIZE
2380 ('SQLGL',
2381 'GL#',
2382 l_chart_of_accounts_id,
2383 l_itemtype
2384 );
2385
2386 IF l_debug_on THEN
2387 WSH_DEBUG_SV.log(l_module_name,'Item Key' , l_itemkey);
2388 END IF;
2389
2390
2391 /* Initialize the workflow item attributes */
2392 wf_engine.SetItemAttrNumber(
2393 itemtype => l_itemtype,
2394 itemkey => l_itemkey,
2395 aname => 'SHIP_FROM_ORG_ID',
2396 avalue => p_ship_from_org_id);
2397
2398 wf_engine.SetItemAttrNumber(
2399 itemtype => l_itemtype,
2400 itemkey => l_itemkey,
2401 aname => 'SHIP_TO_ORG_ID',
2402 avalue => p_ship_to_org_id);
2403
2404 wf_engine.SetItemAttrNumber(
2405 itemtype => l_itemtype,
2406 itemkey => l_itemkey,
2407 aname => 'CARRIER_ID',
2408 avalue => p_carrier_id);
2409
2410 wf_engine.SetItemAttrNumber(
2411 itemtype => l_itemtype,
2412 itemkey => l_itemkey,
2413 aname => 'SUPPLIER_ID',
2414 avalue => p_supplier_id);
2415
2416 wf_engine.SetItemAttrNumber(
2417 itemtype => l_itemtype,
2418 itemkey => l_itemkey,
2419 aname => 'SUPPLIER_SITE_ID',
2420 avalue => p_supplier_site_id);
2421
2422 wf_engine.SetItemAttrNumber(
2423 itemtype => l_itemtype,
2424 itemkey => l_itemkey,
2425 aname => 'TRIP_ID',
2426 avalue => p_trip_id);
2427
2428 wf_engine.SetItemAttrNumber(
2429 itemtype => l_itemtype,
2430 itemkey => l_itemkey,
2431 aname => 'DELIVERY_ID',
2432 avalue => p_delivery_id);
2433
2434 IF l_debug_on THEN
2435 WSH_DEBUG_SV.logmsg(l_module_name,'Calling program unit FND_FLEX_WORKFLOW.GENERATE(',WSH_DEBUG_SV.C_PROC_LEVEL);
2436 END IF;
2437
2438 l_result := FND_FLEX_WORKFLOW.GENERATE(
2439 itemtype => l_itemtype,
2440 itemkey => l_itemkey,
2441 insert_if_new => FALSE,
2442 ccid => lx_return_ccid,
2443 concat_segs => lx_concat_segs,
2444 concat_ids => lx_concat_ids,
2445 concat_descrs => lx_concat_descrs,
2446 error_message => l_errmsg,
2447 new_combination => l_new_combination
2448 );
2449
2450
2451 IF l_debug_on THEN
2452 WSH_DEBUG_SV.log(l_module_name,'ccid : ' , lx_return_ccid);
2453 WSH_DEBUG_SV.log(l_module_name,'Concatenated Segments : ' , lx_concat_segs);
2454 WSH_DEBUG_SV.log(l_module_name,'Error Message' , l_errmsg);
2455 END IF;
2456
2457
2458
2459
2460
2461
2462
2463 x_return_ccid := lx_return_ccid;
2464 x_concat_segs := lx_concat_segs;
2465 x_concat_ids := lx_concat_ids;
2466 x_concat_descrs := lx_concat_descrs;
2467
2468
2469
2470
2471 IF l_result THEN
2472 l_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2473 ELSE
2474
2475 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2476 IF l_debug_on THEN
2477
2478 WSH_DEBUG_SV.log(l_module_name,'Error Message' , l_errmsg);
2479 END IF;
2480
2481
2482 END IF;
2483
2484
2485 IF l_debug_on THEN
2486 WSH_DEBUG_SV.pop(l_module_name);
2487 END IF;
2488
2489 RETURN l_return_status;
2490
2491 EXCEPTION
2492 WHEN OTHERS THEN
2493 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
2494
2495 IF l_debug_on THEN
2496 WSH_DEBUG_SV.log(l_module_name,'Unexpected Error occured in START_FRACCT_WF_PROCESS' || SQLERRM);
2497 WSH_DEBUG_SV.pop(l_module_name);
2498 END IF;
2499
2500 return l_return_status;
2501
2502
2503 END START_FRACCT_WF_PROCESS;
2504
2505
2506
2507 -- -----------------------------------------------------------------------------
2508 -- --
2509 -- NAME: GET_FRACCT_CCID --
2510 -- TYPE: PROCEDURE --
2511 -- PARAMETERS (IN): itemtype NUMBER --
2512 -- itemkey VARCHAR2 --
2513 -- actid NUMBER --
2514 -- funcmode VARCHAR2 --
2515 -- PARAMETERS (OUT): result VARCHAR2 --
2516 -- PARAMETERS (IN OUT): none --
2517 -- RETURN: NONE --
2518 -- DESCRIPTION: This procedure gets the ORGANIZATION_ID which is a --
2519 -- Workflow Attributes and determines the --
2520 -- default freight account from the Shipping Parameters --
2521 -- (WSH_SHIPPING_PARAMETERS. The Workflow attribute --
2522 -- 'GENERATED_CCID' is set accordingly --
2523 -- for the Distribution Account Generator --
2524 -- --
2525 -- --
2526 -- -------------------------------------------------------------------------- --
2527
2528
2529
2530
2531
2532
2533
2534 PROCEDURE GET_FRACCT_CCID
2535 (
2536 itemtype IN VARCHAR2,
2537 itemkey IN VARCHAR2,
2538 actid IN NUMBER,
2539 funcmode IN VARCHAR2,
2540 result OUT NOCOPY VARCHAR2) IS
2541
2542 l_freight_code VARCHAR2(25);
2543 l_carrier_id NUMBER;
2544 l_organization_id NUMBER;
2545 l_ccid NUMBER;
2546 l_debug_on BOOLEAN;
2547 --
2548 l_module_name CONSTANT VARCHAR2(100) := 'wsh.plsql.' || G_PKG_NAME || '.' || 'START_FRACCT_WF_PROCESS';
2549
2550 BEGIN
2551
2552 --
2553 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2554 --
2555 IF l_debug_on IS NULL
2556 THEN
2557 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2558 END IF;
2559 --
2560 --
2561 -- Debug Statements
2562 --
2563 IF l_debug_on THEN
2564 WSH_DEBUG_SV.push(l_module_name);
2565 END IF;
2566
2567 IF funcmode = 'RUN' THEN
2568
2569 l_organization_id := wf_engine.getItemAttrNumber(itemtype,itemkey,'SHIP_FROM_ORG_ID');
2570
2571 IF l_debug_on THEN
2572 WSH_DEBUG_SV.log(l_module_name, 'Ship From Org : ' , l_organization_id);
2573 END IF;
2574
2575
2576
2577 SELECT FPA_DEFAULT_FREIGHT_ACCOUNT
2578 INTO l_ccid
2579 FROM wsh_shipping_parameters
2580 WHERE organization_id = l_organization_id;
2581
2582 IF l_debug_on THEN
2583 WSH_DEBUG_SV.log(l_module_name, ' Freight Account from Shipping Paramters (CCID) : ' , l_ccid);
2584 END IF;
2585
2586
2587
2588 wf_engine.setItemAttrNumber(itemtype,itemkey,'GENERATED_CCID',l_ccid);
2589
2590 result := 'COMPLETE:SUCCESS';
2591 IF l_debug_on THEN
2592 WSH_DEBUG_SV.pop(l_module_name);
2593 END IF;
2594 return;
2595
2596 END IF;
2597
2598 IF funcmode = 'CANCEL' THEN
2599 IF l_debug_on THEN
2600 WSH_DEBUG_SV.pop(l_module_name);
2601 END IF;
2602 return;
2603 END IF;
2604
2605 EXCEPTION
2606
2607 WHEN OTHERS THEN
2608
2609 wf_core.context('FTE_FPA_UTIL','GET_FRACCT_CCID',
2610 itemtype,itemkey,TO_CHAR(actid),funcmode);
2611 result := 'COMPLETE:FAILURE';
2612 RAISE;
2613
2614
2615 end GET_FRACCT_CCID;
2616
2617
2618 PROCEDURE LOG_FAILURE_REASON(
2619 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2620 p_parent_name IN VARCHAR2,
2621 p_parent_id IN NUMBER,
2622 p_failure_type IN VARCHAR2,
2623 p_failure_reason IN VARCHAR2,
2624 x_return_status OUT NOCOPY VARCHAR2,
2625 x_msg_count OUT NOCOPY NUMBER,
2626 x_msg_data OUT NOCOPY VARCHAR2) IS
2627 --{
2628
2629 l_api_name CONSTANT VARCHAR2(30) := 'LOG_FAILURE_REASON';
2630 l_api_version CONSTANT NUMBER := 1.0;
2631
2632 --}
2633
2634
2635 --{
2636 BEGIN
2637 --
2638 -- Standard Start of API savepoint
2639 SAVEPOINT LOG_FAILURE_REASON_PUB;
2640 --
2641 --
2642 -- Initialize message list if p_init_msg_list is set to TRUE.
2643 --
2644 --
2645 IF FND_API.to_Boolean( p_init_msg_list )
2646 THEN
2647 FND_MSG_PUB.initialize;
2648 END IF;
2649 --
2650 --
2651 -- Initialize API return status to success
2652 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2653 x_msg_count := 0;
2654 x_msg_data := 0;
2655
2656 INSERT INTO FTE_FAILURE_REASONS
2657 (INVOICE_REJECT_ID,
2658 PARENT_NAME,
2659 PARENT_ID,
2660 BOL,
2661 FAILURE_TYPE,
2662 FAILURE_REASON,
2663 CREATED_BY,
2664 CREATION_DATE,
2665 LAST_UPDATED_BY,
2666 LAST_UPDATE_DATE,
2667 LAST_UPDATE_LOGIN)
2668 VALUES
2669 (FTE_FAILURE_REASONS_S.nextval,
2670 p_parent_name,
2671 p_parent_id,
2672 NULL,
2673 p_failure_type,
2674 p_failure_reason,
2675 FND_GLOBAL.USER_ID,
2676 SYSDATE,
2677 FND_GLOBAL.USER_ID,
2678 SYSDATE,
2679 FND_GLOBAL.USER_ID);
2680
2681
2682 -- Standard call to get message count and if count is 1,get message info.
2683 --
2684 FND_MSG_PUB.Count_And_Get
2685 (
2686 p_count => x_msg_count,
2687 p_data => x_msg_data,
2688 p_encoded => FND_API.G_FALSE
2689 );
2690 --
2691 --
2692
2693
2694 --}
2695 EXCEPTION
2696 --{
2697 WHEN FND_API.G_EXC_ERROR THEN
2698 ROLLBACK TO LOG_FAILURE_REASON_PUB;
2699 x_return_status := FND_API.G_RET_STS_ERROR;
2700 FND_MSG_PUB.Count_And_Get
2701 (
2702 p_count => x_msg_count,
2703 p_data => x_msg_data,
2704 p_encoded => FND_API.G_FALSE
2705 );
2706 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2707 ROLLBACK TO LOG_FAILURE_REASON_PUB;
2708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2709 FND_MSG_PUB.Count_And_Get
2710 (
2711 p_count => x_msg_count,
2712 p_data => x_msg_data,
2713 p_encoded => FND_API.G_FALSE
2714 );
2715 WHEN OTHERS THEN
2716 ROLLBACK TO LOG_FAILURE_REASON_PUB;
2717 wsh_util_core.default_handler('FTE_TENDER_PVT.TAKE_TENDER_SNAPSHOT_PUB');
2718 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2719 FND_MSG_PUB.Count_And_Get
2720 (
2721 p_count => x_msg_count,
2722 p_data => x_msg_data,
2723 p_encoded => FND_API.G_FALSE
2724 );
2725
2726 --}
2727
2728 END LOG_FAILURE_REASON;
2729
2730
2731
2732 END FTE_FPA_UTIL;