[Home] [Help]
PACKAGE BODY: APPS.IGI_ITR_CHARGE_LINES_AUDIT_PKG
Source
1 PACKAGE BODY IGI_ITR_CHARGE_LINES_AUDIT_PKG as
2 -- $Header: igiitrqb.pls 120.5.12000000.1 2007/09/12 10:32:25 mbremkum ship $
3 --
4
5 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6 l_state_level number := FND_LOG.LEVEL_STATEMENT;
7 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
8 l_event_level number := FND_LOG.LEVEL_EVENT;
9 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
10 l_error_level number := FND_LOG.LEVEL_ERROR;
11 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
12
13
14 PROCEDURE Insert_Row(X_It_Service_Line_Id IN OUT NOCOPY NUMBER,
15 X_It_Header_Id NUMBER,
16 X_It_Line_Num NUMBER,
17 X_Set_Of_Books_Id NUMBER,
18 X_Charge_Center_Id NUMBER,
19 X_Effective_Date DATE,
20 X_Entered_Dr NUMBER,
21 X_Entered_Cr NUMBER,
22 X_Description VARCHAR2,
23 X_Status_Flag VARCHAR2,
24 X_Posting_Flag VARCHAR2,
25 X_Submit_Date DATE,
26 X_Suggested_Amount NUMBER,
27 X_Rejection_Note VARCHAR2,
28 X_Receiving_Ccid NUMBER,
29 X_Creation_Ccid NUMBER,
30 X_Charge_Service_Id NUMBER,
31 X_Failed_Funds_Lookup_Code VARCHAR2,
32 X_Encumbrance_Flag VARCHAR2,
33 X_Encumbered_Amount NUMBER,
34 X_Unencumbered_Amount NUMBER,
35 X_Gl_Encumbered_Date DATE,
36 X_Gl_Encumbered_Period_Name VARCHAR2,
37 X_Gl_Cancelled_Date DATE,
38 X_Prevent_Encumbrance_Flag VARCHAR2,
39 X_Je_Header_Id NUMBER,
40 X_Reversal_Flag VARCHAR2,
41 X_Creation_Date DATE,
42 X_Created_By NUMBER,
43 X_Last_Update_Login NUMBER,
44 X_Last_Update_Date DATE,
45 X_Last_Updated_By NUMBER
46 ) IS
47 /*
48 CURSOR C IS SELECT rowid
49 FROM igi_itr_charge_lines_audit
50 WHERE it_service_line_id = X_It_Service_Line_Id;
51 */
52
53
54 BEGIN
55
56
57 INSERT INTO igi_itr_charge_lines_audit(
58 it_service_line_id,
59 it_header_id,
60 it_line_num,
61 set_of_books_id,
62 charge_center_id,
63 effective_date,
64 entered_dr,
65 entered_cr,
66 description,
67 status_flag,
68 posting_flag,
69 submit_date,
70 suggested_amount,
71 rejection_note,
72 receiving_code_combination_id,
73 creation_code_combination_id,
74 charge_service_id,
75 failed_funds_lookup_code,
76 encumbrance_flag,
77 encumbered_amount,
78 unencumbered_amount,
79 gl_encumbered_date,
80 gl_encumbered_period_name,
81 gl_cancelled_date,
82 prevent_encumbrance_flag,
83 je_header_id,
84 reversal_flag,
85 creation_date,
86 created_by,
87 last_update_login,
88 last_update_date,
89 last_updated_by
90 ) VALUES (
91 X_It_Service_Line_Id,
92 X_It_Header_Id,
93 X_It_Line_Num,
94 X_Set_Of_Books_Id,
95 X_Charge_Center_Id,
96 X_Effective_Date,
97 X_Entered_Dr,
98 X_Entered_Cr,
99 X_Description,
100 X_Status_Flag,
101 X_Posting_Flag,
102 X_Submit_Date,
103 X_Suggested_Amount,
104 X_Rejection_Note,
105 X_Receiving_Ccid,
106 X_Creation_Ccid,
107 X_Charge_Service_Id,
108 X_Failed_Funds_Lookup_Code,
109 X_Encumbrance_Flag,
110 X_Encumbered_Amount,
111 X_Unencumbered_Amount,
112 X_Gl_Encumbered_Date,
113 X_Gl_Encumbered_Period_Name,
114 X_Gl_Cancelled_Date,
115 X_Prevent_Encumbrance_Flag,
116 X_Je_Header_Id,
117 X_Reversal_Flag,
118 X_Creation_Date,
119 X_Created_By,
120 X_Last_Update_Login,
121 X_Last_Update_Date,
122 X_Last_Updated_By
123 );
124 /*****
125 OPEN C;
126 FETCH C INTO X_Rowid;
127 IF (C%NOTFOUND) THEN
128 CLOSE C;
129 RAISE NO_DATA_FOUND;
130 END IF;
131 CLOSE C;
132 ****/
133
134 END Insert_Row;
135
136
137 PROCEDURE Lock_Row(X_It_Service_Line_Id IN OUT NOCOPY NUMBER,
138 X_It_Header_Id NUMBER,
139 X_It_Line_Num NUMBER,
140 X_Set_Of_Books_Id NUMBER,
141 X_Charge_Center_Id NUMBER,
142 X_Effective_Date DATE,
143 X_Entered_Dr NUMBER,
144 X_Entered_Cr NUMBER,
145 X_Description VARCHAR2,
146 X_Status_Flag VARCHAR2,
147 X_Posting_Flag VARCHAR2,
148 X_Submit_Date DATE,
149 X_Suggested_Amount NUMBER,
150 X_Rejection_Note VARCHAR2,
151 X_Receiving_Ccid NUMBER,
152 X_Creation_Ccid NUMBER,
153 X_Charge_Service_Id NUMBER,
154 X_Failed_Funds_Lookup_Code VARCHAR2,
155 X_Encumbrance_Flag VARCHAR2,
156 X_Encumbered_Amount NUMBER,
157 X_Unencumbered_Amount NUMBER,
158 X_Gl_Encumbered_Date DATE,
159 X_Gl_Encumbered_Period_Name VARCHAR2,
160 X_Gl_Cancelled_Date DATE,
161 X_Prevent_Encumbrance_Flag VARCHAR2,
162 X_Je_Header_Id NUMBER,
163 X_Reversal_Flag VARCHAR2
164 ) IS
165
166
167
168 CURSOR C IS
169 SELECT *
170 FROM igi_itr_charge_lines_audit
171 WHERE it_service_line_id = X_It_Service_Line_Id
172 AND reversal_flag = 'N'
173 FOR UPDATE of it_service_line_id NOWAIT;
174
175 Recinfo C%ROWTYPE;
176
177
178 BEGIN
179
180 OPEN C;
181 FETCH C INTO Recinfo;
182 IF (C%NOTFOUND) THEN
183 CLOSE C;
184 fnd_message.set_name('FND','FORM_RECORD_DELETED');
185 IF( l_excep_level >= l_debug_level) THEN
186 FND_LOG.MESSAGE(l_excep_level,'igi.plsql.igiitrqb.IGI_ITR_CHARGE_LINES_AUDIT_PKG.lock_row.msg1', FALSE);
187 END IF;
188 app_exception.raise_exception;
189 END IF;
190 CLOSE C;
191
192 IF (
193 ( (Recinfo.it_service_line_id = X_It_Service_Line_Id)
194 OR ( (Recinfo.it_service_line_id IS NULL)
195 AND (X_It_Service_Line_Id IS NULL)))
196 AND ( (Recinfo.it_header_id = X_It_Header_Id)
197 OR ( (Recinfo.it_header_id IS NULL)
198 AND (X_It_Header_Id IS NULL)))
199 AND ( (Recinfo.it_line_num = X_It_Line_Num)
200 OR ( (Recinfo.it_line_num IS NULL)
201 AND (X_It_Line_Num IS NULL)))
202 AND ( (Recinfo.set_of_books_id = X_Set_Of_Books_Id)
203 OR ( (Recinfo.set_of_books_id IS NULL)
204 AND (X_Set_Of_Books_Id IS NULL)))
205 AND ( (Recinfo.charge_center_id = X_Charge_Center_Id)
206 OR ( (Recinfo.charge_center_id IS NULL)
207 AND (X_Charge_Center_Id IS NULL)))
208 AND ( (Recinfo.effective_date = X_Effective_Date)
209 OR ( (Recinfo.effective_date IS NULL)
210 AND (X_Effective_Date IS NULL)))
211 AND ( (Recinfo.entered_dr = X_Entered_Dr)
212 OR ( (Recinfo.entered_dr IS NULL)
213 AND (X_Entered_Dr IS NULL)))
214 AND ( (Recinfo.entered_cr = X_Entered_Cr)
215 OR ( (Recinfo.entered_cr IS NULL)
216 AND (X_Entered_Cr IS NULL)))
217 AND ( (Recinfo.description = X_Description)
218 OR ( (Recinfo.description IS NULL)
219 AND (X_Description IS NULL)))
220 AND ( (Recinfo.status_flag = X_Status_Flag)
221 OR ( (Recinfo.status_flag IS NULL)
222 AND (X_Status_Flag IS NULL)))
223 AND ( (Recinfo.posting_flag = X_Posting_Flag)
224 OR ( (Recinfo.posting_flag IS NULL)
225 AND (X_Posting_Flag IS NULL)))
226 AND ( (Recinfo.Submit_Date = X_Submit_Date)
227 OR ( (Recinfo.submit_date IS NULL)
228 AND (X_Submit_Date IS NULL)))
229 AND ( (Recinfo.Suggested_Amount = X_Suggested_Amount)
230 OR ( (Recinfo.suggested_amount IS NULL)
231 AND (X_Suggested_Amount IS NULL)))
232 AND ( (Recinfo.Rejection_Note = X_Rejection_Note)
233 OR ( (Recinfo.rejection_note IS NULL)
234 AND (X_Rejection_Note IS NULL)))
235 AND ( (Recinfo.receiving_code_combination_id = X_Receiving_Ccid)
236 OR ( (Recinfo.receiving_code_combination_id IS NULL)
237 AND (X_Receiving_Ccid IS NULL)))
238 AND ( (Recinfo.creation_code_combination_id = X_Creation_Ccid)
239 OR ( (Recinfo.creation_code_combination_id IS NULL)
240 AND (X_Creation_Ccid IS NULL)))
241 AND ( (Recinfo.charge_service_id = X_Charge_Service_Id)
242 OR ( (Recinfo.charge_service_id IS NULL)
243 AND (X_Charge_Service_Id IS NULL)))
244 AND ( (Recinfo.failed_funds_lookup_code = X_Failed_Funds_Lookup_Code)
245 OR ( (Recinfo.failed_funds_lookup_code IS NULL)
246 AND (X_Failed_Funds_Lookup_Code IS NULL)))
247 AND ( (Recinfo.encumbrance_flag = X_Encumbrance_Flag)
248 OR ( (Recinfo.encumbrance_flag IS NULL)
249 AND (X_Encumbrance_Flag IS NULL)))
250 AND ( (Recinfo.encumbered_amount = X_Encumbered_Amount)
251 OR ( (Recinfo.encumbered_amount IS NULL)
252 AND (X_Encumbered_Amount IS NULL)))
253 AND ( (Recinfo.unencumbered_amount = X_Unencumbered_Amount)
254 OR ( (Recinfo.unencumbered_amount IS NULL)
255 AND (X_Unencumbered_Amount IS NULL)))
256 AND ( (Recinfo.gl_encumbered_date = X_Gl_Encumbered_Date)
257 OR ( (Recinfo.gl_encumbered_date IS NULL)
258 AND (X_Gl_Encumbered_Date IS NULL)))
259 AND ( (Recinfo.gl_encumbered_period_name = X_Gl_Encumbered_Period_Name)
260 OR ( (Recinfo.gl_encumbered_period_name IS NULL)
261 AND (X_Gl_Encumbered_Period_Name IS NULL)))
262 AND ( (Recinfo.gl_cancelled_date = X_Gl_Cancelled_Date)
263 OR ( (Recinfo.gl_cancelled_date IS NULL)
264 AND (X_Gl_Cancelled_Date IS NULL)))
265 AND ( (Recinfo.prevent_encumbrance_flag = X_Prevent_Encumbrance_Flag)
266 OR ( (Recinfo.prevent_encumbrance_flag IS NULL)
267 AND (X_Prevent_Encumbrance_Flag IS NULL)))
268 AND ( (Recinfo.je_header_id = X_Je_Header_Id)
269 OR ( (Recinfo.je_header_id IS NULL)
270 AND (X_Je_Header_Id IS NULL)))
271 AND ( (Recinfo.reversal_flag = X_Reversal_Flag)
272 OR ( (Recinfo.reversal_flag IS NULL)
273 AND (X_Reversal_Flag IS NULL)))
274 ) THEN
275 return;
276 ELSE
277 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
278 IF( l_excep_level >= l_debug_level) THEN
279 FND_LOG.MESSAGE(l_excep_level,'igi.plsql.igiitrqb.IGI_ITR_CHARGE_LINES_AUDIT_PKG.lock_row.msg2', FALSE);
280 END IF;
281 APP_EXCEPTION.raise_exception;
282 END IF;
283
284 END Lock_Row;
285
286
287
288
289
290 PROCEDURE Update_Row(X_It_Service_Line_Id IN OUT NOCOPY NUMBER,
291 X_It_Header_Id NUMBER,
292 X_It_Line_Num NUMBER,
293 X_Set_Of_Books_Id NUMBER,
294 X_Charge_Center_Id NUMBER,
295 X_Effective_Date DATE,
296 X_Entered_Dr NUMBER,
297 X_Entered_Cr NUMBER,
298 X_Description VARCHAR2,
299 X_Status_Flag VARCHAR2,
300 X_Posting_Flag VARCHAR2,
301 X_Submit_Date DATE,
302 X_Suggested_Amount NUMBER,
303 X_Rejection_Note VARCHAR2,
304 X_Receiving_Ccid NUMBER,
305 X_Creation_Ccid NUMBER,
306 X_Charge_Service_Id NUMBER,
307 X_Failed_Funds_Lookup_Code VARCHAR2,
308 X_Encumbrance_Flag VARCHAR2,
309 X_Encumbered_Amount NUMBER,
310 X_Unencumbered_Amount NUMBER,
311 X_Gl_Encumbered_Date DATE,
312 X_Gl_Encumbered_Period_Name VARCHAR2,
313 X_Gl_Cancelled_Date DATE,
314 X_Prevent_Encumbrance_Flag VARCHAR2,
315 X_Je_Header_Id NUMBER,
316 X_Reversal_Flag VARCHAR2,
317 X_Last_Update_Login NUMBER,
318 X_Last_Update_Date DATE,
319 X_Last_Updated_By NUMBER
320 ) IS
321 BEGIN
322
323 UPDATE igi_itr_charge_lines_audit
324 SET
325 it_service_line_id = X_It_Service_Line_Id,
326 it_header_id = X_It_Header_Id,
327 it_line_num = X_It_Line_Num,
328 set_of_books_id = X_Set_Of_Books_Id,
329 charge_center_id = X_Charge_Center_Id,
330 effective_date = X_Effective_Date,
331 entered_dr = X_Entered_Dr,
332 entered_cr = X_Entered_Cr,
333 description = X_Description,
334 status_flag = X_Status_Flag,
335 posting_flag = X_Posting_Flag,
336 submit_date = X_Submit_Date,
337 suggested_amount = X_Suggested_Amount,
338 rejection_note = X_Rejection_Note,
339 receiving_code_combination_id = X_Receiving_Ccid,
340 creation_code_combination_id = X_Creation_Ccid,
341 charge_service_id = X_Charge_Service_Id,
342 failed_funds_lookup_code = X_Failed_Funds_Lookup_Code,
343 encumbrance_flag = X_Encumbrance_Flag,
344 encumbered_amount = X_Encumbered_Amount,
345 unencumbered_amount = X_Unencumbered_Amount,
346 gl_encumbered_date = X_Gl_Encumbered_Date,
347 gl_encumbered_period_name = X_Gl_Encumbered_Period_Name,
348 gl_cancelled_date = X_Gl_Cancelled_Date,
349 prevent_encumbrance_flag = X_Prevent_Encumbrance_Flag,
350 je_header_id = X_Je_Header_Id,
351 reversal_flag = X_Reversal_Flag,
352 last_update_login = X_Last_Update_Login,
353 last_update_date = X_Last_Update_Date,
354 last_updated_by = X_Last_Updated_By
355 WHERE it_service_line_id = X_It_Service_Line_Id
356 AND reversal_flag = 'N';
357
358 IF SQL%NOTFOUND THEN
359 raise NO_DATA_FOUND;
360 END IF;
361
362 END Update_Row;
363
364
365
366 END IGI_ITR_CHARGE_LINES_AUDIT_PKG;