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