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