[Home] [Help]
PACKAGE BODY: APPS.ARP_ADJ_PKG
Source
4 /*===========================================================================+
1 PACKAGE BODY ARP_ADJ_PKG AS
2 /* $Header: ARCIADJB.pls 120.7 2006/03/03 22:54:27 hyu ship $*/
3 --
5 | PROCEDURE |
6 | insert_p |
7 | |
8 | DESCRIPTION |
9 | This procedure inserts a row into AR_ADJUSTMENTS table |
10 | |
11 | SCOPE - PUBLIC |
12 | |
13 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
14 | |
15 | ARGUMENTS : IN: |
16 | p_adj_rec - Adjustment Record structure |
17 | OUT: |
18 | p_adj_id - Adjustment Id of inserted ar_adjustments row|
19 | |
20 | RETURNS : NONE |
21 | |
22 | NOTES |
23 | |
24 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
25 | 05/02/95 - Removed the hardcoded assignment to |
26 | created_from column in insert_p proc. |
27 | It was hard coded as 'ARSUECA' |
28 | |
29 | 20-MAR-2000 J Rautiainen |
30 | Added BR project related column |
31 | LINK_TO_TRX_HIST_ID into the table |
32 | handlers. |
33 | |
34 | 31-OCT-2000 Y Rakotonirainy |
35 | Bug 1243304 : Added columns |
36 | adj_tax_acct_rule and |
37 | cons_inv_id into the table handlers. |
38 | |
39 | 03-MARCH-2006 Herve Yu
40 | Late Charge Project: new columns added for insert_row |
41 | interest_header_id, interest_line_id |
42 +===========================================================================*/
43 PROCEDURE insert_p( p_adj_rec IN ar_adjustments%ROWTYPE,
44 p_adj_id OUT NOCOPY ar_adjustments.adjustment_id%TYPE ) IS
45 l_adj_id ar_adjustments.adjustment_id%TYPE;
46 BEGIN
47 arp_standard.debug( '>>>>>>>> arp_adj_pkg.insert_p' );
48 --
49 SELECT ar_adjustments_s.nextval
50 INTO l_adj_id
51 FROM dual;
55 acctd_amount,
52 --
53 INSERT INTO ar_adjustments (
54 adjustment_id,
56 adjustment_type,
57 amount,
58 code_combination_id,
59 created_by,
60 creation_date,
61 gl_date,
62 last_updated_by,
63 last_update_date,
64 set_of_books_id,
65 status,
66 type,
67 created_from,
68 adjustment_number,
69 apply_date,
70 approved_by,
71 associated_cash_receipt_id,
72 automatically_generated,
73 batch_id,
74 chargeback_customer_trx_id,
75 comments,
76 customer_trx_id,
77 customer_trx_line_id,
78 distribution_set_id,
79 freight_adjusted,
80 gl_posted_date,
81 last_update_login,
82 line_adjusted,
83 payment_schedule_id,
84 postable,
85 posting_control_id,
86 reason_code,
87 receivables_charges_adjusted,
88 receivables_trx_id,
89 subsequent_trx_id,
90 tax_adjusted,
91 attribute_category,
92 attribute1,
93 attribute2,
94 attribute3,
95 attribute4,
96 attribute5,
97 attribute6,
98 attribute7,
99 attribute8,
100 attribute9,
101 attribute10,
102 attribute11,
103 attribute12,
104 attribute13,
105 attribute14,
106 attribute15,
107 ussgl_transaction_code,
108 ussgl_transaction_code_context,
109 request_id,
110 program_update_date,
111 program_id,
112 program_application_id,
113 doc_sequence_id,
114 doc_sequence_value,
115 associated_application_id,
116 link_to_trx_hist_id,
117 adj_tax_acct_rule,
118 cons_inv_id
119 ,org_id
120 --{Late Charge Project
121 ,interest_header_id
122 ,interest_line_id )
123 VALUES ( l_adj_id,
124 p_adj_rec.acctd_amount,
125 p_adj_rec.adjustment_type,
126 p_adj_rec.amount,
127 p_adj_rec.code_combination_id,
128 arp_standard.profile.user_id,
129 SYSDATE,
130 p_adj_rec.gl_date,
131 arp_standard.profile.user_id,
132 SYSDATE,
133 p_adj_rec.set_of_books_id,
134 p_adj_rec.status,
135 p_adj_rec.type,
136 p_adj_rec.created_from,
137 ar_adjustment_number_s.nextval,
138 p_adj_rec.apply_date,
139 p_adj_rec.approved_by,
140 p_adj_rec.associated_cash_receipt_id,
141 p_adj_rec.automatically_generated,
142 p_adj_rec.batch_id,
143 p_adj_rec.chargeback_customer_trx_id,
144 p_adj_rec.comments,
145 p_adj_rec.customer_trx_id,
146 p_adj_rec.customer_trx_line_id,
147 p_adj_rec.distribution_set_id,
148 p_adj_rec.freight_adjusted,
149 p_adj_rec.gl_posted_date,
150 NVL( arp_standard.profile.last_update_login,
151 p_adj_rec.last_update_login ),
152 p_adj_rec.line_adjusted,
153 p_adj_rec.payment_schedule_id,
154 p_adj_rec.postable,
155 p_adj_rec.posting_control_id,
156 p_adj_rec.reason_code,
157 p_adj_rec.receivables_charges_adjusted,
158 p_adj_rec.receivables_trx_id,
159 p_adj_rec.subsequent_trx_id,
160 p_adj_rec.tax_adjusted,
161 p_adj_rec.attribute_category,
162 p_adj_rec.attribute1,
163 p_adj_rec.attribute2,
164 p_adj_rec.attribute3,
165 p_adj_rec.attribute4,
166 p_adj_rec.attribute5,
167 p_adj_rec.attribute6,
168 p_adj_rec.attribute7,
169 p_adj_rec.attribute8,
170 p_adj_rec.attribute9,
171 p_adj_rec.attribute10,
172 p_adj_rec.attribute11,
173 p_adj_rec.attribute12,
174 p_adj_rec.attribute13,
175 p_adj_rec.attribute14,
176 p_adj_rec.attribute15,
177 p_adj_rec.ussgl_transaction_code,
178 p_adj_rec.ussgl_transaction_code_context,
179 NVL( arp_standard.profile.request_id,
180 p_adj_rec.request_id ),
181 DECODE( arp_standard.profile.program_id,
182 NULL, NULL,
183 SYSDATE
184 ),
185 NVL( arp_standard.profile.program_id, p_adj_rec.program_id ),
186 NVL( arp_standard.profile.program_application_id,
187 p_adj_rec.program_application_id ),
188 p_adj_rec.doc_sequence_id,
189 p_adj_rec.doc_sequence_value,
190 p_adj_rec.associated_application_id,
191 p_adj_rec.link_to_trx_hist_id,
192 p_adj_rec.adj_tax_acct_rule,
193 p_adj_rec.cons_inv_id
194 ,arp_standard.sysparm.org_id /* SSA changes anuj */
195 --{Late Charge Project
196 ,p_adj_rec.interest_header_id
197 ,p_adj_rec.interest_line_id
198 );
199 --
200 /*-------------------------------------------+
201 | Call central MRC library for insertion |
202 | into MRC tables |
203 +-------------------------------------------*/
204 --{BUG#4301323
205 -- ar_mrc_engine.maintain_mrc_data( p_event_mode => 'INSERT',
209 --}
206 -- p_table_name => 'AR_ADJUSTMENTS',
207 -- p_mode => 'SINGLE',
208 -- p_key_value => l_adj_id);
210 p_adj_id := l_adj_id;
211 arp_standard.debug( '<<<<<<<< arp_adj_pkg.insert_p' );
212 EXCEPTION
213 WHEN OTHERS THEN
214 arp_standard.debug( 'EXCEPTION: arp_adj_pkg.insert_p' );
215 RAISE;
216 END;
217 --
218 /*===========================================================================+
219 | PROCEDURE |
220 | update_p |
221 | |
222 | DESCRIPTION |
223 | This function updates a row into AR_ADJUSTMENTS table |
224 | |
225 | SCOPE - PUBLIC |
226 | |
227 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
228 | |
229 | ARGUMENTS : IN: |
230 | p_adj_rec - Adjustment Record structure |
231 | |
232 | RETURNS : NONE |
233 | |
234 | NOTES |
235 | |
236 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
237 | |
238 | 20-MAR-2000 J Rautiainen |
239 | Added BR project related column |
240 | LINK_TO_TRX_HIST_ID into the table |
241 | handlers. |
242 | |
243 | 31-OCT-2000 Y Rakotonirainy |
244 | Bug 1243304 : Added columns |
245 | adj_tax_acct_rule and |
246 | cons_inv_id into the table handlers. |
247 +===========================================================================*/
248 PROCEDURE update_p( p_adj_rec IN AR_ADJUSTMENTS%ROWTYPE ) IS
249 BEGIN
250 arp_standard.debug( '>>>>>>>> arp_adj_pkg.update_p' );
251 --
252 UPDATE ar_adjustments SET
253 acctd_amount = p_adj_rec.acctd_amount,
254 adjustment_type = p_adj_rec.adjustment_type,
255 amount = p_adj_rec.amount,
256 code_combination_id = p_adj_rec.code_combination_id,
257 gl_date = p_adj_rec.gl_date,
258 last_updated_by = arp_standard.profile.user_id,
259 last_update_date = SYSDATE,
260 set_of_books_id = p_adj_rec.set_of_books_id,
261 status = p_adj_rec.status,
262 type = p_adj_rec.type,
263 adjustment_number = p_adj_rec.adjustment_number,
264 apply_date = p_adj_rec.apply_date,
265 approved_by = p_adj_rec.approved_by,
266 associated_cash_receipt_id =
267 p_adj_rec.associated_cash_receipt_id,
268 automatically_generated =
269 p_adj_rec.automatically_generated,
270 batch_id = p_adj_rec.batch_id,
271 chargeback_customer_trx_id =
272 p_adj_rec.chargeback_customer_trx_id,
273 comments = p_adj_rec.comments,
274 customer_trx_id = p_adj_rec.customer_trx_id,
275 customer_trx_line_id = p_adj_rec.customer_trx_line_id,
276 distribution_set_id = p_adj_rec.distribution_set_id,
277 freight_adjusted = p_adj_rec.freight_adjusted,
278 gl_posted_date = p_adj_rec.gl_posted_date,
279 last_update_login = NVL( arp_standard.profile.last_update_login,
280 p_adj_rec.last_update_login ),
281 line_adjusted = p_adj_rec.line_adjusted,
282 payment_schedule_id = p_adj_rec.payment_schedule_id,
283 postable = p_adj_rec.postable,
284 posting_control_id = p_adj_rec.posting_control_id,
285 reason_code = p_adj_rec.reason_code,
286 receivables_charges_adjusted =
287 p_adj_rec.receivables_charges_adjusted,
288 receivables_trx_id = p_adj_rec.receivables_trx_id,
289 subsequent_trx_id = p_adj_rec.subsequent_trx_id,
290 tax_adjusted = p_adj_rec.tax_adjusted,
291 attribute_category = p_adj_rec.attribute_category,
292 attribute1 = p_adj_rec.attribute1,
293 attribute2 = p_adj_rec.attribute2,
294 attribute3 = p_adj_rec.attribute3,
295 attribute4 = p_adj_rec.attribute4,
296 attributE5 = p_adj_rec.attribute5,
297 attributE6 = p_adj_rec.attribute6,
298 attributE7 = p_adj_rec.attribute7,
299 attributE8 = p_adj_rec.attribute8,
300 attributE9 = p_adj_rec.attribute9,
301 attributE10 = p_adj_rec.attribute10,
302 attributE11 = p_adj_rec.attribute11,
303 attributE12 = p_adj_rec.attribute12,
304 attributE13 = p_adj_rec.attribute13,
305 attributE14 = p_adj_rec.attribute14,
306 attributE15 = p_adj_rec.attribute15,
307 ussgl_transaction_code =
308 p_adj_rec.ussgl_transaction_code,
309 ussgl_transaction_code_context =
310 p_adj_rec.ussgl_transaction_code_context,
311 request_id = NVL( arp_standard.profile.request_id,
312 p_adj_rec.request_id ),
313 program_update_date =
314 DECODE( arp_standard.profile.program_id,
315 NULL, NULL,
316 SYSDATE
317 ),
318 program_id = NVL( arp_standard.profile.program_id,
319 p_adj_rec.program_id ),
323 doc_sequence_id = p_adj_rec.doc_sequence_id,
320 program_application_id =
321 NVL( arp_standard.profile.program_application_id,
322 p_adj_rec.program_application_id ),
324 doc_sequence_value = p_adj_rec.doc_sequence_value,
325 associated_application_id =
326 p_adj_rec.associated_application_id,
327 link_to_trx_hist_id = p_adj_rec.link_to_trx_hist_id,
328 adj_tax_acct_rule = p_adj_rec.adj_tax_acct_rule,
329 cons_inv_id = p_adj_rec.cons_inv_id
330 WHERE adjustment_id = p_adj_rec.adjustment_id;
331 --
332 /*----------------------------------------------------+
333 | Call central MRC library for the generic update |
334 | made above. This is done here rather then in |
335 | the generic update as the where clause changes |
336 | and that information is needed for the MRC engine |
337 +----------------------------------------------------*/
338 --{BUG4301323
339 -- ar_mrc_engine.maintain_mrc_data(
340 -- p_event_mode => 'UPDATE',
341 -- p_table_name => 'AR_ADJUSTMENTS',
342 -- p_mode => 'SINGLE',
343 -- p_key_value => p_adj_rec.adjustment_id
344 -- );
345 --}
346 --
347 arp_standard.debug( '<<<<<<<< arp_adj_pkg.update_p' );
348 EXCEPTION
349 WHEN OTHERS THEN
350 arp_standard.debug( 'EXCEPTION: arp_adj_pkg.update_p' );
351 RAISE;
352 END;
353 --
354 /*===========================================================================+
355 | PROCEDURE |
356 | delete_p |
357 | |
358 | DESCRIPTION |
359 | This function deletes a row from AR_ADJUSTMENTS table |
360 | |
361 | SCOPE - PUBLIC |
362 | |
363 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
364 | |
365 | ARGUMENTS : IN: |
366 | p_adj_id - Adjustment Id to delete a row from |
367 | AR_ADJUSTMENTS |
368 | |
369 | RETURNS : NONE |
370 | |
371 | NOTES |
372 | |
373 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
374 | |
375 +===========================================================================*/
376 PROCEDURE delete_p( p_adj_id IN AR_ADJUSTMENTS.ADJUSTMENT_ID%TYPE ) IS
377 BEGIN
378 arp_standard.debug( '>>>>>>>> arp_adj_pkg.delete_p' );
379 --
380 DELETE FROM ar_adjustments
381 WHERE adjustment_id = p_adj_id;
382
383 /*---------------------------------+
384 | Calling central MRC library |
385 | for MRC Integration |
386 +---------------------------------*/
387 --{BUG4301323
388 -- ar_mrc_engine.maintain_mrc_data(
389 -- p_event_mode => 'DELETE',
390 -- p_table_name => 'AR_ADJUSTMENTS',
391 -- p_mode => 'SINGLE',
392 -- p_key_value => p_adj_id);
393 --}
394 --
395 arp_standard.debug( '<<<<<<<< arp_adj_pkg.delete_p' );
396 EXCEPTION
397 WHEN OTHERS THEN
398 arp_standard.debug( 'EXCEPTION: arp_adj_pkg.delete_p' );
399 RAISE;
400 END;
401 --
402 /*===========================================================================+
403 | PROCEDURE |
404 | lock_p |
405 | |
406 | DESCRIPTION |
407 | This function locks a row in AR_ADJUSTMENTS table |
408 | |
409 | SCOPE - PUBLIC |
410 | |
411 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
412 | |
413 | ARGUMENTS : IN: |
414 | p_adj_id - Adjustment Id of row to be locked in |
415 | AR_ADJUSTMENTS table |
416 | |
417 | RETURNS : NONE |
418 | |
419 | NOTES |
420 | |
421 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
422 | |
423 +===========================================================================*/
424 PROCEDURE lock_p( p_adj_id IN AR_ADJUSTMENTS.ADJUSTMENT_ID%TYPE ) IS
425 l_adj_id AR_ADJUSTMENTS.ADJUSTMENT_ID%TYPE;
426 BEGIN
427 arp_standard.debug( '>>>>>>>> arp_adj_pkg.lock_p' );
428 --
429 SELECT adjustment_id
430 INTO l_adj_id
431 FROM ar_adjustments
432 WHERE adjustment_id = p_adj_id
433 FOR UPDATE OF STATUS NOWAIT;
434 --
435 arp_standard.debug( '<<<<<<<< arp_adj_pkg.lock_p' );
436 EXCEPTION
437 WHEN OTHERS THEN
438 arp_standard.debug( 'EXCEPTION: arp_adj_pkg.lock_p' );
439 RAISE;
440 END;
441 --
442 /*===========================================================================+
443 | PROCEDURE |
444 | fetch_p |
445 | |
446 | DESCRIPTION |
447 | This function fetches a row from AR_ADJUSTMENTS table |
448 | |
449 | SCOPE - PUBLIC |
450 | |
451 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED - NONE |
452 | |
453 | ARGUMENTS : IN: |
454 | p_adj_id - Adjustment Id of row to be fetched from |
455 | AR_ADJUSTMENTS table |
456 | OUT: |
457 | p_adj_rec - Adjustment Record structure |
458 | |
459 | RETURNS : NONE |
460 | |
461 | NOTES |
462 | |
463 | MODIFICATION HISTORY - Created by Ganesh Vaidee - 04/25/95 |
464 | |
465 +===========================================================================*/
466 PROCEDURE fetch_p( p_adj_id IN ar_adjustments.adjustment_id%TYPE,
467 p_adj_rec OUT NOCOPY ar_adjustments%ROWTYPE ) IS
468 BEGIN
469 arp_standard.debug( '>>>>>>>> arp_adj_pkg.fetch_p' );
470 --
471 SELECT *
472 INTO p_adj_rec
473 FROM ar_adjustments
474 WHERE adjustment_id = p_adj_id;
475 --
476 arp_standard.debug( '<<<<<<<< arp_adj_pkg.fetch_p' );
477 EXCEPTION
478 WHEN OTHERS THEN
479 arp_standard.debug( 'EXCEPTION: arp_adj_pkg.fetch_p' );
480 RAISE;
481 END;
482 --
483 END ARP_ADJ_PKG;
484 --