[Home] [Help]
PACKAGE BODY: APPS.PA_WORK_TYPE_UPGRADE
Source
1 PACKAGE BODY PA_WORK_TYPE_UPGRADE AS
2 /* $Header: PAWKUPGB.pls 120.13.12020000.3 2013/03/06 09:50:15 admarath ship $ */
3
4 /* Procedure: Upgrade_WT_Main
5
6 Updates Work Type Id on
7 pa_projects_all,
8 pa_tasks,
9 pa_expenditure_items_all,
10 pa_cost_distribution_lines_all
11
12 Simultaneously, it also updates Tp Amt Type Code on
13 pa_expenditure_items_all,
14 pa_cc_dist_lines_all
15 pa_draft_invoice_details_all
16
17 In FP.M, it has been modified to support update
18 inventory_item_id, wip_resource_id, unit_of_measure
19 on pa_expenditure_items_all
20
21 Parameters: IN
22 P_Num_Of_Processes : User given number, that many processes will be spawned
23 P_Worker_Id : Holds the worker id
24 P_Org_Id : Holds the operating unit
25 P_Txn_Date : Holds the transaction start date
26 P_Txn_Type : Can be 'PJM' or 'WORK TYPE'.
27 if PJM, will update Project Manufacturing Attributes on EI table
28 P_Txn_Src : If given will update EI for the specified Transaction Source only.
29 --Added for R12 AP Lines uptake
30 P_Min_Project_Id : Holds the minimum of the project id range, internally used
31 P_Max_Project_Id : Holds the maximum of the project id range, internally used
32
33 OUT
34 X_Return_Status : Currently not used
35 X_Error_Message_Code : Currently not used
36
37 */
38
39
40 Procedure Upgrade_WT_Main(
41 X_RETURN_STATUS OUT NOCOPY VARCHAR2
42 ,X_ERROR_MESSAGE_CODE OUT NOCOPY VARCHAR2
43 ,P_TXN_TYPE IN VARCHAR2
44 ,P_TXN_SRC IN VARCHAR2
45 ,P_NUM_OF_PROCESSES IN NUMBER
46 ,P_WORKER_ID IN NUMBER
47 ,P_ORG_ID IN NUMBER DEFAULT NULL
48 ,P_TXN_DATE IN VARCHAR2
49 ,P_Min_Project_Id IN NUMBER DEFAULT NULL
50 ,P_Max_Project_Id IN NUMBER DEFAULT NULL
51 )
52
53 Is
54
55 l_child_req_id number;
56 l_Txn_Date Date;
57 l_get_uom varchar2(2000); /* Bug 3817950 */
58 Begin
59
60 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Time = '|| to_char(sysdate,'HH:MI:SS'));
61 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Validated parameters are as follows:');
62 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_Num_Of_Processes = ' || p_num_of_processes);
63 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_worker_id = ' || p_worker_id);
64 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_org_id = ' || p_org_id);
65 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_Txn_Date = ' || P_Txn_Date);
66 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_Txn_Type = ' || P_Txn_Type);
67 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_TXN_SRC = ' || P_TXN_SRC);
68 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_Min_Project_Id = ' || P_Min_Project_Id);
69 FND_FILE.PUT_LINE(FND_FILE.LOG, 'P_Max_Project_Id = ' || P_Max_Project_Id);
70
71 l_Txn_Date := fnd_date.canonical_to_date(P_Txn_Date);
72
73 -- Update only projects and tasks for the given project type in the given OU
74 -- Submit requests to update EI, CDL, CCDL and DID
75 If (p_worker_id = 0) Then
76
77 Declare
78
79 Cursor C_Projects Is
80 Select P.Project_Id,
81 Pt.Work_Type_Id
82 From Pa_Projects_All P,
83 Pa_Project_Types_All Pt
84 Where P.Project_Type = Pt.Project_Type
85 And nvl(P.Org_Id, -99) = nvl(Pt.Org_Id, -99)
86 And Pt.Work_Type_Id is not NULL;
87
88 l_PrjIdTab PA_PLSQL_DATATYPES.IdTabTyp;
89 l_WorkTypeTab PA_PLSQL_DATATYPES.IdTabTyp;
90
91 l_ReqStsTab PA_WORK_TYPE_UPGRADE.ResStsTabType;
92
93 Rows number := 1000;
94
95 l_phase varchar2(255);
96 l_status varchar2(255);
97 l_dev_phase varchar2(255);
98 l_dev_status varchar2(255);
99 l_message varchar2(255);
100
101 l_ins_rowcount number;
102
103 NOTCOMPLETE BOOLEAN := TRUE;
104
105 --Code Changes for Bug No.2984871 start
106 l_rowcount number :=0;
107 --Code Changes for Bug No.2984871 end
108
109 l_inssts VARCHAR2(30);
110 l_industry VARCHAR2(30);
111 l_pa_schema VARCHAR2(30);
112
113 MIN_Project_Id NUMBER;
114 MAX_Project_Id NUMBER;
115
116 l_Min INTEGER := 0;
117 l_Max INTEGER := 0;
118 l_remainder integer:=0;
119
120 Begin
121
122 IF P_Txn_Type = 'WORK TYPE' THEN -- PJM Changes
123
124 OPEN C_Projects;
125
126 LOOP
127
128 l_WorkTypeTab.delete;
129 l_PrjIdTab.delete;
130
131 FETCH C_Projects BULK COLLECT INTO
132 l_PrjIdTab,
133 l_WorkTypeTab
134 LIMIT Rows;
135
136 If l_PrjIdtab.count = 0 Then
137 Exit;
138 End If;
139
140 FORALL i in l_PrjIdTab.first .. l_PrjIdTab.last
141 update pa_projects_all
142 set work_type_id = l_WorkTypeTab(i)
143 where project_id = l_PrjIdTab(i)
144 and work_type_id is null;
145
146 --Code Changes for Bug No.2984871 start
147 l_rowcount:=sql%rowcount;
148 --Code Changes for Bug No.2984871 end
149
150 FND_FILE.PUT_LINE(FND_FILE.LOG,'....................');
151
152 -- Commented for Bug 2984871
153 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'No of Project Records updated = '||SQL%ROWCOUNT);
154
155 --Code Changes for Bug No.2984871 start
156 FND_FILE.PUT_LINE(FND_FILE.LOG,'No of Project Records updated = '||l_rowcount);
157 --dbms_output.PUT_LINE('No of Project Records updated = '||l_rowcount);
158 --Code Changes for Bug No.2984871 end
159
160 FORALL i in l_PrjIdTab.first .. l_PrjIdTab.last
161 update pa_tasks
162 set work_type_id = l_WorkTypeTab(i)
163 where project_id = l_PrjIdTab(i)
164 and work_type_id is null;
165
166 l_rowcount:=sql%rowcount;
167
168 FND_FILE.PUT_LINE(FND_FILE.LOG,'No of Task Records updated = '||l_rowcount);
169 --dbms_output.PUT_LINE('No of Task Records updated = '||l_rowcount);
170
171 commit;
172
173 EXIT WHEN C_Projects%NOTFOUND;
174
175 END LOOP ;
176
177 CLOSE C_Projects;
178
179 END IF; -- P_TXN_TYPE = 'WORK TYPE' -- PJM Changes
180
181 IF P_Txn_Type = 'SUPPLIER TYPE' THEN -- R12 changes
182
183 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start EI Upgrade Process'|| to_char(sysdate, 'HH:MI:SS'));
184
185 -- Get the project range if not supplied
186 /* bug 9132581 changes start: break the statement into two, as P_Max_Project_Id may be null even when P_Min_Project_Id is not null */
187 IF P_Min_Project_Id IS NULL THEN
188 SELECT Min(P.Project_Id) --, Max(P.Project_Id)
189 INTO Min_Project_Id --,Max_Project_Id
190 FROM Pa_Projects_All P
191 WHERE p.org_id = nvl(p_org_id,p.org_id);
192 ELSE
193 Min_Project_Id := P_Min_Project_Id;
194 -- Max_Project_Id := P_Max_Project_Id;
195 END IF;
196
197 IF P_Max_Project_Id IS NULL THEN
198 SELECT Max(P.Project_Id)
199 INTO Max_Project_Id
200 FROM Pa_Projects_All P
201 WHERE p.org_id = nvl(p_org_id,p.org_id);
202 ELSE
203 Max_Project_Id := P_Max_Project_Id;
204 END IF;
205 /* bug 9132581 changes end */
206
207 l_remainder := MOD((Max_Project_Id-Min_Project_Id),p_num_of_processes);
208
209 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Min_Project_Id = ' || Min_Project_Id);
210 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Max_Project_Id = ' || Max_Project_Id);
211
212 l_Max := MIN_Project_Id ;
213
214 IF Min_Project_Id = Max_Project_Id THEN
215
216 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '.....................');
217 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Project_Id = ' || Min_Project_Id);
218
219 -- Call FND API to submit the same request for the EI update
220 l_child_req_id := FND_REQUEST.SUBMIT_REQUEST('PA',
221 'PAWKTPUP',
222 '',
223 '',
224 FALSE,
225 p_txn_type,
226 p_txn_src,
227 1,
228 1,
229 p_org_id,
230 fnd_date.date_to_canonical(l_TXN_DATE),
231 Min_Project_Id,
232 Max_Project_Id);
233 ELSE
234
235 For i in 1..P_Num_Of_Processes Loop
236
237 l_Min := l_Max;
238 l_Max := (l_Min + FLOOR((Max_Project_Id-Min_Project_Id)/p_num_of_processes));
239
240 IF i = P_Num_Of_Processes THEN
241 l_max := l_max + l_remainder;
242 END IF;
243
244 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '.....................');
245 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Min Project_Id = ' || l_Min);
246 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Max Project_Id = ' || l_Max);
247
248 -- Call FND API to submit the same request for the EI update
249 l_child_req_id := FND_REQUEST.SUBMIT_REQUEST('PA',
250 'PAWKTPUP',
251 '',
252 '',
253 FALSE,
254 p_txn_type,
255 p_txn_src,
256 p_num_of_processes,
257 i,
258 p_org_id,
259 fnd_date.date_to_canonical(l_TXN_DATE),
260 l_min,
261 l_max);
262 IF (l_child_req_id = 0) THEN
263 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
264 '...An attempt to submit the upgrade request has failed.');
265 FND_FILE.PUT_LINE(FND_FILE.LOG,
266 '...An attempt to submit the upgrade request has failed.');
267 ELSE
268 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
269 'The process to upgrade the records has been submitted.
270 Please check Request ID: '|| to_char(l_child_req_id));
271 FND_FILE.PUT_LINE(FND_FILE.LOG,
272 'The process to upgrade the records has been submitted.
273 Please check Request ID: '|| to_char(l_child_req_id));
274 END IF;
275
276 End Loop;
277 END IF;
278
279 END IF; -- P_TXN_TYPE = 'SUPPLIER TYPE' -- R12 changes
280
281
282 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Insert into Temp Table '|| to_char(sysdate, 'HH:MI:SS'));
283
284 IF P_TXN_TYPE <> 'SUPPLIER TYPE' THEN -- R12 changes
285 execute immediate 'alter session enable parallel dml';
286 END IF;
287
288 IF P_Txn_Type = 'WORK TYPE' THEN
289
290 INSERT /*+ APPEND parallel(t) */
291 INTO pa_txn_upgrade_temp t
292 (row_id,
293 pk1_id,
294 worker_id,
295 pk2_id)
296 select /*+ parallel(ei) */ ei.rowid, ei.expenditure_item_id id, null , ei.task_id
297 from pa_expenditure_items_all ei
298 where trunc(ei.expenditure_item_date) >= trunc(l_Txn_Date)
299 and nvl(ei.org_id, -99) = nvl(p_org_id,nvl(ei.org_id,-99)) /* Changed for Bug #6129449 by anuragar */
300 and work_type_id is null;
301
302 l_ins_rowcount := SQL%ROWCOUNT;
303
304 FND_FILE.PUT_LINE(FND_FILE.LOG, l_ins_rowcount||' Records inserted for WORK TYPE '|| to_char(sysdate, 'HH:MI:SS'));
305
306 ELSIF P_Txn_Type = 'PJM' THEN
307
308 /* 3968368: Added two unions to handle the PJM invoice charges upgrade. For such txns,
309 PJM has been populating the relevant columns in the pa_transaction_interface table
310 in the following manner:
311
312 Column Populated value
313 --------------------- ------------------------
314 cdl_system_reference1 PO_Distribution_Id
315 cdl_system_reference2 RCV_Transaction_Id
316 cdl_system_reference3 l_receipt_num
317 orig_exp_txn_reference1 Invoice_Id
318 orig_exp_txn_reference2 NULL
319 orig_exp_txn_reference3 NULL
320
321 From 11.5.7 to now
322
323 Column Populated value
324 --------------------- --------------------------
325 orig_exp_txn_reference1 PO_Distribution_Id
326 orig_exp_txn_reference2 InvRec.RCV_Transaction_Id
327 orig_exp_txn_reference3 l_receipt_num
328
329 */
330
331 INSERT /*+ APPEND parallel(t) */
332 INTO pa_txn_upgrade_temp t
333 (row_id
334 ,pk1_id
335 ,worker_id
336 ,pk2_id
337 ,txn_src
338 ,reference1
339 )
340 SELECT /*+ parallel(ei) */ EI.ROWID, EI.expenditure_item_id, null
341 , EI.orig_transaction_reference, EI.transaction_source, 'CCO'
342 FROM pa_expenditure_items_all EI, pa_expenditures_all EXP
343 WHERE EI.transaction_source = NVL(P_Txn_Src, EI.transaction_source)
344 AND EI.transaction_source IN ('Inventory Misc', 'Inventory', 'PJM_CSTBP_INV_ACCOUNTS'
345 ,'PJM_CSTBP_INV_NO_ACCOUNTS', 'Work In Process'
346 ,'PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS'
347 ,'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS'
348 ,'PJM_NON_CSTBP_ST_ACCOUNTS')
349 AND EI.unit_of_measure IS NULL
350 AND TRUNC(ei.expenditure_item_date) >= TRUNC(l_TXN_DATE)
351 AND EI.expenditure_id = EXP.expenditure_id
352 AND EXP.orig_exp_txn_reference1 is null
353 UNION ALL
354 SELECT /*+ parallel(ei) */ EI.ROWID, EI.expenditure_item_id, null
355 , EXP.orig_exp_txn_reference1, EI.transaction_source, 'PODIST'
356 FROM pa_expenditure_items_all EI, pa_expenditures_all EXP
357 WHERE EI.transaction_source IN ('Inventory Misc', 'Inventory', 'PJM_CSTBP_INV_ACCOUNTS'
358 ,'PJM_CSTBP_INV_NO_ACCOUNTS', 'Work In Process'
359 ,'PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS'
360 ,'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS'
361 ,'PJM_NON_CSTBP_ST_ACCOUNTS')
362 AND EI.unit_of_measure IS NULL
363 AND TRUNC(ei.expenditure_item_date) >= TRUNC(l_TXN_DATE)
364 AND EI.expenditure_id = EXP.expenditure_id
365 AND EXP.orig_exp_txn_reference1 is not null
366 AND EXP.orig_exp_txn_reference2 is not null
367 UNION ALL
368 SELECT /*+ parallel(ei) */ EI.ROWID, EI.expenditure_item_id, null
369 , CDL.system_reference1, EI.transaction_source, 'PODIST'
370 FROM pa_expenditure_items_all EI, pa_expenditures_all EXP, pa_cost_distribution_lines_all CDL
371 WHERE EI.transaction_source IN ('Inventory Misc', 'Inventory', 'PJM_CSTBP_INV_ACCOUNTS'
372 ,'PJM_CSTBP_INV_NO_ACCOUNTS', 'Work In Process'
373 ,'PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS'
374 ,'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS'
375 ,'PJM_NON_CSTBP_ST_ACCOUNTS')
376 AND EI.unit_of_measure IS NULL
377 AND TRUNC(ei.expenditure_item_date) >= TRUNC(l_TXN_DATE)
378 AND EI.expenditure_id = EXP.expenditure_id
379 AND EXP.orig_exp_txn_reference1 is not null
380 AND EXP.orig_exp_txn_reference2 is null
381 AND EI.expenditure_item_id = CDL.expenditure_item_id
382 AND CDL.line_num = 1;
383
384 l_ins_rowcount := SQL%ROWCOUNT;
385
386 FND_FILE.PUT_LINE(FND_FILE.LOG, l_ins_rowcount || ' Records inserted for PJM '|| to_char(sysdate, 'HH:MI:SS'));
387
388 END IF;
389
390 commit;
391
392 IF P_Txn_Type <> 'SUPPLIER TYPE' THEN
393
394 UPDATE /*+ parallel(t) */ pa_txn_upgrade_temp t
395 SET worker_id = (ceil(rownum / ceil(l_ins_rowcount / p_num_of_processes )));
396
397 commit;
398
399 l_ReqStsTab.delete; -- PJM Changes
400
401 For i in 1..P_Num_Of_Processes Loop
402
403 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '.....................');
404
405 -- Call FND API to submit the same request for the EI update
406 l_child_req_id := FND_REQUEST.SUBMIT_REQUEST('PA',
407 'PAWKTPUP',
408 '',
409 '',
410 FALSE,
411 p_txn_type,
412 p_txn_src,
413 p_num_of_processes,
414 i,
415 p_org_id,
416 fnd_date.date_to_canonical(l_TXN_DATE) );
417
418 IF (l_child_req_id = 0) THEN
419 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
420 '...An attempt to submit the upgrade request has failed.');
421 FND_FILE.PUT_LINE(FND_FILE.LOG,
422 '...An attempt to submit the upgrade request has failed.');
423 ELSE
424 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
425 'The process to upgrade the records has been submitted.
426 Please check Request ID: '|| to_char(l_child_req_id));
427 FND_FILE.PUT_LINE(FND_FILE.LOG,
428 'The process to upgrade the records has been submitted.
429 Please check Request ID: '|| to_char(l_child_req_id));
430 END IF;
431
432 l_ReqStsTab(i).Request_Id := l_child_req_id;
433
434 if (FND_CONCURRENT.GET_REQUEST_STATUS
435 (
436 l_child_req_id,
437 null, -- pa_schema_name
438 null, -- request_name
439 l_phase,
440 l_status,
441 l_dev_phase,
442 l_dev_status,
443 l_message
444 )) then
445 null;
446 end if;
447
448 l_ReqStsTab(i).Status := l_dev_phase;
449
450 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Req = ' || l_ReqStsTab(i).Request_Id || ' Sts = ' || l_ReqStsTab(i).Status);
451
452 End Loop;
453
454 commit;
455
456 While NOTCOMPLETE Loop
457
458 dbms_lock.sleep(60); /* changed sleep seconds from 300 to 60 for 4130368 */
459
460 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside Loop forever '|| to_char(sysdate,'HH:MI:SS') );
461
462 NOTCOMPLETE := FALSE;
463
464 FOR j in l_ReqStsTab.first..l_ReqStsTab.LAST LOOP
465
466 if (FND_CONCURRENT.GET_REQUEST_STATUS
467 (
468 l_ReqStsTab(j).request_id,
469 null, -- pa_schema_name
470 null, -- request_name
471 l_phase,
472 l_status,
473 l_dev_phase,
474 l_dev_status,
475 l_message
476 )) then
477 null;
478 end if;
479
480 l_ReqStsTab(j).Status := l_dev_phase;
481
482 If l_dev_phase <> 'COMPLETE' Then
483 FND_FILE.PUT_LINE(FND_FILE.LOG,'Loop Again'|| to_char(sysdate,'HH:MI:SS'));
484 NOTCOMPLETE := TRUE;
485 exit;
486 End If;
487
488 END LOOP;
489
490 End Loop;
491
492 IF (NOT FND_INSTALLATION.GET_APP_INFO('PA', l_inssts, l_industry, l_pa_schema)) THEN
493 raise_application_error(-20001,SQLERRM);
494 END IF;
495
496 FND_FILE.PUT_LINE(FND_FILE.LOG,'Truncating Table '|| l_pa_schema || '.pa_txn_upgrade_temp '||
497 to_char(sysdate,'HH:MI:SS') );
498
499 execute immediate ('Truncate table ' || l_pa_schema || '.pa_txn_upgrade_temp');
500
501 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling pa_uom.get_uom for updating UOM Meaning '|| to_char(sysdate,'HH:MI:SS') );
502
503 l_get_uom := 'F';
504
505 l_get_uom := pa_uom.get_uom(fnd_global.USER_ID);
506
507 IF (l_get_uom = 'S') THEN
508
509 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Successfully Updated UOM Meanings in PA '|| to_char(sysdate,'HH:MI:SS'));
510
511 ELSE
512
513 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error While Updating UOM Meanings in PA '||l_get_uom);
514 raise_application_error(-20001,SQLERRM);
515
516 END IF;
517
518 commit;
519 END IF; -- P_Txn_Type <> 'SUPPLIER TYPE'
520
521 End;
522
523 End If; -- p_worker_id
524
525 If (p_worker_id <> 0) Then
526
527 FND_FILE.PUT_LINE(FND_FILE.LOG, p_worker_id|| ' is the worker_id '|| to_char(sysdate,'HH:MI:SS'));
528
529 Declare
530
531 Cursor C_Ei Is
532 Select Temp.Pk1_Id,
533 Tsk.Work_Type_Id,
534 Wt.Tp_Amt_Type_Code
535 From pa_txn_upgrade_temp Temp,
536 Pa_Tasks Tsk,
537 Pa_Work_Types_B Wt
538 Where Worker_Id = p_worker_id
539 And Temp.Pk2_id = Tsk.Task_Id
540 And Tsk.Work_Type_Id = Wt.Work_Type_Id;
541
542 CURSOR Cur_PJM_Attr IS
543 SELECT MMT.Inventory_Item_Id Inventory_Item_ID
544 ,to_number(NULL) Wip_Resource_Id
545 ,MSI.Primary_UOM_Code UOM
546 ,Temp.Pk1_Id
547 FROM MTL_MATERIAL_TRANSACTIONS MMT
548 ,MTL_SYSTEM_ITEMS MSI
549 ,Pa_Txn_Upgrade_Temp Temp
550 WHERE Temp.Worker_Id = p_worker_id
551 And Temp.Pk2_id = MMT.Transaction_Id
552 AND MMT.Inventory_Item_Id = MSI.Inventory_Item_Id
553 AND MMT.Organization_Id =MSI.Organization_Id
554 AND Temp.Txn_Src IN ('Inventory Misc', 'Inventory'
555 ,'PJM_CSTBP_INV_ACCOUNTS', 'PJM_CSTBP_INV_NO_ACCOUNTS')
556 AND Temp.Reference1 = 'CCO'
557 UNION ALL
558 SELECT to_number(NULL) Inventory_Item_ID
559 ,WT.Resource_Id Wip_Resource_ID
560 ,WT.Primary_UOM UOM
561 ,Temp.Pk1_ID
562 FROM WIP_TRANSACTIONS WT
563 ,Pa_Txn_Upgrade_Temp Temp
564 WHERE Temp.Worker_Id = p_worker_id
565 And Temp.Pk2_ID = WT.Transaction_ID
566 AND Temp.Txn_Src IN ('Work In Process'
567 ,'PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS'
568 ,'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS'
569 ,'PJM_NON_CSTBP_ST_ACCOUNTS')
570 AND Temp.Reference1 = 'CCO'
571 UNION ALL
572 SELECT decode(PoDist.destination_type_code, 'INVENTORY' , PoLine.Item_Id, null) Inventory_Item_ID ,
573 decode(PoDist.destination_type_code, 'SHOP FLOOR', PoDist.Bom_Resource_Id, null) Wip_Resource_ID ,
574 'DOLLARS' UOM ,
575 Temp.Pk1_Id
576 FROM Pa_Txn_Upgrade_Temp Temp,
577 Po_Distributions_All PoDist,
578 Po_Lines_All PoLine
579 WHERE Temp.Worker_Id = p_worker_id
580 AND Temp.Pk2_Id = PoDist.po_distribution_id
581 AND PoDist.Po_Line_Id = PoLine.Po_Line_Id
582 AND Temp.Txn_Src in ('Inventory Misc', 'Inventory', 'PJM_CSTBP_INV_ACCOUNTS', 'PJM_CSTBP_INV_NO_ACCOUNTS',
583 'Work In Process','PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS',
584 'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS','PJM_NON_CSTBP_ST_ACCOUNTS')
585 AND Temp.Reference1 = 'PODIST';
586
587
588 l_InvItmIdTab PA_PLSQL_DATATYPES.IdTabTyp;
589 l_WIPIdTab PA_PLSQL_DATATYPES.IdTabTyp;
590 l_UOMTab PA_PLSQL_DATATYPES.Char30TabTyp;
591
592 l_EIIdTab PA_PLSQL_DATATYPES.IdTabTyp;
593 l_WtIdTab PA_PLSQL_DATATYPES.IdTabTyp;
594 l_WtTpAmtTab PA_PLSQL_DATATYPES.Char30TabTyp;
595
596 Rows number := 1000;
597 l_rowcount number :=0;
598
599 TYPE rowid_typ IS TABLE OF varchar2(30) ;
600 TYPE exp_item_id_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.expenditure_item_id%TYPE;
601 TYPE vendor_id_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.vendor_id%TYPE;
602 TYPE doc_header_id_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.document_header_id%TYPE;
603 TYPE doc_dist_id_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.document_distribution_id%TYPE;
604 TYPE doc_line_num_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.document_line_number%TYPE;
605 TYPE doc_payment_id_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.document_payment_id%TYPE;
606 TYPE document_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.document_type%TYPE;
607 TYPE document_dist_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.document_distribution_type%TYPE;
608
609 l_ei_rowid_tbl rowid_typ ;
610 l_exp_item_id_tbl exp_item_id_typ;
611 l_vendor_id_tbl vendor_id_typ;
612 l_doc_header_id_tbl doc_header_id_typ;
613 l_doc_dist_id_tbl doc_dist_id_typ;
614 l_doc_line_num_tbl doc_line_num_typ;
615 l_doc_payment_id_tbl doc_payment_id_typ;
616 l_document_tbl document_typ;
617 l_document_dist_tbl document_dist_typ;
618 l_plsql_max_array_size number := 200;
619
620 -- Get all the VI related transactions to be upgraded.
621 CURSOR cur_ap_po_ei(p_project_id in number) IS
622 SELECT /*+ leading(ei) index(ei,pa_expenditure_items_n8) use_nl(cdl,inv,dist) */
623 ei.rowid,
624 cdl.expenditure_item_id,
625 to_number(cdl.system_reference1) vendor_id,
626 inv.invoice_id doc_header_id,
627 dist.invoice_distribution_id doc_dist_id,
628 dist.invoice_line_number doc_line_num,
629 NVL2(LTRIM(cdl.system_reference4, '0123456789'), NULL, cdl.system_reference4) doc_payment_id,
630 inv.invoice_type_lookup_code doc_type,
631 dist.line_type_lookup_code dist_type
632 FROM pa_cost_distribution_lines_all cdl,
633 pa_expenditure_items_all ei,
634 ap_invoice_distributions_all dist,
635 ap_invoices_all inv
636 WHERE cdl.expenditure_item_id = ei.expenditure_item_id
637 AND inv.invoice_id = to_number(cdl.system_reference2)
638 AND dist.invoice_id = to_number(cdl.system_reference2)
639 AND dist.invoice_id = inv.invoice_id
640 AND dist.project_id > 0
641 AND dist.old_dist_line_number = to_number(cdl.system_reference3)
642 AND ((dist.line_type_lookup_code = decode(ei.transaction_source,'AP VARIANCE',cdl.system_reference4,
643 'AP INVOICE','ITEM',
644 'AP NRTAX','NONREC_TAX',dist.line_type_lookup_code)
645 AND ei.transaction_source <> 'AP DISCOUNTS'
646 OR dist.line_type_lookup_code = DECODE(ei.transaction_source,'AP VARIANCE','T'||cdl.system_reference4))
647 -- Commented for the bug 12566440 (start)
648 /* OR dist.line_type_lookup_code = DECODE(ei.transaction_source,'AP INVOICE','PREPAY')
649 OR dist.line_type_lookup_code = DECODE(ei.transaction_source,'AP INVOICE','FREIGHT') /* 8547295 : Added clause */
650 /*OR dist.line_type_lookup_code = DECODE(ei.transaction_source,'AP INVOICE','MISCELLANEOUS') /* 8547295 : Added clause */
651 /*OR ei.transaction_source = 'AP DISCOUNTS' and dist.line_type_lookup_code NOT IN ('TERV','TRV','ERV','IPV','TIPV'))*/
652 -- Commented for the bug 12566440 (end)
653 -- Added below condition for the bug 12566440 (start)
654 OR (ei.transaction_source = 'AP INVOICE' AND dist.line_type_lookup_code in ('PREPAY','FREIGHT','MISCELLANEOUS','NONREC_TAX','ACCRUAL')) /*Added for bug#14097178 */
655 OR (ei.transaction_source = 'AP EXPENSE' AND dist.line_type_lookup_code in ('ITEM','FREIGHT','MISCELLANEOUS','NONREC_TAX'))
656 OR (ei.transaction_source = 'AP DISCOUNTS' AND dist.line_type_lookup_code NOT IN ('TERV','TRV','ERV','IPV','TIPV')))
657 -- Added above condition for the bug 12566440 (end)
658 AND cdl.system_reference2 IS NOT NULL
659 AND cdl.system_reference3 IS NOT NULL
660 AND cdl.line_type ='R'
661 AND cdl.reversed_flag IS NULL
662 AND cdl.line_num_reversed IS NULL
663 AND ei.document_header_id IS NULL
664 AND nvl(ei.historical_flag,'Y') = 'Y'
665 AND ei.expenditure_item_date between nvl(l_Txn_Date,ei.expenditure_item_date) AND sysdate
666 AND ei.project_id = p_project_id
667 AND ei.transaction_source in ('AP EXPENSE', 'AP INVOICE', 'INTERCOMPANY_AP_INVOICES', 'INTERPROJECT_AP_INVOICES', 'AP NRTAX',
668 'AP VARIANCE', 'AP DISCOUNTS' ,'AP ERV') /* changed for bug 9320194 */
669 UNION ALL
670 SELECT /*+ leading(ei) index(ei,pa_expenditure_items_n8) use_nl(cdl,inv,dist) */
671 ei.rowid,
672 cdl.expenditure_item_id,
673 to_number(cdl.system_reference1) vendor_id,
674 to_number(cdl.system_reference2) doc_header_id,
675 to_number(NVL2(LTRIM(cdl.system_reference4, '0123456789'), NULL, cdl.system_reference4)) doc_dist_id,
676 to_number(cdl.system_reference3) doc_line_num,
677 null doc_payment_id,
678 rcv.destination_type_code doc_type,
679 rcv.transaction_type dist_type
680 FROM pa_cost_distribution_lines_all cdl,
681 pa_expenditure_items_all ei,
682 rcv_transactions rcv
683 WHERE cdl.expenditure_item_id = ei.expenditure_item_id
684 AND rcv.transaction_id = to_number(NVL2(LTRIM(cdl.system_reference4, '0123456789'), NULL, cdl.system_reference4))
685 AND rcv.po_distribution_id = to_number(cdl.system_reference3)
686 AND cdl.system_reference2 IS NOT NULL
687 AND cdl.system_reference3 IS NOT NULL
688 AND cdl.line_type ='R'
689 AND cdl.reversed_flag IS NULL
690 AND cdl.line_num_reversed IS NULL
691 AND ei.document_header_id IS NULL
692 AND nvl(ei.historical_flag,'Y') = 'Y'
693 AND ei.expenditure_item_date between nvl(l_Txn_Date,ei.expenditure_item_date) AND sysdate
694 AND ei.project_id = p_project_id
695 AND ei.transaction_source like 'PO %';
696
697 -- Get all the uncosted VI related transactions to be upgraded.
698 CURSOR cur_uncosted_ei(p_project_id in number) IS
699 SELECT ei.rowid,
700 ei.expenditure_item_id
701 FROM pa_expenditure_items_all ei
702 WHERE ei.cost_distributed_flag = 'N'
703 ANd ei.document_header_id IS NULL
704 AND nvl(ei.historical_flag,'Y') = 'Y'
705 AND ei.expenditure_item_date between nvl(l_Txn_Date,ei.expenditure_item_date) AND sysdate
706 AND ei.project_id = p_project_id
707 AND (ei.transaction_source in ('AP EXPENSE', 'AP INVOICE', 'INTERCOMPANY_AP_INVOICES', 'INTERPROJECT_AP_INVOICES', 'AP NRTAX',
708 'AP VARIANCE', 'AP DISCOUNTS' ,'AP ERV') OR ei.transaction_source like 'PO %') /* changed for bug 9320194 */
709 AND NOT EXISTS ( SELECT NULL
710 FROM pa_cost_distribution_lines_all cdl
711 WHERE cdl.expenditure_item_id = ei.expenditure_item_id);
712
713
714 Cursor cur_upg_project is
715 SELECT project_id
716 FROM pa_projects_all
717 WHERE project_id between P_Min_Project_Id and P_Max_Project_Id;
718
719 /* Added for the bug 14284376 starts */
720 CURSOR cur_ap_po_btc(p_project_id in number)
721 IS
722 SELECT ei.rowid,
723 ei.expenditure_item_id,
724 e.vendor_id
725 FROM pa_expenditures_all e,
726 pa_expenditure_items_all ei
727 WHERE e.expenditure_id =ei.expenditure_id
728 and ei.document_header_id is null
729 AND ei.cost_distributed_flag = 'Y'
730 AND NVL(EI.HISTORICAL_FLAG,'Y') = 'Y'
731 AND ei.project_id = p_project_id
732 AND ei.system_linkage_function = 'BTC'
733 AND ei.expenditure_item_date between nvl(l_Txn_Date,ei.expenditure_item_date) AND sysdate
734 and ei.burden_sum_dest_run_id > 0
735 and (ei.vendor_id is null AND e.vendor_id IS NOT NULL);
736
737 /* Added for the bug 14284376 ends */
738
739 Begin
740
741 IF P_Txn_Type = 'WORK TYPE' THEN
742
743 Open C_Ei;
744
745 Loop
746
747 l_EIIdTab.delete;
748 l_WtIdTab.delete;
749 l_WtTpAmtTab.delete;
750 l_InvItmIdTab.delete;
751 l_WIPIdTab.delete;
752 l_UOMTab.delete;
753
754 FETCH C_EI BULK COLLECT INTO
755 l_EIIdTab,
756 l_WtIdTab,
757 l_WtTpAmtTab
758 LIMIT Rows;
759
760 If l_EIIdTab.count = 0 Then
761 Exit;
762 End If;
763
764 FORALL i in l_EIIdTab.first .. l_EIIdTab.last
765 update pa_expenditure_items_all
766 set work_type_id = l_WtIdTab(i),
767 tp_amt_type_code = l_WtTpAmtTab(i)
768 where Expenditure_Item_Id = l_EIIdTab(i);
769
770 l_rowcount:=sql%rowcount;
771
772 FND_FILE.PUT_LINE(FND_FILE.LOG,'No of EI Records updated = '||l_rowcount);
773 commit;
774
775 FORALL j in l_EIIdTab.first .. l_EIIdTab.last
776 update pa_cost_distribution_lines_all
777 set work_type_id = l_WtIdTab(j)
778 where Expenditure_Item_Id = l_EIIdTab(j)
779 and line_type = 'R';
780
781 l_rowcount:=sql%rowcount;
782
783 FND_FILE.PUT_LINE(FND_FILE.LOG,'No of CDL Records updated = '||l_rowcount);
784 commit;
785
786 FORALL k in l_EIIdTab.first .. l_EIIdTab.last
787 update pa_cc_dist_lines_all
788 set tp_amt_type_code = l_WtTpAmtTab(k)
789 where Expenditure_Item_Id = l_EIIdTab(k)
790 and tp_amt_type_code is null;
791
792 l_rowcount:=sql%rowcount;
793
794 FND_FILE.PUT_LINE(FND_FILE.LOG,'No of CCDL Records updated = '||l_rowcount);
795 commit;
796
797 FORALL l in l_EIIdTab.first .. l_EIIdTab.last
798 update pa_draft_invoice_details_all
799 set tp_amt_type_code = l_WtTpAmtTab(l)
800 where Expenditure_Item_Id = l_EIIdTab(l)
801 and tp_amt_type_code is null;
802
803 l_rowcount:=sql%rowcount;
804
805 FND_FILE.PUT_LINE(FND_FILE.LOG,'No of DID Records updated = '||l_rowcount);
806 commit;
807
808 End Loop;
809
810 Close c_Ei;
811
812
813 ELSIF P_Txn_Type = 'PJM' THEN
814
815 OPEN Cur_PJM_Attr;
816
817 Loop
818
819 l_EIIdTab.delete;
820 l_WtIdTab.delete;
821 l_WtTpAmtTab.delete;
822 l_InvItmIdTab.delete;
823 l_WIPIdTab.delete;
824 l_UOMTab.delete;
825
826 FETCH Cur_PJM_Attr BULK COLLECT INTO
827 l_InvItmIdTab
828 ,l_WIPIdTab
829 ,l_UOMTab
830 ,l_EIIdTab
831 LIMIT Rows;
832
833 If l_EIIdTab.count = 0 Then
834 Exit;
835 End If;
836
837 FORALL i in l_EIIdTab.first .. l_EIIdTab.last
838 update pa_expenditure_items_all
839 set inventory_item_id = l_InvItmIdTab(i)
840 ,wip_resource_id = l_WIPIdTab(i)
841 ,unit_of_measure = l_UOMTab(i)
842 where Expenditure_Item_Id = l_EIIdTab(i);
843
844 l_rowcount:=sql%rowcount;
845
846 FND_FILE.PUT_LINE(FND_FILE.LOG, l_rowcount || ' EI Records updated '||to_char(sysdate,'HH:MI:SS'));
847 commit;
848
849 End Loop;
850
851 Close Cur_PJM_Attr;
852
853 ELSIF P_Txn_Type = 'SUPPLIER TYPE' THEN --R12 changes
854
855 FND_FILE.PUT_LINE(FND_FILE.LOG,'Initializing variables for cur_ap_po_ei');
856
857 l_ei_rowid_tbl := rowid_typ(null);
858 l_exp_item_id_tbl := exp_item_id_typ(null);
859 l_vendor_id_tbl := vendor_id_typ(null);
860 l_doc_header_id_tbl := doc_header_id_typ(null);
861 l_doc_dist_id_tbl := doc_dist_id_typ(null);
862 l_doc_line_num_tbl := doc_line_num_typ(null);
863 l_doc_payment_id_tbl := doc_payment_id_typ(null);
864 l_document_tbl := document_typ(null);
865 l_document_dist_tbl := document_dist_typ(null);
866
867 FND_FILE.PUT_LINE(FND_FILE.LOG,'Opening cursor cur_upg_proj');
868
869 FOR upg_proj in cur_upg_project LOOP
870
871 FND_FILE.PUT_LINE(FND_FILE.LOG,'Opening cursor cur_ap_po_ei');
872
873 OPEN cur_ap_po_ei(upg_proj.project_id);
874 LOOP
875
876
877 l_ei_rowid_tbl.delete;
878 l_exp_item_id_tbl.delete;
879 l_vendor_id_tbl.delete;
880 l_doc_header_id_tbl.delete;
881 l_doc_dist_id_tbl.delete;
882 l_doc_line_num_tbl.delete;
883 l_doc_payment_id_tbl.delete;
884 l_document_tbl.delete;
885 l_document_dist_tbl.delete;
886
887 FND_FILE.PUT_LINE(FND_FILE.LOG,'Fetching Cusor cur_ap_po_ei');
888
889 FETCH cur_ap_po_ei BULK COLLECT INTO
890 l_ei_rowid_tbl,
891 l_exp_item_id_tbl,
892 l_vendor_id_tbl,
893 l_doc_header_id_tbl,
894 l_doc_dist_id_tbl,
895 l_doc_line_num_tbl,
896 l_doc_payment_id_tbl,
897 l_document_tbl,
898 l_document_dist_tbl
899 limit l_plsql_max_array_size;
900
901 FND_FILE.PUT_LINE(FND_FILE.LOG,'After Bulk Collecting Cusor cur_ap_po_ei');
902
903 If l_ei_rowid_tbl.count = 0 Then
904 Exit;
905 End If;
906
907 FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating EI columns -R12 ');
908
909 FORALL i in l_ei_rowid_tbl.first..l_ei_rowid_tbl.last
910 UPDATE pa_expenditure_items_all ei
911 SET ei.vendor_id = l_vendor_id_tbl(i),
912 ei.last_update_date = sysdate,
913 ei.document_header_id = l_doc_header_id_tbl(i),
914 ei.document_distribution_id = l_doc_dist_id_tbl(i),
915 ei.document_line_number = l_doc_line_num_tbl(i),
916 ei.document_payment_id = l_doc_payment_id_tbl(i),
917 ei.document_type = l_document_tbl(i),
918 ei.document_distribution_type = l_document_dist_tbl(i),
919 ei.historical_flag = decode(l_doc_header_id_tbl(i),NULL,NULL,nvl(ei.historical_flag,'Y'))
920 WHERE ei.rowid = l_ei_rowid_tbl(i);
921
922 l_rowcount := nvl(l_rowcount, 0) + SQL%ROWCOUNT;
923
924 -- commit transaction here
925 FND_FILE.PUT_LINE(FND_FILE.LOG,'Commit in Loop');
926 commit;
927
928 END LOOP;
929 CLOSE cur_ap_po_ei;
930
931 FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating uncosted EI columns ');
932 -- Update the adjusted and uncosted expenditures. Bug#5381198.
933 FOR uncosted_ei in cur_uncosted_ei(upg_proj.project_id) LOOP
934
935 UPDATE pa_expenditure_items_all ei
936 SET (ei.vendor_id,
937 ei.document_header_id,
938 ei.document_distribution_id,
939 ei.document_line_number,
940 ei.document_payment_id,
941 ei.document_type,
942 ei.document_distribution_type) = (
943 SELECT uei.vendor_id,
944 uei.document_header_id,
945 uei.document_distribution_id,
946 uei.document_line_number,
947 uei.document_payment_id,
948 uei.document_type,
949 uei.document_distribution_type
950 FROM pa_expenditure_items_all uei
951 WHERE uei.document_header_id >0
952 START WITH uei.expenditure_item_id = uncosted_ei.expenditure_item_id
953 CONNECT BY PRIOR NVL(uei.adjusted_expenditure_item_id,uei.transferred_from_exp_item_id)
954 = uei.expenditure_item_id
955 AND rownum = 1
956 ),
957 ei.historical_flag = NVL(ei.historical_flag,'Y')
958 WHERE ei.rowid = uncosted_ei.rowid;
959
960 END LOOP;
961
962 /* Added for the bug 14284376 starts */
963 FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating costed BTC columns starts');
964
965 FOR costed_btc in cur_ap_po_btc(upg_proj.project_id) LOOP
966
967 update pa_expenditure_items_all ei
968 set ei.vendor_id=costed_btc.vendor_id,
969 ei.last_update_date = sysdate
970 where ei.rowid = costed_btc.rowid
971 and ei.expenditure_item_id = costed_btc.expenditure_item_id;
972
973 l_rowcount := nvl(l_rowcount, 0) + SQL%ROWCOUNT;
974 end loop;
975
976 fnd_file.put_line(fnd_file.log,'Updating costed BTC columns ends | lines updated ='||l_rowcount);
977 /* Added for the bug 14284376 ends */
978
979 -- commit transaction here
980 FND_FILE.PUT_LINE(FND_FILE.LOG,'Commit in Loop - Uncosted Expenditures ');
981 commit;
982 END LOOP;
983
984 END IF;
985
986 Exception
987 When Others Then
988 Raise;
989 End;
990
991 End If;
992
993 Exception
994
995 When Others then
996 x_return_status := fnd_api.g_ret_sts_unexp_error;
997 x_error_message_code := (SQLCODE||' '||SQLERRM);
998 raise_application_error(-20001,SQLERRM);
999
1000 End Upgrade_WT_Main;
1001
1002 END PA_WORK_TYPE_UPGRADE;