[Home] [Help]
PACKAGE BODY: APPS.PA_CBS_LEVEL_HIERARCHY_PVT
Source
4 g_module_name VARCHAR2(100) := 'pa.plsql.PA_CBS_LEVEL_HIERARCHY_PVT';
1 PACKAGE BODY PA_CBS_LEVEL_HIERARCHY_PVT AS
2 --$Header: PACBSLVB.pls 120.0.12020000.2 2013/03/25 11:13:34 bnoorbha noship $
3
5 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
6
7
8 procedure PRINT_MSG(P_MSG VARCHAR2
9 ,p_dbug_flag VARCHAR2 default 'N') is
10
11 BEGIN
12 IF (P_PA_DEBUG_MODE = 'Y' ) Then
13 pa_debug.g_err_stage := substr('LOG:'||p_msg,1,240);
14 PA_DEBUG.write
15 (x_Module => g_module_name
16 ,x_Msg => pa_debug.g_err_stage
17 ,x_Log_Level => 3);
18 END IF;
19 Return;
20 END PRINT_MSG;
21
22
23 Procedure Insert_Level(
24 P_CbsHeaderId IN Number,
25 P_Structure_Level IN Number,
26 P_Cost_Category IN Varchar2,
27 P_Meaning IN Varchar2,
28 P_Description IN Varchar2 Default NULL,
29 P_Record_Version_Number IN Number,
30 X_Return_Status OUT NOCOPY Varchar2,
31 X_Msg_Data OUT NOCOPY Varchar2,
32 X_Msg_Count OUT NOCOPY Number)
33 IS
34 UNABLE_TO_INSERT_LEVEL Exception;
35 L_CostCategoryExists Varchar2(240);
36 L_CbsStructLevelId Number;
37
38 cursor checkLevelExists IS
39 select COST_CATEGORY from PA_CBS_HIERARCHY
40 where CBS_HEADER_ID = P_CbsHeaderId and
41 STRUCTURE_LEVEL = P_Structure_Level;
42
43 cursor getCbsStructLevelId IS
44 Select PA_CBS_HIERARCHY_S.NextVal
45 From Dual;
46
47 BEGIN
48
49 x_return_status := FND_API.G_RET_STS_SUCCESS;
50 x_msg_data := NULL;
51 x_msg_count := 0;
52
53 IF p_pa_debug_mode = 'Y' Then
54 pa_debug.init_err_stack('PA_CBS_LEVEL_HIERARCHY_PVT.Insert_Level');
55 pa_debug.set_process('PLSQL','LOG',P_PA_DEBUG_MODE);
56 End If;
57
58 print_msg('PA_CBS_LEVEL_HIERARCHY_PVT.Insert_Level IN param(Scalar) values');
59 print_msg('P_CbsHeaderId :: ' || P_CbsHeaderId);
60 print_msg('P_Structure_Level :: ' || P_Structure_Level);
61 print_msg('P_Cost_Category :: '|| P_Cost_Category);
62 print_msg('P_Meaning :: '|| P_Meaning);
63 print_msg('P_Description :: '|| P_Description);
64 print_msg('P_Record_Version_Number :: '|| P_Record_Version_Number);
65
66 open checkLevelExists ;
67 fetch checkLevelExists into L_CostCategoryExists ;
68 IF checkLevelExists%FOUND THEN
69 RAISE UNABLE_TO_INSERT_LEVEL;
70 END IF;
71 close checkLevelExists ;
72
73
74
75 open getCbsStructLevelId ;
76 fetch getCbsStructLevelId into L_CbsStructLevelId ;
77 close getCbsStructLevelId ;
78
79 INSERT INTO PA_CBS_HIERARCHY (
80 CBS_STRUCT_LEVEL_ID ,
81 CBS_HEADER_ID ,
82 STRUCTURE_LEVEL ,
83 COST_CATEGORY ,
84 MEANING ,
85 LAST_UPDATE_DATE ,
86 LAST_UPDATED_BY ,
87 CREATION_DATE ,
88 CREATED_BY ,
89 LAST_UPDATE_LOGIN ,
90 DESCRIPTION ,
91 RECORD_VERSION_NUMBER )
92 VALUES (
93 L_CbsStructLevelId ,
94 P_CbsHeaderId ,
95 P_Structure_Level ,
96 P_Cost_Category ,
97 P_Meaning ,
98 SysDate ,
99 Fnd_Global.User_Id ,
100 SysDate ,
101 Fnd_Global.User_Id ,
102 Fnd_Global.Login_Id ,
103 P_Description ,
104 P_Record_Version_Number
105 );
106
107
108
109
110 EXCEPTION
111 When UNABLE_TO_INSERT_LEVEL Then
112 X_Return_Status := 'U';
113 X_Msg_Data := 'PA_DUPLICATE_CBS_LEVEL';
114 X_Msg_Count := 1;
115
116 When Others Then
120 Raise;
117 X_Return_Status := 'U';
118 X_Msg_Data := SqlErrm;
119 X_Msg_Count := 1;
121
122 END Insert_Level;
123
124
125 PROCEDURE Update_Level(
126 P_CbsStructLevelId IN Number,
127 P_CbsHeaderId IN Number,
128 P_Structure_Level IN Number,
129 P_Cost_Category IN Varchar2,
130 P_Meaning IN Varchar2,
131 P_Description IN Varchar2 Default NULL,
132 P_Record_Version_Number IN Number,
133 X_Record_Version_Number OUT NOCOPY Number,
134 X_Return_Status OUT NOCOPY Varchar2,
135 X_Msg_Data OUT NOCOPY Varchar2,
136 X_Msg_Count OUT NOCOPY Number)
137 IS
138 UNABLE_TO_UPDATE_LEVEL Exception;
139 L_CostCategoryUsed Varchar2(1);
140
141 cursor checkLevelUsed IS
142 select 'N' from dual;
143
144 BEGIN
145
146 x_return_status := FND_API.G_RET_STS_SUCCESS;
147 x_msg_data := NULL;
148 x_msg_count := 0;
149
150 IF p_pa_debug_mode = 'Y' Then
151 pa_debug.init_err_stack('PA_CBS_LEVEL_HIERARCHY_PVT.Update_Level');
152 pa_debug.set_process('PLSQL','LOG',P_PA_DEBUG_MODE);
153 End If;
154
155 print_msg('PA_CBS_LEVEL_HIERARCHY_PVT.Update_Level IN param(Scalar) values');
156 print_msg('P_CbsStructLevelId :: ' || P_CbsStructLevelId);
157 print_msg('P_CbsHeaderId :: ' || P_CbsHeaderId);
158 print_msg('P_Structure_Level :: ' || P_Structure_Level);
159 print_msg('P_Cost_Category :: '|| P_Cost_Category);
160 print_msg('P_Description :: '|| P_Description);
161 print_msg('P_Record_Version_Number :: '|| P_Record_Version_Number);
162
163 L_CostCategoryUsed := 'N';
164
165 open checkLevelUsed ;
166 fetch checkLevelUsed into L_CostCategoryUsed ;
167 close checkLevelUsed ;
168
169 IF L_CostCategoryUsed <> 'N' THEN
170 RAISE UNABLE_TO_UPDATE_LEVEL;
171 END IF;
172
173 UPDATE PA_CBS_HIERARCHY
174 SET
175 COST_CATEGORY = P_Cost_Category,
176 MEANING = P_Meaning,
177 DESCRIPTION = P_Description,
178 RECORD_VERSION_NUMBER = RECORD_VERSION_NUMBER + 1,
179 LAST_UPDATE_DATE = Sysdate,
180 LAST_UPDATED_BY = Fnd_Global.User_Id,
181 LAST_UPDATE_LOGIN = Fnd_Global.Login_Id
182 WHERE
183 CBS_HEADER_ID = P_CbsHeaderId
184 AND STRUCTURE_LEVEL = P_Structure_Level
185 AND RECORD_VERSION_NUMBER = P_Record_Version_Number
186 AND CBS_STRUCT_LEVEL_ID = P_CbsStructLevelId;
187
188 X_Record_Version_Number := P_Record_Version_Number + 1;
189
190 EXCEPTION
191 When UNABLE_TO_UPDATE_LEVEL Then
192 X_Return_Status := 'U';
193 X_Msg_Data := 'PA_DUPLICATE_CBS_LEVEL';
194 X_Msg_Count := 1;
195 When Others Then
196 X_Return_Status := 'U';
197 X_Msg_Data := SqlErrm;
201 END Update_Level;
198 X_Msg_Count := 1;
199 Raise;
200
202
203
204 PROCEDURE Delete_Level(
205 P_CbsStructLevelId IN Number,
206 P_CbsHeaderId IN Number,
207 P_Structure_Level IN Number,
208 P_Cost_Category IN Varchar2,
212 X_Msg_Count OUT NOCOPY Number)
209 P_Record_Version_Number IN Number,
210 X_Return_Status OUT NOCOPY Varchar2,
211 X_Msg_Data OUT NOCOPY Varchar2,
213 IS
214 UNABLE_TO_DELETE_LEVEL Exception;
215 L_CostCategoryUsed Varchar2(1);
216
217 cursor checkLevelUsed IS
218 select IS_CBS_USED(P_CbsHeaderId) from dual;
219
220 BEGIN
221
222 x_return_status := FND_API.G_RET_STS_SUCCESS;
223 x_msg_data := NULL;
224 x_msg_count := 0;
225
226 IF p_pa_debug_mode = 'Y' Then
227 pa_debug.init_err_stack('PA_CBS_LEVEL_HIERARCHY_PVT.Delete_Level');
228 pa_debug.set_process('PLSQL','LOG',P_PA_DEBUG_MODE);
229 End If;
230
231 print_msg('PA_CBS_LEVEL_HIERARCHY_PVT.Delete_Level IN param(Scalar) values');
232 print_msg('P_CbsStructLevelId :: ' || P_CbsStructLevelId);
233 print_msg('P_CbsHeaderId :: ' || P_CbsHeaderId);
234 print_msg('P_Structure_Level :: ' || P_Structure_Level);
235 print_msg('P_Cost_Category :: '|| P_Cost_Category);
236 print_msg('P_Record_Version_Number :: '|| P_Record_Version_Number);
237
238 L_CostCategoryUsed := 'N';
239
240 open checkLevelUsed ;
241 fetch checkLevelUsed into L_CostCategoryUsed ;
242 close checkLevelUsed ;
243
244 IF L_CostCategoryUsed <> 'N' THEN
245 RAISE UNABLE_TO_DELETE_LEVEL;
246 END IF;
247
248 delete from PA_RBS_ELEMENTS where
249 rbs_level = (P_Structure_Level)+1 and
250 rbs_version_id = (select rbs_version_id from pa_rbs_versions_b where rbs_header_id = P_CbsHeaderId);
251
252 DELETE FROM PA_CBS_HIERARCHY
253 WHERE CBS_HEADER_ID = P_CbsHeaderId
254 AND STRUCTURE_LEVEL = P_Structure_Level
255 AND RECORD_VERSION_NUMBER = P_Record_Version_Number
256 AND CBS_STRUCT_LEVEL_ID = P_CbsStructLevelId;
257
258
259 EXCEPTION
260 When UNABLE_TO_DELETE_LEVEL Then
261 X_Return_Status := 'U';
262 X_Msg_Data := 'PA_CBS_ASSIGNED_TO_PROJECT';
263 X_Msg_Count := 1;
264 When Others Then
265 X_Return_Status := 'U';
266 X_Msg_Data := SqlErrm;
267 X_Msg_Count := 1;
268 Raise;
269
270 END Delete_Level;
271
272 FUNCTION IS_CBS_USED(
273 P_Cbs_Header_Id IN Number) return varchar2
274 IS
275 L_TEMP VARCHAR2(1);
276
277 CURSOR C1 is
278 select 'Y' from dual where
279 EXISTS
280 (SELECT *
281 FROM PA_PROJECTS_ALL
282 WHERE CBS_VERSION_ID =
283 (select rbs_version_id from pa_rbs_versions_b where rbs_header_id = P_CBS_HEADER_ID));
284
285 begin
286
287 open c1;
288 fetch c1 into L_TEMP;
289 close c1;
290
291 IF L_TEMP='Y' THEN
292 return 'Y';
293 ELSE
294 return 'N';
295 END IF;
296 end;
297
298
299 END PA_CBS_LEVEL_HIERARCHY_PVT;