[Home] [Help]
PACKAGE BODY: APPS.JAI_PA_COSTING_PKG
Source
1 package body jai_pa_costing_pkg as
2 /* $Header: jai_pa_costing.plb 120.11.12020000.2 2012/07/14 09:54:19 nkodakan ship $*/
3 /*------------------------------------------------------------------------------------------------------------
4 CHANGE HISTORY
5 ------------------------------------------------------------------------------------------------------------
6 Sl.No. Date Developer BugNo Version Remarks
7 ------------------------------------------------------------------------------------------------------------
8 1. 17/JAN/2007 brathod 5765161 115.0 Created the initial version
9 2. 05/APR/2007 cbabu 5765161 115.1 Changes done as required for Budget07
10
11 1. 24-APR-2007 cbabu 6012567 120.0 Forward ported to R12 from R11i taking 115.1 version
12 2. 08/Aug/2007 brathod 6321215 120.1 Using deiver transaction quantity instead of
13 quantity as populated in PA_TRANSACTION_INTERFACE
14 for deriving the apportion factor
15
16 3. 28-SEP-2007 Bgowrava 6452772 120.3 For the Transaction type as 'RETURN TO RECEIVING'
17 the non recoverable tax amount is returned as negative.
18 4. 27-NOV-2007 Jia Li Changed function get_nonrec_tax_amount
19 for Tax inclusive Computations.
20 5. 24-Jun-2009 mbremkum 8400140 Modified the code in the procedure
21 update_interface_costs. Added the cursors
22 c_get_non_rec_taxes and c_ja_in_rcv_trx.
23 6. 09-Jul-2009 mbremkum 8660365 Appropriation factor was calculated using quantity from Base
24 Project Line. In R12 Projects populates amounts into Quantity.
25 Hence replaced it with the Delivered Quantity.
26
27 7. 15-Apr-2010 Bo Li 9305067 Replace the old attribute_category columns for JAI_RCV_TRANSACTIONS
28 with new meaningful one
29
30 8. 06-Jul-2010 Jia 7242428 Forward ported to R12.2 from R11i bug#7145704
31 Added the function get_comtmnt_amt
32
33 9. 06-Jul-2010 Jia 8805693 Forward ported to R12.2 from R11i bug#8702421 and bug#8438564
34 Added the procedure update_interface_cost_tax
35
36 10. 06-Jul-2010 Jia 9863751 Forward ported to R12.2 from R11i bug#9188112
37 Added Post_process procedure
38
39 11. 04-May-2012 mbremkum 14026359 Description: Non Recoverables Taxes in Receipt are coming as a
40 positive cost to Projects when Return to Vendor is done on the Receipt
41 Fix: Set Tax Amount to negative when Transaction Type is 'RETURN TO RECEIVING'
42
43 --------------------------------------------------------------------------------------------------------------*/
44 /*----------------------------------------- PRIVATE MEMBERS DECLRATION -------------------------------------*/
45
46 /** Package level variables used in debug package*/
47 lv_object_name jai_cmn_debug_contexts.log_context%type := 'JAI_PA_COSTING_PKG';
48 lv_member_name jai_cmn_debug_contexts.log_context%type;
49 lv_context jai_cmn_debug_contexts.log_context%type;
50 --
51 -- Global variables used throught the package
52 --
53 lv_user_id fnd_user.user_id%type default fnd_global.user_id;
54 lv_login_id fnd_logins.login_id%type default fnd_global.login_id;
55
56 -- Package constants
57 GV_TRX_SRC_PO_RECEIPT constant varchar2 (30) := 'PO RECEIPT';
58 GV_TRX_SRC_PO_RCPT_PRICE_ADJ constant varchar2 (30) := 'PO RECEIPT PRICE ADJ';
59
60 gn_func_amount number;
61 gn_trx_amount number;
62 gv_trx_source varchar2(100);
63 gv_line_type varchar2(30);
64 gn_trx_id number;
65
66
67 procedure set_debug_context
68 is
69
70 begin
71 lv_context := rtrim(lv_object_name || '.'||lv_member_name,'.');
72 end set_debug_context;
73
74
75 function get_func_curr_indicator return varchar2 is
76 begin
77 return gv_functional_currency;
78 end get_func_curr_indicator;
79
80 function get_trx_curr_indicator return varchar2 is
81 begin
82 return gv_transaction_currency;
83 end get_trx_curr_indicator;
84
85 function get_nonrec_tax_amount(
86
87 pv_transaction_source in varchar2,
88 pv_line_type in varchar2,
89 pn_transaction_header_id in number,
90 pn_transaction_dist_id in number, /* One of PO_REQ_DISTRIBUTIONS_ALL.distribution_id, PO_DISTRIBUTIONS_ALL.po_distribution_id, RCV_TRANSACTIONS.transaction_id, AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id */
91 pv_currency_of_return_tax_amt in varchar2 default null, /* no value is passed, then tax amount in transaction currency is returned */
92 pv_transaction_uom in varchar2 default null, /* if not given, then conversion of UOM w.r.to main transaction will not be performed */
93 pn_transaction_qty in number default null,
94 pn_currency_conv_rate in number default null
95
96 ) return number is
97
98 ln_nonreco_tax_amt number;
99 ln_trx_nonreco_tax_amt number;
100 ln_func_nonreco_tax_amt number;
101
102 ln_currency_conv_rate number;
103 lv_currency_of_return_tax_amt varchar2(30);
104
105 ln_apportion_factor number;
106 lv_src_type_rtr VARCHAR2(50); --bgowrava for Bug#6452772
107
108 cursor c_get_reqn_dist_dtl(pn_req_dist_id in number) is
109 select requisition_line_id, req_line_quantity
110 from po_req_distributions_all
111 where distribution_id = pn_req_dist_id;
112 r_get_reqn_dist_dtl c_get_reqn_dist_dtl%rowtype;
113
114 cursor c_get_reqn_line_dtl(pn_req_line_id in number) is
115 select quantity
116 from po_requisition_lines_all
117 where requisition_line_id = pn_req_line_id;
118 r_get_reqn_line_dtl c_get_reqn_line_dtl%rowtype;
119
120 cursor c_get_po_dist_dtl(pn_po_dist_id in number) is
121 select line_location_id, quantity_ordered
122 from po_distributions_all
123 where po_distribution_id = pn_po_dist_id;
124 r_get_po_dist_dtl c_get_po_dist_dtl%rowtype;
125
126 cursor c_get_po_line_loc_dtl(pn_line_loc_id in number) is
127 select quantity
128 from po_line_locations_all
129 where line_location_id = pn_line_loc_id;
130 r_get_po_line_loc_dtl c_get_po_line_loc_dtl%rowtype;
131
132 cursor c_ja_in_rcv_trx(cp_transaction_id in number) is
133 select *
134 from JAI_RCV_TRANSACTIONS
135 where transaction_id = cp_transaction_id;
136 r_ja_in_receive_trx c_ja_in_rcv_trx%rowtype;
137 r_ja_in_deliver_trx c_ja_in_rcv_trx%rowtype;
138
139 ln_reg_id number;
140
141 -- Added by Jia Li for Tax inclusive Computations on 2007/11/27, Begin
142 -- TD16-Changed Project Costing
143 -- these variables storage inclusive reco and non-reco tax amt
144 -----------------------------------------------------------------------
145 ln_trx_inclu_reco_tax_amt NUMBER;
146 ln_func_inclu_reco_tax_amt NUMBER;
147 ---------------------------------------------------------------------------------------
148 -- Added by Jia Li for Tax inclusive Computations on 2007/11/27, End
149
150 begin
151 lv_src_type_rtr := 'RETURN TO RECEIVING'; --bgowrava for Bug#6452772
152 lv_member_name := 'GET_NONREC_TAX_AMOUNT';
153 set_debug_context;
154 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
155 , pn_reg_id => ln_reg_id
156 );
157 if pn_currency_conv_rate is null or pn_currency_conv_rate = 0 then
158 ln_currency_conv_rate := 1;
159 else
160 ln_currency_conv_rate := nvl(pn_currency_conv_rate, 1);
161 end if;
162 lv_currency_of_return_tax_amt := nvl(pv_currency_of_return_tax_amt, gv_transaction_currency);
163
164 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'lv_currency_of_return_tax_amt='||lv_currency_of_return_tax_amt );
165
166 /* this logic is to avoid recalculation of amounts if called multiple times for the same transaction dtls consecutively*/
167 if gv_trx_source = pv_transaction_source
168 and gv_line_type = pv_line_type
169 and gn_trx_id = pn_transaction_dist_id
170 then
171 ln_func_nonreco_tax_amt := gn_func_amount;
172 ln_trx_nonreco_tax_amt := gn_trx_amount;
173 ln_func_inclu_reco_tax_amt := gn_func_amount; -- Added by Jia Li for Tax inclusive Computations on 2007/11/27
174 ln_trx_inclu_reco_tax_amt := gn_trx_amount; -- Added by Jia Li for Tax inclusive Computations on 2007/11/27
175 goto return_amount;
176 else
177 gv_trx_source := pv_transaction_source;
178 gv_line_type := pv_line_type;
179 gn_trx_id := pn_transaction_dist_id;
180 end if;
181
182
183 /* find the apportion factor */
184 /* apportion should consider only quantity */
185 -- NULL;
186
187 /* calculate the non recoverable tax */
188 /* this should consider currency, recoverable percentage and apportion factor when calculating the tax */
189
190 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'pv_line_type='||pv_line_type || ', pv_transaction_source='||pv_transaction_source);
191
192 if pv_transaction_source = JAI_PA_COSTING_PKG.gv_src_oracle_purchasing then
193
194 /* 1 - REQUISITONS */
195 if pv_line_type = JAI_PA_COSTING_PKG.gv_line_type_requisition then
196
197 open c_get_reqn_dist_dtl(pn_transaction_dist_id);
198 fetch c_get_reqn_dist_dtl into r_get_reqn_dist_dtl;
199 close c_get_reqn_dist_dtl;
200
201 select
202 nvl(
203 sum(
204 decode(nvl(a.currency, 'INR'), 'INR',
205 a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1, (1- nvl(b.mod_cr_percentage,0)/100)),
206 a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1, (1- nvl(b.mod_cr_percentage,0)/100)) * ln_currency_conv_rate
207 )
208 ),0) functional_tax_amount ,
209 nvl(
210 sum(
211 decode(nvl(a.currency, 'INR'), 'INR',
212 a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1, (1- nvl(b.mod_cr_percentage,0)/100)) / ln_currency_conv_rate,
213 a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1, (1- nvl(b.mod_cr_percentage,0)/100))
214 )
215 ),0) transaction_tax_amount
216 into ln_func_nonreco_tax_amt, ln_trx_nonreco_tax_amt
217 from JAI_PO_REQ_LINE_TAXES a, JAI_CMN_TAXES_ALL b
218 where a.tax_id = b.tax_id
219 and (
220 ( pn_transaction_dist_id is not null and a.requisition_line_id = r_get_reqn_dist_dtl.requisition_line_id )
221 or (pn_transaction_dist_id is null and a.requisition_header_id = pn_transaction_header_id)
222 );
223
224 -- Added by Jia Li for Tax inclusive Computations on 2007/11/27, Begin
225 -- TD16-Changed Project Costing
226 -- Get inclusive tax amount
227 -----------------------------------------------------------------------
228 SELECT
229 NVL(
230 SUM( DECODE(NVL(a.currency, 'INR'), 'INR', a.tax_amount,
231 a.tax_amount * ln_currency_conv_rate)
232 ),0) functional_tax_amount,
233 NVL(
234 SUM( DECODE(NVL(a.currency, 'INR'), 'INR', a.tax_amount/ln_currency_conv_rate,
235 a.tax_amount)
236 ),0) transaction_tax_amount
237 INTO
238 ln_func_inclu_reco_tax_amt
239 , ln_trx_inclu_reco_tax_amt
240 FROM
241 jai_po_req_line_taxes a
242 , jai_cmn_taxes_all b
243 WHERE a.tax_id = b.tax_id
244 AND NVL(b.inclusive_tax_flag, 'N') = 'Y'
245 AND ( ( pn_transaction_dist_id IS NOT NULL
246 AND
247 a.requisition_line_id = r_get_reqn_dist_dtl.requisition_line_id )
248 OR
249 ( pn_transaction_dist_id IS NULL
250 AND
251 a.requisition_header_id = pn_transaction_header_id )
252 );
253 ---------------------------------------------------------------------------------------
254 -- Added by Jia Li for Tax inclusive Computations on 2007/11/27, End
255
256 open c_get_reqn_line_dtl(r_get_reqn_dist_dtl.requisition_line_id);
257 fetch c_get_reqn_line_dtl into r_get_reqn_line_dtl;
258 close c_get_reqn_line_dtl;
259
260 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'pn_transaction_qty='||pn_transaction_qty ||',r_get_reqn_line_dtl.quantity='||r_get_reqn_line_dtl.quantity );
261
262 if pn_transaction_qty is not null
263 and pn_transaction_qty <> 0
264 and r_get_reqn_line_dtl.quantity is not null
265 and r_get_reqn_line_dtl.quantity <> 0
266 and r_get_reqn_line_dtl.quantity <> pn_transaction_qty
267 then
268 ln_apportion_factor := pn_transaction_qty / r_get_reqn_line_dtl.quantity;
269 else
270 ln_apportion_factor := 1;
271 end if;
272
273 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_apportion_factor='||ln_apportion_factor);
274
275 ln_func_nonreco_tax_amt := ln_func_nonreco_tax_amt * ln_apportion_factor;
276 ln_trx_nonreco_tax_amt := ln_trx_nonreco_tax_amt * ln_apportion_factor;
277
278 ln_func_inclu_reco_tax_amt := ln_func_inclu_reco_tax_amt * ln_apportion_factor; -- Added by Jia Li for Tax inclusive Computations on 2007/11/27
279 ln_trx_inclu_reco_tax_amt := ln_trx_inclu_reco_tax_amt * ln_apportion_factor; -- Added by Jia Li for Tax inclusive Computations on 2007/11/27
280
281
282 /* 2 - PURCHASE ORDERS */
283 elsif pv_line_type = JAI_PA_COSTING_PKG.gv_line_type_purchasing then
284
285 open c_get_po_dist_dtl(pn_transaction_dist_id);
286 fetch c_get_po_dist_dtl into r_get_po_dist_dtl;
287 close c_get_po_dist_dtl;
288
289 select
290 nvl(
291 sum(
292 decode(nvl(a.currency, 'INR'), 'INR',
293 a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)),
294 a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)) * ln_currency_conv_rate
295 )
296 ),0) functional_tax_amount ,
297 nvl(
298 sum(
299 decode(nvl(a.currency, 'INR'), 'INR',
300 a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)) / ln_currency_conv_rate,
301 a.tax_amount * decode( nvl(a.modvat_flag, 'N'), 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100))
302 )
303 ),0) transaction_tax_amount
304 into ln_func_nonreco_tax_amt, ln_trx_nonreco_tax_amt
305 from JAI_PO_TAXES a, JAI_CMN_TAXES_ALL b
306 where a.tax_id = b.tax_id
307 and (
308 ( pn_transaction_dist_id is not null and a.line_location_id = r_get_po_dist_dtl.line_location_id )
309 or ( pn_transaction_dist_id is null and a.po_header_id = pn_transaction_header_id)
310 );
311
312 -- Added by Jia Li for Tax inclusive Computations on 2007/11/27, Begin
313 -- TD16-Changed Project Costing
314 -- Get inclusive tax amount
315 -----------------------------------------------------------------------
316 SELECT
317 NVL(
318 SUM( DECODE(NVL(a.currency, 'INR'), 'INR', a.tax_amount ,
319 a.tax_amount * ln_currency_conv_rate)
320 ),0) functional_tax_amount ,
321 NVL(
322 SUM( DECODE(NVL(a.currency, 'INR'), 'INR', a.tax_amount/ln_currency_conv_rate,
323 a.tax_amount)
324 ),0) transaction_tax_amount
325 INTO
326 ln_func_inclu_reco_tax_amt
327 , ln_trx_inclu_reco_tax_amt
328 FROM
329 jai_po_taxes a
330 , jai_cmn_taxes_all b
331 WHERE a.tax_id = b.tax_id
332 AND NVL(b.inclusive_tax_flag, 'N') = 'Y'
333 AND ( ( pn_transaction_dist_id IS NOT NULL
334 AND
335 a.line_location_id = r_get_po_dist_dtl.line_location_id )
336 OR
337 ( pn_transaction_dist_id IS NULL
338 AND
339 a.po_header_id = pn_transaction_header_id )
340 );
341 ---------------------------------------------------------------------------------------
342 -- Added by Jia Li for Tax inclusive Computations on 2007/11/27, End
343
344 open c_get_po_line_loc_dtl(r_get_po_dist_dtl.line_location_id);
345 fetch c_get_po_line_loc_dtl into r_get_po_line_loc_dtl;
346 close c_get_po_line_loc_dtl;
347
348 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'pn_transaction_qty='||pn_transaction_qty ||',r_get_po_line_loc_dtl.quantity='||r_get_po_line_loc_dtl.quantity);
349
350 if pn_transaction_qty is not null
351 and pn_transaction_qty <> 0
352 and r_get_po_line_loc_dtl.quantity is not null
353 and r_get_po_line_loc_dtl.quantity <> 0
354 and r_get_po_line_loc_dtl.quantity <> pn_transaction_qty
355 then
356 ln_apportion_factor := pn_transaction_qty / r_get_po_line_loc_dtl.quantity;
357 else
358 ln_apportion_factor := 1;
359 end if;
360
361 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_apportion_factor='||ln_apportion_factor);
362
363 ln_func_nonreco_tax_amt := ln_func_nonreco_tax_amt * ln_apportion_factor;
364 ln_trx_nonreco_tax_amt := ln_trx_nonreco_tax_amt * ln_apportion_factor;
365
366 ln_func_inclu_reco_tax_amt := ln_func_inclu_reco_tax_amt * ln_apportion_factor; -- Added by Jia Li for Tax inclusive Computations on 2007/11/27
367 ln_trx_inclu_reco_tax_amt := ln_trx_inclu_reco_tax_amt * ln_apportion_factor; -- Added by Jia Li for Tax inclusive Computations on 2007/11/27
368
369 /* 3 - PURCHASE RECEIPTS */
370 elsif pv_line_type = JAI_PA_COSTING_PKG.gv_line_type_po_receipt then
371
372
373 open c_ja_in_rcv_trx(pn_transaction_dist_id);
374 fetch c_ja_in_rcv_trx into r_ja_in_deliver_trx;
375 close c_ja_in_rcv_trx;
376
377 select
378 /* functional tax amount calc */
379 nvl(
380 sum(
381 decode(nvl(a.currency, 'INR'), 'INR',
382 a.tax_amount
383 * decode(
384 decode(
385 r_ja_in_deliver_trx.cenvat_costed_flag, 'Y', --Modified by Bo Li for bug9305067 replace the attribute2 with cenvat_costed_flag
386 decode(upper(b.tax_type), 'EXCISE', 'N', 'OTHER EXCISE', 'N', 'ADDL. EXCISE', 'N'
387 , 'CVD', 'N', 'EXCISE_EDUCATION_CESS', 'N', 'CVD_EDUCATION_CESS', 'N'
388 , 'ADDITIONAL_CVD', 'N'
389 , jai_constants.tax_type_sh_exc_edu_cess, 'N'
390 , jai_constants.tax_type_sh_cvd_edu_cess, 'N'
391 , a.modvat_flag)
392 , a.modvat_flag
393 ),
394 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)),
395 /* if not INR, then following logic will be applied */
396 a.tax_amount
397 * decode(
398 decode(
399 r_ja_in_deliver_trx.cenvat_costed_flag, 'Y',--Modified by Bo Li for bug9305067 replace the attribute2 with cenvat_costed_flag
400 decode(upper(b.tax_type), 'EXCISE', 'N', 'OTHER EXCISE', 'N', 'ADDL. EXCISE', 'N'
401 , 'CVD', 'N', 'EXCISE_EDUCATION_CESS', 'N', 'CVD_EDUCATION_CESS', 'N'
402 , 'ADDITIONAL_CVD', 'N'
403 , jai_constants.tax_type_sh_exc_edu_cess, 'N'
404 , jai_constants.tax_type_sh_cvd_edu_cess, 'N'
405 , a.modvat_flag)
406 , a.modvat_flag
407 ),
408 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)
409 )
410 * ln_currency_conv_rate
411 )
412 ),0) functional_tax_amount ,
413 /* transaction tax amount calc */
414 nvl(
415 sum(
416 decode(nvl(a.currency, 'INR'), 'INR',
417 a.tax_amount
418 * decode(
419 decode(
420 r_ja_in_deliver_trx.cenvat_costed_flag, 'Y',--Modified by Bo Li for bug9305067 replace the attribute2 with cenvat_costed_flag
421 decode(upper(b.tax_type), 'EXCISE', 'N', 'OTHER EXCISE', 'N', 'ADDL. EXCISE', 'N'
422 , 'CVD', 'N', 'EXCISE_EDUCATION_CESS', 'N', 'CVD_EDUCATION_CESS', 'N'
423 , 'ADDITIONAL_CVD', 'N'
424 , jai_constants.tax_type_sh_exc_edu_cess, 'N'
425 , jai_constants.tax_type_sh_cvd_edu_cess, 'N'
426 , a.modvat_flag)
427 , a.modvat_flag
428 ),
429 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100))
430 / ln_currency_conv_rate,
431 /* if not INR, then following logic will be applied */
432 a.tax_amount
433 * decode(
434 decode(
435 r_ja_in_deliver_trx.cenvat_costed_flag, 'Y',--Modified by Bo Li for bug9305067 replace the attribute2 with cenvat_costed_flag
436 decode(upper(b.tax_type), 'EXCISE', 'N', 'OTHER EXCISE', 'N', 'ADDL. EXCISE', 'N'
437 , 'CVD', 'N', 'EXCISE_EDUCATION_CESS', 'N', 'CVD_EDUCATION_CESS', 'N'
438 , 'ADDITIONAL_CVD', 'N'
439 , jai_constants.tax_type_sh_exc_edu_cess, 'N'
440 , jai_constants.tax_type_sh_cvd_edu_cess, 'N'
441 , a.modvat_flag)
442 , a.modvat_flag
443 ),
444 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)
445 )
446 )
447 ),0) transaction_tax_amount
448 into ln_func_nonreco_tax_amt, ln_trx_nonreco_tax_amt
449 from JAI_RCV_LINE_TAXES a, JAI_CMN_TAXES_ALL b
450 where a.tax_id = b.tax_id
451 and (
452 -- ( pn_transaction_dist_id is not null and a.transaction_id = r_ja_in_deliver_trx.tax_transaction_id )
453 ( pn_transaction_dist_id is not null and a.shipment_line_id = r_ja_in_deliver_trx.shipment_line_id )
454 or ( pn_transaction_dist_id is null and a.shipment_header_id = pn_transaction_header_id)
455 );
456
457
458 -- Added by Jia Li for Tax inclusive Computations on 2007/11/27, Begin
459 -- TD16-Changed Project Costing
460 -- Get inclusive tax amount
461 -----------------------------------------------------------------------
462 SELECT
463 /* functional inclusive tax amount calc */
464 NVL(
465 SUM(
466 DECODE(NVL(a.currency, 'INR'), 'INR', a.tax_amount ,
467 a.tax_amount * ln_currency_conv_rate)
468 ), 0) functional_tax_amount ,
469 /* transaction inclusive tax amount calc */
470 NVL(
471 SUM(
472 DECODE(NVL(a.currency, 'INR'), 'INR', a.tax_amount / ln_currency_conv_rate,
473 a.tax_amount)
474 ), 0) transaction_tax_amount
475 INTO
476 ln_func_inclu_reco_tax_amt
477 , ln_trx_inclu_reco_tax_amt
478 FROM
479 jai_rcv_line_taxes a
480 , jai_cmn_taxes_all b
481 WHERE a.tax_id = b.tax_id
482 AND NVL(b.inclusive_tax_flag, 'N') = 'Y'
483 AND ( ( pn_transaction_dist_id IS NOT NULL
484 -- AND a.transaction_id = r_ja_in_deliver_trx.tax_transaction_id )
485 AND a.shipment_line_id = r_ja_in_deliver_trx.shipment_line_id )
486 OR
487 ( pn_transaction_dist_id IS NULL
488 AND a.shipment_header_id = pn_transaction_header_id)
489 );
490 ---------------------------------------------------------------------------------------
491 -- Added by Jia Li for Tax inclusive Computations on 2007/11/27, End
492
493
494 open c_ja_in_rcv_trx(r_ja_in_deliver_trx.tax_transaction_id);
495 fetch c_ja_in_rcv_trx into r_ja_in_receive_trx;
496 close c_ja_in_rcv_trx;
497
498 /* Cases to be taken care
499 1. Non bonded delivery -- Implemented in the SELECT query for amounts itself using attribute2 of DELIVER transaction
500 2. UOM Conversion
501 3. Quantity change between receive and deliver -- Implemented with below apportion code
502 */
503
504 -- Bug# 6321215
505 -- Changed pn_transaction_qty to r_ja_in_receive_trx.quantity. This is done because in R12 PA populates amount
506 -- for quantity column if expenditure_type is not rate enabled. Hence refering to DELIVER quantity to determine the
507 -- apportion factor
508 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'r_ja_in_deliver_trx.quantity='||r_ja_in_deliver_trx.quantity ||
509 ', r_ja_in_receive_trx.quantity='||r_ja_in_receive_trx.quantity ||
510 ', pn_transaction_qty =' || pn_transaction_qty
511 );
512
513
514 if r_ja_in_deliver_trx.quantity is not null
515 and r_ja_in_deliver_trx.quantity <> 0
516 and r_ja_in_receive_trx.quantity is not null
517 and r_ja_in_receive_trx.quantity <> 0
518 and r_ja_in_receive_trx.quantity <> r_ja_in_deliver_trx.quantity
519 then
520 ln_apportion_factor := r_ja_in_deliver_trx.quantity / r_ja_in_receive_trx.quantity;
521 else
522 ln_apportion_factor := 1;
523 end if;
524 -- End Bug 6321215
525
526 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'ln_apportion_factor='||ln_apportion_factor);
527
528 ln_func_nonreco_tax_amt := ln_func_nonreco_tax_amt * ln_apportion_factor;
529 ln_trx_nonreco_tax_amt := ln_trx_nonreco_tax_amt * ln_apportion_factor;
530
531 ln_func_inclu_reco_tax_amt := ln_func_inclu_reco_tax_amt * ln_apportion_factor; -- Added by Jia Li for Tax inclusive Computations on 2007/11/27
532 ln_trx_inclu_reco_tax_amt := ln_trx_inclu_reco_tax_amt * ln_apportion_factor; -- Added by Jia Li for Tax inclusive Computations on 2007/11/27
533
534 /*START, Bgowrava for Bug#6452772*/
535 IF r_ja_in_deliver_trx.transaction_type = lv_src_type_rtr THEN
536 ln_func_nonreco_tax_amt := ln_func_nonreco_tax_amt*-1;
537 ln_trx_nonreco_tax_amt := ln_trx_nonreco_tax_amt*-1;
538
539 ln_func_inclu_reco_tax_amt := ln_func_inclu_reco_tax_amt * -1; -- Added by Jia Li for Tax inclusive Computations on 2007/11/27
540 ln_trx_inclu_reco_tax_amt := ln_trx_inclu_reco_tax_amt * -1; -- Added by Jia Li for Tax inclusive Computations on 2007/11/27
541
542 END IF;
543 /*END, Bgowrava for Bug#6452772*/
544
545 end if;
546
547 /* 4 - PAYABLE INVOICES */
548 elsif pv_transaction_source = JAI_PA_COSTING_PKG.gv_src_oracle_payables
549 and pv_line_type = JAI_PA_COSTING_PKG.gv_line_type_invoice
550 then
551
552 select
553 nvl(sum(a.base_amount),0) functional_tax_amount,
554 nvl(sum(a.tax_amount),0) transaction_tax_amount
555 into ln_func_nonreco_tax_amt, ln_trx_nonreco_tax_amt
556 from JAI_AP_MATCH_INV_TAXES a, JAI_CMN_TAXES_ALL b
557 where a.tax_id = b.tax_id
558 and nvl(b.mod_cr_percentage, 0) = 0
559 and (a.invoice_id, a.parent_invoice_distribution_id) =
560 ( select invoice_id, invoice_distribution_id from ap_invoice_distributions_all
561 where invoice_id = pn_transaction_header_id and distribution_line_number = pn_transaction_dist_id);
562
563 -- Added by Jia Li for Tax inclusive Computations on 2007/11/27, Begin
564 -- TD16-Changed Project Costing
565 -- Get inclusive recoverable tax amount
566 -----------------------------------------------------------------------
567 SELECT
568 NVL(SUM(a.base_amount),0) functional_tax_amount,
569 NVL(SUM(a.tax_amount),0) transaction_tax_amount
570 INTO
571 ln_func_inclu_reco_tax_amt
572 , ln_trx_inclu_reco_tax_amt
573 FROM
574 jai_ap_match_inv_taxes a
575 , jai_cmn_taxes_all b
576 WHERE a.tax_id = b.tax_id
577 AND NVL(b.inclusive_tax_flag, 'N') = 'Y'
578 AND a.recoverable_flag = 'Y'
579 AND ( a.invoice_id, a.parent_invoice_distribution_id) =
580 ( SELECT
581 invoice_id
582 , invoice_distribution_id
583 FROM
584 ap_invoice_distributions_all
585 WHERE invoice_id = pn_transaction_header_id
586 AND distribution_line_number = pn_transaction_dist_id);
587 ---------------------------------------------------------------------------------------
588 -- Added by Jia Li for Tax inclusive Computations on 2007/11/27, End
589
590 end if;
591
592 -- Added by Jia Li for Tax inclusive Computations on 2007/11/27, Begin
593 ------------------------------------------------------------------------
594 ln_func_nonreco_tax_amt := ln_func_nonreco_tax_amt - ln_func_inclu_reco_tax_amt;
595 ln_trx_nonreco_tax_amt := ln_trx_nonreco_tax_amt - ln_trx_inclu_reco_tax_amt;
596 ------------------------------------------------------------------------------
597 -- Added by Jia Li for Tax inclusive Computations on 2007/11/27, End
598 gn_func_amount := ln_func_nonreco_tax_amt ;
599 gn_trx_amount := ln_trx_nonreco_tax_amt ;
600
601 <<return_amount>>
602
603 if pv_currency_of_return_tax_amt is null
604 or pv_currency_of_return_tax_amt = JAI_PA_COSTING_PKG.gv_transaction_currency
605 then
606 return (ln_trx_nonreco_tax_amt);
607 else
608 return (ln_func_nonreco_tax_amt);
609 end if;
610
611 end get_nonrec_tax_amount;
612
613
614 /*------------------------------------------------------------------------------------------------------------*/
615 procedure pre_process
616 ( p_transaction_source in varchar2,
617 p_batch in varchar2,
618 p_xface_id in number,
619 p_user_id in number
620 )
621 is
622 lv_process_flag varchar2 (2);
623 lv_process_message varchar2 (2000);
624 ln_reg_id number;
625
626 begin <<pre_process>>
627 lv_member_name := 'PRE_PROCESS';
628 set_debug_context;
629 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
630 , pn_reg_id => ln_reg_id
631 );
632
633 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Call Parameters:' || fnd_global.local_chr(10) ||
634 'p_transaction_source='|| p_transaction_source ||fnd_global.local_chr(10) ||
635 'p_batch ='|| p_batch ||fnd_global.local_chr(10) ||
636 'p_xface_id ='|| p_xface_id ||fnd_global.local_chr(10) ||
637 'p_user_id ='|| p_user_id
638 );
639
640 if p_transaction_source not in (GV_TRX_SRC_PO_RECEIPT)
641 -- This source need not be implemented as it is for retroactive pricing functionality, GV_TRX_SRC_PO_RCPT_PRICE_ADJ)
642 then
643 return;
644 end if;
645
646 -- delegate call to update_interface_costs
647 update_interface_costs ( p_transaction_source => p_transaction_source
648 , p_batch => p_batch
649 , p_xface_id => p_xface_id
650 , p_process_flag => lv_process_flag
651 , p_process_message => lv_process_message
652 );
653 if lv_process_flag <> jai_constants.SUCCESSFUL then
654 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'lv_process_flag='||lv_process_flag ||',Message='||lv_process_message);
655 return;
656 end if;
657 /** Deregister procedure and return*/
658 <<deregister_and_return>>
659 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
660
661 exception
662 when others then
663 jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
664 jai_cmn_debug_contexts_pkg.print_stack;
665
666 end pre_process;
667
668
669 -- Added by Jia for FP Bug#9863751, Begin
670 -- Post Processing Client Extension to update pa_addition_flag in jai_pa_tax_trxs to 'Y' if processed
671 ----------------------------------------------------------------------------------------------------------------
672 procedure post_process
673 ( p_transaction_source in varchar2,
674 p_batch in varchar2,
675 p_xface_id in number,
676 p_user_id in number
677 )
678 is
679
680 cursor c_pa_trx_xface_rec_status is
681 select txn_interface_id, transaction_status_code, expenditure_item_id, cdl_system_reference4, attribute9
682 from pa_transaction_interface_all
683 where transaction_source = p_transaction_source
684 and transaction_status_code in ('I', 'A')
685 and interface_id = p_xface_id;
686
687 cursor c_pa_trx_xface_receipt is
688 select txn_interface_id, transaction_status_code, expenditure_item_id, cdl_system_reference4, attribute9
689 from pa_transaction_interface_all
690 where transaction_source = p_transaction_source
691 and transaction_status_code in ('I', 'A')
692 and interface_id = p_xface_id
693 and (attribute10 <> 'INDIA LOCALIZATION' or attribute10 is NULL);
694
695 cursor c_upd_req_id is
696 select *
697 from jai_pa_tax_trxs
698 where interface_id = p_xface_id
699 and transaction_source = p_transaction_source
700 for update of request_id;
701
702 lv_process_flag varchar2 (2);
703 lv_process_message varchar2 (2000);
704 ln_reg_id number;
705
706 r_pa_trx_xface_rec_status c_pa_trx_xface_rec_status%rowtype;
707 r_pa_trx_xface_receipt c_pa_trx_xface_receipt%rowtype;
708 r_upd_req_id c_upd_req_id%rowtype;
709
710 begin
711
712 lv_member_name := 'POST_PROCESS';
713 set_debug_context;
714 jai_cmn_debug_contexts_pkg.register ( pv_context => lv_context
715 , pn_reg_id => ln_reg_id
716 );
717
718 FND_FILE.put_line(FND_FILE.log, 'Post Process Parameters: ' || p_transaction_source || ':' || p_batch || ':' || p_xface_id);
719
720 jai_cmn_debug_contexts_pkg.print (ln_reg_id, 'Call Parameters:' ||
721 ' p_transaction_source='|| p_transaction_source ||
722 ' p_batch ='|| p_batch ||
723 ' p_xface_id ='|| p_xface_id ||
724 ' p_user_id ='|| p_user_id
725 );
726
727 for r_pa_trx_xface_rec_status in c_pa_trx_xface_rec_status
728 loop
729
730 update jai_pa_tax_trxs
731 set pa_addition_flag = 'Y',
732 expenditure_item_id = r_pa_trx_xface_rec_status.expenditure_item_id
733 where cdl_system_reference4 = r_pa_trx_xface_rec_status.cdl_system_reference4
734 and attribute9 = r_pa_trx_xface_rec_status.attribute9
735 and attribute10 = 'INDIA LOCALIZATION';
736
737 end loop; /*for r_pa_trx_xface_rec_status in c_pa_trx_xface_rec_status*/
738
739 for r_pa_trx_xface_rec_status in c_pa_trx_xface_receipt
740 loop
741
742 FND_FILE.put_line(FND_FILE.log, 'Deleting records with transaction_status_code R');
743
744 delete from pa_transaction_interface_all
745 where attribute10 = 'INDIA LOCALIZATION'
746 and transaction_status_code not in ('I', 'A')
747 and cdl_system_reference4 = r_pa_trx_xface_rec_status.cdl_system_reference4;
748
749 end loop; /*for r_pa_trx_xface_rec_status in c_pa_trx_xface_receipt*/
750
751 for r_upd_req_id in c_upd_req_id
752 loop
753
754 FND_FILE.put_line(FND_FILE.log, 'Updating Request ID: ' || fnd_global.conc_request_id());
755
756 update jai_pa_tax_trxs
757 set request_id = fnd_global.conc_request_id()
758 where current of c_upd_req_id;
759 end loop; /*for r_upd_req_id in c_upd_req_id*/
760
761 update pa_transaction_interface_all
762 set transaction_status_code = 'A'
763 where transaction_source = p_transaction_source
764 and transaction_status_code = 'I'
765 and interface_id = p_xface_id;
766
767 /** Deregister procedure and return*/
768 <<deregister_and_return>>
769 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
770
771 exception
772 when others then
773 jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
774 jai_cmn_debug_contexts_pkg.print_stack;
775
776 end post_process;
777 ----------------------------------------------------------------------------------------------------------------
778 -- Added by Jia for FP Bug#9863751, End
779
780
781
782 /*------------------------------------------------------------------------------------------------------------*/
783
784 procedure update_interface_costs
785 ( p_transaction_source in varchar2
786 , p_batch in varchar2
787 , p_xface_id in varchar2
788 , p_process_flag out nocopy varchar2
789 , p_process_message out nocopy varchar2
790 )
791 is
792
793 cursor c_pa_trx_xface_records
794 is
795 select *
796 from pa_transaction_interface_all
797 where transaction_source = p_transaction_source
798 and batch_name = p_batch
799 and transaction_status_code = 'P'
800 and interface_id = p_xface_id;
801
802 /*Bug 8400140 - Start*/
803
804 Cursor c_get_non_rec_taxes ( cp_tax_transaction_id IN JAI_RCV_LINE_TAXES.transaction_id%type,
805 cp_attribute2 IN VARCHAR2,
806 cp_curr_conv_rate In NUMBER)
807 IS
808 select
809 a.tax_id , -- Added by Jia for FP Bug#9863751, fetched Tax ID to uniquely identify a line in jai_pa_tax_trxs
810 a.vendor_id , /*Added by nprashar for bug # 8691525*/
811 /* functional tax amount calc */
812 nvl(
813 decode(nvl(a.currency, 'INR'), 'INR',
814 a.tax_amount
815 * decode(
816 decode(
817 cp_attribute2, 'Y',
818 decode(upper(b.tax_type), 'EXCISE', 'N', 'OTHER EXCISE', 'N', 'ADDL. EXCISE', 'N'
819 , 'CVD', 'N', 'EXCISE_EDUCATION_CESS', 'N', 'CVD_EDUCATION_CESS', 'N'
820 , 'ADDITIONAL_CVD', 'N'
821 , jai_constants.tax_type_sh_exc_edu_cess, 'N'
822 , jai_constants.tax_type_sh_cvd_edu_cess, 'N'
823 , a.modvat_flag)
824 , a.modvat_flag
825 ),
826 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)),
827 /* if not INR, then following logic will be applied */
828 a.tax_amount
829 * decode(
830 decode(
831 cp_attribute2, 'Y',
832 decode(upper(b.tax_type), 'EXCISE', 'N', 'OTHER EXCISE', 'N', 'ADDL. EXCISE', 'N'
833 , 'CVD', 'N', 'EXCISE_EDUCATION_CESS', 'N', 'CVD_EDUCATION_CESS', 'N'
834 , 'ADDITIONAL_CVD', 'N'
835 , jai_constants.tax_type_sh_exc_edu_cess, 'N'
836 , jai_constants.tax_type_sh_cvd_edu_cess, 'N'
837 , a.modvat_flag)
838 , a.modvat_flag
839 ),
840 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)
841 )
842 * cp_curr_conv_rate
843 ),0) functional_tax_amount ,
844 /* transaction tax amount calc */
845 nvl(
846 decode(nvl(a.currency, 'INR'), 'INR',
847 a.tax_amount
848 * decode(
849 decode(
850 cp_attribute2, 'Y',
851 decode(upper(b.tax_type), 'EXCISE', 'N', 'OTHER EXCISE', 'N', 'ADDL. EXCISE', 'N'
852 , 'CVD', 'N', 'EXCISE_EDUCATION_CESS', 'N', 'CVD_EDUCATION_CESS', 'N'
853 , 'ADDITIONAL_CVD', 'N'
854 , jai_constants.tax_type_sh_exc_edu_cess, 'N'
855 , jai_constants.tax_type_sh_cvd_edu_cess, 'N'
856 , a.modvat_flag)
857 , a.modvat_flag
858 ),
859 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100))
860 / cp_curr_conv_rate,
861 /* if not INR, then following logic will be applied */
862 a.tax_amount
863 * decode(
864 decode(
865 cp_attribute2, 'Y',
866 decode(upper(b.tax_type), 'EXCISE', 'N', 'OTHER EXCISE', 'N', 'ADDL. EXCISE', 'N'
867 , 'CVD', 'N', 'EXCISE_EDUCATION_CESS', 'N', 'CVD_EDUCATION_CESS', 'N'
868 , 'ADDITIONAL_CVD', 'N'
869 , jai_constants.tax_type_sh_exc_edu_cess, 'N'
870 , jai_constants.tax_type_sh_cvd_edu_cess, 'N'
871 , a.modvat_flag)
872 , a.modvat_flag
873 ),
874 'N', 1,(1- nvl(b.mod_cr_percentage,0)/100)
875 )
876 ),0) transaction_tax_amount
877 from JAI_RCV_LINE_TAXES a, JAI_CMN_TAXES_ALL b
878 where a.tax_id = b.tax_id
879 and a.transaction_id = cp_tax_transaction_id;
880
881 cursor c_ja_in_rcv_trx(cp_transaction_id in number) is
882 select *
883 from jai_rcv_transactions
884 where transaction_id = cp_transaction_id;
885 r_ja_in_receive_trx c_ja_in_rcv_trx%rowtype;
886 r_ja_in_deliver_trx c_ja_in_rcv_trx%rowtype;
887 ln_apportion_factor number;
888
889 /*Bug 8400140 - End*/
890
891 -- Added by Jia for FP Bug#9863751, Begin
892 ----------------------------------------------------------------------------------------------------------------
893 cursor c_jai_pa_tax_trxs is
894 select *
895 from jai_pa_tax_trxs jait
896 where pa_addition_flag = 'N'
897 and expenditure_item_id is NULL
898 and batch_name = nvl(p_batch, batch_name)
899 and (request_id <> FND_GLOBAL.CONC_REQUEST_ID() or request_id is NULL)
900 and not exists
901 ( select 1
902 from pa_transaction_interface_all pti
903 where pti.transaction_source = jait.transaction_source
904 and pti.cdl_system_reference4 = jait.cdl_system_reference4
905 and pti.attribute9 = jait.attribute9
906 and pti.transaction_status_code = 'P');
907
908 cursor c_check_if_tax_exists(p_transaction_id in number, p_tax_id in number) is
909 select cdl_system_reference4, attribute9
910 from jai_pa_tax_trxs
911 where cdl_system_reference4 = p_transaction_id
912 and attribute9 = p_tax_id;
913
914 r_check_if_tax_exists c_check_if_tax_exists%rowtype;
915 ln_txn_interface_id number;
916 ----------------------------------------------------------------------------------------------------------------
917 -- Added by Jia for FP Bug#9863751, End
918
919 ln_reg_id number;
920 ln_apportioned_txn_tax_amt number;
921 ln_apportioned_func_tax_amt number;
922
923 begin <<update_interface_costs>>
924
925 lv_member_name := 'UPDATE_INTERFACE_COSTS';
926 set_debug_context;
927
928 jai_cmn_debug_contexts_pkg.register (lv_context, ln_reg_id);
929 for r_po_rcpt in c_pa_trx_xface_records
930 loop
931
932 ln_apportioned_func_tax_amt := 0;
933 ln_apportioned_txn_tax_amt := 0;
934
935 /*Commented for Bug 8400140*/
936 /*
937 jai_cmn_debug_contexts_pkg.print
938 ( ln_reg_id
939 , 'r_po_rcpt.cdl_system_reference4 (rcv_transaction_id)='||r_po_rcpt.cdl_system_reference4 || fnd_global.local_chr(10) ||
940 'r_po_rcpt.unit_of_measure='||r_po_rcpt.unit_of_measure|| fnd_global.local_chr(10) ||
941 'r_po_rcpt.quantity='||r_po_rcpt.quantity || fnd_global.local_chr(10) ||
942 'r_po_rcpt.txn_interface_id='||r_po_rcpt.txn_interface_id
943 );
944 ln_apportioned_txn_tax_amt := JAI_PA_COSTING_PKG.get_nonrec_tax_amount
945 ( pv_transaction_source => JAI_PA_COSTING_PKG.GV_SRC_ORACLE_PURCHASING
946 , pv_line_type => JAI_PA_COSTING_PKG.GV_LINE_TYPE_PO_RECEIPT
947 , pn_transaction_header_id => null -- ''
948 , pn_transaction_dist_id => r_po_rcpt.cdl_system_reference4 --> rcv_transaction_id
949 , pv_currency_of_return_tax_amt => JAI_PA_COSTING_PKG.gv_transaction_currency
950 , pv_transaction_uom => r_po_rcpt.unit_of_measure
951 , pn_transaction_qty => r_po_rcpt.quantity
952 , pn_currency_conv_rate => r_po_rcpt.receipt_exchange_rate
953 );
954
955 ln_apportioned_func_tax_amt := JAI_PA_COSTING_PKG.get_nonrec_tax_amount
956 ( pv_transaction_source => JAI_PA_COSTING_PKG.GV_SRC_ORACLE_PURCHASING
957 , pv_line_type => JAI_PA_COSTING_PKG.GV_LINE_TYPE_PO_RECEIPT
958 , pn_transaction_header_id => null -- ''
959 , pn_transaction_dist_id => r_po_rcpt.cdl_system_reference4 --> rcv_transaction_id
960 , pv_currency_of_return_tax_amt => JAI_PA_COSTING_PKG.gv_functional_currency
961 , pv_transaction_uom => r_po_rcpt.unit_of_measure -- this value is not being populated by costing. so we need to fetch from JAI_RCV_TRANSACTIONS
962 , pn_transaction_qty => r_po_rcpt.quantity
963 , pn_currency_conv_rate => r_po_rcpt.receipt_exchange_rate
964 );
965
966 jai_cmn_debug_contexts_pkg.print
967 ( ln_reg_id
968 , 'ln_apportioned_txn_tax_amt='||ln_apportioned_txn_tax_amt
969 ||', ln_apportioned_func_tax_amt='||ln_apportioned_func_tax_amt
970 );
971
972 jai_cmn_debug_contexts_pkg.print
973 ( ln_reg_id
974 ,'Before update pa_transaction_interface_all'
975 );
976 */
977 /*
978 update pa_transaction_interface_all
979 set raw_cost = raw_cost + nvl(ln_apportioned_tax_amt,0)
980 -- commented after talking to PROJECTs DEV team
981 -- ,receipt_currency_amount = receipt_currency_amount + nvl(ln_apportioned_tax_amt,0)
982 , denom_raw_cost = denom_raw_cost + nvl(ln_apportioned_tax_amt,0)
983 , acct_raw_cost = acct_raw_cost + nvl(ln_apportioned_tax_amt,0)
984 where txn_interface_id = r_po_rcpt.txn_interface_id ;
985 */
986
987 -- Commented the above code and added the following for Bug 8400140
988
989 open c_ja_in_rcv_trx(r_po_rcpt.cdl_system_reference4);
990 fetch c_ja_in_rcv_trx into r_ja_in_deliver_trx;
991 close c_ja_in_rcv_trx;
992
993 FOR rec_get_non_rec_taxes IN c_get_non_rec_taxes(r_ja_in_deliver_trx.tax_transaction_id,
994 r_ja_in_deliver_trx.cenvat_costed_flag,--Modified by Bo Li for bug9305067 replace the attribute2 with cenvat_costed_flag
995 nvl(r_po_rcpt.receipt_exchange_rate,1))
996 LOOP
997
998 -- Added by Jia for FP Bug#9863751, Begin
999 -- Check if the Tax lines already exist in JAI_PA_TAX_TRXS
1000 ------------------------------------------------------------------------------------
1001 open c_check_if_tax_exists (r_po_rcpt.cdl_system_reference4, rec_get_non_rec_taxes.tax_id);
1002 fetch c_check_if_tax_exists into r_check_if_tax_exists;
1003 FND_FILE.put_line(FND_FILE.log, 'r_check_if_tax_exists.tax_exist: ' || r_check_if_tax_exists.cdl_system_reference4 || ':'
1004 || r_check_if_tax_exists.attribute9);
1005 exit when c_check_if_tax_exists%FOUND;
1006 close c_check_if_tax_exists;
1007 ------------------------------------------------------------------------------------
1008 -- Added by Jia for FP Bug#9863751, End
1009
1010 open c_ja_in_rcv_trx(r_ja_in_deliver_trx.tax_transaction_id);
1011 fetch c_ja_in_rcv_trx into r_ja_in_receive_trx;
1012 close c_ja_in_rcv_trx;
1013
1014 /* Cases to be taken care
1015 1. Non bonded delivery -- Implemented in the SELECT query for amounts itself using attribute2 of DELIVER transaction
1016 2. UOM Conversion
1017 3. Quantity change between receive and deliver -- Implemented with below apportion code
1018 */
1019 if r_po_rcpt.quantity is not null
1020 and r_po_rcpt.quantity <> 0
1021 and r_ja_in_receive_trx.quantity is not null
1022 and r_ja_in_receive_trx.quantity <> 0
1023 and r_ja_in_receive_trx.quantity <> r_po_rcpt.quantity
1024 then
1025 /*Bug 8660365 - Replaced r_po_rcpt.quantity with r_ja_in_deliver_trx.quantity as Projects update amounts in Quantity in R12*/
1026 ln_apportion_factor := r_ja_in_deliver_trx.quantity / r_ja_in_receive_trx.quantity;
1027 else
1028 ln_apportion_factor := 1;
1029 end if;
1030
1031 ln_apportioned_func_tax_amt := rec_get_non_rec_taxes.functional_tax_amount * ln_apportion_factor;
1032 ln_apportioned_txn_tax_amt := rec_get_non_rec_taxes.transaction_tax_amount * ln_apportion_factor;
1033
1034 /*Bug 14026359 - Tax Amount shall be negative if RTV is done - Start*/
1035
1036 IF r_ja_in_deliver_trx.transaction_type = 'RETURN TO RECEIVING' THEN
1037 ln_apportioned_func_tax_amt := ln_apportioned_func_tax_amt * -1;
1038 ln_apportioned_txn_tax_amt := ln_apportioned_txn_tax_amt * -1;
1039 END IF;
1040
1041 /*Bug 14026359 - End*/
1042
1043 IF nvl(ln_apportioned_txn_tax_amt,0) <> 0 THEN
1044
1045 -- Bug 8400140 - End
1046
1047 -- Commented by Jia for FP Bug#9863751, Begin
1048 ----------------------------------------------------------------------------------------
1049 /*
1050 INSERT INTO pa_transaction_interface_all(
1051 TRANSACTION_SOURCE ,
1052 BATCH_NAME ,
1053 EXPENDITURE_ENDING_DATE ,
1054 EMPLOYEE_NUMBER ,
1055 ORGANIZATION_NAME ,
1056 EXPENDITURE_ITEM_DATE ,
1057 PROJECT_NUMBER ,
1058 TASK_NUMBER ,
1059 EXPENDITURE_TYPE ,
1060 NON_LABOR_RESOURCE ,
1061 NON_LABOR_RESOURCE_ORG_NAME ,
1062 QUANTITY ,
1063 RAW_COST ,
1064 EXPENDITURE_COMMENT ,
1065 TRANSACTION_STATUS_CODE ,
1066 TRANSACTION_REJECTION_CODE ,
1067 EXPENDITURE_ID ,
1068 ORIG_TRANSACTION_REFERENCE ,
1069 ATTRIBUTE_CATEGORY ,
1070 ATTRIBUTE1 ,
1071 ATTRIBUTE2 ,
1072 ATTRIBUTE3 ,
1073 ATTRIBUTE4 ,
1074 ATTRIBUTE5 ,
1075 ATTRIBUTE6 ,
1076 ATTRIBUTE7 ,
1077 ATTRIBUTE8 ,
1078 ATTRIBUTE9 ,
1079 ATTRIBUTE10 ,
1080 RAW_COST_RATE ,
1081 INTERFACE_ID ,
1082 UNMATCHED_NEGATIVE_TXN_FLAG ,
1083 EXPENDITURE_ITEM_ID ,
1084 ORG_ID ,
1085 DR_CODE_COMBINATION_ID ,
1086 CR_CODE_COMBINATION_ID ,
1087 CDL_SYSTEM_REFERENCE1 ,
1088 CDL_SYSTEM_REFERENCE2 ,
1089 CDL_SYSTEM_REFERENCE3 ,
1090 GL_DATE ,
1091 BURDENED_COST ,
1092 BURDENED_COST_RATE ,
1093 SYSTEM_LINKAGE ,
1094 TXN_INTERFACE_ID ,
1095 USER_TRANSACTION_SOURCE ,
1096 CREATED_BY ,
1097 CREATION_DATE ,
1098 LAST_UPDATED_BY ,
1099 LAST_UPDATE_DATE ,
1100 RECEIPT_CURRENCY_AMOUNT ,
1101 RECEIPT_CURRENCY_CODE ,
1102 RECEIPT_EXCHANGE_RATE ,
1103 DENOM_CURRENCY_CODE ,
1104 DENOM_RAW_COST ,
1105 DENOM_BURDENED_COST ,
1106 ACCT_RATE_DATE ,
1107 ACCT_RATE_TYPE ,
1108 ACCT_EXCHANGE_RATE ,
1109 ACCT_RAW_COST ,
1110 ACCT_BURDENED_COST ,
1111 ACCT_EXCHANGE_ROUNDING_LIMIT ,
1112 PROJECT_CURRENCY_CODE ,
1113 PROJECT_RATE_DATE ,
1114 PROJECT_RATE_TYPE ,
1115 PROJECT_EXCHANGE_RATE ,
1116 ORIG_EXP_TXN_REFERENCE1 ,
1117 ORIG_EXP_TXN_REFERENCE2 ,
1118 ORIG_EXP_TXN_REFERENCE3 ,
1119 ORIG_USER_EXP_TXN_REFERENCE ,
1120 VENDOR_NUMBER ,
1121 OVERRIDE_TO_ORGANIZATION_NAME,
1122 REVERSED_ORIG_TXN_REFERENCE ,
1123 BILLABLE_FLAG ,
1124 PERSON_BUSINESS_GROUP_NAME ,
1125 PROJFUNC_CURRENCY_CODE ,
1126 PROJFUNC_COST_RATE_TYPE ,
1127 PROJFUNC_COST_RATE_DATE ,
1128 PROJFUNC_COST_EXCHANGE_RATE ,
1129 PROJECT_RAW_COST ,
1130 PROJECT_BURDENED_COST ,
1131 ASSIGNMENT_NAME ,
1132 WORK_TYPE_NAME ,
1133 CDL_SYSTEM_REFERENCE4 ,
1134 ACCRUAL_FLAG ,
1135 PROJECT_ID ,
1136 TASK_ID ,
1137 PERSON_ID ,
1138 ORGANIZATION_ID ,
1139 NON_LABOR_RESOURCE_ORG_ID ,
1140 VENDOR_ID ,
1141 OVERRIDE_TO_ORGANIZATION_ID ,
1142 ASSIGNMENT_ID ,
1143 WORK_TYPE_ID ,
1144 PERSON_BUSINESS_GROUP_ID ,
1145 INVENTORY_ITEM_ID ,
1146 WIP_RESOURCE_ID ,
1147 UNIT_OF_MEASURE
1148 ) VALUES (
1149 r_po_rcpt.TRANSACTION_SOURCE , -- 'PO RECEIPT NRTAX', --
1150 r_po_rcpt.BATCH_NAME ,
1151 r_po_rcpt.EXPENDITURE_ENDING_DATE ,
1152 r_po_rcpt.EMPLOYEE_NUMBER ,
1153 r_po_rcpt.ORGANIZATION_NAME ,
1154 r_po_rcpt.EXPENDITURE_ITEM_DATE ,
1155 r_po_rcpt.PROJECT_NUMBER ,
1156 r_po_rcpt.TASK_NUMBER ,
1157 r_po_rcpt.EXPENDITURE_TYPE ,
1158 r_po_rcpt.NON_LABOR_RESOURCE ,
1159 r_po_rcpt.NON_LABOR_RESOURCE_ORG_NAME ,
1160 0 , --Bug 8623928 - Quantity must be zero for Tax Lines
1161 decode(r_po_rcpt.RAW_COST, null, null, nvl(ln_apportioned_txn_tax_amt,0)),
1162 r_po_rcpt.EXPENDITURE_COMMENT ,
1163 r_po_rcpt.TRANSACTION_STATUS_CODE ,
1164 r_po_rcpt.TRANSACTION_REJECTION_CODE ,
1165 r_po_rcpt.EXPENDITURE_ID ,
1166 r_po_rcpt.ORIG_TRANSACTION_REFERENCE ,
1167 r_po_rcpt.ATTRIBUTE_CATEGORY ,
1168 r_po_rcpt.ATTRIBUTE1 ,
1169 r_po_rcpt.ATTRIBUTE2 ,
1170 r_po_rcpt.ATTRIBUTE3 ,
1171 r_po_rcpt.ATTRIBUTE4 ,
1172 r_po_rcpt.ATTRIBUTE5 ,
1173 r_po_rcpt.ATTRIBUTE6 ,
1174 r_po_rcpt.ATTRIBUTE7 ,
1175 r_po_rcpt.ATTRIBUTE8 ,
1176 r_po_rcpt.ATTRIBUTE9 ,
1177 'INDIA LOCALIZATION' , -- r_po_rcpt.ATTRIBUTE10 ,
1178 r_po_rcpt.RAW_COST_RATE ,
1179 r_po_rcpt.INTERFACE_ID ,
1180 r_po_rcpt.UNMATCHED_NEGATIVE_TXN_FLAG ,
1181 r_po_rcpt.EXPENDITURE_ITEM_ID ,
1182 r_po_rcpt.ORG_ID ,
1183 r_po_rcpt.DR_CODE_COMBINATION_ID ,
1184 r_po_rcpt.CR_CODE_COMBINATION_ID ,
1185 rec_get_non_rec_taxes.vendor_id , --Added by nprashar for bug # 8563187, replaced this value r_po_rcpt.CDL_SYSTEM_REFERENCE1 by Vendor_id
1186 r_po_rcpt.CDL_SYSTEM_REFERENCE2 ,
1187 r_po_rcpt.CDL_SYSTEM_REFERENCE3 ,
1188 r_po_rcpt.GL_DATE ,
1189 r_po_rcpt.BURDENED_COST ,
1190 r_po_rcpt.BURDENED_COST_RATE ,
1191 r_po_rcpt.SYSTEM_LINKAGE ,
1192 pa_txn_interface_s.nextval ,
1193 r_po_rcpt.USER_TRANSACTION_SOURCE ,
1194 r_po_rcpt.CREATED_BY ,
1195 r_po_rcpt.CREATION_DATE ,
1196 r_po_rcpt.LAST_UPDATED_BY ,
1197 r_po_rcpt.LAST_UPDATE_DATE ,
1198 r_po_rcpt.RECEIPT_CURRENCY_AMOUNT ,
1199 r_po_rcpt.RECEIPT_CURRENCY_CODE ,
1200 r_po_rcpt.RECEIPT_EXCHANGE_RATE ,
1201 r_po_rcpt.DENOM_CURRENCY_CODE ,
1202 decode(r_po_rcpt.DENOM_RAW_COST, null,null, nvl(ln_apportioned_txn_tax_amt,0)),
1203 r_po_rcpt.DENOM_BURDENED_COST ,
1204 r_po_rcpt.ACCT_RATE_DATE ,
1205 r_po_rcpt.ACCT_RATE_TYPE ,
1206 r_po_rcpt.ACCT_EXCHANGE_RATE ,
1207 decode(r_po_rcpt.ACCT_RAW_COST,null, null, nvl(ln_apportioned_func_tax_amt,0)) ,
1208 r_po_rcpt.ACCT_BURDENED_COST ,
1209 r_po_rcpt.ACCT_EXCHANGE_ROUNDING_LIMIT ,
1210 r_po_rcpt.PROJECT_CURRENCY_CODE ,
1211 r_po_rcpt.PROJECT_RATE_DATE ,
1212 r_po_rcpt.PROJECT_RATE_TYPE ,
1213 r_po_rcpt.PROJECT_EXCHANGE_RATE ,
1214 r_po_rcpt.ORIG_EXP_TXN_REFERENCE1 ,
1215 r_po_rcpt.ORIG_EXP_TXN_REFERENCE2 ,
1216 r_po_rcpt.ORIG_EXP_TXN_REFERENCE3 ,
1217 r_po_rcpt.ORIG_USER_EXP_TXN_REFERENCE ,
1218 r_po_rcpt.VENDOR_NUMBER ,
1219 r_po_rcpt.OVERRIDE_TO_ORGANIZATION_NAME,
1220 r_po_rcpt.REVERSED_ORIG_TXN_REFERENCE ,
1221 r_po_rcpt.BILLABLE_FLAG ,
1222 r_po_rcpt.PERSON_BUSINESS_GROUP_NAME ,
1223 r_po_rcpt.PROJFUNC_CURRENCY_CODE ,
1224 r_po_rcpt.PROJFUNC_COST_RATE_TYPE ,
1225 r_po_rcpt.PROJFUNC_COST_RATE_DATE ,
1226 r_po_rcpt.PROJFUNC_COST_EXCHANGE_RATE ,
1227 r_po_rcpt.PROJECT_RAW_COST ,
1228 r_po_rcpt.PROJECT_BURDENED_COST ,
1229 r_po_rcpt.ASSIGNMENT_NAME ,
1230 r_po_rcpt.WORK_TYPE_NAME ,
1231 r_po_rcpt.CDL_SYSTEM_REFERENCE4 ,
1232 r_po_rcpt.ACCRUAL_FLAG ,
1233 r_po_rcpt.PROJECT_ID ,
1234 r_po_rcpt.TASK_ID ,
1235 r_po_rcpt.PERSON_ID ,
1236 r_po_rcpt.ORGANIZATION_ID ,
1237 r_po_rcpt.NON_LABOR_RESOURCE_ORG_ID ,
1238 rec_get_non_rec_taxes.vendor_id , --Added by nprashar for bug # 8563187, Referring vendor id from table ja_in_receipt_tax_lines r_po_rcpt.VENDOR_ID
1239 r_po_rcpt.OVERRIDE_TO_ORGANIZATION_ID ,
1240 r_po_rcpt.ASSIGNMENT_ID ,
1241 r_po_rcpt.WORK_TYPE_ID ,
1242 r_po_rcpt.PERSON_BUSINESS_GROUP_ID ,
1243 r_po_rcpt.INVENTORY_ITEM_ID ,
1244 r_po_rcpt.WIP_RESOURCE_ID ,
1245 r_po_rcpt.UNIT_OF_MEASURE
1246 );
1247
1248 END IF; -- Added for Bug 8400140
1249
1250 jai_cmn_debug_contexts_pkg.print
1251 ( ln_reg_id
1252 , 'Number of rows updated='||sql%rowcount
1253 );
1254
1255 END LOOP; -- Added for Bug 8400140
1256 end loop;
1257 */
1258 ----------------------------------------------------------------------------------------
1259 -- Commented by Jia for FP Bug#9863751, End
1260
1261
1262 -- Added by Jia for FP Bug#9863751, Begin
1263 -- Insert into jai_pa_tax_trxs before inserting into pa_transaction_interface_all
1264 -- This is done to prevent duplicate Tax lines from getting interfaced to Projects
1265 ------------------------------------------------------------------------------------
1266 FND_FILE.put_line(FND_FILE.log, 'Inserting into jai_pa_tax_trxs');
1267
1268 INSERT INTO jai_pa_tax_trxs(
1269 TRANSACTION_SOURCE ,
1270 BATCH_NAME ,
1271 EXPENDITURE_ENDING_DATE ,
1272 EMPLOYEE_NUMBER ,
1273 ORGANIZATION_NAME ,
1274 EXPENDITURE_ITEM_DATE ,
1275 PROJECT_NUMBER ,
1276 TASK_NUMBER ,
1277 EXPENDITURE_TYPE ,
1278 NON_LABOR_RESOURCE ,
1279 NON_LABOR_RESOURCE_ORG_NAME ,
1280 QUANTITY ,
1281 RAW_COST ,
1282 EXPENDITURE_COMMENT ,
1283 TRANSACTION_STATUS_CODE ,
1284 TRANSACTION_REJECTION_CODE ,
1285 EXPENDITURE_ID ,
1286 ORIG_TRANSACTION_REFERENCE ,
1287 ATTRIBUTE_CATEGORY ,
1288 ATTRIBUTE1 ,
1289 ATTRIBUTE2 ,
1290 ATTRIBUTE3 ,
1291 ATTRIBUTE4 ,
1292 ATTRIBUTE5 ,
1293 ATTRIBUTE6 ,
1294 ATTRIBUTE7 ,
1295 ATTRIBUTE8 ,
1296 ATTRIBUTE9 ,
1297 ATTRIBUTE10 ,
1298 RAW_COST_RATE ,
1299 INTERFACE_ID ,
1300 UNMATCHED_NEGATIVE_TXN_FLAG ,
1301 EXPENDITURE_ITEM_ID ,
1302 ORG_ID ,
1303 DR_CODE_COMBINATION_ID ,
1304 CR_CODE_COMBINATION_ID ,
1305 CDL_SYSTEM_REFERENCE1 ,
1306 CDL_SYSTEM_REFERENCE2 ,
1307 CDL_SYSTEM_REFERENCE3 ,
1308 GL_DATE ,
1309 BURDENED_COST ,
1310 BURDENED_COST_RATE ,
1311 SYSTEM_LINKAGE ,
1312 TXN_INTERFACE_ID ,
1313 USER_TRANSACTION_SOURCE ,
1314 CREATED_BY ,
1315 CREATION_DATE ,
1316 LAST_UPDATED_BY ,
1317 LAST_UPDATE_DATE ,
1318 RECEIPT_CURRENCY_AMOUNT ,
1319 RECEIPT_CURRENCY_CODE ,
1320 RECEIPT_EXCHANGE_RATE ,
1321 DENOM_CURRENCY_CODE ,
1322 DENOM_RAW_COST ,
1323 DENOM_BURDENED_COST ,
1324 ACCT_RATE_DATE ,
1325 ACCT_RATE_TYPE ,
1326 ACCT_EXCHANGE_RATE ,
1327 ACCT_RAW_COST ,
1328 ACCT_BURDENED_COST ,
1329 ACCT_EXCHANGE_ROUNDING_LIMIT ,
1330 PROJECT_CURRENCY_CODE ,
1331 PROJECT_RATE_DATE ,
1332 PROJECT_RATE_TYPE ,
1333 PROJECT_EXCHANGE_RATE ,
1334 ORIG_EXP_TXN_REFERENCE1 ,
1335 ORIG_EXP_TXN_REFERENCE2 ,
1336 ORIG_EXP_TXN_REFERENCE3 ,
1337 ORIG_USER_EXP_TXN_REFERENCE ,
1338 VENDOR_NUMBER ,
1339 OVERRIDE_TO_ORGANIZATION_NAME,
1340 REVERSED_ORIG_TXN_REFERENCE ,
1341 BILLABLE_FLAG ,
1342 PERSON_BUSINESS_GROUP_NAME ,
1343 PROJFUNC_CURRENCY_CODE ,
1344 PROJFUNC_COST_RATE_TYPE ,
1345 PROJFUNC_COST_RATE_DATE ,
1346 PROJFUNC_COST_EXCHANGE_RATE ,
1347 PROJECT_RAW_COST ,
1348 PROJECT_BURDENED_COST ,
1349 ASSIGNMENT_NAME ,
1350 WORK_TYPE_NAME ,
1351 CDL_SYSTEM_REFERENCE4 ,
1352 ACCRUAL_FLAG ,
1353 PROJECT_ID ,
1354 TASK_ID ,
1355 PERSON_ID ,
1356 ORGANIZATION_ID ,
1357 NON_LABOR_RESOURCE_ORG_ID ,
1358 VENDOR_ID ,
1359 OVERRIDE_TO_ORGANIZATION_ID ,
1360 ASSIGNMENT_ID ,
1361 WORK_TYPE_ID ,
1362 PERSON_BUSINESS_GROUP_ID ,
1363 INVENTORY_ITEM_ID ,
1364 WIP_RESOURCE_ID ,
1365 UNIT_OF_MEASURE ,
1366 PO_NUMBER ,
1367 PO_HEADER_ID ,
1368 PO_LINE_NUM ,
1369 PO_LINE_ID ,
1370 PERSON_TYPE ,
1371 PO_PRICE_TYPE ,
1372 ADJUSTED_EXPENDITURE_ITEM_ID ,
1373 FC_DOCUMENT_TYPE ,
1374 DOCUMENT_TYPE ,
1375 DOCUMENT_DISTRIBUTION_TYPE ,
1376 SI_ASSETS_ADDITION_FLAG ,
1377 CDL_SYSTEM_REFERENCE5 ,
1378 SC_XFER_CODE ,
1379 ADJUSTED_TXN_INTERFACE_ID ,
1380 NET_ZERO_ADJUSTMENT_FLAG ,
1381 PA_ADDITION_FLAG
1382 ) VALUES (
1383 r_po_rcpt.TRANSACTION_SOURCE , -- 'PO RECEIPT NRTAX', --
1384 r_po_rcpt.BATCH_NAME ,
1385 r_po_rcpt.EXPENDITURE_ENDING_DATE ,
1386 r_po_rcpt.EMPLOYEE_NUMBER ,
1387 r_po_rcpt.ORGANIZATION_NAME ,
1388 r_po_rcpt.EXPENDITURE_ITEM_DATE ,
1389 r_po_rcpt.PROJECT_NUMBER ,
1390 r_po_rcpt.TASK_NUMBER ,
1391 r_po_rcpt.EXPENDITURE_TYPE ,
1392 r_po_rcpt.NON_LABOR_RESOURCE ,
1393 r_po_rcpt.NON_LABOR_RESOURCE_ORG_NAME ,
1394 0 , /*Bug 8623928 - Quantity must be zero for Tax Lines*/
1395 decode(r_po_rcpt.RAW_COST, null, null, nvl(ln_apportioned_txn_tax_amt,0)),
1396 r_po_rcpt.EXPENDITURE_COMMENT ,
1397 r_po_rcpt.TRANSACTION_STATUS_CODE ,
1398 r_po_rcpt.TRANSACTION_REJECTION_CODE ,
1399 r_po_rcpt.EXPENDITURE_ID ,
1400 r_po_rcpt.ORIG_TRANSACTION_REFERENCE ,
1401 r_po_rcpt.ATTRIBUTE_CATEGORY ,
1402 r_po_rcpt.ATTRIBUTE1 ,
1403 r_po_rcpt.ATTRIBUTE2 ,
1404 r_po_rcpt.ATTRIBUTE3 ,
1405 r_po_rcpt.ATTRIBUTE4 ,
1406 r_po_rcpt.ATTRIBUTE5 ,
1407 r_po_rcpt.ATTRIBUTE6 ,
1408 r_po_rcpt.ATTRIBUTE7 ,
1409 r_po_rcpt.ATTRIBUTE8 ,
1410 rec_get_non_rec_taxes.tax_id , /*FP Bug#9863751 - Inserted Tax ID into ATTRIBUTE9 to uniquely identify if a record exists*/
1411 'INDIA LOCALIZATION' ,
1412 r_po_rcpt.RAW_COST_RATE ,
1413 r_po_rcpt.INTERFACE_ID ,
1414 r_po_rcpt.UNMATCHED_NEGATIVE_TXN_FLAG ,
1415 r_po_rcpt.EXPENDITURE_ITEM_ID ,
1416 r_po_rcpt.ORG_ID ,
1417 r_po_rcpt.DR_CODE_COMBINATION_ID ,
1418 r_po_rcpt.CR_CODE_COMBINATION_ID ,
1419 rec_get_non_rec_taxes.vendor_id ,
1420 r_po_rcpt.CDL_SYSTEM_REFERENCE2 ,
1421 r_po_rcpt.CDL_SYSTEM_REFERENCE3 ,
1422 r_po_rcpt.GL_DATE ,
1423 r_po_rcpt.BURDENED_COST ,
1424 r_po_rcpt.BURDENED_COST_RATE ,
1425 r_po_rcpt.SYSTEM_LINKAGE ,
1426 NULL , /*FP Bug#9863751*/
1427 r_po_rcpt.USER_TRANSACTION_SOURCE ,
1428 r_po_rcpt.CREATED_BY ,
1429 r_po_rcpt.CREATION_DATE ,
1430 r_po_rcpt.LAST_UPDATED_BY ,
1431 r_po_rcpt.LAST_UPDATE_DATE ,
1432 r_po_rcpt.RECEIPT_CURRENCY_AMOUNT ,
1433 r_po_rcpt.RECEIPT_CURRENCY_CODE ,
1434 r_po_rcpt.RECEIPT_EXCHANGE_RATE ,
1435 r_po_rcpt.DENOM_CURRENCY_CODE ,
1436 decode(r_po_rcpt.DENOM_RAW_COST, null,null, nvl(ln_apportioned_txn_tax_amt,0)),
1437 r_po_rcpt.DENOM_BURDENED_COST ,
1438 r_po_rcpt.ACCT_RATE_DATE ,
1439 r_po_rcpt.ACCT_RATE_TYPE ,
1440 r_po_rcpt.ACCT_EXCHANGE_RATE ,
1441 decode(r_po_rcpt.ACCT_RAW_COST,null, null, nvl(ln_apportioned_func_tax_amt,0)) ,
1442 r_po_rcpt.ACCT_BURDENED_COST ,
1443 r_po_rcpt.ACCT_EXCHANGE_ROUNDING_LIMIT ,
1444 r_po_rcpt.PROJECT_CURRENCY_CODE ,
1445 r_po_rcpt.PROJECT_RATE_DATE ,
1446 r_po_rcpt.PROJECT_RATE_TYPE ,
1447 r_po_rcpt.PROJECT_EXCHANGE_RATE ,
1448 r_po_rcpt.ORIG_EXP_TXN_REFERENCE1 ,
1449 r_po_rcpt.ORIG_EXP_TXN_REFERENCE2 ,
1450 r_po_rcpt.ORIG_EXP_TXN_REFERENCE3 ,
1451 r_po_rcpt.ORIG_USER_EXP_TXN_REFERENCE ,
1452 r_po_rcpt.VENDOR_NUMBER ,
1453 r_po_rcpt.OVERRIDE_TO_ORGANIZATION_NAME,
1454 r_po_rcpt.REVERSED_ORIG_TXN_REFERENCE ,
1455 r_po_rcpt.BILLABLE_FLAG ,
1456 r_po_rcpt.PERSON_BUSINESS_GROUP_NAME ,
1457 r_po_rcpt.PROJFUNC_CURRENCY_CODE ,
1458 r_po_rcpt.PROJFUNC_COST_RATE_TYPE ,
1459 r_po_rcpt.PROJFUNC_COST_RATE_DATE ,
1460 r_po_rcpt.PROJFUNC_COST_EXCHANGE_RATE ,
1461 r_po_rcpt.PROJECT_RAW_COST ,
1462 r_po_rcpt.PROJECT_BURDENED_COST ,
1463 r_po_rcpt.ASSIGNMENT_NAME ,
1464 r_po_rcpt.WORK_TYPE_NAME ,
1465 r_po_rcpt.CDL_SYSTEM_REFERENCE4 ,
1466 r_po_rcpt.ACCRUAL_FLAG ,
1467 r_po_rcpt.PROJECT_ID ,
1468 r_po_rcpt.TASK_ID ,
1469 r_po_rcpt.PERSON_ID ,
1470 r_po_rcpt.ORGANIZATION_ID ,
1471 r_po_rcpt.NON_LABOR_RESOURCE_ORG_ID ,
1472 rec_get_non_rec_taxes.vendor_id ,
1473 r_po_rcpt.OVERRIDE_TO_ORGANIZATION_ID ,
1474 r_po_rcpt.ASSIGNMENT_ID ,
1475 r_po_rcpt.WORK_TYPE_ID ,
1476 r_po_rcpt.PERSON_BUSINESS_GROUP_ID ,
1477 r_po_rcpt.INVENTORY_ITEM_ID ,
1478 r_po_rcpt.WIP_RESOURCE_ID ,
1479 r_po_rcpt.UNIT_OF_MEASURE ,
1480 r_po_rcpt.PO_NUMBER ,
1481 r_po_rcpt.PO_HEADER_ID ,
1482 r_po_rcpt.PO_LINE_NUM ,
1483 r_po_rcpt.PO_LINE_ID ,
1484 r_po_rcpt.PERSON_TYPE ,
1485 r_po_rcpt.PO_PRICE_TYPE ,
1486 r_po_rcpt.ADJUSTED_EXPENDITURE_ITEM_ID ,
1487 r_po_rcpt.FC_DOCUMENT_TYPE ,
1488 r_po_rcpt.DOCUMENT_TYPE ,
1489 r_po_rcpt.DOCUMENT_DISTRIBUTION_TYPE ,
1490 r_po_rcpt.SI_ASSETS_ADDITION_FLAG ,
1491 r_po_rcpt.CDL_SYSTEM_REFERENCE5 ,
1492 r_po_rcpt.SC_XFER_CODE ,
1493 r_po_rcpt.ADJUSTED_TXN_INTERFACE_ID ,
1494 r_po_rcpt.NET_ZERO_ADJUSTMENT_FLAG ,
1495 'N'
1496 );
1497 END IF;
1498
1499 jai_cmn_debug_contexts_pkg.print
1500 ( ln_reg_id
1501 , 'Number of rows updated='||sql%rowcount
1502 );
1503
1504 END LOOP;
1505
1506 IF c_check_if_tax_exists%ISOPEN THEN
1507 close c_check_if_tax_exists;
1508 END IF;
1509
1510 END LOOP;
1511
1512 -- Insert records into pa_transaction_interface_all from jai_pa_tax_trxs
1513 -- Only unprocessed records must be inserted - This is identified by using the PA_ADDITION_FLAG
1514 FOR r_jai_pa_tax_trxs IN c_jai_pa_tax_trxs
1515 LOOP
1516
1517 SELECT pa_txn_interface_s.nextval
1518 INTO ln_txn_interface_id
1519 FROM dual;
1520
1521 FND_FILE.put_line(FND_FILE.log, 'Inserting into pa_transaction_interface_all :' || ln_txn_interface_id);
1522 FND_FILE.put_line(FND_FILE.log, 'Debugs Values: ' || R_JAI_PA_TAX_TRXS.TRANSACTION_SOURCE || ':' || R_JAI_PA_TAX_TRXS.BATCH_NAME || ':'
1523 || R_JAI_PA_TAX_TRXS.TRANSACTION_STATUS_CODE ||
1524 ':' || R_JAI_PA_TAX_TRXS.SYSTEM_LINKAGE || ':' || R_JAI_PA_TAX_TRXS.ATTRIBUTE10 || ':' || R_JAI_PA_TAX_TRXS.INTERFACE_ID
1525 || ':' || R_JAI_PA_TAX_TRXS.CDL_SYSTEM_REFERENCE4 || ':' || R_JAI_PA_TAX_TRXS.ATTRIBUTE9);
1526
1527 INSERT INTO pa_transaction_interface_all(
1528 TRANSACTION_SOURCE ,
1529 BATCH_NAME ,
1530 EXPENDITURE_ENDING_DATE ,
1531 EMPLOYEE_NUMBER ,
1532 ORGANIZATION_NAME ,
1533 EXPENDITURE_ITEM_DATE ,
1534 PROJECT_NUMBER ,
1535 TASK_NUMBER ,
1536 EXPENDITURE_TYPE ,
1537 NON_LABOR_RESOURCE ,
1538 NON_LABOR_RESOURCE_ORG_NAME ,
1539 QUANTITY ,
1540 RAW_COST ,
1541 EXPENDITURE_COMMENT ,
1542 TRANSACTION_STATUS_CODE ,
1543 TRANSACTION_REJECTION_CODE ,
1544 EXPENDITURE_ID ,
1545 ORIG_TRANSACTION_REFERENCE ,
1546 ATTRIBUTE_CATEGORY ,
1547 ATTRIBUTE1 ,
1548 ATTRIBUTE2 ,
1549 ATTRIBUTE3 ,
1550 ATTRIBUTE4 ,
1551 ATTRIBUTE5 ,
1552 ATTRIBUTE6 ,
1553 ATTRIBUTE7 ,
1554 ATTRIBUTE8 ,
1555 ATTRIBUTE9 ,
1556 ATTRIBUTE10 ,
1557 RAW_COST_RATE ,
1558 INTERFACE_ID ,
1559 UNMATCHED_NEGATIVE_TXN_FLAG ,
1560 EXPENDITURE_ITEM_ID ,
1561 ORG_ID ,
1562 DR_CODE_COMBINATION_ID ,
1563 CR_CODE_COMBINATION_ID ,
1564 CDL_SYSTEM_REFERENCE1 ,
1565 CDL_SYSTEM_REFERENCE2 ,
1566 CDL_SYSTEM_REFERENCE3 ,
1567 GL_DATE ,
1568 BURDENED_COST ,
1569 BURDENED_COST_RATE ,
1570 SYSTEM_LINKAGE ,
1571 TXN_INTERFACE_ID ,
1572 USER_TRANSACTION_SOURCE ,
1573 CREATED_BY ,
1574 CREATION_DATE ,
1575 LAST_UPDATED_BY ,
1576 LAST_UPDATE_DATE ,
1577 RECEIPT_CURRENCY_AMOUNT ,
1578 RECEIPT_CURRENCY_CODE ,
1579 RECEIPT_EXCHANGE_RATE ,
1580 DENOM_CURRENCY_CODE ,
1581 DENOM_RAW_COST ,
1582 DENOM_BURDENED_COST ,
1583 ACCT_RATE_DATE ,
1584 ACCT_RATE_TYPE ,
1585 ACCT_EXCHANGE_RATE ,
1586 ACCT_RAW_COST ,
1587 ACCT_BURDENED_COST ,
1588 ACCT_EXCHANGE_ROUNDING_LIMIT ,
1589 PROJECT_CURRENCY_CODE ,
1590 PROJECT_RATE_DATE ,
1591 PROJECT_RATE_TYPE ,
1592 PROJECT_EXCHANGE_RATE ,
1593 ORIG_EXP_TXN_REFERENCE1 ,
1594 ORIG_EXP_TXN_REFERENCE2 ,
1595 ORIG_EXP_TXN_REFERENCE3 ,
1596 ORIG_USER_EXP_TXN_REFERENCE ,
1597 VENDOR_NUMBER ,
1598 OVERRIDE_TO_ORGANIZATION_NAME,
1599 REVERSED_ORIG_TXN_REFERENCE ,
1600 BILLABLE_FLAG ,
1601 PERSON_BUSINESS_GROUP_NAME ,
1602 PROJFUNC_CURRENCY_CODE ,
1603 PROJFUNC_COST_RATE_TYPE ,
1604 PROJFUNC_COST_RATE_DATE ,
1605 PROJFUNC_COST_EXCHANGE_RATE ,
1606 PROJECT_RAW_COST ,
1607 PROJECT_BURDENED_COST ,
1608 ASSIGNMENT_NAME ,
1609 WORK_TYPE_NAME ,
1610 CDL_SYSTEM_REFERENCE4 ,
1611 ACCRUAL_FLAG ,
1612 PROJECT_ID ,
1613 TASK_ID ,
1614 PERSON_ID ,
1615 ORGANIZATION_ID ,
1616 NON_LABOR_RESOURCE_ORG_ID ,
1617 VENDOR_ID ,
1618 OVERRIDE_TO_ORGANIZATION_ID ,
1619 ASSIGNMENT_ID ,
1620 WORK_TYPE_ID ,
1621 PERSON_BUSINESS_GROUP_ID ,
1622 INVENTORY_ITEM_ID ,
1623 WIP_RESOURCE_ID ,
1624 UNIT_OF_MEASURE ,
1625 PO_NUMBER ,
1626 PO_HEADER_ID ,
1627 PO_LINE_NUM ,
1628 PO_LINE_ID ,
1629 PERSON_TYPE ,
1630 PO_PRICE_TYPE ,
1631 ADJUSTED_EXPENDITURE_ITEM_ID ,
1632 FC_DOCUMENT_TYPE ,
1633 DOCUMENT_TYPE ,
1634 DOCUMENT_DISTRIBUTION_TYPE ,
1635 SI_ASSETS_ADDITION_FLAG ,
1636 CDL_SYSTEM_REFERENCE5 ,
1637 SC_XFER_CODE ,
1638 ADJUSTED_TXN_INTERFACE_ID ,
1639 NET_ZERO_ADJUSTMENT_FLAG
1640 ) VALUES (
1641 R_JAI_PA_TAX_TRXS.TRANSACTION_SOURCE ,
1642 R_JAI_PA_TAX_TRXS.BATCH_NAME ,
1643 R_JAI_PA_TAX_TRXS.EXPENDITURE_ENDING_DATE ,
1644 R_JAI_PA_TAX_TRXS.EMPLOYEE_NUMBER ,
1645 R_JAI_PA_TAX_TRXS.ORGANIZATION_NAME ,
1646 R_JAI_PA_TAX_TRXS.EXPENDITURE_ITEM_DATE ,
1647 R_JAI_PA_TAX_TRXS.PROJECT_NUMBER ,
1648 R_JAI_PA_TAX_TRXS.TASK_NUMBER ,
1649 R_JAI_PA_TAX_TRXS.EXPENDITURE_TYPE ,
1650 R_JAI_PA_TAX_TRXS.NON_LABOR_RESOURCE ,
1651 R_JAI_PA_TAX_TRXS.NON_LABOR_RESOURCE_ORG_NAME ,
1652 R_JAI_PA_TAX_TRXS.QUANTITY ,
1653 R_JAI_PA_TAX_TRXS.RAW_COST ,
1654 R_JAI_PA_TAX_TRXS.EXPENDITURE_COMMENT ,
1655 R_JAI_PA_TAX_TRXS.TRANSACTION_STATUS_CODE ,
1656 R_JAI_PA_TAX_TRXS.TRANSACTION_REJECTION_CODE ,
1657 R_JAI_PA_TAX_TRXS.EXPENDITURE_ID ,
1658 FND_GLOBAL.CONC_REQUEST_ID() , /*FP Bug#9863751 - Current Request ID must be populated in ORIG_TRANSACTION_REFERENCE*/
1659 R_JAI_PA_TAX_TRXS.ATTRIBUTE_CATEGORY ,
1660 R_JAI_PA_TAX_TRXS.ATTRIBUTE1 ,
1661 R_JAI_PA_TAX_TRXS.ATTRIBUTE2 ,
1662 R_JAI_PA_TAX_TRXS.ATTRIBUTE3 ,
1663 R_JAI_PA_TAX_TRXS.ATTRIBUTE4 ,
1664 R_JAI_PA_TAX_TRXS.ATTRIBUTE5 ,
1665 R_JAI_PA_TAX_TRXS.ATTRIBUTE6 ,
1666 R_JAI_PA_TAX_TRXS.ATTRIBUTE7 ,
1667 R_JAI_PA_TAX_TRXS.ATTRIBUTE8 ,
1668 R_JAI_PA_TAX_TRXS.ATTRIBUTE9 ,
1669 R_JAI_PA_TAX_TRXS.ATTRIBUTE10 ,
1670 R_JAI_PA_TAX_TRXS.RAW_COST_RATE ,
1671 R_JAI_PA_TAX_TRXS.INTERFACE_ID ,
1672 R_JAI_PA_TAX_TRXS.UNMATCHED_NEGATIVE_TXN_FLAG ,
1673 R_JAI_PA_TAX_TRXS.EXPENDITURE_ITEM_ID ,
1674 R_JAI_PA_TAX_TRXS.ORG_ID ,
1675 R_JAI_PA_TAX_TRXS.DR_CODE_COMBINATION_ID ,
1676 R_JAI_PA_TAX_TRXS.CR_CODE_COMBINATION_ID ,
1677 R_JAI_PA_TAX_TRXS.CDL_SYSTEM_REFERENCE1 ,
1678 R_JAI_PA_TAX_TRXS.CDL_SYSTEM_REFERENCE2 ,
1679 R_JAI_PA_TAX_TRXS.CDL_SYSTEM_REFERENCE3 ,
1680 R_JAI_PA_TAX_TRXS.GL_DATE ,
1681 R_JAI_PA_TAX_TRXS.BURDENED_COST ,
1682 R_JAI_PA_TAX_TRXS.BURDENED_COST_RATE ,
1683 R_JAI_PA_TAX_TRXS.SYSTEM_LINKAGE ,
1684 ln_txn_interface_id ,
1685 R_JAI_PA_TAX_TRXS.USER_TRANSACTION_SOURCE ,
1686 R_JAI_PA_TAX_TRXS.CREATED_BY ,
1687 R_JAI_PA_TAX_TRXS.CREATION_DATE ,
1688 R_JAI_PA_TAX_TRXS.LAST_UPDATED_BY ,
1689 R_JAI_PA_TAX_TRXS.LAST_UPDATE_DATE ,
1690 R_JAI_PA_TAX_TRXS.RECEIPT_CURRENCY_AMOUNT ,
1691 R_JAI_PA_TAX_TRXS.RECEIPT_CURRENCY_CODE ,
1692 R_JAI_PA_TAX_TRXS.RECEIPT_EXCHANGE_RATE ,
1693 R_JAI_PA_TAX_TRXS.DENOM_CURRENCY_CODE ,
1694 R_JAI_PA_TAX_TRXS.DENOM_RAW_COST ,
1695 R_JAI_PA_TAX_TRXS.DENOM_BURDENED_COST ,
1696 R_JAI_PA_TAX_TRXS.ACCT_RATE_DATE ,
1697 R_JAI_PA_TAX_TRXS.ACCT_RATE_TYPE ,
1698 R_JAI_PA_TAX_TRXS.ACCT_EXCHANGE_RATE ,
1699 R_JAI_PA_TAX_TRXS.ACCT_RAW_COST ,
1700 R_JAI_PA_TAX_TRXS.ACCT_BURDENED_COST ,
1701 R_JAI_PA_TAX_TRXS.ACCT_EXCHANGE_ROUNDING_LIMIT ,
1702 R_JAI_PA_TAX_TRXS.PROJECT_CURRENCY_CODE ,
1703 R_JAI_PA_TAX_TRXS.PROJECT_RATE_DATE ,
1704 R_JAI_PA_TAX_TRXS.PROJECT_RATE_TYPE ,
1705 R_JAI_PA_TAX_TRXS.PROJECT_EXCHANGE_RATE ,
1706 R_JAI_PA_TAX_TRXS.ORIG_EXP_TXN_REFERENCE1 ,
1707 R_JAI_PA_TAX_TRXS.ORIG_EXP_TXN_REFERENCE2 ,
1708 R_JAI_PA_TAX_TRXS.ORIG_EXP_TXN_REFERENCE3 ,
1709 R_JAI_PA_TAX_TRXS.ORIG_USER_EXP_TXN_REFERENCE ,
1710 R_JAI_PA_TAX_TRXS.VENDOR_NUMBER ,
1711 R_JAI_PA_TAX_TRXS.OVERRIDE_TO_ORGANIZATION_NAME,
1712 R_JAI_PA_TAX_TRXS.REVERSED_ORIG_TXN_REFERENCE ,
1713 R_JAI_PA_TAX_TRXS.BILLABLE_FLAG ,
1714 R_JAI_PA_TAX_TRXS.PERSON_BUSINESS_GROUP_NAME ,
1715 R_JAI_PA_TAX_TRXS.PROJFUNC_CURRENCY_CODE ,
1716 R_JAI_PA_TAX_TRXS.PROJFUNC_COST_RATE_TYPE ,
1717 R_JAI_PA_TAX_TRXS.PROJFUNC_COST_RATE_DATE ,
1718 R_JAI_PA_TAX_TRXS.PROJFUNC_COST_EXCHANGE_RATE ,
1719 R_JAI_PA_TAX_TRXS.PROJECT_RAW_COST ,
1720 R_JAI_PA_TAX_TRXS.PROJECT_BURDENED_COST ,
1721 R_JAI_PA_TAX_TRXS.ASSIGNMENT_NAME ,
1722 R_JAI_PA_TAX_TRXS.WORK_TYPE_NAME ,
1723 R_JAI_PA_TAX_TRXS.CDL_SYSTEM_REFERENCE4 ,
1724 R_JAI_PA_TAX_TRXS.ACCRUAL_FLAG ,
1725 R_JAI_PA_TAX_TRXS.PROJECT_ID ,
1726 R_JAI_PA_TAX_TRXS.TASK_ID ,
1727 R_JAI_PA_TAX_TRXS.PERSON_ID ,
1728 R_JAI_PA_TAX_TRXS.ORGANIZATION_ID ,
1729 R_JAI_PA_TAX_TRXS.NON_LABOR_RESOURCE_ORG_ID ,
1730 R_JAI_PA_TAX_TRXS.VENDOR_ID ,
1731 R_JAI_PA_TAX_TRXS.OVERRIDE_TO_ORGANIZATION_ID ,
1732 R_JAI_PA_TAX_TRXS.ASSIGNMENT_ID ,
1733 R_JAI_PA_TAX_TRXS.WORK_TYPE_ID ,
1734 R_JAI_PA_TAX_TRXS.PERSON_BUSINESS_GROUP_ID ,
1735 R_JAI_PA_TAX_TRXS.INVENTORY_ITEM_ID ,
1736 R_JAI_PA_TAX_TRXS.WIP_RESOURCE_ID ,
1737 R_JAI_PA_TAX_TRXS.UNIT_OF_MEASURE ,
1738 R_JAI_PA_TAX_TRXS.PO_NUMBER ,
1739 R_JAI_PA_TAX_TRXS.PO_HEADER_ID ,
1740 R_JAI_PA_TAX_TRXS.PO_LINE_NUM ,
1741 R_JAI_PA_TAX_TRXS.PO_LINE_ID ,
1742 R_JAI_PA_TAX_TRXS.PERSON_TYPE ,
1743 R_JAI_PA_TAX_TRXS.PO_PRICE_TYPE ,
1744 R_JAI_PA_TAX_TRXS.ADJUSTED_EXPENDITURE_ITEM_ID ,
1745 R_JAI_PA_TAX_TRXS.FC_DOCUMENT_TYPE ,
1746 R_JAI_PA_TAX_TRXS.DOCUMENT_TYPE ,
1747 R_JAI_PA_TAX_TRXS.DOCUMENT_DISTRIBUTION_TYPE ,
1748 R_JAI_PA_TAX_TRXS.SI_ASSETS_ADDITION_FLAG ,
1749 R_JAI_PA_TAX_TRXS.CDL_SYSTEM_REFERENCE5 ,
1750 R_JAI_PA_TAX_TRXS.SC_XFER_CODE ,
1751 R_JAI_PA_TAX_TRXS.ADJUSTED_TXN_INTERFACE_ID ,
1752 R_JAI_PA_TAX_TRXS.NET_ZERO_ADJUSTMENT_FLAG
1753 );
1754
1755 END LOOP; /*for r_jai_pa_tax_trxs in c_jai_pa_tax_trxs*/
1756
1757 IF c_jai_pa_tax_trxs%ISOPEN THEN
1758 close c_jai_pa_tax_trxs;
1759 END IF;
1760 ----------------------------------------------------------------------------------------------------------------
1761 -- Added by Jia for FP Bug#9863751, End
1762
1763
1764 /** Deregister procedure and return*/
1765 <<deregister_and_return>>
1766 jai_cmn_debug_contexts_pkg.deregister (pn_reg_id => ln_reg_id);
1767
1768 exception
1769 when others then
1770 p_process_flag := jai_constants.UNEXPECTED_ERROR;
1771 p_process_message := sqlerrm;
1772 jai_cmn_debug_contexts_pkg.print(ln_reg_id,lv_context||'->'||sqlerrm,jai_cmn_debug_contexts_pkg.summary);
1773 jai_cmn_debug_contexts_pkg.print_stack;
1774
1775 end update_interface_costs;
1776
1777
1778 -- Added by Jia for FP Bug#7242428, Begin
1779 -- This function would recalculate the commitment amount when the receipt_accrue_flag is set as Y
1780 ----------------------------------------------------------------------------------------------------------------
1781 FUNCTION get_comtmnt_amt (p_invoice_id IN NUMBER,
1782 p_project_id IN NUMBER,
1783 p_dist_line_number IN NUMBER,
1784 p_amount IN NUMBER)
1785 RETURN NUMBER
1786 IS
1787 CURSOR cur_get_po_dtls ( cp_invoice_id IN NUMBER,
1788 cp_project_id IN NUMBER,
1789 cp_dist_line_num IN NUMBER )
1790 IS
1791 SELECT pda.po_distribution_id,
1792 pda.accrue_on_receipt_flag,
1793 aida.project_id
1794 FROM ap_invoices_all aia,
1795 po_distributions_all pda,
1796 ap_invoice_distributions_all aida
1797 WHERE aia.invoice_id = cp_invoice_id
1798 AND aida.invoice_id = aia.invoice_id
1799 AND pda.po_distribution_id = aida.po_distribution_id
1800 AND aida.distribution_line_number = cp_dist_line_num
1801 AND aida.project_id = cp_project_id;
1802 Cursor cur_get_rcpt_dtls (cp_po_distribution_id IN NUMBER)
1803 IS
1804 SELECT a.transaction_id,
1805 a.shipment_header_id,
1806 a.quantity,
1807 b.currency_conversion_rate
1808 FROM jai_rcv_transactions a, rcv_transactions b
1809 WHERE b.po_distribution_id = cp_po_distribution_id
1810 AND ((b.destination_type_code = 'EXPENSE' ) OR
1811 (b.destination_type_code = 'RECEIVING' AND
1812 b.transaction_type in ('RETURN TO RECEIVING' , 'RETURN TO VENDOR')))
1813 AND b.transaction_id = a.transaction_id;
1814
1815 rec_get_rcpt_dtls cur_get_rcpt_dtls%rowtype;
1816 rec_get_po_dtls cur_get_po_dtls%rowtype;
1817 v_amount NUMBER;
1818 v_rcpt_tax_amount NUMBER;
1819 v_inv_num NUMBER;
1820
1821 BEGIN
1822
1823 OPEN cur_get_po_dtls(p_invoice_id, p_project_id, p_dist_line_number);
1824 FETCH cur_get_po_dtls into rec_get_po_dtls;
1825 CLOSE cur_get_po_dtls;
1826
1827 IF nvl(rec_get_po_dtls.accrue_on_receipt_flag,'N') = 'Y' THEN
1828
1829 OPEN cur_get_rcpt_dtls(rec_get_po_dtls.po_distribution_id);
1830 FETCH cur_get_rcpt_dtls INTO rec_get_rcpt_dtls;
1831 CLOSE cur_get_rcpt_dtls;
1832
1833 v_rcpt_tax_amount := get_nonrec_tax_amount( pv_transaction_source => 'ORACLE_PURCHASING',
1834 pv_line_type => 'PO_RECEIPT',
1835 pn_transaction_header_id => rec_get_rcpt_dtls.shipment_header_id,
1836 pn_transaction_dist_id => rec_get_rcpt_dtls.transaction_id,
1837 pv_currency_of_return_tax_amt => JAI_PA_COSTING_PKG.get_trx_curr_indicator,
1838 pn_transaction_qty => rec_get_rcpt_dtls.quantity,
1839 pn_currency_conv_rate => rec_get_rcpt_dtls.currency_conversion_rate);
1840
1841
1842 select count(*)
1843 into v_inv_num
1844 from ap_invoice_distributions_all
1845 where po_distribution_id = rec_get_po_dtls.po_distribution_id
1846 and pa_addition_flag not in ('Z','T','E','Y');
1847
1848 IF v_inv_num = 0 THEN
1849 v_inv_num := 1;
1850 END IF;
1851
1852 v_amount := p_amount - v_rcpt_tax_amount/v_inv_num;
1853 RETURN v_amount;
1854
1855 ELSE
1856 return p_amount;
1857 END IF;
1858
1859 END get_comtmnt_amt;
1860 ----------------------------------------------------------------------------------------------------------------
1861 -- Added by Jia for FP Bug#7242428, End
1862
1863
1864 -- Added by Jia for FP Bug#8805693, Begin
1865 ----------------------------------------------------------------------------------------------------------------
1866 PROCEDURE update_interface_cost_tax( p_transaction_id IN NUMBER
1867 , p_tax_type IN VARCHAR2 DEFAULT 'VAT'
1868 , p_process_flag out nocopy varchar2
1869 , p_process_message out nocopy varchar2)
1870 IS
1871 lv_transaction_source pa_transaction_interface.user_transaction_source%TYPE;
1872 lv_batch_name pa_transaction_interface.batch_name%TYPE;
1873 lv_user_transaction_source pa_transaction_interface.user_transaction_source%TYPE;
1874 ln_unique_id NUMBER;
1875 ln_org_id NUMBER;
1876 LN_REQUEST_ID NUMBER;
1877 ln_interface_id NUMBER;
1878 lv_process_status_flag VARCHAR2(1);
1879 ld_expenditure_ending_date DATE;
1880 lv_transaction_status_code pa_transaction_interface.transaction_status_code%TYPE;
1881 lv_transaction_rejection_code pa_transaction_interface.transaction_rejection_code%TYPE;
1882 ln_person_business_group_id NUMBER;
1883 ln_apportion_factor NUMBER;
1884 ln_apportioned_func_tax_amt NUMBER;
1885 ln_apportioned_trx_tax_amt NUMBER;
1886 ln_trx_tax_amount NUMBER;
1887 ln_func_tax_amount NUMBER;
1888 rec_get_deliver_trx jai_rcv_transactions%rowtype;
1889 lv_cenvat_rg_flag VARCHAR2(1);
1890 lv_cenvat_rg_message jai_rcv_transactions.cenvat_rg_message%TYPE;
1891
1892 CURSOR Rcv_Receipts_Cur (cp_transaction_id IN rcv_transactions.transaction_id%TYPE)
1893 IS
1894 SELECT rcv_txn.transaction_id rcv_transaction_id,
1895 po_dist.po_distribution_id po_distribution_id,
1896 po_dist.po_header_id po_header_id,
1897 po_head.segment1 po_num,
1898 nvl(rcv_txn.quantity,0) quantity,
1899 nvl(rcv_sub.ENTERED_DR,0) entered_dr,
1900 nvl(rcv_sub.entered_cr,0) entered_cr,
1901 nvl(rcv_sub.ACCOUNTED_DR,0) accounted_dr,
1902 nvl(rcv_sub.accounted_cr,0) accounted_cr,
1903 nvl(rcv_sub.ENTERED_NR_TAX,0) ENTERED_NR_TAX,
1904 nvl(rcv_sub.ACCOUNTED_NR_TAX,0) ACCOUNTED_NR_TAX,
1905 rcv_sub.code_combination_id dr_cc_id,
1906 rcv_txn.CURRENCY_CODE denom_currency_code ,
1907 rcv_txn.CURRENCY_CONVERSION_DATE ,
1908 rcv_txn.CURRENCY_CONVERSION_TYPE ,
1909 rcv_txn.CURRENCY_CONVERSION_RATE ,
1910 trunc(rcv_txn.TRANSACTION_DATE) GL_Date,
1911 rcv_txn.DESTINATION_TYPE_CODE ,
1912 rcv_sub.pa_addition_flag ,
1913 rcv_txn.transaction_type trx_type,
1914 po_dist.project_id,
1915 po_dist.task_id,
1916 VEND.employee_id employee_id,
1917 po_dist.expenditure_type,
1918 po_dist.EXPENDITURE_ITEM_DATE,
1919 VEND.vendor_id vendor_id,
1920 po_dist.EXPENDITURE_ORGANIZATION_ID expenditure_organization_id,
1921 Decode( nvl(VEND.employee_id, 0),0,NULL, PA_UTILS.GetEmpJobId(VEND.employee_id,po_DIST.Expenditure_Item_Date)) Job_id,
1922 po_line.ITEM_DESCRIPTION description,
1923 po_dist.attribute_category,
1924 po_dist.attribute1,
1925 po_dist.attribute2,
1926 po_dist.attribute3,
1927 po_dist.attribute4,
1928 po_dist.attribute5,
1929 po_dist.attribute6,
1930 po_dist.attribute7,
1931 po_dist.attribute8,
1932 po_dist.attribute9,
1933 po_dist.attribute10,
1934 po_dist.ORG_ID
1935 ,rcv_sub.accounting_event_id acct_evt_id
1936 ,rcv_sub.accounted_rec_tax
1937 ,rcv_sub.entered_rec_tax
1938 from rcv_transactions rcv_txn,
1939 rcv_receiving_sub_ledger rcv_sub,
1940 po_headers_all po_head,
1941 po_lines_all po_line,
1942 po_distributions_all po_dist,
1943 po_vendors vend
1944 where po_dist.CODE_COMBINATION_ID =rcv_sub.CODE_COMBINATION_ID
1945 AND rcv_sub.ACTUAL_FLAG = 'A'
1946 and rcv_sub.pa_addition_flag = 'Y'
1947 AND po_dist.project_ID IS NOT NULL
1948 AND po_dist.accrue_on_receipt_flag= 'Y'
1949 and rcv_txn.transaction_id = cp_transaction_id
1950 AND rcv_txn.transaction_id=rcv_sub.rcv_transaction_id
1951 AND rcv_txn.po_header_id=po_head.PO_HEADER_ID
1952 AND po_head.po_header_id=po_line.po_header_id
1953 AND po_line.po_line_id=po_dist.po_line_id
1954 AND rcv_txn.PO_DISTRIBUTION_ID=po_dist.po_distribution_id
1955 AND po_head.vendor_id=VEND.Vendor_Id
1956 order by rcv_txn.po_distribution_id;
1957
1958 CURSOR cur_get_tax_amount(cp_transaction_id IN rcv_transactions.transaction_id%TYPE,
1959 cp_curr_conv_rate IN rcv_transactions.CURRENCY_CONVERSION_RATE%Type,
1960 cp_tax_type IN VARCHAR2)
1961 IS
1962 SELECT tax_id , -- Added by Jia for FP Bug#9863751, fetched Tax ID to uniquely identify a line in jai_pa_tax_trxs
1963 nvl(
1964 decode(
1965 nvl(currency,'INR'),'INR',
1966 decode (modvat_flag, 'Y',0,tax_amount),
1967 decode (modvat_flag, 'Y',0,tax_amount) * cp_curr_conv_rate
1968 )
1969 ,0) functional_tax_amount ,
1970 nvl(
1971 decode(
1972 nvl(currency,'INR'),'INR',
1973 decode (modvat_flag, 'Y',0,tax_amount)/nvl(cp_curr_conv_rate,1),
1974 decode (modvat_flag, 'Y',0,tax_amount)
1975 )
1976 ,0) transaction_tax_amount
1977 FROM JAI_RCV_LINE_TAXES
1978 where ((cp_tax_type = 'VAT' AND tax_type in ( select tax_type
1979 from jai_regime_tax_types_v
1980 where regime_code = 'VAT'))
1981 OR
1982 (cp_tax_type = 'EXCISE' AND tax_type in (jai_constants.tax_type_excise,
1983 jai_constants.tax_type_exc_additional,
1984 jai_constants.tax_type_exc_other,
1985 jai_constants.tax_type_exc_edu_cess,
1986 jai_constants.tax_type_sh_exc_edu_cess
1987 )))
1988 and transaction_id = cp_transaction_id;
1989
1990 CURSOR cur_get_deliver_trx (cp_transaction_id IN rcv_transactions.transaction_id%TYPE)
1991 IS
1992 SELECT *
1993 FROM jai_rcv_transactions
1994 WHERE transaction_type = 'DELIVER'
1995 AND parent_transaction_id = cp_transaction_id;
1996
1997 CURSOR cur_get_process_flag (cp_transaction_id IN rcv_transactions.transaction_id%TYPE)
1998 IS
1999 SELECT process_status_flag
2000 FROM jai_rcv_rgm_lines
2001 WHERE transaction_id = cp_transaction_id;
2002
2003 CURSOR cur_get_cenvat_dtls (cp_transaction_id IN rcv_transactions.transaction_id%TYPE)
2004 IS
2005 SELECT cenvat_rg_status, cenvat_rg_message
2006 FROM jai_rcv_transactions
2007 WHERE transaction_id = cp_transaction_id;
2008
2009 -- Added by Jia for FP Bug#9863751, Begin
2010 ---------------------------------------------------------------------------
2011 cursor c_jai_pa_tax_trxs is
2012 select *
2013 from jai_pa_tax_trxs
2014 where pa_addition_flag = 'N'
2015 and expenditure_item_id is NULL;
2016
2017 cursor c_check_if_tax_exists(p_transaction_id in number, p_tax_id in number) is
2018 select cdl_system_reference4, attribute9
2019 from jai_pa_tax_trxs
2020 where cdl_system_reference4 = p_transaction_id
2021 and attribute9 = p_tax_id;
2022
2023 r_check_if_tax_exists c_check_if_tax_exists%rowtype;
2024 ---------------------------------------------------------------------------
2025 -- Added by Jia for FP Bug#9863751, End
2026
2027 BEGIN
2028 p_process_flag := jai_constants.successful;
2029 p_process_message := NULL;
2030
2031 IF p_tax_type = 'VAT' THEN
2032 OPEN cur_get_process_flag(p_transaction_id);
2033 FETCH cur_get_process_flag INTO lv_process_status_flag;
2034 CLOSE cur_get_process_flag;
2035
2036 IF nvl(lv_process_status_flag,'X') <> 'U' THEN
2037 RETURN;
2038 END IF;
2039
2040 ELSIF p_tax_type = 'EXCISE' THEN
2041 OPEN cur_get_cenvat_dtls(p_transaction_id);
2042 FETCH cur_get_cenvat_dtls INTO lv_cenvat_rg_flag, lv_cenvat_rg_message;
2043 CLOSE cur_get_cenvat_dtls;
2044
2045 IF (nvl(lv_cenvat_rg_flag,'P') <> 'X' OR nvl(lv_cenvat_rg_message,'$$$') <> 'Cenvat Unclaimed') THEN
2046 FND_FILE.put_line(FND_FILE.log, '1');
2047 RETURN;
2048 END IF;
2049 END IF;
2050
2051 OPEN cur_get_deliver_trx(p_transaction_id);
2052 FETCH cur_get_deliver_trx INTO rec_get_deliver_trx;
2053 CLOSE cur_get_deliver_trx;
2054
2055 FOR rec_rcv_receipts IN Rcv_Receipts_Cur (rec_get_deliver_trx.transaction_id)
2056 LOOP
2057 lv_transaction_source := 'PO RECEIPT';
2058 lv_user_transaction_source := 'Oracle Purchasing Receipt Accruals';
2059 lv_transaction_status_code := 'P';
2060 lv_transaction_rejection_code :='';
2061
2062 LN_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID();
2063
2064 FND_FILE.put_line(FND_FILE.log, 'LN_REQUEST_ID '|| LN_REQUEST_ID);
2065
2066 SELECT org_id
2067 INTO ln_org_id
2068 FROM pa_implementations;
2069
2070 /* Get the weekending date of the maximum expenditure item date of
2071 this PO distribution */
2072 SELECT pa_utils.getweekending(MAX(expenditure_item_date))
2073 INTO ld_expenditure_ending_date
2074 FROM po_distributions
2075 WHERE po_distribution_id = rec_rcv_receipts.po_distribution_id;
2076
2077 IF nvl(rec_rcv_receipts.employee_id,0) <> 0 THEN
2078 SELECT emp.business_group_id
2079 INTO ln_person_business_group_id
2080 FROM per_all_people_f emp
2081 WHERE emp.person_id = rec_rcv_receipts.employee_id
2082 AND rec_rcv_receipts.EXPENDITURE_ITEM_DATE between trunc(emp.effective_start_date) and
2083 trunc(emp.effective_end_date);
2084
2085 END IF;
2086
2087 if rec_rcv_receipts.quantity is not null
2088 and rec_rcv_receipts.quantity <> 0
2089 and rec_get_deliver_trx.quantity is not null
2090 and rec_get_deliver_trx.quantity <> 0
2091 and rec_get_deliver_trx.quantity <> rec_rcv_receipts.quantity
2092 then
2093 ln_apportion_factor := rec_get_deliver_trx.quantity / rec_rcv_receipts.quantity;
2094 else
2095 ln_apportion_factor := 1;
2096 end if;
2097 FND_FILE.put_line(FND_FILE.log, 'ln_apportion_factor '|| ln_apportion_factor);
2098 lv_batch_name := NULL;
2099
2100 FOR rec_get_tax_amount IN cur_get_tax_amount (p_transaction_id,
2101 rec_rcv_receipts.CURRENCY_CONVERSION_RATE,
2102 p_tax_type)
2103 LOOP
2104
2105 -- Added by Jia for FP Bug#9863751, Begin
2106 -- Check if the Tax lines already exist in JAI_PA_TAX_TRXS
2107 ---------------------------------------------------------------------------
2108 open c_check_if_tax_exists (rec_get_deliver_trx.transaction_id, rec_get_tax_amount.tax_id);
2109 fetch c_check_if_tax_exists into r_check_if_tax_exists;
2110 exit when c_check_if_tax_exists%FOUND;
2111 close c_check_if_tax_exists;
2112 ---------------------------------------------------------------------------
2113 -- Added by Jia for FP Bug#9863751, End
2114
2115 ln_apportioned_func_tax_amt := rec_get_tax_amount.functional_tax_amount * ln_apportion_factor;
2116 ln_apportioned_trx_tax_amt := rec_get_tax_amount.transaction_tax_amount * ln_apportion_factor;
2117
2118 FND_FILE.put_line(FND_FILE.log, 'ln_apportioned_func_tax_amt '|| ln_apportioned_func_tax_amt||
2119 ' rec_get_tax_amount.functional_tax_amount '|| rec_get_tax_amount.functional_tax_amount ||
2120 ' ln_apportioned_trx_tax_amt '|| ln_apportioned_trx_tax_amt ||
2121 ' rec_get_tax_amount.transaction_tax_amount '|| rec_get_tax_amount.transaction_tax_amount);
2122
2123 SELECT pa_interface_id_s.nextval
2124 into ln_unique_id
2125 FROM dual;
2126
2127 IF lv_batch_name IS NULL THEN
2128 lv_batch_name := 'RCV-'||ln_unique_id;
2129 END IF;
2130 ln_interface_id := ln_unique_id;
2131
2132 IF nvl(ln_apportioned_trx_tax_amt,0) <> 0 THEN
2133
2134 INSERT INTO jai_pa_tax_trxs(
2135 transaction_source
2136 , user_transaction_source
2137 , batch_name
2138 , expenditure_ending_date
2139 , expenditure_item_date
2140 , expenditure_type
2141 , quantity
2142 , raw_cost_rate
2143 , expenditure_comment
2144 , transaction_status_code
2145 , transaction_rejection_code
2146 , orig_transaction_reference
2147 , interface_id
2148 , dr_code_combination_id
2149 , cr_code_combination_id
2150 , cdl_system_reference1
2151 , cdl_system_reference2
2152 , cdl_system_reference3
2153 , cdl_system_reference4
2154 , gl_date
2155 , org_id
2156 , unmatched_negative_txn_flag
2157 , denom_raw_cost
2158 , denom_currency_code
2159 , acct_rate_date
2160 , acct_rate_type
2161 , acct_exchange_rate
2162 , acct_raw_cost
2163 , acct_exchange_rounding_limit
2164 , attribute_category
2165 , attribute1
2166 , attribute2
2167 , attribute3
2168 , attribute4
2169 , attribute5
2170 , attribute6
2171 , attribute7
2172 , attribute8
2173 , attribute9
2174 , attribute10
2175 , orig_exp_txn_reference1
2176 , orig_user_exp_txn_reference
2177 , orig_exp_txn_reference2
2178 , orig_exp_txn_reference3
2179 , last_update_date
2180 , last_updated_by
2181 , creation_date
2182 , created_by
2183 , person_id
2184 , organization_id
2185 , project_id
2186 , task_id
2187 , Vendor_id
2188 , override_to_organization_id
2189 , person_business_group_id
2190 , txn_interface_id
2191 , pa_addition_flag
2192 )
2193 VALUES
2194 ( lv_transaction_source
2195 ,lv_user_transaction_source
2196 ,lv_batch_name
2197 ,ld_expenditure_ending_date
2198 ,rec_rcv_receipts.EXPENDITURE_ITEM_DATE
2199 ,rec_rcv_receipts.expenditure_type
2200 ,rec_rcv_receipts.quantity
2201 ,ln_apportioned_trx_tax_amt
2202 ,rec_rcv_receipts.description
2203 ,lv_transaction_status_code
2204 ,lv_transaction_rejection_code
2205 ,-999999 -- Added by Jia for FP Bug#9863751
2206 ,ln_interface_id
2207 ,rec_rcv_receipts.dr_cc_id
2208 ,NULL
2209 ,rec_rcv_receipts.vendor_id
2210 ,rec_rcv_receipts.po_header_id
2211 ,rec_rcv_receipts.po_distribution_id
2212 ,rec_rcv_receipts.rcv_transaction_id
2213 ,SYSDATE /*rec_rcv_receipts.GL_Date*/
2214 /*Bug 9231374 - Data would be inserted in jai_pa_tax_trxs immediately after VAT or CENVAT is Unclaimed
2215 Hence SYSDATE can be inserted into GL Date. This would ensure that Unclaimed Taxes are accounted in the correct
2216 periods when they are interfaced to Projects*/
2217 ,ln_org_id
2218 ,'Y'
2219 ,ln_apportioned_trx_tax_amt
2220 ,rec_rcv_receipts.denom_currency_code
2221 ,rec_rcv_receipts.CURRENCY_CONVERSION_DATE
2222 ,rec_rcv_receipts.CURRENCY_CONVERSION_TYPE
2223 ,rec_rcv_receipts.CURRENCY_CONVERSION_RATE
2224 ,ln_apportioned_func_tax_amt
2225 ,1
2226 ,rec_rcv_receipts.attribute_category
2227 ,rec_rcv_receipts.attribute1
2228 ,rec_rcv_receipts.attribute2
2229 ,rec_rcv_receipts.attribute3
2230 ,rec_rcv_receipts.attribute4
2231 ,rec_rcv_receipts.attribute5
2232 ,rec_rcv_receipts.attribute6
2233 ,rec_rcv_receipts.attribute7
2234 ,rec_rcv_receipts.attribute8
2235 ,rec_get_tax_amount.tax_id -- Added by Jia for FP Bug#9863751
2236 ,'INDIA LOCALIZATION'
2237 ,rec_rcv_receipts.po_distribution_id
2238 ,rec_rcv_receipts.rcv_transaction_id
2239 ,rec_rcv_receipts.acct_evt_id
2240 ,NULL
2241 ,SYSDATE
2242 ,-1
2243 ,SYSDATE
2244 ,-1
2245 ,rec_rcv_receipts.employee_id
2246 ,rec_rcv_receipts.ORG_ID
2247 ,rec_rcv_receipts.project_id
2248 ,rec_rcv_receipts.task_id
2249 ,rec_rcv_receipts.vendor_id
2250 ,rec_rcv_receipts.expenditure_organization_id
2251 ,ln_person_business_group_id
2252 ,NULL
2253 ,'N'
2254 );
2255 END IF;
2256 END LOOP;
2257 if c_check_if_tax_exists%ISOPEN then
2258 close c_check_if_tax_exists;
2259 end if;
2260 END LOOP;
2261
2262 EXCEPTION
2263 WHEN OTHERS THEN
2264 p_process_flag := jai_constants.unexpected_error;
2265 p_process_message := SUBSTR(SQLERRM,1,200);
2266 END update_interface_cost_tax;
2267 ----------------------------------------------------------------------------------------------------------------
2268 -- Added by Jia for FP Bug#8805693, End
2269
2270
2271 end jai_pa_costing_pkg;