[Home] [Help]
PACKAGE BODY: APPS.IGI_RPI_UPDATE_LINES_D_PKG
Source
1 PACKAGE BODY IGI_RPI_UPDATE_LINES_D_PKG as
2 --- $Header: igiruldb.pls 120.4.12000000.1 2007/08/31 05:53:52 mbremkum ship $
3
4 l_debug_level number:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5
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 PROCEDURE Insert_Row(
14 X_Rowid IN OUT NOCOPY VARCHAR2,
15 X_run_id NUMBER,
16 X_standing_charge_id NUMBER,
17 X_line_item_id NUMBER,
18 X_item_id NUMBER,
19 X_price NUMBER,
20 X_effective_date DATE,
21 X_revised_price NUMBER,
22 X_revised_effective_date DATE,
23 X_previous_price NUMBER,
24 X_previous_effective_date DATE,
25 X_updated_price NUMBER,
26 X_select_flag VARCHAR2,
27 X_Created_By NUMBER,
28 X_Creation_Date DATE,
29 X_Last_Updated_By NUMBER,
30 X_Last_Update_Date DATE,
31 X_Last_Update_Login NUMBER
32 ) IS
33 CURSOR C IS SELECT rowid FROM igi_rpi_update_lines
34 WHERE run_id = X_run_id
35 and item_id = X_item_id
36 and standing_charge_id is not null;
37 BEGIN
38
39
40 INSERT INTO igi_rpi_update_lines (
41 run_id,
42 standing_charge_id,
43 line_item_id,
44 item_id,
45 price,
46 effective_date,
47 revised_price,
48 revised_effective_date,
49 previous_price,
50 previous_effective_date,
51 updated_price,
52 select_flag,
53 Created_By,
54 Creation_Date,
55 Last_Updated_by,
56 Last_Update_Date,
57 Last_Update_Login )
58 VALUES (
59 X_run_id,
60 X_standing_charge_id,
61 X_line_item_id,
62 X_item_id,
63 X_price,
64 X_effective_date,
65 X_revised_price,
66 X_revised_effective_date,
67 X_previous_price,
68 X_previous_effective_date,
69 X_updated_price,
70 X_select_flag,
71 X_Created_By,
72 X_Creation_Date,
73 X_Last_Updated_By,
74 X_Last_Update_Date,
75 X_Last_Update_Login);
76
77 OPEN C;
78 FETCH C INTO X_Rowid;
79 if (C%NOTFOUND) then
80 CLOSE C;
81 Raise NO_DATA_FOUND;
82 end if;
83 CLOSE C;
84 END Insert_Row;
85
86
87 PROCEDURE Lock_Row(
88 X_Rowid IN OUT NOCOPY VARCHAR2,
89 X_run_id NUMBER,
90 X_standing_charge_id NUMBER,
91 X_line_item_id NUMBER,
92 X_item_id NUMBER,
93 X_price NUMBER,
94 X_effective_date DATE,
95 X_revised_price NUMBER,
96 X_revised_effective_date DATE,
97 X_previous_price NUMBER,
98 X_previous_effective_date DATE,
99 X_updated_price NUMBER,
100 X_select_flag VARCHAR2
101 ) IS
102 CURSOR C IS
103 SELECT *
104 FROM igi_rpi_update_lines
105 WHERE rowid = X_Rowid
106 FOR UPDATE of run_id NOWAIT;
107 Recinfo C%ROWTYPE;
108 BEGIN
109 OPEN C;
110 FETCH C INTO Recinfo;
111 if (C%NOTFOUND) then
112 CLOSE C;
113 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
114 --Bug 3199481 (start)
115 If (l_unexp_level >= l_debug_level) then
116 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_update_lines_d_pkg.lock_row.Msg1',FALSE);
117 End if;
118 --Bug 3199481 (end)
119 APP_EXCEPTION.Raise_Exception;
120 end if;
121 CLOSE C;
122 if (
123 (Recinfo.run_id = X_run_id)
124 AND ((Recinfo.standing_charge_id = X_standing_charge_id)
125 OR ((Recinfo.standing_charge_id is null)
126 AND (X_standing_charge_id is null)))
127 AND ((Recinfo.line_item_id = X_line_item_id)
128 OR ((Recinfo.line_item_id is null)
129 AND (X_line_item_id is null)))
130 AND (Recinfo.item_id = X_item_id)
131 AND (Recinfo.price = X_price)
132 AND (Recinfo.effective_date = X_effective_date)
133 AND ((Recinfo.revised_price = X_revised_price)
134 OR ((Recinfo.revised_price is null)
135 AND (X_revised_price is null)))
136 AND ((Recinfo.revised_effective_date = X_revised_effective_date)
137 OR ((Recinfo.revised_effective_date is null)
138 AND (X_revised_effective_date is null)))
139 AND ((Recinfo.previous_price = X_previous_price)
140 OR ((Recinfo.previous_price is null)
141 AND (X_previous_price is null)))
142 AND ((Recinfo.previous_effective_date = X_previous_effective_date)
143 OR ((Recinfo.previous_effective_date is null)
144 AND (X_previous_effective_date is null)))
145 AND ((Recinfo.updated_price = X_updated_price)
146 OR ((Recinfo.updated_price is null)
147 AND (X_updated_price is null)))
148 AND ((Recinfo.select_flag = rtrim(X_select_flag))
149 OR ((Recinfo.select_flag is null)
150 AND (rtrim(X_select_flag) is null)))
151 ) then
152 return;
153 else
154 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
155 --Bug 3199481 (start)
156 If (l_unexp_level >= l_debug_level) then
157 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_update_lines_d_pkg.lock_row.Msg2',FALSE);
158 End if;
159 --Bug 3199481 (end)
160 APP_EXCEPTION.Raise_Exception;
161 end if;
162 END Lock_Row;
163
164 PROCEDURE Update_Row(
165 X_Rowid IN OUT NOCOPY VARCHAR2,
166 X_run_id NUMBER,
167 X_standing_charge_id NUMBER,
168 X_line_item_id NUMBER,
169 X_item_id NUMBER,
170 X_price NUMBER,
171 X_effective_date DATE,
172 X_revised_price NUMBER,
173 X_revised_effective_date DATE,
174 X_previous_price NUMBER,
175 X_previous_effective_date DATE,
176 X_updated_price NUMBER,
177 X_select_flag VARCHAR2,
178 X_Last_Updated_By NUMBER,
179 X_Last_Update_Date DATE,
180 X_Last_Update_Login NUMBER
181 ) IS
182 BEGIN
183 UPDATE igi_rpi_update_lines
184 SET
185 run_id = X_run_id,
186 standing_charge_id = X_standing_charge_id,
187 line_item_id = X_line_item_id,
188 price = X_price,
189 effective_date = X_effective_date,
190 revised_price = X_revised_price,
191 revised_effective_date = X_revised_effective_date,
192 previous_price = X_previous_price,
193 previous_effective_date = X_previous_effective_date,
194 updated_price = X_updated_price,
195 select_flag = X_select_flag,
196 Last_Updated_By = X_Last_Updated_By,
197 Last_Update_Date = X_Last_Update_Date,
198 Last_Update_Login = X_Last_Update_Login
199 WHERE rowid = X_Rowid;
200
201 if (SQL%NOTFOUND) then
202 Raise NO_DATA_FOUND;
203 end if;
204 END Update_Row;
205
206 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
207 BEGIN
208 DELETE FROM igi_rpi_update_lines
209 WHERE rowid = X_Rowid;
210
211 if (SQL%NOTFOUND) then
212 Raise NO_DATA_FOUND;
213 end if;
214 END Delete_Row;
215
216
217 END IGI_RPI_UPDATE_LINES_D_PKG;