1 PACKAGE BODY JG_ZZ_COMMON_PKG AS
2 /* $Header: jgzzvatcmnb.pls 120.11.12010000.5 2009/01/16 16:25:45 vkejriwa ship $
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
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
24 Also added the debug code
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 *********************************************************************************** */
52
53
54 gv_debug_flag constant boolean := false;
55
56 PROCEDURE funct_curr_legal(x_func_curr_code OUT NOCOPY VARCHAR2
57 ,x_rep_entity_name OUT NOCOPY VARCHAR2
58 ,x_legal_entity_id OUT NOCOPY NUMBER
59 ,x_taxpayer_id OUT NOCOPY VARCHAR2
60 ,pn_vat_rep_entity_id IN NUMBER
61 ,pv_period_name IN VARCHAR2 DEFAULT NULL
62 ,pn_period_year IN NUMBER DEFAULT NULL)
63 -- +==================================================================================+
64 -- | Name : funct_curr_legal |
65 -- | Description : funct_curr_legal procedure. |
66 -- | This procedure is used to fetch Functional_currency_code, |
67 -- | rep_context_entity_name,rep_entity_id. |
68 -- | Parameters : |
69 -- | x_func_curr_code : Return Functional_currency_code |
70 -- | x_rep_entity_name : Return rep_context_entity_name |
71 -- | x_legal_entity_id : Return Legal Entity Id |
72 -- | x_taxpayer_id : Tax Payer Id |
73 -- | pn_vat_rep_entity_id: Vat Reporting Entity Id |
74 -- | pv_period_name : Period Name |
75 -- | pn_period_year : Period Year |
76 -- +==================================================================================+
77 IS
78 CURSOR lc_funct_curr_legal IS
79 SELECT DECODE(JZVRE.entity_level_code,'LEDGER',GL.currency_code,nvl(JZVTD.functional_currency_code, jzvtd.trx_currency_code))
80 ,JZVTD.rep_context_entity_name
81 ,JZVTD.rep_entity_id
82 ,JZVTD.taxpayer_id
83 FROM jg_zz_vat_trx_details JZVTD
84 ,jg_zz_vat_rep_status JZVRS
85 ,jg_zz_vat_rep_entities JZVRE
86 ,gl_ledgers GL
87 WHERE JZVTD.reporting_status_id = JZVRS.reporting_status_id
88 AND JZVRS.vat_reporting_entity_id= pn_vat_rep_entity_id
89 AND (JZVRS.tax_calendar_period = pv_period_name
90 OR JZVRS.tax_calendar_year = pn_period_year)
91 AND JZVRE.vat_reporting_entity_id= JZVRS.vat_reporting_entity_id
92 and DECODE(JZVRE.entity_level_code,'LEDGER',JZVRE.ledger_id,1)=DECODE(entity_level_code,'LEDGER',GL.ledger_id,1)
93 AND rownum = 1;
94
95 CURSOR lc_get_le_id IS
96 SELECT cfgd.legal_entity_id
97 FROM jg_zz_vat_rep_entities cfg
98 ,jg_zz_vat_rep_entities cfgd
99 WHERE cfg.vat_reporting_entity_id = pn_vat_rep_entity_id
100 AND (
101 ( cfg.entity_type_code = 'ACCOUNTING'
102 and cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id
103 )
104 or
105 ( cfg.entity_type_code = 'LEGAL'
106 and cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id
107 ));
108
109 BEGIN
110
111 if gv_debug_flag then
112 fnd_file.put_line(fnd_file.log, 'Start CMN.funct_curr_legal. pn_vat_rep_entity_id:'||pn_vat_rep_entity_id
113 ||', pv_period_name:'||pv_period_name||', pn_period_year:'||pn_period_year);
114 end if;
115
116 IF lc_funct_curr_legal%ISOPEN THEN
117 CLOSE lc_funct_curr_legal;
118 END IF;
119 OPEN lc_funct_curr_legal;
120 FETCH lc_funct_curr_legal INTO x_func_curr_code
121 ,x_rep_entity_name
122 ,x_legal_entity_id
123 ,x_taxpayer_id;
124
125 CLOSE lc_funct_curr_legal;
126
127 IF lc_get_le_id%ISOPEN THEN
128 CLOSE lc_get_le_id;
129 END IF;
130
131 OPEN lc_get_le_id;
132 FETCH lc_get_le_id INTO x_legal_entity_id;
133 CLOSE lc_get_le_id;
134
135 EXCEPTION
136 WHEN NO_DATA_FOUND THEN
137 FND_FILE.PUT_LINE(FND_FILE.LOG,' No Header Information found in JG table. '|| SUBSTR(SQLERRM,1,200));
138 WHEN OTHERS THEN
139 RAISE;
140 END funct_curr_legal;
141
142 PROCEDURE tax_registration(x_tax_registration OUT NOCOPY VARCHAR2
143 ,x_period_start_date OUT NOCOPY DATE
144 ,x_period_end_date OUT NOCOPY DATE
145 ,x_status OUT NOCOPY VARCHAR2
146 ,pn_vat_rep_entity_id IN NUMBER
147 ,pv_period_name IN VARCHAR2 DEFAULT NULL
148 ,pn_period_year IN NUMBER DEFAULT NULL
149 ,pv_source IN VARCHAR2)
150 -- +==================================================================================+
151 -- | Name : funct_curr_legal |
152 -- | Description : funct_curr_legal procedure. |
153 -- | This procedure is used to fetch tax_registration_number, |
154 -- | period_start_date,period_end_date,status. |
155 -- | Parameters : |
156 -- | x_tax_registration : Return tax_registration_number |
157 -- | x_period_start_date : Return period_start_date |
158 -- | x_period_end_date : Return period_end_date |
159 -- | x_status : Status (P=Preliminary, F=Final) |
160 -- | pn_vat_rep_entity_id : Vat Reporting Entity Id |
161 -- | pv_period_name : Period Name |
162 -- | pn_period_year : Period Year |
163 -- | pv_source : Possible values for Source are -> AP, AR, GL, ALL |
164 -- +==================================================================================+
165 IS
166 CURSOR lc_tax_registration IS
167 SELECT JZVRS.tax_registration_number
168 ,min(JZVRS.period_start_date)
169 ,max(JZVRS.period_end_date)
170 FROM jg_zz_vat_rep_status JZVRS
171 WHERE JZVRS.vat_reporting_entity_id= pn_vat_rep_entity_id
172 AND (JZVRS.tax_calendar_period = pv_period_name
173 OR JZVRS.tax_calendar_year = pn_period_year)
174 group by JZVRS.tax_registration_number;
175
176 BEGIN
177
178 if gv_debug_flag then
179 fnd_file.put_line(fnd_file.log, 'Start CMN.tax_registration. pn_vat_rep_entity_id:'||pn_vat_rep_entity_id
180 ||', pv_period_name:'||pv_period_name||', pn_period_year:'||pn_period_year||', pv_source:'||pv_source);
181 end if;
182
183 IF lc_tax_registration%ISOPEN THEN
184 CLOSE lc_tax_registration;
185 END IF;
186 OPEN lc_tax_registration;
187
188 FETCH lc_tax_registration INTO x_tax_registration
189 ,x_period_start_date
190 ,x_period_end_date;
191 CLOSE lc_tax_registration;
192
193 x_status := NULL;
194
195 /*x_status := jg_zz_vat_rep_utility.get_period_status(pn_vat_rep_entity_id
196 ,pv_period_name
197 ,pv_source); */
198
199 if gv_debug_flag then
200 fnd_file.put_line(fnd_file.log, 'End CMN.tax_registration. x_tax_registration:'||x_tax_registration
201 ||', x_period_start_date:'||x_period_start_date||', x_period_end_date:'||x_period_end_date);
202 end if;
203
204 EXCEPTION
205 WHEN NO_DATA_FOUND THEN
206 FND_FILE.PUT_LINE(FND_FILE.LOG,'No TRN Period Details found in JG table.'||SUBSTR(SQLERRM,1,200));
207 WHEN OTHERS THEN
208 RAISE;
209 END tax_registration;
210
211 PROCEDURE company_detail(x_company_name OUT NOCOPY VARCHAR2
212 ,x_registration_number OUT NOCOPY VARCHAR2
213 ,x_country OUT NOCOPY VARCHAR2
214 ,x_address1 OUT NOCOPY VARCHAR2
215 ,x_address2 OUT NOCOPY VARCHAR2
216 ,x_address3 OUT NOCOPY VARCHAR2
217 ,x_address4 OUT NOCOPY VARCHAR2
218 ,x_city OUT NOCOPY VARCHAR2
219 ,x_postal_code OUT NOCOPY VARCHAR2
220 ,x_contact OUT NOCOPY VARCHAR2
221 ,x_phone_number OUT NOCOPY VARCHAR2
222 ,x_province OUT NOCOPY VARCHAR2
223 ,x_comm_number OUT NOCOPY VARCHAR2
224 ,x_vat_reg_num OUT NOCOPY VARCHAR2
225 ,pn_legal_entity_id IN NUMBER
226 ,p_vat_reporting_entity_id IN NUMBER
227 )
228
229 -- +===================================================================================+
230 -- | Name : company_detail |
231 -- | Description : company_detail procedure. |
232 -- | This procedure is used to fetch |
233 -- | Company Name |
234 -- | ,Registration Number |
235 -- | ,Country |
236 -- | ,Address1 |
237 -- | ,Address2 |
238 -- | ,Address3 |
239 -- | ,Address4 |
240 -- | ,City |
241 -- | ,Postal Code |
242 -- | ,Contact |
243 -- | ,Phone Number |
244 -- | Parameters : |
245 -- | x_company_name : Company Name |
246 -- | x_registration_number : Registration Number |
247 -- | x_country : Country |
248 -- | x_address1 : Address1 |
249 -- | x_address2 : Address2 |
250 -- | x_address3 : Address3 |
251 -- | x_address4 : Address4 |
252 -- | x_city : City |
253 -- | x_postal_code : Postal Code |
254 -- | x_contact : Contact Person |
255 -- | x_phone_number : Phone Number |
256 -- | pn_legal_entity_id : Legal Entity Identifier or Operating Unit identifier |
257 -- +===================================================================================+
258
259 IS
260
261 CURSOR lc_company_detail IS
262 SELECT XR.registered_name
263 ,XR.registration_number
264 ,FT.territory_short_name
265 ,HL.address_line_1
266 ,HL.address_line_2
267 ,HL.address_line_3
268 ,null address4
269 ,HL.town_or_city
270 ,HL.postal_code
271 --,HP.party_name This now retreived in the second cursor
272 ,hp.party_id
273 -- Adding code for the GLOB006-ER
274 ,HL.region_1
275 FROM xle_registrations XR
276 ,xle_entity_profiles XEP
277 ,hr_locations_all HL
278 ,hz_parties HP
279 ,fnd_territories_vl FT
280 WHERE XR.source_id = XEP.legal_entity_id
281 AND XR.source_table = 'XLE_ENTITY_PROFILES'
282 AND XEP.legal_entity_id = pn_legal_entity_id
283 AND XR.location_id = HL.location_id
284 AND HL.country = FT.TERRITORY_CODE
285 AND XEP.party_id = HP.party_id
286 and xr.identifying_flag = 'Y';
287
288
289
290 /*CURSOR lc_contact_phone(cp_party_id in number) IS
291 SELECT HCP.phone_number
292 FROM hz_contact_points HCP
293 WHERE HCP.owner_table_id = cp_party_id
294 AND HCP.owner_table_name = 'HZ_PARTIES'
295 AND HCP.primary_flag = 'Y'
296 AND HCP.contact_point_type= 'PHONE'
297 AND HCP.status = 'A';*/
298 /*
299 Commented the above code. The Logic to get the Contact Information
300 has been modified and the below cursor has been added for same.
301 Now both the contact name and Contact phone no is being retreived from same table.
302 */
303 CURSOR lc_contact_phone(cp_party_id in number) IS
304 SELECT per.party_name
305 ,per.primary_phone_number
306 FROM HZ_PARTIES HP
307 ,HZ_RELATIONSHIPS REL
308 ,HZ_PARTIES PER
309 WHERE HP.PARTY_ID = cp_party_id
310 AND rel.object_id = HP.PARTY_ID
311 AND rel.subject_id = per.party_id
312 AND rel.relationship_code = 'CONTACT_OF'
313 AND rel.relationship_type = 'CONTACT'
314 AND rel.directional_flag = 'F'
315 AND rel.subject_table_name = 'HZ_PARTIES'
316 AND rel.subject_type = 'PERSON'
317 AND rel.object_table_name = 'HZ_PARTIES'
318 AND Trunc(Nvl(rel.end_date, SYSDATE)) > TRUNC(SYSDATE);
319
320 /* Added for GLOBE-006 ER*/
321 CURSOR lc_commercial_num IS
322 select nvl(xler.registration_number,'') commercial_number
323 from XLE_REGISTRATIONS xler, XLE_JURISDICTIONS_B xlej, xle_entity_profiles xlee
324 where xlej.JURISDICTION_ID= xler.JURISDICTION_ID
325 and xlej.LEGISLATIVE_CAT_CODE = 'COMMERCIAL_LAW'
326 AND xler.source_id = xlee.LEGAL_ENTITY_ID
327 AND xler.source_TABLE = 'XLE_ENTITY_PROFILES'
328 and xlee.legal_entity_id = pn_legal_entity_id;
329
330 ln_party_id HZ_PARTIES.party_id%TYPE;
331
332 BEGIN
333
334 if gv_debug_flag then
335 fnd_file.put_line(fnd_file.log, 'Start CMN.company_detail. pn_legal_entity_id:'||pn_legal_entity_id);
336 end if;
337
338 -- Added for GLOB-006
339 IF p_vat_reporting_entity_id is null THEN -- NONEMEAVAT REPORT
340 BEGIN
341 SELECT zptp.REP_REGISTRATION_NUMBER
342 INTO x_vat_reg_num
343 FROM ZX_PARTY_TAX_PROFILE zptp
344 ,XLE_ETB_PROFILES xetbp
345 WHERE zptp.PARTY_TYPE_CODE = 'LEGAL_ESTABLISHMENT'
346 AND xetbp.party_id=zptp.party_id
347 AND xetbp.MAIN_ESTABLISHMENT_FLAG = 'Y'
348 AND xetbp.LEGAL_ENTITY_ID = pn_legal_entity_id;
349 EXCEPTION
350 WHEN NO_DATA_FOUND THEN
351 FND_FILE.PUT_LINE(FND_FILE.LOG,'No VAT registration_number found. '||SUBSTR(SQLERRM,1,200));
352 WHEN OTHERS THEN
353 RAISE;
354 END;
355 ELSE -- EMEAVAT REPORT
356 BEGIN
357 SELECT cfgd.TAX_REGISTRATION_NUMBER
358 INTO x_vat_reg_num
359 FROM jg_zz_vat_rep_entities cfg
360 ,jg_zz_vat_rep_entities cfgd
361 WHERE cfg.vat_reporting_entity_id = p_vat_reporting_entity_id
362 AND ( ( cfg.entity_type_code = 'ACCOUNTING'
363 AND cfg.mapping_vat_rep_entity_id = cfgd.vat_reporting_entity_id
364 )
365 OR
366 ( cfg.entity_type_code = 'LEGAL'
367 AND cfg.vat_reporting_entity_id = cfgd.vat_reporting_entity_id
368 )
369 );
370 EXCEPTION
371 WHEN NO_DATA_FOUND THEN
372 FND_FILE.PUT_LINE(FND_FILE.LOG,'No VAT registration_number found. '||SUBSTR(SQLERRM,1,200));
373 WHEN OTHERS THEN
374 RAISE;
375 END;
376 END IF;
377 -- END HERE for GLOB-006
378
379 IF lc_company_detail%ISOPEN THEN
380 CLOSE lc_company_detail;
381 END IF;
382 OPEN lc_company_detail;
383 FETCH lc_company_detail INTO x_company_name
384 ,x_registration_number
385 ,x_country
386 ,x_address1
387 ,x_address2
388 ,x_address3
389 ,x_address4
390 ,x_city
391 ,x_postal_code
392 --,x_contact
393 ,ln_party_id
394 ,x_province; -- Added for GLOB-006
395
396 CLOSE lc_company_detail;
397
398 IF lc_contact_phone%ISOPEN THEN
399 CLOSE lc_contact_phone;
400 END IF;
401
402 OPEN lc_contact_phone(ln_party_id);
403 FETCH lc_contact_phone INTO x_contact,x_phone_number;
404 CLOSE lc_contact_phone;
405
406 OPEN lc_commercial_num;
407 FETCH lc_commercial_num INTO x_comm_number;
408 CLOSE lc_commercial_num;
409
410
411 if gv_debug_flag then
412 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);
413 end if;
414
415 EXCEPTION
416 WHEN NO_DATA_FOUND THEN
417 FND_FILE.PUT_LINE(FND_FILE.LOG,'No LE Company Information found. '||SUBSTR(SQLERRM,1,200));
418 WHEN OTHERS THEN
419 RAISE;
420 END company_detail;
421
422 PROCEDURE get_entities_configuration_dtl(x_calendar_name OUT NOCOPY VARCHAR2
423 ,x_enable_register_flag OUT NOCOPY VARCHAR2
424 ,x_enable_report_seq_flag OUT NOCOPY VARCHAR2
425 ,x_enable_alloc_flag OUT NOCOPY VARCHAR2
426 ,x_enable_annual_alloc_flag OUT NOCOPY VARCHAR2
427 ,x_threshold_amt OUT NOCOPY VARCHAR2
428 ,x_entity_identifier OUT NOCOPY VARCHAR2
429 ,p_vat_rep_entity_id IN NUMBER)
430 -- +===========================================================================+
431 -- | Name : get_entities_configuration_dtl |
432 -- | Description : get_entities_configuration_dtl procedure. |
433 -- | This procedure is used to fetch configurable setup|
434 -- | details for LEGAL/ACCOUNTING vat reporting entity.|
435 -- | Input Parameters : |
436 -- | p_vat_rep_entity_id : Vat Reporting Entity Id |
437 -- +===========================================================================+
438 IS
439 CURSOR lc_get_entities_config_details IS
440 SELECT legal_rep_entity.TAX_CALENDAR_NAME,
441 legal_rep_entity.ENABLE_REGISTERS_FLAG,
442 legal_rep_entity.ENABLE_REPORT_SEQUENCE_FLAG,
443 legal_rep_entity.ENABLE_ALLOCATIONS_FLAG,
444 legal_rep_entity.ENABLE_ANNUAL_ALLOCATION_FLAG,
445 legal_rep_entity.THRESHOLD_AMOUNT,
446 actg_rep_entity.ENTITY_IDENTIFIER
447 FROM JG_ZZ_VAT_REP_ENTITIES actg_rep_entity,
448 JG_ZZ_VAT_REP_ENTITIES legal_rep_entity
449 WHERE actg_rep_entity.vat_reporting_entity_id = p_vat_rep_entity_id
450 AND nvl(actg_rep_entity.mapping_vat_rep_entity_id,
451 actg_rep_entity.vat_reporting_entity_id)
452 = legal_rep_entity.vat_reporting_entity_id;
453
454 BEGIN
455
456 if gv_debug_flag then
457 fnd_file.put_line(fnd_file.log,
458 'Start CMN.get_entities_configuration_dtl. pn_vat_rep_entity_id:'
459 ||p_vat_rep_entity_id
460 );
461 end if;
462
463 IF lc_get_entities_config_details%ISOPEN THEN
464 CLOSE lc_get_entities_config_details;
465 END IF;
466 OPEN lc_get_entities_config_details;
467
468 FETCH lc_get_entities_config_details INTO x_calendar_name
469 ,x_enable_register_flag
470 ,x_enable_report_seq_flag
471 ,x_enable_alloc_flag
472 ,x_enable_annual_alloc_flag
473 ,x_threshold_amt
474 ,x_entity_identifier;
475 CLOSE lc_get_entities_config_details;
476
477 if gv_debug_flag then
478 fnd_file.put_line(fnd_file.log, 'End CMN.get_entities_configuration_dtl. x_calendar_name:'||x_calendar_name
479 ||', x_enable_register_flag:'||x_enable_register_flag||', x_enable_report_seq_flag:'||x_enable_report_seq_flag
480 ||', x_enable_alloc_flag:'||x_enable_alloc_flag||', x_enable_annual_alloc_flag:'||x_enable_annual_alloc_flag
481 ||', x_threshold_amt:'|| x_threshold_amt);
482 end if;
483
484 EXCEPTION
485 WHEN NO_DATA_FOUND THEN
486 FND_FILE.PUT_LINE(FND_FILE.LOG,'This VAT reporting entity does not exists.'||SUBSTR(SQLERRM,1,200));
487 WHEN OTHERS THEN
488 RAISE;
489 END get_entities_configuration_dtl;
490
491 FUNCTION get_legal_entity_country_code(p_legal_entity_id IN NUMBER) RETURN VARCHAR2 IS
492 l_country_code varchar2(10);
493 BEGIN
494
495 SELECT HL.country
496 INTO l_country_code
497 FROM xle_registrations XR
498 ,xle_entity_profiles XEP
499 ,hr_locations_all HL
500 WHERE XR.source_id = XEP.legal_entity_id
501 AND XR.source_table = 'XLE_ENTITY_PROFILES'
502 AND XEP.legal_entity_id = p_legal_entity_id
503 AND XR.location_id = HL.location_id
504 AND xr.identifying_flag = 'Y';
505
506 RETURN l_country_code;
507
508 EXCEPTION
509 WHEN OTHERS THEN
510 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in getting country code'||SUBSTR(SQLERRM,1,200));
511 RETURN NULL;
512 END get_legal_entity_country_code;
513
514 FUNCTION get_amt_tot (
515 pn_invoice_id NUMBER,
516 pn_ledger_id NUMBER,
517 pn_precision NUMBER
518 )
519 RETURN NUMBER IS
520 l_amt NUMBER;
521
522 BEGIN
523
524 IF ( pn_ledger_id = -1 )
525 THEN
526 select nvl(base_amount,invoice_amount) into l_amt
527 from ap_invoices_all
528 where invoice_id = pn_invoice_id;
529
530 ELSE
531
532 SELECT
533 sum(round(nvl(xdl.unrounded_accounted_cr,0),pn_precision)) -
534 sum(round(nvl(xdl.unrounded_accounted_dr,0),pn_precision))
535 into l_amt
536 from ap_invoice_distributions_all aid
537 ,xla_ae_headers xah
538 ,xla_ae_lines xal
539 ,xla_distribution_links xdl
540 where aid.invoice_id = pn_invoice_id--13245
541 and xah.ledger_id = pn_ledger_id--2050
542 and aid.posted_flag ='Y'
543 and aid.accounting_event_id = xah.event_id
544 and xah.ae_header_id = xal.ae_header_id
545 and xah.application_id =200
546 and xal.application_id =200
547 and xal.accounting_class_code = 'LIABILITY'
548 and xal.ae_header_id = xdl.ae_header_id
549 and xal.ae_line_num = xdl.ae_line_num
550 and aid.accounting_event_id = xdl.event_id
551 and xdl.application_id =200
552 and xdl.source_distribution_id_num_1 = aid.invoice_distribution_id;
553
554 END IF;
555 RETURN l_amt;
556
557 EXCEPTION
558 WHEN OTHERS THEN
559 FND_FILE.PUT_LINE(FND_FILE.LOG,'Exception in getting invoice_amount'||SUBSTR(SQLERRM,1,200));
560 Return NULL;
561
562 END get_amt_tot;
563
564 END JG_ZZ_COMMON_PKG;
[Home] [Help]