[Home] [Help]
PACKAGE BODY: APPS.IGI_RPI_LINE_DETAILS_PKG
Source
1 PACKAGE BODY IGI_RPI_LINE_DETAILS_PKG as
2 --- $Header: igirldeb.pls 120.5.12000000.1 2007/08/31 05:52:59 mbremkum ship $
3
4 l_debug_level number:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
5 l_state_level number:=FND_LOG.LEVEL_STATEMENT;
6 l_proc_level number:=FND_LOG.LEVEL_PROCEDURE;
7 l_event_level number:=FND_LOG.LEVEL_EVENT;
8 l_excep_level number:=FND_LOG.LEVEL_EXCEPTION;
9 l_error_level number:=FND_LOG.LEVEL_ERROR;
10 l_unexp_level number:=FND_LOG.LEVEL_UNEXPECTED;
11
12 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
13 X_Standing_Charge_Id NUMBER,
14 X_Line_Item_Id IN OUT NOCOPY NUMBER,
15 X_Charge_Item_Number NUMBER,
16 X_Item_Id NUMBER,
17 X_Price NUMBER,
18 X_Quantity NUMBER,
19 X_Period_Name VARCHAR2,
20 X_Current_Effective_Date DATE,
21 X_Description VARCHAR2,
22 X_Creation_Date DATE,
23 X_Created_By NUMBER,
24 X_Last_Update_Date DATE,
25 X_Last_Updated_By NUMBER,
26 X_Last_Update_Login NUMBER,
27 X_Revised_Effective_Date DATE,
28 X_Revised_Price NUMBER,
29 X_Previous_Price NUMBER,
30 X_Previous_Effective_Date DATE,
31 X_Vat_Tax_Id NUMBER,
32 X_Revenue_Code_Combination_Id NUMBER,
33 X_Receivable_Code_Combo_Id NUMBER,
34 X_Additional_Reference VARCHAR2,
35 X_Accounting_rule_id NUMBER,
36 X_Start_date DATE,
37 X_Duration NUMBER,
38 X_Legal_Entity_Id NUMBER, --Added for MOAC Impact Bug No 5905216
39 X_Org_Id NUMBER --Added for MOAC Impact Bug No 5905216
40 ) IS
41 CURSOR C IS SELECT rowid FROM IGI_RPI_LINE_DETAILS
42 WHERE line_item_id = X_Line_Item_Id;
43 CURSOR C2 IS SELECT igi_rpi_line_details_s.nextval FROM sys.dual;
44 BEGIN
45 if (X_Line_Item_Id is NULL) then
46 OPEN C2;
47 FETCH C2 INTO X_Line_Item_Id;
48 CLOSE C2;
49 end if;
50
51 /*Modified the Insert to include ORG_ID and LEGAL_ENTITY_ID for MOAC Impact R12 Uptake Bug No 5905216*/
52
53 INSERT INTO IGI_RPI_LINE_DETAILS(
54 standing_charge_id,
55 line_item_id,
56 charge_item_number,
57 item_id,
58 price,
59 quantity,
60 period_name,
61 current_effective_date,
62 description,
63 creation_date,
64 created_by,
65 last_update_date,
66 last_updated_by,
67 last_update_login,
68 revised_effective_date,
69 revised_price,
70 previous_price,
71 previous_effective_date,
72 vat_tax_id,
73 revenue_code_combination_id,
74 receivable_code_combination_id,
75 additional_reference,
76 accounting_rule_id,
77 start_date,
78 duration,
79 legal_entity_id,
80 org_id
81 ) VALUES (
82 X_Standing_Charge_Id,
83 X_Line_Item_Id,
84 X_Charge_Item_Number,
85 X_Item_Id,
86 X_Price,
87 X_Quantity,
88 X_Period_Name,
89 X_Current_Effective_Date,
90 X_Description,
91 X_Creation_Date,
92 X_Created_By,
93 X_Last_Update_Date,
94 X_Last_Updated_By,
95 X_Last_Update_Login,
96 X_Revised_Effective_Date,
97 X_Revised_Price,
98 X_Previous_Price,
99 X_Previous_Effective_Date,
100 X_Vat_Tax_Id,
101 X_Revenue_Code_Combination_Id,
102 X_Receivable_Code_Combo_Id,
103 X_Additional_Reference,
104 X_Accounting_Rule_id,
105 X_Start_Date,
106 X_Duration,
107 X_Legal_Entity_Id,
108 X_Org_Id
109 );
110
111 OPEN C;
112 FETCH C INTO X_Rowid;
113 if (C%NOTFOUND) then
114 CLOSE C;
115 Raise NO_DATA_FOUND;
116 end if;
117 CLOSE C;
118 END Insert_Row;
119
120
121 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
122 X_Standing_Charge_Id NUMBER,
123 X_Line_Item_Id NUMBER,
124 X_Charge_Item_Number NUMBER,
125 X_Item_Id NUMBER,
126 X_Price NUMBER,
127 X_Quantity NUMBER,
128 X_Period_Name VARCHAR2,
129 X_Current_Effective_Date DATE,
130 X_Description VARCHAR2,
131 X_Revised_Effective_Date DATE,
132 X_Revised_Price NUMBER,
133 X_Previous_Price NUMBER,
134 X_Previous_Effective_Date DATE,
135 X_Vat_Tax_Id NUMBER,
136 X_Revenue_Code_Combination_Id NUMBER,
137 X_Receivable_Code_Combo_Id NUMBER,
138 X_Additional_Reference VARCHAR2,
139 X_accounting_rule_id NUMBER,
140 X_start_date DATE,
141 X_duration NUMBER,
142 -- X_Legal_Entity_Id NUMBER, --Added for MOAC Impact Bug No 5905216
143 X_Org_Id NUMBER --Added for MOAC Impact Bug No 5905216
144 ) IS
145 CURSOR C IS
146 SELECT *
147 FROM IGI_RPI_LINE_DETAILS
148 WHERE rowid = X_Rowid
149 FOR UPDATE of Line_Item_Id NOWAIT;
150 Recinfo C%ROWTYPE;
151
152
153 BEGIN
154 OPEN C;
155 FETCH C INTO Recinfo;
156 if (C%NOTFOUND) then
157 CLOSE C;
158 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
159 --Bug 3199481 (start)
160 If (l_unexp_level >= l_debug_level) then
161 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_line_details_pkg.lock_row.Msg1',FALSE);
162 End if;
163 --Bug 3199481 (end)
164 APP_EXCEPTION.Raise_Exception;
165 end if;
166 CLOSE C;
167 if (
168 (Recinfo.standing_charge_id = X_Standing_Charge_Id)
169 AND (Recinfo.line_item_id = X_Line_Item_Id)
170 AND (Recinfo.charge_item_number = X_Charge_Item_Number)
171 AND (Recinfo.item_id = X_Item_Id)
172 AND (Recinfo.price = X_Price)
173 AND (Recinfo.quantity = X_Quantity)
174 AND (Recinfo.period_name = X_Period_Name)
175 AND (Recinfo.current_effective_date = X_Current_Effective_Date)
176 AND (Recinfo.description = X_Description)
177 /*Added for MOAC Impact R12 Uptake bug No 5905216 - Start*/
178 AND (Recinfo.org_id = X_Org_Id)
179 -- AND (Recinfo.legal_entity_id = X_Legal_Entity_Id)
180 /*Added for MOAC Impact R12 Uptake bug No 5905216 - End*/
181 AND ( (Recinfo.revised_effective_date = X_Revised_Effective_Date)
182 OR ( (Recinfo.revised_effective_date IS NULL)
183 AND (X_Revised_Effective_Date IS NULL)))
184 AND ( (Recinfo.revised_price = X_Revised_Price)
185 OR ( (Recinfo.revised_price IS NULL)
186 AND (X_Revised_Price IS NULL)))
187 AND ( (Recinfo.previous_price = X_Previous_Price)
188 OR ( (Recinfo.previous_price IS NULL)
189 AND (X_Previous_Price IS NULL)))
190 AND ( (Recinfo.previous_effective_date = X_Previous_Effective_Date)
191 OR ( (Recinfo.previous_effective_date IS NULL)
192 AND (X_Previous_effective_date IS NULL)))
193 AND ( (Recinfo.vat_tax_id = X_Vat_Tax_Id)
194 OR ( (Recinfo.vat_tax_id IS NULL)
195 AND (X_Vat_Tax_Id IS NULL)))
196 AND ((Recinfo.revenue_code_combination_id = X_Revenue_Code_Combination_Id)
197 OR ( (Recinfo.revenue_code_combination_id IS NULL)
198 AND (X_Revenue_Code_Combination_Id IS NULL)))
199 AND ((Recinfo.receivable_code_combination_id = X_Receivable_Code_Combo_Id) OR ( (Recinfo.receivable_code_combination_id IS NULL)
200 AND (X_Receivable_Code_Combo_Id IS NULL)))
201 AND ( (Recinfo.additional_reference = X_Additional_Reference)
202 OR ( (Recinfo.additional_reference IS NULL)
203 AND (X_Additional_Reference IS NULL)))
204 AND ( (Recinfo.accounting_rule_id = X_accounting_rule_id)
205 OR ( (Recinfo.accounting_rule_id IS NULL)
206 AND (X_accounting_rule_id IS NULL)))
207 AND ( (Recinfo.start_date = X_start_date)
208 OR ( (Recinfo.start_date IS NULL)
209 AND (X_start_date IS NULL)))
210 AND ( (Recinfo.duration = X_duration)
211 OR ( (Recinfo.duration IS NULL)
212 AND (X_duration IS NULL)))
213
214 ) then
215 return;
216 else
217 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
218 --Bug 3199481 (start)
219 If (l_unexp_level >= l_debug_level) then
220 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_line_details_pkg.lock_row.Msg2',FALSE);
221 End if;
222 --Bug 3199481 (end)
223 APP_EXCEPTION.Raise_Exception;
224 end if;
225 END Lock_Row;
226
227
228
229 PROCEDURE Update_Row(X_Rowid VARCHAR2,
230 X_Standing_Charge_Id NUMBER,
231 X_Line_Item_Id NUMBER,
232 X_Charge_Item_Number NUMBER,
233 X_Item_Id NUMBER,
234 X_Price NUMBER,
235 X_Quantity NUMBER,
236 X_Period_Name VARCHAR2,
237 X_Current_Effective_Date DATE,
238 X_Description VARCHAR2,
239 X_Last_Update_Date DATE,
240 X_Last_Updated_By NUMBER,
241 X_Last_Update_Login NUMBER,
242 X_Revised_Effective_Date DATE,
243 X_Revised_Price NUMBER,
244 X_Previous_Price NUMBER,
245 X_Previous_Effective_Date DATE,
246 X_Vat_Tax_Id NUMBER,
247 X_Revenue_Code_Combination_Id NUMBER,
248 X_Receivable_Code_Combo_Id NUMBER,
249 X_Additional_Reference VARCHAR2,
250 X_accounting_rule_id NUMBER,
251 X_start_date DATE,
252 X_Duration NUMBER,
253 X_Legal_Entity_Id NUMBER, --Added for MOAC Impact Bug No 5905216
254 X_Org_Id NUMBER --Added for MOAC Impact Bug No 5905216
255 ) IS
256 BEGIN
257 UPDATE IGI_RPI_LINE_DETAILS
258 SET
259 standing_charge_id = X_Standing_Charge_Id,
260 line_item_id = X_Line_Item_Id,
261 charge_item_number = X_Charge_Item_Number,
262 item_id = X_Item_Id,
263 price = X_Price,
264 quantity = X_Quantity,
265 period_name = X_Period_Name,
266 current_effective_date = X_Current_Effective_Date,
267 description = X_Description,
268 last_update_date = X_Last_Update_Date,
269 last_updated_by = X_Last_Updated_By,
270 last_update_login = X_Last_Update_Login,
271 revised_effective_date = X_Revised_Effective_Date,
272 revised_price = X_Revised_Price,
273 previous_price = X_Previous_Price,
274 previous_effective_date = X_Previous_Effective_Date,
275 vat_tax_id = X_Vat_Tax_Id,
276 revenue_code_combination_id = X_Revenue_Code_Combination_Id,
277 receivable_code_combination_id = X_Receivable_Code_Combo_Id,
278 additional_reference = X_Additional_Reference,
279 accounting_rule_id = X_accounting_rule_id,
280 start_date = X_start_date,
281 duration = X_duration,
282 org_id = X_Org_Id,
283 legal_entity_id = X_Legal_Entity_Id
284 WHERE rowid = X_Rowid;
285
286 if (SQL%NOTFOUND) then
287 Raise NO_DATA_FOUND;
288 end if;
289 END Update_Row;
290
291 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
292 BEGIN
293 DELETE FROM IGI_RPI_LINE_DETAILS
294 WHERE rowid = X_Rowid;
295
296 if (SQL%NOTFOUND) then
297 Raise NO_DATA_FOUND;
298 end if;
299 END Delete_Row;
300
301
302 END IGI_RPI_LINE_DETAILS_PKG;