1 PACKAGE BODY PA_AUTOALLOC_UTLS AS
2 /* $Header: PAXAAUTB.pls 115.2 99/07/16 15:15:53 porting ship $ */
3
4 ------------------------------------------------------------------------------
5 FUNCTION USED_IN_AUTOALLOCWF ( p_allocation_run_id Number )
6 RETURN VARCHAR2
7 IS
8
9 v_request_id Number ;
10
11 Cursor SetCur IS
12 SELECT BATCH.Request_Id
13 FROM PA_Alloc_Runs_All PA,
14 GL_Auto_Alloc_Batch_History Batch
15 WHERE PA.Run_ID = p_allocation_run_id
16 AND Batch.Request_ID > 0
17 AND Batch.Batch_ID = PA.Rule_ID
18 AND Batch.Batch_Type_Code = 'P'
19 AND Batch.PA_Allocation_Run_ID = p_allocation_run_id;
20
21 BEGIN
22
23 Open SetCur;
24 Fetch SetCur into v_request_id;
25
26 If SetCur%NOTFOUND then
27 Close SetCur;
28 /* Run ID does not belong to AutoAloc Set */
29 RETURN null;
30 End If;
31
32 Close SetCur;
33
34 RETURN to_char(v_request_id);
35
36 END USED_IN_AUTOALLOCWF;
37
38 ------------------------------------------------------------------------------
39 FUNCTION IN_ACTIVE_AUTOALLOCWF ( p_allocation_run_id Number )
40 RETURN VARCHAR2
41 IS
42
43
44 v_end_date Date ;
45 v_item_key Varchar2(15);
46
47 Cursor SetCur IS
48 SELECT WF.End_Date
49 FROM WF_Items_V WF
50 WHERE WF.Item_Type = 'GLALLOC'
51 AND WF.Item_Key = v_item_key
52 AND WF.root_activity = 'GL_SD_ALLOCATION_PROCESS';
53
54 BEGIN
55
56
57 /* Find out if the run is used in an Autoallocation set that uses Work-Flow */
58
59 v_item_key := USED_IN_AUTOALLOCWF (p_allocation_run_id);
60
61 If v_item_key is null Then
62 Return 'N';
63
64 Else
65
66 /*If the run belongs to an autoallocation set that has started a workflow
67 then find the end date recorded in wf_items_v for the item_type,item_key
68 and root_activity.If it's complete then end_date will be not null*/
69
70 Open SetCur;
71 Fetch SetCur into v_end_date;
72
73 If SetCur%NOTFOUND then
74 Close SetCur;
75 /* No process recorded in the WF view */
76 RETURN 'N';
77 End If;
78
79 End If;
80
81 Close SetCur;
82
83 If v_end_date is null then
84 /** Run ID belongs to active AutoAlloc Set **/
85 RETURN 'Y';
86 Else
87 /** Run ID belongs to a completed AUtoAlloc Set **/
88 RETURN 'N';
89 End If;
90
91
92 END IN_ACTIVE_AUTOALLOCWF;
93
94 ------------------------------------------------------------------------------
95
96 END;