1 package body hxc_approval_wf_util as
2 /* $Header: hxcapprwfut.pkb 120.0 2005/05/29 06:12:16 appldev noship $ */
3
4 c_previous_root constant varchar2(12) := 'PREVIOUS_APP';
5 c_previous_approver_name constant varchar2(17) := 'PREVIOUS_APPROVER';
6 c_app_id_name constant varchar2(17) := 'PREVIOUS_APP_ID';
7
8 Procedure copy_previous_approvers
9 (p_item_type in wf_item_types.name%type,
10 p_current_key in wf_item_attribute_values.item_key%type,
11 p_copyto_key in wf_item_attribute_values.item_key%type) is
12
13
14 cursor c_copy_approvers
15 (p_item_type in wf_item_types.name%type,
16 p_item_key in wf_item_attribute_values.item_key%type) is
17 select name, number_value
18 from wf_item_attribute_values
19 where item_type = p_item_type
20 and item_key = p_item_key
21 and name like c_previous_root||'%';
22
23 Begin
24
25 for copy_approvers_rec in c_copy_approvers(p_item_type,p_current_key) loop
26
27 wf_engine.additemattr
28 (p_item_type,
29 p_copyto_key,
30 copy_approvers_rec.name,
31 null,
32 copy_approvers_rec.number_value,
33 null);
34
35 end loop;
36
37 end copy_previous_approvers;
38
39 Function get_previous_approver
40 (p_item_type in wf_item_types.name%type,
41 p_item_key in wf_item_attribute_values.item_key%type,
42 p_app_period_id in hxc_app_period_summary.application_period_id%type)
43 Return number is
44
45 cursor c_previous_approver
46 (p_item_type in wf_item_types.name%type,
47 p_item_key in wf_item_attribute_values.item_key%type,
48 p_app_period_id in hxc_app_period_summary.application_period_id%type) is
49 select preapr.number_value
50 from wf_item_attribute_values appid,
51 wf_item_attribute_values preapr
52 where appid.number_value = p_app_period_id
53 and appid.item_type = p_item_type
54 and appid.item_key = p_item_key
55 and appid.name like c_app_id_name||'%'
56 and preapr.item_type = appid.item_type
57 and preapr.item_key = appid.item_key
58 and preapr.name = replace(appid.name,c_app_id_name,c_previous_approver_name);
59
60 l_previous_approver wf_item_attribute_values.number_value%type;
61
62 Begin
63 open c_previous_approver(p_item_type,p_item_key,p_app_period_id);
64 fetch c_previous_approver into l_previous_approver;
65 if(c_previous_approver%notfound) then
66 l_previous_approver := -1;
67 end if;
68 close c_previous_approver;
69
70 return l_previous_approver;
71
72 End get_previous_approver;
73
74 Function keep_previous_approver
75 (p_item_type in wf_item_types.name%type,
76 p_item_key in wf_item_attribute_values.item_key%type,
77 p_app_period_id in hxc_app_period_summary.application_period_id%type)
78 RETURN number is
79
80 cursor c_previous_approver
81 (p_app_id in hxc_app_period_summary.application_period_id%type) is
82 select approver_id
83 from hxc_app_period_summary
84 where application_period_id = p_app_id;
85
86 cursor c_previous_approvers
87 (p_item_type in wf_item_types.name%type,
88 p_item_key in wf_item_attribute_values.item_key%type) is
89 select count(*)
90 from wf_item_attribute_values
91 where item_type = p_item_type
92 and item_key = p_item_key
93 and name like c_previous_approver_name||'%';
94
95 l_previous_approver hxc_app_period_summary.approver_id%type;
96 l_previous_approvers number;
97 l_item_attribute_name wf_item_attribute_values.name%type;
98 l_item_attr_app_id_name wf_item_attribute_values.name%type;
99
100 Begin
101 --
102 -- 0. Find the last approver of this application period
103 --
104 open c_previous_approver(p_app_period_id);
105 fetch c_previous_approver into l_previous_approver;
106 if(c_previous_approver%notfound) then
107 --
108 -- 0.5 set the previous approver to -1.
109 --
110 l_previous_approver := -1;
111 end if;
112 close c_previous_approver;
113 --
114 -- 1. Set the item attribute value to store it
115 --
116 open c_previous_approvers(p_item_type, p_item_key);
117 fetch c_previous_approvers into l_previous_approvers;
118 if (c_previous_approvers%notfound) then
119 l_previous_approvers := 0;
120 end if;
121 close c_previous_approvers;
122 l_item_attribute_name := c_previous_approver_name||l_previous_approvers;
123 l_item_attr_app_id_name := c_app_id_name||l_previous_approvers;
124 --
125 -- Store the application period id
126 --
127 wf_engine.additemattr
128 (p_item_type,
129 p_item_key,
130 l_item_attr_app_id_name,
131 null,
132 p_app_period_id,
133 null);
134 --
135 -- Store the previous approver
136 --
137 wf_engine.additemattr
138 (p_item_type,
139 p_item_key,
140 l_item_attribute_name,
141 null,
142 l_previous_approver,
143 null);
144
145 return l_previous_approvers;
146
147 End keep_previous_approver;
148
149 End hxc_approval_wf_util;
[Home] [Help]