[Home] [Help]
PACKAGE BODY: APPS.PJM_TASK_AUTOASSIGN_COPY
Source
1 PACKAGE BODY PJM_TASK_AUTOASSIGN_COPY AS
2 /* $Header: PJMTACRB.pls 115.5 2002/10/29 20:15:19 alaw noship $ */
3
4 --
5 -- Global Declarations
6 --
7 G_PKG_NAME VARCHAR2(30) := 'PJM_TASK_AUTOASSIGN_COPY';
8
9 --
10 -- Private Functions and Procedures
11 --
12 FUNCTION Default_Task
13 ( P_Project_ID IN NUMBER
14 , P_Org_ID IN NUMBER
15 , P_Assignment_Type IN VARCHAR2
16 ) RETURN NUMBER IS
17
18 CURSOR c IS
19 SELECT task_id
20 FROM pjm_default_tasks
21 WHERE organization_id = P_Org_ID
22 AND project_id = P_Project_ID
23 AND assignment_type = P_Assignment_Type
24 AND inventory_item_id is null
25 AND category_id is null
26 AND po_header_id is null
27 AND subinventory_code is null
28 AND procure_flag is null
29 AND standard_operation_id is null
30 AND assembly_item_id is null
31 AND department_id is null
32 AND wip_entity_pattern is null
33 AND wip_matl_txn_type is null
34 AND to_organization_id is null;
35
36 L_Project_ID NUMBER := -1;
37 L_Org_ID NUMBER := -1;
38 L_Assignment_Type VARCHAR2(30) := '*';
39 L_Task_ID NUMBER := NULL;
40
41 BEGIN
42
43 IF ( L_Task_ID IS NULL
44 OR ( L_Project_ID <> P_Project_ID
45 OR L_Org_ID <> P_Org_ID
46 OR L_Assignment_Type <> P_Assignment_Type ) ) THEN
47
48 OPEN c;
49 FETCH c INTO L_Task_ID;
50 CLOSE c;
51
52 L_Project_ID := P_Project_ID;
53 L_Org_ID := P_Org_ID;
54 L_Assignment_Type := P_Assignment_Type;
55
56 END IF;
57
58 RETURN ( L_Task_ID );
59
60 END Default_Task;
61
62
63 --
64 -- Functions and Procedures
65 --
66 PROCEDURE Copy_Rules
67 ( P_From_Project_ID IN NUMBER
68 , P_To_Project_ID IN NUMBER
69 , P_Organization_ID IN NUMBER
70 , P_Copy_Option IN VARCHAR2
71 , P_Use_Default_Task IN VARCHAR2
72 , X_Return_Status OUT NOCOPY VARCHAR2
73 , X_Msg_Count OUT NOCOPY NUMBER
74 , X_Msg_Data OUT NOCOPY VARCHAR2
75 , X_Count1 OUT NOCOPY NUMBER
76 , X_Count2 OUT NOCOPY NUMBER
77 ) IS
78
79 CURSOR DSrc IS
80 SELECT assignment_type
81 , project_id
82 , task_id
83 , pjm_project.all_task_idtonum( task_id ) task_number
84 , organization_id
85 , creation_date
86 , created_by
87 , last_update_date
88 , last_updated_by
89 , last_update_login
90 , inventory_item_id
91 , category_id
92 , po_header_id
93 , subinventory_code
94 , procure_flag
95 , standard_operation_id
96 , assembly_item_id
97 , department_id
98 , wip_entity_pattern
99 , wip_matl_txn_type
100 , to_organization_id
101 , comments
102 , task_attribute_category
103 , task_attribute1
104 , task_attribute2
105 , task_attribute3
106 , task_attribute4
107 , task_attribute5
108 , task_attribute6
109 , task_attribute7
110 , task_attribute8
111 , task_attribute9
112 , task_attribute10
113 , task_attribute11
114 , task_attribute12
115 , task_attribute13
116 , task_attribute14
117 , task_attribute15
118 FROM pjm_default_tasks dt
119 WHERE project_id = P_From_Project_ID
120 AND organization_id = nvl( P_Organization_ID , organization_id )
121 AND EXISTS (
122 --
123 -- Make sure the to project is specified in the organization
124 --
125 SELECT null
126 FROM pjm_project_parameters
127 WHERE organization_id = dt.organization_id
128 AND project_id = P_To_Project_ID
129 UNION ALL
130 SELECT null
131 FROM pjm_org_parameters
132 WHERE organization_id = dt.organization_id
133 AND common_project_id = P_To_Project_ID
134 )
135 AND (
136 inventory_item_id is null
137 AND category_id is null
138 AND po_header_id is null
139 AND subinventory_code is null
140 AND procure_flag is null
141 AND standard_operation_id is null
142 AND assembly_item_id is null
143 AND department_id is null
144 AND wip_entity_pattern is null
145 AND wip_matl_txn_type is null
146 AND to_organization_id is null
147 ) ORDER BY organization_id , assignment_type;
148
149 CURSOR Src IS
150 SELECT assignment_type
151 , project_id
152 , task_id
153 , pjm_project.all_task_idtonum( task_id ) task_number
154 , organization_id
155 , creation_date
156 , created_by
157 , last_update_date
158 , last_updated_by
159 , last_update_login
160 , inventory_item_id
161 , category_id
162 , po_header_id
163 , subinventory_code
164 , procure_flag
165 , standard_operation_id
166 , assembly_item_id
167 , department_id
168 , wip_entity_pattern
169 , wip_matl_txn_type
170 , to_organization_id
171 , comments
172 , task_attribute_category
173 , task_attribute1
174 , task_attribute2
175 , task_attribute3
176 , task_attribute4
177 , task_attribute5
178 , task_attribute6
179 , task_attribute7
180 , task_attribute8
181 , task_attribute9
182 , task_attribute10
183 , task_attribute11
184 , task_attribute12
185 , task_attribute13
186 , task_attribute14
187 , task_attribute15
188 FROM pjm_default_tasks dt
189 WHERE project_id = P_From_Project_ID
190 AND organization_id = nvl( P_Organization_ID , organization_id )
191 AND EXISTS (
192 --
193 -- Make sure the to project is specified in the organization
194 --
195 SELECT null
196 FROM pjm_project_parameters
197 WHERE organization_id = dt.organization_id
198 AND project_id = P_To_Project_ID
199 UNION ALL
200 SELECT null
201 FROM pjm_org_parameters
202 WHERE organization_id = dt.organization_id
203 AND common_project_id = P_To_Project_ID
204 )
205 AND NOT (
206 inventory_item_id is null
207 AND category_id is null
208 AND po_header_id is null
209 AND subinventory_code is null
210 AND procure_flag is null
211 AND standard_operation_id is null
212 AND assembly_item_id is null
213 AND department_id is null
214 AND wip_entity_pattern is null
215 AND wip_matl_txn_type is null
216 AND to_organization_id is null
217 ) ORDER BY organization_id , assignment_type;
218
219 DSrcRec DSrc%RowType;
220 SrcRec Src%RowType;
221
222 UserID NUMBER := FND_GLOBAL.User_ID;
223 LoginID NUMBER := FND_GLOBAL.Login_ID;
224 ProjNum VARCHAR2(30);
225 TaskID NUMBER := NULL;
226
227 BEGIN
228 --
229 -- Standard Start of API savepoint
230 --
231 SAVEPOINT copy_rules;
232
233 X_Count1 := 0;
234 X_Count2 := 0;
235 X_Return_Status := FND_API.G_RET_STS_SUCCESS;
236
237 ProjNum := PJM_PROJECT.All_Proj_IDToNum( P_To_Project_ID );
238
239 --
240 -- First copy the default assignment rules
241 --
242 FOR DSrcRec IN DSrc LOOP
243 --
244 -- Try to find a match in the To Project based on Task Number
245 --
246 TaskID := PJM_PROJECT.Val_Task_NumToID( ProjNum , DSrcRec.Task_Number );
247
248 IF ( TaskID IS NOT NULL ) THEN
249 --
250 -- If copy option of "REPLACE" is specified, then update
251 -- existing rule if found
252 --
253 IF ( P_Copy_Option = 'REPLACE' ) THEN
254
255 UPDATE pjm_default_tasks
256 SET task_id = TaskID
257 , last_update_date = SYSDATE
258 , last_updated_by = UserID
259 , last_update_login = LoginID
260 WHERE project_id = P_To_Project_ID
261 AND organization_id = DSrcRec.Organization_ID
262 AND assignment_type = DSrcRec.Assignment_Type
263 AND nvl(inventory_item_id , -1) = nvl(DSrcRec.Inventory_Item_ID , -1)
264 AND nvl(category_id , -1) = nvl(DSrcRec.Category_ID , -1)
265 AND nvl(subinventory_code , '***') = nvl(DSrcRec.Subinventory_Code , '***')
266 AND nvl(po_header_id , -1) = nvl(DSrcRec.PO_Header_ID , -1)
267 AND nvl(procure_flag , '*') = nvl(DSrcRec.Procure_Flag , '*')
268 AND nvl(standard_operation_id , -1) = nvl(DSrcRec.Standard_Operation_ID , -1)
269 AND nvl(department_id , -1) = nvl(DSrcRec.Department_ID , -1)
270 AND nvl(assembly_item_id , -1) = nvl(DSrcRec.Assembly_Item_ID , -1)
271 AND nvl(wip_entity_pattern , '*') = nvl(DSrcRec.WIP_Entity_Pattern , '*')
272 AND nvl(wip_matl_txn_type , '*') = nvl(DSrcRec.WIP_Matl_Txn_Type , '*');
273
274 X_Count1 := X_Count1 + sql%rowcount;
275
276 END IF;
277
278 --
279 -- Create new rule only if an identical rule is not found.
280 -- This is the default behavior for the "MERGE" copy option.
281 -- The UPDATE statement above already handles the copy of the
282 -- rule if existing rule is found.
283 --
284 INSERT INTO pjm_default_tasks
285 ( assignment_type
286 , project_id
287 , task_id
288 , organization_id
289 , creation_date
290 , created_by
291 , last_update_date
292 , last_updated_by
293 , last_update_login
294 , inventory_item_id
295 , category_id
296 , po_header_id
297 , subinventory_code
298 , procure_flag
299 , standard_operation_id
300 , assembly_item_id
301 , department_id
302 , wip_entity_pattern
303 , wip_matl_txn_type
304 , to_organization_id
305 , comments
306 , task_attribute_category
307 , task_attribute1
308 , task_attribute2
309 , task_attribute3
310 , task_attribute4
311 , task_attribute5
312 , task_attribute6
313 , task_attribute7
314 , task_attribute8
315 , task_attribute9
316 , task_attribute10
317 , task_attribute11
318 , task_attribute12
319 , task_attribute13
320 , task_attribute14
321 , task_attribute15 )
322 SELECT DSrcRec.Assignment_Type
323 , P_To_Project_ID
324 , TaskID
325 , DSrcRec.Organization_ID
326 , SYSDATE
327 , UserID
328 , SYSDATE
329 , UserID
330 , LoginID
331 , DSrcRec.Inventory_Item_ID
332 , DSrcRec.Category_ID
333 , DSrcRec.PO_Header_ID
334 , DSrcRec.Subinventory_Code
335 , DSrcRec.Procure_Flag
336 , DSrcRec.Standard_Operation_ID
337 , DSrcRec.Assembly_Item_ID
338 , DSrcRec.Department_ID
339 , DSrcRec.WIP_Entity_Pattern
340 , DSrcRec.WIP_Matl_Txn_Type
341 , DSrcRec.To_Organization_ID
342 , DSrcRec.Comments
343 , DSrcRec.Task_Attribute_Category
344 , DSrcRec.Task_Attribute1
345 , DSrcRec.Task_Attribute2
346 , DSrcRec.Task_Attribute3
347 , DSrcRec.Task_Attribute4
348 , DSrcRec.Task_Attribute5
349 , DSrcRec.Task_Attribute6
350 , DSrcRec.Task_Attribute7
351 , DSrcRec.Task_Attribute8
352 , DSrcRec.Task_Attribute9
353 , DSrcRec.Task_Attribute10
354 , DSrcRec.Task_Attribute11
355 , DSrcRec.Task_Attribute12
356 , DSrcRec.Task_Attribute13
357 , DSrcRec.Task_Attribute14
358 , DSrcRec.Task_Attribute15
359 FROM dual
360 WHERE NOT EXISTS (
361 SELECT NULL
362 FROM pjm_default_tasks
363 WHERE project_id = P_To_Project_ID
364 AND organization_id = DSrcRec.Organization_ID
365 AND assignment_type = DSrcRec.Assignment_Type
366 AND nvl(inventory_item_id , -1) = nvl(DSrcRec.Inventory_Item_ID , -1)
367 AND nvl(category_id , -1) = nvl(DSrcRec.Category_ID , -1)
368 AND nvl(subinventory_code , '***') = nvl(DSrcRec.Subinventory_Code , '***')
369 AND nvl(po_header_id , -1) = nvl(DSrcRec.PO_Header_ID , -1)
370 AND nvl(procure_flag , '*') = nvl(DSrcRec.Procure_Flag , '*')
371 AND nvl(standard_operation_id , -1) = nvl(DSrcRec.Standard_Operation_ID , -1)
372 AND nvl(department_id , -1) = nvl(DSrcRec.Department_ID , -1)
373 AND nvl(assembly_item_id , -1) = nvl(DSrcRec.Assembly_Item_ID , -1)
374 AND nvl(wip_entity_pattern , '*') = nvl(DSrcRec.WIP_Entity_Pattern , '*')
375 AND nvl(wip_matl_txn_type , '*') = nvl(DSrcRec.WIP_Matl_Txn_Type , '*')
376 );
377
378 X_Count1 := X_Count1 + sql%rowcount;
379
380 END IF;
381
382 X_Count2 := X_Count2 + 1;
383
384 END LOOP;
385
386 --
387 -- Next copy the rest of the assignment rules
388 --
389 FOR SrcRec IN Src LOOP
390 --
391 -- Try to find a match in the To Project based on Task Number
395 --
392 --
393 TaskID := PJM_PROJECT.Val_Task_NumToID( ProjNum , SrcRec.Task_Number );
394
396 -- If no match found, and user elects to use the default task,
397 -- then get the default task using the private function.
398 --
399 -- If user elects to skip rule, TaskID is left NULL. The copy
400 -- logic will ignore the rule if TaskID is NULL.
401 --
402 IF ( TaskID IS NULL AND P_Use_Default_Task = 'Y' ) THEN
403 TaskID := Default_Task( P_To_Project_ID , SrcRec.Organization_ID , SrcRec.Assignment_Type );
404 END IF;
405
406 IF ( TaskID IS NOT NULL ) THEN
407 --
408 -- If copy option of "REPLACE" is specified, then update
409 -- existing rule if found
410 --
411 IF ( P_Copy_Option = 'REPLACE' ) THEN
412
413 UPDATE pjm_default_tasks
414 SET task_id = TaskID
415 , last_update_date = SYSDATE
416 , last_updated_by = UserID
417 , last_update_login = LoginID
418 WHERE project_id = P_To_Project_ID
419 AND organization_id = SrcRec.Organization_ID
420 AND assignment_type = SrcRec.Assignment_Type
421 AND nvl(inventory_item_id , -1) = nvl(SrcRec.Inventory_Item_ID , -1)
422 AND nvl(category_id , -1) = nvl(SrcRec.Category_ID , -1)
423 AND nvl(subinventory_code , '***') = nvl(SrcRec.Subinventory_Code , '***')
424 AND nvl(po_header_id , -1) = nvl(SrcRec.PO_Header_ID , -1)
425 AND nvl(procure_flag , '*') = nvl(SrcRec.Procure_Flag , '*')
426 AND nvl(standard_operation_id , -1) = nvl(SrcRec.Standard_Operation_ID , -1)
427 AND nvl(department_id , -1) = nvl(SrcRec.Department_ID , -1)
428 AND nvl(assembly_item_id , -1) = nvl(SrcRec.Assembly_Item_ID , -1)
429 AND nvl(wip_entity_pattern , '*') = nvl(SrcRec.WIP_Entity_Pattern , '*')
430 AND nvl(wip_matl_txn_type , '*') = nvl(SrcRec.WIP_Matl_Txn_Type , '*');
431
432 X_Count1 := X_Count1 + sql%rowcount;
433
434 END IF;
435
436 --
437 -- Create new rule only if an identical rule is not found.
438 -- This is the default behavior for the "MERGE" copy option.
439 -- The UPDATE statement above already handles the copy of the
440 -- rule if existing rule is found.
441 --
442 INSERT INTO pjm_default_tasks
443 ( assignment_type
444 , project_id
445 , task_id
446 , organization_id
447 , creation_date
448 , created_by
449 , last_update_date
450 , last_updated_by
451 , last_update_login
452 , inventory_item_id
453 , category_id
454 , po_header_id
455 , subinventory_code
456 , procure_flag
457 , standard_operation_id
458 , assembly_item_id
459 , department_id
460 , wip_entity_pattern
461 , wip_matl_txn_type
462 , to_organization_id
463 , comments
464 , task_attribute_category
465 , task_attribute1
466 , task_attribute2
467 , task_attribute3
468 , task_attribute4
469 , task_attribute5
470 , task_attribute6
471 , task_attribute7
472 , task_attribute8
473 , task_attribute9
474 , task_attribute10
475 , task_attribute11
476 , task_attribute12
477 , task_attribute13
478 , task_attribute14
479 , task_attribute15 )
480 SELECT SrcRec.Assignment_Type
481 , P_To_Project_ID
482 , TaskID
483 , SrcRec.Organization_ID
484 , SYSDATE
485 , UserID
486 , SYSDATE
487 , UserID
488 , LoginID
489 , SrcRec.Inventory_Item_ID
490 , SrcRec.Category_ID
491 , SrcRec.PO_Header_ID
492 , SrcRec.Subinventory_Code
493 , SrcRec.Procure_Flag
494 , SrcRec.Standard_Operation_ID
495 , SrcRec.Assembly_Item_ID
496 , SrcRec.Department_ID
497 , SrcRec.WIP_Entity_Pattern
498 , SrcRec.WIP_Matl_Txn_Type
499 , SrcRec.To_Organization_ID
500 , SrcRec.Comments
501 , SrcRec.Task_Attribute_Category
502 , SrcRec.Task_Attribute1
503 , SrcRec.Task_Attribute2
504 , SrcRec.Task_Attribute3
505 , SrcRec.Task_Attribute4
506 , SrcRec.Task_Attribute5
507 , SrcRec.Task_Attribute6
508 , SrcRec.Task_Attribute7
509 , SrcRec.Task_Attribute8
510 , SrcRec.Task_Attribute9
511 , SrcRec.Task_Attribute10
512 , SrcRec.Task_Attribute11
513 , SrcRec.Task_Attribute12
514 , SrcRec.Task_Attribute13
515 , SrcRec.Task_Attribute14
516 , SrcRec.Task_Attribute15
517 FROM dual
518 WHERE NOT EXISTS (
519 SELECT NULL
520 FROM pjm_default_tasks
521 WHERE project_id = P_To_Project_ID
522 AND organization_id = SrcRec.Organization_ID
523 AND assignment_type = SrcRec.Assignment_Type
524 AND nvl(inventory_item_id , -1) = nvl(SrcRec.Inventory_Item_ID , -1)
525 AND nvl(category_id , -1) = nvl(SrcRec.Category_ID , -1)
526 AND nvl(subinventory_code , '***') = nvl(SrcRec.Subinventory_Code , '***')
527 AND nvl(po_header_id , -1) = nvl(SrcRec.PO_Header_ID , -1)
528 AND nvl(procure_flag , '*') = nvl(SrcRec.Procure_Flag , '*')
529 AND nvl(standard_operation_id , -1) = nvl(SrcRec.Standard_Operation_ID , -1)
530 AND nvl(department_id , -1) = nvl(SrcRec.Department_ID , -1)
534 );
531 AND nvl(assembly_item_id , -1) = nvl(SrcRec.Assembly_Item_ID , -1)
532 AND nvl(wip_entity_pattern , '*') = nvl(SrcRec.WIP_Entity_Pattern , '*')
533 AND nvl(wip_matl_txn_type , '*') = nvl(SrcRec.WIP_Matl_Txn_Type , '*')
535
536 X_Count1 := X_Count1 + sql%rowcount;
537
538 END IF;
539
540 X_Count2 := X_Count2 + 1;
541
542 END LOOP;
543
544 EXCEPTION
545 WHEN OTHERS THEN
546 ROLLBACK TO copy_rules;
547 X_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
548 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
549 FND_MSG_PUB.add_exc_msg
550 ( p_pkg_name => G_PKG_NAME
551 , p_procedure_name => 'COPY_RULES');
552 END IF;
553 FND_MSG_PUB.Count_And_Get( p_count => X_Msg_Count
554 , p_data => X_Msg_Data );
555
556 END Copy_Rules;
557
558
559 END PJM_TASK_AUTOASSIGN_COPY;