1 package body jg_zz_vat_rep_utility as
2 /* $Header: jgzzvatreputil_b.pls 120.9.12020000.2 2012/07/23 07:58:50 mbarrett ship $*/
3 /* --------------------------------------------------------------------------
4 CHANGE HISTORY:
5 S.No Date Author and Details
6
7 1. 24-jan-2006 Aparajita. Created Version#120.0.
8
9 29-Mar-2006 Aparajita. Modified for revised approach.Version#120.2
10
11 28-Apr-2006 Aparajita. Version#120.3
12 For ALLOCATION, pre_process_update is called only when
13 pv_reallocate_flag <> 'Y' in validate_process_initiation.
14
15 31-May-2006 Aparajita. Version#120.4. xBuild-6
16 changed comparison with 'Y' to compare against
17 fnd_api.g_ret_sts_success in get_last_processed_date procedure.
18 Also in decode, if value is not g_ret_sts_success,
19 changed from 0 to null as count function is being used.
20
21 Added code to hanlde ALL for pv_source
22 in get_last_processed_date procedure.
23
24 2. 23-Jun-2006 Bhavik. Version 120.5 xBuild-7
25 Added third argument lx_row_id in call to jg_zz_vat_rep_entities_pkg.insert_row API,
26 as the signature of this API has been changed. Please refer bug# 5166688 for details
27 regarding change in signature of table handler APIs.
28
29 3. 10-jul-2006 Aparajita. Version#120.6. UT bug fix.
30 Added new value QUERY for parameter pv_called_from of get_reporting_identifier.
31 This functional is now also used from data templates to get the reporting identifier
32 to print it in the template. Added a generic value of QUERY for this purpose.
33 Currently it has the same functionality as that of TABLE HANDLER. However, in this case,
34 the passed entity would always exist.
35 4. 29-Dec-2006 Bug: 5584049. Changed signature of get_period_status function. Added parameter p_vat_register_id.
36 This new parameter will be used for determining the reporting mode of the Italian VAT Registers run.
37
38 -------------------------------------------------------------------------- */
39
40 /* ================================== start of insert_rows - INTERNAL procedure =============================== */
41 procedure insert_row
42 (
43 xn_reporting_status_id out nocopy jg_zz_vat_rep_status.reporting_status_id%type,
44 pn_vat_reporting_entity_id in jg_zz_vat_rep_status.vat_reporting_entity_id%type,
45 pv_tax_registration_number in jg_zz_vat_rep_status.tax_registration_number%type,
46 pv_tax_calendar_name in jg_zz_vat_rep_status.tax_calendar_name%type,
47 pn_tax_calendar_year in jg_zz_vat_rep_status.tax_calendar_year%type,
48 pv_tax_calendar_period in jg_zz_vat_rep_status.tax_calendar_period%type,
49 pv_source in jg_zz_vat_rep_status.source%type,
50 pd_period_start_date in jg_zz_vat_rep_status.period_start_date%type,
51 pd_period_end_date in jg_zz_vat_rep_status.period_end_date%type,
52 pn_mapping_vat_rep_entity_id in jg_zz_vat_rep_status.mapping_vat_rep_entity_id%type
53 )
54 is
55
56 begin
57
58 insert into jg_zz_vat_rep_status
59 (
60 reporting_status_id ,
61 vat_reporting_entity_id ,
62 tax_registration_number ,
63 tax_calendar_name ,
64 tax_calendar_year ,
65 tax_calendar_period ,
66 source ,
67 period_start_date ,
68 period_end_date ,
69 request_id ,
70 program_application_id ,
71 program_id ,
72 program_update_date ,
73 program_login_id ,
74 created_by ,
75 creation_date ,
76 last_updated_by ,
77 last_update_date ,
78 last_update_login ,
79 mapping_vat_rep_entity_id /* Revised Approach Change */
80 )
81 values
82 (
83 jg_zz_vat_rep_status_s.nextval ,
84 pn_vat_reporting_entity_id ,
85 pv_tax_registration_number ,
86 pv_tax_calendar_name ,
87 pn_tax_calendar_year ,
88 pv_tax_calendar_period ,
89 pv_source ,
90 pd_period_start_date ,
91 pd_period_end_date ,
92 fnd_profile.value('CONC_REQUEST_ID') ,
93 fnd_profile.value('PROG_APPL_ID') ,
94 fnd_profile.value('CONC_PROGRAM_ID') ,
95 trunc(sysdate) ,
96 fnd_profile.value('CONC_LOGIN_ID') ,
97 fnd_global.user_id ,
98 sysdate ,
99 fnd_global.user_id ,
100 sysdate ,
101 fnd_global.login_id ,
102 pn_mapping_vat_rep_entity_id
103 ) returning reporting_status_id into xn_reporting_status_id ;
104
105 end insert_row;
106
107 /* ================================== end of insert_rows =============================== */
108
109 /* =========================== Start of get_last_processed_date ======================= */
110 function get_last_processed_date
111 (
112 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
113 pv_source in jg_zz_vat_rep_status.source%type,
114 pv_process_name in varchar2 /* possible values - SELECTION, ALLOCATION, FINAL REPORTING */
115 )
116 return date is
117
118 lv_source jg_zz_vat_rep_status.source%type;
119 ld_last_processed_date date;
120 ld_last_processed_date_source date;
121
122 cursor c_get_last_processed_date_src ( pn_vat_reporting_entity_id number, pv_source varchar2, pv_process_name varchar2 ) is
123 select
124 max(period_end_date)
125 from
126 jg_zz_vat_rep_status
127 where
128 vat_reporting_entity_id = pn_vat_reporting_entity_id
129 and ( pv_source = 'ALL' or source = pv_source)
130 and (
131 ( pv_process_name = 'SELECTION' and selection_status_flag = fnd_api.g_ret_sts_success)
132 or
133 ( pv_process_name = 'ALLOCATION' and allocation_status_flag = fnd_api.g_ret_sts_success)
134 or
135 ( pv_process_name = 'FINAL REPORTING' and
136 final_reporting_status_flag = fnd_api.g_ret_sts_success)
137 );
138
139
140 begin
141
142 /* source = 'ALL' means all the three products need to be processed. In such a case the order for processing is AP>AR>GL */
143
144 if pv_source = 'ALL' then
145 lv_source := 'AP';
146 elsif pv_source = 'AP' then
147 lv_source := 'AP';
148 elsif pv_source = 'AR' then
149 lv_source := 'AR';
150 elsif pv_source = 'GL' then
151 lv_source := 'GL';
152 end if;
153
154
155 loop
156
157 ld_last_processed_date_source := null;
158 open c_get_last_processed_date_src( pn_vat_reporting_entity_id , pv_source , pv_process_name);
159 fetch c_get_last_processed_date_src into ld_last_processed_date_source;
160 close c_get_last_processed_date_src;
161
162 if ld_last_processed_date_source is null then
163
164 /* For the current source there is no previous record, so for over all also it should be null, no need to check other source */
165 ld_last_processed_date := null;
166 goto continue_after_loop;
167
168 elsif ld_last_processed_date_source is not null and ld_last_processed_date is not null then
169
170 if ld_last_processed_date_source < ld_last_processed_date then
171 ld_last_processed_date := ld_last_processed_date_source;
172 end if;
173
174 else
175 ld_last_processed_date := ld_last_processed_date_source;
176 end if;
177
178
179 if pv_source <> 'ALL' then
180 /* processing was for only one product, no need to loop more then once */
181 goto continue_after_loop;
182 elsif lv_source = 'AP' then
183 lv_source := 'AR';
184 elsif lv_source = 'AR' then
185 lv_source := 'GL';
186 elsif lv_source = 'GL' then
187 goto continue_after_loop;
188 end if;
189
190 end loop; /* Main loop by source */
191
192
193 << continue_after_loop >>
194 return ld_last_processed_date;
195
196 /* Exception handling id not required as this is called internally and error handling is in the outer most procedure */
197 end get_last_processed_date;
198 /* ================================== End of get_last_processed_date =============================== */
199
200 /* check_gap_in_process_period - NOT BEING USED AFTER REVISED APPROACH CHANGE */
201 /* ======================= start of check_gap_in_process_period INTERNAL procedure ============== */
202
203 procedure check_gap_in_process_period
204 (
205 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
206 pv_period_set_name in gl_periods.period_set_name%type,
207 pv_tax_calendar_period in gl_periods.period_name%type,
208 pd_start_date in gl_periods.START_DATE%type,
209 pd_end_date in gl_periods.end_date%type,
210 pv_source in jg_zz_vat_rep_status.source%type,
211 pv_process_name in varchar2, /* possible values - SELECTION, ALLOCATION, FINAL REPORTING */
212 xv_return_status out nocopy varchar2,
213 xv_return_message out nocopy varchar2
214 )
215 is
216 ld_last_processed_date date;
217 ld_first_period_start_date date;
218
219 cursor c_get_first_period_start_date(pv_period_set_name varchar2, pv_tax_calendar_period varchar2) is
220 select
221 min(start_date)
222 from
223 gl_periods
224 where period_set_name = pv_period_set_name
225 and period_type =
226 (
227 select period_type
228 from gl_periods
229 where period_set_name = pv_period_set_name
230 and period_name = pv_tax_calendar_period
231 );
232
233
234 begin
235
236 ld_last_processed_date :=
237 get_last_processed_date
238 (
239 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id,
240 pv_source => pv_source,
241 pv_process_name => pv_process_name
242 );
243
244 if ld_last_processed_date is not null then
245
246 if ld_last_processed_date + 1 = pd_start_date then
247 /* There is no gap in period */
248 xv_return_status := fnd_api.g_ret_sts_success;
249 xv_return_message := 'There is no gap in period';
250 else
251 xv_return_status := fnd_api.g_ret_sts_error;
252 xv_return_message := 'Processing has successfully happened till ' || ld_last_processed_date ||
253 '. Gap in processing period is not allowed.';
254 end if;
255
256 else
257
258 /* there is no processing record, ld_last_processed_date is null */
259 open c_get_first_period_start_date(pv_period_set_name, pv_tax_calendar_period);
260 fetch c_get_first_period_start_date into ld_first_period_start_date;
261 close c_get_first_period_start_date;
262
263 if ld_first_period_start_date = pd_start_date then
264 xv_return_status := fnd_api.g_ret_sts_success;
265 xv_return_message := 'This period is the first period in the calendar, so no gap.';
266 else
267 xv_return_status := fnd_api.g_ret_sts_error;
268 xv_return_message := 'No period has been processed, the current period is not the first period in the calendar, Cannot proceed.';
269 end if;
270
271 end if; /* ld_last_processed_date is not null */
272
273 /* all possible code paths assign a value for xv_return_status and xv_return_message, so no need to check for null value */
274 return;
275
276 /* Exception handling is not required as this is called internally and error handling is in the outer most procedure */
277 end check_gap_in_process_period;
278 /* ================================== End of check_gap_in_process_period =============================== */
279
280 /* check_gap_in_process_period - NOT BEING USED AFTER REVISED APPROACH CHANGE */
281
282
283 /* ================================== Start of pre_process_update INTERNAL procedure =============================== */
284 procedure pre_process_update
285 (
286 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
287 pv_tax_calendar_period in gl_periods.period_name%type,
288 pv_source in jg_zz_vat_rep_status.source%type,
289 pv_process_name in varchar2, /* possible values - SELECTION, ALLOCATION, FINAL REPORTING */
290 xv_return_status out nocopy varchar2, /* Possible Values : E - Error, S - Successful */
291 xv_return_message out nocopy varchar2
292 )
293 is
294 begin
295
296 If pv_process_name = 'SELECTION' then
297
298 update
299 jg_zz_vat_rep_status
300 Set
301 selection_status_flag = null,
302 Selection_process_id = null,
303 selection_process_date = null,
304 allocation_status_flag = null,
305 allocation_process_id = null,
306 allocation_process_date = null,
307 final_reporting_status_flag = null,
308 final_reporting_process_id = null,
309 final_reporting_process_date = null,
310 last_updated_by = fnd_global.user_id,
311 last_update_date = sysdate,
312 last_update_login = fnd_global.login_id
313 where
314 vat_reporting_entity_id = pn_vat_reporting_entity_id
315 and tax_calendar_period = pv_tax_calendar_period
316 and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) );
317
318 elsif pv_process_name = 'ALLOCATION' then
319
320 update
321 jg_zz_vat_rep_status
322 Set
323 allocation_status_flag = null,
324 allocation_process_id = null,
325 allocation_process_date = null,
326 final_reporting_status_flag = null,
327 final_reporting_process_id = null,
328 final_reporting_process_date = null,
329 last_updated_by = fnd_global.user_id,
330 last_update_date = sysdate,
331 last_update_login = fnd_global.login_id
332 where
333 vat_reporting_entity_id = pn_vat_reporting_entity_id
334 and tax_calendar_period = pv_tax_calendar_period
335 and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) );
336
337 elsif pv_process_name = 'FINAL REPORTING' then
338
339 update
340 jg_zz_vat_rep_status
341 Set
342 final_reporting_status_flag = null,
343 final_reporting_process_id = null,
344 final_reporting_process_date = null,
345 last_updated_by = fnd_global.user_id,
346 last_update_date = sysdate,
347 last_update_login = fnd_global.login_id
348 where
349 vat_reporting_entity_id = pn_vat_reporting_entity_id
350 and tax_calendar_period = pv_tax_calendar_period
351 and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) );
352
353 End if;
354
355 xv_return_status := fnd_api.g_ret_sts_success;
356 return;
357
358 /* Exception handling is not required as this is called internally and error handling is in the outer most procedure */
359 end pre_process_update;
360 /* ================================== End of pre_process_update =============================== */
361
362 /* ================================== Start of validate_process_initiation EXTERNAL procedure=============================== */
363 procedure validate_process_initiation
364 (
365 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
366 pv_tax_calendar_period in gl_periods.period_name%type,
367 pv_source in jg_zz_vat_rep_status.source%type,
368 pv_process_name in varchar2, /* possible values - SELECTION, ALLOCATION, FINAL REPORTING */
369 pv_reallocate_flag in varchar2 default null, /* Valid for allocation only, Possible values Y or N or nul */
370 xn_reporting_status_id_ap out nocopy jg_zz_vat_rep_status.reporting_status_id%type,
371 xn_reporting_status_id_ar out nocopy jg_zz_vat_rep_status.reporting_status_id%type,
372 xn_reporting_status_id_gl out nocopy jg_zz_vat_rep_status.reporting_status_id%type,
373 xv_return_status out nocopy varchar2,
374 xv_return_message out nocopy varchar2
375 )
376 is
377
378 lv_source jg_zz_vat_rep_status.source%type;
379 ln_reporting_status_id jg_zz_vat_rep_status.reporting_status_id%type;
380 lv_selection_status_flag jg_zz_vat_rep_status.selection_status_flag%type;
381 lv_allocation_status_flag jg_zz_vat_rep_status.allocation_status_flag%type;
382 lv_final_reporting_status_flag jg_zz_vat_rep_status.final_reporting_status_flag%type;
383 ld_period_start_date jg_zz_vat_rep_status.period_start_date%type;
384 ld_period_end_date jg_zz_vat_rep_status.period_end_date%type;
385 ln_tax_calendar_year jg_zz_vat_rep_status.tax_calendar_year%type;
386 lv_return_status varchar2(1);
387 lv_return_message varchar2(254);
388
389 lv_tax_registration_number jg_zz_vat_rep_entities.tax_registration_number%type;
390 lv_tax_calendar_name jg_zz_vat_rep_entities.tax_calendar_name%type;
391 lv_enable_allocations_flag jg_zz_vat_rep_entities.enable_allocations_flag%type;
392 ln_mapping_vat_rep_entity_id jg_zz_vat_rep_entities.mapping_vat_rep_entity_id%type;
393
394
395 cursor c_jg_zz_vat_rep_status(pn_vat_reporting_entity_id number, pv_tax_calendar_period varchar2, pv_source varchar2) is
396 select
397 reporting_status_id,
398 nvl(selection_status_flag, 'N') selection_status_flag,
399 nvl(allocation_status_flag, 'N') allocation_status_flag,
400 nvl(final_reporting_status_flag, 'N') final_reporting_status_flag,
401 period_start_date,
402 period_end_date,
403 tax_calendar_name
404 from
405 jg_zz_vat_rep_status
406 where
407 vat_reporting_entity_id = pn_vat_reporting_entity_id
408 and tax_calendar_period = pv_tax_calendar_period
409 and source = pv_source;
410
411 cursor c_jg_zz_vat_rep_entities (pn_vat_reporting_entity_id number) is
412 select
413 tax_registration_number,
414 tax_calendar_name,
415 enable_allocations_flag,
416 mapping_vat_rep_entity_id /* Revised Approach change */
417 from
418 jg_zz_vat_rep_entities
419 where
420 vat_reporting_entity_id = pn_vat_reporting_entity_id;
421
422
423 cursor c_gl_periods (pv_tax_calendar_name varchar2, pv_tax_calendar_period varchar2) is
424 select
425 start_date,
426 end_date,
427 period_year
428 from
429 gl_periods
430 where
431 period_set_name = pv_tax_calendar_name
432 and period_name = pv_tax_calendar_period;
433
434 cur_rec_jg_zz_vat_rep_status c_jg_zz_vat_rep_status%rowtype;
435 cur_rec_jg_zz_vat_rep_entities c_jg_zz_vat_rep_entities%rowtype;
436 cur_rec_gl_periods c_gl_periods%rowtype;
437
438 begin
439
440 /* source = 'ALL' means all the three products need to be processed. In such a case the order for processing is AP>AR>GL */
441
442 if pv_source = 'ALL' then
443 lv_source := 'AP';
444 elsif pv_source = 'AP' then
445 lv_source := 'AP';
446 elsif pv_source = 'AR' then
447 lv_source := 'AR';
448 elsif pv_source = 'GL' then
449 lv_source := 'GL';
450 end if;
451
452 /* Get the details from jg_zz_vat_rep_entities */
453 cur_rec_jg_zz_vat_rep_entities := null;
454 open c_jg_zz_vat_rep_entities (pn_vat_reporting_entity_id);
455 fetch c_jg_zz_vat_rep_entities into cur_rec_jg_zz_vat_rep_entities;
456 close c_jg_zz_vat_rep_entities;
457
458 /* Revised approach changes for mapping entities */
459
460 if cur_rec_jg_zz_vat_rep_entities.mapping_vat_rep_entity_id is null then
461
462 /* Entity is a legal entity, other values to be taken directly from the entity */
463 lv_tax_registration_number := cur_rec_jg_zz_vat_rep_entities.tax_registration_number;
464 lv_tax_calendar_name := cur_rec_jg_zz_vat_rep_entities.tax_calendar_name;
465 lv_enable_allocations_flag := cur_rec_jg_zz_vat_rep_entities.enable_allocations_flag;
466 ln_mapping_vat_rep_entity_id:= pn_vat_reporting_entity_id;
467
468 /* For legal entities mapping entity is being populated same as that of the entity id for
469 simplicity of implementation. */
470
471 else
472
473 /* Entity is an accounting entity, need to fetch details from the mapping legal entity */
474 ln_mapping_vat_rep_entity_id:= cur_rec_jg_zz_vat_rep_entities.mapping_vat_rep_entity_id;
475
476 cur_rec_jg_zz_vat_rep_entities := null;
477 open c_jg_zz_vat_rep_entities (ln_mapping_vat_rep_entity_id);
478 fetch c_jg_zz_vat_rep_entities into cur_rec_jg_zz_vat_rep_entities;
479 close c_jg_zz_vat_rep_entities;
480
481 lv_tax_registration_number := cur_rec_jg_zz_vat_rep_entities.tax_registration_number;
482 lv_tax_calendar_name := cur_rec_jg_zz_vat_rep_entities.tax_calendar_name;
483 lv_enable_allocations_flag := cur_rec_jg_zz_vat_rep_entities.enable_allocations_flag;
484
485 end if;
486
487 loop
488
489 ln_reporting_status_id := null;
490 /* Check if a record already exists for the given combination */
491 cur_rec_jg_zz_vat_rep_status := null;
492
493 open c_jg_zz_vat_rep_status(pn_vat_reporting_entity_id, pv_tax_calendar_period, lv_source);
494 fetch c_jg_zz_vat_rep_status into cur_rec_jg_zz_vat_rep_status;
495 close c_jg_zz_vat_rep_status;
496
497 if cur_rec_jg_zz_vat_rep_status.reporting_status_id is null then
498
499 /* Record does not exist in jg_zz_vat_rep_status for the given combination */
500
501 /* Validation#1 : Only selection process can initiate the entire processing, for other processes selection should have run */
502 if pv_process_name <> 'SELECTION' then
503 fnd_message.set_name('JG', 'JG_ZZ_VAT_NO_SELECTION');
504 fnd_message.set_token('SOURCE', lv_source);
505 fnd_message.set_token('PROCESS_NAME', pv_process_name);
506 xv_return_message := fnd_message.get;
507 xv_return_status := fnd_api.g_ret_sts_error;
508 goto error_exit_from_procedure;
509 end if;
510
511 /* Control comes here only Selection process is being run for the first time for a given combination */
512 /* Need to insert a record in jg_zz_vat_rep_status, extra info needs to be fetched which is common for all source */
513
514 if ld_period_start_date is null then
515
516 /* common information across all sources is not fetched at all, so need to fetch */
517
518 /* Get the details from gl_periods */
519 open c_gl_periods (lv_tax_calendar_name, pv_tax_calendar_period);
520 fetch c_gl_periods into ld_period_start_date, ld_period_end_date, ln_tax_calendar_year;
521 close c_gl_periods;
522
523 end if;
524
525 /* There should not be any gap in processing -
526
527 In the REVISED APPROACH it was decided not to have the check for no gap
528 in selection or final reporting process
529
530 check_gap_in_process_period
531 (
532 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id,
533 pv_period_set_name => cur_rec_jg_zz_vat_rep_entities.tax_calendar_name,
534 pv_tax_calendar_period => pv_tax_calendar_period,
535 pd_start_date => ld_period_start_date,
536 pd_end_date => ld_period_end_date,
537 pv_source => lv_source,
538 pv_process_name => 'SELECTION',
539 xv_return_status => xv_return_status,
540 xv_return_message => xv_return_message
541 );
542
543 if xv_return_status <> fnd_api.g_ret_sts_success then
544 goto error_exit_from_procedure;
545 end if;
546
547 */
548
549
550 /* insert a record into jg_zz_vat_rep_status by source */
551 insert_row
552 (
553 xn_reporting_status_id => ln_reporting_status_id ,
554 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
555 pv_tax_registration_number => lv_tax_registration_number ,
556 pv_tax_calendar_name => lv_tax_calendar_name ,
557 pn_tax_calendar_year => ln_tax_calendar_year ,
558 pv_tax_calendar_period => pv_tax_calendar_period ,
559 pv_source => lv_source ,
560 pd_period_start_date => ld_period_start_date ,
561 pd_period_end_date => ld_period_end_date ,
562 pn_mapping_vat_rep_entity_id => ln_mapping_vat_rep_entity_id
563 );
564
565 else
566
567 /* Record exists in jg_zz_vat_rep_status for the given combination */
568
569 /* Validation # 2: Common validation - Final reporting should not have happened */
570 if cur_rec_jg_zz_vat_rep_status.final_reporting_status_flag = fnd_api.g_ret_sts_success then
571 fnd_message.set_name('JG', 'JG_ZZ_VAT_FINALLY_REPORTED');
572 fnd_message.set_token('SOURCE', lv_source);
573 fnd_message.set_token('PROCESS_NAME', pv_process_name);
574 xv_return_message := fnd_message.get;
575 xv_return_status := fnd_api.g_ret_sts_error;
576 goto error_exit_from_procedure;
577 end if;
578
579
580 /* Validations and processing by process */
581
582 if pv_process_name = 'SELECTION' then
583
584 /* There should not be any gap in period for selection, but if the record exists in jg_zz_vat_rep_status,
585 it means that selection had already happened. So this check is not required as it is checked when selection happens first time. */
586
587 /* There is no other validation required for SELECTION, need to flush allocation and already selected data if any */
588
589 if cur_rec_jg_zz_vat_rep_status.allocation_status_flag <> 'N' then
590 /* invoke the allocation API to purge the allocation data.*/
591
592 jg_zz_vat_alloc_prc_pkg.purge_allocation_data
593 (
594 pn_reporting_status_id => cur_rec_jg_zz_vat_rep_status.reporting_status_id,
595 pv_reallocate_flag => 'Y', --pv_reallocate_flag, bug#5275230
596 xv_return_status => xv_return_status,
597 xv_return_message => xv_return_message
598 );
599
600 if xv_return_status <> fnd_api.g_ret_sts_success then
601 goto error_exit_from_procedure;
602 end if;
603
604 end if;
605
606 /* invoke the selection API to purge the selection data,
607 control comes here only when selection has already happened, so no need to check the flag */
608 jg_zz_vat_selection_pkg.purge_tax_data
609 (
610 p_reporting_status_id => cur_rec_jg_zz_vat_rep_status.reporting_status_id,
611 x_return_status => xv_return_status
612 );
613
614 if xv_return_status <> fnd_api.g_ret_sts_success then
615 fnd_message.set_name('JG', 'JG_ZZ_VAT_GENERIC_ERROR');
616 fnd_message.set_token('PROCESS_DETAILS', ' during purge of previous selection data');
617 xv_return_message := fnd_message.get;
618 goto error_exit_from_procedure;
619 end if;
620
621 elsif pv_process_name = 'ALLOCATION' then
622
623 /* Check if selection has already happened successfully */
624 if cur_rec_jg_zz_vat_rep_status.selection_status_flag <> fnd_api.g_ret_sts_success then
625 fnd_message.set_name('JG', 'JG_ZZ_VAT_ERROR_SELECTION');
626 fnd_message.set_token('SOURCE', lv_source);
627 fnd_message.set_token('PROCESS_NAME', pv_process_name);
628 xv_return_message := fnd_message.get;
629 xv_return_status := fnd_api.g_ret_sts_error;
630 goto error_exit_from_procedure;
631 end if;
632
633
634 if cur_rec_jg_zz_vat_rep_entities.enable_allocations_flag <> 'Y' then
635
636 /* Check if allocation records need to be purged, there could be a change in setup */
637 if cur_rec_jg_zz_vat_rep_status.allocation_status_flag <> 'N' then
638 /* invoke the allocation API to purge the allocation data.*/
639 jg_zz_vat_alloc_prc_pkg.purge_allocation_data
640 (
641 pn_reporting_status_id => cur_rec_jg_zz_vat_rep_status.reporting_status_id,
642 pv_reallocate_flag => pv_reallocate_flag,
643 xv_return_status => xv_return_status,
644 xv_return_message => xv_return_message
645 );
646
647 if xv_return_status <> fnd_api.g_ret_sts_success then
648 goto error_exit_from_procedure;
649 end if;
650
651 end if;
652
653 end if;
654
655 /* All validations for allocation is over, for re-allocation flush the allocation records */
656 if pv_reallocate_flag = 'Y' then
657 /* invoke the allocation API to purge the allocation data.*/
658 jg_zz_vat_alloc_prc_pkg.purge_allocation_data
659 (
660 pn_reporting_status_id => cur_rec_jg_zz_vat_rep_status.reporting_status_id,
661 pv_reallocate_flag => pv_reallocate_flag,
662 xv_return_status => xv_return_status,
663 xv_return_message => xv_return_message
664 );
665
666 if xv_return_status <> fnd_api.g_ret_sts_success then
667 goto error_exit_from_procedure;
668 end if;
669
670 end if;
671
672 elsif pv_process_name = 'FINAL REPORTING' then
673
674 /* Check if selection has already happened successfully */
675 if cur_rec_jg_zz_vat_rep_status.selection_status_flag <> fnd_api.g_ret_sts_success then
676 fnd_message.set_name('JG', 'JG_ZZ_VAT_ERROR_SELECTION');
677 fnd_message.set_token('SOURCE', lv_source);
678 fnd_message.set_token('PROCESS_NAME', pv_process_name);
679 xv_return_message := fnd_message.get;
680 xv_return_status := fnd_api.g_ret_sts_error;
681 goto error_exit_from_procedure;
682 end if;
683
684 if cur_rec_jg_zz_vat_rep_status.allocation_status_flag = 'N' then
685
686 /* Allocation has not happened, check if it is applicable */
687 if cur_rec_jg_zz_vat_rep_entities.enable_allocations_flag = 'Y' then
688 fnd_message.set_name('JG', 'JG_ZZ_VAT_NO_ALLOCATION');
689 fnd_message.set_token('SOURCE', lv_source);
690 fnd_message.set_token('PROCESS_NAME', pv_process_name);
691 xv_return_message := fnd_message.get;
692 xv_return_status := fnd_api.g_ret_sts_error;
693 goto error_exit_from_procedure;
694 end if;
695
696 elsif cur_rec_jg_zz_vat_rep_status.allocation_status_flag <> fnd_api.g_ret_sts_success then
697
698 fnd_message.set_name('JG', 'JG_ZZ_VAT_ERROR_ALLOCATION');
699 fnd_message.set_token('SOURCE', lv_source);
700 fnd_message.set_token('PROCESS_NAME', pv_process_name);
701 xv_return_message := fnd_message.get;
702 xv_return_status := fnd_api.g_ret_sts_error;
703 goto error_exit_from_procedure;
704
705 end if; /* Has allocation happend successfully or is it enabled but not happened */
706
707 /* Final reporting should not have any gap in processing
708 **Changed for Revised approach, it can have gaps
709 check_gap_in_process_period
710 (
711 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id,
712 pv_period_set_name => cur_rec_jg_zz_vat_rep_entities.tax_calendar_name,
713 pv_tax_calendar_period => pv_tax_calendar_period,
714 pd_start_date => cur_rec_jg_zz_vat_rep_status.period_start_date,
715 pd_end_date => cur_rec_jg_zz_vat_rep_status.period_end_date,
716 pv_source => lv_source,
717 pv_process_name => 'FINAL REPORTING',
718 xv_return_status => xv_return_status,
719 xv_return_message => xv_return_message
720 );
721
722 if xv_return_status <> fnd_api.g_ret_sts_success then
723 goto error_exit_from_procedure;
724 end if;
725
726 Revised approach change */
727
728 end if; /* Validations and processing by process */
729
730 end if; /* Existance of record in jg_zz_vat_rep_status */
731
732
733 if lv_source = 'AP' then
734 xn_reporting_status_id_ap := nvl(ln_reporting_status_id, cur_rec_jg_zz_vat_rep_status.reporting_status_id);
735 elsif lv_source = 'AR' then
736 xn_reporting_status_id_ar := nvl(ln_reporting_status_id, cur_rec_jg_zz_vat_rep_status.reporting_status_id);
737 elsif lv_source = 'GL' then
738 xn_reporting_status_id_gl := nvl(ln_reporting_status_id, cur_rec_jg_zz_vat_rep_status.reporting_status_id);
739 end if;
740
741
742 if pv_source <> 'ALL' then
743 /* processing was for only one product, no need to loop more then once */
744 goto continue_after_loop;
745 elsif lv_source = 'AP' then
746 lv_source := 'AR';
747 elsif lv_source = 'AR' then
748 lv_source := 'GL';
749 elsif lv_source = 'GL' then
750 goto continue_after_loop;
751 end if;
752
753 end loop; /* For each source */
754
755 << continue_after_loop >>
756 /* All validations have been successful, do the required pre process update.
757 For allocation process, this update is not required if it is not reallocation.
758 Allocation is treated separately as unless it is a reallocation or a first time allocation,
759 only existsing error records are allocated */
760 if pv_process_name = 'ALLOCATION' and pv_reallocate_flag <> 'Y' then
761 goto exit_from_procedure;
762 end if;
763
764 pre_process_update
765 (
766 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
767 pv_tax_calendar_period => pv_tax_calendar_period ,
768 pv_source => pv_source ,
769 pv_process_name => pv_process_name ,
770 xv_return_status => xv_return_status ,
771 xv_return_message => xv_return_message
772 );
773
774 if xv_return_status =fnd_api.g_ret_sts_success then
775 return;
776 end if;
777 << exit_from_procedure >>
778
779 << error_exit_from_procedure >>
780 return;
781
782 exception
783 when others then
784 xv_return_status := fnd_api.g_ret_sts_unexp_error;
785 xv_return_message := 'jg_zz_vat_rep_utility.validate_process_initiation~Unexpected Error -' || sqlerrm;
786 return;
787 end validate_process_initiation;
788 /* ================================== End of validate_process_initiation =============================== */
789
790
791 /* ================================== Start of post_process_update EXTERNAL procedure =============================== */
792 procedure post_process_update
793 (
794 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
795 pv_tax_calendar_period in gl_periods.period_name%type,
796 pv_source in jg_zz_vat_rep_status.source%type,
797 pv_process_name in varchar2, /* possible values - SELECTION, ALLOCATION, FINAL REPORTING */
798 pn_process_id in jg_zz_vat_rep_status.selection_process_id%type, /* Process id for SELECTION, ALLOCATION, FINAL REPORTING */
799 pv_process_flag in jg_zz_vat_rep_status.selection_status_flag%type,
800 pv_enable_allocations_flag in jg_zz_vat_rep_entities.enable_allocations_flag%type default null, /* only for final reporting process */
801 xv_return_status out nocopy varchar2, /* Possible Values : E - Error, S - Successful */
802 xv_return_message out nocopy varchar2
803 )
804 is
805 begin
806
807 If pv_process_name = 'SELECTION' then
808
809 update
810 jg_zz_vat_rep_status
811 Set
812 selection_status_flag = pv_process_flag ,
813 Selection_process_id = pn_process_id ,
814 selection_process_date = sysdate ,
815 last_updated_by = fnd_global.user_id,
816 last_update_date = sysdate ,
817 last_update_login = fnd_global.login_id
818 where
819 vat_reporting_entity_id = pn_vat_reporting_entity_id
820 and tax_calendar_period = pv_tax_calendar_period
821 and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) );
822
823 Elsif pv_process_name = 'ALLOCATION' then
824
825 update
826 jg_zz_vat_rep_status
827 Set
828 allocation_status_flag = pv_process_flag ,
829 allocation_process_id = pn_process_id ,
830 allocation_process_date = sysdate ,
831 last_updated_by = fnd_global.user_id ,
832 last_update_date = sysdate ,
833 last_update_login = fnd_global.login_id
834 where
835 vat_reporting_entity_id = pn_vat_reporting_entity_id
836 and tax_calendar_period = pv_tax_calendar_period
837 and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) );
838
839 Elsif pv_process_name = 'FINAL REPORTING' then
840
841 update
842 jg_zz_vat_rep_status
843 Set
844 final_reporting_status_flag = pv_process_flag ,
845 final_reporting_process_id = pn_process_id ,
846 final_reporting_process_date = sysdate ,
847 allocation_status_flag =
848 decode(pv_enable_allocations_flag, null, allocation_status_flag, pv_enable_allocations_flag),
849 /* if allocation is not applicable , it should be captured here */
850 last_updated_by = fnd_global.user_id,
851 last_update_date = sysdate ,
852 last_update_login = fnd_global.login_id
853 where
854 vat_reporting_entity_id = pn_vat_reporting_entity_id
855 and tax_calendar_period = pv_tax_calendar_period
856 and ( (pv_source = 'ALL') OR (pv_source <> 'ALL' and source = pv_source) );
857
858 End if;
859
860 xv_return_status := fnd_api.g_ret_sts_success;
861 return;
862
863
864 exception
865 when others then
866 xv_return_status := fnd_api.g_ret_sts_unexp_error;
867 xv_return_message := 'jg_zz_vat_rep_utility.post_process_update~Unexpected Error -' || sqlerrm;
868 return;
869 end post_process_update;
870 /* ================================== End of post_process_update =============================== */
871
872 /* ================================== Start of get_period_status EXTERNAL function ============= */
873 function get_period_status
874 (
875 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
876 pv_tax_calendar_period in gl_periods.period_name%type,
877 pv_tax_calendar_year in number,
878 pv_source in jg_zz_vat_rep_status.source%type,
879 pv_report_name in varchar2,
880 pv_vat_register_id in jg_zz_vat_registers_b.vat_register_id%type DEFAULT NULL
881 ) return varchar2
882 is
883
884 cursor c_get_count_prelims (pn_vat_reporting_entity_id number,
885 pv_tax_calendar_period varchar2,
886 pv_tax_calendar_year number,
887 pv_source varchar2) is
888 select count(vat_reporting_entity_id) total_record,
889 count(decode(final_reporting_status_flag, fnd_api.g_ret_sts_success, 1, null)) final_record
890 from jg_zz_vat_rep_status
891 where vat_reporting_entity_id = pn_vat_reporting_entity_id
892 and ( tax_calendar_period = nvl(pv_tax_calendar_period,'-1')
893 or tax_calendar_year=nvl(pv_tax_calendar_year,-1))
894 and ((pv_source = 'AP' AND source =pv_source)
895 or(pv_source = 'AR' AND source =pv_source)
896 or(pv_source = 'GL' AND source =pv_source)
897 or(pv_source = 'AP-AR' AND (source = 'AP' or source ='AR'))
898 or(pv_source = 'ALL' AND (source = 'AP' or source ='AR'
899 OR source = 'GL'))
900 );
901
902 ln_total_count number(10);
903 ln_final_count number(10);
904 lv_source varchar2(200);
905 lf_final_flag varchar2(1);
906 ln_reporting_status_id number(10);
907
908 cursor c_rep_status_id
909 (
910 pv_vat_reporting_entity_id number,
911 pv_tax_calendar_period gl_periods.period_name%type,
912 pv_tax_calendar_year number,
913 pv_source varchar2
914 ) is
915 select reporting_status_id
916 from jg_zz_Vat_rep_status
917 where vat_reporting_entity_id = pv_vat_reporting_entity_id
918 and (tax_calendar_period = NVL(pv_tax_calendar_period,'-1') or
919 tax_calendar_year = NVL(pv_tax_calendar_year,-1))
920 and ((pv_source = 'AP' AND source =pv_source)
921 or(pv_source = 'AR' AND source =pv_source)
922 or(pv_source = 'GL' AND source =pv_source)
923 or(pv_source = 'AP-AR' AND (source = 'AP' or source ='AR'))
924 or(pv_source = 'ALL' AND (source = 'AP' or source ='AR'
925 OR source = 'GL'))
926 );
927
928 -- Bug 13925495 Start
929 Cursor C_Current_Period Is
930 Select (glp1.period_year*100)+glp1.period_num current_period
931 from jg_zz_vat_rep_status rep,
932 jg_zz_vat_rep_entities jgvre,
933 gl_periods glp1
934 where rep.vat_reporting_entity_id = pn_vat_reporting_entity_id
935 and ( rep.TAX_CALENDAR_PERIOD = NVL(pv_tax_calendar_period,'-1'))
936 and jgvre.vat_reporting_entity_id = rep.vat_reporting_entity_id
937 and rep.tax_calendar_name = nvl(jgvre.tax_calendar_name, rep.tax_calendar_name)
938 and glp1.PERIOD_SET_NAME = rep.tax_calendar_name
939 and glp1.period_name = jgvre.last_reported_period
940 and rownum = 1;
941
942 Cursor C_Report_Period Is
943 Select (glp1.period_year*100)+glp1.period_num report_period
944 from jg_zz_vat_rep_status rep,
945 gl_periods glp1
946 where rep.vat_reporting_entity_id = pn_vat_reporting_entity_id
947 and ( rep.TAX_CALENDAR_PERIOD = NVL(pv_tax_calendar_period,'-1'))
948 and glp1.PERIOD_SET_NAME = rep.tax_calendar_name
949 and glp1.period_name = rep.tax_calendar_period
950 and glp1.period_year = rep.tax_calendar_year
951 and rownum = 1;
952 -- Bug 13925495 End
953 begin
954
955 /* For common Extracts there is no Reporting Status reported */
956 if pv_report_name is NULL then
957 return NULL;
958 end if;
959
960 begin
961 /* first get the source based on the report */
962 select substr(lookup_code,instr(lookup_code,'-')+1)
963 into lv_source
964 from fnd_lookup_values
965 where lookup_code like pv_report_name || '%'
966 and lookup_type = 'JG_ZZ_VAT_REPORT_SOURCE'
967 and language = 'US';
968 exception
969 when others then
970 RAISE;
971 end;
972
973 /* comments */
974 if(lv_source = 'AP-AR' and pv_report_name = 'JEESPMOR') then
975 lv_source :=pv_source;
976 end if;
977 /* check if final rep has been done before */
978 begin
979 select 'Y'
980 into lf_final_flag
981 from jg_zz_vat_final_reports fin,jg_zz_vat_rep_status rep
982 where fin.REPORT_NAME = pv_report_name
983 and NVL(fin.vat_register_id, -1) = NVL(pv_vat_register_id, -1)
984 and fin.REPORTING_STATUS_ID = rep.reporting_status_id
985 and rep.vat_reporting_entity_id = pn_vat_reporting_entity_id
986 and ( rep.TAX_CALENDAR_PERIOD = NVL(pv_tax_calendar_period,'-1')
987 or rep.TAX_CALENDAR_YEAR = NVL(pv_tax_calendar_year,-1))
988 and rep.FINAL_REPORTING_STATUS_FLAG = 'S'
989 and ((lv_source = 'AP' AND rep.source =lv_source)
990 or(lv_source = 'AR' AND rep.source =lv_source)
991 or(lv_source = 'GL' AND rep.source =lv_source)
992 or(lv_source = 'AP-AR' AND (rep.source = 'AP' or rep.source ='AR'))
993 or(lv_source = 'ALL' AND (rep.source = 'AP' or rep.source ='AR'
994 OR rep.source = 'GL'))
995 )
996 and rownum = 1;
997
998 return 'COPY';
999 exception
1000 when others then
1001 -- Bug 13925495 Start
1002 Begin
1003 lf_final_flag := 'N';
1004 For Current_Period_Rec in C_Current_Period Loop
1005 For Report_Period_Rec in C_Report_Period Loop
1006 If Report_Period_Rec.report_period <= Current_Period_Rec.current_period Then
1007 lf_final_flag := 'Y';
1008 End If;
1009 End Loop;
1010 End Loop;
1011 If lf_final_flag = 'Y' Then
1012 return 'COPY';
1013 Else
1014 lf_final_flag := 'N';
1015 End If;
1016 Exception
1017 When Others Then
1018 lf_final_flag := 'N';
1019 End;
1020 -- Bug 13925495 End
1021 end;
1022
1023 if lf_final_flag ='N' then
1024 open c_get_count_prelims(pn_vat_reporting_entity_id,
1025 pv_tax_calendar_period,
1026 pv_tax_calendar_year,
1027 lv_source);
1028 fetch c_get_count_prelims into ln_total_count, ln_final_count;
1029 close c_get_count_prelims;
1030
1031 /* There will be a max of three records as the number of source = 3 */
1032 if ln_total_count = 0 then
1033 return 'NOT PROCESSED';
1034 elsif ln_total_count > ln_final_count then
1035 return 'PRELIMINARY';
1036 elsif ln_total_count = ln_final_count then
1037 /* both are same and they are not 0 */
1038 if (lv_source = 'ALL' and ln_total_count = 3)
1039 or (lv_source <> 'ALL')
1040 or (lv_source <> 'AP-AR') then
1041
1042 for i in c_rep_status_id
1043 ( pn_vat_reporting_entity_id
1044 ,pv_tax_calendar_period
1045 ,pv_tax_calendar_year
1046 ,lv_source)
1047 loop
1048 insert into jg_zz_vat_final_reports
1049 (
1050 FINAL_REPORT_ID
1051 , REPORTING_STATUS_ID
1052 , REPORT_NAME
1053 , VAT_REGISTER_ID
1054 , CREATED_BY
1055 , CREATION_DATE
1056 , LAST_UPDATE_DATE
1057 , LAST_UPDATED_BY
1058 , REQUEST_ID
1059 , PROGRAM_ID
1060 , PROGRAM_APPLICATION_ID
1061 , PROGRAM_LOGIN_ID
1062 , LAST_UPDATE_LOGIN
1063 , OBJECT_VERSION_NUMBER
1064 )
1065 values
1066 (
1067 jg_zz_vat_final_reports_s.NEXTVAL
1068 , i.reporting_status_id
1069 , pv_report_name
1070 , pv_vat_register_id
1071 , nvl(fnd_profile.value('USER_ID'),1)
1072 , SYSDATE
1073 , SYSDATE
1074 , nvl(fnd_profile.value('USER_ID'),1)
1075 , nvl(fnd_profile.value('REQUEST_ID'),1)
1076 , nvl(fnd_profile.value('PROGRAM_ID'),1)
1077 , nvl(fnd_profile.value('PROGRAM_APPLICATION_ID'),1)
1078 , nvl(fnd_profile.value('PROGRAM_LOGIN_ID'),1)
1079 , nvl(fnd_profile.value('LOGIN_ID'),1)
1080 , 1
1081 );
1082 end loop;
1083 return 'FINAL';
1084 else
1085 return 'PRELIMINARY';
1086 /* Example case - AP and AR finally reported, GL is not even initiated and lv_source = 'ALL' */
1087 end if;
1088 end if;
1089 return 'COPY';
1090 end if;
1091
1092 end get_period_status;
1093 /* ================================== End of get_period_status =============================== */
1094
1095
1096 /* ============================= Start of validate_entity_attributes ========================== */
1097 procedure validate_entity_attributes
1098 (
1099 pv_entity_level_code in jg_zz_vat_rep_entities.entity_level_code%type,
1100 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
1101 pn_ledger_id in jg_zz_vat_rep_entities.ledger_id%type default null,
1102 pv_balancing_segment_value in jg_zz_vat_rep_entities.balancing_segment_value%type default null,
1103 xv_return_status out nocopy varchar2,
1104 xv_return_message out nocopy varchar2
1105 )
1106 is
1107 begin
1108
1109 if pn_vat_reporting_entity_id is null then
1110 /* This parameter is required for entities of all level*/
1111 xv_return_status := fnd_api.g_ret_sts_error;
1112 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1113 fnd_message.set_token('PARAMETER', 'TRN');
1114 fnd_message.set_token('LEVEL', 'pv_entity_level_code');
1115 xv_return_message := fnd_message.get;
1116 xv_return_status := fnd_api.g_ret_sts_error;
1117 goto exit_from_procedure;
1118 end if;
1119
1120 if pv_entity_level_code = 'LEDGER' then
1121
1122 if pn_ledger_id is null then /* Required parameter */
1123 xv_return_status := fnd_api.g_ret_sts_error;
1124 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1125 fnd_message.set_token('PARAMETER', 'LEDGER');
1126 fnd_message.set_token('LEVEL', 'pv_entity_level_code');
1127 xv_return_message := fnd_message.get;
1128 goto exit_from_procedure;
1129 end if;
1130
1131 end if;
1132
1133
1134 if pv_entity_level_code = 'BSV' then
1135
1136 if pn_ledger_id is null then /* Required parameter */
1137 xv_return_status := fnd_api.g_ret_sts_error;
1138 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1139 fnd_message.set_token('PARAMETER', 'LEDGER');
1140 fnd_message.set_token('LEVEL', 'pv_entity_level_code');
1141 xv_return_message := fnd_message.get;
1142 goto exit_from_procedure;
1143 end if;
1144
1145 if pv_balancing_segment_value is null then
1146 xv_return_status := fnd_api.g_ret_sts_error;
1147 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1148 fnd_message.set_token('PARAMETER', 'BSV');
1149 fnd_message.set_token('LEVEL', 'pv_entity_level_code');
1150 xv_return_message := fnd_message.get;
1151 goto exit_from_procedure; end if;
1152
1153 end if;
1154
1155
1156 xv_return_status := fnd_api.g_ret_sts_success;
1157
1158 << exit_from_procedure >>
1159 return;
1160
1161 exception
1162 when others then
1163 xv_return_status := fnd_api.g_ret_sts_unexp_error;
1164 xv_return_message := 'jg_zz_vat_rep_utility.validate_entity_attributes~Unexpected Error -' || sqlerrm;
1165 return;
1166 end validate_entity_attributes;
1167 /* ============================== End of validate_entity_attributes =========================== */
1168
1169
1170 /* =============================== Start of get_accounting_entity ============================ */
1171 function get_accounting_entity
1172 (
1173 pv_entity_level_code in jg_zz_vat_rep_entities.entity_level_code%type,
1174 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
1175 pn_ledger_id in jg_zz_vat_rep_entities.ledger_id%type default null,
1176 pv_balancing_segment_value in jg_zz_vat_rep_entities.balancing_segment_value%type default null
1177 ) return number
1178 is
1179
1180 cursor c_jg_zz_vat_rep_entities is
1181 select vat_reporting_entity_id
1182 from jg_zz_vat_rep_entities
1183 where entity_type_code = 'ACCOUNTING'
1184 and entity_level_code = pv_entity_level_code
1185 and mapping_vat_rep_entity_id = pn_vat_reporting_entity_id
1186 and ledger_id = pn_ledger_id
1187 and (
1188 (pv_entity_level_code = 'LEDGER')
1189 or
1190 (pv_entity_level_code = 'BSV' and balancing_segment_value = pv_balancing_segment_value)
1191 );
1192
1193 ln_vat_reporting_entity_id jg_zz_vat_rep_entities.vat_reporting_entity_id%type;
1194
1195 begin
1196
1197 open c_jg_zz_vat_rep_entities;
1198 fetch c_jg_zz_vat_rep_entities into ln_vat_reporting_entity_id;
1199 close c_jg_zz_vat_rep_entities;
1200
1201 return ln_vat_reporting_entity_id;
1202
1203
1204 exception
1205 when others then
1206 return null;
1207 end;
1208
1209 /* ================================ End of get_accounting_entity ============================ */
1210
1211
1212 /* ============================ Start of create_accounting_entity ============================ */
1213 procedure create_accounting_entity
1214 (
1215 pv_entity_level_code in jg_zz_vat_rep_entities.entity_level_code%type,
1216 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
1217 pn_ledger_id in jg_zz_vat_rep_entities.ledger_id%type,
1218 pv_balancing_segment_value in jg_zz_vat_rep_entities.balancing_segment_value%type default null,
1219 xn_vat_reporting_entity_id out nocopy number,
1220 xv_return_status out nocopy varchar2,
1221 xv_return_message out nocopy varchar2
1222 )
1223 is
1224
1225 lr_record jg_zz_vat_rep_entities%rowtype;
1226 lx_row_id rowid;
1227
1228 begin
1229
1230 lr_record.ledger_id := pn_ledger_id;
1231 lr_record.entity_level_code := pv_entity_level_code;
1232 lr_record.balancing_segment_value := pv_balancing_segment_value;
1233 lr_record.mapping_vat_rep_entity_id := pn_vat_reporting_entity_id;
1234 lr_record.created_by := fnd_global.user_id;
1235 lr_record.creation_date := sysdate;
1236 lr_record.last_updated_by := fnd_global.user_id;
1237 lr_record.last_update_date := sysdate;
1238 lr_record.last_update_login := fnd_global.login_id;
1239
1240 jg_zz_vat_rep_entities_pkg.insert_row
1241 (
1242 x_record => lr_record,
1243 x_vat_reporting_entity_id => xn_vat_reporting_entity_id,
1244 x_row_id => lx_row_id
1245 );
1246
1247
1248 << exit_from_procedure >>
1249 xv_return_status := fnd_api.g_ret_sts_success;
1250 return;
1251
1252 exception
1253 when others then
1254 xv_return_status := fnd_api.g_ret_sts_unexp_error;
1255 xv_return_message := 'jg_zz_vat_rep_utility.create_accounting_entity~Unexpected Error -' || sqlerrm;
1256 return;
1257 end create_accounting_entity;
1258 /* ============================ End of create_accounting_entity ============================ */
1259
1260 /* ============================ Start of get_reporting_identifier ============================ */
1261 function get_reporting_identifier
1262 (
1263 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
1264 pv_entity_level_code in jg_zz_vat_rep_entities.entity_level_code%type default null,
1265 pn_ledger_id in jg_zz_vat_rep_entities.ledger_id%type default null,
1266 pv_balancing_segment_value in jg_zz_vat_rep_entities.balancing_segment_value%type default null,
1267 pv_called_from in varchar2 /* possible values - PARAMETER_FORM, TABLE_HANDLER, QUERY */
1268 ) return varchar2
1269 is
1270
1271 lv_return_status varchar2(1);
1272 lv_return_message varchar2(254);
1273
1274 ln_vat_reporting_entity_id jg_zz_vat_rep_entities.vat_reporting_entity_id%type;
1275 ln_mapping_vat_rep_entity_id jg_zz_vat_rep_entities.mapping_vat_rep_entity_id%type;
1276 ln_ledger_id jg_zz_vat_rep_entities.ledger_id%type;
1277 ln_legal_entity_id jg_zz_vat_rep_entities.legal_entity_id%type;
1278 lv_balancing_segment_value jg_zz_vat_rep_entities.balancing_segment_value%type;
1279 lv_tax_regime_code jg_zz_vat_rep_entities.tax_regime_code%type;
1280 lv_tax_registration_number jg_zz_vat_rep_entities.tax_registration_number%type;
1281 lv_entity_identifier jg_zz_vat_rep_entities.entity_identifier%type;
1282 lv_entity_level_code jg_zz_vat_rep_entities.entity_level_code%type;
1283
1284 cursor c_jg_zz_vat_rep_entities(cpn_vat_reporting_entity_id number) is
1285 select
1286 entity_level_code ,
1287 ledger_id ,
1288 legal_entity_id ,
1289 balancing_segment_value ,
1290 tax_regime_code ,
1291 tax_registration_number ,
1292 mapping_vat_rep_entity_id ,
1293 entity_identifier
1294 from
1295 jg_zz_vat_rep_entities
1296 where
1297 vat_reporting_entity_id = cpn_vat_reporting_entity_id;
1298
1299 cursor c_get_le_identifier(cpn_legal_entity_id number) is
1300 select 'LE:' || substr(name, 1, 30) || '-' || legal_entity_id || ':'
1301 from xle_entity_profiles
1302 where legal_entity_id = cpn_legal_entity_id;
1303
1304 cursor c_get_ledger_identifier(cpn_ledger_id number) is
1305 select 'LEDGER:' || substr(name, 1, 30) || '-' || ledger_id|| ':'
1306 from gl_ledgers_public_v
1307 where ledger_id = cpn_ledger_id;
1308
1309 cursor c_get_bsv_identifier(cpv_balancing_segment_value varchar2, cpn_ledger_id number) is
1310 select 'BSV:' || cpv_balancing_segment_value || ':LEDGER:' || substr(name, 1, 30) || '-' || ledger_id|| ':'
1311 from gl_ledgers_public_v
1312 where ledger_id = cpn_ledger_id;
1313
1314
1315 crec_reporting_entities c_jg_zz_vat_rep_entities%rowtype;
1316 crec_mapping_entities c_jg_zz_vat_rep_entities%rowtype;
1317
1318
1319 begin
1320
1321 if pv_called_from in ('TABLE_HANDLER', 'QUERY') then
1322
1323 ln_vat_reporting_entity_id := pn_vat_reporting_entity_id;
1324
1325 elsif pv_called_from = 'PARAMETER_FORM' then
1326
1327 /* Check if the required attributes are given for an entity */
1328
1329 validate_entity_attributes
1330 (
1331 pv_entity_level_code => pv_entity_level_code ,
1332 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
1333 pn_ledger_id => pn_ledger_id ,
1334 pv_balancing_segment_value => pv_balancing_segment_value ,
1335 xv_return_status => lv_return_status ,
1336 xv_return_message => lv_return_message
1337 );
1338
1339 if lv_return_status <> fnd_api.g_ret_sts_success then
1340 /* Entity does not have valid attributes */
1341 lv_entity_identifier := lv_return_message;
1342 goto exit_from_procedure ;
1343 end if;
1344
1345 if pv_entity_level_code = 'LE' then
1346 ln_vat_reporting_entity_id := pn_vat_reporting_entity_id;
1347 else
1348
1349 ln_mapping_vat_rep_entity_id := pn_vat_reporting_entity_id;
1350
1351 ln_vat_reporting_entity_id :=
1352 get_accounting_entity
1353 (
1354 pv_entity_level_code => pv_entity_level_code ,
1355 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
1356 pn_ledger_id => pn_ledger_id ,
1357 pv_balancing_segment_value => pv_balancing_segment_value
1358 );
1359
1360 if ln_vat_reporting_entity_id is null then
1361 ln_ledger_id := pn_ledger_id;
1362 lv_balancing_segment_value := pv_balancing_segment_value;
1363 lv_entity_level_code := pv_entity_level_code;
1364 end if;
1365
1366 end if; /* pv_entity_level_code */
1367
1368 end if; /* pv_called_from */
1369
1370 if ln_vat_reporting_entity_id is not null then
1371
1372 open c_jg_zz_vat_rep_entities(ln_vat_reporting_entity_id);
1373 fetch c_jg_zz_vat_rep_entities into crec_reporting_entities;
1374 close c_jg_zz_vat_rep_entities;
1375
1376 if crec_reporting_entities.entity_identifier is not null then
1377 return crec_reporting_entities.entity_identifier;
1378 end if;
1379
1380 ln_ledger_id := crec_reporting_entities.ledger_id ;
1381 ln_legal_entity_id := crec_reporting_entities.legal_entity_id ;
1382 lv_balancing_segment_value := crec_reporting_entities.balancing_segment_value ;
1383 lv_tax_regime_code := crec_reporting_entities.tax_regime_code ;
1384 lv_tax_registration_number := crec_reporting_entities.tax_registration_number ;
1385 lv_entity_level_code := crec_reporting_entities.entity_level_code ;
1386 ln_mapping_vat_rep_entity_id := crec_reporting_entities.mapping_vat_rep_entity_id;
1387
1388 end if;
1389
1390
1391 if ln_mapping_vat_rep_entity_id is not null then
1392 open c_jg_zz_vat_rep_entities(ln_mapping_vat_rep_entity_id);
1393 fetch c_jg_zz_vat_rep_entities into crec_mapping_entities;
1394 close c_jg_zz_vat_rep_entities;
1395
1396 lv_tax_regime_code := crec_mapping_entities.tax_regime_code ;
1397 lv_tax_registration_number := crec_mapping_entities.tax_registration_number ;
1398
1399 end if;
1400
1401
1402 if lv_entity_level_code = 'LE' then
1403
1404 open c_get_le_identifier(ln_legal_entity_id);
1405 fetch c_get_le_identifier into lv_entity_identifier;
1406 close c_get_le_identifier;
1407
1408
1409 elsif lv_entity_level_code = 'LEDGER' then
1410
1411 open c_get_ledger_identifier(ln_ledger_id);
1412 fetch c_get_ledger_identifier into lv_entity_identifier;
1413 close c_get_ledger_identifier;
1414
1415 elsif lv_entity_level_code = 'BSV' then
1416
1417 open c_get_bsv_identifier(lv_balancing_segment_value, ln_ledger_id);
1418 fetch c_get_bsv_identifier into lv_entity_identifier;
1419 close c_get_bsv_identifier;
1420
1421 end if;
1422
1423 lv_entity_identifier := lv_entity_identifier || lv_tax_regime_code || ':';
1424 lv_entity_identifier := lv_entity_identifier || lv_tax_registration_number || ':';
1425
1426 if ln_vat_reporting_entity_id is not null then
1427 lv_entity_identifier := lv_entity_identifier || to_char(ln_vat_reporting_entity_id);
1428 end if;
1429
1430
1431 << exit_from_procedure >>
1432 return lv_entity_identifier;
1433
1434 end get_reporting_identifier;
1435 /* Exception handling is not required as, it would be ok to show up the exception error
1436 if it comes in the called program as an exception */
1437
1438 /* ============================ End of get_reporting_identifier ============================ */
1439
1440 /* ============================ Start of maintain_selection_entities ============================ */
1441 procedure maintain_selection_entities
1442 (
1443 pv_entity_level_code in jg_zz_vat_rep_entities.entity_level_code%type,
1444 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
1445 pn_ledger_id in jg_zz_vat_rep_entities.ledger_id%type default null,
1446 pv_balancing_segment_value in jg_zz_vat_rep_entities.balancing_segment_value%type default null,
1447 xn_vat_reporting_entity_id out nocopy number,
1448 xv_return_status out nocopy varchar2,
1449 xv_return_message out nocopy varchar2
1450 )
1451 is
1452
1453 lv_return_status varchar2(1);
1454 lv_return_message varchar2(254);
1455
1456 begin
1457
1458 validate_entity_attributes
1459 (
1460 pv_entity_level_code => pv_entity_level_code ,
1461 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
1462 pn_ledger_id => pn_ledger_id ,
1463 pv_balancing_segment_value => pv_balancing_segment_value ,
1464 xv_return_status => xv_return_status ,
1465 xv_return_message => xv_return_message
1466 );
1467
1468 if xv_return_status <> fnd_api.g_ret_sts_success then
1469 goto exit_from_procedure;
1470 end if;
1471
1472 if pv_entity_level_code = 'LE' then
1473 xn_vat_reporting_entity_id := pn_vat_reporting_entity_id;
1474 xv_return_status := fnd_api.g_ret_sts_success;
1475 goto exit_from_procedure;
1476 else
1477
1478 xn_vat_reporting_entity_id :=
1479 get_accounting_entity
1480 (
1481 pv_entity_level_code => pv_entity_level_code ,
1482 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
1483 pn_ledger_id => pn_ledger_id ,
1484 pv_balancing_segment_value => pv_balancing_segment_value
1485 );
1486
1487 if xn_vat_reporting_entity_id is null then
1488
1489 create_accounting_entity
1490 (
1491 pv_entity_level_code => pv_entity_level_code ,
1492 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
1493 pn_ledger_id => pn_ledger_id ,
1494 pv_balancing_segment_value => pv_balancing_segment_value ,
1495 xn_vat_reporting_entity_id => xn_vat_reporting_entity_id ,
1496 xv_return_status => xv_return_status ,
1497 xv_return_message => xv_return_message
1498 );
1499
1500 else
1501 xv_return_status := fnd_api.g_ret_sts_success;
1502 end if;
1503
1504 end if; /* pv_entity_level_code */
1505
1506 << exit_from_procedure >>
1507 return;
1508
1509
1510 exception
1511 when others then
1512 xv_return_status := fnd_api.g_ret_sts_unexp_error;
1513 xv_return_message := 'jg_zz_vat_rep_utility.maintain_selection_entities~Unexpected Error -' || sqlerrm;
1514 return;
1515 end maintain_selection_entities;
1516 /* ============================ End of maintain_selection_entities ============================ */
1517
1518 end jg_zz_vat_rep_utility;
1519
[Home] [Help]