[Home] [Help]
PACKAGE: APPS.AMW_PROC_ORG_HIERARCHY_PVT
Source
1 PACKAGE AMW_PROC_ORG_HIERARCHY_PVT AS
2 /* $Header: amwvpohs.pls 120.0 2005/05/31 22:31:17 appldev noship $ */
3
4 --===================================================================
5 -- Start of Comments
6 -- -------------------------------------------------------
7 -- Record name
8 -- apo_type
9 -- -------------------------------------------------------
10 -- Parameters:
11 -- CONTROL_COUNT NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
12 -- RISK_COUNT NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
13 -- TOP_PROCESS_ID NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
14 -- PROCESS_ORGANIZATION_ID NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
15 -- LAST_UPDATE_DATE DATE OPTIONAL DEFAULT = FND_API.G_MISS_DATE,
16 -- LAST_UPDATED_BY NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
17 -- CREATION_DATE DATE OPTIONAL DEFAULT = FND_API.G_MISS_DATE,
18 -- CREATED_BY NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
19 -- LAST_UPDATE_LOGIN NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
20 -- PROCESS_ID NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
21 -- STANDARD_PROCESS_FLAG VARCHAR2 OPTIONAL DEFAULT = null,
22 -- RISK_CATEGORY VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
23 -- APPROVAL_STATUS VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
24 -- CERTIFICATION_STATUS VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
25 -- LAST_AUDIT_STATUS VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
26 -- ORGANIZATION_ID NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
27 -- LAST_CERTIFICATION_DATE DATE OPTIONAL DEFAULT = FND_API.G_MISS_DATE,
28 -- LAST_AUDIT_DATE DATE OPTIONAL DEFAULT = FND_API.G_MISS_DATE,
29 -- NEXT_AUDIT_DATE DATE OPTIONAL DEFAULT = FND_API.G_MISS_DATE,
30 -- APPLICATION_OWNER_ID NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
31 -- PROCESS_OWNER_ID NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
32 -- PROCESS_CATEGORY_CODE VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
33 -- SIGNIFICANT_PROCESS_FLAG VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
34 -- FINANCE_OWNER_ID NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
35 -- CREATED_FROM VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
36 -- REQUEST_ID NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
37 -- PROGRAM_APPLICATION_ID NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
38 -- PROGRAM_ID NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
39 -- PROGRAM_UPDATE_DATE DATE OPTIONAL DEFAULT = FND_API.G_MISS_DATE,
40 -- ATTRIBUTE_CATEGORY VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
41 -- ATTRIBUTE1 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
42 -- ATTRIBUTE2 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
43 -- ATTRIBUTE3 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
44 -- ATTRIBUTE4 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
45 -- ATTRIBUTE5 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
46 -- ATTRIBUTE6 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
47 -- ATTRIBUTE7 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
48 -- ATTRIBUTE8 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
49 -- ATTRIBUTE9 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
50 -- ATTRIBUTE10 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
51 -- ATTRIBUTE11 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
52 -- ATTRIBUTE12 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
53 -- ATTRIBUTE13 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
54 -- ATTRIBUTE14 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
55 -- ATTRIBUTE15 VARCHAR2 OPTIONAL DEFAULT = FND_API.G_MISS_CHAR,
56 -- SECURITY_GROUP_ID NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
57 -- OBJECT_VERSION_NUMBER NUMBER OPTIONAL DEFAULT = FND_API.G_MISS_NUM,
58 -- END_DATE DATE OPTIONAL DEFAULT = FND_API.G_MISS_DATE
59 --
60 -- Required
61 --
62 -- Defaults
63 --
64 -- Note: This is automatic generated record definition, it includes all columns
65 -- defined in the table, developer must manually add or delete some of the attributes.
66 --
67 -- End of Comments
68
69 --===================================================================
70
71 TYPE apo_type IS RECORD(
72 CONTROL_COUNT NUMBER := null,
73 RISK_COUNT NUMBER := null,
74 TOP_PROCESS_ID NUMBER := null,
75 PROCESS_ORGANIZATION_ID NUMBER := null,
76 LAST_UPDATE_DATE DATE := null,
77 LAST_UPDATED_BY NUMBER := null,
78 CREATION_DATE DATE := null,
79 CREATED_BY NUMBER := null,
80 LAST_UPDATE_LOGIN NUMBER := null,
81 PROCESS_ID NUMBER := null,
82 STANDARD_PROCESS_FLAG VARCHAR2(1) := null,
83 RISK_CATEGORY VARCHAR2(30) := null,
84 APPROVAL_STATUS VARCHAR2(30) := null,
85 CERTIFICATION_STATUS VARCHAR2(30) := null,
86 LAST_AUDIT_STATUS VARCHAR2(30) := null,
87 ORGANIZATION_ID NUMBER := null,
88 LAST_CERTIFICATION_DATE DATE := null,
89 LAST_AUDIT_DATE DATE := null,
90 NEXT_AUDIT_DATE DATE := null,
91 APPLICATION_OWNER_ID NUMBER := null,
92 PROCESS_OWNER_ID NUMBER := null,
93 PROCESS_CATEGORY_CODE VARCHAR2(30) := null,
94 SIGNIFICANT_PROCESS_FLAG VARCHAR2(1) := null,
95 FINANCE_OWNER_ID NUMBER := null,
96 CREATED_FROM VARCHAR2(30) := null,
97 REQUEST_ID NUMBER := null,
98 PROGRAM_APPLICATION_ID NUMBER := null,
99 PROGRAM_ID NUMBER := null,
100 PROGRAM_UPDATE_DATE DATE := null,
101 ATTRIBUTE_CATEGORY VARCHAR2(30) := null,
102 ATTRIBUTE1 VARCHAR2(150) := null,
103 ATTRIBUTE2 VARCHAR2(150) := null,
104 ATTRIBUTE3 VARCHAR2(150) := null,
105 ATTRIBUTE4 VARCHAR2(150) := null,
106 ATTRIBUTE5 VARCHAR2(150) := null,
107 ATTRIBUTE6 VARCHAR2(150) := null,
108 ATTRIBUTE7 VARCHAR2(150) := null,
109 ATTRIBUTE8 VARCHAR2(150) := null,
110 ATTRIBUTE9 VARCHAR2(150) := null,
111 ATTRIBUTE10 VARCHAR2(150) := null,
112 ATTRIBUTE11 VARCHAR2(150) := null,
113 ATTRIBUTE12 VARCHAR2(150) := null,
114 ATTRIBUTE13 VARCHAR2(150) := null,
115 ATTRIBUTE14 VARCHAR2(150) := null,
116 ATTRIBUTE15 VARCHAR2(150) := null,
117 SECURITY_GROUP_ID NUMBER := null,
118 OBJECT_VERSION_NUMBER NUMBER := null,
119 END_DATE DATE := null
120 );
121
122 g_miss_apo_type apo_type;
123
124 -- ==============================================================================
125 -- Start of Comments
126 -- ==============================================================================
127 -- API Name
128 -- Process_Process_Hierarchy
129 -- Type
130 -- Public
131 -- Pre-Req
132 --
133 -- Parameters
134 --
135 -- IN
136 -- p_process_id IN NUMBER Optional Default = null
137 -- p_organization_id IN NUMBER Optional Default = null
138 -- p_mode IN VARCHAR2 Required Default = 'ASSOCIATE'
139 -- p_apo_type IN apo_type Optional Default = null
140 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
141 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
142 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
143 --
144 -- OUT
145 -- x_return_status OUT VARCHAR2
146 -- x_msg_count OUT NUMBER
147 -- x_msg_data OUT VARCHAR2
148 -- Version : Current version 1.0
149 -- Note:
150 --
151 -- End of Comments
152 -- ==============================================================================
153 --
154
155 procedure process_process_hierarchy(
156 p_process_id in number := null,
157 p_organization_id in number := null,
158 p_mode in varchar2 := 'ASSOCIATE',
159 p_level in number := 0,
160 p_apo_type in apo_type := g_miss_apo_type,
161 p_commit in varchar2 := FND_API.G_FALSE,
162 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
163 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
164 x_return_status out nocopy varchar2,
165 x_msg_count out nocopy number,
166 x_msg_data out nocopy varchar2
167 );
168
169 -- ==============================================================================
170 -- Start of Comments
171 -- ==============================================================================
172 -- API Name
173 -- Associate_Process_Org
174 -- Type
175 -- Private
176 -- Pre-Req
177 --
178 -- Parameters
179 --
180 -- IN
181 -- p_apo_type IN apo_type Optional Default = null
182 -- p_process_id IN NUMBER Optional Default = null
183 -- p_top_process_id IN NUMBER Optional Default = null
184 -- p_organization_id IN NUMBER Optional Default = null
185 -- p_parent_process_id IN NUMBER Optional Default = null
186 -- p_mode IN VARCHAR2 Required Default = 'ASSOCIATE'
187 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
188 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
189 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
190 --
191 -- OUT
192 -- x_return_status OUT VARCHAR2
193 -- x_msg_count OUT NUMBER
194 -- x_msg_data OUT VARCHAR2
195 -- Version : Current version 1.0
196 -- Note:
197 --
198 -- End of Comments
199 -- ==============================================================================
200 --
201
202 procedure associate_process_org(
203 p_apo_type in apo_type := g_miss_apo_type,
204 p_process_id in number := null,
205 p_top_process_id in number := null,
206 p_organization_id in number := null,
207 p_parent_process_id in number := null,
208 p_rcm_assoc in varchar2 := 'N',
209 p_batch_id in number := null,
210 p_rcm_org_intf_id in number := null,
211 p_risk_id in number := null,
212 p_control_id in number := null,
213 p_mode in varchar2 := 'ASSOCIATE',
214 p_commit in varchar2 := FND_API.G_FALSE,
215 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
216 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
217 x_return_status out nocopy varchar2,
218 x_msg_count out nocopy number,
219 x_msg_data out nocopy varchar2
220 );
221
222
223 -- ==============================================================================
224 -- Start of Comments
225 -- ==============================================================================
226 -- API Name
227 -- Process_Amw_Process_Org
228 -- Type
229 -- Private
230 -- Pre-Req
231 --
232 -- Parameters
233 --
234 -- IN
235 -- p_apo_type IN apo_type Optional Default = null
236 -- p_do_insert IN VARCHAR2 Optional Default = 'INSERT'
237 -- p_org_count IN NUMBER Optional Default = 0
238 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
239 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
240 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
241 --
242 --
243 -- OUT
244 -- x_return_status OUT VARCHAR2
245 -- x_msg_count OUT NUMBER
246 -- x_msg_data OUT VARCHAR2
247 -- Version : Current version 1.0
248 -- Note:
249 --
250 -- End of Comments
251 -- ==============================================================================
252 --
253
254 procedure process_amw_process_org(
255 p_apo_type in apo_type := g_miss_apo_type,
256 p_do_insert in varchar2 := 'INSERT',
257 p_org_count in number := 0,
258 p_rcm_assoc in varchar2 := 'N',
259 p_batch_id in number := null,
260 p_rcm_org_intf_id in number := null,
261 p_risk_id in number := null,
262 p_control_id in number := null,
263 p_commit in varchar2 := FND_API.G_FALSE,
264 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
265 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
266 x_return_status out nocopy varchar2,
267 x_msg_count out nocopy number,
268 x_msg_data out nocopy varchar2
269 );
270
271 procedure process_amw_rcm_org(
272 p_batch_id in number := null,
273 p_rcm_org_intf_id in number := null,
274 p_process_organization_id in number := null,
275 p_organization_id in number := null,
276 p_process_id in number := null,
277 p_risk_id in number := null,
278 p_control_id in number := null,
279 p_commit in varchar2 := FND_API.G_FALSE,
280 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
281 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
282 x_return_status out nocopy varchar2,
283 x_msg_count out nocopy number,
284 x_msg_data out nocopy varchar2
285 );
286
287 procedure process_amw_acct_assoc(
288 p_assoc_mode in varchar2 := 'ASSOCIATE',
289 p_process_id in number,
290 p_process_organization_id in number,
291 p_commit in varchar2 := FND_API.G_FALSE,
292 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
293 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
294 x_return_status out nocopy varchar2,
295 x_msg_count out nocopy number,
296 x_msg_data out nocopy varchar2
297 );
298
299 -- ==============================================================================
300 -- Start of Comments
301 -- ==============================================================================
302 -- API Name
303 -- Process_Amw_Risk_Assoc
304 -- Type
305 -- Private
306 -- Pre-Req
307 --
308 -- Parameters
309 --
310 -- IN
311 -- p_assoc_mode IN VARCHAR2 Otional Default = 'ASSOCIATE'
312 -- p_process_id IN number Required
313 -- p_process_organization_id IN number Required
314 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
315 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
316 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
317 -- OUT
318 -- x_return_status OUT VARCHAR2
319 -- x_msg_count OUT NUMBER
320 -- x_msg_data OUT VARCHAR2
321 -- Version : Current version 1.0
322 -- Note:
323 --
324 -- End of Comments
325 -- ==============================================================================
326 --
327
328 procedure process_amw_risk_assoc(
329 p_assoc_mode in varchar2 := 'ASSOCIATE',
330 p_process_id in number,
334 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
331 p_process_organization_id in number,
332 p_commit in varchar2 := FND_API.G_FALSE,
333 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
335 x_return_status out nocopy varchar2,
336 x_msg_count out nocopy number,
337 x_msg_data out nocopy varchar2
338 );
339
340 -- ==============================================================================
341 -- Start of Comments
342 -- ==============================================================================
343 -- API Name
344 -- Process_Amw_Control_Assoc
345 -- Type
346 -- Private
347 -- Pre-Req
348 --
349 -- Parameters
350 --
351 -- IN
352 -- p_assoc_mode IN VARCHAR2 Otional Default = 'ASSOCIATE'
353 -- p_risk_association_id IN number Required
354 -- p_risk_id IN number Required
355 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
356 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
357 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
358 -- OUT
359 -- x_return_status OUT VARCHAR2
360 -- x_msg_count OUT NUMBER
361 -- x_msg_data OUT VARCHAR2
362 -- Version : Current version 1.0
363 -- Note:
364 --
365 -- End of Comments
366 -- ==============================================================================
367 --
368
369 procedure process_amw_control_assoc(
370 p_assoc_mode in varchar2 := 'ASSOCIATE',
371 p_risk_association_id in number,
372 p_risk_id in number,
373 p_commit in varchar2 := FND_API.G_FALSE,
374 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
375 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
376 x_return_status out nocopy varchar2,
377 x_msg_count out nocopy number,
378 x_msg_data out nocopy varchar2
379 );
380
381 -- ==============================================================================
382 -- Start of Comments
383 -- ==============================================================================
384 -- API Name
385 -- Process_Amw_Ap_Assoc
386 -- Type
387 -- Private
388 -- Pre-Req
389 --
390 -- Parameters
391 --
392 -- IN
393 -- p_assoc_mode IN VARCHAR2 Otional Default = 'ASSOCIATE'
394 -- p_control_association_id IN number Required
395 -- p_control_id IN number Required
396 -- p_commit IN VARCHAR2 Required Default = FND_API_G_FALSE
397 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
398 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
399 -- OUT
400 -- x_return_status OUT VARCHAR2
401 -- x_msg_count OUT NUMBER
402 -- x_msg_data OUT VARCHAR2
403 -- Version : Current version 1.0
404 -- Note:
405 --
406 -- End of Comments
407 -- ==============================================================================
408 --
409
410 procedure process_amw_ap_assoc(
411 p_assoc_mode in varchar2 := 'ASSOCIATE',
412 p_control_association_id in number,
413 p_control_id in number,
414 p_commit in varchar2 := FND_API.G_FALSE,
415 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
416 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
417 x_return_status out nocopy varchar2,
418 x_msg_count out nocopy number,
419 x_msg_data out nocopy varchar2
420 );
421
422 PROCEDURE process_hierarchy_count (
423 p_process_id IN NUMBER := NULL,
424 p_organization_id IN NUMBER := NULL,
425 p_risk_count in number := null,
426 p_control_count in number := null,
427 p_mode IN VARCHAR2 := 'ASSOCIATE',
428 p_commit IN VARCHAR2 := fnd_api.g_false,
429 x_return_status OUT NOCOPY VARCHAR2,
430 x_msg_count OUT NOCOPY NUMBER,
431 x_msg_data OUT NOCOPY VARCHAR2
432 );
433
434 -- ==============================================================================
435 -- Start of Comments
436 -- ==============================================================================
437 -- API Name
438 -- Validate_Apo_Type
439 -- Type
440 -- Private
441 -- Pre-Req
442 --
443 -- Parameters
444 --
445 -- IN
446 -- p_api_version_number IN NUMBER REQUIRED
447 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
448 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
449 -- p_apo_type IN apo_type Required
450 --
451 -- OUT
452 -- x_return_status OUT VARCHAR2
453 -- x_msg_count OUT NUMBER
454 -- x_msg_data OUT VARCHAR2
455 -- Version : Current version 1.0
456 -- Note:
457 --
458 -- End of Comments
459 -- ==============================================================================
460 --
461
462 PROCEDURE validate_apo_type(
463 p_api_version_number IN NUMBER,
467 x_return_status OUT nocopy VARCHAR2,
464 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
465 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
466 p_apo_type IN apo_type,
468 x_msg_count OUT nocopy NUMBER,
469 x_msg_data OUT nocopy VARCHAR2
470 );
471
472 -- ==============================================================================
473 -- Start of Comments
474 -- ==============================================================================
475 -- API Name
476 -- Check_Apo_Row
477 -- Type
478 -- Private
479 -- Pre-Req
480 --
481 -- Parameters
482 --
483 -- IN
484 -- p_apo_type IN apo_type Required
485 -- p_validation_mode IN VARCHAR2 Optional Default = JTF_PLSQL_API.g_create
486 --
487 -- OUT
488 -- x_return_status OUT VARCHAR2
489 -- Version : Current version 1.0
490 -- Note:
491 --
492 -- End of Comments
493 -- ==============================================================================
494 --
495 PROCEDURE check_apo_row(
496 p_apo_type IN apo_type,
497 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
498 x_return_status OUT nocopy VARCHAR2
499 );
500
501 FUNCTION GET_parent_process_id(p_process_id in number,
502 p_organization_id in number) return number;
503
504 END AMW_PROC_ORG_HIERARCHY_PVT; -- Package spec