[Home] [Help]
PACKAGE: APPS.AMW_RISK_PVT
Source
1 PACKAGE AMW_Risk_PVT AS
2 /* $Header: amwvrsks.pls 120.0 2005/05/31 19:22:38 appldev noship $ */
3
4 -- ===============================================================
5 -- Package name
6 -- AMW_Risk_PVT
7 -- Purpose
8 -- for Import Risk : Load_Risk (without knowing any risk_id in advance)
9 -- for direct call : Operate_Risk (knowing risk_id or risk_rev_id)
10 -- History
11 -- 7/23/2003 tsho Creates
12 -- 12/09/2004 tsho modify for new column in base table: Classification
13 -- 01/05/2005 tsho add Approve_Risk procedure to approve risk without workflow
14 -- ===============================================================
15
16 -- Default number of records fetch per call
17 G_DEFAULT_NUM_REC_FETCH NUMBER := 30;
18
19
20 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
21 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
22 G_OBJ_TYPE CONSTANT VARCHAR2(80) := AMW_UTILITY_PVT.GET_LOOKUP_MEANING('AMW_OBJECT_TYPE','RISK');
23
24 -- FND_API global constant
25 G_FALSE CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
26 G_TRUE CONSTANT VARCHAR2(1) := FND_API.G_TRUE;
27 G_VALID_LEVEL_FULL CONSTANT NUMBER := FND_API.G_VALID_LEVEL_FULL;
28 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
29 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_ERROR;
30 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := FND_API.G_RET_STS_UNEXP_ERROR;
31
32
33 -- Operate_Risk with global p_operate_mode
34 G_OP_CREATE CONSTANT NUMBER := 10;
35 G_OP_UPDATE CONSTANT NUMBER := 20;
36 G_OP_REVISE CONSTANT NUMBER := 30;
37 G_OP_DELETE CONSTANT NUMBER := 40;
38
39
40 -- ===================================================================
41 -- Record name
42 -- risk_rec_type
43 -- Parameters:
44 -- risk_id
45 -- last_update_date
46 -- last_update_login
47 -- created_by
48 -- last_updated_by
49 -- risk_impact
50 -- likelihood
51 -- attribute_category
52 -- attribute1
53 -- attribute2
54 -- attribute3
55 -- attribute4
56 -- attribute5
57 -- attribute6
58 -- attribute7
59 -- attribute8
60 -- attribute9
61 -- attribute10
62 -- attribute11
63 -- attribute12
64 -- attribute13
65 -- attribute14
66 -- attribute15
67 -- security_group_id
68 -- risk_type
69 -- approval_status
70 -- object_version_number
71 -- approval_date
72 -- creation_date
73 -- risk_rev_num
74 -- risk_rev_id
75 -- requestor_id
76 -- orig_system_reference
77 -- latest_revision_flag
78 -- end_date
79 -- curr_approved_flag
80 -- risk_name
81 -- risk_description
82 -- material
83 -- classification (12.09.2004 added by tsho)
84 -- ===================================================================
85 TYPE risk_rec_type IS RECORD
86 (
87 risk_id NUMBER := NULL,
88 last_update_date DATE := NULL,
89 last_update_login NUMBER := NULL,
90 created_by NUMBER := NULL,
91 last_updated_by NUMBER := NULL,
92 risk_impact VARCHAR2(30) := NULL,
93 likelihood VARCHAR2(30) := NULL,
94 attribute_category VARCHAR2(30) := NULL,
95 attribute1 VARCHAR2(150) := NULL,
96 attribute2 VARCHAR2(150) := NULL,
97 attribute3 VARCHAR2(150) := NULL,
98 attribute4 VARCHAR2(150) := NULL,
99 attribute5 VARCHAR2(150) := NULL,
100 attribute6 VARCHAR2(150) := NULL,
101 attribute7 VARCHAR2(150) := NULL,
102 attribute8 VARCHAR2(150) := NULL,
103 attribute9 VARCHAR2(150) := NULL,
104 attribute10 VARCHAR2(150) := NULL,
105 attribute11 VARCHAR2(150) := NULL,
106 attribute12 VARCHAR2(150) := NULL,
107 attribute13 VARCHAR2(150) := NULL,
108 attribute14 VARCHAR2(150) := NULL,
109 attribute15 VARCHAR2(150) := NULL,
110 security_group_id NUMBER := NULL,
111 risk_type VARCHAR2(30) := NULL,
112 approval_status VARCHAR2(30) := NULL,
113 object_version_number NUMBER := NULL,
114 approval_date DATE := NULL,
115 creation_date DATE := NULL,
116 risk_rev_num NUMBER := NULL,
117 risk_rev_id NUMBER := NULL,
118 requestor_id NUMBER := G_USER_ID,
119 orig_system_reference VARCHAR2(240) := NULL,
120 latest_revision_flag VARCHAR2(1) := NULL,
121 end_date DATE := NULL,
122 curr_approved_flag VARCHAR2(1) := NULL,
123 risk_name VARCHAR2(240) := NULL,
124 risk_description VARCHAR2(4000) := NULL,
125 material varchar2(1) := NULL,
126 classification NUMBER := NULL
127 );
128
129 g_miss_risk_rec risk_rec_type;
130 TYPE risk_tbl_type IS TABLE OF risk_rec_type INDEX BY BINARY_INTEGER;
131 g_miss_risk_tbl risk_tbl_type;
132
133
134
135 -- ===============================================================
136 -- Procedure name
137 -- Load_Risk
138 -- Purpose
139 -- for Import Risk with approval_status 'A' or 'D'
140 -- ===============================================================
141 PROCEDURE Load_Risk(
142 p_api_version_number IN NUMBER,
143 p_init_msg_list IN VARCHAR2 := G_FALSE,
144 p_commit IN VARCHAR2 := G_FALSE,
145 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
146 x_return_status OUT NOCOPY VARCHAR2,
147 x_msg_count OUT NOCOPY NUMBER,
148 x_msg_data OUT NOCOPY VARCHAR2,
149 p_risk_rec IN risk_rec_type,
150 x_risk_rev_id OUT NOCOPY NUMBER,
151 x_risk_id OUT NOCOPY NUMBER
152 );
153
154
155
156 -- ===============================================================
157 -- Procedure name
158 -- Operate_Risk
159 -- Purpose
160 -- operate risk depends on the pass-in p_operate_mode:
161 -- G_OP_CREATE
162 -- G_OP_UPDATE
163 -- G_OP_REVISE
164 -- G_OP_DELETE
165 -- Notes
166 -- the G_OP_UPDATE mode here is in business logic meaning,
167 -- not as the same as update in table handler meaning.
168 -- same goes to other p_operate_mode if it happens to
169 -- have similar name.
170 -- ===============================================================
171 PROCEDURE Operate_Risk(
172 p_operate_mode IN VARCHAR2,
173 p_api_version_number IN NUMBER,
174 p_init_msg_list IN VARCHAR2 := G_FALSE,
175 p_commit IN VARCHAR2 := G_FALSE,
176 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
177 x_return_status OUT NOCOPY VARCHAR2,
178 x_msg_count OUT NOCOPY NUMBER,
179 x_msg_data OUT NOCOPY VARCHAR2,
180 p_risk_rec IN risk_rec_type,
181 x_risk_rev_id OUT NOCOPY NUMBER,
182 x_risk_id OUT NOCOPY NUMBER
183 );
184
185
186
187 -- ===============================================================
188 -- Procedure name
189 -- Create_Risk
190 -- Purpose
191 -- create risk with specified approval_status,
192 -- if no specified approval_status in pass-in p_risk_rec,
193 -- the default approval_status is set to 'D'.
194 -- ===============================================================
195 PROCEDURE Create_Risk(
196 p_operate_mode IN VARCHAR2,
197 p_api_version_number IN NUMBER,
198 p_init_msg_list IN VARCHAR2 := G_FALSE,
199 p_commit IN VARCHAR2 := G_FALSE,
200 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
201
202 x_return_status OUT NOCOPY VARCHAR2,
203 x_msg_count OUT NOCOPY NUMBER,
204 x_msg_data OUT NOCOPY VARCHAR2,
205
206 p_risk_rec IN risk_rec_type,
207 x_risk_rev_id OUT NOCOPY NUMBER,
208 x_risk_id OUT NOCOPY NUMBER
209 );
210
211
212
213 -- ===============================================================
214 -- Procedure name
215 -- Update_Risk
216 -- Purpose
217 -- update risk with specified risk_rev_id,
218 -- if no specified risk_rev_id in pass-in p_risk_rec,
219 -- this will update the one with specified risk_id having
220 -- latest_revision_flag='Y' AND approval_status='D'.
221 -- Notes
222 -- if risk_rev_id is not specified, then
223 -- risk_id is a must when calling Update_Risk
224 -- ===============================================================
225 PROCEDURE Update_Risk(
226 p_operate_mode IN VARCHAR2,
227 p_api_version_number IN NUMBER,
228 p_init_msg_list IN VARCHAR2 := G_FALSE,
229 p_commit IN VARCHAR2 := G_FALSE,
230 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
231
232 x_return_status OUT NOCOPY VARCHAR2,
233 x_msg_count OUT NOCOPY NUMBER,
234 x_msg_data OUT NOCOPY VARCHAR2,
235
236 p_risk_rec IN risk_rec_type,
237 x_risk_rev_id OUT NOCOPY NUMBER,
238 x_risk_id OUT NOCOPY NUMBER
239 );
240
241
242
243 -- ===============================================================
244 -- Procedure name
245 -- Delete_Risk
246 -- Purpose
247 -- delete risk with specified risk_rev_id.
248 -- ===============================================================
249 PROCEDURE Delete_Risk(
250 p_operate_mode IN VARCHAR2,
251 p_api_version_number IN NUMBER,
252 p_init_msg_list IN VARCHAR2 := G_FALSE,
253 p_commit IN VARCHAR2 := G_FALSE,
254 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
255 x_return_status OUT NOCOPY VARCHAR2,
256 x_msg_count OUT NOCOPY NUMBER,
257 x_msg_data OUT NOCOPY VARCHAR2,
258 p_risk_rev_id IN NUMBER,
259 x_risk_id OUT NOCOPY NUMBER
260 );
261
262
263
264
265 -- ===============================================================
266 -- Procedure name
267 -- Revise_Without_Revision_Exists
268 -- Purpose
269 -- revise risk with specified risk_id,
270 -- it'll revise the one having latest_revision_flag='Y'
271 -- AND approval_status='A' OR 'R' of specified risk_id.
272 -- the new revision created by this call will have
273 -- latest_revision_flag='Y', and the approval_status
274 -- will be set to 'D' if not specified in the p_risk_rec
275 -- the revisee(the old one) will have latest_revision_flag='N'
276 -- Note
277 -- actually the name for Revise_Without_Revision_Exists
278 -- should be Revise_Without_Draft_Revision_Exists if there's
279 -- no limitation for the procedure name.
280 -- ===============================================================
281 PROCEDURE Revise_Without_Revision_Exists(
282 p_operate_mode IN VARCHAR2,
283 p_api_version_number IN NUMBER,
284 p_init_msg_list IN VARCHAR2 := G_FALSE,
285 p_commit IN VARCHAR2 := G_FALSE,
286 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
287
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_msg_count OUT NOCOPY NUMBER,
290 x_msg_data OUT NOCOPY VARCHAR2,
291
292 p_risk_rec IN risk_rec_type,
293 x_risk_rev_id OUT NOCOPY NUMBER,
294 x_risk_id OUT NOCOPY NUMBER
295 );
296
297
298
299 -- ===============================================================
300 -- Procedure name
301 -- Validate_risk
302 -- Purpose
303 -- Validate_risk is the container for calling all the other
304 -- validation procedures on one record(Validate_xxx_Rec) and
305 -- the container of validation on items(Check_Risk_Items)
306 -- Note
307 -- basically, this should be called before calling table handler
308 -- ===============================================================
309 PROCEDURE Validate_risk(
310 p_operate_mode IN VARCHAR2,
311 p_api_version_number IN NUMBER,
312 p_init_msg_list IN VARCHAR2 := G_FALSE,
313 p_validation_level IN NUMBER := G_VALID_LEVEL_FULL,
314 p_risk_rec IN risk_rec_type,
315 x_risk_rec OUT NOCOPY risk_rec_type,
316 x_return_status OUT NOCOPY VARCHAR2,
317 x_msg_count OUT NOCOPY NUMBER,
318 x_msg_data OUT NOCOPY VARCHAR2
319 );
320
321
322
323 -- ===============================================================
324 -- Procedure name
325 -- Check_risk_Items
326 -- Purpose
327 -- check all the necessaries for items
328 -- Note
332 -- not the business logic validation.
329 -- Check_risk_Items is the container for calling all the
330 -- other validation procedures on items(check_xxx_Items)
331 -- the validation on items should be only table column constraints
333 -- ===============================================================
334 PROCEDURE Check_risk_Items (
335 p_operate_mode IN VARCHAR2,
336 P_risk_rec IN risk_rec_type,
337 x_return_status OUT NOCOPY VARCHAR2
338 );
339
340
341
342 -- ===============================================================
343 -- Procedure name
344 -- check_risk_uk_items
345 -- Purpose
346 -- check the uniqueness of the items which have been marked
347 -- as unique in table
348 -- ===============================================================
349 PROCEDURE check_risk_uk_items(
350 p_operate_mode IN VARCHAR2,
351 p_risk_rec IN risk_rec_type,
352 x_return_status OUT NOCOPY VARCHAR2
353 );
354
355
356
357 -- ===============================================================
358 -- Procedure name
359 -- check_risk_req_items
360 -- Purpose
361 -- check the requireness of the items which have been marked
362 -- as NOT NULL in table
363 -- Note
364 -- since the standard default with
365 -- FND_API.G_MISS_XXX v.s. NULL has been changed to:
366 -- if user want to update to Null, pass in G_MISS_XXX
367 -- else if user want to update to some value, pass in value
368 -- else if user doesn't want to update, pass in NULL.
369 -- Reference
370 -- http://www-apps.us.oracle.com/atg/performance/
371 -- Standards and Templates>Business Object API Coding Standards
372 -- 2.3.1 Differentiating between Missing parameters and Null parameters
373 -- ===============================================================
374 PROCEDURE check_risk_req_items(
375 p_operate_mode IN VARCHAR2,
376 p_risk_rec IN risk_rec_type,
377 x_return_status OUT NOCOPY VARCHAR2
378 );
379
380
381
382 -- ===============================================================
383 -- Procedure name
384 -- check_risk_FK_items
385 -- Purpose
386 -- check forien key of the items
387 -- ===============================================================
388 PROCEDURE check_risk_FK_items(
389 p_operate_mode IN VARCHAR2,
390 p_risk_rec IN risk_rec_type,
391 x_return_status OUT NOCOPY VARCHAR2
392 );
393
394
395
396 -- ===============================================================
397 -- Procedure name
398 -- check_risk_Lookup_items
399 -- Purpose
400 -- check lookup of the items
401 -- ===============================================================
402 PROCEDURE check_risk_Lookup_items(
403 p_operate_mode IN VARCHAR2,
404 p_risk_rec IN risk_rec_type,
405 x_return_status OUT NOCOPY VARCHAR2
406 );
407
408
409
410
411 -- ===============================================================
412 -- Procedure name
413 -- Complete_risk_Rec
414 -- Purpose
415 -- complete(fill out) the items which are not specified.
416 -- Note
417 -- basically, this is called when G_OP_UPDATE, G_OP_REVISE
418 -- ===============================================================
419 PROCEDURE Complete_risk_Rec (
420 p_risk_rec IN risk_rec_type,
421 x_complete_rec OUT NOCOPY risk_rec_type
422 );
423
424
425
426
427 -- ===============================================================
428 -- Procedure name
429 -- Validate_risk_rec
430 -- Purpose
431 -- check all the necessaries for one record,
432 -- this includes the cross-items validation
433 -- Note
434 -- Validate_risk_rec is the dispatcher of
435 -- other validation procedures on one record.
436 -- business logic validation should go here.
437 -- ===============================================================
438 PROCEDURE Validate_risk_rec(
439 p_operate_mode IN VARCHAR2,
440 p_api_version_number IN NUMBER,
441 p_init_msg_list IN VARCHAR2 := G_FALSE,
442 x_return_status OUT NOCOPY VARCHAR2,
443 x_msg_count OUT NOCOPY NUMBER,
444 x_msg_data OUT NOCOPY VARCHAR2,
445 p_risk_rec IN risk_rec_type
446 );
447
448
449
450
451 -- ===============================================================
452 -- Procedure name
453 -- Validate_create_risk_rec
454 -- Purpose
455 -- this is the validation for mode G_OP_CREATE.
456 -- Note
457 -- risk name cannot be duplicated in table
458 -- ===============================================================
459 PROCEDURE Validate_create_risk_rec(
460 x_return_status OUT NOCOPY VARCHAR2,
461 x_msg_count OUT NOCOPY NUMBER,
462 x_msg_data OUT NOCOPY VARCHAR2,
463 p_risk_rec IN risk_rec_type
464 );
465
466
467
468
469 -- ===============================================================
470 -- Procedure name
471 -- Validate_update_risk_rec
472 -- Purpose
473 -- this is the validation for mode G_OP_UPDATE.
474 -- Note
475 -- risk name cannot be duplicated in table.
476 -- only the risk with approval_status='D' can be use G_OP_UPDATE
477 -- ===============================================================
478 PROCEDURE Validate_update_risk_rec(
479 x_return_status OUT NOCOPY VARCHAR2,
480 x_msg_count OUT NOCOPY NUMBER,
481 x_msg_data OUT NOCOPY VARCHAR2,
482 p_risk_rec IN risk_rec_type
483 );
484
485
486
487
488 -- ===============================================================
489 -- Procedure name
490 -- Validate_revise_risk_rec
491 -- Purpose
492 -- this is the validation for mode G_OP_REVISE.
493 -- Note
494 -- changing risk name when revising a risk is not allowed.
495 -- ===============================================================
496 PROCEDURE Validate_revise_risk_rec(
497 x_return_status OUT NOCOPY VARCHAR2,
498 x_msg_count OUT NOCOPY NUMBER,
499 x_msg_data OUT NOCOPY VARCHAR2,
500 p_risk_rec IN risk_rec_type
501 );
502
503
504
505 -- ===============================================================
506 -- Procedure name
507 -- Validate_delete_risk_rec
508 -- Purpose
509 -- this is the validation for mode G_OP_DELETE.
510 -- Note
511 -- not implemented yet.
512 -- need to find out when(approval_status='?') can G_OP_DELETE.
513 -- ===============================================================
514 PROCEDURE Validate_delete_risk_rec(
515 x_return_status OUT NOCOPY VARCHAR2,
516 x_msg_count OUT NOCOPY NUMBER,
517 x_msg_data OUT NOCOPY VARCHAR2,
518 p_risk_rec IN risk_rec_type
519 );
520
521
522 -- ===============================================================
523 -- Procedure name
524 -- Approve_Risk
525 -- Purpose
526 -- to approve the risk without going through workflow
527 -- Note
528 --
529 -- ===============================================================
530 PROCEDURE Approve_Risk(
531 p_risk_rev_id IN NUMBER,
532 p_init_msg_list IN VARCHAR2 := G_FALSE,
533 x_return_status OUT NOCOPY VARCHAR2,
534 x_msg_count OUT NOCOPY NUMBER,
535 x_msg_data OUT NOCOPY VARCHAR2
536 );
537
538 -- ----------------------------------------------------------------------
539 END AMW_Risk_PVT;