[Home] [Help]
PACKAGE BODY: APPS.IGI_RPI_STANDING_CHARGES_PKG
Source
1 PACKAGE BODY IGI_RPI_STANDING_CHARGES_PKG as
2 --- $Header: igirstcb.pls 120.7.12000000.1 2007/08/31 05:53:44 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 IN OUT NOCOPY NUMBER,
14 X_Charge_Reference VARCHAR2,
15 X_Set_Of_Books_Id NUMBER,
16 X_Standing_Charge_Date DATE,
17 X_Status VARCHAR2,
18 X_Bill_To_Customer_Id NUMBER,
19 X_Bill_To_Site_Use_Id NUMBER,
20 X_Batch_Source_Id NUMBER,
21 X_Cust_Trx_Type_Id NUMBER,
22 X_Salesrep_Id NUMBER,
23 X_Advance_Arrears_Ind VARCHAR2,
24 X_Period_Name VARCHAR2,
25 X_Start_Date DATE,
26 X_Next_Due_Date DATE,
27 X_Creation_Date DATE,
28 X_Created_By NUMBER,
29 X_Last_Update_Date DATE,
30 X_Last_Updated_By NUMBER,
31 X_Last_Update_Login NUMBER,
32 X_Bill_To_Contact_Id NUMBER,
33 X_Ship_To_Customer_Id NUMBER,
34 X_Ship_To_Site_Use_Id NUMBER,
35 X_Ship_To_Contact_Id NUMBER,
36 X_Reminder_Days NUMBER,
37 X_Receipt_Method_Id NUMBER,
38 X_Bank_Account_Id NUMBER,
39 X_Generate_Sequence NUMBER,
40 X_End_Date DATE,
41 X_Review_Date DATE,
42 X_Previous_Due_Date DATE,
43 X_Suppress_Inv_Print VARCHAR2,
44 X_Comments VARCHAR2,
45 X_Description VARCHAR2,
46 X_Ship_To_Address_Id NUMBER,
47 X_Bill_To_Address_Id NUMBER,
48 X_term_id NUMBER,
49 X_Currency_code VARCHAR2,
50 X_Default_Invoicing_Rule VARCHAR2,
51 /*Added for MOAC Impact Bug No 5905216 - Start*/
52 X_Org_Id NUMBER,
53 X_Legal_Entity_Id NUMBER,
54 X_Payment_Trxn_Extension_Id NUMBER
55 /*MOAC Impact Bug No 5905216 - End*/
56 ) IS
57 CURSOR C IS SELECT rowid FROM IGI_RPI_STANDING_CHARGES
58 WHERE standing_charge_id = X_Standing_Charge_Id;
59 CURSOR C2 IS SELECT igi_rpi_standing_charges_s.nextval FROM sys.dual;
60 BEGIN
61 if (X_Standing_Charge_Id is NULL) then
62 OPEN C2;
63 FETCH C2 INTO X_Standing_Charge_Id;
64 CLOSE C2;
65 end if;
66 INSERT INTO IGI_RPI_STANDING_CHARGES(
67 standing_charge_id,
68 charge_reference,
69 set_of_books_id,
70 standing_charge_date,
71 status,
72 bill_to_customer_id,
73 bill_to_site_use_id,
74 batch_source_id,
75 cust_trx_type_id,
76 salesrep_id,
77 advance_arrears_ind,
78 period_name,
79 start_date,
80 next_due_date,
81 creation_date,
82 created_by,
83 last_update_date,
84 last_updated_by,
85 last_update_login,
86 bill_to_contact_id,
87 ship_to_customer_id,
88 ship_to_site_use_id,
89 ship_to_contact_id,
90 reminder_days,
91 receipt_method_id,
92 bank_account_id,
93 generate_sequence,
94 end_date,
95 review_date,
96 previous_due_date,
97 suppress_inv_print,
98 comments,
99 description,
100 ship_to_address_id,
101 bill_to_address_id,
102 term_id,
103 currency_code,
104 default_invoicing_rule,
105 /*MOAC Impact Bug No 5905216 - Start*/
106 org_id,
107 legal_entity_id,
108 payment_trxn_extension_id
109 /*MOAC Impact Bug No 5905216 - End*/
110 ) VALUES (
111 X_Standing_Charge_Id,
112 X_Charge_Reference,
113 X_Set_Of_Books_Id,
114 X_Standing_Charge_Date,
115 X_Status,
116 X_Bill_To_Customer_Id,
117 X_Bill_To_Site_Use_Id,
118 X_Batch_Source_Id,
119 X_Cust_Trx_Type_Id,
120 X_Salesrep_Id,
121 X_Advance_Arrears_Ind,
122 X_Period_Name,
123 X_Start_Date,
124 X_Next_Due_Date,
125 X_Creation_Date,
126 X_Created_By,
127 X_Last_Update_Date,
128 X_Last_Updated_By,
129 X_Last_Update_Login,
130 X_Bill_To_Contact_Id,
131 X_Ship_To_Customer_Id,
132 X_Ship_To_Site_Use_Id,
133 X_Ship_To_Contact_Id,
134 X_Reminder_Days,
135 X_Receipt_Method_Id,
136 X_Bank_Account_Id,
137 X_Generate_Sequence,
138 X_End_Date,
139 X_Review_Date,
140 X_Previous_Due_Date,
141 X_Suppress_Inv_Print,
142 X_Comments,
143 X_Description,
144 X_Ship_To_Address_Id,
145 X_Bill_To_Address_Id,
146 X_term_id,
147 X_currency_code,
148 X_Default_Invoicing_Rule,
149 /*MOAC Impact Bug No 5905216 - Start*/
150 X_Org_id,
151 X_Legal_Entity_Id,
152 X_Payment_Trxn_Extension_Id
153 /*MOAC Impact bug No 5905216 - End*/
154 );
155
156 OPEN C;
157 FETCH C INTO X_Rowid;
158 if (C%NOTFOUND) then
159 CLOSE C;
160 Raise NO_DATA_FOUND;
161 end if;
162 CLOSE C;
163 END Insert_Row;
164
165
166 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
167 X_Standing_Charge_Id NUMBER,
168 X_Charge_Reference VARCHAR2,
169 X_Set_Of_Books_Id NUMBER,
170 X_Standing_Charge_Date DATE,
171 X_Status VARCHAR2,
172 X_Bill_To_Customer_Id NUMBER,
173 X_Bill_To_Site_Use_Id NUMBER,
174 X_Batch_Source_Id NUMBER,
175 X_Cust_Trx_Type_Id NUMBER,
176 X_Salesrep_Id NUMBER,
177 X_Advance_Arrears_Ind VARCHAR2,
178 X_Period_Name VARCHAR2,
179 X_Start_Date DATE,
180 X_Next_Due_Date DATE,
181 X_Bill_To_Contact_Id NUMBER,
182 X_Ship_To_Customer_Id NUMBER,
183 X_Ship_To_Site_Use_Id NUMBER,
184 X_Ship_To_Contact_Id NUMBER,
185 X_Reminder_Days NUMBER,
186 X_Receipt_Method_Id NUMBER,
187 X_Bank_Account_Id NUMBER,
188 X_Generate_Sequence NUMBER,
189 X_End_Date DATE,
190 X_Review_Date DATE,
191 X_Previous_Due_Date DATE,
192 X_Suppress_Inv_Print VARCHAR2,
193 X_Comments VARCHAR2,
194 X_Description VARCHAR2,
195 X_Ship_To_Address_Id NUMBER,
196 X_Bill_To_Address_Id NUMBER,
197 X_term_id NUMBER,
198 X_Currency_code VARCHAR2,
199 X_Default_Invoicing_Rule VARCHAR2,
200 /*MOAC Impact Bug No 5905216 - Start*/
201 X_Legal_Entity_Id NUMBER,
202 X_Payment_Trxn_Extension_Id NUMBER
203 /*MOAC Impact Bug No 5905216 - End*/
204 ) IS
205 CURSOR C IS
206 SELECT *
207 FROM IGI_RPI_STANDING_CHARGES
208 WHERE rowid = X_Rowid
209 FOR UPDATE of Standing_Charge_Id NOWAIT;
210 Recinfo C%ROWTYPE;
211
212
213 BEGIN
214 BEGIN
215 OPEN C;
216 FETCH C INTO Recinfo;
217 if (C%NOTFOUND) then
218 CLOSE C;
219 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
220 --Bug 3199481 (start)
221 If (l_unexp_level >= l_debug_level) then
222 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_standing_charges_pkg.lock_row.Msg1',FALSE);
223 End if;
224 --Bug 3199481 (end)
225 APP_EXCEPTION.Raise_Exception;
226 end if;
227 CLOSE C;
228 EXCEPTION WHEN OTHERS THEN IF C%ISOPEN THEN
229 CLOSE C;
230 END IF;
231 return;
232 END;
233 if (
234 (Recinfo.standing_charge_id = X_Standing_Charge_Id)
235 AND (Recinfo.charge_reference = X_Charge_Reference)
236 AND (Recinfo.set_of_books_id = X_Set_Of_Books_Id)
237 /* Added the null condn check by Panaraya for bug 2439363 */
238 AND ((Recinfo.standing_charge_date = X_Standing_Charge_Date) OR
239 (Recinfo.standing_charge_date IS NULL AND X_Standing_Charge_Date IS NULL))
240 AND (Recinfo.status = X_Status)
241 AND (Recinfo.bill_to_customer_id = X_Bill_To_Customer_Id)
242 AND (Recinfo.bill_to_site_use_id = X_Bill_To_Site_Use_Id)
243 AND (Recinfo.batch_source_id = X_Batch_Source_Id)
244 AND (Recinfo.cust_trx_type_id = X_Cust_Trx_Type_Id)
245 AND (Recinfo.salesrep_id = X_Salesrep_Id)
246 AND (Recinfo.period_name = X_Period_Name)
247 AND (Recinfo.start_date = X_Start_Date)
248 AND (Recinfo.next_due_date = X_Next_Due_Date)
249 AND (Recinfo.term_id = X_term_id)
250 AND (Recinfo.currency_code = X_currency_code)
251 /*MOAC Impact Bug No 5905216 - Start*/
252 AND (Recinfo.legal_entity_id = X_Legal_Entity_Id)
253 /*MOAC Impact Bug No 5905216 - End*/
254 AND ( (Recinfo.payment_trxn_extension_id = X_Payment_Trxn_Extension_Id)
255 OR ( (Recinfo.payment_trxn_extension_id IS NULL )
256 AND (X_Payment_Trxn_Extension_Id is NULL)))
257 AND ( (Recinfo.advance_arrears_ind = X_Advance_Arrears_Ind)
258 OR ( (Recinfo.advance_arrears_ind IS NULL )
259 AND (X_Advance_Arrears_Ind is NULL)))
260 AND ( (Recinfo.bill_to_contact_id = X_Bill_To_Contact_Id)
261 OR ( (Recinfo.bill_to_contact_id IS NULL)
262 AND (X_Bill_To_Contact_Id IS NULL)))
263 AND ( (Recinfo.ship_to_customer_id = X_Ship_To_Customer_Id)
264 OR ( (Recinfo.ship_to_customer_id IS NULL)
265 AND (X_Ship_To_Customer_Id IS NULL)))
266 AND ( (Recinfo.ship_to_site_use_id = X_Ship_To_Site_Use_Id)
267 OR ( (Recinfo.ship_to_site_use_id IS NULL)
268 AND (X_Ship_To_Site_Use_Id IS NULL)))
269 AND ( (Recinfo.ship_to_contact_id = X_Ship_To_Contact_Id)
270 OR ( (Recinfo.ship_to_contact_id IS NULL)
271 AND (X_Ship_To_Contact_Id IS NULL)))
272 AND ( (Recinfo.reminder_days = X_Reminder_Days)
273 OR ( (Recinfo.reminder_days IS NULL)
274 AND (X_Reminder_Days IS NULL)))
275 AND ( (Recinfo.receipt_method_id = X_Receipt_Method_Id)
276 OR ( (Recinfo.receipt_method_id IS NULL)
277 AND (X_Receipt_Method_Id IS NULL)))
278 AND ( (Recinfo.bank_account_id = X_Bank_Account_Id)
279 OR ( (Recinfo.bank_account_id IS NULL)
280 AND (X_Bank_Account_Id IS NULL)))
281 AND ( (Recinfo.generate_sequence = X_Generate_Sequence)
282 OR ( (Recinfo.generate_sequence IS NULL)
283 AND (X_Generate_Sequence IS NULL)))
284 AND ( (Recinfo.end_date = X_End_Date)
285 OR ( (Recinfo.end_date IS NULL)
286 AND (X_End_Date IS NULL)))
287 AND ( (Recinfo.review_date = X_Review_Date)
288 OR ( (Recinfo.review_date IS NULL)
289 AND (X_Review_Date IS NULL)))
290 AND ( (Recinfo.previous_due_date = X_Previous_Due_Date)
291 OR ( (Recinfo.previous_due_date IS NULL)
292 AND (X_Previous_Due_Date IS NULL)))
293 AND ( (Recinfo.suppress_inv_print = X_Suppress_Inv_Print)
294 OR ( (Recinfo.suppress_inv_print IS NULL)
295 AND (X_Suppress_Inv_Print IS NULL)))
296 AND ( (Recinfo.comments = X_Comments)
297 OR ( (Recinfo.comments IS NULL)
298 AND (X_Comments IS NULL)))
299 AND ( (Recinfo.description = X_Description)
300 OR ( (Recinfo.description IS NULL)
301 AND (X_Description IS NULL)))
302 AND ( (Recinfo.ship_to_address_id = X_Ship_To_Address_Id)
303 OR ( (Recinfo.ship_to_address_id IS NULL)
304 AND (X_Ship_To_Address_Id IS NULL)))
305 AND ( (Recinfo.bill_to_address_id = X_Bill_To_Address_Id)
306 OR ( (Recinfo.bill_to_address_id IS NULL)
307 AND (X_Bill_To_Address_Id IS NULL)))
308 AND ( (Recinfo.default_invoicing_rule = X_Default_Invoicing_Rule)
309 OR ( (Recinfo.default_invoicing_rule IS NULL)
310 AND (X_Default_Invoicing_Rule IS NULL)))
311
312 ) then
313 return;
314 else
315 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
316 --Bug 3199481 (start)
317 If (l_unexp_level >= l_debug_level) then
318 FND_LOG.MESSAGE(l_unexp_level,'igi.plsql.igi_rpi_standing_charges_pkg.lock_row.Msg2',FALSE);
319 End if;
320 --Bug 3199481 (end)
321 APP_EXCEPTION.Raise_Exception;
322 end if;
323 END Lock_Row;
324
325
326
327 PROCEDURE Update_Row(X_Rowid VARCHAR2,
328 X_Standing_Charge_Id NUMBER,
329 X_Charge_Reference VARCHAR2,
330 X_Set_Of_Books_Id NUMBER,
331 X_Standing_Charge_Date DATE,
332 X_Status VARCHAR2,
333 X_Bill_To_Customer_Id NUMBER,
334 X_Bill_To_Site_Use_Id NUMBER,
335 X_Batch_Source_Id NUMBER,
336 X_Cust_Trx_Type_Id NUMBER,
337 X_Salesrep_Id NUMBER,
338 X_Advance_Arrears_Ind VARCHAR2,
339 X_Period_Name VARCHAR2,
340 X_Start_Date DATE,
341 X_Next_Due_Date DATE,
342 X_Last_Update_Date DATE,
343 X_Last_Updated_By NUMBER,
344 X_Last_Update_Login NUMBER,
345 X_Bill_To_Contact_Id NUMBER,
346 X_Ship_To_Customer_Id NUMBER,
347 X_Ship_To_Site_Use_Id NUMBER,
348 X_Ship_To_Contact_Id NUMBER,
349 X_Reminder_Days NUMBER,
350 X_Receipt_Method_Id NUMBER,
351 X_Bank_Account_Id NUMBER,
352 X_Generate_Sequence NUMBER,
353 X_End_Date DATE,
354 X_Review_Date DATE,
355 X_Previous_Due_Date DATE,
356 X_Suppress_Inv_Print VARCHAR2,
357 X_Comments VARCHAR2,
358 X_Description VARCHAR2,
359 X_Ship_To_Address_Id NUMBER,
360 X_Bill_To_Address_Id NUMBER,
361 X_term_id NUMBER,
362 X_Currency_code VARCHAR2,
363 X_Default_Invoicing_Rule VARCHAR2,
364 /*MOAC Impact Bug No 5905216 - Start*/
365 X_Legal_Entity_Id NUMBER,
366 X_Payment_Trxn_Extension_Id NUMBER
367 /*MOAC Impact Bug No 5905216 - End*/
368 ) IS
369 BEGIN
370 UPDATE IGI_RPI_STANDING_CHARGES
371 SET
372 standing_charge_id = X_Standing_Charge_Id,
373 charge_reference = X_Charge_Reference,
374 set_of_books_id = X_Set_Of_Books_Id,
375 standing_charge_date = X_Standing_Charge_Date,
376 status = X_Status,
377 bill_to_customer_id = X_Bill_To_Customer_Id,
378 bill_to_site_use_id = X_Bill_To_Site_Use_Id,
379 batch_source_id = X_Batch_Source_Id,
380 cust_trx_type_id = X_Cust_Trx_Type_Id,
381 salesrep_id = X_Salesrep_Id,
382 advance_arrears_ind = X_Advance_Arrears_Ind,
383 period_name = X_Period_Name,
384 start_date = X_Start_Date,
385 next_due_date = X_Next_Due_Date,
386 last_update_date = X_Last_Update_Date,
387 last_updated_by = X_Last_Updated_By,
388 last_update_login = X_Last_Update_Login,
389 bill_to_contact_id = X_Bill_To_Contact_Id,
390 ship_to_customer_id = X_Ship_To_Customer_Id,
391 ship_to_site_use_id = X_Ship_To_Site_Use_Id,
392 ship_to_contact_id = X_Ship_To_Contact_Id,
393 reminder_days = X_Reminder_Days,
394 receipt_method_id = X_Receipt_Method_Id,
395 bank_account_id = X_Bank_Account_Id,
396 generate_sequence = X_Generate_Sequence,
397 end_date = X_End_Date,
398 review_date = X_Review_Date,
399 previous_due_date = X_Previous_Due_Date,
400 suppress_inv_print = X_Suppress_Inv_Print,
401 comments = X_Comments,
402 description = X_Description,
403 ship_to_address_id = X_Ship_To_Address_Id,
404 bill_to_address_id = X_Bill_To_Address_Id,
405 Currency_code = X_currency_code,
406 term_id = X_term_id,
407 default_invoicing_rule = X_Default_Invoicing_Rule,
408 /*MOAC Impact Bug No 5905216 - Start*/
409 legal_entity_id = X_Legal_Entity_Id,
410 payment_trxn_extension_id = X_Payment_Trxn_Extension_Id
411 /*MOAC Impact Bug No 5905216 - End*/
412 WHERE rowid = X_Rowid;
413 if (SQL%NOTFOUND) then
414 Raise NO_DATA_FOUND;
415 end if;
416 END Update_Row;
417
418 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
419 BEGIN
420 DELETE FROM IGI_RPI_STANDING_CHARGES
421 WHERE rowid = X_Rowid;
422
423 if (SQL%NOTFOUND) then
424 Raise NO_DATA_FOUND;
425 end if;
426 END Delete_Row;
427
428
429 END IGI_RPI_STANDING_CHARGES_PKG;