1 PACKAGE AMW_SCOPE_PVT AS
2 /* $Header: amwvscps.pls 120.1 2008/02/08 14:26:41 adhulipa ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMW_SCOPE_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15 TYPE sub_rec_type IS RECORD
16 (
17 subsidiary_code VARCHAR2(150) := null
18 );
19
20 TYPE sub_tbl_type IS TABLE OF sub_rec_type INDEX BY BINARY_INTEGER;
21
22
23 TYPE sub_new_rec_type IS RECORD
24 (
25 subsidiary_id NUMBER
26 );
27
28 TYPE sub_new_tbl_type IS TABLE OF sub_new_rec_type INDEX BY BINARY_INTEGER;
29
30 TYPE LOB_rec_type IS RECORD
31 (
32 lob_code VARCHAR2(150) := null
33 );
34
35 TYPE lob_tbl_type IS TABLE OF lob_rec_type INDEX BY BINARY_INTEGER;
36
37
38 TYPE lob_new_rec_type IS RECORD
39 (
40 lob_id NUMBER
41 );
42
43 TYPE lob_new_tbl_type IS TABLE OF lob_new_rec_type INDEX BY BINARY_INTEGER;
44
45
46 TYPE org_rec_type IS RECORD
47 (
48 org_id NUMBER
49 );
50
51 TYPE org_tbl_type IS TABLE OF org_rec_type INDEX BY BINARY_INTEGER;
52
53
54 TYPE process_rec_type IS RECORD
55 (
56 process_id NUMBER
57 );
58
59 TYPE process_tbl_type IS TABLE OF process_rec_type INDEX BY BINARY_INTEGER;
60
61 TYPE proc_hier_rec_type IS RECORD
62 (
63 top_process_id NUMBER,
64 parent_process_id NUMBER,
65 process_id NUMBER,
66 level_id NUMBER
67 );
68
69 TYPE proc_hier_tbl_type IS TABLE OF proc_hier_rec_type INDEX BY BINARY_INTEGER;
70
71 g_org_tbl org_tbl_type;
72 g_process_tbl process_tbl_type;
73
74 PROCEDURE add_scope
75 (
76 p_api_version_number IN NUMBER := 1.0,
77 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
78 p_commit IN VARCHAR2 := FND_API.g_false,
79 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
80 p_entity_id IN NUMBER,
81 p_entity_type IN VARCHAR2,
82 p_sub_vs IN VARCHAR2,
83 p_LOB_vs IN VARCHAR2,
84 p_subsidiary_tbl IN SUB_TBL_TYPE,
85 p_lob_tbl IN LOB_TBL_TYPE,
86 p_org_tbl IN ORG_TBL_TYPE,
87 p_process_tbl IN PROCESS_TBL_TYPE,
88 x_return_status OUT nocopy VARCHAR2,
89 x_msg_count OUT nocopy NUMBER,
90 x_msg_data OUT nocopy VARCHAR2
91 );
92
93 PROCEDURE populate_custom_hierarchy
94 (
95 p_org_tbl IN ORG_TBL_TYPE,
96 p_entity_id IN NUMBER,
97 p_entity_type IN VARCHAR2
98 );
99
100 PROCEDURE generate_organization_list
101 (
102 p_entity_id IN NUMBER,
103 p_entity_type IN VARCHAR2,
104 p_org_tbl IN ORG_TBL_TYPE,
105 p_org_new_tbl OUT nocopy ORG_TBL_TYPE
106 );
107
108 PROCEDURE generate_subsidiary_list
109 (
110 p_entity_id IN NUMBER,
111 p_entity_type IN VARCHAR2,
112 p_org_new_tbl IN ORG_TBL_TYPE,
113 p_subsidiary_tbl IN sub_tbl_type,
114 p_sub_vs IN VARCHAR2,
115 p_sub_new_tbl OUT nocopy sub_new_tbl_type
116 );
117
118 PROCEDURE generate_lob_list
119 (
120 p_entity_id IN NUMBER,
121 p_entity_type IN VARCHAR2,
122 p_org_new_tbl IN ORG_TBL_TYPE,
123 p_subsidiary_tbl IN sub_tbl_type,
124 p_sub_vs IN VARCHAR2,
125 p_lob_tbl IN lob_tbl_type,
126 p_lob_vs IN VARCHAR2,
127 p_lob_new_tbl OUT nocopy lob_new_tbl_type
128 );
129
130 PROCEDURE populate_process_hierarchy
131 (
132 p_api_version_number IN NUMBER := 1.0,
133 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
134 p_commit IN VARCHAR2 := FND_API.g_false,
135 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
136 p_entity_type IN VARCHAR2,
137 p_entity_id IN NUMBER,
138 p_org_tbl IN org_tbl_type,
139 p_process_tbl IN process_tbl_type,
140 x_return_status OUT nocopy VARCHAR2,
141 x_msg_count OUT nocopy NUMBER,
142 x_msg_data OUT nocopy VARCHAR2
143 );
144
145 PROCEDURE insert_process
146 (
147 p_level_id IN NUMBER,
148 p_parent_process_id IN NUMBER,
149 p_top_process_id IN NUMBER,
150 p_process_org_rev_id IN NUMBER,
151 p_subsidiary_vs IN VARCHAR2,
152 p_subsidiary_code IN VARCHAR2,
153 p_lob_vs IN VARCHAR2,
154 p_lob_code IN VARCHAR2,
155 p_organization_id IN NUMBER,
156 p_entity_type IN VARCHAR2,
157 p_entity_id IN NUMBER
158 );
159
160 PROCEDURE build_project_audit_task
161 (
162 p_api_version_number IN NUMBER,
163 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
164 p_commit IN VARCHAR2 := FND_API.g_false,
165 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
166 p_audit_project_id IN NUMBER,
167 l_ineff_controls IN BOOLEAN := false,
168 p_source_project_id IN NUMBER := 0,
169 x_return_status OUT nocopy VARCHAR2,
170 x_msg_count OUT nocopy NUMBER,
171 x_msg_data OUT nocopy VARCHAR2
172 );
173
174 PROCEDURE populate_denormalized_tables
175 (
176 p_entity_type IN VARCHAR2,
177 p_entity_id IN NUMBER,
178 p_org_tbl IN org_tbl_type,
179 p_process_tbl IN process_tbl_type,
180 p_mode IN VARCHAR2
181 );
182
183 PROCEDURE populate_association_tables
184 (
185 p_api_version_number IN NUMBER := 1.0,
186 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
187 p_commit IN VARCHAR2 := FND_API.g_false,
188 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
189 p_entity_type IN VARCHAR2,
190 p_entity_id IN NUMBER,
191 x_return_status OUT nocopy VARCHAR2,
192 x_msg_count OUT nocopy NUMBER,
193 x_msg_data OUT nocopy VARCHAR2
194 );
195
196 PROCEDURE populate_scope
197 (
198 p_api_version_number IN NUMBER := 1.0,
199 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
200 p_commit IN VARCHAR2 := FND_API.g_false,
201 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
202 p_entity_id IN NUMBER,
203 x_return_status OUT nocopy VARCHAR2,
204 x_msg_count OUT nocopy NUMBER,
205 x_msg_data OUT nocopy VARCHAR2
206 );
207
208 PROCEDURE manage_processes
209 (
210 p_api_version_number IN NUMBER,
211 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
212 p_commit IN VARCHAR2 := FND_API.g_false,
213 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
214 p_entity_type IN VARCHAR2,
215 p_entity_id IN NUMBER,
216 p_organization_id IN NUMBER,
217 p_proc_hier_tbl IN PROC_HIER_TBL_TYPE,
218 x_return_status OUT nocopy VARCHAR2,
219 x_msg_count OUT nocopy NUMBER,
220 x_msg_data OUT nocopy VARCHAR2
221 );
222
223 PROCEDURE remove_from_scope
224 (
225 p_api_version_number IN NUMBER := 1.0,
226 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
227 p_commit IN VARCHAR2 := FND_API.g_false,
228 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
229 p_entity_type IN VARCHAR2,
230 p_entity_id IN NUMBER,
231 p_object_id IN NUMBER,
232 p_object_type IN VARCHAR2,
233 p_subsidiary_vs IN VARCHAR2,
234 p_subsidiary_code IN VARCHAR2,
235 p_LOB_vs IN VARCHAR2,
236 p_LOB_code IN VARCHAR2,
237 p_organization_id IN NUMBER,
238 x_return_status OUT nocopy VARCHAR2,
239 x_msg_count OUT nocopy NUMBER,
240 x_msg_data OUT nocopy VARCHAR2
241 );
242
243
244 PROCEDURE remove_orgs_from_scope
245 (
246 p_api_version_number IN NUMBER := 1.0,
247 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
248 p_commit IN VARCHAR2 := FND_API.g_false,
249 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
250 p_entity_type IN VARCHAR2,
251 p_entity_id IN NUMBER,
252 p_object_id IN NUMBER,
253 x_return_status OUT nocopy VARCHAR2,
254 x_msg_count OUT nocopy NUMBER,
255 x_msg_data OUT nocopy VARCHAR2
256 );
257
258
259 FUNCTION find_child_orgs
260 (
261 p_entity_type IN VARCHAR2,
262 p_entity_id IN NUMBER,
263 p_object_id IN NUMBER,
264 p_object_tbl IN org_tbl_type
265 )
266 RETURN org_tbl_type;
267
268 FUNCTION find_child_objects
269 (
270 p_entity_type IN VARCHAR2,
271 p_entity_id IN NUMBER,
272 p_object_id IN NUMBER,
273 p_object_type IN VARCHAR2,
274 p_object_tbl IN org_tbl_type
275 )
276 RETURN org_tbl_type;
277
278 function get_assoc_task_id(p_project_id in number, p_task_id in number)
279 return number;
280 pragma restrict_references(get_assoc_task_id, WNDS);
281
282 PROCEDURE populate_proj_denorm_tables
283 (
284 p_audit_project_id IN NUMBER
285 );
286
287
288
289 PROCEDURE get_accessible_root_orgs (
290 p_entity_id IN NUMBER,
291 p_entity_type IN VARCHAR2,
292 x_org_ids OUT NOCOPY VARCHAR2);
293
294
295 FUNCTION Has_Org_Access_in_hier (
296 p_is_global_owner IN VARCHAR2,
297 p_org_id IN NUMBER)
298 RETURN VARCHAR2;
299
300 PROCEDURE get_accessible_root_procs (
301 p_entity_id IN NUMBER,
302 p_entity_type IN VARCHAR2,
303 p_org_id IN NUMBER,
304 x_proc_ids OUT NOCOPY VARCHAR2);
305
306 END amw_scope_pvt;
307
308
309