[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_GLOBE_VAL_PKG
Source
1 PACKAGE BODY JL_ZZ_GLOBE_VAL_PKG AS
2 /* $Header: jlzzglvb.pls 120.4 2005/10/30 02:05:51 appldev ship $ */
3
4 --
5 -- Procedure Name:
6 -- count_void_trx_type
7 -- Called From
8 -- RAXSUCTT_ZZ_RA_CUST_TTYPES_BV
9 -- Purpose
10 -- Return the number of Void Transaction Types
11 --
12 FUNCTION count_void_trx_type(
13 p_country_code IN VARCHAR2,
14 p_cust_trx_type_id IN NUMBER) RETURN NUMBER IS
15
16 l_count NUMBER;
17 l_category VARCHAR2(30);
18
19 BEGIN
20
21 IF p_country_code = 'CL' THEN
22 l_category := 'JL.CL.RAXSUCTT.CUST_TRX_TYPES';
23 ELSIF p_country_code = 'AR' THEN
24 l_category := 'JL.AR.RAXSUCTT.CUST_TRX_TYPES';
25 ELSIF p_country_code = 'CO' THEN
26 l_category := 'JL.CO.RAXSUCTT.CUST_TRX_TYPES';
27 END IF;
28
29 SELECT COUNT(*)
30 INTO l_count
31 FROM ra_cust_trx_types ct
32 WHERE ct.global_attribute_category = l_category
33 AND ct.global_attribute6 = 'Y'
34 AND decode(p_cust_trx_type_id,null,-1,ct.cust_trx_type_id) <> nvl(p_cust_trx_type_id,-2);
35
36 RETURN l_count;
37
38 END;
39
40 --
41 -- Procedure Name:
42 -- get_copy_status
43 -- Called From:
44 -- ARXTWMAI_ZZ_TGW_HEADER_BV
45 -- Purpose:
46 -- Gets copy statuses
47 --
48 PROCEDURE get_copy_status(
49 p_country_code IN VARCHAR2,
50 p_customer_trx_id IN NUMBER,
51 p_copy_status_code OUT NOCOPY VARCHAR2,
52 p_copy_status_meaning OUT NOCOPY VARCHAR2) IS
53
54 l_category VARCHAR2(30);
55
56 BEGIN
57
58 IF p_country_code = 'CL' THEN
59 l_category := 'JL.CL.ARXTWMAI.TGW_HEADER';
60 ELSIF p_country_code = 'AR' THEN
61 l_category := 'JL.AR.ARXTWMAI.TGW_HEADER';
62 ELSIF p_country_code = 'CO' THEN
63 l_category := 'JL.CO.ARXTWMAI.TGW_HEADER';
64 END IF;
65
66 IF l_category IS NOT NULL THEN
67
68 SELECT rc.global_attribute20, fl.meaning
69 INTO p_copy_status_code, p_copy_status_meaning
70 FROM ra_customer_trx rc, fnd_lookups fl
71 WHERE rc.customer_trx_id = p_customer_trx_id
75
72 AND rc.global_attribute_category = l_category
73 AND rc.global_attribute20 = fl.lookup_code
74 AND fl.lookup_type = 'JLZZ_COPY_STATUS';
76 ELSE
77 p_copy_status_code := '';
78 p_copy_status_meaning := '';
79 END IF;
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 p_copy_status_code := '';
84 p_copy_status_meaning := '';
85 END;
86
87 --
88 -- Procedure Name:
89 -- get_orig_trx_type
90 -- Called From:
91 -- ARXTWMAI_ZZ_CP_ORIG_TRX_TYPE
92 -- Purpose:
93 -- Return cust_trx_type_id of specified transactions
94 --
95 PROCEDURE get_orig_trx_type(
96 p_customer_trx_id IN NUMBER,
97 p_cust_trx_type_id OUT NOCOPY NUMBER) IS
98
99 BEGIN
100
101 IF p_customer_trx_id IS NOT NULL THEN
102
103 SELECT cust_trx_type_id
104 INTO p_cust_trx_type_id
105 FROM ra_customer_trx
106 WHERE customer_trx_id = p_customer_trx_id;
107
108 ELSE
109
110 p_cust_trx_type_id := '';
111
112 END IF;
113
114 EXCEPTION
115 WHEN NO_DATA_FOUND THEN
116 p_cust_trx_type_id := '';
117 WHEN OTHERS THEN
118 RAISE PROGRAM_ERROR;
119 END;
120
121 --
122 -- Procedure Name:
123 -- chk_company_name_unique
124 -- Called From:
125 -- PERWSLOC_ZZ_LOC_BV
126 -- Purpose:
127 -- To check uniqueness of company name(GLOBAL_ATTRIBUTE8 of AR/CL/CO).
128 --
129 PROCEDURE chk_company_name_unique(
130 p_rowid IN VARCHAR2,
131 p_country_code IN VARCHAR2,
132 p_company_name IN VARCHAR2,
133 p_message_code OUT NOCOPY VARCHAR2) IS
134 dummy NUMBER;
135
136 BEGIN
137
138 select count(1) into dummy
139 from hr_locations_all
140 where global_attribute8 = p_company_name
141 and substrb(nvl(global_attribute_category,'XX.XX'),4,2) = p_country_code
142 and ((p_rowid is null) or (rowid <> p_rowid));
143
144 if (dummy >= 1) then
145 p_message_code := 'FALSE';
146 else
147 p_message_code := 'TRUE';
148 end if;
149
150 END chk_company_name_unique;
151
152 --
153 -- Procedure Name:
154 -- get_last_attrs
155 -- Called From:
156 -- FAXSUBCT_ZZ_LAST_REV_RUN_BV
157 -- Purpose:
158 -- Return last inflation adjusted, revaluation and closed period
159 -- for a given book.
160 --
161
162 PROCEDURE get_last_attrs(
163 p_book_type_code IN VARCHAR2,
164 p_last_inf_adj OUT NOCOPY VARCHAR2,
165 p_last_reval OUT NOCOPY VARCHAR2,
166 p_last_closed_period OUT NOCOPY VARCHAR2,
167 p_last_period_posted OUT NOCOPY VARCHAR2) IS
168
169 BEGIN
170 SELECT b.global_attribute2 last_inf_adj,
171 b.global_attribute3 last_reval,
172 b.global_attribute5 last_closed_period,
173 b.global_attribute19 last_period_posted
174 INTO p_last_inf_adj,p_last_reval,p_last_closed_period,p_last_period_posted
175 FROM fa_book_controls b
176 WHERE b.book_type_code = p_book_type_code;
177 EXCEPTION
178 WHEN OTHERS THEN
179 RAISE PROGRAM_ERROR;
180 END get_last_attrs;
181
182 --
183 -- Procedure Name:
184 -- get_last_period_ctr
185 -- Called From:
186 -- JLCO_FA
187 -- Purpose:
188 -- Return last period counter
189 -- for a deprn calendar given.
190 --
191
192 PROCEDURE get_last_period_ctr(
193 p_deprn_calendar IN VARCHAR2,
194 p_current_fiscal_year IN NUMBER,
195 p_current_period_num IN NUMBER,
196 p_last_period_counter OUT NOCOPY NUMBER)IS
197
198 BEGIN
199 select to_number(p_current_fiscal_year)
200 * ct.number_per_fiscal_year
201 + to_number(p_current_period_num)-1
202 into p_last_period_counter
203 from fa_calendar_types ct
204 where ct.calendar_type = p_deprn_calendar;
205 EXCEPTION
206 WHEN OTHERS THEN
207 RAISE PROGRAM_ERROR;
208 END get_last_period_ctr;
209
210 --
211 -- Function Name:
212 -- verify_adjust_flag
213 -- Called From:
214 -- FAXDPRUN_ZZ_DPRN_RUN_BV
215 -- Purpose:
216 -- If the book is adjustable, then verify if inflation adjustment
217 -- has been run.
218 --
219 -- 03/22/00 Santosh Vaze Bug Fix 1235190 : Added a better suited
220 -- message for the situation where Colombian Generate JE process is not run.
221 -- The following package now returns different flags for different scenarios.
222
223 FUNCTION verify_adjust_flag(
224 p_country_code IN VARCHAR2,
225 p_book_type_code IN VARCHAR2,
226 p_period_name IN VARCHAR2) RETURN NUMBER IS
227
228 l_period_counter NUMBER;
229 l_period_cont NUMBER;
230 l_mass_reval_id NUMBER;
231 allowed VARCHAR2(3);
232 dummy NUMBER;
233
234 BEGIN
235
236 SELECT global_attribute1,TO_NUMBER(global_attribute2),
237 global_attribute3,TO_NUMBER(global_attribute5)
241 WHERE book_type_code = p_book_type_code;
238 INTO allowed,l_period_counter,
239 l_mass_reval_id,l_period_cont
240 FROM fa_book_controls
242
243
244 IF allowed = 'Y' THEN
245 IF p_country_code = 'CO' AND (l_period_counter-l_period_cont > 1) THEN
246 RETURN 1;
247 END IF;
248
249 SELECT count(*)
250 INTO dummy
251 FROM fa_deprn_periods c,fa_mass_revaluations a
252 WHERE a.book_type_code = p_book_type_code
253 AND a.status = 'COMPLETED'
254 AND a.mass_reval_id = l_mass_reval_id
255 AND c.book_type_code = a.book_type_code
256 AND c.period_name = p_period_name
257 AND c.period_counter = l_period_counter;
258 -- AND c.period_counter = a.global_attribute1; /* To fix bug 1013530 */
259
260 IF dummy > 0 then
261 RETURN 0;
262 ELSE
263 RETURN 2;
264 END IF;
265 ELSE
266 RETURN 0;
267 END IF;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 RAISE PROGRAM_ERROR;
272 END verify_adjust_flag;
273
274
275 --
276 -- Function Name:
277 -- verify_book_synchro
278 -- Called From:
279 -- FAXDPRUN_ZZ_DPRN_RUN_BV
280 -- Purpose:
281 -- Verify if the given book has 'Tax' or 'Corporative' associated
282 -- books, and if those books are or not out of synchrony in ran periods.
283 --
284
285 FUNCTION verify_book_synchro(
286 p_book_type_code IN VARCHAR2,
287 p_period_name IN VARCHAR2) RETURN BOOLEAN IS
288 dummy NUMBER;
289
290 BEGIN
291
292 SELECT 1
293 INTO dummy
294 FROM dual
295 WHERE NOT EXISTS ( SELECT 1
296 FROM fa_book_controls b, fa_book_controls a
297 WHERE a.book_type_code = p_book_type_code
298 AND DECODE(a.book_class
299 , 'CORPORATE', a.book_type_code
300 , 'TAX' ,a.distribution_source_book) =
301 DECODE (a.book_class
302 , 'CORPORATE', b.distribution_source_book
303 ,'TAX' ,b.book_type_code)
304 AND b.allow_cip_assets_flag = 'YES'
305 AND b.rowid <> a.rowid
306 AND NOT EXISTS ( SELECT 1
307 FROM fa_deprn_periods c
308 , fa_deprn_periods d
309 WHERE c.book_type_code =
310 a.book_type_code
311 AND c.period_name = p_period_name
312 AND d.book_type_code =
313 b.book_type_code
314 AND d.period_close_date IS NULL
315 AND c.period_counter <=
316 d.period_counter));
317 RETURN TRUE;
318
319 EXCEPTION
320 WHEN OTHERS THEN
321 RETURN FALSE;
322 END verify_book_synchro;
323
324
325 /*
326 * Function Name:
327 * is_foreign_supplier()
328 * Called From:
329 * APXINWKB_AR_INV_SUM_FOLDER_BV
330 * Purpose:
331 * Return TRUE if it is a foreign supplier; FALSE otherwise
332 */
333 FUNCTION is_foreign_supplier(p_vendor_id IN NUMBER) RETURN BOOLEAN IS
334 l_count NUMBER;
335 l_is_foreign BOOLEAN;
336 BEGIN
337 SELECT count(*)
338 INTO l_count
339 FROM po_vendors pv
340 WHERE pv.vendor_id = p_vendor_id AND
341 pv.global_attribute9 = 'FOREIGN_ORIGIN';
342
343 IF l_count = 1 THEN
344 RETURN TRUE;
345 ELSE
346 RETURN FALSE;
347 END IF;
348 END is_foreign_supplier;
349
350
351 /*
352 * Function Name:
353 * get_ship_to_location_code()
354 * Called From:
355 * APXINWKB_AR_D_SUM_FOLDER_BV
356 * Purpose:
357 * Return ship to location code
358 */
359
360
361 FUNCTION get_ship_to_location_code(p_po_distribution_id IN NUMBER) RETURN VARCHAR2 IS
362
363 l_ship_to_location_code hr_locations_all.location_code%type; --bug 2238543: VARCHAR2(30);
364
365 BEGIN
366 SELECT hl.location_code
367 INTO l_ship_to_location_code
368 FROM hr_locations_all hl
369 WHERE hl.location_id = (SELECT pll.ship_to_location_id
370 FROM po_line_locations_all pll
371 WHERE pll.line_location_id = (SELECT pd.line_location_id
372 FROM po_distributions_all pd
373 WHERE pd.po_distribution_id = p_po_distribution_id));
374 RETURN l_ship_to_location_code;
375
376 EXCEPTION
377 WHEN no_data_found THEN
378 RETURN NULL;
379 WHEN others THEN
380 RETURN NULL;
381 END get_ship_to_location_code;
382
383
384 /*
385 * Function Name:
386 * get_vendor_name()
387 * Called From:
388 * APXINWKB_ZZ_SPECIAL_MENU_SPC6
389 * Purpose:
390 * Return vendor_name given the tax payer ID (Vendor Number)
394 BEGIN
391 */
392 FUNCTION get_vendor_name(p_taxpayer_id IN VARCHAR2) RETURN VARCHAR2 IS
393 l_vendor_name VARCHAR2(80);
395 SELECT pv.vendor_name
396 INTO l_vendor_name
397 FROM po_vendors pv
398 WHERE pv.segment1 = p_taxpayer_id;
399
400 RETURN l_vendor_name;
401
402 EXCEPTION
403 WHEN others THEN
404 RETURN NULL;
405 END get_vendor_name;
406
407
408 /*
409 * Function Name:
410 * get_vendor_id()
411 * Called From:
412 * APXINWKB_ZZ_SPECIAL_MENU_SPC6
413 * Purpose:
414 * Bug 4055807: Return vendor_id given the vendor number
415 */
416 FUNCTION get_vendor_id(p_vendor_number IN VARCHAR2) RETURN NUMBER IS
417 l_vendor_id NUMBER;
418 BEGIN
419 SELECT pv.vendor_id
420 INTO l_vendor_id
421 FROM po_vendors pv
422 WHERE pv.segment1 = p_vendor_number;
423
424 RETURN l_vendor_id;
425
426 EXCEPTION
427 WHEN others THEN
428 RETURN NULL;
429 END get_vendor_id;
430
431
432 /*
433 * Procedure Name:
434 * get_awt_type_attributes()
435 * Called From:
436 * APXTADTC_AR_TAX_CODES_BV and APXTADTC_CO_TAX_CODES_BV
437 * Purpose:
438 * Return Argentine and Colombian AWT type attributes given the AWT type code
439 */
440 PROCEDURE get_awt_type_attributes(p_awt_type_code IN VARCHAR2,
441 p_jurisdiction_type OUT NOCOPY VARCHAR2,
442 p_foreign_supplier_flag OUT NOCOPY VARCHAR2,
443 p_min_tax_amount_level OUT NOCOPY VARCHAR2,
444 p_min_wh_amount_level OUT NOCOPY VARCHAR2,
445 p_cumulative_payment_flag OUT NOCOPY VARCHAR2,
446 p_vat_inclusive_flag OUT NOCOPY VARCHAR2,
447 p_user_defined_formula_flag OUT NOCOPY VARCHAR2) IS
448 BEGIN
449 SELECT jurisdiction_type, foreign_supplier_flag, min_tax_amount_level,
450 min_wh_amount_level, cumulative_payment_flag, vat_inclusive_flag,
451 user_defined_formula_flag
452 INTO p_jurisdiction_type, p_foreign_supplier_flag, p_min_tax_amount_level,
453 p_min_wh_amount_level, p_cumulative_payment_flag, p_vat_inclusive_flag,
454 p_user_defined_formula_flag
455 FROM jl_zz_ap_awt_types
456 WHERE awt_type_code = p_awt_type_code;
457
458 EXCEPTION
459 WHEN others THEN
460 RAISE program_error;
461 END get_awt_type_attributes;
462
463
464 /*
465 * Function Name:
466 * exist_legal_address_site()
467 * Called From:
468 * APXVDMVD_ZZ_SITE_BV
469 * Purpose:
470 * Return TRUE if there is already a site chosen as the legal address;
471 * FALSE otherwise.
472 */
473 FUNCTION exist_legal_address_site(p_vendor_id IN NUMBER, p_vendor_site_id IN NUMBER) RETURN BOOLEAN IS
474 l_count NUMBER;
475 l_exist_legal_address_site BOOLEAN;
476 BEGIN
477 SELECT count(*)
478 INTO l_count
479 FROM ap_vendor_sites_v st
480 WHERE st.vendor_id = p_vendor_id AND
481 st.global_attribute17 = 'Y' AND
482 vendor_site_id <> p_vendor_site_id;
483
484 IF l_count = 1 THEN
485 RETURN TRUE;
486 ELSE
487 RETURN FALSE;
488 END IF;
489 END exist_legal_address_site;
490
491 -- Procedure Name:
492 -- get_last_start_date
493 -- Called From:
494 -- FAXASSET_FA_ASSET_BV
495 -- Purpose:
496 -- Return last Inflation Start Date, for a given asset.
497 --
498
499 PROCEDURE get_last_start_date( p_asset_id IN NUMBER,
500 p_last_start_date OUT NOCOPY VARCHAR2) IS
501
502 BEGIN
503 SELECT global_attribute1
504 INTO p_last_start_date
505 FROM fa_additions
506 WHERE asset_id = p_asset_id;
507 EXCEPTION
508 WHEN OTHERS THEN
509 RAISE PROGRAM_ERROR;
510
511 END get_last_start_date;
512
513
514 --
515 -- Function Name:
516 -- eval_asset_reval
517 -- Called From:
518 -- FAXASSET_FA_ASSET_BV
519 -- Purpose:
520 -- Verify is Inflation Adjustment has been applied at
521 -- least once to the given asset.
522 --
523
524 FUNCTION eval_asset_reval( p_asset_id IN NUMBER) RETURN BOOLEAN IS
525
526 dummy NUMBER;
527
528 BEGIN
529
530 SELECT 1
531 INTO dummy
532 FROM dual
533 WHERE EXISTS (SELECT 1
534 FROM fa_mass_revaluation_rules rr,
535 fa_mass_revaluations mr
536 WHERE rr.asset_id = p_asset_id
537 AND rr.mass_reval_id = mr.mass_reval_id
538 AND mr.status = 'COMPLETED');
539 RETURN TRUE;
540
541
542 EXCEPTION
543 WHEN NO_DATA_FOUND THEN
544 RETURN FALSE;
545 END eval_asset_reval;
546
547
548 PROCEDURE get_location_row_id(p_location_id IN NUMBER, p_row_id OUT NOCOPY VARCHAR2) IS
549 l_rowid varchar2(100);
550 BEGIN
551
552 SELECT rowid into l_rowid
553 FROM hr_locations_all
554 WHERE location_id = p_location_id;
555 p_row_id := l_rowid;
556 EXCEPTION
557 WHEN NO_DATA_FOUND THEN
558 NULL;
559 END;
560
561 END JL_ZZ_GLOBE_VAL_PKG;