[Home] [Help]
PACKAGE BODY: APPS.PJM_TRANSFER_CHARGES_PKG
Source
1 PACKAGE BODY PJM_TRANSFER_CHARGES_PKG AS
2 /* $Header: PJMTFCGB.pls 120.14.12010000.2 2008/10/15 12:09:37 ybabulal 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 ) IS
268
269 l_proj_status VARCHAR2(30);
270 l_billable_flag VARCHAR2(1);
271 l_request_id NUMBER;
272 l_user_id NUMBER;
273 l_Exp_Type VARCHAR2(30);
274 l_curr_invoice_id NUMBER;
275 l_first_invoice BOOLEAN;
276 l_imp_req_id NUMBER;
277 l_base_currency_code AP_System_parameters_all.base_currency_code%TYPE;
278
279 -- l_msg_application VARCHAR2(30) := 'PA';
280 -- l_msg_type VARCHAR2(30);
281 -- l_msg_token1 VARCHAR2(30);
282 -- l_msg_token2 VARCHAR2(30);
283 -- l_msg_token3 VARCHAR2(30);
284 -- l_msg_count NUMBER;
285
286 l_IPV_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
287 l_ERV_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
288 l_Freight_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
289 l_Tax_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
290 l_Misc_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
291 l_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
292 l_Batch_Name PA_Transaction_Interface_All.Batch_Name%TYPE;
293 l_Receipt_Num RCV_Shipment_Headers.Receipt_Num%TYPE;
294 l_User_Conv_Type GL_Daily_Conversion_Types.User_Conversion_Type%TYPE;
295 l_Start_Date DATE;
296 l_End_Date DATE;
297 l_Task_Id NUMBER;
298 l_Uom VARCHAR2(25);
299 l_linkage VARCHAR2(25);
300 l_burdened_amount NUMBER;
301 l_progress NUMBER;
302 l_blue_print_enabled_flag VARCHAR2(1);
303 l_autoaccounting_flag VARCHAR2(1);
304 l_transaction_source VARCHAR2(30);
305 l_trx_status_code VARCHAR2(30);
306 l_week_ending DATE;
307 l_week_ending_day VARCHAR2(80);
308 l_week_ending_day_index number;
309 l_denom_raw_cost NUMBER;
310 l_denom_burdened_cost NUMBER;
311 l_acct_raw_cost NUMBER;
312 l_acct_burdened_cost NUMBER;
313
314
315 CURSOR IPV_NP_Curs IS
316 SELECT INV.Invoice_id Invoice_Id
317 , DIST.Invoice_Distribution_Id Invoice_Distribution_Id
318 , PAP.Org_Id Proj_Org_Id
319 , PAP.Project_Id Project_Id
320 , PAP.Segment1 Project_Number
321 , POD.Task_id Task_Id
322 , DIST.Accounting_Date Expenditure_Item_Date
323 , INV.Vendor_Id Vendor_Id
324 , INV.Created_By Created_By
325 , POD.Destination_Organization_Id Expenditure_Organization_Id
326 , POD.Org_Id Org_Id
327 , DIST.description Expenditure_Comment
328 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
329 , DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
330 , nvl( DIST.Accts_Pay_Code_Combination_Id
331 , INV.Accts_Pay_Code_Combination_Id)
332 Accts_Pay_Code_Combination_Id
333 , INV.Invoice_Currency_Code Invoice_Currency_Code
334 , INV.Exchange_Rate_Type Exchange_Rate_Type
335 , INV.Exchange_Date Exchange_Date
336 , INV.Exchange_Rate Exchange_Rate
337 , DIST.Amount Charge_Amount
338 , DIST.Base_Amount Base_Charge_Amount
339 , DIST.PO_Distribution_Id PO_Distribution_Id
340 , DIST.RCV_Transaction_Id RCV_Transaction_Id
341 , DIST.Line_Type_Lookup_Code Line_Type_Lookup_Code
342 FROM AP_Invoices_All INV,
343 (SELECT Invoice_Distribution_Id
344 , Invoice_Id
345 , Project_Id
346 , Task_id
347 , Accounting_Date
348 , Expenditure_Organization_Id
349 , description
350 , Pa_Quantity
351 , Dist_Code_Combination_Id
352 , Accts_Pay_Code_Combination_Id
353 , Amount
354 , Base_Amount
355 , PO_Distribution_Id
356 , RCV_Transaction_Id
357 , Line_Type_Lookup_Code
358 FROM AP_Invoice_Distributions_all
359 WHERE LINE_TYPE_LOOKUP_CODE IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV')
360 AND PA_ADDITION_FLAG in ('E', 'M', 'N')
361 AND POSTED_FLAG = 'Y'
362 ) DIST,
363 PA_Projects_ALL PAP,
364 PJM_Org_Parameters POP,
365 PO_Distributions_All POD
366 WHERE INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
367 AND POD.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
368 AND DIST.Invoice_Id = INV.Invoice_Id
369 AND (( l_Start_Date is null and l_End_Date is null)
370 OR ( l_Start_Date is not null and l_End_Date is not null
371 and DIST.Accounting_Date between l_Start_Date and l_End_Date)
372 OR ( l_Start_Date is not null and l_End_Date is null
373 and DIST.Accounting_Date >= l_Start_Date )
374 OR ( l_Start_Date is null and l_End_Date is not null
375 and DIST.Accounting_Date <= L_End_Date ))
376 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
377 AND DIST.po_distribution_id = POD.po_distribution_id
378 AND POP.Organization_Id = POD.Destination_Organization_Id
379 AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
380 ORDER BY 9,1,2
381 for update;
382
383 CURSOR SPC_NP_Curs IS
384 SELECT INV.Invoice_id Invoice_Id
385 , DIST.Invoice_Distribution_Id Invoice_Distribution_Id
386 , PAP.Org_Id Proj_Org_Id
387 , PAP.Project_Id Project_Id
388 , PAP.Segment1 Project_Number
389 , POD.Task_id Task_Id
390 , DIST.Accounting_Date Expenditure_Item_Date
391 , INV.Vendor_Id Vendor_Id
392 , INV.Created_By Created_By
393 , POD.Destination_Organization_Id Expenditure_Organization_Id
394 , POD.Org_Id Org_Id
395 , DIST.description Expenditure_Comment
396 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
397 , DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
398 , nvl( DIST.Accts_Pay_Code_Combination_Id
399 , INV.Accts_Pay_Code_Combination_Id)
400 Accts_Pay_Code_Combination_Id
401 , INV.Invoice_Currency_Code Invoice_Currency_Code
402 , INV.Exchange_Rate_Type Exchange_Rate_Type
403 , INV.Exchange_Date Exchange_Date
404 , INV.Exchange_Rate Exchange_Rate
405 , DIST.Amount Charge_Amount
406 , DIST.Base_Amount Base_Charge_Amount
407 , DIST.PO_Distribution_Id PO_Distribution_Id
408 , DIST.RCV_Transaction_Id RCV_Transaction_Id
409 , DIST.Line_Type_Lookup_Code Line_Type_Lookup_Code
410 FROM AP_Invoices_All INV,
411 (SELECT C.Invoice_Distribution_Id
412 , C.Invoice_Id
413 , P.Project_Id
414 , P.Task_Id
415 , C.Accounting_Date
416 , C.Expenditure_Organization_Id
417 , C.description
418 , C.Pa_Quantity
419 , C.Dist_Code_Combination_Id
420 , C.Accts_Pay_Code_Combination_Id
421 , C.Amount
422 , C.Base_Amount
423 , NVL(P.PO_Distribution_Id,(SELECT PO_Distribution_Id FROM AP_Invoice_Distributions_all P1
424 WHERE P1.invoice_distribution_id = P.charge_applicable_to_dist_id)) PO_Distribution_Id -- bugfix 7482789
425 , C.RCV_Transaction_Id
426 , C.Line_Type_Lookup_Code
427 FROM AP_Invoice_Distributions_all C, AP_Invoice_Distributions_all P
428 WHERE C.LINE_TYPE_LOOKUP_CODE IN ('FREIGHT','MISCELLANEOUS','NONREC_TAX') -- bugfix 7482789
429 AND C.PA_ADDITION_FLAG in ('E', 'M', 'N')
430 AND C.POSTED_FLAG = 'Y'
431 AND C.charge_applicable_to_dist_id = P.invoice_distribution_id
432 AND (P.charge_applicable_to_dist_id IS NOT NULL OR C.LINE_TYPE_LOOKUP_CODE IN ('FREIGHT','MISCELLANEOUS')) -- bugfix 7482789
433 ) DIST,
434 PA_Projects_ALL PAP,
435 PJM_Org_Parameters POP,
436 PO_Distributions_All POD
437 WHERE INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
438 AND POD.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
439 AND DIST.Invoice_Id = INV.Invoice_Id
440 AND (( l_Start_Date is null and l_End_Date is null)
441 OR ( l_Start_Date is not null and l_End_Date is not null
442 and DIST.Accounting_Date between l_Start_Date and l_End_Date)
443 OR ( l_Start_Date is not null and l_End_Date is null
444 and DIST.Accounting_Date >= l_Start_Date )
445 OR ( l_Start_Date is null and l_End_Date is not null
446 and DIST.Accounting_Date <= L_End_Date ))
447 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
448 AND DIST.po_distribution_id = POD.po_distribution_id
449 AND POP.Organization_Id = POD.Destination_Organization_Id
450 AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
451 ORDER BY 9,1,2
452 for update;
453
454
455 CURSOR IPV_WP_Curs IS
456 SELECT INV.Invoice_id Invoice_Id
457 , DIST.Invoice_Distribution_Id Invoice_Distribution_Id
458 , PAP.Org_Id Proj_Org_Id
459 , PAP.Project_Id Project_Id
460 , PAP.Segment1 Project_Number
461 , POD.Task_id Task_Id
462 , DIST.Accounting_Date Expenditure_Item_Date
463 , INV.Vendor_Id Vendor_Id
464 , INV.Created_By Created_By
465 , POD.Destination_Organization_Id Expenditure_Organization_Id
466 , POD.Org_Id Org_Id
467 , DIST.description Expenditure_Comment
468 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
469 , DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
470 , nvl( DIST.Accts_Pay_Code_Combination_Id
471 , INV.Accts_Pay_Code_Combination_Id)
472 Accts_Pay_Code_Combination_Id
473 , INV.Invoice_Currency_Code Invoice_Currency_Code
474 , INV.Exchange_Rate_Type Exchange_Rate_Type
475 , INV.Exchange_Date Exchange_Date
476 , INV.Exchange_Rate Exchange_Rate
477 , DIST.Amount Charge_Amount
478 , DIST.Base_Amount Base_Charge_Amount
479 , DIST.PO_Distribution_Id PO_Distribution_Id
480 , DIST.RCV_Transaction_Id RCV_Transaction_Id
481 , DIST.Line_Type_Lookup_Code Line_Type_Lookup_Code
482 FROM AP_Invoices_All INV,
483 (SELECT Invoice_Distribution_Id
484 , Invoice_Id
485 , Project_Id
486 , Task_id
487 , Accounting_Date
488 , Expenditure_Organization_Id
489 , description
490 , Pa_Quantity
491 , Dist_Code_Combination_Id
492 , Accts_Pay_Code_Combination_Id
493 , Amount
494 , Base_Amount
495 , PO_Distribution_Id
496 , RCV_Transaction_Id
497 , Line_Type_Lookup_Code
498 FROM AP_Invoice_Distributions_all
499 WHERE LINE_TYPE_LOOKUP_CODE IN ('IPV', 'ERV', 'TIPV', 'TERV', 'TRV')
500 AND PA_ADDITION_FLAG in ('E', 'M', 'N')
501 AND POSTED_FLAG = 'Y'
502 ) DIST,
503 PA_Projects_ALL PAP,
504 PJM_Org_Parameters POP,
505 PO_Distributions_All POD
506 WHERE INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
507 AND POD.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
508 AND DIST.Invoice_Id = INV.Invoice_Id
509 AND (( l_Start_Date is null and l_End_Date is null)
510 OR ( l_Start_Date is not null and l_End_Date is not null
511 and DIST.Accounting_Date between l_Start_Date and l_End_Date)
512 OR ( l_Start_Date is not null and l_End_Date is null
513 and DIST.Accounting_Date >= l_Start_Date )
514 OR ( l_Start_Date is null and l_End_Date is not null
515 and DIST.Accounting_Date <= L_End_Date ))
516 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
517 AND DIST.po_distribution_id = POD.po_distribution_id
518 AND POP.Organization_Id = POD.Destination_Organization_Id
519 AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
520 AND PAP.Project_Id = X_Project_ID
521 ORDER BY 9,1,2
522 for update;
523
524 CURSOR SPC_WP_Curs IS
525 SELECT INV.Invoice_id Invoice_Id
526 , DIST.Invoice_Distribution_Id Invoice_Distribution_Id
527 , PAP.Org_Id Proj_Org_Id
528 , PAP.Project_Id Project_Id
529 , PAP.Segment1 Project_Number
530 , POD.Task_id Task_Id
531 , DIST.Accounting_Date Expenditure_Item_Date
532 , INV.Vendor_Id Vendor_Id
533 , INV.Created_By Created_By
534 , POD.Destination_Organization_Id Expenditure_Organization_Id
535 , POD.Org_Id Org_Id
536 , DIST.description Expenditure_Comment
537 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
538 , DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
539 , nvl( DIST.Accts_Pay_Code_Combination_Id
540 , INV.Accts_Pay_Code_Combination_Id)
541 Accts_Pay_Code_Combination_Id
542 , INV.Invoice_Currency_Code Invoice_Currency_Code
543 , INV.Exchange_Rate_Type Exchange_Rate_Type
544 , INV.Exchange_Date Exchange_Date
545 , INV.Exchange_Rate Exchange_Rate
546 , DIST.Amount Charge_Amount
547 , DIST.Base_Amount Base_Charge_Amount
548 , DIST.PO_Distribution_Id PO_Distribution_Id
549 , DIST.RCV_Transaction_Id RCV_Transaction_Id
550 , DIST.Line_Type_Lookup_Code Line_Type_Lookup_Code
551 FROM AP_Invoices_All INV,
552 (SELECT C.Invoice_Distribution_Id
553 , C.Invoice_Id
554 , P.Project_Id
555 , P.Task_Id
556 , C.Accounting_Date
557 , C.Expenditure_Organization_Id
558 , C.description
559 , C.Pa_Quantity
560 , C.Dist_Code_Combination_Id
561 , C.Accts_Pay_Code_Combination_Id
562 , C.Amount
563 , C.Base_Amount
564 , NVL(P.PO_Distribution_Id,(SELECT PO_Distribution_Id FROM AP_Invoice_Distributions_all P1
565 WHERE P1.invoice_distribution_id = P.charge_applicable_to_dist_id)) PO_Distribution_Id -- bugfix 7482789
566 , C.RCV_Transaction_Id
567 , C.Line_Type_Lookup_Code
568 FROM AP_Invoice_Distributions_all C, AP_Invoice_Distributions_all P
569 WHERE C.LINE_TYPE_LOOKUP_CODE IN ('FREIGHT','MISCELLANEOUS','NONREC_TAX') -- bugfix 7482789
570 AND C.PA_ADDITION_FLAG in ('E', 'M', 'N')
571 AND C.POSTED_FLAG = 'Y'
572 AND C.charge_applicable_to_dist_id = P.invoice_distribution_id
573 AND (P.charge_applicable_to_dist_id IS NOT NULL OR C.LINE_TYPE_LOOKUP_CODE IN ('FREIGHT','MISCELLANEOUS')) -- bugfix 7482789
574 ) DIST,
575 PA_Projects_ALL PAP,
576 PJM_Org_Parameters POP,
577 PO_Distributions_All POD
578 WHERE INV.INVOICE_TYPE_LOOKUP_CODE <> 'EXPENSE REPORT'
579 AND POD.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
580 AND DIST.Invoice_Id = INV.Invoice_Id
581 AND (( l_Start_Date is null and l_End_Date is null)
582 OR ( l_Start_Date is not null and l_End_Date is not null
583 and DIST.Accounting_Date between l_Start_Date and l_End_Date)
584 OR ( l_Start_Date is not null and l_End_Date is null
585 and DIST.Accounting_Date >= l_Start_Date )
586 OR ( l_Start_Date is null and l_End_Date is not null
587 and DIST.Accounting_Date <= L_End_Date ))
588 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
589 AND DIST.po_distribution_id = POD.po_distribution_id
590 AND POP.Organization_Id = POD.Destination_Organization_Id
591 AND PAP.Project_Id = NVL(POD.Project_Id , POP.Common_Project_Id)
592 AND PAP.Project_Id = X_Project_ID
593 ORDER BY 9,1,2
594 for update;
595
596 InvRec IPV_WP_Curs%ROWTYPE;
597
598 CURSOR Po_Data ( P_Distribution_ID NUMBER ) IS
599 SELECT POD.Destination_Type_Code
600 , POL.Item_ID
601 , POD.Bom_Resource_ID Wip_Resource_Id
602 , POD.Destination_Organization_ID
603 FROM po_distributions_all pod
604 , po_lines_all pol
605 WHERE POD.PO_Distribution_ID = P_Distribution_ID
606 AND POL.Po_line_ID = POD.Po_Line_ID;
607
608 PoRec Po_Data%ROWTYPE;
609 l_dummy NUMBER;
610
611
612 BEGIN
613
614 l_curr_invoice_id := -1;
615 l_first_invoice := TRUE;
616 l_progress := 0;
617 if (X_trx_status_code is NULL) then
618 l_trx_status_code := 'P';
619 else
620 l_trx_status_code := X_trx_status_code;
621 end if;
622 l_uom := 'DOLLARS'; -- bug 4145856
623
624
625 fnd_message.set_name('PJM','CONC-APINV IPV Transfer');
626 PJM_CONC.put_line(fnd_message.get || ' ...');
627 PJM_CONC.new_line(1);
628
629 PJM_CONC.put_line('[PROJECT_ID] = ' || X_Project_Id);
630 PJM_CONC.put_line('[START_DATE] = ' || X_Start_Date);
631 PJM_CONC.put_line('[END_DATE] = ' || X_End_Date);
632 PJM_CONC.put_line('[SUBMIT_TRX_IMPORT] = ' || X_Submit_Trx_Import);
633
634 l_request_id := fnd_global.conc_request_id;
635 l_user_id := fnd_global.user_id;
636 l_Start_Date := fnd_date.canonical_to_date(X_Start_Date);
637 l_End_Date := fnd_date.canonical_to_date(X_End_Date);
638
639 PJM_CONC.put_line('[REQUEST_ID] = ' || l_request_id);
640 PJM_CONC.new_line(1);
641
642 l_IPV_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV IPV');
643 l_ERV_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV ERV');
644 l_Freight_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV FREIGHT');
645 l_Tax_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV TAX');
646 l_Misc_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV MISC');
647 l_Batch_Name := Batch_Name;
648
649 PJM_CONC.put_line('Batch_Name = ' || l_batch_name);
650 PJM_CONC.new_line(1);
651
652 ----------------------------------------------------------------------
653 -- Loop for transfering Variances from Invoice_Distribution_All to
654 -- PA_Transaction_Interface_All
655 ----------------------------------------------------------------------
656
657 fnd_message.set_name('PJM','CONC-APINV Start Loop');
658 PJM_CONC.put_line(fnd_message.get || ' ...');
659
660 Timestamp;
661
662 if (x_project_id is not null) then
663
664 OPEN IPV_WP_Curs; -- Process IPV, ERV, and Tax Variances first
665
666 LOOP -- Start process data with project info
667
668 l_progress := 10;
669
670 FETCH IPV_WP_Curs INTO InvRec;
671 EXIT WHEN IPV_WP_Curs%NOTFOUND;
672
673 l_progress := 20;
674
675 ----------------------------------------------------------------------
676 -- Get Accounting Currency Code
677 ----------------------------------------------------------------------
678 if InvRec.Line_Type_Lookup_Code in ('ERV','TERV')
679 then
680 select ap.base_currency_code
681 into l_base_currency_code
682 from ap_system_parameters_all ap
683 where ap.org_id = InvRec.Org_Id;
684 else
685 l_base_currency_code := InvRec.Invoice_Currency_Code;
686 end if;
687
688 --------------------------------------------------------------------
689 -- Get PO Value
690 ---------------------------------------------------------------------
691
692 OPEN Po_Data ( InvRec.Po_Distribution_ID );
693 FETCH Po_Data INTO PoRec;
694 CLOSE Po_Data;
695
696
697 IF ( InvRec.Task_Id IS NOT NULL ) THEN
698 l_Task_Id := InvRec.Task_Id;
699 ELSE
700 l_Task_Id := Assign_Task( InvRec.PO_Distribution_Id
701 , PoRec.Destination_Type_Code
702 , InvRec.Project_Id );
703 END IF;
704
705 PJM_CONC.put_line(' invoice_id ............ '||InvRec.Invoice_Id);
706 PJM_CONC.put_line(' line_num .............. '||
707 InvRec.Invoice_Distribution_Id);
708 PJM_CONC.put_line(' project_id ............ '||InvRec.Project_ID);
709 PJM_CONC.put_line(' task_id ............... '||l_Task_Id);
710 PJM_CONC.put_line(' expenditure_org_id .... '||
711 InvRec.Expenditure_Organization_ID);
712
713 Timestamp;
714
715
716 l_curr_invoice_id := InvRec.Invoice_Id;
717 l_first_invoice := FALSE;
718
719 ----------------------------------------------------------------------
720 -- Get Expenditure Type
721 ----------------------------------------------------------------------
722
723 l_progress := 30;
724
725 l_exp_type := Get_Charges_Expenditure_Type
726 ( invrec.line_type_lookup_code
727 , invrec.project_id
728 , invrec.expenditure_organization_id );
729
730 PJM_CONC.put_line(' Invoice line type is .. '||invrec.line_type_lookup_code);
731 PJM_CONC.put_line(' Charge amount ............ '||
732 InvRec.charge_amount);
733 PJM_CONC.put_line(' Charge expenditure_type .. '||l_exp_type);
734 PJM_CONC.put_line(' expenditure_comment ... '||
735 InvRec.Expenditure_Comment);
736 PJM_CONC.new_line(1);
737
738
739 ---------------------------------------------------------------------
740 -- Set Expenditure Comment
741 ---------------------------------------------------------------------
742 l_progress := 40;
743
744 select decode(invrec.line_type_lookup_code, 'IPV', l_IPV_Exp_Comment,
745 'ERV', l_ERV_Exp_Comment,
746 'FREIGHT', l_Freight_Exp_Comment,
747 'TIPV', l_Tax_Exp_Comment,
748 'TERV', l_ERV_Exp_Comment,
749 'TRV', l_Tax_Exp_Comment,
750 'MISCELLANEOUS', l_Misc_Exp_Comment, null)
751 into l_exp_comment
752 from dual;
753
754
755 ----------------------------------------------------------------------
756 -- Getting Receipt Num from RCV_TRANSACTION_ID if exists
757 ----------------------------------------------------------------------
758
759 l_progress := 50;
760
761 if ( InvRec.RCV_Transaction_Id is not null ) then
762
763 SELECT rsh.receipt_num
764 INTO l_receipt_num
765 FROM rcv_shipment_headers rsh
766 , rcv_transactions rt
767 WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
768 AND rsh.shipment_header_id = rt.shipment_header_id;
769
770 end if;
771
772 ----------------------------------------------------------------------
773 -- Converting System RATE_TYPE to User RATE_TYPE if exists
774 ----------------------------------------------------------------------
775
776 l_progress := 60;
777
778 if ( InvRec.Exchange_Rate_Type is not null ) then
779
780 SELECT User_Conversion_Type
781 INTO l_User_Conv_Type
782 FROM gl_daily_conversion_types
783 WHERE conversion_type = InvRec.Exchange_Rate_Type;
784
785 else -- bug 4219497
786 l_user_conv_type := null;
787
788 end if;
789
790 ----------------------------------------------------------------------
791 -- Insert into PA_TRANSACTION_INTERFACES table
792 ----------------------------------------------------------------------
793
794 fnd_message.set_name('PJM','CONC-APINV Insert');
795 PJM_CONC.put_line('... ' || fnd_message.get);
796
797 if not ( InvRec.Dist_Code_Combination_Id is not null AND
798 nvl(nvl(InvRec.Base_Charge_Amount,
799 InvRec.Charge_Amount) , 0) <> 0 ) then
800
801 PJM_CONC.put_line('...... Charge amount not available, skipping...');
802 -- Mark skipped record to 'G'
803 UPDATE AP_Invoice_Distributions_all
804 SET pa_addition_flag = 'G'
805 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
806 from ap_invoice_distributions_all
807 where Invoice_Id = InvRec.Invoice_Id
808 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
809
810 elsif ( l_Exp_Type is null) then
811
812 PJM_CONC.put_line('...... Inv Org not setup to transfer charges, skipping...');
813 -- Mark skipped record to 'G'
814 UPDATE AP_Invoice_Distributions_all
815 SET pa_addition_flag = 'G'
816 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
817 from ap_invoice_distributions_all
818 where Invoice_Id = InvRec.Invoice_Id
819 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
820
821 else
822
823 BEGIN
824
825 l_progress := 70;
826
827 ---------------------------------------------------------------------
828 -- For Blue Print org, setting Transaction Source according to
829 -- destination_type_code, pa_posting_flag and pa_autoaccounting_flag
830 ---------------------------------------------------------------------
831
832 select NVL(pa_posting_flag,'N'),
833 NVL(pa_autoaccounting_flag,'N')
834 into l_blue_print_enabled_flag,
835 l_autoaccounting_flag
836 from pjm_org_parameters
837 where organization_id = InvRec.Expenditure_Organization_Id;
838
839 if PoRec.destination_type_code = 'INVENTORY' then -- bug 4184314
840 l_linkage := 'INV';
841 If l_blue_print_enabled_flag = 'Y' then
842
843 If l_autoaccounting_flag = 'Y' then
844 /* BP and autoaccounting */
845 l_transaction_source := 'PJM_CSTBP_INV_NO_ACCOUNTS';
846 else
847 /* BP and no autoaccounting -- Send Account to PA */
848 l_transaction_source := 'PJM_CSTBP_INV_ACCOUNTS';
849
850 end if; /* end of check for auto accounting */
851
852 else
853 l_transaction_source := 'Inventory';
854 end if;
855
856 elsif PoRec.destination_type_code = 'SHOP FLOOR' then
857 l_linkage := 'WIP';
858 If l_blue_print_enabled_flag = 'Y' then
859 If l_autoaccounting_flag = 'Y' then
860 /* BP and autoaccounting */
861 l_transaction_source := 'PJM_CSTBP_WIP_NO_ACCOUNTS';
862 else
863 /* BP and no autoaccounting -- Send Account to PA */
864 l_transaction_source := 'PJM_CSTBP_WIP_ACCOUNTS';
865
866 end if; /* end of check for auto accounting */
867 else
868 l_transaction_source := 'Work In Progress';
869
870 end if;
871
872 END IF; /* check for BP org */
873
874
875 -------------------------------------------------
876 -- Set the denom amount for bug 4169096
877 -------------------------------------------------
878
879 if InvRec.Line_Type_Lookup_Code in ('ERV','TERV')
880 then
881 l_denom_raw_cost := InvRec.Base_Charge_Amount;
882 l_acct_raw_cost := InvRec.Base_Charge_Amount;
883 if l_blue_print_enabled_flag = 'Y'
884 then
885 l_denom_burdened_cost := NULL;
886 l_acct_burdened_cost := NULL;
887 else
888 l_denom_burdened_cost := InvRec.Base_Charge_Amount;
889 l_acct_burdened_cost := InvRec.Base_Charge_Amount;
890 end if;
891 else
892 l_denom_raw_cost := InvRec.Charge_Amount;
893 select nvl(InvRec.Base_Charge_Amount, InvRec.Charge_Amount) into l_acct_raw_cost from dual;
894 if l_blue_print_enabled_flag = 'Y'
895 then
896 l_denom_burdened_cost := NULL;
897 l_acct_burdened_cost := NULL;
898 else
899 l_denom_burdened_cost := InvRec.Charge_Amount;
900 l_acct_burdened_cost := l_acct_raw_cost;
901 end if;
902 end if;
903
904
905 -- Get Week Ending of Expenditure Item Date
906 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
907 into l_week_ending_day_index
908 FROM pa_implementations_all WHERE org_id = InvRec.Proj_Org_Id;
909
910 select to_char(to_date('01-01-1950','DD-MM-YYYY') + l_week_ending_day_index - 1, 'Day')
911 into l_week_ending_day from dual;
912
913 select next_day( trunc(InvRec.Expenditure_Item_Date)-1, l_week_ending_day )
914 into l_week_ending
915 from dual;
916
917 PJM_CONC.put_line('...... Processing IPV, ERV, Tax Variances');
918
919 -- Insert for all the charges
920
921 INSERT INTO pa_transaction_interface_all
922 (transaction_source,
923 batch_name,
924 expenditure_ending_date,
925 employee_number,
926 organization_name,
927 expenditure_item_date,
928 project_number,
929 task_number,
930 expenditure_type,
931 quantity,
932 expenditure_comment,
933 orig_transaction_reference,
934 unmatched_negative_txn_flag,
935 dr_code_combination_id,
936 cr_code_combination_id,
937 orig_exp_txn_reference1,
938 orig_exp_txn_reference2,
939 orig_exp_txn_reference3,
940 gl_date,
941 system_linkage,
942 transaction_status_code,
943 denom_currency_code,
944 denom_raw_cost,
945 denom_burdened_cost,
946 acct_rate_date,
947 acct_rate_type,
948 acct_exchange_rate,
949 acct_raw_cost,
950 acct_burdened_cost,
951 creation_date,
952 created_by,
953 last_update_date,
954 last_updated_by,
955 inventory_item_id,
956 unit_of_measure,
957 wip_resource_id,
958 org_id
959 )
960 SELECT
961 l_transaction_source
962 , l_Batch_Name
963 , l_week_ending --pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
964 , NULL
965 , ORG.Name
966 , InvRec.Expenditure_Item_Date
967 , InvRec.Project_Number
968 , TASK.Task_Number
969 , l_Exp_Type
970 , InvRec.PA_Quantity
971 , NVL( InvRec.Expenditure_Comment , l_Exp_Comment )
972 , DIST.Invoice_Distribution_Id
973 , 'Y'
974 , InvRec.Dist_Code_Combination_Id
975 , InvRec.Accts_Pay_Code_Combination_Id
976 , InvRec.PO_Distribution_Id
977 , InvRec.RCV_Transaction_Id
978 , l_receipt_num
979 , DIST.Accounting_Date
980 , l_linkage
981 , l_trx_status_code
982 , l_base_currency_code /* denom_currency_code */
983 , l_denom_raw_cost /* denom_raw_cost */
984 , l_denom_burdened_cost /* denom_burdened_cost */
985 , InvRec.Exchange_Date /* acct_rate_date */
986 , l_User_Conv_Type /* acct_rate_type */
987 , InvRec.Exchange_Rate /* acct_exchange_rate */
988 , l_acct_raw_cost /* acct_raw_cost */
989 , l_acct_burdened_cost /* acct_burdened_cost */
990 , SYSDATE
991 , l_user_id
992 , SYSDATE
993 , l_user_id
994 , PoRec.item_id
995 , l_uom
996 , PoRec.wip_resource_id
997 , InvRec.Org_Id
998 FROM
999 AP_Invoice_Distributions_all DIST
1000 , PA_Tasks TASK
1001 , HR_Organization_Units ORG
1002 WHERE
1003 DIST.Invoice_Id = InvRec.Invoice_Id
1004 AND DIST.Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id
1005 AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
1006 AND TASK.Task_Id = l_Task_Id;
1007
1008
1009 ----------------------------------------------------------------------
1010 -- Update pa_addition_flag to 'Y' for successful invoice distributions
1011 ----------------------------------------------------------------------
1012
1013 fnd_message.set_name('PJM','CONC-APINV Flag Comp');
1014 PJM_CONC.put_line('... ' || fnd_message.get);
1015 PJM_CONC.new_line(1);
1016
1017 l_progress := 80;
1018
1019 UPDATE AP_Invoice_Distributions_all
1020 SET Pa_Addition_Flag = 'Y',
1021 Request_Id = l_request_id
1022 WHERE Invoice_Id = InvRec.Invoice_Id
1023 AND Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id;
1024
1025 EXCEPTION
1026 WHEN NO_DATA_FOUND THEN
1027 NULL;
1028 WHEN DUP_VAL_ON_INDEX THEN
1029 NULL;
1030 END;
1031
1032 end if;
1033
1034 END LOOP; -- End process data with project info
1035
1036 CLOSE IPV_WP_Curs;
1037
1038 OPEN SPC_WP_Curs; -- Process Freight and Misc charges
1039
1040 LOOP -- Start process special charges with project info
1041
1042 l_progress := 81;
1043
1044 FETCH SPC_WP_Curs INTO InvRec;
1045 EXIT WHEN SPC_WP_Curs%NOTFOUND;
1046
1047 l_progress := 82;
1048
1049 ----------------------------------------------------------------------
1050 -- Get Accounting Currency Code
1051 ----------------------------------------------------------------------
1052 l_base_currency_code := InvRec.Invoice_Currency_Code;
1053
1054
1055 --------------------------------------------------------------------
1056 -- Get PO Value
1057 ---------------------------------------------------------------------
1058
1059 OPEN Po_Data ( InvRec.Po_Distribution_ID );
1060 FETCH Po_Data INTO PoRec;
1061 CLOSE Po_Data;
1062
1063
1064 IF ( InvRec.Task_Id IS NOT NULL ) THEN
1065 l_Task_Id := InvRec.Task_Id;
1066 ELSE
1067 l_Task_Id := Assign_Task( InvRec.PO_Distribution_Id
1068 , PoRec.Destination_Type_Code
1069 , InvRec.Project_Id );
1070 END IF;
1071
1072 PJM_CONC.put_line(' invoice_id ............ '||InvRec.Invoice_Id);
1073 PJM_CONC.put_line(' line_num .............. '||
1074 InvRec.Invoice_Distribution_Id);
1075 PJM_CONC.put_line(' project_id ............ '||InvRec.Project_ID);
1076 PJM_CONC.put_line(' task_id ............... '||l_Task_Id);
1077 PJM_CONC.put_line(' expenditure_org_id .... '||
1078 InvRec.Expenditure_Organization_ID);
1079
1080 Timestamp;
1081
1082
1083 l_curr_invoice_id := InvRec.Invoice_Id;
1084 l_first_invoice := FALSE;
1085
1086 ----------------------------------------------------------------------
1087 -- Get Expenditure Type
1088 ----------------------------------------------------------------------
1089
1090 l_progress := 83;
1091
1092 l_exp_type := Get_Charges_Expenditure_Type
1093 ( invrec.line_type_lookup_code
1094 , invrec.project_id
1095 , invrec.expenditure_organization_id );
1096
1097 PJM_CONC.put_line(' Invoice line type is .. '||invrec.line_type_lookup_code);
1098 PJM_CONC.put_line(' Charge amount ............ '||
1099 InvRec.charge_amount);
1100 PJM_CONC.put_line(' Charge expenditure_type .. '||l_exp_type);
1101 PJM_CONC.put_line(' expenditure_comment ... '||
1102 InvRec.Expenditure_Comment);
1103 PJM_CONC.new_line(1);
1104
1105
1106 ---------------------------------------------------------------------
1107 -- Set Expenditure Comment
1108 ---------------------------------------------------------------------
1109 l_progress := 84;
1110
1111 select decode(invrec.line_type_lookup_code, 'IPV', l_IPV_Exp_Comment,
1112 'ERV', l_ERV_Exp_Comment,
1113 'FREIGHT', l_Freight_Exp_Comment,
1114 'TIPV', l_Tax_Exp_Comment,
1115 'TERV', l_ERV_Exp_Comment,
1116 'TRV', l_Tax_Exp_Comment,
1117 'MISCELLANEOUS', l_Misc_Exp_Comment,
1118 'NONREC_TAX', l_Tax_Exp_Comment,null) --bugfix 7482789
1119 into l_exp_comment
1120 from dual;
1121
1122
1123 ----------------------------------------------------------------------
1124 -- Getting Receipt Num from RCV_TRANSACTION_ID if exists
1125 ----------------------------------------------------------------------
1126
1127 l_progress := 85;
1128
1129 if ( InvRec.RCV_Transaction_Id is not null ) then
1130
1131 SELECT rsh.receipt_num
1132 INTO l_receipt_num
1133 FROM rcv_shipment_headers rsh
1134 , rcv_transactions rt
1135 WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
1136 AND rsh.shipment_header_id = rt.shipment_header_id;
1137
1138 end if;
1139
1140 ----------------------------------------------------------------------
1141 -- Converting System RATE_TYPE to User RATE_TYPE if exists
1142 ----------------------------------------------------------------------
1143
1144 l_progress := 86;
1145
1146 if ( InvRec.Exchange_Rate_Type is not null ) then
1147
1148 SELECT User_Conversion_Type
1149 INTO l_User_Conv_Type
1150 FROM gl_daily_conversion_types
1151 WHERE conversion_type = InvRec.Exchange_Rate_Type;
1152
1153 else -- bug 4219497
1154 l_user_conv_type := null;
1155
1156 end if;
1157
1158 ----------------------------------------------------------------------
1159 -- Insert into PA_TRANSACTION_INTERFACES table
1160 ----------------------------------------------------------------------
1161
1162 fnd_message.set_name('PJM','CONC-APINV Insert');
1163 PJM_CONC.put_line('... ' || fnd_message.get);
1164
1165 if not ( InvRec.Dist_Code_Combination_Id is not null AND
1166 nvl(nvl(InvRec.Base_Charge_Amount,
1167 InvRec.Charge_Amount) , 0) <> 0 ) then
1168
1169 PJM_CONC.put_line('...... Charge amount not available, skipping...');
1170 -- Mark skipped record to 'G'
1171 UPDATE AP_Invoice_Distributions_all
1172 SET pa_addition_flag = 'G'
1173 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
1174 from ap_invoice_distributions_all
1175 where Invoice_Id = InvRec.Invoice_Id
1176 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
1177
1178 elsif ( l_Exp_Type is null) then
1179
1180 PJM_CONC.put_line('...... Inv Org not setup to transfer charges, skipping...');
1181 -- Mark skipped record to 'G'
1182 UPDATE AP_Invoice_Distributions_all
1183 SET pa_addition_flag = 'G'
1184 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
1185 from ap_invoice_distributions_all
1186 where Invoice_Id = InvRec.Invoice_Id
1187 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
1188
1189 else
1190
1191 BEGIN
1192
1193 l_progress := 87;
1194
1195 ---------------------------------------------------------------------
1196 -- For Blue Print org, setting Transaction Source according to
1197 -- destination_type_code, pa_posting_flag and pa_autoaccounting_flag
1198 ---------------------------------------------------------------------
1199
1200 select NVL(pa_posting_flag,'N'),
1201 NVL(pa_autoaccounting_flag,'N')
1202 into l_blue_print_enabled_flag,
1203 l_autoaccounting_flag
1204 from pjm_org_parameters
1205 where organization_id = InvRec.Expenditure_Organization_Id;
1206
1207 if PoRec.destination_type_code = 'INVENTORY' then -- bug 4184314
1208 l_linkage := 'INV';
1209 If l_blue_print_enabled_flag = 'Y' then
1210
1211 If l_autoaccounting_flag = 'Y' then
1212 /* BP and autoaccounting */
1213 l_transaction_source := 'PJM_CSTBP_INV_NO_ACCOUNTS';
1214 else
1215 /* BP and no autoaccounting -- Send Account to PA */
1216 l_transaction_source := 'PJM_CSTBP_INV_ACCOUNTS';
1217
1218 end if; /* end of check for auto accounting */
1219
1220 else
1221 l_transaction_source := 'Inventory';
1222 end if;
1223
1224 elsif PoRec.destination_type_code = 'SHOP FLOOR' then
1225 l_linkage := 'WIP';
1226 If l_blue_print_enabled_flag = 'Y' then
1227 If l_autoaccounting_flag = 'Y' then
1228 /* BP and autoaccounting */
1229 l_transaction_source := 'PJM_CSTBP_WIP_NO_ACCOUNTS';
1230 else
1231 /* BP and no autoaccounting -- Send Account to PA */
1232 l_transaction_source := 'PJM_CSTBP_WIP_ACCOUNTS';
1233
1234 end if; /* end of check for auto accounting */
1235 else
1236 l_transaction_source := 'Work In Progress';
1237
1238 end if;
1239
1240 END IF; /* check for BP org */
1241
1242
1243 -------------------------------------------------
1244 -- Set the denom amount for bug 4169096
1245 -------------------------------------------------
1246
1247 if InvRec.Line_Type_Lookup_Code in ('ERV','TERV')
1248 then
1249 l_denom_raw_cost := InvRec.Base_Charge_Amount;
1250 l_acct_raw_cost := InvRec.Base_Charge_Amount;
1251 if l_blue_print_enabled_flag = 'Y'
1252 then
1253 l_denom_burdened_cost := NULL;
1254 l_acct_burdened_cost := NULL;
1255 else
1256 l_denom_burdened_cost := InvRec.Base_Charge_Amount;
1257 l_acct_burdened_cost := InvRec.Base_Charge_Amount;
1258 end if;
1259 else
1260 l_denom_raw_cost := InvRec.Charge_Amount;
1261 select nvl(InvRec.Base_Charge_Amount, InvRec.Charge_Amount) into l_acct_raw_cost from dual;
1262 if l_blue_print_enabled_flag = 'Y'
1263 then
1264 l_denom_burdened_cost := NULL;
1265 l_acct_burdened_cost := NULL;
1266 else
1267 l_denom_burdened_cost := InvRec.Charge_Amount;
1268 l_acct_burdened_cost := l_acct_raw_cost;
1269 end if;
1270 end if;
1271
1272
1273 -- Get Week Ending of Expenditure Item Date
1274 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
1275 into l_week_ending_day_index
1276 FROM pa_implementations_all WHERE org_id = InvRec.Proj_Org_Id;
1277
1278 select to_char(to_date('01-01-1950','DD-MM-YYYY') + l_week_ending_day_index - 1, 'Day')
1279 into l_week_ending_day from dual;
1280
1281 select next_day( trunc(InvRec.Expenditure_Item_Date)-1, l_week_ending_day )
1282 into l_week_ending
1283 from dual;
1284
1285 PJM_CONC.put_line('...... Processing Special Charge');
1286
1287 -- Insert for all the charges
1288
1289 INSERT INTO pa_transaction_interface_all
1290 (transaction_source,
1291 batch_name,
1292 expenditure_ending_date,
1293 employee_number,
1294 organization_name,
1295 expenditure_item_date,
1296 project_number,
1297 task_number,
1298 expenditure_type,
1299 quantity,
1300 expenditure_comment,
1301 orig_transaction_reference,
1302 unmatched_negative_txn_flag,
1303 dr_code_combination_id,
1304 cr_code_combination_id,
1305 orig_exp_txn_reference1,
1306 orig_exp_txn_reference2,
1307 orig_exp_txn_reference3,
1308 gl_date,
1309 system_linkage,
1310 transaction_status_code,
1311 denom_currency_code,
1312 denom_raw_cost,
1313 denom_burdened_cost,
1314 acct_rate_date,
1315 acct_rate_type,
1316 acct_exchange_rate,
1317 acct_raw_cost,
1318 acct_burdened_cost,
1319 creation_date,
1320 created_by,
1321 last_update_date,
1322 last_updated_by,
1323 inventory_item_id,
1324 unit_of_measure,
1325 wip_resource_id,
1326 org_id
1327 )
1328 SELECT
1329 l_transaction_source
1330 , l_Batch_Name
1331 , l_week_ending --pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
1332 , NULL
1333 , ORG.Name
1334 , InvRec.Expenditure_Item_Date
1335 , InvRec.Project_Number
1336 , TASK.Task_Number
1337 , l_Exp_Type
1338 , InvRec.PA_Quantity
1339 , NVL( InvRec.Expenditure_Comment , l_Exp_Comment )
1340 , DIST.Invoice_Distribution_Id
1341 , 'Y'
1342 , InvRec.Dist_Code_Combination_Id
1343 , InvRec.Accts_Pay_Code_Combination_Id
1344 , InvRec.PO_Distribution_Id
1345 , InvRec.RCV_Transaction_Id
1346 , l_receipt_num
1347 , DIST.Accounting_Date
1348 , l_linkage
1349 , l_trx_status_code
1350 , l_base_currency_code /* denom_currency_code */
1351 , l_denom_raw_cost /* denom_raw_cost */
1352 , l_denom_burdened_cost /* denom_burdened_cost */
1353 , InvRec.Exchange_Date /* acct_rate_date */
1354 , l_User_Conv_Type /* acct_rate_type */
1355 , InvRec.Exchange_Rate /* acct_exchange_rate */
1356 , l_acct_raw_cost /* acct_raw_cost */
1357 , l_acct_burdened_cost /* acct_burdened_cost */
1358 , SYSDATE
1359 , l_user_id
1360 , SYSDATE
1361 , l_user_id
1362 , PoRec.item_id
1363 , l_uom
1364 , PoRec.wip_resource_id
1365 , InvRec.Org_Id
1366 FROM
1367 AP_Invoice_Distributions_all DIST
1368 , PA_Tasks TASK
1369 , HR_Organization_Units ORG
1370 WHERE
1371 DIST.Invoice_Id = InvRec.Invoice_Id
1372 AND DIST.Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id
1373 AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
1374 AND TASK.Task_Id = l_Task_Id;
1375
1376
1377 ----------------------------------------------------------------------
1378 -- Update pa_addition_flag to 'Y' for successful invoice distributions
1379 ----------------------------------------------------------------------
1380
1381 fnd_message.set_name('PJM','CONC-APINV Flag Comp');
1382 PJM_CONC.put_line('... ' || fnd_message.get);
1383 PJM_CONC.new_line(1);
1384
1385 l_progress := 88;
1386
1387 UPDATE AP_Invoice_Distributions_all
1388 SET Pa_Addition_Flag = 'Y',
1389 Request_Id = l_request_id
1390 WHERE Invoice_Id = InvRec.Invoice_Id
1391 AND Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id;
1392
1393 EXCEPTION
1394 WHEN NO_DATA_FOUND THEN
1395 NULL;
1396 WHEN DUP_VAL_ON_INDEX THEN
1397 NULL;
1398 END;
1399
1400 end if;
1401
1402 END LOOP; -- End process special charges with project info
1403
1404 CLOSE SPC_WP_Curs;
1405
1406 else -- without project specified
1407
1408 OPEN IPV_NP_Curs;
1409
1410 LOOP -- Start process IPV/ERV/Tax without project specified
1411
1412 l_progress := 90;
1413
1414 FETCH IPV_NP_Curs INTO InvRec;
1415 EXIT WHEN IPV_NP_Curs%NOTFOUND;
1416
1417 l_progress := 100;
1418
1419 --------------------------------------------------------------------
1420 -- Get PO Value
1421 ---------------------------------------------------------------------
1422
1423 OPEN Po_Data ( InvRec.Po_Distribution_ID );
1424 FETCH Po_Data INTO PoRec;
1425 CLOSE Po_Data;
1426
1427 ----------------------------------------------------------------------
1428 -- Get Accounting Currency Code
1429 ----------------------------------------------------------------------
1430 if InvRec.Line_Type_Lookup_Code in ('ERV','TERV')
1431 then
1432 select ap.base_currency_code
1433 into l_base_currency_code
1434 from ap_system_parameters_all ap
1435 where ap.org_id = InvRec.Org_Id;
1436 else
1437 l_base_currency_code := InvRec.Invoice_Currency_Code;
1438 end if;
1439
1440
1441 IF ( InvRec.Task_Id IS NOT NULL ) THEN
1442 l_Task_Id := InvRec.Task_Id;
1443 ELSE
1444 l_Task_Id := Assign_Task( InvRec.PO_Distribution_Id
1445 , PoRec.Destination_Type_Code
1446 , InvRec.Project_Id );
1447 END IF;
1448
1449 PJM_CONC.put_line(' invoice_id ............ '||InvRec.Invoice_Id);
1450 PJM_CONC.put_line(' line_num .............. '||
1451 InvRec.Invoice_Distribution_Id);
1452 PJM_CONC.put_line(' project_id ............ '||InvRec.Project_ID);
1453 PJM_CONC.put_line(' task_id ............... '||l_Task_Id);
1454 PJM_CONC.put_line(' expenditure_org_id .... '||
1455 InvRec.Expenditure_Organization_ID);
1456
1457 Timestamp;
1458
1459
1460 l_curr_invoice_id := InvRec.Invoice_Id;
1461 l_first_invoice := FALSE;
1462
1463 ----------------------------------------------------------------------
1464 -- Get Expenditure Type
1465 ----------------------------------------------------------------------
1466
1467 l_progress := 110;
1468
1469 l_exp_type := Get_Charges_Expenditure_Type
1470 ( invrec.line_type_lookup_code
1471 , invrec.project_id
1472 , invrec.expenditure_organization_id );
1473
1474 PJM_CONC.put_line(' Invoice line type is .. '||invrec.line_type_lookup_code);
1475 PJM_CONC.put_line(' Charge amount ............ '||
1476 InvRec.charge_amount);
1477 PJM_CONC.put_line(' Charge expenditure_type .. '||l_exp_type);
1478 PJM_CONC.put_line(' expenditure_comment ... '||
1479 InvRec.Expenditure_Comment);
1480 PJM_CONC.new_line(1);
1481
1482 ---------------------------------------------------------------------
1483 -- Set Expenditure Comment
1484 ---------------------------------------------------------------------
1485 l_progress := 120;
1486
1487 select decode(invrec.line_type_lookup_code, 'IPV', l_IPV_Exp_Comment,
1488 'ERV', l_ERV_Exp_Comment,
1489 'FREIGHT', l_Freight_Exp_Comment,
1490 'TAX', l_Tax_Exp_Comment,
1491 'TIPV', l_Tax_Exp_Comment,
1492 'TERV', l_Tax_Exp_Comment,
1493 'TRV', l_Tax_Exp_Comment,
1494 'MISCELLANEOUS', l_Misc_Exp_Comment, null)
1495 into l_exp_comment
1496 from dual;
1497
1498 ----------------------------------------------------------------------
1499 -- Getting Receipt Num from RCV_TRANSACTION_ID if exists
1500 ----------------------------------------------------------------------
1501
1502 l_progress := 130;
1503
1504 if ( InvRec.RCV_Transaction_Id is not null ) then
1505
1506 SELECT rsh.receipt_num
1507 INTO l_receipt_num
1508 FROM rcv_shipment_headers rsh
1509 , rcv_transactions rt
1510 WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
1511 AND rsh.shipment_header_id = rt.shipment_header_id;
1512
1513 end if;
1514
1515 ----------------------------------------------------------------------
1516 -- Converting System RATE_TYPE to User RATE_TYPE if exists
1517 ----------------------------------------------------------------------
1518
1519 l_progress := 140;
1520
1521 if ( InvRec.Exchange_Rate_Type is not null ) then
1522
1523 SELECT User_Conversion_Type
1524 INTO l_User_Conv_Type
1525 FROM gl_daily_conversion_types
1526 WHERE conversion_type = InvRec.Exchange_Rate_Type;
1527
1528 else -- bug 4219497
1529 l_user_conv_type := null;
1530
1531 end if;
1532
1533 ----------------------------------------------------------------------
1534 -- Insert into PA_TRANSACTION_INTERFACES table
1535 ----------------------------------------------------------------------
1536
1537 fnd_message.set_name('PJM','CONC-APINV Insert');
1538 PJM_CONC.put_line('... ' || fnd_message.get);
1539
1540 if not ( InvRec.Dist_Code_Combination_Id is not null AND
1541 nvl(nvl(InvRec.Base_Charge_Amount,
1542 InvRec.Charge_Amount) , 0) <> 0 ) then
1543
1544 PJM_CONC.put_line('...... Charge amount not available, skipping...');
1545 -- Mark skipped record to 'G'
1546 UPDATE AP_Invoice_Distributions_all
1547 SET pa_addition_flag = 'G'
1548 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
1549 from ap_invoice_distributions_all
1550 where Invoice_Id = InvRec.Invoice_Id
1551 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
1552
1553 elsif ( l_Exp_Type is null) then
1554
1555 PJM_CONC.put_line('...... Inv Org not setup to transfer charges, skipping...');
1556 -- Mark skipped record to 'G'
1557 UPDATE AP_Invoice_Distributions_all
1558 SET pa_addition_flag = 'G'
1559 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
1560 from ap_invoice_distributions_all
1561 where Invoice_Id = InvRec.Invoice_Id
1562 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
1563
1564 else -- Start proecess
1565
1566 BEGIN
1567
1568 l_progress := 150;
1569
1570 ---------------------------------------------------------------------
1571 -- For Blue Print org, setting Transaction Source , system linkage
1572 -- according to pa_posting_flag and pa_autoaccounting_flag
1573 ---------------------------------------------------------------------
1574
1575 select NVL(pa_posting_flag,'N'),
1576 NVL(pa_autoaccounting_flag,'N')
1577 into l_blue_print_enabled_flag,
1578 l_autoaccounting_flag
1579 from pjm_org_parameters
1580 where organization_id = InvRec.Expenditure_Organization_Id;
1581
1582 if PoRec.destination_type_code = 'INVENTORY' then
1583 l_linkage := 'INV';
1584 If l_blue_print_enabled_flag = 'Y' then
1585
1586 If l_autoaccounting_flag = 'Y' then
1587 /* BP and autoaccounting */
1588 l_transaction_source := 'PJM_CSTBP_INV_NO_ACCOUNTS';
1589 else
1590 /* BP and no autoaccounting -- Send Account to PA */
1591 l_transaction_source := 'PJM_CSTBP_INV_ACCOUNTS';
1592
1593 end if; /* end of check for auto accounting */
1594
1595 else
1596 l_transaction_source := 'Inventory';
1597 end if;
1598
1599 elsif PoRec.destination_type_code = 'SHOP FLOOR' then
1600 l_linkage := 'WIP';
1601 If l_blue_print_enabled_flag = 'Y' then
1602 If l_autoaccounting_flag = 'Y' then
1603 /* BP and autoaccounting */
1604 l_transaction_source := 'PJM_CSTBP_WIP_NO_ACCOUNTS';
1605 else
1606 /* BP and no autoaccounting -- Send Account to PA */
1607 l_transaction_source := 'PJM_CSTBP_WIP_ACCOUNTS';
1608
1609 end if; /* end of check for auto accounting */
1610 else
1611 l_transaction_source := 'Work In Progress';
1612
1613 end if;
1614
1615 END IF; /* check for BP org */
1616
1617
1618 -------------------------------------------------
1619 -- Set the denom amount for bug 4169096
1620 -------------------------------------------------
1621
1622 if InvRec.Line_Type_Lookup_Code in ('ERV','TERV')
1623 then
1624 l_denom_raw_cost := InvRec.Base_Charge_Amount;
1625 l_acct_raw_cost := InvRec.Base_Charge_Amount;
1626 if l_blue_print_enabled_flag = 'Y'
1627 then
1628 l_denom_burdened_cost := NULL;
1629 l_acct_burdened_cost := NULL;
1630 else
1631 l_denom_burdened_cost := InvRec.Base_Charge_Amount;
1632 l_acct_burdened_cost := InvRec.Base_Charge_Amount;
1633 end if;
1634 else
1635 l_denom_raw_cost := InvRec.Charge_Amount;
1636 select nvl(InvRec.Base_Charge_Amount, InvRec.Charge_Amount) into l_acct_raw_cost from dual;
1637 if l_blue_print_enabled_flag = 'Y'
1638 then
1639 l_denom_burdened_cost := NULL;
1640 l_acct_burdened_cost := NULL;
1641 else
1642 l_denom_burdened_cost := InvRec.Charge_Amount;
1643 l_acct_burdened_cost := l_acct_raw_cost;
1644 end if;
1645 end if;
1646
1647
1648 -- Get Week Ending of Expenditure Item Date
1649 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
1650 into l_week_ending_day_index
1651 FROM pa_implementations_all WHERE org_id = InvRec.Proj_Org_Id;
1652
1653 select to_char(to_date('01-01-1950','DD-MM-YYYY') + l_week_ending_day_index - 1, 'Day')
1654 into l_week_ending_day from dual;
1655
1656 select next_day( trunc(InvRec.Expenditure_Item_Date)-1, l_week_ending_day )
1657 into l_week_ending
1658 from dual;
1659
1660 PJM_CONC.put_line('...... Processing IPV, ERV, Tax Variances');
1661
1662 -- Insert for Charges
1663 INSERT INTO pa_transaction_interface_all
1664 (transaction_source,
1665 batch_name,
1666 expenditure_ending_date,
1667 employee_number,
1668 organization_name,
1669 expenditure_item_date,
1670 project_number,
1671 task_number,
1672 expenditure_type,
1673 quantity,
1674 expenditure_comment,
1675 orig_transaction_reference,
1676 unmatched_negative_txn_flag,
1677 dr_code_combination_id,
1678 cr_code_combination_id,
1679 orig_exp_txn_reference1,
1680 orig_exp_txn_reference2,
1681 orig_exp_txn_reference3,
1682 gl_date,
1683 system_linkage,
1684 transaction_status_code,
1685 denom_currency_code,
1686 denom_raw_cost,
1687 denom_burdened_cost,
1688 acct_rate_date,
1689 acct_rate_type,
1690 acct_exchange_rate,
1691 acct_raw_cost,
1692 acct_burdened_cost,
1693 creation_date,
1694 created_by,
1695 last_update_date,
1696 last_updated_by,
1697 Inventory_Item_Id,
1698 Unit_Of_Measure,
1699 Wip_Resource_Id,
1700 Org_Id
1701 )
1702 SELECT
1703 l_transaction_source
1704 , l_Batch_Name
1705 , l_week_ending -- pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
1706 , NULL
1707 , ORG.Name
1708 , InvRec.Expenditure_Item_Date
1709 , InvRec.Project_Number
1710 , TASK.Task_Number
1711 , l_Exp_Type
1712 , InvRec.PA_Quantity
1713 , NVL( InvRec.Expenditure_Comment , l_Exp_Comment )
1714 , DIST.Invoice_Distribution_Id
1715 , 'Y'
1716 , InvRec.Dist_Code_Combination_Id
1717 , InvRec.Accts_Pay_Code_Combination_Id
1718 , InvRec.PO_Distribution_Id
1719 , InvRec.RCV_Transaction_Id
1720 , l_receipt_num
1721 , DIST.Accounting_Date
1722 , l_linkage
1723 , l_trx_status_code
1724 , l_base_currency_code /* denom_currency_code */
1725 , l_denom_raw_cost /* denom_raw_cost */
1726 , l_denom_burdened_cost /* denom_burdened_cost */
1727 , InvRec.Exchange_Date /* acct_rate_date */
1728 , l_User_Conv_Type /* acct_rate_type */
1729 , InvRec.Exchange_Rate /* acct_exchange_rate */
1730 , l_acct_raw_cost /* acct_raw_cost */
1731 , l_acct_burdened_cost /* acct_burdened_cost */
1732 , SYSDATE
1733 , l_user_id
1734 , SYSDATE
1735 , l_user_id
1736 , PoRec.Item_Id
1737 , l_uom
1738 , PoRec.Wip_Resource_Id
1739 , InvRec.Org_Id
1740 FROM
1741 AP_Invoice_Distributions_all DIST
1742 , PA_Tasks TASK
1743 , HR_Organization_Units ORG
1744 WHERE
1745 DIST.Invoice_Id = InvRec.Invoice_Id
1746 AND DIST.Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id
1747 -- AND DIST.PA_Addition_Flag = 'S'
1748 AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
1749 AND TASK.Task_Id = l_Task_Id;
1750
1751 ----------------------------------------------------------------------
1752 -- Update pa_addition_flag to 'Y' for successful invoice distributions
1753 ----------------------------------------------------------------------
1754
1755 fnd_message.set_name('PJM','CONC-APINV Flag Comp');
1756 PJM_CONC.put_line('... ' || fnd_message.get);
1757 PJM_CONC.new_line(1);
1758
1759 l_progress := 160;
1760
1761 UPDATE AP_Invoice_Distributions_all
1762 SET Pa_Addition_Flag = 'Y',
1763 Request_Id = l_request_id
1764 WHERE Invoice_Id = InvRec.Invoice_Id
1765 AND Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id;
1766
1767 EXCEPTION
1768 WHEN NO_DATA_FOUND THEN
1769 NULL;
1770 WHEN DUP_VAL_ON_INDEX THEN
1771 NULL;
1772 END;
1773
1774 end if; -- no project parameter
1775
1776 END LOOP; -- End process data without project specified
1777
1778 CLOSE IPV_NP_Curs;
1779
1780 OPEN SPC_NP_Curs;
1781
1782 LOOP -- Start process Freight and Misc charge without project specified
1783
1784 l_progress := 161;
1785
1786 FETCH SPC_NP_Curs INTO InvRec;
1787 EXIT WHEN SPC_NP_Curs%NOTFOUND;
1788
1789 l_progress := 162;
1790
1791 ----------------------------------------------------------------------
1792 -- Get Accounting Currency Code
1793 ----------------------------------------------------------------------
1794 l_base_currency_code := InvRec.Invoice_Currency_Code;
1795
1796 --------------------------------------------------------------------
1797 -- Get PO Value
1798 ---------------------------------------------------------------------
1799
1800 OPEN Po_Data ( InvRec.Po_Distribution_ID );
1801 FETCH Po_Data INTO PoRec;
1802 CLOSE Po_Data;
1803
1804
1805 IF ( InvRec.Task_Id IS NOT NULL ) THEN
1806 l_Task_Id := InvRec.Task_Id;
1807 ELSE
1808 l_Task_Id := Assign_Task( InvRec.PO_Distribution_Id
1809 , PoRec.Destination_Type_Code
1810 , InvRec.Project_Id );
1811 END IF;
1812
1813 PJM_CONC.put_line(' invoice_id ............ '||InvRec.Invoice_Id);
1814 PJM_CONC.put_line(' line_num .............. '||
1815 InvRec.Invoice_Distribution_Id);
1816 PJM_CONC.put_line(' project_id ............ '||InvRec.Project_ID);
1817 PJM_CONC.put_line(' task_id ............... '||l_Task_Id);
1818 PJM_CONC.put_line(' expenditure_org_id .... '||
1819 InvRec.Expenditure_Organization_ID);
1820
1821 Timestamp;
1822
1823
1824 l_curr_invoice_id := InvRec.Invoice_Id;
1825 l_first_invoice := FALSE;
1826
1827 ----------------------------------------------------------------------
1828 -- Get Expenditure Type
1829 ----------------------------------------------------------------------
1830
1831 l_progress := 163;
1832
1833 l_exp_type := Get_Charges_Expenditure_Type
1834 ( invrec.line_type_lookup_code
1835 , invrec.project_id
1836 , invrec.expenditure_organization_id );
1837
1838 PJM_CONC.put_line(' Invoice line type is .. '||invrec.line_type_lookup_code);
1839 PJM_CONC.put_line(' Charge amount ............ '||
1840 InvRec.charge_amount);
1841 PJM_CONC.put_line(' Charge expenditure_type .. '||l_exp_type);
1842 PJM_CONC.put_line(' expenditure_comment ... '||
1843 InvRec.Expenditure_Comment);
1844 PJM_CONC.new_line(1);
1845
1846 ---------------------------------------------------------------------
1847 -- Set Expenditure Comment
1848 ---------------------------------------------------------------------
1849 l_progress := 164;
1850
1851 select decode(invrec.line_type_lookup_code, 'IPV', l_IPV_Exp_Comment,
1852 'ERV', l_ERV_Exp_Comment,
1853 'FREIGHT', l_Freight_Exp_Comment,
1854 'TAX', l_Tax_Exp_Comment,
1855 'TIPV', l_Tax_Exp_Comment,
1856 'TERV', l_Tax_Exp_Comment,
1857 'TRV', l_Tax_Exp_Comment,
1858 'MISCELLANEOUS', l_Misc_Exp_Comment,
1859 'NONREC_TAX', l_Tax_Exp_Comment,null) --bugfix 7482789
1860 into l_exp_comment
1861 from dual;
1862
1863 ----------------------------------------------------------------------
1864 -- Getting Receipt Num from RCV_TRANSACTION_ID if exists
1865 ----------------------------------------------------------------------
1866
1867 l_progress := 165;
1868
1869 if ( InvRec.RCV_Transaction_Id is not null ) then
1870
1871 SELECT rsh.receipt_num
1872 INTO l_receipt_num
1873 FROM rcv_shipment_headers rsh
1874 , rcv_transactions rt
1875 WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
1876 AND rsh.shipment_header_id = rt.shipment_header_id;
1877
1878 end if;
1879
1880 ----------------------------------------------------------------------
1881 -- Converting System RATE_TYPE to User RATE_TYPE if exists
1882 ----------------------------------------------------------------------
1883
1884 l_progress := 166;
1885
1886 if ( InvRec.Exchange_Rate_Type is not null ) then
1887
1888 SELECT User_Conversion_Type
1889 INTO l_User_Conv_Type
1890 FROM gl_daily_conversion_types
1891 WHERE conversion_type = InvRec.Exchange_Rate_Type;
1892
1893 else -- bug 4219497
1894 l_user_conv_type := null;
1895
1896 end if;
1897
1898 ----------------------------------------------------------------------
1899 -- Insert into PA_TRANSACTION_INTERFACES table
1900 ----------------------------------------------------------------------
1901
1902 fnd_message.set_name('PJM','CONC-APINV Insert');
1903 PJM_CONC.put_line('... ' || fnd_message.get);
1904
1905 if not ( InvRec.Dist_Code_Combination_Id is not null AND
1906 nvl(nvl(InvRec.Base_Charge_Amount,
1907 InvRec.Charge_Amount) , 0) <> 0 ) then
1908
1909 PJM_CONC.put_line('...... Charge amount not available, skipping...');
1910 -- Mark skipped record to 'G'
1911 UPDATE AP_Invoice_Distributions_all
1912 SET pa_addition_flag = 'G'
1913 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
1914 from ap_invoice_distributions_all
1915 where Invoice_Id = InvRec.Invoice_Id
1916 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
1917
1918 elsif ( l_Exp_Type is null) then
1919
1920 PJM_CONC.put_line('...... Inv Org not setup to transfer charges, skipping...');
1921 -- Mark skipped record to 'G'
1922 UPDATE AP_Invoice_Distributions_all
1923 SET pa_addition_flag = 'G'
1924 WHERE invoice_distribution_id = ( select nvl(related_id, charge_applicable_to_dist_id)
1925 from ap_invoice_distributions_all
1926 where Invoice_Id = InvRec.Invoice_Id
1927 and Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id);
1928
1929 else -- Start proecess
1930
1931 BEGIN
1932
1933 l_progress := 167;
1934
1935 ---------------------------------------------------------------------
1936 -- For Blue Print org, setting Transaction Source , system linkage
1937 -- according to pa_posting_flag and pa_autoaccounting_flag
1938 ---------------------------------------------------------------------
1939
1940 select NVL(pa_posting_flag,'N'),
1941 NVL(pa_autoaccounting_flag,'N')
1942 into l_blue_print_enabled_flag,
1943 l_autoaccounting_flag
1944 from pjm_org_parameters
1945 where organization_id = InvRec.Expenditure_Organization_Id;
1946
1947 if PoRec.destination_type_code = 'INVENTORY' then
1948 l_linkage := 'INV';
1949 If l_blue_print_enabled_flag = 'Y' then
1950
1951 If l_autoaccounting_flag = 'Y' then
1952 /* BP and autoaccounting */
1953 l_transaction_source := 'PJM_CSTBP_INV_NO_ACCOUNTS';
1954 else
1955 /* BP and no autoaccounting -- Send Account to PA */
1956 l_transaction_source := 'PJM_CSTBP_INV_ACCOUNTS';
1957
1958 end if; /* end of check for auto accounting */
1959
1960 else
1961 l_transaction_source := 'Inventory';
1962 end if;
1963
1964 elsif PoRec.destination_type_code = 'SHOP FLOOR' then
1965 l_linkage := 'WIP';
1966 If l_blue_print_enabled_flag = 'Y' then
1967 If l_autoaccounting_flag = 'Y' then
1968 /* BP and autoaccounting */
1969 l_transaction_source := 'PJM_CSTBP_WIP_NO_ACCOUNTS';
1970 else
1971 /* BP and no autoaccounting -- Send Account to PA */
1972 l_transaction_source := 'PJM_CSTBP_WIP_ACCOUNTS';
1973
1974 end if; /* end of check for auto accounting */
1975 else
1976 l_transaction_source := 'Work In Progress';
1977
1978 end if;
1979
1980 END IF; /* check for BP org */
1981
1982
1983 -------------------------------------------------
1984 -- Set the denom amount for bug 4169096
1985 -------------------------------------------------
1986
1987 if InvRec.Line_Type_Lookup_Code in ('ERV','TERV')
1988 then
1989 l_denom_raw_cost := InvRec.Base_Charge_Amount;
1990 l_acct_raw_cost := InvRec.Base_Charge_Amount;
1991 if l_blue_print_enabled_flag = 'Y'
1992 then
1993 l_denom_burdened_cost := NULL;
1994 l_acct_burdened_cost := NULL;
1995 else
1996 l_denom_burdened_cost := InvRec.Base_Charge_Amount;
1997 l_acct_burdened_cost := InvRec.Base_Charge_Amount;
1998 end if;
1999 else
2000 l_denom_raw_cost := InvRec.Charge_Amount;
2001 select nvl(InvRec.Base_Charge_Amount, InvRec.Charge_Amount) into l_acct_raw_cost from dual;
2002 if l_blue_print_enabled_flag = 'Y'
2003 then
2004 l_denom_burdened_cost := NULL;
2005 l_acct_burdened_cost := NULL;
2006 else
2007 l_denom_burdened_cost := InvRec.Charge_Amount;
2008 l_acct_burdened_cost := l_acct_raw_cost;
2009 end if;
2010 end if;
2011
2012
2013 -- Get Week Ending of Expenditure Item Date
2014 SELECT decode( exp_cycle_start_day_code, 1, 8, exp_cycle_start_day_code )-1
2015 into l_week_ending_day_index
2016 FROM pa_implementations_all WHERE org_id = InvRec.Proj_Org_Id;
2017
2018 select to_char(to_date('01-01-1950','DD-MM-YYYY') + l_week_ending_day_index - 1, 'Day')
2019 into l_week_ending_day from dual;
2020
2021 select next_day( trunc(InvRec.Expenditure_Item_Date)-1, l_week_ending_day )
2022 into l_week_ending
2023 from dual;
2024
2025 PJM_CONC.put_line('...... Processing Special Charges');
2026
2027 -- Insert for Charges
2028 INSERT INTO pa_transaction_interface_all
2029 (transaction_source,
2030 batch_name,
2031 expenditure_ending_date,
2032 employee_number,
2033 organization_name,
2034 expenditure_item_date,
2035 project_number,
2036 task_number,
2037 expenditure_type,
2038 quantity,
2039 expenditure_comment,
2040 orig_transaction_reference,
2041 unmatched_negative_txn_flag,
2042 dr_code_combination_id,
2043 cr_code_combination_id,
2044 orig_exp_txn_reference1,
2045 orig_exp_txn_reference2,
2046 orig_exp_txn_reference3,
2047 gl_date,
2048 system_linkage,
2049 transaction_status_code,
2050 denom_currency_code,
2051 denom_raw_cost,
2052 denom_burdened_cost,
2053 acct_rate_date,
2054 acct_rate_type,
2055 acct_exchange_rate,
2056 acct_raw_cost,
2057 acct_burdened_cost,
2058 creation_date,
2059 created_by,
2060 last_update_date,
2061 last_updated_by,
2062 Inventory_Item_Id,
2063 Unit_Of_Measure,
2064 Wip_Resource_Id,
2065 Org_Id
2066 )
2067 SELECT
2068 l_transaction_source
2069 , l_Batch_Name
2070 , l_week_ending -- pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
2071 , NULL
2072 , ORG.Name
2073 , InvRec.Expenditure_Item_Date
2074 , InvRec.Project_Number
2075 , TASK.Task_Number
2076 , l_Exp_Type
2077 , InvRec.PA_Quantity
2078 , NVL( InvRec.Expenditure_Comment , l_Exp_Comment )
2079 , DIST.Invoice_Distribution_Id
2080 , 'Y'
2081 , InvRec.Dist_Code_Combination_Id
2082 , InvRec.Accts_Pay_Code_Combination_Id
2083 , InvRec.PO_Distribution_Id
2084 , InvRec.RCV_Transaction_Id
2085 , l_receipt_num
2086 , DIST.Accounting_Date
2087 , l_linkage
2088 , l_trx_status_code
2089 , l_base_currency_code /* denom_currency_code */
2090 , l_denom_raw_cost /* denom_raw_cost */
2091 , l_denom_burdened_cost /* denom_burdened_cost */
2092 , InvRec.Exchange_Date /* acct_rate_date */
2093 , l_User_Conv_Type /* acct_rate_type */
2094 , InvRec.Exchange_Rate /* acct_exchange_rate */
2095 , l_acct_raw_cost /* acct_raw_cost */
2096 , l_acct_burdened_cost /* acct_burdened_cost */
2097 , SYSDATE
2098 , l_user_id
2099 , SYSDATE
2100 , l_user_id
2101 , PoRec.Item_Id
2102 , l_uom
2103 , PoRec.Wip_Resource_Id
2104 , InvRec.Org_Id
2105 FROM
2106 AP_Invoice_Distributions_all DIST
2107 , PA_Tasks TASK
2108 , HR_Organization_Units ORG
2109 WHERE
2110 DIST.Invoice_Id = InvRec.Invoice_Id
2111 AND DIST.Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id
2112 -- AND DIST.PA_Addition_Flag = 'S'
2113 AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
2114 AND TASK.Task_Id = l_Task_Id;
2115
2116 ----------------------------------------------------------------------
2117 -- Update pa_addition_flag to 'Y' for successful invoice distributions
2118 ----------------------------------------------------------------------
2119
2120 fnd_message.set_name('PJM','CONC-APINV Flag Comp');
2121 PJM_CONC.put_line('... ' || fnd_message.get);
2122 PJM_CONC.new_line(1);
2123
2124 l_progress := 168;
2125
2126 UPDATE AP_Invoice_Distributions_all
2127 SET Pa_Addition_Flag = 'Y',
2128 Request_Id = l_request_id
2129 WHERE Invoice_Id = InvRec.Invoice_Id
2130 AND Invoice_Distribution_Id = InvRec.Invoice_Distribution_Id;
2131
2132 EXCEPTION
2133 WHEN NO_DATA_FOUND THEN
2134 NULL;
2135 WHEN DUP_VAL_ON_INDEX THEN
2136 NULL;
2137 END;
2138
2139 end if; -- no project parameter
2140
2141 END LOOP; -- End process special charge without project specified
2142
2143 CLOSE SPC_NP_Curs;
2144
2145
2146 END IF; -- End of both with project specified or without conditions
2147
2148 COMMIT;
2149 fnd_message.set_name('PJM','CONC-APINV Finish Loop');
2150 PJM_CONC.put_line(fnd_message.get || ' ...');
2151 PJM_CONC.new_line(1);
2152
2153 Timestamp;
2154
2155 l_progress := 169;
2156
2157 if (X_Submit_Trx_Import = 'Y') then
2158 l_imp_req_id := fnd_request.submit_request('PA','PAXTRTRX',
2159 'PRC: Transaction Import',
2160 NULL, FALSE,
2161 l_transaction_source,
2162 l_Batch_Name);
2163 end if;
2164
2165 retcode := PJM_CONC.G_conc_success;
2166 return;
2167
2168
2169 EXCEPTION
2170 when OTHERS then
2171 errbuf := 'IPV-'||l_progress||': '||sqlerrm;
2172 retcode := PJM_CONC.G_conc_failure;
2173 return;
2174
2175 END Transfer_Charges_TO_PA;
2176
2177
2178 END PJM_TRANSFER_CHARGES_PKG;