[Home] [Help]
PACKAGE BODY: APPS.PJM_TRANSFER_SPEC_CHARGES_PKG
Source
1 PACKAGE BODY PJM_TRANSFER_SPEC_CHARGES_PKG AS
2 /* $Header: PJMTSPCB.pls 115.31 2004/08/18 19:24:10 yliou ship $ */
3
4 --
5 -- Private Functions and Procedures
6 --
7 FUNCTION Assign_Task
8 ( X_PO_Distribution_Id IN NUMBER
9 , X_Destination_Type_Code IN VARCHAR2
10 , X_Project_Id IN NUMBER
11 ) RETURN VARCHAR2 IS
12
13 CURSOR c_inv IS
14 SELECT PJM_TASK_AUTO_ASSIGN.Inv_Task_WNPS
15 ( POD.Destination_Organization_Id
16 , X_Project_Id
17 , POL.Item_Id
18 , POD.Po_Header_Id
19 , Null
20 , Null )
21 FROM PO_Distributions POD
22 , PO_Lines POL
23 WHERE POD.PO_Distribution_Id = X_PO_Distribution_Id
24 AND POL.PO_Line_Id = POD.PO_Line_Id;
25
26 CURSOR c_wip IS
27 SELECT PJM_TASK_AUTO_ASSIGN.WIP_Task_WNPS
28 ( wo.organization_id
29 , X_Project_Id
30 , wo.standard_operation_id
31 , wdj.wip_entity_id
32 , wdj.primary_item_id
33 , wo.department_id )
34 FROM PO_Distributions POD
35 , WIP_Discrete_Jobs WDJ
36 , WIP_Operations WO
37 WHERE POD.PO_Distribution_Id = X_PO_Distribution_Id
38 AND WO.WIP_Entity_Id = POD.WIP_Entity_Id
39 AND WO.Operation_Seq_Num = POD.WIP_Operation_Seq_Num
40 AND WDJ.WIP_Entity_Id = WO.Wip_Entity_Id;
41
42 L_Task_ID NUMBER;
43
44 BEGIN
45
46 L_Task_ID := NULL;
47
48 IF ( X_Destination_Type_Code = 'INVENTORY' ) THEN
49
50 OPEN c_inv;
51 FETCH c_inv INTO L_Task_ID;
52 CLOSE c_inv;
53
54 ELSIF ( X_Destination_Type_Code = 'SHOP FLOOR' ) THEN
55
56 OPEN c_wip;
57 FETCH c_wip INTO L_Task_ID;
58 CLOSE c_wip;
59
60 END IF;
61
62 RETURN ( L_Task_ID );
63
64 END Assign_Task;
65
66
67 PROCEDURE Timestamp IS
68 Current_Time DATE;
69 BEGIN
70 Current_Time := sysdate;
71 fnd_message_cache.set_name('FND' , 'UTIL-CURRENT TIME');
72 fnd_message_cache.set_token('DATE' , fnd_date.date_to_displaydate(Current_Time));
73 fnd_message_cache.set_token('TIME' , to_char(Current_Time , 'HH24:MI:SS'));
74 PJM_CONC.put_line(fnd_message_cache.get);
75 PJM_CONC.new_line(1);
76 EXCEPTION
77 WHEN OTHERS THEN
78 NULL;
79 END Timestamp;
80
81
82 ---------------------------------------------------------------------------
83 -- PUBLIC PROCEDURE
84 -- Transfer_Spec_Charges_to_PA
85 --
86 -- DESCRIPTION
87 -- This procedure will get the expenditure and costing data for
88 -- Freight, Tax, and other special chargs from AP invoices with
89 -- destination type of INVENTORY or SHOP FLOOR, and push these
90 -- data to PA_TRANSACTION_INTERFACES.
91 --
92 -- PARAMETERS
93 -- X_Project_Id IN
94 -- X_Start_Date IN
95 -- X_End_Date IN
96 -- ERRBUF OUT
97 -- RETCODE OUT
98 --
99 ---------------------------------------------------------------------------
100
101 PROCEDURE Transfer_Spec_Charges_to_PA
102 ( ERRBUF OUT NOCOPY VARCHAR2
103 , RETCODE OUT NOCOPY NUMBER
104 , X_Project_Id IN NUMBER
105 , X_Start_Date IN VARCHAR2
106 , X_End_Date IN VARCHAR2
107 , X_Submit_Trx_Import IN VARCHAR2
108 , X_Trx_Status_Code IN VARCHAR2
109 ) IS
110
111 l_proj_status VARCHAR2(30);
112 l_billable_flag VARCHAR2(1);
113 l_request_id NUMBER;
114 l_user_id NUMBER;
115 l_expenditure_type VARCHAR2(30);
116 l_curr_invoice_id NUMBER;
117 l_first_invoice BOOLEAN;
118 l_imp_req_id NUMBER;
119 l_base_currency_code AP_System_parameters.base_currency_code%TYPE;
120
121 -- l_msg_application VARCHAR2(30) := 'PA';
122 -- l_msg_type VARCHAR2(30);
123 -- l_msg_token1 VARCHAR2(30);
124 -- l_msg_token2 VARCHAR2(30);
125 -- l_msg_token3 VARCHAR2(30);
126 -- l_msg_count NUMBER;
127
128 l_Freight_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
129 l_Tax_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
130 l_Misc_Exp_Comment PA_Transaction_Interface_All.Expenditure_Comment%TYPE;
131 l_Batch_Name PA_Transaction_Interface_All.Batch_Name%TYPE;
132 l_Receipt_Num RCV_Shipment_Headers.Receipt_Num%TYPE;
133 l_User_Conv_Type GL_Daily_Conversion_Types.User_Conversion_Type%TYPE;
134 l_Start_Date DATE;
135 l_End_Date DATE;
136 l_Task_Id NUMBER;
137
138 l_progress NUMBER;
139 l_blue_print_enabled_flag VARCHAR2(1);
140 l_autoaccounting_flag VARCHAR2(1);
141 l_transaction_source VARCHAR2(30);
142 l_trx_status_code VARCHAR2(30);
143
144 CURSOR Inv_WP_Curs IS
145 SELECT
146 INV.Invoice_id Invoice_Id
147 , DIST.Distribution_Line_Number Distribution_Line_Number
148 , DIST.Amount Amount
149 , DIST.Base_Amount Base_Amount
150 , PAP.Project_Id Project_Id
151 , PAP.Segment1 Project_Number
152 , POD.Task_id Task_Id
153 , DIST.Accounting_Date Expenditure_Item_Date
154 , INV.Vendor_Id Vendor_Id
155 , INV.Created_By Created_By
156 , POD.Destination_Organization_Id Expenditure_Organization_Id
157 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
158 , DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
159 , nvl( DIST.Accts_Pay_Code_Combination_Id
160 , INV.Accts_Pay_Code_Combination_Id)
161 Accts_Pay_Code_Combination_Id
162 , INV.Invoice_Currency_Code Invoice_Currency_Code
163 , INV.Exchange_Rate_Type Exchange_Rate_Type
164 , INV.Exchange_Date Exchange_Date
165 , INV.Exchange_Rate Exchange_Rate
166 , DIST.Line_Type_Lookup_Code Distribution_Type
167 , POD.Po_Distribution_Id Po_Distribution_Id
168 , POD.Destination_Type_Code Destination_Type_Code
169 , DIST.RCV_Transaction_Id RCV_Transaction_Id
170 FROM
171 AP_Invoices INV
172 , (
173 SELECT
174 AID.Invoice_Id
175 , AID.Distribution_Line_Number
176 , nvl(AID.invoice_price_variance,AID.Amount) Amount
177 , nvl(AID.base_invoice_price_variance,AID.Base_Amount) Base_Amount
178 , AID.Accounting_Date
179 , AID.Pa_Quantity
180 , AID.Dist_Code_Combination_Id
181 , AID.Accts_Pay_Code_Combination_Id
182 , AID.Line_Type_Lookup_Code
183 , AID.RCV_Transaction_Id
184 , AID.Po_Distribution_Id
185 FROM AP_Invoice_Distributions AID
186 WHERE NOT EXISTS (
187 SELECT 'x'
188 FROM AP_Invoice_Distributions AID2
189 WHERE AID2.Invoice_Id = AID.Invoice_ID
190 AND AID2.Line_Type_Lookup_Code = 'ITEM' )
191 AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
192 AND AID.Posted_Flag = 'Y'
193 AND AID.pa_addition_flag in
194 ( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
195 , 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
196 AND (( l_Start_Date is null and l_End_Date is null)
197 OR ( l_Start_Date is not null and l_End_Date is not null
198 and AID.Accounting_Date between l_Start_Date and l_End_Date)
199 OR ( l_Start_Date is not null and l_End_Date is null
200 and AID.Accounting_Date >= l_Start_Date )
201 OR ( l_Start_Date is null and l_End_Date is not null
202 and AID.Accounting_Date <= l_End_Date ))
203 UNION ALL
204 SELECT
205 AID.Invoice_Id
206 , AID.Distribution_Line_Number
207 , nvl(AID.invoice_price_variance,ACA.Allocated_Amount) Amount
208 , nvl(AID.base_invoice_price_variance,ACA.Allocated_Base_Amount) Base_Amount
209 , AID.Accounting_Date
210 , AID.Pa_Quantity
211 , AID.Dist_Code_Combination_Id
212 , AID.Accts_Pay_Code_Combination_Id
213 , AID.Line_Type_Lookup_Code
214 , AID2.RCV_Transaction_Id
215 , AID2.Po_Distribution_Id
216 FROM AP_Invoice_Distributions AID
217 , AP_Invoice_Distributions AID2
218 , AP_Chrg_Allocations ACA
219 WHERE EXISTS (
220 SELECT 'x'
221 FROM AP_Invoice_Distributions
222 WHERE Invoice_Id = AID.Invoice_ID
223 AND Line_Type_Lookup_Code = 'ITEM' )
224 AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
225 AND AID.Posted_Flag = 'Y'
226 AND AID.pa_addition_flag in
227 ( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
228 , 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
229 AND (( l_Start_Date is null and l_End_Date is null)
230 OR ( l_Start_Date is not null and l_End_Date is not null
231 and AID.Accounting_Date between l_Start_Date and l_End_Date)
232 OR ( l_Start_Date is not null and l_End_Date is null
233 and AID.Accounting_Date >= l_Start_Date )
234 OR ( l_Start_Date is null and l_End_Date is not null
235 and AID.Accounting_Date <= l_End_Date ))
236 AND AID.Invoice_Distribution_Id = ACA.Charge_Dist_Id
237 AND ACA.Item_Dist_Id = AID2.Invoice_Distribution_Id
238 ) DIST
239 , PO_Distributions POD
240 , PA_Projects_ALL PAP
241 WHERE DIST.Invoice_Id = INV.Invoice_Id
242 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
243 AND POD.Destination_Type_Code IN ( 'INVENTORY' , 'SHOP FLOOR' )
244 AND PAP.Project_Id = POD.Project_Id
245 AND PAP.Project_Id = X_Project_Id
246 AND DIST.Po_Distribution_Id = POD.Po_Distribution_Id
247 UNION ALL
248 SELECT
249 INV.Invoice_id Invoice_Id
250 , DIST.Distribution_Line_Number Distribution_Line_Number
251 , DIST.Amount Amount
252 , DIST.Base_Amount Base_Amount
253 , PAP.Project_Id Project_Id
254 , PAP.Segment1 Project_Number
255 , POD.Task_id Task_Id
256 , DIST.Accounting_Date Expenditure_Item_Date
257 , INV.Vendor_Id Vendor_Id
258 , INV.Created_By Created_By
259 , POD.Destination_Organization_Id Expenditure_Organization_Id
260 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
261 , DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
262 , nvl( DIST.Accts_Pay_Code_Combination_Id
263 , INV.Accts_Pay_Code_Combination_Id)
264 Accts_Pay_Code_Combination_Id
265 , INV.Invoice_Currency_Code Invoice_Currency_Code
266 , INV.Exchange_Rate_Type Exchange_Rate_Type
267 , INV.Exchange_Date Exchange_Date
268 , INV.Exchange_Rate Exchange_Rate
269 , DIST.Line_Type_Lookup_Code Distribution_Type
270 , POD.Po_Distribution_Id Po_Distribution_Id
271 , POD.Destination_Type_Code Destination_Type_Code
272 , DIST.RCV_Transaction_Id RCV_Transaction_Id
273 FROM
274 AP_Invoices INV
275 , (
276 SELECT
277 AID.Invoice_Id
278 , AID.Distribution_Line_Number
279 , nvl(AID.invoice_price_variance,AID.Amount) Amount
280 , nvl(AID.base_invoice_price_variance,AID.Base_Amount) Base_Amount
281 , AID.Accounting_Date
282 , AID.Pa_Quantity
283 , AID.Dist_Code_Combination_Id
284 , AID.Accts_Pay_Code_Combination_Id
285 , AID.Line_Type_Lookup_Code
286 , AID.RCV_Transaction_Id
287 , AID.Po_Distribution_Id
288 FROM AP_Invoice_Distributions AID
289 WHERE NOT EXISTS (
290 SELECT 'x'
291 FROM AP_Invoice_Distributions AID2
292 WHERE AID2.Invoice_Id = AID.Invoice_ID
293 AND AID2.Line_Type_Lookup_Code = 'ITEM' )
294 AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
295 AND AID.Posted_Flag = 'Y'
296 AND AID.pa_addition_flag in
297 ( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
298 , 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
299 AND (( l_Start_Date is null and l_End_Date is null)
300 OR ( l_Start_Date is not null and l_End_Date is not null
301 and AID.Accounting_Date between l_Start_Date and l_End_Date)
302 OR ( l_Start_Date is not null and l_End_Date is null
303 and AID.Accounting_Date >= l_Start_Date )
304 OR ( l_Start_Date is null and l_End_Date is not null
305 and AID.Accounting_Date <= l_End_Date ))
306 UNION ALL
307 SELECT
308 AID.Invoice_Id
309 , AID.Distribution_Line_Number
310 , nvl(AID.invoice_price_variance,ACA.Allocated_Amount) Amount
311 , nvl(AID.base_invoice_price_variance,ACA.Allocated_Base_Amount) Base_Amount
312 , AID.Accounting_Date
313 , AID.Pa_Quantity
314 , AID.Dist_Code_Combination_Id
315 , AID.Accts_Pay_Code_Combination_Id
316 , AID.Line_Type_Lookup_Code
317 , AID2.RCV_Transaction_Id
318 , AID2.Po_Distribution_Id
319 FROM AP_Invoice_Distributions AID
320 , AP_Invoice_Distributions AID2
321 , AP_Chrg_Allocations ACA
322 WHERE EXISTS (
323 SELECT 'x'
324 FROM AP_Invoice_Distributions
325 WHERE Invoice_Id = AID.Invoice_ID
326 AND Line_Type_Lookup_Code = 'ITEM' )
327 AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
328 AND AID.Posted_Flag = 'Y'
329 AND AID.pa_addition_flag in
330 ( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
331 , 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
332 AND (( l_Start_Date is null and l_End_Date is null)
333 OR ( l_Start_Date is not null and l_End_Date is not null
334 and AID.Accounting_Date between l_Start_Date and l_End_Date)
335 OR ( l_Start_Date is not null and l_End_Date is null
336 and AID.Accounting_Date >= l_Start_Date )
337 OR ( l_Start_Date is null and l_End_Date is not null
338 and AID.Accounting_Date <= l_End_Date ))
339 AND AID.Invoice_Distribution_Id = ACA.Charge_Dist_Id
340 AND ACA.Item_Dist_Id = AID2.Invoice_Distribution_Id
341 ) DIST
342 , PO_Distributions POD
343 , PA_Projects_ALL PAP
344 , PJM_Org_Parameters POP
345 WHERE DIST.Invoice_Id = INV.Invoice_Id
346 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
347 AND POD.Destination_Type_Code IN ( 'INVENTORY' , 'SHOP FLOOR' )
348 AND POP.Organization_Id = POD.Destination_Organization_Id
349 AND PAP.Project_Id = POP.Common_Project_Id
350 AND POD.Project_Id is null
351 AND PAP.Project_Id = X_Project_Id
352 AND DIST.Po_Distribution_Id = POD.Po_Distribution_Id
353 ORDER BY 9,1,2;
354
355 CURSOR Inv_NP_Curs IS
356 SELECT
357 INV.Invoice_id Invoice_Id
358 , DIST.Distribution_Line_Number Distribution_Line_Number
359 , DIST.Amount Amount
360 , DIST.Base_Amount Base_Amount
361 , PAP.Project_Id Project_Id
362 , PAP.Segment1 Project_Number
363 , POD.Task_id Task_Id
364 , DIST.Accounting_Date Expenditure_Item_Date
365 , INV.Vendor_Id Vendor_Id
366 , INV.Created_By Created_By
367 , POD.Destination_Organization_Id Expenditure_Organization_Id
368 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
369 , DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
370 , nvl( DIST.Accts_Pay_Code_Combination_Id
371 , INV.Accts_Pay_Code_Combination_Id)
372 Accts_Pay_Code_Combination_Id
373 , INV.Invoice_Currency_Code Invoice_Currency_Code
374 , INV.Exchange_Rate_Type Exchange_Rate_Type
375 , INV.Exchange_Date Exchange_Date
376 , INV.Exchange_Rate Exchange_Rate
377 , DIST.Line_Type_Lookup_Code Distribution_Type
378 , POD.Po_Distribution_Id Po_Distribution_Id
379 , POD.Destination_Type_Code Destination_Type_Code
380 , DIST.RCV_Transaction_Id RCV_Transaction_Id
381 FROM
382 AP_Invoices INV
383 , (
384 SELECT
385 AID.Invoice_Id
386 , AID.Distribution_Line_Number
387 , nvl(AID.invoice_price_variance,AID.Amount) Amount
388 , nvl(AID.base_invoice_price_variance,AID.Base_Amount) Base_Amount
389 , AID.Accounting_Date
390 , AID.Pa_Quantity
391 , AID.Dist_Code_Combination_Id
392 , AID.Accts_Pay_Code_Combination_Id
393 , AID.Line_Type_Lookup_Code
394 , AID.RCV_Transaction_Id
395 , AID.Po_Distribution_Id
396 FROM AP_Invoice_Distributions AID
397 WHERE NOT EXISTS (
398 SELECT 'x'
399 FROM AP_Invoice_Distributions AID2
400 WHERE AID2.Invoice_Id = AID.Invoice_ID
401 AND AID2.Line_Type_Lookup_Code = 'ITEM' )
402 AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
403 AND AID.Posted_Flag = 'Y'
404 AND AID.pa_addition_flag in
405 ( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
406 , 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
407 AND (( l_Start_Date is null and l_End_Date is null)
408 OR ( l_Start_Date is not null and l_End_Date is not null
409 and AID.Accounting_Date between l_Start_Date and l_End_Date)
410 OR ( l_Start_Date is not null and l_End_Date is null
411 and AID.Accounting_Date >= l_Start_Date )
412 OR ( l_Start_Date is null and l_End_Date is not null
413 and AID.Accounting_Date <= l_End_Date ))
414 UNION ALL
415 SELECT
416 AID.Invoice_Id
417 , AID.Distribution_Line_Number
418 , nvl(AID.invoice_price_variance,ACA.Allocated_Amount) Amount
419 , nvl(AID.base_invoice_price_variance,ACA.Allocated_Base_Amount) Base_Amount
420 , AID.Accounting_Date
421 , AID.Pa_Quantity
422 , AID.Dist_Code_Combination_Id
423 , AID.Accts_Pay_Code_Combination_Id
424 , AID.Line_Type_Lookup_Code
425 , AID2.RCV_Transaction_Id
426 , AID2.Po_Distribution_Id
427 FROM AP_Invoice_Distributions AID
428 , AP_Invoice_Distributions AID2
429 , AP_Chrg_Allocations ACA
430 WHERE EXISTS (
431 SELECT 'x'
432 FROM AP_Invoice_Distributions
433 WHERE Invoice_Id = AID.Invoice_ID
434 AND Line_Type_Lookup_Code = 'ITEM' )
435 AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
436 AND AID.Posted_Flag = 'Y'
437 AND AID.pa_addition_flag in
438 ( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
439 , 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
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 AID.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 AID.Accounting_Date >= l_Start_Date )
445 OR ( l_Start_Date is null and l_End_Date is not null
446 and AID.Accounting_Date <= l_End_Date ))
447 AND AID.Invoice_Distribution_Id = ACA.Charge_Dist_Id
448 AND ACA.Item_Dist_Id = AID2.Invoice_Distribution_Id
449 ) DIST
450 , PO_Distributions POD
451 , PA_Projects_ALL PAP
452 WHERE DIST.Invoice_Id = INV.Invoice_Id
453 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
454 AND POD.Destination_Type_Code IN ( 'INVENTORY' , 'SHOP FLOOR' )
455 AND PAP.Project_Id = POD.Project_Id
456 AND DIST.Po_Distribution_Id = POD.Po_Distribution_Id
457 UNION ALL
458 SELECT
459 INV.Invoice_id Invoice_Id
460 , DIST.Distribution_Line_Number Distribution_Line_Number
461 , DIST.Amount Amount
462 , DIST.Base_Amount Base_Amount
463 , PAP.Project_Id Project_Id
464 , PAP.Segment1 Project_Number
465 , POD.Task_id Task_Id
466 , DIST.Accounting_Date Expenditure_Item_Date
467 , INV.Vendor_Id Vendor_Id
468 , INV.Created_By Created_By
469 , POD.Destination_Organization_Id Expenditure_Organization_Id
470 , NVL(DIST.Pa_Quantity, 1 ) PA_Quantity
471 , DIST.Dist_Code_Combination_Id Dist_Code_Combination_Id
472 , nvl( DIST.Accts_Pay_Code_Combination_Id
473 , INV.Accts_Pay_Code_Combination_Id)
474 Accts_Pay_Code_Combination_Id
475 , INV.Invoice_Currency_Code Invoice_Currency_Code
476 , INV.Exchange_Rate_Type Exchange_Rate_Type
477 , INV.Exchange_Date Exchange_Date
478 , INV.Exchange_Rate Exchange_Rate
479 , DIST.Line_Type_Lookup_Code Distribution_Type
480 , POD.Po_Distribution_Id Po_Distribution_Id
481 , POD.Destination_Type_Code Destination_Type_Code
482 , DIST.RCV_Transaction_Id RCV_Transaction_Id
483 FROM
484 AP_Invoices INV
485 , (
486 SELECT
487 AID.Invoice_Id
488 , AID.Distribution_Line_Number
489 , nvl(AID.invoice_price_variance,AID.Amount) Amount
490 , nvl(AID.base_invoice_price_variance,AID.Base_Amount) Base_Amount
491 , AID.Accounting_Date
492 , AID.Pa_Quantity
493 , AID.Dist_Code_Combination_Id
494 , AID.Accts_Pay_Code_Combination_Id
495 , AID.Line_Type_Lookup_Code
496 , AID.RCV_Transaction_Id
497 , AID.Po_Distribution_Id
498 FROM AP_Invoice_Distributions AID
499 WHERE NOT EXISTS (
500 SELECT 'x'
501 FROM AP_Invoice_Distributions AID2
502 WHERE AID2.Invoice_Id = AID.Invoice_ID
503 AND AID2.Line_Type_Lookup_Code = 'ITEM' )
504 AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
505 AND AID.Posted_Flag = 'Y'
506 AND AID.pa_addition_flag in
507 ( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
508 , 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
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 AID.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 AID.Accounting_Date >= l_Start_Date )
514 OR ( l_Start_Date is null and l_End_Date is not null
515 and AID.Accounting_Date <= l_End_Date ))
516 UNION ALL
517 SELECT
518 AID.Invoice_Id
519 , AID.Distribution_Line_Number
520 , nvl(AID.invoice_price_variance,ACA.Allocated_Amount) Amount
521 , nvl(AID.base_invoice_price_variance,ACA.Allocated_Base_Amount) Base_Amount
522 , AID.Accounting_Date
523 , AID.Pa_Quantity
524 , AID.Dist_Code_Combination_Id
525 , AID.Accts_Pay_Code_Combination_Id
526 , AID.Line_Type_Lookup_Code
527 , AID2.RCV_Transaction_Id
528 , AID2.Po_Distribution_Id
529 FROM AP_Invoice_Distributions AID
530 , AP_Invoice_Distributions AID2
531 , AP_Chrg_Allocations ACA
532 WHERE EXISTS (
533 SELECT 'x'
534 FROM AP_Invoice_Distributions
535 WHERE Invoice_Id = AID.Invoice_ID
536 AND Line_Type_Lookup_Code = 'ITEM' )
537 AND nvl(AID.Tax_Recoverable_Flag, 'N') = 'N'
538 AND AID.Posted_Flag = 'Y'
539 AND AID.pa_addition_flag in
540 ( 'N' , 'S' , 'A' , 'B' , 'C' , 'D' , 'E' , 'I'
541 , 'J' , 'K' , 'M' , 'P' , 'Q' , 'V' , 'X' , 'W' )
542 AND (( l_Start_Date is null and l_End_Date is null)
543 OR ( l_Start_Date is not null and l_End_Date is not null
544 and AID.Accounting_Date between l_Start_Date and l_End_Date)
545 OR ( l_Start_Date is not null and l_End_Date is null
546 and AID.Accounting_Date >= l_Start_Date )
547 OR ( l_Start_Date is null and l_End_Date is not null
548 and AID.Accounting_Date <= l_End_Date ))
549 AND AID.Invoice_Distribution_Id = ACA.Charge_Dist_Id
550 AND ACA.Item_Dist_Id = AID2.Invoice_Distribution_Id
551 ) DIST
552 , PO_Distributions POD
553 , PA_Projects_ALL PAP
554 , PJM_Org_Parameters POP
555 WHERE DIST.Invoice_Id = INV.Invoice_Id
556 AND NVL(INV.Source, 'XX' ) <> 'Oracle Project Accounting'
557 AND POD.Destination_Type_Code IN ( 'INVENTORY' , 'SHOP FLOOR' )
558 AND POP.Organization_Id = POD.Destination_Organization_Id
559 AND PAP.Project_Id = POP.Common_Project_Id
560 AND POD.Project_Id is null
561 AND DIST.Po_Distribution_Id = POD.Po_Distribution_Id
562 ORDER BY 9,1,2;
563
564 InvRec Inv_WP_Curs%ROWTYPE;
565
566
567 BEGIN
568
569 l_curr_invoice_id := -1;
570 l_first_invoice := TRUE;
571 l_progress := 0;
572 if (X_trx_status_code is NULL)
573 then l_trx_status_code := 'P';
574 else l_trx_status_code := X_trx_status_code;
575 end if;
576
577 fnd_message.set_name('PJM','CONC-APINV Spechrg Transfer');
578 PJM_CONC.put_line(fnd_message.get || ' ...');
579 PJM_CONC.new_line(1);
580
581 PJM_CONC.put_line('[PROJECT_ID] = ' || X_Project_Id);
582 PJM_CONC.put_line('[START_DATE] = ' || X_Start_Date);
583 PJM_CONC.put_line('[END_DATE] = ' || X_End_Date);
584 PJM_CONC.put_line('[SUBMIT_TRX_IMPORT] = ' || X_Submit_Trx_Import);
585
586 l_request_id := fnd_global.conc_request_id;
587 l_user_id := fnd_global.user_id;
588 l_Start_Date := fnd_date.canonical_to_date(X_Start_Date);
589 l_End_Date := fnd_date.canonical_to_date(X_End_Date);
590
591 PJM_CONC.put_line('[REQUEST_ID] = ' || l_request_id);
592 PJM_CONC.new_line(1);
593
594 l_Freight_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV FREIGHT');
595 l_Tax_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV TAX');
596 l_Misc_Exp_Comment := fnd_message.get_string('PJM','CONC-APINV MISC');
597
598 l_Batch_Name := PJM_Transfer_IPV_Pkg.batch_name;
599
600 PJM_CONC.put_line('Batch_Name = ' || l_batch_name);
601 PJM_CONC.new_line(1);
602
603 ----------------------------------------------------------------------------------
604 -- Get Accounting Currency Code
605 ----------------------------------------------------------------------------------
606
607 select ap.base_currency_code
608 into l_base_currency_code
609 from gl_sets_of_books gl
610 , ap_system_parameters ap
611 where gl.set_of_books_id = ap.set_of_books_id;
612
613 -----------------------------------------------------------------------------------
614 -- Loop for transfering Special Charge from Invoice_Distribution to
615 -- PA_Transaction_Interfaces
616 -----------------------------------------------------------------------------------
617
618 fnd_message.set_name('PJM','CONC-APINV Start Loop');
619 PJM_CONC.put_line(fnd_message.get || ' ...');
620
621 Timestamp;
622
623 IF (X_Project_Id is not null) THEN
624
625 OPEN Inv_WP_Curs;
626
627 LOOP
628
629 l_progress := 10;
630
631 FETCH Inv_WP_Curs INTO InvRec;
632 EXIT WHEN Inv_WP_Curs%NOTFOUND;
633
634 --
635 -- If Task not available, use Task AutoAssignment Rules to assign task
636 --
637 l_progress := 15;
638
639 IF ( InvRec.Task_Id IS NOT NULL ) THEN
640 l_Task_Id := InvRec.Task_Id;
641 ELSE
642 l_Task_Id := Assign_Task( InvRec.PO_Distribution_Id
643 , InvRec.Destination_Type_Code
644 , InvRec.Project_Id );
645 END IF;
646
647 PJM_CONC.put_line(' invoice_id ............ '||InvRec.Invoice_Id);
648 PJM_CONC.put_line(' line_num .............. '||
649 InvRec.Distribution_Line_Number);
650 PJM_CONC.put_line(' project_id ............ '||InvRec.Project_ID);
651 PJM_CONC.put_line(' task_id ............... '||l_Task_Id);
652 PJM_CONC.put_line(' expenditure_org_id .... '||
653 InvRec.Expenditure_Organization_ID);
654 PJM_CONC.put_line(' line_type ............. '||
655 InvRec.Distribution_Type);
656 PJM_CONC.new_line(1);
657
658 Timestamp;
659
660 ---------------------------------------------------------------------
661 -- We commit for each invoice.
662 ---------------------------------------------------------------------
663
664 -- if (l_curr_invoice_id <> InvRec.Invoice_Id AND NOT l_first_invoice) then
665 -- COMMIT;
666 -- end if;
667
668 l_curr_invoice_id := InvRec.Invoice_Id;
669 l_first_invoice := FALSE;
670
671 ---------------------------------------------------------------------
672 -- Check Project Status
673 ---------------------------------------------------------------------
674
675 l_progress := 20;
676
677 select decode(InvRec.Distribution_Type,
678 'FREIGHT', nvl(ppp.freight_expenditure_type,
679 pop.freight_expenditure_type),
680 'TAX', nvl(ppp.tax_expenditure_type,
681 pop.tax_expenditure_type),
682 'MISC', nvl(ppp.misc_expenditure_type,
683 pop.misc_expenditure_type),
684 nvl(ppp.misc_expenditure_type,
685 pop.misc_expenditure_type))
686 into l_expenditure_type
687 from pjm_project_parameters ppp
688 , pjm_org_parameters pop
689 where pop.organization_id = InvRec.Expenditure_Organization_Id
690 and ppp.organization_id (+) = pop.organization_id
691 and ppp.project_id (+) = InvRec.Project_Id;
692
693 PJM_CONC.put_line(' expenditure_type ...... '||l_expenditure_type);
694 PJM_CONC.put_line(' amount ................ '||
695 nvl(InvRec.Base_Amount, InvRec.Amount));
696 PJM_CONC.new_line(1);
697
698 l_progress := 30;
699
700 if ( l_expenditure_type is not null ) then
701
702 l_progress := 40;
703
704 UPDATE AP_Invoice_distributions DIST
705 SET DIST.PA_Addition_Flag =
706 DECODE(l_proj_status, 'PA_EX_PROJECT_CLOSED', 'P',
707 'PA_EX_PROJECT_DATE', 'D',
708 'PA_EXP_TASK_STATUS', 'C',
709 'PA_EXP_TASK_EFF', 'I',
710 'PA_EXP_PJ_TC', 'J',
711 'PA_EXP_TASK_TC', 'K',
712 'PA_EXP_INV_PJTK', 'M',
713 NULL, 'S',
714 'Q')
715 , DIST.Last_Update_Date = SYSDATE
716 , DIST.Last_Updated_By = l_user_id
717 , DIST.Request_Id = l_request_id
718 WHERE
719 DIST.Invoice_Id = InvRec.Invoice_Id
720 AND DIST.Distribution_Line_Number = InvRec.Distribution_Line_Number;
721
722 ----------------------------------------------------------------------
723 -- Getting Receipt Num from RCV_TRANSACTION_ID if exists
724 ----------------------------------------------------------------------
725
726 l_progress := 50;
727
728 if ( InvRec.RCV_Transaction_Id is not null ) then
729
730 SELECT rsh.receipt_num
731 INTO l_receipt_num
732 FROM rcv_shipment_headers rsh
733 , rcv_transactions rt
734 WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
735 AND rsh.shipment_header_id = rt.shipment_header_id;
736
737 end if;
738
739 ----------------------------------------------------------------------
740 -- Converting System RATE_TYPE to User RATE_TYPE if exists
741 ----------------------------------------------------------------------
742
743 l_progress := 55;
744
745 if ( InvRec.Exchange_Rate_Type is not null ) then
746
747 SELECT User_Conversion_Type
748 INTO l_User_Conv_Type
749 FROM gl_daily_conversion_types
750 WHERE conversion_type = InvRec.Exchange_Rate_Type;
751
752 end if;
753
754 -----------------------------------------------------------------------
755 -- Insert into PA_TRANSACTION_INTERFACES table
756 -----------------------------------------------------------------------
757
758 fnd_message.set_name('PJM','CONC-APINV Insert');
759 PJM_CONC.put_line('... ' || fnd_message.get);
760
761 BEGIN
762
763 l_progress := 60;
764
765 ---------------------------------------------------------------------
766 -- For Blue Print org, setting Transaction Source according to
767 -- pa_posting_flag and pa_autoaccounting_flag
768 ---------------------------------------------------------------------
769
770 select NVL(pa_posting_flag,'N'),
771 NVL(pa_autoaccounting_flag,'N')
772 into l_blue_print_enabled_flag,
773 l_autoaccounting_flag
774 from pjm_org_parameters
775 where organization_id = InvRec.Expenditure_Organization_Id;
776
777 If l_blue_print_enabled_flag = 'Y' then
778 If l_autoaccounting_flag = 'Y' then
779 /* BP and autoaccounting */
780 l_transaction_source := 'PJM_CSTBP_INV_NO_ACCOUNTS';
781 else
782 /* BP and no autoaccounting -- Send Account to PA */
783 l_transaction_source := 'PJM_CSTBP_INV_ACCOUNTS';
784
785 end if; /* end of check for auto accounting */
786
787 ELSE /* non BP org */
788 l_transaction_source := 'Inventory';
789 END IF; /* check for BP org */
790
791 INSERT INTO pa_transaction_interface
792 (transaction_source,
793 batch_name,
794 expenditure_ending_date,
795 employee_number,
796 organization_name,
797 expenditure_item_date,
798 project_number,
799 task_number,
800 expenditure_type,
801 quantity,
802 expenditure_comment,
803 orig_transaction_reference,
804 unmatched_negative_txn_flag,
805 dr_code_combination_id,
806 cr_code_combination_id,
807 orig_exp_txn_reference1,
808 orig_exp_txn_reference2,
809 orig_exp_txn_reference3,
810 gl_date,
811 system_linkage,
812 transaction_status_code,
813 denom_currency_code,
814 denom_raw_cost,
815 denom_burdened_cost,
816 acct_rate_date,
817 acct_rate_type,
818 acct_exchange_rate,
819 acct_raw_cost,
820 acct_burdened_cost,
821 creation_date,
822 created_by,
823 last_update_date,
824 last_updated_by
825 )
826 SELECT
827 l_transaction_source
828 , l_Batch_Name
829 , pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
830 , NULL
831 , ORG.Name
832 , InvRec.Expenditure_Item_Date
833 , InvRec.Project_Number
834 , TASK.Task_Number
835 , l_Expenditure_Type
836 , InvRec.PA_Quantity
837 , decode(InvRec.Distribution_Type,
838 'FREIGHT', l_Freight_Exp_Comment,
839 'TAX', l_Tax_Exp_Comment,
840 'MISC', l_Misc_Exp_Comment,
841 l_Misc_Exp_Comment)
842 , DIST.Invoice_Distribution_Id
843 , 'Y'
844 , InvRec.Dist_Code_Combination_Id
845 , InvRec.Accts_Pay_Code_Combination_Id
846 , InvRec.PO_Distribution_Id
847 , InvRec.RCV_Transaction_Id
848 , l_receipt_num
849 , DIST.Accounting_Date
850 , 'INV'
851 , l_trx_status_code
852 , InvRec.Invoice_Currency_Code /* denom_currency_code */
853 , InvRec.Amount /* denom_raw_cost */
854 , InvRec.Amount /* denom_burdened_cost */
855 , InvRec.Exchange_Date /* acct_rate_date */
856 , l_User_Conv_Type /* acct_rate_type */
857 , InvRec.Exchange_Rate /* acct_exchange_rate */
858 , nvl(InvRec.Base_Amount, InvRec.Amount) /* acct_raw_cost */
859 , nvl(InvRec.Base_Amount, InvRec.Amount) /* acct_burdened_cost */
860 , SYSDATE
861 , l_user_id
862 , SYSDATE
863 , l_user_id
864 FROM
865 AP_Invoice_Distributions DIST
866 , AP_Invoices INV
867 , PA_Tasks TASK
868 , HR_Organization_Units ORG
869 WHERE
870 DIST.Invoice_Id = InvRec.Invoice_Id
871 AND DIST.Distribution_Line_Number = InvRec.Distribution_Line_Number
872 AND DIST.PA_Addition_Flag = 'S'
873 AND INV.Invoice_ID = DIST.Invoice_Id
874 AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
875 AND TASK.Task_Id = l_Task_Id;
876
877 EXCEPTION
878 WHEN NO_DATA_FOUND THEN
879 NULL;
880 WHEN DUP_VAL_ON_INDEX THEN
881 NULL;
882 END;
883
884 ------------------------------------------------------------------------
885 -- Update pa_addition_flag to 'Y' for successful invoice distributions
886 ------------------------------------------------------------------------
887
888 fnd_message.set_name('PJM','CONC-APINV Flag Comp');
889 PJM_CONC.put_line('... ' || fnd_message.get);
890 PJM_CONC.new_line(1);
891
892
893 l_progress := 70;
894
895 UPDATE AP_Invoice_Distributions
896 SET Pa_Addition_Flag = 'Y'
897 WHERE Pa_Addition_Flag = 'S'
898 AND Invoice_Id = InvRec.Invoice_Id
899 AND Distribution_Line_Number = InvRec.Distribution_Line_Number;
900
901 ELSE /* l_expenditure_type is not null */
902
903 UPDATE AP_Invoice_Distributions
904 SET Pa_Addition_Flag = 'Y'
905 , Request_ID = l_request_id
906 WHERE Invoice_Id = InvRec.Invoice_Id
907 AND Distribution_Line_Number = InvRec.Distribution_Line_Number;
908
909 END IF;
910
911 END LOOP;
912
913 CLOSE Inv_WP_Curs;
914
915 ELSE /* X_Project_Id is null */
916
917 OPEN Inv_NP_Curs;
918
919 LOOP
920
921 l_progress := 10;
922
923 FETCH Inv_NP_Curs INTO InvRec;
924 EXIT WHEN Inv_NP_Curs%NOTFOUND;
925
926 --
927 -- If Task not available, use Task AutoAssignment Rules to assign task
928 --
929 l_progress := 15;
930
931 IF ( InvRec.Task_Id IS NOT NULL ) THEN
932 l_Task_Id := InvRec.Task_Id;
933 ELSE
934 l_Task_Id := Assign_Task( InvRec.PO_Distribution_Id
935 , InvRec.Destination_Type_Code
936 , InvRec.Project_Id );
937 END IF;
938
939 PJM_CONC.put_line(' invoice_id ............ '||InvRec.Invoice_Id);
940 PJM_CONC.put_line(' line_num .............. '||
941 InvRec.Distribution_Line_Number);
942 PJM_CONC.put_line(' project_id ............ '||InvRec.Project_ID);
943 PJM_CONC.put_line(' task_id ............... '||l_Task_Id);
944 PJM_CONC.put_line(' expenditure_org_id .... '||
945 InvRec.Expenditure_Organization_ID);
946 PJM_CONC.put_line(' line_type ............. '||
947 InvRec.Distribution_Type);
948 PJM_CONC.new_line(1);
949
950 Timestamp;
951
952 ---------------------------------------------------------------------
953 -- We commit for each invoice.
954 ---------------------------------------------------------------------
955
956 -- if (l_curr_invoice_id <> InvRec.Invoice_Id AND NOT l_first_invoice) then
957 -- COMMIT;
958 -- end if;
959
960 l_curr_invoice_id := InvRec.Invoice_Id;
961 l_first_invoice := FALSE;
962
963 ---------------------------------------------------------------------
964 -- Check Project Status
965 ---------------------------------------------------------------------
966
967 l_progress := 20;
968
969 select decode(InvRec.Distribution_Type,
970 'FREIGHT', nvl(ppp.freight_expenditure_type,
971 pop.freight_expenditure_type),
972 'TAX', nvl(ppp.tax_expenditure_type,
973 pop.tax_expenditure_type),
974 'MISC', nvl(ppp.misc_expenditure_type,
975 pop.misc_expenditure_type),
976 nvl(ppp.misc_expenditure_type,
977 pop.misc_expenditure_type))
978 into l_expenditure_type
979 from pjm_project_parameters ppp
980 , pjm_org_parameters pop
981 where pop.organization_id = InvRec.Expenditure_Organization_Id
982 and ppp.organization_id (+) = pop.organization_id
983 and ppp.project_id (+) = InvRec.Project_Id;
984
985 PJM_CONC.put_line(' expenditure_type ...... '||l_expenditure_type);
986 PJM_CONC.put_line(' amount ................ '||
987 nvl(InvRec.Base_Amount, InvRec.Amount));
988 PJM_CONC.new_line(1);
989
990 l_progress := 30;
991
992 if ( l_expenditure_type is not null ) then
993
994 l_progress := 40;
995
996 UPDATE AP_Invoice_distributions DIST
997 SET DIST.PA_Addition_Flag =
998 DECODE(l_proj_status, 'PA_EX_PROJECT_CLOSED', 'P',
999 'PA_EX_PROJECT_DATE', 'D',
1000 'PA_EXP_TASK_STATUS', 'C',
1001 'PA_EXP_TASK_EFF', 'I',
1002 'PA_EXP_PJ_TC', 'J',
1003 'PA_EXP_TASK_TC', 'K',
1004 'PA_EXP_INV_PJTK', 'M',
1005 NULL, 'S',
1006 'Q')
1007 , DIST.Last_Update_Date = SYSDATE
1008 , DIST.Last_Updated_By = l_user_id
1009 , DIST.Request_Id = l_request_id
1010 WHERE
1011 DIST.Invoice_Id = InvRec.Invoice_Id
1012 AND DIST.Distribution_Line_Number = InvRec.Distribution_Line_Number;
1013
1014 ----------------------------------------------------------------------
1015 -- Getting Receipt Num from RCV_TRANSACTION_ID if exists
1016 ----------------------------------------------------------------------
1017
1018 l_progress := 50;
1019
1020 if ( InvRec.RCV_Transaction_Id is not null ) then
1021
1022 SELECT rsh.receipt_num
1023 INTO l_receipt_num
1024 FROM rcv_shipment_headers rsh
1025 , rcv_transactions rt
1026 WHERE rt.transaction_id = InvRec.RCV_Transaction_Id
1027 AND rsh.shipment_header_id = rt.shipment_header_id;
1028
1029 end if;
1030
1031 ----------------------------------------------------------------------
1032 -- Converting System RATE_TYPE to User RATE_TYPE if exists
1033 ----------------------------------------------------------------------
1034
1035 l_progress := 55;
1036
1037 if ( InvRec.Exchange_Rate_Type is not null ) then
1038
1039 SELECT User_Conversion_Type
1040 INTO l_User_Conv_Type
1041 FROM gl_daily_conversion_types
1042 WHERE conversion_type = InvRec.Exchange_Rate_Type;
1043
1044 end if;
1045
1046 -----------------------------------------------------------------------
1047 -- Insert into PA_TRANSACTION_INTERFACES table
1048 -----------------------------------------------------------------------
1049
1050 fnd_message.set_name('PJM','CONC-APINV Insert');
1051 PJM_CONC.put_line('... ' || fnd_message.get);
1052
1053 BEGIN
1054
1055 l_progress := 60;
1056
1057 ---------------------------------------------------------------------
1058 -- For Blue Print org, setting Transaction Source according to
1059 -- pa_posting_flag and pa_autoaccounting_flag
1060 ---------------------------------------------------------------------
1061
1062 select NVL(pa_posting_flag,'N'),
1063 NVL(pa_autoaccounting_flag,'N')
1064 into l_blue_print_enabled_flag,
1065 l_autoaccounting_flag
1066 from pjm_org_parameters
1067 where organization_id = InvRec.Expenditure_Organization_Id;
1068
1069 If l_blue_print_enabled_flag = 'Y' then
1070 If l_autoaccounting_flag = 'Y' then
1071 /* BP and autoaccounting */
1072 l_transaction_source := 'PJM_CSTBP_INV_NO_ACCOUNTS';
1073 else
1074 /* BP and no autoaccounting -- Send Account to PA */
1075 l_transaction_source := 'PJM_CSTBP_INV_ACCOUNTS';
1076
1077 end if; /* end of check for auto accounting */
1078
1079 ELSE /* non BP org */
1080 l_transaction_source := 'Inventory';
1081 END IF; /* check for BP org */
1082
1083 INSERT INTO pa_transaction_interface
1084 (transaction_source,
1085 batch_name,
1086 expenditure_ending_date,
1087 employee_number,
1088 organization_name,
1089 expenditure_item_date,
1090 project_number,
1091 task_number,
1092 expenditure_type,
1093 quantity,
1094 expenditure_comment,
1095 orig_transaction_reference,
1096 unmatched_negative_txn_flag,
1097 dr_code_combination_id,
1098 cr_code_combination_id,
1099 orig_exp_txn_reference1,
1100 orig_exp_txn_reference2,
1101 orig_exp_txn_reference3,
1102 gl_date,
1103 system_linkage,
1104 transaction_status_code,
1105 denom_currency_code,
1106 denom_raw_cost,
1107 denom_burdened_cost,
1108 acct_rate_date,
1109 acct_rate_type,
1110 acct_exchange_rate,
1111 acct_raw_cost,
1112 acct_burdened_cost,
1113 creation_date,
1114 created_by,
1115 last_update_date,
1116 last_updated_by
1117 )
1118 SELECT
1119 l_transaction_source
1120 , l_Batch_Name
1121 , pa_utils.GetWeekEnding(InvRec.Expenditure_Item_Date)
1122 , NULL
1123 , ORG.Name
1124 , InvRec.Expenditure_Item_Date
1125 , InvRec.Project_Number
1126 , TASK.Task_Number
1127 , l_Expenditure_Type
1128 , InvRec.PA_Quantity
1129 , decode(InvRec.Distribution_Type,
1130 'FREIGHT', l_Freight_Exp_Comment,
1131 'TAX', l_Tax_Exp_Comment,
1132 'MISC', l_Misc_Exp_Comment,
1133 l_Misc_Exp_Comment)
1134 , DIST.Invoice_Distribution_Id
1135 , 'Y'
1136 , InvRec.Dist_Code_Combination_Id
1137 , InvRec.Accts_Pay_Code_Combination_Id
1138 , InvRec.PO_Distribution_Id
1139 , InvRec.RCV_Transaction_Id
1140 , l_receipt_num
1141 , DIST.Accounting_Date
1142 , 'INV'
1143 , l_trx_status_code
1144 , InvRec.Invoice_Currency_Code /* denom_currency_code */
1145 , InvRec.Amount /* denom_raw_cost */
1146 , InvRec.Amount /* denom_burdened_cost */
1147 , InvRec.Exchange_Date /* acct_rate_date */
1148 , l_User_Conv_Type /* acct_rate_type */
1149 , InvRec.Exchange_Rate /* acct_exchange_rate */
1150 , nvl(InvRec.Base_Amount, InvRec.Amount) /* acct_raw_cost */
1151 , nvl(InvRec.Base_Amount, InvRec.Amount) /* acct_burdened_cost */
1152 , SYSDATE
1153 , l_user_id
1154 , SYSDATE
1155 , l_user_id
1156 FROM
1157 AP_Invoice_Distributions DIST
1158 , AP_Invoices INV
1159 , PA_Tasks TASK
1160 , HR_Organization_Units ORG
1161 WHERE
1162 DIST.Invoice_Id = InvRec.Invoice_Id
1163 AND DIST.Distribution_Line_Number = InvRec.Distribution_Line_Number
1164 AND DIST.PA_Addition_Flag = 'S'
1165 AND INV.Invoice_ID = DIST.Invoice_Id
1166 AND ORG.Organization_Id = InvRec.Expenditure_Organization_Id
1167 AND TASK.Task_Id = l_Task_Id;
1168
1169 EXCEPTION
1170 WHEN NO_DATA_FOUND THEN
1171 NULL;
1172 WHEN DUP_VAL_ON_INDEX THEN
1173 NULL;
1174 END;
1175
1176 ------------------------------------------------------------------------
1177 -- Update pa_addition_flag to 'Y' for successful invoice distributions
1178 ------------------------------------------------------------------------
1179
1180 fnd_message.set_name('PJM','CONC-APINV Flag Comp');
1181 PJM_CONC.put_line('... ' || fnd_message.get);
1182 PJM_CONC.new_line(1);
1183
1184
1185 l_progress := 70;
1186
1187 UPDATE AP_Invoice_Distributions
1188 SET Pa_Addition_Flag = 'Y'
1189 WHERE Pa_Addition_Flag = 'S'
1190 AND Invoice_Id = InvRec.Invoice_Id
1191 AND Distribution_Line_Number = InvRec.Distribution_Line_Number;
1192
1193 ELSE /* l_expenditure_type is not null */
1194
1195 UPDATE AP_Invoice_Distributions
1196 SET Pa_Addition_Flag = 'Y'
1197 , Request_ID = l_request_id
1198 WHERE Invoice_Id = InvRec.Invoice_Id
1199 AND Distribution_Line_Number = InvRec.Distribution_Line_Number;
1200
1201 END IF;
1202
1203 END LOOP;
1204
1205 CLOSE Inv_NP_Curs;
1206
1207 END IF;
1208
1209 COMMIT;
1210
1211 fnd_message.set_name('PJM','CONC-APINV Finish Loop');
1212 PJM_CONC.put_line(fnd_message.get || ' ...');
1213 PJM_CONC.new_line(1);
1214
1215 Timestamp;
1216
1217 l_progress := 80;
1218
1219 if (X_Submit_Trx_Import = 'Y') then
1220 l_imp_req_id := fnd_request.submit_request('PA','PAXTRTRX',
1221 'PRC: Transaction Import',
1222 NULL, FALSE,
1223 'Inventory',
1224 l_Batch_Name);
1225 end if;
1226
1227 retcode := PJM_CONC.G_conc_success;
1228 return;
1229
1230
1231 EXCEPTION
1232 when OTHERS then
1233 errbuf := 'SPC-'||l_progress||': '||sqlerrm;
1234 retcode := PJM_CONC.G_conc_failure;
1235 return;
1236
1237 END Transfer_Spec_Charges_to_PA;
1238
1239
1240 END PJM_TRANSFER_SPEC_CHARGES_PKG;