1 PACKAGE BODY ASO_SHIPMENT_PVT as
2 /* $Header: asovshpb.pls 120.5 2011/09/05 06:38:46 akushwah ship $ */
3 --
4 -- NAME
5 -- ASO_SHIPMENT_PVT
6 --
7 -- HISTORY
8 --
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_SHIPMENT_PVT';
11
12 PROCEDURE Delete_shipment(
13 P_Api_Version_Number IN NUMBER,
14 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
15 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
16 p_qte_line_rec IN aso_quote_pub.qte_line_rec_type
17 := ASO_QUOTE_PUB.G_MISS_QTE_LINE_REC,
18 p_shipment_rec IN aso_quote_pub.shipment_rec_type,
19 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
20 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
21 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
22 )
23 IS
24 l_api_name CONSTANT VARCHAR2(30) := 'Delete_quote';
25 l_api_version_number CONSTANT NUMBER := 1.0;
26
27 CURSOR c_freight_charges IS
28 SELECT FREIGHT_CHARGE_ID FROM aso_freight_charges
29 WHERE QUOTE_SHIPMENT_ID = p_shipment_rec.shipment_id;
30
31 l_return_status VARCHAR2(240);
32 BEGIN
33 -- Standard Start of API savepoint
34 SAVEPOINT DELETE_SHIPMENT_PVT;
35
36 -- Standard call to check for call compatibility.
37 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
38 p_api_version_number,
39 l_api_name,
40 G_PKG_NAME)
41 THEN
42 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
43 END IF;
44
45
46 -- Initialize message list if p_init_msg_list is set to TRUE.
47 IF FND_API.to_Boolean( p_init_msg_list )
48 THEN
49 FND_MSG_PUB.initialize;
50 END IF;
51
52
53 -- Initialize API return status to SUCCESS
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 --
57 -- Api body
58 --
59 IF p_shipment_rec.reservation_id <> FND_API.G_MISS_NUM AND
60 p_shipment_rec.reservation_id IS NOT NULL THEN
61 ASO_RESERVATION_INT.Delete_Reservation(
62 P_Api_Version_Number => 1.0,
63 P_Init_Msg_List => FND_API.G_FALSE,
64 P_Commit => FND_API.G_FALSE,
65 P_line_Rec => p_qte_line_rec,
66 p_shipment_rec => p_shipment_rec,
67 X_Return_Status => l_Return_Status,
68 X_Msg_Count => X_Msg_Count,
69 X_Msg_Data => X_Msg_Data);
70 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
71 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
72 FND_MESSAGE.Set_Name('ASO', 'ASO_API_ERROR_IN_DELETE_RSV');
73 FND_MSG_PUB.ADD;
74 END IF;
75 END IF;
76 END IF;
77
78 FOR freight_rec IN c_freight_charges LOOP
79 ASO_FREIGHT_CHARGES_PKG.Delete_Row(
80 p_FREIGHT_CHARGE_ID => freight_rec.FREIGHT_CHARGE_ID);
81 END LOOP;
82 -- Invoke table handler(ASO_SHIPMENTS_PKG.Delete_Row)
83 ASO_SHIPMENTS_PKG.Delete_Row(
84 p_SHIPMENT_ID => p_shipment_rec.shipment_id);
85
86
87
88 --
89 -- End of API body
90 --
91
92 -- Standard check for p_commit
93 IF FND_API.to_Boolean( p_commit )
94 THEN
95 COMMIT WORK;
96 END IF;
97
98
99 -- Standard call to get message count and if count is 1, get message info.
100 FND_MSG_PUB.Count_And_Get
101 ( p_count => x_msg_count,
102 p_data => x_msg_data
103 );
104 EXCEPTION
105 WHEN FND_API.G_EXC_ERROR THEN
106 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
107 P_API_NAME => L_API_NAME
108 ,P_PKG_NAME => G_PKG_NAME
109 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
110 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
111 ,X_MSG_COUNT => X_MSG_COUNT
112 ,X_MSG_DATA => X_MSG_DATA
113 ,X_RETURN_STATUS => X_RETURN_STATUS);
114
115 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
116 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
117 P_API_NAME => L_API_NAME
118 ,P_PKG_NAME => G_PKG_NAME
119 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
120 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
121 ,X_MSG_COUNT => X_MSG_COUNT
122 ,X_MSG_DATA => X_MSG_DATA
123 ,X_RETURN_STATUS => X_RETURN_STATUS);
124
125 WHEN OTHERS THEN
126 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
127 P_API_NAME => L_API_NAME
128 ,P_PKG_NAME => G_PKG_NAME
129 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
130 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
131 ,P_SQLCODE => SQLCODE
132 ,P_SQLERRM => SQLERRM
133 ,X_MSG_COUNT => X_MSG_COUNT
134 ,X_MSG_DATA => X_MSG_DATA
135 ,X_RETURN_STATUS => X_RETURN_STATUS);
136 End Delete_Shipment;
137
138
139 FUNCTION Get_invoice_to_party_site_id (
140 p_qte_header_id NUMBER,
141 p_qte_line_id NUMBER
142 ) RETURN NUMBER
143 IS
144 CURSOR c_inv_site1 IS
145 SELECT invoice_to_party_site_id FROM ASO_quote_lines_All
146 WHERE quote_line_id = p_qte_line_id
147 AND quote_header_id = p_qte_header_id;
148
149 CURSOR c_inv_site2 IS
150 SELECT invoice_to_party_site_id FROM ASO_quote_headers_All
151 WHERE quote_header_id = p_qte_header_id;
152
153 l_inv_site_id NUMBER;
154
155 cursor c_root_model_line_id is
156 select quote_line_id
157 from aso_quote_line_details
158 where (config_header_id, config_revision_num) = (select config_header_id,config_revision_num
159 from aso_quote_line_details
160 where quote_line_id = p_qte_line_id)
161 and ref_type_code = 'CONFIG'
162 and ref_line_id is null;
163
164 cursor c_item_type_code is
165 select item_type_code
166 from aso_quote_lines_all
167 where quote_line_id = p_qte_line_id;
168
169 l_model_quote_line_id NUMBER;
170 l_item_type_code VARCHAR2(30);
171
172 BEGIN
173 OPEN c_inv_site1;
174 FETCH c_inv_site1 INTO l_inv_site_id;
175
176 IF c_inv_site1%FOUND and l_inv_site_id IS NOT NULL and l_inv_site_id <> FND_API.G_MISS_NUM THEN
177
178 CLOSE c_inv_site1;
179 return l_inv_site_id;
180
181 ELSE
182
183 CLOSE c_inv_site1;
184
185 open c_item_type_code;
186 fetch c_item_type_code into l_item_type_code;
187
188 IF aso_debug_pub.g_debug_flag = 'Y' THEN
189 aso_debug_pub.add('Get_invoice_to_party_site_id: c_item_type_code: l_item_type_code: '||l_item_type_code);
190 END IF;
191
192 IF c_item_type_code%FOUND and l_item_type_code = 'CFG' THEN
193
194 close c_item_type_code;
195
196 open c_root_model_line_id;
197 fetch c_root_model_line_id into l_model_quote_line_id;
198
199 IF aso_debug_pub.g_debug_flag = 'Y' THEN
200 aso_debug_pub.add('Get_invoice_to_party_site_id: c_root_model_line_id: l_model_quote_line_id: ' ||l_model_quote_line_id);
201 END IF;
202
203 IF c_root_model_line_id%FOUND and l_model_quote_line_id is not null THEN
204
205 close c_root_model_line_id;
206
207 l_inv_site_id := Get_invoice_to_party_site_id(p_qte_header_id, l_model_quote_line_id);
208
209 IF aso_debug_pub.g_debug_flag = 'Y' THEN
210 aso_debug_pub.add('Get_invoice_to_party_site_id: Model l_inv_site_id: '||l_inv_site_id);
211 END IF;
212
213 return l_inv_site_id;
214
215 END IF; --c_root_model_line_id
216
217 close c_root_model_line_id;
218
219 END IF; --c_item_type_code
220
221 END IF;
222
223
224 OPEN c_inv_site2;
225 FETCH c_inv_site2 INTO l_inv_site_id;
226
227 IF c_inv_site2%FOUND and l_inv_site_id IS NOT NULL and l_inv_site_id <> FND_API.G_MISS_NUM THEN
228
229 CLOSE c_inv_site2;
230 return l_inv_site_id;
231
232 END IF; --c_inv_site2
233
234 return l_inv_site_id;
235
236 END Get_invoice_to_party_site_id;
237
238
239 -- Function for getting the site_use_id based on Cust Account ID
240 FUNCTION Get_cust_to_party_site_id ( p_qte_header_id NUMBER,
241 p_qte_line_id NUMBER
242 ) RETURN NUMBER
243 IS
244
245 CURSOR c_inv_site1 IS
246 SELECT invoice_to_party_site_id FROM ASO_quote_lines_All
247 WHERE quote_line_id = p_qte_line_id
248 AND quote_header_id = p_qte_header_id;
249
250 CURSOR c_inv_site2 IS
251 SELECT invoice_to_party_site_id FROM ASO_quote_headers_All
252 WHERE quote_header_id = p_qte_header_id;
253
254 l_inv_site_id NUMBER;
255 l_ship_site_id NUMBER;
256 l_bill_site_use_id NUMBER;
257
258 CURSOR c_cust_id IS
259 SELECT cust_account_id FROM ASO_QUOTE_HEADERS_ALL
260 WHERE quote_header_id = p_qte_header_id;
261
262 CURSOR C_site_use(l_cust_id NUMBER, l_inv_site_id NUMBER) IS
263 SELECT site_use_id
264 FROM hz_cust_site_uses b,hz_cust_acct_sites a
265 WHERE b.cust_acct_site_id = a.cust_acct_site_id
266 AND b.site_use_code = 'BILL_TO' --and b.primary_flag = 'Y'
267 AND a.party_site_id = l_inv_site_id
268 AND a.cust_account_id = l_cust_id;
269
270 CURSOR c_inv_cust_id IS
271 SELECT INVOICE_TO_CUST_ACCOUNT_ID FROM ASO_QUOTE_LINES_ALL
272 WHERE quote_header_id = p_qte_header_id and quote_line_id =p_qte_line_id ;
273
274 CURSOR c_inv_cust_id1 IS
275 SELECT INVOICE_TO_CUST_ACCOUNT_ID FROM ASO_QUOTE_HEADERS_ALL
276 WHERE quote_header_id = p_qte_header_id ;
277
278 l_cust_id NUMBER;
279 p_shipment_id NUMBER := NULL;
280
281 cursor c_item_type_code is
282 select item_type_code
283 from aso_quote_lines_all
284 where quote_line_id = p_qte_line_id;
285
286 cursor c_root_model_line_id is
287 select quote_line_id
288 from aso_quote_line_details
289 where (config_header_id, config_revision_num) = (select config_header_id,config_revision_num
290 from aso_quote_line_details
291 where quote_line_id = p_qte_line_id)
292 and ref_type_code = 'CONFIG'
293 and ref_line_id is null;
294
295
296 l_model_quote_line_id NUMBER;
297 l_item_type_code VARCHAR2(30);
298
299 BEGIN
300
301 IF aso_debug_pub.g_debug_flag = 'Y' THEN
302 aso_debug_pub.add('ASO_SHIPMENT_PVT: Get_cust_to_party_site_id: Begin');
303 END IF;
304
305 OPEN c_inv_site1;
306 FETCH c_inv_site1 INTO l_inv_site_id;
307
308 IF c_inv_site1%FOUND AND l_inv_site_id IS NOT NULL and l_inv_site_id <> FND_API.G_MISS_NUM THEN
309
310 CLOSE c_inv_site1;
311
312 OPEN c_inv_cust_id;
313 FETCH c_inv_cust_id into l_cust_id;
314
315 IF c_inv_cust_id%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
316
317 CLOSE c_inv_cust_id;
318
319 OPEN c_inv_cust_id1;
320 FETCH c_inv_cust_id1 into l_cust_id;
321
322 IF c_inv_cust_id1%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
323
324 CLOSE c_inv_cust_id1;
325
326 --Get the Customer Account from the header
327
328 OPEN c_cust_id;
329 FETCH c_cust_id INTO l_cust_id;
330 CLOSE c_cust_id;
331
332 OPEN C_site_use(l_cust_id , l_inv_site_id);
333 FETCH C_site_use into l_bill_site_use_id;
334 CLOSE C_site_use;
335
336 return l_bill_site_use_id;
337
338 END IF;
339
340 CLOSE c_inv_cust_id1;
341
342 OPEN C_site_use(l_cust_id , l_inv_site_id);
343 FETCH C_site_use into l_bill_site_use_id;
344 CLOSE C_site_use;
345 return l_bill_site_use_id;
346
347 END IF;
348
349 CLOSE c_inv_cust_id;
350
351 --Get the site use id from the HZ_cust_site_uses
352
353 OPEN C_site_use(l_cust_id , l_inv_site_id);
354 FETCH C_site_use into l_bill_site_use_id;
355 CLOSE C_site_use;
356
357 return l_bill_site_use_id;
358
359 ELSE
360
361 IF aso_debug_pub.g_debug_flag = 'Y' THEN
362 aso_debug_pub.add('Get_cust_to_party_site_id: Else c_inv_site1');
363 END IF;
364
365 CLOSE c_inv_site1;
366
367 open c_item_type_code;
368 fetch c_item_type_code into l_item_type_code;
369
370 IF aso_debug_pub.g_debug_flag = 'Y' THEN
371 aso_debug_pub.add('Get_cust_to_party_site_id: c_item_type_code: l_item_type_code: '||l_item_type_code);
372 END IF;
373
374 IF c_item_type_code%FOUND and l_item_type_code = 'CFG' THEN
375
376 close c_item_type_code;
377
378 open c_root_model_line_id;
379 fetch c_root_model_line_id into l_model_quote_line_id;
380
381 IF aso_debug_pub.g_debug_flag = 'Y' THEN
382 aso_debug_pub.add('Get_cust_to_party_site_id: c_root_model_line_id: l_model_quote_line_id: '||l_model_quote_line_id);
383 END IF;
384
385 IF c_root_model_line_id%FOUND and l_model_quote_line_id is not null THEN
386
387 close c_root_model_line_id;
388
389 l_bill_site_use_id := Get_cust_to_party_site_id(p_qte_header_id, l_model_quote_line_id);
390
391 return l_bill_site_use_id;
392
393 END IF; --c_root_model_line_id
394
395 close c_root_model_line_id;
396
397 END IF; --c_item_type_code
398
399 close c_item_type_code;
400
401 END IF; --c_inv_site1
402
403
404 OPEN c_inv_site2;
405 FETCH c_inv_site2 INTO l_inv_site_id;
406
407 IF c_inv_site2%FOUND AND l_inv_site_id IS NOT NULL and l_inv_site_id <> FND_API.G_MISS_NUM THEN
408
409 CLOSE c_inv_site2;
410
411 OPEN c_inv_cust_id;
412 FETCH c_inv_cust_id into l_cust_id;
413
414 IF c_inv_cust_id%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
415
416 CLOSE c_inv_cust_id;
417 OPEN c_inv_cust_id1;
418 FETCH c_inv_cust_id1 into l_cust_id;
419
420 IF c_inv_cust_id1%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
421
422 CLOSE c_inv_cust_id1;
423
424 --Get the Customer Account from the header
425 OPEN c_cust_id;
426 FETCH c_cust_id INTO l_cust_id;
427 CLOSE c_cust_id;
428
429 OPEN C_site_use(l_cust_id , l_inv_site_id);
430 FETCH C_site_use into l_bill_site_use_id;
431 CLOSE C_site_use;
432
433 return l_bill_site_use_id;
434
435 END IF; --c_inv_cust_id1
436
437 CLOSE c_inv_cust_id1;
438
439 OPEN C_site_use(l_cust_id , l_inv_site_id);
440 FETCH C_site_use into l_bill_site_use_id;
441 CLOSE C_site_use;
442
443 return l_bill_site_use_id;
444
445 END IF; --c_inv_cust_id
446
447 CLOSE c_inv_cust_id;
448
449 --Get the site use id from the HZ_cust_site_uses
450
451 OPEN C_site_use(l_cust_id , l_inv_site_id);
452 FETCH C_site_use into l_bill_site_use_id;
453 CLOSE C_site_use;
454
455 return l_bill_site_use_id;
456
457 END IF; --c_inv_site2
458
459 close c_inv_site2;
460
461 return l_bill_site_use_id;
462
463 END Get_cust_to_party_site_id;
464
465
466
467 FUNCTION Get_Ship_To_site_Id (
468 p_qte_header_id NUMBER,
469 p_qte_line_id NUMBER,
470 p_shipment_id NUMBER) RETURN NUMBER
471 IS
472 CURSOR c_ship_site1 IS
473 SELECT ship_to_party_site_id, SHIP_TO_CUST_ACCOUNT_ID FROM ASO_SHIPMENTS
474 WHERE shipment_id = p_shipment_id AND quote_line_id =
475 p_qte_line_id AND quote_header_id = p_qte_header_id;
476
477 /* Commented for Bug 12938390(11i Bug 10253993)
478 -- performance fix for bug 5596369
479 CURSOR c_ship_site2 IS
480 SELECT ship_to_party_site_id , SHIP_TO_CUST_ACCOUNT_ID
481 FROM ASO_SHIPMENTS
482 WHERE quote_header_id = p_qte_header_id
483 and shipment_id =p_shipment_id
484 AND quote_line_id IS NULL;
485 */
486
487 /* Cursor c_ship_site2 modified for Bug 12938390 (11i Bug 10253993) */
488 CURSOR c_ship_site2(p_ship_id Number) IS
489 SELECT ship_to_party_site_id , SHIP_TO_CUST_ACCOUNT_ID
490 FROM ASO_SHIPMENTS
491 WHERE quote_header_id = p_qte_header_id
492 AND shipment_id = p_ship_id
493 AND quote_line_id IS NULL;
494
495 l_ship_site_id NUMBER;
496 l_ship_cust_acct_id NUMBER;
497 l_ship_site_use_id NUMBER;
498 CURSOR c_cust_id IS
499 SELECT cust_account_id FROM ASO_QUOTE_HEADERS_ALL
500 WHERE quote_header_id = p_qte_header_id;
501
502 CURSOR c_ship_cust_id IS
503 SELECT SHIP_TO_CUST_ACCOUNT_ID FROM ASO_SHIPMENTS
504 WHERE quote_header_id = p_qte_header_id and quote_line_id =p_qte_line_id and shipment_id =p_shipment_id;
505
506 /* Commented for Bug 12938390(11i Bug 12426838)
507 -- bug 5596369
508 CURSOR c_ship_cust_id1 IS
509 SELECT SHIP_TO_CUST_ACCOUNT_ID
510 FROM ASO_SHIPMENTS
511 WHERE quote_header_id = p_qte_header_id
512 and shipment_id =p_shipment_id
513 and quote_line_id IS NULL;
514 */
515
516 /* Cursor c_ship_cust_id1 modified for Bug 12938390(11i Bug 12426838) */
517 CURSOR c_ship_cust_id1(p_ship_id Number) IS
518 SELECT SHIP_TO_CUST_ACCOUNT_ID
519 FROM ASO_SHIPMENTS
520 WHERE quote_header_id = p_qte_header_id
521 and shipment_id = p_ship_id
522 and quote_line_id IS NULL;
523
524 CURSOR C_site_use(l_cust_id NUMBER, l_ship_site_id NUMBER) IS
525 SELECT site_use_id
526 FROM hz_cust_site_uses b,hz_cust_acct_sites a
527 WHERE b.cust_acct_site_id = a.cust_acct_site_id
528 AND b.site_use_code = 'SHIP_TO' --and b.primary_flag = 'Y'
529 AND a.party_site_id = l_ship_site_id
530 AND a.cust_account_id = l_cust_id;
531
532 cursor c_root_model_line_id is
533 select quote_line_id
534 from aso_quote_line_details
535 where (config_header_id, config_revision_num) = (select config_header_id,config_revision_num
536 from aso_quote_line_details
537 where quote_line_id = p_qte_line_id)
538 and ref_type_code = 'CONFIG'
539 and ref_line_id is null;
540
541 cursor c_item_type_code is
542 select item_type_code
543 from aso_quote_lines_all
544 where quote_line_id = p_qte_line_id;
545
546 cursor c_shipment_id (p_line_id NUMBER) is
547 select shipment_id
548 from aso_shipments
549 where quote_line_id = p_line_id
550 and quote_header_id = p_qte_header_id;
551
552 l_cust_id NUMBER;
553 l_inv_cust_id NUMBER;
554 l_return_status VARCHAR2(1);
555 l_msg_count number;
556 l_msg_data VARCHAR2(2000);
557
558 l_item_type_code VARCHAR2(30);
559 l_model_quote_line_id NUMBER;
560 l_model_shipment_id NUMBER;
561
562 /* Start : Code change for Bug 12938390(11i Bug 10253993) */
563 CURSOR C_SHIPMENT IS
564 SELECT SHIPMENT_ID
565 FROM ASO_SHIPMENTS
566 WHERE quote_header_id = p_qte_header_id
567 AND quote_line_id IS NULL;
568
569 l_shipment_id NUMBER;
570 /* End : Code change for Bug 12938390(11i Bug 10253993) */
571
572 BEGIN
573 IF aso_debug_pub.g_debug_flag = 'Y' THEN
574 aso_debug_pub.add('ASO_SHIPMENT_PVT:p_qte_header_id ' || p_qte_header_id, 1, 'N');
575 aso_debug_pub.add('ASO_SHIPMENT_PVT:quote_line_id ' || p_qte_line_id, 1, 'N');
576 aso_debug_pub.add('ASO_SHIPMENT_PVT:shipment_id ' || p_shipment_id, 1, 'N');
577 END IF;
578
579 OPEN c_ship_site1;
580 FETCH c_ship_site1 INTO l_ship_site_id, l_ship_cust_acct_id;
581
582 IF aso_debug_pub.g_debug_flag = 'Y' THEN
583 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site1 l_ship_site_id ' || l_ship_site_id, 1, 'N');
584 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site1 l_ship_cust_acct_id ' || l_ship_cust_acct_id, 1, 'N');
585 END IF;
586
587 /* Start : Code change for Bug 12938390(11i Bug 12426838) , changed position of this cursor */
588 Open C_SHIPMENT;
589 Fetch C_SHIPMENT INTO l_shipment_id;
590 Close C_SHIPMENT;
591
592 aso_debug_pub.add('ASO_SHIPMENT_PVT: At the start , l_shipment_id : '||l_shipment_id);
593
594 /* End : Code change for Bug 12938390(11i Bug 12426838) */
595
596 IF c_ship_site1%FOUND and l_ship_site_id IS NOT NULL and l_ship_site_id <> FND_API.G_MISS_NUM THEN
597
598 CLOSE c_ship_site1;
599
600 --Get the ship_to_cust_account if present
601 OPEN c_ship_cust_id;
602 FETCH c_ship_cust_id into l_cust_id;
603
604 IF aso_debug_pub.g_debug_flag = 'Y' THEN
605 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_cust_id l_cust_id ' || l_cust_id, 1, 'N');
606 END IF;
607
608 IF c_ship_cust_id%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
609
610 CLOSE c_ship_cust_id;
611
612 --OPEN c_ship_cust_id1;
613 OPEN c_ship_cust_id1(l_shipment_id); -- Code change done for Bug 12938390(11i Bug 12426838)
614 FETCH c_ship_cust_id1 into l_cust_id;
615
616 IF aso_debug_pub.g_debug_flag = 'Y' THEN
617 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_cust_id1 l_cust_id ' || l_cust_id, 1, 'N');
618 END IF;
619
620 IF c_ship_cust_id1%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
621
622 CLOSE c_ship_cust_id1;
623
624 -- Get the Customer Account from the header
625 OPEN c_cust_id;
626 FETCH c_cust_id INTO l_cust_id;
627 CLOSE c_cust_id;
628
629 IF aso_debug_pub.g_debug_flag = 'Y' THEN
630 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_cust_id l_cust_id ' || l_cust_id, 1, 'N');
631 END IF;
632
633 OPEN C_site_use(l_cust_id , l_ship_site_id);
634 FETCH C_site_use into l_ship_site_use_id;
635
636 IF aso_debug_pub.g_debug_flag = 'Y' THEN
637 aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use1 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
638 END IF;
639 CLOSE C_site_use;
640
641 return l_ship_site_use_id;
642
643 END IF;
644
645 CLOSE c_ship_cust_id1;
646
647 OPEN C_site_use(l_cust_id , l_ship_site_id);
648 FETCH C_site_use into l_ship_site_use_id;
649
650 IF aso_debug_pub.g_debug_flag = 'Y' THEN
651 aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use2 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
652 END IF;
653 CLOSE C_site_use;
654
655 return l_ship_site_use_id;
656
657 END IF;
658
659 CLOSE c_ship_cust_id;
660
661
662 --Get the site use id from the HZ_cust_site_uses
663
664 OPEN C_site_use(l_cust_id , l_ship_site_id);
665 FETCH C_site_use into l_ship_site_use_id;
666
667 IF aso_debug_pub.g_debug_flag = 'Y' THEN
668 aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use3 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
669 END IF;
670 CLOSE C_site_use;
671
672 return l_ship_site_use_id;
673
674 ELSE
675
676 CLOSE c_ship_site1;
677
678 open c_item_type_code;
679 fetch c_item_type_code into l_item_type_code;
680
681 IF aso_debug_pub.g_debug_flag = 'Y' THEN
682 aso_debug_pub.add('Get_Ship_To_site_Id: c_item_type_code: l_item_type_code: '||l_item_type_code);
683 END IF;
684
685 IF c_item_type_code%FOUND and l_item_type_code = 'CFG' THEN
686
687 close c_item_type_code;
688
689 open c_root_model_line_id;
690 fetch c_root_model_line_id into l_model_quote_line_id;
691
692 IF aso_debug_pub.g_debug_flag = 'Y' THEN
693 aso_debug_pub.add('Get_Ship_To_site_Id: c_root_model_line_id: l_model_quote_line_id: '||l_model_quote_line_id);
694 END IF;
695
696 IF c_root_model_line_id%FOUND and l_model_quote_line_id is not null THEN
697
698 close c_root_model_line_id;
699
700 open c_shipment_id(l_model_quote_line_id);
701 fetch c_shipment_id into l_model_shipment_id;
702
703 IF aso_debug_pub.g_debug_flag = 'Y' THEN
704 aso_debug_pub.add('Get_Ship_To_site_Id: c_shipment_id: l_model_shipment_id: '||l_model_shipment_id);
705 END IF;
706
707 IF c_shipment_id%FOUND and l_model_shipment_id is not null THEN
708
709 close c_shipment_id;
710
711 l_ship_site_use_id := Get_Ship_To_site_Id(p_qte_header_id, l_model_quote_line_id, l_model_shipment_id);
712 IF aso_debug_pub.g_debug_flag = 'Y' THEN
713 aso_debug_pub.add('Get_Ship_To_site_Id: l_ship_site_use_id: '||l_ship_site_use_id);
714 END IF;
715
716 return l_ship_site_use_id;
717
718 END IF; --c_shipment_id
719
720 close c_shipment_id;
721
722 END IF; --c_root_model_line_id
723
724 close c_root_model_line_id;
725
726 END IF; --c_item_type_code
727
728 close c_item_type_code;
729
730 END IF; --c_ship_site1
731
732 /* Start : Code change for Bug 12938390(11i Bug 10253993) , changed position of this cursor due to bug 12938390 (11i Bug 12426838)
733 Open C_SHIPMENT;
734 Fetch C_SHIPMENT INTO l_shipment_id;
735 Close C_SHIPMENT;
736
737 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site2 l_shipment_id : '||l_shipment_id); */
738
739 OPEN c_ship_site2(l_shipment_id);
740 /* End : Code change for Bug 12938390(11i Bug 10253993) */
741
742 --OPEN c_ship_site2;
743 FETCH c_ship_site2 INTO l_ship_site_id, l_ship_cust_acct_id;
744
745 IF aso_debug_pub.g_debug_flag = 'Y' THEN
746 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site2 l_ship_site_id ' || l_ship_site_id, 1, 'N');
747 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site2 l_ship_cust_acct_id ' || l_ship_cust_acct_id, 1, 'N');
748 END IF;
749
750 IF c_ship_site2%FOUND AND l_ship_site_id IS NOT NULL and l_ship_site_id <> FND_API.G_MISS_NUM THEN
751
752 CLOSE c_ship_site2;
753 --Get the ship_to_cust_account if present
754 OPEN c_ship_cust_id;
755 FETCH c_ship_cust_id into l_cust_id;
756
757 IF aso_debug_pub.g_debug_flag = 'Y' THEN
758 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_cust_id l_cust_id ' || l_cust_id, 1, 'N');
759 END IF;
760
761 IF c_ship_cust_id%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
762
763 CLOSE c_ship_cust_id;
764
765 -- OPEN c_ship_cust_id1;
766 OPEN c_ship_cust_id1(l_shipment_id); -- Code change for Bug 12938390(11i Bug 12426838)
767
768 FETCH c_ship_cust_id1 into l_cust_id;
769
770 IF aso_debug_pub.g_debug_flag = 'Y' THEN
771 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_cust_id1 l_cust_id ' || l_cust_id, 1, 'N');
772 END IF;
773
774 IF c_ship_cust_id1%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
775
776 CLOSE c_ship_cust_id1;
777 --Get the Customer Account from the header
778 OPEN c_cust_id;
779 FETCH c_cust_id INTO l_cust_id;
780 CLOSE c_cust_id;
781
782 IF aso_debug_pub.g_debug_flag = 'Y' THEN
783 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_cust_id l_cust_id ' || l_cust_id, 1, 'N');
784 END IF;
785
786 OPEN C_site_use(l_cust_id , l_ship_site_id);
787 FETCH C_site_use into l_ship_site_use_id;
788
789 IF aso_debug_pub.g_debug_flag = 'Y' THEN
790 aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use4 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
791 END IF;
792
793 CLOSE C_site_use;
794 return l_ship_site_use_id;
795
796 END IF;
797
798 CLOSE c_ship_cust_id1;
799
800 OPEN C_site_use(l_cust_id , l_ship_site_id);
801 FETCH C_site_use into l_ship_site_use_id;
802
803 IF aso_debug_pub.g_debug_flag = 'Y' THEN
804 aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use5 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
805 END IF;
806
807 CLOSE C_site_use;
808 return l_ship_site_use_id;
809
810 END IF;
811 CLOSE c_ship_cust_id;
812
813 --Get the site use id from the HZ_cust_site_uses
814
815 OPEN C_site_use(l_cust_id , l_ship_site_id);
816 FETCH C_site_use into l_ship_site_use_id;
817
818 IF aso_debug_pub.g_debug_flag = 'Y' THEN
819 aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use6 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
820 END IF;
821 CLOSE C_site_use;
822
823 return l_ship_site_use_id;
824
825 END IF;
826
827 CLOSE c_ship_site2;
828
829 return l_ship_site_use_id;
830
831 END Get_Ship_To_site_Id;
832
833
834 FUNCTION Get_Ship_To_party_site_Id (
835 p_qte_header_id NUMBER,
836 p_qte_line_id NUMBER,
837 p_shipment_id NUMBER) RETURN NUMBER
838 IS
839 CURSOR c_ship_site1 IS
840 SELECT ship_to_party_site_id FROM ASO_SHIPMENTS
841 WHERE shipment_id = p_shipment_id
842 AND quote_line_id = p_qte_line_id
843 AND quote_header_id = p_qte_header_id;
844
845 CURSOR c_ship_site2 IS
846 SELECT ship_to_party_site_id FROM ASO_SHIPMENTS
847 WHERE quote_line_id IS NULL AND quote_header_id = p_qte_header_id;
848
849 l_ship_site_id NUMBER;
850
851 cursor c_root_model_line_id is
852 select quote_line_id
853 from aso_quote_line_details
854 where (config_header_id, config_revision_num) = (select config_header_id,config_revision_num
855 from aso_quote_line_details
856 where quote_line_id = p_qte_line_id)
857 and ref_type_code = 'CONFIG'
858 and ref_line_id is null;
859
860 cursor c_item_type_code is
861 select item_type_code
862 from aso_quote_lines_all
863 where quote_line_id = p_qte_line_id;
864
865 cursor c_shipment_id (p_line_id NUMBER) is
866 select shipment_id
867 from aso_shipments
868 where quote_line_id = p_line_id
869 and quote_header_id = p_qte_header_id;
870
871 l_model_quote_line_id NUMBER;
872 l_item_type_code VARCHAR2(30);
873 l_model_shipment_id NUMBER;
874
875 BEGIN
876
877 OPEN c_ship_site1;
878 FETCH c_ship_site1 INTO l_ship_site_id;
879
880 IF c_ship_site1%FOUND and l_ship_site_id IS NOT NULL and l_ship_site_id <> FND_API.G_MISS_NUM THEN
881
882 CLOSE c_ship_site1;
883 return l_ship_site_id;
884
885 ELSE
886
887 CLOSE c_ship_site1;
888
889 open c_item_type_code;
890 fetch c_item_type_code into l_item_type_code;
891
892 IF aso_debug_pub.g_debug_flag = 'Y' THEN
893 aso_debug_pub.add('Get_Ship_To_party_site_Id: c_item_type_code: l_item_type_code: '||l_item_type_code);
894 END IF;
895
896 IF c_item_type_code%FOUND and l_item_type_code = 'CFG' THEN
897
898 close c_item_type_code;
899
900 open c_root_model_line_id;
901 fetch c_root_model_line_id into l_model_quote_line_id;
902
903 IF aso_debug_pub.g_debug_flag = 'Y' THEN
904 aso_debug_pub.add('Get_Ship_To_party_site_Id: c_root_model_line_id: l_model_quote_line_id: '||l_model_quote_line_id);
905 END IF;
906
907 IF c_root_model_line_id%FOUND and l_model_quote_line_id is not null THEN
908
909 close c_root_model_line_id;
910
911 open c_shipment_id(l_model_quote_line_id);
912 fetch c_shipment_id into l_model_shipment_id;
913
914 IF aso_debug_pub.g_debug_flag = 'Y' THEN
915 aso_debug_pub.add('Get_Ship_To_party_site_Id: c_shipment_id: l_model_shipment_id: '||l_model_shipment_id);
916 END IF;
917
918 IF c_shipment_id%FOUND and l_model_shipment_id is not null THEN
919
920 close c_shipment_id;
921
922 l_ship_site_id := Get_Ship_To_party_site_Id(p_qte_header_id, l_model_quote_line_id, l_model_shipment_id);
923
924 IF aso_debug_pub.g_debug_flag = 'Y' THEN
925 aso_debug_pub.add('Get_Ship_To_party_site_Id: l_ship_site_id: '||l_ship_site_id);
926 END IF;
927
928 return l_ship_site_id;
929
930 END IF; --c_shipment_id
931
932 close c_shipment_id;
933
934 END IF; --c_root_model_line_id
935
936 close c_root_model_line_id;
937
938 END IF; --c_item_type_code
939
940 END IF; --c_ship_site1
941
942
943 OPEN c_ship_site2;
944 FETCH c_ship_site2 INTO l_ship_site_id;
945
946 IF c_ship_site2%FOUND and l_ship_site_id IS NOT NULL and l_ship_site_id <> FND_API.G_MISS_NUM THEN
947
948 CLOSE c_ship_site2;
949 return l_ship_site_id;
950
951 END IF; --c_ship_site2
952
953 return l_ship_site_id;
954
955 END Get_Ship_To_party_site_Id;
956
957
958 FUNCTION Get_party_name (
959 p_party_id NUMBER,
960 p_party_type VARCHAR2
961 ) RETURN VARCHAR2
962 IS
963 CURSOR C1 IS
964 select HP.party_id,HP.party_name
965 from hz_relationships HPR,hz_parties HP where hpr.party_id = p_party_id
966 and hp.party_id=HPR.object_id
967 and SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
968 and OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
969 and HPR.directional_flag = 'B'; -- Added for bug 8634067
970
971 CURSOR C2 IS
972 select party_id,party_name
973 from hz_parties HP where party_id = p_party_id;
974 l_party_name VARCHAR2(360);
975 l_party_id NUMBER;
976 BEGIN
977 IF p_party_type = 'PARTY_RELATIONSHIP' THEN
978 OPEN C1;
979 FETCH C1 INTO l_party_id,l_party_name;
980 IF C1%NOTFOUND OR l_party_name IS NULL THEN
981 CLOSE C1;
982 l_party_name := NULL;
983 RETURN l_party_name;
984 END IF;
985 CLOSE C1;
986 RETURN l_party_name;
987 ELSE
988 OPEN C2;
989 FETCH C2 INTO l_party_id,l_party_name;
990 IF C2%NOTFOUND OR l_party_name IS NULL THEN
991 CLOSE C2;
992 l_party_name := NULL;
993 RETURN l_party_name;
994 END IF;
995 CLOSE C2;
996 RETURN l_party_name;
997 END IF;
998 END Get_party_name;
999
1000
1001 FUNCTION Get_party_first_name (
1002 p_party_id NUMBER,
1003 p_party_type VARCHAR2
1004 ) RETURN VARCHAR2
1005 IS
1006 CURSOR C1 IS
1007 select HP.party_id,HP.person_first_name
1008 from hz_relationships HPR,hz_parties HP where hpr.party_id = p_party_id
1009 and hp.party_id=HPR.subject_id
1010 and SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
1011 and OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
1012 and HPR.directional_flag = 'F'; -- 8634067
1013
1014 CURSOR C2 IS
1015 select party_id,person_first_name
1016 from hz_parties HP where party_id = p_party_id;
1017 l_f_party_name VARCHAR2(150);
1018 l_party_id NUMBER;
1019 BEGIN
1020 IF p_party_type = 'PARTY_RELATIONSHIP' THEN
1021 OPEN C1;
1022 FETCH C1 INTO l_party_id,l_f_party_name;
1023 IF C1%NOTFOUND OR l_f_party_name IS NULL THEN
1024 CLOSE C1;
1025 l_f_party_name := NULL;
1026 RETURN l_f_party_name;
1027 END IF;
1028 CLOSE C1;
1029 RETURN l_f_party_name;
1030 ELSE
1031 OPEN C2;
1032 FETCH C2 INTO l_party_id,l_f_party_name;
1033 IF C2%NOTFOUND OR l_f_party_name IS NULL THEN
1034 CLOSE C2;
1035 l_f_party_name := NULL;
1036 RETURN l_f_party_name;
1037 END IF;
1038 CLOSE C2;
1039 RETURN l_f_party_name;
1040 END IF;
1041 END Get_party_first_name;
1042
1043
1044 FUNCTION Get_party_mid_name (
1045 p_party_id NUMBER,
1046 p_party_type VARCHAR2
1047 ) RETURN VARCHAR2
1048 IS
1049 CURSOR C1 IS
1050 select HP.party_id,HP.person_middle_name
1051 from hz_relationships HPR,hz_parties HP where hpr.party_id = p_party_id
1052 and hp.party_id=HPR.subject_id
1053 and SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
1054 and OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
1055 and HPR.directional_flag = 'F'; -- 8634067
1056
1057 CURSOR C2 IS
1058 select party_id,person_middle_name
1059 from hz_parties HP where party_id = p_party_id;
1060 l_m_party_name VARCHAR2(60);
1061 l_party_id NUMBER;
1062 BEGIN
1063 IF p_party_type = 'PARTY_RELATIONSHIP' THEN
1064 OPEN C1;
1065 FETCH C1 INTO l_party_id,l_m_party_name;
1066 IF C1%NOTFOUND OR l_m_party_name IS NULL THEN
1067 CLOSE C1;
1068 l_m_party_name := NULL;
1069 RETURN l_m_party_name;
1070 END IF;
1071 CLOSE C1;
1072 RETURN l_m_party_name;
1073 ELSE
1074 OPEN C2;
1075 FETCH C2 INTO l_party_id,l_m_party_name;
1076 IF C2%NOTFOUND OR l_m_party_name IS NULL THEN
1077 CLOSE C2;
1078 l_m_party_name := NULL;
1079 RETURN l_m_party_name;
1080 END IF;
1081 CLOSE C2;
1082 RETURN l_m_party_name;
1083 END IF;
1084 END Get_party_mid_name;
1085
1086 FUNCTION Get_party_last_name (
1087 p_party_id NUMBER,
1088 p_party_type VARCHAR2
1089 ) RETURN VARCHAR2
1090 IS
1091 CURSOR C1 IS
1092 select HP.party_id,HP.person_last_name
1093 from hz_relationships HPR,hz_parties HP where hpr.party_id = p_party_id
1094 and hp.party_id=HPR.subject_id
1095 and SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
1096 and OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
1097 and HPR.directional_flag = 'F'; -- 8634067
1098
1099 CURSOR C2 IS
1100 select party_id,person_last_name
1101 from hz_parties HP where party_id = p_party_id;
1102 l_l_party_name VARCHAR2(150);
1103 l_party_id NUMBER;
1104 BEGIN
1105 IF p_party_type = 'PARTY_RELATIONSHIP' THEN
1106 OPEN C1;
1107 FETCH C1 INTO l_party_id,l_l_party_name;
1108 IF C1%NOTFOUND OR l_l_party_name IS NULL THEN
1109 CLOSE C1;
1110 l_l_party_name := NULL;
1111 RETURN l_l_party_name;
1112 END IF;
1113 CLOSE C1;
1114 RETURN l_l_party_name;
1115 ELSE
1116 OPEN C2;
1117 FETCH C2 INTO l_party_id,l_l_party_name;
1118 IF C2%NOTFOUND OR l_l_party_name IS NULL THEN
1119 CLOSE C2;
1120 l_l_party_name := NULL;
1121 RETURN l_l_party_name;
1122 END IF;
1123 CLOSE C2;
1124 RETURN l_l_party_name;
1125 END IF;
1126 END Get_party_last_name;
1127
1128
1129 FUNCTION Get_ship_from_org_id (
1130 p_qte_header_id NUMBER,
1131 p_qte_line_id NUMBER
1132 ) RETURN NUMBER
1133
1134 IS
1135
1136 CURSOR c_line_shipment IS
1137 SELECT ship_from_org_id FROM ASO_shipments
1138 WHERE quote_line_id = p_qte_line_id
1139 AND quote_header_id = p_qte_header_id;
1140
1141
1142 CURSOR c_header_shipment IS
1143 SELECT ship_from_org_id FROM ASO_shipments
1144 WHERE quote_header_id = p_qte_header_id
1145 AND quote_line_id IS NULL;
1146
1147 l_ship_from_org_id NUMBER;
1148
1149
1150 Begin
1151 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1152 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_from_org_id: p_qte_header_id :'||p_qte_header_id, 1, 'N');
1153 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_from_org_id: p_qte_line_id :'||p_qte_line_id, 1, 'N');
1154 END IF;
1155
1156 -- Look for line-shipment level ship_from_org_id
1157
1158 open c_line_shipment;
1159 fetch c_line_shipment into l_ship_from_org_id;
1160
1161 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1162 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_from_org_id: c_line_shipment: l_ship_from_org_id'||l_ship_from_org_id, 1, 'N');
1163 END IF;
1164
1165 -- Line-shipment level ship_from_org_id doesn't exist then look for header-shipment level ship_from_org_id
1166
1167 IF c_line_shipment%NOTFOUND OR l_ship_from_org_id IS NULL OR l_ship_from_org_id = FND_API.G_MISS_NUM THEN
1168 open c_header_shipment;
1169 fetch c_header_shipment into l_ship_from_org_id;
1170
1171 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1172 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_from_org_id: c_header_shipment: l_ship_from_org_id'||l_ship_from_org_id, 1, 'N');
1173 END IF;
1174
1175 close c_header_shipment;
1176 END IF;
1177 close c_line_shipment;
1178 return l_ship_from_org_id;
1179 End Get_ship_from_org_id;
1180
1181
1182 FUNCTION Get_ship_method_code(p_qte_header_id NUMBER, p_qte_line_id NUMBER)
1183 RETURN VARCHAR2
1184
1185 IS
1186
1187 CURSOR c_line_shipment IS
1188 SELECT ship_method_code FROM ASO_shipments
1189 WHERE quote_line_id = p_qte_line_id
1190 AND quote_header_id = p_qte_header_id;
1191
1192
1193 CURSOR c_header_shipment IS
1194 SELECT ship_method_code FROM ASO_shipments
1195 WHERE quote_header_id = p_qte_header_id
1196 AND quote_line_id IS NULL;
1197
1198 l_ship_method_code varchar2(30) := null;
1199
1200 Begin
1201
1202 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1203 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_method_code: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1204 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_method_code: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1205 END IF;
1206
1207 -- Look for line-shipment level ship_method_code
1208
1209 if p_qte_line_id is not null and p_qte_header_id is not null then
1210
1211 open c_line_shipment;
1212 fetch c_line_shipment into l_ship_method_code;
1213
1214 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1215 aso_debug_pub.add('Get_ship_method_code: c_line_shipment: l_ship_method_code: '||l_ship_method_code, 1, 'N');
1216 END IF;
1217
1218 -- Line-shipment level ship_method_code doesn't exist then look for header-shipment level ship_method_code
1219
1220 IF c_line_shipment%NOTFOUND OR l_ship_method_code IS NULL THEN
1221
1222 open c_header_shipment;
1223 fetch c_header_shipment into l_ship_method_code;
1224
1225 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1226 aso_debug_pub.add('Get_ship_method_code: c_header_shipment: l_ship_method_code: '||l_ship_method_code, 1, 'N');
1227 END IF;
1228
1229 close c_header_shipment;
1230 END IF;
1231
1232 close c_line_shipment;
1233
1234 elsif p_qte_header_id is not null and p_qte_line_id is null then
1235
1236 open c_header_shipment;
1237 fetch c_header_shipment into l_ship_method_code;
1238
1239 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1240 aso_debug_pub.add('Get_ship_from_org_id: c_header_shipment: l_ship_method_code: '||l_ship_method_code, 1, 'N');
1241 END IF;
1242
1243 close c_header_shipment;
1244
1245 end if;
1246
1247 return l_ship_method_code;
1248
1249 End Get_ship_method_code;
1250
1251
1252 FUNCTION Get_demand_class_code(p_qte_header_id NUMBER, p_qte_line_id NUMBER)
1253 RETURN VARCHAR2
1254
1255 IS
1256
1257 CURSOR c_line_shipment IS
1258 SELECT demand_class_code FROM ASO_shipments
1259 WHERE quote_line_id = p_qte_line_id
1260 AND quote_header_id = p_qte_header_id;
1261
1262
1263 CURSOR c_header_shipment IS
1264 SELECT demand_class_code FROM ASO_shipments
1265 WHERE quote_header_id = p_qte_header_id
1266 AND quote_line_id IS NULL;
1267
1268 l_demand_class_code varchar2(30) := null;
1269
1270 Begin
1271
1272 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1273 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_demand_class_code: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1274 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_demand_class_code: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1275 END IF;
1276
1277 -- Look for line-shipment level demand_class_code
1278
1279 if p_qte_line_id is not null and p_qte_header_id is not null then
1280
1281 open c_line_shipment;
1282 fetch c_line_shipment into l_demand_class_code;
1283
1284 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1285 aso_debug_pub.add('Get_demand_class_code: c_line_shipment: l_demand_class_code: '||l_demand_class_code, 1, 'N');
1286 END IF;
1287
1288 -- Line-shipment level demand_class_code doesn't exist then look for header-shipment level demand_class_code
1289
1290 IF c_line_shipment%NOTFOUND OR l_demand_class_code IS NULL THEN
1291
1292 open c_header_shipment;
1293 fetch c_header_shipment into l_demand_class_code;
1294
1295 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1296 aso_debug_pub.add('Get_demand_class_code: c_header_shipment: l_demand_class_code: '||l_demand_class_code, 1, 'N');
1297 END IF;
1298
1299 close c_header_shipment;
1300 END IF;
1301
1302 close c_line_shipment;
1303
1304 elsif p_qte_header_id is not null and p_qte_line_id is null then
1305
1306 open c_header_shipment;
1307 fetch c_header_shipment into l_demand_class_code;
1308
1309 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1310 aso_debug_pub.add('Get_demand_class_code: c_header_shipment: l_demand_class_code: '||l_demand_class_code, 1, 'N');
1311 END IF;
1312
1313 close c_header_shipment;
1314
1315 end if;
1316
1317 return l_demand_class_code;
1318
1319 End Get_demand_class_code;
1320
1321
1322 FUNCTION Get_ship_to_party_site_id(p_qte_header_id NUMBER, p_qte_line_id NUMBER)
1323 RETURN NUMBER
1324
1325 IS
1326
1327 CURSOR c_line_shipment IS
1328 SELECT ship_to_party_site_id FROM ASO_shipments
1329 WHERE quote_line_id = p_qte_line_id
1330 AND quote_header_id = p_qte_header_id;
1331
1332
1333 CURSOR c_header_shipment IS
1334 SELECT ship_to_party_site_id FROM ASO_shipments
1335 WHERE quote_header_id = p_qte_header_id
1336 AND quote_line_id IS NULL;
1337
1338 l_ship_to_party_site_id number := null;
1339
1340 Begin
1341
1342 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1343 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_party_site_id: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1344 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_party_site_id: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1345 END IF;
1346
1347 -- Look for line-shipment level ship_to_party_site_id
1348
1349 if p_qte_line_id is not null and p_qte_header_id is not null then
1350
1351 open c_line_shipment;
1352 fetch c_line_shipment into l_ship_to_party_site_id;
1353
1354 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1355 aso_debug_pub.add('Get_ship_to_party_site_id: c_line_shipment: l_ship_to_party_site_id: '||l_ship_to_party_site_id, 1, 'N');
1356 END IF;
1357
1358 -- Line-shipment level ship_to_party_site_id doesn't exist then look for header-shipment level ship_to_party_site_id
1359
1360 IF c_line_shipment%NOTFOUND OR l_ship_to_party_site_id IS NULL THEN
1361
1362 open c_header_shipment;
1363 fetch c_header_shipment into l_ship_to_party_site_id;
1364
1365 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1366 aso_debug_pub.add('Get_ship_to_party_site_id: c_header_shipment: l_ship_to_party_site_id: '||l_ship_to_party_site_id, 1, 'N');
1367 END IF;
1368
1369 close c_header_shipment;
1370 END IF;
1371
1372 close c_line_shipment;
1373
1374 elsif p_qte_header_id is not null and p_qte_line_id is null then
1375
1376 open c_header_shipment;
1377 fetch c_header_shipment into l_ship_to_party_site_id;
1378
1379 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1380 aso_debug_pub.add('Get_ship_to_party_site_id: c_header_shipment: l_ship_to_party_site_id: '||l_ship_to_party_site_id, 1, 'N');
1381 END IF;
1382
1383 close c_header_shipment;
1384
1385 end if;
1386
1387 return l_ship_to_party_site_id;
1388
1389 End Get_ship_to_party_site_id;
1390
1391
1392 FUNCTION Get_ship_to_cust_account_id(p_qte_header_id NUMBER, p_qte_line_id NUMBER)
1393 RETURN NUMBER
1394
1395 IS
1396
1397 CURSOR c_line_shipment IS
1398 SELECT ship_to_cust_account_id FROM ASO_shipments
1399 WHERE quote_line_id = p_qte_line_id
1400 AND quote_header_id = p_qte_header_id;
1401
1402
1403 CURSOR c_header_shipment IS
1404 SELECT ship_to_cust_account_id FROM ASO_shipments
1405 WHERE quote_header_id = p_qte_header_id
1406 AND quote_line_id IS NULL;
1407
1408 l_ship_to_cust_account_id number := null;
1409
1410 Begin
1411
1412 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1413 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_cust_account_id: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1414 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_cust_account_id: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1415 END IF;
1416
1417 -- Look for line-shipment level ship_to_cust_account_id
1418
1419 if p_qte_line_id is not null and p_qte_header_id is not null then
1420
1421 open c_line_shipment;
1422 fetch c_line_shipment into l_ship_to_cust_account_id;
1423
1424 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1425 aso_debug_pub.add('Get_ship_to_party_site_id: c_line_shipment: l_ship_to_cust_account_id: '||l_ship_to_cust_account_id, 1, 'N');
1426 END IF;
1427
1428 -- Line-shipment level ship_to_cust_account_id doesn't exist then look for header-shipment level ship_to_cust_account_id
1429
1430 IF c_line_shipment%NOTFOUND OR l_ship_to_cust_account_id IS NULL THEN
1431
1432 open c_header_shipment;
1433 fetch c_header_shipment into l_ship_to_cust_account_id;
1434
1435 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1436 aso_debug_pub.add('Get_ship_to_cust_account_id: c_header_shipment: l_ship_to_cust_account_id: '||l_ship_to_cust_account_id, 1, 'N');
1437 END IF;
1438
1439 close c_header_shipment;
1440 END IF;
1441
1442 close c_line_shipment;
1443
1444 elsif p_qte_header_id is not null and p_qte_line_id is null then
1445
1446 open c_header_shipment;
1447 fetch c_header_shipment into l_ship_to_cust_account_id;
1448
1449 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1450 aso_debug_pub.add('Get_ship_to_cust_account_id: c_header_shipment: l_ship_to_cust_account_id: '||l_ship_to_cust_account_id, 1, 'N');
1451 END IF;
1452
1453 close c_header_shipment;
1454
1455 end if;
1456
1457 return l_ship_to_cust_account_id;
1458
1459 End Get_ship_to_cust_account_id;
1460
1461 END ASO_SHIPMENT_PVT;
[Home] [Help]