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