[Home] [Help]
PACKAGE BODY: APPS.PJM_TRANSFER_IPV_PKG
Source
1 PACKAGE BODY PJM_TRANSFER_IPV_PKG AS
2 /* $Header: PJMTIPVB.pls 115.52 2004/08/18 04:30:08 yliou ship $ */
3
4 Function batch_name
5 RETURN VARCHAR2 IS
6 l_Batch_Name PA_Transaction_Interface_All.Batch_Name%TYPE;
7 l_Batch_ID NUMBER;
8
9 Begin
10 --
11 -- Batch Name can only be 10 characters long so we take the last
12 -- 7 digits of the request id instead of the full id
13 --
14 l_Batch_ID := mod(fnd_global.conc_request_id , 10000000);
15 if (l_Batch_ID < 0) then
16 l_Batch_ID := 0;
17 end if;
18
19 l_Batch_Name := 'PJM' ||
20 lpad(to_char(l_Batch_ID), 7 ,'0');
21
22 return ( l_Batch_Name );
23
24 End;
25
26
27 Function get_ipv_expenditure_type
28 ( X_Project_Id IN NUMBER
29 , X_Org_Id IN NUMBER
30 ) RETURN VARCHAR2 IS
31 l_ipv_expenditure_type VARCHAR2(30);
32
33 Begin
34
35 select nvl(ppp.ipv_expenditure_type, pop.ipv_expenditure_type)
36 into l_ipv_expenditure_type
37 from pjm_project_parameters ppp
38 , pjm_org_parameters pop
39 where pop.organization_id = X_Org_Id
40 and ppp.organization_id (+) = pop.organization_id
41 and ppp.project_id (+) = X_Project_Id;
42
43 return (l_ipv_expenditure_type);
44
45 End;
46
47 Function get_erv_expenditure_type
48 ( X_Project_Id IN NUMBER
49 , X_Org_Id IN NUMBER
50 ) RETURN VARCHAR2 IS
51 l_erv_expenditure_type VARCHAR2(30);
52
53 Begin
54
55 select nvl(ppp.erv_expenditure_type, pop.erv_expenditure_type)
56 into l_erv_expenditure_type
57 from pjm_project_parameters ppp
58 , pjm_org_parameters pop
59 where pop.organization_id = X_Org_Id
60 and ppp.organization_id (+) = pop.organization_id
61 and ppp.project_id (+) = X_Project_Id;
62
63 return (l_erv_expenditure_type);
64
65 End;
66
67
68 FUNCTION Assign_Task
69 ( X_PO_Distribution_Id IN NUMBER
70 , X_Destination_Type_Code IN VARCHAR2
71 , X_Project_Id IN NUMBER
72 ) RETURN VARCHAR2 IS
73
74 CURSOR c_inv IS
75 SELECT PJM_TASK_AUTO_ASSIGN.Inv_Task_WNPS
76 ( POD.Destination_Organization_Id
77 , X_Project_Id
78 , POL.Item_Id
79 , POD.Po_Header_Id
80 , Null
81 , Null )
82 FROM PO_Distributions POD
83 , PO_Lines POL
84 WHERE POD.PO_Distribution_Id = X_PO_Distribution_Id
85 AND POL.PO_Line_Id = POD.PO_Line_Id;
86
87 CURSOR c_wip IS
88 SELECT PJM_TASK_AUTO_ASSIGN.WIP_Task_WNPS
89 ( wo.organization_id
90 , X_Project_Id
91 , wo.standard_operation_id
92 , wdj.wip_entity_id
93 , wdj.primary_item_id
94 , wo.department_id )
95 FROM PO_Distributions POD
96 , WIP_Discrete_Jobs WDJ
97 , WIP_Operations WO
98 WHERE POD.PO_Distribution_Id = X_PO_Distribution_Id
99 AND WO.WIP_Entity_Id = POD.WIP_Entity_Id
100 AND WO.Operation_Seq_Num = POD.WIP_Operation_Seq_Num
101 AND WDJ.WIP_Entity_Id = WO.Wip_Entity_Id;
102
103 L_Task_ID NUMBER;
104
105 BEGIN
106
107 L_Task_ID := NULL;
108
109 IF ( X_Destination_Type_Code = 'INVENTORY' ) THEN
110
111 OPEN c_inv;
112 FETCH c_inv INTO L_Task_ID;
113 CLOSE c_inv;
114
115 ELSIF ( X_Destination_Type_Code = 'SHOP FLOOR' ) THEN
116
117 OPEN c_wip;
118 FETCH c_wip INTO L_Task_ID;
119 CLOSE c_wip;
120
121 END IF;
122
123 RETURN ( L_Task_ID );
124
125 END Assign_Task;
126
127
128 PROCEDURE Timestamp IS
129 Current_Time DATE;
130 BEGIN
131 Current_Time := sysdate;
132 fnd_message_cache.set_name('FND' , 'UTIL-CURRENT TIME');
133 fnd_message_cache.set_token('DATE' , fnd_date.date_to_displaydate(Current_Time));
134 fnd_message_cache.set_token('TIME' , to_char(Current_Time , 'HH24:MI:SS'));
135 PJM_CONC.put_line(fnd_message_cache.get);
136 PJM_CONC.new_line(1);
137 EXCEPTION
138 WHEN OTHERS THEN
139 NULL;
140 END Timestamp;
141
142
143 ---------------------------------------------------------------------------
144 -- PUBLIC PROCEDURE
145 -- Transfer_IPV_to_PA
146 --
147 -- DESCRIPTION
148 -- This procedure will get the expenditure and costing data from Invoice
149 -- Distributions which has IPV amount and the destination type is
150 -- INVENTORY. And then push these data to PA_TRANSACTION_INTERFACES.
151 --
152 -- PARAMETERS
153 -- X_Project_Id IN
154 -- X_Start_Date IN
155 -- X_End_Date IN
156 -- ERRBUF OUT
157 -- RETCODE OUT
158 --
159 ---------------------------------------------------------------------------
160
161 PROCEDURE Transfer_IPV_to_PA
162 ( ERRBUF OUT NOCOPY VARCHAR2
163 , RETCODE OUT NOCOPY NUMBER
164 , X_Project_Id IN NUMBER
165 , X_Start_Date IN VARCHAR2
166 , X_End_Date IN VARCHAR2
167 , X_Submit_Trx_Import IN VARCHAR2
168 , X_Trx_Status_Code IN VARCHAR2
169 ) IS
170
171 l_proj_status VARCHAR2(30);
172 l_billable_flag VARCHAR2(1);
173 l_request_id NUMBER;
174 l_user_id NUMBER;
175 l_IPV_Exp_Type VARCHAR2(30);
176 l_ERV_Exp_Type VARCHAR2(30);
177 l_curr_invoice_id NUMBER;
178 l_first_invoice BOOLEAN;
179 l_imp_req_id NUMBER;
180 l_base_currency_code AP_System_parameters.base_currency_code%TYPE;
181
182 -- l_msg_application VARCHAR2(30) := 'PA';
183 -- l_msg_type VARCHAR2(30);
184 -- l_msg_token1 VARCHAR2(30);
185 -- l_msg_token2 VARCHAR2(30);
186 -- l_msg_token3 VARCHAR2(30);
187 -- l_msg_count NUMBER;
188
189 l_IPV_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
190 l_ERV_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
191 l_Batch_Name PA_Transaction_Interface_All.Batch_Name%TYPE;
192 l_Receipt_Num RCV_Shipment_Headers.Receipt_Num%TYPE;
193 l_User_Conv_Type GL_Daily_Conversion_Types.User_Conversion_Type%TYPE;
194 l_Start_Date DATE;
195 l_End_Date DATE;
196 l_Task_Id NUMBER;
197
198 l_progress NUMBER;
199 l_blue_print_enabled_flag VARCHAR2(1);
200 l_autoaccounting_flag VARCHAR2(1);
201 l_transaction_source VARCHAR2(30);
202 l_trx_status_code VARCHAR2(30);
203
204 CURSOR Inv_Curs IS
205 SELECT
206 INV.Invoice_id Invoice_Id
207 , DIST.Distribution_Line_Number Distribution_Line_Number
208 , PAP.Project_Id Project_Id
209 , PAP.Segment1 Project_Number
210 , POD.Task_id Task_Id
211 , DIST.Accounting_Date Expenditure_Item_Date
212 , INV.Vendor_Id Vendor_Id
213 , INV.Created_By Created_By
214 , POD.Destination_Organization_Id Expenditure_Organization_Id
215 , NVL( DIST.description
216 , POL.Item_Description) Expenditure_Comment
217 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
218 , DIST.Rate_Var_Code_Combination_Id Rate_Var_Code_Combination_Id
219 , DIST.Price_Var_Code_Combination_Id Price_Var_Code_Combination_Id
220 , nvl( DIST.Accts_Pay_Code_Combination_Id
221 , INV.Accts_Pay_Code_Combination_Id)
222 Accts_Pay_Code_Combination_Id
223 , INV.Invoice_Currency_Code Invoice_Currency_Code
224 , INV.Exchange_Rate_Type Exchange_Rate_Type
225 , INV.Exchange_Date Exchange_Date
226 , INV.Exchange_Rate Exchange_Rate
227 , DIST.Invoice_Price_Variance Invoice_Price_Variance
228 , DIST.Base_Invoice_Price_Variance Base_Invoice_Price_Variance
229 , DIST.Exchange_Rate_Variance Exchange_Rate_Variance
230 , POD.PO_Distribution_Id PO_Distribution_Id
231 , POD.Destination_Type_Code Destination_Type_Code
232 , DIST.RCV_Transaction_Id RCV_Transaction_Id
233 FROM
234 AP_Invoices INV,
235 AP_Invoice_Distributions DIST,
236 PO_Distributions POD,
237 PO_Lines POL,
238 PA_Projects_ALL PAP,
239 PJM_Org_Parameters POP
240 WHERE DIST.Pa_Addition_Flag IN ( 'N','S','A','B','C','D','E','I',
241 'J','K','M','P','Q','V','X','W' )
242 AND DIST.Posted_Flag = 'Y'
243 AND DIST.LINE_TYPE_LOOKUP_CODE = 'ITEM'
244 AND INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
245 AND DIST.Invoice_Id = INV.Invoice_Id
246 AND (( l_Start_Date is null and l_End_Date is null)
247 OR ( l_Start_Date is not null and l_End_Date is not null
248 and DIST.Accounting_Date between l_Start_Date and l_End_Date)
249 OR ( l_Start_Date is not null and l_End_Date is null
250 and DIST.Accounting_Date >= l_Start_Date )
251 OR ( l_Start_Date is null and l_End_Date is not null
252 and DIST.Accounting_Date <= l_End_Date ))
253 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
254 AND POD.Destination_Type_Code in ( 'INVENTORY' , 'SHOP FLOOR' )
255 AND POP.Organization_Id = POD.Destination_Organization_Id
256 AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
257 AND PAP.Project_Id = NVL(X_Project_Id, PAP.Project_Id)
258 AND DIST.Po_Distribution_Id = POD.Po_Distribution_Id
259 AND POD.Po_Line_Id = POL.Po_Line_Id
260 ORDER BY 9,1,2
261 for update;
262
263
264 InvRec Inv_Curs%ROWTYPE;
265
266 BEGIN
267
268 l_curr_invoice_id := -1;
269 l_first_invoice := TRUE;
270 l_progress := 0;
271 if (X_trx_status_code is NULL)
272 then l_trx_status_code := 'P';
273 else l_trx_status_code := X_trx_status_code;
274 end if;
275
276 fnd_message.set_name('PJM','CONC-APINV IPV Transfer');
277 PJM_CONC.put_line(fnd_message.get || ' ...');
278 PJM_CONC.new_line(1);
279
280 PJM_CONC.put_line('[PROJECT_ID] = ' || X_Project_Id);
281 PJM_CONC.put_line('[START_DATE] = ' || X_Start_Date);
282 PJM_CONC.put_line('[END_DATE] = ' || X_End_Date);
283 PJM_CONC.put_line('[SUBMIT_TRX_IMPORT] = ' || X_Submit_Trx_Import);
284
285 l_request_id := fnd_global.conc_request_id;
286 l_user_id := fnd_global.user_id;
287 l_Start_Date := fnd_date.canonical_to_date(X_Start_Date);
288 l_End_Date := fnd_date.canonical_to_date(X_End_Date);
289
290 PJM_CONC.put_line('[REQUEST_ID] = ' || l_request_id);
291 PJM_CONC.new_line(1);
292
293 l_IPV_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV IPV');
294 l_ERV_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV ERV');
295
296 l_Batch_Name := PJM_Transfer_IPV_Pkg.batch_name;
297
298 PJM_CONC.put_line('Batch_Name = ' || l_batch_name);
299 PJM_CONC.new_line(1);
300
301 ----------------------------------------------------------------------
302 -- Get Accounting Currency Code
303 ----------------------------------------------------------------------
304
305 l_progress := 10;
306
307 select ap.base_currency_code
308 into l_base_currency_code
309 from gl_sets_of_books gl,
310 ap_system_parameters ap
311 where gl.set_of_books_id = ap.set_of_books_id;
312
313 ----------------------------------------------------------------------
314 -- Set pa_addition_flag of all eligible invoice distributions
315 ----------------------------------------------------------------------
316
317 ----------------------------------------------------------------------
318 -- Bug 1876773
319 -- Due to potential high cost of flagging eligible invoice
320 -- distributions based on PA_ADDITION_FLAG, net zero elimination
321 -- logic will be performed after flagging all eligible invoice
322 -- distributions.
323 --
324 -- Bug 2195329
325 -- Only flag ITEM invoice lines. TAX/FREIGHT/MISC may also result
326 -- in IPV, causing the processing of such lines in the IPV/ERV
327 -- cycle.
328 ----------------------------------------------------------------------
329 fnd_message.set_name('PJM', 'CONC-APINV Flag Inv Dists');
330 PJM_CONC.put_line(fnd_message.get || ' ...');
331
332 l_progress := 20;
333
334 -- UPDATE /*+ index(DIST AP_INVOICE_DISTRIBUTIONS_N14) */
335 -- AP_Invoice_Distributions DIST
336 -- SET DIST.Pa_Addition_Flag = 'S'
337 -- , DIST.Request_Id = l_request_id
338 -- , DIST.Last_Update_Date = SYSDATE
339 -- , DIST.Last_Updated_By = l_user_id
340 -- WHERE DIST.Posted_Flag = 'Y'
341 -- AND DIST.Pa_Addition_Flag in
342 -- ( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
343 -- , 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
344 -- AND DIST.Line_Type_Lookup_Code = 'ITEM'
345 -- AND exists (
346 -- select 'X'
347 -- from ap_invoices inv
348 -- where inv.invoice_id = dist.invoice_id
349 -- and inv.invoice_type_lookup_code <> 'EXPENSE REPORT')
350 -- AND DIST.Accounting_Date <=
351 -- NVL(l_Trx_Thru_Date, DIST.Accounting_Date)
352 -- AND exists (
353 -- Select 'X'
354 -- from Po_Distributions POD
355 -- , PJM_Org_Parameters POP
356 -- where DIST.Po_Distribution_Id = POD.Po_Distribution_ID
357 -- and POP.Organization_Id = POD.Destination_Organization_Id
358 -- and NVL(POD.Project_Id , POP.Common_Project_Id) =
359 -- NVL(X_Project_Id , NVL(POD.Project_Id , POP.Common_Project_Id))
360 -- and POD.Destination_Type_Code in ( 'INVENTORY' , 'SHOP FLOOR' )
361 -- );
362
363 ----------------------------------------------------------------------
364 -- Eliminate all pairing of reversed invoice distributions
365 ----------------------------------------------------------------------
366
367 -- fnd_message.set_name('PJM', 'CONC-APINV Elim NetZero');
368 -- PJM_CONC.put_line(fnd_message.get || ' ...');
369
370 l_progress := 30;
371
372 ----------------------------------------------------------------------
373 -- Bug 1876773
374 -- Net Zero elimination logic simplified based on similar changes
375 -- in PAAPIMP_PKG.
376 -- > Removed NVL() from pa_addition_flag
377 -- > Removed where condition with project_accounting_context column
378 -- > Restructured and simplified subquery
379 ----------------------------------------------------------------------
380 /* Bug 3495426 has taken care of the reversed transactions.
381 Net Zero is no longer needed.
382
383 UPDATE ap_invoice_distributions apd
384 SET apd.pa_addition_flag = 'Z'
385 WHERE apd.pa_addition_flag = 'S'
386 AND apd.request_id = l_request_id
387 AND 0 = (
388 SELECT SUM( nvl(apd2.base_amount , apd2.amount) )
389 FROM po_distributions pod
390 , ap_invoice_distributions apd2
391 WHERE pod.po_distribution_id = apd.po_distribution_id
392 AND apd2.po_distribution_id = pod.po_distribution_id
393 AND apd2.pa_addition_flag = apd.pa_addition_flag
394 AND apd2.request_id = apd.request_id
395 AND apd2.dist_code_combination_id = apd.dist_code_combination_id
396 AND apd2.invoice_id = apd.invoice_id
397 AND apd2.accounting_date = apd.accounting_date
398 );
399 */
400
401 ----------------------------------------------------------------------
402 -- Loop for transfering IPV from Invoice_Distribution to
403 -- PA_Transaction_Interfaces
404 ----------------------------------------------------------------------
405
406 fnd_message.set_name('PJM','CONC-APINV Start Loop');
407 PJM_CONC.put_line(fnd_message.get || ' ...');
408
409 Timestamp;
410
411 OPEN Inv_Curs;
412
413 LOOP
414
415 l_progress := 50;
416
417 FETCH Inv_Curs INTO InvRec;
418 EXIT WHEN Inv_Curs%NOTFOUND;
419
420 l_progress := 55;
421
422 IF ( InvRec.Task_Id IS NOT NULL ) THEN
423 l_Task_Id := InvRec.Task_Id;
424 ELSE
425 l_Task_Id := Assign_Task( InvRec.PO_Distribution_Id
426 , InvRec.Destination_Type_Code
427 , InvRec.Project_Id );
428 END IF;
429
430 PJM_CONC.put_line(' invoice_id ............ '||InvRec.Invoice_Id);
431 PJM_CONC.put_line(' line_num .............. '||
432 InvRec.Distribution_Line_Number);
433 PJM_CONC.put_line(' project_id ............ '||InvRec.Project_ID);
434 PJM_CONC.put_line(' task_id ............... '||l_Task_Id);
435 PJM_CONC.put_line(' expenditure_org_id .... '||
436 InvRec.Expenditure_Organization_ID);
437
438 Timestamp;
439
440 ----------------------------------------------------------------------
441 -- We commit for each invoice.
442 ----------------------------------------------------------------------
443
444 -- if (l_curr_invoice_id <> InvRec.Invoice_Id AND NOT l_first_invoice) then
445 -- COMMIT;
446 -- end if;
447
448 l_curr_invoice_id := InvRec.Invoice_Id;
449 l_first_invoice := FALSE;
450
451 ----------------------------------------------------------------------
452 -- Check Project Status
453 ----------------------------------------------------------------------
454
455 l_progress := 60;
456
457 l_IPV_Exp_Type := Get_IPV_Expenditure_Type
458 ( InvRec.Project_Id
459 , InvRec.Expenditure_Organization_Id );
460
461 l_progress := 70;
462
463 l_ERV_Exp_Type := Get_ERV_Expenditure_Type
464 ( InvRec.Project_Id
465 , InvRec.Expenditure_Organization_Id );
466
467 PJM_CONC.put_line(' ipv_expenditure_type .. '||l_ipv_exp_type);
468 PJM_CONC.put_line(' ipv_amount ............ '||
469 InvRec.Invoice_Price_Variance);
470 PJM_CONC.put_line(' erv_expenditure_type .. '||l_erv_exp_type);
471 PJM_CONC.put_line(' erv_amount ............ '||
472 InvRec.Exchange_Rate_Variance);
473 PJM_CONC.put_line(' expenditure_comment ... '||
474 InvRec.Expenditure_Comment);
475 PJM_CONC.new_line(1);
476
477 ----------------------------------------------------------------------
478 -- Getting Receipt Num from RCV_TRANSACTION_ID if exists
479 ----------------------------------------------------------------------
480
481 l_progress := 100;
482
483 if ( InvRec.RCV_Transaction_Id is not null ) then
484
485 SELECT rsh.receipt_num
486 INTO l_receipt_num
487 FROM rcv_shipment_headers rsh
488 , rcv_transactions rt
489 WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
490 AND rsh.shipment_header_id = rt.shipment_header_id;
491
492 end if;
493
494 ----------------------------------------------------------------------
495 -- Converting System RATE_TYPE to User RATE_TYPE if exists
496 ----------------------------------------------------------------------
497
498 l_progress := 105;
499
500 if ( InvRec.Exchange_Rate_Type is not null ) then
501
502 SELECT User_Conversion_Type
503 INTO l_User_Conv_Type
504 FROM gl_daily_conversion_types
505 WHERE conversion_type = InvRec.Exchange_Rate_Type;
506
507 end if;
508
509 ----------------------------------------------------------------------
510 -- Insert into PA_TRANSACTION_INTERFACES table
511 ----------------------------------------------------------------------
512
513 fnd_message.set_name('PJM','CONC-APINV Insert');
514 PJM_CONC.put_line('... ' || fnd_message.get);
515
516 if not ( InvRec.Price_Var_Code_Combination_Id is not null AND
517 nvl(nvl(InvRec.Base_Invoice_Price_Variance,
518 InvRec.Invoice_Price_Variance) , 0) <> 0 ) then
519
520 PJM_CONC.put_line('...... IPV amount not available, skipping...');
521
522 elsif ( l_IPV_Exp_Type is null) then
523
524 PJM_CONC.put_line('...... Inv Org not setup to transfer IPV, skipping...');
525
526 else
527
528 BEGIN
529
530 l_progress := 110;
531
532 ---------------------------------------------------------------------
533 -- For Blue Print org, setting Transaction Source according to
534 -- pa_posting_flag and pa_autoaccounting_flag
535 ---------------------------------------------------------------------
536
537 select NVL(pa_posting_flag,'N'),
538 NVL(pa_autoaccounting_flag,'N')
539 into l_blue_print_enabled_flag,
540 l_autoaccounting_flag
541 from pjm_org_parameters
542 where organization_id = InvRec.Expenditure_Organization_Id;
543
544 If l_blue_print_enabled_flag = 'Y' then
545 If l_autoaccounting_flag = 'Y' then
546 /* BP and autoaccounting */
547 l_transaction_source := 'PJM_CSTBP_INV_NO_ACCOUNTS';
548 else
549 /* BP and no autoaccounting -- Send Account to PA */
550 l_transaction_source := 'PJM_CSTBP_INV_ACCOUNTS';
551
552 end if; /* end of check for auto accounting */
553
554 ELSE /* non BP org */
555 l_transaction_source := 'Inventory';
556 END IF; /* check for BP org */
557
558
559 PJM_CONC.put_line('...... Processing IPV');
560
561 -- Insert for IPV
562 INSERT INTO pa_transaction_interface
563 (transaction_source,
564 batch_name,
565 expenditure_ending_date,
566 employee_number,
567 organization_name,
568 expenditure_item_date,
569 project_number,
570 task_number,
571 expenditure_type,
572 quantity,
573 expenditure_comment,
574 orig_transaction_reference,
575 unmatched_negative_txn_flag,
576 dr_code_combination_id,
577 cr_code_combination_id,
578 orig_exp_txn_reference1,
579 orig_exp_txn_reference2,
580 orig_exp_txn_reference3,
581 gl_date,
582 system_linkage,
583 transaction_status_code,
584 denom_currency_code,
585 denom_raw_cost,
586 denom_burdened_cost,
587 acct_rate_date,
588 acct_rate_type,
589 acct_exchange_rate,
590 acct_raw_cost,
591 acct_burdened_cost,
592 creation_date,
593 created_by,
594 last_update_date,
595 last_updated_by
596 )
597 SELECT
598 l_transaction_source
599 , l_Batch_Name
600 , pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
601 , NULL
602 , ORG.Name
603 , InvRec.Expenditure_Item_Date
604 , InvRec.Project_Number
605 , TASK.Task_Number
606 , l_IPV_Exp_Type
607 , InvRec.PA_Quantity
608 , NVL( InvRec.Expenditure_Comment , l_IPV_Exp_Comment )
609 , DIST.Invoice_Distribution_Id
610 , 'Y'
611 , InvRec.Price_Var_Code_Combination_Id
612 , InvRec.Accts_Pay_Code_Combination_Id
613 , InvRec.PO_Distribution_Id
614 , InvRec.RCV_Transaction_Id
615 , l_receipt_num
616 , DIST.Accounting_Date
617 , 'INV'
618 , l_trx_status_code
619 , InvRec.Invoice_Currency_Code /* denom_currency_code */
620 , InvRec.Invoice_Price_Variance /* denom_raw_cost */
621 , InvRec.Invoice_Price_Variance /* denom_burdened_cost */
622 , InvRec.Exchange_Date /* acct_rate_date */
623 , l_User_Conv_Type /* acct_rate_type */
624 , InvRec.Exchange_Rate /* acct_exchange_rate */
625 , nvl(InvRec.Base_Invoice_Price_Variance,
626 InvRec.Invoice_Price_Variance) /* acct_raw_cost */
627 , nvl(InvRec.Base_Invoice_Price_Variance,
628 InvRec.Invoice_Price_Variance) /* acct_burdened_cost */
629 , SYSDATE
630 , l_user_id
631 , SYSDATE
632 , l_user_id
633 FROM
634 AP_Invoice_Distributions DIST
635 , PA_Tasks TASK
636 , HR_Organization_Units ORG
637 WHERE
638 DIST.Invoice_Id = InvRec.Invoice_Id
639 AND DIST.Distribution_Line_Number = InvRec.Distribution_Line_Number
640 -- AND DIST.PA_Addition_Flag = 'S'
641 AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
642 AND TASK.Task_Id = l_Task_Id;
643
644 EXCEPTION
645 WHEN NO_DATA_FOUND THEN
646 NULL;
647 WHEN DUP_VAL_ON_INDEX THEN
648 NULL;
649 END;
650
651 end if;
652
653 -- Insert for ERV
654 if not ( InvRec.Rate_Var_Code_Combination_Id is not null AND
655 InvRec.Exchange_Rate_Variance <> 0 ) then
656
657 PJM_CONC.put_line('...... ERV amount not available, skipping...');
658
659 elsif ( l_ERV_Exp_Type is null ) then
660
661 PJM_CONC.put_line('...... Inv Org not setup to transfer ERV, skipping...');
662
663 else
664
665 BEGIN
666
667 l_progress := 120;
668
669 PJM_CONC.put_line('...... Processing ERV');
670
671 INSERT INTO pa_transaction_interface
672 (transaction_source,
673 batch_name,
674 expenditure_ending_date,
675 employee_number,
676 organization_name,
677 expenditure_item_date,
678 project_number,
679 task_number,
680 expenditure_type,
681 quantity,
682 expenditure_comment,
683 orig_transaction_reference,
684 unmatched_negative_txn_flag,
685 dr_code_combination_id,
686 cr_code_combination_id,
687 orig_exp_txn_reference1,
688 orig_exp_txn_reference2,
689 orig_exp_txn_reference3,
690 gl_date,
691 system_linkage,
692 transaction_status_code,
693 denom_currency_code,
694 denom_raw_cost,
695 denom_burdened_cost,
696 acct_rate_date,
697 acct_rate_type,
698 acct_exchange_rate,
699 acct_raw_cost,
700 acct_burdened_cost,
701 creation_date,
702 created_by,
703 last_update_date,
704 last_updated_by
705 )
706 SELECT
707 l_transaction_source
708 , l_Batch_Name
709 , pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
710 , NULL
711 , ORG.Name
712 , InvRec.Expenditure_Item_Date
713 , InvRec.Project_Number
714 , TASK.Task_Number
715 , l_ERV_Exp_Type
716 , InvRec.PA_Quantity
717 , NVL( InvRec.Expenditure_Comment , l_ERV_Exp_Comment )
718 , DIST.Invoice_Distribution_Id
719 , 'Y'
720 , InvRec.Rate_Var_Code_Combination_Id
721 , InvRec.Accts_Pay_Code_Combination_Id
722 , InvRec.PO_Distribution_Id
723 , InvRec.RCV_Transaction_Id
724 , l_receipt_num
725 , DIST.Accounting_Date
726 , 'INV'
727 , l_trx_status_code
728 , l_base_currency_code /* denom_currency_code */
729 , InvRec.Exchange_Rate_Variance /* denom_raw_cost */
730 , InvRec.Exchange_Rate_Variance /* denom_burdened_cost */
731 , NULL /* acct_rate_date */
732 , NULL /* acct_rate_type */
733 , NULL /* acct_exchange_rate */
734 , InvRec.Exchange_Rate_Variance /* acct_raw_cost */
735 , InvRec.Exchange_Rate_Variance /* acct_burdened_cost */
736 , SYSDATE
737 , l_user_id
738 , SYSDATE
739 , l_user_id
740 FROM
741 AP_Invoice_Distributions DIST
742 , PA_Tasks TASK
743 , HR_Organization_Units ORG
744 WHERE
745 DIST.Invoice_Id = InvRec.Invoice_Id
746 AND DIST.Distribution_Line_Number = InvRec.Distribution_Line_Number
747 -- AND DIST.PA_Addition_Flag = 'S'
748 AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
749 AND TASK.Task_Id = l_Task_Id;
750
751 EXCEPTION
752 WHEN NO_DATA_FOUND THEN
753 NULL;
754 WHEN DUP_VAL_ON_INDEX THEN
755 NULL;
756 END;
757 end if;
758
759 ----------------------------------------------------------------------
760 -- Update pa_addition_flag to 'Y' for successful invoice distributions
761 ----------------------------------------------------------------------
762
763 fnd_message.set_name('PJM','CONC-APINV Flag Comp');
764 PJM_CONC.put_line('... ' || fnd_message.get);
765 PJM_CONC.new_line(1);
766
767 l_progress := 130;
768
769 UPDATE AP_Invoice_Distributions
770 SET Pa_Addition_Flag = 'Y',
771 Request_Id = l_request_id
772 WHERE -- Pa_Addition_Flag = 'S' AND
773 Invoice_Id = InvRec.Invoice_Id
774 AND Distribution_Line_Number = InvRec.Distribution_Line_Number;
775
776 END LOOP;
777
778 CLOSE Inv_Curs;
779
780 COMMIT;
781 fnd_message.set_name('PJM','CONC-APINV Finish Loop');
782 PJM_CONC.put_line(fnd_message.get || ' ...');
783 PJM_CONC.new_line(1);
784
785 Timestamp;
786
787 l_progress := 140;
788
789 if (X_Submit_Trx_Import = 'Y') then
790 l_imp_req_id := fnd_request.submit_request('PA','PAXTRTRX',
791 'PRC: Transaction Import',
792 NULL, FALSE,
793 'Inventory',
794 l_Batch_Name);
795 end if;
796
797 retcode := PJM_CONC.G_conc_success;
798 return;
799
800
801 EXCEPTION
802 when OTHERS then
803 errbuf := 'IPV-'||l_progress||': '||sqlerrm;
804 retcode := PJM_CONC.G_conc_failure;
805 return;
806
807 END Transfer_IPV_to_PA;
808
809
810 END PJM_TRANSFER_IPV_PKG;