[Home] [Help]
PACKAGE BODY: APPS.IGI_ITR_CHARGE_LINES_PKG
Source
1 PACKAGE BODY IGI_ITR_CHARGE_LINES_PKG as
2 -- $Header: igiitrtb.pls 120.5.12000000.1 2007/09/12 10:32:50 mbremkum ship $
3 --
4
5
6 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7 l_state_level number := FND_LOG.LEVEL_STATEMENT;
8 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
9 l_event_level number := FND_LOG.LEVEL_EVENT;
10 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
11 l_error_level number := FND_LOG.LEVEL_ERROR;
12 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
13
14
15 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
16 X_It_Service_Line_Id IN OUT NOCOPY NUMBER,
17 X_It_Header_Id NUMBER,
18 X_It_Line_Num NUMBER,
19 X_Set_Of_Books_Id NUMBER,
20 X_Receiving_Ccid NUMBER,
21 X_Creation_Ccid NUMBER,
22 X_Charge_Center_Id NUMBER,
23 X_Charge_Service_Id NUMBER,
24 X_Service_Id NUMBER,
25 X_Crea_Cost_Center VARCHAR2,
26 X_Crea_Conf_Segment_Value VARCHAR2,
27 X_Recv_Cost_Center VARCHAR2,
28 X_Recv_Conf_Segment_Value VARCHAR2,
29 X_Effective_Date DATE,
30 X_Entered_Dr NUMBER,
31 X_Entered_Cr NUMBER,
32 X_Description VARCHAR2,
33 X_Status_Flag VARCHAR2,
34 X_Posting_Flag VARCHAR2,
35 X_Submit_Date DATE,
36 X_Suggested_Amount NUMBER,
37 X_Rejection_Note VARCHAR2,
38 X_Failed_Funds_Lookup_code VARCHAR2,
39 X_Encumbrance_Flag VARCHAR2,
40 X_Encumbered_Amount NUMBER,
41 X_Unencumbered_Amount NUMBER,
42 X_Gl_Encumbered_Date DATE,
43 X_Gl_Encumbered_Period_Name VARCHAR2,
44 X_Gl_Cancelled_Date DATE,
45 X_Prevent_Encumbrance_Flag VARCHAR2,
46 X_Je_Header_Id NUMBER,
47 X_Receiver_Id NUMBER,
48 X_Charge_Range_Id NUMBER,
49 X_Creation_Date DATE,
50 X_Created_By NUMBER,
51 X_Last_Update_Login NUMBER,
52 X_Last_Update_Date DATE,
53 X_Last_Updated_By NUMBER
54 ) IS
55
56 CURSOR C IS SELECT rowid
57 FROM igi_itr_charge_lines
58 WHERE it_service_line_id = X_It_Service_Line_Id;
59
60 CURSOR C2 IS SELECT igi_itr_charge_lines_s.nextval FROM sys.dual;
61
62 BEGIN
63
64 IF X_It_Service_Line_Id is null THEN
65 OPEN C2;
66 FETCH C2 INTO X_It_Service_Line_Id;
67 CLOSE C2;
68 END IF;
69
70
71 INSERT INTO igi_itr_charge_lines(
72 it_service_line_id
73 ,it_header_id
74 ,it_line_num
75 ,set_of_books_id
76 ,receiving_code_combination_id
77 ,creation_code_combination_id
78 ,charge_center_id
79 ,charge_service_id
80 ,service_id
81 ,crea_cost_center
82 ,crea_conf_segment_value
83 ,recv_cost_center
84 ,recv_conf_segment_value
85 ,effective_date
86 ,entered_dr
87 ,entered_cr
88 ,description
89 ,status_flag
90 ,posting_flag
91 ,submit_date
92 ,suggested_amount
93 ,rejection_note
94 ,failed_funds_lookup_code
95 ,encumbrance_flag
96 ,encumbered_amount
97 ,unencumbered_amount
98 ,gl_encumbered_date
99 ,gl_encumbered_period_name
100 ,gl_cancelled_date
101 ,prevent_encumbrance_flag
102 ,je_header_id
103 ,receiver_id
104 ,charge_range_id
105 ,creation_date
106 ,created_by
107 ,last_update_login
108 ,last_update_date
109 ,last_updated_by
110 ) VALUES (
111 X_It_Service_Line_Id
112 ,X_It_Header_Id
113 ,X_It_Line_Num
114 ,X_Set_Of_Books_Id
115 ,X_Receiving_Ccid
116 ,X_Creation_Ccid
117 ,X_Charge_Center_Id
118 ,X_Charge_Service_Id
119 ,X_Service_Id
120 ,X_Crea_Cost_Center
121 ,X_Crea_Conf_Segment_Value
122 ,X_Recv_Cost_Center
123 ,X_Recv_Conf_Segment_Value
124 ,X_Effective_Date
125 ,X_Entered_Dr
126 ,X_Entered_Cr
127 ,X_Description
128 ,X_Status_Flag
129 ,X_Posting_Flag
130 ,X_Submit_Date
131 ,X_Suggested_Amount
132 ,X_Rejection_Note
133 ,X_Failed_Funds_Lookup_code
134 ,X_Encumbrance_Flag
135 ,X_Encumbered_Amount
136 ,X_Unencumbered_Amount
137 ,X_Gl_Encumbered_Date
138 ,X_Gl_Encumbered_Period_Name
139 ,X_Gl_Cancelled_Date
140 ,X_Prevent_Encumbrance_Flag
141 ,X_Je_Header_Id
142 ,X_Receiver_Id
143 ,X_Charge_Range_Id
144 ,X_Creation_Date
145 ,X_Created_By
146 ,X_Last_Update_Login
147 ,X_Last_Update_Date
148 ,X_Last_Updated_By
149 );
150
151 OPEN C;
152 FETCH C INTO X_Rowid;
153 IF (C%NOTFOUND) THEN
154 CLOSE C;
155 RAISE NO_DATA_FOUND;
156 END IF;
157 CLOSE C;
158
159 END Insert_Row;
160
161
162 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
163 X_It_Service_Line_Id NUMBER,
164 X_It_Header_Id NUMBER,
165 X_It_Line_Num NUMBER,
166 X_Set_Of_Books_Id NUMBER,
167 X_Receiving_Ccid NUMBER,
168 X_Creation_Ccid NUMBER,
169 X_Charge_Center_Id NUMBER,
170 X_Charge_Service_Id NUMBER,
171 X_Service_Id NUMBER,
172 X_Crea_Cost_Center VARCHAR2,
173 X_Crea_Conf_Segment_Value VARCHAR2,
174 X_Recv_Cost_Center VARCHAR2,
175 X_Recv_Conf_Segment_Value VARCHAR2,
176 X_Effective_Date DATE,
177 X_Entered_Dr NUMBER,
178 X_Entered_Cr NUMBER,
179 X_Description VARCHAR2,
180 X_Status_Flag VARCHAR2,
181 X_Posting_Flag VARCHAR2,
182 X_Submit_Date DATE,
183 X_Suggested_Amount NUMBER,
184 X_Rejection_Note VARCHAR2,
185 X_Failed_Funds_Lookup_code VARCHAR2,
186 X_Encumbrance_Flag VARCHAR2,
187 X_Encumbered_Amount NUMBER,
188 X_Unencumbered_Amount NUMBER,
189 X_Gl_Encumbered_Date DATE,
190 X_Gl_Encumbered_Period_Name VARCHAR2,
191 X_Gl_Cancelled_Date DATE,
192 X_Prevent_Encumbrance_Flag VARCHAR2,
193 X_Je_Header_Id NUMBER,
194 X_Receiver_Id NUMBER,
195 X_Charge_Range_Id NUMBER
196 ) IS
197
198
199 CURSOR C IS
200 SELECT *
201 FROM igi_itr_charge_lines
202 WHERE rowid = X_Rowid
203 FOR UPDATE of it_service_line_id NOWAIT;
204
205 Recinfo C%ROWTYPE;
206
207
208 BEGIN
209
210 OPEN C;
211 FETCH C INTO Recinfo;
212 IF (C%NOTFOUND) THEN
213 CLOSE C;
214 fnd_message.set_name('FND','FORM_RECORD_DELETED');
215 IF( l_excep_level >= l_debug_level) THEN
216 FND_LOG.MESSAGE(l_excep_level,'igi.plsql.igiitrtb.IGI_ITR_CHARGE_LINES_PKG.lock_row.msg1', FALSE);
217 END IF;
218 app_exception.raise_exception;
219 END IF;
220 CLOSE C;
221 IF (
222 (Recinfo.it_service_line_id = X_It_Service_Line_Id)
223 AND (Recinfo.it_header_id = X_It_Header_Id)
224 AND (Recinfo.it_line_num = X_It_Line_Num)
225 AND ( (Recinfo.set_of_books_id = X_Set_Of_Books_Id)
226 OR ( (Recinfo.set_of_books_id IS NULL)
227 AND (X_Set_Of_Books_Id IS NULL)))
228 AND (Recinfo.receiving_code_combination_id = X_Receiving_Ccid)
229 AND (Recinfo.creation_code_combination_id = X_Creation_Ccid)
230 AND (Recinfo.charge_center_id = X_Charge_Center_Id)
231 AND (Recinfo.charge_service_id = X_Charge_Service_Id)
232 AND ( (Recinfo.service_id = X_Service_Id)
233 OR ( (Recinfo.service_id IS NULL)
234 AND (X_Service_Id IS NULL)))
235 AND ( (Recinfo.crea_cost_center = X_Crea_Cost_Center)
236 OR ( (Recinfo.crea_cost_center IS NULL)
237 AND (X_Crea_Cost_Center IS NULL)))
238 AND ( (Recinfo.crea_conf_segment_value = X_Crea_Conf_Segment_Value)
239 OR ( (Recinfo.crea_conf_segment_value IS NULL)
240 AND (X_Crea_Conf_Segment_Value IS NULL)))
241 AND ( (Recinfo.recv_cost_center = X_Recv_Cost_Center)
242 OR ( (Recinfo.recv_cost_center IS NULL)
243 AND (X_Recv_Cost_Center IS NULL)))
244 AND ( (Recinfo.recv_conf_segment_value = X_Recv_Conf_Segment_Value)
245 OR ( (Recinfo.recv_conf_segment_value IS NULL)
246 AND (X_Recv_Conf_Segment_Value IS NULL)))
247 AND ( (Recinfo.effective_date = X_Effective_Date)
248 OR ( (Recinfo.effective_date IS NULL)
249 AND (X_Effective_Date IS NULL)))
250 AND ( (Recinfo.entered_dr = X_Entered_Dr)
251 OR ( (Recinfo.entered_dr IS NULL)
252 AND (X_Entered_Dr IS NULL)))
253 AND ( (Recinfo.entered_cr = X_Entered_Cr)
254 OR ( (Recinfo.entered_cr IS NULL)
255 AND (X_Entered_Cr IS NULL)))
256 AND ( (Recinfo.description = X_Description)
257 OR ( (Recinfo.description IS NULL)
258 AND (X_Description Is NULL)))
259 AND ( (Recinfo.status_flag = X_Status_Flag)
260 OR ( (Recinfo.status_flag IS NULL)
261 AND (X_Status_Flag IS NULL)))
262 AND ( (Recinfo.posting_flag = X_Posting_Flag)
263 OR ( (Recinfo.posting_flag IS NULL)
264 AND (X_Posting_Flag IS NULL)))
265 AND ( (Recinfo.submit_date = X_Submit_Date)
266 OR ( (Recinfo.submit_date IS NULL)
267 AND (X_Submit_Date IS NULL)))
268 AND ( (Recinfo.suggested_amount = X_Suggested_Amount)
269 OR ( (Recinfo.suggested_amount IS NULL)
270 AND (X_Suggested_Amount IS NULL)))
271 AND ( (Recinfo.rejection_note = X_Rejection_Note)
272 OR ( (Recinfo.rejection_note IS NULL)
273 AND (X_Rejection_Note IS NULL)))
274 AND ( (Recinfo.failed_funds_lookup_code = X_Failed_Funds_Lookup_Code)
275 OR ( (Recinfo.failed_funds_lookup_code IS NULL)
276 AND (X_Failed_Funds_Lookup_Code IS NULL)))
277 AND ( (Recinfo.encumbrance_flag = X_Encumbrance_Flag)
278 OR ( (Recinfo.encumbrance_flag IS NULL)
279 AND (X_Encumbrance_Flag IS NULL)))
280 AND ( (Recinfo.encumbered_amount = X_Encumbered_Amount)
281 OR ( (Recinfo.encumbered_amount IS NULL)
282 AND (X_Encumbered_Amount IS NULL)))
283 AND ( (Recinfo.unencumbered_amount = X_Unencumbered_Amount)
284 OR ( (Recinfo.unencumbered_amount IS NULL)
285 AND (X_Unencumbered_Amount IS NULL)))
286 AND ( (Recinfo.gl_encumbered_date = X_Gl_Encumbered_Date)
287 OR ( (Recinfo.gl_encumbered_date IS NULL)
288 AND (X_Gl_Encumbered_Date IS NULL)))
289 AND ( (Recinfo.gl_encumbered_period_name = X_Gl_Encumbered_Period_Name)
290 OR ( (Recinfo.gl_encumbered_period_name IS NULL)
291 AND (X_Gl_Encumbered_Period_Name IS NULL)))
292 AND ( (Recinfo.gl_cancelled_date = X_Gl_Cancelled_Date)
293 OR ( (Recinfo.gl_cancelled_date IS NULL)
294 AND (X_Gl_Cancelled_Date IS NULL)))
295 AND ( (Recinfo.prevent_encumbrance_flag = X_Prevent_Encumbrance_Flag)
296 OR ( (Recinfo.prevent_encumbrance_flag IS NULL)
297 AND (X_Prevent_Encumbrance_Flag IS NULL)))
298 AND ( (Recinfo.je_header_id = X_Je_Header_Id)
299 OR ( (Recinfo.je_header_id IS NULL)
300 AND (X_Je_Header_Id IS NULL)))
301 AND ( (Recinfo.receiver_id = X_Receiver_Id)
302 OR ( (Recinfo.receiver_id IS NULL)
303 AND (X_Receiver_Id IS NULL)))
304 AND ( (Recinfo.charge_range_id = X_Charge_Range_Id)
305 OR ( (Recinfo.charge_range_id IS NULL)
306 AND (X_Charge_Range_Id IS NULL)))
307 ) THEN
308 return;
309 ELSE
310 fnd_message.set_name('FND','FORM_RECORD_CHANGED');
311 IF( l_excep_level >= l_debug_level) THEN
312 FND_LOG.MESSAGE(l_excep_level,'igi.plsql.igiitrtb.IGI_ITR_CHARGE_LINES_PKG.lock_row.msg2', FALSE);
313 END IF;
314 APP_EXCEPTION.raise_exception;
315 END IF;
316
317 END Lock_Row;
318
319
320
321 PROCEDURE Update_Row(X_Rowid VARCHAR2,
322 X_It_Service_Line_Id NUMBER,
323 X_It_Header_Id NUMBER,
324 X_It_Line_Num NUMBER,
325 X_Set_Of_Books_Id NUMBER,
326 X_Receiving_Ccid NUMBER,
327 X_Creation_Ccid NUMBER,
328 X_Charge_Center_Id NUMBER,
329 X_Charge_Service_Id NUMBER,
330 X_Service_Id NUMBER,
331 X_Crea_Cost_Center VARCHAR2,
332 X_Crea_Conf_Segment_Value VARCHAR2,
333 X_Recv_Cost_Center VARCHAR2,
334 X_Recv_Conf_Segment_Value VARCHAR2,
335 X_Effective_Date DATE,
336 X_Entered_Dr NUMBER,
337 X_Entered_Cr NUMBER,
338 X_Description VARCHAR2,
339 X_Status_Flag VARCHAR2,
340 X_Posting_Flag VARCHAR2,
341 X_Submit_Date DATE,
342 X_Suggested_Amount NUMBER,
343 X_Rejection_Note VARCHAR2,
344 X_Failed_Funds_Lookup_code VARCHAR2,
345 X_Encumbrance_Flag VARCHAR2,
346 X_Encumbered_Amount NUMBER,
347 X_Unencumbered_Amount NUMBER,
348 X_Gl_Encumbered_Date DATE,
349 X_Gl_Encumbered_Period_Name VARCHAR2,
350 X_Gl_Cancelled_Date DATE,
351 X_Prevent_Encumbrance_Flag VARCHAR2,
352 X_Je_Header_Id NUMBER,
353 X_Receiver_Id NUMBER,
354 X_Charge_Range_Id NUMBER,
355 X_Last_Update_Login NUMBER,
356 X_Last_Update_Date DATE,
357 X_Last_Updated_By NUMBER
358 ) IS
359 BEGIN
360
361 UPDATE igi_itr_charge_lines
362 SET
363 it_service_line_id = X_It_Service_Line_Id,
364 it_header_id = X_It_Header_Id,
365 it_line_num = X_It_Line_Num,
366 set_of_books_id = X_Set_Of_Books_Id,
367 receiving_code_combination_id = X_Receiving_Ccid,
368 creation_code_combination_id = X_Creation_Ccid,
369 charge_center_id = X_Charge_Center_Id,
370 charge_service_id = X_Charge_Service_Id,
371 service_id = X_Service_Id,
372 crea_cost_center = X_Crea_Cost_Center,
373 crea_conf_segment_value = X_Crea_Conf_Segment_Value,
374 recv_cost_center = X_Recv_Cost_Center,
375 recv_conf_segment_value = X_Recv_Conf_Segment_Value,
376 effective_date = X_Effective_Date,
377 entered_dr = X_Entered_Dr,
378 entered_cr = X_Entered_Cr,
379 description = X_Description,
380 status_flag = X_Status_Flag,
381 posting_flag = X_Posting_Flag,
382 submit_date = X_Submit_Date,
383 suggested_amount = X_Suggested_Amount,
384 rejection_note = X_Rejection_Note,
385 failed_funds_lookup_code = X_Failed_Funds_Lookup_Code,
386 encumbrance_flag = X_Encumbrance_Flag,
387 encumbered_amount = X_Encumbered_Amount,
388 unencumbered_amount = X_Unencumbered_Amount,
389 gl_encumbered_date = X_Gl_Encumbered_Date,
390 gl_encumbered_period_name = X_GL_Encumbered_Period_Name,
391 gl_cancelled_date = X_Gl_Cancelled_Date,
392 prevent_encumbrance_flag = X_Prevent_Encumbrance_Flag,
393 je_header_id = X_Je_Header_Id,
394 receiver_id = X_Receiver_Id,
395 charge_range_id = X_Charge_Range_Id,
396 last_update_login = X_Last_Update_Login,
397 last_update_date = X_Last_Update_Date,
398 last_updated_by = X_Last_Updated_By
399 WHERE rowid = X_Rowid;
400
401 IF SQL%NOTFOUND THEN
402 raise NO_DATA_FOUND;
403 END IF;
404
405 END Update_Row;
406
407
408
409 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
410 BEGIN
411 DELETE FROM igi_itr_charge_lines
412 WHERE rowid = X_Rowid;
413
414 IF (SQL%NOTFOUND) THEN
415 raise NO_DATA_FOUND;
416 END IF;
417 END Delete_Row;
418
419
420
421 PROCEDURE check_unique(x_rowid VARCHAR2,
422 x_it_line_num NUMBER,
423 x_it_header_id NUMBER) IS
424
425 CURSOR c_dup IS
426 SELECT 'Duplicate'
427 FROM igi_itr_charge_lines cl
428 WHERE cl.it_line_num = x_it_line_num
429 AND cl.it_header_id = x_it_header_id
430 AND (x_rowid IS NULL
431 OR
432 cl.rowid <> x_rowid);
433
434 dummy VARCHAR2(30);
435
436 BEGIN
437
438 OPEN c_dup;
439 FETCH c_dup INTO dummy;
440
441 IF c_dup%FOUND THEN
442 CLOSE c_dup;
443 fnd_message.set_name('IGI','IGI_ITR_DPL_LINE_NO');
444 IF( l_error_level >= l_debug_level) THEN
445 FND_LOG.MESSAGE(l_error_level,'igi.plsql.igiitrtb.IGI_ITR_CHARGE_LINES_PKG.check_unique.msg3', FALSE);
446 END IF;
447 app_exception.raise_exception;
448 END IF;
449
450 CLOSE c_dup;
451
452 END check_unique;
453
454
455 -- OPSF(I) ITR Bug 1764441 22-May-2001 S Brewer Start(1)
456 -- This procedure is called from igi_itr_charge_headers_pkg.delete_row
457 -- to delete all service lines before deleting the charge header
458 PROCEDURE delete_lines(X_It_Header_Id NUMBER) IS
459 BEGIN
460
461 DELETE FROM igi_itr_charge_lines
462 WHERE it_header_id = X_It_Header_Id;
463
464
465 END delete_lines;
466 -- OPSF(I) ITR Bug 1764441 22-May-2001 S Brewer End(1)
467
468
469 END IGI_ITR_CHARGE_LINES_PKG;