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