[Home] [Help]
PACKAGE BODY: APPS.JAI_PA_TAX_PKG
Source
1 package body jai_pa_tax_pkg as
2 /* $Header: jai_pa_tax_pkg.plb 120.3.12020000.2 2013/03/22 05:26:46 vkaranam ship $ */
3
4 /*----------------------------------------------------------------------------------------
5 Change History
6 S.No. DATE Description
7 ------------------------------------------------------------------------------------------
8
9 1 24/04/1005 cbabu for bug#6012570 (5876390) Version: 120.0
10 Projects Billing Enh.
11 forward ported from R11i to R12
12
13
14 2 Sep-14-2010 Modified by Eric Ma for Bug10043656 ,GST enhancement
15
16
17 3 May-10-2011 Modfied by anupgupt for Bug12534669
18 Replaced the org_id with jpsv.org_id in cursor
19 cur_expn_tax_category
20
21 4. Dec-18-2012 For bug 16013918 by anupgupt
22 Removed GST changes
23 ---------------------------------------------------------------------------------------- */
24
25
26 ------------------------------------------------------------------------------------------------------------------------------------------
27 -------------------------------------------------------execution hirarchy--------------------------------------------------------------
28 ------------------------------------------------------------------------------------------------------------------------------------------
29 -- current parent condition calling object called object
30 -- level level
31 ------------------------------------------------------------------------------------------------------------------------------------------
32 -- 1 1 insert or trigger calc_taxes_for_invoices
33 -- update on pa_draft_invoices_all
34 --
35 -- 2 1 when request is not null calc_taxes_for_invoices dflt_taxes_for_invoice_lines
36 -- (work in loop for multi invoices)
37 --
38 -- 2 1 pn_project_id is not null calc_taxes_for_invoices dflt_taxes_for_invoice_lines
39 -- and pn_draft_invoice_num is not null
40 -- (call only single time invoice )
41 --
42 -- 3 2 dflt_taxes_for_invoice_lines calculate_taxes
43 --
44 -- 4 3 when event is default taxes calculate_taxes tax_defaultation_line
45 --
46 -- 4 3 when event is recalculate taxes calculate_taxes tax_recalculate_line
47 --
48 -- 5 4 getting tax category tax_defaultation_line get_tax_category
49 --
50 -- 6 5 according to preference defined tax_defaultation_line get_event_tax_category
51 --
52 -- 6 5 according to preference defined tax_defaultation_line get_project_tax_category
53 --
54 -- 6 5 according to preference defined tax_defaultation_line get_cust_tax_category
55 --
56 -- 6 5 according to preference defined tax_defaultation_line get_expn_tax_category
57 --
58 -- 7 6 insert line info after tax category tax_defaultation_line insert_line_info
59 --
60 -- 8 4 default taxes when tax category is not null tax_defaultation_line default_taxes
61 --
62 ---------------------------------------------------------------------------------------------------------------
63 -----------------------------------------global variable information--------------------------------------------
64 --important-----------------------------------------------------------------------------------------------------
65 ----------------------------------------------------------------------------------------------------------------
66 -- this following variable is just to store header level information
67 -- this variable no where initilaized and assigned again in package
68 -- these cariable initialized in following function
69 -- 1. dflt_taxes_for_invoice_lines
70 -- 2. initialize_variable
71
72 --lv_inv_currency_code
73 --ln_inv_exchange_rate
74 --ln_customer_id
75 --ln_bill_to_customer_id
76 --ln_ship_to_customer_id
77 --ln_bill_to_address_id
78 --ln_ship_to_address_id
79 --ln_draft_invoice_num_credited
80 --ln_write_off_flag
81 -------------------------------------------------------------------------------------------------------------
82 -- these variable is for to provide parent level information whenever
83 -- credit memo invoice is made and it is just passing parent invoice information
84 -- to il tables to store linking of credit invoice and parent project invoice to get the relation ship .
85 -- this variable initialize in following function
86 -- 1. insert_line_info
87
88 --ln_line_amt
89 --ln_draft_invoice_line_id
90 --ln_draft_invoice_id
91
92 -- this record type variable holding information regarding header level table
93 -- this information is common to all lines level records
94 -------------------------------------------------------------------------------------------------------------
95
96 type global_type is record
97 (
98 lv_inv_currency_code pa_draft_invoices_all.inv_currency_code%type ,
99 ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type ,
100 ln_customer_id pa_draft_invoices_all.customer_id%type ,
101 ln_bill_to_customer_id pa_draft_invoices_all.bill_to_customer_id%type ,
102 ln_ship_to_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
103 ln_bill_to_address_id pa_draft_invoices_all.bill_to_address_id%type ,
104 ln_ship_to_address_id pa_draft_invoices_all.ship_to_address_id%type ,
105 -- for credit memo ( invoice ) variables
106 ln_draft_invoice_num_credited pa_draft_invoices_all.draft_invoice_num_credited%type ,
107 ln_write_off_flag pa_draft_invoices_all.write_off_flag%type ,
108 ln_draft_invoice_line_id jai_pa_draft_invoice_lines.draft_invoice_line_id%type,
109 ln_draft_invoice_id jai_pa_draft_invoice_lines.draft_invoice_id%type ,
110 ln_line_amt jai_pa_draft_invoice_lines.line_amt%type
111 ) ;
112
113 pkg_global_type global_type ;
114
115
116 /*-------------------------------BEGIN LOCAL METHOD CALC_TAXES_FOR_INVOICES -----------------------------*/
117 procedure calc_taxes_for_invoices
118 (
119 err_buf out nocopy varchar2 ,
120 retcode out nocopy varchar2 ,
121 pn_request_id in pa_draft_invoices_all.request_id%type ,
122 pn_project_id in pa_draft_invoices_all.project_id%type ,
123 pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
124 pv_event in varchar2
125 ) is
126
127 -- cursor is responsible for getting range of invoices
128 lr_get_invoices pa_draft_invoices_all%rowtype ;
129 lv_process_flag varchar2(30);
130 lv_process_message varchar2(2000);
131 v_parent_request_id NUMBER;
132 req_status BOOLEAN := TRUE;
133 v_phase VARCHAR2(100);
134 v_status VARCHAR2(100);
135 v_dev_phase VARCHAR2(100);
136 v_dev_status VARCHAR2(100);
137 v_message VARCHAR2(100);
138
139 -- cursor for getting invoices detail depends upon request id
140 cursor cur_get_invoices is
141 select pa_draft_invoices_all.*
142 from pa_draft_invoices_all
143 where request_id = pn_request_id ;
144
145 -- cursor for getting one single invoice detail for corresponding project id and draft invoice num
146
147 Cursor cur_get_inv_detail is
148 select pa_draft_invoices_all.*
149 from pa_draft_invoices_all
150 where project_id = pn_project_id and
151 draft_invoice_num = pn_draft_invoice_num ;
152
153 begin
154 if pn_request_id is not null then
155 v_parent_request_id := pn_request_id ;
156 req_status := Fnd_concurrent.wait_for_request( v_parent_request_id,
157 60, -- default value - sleep time in secs
158 0, -- default value - max wait in secs
159 v_phase,
160 v_status,
161 v_dev_phase,
162 v_dev_status,
163 v_message );
164
165 IF v_dev_phase = 'COMPLETE' THEN
166 IF v_dev_status <> 'NORMAL' THEN
167 Fnd_File.put_line(Fnd_File.LOG, 'Exiting with warning as parent request not completed with normal status');
168 Fnd_File.put_line(Fnd_File.LOG, 'Message from parent request :' || v_message);
169 retcode := 1;
170 err_buf := 'Exiting with warningr as parent request not completed with normal status';
171 RETURN;
172 END IF;
173 END IF;
174 IF v_dev_phase = 'COMPLETE' /*OR v_dev_phase = 'INACTIVE'*/ THEN
175 IF v_dev_status = 'NORMAL' THEN
176 for r_get_invoices in cur_get_invoices
177 loop
178 dflt_taxes_for_invoice_lines(
179 r_new => r_get_invoices,
180 pv_action => pv_event,
181 pv_process_message => lv_process_message ,
182 pv_process_flag => lv_process_flag
183 );
184 end loop ;
185 end if ;
186 end if;
187 elsif ( pn_project_id is not null and pn_draft_invoice_num is not null ) then
188 open cur_get_inv_detail ;
189 fetch cur_get_inv_detail into lr_get_invoices ;
190 close cur_get_inv_detail ;
191 dflt_taxes_for_invoice_lines(
192 r_new => lr_get_invoices,
193 pv_action => pv_event,
194 pv_process_message => lv_process_message ,
195 pv_process_flag => lv_process_flag
196 );
197 end if;
198 end calc_taxes_for_invoices ;
199
200
201 /*-------------------------------begin local method dflt_taxes_for_invoice_lines -----------------------------*/
202
203
204 procedure dflt_taxes_for_invoice_lines(
205 r_new in pa_draft_invoices_all%rowtype,
206 pv_action in varchar2,
207 pv_process_message out nocopy varchar2,
208 pv_process_flag out nocopy varchar2
209 ) is
210
211 cursor cur_get_invoice_lines is
212 select pdii.*
213 from pa_draft_invoice_items pdii
214 where pdii.project_id = r_new.project_id and
215 pdii.draft_invoice_num = r_new.draft_invoice_num ;
216 begin
217 ---- package level global variables -----------------------------------------------------
218 -- package global variable help to
219 pkg_global_type.ln_customer_id := r_new.customer_id ;
220 pkg_global_type.ln_bill_to_customer_id := r_new.bill_to_customer_id ;
221 pkg_global_type.ln_ship_to_customer_id := r_new.ship_to_customer_id ;
222 pkg_global_type.ln_bill_to_address_id := r_new.bill_to_address_id ;
223 pkg_global_type.ln_ship_to_address_id := r_new.ship_to_address_id ;
224 pkg_global_type.lv_inv_currency_code := r_new.inv_currency_code ;
225 pkg_global_type.ln_inv_exchange_rate := r_new.projfunc_invtrans_ex_rate; /*Bug 13513070*/
226 pkg_global_type.ln_draft_invoice_num_credited := r_new.draft_invoice_num_credited;
227 pkg_global_type.ln_write_off_flag := r_new.write_off_flag;
228 pkg_global_type.ln_draft_invoice_line_id := null;
229 pkg_global_type.ln_draft_invoice_id := null;
230 pkg_global_type.ln_line_amt := null;
231 -----------------------------------------------------------------------------
232 for r_pa_draft_invoice_items in cur_get_invoice_lines
233 loop
234 jai_pa_tax_pkg.calculate_taxes(
235 r_new => r_pa_draft_invoice_items,
236 pv_action => pv_action,
237 pv_process_message => pv_process_message,
238 pv_process_flag => pv_process_flag
239 );
240 fnd_file.put_line(fnd_file.log,' fire tax default ' || pv_action || pv_process_message || pv_process_flag );
241 end loop ;
242 end dflt_taxes_for_invoice_lines ;
243
244
245 /*-------------------------------begin local method calculate_taxes -----------------------------*/
246 procedure calculate_taxes
247 (
248 r_new in pa_draft_invoice_items%rowtype,
249 pv_action in varchar2,
250 pv_process_message out nocopy varchar2,
251 pv_process_flag out nocopy varchar2
252 ) is
253 begin
254 if pv_action = jai_constants.default_taxes then
255 jai_pa_tax_pkg.tax_defaultation_line(
256 r_new => r_new ,
257 pv_action => pv_action,
258 pv_process_message => pv_process_message,
259 pv_process_flag => pv_process_flag
260 );
261 elsif pv_action = jai_constants.recalculate_taxes then
262 jai_pa_tax_pkg.tax_recalculate_line(
263 r_new => r_new,
264 pv_action => pv_action,
265 pv_process_message => pv_process_message,
266 pv_process_flag => pv_process_flag
267 );
268 end if ;
269 end calculate_taxes ;
270
271 /*-------------------------------begin local method tax_defaultation_line -----------------------------*/
272 procedure tax_defaultation_line
273 (
274 r_new in pa_draft_invoice_items%rowtype,
275 pv_action in varchar2,
276 pv_process_message out nocopy varchar2,
277 pv_process_flag out nocopy varchar2
278 ) is
279 ----------------variable declarations---------------------------
280 ln_tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ;
281 ln_draft_invoice_id jai_pa_draft_invoice_lines.draft_invoice_id%type ;
282 ln_draft_invoice_line_id jai_pa_draft_invoice_lines.draft_invoice_line_id%type ;
283 ln_amount number ;
284 ln_tax_amount number ;
285 -----------------------------------------------------------------
286 begin
287 ----initialization part -----------------------------------------------------
288 ln_tax_category_id := null ;
289 ln_draft_invoice_id := null ;
290 ln_draft_invoice_line_id := null ;
291 ln_amount := 0 ;
292 ln_tax_amount := 0 ;
293 -----------------------------------------------------------------------------
294
295 -- assumption
296 -- 1. for customer
297 -- first priority - customer / site
298 -- second priority - customer null site
299 -- 2. in ship_to_customer id and bill to customer id
300 -- if ship to customer_id is not present then consider bill to customer id
301 -- step 1 - responsible for getting tax category for project id , invoice num , line num on basis of distribution rule
302
303 -- for credit memo invoice generation , tax category is not required
304 if pkg_global_type.ln_draft_invoice_num_credited is null then
305 jai_pa_tax_pkg.get_tax_category(
306 r_new.project_id ,
307 r_new.draft_invoice_num ,
308 r_new.line_num ,
309 r_new.event_task_id ,
310 r_new.event_num ,
311 pv_action ,
312 pv_process_message ,
313 pv_process_flag ,
314 ln_tax_category_id ) ;
315 end if;
316 -- step 2 - responsible for populating project information in india localalization table
317 -- it will populate india localization table even if tax category id is null
318
319 jai_pa_tax_pkg.insert_line_info(
320 r_new ,
321 ln_tax_category_id ,
322 pv_action ,
323 ln_draft_invoice_id ,
324 ln_draft_invoice_line_id ,
325 pv_process_message ,
326 pv_process_flag
327 );
328 -- step 3 - responsible for default taxes
329 -------------if tax category id is null then no defaultation happen
330 if ln_tax_category_id is not null or pkg_global_type.ln_draft_invoice_num_credited is not null then
331 /*Bug 13513070 - Replaced INV_AMOUNT with AMOUNT as AMOUNT stores in Functional Currency*/
332 ln_amount := r_new.amount ; -- invoice amount instead of amount
333 jai_pa_tax_pkg.default_taxes(
334 pn_tax_category_id => ln_tax_category_id,
335 pn_draft_invoice_id => ln_draft_invoice_id,
336 pn_draft_invoice_line_id => ln_draft_invoice_line_id,
337 pv_called_from => null,
338 pn_line_amount => ln_amount,
339 pn_tax_amount => ln_tax_amount,
340 pv_process_message => pv_process_message,
341 pv_process_flag => pv_process_flag
342 ) ;
343 end if ;
344 end tax_defaultation_line ;
345
346 /*-------------------------------BEGIN LOCAL METHOD GET_TAX_CATEGORY -----------------------------*/
347
348 procedure get_tax_category(
349 pn_project_id in pa_draft_invoices_all.project_id%type ,
350 pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
351 pn_line_num in pa_draft_invoice_items.line_num%type ,
352 pn_event_task_id in pa_draft_invoice_items.event_task_id%type,
353 pn_event_num in pa_draft_invoice_items.event_num%type,
354 pv_action in varchar2,
355 pv_process_message out nocopy varchar2,
356 pv_process_flag out nocopy varchar2,
357 pn_tax_category_id out nocopy JAI_CMN_TAX_CTGS_ALL.tax_category_id%type
358 ) is
359
360 ----------------VARIABLE DECLARATIONS---------------------------
361 lv_distribution_rule pa_projects_all.distribution_rule%type ;
362 ln_tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ;
363 ln_process_status varchar2(20) ;
364 -----------------------------------------------------------------
365 ----------------cursor declarations------------------------------
366 -- responsible for getting distribution rule of project id
367 cursor cur_distribution_rule is
368 select ppa.distribution_rule
369 from pa_projects_all ppa
370 where ppa.project_id = pn_project_id and
371 distribution_rule is not null ;
372
373 -- responsible for getting prefernece and context from india localization project setup table
374 cursor cur_context_preference(cv_distribution_rule pa_projects_all.distribution_rule%type ) is
375 select jpsp.distribution_rule , jpsc.context ,jpsp.preference
376 from jai_pa_setup_contexts jpsc , jai_pa_setup_preferences jpsp
377 where jpsc.context_id = jpsp.context_id and
378 jpsp.distribution_rule = cv_distribution_rule
379 order by jpsp.preference asc ;
380 ------------------------------------------------------------------
381
382 begin
383 ----initialization part -----------------------------------------------------
384 lv_distribution_rule := null ;
385 ln_tax_category_id := null ;
386 ln_process_status := jai_pa_tax_pkg.process_fail;
387 -----------------------------------------------------------------------------
388
389 open cur_distribution_rule ;
390 fetch cur_distribution_rule into lv_distribution_rule ;
391 close cur_distribution_rule ;
392
393 if lv_distribution_rule is null then
394 pv_process_message := 'distribution rule not found ';
395 pv_process_flag := jai_constants.expected_error;
396 return ;
397 end if ;
398
399 -- ln_process_status -- process status works as flag in which it tell about previous process was sucessfull or not
400 -- if successfull then it comes out from loop other wise continue with other one .
401 for rec_context_prefernece in cur_context_preference(lv_distribution_rule)
402 loop
403 if jai_constants.setup_event_type = rec_context_prefernece.context then
404 -- tax category for events
405 jai_pa_tax_pkg.get_event_tax_category(
406 pn_project_id ,
407 pn_draft_invoice_num ,
408 pn_line_num ,
409 pn_event_task_id ,
410 pn_event_num ,
411 ln_tax_category_id ,
412 ln_process_status ,
413 pv_process_message ,
414 pv_process_flag
415 ) ;
416 -- exit when tax category is found for events
417 pn_tax_category_id := ln_tax_category_id ;
418 exit when ln_process_status = jai_pa_tax_pkg.process_success ;
419 end if ;
420 if jai_constants.setup_expenditure_type = rec_context_prefernece.context then
421 -- tax category for expenditure
422 jai_pa_tax_pkg.get_expn_tax_category(
423 pn_project_id ,
424 pn_draft_invoice_num ,
425 pn_line_num ,
426 ln_tax_category_id ,
427 ln_process_status ,
428 pv_process_message ,
429 pv_process_flag
430 ) ;
431
432 -- exit when tax category is found for expenditure
433 pn_tax_category_id := ln_tax_category_id ;
434 exit when ln_process_status = jai_pa_tax_pkg.process_success ;
435 end if ;
436 if jai_constants.setup_project = rec_context_prefernece.context then
437 -- tax category for projects
438 jai_pa_tax_pkg.get_project_tax_category(
439 pn_project_id ,
440 pn_draft_invoice_num ,
441 pn_line_num ,
442 ln_tax_category_id ,
443 ln_process_status ,
444 pv_process_message ,
445 pv_process_flag
446 ) ;
447
448 -- exit when tax category is found for projects
449 pn_tax_category_id := ln_tax_category_id ;
450 exit when ln_process_status = jai_pa_tax_pkg.process_success ;
451 end if ;
452
453 if jai_constants.setup_customer_site = rec_context_prefernece.context then
454 -- tax category for customer/site or customer null site
455 jai_pa_tax_pkg.get_cust_tax_category(
456 pn_project_id ,
457 pn_draft_invoice_num ,
458 pn_line_num ,
459 ln_tax_category_id ,
460 ln_process_status ,
461 pv_process_message ,
462 pv_process_flag
463 ) ;
464 -- exit when tax category is found for customer/site or customer null site .
465 pn_tax_category_id := ln_tax_category_id ;
466 exit when ln_process_status = jai_pa_tax_pkg.process_success ;
467 end if ;
468 end loop ;
469 pv_process_message := '';
470 pv_process_flag := jai_constants.successful;
471 exception when others then
472 PV_PROCESS_MESSAGE := SUBSTR('GET_TAX_CATEGORY='|| SQLERRM,1,1999);
473 pv_process_flag := jai_constants.expected_error;
474 end get_tax_category ;
475 /*-------------------------------BEGIN LOCAL METHOD GET_EVENT_TAX_CATEGORY -----------------------------*/
476 procedure get_event_tax_category(
477 pn_project_id in pa_draft_invoices_all.project_id%type ,
478 pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
479 pn_line_num in pa_draft_invoice_items.line_num%type ,
480 pn_event_task_id in pa_draft_invoice_items.event_task_id%type,
481 pn_event_num in pa_draft_invoice_items.event_num%type,
482 pn_tax_category_id out nocopy JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
483 pv_process_status out nocopy varchar2 ,
484 pv_process_message out nocopy varchar2,
485 pv_process_flag out nocopy varchar2
486 ) is
487
488 ----------------VARIABLE DECLARATIONS---------------------------
489 ln_event_task_id pa_draft_invoice_items.event_task_id%type ;
490 ln_event_num pa_draft_invoice_items.event_num%type ;
491 ln_event_type pa_events.event_type%type ;
492 ln_event_type_id pa_event_types.event_type_id%type ;
493 ln_tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%type;
494 ln_org_id NUMBER; /*Bug 8348822*/
495
496 ----------------cursor declarations------------------------------
497 -- responsible for getting event type from pa_events
498
499 cursor cur_get_event_type is
500 select pe.event_type
501 from pa_events pe
502 where pe.project_id = pn_project_id and
503 ( pe.task_id = ln_event_task_id or pe.task_id is null ) and
504 pe.event_num = ln_event_num ;
505
506
507 -- responsible for getting event type id from pa_events_types
508 cursor cur_get_event_type_id is
509 select event_type_id
510 from pa_event_types
511 where event_type = ln_event_type ;
512
513 -- responsible for getting tax category for event
514 cursor cur_get_tax_category is
515 select setup_value1 tax_category
516 from jai_pa_setup_values
517 where context = jai_constants.setup_event_type and -- this will search only for events not for other context types
518 attribute1 = ln_event_type_id and
519 org_id = ln_org_id; /*Bug 8348822 - Tax defaulted based on ORG_ID of the project*/
520
521 begin
522 ----initialization part -----------------------------------------------------
523 pv_process_status := jai_pa_tax_pkg.process_fail; -- package global variable
524 pn_tax_category_id := null ;
525 ln_event_task_id := null ;
526 ln_event_num := null ;
527 ln_event_type := null ;
528 ln_event_type_id := null ;
529 ln_tax_category_id := null ;
530 -----------------------------------------------------------------------------
531 ln_event_task_id := pn_event_task_id ;
532 ln_event_num := pn_event_num ;
533
534 /*Bug 8348822 - Fetch ORG_ID of the project*/
535 select org_id into ln_org_id
536 from pa_projects_all
537 where project_id = pn_project_id;
538
539 -- step1
540 -- if event_task_id and ln_evnet_num is found null then returns ........................
541 if ln_event_num is null then
542 pv_process_status := jai_pa_tax_pkg.process_fail;
543 pn_tax_category_id := null ;
544 return ;
545 end if ;
546
547 open cur_get_event_type ;
548 fetch cur_get_event_type into ln_event_type ;
549 close cur_get_event_type ;
550
551 -- if no event type it's found then returns
552 if ln_event_type is null then
553 pv_process_status := jai_pa_tax_pkg.process_fail;
554 pn_tax_category_id := null ;
555 return ;
556 end if ;
557
558 open cur_get_event_type_id ;
559 fetch cur_get_event_type_id into ln_event_type_id ;
560 close cur_get_event_type_id ;
561
562 -- if event_type_id is not presnet then returns
563 if ln_event_type_id is null then
564 pv_process_status := jai_pa_tax_pkg.process_fail;
565 pn_tax_category_id := null ;
566 return ;
567 end if ;
568
569 open cur_get_tax_category;
570 fetch cur_get_tax_category into ln_tax_category_id ;
571 close cur_get_tax_category ;
572
573 -- if tax_category_id is not presnet then returns
574 if ln_tax_category_id is null then
575 pv_process_status := jai_pa_tax_pkg.process_fail;
576 pn_tax_category_id := null ;
577 return ;
578 end if ;
579
580 pn_tax_category_id := ln_tax_category_id ;
581 pv_process_status := jai_pa_tax_pkg.process_success;
582
583 pv_process_message := '';
584 pv_process_flag := jai_constants.successful;
585
586 exception when others then
587 pv_process_status := jai_pa_tax_pkg.process_fail;
588 pn_tax_category_id := null ;
589 pv_process_message := substr('get_event_tax_category='|| sqlerrm,1,1999);
590 pv_process_flag := jai_constants.unexpected_error;
591 end get_event_tax_category ;
592
593 /*-------------------------------begin local method get_project_tax_category -----------------------------*/
594 procedure get_project_tax_category
595 ( pn_project_id in pa_draft_invoices_all.project_id%type ,
596 pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
597 pn_line_num in pa_draft_invoice_items.line_num%type ,
598 pn_tax_category_id out nocopy JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
599 pv_process_status out nocopy varchar2 ,
600 pv_process_message out nocopy varchar2,
601 pv_process_flag out nocopy varchar2
602 ) is
603 ----------------VARIABLE DECLARATIONS---------------------------
604 ln_tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%type;
605 ln_org_id NUMBER; /*Bug 8348822*/
606 ----------------CURSOR DECLARATIONS------------------------------
607 cursor cur_get_project_all is
608 select setup_value1 tax_category
609 from jai_pa_setup_values
610 where context = jai_constants.setup_project and
611 attribute1 = pn_project_id and
612 org_id = ln_org_id; /*Bug 8348822 - Tax defaulted based on ORG_ID of the project*/
613
614 begin
615 ----initialization part -----------------------------------------------------
616 pv_process_status := jai_pa_tax_pkg.process_fail;
617 pn_tax_category_id := null ;
618 ln_tax_category_id := null ;
619 -----------------------------------------------------------------------------
620
621 /*Bug 8348822 - Fetch ORG_ID of the project*/
622 select org_id into ln_org_id
623 from pa_projects_all
624 where project_id = pn_project_id;
625
626 open cur_get_project_all ;
627 fetch cur_get_project_all into ln_tax_category_id ;
628 close cur_get_project_all ;
629
630 if ln_tax_category_id is null then
631 pv_process_status := jai_pa_tax_pkg.process_fail;
632 pn_tax_category_id := null ;
633 return ;
634 end if ;
635
636 pn_tax_category_id := ln_tax_category_id ;
637 pv_process_status := jai_pa_tax_pkg.process_success;
638
639 pv_process_message := '';
640 pv_process_flag := jai_constants.successful;
641
642 exception when others then
643 pv_process_status := jai_pa_tax_pkg.process_fail;
644 pn_tax_category_id := null ;
645
646 pv_process_message := substr('get_project_tax_category='|| sqlerrm,1,1999);
647 pv_process_flag := jai_constants.unexpected_error;
648 end get_project_tax_category;
649 /*-------------------------------begin local method get_cust_tax_category -----------------------------*/
650 procedure get_cust_tax_category
651 ( pn_project_id in pa_draft_invoices_all.project_id%type ,
652 pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
653 pn_line_num in pa_draft_invoice_items.line_num%type ,
654 pn_tax_category_id out nocopy JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
655 pv_process_status out nocopy varchar2 ,
656 pv_process_message out nocopy varchar2,
657 pv_process_flag out nocopy varchar2
658 ) is
659 ----------------variable declarations---------------------------
660 ln_tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%type;
661 ln_tax_customer_id pa_draft_invoices_all.ship_to_customer_id%type ;
662 ln_tax_address_id pa_draft_invoices_all.ship_to_address_id%type ;
663 ln_org_id NUMBER; /*Bug 8348822*/
664 ----------------cursor declarations------------------------------
665 -- responsible for getting tax category attached with customer / site combination or customer / null site combination
666 cursor cur_cust_site_tax(cn_customer_id pa_draft_invoices_all.ship_to_customer_id%type ,
667 cn_address_id pa_draft_invoices_all.ship_to_address_id%type) is
668 select setup_value1
669 from jai_pa_setup_values
670 where attribute1 = cn_customer_id and
671 attribute2 = cn_address_id and
672 context = jai_constants.setup_customer_site and
673 org_id = ln_org_id; /*Bug 8348822 - Tax defaulted based on ORG_ID of the project*/
674
675 cursor cur_cust_tax(cn_customer_id pa_draft_invoices_all.ship_to_customer_id%type) is
676 select setup_value1
677 from jai_pa_setup_values
678 where attribute1 = cn_customer_id and
679 context = jai_constants.setup_customer_site and
680 attribute2 is null and
681 org_id = ln_org_id; /*Bug 8348822 - Tax defaulted based on ORG_ID of the project*/
682 begin
683 ----initialization part -----------------------------------------------------
684 pv_process_status := jai_pa_tax_pkg.process_fail;
685 pn_tax_category_id := null ;
686 ln_tax_category_id := null ;
687 -----------------------------------------------------------------------------
688 -- responsible for getting customer info
689
690 /*Bug 8348822 - Fetch ORG_ID of the project*/
691 select org_id into ln_org_id
692 from pa_projects_all
693 where project_id = pn_project_id;
694
695 if pkg_global_type.ln_ship_to_customer_id is null then
696 ln_tax_customer_id := pkg_global_type.ln_bill_to_customer_id ;
697 ln_tax_address_id := pkg_global_type.ln_bill_to_address_id ;
698 else
699 ln_tax_customer_id := pkg_global_type.ln_ship_to_customer_id ;
700 ln_tax_address_id := pkg_global_type.ln_ship_to_address_id ;
701 end if ;
702
703 if ln_tax_address_id is not null then
704 open cur_cust_site_tax(ln_tax_customer_id,ln_tax_address_id) ;
705 fetch cur_cust_site_tax into ln_tax_category_id ;
706 close cur_cust_site_tax ;
707 end if ;
708
709 if ln_tax_category_id is null or ln_tax_address_id is null then
710 open cur_cust_tax(ln_tax_customer_id) ;
711 fetch cur_cust_tax into ln_tax_category_id ;
712 close cur_cust_tax ;
713 end if ;
714
715 if ln_tax_category_id is null then
716 pv_process_status := jai_pa_tax_pkg.process_fail;
717 pn_tax_category_id := null ;
718 return ;
719 end if ;
720
721 -- when process sucess in getting tax category----------------------------------------
722 pn_tax_category_id := ln_tax_category_id ;
723 pv_process_status := jai_pa_tax_pkg.process_success;
724 pv_process_message := '';
725 pv_process_flag := jai_constants.successful;
726 ---------------------------------------------------------------------------------------
727 exception when others then
728 pv_process_status := jai_pa_tax_pkg.process_fail;
729 pn_tax_category_id := null ;
730 pv_process_message := substr('get_event_tax_category='|| sqlerrm,1,1999);
731 pv_process_flag := jai_constants.unexpected_error;
732 end get_cust_tax_category;
733
734 /*-------------------------------BEGIN LOCAL METHOD GET_EXPN_TAX_CATEGORY -----------------------------*/
735 procedure get_expn_tax_category
736 ( pn_project_id in pa_draft_invoices_all.project_id%type ,
737 pn_draft_invoice_num in pa_draft_invoices_all.draft_invoice_num%type ,
738 pn_line_num in pa_draft_invoice_items.line_num%type ,
739 pn_tax_category_id out nocopy JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
740 pv_process_status out nocopy varchar2 ,
741 pv_process_message out nocopy varchar2,
742 pv_process_flag out nocopy varchar2
743 ) is
744 ----------------variable declarations---------------------------
745 ln_tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%type;
746 ln_count_expend_type number ;
747 lv_expenditure_type pa_expenditure_types.expenditure_type%type ;
748 ln_org_id NUMBER; /*Bug 8348822*/
749 ----------------cursor declarations------------------------------
750 -- count different types of expenditure defined in invoices lines
751 cursor cur_count_expend_type is
752 select count(distinct expenditure_type)
753 from pa_expenditure_items_all peia
754 where peia.expenditure_item_id in
755 ( select expenditure_item_id
756 from pa_cust_rev_dist_lines_all pdida
757 where pdida.project_id = pn_project_id and
758 pdida.draft_invoice_num = pn_draft_invoice_num and
759 pdida.draft_invoice_item_line_num = pn_line_num
760 );
761 -- find out expenditure type if invoice lines contains same type of expenditure type
762 cursor cur_expend_type is
763 select distinct expenditure_type
764 from pa_expenditure_items_all peia
765 where peia.expenditure_item_id in
766 ( select expenditure_item_id
767 from pa_cust_rev_dist_lines_all pdida
768 where pdida.project_id = pn_project_id and
769 pdida.draft_invoice_num = pn_draft_invoice_num and
770 pdida.draft_invoice_item_line_num = pn_line_num
771 );
772
773 -- find out tax category for expenditure types;
774 cursor cur_expn_tax_category(cv_expenditure_type pa_expenditure_types.expenditure_type%type ) is
775 select jpsv.setup_value1
776 from jai_pa_setup_values jpsv,
777 pa_expenditure_types pet
778 where jpsv.context = jai_constants.setup_expenditure_type and
779 jpsv.attribute1 = pet.expenditure_type_id and
780 pet.expenditure_type =cv_expenditure_type and
781 jpsv.org_id = ln_org_id; /*Bug 8348822 - Tax defaulted based on
782 ORG_ID of the project*/ /* Bug 12534669 Added alias jpsv to org_id column*/
783 begin
784 ----initialization part -----------------------------------------------------
785 pv_process_status := jai_pa_tax_pkg.process_fail;
786 pn_tax_category_id := null ;
787 ln_tax_category_id := null ;
788 ln_count_expend_type := 0 ;
789 lv_expenditure_type := null ;
790 -----------------------------------------------------------------------------
791
792 /*Bug 8348822 - Fetch ORG_ID of the project*/
793 select org_id into ln_org_id
794 from pa_projects_all
795 where project_id = pn_project_id;
796
797 open cur_count_expend_type ;
798 fetch cur_count_expend_type into ln_count_expend_type ;
799 close cur_count_expend_type ;
800
801 -- if expenditure defined for draft invoice lines is greater than 1 then skip this one and continue with next preference
802 if nvl(ln_count_expend_type,0) = 0 or nvl(ln_count_expend_type,0) > 1 then
803 pv_process_status := jai_pa_tax_pkg.process_fail;
804 pn_tax_category_id := null ;
805 return ;
806 end if ;
807
808 open cur_expend_type;
809 fetch cur_expend_type into lv_expenditure_type ;
810 close cur_expend_type ;
811
812 open cur_expn_tax_category( lv_expenditure_type ) ;
813 fetch cur_expn_tax_category into ln_tax_category_id ;
814 close cur_expn_tax_category ;
815
816 -- when process successful in getting tax category----------------------------------------
817 if ln_tax_category_id is null then
818 pv_process_status := jai_pa_tax_pkg.process_fail;
819 pn_tax_category_id := null ;
820 return ;
821 end if ;
822
823 pn_tax_category_id := ln_tax_category_id ;
824 pv_process_status := jai_pa_tax_pkg.process_success;
825
826 pv_process_message := '';
827 pv_process_flag := jai_constants.successful;
828 ---------------------------------------------------------------------------------------
829 exception when others then
830 pv_process_status := jai_pa_tax_pkg.process_fail;
831 pn_tax_category_id := null ;
832
833 pv_process_message := substr('get_event_tax_category='|| sqlerrm,1,1999);
834 pv_process_flag := jai_constants.unexpected_error;
835 end get_expn_tax_category ;
836
837 /*-------------------------------BEGIN LOCAL METHOD INSERT_LINE_INFO -----------------------------*/
838
839 procedure insert_line_info (
840 r_new in pa_draft_invoice_items%rowtype,
841 pn_tax_category_id in JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ,
842 pv_action in varchar2,
843 pn_draft_invoice_id out nocopy jai_pa_draft_invoice_lines.draft_invoice_id%type ,
844 pn_draft_invoice_line_id out nocopy jai_pa_draft_invoice_lines.draft_invoice_line_id%type ,
845 pv_process_message out nocopy varchar2,
846 pv_process_flag out nocopy varchar2
847 ) is
848
849 ln_draft_invoice_id jai_pa_draft_invoice_lines.draft_invoice_id%type ;
850 ln_draft_invoice_line_id jai_pa_draft_invoice_lines.draft_invoice_line_id%type ;
851 ln_count number ;
852 -- credit memo changes
853 ----------------------------------------------------------------------------------------
854 ln_organization_id jai_pa_draft_invoices.organization_id%type;
855 ln_location_id jai_pa_draft_invoices.location_id%type;
856 lv_service_type_code jai_pa_draft_invoice_lines.service_type_code%type;
857 ln_parent_draft_invoice_id jai_pa_draft_invoice_lines.parent_draft_invoice_id%type;
858 ln_parent_tax_category_id jai_pa_draft_invoice_lines.tax_category_id%type;
859 ln_tax_category_id jai_pa_draft_invoice_lines.tax_category_id%type;
860 ----------------------------------------------------------------------------------------
861 -- get draft invoice id for existing draft invoice lines
862
863 cursor cur_draft_invoice_id is
864 select draft_invoice_id
865 from jai_pa_draft_invoices
866 where project_id = r_new.project_id and
867 draft_invoice_num = r_new.draft_invoice_num ;
868
869 -- generate sequence for header id
870 cursor cur_draft_inv_seq is
871 select jai_pa_draft_invoices_s.nextval
872 from dual ;
873
874 -- generate sequence for line id
875 cursor cur_draft_inv_line_seq is
876 select jai_pa_draft_invoice_lines_s.nextval
877 from dual ;
878
879 --for credit memo invoice generation
880 -- this cursor find out organization id and location id from header table
881
882 --credit memo invoice changes ----------------------------------------
883
884 cursor cur_get_header_var is
885 select organization_id , location_id , draft_invoice_id
886 from jai_pa_draft_invoices
887 where project_id = r_new.project_id and
888 draft_invoice_num = pkg_global_type.ln_draft_invoice_num_credited;
889
890 cursor cur_get_detail_var is
891 select service_type_code ,draft_invoice_id , draft_invoice_line_id ,line_amt , tax_category_id
892 from jai_pa_draft_invoice_lines
893 where project_id =r_new.project_id and
894 draft_invoice_num =pkg_global_type.ln_draft_invoice_num_credited and
895 line_num =r_new.draft_inv_line_num_credited ;
896 --end ----------------------------------------
897
898 begin
899
900 ----initialization part -----------------------------------------------------
901 ln_draft_invoice_id := null ;
902 ln_draft_invoice_line_id := null ;
903 ln_count := 0 ;
904 ln_organization_id := null ;
905 ln_location_id := null ;
906 lv_service_type_code := null ;
907 ln_parent_draft_invoice_id := null ;
908 ln_parent_tax_category_id := null ;
909 ln_tax_category_id := null ;
910
911 pkg_global_type.ln_draft_invoice_id := null ;
912 pkg_global_type.ln_draft_invoice_line_id := null ;
913 -----------------------------------------------------------------------------
914
915 --step 1 -- checking for draft invoice id whether record is already present or not
916 open cur_draft_invoice_id ;
917 fetch cur_draft_invoice_id into ln_draft_invoice_id ;
918 close cur_draft_invoice_id ;
919
920 --step 1.1 -- for credit memo invoices , get organization id and location id
921 if pkg_global_type.ln_draft_invoice_num_credited is not null then
922 open cur_get_header_var ;
923 fetch cur_get_header_var into ln_organization_id , ln_location_id ,ln_parent_draft_invoice_id;
924 close cur_get_header_var ;
925
926 open cur_get_detail_var ;
927 fetch cur_get_detail_var into lv_service_type_code ,pkg_global_type.ln_draft_invoice_id , pkg_global_type.ln_draft_invoice_line_id ,pkg_global_type.ln_line_amt ,ln_parent_tax_category_id ;
928 close cur_get_detail_var ;
929
930 ln_tax_category_id := ln_parent_tax_category_id ;
931 else
932 ln_tax_category_id := pn_tax_category_id ;
933 end if ;
934
935 --step 2 -- if it found null then sequence generated number is used
936 if ln_draft_invoice_id is null then
937 open cur_draft_inv_seq ;
938 fetch cur_draft_inv_seq into ln_draft_invoice_id ;
939 close cur_draft_inv_seq;
940
941 insert into jai_pa_draft_invoices (
942 draft_invoice_id ,
943 project_id ,
944 draft_invoice_num ,
945 organization_id ,
946 location_id ,
947 creation_date ,
948 created_by ,
949 last_update_date ,
950 last_updated_by ,
951 last_update_login ,
952 parent_draft_invoice_id
953 )
954 values (
955 ln_draft_invoice_id ,
956 r_new.project_id ,
957 r_new.draft_invoice_num ,
958 ln_organization_id ,
959 ln_location_id ,
960 sysdate ,
961 fnd_global.user_id ,
962 sysdate ,
963 fnd_global.user_id ,
964 fnd_global.login_id ,
965 ln_parent_draft_invoice_id
966 ) ;
967 end if ;
968 --step3 -- generate line id from sequence generated number ;
969 open cur_draft_inv_line_seq ;
970 fetch cur_draft_inv_line_seq into ln_draft_invoice_line_id ;
971 close cur_draft_inv_line_seq;
972
973 --step4 -- insert information into il table
974 insert into jai_pa_draft_invoice_lines(
975 draft_invoice_line_id ,
976 draft_invoice_id ,
977 project_id ,
978 draft_invoice_num ,
979 line_num ,
980 line_amt ,
981 line_tax_amt ,
982 tax_category_id ,
983 creation_date ,
984 created_by ,
985 last_update_date ,
986 last_updated_by ,
987 last_update_login ,
988 service_type_code ,
989 parent_draft_invoice_id ,
990 parent_draft_invoice_line_id
991 )
992 values (
993 ln_draft_invoice_line_id ,
994 ln_draft_invoice_id ,
995 r_new.project_id ,
996 r_new.draft_invoice_num ,
997 r_new.line_num ,
998 r_new.amount ,
999 null ,
1000 ln_tax_category_id ,
1001 sysdate ,
1002 fnd_global.user_id ,
1003 sysdate ,
1004 fnd_global.user_id ,
1005 fnd_global.login_id ,
1006 lv_service_type_code ,
1007 pkg_global_type.ln_draft_invoice_id ,
1008 pkg_global_type.ln_draft_invoice_line_id
1009 );
1010
1011 -- step5 - return invoice id and line id to calling object for further use .....
1012 pn_draft_invoice_id := ln_draft_invoice_id;
1013 pn_draft_invoice_line_id := ln_draft_invoice_line_id;
1014
1015 pv_process_message := '';
1016 pv_process_flag := jai_constants.successful;
1017
1018 exception when others then
1019 pv_process_message := substr('insert_line_info='|| sqlerrm,1,1999);
1020 pv_process_flag := jai_constants.unexpected_error;
1021 end insert_line_info;
1022
1023 /*-------------------------------BEGIN LOCAL METHOD DEFAULT_TAXES -----------------------------*/
1024 procedure default_taxes (
1025 pn_tax_category_id number ,
1026 pn_draft_invoice_id number ,
1027 pn_draft_invoice_line_id number ,
1028 pn_line_amount number ,
1029 pv_called_from varchar2 default null ,
1030 pn_tax_amount out nocopy number ,
1031 pv_process_message out nocopy varchar2 ,
1032 pv_process_flag out nocopy varchar2
1033 ) is
1034
1035 ----------------variable declarations---------------------------
1036 ln_tax_amount number ;
1037 ln_tax_category_id JAI_CMN_TAX_CTGS_ALL.tax_category_id%type ;
1038 lv_inv_currency_code pa_draft_invoices_all.inv_currency_code%type;
1039 ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type;
1040 ----------------------------------------------------------------
1041 -- responsible to check for tax category id whether it is already exists .
1042 cursor cur_get_tax_caegotry is
1043 select tax_category_id
1044 from jai_cmn_document_taxes
1045 where source_doc_id = pn_draft_invoice_id and
1046 source_doc_line_id = pn_draft_invoice_line_id and
1047 source_doc_type = jai_constants.pa_draft_invoice ;
1048
1049 cursor cur_get_inv_info is
1050 select pdia.inv_currency_code , nvl(projfunc_invtrans_ex_rate,1) /*13513070 - Replaced inv_exchange_rate*/
1051 from pa_draft_invoices_all pdia, jai_pa_draft_invoice_lines jpdil
1052 where pdia.project_id = jpdil.project_id and
1053 pdia.draft_invoice_num = jpdil.draft_invoice_num and
1054 jpdil.draft_invoice_line_id = pn_draft_invoice_line_id and
1055 draft_invoice_id = pn_draft_invoice_id ;
1056
1057 cursor cur_get_tax_amount is
1058 select sum(tax_amt)
1059 from jai_cmn_document_taxes
1060 where source_doc_id = pn_draft_invoice_id and
1061 source_doc_line_id = pn_draft_invoice_line_id and
1062 source_doc_type = jai_constants.pa_draft_invoice ;
1063 -- for credit invoice generation
1064 begin
1065 ln_tax_amount := 0 ;
1066 -- for credit invoice generation , tax category should be copy from parent to credit memo
1067 if pkg_global_type.ln_draft_invoice_num_credited is not null then
1068 insert into jai_cmn_document_taxes
1069 (
1070 doc_tax_id ,
1071 tax_line_no ,
1072 tax_id ,
1073 tax_type ,
1074 currency_code ,
1075 tax_rate ,
1076 qty_rate ,
1077 uom ,
1078 tax_amt ,
1079 func_tax_amt ,
1080 modvat_flag ,
1081 tax_category_id ,
1082 source_doc_type ,
1083 source_doc_id ,
1084 source_doc_line_id ,
1085 source_table_name ,
1086 tax_modified_by ,
1087 adhoc_flag ,
1088 precedence_1 ,
1089 precedence_2 ,
1090 precedence_3 ,
1091 precedence_4 ,
1092 precedence_5 ,
1093 precedence_6 ,
1094 precedence_7 ,
1095 precedence_8 ,
1096 precedence_9 ,
1097 precedence_10 ,
1098 creation_date ,
1099 created_by ,
1100 last_update_date ,
1101 last_updated_by ,
1102 last_update_login
1103 )
1104 select jai_cmn_document_taxes_s.nextval ,
1105 j1.tax_line_no ,
1106 j1.tax_id ,
1107 j1.tax_type ,
1108 j1.currency_code ,
1109 j1.tax_rate ,
1110 j1.qty_rate ,
1111 j1.uom ,
1112 round(((pn_line_amount * ((j1.tax_amt *100)/pkg_global_type.ln_line_amt ) )/100),nvl(j2.rounding_factor,0)),
1113 round(((pn_line_amount * ((j1.func_tax_amt *100)/pkg_global_type.ln_line_amt ) )/100),nvl(j2.rounding_factor,0)) ,
1114 j1.modvat_flag , j1.tax_category_id ,
1115 j1.source_doc_type , pn_draft_invoice_id ,
1116 pn_draft_invoice_line_id ,
1117 j1.source_table_name ,
1118 j1.tax_modified_by ,
1119 j1.adhoc_flag ,
1120 j1.precedence_1 ,
1121 j1.precedence_2 ,
1122 j1.precedence_3 ,
1123 j1.precedence_4 ,
1124 j1.precedence_5 ,
1125 j1.precedence_6 ,
1126 j1.precedence_7 ,
1127 j1.precedence_8 ,
1128 j1.precedence_9 ,
1129 j1.precedence_10 ,
1130 sysdate ,
1131 fnd_global.user_id ,
1132 sysdate ,
1133 fnd_global.user_id ,
1134 fnd_global.login_id
1135 from jai_cmn_document_taxes j1 , JAI_CMN_TAXES_ALL j2
1136 where j1.source_doc_id = pkg_global_type.ln_draft_invoice_id and
1137 j1.source_doc_line_id = pkg_global_type.ln_draft_invoice_line_id and
1138 j1.source_doc_type = jai_constants.pa_draft_invoice and
1139 j1.tax_id = j2.tax_id ;
1140
1141
1142
1143 open cur_get_tax_amount ;
1144 fetch cur_get_tax_amount into ln_tax_amount ;
1145 close cur_get_tax_amount;
1146
1147 update jai_pa_draft_invoice_lines
1148 set line_tax_amt = ln_tax_amount
1149 where draft_invoice_line_id = pn_draft_invoice_line_id and
1150 draft_invoice_id = pn_draft_invoice_id ;
1151
1152 pn_tax_amount := ln_tax_amount ;
1153
1154 else
1155
1156 lv_inv_currency_code := pkg_global_type.lv_inv_currency_code;
1157 if lv_inv_currency_code = jai_constants.func_curr then
1158 ln_inv_exchange_rate := 1 ; -- for multi currecncy support
1159 else
1160 ln_inv_exchange_rate := (1/nvl(pkg_global_type.ln_inv_exchange_rate,1)); -- for multi currecncy support
1161 end if;
1162
1163 if pn_tax_category_id is null then
1164 pv_process_message := 'tax category can not be null ';
1165 pv_process_flag := jai_constants.expected_error;
1166 return ;
1167 end if;
1168
1169 open cur_get_tax_caegotry ;
1170 fetch cur_get_tax_caegotry into ln_tax_category_id ;
1171 close cur_get_tax_caegotry ;
1172
1173 --if tax category id is present and it is different then delete from taxes table
1174 if ln_tax_category_id is not null and ln_tax_category_id <> pn_tax_category_id then
1175 delete from jai_cmn_document_taxes
1176 where source_doc_id = pn_draft_invoice_id and
1177 source_doc_line_id = pn_draft_invoice_line_id and
1178 source_doc_type = jai_constants.pa_draft_invoice ;
1179 end if ;
1180
1181 IF PV_CALLED_FROM IS NOT NULL AND PV_CALLED_FROM = 'JAINRWDI' THEN
1182 -- procedure is being called from invoice review - india ui hence get currency related
1183 -- information from the header table (pa_draft_invoices_all).
1184 --
1185 -- if procedure is being called from trigger this control should never come here
1186 -- it will result in mutation
1187 open cur_get_inv_info;
1188 fetch cur_get_inv_info
1189 into lv_inv_currency_code,
1190 ln_inv_exchange_rate ;
1191 close cur_get_inv_info;
1192
1193 if lv_inv_currency_code = jai_constants.func_curr then
1194 ln_inv_exchange_rate := 1 ; -- for multi currecncy support
1195 else
1196 ln_inv_exchange_rate := (1/nvl(ln_inv_exchange_rate,1)); -- FOR MULTI CURRECNCY SUPPORT
1197 END IF;
1198 END IF; -- PV_CALLED_FROM
1199
1200 -- if it is null or tax category id is different then default the taxes
1201 IF LN_TAX_CATEGORY_ID IS NULL OR LN_TAX_CATEGORY_ID <> PN_TAX_CATEGORY_ID THEN
1202 LN_TAX_AMOUNT := PN_LINE_AMOUNT;
1203
1204 jai_cmn_tax_defaultation_pkg.JA_IN_CALC_PREC_TAXES
1205 (
1206 TRANSACTION_NAME => JAI_CONSTANTS.PA_DRAFT_INVOICE,
1207 P_TAX_CATEGORY_ID => PN_TAX_CATEGORY_ID,
1208 P_HEADER_ID => PN_DRAFT_INVOICE_ID,
1209 P_LINE_ID => PN_DRAFT_INVOICE_LINE_ID,
1210 P_TAX_AMOUNT => LN_TAX_AMOUNT,
1211 P_INVENTORY_ITEM_ID => NULL,
1212 P_ASSESSABLE_VALUE => LN_TAX_AMOUNT,
1213 P_VAT_ASSESSABLE_VALUE => LN_TAX_AMOUNT,
1214 P_LINE_QUANTITY => 1,
1215 P_UOM_CODE => NULL,
1216 P_VENDOR_ID => NULL,
1217 P_CURRENCY => LV_INV_CURRENCY_CODE,
1218 P_CURRENCY_CONV_FACTOR => LN_INV_EXCHANGE_RATE, -- FOR MULTI CURRENCT SUPPORT
1219 P_CREATION_DATE => SYSDATE,
1220 P_CREATED_BY => FND_GLOBAL.USER_ID,
1221 P_LAST_UPDATE_DATE => SYSDATE,
1222 P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1223 P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
1224 P_SOURCE_TRX_TYPE => JAI_CONSTANTS.PA_DRAFT_INVOICE,
1225 P_SOURCE_TABLE_NAME => 'JAI_PA_DRAFT_INVOICE_LINES',
1226 P_ACTION => JAI_CONSTANTS.DEFAULT_TAXES
1227 /* for bug 16013918 by anupgupt
1228 , pn_gst_assessable_value=> ln_tax_amount -- Added by Eric Ma for Bug10043656 ,GST enhancement on Sep-14-2010
1229 */
1230 ) ;
1231
1232
1233 pn_tax_amount := ln_tax_amount;
1234
1235 update jai_pa_draft_invoice_lines
1236 set line_tax_amt = pn_tax_amount
1237 where draft_invoice_line_id = pn_draft_invoice_line_id and
1238 draft_invoice_id = pn_draft_invoice_id ;
1239 end if ;
1240 END IF ;
1241 PV_PROCESS_MESSAGE := '';
1242 PV_PROCESS_FLAG := JAI_CONSTANTS.SUCCESSFUL;
1243 EXCEPTION WHEN OTHERS THEN
1244 PV_PROCESS_MESSAGE := SUBSTR('DEFAULT_TAXES : ' || sqlerrm , 1,1999);
1245 PV_PROCESS_FLAG := JAI_CONSTANTS.UNEXPECTED_ERROR;
1246 END DEFAULT_TAXES;
1247
1248 /*-------------------------------BEGIN LOCAL METHOD SYNC_DELETION -----------------------------*/
1249
1250 procedure sync_deletion (
1251 pn_project_id number,
1252 pn_draft_invoice_num number,
1253 pv_process_message out nocopy varchar2,
1254 pv_process_flag out nocopy varchar2
1255 ) is
1256 begin
1257 delete from jai_cmn_document_taxes
1258 where ( source_doc_id , source_doc_line_id ) in
1259 ( select draft_invoice_id , draft_invoice_line_id
1260 from jai_pa_draft_invoice_lines
1261 where project_id = pn_project_id and
1262 draft_invoice_num = pn_draft_invoice_num
1263 ) ;
1264
1265 delete from jai_pa_draft_invoice_lines
1266 where project_id = pn_project_id and
1267 draft_invoice_num = pn_draft_invoice_num ;
1268
1269 delete from jai_pa_draft_invoices
1270 where project_id =pn_project_id and
1271 draft_invoice_num = pn_draft_invoice_num ;
1272
1273 pv_process_message := '';
1274 pv_process_flag := jai_constants.successful;
1275
1276 exception when others then
1277 pv_process_message := substr('sync_deletion : ' || sqlerrm , 1,1999);
1278 pv_process_flag := jai_constants.unexpected_error;
1279 end sync_deletion;
1280 /*-------------------------------BEGIN LOCAL METHOD INITIALIZE_VARIABLE -----------------------------*/
1281
1282 procedure initialize_variable ( pn_project_id number ,
1283 pn_draft_invoice_num number ,
1284 pv_process_message out nocopy varchar2,
1285 pv_process_flag out nocopy varchar2 ) is
1286 cursor cur_project_header is
1287 select
1288 inv_currency_code,
1289 nvl(projfunc_invtrans_ex_rate,1), /*Bug 13513070 - Replaced inv_exchange_rate*/
1290 customer_id,
1291 bill_to_customer_id,
1292 ship_to_customer_id,
1293 bill_to_address_id,
1294 ship_to_address_id,
1295 draft_invoice_num_credited,
1296 write_off_flag
1297 from pa_draft_invoices_all
1298 where project_id = pn_project_id and
1299 draft_invoice_num = pn_draft_invoice_num ;
1300
1301 begin
1302 open cur_project_header ;
1303 fetch cur_project_header
1304 into
1305 pkg_global_type.lv_inv_currency_code ,
1306 pkg_global_type.ln_inv_exchange_rate ,
1307 pkg_global_type.ln_customer_id ,
1308 pkg_global_type.ln_bill_to_customer_id ,
1309 pkg_global_type.ln_ship_to_customer_id ,
1310 pkg_global_type.ln_bill_to_address_id ,
1311 pkg_global_type.ln_ship_to_address_id ,
1312 pkg_global_type.ln_draft_invoice_num_credited ,
1313 pkg_global_type.ln_write_off_flag ;
1314
1315 close cur_project_header ;
1316 pv_process_message := '';
1317 pv_process_flag := jai_constants.successful;
1318 exception when others then
1319 pv_process_message := substr('sync_deletion : ' || sqlerrm , 1,1999);
1320 pv_process_flag := jai_constants.unexpected_error;
1321 end initialize_variable ;
1322
1323 /*-------------------------------begin local method tax_recalculate_line -----------------------------*/
1324
1325 procedure tax_recalculate_line
1326 (
1327 r_new in pa_draft_invoice_items%rowtype,
1328 pv_action in varchar2,
1329 pv_process_message out nocopy varchar2,
1330 pv_process_flag out nocopy varchar2
1331 ) is
1332
1333 lv_inv_currency_code pa_draft_invoices_all.inv_currency_code%type;
1334 ln_inv_exchange_rate pa_draft_invoices_all.inv_exchange_rate%type;
1335 ln_tax_amount number ;
1336
1337 cursor cur_get_line_detail is
1338 select draft_invoice_id , draft_invoice_line_id
1339 from jai_pa_draft_invoice_lines
1340 where project_id = r_new.project_id and
1341 draft_invoice_num = r_new.draft_invoice_num and
1342 line_num = r_new.line_num ;
1343
1344
1345 begin
1346 lv_inv_currency_code := pkg_global_type.lv_inv_currency_code;
1347 if lv_inv_currency_code = jai_constants.func_curr then
1348 ln_inv_exchange_rate := 1 ; -- for multi currecncy support
1349 else
1350 ln_inv_exchange_rate := (1/nvl(pkg_global_type.ln_inv_exchange_rate,1)); -- for multi currecncy support
1351 end if;
1352
1353 FOR R_GET_LINE_DETAIL IN CUR_GET_LINE_DETAIL
1354 LOOP
1355 LN_TAX_AMOUNT := R_NEW.INV_AMOUNT ;
1356 jai_cmn_tax_defaultation_pkg.JA_IN_CALC_PREC_TAXES
1357 (
1358 TRANSACTION_NAME => JAI_CONSTANTS.PA_DRAFT_INVOICE,
1359 P_TAX_CATEGORY_ID => -1 , -- for recalculation tax category not required
1360 P_HEADER_ID => R_GET_LINE_DETAIL.DRAFT_INVOICE_ID,
1361 P_LINE_ID => R_GET_LINE_DETAIL.DRAFT_INVOICE_LINE_ID,
1362 P_TAX_AMOUNT => LN_TAX_AMOUNT,
1363 P_INVENTORY_ITEM_ID => NULL,
1364 P_ASSESSABLE_VALUE => LN_TAX_AMOUNT,
1365 P_VAT_ASSESSABLE_VALUE => LN_TAX_AMOUNT,
1366 P_LINE_QUANTITY => 1,
1367 P_UOM_CODE => NULL,
1368 P_VENDOR_ID => NULL,
1369 P_CURRENCY => LV_INV_CURRENCY_CODE,
1370 P_CURRENCY_CONV_FACTOR => LN_INV_EXCHANGE_RATE, -- for multi currenct support
1371 P_CREATION_DATE => SYSDATE,
1372 P_CREATED_BY => FND_GLOBAL.USER_ID,
1373 P_LAST_UPDATE_DATE => SYSDATE,
1374 P_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
1375 P_LAST_UPDATE_LOGIN => FND_GLOBAL.LOGIN_ID,
1376 P_SOURCE_TRX_TYPE => JAI_CONSTANTS.PA_DRAFT_INVOICE,
1377 P_SOURCE_TABLE_NAME => 'JAI_PA_DRAFT_INVOICE_LINES',
1378 P_ACTION => JAI_CONSTANTS.RECALCULATE_TAXES
1379 /* for bug 16013918 by anupgupt
1380 , pn_gst_assessable_value => ln_tax_amount -- Added by Eric Ma for Bug10043656 ,GST enhancement on Sep-14-2010
1381 */
1382 ) ;
1383
1384 update jai_pa_draft_invoice_lines
1385 set line_tax_amt = ln_tax_amount ,
1386 line_amt = r_new.inv_amount
1387 where draft_invoice_line_id = r_get_line_detail.draft_invoice_line_id and
1388 draft_invoice_id = r_get_line_detail.draft_invoice_id ;
1389
1390 end loop ;
1391 end tax_recalculate_line ;
1392
1393 END jai_pa_tax_pkg ;