[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_DET_PF_HISTORY_PKG
Source
1 PACKAGE BODY IGC_CC_DET_PF_HISTORY_PKG as
2 /* $Header: IGCCDFHB.pls 120.3.12000000.3 2007/10/19 07:03:44 smannava ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_DET_PF_HISTORY_PKG';
5 g_debug_flag VARCHAR2(1) := 'N' ;
6
7 /* ================================================================================
8 PROCEDURE Insert_Row
9 ===============================================================================*/
10
11 PROCEDURE Insert_Row(
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
16 X_return_status OUT NOCOPY VARCHAR2,
17 X_msg_count OUT NOCOPY NUMBER,
18 X_msg_data OUT NOCOPY VARCHAR2,
19 p_Rowid IN OUT NOCOPY VARCHAR2,
20 p_CC_Det_PF_Line_Id IGC_CC_DET_PF_HISTORY.CC_Det_PF_Line_Id%TYPE,
21 p_CC_Det_PF_Line_Num IGC_CC_DET_PF_HISTORY.CC_Det_PF_Line_Num%TYPE,
22 p_CC_Acct_Line_Id IGC_CC_DET_PF_HISTORY.CC_Acct_Line_Id%TYPE,
23 p_Parent_Acct_Line_Id IGC_CC_DET_PF_HISTORY.Parent_Acct_Line_Id%TYPE,
24 p_Parent_Det_PF_Line_Id IGC_CC_DET_PF_HISTORY.Parent_Det_PF_Line_Id%TYPE,
25 p_Det_PF_Version_Num IGC_CC_DET_PF_HISTORY.CC_Det_PF_Version_Num%TYPE,
26 p_Det_PF_Version_Action IGC_CC_DET_PF_HISTORY.CC_Det_PF_Version_Action%TYPE,
27 p_CC_Det_PF_Entered_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Entered_Amt%TYPE,
28 p_CC_Det_PF_Func_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Func_Amt%TYPE,
29 p_CC_Det_PF_Date IGC_CC_DET_PF_HISTORY.CC_Det_PF_Date%TYPE,
30 p_CC_Det_PF_Billed_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Billed_Amt%TYPE,
31 p_CC_Det_PF_Unbilled_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Unbilled_Amt%TYPE,
32 p_CC_Det_PF_Encmbrnc_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Encmbrnc_Amt%TYPE,
33 p_CC_Det_PF_Encmbrnc_Date IGC_CC_DET_PF_HISTORY.CC_Det_PF_Encmbrnc_Date%TYPE,
34 p_CC_Det_PF_Encmbrnc_Status IGC_CC_DET_PF_HISTORY.CC_Det_PF_Encmbrnc_Status%TYPE,
35 p_Last_Update_Date IGC_CC_DET_PF_HISTORY.Last_Update_Date%TYPE,
36 p_Last_Updated_By IGC_CC_DET_PF_HISTORY.Last_Updated_By%TYPE,
37 p_Last_Update_Login IGC_CC_DET_PF_HISTORY.Last_Update_Login%TYPE,
38 p_Creation_Date IGC_CC_DET_PF_HISTORY.Creation_Date%TYPE,
39 p_Created_By IGC_CC_DET_PF_HISTORY.Created_By%TYPE,
40 p_Attribute1 IGC_CC_DET_PF_HISTORY.Attribute1%TYPE,
41 p_Attribute2 IGC_CC_DET_PF_HISTORY.Attribute2%TYPE,
42 p_Attribute3 IGC_CC_DET_PF_HISTORY.Attribute3%TYPE,
43 p_Attribute4 IGC_CC_DET_PF_HISTORY.Attribute4%TYPE,
44 p_Attribute5 IGC_CC_DET_PF_HISTORY.Attribute5%TYPE,
45 p_Attribute6 IGC_CC_DET_PF_HISTORY.Attribute6%TYPE,
46 p_Attribute7 IGC_CC_DET_PF_HISTORY.Attribute7%TYPE,
47 p_Attribute8 IGC_CC_DET_PF_HISTORY.Attribute8%TYPE,
48 p_Attribute9 IGC_CC_DET_PF_HISTORY.Attribute9%TYPE,
49 p_Attribute10 IGC_CC_DET_PF_HISTORY.Attribute10%TYPE,
50 p_Attribute11 IGC_CC_DET_PF_HISTORY.Attribute11%TYPE,
51 p_Attribute12 IGC_CC_DET_PF_HISTORY.Attribute12%TYPE,
52 p_Attribute13 IGC_CC_DET_PF_HISTORY.Attribute13%TYPE,
53 p_Attribute14 IGC_CC_DET_PF_HISTORY.Attribute14%TYPE,
54 p_Attribute15 IGC_CC_DET_PF_HISTORY.Attribute15%TYPE,
55 p_Context IGC_CC_DET_PF_HISTORY.Context%TYPE,
56 G_FLAG IN OUT NOCOPY VARCHAR2
57 ) IS
58
59 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
60 l_api_version CONSTANT NUMBER := 1.0;
61 l_Application_Id NUMBER;
62 l_MRC_Enabled VARCHAR2(1);
63 l_Conversion_Date DATE;
64 l_Set_Of_Books_Id NUMBER;
65 l_Org_Id NUMBER(15);
66 l_return_status VARCHAR2(1);
67
68 CURSOR C_DET_PF_HST_ROWID IS
69 SELECT Rowid
70 FROM IGC_CC_DET_PF_HISTORY
71 WHERE CC_Det_PF_Line_Id = p_CC_Det_PF_Line_Id
72 AND CC_Det_PF_Version_Num = p_Det_PF_Version_Num;
73
74 CURSOR c_igc_app_id IS
75 SELECT Application_Id
76 FROM FND_APPLICATION
77 WHERE Application_Short_Name = 'IGC';
78
79 CURSOR c_det_pf_info IS
80 SELECT SET_OF_BOOKS_ID,
81 ORG_ID,CONVERSION_DATE
82 FROM IGC_CC_HEADERS ICH,
83 IGC_CC_ACCT_LINES IAL,
84 IGC_CC_DET_PF IDP
85 WHERE ICH.CC_HEADER_ID = IAL.CC_HEADER_ID
86 AND IDP.CC_ACCT_LINE_ID = IAL.CC_ACCT_LINE_ID
87 AND IDP.CC_DET_PF_LINE_ID = p_CC_Det_Pf_Line_Id;
88
89
90 BEGIN
91
92 SAVEPOINT Insert_Row_Pvt ;
93
94 -- -----------------------------------------------------------------
95 -- Ensure that the version requested to be used is correct for
96 -- this API.
97 -- -----------------------------------------------------------------
98 IF NOT FND_API.Compatible_API_Call ( l_api_version,
99 p_api_version,
100 l_api_name,
101 G_PKG_NAME )
102 THEN
103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
104 END IF;
105
106
107 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
108 FND_MSG_PUB.initialize ;
109 END IF;
110
111 X_return_status := FND_API.G_RET_STS_SUCCESS ;
112
113 -- -----------------------------------------------------------------
114 -- Insert the DET PF line History record as requested.
115 -- -----------------------------------------------------------------
116 INSERT
117 INTO IGC_CC_DET_PF_HISTORY
118 (CC_Det_PF_Line_Id,
119 Parent_Det_PF_Line_Id,
120 CC_Acct_Line_Id,
121 Parent_Acct_Line_Id,
122 CC_Det_PF_Line_Num,
123 CC_Det_PF_Version_Num,
124 CC_Det_PF_Version_Action,
125 CC_Det_PF_Entered_Amt,
126 CC_Det_PF_Func_Amt,
127 CC_Det_PF_Date,
128 CC_Det_PF_Billed_Amt,
129 CC_Det_PF_Unbilled_Amt,
130 CC_Det_PF_Encmbrnc_Amt,
131 CC_Det_Pf_Encmbrnc_Date,
132 CC_Det_PF_Encmbrnc_Status,
133 Last_Update_Date,
134 Last_Updated_By,
135 Last_Update_Login,
136 Creation_Date,
137 Created_By,
138 Attribute1,
139 Attribute2,
140 Attribute3,
141 Attribute4,
142 Attribute5,
143 Attribute6,
144 Attribute7,
145 Attribute8,
146 Attribute9,
147 Attribute10,
148 Attribute11,
149 Attribute12,
150 Attribute13,
151 Attribute14,
152 Attribute15,
153 Context
154 )
155 VALUES
156 (p_CC_Det_PF_Line_Id,
157 p_Parent_Det_PF_Line_Id,
158 p_CC_Acct_Line_Id,
159 p_Parent_Acct_Line_Id,
160 p_CC_Det_PF_Line_Num,
161 p_Det_PF_Version_Num,
162 p_Det_PF_Version_Action,
163 p_CC_Det_PF_Entered_Amt,
164 p_CC_Det_PF_Func_Amt,
165 p_CC_Det_PF_Date,
166 p_CC_Det_PF_Billed_Amt,
167 p_CC_Det_PF_Unbilled_Amt,
168 p_CC_Det_PF_Encmbrnc_Amt,
169 p_CC_Det_PF_Encmbrnc_Date,
170 p_CC_Det_PF_Encmbrnc_Status,
171 p_Last_Update_Date,
172 p_Last_Updated_By,
173 p_Last_Update_Login,
174 p_Creation_Date,
175 p_Created_By,
176 p_Attribute1,
177 p_Attribute2,
178 p_Attribute3,
179 p_Attribute4,
180 p_Attribute5,
181 p_Attribute6,
182 p_Attribute7,
183 p_Attribute8,
184 p_Attribute9,
185 p_Attribute10,
186 p_Attribute11,
187 p_Attribute12,
188 p_Attribute13,
189 p_Attribute14,
190 p_Attribute15,
191 p_Context
192 );
193
194 -- -------------------------------------------------------------------
195 -- Obtain the ROWID of the record that was just inserted to return
196 -- to the caller.
197 -- -------------------------------------------------------------------
198
199 OPEN C_DET_PF_HST_ROWID;
200 FETCH C_DET_PF_HST_ROWID
201 INTO p_Rowid;
202
203 IF (C_DET_PF_HST_ROWID%NOTFOUND) THEN
204 RAISE FND_API.G_EXC_ERROR ;
205 END IF;
206
207 CLOSE C_DET_PF_HST_ROWID;
208
209 -- ------------------------------------------------------------------
210 -- Obtain the application ID for IGC to be used for the MRC check
211 -- being enabled for this set of books.
212 -- ------------------------------------------------------------------
213 OPEN c_igc_app_id;
214 FETCH c_igc_app_id
215 INTO l_Application_Id;
216
217 -- ------------------------------------------------------------------
218 -- If the application ID can not be attained then exit the procedure
219 -- ------------------------------------------------------------------
220 IF (c_igc_app_id%NOTFOUND) THEN
221 RAISE FND_API.G_EXC_ERROR;
222 END IF;
223
224 CLOSE c_igc_app_id;
225
226 -- ------------------------------------------------------------------
227 -- Obtain the set of books, org id, and the conversion date for the
228 -- CC Header record that the DET PF line is associated to.
229 -- ------------------------------------------------------------------
230 OPEN c_det_pf_info;
231 FETCH c_det_pf_info
232 INTO l_Set_Of_Books_Id,
233 l_Org_Id,
234 l_Conversion_Date;
235
236 -- ------------------------------------------------------------------
237 -- Exit procedure if the values can not be obtained.
238 -- ------------------------------------------------------------------
239 IF (c_det_pf_info%NOTFOUND) THEN
240 RAISE FND_API.G_EXC_ERROR;
241 END IF;
242
243 CLOSE c_det_pf_info;
244
245 -- ------------------------------------------------------------------
246 -- If the conversion date is NULL then fill in the value with the
247 -- current system date.
248 -- ------------------------------------------------------------------
249
250 gl_mc_info.mrc_enabled(
251 l_Set_Of_Books_Id,
252 101, /*--l_Application_Id, commented for MRC uptake*/
253 l_Org_Id,
254 Null,
255 l_MRC_Enabled);
256
257 -- ------------------------------------------------------------------
258 -- If MRC is enabled for this set of books being used then call the
259 -- handler to insert all reporting set of books into the MRC
260 -- table for the account line inserted.
261 -- ------------------------------------------------------------------
262 IF (l_MRC_Enabled = 'Y') THEN
263
264 -- ------------------------------------------------------------------
265 -- MRC Handler For IGC_CC_DET_PF to insert all MRC records for
266 -- the reporting set of books for this PRIMARY set of books.
267 -- ------------------------------------------------------------------
268 IGC_CC_MC_MAIN_HST_PVT.get_rsobs_DET_PF(
269 l_api_version,
270 FND_API.G_FALSE,
271 FND_API.G_FALSE,
272 p_validation_level,
273 l_return_status,
274 X_msg_count,
275 X_msg_data,
276 p_CC_DET_PF_LINE_ID,
277 l_Set_Of_Books_Id,
278 101, /*--l_Application_Id, commented for MRC uptake*/
279 l_org_Id,
280 NVL(l_Conversion_Date, sysdate),
281 p_CC_DET_PF_FUNC_AMT,
282 p_CC_DET_PF_ENCMBRNC_AMT,
283 p_Det_PF_Version_Num,
284 p_Det_PF_Version_Action
285 );
286
287 -- ------------------------------------------------------------------
288 -- Make sure that the insertion was a success
289 -- ------------------------------------------------------------------
290 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
291 RAISE FND_API.G_EXC_ERROR;
292 END IF;
293
294 END IF;
295
296 -- -----------------------------------------------------------------
297 -- If the records are to be commited in this procedure then
298 -- commit the work now otherwise wait for the caller to do COMMIT.
299 -- -----------------------------------------------------------------
300 IF FND_API.To_Boolean ( p_commit ) THEN
301 COMMIT WORK;
302 END iF;
303
304 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
305 p_data => X_msg_data );
306 RETURN;
307
308 EXCEPTION
309
310 WHEN FND_API.G_EXC_ERROR THEN
311
312 ROLLBACK TO Insert_Row_Pvt ;
313 X_return_status := FND_API.G_RET_STS_ERROR;
314 IF (c_det_pf_hst_rowid%ISOPEN) THEN
315 CLOSE c_det_pf_hst_rowid;
316 END IF;
317 IF (c_igc_app_id%ISOPEN) THEN
318 CLOSE c_igc_app_id;
319 END IF;
320 IF (c_det_pf_info%ISOPEN) THEN
321 CLOSE c_det_pf_info;
322 END IF;
323
324 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
325 p_data => X_msg_data );
326
327 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
328
329 ROLLBACK TO Insert_Row_Pvt ;
330 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
331 IF (c_det_pf_hst_rowid%ISOPEN) THEN
332 CLOSE c_det_pf_hst_rowid;
333 END IF;
334 IF (c_igc_app_id%ISOPEN) THEN
335 CLOSE c_igc_app_id;
336 END IF;
337 IF (c_det_pf_info%ISOPEN) THEN
338 CLOSE c_det_pf_info;
339 END IF;
340
341 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
342 p_data => X_msg_data );
343
344 WHEN OTHERS THEN
345
346 ROLLBACK TO Insert_Row_Pvt ;
347 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
348 IF (c_det_pf_hst_rowid%ISOPEN) THEN
349 CLOSE c_det_pf_hst_rowid;
350 END IF;
351 IF (c_igc_app_id%ISOPEN) THEN
352 CLOSE c_igc_app_id;
353 END IF;
354 IF (c_det_pf_info%ISOPEN) THEN
355 CLOSE c_det_pf_info;
356 END IF;
357
358 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
359 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
360 l_api_name);
361 END if;
362
363 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
364 p_data => X_msg_data );
365
366 END Insert_Row;
367
368 /* ================================================================================
369 PROCEDURE Lock_Row
370 ===============================================================================*/
371
372
373 PROCEDURE Lock_Row(
374 p_api_version IN NUMBER,
375 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
376 p_commit IN VARCHAR2 := FND_API.G_FALSE,
377 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
378 X_return_status OUT NOCOPY VARCHAR2,
379 X_msg_count OUT NOCOPY NUMBER,
380 X_msg_data OUT NOCOPY VARCHAR2,
381 p_Rowid IN OUT NOCOPY VARCHAR2,
382 p_CC_Det_PF_Line_Id IGC_CC_DET_PF_HISTORY.CC_Det_PF_Line_Id%TYPE,
383 p_CC_Det_PF_Line_Num IGC_CC_DET_PF_HISTORY.CC_Det_PF_Line_Num%TYPE,
384 p_CC_Acct_Line_Id IGC_CC_DET_PF_HISTORY.CC_Acct_Line_Id%TYPE,
385 p_Parent_Acct_Line_Id IGC_CC_DET_PF_HISTORY.Parent_Acct_Line_Id%TYPE,
386 p_Parent_Det_PF_Line_Id IGC_CC_DET_PF_HISTORY.Parent_Det_PF_Line_Id%TYPE,
387 p_Det_PF_Version_Num IGC_CC_DET_PF_HISTORY.CC_Det_PF_Version_Num%TYPE,
388 p_Det_PF_Version_Action IGC_CC_DET_PF_HISTORY.CC_Det_PF_Version_Action%TYPE,
389 p_CC_Det_PF_Entered_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Entered_Amt%TYPE,
390 p_CC_Det_PF_Func_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Func_Amt%TYPE,
391 p_CC_Det_PF_Date IGC_CC_DET_PF_HISTORY.CC_Det_PF_Date%TYPE,
392 p_CC_Det_PF_Billed_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Billed_Amt%TYPE,
393 p_CC_Det_PF_Unbilled_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Unbilled_Amt%TYPE,
394 p_CC_Det_PF_Encmbrnc_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Encmbrnc_Amt%TYPE,
395 p_CC_Det_PF_Encmbrnc_Date IGC_CC_DET_PF_HISTORY.CC_Det_PF_Encmbrnc_Date%TYPE,
396 p_CC_Det_PF_Encmbrnc_Status IGC_CC_DET_PF_HISTORY.CC_Det_PF_Encmbrnc_Status%TYPE,
397 p_Last_Update_Date IGC_CC_DET_PF_HISTORY.Last_Update_Date%TYPE,
398 p_Last_Updated_By IGC_CC_DET_PF_HISTORY.Last_Updated_By%TYPE,
399 p_Last_Update_Login IGC_CC_DET_PF_HISTORY.Last_Update_Login%TYPE,
400 p_Creation_Date IGC_CC_DET_PF_HISTORY.Creation_Date%TYPE,
401 p_Created_By IGC_CC_DET_PF_HISTORY.Created_By%TYPE,
402 p_Attribute1 IGC_CC_DET_PF_HISTORY.Attribute1%TYPE,
403 p_Attribute2 IGC_CC_DET_PF_HISTORY.Attribute2%TYPE,
404 p_Attribute3 IGC_CC_DET_PF_HISTORY.Attribute3%TYPE,
405 p_Attribute4 IGC_CC_DET_PF_HISTORY.Attribute4%TYPE,
406 p_Attribute5 IGC_CC_DET_PF_HISTORY.Attribute5%TYPE,
407 p_Attribute6 IGC_CC_DET_PF_HISTORY.Attribute6%TYPE,
408 p_Attribute7 IGC_CC_DET_PF_HISTORY.Attribute7%TYPE,
409 p_Attribute8 IGC_CC_DET_PF_HISTORY.Attribute8%TYPE,
410 p_Attribute9 IGC_CC_DET_PF_HISTORY.Attribute9%TYPE,
411 p_Attribute10 IGC_CC_DET_PF_HISTORY.Attribute10%TYPE,
412 p_Attribute11 IGC_CC_DET_PF_HISTORY.Attribute11%TYPE,
413 p_Attribute12 IGC_CC_DET_PF_HISTORY.Attribute12%TYPE,
414 p_Attribute13 IGC_CC_DET_PF_HISTORY.Attribute13%TYPE,
415 p_Attribute14 IGC_CC_DET_PF_HISTORY.Attribute14%TYPE,
416 p_Attribute15 IGC_CC_DET_PF_HISTORY.Attribute15%TYPE,
417 p_Context IGC_CC_DET_PF_HISTORY.Context%TYPE,
418 X_row_locked OUT NOCOPY VARCHAR2,
419 G_FLAG IN OUT NOCOPY VARCHAR2
420 ) IS
421
422 l_api_name CONSTANT VARCHAR2(30) := 'Lock_Row';
423 l_api_version CONSTANT NUMBER := 1.0;
424 Counter NUMBER;
425
426 CURSOR C IS
427 SELECT *
428 FROM IGC_CC_DET_PF_HISTORY
429 WHERE Rowid = p_Rowid
430 FOR UPDATE of CC_Det_PF_Line_Id NOWAIT;
431
432 Recinfo C%ROWTYPE;
433
434 BEGIN
435
436 SAVEPOINT Lock_Row_Pvt ;
437
438 IF NOT FND_API.Compatible_API_Call ( l_api_version,
439 p_api_version,
440 l_api_name,
441 G_PKG_NAME )
442 THEN
443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
444 END IF;
445
446
447 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
448 FND_MSG_PUB.initialize ;
449 END IF;
450
451 X_return_status := FND_API.G_RET_STS_SUCCESS ;
452 X_row_locked := FND_API.G_TRUE ;
453
454 OPEN C;
455 FETCH C
456 INTO Recinfo;
457
458 IF (C%NOTFOUND) THEN
459 CLOSE C;
460 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
461 FND_MSG_PUB.Add;
462 RAISE FND_API.G_EXC_ERROR ;
463 END IF;
464
465 CLOSE C;
466
467 IF (
468 (Recinfo.CC_Det_PF_Line_Id = p_CC_Det_PF_Line_Id)
469 AND (Recinfo.CC_Det_PF_Line_Num = p_CC_Det_PF_Line_Num)
470 AND (Recinfo.CC_Acct_Line_Id = p_CC_Acct_Line_Id)
471 AND (Recinfo.CC_Det_PF_Version_Num = p_Det_PF_Version_Num)
472 AND (Recinfo.CC_Det_PF_Version_Action = p_Det_PF_Version_Action)
473 AND ( (Recinfo.Parent_Det_PF_Line_Id = p_Parent_Det_PF_Line_Id)
474 OR ( (Recinfo.Parent_Det_PF_Line_Id IS NULL)
475 AND (p_Parent_Det_PF_Line_Id IS NULL)))
476 AND ( (Recinfo.Parent_Acct_Line_Id = p_Parent_Acct_Line_Id)
477 OR ( (Recinfo.Parent_Acct_Line_Id IS NULL)
478 AND (p_Parent_Acct_Line_Id IS NULL)))
479 AND ( (Recinfo.CC_Det_PF_Entered_Amt = p_CC_Det_PF_Entered_Amt)
480 OR ( (Recinfo.CC_Det_PF_Entered_Amt IS NULL)
481 AND (p_CC_Det_PF_Entered_Amt IS NULL)))
482 AND ( (Recinfo.CC_Det_PF_Func_Amt = p_CC_Det_PF_Func_Amt)
483 OR ( (Recinfo.CC_Det_PF_Func_Amt IS NULL)
484 AND (p_CC_Det_PF_Func_Amt IS NULL)))
485 AND ( (Recinfo.CC_Det_PF_Date = p_CC_Det_PF_Date)
486 OR ( (Recinfo.CC_Det_PF_Date IS NULL)
487 AND (p_CC_Det_PF_Date IS NULL)))
488 AND ( (Recinfo.CC_Det_PF_Billed_Amt = p_CC_Det_PF_Billed_Amt)
489 OR ( (Recinfo.CC_Det_PF_Billed_Amt IS NULL)
490 AND (p_CC_Det_PF_Billed_Amt IS NULL)))
491 AND ( (Recinfo.CC_Det_PF_UnBilled_Amt = p_CC_Det_PF_UnBilled_Amt )
492 OR ( (Recinfo.CC_Det_PF_UnBilled_Amt IS NULL)
493 AND (p_CC_Det_PF_UnBilled_Amt IS NULL)))
494 AND ( (Recinfo.CC_Det_PF_Encmbrnc_Amt = p_CC_Det_PF_Encmbrnc_Amt)
495 OR ( (Recinfo.CC_Det_PF_Encmbrnc_Amt IS NULL)
496 AND (p_CC_Det_PF_Encmbrnc_Amt IS NULL)))
497 AND ( (Recinfo.CC_Det_PF_Encmbrnc_Date = p_CC_Det_PF_Encmbrnc_Date)
498 OR ( (Recinfo.CC_Det_PF_Encmbrnc_Date IS NULL)
499 AND (p_CC_Det_PF_Encmbrnc_Date IS NULL)))
500 AND ( (Recinfo.CC_Det_PF_Encmbrnc_Status = p_CC_Det_PF_Encmbrnc_Status)
501 OR ( (Recinfo.CC_Det_PF_Encmbrnc_Status IS NULL)
502 AND (p_CC_Det_PF_Encmbrnc_Status IS NULL)))
503 AND ( (Recinfo.Attribute1 = p_Attribute1)
504 OR ( (Recinfo.Attribute1 IS NULL)
505 AND (p_Attribute1 IS NULL)))
506 AND ( (Recinfo.Attribute2 = p_Attribute2)
507 OR ( (Recinfo.Attribute2 IS NULL)
508 AND (p_Attribute2 IS NULL)))
509 AND ( (Recinfo.Attribute3 = p_Attribute3)
510 OR ( (Recinfo.Attribute3 IS NULL)
511 AND (p_Attribute3 IS NULL)))
512 AND ( (Recinfo.Attribute4 = p_Attribute4)
513 OR ( (Recinfo.Attribute4 IS NULL)
514 AND (p_Attribute4 IS NULL)))
515 AND ( (Recinfo.Attribute5 = p_Attribute5)
516 OR ( (Recinfo.Attribute5 IS NULL)
517 AND (p_Attribute5 IS NULL)))
518 AND ( (Recinfo.Attribute6 = p_Attribute6)
519 OR ( (Recinfo.Attribute6 IS NULL)
520 AND (p_Attribute6 IS NULL)))
521 AND ( (Recinfo.Attribute7 = p_Attribute7)
522 OR ( (Recinfo.Attribute7 IS NULL)
523 AND (p_Attribute7 IS NULL)))
524 AND ( (Recinfo.Attribute8 = p_Attribute8)
525 OR ( (Recinfo.Attribute8 IS NULL)
526 AND (p_Attribute8 IS NULL)))
527 AND ( (Recinfo.Attribute9 = p_Attribute9)
528 OR ( (Recinfo.Attribute9 IS NULL)
529 AND (p_Attribute9 IS NULL)))
530 AND ( (Recinfo.Attribute10 = p_Attribute10)
531 OR ( (Recinfo.Attribute10 IS NULL)
532 AND (p_Attribute10 IS NULL)))
533 AND ( (Recinfo.Attribute11 = p_Attribute11)
534 OR ( (Recinfo.Attribute11 IS NULL)
535 AND (p_Attribute11 IS NULL)))
536 AND ( (Recinfo.Attribute12 = p_Attribute12)
537 OR ( (Recinfo.Attribute12 IS NULL)
538 AND (p_Attribute12 IS NULL)))
539 AND ( (Recinfo.Attribute13 = p_Attribute13)
540 OR ( (Recinfo.Attribute13 IS NULL)
541 AND (p_Attribute13 IS NULL)))
542 AND ( (Recinfo.Attribute14 = p_Attribute14)
543 OR ( (Recinfo.Attribute14 IS NULL)
544 AND (p_Attribute14 IS NULL)))
545 AND ( (Recinfo.Attribute15 = p_Attribute15)
546 OR ( (Recinfo.Attribute15 IS NULL)
547 AND (p_Attribute15 IS NULL)))
548 AND ( (Recinfo.Context = p_Context)
549 OR ( (Recinfo.Context IS NULL)
550 AND (p_Context IS NULL)))
551 ) THEN
552
553 NULL;
554
555 ELSE
556
557 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
558 FND_MSG_PUB.Add;
559 RAISE FND_API.G_EXC_ERROR ;
560
561 END IF;
562
563 IF FND_API.To_Boolean ( p_commit ) THEN
564 COMMIT WORK;
565 END iF;
566
567 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
568 p_data => X_msg_data );
569
570 EXCEPTION
571
572 WHEN APP_EXCEPTION.RECORD_LOCK_EXCEPTION THEN
573
574 ROLLBACK TO Lock_Row_Pvt ;
575 X_row_locked := FND_API.G_FALSE;
576 X_return_status := FND_API.G_RET_STS_ERROR;
577 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
578 p_data => X_msg_data );
579
580 WHEN FND_API.G_EXC_ERROR THEN
581
582 ROLLBACK TO Lock_Row_Pvt ;
583 X_return_status := FND_API.G_RET_STS_ERROR;
584 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
585 p_data => X_msg_data );
586
587 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
588
589 ROLLBACK TO Lock_Row_Pvt ;
590 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
591 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
592 p_data => X_msg_data );
593
594 WHEN OTHERS THEN
595
596 ROLLBACK TO Lock_Row_Pvt ;
597 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
598
599 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
600 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
601 l_api_name);
602 END if;
603
604 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
605 p_data => X_msg_data );
606
607 END Lock_Row;
608
609 /* ================================================================================
610 PROCEDURE Update_Row
611 ===============================================================================*/
612
613 PROCEDURE Update_Row(
614 p_api_version IN NUMBER,
615 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
616 p_commit IN VARCHAR2 := FND_API.G_FALSE,
617 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
618 X_return_status OUT NOCOPY VARCHAR2,
619 X_msg_count OUT NOCOPY NUMBER,
620 X_msg_data OUT NOCOPY VARCHAR2,
621 p_Rowid IN OUT NOCOPY VARCHAR2,
622 p_CC_Det_PF_Line_Id IGC_CC_DET_PF_HISTORY.CC_Det_PF_Line_Id%TYPE,
623 p_CC_Det_PF_Line_Num IGC_CC_DET_PF_HISTORY.CC_Det_PF_Line_Num%TYPE,
624 p_CC_Acct_Line_Id IGC_CC_DET_PF_HISTORY.CC_Acct_Line_Id%TYPE,
625 p_Parent_Acct_Line_Id IGC_CC_DET_PF_HISTORY.Parent_Acct_Line_Id%TYPE,
626 p_Parent_Det_PF_Line_Id IGC_CC_DET_PF_HISTORY.Parent_Det_PF_Line_Id%TYPE,
627 p_Det_PF_Version_Num IGC_CC_DET_PF_HISTORY.CC_Det_PF_Version_Num%TYPE,
628 p_Det_PF_Version_Action IGC_CC_DET_PF_HISTORY.CC_Det_PF_Version_Action%TYPE,
629 p_CC_Det_PF_Entered_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Entered_Amt%TYPE,
630 p_CC_Det_PF_Func_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Func_Amt%TYPE,
631 p_CC_Det_PF_Date IGC_CC_DET_PF_HISTORY.CC_Det_PF_Date%TYPE,
632 p_CC_Det_PF_Billed_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Billed_Amt%TYPE,
633 p_CC_Det_PF_Unbilled_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Unbilled_Amt%TYPE,
634 p_CC_Det_PF_Encmbrnc_Amt IGC_CC_DET_PF_HISTORY.CC_Det_PF_Encmbrnc_Amt%TYPE,
635 p_CC_Det_PF_Encmbrnc_Date IGC_CC_DET_PF_HISTORY.CC_Det_PF_Encmbrnc_Date%TYPE,
636 p_CC_Det_PF_Encmbrnc_Status IGC_CC_DET_PF_HISTORY.CC_Det_PF_Encmbrnc_Status%TYPE,
637 p_Last_Update_Date IGC_CC_DET_PF_HISTORY.Last_Update_Date%TYPE,
638 p_Last_Updated_By IGC_CC_DET_PF_HISTORY.Last_Updated_By%TYPE,
639 p_Last_Update_Login IGC_CC_DET_PF_HISTORY.Last_Update_Login%TYPE,
640 p_Creation_Date IGC_CC_DET_PF_HISTORY.Creation_Date%TYPE,
641 p_Created_By IGC_CC_DET_PF_HISTORY.Created_By%TYPE,
642 p_Attribute1 IGC_CC_DET_PF_HISTORY.Attribute1%TYPE,
643 p_Attribute2 IGC_CC_DET_PF_HISTORY.Attribute2%TYPE,
644 p_Attribute3 IGC_CC_DET_PF_HISTORY.Attribute3%TYPE,
645 p_Attribute4 IGC_CC_DET_PF_HISTORY.Attribute4%TYPE,
646 p_Attribute5 IGC_CC_DET_PF_HISTORY.Attribute5%TYPE,
647 p_Attribute6 IGC_CC_DET_PF_HISTORY.Attribute6%TYPE,
648 p_Attribute7 IGC_CC_DET_PF_HISTORY.Attribute7%TYPE,
649 p_Attribute8 IGC_CC_DET_PF_HISTORY.Attribute8%TYPE,
650 p_Attribute9 IGC_CC_DET_PF_HISTORY.Attribute9%TYPE,
651 p_Attribute10 IGC_CC_DET_PF_HISTORY.Attribute10%TYPE,
652 p_Attribute11 IGC_CC_DET_PF_HISTORY.Attribute11%TYPE,
653 p_Attribute12 IGC_CC_DET_PF_HISTORY.Attribute12%TYPE,
654 p_Attribute13 IGC_CC_DET_PF_HISTORY.Attribute13%TYPE,
655 p_Attribute14 IGC_CC_DET_PF_HISTORY.Attribute14%TYPE,
656 p_Attribute15 IGC_CC_DET_PF_HISTORY.Attribute15%TYPE,
657 p_Context IGC_CC_DET_PF_HISTORY.Context%TYPE,
658 G_FLAG IN OUT NOCOPY VARCHAR2
659 ) IS
660
661 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
662 l_api_version CONSTANT NUMBER := 1.0;
663
664 BEGIN
665
666 SAVEPOINT Update_Row_Pvt ;
667
668 IF NOT FND_API.Compatible_API_Call ( l_api_version,
669 p_api_version,
670 l_api_name,
671 G_PKG_NAME )
672 THEN
673 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
674 END IF;
675
676
677 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
678 FND_MSG_PUB.initialize ;
679 END IF;
680
681 X_return_status := FND_API.G_RET_STS_SUCCESS ;
682
683 UPDATE IGC_CC_DET_PF_HISTORY
684 SET CC_Det_PF_Line_Id = p_CC_Det_PF_Line_Id,
685 Parent_Det_PF_Line_Id = p_Parent_Det_PF_Line_Id,
686 CC_Acct_Line_Id = p_CC_Acct_Line_Id,
687 Parent_Acct_Line_Id = p_Parent_Acct_Line_Id,
688 CC_Det_PF_Line_Num = p_CC_Det_PF_Line_Num,
689 CC_Det_PF_Version_Num = p_Det_PF_Version_Num,
690 CC_Det_PF_Version_Action = p_Det_PF_Version_Action,
691 CC_Det_PF_Entered_Amt = p_CC_Det_PF_Entered_Amt,
692 CC_Det_PF_Func_Amt = p_CC_Det_PF_Func_Amt,
693 CC_Det_PF_Date = p_CC_Det_PF_Date,
694 CC_Det_PF_Billed_Amt = p_CC_Det_PF_Billed_Amt,
695 CC_Det_PF_Unbilled_Amt = p_CC_Det_PF_Unbilled_Amt,
696 CC_Det_PF_Encmbrnc_Amt = p_CC_Det_PF_Encmbrnc_Amt,
697 CC_Det_PF_Encmbrnc_Date = p_CC_Det_PF_Encmbrnc_Date,
698 CC_Det_PF_Encmbrnc_Status = p_CC_Det_PF_Encmbrnc_Status,
699 Last_Update_Date = p_Last_Update_Date,
700 Last_Updated_By = p_Last_Updated_By,
701 Last_Update_Login = p_Last_Update_Login,
702 Creation_Date = p_Creation_Date,
703 Created_By = p_Created_By,
704 Attribute1 = p_Attribute1,
705 Attribute2 = p_Attribute2,
706 Attribute3 = p_Attribute3,
707 Attribute4 = p_Attribute4,
708 Attribute5 = p_Attribute5,
709 Attribute6 = p_Attribute6,
710 Attribute7 = p_Attribute7,
711 Attribute8 = p_Attribute8,
712 Attribute9 = p_Attribute9,
713 Attribute10 = p_Attribute10,
714 Attribute11 = p_Attribute11,
715 Attribute12 = p_Attribute12,
716 Attribute13 = p_Attribute13,
717 Attribute14 = p_Attribute14,
718 Attribute15 = p_Attribute15,
719 Context = p_Context
720 WHERE rowid = p_Rowid;
721
722 IF (SQL%NOTFOUND) THEN
723 RAISE NO_DATA_FOUND;
724 END IF;
725
726 IF FND_API.To_Boolean ( p_commit ) THEN
727 COMMIT WORK;
728 END iF;
729
730 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
731 p_data => X_msg_data );
732
733 EXCEPTION
734
735 WHEN FND_API.G_EXC_ERROR THEN
736
737 ROLLBACK TO Update_Row_Pvt ;
738 X_return_status := FND_API.G_RET_STS_ERROR;
739 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
740 p_data => X_msg_data );
741
742 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
743
744 ROLLBACK TO Update_Row_Pvt ;
745 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
746 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
747 p_data => X_msg_data );
748
749 WHEN OTHERS THEN
750
751 ROLLBACK TO Update_Row_Pvt ;
752 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
753
754 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
755 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
756 l_api_name);
757 END if;
758
759 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
760 p_data => X_msg_data );
761
762 END Update_Row;
763
764 /* ================================================================================
765 PROCEDURE Delete_Row
766 ===============================================================================*/
767
768
769 PROCEDURE Delete_Row(
770 p_api_version IN NUMBER,
771 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
772 p_commit IN VARCHAR2 := FND_API.G_FALSE,
773 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
774 X_return_status OUT NOCOPY VARCHAR2,
775 X_msg_count OUT NOCOPY NUMBER,
776 X_msg_data OUT NOCOPY VARCHAR2,
777 p_Rowid VARCHAR2,
778 G_FLAG IN OUT NOCOPY VARCHAR2
779 ) IS
780
781 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
782 l_api_version CONSTANT NUMBER := 1.0;
783
784 l_return_status VARCHAR2(1) ;
785 l_msg_count NUMBER ;
786 l_msg_data VARCHAR2(2000) ;
787
788 BEGIN
789
790 SAVEPOINT Delete_Row_Pvt ;
791
792 IF NOT FND_API.Compatible_API_Call ( l_api_version,
793 p_api_version,
794 l_api_name,
795 G_PKG_NAME )
796 THEN
797 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
798 END IF;
799
800 IF FND_API.to_Boolean ( p_init_msg_list ) THEN
801 FND_MSG_PUB.initialize ;
802 END IF ;
803
804 X_return_status := FND_API.G_RET_STS_SUCCESS ;
805
806 DELETE
807 FROM IGC_CC_DET_PF_HISTORY
808 WHERE rowid = p_Rowid;
809
810 IF (SQL%NOTFOUND) THEN
811 RAISE NO_DATA_FOUND;
812 END IF;
813
814 IF FND_API.To_Boolean ( p_commit ) THEN
815 COMMIT WORK;
816 END iF;
817
818 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
819 p_data => X_msg_data );
820
821 EXCEPTION
822
823 WHEN FND_API.G_EXC_ERROR THEN
824
825 ROLLBACK TO Delete_Row_Pvt ;
826 X_return_status := FND_API.G_RET_STS_ERROR;
827 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
828 p_data => X_msg_data );
829
830 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
831
832 ROLLBACK TO Delete_Row_Pvt ;
833 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
834 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
835 p_data => X_msg_data );
836
837 WHEN OTHERS THEN
838
839 ROLLBACK TO Delete_Row_Pvt ;
840 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
841
842 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
843 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME,
844 l_api_name);
845 END if;
846
847 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
848 p_data => X_msg_data );
849
850 END Delete_Row;
851
852 END IGC_CC_DET_PF_HISTORY_PKG;