[Home] [Help]
PACKAGE BODY: APPS.WSH_CUST_MERGE
Source
1 PACKAGE BODY WSH_CUST_MERGE as
2 /* $Header: WSHCMRGB.pls 120.9.12010000.4 2009/12/03 16:10:33 gbhargav ship $ */
3
4 --
5 --
6 TYPE g_number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
7 TYPE g_rowid_tbl_type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
8 TYPE g_char_tbl_type IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
9 TYPE g_char_hash_string IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
10 TYPE PARAM_INFO_TAB_TYPE IS TABLE OF WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ INDEX BY BINARY_INTEGER;
11
12 G_WMS_ENABLED g_char_tbl_type;
13 G_PARAM_INFO_TAB PARAM_INFO_TAB_TYPE;
14 G_FTE_INSTALLED VARCHAR2(10);
15 G_DELIVERY_ID g_number_tbl_type;
16
17 -- For Debugging
18 G_PROFILE_VAL VARCHAR2(30);
19 --
20 -- Global Variables
21 G_MERGE_HEADER_ID NUMBER;
22 G_FROM_CUSTOMER_ID NUMBER;
23 G_FROM_CUST_SITE_ID NUMBER;
24 G_FROM_LOCATION_ID NUMBER;
25 G_TO_CUSTOMER_ID NUMBER;
26 G_TO_CUST_SITE_ID NUMBER;
27 G_TO_LOCATION_ID NUMBER;
28 G_SITE_USE_CODE RA_CUSTOMER_MERGES.Customer_Site_Code%TYPE;
29 G_BATCH_LIMIT CONSTANT NUMBER := 10000;
30
31
32 --
33 -- Return TimeStamp
34 --
35 FUNCTION getTimeStamp RETURN VARCHAR2
36 IS
37 BEGIN
38 RETURN TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS');
39 EXCEPTION
40 WHEN OTHERS THEN RAISE;
41 END getTimeStamp;
42
43 --
44 --
45 PROCEDURE setARMessageUpdateTable(p_tableName IN VARCHAR2)
46 IS
47 BEGIN
48 arp_message.set_name('AR','AR_UPDATING_TABLE');
49 arp_message.set_token('TABLE_NAME', p_tableName, FALSE);
50 EXCEPTION
51 WHEN OTHERS THEN RAISE;
52 END setARMessageUpdateTable;
53
54 --
55 --
56 PROCEDURE setARMessageLockTable(p_tableName IN VARCHAR2)
57 IS
58 BEGIN
59 arp_message.set_name('AR','AR_LOCKING_TABLE');
60 arp_message.set_token('TABLE_NAME', p_tableName, FALSE);
61 EXCEPTION
62 WHEN OTHERS THEN RAISE;
63 END setARMessageLockTable;
64
65
66 --
67 --
68 PROCEDURE setARMessageRowCount(p_rowCount IN NUMBER)
69 IS
70 BEGIN
71 arp_message.set_name('AR','AR_ROWS_UPDATED');
72 arp_message.set_token('NUM_ROWS', to_char(p_rowCount));
73 EXCEPTION
74 WHEN OTHERS THEN
75 RAISE;
76 END setARMessageRowCount;
77
78
79 --
80 --
81 -- Added for bug 4759811.
82 PROCEDURE setARMessageRowCount(p_mesgName IN VARCHAR, p_rowCount IN NUMBER)
83 IS
84 BEGIN
85 arp_message.set_name('AR', p_mesgName);
86 arp_message.set_token('NUM_ROWS', to_char(p_rowCount));
87 EXCEPTION
88 WHEN OTHERS THEN
89 RAISE;
90 END setARMessageRowCount;
91
92 --
93 --
94 -- Added for bug 4759811.
95 --
96 PROCEDURE Insert_Log_Table (
97 p_id_tab IN g_number_tbl_type,
98 p_table_name IN VARCHAR2,
99 p_req_id IN NUMBER,
100 x_return_status OUT NOCOPY VARCHAR2 )
101 IS
102 --
103 l_debug_on BOOLEAN;
104 --
105 BEGIN
106
107 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
108 --
109 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
110 --
111 IF l_debug_on IS NULL
112 THEN
113 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
114 END IF;
115 --
116 --
117 IF l_debug_on THEN
118 arp_message.set_line('WSH_CUST_MERGE.Insert_Log_Table()+' || getTimeStamp );
119 END IF;
120 --
121
122 -- Insert into Hz_Log_Table
123 FORALL InsCnt in p_id_tab.FIRST..p_id_tab.LAST
124 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
125 MERGE_LOG_ID,
126 TABLE_NAME,
127 MERGE_HEADER_ID,
128 PRIMARY_KEY_ID,
129 NUM_COL1_ORIG,
130 NUM_COL1_NEW,
131 NUM_COL2_ORIG,
132 NUM_COL2_NEW,
133 NUM_COL3_ORIG,
134 NUM_COL3_NEW,
135 NUM_COL4_ORIG,
136 NUM_COL4_NEW,
137 NUM_COL5_ORIG,
138 NUM_COL5_NEW,
139 ACTION_FLAG,
140 REQUEST_ID,
141 CREATED_BY,
142 CREATION_DATE,
143 LAST_UPDATE_LOGIN,
144 LAST_UPDATE_DATE,
145 LAST_UPDATED_BY )
146 VALUES (
147 HZ_CUSTOMER_MERGE_LOG_S.NextVal,
148 p_table_name,
149 G_MERGE_HEADER_ID,
150 p_id_tab(insCnt),
151 G_FROM_CUSTOMER_ID,
152 G_TO_CUSTOMER_ID,
153 G_FROM_CUST_SITE_ID,
154 G_TO_CUST_SITE_ID,
155 G_FROM_LOCATION_ID,
156 G_TO_LOCATION_ID,
157 NULL,
158 NULL,
159 NULL,
160 NULL,
161 'U',
162 p_req_id,
163 HZ_UTILITY_PUB.Created_By,
164 HZ_UTILITY_PUB.Creation_Date,
165 HZ_UTILITY_PUB.Last_Update_Login,
166 HZ_UTILITY_PUB.Last_Update_Date,
167 HZ_UTILITY_PUB.Last_Updated_By );
168
169 --
170 setARMessageRowCount( 'HZ_CUSTOMER_MERGE_LOG', SQL%ROWCOUNT );
171
172 --
173 IF l_debug_on THEN
174 arp_message.set_line('WSH_CUST_MERGE.Insert_Log_Table()+' || getTimeStamp );
175 END IF;
176 --
177
178 EXCEPTION
179 WHEN OTHERS THEN
180 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
181 --
182 IF l_debug_on THEN
183 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Insert_Log_Table()+ Others - ' || getTimeStamp);
184 ARP_MESSAGE.Set_Error('Eror Mesg : ' || sqlerrm );
185 END IF;
186 --
187 END Insert_Log_Table;
188
189
190 -----------------------------------------------------------------------------------------
191 -- PROCEDURE : ADJUST_WEIGHT_VOLUME
192 -- PARAMETERS :
193 -- p_entity_type CONT - While unassigning from Containers
194 -- DEL-CONT - While unassigning from Deliveries/LPN's
195 -- TRIP-STOP - While unassigning from Stop's
196 -- p_delivery_detail array of delivery detail id
197 -- p_parent_delivery_detail array of parent delivery detail id
198 -- p_delivery_id array of delivery id
199 -- p_delivery_leg_id array of delivery leg id
200 -- p_net_weight array of net weight
201 -- p_gross_weight array of gross weight
202 -- p_volume array of volume
203 -- x_return_status Returns the status of call
204 --
205 -- COMMENT :
206 -- Code is similar to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_From_Cont
207 -- when p_entity_type is 'CONT'
208 -- Code is similar to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_From_Delivery
209 -- when p_entity_type is 'DEL-CONT'
210 -----------------------------------------------------------------------------------------
211
212 PROCEDURE Adjust_Weight_Volume (
213 p_entity_type IN VARCHAR2,
214 p_delivery_detail IN g_number_tbl_type,
215 p_parent_delivery_detail IN g_number_tbl_type,
216 p_delivery_id IN g_number_tbl_type,
217 p_delivery_leg_id IN g_number_tbl_type,
218 p_net_weight IN g_number_tbl_type,
219 p_gross_weight IN g_number_tbl_type,
220 p_volume IN g_number_tbl_type,
221 x_return_status OUT NOCOPY VARCHAR2 )
222 IS
223 l_del_tab WSH_UTIL_CORE.Id_Tab_Type;
224 l_return_status VARCHAR2(10);
225
226 Weight_Volume_Exp EXCEPTION;
227
228 --
229 l_debug_on BOOLEAN;
230 --
231 BEGIN
232
233 x_return_status := FND_API.G_RET_STS_SUCCESS;
234 --
235 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
236 --
237 IF l_debug_on IS NULL THEN
238 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
239 END IF;
240
241 --
242 IF l_debug_on THEN
243 arp_message.set_line('WSH_CUST_MERGE.Adjust_Weight_Volume()+' || getTimeStamp );
244 END IF;
245
246 --
247 -- Call Mark_Reprice_Reqired, only when Unassigning from delivery
248 -- and FTE is Installed
249 --
250 IF ( p_entity_type = 'DEL-CONT' and G_FTE_INSTALLED = 'Y' ) THEN
251 -- { Mark Reprice
252 l_return_status := NULL;
253
254 IF ( p_delivery_id.COUNT > 0 ) THEN
255 FOR i in p_delivery_id.FIRST..p_delivery_id.LAST
256 LOOP
257 l_del_tab(i) := p_delivery_id(i);
258 END LOOP;
259 END IF;
260
261 WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required (
262 p_entity_type => 'DELIVERY',
263 p_entity_ids => l_del_tab,
264 x_return_status => l_return_status);
265
266 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
267 WSH_UTIL_CORE.G_RET_STS_WARNING) ) THEN
268 --
269 IF ( l_debug_on ) THEN
270 ARP_MESSAGE.Set_Error('API WSH_DELIVERY_LEGS_ACTIONS.Mark_Reprice_Required returned error');
271 END IF;
272 --
273 RAISE Weight_Volume_Exp;
274 END IF;
275 -- } Mark Reprice
276 END IF;
277
278 IF ( p_entity_type in ( 'CONT', 'DEL-CONT' ) )
279 THEN
280 -- { Entity type
281 -- Weight/Volume adjustments
282 FOR wvCnt IN p_delivery_detail.FIRST..p_delivery_detail.LAST
283 LOOP
284 -- { W/V adjustment Loop
285 -- Call WV API, If
286 -- 1. CONT
287 -- When Unassigning from container(i.e., delivery detail is assigned to
288 -- container but not assigned to a delivery.
289 -- OR
290 -- 2. DEL-CONT
291 -- When Unassigning from delivery
292 IF ( ( p_entity_type = 'CONT' AND
293 p_delivery_id(wvCnt) IS NULL AND
294 p_parent_delivery_detail(wvCnt) IS NOT NULL ) OR
295 ( p_entity_type = 'DEL-CONT' ) )
296 THEN
297 -- {
298 l_return_status := NULL;
299
300 WSH_WV_UTILS.DD_WV_Post_Process (
301 p_delivery_detail_id => p_delivery_detail(wvCnt),
302 p_diff_gross_wt => -1 * p_gross_weight(wvCnt),
303 p_diff_net_wt => -1 * p_net_weight(wvCnt),
304 p_diff_volume => -1 * p_volume(wvCnt),
305 p_diff_fill_volume => -1 * p_volume(wvCnt),
306 p_check_for_empty => 'Y',
307 x_return_status => l_return_status );
308
309 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
310 WSH_UTIL_CORE.G_RET_STS_WARNING) )
311 THEN
312 --
313 IF ( l_debug_on ) THEN
314 ARP_MESSAGE.Set_Error('API WSH_WV_UTILS.DD_WV_Post_Process returned error');
315 END IF;
316 --
317 RAISE Weight_Volume_Exp;
318 END IF;
319 -- }
320 END IF;
321 -- } W/V adjustment Loop
322 END LOOP;
323 -- } Entity Type
324 ELSIF ( p_entity_type = 'TRIP-STOP' )
325 THEN
326 -- Calling WV API, when unassigning delivery from a trip
327 FOR wvCnt IN p_delivery_id.FIRST..p_delivery_id.LAST
328 LOOP
329 l_return_status := NULL;
330
331 WSH_WV_UTILS.Del_WV_Post_Process(
332 p_delivery_id => p_delivery_id(wvCnt),
333 p_diff_gross_wt => -1 * p_gross_weight(wvCnt),
334 p_diff_net_wt => -1 * p_net_weight(wvCnt),
335 p_diff_volume => -1 * p_volume(wvCnt),
336 p_check_for_empty => 'Y',
337 p_leg_id => p_delivery_leg_id(wvCnt),
338 x_return_status => l_return_status);
339
340 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
341 WSH_UTIL_CORE.G_RET_STS_WARNING) )
342 THEN
343 --
344 IF ( l_debug_on ) THEN
345 ARP_MESSAGE.Set_Error('API WSH_WV_UTILS.Del_WV_Post_Process returned error');
346 END IF;
347 --
348 RAISE Weight_Volume_Exp;
349 END IF;
350
351 END LOOP;
352 END IF;
353
354 --
355 IF ( l_debug_on ) THEN
356 arp_message.set_line('WSH_CUST_MERGE.Adjust_Weight_Volume()+' || getTimeStamp );
357 END IF;
358 --
359
360 EXCEPTION
361 WHEN Weight_Volume_Exp THEN
362 x_return_status := l_return_status;
363 --
364 IF ( l_debug_on ) THEN
365 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Adjust_Weight_Volume()+ Weight_Volume_Exp - ' || getTimeStamp );
366 END IF;
367 --
368
369 WHEN OTHERS THEN
370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
371 --
372 IF ( l_debug_on ) THEN
373 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Adjust_Weight_Volume()+ Others - ' || getTimeStamp);
374 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
375 END IF;
376 --
377 END Adjust_Weight_Volume;
378 --
379 --
380 --
381 -- ===============================================================================
382 -- PROCEDURE : ADJUST_PARENT_WV
383 -- PARAMETERS :
384 -- p_entity_type CONT - While unassigning from Containers
385 -- DEL-CONT - While unassigning from Deliveries/LPN's
386 -- p_delivery_detail array of delivery detail id
387 -- p_parent_delivery_detail array of parent delivery detail id
388 -- p_delivery_id array of delivery id
389 -- p_inventory_item_id array inventory item id
390 -- p_organization_id array of organization id
391 -- p_weight_uom array of weight UOM code
392 -- p_volume_uom array of volume UOM code
393 -- x_return_status Returns the status of call
394 --
395 -- COMMENT :
396 -- Code is similar to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_From_Cont
397 -- when p_entity_type is 'CONT'
398 -- Code is similar to WSH_DELIVERY_DETAILS_ACTIONS.Unassign_Detail_From_Delivery
399 -- when p_entity_type is 'DEL-CONT'
400 -- ===============================================================================
401 PROCEDURE Adjust_Parent_WV (
402 p_entity_type IN VARCHAR2,
403 p_delivery_detail IN g_number_tbl_type,
404 p_parent_delivery_detail IN g_number_tbl_type,
405 p_delivery_id IN g_number_tbl_type,
406 p_inventory_item_id IN g_number_tbl_type,
407 p_organization_id IN g_number_tbl_type,
408 p_weight_uom IN g_char_tbl_type,
409 p_volume_uom IN g_char_tbl_type,
410 x_return_status OUT NOCOPY VARCHAR2 )
411 IS
412 l_param_info WSH_SHIPPING_PARAMS_PVT.Parameter_Rec_Typ;
413 l_return_status VARCHAR2(10);
414
415 Weight_Volume_Exp EXCEPTION;
416
417 --
418 l_debug_on BOOLEAN;
419 --
420 BEGIN
421
422 x_return_status := FND_API.G_RET_STS_SUCCESS;
423 --
424 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
425 --
426 IF l_debug_on IS NULL
427 THEN
428 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
429 END IF;
430 --
431 --
432 IF l_debug_on THEN
433 arp_message.set_line('WSH_CUST_MERGE.Adjust_Parent_WV()+' || getTimeStamp );
434 END IF;
435 --
436
437 -- Weight/Volume adjustments
438 FOR wvCnt IN p_delivery_detail.FIRST..p_delivery_detail.LAST
439 LOOP
440 -- { W/V adjustment Loop
441 -- Call WV API, If
442 -- 1. CONT
443 -- When Unassigning from container(i.e., delivery detail is assigned to
444 -- container but not assigned to a delivery.
445 -- OR
446 -- 2. DEL-CONT
447 -- When Unassigning from delivery
448 IF ( ( p_entity_type = 'CONT' AND
449 p_delivery_id(wvCnt) IS NULL AND
450 p_parent_delivery_detail(wvCnt) IS NOT NULL ) OR
451 ( p_entity_type = 'DEL-CONT' ) )
452 THEN
453 -- {
454 l_return_status := NULL;
455
456 IF ( NOT G_PARAM_INFO_TAB.EXISTS(p_organization_id(wvCnt)) )
457 THEN
458 l_return_status := NULL;
459
460 WSH_SHIPPING_PARAMS_PVT.Get(
461 p_organization_id => p_organization_id(wvCnt),
462 x_param_info => l_param_info,
463 x_return_status => l_return_status);
464
465 IF l_return_status = WSH_UTIL_CORE.G_RET_STS_ERROR THEN
466 --
467 IF ( l_debug_on ) THEN
468 ARP_MESSAGE.Set_Error('API WSH_SHIPPING_PARAMS_PVT.Get returned error');
469 END IF;
470 --
471 RAISE Weight_Volume_Exp;
472 END IF;
473
474 G_PARAM_INFO_TAB(p_organization_id(wvCnt)) := l_param_info;
475 END IF;
476
477 IF ( G_PARAM_INFO_TAB(p_organization_id(wvCnt)).Percent_Fill_Basis_Flag = 'Q' AND
478 ( ( p_entity_type = 'DEL-CONT' AND p_parent_delivery_detail(wvCnt) IS NOT NULL ) OR
479 ( p_entity_type = 'CONT' ) ) )
480 THEN
481 l_return_status := NULL;
482
483 WSH_WV_UTILS.Adjust_Parent_WV(
484 p_entity_type => 'CONTAINER',
485 p_entity_id => p_parent_delivery_detail(wvCnt),
486 p_gross_weight => 0,
487 p_net_weight => 0,
488 p_volume => 0,
489 p_filled_volume => 0,
490 p_wt_uom_code => p_weight_uom(wvCnt),
491 p_vol_uom_code => p_volume_uom(wvCnt),
492 p_inv_item_id => p_inventory_item_id(wvCnt),
493 x_return_status => l_return_status);
494
495 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
496 WSH_UTIL_CORE.G_RET_STS_WARNING) )
497 THEN
498 --
499 IF ( l_debug_on ) THEN
500 ARP_MESSAGE.Set_Error('API WSH_WV_UTILS.Adjust_Parent_WV returned error');
501 END IF;
502 --
503 RAISE Weight_Volume_Exp;
504 END IF;
505 END IF;
506 -- }
507 END IF;
508 -- } W/V adjustment Loop
509 END LOOP;
510
511 --
512 IF ( l_debug_on ) THEN
513 arp_message.set_line('WSH_CUST_MERGE.Adjust_Parent_WV()+' || getTimeStamp );
514 END IF;
515 --
516
517 EXCEPTION
518 WHEN Weight_Volume_Exp THEN
519 x_return_status := l_return_status;
520 --
521 IF ( l_debug_on ) THEN
522 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Adjust_Parent_WV()+ Weight_Volume_Exp - ' || getTimeStamp );
523 END IF;
524 --
525
526 WHEN OTHERS THEN
527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
528 --
529 IF ( l_debug_on ) THEN
530 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Adjust_Parent_WV()+ Others - ' || getTimeStamp );
531 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
532 END IF;
533 --
534 END Adjust_Parent_WV;
535
536 --
537 --
538 --
539 PROCEDURE Unassign_Details_From_Delivery (
540 p_req_id IN NUMBER,
541 x_return_status OUT NOCOPY VARCHAR2 )
542 IS
543
544 -- Cursor to fetch containers from which delivery details
545 -- are unassigned.
546 CURSOR Get_Cont_Unassign_Details (
547 p_customer_id NUMBER,
548 p_site_use_id NUMBER,
549 p_location_id NUMBER )
550 IS
551 SELECT Det.Delivery_Detail_Id, Det.Rowid,
552 Dlvy.Customer_Id
553 FROM Wsh_Delivery_Details Det,
554 Wsh_Delivery_Assignments DelAsgn,
555 Wsh_New_Deliveries Dlvy
556 WHERE Dlvy.Delivery_Id = DelAsgn.Delivery_Id
557 AND DelAsgn.Delivery_Detail_Id = Det.Delivery_Detail_Id
558 AND Det.Container_Flag = 'Y'
559 AND Det.Delivery_Detail_Id IN
560 ( SELECT Asgn.Parent_Delivery_Detail_Id
561 FROM Wsh_Delivery_Assignments Asgn
562 WHERE Asgn.Parent_Delivery_Detail_Id IS NOT NULL
563 CONNECT BY PRIOR Asgn.Parent_Delivery_Detail_Id = Asgn.Delivery_Detail_Id
564 START WITH Asgn.delivery_detail_id IN
565 ( SELECT Wdd.Delivery_Detail_Id
566 FROM WSH_DELIVERY_DETAILS Wdd,
567 WSH_DELIVERY_ASSIGNMENTS Wda,
568 WSH_NEW_DELIVERIES Wnd,
569 Wsh_Tmp Tmp
570 WHERE Wdd.Container_Flag = 'N'
571 AND Wdd.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
572 AND Wdd.Customer_id = p_customer_id
573 AND Wdd.Ship_To_Location_Id = p_location_id
574 AND Wdd.Ship_To_Site_Use_Id = p_site_use_id
575 AND Wdd.Delivery_Detail_Id = Wda.Delivery_Detail_Id
576 AND Wda.delivery_id is not null
577 AND Wda.Delivery_Id = Wnd.Delivery_Id
578 AND Wnd.Ultimate_Dropoff_Location_Id <> p_location_id
579 AND Wnd.Status_Code = 'OP'
580 AND Wnd.Delivery_Id = Tmp.Column1
581 AND exists
582 ( SELECT 'x'
583 FROM WSH_DELIVERY_ASSIGNMENTS WDA1,
584 WSH_DELIVERY_DETAILS WDD1
585 WHERE WDD1.DELIVERY_DETAIL_ID = WDA1.DELIVERY_DETAIL_ID
586 AND WDD1.Container_Flag = 'N'
587 AND WDA1.Delivery_Id = WND.Delivery_Id
588 AND WDD1.Ship_To_Location_id = WND.Ultimate_Dropoff_Location_Id ) ) )
589 FOR UPDATE OF Det.Delivery_Detail_Id NOWAIT;
590
591 -- Cursor to fetch delivery details which are to be unassigned from
592 -- Delivery/Container
593 CURSOR Get_Wsh_Unassign_Details (
594 p_customer_id NUMBER,
595 p_site_use_id NUMBER,
596 p_location_id NUMBER )
597 IS
598 SELECT Wda.rowid, Wfc.rowid, Wda.Delivery_id, Wnd.Name,
599 Wdd.Delivery_Detail_Id, Wda.Parent_Delivery_Detail_Id,
600 Wdd.Gross_Weight, Wdd.Net_Weight, Wdd.Volume,
601 Wdd.Weight_Uom_Code, Wdd.Volume_Uom_Code,
602 Wdd.Organization_Id, Wdd.Inventory_Item_Id,
603 Wdd.Move_Order_Line_Id, Wdd.Released_Status,
604 Wnd.Ignore_For_Planning -- OTM R12 : unassign delivery detail
605 FROM WSH_DELIVERY_DETAILS Wdd,
606 WSH_DELIVERY_ASSIGNMENTS Wda,
607 WSH_NEW_DELIVERIES Wnd,
608 Wsh_Freight_Costs Wfc,
609 Wsh_Tmp Tmp
610 WHERE Wdd.Container_Flag = 'N'
611 AND Wdd.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
612 AND Wdd.Customer_id = p_customer_id
613 AND Wdd.Ship_To_Location_Id = p_location_id
614 AND Wdd.Ship_To_Site_Use_Id = p_site_use_id
615 AND NVL(Wfc.Charge_Source_Code, 'PRICING_ENGINE') = 'PRICING_ENGINE'
616 AND Wfc.Delivery_Detail_Id (+) = Wdd.Delivery_Detail_Id
617 AND Wdd.Delivery_Detail_Id = Wda.Delivery_Detail_Id
618 AND Wda.delivery_id is not null
619 AND Wda.Delivery_Id = Wnd.Delivery_Id
620 AND Wnd.Ultimate_Dropoff_Location_Id <> p_location_id
621 AND Wnd.Status_Code = 'OP'
622 AND Wnd.Delivery_Id = Tmp.Column1
623 AND exists (
624 SELECT 'x'
625 FROM WSH_DELIVERY_ASSIGNMENTS WDA1,
626 WSH_DELIVERY_DETAILS WDD1
627 WHERE WDD1.DELIVERY_DETAIL_ID = WDA1.DELIVERY_DETAIL_ID
628 AND WDD1.Container_Flag = 'N'
629 AND WDA1.Delivery_Id = WND.Delivery_Id
630 AND WDD1.Ship_To_Location_id = WND.Ultimate_Dropoff_Location_Id)
631 FOR UPDATE OF Wda.Delivery_Detail_Id, Wfc.Freight_Cost_Id NOWAIT;
632
633 CURSOR Get_Grouping_Id is
634 SELECT Wsh_Delivery_Group_S.NEXTVAL
635 FROM Dual;
636
637 l_grossWeightTab g_number_tbl_type;
638 l_netWeightTab g_number_tbl_type;
639 l_volumeTab g_number_tbl_type;
640 l_deliveryDetailIdTab g_number_tbl_type;
641 l_parentDeliveryDetailIdTab g_number_tbl_type;
642 l_deliveryIdTab g_number_tbl_type;
643 l_inventoryItemIdTab g_number_tbl_type;
644 l_organizationIdTab g_number_tbl_type;
645 l_moveOrderLineIdTab g_number_tbl_type;
646 l_dummyTab g_number_tbl_type;
647 l_customerIdTab g_number_tbl_type;
648
649 l_deliveryNameTab g_char_tbl_type;
650 l_weightUomTab g_char_tbl_type;
651 l_volumeUomTab g_char_tbl_type;
652 l_releasedStatusTab g_char_tbl_type;
653 l_deliveryDetailRowidTab g_rowid_tbl_type;
654 l_deliveryAssgRowidTab g_rowid_tbl_type;
655 l_freightCostRowidTab g_rowid_tbl_type;
656
657 l_carton_grouping_id NUMBER;
658 l_exception_id NUMBER;
659 l_msg_count NUMBER;
660 l_msg_data VARCHAR2(32767);
661 l_return_status VARCHAR2(10);
662 l_message_text VARCHAR2(32767);
663 l_message_name VARCHAR2(50);
664
665 Unassign_Del_Exp EXCEPTION;
666
667 -- OTM R12 : unassign delivery detail
668 l_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
669 l_delivery_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
670 l_ignoreForPlanningTab g_char_tbl_type;
671 l_is_delivery_empty VARCHAR2(1);
672 l_index NUMBER;
673 l_count NUMBER;
674 l_gc3_is_installed VARCHAR2(1);
675 -- End of OTM R12 : unassign delivery detail
676
677 --
678 l_debug_on BOOLEAN;
679 --
680 BEGIN
681 --
682 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
683 --
684 IF l_debug_on IS NULL
685 THEN
686 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
687 END IF;
688 --
689 IF l_debug_on THEN
690 arp_message.set_line('WSH_CUST_MERGE.Unassign_Details_From_Delivery()+' || getTimeStamp );
691 END IF;
692 --
693
694 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
695
696 -- OTM R12
697 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
698
699 IF (l_gc3_is_installed IS NULL) THEN
700 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
701 END IF;
702 -- End of OTM R12
703
704 OPEN Get_Cont_Unassign_Details (
705 G_TO_CUSTOMER_ID,
706 G_TO_CUST_SITE_ID,
707 G_TO_LOCATION_ID );
708
709 LOOP
710 -- {
711 FETCH Get_Cont_Unassign_Details
712 BULK COLLECT INTO l_deliveryDetailIdTab,
713 l_deliveryDetailRowidTab,
714 l_customerIdTab
715 LIMIT G_BATCH_LIMIT;
716
717 IF ( l_deliveryDetailRowidTab.COUNT > 0 )
718 THEN
719 FORALL updCnt in l_deliveryDetailRowidTab.FIRST..l_deliveryDetailRowidTab.LAST
720 UPDATE Wsh_Delivery_Details Wdd
721 SET Customer_Id = l_customerIdTab(updCnt),
722 last_update_date = SYSDATE,
723 last_updated_by = fnd_global.user_id,
724 last_update_login = fnd_global.conc_login_id,
725 program_application_id = fnd_global.prog_appl_id,
726 program_id = fnd_global.conc_program_id,
727 program_update_date = SYSDATE
728 WHERE rowid = l_deliveryDetailRowidTab(updCnt);
729
730 --
731 setARMessageRowCount( 'WSH_DELIVERY_DETAILS', SQL%ROWCOUNT );
732 END IF;
733
734 EXIT WHEN Get_Cont_Unassign_Details%NOTFOUND;
735 -- }
736 END LOOP;
737
738 CLOSE Get_Cont_Unassign_Details;
739
740
741 OPEN Get_Wsh_Unassign_Details (
742 G_TO_CUSTOMER_ID,
743 G_TO_CUST_SITE_ID,
744 G_TO_LOCATION_ID );
745
746 LOOP
747 -- {
748 FETCH Get_Wsh_Unassign_Details
749 BULK COLLECT INTO l_deliveryAssgRowidTab,
750 l_freightCostRowidTab,
751 l_deliveryIdTab,
752 l_deliveryNameTab,
753 l_deliveryDetailIdTab,
754 l_parentDeliveryDetailIdTab,
755 l_grossWeightTab,
756 l_netWeightTab,
757 l_volumeTab,
758 l_weightUomTab,
759 l_volumeUomTab,
760 l_organizationIdTab,
761 l_inventoryItemIdTab,
762 l_moveOrderLineIdTab,
763 l_releasedStatusTab,
764 l_ignoreForPlanningTab -- OTM R12 : unassign delivery detail
765 LIMIT G_BATCH_LIMIT;
766
767 IF ( l_deliveryAssgRowidTab.COUNT > 0 )
768 THEN
769 -- {
770 l_return_status := NULL;
771 l_dummyTab.delete;
772
773 Adjust_Weight_Volume (
774 p_entity_type => 'DEL-CONT',
775 p_delivery_detail => l_deliveryDetailIdTab,
776 p_parent_delivery_detail => l_parentDeliveryDetailIdTab,
777 p_delivery_id => l_deliveryIdTab,
778 p_delivery_leg_id => l_dummyTab,
779 p_net_weight => l_netWeightTab,
780 p_gross_weight => l_grossWeightTab,
781 p_volume => l_volumeTab,
782 x_return_status => l_return_status );
783
784 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
785 WSH_UTIL_CORE.G_RET_STS_WARNING) )
786 THEN
787 --
788 IF ( l_debug_on ) THEN
789 ARP_MESSAGE.Set_Error('Error returned from API Adjust_Weight_Volume');
790 END IF;
791 --
792 RAISE Unassign_Del_Exp;
793 END IF;
794
795 FORALL unassignDelCnt IN l_deliveryAssgRowidTab.FIRST..l_deliveryAssgRowidTab.LAST
796 UPDATE WSH_DELIVERY_ASSIGNMENTS
797 SET parent_delivery_detail_id = null,
798 delivery_id = null,
799 last_update_date = SYSDATE,
800 last_updated_by = fnd_global.user_id,
801 last_update_login = fnd_global.conc_login_id,
802 program_application_id = fnd_global.prog_appl_id,
803 program_id = fnd_global.conc_program_id,
804 program_update_date = SYSDATE
805 WHERE rowid = l_deliveryAssgRowidTab(unassignDelCnt);
806
807 setARMessageRowCount( 'WSH_DELIVERY_ASSIGNMENTS', SQL%ROWCOUNT );
808
809 -- OTM R12 : unassign delivery detail
810 -- container_flag is always 'N' for rows in l_deliveryAssgRowidTab
811 --
812 IF (l_gc3_is_installed = 'Y') THEN
813
814 -- following need to be initialized since they can be reused within
815 -- this loop by G_BATCH_LIMIT
816
817 l_count := 0;
818 l_delivery_id_tab.DELETE;
819 l_interface_flag_tab.DELETE;
820
821 l_index := l_deliveryIdTab.FIRST;
822 WHILE (l_index IS NOT NULL) LOOP
823 IF (nvl(l_ignoreForPlanningTab(l_index), 'N') = 'N') THEN
824
825 -- it is possible that l_delivery_id_tab contains more than one
826 -- entries with the same delivery id if l_deliveryIdTab does
827
828 l_is_delivery_empty := WSH_NEW_DELIVERY_ACTIONS.IS_DELIVERY_EMPTY(l_deliveryIdTab(l_index));
829
830 IF (l_is_delivery_empty = WSH_UTIL_CORE.G_RET_STS_ERROR) THEN
831 IF ( l_debug_on ) THEN
832 ARP_MESSAGE.Set_Error('Error returned from API wsh_new_delivery_actions.is_delivery_empty');
833 END IF;
834 raise FND_API.G_EXC_ERROR;
835 ELSIF (l_is_delivery_empty = 'Y') THEN
836 l_count := l_count + 1;
837 l_delivery_id_tab(l_count) := l_deliveryIdTab(l_index);
838 l_interface_flag_tab(l_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED;
839 IF (l_debug_on) THEN
840 arp_message.set_line('l_count-'||l_count||' l_delivery_id_tab-'||l_delivery_id_tab(l_count)||' l_interface_flag_tab-'||l_interface_flag_tab(l_count));
841 END IF;
842 ELSIF (l_is_delivery_empty = 'N') THEN
843 l_count := l_count + 1;
844 l_delivery_id_tab(l_count) := l_deliveryIdTab(l_index);
845 l_interface_flag_tab(l_count) := NULL;
846 --Bug7608629
847 --removed code which checked for gross weight
848 --now irrespective of gross weight UPDATE_TMS_INTERFACE_FLAG will be called
849 IF (l_debug_on) THEN
850 arp_message.set_line('l_count-'||l_count||' l_delivery_id_tab-'||l_delivery_id_tab(l_count)||' l_interface_flag_tab-'||l_interface_flag_tab(l_count));
851 END IF;
852 END IF;
853
854 END IF;
855 l_index := l_deliveryIdTab.NEXT(l_index);
856 END LOOP;
857
858 IF l_count > 0 THEN
859 WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG(
860 p_delivery_id_tab => l_delivery_id_tab,
861 p_tms_interface_flag_tab => l_interface_flag_tab,
862 x_return_status => l_return_status);
863
864 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
865 --
866 IF l_debug_on THEN
867 ARP_MESSAGE.Set_Error('Error returned from API WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG '||l_return_status);
868 END IF;
869 RAISE Unassign_Del_Exp;
870 END IF;
871 END IF;
872
873 END IF;
874 -- End of OTM R12 : unassign delivery detail
875
876
877 -- For WMS Enabled org and Release to Warehouse lines
878 IF ( l_moveOrderLineIdTab.COUNT > 0 )
879 THEN
880 -- { WMS Loop
881 FOR wmsCnt IN l_moveOrderLineIdTab.FIRST..l_moveOrderLineIdTab.LAST
882 LOOP
883 IF ( l_releasedStatusTab(wmsCnt) = 'S' AND
884 l_moveOrderLineIdTab(wmsCnt) is not null )
885 THEN
886 -- { Released Status If
887 IF ( NOT G_WMS_ENABLED.EXISTS(l_organizationIdTab(wmsCnt)) )
888 THEN
889 G_WMS_ENABLED(l_organizationIdTab(wmsCnt)) := WSH_UTIL_VALIDATE.Check_Wms_Org(l_organizationIdTab(wmsCnt));
890 END IF;
891
892 IF ( G_WMS_ENABLED(l_organizationIdTab(wmsCnt)) = 'Y' )
893 THEN
894 l_return_status := NULL;
895 l_msg_count := NULL;
896 l_msg_count := NULL;
897
898 OPEN Get_Grouping_Id;
899 FETCH Get_Grouping_Id INTO l_carton_grouping_id;
900 IF ( Get_Grouping_Id%NOTFOUND )
901 THEN
902 ARP_MESSAGE.Set_Error('Not able to fetch carton_grouping_id');
903 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
904 CLOSE Get_Grouping_Id;
905 --
906 RAISE Unassign_Del_Exp;
907 --
908 END IF;
909 CLOSE Get_Grouping_Id;
910
911 INV_MO_Cancel_PVT.Update_Mol_Carton_Group (
912 x_return_status => l_return_status,
913 x_msg_cnt => l_msg_count,
914 x_msg_data => l_msg_data,
915 p_line_id => l_moveOrderLineIdTab(wmsCnt),
916 p_carton_grouping_id => l_carton_grouping_id );
917
918 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
919 WSH_UTIL_CORE.G_RET_STS_WARNING) )
920 THEN
921 --
922 IF ( l_debug_on ) THEN
923 ARP_MESSAGE.Set_Error('Error returned from API INV_MO_Cancel_PVT.Update_Mol_Carton_Group');
924 END IF;
925 --
926 RAISE Unassign_Del_Exp;
927 END IF;
928 END IF;
929 -- } Released Status If
930 END IF;
931 END LOOP;
932 -- } WMS If
933 END IF;
934 -- For WMS Enabled org and Release to Warehouse lines
935
936 l_return_status := NULL;
937
938 Adjust_Parent_WV (
939 p_entity_type => 'DEL-CONT',
940 p_delivery_detail => l_deliveryDetailIdTab,
941 p_parent_delivery_detail => l_parentDeliveryDetailIdTab,
942 p_delivery_id => l_deliveryIdTab,
943 p_inventory_item_id => l_inventoryItemIdTab,
944 p_organization_id => l_organizationIdTab,
945 p_weight_uom => l_weightUomTab,
946 p_volume_uom => l_volumeUomTab,
947 x_return_status => l_return_status );
948
949 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
950 WSH_UTIL_CORE.G_RET_STS_WARNING) )
951 THEN
952 --
953 IF ( l_debug_on ) THEN
954 ARP_MESSAGE.Set_Error('Error returned from API Adjust_Parent_WV');
955 END IF;
956 --
957 RAISE Unassign_Del_Exp;
958 END IF;
959
960 IF ( G_FTE_INSTALLED = 'Y' AND
961 l_freightCostRowidTab.COUNT > 0 )
962 THEN
963 FORALL delCnt in l_freightCostRowidTab.FIRST..l_freightCostRowidTab.LAST
964 DELETE FROM Wsh_Freight_Costs
965 WHERE Rowid = l_freightCostRowidTab(delCnt);
966 END IF;
967
968 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
969 THEN
970 l_return_status := NULL;
971
972 Insert_Log_Table (
973 p_id_tab => l_deliveryDetailIdTab,
974 p_table_name => 'WSH_DELIVERY_ASSIGNMENTS',
975 p_req_id => p_req_id,
976 x_return_status => l_return_status );
977
978 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
979 THEN
980 Raise Unassign_Del_Exp;
981 END IF;
982 END IF;
983
984 IF ( l_deliveryIdTab.COUNT > 0 )
985 THEN
986 FORALL delCnt in l_deliveryIdTab.FIRST..l_deliveryIdTab.LAST
987 DELETE FROM wsh_tmp WHERE column1 = l_deliveryIdTab(delCnt);
988 END IF;
989 -- }
990 END IF;
991
992 IF ( l_deliveryIdTab.COUNT > 0 )
993 THEN -- { Log Exception
994 l_message_name := 'WSH_CMRG_UNASSIGN_DELIVERY';
995
996 FOR expCnt in l_deliveryIdTab.FIRST..l_deliveryIdTab.LAST
997 LOOP
998 -- { Loop for logging Expceptions
999 -- Setting the Messages
1000 FND_MESSAGE.Set_Name ('WSH', l_message_name );
1001 FND_MESSAGE.Set_Token ('PS1', G_FROM_CUST_SITE_ID );
1002 FND_MESSAGE.Set_Token ('PS2', G_TO_CUST_SITE_ID );
1003 FND_MESSAGE.Set_Token ('DELIVERY_DETAIL_ID', l_deliveryDetailIdTab(expCnt) );
1004
1005 l_message_text := FND_MESSAGE.Get;
1006
1007 l_return_status := NULL;
1008 l_msg_count := NULL;
1009 l_msg_data := NULL;
1010 l_exception_id := NULL;
1011
1012 WSH_XC_UTIL.Log_Exception
1013 (
1014 p_api_version => 1.0,
1015 x_return_status => l_return_status,
1016 x_msg_count => l_msg_count,
1017 x_msg_data => l_msg_data,
1018 x_exception_id => l_exception_id,
1019 p_exception_location_id => G_TO_LOCATION_ID,
1020 p_logged_at_location_id => G_TO_LOCATION_ID,
1021 p_logging_entity => 'SHIPPER',
1022 p_logging_entity_id => Fnd_Global.user_id,
1023 p_exception_name => 'WSH_CUSTOMER_MERGE_CHANGE',
1024 p_message => l_message_text,
1025 p_severity => 'LOW',
1026 p_manually_logged => 'N',
1027 p_delivery_id => l_deliveryIdTab(expCnt),
1028 p_delivery_name => l_deliveryNameTab(expCnt),
1029 p_error_message => l_message_text
1030 );
1031
1032 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1033 THEN
1034 ARP_MESSAGE.Set_Error('API WSH_XC_UTIL.Log_Exception returned error..');
1035 RAISE Unassign_Del_Exp;
1036 END IF;
1037
1038 -- }
1039 END LOOP;
1040 -- }
1041 END IF;
1042
1043 EXIT WHEN Get_Wsh_Unassign_Details%NOTFOUND;
1044 -- }
1045 END LOOP;
1046
1047 CLOSE Get_Wsh_Unassign_Details;
1048 --
1049 IF l_debug_on THEN
1050 arp_message.set_line('WSH_CUST_MERGE.Unassign_Details_From_Delivery()+' || getTimeStamp );
1051 END IF;
1052 --
1053 EXCEPTION
1054 WHEN Unassign_Del_Exp THEN
1055 x_return_status := l_return_status;
1056 --
1057 IF ( l_debug_on ) THEN
1058 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Unassign_Details_From_Delivery()+ Unassign_Del_Exp - ' || getTimeStamp );
1059 END IF;
1060 --
1061 IF ( Get_Cont_Unassign_Details%ISOPEN ) THEN
1062 CLOSE Get_Cont_Unassign_Details;
1063 END IF;
1064
1065 IF ( Get_Wsh_Unassign_Details%ISOPEN ) THEN
1066 CLOSE Get_Wsh_Unassign_Details;
1067 END IF;
1068
1069 IF ( Get_Grouping_Id%ISOPEN ) THEN
1070 CLOSE Get_Grouping_Id;
1071 END IF;
1072 --
1073 WHEN OTHERS THEN
1074 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1075 --
1076 IF ( l_debug_on ) THEN
1077 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Unassign_Details_From_Delivery()+ Others - ' || getTimeStamp );
1078 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
1079 END IF;
1080 --
1081 IF ( Get_Cont_Unassign_Details%ISOPEN ) THEN
1082 CLOSE Get_Cont_Unassign_Details;
1083 END IF;
1084
1085 IF ( Get_Wsh_Unassign_Details%ISOPEN ) THEN
1086 CLOSE Get_Wsh_Unassign_Details;
1087 END IF;
1088
1089 IF ( Get_Grouping_Id%ISOPEN ) THEN
1090 CLOSE Get_Grouping_Id;
1091 END IF;
1092 --
1093 END Unassign_Details_From_Delivery;
1094
1095 -----------------------------------------------------------------------------------------
1096 --
1097 -- PROCEDURE : GET_DELIVERY_HASH
1098 --
1099 -- DESCRIPTION :
1100 -- Get_Delivery_Hash generates new hash value and hash string for
1101 -- deliveries(from wsh_tmp table) which are to be updated with new
1102 -- Customer/Location ids
1103 --
1104 -- PARAMETERS :
1105 -- x_hash_string_tab => Contains array of Hash String for deliveries
1106 -- x_hash_value_tab => Contains array of Hash String for deliveries
1107 -- x_delivery_id_tab => Contains array of delivery ids
1108 -- x_return_status => Return status of API
1109 -----------------------------------------------------------------------------------------
1110
1111 PROCEDURE Get_Delivery_Hash (
1112 x_hash_string_tab OUT NOCOPY g_char_hash_string,
1113 x_hash_value_tab OUT NOCOPY g_number_tbl_type,
1114 x_delivery_id_tab OUT NOCOPY g_number_tbl_type,
1115 x_return_status OUT NOCOPY VARCHAR2 )
1116 IS
1117 CURSOR Get_Tmp_Deliveries
1118 IS
1119 SELECT to_number(Column1) delivery_id, Column3
1120 FROM Wsh_Tmp;
1121
1122 l_grp_attr_tab_type WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
1123 l_action_code VARCHAR2(30);
1124 l_return_status VARCHAR2(1);
1125 l_hash_count NUMBER;
1126
1127 Update_Hash_Exp EXCEPTION;
1128
1129 --
1130 l_debug_on BOOLEAN;
1131 --
1132 BEGIN
1133 --
1134 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1135 --
1136 IF l_debug_on IS NULL
1137 THEN
1138 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1139 END IF;
1140 --
1141 IF l_debug_on THEN
1142 arp_message.set_line('WSH_CUST_MERGE.Get_Delivery_Hash()+' || getTimeStamp );
1143 END IF;
1144 --
1145
1146 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1147 l_hash_count := 0;
1148
1149 FOR i IN Get_Tmp_Deliveries
1150 LOOP
1151 -- {
1152 IF ( NOT G_DELIVERY_ID.EXISTS(i.delivery_id) )
1153 THEN
1154 -- {
1155 l_grp_attr_tab_type(1).Entity_Type := 'DELIVERY_DETAIL';
1156 l_grp_attr_tab_type(1).Entity_Id := i.Column3;
1157
1158 WSH_DELIVERY_AUTOCREATE.Create_Hash (
1159 p_grouping_attributes => l_grp_attr_tab_type,
1160 p_group_by_header => 'N',
1161 p_action_code => l_action_code,
1162 x_return_status => l_return_status );
1163
1164 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
1165 WSH_UTIL_CORE.G_RET_STS_WARNING) )
1166 THEN
1167 --
1168 IF ( l_debug_on ) THEN
1169 ARP_MESSAGE.Set_Error('Error returned from API Create_Hash');
1170 END IF;
1171 --
1172 RAISE Update_Hash_Exp;
1173 END IF;
1174
1175 l_hash_count := l_hash_count + 1;
1176 x_hash_string_tab(l_hash_count) := l_grp_attr_tab_type(1).l1_hash_string;
1177 x_hash_value_tab(l_hash_count) := l_grp_attr_tab_type(1).l1_hash_value;
1178 x_delivery_id_tab(l_hash_count) := i.delivery_id;
1179 G_DELIVERY_ID(i.delivery_id) := i.delivery_id;
1180 -- }
1181 END IF;
1182 -- }
1183 END LOOP;
1184
1185 --
1186 IF l_debug_on THEN
1187 arp_message.set_line('WSH_CUST_MERGE.Get_Delivery_Hash()+' || getTimeStamp );
1188 END IF;
1189 --
1190 EXCEPTION
1191 WHEN Update_Hash_Exp THEN
1192 x_return_status := l_return_status;
1193 --
1194 IF ( l_debug_on ) THEN
1195 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Get_Delivery_Hash()+ Update_Hash_Exp - ' || getTimeStamp );
1196 END IF;
1197 --
1198 WHEN OTHERS THEN
1199 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1200 --
1201 IF ( l_debug_on ) THEN
1202 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Get_Delivery_Hash()+ Others - ' || getTimeStamp );
1203 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
1204 END IF;
1205 --
1206 END Get_Delivery_Hash;
1207 --
1208 --
1209 --
1210 --
1211 PROCEDURE Process_Deliveries (
1212 p_req_id IN NUMBER,
1213 x_return_status OUT NOCOPY VARCHAR2 )
1214 IS
1215 -- Cursor to fetch Container record details for delivery lines
1216 -- which are assigned to delivery
1217 CURSOR Get_Delivery_Containers
1218 IS
1219 SELECT Wdd.Delivery_Detail_Id, Wdd.Rowid
1220 FROM Wsh_Delivery_Details Wdd,
1221 Wsh_Delivery_Assignments Wda,
1222 Wsh_Tmp Tmp
1223 WHERE Wdd.Container_Flag = 'Y'
1224 AND Wdd.Delivery_Detail_Id = Wda.Parent_Delivery_Detail_Id
1225 AND Parent_Delivery_Detail_Id IS NOT NULL
1226 AND Wda.Delivery_Id = Tmp.Column1
1227 FOR UPDATE OF Wda.Delivery_Detail_Id NOWAIT;
1228
1229 -- Cursor to fetch deliveries which are to be Unassigned from Trip Stops
1230 CURSOR Get_Del_Unassign_From_Stop ( p_location_id NUMBER )
1231 IS
1232 SELECT Wdl.Delivery_Id, Wdl.Drop_Off_Stop_Id, Wts.Trip_Id,
1233 Wdl.Delivery_Leg_Id, Wnd.Gross_Weight, Wnd.Net_Weight,
1234 Wnd.Volume, Wdl.Rowid, Tmp.Rowid,
1235 NVL(Wnd.ignore_for_planning, 'N') --OTM R12
1236 FROM Wsh_New_Deliveries Wnd,
1237 Wsh_Delivery_Legs Wdl,
1238 Wsh_Trip_Stops Wts,
1239 Wsh_Tmp Tmp
1240 WHERE Wnd.Ultimate_Dropoff_Location_Id = p_location_id
1241 AND Wts.Stop_Id = Wdl.Drop_Off_Stop_Id
1242 AND Wnd.Delivery_Id = Wdl.Delivery_Id
1243 AND Wdl.Delivery_Id = Tmp.Column1
1244 AND exists (
1245 SELECT 'x'
1246 FROM Wsh_New_Deliveries Del,
1247 Wsh_Delivery_Legs Legs
1248 WHERE Del.Ultimate_Dropoff_Location_Id <> p_location_id
1249 AND Del.Delivery_Id = Legs.Delivery_Id
1250 AND Legs.Drop_Off_Stop_Id = Wdl.Drop_Off_Stop_Id
1251 )
1252 FOR UPDATE OF Wdl.Delivery_Leg_Id NOWAIT;
1253
1254 -- OTM R12 : customer merge
1255 -- getting deliveries on the trip where another delivery is unassigned
1256 -- these deliveries will be set to AW
1257 CURSOR c_get_deliveries (p_trip_id IN NUMBER,p_exclude_dlvy IN NUMBER) IS
1258 SELECT wdl.delivery_id
1259 FROM wsh_delivery_legs wdl,
1260 wsh_trip_stops wts,
1261 wsh_new_deliveries wnd
1262 WHERE wdl.pick_up_stop_id = wts.stop_id
1263 AND wts.trip_id = p_trip_id
1264 AND wdl.delivery_id = wnd.delivery_id
1265 AND wnd.status_code = 'OP'
1266 AND wnd.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED
1267 AND wnd.delivery_id <> p_exclude_dlvy;
1268
1269 -- getting empty deliveries that belongs to the customer and matches the drop
1270 -- off location id
1271 CURSOR c_get_empty_deliveries(p_customer_id IN NUMBER, p_dropoff_location_id IN NUMBER) IS
1272 SELECT wnd.delivery_id
1273 FROM wsh_new_deliveries wnd
1274 WHERE NVL(wnd.Customer_Id, p_customer_id) = p_customer_id
1275 AND wnd.ultimate_dropoff_location_Id = p_dropoff_location_id
1276 AND wnd.status_code = 'OP'
1277 AND NOT EXISTS
1278 (
1279 SELECT 1
1280 FROM wsh_delivery_assignments wda,
1281 wsh_delivery_details wdd
1282 WHERE wda.delivery_id = wnd.delivery_id
1283 AND wda.delivery_detail_id = wdd.delivery_detail_id
1284 AND wdd.container_flag = 'N'
1285 );
1286
1287 -- getting freight cost type id for the OTM freight cost
1288 CURSOR c_get_frcost_type_id IS
1289 SELECT freight_cost_type_id
1290 FROM wsh_freight_cost_types
1291 WHERE name = 'OTM Freight Cost'
1292 AND freight_cost_type_code = 'FREIGHT';
1293
1294 l_trip_id NUMBER;
1295 l_trip_status WSH_TRIPS.STATUS_CODE%TYPE;
1296 l_aw_dlvy_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1297 l_aw_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1298 l_frcost_type_id NUMBER;
1299
1300 l_skip VARCHAR2(1);
1301 l_dlvy_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1302 l_gc3_is_installed VARCHAR2(1);
1303
1304 frcost_not_found EXCEPTION;
1305 l_tms_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1306 l_tms_trip_tab WSH_DELIVERY_VALIDATIONS.trip_info_tab_type;
1307 l_new_delivery_leg_tab g_rowid_tbl_type;
1308 l_delivery_leg_count NUMBER;
1309 l_ignoreTab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1310 l_tms_count NUMBER;
1311 l_tms_delivery_id_tab g_number_tbl_type;
1312
1313 -- End of OTM R12 : customer merge
1314
1315 -- OTM R12 : update delivery
1316 l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1317 l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
1318 l_new_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1319 l_tms_update WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1320 l_trip_not_found VARCHAR2(1);
1321 l_trip_info_rec WSH_DELIVERY_VALIDATIONS.trip_info_rec_type;
1322 l_tms_version_number g_number_tbl_type;
1323 l_tms_interface_flag WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1324 l_index NUMBER;
1325 l_delivery_count NUMBER;
1326 -- End of OTM R12 : update delivery
1327
1328 l_deliveryDetailIdTab g_number_tbl_type;
1329 l_deliveryLegIdTab g_number_tbl_type;
1330 l_deliveryIdTab g_number_tbl_type;
1331 l_stopIdTab g_number_tbl_type;
1332 l_tripIdTab g_number_tbl_type;
1333 l_grossWeightTab g_number_tbl_type;
1334 l_netWeightTab g_number_tbl_type;
1335 l_volumeTab g_number_tbl_type;
1336 l_dummyIdTab g_number_tbl_type;
1337 l_hash_value_tab g_number_tbl_type;
1338 l_delivery_id_tab g_number_tbl_type;
1339
1340 l_hash_string_tab g_char_hash_string;
1341
1342 l_deliveryDetailRowidTab g_rowid_tbl_type;
1343 l_deliveryRowidTab g_rowid_tbl_type;
1344 l_legsRowidTab g_rowid_tbl_type;
1345 l_tempRowidTab g_rowid_tbl_type;
1346
1347 l_exception_id NUMBER;
1348 l_msg_count NUMBER;
1349 l_msg_data VARCHAR2(32767);
1350 l_return_status VARCHAR2(10);
1351 l_message_text VARCHAR2(32767);
1352 l_message_name VARCHAR2(50);
1353
1354 Update_Del_Exp EXCEPTION;
1355 --
1356 l_debug_on BOOLEAN;
1357 --
1358 BEGIN
1359 --
1360 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1361 --
1362 IF l_debug_on IS NULL
1363 THEN
1364 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1365 END IF;
1366 --
1367 IF l_debug_on THEN
1368 arp_message.set_line('WSH_CUST_MERGE.Process_Deliveries()+' || getTimeStamp );
1369 END IF;
1370 --
1371
1372 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1373
1374 -- OTM R12
1375 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1376
1377 IF (l_gc3_is_installed IS NULL) THEN
1378 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1379 END IF;
1380 -- End of OTM R12
1381
1382 OPEN Get_Delivery_Containers;
1383
1384 LOOP
1385 -- { to update container records
1386 FETCH Get_Delivery_Containers
1387 BULK COLLECT INTO l_deliveryDetailIdTab, l_deliveryDetailRowidTab
1388 LIMIT G_BATCH_LIMIT;
1389
1390 IF ( l_deliveryDetailRowidTab.COUNT > 0 )
1391 THEN
1392 -- { count > 0
1393 -- For container records ship_to_site_use_id and deliver_to_site_use_id
1394 -- is null
1395 FORALL updCnt IN l_deliveryDetailRowidTab.FIRST..l_deliveryDetailRowidTab.LAST
1396 UPDATE Wsh_Delivery_Details Wdd
1397 SET customer_id = decode( customer_id,
1398 G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
1399 customer_id ),
1400 ship_to_location_id = decode( ship_to_location_id,
1401 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
1402 ship_to_location_id ),
1403 deliver_to_location_id = decode( deliver_to_location_id,
1404 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
1405 deliver_to_location_id ),
1406 last_update_date = SYSDATE,
1407 last_updated_by = arp_standard.profile.user_id,
1408 last_update_login = arp_standard.profile.last_update_login,
1409 request_id = p_req_id,
1410 program_application_id = arp_standard.profile.program_application_id ,
1411 program_id = arp_standard.profile.program_id,
1412 program_update_date = SYSDATE
1413 WHERE Wdd.Rowid = l_deliveryDetailRowidTab(updCnt);
1414
1415 --
1416 setARMessageRowCount( 'WSH_DELIVERY_DETAILS', SQL%ROWCOUNT );
1417
1418 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
1419 THEN
1420 l_return_status := NULL;
1421
1422 Insert_Log_Table (
1423 p_id_tab => l_deliveryDetailIdTab,
1424 p_table_name => 'WSH_DELIVERY_DETAILS',
1425 p_req_id => p_req_id,
1426 x_return_status => l_return_status );
1427
1428 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1429 THEN
1430 Raise Update_Del_Exp;
1431 END IF;
1432 END IF;
1433 -- } Count > 0
1434 END IF;
1435
1436 EXIT WHEN Get_Delivery_Containers%NOTFOUND;
1437 -- } to update container records
1438 END LOOP;
1439
1440 CLOSE Get_Delivery_Containers;
1441
1442 l_hash_value_tab.DELETE;
1443 l_hash_string_tab.DELETE;
1444 l_delivery_id_tab.DELETE;
1445 l_return_status := NULL;
1446
1447 Get_Delivery_Hash (
1448 x_hash_string_tab => l_hash_string_tab,
1449 x_hash_value_tab => l_hash_value_tab,
1450 x_delivery_id_tab => l_delivery_id_tab,
1451 x_return_status => l_return_status );
1452
1453 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1454 THEN
1455 Raise Update_Del_Exp;
1456 END IF;
1457
1458 IF ( l_hash_value_tab.COUNT > 0 )
1459 THEN
1460 -- { Hash Value Count > 0
1461
1462 -- OTM R12 : update delivery
1463 -- initialize l_tms_update table with 'N'
1464 -- l_tms_update is used in the next update SQL regardless of
1465 -- GC3_INSTALLED status, so need to initialize it
1466
1467 -- following tables to be used by the update statement
1468 -- l_new_interface_flag_tab can't be used since it doesn't have
1469 -- entries for all the deliveries, but only those that will be passed
1470 -- to wsh_xc_util.log_otm_exception
1471
1472 l_index := l_hash_value_tab.FIRST;
1473 WHILE (l_index IS NOT NULL) LOOP
1474 l_tms_update(l_index) := 'N';
1475 l_tms_interface_flag(l_index) := NULL;
1476 l_tms_version_number(l_index) := NULL;
1477 l_index := l_hash_value_tab.NEXT(l_index);
1478 END LOOP;
1479
1480 IF (l_gc3_is_installed = 'Y') THEN
1481
1482 -- not to call l_delivery_info_tab.count repeatedly, performance
1483 l_delivery_count := 0;
1484 l_tms_count := 0;
1485
1486 -- for loop to populate
1487 l_index := l_hash_value_tab.FIRST;
1488 WHILE (l_index IS NOT NULL) LOOP
1489 l_trip_not_found := 'N';
1490
1491 WSH_DELIVERY_VALIDATIONS.get_delivery_information(
1492 p_delivery_id => l_delivery_id_tab(l_index),
1493 x_delivery_rec => l_delivery_info,
1494 x_return_status => l_return_status);
1495
1496 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1497 x_return_status := l_return_status;
1498 RETURN;
1499 END IF;
1500
1501 IF (nvl(l_delivery_info.ignore_for_planning, 'N') = 'N') THEN
1502
1503 --get trip information for delivery, no update when trip not OPEN
1504 WSH_DELIVERY_VALIDATIONS.get_trip_information
1505 (p_delivery_id => l_delivery_id_tab(l_index),
1506 x_trip_info_rec => l_trip_info_rec,
1507 x_return_status => l_return_status);
1508
1509 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1510 x_return_status := l_return_status;
1511 RETURN;
1512 END IF;
1513
1514 IF l_debug_on THEN
1515 WSH_DEBUG_SV.log('my_module','l_delivery_id-'||l_delivery_info.delivery_id
1516 ||' l_dropoff_location-'||l_delivery_info.ultimate_dropoff_location_id
1517 ||' l_tms_interface_flag-'||l_delivery_info.tms_interface_flag
1518 ||' l_tms_version_number-'||l_delivery_info.tms_version_number
1519 ||' g_from_location-'||G_FROM_LOCATION_ID
1520 ||' g_to_location-'||G_TO_LOCATION_ID);
1521 END IF;
1522
1523 --if trip exist, save the information for later delivery unassignment
1524 IF (l_trip_info_rec.trip_id IS NOT NULL
1525 AND NVL(l_delivery_info.ultimate_dropoff_location_id, -1) = NVL(G_FROM_LOCATION_ID, -1)
1526 AND NVL(l_delivery_info.ultimate_dropoff_location_id, -1) <> NVL(G_TO_LOCATION_ID, -1)) THEN
1527 l_tms_count := l_tms_count + 1;
1528 l_tms_delivery_info_tab(l_tms_count) := l_delivery_info;
1529 l_tms_trip_tab(l_tms_count) := l_trip_info_rec;
1530 END IF;
1531
1532 IF l_debug_on THEN
1533 WSH_DEBUG_SV.log('my_module','tms_count', l_tms_count);
1534 END IF;
1535
1536 IF (l_trip_info_rec.trip_id IS NULL) THEN
1537 l_trip_not_found := 'Y';
1538 END IF;
1539
1540 -- only do changes when there's no trip or trip status is OPEN
1541 IF (l_trip_info_rec.status_code = 'OP' OR
1542 l_trip_not_found = 'Y') THEN
1543
1544 -- checking for changes in the dropoff location id, update only
1545 -- if dropoff location id is equal to G_FROM_LOCATION_ID
1546 -- and not equal to G_TO_LOCATION_ID
1547
1548 IF (nvl(l_delivery_info.ultimate_dropoff_location_id, -1)
1549 = nvl(G_FROM_LOCATION_ID, -1) AND
1550 nvl(l_delivery_info.ultimate_dropoff_location_id, -1)
1551 <> nvl(G_TO_LOCATION_ID, -1)) THEN
1552 IF (l_delivery_info.tms_interface_flag NOT IN
1553 (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
1554 WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
1555 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
1556 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
1557 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED)) THEN
1558 l_tms_update(l_index) := 'Y';
1559 l_delivery_count := l_delivery_count + 1;
1560 l_delivery_info_tab(l_delivery_count) := l_delivery_info;
1561 l_new_interface_flag_tab(l_delivery_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
1562 l_tms_version_number(l_index) := nvl(l_delivery_info.tms_version_number, 1) + 1;
1563 l_tms_interface_flag(l_index) := l_new_interface_flag_tab(l_delivery_count);
1564 END IF;
1565 END IF; -- checking the value differences
1566 END IF; -- IF ((l_trip_not_found = 'N' AND
1567 END IF; -- if ignore_for_planning
1568
1569 IF l_debug_on THEN
1570 WSH_DEBUG_SV.log('my_module','l_delivery_id-'||l_delivery_info.delivery_id
1571 ||' l_tms_update-'||l_tms_update(l_index)
1572 ||' l_tms_interface_flag-'||l_tms_interface_flag(l_index)
1573 ||' l_tms_version_number-'||l_tms_version_number(l_index));
1574 arp_message.set_line('l_tms_update-'||l_tms_update(l_index)
1575 ||' l_tms_interface_flag-'||l_tms_interface_flag(l_index)
1576 ||' l_tms_version_number-'||l_tms_version_number(l_index));
1577 END IF;
1578 l_index := l_hash_value_tab.NEXT(l_index);
1579 END LOOP;
1580 END IF; -- if GC3 is installed
1581 -- End of OTM R12 : update delivery
1582
1583 FORALL updCnt IN l_hash_value_tab.FIRST..l_hash_value_tab.LAST
1584 UPDATE WSH_NEW_DELIVERIES Wnd
1585 SET Hash_Value = nvl(l_hash_value_tab(updCnt), Hash_Value),
1586 Hash_String = nvl(l_hash_string_tab(updCnt), Hash_String),
1587 customer_id = decode(customer_id,
1588 G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
1589 customer_id),
1590 ultimate_dropoff_location_id = decode(ultimate_dropoff_location_id,
1591 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
1592 ultimate_dropoff_location_id ),
1593 last_update_date = SYSDATE,
1594 last_updated_by = arp_standard.profile.user_id,
1595 last_update_login = arp_standard.profile.last_update_login,
1596 request_id = p_req_id,
1597 program_application_id = arp_standard.profile.program_application_id ,
1598 program_id = arp_standard.profile.program_id,
1599 program_update_date = SYSDATE,
1600 -- OTM R12 : update delivery
1601 TMS_INTERFACE_FLAG = decode(l_tms_update(updCnt), 'Y',
1602 l_tms_interface_flag(updCnt),
1603 nvl(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
1604 TMS_VERSION_NUMBER = decode(l_tms_update(updCnt), 'Y',
1605 l_tms_version_number(updCnt),
1606 nvl(TMS_VERSION_NUMBER, 1))
1607 -- End of OTM R12 : update delivery
1608 WHERE Delivery_Id = l_delivery_id_tab(updCnt)
1609 RETURNING Wnd.Delivery_Id BULK COLLECT INTO l_deliveryIdTab;
1610
1611 -- OTM R12 : update delivery
1612 IF (l_gc3_is_installed = 'Y' AND l_delivery_count > 0) THEN
1613 WSH_XC_UTIL.LOG_OTM_EXCEPTION(
1614 p_delivery_info_tab => l_delivery_info_tab,
1615 p_new_interface_flag_tab => l_new_interface_flag_tab,
1616 x_return_status => l_return_status);
1617
1618 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1619 x_return_status := l_return_status;
1620 RETURN;
1621 END IF;
1622 END IF;
1623 -- End of OTM R12 : update delivery
1624
1625 --
1626 setARMessageRowCount( 'WSH_NEW_DELIVERIES', SQL%ROWCOUNT );
1627
1628 IF l_debug_on THEN
1629 WSH_DEBUG_SV.log('my module', 'rows updated', l_deliveryIdTab.COUNT);
1630 WSH_DEBUG_SV.log('my module', 'rows suppose to update', l_hash_value_tab.COUNT);
1631 END IF;
1632
1633 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
1634 THEN
1635 -- { Profile Value
1636 l_return_status := NULL;
1637
1638 Insert_Log_Table (
1639 p_id_tab => l_deliveryIdTab,
1640 p_table_name => 'WSH_NEW_DELIVERIES',
1641 p_req_id => p_req_id,
1642 x_return_status => l_return_status );
1643
1644 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1645 THEN
1646 Raise Update_Del_Exp;
1647 END IF;
1648 -- } Profile Value
1649 END IF;
1650 -- } Hash Value Count > 0
1651 END IF;
1652
1653 OPEN Get_Del_Unassign_From_Stop ( G_TO_LOCATION_ID );
1654
1655 LOOP
1656 -- {
1657 FETCH Get_Del_Unassign_From_Stop
1658 BULK COLLECT INTO l_deliveryIdTab,
1659 l_stopIdTab,
1660 l_tripIdTab,
1661 l_deliveryLegIdTab,
1662 l_grossWeightTab,
1663 l_netWeightTab,
1664 l_volumeTab,
1665 l_legsRowidTab,
1666 l_tempRowidTab,
1667 l_ignoreTab --OTM R12
1668 LIMIT G_BATCH_LIMIT;
1669
1670 l_return_status := NULL;
1671
1672 IF ( l_legsRowidTab.COUNT > 0 )
1673 THEN
1674
1675 --This adjusts the trip stop weight volume. For all the deliveries
1676 --that are to be unassigned, it's okay to adjust them here.
1677 --OTM project will only unassign more deliveries.
1678 --It is also okay to not adjust the weight/vol for some OTM trips
1679 --since those will be synced up with OTM later
1680 Adjust_Weight_Volume (
1681 p_entity_type => 'TRIP-STOP',
1682 p_delivery_detail => l_dummyIdTab,
1683 p_parent_delivery_detail => l_dummyIdTab,
1684 p_delivery_id => l_deliveryIdTab,
1685 p_delivery_leg_id => l_deliveryLegIdTab,
1686 p_net_weight => l_netWeightTab,
1687 p_gross_weight => l_grossWeightTab,
1688 p_volume => l_volumeTab,
1689 x_return_status => l_return_status );
1690
1691 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
1692 WSH_UTIL_CORE.G_RET_STS_WARNING) )
1693 THEN
1694 --
1695 IF ( l_debug_on ) THEN
1696 ARP_MESSAGE.Set_Error('Error returned from API Adjust_Weight_Volume');
1697 END IF;
1698 --
1699 RAISE Update_Del_Exp;
1700 END IF;
1701
1702 --OTM R12
1703 --generating a list of the ignore for planning deliveries from
1704 --the cursor selected list because when OTM is installed, we should only
1705 --be dealing with ignore for planning deliveries in this LOOP, include
1706 --for planning deliveries will be done after this
1707 l_index := l_ignoreTab.FIRST;
1708 l_delivery_leg_count := 0;
1709
1710 IF (l_gc3_is_installed = 'N') THEN
1711 l_delivery_leg_count := l_legsRowidTab.COUNT;
1712 l_new_delivery_leg_tab := l_legsRowidTab;
1713 ELSE
1714 --
1715 WHILE (l_index IS NOT NULL) LOOP
1716
1717 IF (l_ignoreTab(l_index) = 'Y' ) THEN
1718 l_delivery_leg_count := l_delivery_leg_count + 1;
1719 l_new_delivery_leg_tab(l_delivery_leg_count) := l_legsRowidTab(l_index);
1720 END IF;
1721 l_index := l_ignoreTab.NEXT(l_index);
1722 END LOOP;
1723
1724 END IF;
1725
1726 IF (l_delivery_leg_count > 0 AND l_new_delivery_leg_tab.count > 0) THEN
1727 FORALL delCnt IN l_new_delivery_leg_tab.FIRST..l_new_delivery_leg_tab.LAST
1728 DELETE FROM Wsh_Delivery_Legs
1729 WHERE Rowid = l_new_delivery_leg_tab(delCnt);
1730 END IF;
1731 --END OTM R12
1732
1733 IF ( G_FTE_INSTALLED = 'Y' AND l_deliveryLegIdTab.COUNT > 0)
1734 THEN
1735 FORALL delCnt IN l_deliveryLegIdTab.FIRST..l_deliveryLegIdTab.LAST
1736 DELETE FROM Wsh_Freight_Costs
1737 WHERE Delivery_Leg_Id = l_deliveryLegIdTab(delCnt);
1738 END IF;
1739 END IF;
1740
1741 --For OTM related deliveries, all the deliveries will be removed from wsh_tmp,
1742 --so removing them here is okay since OTM flow will only unassign more deliveries than
1743 --the original flow.
1744 IF ( l_tempRowidTab.COUNT > 0 )
1745 THEN
1746 -- Deletes records from Wsh_Tmp table, so that locations are not
1747 -- updated for stops which has deliveries with different dropoff
1748 -- locations.
1749 FORALL delCnt IN l_tempRowidTab.FIRST..l_tempRowidTab.LAST
1750 DELETE FROM Wsh_Tmp
1751 WHERE Rowid = l_tempRowidTab(delCnt);
1752 END IF;
1753
1754 EXIT WHEN Get_Del_Unassign_From_Stop%NOTFOUND;
1755 -- }
1756 END LOOP;
1757
1758 CLOSE Get_Del_Unassign_From_Stop;
1759
1760 -- OTM R12 : customer merge
1761 -- When flow reaches here, the delivery is already assigned to a trip
1762 -- Check if it is an OTM trip + Trip (and included deliveries) is open
1763 -- then unassign the delivery and mark it UR, update the tms_version
1764 -- number also(unassigning should set delivery to UR and not DR+Ignore)
1765 -- Case 1: Other deliveries on this OTM trip should be marked AW,
1766 -- if they are not UR (if they are UR, leave them as UR)
1767 -- Case 2: If the Customer merge causes 2 include for planning
1768 -- deliveries to be selected which are on the same OTM trip
1769 -- --> when the first delivery is processed, it will mark the second
1770 -- one as AW and itself becomes UR. Continuing with the process,
1771 -- 2nd delivery is selected for update, then it becomes UR from AW,
1772 -- the first delivery which was marked UR earlier shouldn't get set
1773 -- to AW here
1774 -- Case 3: Due to customer merge, 2 deliveries are selected,
1775 -- out of which 1 is ignore for planning and other is
1776 -- include for planning. So for Ignore for planning cases,
1777 -- keep the current behavior and for include for planning, use Case#1.
1778
1779 IF l_gc3_is_installed = 'Y' THEN
1780 -- need to go through empty deliveries also to make sure all empty deliveries on
1781 -- OTM trip is being unassigned
1782 OPEN c_get_empty_deliveries(G_FROM_CUSTOMER_ID, G_FROM_LOCATION_ID );
1783 FETCH c_get_empty_deliveries BULK COLLECT INTO l_tms_delivery_id_tab;
1784 CLOSE c_get_empty_deliveries;
1785
1786 --reinitialize the count to the table count because this part of
1787 --the code could be triggered in another flow without the previous count
1788 --initilization.
1789 l_tms_count := l_tms_delivery_info_tab.COUNT;
1790
1791 IF l_debug_on THEN
1792 WSH_DEBUG_SV.log('my_module','tms_delivery_id_count', l_tms_delivery_id_tab.count);
1793 END IF;
1794
1795 -- for loop to populate
1796 l_index := l_tms_delivery_id_tab.FIRST;
1797 WHILE (l_index IS NOT NULL) LOOP
1798
1799 WSH_DELIVERY_VALIDATIONS.get_delivery_information(
1800 p_delivery_id => l_tms_delivery_id_tab(l_index),
1801 x_delivery_rec => l_delivery_info,
1802 x_return_status => l_return_status);
1803
1804 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1805 x_return_status := l_return_status;
1806 RETURN;
1807 END IF;
1808
1809 IF (nvl(l_delivery_info.ignore_for_planning, 'N') = 'N') THEN
1810
1811 --get trip information for delivery, no update when trip not OPEN
1812 WSH_DELIVERY_VALIDATIONS.get_trip_information
1813 (p_delivery_id => l_tms_delivery_id_tab(l_index),
1814 x_trip_info_rec => l_trip_info_rec,
1815 x_return_status => l_return_status);
1816
1817 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1818 x_return_status := l_return_status;
1819 RETURN;
1820 END IF;
1821
1822 IF l_debug_on THEN
1823 WSH_DEBUG_SV.log('my_module','l_delivery_id-'||l_delivery_info.delivery_id
1824 ||' l_dropoff_location-'||l_delivery_info.ultimate_dropoff_location_id
1825 ||' l_tms_interface_flag-'||l_delivery_info.tms_interface_flag
1826 ||' l_tms_version_number-'||l_delivery_info.tms_version_number
1827 ||' g_from_location-'||G_FROM_LOCATION_ID||' g_to_location-'||G_TO_LOCATION_ID);
1828
1829 WSH_DEBUG_SV.log('my_module','tms_count', l_tms_count);
1830 END IF;
1831
1832 --if trip exist, save the information for later delivery unassign
1833 IF (l_trip_info_rec.trip_id IS NOT NULL
1834 AND NVL(l_delivery_info.ultimate_dropoff_location_id, -1) = NVL(G_FROM_LOCATION_ID, -1)
1835 AND NVL(l_delivery_info.ultimate_dropoff_location_id, -1) <> NVL(G_TO_LOCATION_ID, -1)) THEN
1836 l_tms_count := l_tms_count + 1;
1837 l_tms_delivery_info_tab(l_tms_count) := l_delivery_info;
1838 l_tms_trip_tab(l_tms_count) := l_trip_info_rec;
1839 END IF;
1840
1841 IF l_debug_on THEN
1842 WSH_DEBUG_SV.log('my_module','tms_count', l_tms_count);
1843 END IF;
1844
1845 END IF;
1846 l_index := l_tms_delivery_id_tab.NEXT(l_index);
1847 END LOOP;
1848
1849 IF l_debug_on THEN
1850 WSH_DEBUG_SV.log('my_module','AFTER LOOP tms_count', l_tms_count);
1851 END IF;
1852
1853 --for all the deliveries saved, need to unassign from trip and delete the freight cost
1854 IF (l_tms_count > 0) THEN
1855 l_index := l_tms_delivery_info_tab.FIRST;
1856 WHILE (l_index IS NOT NULL) LOOP
1857
1858 IF l_debug_on THEN
1859 WSH_DEBUG_SV.log('my_module', l_index || 'delivery id-'||l_tms_delivery_info_tab(l_index).delivery_id
1860 ||' ignore for planning-'||l_tms_delivery_info_tab(l_index).ignore_for_planning
1861 ||' tms flag-'||l_tms_delivery_info_tab(l_index).tms_interface_flag
1862 ||' delivery status-'||l_tms_delivery_info_tab(l_index).status_code
1863 ||' trip_status-'||l_tms_trip_tab(l_index).status_code);
1864 arp_message.set_line(l_index || 'delivery id-'||l_tms_delivery_info_tab(l_index).delivery_id
1865 ||' ignore for planning-'||l_tms_delivery_info_tab(l_index).ignore_for_planning
1866 ||' tms flag-'||l_tms_delivery_info_tab(l_index).tms_interface_flag
1867 ||' delivery status-'||l_tms_delivery_info_tab(l_index).status_code
1868 ||' trip_status-'||l_tms_trip_tab(l_index).status_code);
1869 END IF;
1870
1871 -- Include UR, as delivery got updated above to UR
1872 -- Include DR to count empty deliveries that are assigned
1873 -- to trip, they should be unassigned + the drop off location should be updated
1874 IF (l_tms_delivery_info_tab(l_index).ignore_for_planning = 'N' AND
1875 l_tms_delivery_info_tab(l_index).tms_interface_flag IN
1876 (WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
1877 WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
1878 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED,
1879 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
1880 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
1881 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) AND
1882 l_tms_delivery_info_tab(l_index).status_code = 'OP' AND
1883 l_tms_trip_tab(l_index).status_code = 'OP') THEN
1884
1885 -- it is already assigned to a trip,
1886 -- there will be only one OTM trip for delivery
1887 l_dlvy_id_tab(l_dlvy_id_tab.COUNT+1) := l_tms_delivery_info_tab(l_index).delivery_id;
1888
1889 -- these deliveries would be set to UR already
1890 -- by the above update statement.
1891 -- Update the other deliveries in the trip, which are
1892 -- not part of the above selection to AW.
1893 -- Also log appropriate exception(within the API called)
1894 -- Need to update the version number also
1895 -- If trip is open, find other deliveries associated
1896 -- with the trip
1897
1898 FOR rec in c_get_deliveries(l_tms_trip_tab(l_index).trip_id, l_tms_delivery_info_tab(l_index).delivery_id) LOOP
1899 l_aw_dlvy_tab(l_aw_dlvy_tab.count + 1) := rec.delivery_id;
1900 l_aw_interface_flag_tab(l_aw_interface_flag_tab.count + 1) := WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER;
1901 END LOOP;
1902
1903 IF l_debug_on THEN
1904 WSH_DEBUG_SV.LOG('my module', 'unassign delivery count-'||l_dlvy_id_tab.COUNT||' aw delivery count-'||l_aw_dlvy_tab.COUNT);
1905 END IF;
1906
1907 END IF;
1908 l_index := l_tms_delivery_info_tab.NEXT(l_index);
1909 END LOOP; --end of finding include for planning deliveries
1910 END IF;
1911
1912 IF l_debug_on THEN
1913 WSH_DEBUG_SV.LOG('my module', 'unassign delivery count-'||l_dlvy_id_tab.COUNT||' aw delivery count-'||l_aw_dlvy_tab.COUNT);
1914 arp_message.set_line('unassign delivery count-'||l_dlvy_id_tab.COUNT||' aw delivery count-'||l_aw_dlvy_tab.COUNT);
1915 END IF;
1916
1917 -- Update the Interface Flag to AW for selected deliveries
1918 -- Log appropriate exceptions(within the API)
1919 -- Need to update the version number also
1920 IF l_aw_dlvy_tab.count > 0 THEN
1921 -- Call Update after the above LOOP
1922 WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG(
1923 p_delivery_id_tab => l_aw_dlvy_tab,
1924 p_tms_interface_flag_tab => l_aw_interface_flag_tab,
1925 x_return_status => l_return_status);
1926 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1927 IF l_debug_on THEN
1928 ARP_MESSAGE.Set_Error('Error returned from API WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG '||l_return_status);
1929 END IF;
1930 RAISE Update_Del_Exp;
1931 END IF;
1932 END IF;
1933
1934 -- For Include for Planning Deliveries , converted to UR , Delete Legs
1935 IF l_dlvy_id_tab.count > 0 THEN
1936 FORALL delCnt IN l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
1937 DELETE FROM WSH_DELIVERY_LEGS
1938 WHERE DELIVERY_ID = l_dlvy_id_tab(delCnt);
1939 END IF;
1940
1941 -- Freight Cost will get updated for scenarios where Cost was sent by
1942 -- OTM, when EBS receives the trip next time. But to avoid cases
1943 -- where user flips the Ignore for Planning flag,
1944 -- Deleting OTM freight costs
1945
1946 -- Get Freight Cost Type id
1947 l_frcost_type_id := NULL;
1948
1949 OPEN c_get_frcost_type_id;
1950 FETCH c_get_frcost_type_id INTO l_frcost_type_id;
1951 IF c_get_frcost_type_id%NOTFOUND THEN
1952 RAISE frcost_not_found;
1953 END IF;
1954 CLOSE c_get_frcost_type_id;
1955
1956 IF l_dlvy_id_tab.count > 0 THEN
1957 -- Delivery Legs
1958 FORALL delCnt IN l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
1959 DELETE FROM Wsh_Freight_Costs
1960 WHERE Delivery_Leg_Id IN
1961 (SELECT wdl.delivery_leg_id
1962 FROM wsh_delivery_legs wdl
1963 WHERE wdl.delivery_id = l_dlvy_id_tab(delCnt))
1964 AND freight_cost_type_id = l_frcost_type_id;
1965
1966 -- Delivery
1967 FORALL delCnt IN l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
1968 DELETE FROM Wsh_Freight_Costs
1969 WHERE Delivery_Id = l_dlvy_id_tab(delCnt)
1970 AND freight_cost_type_id = l_frcost_type_id;
1971 END IF;
1972 END IF; -- if l_gc3_is_installed
1973 -- End of OTM R12 : customer merge
1974
1975 -- Logging Exceptions for Deliveries Unassigned from Stop
1976 IF ( l_stopIdTab.COUNT > 0 )
1977 THEN
1978 -- { Log Exception
1979 l_message_name := 'WSH_CMRG_UNASSIGN_STOP';
1980
1981 FOR ExpCnt in l_stopIdTab.FIRST..l_stopIdTab.LAST
1982 LOOP
1983 -- { Loop for logging Expceptions
1984
1985 -- OTM R12 : customer merge
1986 l_skip := 'N'; -- for each stop record
1987 --l_deliveryIdTab and l_StopIdTab count matches
1988
1989 IF l_gc3_is_installed = 'Y' THEN
1990 IF l_dlvy_id_tab.count > 0 THEN
1991 l_index := l_dlvy_id_tab.FIRST;
1992 WHILE (l_index IS NOT NULL) LOOP
1993 IF l_deliveryIdTab(ExpCnt) = l_dlvy_id_tab(l_index) THEN
1994 -- skip this stop record, this delivery has been
1995 -- unassigned and no need to update OTM trip stop
1996 l_skip := 'Y';--skip this stop record
1997 EXIT;
1998 END IF;
1999 l_index := l_dlvy_id_tab.NEXT(l_index);
2000 END LOOP;
2001 END IF;
2002 END IF;
2003
2004 IF l_skip = 'N' THEN --if l_skip is 'N' then that means the delivery is not unassigned previously due to OTM integration
2005 -- CONTINUE WITH CURRENT CODE/LOGIC
2006 -- End of OTM R12 : customer merge
2007
2008 FND_MESSAGE.Set_Name ('WSH', l_message_name );
2009 FND_MESSAGE.Set_Token ('PS1', G_FROM_CUST_SITE_ID );
2010 FND_MESSAGE.Set_Token ('PS2', G_TO_CUST_SITE_ID );
2011 FND_MESSAGE.Set_Token ('DELIVERY_ID', l_deliveryIdTab(ExpCnt) );
2012
2013 l_message_text := FND_MESSAGE.Get;
2014
2015 l_return_status := NULL;
2016 l_msg_count := NULL;
2017 l_msg_data := NULL;
2018 l_exception_id := NULL;
2019
2020 WSH_XC_UTIL.log_exception
2021 (
2022 p_api_version => 1.0,
2023 x_return_status => l_return_status,
2024 x_msg_count => l_msg_count,
2025 x_msg_data => l_msg_data,
2026 x_exception_id => l_exception_id,
2027 p_exception_location_id => G_TO_LOCATION_ID,
2028 p_logged_at_location_id => G_TO_LOCATION_ID,
2029 p_logging_entity => 'SHIPPER',
2030 p_logging_entity_id => Fnd_Global.user_id,
2031 p_exception_name => 'WSH_CUSTOMER_MERGE_CHANGE',
2032 p_message => l_message_text,
2033 p_severity => 'LOW',
2034 p_manually_logged => 'N',
2035 p_trip_id => l_tripIdTab(ExpCnt),
2036 p_trip_stop_id => l_stopIdTab(ExpCnt),
2037 p_error_message => l_message_text
2038 );
2039
2040 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2041 THEN
2042 RAISE Update_Del_Exp;
2043 END IF;
2044 END IF; -- OTM R12 : customer merge
2045
2046 -- } Loop for logging Expceptions
2047 END LOOP;
2048 -- } Log Exception
2049 END IF;
2050
2051
2052 --OTM R12: customer merge
2053 IF (l_gc3_is_installed = 'Y' AND l_dlvy_id_tab.count > 0) THEN
2054 -- Filter the list in wsh_tmp which has column1 as delivery_id
2055 -- and column2 as stop id
2056 -- Delete the records from wsh_tmp where column1 = include for
2057 -- planning delivery ids selected above
2058 -- the filter here is done because we don't want to update OTM trip stops,
2059 -- and the update sql right after updates the trip stops.
2060
2061 FORALL delCnt IN l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
2062 DELETE FROM wsh_tmp
2063 WHERE column1 = l_dlvy_id_tab(delCnt);
2064 END IF;
2065 -- End of OTM R12 : customer merge
2066
2067 UPDATE WSH_TRIP_STOPS Wts
2068 SET stop_location_id = G_TO_LOCATION_ID,
2069 last_update_date = SYSDATE,
2070 last_updated_by = arp_standard.profile.user_id,
2071 last_update_login = arp_standard.profile.last_update_login,
2072 request_id = p_req_id,
2073 program_application_id = arp_standard.profile.program_application_id ,
2074 program_id = arp_standard.profile.program_id,
2075 program_update_date = SYSDATE
2076 WHERE Wts.Stop_Id in (
2077 SELECT Column2
2078 FROM WSH_TMP
2079 WHERE Column2 IS NOT NULL )
2080 RETURNING Wts.Stop_Id BULK COLLECT INTO l_stopIdTab;
2081
2082 --
2083 setARMessageRowCount( 'WSH_TRIP_STOPS', SQL%ROWCOUNT );
2084
2085 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
2086 THEN
2087 l_return_status := NULL;
2088
2089 Insert_Log_Table (
2090 p_id_tab => l_stopIdTab,
2091 p_table_name => 'WSH_TRIP_STOPS',
2092 p_req_id => p_req_id,
2093 x_return_status => l_return_status );
2094
2095 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2096 THEN
2097 Raise Update_Del_Exp;
2098 END IF;
2099 END IF;
2100
2101 --
2102 IF l_debug_on THEN
2103 arp_message.set_line('WSH_CUST_MERGE.Process_Deliveries()+' || getTimeStamp );
2104 END IF;
2105 --
2106 EXCEPTION
2107 WHEN Update_Del_Exp THEN
2108 x_return_status := l_return_status;
2109 --
2110 IF ( l_debug_on ) THEN
2111 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Deliveries()+ Update_Del_Exp - ' || getTimeStamp );
2112 END IF;
2113 --
2114 IF ( Get_Del_Unassign_From_Stop%ISOPEN ) THEN
2115 CLOSE Get_Del_Unassign_From_Stop;
2116 END IF;
2117
2118 IF ( Get_Delivery_Containers%ISOPEN ) THEN
2119 CLOSE Get_Delivery_Containers;
2120 END IF;
2121
2122 IF (c_get_deliveries %ISOPEN) THEN
2123 CLOSE c_get_deliveries;
2124 END IF;
2125
2126 IF (c_get_frcost_type_id %ISOPEN) THEN
2127 CLOSE c_get_frcost_type_id;
2128 END IF;
2129
2130 IF (Get_Del_Unassign_From_Stop%ISOPEN) THEN
2131 CLOSE Get_Del_Unassign_From_Stop;
2132 END IF;
2133 -- End of OTM R12 : customer merge
2134 -- OTM R12 : customer merge
2135 WHEN frcost_not_found THEN
2136 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2137 --
2138 IF ( l_debug_on ) THEN
2139 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Deliveries()+ OTM Freight cost type not defined- ' || getTimeStamp );
2140 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
2141 END IF;
2142 --
2143 IF ( Get_Del_Unassign_From_Stop%ISOPEN ) THEN
2144 CLOSE Get_Del_Unassign_From_Stop;
2145 END IF;
2146
2147 IF ( Get_Delivery_Containers%ISOPEN ) THEN
2148 CLOSE Get_Delivery_Containers;
2149 END IF;
2150
2151 IF (c_get_deliveries %ISOPEN) THEN
2152 CLOSE c_get_deliveries;
2153 END IF;
2154
2155 IF (c_get_frcost_type_id %ISOPEN) THEN
2156 CLOSE c_get_frcost_type_id;
2157 END IF;
2158
2159 IF (Get_Del_Unassign_From_Stop%ISOPEN) THEN
2160 CLOSE Get_Del_Unassign_From_Stop;
2161 END IF;
2162 -- End of OTM R12 : customer merge
2163 WHEN OTHERS THEN
2164 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2165 --
2166 IF ( l_debug_on ) THEN
2167 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Deliveries()+ Others - ' || getTimeStamp );
2168 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
2169 END IF;
2170 --
2171 IF ( Get_Del_Unassign_From_Stop%ISOPEN ) THEN
2172 CLOSE Get_Del_Unassign_From_Stop;
2173 END IF;
2174
2175 IF ( Get_Delivery_Containers%ISOPEN ) THEN
2176 CLOSE Get_Delivery_Containers;
2177 END IF;
2178
2179 -- OTM R12 : customer merge
2180 IF (c_get_deliveries %ISOPEN) THEN
2181 CLOSE c_get_deliveries;
2182 END IF;
2183
2184 IF (c_get_frcost_type_id %ISOPEN) THEN
2185 CLOSE c_get_frcost_type_id;
2186 END IF;
2187
2188 IF (Get_Del_Unassign_From_Stop%ISOPEN) THEN
2189 CLOSE Get_Del_Unassign_From_Stop;
2190 END IF;
2191 -- End of OTM R12 : customer merge
2192 --
2193 END Process_Deliveries;
2194 --
2195 --
2196 --
2197 PROCEDURE Process_Open_Deliveries (
2198 p_req_id IN NUMBER,
2199 x_return_status OUT NOCOPY VARCHAR2 )
2200 IS
2201 -- Cursor to fetch Open delivery details.
2202 CURSOR Get_Empty_Deliveries (
2203 p_customer_id NUMBER,
2204 p_location_id NUMBER )
2205 IS
2206 SELECT Wnd.Delivery_Id, Wnd.Rowid
2207 FROM Wsh_New_Deliveries WND
2208 WHERE nvl(Wnd.Customer_Id, p_customer_id) = p_customer_id
2209 AND Wnd.Ultimate_Dropoff_Location_Id = p_location_id
2210 AND Wnd.Status_Code = 'OP'
2211 AND NOT EXISTS
2212 ( SELECT 'x' FROM Wsh_Delivery_Assignments Wda
2213 WHERE Wda.Delivery_Id = Wnd.Delivery_Id )
2214 FOR UPDATE OF Wnd.Delivery_Id NOWAIT;
2215
2216 -- Cursor to fetch deliveries which contains only containers under it.
2217 CURSOR Get_Empty_Cont_Delivery (
2218 p_customer_id NUMBER,
2219 p_location_id NUMBER )
2220 IS
2221 SELECT Det.Delivery_Detail_Id, Dlvy.Delivery_Id,
2222 Det.Rowid, Dlvy.Rowid
2223 FROM Wsh_Delivery_Details Det,
2224 Wsh_Delivery_Assignments Asg,
2225 Wsh_New_Deliveries Dlvy
2226 WHERE Det.Delivery_Detail_Id = Asg.Delivery_Detail_Id
2227 AND Asg.Delivery_Id = Dlvy.Delivery_Id
2228 AND Dlvy.Delivery_Id in
2229 ( SELECT Wnd.Delivery_Id
2230 FROM Wsh_Delivery_Details Wdd,
2231 Wsh_Delivery_Assignments Wda,
2232 Wsh_New_Deliveries Wnd
2233 WHERE Wdd.Container_Flag = 'Y'
2234 AND Wdd.Delivery_Detail_Id = Wda.Delivery_Detail_id
2235 AND Wda.Delivery_Id = Wnd.Delivery_Id
2236 AND nvl(Wnd.Customer_Id, p_customer_id) = p_customer_id
2237 AND Wnd.Ultimate_Dropoff_Location_Id = p_location_id
2238 AND Wnd.Status_Code = 'OP'
2239 AND NOT EXISTS
2240 ( SELECT 'X'
2241 FROM Wsh_Delivery_Assignments Asgn
2242 WHERE Asgn.Parent_Delivery_Detail_Id = Wdd.Delivery_Detail_Id
2243 AND Asgn.Delivery_Id = Wnd.Delivery_Id )
2244 AND NOT EXISTS
2245 ( SELECT 'X'
2246 FROM Wsh_Delivery_Details dd,
2247 Wsh_Delivery_Assignments da
2248 WHERE dd.Container_Flag = 'N'
2249 AND dd.Delivery_Detail_Id = da.Delivery_Detail_Id
2250 AND da.Delivery_Id = Wnd.Delivery_Id ) )
2251 FOR UPDATE OF Det.Delivery_Detail_Id, Dlvy.Delivery_Id NOWAIT;
2252
2253 l_deliveryDetailIdTab g_number_tbl_type;
2254 l_deliveryIdTab g_number_tbl_type;
2255
2256 l_deliveryRowidTab g_rowid_tbl_type;
2257 l_deliveryDetailRowidTab g_rowid_tbl_type;
2258
2259 l_return_status VARCHAR2(10);
2260
2261 Process_Deliveries_Exp EXCEPTION;
2262
2263 --
2264 l_debug_on BOOLEAN;
2265 --
2266 BEGIN
2267 --
2268 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2269 --
2270 IF l_debug_on IS NULL
2271 THEN
2272 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2273 END IF;
2274 --
2275 IF l_debug_on THEN
2276 arp_message.set_line('WSH_CUST_MERGE.Process_Open_Deliveries()+' || getTimeStamp );
2277 END IF;
2278 --
2279
2280 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2281
2282 OPEN Get_Empty_Deliveries ( G_FROM_CUSTOMER_ID,
2283 G_FROM_LOCATION_ID );
2284
2285 LOOP
2286 -- {
2287 FETCH Get_Empty_Deliveries
2288 BULK COLLECT INTO l_deliveryIdTab,
2289 l_deliveryRowidTab
2290 LIMIT G_BATCH_LIMIT;
2291
2292 IF ( l_deliveryRowidTab.COUNT > 0 )
2293 THEN
2294
2295 -- OTM R12 : update delivery
2296 -- no code changes are needed for the following update
2297 -- since the deliveries selected by cursor Get_Empty_Deliveries are
2298 -- empty deliveries
2299
2300 FORALL updCnt in l_deliveryRowidTab.FIRST..l_deliveryRowidTab.LAST
2301 UPDATE Wsh_New_Deliveries
2302 SET customer_id = decode(customer_id,
2303 G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
2304 customer_id),
2305 ultimate_dropoff_location_id = decode(ultimate_dropoff_location_id,
2306 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
2307 ultimate_dropoff_location_id ),
2308 last_update_date = SYSDATE,
2309 last_updated_by = arp_standard.profile.user_id,
2310 last_update_login = arp_standard.profile.last_update_login,
2311 request_id = p_req_id,
2312 program_application_id = arp_standard.profile.program_application_id ,
2313 program_id = arp_standard.profile.program_id,
2314 program_update_date = SYSDATE
2315 WHERE Rowid = l_deliveryRowidTab(updCnt);
2316
2317 --
2318 setARMessageRowCount( 'WSH_TRIP_STOPS', SQL%ROWCOUNT );
2319
2320 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
2321 THEN
2322 l_return_status := NULL;
2323
2324 Insert_Log_Table (
2325 p_id_tab => l_deliveryIdTab,
2326 p_table_name => 'WSH_NEW_DELIVERIES',
2327 p_req_id => p_req_id,
2328 x_return_status => l_return_status );
2329
2330 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2331 THEN
2332 RAISE Process_Deliveries_Exp;
2333 END IF;
2334 END IF;
2335 END IF;
2336
2337 EXIT WHEN Get_Empty_Deliveries%NOTFOUND;
2338 -- }
2339 END LOOP;
2340
2341 CLOSE Get_Empty_Deliveries;
2342
2343 OPEN Get_Empty_Cont_Delivery ( G_FROM_CUSTOMER_ID,
2344 G_FROM_LOCATION_ID );
2345
2346 LOOP
2347 -- {
2348 FETCH Get_Empty_Cont_Delivery
2349 BULK COLLECT INTO l_deliveryDetailIdTab,
2350 l_deliveryIdTab,
2351 l_deliveryDetailRowidTab,
2352 l_deliveryRowidTab
2353 LIMIT G_BATCH_LIMIT;
2354
2355 IF ( l_deliveryRowidTab.COUNT > 0 )
2356 THEN
2357
2358 -- OTM R12 : update delivery
2359 -- no code changes are needed for the following update
2360 -- since the deliveries selected by cursor Get_Empty_Cont_Delivery are
2361 -- empty deliveries
2362
2363 FORALL updCnt in l_deliveryRowidTab.FIRST..l_deliveryRowidTab.LAST
2364 UPDATE Wsh_New_Deliveries
2365 SET customer_id = decode(customer_id,
2366 G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
2367 customer_id),
2368 ultimate_dropoff_location_id = decode(ultimate_dropoff_location_id,
2369 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
2370 ultimate_dropoff_location_id ),
2371 last_update_date = SYSDATE,
2372 last_updated_by = arp_standard.profile.user_id,
2373 last_update_login = arp_standard.profile.last_update_login,
2374 request_id = p_req_id,
2375 program_application_id = arp_standard.profile.program_application_id ,
2376 program_id = arp_standard.profile.program_id,
2377 program_update_date = SYSDATE
2378 WHERE Rowid = l_deliveryRowidTab(updCnt);
2379
2380 --
2381 setARMessageRowCount( 'WSH_TRIP_STOPS', SQL%ROWCOUNT );
2382
2383 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
2384 THEN
2385 l_return_status := NULL;
2386
2387 Insert_Log_Table (
2388 p_id_tab => l_deliveryIdTab,
2389 p_table_name => 'WSH_NEW_DELIVERIES',
2390 p_req_id => p_req_id,
2391 x_return_status => l_return_status );
2392
2393 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2394 THEN
2395 RAISE Process_Deliveries_Exp;
2396 END IF;
2397 END IF;
2398 IF l_deliveryDetailRowidTab.COUNT > 0 THEN
2399 FORALL updCnt in l_deliveryDetailRowidTab.FIRST..l_deliveryDetailRowidTab.LAST
2400 UPDATE Wsh_Delivery_Details Wdd
2401 SET customer_id = decode( customer_id,
2402 G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
2403 customer_id ),
2404 ship_to_location_id = decode( ship_to_location_id,
2405 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
2406 ship_to_location_id ),
2407 deliver_to_location_id = decode( deliver_to_location_id,
2408 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
2409 deliver_to_location_id ),
2410 last_update_date = SYSDATE,
2411 last_updated_by = arp_standard.profile.user_id,
2412 last_update_login = arp_standard.profile.last_update_login,
2413 request_id = p_req_id,
2414 program_application_id = arp_standard.profile.program_application_id ,
2415 program_id = arp_standard.profile.program_id,
2416 program_update_date = SYSDATE
2417 WHERE Wdd.Rowid = l_deliveryDetailRowidTab(updCnt);
2418 END IF;
2419 --
2420 setARMessageRowCount( 'WSH_DELIVERY_DETAILS', SQL%ROWCOUNT );
2421
2422 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
2423 THEN
2424 l_return_status := NULL;
2425
2426 Insert_Log_Table (
2427 p_id_tab => l_deliveryDetailIdTab,
2428 p_table_name => 'WSH_DELIVERY_DETAILS',
2429 p_req_id => p_req_id,
2430 x_return_status => l_return_status );
2431
2432 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2433 THEN
2434 RAISE Process_Deliveries_Exp;
2435 END IF;
2436 END IF;
2437
2438 END IF;
2439
2440 EXIT WHEN Get_Empty_Cont_Delivery%NOTFOUND;
2441 -- }
2442 END LOOP;
2443
2444 CLOSE Get_Empty_Cont_Delivery;
2445
2446 --
2447 IF l_debug_on THEN
2448 arp_message.set_line('WSH_CUST_MERGE.Process_Open_Deliveries()+' || getTimeStamp );
2449 END IF;
2450 --
2451 EXCEPTION
2452 WHEN Process_Deliveries_Exp THEN
2453 x_return_status := l_return_status;
2454 --
2455 IF ( l_debug_on ) THEN
2456 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Open_Deliveries()+ Process_Deliveries_Exp - ' || getTimeStamp );
2457 END IF;
2458 --
2459 IF ( Get_Empty_Deliveries%ISOPEN ) THEN
2460 CLOSE Get_Empty_Deliveries;
2461 END IF;
2462
2463 IF ( Get_Empty_Cont_Delivery%ISOPEN ) THEN
2464 CLOSE Get_Empty_Cont_Delivery;
2465 END IF;
2466 --
2467 WHEN OTHERS THEN
2468 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2469 --
2470 IF ( l_debug_on ) THEN
2471 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Open_Deliveries()+ Others - ' || getTimeStamp );
2472 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
2473 END IF;
2474 --
2475 IF ( Get_Empty_Deliveries%ISOPEN ) THEN
2476 CLOSE Get_Empty_Deliveries;
2477 END IF;
2478
2479 IF ( Get_Empty_Cont_Delivery%ISOPEN ) THEN
2480 CLOSE Get_Empty_Cont_Delivery;
2481 END IF;
2482 --
2483 END Process_Open_Deliveries;
2484
2485 --
2486 --
2487 --
2488 PROCEDURE Process_Open_Lines (
2489 p_req_id IN NUMBER,
2490 x_return_status OUT NOCOPY VARCHAR2 )
2491 IS
2492 -- Cursor to fetch Open delivery details.
2493 CURSOR Get_Open_Lines ( p_customer_id NUMBER,
2494 p_site_use_id NUMBER,
2495 p_location_id NUMBER )
2496 IS
2497 SELECT WDD.Delivery_Detail_Id, WDA.Parent_Delivery_Detail_Id,
2498 WND.Delivery_Id, WTS.Stop_Id,
2499 WDD.Net_Weight, WDD.Gross_Weight,
2500 WDD.Volume, Wdd.Weight_Uom_code, Wdd.Volume_Uom_Code,
2501 Wdd.Inventory_Item_Id, Wdd.Organization_Id,
2502 WDD.Rowid, WDA.Rowid
2503 FROM Wsh_Delivery_Details WDD,
2504 Wsh_Delivery_Assignments WDA,
2505 Wsh_New_Deliveries WND,
2506 Wsh_Delivery_Legs WDL,
2507 Wsh_Trip_Stops WTS
2508 WHERE WTS.Stop_id (+) = WDL.Drop_Off_Stop_Id
2509 AND WDL.Delivery_Id (+) = WND.Delivery_Id
2510 AND NVL(WND.Status_Code, 'OP') = 'OP'
2511 AND WND.Delivery_Id (+) = WDA.Delivery_Id
2512 AND WDA.Delivery_Detail_Id = WDD.Delivery_Detail_Id
2513 AND WDD.Container_Flag = 'N'
2514 AND (WDD.Customer_Id = p_customer_id
2515 --Bug 5900667: Reverting back from AND to OR
2516 OR ((WDD.Ship_To_Site_Use_Id = p_site_use_id
2517 AND WDD.Ship_To_Location_Id = p_location_id )
2518 OR (WDD.Deliver_To_Site_Use_Id = p_site_use_id
2519 AND WDD.Deliver_To_Location_Id = p_location_id )))
2520 AND WDD.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
2521 FOR UPDATE OF Wdd.Delivery_Detail_Id, Wda.Delivery_Detail_Id, Wnd.Delivery_Id, Wts.Stop_Id NOWAIT;
2522
2523 l_deliveryDetailIdTab g_number_tbl_type;
2524 l_parentDeliveryDetailIdTab g_number_tbl_type;
2525 l_deliveryIdTab g_number_tbl_type;
2526 l_dummyIdTab g_number_tbl_type;
2527 l_stopIdTab g_number_tbl_type;
2528 l_grossWeightTab g_number_tbl_type;
2529 l_netWeightTab g_number_tbl_type;
2530 l_volumeTab g_number_tbl_type;
2531 l_inventoryItemIdTab g_number_tbl_type;
2532 l_organizationIdTab g_number_tbl_type;
2533
2534 l_weightUomTab g_char_tbl_type;
2535 l_volumeUomTab g_char_tbl_type;
2536
2537 l_deliveryDetailRowidTab g_rowid_tbl_type;
2538 l_deliveryAssgRowidTab g_rowid_tbl_type;
2539
2540 l_exception_id NUMBER;
2541 l_msg_count NUMBER;
2542 l_msg_data VARCHAR2(32767);
2543 l_return_status VARCHAR2(10);
2544 l_message_text VARCHAR2(32767);
2545 l_message_name VARCHAR2(50);
2546 l_tmp_cnt NUMBER;
2547
2548 Process_Open_Lines_Exp EXCEPTION;
2549 --
2550 l_debug_on BOOLEAN;
2551 --
2552 BEGIN
2553 --
2554 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2555 --
2556 IF l_debug_on IS NULL
2557 THEN
2558 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2559 END IF;
2560 --
2561 IF l_debug_on THEN
2562 arp_message.set_line('WSH_CUST_MERGE.Process_Open_Lines()+' || getTimeStamp );
2563 END IF;
2564 --
2565
2566 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2567
2568 -- Processing open lines in shipping
2569 OPEN Get_Open_Lines ( G_FROM_CUSTOMER_ID,
2570 G_FROM_CUST_SITE_ID,
2571 G_FROM_LOCATION_ID );
2572
2573 LOOP
2574 -- { Loop for Open delivery lines
2575 FETCH Get_Open_Lines
2576 BULK COLLECT INTO l_deliveryDetailIdTab,
2577 l_parentDeliveryDetailIdTab,
2578 l_deliveryIdTab,
2579 l_stopIdTab,
2580 l_netWeightTab,
2581 l_grossWeightTab,
2582 l_volumeTab,
2583 l_weightUomTab,
2584 l_volumeUomTab,
2585 l_inventoryItemIdTab,
2586 l_organizationIdTab,
2587 l_deliveryDetailRowidTab,
2588 l_deliveryAssgRowidTab
2589 LIMIT G_BATCH_LIMIT;
2590
2591 IF ( l_deliveryDetailIdTab.COUNT > 0 )
2592 THEN
2593 -- {
2594
2595 -- Update non-container lines
2596 IF l_deliveryDetailRowidTab.count > 0 THEN
2597 FORALL bulkCnt in l_deliveryDetailRowidTab.FIRST..l_deliveryDetailRowidTab.LAST
2598 UPDATE Wsh_Delivery_Details Wdd
2599 SET customer_id = decode( customer_id,
2600 G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
2601 customer_id ),
2602 ship_to_site_use_id = decode( ship_to_site_use_id,
2603 G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
2604 ship_to_site_use_id ),
2605 deliver_to_site_use_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
2606 G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
2607 deliver_to_site_use_id ),
2608 ship_to_location_id = decode( ship_to_site_use_id,
2609 G_FROM_CUST_SITE_ID,
2610 decode(ship_to_location_id,
2611 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
2612 ship_to_location_id ),
2613 ship_to_location_id ),
2614 deliver_to_location_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
2615 G_FROM_CUST_SITE_ID,
2616 decode(deliver_to_location_id,
2617 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
2618 deliver_to_location_id ),
2619 deliver_to_location_id ),
2620 last_update_date = SYSDATE,
2621 last_updated_by = arp_standard.profile.user_id,
2622 last_update_login = arp_standard.profile.last_update_login,
2623 request_id = p_req_id,
2624 program_application_id = arp_standard.profile.program_application_id ,
2625 program_id = arp_standard.profile.program_id,
2626 program_update_date = SYSDATE
2627 WHERE Wdd.Rowid = l_deliveryDetailRowidTab(bulkCnt);
2628 END IF;
2629 --
2630 setARMessageRowCount( 'WSH_DELIVERY_DETAILS', SQL%ROWCOUNT );
2631
2632 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
2633 THEN
2634 l_return_status := NULL;
2635
2636 Insert_Log_Table (
2637 p_id_tab => l_deliveryDetailIdTab,
2638 p_table_name => 'WSH_DELIVERY_DETAILS',
2639 p_req_id => p_req_id,
2640 x_return_status => l_return_status );
2641
2642 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2643 THEN
2644 RAISE Process_Open_Lines_Exp;
2645 END IF;
2646 END IF;
2647
2648 -- }
2649 END IF;
2650
2651 -- Unassign from container only if site use code is "SHIP_TO"
2652 IF ( l_deliveryAssgRowidTab.COUNT > 0 and
2653 G_SITE_USE_CODE = 'SHIP_TO' )
2654 THEN
2655 -- {
2656 l_return_status := NULL;
2657 l_dummyIdTab.delete;
2658
2659 Adjust_Weight_Volume (
2660 p_entity_type => 'CONT',
2661 p_delivery_detail => l_deliveryDetailIdTab,
2662 p_parent_delivery_detail => l_parentDeliveryDetailIdTab,
2663 p_delivery_id => l_deliveryIdTab,
2664 p_delivery_leg_id => l_dummyIdTab,
2665 p_net_weight => l_netWeightTab,
2666 p_gross_weight => l_grossWeightTab,
2667 p_volume => l_volumeTab,
2668 x_return_status => l_return_status );
2669
2670 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
2671 WSH_UTIL_CORE.G_RET_STS_WARNING) )
2672 THEN
2673 --
2674 IF ( l_debug_on ) THEN
2675 ARP_MESSAGE.Set_Error('Error returned from API Adjust_Weight_Volume');
2676 END IF;
2677 --
2678 RAISE Process_Open_Lines_Exp;
2679 END IF;
2680
2681 -- Unassign delivery details from containers if it is packed and not
2682 -- assigned to a delivery.
2683 FORALL unassignCnt IN l_deliveryAssgRowidTab.FIRST..l_deliveryAssgRowidTab.LAST
2684 UPDATE WSH_DELIVERY_ASSIGNMENTS
2685 SET parent_delivery_detail_id = null,
2686 last_update_date = SYSDATE,
2687 last_updated_by = arp_standard.profile.user_id,
2688 last_update_login = arp_standard.profile.last_update_login,
2689 program_application_id = arp_standard.profile.program_application_id,
2690 program_id = arp_standard.profile.program_id,
2691 program_update_date = SYSDATE
2692 WHERE rowid = l_deliveryAssgRowidTab(unassignCnt)
2693 AND Parent_Delivery_Detail_Id IS NOT NULL
2694 AND Delivery_Id IS NULL;
2695
2696 --
2697 setARMessageRowCount( 'WSH_DELIVERY_ASSIGNMENTS', SQL%ROWCOUNT );
2698
2699 l_return_status := NULL;
2700 l_dummyIdTab.delete;
2701
2702 Adjust_Parent_WV (
2703 p_entity_type => 'CONT',
2704 p_delivery_detail => l_deliveryDetailIdTab,
2705 p_parent_delivery_detail => l_parentDeliveryDetailIdTab,
2706 p_delivery_id => l_deliveryIdTab,
2707 p_inventory_item_id => l_inventoryItemIdTab,
2708 p_organization_id => l_organizationIdTab,
2709 p_weight_uom => l_weightUomTab,
2710 p_volume_uom => l_volumeUomTab,
2711 x_return_status => l_return_status );
2712
2713 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
2714 WSH_UTIL_CORE.G_RET_STS_WARNING) )
2715 THEN
2716 --
2717 IF ( l_debug_on ) THEN
2718 ARP_MESSAGE.Set_Error('Error returned from API Adjust_Parent_WV');
2719 END IF;
2720 --
2721 RAISE Process_Open_Lines_Exp;
2722 END IF;
2723 -- }
2724 END IF;
2725
2726 -- Log exceptions against conatiners from which delivery details
2727 -- are Unassigned in above update
2728 IF ( l_parentDeliveryDetailIdTab.COUNT > 0 and
2729 G_SITE_USE_CODE = 'SHIP_TO' )
2730 THEN
2731 -- {
2732 l_message_name := 'WSH_CMRG_UNASSIGN_CONTAINER';
2733
2734 FOR expCnt in l_parentDeliveryDetailIdTab.FIRST..l_parentDeliveryDetailIdTab.LAST
2735 LOOP
2736 -- { Loop for logging Exception
2737 IF ( l_parentDeliveryDetailIdTab(expCnt) IS NOT NULL AND
2738 l_deliveryIdTab(expCnt) IS NULL )
2739 THEN
2740 -- {
2741 FND_MESSAGE.Set_Name ('WSH', l_message_name );
2742 FND_MESSAGE.Set_Token ('PS1', G_FROM_CUST_SITE_ID );
2743 FND_MESSAGE.Set_Token ('PS2', G_TO_CUST_SITE_ID );
2744 FND_MESSAGE.Set_Token ('DELIVERY_DETAIL_ID', l_deliveryDetailIdTab(ExpCnt) );
2745
2746 l_message_text := FND_MESSAGE.Get;
2747
2748 l_return_status := NULL;
2749 l_msg_count := NULL;
2750 l_msg_data := NULL;
2751 l_exception_id := NULL;
2752
2753 WSH_XC_UTIL.Log_Exception
2754 (
2755 p_api_version => 1.0,
2756 x_return_status => l_return_status,
2757 x_msg_count => l_msg_count,
2758 x_msg_data => l_msg_data,
2759 x_exception_id => l_exception_id,
2760 p_exception_location_id => G_TO_LOCATION_ID,
2761 p_logged_at_location_id => G_TO_LOCATION_ID,
2762 p_logging_entity => 'SHIPPER',
2763 p_logging_entity_id => Fnd_Global.user_id,
2764 p_exception_name => 'WSH_CUSTOMER_MERGE_CHANGE',
2765 p_message => l_message_text,
2766 p_severity => 'LOW',
2767 p_manually_logged => 'N',
2768 p_delivery_detail_id => l_parentDeliveryDetailIdTab(expCnt),
2769 p_error_message => l_message_text
2770 );
2771
2772 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2773 THEN
2774 ARP_MESSAGE.Set_Error('Error returned from API WSH_XC_UTIL.Log_Exception');
2775 RAISE Process_Open_Lines_Exp;
2776 END IF;
2777
2778 -- }
2779 END IF;
2780 -- } Loop for logging Exception
2781 END LOOP;
2782 -- }
2783 END IF;
2784
2785 -- Insert delivery and stop details into wsh_tmp table
2786 IF ( l_deliveryIdTab.COUNT > 0 and
2787 G_SITE_USE_CODE = 'SHIP_TO' )
2788 THEN
2789 -- Inserting records in bulk into temp table for future reference
2790 -- during processing.
2791 -- Dulplicate entries are avoided using NOT EXISTS condition
2792 FORALL insCnt in l_deliveryIdTab.FIRST..l_deliveryIdTab.LAST
2793 INSERT INTO Wsh_Tmp ( Column1, Column2, Column3 )
2794 SELECT l_deliveryIdTab(insCnt), l_stopIdTab(insCnt), l_deliveryDetailIdTab(insCnt)
2795 FROM DUAL
2796 WHERE l_deliveryIdTab(insCnt) IS NOT NULL
2797 --Start of fix for bug 5900667
2798 --Populate details of delivery/stop into table only if location matches.
2799 AND EXISTS
2800 ( SELECT 'x'
2801 FROM Wsh_New_Deliveries
2802 WHERE ultimate_dropoff_location_id = G_FROM_LOCATION_ID
2803 AND delivery_id = l_deliveryIdTab(insCnt) )
2804 --End of fix for bug 5900667
2805 AND NOT EXISTS
2806 ( SELECT 'x'
2807 FROM Wsh_Tmp
2808 WHERE Column1 = l_deliveryIdTab(insCnt)
2809 AND ( Column2 = l_stopIdTab(insCnt) OR l_stopIdTab(insCnt) is null ) );
2810 END IF;
2811
2812 EXIT WHEN Get_Open_Lines%NOTFOUND;
2813 -- } Loop for Open delivery lines
2814 END LOOP;
2815
2816 CLOSE Get_Open_Lines;
2817
2818 -- Updation of delivery, stop and Unassigning from delivery, stop is done
2819 -- only if G_SITE_USE_CODE is "SHIP_TO"
2820 IF ( G_SITE_USE_CODE = 'SHIP_TO' )
2821 THEN
2822 -- ( site use code
2823 Unassign_Details_From_Delivery (
2824 p_req_id => p_req_id,
2825 x_return_status => l_return_status );
2826
2827 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2828 THEN
2829 RAISE Process_Open_Lines_Exp;
2830 END IF;
2831
2832 SELECT COUNT(*)
2833 INTO l_tmp_cnt
2834 FROM WSH_TMP;
2835
2836 IF ( l_tmp_cnt > 0 )
2837 THEN
2838 Process_Deliveries (
2839 p_req_id => p_req_id,
2840 x_return_status => l_return_status );
2841
2842 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2843 THEN
2844 RAISE Process_Open_Lines_Exp;
2845 END IF;
2846 END IF;
2847 -- } site use code
2848 END IF;
2849
2850 -- Deleting records from Wsh_Tmp table
2851 DELETE FROM Wsh_Tmp;
2852
2853 --
2854 IF l_debug_on THEN
2855 arp_message.set_line('WSH_CUST_MERGE.Process_Open_Lines()+' || getTimeStamp );
2856 END IF;
2857 --
2858 EXCEPTION
2859 WHEN Process_Open_Lines_Exp THEN
2860 x_return_status := l_return_status;
2861 --
2862 IF l_debug_on THEN
2863 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Open_Lines()+ Process_Open_Lines_Exp - ' || getTimeStamp );
2864 END IF;
2865 --
2866
2867 -- Close open cursors
2868 IF ( Get_Open_Lines%ISOPEN ) THEN
2869 CLOSE Get_Open_Lines;
2870 END IF;
2871
2872 WHEN OTHERS THEN
2873 --
2874 IF l_debug_on THEN
2875 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Open_Lines()+ Others - ' || getTimeStamp );
2876 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
2877 END IF;
2878 --
2879 -- Close open cursors
2880 IF ( Get_Open_Lines%ISOPEN ) THEN
2881 CLOSE Get_Open_Lines;
2882 END IF;
2883
2884 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2885 END Process_Open_Lines;
2886
2887 -----------------------------------------------------------------------------------------
2888 --
2889 -- Procedure: Delivery_Details
2890 -- Description: New code to merge customer and site information in WSH_DELIVERY_DETAILS.
2891 -- Usage: Called by WSH_CUST_MERGE.Merge
2892 --
2893 --
2894 -- Assumptions
2895 -- If deliver_to_site_use id is null, it is assumed to be same as
2896 -- ship_to_site_use_id for containers, deliver_to_site_use_id and ship_to_site_use_id will be null.
2897 -- hence, this code looks at the container hierarchy of a line being updated
2898 -- and updates their locations to be same as the corresp. line.(if line is not
2899 -- shipped)
2900 -----------------------------------------------------------------------------------------
2901
2902 Procedure Delivery_Details
2903 (
2904 Req_Id IN NUMBER,
2905 Set_Num IN NUMBER,
2906 Process_Mode IN VARCHAR2
2907 )
2908 IS
2909
2910 CURSOR Get_Shipped_Cont_Lines (
2911 p_customer_id NUMBER,
2912 p_site_use_id NUMBER,
2913 p_location_id NUMBER )
2914 IS
2915 SELECT Det.Delivery_Detail_Id, Det.Rowid
2916 FROM Wsh_Delivery_Details Det
2917 WHERE Det.Container_Flag = 'Y'
2918 AND Det.Delivery_Detail_Id IN
2919 ( SELECT Wda.Parent_Delivery_Detail_Id
2920 FROM Wsh_Delivery_Assignments Wda
2921 WHERE Wda.Parent_Delivery_Detail_Id IS NOT NULL
2922 CONNECT BY PRIOR Wda.Parent_Delivery_Detail_Id = Wda.Delivery_Detail_Id
2923 START WITH wda.delivery_detail_id IN
2924 ( SELECT WDD.Delivery_Detail_Id
2925 FROM Wsh_Delivery_Details WDD,
2926 Wsh_Delivery_Assignments WDA,
2927 Wsh_New_Deliveries WND
2928 WHERE Wnd.Status_Code in ( 'CO', 'CL', 'IT' )
2929 -- Added Parent_Delivery_Detail_Id for Perf. improvement,
2930 -- as per perf. team suggestion.
2931 AND Wda.Parent_Delivery_Detail_Id IS NOT NULL
2932 AND Wnd.Delivery_Id = Wda.Delivery_Id
2933 AND Wda.Delivery_Detail_Id = Wdd.Delivery_Detail_Id
2934 AND Wdd.Container_Flag = 'N'
2935 AND (Wdd.Customer_Id = p_customer_id
2936 --Bug 5900667: Reverting back from AND to OR
2937 OR ((Wdd.Ship_To_Site_Use_Id = p_site_use_id AND
2938 Wdd.Ship_To_Location_Id = p_location_id )
2939 OR (Wdd.Deliver_To_Site_Use_Id = p_site_use_id AND
2940 Wdd.Deliver_To_Location_Id = p_location_id)))
2941 AND Wdd.Released_Status in ( 'Y', 'C', 'X' ) ) )
2942 FOR UPDATE OF Det.Delivery_Detail_Id NOWAIT;
2943
2944 -- Cursor for selecting Closed and Confirmed shipping lines
2945 CURSOR Get_Shipped_Lines ( p_customer_id NUMBER,
2946 p_site_use_id NUMBER,
2947 p_location_id NUMBER )
2948 IS
2949 SELECT Wdd.Delivery_Detail_Id, Wdd.Rowid
2950 FROM Wsh_Delivery_Details WDD,
2951 Wsh_Delivery_Assignments WDA,
2952 Wsh_New_Deliveries WND
2953 WHERE Wnd.Status_Code in ( 'CO', 'CL', 'IT' )
2954 AND Wnd.Delivery_Id = Wda.Delivery_Id
2955 AND Wda.Delivery_Detail_Id = Wdd.Delivery_Detail_Id
2956 AND Wdd.Container_Flag = 'N'
2957 AND (Wdd.Customer_Id = p_customer_id
2958 --Bug 5900667: Reverting back from AND to OR
2959 OR ((Wdd.Ship_To_Site_Use_Id = p_site_use_id
2960 AND Wdd.Ship_To_Location_Id = p_location_id )
2961 OR (Wdd.Deliver_To_Site_Use_Id = p_site_use_id
2962 AND Wdd.Deliver_To_Location_Id = p_location_id)))
2963 AND Wdd.Released_Status in ( 'Y', 'C', 'X' )
2964 FOR UPDATE OF Wdd.Delivery_Detail_Id NOWAIT;
2965
2966 l_fromCustomerIdTab g_number_tbl_type;
2967 l_toCustomerIdTab g_number_tbl_type;
2968 l_fromCustomerSiteIdTab g_number_tbl_type;
2969 l_toCustomerSiteIdTab g_number_tbl_type;
2970 l_orgcustomerMergeHeaderIdTab g_number_tbl_type;
2971 l_deliveryDetailIdTab g_number_tbl_type;
2972 l_customerSiteCodeTab g_char_tbl_type;
2973 l_deliveryDetailRowidTab g_rowid_tbl_type;
2974
2975 l_old_location_id NUMBER;
2976 l_new_location_id NUMBER;
2977 l_return_status VARCHAR2(10);
2978
2979 Merge_Exp EXCEPTION;
2980
2981 --
2982 l_debug_on BOOLEAN;
2983 --
2984 wsh_cust_site_to_loc_err EXCEPTION;
2985
2986 BEGIN
2987 --
2988 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2989 --
2990 IF l_debug_on IS NULL
2991 THEN
2992 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2993 END IF;
2994 --
2995 IF l_debug_on THEN
2996 arp_message.set_line('WSH_CUST_MERGE.Delivery_Details()+' || getTimeStamp );
2997 END IF;
2998
2999 IF (process_mode = 'LOCK') THEN
3000 setARMessageLockTable('WSH_DELIVERY_DETAILS');
3001 NULL;
3002 ELSE
3003 --{ Not Process Lock
3004 -- WSH tables stores only transactions for SHIP_TO and DELIVER_TO transactions
3005 SELECT duplicate_id, customer_id,
3006 duplicate_site_id, customer_site_id, customer_merge_header_id,
3007 customer_site_code
3008 BULK COLLECT INTO l_fromCustomerIdTab, l_toCustomerIdTab,
3009 l_fromCustomerSiteIdTab, l_toCustomerSiteIdTab, l_orgcustomerMergeHeaderIdTab,
3010 l_customerSiteCodeTab
3011 FROM ra_customer_merges
3012 WHERE process_flag = 'N'
3013 AND customer_site_code in ( 'SHIP_TO', 'DELIVER_TO' )
3014 AND request_id = Req_Id
3015 AND set_number = Set_Num;
3016
3017 IF l_fromCustomerIdTab.COUNT > 0
3018 THEN
3019 -- { Record exists in Ra_Customer_Merges table
3020 FOR i in l_fromCustomerIdTab.FIRST..l_fromCustomerIdTab.LAST
3021 LOOP
3022 -- { Main Loop
3023 -- Get Locations of the old/duplicate Site Use ID
3024 --
3025 BEGIN
3026 l_old_location_id := WSH_UTIL_CORE.Cust_Site_To_Location(l_fromCustomerSiteIdTab(i));
3027 EXCEPTION
3028 WHEN NO_DATA_FOUND then
3029 raise wsh_cust_site_to_loc_err;
3030 WHEN OTHERS then
3031 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
3032 raise;
3033 END;
3034 --
3035 --
3036 -- Get Locations of the new Site Use ID
3037 --
3038 BEGIN
3039 l_new_location_id := WSH_UTIL_CORE.Cust_Site_To_Location(l_toCustomerSiteIdTab(i));
3040 EXCEPTION
3041 WHEN NO_DATA_FOUND then
3042 raise wsh_cust_site_to_loc_err;
3043 WHEN OTHERS then
3044 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
3045 raise;
3046 END;
3047 --
3048
3049 -- Populate Into Global Variables.
3050 -- These Global Variables are used in following API's
3051 -- 1. Process_Open_Lines
3052 -- 2. Unassign_Details_From_Delivery
3053 -- 3. Process_Deliveries
3054 -- 4. Insert_Log_Table
3055 G_MERGE_HEADER_ID := l_orgcustomerMergeHeaderIdTab(i);
3056 G_FROM_CUSTOMER_ID := l_fromCustomerIdTab(i);
3057 G_FROM_CUST_SITE_ID := l_fromCustomerSiteIdTab(i);
3058 G_FROM_LOCATION_ID := l_old_location_id;
3059 G_TO_CUSTOMER_ID := l_toCustomerIdTab(i);
3060 G_TO_CUST_SITE_ID := l_toCustomerSiteIdTab(i);
3061 G_TO_LOCATION_ID := l_new_location_id;
3062 G_SITE_USE_CODE := l_customerSiteCodeTab(i);
3063
3064 OPEN Get_Shipped_Cont_Lines (
3065 G_FROM_CUSTOMER_ID,
3066 G_FROM_CUST_SITE_ID,
3067 G_FROM_LOCATION_ID );
3068
3069 LOOP
3070 -- { Loop to process shipped container lines
3071 FETCH Get_Shipped_Cont_Lines
3072 BULK COLLECT INTO l_deliveryDetailIdTab,
3073 l_deliveryDetailRowidTab
3074 LIMIT G_BATCH_LIMIT;
3075
3076 IF ( l_deliveryDetailRowidTab.COUNT > 0 )
3077 THEN
3078 -- {
3079 -- Update Container lines
3080 FORALL bulkCnt in l_deliveryDetailRowidTab.FIRST..l_deliveryDetailRowidTab.LAST
3081 UPDATE Wsh_Delivery_Details WDD
3082 SET customer_id = decode(customer_id, G_FROM_CUSTOMER_ID,
3083 G_TO_CUSTOMER_ID, customer_id ),
3084 ship_to_site_use_id = decode(ship_to_site_use_id,
3085 G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
3086 ship_to_site_use_id ),
3087 deliver_to_site_use_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
3088 G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
3089 deliver_to_site_use_id ),
3090 last_update_date = SYSDATE,
3091 last_updated_by = arp_standard.profile.user_id,
3092 last_update_login = arp_standard.profile.last_update_login,
3093 request_id = req_id,
3094 program_application_id = arp_standard.profile.program_application_id ,
3095 program_id = arp_standard.profile.program_id,
3096 program_update_date = SYSDATE
3097 WHERE Wdd.Container_Flag = 'Y'
3098 AND Wdd.Rowid = l_deliveryDetailRowidTab(bulkCnt);
3099
3100 --
3101 setARMessageRowCount( 'WSH_DELIVERY_DETAILS', SQL%ROWCOUNT );
3102
3103 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
3104 THEN
3105 l_return_status := NULL;
3106
3107 Insert_Log_Table (
3108 p_id_tab => l_deliveryDetailIdTab,
3109 p_table_name => 'WSH_DELIVERY_DETAILS',
3110 p_req_id => req_id,
3111 x_return_status => l_return_status );
3112
3113 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
3114 THEN
3115 RAISE Merge_Exp;
3116 END IF;
3117 END IF;
3118 -- }
3119 END IF;
3120
3121 EXIT WHEN Get_Shipped_Cont_Lines%NOTFOUND;
3122
3123 -- } Loop to process shipped container lines
3124 END LOOP;
3125
3126 CLOSE Get_Shipped_Cont_Lines;
3127
3128 OPEN Get_Shipped_Lines (
3129 G_FROM_CUSTOMER_ID,
3130 G_FROM_CUST_SITE_ID,
3131 G_FROM_LOCATION_ID );
3132 LOOP
3133 -- { Loop to process shipped lines
3134 FETCH Get_Shipped_Lines
3135 BULK COLLECT INTO l_deliveryDetailIdTab,
3136 l_deliveryDetailRowidTab
3137 LIMIT G_BATCH_LIMIT;
3138
3139 IF ( l_deliveryDetailIdTab.COUNT > 0 )
3140 THEN
3141 -- {
3142 -- Update non-container lines
3143 IF l_deliveryDetailRowidTab.COUNT > 0 THEN
3144 FORALL bulkCnt in l_deliveryDetailRowidTab.FIRST..l_deliveryDetailRowidTab.LAST
3145 UPDATE Wsh_Delivery_Details Wdd
3146 SET customer_id = decode(customer_id, G_FROM_CUSTOMER_ID,
3147 G_TO_CUSTOMER_ID, customer_id ),
3148 ship_to_site_use_id = decode(ship_to_site_use_id,
3149 G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
3150 ship_to_site_use_id ),
3151 deliver_to_site_use_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
3152 G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
3153 deliver_to_site_use_id ),
3154 last_update_date = SYSDATE,
3155 last_updated_by = arp_standard.profile.user_id,
3156 last_update_login = arp_standard.profile.last_update_login,
3157 request_id = req_id,
3158 program_application_id = arp_standard.profile.program_application_id ,
3159 program_id = arp_standard.profile.program_id,
3160 program_update_date = SYSDATE
3161 WHERE Wdd.Rowid = l_deliveryDetailRowidTab(bulkCnt);
3162 END IF;
3163 --
3164 setARMessageRowCount( 'WSH_DELIVERY_DETAILS', SQL%ROWCOUNT );
3165
3166 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
3167 THEN
3168 l_return_status := NULL;
3169
3170 Insert_Log_Table (
3171 p_id_tab => l_deliveryDetailIdTab,
3172 p_table_name => 'WSH_DELIVERY_DETAILS',
3173 p_req_id => req_id,
3174 x_return_status => l_return_status );
3175
3176 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
3177 THEN
3178 --
3179 IF ( l_debug_on ) THEN
3180 ARP_MESSAGE.Set_Error('Error returned from API Insert_Log_Table');
3181 END IF;
3182 --
3183 RAISE Merge_Exp;
3184 END IF;
3185 END IF;
3186 -- }
3187 END IF;
3188
3189 EXIT WHEN Get_Shipped_Lines%NOTFOUND;
3190 -- } Loop to process shipped lines
3191 END LOOP;
3192
3193 CLOSE Get_Shipped_Lines;
3194
3195 DELETE FROM Wsh_Tmp;
3196
3197 -- Processes open delivery detail lines
3198 -- All others necessary values are taken from Global Variables
3199 Process_Open_Lines (
3200 p_req_id => req_id,
3201 x_return_status => l_return_status );
3202
3203 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
3204 THEN
3205 --
3206 IF ( l_debug_on ) THEN
3207 ARP_MESSAGE.Set_Error('Error returned from API Process_Open_Lines');
3208 END IF;
3209 --
3210 RAISE Merge_Exp;
3211 END IF;
3212
3213 IF ( G_SITE_USE_CODE = 'SHIP_TO' )
3214 THEN
3215 -- {
3216 -- Processes open delivery lines which are Empty/Contains only
3217 -- under the delivery.
3218 Process_Open_Deliveries (
3219 p_req_id => req_id,
3220 x_return_status => l_return_status );
3221
3222 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
3223 THEN
3224 --
3225 IF ( l_debug_on ) THEN
3226 ARP_MESSAGE.Set_Error('Error returned from API Process_Open_Deliveries');
3227 END IF;
3228 --
3229 RAISE Merge_Exp;
3230 END IF;
3231 -- }
3232 END IF;
3233 -- } Main Loop
3234 END LOOP;
3235 -- } Record exists in Ra_Customer_Merges table
3236 END IF;
3237 --} Not Process Lock
3238 END IF;
3239
3240
3241 IF l_debug_on THEN
3242 arp_message.set_line('WSH_CUST_MERGE.Delivery_Details()-' || getTimeStamp);
3243 END IF;
3244
3245 EXCEPTION
3246 WHEN wsh_cust_site_to_loc_err THEN
3247 --
3248 IF ( l_debug_on ) THEN
3249 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Delivery_Details()+ wsh_cust_site_to_loc_err - ' || getTimeStamp );
3250 END IF;
3251 --
3252
3253 RAISE;
3254 WHEN Merge_Exp THEN
3255 --
3256 IF ( l_debug_on ) THEN
3257 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Delivery_Details - Merge_Exp');
3258 END IF;
3259 --
3260
3261 -- Close if cursors are open
3262 IF ( Get_Shipped_Cont_Lines%ISOPEN ) THEN
3263 CLOSE Get_Shipped_Cont_Lines;
3264 END IF;
3265
3266 IF ( Get_Shipped_Lines%ISOPEN ) THEN
3267 CLOSE Get_Shipped_Lines;
3268 END IF;
3269
3270 RAISE;
3271
3272 WHEN OTHERS THEN
3273 --
3274 IF ( l_debug_on ) THEN
3275 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Delivery_Details()+ Others - ' || getTimeStamp );
3276 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
3277 END IF;
3278 --
3279
3280 -- Close if cursors are open
3281 IF ( Get_Shipped_Cont_Lines%ISOPEN ) THEN
3282 CLOSE Get_Shipped_Cont_Lines;
3283 END IF;
3284
3285 IF ( Get_Shipped_Lines%ISOPEN ) THEN
3286 CLOSE Get_Shipped_Lines;
3287 END IF;
3288
3289 RAISE;
3290
3291 END Delivery_Details;
3292
3293 --
3294 --
3295 -- Procedure : Picking_batches
3296 -- Description : New code to merge customer and site information in
3297 -- WSH_PICKING_BATCHES
3298 -- Usage : Called by WSH_CUST_MERGE.Merge
3299
3300 Procedure Picking_batches
3301 (
3302 Req_Id IN NUMBER,
3303 Set_Num IN NUMBER,
3304 Process_Mode IN VARCHAR2
3305 )
3306 IS
3307 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
3308 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
3309 INDEX BY BINARY_INTEGER;
3310 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
3311
3312 TYPE BATCH_ID_LIST_TYPE IS TABLE OF
3313 WSH_PICKING_BATCHES.BATCH_ID%TYPE
3314 INDEX BY BINARY_INTEGER;
3315 PRIMARY_KEY_ID_LIST BATCH_ID_LIST_TYPE;
3316
3317 TYPE customer_id_LIST_TYPE IS TABLE OF
3318 WSH_PICKING_BATCHES.customer_id%TYPE
3319 INDEX BY BINARY_INTEGER;
3320 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
3321 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
3322
3323 l_profile_val VARCHAR2(30);
3324 CURSOR merged_records IS
3325 SELECT distinct CUSTOMER_MERGE_HEADER_ID
3326 ,BATCH_ID
3327 ,yt.customer_id
3328 FROM WSH_PICKING_BATCHES yt, ra_customer_merges m
3329 WHERE (
3330 yt.customer_id = m.DUPLICATE_ID
3331 ) AND m.process_flag = 'N'
3332 AND m.request_id = req_id
3333 AND m.set_number = set_num;
3334 l_last_fetch BOOLEAN := FALSE;
3335 l_count NUMBER :=0;
3336 --
3337 l_debug_on BOOLEAN;
3338 --
3339 BEGIN
3340 --
3341 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3342 --
3343 IF l_debug_on IS NULL
3344 THEN
3345 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3346 END IF;
3347 --
3348 IF l_debug_on THEN
3349 arp_message.set_line('WSH_CUST_MERGE.PICKING_BATCHES()+' || getTimeStamp);
3350 END IF;
3351 IF process_mode='LOCK' THEN
3352 setARMessageLockTable('WSH_PICKING_BATCHES');
3353 NULL;
3354 ELSE
3355 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
3356 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','WSH_PICKING_BATCHES',FALSE);
3357 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
3358 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
3359
3360 open merged_records;
3361 LOOP
3362 FETCH merged_records BULK COLLECT INTO
3363 MERGE_HEADER_ID_LIST
3364 , PRIMARY_KEY_ID_LIST
3365 , NUM_COL1_ORIG_LIST
3366 limit 1000;
3367 IF merged_records%NOTFOUND THEN
3368 l_last_fetch := TRUE;
3369 END IF;
3370 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
3371 exit;
3372 END IF;
3373 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
3374 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
3375 END LOOP;
3376 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3377 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
3378 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
3379 MERGE_LOG_ID,
3380 TABLE_NAME,
3381 MERGE_HEADER_ID,
3382 PRIMARY_KEY_ID,
3383 NUM_COL1_ORIG,
3384 NUM_COL1_NEW,
3385 ACTION_FLAG,
3386 REQUEST_ID,
3387 CREATED_BY,
3388 CREATION_DATE,
3389 LAST_UPDATE_LOGIN,
3390 LAST_UPDATE_DATE,
3391 LAST_UPDATED_BY
3392 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
3393 'WSH_PICKING_BATCHES',
3394 MERGE_HEADER_ID_LIST(I),
3395 PRIMARY_KEY_ID_LIST(I),
3396 NUM_COL1_ORIG_LIST(I),
3397 NUM_COL1_NEW_LIST(I),
3398 'U',
3399 req_id,
3400 hz_utility_pub.CREATED_BY,
3401 hz_utility_pub.CREATION_DATE,
3402 hz_utility_pub.LAST_UPDATE_LOGIN,
3403 hz_utility_pub.LAST_UPDATE_DATE,
3404 hz_utility_pub.LAST_UPDATED_BY
3405 );
3406
3407 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
3408 UPDATE WSH_PICKING_BATCHES yt SET
3409 customer_id=NUM_COL1_NEW_LIST(I)
3410 , LAST_UPDATE_DATE=SYSDATE
3411 , last_updated_by=arp_standard.profile.user_id
3412 , last_update_login=arp_standard.profile.last_update_login
3413 , REQUEST_ID=req_id
3414 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
3415 , PROGRAM_ID=arp_standard.profile.program_id
3416 , PROGRAM_UPDATE_DATE=SYSDATE
3417 WHERE BATCH_ID=PRIMARY_KEY_ID_LIST(I)
3418 ;
3419 l_count := l_count + SQL%ROWCOUNT;
3420 IF l_last_fetch THEN
3421 EXIT;
3422 END IF;
3423 END LOOP;
3424
3425 arp_message.set_name('AR','AR_ROWS_UPDATED');
3426 arp_message.set_token('NUM_ROWS',to_char(l_count));
3427 END IF;
3428 IF l_debug_on THEN
3429 arp_message.set_line('WSH_CUST_MERGE.PICKING_BATCHES()+' || getTimeStamp);
3430 END IF;
3431 EXCEPTION
3432 WHEN OTHERS THEN
3433 arp_message.set_line( 'WSH_MERGE_PICKING_BATCHES');
3434 RAISE;
3435 END Picking_batches;
3436 --
3437 --
3438 -- Procedure : Calendar_Assignments
3439 -- Description : New code to merge customer and site information in
3440 -- WSH_CALENDAR_ASSIGNMENTS
3441 -- Usage : Called by WSH_CUST_MERGE.Merge
3442
3443 Procedure Calendar_Assignments
3444 (
3445 Req_Id IN NUMBER,
3446 Set_Num IN NUMBER,
3447 Process_Mode IN VARCHAR2
3448 )
3449 IS
3450 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
3451 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
3452 INDEX BY BINARY_INTEGER;
3453 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
3454
3455 TYPE CALENDAR_ASSIGNMENT_ID_LIST_TY IS TABLE OF
3456 WSH_CALENDAR_ASSIGNMENTS.CALENDAR_ASSIGNMENT_ID%TYPE
3457 INDEX BY BINARY_INTEGER;
3458 PRIMARY_KEY_ID_LIST CALENDAR_ASSIGNMENT_ID_LIST_TY;
3459
3460 TYPE customer_id_LIST_TYPE IS TABLE OF
3461 WSH_CALENDAR_ASSIGNMENTS.customer_id%TYPE
3462 INDEX BY BINARY_INTEGER;
3463 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
3464 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
3465
3466 TYPE customer_site_use_id_LIST_TYPE IS TABLE OF
3467 WSH_CALENDAR_ASSIGNMENTS.customer_site_use_id%TYPE
3468 INDEX BY BINARY_INTEGER;
3469 NUM_COL2_ORIG_LIST customer_site_use_id_LIST_TYPE;
3470 NUM_COL2_NEW_LIST customer_site_use_id_LIST_TYPE;
3471
3472 l_profile_val VARCHAR2(30);
3473 CURSOR merged_records IS
3474 SELECT distinct CUSTOMER_MERGE_HEADER_ID
3475 ,CALENDAR_ASSIGNMENT_ID
3476 ,yt.customer_id
3477 ,yt.customer_site_use_id
3478 FROM WSH_CALENDAR_ASSIGNMENTS yt, ra_customer_merges m
3479 WHERE (
3480 yt.customer_id = m.DUPLICATE_ID
3481 OR yt.customer_site_use_id = m.DUPLICATE_SITE_ID
3482 ) AND m.process_flag = 'N'
3483 AND m.request_id = req_id
3484 AND m.set_number = set_num;
3485 l_last_fetch BOOLEAN := FALSE;
3486 l_count NUMBER :=0;
3487 --
3488 l_debug_on BOOLEAN;
3489 --
3490 BEGIN
3491 IF l_debug_on THEN
3492 arp_message.set_line('WSH_CUST_MERGE.CALENDAR_ASSIGNMENTS()+' || getTimeStamp);
3493 END IF;
3494
3495 IF process_mode='LOCK' THEN
3496 setARMessageLockTable('WSH_CALENDAR_ASSIGNMENTS');
3497 NULL;
3498 ELSE
3499 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
3500 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','WSH_CALENDAR_ASSIGNMENTS',FALSE);
3501 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
3502 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
3503
3504 open merged_records;
3505 LOOP
3506 FETCH merged_records BULK COLLECT INTO
3507 MERGE_HEADER_ID_LIST
3508 , PRIMARY_KEY_ID_LIST
3509 , NUM_COL1_ORIG_LIST
3510 , NUM_COL2_ORIG_LIST
3511 limit 1000;
3512 IF merged_records%NOTFOUND THEN
3513 l_last_fetch := TRUE;
3514 END IF;
3515 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
3516 exit;
3517 END IF;
3518 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
3519 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
3520 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
3521 END LOOP;
3522 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3523 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
3524 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
3525 MERGE_LOG_ID,
3526 TABLE_NAME,
3527 MERGE_HEADER_ID,
3528 PRIMARY_KEY_ID,
3529 NUM_COL1_ORIG,
3530 NUM_COL1_NEW,
3531 NUM_COL2_ORIG,
3532 NUM_COL2_NEW,
3533 ACTION_FLAG,
3534 REQUEST_ID,
3535 CREATED_BY,
3536 CREATION_DATE,
3537 LAST_UPDATE_LOGIN,
3538 LAST_UPDATE_DATE,
3539 LAST_UPDATED_BY
3540 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
3541 'WSH_CALENDAR_ASSIGNMENTS',
3542 MERGE_HEADER_ID_LIST(I),
3543 PRIMARY_KEY_ID_LIST(I),
3544 NUM_COL1_ORIG_LIST(I),
3545 NUM_COL1_NEW_LIST(I),
3546 NUM_COL2_ORIG_LIST(I),
3547 NUM_COL2_NEW_LIST(I),
3548 'U',
3549 req_id,
3550 hz_utility_pub.CREATED_BY,
3551 hz_utility_pub.CREATION_DATE,
3552 hz_utility_pub.LAST_UPDATE_LOGIN,
3553 hz_utility_pub.LAST_UPDATE_DATE,
3554 hz_utility_pub.LAST_UPDATED_BY
3555 );
3556
3557 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
3558 UPDATE WSH_CALENDAR_ASSIGNMENTS yt SET
3559 customer_id=NUM_COL1_NEW_LIST(I)
3560 ,customer_site_use_id=NUM_COL2_NEW_LIST(I)
3561 , LAST_UPDATE_DATE=SYSDATE
3562 , last_updated_by=arp_standard.profile.user_id
3563 , last_update_login=arp_standard.profile.last_update_login
3564 , REQUEST_ID=req_id
3565 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
3566 , PROGRAM_ID=arp_standard.profile.program_id
3567 , PROGRAM_UPDATE_DATE=SYSDATE
3568 WHERE CALENDAR_ASSIGNMENT_ID=PRIMARY_KEY_ID_LIST(I)
3569 ;
3570 l_count := l_count + SQL%ROWCOUNT;
3571 IF l_last_fetch THEN
3572 EXIT;
3573 END IF;
3574 END LOOP;
3575
3576 arp_message.set_name('AR','AR_ROWS_UPDATED');
3577 arp_message.set_token('NUM_ROWS',to_char(l_count));
3578 END IF;
3579 IF l_debug_on THEN
3580 arp_message.set_line('WSH_CUST_MERGE.CALENDAR_ASSIGNMENTS()+' || getTimeStamp);
3581 END IF;
3582 EXCEPTION
3583 WHEN OTHERS THEN
3584 arp_message.set_line( 'WSH_MERGE_CALENDAR_ASSIGNMENTS');
3585 RAISE;
3586 END Calendar_Assignments;
3587
3588 -----------------------------------------------------------------------------------------
3589 --
3590 --
3591 -- Procedure : Picking_rules
3592 -- Description : New code to merge customer and site information in
3593 -- WSH_PICKING_RULES
3594 -- Usage : Called by WSH_CUST_MERGE.Merge
3595 --
3596 -----------------------------------------------------------------------------------------
3597
3598 Procedure Picking_rules ( Req_Id IN NUMBER,
3599 Set_Num IN NUMBER,
3600 Process_Mode IN VARCHAR2)
3601 IS
3602 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
3603 INDEX BY BINARY_INTEGER;
3604 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
3605
3606 TYPE PICKING_RULE_ID_LIST_TYPE IS TABLE OF WSH_PICKING_RULES.PICKING_RULE_ID%TYPE
3607 INDEX BY BINARY_INTEGER;
3608 PRIMARY_KEY_ID_LIST PICKING_RULE_ID_LIST_TYPE;
3609
3610 TYPE customer_id_LIST_TYPE IS TABLE OF WSH_PICKING_RULES.customer_id%TYPE
3611 INDEX BY BINARY_INTEGER;
3612 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
3613 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
3614
3615 l_profile_val VARCHAR2(30);
3616 CURSOR merged_records IS
3617 SELECT distinct CUSTOMER_MERGE_HEADER_ID
3618 ,PICKING_RULE_ID
3619 ,yt.customer_id
3620 FROM WSH_PICKING_RULES yt, ra_customer_merges m
3621 WHERE (
3622 yt.customer_id = m.DUPLICATE_ID
3623 ) AND m.process_flag = 'N'
3624 AND m.request_id = req_id
3625 AND m.set_number = set_num;
3626 l_last_fetch BOOLEAN := FALSE;
3627 l_count NUMBER :=0;
3628 --
3629 l_debug_on BOOLEAN;
3630 --
3631 BEGIN
3632 --
3633 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3634 --
3635 IF l_debug_on IS NULL
3636 THEN
3637 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3638 END IF;
3639 --
3640 IF l_debug_on THEN
3641 arp_message.set_line('WSH_CUST_MERGE.PICKING_RULES()+' || getTimeStamp);
3642 END IF;
3643
3644 IF process_mode='LOCK' THEN
3645 setARMessageLockTable('WSH_PICKING_RULES');
3646 NULL;
3647 ELSE
3648 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
3649 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','WSH_PICKING_RULES',FALSE);
3650 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
3651 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
3652
3653 open merged_records;
3654 LOOP
3655 FETCH merged_records BULK COLLECT INTO
3656 MERGE_HEADER_ID_LIST
3657 , PRIMARY_KEY_ID_LIST
3658 , NUM_COL1_ORIG_LIST
3659 limit 1000;
3660 IF merged_records%NOTFOUND THEN
3661 l_last_fetch := TRUE;
3662 END IF;
3663 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
3664 exit;
3665 END IF;
3666 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
3667 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
3668 END LOOP;
3669 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3670 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
3671 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
3672 MERGE_LOG_ID,
3673 TABLE_NAME,
3674 MERGE_HEADER_ID,
3675 PRIMARY_KEY_ID,
3676 NUM_COL1_ORIG,
3677 NUM_COL1_NEW,
3678 ACTION_FLAG,
3679 REQUEST_ID,
3680 CREATED_BY,
3681 CREATION_DATE,
3682 LAST_UPDATE_LOGIN,
3683 LAST_UPDATE_DATE,
3684 LAST_UPDATED_BY
3685 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
3686 'WSH_PICKING_RULES',
3687 MERGE_HEADER_ID_LIST(I),
3688 PRIMARY_KEY_ID_LIST(I),
3689 NUM_COL1_ORIG_LIST(I),
3690 NUM_COL1_NEW_LIST(I),
3691 'U',
3692 req_id,
3693 hz_utility_pub.CREATED_BY,
3694 hz_utility_pub.CREATION_DATE,
3695 hz_utility_pub.LAST_UPDATE_LOGIN,
3696 hz_utility_pub.LAST_UPDATE_DATE,
3697 hz_utility_pub.LAST_UPDATED_BY
3698 );
3699
3700 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
3701 UPDATE WSH_PICKING_RULES yt SET
3702 customer_id=NUM_COL1_NEW_LIST(I)
3703 , LAST_UPDATE_DATE=SYSDATE
3704 , last_updated_by=arp_standard.profile.user_id
3705 , last_update_login=arp_standard.profile.last_update_login
3706 , REQUEST_ID=req_id
3707 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
3708 , PROGRAM_ID=arp_standard.profile.program_id
3709 , PROGRAM_UPDATE_DATE=SYSDATE
3710 WHERE PICKING_RULE_ID=PRIMARY_KEY_ID_LIST(I)
3711 ;
3712 l_count := l_count + SQL%ROWCOUNT;
3713
3714 IF l_last_fetch THEN
3715 EXIT;
3716 END IF;
3717 END LOOP;
3718
3719 arp_message.set_name('AR','AR_ROWS_UPDATED');
3720 arp_message.set_token('NUM_ROWS',to_char(l_count));
3721 END IF;
3722 IF l_debug_on THEN
3723 arp_message.set_line('WSH_CUST_MERGE.PICKING_RULES()-' || getTimeStamp);
3724 END IF;
3725 EXCEPTION
3726 WHEN OTHERS THEN
3727 arp_message.set_line( 'WSH_MERGE_PICKING_RULES');
3728 RAISE;
3729 END Picking_rules;
3730
3731
3732 -----------------------------------------------------------------------------------------
3733 --
3734 -- PROCEDURE: CHECK_WMS_DETIALS
3735 -- PURPOSE: To check whether the merge involves wms organisations in which
3736 -- the merge will be filed
3737 --
3738 -----------------------------------------------------------------------------------------
3739 Procedure Check_WMS_Details ( Req_Id IN NUMBER,
3740 Set_Num IN NUMBER,
3741 Process_Mode IN VARCHAR2 )
3742 IS
3743 /* Bug 7117470 According to vedo condition, Check_Wms_Details should raise
3744 exception only if Delivery_Detail is WMS Enabled and Staged ('Y') */
3745 CURSOR C1 IS
3746 SELECT 1 FROM DUAL
3747 WHERE EXISTS
3748 ( SELECT 'x'
3749 FROM wsh_delivery_details wdd,
3750 wsh_delivery_assignments wda,
3751 ra_customer_merges rcm,
3752 mtl_parameters mtl
3753 WHERE mtl.wms_enabled_flag = 'Y'
3754 AND mtl.organization_id = wdd.organization_id
3755 AND wda.parent_delivery_detail_id IS NOT NULL
3756 -- AND wda.delivery_id IS NULL
3757 AND wda.delivery_detail_id = wdd.delivery_detail_id
3758 AND wdd.customer_id = rcm.duplicate_id
3759 AND wdd.ship_to_location_id = WSH_UTIL_CORE.Cust_Site_To_Location( duplicate_site_id )
3760 AND wdd.container_flag = 'N'
3761 -- AND wdd.released_status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
3762 AND wdd.released_status = 'Y'
3763 AND rcm.customer_site_code = 'SHIP_TO'
3764 AND rcm.process_flag = 'N'
3765 AND rcm.request_id = Req_Id
3766 AND rcm.set_number = Set_Num );
3767
3768 l_count NUMBER;
3769 WMS_Exception EXCEPTION;
3770 --
3771 l_debug_on BOOLEAN;
3772 --
3773 BEGIN
3774 --
3775 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3776 --
3777 IF l_debug_on IS NULL THEN
3778 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3779 END IF;
3780 --
3781 IF l_debug_on THEN
3782 arp_message.set_line('WSH_CUST_MERGE.Check_WMS_Details()+' || getTimeStamp );
3783 END IF;
3784 --
3785
3786 OPEN C1;
3787 FETCH C1 INTO l_count;
3788 IF ( C1%FOUND ) THEN
3789 --
3790 IF ( l_debug_on ) THEN
3791 ARP_MESSAGE.Set_Error('There exists WMS records in shipping which are Staged');
3792 END IF;
3793 --
3794 CLOSE C1;
3795 RAISE WMS_Exception;
3796 END IF;
3797 CLOSE C1;
3798
3799 --
3800 IF l_debug_on THEN
3801 arp_message.set_line('WSH_CUST_MERGE.Check_WMS_Details()+' || getTimeStamp );
3802 END IF;
3803 --
3804 EXCEPTION
3805 WHEN WMS_Exception THEN
3806 --
3807 IF ( l_debug_on ) THEN
3808 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Check_WMS_Details - WMS_Exception');
3809 END IF;
3810 --
3811 IF ( C1%ISOPEN ) THEN
3812 CLOSE C1;
3813 END IF;
3814 --
3815 RAISE;
3816 --
3817 WHEN OTHERS THEN
3818 --
3819 IF ( l_debug_on ) THEN
3820 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Check_WMS_Details()+ Others - ' || getTimeStamp );
3821 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
3822 END IF;
3823 --
3824 IF ( C1%ISOPEN ) THEN
3825 CLOSE C1;
3826 END IF;
3827 --
3828 RAISE;
3829 --
3830 END Check_WMS_Details;
3831 --
3832 -- LSP PROJECT : Begin
3833 -----------------------------------------------------------------------------------------
3834 --
3835 -- PROCEDURE: CHECK_LSP_INSTALL
3836 -- PURPOSE: To check whether the merge involves LSP clients.
3837 -- Merge is not allowed from LSP client to another LSP client/customer OR from customer to LSP client.
3838 -- However merge is allowed when both from and to LSP clients are same.
3839 --
3840 -----------------------------------------------------------------------------------------
3841 Procedure Check_LSP_Install ( Req_Id IN NUMBER,
3842 Set_Num IN NUMBER,
3843 Process_Mode IN VARCHAR2 )
3844 IS
3845 CURSOR C1 IS
3846 SELECT 1 FROM DUAL
3847 WHERE EXISTS
3848 ( SELECT 'x'
3849 FROM mtl_client_parameters_v mcp,
3850 ra_customer_merges rcm
3851 WHERE rcm.duplicate_id <> rcm.customer_id
3852 AND rcm.process_flag = 'N'
3853 AND rcm.request_id = Req_Id
3854 AND rcm.set_number = Set_Num
3855 AND ( mcp.client_id = rcm.duplicate_id
3856 OR mcp.client_id = rcm.customer_id));
3857
3858 l_count NUMBER;
3859 LSP_Exception EXCEPTION;
3860 --
3861 l_debug_on BOOLEAN;
3862 --
3863 BEGIN
3864 --
3865 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3866 --
3867 IF l_debug_on IS NULL THEN
3868 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3869 END IF;
3870 --
3871 IF l_debug_on THEN
3872 arp_message.set_line('WSH_CUST_MERGE.Check_LSP_Install()+' || getTimeStamp );
3873 END IF;
3874 --
3875 IF WMS_DEPLOY.WMS_DEPLOYMENT_MODE = 'L' THEN
3876 IF l_debug_on THEN
3877 arp_message.set_line('WMS Deployment Mode profile is set to LSP');
3878 END IF;
3879 OPEN C1;
3880 FETCH C1 INTO l_count;
3881 IF ( C1%FOUND ) THEN
3882 --
3883 IF ( l_debug_on ) THEN
3884 ARP_MESSAGE.Set_Error('Merge is not allowed from LSP client to another LSP client/customer OR from customer to LSP client');
3885 END IF;
3886 --
3887 CLOSE C1;
3888 RAISE LSP_Exception;
3889 END IF;
3890 CLOSE C1;
3891 ELSE
3892 IF l_debug_on THEN
3893 arp_message.set_line('WMS Deployment Mode profile value is not LSP');
3894 END IF;
3895 END IF;
3896 --
3897 IF l_debug_on THEN
3898 arp_message.set_line('WSH_CUST_MERGE.Check_LSP_Install()+' || getTimeStamp );
3899 END IF;
3900 --
3901 EXCEPTION
3902 WHEN LSP_Exception THEN
3903 --
3904 IF ( l_debug_on ) THEN
3905 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Check_LSP_Install - LSP_Exception');
3906 END IF;
3907 --
3908 IF ( C1%ISOPEN ) THEN
3909 CLOSE C1;
3910 END IF;
3911 --
3912 RAISE;
3913 --
3914 WHEN OTHERS THEN
3915 --
3916 IF ( l_debug_on ) THEN
3917 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Check_LSP_Install()+ Others - ' || getTimeStamp );
3918 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
3919 END IF;
3920 --
3921 IF ( C1%ISOPEN ) THEN
3922 CLOSE C1;
3923 END IF;
3924 --
3925 RAISE;
3926 --
3927 END Check_LSP_Install;
3928 --
3929 -- LSP PROJECT : end
3930
3931 -----------------------------------------------------------------------------------------
3932 --
3933 --
3934 -- Procedure: Merge
3935 -- Description: New code to merge customer and site information
3936 -- throughout WSH. This is the main procedure for
3937 -- customer merge for WSH, which calls all other internal
3938 -- procedures for customer merge based on the functional areas.
3939 -- Usage: Called by TCA's Customer Merge.
3940 --
3941 -----------------------------------------------------------------------------------------
3942
3943 PROCEDURE Merge(Req_Id IN NUMBER, Set_Num IN NUMBER, Process_Mode IN VARCHAR2 )
3944 IS
3945 l_duplicateIdTab g_number_tbl_type;
3946 l_customerIdTab g_number_tbl_type;
3947 l_duplicateSiteIdTab g_number_tbl_type;
3948 l_customerSiteIdTab g_number_tbl_type;
3949 l_customerMergeHeaderIdTab g_number_tbl_type;
3950
3951 BEGIN
3952 /* Calls to other internal procedures for customer Merge */
3953 arp_message.set_line('WSH_CUST_MERGE.Merge()+' || getTimeStamp);
3954 --
3955 arp_message.set_line('Req_Id,Set_Num,Process_Mode:' || Req_Id||','||Set_Num||','||Process_Mode);
3956
3957 --
3958 -- For inserting record into HZ Log Table based on profile option value
3959 --
3960 IF ( G_PROFILE_VAL IS NULL ) THEN
3961 G_PROFILE_VAL := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
3962 END IF;
3963
3964 --
3965 -- Check whether FTE is Installed
3966 --
3967 IF ( G_FTE_INSTALLED IS NULL ) THEN
3968 G_FTE_INSTALLED := WSH_UTIL_CORE.Fte_Is_Installed;
3969 END IF;
3970 -- LSP PROJECT : Check for LSP clients.
3971 WSH_CUST_MERGE.Check_Lsp_Install( Req_Id, Set_Num, Process_Mode ); -- ADDED
3972 WSH_CUST_MERGE.Check_WMS_Details( Req_Id, Set_Num, Process_Mode ); -- ADDED
3973 WSH_CUST_MERGE.Delivery_Details( Req_Id, Set_Num, Process_Mode );
3974 -- WSH_CUST_MERGE.Deliveries( Req_Id, Set_Num, Process_Mode );
3975 WSH_CUST_MERGE.Picking_Rules( Req_Id, Set_Num, Process_Mode ); -- NOCHANGE
3976 WSH_CUST_MERGE.Picking_Batches( Req_Id, Set_Num, Process_Mode ); -- NOCHANGE
3977 WSH_CUST_MERGE.Calendar_Assignments(Req_Id, Set_Num, Process_Mode ); -- NOCHANGE
3978
3979 arp_message.set_line('WSH_CUST_MERGE.Merge()-' || getTimeStamp);
3980
3981 EXCEPTION
3982 WHEN OTHERS THEN
3983 arp_message.set_error('WSH_CUST_MERGE.Merge');
3984 RAISE;
3985
3986 END Merge;
3987
3988 END WSH_CUST_MERGE;