[Home] [Help]
PACKAGE BODY: APPS.AMW_ASSESSMENTS_PVT
Source
1 PACKAGE BODY AMW_ASSESSMENTS_PVT as
2 /* $Header: amwtassb.pls 120.0 2005/05/31 20:08:30 appldev noship $ */
3
4 -- ===============================================================
5 -- Package name
6 -- AMW_ASSESSMENTS_PVT
7 -- Purpose
8 -- for handling Assessments and the related
9 -- History
10 -- 12/28/2003 tsho Creates
11 -- ===============================================================
12
13
14 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AMW_ASSESSMENTS_PVT';
15 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amwtassb.pls';
16
17
18 -- ===============================================================
19 -- Procedure name
20 -- Delete_Procedure_Related
21 -- Purpose
22 -- Delete any others related to this assessment procedure,
23 -- ie. assessProcedure, assessProcedureStep
24 -- ===============================================================
25 PROCEDURE Delete_Procedure_Related(
26 p_assess_procedure_id IN NUMBER,
27 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
28 p_commit IN VARCHAR2 := FND_API.G_FALSE,
29 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
30 x_return_status OUT NOCOPY VARCHAR2,
31 x_msg_count OUT NOCOPY NUMBER,
32 x_msg_data OUT NOCOPY VARCHAR2
33 ) IS
34
35 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Procedure_Related';
36 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
37
38 BEGIN
39 -- create savepoint if p_commit is true
40 IF p_commit = FND_API.G_TRUE THEN
41 SAVEPOINT Delete_Procedure_Related;
42 END IF;
43
44 x_return_status := G_RET_STS_SUCCESS;
45
46 IF (p_assess_procedure_id IS NOT NULL) THEN
47 delete from AMW_ASSESS_PROCEDURE_STEPS_TL
48 where ASSESS_PROCEDURE_STEP_ID IN (
49 select ASSESS_PROCEDURE_STEP_ID
50 from AMW_ASSESS_PROCEDURE_STEPS_B
51 where ASSESS_PROCEDURE_ID = p_assess_procedure_id
52 );
53
54 delete from AMW_ASSESS_PROCEDURE_STEPS_B
55 where ASSESS_PROCEDURE_ID = p_assess_procedure_id;
56
57 delete from AMW_ASSESS_PROCEDURES_TL
58 where ASSESS_PROCEDURE_ID = p_assess_procedure_id;
59
60 delete from AMW_ASSESS_PROCEDURES_B
61 where ASSESS_PROCEDURE_ID = p_assess_procedure_id;
62
63 END IF; -- end of if: p_assess_procedure_id IS NOT NULL
64
65 EXCEPTION
66 WHEN FND_API.G_EXC_ERROR THEN
67 IF p_commit = FND_API.G_TRUE THEN
68 ROLLBACK TO Delete_Procedure_Related;
69 END IF;
70
71 x_return_status := FND_API.G_RET_STS_ERROR;
72 fnd_msg_pub.count_and_get(p_count => x_msg_count,
73 p_data => x_msg_data);
74 WHEN OTHERS THEN
75 IF p_commit = FND_API.G_TRUE THEN
76 ROLLBACK TO Delete_Procedure_Related;
77 END IF;
78
79 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
80 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_ASSESSMENTS_PVT',
81 p_procedure_name => 'Delete_Procedure_Related',
82 p_error_text => SUBSTRB(SQLERRM,1,240));
83
84 fnd_msg_pub.count_and_get(p_count => x_msg_count,
85 p_data => x_msg_data);
86
87 END Delete_Procedure_Related;
88
89
90
91
92 -- ===============================================================
93 -- Procedure name
94 -- Delete_Assessment_Related
95 -- Purpose
96 -- Delete any others related to this assessment,
97 -- ie. AssessmentProcedure Assoc, Survey, Context, Component
98 -- ===============================================================
99 PROCEDURE Delete_Assessment_Related(
100 p_assessment_id IN NUMBER,
101 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
102 p_commit IN VARCHAR2 := FND_API.G_FALSE,
103 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
104 x_return_status OUT NOCOPY VARCHAR2,
105 x_msg_count OUT NOCOPY NUMBER,
106 x_msg_data OUT NOCOPY VARCHAR2
107 ) IS
108
109 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Procedure_Related';
110 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
111
112 BEGIN
113 -- create savepoint if p_commit is true
114 IF p_commit = FND_API.G_TRUE THEN
115 SAVEPOINT Delete_Procedure_Related;
116 END IF;
117
118 x_return_status := G_RET_STS_SUCCESS;
119
120 IF (p_assessment_id IS NOT NULL) THEN
121 -- Assessment Procedure Association
122 DELETE FROM AMW_ASSESS_PROCEDURE_ASSOCS WHERE OBJECT_TYPE='ASSESSMENT' AND PK1=p_assessment_id;
123
124 -- Context
125 DELETE FROM AMW_ASSESSMENT_CONTEXTS WHERE ASSESSMENT_ID = p_assessment_id;
126
127 -- Survey Association
128 DELETE FROM AMW_SURVEY_ASSOCS WHERE OBJECT_TYPE='ASSESSMENT' AND OBJECT_ID=p_assessment_id;
129
130 -- Assessment Component
131 DELETE FROM AMW_ASSESSMENT_COMPONENTS WHERE OBJECT_TYPE='ASSESSMENT' AND OBJECT_ID=p_assessment_id;
132
133 -- Attachment
134 FND_ATTACHED_DOCUMENTS2_PKG.delete_attachments('AMW_ASSESSMENT',
135 p_assessment_id,
136 null,
137 null,
138 null,
139 null,
140 'N',
141 null);
142 END IF; -- end of if: p_assessment_id IS NOT NULL
143
144 EXCEPTION
145 WHEN FND_API.G_EXC_ERROR THEN
146 IF p_commit = FND_API.G_TRUE THEN
147 ROLLBACK TO Delete_Assessment_Related;
148 END IF;
149
150 x_return_status := FND_API.G_RET_STS_ERROR;
151 fnd_msg_pub.count_and_get(p_count => x_msg_count,
152 p_data => x_msg_data);
153 WHEN OTHERS THEN
154 IF p_commit = FND_API.G_TRUE THEN
155 ROLLBACK TO Delete_Assessment_Related;
156 END IF;
157
158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
159 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AMW_ASSESSMENTS_PVT',
160 p_procedure_name => 'Delete_Assessment_Related',
161 p_error_text => SUBSTRB(SQLERRM,1,240));
162
163 fnd_msg_pub.count_and_get(p_count => x_msg_count,
164 p_data => x_msg_data);
165
166 END Delete_Assessment_Related;
167
168
169 -- ----------------------------------------------------------------------
170 END AMW_ASSESSMENTS_PVT;