[Home] [Help]
PACKAGE BODY: APPS.PA_WORK_TYPE_UPGRADE
Source
1 PACKAGE BODY PA_WORK_TYPE_UPGRADE AS
2 /* $Header: PAWKUPGB.pls 120.8.12000000.2 2007/06/15 10:45:48 anuragar 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 IF P_Min_Project_Id IS NULL THEN
187 SELECT Min(P.Project_Id), Max(P.Project_Id)
188 INTO Min_Project_Id,Max_Project_Id
189 FROM Pa_Projects_All P
190 WHERE p.org_id = nvl(p_org_id,p.org_id);
191 ELSE
192 Min_Project_Id := P_Min_Project_Id;
193 Max_Project_Id := P_Max_Project_Id;
194 END IF;
195
196 l_remainder := MOD((Max_Project_Id-Min_Project_Id),p_num_of_processes);
197
198 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Min_Project_Id = ' || Min_Project_Id);
199 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Max_Project_Id = ' || Max_Project_Id);
200
201 l_Max := MIN_Project_Id ;
202
203 IF Min_Project_Id = Max_Project_Id THEN
204
205 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '.....................');
206 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Project_Id = ' || Min_Project_Id);
207
208 -- Call FND API to submit the same request for the EI update
209 l_child_req_id := FND_REQUEST.SUBMIT_REQUEST('PA',
210 'PAWKTPUP',
211 '',
212 '',
213 FALSE,
214 p_txn_type,
215 p_txn_src,
216 1,
217 1,
218 p_org_id,
219 fnd_date.date_to_canonical(l_TXN_DATE),
220 Min_Project_Id,
221 Max_Project_Id);
222 ELSE
223
224 For i in 1..P_Num_Of_Processes Loop
225
226 l_Min := l_Max;
227 l_Max := (l_Min + FLOOR((Max_Project_Id-Min_Project_Id)/p_num_of_processes));
228
229 IF i = P_Num_Of_Processes THEN
230 l_max := l_max + l_remainder;
231 END IF;
232
233 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '.....................');
234 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Min Project_Id = ' || l_Min);
235 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Max Project_Id = ' || l_Max);
236
237 -- Call FND API to submit the same request for the EI update
238 l_child_req_id := FND_REQUEST.SUBMIT_REQUEST('PA',
239 'PAWKTPUP',
240 '',
241 '',
242 FALSE,
243 p_txn_type,
244 p_txn_src,
245 p_num_of_processes,
246 i,
247 p_org_id,
248 fnd_date.date_to_canonical(l_TXN_DATE),
249 l_min,
250 l_max);
251 IF (l_child_req_id = 0) THEN
252 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
253 '...An attempt to submit the upgrade request has failed.');
254 FND_FILE.PUT_LINE(FND_FILE.LOG,
255 '...An attempt to submit the upgrade request has failed.');
256 ELSE
257 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
258 'The process to upgrade the records has been submitted.
259 Please check Request ID: '|| to_char(l_child_req_id));
260 FND_FILE.PUT_LINE(FND_FILE.LOG,
261 'The process to upgrade the records has been submitted.
262 Please check Request ID: '|| to_char(l_child_req_id));
263 END IF;
264
265 End Loop;
266 END IF;
267
268 END IF; -- P_TXN_TYPE = 'SUPPLIER TYPE' -- R12 changes
269
270
271 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start Insert into Temp Table '|| to_char(sysdate, 'HH:MI:SS'));
272
273 IF P_TXN_TYPE <> 'SUPPLIER TYPE' THEN -- R12 changes
274 execute immediate 'alter session enable parallel dml';
275 END IF;
276
277 IF P_Txn_Type = 'WORK TYPE' THEN
278
279 INSERT /*+ APPEND parallel(t) */
280 INTO pa_txn_upgrade_temp t
281 (row_id,
282 pk1_id,
283 worker_id,
284 pk2_id)
285 select /*+ parallel(ei) */ ei.rowid, ei.expenditure_item_id id, null , ei.task_id
286 from pa_expenditure_items_all ei
287 where trunc(ei.expenditure_item_date) >= trunc(l_Txn_Date)
288 and nvl(ei.org_id, -99) = nvl(p_org_id,nvl(ei.org_id,-99)) /* Changed for Bug #6129449 by anuragar */
289 and work_type_id is null;
290
291 l_ins_rowcount := SQL%ROWCOUNT;
292
293 FND_FILE.PUT_LINE(FND_FILE.LOG, l_ins_rowcount||' Records inserted for WORK TYPE '|| to_char(sysdate, 'HH:MI:SS'));
294
295 ELSIF P_Txn_Type = 'PJM' THEN
296
297 /* 3968368: Added two unions to handle the PJM invoice charges upgrade. For such txns,
298 PJM has been populating the relevant columns in the pa_transaction_interface table
299 in the following manner:
300
301 Column Populated value
302 --------------------- ------------------------
303 cdl_system_reference1 PO_Distribution_Id
304 cdl_system_reference2 RCV_Transaction_Id
305 cdl_system_reference3 l_receipt_num
306 orig_exp_txn_reference1 Invoice_Id
307 orig_exp_txn_reference2 NULL
308 orig_exp_txn_reference3 NULL
309
310 From 11.5.7 to now
311
312 Column Populated value
313 --------------------- --------------------------
314 orig_exp_txn_reference1 PO_Distribution_Id
315 orig_exp_txn_reference2 InvRec.RCV_Transaction_Id
316 orig_exp_txn_reference3 l_receipt_num
317
318 */
319
320 INSERT /*+ APPEND parallel(t) */
321 INTO pa_txn_upgrade_temp t
322 (row_id
323 ,pk1_id
324 ,worker_id
325 ,pk2_id
326 ,txn_src
327 ,reference1
328 )
329 SELECT /*+ parallel(ei) */ EI.ROWID, EI.expenditure_item_id, null
330 , EI.orig_transaction_reference, EI.transaction_source, 'CCO'
331 FROM pa_expenditure_items_all EI, pa_expenditures_all EXP
332 WHERE EI.transaction_source = NVL(P_Txn_Src, EI.transaction_source)
333 AND EI.transaction_source IN ('Inventory Misc', 'Inventory', 'PJM_CSTBP_INV_ACCOUNTS'
334 ,'PJM_CSTBP_INV_NO_ACCOUNTS', 'Work In Process'
335 ,'PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS'
336 ,'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS'
337 ,'PJM_NON_CSTBP_ST_ACCOUNTS')
338 AND EI.unit_of_measure IS NULL
339 AND TRUNC(ei.expenditure_item_date) >= TRUNC(l_TXN_DATE)
340 AND EI.expenditure_id = EXP.expenditure_id
341 AND EXP.orig_exp_txn_reference1 is null
342 UNION ALL
343 SELECT /*+ parallel(ei) */ EI.ROWID, EI.expenditure_item_id, null
344 , EXP.orig_exp_txn_reference1, EI.transaction_source, 'PODIST'
345 FROM pa_expenditure_items_all EI, pa_expenditures_all EXP
346 WHERE EI.transaction_source IN ('Inventory Misc', 'Inventory', 'PJM_CSTBP_INV_ACCOUNTS'
347 ,'PJM_CSTBP_INV_NO_ACCOUNTS', 'Work In Process'
348 ,'PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS'
349 ,'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS'
350 ,'PJM_NON_CSTBP_ST_ACCOUNTS')
351 AND EI.unit_of_measure IS NULL
352 AND TRUNC(ei.expenditure_item_date) >= TRUNC(l_TXN_DATE)
353 AND EI.expenditure_id = EXP.expenditure_id
354 AND EXP.orig_exp_txn_reference1 is not null
355 AND EXP.orig_exp_txn_reference2 is not null
356 UNION ALL
357 SELECT /*+ parallel(ei) */ EI.ROWID, EI.expenditure_item_id, null
358 , CDL.system_reference1, EI.transaction_source, 'PODIST'
359 FROM pa_expenditure_items_all EI, pa_expenditures_all EXP, pa_cost_distribution_lines_all CDL
360 WHERE EI.transaction_source IN ('Inventory Misc', 'Inventory', 'PJM_CSTBP_INV_ACCOUNTS'
361 ,'PJM_CSTBP_INV_NO_ACCOUNTS', 'Work In Process'
362 ,'PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS'
363 ,'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS'
364 ,'PJM_NON_CSTBP_ST_ACCOUNTS')
365 AND EI.unit_of_measure IS NULL
366 AND TRUNC(ei.expenditure_item_date) >= TRUNC(l_TXN_DATE)
367 AND EI.expenditure_id = EXP.expenditure_id
368 AND EXP.orig_exp_txn_reference1 is not null
369 AND EXP.orig_exp_txn_reference2 is null
370 AND EI.expenditure_item_id = CDL.expenditure_item_id
371 AND CDL.line_num = 1;
372
373 l_ins_rowcount := SQL%ROWCOUNT;
374
375 FND_FILE.PUT_LINE(FND_FILE.LOG, l_ins_rowcount || ' Records inserted for PJM '|| to_char(sysdate, 'HH:MI:SS'));
376
377 END IF;
378
379 commit;
380
381 IF P_Txn_Type <> 'SUPPLIER TYPE' THEN
382
383 UPDATE /*+ parallel(t) */ pa_txn_upgrade_temp t
384 SET worker_id = (ceil(rownum / ceil(l_ins_rowcount / p_num_of_processes )));
385
386 commit;
387
388 l_ReqStsTab.delete; -- PJM Changes
389
390 For i in 1..P_Num_Of_Processes Loop
391
392 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '.....................');
393
394 -- Call FND API to submit the same request for the EI update
395 l_child_req_id := FND_REQUEST.SUBMIT_REQUEST('PA',
396 'PAWKTPUP',
397 '',
398 '',
399 FALSE,
400 p_txn_type,
401 p_txn_src,
402 p_num_of_processes,
403 i,
404 p_org_id,
405 fnd_date.date_to_canonical(l_TXN_DATE) );
406
407 IF (l_child_req_id = 0) THEN
408 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
409 '...An attempt to submit the upgrade request has failed.');
410 FND_FILE.PUT_LINE(FND_FILE.LOG,
411 '...An attempt to submit the upgrade request has failed.');
412 ELSE
413 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
414 'The process to upgrade the records has been submitted.
415 Please check Request ID: '|| to_char(l_child_req_id));
416 FND_FILE.PUT_LINE(FND_FILE.LOG,
417 'The process to upgrade the records has been submitted.
418 Please check Request ID: '|| to_char(l_child_req_id));
419 END IF;
420
421 l_ReqStsTab(i).Request_Id := l_child_req_id;
422
423 if (FND_CONCURRENT.GET_REQUEST_STATUS
424 (
425 l_child_req_id,
426 null, -- pa_schema_name
427 null, -- request_name
428 l_phase,
429 l_status,
430 l_dev_phase,
431 l_dev_status,
432 l_message
433 )) then
434 null;
435 end if;
436
437 l_ReqStsTab(i).Status := l_dev_phase;
438
439 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Req = ' || l_ReqStsTab(i).Request_Id || ' Sts = ' || l_ReqStsTab(i).Status);
440
441 End Loop;
442
443 commit;
444
445 While NOTCOMPLETE Loop
446
447 dbms_lock.sleep(60); /* changed sleep seconds from 300 to 60 for 4130368 */
448
449 FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside Loop forever '|| to_char(sysdate,'HH:MI:SS') );
450
451 NOTCOMPLETE := FALSE;
452
453 FOR j in l_ReqStsTab.first..l_ReqStsTab.LAST LOOP
454
455 if (FND_CONCURRENT.GET_REQUEST_STATUS
456 (
457 l_ReqStsTab(j).request_id,
458 null, -- pa_schema_name
459 null, -- request_name
460 l_phase,
461 l_status,
462 l_dev_phase,
463 l_dev_status,
464 l_message
465 )) then
466 null;
467 end if;
468
469 l_ReqStsTab(j).Status := l_dev_phase;
470
471 If l_dev_phase <> 'COMPLETE' Then
472 FND_FILE.PUT_LINE(FND_FILE.LOG,'Loop Again'|| to_char(sysdate,'HH:MI:SS'));
473 NOTCOMPLETE := TRUE;
474 exit;
475 End If;
476
477 END LOOP;
478
479 End Loop;
480
481 IF (NOT FND_INSTALLATION.GET_APP_INFO('PA', l_inssts, l_industry, l_pa_schema)) THEN
482 raise_application_error(-20001,SQLERRM);
483 END IF;
484
485 FND_FILE.PUT_LINE(FND_FILE.LOG,'Truncating Table '|| l_pa_schema || '.pa_txn_upgrade_temp '||
486 to_char(sysdate,'HH:MI:SS') );
487
488 execute immediate ('Truncate table ' || l_pa_schema || '.pa_txn_upgrade_temp');
489
490 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling pa_uom.get_uom for updating UOM Meaning '|| to_char(sysdate,'HH:MI:SS') );
491
492 l_get_uom := 'F';
493
494 l_get_uom := pa_uom.get_uom(fnd_global.USER_ID);
495
496 IF (l_get_uom = 'S') THEN
497
498 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Successfully Updated UOM Meanings in PA '|| to_char(sysdate,'HH:MI:SS'));
499
500 ELSE
501
502 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error While Updating UOM Meanings in PA '||l_get_uom);
503 raise_application_error(-20001,SQLERRM);
504
505 END IF;
506
507 commit;
508 END IF; -- P_Txn_Type <> 'SUPPLIER TYPE'
509
510 End;
511
512 End If; -- p_worker_id
513
514 If (p_worker_id <> 0) Then
515
516 FND_FILE.PUT_LINE(FND_FILE.LOG, p_worker_id|| ' is the worker_id '|| to_char(sysdate,'HH:MI:SS'));
517
518 Declare
519
520 Cursor C_Ei Is
521 Select Temp.Pk1_Id,
522 Tsk.Work_Type_Id,
523 Wt.Tp_Amt_Type_Code
524 From pa_txn_upgrade_temp Temp,
525 Pa_Tasks Tsk,
526 Pa_Work_Types_B Wt
527 Where Worker_Id = p_worker_id
528 And Temp.Pk2_id = Tsk.Task_Id
529 And Tsk.Work_Type_Id = Wt.Work_Type_Id;
530
531 CURSOR Cur_PJM_Attr IS
532 SELECT MMT.Inventory_Item_Id Inventory_Item_ID
533 ,to_number(NULL) Wip_Resource_Id
534 ,MSI.Primary_UOM_Code UOM
535 ,Temp.Pk1_Id
536 FROM MTL_MATERIAL_TRANSACTIONS MMT
537 ,MTL_SYSTEM_ITEMS MSI
538 ,Pa_Txn_Upgrade_Temp Temp
539 WHERE Temp.Worker_Id = p_worker_id
540 And Temp.Pk2_id = MMT.Transaction_Id
541 AND MMT.Inventory_Item_Id = MSI.Inventory_Item_Id
542 AND MMT.Organization_Id =MSI.Organization_Id
543 AND Temp.Txn_Src IN ('Inventory Misc', 'Inventory'
544 ,'PJM_CSTBP_INV_ACCOUNTS', 'PJM_CSTBP_INV_NO_ACCOUNTS')
545 AND Temp.Reference1 = 'CCO'
546 UNION ALL
547 SELECT to_number(NULL) Inventory_Item_ID
548 ,WT.Resource_Id Wip_Resource_ID
549 ,WT.Primary_UOM UOM
550 ,Temp.Pk1_ID
551 FROM WIP_TRANSACTIONS WT
552 ,Pa_Txn_Upgrade_Temp Temp
553 WHERE Temp.Worker_Id = p_worker_id
554 And Temp.Pk2_ID = WT.Transaction_ID
555 AND Temp.Txn_Src IN ('Work In Process'
556 ,'PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS'
557 ,'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS'
558 ,'PJM_NON_CSTBP_ST_ACCOUNTS')
559 AND Temp.Reference1 = 'CCO'
560 UNION ALL
561 SELECT decode(PoDist.destination_type_code, 'INVENTORY' , PoLine.Item_Id, null) Inventory_Item_ID ,
562 decode(PoDist.destination_type_code, 'SHOP FLOOR', PoDist.Bom_Resource_Id, null) Wip_Resource_ID ,
563 'DOLLARS' UOM ,
564 Temp.Pk1_Id
565 FROM Pa_Txn_Upgrade_Temp Temp,
566 Po_Distributions_All PoDist,
567 Po_Lines_All PoLine
568 WHERE Temp.Worker_Id = p_worker_id
569 AND Temp.Pk2_Id = PoDist.po_distribution_id
570 AND PoDist.Po_Line_Id = PoLine.Po_Line_Id
571 AND Temp.Txn_Src in ('Inventory Misc', 'Inventory', 'PJM_CSTBP_INV_ACCOUNTS', 'PJM_CSTBP_INV_NO_ACCOUNTS',
572 'Work In Process','PJM_CSTBP_ST_ACCOUNTS', 'PJM_CSTBP_ST_NO_ACCOUNTS',
573 'PJM_CSTBP_WIP_ACCOUNTS', 'PJM_CSTBP_WIP_NO_ACCOUNTS','PJM_NON_CSTBP_ST_ACCOUNTS')
574 AND Temp.Reference1 = 'PODIST';
575
576
577 l_InvItmIdTab PA_PLSQL_DATATYPES.IdTabTyp;
578 l_WIPIdTab PA_PLSQL_DATATYPES.IdTabTyp;
579 l_UOMTab PA_PLSQL_DATATYPES.Char30TabTyp;
580
581 l_EIIdTab PA_PLSQL_DATATYPES.IdTabTyp;
582 l_WtIdTab PA_PLSQL_DATATYPES.IdTabTyp;
583 l_WtTpAmtTab PA_PLSQL_DATATYPES.Char30TabTyp;
584
585 Rows number := 1000;
586 l_rowcount number :=0;
587
588 TYPE rowid_typ IS TABLE OF varchar2(30) ;
589 TYPE exp_item_id_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.expenditure_item_id%TYPE;
590 TYPE vendor_id_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.vendor_id%TYPE;
591 TYPE doc_header_id_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.document_header_id%TYPE;
592 TYPE doc_dist_id_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.document_distribution_id%TYPE;
593 TYPE doc_line_num_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.document_line_number%TYPE;
594 TYPE doc_payment_id_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.document_payment_id%TYPE;
595 TYPE document_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.document_type%TYPE;
596 TYPE document_dist_typ IS TABLE OF PA_EXPENDITURE_ITEMS_ALL.document_distribution_type%TYPE;
597
598 l_ei_rowid_tbl rowid_typ ;
599 l_exp_item_id_tbl exp_item_id_typ;
600 l_vendor_id_tbl vendor_id_typ;
601 l_doc_header_id_tbl doc_header_id_typ;
602 l_doc_dist_id_tbl doc_dist_id_typ;
603 l_doc_line_num_tbl doc_line_num_typ;
604 l_doc_payment_id_tbl doc_payment_id_typ;
605 l_document_tbl document_typ;
606 l_document_dist_tbl document_dist_typ;
607 l_plsql_max_array_size number := 200;
608
609 -- Get all the VI related transactions to be upgraded.
610 CURSOR cur_ap_po_ei(p_project_id in number) IS
611 SELECT /*+ leading(ei) index(ei,pa_expenditure_items_n8) use_nl(cdl,inv,dist) */
612 ei.rowid,
613 cdl.expenditure_item_id,
614 to_number(cdl.system_reference1) vendor_id,
615 inv.invoice_id doc_header_id,
616 dist.invoice_distribution_id doc_dist_id,
617 dist.invoice_line_number doc_line_num,
618 NVL2(LTRIM(cdl.system_reference4, '0123456789'), NULL, cdl.system_reference4) doc_payment_id,
619 inv.invoice_type_lookup_code doc_type,
620 dist.line_type_lookup_code dist_type
621 FROM pa_cost_distribution_lines_all cdl,
622 pa_expenditure_items_all ei,
623 ap_invoice_distributions_all dist,
624 ap_invoices_all inv
625 WHERE cdl.expenditure_item_id = ei.expenditure_item_id
626 AND inv.invoice_id = to_number(cdl.system_reference2)
627 AND dist.invoice_id = to_number(cdl.system_reference2)
628 AND dist.invoice_id = inv.invoice_id
629 AND dist.project_id > 0
630 AND dist.old_dist_line_number = to_number(cdl.system_reference3)
631 AND ((dist.line_type_lookup_code = decode(ei.transaction_source,'AP VARIANCE',cdl.system_reference4,
632 'AP INVOICE','ITEM',
633 'AP NRTAX','NONREC_TAX',dist.line_type_lookup_code)
634 AND ei.transaction_source <> 'AP DISCOUNTS'
635 OR dist.line_type_lookup_code = DECODE(ei.transaction_source,'AP VARIANCE','T'||cdl.system_reference4))
636 OR dist.line_type_lookup_code = DECODE(ei.transaction_source,'AP INVOICE','PREPAY')
637 OR ei.transaction_source = 'AP DISCOUNTS' and dist.line_type_lookup_code NOT IN ('TERV','TRV','ERV','IPV','TIPV'))
638 AND cdl.system_reference2 IS NOT NULL
639 AND cdl.system_reference3 IS NOT NULL
640 AND cdl.line_type ='R'
641 AND cdl.reversed_flag IS NULL
642 AND cdl.line_num_reversed IS NULL
643 AND ei.document_header_id IS NULL
644 AND nvl(ei.historical_flag,'Y') = 'Y'
645 AND ei.expenditure_item_date between nvl(l_Txn_Date,ei.expenditure_item_date) AND sysdate
646 AND ei.project_id = p_project_id
647 AND ei.transaction_source like 'AP %'
648 UNION ALL
649 SELECT /*+ leading(ei) index(ei,pa_expenditure_items_n8) use_nl(cdl,inv,dist) */
650 ei.rowid,
651 cdl.expenditure_item_id,
652 to_number(cdl.system_reference1) vendor_id,
653 to_number(cdl.system_reference2) doc_header_id,
654 to_number(NVL2(LTRIM(cdl.system_reference4, '0123456789'), NULL, cdl.system_reference4)) doc_dist_id,
655 to_number(cdl.system_reference3) doc_line_num,
656 null doc_payment_id,
657 rcv.destination_type_code doc_type,
658 rcv.transaction_type dist_type
659 FROM pa_cost_distribution_lines_all cdl,
660 pa_expenditure_items_all ei,
661 rcv_transactions rcv
662 WHERE cdl.expenditure_item_id = ei.expenditure_item_id
663 AND rcv.transaction_id = to_number(NVL2(LTRIM(cdl.system_reference4, '0123456789'), NULL, cdl.system_reference4))
664 AND rcv.po_distribution_id = to_number(cdl.system_reference3)
665 AND cdl.system_reference2 IS NOT NULL
666 AND cdl.system_reference3 IS NOT NULL
667 AND cdl.line_type ='R'
668 AND cdl.reversed_flag IS NULL
669 AND cdl.line_num_reversed IS NULL
670 AND ei.document_header_id IS NULL
671 AND nvl(ei.historical_flag,'Y') = 'Y'
672 AND ei.expenditure_item_date between nvl(l_Txn_Date,ei.expenditure_item_date) AND sysdate
673 AND ei.project_id = p_project_id
674 AND ei.transaction_source like 'PO %';
675
676 -- Get all the uncosted VI related transactions to be upgraded.
677 CURSOR cur_uncosted_ei(p_project_id in number) IS
678 SELECT ei.rowid,
679 ei.expenditure_item_id
680 FROM pa_expenditure_items_all ei
681 WHERE ei.cost_distributed_flag = 'N'
682 ANd ei.document_header_id IS NULL
683 AND nvl(ei.historical_flag,'Y') = 'Y'
684 AND ei.expenditure_item_date between nvl(l_Txn_Date,ei.expenditure_item_date) AND sysdate
685 AND ei.project_id = p_project_id
686 AND (ei.transaction_source like 'AP %' OR ei.transaction_source like 'PO %')
687 AND NOT EXISTS ( SELECT NULL
688 FROM pa_cost_distribution_lines_all cdl
689 WHERE cdl.expenditure_item_id = ei.expenditure_item_id);
690
691
692 Cursor cur_upg_project is
693 SELECT project_id
694 FROM pa_projects_all
695 WHERE project_id between P_Min_Project_Id and P_Max_Project_Id;
696
697 Begin
698
699 IF P_Txn_Type = 'WORK TYPE' THEN
700
701 Open C_Ei;
702
703 Loop
704
705 l_EIIdTab.delete;
706 l_WtIdTab.delete;
707 l_WtTpAmtTab.delete;
708 l_InvItmIdTab.delete;
709 l_WIPIdTab.delete;
710 l_UOMTab.delete;
711
712 FETCH C_EI BULK COLLECT INTO
713 l_EIIdTab,
714 l_WtIdTab,
715 l_WtTpAmtTab
716 LIMIT Rows;
717
718 If l_EIIdTab.count = 0 Then
719 Exit;
720 End If;
721
722 FORALL i in l_EIIdTab.first .. l_EIIdTab.last
723 update pa_expenditure_items_all
724 set work_type_id = l_WtIdTab(i),
725 tp_amt_type_code = l_WtTpAmtTab(i)
726 where Expenditure_Item_Id = l_EIIdTab(i);
727
728 l_rowcount:=sql%rowcount;
729
730 FND_FILE.PUT_LINE(FND_FILE.LOG,'No of EI Records updated = '||l_rowcount);
731 commit;
732
733 FORALL j in l_EIIdTab.first .. l_EIIdTab.last
734 update pa_cost_distribution_lines_all
735 set work_type_id = l_WtIdTab(j)
736 where Expenditure_Item_Id = l_EIIdTab(j)
737 and line_type = 'R';
738
739 l_rowcount:=sql%rowcount;
740
741 FND_FILE.PUT_LINE(FND_FILE.LOG,'No of CDL Records updated = '||l_rowcount);
742 commit;
743
744 FORALL k in l_EIIdTab.first .. l_EIIdTab.last
745 update pa_cc_dist_lines_all
746 set tp_amt_type_code = l_WtTpAmtTab(k)
747 where Expenditure_Item_Id = l_EIIdTab(k)
748 and tp_amt_type_code is null;
749
750 l_rowcount:=sql%rowcount;
751
752 FND_FILE.PUT_LINE(FND_FILE.LOG,'No of CCDL Records updated = '||l_rowcount);
753 commit;
754
755 FORALL l in l_EIIdTab.first .. l_EIIdTab.last
756 update pa_draft_invoice_details_all
757 set tp_amt_type_code = l_WtTpAmtTab(l)
758 where Expenditure_Item_Id = l_EIIdTab(l)
759 and tp_amt_type_code is null;
760
761 l_rowcount:=sql%rowcount;
762
763 FND_FILE.PUT_LINE(FND_FILE.LOG,'No of DID Records updated = '||l_rowcount);
764 commit;
765
766 End Loop;
767
768 Close c_Ei;
769
770
771 ELSIF P_Txn_Type = 'PJM' THEN
772
773 OPEN Cur_PJM_Attr;
774
775 Loop
776
777 l_EIIdTab.delete;
778 l_WtIdTab.delete;
779 l_WtTpAmtTab.delete;
780 l_InvItmIdTab.delete;
781 l_WIPIdTab.delete;
782 l_UOMTab.delete;
783
784 FETCH Cur_PJM_Attr BULK COLLECT INTO
785 l_InvItmIdTab
786 ,l_WIPIdTab
787 ,l_UOMTab
788 ,l_EIIdTab
789 LIMIT Rows;
790
791 If l_EIIdTab.count = 0 Then
792 Exit;
793 End If;
794
795 FORALL i in l_EIIdTab.first .. l_EIIdTab.last
796 update pa_expenditure_items_all
797 set inventory_item_id = l_InvItmIdTab(i)
798 ,wip_resource_id = l_WIPIdTab(i)
799 ,unit_of_measure = l_UOMTab(i)
800 where Expenditure_Item_Id = l_EIIdTab(i);
801
802 l_rowcount:=sql%rowcount;
803
804 FND_FILE.PUT_LINE(FND_FILE.LOG, l_rowcount || ' EI Records updated '||to_char(sysdate,'HH:MI:SS'));
805 commit;
806
807 End Loop;
808
809 Close Cur_PJM_Attr;
810
811 ELSIF P_Txn_Type = 'SUPPLIER TYPE' THEN --R12 changes
812
813 FND_FILE.PUT_LINE(FND_FILE.LOG,'Initializing variables for cur_ap_po_ei');
814
815 l_ei_rowid_tbl := rowid_typ(null);
816 l_exp_item_id_tbl := exp_item_id_typ(null);
817 l_vendor_id_tbl := vendor_id_typ(null);
818 l_doc_header_id_tbl := doc_header_id_typ(null);
819 l_doc_dist_id_tbl := doc_dist_id_typ(null);
820 l_doc_line_num_tbl := doc_line_num_typ(null);
821 l_doc_payment_id_tbl := doc_payment_id_typ(null);
822 l_document_tbl := document_typ(null);
823 l_document_dist_tbl := document_dist_typ(null);
824
825 FND_FILE.PUT_LINE(FND_FILE.LOG,'Opening cursor cur_upg_proj');
826
827 FOR upg_proj in cur_upg_project LOOP
828
829 FND_FILE.PUT_LINE(FND_FILE.LOG,'Opening cursor cur_ap_po_ei');
830
831 OPEN cur_ap_po_ei(upg_proj.project_id);
832 LOOP
833
834
835 l_ei_rowid_tbl.delete;
836 l_exp_item_id_tbl.delete;
837 l_vendor_id_tbl.delete;
838 l_doc_header_id_tbl.delete;
839 l_doc_dist_id_tbl.delete;
840 l_doc_line_num_tbl.delete;
841 l_doc_payment_id_tbl.delete;
842 l_document_tbl.delete;
843 l_document_dist_tbl.delete;
844
845 FND_FILE.PUT_LINE(FND_FILE.LOG,'Fetching Cusor cur_ap_po_ei');
846
847 FETCH cur_ap_po_ei BULK COLLECT INTO
848 l_ei_rowid_tbl,
849 l_exp_item_id_tbl,
850 l_vendor_id_tbl,
851 l_doc_header_id_tbl,
852 l_doc_dist_id_tbl,
853 l_doc_line_num_tbl,
854 l_doc_payment_id_tbl,
855 l_document_tbl,
856 l_document_dist_tbl
857 limit l_plsql_max_array_size;
858
859 FND_FILE.PUT_LINE(FND_FILE.LOG,'After Bulk Collecting Cusor cur_ap_po_ei');
860
861 If l_ei_rowid_tbl.count = 0 Then
862 Exit;
863 End If;
864
865 FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating EI columns -R12 ');
866
867 FORALL i in l_ei_rowid_tbl.first..l_ei_rowid_tbl.last
868 UPDATE pa_expenditure_items_all ei
869 SET ei.vendor_id = l_vendor_id_tbl(i),
870 ei.last_update_date = sysdate,
871 ei.document_header_id = l_doc_header_id_tbl(i),
872 ei.document_distribution_id = l_doc_dist_id_tbl(i),
873 ei.document_line_number = l_doc_line_num_tbl(i),
874 ei.document_payment_id = l_doc_payment_id_tbl(i),
875 ei.document_type = l_document_tbl(i),
876 ei.document_distribution_type = l_document_dist_tbl(i),
877 ei.historical_flag = decode(l_doc_header_id_tbl(i),NULL,NULL,nvl(ei.historical_flag,'Y'))
878 WHERE ei.rowid = l_ei_rowid_tbl(i);
879
880 l_rowcount := nvl(l_rowcount, 0) + SQL%ROWCOUNT;
881
882 -- commit transaction here
883 FND_FILE.PUT_LINE(FND_FILE.LOG,'Commit in Loop');
884 commit;
885
886 END LOOP;
887 CLOSE cur_ap_po_ei;
888
889 FND_FILE.PUT_LINE(FND_FILE.LOG,'Updating uncosted EI columns ');
890 -- Update the adjusted and uncosted expenditures. Bug#5381198.
891 FOR uncosted_ei in cur_uncosted_ei(upg_proj.project_id) LOOP
892
893 UPDATE pa_expenditure_items_all ei
894 SET (ei.vendor_id,
895 ei.document_header_id,
896 ei.document_distribution_id,
897 ei.document_line_number,
898 ei.document_payment_id,
899 ei.document_type,
900 ei.document_distribution_type) = (
901 SELECT uei.vendor_id,
902 uei.document_header_id,
903 uei.document_distribution_id,
904 uei.document_line_number,
905 uei.document_payment_id,
906 uei.document_type,
907 uei.document_distribution_type
908 FROM pa_expenditure_items_all uei
909 WHERE uei.document_header_id >0
910 START WITH uei.expenditure_item_id = uncosted_ei.expenditure_item_id
911 CONNECT BY PRIOR NVL(uei.adjusted_expenditure_item_id,uei.transferred_from_exp_item_id)
912 = uei.expenditure_item_id
913 AND rownum = 1
914 ),
915 ei.historical_flag = NVL(ei.historical_flag,'Y')
916 WHERE ei.rowid = uncosted_ei.rowid;
917
918 END LOOP;
919
920 -- commit transaction here
921 FND_FILE.PUT_LINE(FND_FILE.LOG,'Commit in Loop - Uncosted Expenditures ');
922 commit;
923 END LOOP;
924
925 END IF;
926
927 Exception
928 When Others Then
929 Raise;
930 End;
931
932 End If;
933
934 Exception
935
936 When Others then
937 x_return_status := fnd_api.g_ret_sts_unexp_error;
938 x_error_message_code := (SQLCODE||' '||SQLERRM);
939 raise_application_error(-20001,SQLERRM);
940
941 End Upgrade_WT_Main;
942
943 END PA_WORK_TYPE_UPGRADE;