[Home] [Help]
PACKAGE BODY: APPS.AP_APXVDMVD_PKG
Source
1 PACKAGE BODY AP_APXVDMVD_PKG AS
2 /* $Header: apvdmvdb.pls 120.14 2012/02/07 21:43:44 lkarna ship $ */
3 --
4 --
5 PROCEDURE INITIALIZE (
6 x_user_defined_vendor_num_code in out NOCOPY varchar2,
7 x_manual_vendor_num_type in out NOCOPY varchar2,
8 x_rfq_only_site_flag in out NOCOPY varchar2,
9 x_ship_to_location_id in out NOCOPY number,
10 x_ship_to_location_code in out NOCOPY varchar2,
11 x_bill_to_location_id in out NOCOPY number,
12 x_bill_to_location_code in out NOCOPY varchar2,
13 x_fob_lookup_code in out NOCOPY varchar2,
14 x_freight_terms_lookup_code in out NOCOPY varchar2,
15 x_terms_id in out NOCOPY number,
16 x_terms_disp in out NOCOPY varchar2,
17 x_always_take_disc_flag in out NOCOPY varchar2,
18 x_invoice_currency_code in out NOCOPY varchar2,
19 x_org_id in out NOCOPY number,
20 x_set_of_books_id in out NOCOPY number,
21 x_short_name in out NOCOPY varchar2,
22 x_payment_currency_code in out NOCOPY varchar2,
23 x_accts_pay_ccid in out NOCOPY number,
24 x_future_dated_payment_ccid in out NOCOPY number,
25 x_prepay_code_combination_id in out NOCOPY number,
26 x_vendor_pay_group_lookup_code in out NOCOPY varchar2,
27 x_sys_auto_calc_int_flag in out NOCOPY varchar2,
28 x_terms_date_basis in out NOCOPY varchar2,
29 x_terms_date_basis_disp in out NOCOPY varchar2,
30 x_chart_of_accounts_id in out NOCOPY number,
31 x_fob_lookup_disp in out NOCOPY varchar2,
32 x_freight_terms_lookup_disp in out NOCOPY varchar2,
33 x_vendor_pay_group_disp in out NOCOPY varchar2,
34 x_fin_require_matching in out NOCOPY varchar2,
35 x_sys_require_matching in out NOCOPY varchar2,
36 x_fin_match_option in out NOCOPY varchar2,
37 x_po_create_dm_flag in out NOCOPY varchar2,
38 x_exclusive_payment in out NOCOPY varchar2,
39 x_vendor_auto_int_default in out NOCOPY varchar2,
40 x_inventory_organization_id in out NOCOPY number,
41 x_ship_via_lookup_code in out NOCOPY varchar2,
42 x_ship_via_disp in out NOCOPY varchar2,
43 x_sysdate in out NOCOPY date,
44 x_enforce_ship_to_loc_code in out NOCOPY varchar2,
45 x_receiving_routing_id in out NOCOPY number,
46 x_qty_rcv_tolerance in out NOCOPY number,
47 x_qty_rcv_exception_code in out NOCOPY varchar2,
48 x_days_early_receipt_allowed in out NOCOPY number,
49 x_days_late_receipt_allowed in out NOCOPY number,
50 x_allow_sub_receipts_flag in out NOCOPY varchar2,
51 x_allow_unord_receipts_flag in out NOCOPY varchar2,
52 x_receipt_days_exception_code in out NOCOPY varchar2,
53 x_enforce_ship_to_loc_disp in out NOCOPY varchar2,
54 x_qty_rcv_exception_disp in out NOCOPY varchar2,
55 x_receipt_days_exception_disp in out NOCOPY varchar2,
56 x_receipt_required_flag in out NOCOPY varchar2,
57 x_inspection_required_flag in out NOCOPY varchar2,
58 x_payment_method_lookup_code in out NOCOPY varchar2,
59 x_payment_method_disp in out NOCOPY varchar2,
60 x_pay_date_basis_lookup_code in out NOCOPY varchar2,
61 x_pay_date_basis_disp in out NOCOPY varchar2,
62 x_receiving_routing_name in out NOCOPY varchar2,
63 x_AP_inst_flag in out NOCOPY varchar2,
64 x_PO_inst_flag in out NOCOPY varchar2,
65 x_home_country_code in out NOCOPY varchar2,
66 x_default_country_code in out NOCOPY varchar2,
67 x_default_country_disp in out NOCOPY varchar2,
68 x_default_awt_group_id in out NOCOPY number,
69 x_default_awt_group_name in out NOCOPY varchar2,
70 x_allow_awt_flag in out NOCOPY varchar2,
71 x_create_awt_dists_type in out NOCOPY varchar2, --bug 13092500
72 x_base_currency_code in out NOCOPY varchar2,
73 x_address_style in out NOCOPY varchar2,
74 /* eTax Uptake
75 x_auto_tax_calc_flag in out NOCOPY varchar2,
76 x_auto_tax_calc_override in out NOCOPY varchar2,
77 x_amount_includes_tax_flag in out NOCOPY varchar2,
78 x_amount_includes_tax_override in out NOCOPY varchar2,
79 x_ap_tax_rounding_rule in out NOCOPY varchar2,
80 x_vat_code in out NOCOPY varchar2, */
81 x_use_bank_charge_flag in out NOCOPY varchar2,
82 x_bank_charge_bearer in out NOCOPY varchar2,
83 x_employee_id in out NOCOPY varchar2, --bug 12835899
84 X_calling_sequence in varchar2 ) is
85
86 --
87 l_appl_short_name varchar2(30);
88 l_ap_status varchar2(30);
89 l_po_status varchar2(30);
90 l_industry varchar2(30);
91 l_oracle_schema varchar2(30);
92 dummy boolean;
93 l_po_setup number;
94 --
95 -- inactive dates
96 --
97 l_ship_to_loc_inactive_date date;
98 l_bill_to_loc_inactive_date date;
99 l_fob_inactive_date date;
100 l_freight_terms_inactive_date date;
101 l_terms_inactive_date date;
102 l_payment_method_inactive_date date;
103 l_ship_via_inactive_date date;
104 --
105 --
106 current_calling_sequence varchar2(2000);
107 debug_info varchar2(100);
108
109 -- Bug 5087698
110 --
111 l_ap_options number;
112 l_fin_options number;
113 --
114 -- Load initial defaults into WORLD block using several
115 -- select statements
116 --
117 begin
118
119 -- Update the calling sequence
120 --
121 current_calling_sequence := 'AP_APXVDMVD_PKG.INITIALIZE<-' ||
122 X_calling_sequence;
123 --
124 --
125 dummy := fnd_installation.get_app_info('AP',l_ap_status,l_industry,l_oracle_schema);
126 x_AP_inst_flag := l_ap_status;
127 dummy := fnd_installation.get_app_info('PO',l_po_status,l_industry,l_oracle_schema);
128 x_PO_inst_flag := l_po_status;
129 --
130 --
131 debug_info := 'Select from ap_lookup_codes, po_lookup_codes, hr_locations, ....';
132 --Bug :2809214 MOAC - Supplier Attribute Change Project
133 --Changed the source table from financial_options and system_options to
134 --ap_product_setup for some of columns. For list of columns please refer to bug.
135
136 --
137 -- Bug 5087698
138
139 SELECT count(*)
140 INTO l_ap_options
141 FROM ap_system_parameters
142 WHERE nvl(org_id,-99) = nvl(x_org_id,-99);
143
144 SELECT count(*)
145 INTO l_fin_options
146 FROM financials_system_parameters
147 WHERE nvl(org_id,-99) = nvl(x_org_id,-99);
148
149 IF l_ap_options > 0 and l_fin_options > 0 THEN
150
151 SELECT aps.supplier_numbering_method,
152 aps.supplier_num_type,
153 fin.rfq_only_site_flag,
154 fin.ship_to_location_id, -- ship_to_location_id
155 hl2.location_code, -- ship_to_location_code
156 nvl(hl2.inactive_date,sysdate+1), -- ship to inactive date
157 fin.bill_to_location_id, -- bill_to_location_id
158 hl1.location_code, -- bill_to_location_code
159 nvl(hl1.inactive_date,sysdate+1), -- bill_to_location inatcive date
160 fin.fob_lookup_code, -- fob_lookup_code
161 pc1.displayed_field, -- fob_lookup_disp
162 nvl(pc1.inactive_date,sysdate+1), -- fob inactive_date
163 fin.freight_terms_lookup_code, -- freight_terms_lookup_code
164 pc2.displayed_field, -- freight_terms_lookup_disp
165 nvl(pc2.inactive_date,sysdate+1), -- freight_terms inactive_date
166 aps.terms_id, -- terms_id
167 tm.name, -- terms_name
168 nvl(tm.end_date_active,sysdate+1), -- terms_inactive_date
169 aps.payment_method_lookup_code, -- payment_method_lookup_code
170 lc1.displayed_field, -- payment_method_disp
171 nvl(lc1.inactive_date,sysdate+1), -- payment_method inactve date
172 aps.always_take_disc_flag,
173 aps.pay_date_basis_lookup_code, -- pay_date_basis_lookup_code
174 lc2.displayed_field, -- pay_date_basis_disp
175 -- Invoice Currency
176 -- In R12, with the MOAC project the invoice currency was moved
177 -- product setup level. But payment currency was not thought
178 -- about properly. So modified the code such that
179 -- the defaulting to supplier will be the ledger currency
180 -- otherwise it will be derived from the invoice currency in the
181 -- product setup.
182 nvl(ap.base_currency_code,aps.invoice_currency_code),
183 fin.org_id,
184 fin.set_of_books_id,
185 gl.short_name,
186 -- Invoice Currency
187 -- In R12, with the MOAC project the invoice currency was moved
188 -- product setup level. But payment currency was not thought
189 -- about properly. So modified the code such that
190 -- the defaulting to supplier will be the ledger currency
191 -- otherwise it will be derived from the invoice currency in the
192 -- product setup.
193 nvl(ap.base_currency_code,aps.invoice_currency_code),
194 fin.accts_pay_code_combination_id,
195 fin.future_dated_payment_ccid,
196 fin.prepay_code_combination_id,
197 aps.supplier_pay_group_lookup_code,
198 pc3.lookup_code, --2122951 changed to lookup_code
199 aps.auto_calculate_interest_flag,
200 -- Bug 1492237 Get terms_date_basis from ap insead of fin
201 aps.terms_date_basis, -- terms_date_basis
202 lc3.displayed_field, -- terms_date_basis_disp
203 gl.chart_of_accounts_id,
204 aps.hold_unmatched_invoices_flag,
205 ap.hold_unmatched_invoices_flag,
206 fin.match_option,
207 fin.exclusive_payment_flag,
208 ap.vendor_auto_int_default,
209 fin.inventory_organization_id,
210 fin.ship_via_lookup_code, -- ship_via_lookup_code
211 ofr.description, -- ship_via_disp
212 nvl(ofr.disable_date,sysdate+1), -- ship_via inactive date
213 sysdate,
214 ap.base_currency_code,
215 fin.vat_country_code,
216 ap.default_awt_group_id,
217 awt.name,
218 nvl(ap.allow_awt_flag, 'N'),
219 ap.create_awt_dists_type, --bug 13092500
220 ap.use_bank_charge_flag, --5007989
221 nvl(ap.bank_charge_bearer, 'I') --5007989
222 INTO x_user_defined_vendor_num_code,
223 x_manual_vendor_num_type,
224 x_rfq_only_site_flag,
225 x_ship_to_location_id,
226 x_ship_to_location_code,
227 l_ship_to_loc_inactive_date,
228 x_bill_to_location_id,
229 x_bill_to_location_code,
230 l_bill_to_loc_inactive_date,
231 x_fob_lookup_code,
232 x_fob_lookup_disp,
233 l_fob_inactive_date,
234 x_freight_terms_lookup_code,
235 x_freight_terms_lookup_disp,
236 l_freight_terms_inactive_date,
237 x_terms_id,
238 x_terms_disp,
239 l_terms_inactive_date,
240 x_payment_method_lookup_code,
241 x_payment_method_disp,
242 l_payment_method_inactive_date,
243 x_always_take_disc_flag,
244 x_pay_date_basis_lookup_code,
245 x_pay_date_basis_disp,
246 x_invoice_currency_code,
247 x_org_id,
248 x_set_of_books_id,
249 x_short_name,
250 x_payment_currency_code,
251 x_accts_pay_ccid,
252 x_future_dated_payment_ccid,
253 x_prepay_code_combination_id,
254 x_vendor_pay_group_lookup_code,
255 x_vendor_pay_group_disp,
256 x_sys_auto_calc_int_flag,
257 x_terms_date_basis,
258 x_terms_date_basis_disp,
259 x_chart_of_accounts_id,
260 x_fin_require_matching,
261 x_sys_require_matching,
262 x_fin_match_option,
263 x_exclusive_payment,
264 x_vendor_auto_int_default,
265 x_inventory_organization_id,
266 x_ship_via_lookup_code,
267 x_ship_via_disp,
268 l_ship_via_inactive_date,
269 x_sysdate,
270 x_base_currency_code,
271 x_home_country_code,
272 x_default_awt_group_id,
273 x_default_awt_group_name,
274 x_allow_awt_flag,
275 x_create_awt_dists_type, --bug 13092500
276 x_use_bank_charge_flag,
277 x_bank_charge_bearer
278 FROM ap_lookup_codes lc1,
279 ap_lookup_codes lc2,
280 ap_lookup_codes lc3,
281 po_lookup_codes pc1,
282 po_lookup_codes pc2,
283 po_lookup_codes pc3,
284 hr_locations_all hl1,
285 hr_locations_all hl2,
286 ap_terms_tl tm,
287 org_freight_tl ofr,
288 gl_ledgers gl,
289 financials_system_params_all fin,
290 ap_system_parameters_all ap,
291 ap_awt_groups awt,
292 ap_product_setup aps
293 WHERE gl.ledger_id = fin.set_of_books_id
294 AND lc1.lookup_type(+) = 'PAYMENT METHOD'
295 AND lc1.lookup_code(+) = aps.payment_method_lookup_code
296 AND lc2.lookup_type(+) = 'PAY DATE BASIS'
297 AND lc2.lookup_code(+) = aps.pay_date_basis_lookup_code
298 AND lc3.lookup_type(+) = 'TERMS DATE BASIS'
299 AND lc3.lookup_code(+) = aps.terms_date_basis
300 AND pc1.lookup_type(+) = 'FOB'
301 AND pc1.lookup_code(+) = fin.fob_lookup_code
302 AND pc2.lookup_type(+) = 'FREIGHT TERMS'
303 AND pc2.lookup_code(+) = fin.freight_terms_lookup_code
304 AND pc3.lookup_type(+) = 'PAY GROUP'
305 AND pc3.lookup_code(+) = aps.supplier_pay_group_lookup_code
306 AND hl1.location_id(+) = fin.bill_to_location_id
307 AND hl1.bill_to_site_flag(+) = 'Y'
308 AND hl2.location_id(+) = fin.ship_to_location_id
309 AND hl2.ship_to_site_flag(+) = 'Y'
310 AND ofr.freight_code(+) = fin.ship_via_lookup_code
311 AND ofr.organization_id(+) = fin.inventory_organization_id
312 AND ofr.language(+) = userenv('LANG')
313 AND awt.group_id(+) = ap.default_awt_group_id
314 AND aps.terms_id = tm.term_id(+)
315 AND tm.language(+) = userenv('LANG')
316 AND fin.org_id = x_org_id
317 AND fin.set_of_books_id = ap.set_of_books_id
318 AND fin.org_id = ap.org_id;
319
320 --bug 12835899
321 IF x_employee_id is not null THEN
322
323 select employee_terms_id,
324 employee_pay_group_lookup_code,
325 nvl(tm.end_date_active,sysdate+1)
326 into x_terms_id,
327 x_vendor_pay_group_lookup_code,
328 l_terms_inactive_date
329 from ap_system_parameters ap,
330 ap_terms tm
331 where org_id = x_org_id
332 and ap.employee_terms_id = tm.term_id(+);
333
334 END IF;
335 --end of bug 12835899
336
337 END IF;
338
339 --
340 --
341 if (x_PO_inst_flag = 'I') then
342 --
343 -- Bug 457417 - Just checking if PO has been installed or not is no longer
344 -- sufficient in a multi-org environment, we need to know not only if PO has
345 -- been installed but if it has been setup. We assume that if PO has been
346 -- installed the table po_system_parameters exists and if it has been setup
347 -- it contains a record. (mhtaylor 30/Jan/98)
348 --
349 debug_info := 'Check to see if PO has been setup';
350 --
351 l_po_setup := 0;
352 --
353 SELECT count(*)
354 INTO l_po_setup
355 FROM po_system_parameters
356 WHERE nvl(org_id,-99) = nvl(x_org_id,-99);
357 --
358 IF l_po_setup > 0 THEN
359 --
360 debug_info := 'Select from rcv_parameters, po_lookup_codes, po_system_parameters';
361
362 SELECT rp.enforce_ship_to_location_code,
363 rp.receiving_routing_id,
364 rp.qty_rcv_tolerance,
365 rp.qty_rcv_exception_code,
366 rp.days_early_receipt_allowed,
367 rp.days_late_receipt_allowed,
368 rp.allow_substitute_receipts_flag,
369 rp.allow_unordered_receipts_flag,
370 rp.receipt_days_exception_code,
371 pc1.displayed_field, -- enforce_ship_to_loc_disp
372 pc2.displayed_field, -- qty_rcv_exception_disp
373 pc3.displayed_field, -- receipt_days_exception_disp
374 po.receiving_flag,
375 po.inspection_required_flag,
376 po.create_debit_memo_flag
377 INTO x_enforce_ship_to_loc_code,
378 x_receiving_routing_id,
379 x_qty_rcv_tolerance,
380 x_qty_rcv_exception_code ,
381 x_days_early_receipt_allowed,
382 x_days_late_receipt_allowed,
383 x_allow_sub_receipts_flag,
384 x_allow_unord_receipts_flag,
385 x_receipt_days_exception_code,
386 x_enforce_ship_to_loc_disp,
387 x_qty_rcv_exception_disp,
388 x_receipt_days_exception_disp,
389 x_receipt_required_flag,
390 x_inspection_required_flag,
391 x_po_create_dm_flag
392 FROM rcv_parameters rp,
393 po_lookup_codes pc1,
394 po_lookup_codes pc2,
395 po_lookup_codes pc3,
396 po_system_parameters po
397 WHERE rp.organization_id = x_inventory_organization_id
398 AND pc1.lookup_type(+) = 'RECEIVING CONTROL LEVEL'
399 AND pc1.lookup_code(+) = rp.enforce_ship_to_location_code
400 AND pc2.lookup_type(+) = 'RECEIVING CONTROL LEVEL'
401 AND pc2.lookup_code(+) = rp.qty_rcv_exception_code
402 AND pc3.lookup_type(+) = 'RECEIVING CONTROL LEVEL'
403 AND pc3.lookup_code(+) = rp.receipt_days_exception_code
404 --MO Access Control
405 AND nvl(po.org_id,-99) = nvl(x_org_id,-99);
406 --
407 --
408 debug_info := 'Select routing_name';
409
410 SELECT rh.routing_name
411 INTO x_receiving_routing_name
412 FROM rcv_Routing_Headers rh
413 WHERE rh.routing_header_id = x_receiving_routing_id;
414 --
415 ELSE
416 x_PO_inst_flag := '';
417 END IF;
418 --
419 end if;
420 --
421 --
422 fnd_profile.get('DEFAULT_COUNTRY',x_default_country_code);
423 --
424 --
425 if ( x_default_country_code is null ) then
426 x_default_country_code := x_home_country_code;
427 end if;
428 --
429 --
430 if ( x_default_country_code is not null ) then
431
432 select territory_short_name,
433 address_style
434 into x_default_country_disp,
435 x_address_style
436 from fnd_territories_vl
437 where territory_code = x_default_country_code
438 OR iso_territory_code = x_default_country_code; --Bug 5260178
439
440 end if;
441 --
442 -- Clear defaults if inactive
443 --
444 if sysdate > l_ship_to_loc_inactive_date then
445 x_ship_to_location_id := null;
446 x_ship_to_location_code := null;
447 end if;
448 --
449 --
450 if sysdate > l_bill_to_loc_inactive_date then
451 x_bill_to_location_id := null;
452 x_bill_to_location_code := null;
453 end if;
454 --
455 --
456 if sysdate > l_fob_inactive_date then
457 x_fob_lookup_code := null;
458 x_fob_lookup_disp := null;
459 end if;
460 --
461 --
462 if sysdate > l_freight_terms_inactive_date then
463 x_freight_terms_lookup_code := null;
464 x_freight_terms_lookup_disp := null;
465 end if;
466 --
467 --
468 if sysdate > l_terms_inactive_date then
469 x_terms_id := null;
470 x_terms_disp := null;
471 end if;
472 --
473 --
474 if sysdate > l_ship_via_inactive_date then
475 x_ship_via_lookup_code := null;
476 x_ship_via_disp := null;
477 end if;
478 --
479 -- Supply defaults from ap_lookup_codes where null values resulted
480 -- from lookup in financials_system_parameters table
481 --
482 if x_pay_date_basis_lookup_code is NULL then
483 debug_info := 'Select pay_date_basis displayed field';
484 SELECT lc.lookup_code,
485 lc.displayed_field
486 INTO x_pay_date_basis_lookup_code,
487 x_pay_date_basis_disp
488 FROM ap_lookup_codes lc
489 WHERE lc.lookup_type = 'PAY DATE BASIS'
490 AND lc.lookup_code = 'DISCOUNT';
491 end if;
492
493 if x_payment_method_lookup_code is NULL then
494 debug_info := 'Select payment_method display field';
495 SELECT lc.lookup_code,
496 lc.displayed_field
497 INTO x_payment_method_lookup_code,
498 x_payment_method_disp
499
500 FROM ap_lookup_codes lc
501 WHERE lc.lookup_type = 'PAYMENT METHOD'
502 AND lc.lookup_code = 'CHECK';
503 end if;
504 --
505 EXCEPTION
506 WHEN OTHERS THEN
507 IF (SQLCODE <> -20001) THEN
508 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
509 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
510 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
511 FND_MESSAGE.SET_TOKEN('PARAMETERS','SHIP_TO_LOCATION_ID = ' ||
512 x_ship_to_location_id ||
513 'BILL_TO_LOCATION_ID = ' || x_bill_to_location_id ||
514 'FOB_LOOKUP_CODE = ' || x_fob_lookup_code ||
515 'FREIGHT_TERMS_LOOKUP_CODE = ' || x_freight_terms_lookup_code ||
516 'TERMS_ID = ' || x_terms_id ||
517 'PAYMENT_METHOD_LOOKUP_CODE = ' || x_payment_method_lookup_code ||
518 'PAY_DATE_BASIS_LOOKUP_CODE = ' || x_pay_date_basis_lookup_code ||
519 'SET_OF_BOOKS_ID = ' || x_set_of_books_id ||
520 'VENDOR_PAY_GROUP_LOOKUP_CODE = ' || x_vendor_pay_group_lookup_code ||
521 'TERMS_DATE_BASIS = ' || x_terms_date_basis ||
522 'SHIP_VIA_LOOKUP_CODE = ' || x_ship_via_lookup_code ||
523 'INVENTORY_ORGANIZATION_ID = ' || x_inventory_organization_id ||
524 'ENFORCE_SHIP_TO_LOC_CODE = ' || x_enforce_ship_to_loc_code ||
525 'QTY_RCV_EXCEPTION_CODE = ' || x_qty_rcv_exception_code ||
526 'RECEIPT_DAYS_EXCEPTION_CODE = ' || x_receipt_days_exception_code ||
527 'RECEIVING_ROUTING_ID = ' || x_receiving_routing_id);
528
529 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
530 END IF;
531 APP_EXCEPTION.RAISE_EXCEPTION;
532
533
534 END INITIALIZE;
535 --
536
537 --
538 --
539 PROCEDURE Initialize_Supplier_Attr (
540 x_user_defined_vendor_num_code in out NOCOPY varchar2,
541 x_manual_vendor_num_type in out NOCOPY varchar2,
542 x_terms_id in out NOCOPY number,
543 x_terms_disp in out NOCOPY varchar2,
544 x_always_take_disc_flag in out NOCOPY varchar2,
545 x_invoice_currency_code in out NOCOPY varchar2,
546 x_vendor_pay_group_lookup_code in out NOCOPY varchar2,
547 x_sys_auto_calc_int_flag in out NOCOPY varchar2,
548 x_terms_date_basis in out NOCOPY varchar2,
549 x_terms_date_basis_disp in out NOCOPY varchar2,
550 x_vendor_pay_group_disp in out NOCOPY varchar2,
551 x_fin_require_matching in out NOCOPY varchar2,
552 x_fin_match_option in out NOCOPY varchar2,
553 x_sysdate in out NOCOPY date,
554 x_pay_date_basis_lookup_code in out NOCOPY varchar2,
555 x_pay_date_basis_disp in out NOCOPY varchar2,
556 x_AP_inst_flag in out NOCOPY varchar2,
557 x_use_bank_charge_flag in out NOCOPY varchar2,
558 x_bank_charge_bearer in out NOCOPY varchar2,
559 X_calling_sequence in varchar2 ) is
560
561 --
562 l_ap_status varchar2(30);
563 l_industry varchar2(30);
564 l_oracle_schema varchar2(30);
565 dummy boolean;
566 l_po_setup number;
567
568 l_terms_inactive_date date;
569 --
570 --
571 current_calling_sequence varchar2(2000);
572 debug_info varchar2(100);
573
574 begin
575
576 -- Update the calling sequence
577 --
578 current_calling_sequence := 'AP_APXVDMVD_PKG.Initialize_Supplier_Attr<-' ||
579 X_calling_sequence;
580 --
581 --
582
583 dummy := fnd_installation.get_app_info
584 ('SQLAP',l_ap_status,l_industry,l_oracle_schema);
585 x_AP_inst_flag := l_ap_status;
586
587 --
588 --
589 debug_info
590 := 'Select from ap_lookup_codes, po_lookup_codes, hr_locations, ....';
591 SELECT aps.supplier_numbering_method,
592 aps.supplier_num_type,
593 aps.terms_id, -- terms_id
594 tm.name, -- terms_name
595 nvl(tm.end_date_active,sysdate+1), -- terms_inactive_date
596 aps.always_take_disc_flag,
597 aps.pay_date_basis_lookup_code, -- pay_date_basis_lookup_code
598 lc2.displayed_field, -- pay_date_basis_disp
599 aps.invoice_currency_code,
600 aps.supplier_pay_group_lookup_code,
601 pc3.lookup_code,
602 aps.auto_calculate_interest_flag,
603 aps.terms_date_basis, -- terms_date_basis
604 lc3.displayed_field, -- terms_date_basis_disp
605 aps.hold_unmatched_invoices_flag,
606 sysdate,
607 --5007989 ap.use_bank_charge_flag,
608 --5007989 nvl(ap.bank_charge_bearer, 'I')
609 aps.match_option --bug6075649
610 INTO x_user_defined_vendor_num_code,
611 x_manual_vendor_num_type,
612 x_terms_id,
613 x_terms_disp,
614 l_terms_inactive_date,
615 x_always_take_disc_flag,
616 x_pay_date_basis_lookup_code,
617 x_pay_date_basis_disp,
618 x_invoice_currency_code,
619 x_vendor_pay_group_lookup_code,
620 x_vendor_pay_group_disp,
621 x_sys_auto_calc_int_flag,
622 x_terms_date_basis,
623 x_terms_date_basis_disp,
624 x_fin_require_matching,
625 x_sysdate,
626 -- x_use_bank_charge_flag,
627 -- x_bank_charge_bearer
628 x_fin_match_option --bug6075649
629 FROM ap_lookup_codes lc2,
630 ap_lookup_codes lc3,
631 po_lookup_codes pc3,
632 ap_terms tm,
633 ap_product_setup aps
634 WHERE lc2.lookup_type(+) = 'PAY DATE BASIS'
635 AND lc2.lookup_code(+) = aps.pay_date_basis_lookup_code
636 AND lc3.lookup_type(+) = 'TERMS DATE BASIS'
637 AND lc3.lookup_code(+) = aps.terms_date_basis
638 AND pc3.lookup_type(+) = 'PAY GROUP'
639 AND pc3.lookup_code(+) = aps.supplier_pay_group_lookup_code
640 AND aps.terms_id = tm.term_id(+);
641
642
643 --
644 -- Clear defaults if inactive
645 --
646 --
647 --
648 if sysdate > l_terms_inactive_date then
649 x_terms_id := null;
650 x_terms_disp := null;
651 end if;
652
653 if x_pay_date_basis_lookup_code is NULL then
654 debug_info := 'Select pay_date_basis displayed field';
655 SELECT lc.lookup_code,
656 lc.displayed_field
657 INTO x_pay_date_basis_lookup_code,
658 x_pay_date_basis_disp
659 FROM ap_lookup_codes lc
660 WHERE lc.lookup_type = 'PAY DATE BASIS'
661 AND lc.lookup_code = 'DISCOUNT';
662 end if;
663
664 --
665 EXCEPTION
666 WHEN OTHERS THEN
667 IF (SQLCODE <> -20001) THEN
668 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
669 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
670 FND_MESSAGE.SET_TOKEN
671 ('CALLING_SEQUENCE',current_calling_sequence);
672 FND_MESSAGE.SET_TOKEN('PARAMETERS',
673 'TERMS_ID = ' || x_terms_id ||
674 'PAY_DATE_BASIS_LOOKUP_CODE = ' || x_pay_date_basis_lookup_code ||
675 'VENDOR_PAY_GROUP_LOOKUP_CODE = ' ||
676 x_vendor_pay_group_lookup_code ||
677 'TERMS_DATE_BASIS = ' || x_terms_date_basis);
678 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
679 END IF;
680 APP_EXCEPTION.RAISE_EXCEPTION;
681
682
683 END Initialize_Supplier_Attr;
684 --
685
686 END AP_APXVDMVD_PKG;