[Home] [Help]
PACKAGE BODY: APPS.PSP_AUTO_PKG
Source
1 PACKAGE BODY psp_auto_pkg AS
2 --$Header: PSPAUTHB.pls 115.8 2002/11/19 12:00:33 ddubey psp2376993.sql $
3 /****************************************************************************************************
4 History:-
5
6 Subha Ramachandran 03/Feb/2000 Chnages for Multi-org Implementation
7
8
9 ******************************************************************************************************/
10
11 PROCEDURE Insert_Accounts_Row (X_Rowid IN OUT NOCOPY VARCHAR2,
12 X_Acct_Id NUMBER,
13 X_Acct_Type VARCHAR2,
14 X_Period_Type VARCHAR2,
15 X_Acct_Seq_Num NUMBER,
16 X_Expenditure_Type VARCHAR2,
17 X_Segment_num NUMBER,
18 X_Natural_Account VARCHAR2,
19 X_Start_Date_Active DATE,
20 X_End_Date_Active DATE ,
21 X_Set_of_Books_Id NUMBER,
22 X_Business_Group_Id NUMBER
23 ) IS
24 CURSOR C IS
25 SELECT ROWID
26 FROM PSP_AUTO_ACCTS
27 WHERE acct_id = X_Acct_Id;
28 BEGIN
29 INSERT INTO psp_auto_accts
30 (
31 acct_id,
32 acct_type,
33 period_type,
34 expenditure_type,
35 segment_num,
36 natural_account,
37 start_date_active,
38 end_date_active,
39 acct_seq_num,
40 set_of_books_id,
41 business_group_id,
42 last_update_date,
43 last_updated_by,
44 last_update_login,
45 created_by,
46 creation_date
47 )
48 VALUES
49 (
50 X_Acct_Id,
51 X_Acct_Type,
52 X_Period_Type,
53 X_Expenditure_Type,
54 X_Segment_num,
55 X_Natural_Account,
56 X_Start_Date_Active,
57 X_End_Date_Active,
58 X_Acct_Seq_Num,
59 X_Set_of_Books_Id,
60 X_Business_Group_Id,
61 sysdate,
62 fnd_global.user_id,
63 fnd_global.login_id,
64 fnd_global.user_id,
65 sysdate
66 );
67 OPEN C;
68 FETCH C INTO X_Rowid;
69 if (C%NOTFOUND) then
70 CLOSE C;
71 RAISE NO_DATA_FOUND;
72 end if;
73 CLOSE C;
74 END Insert_Accounts_Row;
75 --================================================================================
76 --================================================================================
77 PROCEDURE Lock_Accounts_Row (X_Rowid VARCHAR2,
78 X_Acct_Id NUMBER,
79 X_Acct_Type VARCHAR2,
80 X_Period_Type VARCHAR2,
81 X_Acct_Seq_Num NUMBER,
82 X_Expenditure_Type VARCHAR2,
83 X_Segment_num NUMBER,
84 X_Natural_Account VARCHAR2,
85 X_Start_Date_Active DATE,
86 X_End_Date_Active DATE,
87 X_Set_of_Books_Id NUMBER,
88 X_Business_Group_Id NUMBER
89 ) IS
90 CURSOR C IS
91 SELECT *
92 FROM psp_auto_accts
93 WHERE ROWID = X_Rowid
94 FOR UPDATE OF Acct_Id NOWAIT;
95 Recinfo C%ROWTYPE;
96 BEGIN
97 OPEN C;
98 FETCH C INTO Recinfo;
99 if (C%NOTFOUND) then
100 CLOSE C;
101 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
102 APP_EXCEPTION.Raise_Exception;
103 end if;
104 CLOSE C;
105 if (
106 -- check that mandatory columns match values in form
107 (Recinfo.Acct_Id = X_Acct_Id)
108 AND (Recinfo.Start_Date_Active = X_Start_Date_Active)
109 AND (Recinfo.Acct_Seq_Num = X_Acct_Seq_Num)
110 AND (Recinfo.Period_Type = X_Period_Type)
111 -- check that non-mandatory columns match values in form
112 AND ((Recinfo.Expenditure_Type = X_Expenditure_Type)
113 OR ((Recinfo.Expenditure_Type IS NULL)
114 AND (X_Expenditure_Type IS NULL)))
115 AND ((Recinfo.Natural_Account = X_Natural_Account)
116 OR ((Recinfo.Natural_Account IS NULL)
117 AND (X_Natural_Account IS NULL)))
118 AND ((Recinfo.End_Date_Active = X_End_Date_Active)
119 OR ((Recinfo.End_Date_Active IS NULL)
120 AND (X_End_Date_Active IS NULL)))
121 ) then
122 return;
123 else
124 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
125 APP_EXCEPTION.Raise_Exception;
126 end if;
127 END Lock_Accounts_Row;
128 PROCEDURE Update_Accounts_Row (X_Rowid VARCHAR2,
129 X_Acct_Id NUMBER,
130 X_Acct_Type VARCHAR2,
131 X_Period_Type VARCHAR2,
132 X_Acct_Seq_Num NUMBER,
133 X_Expenditure_Type VARCHAR2,
134 X_Segment_num NUMBER,
135 X_Natural_Account VARCHAR2,
136 X_Start_Date_Active DATE,
137 X_End_Date_Active DATE ,
138 X_Set_of_Books_Id NUMBER,
139 X_Business_Group_Id NUMBER
140 ) IS
141 BEGIN
142 UPDATE psp_auto_accts
143 SET Acct_Id = X_Acct_Id,
144 Acct_Type = X_Acct_Type,
145 Period_Type = X_Period_Type,
146 Acct_Seq_Num = X_Acct_Seq_Num,
147 Expenditure_Type = X_Expenditure_Type,
148 Segment_num = X_Segment_num,
149 Natural_Account = X_Natural_Account,
150
151 Start_Date_Active = X_Start_Date_Active,
152 End_Date_Active = X_End_Date_Active,
153 Set_of_books_id = X_Set_of_Books_Id,
154 Business_Group_Id = X_Business_group_id,
155 Last_Update_Date = sysdate,
156 Last_Updated_By = fnd_global.user_id,
157 Last_Update_Login = fnd_global.login_id
158 WHERE ROWID = X_Rowid;
159 if (SQL%NOTFOUND) then
160 Raise NO_DATA_FOUND;
161 end if;
162 END Update_Accounts_Row;
163
164 PROCEDURE Delete_Accounts_Row (X_Rowid VARCHAR2,
165 X_Acct_Id NUMBER) IS
166
167 v_Dummy varchar2(1);
168
169 cursor expressions_c is
170 select null from psp_auto_params
171 where acct_id = X_Acct_Id;
172
173 cursor rules_c is
174 select null from psp_auto_rules
175 where acct_id = X_Acct_Id;
176
177
178 BEGIN
179 -- Delete the detail records
180 open expressions_c;
181 fetch expressions_c into v_Dummy;
182 if (expressions_c%FOUND) then
183 DELETE FROM psp_auto_params
184 WHERE Acct_Id = X_Acct_Id;
185 if (SQL%NOTFOUND) then
186 close expressions_c;
187 Raise NO_DATA_FOUND;
188 end if;
189 close expressions_c;
190 end if;
191
192 -- Delete the detail records (rules)
193 open rules_c;
194 fetch rules_c into v_Dummy;
195 if (rules_c%FOUND) then
196 DELETE FROM psp_auto_rules
197 WHERE Acct_Id = X_Acct_Id;
198 if (SQL%NOTFOUND) then
199 close rules_c;
200 Raise NO_DATA_FOUND;
201 end if;
202 close rules_c;
203 end if;
204
205
206 -- Delete the master record
207 DELETE FROM psp_auto_accts
208 WHERE ROWID = X_Rowid;
209 if (SQL%NOTFOUND) then
210 Raise NO_DATA_FOUND;
211 end if;
212 End Delete_Accounts_Row;
213
214
215 PROCEDURE Insert_Expressions_Row (X_Rowid IN OUT NOCOPY VARCHAR2,
216 X_Acct_Id NUMBER,
217 X_Param_Line_Num NUMBER,
218 X_Lookup_Id NUMBER,
219 X_Operand VARCHAR2,
220 X_User_Value VARCHAR2
221 ) IS
222 CURSOR C IS
223 SELECT ROWID
224 FROM psp_auto_params
225 WHERE Acct_Id = X_Acct_Id
226 AND Param_line_num = X_Param_Line_Num;
227 BEGIN
228 INSERT INTO psp_auto_params
229 (
230 Acct_Id,
231 Param_Line_Num,
232 Lookup_Id,
233 Operand,
234 User_Value,
235 Last_Update_Date,
236 Last_Updated_By,
237 Last_Update_Login,
238 Created_By,
239 Creation_Date
240 )
241 VALUES
242 (
243 X_Acct_Id,
244 X_Param_Line_Num,
245 X_Lookup_Id,
246 X_Operand,
247 X_User_Value,
248 sysdate,
249 fnd_global.user_id,
250 fnd_global.login_id,
251 fnd_global.user_id,
252 sysdate
253 );
254 OPEN C;
255 FETCH C INTO X_Rowid;
256 if (C%NOTFOUND) then
257 CLOSE C;
258 RAISE NO_DATA_FOUND;
259 end if;
260 CLOSE C;
261 END Insert_Expressions_Row;
262 PROCEDURE Lock_Expressions_Row (X_Rowid VARCHAR2,
263 X_Acct_Id NUMBER,
264 X_Param_Line_Num NUMBER,
265 X_Lookup_Id NUMBER,
266 X_Operand VARCHAR2,
267 X_User_Value VARCHAR2
268 ) IS
269 CURSOR C IS
270 SELECT *
271 FROM psp_auto_params
272 WHERE ROWID = X_Rowid
273 FOR UPDATE OF Acct_Id NOWAIT;
274 Recinfo C%ROWTYPE;
275 BEGIN
276 OPEN C;
277 FETCH C INTO Recinfo;
278 if (C%NOTFOUND) then
279 CLOSE C;
280 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
281 APP_EXCEPTION.Raise_Exception;
282 end if;
283 CLOSE C;
284 if (
285 -- check that mandatory columns match values in form
286 (Recinfo.Acct_Id = X_Acct_Id)
287 AND (Recinfo.Param_Line_Num = X_Param_Line_Num)
288 AND (Recinfo.Lookup_Id = X_Lookup_Id)
289 AND (Recinfo.Operand = X_Operand)
290 -- check that non-mandatory columns match values in form
291 AND ((Recinfo.User_Value = X_User_Value)
292 OR ((Recinfo.User_Value IS NULL)
293 AND (X_User_Value IS NULL)))
294 ) then
295 return;
296 else
297 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
298 APP_EXCEPTION.Raise_Exception;
299 end if;
300 END Lock_Expressions_Row;
301 PROCEDURE Update_Expressions_Row (X_Rowid VARCHAR2,
302 X_Acct_Id NUMBER,
303 X_Param_Line_Num NUMBER,
304 X_Lookup_Id NUMBER,
305 X_Operand VARCHAR2,
306 X_User_Value VARCHAR2
307 ) IS
308 BEGIN
309 UPDATE psp_auto_params
310 SET Acct_Id = X_Acct_Id,
311 Param_Line_Num = X_Param_Line_Num,
312 Lookup_Id = X_Lookup_Id,
313 Operand = X_Operand,
314 User_Value = X_User_Value,
315 Last_Update_Date = sysdate,
316 Last_Updated_By = fnd_global.user_id,
317 Last_Update_Login = fnd_global.login_id
318 WHERE ROWID = X_Rowid;
319 if (SQL%NOTFOUND) then
320 Raise NO_DATA_FOUND;
321 end if;
322 END Update_Expressions_Row;
323 PROCEDURE Delete_Expressions_Row (X_Rowid VARCHAR2) IS
324 BEGIN
325 -- Delete the detail records
326 DELETE FROM psp_auto_params
327 WHERE ROWID = X_Rowid;
328 if (SQL%NOTFOUND) then
329 Raise NO_DATA_FOUND;
330 end if;
331 End Delete_Expressions_Row;
332 END psp_auto_pkg;