1 PACKAGE BODY PA_BUDGET_INTEGRATION_PKG AS
2 /* $Header: PABDINTB.pls 120.3 2006/02/16 11:27:56 bkattupa noship $ */
3
4 ----------------------------------------------------------------------------------------
5 -- Package : PA_BUDGET_INTEGRATION_PKG
6 -- Purpose : Table Handlers APIs to Insert/Update/Delete/Lock a record
7 -- thru SQL Form
8 -- Parameters :
9 -- P_Calling_Mode--> SUBMIT / GENERATE_ACCOUNT
10 ----------------------------------------------------------------------------------------
11
12
13 ----------------------------------------------------------------------------------------
14 -- Procedure : Insert_Row
15 -- Purpose : To insert a record into PA_BUDGETARY_CONTROL_OPTIONS table
16 -- Parameters : Ref: P_Calling_Mode--> SUBMIT / GENERATE_ACCOUNT
17 ----------------------------------------------------------------------------------------
18 PROCEDURE Insert_Row (
19 X_Rowid OUT NOCOPY ROWID,
20 p_Project_Type IN VARCHAR2,
21 p_Project_ID IN NUMBER,
22 p_Balance_Type IN VARCHAR2,
23 p_Budget_Type_Code IN VARCHAR2,
24 p_External_Budget_Code IN VARCHAR2,
25 p_GL_Budget_Version_ID IN NUMBER,
26 p_Encumbrance_Type_ID IN NUMBER,
27 p_Bdgt_Cntrl_Flag IN VARCHAR2,
28 P_FC_Level_Project IN VARCHAR2,
29 P_FC_Level_Task IN VARCHAR2,
30 P_FC_Level_RsrcGrp IN VARCHAR2,
31 P_FC_Level_Rsrs IN VARCHAR2,
32 P_Amount_Type IN VARCHAR2,
33 P_Boundary_Code IN VARCHAR2,
34 p_Project_Type_Org_ID IN NUMBER,
35 p_Last_Update_Date IN DATE,
36 p_Last_Updated_By IN NUMBER,
37 p_Creation_Date IN DATE,
38 p_Created_By IN NUMBER,
39 p_Last_Update_Login IN NUMBER
40 )
41 AS
42 BEGIN
43
44 BEGIN
45 INSERT INTO PA_BUDGETARY_CONTROL_OPTIONS (
46 Project_Type,
47 Project_ID,
48 Balance_Type,
49 Budget_Type_Code,
50 External_Budget_Code,
51 GL_Budget_Version_ID,
52 Encumbrance_Type_ID,
53 Bdgt_Cntrl_Flag,
54 Fund_Control_Level_Project,
55 Fund_Control_Level_Task,
56 Fund_Control_Level_Res_Grp,
57 Fund_Control_Level_Res,
58 Amount_Type,
59 Boundary_Code,
60 Project_Type_Org_ID,
61 Last_Update_Date,
62 Last_Updated_By,
63 Creation_Date,
64 Created_By,
65 Last_Update_Login
66 )
67 VALUES (
68 P_Project_Type,
69 P_Project_ID,
70 P_Balance_Type,
71 P_Budget_Type_Code,
72 P_External_Budget_Code,
73 P_GL_Budget_Version_ID,
74 P_Encumbrance_Type_ID,
75 P_Bdgt_Cntrl_Flag,
76 P_FC_Level_Project,
77 P_FC_Level_Task,
78 P_FC_Level_RsrcGrp,
79 P_FC_Level_Rsrs,
80 P_Amount_Type,
81 P_Boundary_Code,
82 P_Project_Type_Org_ID,
83 P_Last_Update_Date,
84 P_Last_Updated_By,
85 P_Creation_Date,
86 P_Created_By,
87 P_Last_Update_Login
88 );
89 END;
90
91 -- Fetch the rowid that is inserted into a table which will be used for
92 -- updating/deleting the same record if performed in the same
93 -- session (in SQL*Forms) ==> For details refer to the appln. Dev. Stds.
94 BEGIN
95 SELECT RowID
96 INTO X_RowID
97 FROM PA_BUDGETARY_CONTROL_OPTIONS
98 WHERE Budget_Type_Code = P_Budget_Type_Code
99 AND ( Project_ID = P_Project_ID OR Project_Type = P_Project_Type)
100 AND nvl(Project_Type_Org_ID,-99) = nvl(P_Project_Type_Org_ID,-99) ; -- Added for bug #4772022
101 EXCEPTION
102 WHEN NO_DATA_FOUND THEN
103 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_ERROR');
104 APP_EXCEPTION.Raise_Exception;
105 WHEN TOO_MANY_ROWS THEN
106 FND_MESSAGE.Set_Name('FND', 'PA_BC_DUPLCT_BDGT_TYP');
107 APP_EXCEPTION.Raise_Exception;
108 END;
109
110 END Insert_Row;
111
112 ----------------------------------------------------------------------------------------
113 -- Procedure : Update_Row
114 -- Purpose : To Update a record in PA_BUDGETARY_CONTROL_OPTIONS table
115 -- Parameters : ? Ref: P_Calling_Mode--> SUBMIT / GENERATE_ACCOUNT
116 ----------------------------------------------------------------------------------------
117 PROCEDURE Update_Row (
118 p_Rowid IN ROWID,
119 p_Project_Type IN VARCHAR2,
120 p_Project_ID IN NUMBER,
121 p_Balance_Type IN VARCHAR2,
122 p_Budget_Type_Code IN VARCHAR2,
123 p_External_Budget_Code IN VARCHAR2,
124 p_GL_Budget_Version_ID IN NUMBER,
125 p_Encumbrance_Type_ID IN NUMBER,
126 p_Bdgt_Cntrl_Flag IN VARCHAR2,
127 P_FC_Level_Project IN VARCHAR2,
128 P_FC_Level_Task IN VARCHAR2,
129 P_FC_Level_RsrcGrp IN VARCHAR2,
130 P_FC_Level_Rsrs IN VARCHAR2,
131 P_Amount_Type IN VARCHAR2,
132 P_Boundary_Code IN VARCHAR2,
133 p_Last_Update_Date IN DATE,
134 p_Last_Updated_By IN NUMBER,
135 p_Creation_Date IN DATE,
136 p_Created_By IN NUMBER,
137 p_Last_Update_Login IN NUMBER
138 )
139 AS
140 BEGIN
141
142 UPDATE PA_BUDGETARY_CONTROL_OPTIONS
143 SET
144 Project_Type = p_Project_Type,
145 Project_ID = p_Project_ID,
146 Balance_Type = p_Balance_Type,
147 Budget_Type_Code = p_Budget_Type_Code,
148 External_Budget_Code = p_External_Budget_Code,
149 GL_Budget_Version_ID = p_GL_Budget_Version_ID,
150 Encumbrance_Type_ID = p_Encumbrance_Type_ID,
151 Bdgt_Cntrl_Flag = p_Bdgt_Cntrl_Flag,
152 Fund_Control_Level_Project = P_FC_Level_Project,
153 Fund_Control_Level_Task = P_FC_Level_Task,
154 Fund_Control_Level_Res_Grp = P_FC_Level_RsrcGrp,
155 Fund_Control_Level_Res = P_FC_Level_Rsrs,
156 Amount_Type = p_Amount_Type,
157 Boundary_Code = p_Boundary_Code,
158 Last_Update_Date = p_Last_Update_Date,
159 Last_Updated_By = p_Last_Updated_By,
160 Creation_Date = p_Creation_Date,
161 Created_By = p_Created_By,
162 Last_Update_Login = p_Last_Update_Login
163 WHERE
164 rowid = p_Rowid;
165
166 IF (SQL%NOTFOUND) THEN
167 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_ERROR');
168 APP_EXCEPTION.Raise_Exception;
169 END IF;
170
171 END Update_Row;
172
173 ----------------------------------------------------------------------------------------
174 -- Procedure : Delete_Row
175 -- Purpose : To Delete a record from PA_BUDGETARY_CONTROL_OPTIONS table
176 -- Parameters : ? Ref: P_Calling_Mode--> SUBMIT / GENERATE_ACCOUNT
177 ----------------------------------------------------------------------------------------
178 PROCEDURE Delete_Row (
179 p_Rowid IN ROWID
180 )
181 AS
182 BEGIN
183
184 DELETE FROM PA_BUDGETARY_CONTROL_OPTIONS
185 WHERE RowID = p_Rowid;
186
187 IF (SQL%NOTFOUND) THEN
188 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_ERROR');
189 APP_EXCEPTION.Raise_Exception;
190 END IF;
191
192 END Delete_Row;
193
194
195 ----------------------------------------------------------------------------------------
196 -- Procedure : Lock_Row
197 -- Purpose : To Lock a record in PA_BUDGETARY_CONTROL_OPTIONS table
198 -- Parameters : ? Ref: P_Calling_Mode--> SUBMIT / GENERATE_ACCOUNT
199 ----------------------------------------------------------------------------------------
200 PROCEDURE Lock_Row (
201 p_Rowid IN ROWID,
202 p_Project_Type IN VARCHAR2,
203 p_Project_ID IN NUMBER,
204 p_Balance_Type IN VARCHAR2,
205 p_Budget_Type_Code IN VARCHAR2,
206 p_External_Budget_Code IN VARCHAR2,
207 p_GL_Budget_Version_ID IN NUMBER,
208 p_Encumbrance_Type_ID IN NUMBER,
209 p_Bdgt_Cntrl_Flag IN VARCHAR2,
210 P_FC_Level_Project IN VARCHAR2,
211 P_FC_Level_Task IN VARCHAR2,
212 P_FC_Level_RsrcGrp IN VARCHAR2,
213 P_FC_Level_Rsrs IN VARCHAR2,
214 P_Amount_Type IN VARCHAR2,
215 P_Boundary_Code IN VARCHAR2
216 )
217 IS
218
219 Cursor C is
220 SELECT
221 Project_Type,
222 Project_ID,
223 Balance_Type,
224 Budget_Type_Code,
225 External_Budget_Code,
226 GL_Budget_Version_ID,
227 Encumbrance_Type_ID,
228 Bdgt_Cntrl_Flag,
229 Amount_Type,
230 Boundary_Code
231 FROM
232 PA_BUDGETARY_CONTROL_OPTIONS
233 WHERE
234 RowID = p_RowID
235 FOR UPDATE OF Budget_Type_Code NOWAIT;
236
237 RecInfo C%RowType;
238
239 BEGIN
240
241 OPEN C;
242 FETCH C INTO RecInfo;
243
244 -- Check whether the record is present in the same session
245 -- If not present, then it is deleted, so display the error message
246 IF (C%NOTFOUND)
247 THEN
248 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_LOCKED');
249 APP_EXCEPTION.Raise_Exception;
250 END IF;
251
252 -- Check whether the record is existing in the same session
253 -- If present, then return without displaying the error message
254 IF ( (Recinfo.Budget_Type_Code = p_Budget_Type_Code)
255 AND
256 ( (Recinfo.Project_Type = p_Project_Type) OR
257 ( (Recinfo.Project_Type IS NULL) AND (p_Project_Type IS NULL))
258 )
259 AND
260 ( (Recinfo.Project_ID = p_Project_ID) OR
261 ( (Recinfo.Project_ID IS NULL) AND (p_Project_ID IS NULL))
262 )
263 AND
264 ( (Recinfo.Balance_Type = p_Balance_Type) OR
265 ( (Recinfo.Balance_Type IS NULL) AND (p_Balance_Type IS NULL))
266 )
267 AND
268 ( (Recinfo.External_Budget_Code = p_External_Budget_Code) OR
269 ( (Recinfo.External_Budget_Code IS NULL) AND (p_External_Budget_Code IS NULL))
270 )
271 AND
272 ( (Recinfo.GL_Budget_Version_ID = p_GL_Budget_Version_ID) OR
273 ( (Recinfo.GL_Budget_Version_ID IS NULL) AND (p_GL_Budget_Version_ID IS NULL))
274 )
275 AND
276 ( (Recinfo.Encumbrance_Type_ID = p_Encumbrance_Type_ID) OR
277 ( (Recinfo.Encumbrance_Type_ID IS NULL) AND (p_Encumbrance_Type_ID IS NULL))
278 )
279 AND
280 ( (Recinfo.Bdgt_Cntrl_Flag = p_Bdgt_Cntrl_Flag) OR
281 ( (Recinfo.Bdgt_Cntrl_Flag IS NULL) AND (p_Bdgt_Cntrl_Flag IS NULL))
282 )
283 AND
284 ( (Recinfo.Amount_Type = p_Amount_Type) OR
285 ( (Recinfo.Amount_Type IS NULL) AND (p_Amount_Type IS NULL))
286 )
287 AND
288 ( (Recinfo.Boundary_Code = p_Boundary_Code) OR
289 ( (Recinfo.Boundary_Code IS NULL) AND (p_Boundary_Code IS NULL))
290 )
291 )
292 THEN
293 RETURN;
294 ELSE
295 -- After scanning all the data elements, if any of the element is not matching
296 -- with the current record, then the record is changed, so display the error message
297 CLOSE C;
298 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
299 APP_EXCEPTION.Raise_Exception;
300 END IF;
301 END Lock_Row;
302
303 END PA_BUDGET_INTEGRATION_PKG ; /* End Package Specifications PA_BUDGET_INTEGRATION_PKG */
[Home] [Help]