1 PACKAGE BODY QOT_DEFAULT_PVT AS
2 /* $Header: qotvdefb.pls 120.30 2011/12/02 06:28:08 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 /* Bug 13111138: Added additional condition ( and site_use_id IS NULL )
605 For selecting Payment Terms associated with Customer Account only
606 i.e. restrict Payment terms associated with Customer Sites (Addresses)
607 */
608
609 Cursor default_payment_term_customer(l_cust_account_id NUMBER) Is
610 select hcp.standard_terms
611 from hz_cust_accounts hca,
612 hz_customer_profiles hcp
613 where hcp.cust_account_id = hca.cust_account_id
614 and hca.cust_account_id = l_cust_account_id
615 and nvl(hcp.status,'A') = 'A'
616 and site_use_id IS NULL;
617
618 l_cust_acct_id number;
619 l_term_id Number;
620
621 Begin
622 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
623 aso_debug_pub.add('Function Get_PaymentTerm_From_Customer --- Begin : ' ,1,'N');
624 END IF;
625
626 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
627 l_cust_acct_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_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_HEADER_V '||
631 ' l_cust_acct_id : ' || l_cust_acct_id ,1,'N');
632 END IF;
633 ELSIF p_database_object_name='ASO_AK_QUOTE_OPPTY_V' THEN
634 l_cust_acct_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_cust_account_id;
635
636 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
637 aso_debug_pub.add('Function Get_PaymentTerm_From_Customer Database Object ASO_AK_QUOTE_OPPTY_V '||
638 ' l_cust_acct_id : ' || l_cust_acct_id ,1,'N');
639 END IF;
640 END IF;
641
642 Open default_payment_term_customer(l_cust_acct_id);
643 Fetch default_payment_term_customer into l_term_id;
644 CLOSE default_payment_term_customer;
645
646 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
647 aso_debug_pub.add('Function Get_PaymentTerm_From_Customer Cursor default_payment_term_customer '||
648 ' l_term_id : ' || l_term_id ,1,'N');
649 END IF;
650
651 RETURN l_term_id;
652
653 EXCEPTION
654 WHEN NO_DATA_FOUND THEN
655 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
656 aso_debug_pub.add('Function Get_PaymentTerm_From_Customer NO_DATA_FOUND Exception Occurs: ',1,'N');
657 END IF;
658 RETURN NULL;
659
660 WHEN OTHERS THEN
661 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
662 aso_debug_pub.add('Function Get_PaymentTerm_From_Customer Inside When Others Exception',1,'N');
663 END IF;
664
665 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
666 THEN
667 OE_MSG_PUB.Add_Exc_Msg
668 ( G_PKG_NAME ,
669 ' Get_PaymentTerm_From_Customer '
670 );
671 END IF;
672
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
674
675 END Get_PaymentTerm_From_Customer;
676
677 FUNCTION Get_ExpirationDate (
678 P_Database_Object_Name IN VARCHAR2,
679 P_Attribute_Code IN VARCHAR2
680 ) RETURN DATE
681 IS
682
683 CURSOR C_Get_Expiration_Date (l_Def_Cal VARCHAR2, l_Def_Per VARCHAR2) IS
684 SELECT End_Date
685 FROM GL_PERIODS_V
686 WHERE Period_Type = l_Def_Per
687 AND Period_Set_Name = l_Def_Cal
688 AND SYSDATE BETWEEN NVL(Start_Date,sysdate) AND NVL(End_Date,sysdate);
689
690 l_Default_Cal_Prof VARCHAR2(15):= FND_PROFILE.VALUE ('ASO_DEFAULT_EXP_GL_CAL' );
691 l_Default_Per_Prof VARCHAR2(15):= FND_PROFILE.VALUE ('ASO_DEFAULT_EXP_GL_PERIOD' );
692 l_qte_duration_prof NUMBER := NVL(FND_PROFILE.VALUE ('ASO_QUOTE_DURATION'),30);
693
694 l_Quote_Exp_Date DATE;
695
696 BEGIN
697 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
698 aso_debug_pub.add('Function Get_ExpiraionDate -- Begin: ',1,'N');
699 END IF;
700
701 IF l_Default_Cal_Prof IS NOT NULL
702 AND l_Default_Per_Prof IS NOT NULL THEN
703
704 OPEN C_Get_Expiration_Date(l_Default_Cal_Prof , l_Default_Per_Prof );
705 FETCH C_Get_Expiration_Date INTO l_Quote_Exp_Date;
706 CLOSE C_Get_Expiration_Date;
707
708 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
709 aso_debug_pub.add('Function Get_ExpirationDate Cursor C_Get_Expiration_Date l_Quote_Exp_Date : '||
710 l_Quote_Exp_Date ,1,'N');
711 END IF;
712
713 IF l_Quote_Exp_Date IS NULL THEN
714 l_Quote_Exp_Date := SYSDATE + l_Qte_Duration_Prof;
715 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
716 aso_debug_pub.add('Function Get_ExpirationDate Cursor C_Get_Expiration_Date returns ' ||
717 ' l_Quote_Exp_Date AS Null Then l_Quote_Exp_Date :' || l_Quote_Exp_Date ,1,'N');
718 END IF;
719
720 RETURN l_Quote_Exp_Date;
721
722 END IF;
723
724 RETURN l_Quote_Exp_Date;
725
726
727
728 ELSE
729
730 l_Quote_Exp_Date := SYSDATE + l_Qte_Duration_Prof;
731
732 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
733 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');
734 END IF;
735
736 RETURN l_Quote_Exp_Date;
737
738 END IF;
739
740 EXCEPTION
741 WHEN NO_DATA_FOUND THEN
742 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
743 aso_debug_pub.add('Function Get_ExpirationDate NO_DATA_FOUND Exception Occurs: ',1,'N');
744 END IF;
745
746 RETURN NULL;
747 WHEN OTHERS THEN
748 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
749 aso_debug_pub.add('Function Get_ExpirationDate Inside When Others Exception',1,'N');
750 END IF;
751 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
752 THEN
753 OE_MSG_PUB.Add_Exc_Msg
754 ( G_PKG_NAME ,
755 ' Get_ExpirationDate '
756 );
757 END IF;
758
759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
760
761 END Get_ExpirationDate;
762
763
764
765
766
767 FUNCTION Get_QuotePhone (
768 P_Database_Object_Name IN VARCHAR2,
769 P_Attribute_Code IN VARCHAR2
770 ) RETURN NUMBER
771 IS
772
773 CURSOR C_Get_Type (l_party NUMBER) IS
774 SELECT Party_Type
775 FROM HZ_PARTIES
776 WHERE Party_Id = l_party
777 AND Status = 'A';
778
779 CURSOR C_Get_Phone (l_party NUMBER) IS
780 SELECT Contact_Point_Id
781 FROM HZ_CONTACT_POINTS
782 WHERE Owner_Table_Id = l_party
783 AND Owner_Table_Name = 'HZ_PARTIES'
784 AND Contact_Point_Type = 'PHONE'
785 AND Status = 'A'
786 AND Primary_Flag = 'Y';
787
788 l_party_id NUMBER;
789 l_cust_party_id NUMBER;
790 l_ret_value NUMBER;
791 l_cust_party_type VARCHAR2(15);
792
793 BEGIN
794 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
795 aso_debug_pub.add('Function Get_QuotePhone --- Begin ',1,'N');
796 END IF;
797
798 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
799 l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.q_cust_party_id;
800 l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.q_party_id;
801
802 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
803 aso_debug_pub.add('Function Get_QuotePhone Database Object ASO_AK_QUOTE_HEADER_V '||
804 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
805 aso_debug_pub.add('Function Get_QuotePhone Database Object ASO_AK_QUOTE_HEADER_V '||
806 ' l_party_id :' || l_party_id ,1,'N');
807 END IF;
808
809 ELSIF p_database_object_name='ASO_AK_QUOTE_OPPTY_V' THEN
810 --l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_cust_party_id; --Bug#5195151
811 l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_cust_party_id;
812 l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_party_id;
813
814 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
815 aso_debug_pub.add('Function Get_QuotePhone Database Object ASO_AK_QUOTE_OPPTY_V '||
816 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
817 aso_debug_pub.add('Function Get_QuotePhone Database Object ASO_AK_QUOTE_OPPTY_V '||
818 ' l_party_id :' || l_party_id ,1,'N');
819 END IF;
820
821 END IF;
822
823 OPEN C_Get_Type (l_cust_party_id);
824 FETCH C_Get_Type INTO l_cust_party_type;
825 CLOSE C_Get_Type;
826
827 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
828 aso_debug_pub.add('Function Get_QuotePhone Customer Party Type : '||l_cust_party_type,1,'N');
829 END IF;
830
831 IF l_cust_party_type = 'PERSON' THEN
832
833 OPEN C_Get_Phone (l_cust_party_id);
834 FETCH C_Get_Phone INTO l_ret_value;
835 CLOSE C_Get_Phone;
836
837 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
838 aso_debug_pub.add('Function Get_QuotePhone Customer Party Type is PERSON l_ret_value: '||
839 l_ret_value,1,'N');
840 END IF;
841 ELSE
842
843 IF l_party_id IS NOT NULL AND l_party_id <> FND_API.G_MISS_NUM THEN
844 OPEN C_Get_Phone (l_party_id);
845 FETCH C_Get_Phone INTO l_ret_value;
846 CLOSE C_Get_Phone;
847 END IF;
848
849 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
850 aso_debug_pub.add('Function Get_QuotePhone Customer Party Type is Organization l_ret_value:'||
851 l_ret_value,1,'N');
852 END IF;
853
854 END IF;
855
856 RETURN l_ret_value;
857
858 EXCEPTION
859 WHEN NO_DATA_FOUND THEN
860 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
861 aso_debug_pub.add('Function Get_QuotePhone NO_DATA_FOUND Exception Occurs : ',1,'N');
862 END IF;
863
864 RETURN NULL;
865 WHEN OTHERS THEN
866 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
867 aso_debug_pub.add('Function Get_QuotePhone Inside When Others Exception',1,'N');
868 END IF;
869
870 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
871 THEN
872 OE_MSG_PUB.Add_Exc_Msg
873 ( G_PKG_NAME ,
874 ' Get_QuotePhone '
875 );
876 END IF;
877
878 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
879
880 END Get_QuotePhone;
881
882 FUNCTION Get_QuoteAddress(
883 P_Database_Object_Name IN VARCHAR2,
884 P_Attribute_Code IN VARCHAR2
885 ) RETURN NUMBER
886 IS
887
888 CURSOR C_Get_Type (l_party NUMBER) IS
889 SELECT Party_Type
890 FROM HZ_PARTIES
891 WHERE Party_Id = l_party
892 AND Status = 'A';
893
894 CURSOR C_Use_Exists (l_party NUMBER) IS
895 SELECT A.Party_Site_Id
896 FROM HZ_PARTY_SITES A, HZ_PARTY_SITE_USES B
897 WHERE A.Party_Id = l_party
898 AND A.Party_Site_Id = B.Party_Site_Id
899 AND B.Site_Use_Type = 'SOLD_TO'
900 AND B.Primary_Per_Type = 'Y'
901 AND A.Status = 'A'
902 AND B.Status = 'A';
903
904 CURSOR C_Get_PrAddr (l_party NUMBER) IS
905 SELECT Party_Site_Id
906 FROM HZ_PARTY_SITES
907 WHERE Party_Id = l_party
908 AND Identifying_Address_Flag = 'Y'
909 AND Status = 'A';
910
911 l_cust_party_type VARCHAR2(15);
912 l_cust_party_id NUMBER;
913 l_party_id NUMBER;
914 x_party_site_id NUMBER;
915
916 BEGIN
917 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
918 aso_debug_pub.add('Function Get_QuoteAddress --- Begin :',1,'N');
919 END IF;
920
921 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
922 l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_cust_party_id;
923 l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_party_id;
924
925 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
926 aso_debug_pub.add('Function Get_QuoteAddress Database Object ASO_AK_QUOTE_HEADER_V '||
927 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
928 aso_debug_pub.add('Function Get_QuoteAddress Database Object ASO_AK_QUOTE_HEADER_V '||
929 ' l_party_id :' || l_party_id ,1,'N');
930 END IF;
931
932 ELSIF p_database_object_name='ASO_AK_QUOTE_OPPTY_V' THEN
933 --l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_cust_party_id; --Bug#5195151
934 l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_cust_party_id;
935 l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_party_id;
936
937 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
938 aso_debug_pub.add('Function Get_QuoteAddress Database Object ASO_AK_QUOTE_OPPTY_V '||
939 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
940 aso_debug_pub.add('Function Get_QuoteAddress Database Object ASO_AK_QUOTE_OPPTY_V '||
941 ' l_party_id :' || l_party_id ,1,'N');
942 END IF;
943 END IF;
944
945 OPEN C_Get_Type (l_cust_party_id);
946 FETCH C_Get_Type INTO l_cust_party_type;
947 CLOSE C_Get_Type;
948
949 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
950 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Get_Type l_cust_party_type :'||
951 l_cust_party_type,1,'N');
952 END IF;
953
954 /* if Party is PERSON */
955 IF l_cust_party_type = 'PERSON' THEN
956 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
957 aso_debug_pub.add('Function Get_QuoteAddress IF l_cust_party_type is PERSON :',1,'N');
958 END IF;
959 /* Get Primary Sold To Address for Quote to Customer */
960 OPEN C_Use_Exists (l_cust_party_id);
961 FETCH C_Use_Exists INTO x_party_site_id;
962 CLOSE C_Use_Exists;
963
964 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
965 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Use_Exists Primary Address ' ||
966 ' x_party_site_id :' ||x_party_site_id ,1,'N');
967 END IF;
968 IF x_party_site_id IS NULL THEN
969 /* Get Identifying Address for Quote to Customer */
970 OPEN C_Get_PrAddr (l_cust_party_id);
971 FETCH C_Get_PrAddr INTO x_party_site_id;
972 CLOSE C_Get_PrAddr;
973 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
974 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Get_PrAddr Identifying Address'||
975 ' x_party_site_id :' ||x_party_site_id ,1,'N');
976 END IF;
977 END IF;
978
979 ELSE
980 /* If Party Type is Organization */
981 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
982 aso_debug_pub.add('Function Get_QuoteAddress IF l_cust_party_type is ORGANIZATION :',1,'N');
983 END IF;
984 IF nvl(l_party_id,l_cust_party_id) = l_cust_party_id THEN
985 /* if party type is Organization and contact is not specified */
986
987 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
988 aso_debug_pub.add('Function Get_QuoteAddress IF l_cust_party_type is ORGANIZATION '||
989 ' and Contact is not Specified :',1,'N');
990 END IF;
991
992 /* Get Primary Sold to Address for Quote to Customer */
993
994 OPEN C_Use_Exists (l_cust_party_id);
995 FETCH C_Use_Exists INTO x_party_site_id;
996 CLOSE C_Use_Exists;
997
998 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
999 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Use_Exists Primary Address ' ||
1000 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1001 END IF;
1002
1003 IF x_party_site_id IS NULL THEN
1004 /* Get Identifying Address for Quote to Customer */
1005
1006 OPEN C_Get_PrAddr (l_cust_party_id);
1007 FETCH C_Get_PrAddr INTO x_party_site_id;
1008 CLOSE C_Get_PrAddr;
1009
1010 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1011 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Get_PrAddr Identifying Address '||
1012 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1013 END IF;
1014 END IF; ----- TEST
1015 ELSIF l_party_id <> l_cust_party_id THEN
1016 /* if party type is Organization and contact is specified */
1017 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1018 aso_debug_pub.add('Function Get_QuoteAddress IF l_cust_party_type is ORGANIZATION '||
1019 ' and Contact is Specified :',1,'N');
1020 END IF;
1021 /* Get Primary 'SOLD TO' address for Quote to Contact */
1022 OPEN C_Use_Exists (l_party_id);
1023 FETCH C_Use_Exists INTO x_party_site_id;
1024 CLOSE C_Use_Exists;
1025
1026 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1027 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Use_Exists Primary Address ' ||
1028 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1029 END IF;
1030
1031 IF x_party_site_id IS NULL THEN
1032 /* Get Identifying Address for Quote to Contact */
1033
1034 OPEN C_Get_PrAddr (l_party_id);
1035 FETCH C_Get_PrAddr INTO x_party_site_id;
1036 CLOSE C_Get_PrAddr;
1037
1038 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1039 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Get_PrAddr Identifying Address '||
1040 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1041 END IF;
1042
1043 END IF;
1044
1045 IF x_party_site_id IS NULL THEN
1046 /* Get Primary 'SOLD TO' address for Quote to Customer */
1047
1048 OPEN C_Use_Exists (l_cust_party_id);
1049 FETCH C_Use_Exists INTO x_party_site_id;
1050 CLOSE C_Use_Exists;
1051
1052 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1053 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Use_Exists Primary Address ' ||
1054 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1055 END IF;
1056
1057 IF x_party_site_id IS NULL THEN
1058 /* Get Identifying address for Quote to Customer */
1059
1060 OPEN C_Get_PrAddr (l_cust_party_id);
1061 FETCH C_Get_PrAddr INTO x_party_site_id;
1062 CLOSE C_Get_PrAddr;
1063
1064 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1065 aso_debug_pub.add('Function Get_QuoteAddress Cursor C_Get_PrAddr Identifying '||
1066 ' Address x_party_site_id :' ||x_party_site_id ,1,'N');
1067 END IF;
1068 END IF; -- if x_party_site_id IS NULL
1069
1070 END IF; -- if x_party_site_id IS NULL
1071
1072 END IF;
1073
1074 END IF;
1075 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1076 aso_debug_pub.add('Function Get_QuoteAddress Party_Site_ID :' || x_party_site_id ,1,'N');
1077 END IF;
1078
1079 RETURN x_party_site_id;
1080
1081 EXCEPTION
1082 WHEN NO_DATA_FOUND THEN
1083 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1084 aso_debug_pub.add('Function Get_QuoteAddress NO_DATA_FOUND Exception Occurs :',1,'N');
1085 END IF;
1086
1087 RETURN NULL;
1088 WHEN OTHERS THEN
1089 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1090 aso_debug_pub.add('Function Get_QuoteAddress Inside When Others Exception',1,'N');
1091 END IF;
1092
1093 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1094 OE_MSG_PUB.Add_Exc_Msg
1095 ( G_PKG_NAME ,
1096 ' Get_QuoteAddress '
1097 );
1098 END IF;
1099
1100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1101
1102 END Get_QuoteAddress;
1103
1104 FUNCTION Get_BillAddress(
1105 P_Database_Object_Name IN VARCHAR2,
1106 P_Attribute_Code IN VARCHAR2
1107 ) RETURN NUMBER
1108 IS
1109
1110 CURSOR C_Get_Type (l_party NUMBER) IS
1111 SELECT Party_Type
1112 FROM HZ_PARTIES
1113 WHERE Party_Id = l_party
1114 AND Status = 'A';
1115
1116 CURSOR C_Use_Exists (l_party NUMBER) IS
1117 SELECT A.Party_Site_Id
1118 FROM HZ_PARTY_SITES A, HZ_PARTY_SITE_USES B
1119 WHERE A.Party_Id = l_party
1120 AND A.Party_Site_Id = B.Party_Site_Id
1121 AND B.Site_Use_Type = 'BILL_TO'
1122 AND B.Primary_Per_Type = 'Y'
1123 AND A.Status = 'A'
1124 AND B.Status = 'A';
1125
1126 CURSOR C_Get_PrAddr (l_party NUMBER) IS
1127 SELECT Party_Site_Id
1128 FROM HZ_PARTY_SITES
1129 WHERE Party_Id = l_party
1130 AND Identifying_Address_Flag = 'Y'
1131 AND Status = 'A';
1132
1133 l_cust_party_type VARCHAR2(15);
1134 l_cust_party_id NUMBER;
1135 l_party_id NUMBER;
1136 x_party_site_id NUMBER;
1137
1138 BEGIN
1139 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1140 aso_debug_pub.add('Function Get_BillAddress --- Begin : ',1,'N');
1141 END IF;
1142
1143 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
1144 --l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_cust_party_id;
1145 l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_INV_TO_CUST_PTY_ID ;
1146 --l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_party_id;
1147 l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_INV_TO_PTY_ID ;
1148
1149 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1150 aso_debug_pub.add('Function Get_BillAddress Database Object ASO_AK_QUOTE_HEADER_V '||
1151 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
1152 aso_debug_pub.add('Function Get_BillAddress Database Object ASO_AK_QUOTE_HEADER_V '||
1153 ' l_party_id :' || l_party_id ,1,'N');
1154 END IF;
1155
1156 ELSIF p_database_object_name='ASO_AK_QUOTE_OPPTY_V' THEN
1157 --l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_cust_party_id;
1158 --l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_party_id;
1159 l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_cust_party_id;
1160
1161 /* Bug 9594091 -- Commented not to copy the Sold to customer contact to
1162 * Bill to Contact in Oppty to Quote defaulting bill to address flow */
1163
1164 --l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_sld_to_cont_id;
1165
1166 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1167 aso_debug_pub.add('Function Get_BillAddress Database Object ASO_AK_QUOTE_OPPTY_V '||
1168 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
1169 aso_debug_pub.add('Function Get_BillAddress Database Object ASO_AK_QUOTE_OPPTY_V '||
1170 ' l_party_id :' || l_party_id ,1,'N');
1171 END IF;
1172
1173 END IF;
1174
1175 OPEN C_Get_Type (l_cust_party_id);
1176 FETCH C_Get_Type INTO l_cust_party_type;
1177 CLOSE C_Get_Type;
1178 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1179 aso_debug_pub.add('Function Get_BillAddress Cursor C_Get_Type l_cust_party_type :'||
1180 l_cust_party_type,1,'N');
1181 END IF;
1182 /* if Party Type is PERSON */
1183 IF l_cust_party_type = 'PERSON' THEN
1184 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1185 aso_debug_pub.add('Function Get_BillAddress IF l_cust_party_type is PERSON :',1,'N');
1186 END IF;
1187 /* Get Primary Bill To Address for Bill to Customer */
1188 OPEN C_Use_Exists (l_cust_party_id);
1189 FETCH C_Use_Exists INTO x_party_site_id;
1190 CLOSE C_Use_Exists;
1191
1192 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1193 aso_debug_pub.add('Function Get_BillAddress Cursor C_Use_Exists Primary Address ' ||
1194 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1195 END IF;
1196
1197 IF x_party_site_id IS NULL THEN
1198 /* Get Identifying Address for Bill to Customer */
1199 OPEN C_Get_PrAddr (l_cust_party_id);
1200 FETCH C_Get_PrAddr INTO x_party_site_id;
1201 CLOSE C_Get_PrAddr;
1202
1203 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1204 aso_debug_pub.add('Function Get_BillAddress Cursor C_Get_PrAddr Identifying Address'||
1205 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1206 END IF;
1207
1208 END IF;
1209
1210 ELSE
1211 /* if Party Type is ORGANIZATION */
1212
1213 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1214 aso_debug_pub.add('Function Get_BillAddress IF l_cust_party_type is Organization :',1,'N');
1215 END IF;
1216 IF nvl(l_party_id,l_cust_party_id) = l_cust_party_id THEN
1217
1218 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1219 aso_debug_pub.add('Function Get_BillAddress IF l_cust_party_type is ORGANIZATION '||
1220 ' and Contact is not Specified :',1,'N');
1221 END IF;
1222 /* Get Primary Bill To Address for Bill to Customer */
1223 OPEN C_Use_Exists (l_cust_party_id);
1224 FETCH C_Use_Exists INTO x_party_site_id;
1225 CLOSE C_Use_Exists;
1226
1227 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1228 aso_debug_pub.add('Function Get_BillAddress Cursor C_Use_Exists Primary Address ' ||
1229 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1230 END IF;
1231
1232 IF x_party_site_id IS NULL THEN
1233 /* Get Identifying Address for Bill to Customer */
1234
1235 OPEN C_Get_PrAddr (l_cust_party_id);
1236 FETCH C_Get_PrAddr INTO x_party_site_id;
1237 CLOSE C_Get_PrAddr;
1238
1239 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1240 aso_debug_pub.add('Function Get_BillAddress Cursor C_Get_PrAddr Identifying Address'||
1241 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1242 END IF;
1243
1244 END IF;
1245
1246 ELSIF l_party_id <> l_cust_party_id THEN
1247
1248 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1249 aso_debug_pub.add('Function Get_BillAddress IF l_cust_party_type is ORGANIZATION '||
1250 ' and Contact is Specified :',1,'N');
1251 END IF;
1252
1253 /* Get Primary Bill To Address for Bill to Customer */
1254 OPEN C_Use_Exists (l_party_id);
1255 FETCH C_Use_Exists INTO x_party_site_id;
1256 CLOSE C_Use_Exists;
1257
1258 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1259 aso_debug_pub.add('Function Get_BillAddress Cursor C_Use_Exists Primary Address ' ||
1260 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1261 END IF;
1262
1263 IF x_party_site_id IS NULL THEN
1264 /* Get Identifying Address for Bill to Customer */
1265
1266 OPEN C_Get_PrAddr (l_party_id);
1267 FETCH C_Get_PrAddr INTO x_party_site_id;
1268 CLOSE C_Get_PrAddr;
1269
1270 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1271 aso_debug_pub.add('Function Get_BillAddress Cursor C_Get_PrAddr Identifying Address'||
1272 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1273 END IF;
1274 END IF;
1275
1276 IF x_party_site_id IS NULL THEN
1277 /* Get Primary Bill To Address for Bill to Customer */
1278 OPEN C_Use_Exists (l_cust_party_id);
1279 FETCH C_Use_Exists INTO x_party_site_id;
1280 CLOSE C_Use_Exists;
1281
1282 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1283 aso_debug_pub.add('Function Get_BillAddress Cursor C_Use_Exists Primary Address ' ||
1284 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1285 END IF;
1286 IF x_party_site_id IS NULL THEN
1287 /* Get Identifying Address for Bill to Customer */
1288
1289 OPEN C_Get_PrAddr (l_cust_party_id);
1290 FETCH C_Get_PrAddr INTO x_party_site_id;
1291 CLOSE C_Get_PrAddr;
1292
1293 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1294 aso_debug_pub.add('Function Get_BillAddress Cursor C_Get_PrAddr Identifying Address'||
1295 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1296 END IF;
1297
1298 END IF;
1299
1300
1301 END IF;
1302
1303 END IF;
1304
1305 END IF;
1306 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1307 aso_debug_pub.add('Function Get_BillAddress Party_Site_ID Returned :'||x_party_site_id ,1,'N');
1308 END IF;
1309
1310 RETURN x_party_site_id;
1311
1312 EXCEPTION
1313 WHEN NO_DATA_FOUND THEN
1314 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1315 aso_debug_pub.add('Function Get_BillAddress NO_DATA_FOUND Exception Occurs',1,'N');
1316 END IF;
1317
1318 RETURN NULL;
1319 WHEN OTHERS THEN
1320 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1321 aso_debug_pub.add('Function Get_BillAddress Inside When Others Exception',1,'N');
1322 END IF;
1323 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1324 THEN
1325 OE_MSG_PUB.Add_Exc_Msg
1326 ( G_PKG_NAME ,
1327 ' Get_BillAddress '
1328 );
1329 END IF;
1330
1331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1332
1333 END Get_BillAddress;
1334
1335 FUNCTION Get_ShipAddress(
1336 P_Database_Object_Name IN VARCHAR2,
1337 P_Attribute_Code IN VARCHAR2
1338 ) RETURN NUMBER
1339 IS
1340
1341 CURSOR C_Get_Type (l_party NUMBER) IS
1342 SELECT Party_Type
1343 FROM HZ_PARTIES
1344 WHERE Party_Id = l_party
1345 AND Status = 'A';
1346
1347 CURSOR C_Use_Exists (l_party NUMBER) IS
1348 SELECT A.Party_Site_Id
1349 FROM HZ_PARTY_SITES A, HZ_PARTY_SITE_USES B
1350 WHERE A.Party_Id = l_party
1351 AND A.Party_Site_Id = B.Party_Site_Id
1352 AND B.Site_Use_Type = 'SHIP_TO'
1353 AND B.Primary_Per_Type = 'Y'
1354 AND A.Status = 'A'
1355 AND B.Status = 'A';
1356
1357 CURSOR C_Get_PrAddr (l_party NUMBER) IS
1358 SELECT Party_Site_Id
1359 FROM HZ_PARTY_SITES
1360 WHERE Party_Id = l_party
1361 AND Identifying_Address_Flag = 'Y'
1362 AND Status = 'A';
1363
1364 l_cust_party_type VARCHAR2(15);
1365 l_cust_party_id NUMBER;
1366 l_party_id NUMBER;
1367 x_party_site_id NUMBER;
1368
1369 BEGIN
1370 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1371 aso_debug_pub.add('Function Get_ShipAddress --- Begin : ',1,'N');
1372 END IF;
1373
1374 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
1375
1376 /* Done code change for Bug - 5763528, commented following code -
1377
1378 --l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_cust_party_id;
1379 l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_INV_TO_CUST_PTY_ID;
1380 --l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_party_id;
1381 l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_INV_TO_PTY_ID; */
1382
1383 -- Following code replaced above code
1384
1385 l_cust_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_SHIP_TO_CUST_PARTY_ID;
1386 l_party_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_SHIP_TO_PARTY_ID;
1387
1388 -- End of code for Bug - 5763528
1389
1390 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1391 aso_debug_pub.add('Function Get_ShipAddress Database Object ASO_AK_QUOTE_HEADER_V '||
1392 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
1393 aso_debug_pub.add('Function Get_ShipAddress Database Object ASO_AK_QUOTE_HEADER_V '||
1394 ' l_party_id :' || l_party_id ,1,'N');
1395 END IF;
1396
1397 ELSIF p_database_object_name='ASO_AK_QUOTE_OPPTY_V' THEN
1398 --l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_cust_party_id;
1399 --l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_party_id;
1400 l_cust_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_cust_party_id;
1401 /* Bug 9594091 -- Commented not to copy the Sold to customer contact to ship to
1402 * Contact in Oppty to Quote defaulting bill to address flow */
1403
1404 --l_party_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_oppty_sld_to_cont_id;
1405
1406 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1407 aso_debug_pub.add('Function Get_ShipAddress Database Object ASO_AK_QUOTE_OPPTY_V '||
1408 ' l_cust_party_id :' || l_cust_party_id ,1,'N');
1409 aso_debug_pub.add('Function Get_ShipAddress Database Object ASO_AK_QUOTE_OPPTY_V '||
1410 ' l_party_id :' || l_party_id ,1,'N');
1411 END IF;
1412
1413 END IF;
1414
1415 OPEN C_Get_Type (l_cust_party_id);
1416 FETCH C_Get_Type INTO l_cust_party_type;
1417 CLOSE C_Get_Type;
1418 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1419 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Get_Type l_cust_party_type :'||
1420 l_cust_party_type,1,'N');
1421 END IF;
1422 /* if party type is PERSON */
1423 IF l_cust_party_type = 'PERSON' THEN
1424 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1425 aso_debug_pub.add('Function Get_ShipAddress IF l_cust_party_type is PERSON :',1,'N');
1426 END IF;
1427 /* Get Primary Ship To Address for Ship to Customer */
1428 OPEN C_Use_Exists (l_cust_party_id);
1429 FETCH C_Use_Exists INTO x_party_site_id;
1430 CLOSE C_Use_Exists;
1431
1432 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1433 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Use_Exists Primary Address ' ||
1434 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1435 END IF;
1436 IF x_party_site_id IS NULL THEN
1437 /* Get Identifying Address for SHIP to Customer */
1438 OPEN C_Get_PrAddr (l_cust_party_id);
1439 FETCH C_Get_PrAddr INTO x_party_site_id;
1440 CLOSE C_Get_PrAddr;
1441
1442 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1443 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Get_PrAddr Identifying Address'||
1444 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1445 END IF;
1446 END IF;
1447
1448 ELSE
1449 /* IF Party type is ORGANIZATION */
1450 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1451 aso_debug_pub.add('Function Get_ShipAddress IF l_cust_party_type is ORGANIZATION :',1,'N');
1452 END IF;
1453 IF nvl(l_party_id,l_cust_party_id) = l_cust_party_id THEN
1454 /* If Organization Contact IS NOT Specified */
1455 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1456 aso_debug_pub.add('Function Get_ShipAddress IF l_cust_party_type is ORGANIZATION '||
1457 ' and Contact is not Specified :',1,'N');
1458 END IF;
1459 /* Get Primary Ship To Address for Ship to Customer */
1460 OPEN C_Use_Exists (l_cust_party_id);
1461 FETCH C_Use_Exists INTO x_party_site_id;
1462 CLOSE C_Use_Exists;
1463
1464 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1465 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Use_Exists Primary Address ' ||
1466 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1467 END IF;
1468
1469 IF x_party_site_id IS NULL THEN
1470 /* Get Identifying Address for Ship to Customer */
1471 OPEN C_Get_PrAddr (l_cust_party_id);
1472 FETCH C_Get_PrAddr INTO x_party_site_id;
1473 CLOSE C_Get_PrAddr;
1474
1475 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1476 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Get_PrAddr Identifying Address'||
1477 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1478 END IF;
1479
1480 END IF;
1481
1482 ELSIF l_party_id <> l_cust_party_id THEN
1483 /* IF Organization Contact is Specified */
1484 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1485 aso_debug_pub.add('Function Get_ShipAddress IF l_cust_party_type is ORGANIZATION '||
1486 ' and Contact is Specified :',1,'N');
1487 END IF;
1488 /* Get Primary Ship To Address for Ship to Customer */
1489 OPEN C_Use_Exists (l_party_id);
1490 FETCH C_Use_Exists INTO x_party_site_id;
1491 CLOSE C_Use_Exists;
1492
1493 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1494 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Use_Exists Primary Address ' ||
1495 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1496 END IF;
1497
1498 IF x_party_site_id IS NULL THEN
1499 /* Get Identifying Address for Ship to Customer */
1500 OPEN C_Get_PrAddr (l_party_id);
1501 FETCH C_Get_PrAddr INTO x_party_site_id;
1502 CLOSE C_Get_PrAddr;
1503
1504 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1505 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Get_PrAddr Identifying Address'||
1506 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1507 END IF;
1508
1509 END IF;
1510
1511 IF x_party_site_id IS NULL THEN
1512 /* Get Primary Ship To Address for Ship to Customer */
1513 OPEN C_Use_Exists (l_cust_party_id);
1514 FETCH C_Use_Exists INTO x_party_site_id;
1515 CLOSE C_Use_Exists;
1516
1517 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1518 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Use_Exists Primary Address ' ||
1519 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1520 END IF;
1521
1522 IF x_party_site_id IS NULL THEN
1523 /* Get Identifying Address for Ship to Customer */
1524 OPEN C_Get_PrAddr (l_cust_party_id);
1525 FETCH C_Get_PrAddr INTO x_party_site_id;
1526 CLOSE C_Get_PrAddr;
1527
1528 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1529 aso_debug_pub.add('Function Get_ShipAddress Cursor C_Get_PrAddr Identifying Address'||
1530 ' x_party_site_id :' ||x_party_site_id ,1,'N');
1531 END IF;
1532
1533 END IF;
1534
1535
1536 END IF;
1537
1538 END IF;
1539
1540 END IF;
1541 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1542 aso_debug_pub.add('Function Get_ShipAddress Party_Site_ID Returned : '||x_party_site_id,1,'N');
1543 END IF;
1544
1545 RETURN x_party_site_id;
1546
1547 EXCEPTION
1548 WHEN NO_DATA_FOUND THEN
1549 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1550 aso_debug_pub.add('Function Get_ShipAddress NO_DATA_FOUND Exception Occurs',1,'N');
1551 END IF;
1552
1553 RETURN NULL;
1554 WHEN OTHERS THEN
1555 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1556 aso_debug_pub.add('Function Get_ShipAddress Inside When Others Exception',1,'N');
1557 END IF;
1558 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1559 THEN
1560 OE_MSG_PUB.Add_Exc_Msg
1561 ( G_PKG_NAME ,
1562 ' Get_ShipAddress '
1563 );
1564 END IF;
1565
1566 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1567
1568 END Get_ShipAddress;
1569
1570
1571
1572 FUNCTION Get_SalesGroup_From_Salesrep(
1573 P_Database_Object_Name IN VARCHAR2,
1574 P_Attribute_Code IN VARCHAR2
1575 ) RETURN NUMBER
1576 IS
1577
1578 CURSOR C_sales_grp (x_resource_id Number) IS
1579 SELECT jrgm.group_id
1580 FROM JTF_RS_GROUP_MEMBERS jrgm,
1581 JTF_RS_GROUPS_tl jrgt,
1582 JTF_RS_GROUP_USAGES jrgu
1583 WHERE jrgm.group_id =jrgt.group_id
1584 AND jrgt.language= userenv('LANG')
1585 AND jrgu.group_id = jrgm.group_id
1586 AND jrgu.usage = 'SALES'
1587 AND nvl(jrgm.delete_flag, 'N') <> 'Y'
1588 AND exists (SELECT 1 FROM
1589 jtf_rs_role_relations jrrr
1590 WHERE jrrr.role_resource_id= jrgm.group_member_id
1591 AND trunc( nvl(jrrr.start_date_active, SYSDATE)) <= trunc( SYSDATE )
1592 AND trunc( nvl(jrrr.end_date_active, SYSDATE)) >= trunc( SYSDATE )
1593 AND jrrr.role_resource_type='RS_GROUP_MEMBER'
1594 AND nvl(jrrr.delete_flag, 'N') <> 'Y'
1595 AND ROWNUM= 1)
1596 AND jrgm.resource_id = x_resource_id;
1597
1598 Cursor C_salesrep (X_User_Id Number) IS
1599 SELECT j.resource_id
1600 FROM jtf_rs_salesreps_mo_v srp, jtf_rs_resource_extns_vl j
1601 WHERE j.user_id = X_User_Id
1602 AND j.resource_id = srp.resource_id
1603 AND nvl(srp.status,'A') = 'A'
1604 AND nvl(trunc(srp.start_date_active), trunc(sysdate)) <= trunc(sysdate)
1605 AND nvl(trunc(srp.end_date_active), trunc(sysdate)) >= trunc(sysdate)
1606 AND nvl(trunc(j.start_date_active), trunc(sysdate)) <= trunc(sysdate)
1607 AND nvl(trunc(j.end_date_active), trunc(sysdate)) >= trunc(sysdate);
1608
1609 l_resource_id NUMBER;
1610 l_salesgroup_id NUMBER;
1611 l_org_id NUMBER;
1612 BEGIN
1613
1614 If NVL(FND_PROFILE.VALUE('ASO_AUTO_TEAM_ASSIGN'),'NONE') <> 'NONE' Then
1615 aso_debug_pub.add('Function Get_SalesGroup_From_Salesrep -- Automatic Team Assign'||fnd_profile.value('ASO_AUTO_TEAM_ASSIGN'),1,'N');
1616 return null;
1617 End If;
1618
1619 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
1620 l_resource_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_resource_id;
1621 l_org_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_Org_id;
1622 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
1623 l_resource_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.O_resource_id;
1624 l_org_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.O_Org_id;
1625 END IF;
1626
1627 IF l_org_id is null or l_org_id = FND_API.G_MISS_NUM THEN
1628 return null;
1629 END IF;
1630
1631 IF l_resource_id IS NULL OR l_resource_id = FND_API.G_MISS_NUM THEN
1632 OPEN C_salesrep(FND_GLOBAL.USER_ID);
1633 FETCH C_salesrep INTO l_resource_id;
1634 CLOSE C_salesrep;
1635 END IF;
1636
1637 IF l_resource_id IS NOT NULL THEN
1638 OPEN C_sales_grp(l_resource_id);
1639 FETCH C_sales_grp INTO l_salesgroup_id;
1640 CLOSE C_sales_grp;
1641 END IF;
1642
1643 RETURN l_salesgroup_id;
1644
1645 EXCEPTION
1646
1647 WHEN NO_DATA_FOUND THEN
1648 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1649 aso_debug_pub.add('Function Get_SalesGroup_From_Salesrep NO_DATA_FOUND Exception Occurs',1,'N');
1650 END IF;
1651
1652 RETURN NULL;
1653
1654 WHEN OTHERS THEN
1655 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1656 aso_debug_pub.add('Function Get_SalesGroup_From_Salesrep Inside When Others Exception',1,'N');
1657 END IF;
1658
1659 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1660 THEN
1661 OE_MSG_PUB.Add_Exc_Msg
1662 ( G_PKG_NAME ,
1663 ' Get_SalesGroup_From_Salesrep '
1664 );
1665 END IF;
1666
1667 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1668
1669 END Get_SalesGroup_From_Salesrep;
1670
1671 FUNCTION Get_SalesGroup_From_Profile (
1672 P_Database_Object_Name IN VARCHAR2,
1673 P_Attribute_Code IN VARCHAR2
1674 ) RETURN NUMBER
1675 IS
1676 l_org_id NUMBER;
1677 l_salesgroup_id NUMBER;
1678
1679 BEGIN
1680
1681 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1682 aso_debug_pub.add('Function Get_SalesGroup_From_Profile -- Begin :',1,'N');
1683 END IF;
1684
1685 If NVL(FND_PROFILE.VALUE('ASO_AUTO_TEAM_ASSIGN'),'NONE') <> 'NONE' Then
1686 aso_debug_pub.add('Function Get_SalesGroup_From_Profile -- Automatic Team Assign'||fnd_profile.value('ASO_AUTO_TEAM_ASSIGN'),1,'N');
1687 return null;
1688 End If;
1689
1690 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
1691 l_org_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_Org_id;
1692 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
1693 l_org_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.O_Org_id;
1694 END IF;
1695
1696 l_salesgroup_id := aso_utility_pvt.get_ou_attribute_value(aso_utility_pvt.G_DEFAULT_SALES_GROUP,l_org_id);
1697
1698 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1699 aso_debug_pub.add('Function Get_SalesGroup_From_Profile Sales Group Id :' ||l_salesgroup_id,1,'N');
1700 END IF;
1701
1702 RETURN l_salesgroup_id;
1703
1704 EXCEPTION
1705
1706 WHEN OTHERS THEN
1707 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1708 aso_debug_pub.add('Function Get_SalesGroup_From_Profile Inside When Others Exception',1,'N');
1709 END IF;
1710 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1711 THEN
1712 OE_MSG_PUB.Add_Exc_Msg
1713 ( G_PKG_NAME ,
1714 ' Get_SalesGroup_From_Profile '
1715 );
1716 END IF;
1717
1718 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1719
1720 END Get_SalesGroup_From_Profile;
1721
1722
1723 FUNCTION Get_SalesRep (
1724 P_Database_Object_Name IN VARCHAR2,
1725 P_Attribute_Code IN VARCHAR2
1726 ) RETURN NUMBER
1727 IS
1728
1729 CURSOR C_Get_Creator_Res (l_user_id NUMBER) IS
1730 SELECT resource_id
1731 FROM JTF_RS_RESOURCE_EXTNS
1732 WHERE user_id = l_user_id
1733 AND SYSDATE BETWEEN NVL(start_date_active,sysdate) AND NVL(end_date_active, SYSDATE);
1734
1735 CURSOR C_Valid_SalesRep (l_res_id NUMBER) IS
1736 SELECT 'Y'
1737 FROM JTF_RS_SALESREPS_MO_V
1738 WHERE resource_id = l_res_id
1739 AND NVL(status,'A') = 'A'
1740 AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
1741 AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate);
1742
1743 CURSOR C_Get_Res_From_Srep (l_Srep VARCHAR2) IS
1744 SELECT Resource_Id
1745 FROM JTF_RS_SALESREPS_MO_V
1746 WHERE salesrep_number = l_Srep
1747 AND NVL(status,'A') = 'A'
1748 AND nvl(trunc(start_date_active), trunc(sysdate)) <= trunc(sysdate)
1749 AND nvl(trunc(end_date_active), trunc(sysdate)) >= trunc(sysdate);
1750
1751 l_creator_res NUMBER;
1752 l_valid VARCHAR2(1) := 'N';
1753 l_default_salesrep VARCHAR2(40) := aso_utility_pvt.get_default_salesrep;
1754 l_profile_salesrep_id NUMBER;
1755 l_org_id NUMBER;
1756
1757 BEGIN
1758 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1759 aso_debug_pub.add('Function Get_SalesRep --- Begin :',1,'N');
1760 END IF;
1761
1762 If NVL(FND_PROFILE.VALUE('ASO_AUTO_TEAM_ASSIGN'),'NONE') <> 'NONE' Then
1763 aso_debug_pub.add('Function Get_SalesRep -- Automatic Team Assign'||fnd_profile.value('ASO_AUTO_TEAM_ASSIGN'),1,'N');
1764 return null;
1765 End If;
1766
1767 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
1768 l_org_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_ORG_ID;
1769 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
1770 l_org_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.O_ORG_ID;
1771 END IF;
1772
1773 IF l_org_id is null or l_org_id = FND_API.G_MISS_NUM THEN
1774 return null;
1775 END IF;
1776
1777 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1778 aso_debug_pub.add('Function Get_SalesRep Database Object :'||p_database_object_name,1,'N');
1779 aso_debug_pub.add('Function Get_SalesRep ORG ID : '|| l_org_id,1,'N');
1780 END IF;
1781
1782 OPEN C_Get_Creator_Res(FND_GLOBAL.USER_ID);
1783 FETCH C_Get_Creator_Res INTO l_creator_res;
1784 CLOSE C_Get_Creator_Res;
1785
1786 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1787 aso_debug_pub.add('Function Get_SalesRep Cursor C_Get_Creator_Res: '||l_creator_res,1,'N');
1788 END IF;
1789
1790 IF l_creator_res IS NOT NULL THEN
1791 OPEN C_Valid_SalesRep (l_creator_res);
1792 FETCH C_Valid_SalesRep INTO l_valid;
1793 CLOSE C_Valid_SalesRep;
1794
1795 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1796 aso_debug_pub.add('Function Get_SalesRep Cursor C_Valid_SalesRep: '||l_valid,1,'N');
1797 END IF;
1798 END IF;
1799
1800 IF (l_creator_res IS NULL OR l_valid <> 'Y') THEN --AND l_profile_salesrep_id IS NOT NULL THEN
1801
1802 -- Bug 4724024
1803 IF (l_valid <> 'Y') THEN
1804 l_creator_res := NULL ;
1805 END IF;
1806
1807 -- Passing Org id in the call - Girish 10/18/2005
1808 l_profile_salesrep_id := aso_utility_pvt.get_ou_attribute_value(l_default_salesrep, l_org_id);
1809
1810 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1811 aso_debug_pub.add('Function Get_SalesRep l_profile_salesrep_id: '||l_profile_salesrep_id,1,'N');
1812 END IF;
1813
1814 IF l_profile_salesrep_id IS NOT NULL THEN
1815 OPEN C_Get_Res_From_Srep (l_profile_salesrep_id);
1816 FETCH C_Get_Res_From_Srep INTO l_creator_res;
1817 CLOSE C_Get_Res_From_Srep;
1818 END IF;
1819 END IF;
1820
1821 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1822 aso_debug_pub.add('Function Get_SalesRep Returns: '||l_creator_res,1,'N');
1823 END IF;
1824
1825 RETURN l_creator_res;
1826
1827 EXCEPTION
1828 WHEN NO_DATA_FOUND THEN
1829 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1830 aso_debug_pub.add('Function Get_SalesRep NO_DATA_FOUND Exception Occurs',1,'N');
1831 END IF;
1832
1833 RETURN NULL;
1834
1835 WHEN OTHERS THEN
1836 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1837 aso_debug_pub.add('Function Get_SalesRep Inside When Others Exception',1,'N');
1838 END IF;
1839 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1840 THEN
1841 OE_MSG_PUB.Add_Exc_Msg
1842 ( G_PKG_NAME ,
1843 ' Get_SalesRep '
1844 );
1845 END IF;
1846
1847 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1848
1849 END Get_SalesRep;
1850
1851 FUNCTION Get_Currency_from_pricelist(
1852 P_Database_Object_Name IN VARCHAR2,
1853 P_Attribute_Code IN VARCHAR2
1854 ) RETURN VARCHAR2
1855 is
1856 Cursor C_currency_code(q_price_list_id Number) is
1857 SELECT currency_code
1858 FROM qp_price_lists_v
1859 WHERE price_list_id = q_price_list_id;
1860
1861 l_price_list_id Number;
1862 l_currency_code C_currency_code%rowtype;
1863
1864 Begin
1865 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1866 aso_debug_pub.add('Function Get_Currency_from_pricelist --- Begin : ',1,'N');
1867 END IF;
1868
1869 IF p_database_object_name = 'ASO_AK_QUOTE_HEADER_V' THEN
1870 l_price_list_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.q_price_list_id;
1871
1872 ELSIF p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
1873 l_price_list_id := ASO_QUOTE_OPPTY_DEF_HDLR.g_record.o_price_list_id;
1874
1875
1876 END IF;
1877
1878 open C_currency_code(l_price_list_id);
1879 fetch C_currency_code into l_currency_code;
1880 close C_Currency_code;
1881
1882 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1883 aso_debug_pub.add('Function Get_Currency_from_pricelist Returns : '||l_currency_code.currency_code,1,'N');
1884 END IF;
1885
1886 RETURN l_currency_code.currency_code;
1887
1888
1889 EXCEPTION
1890 WHEN NO_DATA_FOUND THEN
1891 RETURN NULL;
1892
1893 WHEN OTHERS THEN
1894 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1895 aso_debug_pub.add('Function Get_Currency_from_pricelist Inside When Others Exception',1,'N');
1896 END IF;
1897 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1898 THEN
1899 OE_MSG_PUB.Add_Exc_Msg
1900 ( G_PKG_NAME ,
1901 ' Get_Currency_from_pricelist '
1902 );
1903 END IF;
1904
1905 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1906
1907 End Get_Currency_from_pricelist;
1908
1909 FUNCTION Get_Currency_from_Profile(
1910 P_Database_Object_Name IN VARCHAR2,
1911 P_Attribute_Code IN VARCHAR2
1912 ) RETURN VARCHAR2
1913 Is
1914 Begin
1915 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1916 aso_debug_pub.add('Function Get_Currency_from_Profile --- Begin :',1,'N');
1917 END IF;
1918
1919 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' OR
1920 p_database_object_name = 'ASO_AK_QUOTE_OPPTY_V' THEN
1921 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1922 aso_debug_pub.add('Function Get_Currency_From_Profile Profile ICX_PREFERRED_CURRENCY : '||
1923 fnd_profile.Value('ICX_PREFERRED_CURRENCY') ,1,'N');
1924 END IF;
1925
1926 return FND_PROFILE.Value('ICX_PREFERRED_CURRENCY');
1927 END IF;
1928 EXCEPTION
1929 WHEN NO_DATA_FOUND THEN
1930 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1931 aso_debug_pub.add('Function Get_Currency_from_Profile NO_DATA_FOUND Exception Occurs : ',1,'N');
1932 END IF;
1933
1934 RETURN NULL;
1935
1936 WHEN OTHERS THEN
1937 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1938 aso_debug_pub.add('Function Get_Currency_From_Profile Inside When Others Exception',1,'N');
1939 END IF;
1940 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1941 THEN
1942 OE_MSG_PUB.Add_Exc_Msg
1943 ( G_PKG_NAME ,
1944 ' Get_Currency_from_Profile '
1945 );
1946 END IF;
1947
1948 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1949
1950 END Get_Currency_from_Profile;
1951
1952
1953 FUNCTION Get_RequestedDateType(P_Database_Object_Name IN VARCHAR2,
1954 P_Attribute_Code IN VARCHAR2) RETURN VARCHAR2
1955
1956 IS
1957
1958 l_ret_value HZ_CUST_ACCOUNTS.DATE_TYPE_PREFERENCE%TYPE;
1959 l_acct_id NUMBER;
1960
1961 CURSOR C_Get_RDT (l_acct NUMBER) IS
1962 SELECT date_type_preference
1963 FROM HZ_CUST_ACCOUNTS
1964 WHERE Cust_Account_Id = l_acct
1965 AND Status = 'A';
1966
1967 BEGIN
1968 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1969 aso_debug_pub.add('Function Get_RequestedDateType --- Begin : ',1,'N');
1970 END IF;
1971
1972 IF p_database_object_name='ASO_AK_QUOTE_HEADER_V' THEN
1973 l_acct_id := ASO_QUOTE_HEADER_DEF_HDLR.g_record.Q_cust_account_id;
1974
1975 IF ( l_acct_id IS NOT NULL AND
1976 l_acct_id <> FND_API.G_MISS_NUM ) THEN
1977 OPEN C_Get_RDT (l_acct_id);
1978 FETCH C_Get_RDT INTO l_ret_value;
1979 CLOSE C_Get_RDT;
1980
1981 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1982 aso_debug_pub.add('Function Get_RequestedDateType cusror C_Get_RDT l_ret_value : ' ||
1983 l_ret_value ,1,'N');
1984 END IF;
1985 ELSE
1986 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1987 aso_debug_pub.add('Function Get_RequestedDateType -CustomerAccount is null - requesteddatetype no retrieved',1,'N');
1988 END IF;
1989 END IF;
1990
1991 END IF;
1992
1993 RETURN l_ret_value;
1994
1995 EXCEPTION
1996 WHEN NO_DATA_FOUND THEN
1997 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
1998 aso_debug_pub.add('Function Get_RequestedDateType NO_DATA_FOUND Occurs ',1,'N');
1999 END IF;
2000
2001 RETURN NULL;
2002
2003 WHEN OTHERS THEN
2004
2005 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2006 aso_debug_pub.add('Function Get_RequestedDateType Inside When Others Exception',1,'N');
2007 END IF;
2008
2009 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2010 THEN
2011 OE_MSG_PUB.Add_Exc_Msg
2012 ( G_PKG_NAME ,
2013 ' Get_RequestedDateType '
2014 );
2015 END IF;
2016
2017 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2018
2019 END Get_RequestedDateType;
2020
2021
2022 FUNCTION Get_ChargePeriodicity(P_Database_Object_Name IN VARCHAR2,
2023 P_Attribute_Code IN VARCHAR2) RETURN VARCHAR2
2024
2025 IS
2026
2027 l_ret_value mtl_system_items_vl.charge_periodicity_code%TYPE;
2028 l_organization_id NUMBER;
2029 l_inventory_item_id NUMBER ;
2030
2031 CURSOR C_Get_CP (l_organization NUMBER, l_inventory_item NUMBER) IS
2032 SELECT charge_periodicity_code
2033 FROM mtl_system_items_b
2034 WHERE organization_id = l_organization
2035 AND inventory_item_id = l_inventory_item;
2036
2037 BEGIN
2038 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2039 aso_debug_pub.add('Function Get_ChargePeriodicity --- Begin : ',1,'N');
2040 END IF;
2041
2042 IF p_database_object_name='ASO_AK_QUOTE_LINE_V' THEN
2043 l_organization_id := ASO_QUOTE_LINE_DEF_HDLR.g_record.L_ORGANIZATION_ID;
2044 l_inventory_item_id := ASO_QUOTE_LINE_DEF_HDLR.g_record.L_INVENTORY_ITEM_ID;
2045
2046 IF ( l_organization_id IS NOT NULL AND
2047 l_organization_id <> FND_API.G_MISS_NUM )
2048 AND ( l_inventory_item_id IS NOT NULL AND
2049 l_inventory_item_id <> FND_API.G_MISS_NUM )THEN
2050 OPEN C_Get_CP (l_organization_id, l_inventory_item_id);
2051 FETCH C_Get_CP INTO l_ret_value;
2052 CLOSE C_Get_CP;
2053
2054 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2055 aso_debug_pub.add('Function Get_ChargePeriodicity cusror C_Get_CP l_ret_value : ' ||
2056 l_ret_value ,1,'N');
2057 END IF;
2058 ELSE
2059 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2060 aso_debug_pub.add('Function Get_ChargePeriodicity -Org Id is null - Charge Periodicity not retrieved',1,'N');
2061 END IF;
2062 END IF;
2063
2064 END IF;
2065
2066 RETURN l_ret_value;
2067
2068 EXCEPTION
2069 WHEN NO_DATA_FOUND THEN
2070 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2071 aso_debug_pub.add('Function Get_ChargePeriodicity NO_DATA_FOUND Occurs ',1,'N');
2072 END IF;
2073
2074 RETURN NULL;
2075
2076 WHEN OTHERS THEN
2077
2078 IF ASO_DEBUG_PUB.G_Debug_Flag = 'Y' THEN
2079 aso_debug_pub.add('Function Get_ChargePeriodicity Inside When Others Exception',1,'N');
2080 END IF;
2081
2082 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2083 THEN
2084 OE_MSG_PUB.Add_Exc_Msg
2085 ( G_PKG_NAME ,
2086 ' Get_ChargePeriodicity '
2087 );
2088 END IF;
2089
2090 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2091
2092 END Get_ChargePeriodicity;
2093
2094 END QOT_DEFAULT_PVT;
[Home] [Help]