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