[Home] [Help]
PACKAGE BODY: APPS.PJM_TRANSFER_CHARGES_PKG
Source
1 PACKAGE BODY PJM_TRANSFER_CHARGES_PKG AS
2 /* $Header: PJMTFCGB.pls 120.21.12020000.3 2012/11/21 00:53:50 liawei ship $ */
3
4 -- Start of comments
5 -- API name : Batch_Name
6 -- Type : Public
7 -- Pre-reqs : None.
8 -- Function : Get the batch name for the batch process
9 -- Parameters :
10 -- IN : N/A
11 -- Version : Current version 1.0
12 -- Previous version 1.0
13 -- Initial version 1.0
14 -- End of comments
15
16 Function batch_name
17 RETURN VARCHAR2 IS
18 l_Batch_Name PA_Transaction_Interface_All.Batch_Name%TYPE;
19 l_Batch_ID NUMBER;
20
21 Begin
22 --
23 -- Batch Name can only be 10 characters long so we take the last
24 -- 7 digits of the request id instead of the full id
25 --
26 l_Batch_ID := mod(fnd_global.conc_request_id , 10000000);
27 if (l_Batch_ID < 0) then
28 l_Batch_ID := 0;
29 end if;
30
31 l_Batch_Name := 'PJM' ||
32 lpad(to_char(l_Batch_ID), 7 ,'0');
33
34 return ( l_Batch_Name );
35
36 End Batch_Name;
37
38 /*
39 Function get_ipv_expenditure_type
40 ( X_Project_Id IN NUMBER
41 , X_Org_Id IN NUMBER
42 ) RETURN VARCHAR2 IS
43 l_ipv_expenditure_type VARCHAR2(30);
44
45 Begin
46
47 select nvl(ppp.ipv_expenditure_type, pop.ipv_expenditure_type)
48 into l_ipv_expenditure_type
49 from pjm_project_parameters ppp
50 , pjm_org_parameters pop
51 where pop.organization_id = X_Org_Id
52 and ppp.organization_id (+) = pop.organization_id
53 and ppp.project_id (+) = X_Project_Id;
54
55 return (l_ipv_expenditure_type);
56
57 End;
58
59 Function get_erv_expenditure_type
60 ( X_Project_Id IN NUMBER
61 , X_Org_Id IN NUMBER
62 ) RETURN VARCHAR2 IS
63 l_erv_expenditure_type VARCHAR2(30);
64
65 Begin
66
67 select nvl(ppp.erv_expenditure_type, pop.erv_expenditure_type)
68 into l_erv_expenditure_type
69 from pjm_project_parameters ppp
70 , pjm_org_parameters pop
71 where pop.organization_id = X_Org_Id
72 and ppp.organization_id (+) = pop.organization_id
73 and ppp.project_id (+) = X_Project_Id;
74
75 return (l_erv_expenditure_type);
76
77 End; */
78
79 -- Start of comments
80 -- API name : Get_Charges_Expenditure_Type
81 -- Type : Public
82 -- Pre-reqs : None.
83 -- Function : Get the expenture type for IPV,ERV and special
84 -- : charges
85 -- Parameters :
86 -- IN : X_Type VARCHAR2
87 -- IN : X_Project_ID NUMBER
88 -- IN : X_Org_ID NUMBER
89 -- Version : Current version 1.0
90 -- Previous version 1.0
91 -- Initial version 1.0
92 -- End of comments
93
94 Function get_charges_expenditure_type
95 ( X_Type IN VARCHAR2
96 , X_Project_Id IN NUMBER
97 , X_Org_Id IN NUMBER
98 ) RETURN VARCHAR2 IS
99 l_expenditure_type VARCHAR2(30);
100
101 /* added NONREC_TAX in the decode statement for bug 7482789*/
102 cursor c is
103 select decode(X_Type,
104 'FREIGHT', nvl(ppp.freight_expenditure_type,
105 pop.freight_expenditure_type),
106 'MISCELLANEOUS', nvl(ppp.misc_expenditure_type,
107 pop.misc_expenditure_type),
108 'TIPV', nvl(ppp.tax_expenditure_type,
109 pop.tax_expenditure_type),
110 'TERV', nvl(ppp.erv_expenditure_type,
111 pop.erv_expenditure_type),
112 'IPV', nvl(ppp.ipv_expenditure_type,
113 pop.ipv_expenditure_type),
114 'ERV', nvl(ppp.erv_expenditure_type,
115 pop.erv_expenditure_type),
116 'TRV', nvl(ppp.tax_expenditure_type,
117 pop.tax_expenditure_type),
118 'NONREC_TAX', nvl(ppp.tax_expenditure_type,
119 pop.tax_expenditure_type),
120 null)
121 into l_expenditure_type
122 from pjm_project_parameters ppp
123 , pjm_org_parameters pop
124 where pop.organization_id = X_Org_ID
125 and ppp.organization_id (+) = pop.organization_id
126 and ppp.project_id (+) = X_Project_Id;
127
128 Begin
129
130 if X_Type is not null then
131 open c;
132 fetch c into l_expenditure_type;
133 close c;
134 end if;
135
136 return l_expenditure_type;
137
138 End get_charges_expenditure_type;
139
140 -- Start of comments
141 -- API name : Assign_Task
142 -- Type : Private
143 -- Pre-reqs : None.
144 -- Function : Assign tasks for common projects
145 -- Parameters :
146 -- IN : X_PO_Distribution_ID NUMBER
147 -- IN : X_Destination_Type_Code VARCHAR2
148 -- IN : X_Project_ID NUMBER
149 -- Version : Current version 1.0
150 -- Previous version 1.0
151 -- Initial version 1.0
152 -- End of comments
153
154 FUNCTION Assign_Task
155 ( X_PO_Distribution_Id IN NUMBER
156 , X_Destination_Type_Code IN VARCHAR2
157 , X_Project_Id IN NUMBER
158 ) RETURN VARCHAR2 IS
159
160 CURSOR c_inv IS
161 SELECT PJM_TASK_AUTO_ASSIGN.Inv_Task_WNPS
162 ( POD.Destination_Organization_Id
163 , X_Project_Id
164 , POL.Item_Id
165 , POD.Po_Header_Id
166 , Null
167 , Null )
168 FROM PO_Distributions_All POD
169 , PO_Lines_All POL
170 WHERE POD.PO_Distribution_Id = X_PO_Distribution_Id
171 AND POL.PO_Line_Id = POD.PO_Line_Id;
172
173 CURSOR c_wip IS
174 SELECT PJM_TASK_AUTO_ASSIGN.WIP_Task_WNPS
175 ( wo.organization_id
176 , X_Project_Id
177 , wo.standard_operation_id
178 , wdj.wip_entity_id
179 , wdj.primary_item_id
180 , wo.department_id )
181 FROM PO_Distributions_All POD
182 , WIP_Discrete_Jobs WDJ
183 , WIP_Operations WO
184 WHERE POD.PO_Distribution_Id = X_PO_Distribution_Id
185 AND WO.WIP_Entity_Id = POD.WIP_Entity_Id
186 AND WO.Operation_Seq_Num = POD.WIP_Operation_Seq_Num
187 AND WDJ.WIP_Entity_Id = WO.Wip_Entity_Id;
188
189 L_Task_ID NUMBER;
190
191 BEGIN
192
193 L_Task_ID := NULL;
194
195 IF ( X_Destination_Type_Code = 'INVENTORY' ) THEN
196
197 OPEN c_inv;
198 FETCH c_inv INTO L_Task_ID;
199 CLOSE c_inv;
200
201 ELSIF ( X_Destination_Type_Code = 'SHOP FLOOR' ) THEN
202
203 OPEN c_wip;
204 FETCH c_wip INTO L_Task_ID;
205 CLOSE c_wip;
206
207 END IF;
208
209 RETURN ( L_Task_ID );
210
211 END Assign_Task;
212
213 -- Start of comments
214 -- API name : Timestamp
215 -- Type : Private
216 -- Pre-reqs : None.
217 -- Function : Set time stamp for log file
218 -- Parameters : N/A
219 -- Version : Current version 1.0
220 -- Previous version 1.0
221 -- Initial version 1.0
222 -- End of comments
223
224 PROCEDURE Timestamp IS
225 Current_Time DATE;
226 BEGIN
227 Current_Time := sysdate;
228 fnd_message_cache.set_name('FND' , 'UTIL-CURRENT TIME');
229 fnd_message_cache.set_token('DATE' , fnd_date.date_to_displaydate(Current_Time));
230 fnd_message_cache.set_token('TIME' , to_char(Current_Time , 'HH24:MI:SS'));
231 PJM_CONC.put_line(fnd_message_cache.get);
232 PJM_CONC.new_line(1);
233 EXCEPTION
234 WHEN OTHERS THEN
235 NULL;
236 END Timestamp;
237
238 -- Start of comments
239 -- API name : Transfer_Charges_To_PA
240 -- Type : Public
241 -- Pre-reqs : None.
242 -- Function : Get the expenditure and costing data from invoice
243 -- : distributions which has amount for IPV, ERV and
244 -- : special charges, and the destination type is
245 -- : INVENTORY or SHOP FLOOR, then push data to PA
246 -- Parameters :
247 -- IN : X_Project_ID NUMBER
248 -- IN : X_Start_Date DATE
249 -- IN : X_End_Date DATE
250 -- IN : X_Submit_Trx_Import VARCHAR2
251 -- IN : X_Trx_Status_Code VARCHAR2
252 -- OUT : ERRBUF NUMBER
253 -- OUT : RETCODE NUMBER
254 -- Version : Current version 1.0
255 -- Previous version 1.0
256 -- Initial version 1.0
257 -- End of comments
258
259 PROCEDURE Transfer_Charges_to_PA
260 ( ERRBUF OUT NOCOPY VARCHAR2
261 , RETCODE OUT NOCOPY NUMBER
262 , X_Project_Id IN NUMBER
263 , X_Start_Date IN VARCHAR2
264 , X_End_Date IN VARCHAR2
265 , X_Submit_Trx_Import IN VARCHAR2
266 , X_Trx_Status_Code IN VARCHAR2
267 , X_Exp_Type_Bsd_On_Pur_Ctgy IN VARCHAR2 DEFAULT 'N' -- bug fix 12835119(FP of 7372638)
268 ) IS
269
270 l_proj_status VARCHAR2(30);
271 l_billable_flag VARCHAR2(1);
272 l_request_id NUMBER;
273 l_user_id NUMBER;
274 l_Exp_Type VARCHAR2(30);
275 l_curr_invoice_id NUMBER;
276 l_first_invoice BOOLEAN;
277 l_imp_req_id NUMBER;
278 l_base_currency_code AP_System_parameters_all.base_currency_code%TYPE;
279
280 -- l_msg_application VARCHAR2(30) := 'PA';
281 -- l_msg_type VARCHAR2(30);
282 -- l_msg_token1 VARCHAR2(30);
283 -- l_msg_token2 VARCHAR2(30);
284 -- l_msg_token3 VARCHAR2(30);
285 -- l_msg_count NUMBER;
286
287 l_IPV_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
288 l_ERV_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
289 l_Freight_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
290 l_Tax_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
291 l_Misc_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
292 l_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
293 l_Batch_Name PA_Transaction_Interface_All.Batch_Name%TYPE;
294 l_Receipt_Num RCV_Shipment_Headers.Receipt_Num%TYPE;
295 l_User_Conv_Type GL_Daily_Conversion_Types.User_Conversion_Type%TYPE;
296 l_Start_Date DATE;
297 l_End_Date DATE;
298 l_Task_Id NUMBER;
299 l_Uom VARCHAR2(25);
300 l_linkage VARCHAR2(25);
301 l_burdened_amount NUMBER;
302 l_progress NUMBER;
303 l_blue_print_enabled_flag VARCHAR2(1);
304 l_autoaccounting_flag VARCHAR2(1);
305 l_transaction_source VARCHAR2(30);
306 l_trx_status_code VARCHAR2(30);
307 l_week_ending DATE;
308 l_week_ending_day VARCHAR2(80);
309 l_week_ending_day_index number;
310 l_denom_raw_cost NUMBER;
311 l_denom_burdened_cost NUMBER;
312 l_acct_raw_cost NUMBER;
313 l_acct_burdened_cost NUMBER;
314 /* Bug 8506213, system reference is default to null and will be changed to
315 'PJM' if transaction source is either Inventory or Work In Process.
316 Later in this procedure, when inserting into pa_transaction_interface_all
317 table, l_system_reference will be used to populate the
318 cdl_system_reference4 column (4 places) */
319 l_system_reference VARCHAR2(30);
320
321 l_direct_item_PO NUMBER; -- bug fix 12835119(FP of 7372638)
322 l_po_category_id NUMBER; -- bug fix 12835119(FP of 7372638)
323 l_wip_entity_id NUMBER; -- bug fix 12835119(FP of 7372638)
324 l_po_line_id NUMBER; -- bug fix 12835119(FP of 7372638)
325 l_exp_org_id NUMBER := NULL; -- bug fix 13853188 (FP of 8244610)
326
327 --Bug 14795334 OU parameter derived by Profile Option MO: Operating Unit
328 l_org_id NUMBER;
329
330 --Bug 14795334 cursor with OU added
331 CURSOR IPV_NP_Curs IS
332 SELECT INV.Invoice_id Invoice_Id
333 , DIST.Invoice_Distribution_Id Invoice_Distribution_Id
334 , PAP.Org_Id Proj_Org_Id
335 , PAP.Project_Id Project_Id
336 , PAP.Segment1 Project_Number
337 , POD.Task_id Task_Id
338 , DIST.Accounting_Date Expenditure_Item_Date
339 , INV.Vendor_Id Vendor_Id
340 , INV.Created_By Created_By
341 , POD.Destination_Organization_Id Expenditure_Organization_Id
342 , POD.Org_Id Org_Id
343 , DIST.description Expenditure_Comment
344 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
345 , DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
346 , nvl( DIST.Accts_Pay_Code_Combination_Id
347 , INV.Accts_Pay_Code_Combination_Id)
348 Accts_Pay_Code_Combination_Id
349 , INV.Invoice_Currency_Code Invoice_Currency_Code
350 , INV.Exchange_Rate_Type Exchange_Rate_Type
351 , INV.Exchange_Date Exchange_Date
352 , INV.Exchange_Rate Exchange_Rate
353 , DIST.Amount Charge_Amount
354 , DIST.Base_Amount Base_Charge_Amount
355 , DIST.PO_Distribution_Id PO_Distribution_Id
356 , DIST.RCV_Transaction_Id RCV_Transaction_Id
357 , DIST.Line_Type_Lookup_Code Line_Type_Lookup_Code
358 FROM AP_Invoices_All INV,
359 (SELECT Invoice_Distribution_Id
360 , Invoice_Id
361 , Project_Id
362 , Task_id
363 , Org_id
364 , Accounting_Date
365 , Expenditure_Organization_Id
366 , description
367 , Pa_Quantity
368 , Dist_Code_Combination_Id
369 , Accts_Pay_Code_Combination_Id
370 , Amount
371 , Base_Amount
372 , PO_Distribution_Id
373 , RCV_Transaction_Id
374 , Line_Type_Lookup_Code
375 FROM AP_Invoice_Distributions_all
376 WHERE LINE_TYPE_LOOKUP_CODE IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV')
377 AND (Accounting_Date between NVL(l_Start_Date, to_date('0001-01-01', 'YYYY-MM-DD')) and NVL(l_End_Date, to_date('9999-01-01', 'YYYY-MM-DD')))
378 AND PA_ADDITION_FLAG in ('E', 'M', 'N')
379 AND POSTED_FLAG = 'Y'
380 ) DIST,
381 PA_Projects_ALL PAP,
382 PJM_Org_Parameters POP,
383 PO_Distributions_All POD
384 WHERE INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
385 AND POD.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
386 AND DIST.Invoice_Id = INV.Invoice_Id
387 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
388 AND DIST.po_distribution_id = POD.po_distribution_id
389 AND INV.org_id = l_org_id
390 AND DIST.org_id = INV.org_id
391 AND PAP.org_id = INV.org_id
392 AND POP.Organization_Id = POD.Destination_Organization_Id
393 AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
394 ORDER BY 9,1,2
395 for update;
396 --Bug 14795334 cursor with OU added
397
398 CURSOR SPC_NP_Curs IS
399 SELECT INV.Invoice_id Invoice_Id
400 , DIST.Invoice_Distribution_Id Invoice_Distribution_Id
401 , PAP.Org_Id Proj_Org_Id
402 , PAP.Project_Id Project_Id
403 , PAP.Segment1 Project_Number
404 , POD.Task_id Task_Id
405 , DIST.Accounting_Date Expenditure_Item_Date
406 , INV.Vendor_Id Vendor_Id
407 , INV.Created_By Created_By
408 , POD.Destination_Organization_Id Expenditure_Organization_Id
409 , POD.Org_Id Org_Id
410 , DIST.description Expenditure_Comment
411 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
412 , DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
413 , nvl( DIST.Accts_Pay_Code_Combination_Id
414 , INV.Accts_Pay_Code_Combination_Id)
415 Accts_Pay_Code_Combination_Id
416 , INV.Invoice_Currency_Code Invoice_Currency_Code
417 , INV.Exchange_Rate_Type Exchange_Rate_Type
418 , INV.Exchange_Date Exchange_Date
419 , INV.Exchange_Rate Exchange_Rate
420 , DIST.Amount Charge_Amount
421 , DIST.Base_Amount Base_Charge_Amount
422 , DIST.PO_Distribution_Id PO_Distribution_Id
423 , DIST.RCV_Transaction_Id RCV_Transaction_Id
424 , DIST.Line_Type_Lookup_Code Line_Type_Lookup_Code
425 FROM AP_Invoices_All INV,
426 (SELECT C.Invoice_Distribution_Id
427 , C.Invoice_Id
428 , P.Project_Id
429 , P.Task_Id
430 , C.Org_Id
431 , C.Accounting_Date
432 , C.Expenditure_Organization_Id
433 , C.description
434 , C.Pa_Quantity
435 , C.Dist_Code_Combination_Id
436 , C.Accts_Pay_Code_Combination_Id
437 , C.Amount
438 , C.Base_Amount
439 , NVL(P.PO_Distribution_Id,(SELECT PO_Distribution_Id FROM AP_Invoice_Distributions_all P1
440 WHERE P1.invoice_distribution_id = P.charge_applicable_to_dist_id)) PO_Distribution_Id -- bugfix 7482789
441 , C.RCV_Transaction_Id
442 , C.Line_Type_Lookup_Code
443 FROM AP_Invoice_Distributions_all C, AP_Invoice_Distributions_all P
444 WHERE C.LINE_TYPE_LOOKUP_CODE IN ('FREIGHT','MISCELLANEOUS','NONREC_TAX') -- bugfix 7482789
445 AND C.PA_ADDITION_FLAG in ('E', 'M', 'N')
446 AND C.POSTED_FLAG = 'Y'
447 AND (C.Accounting_Date between NVL(l_Start_Date, to_date('0001-01-01', 'YYYY-MM-DD')) and NVL(l_End_Date, to_date('9999-01-01', 'YYYY-MM-DD')))
448 AND C.charge_applicable_to_dist_id = P.invoice_distribution_id
449 AND (P.charge_applicable_to_dist_id IS NOT NULL OR C.LINE_TYPE_LOOKUP_CODE IN ('FREIGHT','MISCELLANEOUS')) -- bugfix 7482789
450 ) DIST,
451 PA_Projects_ALL PAP,
452 PJM_Org_Parameters POP,
453 PO_Distributions_All POD
454 WHERE INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
455 AND POD.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
456 AND DIST.Invoice_Id = INV.Invoice_Id
457 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
458 AND DIST.po_distribution_id = POD.po_distribution_id
459 AND INV.org_id = l_org_id
460 AND DIST.org_id = INV.org_id
461 AND PAP.org_id = INV.org_id
462 AND POP.Organization_Id = POD.Destination_Organization_Id
463 AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
464 ORDER BY 9,1,2
465 for update;
466
467
468 CURSOR IPV_WP_Curs IS
469 SELECT INV.Invoice_id Invoice_Id
470 , DIST.Invoice_Distribution_Id Invoice_Distribution_Id
471 , PAP.Org_Id Proj_Org_Id
472 , PAP.Project_Id Project_Id
473 , PAP.Segment1 Project_Number
474 , POD.Task_id Task_Id
475 , DIST.Accounting_Date Expenditure_Item_Date
476 , INV.Vendor_Id Vendor_Id
477 , INV.Created_By Created_By
478 , POD.Destination_Organization_Id Expenditure_Organization_Id
479 , POD.Org_Id Org_Id
480 , DIST.description Expenditure_Comment
481 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
482 , DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
483 , nvl( DIST.Accts_Pay_Code_Combination_Id
484 , INV.Accts_Pay_Code_Combination_Id)
485 Accts_Pay_Code_Combination_Id
486 , INV.Invoice_Currency_Code Invoice_Currency_Code
487 , INV.Exchange_Rate_Type Exchange_Rate_Type
488 , INV.Exchange_Date Exchange_Date
489 , INV.Exchange_Rate Exchange_Rate
490 , DIST.Amount Charge_Amount
491 , DIST.Base_Amount Base_Charge_Amount
492 , DIST.PO_Distribution_Id PO_Distribution_Id
493 , DIST.RCV_Transaction_Id RCV_Transaction_Id
494 , DIST.Line_Type_Lookup_Code Line_Type_Lookup_Code
495 FROM AP_Invoices_All INV,
496 (SELECT Invoice_Distribution_Id
497 , Invoice_Id
498 , Project_Id
499 , Task_id
500 , Accounting_Date
501 , Expenditure_Organization_Id
502 , description
503 , Pa_Quantity
504 , Dist_Code_Combination_Id
505 , Accts_Pay_Code_Combination_Id
506 , Amount
507 , Base_Amount
508 , PO_Distribution_Id
509 , RCV_Transaction_Id
510 , Line_Type_Lookup_Code
511 FROM AP_Invoice_Distributions_all
512 WHERE LINE_TYPE_LOOKUP_CODE IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV')
513 AND PA_ADDITION_FLAG in ('E', 'M', 'N')
514 AND POSTED_FLAG = 'Y'
515 ) DIST,
516 PA_Projects_ALL PAP,
517 PJM_Org_Parameters POP,
518 PO_Distributions_All POD
519 WHERE INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
520 AND POD.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
521 AND DIST.Invoice_Id = INV.Invoice_Id
522 AND (( l_Start_Date is null and l_End_Date is null)
523 OR ( l_Start_Date is not null and l_End_Date is not null
524 and DIST.Accounting_Date between l_Start_Date and l_End_Date)
525 OR ( l_Start_Date is not null and l_End_Date is null
526 and DIST.Accounting_Date >= l_Start_Date )
527 OR ( l_Start_Date is null and l_End_Date is not null
528 and DIST.Accounting_Date <= L_End_Date ))
529 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
530 AND DIST.po_distribution_id = POD.po_distribution_id
531 AND POP.Organization_Id = POD.Destination_Organization_Id
532 AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
533 AND PAP.Project_Id = X_Project_ID
534 ORDER BY 9,1,2
535 for update;
536
537 CURSOR SPC_WP_Curs IS
538 SELECT INV.Invoice_id Invoice_Id
539 , DIST.Invoice_Distribution_Id Invoice_Distribution_Id
540 , PAP.Org_Id Proj_Org_Id
541 , PAP.Project_Id Project_Id
542 , PAP.Segment1 Project_Number
543 , POD.Task_id Task_Id
544 , DIST.Accounting_Date Expenditure_Item_Date
545 , INV.Vendor_Id Vendor_Id
546 , INV.Created_By Created_By
547 , POD.Destination_Organization_Id Expenditure_Organization_Id
548 , POD.Org_Id Org_Id
549 , DIST.description Expenditure_Comment
550 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
551 , DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
552 , nvl( DIST.Accts_Pay_Code_Combination_Id
553 , INV.Accts_Pay_Code_Combination_Id)
554 Accts_Pay_Code_Combination_Id
555 , INV.Invoice_Currency_Code Invoice_Currency_Code
556 , INV.Exchange_Rate_Type Exchange_Rate_Type
557 , INV.Exchange_Date Exchange_Date
558 , INV.Exchange_Rate Exchange_Rate
559 , DIST.Amount Charge_Amount
560 , DIST.Base_Amount Base_Charge_Amount
561 , DIST.PO_Distribution_Id PO_Distribution_Id
562 , DIST.RCV_Transaction_Id RCV_Transaction_Id
563 , DIST.Line_Type_Lookup_Code Line_Type_Lookup_Code
564 FROM AP_Invoices_All INV,
565 (SELECT C.Invoice_Distribution_Id
566 , C.Invoice_Id
567 , P.Project_Id
568 , P.Task_Id
569 , C.Accounting_Date
570 , C.Expenditure_Organization_Id
571 , C.description
572 , C.Pa_Quantity
573 , C.Dist_Code_Combination_Id
574 , C.Accts_Pay_Code_Combination_Id
575 , C.Amount
576 , C.Base_Amount
577 , NVL(P.PO_Distribution_Id,(SELECT PO_Distribution_Id FROM AP_Invoice_Distributions_all P1
578 WHERE P1.invoice_distribution_id = P.charge_applicable_to_dist_id)) PO_Distribution_Id -- bugfix 7482789
579 , C.RCV_Transaction_Id
580 , C.Line_Type_Lookup_Code
581 FROM AP_Invoice_Distributions_all C, AP_Invoice_Distributions_all P
582 WHERE C.LINE_TYPE_LOOKUP_CODE IN ('FREIGHT','MISCELLANEOUS','NONREC_TAX') -- bugfix 7482789
583 AND C.PA_ADDITION_FLAG in ('E', 'M', 'N')
584 AND C.POSTED_FLAG = 'Y'
585 AND C.charge_applicable_to_dist_id = P.invoice_distribution_id
586 AND (P.charge_applicable_to_dist_id IS NOT NULL OR C.LINE_TYPE_LOOKUP_CODE IN ('FREIGHT','MISCELLANEOUS')) -- bugfix 7482789
587 ) DIST,
588 PA_Projects_ALL PAP,
589 PJM_Org_Parameters POP,
590 PO_Distributions_All POD
591 WHERE INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
592 AND POD.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
593 AND DIST.Invoice_Id = INV.Invoice_Id
594 AND (( l_Start_Date is null and l_End_Date is null)
595 OR ( l_Start_Date is not null and l_End_Date is not null
596 and DIST.Accounting_Date between l_Start_Date and l_End_Date)
597 OR ( l_Start_Date is not null and l_End_Date is null
598 and DIST.Accounting_Date >= l_Start_Date )
599 OR ( l_Start_Date is null and l_End_Date is not null
600 and DIST.Accounting_Date <= L_End_Date ))
601 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
602 AND DIST.po_distribution_id = POD.po_distribution_id
603 AND POP.Organization_Id = POD.Destination_Organization_Id
604 AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
605 AND PAP.Project_Id = X_Project_ID
606 ORDER BY 9,1,2
607 for update;
608
609 InvRec IPV_WP_Curs%ROWTYPE;
610
611 CURSOR Po_Data ( P_Distribution_ID NUMBER ) IS
612 SELECT POD.Destination_Type_Code
613 , POL.Item_ID
614 , POD.Bom_Resource_ID Wip_Resource_Id
615 , POD.Destination_Organization_ID
616 FROM po_distributions_all pod
617 , po_lines_all pol
618 WHERE POD.PO_Distribution_ID = P_Distribution_ID
619 AND POL.Po_line_ID = POD.Po_Line_ID;
620
621 PoRec Po_Data%ROWTYPE;
622 l_dummy NUMBER;
623
624
625 BEGIN
626
627 --Bug 14795334 Deriving OU id
628 l_org_id := NVL(FND_PROFILE.VALUE('ORG_ID'), -99);
629 l_curr_invoice_id := -1;
630 l_first_invoice := TRUE;
631 l_progress := 0;
632 if (X_trx_status_code is NULL) then
633 l_trx_status_code := 'P';
634 else
635 l_trx_status_code := X_trx_status_code;
636 end if;
637 l_uom := 'DOLLARS'; -- bug 4145856
638
639
640 fnd_message.set_name('PJM','CONC-APINV IPV Transfer');
641 PJM_CONC.put_line(fnd_message.get || ' ...');
642 PJM_CONC.new_line(1);
643
644 PJM_CONC.put_line('[PROJECT_ID] = ' || X_Project_Id);
645 PJM_CONC.put_line('[START_DATE] = ' || X_Start_Date);
646 PJM_CONC.put_line('[END_DATE] = ' || X_End_Date);
647 PJM_CONC.put_line('[SUBMIT_TRX_IMPORT] = ' || X_Submit_Trx_Import);
648 PJM_CONC.put_line('[EXP_TYPE_BSD_ON_PUR_CTGY] = ' || X_Exp_Type_Bsd_On_Pur_Ctgy); -- bug fix 12835119 (FP of 7372638)
649
650 l_request_id := fnd_global.conc_request_id;
651 l_user_id := fnd_global.user_id;
652 l_Start_Date := fnd_date.canonical_to_date(X_Start_Date);
653 l_End_Date := fnd_date.canonical_to_date(X_End_Date);
654
655 PJM_CONC.put_line('[REQUEST_ID] = ' || l_request_id);
656 PJM_CONC.new_line(1);
657
658 l_IPV_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV IPV');
659 l_ERV_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV ERV');
660 l_Freight_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV FREIGHT');
661 l_Tax_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV TAX');
662 l_Misc_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV MISC');
663 l_Batch_Name := Batch_Name;
664
665 PJM_CONC.put_line('Batch_Name = ' || l_batch_name);
666 PJM_CONC.new_line(1);
667
668 ----------------------------------------------------------------------
669 -- Loop for transfering Variances from Invoice_Distribution_All to
670 -- PA_Transaction_Interface_All
671 ----------------------------------------------------------------------
672
673 fnd_message.set_name('PJM','CONC-APINV Start Loop');
674 PJM_CONC.put_line(fnd_message.get || ' ...');
675
676 Timestamp;
677
678 if (x_project_id is not null) then
679
680 OPEN IPV_WP_Curs; -- Process IPV, ERV, and Tax Variances first
681
682 LOOP -- Start process data with project info
683
684 l_progress := 10;
685
686 FETCH IPV_WP_Curs INTO InvRec;
687 EXIT WHEN IPV_WP_Curs%NOTFOUND;
688
689 l_progress := 20;
690
691 ----------------------------------------------------------------------
692 -- Get Accounting Currency Code
693 ----------------------------------------------------------------------
694 if InvRec.Line_Type_Lookup_Code in ('ERV','TERV')
695 then
696 select ap.base_currency_code
697 into l_base_currency_code
698 from ap_system_parameters_all ap
699 where ap.org_id = InvRec.Org_Id;
700 else
701 l_base_currency_code := InvRec.Invoice_Currency_Code;
702 end if;
703
704 --------------------------------------------------------------------
705 -- Get PO Value
706 ---------------------------------------------------------------------
707
708 OPEN Po_Data ( InvRec.Po_Distribution_ID );
709 FETCH Po_Data INTO PoRec;
710 CLOSE Po_Data;
711
712
713 IF ( InvRec.Task_Id IS NOT NULL ) THEN
714 l_Task_Id := InvRec.Task_Id;
715 ELSE
716 l_Task_Id := Assign_Task( InvRec.PO_Distribution_Id
717 , PoRec.Destination_Type_Code
718 , InvRec.Project_Id );
719 END IF;
720
721 PJM_CONC.put_line(' invoice_id ............ '||InvRec.Invoice_Id);
722 PJM_CONC.put_line(' line_num .............. '||
723 InvRec.Invoice_Distribution_Id);
724 PJM_CONC.put_line(' project_id ............ '||InvRec.Project_ID);
725 PJM_CONC.put_line(' task_id ............... '||l_Task_Id);
726 PJM_CONC.put_line(' expenditure_org_id .... '||
727 InvRec.Expenditure_Organization_ID);
728
729 Timestamp;
730
731
732 l_curr_invoice_id := InvRec.Invoice_Id;
733 l_first_invoice := FALSE;
734
735 ----------------------------------------------------------------------
736 -- Get Expenditure Type
737 ----------------------------------------------------------------------
738
739 l_progress := 30;
740
741 l_exp_type := NULL; -- bug fix 12835119(FP of 7372638)
742 IF (X_Exp_Type_Bsd_On_Pur_Ctgy = 'Y') THEN
743
744 -- get wip entity id ,po header id for the PO.
745 SELECT wip_entity_id,po_line_id
746 INTO l_wip_entity_id,l_po_line_id
747 FROM PO_DISTRIBUTIONS_ALL
748 WHERE po_distribution_id = InvRec.PO_Distribution_Id;
749
750 IF (l_wip_entity_id is not NULL and l_po_line_id is not NULL) THEN
751 Begin
752 select distinct category_id
753 into l_po_category_id
754 from WIP_EAM_DIRECT_ITEMS_V
755 where work_order_number = l_wip_entity_id
756 and po_line_id = l_po_line_id;
757 EXCEPTION
758 when others then
759 NULL;
760 END;
761 END IF;
762 PJM_CONC.put_line('Category id for Direct item'||l_po_category_id);
763
764 IF l_po_category_id is not NULL then
765 l_exp_type := get_ExpType_for_DirectItem(InvRec.Invoice_Id,l_po_category_id);
766 PJM_CONC.put_line('Exp type for Direct item'||l_exp_type);
767 END IF;
768 END IF;
769 --- bug fix 12835119(FP of 7372638)
770
771 IF (l_exp_type is NULL) THEN --- bug fix 12835119(FP of 7372638)
772
773 l_exp_type := Get_Charges_Expenditure_Type
774 ( invrec.line_type_lookup_code
775 , invrec.project_id
776 , invrec.expenditure_organization_id );
777
778 END IF; --- bug fix 12835119(FP of 7372638)
779 PJM_CONC.put_line(' Invoice line type is .. '||invrec.line_type_lookup_code);
780 PJM_CONC.put_line(' Charge amount ............ '||
781 InvRec.charge_amount);
782 PJM_CONC.put_line(' Charge expenditure_type .. '||l_exp_type);
783 PJM_CONC.put_line(' expenditure_comment ... '||
784 InvRec.Expenditure_Comment);
785 PJM_CONC.new_line(1);
786
787
788
789 /* Bug 13853188 The expenditure organization is not active */
790 Begin
791 SELECT BD.pa_expenditure_org_id
792 INTO l_exp_org_id
793 FROM PO_DISTRIBUTIONS_ALL POD,
794 wip_operations WO ,
795 bom_departments BD
796 WHERE POD.wip_entity_id = WO.wip_entity_id
797 AND NVL(wo.repetitive_schedule_id, -99999) = NVL(POD.wip_repetitive_schedule_id, -99999)
798 AND POD.wip_operation_seq_num = WO.OPERATION_SEQ_NUM
799 AND BD.department_id = WO.department_id
800 AND BD.organization_id = WO.organization_id
801 AND pod.po_distribution_id = InvRec.PO_Distribution_Id;
802
803 PJM_CONC.put_line('pa_expenditure_org_id .....'||l_exp_org_id);
804 PJM_CONC.put_line('pa_expenditure_org_id .....'||InvRec.PO_Distribution_Id);
805
806 EXCEPTION
807 when others then
808 PJM_CONC.put_line('Exp organization is not specified on Department. Will use the Inv organization. ');
809 END;
810 /* Bug Fix ends Bug 13853188 The expenditure organization is not active */
811 ---------------------------------------------------------------------
812 -- Set Expenditure Comment
813 ---------------------------------------------------------------------
814 l_progress := 40;
815
816 select decode(invrec.line_type_lookup_code, 'IPV', l_IPV_Exp_Comment,
817 'ERV', l_ERV_Exp_Comment,
818 'FREIGHT', l_Freight_Exp_Comment,
819 'TIPV', l_Tax_Exp_Comment,
820 'TERV', l_ERV_Exp_Comment,
821 'TRV', l_Tax_Exp_Comment,
822 'MISCELLANEOUS', l_Misc_Exp_Comment, null)
823 into l_exp_comment
824 from dual;
825
826
827 ----------------------------------------------------------------------
828 -- Getting Receipt Num from RCV_TRANSACTION_ID if exists
829 ----------------------------------------------------------------------
830
831 l_progress := 50;
832
833 if ( InvRec.RCV_Transaction_Id is not null ) then
834
835 SELECT rsh.receipt_num
836 INTO l_receipt_num
837 FROM rcv_shipment_headers rsh
838 , rcv_transactions rt
839 WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
840 AND rsh.shipment_header_id = rt.shipment_header_id;
841
842 end if;
843
844 ----------------------------------------------------------------------
845 -- Converting System RATE_TYPE to User RATE_TYPE if exists
846 ----------------------------------------------------------------------
847
848 l_progress := 60;
849
850 if ( InvRec.Exchange_Rate_Type is not null ) then
851
852 SELECT User_Conversion_Type
853 INTO l_User_Conv_Type
854 FROM gl_daily_conversion_types
855 WHERE conversion_type = InvRec.Exchange_Rate_Type;
856
857 else -- bug 4219497
858 l_user_conv_type := null;
859
860 end if;
861
862 ----------------------------------------------------------------------
863 -- Insert into PA_TRANSACTION_INTERFACES table
864 ----------------------------------------------------------------------
865
866 fnd_message.set_name('PJM','CONC-APINV Insert');
867 PJM_CONC.put_line('... ' || fnd_message.get);
868
869 if not ( InvRec.Dist_Code_Combination_Id is not null AND
870 nvl(nvl(InvRec.Base_Charge_Amount,
871 InvRec.Charge_Amount) , 0) <> 0 ) then
872
873 PJM_CONC.put_line('...... Charge amount not available, skipping...');
874 -- Mark skipped record to 'G'
875 UPDATE AP_Invoice_Distributions_all
876 SET pa_addition_flag = 'G'
877 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
878 from ap_invoice_distributions_all
879 where Invoice_Id = InvRec.Invoice_Id
880 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
881
882 elsif ( l_Exp_Type is null) then
883
884 PJM_CONC.put_line('...... Inv Org not setup to transfer charges, skipping...');
885 -- Mark skipped record to 'G'
886 UPDATE AP_Invoice_Distributions_all
887 SET pa_addition_flag = 'G'
888 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
889 from ap_invoice_distributions_all
890 where Invoice_Id = InvRec.Invoice_Id
891 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
892
893 else
894
895 BEGIN
896
897 l_progress := 70;
898
899 ---------------------------------------------------------------------
900 -- For Blue Print org, setting Transaction Source according to
901 -- destination_type_code, pa_posting_flag and pa_autoaccounting_flag
902 ---------------------------------------------------------------------
903
904 select NVL(pa_posting_flag,'N'),
905 NVL(pa_autoaccounting_flag,'N')
906 into l_blue_print_enabled_flag,
907 l_autoaccounting_flag
908 from pjm_org_parameters
909 where organization_id = InvRec.Expenditure_Organization_Id;
910
911 l_system_reference := NULL;
912 if PoRec.destination_type_code = 'INVENTORY' then -- bug 4184314
913 l_linkage := 'INV';
914 -- Bug 10048051 starts
915 If l_blue_print_enabled_flag = 'Y' then
916
917 --If l_autoaccounting_flag = 'Y' then
918 /* BP and autoaccounting */
919 --l_transaction_source := 'PJM_CSTBP_INV_NO_ACCOUNTS';
920 --else
921 /* BP and no autoaccounting -- Send Account to PA */
922 --l_transaction_source := 'PJM_CSTBP_INV_ACCOUNTS';
923
924 --end if; /* end of check for auto accounting */
925 l_transaction_source := 'PJM_CSTBP_INV_GL_ACCOUNTS';
926 -- Bug 10048051 ends
927 else
928 l_transaction_source := 'Inventory';
929 l_system_reference := 'PJM';
930 end if;
931
932 elsif PoRec.destination_type_code = 'SHOP FLOOR' then
933 l_linkage := 'WIP';
934 If l_blue_print_enabled_flag = 'Y' then
935 If l_autoaccounting_flag = 'Y' then
936 /* BP and autoaccounting */
937 l_transaction_source := 'PJM_CSTBP_WIP_NO_ACCOUNTS';
938 else
939 /* BP and no autoaccounting -- Send Account to PA */
940 l_transaction_source := 'PJM_CSTBP_WIP_ACCOUNTS';
941
942 end if; /* end of check for auto accounting */
943 else
944 l_transaction_source := 'Work In Process';
945 l_system_reference := 'PJM';
946 end if;
947
948 END IF; /* check for BP org */
949
950
951
952 -------------------------------------------------
953 -- Set the denom amount for bug 4169096
954 -------------------------------------------------
955
956 if InvRec.Line_Type_Lookup_Code in ('ERV','TERV')
957 then
958 l_denom_raw_cost := InvRec.Base_Charge_Amount;
959 l_acct_raw_cost := InvRec.Base_Charge_Amount;
960 if l_blue_print_enabled_flag = 'Y'
961 then
962 l_denom_burdened_cost := NULL;
963 l_acct_burdened_cost := NULL;
964 else
965 l_denom_burdened_cost := InvRec.Base_Charge_Amount;
966 l_acct_burdened_cost := InvRec.Base_Charge_Amount;
967 end if;
968 else
969 l_denom_raw_cost := InvRec.Charge_Amount;
970 select nvl(InvRec.Base_Charge_Amount, InvRec.Charge_Amount) into l_acct_raw_cost from dual;
971 if l_blue_print_enabled_flag = 'Y'
972 then
973 l_denom_burdened_cost := NULL;
974 l_acct_burdened_cost := NULL;
975 else
976 l_denom_burdened_cost := InvRec.Charge_Amount;
977 l_acct_burdened_cost := l_acct_raw_cost;
978 end if;
979 end if;
980
981
982 -- Get Week Ending of Expenditure Item Date
983 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
984 into l_week_ending_day_index
985 FROM pa_implementations_all WHERE org_id = InvRec.Proj_Org_Id;
986
987 select to_char(to_date('01-01-1950','DD-MM-YYYY') + l_week_ending_day_index - 1, 'Day')
988 into l_week_ending_day from dual;
989
990 select next_day( trunc(InvRec.Expenditure_Item_Date)-1, l_week_ending_day )
991 into l_week_ending
992 from dual;
993
994 PJM_CONC.put_line('...... Processing IPV, ERV, Tax Variances');
995
996 -- Insert for all the charges
997
998 INSERT INTO pa_transaction_interface_all
999 (transaction_source,
1000 batch_name,
1001 expenditure_ending_date,
1002 employee_number,
1003 organization_name,
1004 expenditure_item_date,
1005 project_number,
1006 task_number,
1007 expenditure_type,
1008 quantity,
1009 expenditure_comment,
1010 orig_transaction_reference,
1011 unmatched_negative_txn_flag,
1012 dr_code_combination_id,
1013 cr_code_combination_id,
1014 orig_exp_txn_reference1,
1015 orig_exp_txn_reference2,
1016 orig_exp_txn_reference3,
1017 gl_date,
1018 system_linkage,
1019 transaction_status_code,
1020 denom_currency_code,
1021 denom_raw_cost,
1022 denom_burdened_cost,
1023 acct_rate_date,
1024 acct_rate_type,
1025 acct_exchange_rate,
1026 acct_raw_cost,
1027 acct_burdened_cost,
1028 creation_date,
1029 created_by,
1030 last_update_date,
1031 last_updated_by,
1032 inventory_item_id,
1033 unit_of_measure,
1034 wip_resource_id,
1035 org_id,
1036 cdl_system_reference4
1037 )
1038 SELECT
1039 l_transaction_source
1040 , l_Batch_Name
1041 , l_week_ending --pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
1042 , NULL
1043 , ORG.Name
1044 , InvRec.Expenditure_Item_Date
1045 , InvRec.Project_Number
1046 , TASK.Task_Number
1047 , l_Exp_Type
1048 , InvRec.PA_Quantity
1049 , NVL( InvRec.Expenditure_Comment , l_Exp_Comment )
1050 , DIST.Invoice_Distribution_Id
1051 , 'Y'
1052 , InvRec.Dist_Code_Combination_Id
1053 , InvRec.Accts_Pay_Code_Combination_Id
1054 , InvRec.PO_Distribution_Id
1055 , InvRec.RCV_Transaction_Id
1056 , l_receipt_num
1057 , DIST.Accounting_Date
1058 , l_linkage
1059 , l_trx_status_code
1060 , l_base_currency_code /* denom_currency_code */
1061 , l_denom_raw_cost /* denom_raw_cost */
1062 , l_denom_burdened_cost /* denom_burdened_cost */
1063 , InvRec.Exchange_Date /* acct_rate_date */
1064 , l_User_Conv_Type /* acct_rate_type */
1065 , InvRec.Exchange_Rate /* acct_exchange_rate */
1066 , l_acct_raw_cost /* acct_raw_cost */
1067 , l_acct_burdened_cost /* acct_burdened_cost */
1068 , SYSDATE
1069 , l_user_id
1070 , SYSDATE
1071 , l_user_id
1072 , PoRec.item_id
1073 , l_uom
1074 , PoRec.wip_resource_id
1075 , InvRec.Org_Id
1076 , l_system_reference
1077 FROM
1078 AP_Invoice_Distributions_all DIST
1079 , PA_Tasks TASK
1080 , HR_Organization_Units ORG
1081 WHERE
1082 DIST.Invoice_Id = InvRec.Invoice_Id
1083 AND DIST.Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id
1084 AND ORG.Organization_Id = NVL(l_exp_org_id,InvRec.Expenditure_Organization_Id) /* Bug 13853188 */
1085 AND TASK.Task_Id = l_Task_Id;
1086
1087
1088 ----------------------------------------------------------------------
1089 -- Update pa_addition_flag to 'Y' for successful invoice distributions
1090 ----------------------------------------------------------------------
1091
1092 fnd_message.set_name('PJM','CONC-APINV Flag Comp');
1093 PJM_CONC.put_line('... ' || fnd_message.get);
1094 PJM_CONC.new_line(1);
1095
1096 l_progress := 80;
1097
1098 UPDATE AP_Invoice_Distributions_all
1099 SET Pa_Addition_Flag = 'Y',
1100 Request_Id = l_request_id
1101 WHERE Invoice_Id = InvRec.Invoice_Id
1102 AND Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id;
1103
1104 EXCEPTION
1105 WHEN NO_DATA_FOUND THEN
1106 NULL;
1107 WHEN DUP_VAL_ON_INDEX THEN
1108 NULL;
1109 END;
1110
1111 end if;
1112
1113 END LOOP; -- End process data with project info
1114
1115 CLOSE IPV_WP_Curs;
1116
1117 OPEN SPC_WP_Curs; -- Process Freight and Misc charges
1118
1119 LOOP -- Start process special charges with project info
1120
1121 l_progress := 81;
1122
1123 FETCH SPC_WP_Curs INTO InvRec;
1124 EXIT WHEN SPC_WP_Curs%NOTFOUND;
1125
1126 l_progress := 82;
1127
1128 ----------------------------------------------------------------------
1129 -- Get Accounting Currency Code
1130 ----------------------------------------------------------------------
1131 l_base_currency_code := InvRec.Invoice_Currency_Code;
1132
1133
1134 --------------------------------------------------------------------
1135 -- Get PO Value
1136 ---------------------------------------------------------------------
1137
1138 OPEN Po_Data ( InvRec.Po_Distribution_ID );
1139 FETCH Po_Data INTO PoRec;
1140 CLOSE Po_Data;
1141
1142
1143 IF ( InvRec.Task_Id IS NOT NULL ) THEN
1144 l_Task_Id := InvRec.Task_Id;
1145 ELSE
1146 l_Task_Id := Assign_Task( InvRec.PO_Distribution_Id
1147 , PoRec.Destination_Type_Code
1148 , InvRec.Project_Id );
1149 END IF;
1150
1151 PJM_CONC.put_line(' invoice_id ............ '||InvRec.Invoice_Id);
1152 PJM_CONC.put_line(' line_num .............. '||
1153 InvRec.Invoice_Distribution_Id);
1154 PJM_CONC.put_line(' project_id ............ '||InvRec.Project_ID);
1155 PJM_CONC.put_line(' task_id ............... '||l_Task_Id);
1156 PJM_CONC.put_line(' expenditure_org_id .... '||
1157 InvRec.Expenditure_Organization_ID);
1158
1159 Timestamp;
1160
1161
1162 l_curr_invoice_id := InvRec.Invoice_Id;
1163 l_first_invoice := FALSE;
1164
1165 ----------------------------------------------------------------------
1166 -- Get Expenditure Type
1167 ----------------------------------------------------------------------
1168
1169 l_progress := 83;
1170
1171 l_exp_type := NULL; -- bug fix 12835119(FP of 7372638)
1172 IF (X_Exp_Type_Bsd_On_Pur_Ctgy = 'Y') THEN
1173
1174 -- get wip entity id ,po header id for the PO.
1175 SELECT wip_entity_id,po_line_id
1176 INTO l_wip_entity_id,l_po_line_id
1177 FROM PO_DISTRIBUTIONS_ALL
1178 WHERE po_distribution_id = InvRec.PO_Distribution_Id;
1179
1180 IF (l_wip_entity_id is not NULL and l_po_line_id is not NULL) THEN
1181 Begin
1182 select distinct category_id
1183 into l_po_category_id
1184 from WIP_EAM_DIRECT_ITEMS_V
1185 where work_order_number = l_wip_entity_id
1186 and po_line_id = l_po_line_id;
1187 EXCEPTION
1188 when others then
1189 NULL;
1190 END;
1191 END IF;
1192 PJM_CONC.put_line('Category id for Direct item'||l_po_category_id);
1193
1194 IF l_po_category_id is not NULL then
1195 l_exp_type := get_ExpType_for_DirectItem(InvRec.Invoice_Id,l_po_category_id);
1196 PJM_CONC.put_line('Exp type for Direct item'||l_exp_type);
1197 END IF;
1198 END IF;
1199 --- bug fix 12835119(FP of 7372638)
1200
1201 IF (l_exp_type is NULL) THEN --- bug fix 12835119(FP of 7372638)
1202
1203
1204 l_exp_type := Get_Charges_Expenditure_Type
1205 ( invrec.line_type_lookup_code
1206 , invrec.project_id
1207 , invrec.expenditure_organization_id );
1208
1209 END IF; --- bug fix 12835119(FP of 7372638)
1210
1211 PJM_CONC.put_line(' Invoice line type is .. '||invrec.line_type_lookup_code);
1212 PJM_CONC.put_line(' Charge amount ............ '||
1213 InvRec.charge_amount);
1214 PJM_CONC.put_line(' Charge expenditure_type .. '||l_exp_type);
1215 PJM_CONC.put_line(' expenditure_comment ... '||
1216 InvRec.Expenditure_Comment);
1217 PJM_CONC.new_line(1);
1218
1219
1220 /* Bug 13853188 The expenditure organization is not active */
1221 Begin
1222 SELECT BD.pa_expenditure_org_id
1223 INTO l_exp_org_id
1224 FROM PO_DISTRIBUTIONS_ALL POD,
1225 wip_operations WO ,
1226 bom_departments BD
1227 WHERE POD.wip_entity_id = WO.wip_entity_id
1228 AND NVL(wo.repetitive_schedule_id, -99999) = NVL(POD.wip_repetitive_schedule_id, -99999)
1229 AND POD.wip_operation_seq_num = WO.OPERATION_SEQ_NUM
1230 AND BD.department_id = WO.department_id
1231 AND BD.organization_id = WO.organization_id
1232 AND pod.po_distribution_id = InvRec.PO_Distribution_Id;
1233
1234 PJM_CONC.put_line('pa_expenditure_org_id .....'||l_exp_org_id);
1235 PJM_CONC.put_line('pa_expenditure_org_id .....'||InvRec.PO_Distribution_Id);
1236
1237 EXCEPTION
1238 when others then
1239 PJM_CONC.put_line('Exp organization is not specified on Department. Will use the Inv organization. ');
1240 END;
1241 /* Bug Fix ends Bug 13853188 The expenditure organization is not active */
1242
1243
1244 ---------------------------------------------------------------------
1245 -- Set Expenditure Comment
1246 ---------------------------------------------------------------------
1247 l_progress := 84;
1248
1249 select decode(invrec.line_type_lookup_code, 'IPV', l_IPV_Exp_Comment,
1250 'ERV', l_ERV_Exp_Comment,
1251 'FREIGHT', l_Freight_Exp_Comment,
1252 'TIPV', l_Tax_Exp_Comment,
1253 'TERV', l_ERV_Exp_Comment,
1254 'TRV', l_Tax_Exp_Comment,
1255 'MISCELLANEOUS', l_Misc_Exp_Comment,
1256 'NONREC_TAX', l_Tax_Exp_Comment,null) --bugfix 7482789
1257 into l_exp_comment
1258 from dual;
1259
1260
1261 ----------------------------------------------------------------------
1262 -- Getting Receipt Num from RCV_TRANSACTION_ID if exists
1263 ----------------------------------------------------------------------
1264
1265 l_progress := 85;
1266
1267 if ( InvRec.RCV_Transaction_Id is not null ) then
1268
1269 SELECT rsh.receipt_num
1270 INTO l_receipt_num
1271 FROM rcv_shipment_headers rsh
1272 , rcv_transactions rt
1273 WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
1274 AND rsh.shipment_header_id = rt.shipment_header_id;
1275
1276 end if;
1277
1278 ----------------------------------------------------------------------
1279 -- Converting System RATE_TYPE to User RATE_TYPE if exists
1280 ----------------------------------------------------------------------
1281
1282 l_progress := 86;
1283
1284 if ( InvRec.Exchange_Rate_Type is not null ) then
1285
1286 SELECT User_Conversion_Type
1287 INTO l_User_Conv_Type
1288 FROM gl_daily_conversion_types
1289 WHERE conversion_type = InvRec.Exchange_Rate_Type;
1290
1291 else -- bug 4219497
1292 l_user_conv_type := null;
1293
1294 end if;
1295
1296 ----------------------------------------------------------------------
1297 -- Insert into PA_TRANSACTION_INTERFACES table
1298 ----------------------------------------------------------------------
1299
1300 fnd_message.set_name('PJM','CONC-APINV Insert');
1301 PJM_CONC.put_line('... ' || fnd_message.get);
1302
1303 if not ( InvRec.Dist_Code_Combination_Id is not null AND
1304 nvl(nvl(InvRec.Base_Charge_Amount,
1305 InvRec.Charge_Amount) , 0) <> 0 ) then
1306
1307 PJM_CONC.put_line('...... Charge amount not available, skipping...');
1308 -- Mark skipped record to 'G'
1309 UPDATE AP_Invoice_Distributions_all
1310 SET pa_addition_flag = 'G'
1311 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
1312 from ap_invoice_distributions_all
1313 where Invoice_Id = InvRec.Invoice_Id
1314 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
1315
1316 elsif ( l_Exp_Type is null) then
1317
1318 PJM_CONC.put_line('...... Inv Org not setup to transfer charges, skipping...');
1319 -- Mark skipped record to 'G'
1320 UPDATE AP_Invoice_Distributions_all
1321 SET pa_addition_flag = 'G'
1322 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
1323 from ap_invoice_distributions_all
1324 where Invoice_Id = InvRec.Invoice_Id
1325 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
1326
1327 else
1328
1329 BEGIN
1330
1331 l_progress := 87;
1332
1333 ---------------------------------------------------------------------
1334 -- For Blue Print org, setting Transaction Source according to
1335 -- destination_type_code, pa_posting_flag and pa_autoaccounting_flag
1336 ---------------------------------------------------------------------
1337
1338 select NVL(pa_posting_flag,'N'),
1339 NVL(pa_autoaccounting_flag,'N')
1340 into l_blue_print_enabled_flag,
1341 l_autoaccounting_flag
1342 from pjm_org_parameters
1343 where organization_id = InvRec.Expenditure_Organization_Id;
1344
1345 l_system_reference := NULL;
1346 if PoRec.destination_type_code = 'INVENTORY' then -- bug 4184314
1347 l_linkage := 'INV';
1348 -- Bug 10048051 starts
1349 If l_blue_print_enabled_flag = 'Y' then
1350
1351 --If l_autoaccounting_flag = 'Y' then
1352 /* BP and autoaccounting */
1353 --l_transaction_source := 'PJM_CSTBP_INV_NO_ACCOUNTS';
1354 --else
1355 /* BP and no autoaccounting -- Send Account to PA */
1356 --l_transaction_source := 'PJM_CSTBP_INV_ACCOUNTS';
1357
1358 --end if; /* end of check for auto accounting */
1359 l_transaction_source := 'PJM_CSTBP_INV_GL_ACCOUNTS';
1360 -- Bug 10048051 ends
1361 else
1362 l_transaction_source := 'Inventory';
1363 l_system_reference := 'PJM';
1364 end if;
1365
1366 elsif PoRec.destination_type_code = 'SHOP FLOOR' then
1367 l_linkage := 'WIP';
1368 If l_blue_print_enabled_flag = 'Y' then
1369 If l_autoaccounting_flag = 'Y' then
1370 /* BP and autoaccounting */
1371 l_transaction_source := 'PJM_CSTBP_WIP_NO_ACCOUNTS';
1372 else
1373 /* BP and no autoaccounting -- Send Account to PA */
1374 l_transaction_source := 'PJM_CSTBP_WIP_ACCOUNTS';
1375
1376 end if; /* end of check for auto accounting */
1377 else
1378 l_transaction_source := 'Work In Process';
1379 l_system_reference := 'PJM';
1380 end if;
1381
1382 END IF; /* check for BP org */
1383
1384
1385 -------------------------------------------------
1386 -- Set the denom amount for bug 4169096
1387 -------------------------------------------------
1388
1389 if InvRec.Line_Type_Lookup_Code in ('ERV','TERV')
1390 then
1391 l_denom_raw_cost := InvRec.Base_Charge_Amount;
1392 l_acct_raw_cost := InvRec.Base_Charge_Amount;
1393 if l_blue_print_enabled_flag = 'Y'
1394 then
1395 l_denom_burdened_cost := NULL;
1396 l_acct_burdened_cost := NULL;
1397 else
1398 l_denom_burdened_cost := InvRec.Base_Charge_Amount;
1399 l_acct_burdened_cost := InvRec.Base_Charge_Amount;
1400 end if;
1401 else
1402 l_denom_raw_cost := InvRec.Charge_Amount;
1403 select nvl(InvRec.Base_Charge_Amount, InvRec.Charge_Amount) into l_acct_raw_cost from dual;
1404 if l_blue_print_enabled_flag = 'Y'
1405 then
1406 l_denom_burdened_cost := NULL;
1407 l_acct_burdened_cost := NULL;
1408 else
1409 l_denom_burdened_cost := InvRec.Charge_Amount;
1410 l_acct_burdened_cost := l_acct_raw_cost;
1411 end if;
1412 end if;
1413
1414
1415 -- Get Week Ending of Expenditure Item Date
1416 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
1417 into l_week_ending_day_index
1418 FROM pa_implementations_all WHERE org_id = InvRec.Proj_Org_Id;
1419
1420 select to_char(to_date('01-01-1950','DD-MM-YYYY') + l_week_ending_day_index - 1, 'Day')
1421 into l_week_ending_day from dual;
1422
1423 select next_day( trunc(InvRec.Expenditure_Item_Date)-1, l_week_ending_day )
1424 into l_week_ending
1425 from dual;
1426
1427 PJM_CONC.put_line('...... Processing Special Charge');
1428
1429 -- Insert for all the charges
1430
1431 INSERT INTO pa_transaction_interface_all
1432 (transaction_source,
1433 batch_name,
1434 expenditure_ending_date,
1435 employee_number,
1436 organization_name,
1437 expenditure_item_date,
1438 project_number,
1439 task_number,
1440 expenditure_type,
1441 quantity,
1442 expenditure_comment,
1443 orig_transaction_reference,
1444 unmatched_negative_txn_flag,
1445 dr_code_combination_id,
1446 cr_code_combination_id,
1447 orig_exp_txn_reference1,
1448 orig_exp_txn_reference2,
1449 orig_exp_txn_reference3,
1450 gl_date,
1451 system_linkage,
1452 transaction_status_code,
1453 denom_currency_code,
1454 denom_raw_cost,
1455 denom_burdened_cost,
1456 acct_rate_date,
1457 acct_rate_type,
1458 acct_exchange_rate,
1459 acct_raw_cost,
1460 acct_burdened_cost,
1461 creation_date,
1462 created_by,
1463 last_update_date,
1464 last_updated_by,
1465 inventory_item_id,
1466 unit_of_measure,
1467 wip_resource_id,
1468 org_id,
1469 cdl_system_reference4
1470 )
1471 SELECT
1472 l_transaction_source
1473 , l_Batch_Name
1474 , l_week_ending --pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
1475 , NULL
1476 , ORG.Name
1477 , InvRec.Expenditure_Item_Date
1478 , InvRec.Project_Number
1479 , TASK.Task_Number
1480 , l_Exp_Type
1481 , InvRec.PA_Quantity
1482 , NVL( InvRec.Expenditure_Comment , l_Exp_Comment )
1483 , DIST.Invoice_Distribution_Id
1484 , 'Y'
1485 , InvRec.Dist_Code_Combination_Id
1486 , InvRec.Accts_Pay_Code_Combination_Id
1487 , InvRec.PO_Distribution_Id
1488 , InvRec.RCV_Transaction_Id
1489 , l_receipt_num
1490 , DIST.Accounting_Date
1491 , l_linkage
1492 , l_trx_status_code
1493 , l_base_currency_code /* denom_currency_code */
1494 , l_denom_raw_cost /* denom_raw_cost */
1495 , l_denom_burdened_cost /* denom_burdened_cost */
1496 , InvRec.Exchange_Date /* acct_rate_date */
1497 , l_User_Conv_Type /* acct_rate_type */
1498 , InvRec.Exchange_Rate /* acct_exchange_rate */
1499 , l_acct_raw_cost /* acct_raw_cost */
1500 , l_acct_burdened_cost /* acct_burdened_cost */
1501 , SYSDATE
1502 , l_user_id
1503 , SYSDATE
1504 , l_user_id
1505 , PoRec.item_id
1506 , l_uom
1507 , PoRec.wip_resource_id
1508 , InvRec.Org_Id
1509 , l_system_reference
1510 FROM
1511 AP_Invoice_Distributions_all DIST
1512 , PA_Tasks TASK
1513 , HR_Organization_Units ORG
1514 WHERE
1515 DIST.Invoice_Id = InvRec.Invoice_Id
1516 AND DIST.Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id
1517 AND ORG.Organization_Id = NVL(l_exp_org_id,InvRec.Expenditure_Organization_Id) /* Bug 13853188 */
1518 AND TASK.Task_Id = l_Task_Id;
1519
1520
1521 ----------------------------------------------------------------------
1522 -- Update pa_addition_flag to 'Y' for successful invoice distributions
1523 ----------------------------------------------------------------------
1524
1525 fnd_message.set_name('PJM','CONC-APINV Flag Comp');
1526 PJM_CONC.put_line('... ' || fnd_message.get);
1527 PJM_CONC.new_line(1);
1528
1529 l_progress := 88;
1530
1531 UPDATE AP_Invoice_Distributions_all
1532 SET Pa_Addition_Flag = 'Y',
1533 Request_Id = l_request_id
1534 WHERE Invoice_Id = InvRec.Invoice_Id
1535 AND Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id;
1536
1537 EXCEPTION
1538 WHEN NO_DATA_FOUND THEN
1539 NULL;
1540 WHEN DUP_VAL_ON_INDEX THEN
1541 NULL;
1542 END;
1543
1544 end if;
1545
1546 END LOOP; -- End process special charges with project info
1547
1548 CLOSE SPC_WP_Curs;
1549
1550 else -- without project specified
1551
1552 OPEN IPV_NP_Curs;
1553
1554 LOOP -- Start process IPV/ERV/Tax without project specified
1555
1556 l_progress := 90;
1557
1558 FETCH IPV_NP_Curs INTO InvRec;
1559 EXIT WHEN IPV_NP_Curs%NOTFOUND;
1560
1561 l_progress := 100;
1562
1563 --------------------------------------------------------------------
1564 -- Get PO Value
1565 ---------------------------------------------------------------------
1566
1567 OPEN Po_Data ( InvRec.Po_Distribution_ID );
1568 FETCH Po_Data INTO PoRec;
1569 CLOSE Po_Data;
1570
1571 ----------------------------------------------------------------------
1572 -- Get Accounting Currency Code
1573 ----------------------------------------------------------------------
1574 if InvRec.Line_Type_Lookup_Code in ('ERV','TERV')
1575 then
1576 select ap.base_currency_code
1577 into l_base_currency_code
1578 from ap_system_parameters_all ap
1579 where ap.org_id = InvRec.Org_Id;
1580 else
1581 l_base_currency_code := InvRec.Invoice_Currency_Code;
1582 end if;
1583
1584
1585 IF ( InvRec.Task_Id IS NOT NULL ) THEN
1586 l_Task_Id := InvRec.Task_Id;
1587 ELSE
1588 l_Task_Id := Assign_Task( InvRec.PO_Distribution_Id
1589 , PoRec.Destination_Type_Code
1590 , InvRec.Project_Id );
1591 END IF;
1592
1593 PJM_CONC.put_line(' invoice_id ............ '||InvRec.Invoice_Id);
1594 PJM_CONC.put_line(' line_num .............. '||
1595 InvRec.Invoice_Distribution_Id);
1596 PJM_CONC.put_line(' project_id ............ '||InvRec.Project_ID);
1597 PJM_CONC.put_line(' task_id ............... '||l_Task_Id);
1598 PJM_CONC.put_line(' expenditure_org_id .... '||
1599 InvRec.Expenditure_Organization_ID);
1600
1601 Timestamp;
1602
1603
1604 l_curr_invoice_id := InvRec.Invoice_Id;
1605 l_first_invoice := FALSE;
1606
1607 ----------------------------------------------------------------------
1608 -- Get Expenditure Type
1609 ----------------------------------------------------------------------
1610
1611 l_progress := 110;
1612
1613 l_exp_type := NULL; -- bug fix 12835119(FP of 7372638)
1614 IF (X_Exp_Type_Bsd_On_Pur_Ctgy = 'Y') THEN
1615
1616 -- get wip entity id ,po header id for the PO.
1617 SELECT wip_entity_id,po_line_id
1618 INTO l_wip_entity_id,l_po_line_id
1619 FROM PO_DISTRIBUTIONS_ALL
1620 WHERE po_distribution_id = InvRec.PO_Distribution_Id;
1621
1622 IF (l_wip_entity_id is not NULL and l_po_line_id is not NULL) THEN
1623 Begin
1624 select distinct category_id
1625 into l_po_category_id
1626 from WIP_EAM_DIRECT_ITEMS_V
1627 where work_order_number = l_wip_entity_id
1628 and po_line_id = l_po_line_id;
1629 EXCEPTION
1630 when others then
1631 NULL;
1632 END;
1633 END IF;
1634 PJM_CONC.put_line('Category id for Direct item'||l_po_category_id);
1635
1636 IF l_po_category_id is not NULL then
1637 l_exp_type := get_ExpType_for_DirectItem(InvRec.Invoice_Id,l_po_category_id);
1638 PJM_CONC.put_line('Exp type for Direct item'||l_exp_type);
1639 END IF;
1640 END IF;
1641 --- bug fix 12835119(FP of 7372638)
1642
1643 IF (l_exp_type is NULL) THEN --- bug fix 12835119(FP of 7372638)
1644
1645 l_exp_type := Get_Charges_Expenditure_Type
1646 ( invrec.line_type_lookup_code
1647 , invrec.project_id
1648 , invrec.expenditure_organization_id );
1649 END IF; --- bug fix 12835119(FP of 7372638)
1650
1651 PJM_CONC.put_line(' Invoice line type is .. '||invrec.line_type_lookup_code);
1652 PJM_CONC.put_line(' Charge amount ............ '||
1653 InvRec.charge_amount);
1654 PJM_CONC.put_line(' Charge expenditure_type .. '||l_exp_type);
1655 PJM_CONC.put_line(' expenditure_comment ... '||
1656 InvRec.Expenditure_Comment);
1657 PJM_CONC.new_line(1);
1658
1659
1660 /* Bug 13853188 The expenditure organization is not active */
1661 Begin
1662 SELECT BD.pa_expenditure_org_id
1663 INTO l_exp_org_id
1664 FROM PO_DISTRIBUTIONS_ALL POD,
1665 wip_operations WO ,
1666 bom_departments BD
1667 WHERE POD.wip_entity_id = WO.wip_entity_id
1668 AND NVL(wo.repetitive_schedule_id, -99999) = NVL(POD.wip_repetitive_schedule_id, -99999)
1669 AND POD.wip_operation_seq_num = WO.OPERATION_SEQ_NUM
1670 AND BD.department_id = WO.department_id
1671 AND BD.organization_id = WO.organization_id
1672 AND pod.po_distribution_id = InvRec.PO_Distribution_Id;
1673
1674 PJM_CONC.put_line('pa_expenditure_org_id .....'||l_exp_org_id);
1675 PJM_CONC.put_line('pa_expenditure_org_id .....'||InvRec.PO_Distribution_Id);
1676
1677 EXCEPTION
1678 when others then
1679 PJM_CONC.put_line('Exp organization is not specified on Department. Will use the Inv organization. ');
1680 END;
1681 /* Bug Fix ends Bug 13853188 The expenditure organization is not active */
1682
1683
1684 ---------------------------------------------------------------------
1685 -- Set Expenditure Comment
1686 ---------------------------------------------------------------------
1687 l_progress := 120;
1688
1689 select decode(invrec.line_type_lookup_code, 'IPV', l_IPV_Exp_Comment,
1690 'ERV', l_ERV_Exp_Comment,
1691 'FREIGHT', l_Freight_Exp_Comment,
1692 'TAX', l_Tax_Exp_Comment,
1693 'TIPV', l_Tax_Exp_Comment,
1694 'TERV', l_Tax_Exp_Comment,
1695 'TRV', l_Tax_Exp_Comment,
1696 'MISCELLANEOUS', l_Misc_Exp_Comment, null)
1697 into l_exp_comment
1698 from dual;
1699
1700 ----------------------------------------------------------------------
1701 -- Getting Receipt Num from RCV_TRANSACTION_ID if exists
1702 ----------------------------------------------------------------------
1703
1704 l_progress := 130;
1705
1706 if ( InvRec.RCV_Transaction_Id is not null ) then
1707
1708 SELECT rsh.receipt_num
1709 INTO l_receipt_num
1710 FROM rcv_shipment_headers rsh
1711 , rcv_transactions rt
1712 WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
1713 AND rsh.shipment_header_id = rt.shipment_header_id;
1714
1715 end if;
1716
1717 ----------------------------------------------------------------------
1718 -- Converting System RATE_TYPE to User RATE_TYPE if exists
1719 ----------------------------------------------------------------------
1720
1721 l_progress := 140;
1722
1723 if ( InvRec.Exchange_Rate_Type is not null ) then
1724
1725 SELECT User_Conversion_Type
1726 INTO l_User_Conv_Type
1727 FROM gl_daily_conversion_types
1728 WHERE conversion_type = InvRec.Exchange_Rate_Type;
1729
1730 else -- bug 4219497
1731 l_user_conv_type := null;
1732
1733 end if;
1734
1735 ----------------------------------------------------------------------
1736 -- Insert into PA_TRANSACTION_INTERFACES table
1737 ----------------------------------------------------------------------
1738
1739 fnd_message.set_name('PJM','CONC-APINV Insert');
1740 PJM_CONC.put_line('... ' || fnd_message.get);
1741
1742 if not ( InvRec.Dist_Code_Combination_Id is not null AND
1743 nvl(nvl(InvRec.Base_Charge_Amount,
1744 InvRec.Charge_Amount) , 0) <> 0 ) then
1745
1746 PJM_CONC.put_line('...... Charge amount not available, skipping...');
1747 -- Mark skipped record to 'G'
1748 UPDATE AP_Invoice_Distributions_all
1749 SET pa_addition_flag = 'G'
1750 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
1751 from ap_invoice_distributions_all
1752 where Invoice_Id = InvRec.Invoice_Id
1753 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
1754
1755 elsif ( l_Exp_Type is null) then
1756
1757 PJM_CONC.put_line('...... Inv Org not setup to transfer charges, skipping...');
1758 -- Mark skipped record to 'G'
1759 UPDATE AP_Invoice_Distributions_all
1760 SET pa_addition_flag = 'G'
1761 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
1762 from ap_invoice_distributions_all
1763 where Invoice_Id = InvRec.Invoice_Id
1764 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
1765
1766 else -- Start proecess
1767
1768 BEGIN
1769
1770 l_progress := 150;
1771
1772 ---------------------------------------------------------------------
1773 -- For Blue Print org, setting Transaction Source , system linkage
1774 -- according to pa_posting_flag and pa_autoaccounting_flag
1775 ---------------------------------------------------------------------
1776
1777 select NVL(pa_posting_flag,'N'),
1778 NVL(pa_autoaccounting_flag,'N')
1779 into l_blue_print_enabled_flag,
1780 l_autoaccounting_flag
1781 from pjm_org_parameters
1782 where organization_id = InvRec.Expenditure_Organization_Id;
1783
1784 l_system_reference := NULL;
1785 if PoRec.destination_type_code = 'INVENTORY' then
1786 l_linkage := 'INV';
1787 -- Bug 10048051 starts
1788 If l_blue_print_enabled_flag = 'Y' then
1789
1790 --If l_autoaccounting_flag = 'Y' then
1791 /* BP and autoaccounting */
1792 --l_transaction_source := 'PJM_CSTBP_INV_NO_ACCOUNTS';
1793 --else
1794 /* BP and no autoaccounting -- Send Account to PA */
1795 --l_transaction_source := 'PJM_CSTBP_INV_ACCOUNTS';
1796
1797 --end if; /* end of check for auto accounting */
1798 l_transaction_source := 'PJM_CSTBP_INV_GL_ACCOUNTS';
1799 -- Bug 10048051 ends
1800 else
1801 l_transaction_source := 'Inventory';
1802 l_system_reference := 'PJM';
1803 end if;
1804
1805 elsif PoRec.destination_type_code = 'SHOP FLOOR' then
1806 l_linkage := 'WIP';
1807 If l_blue_print_enabled_flag = 'Y' then
1808 If l_autoaccounting_flag = 'Y' then
1809 /* BP and autoaccounting */
1810 l_transaction_source := 'PJM_CSTBP_WIP_NO_ACCOUNTS';
1811 else
1812 /* BP and no autoaccounting -- Send Account to PA */
1813 l_transaction_source := 'PJM_CSTBP_WIP_ACCOUNTS';
1814
1815 end if; /* end of check for auto accounting */
1816 else
1817 l_transaction_source := 'Work In Process';
1818 l_system_reference := 'PJM';
1819 end if;
1820
1821 END IF; /* check for BP org */
1822
1823
1824
1825 -------------------------------------------------
1826 -- Set the denom amount for bug 4169096
1827 -------------------------------------------------
1828
1829 if InvRec.Line_Type_Lookup_Code in ('ERV','TERV')
1830 then
1831 l_denom_raw_cost := InvRec.Base_Charge_Amount;
1832 l_acct_raw_cost := InvRec.Base_Charge_Amount;
1833 if l_blue_print_enabled_flag = 'Y'
1834 then
1835 l_denom_burdened_cost := NULL;
1836 l_acct_burdened_cost := NULL;
1837 else
1838 l_denom_burdened_cost := InvRec.Base_Charge_Amount;
1839 l_acct_burdened_cost := InvRec.Base_Charge_Amount;
1840 end if;
1841 else
1842 l_denom_raw_cost := InvRec.Charge_Amount;
1843 select nvl(InvRec.Base_Charge_Amount, InvRec.Charge_Amount) into l_acct_raw_cost from dual;
1844 if l_blue_print_enabled_flag = 'Y'
1845 then
1846 l_denom_burdened_cost := NULL;
1847 l_acct_burdened_cost := NULL;
1848 else
1849 l_denom_burdened_cost := InvRec.Charge_Amount;
1850 l_acct_burdened_cost := l_acct_raw_cost;
1851 end if;
1852 end if;
1853
1854
1855 -- Get Week Ending of Expenditure Item Date
1856 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
1857 into l_week_ending_day_index
1858 FROM pa_implementations_all WHERE org_id = InvRec.Proj_Org_Id;
1859
1860 select to_char(to_date('01-01-1950','DD-MM-YYYY') + l_week_ending_day_index - 1, 'Day')
1861 into l_week_ending_day from dual;
1862
1863 select next_day( trunc(InvRec.Expenditure_Item_Date)-1, l_week_ending_day )
1864 into l_week_ending
1865 from dual;
1866
1867 PJM_CONC.put_line('...... Processing IPV, ERV, Tax Variances');
1868
1869 -- Insert for Charges
1870 INSERT INTO pa_transaction_interface_all
1871 (transaction_source,
1872 batch_name,
1873 expenditure_ending_date,
1874 employee_number,
1875 organization_name,
1876 expenditure_item_date,
1877 project_number,
1878 task_number,
1879 expenditure_type,
1880 quantity,
1881 expenditure_comment,
1882 orig_transaction_reference,
1883 unmatched_negative_txn_flag,
1884 dr_code_combination_id,
1885 cr_code_combination_id,
1886 orig_exp_txn_reference1,
1887 orig_exp_txn_reference2,
1888 orig_exp_txn_reference3,
1889 gl_date,
1890 system_linkage,
1891 transaction_status_code,
1892 denom_currency_code,
1893 denom_raw_cost,
1894 denom_burdened_cost,
1895 acct_rate_date,
1896 acct_rate_type,
1897 acct_exchange_rate,
1898 acct_raw_cost,
1899 acct_burdened_cost,
1900 creation_date,
1901 created_by,
1902 last_update_date,
1903 last_updated_by,
1904 Inventory_Item_Id,
1905 Unit_Of_Measure,
1906 Wip_Resource_Id,
1907 Org_Id,
1908 cdl_system_reference4
1909 )
1910 SELECT
1911 l_transaction_source
1912 , l_Batch_Name
1913 , l_week_ending -- pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
1914 , NULL
1915 , ORG.Name
1916 , InvRec.Expenditure_Item_Date
1917 , InvRec.Project_Number
1918 , TASK.Task_Number
1919 , l_Exp_Type
1920 , InvRec.PA_Quantity
1921 , NVL( InvRec.Expenditure_Comment , l_Exp_Comment )
1922 , DIST.Invoice_Distribution_Id
1923 , 'Y'
1924 , InvRec.Dist_Code_Combination_Id
1925 , InvRec.Accts_Pay_Code_Combination_Id
1926 , InvRec.PO_Distribution_Id
1927 , InvRec.RCV_Transaction_Id
1928 , l_receipt_num
1929 , DIST.Accounting_Date
1930 , l_linkage
1931 , l_trx_status_code
1932 , l_base_currency_code /* denom_currency_code */
1933 , l_denom_raw_cost /* denom_raw_cost */
1934 , l_denom_burdened_cost /* denom_burdened_cost */
1935 , InvRec.Exchange_Date /* acct_rate_date */
1936 , l_User_Conv_Type /* acct_rate_type */
1937 , InvRec.Exchange_Rate /* acct_exchange_rate */
1938 , l_acct_raw_cost /* acct_raw_cost */
1939 , l_acct_burdened_cost /* acct_burdened_cost */
1940 , SYSDATE
1941 , l_user_id
1942 , SYSDATE
1943 , l_user_id
1944 , PoRec.Item_Id
1945 , l_uom
1946 , PoRec.Wip_Resource_Id
1947 , InvRec.Org_Id
1948 , l_system_reference
1949 FROM
1950 AP_Invoice_Distributions_all DIST
1951 , PA_Tasks TASK
1952 , HR_Organization_Units ORG
1953 WHERE
1954 DIST.Invoice_Id = InvRec.Invoice_Id
1955 AND DIST.Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id
1956 -- AND DIST.PA_Addition_Flag = 'S'
1957 AND ORG.Organization_Id = NVL(l_exp_org_id,InvRec.Expenditure_Organization_Id) /* Bug 13853188 */
1958 AND TASK.Task_Id = l_Task_Id;
1959
1960 ----------------------------------------------------------------------
1961 -- Update pa_addition_flag to 'Y' for successful invoice distributions
1962 ----------------------------------------------------------------------
1963
1964 fnd_message.set_name('PJM','CONC-APINV Flag Comp');
1965 PJM_CONC.put_line('... ' || fnd_message.get);
1966 PJM_CONC.new_line(1);
1967
1968 l_progress := 160;
1969
1970 UPDATE AP_Invoice_Distributions_all
1971 SET Pa_Addition_Flag = 'Y',
1972 Request_Id = l_request_id
1973 WHERE Invoice_Id = InvRec.Invoice_Id
1974 AND Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id;
1975
1976 EXCEPTION
1977 WHEN NO_DATA_FOUND THEN
1978 NULL;
1979 WHEN DUP_VAL_ON_INDEX THEN
1980 NULL;
1981 END;
1982
1983 end if; -- no project parameter
1984
1985 END LOOP; -- End process data without project specified
1986
1987 CLOSE IPV_NP_Curs;
1988
1989 OPEN SPC_NP_Curs;
1990
1991 LOOP -- Start process Freight and Misc charge without project specified
1992
1993 l_progress := 161;
1994
1995 FETCH SPC_NP_Curs INTO InvRec;
1996 EXIT WHEN SPC_NP_Curs%NOTFOUND;
1997
1998 l_progress := 162;
1999
2000 ----------------------------------------------------------------------
2001 -- Get Accounting Currency Code
2002 ----------------------------------------------------------------------
2003 l_base_currency_code := InvRec.Invoice_Currency_Code;
2004
2005 --------------------------------------------------------------------
2006 -- Get PO Value
2007 ---------------------------------------------------------------------
2008
2009 OPEN Po_Data ( InvRec.Po_Distribution_ID );
2010 FETCH Po_Data INTO PoRec;
2011 CLOSE Po_Data;
2012
2013
2014 IF ( InvRec.Task_Id IS NOT NULL ) THEN
2015 l_Task_Id := InvRec.Task_Id;
2016 ELSE
2017 l_Task_Id := Assign_Task( InvRec.PO_Distribution_Id
2018 , PoRec.Destination_Type_Code
2019 , InvRec.Project_Id );
2020 END IF;
2021
2022 PJM_CONC.put_line(' invoice_id ............ '||InvRec.Invoice_Id);
2023 PJM_CONC.put_line(' line_num .............. '||
2024 InvRec.Invoice_Distribution_Id);
2025 PJM_CONC.put_line(' project_id ............ '||InvRec.Project_ID);
2026 PJM_CONC.put_line(' task_id ............... '||l_Task_Id);
2027 PJM_CONC.put_line(' expenditure_org_id .... '||
2028 InvRec.Expenditure_Organization_ID);
2029
2030 Timestamp;
2031
2032
2033 l_curr_invoice_id := InvRec.Invoice_Id;
2034 l_first_invoice := FALSE;
2035
2036 ----------------------------------------------------------------------
2037 -- Get Expenditure Type
2038 ----------------------------------------------------------------------
2039
2040 l_progress := 163;
2041
2042 l_exp_type := NULL; -- bug fix 12835119(FP of 7372638)
2043 IF (X_Exp_Type_Bsd_On_Pur_Ctgy = 'Y') THEN
2044
2045 -- get wip entity id ,po header id for the PO.
2046 SELECT wip_entity_id,po_line_id
2047 INTO l_wip_entity_id,l_po_line_id
2048 FROM PO_DISTRIBUTIONS_ALL
2049 WHERE po_distribution_id = InvRec.PO_Distribution_Id;
2050
2051 IF (l_wip_entity_id is not NULL and l_po_line_id is not NULL) THEN
2052 Begin
2053 select distinct category_id
2054 into l_po_category_id
2055 from WIP_EAM_DIRECT_ITEMS_V
2056 where work_order_number = l_wip_entity_id
2057 and po_line_id = l_po_line_id;
2058 EXCEPTION
2059 when others then
2060 NULL;
2061 END;
2062 END IF;
2063 PJM_CONC.put_line('Category id for Direct item'||l_po_category_id);
2064
2065 IF l_po_category_id is not NULL then
2066 l_exp_type := get_ExpType_for_DirectItem(InvRec.Invoice_Id,l_po_category_id);
2067 PJM_CONC.put_line('Exp type for Direct item'||l_exp_type);
2068 END IF;
2069 END IF;
2070 --- bug fix 12835119(FP of 7372638)
2071
2072 IF (l_exp_type is NULL) THEN --- bug fix 12835119(FP of 7372638)
2073
2074 l_exp_type := Get_Charges_Expenditure_Type
2075 ( invrec.line_type_lookup_code
2076 , invrec.project_id
2077 , invrec.expenditure_organization_id );
2078 END IF; --- bug fix 12835119(FP of 7372638)
2079
2080 PJM_CONC.put_line(' Invoice line type is .. '||invrec.line_type_lookup_code);
2081 PJM_CONC.put_line(' Charge amount ............ '||
2082 InvRec.charge_amount);
2083 PJM_CONC.put_line(' Charge expenditure_type .. '||l_exp_type);
2084 PJM_CONC.put_line(' expenditure_comment ... '||
2085 InvRec.Expenditure_Comment);
2086 PJM_CONC.new_line(1);
2087
2088 /* Bug 13853188 The expenditure organization is not active */
2089 Begin
2090 SELECT BD.pa_expenditure_org_id
2091 INTO l_exp_org_id
2092 FROM PO_DISTRIBUTIONS_ALL POD,
2093 wip_operations WO ,
2094 bom_departments BD
2095 WHERE POD.wip_entity_id = WO.wip_entity_id
2096 AND NVL(wo.repetitive_schedule_id, -99999) = NVL(POD.wip_repetitive_schedule_id, -99999)
2097 AND POD.wip_operation_seq_num = WO.OPERATION_SEQ_NUM
2098 AND BD.department_id = WO.department_id
2099 AND BD.organization_id = WO.organization_id
2100 AND pod.po_distribution_id = InvRec.PO_Distribution_Id;
2101
2102 PJM_CONC.put_line('pa_expenditure_org_id .....'||l_exp_org_id);
2103 PJM_CONC.put_line('pa_expenditure_org_id .....'||InvRec.PO_Distribution_Id);
2104
2105 EXCEPTION
2106 when others then
2107 PJM_CONC.put_line('Exp organization is not specified on Department. Will use the Inv organization. ');
2108 END;
2109 /* Bug Fix ends Bug 13853188 The expenditure organization is not active */
2110
2111 ---------------------------------------------------------------------
2112 -- Set Expenditure Comment
2113 ---------------------------------------------------------------------
2114 l_progress := 164;
2115
2116 select decode(invrec.line_type_lookup_code, 'IPV', l_IPV_Exp_Comment,
2117 'ERV', l_ERV_Exp_Comment,
2118 'FREIGHT', l_Freight_Exp_Comment,
2119 'TAX', l_Tax_Exp_Comment,
2120 'TIPV', l_Tax_Exp_Comment,
2121 'TERV', l_Tax_Exp_Comment,
2122 'TRV', l_Tax_Exp_Comment,
2123 'MISCELLANEOUS', l_Misc_Exp_Comment,
2124 'NONREC_TAX', l_Tax_Exp_Comment,null) --bugfix 7482789
2125 into l_exp_comment
2126 from dual;
2127
2128 ----------------------------------------------------------------------
2129 -- Getting Receipt Num from RCV_TRANSACTION_ID if exists
2130 ----------------------------------------------------------------------
2131
2132 l_progress := 165;
2133
2134 if ( InvRec.RCV_Transaction_Id is not null ) then
2135
2136 SELECT rsh.receipt_num
2137 INTO l_receipt_num
2138 FROM rcv_shipment_headers rsh
2139 , rcv_transactions rt
2140 WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
2141 AND rsh.shipment_header_id = rt.shipment_header_id;
2142
2143 end if;
2144
2145 ----------------------------------------------------------------------
2146 -- Converting System RATE_TYPE to User RATE_TYPE if exists
2147 ----------------------------------------------------------------------
2148
2149 l_progress := 166;
2150
2151 if ( InvRec.Exchange_Rate_Type is not null ) then
2152
2153 SELECT User_Conversion_Type
2154 INTO l_User_Conv_Type
2155 FROM gl_daily_conversion_types
2156 WHERE conversion_type = InvRec.Exchange_Rate_Type;
2157
2158 else -- bug 4219497
2159 l_user_conv_type := null;
2160
2161 end if;
2162
2163 ----------------------------------------------------------------------
2164 -- Insert into PA_TRANSACTION_INTERFACES table
2165 ----------------------------------------------------------------------
2166
2167 fnd_message.set_name('PJM','CONC-APINV Insert');
2168 PJM_CONC.put_line('... ' || fnd_message.get);
2169
2170 if not ( InvRec.Dist_Code_Combination_Id is not null AND
2171 nvl(nvl(InvRec.Base_Charge_Amount,
2172 InvRec.Charge_Amount) , 0) <> 0 ) then
2173
2174 PJM_CONC.put_line('...... Charge amount not available, skipping...');
2175 -- Mark skipped record to 'G'
2176 UPDATE AP_Invoice_Distributions_all
2177 SET pa_addition_flag = 'G'
2178 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
2179 from ap_invoice_distributions_all
2180 where Invoice_Id = InvRec.Invoice_Id
2181 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
2182
2183 elsif ( l_Exp_Type is null) then
2184
2185 PJM_CONC.put_line('...... Inv Org not setup to transfer charges, skipping...');
2186 -- Mark skipped record to 'G'
2187 UPDATE AP_Invoice_Distributions_all
2188 SET pa_addition_flag = 'G'
2189 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
2190 from ap_invoice_distributions_all
2191 where Invoice_Id = InvRec.Invoice_Id
2192 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
2193
2194 else -- Start proecess
2195
2196 BEGIN
2197
2198 l_progress := 167;
2199
2200 ---------------------------------------------------------------------
2201 -- For Blue Print org, setting Transaction Source , system linkage
2202 -- according to pa_posting_flag and pa_autoaccounting_flag
2203 ---------------------------------------------------------------------
2204
2205 select NVL(pa_posting_flag,'N'),
2206 NVL(pa_autoaccounting_flag,'N')
2207 into l_blue_print_enabled_flag,
2208 l_autoaccounting_flag
2209 from pjm_org_parameters
2210 where organization_id = InvRec.Expenditure_Organization_Id;
2211
2212 l_system_reference := NULL;
2213 if PoRec.destination_type_code = 'INVENTORY' then
2214 l_linkage := 'INV';
2215 -- Bug 10048051 starts
2216 If l_blue_print_enabled_flag = 'Y' then
2217
2218 --If l_autoaccounting_flag = 'Y' then
2219 /* BP and autoaccounting */
2220 --l_transaction_source := 'PJM_CSTBP_INV_NO_ACCOUNTS';
2221 --else
2222 /* BP and no autoaccounting -- Send Account to PA */
2223 --l_transaction_source := 'PJM_CSTBP_INV_ACCOUNTS';
2224
2225 --end if; /* end of check for auto accounting */
2226 l_transaction_source := 'PJM_CSTBP_INV_GL_ACCOUNTS';
2227 -- Bug 10048051 ends
2228 else
2229 l_transaction_source := 'Inventory';
2230 l_system_reference := 'PJM';
2231 end if;
2232
2233 elsif PoRec.destination_type_code = 'SHOP FLOOR' then
2234 l_linkage := 'WIP';
2235 If l_blue_print_enabled_flag = 'Y' then
2236 If l_autoaccounting_flag = 'Y' then
2237 /* BP and autoaccounting */
2238 l_transaction_source := 'PJM_CSTBP_WIP_NO_ACCOUNTS';
2239 else
2240 /* BP and no autoaccounting -- Send Account to PA */
2241 l_transaction_source := 'PJM_CSTBP_WIP_ACCOUNTS';
2242
2243 end if; /* end of check for auto accounting */
2244 else
2245 l_transaction_source := 'Work In Process';
2246 l_system_reference := 'PJM';
2247 end if;
2248
2249 END IF; /* check for BP org */
2250
2251
2252
2253
2254 -------------------------------------------------
2255 -- Set the denom amount for bug 4169096
2256 -------------------------------------------------
2257
2258 if InvRec.Line_Type_Lookup_Code in ('ERV','TERV')
2259 then
2260 l_denom_raw_cost := InvRec.Base_Charge_Amount;
2261 l_acct_raw_cost := InvRec.Base_Charge_Amount;
2262 if l_blue_print_enabled_flag = 'Y'
2263 then
2264 l_denom_burdened_cost := NULL;
2265 l_acct_burdened_cost := NULL;
2266 else
2267 l_denom_burdened_cost := InvRec.Base_Charge_Amount;
2268 l_acct_burdened_cost := InvRec.Base_Charge_Amount;
2269 end if;
2270 else
2271 l_denom_raw_cost := InvRec.Charge_Amount;
2272 select nvl(InvRec.Base_Charge_Amount, InvRec.Charge_Amount) into l_acct_raw_cost from dual;
2273 if l_blue_print_enabled_flag = 'Y'
2274 then
2275 l_denom_burdened_cost := NULL;
2276 l_acct_burdened_cost := NULL;
2277 else
2278 l_denom_burdened_cost := InvRec.Charge_Amount;
2279 l_acct_burdened_cost := l_acct_raw_cost;
2280 end if;
2281 end if;
2282
2283
2284 -- Get Week Ending of Expenditure Item Date
2285 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
2286 into l_week_ending_day_index
2287 FROM pa_implementations_all WHERE org_id = InvRec.Proj_Org_Id;
2288
2289 select to_char(to_date('01-01-1950','DD-MM-YYYY') + l_week_ending_day_index - 1, 'Day')
2290 into l_week_ending_day from dual;
2291
2292 select next_day( trunc(InvRec.Expenditure_Item_Date)-1, l_week_ending_day )
2293 into l_week_ending
2294 from dual;
2295
2296 PJM_CONC.put_line('...... Processing Special Charges');
2297
2298 -- Insert for Charges
2299 INSERT INTO pa_transaction_interface_all
2300 (transaction_source,
2301 batch_name,
2302 expenditure_ending_date,
2303 employee_number,
2304 organization_name,
2305 expenditure_item_date,
2306 project_number,
2307 task_number,
2308 expenditure_type,
2309 quantity,
2310 expenditure_comment,
2311 orig_transaction_reference,
2312 unmatched_negative_txn_flag,
2313 dr_code_combination_id,
2314 cr_code_combination_id,
2315 orig_exp_txn_reference1,
2316 orig_exp_txn_reference2,
2317 orig_exp_txn_reference3,
2318 gl_date,
2319 system_linkage,
2320 transaction_status_code,
2321 denom_currency_code,
2322 denom_raw_cost,
2323 denom_burdened_cost,
2324 acct_rate_date,
2325 acct_rate_type,
2326 acct_exchange_rate,
2327 acct_raw_cost,
2328 acct_burdened_cost,
2329 creation_date,
2330 created_by,
2331 last_update_date,
2332 last_updated_by,
2333 Inventory_Item_Id,
2334 Unit_Of_Measure,
2335 Wip_Resource_Id,
2336 Org_Id,
2337 cdl_system_reference4
2338 )
2339 SELECT
2340 l_transaction_source
2341 , l_Batch_Name
2342 , l_week_ending -- pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
2343 , NULL
2344 , ORG.Name
2345 , InvRec.Expenditure_Item_Date
2346 , InvRec.Project_Number
2347 , TASK.Task_Number
2348 , l_Exp_Type
2349 , InvRec.PA_Quantity
2350 , NVL( InvRec.Expenditure_Comment , l_Exp_Comment )
2351 , DIST.Invoice_Distribution_Id
2352 , 'Y'
2353 , InvRec.Dist_Code_Combination_Id
2354 , InvRec.Accts_Pay_Code_Combination_Id
2355 , InvRec.PO_Distribution_Id
2356 , InvRec.RCV_Transaction_Id
2357 , l_receipt_num
2358 , DIST.Accounting_Date
2359 , l_linkage
2360 , l_trx_status_code
2361 , l_base_currency_code /* denom_currency_code */
2362 , l_denom_raw_cost /* denom_raw_cost */
2363 , l_denom_burdened_cost /* denom_burdened_cost */
2364 , InvRec.Exchange_Date /* acct_rate_date */
2365 , l_User_Conv_Type /* acct_rate_type */
2366 , InvRec.Exchange_Rate /* acct_exchange_rate */
2367 , l_acct_raw_cost /* acct_raw_cost */
2368 , l_acct_burdened_cost /* acct_burdened_cost */
2369 , SYSDATE
2370 , l_user_id
2371 , SYSDATE
2372 , l_user_id
2373 , PoRec.Item_Id
2374 , l_uom
2375 , PoRec.Wip_Resource_Id
2376 , InvRec.Org_Id
2377 , l_system_reference
2378 FROM
2379 AP_Invoice_Distributions_all DIST
2380 , PA_Tasks TASK
2381 , HR_Organization_Units ORG
2382 WHERE
2383 DIST.Invoice_Id = InvRec.Invoice_Id
2384 AND DIST.Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id
2385 -- AND DIST.PA_Addition_Flag = 'S'
2386 AND ORG.Organization_Id = NVL(l_exp_org_id,InvRec.Expenditure_Organization_Id) /* Bug 13853188 */
2387 AND TASK.Task_Id = l_Task_Id;
2388
2389 ----------------------------------------------------------------------
2390 -- Update pa_addition_flag to 'Y' for successful invoice distributions
2391 ----------------------------------------------------------------------
2392
2393 fnd_message.set_name('PJM','CONC-APINV Flag Comp');
2394 PJM_CONC.put_line('... ' || fnd_message.get);
2395 PJM_CONC.new_line(1);
2396
2397 l_progress := 168;
2398
2399 UPDATE AP_Invoice_Distributions_all
2400 SET Pa_Addition_Flag = 'Y',
2401 Request_Id = l_request_id
2402 WHERE Invoice_Id = InvRec.Invoice_Id
2403 AND Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id;
2404
2405 EXCEPTION
2406 WHEN NO_DATA_FOUND THEN
2407 NULL;
2408 WHEN DUP_VAL_ON_INDEX THEN
2409 NULL;
2410 END;
2411
2412 end if; -- no project parameter
2413
2414 END LOOP; -- End process special charge without project specified
2415
2416 CLOSE SPC_NP_Curs;
2417
2418
2419 END IF; -- End of both with project specified or without conditions
2420
2421 COMMIT;
2422 fnd_message.set_name('PJM','CONC-APINV Finish Loop');
2423 PJM_CONC.put_line(fnd_message.get || ' ...');
2424 PJM_CONC.new_line(1);
2425
2426 Timestamp;
2427
2428 l_progress := 169;
2429
2430 if (X_Submit_Trx_Import = 'Y') then
2431 l_imp_req_id := fnd_request.submit_request('PA','PAXTRTRX',
2432 'PRC: Transaction Import',
2433 NULL, FALSE,
2434 l_transaction_source,
2435 l_Batch_Name);
2436 end if;
2437
2438 retcode := PJM_CONC.G_conc_success;
2439 return;
2440
2441
2442 EXCEPTION
2443 when OTHERS then
2444 errbuf := 'IPV-'||l_progress||': '||sqlerrm;
2445 retcode := PJM_CONC.G_conc_failure;
2446 return;
2447
2448 END Transfer_Charges_TO_PA;
2449
2450 --- bug fix 12835119(FP of 7372638)
2451 FUNCTION get_ExpType_for_DirectItem(
2452 P_Invoice_ID IN NUMBER,
2453 P_PO_category_id IN NUMBER)
2454 RETURN VARCHAR2 IS
2455 l_expenditure_type VARCHAR2(30);
2456 l_invoice_date DATE;
2457
2458 BEGIN
2459 PJM_CONC.put_line('Get ExpType for DirectItem');
2460 -------------------------
2461 -- Obtain Invoice_date
2462 -------------------------
2463 select invoice_date
2464 into l_invoice_date
2465 from AP_Invoices_All
2466 where invoice_id = P_Invoice_ID;
2467
2468 ----------------------------
2469 -- Obtain expenditure type
2470 ----------------------------
2471 IF P_PO_category_id is not NULL then
2472 begin
2473 select pet.expenditure_type
2474 into l_expenditure_type
2475 from cst_cat_ele_exp_assocs cceea,
2476 pa_expenditure_types pet
2477 where cceea.category_id = P_PO_category_id
2478 and l_invoice_date >= cceea.start_date
2479 and l_invoice_date < (nvl(cceea.end_date, sysdate) + 1)
2480 and cceea.expenditure_type_id = pet.expenditure_type_id;
2481
2482 return l_expenditure_type;
2483 EXCEPTION
2484 WHEN No_Data_Found THEN
2485 PJM_CONC.put_line('Purchase category association is either not defined or inactive');
2486 RETURN NULL;
2487 when OTHERS then
2488 NULL;
2489 RETURN NULL;
2490 end;
2491 END IF;
2492 END get_ExpType_for_DirectItem;
2493 -- bug fix 12835119(FP of 7372638)
2494
2495
2496
2497 END PJM_TRANSFER_CHARGES_PKG;