[Home] [Help]
PACKAGE BODY: APPS.JG_ZZ_VAT_ALLOC_PRC_PKG
Source
1 PACKAGE BODY JG_ZZ_VAT_ALLOC_PRC_PKG AS
2 /* $Header: jgzzvatallocprcb.pls 120.14.12020000.4 2012/11/29 09:55:21 rshergil ship $*/
3
4 gv_debug_flag BOOLEAN; /* added for debug */
5
6 -- Variables used by allocate_box procedure to allow multiple rules for same transaction
7 gv_hierarchy_level NUMBER(15); /* 1=tax_code; 2=tax_status; 3=tax_rate_code; 4=tax_jurisdiction_code */
8 gv_tax_status jg_zz_vat_alloc_rules.tax_status%TYPE;
9 gv_tax_rate_code jg_zz_vat_alloc_rules.tax_rate_code%TYPE;
10 gv_tax_rate_id jg_zz_vat_alloc_rules.tax_rate_id%TYPE; --bug14241603
11 gv_tax_jurisdiction_code jg_zz_vat_alloc_rules.tax_jurisdiction_code%TYPE;
12 gv_allocation_rule_id jg_zz_vat_alloc_rules.allocation_rule_id%TYPE;
13 gv_appl_alloc_rule_id jg_zz_vat_alloc_rules.allocation_rule_id%TYPE;
14
15 /* API to flush the data */
16 procedure purge_allocation_data(
17 pn_reporting_status_id number, /* Primarykey Indicator for repEntity, tax_calerdar and source */
18 pv_reallocate_flag varchar2,
19 xv_return_status out nocopy varchar2,
20 xv_return_message out nocopy varchar2
21 ) IS
22
23 begin
24
25 if nvl(pv_reallocate_flag, 'N') = 'Y' then
26 delete from jg_zz_vat_box_allocs
27 where vat_transaction_id in
28 ( select vat_transaction_id
29 from jg_zz_vat_trx_details a
30 where a.reporting_status_id = pn_reporting_status_id);
31
32 delete from jg_zz_vat_box_errors
33 where vat_transaction_id in
34 ( select vat_transaction_id
35 from jg_zz_vat_trx_details a
36 where a.reporting_status_id = pn_reporting_status_id);
37
38 end if;
39
40 exception
41 when others then
42 xv_return_status := fnd_api.g_ret_sts_unexp_error;
43 xv_return_message := 'jg_zz_vat_alloc_prc_pkg.purge_allocation_data ~ Unexpected Error -' || sqlerrm;
44 end purge_allocation_data;
45
46 PROCEDURE insert_allocation_error (
47 pn_vat_transaction_id number,
48 pv_allocation_error_code varchar2,
49 pv_period_type varchar2,
50 pn_created_by number,
51 pn_last_updated_by number,
52 pn_last_update_login number,
53 xv_return_status out nocopy varchar2,
54 xv_return_message out nocopy varchar2
55 ) IS
56
57 BEGIN
58 if gv_debug_flag then
59 fnd_file.put_line(fnd_file.log, 'insert_allocation_error - start');
60 end if;
61 /*
62 if gv_debug_flag then
63 fnd_file.put_line(fnd_file.log, 'pn_vat_transaction_id:'||pn_vat_transaction_id
64 ||', pv_allocation_error_code:'||pv_allocation_error_code
65 ||', pv_period_type:'||pv_period_type
66 ||', pn_created_by:'||pn_created_by
67 ||', pn_last_updated_by:'||pn_last_updated_by
68 ||', pn_last_update_login:'||pn_last_update_login);
69 end if;
70 */
71 INSERT INTO jg_zz_vat_box_errors(
72 vat_transaction_id ,
73 allocation_error_code ,
74 period_type ,
75 creation_date ,
76 created_by ,
77 last_update_date ,
78 last_updated_by ,
79 last_update_login
80 ) VALUES (
81 pn_vat_transaction_id ,
82 pv_allocation_error_code ,
83 pv_period_type ,
84 sysdate,
85 pn_created_by ,
86 sysdate,
87 pn_last_updated_by ,
88 pn_last_update_login
89 );
90
91 exception
92 when others then
93 xv_return_status := fnd_api.g_ret_sts_unexp_error;
94 xv_return_message := 'jg_zz_vat_alloc_prc_pkg.insert_allocation_error ~ Unexpected Error -' || sqlerrm;
95 END insert_allocation_error;
96
97 PROCEDURE update_allocation_error (
98 pn_vat_transaction_id number,
99 pv_allocation_error_code varchar2,
100 pv_period_type varchar2,
101 pn_last_updated_by number,
102 pn_last_update_login number,
103 xv_return_status out nocopy varchar2,
104 xv_return_message out nocopy varchar2
105 ) IS
106
107 BEGIN
108 UPDATE jg_zz_vat_box_errors
109 SET allocation_error_code = pv_allocation_error_code,
110 last_updated_by = pn_last_updated_by,
111 last_update_date = sysdate,
112 last_update_login = pn_last_update_login
113 WHERE Vat_transaction_id = pn_vat_transaction_id
114 AND period_type = pv_period_type;
115
116 exception
117 when others then
118 xv_return_status := fnd_api.g_ret_sts_unexp_error;
119 xv_return_message := 'jg_zz_vat_alloc_prc_pkg.update_allocation_error ~ Unexpected Error -' || sqlerrm;
120 end update_allocation_error;
121
122 PROCEDURE delete_allocation_error (
123 pn_vat_transaction_id number,
124 pv_allocation_error_code varchar2,
125 pv_period_type varchar2,
126 xv_return_status out nocopy varchar2,
127 xv_return_message out nocopy varchar2
128 ) IS
129
130 BEGIN
131 DELETE FROM jg_zz_vat_box_errors
132 WHERE Vat_transaction_id = pn_vat_transaction_id
133 AND allocation_error_code = pv_allocation_error_code
134 AND period_type = pv_period_type;
135
136 exception
137 when others then
138 xv_return_status := fnd_api.g_ret_sts_unexp_error;
139 xv_return_message := 'jg_zz_vat_alloc_prc_pkg.delete_allocation_error ~ Unexpected Error -' || sqlerrm;
140 end delete_allocation_error;
141
142 /* Main procedure that performs the allocation */
143 PROCEDURE run_allocation (
144 xv_errbuf OUT nocopy varchar2, /*Out parameter for conc. program*/
145 xv_retcode OUT nocopy varchar2, /*Out parameter for conc. program*/
146 pn_vat_reporting_entity_id number, /*this contains TRN, tax_calerdar etc. */
147 pv_tax_calendar_period varchar2, /* calendar period for which allocation should run*/
148 pv_source varchar2, /*one of AP, AR, GL, ALL */
149 pv_reallocate_flag varchar2 /*'Y'- to reallocate all the previous allocation again*/
150 ) IS
151
152 lv_extract_source_ledger varchar2(2);
153 ld_today DATE;
154
155 /* WHO Columns */
156 ld_creation_date DATE;
157 ln_created_by NUMBER(15);
158 ld_last_update_date DATE;
159 ln_last_updated_by NUMBER(15);
160 ln_last_update_login NUMBER(15);
161
162 /* Concurrent request identifier columns */
163 ln_request_id NUMBER(15);
164 ln_program_application_id NUMBER(15);
165 ln_program_id NUMBER(15);
166 ln_program_login_id NUMBER(15);
167 ln_errors_conc_request_id NUMBER(15);
168 lb_ret_value BOOLEAN;
169
170 ln_allocation_process_id NUMBER(15);
171 lv_allocation_status_flag jg_zz_vat_rep_status.allocation_status_flag%TYPE;
172 lv_curr_allocation_status jg_zz_vat_rep_status.allocation_status_flag%TYPE;
173
174 /*indicates for how many products the allocation has to happen. will be initialized in code */
175 ln_source_iterations number(1);
176 ln_period_type_iterations number(1);
177 ln_rep_status_id jg_zz_vat_rep_status.reporting_status_id%TYPE;
178 ln_rep_status_id_ap jg_zz_vat_rep_status.reporting_status_id%TYPE;
179 ln_rep_status_id_ar jg_zz_vat_rep_status.reporting_status_id%TYPE;
180 ln_rep_status_id_gl jg_zz_vat_rep_status.reporting_status_id%TYPE;
181 lv_period_type jg_zz_vat_box_allocs.period_type%TYPE;
182 lv_financial_document_type jg_zz_vat_alloc_rules.financial_document_type%TYPE;
183
184 lv_fresh_allocation_flag varchar2(1);
185 lv_enable_annual_alloc_flag varchar2(1); /* variable that indicates whether annual allocation is required or not */
186 lv_enable_alloc_flag varchar2(1); /* variable that indicates whether allocations is required or not */
187 lv_allocation_errored_flag varchar2(1);
188 lv_allocation_error_code varchar2(100);
189 ln_allocation_rule_id jg_zz_vat_box_allocs.allocation_rule_id%TYPE;
190 lv_tax_box jg_zz_vat_box_allocs.tax_box%TYPE;
191 lv_taxable_box jg_zz_vat_box_allocs.taxable_box%TYPE;
192 ln_vat_box_allocation_id jg_zz_vat_box_allocs.vat_box_allocation_id%TYPE;
193 lv_check_alloc_trans number(15);
194
195 lv_return_flag jg_zz_vat_rep_status.allocation_status_flag%TYPE;
196 lv_return_message VARCHAR2(1996);
197 ln_allocated_cnt NUMBER;
198 ln_del_errored_cnt NUMBER;
199 ln_upd_errored_cnt NUMBER;
200 ln_ins_errored_cnt NUMBER;
201 lv_tax_rate_code zx_rates_b.tax_rate_code%type;
202 lv_vat_trans_type zx_rates_b.vat_transaction_type_code%type;
203 lv_alloc_flag VARCHAR2(1);
204
205 CURSOR c_get_alloc_flags IS
206 SELECT nvl(map_jzvre.enable_allocations_flag, g_no) enable_allocations,
207 nvl(map_jzvre.enable_annual_allocation_flag, g_no) enable_annual_allocations
208 FROM jg_zz_vat_rep_entities jzvre
209 , jg_zz_vat_rep_entities map_jzvre
210 WHERE
211 (jzvre.vat_reporting_entity_id = pn_vat_reporting_entity_id
212 and
213 jzvre.entity_type_code = 'ACCOUNTING'
214 and
215 map_jzvre.vat_reporting_entity_id = jzvre.mapping_vat_rep_entity_id)
216 OR
217 (jzvre.vat_reporting_entity_id = pn_vat_reporting_entity_id
218 and
219 jzvre.entity_type_code = 'LEGAL'
220 and
221 map_jzvre.vat_reporting_entity_id = jzvre.vat_reporting_entity_id);
222
223 CURSOR c_ap_trx_type(cpn_trx_id in number) is
224 SELECT invoice_type_lookup_code
225 FROM ap_invoices_all
226 WHERE invoice_id = cpn_trx_id;
227
228 CURSOR c_cr_dtl(cpn_trx_id in number) is
229 SELECT cr.reversal_category cr_rev_category
230 FROM ar_cash_receipts_all cr
231 WHERE cr.cash_receipt_id = cpn_trx_id
232 AND cr.type = 'MISC'; /* got from R11i belgium solution */
233 l_cr_dtl_rec c_cr_dtl%ROWTYPE;
234
235 CURSOR c_sl_trx_type_dtl(cpn_trx_id in number) is
236 SELECT sl.trx_type sl_trx_type
237 FROM ar_cash_receipt_history_all crh,
238 ce_statement_reconcils_all sr,
239 ce_statement_lines sl
240 WHERE crh.cash_receipt_id = cpn_trx_id
241 AND crh.cash_receipt_history_id = sr.reference_id
242 AND sr.statement_line_id = sl.statement_line_id
243 AND crh.org_id = sr.org_id; -- Bug 8364296
244 l_sl_dtl_rec c_sl_trx_type_dtl%ROWTYPE;
245
246 CURSOR c_ar_trx_type(cp_trx_type_id in number) is
247 SELECT type
248 FROM ra_cust_trx_types_all
249 WHERE cust_trx_type_id = cp_trx_type_id;
250
251 /* fetches all transactions for which box is not allocated */
252 /* this cursor is only used for reference in REF CURSOR RETURN statement */
253 CURSOR c_trxs_for_allocation( cp_source varchar2, cp_period_name varchar2 ) IS
254 SELECT
255 jg_zz_vat_alloc_prc_pkg.g_fresh_allocation allocation_type,
256 /* 'FRESH ALLOCATION' allocation_type, */
257 dtl.extract_source_ledger,
258 dtl.tax,
259 dtl.tax_status_code,
260 dtl.tax_jurisdiction_code,
261 dtl.tax_rate_code,
262 dtl.tax_rate_id,
263 dtl.reporting_status_id,
264 dtl.event_class_code,
265 dtl.entity_code,
266 dtl.trx_id,
267 dtl.trx_type_id,
268 dtl.trx_type_mng,
269 dtl.tax_recoverable_flag,
270 dtl.vat_transaction_id,
271 dtl.tax_rate_vat_trx_type_code vat_trans_type,
272 nvl(dtl.tax_amt_funcl_curr, dtl.tax_amt) tax_amount,
273 NULL allocation_error_code,
274 NULL period_type
275 FROM jg_zz_vat_trx_details dtl, jg_zz_vat_rep_status status
276 WHERE status.reporting_status_id = dtl.reporting_status_id
277 AND status.vat_reporting_entity_id = pn_vat_reporting_entity_id
278 AND status.tax_calendar_period = cp_period_name
279 AND dtl.extract_source_ledger = cp_source
280 AND dtl.trx_line_class <> 'ADJ'; --bug14514143
281
282 TYPE trxs_for_alloc_csr_type IS REF CURSOR RETURN c_trxs_for_allocation%ROWTYPE;
283 l_trxs_for_alloc_csr trxs_for_alloc_csr_type;
284 l_trx_rec c_trxs_for_allocation%ROWTYPE;
285
286 FUNCTION get_transactions_cursor(
287 pv_extract_source_ledger varchar2,
288 pv_tax_period_name varchar2,
289 pv_fresh_allocation_flag varchar2
290 ) return trxs_for_alloc_csr_type is
291
292 l_trxs_csr trxs_for_alloc_csr_type;
293 begin
294
295 if gv_debug_flag then
296 fnd_file.put_line(fnd_file.log, 'get_transactions_cursor-begin');
297 end if;
298
299 if pv_fresh_allocation_flag = g_yes then
300
301 if gv_debug_flag then
302 fnd_file.put_line(fnd_file.log, 'get_transactions_cursor- pv_fresh_allocation_flag = g_yes');
303 end if;
304
305 /*
306 NOTE: if any of the below cursor is changed to add/remove select columns,
307 then the same change also should be done for the other select statement
308 + the cursor c_trxs_for_allocation which is defined above
309 */
310 OPEN l_trxs_csr FOR
311 SELECT
312 jg_zz_vat_alloc_prc_pkg.g_fresh_allocation allocation_type,
313 /* 'FRESH ALLOCATION' allocation_type, */
314 dtl.extract_source_ledger,
315 dtl.tax,
316 dtl.tax_status_code,
317 dtl.tax_jurisdiction_code,
318 dtl.tax_rate_code,
319 dtl.tax_rate_id,
320 dtl.reporting_status_id,
321 dtl.event_class_code,
322 dtl.entity_code,
323 dtl.trx_id,
324 dtl.trx_type_id,
325 dtl.trx_type_mng,
326 dtl.tax_recoverable_flag,
327 dtl.vat_transaction_id,
328 dtl.tax_rate_vat_trx_type_code vat_trans_type,
329 nvl(dtl.tax_amt_funcl_curr, dtl.tax_amt) tax_amount,
330 NULL allocation_error_code,
331 NULL period_type
332 FROM jg_zz_vat_trx_details dtl, jg_zz_vat_rep_status status
333 WHERE status.reporting_status_id = dtl.reporting_status_id
334 AND status.vat_reporting_entity_id = pn_vat_reporting_entity_id
335 AND status.tax_calendar_period = pv_tax_period_name
336 AND dtl.extract_source_ledger = pv_extract_source_ledger
337 AND dtl.trx_line_class <> 'ADJ'; --bug14514143
338
339 else /*return errors cursor */
340
341 if gv_debug_flag then
342 fnd_file.put_line(fnd_file.log, 'get_transactions_cursor- pv_fresh_allocation_flag <> g_yes: returns errors only');
343 end if;
344
345 OPEN l_trxs_csr FOR
346 SELECT
347 jg_zz_vat_alloc_prc_pkg.g_error_allocation allocation_type,
348 /* 'ERROR ALLOCATION' allocation_type, */
349 dtl.extract_source_ledger,
350 dtl.tax,
351 dtl.tax_status_code,
352 dtl.tax_jurisdiction_code,
353 dtl.tax_rate_code,
354 dtl.tax_rate_id,
355 dtl.reporting_status_id,
356 dtl.event_class_code,
357 dtl.entity_code,
358 dtl.trx_id,
359 dtl.trx_type_id,
360 dtl.trx_type_mng,
361 dtl.tax_recoverable_flag,
362 dtl.vat_transaction_id,
363 dtl.tax_rate_vat_trx_type_code vat_trans_type,
364 nvl(dtl.tax_amt_funcl_curr, dtl.tax_amt) tax_amount,
365 err.allocation_error_code allocation_error_code,
366 err.period_type period_type
367 FROM jg_zz_vat_trx_details dtl,
368 jg_zz_vat_box_errors err,
369 jg_zz_vat_rep_status status
370 WHERE status.reporting_status_id = dtl.reporting_status_id
371 AND dtl.vat_transaction_id = err.vat_transaction_id
372 AND status.vat_reporting_entity_id = pn_vat_reporting_entity_id
373 AND status.tax_calendar_period = pv_tax_period_name
374 AND dtl.extract_source_ledger = pv_extract_source_ledger
375 AND dtl.trx_line_class <> 'ADJ'; --bug14514143
376
377 end if;
378
379 if gv_debug_flag then
380 fnd_file.put_line(fnd_file.log, 'get_transactions_cursor- return');
381 end if;
382
383 return l_trxs_csr;
384
385 end get_transactions_cursor;
386
387 BEGIN
388
389 gv_debug_flag := true;
390 if gv_debug_flag then
391 fnd_file.put_line(fnd_file.log, 'run allocation - start');
392 end if;
393
394
395 /* Initializing Variables */
396 ln_created_by := FND_GLOBAL.user_id;
397 ln_last_updated_by := FND_GLOBAL.user_id;
398 ln_last_update_login := FND_GLOBAL.login_id;
399 ld_today := trunc(SYSDATE);
400
401 ln_request_id := FND_PROFILE.value('CONC_REQUEST_ID');
402 ln_program_application_id := FND_PROFILE.value('PROG_APPL_ID');
403 ln_program_id := FND_PROFILE.value('CONC_PROGRAM_ID');
404 ln_program_login_id := FND_PROFILE.value('CONC_LOGIN_ID');
405 ln_allocated_cnt := 0;
406 ln_ins_errored_cnt := 0;
407 ln_upd_errored_cnt := 0;
408 ln_del_errored_cnt := 0;
409
410 if gv_debug_flag then
411 fnd_file.put_line(fnd_file.log, 'run allocation - before JG_ZZ_VAT_REP_UTILITY.validate_process_initiation');
412 end if;
413
414
415 /* Make a call to utility package which validates and determines whether to proceed further with this process or not */
416 JG_ZZ_VAT_REP_UTILITY.validate_process_initiation(
417 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id,
418 pv_tax_calendar_period => pv_tax_calendar_period,
419 pv_source => pv_source,
420 pv_reallocate_flag => pv_reallocate_flag,
421 pv_process_name => 'ALLOCATION', /* is this correct */
422 xn_reporting_status_id_ap => ln_rep_status_id_ap,
423 xn_reporting_status_id_ar => ln_rep_status_id_ar,
424 xn_reporting_status_id_gl => ln_rep_status_id_gl,
425 xv_return_status => lv_return_flag,
426 xv_return_message => lv_return_message
427 );
428
429
430 /* raise error if validation failed */
431 if lv_return_flag in (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) then
432 if gv_debug_flag then
433 fnd_file.put_line(fnd_file.log, 'run allocation - Error JG_ZZ_VAT_REP_UTILITY.validate_process_initiation. value-'||lv_return_flag);
434 end if;
435
436 /* print return message;
437 request WARNING (retcode=1), ERROR (retcode=2)*/
438 xv_errbuf := lv_return_message;
439 xv_retcode := 2;
440 return;
441 end if;
442
443 open c_get_alloc_flags;
444 fetch c_get_alloc_flags into lv_enable_alloc_flag, lv_enable_annual_alloc_flag;
445 close c_get_alloc_flags;
446
447 if gv_debug_flag then
448 fnd_file.put_line(fnd_file.log, 'run allocation - lv_enable_alloc_flag:'||lv_enable_alloc_flag ||', lv_enable_annual_alloc_flag:'||lv_enable_annual_alloc_flag);
449 end if;
450
451 /* logic to set the variable that indicates the no of source ledgers the will be allocated */
452 ln_source_iterations := 1;
453 if pv_source = g_source_all then
454 ln_source_iterations := 3;
455 end if;
456
457 if gv_debug_flag then
458 fnd_file.put_line(fnd_file.log, 'run allocation - Product Iterations'||ln_source_iterations);
459 end if;
460
461 FOR product IN 1..ln_source_iterations LOOP /* 1=AP, 2=AR, 3=GL */
462
463 if gv_debug_flag then
464 fnd_file.put_line(fnd_file.log, ' product loop:'||product);
465 end if;
466
467 /* initialization for SOURCE specific variables */
468 lv_curr_allocation_status := null;
469 lv_allocation_status_flag := null;
470
471 /* logic to decide for which source ledger the processing has to run */
472 if pv_source <> g_source_all then
473 lv_extract_source_ledger := pv_source;
474
475 if pv_source = g_source_ap then ln_rep_status_id := ln_rep_status_id_ap;
476 elsif pv_source = g_source_ar then ln_rep_status_id := ln_rep_status_id_ar;
477 elsif pv_source = g_source_gl then ln_rep_status_id := ln_rep_status_id_gl;
478 end if;
479
480 /* this will get executed only if pv_source = g_source_all */
481 elsif product = 1 then
482 lv_extract_source_ledger := g_source_ap;
483 ln_rep_status_id := ln_rep_status_id_ap;
484 elsif product = 2 then
485 lv_extract_source_ledger := g_source_ar;
486 ln_rep_status_id := ln_rep_status_id_ar;
487 elsif product = 3 then
488 lv_extract_source_ledger := g_source_gl;
489 ln_rep_status_id := ln_rep_status_id_gl;
490 end if;
491
492 if gv_debug_flag then
493 fnd_file.put_line(fnd_file.log, 'run allocation - Extract Source Ledger'||lv_extract_source_ledger);
494 fnd_file.put_line(fnd_file.log, 'run allocation - Rep Status ID'||ln_rep_status_id);
495 end if;
496
497 /*if gv_debug_flag then
498 fnd_file.put_line(fnd_file.log, 'run allocation - pl->bCallTo-get_allocation_status - ln_rep_status_id:'||ln_rep_status_id);
499 end if;
500 */
501 lv_curr_allocation_status := get_allocation_status(pn_reporting_status_id => ln_rep_status_id);
502
503 /*if gv_debug_flag then
504 fnd_file.put_line(fnd_file.log, 'run allocation - pl->aCallTo-get_allocation_status:'||lv_curr_allocation_status);
505 end if;
506 */
507 /* logic to say whether to execute cursor for first time allocation/reallocation or errored allocations*/
508 if pv_reallocate_flag = g_no and lv_curr_allocation_status = g_yes then
509 GOTO next_source; /* no need of processing for this source as it is already done */
510 elsif (pv_reallocate_flag = g_yes or lv_curr_allocation_status = g_no) then
511 lv_fresh_allocation_flag := g_yes;
512 else /*meaning the processing for allocation errors */
513 lv_fresh_allocation_flag := g_no;
514 end if;
515
516 /* Dynamic cursor that fetches data either from JG_ZZ_VAT_TRX_DETAILS or JG_ZZ_VAT_BOX_ERRORS depending
517 on the REALLOCATE_FLAG value
518 While looping thru transaction -
519 If the record is being processed from JG_ZZ_VAT_TRX_DETAILS, then it is processed for both period_types
520 else if the record is being processed from JG_ZZ_VAT_BOX_ERRORS, then only error period_type is processed
521 */
522 l_trxs_for_alloc_csr :=
523 get_transactions_cursor(
524 pv_extract_source_ledger => lv_extract_source_ledger,
525 pv_tax_period_name => pv_tax_calendar_period,
526 pv_fresh_allocation_flag => lv_fresh_allocation_flag
527 );
528
529 LOOP
530
531 lv_financial_document_type := null;
532
533 FETCH l_trxs_for_alloc_csr INTO l_trx_rec;
534 EXIT WHEN l_trxs_for_alloc_csr%NOTFOUND;
535
536 if gv_debug_flag then
537 fnd_file.put_line(fnd_file.log, 'run allocation - trxloop 1. lv_fresh_allocation_flag:'||lv_fresh_allocation_flag);
538 fnd_file.put_line(fnd_file.log, 'run allocation - Transaction ID - '||l_trx_rec.trx_id);
539 fnd_file.put_line(fnd_file.log, 'run allocation - VAT Transaction ID - '||l_trx_rec.vat_transaction_id);
540 end if;
541
542
543 /* should we loop here twice if it is fresh allocation */
544
545 if lv_fresh_allocation_flag = g_yes then
546 if lv_enable_annual_alloc_flag = g_yes then
547 ln_period_type_iterations := 2;
548 else
549 ln_period_type_iterations := 1;
550 end if;
551
552 /* incase of error allocations, period type iterations should be 1 as it can contain PERIODIC
553 as well annual error */
554 else
555 ln_period_type_iterations := 1;
556 end if;
557
558 if gv_debug_flag then
559 fnd_file.put_line(fnd_file.log, 'run allocation - trxloop 2. lv_fresh_allocation_flag:'||lv_fresh_allocation_flag
560 ||', entityCode:'||l_trx_rec.entity_code ||', trx_type_mng:'||l_trx_rec.trx_type_mng
561 );
562 end if;
563
564 /* Financial document type derivation based on SOURCE (AP, AR, GL) */
565 if lv_extract_source_ledger = g_source_ap then
566 open c_ap_trx_type(l_trx_rec.trx_id);
567 fetch c_ap_trx_type into lv_financial_document_type;
568 close c_ap_trx_type;
569
570 elsif lv_extract_source_ledger = g_source_ar then
571
572 /* SOURCE = 'CR' i.e Cash Receipts */
573 if l_trx_rec.entity_code = g_ar_entitycode_receipts then
574
575 open c_cr_dtl(l_trx_rec.trx_id);
576 fetch c_cr_dtl into l_cr_dtl_rec;
577 close c_cr_dtl;
578
579 open c_sl_trx_type_dtl(l_trx_rec.trx_id);
580 fetch c_sl_trx_type_dtl into l_sl_dtl_rec;
581 close c_sl_trx_type_dtl;
582
583 if gv_debug_flag then
584 fnd_file.put_line(fnd_file.log, 'run allocation - trxloop 3. sl_trx_type:'
585 ||l_sl_dtl_rec.sl_trx_type ||', cr_rev_category:'||l_cr_dtl_rec.cr_rev_category
586 );
587 end if;
588
589 if l_sl_dtl_rec.sl_trx_type in ('CREDIT','MISC_CREDIT') then
590 /* Reversal Misc Receipt */
591 if l_cr_dtl_rec.cr_rev_category = 'REV' then
592 lv_financial_document_type := 'MISCREVR';
593
594 /* Misc Cash Receipt */
595 elsif l_cr_dtl_rec.cr_rev_category is NULL then
596 lv_financial_document_type := 'MISCREC';
597 end if;
598
599 elsif l_sl_dtl_rec.sl_trx_type in ('DEBIT','MISC_DEBIT') then
600 /* Reversal Misc Payment */
601 if l_cr_dtl_rec.cr_rev_category = 'REV' then
602 lv_financial_document_type := 'MISCREVP';
603
604 /* Misc Cash Payment */
605 elsif l_cr_dtl_rec.cr_rev_category is NULL then
606 lv_financial_document_type := 'MISCPAY';
607 end if;
608
609 end if;
610
611 elsif l_trx_rec.entity_code = g_ar_entitycode_transactions then
612 open c_ar_trx_type(l_trx_rec.trx_type_id);
613 fetch c_ar_trx_type into lv_financial_document_type;
614 close c_ar_trx_type;
615
616 /* commented during UT
617 lv_financial_document_type := l_trx_rec.trx_type_mng;
618 */
619
620 end if;
621
622 elsif lv_extract_source_ledger = g_source_gl then
623 lv_financial_document_type := 'N/A';
624
625 end if;
626
627 if gv_debug_flag then
628 fnd_file.put_line(fnd_file.log, 'run allocation - financial_document_type : '||lv_financial_document_type);
629 end if;
630
631 if gv_debug_flag then
632 fnd_file.put_line(fnd_file.log, 'run allocation - Period Iterations'||ln_period_type_iterations);
633 end if;
634
635 /* loop to handle the processing for PERIODIC as well as ANNUAL allocations based on setup*/
636 for period_type in 1..ln_period_type_iterations loop
637
638 /* initialization for PERIOD_TYPE specific local variables */
639 lv_tax_box := null;
640 lv_taxable_box := null;
641 ln_allocation_rule_id := null;
642 lv_allocation_error_code := null;
643 lv_allocation_errored_flag := g_no;
644 /*
645 author: brathod
646 Moved statement lv_allocation_errored_flag := g_no; to from trx loop to here, as it needs to be
647 initialized every time a new rules is picked-up. This issue was identified because of the following
648 senario,
649 Consider that both rules (Periodic, Annual) are defined. Now first time allocation process
650 failes to find a rule for period_type='PERIODIC' and it sets the error flag (lv_allocation_errored_flag)to Y.
651 Next time when a rule with period_type='ANNUAL' is actually matching, it will still have stale error flag
652 with value Y (as it was being reset only for a new trx and not between the period_type iteration) and hence
653 it thinks that the no applicable rule is found. It tries to report this as an error by inserting a record
654 in errors table. However this time the rule is matching, hence error_code is null and due to this
655 procedure to insert error record fails with exception "Cannot insert null"
656 */
657
658 if lv_fresh_allocation_flag = g_no then
659 lv_period_type := l_trx_rec.period_type; /* this is the period type of the error allocation */
660 elsif period_type = 1 then
661 lv_period_type := g_period_type_periodic;
662 else /* period_type = 2 then */
663 lv_period_type := g_period_type_annual;
664 end if;
665
666 /*if gv_debug_flag then
667 fnd_file.put_line(fnd_file.log, 'run allocation - periodTypeloop 1. BefAllocBox. lv_period_type:'||lv_period_type);
668 end if;
669 */
670
671 -- Initialize the variable for counting the no. of transactions getting allocated
672 lv_check_alloc_trans := 0;
673 gv_hierarchy_level := 0;
674
675 if gv_debug_flag then
676 fnd_file.put_line(fnd_file.log, 'run allocation - ENTERING LOOP Before calling Allocation_Box()');
677 end if;
678
679 LOOP
680
681 if gv_debug_flag then
682 fnd_file.put_line(fnd_file.log, 'run allocation - INSIDE LOOP Before calling Allocation_Box()');
683 end if;
684
685 lv_tax_box := null;
686 lv_taxable_box := null;
687 ln_allocation_rule_id := null;
688 lv_allocation_error_code := null;
689 lv_allocation_errored_flag := g_no;
690 lv_allocation_status_flag := null;
691 lv_return_flag := null;
692 lv_return_message := null;
693
694 begin
695 lv_alloc_flag := NULL;
696 lv_tax_rate_code := NULL;
697 lv_vat_trans_type := NULL;
698
699 select tax_rate_code, vat_transaction_type_code
700 into lv_tax_rate_code, lv_vat_trans_type
701 from zx_rates_b
702 where tax_rate_id = l_trx_rec.tax_rate_id;
703
704 if l_trx_rec.tax_rate_code = lv_tax_rate_code then
705 if (l_trx_rec.vat_trans_type is null AND lv_vat_trans_type is null)
706 OR (l_trx_rec.vat_trans_type = lv_vat_trans_type) then
707 lv_alloc_flag := 'Y';
708 else
709 lv_alloc_flag := 'N';
710 lv_allocation_error_code := 'JG_ZZ_INVALID_VAT_TRANS_TYPE';
711 end if;
712 else
713 lv_alloc_flag := 'N';
714 lv_allocation_error_code := 'JG_ZZ_INVALID_TAX_RATE_CODE';
715 end if;
716
717 exception
718 when others then
719 lv_alloc_flag := 'N';
720 lv_allocation_error_code := 'JG_ZZ_INVALID_TAX_RATE_CODE';
721 end;
722
723 if gv_debug_flag then
724 fnd_file.put_line(fnd_file.log, 'run allocation - Allocation Flag '||lv_alloc_flag);
725 end if;
726 if gv_debug_flag then
727 fnd_file.put_line(fnd_file.log, 'run allocation - Allocation_error_code' ||lv_allocation_error_code);
728 end if;
729
730 if lv_alloc_flag = 'Y' then
731 allocate_box(
732 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id,
733 pv_period_type => lv_period_type,
734 pv_source => l_trx_rec.extract_source_ledger,
735 pv_event_class_code => lv_financial_document_type, /* l_trx_rec.event_class_code, */
736 pv_tax => l_trx_rec.tax,
737 pv_tax_status => l_trx_rec.tax_status_code,
738 pv_tax_rate_id => l_trx_rec.tax_rate_id, --bug14241603
739 pv_tax_jurisdiction => l_trx_rec.tax_jurisdiction_code,
740 pv_tax_rate_code => l_trx_rec.tax_rate_code,
741 pv_tax_recoverable_flag => l_trx_rec.tax_recoverable_flag,
742 xv_tax_box => lv_tax_box,
743 xv_taxable_box => lv_taxable_box,
744 xn_allocation_rule_id => ln_allocation_rule_id,
745 xv_error_code => lv_allocation_error_code,
746 xv_return_status => lv_return_flag,
747 xv_return_message => lv_return_message
748 );
749
750 if gv_debug_flag then
751 fnd_file.put_line(fnd_file.log, 'run allocation - AFTER allocation_box() CALL');
752 end if;
753
754 if gv_debug_flag then
755 fnd_file.put_line(fnd_file.log, 'run allocation - periodTypeloop 2. AftAllocBox. lv_period_type:'||lv_period_type
756 ||', lv_return_flag:'||lv_return_flag
757 ||', lv_return_message:'||lv_return_message
758 ||', tax_box:'||lv_tax_box
759 ||', taxable_box:'||lv_taxable_box
760 ||', alc_rule_id:'||ln_allocation_rule_id
761 ||', error_code:'||lv_allocation_error_code
762 -- ||', sign:'||ln_sign_indicator
763 );
764 end if;
765 end if;
766
767 /* raise error if validation failed */
768 if lv_return_flag in (fnd_api.g_ret_sts_unexp_error) then
769 xv_errbuf := lv_return_message;
770 xv_retcode := 2;
771 return;
772 end if;
773
774 if lv_allocation_error_code IS NOT NULL then
775 if gv_hierarchy_level > 0 then
776 if lv_check_alloc_trans > 0 then
777 /* if the allocation is done for a previous error, then flush the error */
778 if lv_fresh_allocation_flag = g_no then
779 delete_allocation_error(
780 pn_vat_transaction_id => l_trx_rec.vat_transaction_id,
781 pv_allocation_error_code => l_trx_rec.allocation_error_code,
782 pv_period_type => lv_period_type,
783 xv_return_status => lv_return_flag,
784 xv_return_message => lv_return_message
785 );
786
787 /* raise error if validation failed */
788 if lv_return_flag in (fnd_api.g_ret_sts_unexp_error) then
789 xv_errbuf := lv_return_message;
790 xv_retcode := 2;
791 return;
792 end if;
793
794 ln_del_errored_cnt := ln_del_errored_cnt + 1;
795 end if;
796 exit;
797 elsif lv_tax_box is null or lv_taxable_box is null then
798 if gv_debug_flag then
799 fnd_file.put_line(fnd_file.log, 'run allocation - Setting ERROR JG_ZZ_NO_BOX_IN_RULE');
800 end if;
801 lv_allocation_error_code := 'JG_ZZ_NO_BOX_IN_RULE';
802 end if;
803 end if;
804
805 /* allocation error found */
806 if l_trx_rec.tax_amount = 0 and l_trx_rec.extract_source_ledger = 'AP' then
807 /* Transactions with Tax Amount = 0 and source = 'AP' should not go in jg_zz_vat_box_errors table */
808 exit;
809 end if;
810
811 if lv_fresh_allocation_flag = g_yes then
812
813 if gv_debug_flag then
814 fnd_file.put_line(fnd_file.log, 'run allocation - periodTypeloop. BefDML2 : '
815 ||' lv_allocation_errored_flag = g_yes and lv_fresh_allocation_flag = g_yes'
816 );
817 end if;
818
819 if gv_debug_flag then
820 fnd_file.put_line(fnd_file.log, 'run allocation - Calling PROCEDURE insert_allocation_error');
821 fnd_file.put_line(fnd_file.log, 'run allocation - VAT Transaction ID '||l_trx_rec.vat_transaction_id);
822 fnd_file.put_line(fnd_file.log, 'run allocation - Error Code '||lv_allocation_error_code);
823 end if;
824
825 insert_allocation_error(
826 pn_vat_transaction_id => l_trx_rec.vat_transaction_id,
827 pv_allocation_error_code => lv_allocation_error_code,
828 pv_period_type => lv_period_type,
829 pn_created_by => ln_created_by,
830 pn_last_updated_by => ln_last_updated_by,
831 pn_last_update_login => ln_last_update_login,
832 xv_return_status => lv_return_flag,
833 xv_return_message => lv_return_message
834 );
835
836 if gv_debug_flag then
837 fnd_file.put_line(fnd_file.log, 'run allocation - AFTER PROCEDURE insert_allocation_error');
838 end if;
839
840 /* raise error if above call failed */
841 if lv_return_flag in (fnd_api.g_ret_sts_unexp_error) then
842 xv_errbuf := lv_return_message;
843 xv_retcode := 2;
844 return;
845 end if;
846
847 ln_ins_errored_cnt := ln_ins_errored_cnt + 1;
848
849 exit;
850
851 elsif lv_fresh_allocation_flag = g_no then
852
853 if gv_debug_flag then
854 fnd_file.put_line(fnd_file.log, 'run allocation - periodTypeloop. BefDML3 : '
855 ||' lv_allocation_errored_flag = g_yes and lv_fresh_allocation_flag = g_no'
856 );
857 end if;
858 update_allocation_error(
859 pn_vat_transaction_id => l_trx_rec.vat_transaction_id,
860 pv_allocation_error_code => lv_allocation_error_code,
861 pv_period_type => lv_period_type,
862 pn_last_updated_by => ln_last_updated_by,
863 pn_last_update_login => ln_last_update_login,
864 xv_return_status => lv_return_flag,
865 xv_return_message => lv_return_message
866 );
867 /* raise error if validation failed */
868 if lv_return_flag in (fnd_api.g_ret_sts_unexp_error) then
869 xv_errbuf := lv_return_message;
870 xv_retcode := 2;
871 return;
872 end if;
873
874 ln_upd_errored_cnt := ln_upd_errored_cnt + 1;
875
876 exit;
877
878 end if;
879
880 end if;
881
882 /* check if the call to allocate_box has assigned a tax and taxable box. If not error should be noted */
883 if (lv_tax_box IS NULL and lv_taxable_box IS NULL) then
884 lv_allocation_errored_flag := g_yes;
885 lv_allocation_status_flag := fnd_api.g_ret_sts_error;
886 end if;
887
888 if gv_debug_flag then
889 fnd_file.put_line(fnd_file.log, 'run allocation - periodTypeloop 3. BefDML.'
890 ||' lv_allocation_errored_flag:'||lv_allocation_errored_flag
891 ||', lv_allocation_status_flag:'||lv_allocation_status_flag
892 );
893 end if;
894
895 /* following check is not required as per the Conf. Call with HQ
896 <<posting_check>>
897 If transaction is not posted then
898 -- record the POSTING ERROR CODE (60163) in errors table
899 lv_allocation_error_code := g_alloc_errcode_not_posted;
900 lv_allocation_errored_flag := g_yes;
901 end if;
902 */
903
904 /* i.e successfully found a reco. / non reco. Box */
905 if lv_allocation_errored_flag = g_no then
906
907 if gv_debug_flag then
908 fnd_file.put_line(fnd_file.log, 'run allocation - periodTypeloop. BefDML1 : lv_allocation_errored_flag = g_no'
909 );
910 end if;
911
912 ln_allocated_cnt := ln_allocated_cnt + 1;
913
914 if gv_debug_flag then
915 fnd_file.put_line(fnd_file.log, 'run allocation - Calling jg_zz_vat_box_allocs_pkg.insert_row');
916 fnd_file.put_line(fnd_file.log, 'run allocation - vat box allocation ID'||ln_vat_box_allocation_id);
917 fnd_file.put_line(fnd_file.log, 'run allocation - vat transaction ID'||l_trx_rec.vat_transaction_id);
918 fnd_file.put_line(fnd_file.log, 'run allocation - Allocation Rule ID'||ln_allocation_rule_id);
919
920 end if;
921
922 jg_zz_vat_box_allocs_pkg.insert_row(
923 xn_vat_box_allocation_id => ln_vat_box_allocation_id,
924 pn_vat_transaction_id => l_trx_rec.vat_transaction_id,
925 pv_period_type => lv_period_type,
926 pn_allocation_rule_id => ln_allocation_rule_id,
927 pv_tax_box => lv_tax_box,
928 pv_taxable_box => lv_taxable_box,
929 pv_tax_recoverable_flag => l_trx_rec.tax_recoverable_flag,
930 pn_request_id => ln_request_id,
931 pn_program_application_id => ln_program_application_id,
932 pn_program_id => ln_program_id,
933 pn_program_login_id => ln_program_login_id,
934 pn_created_by => ln_created_by,
935 pn_last_updated_by => ln_last_updated_by,
936 pn_last_update_login => ln_last_update_login,
937 xv_return_status => lv_return_flag,
938 xv_return_message => lv_return_message
939 );
940
941 lv_check_alloc_trans := lv_check_alloc_trans + 1;
942
943 /* raise error if error is returned from the call */
944 if lv_return_flag in (fnd_api.g_ret_sts_unexp_error) then
945 xv_errbuf := lv_return_message;
946 xv_retcode := 2;
947 return;
948 end if;
949
950 end if;
951
952 end loop; /* Fetch Allocation Rules */
953
954 end loop; /* for period types */
955
956 end loop; /* for JG transactions */
957
958 if gv_debug_flag then
959 fnd_file.put_line(fnd_file.log, 'run allocation - EndLoop Trx. lv_allocation_status_flag:'||lv_allocation_status_flag );
960 end if;
961
962 /* derive the sequence value for the allocation process that will be punched in the status table
963 Preparation for call to post process update */
964 if lv_allocation_status_flag is null then
965 lv_allocation_status_flag := fnd_api.g_ret_sts_success;
966 end if;
967
968 if ln_allocation_process_id is null then
969 select jg_zz_vat_rep_status_s2.nextval into ln_allocation_process_id from dual;
970 end if;
971 lv_return_flag := null;
972 lv_return_message := null;
973
974 if gv_debug_flag then
975 fnd_file.put_line(fnd_file.log, 'run allocation - before jg_zz_vat_rep_utility.post_process_update.'
976 );
977 end if;
978 /* Call the utility API to update allocation_process columns of jg_zz_vat_rep_status table by passing proper values.*/
979 jg_zz_vat_rep_utility.post_process_update(
980 pn_vat_reporting_entity_id => pn_vat_reporting_entity_id,
981 pv_tax_calendar_period => pv_tax_calendar_period,
982 pv_source => lv_extract_source_ledger,
983 pv_process_name => 'ALLOCATION',
984 pn_process_id => ln_allocation_process_id,
985 pv_process_flag => lv_allocation_status_flag ,
986 pv_enable_allocations_flag => lv_enable_alloc_flag,
987 xv_return_status => lv_return_flag,
988 xv_return_message => lv_return_message
989 );
990
991 if gv_debug_flag then
992 fnd_file.put_line(fnd_file.log, 'run allocation - after jg_zz_vat_rep_utility.post_process_update.'
993 ||' lv_return_flag:'||lv_return_flag
994 );
995 end if;
996 /* raise error if validation failed */
997 if lv_return_flag in (fnd_api.g_ret_sts_error, fnd_api.g_ret_sts_unexp_error) then
998 xv_errbuf := lv_return_message;
999 xv_retcode := 2;
1000 return;
1001 end if;
1002
1003 <<next_source>>
1004 null;
1005
1006 END LOOP; /* end for source */
1007
1008 if gv_debug_flag then
1009 fnd_file.put_line(fnd_file.log, 'run allocation - after EndLoop Source. ln_allocated_cnt:'||ln_allocated_cnt
1010 ||', ln_ins_errored_cnt:'||ln_ins_errored_cnt
1011 ||', ln_upd_errored_cnt:'||ln_upd_errored_cnt
1012 ||', ln_del_errored_cnt:'||ln_del_errored_cnt
1013 );
1014 end if;
1015
1016 /* Finally submit allocation errors report to display the allocation errors
1017 Submit a request for ALLOCATION ERRORS report with a call to FND_REQUEST.SUBMIT API by providing the parameters */
1018 if ln_ins_errored_cnt > 0 or ln_upd_errored_cnt > 0 then
1019
1020 /* need to correct these values filled for the parameters */
1021 /* add_layout call is required to associate a XML Pub. template to the request output */
1022 lb_ret_value :=
1023 fnd_request.add_layout(
1024 template_appl_name => 'JG',
1025 template_code => 'JGZZAERL',
1026 template_language => 'en',
1027 template_territory => 'US',
1028 output_format => 'PDF'
1029 );
1030
1031 if gv_debug_flag then
1032 fnd_file.put_line(fnd_file.log, 'run allocation - after fnd_request.add_layout'
1033 );
1034 end if;
1035
1036 ln_errors_conc_request_id :=
1037 fnd_request.submit_request(
1038 'JG','JGZZAERL','','',false,
1039 pn_vat_reporting_entity_id, pv_tax_calendar_period, pv_source, FND_GLOBAL.LOCAL_CHR(0),'',
1040 '','','','','','','','','','',
1041 '','','','','','','','','','',
1042 '','','','','','','','','','',
1043 '','','','','','','','','','',
1044 '','','','','','','','','','',
1045 '','','','','','','','','','',
1046 '','','','','','','','','','',
1047 '','','','','','','','','','',
1048 '','','','','','','','','','',
1049 '','','','','');
1050
1051 if gv_debug_flag then
1052 fnd_file.put_line(fnd_file.log, 'run allocation - after fnd_request.submit_request'
1053 );
1054 end if;
1055
1056 end if;
1057
1058 END run_allocation;
1059
1060 /*
1061 Takes the transaction details and allocates a VAT box by matching them with the rule definition details.
1062 If not able to find any box, then returns the xv_error_code with corresponding MESSAGE_CODE
1063 */
1064 PROCEDURE allocate_box(
1065 pn_vat_reporting_entity_id number,
1066 pv_period_type VARCHAR2,
1067 pv_source VARCHAR2,
1068 pv_event_class_code VARCHAR2,
1069 pv_tax VARCHAR2,
1070 pv_tax_status VARCHAR2,
1071 pv_tax_jurisdiction VARCHAR2,
1072 pv_tax_rate_code VARCHAR2,
1073 pv_tax_rate_id number, --bug14241603
1074 pv_tax_recoverable_flag VARCHAR2,
1075 xv_tax_box OUT nocopy VARCHAR2,
1076 xv_taxable_box OUT nocopy VARCHAR2,
1077 xn_allocation_rule_id OUT nocopy VARCHAR2,
1078 xv_error_code OUT nocopy VARCHAR2,
1079 xv_return_status out nocopy varchar2,
1080 xv_return_message out nocopy varchar2
1081 ) is
1082
1083 lv_rule_found VARCHAR2(1);
1084 ld_today DATE;
1085
1086 lv_hierarchy_debug varchar2(30);
1087 lv_statement varchar2(10);
1088
1089 lv_allocation_rule_id jg_zz_vat_alloc_rules.allocation_rule_id%TYPE;
1090 lv_tax_box_recoverable jg_zz_vat_alloc_rules.tax_box_recoverable%TYPE;
1091 lv_tax_box_non_rec jg_zz_vat_alloc_rules.tax_box_non_recoverable%TYPE;
1092 lv_taxable_boxes jg_zz_vat_alloc_rules.taxable_box_non_recoverable%TYPE;
1093
1094 CURSOR c_get_alloc_rules_for_tax(
1095 cp_period_type VARCHAR2,
1096 cp_source VARCHAR2,
1097 cp_financial_document_type VARCHAR2,
1098 cp_tax VARCHAR2,
1099 cp_tax_status VARCHAR2,
1100 cp_tax_rate_code VARCHAR2,
1101 cp_tax_rate_id number, --bug14241603
1102 cp_tax_jurisdiction_code VARCHAR2
1103 ) IS
1104 SELECT
1105 a.allocation_rule_id,
1106 a.source,
1107 a.financial_document_type,
1108 a.vat_transaction_type,
1109 a.tax_code tax,
1110 a.tax_status tax_status_code,
1111 a.tax_rate_code,
1112 a.tax_rate_id, --bug14241603
1113 a.tax_jurisdiction_code,
1114 a.tax_box_recoverable,
1115 a.tax_box_non_recoverable,
1116 a.taxable_box_recoverable,
1117 a.taxable_box_non_recoverable
1118 FROM jg_zz_vat_alloc_rules a
1119 ,jg_zz_vat_rep_entities b
1120 WHERE b.vat_reporting_entity_id = pn_vat_reporting_entity_id
1121 and ((b.entity_type_code = 'ACCOUNTING'
1122 and
1123 b.mapping_vat_rep_entity_id = a.vat_reporting_entity_id)
1124 OR
1125 (b.entity_type_code = 'LEGAL'
1126 and
1127 b.vat_reporting_entity_id = a.vat_reporting_entity_id))
1128 AND a.period_type = cp_period_type
1129 AND a.source = cp_source
1130 AND a.financial_document_type = cp_financial_document_type
1131 AND a.tax_code = cp_tax
1132 --AND nvl(a.tax_status, '1') = nvl(cp_tax_status, '1') --9729100
1133 --AND nvl(a.tax_rate_code, '1') = nvl(cp_tax_rate_code,'1')
1134 --AND nvl(a.tax_jurisdiction_code,'1') = nvl(cp_tax_jurisdiction_code, '1')
1135 AND (a.tax_status is null or a.tax_status = nvl(cp_tax_status, '1'))
1136 AND (a.tax_rate_code is null or a.tax_rate_code = nvl(cp_tax_rate_code, '1'))
1137 AND (a.tax_rate_id is null or a.tax_rate_id = nvl(cp_tax_rate_id, '1')) --bug14241603
1138 AND (a.tax_jurisdiction_code is null or a.tax_jurisdiction_code = nvl(cp_tax_jurisdiction_code, '1'))
1139 AND ld_today BETWEEN a.effective_from_date AND nvl(a.effective_to_date, ld_today)
1140 ORDER BY
1141 a.source,
1142 a.financial_document_type,
1143 a.tax_code NULLS LAST,
1144 a.tax_status NULLS LAST,
1145 a.tax_rate_code NULLS LAST,
1146 a.tax_jurisdiction_code NULLS LAST,
1147 decode(cp_source, 'AP', decode(pv_tax_recoverable_flag, g_yes,
1148 decode(a.taxable_box_non_recoverable, NULL, NULL,
1149 decode(a.tax_box_recoverable, NULL, NULL,
1150 a.tax_box_recoverable)),
1151 decode(a.taxable_box_non_recoverable, NULL, NULL,
1152 decode(a.tax_box_non_recoverable, NULL, NULL,
1153 a.tax_box_non_recoverable))),
1154 decode(a.taxable_box_non_recoverable, NULL, NULL,
1155 decode(a.tax_box_recoverable, NULL, NULL,
1156 a.tax_box_recoverable))) NULLS LAST;
1157
1158 CURSOR c_get_rules_stat_code_jrdict(
1159 cp_vat_reporting_entity_id NUMBER,
1160 cp_period_type VARCHAR2,
1161 cp_source VARCHAR2,
1162 cp_financial_document_type VARCHAR2,
1163 cp_tax VARCHAR2
1164 ) IS
1165 SELECT
1166 a.allocation_rule_id,
1167 a.tax_box_recoverable,
1168 a.tax_box_non_recoverable,
1169 nvl(a.taxable_box_recoverable, a.taxable_box_non_recoverable)
1170 FROM jg_zz_vat_alloc_rules a
1171 ,jg_zz_vat_rep_entities b
1172 WHERE b.vat_reporting_entity_id = cp_vat_reporting_entity_id
1173 AND ((b.entity_type_code = 'ACCOUNTING'
1174 and
1175 b.mapping_vat_rep_entity_id = a.vat_reporting_entity_id)
1176 OR
1177 (b.entity_type_code = 'LEGAL'
1178 and
1179 b.vat_reporting_entity_id = a.vat_reporting_entity_id))
1180 AND a.period_type = cp_period_type
1181 AND a.source = cp_source
1182 AND a.financial_document_type = cp_financial_document_type
1183 AND a.tax_code = cp_tax
1184 --AND nvl(a.tax_status, '1') = nvl(gv_tax_status, '1') --9729100
1185 --AND nvl(a.tax_rate_code, '1') = nvl(gv_tax_rate_code,'1')
1186 --AND nvl(a.tax_jurisdiction_code,'1') = nvl(gv_tax_jurisdiction_code, '1')
1187 AND (a.tax_status is null or a.tax_status = nvl(gv_tax_status, '1'))
1188 AND (a.tax_rate_code is null or a.tax_rate_code = nvl(gv_tax_rate_code, '1'))
1189 AND (a.tax_rate_id is null or a.tax_rate_id = nvl(gv_tax_rate_id, '1')) --bug14241603
1190 AND (a.tax_jurisdiction_code is null or a.tax_jurisdiction_code = nvl(gv_tax_jurisdiction_code, '1'))
1191 AND ld_today BETWEEN a.effective_from_date AND nvl(a.effective_to_date, ld_today)
1192 AND a.allocation_rule_id <> gv_appl_alloc_rule_id
1193 AND a.allocation_rule_id > gv_allocation_rule_id
1194 ORDER BY
1195 a.allocation_rule_id;
1196
1197 CURSOR c_get_minimum_alloc_rule_id(
1198 cp_vat_reporting_entity_id NUMBER,
1199 cp_period_type VARCHAR2,
1200 cp_source VARCHAR2,
1201 cp_financial_document_type VARCHAR2,
1202 cp_tax VARCHAR2,
1203 cp_allocation_rule_id NUMBER
1204 ) IS
1205 SELECT
1206 min(a.allocation_rule_id) - 1
1207 FROM jg_zz_vat_alloc_rules a
1208 ,jg_zz_vat_rep_entities b
1209 WHERE b.vat_reporting_entity_id = cp_vat_reporting_entity_id
1210 AND ((b.entity_type_code = 'ACCOUNTING'
1211 and
1212 b.mapping_vat_rep_entity_id = a.vat_reporting_entity_id)
1213 OR
1214 (b.entity_type_code = 'LEGAL'
1215 and
1216 b.vat_reporting_entity_id = a.vat_reporting_entity_id))
1217 AND a.period_type = cp_period_type
1218 AND a.source = cp_source
1219 AND a.financial_document_type = cp_financial_document_type
1220 AND a.tax_code = cp_tax
1221 --AND nvl(a.tax_status, '1') = nvl(gv_tax_status, '1') --9729100
1222 --AND nvl(a.tax_rate_code, '1') = nvl(gv_tax_rate_code,'1')
1223 --AND nvl(a.tax_jurisdiction_code,'1') = nvl(gv_tax_jurisdiction_code, '1')
1224 AND (a.tax_status is null or a.tax_status = nvl(gv_tax_status, '1'))
1225 AND (a.tax_rate_code is null or a.tax_rate_code = nvl(gv_tax_rate_code, '1'))
1226 AND (a.tax_rate_id is null or a.tax_rate_id = nvl(gv_tax_rate_id, '1')) --bug14241603
1227 AND (a.tax_jurisdiction_code is null or a.tax_jurisdiction_code = nvl(gv_tax_jurisdiction_code, '1'))
1228 AND ld_today BETWEEN a.effective_from_date AND nvl(a.effective_to_date, ld_today)
1229 AND a.allocation_rule_id <> cp_allocation_rule_id;
1230
1231 begin
1232
1233 ld_today := trunc(sysdate);
1234 lv_rule_found := g_no;
1235
1236 if gv_debug_flag then
1237 fnd_file.put_line(fnd_file.log, 'allocate_box - afterHierarchy. gv_hierarchy_level:'||gv_hierarchy_level
1238 ||', gv_appl_alloc_rule_id ::'||gv_appl_alloc_rule_id
1239 ||', gv_allocation_rule_id ::'||gv_allocation_rule_id
1240 );
1241 end if;
1242
1243 if gv_hierarchy_level = 0 then
1244
1245 if gv_debug_flag then
1246 fnd_file.put_line(fnd_file.log, 'allocate_box - start' );
1247 fnd_file.put_line(fnd_file.log, 'allocate_box - Hierarchy Level '||gv_hierarchy_level );
1248 end if;
1249 lv_statement := '1';
1250
1251 if gv_debug_flag then
1252 fnd_file.put_line(fnd_file.log, 'Params-'
1253 ||', pn_vat_reporting_entity_id:'||pn_vat_reporting_entity_id
1254 ||', pv_period_type:'||pv_period_type
1255 ||', pv_source:'||pv_source
1256 ||', pv_event_class_code:'||pv_event_class_code
1257 ||', pv_tax:'||pv_tax
1258 ||', pv_tax_status:'||pv_tax_status
1259 ||', pv_tax_jurisdiction:'||pv_tax_jurisdiction
1260 ||', pv_tax_rate_code:'||pv_tax_rate_code
1261 ||', pv_tax_rate_id:'||pv_tax_rate_id --bug14241603
1262 ||', pv_tax_recoverable_flag:'||pv_tax_recoverable_flag
1263 );
1264 end if;
1265
1266 /* fetch all rules defined for the tax for a period type */
1267 for rule IN c_get_alloc_rules_for_tax(pv_period_type,
1268 pv_source, pv_event_class_code, pv_tax, pv_tax_status, pv_tax_rate_code, pv_tax_rate_id, pv_tax_jurisdiction) --bug14241603
1269 loop
1270
1271 if gv_debug_flag then
1272 fnd_file.put_line(fnd_file.log, 'allocate_box - RECORD found in Cursor c_get_alloc_rules_for_tax');
1273 end if;
1274
1275 lv_statement := '2';
1276 lv_rule_found := g_no;
1277
1278 lv_hierarchy_debug := 'T';
1279 /* Start of hierarchical derivation of Tax box. */
1280 /* Checking for Status */
1281 if gv_debug_flag then
1282 fnd_file.put_line(fnd_file.log, 'allocate_box - rule.tax_status_code:'||rule.tax_status_code||', pv_tax_status:'||pv_tax_status);
1283 end if;
1284 if rule.tax_status_code = pv_tax_status then
1285 lv_statement := '3';
1286 lv_hierarchy_debug := 'T:S';
1287 /* checking for Tax Rate */
1288 if gv_debug_flag then
1289 fnd_file.put_line(fnd_file.log, 'allocate_box - rule.tax_rate_code:'||rule.tax_rate_code||', pv_tax_rate_code:'||pv_tax_rate_code);
1290 end if;
1291 if rule.tax_rate_code = pv_tax_rate_code then
1292 lv_statement := '4';
1293 lv_hierarchy_debug := 'T:S:R';
1294 /* checking for Jurisdiction */
1295 if gv_debug_flag then
1296 fnd_file.put_line(fnd_file.log, 'allocate_box - rule.tax_jurisdiction_code:'||rule.tax_jurisdiction_code||', pv_tax_jurisdiction:'||pv_tax_jurisdiction);
1297 end if;
1298 if rule.tax_jurisdiction_code = pv_tax_jurisdiction then
1299 if gv_debug_flag then
1300 fnd_file.put_line(fnd_file.log, 'allocate_box - rule.tax_jurisdiction_code = pv_tax_jurisdiction');
1301 end if;
1302 lv_statement := '5';
1303 lv_hierarchy_debug := 'T:S:R:J';
1304 lv_rule_found := g_yes;
1305 gv_hierarchy_level := 4;
1306 /* Tax Rate is success, but jurisdiction failed. so assign the default box of rate that is not specific to any jurisdiction under the rate*/
1307
1308 elsif rule.tax_jurisdiction_code is null then
1309 if gv_debug_flag then
1310 fnd_file.put_line(fnd_file.log, 'allocate_box - rule.tax_jurisdiction_code is null');
1311 end if;
1312
1313 lv_statement := '6';
1314 lv_hierarchy_debug := 'T:S:R:Jnull';
1315 lv_rule_found := g_yes;
1316 gv_hierarchy_level := 3;
1317 else
1318 if gv_debug_flag then
1319 fnd_file.put_line(fnd_file.log, 'allocate_box - else part one');
1320 end if;
1321
1322 null;
1323 end if;
1324
1325 /* Tax status is success, but rate failed. so assign the default box of status which is not specific to any rate under the status */
1326 elsif rule.tax_rate_code is null then
1327 if gv_debug_flag then
1328 fnd_file.put_line(fnd_file.log, 'allocate_box - rule.tax_rate_code is null');
1329 end if;
1330 lv_statement := '7';
1331 lv_hierarchy_debug := 'T:S:Rnull';
1332 lv_rule_found := g_yes;
1333 gv_hierarchy_level := 2;
1334 else
1335 if gv_debug_flag then
1336 fnd_file.put_line(fnd_file.log, 'allocate_box - else part two ');
1337 end if;
1338 lv_statement := '8';
1339 lv_hierarchy_debug := 'T:S:null';
1340 null;
1341 end if;
1342
1343 /* Tax is success, but status failed. so assign the default box of tax which is not specific to any status under the tax*/
1344 elsif rule.tax_status_code is null then
1345 if gv_debug_flag then
1346 fnd_file.put_line(fnd_file.log, 'allocate_box - rule.tax_status_code is null ');
1347 end if;
1348 lv_statement := '9';
1349 lv_hierarchy_debug := 'T:Snull';
1350 lv_rule_found := g_yes;
1351 gv_hierarchy_level := 1;
1352 else
1353 if gv_debug_flag then
1354 fnd_file.put_line(fnd_file.log, 'allocate_box - else part three ');
1355 end if;
1356 lv_statement := '10';
1357 lv_hierarchy_debug := 'T:null';
1358 null;
1359 end if;
1360
1361 if gv_debug_flag then
1362 fnd_file.put_line(fnd_file.log, 'allocate_box - afterHierarchy. lv_rule_found:'||lv_rule_found
1363 ||', pv_tax_recoverable_flag:'||pv_tax_recoverable_flag
1364 );
1365 end if;
1366
1367 if lv_rule_found = g_yes then
1368 -- Get the values from the cursor variable
1369 lv_allocation_rule_id := rule.allocation_rule_id;
1370 lv_tax_box_recoverable := rule.tax_box_recoverable;
1371 lv_tax_box_non_rec := rule.tax_box_non_recoverable;
1372 lv_taxable_boxes := nvl(rule.taxable_box_recoverable,rule.taxable_box_non_recoverable);
1373
1374 if gv_debug_flag then
1375 fnd_file.put_line(fnd_file.log, 'allocate_box - afterHierarchy. lv_allocation_rule_id1:'||lv_allocation_rule_id);
1376 end if;
1377
1378 -- Assign the value to global variables accordingly
1379 gv_tax_status := NULL;
1380 gv_tax_rate_code := NULL;
1381 gv_tax_rate_id := NULL; --bug14241603
1382 gv_tax_jurisdiction_code := NULL;
1383
1384 if (gv_hierarchy_level = 2) or (gv_hierarchy_level = 3) or (gv_hierarchy_level = 4) then
1385 gv_tax_status := rule.tax_status_code;
1386 end if;
1387 if (gv_hierarchy_level = 3) or (gv_hierarchy_level = 4) then
1388 gv_tax_rate_code := rule.tax_rate_code;
1389 gv_tax_rate_id := rule.tax_rate_id; --bug14241603
1390 end if;
1391 if (gv_hierarchy_level = 4) then
1392 gv_tax_jurisdiction_code := rule.tax_jurisdiction_code;
1393 end if;
1394
1395 -- Assign the appropriate value to gv_allocation_rule_id variable
1396 gv_appl_alloc_rule_id := lv_allocation_rule_id;
1397
1398 if gv_debug_flag then
1399 fnd_file.put_line(fnd_file.log, 'allocate_box - afterHierarchy. gv_appl_alloc_rule_id2:'||gv_appl_alloc_rule_id);
1400 end if;
1401
1402 -- Get the minimum allocation rule ID
1403 open c_get_minimum_alloc_rule_id(pn_vat_reporting_entity_id, pv_period_type, pv_source, pv_event_class_code, pv_tax, lv_allocation_rule_id);
1404 fetch c_get_minimum_alloc_rule_id into gv_allocation_rule_id;
1405 close c_get_minimum_alloc_rule_id;
1406
1407 if gv_debug_flag then
1408 fnd_file.put_line(fnd_file.log, 'allocate_box - c_get_minimum_alloc_rule_id 1:'||gv_allocation_rule_id);
1409 end if;
1410
1411 IF gv_allocation_rule_id IS NULL THEN
1412 gv_allocation_rule_id := lv_allocation_rule_id;
1413 if gv_debug_flag then
1414 fnd_file.put_line(fnd_file.log, 'Only one allocation rule applicable' );
1415 end if;
1416 END IF;
1417
1418 if gv_debug_flag then
1419 fnd_file.put_line(fnd_file.log, 'allocate_box - c_get_minimum_alloc_rule_id 2:'||gv_allocation_rule_id);
1420 end if;
1421 if gv_debug_flag then
1422 fnd_file.put_line(fnd_file.log, 'allocate_box - afterHierarchy. gv_allocation_rule_id3:'||gv_allocation_rule_id);
1423 end if;
1424 exit;
1425 end if;
1426 end loop;
1427 else
1428
1429 if gv_debug_flag then
1430 fnd_file.put_line(fnd_file.log, 'allocate_box - Hierarchy Level not 0');
1431 end if;
1432 if gv_debug_flag then
1433 fnd_file.put_line(fnd_file.log, 'Params-'
1434 ||', pn_vat_reporting_entity_id:'||pn_vat_reporting_entity_id
1435 ||', pv_period_type:'||pv_period_type
1436 ||', pv_source:'||pv_source
1437 ||', pv_event_class_code:'||pv_event_class_code
1438 ||', pv_tax:'||pv_tax
1439 ||', pv_tax_status:'||pv_tax_status
1440 ||', pv_tax_jurisdiction:'||pv_tax_jurisdiction
1441 ||', pv_tax_rate_code:'||pv_tax_rate_code
1442 ||', pv_tax_rate_id:'||pv_tax_rate_id --bug14241603
1443 ||', pv_tax_recoverable_flag:'||pv_tax_recoverable_flag
1444 );
1445 end if;
1446
1447 lv_rule_found := g_no;
1448
1449 lv_allocation_rule_id := 0;
1450 if gv_debug_flag then
1451 fnd_file.put_line(fnd_file.log, 'allocate_box - Opening Cursor c_get_rules_stat_code_jrdict' );
1452 end if;
1453 open c_get_rules_stat_code_jrdict(pn_vat_reporting_entity_id, pv_period_type, pv_source, pv_event_class_code, pv_tax);
1454 fetch c_get_rules_stat_code_jrdict into lv_allocation_rule_id,
1455 lv_tax_box_recoverable,
1456 lv_tax_box_non_rec,
1457 lv_taxable_boxes;
1458
1459 close c_get_rules_stat_code_jrdict;
1460
1461 if gv_debug_flag then
1462 fnd_file.put_line(fnd_file.log, 'allocate_box - AFTER Cursor c_get_rules_stat_code_jrdict FETCH' );
1463 fnd_file.put_line(fnd_file.log, 'allocate_box - Allocation_rule_id '||lv_allocation_rule_id);
1464 fnd_file.put_line(fnd_file.log, 'allocate_box - Tax Box Recoverable '||lv_tax_box_recoverable);
1465 fnd_file.put_line(fnd_file.log, 'allocate_box - tax Box Non Recoverables '||lv_tax_box_non_rec);
1466 end if;
1467
1468 if lv_allocation_rule_id <> 0 then
1469 lv_rule_found := g_yes;
1470 gv_allocation_rule_id := lv_allocation_rule_id;
1471 end if;
1472
1473 end if;
1474
1475 if lv_rule_found = g_yes then
1476 xn_allocation_rule_id := lv_allocation_rule_id;
1477 xv_taxable_box := lv_taxable_boxes;
1478 if (pv_source = 'AP' and pv_tax_recoverable_flag = g_yes) or (pv_source = 'AR') or (pv_source = 'GL') then
1479 xv_tax_box := lv_tax_box_recoverable;
1480 else
1481 xv_tax_box := lv_tax_box_non_rec;
1482 end if;
1483 elsif lv_rule_found = g_no then
1484 if gv_debug_flag then
1485 fnd_file.put_line(fnd_file.log, 'allocate_box - Setting ERROR NO ALLOC RULE FOUND');
1486 end if;
1487 /* execution will come here if no matching rule is found */
1488 xv_error_code := JG_ZZ_VAT_ALLOC_PRC_PKG.g_alloc_errcode_rule_not_found;
1489 return;
1490 end if;
1491
1492 if gv_debug_flag then
1493 fnd_file.put_line(fnd_file.log, 'allocate_box - end. Hierarchy Path-'||lv_hierarchy_debug );
1494 end if;
1495
1496 exception
1497 when others then
1498 if gv_debug_flag then
1499 fnd_file.put_line(fnd_file.log, 'allocate_box - ERROR lv_statement:'||lv_statement );
1500 end if;
1501 xv_return_status := fnd_api.g_ret_sts_unexp_error;
1502 xv_return_message := 'jg_zz_vat_alloc_prc_pkg.allocate_box ~ Unexpected Error -' || sqlerrm;
1503
1504 end allocate_box;
1505
1506 FUNCTION get_allocation_status(
1507 pn_reporting_status_id NUMBER
1508 ) return varchar2 IS
1509 cursor c_get_allocation_status is
1510 select nvl(allocation_status_flag, g_no) allocation_status
1511 from jg_zz_vat_rep_status a
1512 where a.reporting_status_id = pn_reporting_status_id;
1513
1514 lv_allocation_status jg_zz_vat_rep_status.allocation_status_flag%TYPE;
1515
1516 begin
1517 if gv_debug_flag then
1518 fnd_file.put_line(fnd_file.log, 'get_allocation_status - start ->'||lv_allocation_status );
1519 end if;
1520 open c_get_allocation_status;
1521 fetch c_get_allocation_status into lv_allocation_status;
1522 close c_get_allocation_status;
1523
1524 if gv_debug_flag then
1525 fnd_file.put_line(fnd_file.log, 'get_allocation_status - return ->'||lv_allocation_status );
1526 end if;
1527
1528 return lv_allocation_status;
1529 end get_allocation_status;
1530
1531 END JG_ZZ_VAT_ALLOC_PRC_PKG;