[Home] [Help]
PACKAGE BODY: APPS.PA_RBS_ELEMENTS_PKG
Source
1 Package Body Pa_Rbs_Elements_Pkg AS
2 /* $Header: PARELETB.pls 120.0 2005/05/29 20:40:20 appldev noship $*/
3
4 Procedure Insert_Row(
5 P_Rbs_Element_Name_Id IN Number,
6 P_RBS_Version_Id IN Number,
7 P_Outline_Number IN Varchar2,
8 P_Order_Number IN Number,
9 P_Resource_Type_Id IN Number,
10 P_Resource_Source_Id IN Number,
11 P_Person_Id IN Number,
12 P_Job_Id IN Number,
13 P_Organization_Id IN Number,
14 P_Expenditure_Type_Id IN Number,
15 P_Event_Type_Id IN Number,
16 P_Expenditure_Category_Id IN Number,
17 P_Revenue_Category_Id IN Number,
18 P_Inventory_Item_Id IN Number,
19 P_Item_Category_Id IN Number,
20 P_BOM_Labor_Id IN Number,
21 P_BOM_Equipment_Id IN Number,
22 P_Non_Labor_Resource_Id IN Number,
23 P_Role_Id IN Number,
24 P_Person_Type_Id IN Number,
25 P_Resource_Class_Id IN Number,
26 P_Supplier_Id IN Number,
27 P_Rule_Flag IN Varchar2,
28 P_Parent_Element_Id IN Number,
29 P_Rbs_Level IN Number,
30 P_Element_Identifier IN Number,
31 P_User_Created_Flag IN Varchar2,
32 P_User_Defined_Custom1_Id IN Number,
33 P_User_Defined_Custom2_Id IN Number,
34 P_User_Defined_Custom3_Id IN Number,
35 P_User_Defined_Custom4_Id IN Number,
36 P_User_Defined_Custom5_Id IN Number,
37 P_Last_Update_Date IN Date,
38 P_Last_Updated_By IN Number,
39 P_Last_Update_Login IN Number,
40 P_Creation_Date IN Date,
41 P_Created_By IN Number,
42 X_RBS_Element_Id OUT NOCOPY Number,
43 X_Error_Msg_Data OUT NOCOPY Varchar2)
44
45 Is
46
47 UNABLE_TO_CREATE_REC Exception;
48 l_RowId RowId := Null;
49
50 Cursor Return_RowId(P_Id IN Number) is
51 Select
52 RowId
53 From
54 Pa_RBS_Elements
55 Where
56 RBS_Element_Id = P_Id;
57
58 Cursor GetNextId is
59 Select
60 Pa_Rbs_Elements_S.NextVal
61 From
62 Dual;
63
64 Begin
65
66 Open GetNextId;
67 Fetch GetNextId Into X_RBS_Element_Id;
68 Close GetNextId;
69
70 Insert Into Pa_RBS_Elements (
71 RBS_Element_Id,
72 Rbs_Element_Name_Id,
73 RBS_Version_Id,
74 Outline_Number,
75 Order_Number,
76 Resource_Type_Id,
77 Resource_Source_Id,
78 Person_Id,
79 Job_Id,
80 Organization_Id,
81 Expenditure_Type_Id,
82 Event_Type_Id,
83 Expenditure_Category_Id,
84 Revenue_Category_Id,
85 Inventory_Item_Id,
86 Item_Category_Id,
87 BOM_Labor_Id,
88 BOM_Equipment_Id,
89 Non_Labor_Resource_Id,
90 Role_Id,
91 Person_Type_Id,
92 Resource_Class_Id,
93 Supplier_Id,
94 Rule_Flag,
95 Parent_Element_Id,
96 Rbs_Level,
97 Element_Identifier,
98 User_Created_Flag,
99 User_Defined_Custom1_Id,
100 User_Defined_Custom2_Id,
101 User_Defined_Custom3_Id,
102 User_Defined_Custom4_Id,
103 User_Defined_Custom5_Id,
104 Last_Update_Date,
105 Last_Updated_By,
106 Creation_Date,
107 Created_By,
108 Last_Update_Login,
109 Record_Version_Number )
110 Values (
111 X_RBS_Element_Id,
112 P_Rbs_Element_Name_Id,
113 P_RBS_Version_Id,
114 P_Outline_Number,
115 P_Order_Number,
116 P_Resource_Type_Id,
117 P_Resource_Source_Id,
118 P_Person_Id,
119 P_Job_Id,
120 P_Organization_Id,
121 P_Expenditure_Type_Id,
122 P_Event_Type_Id,
123 P_Expenditure_Category_Id,
124 P_Revenue_Category_Id,
125 P_Inventory_Item_Id,
126 P_Item_Category_Id,
127 P_BOM_Labor_Id,
128 P_BOM_Equipment_Id,
129 P_Non_Labor_Resource_Id,
130 P_Role_Id,
131 P_Person_Type_Id,
132 P_Resource_Class_Id,
133 P_Supplier_Id,
134 P_Rule_Flag,
135 P_Parent_Element_Id,
136 P_Rbs_Level,
137 P_Element_Identifier,
138 P_User_Created_Flag,
139 P_User_Defined_Custom1_Id,
140 P_User_Defined_Custom2_Id,
141 P_User_Defined_Custom3_Id,
142 P_User_Defined_Custom4_Id,
143 P_User_Defined_Custom5_Id,
144 P_Last_Update_Date,
145 P_Last_Updated_By,
146 P_Creation_Date,
147 P_Created_By,
148 P_Last_Update_Login,
149 1);
150
151 Open Return_RowId (P_Id => X_RBS_Element_Id);
152 Fetch Return_RowId Into l_RowId;
153
154 If Return_RowId%NotFound Then
155
156 Close Return_RowId;
157 X_RBS_Element_Id := Null;
158 Raise UNABLE_TO_CREATE_REC;
159
160 End If;
161
162 Close Return_RowId;
163
164 Exception
165 When UNABLE_TO_CREATE_REC Then
166 -- System Error for sys admin information needed
167 X_Error_Msg_Data := 'PA_UNABLE_TO_CREATE_REC';
168 When others Then
169 Raise;
170
171 End Insert_Row;
172
173 Procedure Update_Row(
174 P_Rbs_Element_Id IN Number,
175 P_Rbs_Element_Name_Id IN Number,
176 P_Rbs_Version_Id IN Number,
177 P_Outline_Number IN Varchar2,
178 P_Order_Number IN Number,
179 P_Resource_Type_Id IN Number,
180 P_Resource_Source_Id IN Number,
181 P_Person_Id IN Number,
182 P_Job_Id IN Number,
183 P_Organization_Id IN Number,
184 P_Expenditure_Type_Id IN Number,
185 P_Event_Type_Id IN Number,
186 P_Expenditure_Category_Id IN Number,
187 P_Revenue_Category_Id IN Number,
188 P_Inventory_Item_Id IN Number,
189 P_Item_Category_Id IN Number,
190 P_BOM_Labor_Id IN Number,
191 P_BOM_Equipment_Id IN Number,
192 P_Non_Labor_Resource_Id IN Number,
193 P_Role_Id IN Number,
194 P_Person_Type_Id IN Number,
195 P_Resource_Class_Id IN Number,
196 P_Supplier_Id IN Number,
197 P_Rule_Flag IN Varchar2,
198 P_Parent_Element_Id IN Number,
199 P_Rbs_Level IN Number,
200 P_Element_Identifier IN Number,
201 P_User_Created_Flag IN Varchar2,
202 P_User_Defined_Custom1_Id IN Number,
203 P_User_Defined_Custom2_Id IN Number,
204 P_User_Defined_Custom3_Id IN Number,
205 P_User_Defined_Custom4_Id IN Number,
206 P_User_Defined_Custom5_Id IN Number,
207 P_Last_Update_Date IN Date,
208 P_Last_Updated_By IN Number,
209 P_Last_Update_Login IN Number,
210 X_Error_Msg_Data OUT NOCOPY Varchar2)
211
212 Is
213
214 REC_VER_NUM_MISMATCH Exception;
215
216 Begin
217
218 Update PA_RBS_Elements
219 Set
220 RBS_Version_Id = P_Rbs_Version_Id,
221 Rbs_Element_Name_Id = P_Rbs_Element_Name_Id,
222 Outline_Number = P_Outline_Number,
223 Order_Number = P_Order_Number,
224 Resource_Type_Id = P_Resource_Type_Id,
225 Resource_Source_Id = P_Resource_Source_Id,
226 Person_Id = P_Person_Id,
227 Job_Id = P_Job_Id,
228 Organization_Id = P_Organization_Id,
229 Expenditure_Type_Id = P_Expenditure_Type_Id,
230 Event_Type_Id = P_Event_Type_Id,
231 Expenditure_Category_Id = P_Expenditure_Category_Id,
232 Revenue_Category_Id = P_Revenue_Category_Id,
233 Inventory_Item_Id = P_Inventory_Item_Id,
234 Item_Category_Id = P_Item_Category_Id,
235 BOM_Labor_Id = P_BOM_Labor_Id,
236 BOM_Equipment_Id = P_BOM_Equipment_Id,
237 Non_Labor_Resource_Id = P_Non_Labor_Resource_Id,
238 Role_Id = P_Role_Id,
239 Person_Type_Id = P_Person_Type_Id,
240 Resource_Class_Id = P_Resource_Class_Id,
241 Supplier_Id = P_Supplier_Id,
242 Rule_Flag = P_Rule_Flag,
243 Parent_Element_Id = P_Parent_Element_Id,
244 Rbs_Level = P_Rbs_Level,
245 Element_Identifier = P_Element_Identifier,
246 User_Created_Flag = P_User_Created_Flag,
247 User_Defined_Custom1_Id = P_User_Defined_Custom1_Id,
248 User_Defined_Custom2_Id = P_User_Defined_Custom2_Id,
249 User_Defined_Custom3_Id = P_User_Defined_Custom3_Id,
250 User_Defined_Custom4_Id = P_User_Defined_Custom4_Id,
251 User_Defined_Custom5_Id = P_User_Defined_Custom5_Id,
252 Last_Update_Date = P_Last_Update_Date,
253 Last_Updated_By = P_Last_Updated_By,
254 Last_Update_Login = P_Last_Update_Login,
255 Record_Version_Number = Record_Version_Number + 1
256 Where
257 Rbs_Element_Id = P_Rbs_Element_Id;
258
259 If Sql%NotFound Then
260
261 Raise REC_VER_NUM_MISMATCH;
262
263 End If;
264
265
266 Exception
267 When REC_VER_NUM_MISMATCH Then
268 X_Error_Msg_Data := 'PA_RECORD_ALREADY_UPDATED';
269 When Others Then
270 Raise;
271
272 End Update_Row;
273
274 Procedure Delete_Row(
278
275 P_RBS_Element_Id IN Number)
276
277 Is
279
280 Begin
281
282 Delete
283 From
284 Pa_Rbs_Elements
285 Where
286 Rbs_Element_Id = P_Rbs_Element_Id;
287
288 Exception
289 When Others Then
290 Raise;
291
292 End Delete_Row;
293
294 End Pa_Rbs_Elements_Pkg;