1 PACKAGE AMW_RL_HIERARCHY_PKG AS
2 /*$Header: amwrlhrs.pls 120.1 2005/11/29 11:24:16 appldev noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMW_RL_HIERARCHY_PKG';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwrlhrb.pls';
6 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
7 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
8
9
10
11 amw_process_locked_exception exception;
12 amw_circularity_exception exception;
13 amw_processing_exception exception;
14 wf_cycle_present_exception exception;
15 amw_insfcnt_prvlg_exception exception;
16 /* raised when a deleted (end_dated) process
17 * is attempted to be brought back into
18 * hierarchy
19 */
20 amw_process_deleted_exception exception;
21
22 /* does the link between parent and child exist in the latest
23 * hierarchy?
24 */
25 cursor c_link_exist (l_parent_process_id number,
26 l_child_process_id number) is
27 select 1 from amw_latest_hierarchies where
28 (organization_id is null or organization_id = -1) and
29 parent_id = l_parent_process_id and
30 child_id = l_child_process_id;
31
32
33
34
35 cursor c_all_latest_links_rl is
36 select parent_id, child_id
37 from amw_latest_hierarchies
38 where (organization_id is null or organization_id = -1);
39
40 cursor c_all_latest_links_org(l_org_id in number) is
41 select parent_id, child_id
42 from amw_latest_hierarchies
43 where (organization_id = l_org_id);
44
45 cursor c_all_latest_relations is
46 select process_id, parent_child_id, up_down_ind
47 from amw_proc_hierarchy_denorm
48 where hierarchy_type = 'L';
49
50 type link_rec is record (parent_id amw_latest_hierarchies.parent_id%type,
51 child_id amw_latest_hierarchies.child_id%type);
52
53
54 type links_tbl is table of link_rec;
55 type process_tbl is table of amw_proc_hierarchy_denorm.process_id%type;
56 type parent_child_tbl is table of amw_proc_hierarchy_denorm.parent_child_id%type;
57 type up_down_ind_tbl is table of amw_proc_hierarchy_denorm.up_down_ind%type;
58
59
60
61
62 type visited_node_index_tbl is table of pls_integer index by varchar2(40);
63 visited_tbl visited_node_index_tbl;
64
65
66 /* this table stores information whether a particular (ancestor, descendant)
67 * has already been processed. To do this it encodes the ancestor_id and
68 * descendant_id as <ancestor_id>:<descendant_id> for ex. 101:102 and uses
69 * this string as an index. The associated integer is just 1
70 * Therefore to look up whether a link was already stored (in some plsql
71 * table we just need to do x_index_tbl.exists(encode(an_id, d_id))
72 */
73 type index_tbl is table of pls_integer index by varchar2(80);
74
75 /* tn can be just read : table of numbers
76 * this table stores process ids (what those are is given by the index of
77 * the next type below)
78 */
79 type tn is table of number;
80
81 /* Read as : links table
82 * Stores links
83 */
84 type lt is table of tn index by varchar2(50);
85
86 p_links_tbl links_tbl;
87 x_process_tbl process_tbl;
88 x_parent_child_tbl parent_child_tbl;
89 x_up_down_ind_tbl up_down_ind_tbl;
90 x_index pls_integer;
91 x_index_tbl index_tbl;
92 x_t1 lt;
93 x_t2 lt;
94 g_sysdate DATE := sysdate;
95
96
97 procedure update_org_count(p_process_id in number);
98 procedure update_latest_risk_counts(p_process_id in number);
99 procedure update_latest_control_counts(p_process_id in number);
100 procedure update_approved_risk_counts(p_process_id in number);
101 procedure update_approved_control_counts(p_process_id in number);
102 procedure update_all_org_counts;
103 procedure add_existing_process_as_child(
104
105 p_parent_process_id in number,
106 p_child_process_id in number,
107 l_sysdate in Date default sysdate,
108 x_return_status out nocopy varchar2,
109 x_msg_count out nocopy number,
110 x_msg_data out nocopy varchar2);
111 procedure delete_child(
112
113 p_parent_process_id in number,
114 p_child_process_id in number,
115 l_sysdate in Date default sysdate,
116 x_return_status out nocopy varchar2,
117 x_msg_count out nocopy number,
118 x_msg_data out nocopy varchar2);
119 procedure import_wf_process(
120 p_parent_process_id in number,
121 p_comb_string in varchar2,
122 p_overwrite_ex in varchar2,
123 l_sysdate in Date default sysdate,
124 p_update_denorm_count IN VARCHAR2 := 'Y',
125 x_return_status out nocopy varchar2,
126 x_msg_count out nocopy number,
127 x_msg_data out nocopy varchar2);
128 function is_ancestor_in_hierarchy(p_process1_id in number,
129 p_process2_id in number)
130 return boolean;
131 function is_locked_process(p_process_id in number) return boolean;
132 procedure update_denorm_add_child(p_parent_id number,
133 p_child_id number,
134 l_sysdate in Date default sysdate);
135 procedure update_denorm(p_org_id in number,
136 l_sysdate in Date default sysdate);
137 procedure update_approved_denorm(p_org_id in number,
138 l_sysdate in Date default sysdate);
139 procedure update_rc_latest_counts(p_process_id in number,
140 x_return_status out nocopy varchar2,
141 x_msg_count out nocopy number,
142 x_msg_data out nocopy varchar2);
143 procedure update_appr_control_counts;
144 procedure update_appr_risk_counts;
145
146
147 /* p_mode is an indicator on what type of count to update
148 * pass 'R' for only risk count updating
149 * pass 'C' for only control count updating
150 * pass 'RC' for updating both
151 */
152 procedure update_all_latest_rc_counts(p_mode in varchar2);
153
154 procedure revise_process_if_necessary
155 (p_process_id in number,
156 l_sysdate in Date default sysdate);
157 function is_deleted_process(p_process_id in number)
158 return boolean;
159 procedure update_appr_ch_ord_num_if_reqd
160 (p_org_id in number,
161 p_parent_id in number,
162 p_child_id in number,
163 p_instance_id in number);
164 function get_process_id_from_wf_params(p_name in varchar2,
165 p_item_type in varchar2)
166 return number;
167 function does_wf_proc_exist_in_icm(p_name in varchar2,
168 p_item_type in varchar2)
169 return boolean;
170 function get_process_code return varchar2;
171
172 procedure add_WEBADI_HIERARCHY_LINKS(
173 p_child_order_number in number,
174 p_parent_process_id in number,
175 p_child_process_id in number,
176 l_sysdate in Date default sysdate,
177 x_return_status out nocopy varchar2,
178 x_msg_count out nocopy number,
179 x_msg_data out nocopy varchar2);
180
181 /* This procedure is called from ProcessRevisionAMImpl.java
182 * It is called when it is detected that for an approved process
183 * during updating it : the list of attachments is NOT changed
184 * and neither is any other attribute that revises the process
185 * In this case the process is NOT to be revised
186 * HOWEVER : a particular attachment content may be modified.
187 * The set of attachments that one works on in the middle tier
188 * are those attached to a temporary processRevId (to which all
189 * the attachments from the original processRevId were copied in the
190 * very beginning) ::: At the end, this processRevId is unused since
191 * the process is not being revised. But to deal with the fact that
192 * attachment content may have changed, we do a delete/copy/delete
193 * old attachments (deleted); new ones copied to old, new ones deleted.
194 */
195
196 procedure update_attachments(p_old_prev_id in varchar2,
197 p_new_prev_id in varchar2,
198 x_return_status out nocopy varchar2,
199 x_msg_count out nocopy number,
200 x_msg_data out nocopy varchar2);
201
202
203 procedure create_new_process_as_child(
204 p_parent_process_id in number,
205 p_item_type in varchar2,
206 p_display_name in varchar2,
207 p_description in varchar2,
208 p_control_type in varchar2,
209 x_return_status out nocopy varchar2,
210 x_msg_count out nocopy number,
211 x_msg_data out nocopy varchar2);
212
213 procedure conv_tutor_add_child(
214 p_parent_process_id in number,
215 p_display_name in varchar2,
216 p_control_type in varchar2,
217 x_return_status out nocopy varchar2,
218 x_msg_count out nocopy number,
219 x_msg_data out nocopy varchar2);
220
221 procedure conv_tutor_grants(l_process_id in number);
222
223 procedure Check_Root_Access(p_predicate in varchar2,
224 p_hasAccess out NOCOPY varchar2);
225
226 PROCEDURE reset_count(
227 errbuf out nocopy varchar2,
228 retcode out nocopy varchar2
229 );
230
231 function is_proc_in_ltst_hier(p_process_id in number) return number;
232
233 function areChildListSame(p_process_id in number) return varchar;
234
235 function does_apprvd_ver_exst(p_process_id in number) return varchar;
236
237 procedure isProcessUndoAble ( p_process_id in number,
238 ret_value out nocopy varchar2,
239 x_return_status out nocopy varchar2,
240 x_msg_count out nocopy number,
241 x_msg_data out nocopy varchar2);
242
243 procedure delete_draft (p_process_id in number,
244 x_return_status out nocopy varchar2,
245 x_msg_count out nocopy number,
246 x_msg_data out nocopy varchar2);
247 -- ko Procedure to Create the process owner grant on the the given process id for the current user..
248 procedure create_process_owner_grant(p_process_id in varchar2,
249 x_return_status out nocopy varchar2,
250 x_msg_count out nocopy number,
251 x_msg_data out nocopy varchar2);
252 /********************************************************
253 KOSRINIV..
254 PROCEDURE to delete the child process from the hierarchy
255 to use this procedure from Process Update Page..
256 ****************************************************************/
257 procedure delete_activities(p_parent_process_id in number,
258 p_child_id_string in varchar2,
259 x_return_status out nocopy varchar2,
260 x_msg_count out nocopy number,
261 x_msg_data out nocopy varchar2);
262 /********************************************************
263 KOSRINIV..
264 PROCEDURE to add the child process under a process hierarchy
265 to use this procedure from Process Update Page..
266 ****************************************************************/
267 procedure add_activities( p_parent_process_id in number,
268 p_child_id_string in varchar2,
269 p_sysdate in Date default sysdate,
270 x_return_status out nocopy varchar2,
271 x_msg_count out nocopy number,
272 x_msg_data out nocopy varchar2);
273 /***************************************************************
274 KOSRINIV..
275 pl/sql wrapper PROCEDURE to revise a process
276 ****************************************************************/
277
278 procedure revise_process(p_process_id in number,
279 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
280 x_return_status out nocopy varchar2,
281 x_msg_count out nocopy number,
282 x_msg_data out nocopy varchar2);
283
284 PROCEDURE update_latest_denorm_counts ( p_process_id IN NUMBER,
285 p_commit IN VARCHAR2 := FND_API.G_FALSE,
286 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
287 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY VARCHAR2,
290 x_msg_data OUT NOCOPY VARCHAR2);
291
292 END AMW_RL_HIERARCHY_PKG;
293
[Home] [Help]