1 package body jg_zz_vat_rep_utility as
2 /* $Header: jgzzvatreputil_b.pls 120.9 2006/12/29 07:07:44 rjreddy 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
345 last_updated_by = fnd_global.user_id,
342 final_reporting_status_flag = null,
343 final_reporting_process_id = null,
344 final_reporting_process_date = null,
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
448 elsif pv_source = 'GL' then
445 lv_source := 'AP';
446 elsif pv_source = 'AR' then
447 lv_source := 'AR';
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 ,
559 pv_source => lv_source ,
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 ,
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,
666 if xv_return_status <> fnd_api.g_ret_sts_success then
663 xv_return_message => xv_return_message
664 );
665
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 begin
929
930 /* For common Extracts there is no Reporting Status reported */
931 if pv_report_name is NULL then
932 return NULL;
933 end if;
934
935 begin
936 /* first get the source based on the report */
937 select substr(lookup_code,instr(lookup_code,'-')+1)
938 into lv_source
939 from fnd_lookup_values
940 where lookup_code like pv_report_name || '%'
941 and lookup_type = 'JG_ZZ_VAT_REPORT_SOURCE'
942 and language = 'US';
943 exception
944 when others then
945 RAISE;
946 end;
947
948 /* comments */
949 if(lv_source = 'AP-AR' and pv_report_name = 'JEESPMOR') then
950 lv_source :=pv_source;
951 end if;
952 /* check if final rep has been done before */
953 begin
954 select 'Y'
955 into lf_final_flag
956 from jg_zz_vat_final_reports fin,jg_zz_vat_rep_status rep
957 where fin.REPORT_NAME = pv_report_name
958 and NVL(fin.vat_register_id, -1) = NVL(pv_vat_register_id, -1)
959 and fin.REPORTING_STATUS_ID = rep.reporting_status_id
960 and rep.vat_reporting_entity_id = pn_vat_reporting_entity_id
961 and ( rep.TAX_CALENDAR_PERIOD = NVL(pv_tax_calendar_period,'-1')
962 or rep.TAX_CALENDAR_YEAR = NVL(pv_tax_calendar_year,-1))
963 and rep.FINAL_REPORTING_STATUS_FLAG = 'S'
964 and ((lv_source = 'AP' AND rep.source =lv_source)
965 or(lv_source = 'AR' AND rep.source =lv_source)
966 or(lv_source = 'GL' AND rep.source =lv_source)
967 or(lv_source = 'AP-AR' AND (rep.source = 'AP' or rep.source ='AR'))
968 or(lv_source = 'ALL' AND (rep.source = 'AP' or rep.source ='AR'
969 OR rep.source = 'GL'))
970 )
971 and rownum = 1;
972
973 return 'COPY';
974 exception
975 when others then
976 lf_final_flag :='N';
977 end;
978
979 if lf_final_flag ='N' then
980 open c_get_count_prelims(pn_vat_reporting_entity_id,
981 pv_tax_calendar_period,
982 pv_tax_calendar_year,
983 lv_source);
984 fetch c_get_count_prelims into ln_total_count, ln_final_count;
985 close c_get_count_prelims;
986
987 /* There will be a max of three records as the number of source = 3 */
988 if ln_total_count = 0 then
989 return 'NOT PROCESSED';
990 elsif ln_total_count > ln_final_count then
991 return 'PRELIMINARY';
992 elsif ln_total_count = ln_final_count then
993 /* both are same and they are not 0 */
994 if (lv_source = 'ALL' and ln_total_count = 3)
995 or (lv_source <> 'ALL')
996 or (lv_source <> 'AP-AR') then
997
998 for i in c_rep_status_id
999 ( pn_vat_reporting_entity_id
1000 ,pv_tax_calendar_period
1001 ,pv_tax_calendar_year
1002 ,lv_source)
1003 loop
1004 insert into jg_zz_vat_final_reports
1005 (
1006 FINAL_REPORT_ID
1007 , REPORTING_STATUS_ID
1008 , REPORT_NAME
1009 , VAT_REGISTER_ID
1010 , CREATED_BY
1011 , CREATION_DATE
1012 , LAST_UPDATE_DATE
1013 , LAST_UPDATED_BY
1014 , REQUEST_ID
1015 , PROGRAM_ID
1016 , PROGRAM_APPLICATION_ID
1020 )
1017 , PROGRAM_LOGIN_ID
1018 , LAST_UPDATE_LOGIN
1019 , OBJECT_VERSION_NUMBER
1021 values
1022 (
1023 jg_zz_vat_final_reports_s.NEXTVAL
1024 , i.reporting_status_id
1025 , pv_report_name
1026 , pv_vat_register_id
1027 , nvl(fnd_profile.value('USER_ID'),1)
1028 , SYSDATE
1029 , SYSDATE
1030 , nvl(fnd_profile.value('USER_ID'),1)
1031 , nvl(fnd_profile.value('REQUEST_ID'),1)
1032 , nvl(fnd_profile.value('PROGRAM_ID'),1)
1033 , nvl(fnd_profile.value('PROGRAM_APPLICATION_ID'),1)
1034 , nvl(fnd_profile.value('PROGRAM_LOGIN_ID'),1)
1035 , nvl(fnd_profile.value('LOGIN_ID'),1)
1036 , 1
1037 );
1038 end loop;
1039 return 'FINAL';
1040 else
1041 return 'PRELIMINARY';
1042 /* Example case - AP and AR finally reported, GL is not even initiated and lv_source = 'ALL' */
1043 end if;
1044 end if;
1045 return 'COPY';
1046 end if;
1047
1048 end get_period_status;
1049 /* ================================== End of get_period_status =============================== */
1050
1051
1052 /* ============================= Start of validate_entity_attributes ========================== */
1053 procedure validate_entity_attributes
1054 (
1055 pv_entity_level_code in jg_zz_vat_rep_entities.entity_level_code%type,
1056 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
1057 pn_ledger_id in jg_zz_vat_rep_entities.ledger_id%type default null,
1058 pv_balancing_segment_value in jg_zz_vat_rep_entities.balancing_segment_value%type default null,
1059 xv_return_status out nocopy varchar2,
1060 xv_return_message out nocopy varchar2
1061 )
1062 is
1063 begin
1064
1065 if pn_vat_reporting_entity_id is null then
1066 /* This parameter is required for entities of all level*/
1067 xv_return_status := fnd_api.g_ret_sts_error;
1068 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1069 fnd_message.set_token('PARAMETER', 'TRN');
1070 fnd_message.set_token('LEVEL', 'pv_entity_level_code');
1071 xv_return_message := fnd_message.get;
1072 xv_return_status := fnd_api.g_ret_sts_error;
1073 goto exit_from_procedure;
1074 end if;
1075
1076 if pv_entity_level_code = 'LEDGER' then
1077
1078 if pn_ledger_id is null then /* Required parameter */
1079 xv_return_status := fnd_api.g_ret_sts_error;
1080 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1081 fnd_message.set_token('PARAMETER', 'LEDGER');
1082 fnd_message.set_token('LEVEL', 'pv_entity_level_code');
1083 xv_return_message := fnd_message.get;
1084 goto exit_from_procedure;
1085 end if;
1086
1087 end if;
1088
1089
1090 if pv_entity_level_code = 'BSV' then
1091
1092 if pn_ledger_id is null then /* Required parameter */
1093 xv_return_status := fnd_api.g_ret_sts_error;
1094 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1095 fnd_message.set_token('PARAMETER', 'LEDGER');
1096 fnd_message.set_token('LEVEL', 'pv_entity_level_code');
1097 xv_return_message := fnd_message.get;
1098 goto exit_from_procedure;
1099 end if;
1100
1101 if pv_balancing_segment_value is null then
1102 xv_return_status := fnd_api.g_ret_sts_error;
1103 fnd_message.set_name('JG', 'JG_ZZ_VAT_INVALID_ENTITY');
1104 fnd_message.set_token('PARAMETER', 'BSV');
1105 fnd_message.set_token('LEVEL', 'pv_entity_level_code');
1106 xv_return_message := fnd_message.get;
1107 goto exit_from_procedure; end if;
1108
1109 end if;
1110
1111
1112 xv_return_status := fnd_api.g_ret_sts_success;
1113
1114 << exit_from_procedure >>
1115 return;
1116
1117 exception
1118 when others then
1119 xv_return_status := fnd_api.g_ret_sts_unexp_error;
1120 xv_return_message := 'jg_zz_vat_rep_utility.validate_entity_attributes~Unexpected Error -' || sqlerrm;
1121 return;
1122 end validate_entity_attributes;
1123 /* ============================== End of validate_entity_attributes =========================== */
1124
1125
1126 /* =============================== Start of get_accounting_entity ============================ */
1127 function get_accounting_entity
1128 (
1129 pv_entity_level_code in jg_zz_vat_rep_entities.entity_level_code%type,
1130 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
1131 pn_ledger_id in jg_zz_vat_rep_entities.ledger_id%type default null,
1132 pv_balancing_segment_value in jg_zz_vat_rep_entities.balancing_segment_value%type default null
1133 ) return number
1134 is
1135
1136 cursor c_jg_zz_vat_rep_entities is
1137 select vat_reporting_entity_id
1138 from jg_zz_vat_rep_entities
1139 where entity_type_code = 'ACCOUNTING'
1140 and entity_level_code = pv_entity_level_code
1141 and mapping_vat_rep_entity_id = pn_vat_reporting_entity_id
1142 and ledger_id = pn_ledger_id
1143 and (
1147 );
1144 (pv_entity_level_code = 'LEDGER')
1145 or
1146 (pv_entity_level_code = 'BSV' and balancing_segment_value = pv_balancing_segment_value)
1148
1149 ln_vat_reporting_entity_id jg_zz_vat_rep_entities.vat_reporting_entity_id%type;
1150
1151 begin
1152
1153 open c_jg_zz_vat_rep_entities;
1154 fetch c_jg_zz_vat_rep_entities into ln_vat_reporting_entity_id;
1155 close c_jg_zz_vat_rep_entities;
1156
1157 return ln_vat_reporting_entity_id;
1158
1159
1160 exception
1161 when others then
1162 return null;
1163 end;
1164
1165 /* ================================ End of get_accounting_entity ============================ */
1166
1167
1168 /* ============================ Start of create_accounting_entity ============================ */
1169 procedure create_accounting_entity
1170 (
1171 pv_entity_level_code in jg_zz_vat_rep_entities.entity_level_code%type,
1172 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
1173 pn_ledger_id in jg_zz_vat_rep_entities.ledger_id%type,
1174 pv_balancing_segment_value in jg_zz_vat_rep_entities.balancing_segment_value%type default null,
1175 xn_vat_reporting_entity_id out nocopy number,
1176 xv_return_status out nocopy varchar2,
1177 xv_return_message out nocopy varchar2
1178 )
1179 is
1180
1181 lr_record jg_zz_vat_rep_entities%rowtype;
1182 lx_row_id rowid;
1183
1184 begin
1185
1186 lr_record.ledger_id := pn_ledger_id;
1187 lr_record.entity_level_code := pv_entity_level_code;
1188 lr_record.balancing_segment_value := pv_balancing_segment_value;
1189 lr_record.mapping_vat_rep_entity_id := pn_vat_reporting_entity_id;
1190 lr_record.created_by := fnd_global.user_id;
1191 lr_record.creation_date := sysdate;
1192 lr_record.last_updated_by := fnd_global.user_id;
1193 lr_record.last_update_date := sysdate;
1194 lr_record.last_update_login := fnd_global.login_id;
1195
1196 jg_zz_vat_rep_entities_pkg.insert_row
1197 (
1198 x_record => lr_record,
1199 x_vat_reporting_entity_id => xn_vat_reporting_entity_id,
1200 x_row_id => lx_row_id
1201 );
1202
1203
1204 << exit_from_procedure >>
1205 xv_return_status := fnd_api.g_ret_sts_success;
1206 return;
1207
1208 exception
1209 when others then
1210 xv_return_status := fnd_api.g_ret_sts_unexp_error;
1211 xv_return_message := 'jg_zz_vat_rep_utility.create_accounting_entity~Unexpected Error -' || sqlerrm;
1212 return;
1213 end create_accounting_entity;
1214 /* ============================ End of create_accounting_entity ============================ */
1215
1216 /* ============================ Start of get_reporting_identifier ============================ */
1217 function get_reporting_identifier
1218 (
1219 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
1220 pv_entity_level_code in jg_zz_vat_rep_entities.entity_level_code%type default null,
1221 pn_ledger_id in jg_zz_vat_rep_entities.ledger_id%type default null,
1222 pv_balancing_segment_value in jg_zz_vat_rep_entities.balancing_segment_value%type default null,
1223 pv_called_from in varchar2 /* possible values - PARAMETER_FORM, TABLE_HANDLER, QUERY */
1224 ) return varchar2
1225 is
1226
1227 lv_return_status varchar2(1);
1228 lv_return_message varchar2(254);
1229
1230 ln_vat_reporting_entity_id jg_zz_vat_rep_entities.vat_reporting_entity_id%type;
1231 ln_mapping_vat_rep_entity_id jg_zz_vat_rep_entities.mapping_vat_rep_entity_id%type;
1232 ln_ledger_id jg_zz_vat_rep_entities.ledger_id%type;
1233 ln_legal_entity_id jg_zz_vat_rep_entities.legal_entity_id%type;
1234 lv_balancing_segment_value jg_zz_vat_rep_entities.balancing_segment_value%type;
1235 lv_tax_regime_code jg_zz_vat_rep_entities.tax_regime_code%type;
1236 lv_tax_registration_number jg_zz_vat_rep_entities.tax_registration_number%type;
1237 lv_entity_identifier jg_zz_vat_rep_entities.entity_identifier%type;
1238 lv_entity_level_code jg_zz_vat_rep_entities.entity_level_code%type;
1239
1240 cursor c_jg_zz_vat_rep_entities(cpn_vat_reporting_entity_id number) is
1241 select
1242 entity_level_code ,
1243 ledger_id ,
1244 legal_entity_id ,
1245 balancing_segment_value ,
1246 tax_regime_code ,
1247 tax_registration_number ,
1248 mapping_vat_rep_entity_id ,
1249 entity_identifier
1250 from
1251 jg_zz_vat_rep_entities
1252 where
1253 vat_reporting_entity_id = cpn_vat_reporting_entity_id;
1254
1255 cursor c_get_le_identifier(cpn_legal_entity_id number) is
1256 select 'LE:' || substr(name, 1, 30) || '-' || legal_entity_id || ':'
1257 from xle_entity_profiles
1258 where legal_entity_id = cpn_legal_entity_id;
1259
1260 cursor c_get_ledger_identifier(cpn_ledger_id number) is
1264
1261 select 'LEDGER:' || substr(name, 1, 30) || '-' || ledger_id|| ':'
1262 from gl_ledgers_public_v
1263 where ledger_id = cpn_ledger_id;
1265 cursor c_get_bsv_identifier(cpv_balancing_segment_value varchar2, cpn_ledger_id number) is
1266 select 'BSV:' || cpv_balancing_segment_value || ':LEDGER:' || substr(name, 1, 30) || '-' || ledger_id|| ':'
1267 from gl_ledgers_public_v
1268 where ledger_id = cpn_ledger_id;
1269
1270
1271 crec_reporting_entities c_jg_zz_vat_rep_entities%rowtype;
1272 crec_mapping_entities c_jg_zz_vat_rep_entities%rowtype;
1273
1274
1275 begin
1276
1277 if pv_called_from in ('TABLE_HANDLER', 'QUERY') then
1278
1279 ln_vat_reporting_entity_id := pn_vat_reporting_entity_id;
1280
1281 elsif pv_called_from = 'PARAMETER_FORM' then
1282
1283 /* Check if the required attributes are given for an entity */
1284
1285 validate_entity_attributes
1286 (
1287 pv_entity_level_code => pv_entity_level_code ,
1288 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
1289 pn_ledger_id => pn_ledger_id ,
1290 pv_balancing_segment_value => pv_balancing_segment_value ,
1291 xv_return_status => lv_return_status ,
1292 xv_return_message => lv_return_message
1293 );
1294
1295 if lv_return_status <> fnd_api.g_ret_sts_success then
1296 /* Entity does not have valid attributes */
1297 lv_entity_identifier := lv_return_message;
1298 goto exit_from_procedure ;
1299 end if;
1300
1301 if pv_entity_level_code = 'LE' then
1302 ln_vat_reporting_entity_id := pn_vat_reporting_entity_id;
1303 else
1304
1305 ln_mapping_vat_rep_entity_id := pn_vat_reporting_entity_id;
1306
1307 ln_vat_reporting_entity_id :=
1308 get_accounting_entity
1309 (
1310 pv_entity_level_code => pv_entity_level_code ,
1311 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
1312 pn_ledger_id => pn_ledger_id ,
1313 pv_balancing_segment_value => pv_balancing_segment_value
1314 );
1315
1316 if ln_vat_reporting_entity_id is null then
1317 ln_ledger_id := pn_ledger_id;
1318 lv_balancing_segment_value := pv_balancing_segment_value;
1319 lv_entity_level_code := pv_entity_level_code;
1320 end if;
1321
1322 end if; /* pv_entity_level_code */
1323
1324 end if; /* pv_called_from */
1325
1326 if ln_vat_reporting_entity_id is not null then
1327
1328 open c_jg_zz_vat_rep_entities(ln_vat_reporting_entity_id);
1329 fetch c_jg_zz_vat_rep_entities into crec_reporting_entities;
1330 close c_jg_zz_vat_rep_entities;
1331
1332 if crec_reporting_entities.entity_identifier is not null then
1333 return crec_reporting_entities.entity_identifier;
1334 end if;
1335
1336 ln_ledger_id := crec_reporting_entities.ledger_id ;
1337 ln_legal_entity_id := crec_reporting_entities.legal_entity_id ;
1338 lv_balancing_segment_value := crec_reporting_entities.balancing_segment_value ;
1339 lv_tax_regime_code := crec_reporting_entities.tax_regime_code ;
1340 lv_tax_registration_number := crec_reporting_entities.tax_registration_number ;
1341 lv_entity_level_code := crec_reporting_entities.entity_level_code ;
1342 ln_mapping_vat_rep_entity_id := crec_reporting_entities.mapping_vat_rep_entity_id;
1343
1344 end if;
1345
1346
1347 if ln_mapping_vat_rep_entity_id is not null then
1348 open c_jg_zz_vat_rep_entities(ln_mapping_vat_rep_entity_id);
1349 fetch c_jg_zz_vat_rep_entities into crec_mapping_entities;
1350 close c_jg_zz_vat_rep_entities;
1351
1352 lv_tax_regime_code := crec_mapping_entities.tax_regime_code ;
1353 lv_tax_registration_number := crec_mapping_entities.tax_registration_number ;
1354
1355 end if;
1356
1357
1358 if lv_entity_level_code = 'LE' then
1359
1360 open c_get_le_identifier(ln_legal_entity_id);
1361 fetch c_get_le_identifier into lv_entity_identifier;
1362 close c_get_le_identifier;
1363
1364
1365 elsif lv_entity_level_code = 'LEDGER' then
1366
1367 open c_get_ledger_identifier(ln_ledger_id);
1368 fetch c_get_ledger_identifier into lv_entity_identifier;
1369 close c_get_ledger_identifier;
1370
1371 elsif lv_entity_level_code = 'BSV' then
1372
1373 open c_get_bsv_identifier(lv_balancing_segment_value, ln_ledger_id);
1374 fetch c_get_bsv_identifier into lv_entity_identifier;
1375 close c_get_bsv_identifier;
1376
1377 end if;
1378
1379 lv_entity_identifier := lv_entity_identifier || lv_tax_regime_code || ':';
1380 lv_entity_identifier := lv_entity_identifier || lv_tax_registration_number || ':';
1381
1382 if ln_vat_reporting_entity_id is not null then
1383 lv_entity_identifier := lv_entity_identifier || to_char(ln_vat_reporting_entity_id);
1384 end if;
1385
1386
1387 << exit_from_procedure >>
1388 return lv_entity_identifier;
1389
1390 end get_reporting_identifier;
1391 /* Exception handling is not required as, it would be ok to show up the exception error
1392 if it comes in the called program as an exception */
1393
1394 /* ============================ End of get_reporting_identifier ============================ */
1395
1396 /* ============================ Start of maintain_selection_entities ============================ */
1397 procedure maintain_selection_entities
1398 (
1399 pv_entity_level_code in jg_zz_vat_rep_entities.entity_level_code%type,
1400 pn_vat_reporting_entity_id in jg_zz_vat_rep_entities.vat_reporting_entity_id%type,
1401 pn_ledger_id in jg_zz_vat_rep_entities.ledger_id%type default null,
1402 pv_balancing_segment_value in jg_zz_vat_rep_entities.balancing_segment_value%type default null,
1403 xn_vat_reporting_entity_id out nocopy number,
1404 xv_return_status out nocopy varchar2,
1405 xv_return_message out nocopy varchar2
1406 )
1407 is
1408
1409 lv_return_status varchar2(1);
1410 lv_return_message varchar2(254);
1411
1412 begin
1413
1414 validate_entity_attributes
1415 (
1416 pv_entity_level_code => pv_entity_level_code ,
1417 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
1418 pn_ledger_id => pn_ledger_id ,
1419 pv_balancing_segment_value => pv_balancing_segment_value ,
1420 xv_return_status => xv_return_status ,
1421 xv_return_message => xv_return_message
1422 );
1423
1424 if xv_return_status <> fnd_api.g_ret_sts_success then
1425 goto exit_from_procedure;
1426 end if;
1427
1428 if pv_entity_level_code = 'LE' then
1429 xn_vat_reporting_entity_id := pn_vat_reporting_entity_id;
1430 xv_return_status := fnd_api.g_ret_sts_success;
1431 goto exit_from_procedure;
1432 else
1433
1434 xn_vat_reporting_entity_id :=
1435 get_accounting_entity
1436 (
1437 pv_entity_level_code => pv_entity_level_code ,
1438 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
1439 pn_ledger_id => pn_ledger_id ,
1440 pv_balancing_segment_value => pv_balancing_segment_value
1441 );
1442
1443 if xn_vat_reporting_entity_id is null then
1444
1445 create_accounting_entity
1446 (
1447 pv_entity_level_code => pv_entity_level_code ,
1448 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id ,
1449 pn_ledger_id => pn_ledger_id ,
1450 pv_balancing_segment_value => pv_balancing_segment_value ,
1451 xn_vat_reporting_entity_id => xn_vat_reporting_entity_id ,
1452 xv_return_status => xv_return_status ,
1453 xv_return_message => xv_return_message
1454 );
1455
1456 else
1457 xv_return_status := fnd_api.g_ret_sts_success;
1458 end if;
1459
1460 end if; /* pv_entity_level_code */
1461
1462 << exit_from_procedure >>
1463 return;
1464
1465
1466 exception
1467 when others then
1468 xv_return_status := fnd_api.g_ret_sts_unexp_error;
1469 xv_return_message := 'jg_zz_vat_rep_utility.maintain_selection_entities~Unexpected Error -' || sqlerrm;
1470 return;
1471 end maintain_selection_entities;
1472 /* ============================ End of maintain_selection_entities ============================ */
1473
1474 end jg_zz_vat_rep_utility;
1475
[Home] [Help]