[Home] [Help]
PACKAGE BODY: APPS.WSH_CUST_MERGE
Source
1 PACKAGE BODY WSH_CUST_MERGE as
2 /* $Header: WSHCMRGB.pls 120.9.12010000.2 2008/08/04 12:29:20 suppal 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
431 --
428 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
429 END IF;
430 --
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
566 FROM WSH_DELIVERY_DETAILS Wdd,
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
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
671 l_is_delivery_empty VARCHAR2(1);
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;
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,
806
803 program_id = fnd_global.conc_program_id,
804 program_update_date = SYSDATE
805 WHERE rowid = l_deliveryAssgRowidTab(unassignDelCnt);
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 -- do not update if assign/unassign 0/NULL weight line/LPN
844 -- to nonempty delivery
845 IF (l_grossWeightTab(l_index) <> 0) THEN
846 l_count := l_count + 1;
847 l_delivery_id_tab(l_count) := l_deliveryIdTab(l_index);
848 l_interface_flag_tab(l_count) := NULL;
849 --setting this flag here to null so that the
850 --update_tms_interface_flag procedure will take care of it.
851 IF (l_debug_on) THEN
852 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));
853 END IF;
854 END IF;
855 END IF;
856
857 END IF;
858 l_index := l_deliveryIdTab.NEXT(l_index);
859 END LOOP;
860
861 IF l_count > 0 THEN
862 WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG(
863 p_delivery_id_tab => l_delivery_id_tab,
864 p_tms_interface_flag_tab => l_interface_flag_tab,
865 x_return_status => l_return_status);
866
867 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_ERROR, WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR)) THEN
868 --
869 IF l_debug_on THEN
870 ARP_MESSAGE.Set_Error('Error returned from API WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG '||l_return_status);
871 END IF;
872 RAISE Unassign_Del_Exp;
873 END IF;
874 END IF;
875
876 END IF;
877 -- End of OTM R12 : unassign delivery detail
878
879
880 -- For WMS Enabled org and Release to Warehouse lines
881 IF ( l_moveOrderLineIdTab.COUNT > 0 )
882 THEN
883 -- { WMS Loop
884 FOR wmsCnt IN l_moveOrderLineIdTab.FIRST..l_moveOrderLineIdTab.LAST
885 LOOP
886 IF ( l_releasedStatusTab(wmsCnt) = 'S' AND
887 l_moveOrderLineIdTab(wmsCnt) is not null )
888 THEN
889 -- { Released Status If
890 IF ( NOT G_WMS_ENABLED.EXISTS(l_organizationIdTab(wmsCnt)) )
891 THEN
892 G_WMS_ENABLED(l_organizationIdTab(wmsCnt)) := WSH_UTIL_VALIDATE.Check_Wms_Org(l_organizationIdTab(wmsCnt));
893 END IF;
894
895 IF ( G_WMS_ENABLED(l_organizationIdTab(wmsCnt)) = 'Y' )
896 THEN
897 l_return_status := NULL;
898 l_msg_count := NULL;
899 l_msg_count := NULL;
900
901 OPEN Get_Grouping_Id;
902 FETCH Get_Grouping_Id INTO l_carton_grouping_id;
903 IF ( Get_Grouping_Id%NOTFOUND )
904 THEN
905 ARP_MESSAGE.Set_Error('Not able to fetch carton_grouping_id');
906 l_return_status := WSH_UTIL_CORE.G_RET_STS_ERROR;
907 CLOSE Get_Grouping_Id;
908 --
909 RAISE Unassign_Del_Exp;
910 --
911 END IF;
912 CLOSE Get_Grouping_Id;
913
917 x_msg_data => l_msg_data,
914 INV_MO_Cancel_PVT.Update_Mol_Carton_Group (
915 x_return_status => l_return_status,
916 x_msg_cnt => l_msg_count,
918 p_line_id => l_moveOrderLineIdTab(wmsCnt),
919 p_carton_grouping_id => l_carton_grouping_id );
920
921 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
922 WSH_UTIL_CORE.G_RET_STS_WARNING) )
923 THEN
924 --
925 IF ( l_debug_on ) THEN
926 ARP_MESSAGE.Set_Error('Error returned from API INV_MO_Cancel_PVT.Update_Mol_Carton_Group');
927 END IF;
928 --
929 RAISE Unassign_Del_Exp;
930 END IF;
931 END IF;
932 -- } Released Status If
933 END IF;
934 END LOOP;
935 -- } WMS If
936 END IF;
937 -- For WMS Enabled org and Release to Warehouse lines
938
939 l_return_status := NULL;
940
941 Adjust_Parent_WV (
942 p_entity_type => 'DEL-CONT',
943 p_delivery_detail => l_deliveryDetailIdTab,
944 p_parent_delivery_detail => l_parentDeliveryDetailIdTab,
945 p_delivery_id => l_deliveryIdTab,
946 p_inventory_item_id => l_inventoryItemIdTab,
947 p_organization_id => l_organizationIdTab,
948 p_weight_uom => l_weightUomTab,
949 p_volume_uom => l_volumeUomTab,
950 x_return_status => l_return_status );
951
952 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
953 WSH_UTIL_CORE.G_RET_STS_WARNING) )
954 THEN
955 --
956 IF ( l_debug_on ) THEN
957 ARP_MESSAGE.Set_Error('Error returned from API Adjust_Parent_WV');
958 END IF;
959 --
960 RAISE Unassign_Del_Exp;
961 END IF;
962
963 IF ( G_FTE_INSTALLED = 'Y' AND
964 l_freightCostRowidTab.COUNT > 0 )
965 THEN
966 FORALL delCnt in l_freightCostRowidTab.FIRST..l_freightCostRowidTab.LAST
967 DELETE FROM Wsh_Freight_Costs
968 WHERE Rowid = l_freightCostRowidTab(delCnt);
969 END IF;
970
971 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
972 THEN
973 l_return_status := NULL;
974
975 Insert_Log_Table (
976 p_id_tab => l_deliveryDetailIdTab,
977 p_table_name => 'WSH_DELIVERY_ASSIGNMENTS',
978 p_req_id => p_req_id,
979 x_return_status => l_return_status );
980
981 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
982 THEN
983 Raise Unassign_Del_Exp;
984 END IF;
985 END IF;
986
987 IF ( l_deliveryIdTab.COUNT > 0 )
988 THEN
989 FORALL delCnt in l_deliveryIdTab.FIRST..l_deliveryIdTab.LAST
990 DELETE FROM wsh_tmp WHERE column1 = l_deliveryIdTab(delCnt);
991 END IF;
992 -- }
993 END IF;
994
995 IF ( l_deliveryIdTab.COUNT > 0 )
996 THEN -- { Log Exception
997 l_message_name := 'WSH_CMRG_UNASSIGN_DELIVERY';
998
999 FOR expCnt in l_deliveryIdTab.FIRST..l_deliveryIdTab.LAST
1000 LOOP
1001 -- { Loop for logging Expceptions
1002 -- Setting the Messages
1003 FND_MESSAGE.Set_Name ('WSH', l_message_name );
1004 FND_MESSAGE.Set_Token ('PS1', G_FROM_CUST_SITE_ID );
1005 FND_MESSAGE.Set_Token ('PS2', G_TO_CUST_SITE_ID );
1006 FND_MESSAGE.Set_Token ('DELIVERY_DETAIL_ID', l_deliveryDetailIdTab(expCnt) );
1007
1008 l_message_text := FND_MESSAGE.Get;
1009
1010 l_return_status := NULL;
1011 l_msg_count := NULL;
1012 l_msg_data := NULL;
1013 l_exception_id := NULL;
1014
1015 WSH_XC_UTIL.Log_Exception
1016 (
1017 p_api_version => 1.0,
1018 x_return_status => l_return_status,
1019 x_msg_count => l_msg_count,
1020 x_msg_data => l_msg_data,
1021 x_exception_id => l_exception_id,
1022 p_exception_location_id => G_TO_LOCATION_ID,
1023 p_logged_at_location_id => G_TO_LOCATION_ID,
1024 p_logging_entity => 'SHIPPER',
1025 p_logging_entity_id => Fnd_Global.user_id,
1026 p_exception_name => 'WSH_CUSTOMER_MERGE_CHANGE',
1027 p_message => l_message_text,
1028 p_severity => 'LOW',
1029 p_manually_logged => 'N',
1033 );
1030 p_delivery_id => l_deliveryIdTab(expCnt),
1031 p_delivery_name => l_deliveryNameTab(expCnt),
1032 p_error_message => l_message_text
1034
1035 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1036 THEN
1037 ARP_MESSAGE.Set_Error('API WSH_XC_UTIL.Log_Exception returned error..');
1038 RAISE Unassign_Del_Exp;
1039 END IF;
1040
1041 -- }
1042 END LOOP;
1043 -- }
1044 END IF;
1045
1046 EXIT WHEN Get_Wsh_Unassign_Details%NOTFOUND;
1047 -- }
1048 END LOOP;
1049
1050 CLOSE Get_Wsh_Unassign_Details;
1051 --
1052 IF l_debug_on THEN
1053 arp_message.set_line('WSH_CUST_MERGE.Unassign_Details_From_Delivery()+' || getTimeStamp );
1054 END IF;
1055 --
1056 EXCEPTION
1057 WHEN Unassign_Del_Exp THEN
1058 x_return_status := l_return_status;
1059 --
1060 IF ( l_debug_on ) THEN
1061 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Unassign_Details_From_Delivery()+ Unassign_Del_Exp - ' || getTimeStamp );
1062 END IF;
1063 --
1064 IF ( Get_Cont_Unassign_Details%ISOPEN ) THEN
1065 CLOSE Get_Cont_Unassign_Details;
1066 END IF;
1067
1068 IF ( Get_Wsh_Unassign_Details%ISOPEN ) THEN
1069 CLOSE Get_Wsh_Unassign_Details;
1070 END IF;
1071
1072 IF ( Get_Grouping_Id%ISOPEN ) THEN
1073 CLOSE Get_Grouping_Id;
1074 END IF;
1075 --
1076 WHEN OTHERS THEN
1077 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1078 --
1079 IF ( l_debug_on ) THEN
1080 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Unassign_Details_From_Delivery()+ Others - ' || getTimeStamp );
1081 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
1082 END IF;
1083 --
1084 IF ( Get_Cont_Unassign_Details%ISOPEN ) THEN
1085 CLOSE Get_Cont_Unassign_Details;
1086 END IF;
1087
1088 IF ( Get_Wsh_Unassign_Details%ISOPEN ) THEN
1089 CLOSE Get_Wsh_Unassign_Details;
1090 END IF;
1091
1092 IF ( Get_Grouping_Id%ISOPEN ) THEN
1093 CLOSE Get_Grouping_Id;
1094 END IF;
1095 --
1096 END Unassign_Details_From_Delivery;
1097
1098 -----------------------------------------------------------------------------------------
1099 --
1100 -- PROCEDURE : GET_DELIVERY_HASH
1101 --
1102 -- DESCRIPTION :
1103 -- Get_Delivery_Hash generates new hash value and hash string for
1104 -- deliveries(from wsh_tmp table) which are to be updated with new
1105 -- Customer/Location ids
1106 --
1107 -- PARAMETERS :
1108 -- x_hash_string_tab => Contains array of Hash String for deliveries
1109 -- x_hash_value_tab => Contains array of Hash String for deliveries
1110 -- x_delivery_id_tab => Contains array of delivery ids
1111 -- x_return_status => Return status of API
1112 -----------------------------------------------------------------------------------------
1113
1114 PROCEDURE Get_Delivery_Hash (
1115 x_hash_string_tab OUT NOCOPY g_char_hash_string,
1116 x_hash_value_tab OUT NOCOPY g_number_tbl_type,
1117 x_delivery_id_tab OUT NOCOPY g_number_tbl_type,
1118 x_return_status OUT NOCOPY VARCHAR2 )
1119 IS
1120 CURSOR Get_Tmp_Deliveries
1121 IS
1122 SELECT to_number(Column1) delivery_id, Column3
1123 FROM Wsh_Tmp;
1124
1125 l_grp_attr_tab_type WSH_DELIVERY_AUTOCREATE.grp_attr_tab_type;
1126 l_action_code VARCHAR2(30);
1127 l_return_status VARCHAR2(1);
1128 l_hash_count NUMBER;
1129
1130 Update_Hash_Exp EXCEPTION;
1131
1132 --
1133 l_debug_on BOOLEAN;
1134 --
1135 BEGIN
1136 --
1137 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1138 --
1139 IF l_debug_on IS NULL
1140 THEN
1141 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1142 END IF;
1143 --
1144 IF l_debug_on THEN
1145 arp_message.set_line('WSH_CUST_MERGE.Get_Delivery_Hash()+' || getTimeStamp );
1146 END IF;
1147 --
1148
1149 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1150 l_hash_count := 0;
1151
1152 FOR i IN Get_Tmp_Deliveries
1153 LOOP
1154 -- {
1155 IF ( NOT G_DELIVERY_ID.EXISTS(i.delivery_id) )
1156 THEN
1157 -- {
1158 l_grp_attr_tab_type(1).Entity_Type := 'DELIVERY_DETAIL';
1159 l_grp_attr_tab_type(1).Entity_Id := i.Column3;
1160
1161 WSH_DELIVERY_AUTOCREATE.Create_Hash (
1162 p_grouping_attributes => l_grp_attr_tab_type,
1163 p_group_by_header => 'N',
1164 p_action_code => l_action_code,
1165 x_return_status => l_return_status );
1166
1167 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
1168 WSH_UTIL_CORE.G_RET_STS_WARNING) )
1169 THEN
1170 --
1171 IF ( l_debug_on ) THEN
1172 ARP_MESSAGE.Set_Error('Error returned from API Create_Hash');
1173 END IF;
1174 --
1178 l_hash_count := l_hash_count + 1;
1175 RAISE Update_Hash_Exp;
1176 END IF;
1177
1179 x_hash_string_tab(l_hash_count) := l_grp_attr_tab_type(1).l1_hash_string;
1180 x_hash_value_tab(l_hash_count) := l_grp_attr_tab_type(1).l1_hash_value;
1181 x_delivery_id_tab(l_hash_count) := i.delivery_id;
1182 G_DELIVERY_ID(i.delivery_id) := i.delivery_id;
1183 -- }
1184 END IF;
1185 -- }
1186 END LOOP;
1187
1188 --
1189 IF l_debug_on THEN
1190 arp_message.set_line('WSH_CUST_MERGE.Get_Delivery_Hash()+' || getTimeStamp );
1191 END IF;
1192 --
1193 EXCEPTION
1194 WHEN Update_Hash_Exp THEN
1195 x_return_status := l_return_status;
1196 --
1197 IF ( l_debug_on ) THEN
1198 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Get_Delivery_Hash()+ Update_Hash_Exp - ' || getTimeStamp );
1199 END IF;
1200 --
1201 WHEN OTHERS THEN
1202 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
1203 --
1204 IF ( l_debug_on ) THEN
1205 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Get_Delivery_Hash()+ Others - ' || getTimeStamp );
1206 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
1207 END IF;
1208 --
1209 END Get_Delivery_Hash;
1210 --
1211 --
1212 --
1213 --
1214 PROCEDURE Process_Deliveries (
1215 p_req_id IN NUMBER,
1216 x_return_status OUT NOCOPY VARCHAR2 )
1217 IS
1218 -- Cursor to fetch Container record details for delivery lines
1219 -- which are assigned to delivery
1220 CURSOR Get_Delivery_Containers
1221 IS
1222 SELECT Wdd.Delivery_Detail_Id, Wdd.Rowid
1223 FROM Wsh_Delivery_Details Wdd,
1224 Wsh_Delivery_Assignments Wda,
1225 Wsh_Tmp Tmp
1226 WHERE Wdd.Container_Flag = 'Y'
1227 AND Wdd.Delivery_Detail_Id = Wda.Parent_Delivery_Detail_Id
1228 AND Parent_Delivery_Detail_Id IS NOT NULL
1229 AND Wda.Delivery_Id = Tmp.Column1
1230 FOR UPDATE OF Wda.Delivery_Detail_Id NOWAIT;
1231
1232 -- Cursor to fetch deliveries which are to be Unassigned from Trip Stops
1233 CURSOR Get_Del_Unassign_From_Stop ( p_location_id NUMBER )
1234 IS
1235 SELECT Wdl.Delivery_Id, Wdl.Drop_Off_Stop_Id, Wts.Trip_Id,
1236 Wdl.Delivery_Leg_Id, Wnd.Gross_Weight, Wnd.Net_Weight,
1237 Wnd.Volume, Wdl.Rowid, Tmp.Rowid,
1238 NVL(Wnd.ignore_for_planning, 'N') --OTM R12
1239 FROM Wsh_New_Deliveries Wnd,
1240 Wsh_Delivery_Legs Wdl,
1241 Wsh_Trip_Stops Wts,
1242 Wsh_Tmp Tmp
1243 WHERE Wnd.Ultimate_Dropoff_Location_Id = p_location_id
1244 AND Wts.Stop_Id = Wdl.Drop_Off_Stop_Id
1245 AND Wnd.Delivery_Id = Wdl.Delivery_Id
1246 AND Wdl.Delivery_Id = Tmp.Column1
1247 AND exists (
1248 SELECT 'x'
1249 FROM Wsh_New_Deliveries Del,
1250 Wsh_Delivery_Legs Legs
1251 WHERE Del.Ultimate_Dropoff_Location_Id <> p_location_id
1252 AND Del.Delivery_Id = Legs.Delivery_Id
1253 AND Legs.Drop_Off_Stop_Id = Wdl.Drop_Off_Stop_Id
1254 )
1255 FOR UPDATE OF Wdl.Delivery_Leg_Id NOWAIT;
1256
1257 -- OTM R12 : customer merge
1258 -- getting deliveries on the trip where another delivery is unassigned
1259 -- these deliveries will be set to AW
1260 CURSOR c_get_deliveries (p_trip_id IN NUMBER,p_exclude_dlvy IN NUMBER) IS
1261 SELECT wdl.delivery_id
1262 FROM wsh_delivery_legs wdl,
1263 wsh_trip_stops wts,
1264 wsh_new_deliveries wnd
1265 WHERE wdl.pick_up_stop_id = wts.stop_id
1266 AND wts.trip_id = p_trip_id
1267 AND wdl.delivery_id = wnd.delivery_id
1268 AND wnd.status_code = 'OP'
1269 AND wnd.tms_interface_flag = WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED
1270 AND wnd.delivery_id <> p_exclude_dlvy;
1271
1272 -- getting empty deliveries that belongs to the customer and matches the drop
1273 -- off location id
1274 CURSOR c_get_empty_deliveries(p_customer_id IN NUMBER, p_dropoff_location_id IN NUMBER) IS
1275 SELECT wnd.delivery_id
1276 FROM wsh_new_deliveries wnd
1277 WHERE NVL(wnd.Customer_Id, p_customer_id) = p_customer_id
1278 AND wnd.ultimate_dropoff_location_Id = p_dropoff_location_id
1279 AND wnd.status_code = 'OP'
1280 AND NOT EXISTS
1281 (
1282 SELECT 1
1283 FROM wsh_delivery_assignments wda,
1284 wsh_delivery_details wdd
1285 WHERE wda.delivery_id = wnd.delivery_id
1286 AND wda.delivery_detail_id = wdd.delivery_detail_id
1287 AND wdd.container_flag = 'N'
1288 );
1289
1290 -- getting freight cost type id for the OTM freight cost
1291 CURSOR c_get_frcost_type_id IS
1292 SELECT freight_cost_type_id
1293 FROM wsh_freight_cost_types
1294 WHERE name = 'OTM Freight Cost'
1295 AND freight_cost_type_code = 'FREIGHT';
1296
1297 l_trip_id NUMBER;
1298 l_trip_status WSH_TRIPS.STATUS_CODE%TYPE;
1299 l_aw_dlvy_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1300 l_aw_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1301 l_frcost_type_id NUMBER;
1302
1303 l_skip VARCHAR2(1);
1307 frcost_not_found EXCEPTION;
1304 l_dlvy_id_tab WSH_UTIL_CORE.ID_TAB_TYPE;
1305 l_gc3_is_installed VARCHAR2(1);
1306
1308 l_tms_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1309 l_tms_trip_tab WSH_DELIVERY_VALIDATIONS.trip_info_tab_type;
1310 l_new_delivery_leg_tab g_rowid_tbl_type;
1311 l_delivery_leg_count NUMBER;
1312 l_ignoreTab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1313 l_tms_count NUMBER;
1314 l_tms_delivery_id_tab g_number_tbl_type;
1315
1316 -- End of OTM R12 : customer merge
1317
1318 -- OTM R12 : update delivery
1319 l_delivery_info_tab WSH_NEW_DELIVERIES_PVT.Delivery_Attr_Tbl_Type;
1320 l_delivery_info WSH_NEW_DELIVERIES_PVT.Delivery_Rec_Type;
1321 l_new_interface_flag_tab WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1322 l_tms_update WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1323 l_trip_not_found VARCHAR2(1);
1324 l_trip_info_rec WSH_DELIVERY_VALIDATIONS.trip_info_rec_type;
1325 l_tms_version_number g_number_tbl_type;
1326 l_tms_interface_flag WSH_UTIL_CORE.COLUMN_TAB_TYPE;
1327 l_index NUMBER;
1328 l_delivery_count NUMBER;
1329 -- End of OTM R12 : update delivery
1330
1331 l_deliveryDetailIdTab g_number_tbl_type;
1332 l_deliveryLegIdTab g_number_tbl_type;
1333 l_deliveryIdTab g_number_tbl_type;
1334 l_stopIdTab g_number_tbl_type;
1335 l_tripIdTab g_number_tbl_type;
1336 l_grossWeightTab g_number_tbl_type;
1337 l_netWeightTab g_number_tbl_type;
1338 l_volumeTab g_number_tbl_type;
1339 l_dummyIdTab g_number_tbl_type;
1340 l_hash_value_tab g_number_tbl_type;
1341 l_delivery_id_tab g_number_tbl_type;
1342
1343 l_hash_string_tab g_char_hash_string;
1344
1345 l_deliveryDetailRowidTab g_rowid_tbl_type;
1346 l_deliveryRowidTab g_rowid_tbl_type;
1347 l_legsRowidTab g_rowid_tbl_type;
1348 l_tempRowidTab g_rowid_tbl_type;
1349
1350 l_exception_id NUMBER;
1351 l_msg_count NUMBER;
1352 l_msg_data VARCHAR2(32767);
1353 l_return_status VARCHAR2(10);
1354 l_message_text VARCHAR2(32767);
1355 l_message_name VARCHAR2(50);
1356
1357 Update_Del_Exp EXCEPTION;
1358 --
1359 l_debug_on BOOLEAN;
1360 --
1361 BEGIN
1362 --
1363 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
1364 --
1365 IF l_debug_on IS NULL
1366 THEN
1367 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
1368 END IF;
1369 --
1370 IF l_debug_on THEN
1371 arp_message.set_line('WSH_CUST_MERGE.Process_Deliveries()+' || getTimeStamp );
1372 END IF;
1373 --
1374
1375 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
1376
1377 -- OTM R12
1378 l_gc3_is_installed := WSH_UTIL_CORE.G_GC3_IS_INSTALLED;
1379
1380 IF (l_gc3_is_installed IS NULL) THEN
1381 l_gc3_is_installed := WSH_UTIL_CORE.GC3_IS_INSTALLED;
1382 END IF;
1383 -- End of OTM R12
1384
1385 OPEN Get_Delivery_Containers;
1386
1387 LOOP
1388 -- { to update container records
1389 FETCH Get_Delivery_Containers
1390 BULK COLLECT INTO l_deliveryDetailIdTab, l_deliveryDetailRowidTab
1391 LIMIT G_BATCH_LIMIT;
1392
1393 IF ( l_deliveryDetailRowidTab.COUNT > 0 )
1394 THEN
1395 -- { count > 0
1396 -- For container records ship_to_site_use_id and deliver_to_site_use_id
1397 -- is null
1398 FORALL updCnt IN l_deliveryDetailRowidTab.FIRST..l_deliveryDetailRowidTab.LAST
1399 UPDATE Wsh_Delivery_Details Wdd
1400 SET customer_id = decode( customer_id,
1401 G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
1402 customer_id ),
1403 ship_to_location_id = decode( ship_to_location_id,
1404 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
1405 ship_to_location_id ),
1406 deliver_to_location_id = decode( deliver_to_location_id,
1407 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
1408 deliver_to_location_id ),
1409 last_update_date = SYSDATE,
1410 last_updated_by = arp_standard.profile.user_id,
1411 last_update_login = arp_standard.profile.last_update_login,
1412 request_id = p_req_id,
1413 program_application_id = arp_standard.profile.program_application_id ,
1414 program_id = arp_standard.profile.program_id,
1415 program_update_date = SYSDATE
1416 WHERE Wdd.Rowid = l_deliveryDetailRowidTab(updCnt);
1417
1418 --
1419 setARMessageRowCount( 'WSH_DELIVERY_DETAILS', SQL%ROWCOUNT );
1420
1421 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
1422 THEN
1423 l_return_status := NULL;
1424
1425 Insert_Log_Table (
1429 x_return_status => l_return_status );
1426 p_id_tab => l_deliveryDetailIdTab,
1427 p_table_name => 'WSH_DELIVERY_DETAILS',
1428 p_req_id => p_req_id,
1430
1431 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1432 THEN
1433 Raise Update_Del_Exp;
1434 END IF;
1435 END IF;
1436 -- } Count > 0
1437 END IF;
1438
1439 EXIT WHEN Get_Delivery_Containers%NOTFOUND;
1440 -- } to update container records
1441 END LOOP;
1442
1443 CLOSE Get_Delivery_Containers;
1444
1445 l_hash_value_tab.DELETE;
1446 l_hash_string_tab.DELETE;
1447 l_delivery_id_tab.DELETE;
1448 l_return_status := NULL;
1449
1450 Get_Delivery_Hash (
1451 x_hash_string_tab => l_hash_string_tab,
1452 x_hash_value_tab => l_hash_value_tab,
1453 x_delivery_id_tab => l_delivery_id_tab,
1454 x_return_status => l_return_status );
1455
1456 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1457 THEN
1458 Raise Update_Del_Exp;
1459 END IF;
1460
1461 IF ( l_hash_value_tab.COUNT > 0 )
1462 THEN
1463 -- { Hash Value Count > 0
1464
1465 -- OTM R12 : update delivery
1466 -- initialize l_tms_update table with 'N'
1467 -- l_tms_update is used in the next update SQL regardless of
1468 -- GC3_INSTALLED status, so need to initialize it
1469
1470 -- following tables to be used by the update statement
1471 -- l_new_interface_flag_tab can't be used since it doesn't have
1472 -- entries for all the deliveries, but only those that will be passed
1473 -- to wsh_xc_util.log_otm_exception
1474
1475 l_index := l_hash_value_tab.FIRST;
1476 WHILE (l_index IS NOT NULL) LOOP
1477 l_tms_update(l_index) := 'N';
1478 l_tms_interface_flag(l_index) := NULL;
1479 l_tms_version_number(l_index) := NULL;
1480 l_index := l_hash_value_tab.NEXT(l_index);
1481 END LOOP;
1482
1483 IF (l_gc3_is_installed = 'Y') THEN
1484
1485 -- not to call l_delivery_info_tab.count repeatedly, performance
1486 l_delivery_count := 0;
1487 l_tms_count := 0;
1488
1489 -- for loop to populate
1490 l_index := l_hash_value_tab.FIRST;
1491 WHILE (l_index IS NOT NULL) LOOP
1492 l_trip_not_found := 'N';
1493
1494 WSH_DELIVERY_VALIDATIONS.get_delivery_information(
1495 p_delivery_id => l_delivery_id_tab(l_index),
1496 x_delivery_rec => l_delivery_info,
1497 x_return_status => l_return_status);
1498
1499 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1500 x_return_status := l_return_status;
1501 RETURN;
1502 END IF;
1503
1504 IF (nvl(l_delivery_info.ignore_for_planning, 'N') = 'N') THEN
1505
1506 --get trip information for delivery, no update when trip not OPEN
1507 WSH_DELIVERY_VALIDATIONS.get_trip_information
1508 (p_delivery_id => l_delivery_id_tab(l_index),
1509 x_trip_info_rec => l_trip_info_rec,
1510 x_return_status => l_return_status);
1511
1512 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1513 x_return_status := l_return_status;
1514 RETURN;
1515 END IF;
1516
1517 IF l_debug_on THEN
1518 WSH_DEBUG_SV.log('my_module','l_delivery_id-'||l_delivery_info.delivery_id
1519 ||' l_dropoff_location-'||l_delivery_info.ultimate_dropoff_location_id
1520 ||' l_tms_interface_flag-'||l_delivery_info.tms_interface_flag
1521 ||' l_tms_version_number-'||l_delivery_info.tms_version_number
1522 ||' g_from_location-'||G_FROM_LOCATION_ID
1523 ||' g_to_location-'||G_TO_LOCATION_ID);
1524 END IF;
1525
1526 --if trip exist, save the information for later delivery unassignment
1527 IF (l_trip_info_rec.trip_id IS NOT NULL
1528 AND NVL(l_delivery_info.ultimate_dropoff_location_id, -1) = NVL(G_FROM_LOCATION_ID, -1)
1529 AND NVL(l_delivery_info.ultimate_dropoff_location_id, -1) <> NVL(G_TO_LOCATION_ID, -1)) THEN
1530 l_tms_count := l_tms_count + 1;
1531 l_tms_delivery_info_tab(l_tms_count) := l_delivery_info;
1532 l_tms_trip_tab(l_tms_count) := l_trip_info_rec;
1533 END IF;
1534
1535 IF l_debug_on THEN
1536 WSH_DEBUG_SV.log('my_module','tms_count', l_tms_count);
1537 END IF;
1538
1539 IF (l_trip_info_rec.trip_id IS NULL) THEN
1540 l_trip_not_found := 'Y';
1541 END IF;
1542
1543 -- only do changes when there's no trip or trip status is OPEN
1544 IF (l_trip_info_rec.status_code = 'OP' OR
1545 l_trip_not_found = 'Y') THEN
1546
1547 -- checking for changes in the dropoff location id, update only
1551 IF (nvl(l_delivery_info.ultimate_dropoff_location_id, -1)
1548 -- if dropoff location id is equal to G_FROM_LOCATION_ID
1549 -- and not equal to G_TO_LOCATION_ID
1550
1552 = nvl(G_FROM_LOCATION_ID, -1) AND
1553 nvl(l_delivery_info.ultimate_dropoff_location_id, -1)
1554 <> nvl(G_TO_LOCATION_ID, -1)) THEN
1555 IF (l_delivery_info.tms_interface_flag NOT IN
1556 (WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT,
1557 WSH_NEW_DELIVERIES_PVT.C_TMS_CREATE_REQUIRED,
1558 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
1559 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS,
1560 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED)) THEN
1561 l_tms_update(l_index) := 'Y';
1562 l_delivery_count := l_delivery_count + 1;
1563 l_delivery_info_tab(l_delivery_count) := l_delivery_info;
1564 l_new_interface_flag_tab(l_delivery_count) := WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED;
1565 l_tms_version_number(l_index) := nvl(l_delivery_info.tms_version_number, 1) + 1;
1566 l_tms_interface_flag(l_index) := l_new_interface_flag_tab(l_delivery_count);
1567 END IF;
1568 END IF; -- checking the value differences
1569 END IF; -- IF ((l_trip_not_found = 'N' AND
1570 END IF; -- if ignore_for_planning
1571
1572 IF l_debug_on THEN
1573 WSH_DEBUG_SV.log('my_module','l_delivery_id-'||l_delivery_info.delivery_id
1574 ||' 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 arp_message.set_line('l_tms_update-'||l_tms_update(l_index)
1578 ||' l_tms_interface_flag-'||l_tms_interface_flag(l_index)
1579 ||' l_tms_version_number-'||l_tms_version_number(l_index));
1580 END IF;
1581 l_index := l_hash_value_tab.NEXT(l_index);
1582 END LOOP;
1583 END IF; -- if GC3 is installed
1584 -- End of OTM R12 : update delivery
1585
1586 FORALL updCnt IN l_hash_value_tab.FIRST..l_hash_value_tab.LAST
1587 UPDATE WSH_NEW_DELIVERIES Wnd
1588 SET Hash_Value = nvl(l_hash_value_tab(updCnt), Hash_Value),
1589 Hash_String = nvl(l_hash_string_tab(updCnt), Hash_String),
1590 customer_id = decode(customer_id,
1591 G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
1592 customer_id),
1593 ultimate_dropoff_location_id = decode(ultimate_dropoff_location_id,
1594 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
1595 ultimate_dropoff_location_id ),
1596 last_update_date = SYSDATE,
1597 last_updated_by = arp_standard.profile.user_id,
1598 last_update_login = arp_standard.profile.last_update_login,
1599 request_id = p_req_id,
1600 program_application_id = arp_standard.profile.program_application_id ,
1601 program_id = arp_standard.profile.program_id,
1602 program_update_date = SYSDATE,
1603 -- OTM R12 : update delivery
1604 TMS_INTERFACE_FLAG = decode(l_tms_update(updCnt), 'Y',
1605 l_tms_interface_flag(updCnt),
1606 nvl(TMS_INTERFACE_FLAG, WSH_NEW_DELIVERIES_PVT.C_TMS_NOT_TO_BE_SENT)),
1607 TMS_VERSION_NUMBER = decode(l_tms_update(updCnt), 'Y',
1608 l_tms_version_number(updCnt),
1609 nvl(TMS_VERSION_NUMBER, 1))
1610 -- End of OTM R12 : update delivery
1611 WHERE Delivery_Id = l_delivery_id_tab(updCnt)
1612 RETURNING Wnd.Delivery_Id BULK COLLECT INTO l_deliveryIdTab;
1613
1614 -- OTM R12 : update delivery
1615 IF (l_gc3_is_installed = 'Y' AND l_delivery_count > 0) THEN
1616 WSH_XC_UTIL.LOG_OTM_EXCEPTION(
1617 p_delivery_info_tab => l_delivery_info_tab,
1618 p_new_interface_flag_tab => l_new_interface_flag_tab,
1619 x_return_status => l_return_status);
1620
1621 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1622 x_return_status := l_return_status;
1623 RETURN;
1624 END IF;
1625 END IF;
1626 -- End of OTM R12 : update delivery
1627
1628 --
1629 setARMessageRowCount( 'WSH_NEW_DELIVERIES', SQL%ROWCOUNT );
1630
1631 IF l_debug_on THEN
1632 WSH_DEBUG_SV.log('my module', 'rows updated', l_deliveryIdTab.COUNT);
1633 WSH_DEBUG_SV.log('my module', 'rows suppose to update', l_hash_value_tab.COUNT);
1634 END IF;
1635
1636 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
1637 THEN
1638 -- { Profile Value
1639 l_return_status := NULL;
1640
1641 Insert_Log_Table (
1642 p_id_tab => l_deliveryIdTab,
1643 p_table_name => 'WSH_NEW_DELIVERIES',
1644 p_req_id => p_req_id,
1648 THEN
1645 x_return_status => l_return_status );
1646
1647 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
1649 Raise Update_Del_Exp;
1650 END IF;
1651 -- } Profile Value
1652 END IF;
1653 -- } Hash Value Count > 0
1654 END IF;
1655
1656 OPEN Get_Del_Unassign_From_Stop ( G_TO_LOCATION_ID );
1657
1658 LOOP
1659 -- {
1660 FETCH Get_Del_Unassign_From_Stop
1661 BULK COLLECT INTO l_deliveryIdTab,
1662 l_stopIdTab,
1663 l_tripIdTab,
1664 l_deliveryLegIdTab,
1665 l_grossWeightTab,
1666 l_netWeightTab,
1667 l_volumeTab,
1668 l_legsRowidTab,
1669 l_tempRowidTab,
1670 l_ignoreTab --OTM R12
1671 LIMIT G_BATCH_LIMIT;
1672
1673 l_return_status := NULL;
1674
1675 IF ( l_legsRowidTab.COUNT > 0 )
1676 THEN
1677
1678 --This adjusts the trip stop weight volume. For all the deliveries
1679 --that are to be unassigned, it's okay to adjust them here.
1680 --OTM project will only unassign more deliveries.
1681 --It is also okay to not adjust the weight/vol for some OTM trips
1682 --since those will be synced up with OTM later
1683 Adjust_Weight_Volume (
1684 p_entity_type => 'TRIP-STOP',
1685 p_delivery_detail => l_dummyIdTab,
1686 p_parent_delivery_detail => l_dummyIdTab,
1687 p_delivery_id => l_deliveryIdTab,
1688 p_delivery_leg_id => l_deliveryLegIdTab,
1689 p_net_weight => l_netWeightTab,
1690 p_gross_weight => l_grossWeightTab,
1691 p_volume => l_volumeTab,
1692 x_return_status => l_return_status );
1693
1694 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
1695 WSH_UTIL_CORE.G_RET_STS_WARNING) )
1696 THEN
1697 --
1698 IF ( l_debug_on ) THEN
1699 ARP_MESSAGE.Set_Error('Error returned from API Adjust_Weight_Volume');
1700 END IF;
1701 --
1702 RAISE Update_Del_Exp;
1703 END IF;
1704
1705 --OTM R12
1706 --generating a list of the ignore for planning deliveries from
1707 --the cursor selected list because when OTM is installed, we should only
1708 --be dealing with ignore for planning deliveries in this LOOP, include
1709 --for planning deliveries will be done after this
1710 l_index := l_ignoreTab.FIRST;
1711 l_delivery_leg_count := 0;
1712
1713 IF (l_gc3_is_installed = 'N') THEN
1714 l_delivery_leg_count := l_legsRowidTab.COUNT;
1715 l_new_delivery_leg_tab := l_legsRowidTab;
1716 ELSE
1717 --
1718 WHILE (l_index IS NOT NULL) LOOP
1719
1720 IF (l_ignoreTab(l_index) = 'Y' ) THEN
1721 l_delivery_leg_count := l_delivery_leg_count + 1;
1722 l_new_delivery_leg_tab(l_delivery_leg_count) := l_legsRowidTab(l_index);
1723 END IF;
1724 l_index := l_ignoreTab.NEXT(l_index);
1725 END LOOP;
1726
1727 END IF;
1728
1729 IF (l_delivery_leg_count > 0 AND l_new_delivery_leg_tab.count > 0) THEN
1730 FORALL delCnt IN l_new_delivery_leg_tab.FIRST..l_new_delivery_leg_tab.LAST
1731 DELETE FROM Wsh_Delivery_Legs
1732 WHERE Rowid = l_new_delivery_leg_tab(delCnt);
1733 END IF;
1734 --END OTM R12
1735
1736 IF ( G_FTE_INSTALLED = 'Y' AND l_deliveryLegIdTab.COUNT > 0)
1737 THEN
1738 FORALL delCnt IN l_deliveryLegIdTab.FIRST..l_deliveryLegIdTab.LAST
1739 DELETE FROM Wsh_Freight_Costs
1740 WHERE Delivery_Leg_Id = l_deliveryLegIdTab(delCnt);
1741 END IF;
1742 END IF;
1743
1744 --For OTM related deliveries, all the deliveries will be removed from wsh_tmp,
1745 --so removing them here is okay since OTM flow will only unassign more deliveries than
1746 --the original flow.
1747 IF ( l_tempRowidTab.COUNT > 0 )
1748 THEN
1749 -- Deletes records from Wsh_Tmp table, so that locations are not
1750 -- updated for stops which has deliveries with different dropoff
1751 -- locations.
1752 FORALL delCnt IN l_tempRowidTab.FIRST..l_tempRowidTab.LAST
1753 DELETE FROM Wsh_Tmp
1754 WHERE Rowid = l_tempRowidTab(delCnt);
1755 END IF;
1756
1757 EXIT WHEN Get_Del_Unassign_From_Stop%NOTFOUND;
1758 -- }
1759 END LOOP;
1760
1761 CLOSE Get_Del_Unassign_From_Stop;
1762
1763 -- OTM R12 : customer merge
1764 -- When flow reaches here, the delivery is already assigned to a trip
1765 -- Check if it is an OTM trip + Trip (and included deliveries) is open
1766 -- then unassign the delivery and mark it UR, update the tms_version
1767 -- number also(unassigning should set delivery to UR and not DR+Ignore)
1768 -- Case 1: Other deliveries on this OTM trip should be marked AW,
1769 -- if they are not UR (if they are UR, leave them as UR)
1770 -- Case 2: If the Customer merge causes 2 include for planning
1774 -- 2nd delivery is selected for update, then it becomes UR from AW,
1771 -- deliveries to be selected which are on the same OTM trip
1772 -- --> when the first delivery is processed, it will mark the second
1773 -- one as AW and itself becomes UR. Continuing with the process,
1775 -- the first delivery which was marked UR earlier shouldn't get set
1776 -- to AW here
1777 -- Case 3: Due to customer merge, 2 deliveries are selected,
1778 -- out of which 1 is ignore for planning and other is
1779 -- include for planning. So for Ignore for planning cases,
1780 -- keep the current behavior and for include for planning, use Case#1.
1781
1782 IF l_gc3_is_installed = 'Y' THEN
1783 -- need to go through empty deliveries also to make sure all empty deliveries on
1784 -- OTM trip is being unassigned
1785 OPEN c_get_empty_deliveries(G_FROM_CUSTOMER_ID, G_FROM_LOCATION_ID );
1786 FETCH c_get_empty_deliveries BULK COLLECT INTO l_tms_delivery_id_tab;
1787 CLOSE c_get_empty_deliveries;
1788
1789 --reinitialize the count to the table count because this part of
1790 --the code could be triggered in another flow without the previous count
1791 --initilization.
1792 l_tms_count := l_tms_delivery_info_tab.COUNT;
1793
1794 IF l_debug_on THEN
1795 WSH_DEBUG_SV.log('my_module','tms_delivery_id_count', l_tms_delivery_id_tab.count);
1796 END IF;
1797
1798 -- for loop to populate
1799 l_index := l_tms_delivery_id_tab.FIRST;
1800 WHILE (l_index IS NOT NULL) LOOP
1801
1802 WSH_DELIVERY_VALIDATIONS.get_delivery_information(
1803 p_delivery_id => l_tms_delivery_id_tab(l_index),
1804 x_delivery_rec => l_delivery_info,
1805 x_return_status => l_return_status);
1806
1807 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1808 x_return_status := l_return_status;
1809 RETURN;
1810 END IF;
1811
1812 IF (nvl(l_delivery_info.ignore_for_planning, 'N') = 'N') THEN
1813
1814 --get trip information for delivery, no update when trip not OPEN
1815 WSH_DELIVERY_VALIDATIONS.get_trip_information
1816 (p_delivery_id => l_tms_delivery_id_tab(l_index),
1817 x_trip_info_rec => l_trip_info_rec,
1818 x_return_status => l_return_status);
1819
1820 IF (l_return_status IN (WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR, WSH_UTIL_CORE.G_RET_STS_ERROR)) THEN
1821 x_return_status := l_return_status;
1822 RETURN;
1823 END IF;
1824
1825 IF l_debug_on THEN
1826 WSH_DEBUG_SV.log('my_module','l_delivery_id-'||l_delivery_info.delivery_id
1827 ||' l_dropoff_location-'||l_delivery_info.ultimate_dropoff_location_id
1828 ||' l_tms_interface_flag-'||l_delivery_info.tms_interface_flag
1829 ||' l_tms_version_number-'||l_delivery_info.tms_version_number
1830 ||' g_from_location-'||G_FROM_LOCATION_ID||' g_to_location-'||G_TO_LOCATION_ID);
1831
1832 WSH_DEBUG_SV.log('my_module','tms_count', l_tms_count);
1833 END IF;
1834
1835 --if trip exist, save the information for later delivery unassign
1836 IF (l_trip_info_rec.trip_id IS NOT NULL
1837 AND NVL(l_delivery_info.ultimate_dropoff_location_id, -1) = NVL(G_FROM_LOCATION_ID, -1)
1838 AND NVL(l_delivery_info.ultimate_dropoff_location_id, -1) <> NVL(G_TO_LOCATION_ID, -1)) THEN
1839 l_tms_count := l_tms_count + 1;
1840 l_tms_delivery_info_tab(l_tms_count) := l_delivery_info;
1841 l_tms_trip_tab(l_tms_count) := l_trip_info_rec;
1842 END IF;
1843
1844 IF l_debug_on THEN
1845 WSH_DEBUG_SV.log('my_module','tms_count', l_tms_count);
1846 END IF;
1847
1848 END IF;
1849 l_index := l_tms_delivery_id_tab.NEXT(l_index);
1850 END LOOP;
1851
1852 IF l_debug_on THEN
1853 WSH_DEBUG_SV.log('my_module','AFTER LOOP tms_count', l_tms_count);
1854 END IF;
1855
1856 --for all the deliveries saved, need to unassign from trip and delete the freight cost
1857 IF (l_tms_count > 0) THEN
1858 l_index := l_tms_delivery_info_tab.FIRST;
1859 WHILE (l_index IS NOT NULL) LOOP
1860
1861 IF l_debug_on THEN
1862 WSH_DEBUG_SV.log('my_module', l_index || 'delivery id-'||l_tms_delivery_info_tab(l_index).delivery_id
1863 ||' ignore for planning-'||l_tms_delivery_info_tab(l_index).ignore_for_planning
1864 ||' tms flag-'||l_tms_delivery_info_tab(l_index).tms_interface_flag
1865 ||' delivery status-'||l_tms_delivery_info_tab(l_index).status_code
1866 ||' trip_status-'||l_tms_trip_tab(l_index).status_code);
1867 arp_message.set_line(l_index || 'delivery id-'||l_tms_delivery_info_tab(l_index).delivery_id
1868 ||' ignore for planning-'||l_tms_delivery_info_tab(l_index).ignore_for_planning
1869 ||' tms flag-'||l_tms_delivery_info_tab(l_index).tms_interface_flag
1870 ||' delivery status-'||l_tms_delivery_info_tab(l_index).status_code
1871 ||' trip_status-'||l_tms_trip_tab(l_index).status_code);
1872 END IF;
1873
1877 IF (l_tms_delivery_info_tab(l_index).ignore_for_planning = 'N' AND
1874 -- Include UR, as delivery got updated above to UR
1875 -- Include DR to count empty deliveries that are assigned
1876 -- to trip, they should be unassigned + the drop off location should be updated
1878 l_tms_delivery_info_tab(l_index).tms_interface_flag IN
1879 (WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER,
1880 WSH_NEW_DELIVERIES_PVT.C_TMS_ANSWER_RECEIVED,
1881 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_REQUIRED,
1882 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_REQUIRED,
1883 WSH_NEW_DELIVERIES_PVT.C_TMS_UPDATE_IN_PROCESS,
1884 WSH_NEW_DELIVERIES_PVT.C_TMS_DELETE_IN_PROCESS) AND
1885 l_tms_delivery_info_tab(l_index).status_code = 'OP' AND
1886 l_tms_trip_tab(l_index).status_code = 'OP') THEN
1887
1888 -- it is already assigned to a trip,
1889 -- there will be only one OTM trip for delivery
1890 l_dlvy_id_tab(l_dlvy_id_tab.COUNT+1) := l_tms_delivery_info_tab(l_index).delivery_id;
1891
1892 -- these deliveries would be set to UR already
1893 -- by the above update statement.
1894 -- Update the other deliveries in the trip, which are
1895 -- not part of the above selection to AW.
1896 -- Also log appropriate exception(within the API called)
1897 -- Need to update the version number also
1898 -- If trip is open, find other deliveries associated
1899 -- with the trip
1900
1901 FOR rec in c_get_deliveries(l_tms_trip_tab(l_index).trip_id, l_tms_delivery_info_tab(l_index).delivery_id) LOOP
1902 l_aw_dlvy_tab(l_aw_dlvy_tab.count + 1) := rec.delivery_id;
1903 l_aw_interface_flag_tab(l_aw_interface_flag_tab.count + 1) := WSH_NEW_DELIVERIES_PVT.C_TMS_AWAITING_ANSWER;
1904 END LOOP;
1905
1906 IF l_debug_on THEN
1907 WSH_DEBUG_SV.LOG('my module', 'unassign delivery count-'||l_dlvy_id_tab.COUNT||' aw delivery count-'||l_aw_dlvy_tab.COUNT);
1908 END IF;
1909
1910 END IF;
1911 l_index := l_tms_delivery_info_tab.NEXT(l_index);
1912 END LOOP; --end of finding include for planning deliveries
1913 END IF;
1914
1915 IF l_debug_on THEN
1916 WSH_DEBUG_SV.LOG('my module', 'unassign delivery count-'||l_dlvy_id_tab.COUNT||' aw delivery count-'||l_aw_dlvy_tab.COUNT);
1917 arp_message.set_line('unassign delivery count-'||l_dlvy_id_tab.COUNT||' aw delivery count-'||l_aw_dlvy_tab.COUNT);
1918 END IF;
1919
1920 -- Update the Interface Flag to AW for selected deliveries
1921 -- Log appropriate exceptions(within the API)
1922 -- Need to update the version number also
1923 IF l_aw_dlvy_tab.count > 0 THEN
1924 -- Call Update after the above LOOP
1925 WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG(
1926 p_delivery_id_tab => l_aw_dlvy_tab,
1927 p_tms_interface_flag_tab => l_aw_interface_flag_tab,
1928 x_return_status => l_return_status);
1929 IF (l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS) THEN
1930 IF l_debug_on THEN
1931 ARP_MESSAGE.Set_Error('Error returned from API WSH_NEW_DELIVERIES_PVT.UPDATE_TMS_INTERFACE_FLAG '||l_return_status);
1932 END IF;
1933 RAISE Update_Del_Exp;
1934 END IF;
1935 END IF;
1936
1937 -- For Include for Planning Deliveries , converted to UR , Delete Legs
1938 IF l_dlvy_id_tab.count > 0 THEN
1939 FORALL delCnt IN l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
1940 DELETE FROM WSH_DELIVERY_LEGS
1941 WHERE DELIVERY_ID = l_dlvy_id_tab(delCnt);
1942 END IF;
1943
1944 -- Freight Cost will get updated for scenarios where Cost was sent by
1945 -- OTM, when EBS receives the trip next time. But to avoid cases
1946 -- where user flips the Ignore for Planning flag,
1947 -- Deleting OTM freight costs
1948
1949 -- Get Freight Cost Type id
1950 l_frcost_type_id := NULL;
1951
1952 OPEN c_get_frcost_type_id;
1953 FETCH c_get_frcost_type_id INTO l_frcost_type_id;
1954 IF c_get_frcost_type_id%NOTFOUND THEN
1955 RAISE frcost_not_found;
1956 END IF;
1957 CLOSE c_get_frcost_type_id;
1958
1959 IF l_dlvy_id_tab.count > 0 THEN
1960 -- Delivery Legs
1961 FORALL delCnt IN l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
1962 DELETE FROM Wsh_Freight_Costs
1963 WHERE Delivery_Leg_Id IN
1964 (SELECT wdl.delivery_leg_id
1965 FROM wsh_delivery_legs wdl
1966 WHERE wdl.delivery_id = l_dlvy_id_tab(delCnt))
1967 AND freight_cost_type_id = l_frcost_type_id;
1968
1969 -- Delivery
1970 FORALL delCnt IN l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
1971 DELETE FROM Wsh_Freight_Costs
1972 WHERE Delivery_Id = l_dlvy_id_tab(delCnt)
1973 AND freight_cost_type_id = l_frcost_type_id;
1974 END IF;
1975 END IF; -- if l_gc3_is_installed
1976 -- End of OTM R12 : customer merge
1977
1978 -- Logging Exceptions for Deliveries Unassigned from Stop
1979 IF ( l_stopIdTab.COUNT > 0 )
1980 THEN
1981 -- { Log Exception
1982 l_message_name := 'WSH_CMRG_UNASSIGN_STOP';
1983
1984 FOR ExpCnt in l_stopIdTab.FIRST..l_stopIdTab.LAST
1985 LOOP
1986 -- { Loop for logging Expceptions
1987
1988 -- OTM R12 : customer merge
1992 IF l_gc3_is_installed = 'Y' THEN
1989 l_skip := 'N'; -- for each stop record
1990 --l_deliveryIdTab and l_StopIdTab count matches
1991
1993 IF l_dlvy_id_tab.count > 0 THEN
1994 l_index := l_dlvy_id_tab.FIRST;
1995 WHILE (l_index IS NOT NULL) LOOP
1996 IF l_deliveryIdTab(ExpCnt) = l_dlvy_id_tab(l_index) THEN
1997 -- skip this stop record, this delivery has been
1998 -- unassigned and no need to update OTM trip stop
1999 l_skip := 'Y';--skip this stop record
2000 EXIT;
2001 END IF;
2002 l_index := l_dlvy_id_tab.NEXT(l_index);
2003 END LOOP;
2004 END IF;
2005 END IF;
2006
2007 IF l_skip = 'N' THEN --if l_skip is 'N' then that means the delivery is not unassigned previously due to OTM integration
2008 -- CONTINUE WITH CURRENT CODE/LOGIC
2009 -- End of OTM R12 : customer merge
2010
2011 FND_MESSAGE.Set_Name ('WSH', l_message_name );
2012 FND_MESSAGE.Set_Token ('PS1', G_FROM_CUST_SITE_ID );
2013 FND_MESSAGE.Set_Token ('PS2', G_TO_CUST_SITE_ID );
2014 FND_MESSAGE.Set_Token ('DELIVERY_ID', l_deliveryIdTab(ExpCnt) );
2015
2016 l_message_text := FND_MESSAGE.Get;
2017
2018 l_return_status := NULL;
2019 l_msg_count := NULL;
2020 l_msg_data := NULL;
2021 l_exception_id := NULL;
2022
2023 WSH_XC_UTIL.log_exception
2024 (
2025 p_api_version => 1.0,
2026 x_return_status => l_return_status,
2027 x_msg_count => l_msg_count,
2028 x_msg_data => l_msg_data,
2029 x_exception_id => l_exception_id,
2030 p_exception_location_id => G_TO_LOCATION_ID,
2031 p_logged_at_location_id => G_TO_LOCATION_ID,
2032 p_logging_entity => 'SHIPPER',
2033 p_logging_entity_id => Fnd_Global.user_id,
2034 p_exception_name => 'WSH_CUSTOMER_MERGE_CHANGE',
2035 p_message => l_message_text,
2036 p_severity => 'LOW',
2037 p_manually_logged => 'N',
2038 p_trip_id => l_tripIdTab(ExpCnt),
2039 p_trip_stop_id => l_stopIdTab(ExpCnt),
2040 p_error_message => l_message_text
2041 );
2042
2043 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2044 THEN
2045 RAISE Update_Del_Exp;
2046 END IF;
2047 END IF; -- OTM R12 : customer merge
2048
2049 -- } Loop for logging Expceptions
2050 END LOOP;
2051 -- } Log Exception
2052 END IF;
2053
2054
2055 --OTM R12: customer merge
2056 IF (l_gc3_is_installed = 'Y' AND l_dlvy_id_tab.count > 0) THEN
2057 -- Filter the list in wsh_tmp which has column1 as delivery_id
2058 -- and column2 as stop id
2059 -- Delete the records from wsh_tmp where column1 = include for
2060 -- planning delivery ids selected above
2061 -- the filter here is done because we don't want to update OTM trip stops,
2062 -- and the update sql right after updates the trip stops.
2063
2064 FORALL delCnt IN l_dlvy_id_tab.FIRST..l_dlvy_id_tab.LAST
2065 DELETE FROM wsh_tmp
2066 WHERE column1 = l_dlvy_id_tab(delCnt);
2067 END IF;
2068 -- End of OTM R12 : customer merge
2069
2070 UPDATE WSH_TRIP_STOPS Wts
2071 SET stop_location_id = G_TO_LOCATION_ID,
2072 last_update_date = SYSDATE,
2073 last_updated_by = arp_standard.profile.user_id,
2074 last_update_login = arp_standard.profile.last_update_login,
2075 request_id = p_req_id,
2076 program_application_id = arp_standard.profile.program_application_id ,
2077 program_id = arp_standard.profile.program_id,
2078 program_update_date = SYSDATE
2079 WHERE Wts.Stop_Id in (
2080 SELECT Column2
2081 FROM WSH_TMP
2082 WHERE Column2 IS NOT NULL )
2083 RETURNING Wts.Stop_Id BULK COLLECT INTO l_stopIdTab;
2084
2085 --
2086 setARMessageRowCount( 'WSH_TRIP_STOPS', SQL%ROWCOUNT );
2087
2088 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
2089 THEN
2090 l_return_status := NULL;
2091
2092 Insert_Log_Table (
2093 p_id_tab => l_stopIdTab,
2094 p_table_name => 'WSH_TRIP_STOPS',
2095 p_req_id => p_req_id,
2096 x_return_status => l_return_status );
2097
2098 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2099 THEN
2100 Raise Update_Del_Exp;
2101 END IF;
2102 END IF;
2103
2104 --
2105 IF l_debug_on THEN
2106 arp_message.set_line('WSH_CUST_MERGE.Process_Deliveries()+' || getTimeStamp );
2107 END IF;
2108 --
2109 EXCEPTION
2110 WHEN Update_Del_Exp THEN
2111 x_return_status := l_return_status;
2112 --
2113 IF ( l_debug_on ) THEN
2117 IF ( Get_Del_Unassign_From_Stop%ISOPEN ) THEN
2114 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Deliveries()+ Update_Del_Exp - ' || getTimeStamp );
2115 END IF;
2116 --
2118 CLOSE Get_Del_Unassign_From_Stop;
2119 END IF;
2120
2121 IF ( Get_Delivery_Containers%ISOPEN ) THEN
2122 CLOSE Get_Delivery_Containers;
2123 END IF;
2124
2125 IF (c_get_deliveries %ISOPEN) THEN
2126 CLOSE c_get_deliveries;
2127 END IF;
2128
2129 IF (c_get_frcost_type_id %ISOPEN) THEN
2130 CLOSE c_get_frcost_type_id;
2131 END IF;
2132
2133 IF (Get_Del_Unassign_From_Stop%ISOPEN) THEN
2134 CLOSE Get_Del_Unassign_From_Stop;
2135 END IF;
2136 -- End of OTM R12 : customer merge
2137 -- OTM R12 : customer merge
2138 WHEN frcost_not_found THEN
2139 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2140 --
2141 IF ( l_debug_on ) THEN
2142 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Deliveries()+ OTM Freight cost type not defined- ' || getTimeStamp );
2143 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
2144 END IF;
2145 --
2146 IF ( Get_Del_Unassign_From_Stop%ISOPEN ) THEN
2147 CLOSE Get_Del_Unassign_From_Stop;
2148 END IF;
2149
2150 IF ( Get_Delivery_Containers%ISOPEN ) THEN
2151 CLOSE Get_Delivery_Containers;
2152 END IF;
2153
2154 IF (c_get_deliveries %ISOPEN) THEN
2155 CLOSE c_get_deliveries;
2156 END IF;
2157
2158 IF (c_get_frcost_type_id %ISOPEN) THEN
2159 CLOSE c_get_frcost_type_id;
2160 END IF;
2161
2162 IF (Get_Del_Unassign_From_Stop%ISOPEN) THEN
2163 CLOSE Get_Del_Unassign_From_Stop;
2164 END IF;
2165 -- End of OTM R12 : customer merge
2166 WHEN OTHERS THEN
2167 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2168 --
2169 IF ( l_debug_on ) THEN
2170 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Deliveries()+ Others - ' || getTimeStamp );
2171 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
2172 END IF;
2173 --
2174 IF ( Get_Del_Unassign_From_Stop%ISOPEN ) THEN
2175 CLOSE Get_Del_Unassign_From_Stop;
2176 END IF;
2177
2178 IF ( Get_Delivery_Containers%ISOPEN ) THEN
2179 CLOSE Get_Delivery_Containers;
2180 END IF;
2181
2182 -- OTM R12 : customer merge
2183 IF (c_get_deliveries %ISOPEN) THEN
2184 CLOSE c_get_deliveries;
2185 END IF;
2186
2187 IF (c_get_frcost_type_id %ISOPEN) THEN
2188 CLOSE c_get_frcost_type_id;
2189 END IF;
2190
2191 IF (Get_Del_Unassign_From_Stop%ISOPEN) THEN
2192 CLOSE Get_Del_Unassign_From_Stop;
2193 END IF;
2194 -- End of OTM R12 : customer merge
2195 --
2196 END Process_Deliveries;
2197 --
2198 --
2199 --
2200 PROCEDURE Process_Open_Deliveries (
2201 p_req_id IN NUMBER,
2202 x_return_status OUT NOCOPY VARCHAR2 )
2203 IS
2204 -- Cursor to fetch Open delivery details.
2205 CURSOR Get_Empty_Deliveries (
2206 p_customer_id NUMBER,
2207 p_location_id NUMBER )
2208 IS
2209 SELECT Wnd.Delivery_Id, Wnd.Rowid
2210 FROM Wsh_New_Deliveries WND
2211 WHERE nvl(Wnd.Customer_Id, p_customer_id) = p_customer_id
2212 AND Wnd.Ultimate_Dropoff_Location_Id = p_location_id
2213 AND Wnd.Status_Code = 'OP'
2214 AND NOT EXISTS
2215 ( SELECT 'x' FROM Wsh_Delivery_Assignments Wda
2216 WHERE Wda.Delivery_Id = Wnd.Delivery_Id )
2217 FOR UPDATE OF Wnd.Delivery_Id NOWAIT;
2218
2219 -- Cursor to fetch deliveries which contains only containers under it.
2220 CURSOR Get_Empty_Cont_Delivery (
2221 p_customer_id NUMBER,
2222 p_location_id NUMBER )
2223 IS
2224 SELECT Det.Delivery_Detail_Id, Dlvy.Delivery_Id,
2225 Det.Rowid, Dlvy.Rowid
2226 FROM Wsh_Delivery_Details Det,
2227 Wsh_Delivery_Assignments Asg,
2228 Wsh_New_Deliveries Dlvy
2229 WHERE Det.Delivery_Detail_Id = Asg.Delivery_Detail_Id
2230 AND Asg.Delivery_Id = Dlvy.Delivery_Id
2231 AND Dlvy.Delivery_Id in
2232 ( SELECT Wnd.Delivery_Id
2233 FROM Wsh_Delivery_Details Wdd,
2234 Wsh_Delivery_Assignments Wda,
2235 Wsh_New_Deliveries Wnd
2236 WHERE Wdd.Container_Flag = 'Y'
2237 AND Wdd.Delivery_Detail_Id = Wda.Delivery_Detail_id
2238 AND Wda.Delivery_Id = Wnd.Delivery_Id
2239 AND nvl(Wnd.Customer_Id, p_customer_id) = p_customer_id
2240 AND Wnd.Ultimate_Dropoff_Location_Id = p_location_id
2241 AND Wnd.Status_Code = 'OP'
2242 AND NOT EXISTS
2243 ( SELECT 'X'
2244 FROM Wsh_Delivery_Assignments Asgn
2245 WHERE Asgn.Parent_Delivery_Detail_Id = Wdd.Delivery_Detail_Id
2246 AND Asgn.Delivery_Id = Wnd.Delivery_Id )
2247 AND NOT EXISTS
2248 ( SELECT 'X'
2252 AND dd.Delivery_Detail_Id = da.Delivery_Detail_Id
2249 FROM Wsh_Delivery_Details dd,
2250 Wsh_Delivery_Assignments da
2251 WHERE dd.Container_Flag = 'N'
2253 AND da.Delivery_Id = Wnd.Delivery_Id ) )
2254 FOR UPDATE OF Det.Delivery_Detail_Id, Dlvy.Delivery_Id NOWAIT;
2255
2256 l_deliveryDetailIdTab g_number_tbl_type;
2257 l_deliveryIdTab g_number_tbl_type;
2258
2259 l_deliveryRowidTab g_rowid_tbl_type;
2260 l_deliveryDetailRowidTab g_rowid_tbl_type;
2261
2262 l_return_status VARCHAR2(10);
2263
2264 Process_Deliveries_Exp EXCEPTION;
2265
2266 --
2267 l_debug_on BOOLEAN;
2268 --
2269 BEGIN
2270 --
2271 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2272 --
2273 IF l_debug_on IS NULL
2274 THEN
2275 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2276 END IF;
2277 --
2278 IF l_debug_on THEN
2279 arp_message.set_line('WSH_CUST_MERGE.Process_Open_Deliveries()+' || getTimeStamp );
2280 END IF;
2281 --
2282
2283 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2284
2285 OPEN Get_Empty_Deliveries ( G_FROM_CUSTOMER_ID,
2286 G_FROM_LOCATION_ID );
2287
2288 LOOP
2289 -- {
2290 FETCH Get_Empty_Deliveries
2291 BULK COLLECT INTO l_deliveryIdTab,
2292 l_deliveryRowidTab
2293 LIMIT G_BATCH_LIMIT;
2294
2295 IF ( l_deliveryRowidTab.COUNT > 0 )
2296 THEN
2297
2298 -- OTM R12 : update delivery
2299 -- no code changes are needed for the following update
2300 -- since the deliveries selected by cursor Get_Empty_Deliveries are
2301 -- empty deliveries
2302
2303 FORALL updCnt in l_deliveryRowidTab.FIRST..l_deliveryRowidTab.LAST
2304 UPDATE Wsh_New_Deliveries
2305 SET customer_id = decode(customer_id,
2306 G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
2307 customer_id),
2308 ultimate_dropoff_location_id = decode(ultimate_dropoff_location_id,
2309 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
2310 ultimate_dropoff_location_id ),
2311 last_update_date = SYSDATE,
2312 last_updated_by = arp_standard.profile.user_id,
2313 last_update_login = arp_standard.profile.last_update_login,
2314 request_id = p_req_id,
2315 program_application_id = arp_standard.profile.program_application_id ,
2316 program_id = arp_standard.profile.program_id,
2317 program_update_date = SYSDATE
2318 WHERE Rowid = l_deliveryRowidTab(updCnt);
2319
2320 --
2321 setARMessageRowCount( 'WSH_TRIP_STOPS', SQL%ROWCOUNT );
2322
2323 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
2324 THEN
2325 l_return_status := NULL;
2326
2327 Insert_Log_Table (
2328 p_id_tab => l_deliveryIdTab,
2329 p_table_name => 'WSH_NEW_DELIVERIES',
2330 p_req_id => p_req_id,
2331 x_return_status => l_return_status );
2332
2333 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2334 THEN
2335 RAISE Process_Deliveries_Exp;
2336 END IF;
2337 END IF;
2338 END IF;
2339
2340 EXIT WHEN Get_Empty_Deliveries%NOTFOUND;
2341 -- }
2342 END LOOP;
2343
2344 CLOSE Get_Empty_Deliveries;
2345
2346 OPEN Get_Empty_Cont_Delivery ( G_FROM_CUSTOMER_ID,
2347 G_FROM_LOCATION_ID );
2348
2349 LOOP
2350 -- {
2351 FETCH Get_Empty_Cont_Delivery
2352 BULK COLLECT INTO l_deliveryDetailIdTab,
2353 l_deliveryIdTab,
2354 l_deliveryDetailRowidTab,
2355 l_deliveryRowidTab
2356 LIMIT G_BATCH_LIMIT;
2357
2358 IF ( l_deliveryRowidTab.COUNT > 0 )
2359 THEN
2360
2361 -- OTM R12 : update delivery
2362 -- no code changes are needed for the following update
2363 -- since the deliveries selected by cursor Get_Empty_Cont_Delivery are
2364 -- empty deliveries
2365
2366 FORALL updCnt in l_deliveryRowidTab.FIRST..l_deliveryRowidTab.LAST
2367 UPDATE Wsh_New_Deliveries
2368 SET customer_id = decode(customer_id,
2369 G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
2370 customer_id),
2371 ultimate_dropoff_location_id = decode(ultimate_dropoff_location_id,
2375 last_updated_by = arp_standard.profile.user_id,
2372 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
2373 ultimate_dropoff_location_id ),
2374 last_update_date = SYSDATE,
2376 last_update_login = arp_standard.profile.last_update_login,
2377 request_id = p_req_id,
2378 program_application_id = arp_standard.profile.program_application_id ,
2379 program_id = arp_standard.profile.program_id,
2380 program_update_date = SYSDATE
2381 WHERE Rowid = l_deliveryRowidTab(updCnt);
2382
2383 --
2384 setARMessageRowCount( 'WSH_TRIP_STOPS', SQL%ROWCOUNT );
2385
2386 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
2387 THEN
2388 l_return_status := NULL;
2389
2390 Insert_Log_Table (
2391 p_id_tab => l_deliveryIdTab,
2392 p_table_name => 'WSH_NEW_DELIVERIES',
2393 p_req_id => p_req_id,
2394 x_return_status => l_return_status );
2395
2396 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2397 THEN
2398 RAISE Process_Deliveries_Exp;
2399 END IF;
2400 END IF;
2401 IF l_deliveryDetailRowidTab.COUNT > 0 THEN
2402 FORALL updCnt in l_deliveryDetailRowidTab.FIRST..l_deliveryDetailRowidTab.LAST
2403 UPDATE Wsh_Delivery_Details Wdd
2404 SET customer_id = decode( customer_id,
2405 G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
2406 customer_id ),
2407 ship_to_location_id = decode( ship_to_location_id,
2408 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
2409 ship_to_location_id ),
2410 deliver_to_location_id = decode( deliver_to_location_id,
2411 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
2412 deliver_to_location_id ),
2413 last_update_date = SYSDATE,
2414 last_updated_by = arp_standard.profile.user_id,
2415 last_update_login = arp_standard.profile.last_update_login,
2416 request_id = p_req_id,
2417 program_application_id = arp_standard.profile.program_application_id ,
2418 program_id = arp_standard.profile.program_id,
2419 program_update_date = SYSDATE
2420 WHERE Wdd.Rowid = l_deliveryDetailRowidTab(updCnt);
2421 END IF;
2422 --
2423 setARMessageRowCount( 'WSH_DELIVERY_DETAILS', SQL%ROWCOUNT );
2424
2425 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
2426 THEN
2427 l_return_status := NULL;
2428
2429 Insert_Log_Table (
2430 p_id_tab => l_deliveryDetailIdTab,
2431 p_table_name => 'WSH_DELIVERY_DETAILS',
2432 p_req_id => p_req_id,
2433 x_return_status => l_return_status );
2434
2435 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2436 THEN
2437 RAISE Process_Deliveries_Exp;
2438 END IF;
2439 END IF;
2440
2441 END IF;
2442
2443 EXIT WHEN Get_Empty_Cont_Delivery%NOTFOUND;
2444 -- }
2445 END LOOP;
2446
2447 CLOSE Get_Empty_Cont_Delivery;
2448
2449 --
2450 IF l_debug_on THEN
2451 arp_message.set_line('WSH_CUST_MERGE.Process_Open_Deliveries()+' || getTimeStamp );
2452 END IF;
2453 --
2454 EXCEPTION
2455 WHEN Process_Deliveries_Exp THEN
2456 x_return_status := l_return_status;
2457 --
2458 IF ( l_debug_on ) THEN
2459 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Open_Deliveries()+ Process_Deliveries_Exp - ' || getTimeStamp );
2460 END IF;
2461 --
2462 IF ( Get_Empty_Deliveries%ISOPEN ) THEN
2463 CLOSE Get_Empty_Deliveries;
2464 END IF;
2465
2466 IF ( Get_Empty_Cont_Delivery%ISOPEN ) THEN
2467 CLOSE Get_Empty_Cont_Delivery;
2468 END IF;
2469 --
2470 WHEN OTHERS THEN
2471 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2472 --
2473 IF ( l_debug_on ) THEN
2474 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Open_Deliveries()+ Others - ' || getTimeStamp );
2475 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
2476 END IF;
2477 --
2478 IF ( Get_Empty_Deliveries%ISOPEN ) THEN
2479 CLOSE Get_Empty_Deliveries;
2480 END IF;
2481
2482 IF ( Get_Empty_Cont_Delivery%ISOPEN ) THEN
2483 CLOSE Get_Empty_Cont_Delivery;
2484 END IF;
2485 --
2486 END Process_Open_Deliveries;
2487
2488 --
2489 --
2490 --
2491 PROCEDURE Process_Open_Lines (
2492 p_req_id IN NUMBER,
2493 x_return_status OUT NOCOPY VARCHAR2 )
2494 IS
2495 -- Cursor to fetch Open delivery details.
2496 CURSOR Get_Open_Lines ( p_customer_id NUMBER,
2500 SELECT WDD.Delivery_Detail_Id, WDA.Parent_Delivery_Detail_Id,
2497 p_site_use_id NUMBER,
2498 p_location_id NUMBER )
2499 IS
2501 WND.Delivery_Id, WTS.Stop_Id,
2502 WDD.Net_Weight, WDD.Gross_Weight,
2503 WDD.Volume, Wdd.Weight_Uom_code, Wdd.Volume_Uom_Code,
2504 Wdd.Inventory_Item_Id, Wdd.Organization_Id,
2505 WDD.Rowid, WDA.Rowid
2506 FROM Wsh_Delivery_Details WDD,
2507 Wsh_Delivery_Assignments WDA,
2508 Wsh_New_Deliveries WND,
2509 Wsh_Delivery_Legs WDL,
2510 Wsh_Trip_Stops WTS
2511 WHERE WTS.Stop_id (+) = WDL.Drop_Off_Stop_Id
2512 AND WDL.Delivery_Id (+) = WND.Delivery_Id
2513 AND NVL(WND.Status_Code, 'OP') = 'OP'
2514 AND WND.Delivery_Id (+) = WDA.Delivery_Id
2515 AND WDA.Delivery_Detail_Id = WDD.Delivery_Detail_Id
2516 AND WDD.Container_Flag = 'N'
2517 AND (WDD.Customer_Id = p_customer_id
2518 --Bug 5900667: Reverting back from AND to OR
2519 OR ((WDD.Ship_To_Site_Use_Id = p_site_use_id
2520 AND WDD.Ship_To_Location_Id = p_location_id )
2521 OR (WDD.Deliver_To_Site_Use_Id = p_site_use_id
2522 AND WDD.Deliver_To_Location_Id = p_location_id )))
2523 AND WDD.Released_Status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
2524 FOR UPDATE OF Wdd.Delivery_Detail_Id, Wda.Delivery_Detail_Id, Wnd.Delivery_Id, Wts.Stop_Id NOWAIT;
2525
2526 l_deliveryDetailIdTab g_number_tbl_type;
2527 l_parentDeliveryDetailIdTab g_number_tbl_type;
2528 l_deliveryIdTab g_number_tbl_type;
2529 l_dummyIdTab g_number_tbl_type;
2530 l_stopIdTab g_number_tbl_type;
2531 l_grossWeightTab g_number_tbl_type;
2532 l_netWeightTab g_number_tbl_type;
2533 l_volumeTab g_number_tbl_type;
2534 l_inventoryItemIdTab g_number_tbl_type;
2535 l_organizationIdTab g_number_tbl_type;
2536
2537 l_weightUomTab g_char_tbl_type;
2538 l_volumeUomTab g_char_tbl_type;
2539
2540 l_deliveryDetailRowidTab g_rowid_tbl_type;
2541 l_deliveryAssgRowidTab g_rowid_tbl_type;
2542
2543 l_exception_id NUMBER;
2544 l_msg_count NUMBER;
2545 l_msg_data VARCHAR2(32767);
2546 l_return_status VARCHAR2(10);
2547 l_message_text VARCHAR2(32767);
2548 l_message_name VARCHAR2(50);
2549 l_tmp_cnt NUMBER;
2550
2551 Process_Open_Lines_Exp EXCEPTION;
2552 --
2553 l_debug_on BOOLEAN;
2554 --
2555 BEGIN
2556 --
2557 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2558 --
2559 IF l_debug_on IS NULL
2560 THEN
2561 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2562 END IF;
2563 --
2564 IF l_debug_on THEN
2565 arp_message.set_line('WSH_CUST_MERGE.Process_Open_Lines()+' || getTimeStamp );
2566 END IF;
2567 --
2568
2569 x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
2570
2571 -- Processing open lines in shipping
2572 OPEN Get_Open_Lines ( G_FROM_CUSTOMER_ID,
2573 G_FROM_CUST_SITE_ID,
2574 G_FROM_LOCATION_ID );
2575
2576 LOOP
2577 -- { Loop for Open delivery lines
2578 FETCH Get_Open_Lines
2579 BULK COLLECT INTO l_deliveryDetailIdTab,
2580 l_parentDeliveryDetailIdTab,
2581 l_deliveryIdTab,
2582 l_stopIdTab,
2583 l_netWeightTab,
2584 l_grossWeightTab,
2585 l_volumeTab,
2586 l_weightUomTab,
2587 l_volumeUomTab,
2588 l_inventoryItemIdTab,
2589 l_organizationIdTab,
2590 l_deliveryDetailRowidTab,
2591 l_deliveryAssgRowidTab
2592 LIMIT G_BATCH_LIMIT;
2593
2594 IF ( l_deliveryDetailIdTab.COUNT > 0 )
2595 THEN
2596 -- {
2597
2598 -- Update non-container lines
2599 IF l_deliveryDetailRowidTab.count > 0 THEN
2600 FORALL bulkCnt in l_deliveryDetailRowidTab.FIRST..l_deliveryDetailRowidTab.LAST
2601 UPDATE Wsh_Delivery_Details Wdd
2602 SET customer_id = decode( customer_id,
2603 G_FROM_CUSTOMER_ID, G_TO_CUSTOMER_ID,
2604 customer_id ),
2605 ship_to_site_use_id = decode( ship_to_site_use_id,
2606 G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
2607 ship_to_site_use_id ),
2608 deliver_to_site_use_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
2609 G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
2610 deliver_to_site_use_id ),
2611 ship_to_location_id = decode( ship_to_site_use_id,
2612 G_FROM_CUST_SITE_ID,
2613 decode(ship_to_location_id,
2617 deliver_to_location_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
2614 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
2615 ship_to_location_id ),
2616 ship_to_location_id ),
2618 G_FROM_CUST_SITE_ID,
2619 decode(deliver_to_location_id,
2620 G_FROM_LOCATION_ID, G_TO_LOCATION_ID,
2621 deliver_to_location_id ),
2622 deliver_to_location_id ),
2623 last_update_date = SYSDATE,
2624 last_updated_by = arp_standard.profile.user_id,
2625 last_update_login = arp_standard.profile.last_update_login,
2626 request_id = p_req_id,
2627 program_application_id = arp_standard.profile.program_application_id ,
2628 program_id = arp_standard.profile.program_id,
2629 program_update_date = SYSDATE
2630 WHERE Wdd.Rowid = l_deliveryDetailRowidTab(bulkCnt);
2631 END IF;
2632 --
2633 setARMessageRowCount( 'WSH_DELIVERY_DETAILS', SQL%ROWCOUNT );
2634
2635 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
2636 THEN
2637 l_return_status := NULL;
2638
2639 Insert_Log_Table (
2640 p_id_tab => l_deliveryDetailIdTab,
2641 p_table_name => 'WSH_DELIVERY_DETAILS',
2642 p_req_id => p_req_id,
2643 x_return_status => l_return_status );
2644
2645 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2646 THEN
2647 RAISE Process_Open_Lines_Exp;
2648 END IF;
2649 END IF;
2650
2651 -- }
2652 END IF;
2653
2654 -- Unassign from container only if site use code is "SHIP_TO"
2655 IF ( l_deliveryAssgRowidTab.COUNT > 0 and
2656 G_SITE_USE_CODE = 'SHIP_TO' )
2657 THEN
2658 -- {
2659 l_return_status := NULL;
2660 l_dummyIdTab.delete;
2661
2662 Adjust_Weight_Volume (
2663 p_entity_type => 'CONT',
2664 p_delivery_detail => l_deliveryDetailIdTab,
2665 p_parent_delivery_detail => l_parentDeliveryDetailIdTab,
2666 p_delivery_id => l_deliveryIdTab,
2667 p_delivery_leg_id => l_dummyIdTab,
2668 p_net_weight => l_netWeightTab,
2669 p_gross_weight => l_grossWeightTab,
2670 p_volume => l_volumeTab,
2671 x_return_status => l_return_status );
2672
2673 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
2674 WSH_UTIL_CORE.G_RET_STS_WARNING) )
2675 THEN
2676 --
2677 IF ( l_debug_on ) THEN
2678 ARP_MESSAGE.Set_Error('Error returned from API Adjust_Weight_Volume');
2679 END IF;
2680 --
2681 RAISE Process_Open_Lines_Exp;
2682 END IF;
2683
2684 -- Unassign delivery details from containers if it is packed and not
2685 -- assigned to a delivery.
2686 FORALL unassignCnt IN l_deliveryAssgRowidTab.FIRST..l_deliveryAssgRowidTab.LAST
2687 UPDATE WSH_DELIVERY_ASSIGNMENTS
2688 SET parent_delivery_detail_id = null,
2689 last_update_date = SYSDATE,
2690 last_updated_by = arp_standard.profile.user_id,
2691 last_update_login = arp_standard.profile.last_update_login,
2692 program_application_id = arp_standard.profile.program_application_id,
2693 program_id = arp_standard.profile.program_id,
2694 program_update_date = SYSDATE
2695 WHERE rowid = l_deliveryAssgRowidTab(unassignCnt)
2696 AND Parent_Delivery_Detail_Id IS NOT NULL
2697 AND Delivery_Id IS NULL;
2698
2699 --
2700 setARMessageRowCount( 'WSH_DELIVERY_ASSIGNMENTS', SQL%ROWCOUNT );
2701
2702 l_return_status := NULL;
2703 l_dummyIdTab.delete;
2704
2705 Adjust_Parent_WV (
2706 p_entity_type => 'CONT',
2707 p_delivery_detail => l_deliveryDetailIdTab,
2708 p_parent_delivery_detail => l_parentDeliveryDetailIdTab,
2709 p_delivery_id => l_deliveryIdTab,
2710 p_inventory_item_id => l_inventoryItemIdTab,
2711 p_organization_id => l_organizationIdTab,
2712 p_weight_uom => l_weightUomTab,
2713 p_volume_uom => l_volumeUomTab,
2714 x_return_status => l_return_status );
2715
2716 IF ( l_return_status NOT IN ( WSH_UTIL_CORE.G_RET_STS_SUCCESS,
2717 WSH_UTIL_CORE.G_RET_STS_WARNING) )
2718 THEN
2719 --
2720 IF ( l_debug_on ) THEN
2721 ARP_MESSAGE.Set_Error('Error returned from API Adjust_Parent_WV');
2722 END IF;
2723 --
2727 END IF;
2724 RAISE Process_Open_Lines_Exp;
2725 END IF;
2726 -- }
2728
2729 -- Log exceptions against conatiners from which delivery details
2730 -- are Unassigned in above update
2731 IF ( l_parentDeliveryDetailIdTab.COUNT > 0 and
2732 G_SITE_USE_CODE = 'SHIP_TO' )
2733 THEN
2734 -- {
2735 l_message_name := 'WSH_CMRG_UNASSIGN_CONTAINER';
2736
2737 FOR expCnt in l_parentDeliveryDetailIdTab.FIRST..l_parentDeliveryDetailIdTab.LAST
2738 LOOP
2739 -- { Loop for logging Exception
2740 IF ( l_parentDeliveryDetailIdTab(expCnt) IS NOT NULL AND
2741 l_deliveryIdTab(expCnt) IS NULL )
2742 THEN
2743 -- {
2744 FND_MESSAGE.Set_Name ('WSH', l_message_name );
2745 FND_MESSAGE.Set_Token ('PS1', G_FROM_CUST_SITE_ID );
2746 FND_MESSAGE.Set_Token ('PS2', G_TO_CUST_SITE_ID );
2747 FND_MESSAGE.Set_Token ('DELIVERY_DETAIL_ID', l_deliveryDetailIdTab(ExpCnt) );
2748
2749 l_message_text := FND_MESSAGE.Get;
2750
2751 l_return_status := NULL;
2752 l_msg_count := NULL;
2753 l_msg_data := NULL;
2754 l_exception_id := NULL;
2755
2756 WSH_XC_UTIL.Log_Exception
2757 (
2758 p_api_version => 1.0,
2759 x_return_status => l_return_status,
2760 x_msg_count => l_msg_count,
2761 x_msg_data => l_msg_data,
2762 x_exception_id => l_exception_id,
2763 p_exception_location_id => G_TO_LOCATION_ID,
2764 p_logged_at_location_id => G_TO_LOCATION_ID,
2765 p_logging_entity => 'SHIPPER',
2766 p_logging_entity_id => Fnd_Global.user_id,
2767 p_exception_name => 'WSH_CUSTOMER_MERGE_CHANGE',
2768 p_message => l_message_text,
2769 p_severity => 'LOW',
2770 p_manually_logged => 'N',
2771 p_delivery_detail_id => l_parentDeliveryDetailIdTab(expCnt),
2772 p_error_message => l_message_text
2773 );
2774
2775 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2776 THEN
2777 ARP_MESSAGE.Set_Error('Error returned from API WSH_XC_UTIL.Log_Exception');
2778 RAISE Process_Open_Lines_Exp;
2779 END IF;
2780
2781 -- }
2782 END IF;
2783 -- } Loop for logging Exception
2784 END LOOP;
2785 -- }
2786 END IF;
2787
2788 -- Insert delivery and stop details into wsh_tmp table
2789 IF ( l_deliveryIdTab.COUNT > 0 and
2790 G_SITE_USE_CODE = 'SHIP_TO' )
2791 THEN
2792 -- Inserting records in bulk into temp table for future reference
2793 -- during processing.
2794 -- Dulplicate entries are avoided using NOT EXISTS condition
2795 FORALL insCnt in l_deliveryIdTab.FIRST..l_deliveryIdTab.LAST
2796 INSERT INTO Wsh_Tmp ( Column1, Column2, Column3 )
2797 SELECT l_deliveryIdTab(insCnt), l_stopIdTab(insCnt), l_deliveryDetailIdTab(insCnt)
2798 FROM DUAL
2799 WHERE l_deliveryIdTab(insCnt) IS NOT NULL
2800 --Start of fix for bug 5900667
2801 --Populate details of delivery/stop into table only if location matches.
2802 AND EXISTS
2803 ( SELECT 'x'
2804 FROM Wsh_New_Deliveries
2805 WHERE ultimate_dropoff_location_id = G_FROM_LOCATION_ID
2806 AND delivery_id = l_deliveryIdTab(insCnt) )
2807 --End of fix for bug 5900667
2808 AND NOT EXISTS
2809 ( SELECT 'x'
2810 FROM Wsh_Tmp
2811 WHERE Column1 = l_deliveryIdTab(insCnt)
2812 AND ( Column2 = l_stopIdTab(insCnt) OR l_stopIdTab(insCnt) is null ) );
2813 END IF;
2814
2815 EXIT WHEN Get_Open_Lines%NOTFOUND;
2816 -- } Loop for Open delivery lines
2817 END LOOP;
2818
2819 CLOSE Get_Open_Lines;
2820
2821 -- Updation of delivery, stop and Unassigning from delivery, stop is done
2822 -- only if G_SITE_USE_CODE is "SHIP_TO"
2823 IF ( G_SITE_USE_CODE = 'SHIP_TO' )
2824 THEN
2825 -- ( site use code
2826 Unassign_Details_From_Delivery (
2827 p_req_id => p_req_id,
2828 x_return_status => l_return_status );
2829
2830 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2831 THEN
2832 RAISE Process_Open_Lines_Exp;
2833 END IF;
2834
2835 SELECT COUNT(*)
2836 INTO l_tmp_cnt
2837 FROM WSH_TMP;
2838
2839 IF ( l_tmp_cnt > 0 )
2840 THEN
2841 Process_Deliveries (
2842 p_req_id => p_req_id,
2843 x_return_status => l_return_status );
2844
2848 END IF;
2845 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
2846 THEN
2847 RAISE Process_Open_Lines_Exp;
2849 END IF;
2850 -- } site use code
2851 END IF;
2852
2853 -- Deleting records from Wsh_Tmp table
2854 DELETE FROM Wsh_Tmp;
2855
2856 --
2857 IF l_debug_on THEN
2858 arp_message.set_line('WSH_CUST_MERGE.Process_Open_Lines()+' || getTimeStamp );
2859 END IF;
2860 --
2861 EXCEPTION
2862 WHEN Process_Open_Lines_Exp THEN
2863 x_return_status := l_return_status;
2864 --
2865 IF l_debug_on THEN
2866 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Open_Lines()+ Process_Open_Lines_Exp - ' || getTimeStamp );
2867 END IF;
2868 --
2869
2870 -- Close open cursors
2871 IF ( Get_Open_Lines%ISOPEN ) THEN
2872 CLOSE Get_Open_Lines;
2873 END IF;
2874
2875 WHEN OTHERS THEN
2876 --
2877 IF l_debug_on THEN
2878 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Process_Open_Lines()+ Others - ' || getTimeStamp );
2879 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
2880 END IF;
2881 --
2882 -- Close open cursors
2883 IF ( Get_Open_Lines%ISOPEN ) THEN
2884 CLOSE Get_Open_Lines;
2885 END IF;
2886
2887 x_return_status := WSH_UTIL_CORE.G_RET_STS_UNEXP_ERROR;
2888 END Process_Open_Lines;
2889
2890 -----------------------------------------------------------------------------------------
2891 --
2892 -- Procedure: Delivery_Details
2893 -- Description: New code to merge customer and site information in WSH_DELIVERY_DETAILS.
2894 -- Usage: Called by WSH_CUST_MERGE.Merge
2895 --
2896 --
2897 -- Assumptions
2898 -- If deliver_to_site_use id is null, it is assumed to be same as
2899 -- ship_to_site_use_id for containers, deliver_to_site_use_id and ship_to_site_use_id will be null.
2900 -- hence, this code looks at the container hierarchy of a line being updated
2901 -- and updates their locations to be same as the corresp. line.(if line is not
2902 -- shipped)
2903 -----------------------------------------------------------------------------------------
2904
2905 Procedure Delivery_Details
2906 (
2907 Req_Id IN NUMBER,
2908 Set_Num IN NUMBER,
2909 Process_Mode IN VARCHAR2
2910 )
2911 IS
2912
2913 CURSOR Get_Shipped_Cont_Lines (
2914 p_customer_id NUMBER,
2915 p_site_use_id NUMBER,
2916 p_location_id NUMBER )
2917 IS
2918 SELECT Det.Delivery_Detail_Id, Det.Rowid
2919 FROM Wsh_Delivery_Details Det
2920 WHERE Det.Container_Flag = 'Y'
2921 AND Det.Delivery_Detail_Id IN
2922 ( SELECT Wda.Parent_Delivery_Detail_Id
2923 FROM Wsh_Delivery_Assignments Wda
2924 WHERE Wda.Parent_Delivery_Detail_Id IS NOT NULL
2925 CONNECT BY PRIOR Wda.Parent_Delivery_Detail_Id = Wda.Delivery_Detail_Id
2926 START WITH wda.delivery_detail_id IN
2927 ( SELECT WDD.Delivery_Detail_Id
2928 FROM Wsh_Delivery_Details WDD,
2929 Wsh_Delivery_Assignments WDA,
2930 Wsh_New_Deliveries WND
2931 WHERE Wnd.Status_Code in ( 'CO', 'CL', 'IT' )
2932 -- Added Parent_Delivery_Detail_Id for Perf. improvement,
2933 -- as per perf. team suggestion.
2934 AND Wda.Parent_Delivery_Detail_Id IS NOT NULL
2935 AND Wnd.Delivery_Id = Wda.Delivery_Id
2936 AND Wda.Delivery_Detail_Id = Wdd.Delivery_Detail_Id
2937 AND Wdd.Container_Flag = 'N'
2938 AND (Wdd.Customer_Id = p_customer_id
2939 --Bug 5900667: Reverting back from AND to OR
2940 OR ((Wdd.Ship_To_Site_Use_Id = p_site_use_id AND
2941 Wdd.Ship_To_Location_Id = p_location_id )
2942 OR (Wdd.Deliver_To_Site_Use_Id = p_site_use_id AND
2943 Wdd.Deliver_To_Location_Id = p_location_id)))
2944 AND Wdd.Released_Status in ( 'Y', 'C', 'X' ) ) )
2945 FOR UPDATE OF Det.Delivery_Detail_Id NOWAIT;
2946
2947 -- Cursor for selecting Closed and Confirmed shipping lines
2948 CURSOR Get_Shipped_Lines ( p_customer_id NUMBER,
2949 p_site_use_id NUMBER,
2950 p_location_id NUMBER )
2951 IS
2952 SELECT Wdd.Delivery_Detail_Id, Wdd.Rowid
2953 FROM Wsh_Delivery_Details WDD,
2954 Wsh_Delivery_Assignments WDA,
2955 Wsh_New_Deliveries WND
2956 WHERE Wnd.Status_Code in ( 'CO', 'CL', 'IT' )
2957 AND Wnd.Delivery_Id = Wda.Delivery_Id
2958 AND Wda.Delivery_Detail_Id = Wdd.Delivery_Detail_Id
2959 AND Wdd.Container_Flag = 'N'
2960 AND (Wdd.Customer_Id = p_customer_id
2961 --Bug 5900667: Reverting back from AND to OR
2962 OR ((Wdd.Ship_To_Site_Use_Id = p_site_use_id
2963 AND Wdd.Ship_To_Location_Id = p_location_id )
2964 OR (Wdd.Deliver_To_Site_Use_Id = p_site_use_id
2965 AND Wdd.Deliver_To_Location_Id = p_location_id)))
2966 AND Wdd.Released_Status in ( 'Y', 'C', 'X' )
2970 l_toCustomerIdTab g_number_tbl_type;
2967 FOR UPDATE OF Wdd.Delivery_Detail_Id NOWAIT;
2968
2969 l_fromCustomerIdTab g_number_tbl_type;
2971 l_fromCustomerSiteIdTab g_number_tbl_type;
2972 l_toCustomerSiteIdTab g_number_tbl_type;
2973 l_orgcustomerMergeHeaderIdTab g_number_tbl_type;
2974 l_deliveryDetailIdTab g_number_tbl_type;
2975 l_customerSiteCodeTab g_char_tbl_type;
2976 l_deliveryDetailRowidTab g_rowid_tbl_type;
2977
2978 l_old_location_id NUMBER;
2979 l_new_location_id NUMBER;
2980 l_return_status VARCHAR2(10);
2981
2982 Merge_Exp EXCEPTION;
2983
2984 --
2985 l_debug_on BOOLEAN;
2986 --
2987 wsh_cust_site_to_loc_err EXCEPTION;
2988
2989 BEGIN
2990 --
2991 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
2992 --
2993 IF l_debug_on IS NULL
2994 THEN
2995 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
2996 END IF;
2997 --
2998 IF l_debug_on THEN
2999 arp_message.set_line('WSH_CUST_MERGE.Delivery_Details()+' || getTimeStamp );
3000 END IF;
3001
3002 IF (process_mode = 'LOCK') THEN
3003 setARMessageLockTable('WSH_DELIVERY_DETAILS');
3004 NULL;
3005 ELSE
3006 --{ Not Process Lock
3007 -- WSH tables stores only transactions for SHIP_TO and DELIVER_TO transactions
3008 SELECT duplicate_id, customer_id,
3009 duplicate_site_id, customer_site_id, customer_merge_header_id,
3010 customer_site_code
3011 BULK COLLECT INTO l_fromCustomerIdTab, l_toCustomerIdTab,
3012 l_fromCustomerSiteIdTab, l_toCustomerSiteIdTab, l_orgcustomerMergeHeaderIdTab,
3013 l_customerSiteCodeTab
3014 FROM ra_customer_merges
3015 WHERE process_flag = 'N'
3016 AND customer_site_code in ( 'SHIP_TO', 'DELIVER_TO' )
3017 AND request_id = Req_Id
3018 AND set_number = Set_Num;
3019
3020 IF l_fromCustomerIdTab.COUNT > 0
3021 THEN
3022 -- { Record exists in Ra_Customer_Merges table
3023 FOR i in l_fromCustomerIdTab.FIRST..l_fromCustomerIdTab.LAST
3024 LOOP
3025 -- { Main Loop
3026 -- Get Locations of the old/duplicate Site Use ID
3027 --
3028 BEGIN
3029 l_old_location_id := WSH_UTIL_CORE.Cust_Site_To_Location(l_fromCustomerSiteIdTab(i));
3030 EXCEPTION
3031 WHEN NO_DATA_FOUND then
3032 raise wsh_cust_site_to_loc_err;
3033 WHEN OTHERS then
3034 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
3035 raise;
3036 END;
3037 --
3038 --
3039 -- Get Locations of the new Site Use ID
3040 --
3041 BEGIN
3042 l_new_location_id := WSH_UTIL_CORE.Cust_Site_To_Location(l_toCustomerSiteIdTab(i));
3043 EXCEPTION
3044 WHEN NO_DATA_FOUND then
3045 raise wsh_cust_site_to_loc_err;
3046 WHEN OTHERS then
3047 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
3048 raise;
3049 END;
3050 --
3051
3052 -- Populate Into Global Variables.
3053 -- These Global Variables are used in following API's
3054 -- 1. Process_Open_Lines
3055 -- 2. Unassign_Details_From_Delivery
3056 -- 3. Process_Deliveries
3057 -- 4. Insert_Log_Table
3058 G_MERGE_HEADER_ID := l_orgcustomerMergeHeaderIdTab(i);
3059 G_FROM_CUSTOMER_ID := l_fromCustomerIdTab(i);
3060 G_FROM_CUST_SITE_ID := l_fromCustomerSiteIdTab(i);
3061 G_FROM_LOCATION_ID := l_old_location_id;
3062 G_TO_CUSTOMER_ID := l_toCustomerIdTab(i);
3063 G_TO_CUST_SITE_ID := l_toCustomerSiteIdTab(i);
3064 G_TO_LOCATION_ID := l_new_location_id;
3065 G_SITE_USE_CODE := l_customerSiteCodeTab(i);
3066
3067 OPEN Get_Shipped_Cont_Lines (
3068 G_FROM_CUSTOMER_ID,
3069 G_FROM_CUST_SITE_ID,
3070 G_FROM_LOCATION_ID );
3071
3072 LOOP
3073 -- { Loop to process shipped container lines
3074 FETCH Get_Shipped_Cont_Lines
3075 BULK COLLECT INTO l_deliveryDetailIdTab,
3076 l_deliveryDetailRowidTab
3077 LIMIT G_BATCH_LIMIT;
3078
3079 IF ( l_deliveryDetailRowidTab.COUNT > 0 )
3080 THEN
3081 -- {
3082 -- Update Container lines
3083 FORALL bulkCnt in l_deliveryDetailRowidTab.FIRST..l_deliveryDetailRowidTab.LAST
3084 UPDATE Wsh_Delivery_Details WDD
3085 SET customer_id = decode(customer_id, G_FROM_CUSTOMER_ID,
3086 G_TO_CUSTOMER_ID, customer_id ),
3087 ship_to_site_use_id = decode(ship_to_site_use_id,
3088 G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
3089 ship_to_site_use_id ),
3093 last_update_date = SYSDATE,
3090 deliver_to_site_use_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
3091 G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
3092 deliver_to_site_use_id ),
3094 last_updated_by = arp_standard.profile.user_id,
3095 last_update_login = arp_standard.profile.last_update_login,
3096 request_id = req_id,
3097 program_application_id = arp_standard.profile.program_application_id ,
3098 program_id = arp_standard.profile.program_id,
3099 program_update_date = SYSDATE
3100 WHERE Wdd.Container_Flag = 'Y'
3101 AND Wdd.Rowid = l_deliveryDetailRowidTab(bulkCnt);
3102
3103 --
3104 setARMessageRowCount( 'WSH_DELIVERY_DETAILS', SQL%ROWCOUNT );
3105
3106 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
3107 THEN
3108 l_return_status := NULL;
3109
3110 Insert_Log_Table (
3111 p_id_tab => l_deliveryDetailIdTab,
3112 p_table_name => 'WSH_DELIVERY_DETAILS',
3113 p_req_id => req_id,
3114 x_return_status => l_return_status );
3115
3116 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
3117 THEN
3118 RAISE Merge_Exp;
3119 END IF;
3120 END IF;
3121 -- }
3122 END IF;
3123
3124 EXIT WHEN Get_Shipped_Cont_Lines%NOTFOUND;
3125
3126 -- } Loop to process shipped container lines
3127 END LOOP;
3128
3129 CLOSE Get_Shipped_Cont_Lines;
3130
3131 OPEN Get_Shipped_Lines (
3132 G_FROM_CUSTOMER_ID,
3133 G_FROM_CUST_SITE_ID,
3134 G_FROM_LOCATION_ID );
3135 LOOP
3136 -- { Loop to process shipped lines
3137 FETCH Get_Shipped_Lines
3138 BULK COLLECT INTO l_deliveryDetailIdTab,
3139 l_deliveryDetailRowidTab
3140 LIMIT G_BATCH_LIMIT;
3141
3142 IF ( l_deliveryDetailIdTab.COUNT > 0 )
3143 THEN
3144 -- {
3145 -- Update non-container lines
3146 IF l_deliveryDetailRowidTab.COUNT > 0 THEN
3147 FORALL bulkCnt in l_deliveryDetailRowidTab.FIRST..l_deliveryDetailRowidTab.LAST
3148 UPDATE Wsh_Delivery_Details Wdd
3149 SET customer_id = decode(customer_id, G_FROM_CUSTOMER_ID,
3150 G_TO_CUSTOMER_ID, customer_id ),
3151 ship_to_site_use_id = decode(ship_to_site_use_id,
3152 G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
3153 ship_to_site_use_id ),
3154 deliver_to_site_use_id = decode( nvl(deliver_to_site_use_id, ship_to_site_use_id),
3155 G_FROM_CUST_SITE_ID, G_TO_CUST_SITE_ID,
3156 deliver_to_site_use_id ),
3157 last_update_date = SYSDATE,
3158 last_updated_by = arp_standard.profile.user_id,
3159 last_update_login = arp_standard.profile.last_update_login,
3160 request_id = req_id,
3161 program_application_id = arp_standard.profile.program_application_id ,
3162 program_id = arp_standard.profile.program_id,
3163 program_update_date = SYSDATE
3164 WHERE Wdd.Rowid = l_deliveryDetailRowidTab(bulkCnt);
3165 END IF;
3166 --
3167 setARMessageRowCount( 'WSH_DELIVERY_DETAILS', SQL%ROWCOUNT );
3168
3169 IF ( G_PROFILE_VAL IS NOT NULL AND G_PROFILE_VAL = 'Y' )
3170 THEN
3171 l_return_status := NULL;
3172
3173 Insert_Log_Table (
3174 p_id_tab => l_deliveryDetailIdTab,
3175 p_table_name => 'WSH_DELIVERY_DETAILS',
3176 p_req_id => req_id,
3177 x_return_status => l_return_status );
3178
3179 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
3180 THEN
3181 --
3182 IF ( l_debug_on ) THEN
3183 ARP_MESSAGE.Set_Error('Error returned from API Insert_Log_Table');
3184 END IF;
3185 --
3186 RAISE Merge_Exp;
3187 END IF;
3188 END IF;
3189 -- }
3190 END IF;
3191
3192 EXIT WHEN Get_Shipped_Lines%NOTFOUND;
3193 -- } Loop to process shipped lines
3197
3194 END LOOP;
3195
3196 CLOSE Get_Shipped_Lines;
3198 DELETE FROM Wsh_Tmp;
3199
3200 -- Processes open delivery detail lines
3201 -- All others necessary values are taken from Global Variables
3202 Process_Open_Lines (
3203 p_req_id => req_id,
3204 x_return_status => l_return_status );
3205
3206 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
3207 THEN
3208 --
3209 IF ( l_debug_on ) THEN
3210 ARP_MESSAGE.Set_Error('Error returned from API Process_Open_Lines');
3211 END IF;
3212 --
3213 RAISE Merge_Exp;
3214 END IF;
3215
3216 IF ( G_SITE_USE_CODE = 'SHIP_TO' )
3217 THEN
3218 -- {
3219 -- Processes open delivery lines which are Empty/Contains only
3220 -- under the delivery.
3221 Process_Open_Deliveries (
3222 p_req_id => req_id,
3223 x_return_status => l_return_status );
3224
3225 IF ( l_return_status <> WSH_UTIL_CORE.G_RET_STS_SUCCESS )
3226 THEN
3227 --
3228 IF ( l_debug_on ) THEN
3229 ARP_MESSAGE.Set_Error('Error returned from API Process_Open_Deliveries');
3230 END IF;
3231 --
3232 RAISE Merge_Exp;
3233 END IF;
3234 -- }
3235 END IF;
3236 -- } Main Loop
3237 END LOOP;
3238 -- } Record exists in Ra_Customer_Merges table
3239 END IF;
3240 --} Not Process Lock
3241 END IF;
3242
3243
3244 IF l_debug_on THEN
3245 arp_message.set_line('WSH_CUST_MERGE.Delivery_Details()-' || getTimeStamp);
3246 END IF;
3247
3248 EXCEPTION
3249 WHEN wsh_cust_site_to_loc_err THEN
3250 --
3251 IF ( l_debug_on ) THEN
3252 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Delivery_Details()+ wsh_cust_site_to_loc_err - ' || getTimeStamp );
3253 END IF;
3254 --
3255
3256 RAISE;
3257 WHEN Merge_Exp THEN
3258 --
3259 IF ( l_debug_on ) THEN
3260 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Delivery_Details - Merge_Exp');
3261 END IF;
3262 --
3263
3264 -- Close if cursors are open
3265 IF ( Get_Shipped_Cont_Lines%ISOPEN ) THEN
3266 CLOSE Get_Shipped_Cont_Lines;
3267 END IF;
3268
3269 IF ( Get_Shipped_Lines%ISOPEN ) THEN
3270 CLOSE Get_Shipped_Lines;
3271 END IF;
3272
3273 RAISE;
3274
3275 WHEN OTHERS THEN
3276 --
3277 IF ( l_debug_on ) THEN
3278 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Delivery_Details()+ Others - ' || getTimeStamp );
3279 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
3280 END IF;
3281 --
3282
3283 -- Close if cursors are open
3284 IF ( Get_Shipped_Cont_Lines%ISOPEN ) THEN
3285 CLOSE Get_Shipped_Cont_Lines;
3286 END IF;
3287
3288 IF ( Get_Shipped_Lines%ISOPEN ) THEN
3289 CLOSE Get_Shipped_Lines;
3290 END IF;
3291
3292 RAISE;
3293
3294 END Delivery_Details;
3295
3296 --
3297 --
3298 -- Procedure : Picking_batches
3299 -- Description : New code to merge customer and site information in
3300 -- WSH_PICKING_BATCHES
3301 -- Usage : Called by WSH_CUST_MERGE.Merge
3302
3303 Procedure Picking_batches
3304 (
3305 Req_Id IN NUMBER,
3306 Set_Num IN NUMBER,
3307 Process_Mode IN VARCHAR2
3308 )
3309 IS
3310 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
3311 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
3312 INDEX BY BINARY_INTEGER;
3313 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
3314
3315 TYPE BATCH_ID_LIST_TYPE IS TABLE OF
3316 WSH_PICKING_BATCHES.BATCH_ID%TYPE
3317 INDEX BY BINARY_INTEGER;
3318 PRIMARY_KEY_ID_LIST BATCH_ID_LIST_TYPE;
3319
3320 TYPE customer_id_LIST_TYPE IS TABLE OF
3321 WSH_PICKING_BATCHES.customer_id%TYPE
3322 INDEX BY BINARY_INTEGER;
3323 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
3324 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
3325
3326 l_profile_val VARCHAR2(30);
3327 CURSOR merged_records IS
3328 SELECT distinct CUSTOMER_MERGE_HEADER_ID
3329 ,BATCH_ID
3330 ,yt.customer_id
3331 FROM WSH_PICKING_BATCHES yt, ra_customer_merges m
3332 WHERE (
3333 yt.customer_id = m.DUPLICATE_ID
3334 ) AND m.process_flag = 'N'
3335 AND m.request_id = req_id
3336 AND m.set_number = set_num;
3337 l_last_fetch BOOLEAN := FALSE;
3338 l_count NUMBER :=0;
3339 --
3340 l_debug_on BOOLEAN;
3341 --
3342 BEGIN
3343 --
3344 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3345 --
3346 IF l_debug_on IS NULL
3347 THEN
3348 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3349 END IF;
3350 --
3351 IF l_debug_on THEN
3355 setARMessageLockTable('WSH_PICKING_BATCHES');
3352 arp_message.set_line('WSH_CUST_MERGE.PICKING_BATCHES()+' || getTimeStamp);
3353 END IF;
3354 IF process_mode='LOCK' THEN
3356 NULL;
3357 ELSE
3358 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
3359 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','WSH_PICKING_BATCHES',FALSE);
3360 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
3361 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
3362
3363 open merged_records;
3364 LOOP
3365 FETCH merged_records BULK COLLECT INTO
3366 MERGE_HEADER_ID_LIST
3367 , PRIMARY_KEY_ID_LIST
3368 , NUM_COL1_ORIG_LIST
3369 limit 1000;
3370 IF merged_records%NOTFOUND THEN
3371 l_last_fetch := TRUE;
3372 END IF;
3373 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
3374 exit;
3375 END IF;
3376 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
3377 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
3378 END LOOP;
3379 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3380 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
3381 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
3382 MERGE_LOG_ID,
3383 TABLE_NAME,
3384 MERGE_HEADER_ID,
3385 PRIMARY_KEY_ID,
3386 NUM_COL1_ORIG,
3387 NUM_COL1_NEW,
3388 ACTION_FLAG,
3389 REQUEST_ID,
3390 CREATED_BY,
3391 CREATION_DATE,
3392 LAST_UPDATE_LOGIN,
3393 LAST_UPDATE_DATE,
3394 LAST_UPDATED_BY
3395 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
3396 'WSH_PICKING_BATCHES',
3397 MERGE_HEADER_ID_LIST(I),
3398 PRIMARY_KEY_ID_LIST(I),
3399 NUM_COL1_ORIG_LIST(I),
3400 NUM_COL1_NEW_LIST(I),
3401 'U',
3402 req_id,
3403 hz_utility_pub.CREATED_BY,
3404 hz_utility_pub.CREATION_DATE,
3405 hz_utility_pub.LAST_UPDATE_LOGIN,
3406 hz_utility_pub.LAST_UPDATE_DATE,
3407 hz_utility_pub.LAST_UPDATED_BY
3408 );
3409
3410 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
3411 UPDATE WSH_PICKING_BATCHES yt SET
3412 customer_id=NUM_COL1_NEW_LIST(I)
3413 , LAST_UPDATE_DATE=SYSDATE
3414 , last_updated_by=arp_standard.profile.user_id
3415 , last_update_login=arp_standard.profile.last_update_login
3416 , REQUEST_ID=req_id
3417 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
3418 , PROGRAM_ID=arp_standard.profile.program_id
3419 , PROGRAM_UPDATE_DATE=SYSDATE
3420 WHERE BATCH_ID=PRIMARY_KEY_ID_LIST(I)
3421 ;
3422 l_count := l_count + SQL%ROWCOUNT;
3423 IF l_last_fetch THEN
3424 EXIT;
3425 END IF;
3426 END LOOP;
3427
3428 arp_message.set_name('AR','AR_ROWS_UPDATED');
3429 arp_message.set_token('NUM_ROWS',to_char(l_count));
3430 END IF;
3431 IF l_debug_on THEN
3432 arp_message.set_line('WSH_CUST_MERGE.PICKING_BATCHES()+' || getTimeStamp);
3433 END IF;
3434 EXCEPTION
3435 WHEN OTHERS THEN
3436 arp_message.set_line( 'WSH_MERGE_PICKING_BATCHES');
3437 RAISE;
3438 END Picking_batches;
3439 --
3440 --
3441 -- Procedure : Calendar_Assignments
3442 -- Description : New code to merge customer and site information in
3443 -- WSH_CALENDAR_ASSIGNMENTS
3444 -- Usage : Called by WSH_CUST_MERGE.Merge
3445
3446 Procedure Calendar_Assignments
3447 (
3448 Req_Id IN NUMBER,
3449 Set_Num IN NUMBER,
3450 Process_Mode IN VARCHAR2
3451 )
3452 IS
3453 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF
3454 RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
3455 INDEX BY BINARY_INTEGER;
3456 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
3457
3458 TYPE CALENDAR_ASSIGNMENT_ID_LIST_TY IS TABLE OF
3459 WSH_CALENDAR_ASSIGNMENTS.CALENDAR_ASSIGNMENT_ID%TYPE
3460 INDEX BY BINARY_INTEGER;
3461 PRIMARY_KEY_ID_LIST CALENDAR_ASSIGNMENT_ID_LIST_TY;
3462
3463 TYPE customer_id_LIST_TYPE IS TABLE OF
3464 WSH_CALENDAR_ASSIGNMENTS.customer_id%TYPE
3465 INDEX BY BINARY_INTEGER;
3466 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
3467 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
3468
3469 TYPE customer_site_use_id_LIST_TYPE IS TABLE OF
3470 WSH_CALENDAR_ASSIGNMENTS.customer_site_use_id%TYPE
3471 INDEX BY BINARY_INTEGER;
3472 NUM_COL2_ORIG_LIST customer_site_use_id_LIST_TYPE;
3473 NUM_COL2_NEW_LIST customer_site_use_id_LIST_TYPE;
3474
3475 l_profile_val VARCHAR2(30);
3476 CURSOR merged_records IS
3477 SELECT distinct CUSTOMER_MERGE_HEADER_ID
3478 ,CALENDAR_ASSIGNMENT_ID
3479 ,yt.customer_id
3480 ,yt.customer_site_use_id
3481 FROM WSH_CALENDAR_ASSIGNMENTS yt, ra_customer_merges m
3482 WHERE (
3483 yt.customer_id = m.DUPLICATE_ID
3484 OR yt.customer_site_use_id = m.DUPLICATE_SITE_ID
3485 ) AND m.process_flag = 'N'
3486 AND m.request_id = req_id
3490 --
3487 AND m.set_number = set_num;
3488 l_last_fetch BOOLEAN := FALSE;
3489 l_count NUMBER :=0;
3491 l_debug_on BOOLEAN;
3492 --
3493 BEGIN
3494 IF l_debug_on THEN
3495 arp_message.set_line('WSH_CUST_MERGE.CALENDAR_ASSIGNMENTS()+' || getTimeStamp);
3496 END IF;
3497
3498 IF process_mode='LOCK' THEN
3499 setARMessageLockTable('WSH_CALENDAR_ASSIGNMENTS');
3500 NULL;
3501 ELSE
3502 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
3503 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','WSH_CALENDAR_ASSIGNMENTS',FALSE);
3504 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
3505 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
3506
3507 open merged_records;
3508 LOOP
3509 FETCH merged_records BULK COLLECT INTO
3510 MERGE_HEADER_ID_LIST
3511 , PRIMARY_KEY_ID_LIST
3512 , NUM_COL1_ORIG_LIST
3513 , NUM_COL2_ORIG_LIST
3514 limit 1000;
3515 IF merged_records%NOTFOUND THEN
3516 l_last_fetch := TRUE;
3517 END IF;
3518 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
3519 exit;
3520 END IF;
3521 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
3522 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
3523 NUM_COL2_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_SITE_USE(NUM_COL2_ORIG_LIST(I));
3524 END LOOP;
3525 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3526 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
3527 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
3528 MERGE_LOG_ID,
3529 TABLE_NAME,
3530 MERGE_HEADER_ID,
3531 PRIMARY_KEY_ID,
3532 NUM_COL1_ORIG,
3533 NUM_COL1_NEW,
3534 NUM_COL2_ORIG,
3535 NUM_COL2_NEW,
3536 ACTION_FLAG,
3537 REQUEST_ID,
3538 CREATED_BY,
3539 CREATION_DATE,
3540 LAST_UPDATE_LOGIN,
3541 LAST_UPDATE_DATE,
3542 LAST_UPDATED_BY
3543 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
3544 'WSH_CALENDAR_ASSIGNMENTS',
3545 MERGE_HEADER_ID_LIST(I),
3546 PRIMARY_KEY_ID_LIST(I),
3547 NUM_COL1_ORIG_LIST(I),
3548 NUM_COL1_NEW_LIST(I),
3549 NUM_COL2_ORIG_LIST(I),
3550 NUM_COL2_NEW_LIST(I),
3551 'U',
3552 req_id,
3553 hz_utility_pub.CREATED_BY,
3554 hz_utility_pub.CREATION_DATE,
3555 hz_utility_pub.LAST_UPDATE_LOGIN,
3556 hz_utility_pub.LAST_UPDATE_DATE,
3557 hz_utility_pub.LAST_UPDATED_BY
3558 );
3559
3560 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
3561 UPDATE WSH_CALENDAR_ASSIGNMENTS yt SET
3562 customer_id=NUM_COL1_NEW_LIST(I)
3563 ,customer_site_use_id=NUM_COL2_NEW_LIST(I)
3564 , LAST_UPDATE_DATE=SYSDATE
3565 , last_updated_by=arp_standard.profile.user_id
3566 , last_update_login=arp_standard.profile.last_update_login
3567 , REQUEST_ID=req_id
3568 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
3569 , PROGRAM_ID=arp_standard.profile.program_id
3570 , PROGRAM_UPDATE_DATE=SYSDATE
3571 WHERE CALENDAR_ASSIGNMENT_ID=PRIMARY_KEY_ID_LIST(I)
3572 ;
3573 l_count := l_count + SQL%ROWCOUNT;
3574 IF l_last_fetch THEN
3575 EXIT;
3576 END IF;
3577 END LOOP;
3578
3579 arp_message.set_name('AR','AR_ROWS_UPDATED');
3580 arp_message.set_token('NUM_ROWS',to_char(l_count));
3581 END IF;
3582 IF l_debug_on THEN
3583 arp_message.set_line('WSH_CUST_MERGE.CALENDAR_ASSIGNMENTS()+' || getTimeStamp);
3584 END IF;
3585 EXCEPTION
3586 WHEN OTHERS THEN
3587 arp_message.set_line( 'WSH_MERGE_CALENDAR_ASSIGNMENTS');
3588 RAISE;
3589 END Calendar_Assignments;
3590
3591 -----------------------------------------------------------------------------------------
3592 --
3593 --
3594 -- Procedure : Picking_rules
3595 -- Description : New code to merge customer and site information in
3596 -- WSH_PICKING_RULES
3597 -- Usage : Called by WSH_CUST_MERGE.Merge
3598 --
3599 -----------------------------------------------------------------------------------------
3600
3601 Procedure Picking_rules ( Req_Id IN NUMBER,
3602 Set_Num IN NUMBER,
3603 Process_Mode IN VARCHAR2)
3604 IS
3605 TYPE MERGE_HEADER_ID_LIST_TYPE IS TABLE OF RA_CUSTOMER_MERGE_HEADERS.CUSTOMER_MERGE_HEADER_ID%TYPE
3606 INDEX BY BINARY_INTEGER;
3607 MERGE_HEADER_ID_LIST MERGE_HEADER_ID_LIST_TYPE;
3608
3609 TYPE PICKING_RULE_ID_LIST_TYPE IS TABLE OF WSH_PICKING_RULES.PICKING_RULE_ID%TYPE
3610 INDEX BY BINARY_INTEGER;
3611 PRIMARY_KEY_ID_LIST PICKING_RULE_ID_LIST_TYPE;
3612
3613 TYPE customer_id_LIST_TYPE IS TABLE OF WSH_PICKING_RULES.customer_id%TYPE
3614 INDEX BY BINARY_INTEGER;
3615 NUM_COL1_ORIG_LIST customer_id_LIST_TYPE;
3616 NUM_COL1_NEW_LIST customer_id_LIST_TYPE;
3617
3618 l_profile_val VARCHAR2(30);
3619 CURSOR merged_records IS
3620 SELECT distinct CUSTOMER_MERGE_HEADER_ID
3624 WHERE (
3621 ,PICKING_RULE_ID
3622 ,yt.customer_id
3623 FROM WSH_PICKING_RULES yt, ra_customer_merges m
3625 yt.customer_id = m.DUPLICATE_ID
3626 ) AND m.process_flag = 'N'
3627 AND m.request_id = req_id
3628 AND m.set_number = set_num;
3629 l_last_fetch BOOLEAN := FALSE;
3630 l_count NUMBER :=0;
3631 --
3632 l_debug_on BOOLEAN;
3633 --
3634 BEGIN
3635 --
3636 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3637 --
3638 IF l_debug_on IS NULL
3639 THEN
3640 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3641 END IF;
3642 --
3643 IF l_debug_on THEN
3644 arp_message.set_line('WSH_CUST_MERGE.PICKING_RULES()+' || getTimeStamp);
3645 END IF;
3646
3647 IF process_mode='LOCK' THEN
3648 setARMessageLockTable('WSH_PICKING_RULES');
3649 NULL;
3650 ELSE
3651 ARP_MESSAGE.SET_NAME('AR','AR_UPDATING_TABLE');
3652 ARP_MESSAGE.SET_TOKEN('TABLE_NAME','WSH_PICKING_RULES',FALSE);
3653 HZ_ACCT_MERGE_UTIL.load_set(set_num, req_id);
3654 l_profile_val := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
3655
3656 open merged_records;
3657 LOOP
3658 FETCH merged_records BULK COLLECT INTO
3659 MERGE_HEADER_ID_LIST
3660 , PRIMARY_KEY_ID_LIST
3661 , NUM_COL1_ORIG_LIST
3662 limit 1000;
3663 IF merged_records%NOTFOUND THEN
3664 l_last_fetch := TRUE;
3665 END IF;
3666 IF MERGE_HEADER_ID_LIST.COUNT = 0 and l_last_fetch then
3667 exit;
3668 END IF;
3669 FOR I in 1..MERGE_HEADER_ID_LIST.COUNT LOOP
3670 NUM_COL1_NEW_LIST(I) := HZ_ACCT_MERGE_UTIL.GETDUP_ACCOUNT(NUM_COL1_ORIG_LIST(I));
3671 END LOOP;
3672 IF l_profile_val IS NOT NULL AND l_profile_val = 'Y' THEN
3673 FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
3674 INSERT INTO HZ_CUSTOMER_MERGE_LOG (
3675 MERGE_LOG_ID,
3676 TABLE_NAME,
3677 MERGE_HEADER_ID,
3678 PRIMARY_KEY_ID,
3679 NUM_COL1_ORIG,
3680 NUM_COL1_NEW,
3681 ACTION_FLAG,
3682 REQUEST_ID,
3683 CREATED_BY,
3684 CREATION_DATE,
3685 LAST_UPDATE_LOGIN,
3686 LAST_UPDATE_DATE,
3687 LAST_UPDATED_BY
3688 ) VALUES ( HZ_CUSTOMER_MERGE_LOG_s.nextval,
3689 'WSH_PICKING_RULES',
3690 MERGE_HEADER_ID_LIST(I),
3691 PRIMARY_KEY_ID_LIST(I),
3692 NUM_COL1_ORIG_LIST(I),
3693 NUM_COL1_NEW_LIST(I),
3694 'U',
3695 req_id,
3696 hz_utility_pub.CREATED_BY,
3697 hz_utility_pub.CREATION_DATE,
3698 hz_utility_pub.LAST_UPDATE_LOGIN,
3699 hz_utility_pub.LAST_UPDATE_DATE,
3700 hz_utility_pub.LAST_UPDATED_BY
3701 );
3702
3703 END IF; FORALL I in 1..MERGE_HEADER_ID_LIST.COUNT
3704 UPDATE WSH_PICKING_RULES yt SET
3705 customer_id=NUM_COL1_NEW_LIST(I)
3706 , LAST_UPDATE_DATE=SYSDATE
3707 , last_updated_by=arp_standard.profile.user_id
3708 , last_update_login=arp_standard.profile.last_update_login
3709 , REQUEST_ID=req_id
3710 , PROGRAM_APPLICATION_ID=arp_standard.profile.program_application_id
3711 , PROGRAM_ID=arp_standard.profile.program_id
3712 , PROGRAM_UPDATE_DATE=SYSDATE
3713 WHERE PICKING_RULE_ID=PRIMARY_KEY_ID_LIST(I)
3714 ;
3715 l_count := l_count + SQL%ROWCOUNT;
3716
3717 IF l_last_fetch THEN
3718 EXIT;
3719 END IF;
3720 END LOOP;
3721
3722 arp_message.set_name('AR','AR_ROWS_UPDATED');
3723 arp_message.set_token('NUM_ROWS',to_char(l_count));
3724 END IF;
3725 IF l_debug_on THEN
3726 arp_message.set_line('WSH_CUST_MERGE.PICKING_RULES()-' || getTimeStamp);
3727 END IF;
3728 EXCEPTION
3729 WHEN OTHERS THEN
3730 arp_message.set_line( 'WSH_MERGE_PICKING_RULES');
3731 RAISE;
3732 END Picking_rules;
3733
3734
3735 -----------------------------------------------------------------------------------------
3736 --
3737 -- PROCEDURE: CHECK_WMS_DETIALS
3738 -- PURPOSE: To check whether the merge involves wms organisations in which
3739 -- the merge will be filed
3740 --
3741 -----------------------------------------------------------------------------------------
3742 Procedure Check_WMS_Details ( Req_Id IN NUMBER,
3743 Set_Num IN NUMBER,
3744 Process_Mode IN VARCHAR2 )
3745 IS
3746 /* Bug 7117470 According to vedo condition, Check_Wms_Details should raise
3747 exception only if Delivery_Detail is WMS Enabled and Staged ('Y') */
3748 CURSOR C1 IS
3749 SELECT 1 FROM DUAL
3750 WHERE EXISTS
3751 ( SELECT 'x'
3752 FROM wsh_delivery_details wdd,
3753 wsh_delivery_assignments wda,
3754 ra_customer_merges rcm,
3755 mtl_parameters mtl
3756 WHERE mtl.wms_enabled_flag = 'Y'
3757 AND mtl.organization_id = wdd.organization_id
3758 AND wda.parent_delivery_detail_id IS NOT NULL
3759 -- AND wda.delivery_id IS NULL
3760 AND wda.delivery_detail_id = wdd.delivery_detail_id
3761 AND wdd.customer_id = rcm.duplicate_id
3762 AND wdd.ship_to_location_id = WSH_UTIL_CORE.Cust_Site_To_Location( duplicate_site_id )
3763 AND wdd.container_flag = 'N'
3764 -- AND wdd.released_status IN ( 'R', 'N', 'X', 'Y', 'S', 'B' )
3765 AND wdd.released_status = 'Y'
3766 AND rcm.customer_site_code = 'SHIP_TO'
3767 AND rcm.process_flag = 'N'
3768 AND rcm.request_id = Req_Id
3769 AND rcm.set_number = Set_Num );
3770
3771 l_count NUMBER;
3772 WMS_Exception EXCEPTION;
3773 --
3774 l_debug_on BOOLEAN;
3775 --
3776 BEGIN
3777 --
3778 l_debug_on := WSH_DEBUG_INTERFACE.g_debug;
3779 --
3780 IF l_debug_on IS NULL THEN
3781 l_debug_on := WSH_DEBUG_SV.is_debug_enabled;
3782 END IF;
3783 --
3784 IF l_debug_on THEN
3785 arp_message.set_line('WSH_CUST_MERGE.Check_WMS_Details()+' || getTimeStamp );
3786 END IF;
3787 --
3788
3789 OPEN C1;
3790 FETCH C1 INTO l_count;
3791 IF ( C1%FOUND ) THEN
3792 --
3793 IF ( l_debug_on ) THEN
3794 ARP_MESSAGE.Set_Error('There exists WMS records in shipping which are Staged');
3795 END IF;
3796 --
3797 CLOSE C1;
3798 RAISE WMS_Exception;
3799 END IF;
3800 CLOSE C1;
3801
3802 --
3803 IF l_debug_on THEN
3804 arp_message.set_line('WSH_CUST_MERGE.Check_WMS_Details()+' || getTimeStamp );
3805 END IF;
3806 --
3807 EXCEPTION
3808 WHEN WMS_Exception THEN
3809 --
3810 IF ( l_debug_on ) THEN
3811 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Check_WMS_Details - WMS_Exception');
3812 END IF;
3813 --
3814 IF ( C1%ISOPEN ) THEN
3815 CLOSE C1;
3816 END IF;
3817 --
3818 RAISE;
3819 --
3820 WHEN OTHERS THEN
3821 --
3822 IF ( l_debug_on ) THEN
3823 ARP_MESSAGE.Set_Error('WSH_CUST_MERGE.Check_WMS_Details()+ Others - ' || getTimeStamp );
3824 ARP_MESSAGE.Set_Error('Error Mesg : ' || sqlerrm );
3825 END IF;
3826 --
3827 IF ( C1%ISOPEN ) THEN
3828 CLOSE C1;
3829 END IF;
3830 --
3831 RAISE;
3832 --
3833 END Check_WMS_Details;
3834
3835 -----------------------------------------------------------------------------------------
3836 --
3837 --
3838 -- Procedure: Merge
3839 -- Description: New code to merge customer and site information
3840 -- throughout WSH. This is the main procedure for
3841 -- customer merge for WSH, which calls all other internal
3842 -- procedures for customer merge based on the functional areas.
3843 -- Usage: Called by TCA's Customer Merge.
3844 --
3845 -----------------------------------------------------------------------------------------
3846
3847 PROCEDURE Merge(Req_Id IN NUMBER, Set_Num IN NUMBER, Process_Mode IN VARCHAR2 )
3848 IS
3849 l_duplicateIdTab g_number_tbl_type;
3850 l_customerIdTab g_number_tbl_type;
3851 l_duplicateSiteIdTab g_number_tbl_type;
3852 l_customerSiteIdTab g_number_tbl_type;
3853 l_customerMergeHeaderIdTab g_number_tbl_type;
3854
3855 BEGIN
3856 /* Calls to other internal procedures for customer Merge */
3857 arp_message.set_line('WSH_CUST_MERGE.Merge()+' || getTimeStamp);
3858 --
3859 arp_message.set_line('Req_Id,Set_Num,Process_Mode:' || Req_Id||','||Set_Num||','||Process_Mode);
3860
3861 --
3862 -- For inserting record into HZ Log Table based on profile option value
3863 --
3864 IF ( G_PROFILE_VAL IS NULL ) THEN
3865 G_PROFILE_VAL := FND_PROFILE.VALUE('HZ_AUDIT_ACCT_MERGE');
3866 END IF;
3867
3868 --
3869 -- Check whether FTE is Installed
3870 --
3871 IF ( G_FTE_INSTALLED IS NULL ) THEN
3872 G_FTE_INSTALLED := WSH_UTIL_CORE.Fte_Is_Installed;
3873 END IF;
3874
3875 WSH_CUST_MERGE.Check_WMS_Details( Req_Id, Set_Num, Process_Mode ); -- ADDED
3876 WSH_CUST_MERGE.Delivery_Details( Req_Id, Set_Num, Process_Mode );
3877 -- WSH_CUST_MERGE.Deliveries( Req_Id, Set_Num, Process_Mode );
3878 WSH_CUST_MERGE.Picking_Rules( Req_Id, Set_Num, Process_Mode ); -- NOCHANGE
3879 WSH_CUST_MERGE.Picking_Batches( Req_Id, Set_Num, Process_Mode ); -- NOCHANGE
3880 WSH_CUST_MERGE.Calendar_Assignments(Req_Id, Set_Num, Process_Mode ); -- NOCHANGE
3881
3882 arp_message.set_line('WSH_CUST_MERGE.Merge()-' || getTimeStamp);
3883
3884 EXCEPTION
3885 WHEN OTHERS THEN
3886 arp_message.set_error('WSH_CUST_MERGE.Merge');
3887 RAISE;
3888
3889 END Merge;
3890
3891 END WSH_CUST_MERGE;