1 PACKAGE BODY ASO_SHIPMENT_PVT as
2 /* $Header: asovshpb.pls 120.3 2006/10/25 18:04:21 skulkarn 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 -- performance fix for bug 5596369
478 CURSOR c_ship_site2 IS
479 SELECT ship_to_party_site_id , SHIP_TO_CUST_ACCOUNT_ID
480 FROM ASO_SHIPMENTS
481 WHERE quote_header_id = p_qte_header_id
482 and shipment_id =p_shipment_id
483 AND quote_line_id IS NULL;
484
485
486 l_ship_site_id NUMBER;
487 l_ship_cust_acct_id NUMBER;
488 l_ship_site_use_id NUMBER;
489 CURSOR c_cust_id IS
490 SELECT cust_account_id FROM ASO_QUOTE_HEADERS_ALL
491 WHERE quote_header_id = p_qte_header_id;
492
493 CURSOR c_ship_cust_id IS
494 SELECT SHIP_TO_CUST_ACCOUNT_ID FROM ASO_SHIPMENTS
495 WHERE quote_header_id = p_qte_header_id and quote_line_id =p_qte_line_id and shipment_id =p_shipment_id;
496
497
498 -- bug 5596369
499 CURSOR c_ship_cust_id1 IS
500 SELECT SHIP_TO_CUST_ACCOUNT_ID
501 FROM ASO_SHIPMENTS
502 WHERE quote_header_id = p_qte_header_id
503 and shipment_id =p_shipment_id
504 and quote_line_id IS NULL;
505
506 CURSOR C_site_use(l_cust_id NUMBER, l_ship_site_id NUMBER) IS
507 SELECT site_use_id
508 FROM hz_cust_site_uses b,hz_cust_acct_sites a
509 WHERE b.cust_acct_site_id = a.cust_acct_site_id
510 AND b.site_use_code = 'SHIP_TO' --and b.primary_flag = 'Y'
511 AND a.party_site_id = l_ship_site_id
512 AND a.cust_account_id = l_cust_id;
513
514 cursor c_root_model_line_id is
515 select quote_line_id
516 from aso_quote_line_details
517 where (config_header_id, config_revision_num) = (select config_header_id,config_revision_num
518 from aso_quote_line_details
519 where quote_line_id = p_qte_line_id)
520 and ref_type_code = 'CONFIG'
521 and ref_line_id is null;
522
523 cursor c_item_type_code is
524 select item_type_code
525 from aso_quote_lines_all
526 where quote_line_id = p_qte_line_id;
527
528 cursor c_shipment_id (p_line_id NUMBER) is
529 select shipment_id
530 from aso_shipments
531 where quote_line_id = p_line_id
532 and quote_header_id = p_qte_header_id;
533
534 l_cust_id NUMBER;
535 l_inv_cust_id NUMBER;
536 l_return_status VARCHAR2(1);
537 l_msg_count number;
538 l_msg_data VARCHAR2(2000);
539
540 l_item_type_code VARCHAR2(30);
541 l_model_quote_line_id NUMBER;
542 l_model_shipment_id NUMBER;
543
544 BEGIN
545 IF aso_debug_pub.g_debug_flag = 'Y' THEN
546 aso_debug_pub.add('ASO_SHIPMENT_PVT:p_qte_header_id ' || p_qte_header_id, 1, 'N');
547 aso_debug_pub.add('ASO_SHIPMENT_PVT:quote_line_id ' || p_qte_line_id, 1, 'N');
548 aso_debug_pub.add('ASO_SHIPMENT_PVT:shipment_id ' || p_shipment_id, 1, 'N');
549 END IF;
550
551 OPEN c_ship_site1;
552 FETCH c_ship_site1 INTO l_ship_site_id, l_ship_cust_acct_id;
553
554 IF aso_debug_pub.g_debug_flag = 'Y' THEN
555 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site1 l_ship_site_id ' || l_ship_site_id, 1, 'N');
556 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site1 l_ship_cust_acct_id ' || l_ship_cust_acct_id, 1, 'N');
557 END IF;
558
559 IF c_ship_site1%FOUND and l_ship_site_id IS NOT NULL and l_ship_site_id <> FND_API.G_MISS_NUM THEN
560
561 CLOSE c_ship_site1;
562
563 --Get the ship_to_cust_account if present
564 OPEN c_ship_cust_id;
565 FETCH c_ship_cust_id into l_cust_id;
566
567 IF aso_debug_pub.g_debug_flag = 'Y' THEN
568 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_cust_id l_cust_id ' || l_cust_id, 1, 'N');
569 END IF;
570
571 IF c_ship_cust_id%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
572
573 CLOSE c_ship_cust_id;
574
575 OPEN c_ship_cust_id1;
576 FETCH c_ship_cust_id1 into l_cust_id;
577
578 IF aso_debug_pub.g_debug_flag = 'Y' THEN
579 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_cust_id1 l_cust_id ' || l_cust_id, 1, 'N');
580 END IF;
581
582 IF c_ship_cust_id1%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
583
584 CLOSE c_ship_cust_id1;
585
586 -- Get the Customer Account from the header
587 OPEN c_cust_id;
588 FETCH c_cust_id INTO l_cust_id;
589 CLOSE c_cust_id;
590
591 IF aso_debug_pub.g_debug_flag = 'Y' THEN
592 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_cust_id l_cust_id ' || l_cust_id, 1, 'N');
593 END IF;
594
595 OPEN C_site_use(l_cust_id , l_ship_site_id);
596 FETCH C_site_use into l_ship_site_use_id;
597
598 IF aso_debug_pub.g_debug_flag = 'Y' THEN
599 aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use1 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
600 END IF;
601 CLOSE C_site_use;
602
603 return l_ship_site_use_id;
604
605 END IF;
606
607 CLOSE c_ship_cust_id1;
608
609 OPEN C_site_use(l_cust_id , l_ship_site_id);
610 FETCH C_site_use into l_ship_site_use_id;
611
612 IF aso_debug_pub.g_debug_flag = 'Y' THEN
613 aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use2 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
614 END IF;
615 CLOSE C_site_use;
616
617 return l_ship_site_use_id;
618
619 END IF;
620
621 CLOSE c_ship_cust_id;
622
623
624 --Get the site use id from the HZ_cust_site_uses
625
626 OPEN C_site_use(l_cust_id , l_ship_site_id);
627 FETCH C_site_use into l_ship_site_use_id;
628
629 IF aso_debug_pub.g_debug_flag = 'Y' THEN
630 aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use3 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
631 END IF;
632 CLOSE C_site_use;
633
634 return l_ship_site_use_id;
635
636 ELSE
637
638 CLOSE c_ship_site1;
639
640 open c_item_type_code;
641 fetch c_item_type_code into l_item_type_code;
642
643 IF aso_debug_pub.g_debug_flag = 'Y' THEN
644 aso_debug_pub.add('Get_Ship_To_site_Id: c_item_type_code: l_item_type_code: '||l_item_type_code);
645 END IF;
646
647 IF c_item_type_code%FOUND and l_item_type_code = 'CFG' THEN
648
649 close c_item_type_code;
650
651 open c_root_model_line_id;
652 fetch c_root_model_line_id into l_model_quote_line_id;
653
654 IF aso_debug_pub.g_debug_flag = 'Y' THEN
655 aso_debug_pub.add('Get_Ship_To_site_Id: c_root_model_line_id: l_model_quote_line_id: '||l_model_quote_line_id);
656 END IF;
657
658 IF c_root_model_line_id%FOUND and l_model_quote_line_id is not null THEN
659
660 close c_root_model_line_id;
661
662 open c_shipment_id(l_model_quote_line_id);
663 fetch c_shipment_id into l_model_shipment_id;
664
665 IF aso_debug_pub.g_debug_flag = 'Y' THEN
666 aso_debug_pub.add('Get_Ship_To_site_Id: c_shipment_id: l_model_shipment_id: '||l_model_shipment_id);
667 END IF;
668
669 IF c_shipment_id%FOUND and l_model_shipment_id is not null THEN
670
671 close c_shipment_id;
672
673 l_ship_site_use_id := Get_Ship_To_site_Id(p_qte_header_id, l_model_quote_line_id, l_model_shipment_id);
674 IF aso_debug_pub.g_debug_flag = 'Y' THEN
675 aso_debug_pub.add('Get_Ship_To_site_Id: l_ship_site_use_id: '||l_ship_site_use_id);
676 END IF;
677
678 return l_ship_site_use_id;
679
680 END IF; --c_shipment_id
681
682 close c_shipment_id;
683
684 END IF; --c_root_model_line_id
685
686 close c_root_model_line_id;
687
688 END IF; --c_item_type_code
689
690 close c_item_type_code;
691
692 END IF; --c_ship_site1
693
694
695 OPEN c_ship_site2;
696 FETCH c_ship_site2 INTO l_ship_site_id, l_ship_cust_acct_id;
697
698 IF aso_debug_pub.g_debug_flag = 'Y' THEN
699 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site2 l_ship_site_id ' || l_ship_site_id, 1, 'N');
700 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_site2 l_ship_cust_acct_id ' || l_ship_cust_acct_id, 1, 'N');
701 END IF;
702
703 IF c_ship_site2%FOUND AND l_ship_site_id IS NOT NULL and l_ship_site_id <> FND_API.G_MISS_NUM THEN
704
705 CLOSE c_ship_site2;
706 --Get the ship_to_cust_account if present
707 OPEN c_ship_cust_id;
708 FETCH c_ship_cust_id into l_cust_id;
709
710 IF aso_debug_pub.g_debug_flag = 'Y' THEN
711 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_cust_id l_cust_id ' || l_cust_id, 1, 'N');
712 END IF;
713
714 IF c_ship_cust_id%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
715
716 CLOSE c_ship_cust_id;
717 OPEN c_ship_cust_id1;
718
719 FETCH c_ship_cust_id1 into l_cust_id;
720
721 IF aso_debug_pub.g_debug_flag = 'Y' THEN
722 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_ship_cust_id1 l_cust_id ' || l_cust_id, 1, 'N');
723 END IF;
724
725 IF c_ship_cust_id1%NOTFOUND or l_cust_id IS NULL or l_cust_id = FND_API.G_MISS_NUM THEN
726
727 CLOSE c_ship_cust_id1;
728 --Get the Customer Account from the header
729 OPEN c_cust_id;
730 FETCH c_cust_id INTO l_cust_id;
731 CLOSE c_cust_id;
732
733 IF aso_debug_pub.g_debug_flag = 'Y' THEN
734 aso_debug_pub.add('ASO_SHIPMENT_PVT:c_cust_id l_cust_id ' || l_cust_id, 1, 'N');
735 END IF;
736
737 OPEN C_site_use(l_cust_id , l_ship_site_id);
738 FETCH C_site_use into l_ship_site_use_id;
739
740 IF aso_debug_pub.g_debug_flag = 'Y' THEN
741 aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use4 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
742 END IF;
743
744 CLOSE C_site_use;
745 return l_ship_site_use_id;
746
747 END IF;
748
749 CLOSE c_ship_cust_id1;
750
751 OPEN C_site_use(l_cust_id , l_ship_site_id);
752 FETCH C_site_use into l_ship_site_use_id;
753
754 IF aso_debug_pub.g_debug_flag = 'Y' THEN
755 aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use5 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
756 END IF;
757
758 CLOSE C_site_use;
759 return l_ship_site_use_id;
760
761 END IF;
762 CLOSE c_ship_cust_id;
763
764 --Get the site use id from the HZ_cust_site_uses
765
766 OPEN C_site_use(l_cust_id , l_ship_site_id);
767 FETCH C_site_use into l_ship_site_use_id;
768
769 IF aso_debug_pub.g_debug_flag = 'Y' THEN
770 aso_debug_pub.add('ASO_SHIPMENT_PVT:C_site_use6 l_ship_site_use_id ' || l_ship_site_use_id, 1, 'N');
771 END IF;
772 CLOSE C_site_use;
773
774 return l_ship_site_use_id;
775
776 END IF;
777
778 CLOSE c_ship_site2;
779
780 return l_ship_site_use_id;
781
782 END Get_Ship_To_site_Id;
783
784
785 FUNCTION Get_Ship_To_party_site_Id (
786 p_qte_header_id NUMBER,
787 p_qte_line_id NUMBER,
788 p_shipment_id NUMBER) RETURN NUMBER
789 IS
790 CURSOR c_ship_site1 IS
791 SELECT ship_to_party_site_id FROM ASO_SHIPMENTS
792 WHERE shipment_id = p_shipment_id
793 AND quote_line_id = p_qte_line_id
794 AND quote_header_id = p_qte_header_id;
795
796 CURSOR c_ship_site2 IS
797 SELECT ship_to_party_site_id FROM ASO_SHIPMENTS
798 WHERE quote_line_id IS NULL AND quote_header_id = p_qte_header_id;
799
800 l_ship_site_id NUMBER;
801
802 cursor c_root_model_line_id is
803 select quote_line_id
804 from aso_quote_line_details
805 where (config_header_id, config_revision_num) = (select config_header_id,config_revision_num
806 from aso_quote_line_details
807 where quote_line_id = p_qte_line_id)
808 and ref_type_code = 'CONFIG'
809 and ref_line_id is null;
810
811 cursor c_item_type_code is
812 select item_type_code
813 from aso_quote_lines_all
814 where quote_line_id = p_qte_line_id;
815
816 cursor c_shipment_id (p_line_id NUMBER) is
817 select shipment_id
818 from aso_shipments
819 where quote_line_id = p_line_id
820 and quote_header_id = p_qte_header_id;
821
822 l_model_quote_line_id NUMBER;
823 l_item_type_code VARCHAR2(30);
824 l_model_shipment_id NUMBER;
825
826 BEGIN
827
828 OPEN c_ship_site1;
829 FETCH c_ship_site1 INTO l_ship_site_id;
830
831 IF c_ship_site1%FOUND and l_ship_site_id IS NOT NULL and l_ship_site_id <> FND_API.G_MISS_NUM THEN
832
833 CLOSE c_ship_site1;
834 return l_ship_site_id;
835
836 ELSE
837
838 CLOSE c_ship_site1;
839
840 open c_item_type_code;
841 fetch c_item_type_code into l_item_type_code;
842
843 IF aso_debug_pub.g_debug_flag = 'Y' THEN
844 aso_debug_pub.add('Get_Ship_To_party_site_Id: c_item_type_code: l_item_type_code: '||l_item_type_code);
845 END IF;
846
847 IF c_item_type_code%FOUND and l_item_type_code = 'CFG' THEN
848
849 close c_item_type_code;
850
851 open c_root_model_line_id;
852 fetch c_root_model_line_id into l_model_quote_line_id;
853
854 IF aso_debug_pub.g_debug_flag = 'Y' THEN
855 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);
856 END IF;
857
858 IF c_root_model_line_id%FOUND and l_model_quote_line_id is not null THEN
859
860 close c_root_model_line_id;
861
862 open c_shipment_id(l_model_quote_line_id);
863 fetch c_shipment_id into l_model_shipment_id;
864
865 IF aso_debug_pub.g_debug_flag = 'Y' THEN
866 aso_debug_pub.add('Get_Ship_To_party_site_Id: c_shipment_id: l_model_shipment_id: '||l_model_shipment_id);
867 END IF;
868
869 IF c_shipment_id%FOUND and l_model_shipment_id is not null THEN
870
871 close c_shipment_id;
872
873 l_ship_site_id := Get_Ship_To_party_site_Id(p_qte_header_id, l_model_quote_line_id, l_model_shipment_id);
874
875 IF aso_debug_pub.g_debug_flag = 'Y' THEN
876 aso_debug_pub.add('Get_Ship_To_party_site_Id: l_ship_site_id: '||l_ship_site_id);
877 END IF;
878
879 return l_ship_site_id;
880
881 END IF; --c_shipment_id
882
883 close c_shipment_id;
884
885 END IF; --c_root_model_line_id
886
887 close c_root_model_line_id;
888
889 END IF; --c_item_type_code
890
891 END IF; --c_ship_site1
892
893
894 OPEN c_ship_site2;
895 FETCH c_ship_site2 INTO l_ship_site_id;
896
897 IF c_ship_site2%FOUND and l_ship_site_id IS NOT NULL and l_ship_site_id <> FND_API.G_MISS_NUM THEN
898
899 CLOSE c_ship_site2;
900 return l_ship_site_id;
901
902 END IF; --c_ship_site2
903
904 return l_ship_site_id;
905
906 END Get_Ship_To_party_site_Id;
907
908
909 FUNCTION Get_party_name (
910 p_party_id NUMBER,
911 p_party_type VARCHAR2
912 ) RETURN VARCHAR2
913 IS
914 CURSOR C1 IS
915 select HP.party_id,HP.party_name
916 from hz_relationships HPR,hz_parties HP where hpr.party_id = p_party_id
917 and hp.party_id=HPR.object_id
918 and SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
919 and OBJECT_TABLE_NAME(+) = 'HZ_PARTIES';
920
921 CURSOR C2 IS
922 select party_id,party_name
923 from hz_parties HP where party_id = p_party_id;
924 l_party_name VARCHAR2(360);
925 l_party_id NUMBER;
926 BEGIN
927 IF p_party_type = 'PARTY_RELATIONSHIP' THEN
928 OPEN C1;
929 FETCH C1 INTO l_party_id,l_party_name;
930 IF C1%NOTFOUND OR l_party_name IS NULL THEN
931 CLOSE C1;
932 l_party_name := NULL;
933 RETURN l_party_name;
934 END IF;
935 CLOSE C1;
936 RETURN l_party_name;
937 ELSE
938 OPEN C2;
939 FETCH C2 INTO l_party_id,l_party_name;
940 IF C2%NOTFOUND OR l_party_name IS NULL THEN
941 CLOSE C2;
942 l_party_name := NULL;
943 RETURN l_party_name;
944 END IF;
945 CLOSE C2;
946 RETURN l_party_name;
947 END IF;
948 END Get_party_name;
949
950
951 FUNCTION Get_party_first_name (
952 p_party_id NUMBER,
953 p_party_type VARCHAR2
954 ) RETURN VARCHAR2
955 IS
956 CURSOR C1 IS
957 select HP.party_id,HP.person_first_name
958 from hz_relationships HPR,hz_parties HP where hpr.party_id = p_party_id
959 and hp.party_id=HPR.subject_id
960 and SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
961 and OBJECT_TABLE_NAME(+) = 'HZ_PARTIES';
962
963 CURSOR C2 IS
964 select party_id,person_first_name
965 from hz_parties HP where party_id = p_party_id;
966 l_f_party_name VARCHAR2(150);
967 l_party_id NUMBER;
968 BEGIN
969 IF p_party_type = 'PARTY_RELATIONSHIP' THEN
970 OPEN C1;
971 FETCH C1 INTO l_party_id,l_f_party_name;
972 IF C1%NOTFOUND OR l_f_party_name IS NULL THEN
973 CLOSE C1;
974 l_f_party_name := NULL;
975 RETURN l_f_party_name;
976 END IF;
977 CLOSE C1;
978 RETURN l_f_party_name;
979 ELSE
980 OPEN C2;
981 FETCH C2 INTO l_party_id,l_f_party_name;
982 IF C2%NOTFOUND OR l_f_party_name IS NULL THEN
983 CLOSE C2;
984 l_f_party_name := NULL;
985 RETURN l_f_party_name;
986 END IF;
987 CLOSE C2;
988 RETURN l_f_party_name;
989 END IF;
990 END Get_party_first_name;
991
992
993 FUNCTION Get_party_mid_name (
994 p_party_id NUMBER,
995 p_party_type VARCHAR2
996 ) RETURN VARCHAR2
997 IS
998 CURSOR C1 IS
999 select HP.party_id,HP.person_middle_name
1000 from hz_relationships HPR,hz_parties HP where hpr.party_id = p_party_id
1001 and hp.party_id=HPR.subject_id
1002 and SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
1003 and OBJECT_TABLE_NAME(+) = 'HZ_PARTIES';
1004
1005 CURSOR C2 IS
1006 select party_id,person_middle_name
1007 from hz_parties HP where party_id = p_party_id;
1008 l_m_party_name VARCHAR2(60);
1009 l_party_id NUMBER;
1010 BEGIN
1011 IF p_party_type = 'PARTY_RELATIONSHIP' THEN
1012 OPEN C1;
1013 FETCH C1 INTO l_party_id,l_m_party_name;
1014 IF C1%NOTFOUND OR l_m_party_name IS NULL THEN
1015 CLOSE C1;
1016 l_m_party_name := NULL;
1017 RETURN l_m_party_name;
1018 END IF;
1019 CLOSE C1;
1020 RETURN l_m_party_name;
1021 ELSE
1022 OPEN C2;
1023 FETCH C2 INTO l_party_id,l_m_party_name;
1024 IF C2%NOTFOUND OR l_m_party_name IS NULL THEN
1025 CLOSE C2;
1026 l_m_party_name := NULL;
1027 RETURN l_m_party_name;
1028 END IF;
1029 CLOSE C2;
1030 RETURN l_m_party_name;
1031 END IF;
1032 END Get_party_mid_name;
1033 FUNCTION Get_party_last_name (
1034 p_party_id NUMBER,
1035 p_party_type VARCHAR2
1036 ) RETURN VARCHAR2
1037 IS
1038 CURSOR C1 IS
1039 select HP.party_id,HP.person_last_name
1040 from hz_relationships HPR,hz_parties HP where hpr.party_id = p_party_id
1041 and hp.party_id=HPR.subject_id
1042 and SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
1043 and OBJECT_TABLE_NAME(+) = 'HZ_PARTIES';
1044
1045 CURSOR C2 IS
1046 select party_id,person_last_name
1047 from hz_parties HP where party_id = p_party_id;
1048 l_l_party_name VARCHAR2(150);
1049 l_party_id NUMBER;
1050 BEGIN
1051 IF p_party_type = 'PARTY_RELATIONSHIP' THEN
1052 OPEN C1;
1053 FETCH C1 INTO l_party_id,l_l_party_name;
1054 IF C1%NOTFOUND OR l_l_party_name IS NULL THEN
1055 CLOSE C1;
1056 l_l_party_name := NULL;
1057 RETURN l_l_party_name;
1058 END IF;
1059 CLOSE C1;
1060 RETURN l_l_party_name;
1061 ELSE
1062 OPEN C2;
1063 FETCH C2 INTO l_party_id,l_l_party_name;
1064 IF C2%NOTFOUND OR l_l_party_name IS NULL THEN
1065 CLOSE C2;
1066 l_l_party_name := NULL;
1067 RETURN l_l_party_name;
1068 END IF;
1069 CLOSE C2;
1070 RETURN l_l_party_name;
1071 END IF;
1072 END Get_party_last_name;
1073
1074
1075 FUNCTION Get_ship_from_org_id (
1076 p_qte_header_id NUMBER,
1077 p_qte_line_id NUMBER
1078 ) RETURN NUMBER
1079
1080 IS
1081
1082 CURSOR c_line_shipment IS
1083 SELECT ship_from_org_id FROM ASO_shipments
1084 WHERE quote_line_id = p_qte_line_id
1085 AND quote_header_id = p_qte_header_id;
1086
1087
1088 CURSOR c_header_shipment IS
1089 SELECT ship_from_org_id FROM ASO_shipments
1090 WHERE quote_header_id = p_qte_header_id
1091 AND quote_line_id IS NULL;
1092
1093 l_ship_from_org_id NUMBER;
1094
1095
1096 Begin
1097 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1098 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_from_org_id: p_qte_header_id :'||p_qte_header_id, 1, 'N');
1099 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_from_org_id: p_qte_line_id :'||p_qte_line_id, 1, 'N');
1100 END IF;
1101
1102 -- Look for line-shipment level ship_from_org_id
1103
1104 open c_line_shipment;
1105 fetch c_line_shipment into l_ship_from_org_id;
1106
1107 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1108 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');
1109 END IF;
1110
1111 -- Line-shipment level ship_from_org_id doesn't exist then look for header-shipment level ship_from_org_id
1112
1113 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
1114 open c_header_shipment;
1115 fetch c_header_shipment into l_ship_from_org_id;
1116
1117 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1118 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');
1119 END IF;
1120
1121 close c_header_shipment;
1122 END IF;
1123 close c_line_shipment;
1124 return l_ship_from_org_id;
1125 End Get_ship_from_org_id;
1126
1127
1128 FUNCTION Get_ship_method_code(p_qte_header_id NUMBER, p_qte_line_id NUMBER)
1129 RETURN VARCHAR2
1130
1131 IS
1132
1133 CURSOR c_line_shipment IS
1134 SELECT ship_method_code FROM ASO_shipments
1135 WHERE quote_line_id = p_qte_line_id
1136 AND quote_header_id = p_qte_header_id;
1137
1138
1139 CURSOR c_header_shipment IS
1140 SELECT ship_method_code FROM ASO_shipments
1141 WHERE quote_header_id = p_qte_header_id
1142 AND quote_line_id IS NULL;
1143
1144 l_ship_method_code varchar2(30) := null;
1145
1146 Begin
1147
1148 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1149 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_method_code: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1150 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_method_code: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1151 END IF;
1152
1153 -- Look for line-shipment level ship_method_code
1154
1155 if p_qte_line_id is not null and p_qte_header_id is not null then
1156
1157 open c_line_shipment;
1158 fetch c_line_shipment into l_ship_method_code;
1159
1160 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1161 aso_debug_pub.add('Get_ship_method_code: c_line_shipment: l_ship_method_code: '||l_ship_method_code, 1, 'N');
1162 END IF;
1163
1164 -- Line-shipment level ship_method_code doesn't exist then look for header-shipment level ship_method_code
1165
1166 IF c_line_shipment%NOTFOUND OR l_ship_method_code IS NULL THEN
1167
1168 open c_header_shipment;
1169 fetch c_header_shipment into l_ship_method_code;
1170
1171 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1172 aso_debug_pub.add('Get_ship_method_code: c_header_shipment: l_ship_method_code: '||l_ship_method_code, 1, 'N');
1173 END IF;
1174
1175 close c_header_shipment;
1176 END IF;
1177
1178 close c_line_shipment;
1179
1180 elsif p_qte_header_id is not null and p_qte_line_id is null then
1181
1182 open c_header_shipment;
1183 fetch c_header_shipment into l_ship_method_code;
1184
1185 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1186 aso_debug_pub.add('Get_ship_from_org_id: c_header_shipment: l_ship_method_code: '||l_ship_method_code, 1, 'N');
1187 END IF;
1188
1189 close c_header_shipment;
1190
1191 end if;
1192
1193 return l_ship_method_code;
1194
1195 End Get_ship_method_code;
1196
1197
1198 FUNCTION Get_demand_class_code(p_qte_header_id NUMBER, p_qte_line_id NUMBER)
1199 RETURN VARCHAR2
1200
1201 IS
1202
1203 CURSOR c_line_shipment IS
1204 SELECT demand_class_code FROM ASO_shipments
1205 WHERE quote_line_id = p_qte_line_id
1206 AND quote_header_id = p_qte_header_id;
1207
1208
1209 CURSOR c_header_shipment IS
1210 SELECT demand_class_code FROM ASO_shipments
1211 WHERE quote_header_id = p_qte_header_id
1212 AND quote_line_id IS NULL;
1213
1214 l_demand_class_code varchar2(30) := null;
1215
1216 Begin
1217
1218 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1219 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_demand_class_code: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1220 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_demand_class_code: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1221 END IF;
1222
1223 -- Look for line-shipment level demand_class_code
1224
1225 if p_qte_line_id is not null and p_qte_header_id is not null then
1226
1227 open c_line_shipment;
1228 fetch c_line_shipment into l_demand_class_code;
1229
1230 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1231 aso_debug_pub.add('Get_demand_class_code: c_line_shipment: l_demand_class_code: '||l_demand_class_code, 1, 'N');
1232 END IF;
1233
1234 -- Line-shipment level demand_class_code doesn't exist then look for header-shipment level demand_class_code
1235
1236 IF c_line_shipment%NOTFOUND OR l_demand_class_code IS NULL THEN
1237
1238 open c_header_shipment;
1239 fetch c_header_shipment into l_demand_class_code;
1240
1241 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1242 aso_debug_pub.add('Get_demand_class_code: c_header_shipment: l_demand_class_code: '||l_demand_class_code, 1, 'N');
1243 END IF;
1244
1245 close c_header_shipment;
1246 END IF;
1247
1248 close c_line_shipment;
1249
1250 elsif p_qte_header_id is not null and p_qte_line_id is null then
1251
1252 open c_header_shipment;
1253 fetch c_header_shipment into l_demand_class_code;
1254
1255 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1256 aso_debug_pub.add('Get_demand_class_code: c_header_shipment: l_demand_class_code: '||l_demand_class_code, 1, 'N');
1257 END IF;
1258
1259 close c_header_shipment;
1260
1261 end if;
1262
1263 return l_demand_class_code;
1264
1265 End Get_demand_class_code;
1266
1267
1268 FUNCTION Get_ship_to_party_site_id(p_qte_header_id NUMBER, p_qte_line_id NUMBER)
1269 RETURN NUMBER
1270
1271 IS
1272
1273 CURSOR c_line_shipment IS
1274 SELECT ship_to_party_site_id FROM ASO_shipments
1275 WHERE quote_line_id = p_qte_line_id
1276 AND quote_header_id = p_qte_header_id;
1277
1278
1279 CURSOR c_header_shipment IS
1280 SELECT ship_to_party_site_id FROM ASO_shipments
1281 WHERE quote_header_id = p_qte_header_id
1282 AND quote_line_id IS NULL;
1283
1284 l_ship_to_party_site_id number := null;
1285
1286 Begin
1287
1288 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1289 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_party_site_id: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1290 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_party_site_id: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1291 END IF;
1292
1293 -- Look for line-shipment level ship_to_party_site_id
1294
1295 if p_qte_line_id is not null and p_qte_header_id is not null then
1296
1297 open c_line_shipment;
1298 fetch c_line_shipment into l_ship_to_party_site_id;
1299
1300 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1301 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');
1302 END IF;
1303
1304 -- Line-shipment level ship_to_party_site_id doesn't exist then look for header-shipment level ship_to_party_site_id
1305
1306 IF c_line_shipment%NOTFOUND OR l_ship_to_party_site_id IS NULL THEN
1307
1308 open c_header_shipment;
1309 fetch c_header_shipment into l_ship_to_party_site_id;
1310
1311 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1312 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');
1313 END IF;
1314
1315 close c_header_shipment;
1316 END IF;
1317
1318 close c_line_shipment;
1319
1320 elsif p_qte_header_id is not null and p_qte_line_id is null then
1321
1322 open c_header_shipment;
1323 fetch c_header_shipment into l_ship_to_party_site_id;
1324
1325 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1326 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');
1327 END IF;
1328
1329 close c_header_shipment;
1330
1331 end if;
1332
1333 return l_ship_to_party_site_id;
1334
1335 End Get_ship_to_party_site_id;
1336
1337
1338 FUNCTION Get_ship_to_cust_account_id(p_qte_header_id NUMBER, p_qte_line_id NUMBER)
1339 RETURN NUMBER
1340
1341 IS
1342
1343 CURSOR c_line_shipment IS
1344 SELECT ship_to_cust_account_id FROM ASO_shipments
1345 WHERE quote_line_id = p_qte_line_id
1346 AND quote_header_id = p_qte_header_id;
1347
1348
1349 CURSOR c_header_shipment IS
1350 SELECT ship_to_cust_account_id FROM ASO_shipments
1351 WHERE quote_header_id = p_qte_header_id
1352 AND quote_line_id IS NULL;
1353
1354 l_ship_to_cust_account_id number := null;
1355
1356 Begin
1357
1358 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1359 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_cust_account_id: p_qte_header_id : '||p_qte_header_id, 1, 'Y');
1360 aso_debug_pub.add('ASO_SHIPMENT_PVT.Get_ship_to_cust_account_id: p_qte_line_id : '||p_qte_line_id, 1, 'Y');
1361 END IF;
1362
1363 -- Look for line-shipment level ship_to_cust_account_id
1364
1365 if p_qte_line_id is not null and p_qte_header_id is not null then
1366
1367 open c_line_shipment;
1368 fetch c_line_shipment into l_ship_to_cust_account_id;
1369
1370 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1371 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');
1372 END IF;
1373
1374 -- Line-shipment level ship_to_cust_account_id doesn't exist then look for header-shipment level ship_to_cust_account_id
1375
1376 IF c_line_shipment%NOTFOUND OR l_ship_to_cust_account_id IS NULL THEN
1377
1378 open c_header_shipment;
1379 fetch c_header_shipment into l_ship_to_cust_account_id;
1380
1381 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1382 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');
1383 END IF;
1384
1385 close c_header_shipment;
1386 END IF;
1387
1388 close c_line_shipment;
1389
1390 elsif p_qte_header_id is not null and p_qte_line_id is null then
1391
1392 open c_header_shipment;
1393 fetch c_header_shipment into l_ship_to_cust_account_id;
1394
1395 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1396 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');
1397 END IF;
1398
1399 close c_header_shipment;
1400
1401 end if;
1402
1403 return l_ship_to_cust_account_id;
1404
1405 End Get_ship_to_cust_account_id;
1406
1407 END ASO_SHIPMENT_PVT;
[Home] [Help]