1 PACKAGE BODY JA_CN_DFF_ASSIGNMENTS_PKG AS
2 --$Header: JACNDFAB.pls 120.1.12000000.1 2007/08/13 14:09:30 qzhao noship $
3 --+=======================================================================+
4 --| Copyright (c) 2006 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNDFAB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| This package provides table handers for |
13 --| table JA_CN_DFF_ASSIGNMENTS, these handlers |
14 --| will be called by 'DFF Assignments' form to operate data in table|
15 --| JA_CN_DFF_ASSIGNMENTS |
16 --| |
17 --| PROCEDURE LIST |
18 --| PROCEDURE Insert_Row |
19 --| PROCEDURE Update_Row |
20 --| PROCEDURE Lock_Row |
21 --| |
22 --| HISTORY |
23 --| 2006/03/01 Jackey Li Created |
24 --+======================================================================*/
25
26 G_MODULE_PREFIX VARCHAR2(50) := 'ja.pl/sql.JA_CN_DFF_ASSIGNMENTS_PKG';
27 --==========================================================================
28 -- PROCEDURE NAME:
29 --
30 -- Insert_Row Public
31 --
32 -- DESCRIPTION:
33 --
34 -- This procedure is to insert data that are passed in by parameters into
35 -- table JA_CN_DFF_ASSIGNMENTS to create a new record
36 --
37 -- PARAMETERS:
38 -- In: p_application_id Application ID
39 -- p_dff_name Descriptive Flexfield Name
40 -- p_dff_title_code DFF title lookup code
41 -- p_context_code DFF context
42 -- p_attribute_column DFF column
43 -- p_creation_date Creation date
44 -- p_created_by Identifier of user that creates
45 -- the record
46 -- p_last_update_date Last update date of the record
47 -- p_last_updated_by Last update by
48 -- p_last_update_login Last update login
49 --
50 -- In Out: p_row_id Row id of a table record
51 --
52 --
53 -- DESIGN REFERENCES:
54 -- CNAO_DFF_ASSIGNMENT_FORM_TD.doc
55 --
56 -- CHANGE HISTORY:
57 --
58 -- 01-MAR-2006 Jackey Li created
59 -- 16-MAy-2007 yanbo liu changed
60 -- add chart_of_accounts_id column.
61 --===========================================================================
62 PROCEDURE Insert_Row(p_row_id IN OUT NOCOPY VARCHAR2
63 ,P_application_id IN NUMBER
64 ,p_dff_name IN VARCHAR2
65 ,p_dff_title_code IN VARCHAR2
66 ,p_context_code IN VARCHAR2
67 ,p_attribute_column IN VARCHAR2
68 ,p_creation_date IN DATE
69 ,p_created_by IN NUMBER
70 ,p_last_update_date IN DATE
71 ,p_last_updated_by IN NUMBER
72 ,p_last_update_login IN NUMBER
73 ,p_chart_of_accounts_id In NUMBER
74 ) IS
75
76 l_procedure_name VARCHAR2(100) := 'Insert_Row';
77 l_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
78 l_proc_level NUMBER := FND_LOG.Level_Procedure;
79
80 CURSOR C IS
81 SELECT ROWID
82 FROM JA_CN_DFF_ASSIGNMENTS
83 WHERE DFF_TITLE_CODE = p_dff_title_code;
84
85 BEGIN
86
87 --log for debug
88 IF (l_proc_level >= l_dbg_level) THEN
89 FND_LOG.STRING(l_proc_level,
90 G_MODULE_PREFIX || '.' || l_procedure_name || '.begin',
91 'Enter procedure');
92 END IF; --( l_proc_level >= l_dbg_level)
93
94 --Insert data into table JA_CN_DFF_ASSIGNMENTS
95 INSERT INTO JA_CN_DFF_ASSIGNMENTS
96 (application_id
97 ,descriptive_flexfield_name
98 ,dff_title_code
99 ,context_code
100 ,attribute_column
101 ,creation_date
102 ,created_by
103 ,last_update_date
104 ,last_updated_by
105 ,last_update_login
106 ,chart_of_accounts_id
107 )
108 VALUES
109 (
110 p_application_id
111 ,p_dff_name
112 ,p_dff_title_code
113 ,p_context_code
114 ,p_attribute_column
115 ,p_creation_date
116 ,p_created_by
117 ,p_last_update_date
118 ,p_last_updated_by
119 ,p_last_update_login
120 ,p_chart_of_accounts_id
121 );
122
123 --In case of insert failed, raise error
124
125
126
127
128 OPEN c;
129 FETCH c
130 INTO p_row_id;
131 IF (c%NOTFOUND) THEN
132 CLOSE c;
133 RAISE NO_DATA_FOUND;
134 END IF; --(c%NOTFOUND)
135 CLOSE C;
136
137 --log for debug
138 IF (l_proc_level >= l_dbg_level) THEN
139 FND_LOG.STRING(l_proc_level,
140 G_MODULE_PREFIX || '.' || l_procedure_name || '.end',
141 'Exit procedure');
142 END IF; --( l_proc_level >= l_dbg_level)
143
144 END Insert_Row;
145
146 --==========================================================================
147 -- PROCEDURE NAME:
148 --
149 -- Update_Row Public
150 --
151 -- DESCRIPTION:
152 --
153 -- This procedure is used to update data in table JA_CN_DFF_ASSIGNMENTS
154 -- according to parameters passed in
155 --
156 -- PARAMETERS:
157 -- In: p_application_id Application ID
158 -- p_dff_name Descriptive Flexfield Name
159 -- p_dff_title_code DFF title lookup code
160 -- p_context_code DFF context
161 -- p_attribute_column DFF column
162 -- p_creation_date Creation date
163 -- p_created_by Identifier of user that creates
164 -- the record
165 -- p_last_update_date Last update date of the record
166 -- p_last_updated_by Last update by
167 -- p_last_update_login Last update login
168 --
169 -- In Out: p_row_id Row id of a table record
170 --
171 --
172 -- DESIGN REFERENCES:
173 -- CNAO_DFF_ASSIGNMENT_FORM_TD.doc
174 --
175 -- CHANGE HISTORY:
176 --
177 -- 01-MAR-2006 Jackey Li created
178 -- 16-MAy-2007 yanbo liu changed
179 -- add chart_of_accounts_id column.
180 --===========================================================================
181 PROCEDURE Update_Row(p_row_id IN OUT NOCOPY VARCHAR2
182 ,p_application_id IN NUMBER
183 ,p_dff_name IN VARCHAR2
184 ,p_dff_title_code IN VARCHAR2
185 ,p_context_code IN VARCHAR2
186 ,p_attribute_column IN VARCHAR2
187 ,p_creation_date IN DATE
188 ,p_created_by IN NUMBER
189 ,p_last_update_date IN DATE
190 ,p_last_updated_by IN NUMBER
191 ,p_last_update_login IN NUMBER
192 ,p_chart_of_accounts_id In NUMBER) IS
193
194 l_procedure_name VARCHAR2(100) := 'Update_Row';
195 l_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
196 l_proc_level NUMBER := FND_LOG.Level_Procedure;
197
198 BEGIN
199
200 --log for debug
201 IF (l_proc_level >= l_dbg_level) THEN
202 FND_LOG.STRING(l_proc_level,
203 G_MODULE_PREFIX || '.' || l_procedure_name || '.begin',
204 'Enter procedure');
205 END IF; --( l_proc_level >= l_dbg_level)
206
207 --Update data on table JA_CN_DFF_ASSIGNMENTS
208 UPDATE JA_CN_DFF_ASSIGNMENTS
209 SET application_id = p_application_id,
210 descriptive_flexfield_name = p_dff_name,
211 dff_title_code = p_dff_title_code,
212 context_code = p_context_code,
213 attribute_column = p_attribute_column,
214 creation_date = p_creation_date,
215 created_by = p_created_by,
216 last_update_date = p_last_update_date,
217 last_updated_by = p_last_updated_by,
218 last_update_login = p_last_update_login,
219 chart_of_accounts_id = p_chart_of_accounts_id
220 WHERE ROWID = p_row_id;
221
222 --In case of update failed, raise error
223 IF (SQL%NOTFOUND) THEN
224 RAISE NO_DATA_FOUND;
225 END IF; --(SQL%NOTFOUND)
226
227 --log for debug
228 IF (l_proc_level >= l_dbg_level) THEN
229 FND_LOG.STRING(l_proc_level,
230 G_MODULE_PREFIX || '.' || l_procedure_name || '.end',
231 'Exit procedure');
232 END IF; --( l_proc_level >= l_dbg_level)
233
234 END Update_Row;
235
236 --==========================================================================
237 -- PROCEDURE NAME:
238 --
239 -- Lock_Row Public
240 --
241 -- DESCRIPTION:
242 --
243 -- This procedure is used to implement lock on row level on table
244 -- JA_CN_DFF_ASSIGNMENTS
245 --
246 -- PARAMETERS:
247 -- In: p_application_id Application ID
248 -- p_dff_name Descriptive Flexfield Name
249 -- p_dff_title_code DFF title lookup code
250 -- p_context_code DFF context
251 -- p_attribute_column DFF column
252 -- p_creation_date Creation date
253 -- p_created_by Identifier of user that creates
254 -- the record
255 -- p_last_update_date Last update date of the record
256 -- p_last_updated_by Last update by
257 -- p_last_update_login Last update login
258 --
259 -- In Out: p_row_id Row id of a table record
260 --
261 --
262 -- DESIGN REFERENCES:
263 -- CNAO_DFF_ASSIGNMENT_FORM_TD.doc
264 --
265 -- CHANGE HISTORY:
266 --
267 -- 01-MAR-2006 Jackey Li created
268 -- 16-MAy-2007 yanbo liu changed
269 -- add chart_of_accounts_id column.
270 --===========================================================================
271 PROCEDURE Lock_Row(p_row_id IN OUT NOCOPY VARCHAR2
272 ,p_application_id IN NUMBER
273 ,p_dff_name IN VARCHAR2
274 ,p_dff_title_code IN VARCHAR2
275 ,p_context_code IN VARCHAR2
276 ,p_attribute_column IN VARCHAR2
277 ,p_creation_date IN DATE
278 ,p_created_by IN NUMBER
279 ,p_last_update_date IN DATE
280 ,p_last_updated_by IN NUMBER
281 ,p_last_update_login IN NUMBER
282 ,p_chart_of_accounts_id In NUMBER) IS
283
284 l_procedure_name VARCHAR2(100) := 'Lock_Row';
285 l_dbg_level NUMBER := FND_LOG.G_Current_Runtime_Level;
286 l_proc_level NUMBER := FND_LOG.Level_Procedure;
287
288 CURSOR c IS
289 SELECT *
290 FROM JA_CN_DFF_ASSIGNMENTS
291 WHERE ROWID = p_row_id
292 FOR UPDATE OF dff_title_code NOWAIT;
293
294 recinfo c%ROWTYPE;
295
296 BEGIN
297
298 --log for debug
299 IF (l_proc_level >= l_dbg_level) THEN
300 FND_LOG.STRING(l_proc_level,
301 G_MODULE_PREFIX || '.' || l_procedure_name || '.begin',
302 'Begin procedure');
303 END IF; --( l_proc_level >= l_dbg_level)
304
305 IF p_dff_title_code IS NOT NULL THEN
306 --If a record has been deleted as form tries to excute dml operation
307 --on that record,then raise error to form
308 OPEN c;
309 FETCH c
310 INTO recinfo;
311 IF (c%NOTFOUND) THEN
312 CLOSE c;
313 FND_MESSAGE.Set_Name('FND',
314 'FORM_RECORD_DELETED');
315 APP_EXCEPTION.Raise_Exception;
316 END IF; --(c%NOTFOUND)
317 CLOSE c;
318
319 --To judge if a record has been changed by other programs as the form
320 --tries to execute DML operation on that record,if 'Yes', raise error,
321 --else the form will be able to do DML operation on the record.
322 IF ((recinfo.application_id = p_application_id) AND
323 (recinfo.chart_of_accounts_id = p_chart_of_accounts_id) AND
324 (rtrim(recinfo.descriptive_flexfield_name) = p_dff_name) AND
325 (recinfo.dff_title_code = p_dff_title_code) AND
326 ((rtrim(recinfo.context_code) = p_context_code) OR
327 ((rtrim(recinfo.context_code) IS NULL) AND
328 (p_context_code IS NULL))) AND
329 ((rtrim(recinfo.attribute_column) = p_attribute_column) OR
330 ((rtrim(recinfo.attribute_column) IS NULL) AND
331 (p_attribute_column IS NULL)))) THEN
332 RETURN;
333 ELSE
334 FND_MESSAGE.Set_Name('FND',
335 'FORM_RECORD_CHANGED');
336 APP_EXCEPTION.Raise_Exception;
337 END IF; --((recinfo.ra_gl_date=p_ra_gl_date) ...
338 END IF; --IF p_dff_title_code IS NULL
339
340 --log for debug
341 IF (l_proc_level >= l_dbg_level) THEN
342 FND_LOG.STRING(l_proc_level,
343 G_MODULE_PREFIX || '.' || l_procedure_name || '.end',
344 'Exit procedure');
345 END IF; --( l_proc_level >= l_dbg_level)
346 END Lock_Row;
347
348 END JA_CN_DFF_ASSIGNMENTS_PKG;
[Home] [Help]