1 PACKAGE BODY QOT_DEFAULT_PVT AS
2 /* $Header: qotvdefb.pls 120.22 2007/11/08 12:00:05 akushwah ship $ */
3 -- Package name : QOT_DEFAULT_PVT
4 -- Purpose :
5 -- History :
6 -- NOTE :
7 -- End of Comments
8
9 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
10
11 FUNCTION Get_OperatingUnit(P_Database_Object_Name IN VARCHAR2,
12 P_Attribute_Code IN VARCHAR2) RETURN NUMBER
13
14 IS
15
16 l_default_org_id NUMBER;
17 l_default_org_name VARCHAR2(240);
18 l_ou_count NUMBER;
19
20 BEGIN
21 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
22 aso_debug_pub.add('Function Get_OperatingUnit Default -- Begin :',1,'N');
23 END IF;
24
25 MO_UTILS.GET_DEFAULT_OU( P_DEFAULT_ORG_ID => l_default_org_id,
26 P_DEFAULT_OU_NAME => l_default_org_name,
27 P_OU_COUNT => l_ou_count
28 );
29 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
30 aso_debug_pub.add('Function Get_OperatingUnit Default Organization Id :' ||l_default_org_id,1,'N');
31 aso_debug_pub.add('Function Get_OperatingUnit Default Organization Name :'||l_default_org_name,1,'N');
32 END IF;
33
34 IF l_default_org_id is not null THEN
35 mo_global.set_policy_context('S',l_default_org_id);
36 END IF;
37
38 RETURN l_default_org_id;
39
40 EXCEPTION
41 WHEN NO_DATA_FOUND THEN
42 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
43 aso_debug_pub.add('Function Get_OperatingUnit NO_DATA_FOUND Exception Occurs: ',1,'N');
44 END IF;
45
46 RETURN NULL;
47
48 WHEN OTHERS THEN
49
50 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
51 aso_debug_pub.add('Function Get_OperatingUnit Inside When Others Exception',1,'N');
52 END IF;
53
54 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
55 THEN
56 OE_MSG_PUB.Add_Exc_Msg
57 ( G_PKG_NAME ,
58 ' Get_OperatingUnit '
59 );
60 END IF;
61
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63
64 END Get_OperatingUnit;
65
66
67 FUNCTION Get_OrderType(P_Database_Object_Name IN VARCHAR2,
68 P_Attribute_Code IN VARCHAR2) RETURN NUMBER
69
70 IS
71
72 l_order_type_id NUMBER;
73 l_org_id NUMBER;
74
75 BEGIN
76 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
77 aso_debug_pub.add('Function Get_OrderType Default -- Begin :',1,'N');
78 END IF;
79
80 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
81 l_org_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_Org_id;
82 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
83 l_org_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.O_Org_id;
84 END IF;
85
86 l_order_type_id := aso_utility_pvt.get_ou_attribute_value(aso_utility_pvt.GET_DEFAULT_ORDER_TYPE,l_org_id);
87
88 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
89 aso_debug_pub.add('Function Get_OrderType Default Organization Id :' ||l_order_type_id,1,'N');
90 END IF;
91
92 RETURN l_order_type_id;
93
94 EXCEPTION
95 WHEN NO_DATA_FOUND THEN
96 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
97 aso_debug_pub.add('Function Get_OrderType NO_DATA_FOUND Exception Occurs: ',1,'N');
98 END IF;
99
100 RETURN NULL;
101
102 WHEN OTHERS THEN
103
104 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
105 aso_debug_pub.add('Function Get_OrderType Inside When Others Exception',1,'N');
106 END IF;
107
108 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
109 THEN
110 OE_MSG_PUB.Add_Exc_Msg
111 ( G_PKG_NAME ,
112 ' Get_OrderType '
113 );
114 END IF;
115
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117
118 END Get_OrderType;
119
120 FUNCTION Get_ContractTemplate(P_Database_Object_Name IN VARCHAR2,
121 P_Attribute_Code IN VARCHAR2) RETURN NUMBER
122
123 IS
124
125 l_contract_template_id NUMBER;
126 l_org_id NUMBER;
127
128 BEGIN
129 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
130 aso_debug_pub.add('Function Get_ContractTemplate Default -- Begin :',1,'N');
131 END IF;
132
133 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
134 l_org_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_Org_id;
135 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
136 l_org_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.O_Org_id;
137 END IF;
138
139 l_contract_template_id := aso_utility_pvt.get_ou_attribute_value(aso_utility_pvt.GET_DEFAULT_CONTRACT_TEMPLATE,l_org_id);
140
141 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
142 aso_debug_pub.add('Function Get_ContractTemplate Contract_Template_Id :' ||l_contract_template_id,1,'N');
143 END IF;
144
145 RETURN l_contract_template_id;
146
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
150 aso_debug_pub.add('Function Get_ContractTemplate NO_DATA_FOUND Exception Occurs: ',1,'N');
151 END IF;
152
153 RETURN NULL;
154
155 WHEN OTHERS THEN
156
157 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
158 aso_debug_pub.add('Function Get_ContractTemplate Inside When Others Exception',1,'N');
159 END IF;
160
161 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
162 THEN
163 OE_MSG_PUB.Add_Exc_Msg
164 ( G_PKG_NAME ,
165 ' Get_ContractTemplate '
166 );
167 END IF;
168
169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
170
171 END Get_ContractTemplate;
172
173 FUNCTION Get_CustAcct_From_CustParty (
174 P_Database_Object_Name IN VARCHAR2,
175 P_Attribute_Code IN VARCHAR2
176 ) RETURN NUMBER
177 IS
178
179 CURSOR C_Get_Acct_Count(l_party NUMBER) IS
180 SELECT count(rowid)
181 FROM HZ_CUST_ACCOUNTS
182 WHERE party_id = l_party
183 AND status = 'A'
184 AND trunc(sysdate) BETWEEN trunc(NVL(account_activation_date, sysdate))
185 AND trunc(NVL(account_termination_date, sysdate));
186
187 CURSOR C_Get_Acct (l_party NUMBER) IS
188 SELECT Cust_Account_Id
189 FROM HZ_CUST_ACCOUNTS
190 WHERE Party_Id = l_party
191 AND status = 'A'
192 AND trunc(sysdate) BETWEEN trunc(NVL(account_activation_date, sysdate))
193 AND trunc(NVL(account_termination_date, sysdate));
194
195 CURSOR C_oldest_Account(l_party NUMBER) IS
196 SELECT CUST_ACCOUNT_ID
197 FROM HZ_CUST_ACCOUNTS
198 WHERE Party_id = l_party
199 AND status = 'A'
200 AND trunc(sysdate) BETWEEN trunc(NVL(account_activation_date, sysdate)) AND
201 trunc(NVL(account_termination_date, sysdate))
202 AND ROWNUM = 1
203 ORDER BY account_activation_date;
204
205 l_party_id NUMBER;
206 l_ret_value NUMBER;
207 l_acct_count NUMBER;
208
209 BEGIN
210
211 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
212 aso_debug_pub.add('Function Get_CustAcct_From_CustParty -- Begin :',1,'N');
213 END IF;
214
215 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
216 l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_cust_party_id;
217
218 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
219 aso_debug_pub.add('Function Get_CustAcct_From_CustParty Database Object ASO_AK_QUOTE_HEADER_V '||
220 ' l_party_id : ' || l_party_id ,1,'N');
221 END IF;
222
223 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
224 l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_cust_party_id;
225
226 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
227 aso_debug_pub.add('Function Get_CustAcct_From_CustParty Database Object ASO_AK_QUOTE_OPPTY_V '||
228 ' l_party_id : ' || l_party_id ,1,'N');
229 END IF;
230
231 END IF;
232
233 OPEN C_Get_Acct_Count (l_party_id);
234 FETCH C_Get_Acct_Count INTO l_acct_count;
235 CLOSE C_Get_Acct_Count;
236
237 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
238 aso_debug_pub.add('Function Get_CustAcct_From_CustParty Cursor C_Get_Acct_Count l_acct_count : ' ||
239 l_acct_count ,1,'N');
240 END IF;
241
242 IF l_acct_count > 1 THEN
243 OPEN C_oldest_Account(l_party_id);
244 FETCH C_oldest_Account INTO l_ret_value;
245 CLOSE C_oldest_Account;
246 ELSE
247
248 OPEN C_Get_Acct (l_party_id);
249 FETCH C_Get_Acct INTO l_ret_value;
250 CLOSE C_Get_Acct;
251 END IF;
252
253 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
254 aso_debug_pub.add('Function Get_CustAcct_From_CustParty Returns : ' || l_ret_value ,1,'N');
255 END IF;
256
257 RETURN l_ret_value;
258
259 EXCEPTION
260 WHEN NO_DATA_FOUND THEN
261 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
262 aso_debug_pub.add('Function Get_CustAcct_From_CustParty NO_DATA_FOUND Exception Occurs: ',1,'N');
263 END IF;
264
265 RETURN NULL;
266
267 WHEN OTHERS THEN
268
269 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
270 aso_debug_pub.add('Function Get_CustAcct_From_CustParty Inside When Others Exception',1,'N');
271 END IF;
272
273 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
274 THEN
275 OE_MSG_PUB.Add_Exc_Msg
276 ( G_PKG_NAME ,
277 ' Get_CustAcct_From_CustParty '
278 );
279 END IF;
280
281 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
282
283 END Get_CustAcct_From_CustParty;
284
285 FUNCTION Get_PriceList_From_Agreement (
286 P_Database_Object_Name IN VARCHAR2,
287 P_Attribute_Code IN VARCHAR2
288 ) RETURN NUMBER
289 IS
290
291 CURSOR C_Get_PL (l_agreement_id NUMBER) IS
292 SELECT Price_List_Id
293 FROM OE_AGREEMENTS_B
294 WHERE Agreement_Id = l_agreement_id
295 AND sysdate BETWEEN nvl(Start_Date_Active,sysdate) AND nvl(End_Date_Active,sysdate);
296
297 l_agreement_id NUMBER;
298 l_ret_value NUMBER;
299
300 BEGIN
301 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
302 aso_debug_pub.add('Function Get_PriceList_From_Agreement -- Begin :',1,'N');
303 END IF;
304
305
306 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
307 l_agreement_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_contract_id;
308
309 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
310 aso_debug_pub.add('Function Get_PriceList_From_Agreement Database Object ASO_AK_QUOTE_HEADER_V '||
311 ' l_agreement_id : ' || l_agreement_id ,1,'N');
312 END IF;
313
314 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
315 l_agreement_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_contract_id;
316
317 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
318 aso_debug_pub.add('Function Get_PriceList_From_Agreement Database Object ASO_AK_QUOTE_OPPTY_V '||
319 ' l_agreement_id : ' || l_agreement_id ,1,'N');
320 END IF;
321 ELSIF p_database_object_name = 'ASO_AK_QUOTE_LINE_V' THEN
322 l_agreement_id := ASO_QUOTE_LINE_DEF_HDLR.g_record.l_agreement_id;
323
324 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
325 aso_debug_pub.add('Function Get_PriceList_From_Agreement Database Object ASO_AK_QUOTE_LINE_V '||
326 ' l_agreement_id : ' || l_agreement_id ,1,'N');
327 END IF;
328 END IF;
329
330 OPEN C_Get_PL (l_agreement_id);
331 FETCH C_Get_PL INTO l_ret_value;
332 CLOSE C_Get_PL;
333
334 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
335 aso_debug_pub.add('Function Get_PriceList_From_Agreement Cursor C_Get_PL l_ret_vaule : ' ||
336 l_ret_value,1,'N');
337 END IF;
338
339 RETURN l_ret_value;
340
341 EXCEPTION
342
343 WHEN NO_DATA_FOUND THEN
344 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
345 aso_debug_pub.add('Function Get_PriceList_From_Agreement NO_DATA_FOUND Exception Occurs: ',1,'N');
346 END IF;
347
348 RETURN NULL;
349
350 WHEN OTHERS THEN
351
352 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
353 aso_debug_pub.add('Function Get_PriceList_From_Agreement Inside When Others Exception',1,'N');
354 END IF;
355
356 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
357 THEN
358 OE_MSG_PUB.Add_Exc_Msg
359 ( G_PKG_NAME ,
360 ' Get_PriceList_From_Agreement '
361 );
362 END IF;
363
364 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
365
366 END Get_PriceList_From_Agreement;
367
368
369 FUNCTION Get_PriceList_From_CustAcct (
370 P_Database_Object_Name IN VARCHAR2,
371 P_Attribute_Code IN VARCHAR2
372 ) RETURN NUMBER
373 IS
374
375 CURSOR C_Get_PL (l_acct NUMBER) IS
376 SELECT Price_List_Id
377 FROM HZ_CUST_ACCOUNTS
378 WHERE Cust_Account_Id = l_acct
379 AND Status = 'A';
380
381 CURSOR C_Get_CustAcct(l_header_id NUMBER) IS
382 SELECT cust_account_id
383 FROM ASO_QUOTE_HEADERS_ALL
384 WHERE quote_header_id = l_header_id;
385
386
387 l_acct_id NUMBER;
388 l_ret_value NUMBER;
389 l_quote_header_id NUMBER;
390
391 BEGIN
392 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
393 aso_debug_pub.add('Function Get_PriceList_From_CustAcct -- Begin :',1,'N');
394 END IF;
395
396 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
397 l_acct_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_cust_account_id;
398
399 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
400 aso_debug_pub.add('Function Get_PriceList_From_CustAcct Database Object ASO_AK_QUOTE_HEADER_V '||
401 ' l_acct_id : ' || l_acct_id ,1,'N');
402 END IF;
403
404 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
405 l_acct_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_cust_account_id;
406
407 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
408 aso_debug_pub.add('Function Get_PriceList_From_CustAcct Database Object ASO_AK_QUOTE_OPPTY_V '||
409 ' l_acct_id : ' || l_acct_id ,1,'N');
410 END IF;
411
412 END IF;
413
414 OPEN C_Get_PL (l_acct_id);
415 FETCH C_Get_PL INTO l_ret_value;
416 CLOSE C_Get_PL;
417
418 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
419 aso_debug_pub.add('Function Get_PriceList_From_CustAcct Cursor C_Get_PL l_rel_value : ' ||
420 l_ret_value ,1,'N');
421 END IF;
422
423 RETURN l_ret_value;
424
425 EXCEPTION
426 WHEN NO_DATA_FOUND THEN
427 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
428 aso_debug_pub.add('Function Get_PriceList_From_CustAcct NO_DATA_FOUND Exception Occurs: ',1,'N');
429 END IF;
430
431 RETURN NULL;
432
433 WHEN OTHERS THEN
434
435 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
436 aso_debug_pub.add('Function Get_PriceList_From_CustAcct Inside When Others Exception',1,'N');
437 END IF;
438
439 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
440 THEN
441 OE_MSG_PUB.Add_Exc_Msg
442 ( G_PKG_NAME ,
443 ' Get_PriceList_From_CustAcct '
444 );
445 END IF;
446
447 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
448
449 END Get_PriceList_From_CustAcct;
450
451
452 FUNCTION Get_PriceList_From_OrderType (
453 P_Database_Object_Name IN VARCHAR2,
454 P_Attribute_Code IN VARCHAR2
455 ) RETURN NUMBER
456 IS
457
458 l_order_type_id NUMBER;
459 l_ret_value NUMBER;
460
461 CURSOR C_Get_PL (l_order_type NUMBER) IS
462 SELECT Price_List_Id
463 FROM OE_TRANSACTION_TYPES_ALL
464 WHERE Transaction_Type_Id = l_order_type
465 AND sysdate BETWEEN nvl(Start_Date_Active,sysdate) AND nvl(End_Date_Active,sysdate);
466
467
468 BEGIN
469 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
470 aso_debug_pub.add('Function Get_PriceList_From_OrderType -- Begin : ',1,'N');
471 END IF;
472
473 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
474 l_order_type_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_order_type_id;
475
476 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
477 aso_debug_pub.add('Function Get_PriceList_From_OrderType Database Object ASO_AK_QUOTE_HEADER_V '||
478 ' l_order_type_id : ' || l_order_type_id ,1,'N');
479 END IF;
480
481 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
482 l_order_type_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_order_type_id;
483
484 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
485 aso_debug_pub.add('Function Get_PriceList_From_OrderType Database Object ASO_AK_QUOTE_OPPTY_V '||
486 ' l_order_type_id : ' || l_order_type_id ,1,'N');
487 END IF;
488 END IF;
489
490 OPEN C_Get_PL (l_order_type_id);
491 FETCH C_Get_PL INTO l_ret_value;
492 CLOSE C_Get_PL;
493
494 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
495 aso_debug_pub.add('Function Get_PriceList_From_OrderType l_ret_value : ' || l_ret_value ,1,'N');
496 END IF;
497
498 RETURN l_ret_value;
499
500 EXCEPTION
501
502 WHEN NO_DATA_FOUND THEN
503 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
504 aso_debug_pub.add('Function Get_PriceList_From_OrderType NO_DATA_FOUND Exception Occurs: ',1,'N');
505 END IF;
506
507 RETURN NULL;
508
509 WHEN OTHERS THEN
510 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
511 aso_debug_pub.add('Function Get_PriceList_From_OrderType Inside When Others Exception',1,'N');
512 END IF;
513
514 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
515 THEN
516 OE_MSG_PUB.Add_Exc_Msg
517 ( G_PKG_NAME ,
518 ' Get_PriceList_From_OrderType '
519 );
520 END IF;
521
522 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
523
524 END Get_PriceList_From_OrderType;
525
526
527 FUNCTION Get_PaymentTerm_From_Agreement (
528 P_Database_Object_Name IN VARCHAR2,
529 P_Attribute_Code IN VARCHAR2
530 ) RETURN NUMBER
531 IS
532
533 CURSOR C_Get_PayTerm (l_agreement_id NUMBER) IS
534 SELECT Term_Id
535 FROM OE_AGREEMENTS_B
536 WHERE Agreement_Id = l_agreement_id
537 AND sysdate BETWEEN nvl(Start_Date_Active,sysdate) AND nvl(End_Date_Active,sysdate);
538
539 l_agreement_id NUMBER;
540 l_ret_value NUMBER;
541
542 BEGIN
543 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
544 aso_debug_pub.add('Function Get_PaymentTerm_From_Agreement --- Begin : ' ,1,'N');
545 END IF;
546
547 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
548 l_agreement_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_contract_id;
549
550 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
551 aso_debug_pub.add('Function Get_PaymentTerm_From_Agreement Database Object ASO_AK_QUOTE_HEADER_V '||
552 ' l_agreement_id : ' || l_agreement_id ,1,'N');
553 END IF;
554
555 ELSIF p_database_object_name='ASO_AK_QUOTE_OPPTY_V' THEN
556 l_agreement_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_contract_id;
557
558 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
559 aso_debug_pub.add('Function Get_PaymentTerm_From_Agreement Database Object ASO_AK_QUOTE_OPPTY_V '||
560 ' l_agreement_id : ' || l_agreement_id ,1,'N');
561 END IF;
562
563 END IF;
564
565 OPEN C_Get_PayTerm (l_agreement_id);
566 FETCH C_Get_PayTerm INTO l_ret_value;
567 CLOSE C_Get_PayTerm;
568
569 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
570 aso_debug_pub.add('Function Get_PaymentTerm_From_Agreement Cursor C_Get_PayTerm l_ret_value: '||
571 l_ret_value ,1,'N');
572 END IF;
573 RETURN l_ret_value;
574
575 EXCEPTION
576 WHEN NO_DATA_FOUND THEN
577 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
578 aso_debug_pub.add('Function Get_PaymentTerm_From_Agreement NO_DATA_FOUND Exception Occurs: ',1,'N');
579 END IF;
580
581 RETURN NULL;
582 WHEN OTHERS THEN
583 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
584 aso_debug_pub.add('Function Get_PaymentTerm_From_Agreement Inside When Others Exception',1,'N');
585 END IF;
586
587 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
588 THEN
589 OE_MSG_PUB.Add_Exc_Msg
590 ( G_PKG_NAME ,
591 ' Get_PaymentTerm_From_Agreement '
592 );
593 END IF;
594
595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
596
597 END Get_PaymentTerm_From_Agreement;
598
599 FUNCTION Get_PaymentTerm_From_Customer (
600 P_Database_Object_Name IN VARCHAR2,
601 P_Attribute_Code IN VARCHAR2 ) RETURN NUMBER
602 IS
603
604 Cursor default_payment_term_customer(l_cust_account_id NUMBER) Is
605 select hcp.standard_terms
606 from hz_cust_accounts hca,
607 hz_customer_profiles hcp
608 where hcp.cust_account_id = hca.cust_account_id
609 and hca.cust_account_id = l_cust_account_id
610 and nvl(hcp.status,'A') = 'A';
611 l_cust_acct_id number;
612 l_term_id Number;
613
614 Begin
615 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
616 aso_debug_pub.add('Function Get_PaymentTerm_From_Customer --- Begin : ' ,1,'N');
617 END IF;
618
619 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
620 l_cust_acct_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_Cust_Account_id;
621
622 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
623 aso_debug_pub.add('Function Get_PaymentTerm_From_Customer Database Object ASO_AK_QUOTE_HEADER_V '||
624 ' l_cust_acct_id : ' || l_cust_acct_id ,1,'N');
625 END IF;
626 ELSIF p_database_object_name='ASO_AK_QUOTE_OPPTY_V' THEN
627 l_cust_acct_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_cust_account_id;
628
629 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
630 aso_debug_pub.add('Function Get_PaymentTerm_From_Customer Database Object ASO_AK_QUOTE_OPPTY_V '||
631 ' l_cust_acct_id : ' || l_cust_acct_id ,1,'N');
632 END IF;
633 END IF;
634
635 Open default_payment_term_customer(l_cust_acct_id);
636 Fetch default_payment_term_customer into l_term_id;
637 CLOSE default_payment_term_customer;
638
639 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
640 aso_debug_pub.add('Function Get_PaymentTerm_From_Customer Cursor default_payment_term_customer '||
641 ' l_term_id : ' || l_term_id ,1,'N');
642 END IF;
643
644 RETURN l_term_id;
645
646 EXCEPTION
647 WHEN NO_DATA_FOUND THEN
648 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
649 aso_debug_pub.add('Function Get_PaymentTerm_From_Customer NO_DATA_FOUND Exception Occurs: ',1,'N');
650 END IF;
651 RETURN NULL;
652
653 WHEN OTHERS THEN
654 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
655 aso_debug_pub.add('Function Get_PaymentTerm_From_Customer Inside When Others Exception',1,'N');
656 END IF;
657
658 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
659 THEN
660 OE_MSG_PUB.Add_Exc_Msg
661 ( G_PKG_NAME ,
662 ' Get_PaymentTerm_From_Customer '
663 );
664 END IF;
665
666 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
667
668 END Get_PaymentTerm_From_Customer;
669
670 FUNCTION Get_ExpirationDate (
671 P_Database_Object_Name IN VARCHAR2,
672 P_Attribute_Code IN VARCHAR2
673 ) RETURN DATE
674 IS
675
676 CURSOR C_Get_Expiration_Date (l_Def_Cal VARCHAR2, l_Def_Per VARCHAR2) IS
677 SELECT End_Date
678 FROM GL_PERIODS_V
679 WHERE Period_Type = l_Def_Per
680 AND Period_Set_Name = l_Def_Cal
681 AND SYSDATE BETWEEN NVL(Start_Date,sysdate) AND NVL(End_Date,sysdate);
682
683 l_Default_Cal_Prof VARCHAR2(15):= FND_PROFILE.VALUE ('ASO_DEFAULT_EXP_GL_CAL' );
684 l_Default_Per_Prof VARCHAR2(15):= FND_PROFILE.VALUE ('ASO_DEFAULT_EXP_GL_PERIOD' );
685 l_qte_duration_prof NUMBER := NVL(FND_PROFILE.VALUE ('ASO_QUOTE_DURATION'),30);
686
687 l_Quote_Exp_Date DATE;
688
689 BEGIN
690 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
691 aso_debug_pub.add('Function Get_ExpiraionDate -- Begin: ',1,'N');
692 END IF;
693
694 IF l_Default_Cal_Prof IS NOT NULL
695 AND l_Default_Per_Prof IS NOT NULL THEN
696
697 OPEN C_Get_Expiration_Date(l_Default_Cal_Prof , l_Default_Per_Prof );
698 FETCH C_Get_Expiration_Date INTO l_Quote_Exp_Date;
699 CLOSE C_Get_Expiration_Date;
700
701 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
702 aso_debug_pub.add('Function Get_ExpirationDate Cursor C_Get_Expiration_Date l_Quote_Exp_Date : '||
703 l_Quote_Exp_Date ,1,'N');
704 END IF;
705
706 IF l_Quote_Exp_Date IS NULL THEN
707 l_Quote_Exp_Date := SYSDATE + l_Qte_Duration_Prof;
708 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
709 aso_debug_pub.add('Function Get_ExpirationDate Cursor C_Get_Expiration_Date returns ' ||
710 ' l_Quote_Exp_Date AS Null Then l_Quote_Exp_Date :' || l_Quote_Exp_Date ,1,'N');
711 END IF;
712
713 RETURN l_Quote_Exp_Date;
714
715 END IF;
716
717 RETURN l_Quote_Exp_Date;
718
719
720
721 ELSE
722
723 l_Quote_Exp_Date := SYSDATE + l_Qte_Duration_Prof;
724
725 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
726 aso_debug_pub.add('Function Get_ExpirationDate if Profile ASO_DEFAULT_EXP_GL_CAL and Profile '|| ' ASO_DEFAULT_EXP_GL_PERIOD ARE NULL Then l_Quote_Exp_Date : ' || l_Quote_Exp_Date ,1,'N');
727 END IF;
728
729 RETURN l_Quote_Exp_Date;
730
731 END IF;
732
733 EXCEPTION
734 WHEN NO_DATA_FOUND THEN
735 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
736 aso_debug_pub.add('Function Get_ExpirationDate NO_DATA_FOUND Exception Occurs: ',1,'N');
737 END IF;
738
739 RETURN NULL;
740 WHEN OTHERS THEN
741 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
742 aso_debug_pub.add('Function Get_ExpirationDate Inside When Others Exception',1,'N');
743 END IF;
744 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
745 THEN
746 OE_MSG_PUB.Add_Exc_Msg
747 ( G_PKG_NAME ,
748 ' Get_ExpirationDate '
749 );
750 END IF;
751
752 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
753
754 END Get_ExpirationDate;
755
756
757
758
759
760 FUNCTION Get_QuotePhone (
761 P_Database_Object_Name IN VARCHAR2,
762 P_Attribute_Code IN VARCHAR2
763 ) RETURN NUMBER
764 IS
765
766 CURSOR C_Get_Type (l_party NUMBER) IS
767 SELECT Party_Type
768 FROM HZ_PARTIES
769 WHERE Party_Id = l_party
770 AND Status = 'A';
771
772 CURSOR C_Get_Phone (l_party NUMBER) IS
773 SELECT Contact_Point_Id
774 FROM HZ_CONTACT_POINTS
775 WHERE Owner_Table_Id = l_party
776 AND Owner_Table_Name = 'HZ_PARTIES'
777 AND Contact_Point_Type = 'PHONE'
778 AND Status = 'A'
779 AND Primary_Flag = 'Y';
780
781 l_party_id NUMBER;
782 l_cust_party_id NUMBER;
783 l_ret_value NUMBER;
784 l_cust_party_type VARCHAR2(15);
785
786 BEGIN
787 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
788 aso_debug_pub.add('Function Get_QuotePhone --- Begin ',1,'N');
789 END IF;
790
791 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
792 l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.q_cust_party_id;
793 l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.q_party_id;
794
795 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
796 aso_debug_pub.add('Function Get_QuotePhone Database Object ASO_AK_QUOTE_HEADER_V '||
797 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
798 aso_debug_pub.add('Function Get_QuotePhone Database Object ASO_AK_QUOTE_HEADER_V '||
799 ' l_party_id :' || l_party_id ,1,'N');
800 END IF;
801
802 ELSIF p_database_object_name='ASO_AK_QUOTE_OPPTY_V' THEN
803 --l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_cust_party_id; --Bug#5195151
804 l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_cust_party_id;
805 l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_party_id;
806
807 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
808 aso_debug_pub.add('Function Get_QuotePhone Database Object ASO_AK_QUOTE_OPPTY_V '||
809 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
810 aso_debug_pub.add('Function Get_QuotePhone Database Object ASO_AK_QUOTE_OPPTY_V '||
811 ' l_party_id :' || l_party_id ,1,'N');
812 END IF;
813
814 END IF;
815
816 OPEN C_Get_Type (l_cust_party_id);
817 FETCH C_Get_Type INTO l_cust_party_type;
818 CLOSE C_Get_Type;
819
820 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
821 aso_debug_pub.add('Function Get_QuotePhone Customer Party Type : '||l_cust_party_type,1,'N');
822 END IF;
823
824 IF l_cust_party_type = 'PERSON' THEN
825
826 OPEN C_Get_Phone (l_cust_party_id);
827 FETCH C_Get_Phone INTO l_ret_value;
828 CLOSE C_Get_Phone;
829
830 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
831 aso_debug_pub.add('Function Get_QuotePhone Customer Party Type is PERSON l_ret_value: '||
832 l_ret_value,1,'N');
833 END IF;
834 ELSE
835
836 IF l_party_id IS NOT NULL AND l_party_id <> FND_API.G_MISS_NUM THEN
837 OPEN C_Get_Phone (l_party_id);
838 FETCH C_Get_Phone INTO l_ret_value;
839 CLOSE C_Get_Phone;
840 END IF;
841
842 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
843 aso_debug_pub.add('Function Get_QuotePhone Customer Party Type is Organization l_ret_value:'||
844 l_ret_value,1,'N');
845 END IF;
846
847 END IF;
848
849 RETURN l_ret_value;
850
851 EXCEPTION
852 WHEN NO_DATA_FOUND THEN
853 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
854 aso_debug_pub.add('Function Get_QuotePhone NO_DATA_FOUND Exception Occurs : ',1,'N');
855 END IF;
856
857 RETURN NULL;
858 WHEN OTHERS THEN
859 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
860 aso_debug_pub.add('Function Get_QuotePhone Inside When Others Exception',1,'N');
861 END IF;
862
863 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
864 THEN
865 OE_MSG_PUB.Add_Exc_Msg
866 ( G_PKG_NAME ,
867 ' Get_QuotePhone '
868 );
869 END IF;
870
871 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
872
873 END Get_QuotePhone;
874
875 FUNCTION Get_QuoteAddress(
876 P_Database_Object_Name IN VARCHAR2,
877 P_Attribute_Code IN VARCHAR2
878 ) RETURN NUMBER
879 IS
880
881 CURSOR C_Get_Type (l_party NUMBER) IS
882 SELECT Party_Type
883 FROM HZ_PARTIES
884 WHERE Party_Id = l_party
885 AND Status = 'A';
886
887 CURSOR C_Use_Exists (l_party NUMBER) IS
888 SELECT A.Party_Site_Id
889 FROM HZ_PARTY_SITES A, HZ_PARTY_SITE_USES B
890 WHERE A.Party_Id = l_party
891 AND A.Party_Site_Id = B.Party_Site_Id
892 AND B.Site_Use_Type = 'SOLD_TO'
893 AND B.Primary_Per_Type = 'Y'
894 AND A.Status = 'A'
895 AND B.Status = 'A';
896
897 CURSOR C_Get_PrAddr (l_party NUMBER) IS
898 SELECT Party_Site_Id
899 FROM HZ_PARTY_SITES
900 WHERE Party_Id = l_party
901 AND Identifying_Address_Flag = 'Y'
902 AND Status = 'A';
903
904 l_cust_party_type VARCHAR2(15);
905 l_cust_party_id NUMBER;
906 l_party_id NUMBER;
907 x_party_site_id NUMBER;
908
909 BEGIN
910 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
911 aso_debug_pub.add('Function Get_QuoteAddress --- Begin :',1,'N');
912 END IF;
913
914 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
915 l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_cust_party_id;
916 l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_party_id;
917
918 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
919 aso_debug_pub.add('Function Get_QuoteAddress Database Object ASO_AK_QUOTE_HEADER_V '||
920 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
921 aso_debug_pub.add('Function Get_QuoteAddress Database Object ASO_AK_QUOTE_HEADER_V '||
922 ' l_party_id :' || l_party_id ,1,'N');
923 END IF;
924
925 ELSIF p_database_object_name='ASO_AK_QUOTE_OPPTY_V' THEN
926 --l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_cust_party_id; --Bug#5195151
927 l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_cust_party_id;
928 l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_party_id;
929
930 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
931 aso_debug_pub.add('Function Get_QuoteAddress Database Object ASO_AK_QUOTE_OPPTY_V '||
932 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
933 aso_debug_pub.add('Function Get_QuoteAddress Database Object ASO_AK_QUOTE_OPPTY_V '||
934 ' l_party_id :' || l_party_id ,1,'N');
935 END IF;
936 END IF;
937
938 OPEN C_Get_Type (l_cust_party_id);
939 FETCH C_Get_Type INTO l_cust_party_type;
940 CLOSE C_Get_Type;
941
942 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
943 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Get_Type l_cust_party_type :'||
944 l_cust_party_type,1,'N');
945 END IF;
946
947 /* if Party is PERSON */
948 IF l_cust_party_type = 'PERSON' THEN
949 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
950 aso_debug_pub.add('Function Get_QuoteAddress IF l_cust_party_type is PERSON :',1,'N');
951 END IF;
952 /* Get Primary Sold To Address for Quote to Customer */
953 OPEN C_Use_Exists (l_cust_party_id);
954 FETCH C_Use_Exists INTO x_party_site_id;
955 CLOSE C_Use_Exists;
956
957 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
958 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Use_Exists Primary Address ' ||
959 ' x_party_site_id :' ||x_party_site_id ,1,'N');
960 END IF;
961 IF x_party_site_id IS NULL THEN
962 /* Get Identifying Address for Quote to Customer */
963 OPEN C_Get_PrAddr (l_cust_party_id);
964 FETCH C_Get_PrAddr INTO x_party_site_id;
965 CLOSE C_Get_PrAddr;
966 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
967 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Get_PrAddr Identifying Address'||
968 ' x_party_site_id :' ||x_party_site_id ,1,'N');
969 END IF;
970 END IF;
971
972 ELSE
973 /* If Party Type is Organization */
974 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
975 aso_debug_pub.add('Function Get_QuoteAddress IF l_cust_party_type is ORGANIZATION :',1,'N');
976 END IF;
977 IF nvl(l_party_id,l_cust_party_id) = l_cust_party_id THEN
978 /* if party type is Organization and contact is not specified */
979
980 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
981 aso_debug_pub.add('Function Get_QuoteAddress IF l_cust_party_type is ORGANIZATION '||
982 ' and Contact is not Specified :',1,'N');
983 END IF;
984
985 /* Get Primary Sold to Address for Quote to Customer */
986
987 OPEN C_Use_Exists (l_cust_party_id);
988 FETCH C_Use_Exists INTO x_party_site_id;
989 CLOSE C_Use_Exists;
990
991 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
992 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Use_Exists Primary Address ' ||
993 ' x_party_site_id :' ||x_party_site_id ,1,'N');
994 END IF;
995
996 IF x_party_site_id IS NULL THEN
997 /* Get Identifying Address for Quote to Customer */
998
999 OPEN C_Get_PrAddr (l_cust_party_id);
1000 FETCH C_Get_PrAddr INTO x_party_site_id;
1001 CLOSE C_Get_PrAddr;
1002
1003 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1004 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Get_PrAddr Identifying Address '||
1005 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1006 END IF;
1007 END IF; ----- TEST
1008 ELSIF l_party_id <> l_cust_party_id THEN
1009 /* if party type is Organization and contact is specified */
1010 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1011 aso_debug_pub.add('Function Get_QuoteAddress IF l_cust_party_type is ORGANIZATION '||
1012 ' and Contact is Specified :',1,'N');
1013 END IF;
1014 /* Get Primary 'SOLD TO' address for Quote to Contact */
1015 OPEN C_Use_Exists (l_party_id);
1016 FETCH C_Use_Exists INTO x_party_site_id;
1017 CLOSE C_Use_Exists;
1018
1019 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1020 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Use_Exists Primary Address ' ||
1021 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1022 END IF;
1023
1024 IF x_party_site_id IS NULL THEN
1025 /* Get Identifying Address for Quote to Contact */
1026
1027 OPEN C_Get_PrAddr (l_party_id);
1028 FETCH C_Get_PrAddr INTO x_party_site_id;
1029 CLOSE C_Get_PrAddr;
1030
1031 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1032 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Get_PrAddr Identifying Address '||
1033 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1034 END IF;
1035
1036 END IF;
1037
1038 IF x_party_site_id IS NULL THEN
1039 /* Get Primary 'SOLD TO' address for Quote to Customer */
1040
1041 OPEN C_Use_Exists (l_cust_party_id);
1042 FETCH C_Use_Exists INTO x_party_site_id;
1043 CLOSE C_Use_Exists;
1044
1045 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1046 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Use_Exists Primary Address ' ||
1047 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1048 END IF;
1049
1050 IF x_party_site_id IS NULL THEN
1051 /* Get Identifying address for Quote to Customer */
1052
1053 OPEN C_Get_PrAddr (l_cust_party_id);
1054 FETCH C_Get_PrAddr INTO x_party_site_id;
1055 CLOSE C_Get_PrAddr;
1056
1057 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1058 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Get_PrAddr Identifying '||
1059 ' Address x_party_site_id :' ||x_party_site_id ,1,'N');
1060 END IF;
1061 END IF; -- if x_party_site_id IS NULL
1062
1063 END IF; -- if x_party_site_id IS NULL
1064
1065 END IF;
1066
1067 END IF;
1068 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1069 aso_debug_pub.add('Function Get_QuoteAddress Party_Site_ID :' || x_party_site_id ,1,'N');
1070 END IF;
1071
1072 RETURN x_party_site_id;
1073
1074 EXCEPTION
1075 WHEN NO_DATA_FOUND THEN
1076 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1077 aso_debug_pub.add('Function Get_QuoteAddress NO_DATA_FOUND Exception Occurs :',1,'N');
1078 END IF;
1079
1080 RETURN NULL;
1081 WHEN OTHERS THEN
1082 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1083 aso_debug_pub.add('Function Get_QuoteAddress Inside When Others Exception',1,'N');
1084 END IF;
1085
1086 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1087 OE_MSG_PUB.Add_Exc_Msg
1088 ( G_PKG_NAME ,
1089 ' Get_QuoteAddress '
1090 );
1091 END IF;
1092
1093 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1094
1095 END Get_QuoteAddress;
1096
1097 FUNCTION Get_BillAddress(
1098 P_Database_Object_Name IN VARCHAR2,
1099 P_Attribute_Code IN VARCHAR2
1100 ) RETURN NUMBER
1101 IS
1102
1103 CURSOR C_Get_Type (l_party NUMBER) IS
1104 SELECT Party_Type
1105 FROM HZ_PARTIES
1106 WHERE Party_Id = l_party
1107 AND Status = 'A';
1108
1109 CURSOR C_Use_Exists (l_party NUMBER) IS
1110 SELECT A.Party_Site_Id
1111 FROM HZ_PARTY_SITES A, HZ_PARTY_SITE_USES B
1112 WHERE A.Party_Id = l_party
1113 AND A.Party_Site_Id = B.Party_Site_Id
1114 AND B.Site_Use_Type = 'BILL_TO'
1115 AND B.Primary_Per_Type = 'Y'
1116 AND A.Status = 'A'
1117 AND B.Status = 'A';
1118
1119 CURSOR C_Get_PrAddr (l_party NUMBER) IS
1120 SELECT Party_Site_Id
1121 FROM HZ_PARTY_SITES
1122 WHERE Party_Id = l_party
1123 AND Identifying_Address_Flag = 'Y'
1124 AND Status = 'A';
1125
1126 l_cust_party_type VARCHAR2(15);
1127 l_cust_party_id NUMBER;
1128 l_party_id NUMBER;
1129 x_party_site_id NUMBER;
1130
1131 BEGIN
1132 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1133 aso_debug_pub.add('Function Get_BillAddress --- Begin : ',1,'N');
1134 END IF;
1135
1136 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
1137 --l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_cust_party_id;
1138 l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_INV_TO_CUST_PTY_ID ;
1139 --l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_party_id;
1140 l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_INV_TO_PTY_ID ;
1141
1142 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1143 aso_debug_pub.add('Function Get_BillAddress Database Object ASO_AK_QUOTE_HEADER_V '||
1144 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
1145 aso_debug_pub.add('Function Get_BillAddress Database Object ASO_AK_QUOTE_HEADER_V '||
1146 ' l_party_id :' || l_party_id ,1,'N');
1147 END IF;
1148
1149 ELSIF p_database_object_name='ASO_AK_QUOTE_OPPTY_V' THEN
1150 --l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_cust_party_id;
1151 --l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_party_id;
1152 l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_cust_party_id;
1153 l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_sld_to_cont_id;
1154
1155 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1156 aso_debug_pub.add('Function Get_BillAddress Database Object ASO_AK_QUOTE_OPPTY_V '||
1157 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
1158 aso_debug_pub.add('Function Get_BillAddress Database Object ASO_AK_QUOTE_OPPTY_V '||
1159 ' l_party_id :' || l_party_id ,1,'N');
1160 END IF;
1161
1162 END IF;
1163
1164 OPEN C_Get_Type (l_cust_party_id);
1165 FETCH C_Get_Type INTO l_cust_party_type;
1166 CLOSE C_Get_Type;
1167 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1168 aso_debug_pub.add('Function Get_BillAddress Cursor C_Get_Type l_cust_party_type :'||
1169 l_cust_party_type,1,'N');
1170 END IF;
1171 /* if Party Type is PERSON */
1172 IF l_cust_party_type = 'PERSON' THEN
1173 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1174 aso_debug_pub.add('Function Get_BillAddress IF l_cust_party_type is PERSON :',1,'N');
1175 END IF;
1176 /* Get Primary Bill To Address for Bill to Customer */
1177 OPEN C_Use_Exists (l_cust_party_id);
1178 FETCH C_Use_Exists INTO x_party_site_id;
1179 CLOSE C_Use_Exists;
1180
1181 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1182 aso_debug_pub.add('Function Get_BillAddress Cursor C_Use_Exists Primary Address ' ||
1183 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1184 END IF;
1185
1186 IF x_party_site_id IS NULL THEN
1187 /* Get Identifying Address for Bill to Customer */
1188 OPEN C_Get_PrAddr (l_cust_party_id);
1189 FETCH C_Get_PrAddr INTO x_party_site_id;
1190 CLOSE C_Get_PrAddr;
1191
1192 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1193 aso_debug_pub.add('Function Get_BillAddress Cursor C_Get_PrAddr Identifying Address'||
1194 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1195 END IF;
1196
1197 END IF;
1198
1199 ELSE
1200 /* if Party Type is ORGANIZATION */
1201
1202 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1203 aso_debug_pub.add('Function Get_BillAddress IF l_cust_party_type is Organization :',1,'N');
1204 END IF;
1205 IF nvl(l_party_id,l_cust_party_id) = l_cust_party_id THEN
1206
1207 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1208 aso_debug_pub.add('Function Get_BillAddress IF l_cust_party_type is ORGANIZATION '||
1209 ' and Contact is not Specified :',1,'N');
1210 END IF;
1211 /* Get Primary Bill To Address for Bill to Customer */
1212 OPEN C_Use_Exists (l_cust_party_id);
1213 FETCH C_Use_Exists INTO x_party_site_id;
1214 CLOSE C_Use_Exists;
1215
1216 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1217 aso_debug_pub.add('Function Get_BillAddress Cursor C_Use_Exists Primary Address ' ||
1218 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1219 END IF;
1220
1221 IF x_party_site_id IS NULL THEN
1222 /* Get Identifying Address for Bill to Customer */
1223
1224 OPEN C_Get_PrAddr (l_cust_party_id);
1225 FETCH C_Get_PrAddr INTO x_party_site_id;
1226 CLOSE C_Get_PrAddr;
1227
1228 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1229 aso_debug_pub.add('Function Get_BillAddress Cursor C_Get_PrAddr Identifying Address'||
1230 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1231 END IF;
1232
1233 END IF;
1234
1235 ELSIF l_party_id <> l_cust_party_id THEN
1236
1237 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1238 aso_debug_pub.add('Function Get_BillAddress IF l_cust_party_type is ORGANIZATION '||
1239 ' and Contact is Specified :',1,'N');
1240 END IF;
1241
1242 /* Get Primary Bill To Address for Bill to Customer */
1243 OPEN C_Use_Exists (l_party_id);
1244 FETCH C_Use_Exists INTO x_party_site_id;
1245 CLOSE C_Use_Exists;
1246
1247 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1248 aso_debug_pub.add('Function Get_BillAddress Cursor C_Use_Exists Primary Address ' ||
1249 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1250 END IF;
1251
1252 IF x_party_site_id IS NULL THEN
1253 /* Get Identifying Address for Bill to Customer */
1254
1255 OPEN C_Get_PrAddr (l_party_id);
1256 FETCH C_Get_PrAddr INTO x_party_site_id;
1257 CLOSE C_Get_PrAddr;
1258
1259 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1260 aso_debug_pub.add('Function Get_BillAddress Cursor C_Get_PrAddr Identifying Address'||
1261 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1262 END IF;
1263 END IF;
1264
1265 IF x_party_site_id IS NULL THEN
1266 /* Get Primary Bill To Address for Bill to Customer */
1267 OPEN C_Use_Exists (l_cust_party_id);
1268 FETCH C_Use_Exists INTO x_party_site_id;
1269 CLOSE C_Use_Exists;
1270
1271 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1272 aso_debug_pub.add('Function Get_BillAddress Cursor C_Use_Exists Primary Address ' ||
1273 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1274 END IF;
1275 IF x_party_site_id IS NULL THEN
1276 /* Get Identifying Address for Bill to Customer */
1277
1278 OPEN C_Get_PrAddr (l_cust_party_id);
1279 FETCH C_Get_PrAddr INTO x_party_site_id;
1280 CLOSE C_Get_PrAddr;
1281
1282 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1283 aso_debug_pub.add('Function Get_BillAddress Cursor C_Get_PrAddr Identifying Address'||
1284 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1285 END IF;
1286
1287 END IF;
1288
1289
1290 END IF;
1291
1292 END IF;
1293
1294 END IF;
1295 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1296 aso_debug_pub.add('Function Get_BillAddress Party_Site_ID Returned :'||x_party_site_id ,1,'N');
1297 END IF;
1298
1299 RETURN x_party_site_id;
1300
1301 EXCEPTION
1302 WHEN NO_DATA_FOUND THEN
1303 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1304 aso_debug_pub.add('Function Get_BillAddress NO_DATA_FOUND Exception Occurs',1,'N');
1305 END IF;
1306
1307 RETURN NULL;
1308 WHEN OTHERS THEN
1309 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1310 aso_debug_pub.add('Function Get_BillAddress Inside When Others Exception',1,'N');
1311 END IF;
1312 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1313 THEN
1314 OE_MSG_PUB.Add_Exc_Msg
1315 ( G_PKG_NAME ,
1316 ' Get_BillAddress '
1317 );
1318 END IF;
1319
1320 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1321
1322 END Get_BillAddress;
1323
1324 FUNCTION Get_ShipAddress(
1325 P_Database_Object_Name IN VARCHAR2,
1326 P_Attribute_Code IN VARCHAR2
1327 ) RETURN NUMBER
1328 IS
1329
1330 CURSOR C_Get_Type (l_party NUMBER) IS
1331 SELECT Party_Type
1332 FROM HZ_PARTIES
1333 WHERE Party_Id = l_party
1334 AND Status = 'A';
1335
1336 CURSOR C_Use_Exists (l_party NUMBER) IS
1337 SELECT A.Party_Site_Id
1338 FROM HZ_PARTY_SITES A, HZ_PARTY_SITE_USES B
1339 WHERE A.Party_Id = l_party
1340 AND A.Party_Site_Id = B.Party_Site_Id
1341 AND B.Site_Use_Type = 'SHIP_TO'
1342 AND B.Primary_Per_Type = 'Y'
1343 AND A.Status = 'A'
1344 AND B.Status = 'A';
1345
1346 CURSOR C_Get_PrAddr (l_party NUMBER) IS
1347 SELECT Party_Site_Id
1348 FROM HZ_PARTY_SITES
1349 WHERE Party_Id = l_party
1350 AND Identifying_Address_Flag = 'Y'
1351 AND Status = 'A';
1352
1353 l_cust_party_type VARCHAR2(15);
1354 l_cust_party_id NUMBER;
1355 l_party_id NUMBER;
1356 x_party_site_id NUMBER;
1357
1358 BEGIN
1359 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1360 aso_debug_pub.add('Function Get_ShipAddress --- Begin : ',1,'N');
1361 END IF;
1362
1363 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
1364
1365 /* Done code change for Bug - 5763528, commented following code -
1366
1367 --l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_cust_party_id;
1368 l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_INV_TO_CUST_PTY_ID;
1369 --l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_party_id;
1370 l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_INV_TO_PTY_ID; */
1371
1372 -- Following code replaced above code
1373
1374 l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_SHIP_TO_CUST_PARTY_ID;
1375 l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_SHIP_TO_PARTY_ID;
1376
1377 -- End of code for Bug - 5763528
1378
1379 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1380 aso_debug_pub.add('Function Get_ShipAddress Database Object ASO_AK_QUOTE_HEADER_V '||
1381 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
1382 aso_debug_pub.add('Function Get_ShipAddress Database Object ASO_AK_QUOTE_HEADER_V '||
1383 ' l_party_id :' || l_party_id ,1,'N');
1384 END IF;
1385
1386 ELSIF p_database_object_name='ASO_AK_QUOTE_OPPTY_V' THEN
1387 --l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_cust_party_id;
1388 --l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_party_id;
1389 l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_cust_party_id;
1390 l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_sld_to_cont_id;
1391
1392 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1393 aso_debug_pub.add('Function Get_ShipAddress Database Object ASO_AK_QUOTE_OPPTY_V '||
1394 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
1395 aso_debug_pub.add('Function Get_ShipAddress Database Object ASO_AK_QUOTE_OPPTY_V '||
1396 ' l_party_id :' || l_party_id ,1,'N');
1397 END IF;
1398
1399 END IF;
1400
1401 OPEN C_Get_Type (l_cust_party_id);
1402 FETCH C_Get_Type INTO l_cust_party_type;
1403 CLOSE C_Get_Type;
1404 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1405 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Get_Type l_cust_party_type :'||
1406 l_cust_party_type,1,'N');
1407 END IF;
1408 /* if party type is PERSON */
1409 IF l_cust_party_type = 'PERSON' THEN
1410 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1411 aso_debug_pub.add('Function Get_ShipAddress IF l_cust_party_type is PERSON :',1,'N');
1412 END IF;
1413 /* Get Primary Ship To Address for Ship to Customer */
1414 OPEN C_Use_Exists (l_cust_party_id);
1415 FETCH C_Use_Exists INTO x_party_site_id;
1416 CLOSE C_Use_Exists;
1417
1418 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1419 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Use_Exists Primary Address ' ||
1420 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1421 END IF;
1422 IF x_party_site_id IS NULL THEN
1423 /* Get Identifying Address for SHIP to Customer */
1424 OPEN C_Get_PrAddr (l_cust_party_id);
1425 FETCH C_Get_PrAddr INTO x_party_site_id;
1426 CLOSE C_Get_PrAddr;
1427
1428 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1429 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Get_PrAddr Identifying Address'||
1430 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1431 END IF;
1432 END IF;
1433
1434 ELSE
1435 /* IF Party type is ORGANIZATION */
1436 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1437 aso_debug_pub.add('Function Get_ShipAddress IF l_cust_party_type is ORGANIZATION :',1,'N');
1438 END IF;
1439 IF nvl(l_party_id,l_cust_party_id) = l_cust_party_id THEN
1440 /* If Organization Contact IS NOT Specified */
1441 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1442 aso_debug_pub.add('Function Get_ShipAddress IF l_cust_party_type is ORGANIZATION '||
1443 ' and Contact is not Specified :',1,'N');
1444 END IF;
1445 /* Get Primary Ship To Address for Ship to Customer */
1446 OPEN C_Use_Exists (l_cust_party_id);
1447 FETCH C_Use_Exists INTO x_party_site_id;
1448 CLOSE C_Use_Exists;
1449
1450 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1451 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Use_Exists Primary Address ' ||
1452 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1453 END IF;
1454
1455 IF x_party_site_id IS NULL THEN
1456 /* Get Identifying Address for Ship to Customer */
1457 OPEN C_Get_PrAddr (l_cust_party_id);
1458 FETCH C_Get_PrAddr INTO x_party_site_id;
1459 CLOSE C_Get_PrAddr;
1460
1461 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1462 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Get_PrAddr Identifying Address'||
1463 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1464 END IF;
1465
1466 END IF;
1467
1468 ELSIF l_party_id <> l_cust_party_id THEN
1469 /* IF Organization Contact is Specified */
1470 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1471 aso_debug_pub.add('Function Get_ShipAddress IF l_cust_party_type is ORGANIZATION '||
1472 ' and Contact is Specified :',1,'N');
1473 END IF;
1474 /* Get Primary Ship To Address for Ship to Customer */
1475 OPEN C_Use_Exists (l_party_id);
1476 FETCH C_Use_Exists INTO x_party_site_id;
1477 CLOSE C_Use_Exists;
1478
1479 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1480 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Use_Exists Primary Address ' ||
1481 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1482 END IF;
1483
1484 IF x_party_site_id IS NULL THEN
1485 /* Get Identifying Address for Ship to Customer */
1486 OPEN C_Get_PrAddr (l_party_id);
1487 FETCH C_Get_PrAddr INTO x_party_site_id;
1488 CLOSE C_Get_PrAddr;
1489
1490 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1491 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Get_PrAddr Identifying Address'||
1492 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1493 END IF;
1494
1495 END IF;
1496
1497 IF x_party_site_id IS NULL THEN
1498 /* Get Primary Ship To Address for Ship to Customer */
1499 OPEN C_Use_Exists (l_cust_party_id);
1500 FETCH C_Use_Exists INTO x_party_site_id;
1501 CLOSE C_Use_Exists;
1502
1503 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1504 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Use_Exists Primary Address ' ||
1505 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1506 END IF;
1507
1508 IF x_party_site_id IS NULL THEN
1509 /* Get Identifying Address for Ship to Customer */
1510 OPEN C_Get_PrAddr (l_cust_party_id);
1511 FETCH C_Get_PrAddr INTO x_party_site_id;
1512 CLOSE C_Get_PrAddr;
1513
1514 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1515 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Get_PrAddr Identifying Address'||
1516 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1517 END IF;
1518
1519 END IF;
1520
1521
1522 END IF;
1523
1524 END IF;
1525
1526 END IF;
1527 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1528 aso_debug_pub.add('Function Get_ShipAddress Party_Site_ID Returned : '||x_party_site_id,1,'N');
1529 END IF;
1530
1531 RETURN x_party_site_id;
1532
1533 EXCEPTION
1534 WHEN NO_DATA_FOUND THEN
1535 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1536 aso_debug_pub.add('Function Get_ShipAddress NO_DATA_FOUND Exception Occurs',1,'N');
1537 END IF;
1538
1539 RETURN NULL;
1540 WHEN OTHERS THEN
1541 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1542 aso_debug_pub.add('Function Get_ShipAddress Inside When Others Exception',1,'N');
1543 END IF;
1544 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1545 THEN
1546 OE_MSG_PUB.Add_Exc_Msg
1547 ( G_PKG_NAME ,
1548 ' Get_ShipAddress '
1549 );
1550 END IF;
1551
1552 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1553
1554 END Get_ShipAddress;
1555
1556
1557
1558 FUNCTION Get_SalesGroup_From_Salesrep(
1559 P_Database_Object_Name IN VARCHAR2,
1560 P_Attribute_Code IN VARCHAR2
1561 ) RETURN NUMBER
1562 IS
1563
1564 CURSOR C_sales_grp (x_resource_id Number) IS
1565 SELECT jrgm.group_id
1566 FROM JTF_RS_GROUP_MEMBERS jrgm,
1567 JTF_RS_GROUPS_tl jrgt,
1568 JTF_RS_GROUP_USAGES jrgu
1569 WHERE jrgm.group_id =jrgt.group_id
1570 AND jrgt.language= userenv('LANG')
1571 AND jrgu.group_id = jrgm.group_id
1572 AND jrgu.usage = 'SALES'
1573 AND nvl(jrgm.delete_flag, 'N') <> 'Y'
1574 AND exists (SELECT 1 FROM
1575 jtf_rs_role_relations jrrr
1576 WHERE jrrr.role_resource_id= jrgm.group_member_id
1577 AND trunc( nvl(jrrr.start_date_active, SYSDATE)) <= trunc( SYSDATE )
1578 AND trunc( nvl(jrrr.end_date_active, SYSDATE)) >= trunc( SYSDATE )
1579 AND jrrr.role_resource_type='RS_GROUP_MEMBER'
1580 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
1581 AND ROWNUM= 1)
1582 AND jrgm.resource_id = x_resource_id;
1583
1584 Cursor C_salesrep (X_User_Id Number) IS
1585 SELECT j.resource_id
1586 FROM jtf_rs_salesreps_mo_v srp, jtf_rs_resource_extns_vl j
1587 WHERE j.user_id = X_User_Id
1588 AND j.resource_id = srp.resource_id
1589 AND nvl(srp.status,'A') = 'A'
1590 AND nvl(trunc(srp.start_date_active), trunc(sysdate)) <= trunc(sysdate)
1591 AND nvl(trunc(srp.end_date_active), trunc(sysdate)) >= trunc(sysdate)
1592 AND nvl(trunc(j.start_date_active), trunc(sysdate)) <= trunc(sysdate)
1593 AND nvl(trunc(j.end_date_active), trunc(sysdate)) >= trunc(sysdate);
1594
1595 l_resource_id NUMBER;
1596 l_salesgroup_id NUMBER;
1597 l_org_id NUMBER;
1598 BEGIN
1599
1600 If NVL(FND_PROFILE.VALUE('ASO_AUTO_TEAM_ASSIGN'),'NONE') <> 'NONE' Then
1601 aso_debug_pub.add('Function Get_SalesGroup_From_Salesrep -- Automatic Team Assign'||fnd_profile.value('ASO_AUTO_TEAM_ASSIGN'),1,'N');
1602 return null;
1603 End If;
1604
1605 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
1606 l_resource_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_resource_id;
1607 l_org_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_Org_id;
1608 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
1609 l_resource_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.O_resource_id;
1610 l_org_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.O_Org_id;
1611 END IF;
1612
1613 IF l_org_id is null or l_org_id = FND_API.G_MISS_NUM THEN
1614 return null;
1615 END IF;
1616
1617 IF l_resource_id IS NULL OR l_resource_id = FND_API.G_MISS_NUM THEN
1618 OPEN C_salesrep(G_USER_ID);
1619 FETCH C_salesrep INTO l_resource_id;
1620 CLOSE C_salesrep;
1621 END IF;
1622
1623 IF l_resource_id IS NOT NULL THEN
1624 OPEN C_sales_grp(l_resource_id);
1625 FETCH C_sales_grp INTO l_salesgroup_id;
1626 CLOSE C_sales_grp;
1627 END IF;
1628
1629 RETURN l_salesgroup_id;
1630
1631 EXCEPTION
1632
1633 WHEN NO_DATA_FOUND THEN
1634 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1635 aso_debug_pub.add('Function Get_SalesGroup_From_Salesrep NO_DATA_FOUND Exception Occurs',1,'N');
1636 END IF;
1637
1638 RETURN NULL;
1639
1640 WHEN OTHERS THEN
1641 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1642 aso_debug_pub.add('Function Get_SalesGroup_From_Salesrep Inside When Others Exception',1,'N');
1643 END IF;
1644
1645 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1646 THEN
1647 OE_MSG_PUB.Add_Exc_Msg
1648 ( G_PKG_NAME ,
1649 ' Get_SalesGroup_From_Salesrep '
1650 );
1651 END IF;
1652
1653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1654
1655 END Get_SalesGroup_From_Salesrep;
1656
1657 FUNCTION Get_SalesGroup_From_Profile (
1658 P_Database_Object_Name IN VARCHAR2,
1659 P_Attribute_Code IN VARCHAR2
1660 ) RETURN NUMBER
1661 IS
1662 l_org_id NUMBER;
1663 l_salesgroup_id NUMBER;
1664
1665 BEGIN
1666
1667 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1668 aso_debug_pub.add('Function Get_SalesGroup_From_Profile -- Begin :',1,'N');
1669 END IF;
1670
1671 If NVL(FND_PROFILE.VALUE('ASO_AUTO_TEAM_ASSIGN'),'NONE') <> 'NONE' Then
1672 aso_debug_pub.add('Function Get_SalesGroup_From_Profile -- Automatic Team Assign'||fnd_profile.value('ASO_AUTO_TEAM_ASSIGN'),1,'N');
1673 return null;
1674 End If;
1675
1676 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
1677 l_org_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_Org_id;
1678 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
1679 l_org_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.O_Org_id;
1680 END IF;
1681
1682 l_salesgroup_id := aso_utility_pvt.get_ou_attribute_value(aso_utility_pvt.G_DEFAULT_SALES_GROUP,l_org_id);
1683
1684 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1685 aso_debug_pub.add('Function Get_SalesGroup_From_Profile Sales Group Id :' ||l_salesgroup_id,1,'N');
1686 END IF;
1687
1688 RETURN l_salesgroup_id;
1689
1690 EXCEPTION
1691
1692 WHEN OTHERS THEN
1693 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1694 aso_debug_pub.add('Function Get_SalesGroup_From_Profile Inside When Others Exception',1,'N');
1695 END IF;
1696 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1697 THEN
1698 OE_MSG_PUB.Add_Exc_Msg
1699 ( G_PKG_NAME ,
1700 ' Get_SalesGroup_From_Profile '
1701 );
1702 END IF;
1703
1704 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1705
1706 END Get_SalesGroup_From_Profile;
1707
1708
1709 FUNCTION Get_SalesRep (
1710 P_Database_Object_Name IN VARCHAR2,
1711 P_Attribute_Code IN VARCHAR2
1712 ) RETURN NUMBER
1713 IS
1714
1715 CURSOR C_Get_Creator_Res (l_user_id NUMBER) IS
1716 SELECT resource_id
1717 FROM JTF_RS_RESOURCE_EXTNS
1718 WHERE user_id = l_user_id
1719 AND SYSDATE BETWEEN NVL(start_date_active,sysdate) AND NVL(end_date_active, SYSDATE);
1720
1721 CURSOR C_Valid_SalesRep (l_res_id NUMBER) IS
1722 SELECT 'Y'
1723 FROM JTF_RS_SALESREPS_MO_V
1724 WHERE resource_id = l_res_id
1725 AND NVL(status,'A') = 'A'
1726 AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
1727 AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate);
1728
1729 CURSOR C_Get_Res_From_Srep (l_Srep VARCHAR2) IS
1730 SELECT Resource_Id
1731 FROM JTF_RS_SALESREPS_MO_V
1732 WHERE salesrep_number = l_Srep
1733 AND NVL(status,'A') = 'A'
1734 AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
1735 AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate);
1736
1737 l_creator_res NUMBER;
1738 l_valid VARCHAR2(1) := 'N';
1739 l_default_salesrep VARCHAR2(40) := aso_utility_pvt.get_default_salesrep;
1740 l_profile_salesrep_id NUMBER;
1741 l_org_id NUMBER;
1742
1743 BEGIN
1744 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1745 aso_debug_pub.add('Function Get_SalesRep --- Begin :',1,'N');
1746 END IF;
1747
1748 If NVL(FND_PROFILE.VALUE('ASO_AUTO_TEAM_ASSIGN'),'NONE') <> 'NONE' Then
1749 aso_debug_pub.add('Function Get_SalesRep -- Automatic Team Assign'||fnd_profile.value('ASO_AUTO_TEAM_ASSIGN'),1,'N');
1750 return null;
1751 End If;
1752
1753 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
1754 l_org_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_ORG_ID;
1755 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
1756 l_org_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.O_ORG_ID;
1757 END IF;
1758
1759 IF l_org_id is null or l_org_id = FND_API.G_MISS_NUM THEN
1760 return null;
1761 END IF;
1762
1763 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1764 aso_debug_pub.add('Function Get_SalesRep Database Object :'||p_database_object_name,1,'N');
1765 aso_debug_pub.add('Function Get_SalesRep ORG ID : '|| l_org_id,1,'N');
1766 END IF;
1767
1768 OPEN C_Get_Creator_Res(G_USER_ID);
1769 FETCH C_Get_Creator_Res INTO l_creator_res;
1770 CLOSE C_Get_Creator_Res;
1771
1772 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1773 aso_debug_pub.add('Function Get_SalesRep Cursor C_Get_Creator_Res: '||l_creator_res,1,'N');
1774 END IF;
1775
1776 IF l_creator_res IS NOT NULL THEN
1777 OPEN C_Valid_SalesRep (l_creator_res);
1778 FETCH C_Valid_SalesRep INTO l_valid;
1779 CLOSE C_Valid_SalesRep;
1780
1781 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1782 aso_debug_pub.add('Function Get_SalesRep Cursor C_Valid_SalesRep: '||l_valid,1,'N');
1783 END IF;
1784 END IF;
1785
1786 IF (l_creator_res IS NULL OR l_valid <> 'Y') THEN --AND l_profile_salesrep_id IS NOT NULL THEN
1787
1788 -- Bug 4724024
1789 IF (l_valid <> 'Y') THEN
1790 l_creator_res := NULL ;
1791 END IF;
1792
1793 -- Passing Org id in the call - Girish 10/18/2005
1794 l_profile_salesrep_id := aso_utility_pvt.get_ou_attribute_value(l_default_salesrep, l_org_id);
1795
1796 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1797 aso_debug_pub.add('Function Get_SalesRep l_profile_salesrep_id: '||l_profile_salesrep_id,1,'N');
1798 END IF;
1799
1800 IF l_profile_salesrep_id IS NOT NULL THEN
1801 OPEN C_Get_Res_From_Srep (l_profile_salesrep_id);
1802 FETCH C_Get_Res_From_Srep INTO l_creator_res;
1803 CLOSE C_Get_Res_From_Srep;
1804 END IF;
1805 END IF;
1806
1807 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1808 aso_debug_pub.add('Function Get_SalesRep Returns: '||l_creator_res,1,'N');
1809 END IF;
1810
1811 RETURN l_creator_res;
1812
1813 EXCEPTION
1814 WHEN NO_DATA_FOUND THEN
1815 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1816 aso_debug_pub.add('Function Get_SalesRep NO_DATA_FOUND Exception Occurs',1,'N');
1817 END IF;
1818
1819 RETURN NULL;
1820
1821 WHEN OTHERS THEN
1822 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1823 aso_debug_pub.add('Function Get_SalesRep Inside When Others Exception',1,'N');
1824 END IF;
1825 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1826 THEN
1827 OE_MSG_PUB.Add_Exc_Msg
1828 ( G_PKG_NAME ,
1829 ' Get_SalesRep '
1830 );
1831 END IF;
1832
1833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1834
1835 END Get_SalesRep;
1836
1837 FUNCTION Get_Currency_from_pricelist(
1838 P_Database_Object_Name IN VARCHAR2,
1839 P_Attribute_Code IN VARCHAR2
1840 ) RETURN VARCHAR2
1841 is
1842 Cursor C_currency_code(q_price_list_id Number) is
1843 SELECT currency_code
1844 FROM qp_price_lists_v
1845 WHERE price_list_id = q_price_list_id;
1846
1847 l_price_list_id Number;
1848 l_currency_code C_currency_code%rowtype;
1849
1850 Begin
1851 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1852 aso_debug_pub.add('Function Get_Currency_from_pricelist --- Begin : ',1,'N');
1853 END IF;
1854
1855 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
1856 l_price_list_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.q_price_list_id;
1857
1858 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
1859 l_price_list_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_price_list_id;
1860
1861
1862 END IF;
1863
1864 open C_currency_code(l_price_list_id);
1865 fetch C_currency_code into l_currency_code;
1866 close C_Currency_code;
1867
1868 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1869 aso_debug_pub.add('Function Get_Currency_from_pricelist Returns : '||l_currency_code.currency_code,1,'N');
1870 END IF;
1871
1872 RETURN l_currency_code.currency_code;
1873
1874
1875 EXCEPTION
1876 WHEN NO_DATA_FOUND THEN
1877 RETURN NULL;
1878
1879 WHEN OTHERS THEN
1880 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1881 aso_debug_pub.add('Function Get_Currency_from_pricelist Inside When Others Exception',1,'N');
1882 END IF;
1883 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1884 THEN
1885 OE_MSG_PUB.Add_Exc_Msg
1886 ( G_PKG_NAME ,
1887 ' Get_Currency_from_pricelist '
1888 );
1889 END IF;
1890
1891 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1892
1893 End Get_Currency_from_pricelist;
1894
1895 FUNCTION Get_Currency_from_Profile(
1896 P_Database_Object_Name IN VARCHAR2,
1897 P_Attribute_Code IN VARCHAR2
1898 ) RETURN VARCHAR2
1899 Is
1900 Begin
1901 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1902 aso_debug_pub.add('Function Get_Currency_from_Profile --- Begin :',1,'N');
1903 END IF;
1904
1905 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' OR
1906 p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
1907 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1908 aso_debug_pub.add('Function Get_Currency_From_Profile Profile ICX_PREFERRED_CURRENCY : '||
1909 fnd_profile.Value('ICX_PREFERRED_CURRENCY') ,1,'N');
1910 END IF;
1911
1912 return FND_PROFILE.Value('ICX_PREFERRED_CURRENCY');
1913 END IF;
1914 EXCEPTION
1915 WHEN NO_DATA_FOUND THEN
1916 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1917 aso_debug_pub.add('Function Get_Currency_from_Profile NO_DATA_FOUND Exception Occurs : ',1,'N');
1918 END IF;
1919
1920 RETURN NULL;
1921
1922 WHEN OTHERS THEN
1923 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1924 aso_debug_pub.add('Function Get_Currency_From_Profile Inside When Others Exception',1,'N');
1925 END IF;
1926 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1927 THEN
1928 OE_MSG_PUB.Add_Exc_Msg
1929 ( G_PKG_NAME ,
1930 ' Get_Currency_from_Profile '
1931 );
1932 END IF;
1933
1934 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1935
1936 END Get_Currency_from_Profile;
1937
1938
1939 FUNCTION Get_RequestedDateType(P_Database_Object_Name IN VARCHAR2,
1940 P_Attribute_Code IN VARCHAR2) RETURN VARCHAR2
1941
1942 IS
1943
1944 l_ret_value HZ_CUST_ACCOUNTS.DATE_TYPE_PREFERENCE%TYPE;
1945 l_acct_id NUMBER;
1946
1947 CURSOR C_Get_RDT (l_acct NUMBER) IS
1948 SELECT date_type_preference
1949 FROM HZ_CUST_ACCOUNTS
1950 WHERE Cust_Account_Id = l_acct
1951 AND Status = 'A';
1952
1953 BEGIN
1954 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1955 aso_debug_pub.add('Function Get_RequestedDateType --- Begin : ',1,'N');
1956 END IF;
1957
1958 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
1959 l_acct_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_cust_account_id;
1960
1961 IF ( l_acct_id IS NOT NULL AND
1962 l_acct_id <> FND_API.G_MISS_NUM ) THEN
1963 OPEN C_Get_RDT (l_acct_id);
1964 FETCH C_Get_RDT INTO l_ret_value;
1965 CLOSE C_Get_RDT;
1966
1967 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1968 aso_debug_pub.add('Function Get_RequestedDateType cusror C_Get_RDT l_ret_value : ' ||
1969 l_ret_value ,1,'N');
1970 END IF;
1971 ELSE
1972 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1973 aso_debug_pub.add('Function Get_RequestedDateType -CustomerAccount is null - requesteddatetype no retrieved',1,'N');
1974 END IF;
1975 END IF;
1976
1977 END IF;
1978
1979 RETURN l_ret_value;
1980
1981 EXCEPTION
1982 WHEN NO_DATA_FOUND THEN
1983 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1984 aso_debug_pub.add('Function Get_RequestedDateType NO_DATA_FOUND Occurs ',1,'N');
1985 END IF;
1986
1987 RETURN NULL;
1988
1989 WHEN OTHERS THEN
1990
1991 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1992 aso_debug_pub.add('Function Get_RequestedDateType Inside When Others Exception',1,'N');
1993 END IF;
1994
1995 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1996 THEN
1997 OE_MSG_PUB.Add_Exc_Msg
1998 ( G_PKG_NAME ,
1999 ' Get_RequestedDateType '
2000 );
2001 END IF;
2002
2003 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2004
2005 END Get_RequestedDateType;
2006
2007
2008 FUNCTION Get_ChargePeriodicity(P_Database_Object_Name IN VARCHAR2,
2009 P_Attribute_Code IN VARCHAR2) RETURN VARCHAR2
2010
2011 IS
2012
2013 l_ret_value mtl_system_items_vl.charge_periodicity_code%TYPE;
2014 l_organization_id NUMBER;
2015 l_inventory_item_id NUMBER ;
2016
2017 CURSOR C_Get_CP (l_organization NUMBER, l_inventory_item NUMBER) IS
2018 SELECT charge_periodicity_code
2019 FROM mtl_system_items_b
2020 WHERE organization_id = l_organization
2021 AND inventory_item_id = l_inventory_item;
2022
2023 BEGIN
2024 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2025 aso_debug_pub.add('Function Get_ChargePeriodicity --- Begin : ',1,'N');
2026 END IF;
2027
2028 IF p_database_object_name='ASO_AK_QUOTE_LINE_V' THEN
2029 l_organization_id := ASO_QUOTE_LINE_DEF_HDLR.g_record.L_ORGANIZATION_ID;
2030 l_inventory_item_id := ASO_QUOTE_LINE_DEF_HDLR.g_record.L_INVENTORY_ITEM_ID;
2031
2032 IF ( l_organization_id IS NOT NULL AND
2033 l_organization_id <> FND_API.G_MISS_NUM )
2034 AND ( l_inventory_item_id IS NOT NULL AND
2035 l_inventory_item_id <> FND_API.G_MISS_NUM )THEN
2036 OPEN C_Get_CP (l_organization_id, l_inventory_item_id);
2037 FETCH C_Get_CP INTO l_ret_value;
2038 CLOSE C_Get_CP;
2039
2040 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2041 aso_debug_pub.add('Function Get_ChargePeriodicity cusror C_Get_CP l_ret_value : ' ||
2042 l_ret_value ,1,'N');
2043 END IF;
2044 ELSE
2045 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2046 aso_debug_pub.add('Function Get_ChargePeriodicity -Org Id is null - Charge Periodicity not retrieved',1,'N');
2047 END IF;
2048 END IF;
2049
2050 END IF;
2051
2052 RETURN l_ret_value;
2053
2054 EXCEPTION
2055 WHEN NO_DATA_FOUND THEN
2056 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2057 aso_debug_pub.add('Function Get_ChargePeriodicity NO_DATA_FOUND Occurs ',1,'N');
2058 END IF;
2059
2060 RETURN NULL;
2061
2062 WHEN OTHERS THEN
2063
2064 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2065 aso_debug_pub.add('Function Get_ChargePeriodicity Inside When Others Exception',1,'N');
2066 END IF;
2067
2068 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2069 THEN
2070 OE_MSG_PUB.Add_Exc_Msg
2071 ( G_PKG_NAME ,
2072 ' Get_ChargePeriodicity '
2073 );
2074 END IF;
2075
2076 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2077
2078 END Get_ChargePeriodicity;
2079
2080 END QOT_DEFAULT_PVT;
[Home] [Help]