1 PACKAGE BODY OE_Default_Pvt AS
2 /* $Header: OEXVDEFB.pls 120.15 2012/01/03 18:58:13 gabhatia ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Default_Pvt';
7
8 -----------------------------------------------------------------------
9 -- DEFAULTING FUNCTIONS TO BE USED FOR ATTRIBUTES ON ORDER HEADER
10 -----------------------------------------------------------------------
11
12
13 FUNCTION Get_Credit_Card_Number
14 (p_database_object_name IN VARCHAR2
15 ,p_attribute_code IN VARCHAR2)
16 RETURN VARCHAR2
17 IS
18 l_Credit_Card_Number VARCHAR2(80);
19 l_Bank_Account_ID Number;
20 l_Invoice_To_Cust_Id Number;
21
22 /*
23 Cursor C_Credit_Card_Number (X_Bank_Account_ID NUMBER) is
24 SELECT bank_account_num
25 FROM ap_bank_accounts
26 WHERE bank_account_id = X_Bank_Account_Id
27 AND bank_branch_id=1
28 AND account_type='EXTERNAL';
29 */
30
31 Cursor C_Invoice_To_Cust (X_Invoice_To_Org_Id NUMBER) is
32 SELECT customer_id
33 FROM oe_invoice_to_orgs_v
34 WHERE organization_id = X_Invoice_To_Org_Id;
35
36 Cursor get_hdr_sold_inv(x_header_id number) is
37 SELECT sold_to_org_id, invoice_to_org_id
38 from oe_order_headers_all
39 where header_id = x_header_id;
40
41 Cursor get_line_sold_inv(x_line_id number) is
42 SELECT sold_to_org_id, invoice_to_org_id
43 from oe_order_lines_all
44 where line_id = x_line_id;
45
46 l_cust_org_id number;
47 l_invoice_to_org_id number;
48 l_payment_type_code varchar2(30) := NULL;
49 --R12 CC Encryption
50 l_result_limit IBY_FNDCPT_COMMON_PUB.ResultLimit_rec_type;
51 l_conditions IBY_FNDCPT_COMMON_PUB.TrxnContext_rec_type;
52 l_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
53 l_assignments IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_tbl_type;
54 L_response_code IBY_FNDCPT_COMMON_PUB.Result_rec_type;
55 L_return_status VARCHAR2(30);
56 L_msg_count NUMBER;
57 L_msg_data VARCHAR2(2000);
58 l_payment_function VARCHAR2(40) := 'CUSTOMER_PAYMENT';
59 l_org_type VARCHAR2(40) := 'OPERATING_UNIT';
60 l_party_id NUMBER;
61 l_cust_account_id NUMBER;
62 l_trans_curr_code VARCHAR2(20); --Verify
63 l_card_instrument IBY_FNDCPT_SETUP_PUB.CreditCard_rec_Type;
64
65 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
66
67 BEGIN
68
69 IF l_debug_level > 0 THEN
70 oe_debug_pub.add('Entering OE_Default_PVT.Get_Credit_Card_Number.', 1);
71 END IF;
72
73
74 IF p_database_object_name = 'OE_AK_ORDER_HEADERS_V' THEN
75
76 l_payment_type_code := ONT_HEADER_DEF_HDLR.g_record.payment_type_code;
77
78 IF ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id IS NOT NULL
79 AND ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id <> FND_API.G_MISS_NUM
80 THEN
81
82 l_cust_org_id := ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id;
83 IF ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id IS NOT NULL
84 AND ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id <> FND_API.G_MISS_NUM
85 THEN
86 l_invoice_to_org_id := ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id;
87 END IF; -- if ont_header_def_hdlr.g_record.invoice_to...
88 ELSE
89 RETURN NULL;
90 END IF; -- if ont_header_def_hdlr.g_record.sold_to_org_id is not null
91
92 ELSIF p_database_object_name = 'OE_AK_HEADER_PAYMENTS_V' THEN
93
94 l_payment_type_code := ONT_HEADER_PAYMENT_DEF_HDLR.g_record.payment_type_code;
95
96 OPEN get_hdr_sold_inv(ONT_HEADER_PAYMENT_DEF_HDLR.g_record.header_id);
97 FETCH get_hdr_sold_inv into l_cust_org_id, l_invoice_to_org_id;
98 CLOSE get_hdr_sold_inv;
99
100 ELSIF p_database_object_name = 'OE_AK_LINE_PAYMENTS_V' THEN
101
102 l_payment_type_code := ONT_LINE_PAYMENT_DEF_HDLR.g_record.payment_type_code;
103
104 OPEN get_line_sold_inv(ONT_LINE_PAYMENT_DEF_HDLR.g_record.line_id);
105 FETCH get_line_sold_inv into l_cust_org_id, l_invoice_to_org_id;
106 CLOSE get_line_sold_inv;
107
108 END IF;
109
110 IF nvl(l_payment_type_code, 'x') <> 'CREDIT_CARD' THEN
111
112 Return null;
113
114 END IF;
115
116 /* Fix Bug #2297053:Customer of Invoice To may be different from the Sold To */
117 --R12 CC Encryption Verify
118 --l_trans_curr_code := ONT_HEADER_DEF_HDLR.transactional_curr_code;
119 --R12 CC Encryption
120
121 IF l_invoice_to_org_id is not null THEN
122 OPEN C_Invoice_To_Cust(l_invoice_to_org_id);
123 FETCH C_Invoice_To_Cust INTO l_Invoice_To_Cust_Id;
124 CLOSE C_Invoice_To_Cust;
125 END IF;
126 --R12 CC Encryption
127 --Need to call the appropriate payments API to populate
128 --the credit card details now.
129
130 /*l_Bank_Account_ID := arp_bank_pkg.get_primary_bank_acct
131 ( l_Invoice_To_Cust_Id
132 , l_invoice_to_org_id
133 , TRUE );
134 ELSIF l_cust_org_id is not null THEN
135
136 l_Bank_Account_ID := arp_bank_pkg.get_primary_bank_acct
137 (l_cust_org_id);
138 END IF;
139
140 IF l_Bank_Account_ID is NOT NULL THEN
141 OPEN C_Credit_Card_Number(l_Bank_Account_ID);
142 FETCH C_Credit_Card_Number INTO l_Credit_Card_Number;
143 CLOSE C_Credit_Card_Number;
144 RETURN l_Credit_Card_Number;
145 ELSE
146 RETURN NULL;*/
147
148 --R12 CC Encryption
149 Begin
150 Select party_site.party_id, acct_site.cust_account_id
151 Into l_party_id, l_cust_account_id
152 From HZ_CUST_SITE_USES_ALL SITE,
153 HZ_PARTY_SITES PARTY_SITE,
154 HZ_CUST_ACCT_SITES ACCT_SITE
155 Where SITE.SITE_USE_ID = l_invoice_to_org_id
156 AND SITE.SITE_USE_CODE = 'BILL_TO'
157 AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
158 AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
159 AND SITE.ORG_ID = ACCT_SITE.ORG_ID;
160 Exception
161 When No_Data_Found THEN
162 Null;
163 End;
164
165 L_result_limit.default_flag := 'Y';
166
167 --Setting the payer context values
168 l_payer.party_id := l_party_id; --Verify
169 l_payer.payment_function := l_payment_function;
170 l_payer.org_type := l_org_type;
171 l_payer.org_id := OE_GLOBALS.G_ORG_ID;
172 l_payer.cust_account_id := l_cust_account_id; --l_invoice_to_org_id
173 l_payer.account_site_id := l_invoice_to_org_id;
174
175 l_conditions.Currency_Code := l_trans_curr_code;
176 l_conditions.Payment_InstrType := 'CREDITCARD';
177
178 IF l_debug_level > 0 THEN
179 oe_debug_pub.add('Payer context values in Defaulting package');
180 oe_debug_pub.add('Party id'||l_party_id);
181 oe_debug_pub.add('Org id'||l_payer.org_id);
182 oe_debug_pub.add('cust acct id'||l_invoice_to_org_id);
183 oe_debug_pub.add('acct site id'||l_invoice_to_cust_id);
184 END IF;
185
186 --payer equivalency value of g_payer_equiv_upward means to retrieve from
187 --if out found at the current transaction level higher level,
188 --site -> customer -> party level
189 IBY_FNDCPT_SETUP_PUB.Get_Trxn_Appl_Instr_Assign
190 (p_api_version => 1.0,
191 X_return_status => l_return_status,
192 X_msg_count => l_msg_count,
193 X_msg_data => l_msg_data,
194 P_payer => l_payer,
195 P_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
196 P_conditions => l_conditions,
197 P_result_limit => l_result_limit,
198 X_assignments => l_assignments,
199 X_response => l_response_code);
200
201 IF l_debug_level > 0 THEN
202 oe_debug_pub.add('Return status after Get_Trxn_Appl_Instr_Assign is: '||l_return_status, 3);
203 oe_debug_pub.add('assignment id is: '||l_assignments(1).instrument.instrument_id, 3);
204 END IF;
205
206 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
207 IF l_debug_level > 0 THEN
208 oe_debug_pub.add('Result error code in Get_Trxn_Appl_Instr_Assign'||l_response_code.result_code);
209 END IF;
210 RAISE FND_API.G_EXC_ERROR;
211 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
212 IF l_debug_level > 0 THEN
213 oe_debug_pub.add('Result error code in Get_Trxn_Appl_Instr_Assign'||l_response_code.result_code);
214 END IF;
215 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
216 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
217 IF l_debug_level > 0 THEN
218 oe_debug_pub.add('Get_Trxn_Appl_Instr_Assign Successful....'||l_assignments(1).instrument.instrument_id);
219 oe_debug_pub.add('After calling Get_Trxn_Appl_Instr_Assign');
220 END IF;
221 END IF;
222
223 IF l_debug_level > 0 THEN
224 oe_debug_pub.add('Calling IBY_FNDCPT_SETUP_PUB.Get_Card.',3);
225 END IF;
226
227
228 IBY_FNDCPT_SETUP_PUB.Get_Card
229 (p_api_version => 1.0,
230 X_return_status => l_return_status,
231 X_msg_count => l_msg_count,
232 X_msg_data => l_msg_data,
233 P_card_id => l_assignments(1).instrument.instrument_id,
234 X_card_instrument => l_card_instrument,
235 X_response => l_response_code);
236
237 IF l_debug_level > 0 THEN
238 oe_debug_pub.add('Return status after IBY_FNDCPT_SETUP_PUB.Get_Card.'||l_return_status, 3);
239 END IF;
240
241 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
242 IF l_debug_level > 0 THEN
243 oe_debug_pub.add('Result error code in Get_Card'||l_response_code.result_code);
244 END IF;
245 RAISE FND_API.G_EXC_ERROR;
246 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
247 IF l_debug_level > 0 THEN
248 oe_debug_pub.add('Result error code in Get_Card'||l_response_code.result_code);
249 END IF;
250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
251 ELSIF l_return_status =FND_API.G_RET_STS_SUCCESS THEN
252 IF l_debug_level > 0 THEN
253 oe_debug_pub.add('Get_Card Successful....');
254 oe_debug_pub.add('After calling Get_Card');
255 oe_debug_pub.add('Card values in Defaulting package');
256 --oe_debug_pub.add('card_number'||l_card_instrument.card_number);
257 --oe_debug_pub.add('expiration_date'||l_card_instrument.expiration_date);
258 --oe_debug_pub.add('Card_Holder_Name'||l_card_instrument.Card_Holder_Name);
259 --oe_debug_pub.add('card_id'||l_card_instrument.card_id);
260 oe_debug_pub.add('Instrument_id'||l_assignments(1).instrument.instrument_id);
261 oe_debug_pub.add('assign id'||l_assignments(1).assignment_id);
262 END IF;
263 END IF;
264
265
266 IF p_database_object_name = 'OE_AK_ORDER_HEADERS_V' THEN
267 ONT_HEADER_DEF_HDLR.g_record.credit_card_number := l_card_instrument.card_number;
268 ONT_HEADER_DEF_HDLR.g_record.credit_card_holder_name := l_card_instrument.card_holder_name;
269 ONT_HEADER_DEF_HDLR.g_record.credit_card_code := l_card_instrument.card_issuer;
270 ONT_HEADER_DEF_HDLR.g_record.credit_card_expiration_date := l_card_instrument.expiration_date;
271 OE_Default_Pvt.g_default_instrument_id := l_assignments(1).instrument.instrument_id;
272 OE_Default_Pvt.g_default_instr_assignment_id := l_assignments(1).assignment_id;
273 ELSIF p_database_object_name = 'OE_AK_HEADER_PAYMENTS_V' THEN
274 ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_number := l_card_instrument.card_number;
275 ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_holder_name := l_card_instrument.card_holder_name;
276 ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_code := l_card_instrument.card_issuer;
277 ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_expiration_date := l_card_instrument.expiration_date;
278 OE_Default_Pvt.g_default_instrument_id := l_assignments(1).instrument.instrument_id;
279 OE_Default_Pvt.g_default_instr_assignment_id := l_assignments(1).assignment_id;
280 ELSIF p_database_object_name = 'OE_AK_LINE_PAYMENTS_V' THEN
281 ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_number := l_card_instrument.card_number;
282 ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_holder_name := l_card_instrument.card_holder_name;
283 ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_code := l_card_instrument.card_issuer;
284 ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_expiration_date := l_card_instrument.expiration_date;
285 OE_Default_Pvt.g_default_instrument_id := l_assignments(1).instrument.instrument_id;
286 OE_Default_Pvt.g_default_instr_assignment_id := l_assignments(1).assignment_id;
287 END IF;
288
289
290 --R12 CC Encryption
291
292 --IF l_debug_level > 0 THEN
293 --oe_debug_pub.add('returned card number is: '||l_card_instrument.card_number);
294 --END IF;
295
296 RETURN l_card_instrument.card_number;
297
298 EXCEPTION
299 WHEN NO_DATA_FOUND THEN
300 RETURN NULL;
301
302 WHEN FND_API.G_EXC_ERROR THEN
303 l_return_status := FND_API.G_RET_STS_ERROR;
304 OE_MSG_PUB.Count_And_Get
305 ( p_count => l_msg_count,
306 p_data => l_msg_data
307 );
308 RETURN NULL;
309
310 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
311 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 OE_MSG_PUB.Count_And_Get
313 ( p_count => l_msg_count,
314 p_data => l_msg_data
315 );
316 RETURN NULL;
317
318 WHEN OTHERS THEN
319 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
320 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
321 THEN
322 FND_MSG_PUB.Add_Exc_Msg
323 ( G_PKG_NAME
324 , 'OE_DEFAULT_PVT'
325 );
326 END IF;
327
328 OE_MSG_PUB.Count_And_Get
329 ( p_count => l_msg_count,
330 p_data => l_msg_data
331 );
332 RETURN NULL;
333 END Get_Credit_Card_Number;
334
335 --R12 CC Encryption
336 FUNCTION Get_Credit_Card_Code
337 ( p_database_object_name IN VARCHAR2
338 ,p_attribute_code IN VARCHAR2)
339 RETURN VARCHAR2
340 IS
341 BEGIN
342
343
344 IF p_database_object_name = 'OE_AK_ORDER_HEADERS_V'
345 AND ONT_HEADER_DEF_HDLR.g_record.credit_card_code IS NOT NULL
346 AND ONT_HEADER_DEF_HDLR.g_record.credit_card_code <> FND_API.G_MISS_CHAR THEN
347 RETURN ONT_HEADER_DEF_HDLR.g_record.credit_card_code;
348 ELSIF p_database_object_name = 'OE_AK_HEADER_PAYMENTS_V'
349 AND ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_code IS NOT NULL
350 AND ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_code <> FND_API.G_MISS_CHAR
351 THEN
352 RETURN ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_code;
353 ELSIF p_database_object_name = 'OE_AK_LINE_PAYMENTS_V'
354 AND ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_code IS NOT NULL
355 AND ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_code <> FND_API.G_MISS_CHAR
356 THEN
357 RETURN ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_code;
358 END IF;
359 RETURN NULL;
360
361 END Get_Credit_Card_Code;
362 --R12 CC Encryption
363
364 FUNCTION Get_CC_Holder_Name
365 ( p_database_object_name IN VARCHAR2
366 ,p_attribute_code IN VARCHAR2)
367 RETURN VARCHAR2
368 IS
369 l_Credit_Card_Holder_Name VARCHAR2(80);
370 l_Bank_Account_ID Number;
371 /**
372 Cursor C_Holder_Name (X_Bank_Account_ID NUMBER) is
373 SELECT bank_account_name
374 FROM ap_bank_accounts
375 WHERE bank_account_id = X_Bank_Account_Id
376 AND bank_branch_id=1
377 AND account_type='EXTERNAL';
378
379 Cursor C_Holder_Name2 (X_CC_Number VARCHAR2) is
380 SELECT bank_account_name
381 FROM ap_bank_accounts
382 WHERE bank_account_num=X_CC_Number
383 AND bank_branch_id=1
384 AND account_type='EXTERNAL'
385 AND rownum=1;
386 **/
387
388 Cursor get_hdr_sold_inv(x_header_id number) is
389 SELECT sold_to_org_id, invoice_to_org_id
390 from oe_order_headers_all
391 where header_id = x_header_id;
392
393 Cursor get_line_sold_inv(x_line_id number) is
394 SELECT sold_to_org_id, invoice_to_org_id
395 from oe_order_lines_all
396 where line_id = x_line_id;
397
398 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
399 l_credit_card_number VARCHAR2(80) := NULL;
400 l_cust_org_id number;
401 l_invoice_to_org_id number;
402
403 BEGIN
404
405 /*
406 ** Bug Fix # 2297053: CC Holder now defaulted from CC Number.
407 ** Old defaulting code will not even get executed now.
408 */
409 --R12 CC Encryption
410 IF p_database_object_name = 'OE_AK_ORDER_HEADERS_V'
411 AND ONT_HEADER_DEF_HDLR.g_record.credit_card_holder_name IS NOT NULL
412 AND ONT_HEADER_DEF_HDLR.g_record.credit_card_holder_name
413 <> FND_API.G_MISS_CHAR
414 THEN
415
416 RETURN ONT_HEADER_DEF_HDLR.g_record.credit_card_holder_name;
417 ELSIF p_database_object_name = 'OE_AK_HEADER_PAYMENTS_V'
418 AND ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_holder_name IS NOT NULL
419 AND ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_holder_name
420 <> FND_API.G_MISS_CHAR
421 THEN
422 RETURN ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_holder_name;
423 ELSIF p_database_object_name = 'OE_AK_LINE_PAYMENTS_V'
424 AND ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_holder_name IS NOT NULL
425 AND ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_holder_name
426 <> FND_API.G_MISS_CHAR
427 THEN
428 RETURN ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_holder_name;
429 END IF;
430 RETURN NULL;
431
432 /*IF p_database_object_name = 'OE_AK_ORDER_HEADERS_V' THEN
433 l_credit_card_number := ONT_HEADER_DEF_HDLR.g_record.credit_card_number;
434 ELSIF p_database_object_name = 'OE_AK_HEADER_PAYMENTS_V' THEN
435 l_credit_card_number := ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_number;
436 ELSIF p_database_object_name = 'OE_AK_LINE_PAYMENTS_V' THEN
437 l_credit_card_number := ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_number;
438 END IF;
439
440 IF l_credit_card_number IS NOT NULL AND
441 l_credit_card_number <> FND_API.G_MISS_CHAR THEN
442 OPEN C_Holder_Name2(l_credit_card_number);
443 FETCH C_Holder_Name2 INTO l_Credit_Card_Holder_Name;
444 CLOSE C_Holder_Name2;
445
446 END IF;
447
448 RETURN l_Credit_Card_Holder_Name;
449
450 -- code below is not needed. Hence the return statement is above this line
451
452 IF p_database_object_name = 'OE_AK_ORDER_HEADERS_V' THEN
453
454 IF ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id IS NOT NULL
455 AND ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id <> FND_API.G_MISS_NUM
456 THEN
457
458 l_cust_org_id := ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id;
459 IF ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id IS NOT NULL
460 AND ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id <> FND_API.G_MISS_NUM
461 THEN
462 l_invoice_to_org_id := ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id;
463 END IF; -- if ont_header_def_hdlr.g_record.invoice_to...
464 END IF; -- if ont_header_def_hdlr.g_record.sold_to_org_id is not null
465
466 ELSIF p_database_object_name = 'OE_AK_HEADER_PAYMENTS_V' THEN
467
468 OPEN get_hdr_sold_inv(ONT_HEADER_PAYMENT_DEF_HDLR.g_record.header_id);
469 FETCH get_hdr_sold_inv into l_cust_org_id, l_invoice_to_org_id;
470 CLOSE get_hdr_sold_inv;
471
472 ELSIF p_database_object_name = 'OE_AK_LINE_PAYMENTS_V' THEN
473
474 OPEN get_line_sold_inv(ONT_LINE_PAYMENT_DEF_HDLR.g_record.line_id);
475 FETCH get_line_sold_inv into l_cust_org_id, l_invoice_to_org_id;
476 CLOSE get_line_sold_inv;
477
478 END IF;
479
480 IF l_cust_org_id IS NOT NULL THEN
481 IF l_invoice_to_org_id IS NOT NULL THEN
482 l_Bank_Account_ID := arp_bank_pkg.get_primary_bank_acct
483 ( l_cust_org_id
484 , l_invoice_to_org_id
485 , TRUE);
486 ELSE
487 l_Bank_Account_ID := arp_bank_pkg.get_primary_bank_acct
488 (l_cust_org_id);
489 END IF;
490 ELSE
491 RETURN NULL;
492 END IF;
493
494 IF l_Bank_Account_ID is NOT NULL THEN
495 OPEN C_Holder_Name(l_Bank_Account_ID);
496 FETCH C_Holder_Name INTO l_Credit_Card_Holder_Name;
497 CLOSE C_Holder_Name;
498 RETURN l_Credit_Card_Holder_Name;
499 ELSE
500 RETURN NULL;
501 END IF;*/
502 --R12 CC Encryption
503
504 EXCEPTION
505 WHEN NO_DATA_FOUND THEN
506 RETURN NULL;
507 END Get_CC_Holder_Name;
508
509 FUNCTION Get_CC_Expiration_Date
510 ( p_database_object_name IN VARCHAR2
511 ,p_attribute_code IN VARCHAR2)
512 RETURN VARCHAR2
513 IS
514 l_CC_Expiration_Date VARCHAR2(20);
515 l_Bank_Account_ID Number;
516 /**
517 Cursor C_Expiry_Date (X_Bank_Account_ID NUMBER) is
518 SELECT to_char(inactive_date,'DD-MON-YYYY')
519 FROM ap_bank_accounts
520 WHERE bank_account_id = X_Bank_Account_Id
521 AND bank_branch_id=1
522 AND account_type='EXTERNAL';
523
524 Cursor C_Expiry_Date2 (X_CC_Number VARCHAR2) is
525 SELECT to_char(inactive_date,'DD-MON-YYYY')
526 FROM ap_bank_accounts
527 WHERE bank_account_num=X_CC_Number
528 AND bank_branch_id=1
529 AND account_type='EXTERNAL'
530 AND rownum=1;
531 **/
532 --
533 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
534 --
535 l_credit_card_number VARCHAR2(80) := NULL;
536
537 BEGIN
538
539 --R12 CC Encryption
540 IF p_database_object_name = 'OE_AK_ORDER_HEADERS_V'
541 AND ONT_HEADER_DEF_HDLR.g_record.credit_card_expiration_date IS NOT NULL
542 AND ONT_HEADER_DEF_HDLR.g_record.credit_card_expiration_date
543 <> FND_API.G_MISS_DATE
544 THEN
545 RETURN ONT_HEADER_DEF_HDLR.g_record.credit_card_expiration_date;
546 ELSIF p_database_object_name = 'OE_AK_HEADER_PAYMENTS_V'
547 AND ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_expiration_date IS NOT NULL
548 AND ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_expiration_date
549 <> FND_API.G_MISS_DATE
550 THEN
551 RETURN ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_expiration_date;
552 ELSIF p_database_object_name = 'OE_AK_LINE_PAYMENTS_V'
553 AND ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_expiration_date IS NOT NULL
554 AND ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_expiration_date
555 <> FND_API.G_MISS_DATE
556 THEN
557 RETURN ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_expiration_date;
558 END IF;
559
560 RETURN NULL;
561
562 /*
563 ** Bug Fix # 2297053: CC Exp Dt now defaulted from CC Number.
564 ** Old defaulting code will not even get executed now.
565 */
566
567 /*IF p_database_object_name = 'OE_AK_ORDER_HEADERS_V' THEN
568 l_credit_card_number := ONT_HEADER_DEF_HDLR.g_record.credit_card_number;
569 ELSIF p_database_object_name = 'OE_AK_HEADER_PAYMENTS_V' THEN
570 l_credit_card_number := ONT_HEADER_PAYMENT_DEF_HDLR.g_record.credit_card_number;
571 ELSIF p_database_object_name = 'OE_AK_LINE_PAYMENTS_V' THEN
572 l_credit_card_number := ONT_LINE_PAYMENT_DEF_HDLR.g_record.credit_card_number;
573 END IF;
574
575 IF l_credit_card_number IS NOT NULL AND
576 l_credit_card_number <> FND_API.G_MISS_CHAR THEN
577 OPEN C_Expiry_Date2(l_credit_card_number);
578 FETCH C_Expiry_Date2 INTO l_CC_Expiration_Date;
579 CLOSE C_Expiry_Date2;
580 END IF;
581
582 RETURN l_CC_Expiration_Date;
583
584 IF ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id IS NOT NULL
585 AND ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id <> FND_API.G_MISS_NUM THEN
586 IF ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id IS NOT NULL
587 AND ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id <> FND_API.G_MISS_NUM THEN
588 l_Bank_Account_ID := arp_bank_pkg.get_primary_bank_acct
589 (ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id
590 , ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id
591 , TRUE);
592 ELSE
593 l_Bank_Account_ID := arp_bank_pkg.get_primary_bank_acct
594 (ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id);
595 END IF;
596 ELSE
597 RETURN NULL;
598 END IF;
599 IF l_Bank_Account_ID is NOT NULL THEN
600 OPEN C_Expiry_Date(l_Bank_Account_ID);
601 FETCH C_Expiry_Date INTO l_CC_Expiration_Date;
602 CLOSE C_Expiry_Date;
603 RETURN l_CC_Expiration_Date;
604 ELSE
605 RETURN NULL;
606 END IF;*/
607 --R12 CC Encryption
608 EXCEPTION
609 WHEN NO_DATA_FOUND THEN
610 RETURN NULL;
611 END Get_CC_Expiration_Date;
612
613 /*************************************************************/
614 --
615 -- Following function is no more used in defaulting
616 --
617 /*************************************************************/
618
619 FUNCTION Get_Tax_Exempt_Number
620 ( p_database_object_name IN VARCHAR2
621 ,p_attribute_code IN VARCHAR2)
622 RETURN VARCHAR2
623 IS
624 l_tax_exempt_number VARCHAR2(80);
625 l_line_type_rec OE_Order_Cache.line_type_Rec_Type;
626 /***
627 CURSOR C_Std_Tax_Exemption(X_Ship_To_Org_Id NUMBER,
628 X_Invoice_To_Org_id NUMBER,
629 X_Date_Ordered DATE) is
630
631 SELECT tax.tax_exempt_number
632 FROM tax_exemptions_qp_v tax,
633 oe_ship_to_orgs_v s,
634 hr_organization_information hr,
635 hz_cust_site_uses site --ra_site_uses (bug fix 2116858)
636 WHERE tax.ship_to_site_use_id = s.site_use_id
637 AND s.organization_id = X_Ship_To_Org_Id
638 AND tax.bill_to_customer_id = hr.org_information1
639 AND hr.organization_id = X_Invoice_To_Org_Id
640 and hr.org_information_context = 'Customer/Supplier Association'
641 and SITE.SITE_USE_ID = TO_NUMBER ( hr.ORG_INFORMATION2 )
642 and SITE.SITE_USE_CODE = 'BILL_TO'
643 AND trunc(NVL(X_Date_Ordered, SYSDATE))
644 between trunc(tax.start_date) and
645 trunc(NVL(tax.end_date, NVL(X_Date_Ordered, SYSDATE)))
646 AND tax.status_code = 'PRIMARY'
647 AND ROWNUM = 1;
648 ***/
649 --
650 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
651 --
652 BEGIN
653 /***
654 IF ONT_HEADER_DEF_HDLR.g_record.ship_to_org_id IS NOT NULL AND
655 ONT_HEADER_DEF_HDLR.g_record.ship_to_org_id <> FND_API.G_MISS_NUM AND
656 ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id IS NOT NULL AND
657 ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id <> FND_API.G_MISS_NUM AND
658 ONT_HEADER_DEF_HDLR.g_record.ordered_date IS NOT NULL AND
659 ONT_HEADER_DEF_HDLR.g_record.ordered_date <> FND_API.G_MISS_DATE
660 THEN
661
662
663 OPEN C_Std_Tax_Exemption(ONT_HEADER_DEF_HDLR.g_record.ship_to_org_id,
664 ONT_HEADER_DEF_HDLR.g_record.Invoice_To_Org_id,
665 ONT_HEADER_DEF_HDLR.g_record.ordered_date);
666 FETCH C_Std_Tax_Exemption INTO l_tax_exempt_number;
667 CLOSE C_Std_Tax_Exemption;
668 END IF;
669 RETURN l_tax_exempt_number;
670 EXCEPTION
671 WHEN NO_DATA_FOUND THEN
672 RETURN NULL;
673 ***/
674 RETURN NULL;
675 END Get_Tax_Exempt_Number;
676
677 /*************************************************************/
678 --
679 -- Following function is no more used in defaulting
680 --
681 /*************************************************************/
682
683 FUNCTION Get_Tax_Exempt_Reason
684 ( p_database_object_name IN VARCHAR2
685 ,p_attribute_code IN VARCHAR2)
686 RETURN VARCHAR2
687 IS
688 l_tax_exempt_reason_code VARCHAR2(80);
689 l_line_type_rec OE_Order_Cache.line_type_Rec_Type;
690 /***
691 CURSOR C_Std_Tax_Exemption(X_Ship_To_Org_Id NUMBER,
692 X_Invoice_To_Org_id NUMBER,
693 X_Date_Ordered DATE) is
694 SELECT tax.tax_exempt_reason_code
695 FROM tax_exemptions_qp_v tax,
696 oe_ship_to_orgs_v s,
697 hr_organization_information hr,
698 hz_cust_site_uses site --ra_site_uses (bug fix 2116858)
699 WHERE tax.ship_to_site_use_id = s.site_use_id
700 AND s.organization_id = X_Ship_To_Org_Id
701 AND tax.bill_to_customer_id = hr.org_information1
702 AND hr.organization_id = X_Invoice_To_Org_Id
703 and hr.org_information_context = 'Customer/Supplier Association'
704 and SITE.SITE_USE_ID = TO_NUMBER ( hr.ORG_INFORMATION2 )
705 and SITE.SITE_USE_CODE = 'BILL_TO'
706 AND trunc(NVL(X_Date_Ordered, SYSDATE))
707 between trunc(tax.start_date) and
708 trunc(NVL(tax.end_date, NVL(X_Date_Ordered, SYSDATE)))
709 AND tax.status_code = 'PRIMARY'
710 AND ROWNUM = 1;
711 ***/
712 --
713 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
714 --
715 BEGIN
716 /***
717 IF ONT_HEADER_DEF_HDLR.g_record.ship_to_org_id IS NOT NULL AND
718 ONT_HEADER_DEF_HDLR.g_record.ship_to_org_id <> FND_API.G_MISS_NUM AND
719 ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id IS NOT NULL AND
720 ONT_HEADER_DEF_HDLR.g_record.invoice_to_org_id <> FND_API.G_MISS_NUM AND
721 ONT_HEADER_DEF_HDLR.g_record.ordered_date IS NOT NULL AND
722 ONT_HEADER_DEF_HDLR.g_record.ordered_date <> FND_API.G_MISS_DATE
723 THEN
724
725 OPEN C_Std_Tax_Exemption(ONT_HEADER_DEF_HDLR.g_record.ship_to_org_id,
726 ONT_HEADER_DEF_HDLR.g_record.Invoice_To_Org_id,
727 ONT_HEADER_DEF_HDLR.g_record.ordered_date);
728 FETCH C_Std_Tax_Exemption INTO l_tax_exempt_reason_code;
729 CLOSE C_Std_Tax_Exemption;
730 END IF;
731 RETURN l_tax_exempt_reason_code;
732 EXCEPTION
733 WHEN NO_DATA_FOUND THEN
734 RETURN NULL;
735 ***/
736 RETURN NULL;
737 END Get_Tax_Exempt_Reason;
738
739 -- Added for new view-defination (oe_tax_exemptions_qp_v)
740 -- for Tax Exemption details.
741
742 FUNCTION Get_Tax_Exemption_Details
743 ( p_database_object_name IN VARCHAR2
744 ,p_attribute_code IN VARCHAR2)
745 RETURN VARCHAR2
746 IS
747 l_tax_exempt_flag VARCHAR2(1) := NULL;
748 l_tax_exempt_number VARCHAR2(80) := NULL;
749 l_tax_exempt_reason_code VARCHAR2(80) := NULL;
750 l_line_type_rec OE_Order_Cache.line_type_Rec_Type;
751
752 -- eBTax changes
753 /*
754 CURSOR C_Line_Std_Tax_Exemption(X_Ship_To_Org_Id NUMBER,
755 X_Invoice_To_Org_id NUMBER,
756 X_Sold_To_Org_id NUMBER,
757 X_Tax_Date DATE,
758 X_Tax_Code VARCHAR2) is
759 --* recheck the joins
760 SELECT tax_exempt_number,tax_exempt_reason_code
761 FROM zx_exemptions_v
762 WHERE site_use_id = NVL(X_Ship_To_Org_Id,X_Invoice_To_Org_id)
763 AND cust_account_id = X_Sold_To_Org_Id
764 --* AND tax_code = X_Tax_Code
765 AND trunc(NVL(X_Tax_Date, SYSDATE)) BETWEEN trunc(effective_from) and
766 trunc(NVL(effective_to, NVL(X_Tax_Date, SYSDATE)))
767 AND status_code = 'PRIMARY'
768 AND rownum = 1;
769 */
770
771 l_ship_to_cust_Acct_id hz_cust_Accounts.cust_Account_id%type;
772 l_ship_to_party_id hz_cust_accounts.party_id%type;
773 l_ship_to_party_site_id hz_party_sites.party_site_id%type;
774 l_bill_to_cust_Acct_id hz_cust_Accounts.cust_Account_id%type;
775 l_bill_to_party_id hz_cust_accounts.party_id%type;
776 l_bill_to_party_site_id hz_party_sites.party_site_id%type;
777 l_org_id NUMBER;
778 -- l_legal_entity_id NUMBER;
779
780 cursor partyinfo(p_site_org_id HZ_CUST_SITE_USES_ALL.SITE_USE_ID%type) is
781 SELECT cust_acct.cust_account_id,
782 cust_Acct.party_id,
783 acct_site.party_site_id,
784 site_use.org_id
785 FROM
786 HZ_CUST_SITE_USES_ALL site_use,
787 HZ_CUST_ACCT_SITES_ALL acct_site,
788 HZ_CUST_ACCOUNTS_ALL cust_Acct
789 WHERE site_use.site_use_id = p_site_org_id
790 AND site_use.cust_acct_site_id = acct_site.cust_acct_site_id
791 and acct_site.cust_account_id = cust_acct.cust_account_id;
792
793 --
794 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
795 --
796 BEGIN
797
798 l_line_type_rec := oe_order_cache.load_line_type(ONT_LINE_DEF_HDLR.g_record.line_type_id);
799
800 IF nvl(ONT_LINE_DEF_HDLR.g_record.tax_exempt_flag, 'S') <> 'R' AND
801 l_line_type_rec.calculate_tax_flag = 'N' THEN
802
803 RETURN NULL;
804 END IF;
805
806 -- If defaulting for Header Level Tax exemptions
807 IF p_database_object_name = 'OE_AK_ORDER_HEADERS_V' THEN
808 RETURN NULL;
809
810 -- If defaulting for Line Level Tax exemptions
811
812 ELSIF p_database_object_name = 'OE_AK_ORDER_LINES_V' THEN
813
814 IF ONT_LINE_Def_Hdlr.g_record.tax_exempt_flag = 'S' THEN
815
816 -- bug 5485367 when tax_exempt_flag is 'S', shouldn't have any tax_exempt_number
817 -- without tax_exempt_number, tax_exempt_reason is not necessary either
818 -- so the following defaulting code is no longer used
819 IF l_debug_level > 0 THEN
820 oe_debug_pub.add( 'Do not default tax exempt because flag is standard');
821 END IF;
822 RETURN NULL;
823 -- Check whether the attribute has got a value in privious call to
824 -- the related attribute.
825 -- e.g. when tax exempt number is defaulted, the reason code is also
826 -- retrieved and stored in the global record
827 -- (OE_Line_DEF_HANDLER.g_record). It can used in defaulting the
828 -- tax exempt reason.
829
830 IF p_attribute_code = 'TAX_EXEMPT_REASON' THEN
831 IF ONT_LINE_Def_Hdlr.g_record.tax_exempt_reason_code <> FND_API.G_MISS_CHAR
832 THEN
833 RETURN ONT_LINE_Def_Hdlr.g_record.tax_exempt_reason_code;
834 END IF;
835 ELSIF p_attribute_code = 'TAX_EXEMPT_NUMBER' THEN
836 IF ONT_LINE_Def_Hdlr.g_record.tax_exempt_number <> FND_API.G_MISS_CHAR
837 THEN
838 RETURN ONT_LINE_Def_Hdlr.g_record.tax_exempt_number;
839 END IF;
840 END IF;
841 IF ((ONT_LINE_Def_Hdlr.g_record.ship_to_org_id IS NOT NULL AND
842 ONT_LINE_Def_Hdlr.g_record.ship_to_org_id <> FND_API.G_MISS_NUM) OR
843 (ONT_LINE_Def_Hdlr.g_record.invoice_to_org_id IS NOT NULL AND
844 ONT_LINE_Def_Hdlr.g_record.invoice_to_org_id <> FND_API.G_MISS_NUM)) AND
845 ONT_LINE_Def_Hdlr.g_record.sold_to_org_id IS NOT NULL AND
846 ONT_LINE_Def_Hdlr.g_record.sold_to_org_id <> FND_API.G_MISS_NUM AND
847 ONT_LINE_Def_Hdlr.g_record.tax_date IS NOT NULL AND
848 ONT_LINE_Def_Hdlr.g_record.tax_date <> FND_API.G_MISS_DATE AND
849 ONT_LINE_Def_Hdlr.g_record.tax_code IS NOT NULL AND
850 ONT_LINE_Def_Hdlr.g_record.tax_code <> FND_API.G_MISS_CHAR
851 THEN
852 --bsadri if the cached values are the same do not fetch them
853 IF nvl(ONT_LINE_Def_Hdlr.g_record.ship_to_org_id,FND_API.G_MISS_NUM)
854 <>nvl( OE_Order_Cache.g_TAX_EXEMPTION_CACH.ship_to_org_id,FND_API.G_MISS_NUM)
855 OR nvl(ONT_LINE_Def_Hdlr.g_record.invoice_to_org_id,FND_API.G_MISS_NUM) <>
856 NVL( OE_Order_Cache.g_TAX_EXEMPTION_CACH.invoice_to_org_id,FND_API.G_MISS_NUM)
857 OR NVL(ONT_LINE_Def_Hdlr.g_record.sold_to_org_id,FND_API.G_MISS_NUM) <>
858 nvl(OE_Order_Cache.g_TAX_EXEMPTION_CACH.sold_to_org_id,FND_API.G_MISS_NUM)
859 OR nvl(ONT_LINE_Def_Hdlr.g_record.tax_date,FND_API.G_MISS_DATE) <>
860 nvl(OE_Order_Cache.g_TAX_EXEMPTION_CACH.tax_date,FND_API.G_MISS_DATE)
861 OR nvl(ONT_LINE_Def_Hdlr.g_record.tax_code,FND_API.G_MISS_CHAR) <>
862 nvl(OE_Order_Cache.g_TAX_EXEMPTION_CACH.tax_code ,FND_API.G_MISS_CHAR)
863 THEN --{
864 -- eBTax changes
865 /* OPEN C_Line_Std_Tax_Exemption(ONT_LINE_Def_Hdlr.g_record.ship_to_org_id,
866 ONT_LINE_Def_Hdlr.g_record.Invoice_To_Org_id,
867 ONT_LINE_Def_Hdlr.g_record.sold_To_Org_id,
868 ONT_LINE_Def_Hdlr.g_record.tax_date,
869 ONT_LINE_Def_Hdlr.g_record.tax_code);
870 FETCH C_Line_Std_Tax_Exemption INTO l_tax_exempt_number,
871 l_tax_exempt_reason_code;
872 CLOSE C_Line_Std_Tax_Exemption;
873 */
874
875 open partyinfo(ONT_LINE_Def_Hdlr.g_record.Invoice_To_Org_id);
876 fetch partyinfo into l_bill_to_cust_Acct_id,
877 l_bill_to_party_id,
878 l_bill_to_party_site_id,
879 l_org_id;
880 close partyinfo;
881
882 if ONT_LINE_Def_Hdlr.g_record.ship_to_org_id = ONT_LINE_Def_Hdlr.g_record.Invoice_To_Org_id then
883 l_ship_to_cust_Acct_id := l_bill_to_cust_Acct_id;
884 l_ship_to_party_id := l_bill_to_party_id;
885 l_ship_to_party_site_id := l_bill_to_party_site_id ;
886 else
887 open partyinfo(ONT_LINE_Def_Hdlr.g_record.ship_to_org_id);
888 fetch partyinfo into l_ship_to_cust_Acct_id,
889 l_ship_to_party_id,
890 l_ship_to_party_site_id,
891 l_org_id;
892 close partyinfo;
893 end if;
894
895
896 SELECT EXEMPT_CERTIFICATE_NUMBER,
897 EXEMPT_REASON_CODE
898 INTO l_tax_exempt_number,
899 l_tax_exempt_reason_code
900 FROM ZX_EXEMPTIONS_V
901 WHERE
902 nvl(site_use_id,nvl(ONT_LINE_Def_Hdlr.g_record.ship_to_org_id,
903 ONT_LINE_Def_Hdlr.g_record.invoice_to_org_id))
904 = nvl(ONT_LINE_Def_Hdlr.g_record.ship_to_org_id,
905 ONT_LINE_Def_Hdlr.g_record.Invoice_to_org_id)
906 AND nvl(cust_account_id, l_bill_to_cust_acct_id) = l_bill_to_cust_acct_id
907 AND nvl(PARTY_SITE_ID,nvl(l_ship_to_party_site_id, l_bill_to_party_site_id))=
908 nvl(l_ship_to_party_site_id, l_bill_to_party_site_id)
909 AND org_id = l_org_id
910 AND party_id = l_bill_to_party_id
911 -- AND nvl(LEGAL_ENTITY_ID,-99) IN (nvl(l_legal_entity_id, legal_entity_id), -99)
912 AND EXEMPTION_STATUS_CODE = 'PRIMARY'
913 AND TRUNC(NVL(ONT_LINE_Def_Hdlr.g_record.tax_date,sysdate))
914 BETWEEN TRUNC(EFFECTIVE_FROM)
915 AND TRUNC(NVL(EFFECTIVE_TO,NVL(ONT_LINE_Def_Hdlr.g_record.tax_date,sysdate)))
916 AND ROWNUM = 1;
917
918 -- end eBtax changes
919
920
921 OE_Order_Cache.g_TAX_EXEMPTION_CACH.ship_to_org_id :=
922 ONT_LINE_Def_Hdlr.g_record.ship_to_org_id ;
923 OE_Order_Cache.g_TAX_EXEMPTION_CACH.invoice_to_org_id :=
924 ONT_LINE_Def_Hdlr.g_record.invoice_to_org_id;
925 OE_Order_Cache.g_TAX_EXEMPTION_CACH.sold_to_org_id :=
926 ONT_LINE_Def_Hdlr.g_record.sold_to_org_id ;
927 OE_Order_Cache.g_TAX_EXEMPTION_CACH.tax_date :=
928 ONT_LINE_Def_Hdlr.g_record.tax_date;
929 OE_Order_Cache.g_TAX_EXEMPTION_CACH.tax_code :=
930 ONT_LINE_Def_Hdlr.g_record.tax_code;
931 OE_Order_Cache.g_TAX_EXEMPTION_CACH.tax_exempt_number :=
932 l_tax_exempt_number;
933 OE_Order_Cache.g_TAX_EXEMPTION_CACH.tax_exempt_reason_code :=
934 l_tax_exempt_reason_code;
935
936
937 ELSE --}{
938 --use the cached values
939 l_tax_exempt_number :=
940 OE_Order_Cache.g_TAX_EXEMPTION_CACH.tax_exempt_number;
941 l_tax_exempt_reason_code :=
942 OE_Order_Cache.g_TAX_EXEMPTION_CACH.tax_exempt_reason_code;
943 END IF; --}
944 END IF;
945
946 ELSIF ONT_LINE_Def_Hdlr.g_record.tax_exempt_flag = 'R' THEN
947 RETURN NULL;
948
949 ELSIF ONT_LINE_Def_Hdlr.g_record.tax_exempt_flag = 'E' THEN
950
951 -- Check whether the attribute has got a value in privious call to
952 -- the related attribute.
953 -- e.g. when tax exempt number is defaulted, the reason code is also
954 -- retrieved and stored in the global record
955 -- (OE_Line_DEF_HANDLER.g_record). It can used in defaulting the
956 -- tax exempt reason.
957
958 IF p_attribute_code = 'TAX_EXEMPT_REASON' THEN
959 IF ONT_LINE_Def_Hdlr.g_record.tax_exempt_reason_code <> FND_API.G_MISS_CHAR
960 THEN
961 RETURN ONT_LINE_Def_Hdlr.g_record.tax_exempt_reason_code;
962 END IF;
963 ELSIF p_attribute_code = 'TAX_EXEMPT_NUMBER' THEN
964 IF ONT_LINE_Def_Hdlr.g_record.tax_exempt_number <> FND_API.G_MISS_CHAR
965 THEN
966 RETURN ONT_LINE_Def_Hdlr.g_record.tax_exempt_number;
967 END IF;
968 END IF;
969
970
971 -- Check the Header Record for the Tax Handling
972
973 BEGIN
974 SELECT tax_exempt_flag,
975 tax_exempt_number,
976 tax_exempt_reason_code
977 INTO l_tax_exempt_flag,
978 l_tax_exempt_number,
979 l_tax_exempt_reason_code
980 FROM OE_ORDER_HEADERS_ALL
981 WHERE header_id = ONT_LINE_Def_Hdlr.g_record.header_id;
982 IF l_tax_exempt_flag <> 'E' THEN
983 l_tax_exempt_number := NULL;
984 l_tax_exempt_reason_code := NULL;
985 END IF;
986
987 -- ??? problem here is that if the header level flag is not 'E' then
988 -- the tax exempt reason will remain null. It will fail in the
989 -- validation for the Order Line as the Tax Exempt Reason is a
990 -- required field at line level when the Tax Exempt Flag = 'E'.
991
992 END;
993
994 END IF;
995
996 END IF;
997 -- bug 5184842 only set both when both needs to be defaulted
998 -- otherwise, the return value should take care
999 IF (ONT_LINE_Def_Hdlr.g_record.tax_exempt_reason_code = FND_API.G_MISS_CHAR
1000 AND ONT_LINE_Def_Hdlr.g_record.tax_exempt_number = FND_API.G_MISS_CHAR)
1001 THEN
1002 IF l_debug_level > 0 THEN
1003 oe_debug_pub.add( 'DEFAULTED TAX EXEMPT NUMBER/REASON '||L_TAX_EXEMPT_NUMBER|| '/' || L_TAX_EXEMPT_REASON_CODE ) ;
1004 END IF;
1005
1006 ONT_LINE_Def_Hdlr.g_record.tax_exempt_number := l_tax_exempt_number;
1007 ONT_LINE_Def_Hdlr.g_record.tax_exempt_reason_code := l_tax_exempt_reason_code;
1008 END IF;--bug5184842
1009
1010 IF p_attribute_code = 'TAX_EXEMPT_NUMBER' THEN
1011 IF l_debug_level > 0 THEN
1012 oe_debug_pub.add( 'DEFAULTED TAX EXEMPT NUMBER '||L_TAX_EXEMPT_NUMBER) ;
1013 END IF;
1014
1015 RETURN l_tax_exempt_number;
1016 ELSIF p_attribute_code = 'TAX_EXEMPT_REASON' THEN
1017 IF l_debug_level > 0 THEN
1018 oe_debug_pub.add( 'DEFAULTED TAX EXEMPT REASON '||L_TAX_EXEMPT_REASON_CODE ) ;
1019 END IF;
1020 RETURN l_tax_exempt_reason_code;
1021 END IF;
1022
1023 EXCEPTION
1024 WHEN NO_DATA_FOUND THEN
1025 IF p_database_object_name = 'OE_AK_ORDER_HEADERS_V' THEN
1026 ONT_HEADER_Def_Hdlr.g_record.tax_exempt_number := NULL;
1027 ONT_HEADER_Def_Hdlr.g_record.tax_exempt_reason_code := NULL;
1028 ELSIF p_database_object_name = 'OE_AK_ORDER_LINES_V' THEN
1029 ONT_LINE_Def_Hdlr.g_record.tax_exempt_number := NULL;
1030 ONT_LINE_Def_Hdlr.g_record.tax_exempt_reason_code := NULL;
1031 END IF;
1032 RETURN NULL;
1033
1034 WHEN OTHERS THEN
1035
1036 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1037 THEN
1038 OE_MSG_PUB.Add_Exc_Msg
1039 ( G_PKG_NAME ,
1040 'Get_Tax_Exemption_details'
1041 );
1042 END IF;
1043
1044 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1045 END Get_Tax_Exemption_Details;
1046
1047
1048 -- Get the Set of Books Currency
1049
1050 FUNCTION Get_SOB_currency_Code
1051 ( p_database_object_name IN VARCHAR2
1052 ,p_attribute_code IN VARCHAR2)
1053 RETURN VARCHAR2
1054 IS
1055 X_Currency_Code VARCHAR2(15);
1056 l_set_of_books_rec OE_Order_Cache.Set_Of_Books_Rec_Type;
1057 --
1058 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1059 --
1060 BEGIN
1061
1062 l_set_of_books_rec :=
1063 OE_Order_Cache.Load_Set_Of_Books;
1064 x_Currency_Code := l_set_of_books_rec.Currency_Code;
1065
1066 IF x_Currency_Code = FND_API.G_MISS_CHAR THEN
1067
1068 x_Currency_Code := Null;
1069
1070 END IF;
1071
1072 RETURN x_Currency_Code;
1073
1074 EXCEPTION
1075
1076 WHEN OTHERS THEN
1077
1078 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1079 THEN
1080 OE_MSG_PUB.Add_Exc_Msg
1081 ( G_PKG_NAME ,
1082 'GET_TAX_CODE'
1083 );
1084 END IF;
1085
1086 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1087
1088 END Get_SOB_Currency_Code;
1089
1090
1091 -----------------------------------------------------------------------
1092 -- DEFAULTING FUNCTIONS TO BE USED FOR ATTRIBUTES ON ORDER LINE
1093 -----------------------------------------------------------------------
1094
1095 -- Function to get the Default TAX_CODE
1096
1097 FUNCTION Get_Tax_Code
1098 ( p_database_object_name IN VARCHAR2
1099 ,p_attribute_code IN VARCHAR2)
1100 RETURN VARCHAR2
1101 IS
1102 l_site_org_id NUMBER;
1103 l_org_id NUMBER;
1104 l_Set_Of_Books_Id NUMBER;
1105 l_Ord_Type_Id NUMBER;
1106 x_tax_code VARCHAR2(50);
1107 l_trx_type_id NUMBER;
1108 l_set_of_books_rec OE_Order_Cache.Set_Of_Books_Rec_Type;
1109 l_line_type_rec OE_Order_Cache.line_type_Rec_Type;
1110 l_organization_id NUMBER := -1;
1111 l_cust_trx_type_id NUMBER := 0;
1112 l_calculate_tax_flag VARCHAR2(1) ;
1113
1114 --bug4333881
1115 l_commitment_id NUMBER;
1116
1117 /* Cursor C_tax_calculation_flag is
1118 SELECT tax_calculation_flag
1119 into l_calculate_tax_flag
1120 FROM RA_CUST_TRX_TYPES
1121 WHERE CUST_TRX_TYPE_ID = l_cust_trx_type_id;
1122 */
1123 /* added by Renga for bug 1476390 */
1124 --
1125 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1126 --
1127 BEGIN
1128 IF l_debug_level > 0 THEN
1129 oe_debug_pub.add( 'ENTERING OE_DEFAULT_PVT.GET_TAX_CODE' , 1 ) ;
1130 END IF;
1131
1132 Begin
1133 --bug4333881 start
1134 IF l_debug_level > 0 THEN
1135 oe_debug_pub.add('line_id : ' || ONT_LINE_DEF_HDLR.g_record.line_id);
1136 oe_debug_pub.add('commitment_id : ' || ONT_LINE_DEF_HDLR.g_record.commitment_id);
1137 END IF;
1138
1139 l_commitment_id := ONT_LINE_DEF_HDLR.g_record.commitment_id;
1140
1141 IF l_commitment_id IS NOT NULL AND
1142 l_commitment_id <> FND_API.G_MISS_NUM THEN
1143 BEGIN
1144 SELECT NVL(cust_type.subsequent_trx_type_id,cust_type.cust_trx_type_id)
1145 INTO l_cust_trx_type_id
1146 FROM ra_cust_trx_types cust_type,ra_customer_trx_all cust_trx
1147 WHERE cust_type.cust_trx_type_id = cust_trx.cust_trx_type_id
1148 AND cust_trx.customer_trx_id = l_commitment_id;
1149
1150 IF l_debug_level > 0 THEN
1151 oe_debug_pub.add( 'value of commitment customer trx type id '||l_cust_trx_type_id,1);
1152 END IF;
1153
1154 EXCEPTION
1155 WHEN NO_DATA_FOUND THEN
1156 l_cust_trx_type_id := 0;
1157 WHEN OTHERS THEN
1158 RETURN null;
1159 END;
1160 ELSE
1161 l_cust_trx_type_id := OE_Invoice_PUB.Get_Customer_Transaction_Type(ONT_LINE_DEF_HDLR.g_record);
1162 oe_debug_pub.add( 'value of customer trx type id '||l_cust_trx_type_id,1);
1163 END IF;
1164 --bug4333881 end
1165
1166 --bug 3175277 begin
1167 --There is no need to load line_type cache again because
1168 --OE_Invoice_PUB.Get_Customer_Transaction_Type checks the line_type cache to get cust_trx_type_id
1169 /*if (l_cust_trx_type_id = 0 or l_cust_trx_type_id is null) then
1170 l_line_type_rec := oe_order_cache.load_line_type(ONT_LINE_DEF_HDLR.g_record.line_type_id);
1171 l_cust_trx_type_id := l_line_type_rec.cust_trx_type_id;
1172 oe_debug_pub.add( 'customer trx type id:'||l_cust_trx_type_id,1);
1173 end if; */
1174 --bug 3175277 end
1175
1176 -- performance fix for bug 4200055
1177 IF OE_ORDER_CACHE.g_line_type_rec.cust_trx_type_id = l_cust_trx_type_id THEN
1178 l_calculate_tax_flag := OE_ORDER_CACHE.g_line_type_rec.calculate_tax_flag ;
1179 ELSE
1180 SELECT tax_calculation_flag
1181 into l_calculate_tax_flag
1182 FROM RA_CUST_TRX_TYPES
1183 WHERE CUST_TRX_TYPE_ID = l_cust_trx_type_id;
1184 END IF ;
1185 /*if( l_cust_trx_type_id is not NULL or l_cust_trx_type_id <> 0) then
1186 SELECT tax_calculation_flag
1187 into l_calculate_tax_flag
1188 FROM RA_CUST_TRX_TYPES
1189 WHERE CUST_TRX_TYPE_ID = l_cust_trx_type_id;
1190 end if; */
1191 -- end bug 4200055
1192 --l_line_type_rec := oe_order_cache.load_line_type(ONT_LINE_DEF_HDLR.g_record.line_type_id);
1193
1194 IF l_debug_level > 0 THEN
1195 oe_debug_pub.add('l_calculate_tax_flag : '|| l_calculate_tax_flag);
1196 END IF;
1197
1198 IF nvl(ONT_LINE_DEF_HDLR.g_record.tax_exempt_flag, 'S') <> 'R' AND
1199 l_calculate_tax_flag = 'N' THEN
1200
1201 RETURN NULL;
1202 END IF;
1203
1204 EXCEPTION
1205 WHEN No_Data_Found THEN
1206 oe_debug_pub.add(' in no data found for cust_trx_type_id - tax code ');
1207 RETURN NULL;
1208
1209 WHEN OTHERS THEN
1210 oe_debug_pub.add(' in when others for cust_trx_type_code - tax code' );
1211 End;
1212
1213 IF ONT_LINE_DEF_HDLR.g_record.inventory_item_id IS NOT NULL AND
1214 ONT_LINE_DEF_HDLR.g_record.inventory_item_id <> FND_API.G_MISS_NUM AND
1215 ONT_LINE_DEF_HDLR.g_record.tax_date IS NOT NULL AND
1216 ONT_LINE_DEF_HDLR.g_record.tax_date <> FND_API.G_MISS_DATE
1217 THEN
1218
1219
1220 OE_GLOBALS.Set_Context;
1221
1222 l_org_id := OE_GLOBALS.G_ORG_ID;
1223
1224 /* added by Renga for bug 1476390 */
1225
1226 l_organization_id := OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID', l_org_id);
1227
1228
1229 IF l_debug_level > 0 THEN
1230 oe_debug_pub.add( 'SHIP_TO'||TO_CHAR ( ONT_LINE_DEF_HDLR.G_RECORD.SHIP_TO_ORG_ID ) , 3 ) ;
1231 END IF;
1232 IF l_debug_level > 0 THEN
1233 oe_debug_pub.add( 'BILL_TO'||TO_CHAR ( ONT_LINE_DEF_HDLR.G_RECORD.INVOICE_TO_ORG_ID ) , 3 ) ;
1234 END IF;
1235 IF l_debug_level > 0 THEN
1236 oe_debug_pub.add( 'TAX_DATE'||TO_CHAR ( ONT_LINE_DEF_HDLR.G_RECORD.TAX_DATE ) , 3 ) ;
1237 END IF;
1238 IF l_debug_level > 0 THEN
1239 oe_debug_pub.add( 'SOLD TO'||TO_CHAR ( ONT_LINE_DEF_HDLR.G_RECORD.SOLD_TO_ORG_ID ) , 3 ) ;
1240 END IF;
1241 IF l_debug_level > 0 THEN
1242 oe_debug_pub.add( 'ORG'||TO_CHAR ( L_ORG_ID ) , 3 ) ;
1243 END IF;
1244 IF l_debug_level > 0 THEN
1245 oe_debug_pub.add( 'ITEM VALIDATION ORG'||TO_CHAR ( L_ORGANIZATION_ID ) , 3 ) ;
1246 END IF;
1247 IF l_debug_level > 0 THEN
1248 oe_debug_pub.add( 'ITEM_ID'||TO_CHAR ( ONT_LINE_DEF_HDLR.G_RECORD.INVENTORY_ITEM_ID ) , 3 ) ;
1249 END IF;
1250 IF l_debug_level > 0 THEN
1251 oe_debug_pub.add( 'SHIP_FROM'||TO_CHAR ( ONT_LINE_DEF_HDLR.G_RECORD.SHIP_FROM_ORG_ID ) ) ;
1252 END IF;
1253
1254 IF l_organization_id IS NOT NULL AND
1255 l_organization_id <> FND_API.G_MISS_NUM
1256 THEN
1257
1258 IF l_debug_level > 0 THEN
1259 oe_debug_pub.add( 'REN 1' , 3 ) ;
1260 END IF;
1261 -- Get Set of book ID
1262 l_set_of_books_rec := OE_Order_Cache.Load_Set_Of_Books;
1263 l_Set_Of_Books_Id := l_set_of_books_rec.set_of_books_id;
1264 oe_debug_pub.add('Set of books id:'||l_set_of_books_id , 3 ) ;
1265
1266 IF l_debug_level > 0 THEN
1267 oe_debug_pub.add( 'REN 2' , 3 ) ;
1268 END IF;
1269
1270
1271 --bug 3175277
1272 l_trx_type_id := l_cust_trx_type_id;
1273 oe_debug_pub.add('trx type id:'||l_trx_type_id , 3 ) ;
1274
1275 -- Call the AR's API to get the default tax code
1276
1277 --bsadri caching the values
1278
1279 IF nvl(ONT_LINE_DEF_HDLR.g_record.ship_to_org_id,FND_API.G_MISS_NUM)<>
1280
1281 nvl(OE_Order_Cache.g_TAX_CODE_CACH.ship_to_org_id,FND_API.G_MISS_NUM)
1282 OR nvl(ONT_LINE_DEF_HDLR.g_record.invoice_to_org_id,FND_API.G_MISS_NUM)<>
1283 nvl(OE_Order_Cache.g_TAX_CODE_CACH.invoice_to_org_id,FND_API.G_MISS_NUM)
1284 OR nvl(ONT_LINE_DEF_HDLR.g_record.inventory_item_id,FND_API.G_MISS_NUM) <>
1285 nvl(OE_Order_Cache.g_TAX_CODE_CACH.inventory_item_id,FND_API.G_MISS_NUM)
1286 OR nvl(ONT_LINE_DEF_HDLR.g_record.ship_from_org_id,FND_API.G_MISS_NUM)<>
1287 nvl(OE_Order_Cache.g_TAX_CODE_CACH.ship_from_org_id ,FND_API.G_MISS_NUM)
1288 OR nvl(ONT_LINE_DEF_HDLR.g_record.tax_date,FND_API.G_MISS_DATE) <>
1289 nvl(OE_Order_Cache.g_TAX_CODE_CACH.tax_date,FND_API.G_MISS_DATE)
1290 OR nvl(l_trx_type_id,FND_API.G_MISS_NUM) <>
1291 nvl( OE_Order_Cache.g_TAX_CODE_CACH.trx_type_id,FND_API.G_MISS_NUM)
1292 THEN
1293
1294 IF l_debug_level > 0 THEN
1295 oe_debug_pub.add( 'REN INSIDE TC 1 ' , 3 ) ;
1296 END IF;
1297
1298
1299 zx_ar_tax_classificatn_def_pkg.get_default_tax_classification(
1300 p_ship_to_site_use_id => ONT_LINE_DEF_HDLR.g_record.ship_to_org_id,
1301 p_bill_to_site_use_id => ONT_LINE_DEF_HDLR.g_record.invoice_to_org_id,
1302 p_inventory_item_id => ONT_LINE_DEF_HDLR.g_record.inventory_item_id,
1303 p_organization_id => ONT_LINE_DEF_HDLR.g_record.ship_from_org_id,
1304 p_set_of_books_id => l_Set_Of_Books_Id,
1305 p_trx_date => trunc(ONT_LINE_DEF_HDLR.g_record.tax_date),
1306 p_trx_type_id => l_trx_type_id,
1307 p_tax_classification_code => x_tax_code,
1308 appl_short_name => 'ONT',
1309 p_entity_code => 'OE_ORDER_HEADERS',
1310 p_event_class_code => 'SALES_TRANSACTION_TAX_QUOTE',
1311 p_application_id => 660,
1312 p_internal_organization_id => l_org_id);
1313
1314 OE_Order_Cache.g_TAX_CODE_CACH.ship_to_org_id :=
1315 ONT_LINE_DEF_HDLR.g_record.ship_to_org_id;
1316 OE_Order_Cache.g_TAX_CODE_CACH.invoice_to_org_id :=
1317 ONT_LINE_DEF_HDLR.g_record.invoice_to_org_id;
1318 OE_Order_Cache.g_TAX_CODE_CACH.inventory_item_id :=
1319 ONT_LINE_DEF_HDLR.g_record.inventory_item_id;
1320 OE_Order_Cache.g_TAX_CODE_CACH.ship_from_org_id :=
1321 ONT_LINE_DEF_HDLR.g_record.ship_from_org_id;
1322 OE_Order_Cache.g_TAX_CODE_CACH.tax_date :=
1323 ONT_LINE_DEF_HDLR.g_record.tax_date;
1324 OE_Order_Cache.g_TAX_CODE_CACH.trx_type_id :=
1325 l_trx_type_id;
1326 OE_Order_Cache.g_TAX_CODE_CACH.tax_code := x_tax_code;
1327
1328 ELSE
1329 --use the cached values
1330 x_tax_code := OE_Order_Cache.g_TAX_CODE_CACH.tax_code ;
1331
1332 END IF; /* if cached values are different than passed values */
1333
1334 IF l_debug_level > 0 THEN
1335 oe_debug_pub.add( 'DEFAULTED TAX CODE IS '||X_TAX_CODE ) ;
1336 END IF;
1337 IF (x_tax_code IS NOT NULL) THEN
1338 RETURN x_tax_code;
1339 END IF;
1340
1341 END IF; /* if l_organization_id is not null */
1342
1343 END IF; /* if inventory_item_id and tax_date are not null and not missing */
1344
1345 IF l_debug_level > 0 THEN
1346 oe_debug_pub.add( 'EXITING OE_DEFAULT_PVT.GET_TAX_CODE' , 1 ) ;
1347 END IF;
1348 RETURN NULL;
1349
1350 EXCEPTION
1351
1352 WHEN NO_DATA_FOUND THEN
1353
1354 RETURN NULL;
1355
1356 WHEN OTHERS THEN
1357
1358 IF OE_MSG_PUB.Check_Msg_Level (OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1359 THEN
1360 OE_MSG_PUB.Add_Exc_Msg
1361 ( G_PKG_NAME ,
1362 'GET_TAX_CODE'
1363 );
1364 END IF;
1365
1366 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1367
1368 END Get_Tax_Code;
1369
1370 FUNCTION Get_Commitment_From_Agreement
1371 (p_database_object_name IN VARCHAR2
1372 ,p_attribute_code IN VARCHAR2)
1373 RETURN NUMBER
1374 IS
1375 l_trans_curr_code VARCHAR2(30);
1376 l_Commitment_id NUMBER:= NULL;
1377 l_agreement_id NUMBER := NULL;
1378 l_class VARCHAR2(30);
1379 l_so_source_code VARCHAR2(30);
1380 l_oe_installed_flag VARCHAR2(30);
1381 l_commitment_bal NUMBER;
1382
1383
1384 --
1385 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1386 --
1387 BEGIN
1388
1389 IF l_debug_level > 0 THEN
1390 oe_debug_pub.add( 'ENTERING OE_DEFAULT_PVT.GET_COMMITMENT_FROM_AGREEMENT' , 1 ) ;
1391 END IF;
1392
1393 IF ONT_LINE_Def_Hdlr.g_record.Agreement_id IS NOT NULL THEN
1394 SELECT transactional_curr_code
1395 INTO l_trans_curr_code
1396 FROM oe_order_headers
1397 WHERE header_id = ONT_LINE_Def_Hdlr.g_record.header_id;
1398
1399 IF l_debug_level > 0 THEN
1400 oe_debug_pub.add( 'AGREEMENT_ID: '||ONT_LINE_DEF_HDLR.G_RECORD.AGREEMENT_ID|| ' L_TRANS_CURR_CODE: '||L_TRANS_CURR_CODE ||' SOLD TO ORG: '||ONT_LINE_DEF_HDLR.G_RECORD.SOLD_TO_ORG_ID ) ;
1401 END IF;
1402
1403 --Bug 3247840 fix query for better performance
1404 SELECT /* MOAC_SQL_CHANGE */ customer_trx_id
1405 INTO l_Commitment_id
1406 FROM ra_customer_trx_all ratrx,
1407 ra_cust_trx_types ractt
1408 WHERE ractt.type in ('DEP','GUAR')
1409 AND ratrx.cust_trx_type_id = ractt.cust_trx_type_id
1410 AND ratrx.org_id = ractt.org_id
1411 AND ratrx.bill_to_customer_id = ONT_LINE_Def_Hdlr.g_record.sold_to_org_id
1412 AND ratrx.invoice_currency_code = l_trans_curr_code
1413 AND TRUNC(sysdate) BETWEEN TRUNC(
1414 NVL(ratrx.start_date_commitment, sysdate))
1415 AND TRUNC(NVL( ratrx.end_date_commitment, sysdate ))
1416 AND ratrx.agreement_id = ONT_LINE_Def_Hdlr.g_record.Agreement_id
1417 /* Peformance changes for sql id 14882692 */
1418 AND ratrx.complete_flag = 'Y'
1419
1420 UNION ALL /* Peformance changes for sql id 14882692 */
1421
1422 SELECT /* MOAC_SQL_CHANGE */ customer_trx_id
1423 --INTO l_Commitment_id
1424 FROM ra_customer_trx_all ratrx,
1425 ra_cust_trx_types ractt
1426 WHERE ractt.type in ('DEP','GUAR')
1427 AND ratrx.cust_trx_type_id = ractt.cust_trx_type_id
1428 AND ratrx.org_id = ractt.org_id
1429 AND ratrx.bill_to_customer_id IN (
1430 SELECT cust_account_id
1431 FROM hz_cust_acct_relate
1432 WHERE related_cust_account_id = ONT_LINE_Def_Hdlr.g_record.sold_to_org_id
1433 AND cust_account_id <> ONT_LINE_Def_Hdlr.g_record.sold_to_org_id
1434 AND status = 'A'
1435 AND bill_to_flag = 'Y')
1436 AND ratrx.invoice_currency_code = l_trans_curr_code
1437 AND TRUNC(sysdate) BETWEEN TRUNC(
1438 NVL(ratrx.start_date_commitment, sysdate))
1439 AND TRUNC(NVL( ratrx.end_date_commitment, sysdate ))
1440 AND ratrx.agreement_id = ONT_LINE_Def_Hdlr.g_record.Agreement_id
1441 AND ratrx.complete_flag = 'Y';
1442
1443 -- bug 2270925, to validate the commitment balance is greater than zero
1444 -- before defaulting it from agreement.
1445 l_class := NULL;
1446 l_so_source_code := FND_PROFILE.VALUE('ONT_SOURCE_CODE');
1447 l_oe_installed_flag := 'I';
1448
1449 l_commitment_bal := ARP_BAL_UTIL.GET_COMMITMENT_BALANCE(
1450 l_commitment_id
1451 ,l_class
1452 ,l_so_source_code
1453 ,l_oe_installed_flag );
1454
1455 IF l_debug_level > 0 THEN
1456 oe_debug_pub.add( 'OEXVDEFB: COMMITMENT BALANCE IS: '||L_COMMITMENT_BAL ) ;
1457 END IF;
1458
1459 IF l_commitment_bal > 0 THEN
1460 IF l_debug_level > 0 THEN
1461 oe_debug_pub.add( 'AGREEMENT IS NOT NULL - RETURNING COMMITMENT: '||L_COMMITMENT_ID ) ;
1462 END IF;
1463 RETURN l_Commitment_id;
1464 ELSE
1465 IF l_debug_level > 0 THEN
1466 oe_debug_pub.add( 'COMMITMENT BALANCE IS ZERO - RETURNING NULL COMMITMENT' ) ;
1467 END IF;
1468 RETURN NULL;
1469 END IF;
1470
1471 ELSE
1472 IF l_debug_level > 0 THEN
1473 oe_debug_pub.add( 'AGREEMENT IS NULL ON LINE - RETURNING NULL COMMITMENT' ) ;
1474 END IF;
1475 RETURN NULL;
1476 END IF;
1477
1478 IF l_debug_level > 0 THEN
1479 oe_debug_pub.add( 'EXITING OE_DEFAULT_PVT.GET_COMMITMENT_FROM_AGREEMENT' , 1 ) ;
1480 END IF;
1481 EXCEPTION
1482 WHEN NO_DATA_FOUND THEN
1483 IF l_debug_level > 0 THEN
1484 oe_debug_pub.add( 'IN WHEN NO DATA FOUND - RETURNING NULL COMMITMENT' ) ;
1485 END IF;
1486 RETURN NULL;
1487 WHEN TOO_MANY_ROWS THEN
1488 IF l_debug_level > 0 THEN
1489 oe_debug_pub.add( 'IN WHEN TOO MANY ROWS - RETURNING NULL COMMITMENT' ) ;
1490 END IF;
1491 RETURN NULL;
1492 END Get_Commitment_From_Agreement;
1493
1494 FUNCTION Get_Accounting_Rule_Duration
1495 (p_database_object_name IN VARCHAR2
1496 ,p_attribute_code IN VARCHAR2)
1497 RETURN NUMBER IS
1498 l_accounting_rule_duration NUMBER;
1499 l_rule_type VARCHAR2(10);
1500 --
1501 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1502 --
1503 BEGIN
1504 IF l_debug_level > 0 THEN
1505 oe_debug_pub.add( 'ENTERING OE_DEFAULT_PVT. GET_ACCOUNTING_RULE_DURATION' , 1 ) ;
1506 END IF;
1507 IF OE_CODE_CONTROL.Get_Code_Release_Level < '110509' THEN
1508 IF l_debug_level > 0 THEN
1509 oe_debug_pub.add( 'OEXVDEFB: BELOW PACK I , DO NOT DEFAULT ACCOUNTING DURATION' ) ;
1510 END IF;
1511 RETURN NULL;
1512 END IF;
1513
1514 IF l_debug_level > 0 THEN
1515 oe_debug_pub.add( 'ITEM_TYPE_CODE:'||ONT_LINE_DEF_HDLR.G_RECORD.ITEM_TYPE_CODE||':ACCOUNTING_RULE_ID:'||ONT_LINE_DEF_HDLR.G_RECORD.ACCOUNTING_RULE_ID ) ;
1516 END IF;
1517 IF (ONT_LINE_Def_Hdlr.g_record.item_type_code <> 'SERVICE') AND ONT_LINE_Def_Hdlr.g_record.Accounting_Rule_ID IS NOT NULL THEN
1518 SELECT type
1519 INTO l_rule_type
1520 FROM ra_rules
1521 WHERE rule_id= ONT_LINE_Def_Hdlr.g_record.Accounting_Rule_ID;
1522 IF l_debug_level > 0 THEN
1523 oe_debug_pub.add( 'RULE_TYPE IS:'||L_RULE_TYPE ) ;
1524 END IF;
1525 IF l_rule_type = 'ACC_DUR' THEN
1526 SELECT accounting_rule_duration
1527 INTO l_accounting_rule_duration
1528 FROM oe_order_headers
1529 WHERE header_id = ONT_LINE_Def_Hdlr.g_record.header_id;
1530 IF l_debug_level > 0 THEN
1531 oe_debug_pub.add( 'EXITING OE_DEFAULT_PVT.GET_ACCOUNTING_RULE_DURATION - DURATION:'|| L_ACCOUNTING_RULE_DURATION ) ;
1532 END IF;
1533 RETURN l_accounting_rule_duration;
1534 END IF;
1535 END IF;
1536 RETURN NULL;
1537 IF l_debug_level > 0 THEN
1538 oe_debug_pub.add( 'EXITING OE_DEFAULT_PVT.GET_ACCOUNTING_RULE_DURATION' ) ;
1539 END IF;
1540 EXCEPTION
1541 WHEN OTHERS THEN
1542 IF l_debug_level > 0 THEN
1543 oe_debug_pub.add( 'IN WHEN OTHERS - RETURNING NULL ACCOUNTING RULE DURATION' ) ;
1544 END IF;
1545 RETURN NULL;
1546 END Get_Accounting_Rule_Duration;
1547
1548 -- QUOTING changes
1549 -- Returns ID of the primary location with site use
1550 -- of 'SOLD_TO'
1551 FUNCTION Get_Primary_Customer_Location
1552 ( p_database_object_name IN VARCHAR2
1553 ,p_attribute_code IN VARCHAR2)
1554 RETURN VARCHAR2
1555 IS
1556 l_sold_to_org_id NUMBER;
1557 l_site_use_id NUMBER;
1558 --
1559 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1560 --
1561 BEGIN
1562 l_sold_to_org_id := ONT_HEADER_DEF_HDLR.g_record.SOLD_TO_ORG_ID;
1563 IF l_debug_level > 0 THEN
1564 oe_debug_pub.add('ENTER Get_Primary_Customer_Location');
1565 oe_debug_pub.add('Sold To Org ID :'||l_sold_to_org_id);
1566 END IF;
1567
1568 IF l_sold_to_org_id is not null
1569 AND l_sold_to_org_id <> fnd_api.g_miss_num
1570 THEN
1571
1572 BEGIN
1573
1574 SELECT /* MOAC_SQL_CHANGE */ SITE.SITE_USE_ID
1575 INTO l_site_use_id
1576 FROM HZ_CUST_ACCT_SITES ADDR
1577 ,HZ_CUST_SITE_USES_ALL SITE
1578 WHERE ADDR.CUST_ACCOUNT_ID = l_sold_to_org_id
1579 AND ADDR.STATUS = 'A'
1580 AND SITE.CUST_ACCT_SITE_ID = ADDR.CUST_ACCT_SITE_ID
1581 AND ADDR.ORG_ID = SITE.ORG_ID
1582 AND SITE.SITE_USE_CODE = 'SOLD_TO'
1583 AND SITE.PRIMARY_FLAG = 'Y'
1584 AND SITE.STATUS = 'A'
1585 AND ROWNUM = 1;
1586
1587 RETURN l_site_use_id;
1588
1589 EXCEPTION
1590 -- Return null if there is no primary sold to site
1591 WHEN NO_DATA_FOUND THEN
1592 RETURN NULL;
1593 END;
1594
1595 -- Return null if there is no customer
1596 ELSE
1597
1598 RETURN NULL;
1599
1600 END IF;
1601
1602 EXCEPTION
1603 WHEN OTHERS THEN
1604 IF l_debug_level > 0 THEN
1605 oe_debug_pub.add('Error in Get_Primary_Customer_Location') ;
1606 oe_debug_pub.add('Error :'||substr(sqlerrm,1,200)) ;
1607 END IF;
1608 RETURN NULL;
1609 END Get_Primary_Customer_Location;
1610
1611 FUNCTION Get_Receipt_Method
1612 ( p_database_object_name IN VARCHAR2
1613 ,p_attribute_code IN VARCHAR2)
1614 RETURN NUMBER
1615 IS
1616 l_header_rec OE_ORDER_PUB.Header_rec_Type;
1617 l_pay_method_id NUMBER;
1618 l_payment_type_code VARCHAR2(30);
1619 l_org_id NUMBER ;
1620 --
1621 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1622 --
1623 l_header_id number;
1624 BEGIN
1625 l_org_id:= ONT_HEADER_DEF_HDLR.g_record.org_id;
1626 IF l_debug_level > 0 THEN
1627 oe_debug_pub.add('Entering Get_Receipt_Method');
1628 END IF;
1629
1630 IF p_database_object_name = 'OE_AK_HEADER_PAYMENTS_V' THEN
1631 l_payment_type_code := ONT_HEADER_PAYMENT_DEF_HDLR.g_record.payment_type_code;
1632 l_header_id := ONT_HEADER_PAYMENT_DEF_HDLR.g_record.header_id;
1633
1634 IF l_debug_level > 0 THEN
1635 oe_debug_pub.add('payment_type_code from header payments'||l_payment_type_code);
1636 END IF;
1637 ELSIF p_database_object_name = 'OE_AK_LINE_PAYMENTS_V' THEN
1638 l_payment_type_code := ONT_LINE_PAYMENT_DEF_HDLR.g_record.payment_type_code;
1639 l_header_id := ONT_LINE_PAYMENT_DEF_HDLR.g_record.header_id;
1640
1641 IF l_debug_level > 0 THEN
1642 oe_debug_pub.add('payment_type_code from line payments'||l_payment_type_code ||':org_id:'||l_org_id);
1643 END IF;
1644 ELSIF p_database_object_name = 'OE_AK_ORDER_HEADERS_V' THEN
1645 l_header_id := ONT_HEADER_DEF_HDLR.g_record.header_id;
1646 END IF;
1647
1648 IF l_payment_type_code IS NOT NULL AND
1649 l_payment_type_code <> FND_API.G_MISS_CHAR AND
1650 l_payment_type_code <> 'COMMITMENT' THEN /* Bug #3536642 */
1651
1652 IF l_org_id IS NULL or l_org_id = FND_API.G_MISS_NUM THEN
1653 select org_id
1654 into l_org_id
1655 from oe_order_headers_all
1656 where header_id = l_header_id;
1657 IF l_debug_level > 0 THEN
1658 oe_debug_pub.add('org_id:'||l_org_id);
1659 END IF;
1660 END IF;
1661
1662 select receipt_method_id
1663 into l_pay_method_id
1664 from oe_payment_types_all
1665 where payment_type_code = l_payment_type_code
1666 AND nvl(org_id, -99) = nvl(l_org_id, -99);
1667
1668 IF l_debug_level > 0 THEN
1669 oe_debug_pub.add('l_pay_method_id from payment type:'||l_pay_method_id);
1670 END IF;
1671 IF l_pay_method_id IS NOT NULL THEN
1672 RETURN l_pay_method_id;
1673 END IF;
1674
1675 IF l_payment_type_code = 'CREDIT_CARD' OR
1676 l_payment_type_code = 'ACH' OR
1677 l_payment_type_code = 'DIRECT_DEBIT' THEN -- bug 8771134
1678
1679 l_header_rec := OE_HEADER_UTIL.Query_Row(l_header_id);
1680
1681 IF l_debug_level > 0 THEN
1682 oe_debug_pub.add('Sold To Org ID :'||l_header_rec.sold_to_org_id);
1683 oe_debug_pub.add('Invoice To Org ID :'||l_header_rec.invoice_to_org_id);
1684 oe_debug_pub.add('Calling OE_Verify_Payment_PUB.Get_Primary_Pay_Method');
1685 END IF;
1686
1687 l_pay_method_id := OE_Verify_Payment_PUB.Get_Primary_Pay_Method
1688 ( p_header_rec => l_header_rec ) ;
1689
1690 IF l_debug_level > 0 THEN
1691 oe_debug_pub.add( 'After Getting primary payment method'||l_pay_method_id , 5 ) ;
1692 END IF;
1693
1694 RETURN l_pay_method_id;
1695 ELSE
1696 RETURN l_pay_method_id;
1697 END IF;
1698 END IF;
1699 RETURN NULL;
1700
1701 EXCEPTION
1702 WHEN OTHERS THEN
1703 IF l_debug_level > 0 THEN
1704 oe_debug_pub.add('Error in Get_Receipt_method') ;
1705 oe_debug_pub.add('Error :'||substr(sqlerrm,1,200)) ;
1706 END IF;
1707 RETURN NULL;
1708 END Get_Receipt_Method;
1709
1710 -- Bug 3581592
1711 -- Default deliver to via the API instead of Related Record rule as there
1712 -- is a performance issue with oe_ak_sold_to_orgs_v if primary deliver to
1713 -- is fetched via the view.
1714 FUNCTION Get_Primary_Deliver_To
1715 ( p_database_object_name IN VARCHAR2
1716 ,p_attribute_code IN VARCHAR2)
1717 RETURN VARCHAR2 IS
1718 l_sold_to_org_id NUMBER;
1719 l_site_use_id NUMBER;
1720 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1721 BEGIN
1722
1723 IF l_debug_level > 0 THEN
1724 oe_debug_pub.add('Enter Get_Primary_Deliver_To');
1725 END IF;
1726
1727 IF p_database_object_name = 'OE_AK_ORDER_HEADERS_V' THEN
1728 l_sold_to_org_id := ONT_HEADER_DEF_HDLR.g_record.sold_to_org_id;
1729 ELSIF p_database_object_name = 'OE_AK_ORDER_LINES_V' THEN
1730 l_sold_to_org_id := ONT_LINE_DEF_HDLR.g_record.sold_to_org_id;
1731 ELSE
1732 IF l_debug_level > 0 THEN
1733 oe_debug_pub.add('Invalid DB object :'||p_database_object_name);
1734 END IF;
1735 RETURN NULL;
1736 END IF;
1737
1738 IF l_sold_to_org_id is not null
1739 AND l_sold_to_org_id <> fnd_api.g_miss_num
1740 THEN
1741
1742 BEGIN
1743
1744 SELECT /* MOAC_SQL_CHANGE */ SITE.SITE_USE_ID
1745 INTO l_site_use_id
1746 FROM HZ_CUST_ACCT_SITES ADDR
1747 ,HZ_CUST_SITE_USES_ALL SITE
1748 WHERE ADDR.CUST_ACCOUNT_ID = l_sold_to_org_id
1749 AND ADDR.STATUS = 'A'
1750 AND SITE.CUST_ACCT_SITE_ID = ADDR.CUST_ACCT_SITE_ID
1751 AND SITE.ORG_ID = ADDR.ORG_ID
1752 AND SITE.SITE_USE_CODE = 'DELIVER_TO'
1753 AND SITE.PRIMARY_FLAG = 'Y'
1754 AND SITE.STATUS = 'A'
1755 AND ROWNUM = 1;
1756
1757 RETURN l_site_use_id;
1758
1759 EXCEPTION
1760 -- Return null if there is no primary deliver to site
1761 WHEN NO_DATA_FOUND THEN
1762 RETURN NULL;
1763 END;
1764
1765 -- Return null if there is no customer
1766 ELSE
1767
1768 RETURN NULL;
1769
1770 END IF;
1771
1772 END Get_Primary_Deliver_To;
1773
1774 --bug 13088960 start
1775 FUNCTION Get_Latest_Acceptable_Date(
1776 P_Database_Object_Name In Varchar2,
1777 P_Attribute_Code In Varchar2
1778 )
1779 RETURN DATE
1780 IS
1781 l_latest_acceptable_date DATE;
1782 --
1783 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1784 --
1785 BEGIN
1786
1787 OE_ORDER_CACHE.Load_Order_Header(Ont_Line_Def_Hdlr.G_Record.header_id);
1788 l_latest_acceptable_date := Ont_Line_Def_Hdlr.G_Record.request_date
1789 + OE_ORDER_CACHE.g_header_rec.latest_schedule_limit ;
1790
1791 RETURN l_latest_acceptable_date;
1792
1793 EXCEPTION
1794 WHEN OTHERS THEN
1795 l_latest_acceptable_date := null;
1796 RETURN l_latest_acceptable_date;
1797 END Get_Latest_Acceptable_Date;
1798
1799 --end bug 13088960
1800
1801 END OE_Default_Pvt;
[Home] [Help]