[Home] [Help]
PACKAGE BODY: APPS.OE_BULK_HOLDS_PVT
Source
1 PACKAGE BODY OE_Bulk_Holds_PVT AS
2 /* $Header: OEBVHLDB.pls 120.1.12010000.7 2008/11/24 04:31:37 smusanna ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='OE_Bulk_Holds_PVT';
5
6
7 /****************************************************************************
8 Valid Entity Combination
9 ^^^^^^^^^^^^^^^^^^^^^^^^
10 > 1. Customer - Header Level
11 > 2. item - Line Level
12 > 3. ship_to - Line Level
13 > 4. bill to - Line Level
14 > 5. warehouse - Line Level
15 > 6. Item - Customer - Line Level
16 > 7. Item - Customer Ship to Site - Line Level
17 > 8. Item - Customer Bill to Site - Line Level
18 > 9. Item - Warehouse - Line Level
19 > 10. Warehouse - Customer - Line Level
20 > 11. Warehouse - Customer Ship to Site - Line Level
21 > 12. Warehouse - Customer Bill to Site - Line Level
22
23 ***************************************************************************/
24 PROCEDURE Extend_Holds_Tbl;
25
26 PROCEDURE Extend_Hold_Source_Rec;
27
28 PROCEDURE Initialize_Holds_Tbl IS
29 BEGIN
30
31 -- Clear the Globals.
32 g_hold_header_id.DELETE;
33 g_hold_line_id.DELETE;
34 g_hold_Source_Id.DELETE;
35 g_hold_ship_set.DELETE;
36 g_hold_arrival_set.DELETE;
37 g_hold_top_model_line_id.DELETE;
38 g_hold_activity_name.DELETE;
39
40 g_hold_source_rec.HOLD_ID.DELETE;
41 g_hold_source_rec.HOLD_SOURCE_ID.DELETE;
42 g_hold_source_rec.HOLD_ENTITY_CODE.DELETE;
43 g_hold_source_rec.HOLD_ENTITY_ID.DELETE;
44
45 EXCEPTION
46 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
47 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
48 WHEN OTHERS THEN
49 OE_BULK_MSG_PUB.Add_Exc_Msg
50 ( G_PKG_NAME
51 , 'Initialize_Holds_Tbl'
52 );
53 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
54 END Initialize_Holds_Tbl;
55
56 PROCEDURE Load_Hold_Sources
57 IS
58 CURSOR C_Hold_Sources IS
59 SELECT HS.HOLD_SOURCE_ID,
60 HS.HOLD_ENTITY_ID,
61 HS.HOLD_ENTITY_CODE,
62 HS.HOLD_ENTITY_ID2,
63 HS.HOLD_ENTITY_CODE2,
64 HLD.ACTIVITY_NAME
65 FROM OE_HOLD_SOURCES HS,
66 OE_HOLD_DEFINITIONS HLD
67 WHERE --ER#7479609 HS.HOLD_ENTITY_CODE IN ('C','I','B','W','S')
68 HS.HOLD_ENTITY_CODE IN ('C','I','B','D','W','S','PL','OT','CD','SC','P','SM') --ER#7479609
69 AND ROUND( NVL(HS.HOLD_UNTIL_DATE, SYSDATE ) ) >=
70 ROUND( SYSDATE )
71 AND HS.RELEASED_FLAG = 'N'
72 AND HLD.HOLD_ID = HS.HOLD_ID
73 AND SYSDATE
74 BETWEEN NVL( HLD.START_DATE_ACTIVE, SYSDATE )
75 AND NVL( HLD.END_DATE_ACTIVE, SYSDATE )
76 ORDER BY HS.HOLD_ENTITY_CODE,HS.HOLD_ENTITY_CODE2,HS.HOLD_ENTITY_ID,
77 HS.HOLD_ENTITY_ID2;
78
79 T_HOLD_SOURCE_ID OE_WSH_BULK_GRP.T_NUM := OE_WSH_BULK_GRP.T_NUM();
80 --ER#7479609 T_HOLD_ENTITY_ID OE_WSH_BULK_GRP.T_NUM;
81 T_HOLD_ENTITY_ID OE_WSH_BULK_GRP.T_V50 := OE_WSH_BULK_GRP.T_V50(); --ER#7479609
82 T_HOLD_ENTITY_CODE OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30();
83 --ER#7479609 T_HOLD_ENTITY_ID2 OE_WSH_BULK_GRP.T_NUM;
84 T_HOLD_ENTITY_ID2 OE_WSH_BULK_GRP.T_V50 := OE_WSH_BULK_GRP.T_V50(); --ER#7479609
85 T_HOLD_ENTITY_CODE2 OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30();
86 T_HOLD_ACTIVITY_NAME OE_WSH_BULK_GRP.T_V30 := OE_WSH_BULK_GRP.T_V30();
87
88 l_count BINARY_INTEGER;
89 BEGIN
90
91 oe_debug_pub.add('Entering OE_Bulk_Holds_PVT.Load_Hold_Sources'); --ER#7479609 debug
92 -- Clear the Globals.
93 g_hold_header_id.DELETE;
94 g_hold_line_id.DELETE;
95 g_hold_Source_Id.DELETE;
96 g_hold_ship_set.DELETE;
97 g_hold_arrival_set.DELETE;
98 g_hold_top_model_line_id.DELETE;
99 g_hold_activity_name.DELETE;
100
101 Customer_Pointer.DELETE;
102 Item_Pointer.DELETE;
103 ship_to_Pointer.DELETE;
104 bill_to_Pointer.DELETE;
105 Warehouse_Pointer.DELETE;
106 --ER#7479609 start
107 PriceList_Pointer.DELETE;
108 OrderType_Pointer.DELETE;
109 CreationDate_Pointer.DELETE;
110 SalesChannel_Pointer.DELETE;
111 PaymentType_Pointer.DELETE;
112 ShipMethod_Pointer.DELETE;
113 deliver_to_Pointer.DELETE;
114 --ER#7479609 end
115
116 Item_Customer_Pointer.DELETE;
117 Item_shipto_Pointer.DELETE;
118 Item_Billto_Pointer.DELETE;
119 Item_Warehouse_Pointer.DELETE;
120 --ER#7479609 start
121 Item_ShipMethod_Pointer.DELETE;
122 Item_Deliverto_Pointer.DELETE;
123 Item_PriceList_Pointer.DELETE;
124 --ER#7479609 Item_Project_Pointer.DELETE;
125 Item_SourceType_Pointer.DELETE;
126 Item_LineType_Pointer.DELETE;
127 --ER#7479609 end
128
129 Warehouse_Customer_Pointer.DELETE;
130 Warehouse_shipto_Pointer.DELETE;
131 Warehouse_Billto_Pointer.DELETE;
132 --ER#7479609 start
133 Warehouse_LineType_Pointer.DELETE;
134 Warehouse_ShipMethod_Pointer.DELETE;
135 Warehouse_Deliverto_Pointer.DELETE;
136 Warehouse_SourceType_Pointer.DELETE;
137 --ER#7479609 end
138
139 --ER#7479609 start
140 Cust_SourceType_Pointer.DELETE;
141 Cust_Billto_Pointer.DELETE;
142 Cust_Shipto_Pointer.DELETE;
143 Cust_Deliverto_Pointer.DELETE;
144 Cust_PriceList_Pointer.DELETE;
145 Cust_LineType_Pointer.DELETE;
146 Cust_PayTerm_Pointer.DELETE;
147 Cust_OrderType_Pointer.DELETE;
148 Cust_PaymentType_Pointer.DELETE;
149 Cust_Curr_Pointer.DELETE;
150 Cust_SalesChannel_Pointer.DELETE;
151
152
153 --ER#7479609 Project_Task_Pointer.DELETE;
154
155 PriceList_Curr_Pointer.DELETE;
156
157 OrderType_Curr_Pointer.DELETE;
158 OrderType_LineType_Pointer.DELETE;
159
160 CreDate_CreBy_Pointer.DELETE;
161 --ER#7479609 end
162
163 Customer_Hold.DELETE;
164 Item_Hold.DELETE;
165 ship_to_Hold.DELETE;
166 bill_to_Hold.DELETE;
167 Warehouse_Hold.DELETE;
168 --ER#7479609 start
169 PriceList_Hold.DELETE;
170 OrderType_Hold.DELETE;
171 CreationDate_Hold.DELETE;
172 SalesChannel_Hold.DELETE;
173 PaymentType_Hold.DELETE;
174 ShipMethod_Hold.DELETE;
175 deliver_to_Hold.DELETE;
176 --ER#7479609 end
177
178 Item_Customer_Hold.DELETE;
179 Item_shipto_Hold.DELETE;
180 Item_Billto_Hold.DELETE;
181 Item_Warehouse_Hold.DELETE;
182 --ER#7479609 start
183 Item_ShipMethod_Hold.DELETE;
184 Item_Deliverto_Hold.DELETE;
185 Item_PriceList_Hold.DELETE;
186 Item_SourceType_Hold.DELETE;
187 Item_LineType_Hold.DELETE;
188 --ER#7479609 end
189
190 Warehouse_Customer_Hold.DELETE;
191 Warehouse_shipto_Hold.DELETE;
192 Warehouse_Billto_Hold.DELETE;
193 --ER#7479609 start
194 Warehouse_LineType_Hold.DELETE;
195 Warehouse_ShipMethod_Hold.DELETE;
196 Warehouse_Deliverto_Hold.DELETE;
197 Warehouse_SourceType_Hold.DELETE;
198 --ER#7479609 end
199
200 --ER#7479609 start
201 Cust_SourceType_Hold.DELETE;
202 Cust_Billto_Hold.DELETE;
203 Cust_Shipto_Hold.DELETE;
204 Cust_Deliverto_Hold.DELETE;
205 Cust_PriceList_Hold.DELETE;
206 Cust_LineType_Hold.DELETE;
207 Cust_PayTerm_Hold.DELETE;
208 Cust_OrderType_Hold.DELETE;
209 Cust_PaymentType_Hold.DELETE;
210 Cust_Curr_Hold.DELETE;
211 Cust_SalesChannel_Hold.DELETE;
212
213
214
215 PriceList_Curr_Hold.DELETE;
216
217 OrderType_Curr_Hold.DELETE;
218 OrderType_LineType_Hold.DELETE;
219
220 CreDate_CreBy_Hold.DELETE;
221 --ER#7479609 end
222
223 oe_debug_pub.add('Before opening C_Hold_Sources'); --ER#7479609 debug
224 -- Load the Hold Sources into Globals.
225 OPEN C_Hold_Sources;
226 FETCH C_Hold_Sources BULK COLLECT INTO
227 T_HOLD_SOURCE_ID,
228 T_HOLD_ENTITY_ID,
229 T_HOLD_ENTITY_CODE,
230 T_HOLD_ENTITY_ID2,
231 T_HOLD_ENTITY_CODE2,
232 T_HOLD_ACTIVITY_NAME;
233 CLOSE C_Hold_Sources;
234 oe_debug_pub.add('After Closing C_Hold_Sources:'||T_HOLD_SOURCE_ID.COUNT); --ER#7479609 debug
235
236 FOR i IN 1..T_HOLD_SOURCE_ID.COUNT LOOP
237
238 -- Load Customer Hold Source
239 IF T_HOLD_ENTITY_CODE(i) = 'C' AND T_HOLD_ENTITY_ID2(i) IS NULL
240 THEN
241 l_count := Customer_Hold.COUNT;
242 Customer_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
243 Customer_Hold(l_count+1).Entity_Id2 := NULL;
244 Customer_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
245 Customer_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
246
247 IF NOT Customer_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
248 Customer_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
249 END IF;
250
251 END IF;
252
253
254 -- Load Item Hold Source
255 IF T_HOLD_ENTITY_CODE(i) = 'I' AND T_HOLD_ENTITY_ID2(i) IS NULL
256 THEN
257 l_count := Item_Hold.COUNT;
258 Item_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
259 Item_Hold(l_count+1).Entity_Id2 := NULL;
260 Item_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
261 Item_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
262
263 IF NOT Item_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
264 Item_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
265 END IF;
266 END IF;
267
268 -- Load Ship_To Hold Source
269 IF T_HOLD_ENTITY_CODE(i) = 'S' AND T_HOLD_ENTITY_ID2(i) IS NULL
270 THEN
271 l_count := Ship_to_Hold.COUNT;
272 Ship_to_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
273 Ship_to_Hold(l_count+1).Entity_Id2 := NULL;
274 Ship_to_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
275 Ship_to_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
276
277 IF NOT Ship_to_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
278 Ship_to_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
279 END IF;
280 END IF;
281
282 -- Load Bill_To Hold Source
283 IF T_HOLD_ENTITY_CODE(i) = 'B' AND T_HOLD_ENTITY_ID2(i) IS NULL
284 THEN
285 l_count := Bill_to_Hold.COUNT;
286 Bill_to_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
287 Bill_to_Hold(l_count+1).Entity_Id2 := NULL;
288 Bill_to_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
289 Bill_to_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
290
291 IF NOT Bill_to_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
292 Bill_to_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
293 END IF;
294 END IF;
295
296 -- Load Warehouse Hold Source
297 IF T_HOLD_ENTITY_CODE(i) = 'W' AND T_HOLD_ENTITY_ID2(i) IS NULL
298 THEN
299 l_count := Warehouse_Hold.COUNT;
300 Warehouse_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
301 Warehouse_Hold(l_count+1).Entity_Id2 := NULL;
302 Warehouse_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
303 Warehouse_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
304
305 IF NOT Warehouse_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
306 Warehouse_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
307 END IF;
308 END IF;
309
310
311
312 --ER#7479609 start
313
314 IF T_HOLD_ENTITY_CODE(i) = 'PL' AND T_HOLD_ENTITY_ID2(i) IS NULL
315 THEN
316 l_count := PriceList_Hold.COUNT;
317 PriceList_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
318 PriceList_Hold(l_count+1).Entity_Id2 := NULL;
319 PriceList_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
320 PriceList_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
321
322 IF NOT PriceList_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
323 PriceList_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
324 END IF;
325 END IF;
326
327 IF T_HOLD_ENTITY_CODE(i) = 'OT' AND T_HOLD_ENTITY_ID2(i) IS NULL
328 THEN
329 l_count := OrderType_Hold.COUNT;
330 OrderType_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
331 OrderType_Hold(l_count+1).Entity_Id2 := NULL;
332 OrderType_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
333 OrderType_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
334
335 IF NOT OrderType_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
336 OrderType_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
337 END IF;
338 END IF;
339
340 IF T_HOLD_ENTITY_CODE(i) = 'CD' AND T_HOLD_ENTITY_ID2(i) IS NULL
341 THEN
342 l_count := CreationDate_Hold.COUNT;
343 CreationDate_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
344 CreationDate_Hold(l_count+1).Entity_Id2 := NULL;
345 CreationDate_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
346 CreationDate_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
347
348 IF NOT CreationDate_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
349 CreationDate_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
350 END IF;
351 END IF;
352
353 IF T_HOLD_ENTITY_CODE(i) = 'SC' AND T_HOLD_ENTITY_ID2(i) IS NULL
354 THEN
355 l_count := SalesChannel_Hold.COUNT;
356 SalesChannel_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
357 SalesChannel_Hold(l_count+1).Entity_Id2 := NULL;
358 SalesChannel_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
359 SalesChannel_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
360
361 IF NOT SalesChannel_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
362 SalesChannel_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
363 END IF;
364 END IF;
365
366 oe_debug_pub.add('Before Loading payment type'); --ER#7479609 debug
367 IF T_HOLD_ENTITY_CODE(i) = 'P' AND T_HOLD_ENTITY_ID2(i) IS NULL
368 THEN
369 l_count := PaymentType_Hold.COUNT;
370 PaymentType_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
371 PaymentType_Hold(l_count+1).Entity_Id2 := NULL;
372 PaymentType_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
373 PaymentType_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
374
375 IF NOT PaymentType_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
376 PaymentType_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
377 END IF;
378
379 END IF;
380
381 IF T_HOLD_ENTITY_CODE(i) = 'SM' AND T_HOLD_ENTITY_ID2(i) IS NULL
382 THEN
383 l_count := ShipMethod_Hold.COUNT;
384 ShipMethod_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
385 ShipMethod_Hold(l_count+1).Entity_Id2 := NULL;
386 ShipMethod_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
387 ShipMethod_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
388
389 IF NOT ShipMethod_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
390 ShipMethod_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
391 END IF;
392 END IF;
393
394 IF T_HOLD_ENTITY_CODE(i) = 'D' AND T_HOLD_ENTITY_ID2(i) IS NULL
395 THEN
396 l_count := deliver_to_Hold.COUNT;
397 deliver_to_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
398 deliver_to_Hold(l_count+1).Entity_Id2 := NULL;
399 deliver_to_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
400 deliver_to_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
401
402 IF NOT deliver_to_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
403 deliver_to_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
404 END IF;
405 END IF;
406
407 --ER#7479609 end
408
409 --ER#7479609 start
410
411 IF T_HOLD_ENTITY_CODE(i) = 'C' AND T_HOLD_ENTITY_ID2(i) = 'ST'
412 THEN
413 l_count := Cust_SourceType_Hold.COUNT;
414 Cust_SourceType_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
415 Cust_SourceType_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
416 Cust_SourceType_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
417 Cust_SourceType_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
418
419 IF NOT Cust_SourceType_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
420 Cust_SourceType_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
421 END IF;
422
423 END IF;
424
425 IF T_HOLD_ENTITY_CODE(i) = 'C' AND T_HOLD_ENTITY_ID2(i) = 'B'
426 THEN
427 l_count := Cust_Billto_Hold.COUNT;
428 Cust_Billto_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
429 Cust_Billto_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
430 Cust_Billto_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
431 Cust_Billto_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
432
433 IF NOT Cust_Billto_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
434 Cust_Billto_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
435 END IF;
436
437 END IF;
438
439
440 IF T_HOLD_ENTITY_CODE(i) = 'C' AND T_HOLD_ENTITY_ID2(i) = 'S'
441 THEN
442 l_count := Cust_Shipto_Hold.COUNT;
443 Cust_Shipto_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
444 Cust_Shipto_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
445 Cust_Shipto_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
446 Cust_Shipto_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
447
448 IF NOT Cust_Shipto_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
449 Cust_Shipto_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
450 END IF;
451
452 END IF;
453
454
455 IF T_HOLD_ENTITY_CODE(i) = 'C' AND T_HOLD_ENTITY_ID2(i) = 'D'
456 THEN
457 l_count := Cust_Deliverto_Hold.COUNT;
458 Cust_Deliverto_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
459 Cust_Deliverto_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
460 Cust_Deliverto_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
461 Cust_Deliverto_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
462
463 IF NOT Cust_Deliverto_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
464 Cust_Deliverto_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
465 END IF;
466
467 END IF;
468
469
470 IF T_HOLD_ENTITY_CODE(i) = 'C' AND T_HOLD_ENTITY_ID2(i) = 'PL'
471 THEN
472 l_count := Cust_PriceList_Hold.COUNT;
473 Cust_PriceList_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
474 Cust_PriceList_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
475 Cust_PriceList_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
476 Cust_PriceList_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
477
478 IF NOT Cust_PriceList_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
479 Cust_PriceList_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
480 END IF;
481
482 END IF;
483
484
485 IF T_HOLD_ENTITY_CODE(i) = 'C' AND T_HOLD_ENTITY_ID2(i) = 'LT'
486 THEN
487 l_count := Cust_LineType_Hold.COUNT;
488 Cust_LineType_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
489 Cust_LineType_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
490 Cust_LineType_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
491 Cust_LineType_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
492
493 IF NOT Cust_LineType_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
494 Cust_LineType_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
495 END IF;
496
497 END IF;
498
499
500 IF T_HOLD_ENTITY_CODE(i) = 'C' AND T_HOLD_ENTITY_ID2(i) = 'PT'
501 THEN
502 l_count := Cust_PayTerm_Hold.COUNT;
503 Cust_PayTerm_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
504 Cust_PayTerm_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
505 Cust_PayTerm_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
506 Cust_PayTerm_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
507
508 IF NOT Cust_PayTerm_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
509 Cust_PayTerm_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
510 END IF;
511
512 END IF;
513
514
515 IF T_HOLD_ENTITY_CODE(i) = 'C' AND T_HOLD_ENTITY_ID2(i) = 'OT'
516 THEN
517 l_count := Cust_OrderType_Hold.COUNT;
518 Cust_OrderType_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
519 Cust_OrderType_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
520 Cust_OrderType_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
521 Cust_OrderType_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
522
523 IF NOT Cust_OrderType_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
524 Cust_OrderType_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
525 END IF;
526
527 END IF;
528
529
530 IF T_HOLD_ENTITY_CODE(i) = 'C' AND T_HOLD_ENTITY_ID2(i) = 'P'
531 THEN
532 l_count := Cust_PaymentType_Hold.COUNT;
533 Cust_PaymentType_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
534 Cust_PaymentType_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
535 Cust_PaymentType_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
536 Cust_PaymentType_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
537
538 IF NOT Cust_PaymentType_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
539 Cust_PaymentType_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
540 END IF;
541
542 END IF;
543
544
545 IF T_HOLD_ENTITY_CODE(i) = 'C' AND T_HOLD_ENTITY_ID2(i) = 'TC'
546 THEN
547 l_count := Cust_Curr_Hold.COUNT;
548 Cust_Curr_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
549 Cust_Curr_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
550 Cust_Curr_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
551 Cust_Curr_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
552
553 IF NOT Cust_Curr_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
554 Cust_Curr_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
555 END IF;
556
557 END IF;
558
559
560 IF T_HOLD_ENTITY_CODE(i) = 'C' AND T_HOLD_ENTITY_ID2(i) = 'SC'
561 THEN
562 l_count := Cust_SalesChannel_Hold.COUNT;
563 Cust_SalesChannel_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
564 Cust_SalesChannel_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
565 Cust_SalesChannel_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
566 Cust_SalesChannel_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
567
568 IF NOT Cust_SalesChannel_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
569 Cust_SalesChannel_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
570 END IF;
571
572 END IF;
573
574
575 --ER#7479609 end
576
577 -- Load Item Customer Hold Source
578 IF T_HOLD_ENTITY_CODE(i) = 'I' AND T_HOLD_ENTITY_CODE2(i) = 'C'
579 THEN
580 l_count := Item_Customer_Hold.COUNT;
581 Item_Customer_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
582 Item_Customer_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
583 Item_Customer_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
584 Item_Customer_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
585
586 IF NOT Item_Customer_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
587 Item_Customer_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
588 END IF;
589
590 END IF;
591
592 -- Load Item Ship_To Hold Source
593 IF T_HOLD_ENTITY_CODE(i) = 'I' AND T_HOLD_ENTITY_CODE2(i) = 'S'
594 THEN
595 l_count := Item_Shipto_Hold.COUNT;
596 Item_Shipto_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
597 Item_Shipto_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
598 Item_Shipto_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
599 Item_Shipto_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
600
601 IF NOT Item_Shipto_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
602 Item_Shipto_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
603 END IF;
604
605 END IF;
606
607 -- Load Item Bill_To Hold Source
608 IF T_HOLD_ENTITY_CODE(i) = 'I' AND T_HOLD_ENTITY_CODE2(i) = 'B'
609 THEN
610 l_count := Item_Billto_Hold.COUNT;
611 Item_Billto_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
612 Item_Billto_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
613 Item_Billto_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
614 Item_Billto_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
615
616 IF NOT Item_Billto_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
617 Item_Billto_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
618 END IF;
619
620 END IF;
621
622 -- Load Item Warehouse Hold Source
623 IF T_HOLD_ENTITY_CODE(i) = 'I' AND T_HOLD_ENTITY_CODE2(i) = 'W'
624 THEN
625 l_count := Item_Warehouse_Hold.COUNT;
626 Item_Warehouse_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
627 Item_Warehouse_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
628 Item_Warehouse_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
629 Item_Warehouse_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
630
631 IF NOT Item_Warehouse_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
632 Item_Warehouse_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
633 END IF;
634
635 END IF;
636
637 --ER#7479609 start
638 -- Load Item Linetype Hold Source
639 IF T_HOLD_ENTITY_CODE(i) = 'I' AND T_HOLD_ENTITY_CODE2(i) = 'LT'
640 THEN
641 l_count := Item_LineType_Hold.COUNT;
642 Item_LineType_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
643 Item_LineType_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
644 Item_LineType_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
645 Item_LineType_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
646
647 IF NOT Item_LineType_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
648 Item_LineType_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
649 END IF;
650 END IF;
651 --ER#7479609 end
652
653 --ER#7479609 start
654
655 IF T_HOLD_ENTITY_CODE(i) = 'I' AND T_HOLD_ENTITY_CODE2(i) = 'SM'
656 THEN
657 l_count := Item_ShipMethod_Hold.COUNT;
658 Item_ShipMethod_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
659 Item_ShipMethod_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
660 Item_ShipMethod_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
661 Item_ShipMethod_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
662
663 IF NOT Item_ShipMethod_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
664 Item_ShipMethod_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
665 END IF;
666
667 END IF;
668
669
670 IF T_HOLD_ENTITY_CODE(i) = 'I' AND T_HOLD_ENTITY_CODE2(i) = 'D'
671 THEN
672 l_count := Item_Warehouse_Hold.COUNT;
673 Item_Deliverto_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
674 Item_Deliverto_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
675 Item_Deliverto_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
676 Item_Deliverto_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
677
678 IF NOT Item_Deliverto_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
679 Item_Deliverto_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
680 END IF;
681
682 END IF;
683
684 IF T_HOLD_ENTITY_CODE(i) = 'I' AND T_HOLD_ENTITY_CODE2(i) = 'PL'
685 THEN
686 l_count := Item_PriceList_Hold.COUNT;
687 Item_PriceList_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
688 Item_PriceList_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
689 Item_PriceList_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
690 Item_PriceList_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
691
692 IF NOT Item_PriceList_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
693 Item_PriceList_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
694 END IF;
695
696 END IF;
697
698
699 IF T_HOLD_ENTITY_CODE(i) = 'I' AND T_HOLD_ENTITY_CODE2(i) = 'ST'
700 THEN
701 l_count := Item_SourceType_Hold.COUNT;
702 Item_SourceType_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
703 Item_SourceType_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
704 Item_SourceType_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
705 Item_SourceType_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
706
707 IF NOT Item_SourceType_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
708 Item_SourceType_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
709 END IF;
710
711 END IF;
712
713 --ER#7479609 end
714 oe_debug_pub.add('Before Loading Ware House'); --ER#7479609 debug
715 -- Load Warehouse Customer Hold Source
716 IF T_HOLD_ENTITY_CODE(i) = 'W' AND T_HOLD_ENTITY_CODE2(i) = 'C'
717 THEN
718 l_count := Warehouse_Customer_Hold.COUNT;
719 Warehouse_Customer_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
720 Warehouse_Customer_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
721 Warehouse_Customer_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
722 Warehouse_Customer_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
723
724 IF NOT Warehouse_Customer_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
725 Warehouse_Customer_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
726 END IF;
727
728 END IF;
729
730 -- Load Warehouse Shipto Hold Source
731 IF T_HOLD_ENTITY_CODE(i) = 'W' AND T_HOLD_ENTITY_CODE2(i) = 'S'
732 THEN
733 l_count := Warehouse_shipto_Hold.COUNT;
734 Warehouse_Shipto_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
735 Warehouse_Shipto_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
736 Warehouse_Shipto_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
737 Warehouse_Shipto_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
738
739 IF NOT Warehouse_Shipto_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
740 Warehouse_Shipto_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
741 END IF;
742
743 END IF;
744
745 -- Load Warehouse Billto Hold Source
746 IF T_HOLD_ENTITY_CODE(i) = 'W' AND T_HOLD_ENTITY_CODE2(i) = 'B'
747 THEN
748 l_count := Warehouse_Billto_hold.COUNT;
749 Warehouse_Billto_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
750 Warehouse_Billto_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
751 Warehouse_Billto_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
752 Warehouse_Billto_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
753
754 IF NOT Warehouse_Billto_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
755 Warehouse_Billto_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
756 END IF;
757
758 END IF;
759
760 --ER#7479609 start
761
762 IF T_HOLD_ENTITY_CODE(i) = 'W' AND T_HOLD_ENTITY_CODE2(i) = 'LT'
763 THEN
764 l_count := Warehouse_LineType_Hold.COUNT;
765 Warehouse_LineType_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
766 Warehouse_LineType_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
767 Warehouse_LineType_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
768 Warehouse_LineType_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
769
770 IF NOT Warehouse_LineType_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
771 Warehouse_LineType_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
772 END IF;
773
774 END IF;
775
776 IF T_HOLD_ENTITY_CODE(i) = 'W' AND T_HOLD_ENTITY_CODE2(i) = 'SM'
777 THEN
778 l_count := Warehouse_ShipMethod_Hold.COUNT;
779 Warehouse_ShipMethod_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
780 Warehouse_ShipMethod_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
781 Warehouse_ShipMethod_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
782 Warehouse_ShipMethod_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
783
784 IF NOT Warehouse_ShipMethod_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
785 Warehouse_ShipMethod_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
786 END IF;
787
788 END IF;
789
790 IF T_HOLD_ENTITY_CODE(i) = 'W' AND T_HOLD_ENTITY_CODE2(i) = 'D'
791 THEN
792 l_count := Warehouse_Deliverto_Hold.COUNT;
793 Warehouse_Deliverto_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
794 Warehouse_Deliverto_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
795 Warehouse_Deliverto_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
796 Warehouse_Deliverto_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
797
798 IF NOT Warehouse_Deliverto_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
799 Warehouse_Deliverto_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
800 END IF;
801
802 END IF;
803
804 IF T_HOLD_ENTITY_CODE(i) = 'W' AND T_HOLD_ENTITY_CODE2(i) = 'ST'
805 THEN
806 l_count := Warehouse_SourceType_Hold.COUNT;
807 Warehouse_SourceType_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
808 Warehouse_SourceType_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
809 Warehouse_SourceType_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
810 Warehouse_SourceType_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
811
812 IF NOT Warehouse_SourceType_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
813 Warehouse_SourceType_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
814 END IF;
815
816 END IF;
817
818 --ER#7479609 end
819
820 --ER#7479609 start
821 -- Misc
822
823
824 IF T_HOLD_ENTITY_CODE(i) = 'PL' AND T_HOLD_ENTITY_CODE2(i) = 'TC'
825 THEN
826 l_count := PriceList_Curr_Hold.COUNT;
827 PriceList_Curr_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
828 PriceList_Curr_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
829 PriceList_Curr_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
830 PriceList_Curr_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
831
832 IF NOT PriceList_Curr_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
833 PriceList_Curr_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
834 END IF;
835
836 END IF;
837
838
839 IF T_HOLD_ENTITY_CODE(i) = 'OT' AND T_HOLD_ENTITY_CODE2(i) = 'TC'
840 THEN
841 l_count := OrderType_Curr_Hold.COUNT;
842 OrderType_Curr_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
843 OrderType_Curr_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
844 OrderType_Curr_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
845 OrderType_Curr_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
846
847 IF NOT OrderType_Curr_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
848 OrderType_Curr_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
849 END IF;
850
851 END IF;
852
853
854 IF T_HOLD_ENTITY_CODE(i) = 'OT' AND T_HOLD_ENTITY_CODE2(i) = 'LT'
855 THEN
856 l_count := OrderType_LineType_Hold.COUNT;
857 OrderType_LineType_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
858 OrderType_LineType_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
859 OrderType_LineType_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
860 OrderType_LineType_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
861
862 IF NOT OrderType_LineType_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
863 OrderType_LineType_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
864 END IF;
865
866 END IF;
867
868
869 IF T_HOLD_ENTITY_CODE(i) = 'CD' AND T_HOLD_ENTITY_CODE2(i) = 'CB'
870 THEN
871 l_count := CreDate_CreBy_Hold.COUNT;
872 CreDate_CreBy_Hold(l_count+1).Entity_Id1 := T_HOLD_ENTITY_ID(i);
873 CreDate_CreBy_Hold(l_count+1).Entity_Id2 := T_HOLD_ENTITY_ID2(i);
874 CreDate_CreBy_Hold(l_count+1).Hold_Source_Id := T_HOLD_SOURCE_ID(i);
875 CreDate_CreBy_Hold(l_count+1).activity_name := T_HOLD_ACTIVITY_NAME(i);
876
877 IF NOT CreDate_CreBy_Pointer.EXISTS(T_HOLD_ENTITY_ID(i)) THEN
878 CreDate_CreBy_Pointer(T_HOLD_ENTITY_ID(i)) := l_count+1;
879 END IF;
880
881 END IF;
882
883
884 --ER#7479609 end
885
886 END LOOP;
887
888 oe_debug_pub.add('Exitting OE_Bulk_Holds_PVT.Load_Hold_Sources'); --ER#7479609 debug
889
890 EXCEPTION
891 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
893 WHEN OTHERS THEN
894 OE_BULK_MSG_PUB.Add_Exc_Msg
895 ( G_PKG_NAME
896 , 'Load_Hold_Sources'
897 );
898 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
899 END Load_Hold_Sources;
900
901
902
903 --ER#7479609 start
904 PROCEDURE Evaluate_Holds(
905 p_header_rec IN OE_Order_PUB.Header_Rec_Type,
906 p_line_rec IN OE_Order_PUB.Line_Rec_Type,
907 p_check_only_warehouse_holds IN BOOLEAN := FALSE,
908 p_on_generic_hold OUT NOCOPY BOOLEAN,
909 p_on_booking_hold OUT NOCOPY BOOLEAN,
910 p_on_scheduling_hold OUT NOCOPY BOOLEAN
911 )
912 IS
913 i NUMBER;
914 j NUMBER;
915 l_activity_name VARCHAR2(30);
916 l_inventory_item_id NUMBER;
917
918 BEGIN
919 oe_debug_pub.add('Entering OE_Bulk_Holds_PVT.Evaluate_Holds');
920 oe_debug_pub.add('Header'||p_header_rec.header_id);
921 oe_debug_pub.add('Line:'||p_line_rec.header_id||':'||p_line_rec.line_id);
922
923 -- Set the OUT parameter
924 p_on_generic_hold := FALSE;
925 p_on_booking_hold := FALSE;
926 p_on_scheduling_hold := FALSE;
927
928 IF p_line_rec.line_id IS NULL THEN -- Header Level Holds
929
930 -- Check Customer Hold
931 IF Customer_Pointer.EXISTS(p_header_rec.sold_to_org_id) THEN
932
933 j := Customer_Pointer(p_header_rec.sold_to_org_id);
934 WHILE Customer_hold(j).Entity_Id1 = p_header_rec.sold_to_org_id
935 LOOP
936 Mark_Hold(p_header_id => p_header_rec.header_id,
937 p_line_id => NULL,
938 p_line_number => NULL,
939 p_hold_source_id => Customer_hold(j).hold_source_id,
940 p_ship_set_name => NULL,
941 p_arrival_set_name => NULL,
942 p_activity_name => Customer_hold(j).activity_name,
943 p_attribute => 'Customer',
944 p_top_model_line_id => NULL
945 );
946 IF Customer_hold(j).activity_name IS NULL THEN
947 p_on_generic_hold := TRUE;
948 ELSIF Customer_hold(j).activity_name = 'BOOK_ORDER' THEN
949 p_on_booking_hold := TRUE;
950 END IF;
951 j := j+1;
952
953 IF j > Customer_hold.COUNT THEN
954 EXIT;
955 END IF;
956
957 END LOOP;
958
959 END IF;
960
961
962 --ER#7479609 start
963
964 -- Check Sales channel Hold
965 IF SalesChannel_Pointer.EXISTS(p_header_rec.sales_channel_code) THEN
966
967 j := SalesChannel_Pointer(p_header_rec.sales_channel_code);
968 WHILE SalesChannel_Hold(j).Entity_Id1 = p_header_rec.sales_channel_code
969 LOOP
970 Mark_Hold(p_header_id => p_header_rec.header_id,
971 p_line_id => NULL,
972 p_line_number => NULL,
973 p_hold_source_id => SalesChannel_Hold(j).hold_source_id,
974 p_ship_set_name => NULL,
975 p_arrival_set_name => NULL,
976 p_activity_name => SalesChannel_Hold(j).activity_name,
977 p_attribute => 'Sales Channel',
978 p_top_model_line_id => NULL
979 );
980 IF SalesChannel_Hold(j).activity_name IS NULL THEN
981 p_on_generic_hold := TRUE;
982 ELSIF SalesChannel_Hold(j).activity_name = 'BOOK_ORDER' THEN
983 p_on_booking_hold := TRUE;
984 END IF;
985 j := j+1;
986
987 IF j > SalesChannel_Hold.COUNT THEN
988 EXIT;
989 END IF;
990
991 END LOOP;
992
993 END IF;
994
995 -- Check Payment Type Hold
996 IF PaymentType_Pointer.EXISTS(p_header_rec.payment_type_code) THEN
997
998 j := PaymentType_Pointer(p_header_rec.payment_type_code);
999 WHILE PaymentType_Hold(j).Entity_Id1 = p_header_rec.payment_type_code
1000 LOOP
1001 Mark_Hold(p_header_id => p_header_rec.header_id,
1002 p_line_id => NULL,
1003 p_line_number => NULL,
1004 p_hold_source_id => PaymentType_Hold(j).hold_source_id,
1005 p_ship_set_name => NULL,
1006 p_arrival_set_name => NULL,
1007 p_activity_name => PaymentType_Hold(j).activity_name,
1008 p_attribute => 'Payment Type',
1009 p_top_model_line_id => NULL
1010 );
1011 IF PaymentType_Hold(j).activity_name IS NULL THEN
1012 p_on_generic_hold := TRUE;
1013 ELSIF PaymentType_Hold(j).activity_name = 'BOOK_ORDER' THEN
1014 p_on_booking_hold := TRUE;
1015 END IF;
1016 j := j+1;
1017
1018 IF j > PaymentType_Hold.COUNT THEN
1019 EXIT;
1020 END IF;
1021
1022 END LOOP;
1023
1024 END IF;
1025
1026 -- Check Customer Order Type Holds
1027 IF p_header_rec.sold_to_org_id IS NOT NULL AND
1028 p_header_rec.order_type_id IS NOT NULL AND
1029 Cust_OrderType_Pointer.EXISTS(p_header_rec.sold_to_org_id)
1030 THEN
1031 j := Cust_OrderType_Pointer(p_header_rec.sold_to_org_id);
1032 WHILE Cust_OrderType_Hold(j).Entity_Id1 = p_header_rec.sold_to_org_id
1033 LOOP
1034
1035 IF Cust_OrderType_Hold(j).Entity_Id2 = p_header_rec.order_type_id
1036 THEN
1037 Mark_Hold(p_header_id => p_header_rec.header_id,
1038 p_line_id => NULL,
1039 p_line_number => NULL,
1040 p_hold_source_id => Cust_OrderType_Hold(j).hold_source_id,
1041 p_ship_set_name => NULL,
1042 p_arrival_set_name => NULL,
1043 p_activity_name => Cust_OrderType_Hold(j).activity_name,
1044 p_attribute => 'Customer/Order Type',
1045 p_top_model_line_id => NULL
1046 );
1047 IF Cust_OrderType_Hold(j).activity_name IS NULL THEN
1048 p_on_generic_hold := TRUE;
1049 ELSIF Cust_OrderType_Hold(j).activity_name = 'BOOK_ORDER' THEN
1050 p_on_booking_hold := TRUE;
1051 END IF;
1052
1053 END IF;
1054 j := j + 1;
1055
1056 IF j > Cust_OrderType_Hold.COUNT THEN
1057 EXIT;
1058 END IF;
1059
1060 END LOOP;
1061 END IF;
1062
1063
1064 -- Check Customer payment Type Holds
1065
1066 IF p_header_rec.sold_to_org_id IS NOT NULL AND
1067 p_header_rec.payment_type_code IS NOT NULL AND
1068 Cust_PaymentType_Pointer.EXISTS(p_header_rec.sold_to_org_id)
1069 THEN
1070 j := Cust_PaymentType_Pointer(p_header_rec.sold_to_org_id);
1071 WHILE Cust_PaymentType_Hold(j).Entity_Id1 = p_header_rec.sold_to_org_id
1072 LOOP
1073
1074 IF Cust_PaymentType_Hold(j).Entity_Id2 = p_header_rec.payment_type_code
1075 THEN
1076 Mark_Hold(p_header_id => p_header_rec.header_id,
1077 p_line_id => NULL,
1078 p_line_number => NULL,
1079 p_hold_source_id => Cust_PaymentType_Hold(j).hold_source_id,
1080 p_ship_set_name => NULL,
1081 p_arrival_set_name => NULL,
1082 p_activity_name => Cust_PaymentType_Hold(j).activity_name,
1083 p_attribute => 'Customer/Payment Type',
1084 p_top_model_line_id => NULL
1085 );
1086 IF Cust_PaymentType_Hold(j).activity_name IS NULL THEN
1087 p_on_generic_hold := TRUE;
1088 ELSIF Cust_PaymentType_Hold(j).activity_name = 'BOOK_ORDER' THEN
1089 p_on_booking_hold := TRUE;
1090 END IF;
1091
1092 END IF;
1093 j := j + 1;
1094
1095 IF j > Cust_PaymentType_Hold.COUNT THEN
1096 EXIT;
1097 END IF;
1098
1099 END LOOP;
1100 END IF;
1101
1102 -- Check Customer currency Holds
1103 IF p_header_rec.sold_to_org_id IS NOT NULL AND
1104 p_header_rec.transactional_curr_code IS NOT NULL AND
1105 Cust_Curr_Pointer.EXISTS(p_header_rec.sold_to_org_id)
1106 THEN
1107 j := Cust_Curr_Pointer(p_header_rec.sold_to_org_id);
1108 WHILE Cust_Curr_Hold(j).Entity_Id1 = p_header_rec.sold_to_org_id
1109 LOOP
1110
1111 IF Cust_Curr_Hold(j).Entity_Id2 = p_header_rec.transactional_curr_code
1112 THEN
1113 Mark_Hold(p_header_id => p_header_rec.header_id,
1114 p_line_id => NULL,
1115 p_line_number => NULL,
1116 p_hold_source_id => Cust_Curr_Hold(j).hold_source_id,
1117 p_ship_set_name => NULL,
1118 p_arrival_set_name => NULL,
1119 p_activity_name => Cust_Curr_Hold(j).activity_name,
1120 p_attribute => 'Customer/Currency',
1121 p_top_model_line_id => NULL
1122 );
1123 IF Cust_Curr_Hold(j).activity_name IS NULL THEN
1124 p_on_generic_hold := TRUE;
1125 ELSIF Cust_Curr_Hold(j).activity_name = 'BOOK_ORDER' THEN
1126 p_on_booking_hold := TRUE;
1127 END IF;
1128
1129 END IF;
1130 j := j + 1;
1131
1132 IF j > Cust_Curr_Hold.COUNT THEN
1133 EXIT;
1134 END IF;
1135
1136 END LOOP;
1137 END IF;
1138
1139 -- Check Customer Sales Channel Holds
1140 IF p_header_rec.sold_to_org_id IS NOT NULL AND
1141 p_header_rec.sales_channel_code IS NOT NULL AND
1142 Cust_SalesChannel_Pointer.EXISTS(p_header_rec.sold_to_org_id)
1143 THEN
1144 j := Cust_SalesChannel_Pointer(p_header_rec.sold_to_org_id);
1145 WHILE Cust_SalesChannel_Hold(j).Entity_Id1 = p_header_rec.sold_to_org_id
1146 LOOP
1147
1148 IF Cust_SalesChannel_Hold(j).Entity_Id2 = p_header_rec.sales_channel_code
1149 THEN
1150 Mark_Hold(p_header_id => p_header_rec.header_id,
1151 p_line_id => NULL,
1152 p_line_number => NULL,
1153 p_hold_source_id => Cust_SalesChannel_Hold(j).hold_source_id,
1154 p_ship_set_name => NULL,
1155 p_arrival_set_name => NULL,
1156 p_activity_name => Cust_SalesChannel_Hold(j).activity_name,
1157 p_attribute => 'Customer/Sales Channel',
1158 p_top_model_line_id => NULL
1159 );
1160 IF Cust_SalesChannel_Hold(j).activity_name IS NULL THEN
1161 p_on_generic_hold := TRUE;
1162 ELSIF Cust_SalesChannel_Hold(j).activity_name = 'BOOK_ORDER' THEN
1163 p_on_booking_hold := TRUE;
1164 END IF;
1165
1166 END IF;
1167 j := j + 1;
1168
1169 IF j > Cust_SalesChannel_Hold.COUNT THEN
1170 EXIT;
1171 END IF;
1172
1173 END LOOP;
1174 END IF;
1175
1176 --ER#7479609 end
1177 ELSE -- Line Level Holds
1178
1179 IF p_check_only_warehouse_holds THEN
1180 GOTO Just_WareHouse_Holds;
1181 END IF;
1182
1183 -- Check Item Hold
1184 IF p_line_rec.inventory_item_id IS NOT NULL AND
1185 Item_Pointer.EXISTS(p_line_rec.inventory_item_id)
1186 THEN
1187 j := Item_Pointer(p_line_rec.inventory_item_id);
1188 WHILE item_hold(j).Entity_Id1 = p_line_rec.inventory_item_id
1189 LOOP
1190 Mark_Hold(p_header_id => p_line_rec.header_id,
1191 p_line_id => p_line_rec.line_id,
1192 p_line_number => p_line_rec.line_number,
1193 p_hold_source_id => Item_Hold(j).hold_source_id,
1194 p_ship_set_name => NULL,
1195 p_arrival_set_name => NULL,
1196 p_activity_name => Item_hold(j).activity_name,
1197 p_attribute => 'Item',
1198 p_top_model_line_id => p_line_rec.top_model_line_id
1199 );
1200 IF item_hold(j).activity_name IS NULL THEN
1201 p_on_generic_hold := TRUE;
1202 ELSIF item_hold(j).activity_name = 'LINE_SCHEDULING' THEN
1203 p_on_scheduling_hold := TRUE;
1204 END IF;
1205 j := j+1;
1206 IF j > Item_Hold.COUNT THEN
1207 EXIT;
1208 END IF;
1209
1210 END LOOP;
1211
1212 END IF;
1213
1214 -- Check Ship_To Hold
1215 IF p_line_rec.ship_to_org_id IS NOT NULL AND
1216 Ship_To_Pointer.EXISTS(p_line_rec.ship_to_org_id)
1217 THEN
1218 j := ship_to_Pointer(p_line_rec.ship_to_org_id);
1219 WHILE Ship_to_hold(j).Entity_Id1 = p_line_rec.ship_to_org_id
1220 LOOP
1221 Mark_Hold(p_header_id => p_line_rec.header_id,
1222 p_line_id => p_line_rec.line_id,
1223 p_line_number => p_line_rec.line_number,
1224 p_hold_source_id => Ship_to_Hold(j).hold_source_id,
1225 p_ship_set_name => NULL,
1226 p_arrival_set_name => NULL,
1227 p_activity_name => Ship_to_hold(j).activity_name,
1228 p_attribute => 'Ship to Site',
1229 p_top_model_line_id => p_line_rec.top_model_line_id
1230 );
1231 IF Ship_to_hold(j).activity_name IS NULL THEN
1232 p_on_generic_hold := TRUE;
1233 ELSIF Ship_to_hold(j).activity_name = 'LINE_SCHEDULING' THEN
1234 p_on_scheduling_hold := TRUE;
1235 END IF;
1236 j := j+1;
1237 IF j > Ship_to_Hold.COUNT THEN
1238 EXIT;
1239 END IF;
1240
1241 END LOOP;
1242
1243 END IF;
1244
1245 -- Check Bill_To Hold
1246 IF p_line_rec.invoice_to_org_id IS NOT NULL AND
1247 Bill_To_Pointer.EXISTS(p_line_rec.invoice_to_org_id)
1248 THEN
1249 j := Bill_To_Pointer(p_line_rec.invoice_to_org_id);
1250 WHILE Bill_To_hold(j).Entity_Id1 = p_line_rec.invoice_to_org_id
1251 LOOP
1252 Mark_Hold(p_header_id => p_line_rec.header_id,
1253 p_line_id => p_line_rec.line_id,
1254 p_line_number => p_line_rec.line_number,
1255 p_hold_source_id => Bill_To_hold(j).hold_source_id,
1256 p_ship_set_name => NULL,
1257 p_arrival_set_name => NULL,
1258 p_activity_name => Bill_To_hold(j).activity_name,
1259 p_attribute => 'Bill to Site',
1260 p_top_model_line_id => p_line_rec.top_model_line_id
1261 );
1262 IF Bill_To_hold(j).activity_name IS NULL THEN
1263 p_on_generic_hold := TRUE;
1264 ELSIF Bill_To_hold(j).activity_name = 'LINE_SCHEDULING' THEN
1265 p_on_scheduling_hold := TRUE;
1266 END IF;
1267 j := j+1;
1268 IF j > Bill_to_Hold.COUNT THEN
1269 EXIT;
1270 END IF;
1271
1272 END LOOP;
1273
1274 END IF;
1275
1276 --ER#7479609 start
1277
1278 -- Check Price List Hold
1279 IF p_line_rec.price_list_id IS NOT NULL AND
1280 PriceList_Pointer.EXISTS(p_line_rec.price_list_id)
1281 THEN
1282 j := PriceList_Pointer(p_line_rec.price_list_id);
1283 WHILE PriceList_Hold(j).Entity_Id1 = p_line_rec.price_list_id
1284 LOOP
1285 Mark_Hold(p_header_id => p_line_rec.header_id,
1286 p_line_id => p_line_rec.line_id,
1287 p_line_number => p_line_rec.line_number,
1288 p_hold_source_id => PriceList_Hold(j).hold_source_id,
1289 p_ship_set_name => NULL,
1290 p_arrival_set_name => NULL,
1291 p_activity_name => PriceList_Hold(j).activity_name,
1292 p_attribute => 'Price List',
1293 p_top_model_line_id => p_line_rec.top_model_line_id
1294 );
1295 IF PriceList_Hold(j).activity_name IS NULL THEN
1296 p_on_generic_hold := TRUE;
1297 ELSIF PriceList_Hold(j).activity_name = 'LINE_SCHEDULING' THEN
1298 p_on_scheduling_hold := TRUE;
1299 END IF;
1300 j := j+1;
1301 IF j > PriceList_Hold.COUNT THEN
1302 EXIT;
1303 END IF;
1304
1305 END LOOP;
1306
1307 END IF;
1308
1309 -- Check Order Type Hold
1310 IF p_header_rec.order_type_id IS NOT NULL AND
1311 OrderType_Pointer.EXISTS(p_header_rec.order_type_id)
1312 THEN
1313 j := OrderType_Pointer(p_header_rec.order_type_id);
1314 WHILE OrderType_Hold(j).Entity_Id1 = p_header_rec.order_type_id
1315 LOOP
1316 Mark_Hold(p_header_id => p_line_rec.header_id,
1317 p_line_id => p_line_rec.line_id,
1318 p_line_number => p_line_rec.line_number,
1319 p_hold_source_id => OrderType_Hold(j).hold_source_id,
1320 p_ship_set_name => NULL,
1321 p_arrival_set_name => NULL,
1322 p_activity_name => OrderType_Hold(j).activity_name,
1323 p_attribute => 'POrder Type',
1324 p_top_model_line_id => p_line_rec.top_model_line_id
1325 );
1326 IF OrderType_Hold(j).activity_name IS NULL THEN
1327 p_on_generic_hold := TRUE;
1328 ELSIF OrderType_Hold(j).activity_name = 'LINE_SCHEDULING' THEN
1329 p_on_scheduling_hold := TRUE;
1330 END IF;
1331 j := j+1;
1332 IF j > OrderType_Hold.COUNT THEN
1333 EXIT;
1334 END IF;
1335
1336 END LOOP;
1337
1338 END IF;
1339
1340 -- Check Creation Date Hold
1341 IF p_line_rec.creation_date IS NOT NULL AND
1342 CreationDate_Pointer.EXISTS(to_char(p_line_rec.creation_date,'DD-MON-RRRR'))
1343 THEN
1344 j := CreationDate_Pointer(to_char(p_line_rec.creation_date,'DD-MON-RRRR'));
1345 WHILE CreationDate_Hold(j).Entity_Id1 = to_char(p_line_rec.creation_date,'DD-MON-RRRR')
1346 LOOP
1347 Mark_Hold(p_header_id => p_line_rec.header_id,
1348 p_line_id => p_line_rec.line_id,
1349 p_line_number => p_line_rec.line_number,
1350 p_hold_source_id => CreationDate_Hold(j).hold_source_id,
1351 p_ship_set_name => NULL,
1352 p_arrival_set_name => NULL,
1353 p_activity_name => CreationDate_Hold(j).activity_name,
1354 p_attribute => 'Creation Date',
1355 p_top_model_line_id => p_line_rec.top_model_line_id
1356 );
1357 IF CreationDate_Hold(j).activity_name IS NULL THEN
1358 p_on_generic_hold := TRUE;
1359 ELSIF CreationDate_Hold(j).activity_name = 'LINE_SCHEDULING' THEN
1360 p_on_scheduling_hold := TRUE;
1361 END IF;
1362 j := j+1;
1363 IF j > CreationDate_Hold.COUNT THEN
1364 EXIT;
1365 END IF;
1366
1367 END LOOP;
1368
1369 END IF;
1370
1371
1372 -- Check Shipping Method Hold
1373 IF p_line_rec.shipping_method_code IS NOT NULL AND
1374 ShipMethod_Pointer.EXISTS(p_line_rec.shipping_method_code)
1375 THEN
1376 j := ShipMethod_Pointer(p_line_rec.shipping_method_code);
1377 WHILE ShipMethod_Hold(j).Entity_Id1 = p_line_rec.shipping_method_code
1378 LOOP
1379 Mark_Hold(p_header_id => p_line_rec.header_id,
1380 p_line_id => p_line_rec.line_id,
1381 p_line_number => p_line_rec.line_number,
1382 p_hold_source_id => ShipMethod_Hold(j).hold_source_id,
1383 p_ship_set_name => NULL,
1384 p_arrival_set_name => NULL,
1385 p_activity_name => ShipMethod_Hold(j).activity_name,
1386 p_attribute => 'Shipping Method',
1387 p_top_model_line_id => p_line_rec.top_model_line_id
1388 );
1389 IF ShipMethod_Hold(j).activity_name IS NULL THEN
1390 p_on_generic_hold := TRUE;
1391 ELSIF ShipMethod_Hold(j).activity_name = 'LINE_SCHEDULING' THEN
1392 p_on_scheduling_hold := TRUE;
1393 END IF;
1394 j := j+1;
1395 IF j > ShipMethod_Hold.COUNT THEN
1396 EXIT;
1397 END IF;
1398
1399 END LOOP;
1400
1401 END IF;
1402
1403
1404 --ER#7479609 end
1405
1406
1407 -- Check Item Customer Holds
1408 IF p_line_rec.inventory_item_id IS NOT NULL AND
1409 p_line_rec.sold_to_org_id IS NOT NULL AND
1410 Item_Customer_Pointer.EXISTS(p_line_rec.inventory_item_id)
1411 THEN
1412 j := Item_Customer_Pointer(p_line_rec.inventory_item_id);
1413 WHILE Item_Customer_hold(j).Entity_Id1 = p_line_rec.inventory_item_id
1414 LOOP
1415
1416 IF Item_Customer_Hold(j).Entity_Id2 = p_line_rec.sold_to_org_id
1417 THEN
1418 Mark_Hold(p_header_id => p_line_rec.header_id,
1419 p_line_id => p_line_rec.line_id,
1420 p_line_number => p_line_rec.line_number,
1421 p_hold_source_id => Item_Customer_Hold(j).hold_source_id,
1422 p_ship_set_name => NULL,
1423 p_arrival_set_name => NULL,
1424 p_activity_name => Item_Customer_Hold(j).activity_name,
1425 p_attribute => 'Item\Customer',
1426 p_top_model_line_id => p_line_rec.top_model_line_id
1427 );
1428 IF Item_Customer_Hold(j).activity_name IS NULL THEN
1429 p_on_generic_hold := TRUE;
1430 ELSIF Item_Customer_Hold(j).activity_name = 'LINE_SCHEDULING'
1431 THEN
1432 p_on_scheduling_hold := TRUE;
1433 END IF;
1434
1435 END IF;
1436 j := j + 1;
1437
1438 IF j > Item_Customer_hold.COUNT THEN
1439 EXIT;
1440 END IF;
1441
1442 END LOOP;
1443 END IF;
1444
1445 -- Check Item Shipto Holds
1446 IF p_line_rec.inventory_item_id IS NOT NULL AND
1447 p_line_rec.ship_to_org_id IS NOT NULL AND
1448 Item_Shipto_Pointer.EXISTS(p_line_rec.inventory_item_id)
1449 THEN
1450 j := Item_shipto_Pointer(p_line_rec.inventory_item_id);
1451 WHILE Item_shipto_hold(j).Entity_Id1 = p_line_rec.inventory_item_id
1452 LOOP
1453
1454 IF Item_shipto_Hold(j).Entity_Id2 = p_line_rec.ship_to_org_id
1455 THEN
1456 Mark_Hold(p_header_id => p_line_rec.header_id,
1457 p_line_id => p_line_rec.line_id,
1458 p_line_number => p_line_rec.line_number,
1459 p_hold_source_id => Item_shipto_Hold(j).hold_source_id,
1460 p_ship_set_name => NULL,
1461 p_arrival_set_name => NULL,
1462 p_activity_name => Item_shipto_Hold(j).activity_name,
1463 p_attribute => 'Item\Ship to Site',
1464 p_top_model_line_id => p_line_rec.top_model_line_id
1465 );
1466 IF Item_shipto_Hold(j).activity_name IS NULL THEN
1467 p_on_generic_hold := TRUE;
1468 ELSIF Item_shipto_Hold(j).activity_name = 'LINE_SCHEDULING'
1469 THEN
1470 p_on_scheduling_hold := TRUE;
1471 END IF;
1472
1473 END IF;
1474 j := j + 1;
1475
1476 IF j > Item_shipto_hold.COUNT THEN
1477 EXIT;
1478 END IF;
1479
1480 END LOOP;
1481 END IF;
1482
1483 -- Check Item LineType Holds
1484 IF p_line_rec.inventory_item_id IS NOT NULL AND
1485 p_line_rec.line_type_id IS NOT NULL AND
1486 Item_LineType_Pointer.EXISTS(p_line_rec.inventory_item_id)
1487 THEN
1488 j := Item_LineType_Pointer(p_line_rec.inventory_item_id);
1489 WHILE Item_LineType_Hold(j).Entity_Id1 = p_line_rec.inventory_item_id
1490 LOOP
1491
1492 IF Item_LineType_Hold(j).Entity_Id2 = p_line_rec.line_type_id
1493 THEN
1494 Mark_Hold(p_header_id => p_line_rec.header_id,
1495 p_line_id => p_line_rec.line_id,
1496 p_line_number => p_line_rec.line_number,
1497 p_hold_source_id => Item_LineType_Hold(j).hold_source_id,
1498 p_ship_set_name => NULL,
1499 p_arrival_set_name => NULL,
1500 p_activity_name => Item_LineType_Hold(j).activity_name,
1501 p_attribute => 'Item\Line Type',
1502 p_top_model_line_id => p_line_rec.top_model_line_id
1503 );
1504 IF Item_LineType_Hold(j).activity_name IS NULL THEN
1505 p_on_generic_hold := TRUE;
1506 ELSIF Item_LineType_Hold(j).activity_name = 'LINE_SCHEDULING'
1507 THEN
1508 p_on_scheduling_hold := TRUE;
1509 END IF;
1510
1511 END IF;
1512 j := j + 1;
1513
1514 IF j > Item_LineType_Hold.COUNT THEN
1515 EXIT;
1516 END IF;
1517
1518 END LOOP;
1519 END IF;
1520
1521 --ER#7479609 start
1522
1523
1524 -- Check Item Customer Holds
1525 IF p_line_rec.inventory_item_id IS NOT NULL AND
1526 p_line_rec.sold_to_org_id IS NOT NULL AND
1527 Item_Customer_Pointer.EXISTS(p_line_rec.inventory_item_id)
1528 THEN
1529 j := Item_Customer_Pointer(p_line_rec.inventory_item_id);
1530 WHILE Item_Customer_hold(j).Entity_Id1 = p_line_rec.inventory_item_id
1531 LOOP
1532
1533 IF Item_Customer_Hold(j).Entity_Id2 = p_line_rec.sold_to_org_id
1534 THEN
1535 Mark_Hold(p_header_id => p_line_rec.header_id,
1536 p_line_id => p_line_rec.line_id,
1537 p_line_number => p_line_rec.line_number,
1538 p_hold_source_id => Item_Customer_Hold(j).hold_source_id,
1539 p_ship_set_name => NULL,
1540 p_arrival_set_name => NULL,
1541 p_activity_name => Item_Customer_Hold(j).activity_name,
1542 p_attribute => 'Item\Customer',
1543 p_top_model_line_id => p_line_rec.top_model_line_id
1544 );
1545 IF Item_Customer_Hold(j).activity_name IS NULL THEN
1546 p_on_generic_hold := TRUE;
1547 ELSIF Item_Customer_Hold(j).activity_name = 'LINE_SCHEDULING'
1548 THEN
1549 p_on_scheduling_hold := TRUE;
1550 END IF;
1551
1552 END IF;
1553 j := j + 1;
1554
1555 IF j > Item_Customer_hold.COUNT THEN
1556 EXIT;
1557 END IF;
1558
1559 END LOOP;
1560 END IF;
1561
1562 -- Check Item Ship Method Holds
1563 IF p_line_rec.inventory_item_id IS NOT NULL AND
1564 p_line_rec.shipping_method_code IS NOT NULL AND
1565 Item_ShipMethod_Pointer.EXISTS(p_line_rec.inventory_item_id)
1566 THEN
1567 j := Item_ShipMethod_Pointer(p_line_rec.inventory_item_id);
1568 WHILE Item_ShipMethod_Hold(j).Entity_Id1 = p_line_rec.inventory_item_id
1569 LOOP
1570
1571 IF Item_ShipMethod_Hold(j).Entity_Id2 = p_line_rec.shipping_method_code
1572 THEN
1573 Mark_Hold(p_header_id => p_line_rec.header_id,
1574 p_line_id => p_line_rec.line_id,
1575 p_line_number => p_line_rec.line_number,
1576 p_hold_source_id => Item_ShipMethod_Hold(j).hold_source_id,
1577 p_ship_set_name => NULL,
1578 p_arrival_set_name => NULL,
1579 p_activity_name => Item_ShipMethod_Hold(j).activity_name,
1580 p_attribute => 'Item\Ship Method',
1581 p_top_model_line_id => p_line_rec.top_model_line_id
1582 );
1583 IF Item_ShipMethod_Hold(j).activity_name IS NULL THEN
1584 p_on_generic_hold := TRUE;
1585 ELSIF Item_ShipMethod_Hold(j).activity_name = 'LINE_SCHEDULING'
1586 THEN
1587 p_on_scheduling_hold := TRUE;
1588 END IF;
1589
1590 END IF;
1591 j := j + 1;
1592
1593 IF j > Item_ShipMethod_Hold.COUNT THEN
1594 EXIT;
1595 END IF;
1596
1597 END LOOP;
1598 END IF;
1599
1600 -- Check Item Deliver to Site Holds
1601 IF p_line_rec.inventory_item_id IS NOT NULL AND
1602 p_line_rec.deliver_to_org_id IS NOT NULL AND
1603 Item_Deliverto_Pointer.EXISTS(p_line_rec.inventory_item_id)
1604 THEN
1605 j := Item_Deliverto_Pointer(p_line_rec.inventory_item_id);
1606 WHILE Item_Deliverto_Hold(j).Entity_Id1 = p_line_rec.inventory_item_id
1607 LOOP
1608
1609 IF Item_Deliverto_Hold(j).Entity_Id2 = p_line_rec.deliver_to_org_id
1610 THEN
1611 Mark_Hold(p_header_id => p_line_rec.header_id,
1612 p_line_id => p_line_rec.line_id,
1613 p_line_number => p_line_rec.line_number,
1614 p_hold_source_id => Item_Deliverto_Hold(j).hold_source_id,
1615 p_ship_set_name => NULL,
1616 p_arrival_set_name => NULL,
1617 p_activity_name => Item_Deliverto_Hold(j).activity_name,
1618 p_attribute => 'Item\Deliver to Site',
1619 p_top_model_line_id => p_line_rec.top_model_line_id
1620 );
1621 IF Item_Deliverto_Hold(j).activity_name IS NULL THEN
1622 p_on_generic_hold := TRUE;
1623 ELSIF Item_Deliverto_Hold(j).activity_name = 'LINE_SCHEDULING'
1624 THEN
1625 p_on_scheduling_hold := TRUE;
1626 END IF;
1627
1628 END IF;
1629 j := j + 1;
1630
1631 IF j > Item_Deliverto_Hold.COUNT THEN
1632 EXIT;
1633 END IF;
1634
1635 END LOOP;
1636 END IF;
1637
1638
1639 -- Check Item Price List Holds
1640 IF p_line_rec.inventory_item_id IS NOT NULL AND
1641 p_line_rec.price_list_id IS NOT NULL AND
1642 Item_PriceList_Pointer.EXISTS(p_line_rec.inventory_item_id)
1643 THEN
1644 j := Item_PriceList_Pointer(p_line_rec.inventory_item_id);
1645 WHILE Item_PriceList_Hold(j).Entity_Id1 = p_line_rec.inventory_item_id
1646 LOOP
1647
1648 IF Item_PriceList_Hold(j).Entity_Id2 = p_line_rec.price_list_id
1649 THEN
1650 Mark_Hold(p_header_id => p_line_rec.header_id,
1651 p_line_id => p_line_rec.line_id,
1652 p_line_number => p_line_rec.line_number,
1653 p_hold_source_id => Item_PriceList_Hold(j).hold_source_id,
1654 p_ship_set_name => NULL,
1655 p_arrival_set_name => NULL,
1656 p_activity_name => Item_PriceList_Hold(j).activity_name,
1657 p_attribute => 'Item\Price List',
1658 p_top_model_line_id => p_line_rec.top_model_line_id
1659 );
1660 IF Item_PriceList_Hold(j).activity_name IS NULL THEN
1661 p_on_generic_hold := TRUE;
1662 ELSIF Item_PriceList_Hold(j).activity_name = 'LINE_SCHEDULING'
1663 THEN
1664 p_on_scheduling_hold := TRUE;
1665 END IF;
1666
1667 END IF;
1668 j := j + 1;
1669
1670 IF j > Item_PriceList_Hold.COUNT THEN
1671 EXIT;
1672 END IF;
1673
1674 END LOOP;
1675 END IF;
1676
1677
1678 -- Check Item Source Type Holds
1679 IF p_line_rec.inventory_item_id IS NOT NULL AND
1680 p_line_rec.source_type_code IS NOT NULL AND
1681 Item_SourceType_Pointer.EXISTS(p_line_rec.inventory_item_id)
1682 THEN
1683 j := Item_SourceType_Pointer(p_line_rec.inventory_item_id);
1684 WHILE Item_SourceType_Hold(j).Entity_Id1 = p_line_rec.inventory_item_id
1685 LOOP
1686
1687 IF Item_SourceType_Hold(j).Entity_Id2 = p_line_rec.source_type_code
1688 THEN
1689 Mark_Hold(p_header_id => p_line_rec.header_id,
1690 p_line_id => p_line_rec.line_id,
1691 p_line_number => p_line_rec.line_number,
1692 p_hold_source_id => Item_SourceType_Hold(j).hold_source_id,
1693 p_ship_set_name => NULL,
1694 p_arrival_set_name => NULL,
1695 p_activity_name => Item_SourceType_Hold(j).activity_name,
1696 p_attribute => 'Item\Source Type',
1697 p_top_model_line_id => p_line_rec.top_model_line_id
1698 );
1699 IF Item_SourceType_Hold(j).activity_name IS NULL THEN
1700 p_on_generic_hold := TRUE;
1701 ELSIF Item_SourceType_Hold(j).activity_name = 'LINE_SCHEDULING'
1702 THEN
1703 p_on_scheduling_hold := TRUE;
1704 END IF;
1705
1706 END IF;
1707 j := j + 1;
1708
1709 IF j > Item_SourceType_Hold.COUNT THEN
1710 EXIT;
1711 END IF;
1712
1713 END LOOP;
1714 END IF;
1715
1716 --ER#7479609 end
1717
1718 -- Check Item BillTo Holds
1719 IF p_line_rec.inventory_item_id IS NOT NULL AND
1720 p_line_rec.invoice_to_org_id IS NOT NULL AND
1721 Item_Billto_Pointer.EXISTS(p_line_rec.inventory_item_id)
1722 THEN
1723 j := Item_billto_Pointer(p_line_rec.inventory_item_id);
1724 WHILE Item_billto_hold(j).Entity_Id1 = p_line_rec.inventory_item_id
1725 LOOP
1726
1727 IF Item_billto_Hold(j).Entity_Id2 = p_line_rec.invoice_to_org_id
1728 THEN
1729 Mark_Hold(p_header_id => p_line_rec.header_id,
1730 p_line_id => p_line_rec.line_id,
1731 p_line_number => p_line_rec.line_number,
1732 p_hold_source_id => Item_billto_Hold(j).hold_source_id,
1733 p_ship_set_name => NULL,
1734 p_arrival_set_name => NULL,
1735 p_activity_name => Item_billto_Hold(j).activity_name,
1736 p_attribute => 'Item\Bill to Site',
1737 p_top_model_line_id => p_line_rec.top_model_line_id
1738 );
1739 IF Item_billto_Hold(j).activity_name IS NULL THEN
1740 p_on_generic_hold := TRUE;
1741 ELSIF Item_billto_Hold(j).activity_name = 'LINE_SCHEDULING'
1742 THEN
1743 p_on_scheduling_hold := TRUE;
1744 END IF;
1745
1746 END IF;
1747 j := j + 1;
1748
1749 IF j > Item_billto_hold.COUNT THEN
1750 EXIT;
1751 END IF;
1752
1753 END LOOP;
1754 END IF;
1755
1756 --ER#7479609 start
1757
1758 -- Check Customer Source Type Holds
1759 IF p_line_rec.sold_to_org_id IS NOT NULL AND
1760 p_line_rec.source_type_code IS NOT NULL AND
1761 Cust_SourceType_Pointer.EXISTS(p_line_rec.sold_to_org_id)
1762 THEN
1763 j := Cust_SourceType_Pointer(p_line_rec.sold_to_org_id);
1764 WHILE Cust_SourceType_Hold(j).Entity_Id1 = p_line_rec.sold_to_org_id
1765 LOOP
1766
1767 IF Cust_SourceType_Hold(j).Entity_Id2 = p_line_rec.source_type_code
1768 THEN
1769 Mark_Hold(p_header_id => p_line_rec.header_id,
1770 p_line_id => p_line_rec.line_id,
1771 p_line_number => p_line_rec.line_number,
1772 p_hold_source_id => Cust_SourceType_Hold(j).hold_source_id,
1773 p_ship_set_name => NULL,
1774 p_arrival_set_name => NULL,
1775 p_activity_name => Cust_SourceType_Hold(j).activity_name,
1776 p_attribute => 'Customer/Source Type',
1777 p_top_model_line_id => p_line_rec.top_model_line_id
1778 );
1779 IF Cust_SourceType_Hold(j).activity_name IS NULL THEN
1780 p_on_generic_hold := TRUE;
1781 ELSIF Cust_SourceType_Hold(j).activity_name = 'LINE_SCHEDULING'
1782 THEN
1783 p_on_scheduling_hold := TRUE;
1784 END IF;
1785
1786 END IF;
1787 j := j + 1;
1788
1789 IF j > Cust_SourceType_Hold.COUNT THEN
1790 EXIT;
1791 END IF;
1792
1793 END LOOP;
1794 END IF;
1795
1796 -- Check Customer Bill to Site Holds
1797 IF p_line_rec.sold_to_org_id IS NOT NULL AND
1798 p_line_rec.invoice_to_org_id IS NOT NULL AND
1799 Cust_Billto_Pointer.EXISTS(p_line_rec.sold_to_org_id)
1800 THEN
1801 j := Cust_Billto_Pointer(p_line_rec.sold_to_org_id);
1802 WHILE Cust_Billto_Hold(j).Entity_Id1 = p_line_rec.sold_to_org_id
1803 LOOP
1804
1805 IF Cust_Billto_Hold(j).Entity_Id2 = p_line_rec.invoice_to_org_id
1806 THEN
1807 Mark_Hold(p_header_id => p_line_rec.header_id,
1808 p_line_id => p_line_rec.line_id,
1809 p_line_number => p_line_rec.line_number,
1810 p_hold_source_id => Cust_Billto_Hold(j).hold_source_id,
1811 p_ship_set_name => NULL,
1812 p_arrival_set_name => NULL,
1813 p_activity_name => Cust_Billto_Hold(j).activity_name,
1814 p_attribute => 'Customer/Bill to Site',
1815 p_top_model_line_id => p_line_rec.top_model_line_id
1816 );
1817 IF Cust_Billto_Hold(j).activity_name IS NULL THEN
1818 p_on_generic_hold := TRUE;
1819 ELSIF Cust_Billto_Hold(j).activity_name = 'LINE_SCHEDULING'
1820 THEN
1821 p_on_scheduling_hold := TRUE;
1822 END IF;
1823
1824 END IF;
1825 j := j + 1;
1826
1827 IF j > Cust_Billto_Hold.COUNT THEN
1828 EXIT;
1829 END IF;
1830
1831 END LOOP;
1832 END IF;
1833
1834 -- Check Customer Ship to Site Holds
1835 IF p_line_rec.sold_to_org_id IS NOT NULL AND
1836 p_line_rec.ship_to_org_id IS NOT NULL AND
1837 Cust_Shipto_Pointer.EXISTS(p_line_rec.sold_to_org_id)
1838 THEN
1839 j := Cust_Shipto_Pointer(p_line_rec.sold_to_org_id);
1840 WHILE Cust_Shipto_Hold(j).Entity_Id1 = p_line_rec.sold_to_org_id
1841 LOOP
1842
1843 IF Cust_Shipto_Hold(j).Entity_Id2 = p_line_rec.ship_to_org_id
1844 THEN
1845 Mark_Hold(p_header_id => p_line_rec.header_id,
1846 p_line_id => p_line_rec.line_id,
1847 p_line_number => p_line_rec.line_number,
1848 p_hold_source_id => Cust_Shipto_Hold(j).hold_source_id,
1849 p_ship_set_name => NULL,
1850 p_arrival_set_name => NULL,
1851 p_activity_name => Cust_Shipto_Hold(j).activity_name,
1852 p_attribute => 'Customer/Ship to Site',
1853 p_top_model_line_id => p_line_rec.top_model_line_id
1854 );
1855 IF Cust_Shipto_Hold(j).activity_name IS NULL THEN
1856 p_on_generic_hold := TRUE;
1857 ELSIF Cust_Shipto_Hold(j).activity_name = 'LINE_SCHEDULING'
1858 THEN
1859 p_on_scheduling_hold := TRUE;
1860 END IF;
1861
1862 END IF;
1863 j := j + 1;
1864
1865 IF j > Cust_Shipto_Hold.COUNT THEN
1866 EXIT;
1867 END IF;
1868
1869 END LOOP;
1870 END IF;
1871
1872
1873 -- Check Customer Deliver to Site Holds
1874 IF p_line_rec.sold_to_org_id IS NOT NULL AND
1875 p_line_rec.deliver_to_org_id IS NOT NULL AND
1876 Cust_Deliverto_Pointer.EXISTS(p_line_rec.sold_to_org_id)
1877 THEN
1878 j := Cust_Deliverto_Pointer(p_line_rec.sold_to_org_id);
1879 WHILE Cust_Deliverto_Hold(j).Entity_Id1 = p_line_rec.deliver_to_org_id
1880 LOOP
1881
1882 IF Cust_Deliverto_Hold(j).Entity_Id2 = p_line_rec.ship_to_org_id
1883 THEN
1884 Mark_Hold(p_header_id => p_line_rec.header_id,
1885 p_line_id => p_line_rec.line_id,
1886 p_line_number => p_line_rec.line_number,
1887 p_hold_source_id => Cust_Deliverto_Hold(j).hold_source_id,
1888 p_ship_set_name => NULL,
1889 p_arrival_set_name => NULL,
1890 p_activity_name => Cust_Deliverto_Hold(j).activity_name,
1891 p_attribute => 'Customer/Deliver to Site',
1892 p_top_model_line_id => p_line_rec.top_model_line_id
1893 );
1894 IF Cust_Deliverto_Hold(j).activity_name IS NULL THEN
1895 p_on_generic_hold := TRUE;
1896 ELSIF Cust_Deliverto_Hold(j).activity_name = 'LINE_SCHEDULING'
1897 THEN
1898 p_on_scheduling_hold := TRUE;
1899 END IF;
1900
1901 END IF;
1902 j := j + 1;
1903
1904 IF j > Cust_Deliverto_Hold.COUNT THEN
1905 EXIT;
1906 END IF;
1907
1908 END LOOP;
1909 END IF;
1910
1911
1912 -- Check Customer Price List Holds
1913 IF p_line_rec.sold_to_org_id IS NOT NULL AND
1914 p_line_rec.price_list_id IS NOT NULL AND
1915 Cust_PriceList_Pointer.EXISTS(p_line_rec.sold_to_org_id)
1916 THEN
1917 j := Cust_PriceList_Pointer(p_line_rec.sold_to_org_id);
1918 WHILE Cust_PriceList_Hold(j).Entity_Id1 = p_line_rec.deliver_to_org_id
1919 LOOP
1920
1921 IF Cust_PriceList_Hold(j).Entity_Id2 = p_line_rec.price_list_id
1922 THEN
1923 Mark_Hold(p_header_id => p_line_rec.header_id,
1924 p_line_id => p_line_rec.line_id,
1925 p_line_number => p_line_rec.line_number,
1926 p_hold_source_id => Cust_PriceList_Hold(j).hold_source_id,
1927 p_ship_set_name => NULL,
1928 p_arrival_set_name => NULL,
1929 p_activity_name => Cust_PriceList_Hold(j).activity_name,
1930 p_attribute => 'Customer/Price List',
1931 p_top_model_line_id => p_line_rec.top_model_line_id
1932 );
1933 IF Cust_PriceList_Hold(j).activity_name IS NULL THEN
1934 p_on_generic_hold := TRUE;
1935 ELSIF Cust_PriceList_Hold(j).activity_name = 'LINE_SCHEDULING'
1936 THEN
1937 p_on_scheduling_hold := TRUE;
1938 END IF;
1939
1940 END IF;
1941 j := j + 1;
1942
1943 IF j > Cust_PriceList_Hold.COUNT THEN
1944 EXIT;
1945 END IF;
1946
1947 END LOOP;
1948 END IF;
1949
1950 -- Check Customer Line type Holds
1951 IF p_line_rec.sold_to_org_id IS NOT NULL AND
1952 p_line_rec.line_type_id IS NOT NULL AND
1953 Cust_LineType_Pointer.EXISTS(p_line_rec.sold_to_org_id)
1954 THEN
1955 j := Cust_LineType_Pointer(p_line_rec.sold_to_org_id);
1956 WHILE Cust_LineType_Hold(j).Entity_Id1 = p_line_rec.deliver_to_org_id
1957 LOOP
1958
1959 IF Cust_LineType_Hold(j).Entity_Id2 = p_line_rec.line_type_id
1960 THEN
1961 Mark_Hold(p_header_id => p_line_rec.header_id,
1962 p_line_id => p_line_rec.line_id,
1963 p_line_number => p_line_rec.line_number,
1964 p_hold_source_id => Cust_LineType_Hold(j).hold_source_id,
1965 p_ship_set_name => NULL,
1966 p_arrival_set_name => NULL,
1967 p_activity_name => Cust_LineType_Hold(j).activity_name,
1968 p_attribute => 'Customer/Line Type',
1969 p_top_model_line_id => p_line_rec.top_model_line_id
1970 );
1971 IF Cust_LineType_Hold(j).activity_name IS NULL THEN
1972 p_on_generic_hold := TRUE;
1973 ELSIF Cust_LineType_Hold(j).activity_name = 'LINE_SCHEDULING'
1974 THEN
1975 p_on_scheduling_hold := TRUE;
1976 END IF;
1977
1978 END IF;
1979 j := j + 1;
1980
1981 IF j > Cust_LineType_Hold.COUNT THEN
1982 EXIT;
1983 END IF;
1984
1985 END LOOP;
1986 END IF;
1987
1988 -- Check Customer Payment term Holds
1989 IF p_line_rec.sold_to_org_id IS NOT NULL AND
1990 p_line_rec.payment_term_id IS NOT NULL AND
1991 Cust_PayTerm_Pointer.EXISTS(p_line_rec.sold_to_org_id)
1992 THEN
1993 j := Cust_PayTerm_Pointer(p_line_rec.sold_to_org_id);
1994 WHILE Cust_PayTerm_Hold(j).Entity_Id1 = p_line_rec.deliver_to_org_id
1995 LOOP
1996
1997 IF Cust_PayTerm_Hold(j).Entity_Id2 = p_line_rec.payment_term_id
1998 THEN
1999 Mark_Hold(p_header_id => p_line_rec.header_id,
2000 p_line_id => p_line_rec.line_id,
2001 p_line_number => p_line_rec.line_number,
2002 p_hold_source_id => Cust_PayTerm_Hold(j).hold_source_id,
2003 p_ship_set_name => NULL,
2004 p_arrival_set_name => NULL,
2005 p_activity_name => Cust_PayTerm_Hold(j).activity_name,
2006 p_attribute => 'Customer/Payment Term',
2007 p_top_model_line_id => p_line_rec.top_model_line_id
2008 );
2009 IF Cust_PayTerm_Hold(j).activity_name IS NULL THEN
2010 p_on_generic_hold := TRUE;
2011 ELSIF Cust_PayTerm_Hold(j).activity_name = 'LINE_SCHEDULING'
2012 THEN
2013 p_on_scheduling_hold := TRUE;
2014 END IF;
2015
2016 END IF;
2017 j := j + 1;
2018
2019 IF j > Cust_PayTerm_Hold.COUNT THEN
2020 EXIT;
2021 END IF;
2022
2023 END LOOP;
2024 END IF;
2025
2026
2027 -- Check Order Type Line Type Holds
2028 IF p_header_rec.order_type_id IS NOT NULL AND
2029 p_line_rec.line_type_id IS NOT NULL AND
2030 OrderType_LineType_Pointer.EXISTS(p_header_rec.order_type_id)
2031 THEN
2032 j := OrderType_LineType_Pointer(p_header_rec.order_type_id);
2033 WHILE OrderType_LineType_Hold(j).Entity_Id1 = p_header_rec.order_type_id
2034 LOOP
2035
2036 IF OrderType_LineType_Hold(j).Entity_Id2 = p_line_rec.line_type_id
2037 THEN
2038 Mark_Hold(p_header_id => p_line_rec.header_id,
2039 p_line_id => p_line_rec.line_id,
2040 p_line_number => p_line_rec.line_number,
2041 p_hold_source_id => OrderType_LineType_Hold(j).hold_source_id,
2042 p_ship_set_name => NULL,
2043 p_arrival_set_name => NULL,
2044 p_activity_name => OrderType_LineType_Hold(j).activity_name,
2045 p_attribute => 'Order Type/Line Type',
2046 p_top_model_line_id => p_line_rec.top_model_line_id
2047 );
2048 IF OrderType_LineType_Hold(j).activity_name IS NULL THEN
2049 p_on_generic_hold := TRUE;
2050 ELSIF OrderType_LineType_Hold(j).activity_name = 'LINE_SCHEDULING'
2051 THEN
2052 p_on_scheduling_hold := TRUE;
2053 END IF;
2054
2055 END IF;
2056 j := j + 1;
2057
2058 IF j > OrderType_LineType_Hold.COUNT THEN
2059 EXIT;
2060 END IF;
2061
2062 END LOOP;
2063 END IF;
2064
2065
2066 -- Check Creation date/Creation By Holds
2067 IF p_line_rec.creation_date IS NOT NULL AND
2068 p_line_rec.created_by IS NOT NULL AND
2069 CreDate_CreBy_Pointer.EXISTS(to_char(p_line_rec.creation_date,'DD-MON-RRRR'))
2070 THEN
2071 j := CreDate_CreBy_Pointer(to_char(p_line_rec.creation_date,'DD-MON-RRRR'));
2072 WHILE CreDate_CreBy_Hold(j).Entity_Id1 = to_char(p_line_rec.creation_date,'DD-MON-RRRR')
2073 LOOP
2074
2075 IF CreDate_CreBy_Hold(j).Entity_Id2 = p_line_rec.created_by
2076 THEN
2077 Mark_Hold(p_header_id => p_line_rec.header_id,
2078 p_line_id => p_line_rec.line_id,
2079 p_line_number => p_line_rec.line_number,
2080 p_hold_source_id => CreDate_CreBy_Hold(j).hold_source_id,
2081 p_ship_set_name => NULL,
2082 p_arrival_set_name => NULL,
2083 p_activity_name => CreDate_CreBy_Hold(j).activity_name,
2084 p_attribute => 'Creation Date/Creation By',
2085 p_top_model_line_id => p_line_rec.top_model_line_id
2086 );
2087 IF CreDate_CreBy_Hold(j).activity_name IS NULL THEN
2088 p_on_generic_hold := TRUE;
2089 ELSIF CreDate_CreBy_Hold(j).activity_name = 'LINE_SCHEDULING'
2090 THEN
2091 p_on_scheduling_hold := TRUE;
2092 END IF;
2093
2094 END IF;
2095 j := j + 1;
2096
2097 IF j > CreDate_CreBy_Hold.COUNT THEN
2098 EXIT;
2099 END IF;
2100
2101 END LOOP;
2102 END IF;
2103
2104
2105 --ER#7479609 end
2106
2107 <<Just_WareHouse_Holds>>
2108
2109 -- Check Item Warehouse Holds
2110 IF p_line_rec.inventory_item_id IS NOT NULL AND
2111 p_line_rec.ship_from_org_id IS NOT NULL AND
2112 Item_Warehouse_Pointer.EXISTS(p_line_rec.inventory_item_id)
2113 THEN
2114 j := Item_Warehouse_Pointer(p_line_rec.inventory_item_id);
2115 WHILE Item_Warehouse_hold(j).Entity_Id1 = p_line_rec.inventory_item_id
2116 LOOP
2117
2118 IF Item_Warehouse_Hold(j).Entity_Id2 = p_line_rec.ship_from_org_id
2119 THEN
2120 Mark_Hold(p_header_id => p_line_rec.header_id,
2121 p_line_id => p_line_rec.line_id,
2122 p_line_number => p_line_rec.line_number,
2123 p_hold_source_id => Item_Warehouse_Hold(j).hold_source_id,
2124 p_ship_set_name => NULL,
2125 p_arrival_set_name => NULL,
2126 p_activity_name => Item_Warehouse_Hold(j).activity_name,
2127 p_attribute => 'Item\Warehouse',
2128 p_top_model_line_id => p_line_rec.top_model_line_id
2129 );
2130 IF Item_Warehouse_Hold(j).activity_name IS NULL THEN
2131 p_on_generic_hold := TRUE;
2132 ELSIF Item_Warehouse_Hold(j).activity_name = 'LINE_SCHEDULING'
2133 THEN
2134 p_on_scheduling_hold := TRUE;
2135 END IF;
2136
2137 END IF;
2138 j := j + 1;
2139
2140 IF j > Item_Warehouse_hold.COUNT THEN
2141 EXIT;
2142 END IF;
2143
2144 END LOOP;
2145 END IF;
2146
2147 -- Check Warehouse Hold
2148 IF p_line_rec.ship_from_org_id IS NOT NULL AND
2149 Warehouse_Pointer.EXISTS(p_line_rec.ship_from_org_id)
2150 THEN
2151 j := Warehouse_Pointer(p_line_rec.ship_from_org_id);
2152 WHILE Warehouse_hold(j).Entity_Id1 = p_line_rec.ship_from_org_id
2153 LOOP
2154 Mark_Hold(p_header_id => p_line_rec.header_id,
2155 p_line_id => p_line_rec.line_id,
2156 p_line_number => p_line_rec.line_number,
2157 p_hold_source_id => Warehouse_hold(j).hold_source_id,
2158 p_ship_set_name => NULL,
2159 p_arrival_set_name => NULL,
2160 p_activity_name => Warehouse_hold(j).activity_name,
2161 p_attribute => 'Warehouse',
2162 p_top_model_line_id => p_line_rec.top_model_line_id
2163 );
2164 IF Warehouse_hold(j).activity_name IS NULL THEN
2165 p_on_generic_hold := TRUE;
2166 ELSIF Warehouse_hold(j).activity_name = 'LINE_SCHEDULING' THEN
2167 p_on_scheduling_hold := TRUE;
2168 END IF;
2169 j := j+1;
2170 IF j > Warehouse_Hold.COUNT THEN
2171 EXIT;
2172 END IF;
2173
2174 END LOOP;
2175
2176 END IF;
2177
2178 -- Check Warehouse Customer Holds
2179 IF p_line_rec.ship_from_org_id IS NOT NULL AND
2180 p_line_rec.sold_to_org_id IS NOT NULL AND
2181 Warehouse_Customer_Pointer.EXISTS(p_line_rec.ship_from_org_id)
2182 THEN
2183 j := Warehouse_Customer_Pointer(p_line_rec.ship_from_org_id);
2184 WHILE Warehouse_Customer_Hold(j).Entity_Id1 = p_line_rec.ship_from_org_id
2185 LOOP
2186
2187 IF Warehouse_Customer_Hold(j).Entity_Id2 = p_line_rec.sold_to_org_id
2188 THEN
2189 Mark_Hold(p_header_id => p_line_rec.header_id,
2190 p_line_id => p_line_rec.line_id,
2191 p_line_number => p_line_rec.line_number,
2192 p_hold_source_id =>Warehouse_Customer_Hold(j).hold_source_id,
2193 p_ship_set_name => NULL,
2194 p_arrival_set_name => NULL,
2195 p_activity_name => Warehouse_Customer_Hold(j).activity_name,
2196 p_attribute => 'Warehouse\Customer',
2197 p_top_model_line_id => p_line_rec.top_model_line_id
2198 );
2199 IF Warehouse_Customer_Hold(j).activity_name IS NULL THEN
2200 p_on_generic_hold := TRUE;
2201 ELSIF Warehouse_Customer_Hold(j).activity_name = 'LINE_SCHEDULING'
2202 THEN
2203 p_on_scheduling_hold := TRUE;
2204 END IF;
2205
2206 END IF;
2207 j := j + 1;
2208
2209 IF j > Warehouse_Customer_Hold.COUNT THEN
2210 EXIT;
2211 END IF;
2212
2213 END LOOP;
2214 END IF;
2215
2216 -- Check Warehouse Shipto Holds
2217 IF p_line_rec.ship_from_org_id IS NOT NULL AND
2218 p_line_rec.ship_to_org_id IS NOT NULL AND
2219 Warehouse_shipto_pointer.EXISTS(p_line_rec.ship_from_org_id)
2220 THEN
2221 j := Warehouse_shipto_pointer(p_line_rec.ship_from_org_id);
2222 WHILE Warehouse_shipto_Hold(j).Entity_Id1 = p_line_rec.ship_from_org_id
2223 LOOP
2224
2225 IF Warehouse_shipto_Hold(j).Entity_Id2 = p_line_rec.ship_to_org_id
2226 THEN
2227 Mark_Hold(p_header_id => p_line_rec.header_id,
2228 p_line_id => p_line_rec.line_id,
2229 p_line_number => p_line_rec.line_number,
2230 p_hold_source_id => Warehouse_shipto_Hold(j).hold_source_id,
2231 p_ship_set_name => NULL,
2232 p_arrival_set_name => NULL,
2233 p_activity_name => Warehouse_shipto_Hold(j).activity_name,
2234 p_attribute => 'Warehouse\Ship to Site',
2235 p_top_model_line_id => p_line_rec.top_model_line_id
2236 );
2237 IF Warehouse_shipto_Hold(j).activity_name IS NULL THEN
2238 p_on_generic_hold := TRUE;
2239 ELSIF Warehouse_shipto_Hold(j).activity_name = 'LINE_SCHEDULING'
2240 THEN
2241 p_on_scheduling_hold := TRUE;
2242 END IF;
2243
2244 END IF;
2245 j := j + 1;
2246
2247 IF j > Warehouse_shipto_Hold.COUNT THEN
2248 EXIT;
2249 END IF;
2250
2251 END LOOP;
2252 END IF;
2253
2254 -- Check Warehouse BillTo Holds
2255 IF p_line_rec.ship_from_org_id IS NOT NULL AND
2256 p_line_rec.invoice_to_org_id IS NOT NULL AND
2257 Warehouse_billto_pointer.EXISTS(p_line_rec.ship_from_org_id)
2258 THEN
2259 j := Warehouse_billto_pointer(p_line_rec.ship_from_org_id);
2260 WHILE Warehouse_billto_Hold(j).Entity_Id1 = p_line_rec.ship_from_org_id
2261 LOOP
2262
2263 IF Warehouse_billto_Hold(j).Entity_Id2 = p_line_rec.invoice_to_org_id
2264 THEN
2265 Mark_Hold(p_header_id => p_line_rec.header_id,
2266 p_line_id => p_line_rec.line_id,
2267 p_line_number => p_line_rec.line_number,
2268 p_hold_source_id => Warehouse_billto_Hold(j).hold_source_id,
2269 p_ship_set_name => NULL,
2270 p_arrival_set_name => NULL,
2271 p_activity_name => Warehouse_billto_Hold(j).activity_name,
2272 p_attribute => 'Warehouse\Bill to Site',
2273 p_top_model_line_id => p_line_rec.top_model_line_id
2274 );
2275 IF Warehouse_billto_Hold(j).activity_name IS NULL THEN
2276 p_on_generic_hold := TRUE;
2277 ELSIF Warehouse_billto_Hold(j).activity_name = 'LINE_SCHEDULING'
2278 THEN
2279 p_on_scheduling_hold := TRUE;
2280 END IF;
2281
2282 END IF;
2283 j := j + 1;
2284
2285 IF j > Warehouse_billto_Hold.COUNT THEN
2286 EXIT;
2287 END IF;
2288
2289 END LOOP;
2290 END IF;
2291
2292 --ER#7479609 start
2293
2294 -- Check Warehouse Line Type Holds
2295 IF p_line_rec.ship_from_org_id IS NOT NULL AND
2296 p_line_rec.line_type_id IS NOT NULL AND
2297 Warehouse_LineType_Pointer.EXISTS(p_line_rec.ship_from_org_id)
2298 THEN
2299 j := Warehouse_LineType_Pointer(p_line_rec.ship_from_org_id);
2300 WHILE Warehouse_LineType_Hold(j).Entity_Id1 = p_line_rec.ship_from_org_id
2301 LOOP
2302
2303 IF Warehouse_LineType_Hold(j).Entity_Id2 = p_line_rec.line_type_id
2304 THEN
2305 Mark_Hold(p_header_id => p_line_rec.header_id,
2306 p_line_id => p_line_rec.line_id,
2307 p_line_number => p_line_rec.line_number,
2308 p_hold_source_id => Warehouse_LineType_Hold(j).hold_source_id,
2309 p_ship_set_name => NULL,
2310 p_arrival_set_name => NULL,
2311 p_activity_name => Warehouse_LineType_Hold(j).activity_name,
2312 p_attribute => 'Warehouse\Line Type',
2313 p_top_model_line_id => p_line_rec.top_model_line_id
2314 );
2315 IF Warehouse_LineType_Hold(j).activity_name IS NULL THEN
2316 p_on_generic_hold := TRUE;
2317 ELSIF Warehouse_LineType_Hold(j).activity_name = 'LINE_SCHEDULING'
2318 THEN
2319 p_on_scheduling_hold := TRUE;
2320 END IF;
2321
2322 END IF;
2323 j := j + 1;
2324
2325 IF j > Warehouse_LineType_Hold.COUNT THEN
2326 EXIT;
2327 END IF;
2328
2329 END LOOP;
2330 END IF;
2331
2332 -- Check Warehouse Shipping Method Holds
2333 IF p_line_rec.ship_from_org_id IS NOT NULL AND
2334 p_line_rec.shipping_method_code IS NOT NULL AND
2335 Warehouse_ShipMethod_Pointer.EXISTS(p_line_rec.ship_from_org_id)
2336 THEN
2337 j := Warehouse_ShipMethod_Pointer(p_line_rec.ship_from_org_id);
2338 WHILE Warehouse_ShipMethod_Hold(j).Entity_Id1 = p_line_rec.ship_from_org_id
2339 LOOP
2340
2341 IF Warehouse_ShipMethod_Hold(j).Entity_Id2 = p_line_rec.shipping_method_code
2342 THEN
2343 Mark_Hold(p_header_id => p_line_rec.header_id,
2344 p_line_id => p_line_rec.line_id,
2345 p_line_number => p_line_rec.line_number,
2346 p_hold_source_id => Warehouse_ShipMethod_Hold(j).hold_source_id,
2347 p_ship_set_name => NULL,
2348 p_arrival_set_name => NULL,
2349 p_activity_name => Warehouse_ShipMethod_Hold(j).activity_name,
2350 p_attribute => 'Warehouse\Shipping Method',
2351 p_top_model_line_id => p_line_rec.top_model_line_id
2352 );
2353 IF Warehouse_ShipMethod_Hold(j).activity_name IS NULL THEN
2354 p_on_generic_hold := TRUE;
2355 ELSIF Warehouse_ShipMethod_Hold(j).activity_name = 'LINE_SCHEDULING'
2356 THEN
2357 p_on_scheduling_hold := TRUE;
2358 END IF;
2359
2360 END IF;
2361 j := j + 1;
2362
2363 IF j > Warehouse_ShipMethod_Hold.COUNT THEN
2364 EXIT;
2365 END IF;
2366
2367 END LOOP;
2368 END IF;
2369
2370 -- Check Warehouse Deliver to Site Holds
2371 IF p_line_rec.ship_from_org_id IS NOT NULL AND
2372 p_line_rec.deliver_to_org_id IS NOT NULL AND
2373 Warehouse_Deliverto_Pointer.EXISTS(p_line_rec.ship_from_org_id)
2374 THEN
2375 j := Warehouse_Deliverto_Pointer(p_line_rec.ship_from_org_id);
2376 WHILE Warehouse_Deliverto_Hold(j).Entity_Id1 = p_line_rec.ship_from_org_id
2377 LOOP
2378
2379 IF Warehouse_Deliverto_Hold(j).Entity_Id2 = p_line_rec.deliver_to_org_id
2380 THEN
2381 Mark_Hold(p_header_id => p_line_rec.header_id,
2382 p_line_id => p_line_rec.line_id,
2383 p_line_number => p_line_rec.line_number,
2384 p_hold_source_id => Warehouse_Deliverto_Hold(j).hold_source_id,
2385 p_ship_set_name => NULL,
2386 p_arrival_set_name => NULL,
2387 p_activity_name => Warehouse_Deliverto_Hold(j).activity_name,
2388 p_attribute => 'Warehouse\Deliver to Site',
2389 p_top_model_line_id => p_line_rec.top_model_line_id
2390 );
2391 IF Warehouse_Deliverto_Hold(j).activity_name IS NULL THEN
2392 p_on_generic_hold := TRUE;
2393 ELSIF Warehouse_Deliverto_Hold(j).activity_name = 'LINE_SCHEDULING'
2394 THEN
2395 p_on_scheduling_hold := TRUE;
2396 END IF;
2397
2398 END IF;
2399 j := j + 1;
2400
2401 IF j > Warehouse_Deliverto_Hold.COUNT THEN
2402 EXIT;
2403 END IF;
2404
2405 END LOOP;
2406 END IF;
2407
2408
2409 -- Check Warehouse Source type Holds
2410 IF p_line_rec.ship_from_org_id IS NOT NULL AND
2411 p_line_rec.source_type_code IS NOT NULL AND
2412 Warehouse_SourceType_Pointer.EXISTS(p_line_rec.ship_from_org_id)
2413 THEN
2414 j := Warehouse_SourceType_Pointer(p_line_rec.ship_from_org_id);
2415 WHILE Warehouse_SourceType_Hold(j).Entity_Id1 = p_line_rec.ship_from_org_id
2416 LOOP
2417
2418 IF Warehouse_SourceType_Hold(j).Entity_Id2 = p_line_rec.source_type_code
2419 THEN
2420 Mark_Hold(p_header_id => p_line_rec.header_id,
2421 p_line_id => p_line_rec.line_id,
2422 p_line_number => p_line_rec.line_number,
2423 p_hold_source_id => Warehouse_SourceType_Hold(j).hold_source_id,
2424 p_ship_set_name => NULL,
2425 p_arrival_set_name => NULL,
2426 p_activity_name => Warehouse_SourceType_Hold(j).activity_name,
2427 p_attribute => 'Warehouse\Source Type',
2428 p_top_model_line_id => p_line_rec.top_model_line_id
2429 );
2430 IF Warehouse_SourceType_Hold(j).activity_name IS NULL THEN
2431 p_on_generic_hold := TRUE;
2432 ELSIF Warehouse_SourceType_Hold(j).activity_name = 'LINE_SCHEDULING'
2433 THEN
2434 p_on_scheduling_hold := TRUE;
2435 END IF;
2436
2437 END IF;
2438 j := j + 1;
2439
2440 IF j > Warehouse_SourceType_Hold.COUNT THEN
2441 EXIT;
2442 END IF;
2443
2444 END LOOP;
2445 END IF;
2446
2447 --ER#7479609 end
2448
2449 END IF;
2450 oe_debug_pub.add('Exitting OE_Bulk_Holds_PVT.Evaluate_Holds'); --ER#7479609 debug
2451 EXCEPTION
2452 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2453 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2454 WHEN OTHERS THEN
2455 OE_BULK_MSG_PUB.Add_Exc_Msg
2456 ( G_PKG_NAME
2457 , 'Evaluate_Holds'
2458 );
2459 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2460 END Evaluate_Holds;
2461
2462 --ER#7479609 end
2463
2464
2465
2466 PROCEDURE Evaluate_Holds(
2467 p_header_id IN NUMBER,
2468 p_line_id IN NUMBER,
2469 p_line_number IN NUMBER,
2470 p_sold_to_org_id IN NUMBER,
2471 p_inventory_item_id IN NUMBER,
2472 p_ship_from_org_id IN NUMBER,
2473 p_invoice_to_org_id IN NUMBER,
2474 p_ship_to_org_id IN NUMBER,
2475 p_top_model_line_id IN NUMBER,
2476 p_ship_set_name IN VARCHAR2,
2477 p_arrival_set_name IN VARCHAR2,
2478 p_check_only_warehouse_holds IN BOOLEAN := FALSE,
2479 p_on_generic_hold OUT NOCOPY BOOLEAN,
2480 p_on_booking_hold OUT NOCOPY BOOLEAN,
2481 p_on_scheduling_hold OUT NOCOPY BOOLEAN
2482 )
2483 IS
2484 i NUMBER;
2485 j NUMBER;
2486 l_activity_name VARCHAR2(30);
2487 BEGIN
2488 -- Set the OUT parameter
2489 p_on_generic_hold := FALSE;
2490 p_on_booking_hold := FALSE;
2491 p_on_scheduling_hold := FALSE;
2492
2493 IF p_line_id IS NULL THEN -- Header Level Holds
2494
2495 -- Check Customer Hold
2496 IF Customer_Pointer.EXISTS(p_sold_to_org_id) THEN
2497
2498 j := Customer_Pointer(p_sold_to_org_id);
2499 WHILE Customer_hold(j).Entity_Id1 = p_sold_to_org_id
2500 LOOP
2501 Mark_Hold(p_header_id => p_header_id,
2502 p_line_id => NULL,
2503 p_line_number => NULL,
2504 p_hold_source_id => Customer_hold(j).hold_source_id,
2505 p_ship_set_name => NULL,
2506 p_arrival_set_name => NULL,
2507 p_activity_name => Customer_hold(j).activity_name,
2508 p_attribute => 'Customer',
2509 p_top_model_line_id => NULL
2510 );
2511 IF Customer_hold(j).activity_name IS NULL THEN
2512 p_on_generic_hold := TRUE;
2513 ELSIF Customer_hold(j).activity_name = 'BOOK_ORDER' THEN
2514 p_on_booking_hold := TRUE;
2515 END IF;
2516 j := j+1;
2517
2518 IF j > Customer_hold.COUNT THEN
2519 EXIT;
2520 END IF;
2521
2522 END LOOP;
2523
2524 END IF;
2525
2526 ELSE -- Line Level Holds
2527
2528 IF p_check_only_warehouse_holds THEN
2529 GOTO Just_WareHouse_Holds;
2530 END IF;
2531
2532 -- Check Item Hold
2533 IF p_inventory_item_id IS NOT NULL AND
2534 Item_Pointer.EXISTS(p_inventory_item_id)
2535 THEN
2536 j := Item_Pointer(p_inventory_item_id);
2537 WHILE item_hold(j).Entity_Id1 = p_inventory_item_id
2538 LOOP
2539 Mark_Hold(p_header_id => p_header_id,
2540 p_line_id => p_line_id,
2541 p_line_number => p_line_number,
2542 p_hold_source_id => Item_Hold(j).hold_source_id,
2543 p_ship_set_name => p_ship_set_name,
2544 p_arrival_set_name => p_arrival_set_name,
2545 p_activity_name => Item_hold(j).activity_name,
2546 p_attribute => 'Item',
2547 p_top_model_line_id => p_top_model_line_id
2548 );
2549 IF item_hold(j).activity_name IS NULL THEN
2550 p_on_generic_hold := TRUE;
2551 ELSIF item_hold(j).activity_name = 'LINE_SCHEDULING' THEN
2552 p_on_scheduling_hold := TRUE;
2553 END IF;
2554 j := j+1;
2555 IF j > Item_Hold.COUNT THEN
2556 EXIT;
2557 END IF;
2558
2559 END LOOP;
2560
2561 END IF;
2562
2563 -- Check Ship_To Hold
2564 IF p_ship_to_org_id IS NOT NULL AND
2565 Ship_To_Pointer.EXISTS(p_ship_to_org_id)
2566 THEN
2567 j := ship_to_Pointer(p_ship_to_org_id);
2568 WHILE Ship_to_hold(j).Entity_Id1 = p_ship_to_org_id
2569 LOOP
2570 Mark_Hold(p_header_id => p_header_id,
2571 p_line_id => p_line_id,
2572 p_line_number => p_line_number,
2573 p_hold_source_id => Ship_to_Hold(j).hold_source_id,
2574 p_ship_set_name => p_ship_set_name,
2575 p_arrival_set_name => p_arrival_set_name,
2576 p_activity_name => Ship_to_hold(j).activity_name,
2577 p_attribute => 'Ship to Site',
2578 p_top_model_line_id => p_top_model_line_id
2579 );
2580 IF Ship_to_hold(j).activity_name IS NULL THEN
2581 p_on_generic_hold := TRUE;
2582 ELSIF Ship_to_hold(j).activity_name = 'LINE_SCHEDULING' THEN
2583 p_on_scheduling_hold := TRUE;
2584 END IF;
2585 j := j+1;
2586 IF j > Ship_to_Hold.COUNT THEN
2587 EXIT;
2588 END IF;
2589
2590 END LOOP;
2591
2592 END IF;
2593
2594 -- Check Bill_To Hold
2595 IF p_invoice_to_org_id IS NOT NULL AND
2596 Bill_To_Pointer.EXISTS(p_invoice_to_org_id)
2597 THEN
2598 j := Bill_To_Pointer(p_invoice_to_org_id);
2599 WHILE Bill_To_hold(j).Entity_Id1 = p_invoice_to_org_id
2600 LOOP
2601 Mark_Hold(p_header_id => p_header_id,
2602 p_line_id => p_line_id,
2603 p_line_number => p_line_number,
2604 p_hold_source_id => Bill_To_hold(j).hold_source_id,
2605 p_ship_set_name => p_ship_set_name,
2606 p_arrival_set_name => p_arrival_set_name,
2607 p_activity_name => Bill_To_hold(j).activity_name,
2608 p_attribute => 'Bill to Site',
2609 p_top_model_line_id => p_top_model_line_id
2610 );
2611 IF Bill_To_hold(j).activity_name IS NULL THEN
2612 p_on_generic_hold := TRUE;
2613 ELSIF Bill_To_hold(j).activity_name = 'LINE_SCHEDULING' THEN
2614 p_on_scheduling_hold := TRUE;
2615 END IF;
2616 j := j+1;
2617 IF j > Bill_to_Hold.COUNT THEN
2618 EXIT;
2619 END IF;
2620
2621 END LOOP;
2622
2623 END IF;
2624
2625
2626 -- Check Item Customer Holds
2627 IF p_inventory_item_id IS NOT NULL AND
2628 p_sold_to_org_id IS NOT NULL AND
2629 Item_Customer_Pointer.EXISTS(p_inventory_item_id)
2630 THEN
2631 j := Item_Customer_Pointer(p_inventory_item_id);
2632 WHILE Item_Customer_hold(j).Entity_Id1 = p_inventory_item_id
2633 LOOP
2634
2635 IF Item_Customer_Hold(j).Entity_Id2 = p_sold_to_org_id
2636 THEN
2637 Mark_Hold(p_header_id => p_header_id,
2638 p_line_id => p_line_id,
2639 p_line_number => p_line_number,
2640 p_hold_source_id => Item_Customer_Hold(j).hold_source_id,
2641 p_ship_set_name => p_ship_set_name,
2642 p_arrival_set_name => p_arrival_set_name,
2643 p_activity_name => Item_Customer_Hold(j).activity_name,
2644 p_attribute => 'Item\Customer',
2645 p_top_model_line_id => p_top_model_line_id
2646 );
2647 IF Item_Customer_Hold(j).activity_name IS NULL THEN
2648 p_on_generic_hold := TRUE;
2649 ELSIF Item_Customer_Hold(j).activity_name = 'LINE_SCHEDULING'
2650 THEN
2651 p_on_scheduling_hold := TRUE;
2652 END IF;
2653
2654 END IF;
2655 j := j + 1;
2656
2657 IF j > Item_Customer_hold.COUNT THEN
2658 EXIT;
2659 END IF;
2660
2661 END LOOP;
2662 END IF;
2663
2664 -- Check Item Shipto Holds
2665 IF p_inventory_item_id IS NOT NULL AND
2666 p_ship_to_org_id IS NOT NULL AND
2667 Item_Shipto_Pointer.EXISTS(p_inventory_item_id)
2668 THEN
2669 j := Item_shipto_Pointer(p_inventory_item_id);
2670 WHILE Item_shipto_hold(j).Entity_Id1 = p_inventory_item_id
2671 LOOP
2672
2673 IF Item_shipto_Hold(j).Entity_Id2 = p_ship_to_org_id
2674 THEN
2675 Mark_Hold(p_header_id => p_header_id,
2676 p_line_id => p_line_id,
2677 p_line_number => p_line_number,
2678 p_hold_source_id => Item_shipto_Hold(j).hold_source_id,
2679 p_ship_set_name => p_ship_set_name,
2680 p_arrival_set_name => p_arrival_set_name,
2681 p_activity_name => Item_shipto_Hold(j).activity_name,
2682 p_attribute => 'Item\Ship to Site',
2683 p_top_model_line_id => p_top_model_line_id
2684 );
2685 IF Item_shipto_Hold(j).activity_name IS NULL THEN
2686 p_on_generic_hold := TRUE;
2687 ELSIF Item_shipto_Hold(j).activity_name = 'LINE_SCHEDULING'
2688 THEN
2689 p_on_scheduling_hold := TRUE;
2690 END IF;
2691
2692 END IF;
2693 j := j + 1;
2694
2695 IF j > Item_shipto_hold.COUNT THEN
2696 EXIT;
2697 END IF;
2698
2699 END LOOP;
2700 END IF;
2701
2702 -- Check Item BillTo Holds
2703 IF p_inventory_item_id IS NOT NULL AND
2704 p_invoice_to_org_id IS NOT NULL AND
2705 Item_Billto_Pointer.EXISTS(p_inventory_item_id)
2706 THEN
2707 j := Item_billto_Pointer(p_inventory_item_id);
2708 WHILE Item_billto_hold(j).Entity_Id1 = p_inventory_item_id
2709 LOOP
2710
2711 IF Item_billto_Hold(j).Entity_Id2 = p_invoice_to_org_id
2712 THEN
2713 Mark_Hold(p_header_id => p_header_id,
2714 p_line_id => p_line_id,
2715 p_line_number => p_line_number,
2716 p_hold_source_id => Item_billto_Hold(j).hold_source_id,
2717 p_ship_set_name => p_ship_set_name,
2718 p_arrival_set_name => p_arrival_set_name,
2719 p_activity_name => Item_billto_Hold(j).activity_name,
2720 p_attribute => 'Item\Bill to Site',
2721 p_top_model_line_id => p_top_model_line_id
2722 );
2723 IF Item_billto_Hold(j).activity_name IS NULL THEN
2724 p_on_generic_hold := TRUE;
2725 ELSIF Item_billto_Hold(j).activity_name = 'LINE_SCHEDULING'
2726 THEN
2727 p_on_scheduling_hold := TRUE;
2728 END IF;
2729
2730 END IF;
2731 j := j + 1;
2732
2733 IF j > Item_billto_hold.COUNT THEN
2734 EXIT;
2735 END IF;
2736
2737 END LOOP;
2738 END IF;
2739
2740 <<Just_WareHouse_Holds>>
2741
2742 -- Check Item Warehouse Holds
2743 IF p_inventory_item_id IS NOT NULL AND
2744 p_ship_from_org_id IS NOT NULL AND
2745 Item_Warehouse_Pointer.EXISTS(p_inventory_item_id)
2746 THEN
2747 j := Item_Warehouse_Pointer(p_inventory_item_id);
2748 WHILE Item_Warehouse_hold(j).Entity_Id1 = p_inventory_item_id
2749 LOOP
2750
2751 IF Item_Warehouse_Hold(j).Entity_Id2 = p_ship_from_org_id
2752 THEN
2753 Mark_Hold(p_header_id => p_header_id,
2754 p_line_id => p_line_id,
2755 p_line_number => p_line_number,
2756 p_hold_source_id => Item_Warehouse_Hold(j).hold_source_id,
2757 p_ship_set_name => p_ship_set_name,
2758 p_arrival_set_name => p_arrival_set_name,
2759 p_activity_name => Item_Warehouse_Hold(j).activity_name,
2760 p_attribute => 'Item\Warehouse',
2761 p_top_model_line_id => p_top_model_line_id
2762 );
2763 IF Item_Warehouse_Hold(j).activity_name IS NULL THEN
2764 p_on_generic_hold := TRUE;
2765 ELSIF Item_Warehouse_Hold(j).activity_name = 'LINE_SCHEDULING'
2766 THEN
2767 p_on_scheduling_hold := TRUE;
2768 END IF;
2769
2770 END IF;
2771 j := j + 1;
2772
2773 IF j > Item_Warehouse_hold.COUNT THEN
2774 EXIT;
2775 END IF;
2776
2777 END LOOP;
2778 END IF;
2779
2780 -- Check Warehouse Hold
2781 IF p_ship_from_org_id IS NOT NULL AND
2782 Warehouse_Pointer.EXISTS(p_ship_from_org_id)
2783 THEN
2784 j := Warehouse_Pointer(p_ship_from_org_id);
2785 WHILE Warehouse_hold(j).Entity_Id1 = p_ship_from_org_id
2786 LOOP
2787 Mark_Hold(p_header_id => p_header_id,
2788 p_line_id => p_line_id,
2789 p_line_number => p_line_number,
2790 p_hold_source_id => Warehouse_hold(j).hold_source_id,
2791 p_ship_set_name => p_ship_set_name,
2792 p_arrival_set_name => p_arrival_set_name,
2793 p_activity_name => Warehouse_hold(j).activity_name,
2794 p_attribute => 'Warehouse',
2795 p_top_model_line_id => p_top_model_line_id
2796 );
2797 IF Warehouse_hold(j).activity_name IS NULL THEN
2798 p_on_generic_hold := TRUE;
2799 ELSIF Warehouse_hold(j).activity_name = 'LINE_SCHEDULING' THEN
2800 p_on_scheduling_hold := TRUE;
2801 END IF;
2802 j := j+1;
2803 IF j > Warehouse_Hold.COUNT THEN
2804 EXIT;
2805 END IF;
2806
2807 END LOOP;
2808
2809 END IF;
2810
2811 -- Check Warehouse Customer Holds
2812 IF p_ship_from_org_id IS NOT NULL AND
2813 p_sold_to_org_id IS NOT NULL AND
2814 Warehouse_Customer_Pointer.EXISTS(p_ship_from_org_id)
2815 THEN
2816 j := Warehouse_Customer_Pointer(p_ship_from_org_id);
2817 WHILE Warehouse_Customer_Hold(j).Entity_Id1 = p_ship_from_org_id
2818 LOOP
2819
2820 IF Warehouse_Customer_Hold(j).Entity_Id2 = p_sold_to_org_id
2821 THEN
2822 Mark_Hold(p_header_id => p_header_id,
2823 p_line_id => p_line_id,
2824 p_line_number => p_line_number,
2825 p_hold_source_id =>Warehouse_Customer_Hold(j).hold_source_id,
2826 p_ship_set_name => p_ship_set_name,
2827 p_arrival_set_name => p_arrival_set_name,
2828 p_activity_name => Warehouse_Customer_Hold(j).activity_name,
2829 p_attribute => 'Warehouse\Customer',
2830 p_top_model_line_id => p_top_model_line_id
2831 );
2832 IF Warehouse_Customer_Hold(j).activity_name IS NULL THEN
2833 p_on_generic_hold := TRUE;
2834 ELSIF Warehouse_Customer_Hold(j).activity_name = 'LINE_SCHEDULING'
2835 THEN
2836 p_on_scheduling_hold := TRUE;
2837 END IF;
2838
2839 END IF;
2840 j := j + 1;
2841
2842 IF j > Warehouse_Customer_Hold.COUNT THEN
2843 EXIT;
2844 END IF;
2845
2846 END LOOP;
2847 END IF;
2848
2849 -- Check Warehouse Shipto Holds
2850 IF p_ship_from_org_id IS NOT NULL AND
2851 p_ship_to_org_id IS NOT NULL AND
2852 Warehouse_shipto_pointer.EXISTS(p_ship_from_org_id)
2853 THEN
2854 j := Warehouse_shipto_pointer(p_ship_from_org_id);
2855 WHILE Warehouse_shipto_Hold(j).Entity_Id1 = p_ship_from_org_id
2856 LOOP
2857
2858 IF Warehouse_shipto_Hold(j).Entity_Id2 = p_ship_to_org_id
2859 THEN
2860 Mark_Hold(p_header_id => p_header_id,
2861 p_line_id => p_line_id,
2862 p_line_number => p_line_number,
2863 p_hold_source_id => Warehouse_shipto_Hold(j).hold_source_id,
2864 p_ship_set_name => p_ship_set_name,
2865 p_arrival_set_name => p_arrival_set_name,
2866 p_activity_name => Warehouse_shipto_Hold(j).activity_name,
2867 p_attribute => 'Warehouse\Ship to Site',
2868 p_top_model_line_id => p_top_model_line_id
2869 );
2870 IF Warehouse_shipto_Hold(j).activity_name IS NULL THEN
2871 p_on_generic_hold := TRUE;
2872 ELSIF Warehouse_shipto_Hold(j).activity_name = 'LINE_SCHEDULING'
2873 THEN
2874 p_on_scheduling_hold := TRUE;
2875 END IF;
2876
2877 END IF;
2878 j := j + 1;
2879
2880 IF j > Warehouse_shipto_Hold.COUNT THEN
2881 EXIT;
2882 END IF;
2883
2884 END LOOP;
2885 END IF;
2886
2887 -- Check Warehouse BillTo Holds
2888 IF p_ship_from_org_id IS NOT NULL AND
2889 p_invoice_to_org_id IS NOT NULL AND
2890 Warehouse_billto_pointer.EXISTS(p_ship_from_org_id)
2891 THEN
2892 j := Warehouse_billto_pointer(p_ship_from_org_id);
2893 WHILE Warehouse_billto_Hold(j).Entity_Id1 = p_ship_from_org_id
2894 LOOP
2895
2896 IF Warehouse_billto_Hold(j).Entity_Id2 = p_invoice_to_org_id
2897 THEN
2898 Mark_Hold(p_header_id => p_header_id,
2899 p_line_id => p_line_id,
2900 p_line_number => p_line_number,
2901 p_hold_source_id => Warehouse_billto_Hold(j).hold_source_id,
2902 p_ship_set_name => p_ship_set_name,
2903 p_arrival_set_name => p_arrival_set_name,
2904 p_activity_name => Warehouse_billto_Hold(j).activity_name,
2905 p_attribute => 'Warehouse\Bill to Site',
2906 p_top_model_line_id => p_top_model_line_id
2907 );
2908 IF Warehouse_billto_Hold(j).activity_name IS NULL THEN
2909 p_on_generic_hold := TRUE;
2910 ELSIF Warehouse_billto_Hold(j).activity_name = 'LINE_SCHEDULING'
2911 THEN
2912 p_on_scheduling_hold := TRUE;
2913 END IF;
2914
2915 END IF;
2916 j := j + 1;
2917
2918 IF j > Warehouse_billto_Hold.COUNT THEN
2919 EXIT;
2920 END IF;
2921
2922 END LOOP;
2923 END IF;
2924
2925 END IF;
2926
2927 EXCEPTION
2928 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2929 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2930 WHEN OTHERS THEN
2931 OE_BULK_MSG_PUB.Add_Exc_Msg
2932 ( G_PKG_NAME
2933 , 'Evaluate_Holds'
2934 );
2935 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2936 END Evaluate_Holds;
2937
2938 PROCEDURE Extend_Holds_Tbl
2939 IS
2940 BEGIN
2941
2942 G_Hold_header_id.EXTEND;
2943 G_Hold_line_id.EXTEND;
2944 G_Hold_source_id.EXTEND;
2945 G_hold_ship_set.EXTEND;
2946 G_hold_arrival_set.EXTEND;
2947 G_hold_top_model_line_id.EXTEND;
2948 G_hold_activity_name.EXTEND;
2949
2950 EXCEPTION
2951 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2953 WHEN OTHERS THEN
2954 OE_BULK_MSG_PUB.Add_Exc_Msg
2955 ( G_PKG_NAME
2956 , 'Extend_Holds_Tbl'
2957 );
2958 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2959 END Extend_Holds_Tbl;
2960
2961 PROCEDURE Create_Holds
2962 IS
2963 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
2964 l_org_id number :=mo_global.get_current_org_id; --5488209
2965 BEGIN
2966
2967 IF l_debug_level > 0 THEN
2968 oe_debug_pub.add('No. of holds to create is '||G_Hold_header_id.COUNT);
2969 oe_debug_pub.add('No. of holds sources to create is '
2970 ||g_hold_source_rec.HOLD_SOURCE_ID.COUNT);
2971 END IF;
2972
2973 FORALL i IN 1..G_Hold_header_id.COUNT
2974 --added org_id in insert for bug 5488209
2975 INSERT INTO OE_ORDER_HOLDS
2976 ( ORDER_HOLD_ID
2977 , LAST_UPDATE_DATE
2978 , LAST_UPDATED_BY
2979 , CREATION_DATE
2980 , CREATED_BY
2981 , LAST_UPDATE_LOGIN
2982 , HOLD_SOURCE_ID
2983 , HEADER_ID
2984 , LINE_ID
2985 , RELEASED_FLAG
2986 , ORG_ID
2987 )
2988 VALUES
2989 ( OE_ORDER_HOLDS_S.NEXTVAL
2990 , SYSDATE
2991 , NVL(FND_GLOBAL.USER_ID, -1)
2992 , SYSDATE
2993 , NVL(FND_GLOBAL.USER_ID, -1)
2994 , NULL
2995 , G_hold_source_id(i)
2996 , G_Hold_header_id(i)
2997 , G_Hold_line_id(i)
2998 , 'N'
2999 , l_org_id
3000 );
3001
3002 -- Clear the Globals.
3003 g_hold_header_id.DELETE;
3004 g_hold_line_id.DELETE;
3005 g_hold_Source_Id.DELETE;
3006 g_hold_ship_set.DELETE;
3007 g_hold_arrival_set.DELETE;
3008 g_hold_top_model_line_id.DELETE;
3009 g_hold_activity_name.DELETE;
3010
3011 IF g_hold_source_rec.HOLD_SOURCE_ID.COUNT > 0 THEN
3012 FORALL i IN 1..g_hold_source_rec.HOLD_SOURCE_ID.COUNT
3013 --added org_id in insert for bug 5488209
3014 INSERT INTO OE_HOLD_SOURCES
3015 ( HOLD_SOURCE_ID
3016 , LAST_UPDATE_DATE
3017 , LAST_UPDATED_BY
3018 , CREATION_DATE
3019 , CREATED_BY
3020 , LAST_UPDATE_LOGIN
3021 , PROGRAM_APPLICATION_ID
3022 , PROGRAM_ID
3023 , PROGRAM_UPDATE_DATE
3024 , REQUEST_ID
3025 , HOLD_ID
3026 , HOLD_ENTITY_CODE
3027 , HOLD_ENTITY_ID
3028 , HOLD_UNTIL_DATE
3029 , RELEASED_FLAG
3030 , ORG_ID
3031 )
3032 VALUES
3033 ( g_hold_source_rec.hold_source_id(i)
3034 , sysdate
3035 , NVL(FND_GLOBAL.USER_ID, -1)
3036 , sysdate
3037 , NVL(FND_GLOBAL.USER_ID, -1)
3038 , NULL
3039 , NULL
3040 , NULL
3041 , NULL
3042 , NULL
3043 , g_hold_source_rec.HOLD_ID(i)
3044 , g_hold_source_rec.HOLD_ENTITY_CODE(i)
3045 , g_hold_source_rec.HOLD_ENTITY_ID(i)
3046 , NULL
3047 , 'N'
3048 , l_org_id
3049 );
3050
3051 g_hold_source_rec.HOLD_ID.DELETE;
3052 g_hold_source_rec.HOLD_SOURCE_ID.DELETE;
3053 g_hold_source_rec.HOLD_ENTITY_CODE.DELETE;
3054 g_hold_source_rec.HOLD_ENTITY_ID.DELETE;
3055 END IF;
3056 EXCEPTION
3057 WHEN OTHERS THEN
3058 IF l_debug_level > 0 THEN
3059 oe_debug_pub.add('In OTHERS error ' || SUBSTR(sqlerrm,1,240), 1);
3060 END IF;
3061 OE_BULK_MSG_PUB.Add_Exc_Msg
3062 ( G_PKG_NAME
3063 , 'Create_Holds'
3064 );
3065 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3066 END Create_Holds;
3067
3068 PROCEDURE Mark_Hold(p_header_id IN NUMBER,
3069 p_line_id IN NUMBER,
3070 p_line_number IN NUMBER,
3071 p_hold_source_id IN NUMBER,
3072 p_ship_set_name IN VARCHAR2,
3073 p_arrival_set_name IN VARCHAR2,
3074 p_activity_name IN VARCHAR2,
3075 p_attribute IN VARCHAR2,
3076 p_top_model_line_id IN NUMBER
3077 )
3078 IS
3079 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3080 i BINARY_INTEGER;
3081 BEGIN
3082
3083 i := G_Hold_header_id.COUNT;
3084 Extend_Holds_Tbl;
3085
3086 G_Hold_header_id(i+1) := p_header_id;
3087 G_Hold_line_id(i+1) := p_line_id;
3088 G_Hold_source_id(i+1) := p_hold_source_id;
3089 G_Hold_ship_set(i+1) := p_ship_set_name;
3090 G_Hold_arrival_set(i+1) := p_arrival_set_name;
3091 G_Hold_top_model_line_id(i+1) := p_top_model_line_id;
3092 G_Hold_activity_name(i+1) := p_activity_name;
3093
3094 IF p_line_id IS NULL THEN
3095 FND_MESSAGE.SET_NAME('ONT','OE_HLD_APPLIED');
3096 ELSE
3097 FND_MESSAGE.SET_NAME('ONT','OE_HLD_APPLIED_LINE');
3098 FND_MESSAGE.SET_TOKEN('LINE_NUMBER',p_line_number);
3099 END IF;
3100 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',p_attribute);
3101 OE_BULK_MSG_PUB.ADD;
3102
3103 EXCEPTION
3104 WHEN OTHERS THEN
3105 IF l_debug_level > 0 THEN
3106 oe_debug_pub.add('In OTHERS error - Mark_Hold ' ||
3107 SUBSTR(sqlerrm,1,240), 1);
3108 END IF;
3109 OE_BULK_MSG_PUB.Add_Exc_Msg
3110 ( G_PKG_NAME
3111 , 'Mark_Hold'
3112 );
3113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3114 END Mark_Hold;
3115
3116 FUNCTION Is_Header_On_Hold(
3117 p_header_id IN NUMBER,
3118 p_hold_index IN OUT NOCOPY NUMBER
3119 ) RETURN BOOLEAN
3120 IS
3121 i NUMBER;
3122 l_old_header_id NUMBER;
3123 BEGIN
3124
3125 IF g_hold_header_id.COUNT > 0 THEN
3126
3127 IF p_hold_index IS NULL THEN
3128 p_hold_index := 1;
3129 END IF;
3130
3131 l_old_header_id := g_hold_header_id(p_hold_index);
3132
3133 FOR i IN p_hold_index..g_hold_header_id.COUNT LOOP
3134
3135 -- IF there are NO Header Level Holds
3136 IF g_hold_line_id(i) IS NOT NULL THEN
3137 RETURN FALSE;
3138 END IF;
3139
3140 -- Since the search is a linear one with incremental header_id
3141 IF g_hold_header_id(i) > p_header_id THEN
3142 RETURN FALSE;
3143 END IF;
3144
3145 IF g_hold_header_id(i) <> l_old_header_id THEN
3146 p_hold_index := i;
3147 END IF;
3148
3149 IF p_header_id = g_hold_header_id(i) AND
3150 g_hold_activity_name(i) IS NULL THEN
3151 RETURN TRUE;
3152 END IF;
3153 l_old_header_id := g_hold_header_id(i);
3154
3155 END LOOP;
3156
3157 END IF;
3158
3159 RETURN FALSE;
3160
3161 EXCEPTION
3162 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3164 WHEN OTHERS THEN
3165 OE_BULK_MSG_PUB.Add_Exc_Msg
3166 ( G_PKG_NAME
3167 , 'Is_Header_On_Hold'
3168 );
3169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3170 END Is_Header_On_Hold;
3171
3172 FUNCTION Is_Line_On_Hold(
3173 p_header_id IN NUMBER,
3174 p_line_id IN NUMBER,
3175 p_top_model_line_id IN NUMBER,
3176 p_ship_set_name IN VARCHAR2,
3177 p_arrival_set_name IN VARCHAR2,
3178 p_hold_index IN OUT NOCOPY NUMBER
3179 ) RETURN BOOLEAN
3180 IS
3181 i BINARY_INTEGER;
3182 l_old_header_id NUMBER;
3183 BEGIN
3184
3185 IF g_hold_header_id.COUNT > 0 THEN
3186
3187 i := 1;
3188 l_old_header_id := g_hold_header_id(p_hold_index);
3189 -- Check if the header is on hold
3190 WHILE g_hold_line_id(i) IS NULL LOOP
3191 IF p_header_id = g_hold_header_id(i) AND
3192 g_hold_activity_name(i) IS NULL THEN
3193 RETURN TRUE;
3194 END IF;
3195 i := i+1;
3196
3197 IF i > g_hold_header_id.COUNT THEN
3198 RETURN FALSE;
3199 END IF;
3200
3201 END LOOP;
3202
3203 -- Header is not on HOLD.
3204
3205 IF p_hold_index IS NULL THEN
3206
3207 IF i <= g_hold_header_id.COUNT THEN
3208 p_hold_index := i;
3209 END IF;
3210
3211 -- IF there are no lines on hold then
3212 IF p_hold_index IS NULL THEN
3213 RETURN FALSE;
3214 END IF;
3215
3216 END IF;
3217
3218 IF p_header_id > g_hold_header_id(g_hold_header_id.LAST) THEN
3219 RETURN FALSE;
3220 END IF;
3221
3222 l_old_header_id := g_hold_header_id(p_hold_index);
3223
3224 FOR i IN p_hold_index..g_hold_header_id.COUNT LOOP
3225
3226 -- Since the search is a linear one with incremental header_id
3227 IF g_hold_header_id(i) > p_header_id THEN
3228 RETURN FALSE;
3229 END IF;
3230
3231 IF g_hold_header_id(i) <> l_old_header_id THEN
3232 p_hold_index := i;
3233 END IF;
3234
3235 -- Check if there is a Match
3236 IF p_header_id = g_hold_header_id(i) AND
3237 (p_top_model_line_id = g_hold_top_model_line_id(i) OR
3238 p_ship_set_name = g_hold_ship_set(i) OR
3239 p_arrival_set_name = g_hold_arrival_set(i)) AND
3240 g_hold_activity_name(i) IS NULL
3241 THEN
3242 RETURN TRUE;
3243 END IF;
3244
3245 l_old_header_id := g_hold_header_id(i);
3246
3247 END LOOP;
3248 END IF;
3249
3250 RETURN FALSE;
3251
3252 EXCEPTION
3253 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3254 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3255 WHEN OTHERS THEN
3256 OE_BULK_MSG_PUB.Add_Exc_Msg
3257 ( G_PKG_NAME
3258 , 'Is_Line_On_Hold'
3259 );
3260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3261 END Is_Line_On_Hold;
3262
3263 PROCEDURE Apply_GSA_Hold(p_header_id IN NUMBER,
3264 p_line_id IN NUMBER,
3265 p_line_number IN NUMBER,
3266 p_hold_id IN NUMBER,
3267 p_ship_set_name IN VARCHAR2,
3268 p_arrival_set_name IN VARCHAR2,
3269 p_activity_name IN VARCHAR2,
3270 p_attribute IN VARCHAR2,
3271 p_top_model_line_id IN NUMBER,
3272 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2 --bug 3735141
3273 )
3274 IS
3275 i BINARY_INTEGER;
3276 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3277 BEGIN
3278 x_return_status := FND_API.G_RET_STS_SUCCESS;
3279 -- Check the count of the Global Table for Hold Source Rec
3280 i := g_hold_source_rec.HOLD_ID.COUNT;
3281
3282 -- Assign memory
3283 Extend_Hold_Source_Rec;
3284
3285 g_hold_source_rec.HOLD_ID(i+1) := p_hold_id;
3286
3287 -- Get the Hold Source Id
3288 SELECT OE_HOLD_SOURCES_S.NEXTVAL
3289 INTO g_hold_source_rec.HOLD_SOURCE_ID(i+1)
3290 FROM DUAL;
3291
3292 g_hold_source_rec.HOLD_ENTITY_CODE(i+1) := 'O';
3293 g_hold_source_rec.HOLD_ENTITY_ID(i+1) := p_header_id; --bug 3716296
3294 -- passing header_id as hold_entity_id instead of line_id
3295
3296 Mark_Hold(p_header_id => p_header_id,
3297 p_line_id => p_line_id,
3298 p_line_number => NULL,
3299 p_hold_source_id => g_hold_source_rec.HOLD_SOURCE_ID(i+1),
3300 p_ship_set_name => NULL,
3301 p_arrival_set_name => NULL,
3302 p_activity_name => NULL,
3303 p_attribute => 'GSA PRICE Violation',
3304 p_top_model_line_id => p_top_model_line_id
3305 );
3306
3307 --bug 3735141
3308 EXCEPTION
3309 WHEN OTHERS THEN
3310 IF l_debug_level > 0 THEN
3311 oe_debug_pub.add('In OTHERS error - Apply GSA Hold ' ||
3312 SUBSTR(sqlerrm,1,240), 1);
3313 END IF;
3314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3315 --bug 3735141
3316
3317 END Apply_GSA_Hold;
3318
3319 PROCEDURE Extend_Hold_Source_Rec
3320 IS
3321 BEGIN
3322 g_hold_source_rec.HOLD_ID.EXTEND;
3323 g_hold_source_rec.HOLD_SOURCE_ID.EXTEND;
3324 g_hold_source_rec.HOLD_ENTITY_CODE.EXTEND;
3325 g_hold_source_rec.HOLD_ENTITY_ID.EXTEND;
3326 END Extend_Hold_Source_Rec;
3327
3328 ------------------------------------------------------------------------
3329 -- FUNCTION Check_for_Holds
3330 -- This API is called by scheduling
3331 --
3332 -- Checks if there are any holds on the order or order line. If
3333 -- order line, then checks for holds on the order that it belongs to.
3334 -- If ATO line, then checks for holds on other lines belonging to the
3335 -- same ATO model. If SMC line, then checks for other lines in the SMC.
3336 -- If included item line then checks for hold on its immediate parent
3337 -- if included item flag is set appropriately in the hold definition.
3338 ------------------------------------------------------------------------
3339 FUNCTION Check_For_Holds(p_header_id IN NUMBER,
3340 p_line_id IN NUMBER,
3341 p_line_index IN NUMBER,
3342 p_header_index IN NUMBER,
3343 p_top_model_line_index IN NUMBER,
3344 p_ship_model_complete_flag IN VARCHAR2,
3345 p_ato_line_index IN NUMBER,
3346 p_ii_parent_line_index IN NUMBER
3347 ) RETURN BOOLEAN
3348 IS
3349
3350 l_result BOOLEAN;
3351 l_header_hold_index BINARY_INTEGER;
3352 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
3353
3354 BEGIN
3355
3356 IF l_debug_level > 0 THEN
3357 oe_debug_pub.add( 'IN OE_BULK_HOLDS_PVT.Check_For_Holds');
3358 oe_debug_pub.add( 'p_line_index is '||p_line_index);
3359 END IF;
3360
3361 -- Initialize API return value to False
3362 l_result := FALSE;
3363
3364 -- First Check if the line is on generic HOLD
3365 IF G_Line_Holds_Tbl.EXISTS(p_line_index) THEN
3366 IF G_Line_Holds_Tbl(p_line_index).On_Generic_Hold = 'Y' THEN
3367
3368 IF l_debug_level > 0 THEN
3369 oe_debug_pub.add( 'Line on Generic Hold' ||p_line_id ) ;
3370 END IF;
3371 RETURN TRUE;
3372
3373 END IF;
3374 END IF;
3375
3376 -- Check if the header is on HOLD.
3377 l_result := Is_Header_On_Hold(p_header_id => p_header_id ,
3378 p_hold_index => l_header_hold_index );
3379
3380 IF l_result THEN
3381 IF l_debug_level > 0 THEN
3382 oe_debug_pub.add( 'HEADER LEVEL HOLD EXISTS' ) ;
3383 END IF;
3384 RETURN l_result;
3385 END IF;
3386
3387 -- Check if the line is part of an ATO model.
3388 IF p_ato_line_index IS NOT NULL AND
3389 p_top_model_line_index IS NOT NULL
3390 THEN
3391 -- Check if the ato_line has been marked for Hold.
3392 IF G_Line_Holds_Tbl.EXISTS(p_ato_line_index) THEN
3393 IF G_Line_Holds_Tbl(p_ato_line_index).Any_ato_line_on_hold = 'Y' THEN
3394
3395 IF l_debug_level > 0 THEN
3396 oe_debug_pub.add( 'Line is part of ATO model and one of the lines
3397 is on Generic Hold') ;
3398 END IF;
3399 RETURN TRUE;
3400 END IF;
3401 END IF;
3402 END IF;
3403
3404 -- Check if the line is part of an SMC Model or Kit-included items.
3405 IF p_top_model_line_index IS NOT NULL AND
3406 p_ship_model_complete_flag = 'Y'
3407 THEN
3408 -- Check if the Top Model line has been marked for Hold.
3409 IF G_Line_Holds_Tbl.EXISTS(p_top_model_line_index) THEN
3410 IF G_Line_Holds_Tbl(p_top_model_line_index).Any_SMC_Line_on_hold = 'Y'
3411 THEN
3412
3413 IF l_debug_level > 0 THEN
3414 oe_debug_pub.add( 'Line is part of SMC model and one of the lines
3415 is on Generic Hold') ;
3416 END IF;
3417 RETURN TRUE;
3418 END IF;
3419 END IF;
3420 END IF;
3421
3422 -- Check if the line is an included item line.
3423 IF p_ii_parent_line_index IS NOT NULL THEN
3424 -- Check if the parent is on generic hold and hold source says that put
3425 -- included items also on hold
3426 IF G_Line_Holds_Tbl.EXISTS(p_ii_parent_line_index) THEN
3427
3428 IF G_Line_Holds_Tbl(p_ii_parent_line_index).On_Generic_Hold = 'Y' AND
3429 G_Line_Holds_Tbl(p_ii_parent_line_index).hold_ii_flag = 'Y'
3430 THEN
3431 IF l_debug_level > 0 THEN
3432 oe_debug_pub.add( 'II Parent line is on Generic Hold') ;
3433 END IF;
3434 RETURN TRUE;
3435 END IF;
3436 END IF;
3437 END IF;
3438 IF l_debug_level > 0 THEN
3439 oe_debug_pub.add( 'Exiting OE_BULK_HOLDS_PVT.Check_For_Holds');
3440 END IF;
3441 RETURN FALSE;
3442
3443 EXCEPTION
3444 WHEN OTHERS THEN
3445 IF l_debug_level > 0 THEN
3446 oe_debug_pub.add( 'In others of OE_BULK_HOLDS_PVT.Check_For_Holds');
3447 END IF;
3448 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3449 THEN
3450 OE_MSG_PUB.Add_Exc_Msg
3451 ( G_PKG_NAME
3452 , 'Check_For_Holds'
3453 );
3454 END IF;
3455
3456 END Check_For_Holds;
3457
3458 END OE_Bulk_Holds_PVT;