1 PACKAGE BODY FTE_PO_INTEGRATION_GRP AS
2 /* $Header: FTEGPOIB.pls 120.7 2006/05/31 19:11:17 schennal noship $ */
3 /*
4 -- Global constants
5 -- +======================================================================+
6 -- Procedure :
7 -- FTE-PO Integration Package
8 -- GET_ESTIMATED_RATES uses to get the estimated rates based on the given shipment header id
9 -- This API will not call any Rating or Re-rating API internally
10 -- It will get only pre-rated information from wsh_freight_cost table
11 -- and pro-rate the container rates whereever it is necessary
12 -- Description:
13 -- Getting the rates are divided into 3 sections.
14 -- 1. Find all deliveries are not rated or partially rated or re-rate required.
15 -- All corresponding receipt lines will have the rate zero since they are not valid or no rates
16 -- 2. Non-matched receipts w/ Delivery
17 -- we need to sum up the rates against all po lines of the
18 -- shipment header and pro-rated to all receipt lines
19 -- based on the qty at receipt
20 -- step-1. Find the shipment header has a match delivery or not
21 -- step-2. if there is a match, go to main-step-3 for matching receipt rates
22 -- step-3 if there is a mismatch, find all corresponding po lines for the given shipment header id
23 -- from wdd/rcv and get the rates from wfc tables
24 -- (by calling get_rcv_shipment_lines API)
25 -- 3. Matched Receipts w/ Delivery (it means you can have a receipt line id
26 -- at WDD level and Shipment header id at WND
27 -- --Get the detail rates for TL, LTL, Parcel, etc.. for non packed items
28 -- --Get all contaner rates for TL, LTL, and Parcel, etc for packed items.
29 -- for LTL, the rates are stored at detail level, no need of pro-rating
30 -- for Non-LTL, the rates needs to be pro-rated based on the qty
31 -- Inputs:
32 -- rate input parameters ( self explained )
33 -- Output:
34 -- Table of Receipt Lines w/ cost, currency,vendor id, vendor site id,return status and message text
35 -- Status, and messages data
36 -- Return Status within the Table as follows:-
37 -- Value of RETURN_STATUS can be S- Success, W- Warning, Error, and U- Unhandled Exception.
38 -- Value of MESSAGE_TEXT will be a translated message as follows:
39 -- 1. Rate is available (if RETURN_STATUS is S)
40 -- 2. Rate is not available (if RETURN_STATUS is W)
41 -- 3. Currency conversion failed (if RETURN_STATUS is E)
42 -- 4. Standard Oracle Error / Program Error (if RETURN_STATUS is U)
43 --Note: Rate are not available could be due to the following reason
44 -- 1. One of the Delivery leg is not rated
45 -- 2. Rates on one of the Delivery Leg is Dirty (REQUIRED_REPRICE FLAG is set to Y)
46 -- Proration logic of the distribution of container level cost to the container contents
47 --For ex: Total Cost at Container Level is $100.00 (Net Wt - 100 LBS)
48 -- ITEM NAME NET WT RATE DISTRIBUTED
49 -- ITEM-A 50 LBS (50/105 * 100) = $47.62
50 -- ITEM-B 30 LBS (30/105 * 100) = $28.57
51 -- ITEM-C 25 LBS (25/105 * 100) = $23.81
52 -- Item Total 105 LBS $100.00
53
54 -- +======================================================================+
55 */
56 /* TBD list
57 --Compare with Shipped qty rather than total receipt qty in case of non-matching case.
58 --Input can be receipt line id rather than receipt header id
59 --Output needs to make to the PO_RCV_CHARGES table structure for the future support.
60
61 */
62 --
63 G_PKG_NAME CONSTANT VARCHAR2(50) := 'FTE_PO_INTEGRATION_GRP';
64 /* This API will be called only if the shipment does not match with receipts
65 and WDD does not contain rcv_shipment_line_id
66 This procedure will be used to get all receipt shipment lines and recd qty
67 for the given shipment header id, po line id, po line location id
68 Prorate the cost at po line location level to each receipt lines based on the qty
69 */
70
71 PROCEDURE get_rcv_shipment_lines ( x_return_status OUT NOCOPY VARCHAR2,
72 P_SHIPMENT_HEADER_ID IN VARCHAR2,
73 P_PO_LINE_ID IN NUMBER,
74 P_PO_LINE_LOCATION_ID IN NUMBER,
75 P_TOTAL_COST IN NUMBER,
76 P_SHIP_QTY_UOM IN VARCHAR2,
77 P_SHIP_QTY IN NUMBER,
78 X_RCV_SHIP_LINES_TABLE OUT NOCOPY FTE_PO_INTEGRATION_GRP.fte_number_table,
79 X_RCV_SHIP_COST_TABLE OUT NOCOPY FTE_PO_INTEGRATION_GRP.fte_number_table);
80 --
81 l_receipt_lines_rec FTE_PO_INTEGRATION_GRP.FTE_RECEIPT_LINE_REC;
82 l_receipt_lines_tab FTE_PO_INTEGRATION_GRP.FTE_RECEIPT_LINES_TAB;
83
84 l_debug_on BOOLEAN;
85 l_debugfile varchar2(2000);
86
87 PROCEDURE GET_ESTIMATED_RATES(
88 p_init_msg_list IN VARCHAR2,
89 p_api_version_number IN NUMBER,
90 x_msg_count OUT NOCOPY NUMBER,
91 x_msg_data OUT NOCOPY VARCHAR2,
92 x_return_status OUT NOCOPY VARCHAR2,
93 p_shipment_header_id IN NUMBER,
94 x_receipt_lines_tab OUT NOCOPY FTE_PO_INTEGRATION_GRP.FTE_RECEIPT_LINES_TAB)
95 IS
96
97 /* find all deliveries for matching or non-matching shipments*/
98 cursor c_get_deliveries(l_shipment_header_id number) IS
99 select wnd.delivery_id,'Y' RATE_AVAILABLE_FLAG, 'Y' MATCHING_FLAG
100 FROM WSH_NEW_DELIVERIES WND
101 WHERE WND.RCV_SHIPMENT_HEADER_ID = l_shipment_header_id
102 AND WND.RCV_SHIPMENT_HEADER_ID is not null
103 union
104 select distinct wda.DELIVERY_ID, 'Y' RATE_AVAILABLE_FLAG,'N' MATCHING_FLAG
105 from WSH_DELIVERY_ASSIGNMENTS_V wda,
106 wsh_delivery_details wdd,
107 rcv_shipment_lines rsl
108 where wda.delivery_detail_id=wdd.delivery_detail_id
109 and wdd.rcv_shipment_line_id is null
110 and rsl.shipment_header_id = l_shipment_header_id
111 AND rsl.po_line_location_id = wdd.PO_SHIPMENT_LINE_ID
112 and rsl.po_line_id = wdd.source_line_id
113 and rsl.po_header_id = wdd.source_header_id
114 and wdd.source_code = 'PO'
115 and wda.delivery_id is not null
116 and exists
117 (
118 select shipment_header_id
119 from wsh_inbound_txn_history
120 where shipment_header_id = rsl.shipment_header_id
121 AND status = 'PENDING_MATCHING'
122 AND transaction_type = 'RECEIPT'
123 );
124
125 /* find all deliveries for no rates or partial rates for all deliveries for the given rcv ship header id */
126
127 cursor c_get_no_rates_del IS
128 select distinct tmp.delivery_id
129 FROM fte_estimate_rates_del_temp tmp,
130 WSH_DELIVERY_LEGS wdl
131 WHERE tmp.DELIVERY_ID = WDL.DELIVERY_ID
132 AND ( NVL(WDL.REPRICE_REQUIRED,'N') = 'Y'
133 OR NOT EXISTS
134 (SELECT WFC.DELIVERY_LEG_ID
135 FROM WSH_FREIGHT_COSTS WFC
136 WHERE WFC.DELIVERY_LEG_ID = WDL.DELIVERY_LEG_ID
137 AND WDL.DELIVERY_LEG_ID IS NOT NULL
138 AND WFC.LINE_TYPE_CODE = 'SUMMARY'
139 AND WFC.CHARGE_SOURCE_CODE= 'PRICING_ENGINE'
140 AND WFC.TOTAL_AMOUNT is not null)) ;
141
142 --output table variable
143 l_del_table FTE_PO_INTEGRATION_GRP.fte_number_table;
144 l_rate_available_table FTE_PO_INTEGRATION_GRP.fte_varchar3_table;
145 l_matching_table FTE_PO_INTEGRATION_GRP.fte_varchar3_table;
146 l_del_count NUMBER;
147 l_no_rate_del_count NUMBER;
148 l_NR_count NUMBER;
149
150
151 /* find all receipts for those receipt lines where there is no rates available or
152 partial rates available or available rates needs to re-rated */
153 -- fte_estimate_rates_del_temp contain the delivery list for the given shipment header id
154 cursor c_get_receipts_no_rates IS
155 select distinct wdd.vendor_id,
156 wdd.ship_from_site_id vendor_site_id,
157 wdd.source_line_id,
158 wdd.po_shipment_line_id,
159 wdd.rcv_shipment_line_id
160 FROM WSH_DELIVERY_DETAILS WDD,
161 WSH_DELIVERY_ASSIGNMENTS_V WDA2,
162 fte_estimate_rates_del_temp tmp
163 WHERE WDA2.delivery_detail_id = wdd.delivery_detail_id
164 AND tmp.RATE_AVAILABLE_FLAG = 'N' and tmp.MATCHING_FLAG = 'Y'
165 AND wda2.delivery_id = tmp.delivery_id
166 UNION
167 select distinct wdd.vendor_id,
168 wdd.ship_from_site_id vendor_site_id,
169 wdd.source_line_id,
170 wdd.po_shipment_line_id,
171 rsl.shipment_line_id
172 FROM WSH_DELIVERY_DETAILS WDD,
173 WSH_DELIVERY_ASSIGNMENTS_V WDA,
174 rcv_shipment_lines rsl,
175 fte_estimate_rates_del_temp tmp2
176 WHERE WDA.delivery_detail_id = wdd.delivery_detail_id
177 AND rsl.po_line_location_id = wdd.PO_SHIPMENT_LINE_ID
178 and rsl.po_line_id = wdd.source_line_id
179 and wdd.source_code = 'PO'
180 and wda.delivery_id is not null
181 AND tmp2.RATE_AVAILABLE_FLAG = 'N'
182 AND tmp2.MATCHING_FLAG = 'N'
183 AND wda.delivery_id = tmp2.delivery_id;
184
185 /* This following query returns all delivery lines along with the receipt lines and the cost associated
186 for the given Parameters - Shipment Header Id
187 Line Type code ( "SUMMARY" or "PRICE" )
188 Mode of Transport is devided into three. 1. TL and 2. LTL, 3. NON-TL-LTL
189 For TL Mode, rates are stores at SUMMARY ( line type code ) and others stored at PRICE (line type code)
190
191 --If the rates are in multiple currencies or the currency from WFC is different from WDD,
192 need to convert into WDD Currency and sum of the cost as output
193 -- This query will be returned all loose items ( all modes ) and packed items for LTL since packed items are rated
194 */
195 cursor c_get_receipts_detail_rates(l_shipment_header_id number ) IS
196
197 SELECT WDD.VENDOR_ID,
198 WDD.SHIP_FROM_SITE_ID VENDOR_SITE_ID,
199 WDD.SOURCE_LINE_ID,
200 WDD.PO_SHIPMENT_LINE_ID,
201 WDD.RCV_SHIPMENT_LINE_ID,
202 WDD.CURRENCY_CODE PO_CURRENCY_CODE,
203 WFC.CURRENCY_CODE,
204 WDD.REQUESTED_QUANTITY_UOM,
205 sum(nvl(wdd.received_quantity, nvl(wdd.shipped_quantity,
206 NVL(wdd.picked_quantity, wdd.requested_quantity)))) TOTAL_SHIP_QTY,
207 SUM(TOTAL_AMOUNT) TOTAL_COST
208 FROM WSH_NEW_DELIVERIES WND,
209 WSH_DELIVERY_LEGS WDL,
210 WSH_FREIGHT_COSTS WFC,
211 WSH_DELIVERY_DETAILS WDD,
212 WSH_DELIVERY_ASSIGNMENTS_V WDA,
213 WSH_TRIPS WT,
214 WSH_TRIP_STOPS WTS1,
215 WSH_TRIP_STOPS WTS2,
216 fte_estimate_rates_del_temp tmp
217 WHERE WND.DELIVERY_ID = WDL.DELIVERY_ID
218 AND WDA.DELIVERY_ID = WND.DELIVERY_ID
219 AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
220 AND WDL.DELIVERY_LEG_ID=WFC.DELIVERY_LEG_ID
221 AND WND.DELIVERY_ID = WFC.DELIVERY_ID
222 AND WDD.DELIVERY_DETAIL_ID=WFC.DELIVERY_DETAIL_ID
223 --AND WND.RCV_SHIPMENT_HEADER_ID IS NOT NULL
224 -- For only those deliveries has rates
225 AND tmp.delivery_id =WND.DELIVERY_ID
226 AND tmp.RATE_AVAILABLE_FLAG = 'Y'
227 AND WDD.CONTAINER_FLAG='N' -- only loosed items
228 -- For LTL, get all items including packed items, for others only loose items
229 -- since LTL rates are done at low level detail level as well.
230 AND ( WT.MODE_OF_TRANSPORT= 'LTL' or (WT.MODE_OF_TRANSPORT <> 'LTL' AND WDA.PARENT_DELIVERY_DETAIL_ID is NULL))
231 AND WFC.DELIVERY_DETAIL_ID IS NOT NULL -- only detail level rate
232 -- Line Type Code is SUMMARY for TL(TRUCK), PRICE for Non-TL
233 AND ((WFC.LINE_TYPE_CODE = 'SUMMARY' and WT.MODE_OF_TRANSPORT = 'TRUCK')
234 OR
235 (WFC.LINE_TYPE_CODE = 'PRICE' and WT.MODE_OF_TRANSPORT <> 'TRUCK'))
236 AND WFC.CHARGE_SOURCE_CODE= 'PRICING_ENGINE' -- Only FTE charge
237 AND WTS1.STOP_ID = WDL.PICK_UP_STOP_ID
238 AND WTS2.STOP_ID = WDL.DROP_OFF_STOP_ID
239 AND WT.TRIP_ID = WTS1.TRIP_ID
240 AND WT.TRIP_ID = WTS2.TRIP_ID
241
242 GROUP BY
243 WDD.VENDOR_ID,
244 WDD.SHIP_FROM_SITE_ID ,
245 WDD.SOURCE_LINE_ID,
246 WDD.PO_SHIPMENT_LINE_ID,
247 WDD.RCV_SHIPMENT_LINE_ID,
248 WDD.CURRENCY_CODE,
249 WFC.CURRENCY_CODE,
250 WDD.REQUESTED_QUANTITY_UOM;
251
252 /* Get all container level rates for the given shipment header and excluded delivery list
253 This query return only for non-LTL rates since LTL rates are already rated at detail level.
254 Container Level rates will be pro-rated to the detail level based on the net qty (total of all item qty)
255 --TL - SUMMARY, Non-TL - SUMMARY / PRICE
256 */
257 cursor c_get_container_rates(l_shipment_header_id number) IS
258 SELECT WDD.DELIVERY_DETAIL_ID PARENT_CONTAINER_ID,
259 WDD.CURRENCY_CODE PO_CURRENCY_CODE,
260 WFC.CURRENCY_CODE,
261 SUM(TOTAL_AMOUNT) TOTAL_COST
262 FROM WSH_NEW_DELIVERIES WND,
263 WSH_DELIVERY_LEGS WDL,
264 WSH_FREIGHT_COSTS WFC,
265 WSH_DELIVERY_DETAILS WDD,
266 WSH_DELIVERY_ASSIGNMENTS_V WDA,
267 WSH_TRIPS WT,
268 WSH_TRIP_STOPS WTS1,
269 WSH_TRIP_STOPS WTS2,
270 fte_estimate_rates_del_temp tmp
271 WHERE WND.DELIVERY_ID = WDL.DELIVERY_ID
272 AND WDA.DELIVERY_ID = WND.DELIVERY_ID
273 AND WDD.DELIVERY_DETAIL_ID=WDA.DELIVERY_DETAIL_ID
274 AND WDL.DELIVERY_LEG_ID=WFC.DELIVERY_LEG_ID
275 AND WND.DELIVERY_ID = WFC.DELIVERY_ID
276 AND WDD.DELIVERY_DETAIL_ID=WFC.DELIVERY_DETAIL_ID
277 --AND WND.RCV_SHIPMENT_HEADER_ID IS NOT NULL
278 -- For only those deliveries has rates
279 AND tmp.delivery_id =WND.DELIVERY_ID
280 AND tmp.RATE_AVAILABLE_FLAG = 'Y'
281 AND WDD.CONTAINER_FLAG='Y' -- Only container item
282 AND WDA.PARENT_DELIVERY_DETAIL_ID IS NULL -- only top level items.
283 AND WFC.DELIVERY_DETAIL_ID IS NOT NULL -- only detail level rate
284 AND WFC.LINE_TYPE_CODE in ('SUMMARY','PRICE') -- Only Summary rate at the container
285 AND WFC.CHARGE_SOURCE_CODE= 'PRICING_ENGINE'
286 AND WTS1.STOP_ID = WDL.PICK_UP_STOP_ID
287 AND WTS2.STOP_ID = WDL.DROP_OFF_STOP_ID
288 AND WT.TRIP_ID = WTS1.TRIP_ID
289 AND WT.TRIP_ID = WTS2.TRIP_ID
290 -- Since LTL rates are already calculated at detail level, no need to pro-rate again at detail level.
291 AND WT.MODE_OF_TRANSPORT <>'LTL'
292 AND ((WT.MODE_OF_TRANSPORT = 'TRUCK' and WFC.LINE_TYPE_CODE = 'SUMMARY')
293 OR
294 (WT.MODE_OF_TRANSPORT <> 'TRUCK' and WFC.LINE_TYPE_CODE in ('SUMMARY','PRICE')) )
295 GROUP BY
296 WDD.DELIVERY_DETAIL_ID,
297 WDD.CURRENCY_CODE,
298 WFC.CURRENCY_CODE ;
299
300 /* Get all container items ( nested ) for a given parent del-detail id
301 --Not used for this release since items never packed in nested container for I/B shipments (not supported)
302 cursor c_get_container_contents ( l_parent_delivery_detail_id number ) IS
303 SELECT WDD.DELIVERY_DETAIL_ID,
304 WDD.VENDOR_ID,
305 WDD.SOURCE_LINE_ID,
306 WDD.SHIP_FROM_SITE_ID,
307 WDD.PO_SHIPMENT_LINE_ID,
308 WDD.RCV_SHIPMENT_LINE_ID,
309 WDD.CURRENCY_CODE,
310 WDD.NET_WEIGHT,
311 WDD.WEIGHT_UOM_CODE
312 FROM WSH_DELIVERY_DETAILS WDD
313 WHERE
314 WDD.CONTAINER_FLAG='N'
315 AND EXISTS
316 (SELECT 1 FROM WSH_DELIVERY_ASSIGNMENTS_V WDA
317 WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
318 START WITH WDA.DELIVERY_DETAIL_ID = l_parent_delivery_detail_id
319 CONNECT BY PRIOR WDA.DELIVERY_DETAIL_ID = WDA.PARENT_DELIVERY_DETAIL_ID)
320 */
321 -- Since all I/B shipments are packed only one level container, no need of the above query..kept the query for future usage
322 -- Avoiding connect by prior usage due to performance reason and not required as of now (with current functionality)
323 -- Need to to pro-rate the rate from container level to the detail level based on the net qty
324
325 cursor c_get_container_contents ( l_parent_delivery_detail_id number ) IS
326 SELECT WDD.DELIVERY_DETAIL_ID,
327 WDD.VENDOR_ID,
328 WDD.SHIP_FROM_SITE_ID,
329 WDD.SOURCE_LINE_ID,
330 WDD.PO_SHIPMENT_LINE_ID,
331 WDD.RCV_SHIPMENT_LINE_ID,
332 WDD.CURRENCY_CODE,
333 WDD.INVENTORY_ITEM_ID,
334 WDD.NET_WEIGHT,
335 WDD.WEIGHT_UOM_CODE
336 FROM WSH_DELIVERY_DETAILS WDD,
337 WSH_DELIVERY_ASSIGNMENTS_V WDA
338 WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
339 and WDA.PARENT_DELIVERY_DETAIL_ID = l_parent_delivery_detail_id;
340
341
342 -- Declare local variables
343
344 i number ;
345 j number ;
346 k number ;
347 l_index number;
348
349 l_total_net_qty number ;
350 l_cont_contents_found_flag VARCHAR2(1);
351 l_no_rates_delivery_flag VARCHAR2(1);
352 l_rates_delivery_flag VARCHAR2(1);
356 l_net_weight number ;
353 l_delivery_exist_flag VARCHAR2(1);
354 l_delivery_list varchar2(2000);
355 l_no_rates_delivery_list varchar2(2000);
357 l_first_uom_code varchar2(3);
358
359 l_return_status VARCHAR2(1);
360 l_number_of_warnings NUMBER := 0;
361 l_number_of_errors NUMBER := 0;
362 l_msg_data VARCHAR2(4000);
363 -- Out parameter variables
364
365 l_message varchar2(1000);
366 l_loop_counter number;
367 l_rcv_count number ;
368
369 --Get RCV shipment Lines out variables
370 l_RCV_SHIP_LINES_TABLE FTE_PO_INTEGRATION_GRP.fte_number_table;
371 l_RCV_SHIP_COST_TABLE FTE_PO_INTEGRATION_GRP.fte_number_table;
372
373 -- Container level rates variable
374 l_CNT_parent_cont_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
375 l_CNT_po_corrency_code_table FTE_PO_INTEGRATION_GRP.fte_varchar15_table;
376 l_CNT_wfc_corrency_code_table FTE_PO_INTEGRATION_GRP.fte_varchar15_table;
377 l_CNT_total_cost_table FTE_PO_INTEGRATION_GRP.fte_number_table;
378
379 -- Container Contents Level Rates variables
380 l_DET_vendor_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
381 l_DET_vendor_site_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
382 l_DET_po_line_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
383 l_DET_po_line_loc_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
384 l_DET_rcv_ship_line_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
385 l_DET_po_corrency_code_table FTE_PO_INTEGRATION_GRP.fte_varchar15_table;
386 l_DET_wfc_corrency_code_table FTE_PO_INTEGRATION_GRP.fte_varchar15_table;
387 l_DET_total_cost_table FTE_PO_INTEGRATION_GRP.fte_number_table;
388
389 --Container contents variables (store the values from the above query )
390
391 l_CC_delivery_detail_table FTE_PO_INTEGRATION_GRP.fte_number_table;
392 l_CC_vendor_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
393 l_CC_vendor_site_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
394 l_CC_po_line_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
395 l_CC_po_line_loc_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
396 l_CC_rcv_ship_line_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
397 l_CC_po_corrency_code_table FTE_PO_INTEGRATION_GRP.fte_varchar15_table;
398 l_CC_item_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
399 l_CC_net_wt_table FTE_PO_INTEGRATION_GRP.fte_number_table;
400 l_CC_uom_code_table FTE_PO_INTEGRATION_GRP.fte_varchar3_table;
401
402 -- Cursor parameters
403 l_mode varchar2(20);
404 l_line_type_code varchar2(20);
405
406 l_delivery_list_table FTE_PO_INTEGRATION_GRP.fte_number_table;
407
408 -- Rates variables (all rates)
409 l_vendor_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
410 l_vendor_site_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
411 l_po_line_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
412 l_po_line_loc_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
413 l_rcv_ship_line_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
414 l_po_corrency_code_table FTE_PO_INTEGRATION_GRP.fte_varchar15_table;
415 l_wfc_corrency_code_table FTE_PO_INTEGRATION_GRP.fte_varchar15_table;
416 l_ship_qty_table FTE_PO_INTEGRATION_GRP.fte_number_table;
417 l_ship_qty_uom_table FTE_PO_INTEGRATION_GRP.fte_varchar3_table;
418 l_total_cost_table FTE_PO_INTEGRATION_GRP.fte_number_table;
419
420 -- NR - No rates list variables
421 l_NR_vendor_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
422 l_NR_vendor_site_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
423 l_NR_po_line_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
424 l_NR_po_line_loc_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
425 l_NR_rcv_ship_line_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
426
427 -- Exception variables
428
429 e_validation_error EXCEPTION;
430 --
431
432 l_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'GET_ESTIMATED_RATES';
433 --
434 BEGIN
435 SAVEPOINT FTE_PO_INTEGRATION_GRP;
436
437 -- Initialize message list if p_init_msg_list is set to TRUE.
438 --
439 --
440 IF FND_API.to_Boolean( p_init_msg_list )
441 THEN
442 FND_MSG_PUB.initialize;
443 END IF;
444 --
445 --
446 -- Initialize API return status to success
447 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
448 x_msg_count := 0;
449 x_msg_data := '';
450
451 -- Debug
452 --
453 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
454 --
455 IF l_debug_on IS NULL
456 THEN
457 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
458 END IF;
459 --
460 -- Debug Statements
461 --
462 IF l_debug_on THEN
463 fnd_profile.get('WSH_DEBUG_LOG_DIRECTORY',l_debugfile);
464 l_debugfile := l_debugfile||'/'||WSH_DEBUG_SV.g_file;
465
466 WSH_DEBUG_SV.push(l_module_name);
467 WSH_DEBUG_SV.log(l_module_name,'Begin of the process ',l_debugfile);
468 WSH_DEBUG_SV.log(l_module_name,'Shipment Header id ',p_shipment_header_id);
469 END IF;
470 --
471 -- initialize variables and tables
472 l_receipt_lines_tab.delete;
473 x_receipt_lines_tab.delete;
474
475 -- find all deliveries (matching and mismatching shipments)
476 OPEN c_get_deliveries(p_shipment_header_id);
477 FETCH c_get_deliveries BULK COLLECT
478 INTO l_del_table,l_rate_available_table,l_matching_table;
479 l_del_count := l_del_table.count ;
480 WSH_DEBUG_SV.log(l_module_name,'Total deliveries found for the given header id : ',l_del_count);
481 l_delivery_exist_flag := 'N';
482 l_no_rate_del_count := 0;
483 --
484 IF l_del_count > 0 then
485 --{
486 l_delivery_exist_flag := 'Y';
487 FORALL j IN 1..l_del_count
488 INSERT INTO fte_estimate_rates_del_temp(DELIVERY_ID,RATE_AVAILABLE_FLAG,MATCHING_FLAG)
489 VALUES (l_del_table(j),l_rate_available_table(j),l_matching_table(j));
493 FETCH c_get_no_rates_del BULK COLLECT
490
491 -- find all deliveries for no rates or partial rates (matching and mismatching shipments)
492 OPEN c_get_no_rates_del;
494 INTO l_del_table;
495 l_no_rate_del_count := l_del_table.count ;
496 WSH_DEBUG_SV.log(l_module_name,'Total deliveries, which have no rates: ',l_no_rate_del_count);
497 --
498 --Updating those deliveries with RATE_AVAILABLE_FLAG ='N'
499 IF l_no_rate_del_count > 0 then
500 FORALL j IN 1..l_no_rate_del_count
501 UPDATE fte_estimate_rates_del_temp
502 SET RATE_AVAILABLE_FLAG ='N'
503 WHERE DELIVERY_ID = l_del_table(j);
504 END IF;
505 --}
506 ELSE
507 -- No record found for the given rcv header id
508 FND_MESSAGE.SET_NAME('FTE','FTE_EC_NO_DATA_FOUND');
509 FND_MESSAGE.SET_TOKEN('LOG_FILE',l_debugfile);
510 raise e_validation_error;
511 END IF;
512 --
513 -- Get all Rates ( except LTL) Cotainer Level Rates
514 --
515 OPEN c_get_container_rates(p_shipment_header_id);
516 --dbms_output.put_line('after open cursor c_get_container_rates for CNT Rates');
517 FETCH c_get_container_rates BULK COLLECT
518 INTO l_CNT_parent_cont_id_table,
519 l_CNT_po_corrency_code_table,
520 l_CNT_wfc_corrency_code_table,
521 l_CNT_total_cost_table;
522 close c_get_container_rates;
523 --dbms_output.put_line('after close cursor c_get_container_rates for CNT Rates'||l_CNT_parent_cont_id_table.COUNT);
524 --
525 IF l_debug_on THEN
526 WSH_DEBUG_SV.log(l_module_name,'cursor c_get_container_rates - All Rates except LTL - number '||
527 'of records found: ', l_CNT_parent_cont_id_table.COUNT);
528 END IF;
529 --
530 --
531 -- Get all Container Contents for the given Container Parent Container
532 --
533 l_cont_contents_found_flag := 'N';
534 i := 1;
535 j := 1;
536 k := 1;
537 --dbms_output.put_line('validating l_CNT_parent_cont_id_table.COUNT > 0 ');
538 IF l_CNT_parent_cont_id_table.COUNT > 0 THEN
539 --{
540 --
541 --dbms_output.put_line('validated l_CNT_parent_cont_id_table.COUNT > 0 ');
542 -- For each container, get all contents , total the net qty of the contents, pro-rate the cost to each contents
543 i := l_CNT_parent_cont_id_table.FIRST;
544 WHILE i is not NULL
545 LOOP
546 --{
547 OPEN c_get_container_contents(l_CNT_parent_cont_id_table(i));
548 FETCH c_get_container_contents BULK COLLECT
549 INTO l_CC_delivery_detail_table,
550 l_CC_vendor_id_table,
551 l_CC_vendor_site_id_table,
552 l_CC_po_line_id_table,
553 l_CC_po_line_loc_id_table,
554 l_CC_rcv_ship_line_id_table,
555 l_CC_po_corrency_code_table,
556 l_CC_item_id_table,
557 l_CC_net_wt_table,
558 l_CC_uom_code_table;
559 close c_get_container_contents;
560 --
561 IF l_debug_on THEN
562 WSH_DEBUG_SV.log(l_module_name,'cursor c_get_container_contents - number '||
563 'of records found: ', l_CC_delivery_detail_table.COUNT);
564 END IF;
565 -- Initializing the net wt for each container
566 l_total_net_qty := 0;
567 -- Get the total net wt for each container in case net qty at container level does not match with
568 -- the total net qty of the contents.
569 j := l_CC_delivery_detail_table.FIRST;
570 WHILE j is not NULL
571 LOOP
572 --{
573 l_cont_contents_found_flag := 'Y';
574 --
575 -- Need to convert the Uom code into the 1st UOM to total the net qty for the prorating.
576 -- in case of different uom in different container content items
577 --
578 IF j = 1 then
579 l_first_uom_code := l_CC_uom_code_table(j);
580 END IF;
581 -- Convert the uom if different from the 1st UOM
582 IF l_first_uom_code <> l_CC_uom_code_table(j) and nvl(l_CC_net_wt_table(j),0) > 0 then
583 l_CC_net_wt_table(j) := WSH_WV_UTILS.Convert_Uom (l_CC_uom_code_table(j),
584 l_first_uom_code,
585 l_CC_net_wt_table(j),
586 l_CC_item_id_table(j));
587 END IF;
588 -- Total
589 l_total_net_qty := l_total_net_qty + nvl(l_CC_net_wt_table(j),0) ;
590 --
591 j := l_CC_delivery_detail_table.next(j);
592 --}
593 END LOOP;-- End of WHILE j is not NULL
594 IF l_total_net_qty <= 0 then
595 WSH_DEBUG_SV.log(l_module_name,'Total net Qty for the contents of the Container id '||l_CNT_parent_cont_id_table(i) ||
596 ' s zero '||l_total_net_qty);
597 END IF;
598 --VALIDATION CHECK
599 IF l_CNT_total_cost_table(i) > 0 and l_total_net_qty <= 0 then
600 --{
601 IF l_debug_on THEN
602 WSH_DEBUG_SV.log(l_module_name,'Total Cost at the container level is ',l_CNT_total_cost_table(i));
603 WSH_DEBUG_SV.log(l_module_name,'Total net Qty for the contents of the Container id ',l_CNT_parent_cont_id_table(i) ||
604 ' s zero '||l_total_net_qty);
605 END IF;
606 --dbms_output.put_line('Total Qty of the container contents is zero ');
607 FND_MESSAGE.SET_NAME('FTE','FTE_EC_MISSING_DETAIL_NET_QTY');
608 FND_MESSAGE.SET_TOKEN('LOG_FILE',l_debugfile);
609 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
613 j := l_CC_delivery_detail_table.FIRST;
610 raise e_validation_error;
611 --}
612 END IF;
614 WHILE j is not NULL
615 LOOP
616 --{
617 -- Prorate the Total Cost into detail contents based on the net wt at the line item level.
618 -- Proration logic of the distribution of container level cost to the container contents
619 --For ex: Total Cost at Container Level is $100.00 (Net Wt - 100 LBS)
620 -- ITEM NAME NET WT RATE DISTRIBUTED
621 -- ITEM-A 50 LBS (50/105 * 100) = $47.62
622 -- ITEM-B 30 LBS (30/105 * 100) = $28.57
623 -- ITEM-C 25 LBS (25/105 * 100) = $23.81
624 -- Item Total 105 LBS $100.00
625 --
626 -- Calcualte the cost at item level
627 -- Store all container contents rates here (except LTL )
628 --
629 IF l_total_net_qty > 0 then
630 l_DET_total_cost_table(k) := (l_CC_net_wt_table(j)/l_total_net_qty) * l_CNT_total_cost_table(i);
631 --
632 IF l_debug_on THEN
633 WSH_DEBUG_SV.log(l_module_name,'Total cost for the rcv shipment line '||l_CC_rcv_ship_line_id_table(j)||' Cost-'
634 ||l_DET_total_cost_table(k) || ' '||l_CNT_wfc_corrency_code_table(i));
635 END IF;
636 IF l_CC_po_corrency_code_table(j) <> l_CNT_wfc_corrency_code_table(i) then
637 --{
638 l_DET_total_cost_table(k) :=GL_CURRENCY_API.convert_amount (
639 x_from_currency => l_CNT_wfc_corrency_code_table(i),
640 x_to_currency => l_CC_po_corrency_code_table(j),
641 x_conversion_date => sysdate,
642 x_amount => l_DET_total_cost_table(k));
643 IF l_debug_on THEN
644 --{
645 WSH_DEBUG_SV.log(l_module_name,'Total cost for the delivery detail after conversion '
646 ||l_DET_total_cost_table(k)||' '||l_CC_po_corrency_code_table(j));
647 --}
648 END IF;
649 --}
650 END IF;
651 --}
652 ELSE
653 --{
654 -- Assiging the cost as zero since the total container qty is zero
655 l_DET_total_cost_table(k) := 0;
656 --}
657 END IF;
658 --Store the remaining attributes at detail level
659 l_DET_vendor_id_table(k) := l_CC_vendor_id_table(j);
660 l_DET_vendor_site_id_table(k) := l_CC_vendor_site_id_table(j);
661 l_DET_po_line_id_table(k) := l_CC_po_line_id_table(j);
662 l_DET_po_line_loc_id_table(k) := l_CC_po_line_loc_id_table(j);
663 l_DET_rcv_ship_line_id_table(k) := l_CC_rcv_ship_line_id_table(j);
664 l_DET_po_corrency_code_table(k) := l_CC_po_corrency_code_table(j);
665 -- Added this since po and wfc currency are same since
666 -- it is converted at the time of total cost calc.
667 l_DET_wfc_corrency_code_table(k) := l_CC_po_corrency_code_table(j);
668 --
669 j := l_CC_delivery_detail_table.next(j);
670 k := k+ 1; --increment by 1 to store next item
671 --}
672 END LOOP; -- End of WHILE j is not NULL
673 -- End of l_CC_delivery_detail_table
674 i := l_CNT_parent_cont_id_table.next(i);
675 -- Go to the next container
676 --}
677 END LOOP;
678 --End of l_CNT_parent_cont_id_table table processing
679 --End of all container level rating pro-rated to the line level
680 --}
681 END IF; --End of container rates and allocation to the detail
682 -- Get all receipts that do not have any rates or partial rates or invalid rate (re-rate required)
683 l_NR_count := 0;
684 IF l_no_rate_del_count > 0 THEN
685 OPEN c_get_receipts_no_rates;
686 FETCH c_get_receipts_no_rates BULK COLLECT
687 INTO l_NR_vendor_id_table,
688 l_NR_vendor_site_id_table,
689 l_NR_po_line_id_table,
690 l_NR_po_line_loc_id_table,
691 l_NR_rcv_ship_line_id_table;
692 close c_get_receipts_no_rates;
693 l_NR_count := l_NR_rcv_ship_line_id_table.COUNT;
694 IF l_debug_on THEN
695 WSH_DEBUG_SV.log(l_module_name,'cursor c_get_receipts_no_rates - number '||
696 'of records found: ', l_NR_count);
697 END IF;
698 END IF;
699 --
700 --dbms_output.put_line('Building Output Table ');
701 -- Building Output Table for Non-Rates receipts
702 -- Storing all receipt lines, which do not have any rates or partial rates or dirty rates
703 -- Cost is zero for this case.
704 l_message := FND_MESSAGE.Get_String('FTE', 'FTE_EC_NO_RATES_AVAILABLE');
705 --dbms_output.put_line('Message Rate not found '||l_message);
706 -- Purge the table before storing receipt lines and cost
707 l_rcv_ship_lines_table.delete;
708 l_rcv_ship_cost_table.delete;
709 l_no_rates_delivery_flag := 'N';
710 --
711 IF l_NR_count > 0 THEN
712 --{ If there is any shipment lines with no rates
713 l_no_rates_delivery_flag := 'Y';
714 j := l_NR_rcv_ship_line_id_table.FIRST ;
715 WHILE j is not null
716 LOOP
717 --{
718 IF l_debug_on THEN
719 WSH_DEBUG_SV.logmsg(l_module_name,'Storing the following info w/ zero cost ');
720 WSH_DEBUG_SV.log(l_module_name,'p_shipment_header_id : ',p_shipment_header_id);
721 WSH_DEBUG_SV.log(l_module_name,'rcv shipment line id : ',l_NR_rcv_ship_line_id_table(j));
725 WSH_DEBUG_SV.log(l_module_name,'VENDOR_SITE_ID ',l_NR_vendor_site_id_table(j));
722 WSH_DEBUG_SV.log(l_module_name,'l_NR_po_line_id_table(j) : ',l_NR_po_line_id_table(j));
723 WSH_DEBUG_SV.log(l_module_name,'l_NR_po_line_loc_id_table(j) : ',l_NR_po_line_loc_id_table(j));
724 WSH_DEBUG_SV.log(l_module_name,'VENDOR_ID ',l_NR_vendor_id_table(j));
726 WSH_DEBUG_SV.log(l_module_name,'status','W');
727 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
728 END IF;
729 --PL/SQL table is building with the index of RCV Shipment Line Id
730 i := l_NR_rcv_ship_line_id_table(j);
731 l_receipt_lines_tab(i).RCV_SHIPMENT_LINE_ID := l_NR_rcv_ship_line_id_table(j);
732 l_receipt_lines_tab(i).VENDOR_ID := l_NR_vendor_id_table(j);
733 l_receipt_lines_tab(i).VENDOR_SITE_ID := l_NR_vendor_site_id_table(j);
734 l_receipt_lines_tab(i).CURRENCY_CODE := null;
735 l_receipt_lines_tab(i).TOTAL_COST := 0;
736 l_receipt_lines_tab(i).RETURN_STATUS := 'W';
737 l_receipt_lines_tab(i).MESSAGE_TEXT := l_message;
738
739 j := l_NR_rcv_ship_line_id_table.next(j);
740 --}
741 END LOOP; --End of WHILE j is not NULL
742 --}
743 END IF; --End of l_NR_po_line_id_table.COUNT > 0 validation
744 --
745 --dbms_output.put_line('End of No-Rates lines building, if any ');
746 --
747 -- Rates will be calculated by Either Matched Receipts and WDD / Mismatched
748
749 -- Mismatched CASE - Could not match the Receipt with Delivery Lines
750 -- Get the Rates for all PO lines for the given Shipment Header ( Mismatched Receipts/Shipments)
751 -- Get the Net Qty of all po lines
752 -- Distribute the the Rates based on the net qty of each receipt line
753
754 -- Matched CASE - Matched the Receipts w/ Delivery Lines
755 -- Get only TL Rates (only loose items)
756 --dbms_output.put_line('get receipt detail rates for shipment header id '||p_shipment_header_id);
757 --
758 OPEN c_get_receipts_detail_rates(p_shipment_header_id);
759 --dbms_output.put_line('after open cursor c_get_receipts_detail_rates for Rates');
760 FETCH c_get_receipts_detail_rates BULK COLLECT
761 INTO l_vendor_id_table,
762 l_vendor_site_id_table,
763 l_po_line_id_table,
764 l_po_line_loc_id_table,
765 l_rcv_ship_line_id_table,
766 l_po_corrency_code_table,
767 l_wfc_corrency_code_table,
768 l_ship_qty_uom_table,
769 l_ship_qty_table,
770 l_total_cost_table;
771 close c_get_receipts_detail_rates;
772 --dbms_output.put_line('after close cursor c_get_receipts_detail_rates for Rates '||l_rcv_ship_line_id_table.COUNT);
773 --
774 IF l_debug_on THEN
775 WSH_DEBUG_SV.log(l_module_name,'cursor c_get_receipts_detail_rates - Rates - number '||
776 'of records found: ', l_rcv_ship_line_id_table.COUNT);
777 END IF;
778 -- Validating that if there are rates exist for all modes or container
779 -- if l_vendor_id_table.count = 0 means there no rates availble for non-packed items except LTL
780 -- l_CNT_parent_cont_id_table.count = 0 means there is no rates available for packed items
781
782 if l_vendor_id_table.count = 0 and l_CNT_parent_cont_id_table.count = 0 then
783 --{
784 --dbms_output.put_line('Delivery w/ rates not found for shipment header id '||p_shipment_header_id);
785 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
786 l_number_of_warnings := l_number_of_warnings + 1;
787 FND_MESSAGE.SET_NAME('FTE','FTE_EC_RATES_NOT_AVAILABLE');
788 FND_MESSAGE.SET_TOKEN('LOG_FILE',l_debugfile);
789 raise e_validation_error;
790 --}
791 end if;
792 l_message := FND_MESSAGE.Get_String('FTE', 'FTE_EC_RATES_AVAILABLE');
793 --
794 --dbms_output.put_line('No of Rates lines '|| l_rcv_ship_line_id_table.count );
795 l_loop_counter := 1;
796 FOR l_loop_counter IN 1..2
797 LOOP
798 --{
799 --
800 -- loop counter = 1 for all modes rating except the container detail rating
801 -- loop counter = 2 for Container Detail rating
802 --
803 -- Assign the Container Detail Ratings to the same PL/SQL columns for the processing rather than seperate loop
804 IF l_loop_counter = 2 then
805 l_vendor_id_table := l_DET_vendor_id_table;
806 l_vendor_site_id_table := l_DET_vendor_site_id_table;
807 l_rcv_ship_line_id_table := l_DET_rcv_ship_line_id_table;
808 l_po_corrency_code_table := l_DET_po_corrency_code_table;
809 l_wfc_corrency_code_table := l_DET_wfc_corrency_code_table ;
810 l_total_cost_table := l_DET_total_cost_table;
811 --dbms_output.put_line('No of packed items -Rates lines '|| l_rcv_ship_line_id_table.count );
812 END IF;
813 IF l_debug_on THEN
814 WSH_DEBUG_SV.log(l_module_name,'LOOP Counter 1. All modes, 2. Container Detail ',l_loop_counter );
815 WSH_DEBUG_SV.log(l_module_name,'No of records to process : ',l_rcv_ship_line_id_table.COUNT );
816 END IF;
817 j := l_rcv_ship_line_id_table.FIRST;
818 WHILE j is NOT NULL
819 LOOP
820 --{
821 --dbms_output.put_line('Within Loop ');
822 -- Building the Output table with the index id as RCV Shipment Line Id
823 --i := l_rcv_ship_line_id_table(j);
824 --Converting the Curency if the pos currency is different from rated curency
825 -- and if the cost > 0
826 IF l_debug_on THEN
827 WSH_DEBUG_SV.log(l_module_name,'Process for RCV Shipment Line : ',l_rcv_ship_line_id_table(j) );
828 WSH_DEBUG_SV.log(l_module_name,'Compare Currencies PO currency : ',l_po_corrency_code_table(j) );
829 WSH_DEBUG_SV.log(l_module_name,'Compare Currencies Rate currency : ',l_wfc_corrency_code_table(j));
833 IF (l_po_corrency_code_table(j) <> l_wfc_corrency_code_table(j)) AND
830 WSH_DEBUG_SV.log(l_module_name,'Total Cost for conversion : ',l_total_cost_table(j));
831 END IF;
832 --dbms_output.put_line('Compare currency code '||l_po_corrency_code_table(j)||' and '||l_wfc_corrency_code_table(j));
834 nvl(l_total_cost_table(j),0) > 0 then
835 --{
836 --dbms_output.put_line('Converting currency code ');
837 l_total_cost_table(j) :=GL_CURRENCY_API.convert_amount(
838 l_wfc_corrency_code_table(j),
839 l_po_corrency_code_table(j),
840 SYSDATE,
841 'Corporate',
842 l_total_cost_table(j)
843 );
844 --}
845 END IF;
846 --dbms_output.put_line('Before purging RCV Tables ');
847 l_rcv_ship_lines_table.delete;
848 l_rcv_ship_cost_table.delete;
849 --dbms_output.put_line('After purging RCV Tables ');
850 -- Validating that shipment is matched or not
851 -- If the rcv shipment line is NULL, the shipment line is not matched
852 -- Need to call get_rcv_shipment_lines and pro-rated cost for each rcv shipment lines
853 if l_rcv_ship_line_id_table(j) is null then
854 --{
855 IF l_debug_on THEN
856 WSH_DEBUG_SV.log(l_module_name,'Calling get_rcv_shipment_lines API since RCV Shipment Line id is NULL : ',l_rcv_ship_line_id_table(j) );
857 WSH_DEBUG_SV.log(l_module_name,'Calling Parameters - P_PO_LINE_ID : ',l_po_line_id_table(j) );
858 WSH_DEBUG_SV.log(l_module_name,'Calling Parameters - P_PO_LINE_LOCATION_ID : ',l_po_line_loc_id_table(j) );
859 WSH_DEBUG_SV.log(l_module_name,'Calling Parameters - P_TOTAL_COST : ',l_total_cost_table(j) );
860 WSH_DEBUG_SV.log(l_module_name,'Calling Parameters - P_SHIP_QTY : ',l_ship_qty_table(j) );
861 WSH_DEBUG_SV.log(l_module_name,'Calling Parameters - P_SHIP_QTY_UOM : ',l_ship_qty_uom_table(j) );
862 END IF;
863 --dbms_output.put_line('RCV shipment line id is null, calling get_rcv_shipment_lines API to get the receipt lines');
864 -- get all rcv shipment lines for the given po line id and po line location id
865 get_rcv_shipment_lines (x_return_status => x_return_status,
866 P_SHIPMENT_HEADER_ID =>p_shipment_header_id,
867 P_PO_LINE_ID =>l_po_line_id_table(j),
868 P_PO_LINE_LOCATION_ID =>l_po_line_loc_id_table(j),
869 P_TOTAL_COST =>l_total_cost_table(j),
870 P_SHIP_QTY_UOM =>l_ship_qty_uom_table(j),
871 P_SHIP_QTY =>l_ship_qty_table(j),
872 X_RCV_SHIP_LINES_TABLE =>l_rcv_ship_lines_table,
873 X_RCV_SHIP_COST_TABLE =>l_rcv_ship_cost_table);
874 if x_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS then
875 --dbms_output.put_line('error while calling get_rcv_shipment_lines API');
876 FND_MESSAGE.SET_NAME('FTE','FTE_EC_ERROR_SHP_LN_API');
877 FND_MESSAGE.SET_TOKEN('LOG_FILE',l_debugfile);
878 IF l_debug_on THEN
879 WSH_DEBUG_SV.log(l_module_name,'Error after Calling get_rcv_shipment_lines API Status : ',x_return_status );
880 END IF;
881 raise e_validation_error;
882 end if;
883 --
884 --}
885 else
886 --{
887 IF l_debug_on THEN
888 WSH_DEBUG_SV.log(l_module_name,'store the one is matched into RCV_SHIP_LINES_TABLE ',l_rcv_ship_line_id_table(j));
889 END IF;
890 --dbms_output.put_line('RCV shipment line is not null ');
891 -- These are the matching receipts, store the one is matched into RCV_SHIP_LINES_TABLE, RCV_SHIP_COST_TABLE
892 l_rcv_ship_lines_table(1) := l_rcv_ship_line_id_table(j);
893 l_rcv_ship_cost_table(1) := l_total_cost_table(j);
894 --dbms_output.put_line('After storing shipment line and cost ');
895 --}
896 end if; --end of l_rcv_ship_line_id_table(j) is null
897 --
898 --dbms_output.put_line('validate l_rcv_ship_lines_table.COUNT > 0 ');
899 --
900 k := l_rcv_ship_lines_table.FIRST;
901 WHILE k is not NULL
902 LOOP
903 --{
904 i := l_rcv_ship_lines_table(k);
905 --dbms_output.put_line(' Rcv Shipment Line id '||i);
906 IF l_receipt_lines_tab.EXISTS(i) then
907 IF l_debug_on THEN
908 WSH_DEBUG_SV.log(l_module_name,'Rcv Shipment Line id -Status ',l_receipt_lines_tab(i).RETURN_STATUS);
909 WSH_DEBUG_SV.log(l_module_name,'Rcv Shipment Line id does exist and Existing cost from other deliveries ',l_receipt_lines_tab(i).TOTAL_COST);
910 WSH_DEBUG_SV.log(l_module_name,'New Cost from this delivery for the same Rcv Shipment Line id ',l_rcv_ship_cost_table(k));
911 END IF;
912 IF l_receipt_lines_tab(i).RETURN_STATUS ='S' THEN
913 l_receipt_lines_tab(i).TOTAL_COST := nvl(l_receipt_lines_tab(i).TOTAL_COST,0) + nvl(l_rcv_ship_cost_table(k),0);
914 IF l_debug_on THEN
915 WSH_DEBUG_SV.log(l_module_name,'Rcv Shipment Line id does exist and added the cost',l_rcv_ship_cost_table(k));
916 END IF;
917 ELSE
918 IF l_debug_on THEN
919 WSH_DEBUG_SV.log(l_module_name,'One of the Delivery does not have the valid rate against the same Rcv Shipment Line id, '||
920 ' so the rate against this receipt line is incorrect and set to zero ',l_receipt_lines_tab(i).TOTAL_COST);
921 END IF;
922 END IF;
923 ELSE
927 WSH_DEBUG_SV.log(l_module_name,'vendor site id ',l_vendor_site_id_table(j));
924 IF l_debug_on THEN
925 WSH_DEBUG_SV.log(l_module_name,'Storing output table for id ',i);
926 WSH_DEBUG_SV.log(l_module_name,'Vendor Id ',l_vendor_id_table(j));
928 WSH_DEBUG_SV.log(l_module_name,'rcv id ',l_RCV_SHIP_LINES_TABLE(k));
929 WSH_DEBUG_SV.log(l_module_name,'cost ',l_RCV_SHIP_COST_TABLE(k));
930 WSH_DEBUG_SV.log(l_module_name,'currency code ',l_po_corrency_code_table(j));
931 WSH_DEBUG_SV.log(l_module_name,'status','S');
932 WSH_DEBUG_SV.logmsg(l_module_name,'---------------------------------');
933 END IF;
934 --
935 l_receipt_lines_rec.VENDOR_ID := l_vendor_id_table(j);
936 l_receipt_lines_rec.VENDOR_SITE_ID := l_vendor_site_id_table(j);
937 l_receipt_lines_rec.RCV_SHIPMENT_LINE_ID := l_RCV_SHIP_LINES_TABLE(k);
938 l_receipt_lines_rec.CURRENCY_CODE := l_po_corrency_code_table(j);
939 l_receipt_lines_rec.TOTAL_COST := l_RCV_SHIP_COST_TABLE(k);
940 l_receipt_lines_rec.RETURN_STATUS := 'S';
941 l_receipt_lines_rec.MESSAGE_TEXT := l_message;
942
943 --dbms_output.put_line(' End of Storing output table ');
944
945 l_receipt_lines_tab(i) := l_receipt_lines_rec;
946
947 --dbms_output.put_line(' After Storing output table ');
948
949 --}
950 END IF; --End of l_receipt_lines_tab.EXIST
951 k := l_rcv_ship_lines_table.next(k);
952 --
953 --}
954 END LOOP; -- End of WHILE k is not NULL
955 --
956 j := l_rcv_ship_line_id_table.next(j);
957 --
958 --}
959 END LOOP; -- End of WHILE j is not NULL
960 --}
961 END LOOP; -- End of l_loop_counter IN 1..2
962 --Storing the variable to the output table x_receipt_lines_tab
963 --
964 x_receipt_lines_tab := l_receipt_lines_tab;
965 --
966 -- End of building the output table - X_RECEIPT_LINES_TAB
967 --
968 IF l_debug_on THEN
969 WSH_DEBUG_SV.logmsg(l_module_name,' --End of process --After Storing output table x_receipt_lines_tab--- ');
970 end if;
971 /* this last secton is for testing only and this will be removed after the UT */
972 --dbms_output.put_line(' Total receipt lines '||x_receipt_lines_tab.COUNT);
973 /*
974 i := x_receipt_lines_tab.FIRST ;
975 j := 1;
976 WHILE j <= x_receipt_lines_tab.COUNT
977 LOOP
978 insert into FTE_RECEIPT_LINE_RECORDS
979 values
980 ( x_receipt_lines_tab(i).VENDOR_ID,
981 x_receipt_lines_tab(i).VENDOR_SITE_ID,
982 x_receipt_lines_tab(i).RCV_SHIPMENT_LINE_ID,
983 x_receipt_lines_tab(i).CURRENCY_CODE,
984 x_receipt_lines_tab(i).TOTAL_COST,
985 x_receipt_lines_tab(i).RETURN_STATUS,
986 x_receipt_lines_tab(i).MESSAGE_TEXT );
987 i := x_receipt_lines_tab.NEXT(i);
988 j := j+1;
989 END LOOP;
990 COMMIT;
991 */
992 -- clearing the cache after each call.
993 delete from fte_estimate_rates_del_temp;
994 /* end of temporary section to be removed after UT */
995 --dbms_output.put_line(' just before calling api_post_call ');
996 wsh_util_core.api_post_call(
997 p_return_status =>x_return_status,
998 x_num_warnings =>l_number_of_warnings,
999 x_num_errors =>l_number_of_errors,
1000 p_msg_data =>l_msg_data);
1001 --dbms_output.put_line(' just after calling api_post_call ');
1002 IF l_number_of_errors > 0
1003 THEN
1004 x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1005 ELSIF l_number_of_warnings > 0
1006 THEN
1007 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1008 ELSE
1009 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1010 END IF;
1011
1012 FND_MSG_PUB.Count_And_Get
1013 (
1014 p_count => x_msg_count,
1015 p_data => x_msg_data,
1016 p_encoded => FND_API.G_FALSE
1017 );
1018 EXCEPTION
1019 WHEN FND_API.G_EXC_ERROR THEN
1020 ROLLBACK TO FTE_PO_INTEGRATION_GRP;
1021 x_return_status := FND_API.G_RET_STS_ERROR;
1022 FND_MSG_PUB.Count_And_Get
1023 (
1024 p_count => x_msg_count,
1025 p_data => x_msg_data,
1026 p_encoded => FND_API.G_FALSE
1027 );
1028 IF l_debug_on THEN
1029 WSH_DEBUG_SV.pop(l_module_name);
1030 END IF;
1031
1032 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1033 ROLLBACK TO FTE_PO_INTEGRATION_GRP;
1034 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1035 FND_MSG_PUB.Count_And_Get
1036 (
1037 p_count => x_msg_count,
1038 p_data => x_msg_data,
1039 p_encoded => FND_API.G_FALSE
1040 );
1041 IF l_debug_on THEN
1042 WSH_DEBUG_SV.pop(l_module_name);
1043 END IF;
1044 WHEN e_validation_error THEN
1045 ROLLBACK TO FTE_PO_INTEGRATION_GRP;
1046 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1047 FND_MSG_PUB.Count_And_Get
1048 (
1049 p_count => x_msg_count,
1050 p_data => x_msg_data,
1051 p_encoded => FND_API.G_FALSE
1052 );
1053 IF l_debug_on THEN
1054 WSH_DEBUG_SV.pop(l_module_name);
1055 END IF;
1056 WHEN OTHERS then
1060 wsh_util_core.default_handler('FTE_PO_INTEGRATION_GRP.GET_ESTIMATED_RATES');
1057 ROLLBACK TO FTE_PO_INTEGRATION_GRP;
1058 WSH_DEBUG_SV.logmsg(l_module_name,'End of process with error : '||sqlerrm);
1059 --dbms_output.put_line('Unhandled Exception '||sqlerrm );
1061 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1062 WSH_UTIL_CORE.ADD_MESSAGE(x_return_status);
1063 FND_MSG_PUB.Count_And_Get
1064 (
1065 p_count => x_msg_count,
1066 p_data => x_msg_data,
1067 p_encoded => FND_API.G_FALSE
1068 );
1069 IF l_debug_on THEN
1070 WSH_DEBUG_SV.pop(l_module_name);
1071 END IF;
1072 -- will be removed
1073 END GET_ESTIMATED_RATES;
1074
1075 /*----------------------------------------------------------------------------------------------
1076 This procedure will be used to get all corresponding Receipt Shipment Lines for the given
1077 Shipment Header, PO Line id, PO Line location id since
1078 rcv_shipment_line_id is not populated in WDD due to mismatching receipts against the shipment
1079 -- Once you get all receipts, will be pro-rated the total cost at po line location level to
1080 -- all receipts based on the qty
1081 Input Parameters
1082 -
1083 P_SHIPMENT_HEADER_ID - Shipment header id
1084 P_PO_LINE_ID - Po Line ID
1085 P_PO_LINE_LOCATION_ID - Po Line location id
1086 P_TOTAL_COST - Total Cost at this level to distribute to all receipts based on the qty received
1087 Out parameters
1088 X_RCV_SHIP_LINES_TABLE - Table of receipts
1089 X_RCV_SHIP_COST_TABLE - Cost associated to each receipts
1090
1091 -----------------------------------------------------------------------------------------------*/
1092 /* This API will be called only if the shipment does not match with receipts
1093 and WDD does not contain rcv_shipment_line_id
1094 This procedure will be used to get all receipt shipment lines and recd qty
1095 for the given shipment header id, po line id, po line location id
1096 Prorate the cost at po line location level to each receipt lines based on the qty
1097 */
1098
1099 PROCEDURE get_rcv_shipment_lines ( x_return_status OUT NOCOPY VARCHAR2,
1100 P_SHIPMENT_HEADER_ID IN VARCHAR2,
1101 P_PO_LINE_ID IN NUMBER,
1102 P_PO_LINE_LOCATION_ID IN NUMBER,
1103 P_TOTAL_COST IN NUMBER,
1104 P_SHIP_QTY_UOM IN VARCHAR2,
1105 P_SHIP_QTY IN NUMBER,
1106 X_RCV_SHIP_LINES_TABLE OUT NOCOPY FTE_PO_INTEGRATION_GRP.fte_number_table,
1107 X_RCV_SHIP_COST_TABLE OUT NOCOPY FTE_PO_INTEGRATION_GRP.fte_number_table) IS
1108
1109 -- Need to verify the item id and qty uom (or unit of measure )
1110 -- get the item id from wdd instead of rsl since it will be same for the same po line location id
1111 -- Get all receipt shipment lines from RCV_SHIPMENT_LINES for the given po_line_id and po_line_location_id
1112 cursor c_get_rcv_shipment_lines (l_shipment_header_id number, l_po_line_id number, l_po_line_loc_id number) IS
1113 select shipment_line_id,
1114 item_id,
1115 quantity_received,
1116 unit_of_measure,
1117 0 total_cost
1118 from rcv_shipment_lines
1119 where shipment_header_id = l_shipment_header_id
1120 and po_line_id = l_po_line_id
1121 and po_line_location_id = l_po_line_loc_id;
1122
1123 l_total_net_qty number;
1124 --l_debug_on BOOLEAN;
1125 l_uom_code varchar2(3);
1126 l_uom varchar2(25);
1127 h number;
1128
1129 e_validation_error EXCEPTION;
1130
1131 l_item_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
1132 l_qty_table FTE_PO_INTEGRATION_GRP.fte_number_table;
1133 l_uom_code_table FTE_PO_INTEGRATION_GRP.fte_varchar25_table;
1134 l_rcv_ship_line_id_table FTE_PO_INTEGRATION_GRP.fte_number_table;
1135
1136 l_sub_module_name CONSTANT VARCHAR2(100) := 'fte.plsql.' || G_PKG_NAME || '.' || 'GET_RCV_SHIPMENT_LINES';
1137 --
1138 BEGIN
1139 --dbms_output.put_line('You are calling GET_RCV_SHIPMENT_LINES API');
1140 /* Moved to global variable
1141 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1142 IF l_debug_on IS NULL
1143 THEN
1144 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1145 END IF;
1146 */
1147 OPEN c_get_rcv_shipment_lines(P_SHIPMENT_HEADER_ID,P_PO_LINE_ID,P_PO_LINE_LOCATION_ID);
1148 FETCH c_get_rcv_shipment_lines BULK COLLECT
1149 INTO x_rcv_ship_lines_table,
1150 l_item_id_table,
1151 l_qty_table,
1152 l_uom_code_table,
1153 x_rcv_ship_cost_table;
1154 close c_get_rcv_shipment_lines;
1155 --
1156 IF l_debug_on THEN
1157 WSH_DEBUG_SV.log(l_sub_module_name,'cursor c_get_rcv_shipment_lines - number '||
1158 'of records found: ', x_rcv_ship_lines_table.COUNT);
1159 END IF;
1160 IF nvl(P_SHIP_QTY,0) <= 0 then
1161 WSH_DEBUG_SV.logmsg(l_sub_module_name,'Total Ship Qty for the PO Line'||p_po_line_id||'-'||P_PO_LINE_LOCATION_ID||
1162 ' zero qty '||l_total_net_qty);
1163 --dbms_output.put_line('Total Ship Qty is zero ');
1164 FND_MESSAGE.SET_NAME('FTE','FTE_EC_SHIP_QTY_ZERO');
1165 -- x_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
1166 --dbms_output.put_line('Total Ship Qty for the given po line id, po line location id is zero ');
1167 -- raise e_validation_error;
1168 END IF;
1169 -- Only if P_TOTAL_COST is > 0 and Ship Qty > 0, distribute the cost to all receipts, otherwise just return all receipts with zero cost
1170 --
1171 IF nvl(P_TOTAL_COST,0) > 0 and nvl(P_SHIP_QTY,0) > 0 then
1172 --{
1173 -- Initializing the net wt
1174 l_total_net_qty := P_SHIP_QTY;
1175 l_uom_code := null;
1176 l_uom := null;
1177 --Total Recept qty is replaced with Total Shipment Qty from Shipment lines since Rec qty may not match with Ship.Qty
1178 --and Total cost needs to be distributed as given below
1179 h := x_rcv_ship_lines_table.FIRST ;
1180 WHILE h IS NOT NULL
1181 LOOP
1182 --{
1183 IF l_debug_on THEN
1184 WSH_DEBUG_SV.log(l_sub_module_name,'Rct Qty / UOM ',l_qty_table(h)||l_uom_code_table(h));
1185 END IF;
1186 -- get the uom code for the given unit of measure
1187 if l_uom_code_table(h) is not null and (P_SHIP_QTY_UOM <> l_uom_code_table(h)) then
1188 l_uom := l_uom_code_table(h);
1189 select uom_code into l_uom_code from MTL_UNITS_OF_MEASURE
1190 where UNIT_OF_MEASURE = l_uom;
1191 end if;
1192 -- Prorate the Total Cost into all receipt lines
1193 -- Proration logic of the distribution of po line level cost to all related receipts
1194 -- Proration logic is based on the total shipments qty versus each rec.qty
1195 --For ex: Total Cost at Po Line loc level is $100.00 (Net Wt - 100 LBS)
1196 -- Receipt# QTY RATE DISTRIBUTED
1197 -- Receipt-1 50 EA (50/105 * 100) = $47.62
1198 -- Receipt-2 30 EA (30/105 * 100) = $28.57
1199 -- Receipt-3 25 EA (25/105 * 100) = $23.81
1200 -- Receipt Total 105 EA $100.00
1201 --
1202 -- Calculate the cost at each receipt level
1203 -- Converting UOMs between Shipped Qty and Recd Qty, if different
1204 IF (P_SHIP_QTY_UOM <> l_uom_code) and nvl(l_qty_table(h),0) > 0 then
1205 -- Need to verify on this conversion
1206 l_qty_table(h) := WSH_WV_UTILS.Convert_Uom (l_uom_code,
1207 P_SHIP_QTY_UOM,
1208 l_qty_table(h),
1209 l_item_id_table(h));
1210 END IF;
1211 --
1212 --
1213 x_rcv_ship_cost_table(h) := (l_qty_table(h)/P_SHIP_QTY) * p_total_cost;
1214 --
1215 IF l_debug_on THEN
1216 WSH_DEBUG_SV.log(l_sub_module_name,'Rct Qty / Converted UOM ',l_qty_table(h)||l_uom_code);
1217 WSH_DEBUG_SV.log(l_sub_module_name,'Cost allocated for receipt line ',x_rcv_ship_lines_table(h)||
1218 ' Qty= '||l_qty_table(h)|| ' / '||l_total_net_qty|| ' Cost is '||x_rcv_ship_cost_table(h));
1219 END IF;
1220 --
1221 h := x_rcv_ship_lines_table.NEXT(h);
1222 --}
1223 END LOOP; --End of x_rcv_ship_lines_table Table
1224 --}
1225 END IF; -- nvl(P_TOTAL_COST,0) > 0
1226 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1227 --
1228 --dbms_output.put_line('End of getting rcv shipment lines for the non-matching shipments ');
1229
1230 EXCEPTION
1231 WHEN e_validation_error THEN
1232 null;
1233 WHEN no_data_found then
1234 WSH_DEBUG_SV.log(l_sub_module_name,'Could not find the UOM Code for the Rct Qty UOM ',l_qty_table(h)||l_uom);
1235 WSH_DEBUG_SV.log(l_sub_module_name,'Pls query the UOM table to verify the Unit of Measure exist or not',l_uom);
1236 x_return_status := WSH_UTIL_CORE.G_RET_STS_WARNING;
1237 WHEN others then
1238 wsh_util_core.default_handler('FTE_PO_INTEGRATION_GRP.GET_RCV_SHIPMENT_LINES API');
1239 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1240 --dbms_output.put_line('Unhandled Exception '||sqlerrm );
1241 END GET_RCV_SHIPMENT_LINES;
1242
1243 END FTE_PO_INTEGRATION_GRP;
[Home] [Help]