1 PACKAGE BODY JG_ZZ_COMMON_PKG AS
2 /* $Header: jgzzvatcmnb.pls 120.22.12020000.2 2012/07/17 10:30:10 rshergil noship $
3 *************************************************************************************
4 | Copyright (c) 1996 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 *************************************************************************************
7
8
9 PROGRAM NAME
10 JGZZ_COMMON_PKB.pls
11
12 DESCRIPTION
13 Script to create package body for the common packn
14
15 HISTORY
16 =======
17
18 VERSION DATE AUTHOR(S) DESCRIPTION
19 ------- ----------- --------------- -------------------------------------
20 DRAFT 1A 31-Jan-2006 Manish Upadhyay Initial draft version
24 Also added the debug code
21 DRAFT 1B 21-Feb-2006 Manish Upadhyay Modified as per the Review comments
22 120.2 26-APR-2006 Brathod Bug: 5188902, Changed X_taxpayer_id from Number to Varchar2
23 120.3 31-May-2006 Vijay modified the code to rectify issues observed during UT of extracts
25 120.4 31-May-2006 Rukmani Basker Added procedure to get configurable
26 setup for an reporting entity
27 get_entities_configuration_dtl
28 120.5 27-Jun-2006 Suresh.Pasupunuri Added X_ENTITY_IDENTIFIER new parameter
29 to get_entities_configuration_dtl funct
30 120.6 08-Aug-2006 Suresh.Pasupunuri Commented the API call get_period_status in the procedure
31 tax_registration. Handling this API call in each extract separately
32 120.7 09-Sep-2006 Suresh.Pasupunuri Introduced a new cursor"lc_get_le_id" in function "funct_curr_legal
33 " for getting the legal entity id. This is because the existing
34 cursor-"lc_funct_curr_legal" will returns the legal entity id
35 information only if the data is available for reporting,
36 which is incorrect.
37 120.8 30-Mar-2007 Ashanka Das Modified to Logic to retrieve the Contact Information like Contact name and Contact
38 Phone number to be displayed on all report headers.
39 Modified the logic for Bug 5616752 and 5566343.
40 120.9 30-Mar-2007 Ashanka Das Re-Modified the Logic so that even if there is no Contact, then also the
41 Company Info should be populated. Only Contact and telephone will be blank if there is no
42 Contact info, other data in the headers will be populated normally
43 120.10 25-Jul-2008 Varun Kejriwal Modified the code in the function funct_curr_legal in the cursor
44 lc_funct_curr_legal. Here the currency is no more fetched from
45 jg_zz_vat_trx_details. First it is verified for what the
46 Selection process was run. If for LE(Legal Entity) then from
47 jgzzvattrxdetails else if LEDGER then from gl_ledgers for bug 7287545
48 120.13 09-Jan-2009 Varun Kejriwal Added a function get_amt_tot which takes invoice_id and ledger_id as parameters
49 and based on the type of the reporting entity ( LE/ Primary Ledger/ Secondary Ledger ),
50 it returns the appropriate invoice_amount.
51 120.18 23-Feb-2009 Suresh Bug 9381398:
52 Modified the get_amt_tot function logic. Earlier the invoice amount
53 logic was based on xla distribution links. But due to some limitation
54 while accessing the amounts information for upgrade transactions
55 changed the logic to fetch the amounts from xla ae lines.
56 Refer bug 9386590 for more details on upgrade transanctions issue
57 with xla distribution links.
58 120.11.12010000.11 13-Sep-2011 sakekuma Bug 12779390: Modified the get_amt_tot function logic to calculate invoice amount.Added
59 and xe.EVENT_TYPE_CODE <> 'PREPAYMENT APPLIED' so that prepayment applied events are
60 not picked up while calculation invoice amount.
61 120.11.12010000.12 21-Mar-2012 Mallika Kandula Bug 13853951 : Modified get_amt_tot function to exclude 'PREPAYMENT UNAPPLIED' events
62 *********************************************************************************** */
63
64
65 gv_debug_flag constant boolean := false;
66
67 PROCEDURE funct_curr_legal(x_func_curr_code OUT NOCOPY VARCHAR2
68 ,x_rep_entity_name OUT NOCOPY VARCHAR2
69 ,x_legal_entity_id OUT NOCOPY NUMBER
70 ,x_taxpayer_id OUT NOCOPY VARCHAR2
71 ,pn_vat_rep_entity_id IN NUMBER
72 ,pv_period_name IN VARCHAR2 DEFAULT NULL
73 ,pn_period_year IN NUMBER DEFAULT NULL)
74 -- +==================================================================================+
75 -- | Name : funct_curr_legal |
76 -- | Description : funct_curr_legal procedure. |
77 -- | This procedure is used to fetch Functional_currency_code, |
78 -- | rep_context_entity_name,rep_entity_id. |
79 -- | Parameters : |
80 -- | x_func_curr_code : Return Functional_currency_code |
81 -- | x_rep_entity_name : Return rep_context_entity_name |
82 -- | x_legal_entity_id : Return Legal Entity Id |
83 -- | x_taxpayer_id : Tax Payer Id |
84 -- | pn_vat_rep_entity_id: Vat Reporting Entity Id |
85 -- | pv_period_name : Period Name |
86 -- | pn_period_year : Period Year |
87 -- +==================================================================================+
88 IS
89 CURSOR lc_funct_curr_legal IS
90 SELECT DECODE(JZVRE.entity_level_code,'LEDGER',GL.currency_code,nvl(JZVTD.functional_currency_code, jzvtd.trx_currency_code))
91 ,JZVTD.rep_context_entity_name
92 ,JZVTD.rep_entity_id
96 ,jg_zz_vat_rep_entities JZVRE
93 ,JZVTD.taxpayer_id
94 FROM jg_zz_vat_trx_details JZVTD
95 ,jg_zz_vat_rep_status JZVRS
97 ,gl_ledgers GL
98 WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
99 AND JZVRS.vat_reporting_entity_id= pn_vat_rep_entity_id
100 AND (JZVRS.tax_calendar_period = pv_period_name
101 OR JZVRS.tax_calendar_year = pn_period_year)
102 AND JZVRE.vat_reporting_entity_id= JZVRS.vat_reporting_entity_id
103 and DECODE(JZVRE.entity_level_code,'LEDGER',JZVRE.ledger_id,1)=DECODE(entity_level_code,'LEDGER',GL.ledger_id,1)
104 AND rownum = 1;
105
106 CURSOR lc_get_le_id IS
107 SELECT cfgd.legal_entity_id
108 FROM jg_zz_vat_rep_entities cfg
109 ,jg_zz_vat_rep_entities cfgd
110 WHERE cfg.vat_reporting_entity_id = pn_vat_rep_entity_id
111 AND (
112 ( cfg.entity_type_code = 'ACCOUNTING'
113 and cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id
114 )
115 or
116 ( cfg.entity_type_code = 'LEGAL'
117 and cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id
118 ));
119
120 BEGIN
121
122 if gv_debug_flag then
123 fnd_file.put_line(fnd_file.log, 'Start CMN.funct_curr_legal. pn_vat_rep_entity_id:'||pn_vat_rep_entity_id
124 ||', pv_period_name:'||pv_period_name||', pn_period_year:'||pn_period_year);
125 end if;
126
127 IF lc_funct_curr_legal%ISOPEN THEN
128 CLOSE lc_funct_curr_legal;
129 END IF;
130 OPEN lc_funct_curr_legal;
131 FETCH lc_funct_curr_legal INTO x_func_curr_code
132 ,x_rep_entity_name
133 ,x_legal_entity_id
134 ,x_taxpayer_id;
135
136 CLOSE lc_funct_curr_legal;
137
138 IF lc_get_le_id%ISOPEN THEN
139 CLOSE lc_get_le_id;
140 END IF;
141
142 OPEN lc_get_le_id;
143 FETCH lc_get_le_id INTO x_legal_entity_id;
144 CLOSE lc_get_le_id;
145
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 FND_FILE.PUT_LINE(FND_FILE.LOG,' No Header Information found in JG table. '|| SUBSTR(SQLERRM,1,200));
149 WHEN OTHERS THEN
150 RAISE;
151 END funct_curr_legal;
152
153 PROCEDURE tax_registration(x_tax_registration OUT NOCOPY VARCHAR2
154 ,x_period_start_date OUT NOCOPY DATE
155 ,x_period_end_date OUT NOCOPY DATE
156 ,x_status OUT NOCOPY VARCHAR2
157 ,pn_vat_rep_entity_id IN NUMBER
158 ,pv_period_name IN VARCHAR2 DEFAULT NULL
159 ,pn_period_year IN NUMBER DEFAULT NULL
160 ,pv_source IN VARCHAR2)
161 -- +==================================================================================+
162 -- | Name : funct_curr_legal |
163 -- | Description : funct_curr_legal procedure. |
164 -- | This procedure is used to fetch tax_registration_number, |
165 -- | period_start_date,period_end_date,status. |
166 -- | Parameters : |
167 -- | x_tax_registration : Return tax_registration_number |
168 -- | x_period_start_date : Return period_start_date |
169 -- | x_period_end_date : Return period_end_date |
170 -- | x_status : Status (P=Preliminary, F=Final) |
171 -- | pn_vat_rep_entity_id : Vat Reporting Entity Id |
172 -- | pv_period_name : Period Name |
173 -- | pn_period_year : Period Year |
174 -- | pv_source : Possible values for Source are -> AP, AR, GL, ALL |
175 -- +==================================================================================+
176 IS
177 CURSOR lc_tax_registration IS
178 SELECT JZVRS.tax_registration_number
179 ,min(JZVRS.period_start_date)
180 ,max(JZVRS.period_end_date)
181 FROM jg_zz_vat_rep_status JZVRS
182 WHERE JZVRS.vat_reporting_entity_id= pn_vat_rep_entity_id
183 AND (JZVRS.tax_calendar_period = pv_period_name
184 OR JZVRS.tax_calendar_year = pn_period_year)
185 group by JZVRS.tax_registration_number;
186
187 BEGIN
188
189 if gv_debug_flag then
190 fnd_file.put_line(fnd_file.log, 'Start CMN.tax_registration. pn_vat_rep_entity_id:'||pn_vat_rep_entity_id
191 ||', pv_period_name:'||pv_period_name||', pn_period_year:'||pn_period_year||', pv_source:'||pv_source);
192 end if;
193
194 IF lc_tax_registration%ISOPEN THEN
195 CLOSE lc_tax_registration;
196 END IF;
197 OPEN lc_tax_registration;
198
199 FETCH lc_tax_registration INTO x_tax_registration
200 ,x_period_start_date
201 ,x_period_end_date;
202 CLOSE lc_tax_registration;
203
204 x_status := NULL;
205
206 /*x_status := jg_zz_vat_rep_utility.get_period_status(pn_vat_rep_entity_id
207 ,pv_period_name
208 ,pv_source); */
209
210 if gv_debug_flag then
211 fnd_file.put_line(fnd_file.log, 'End CMN.tax_registration. x_tax_registration:'||x_tax_registration
212 ||', x_period_start_date:'||x_period_start_date||', x_period_end_date:'||x_period_end_date);
216 WHEN NO_DATA_FOUND THEN
213 end if;
214
215 EXCEPTION
217 FND_FILE.PUT_LINE(FND_FILE.LOG,'No TRN Period Details found in JG table.'||SUBSTR(SQLERRM,1,200));
218 WHEN OTHERS THEN
219 RAISE;
220 END tax_registration;
221
222 PROCEDURE company_detail(x_company_name OUT NOCOPY VARCHAR2
223 ,x_registration_number OUT NOCOPY VARCHAR2
224 ,x_country OUT NOCOPY VARCHAR2
225 ,x_address1 OUT NOCOPY VARCHAR2
226 ,x_address2 OUT NOCOPY VARCHAR2
227 ,x_address3 OUT NOCOPY VARCHAR2
228 ,x_address4 OUT NOCOPY VARCHAR2
229 ,x_city OUT NOCOPY VARCHAR2
230 ,x_postal_code OUT NOCOPY VARCHAR2
231 ,x_contact OUT NOCOPY VARCHAR2
232 ,x_phone_number OUT NOCOPY VARCHAR2
233 ,x_province OUT NOCOPY VARCHAR2
234 ,x_comm_number OUT NOCOPY VARCHAR2
235 ,x_vat_reg_num OUT NOCOPY VARCHAR2
236 ,pn_legal_entity_id IN NUMBER
237 ,p_vat_reporting_entity_id IN NUMBER
238 )
239
240 -- +===================================================================================+
241 -- | Name : company_detail |
242 -- | Description : company_detail procedure. |
243 -- | This procedure is used to fetch |
244 -- | Company Name |
245 -- | ,Registration Number |
246 -- | ,Country |
247 -- | ,Address1 |
248 -- | ,Address2 |
249 -- | ,Address3 |
250 -- | ,Address4 |
251 -- | ,City |
252 -- | ,Postal Code |
253 -- | ,Contact |
254 -- | ,Phone Number |
255 -- | Parameters : |
256 -- | x_company_name : Company Name |
257 -- | x_registration_number : Registration Number |
258 -- | x_country : Country |
259 -- | x_address1 : Address1 |
260 -- | x_address2 : Address2 |
261 -- | x_address3 : Address3 |
262 -- | x_address4 : Address4 |
263 -- | x_city : City |
264 -- | x_postal_code : Postal Code |
265 -- | x_contact : Contact Person |
266 -- | x_phone_number : Phone Number |
267 -- | pn_legal_entity_id : Legal Entity Identifier or Operating Unit identifier |
268 -- +===================================================================================+
269
270 IS
271
272 CURSOR lc_company_detail IS
273 SELECT XR.registered_name
274 ,XR.registration_number
275 ,FT.territory_short_name
276 ,HL.address_line_1
277 ,HL.address_line_2
278 ,HL.address_line_3
279 ,null address4
280 ,HL.town_or_city
281 ,HL.postal_code
282 --,HP.party_name This now retreived in the second cursor
283 ,hp.party_id
284 -- Adding code for the GLOB006-ER
285 ,HL.region_1
286 FROM xle_registrations XR
287 ,xle_entity_profiles XEP
288 ,hr_locations_all HL
289 ,hz_parties HP
290 ,fnd_territories_vl FT
291 WHERE XR.source_id = XEP.legal_entity_id
292 AND XR.source_table = 'XLE_ENTITY_PROFILES'
293 AND XEP.legal_entity_id = pn_legal_entity_id
294 AND XR.location_id = HL.location_id
295 AND HL.country = FT.TERRITORY_CODE
296 AND XEP.party_id = HP.party_id
297 and xr.identifying_flag = 'Y';
298
299
300
301 /*CURSOR lc_contact_phone(cp_party_id in number) IS
302 SELECT HCP.phone_number
303 FROM hz_contact_points HCP
304 WHERE HCP.owner_table_id = cp_party_id
305 AND HCP.owner_table_name = 'HZ_PARTIES'
306 AND HCP.primary_flag = 'Y'
307 AND HCP.contact_point_type= 'PHONE'
308 AND HCP.status = 'A';*/
309 /*
310 Commented the above code. The Logic to get the Contact Information
311 has been modified and the below cursor has been added for same.
312 Now both the contact name and Contact phone no is being retreived from same table.
313 */
314 CURSOR lc_contact_phone(cp_party_id in number) IS
315 SELECT per.party_name
319 ,HZ_PARTIES PER
316 ,per.primary_phone_number
317 FROM HZ_PARTIES HP
318 ,HZ_RELATIONSHIPS REL
320 WHERE HP.PARTY_ID = cp_party_id
321 AND rel.object_id = HP.PARTY_ID
322 AND rel.subject_id = per.party_id
323 AND rel.relationship_code = 'CONTACT_OF'
324 AND rel.relationship_type = 'CONTACT'
325 AND rel.directional_flag = 'F'
326 AND rel.subject_table_name = 'HZ_PARTIES'
327 AND rel.subject_type = 'PERSON'
328 AND rel.object_table_name = 'HZ_PARTIES'
329 AND Trunc(Nvl(rel.end_date, SYSDATE)) > TRUNC(SYSDATE);
330
331 /* Added for GLOBE-006 ER*/
332 CURSOR lc_commercial_num IS
333 select nvl(xler.registration_number,'') commercial_number
334 from XLE_REGISTRATIONS xler, XLE_JURISDICTIONS_B xlej, xle_entity_profiles xlee
335 where xlej.JURISDICTION_ID= xler.JURISDICTION_ID
336 and xlej.LEGISLATIVE_CAT_CODE = 'COMMERCIAL_LAW'
337 AND xler.source_id = xlee.LEGAL_ENTITY_ID
338 AND xler.source_TABLE = 'XLE_ENTITY_PROFILES'
339 and xlee.legal_entity_id = pn_legal_entity_id;
340
341 ln_party_id HZ_PARTIES.party_id%TYPE;
342
343 BEGIN
344
345 if gv_debug_flag then
346 fnd_file.put_line(fnd_file.log, 'Start CMN.company_detail. pn_legal_entity_id:'||pn_legal_entity_id);
347 end if;
348
349 -- Added for GLOB-006
350 IF p_vat_reporting_entity_id is null THEN -- NONEMEAVAT REPORT
351 BEGIN
352 SELECT zptp.REP_REGISTRATION_NUMBER
353 INTO x_vat_reg_num
354 FROM ZX_PARTY_TAX_PROFILE zptp
355 ,XLE_ETB_PROFILES xetbp
356 WHERE zptp.PARTY_TYPE_CODE = 'LEGAL_ESTABLISHMENT'
357 AND xetbp.party_id=zptp.party_id
358 AND xetbp.MAIN_ESTABLISHMENT_FLAG = 'Y'
359 AND xetbp.LEGAL_ENTITY_ID = pn_legal_entity_id
360 AND trunc(sysdate) BETWEEN trunc(nvl(xetbp.main_effective_from, sysdate)) AND trunc(nvl(xetbp.main_effective_to, sysdate));
361 EXCEPTION
362 WHEN NO_DATA_FOUND THEN
363 FND_FILE.PUT_LINE(FND_FILE.LOG,'No VAT registration_number found. '||SUBSTR(SQLERRM,1,200));
364 WHEN OTHERS THEN
365 RAISE;
366 END;
367 ELSE -- EMEAVAT REPORT
368 BEGIN
369 SELECT cfgd.TAX_REGISTRATION_NUMBER
370 INTO x_vat_reg_num
371 FROM jg_zz_vat_rep_entities cfg
372 ,jg_zz_vat_rep_entities cfgd
373 WHERE cfg.vat_reporting_entity_id = p_vat_reporting_entity_id
374 AND ( ( cfg.entity_type_code = 'ACCOUNTING'
375 AND cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id
376 )
377 OR
378 ( cfg.entity_type_code = 'LEGAL'
379 AND cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id
380 )
381 );
382 EXCEPTION
383 WHEN NO_DATA_FOUND THEN
384 FND_FILE.PUT_LINE(FND_FILE.LOG,'No VAT registration_number found. '||SUBSTR(SQLERRM,1,200));
385 WHEN OTHERS THEN
386 RAISE;
387 END;
388 END IF;
389 -- END HERE for GLOB-006
390
391 IF lc_company_detail%ISOPEN THEN
392 CLOSE lc_company_detail;
393 END IF;
394 OPEN lc_company_detail;
395 FETCH lc_company_detail INTO x_company_name
396 ,x_registration_number
397 ,x_country
398 ,x_address1
399 ,x_address2
400 ,x_address3
401 ,x_address4
402 ,x_city
403 ,x_postal_code
404 --,x_contact
405 ,ln_party_id
406 ,x_province; -- Added for GLOB-006
407
408 CLOSE lc_company_detail;
409
410 IF lc_contact_phone%ISOPEN THEN
411 CLOSE lc_contact_phone;
412 END IF;
413
414 OPEN lc_contact_phone(ln_party_id);
415 FETCH lc_contact_phone INTO x_contact,x_phone_number;
416 CLOSE lc_contact_phone;
417
418 OPEN lc_commercial_num;
419 FETCH lc_commercial_num INTO x_comm_number;
420 CLOSE lc_commercial_num;
421
422
423 if gv_debug_flag then
424 fnd_file.put_line(fnd_file.log, 'Start CMN.company_detail. x_company_name:'||x_company_name||', ln_party_id:'||ln_party_id||', x_registration_number:'||x_registration_number);
425 end if;
426
427 EXCEPTION
428 WHEN NO_DATA_FOUND THEN
429 FND_FILE.PUT_LINE(FND_FILE.LOG,'No LE Company Information found. '||SUBSTR(SQLERRM,1,200));
430 WHEN OTHERS THEN
431 RAISE;
432 END company_detail;
433
434 PROCEDURE get_entities_configuration_dtl(x_calendar_name OUT NOCOPY VARCHAR2
435 ,x_enable_register_flag OUT NOCOPY VARCHAR2
436 ,x_enable_report_seq_flag OUT NOCOPY VARCHAR2
437 ,x_enable_alloc_flag OUT NOCOPY VARCHAR2
438 ,x_enable_annual_alloc_flag OUT NOCOPY VARCHAR2
439 ,x_threshold_amt OUT NOCOPY VARCHAR2
440 ,x_entity_identifier OUT NOCOPY VARCHAR2
441 ,p_vat_rep_entity_id IN NUMBER)
442 -- +===========================================================================+
443 -- | Name : get_entities_configuration_dtl |
444 -- | Description : get_entities_configuration_dtl procedure. |
445 -- | This procedure is used to fetch configurable setup|
446 -- | details for LEGAL/ACCOUNTING vat reporting entity.|
447 -- | Input Parameters : |
448 -- | p_vat_rep_entity_id : Vat Reporting Entity Id |
449 -- +===========================================================================+
450 IS
451 CURSOR lc_get_entities_config_details IS
452 SELECT legal_rep_entity.TAX_CALENDAR_NAME,
453 legal_rep_entity.ENABLE_REGISTERS_FLAG,
454 legal_rep_entity.ENABLE_REPORT_SEQUENCE_FLAG,
455 legal_rep_entity.ENABLE_ALLOCATIONS_FLAG,
456 legal_rep_entity.ENABLE_ANNUAL_ALLOCATION_FLAG,
457 legal_rep_entity.THRESHOLD_AMOUNT,
458 actg_rep_entity.ENTITY_IDENTIFIER
459 FROM JG_ZZ_VAT_REP_ENTITIES actg_rep_entity,
460 JG_ZZ_VAT_REP_ENTITIES legal_rep_entity
461 WHERE actg_rep_entity.vat_reporting_entity_id = p_vat_rep_entity_id
462 AND nvl(actg_rep_entity.mapping_vat_rep_entity_id,
463 actg_rep_entity.vat_reporting_entity_id)
464 = legal_rep_entity.vat_reporting_entity_id;
465
466 BEGIN
467
468 if gv_debug_flag then
469 fnd_file.put_line(fnd_file.log,
470 'Start CMN.get_entities_configuration_dtl. pn_vat_rep_entity_id:'
471 ||p_vat_rep_entity_id
472 );
473 end if;
474
475 IF lc_get_entities_config_details%ISOPEN THEN
476 CLOSE lc_get_entities_config_details;
477 END IF;
478 OPEN lc_get_entities_config_details;
479
480 FETCH lc_get_entities_config_details INTO x_calendar_name
481 ,x_enable_register_flag
482 ,x_enable_report_seq_flag
483 ,x_enable_alloc_flag
484 ,x_enable_annual_alloc_flag
485 ,x_threshold_amt
486 ,x_entity_identifier;
487 CLOSE lc_get_entities_config_details;
488
489 if gv_debug_flag then
490 fnd_file.put_line(fnd_file.log, 'End CMN.get_entities_configuration_dtl. x_calendar_name:'||x_calendar_name
491 ||', x_enable_register_flag:'||x_enable_register_flag||', x_enable_report_seq_flag:'||x_enable_report_seq_flag
492 ||', x_enable_alloc_flag:'||x_enable_alloc_flag||', x_enable_annual_alloc_flag:'||x_enable_annual_alloc_flag
493 ||', x_threshold_amt:'|| x_threshold_amt);
494 end if;
495
496 EXCEPTION
497 WHEN NO_DATA_FOUND THEN
498 FND_FILE.PUT_LINE(FND_FILE.LOG,'This VAT reporting entity does not exists.'||SUBSTR(SQLERRM,1,200));
499 WHEN OTHERS THEN
500 RAISE;
501 END get_entities_configuration_dtl;
502
503 FUNCTION get_legal_entity_country_code(p_legal_entity_id IN NUMBER) RETURN VARCHAR2 IS
504 l_country_code varchar2(10);
505 BEGIN
506
507 SELECT HL.country
508 INTO l_country_code
509 FROM xle_registrations XR
510 ,xle_entity_profiles XEP
511 ,hr_locations_all HL
512 WHERE XR.source_id = XEP.legal_entity_id
513 AND XR.source_table = 'XLE_ENTITY_PROFILES'
514 AND XEP.legal_entity_id = p_legal_entity_id
515 AND XR.location_id = HL.location_id
516 AND xr.identifying_flag = 'Y';
517
518 RETURN l_country_code;
519
520 EXCEPTION
521 WHEN OTHERS THEN
522 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in getting country code'||SUBSTR(SQLERRM,1,200));
523 RETURN NULL;
524 END get_legal_entity_country_code;
525
526 FUNCTION get_amt_tot (
527 pn_invoice_id NUMBER,
528 pn_ledger_id NUMBER,
529 pn_precision NUMBER
530 )
531 RETURN NUMBER IS
532 l_amt NUMBER;
533
534 BEGIN
535
536 IF ( pn_ledger_id = -1 )
537 THEN
538 select nvl(base_amount,invoice_amount) into l_amt
539 from ap_invoices_all
540 where invoice_id = pn_invoice_id;
541
542 ELSE
543
544 SELECT SUM (ROUND (NVL (xal.accounted_cr, 0), pn_precision))
545 - SUM (ROUND (NVL (xal.accounted_dr, 0), pn_precision))
546 into l_amt
547 FROM xla_transaction_entities xte,
548 xla_events xe,
549 xla_ae_headers xah,
550 xla_ae_lines xal
551 WHERE xte.entity_id = xe.entity_id
552 AND xe.application_id = 200
553 AND xah.event_id = xe.event_id
554 AND xah.application_id = 200
555 AND xte.application_id = 200
556 AND xah.ledger_id = pn_ledger_id
557 AND NVL (xe.budgetary_control_flag, 'N') = 'N'
558 AND xe.event_status_code = 'P'
559 and xe.process_status_code = 'P'
560 and xe.EVENT_TYPE_CODE not in ('PREPAYMENT APPLIED','PREPAYMENT UNAPPLIED') --bug 12779390
561 AND xal.ae_header_id = xah.ae_header_id
562 AND xal.accounting_class_code = 'LIABILITY'
563 AND xte.entity_code = 'AP_INVOICES'
564 AND NVL (xte.source_id_int_1, -99) = pn_invoice_id;
565
566 END IF;
567 RETURN l_amt;
568
569 EXCEPTION
570 WHEN OTHERS THEN
571 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in getting invoice_amount'||SUBSTR(SQLERRM,1,200));
572 Return NULL;
573
574 END get_amt_tot;
575
576 END JG_ZZ_COMMON_PKG;
[Home] [Help]