[Home] [Help]
PACKAGE BODY: APPS.ARP_CTL_PKG
Source
1 PACKAGE BODY ARP_CTL_PKG AS
2 /* $Header: ARTICTLB.pls 120.45.12020000.4 2013/02/26 11:43:08 dgaurab ship $ */
3 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4
5 /*--------------------------------------------------------+
6 | Dummy constants for use in update and lock operations |
7 +--------------------------------------------------------*/
8
9 AR_TEXT_DUMMY CONSTANT VARCHAR2(10) := '~~!@#$*&^';
10 AR_TEXT3_DUMMY CONSTANT VARCHAR2(10) := '~!@';
11 AR_FLAG_DUMMY CONSTANT VARCHAR2(10) := '~';
12 AR_NUMBER_DUMMY CONSTANT NUMBER(15) := -999999999999999;
13 AR_DATE_DUMMY CONSTANT DATE := to_date(1, 'J');
14
15 /*---------------------------------------------------------------+
16 | Package global variables to hold the parsed update cursors. |
17 | This allows the cursors to be reused without being reparsed. |
18 +---------------------------------------------------------------*/
19
20 pg_cursor1 integer := '';
21 pg_cursor2 integer := '';
22
23 /*-------------------------------------+
24 | WHO column values from FND_GLOBAL |
25 +-------------------------------------*/
26
27 pg_user_id number;
28 pg_conc_login_id number;
29 pg_login_id number;
30 pg_prog_appl_id number;
31 pg_conc_program_id number;
32
33 --{BUG#3339072
34 TYPE ctlrec IS RECORD (
35 customer_trx_line_id DBMS_SQL.NUMBER_TABLE,
36 customer_trx_id DBMS_SQL.NUMBER_TABLE,
37 created_by DBMS_SQL.VARCHAR2_TABLE,
38 creation_date DBMS_SQL.DATE_TABLE,
39 last_updated_by DBMS_SQL.VARCHAR2_TABLE,
40 last_update_date DBMS_SQL.DATE_TABLE,
41 last_update_login DBMS_SQL.VARCHAR2_TABLE,
42 line_number DBMS_SQL.NUMBER_TABLE,
43 line_type DBMS_SQL.VARCHAR2_TABLE,
44 set_of_books_id DBMS_SQL.NUMBER_TABLE,
45 accounting_rule_id DBMS_SQL.NUMBER_TABLE,
46 autorule_complete_flag DBMS_SQL.VARCHAR2_TABLE,
47 last_period_to_credit DBMS_SQL.NUMBER_TABLE,
48 description DBMS_SQL.VARCHAR2_TABLE,
49 initial_customer_trx_line_id DBMS_SQL.NUMBER_TABLE,
50 inventory_item_id DBMS_SQL.NUMBER_TABLE,
51 item_exception_rate_id DBMS_SQL.NUMBER_TABLE,
52 memo_line_id DBMS_SQL.NUMBER_TABLE,
53 reason_code DBMS_SQL.VARCHAR2_TABLE,
54 previous_customer_trx_id DBMS_SQL.NUMBER_TABLE,
55 previous_customer_trx_line_id DBMS_SQL.NUMBER_TABLE,
56 link_to_cust_trx_line_id DBMS_SQL.NUMBER_TABLE,
57 unit_standard_price DBMS_SQL.NUMBER_TABLE,
58 unit_selling_price DBMS_SQL.NUMBER_TABLE,
59 gross_unit_selling_price DBMS_SQL.NUMBER_TABLE,-- Bug 7389126 KALYAN
60 gross_extended_amount DBMS_SQL.NUMBER_TABLE, -- 6882394
61 original_extended_amount DBMS_SQL.NUMBER_TABLE,-- 6882394
62 original_revenue_amount DBMS_SQL.NUMBER_TABLE,-- 6882394
63 quantity_credited DBMS_SQL.NUMBER_TABLE,
64 quantity_invoiced DBMS_SQL.NUMBER_TABLE, -- Bug 6990227
65 extended_amount DBMS_SQL.NUMBER_TABLE,
66 revenue_amount DBMS_SQL.NUMBER_TABLE,
67 sales_order DBMS_SQL.VARCHAR2_TABLE,
68 sales_order_date DBMS_SQL.DATE_TABLE,
69 sales_order_line DBMS_SQL.VARCHAR2_TABLE,
70 sales_order_revision DBMS_SQL.NUMBER_TABLE,
71 sales_order_source DBMS_SQL.VARCHAR2_TABLE,
72 tax_exemption_id DBMS_SQL.NUMBER_TABLE,
73 tax_precedence DBMS_SQL.NUMBER_TABLE,
74 tax_rate DBMS_SQL.NUMBER_TABLE,
75 uom_code DBMS_SQL.VARCHAR2_TABLE,
76 default_ussgl_transaction_code DBMS_SQL.VARCHAR2_TABLE,
77 default_ussgl_trx_code_context DBMS_SQL.VARCHAR2_TABLE,
78 sales_tax_id DBMS_SQL.NUMBER_TABLE,
79 location_segment_id DBMS_SQL.NUMBER_TABLE,
80 vat_tax_id DBMS_SQL.NUMBER_TABLE,
81 amount_includes_tax_flag DBMS_SQL.VARCHAR2_TABLE,
82 warehouse_id DBMS_SQL.NUMBER_TABLE,
83 taxable_amount DBMS_SQL.NUMBER_TABLE,
84 translated_description DBMS_SQL.VARCHAR2_TABLE,
85 org_id DBMS_SQL.NUMBER_TABLE,
86 ship_to_customer_id DBMS_SQL.NUMBER_TABLE,
87 ship_to_address_id DBMS_SQL.NUMBER_TABLE,
88 ship_to_site_use_id DBMS_SQL.NUMBER_TABLE,
89 ship_to_contact_id DBMS_SQL.NUMBER_TABLE,
90 tax_classification_code DBMS_SQL.VARCHAR2_TABLE,
91 historical_flag DBMS_SQL.VARCHAR2_TABLE,
92 memo_line_type DBMS_SQL.VARCHAR2_TABLE);
93 --}
94 /*===========================================================================+
95 | FUNCTION |
96 | calculate_prorated_tax_amount |
97 | |
98 | DESCRIPTION |
99 | This function will prorate the tax across lines when the |
100 | credit transaction is of type TAX ONLY |
101 | |
102 | SCOPE - PUBLIC |
103 | |
104 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
105 | arp_util.debug |
106 | |
107 | ARGUMENTS : IN: |
108 | p_mode |
109 | p_tax_amount |
110 | p_customer_trx_id |
111 | p_customer_trx_line_id |
112 | RETURNS : Number |
113 | |
114 | NOTES |
115 | |
116 | MODIFICATION HISTORY |
117 | 06-FEB-2008 Manohar Kolla V K Created |
118 | |
119 +===========================================================================*/
120
121 FUNCTION calculate_prorated_tax_amount (p_mode IN varchar2,
122 p_tax_amount IN ra_customer_trx_lines.extended_amount%type,
123 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
124 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%type)
125 RETURN NUMBER
126 IS
127 l_tax_recoverable ra_customer_trx_lines.tax_recoverable%type;
128 l_total_tax_recoverable ra_customer_trx_lines.tax_recoverable%type;
129 l_precision FND_CURRENCIES.precision%type;
130 l_mau FND_CURRENCIES.minimum_accountable_unit%type;
131 l_tax_amount ra_customer_trx_lines.extended_amount%type;
132
133 BEGIN
134 IF PG_DEBUG in ('Y', 'C') THEN
135 arp_util.debug( 'arp_ctl_pkg.calculate_prorated_tax_amount()+');
136 END IF;
137
138 IF p_mode = 'INSERT_NO_LINE' then
139
140 select sum(decode(ct.complete_flag , 'N', 0,
141 nvl(ctl.tax_recoverable,
142 (select sum(ctl_tax.extended_amount) from ra_customer_trx_lines_all ctl_tax
143 where ctl_tax.customer_trx_id = ctl.customer_trx_id
144 and ctl_tax.link_to_cust_trx_line_id = ctl.customer_trx_line_id
145 and ctl_tax.line_type = 'TAX')))) tax_recoverable
146 INTO l_tax_recoverable
147 from ra_customer_trx_lines_all orig_ctl,
148 ra_customer_trx_lines_all ctl,
149 ra_customer_trx_lines_all cm_ctl,
150 ra_customer_trx_all ct
151 where (ctl.customer_trx_line_id = orig_ctl.customer_trx_line_id
152 OR ( ctl.previous_customer_trx_line_id IS NOT NULL
153 AND ctl.previous_customer_trx_line_id = orig_ctl.customer_trx_line_id)
154 )
155 and orig_ctl.customer_trx_id = cm_ctl.previous_customer_trx_id
156 and orig_ctl.customer_trx_line_id = cm_ctl.previous_customer_trx_line_id
157 and cm_ctl.customer_trx_id = p_customer_trx_id
158 and cm_ctl.customer_trx_line_id = p_customer_trx_line_id
159 and ctl.line_type = 'LINE'
160 and ct.customer_trx_id = ctl.customer_trx_id
161 group by orig_ctl.customer_trx_line_id, ctl.line_type;
162
163 select sum(decode(ct.complete_flag , 'N', 0,
164 nvl(ctl.tax_recoverable, (select sum(ctl_tax.extended_amount) from ra_customer_trx_lines_all ctl_tax
165 where ctl_tax.customer_trx_id = ctl.customer_trx_id
166 and ctl_tax.link_to_cust_trx_line_id = ctl.customer_trx_line_id
167 and ctl_tax.line_type = 'TAX')))) total_tax_recoverable
168 INTO l_total_tax_recoverable
169 from ra_customer_trx_lines_all orig_ctl,
170 ra_customer_trx_lines_all ctl,
171 ra_customer_trx_lines_all cm_ctl,
172 ra_customer_trx_all ct
173 where (ctl.customer_trx_line_id = orig_ctl.customer_trx_line_id
174 OR ( ctl.previous_customer_trx_line_id IS NOT NULL
175 AND ctl.previous_customer_trx_line_id = orig_ctl.customer_trx_line_id)
176 )
177 and orig_ctl.customer_trx_id = cm_ctl.previous_customer_trx_id
178 and orig_ctl.customer_trx_line_id = cm_ctl.previous_customer_trx_line_id
179 and cm_ctl.customer_trx_id = p_customer_trx_id
180 and ctl.line_type = 'LINE'
181 and ct.customer_trx_id = ctl.customer_trx_id;
182
183 if l_total_tax_recoverable = 0 then
184 l_total_tax_recoverable := 1;
185 end if;
186
187 select
188 CUR.precision,
189 CUR.minimum_accountable_unit
190 into l_precision,
191 l_mau
192 from RA_CUSTOMER_TRX TRX,
193 FND_CURRENCIES CUR
194 where TRX.customer_trx_id = p_customer_trx_id
195 and TRX.invoice_currency_code = CUR.currency_code;
196
197 l_tax_amount := p_tax_amount * (l_tax_recoverable / l_total_tax_recoverable);
198
199 IF l_precision is not null
200 THEN
201 l_tax_amount := round(l_tax_amount, l_precision);
202 ELSE
203 l_tax_amount := (round(l_tax_amount / l_mau)
204 * l_mau);
205 END IF;
206 ELSE
207 l_tax_amount := p_tax_amount;
208 END IF;
209
210 IF PG_DEBUG in ('Y', 'C') THEN
211 arp_util.debug( 'l_tax_recoverable = ' || l_tax_recoverable);
212 arp_util.debug( 'l_total_tax_recoverable = ' || l_total_tax_recoverable);
213 arp_util.debug( 'l_tax_amount = ' || l_tax_amount);
214 END IF;
215
216 IF PG_DEBUG in ('Y', 'C') THEN
217 arp_util.debug( 'arp_ctl_pkg.calculate_prorated_tax_amount()-');
218 END IF;
219
220 RETURN l_tax_amount;
221 EXCEPTION
222 WHEN NO_DATA_FOUND THEN
223 return null;
224 WHEN OTHERS THEN
225 IF PG_DEBUG in ('Y', 'C') THEN
226 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.calculate_prorated_tax_amount');
227 arp_util.debug( '');
228 arp_util.debug( 'p_customer_trx_id = '||p_customer_trx_id);
229 arp_util.debug( 'p_customer_trx_line_id = '||p_customer_trx_line_id);
230 arp_util.debug( 'p_tax_amount = '||p_tax_amount);
231 arp_util.debug( 'p_mode = '||p_mode);
232 END IF;
233 RAISE;
234
235 END calculate_prorated_tax_amount;
236
237 /*===========================================================================+
238 | PROCEDURE |
239 | bind_line_variables |
240 | |
241 | DESCRIPTION |
242 | Binds variables from the record variable to the bind variables |
243 | in the dynamic SQL update statement. |
244 | |
245 | SCOPE - PRIVATE |
246 | |
247 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
248 | dbms_sql.bind_variable |
249 | arp_util.debug |
250 | |
251 | ARGUMENTS : IN: |
252 | p_update_cursor - ID of the update cursor |
253 | p_line_rec - ra_customer_trx_lines record |
254 | OUT: |
255 | None |
256 | |
257 | RETURNS : NONE |
258 | |
259 | NOTES |
260 | |
261 | MODIFICATION HISTORY |
262 | 06-JUN-95 Charlie Tomberg Created |
263 | |
264 | Rel. 11 Changes: |
265 | ---------------- |
266 | 07-22-97 OSTEINME added code to bind variables for |
267 | three new database columns: |
268 | - gross_unit_selling_price |
269 | - gross_extended_amount |
270 | - amount_includes_tax_flag |
271 | |
272 | 08-20-97 KTANG bind variables for |
273 | global_attribute_category and |
274 | global_attribute[1-20] for global |
275 | descriptive flexfield |
276 | |
277 | 10-JAN-99 Saloni Shah added warehouse_id for global tax |
278 | engine changes |
279 | 22-MAR-99 Debbie Jancis added translated_description for |
280 | MLS project. |
281 | |
282 | 20-MAR-2000 J Rautiainen Added BR project related columns |
283 | EXTENDED_ACCTD_AMOUNT, BR_REF_CUSTOMER_TRX_ID, |
284 | BR_REF_PAYMENT_SCHEDULE_ID and BR_ADJUSTMENT_ID |
285 | into table handlers |
286 | |
287 | 31-OCT-2000 Y Rakotonirainy Bug 1243304 : Added column wh_update_date |
288 | into the table handlers. |
289 | 04-NOV-2005 MRAYMOND 4713671 - added ship_to and tax columns
290 +===========================================================================*/
291
292
293 PROCEDURE bind_line_variables(p_update_cursor IN integer,
294 p_line_rec IN ra_customer_trx_lines%rowtype) IS
295
296 BEGIN
297
298 arp_util.debug('arp_ctl_pkg.bind_line_variables()+');
299
300
301 /*------------------+
302 | Dummy constants |
303 +------------------*/
304
305 dbms_sql.bind_variable(p_update_cursor, ':ar_text_dummy',
306 AR_TEXT_DUMMY);
307
308 dbms_sql.bind_variable(p_update_cursor, ':ar_text3_dummy',
309 AR_TEXT3_DUMMY);
310
311 dbms_sql.bind_variable(p_update_cursor, ':ar_flag_dummy',
312 AR_FLAG_DUMMY);
313
314 dbms_sql.bind_variable(p_update_cursor, ':ar_number_dummy',
315 AR_NUMBER_DUMMY);
316
317 dbms_sql.bind_variable(p_update_cursor, ':ar_date_dummy',
318 AR_DATE_DUMMY);
319
320 /*------------------+
321 | WHO variables |
322 +------------------*/
323
324 dbms_sql.bind_variable(p_update_cursor, ':pg_user_id',
325 pg_user_id);
326
327 dbms_sql.bind_variable(p_update_cursor, ':pg_login_id',
328 pg_login_id);
329
330 dbms_sql.bind_variable(p_update_cursor, ':pg_conc_login_id',
331 pg_conc_login_id);
332
333
334 /*----------------------------------------------+
335 | Bind variables for all columns in the table |
336 +----------------------------------------------*/
337
338
339 dbms_sql.bind_variable(p_update_cursor, ':customer_trx_line_id',
340 p_line_rec.customer_trx_line_id);
341
342 dbms_sql.bind_variable(p_update_cursor, ':customer_trx_id',
343 p_line_rec.customer_trx_id);
344
345 dbms_sql.bind_variable(p_update_cursor, ':line_number',
346 p_line_rec.line_number);
347
348 dbms_sql.bind_variable(p_update_cursor, ':line_type',
349 p_line_rec.line_type);
350
351 dbms_sql.bind_variable(p_update_cursor, ':quantity_credited',
352 p_line_rec.quantity_credited);
353
354 dbms_sql.bind_variable(p_update_cursor, ':quantity_invoiced',
355 p_line_rec.quantity_invoiced);
356
357 dbms_sql.bind_variable(p_update_cursor, ':quantity_ordered',
358 p_line_rec.quantity_ordered);
359
360 dbms_sql.bind_variable(p_update_cursor, ':unit_selling_price',
361 p_line_rec.unit_selling_price);
362
363 dbms_sql.bind_variable(p_update_cursor, ':unit_standard_price',
364 p_line_rec.unit_standard_price);
365
366 dbms_sql.bind_variable(p_update_cursor, ':revenue_amount',
367 p_line_rec.revenue_amount);
368
369 dbms_sql.bind_variable(p_update_cursor, ':extended_amount',
370 p_line_rec.extended_amount);
371
372 dbms_sql.bind_variable(p_update_cursor, ':memo_line_id',
373 p_line_rec.memo_line_id);
374
375 dbms_sql.bind_variable(p_update_cursor, ':inventory_item_id',
376 p_line_rec.inventory_item_id);
377
378 dbms_sql.bind_variable(p_update_cursor, ':item_exception_rate_id',
379 p_line_rec.item_exception_rate_id);
380
381 dbms_sql.bind_variable(p_update_cursor, ':description',
382 p_line_rec.description);
383
384 dbms_sql.bind_variable(p_update_cursor, ':item_context',
385 p_line_rec.item_context);
386
387 dbms_sql.bind_variable(p_update_cursor, ':initial_customer_trx_line_id',
388 p_line_rec.initial_customer_trx_line_id);
389
390 dbms_sql.bind_variable(p_update_cursor, ':link_to_cust_trx_line_id',
391 p_line_rec.link_to_cust_trx_line_id);
392
393 dbms_sql.bind_variable(p_update_cursor, ':previous_customer_trx_id',
394 p_line_rec.previous_customer_trx_id);
395
396 dbms_sql.bind_variable(p_update_cursor, ':previous_customer_trx_line_id',
397 p_line_rec.previous_customer_trx_line_id);
398
399 dbms_sql.bind_variable(p_update_cursor, ':accounting_rule_duration',
400 p_line_rec.accounting_rule_duration);
401
402 dbms_sql.bind_variable(p_update_cursor, ':accounting_rule_id',
403 p_line_rec.accounting_rule_id);
404
405 dbms_sql.bind_variable(p_update_cursor, ':rule_start_date',
406 p_line_rec.rule_start_date);
407
408 dbms_sql.bind_variable(p_update_cursor, ':autorule_complete_flag',
409 p_line_rec.autorule_complete_flag);
410
411 dbms_sql.bind_variable(p_update_cursor, ':autorule_duration_processed',
412 p_line_rec.autorule_duration_processed);
413
414 dbms_sql.bind_variable(p_update_cursor, ':reason_code',
415 p_line_rec.reason_code);
416
417 dbms_sql.bind_variable(p_update_cursor, ':last_period_to_credit',
418 p_line_rec.last_period_to_credit);
419
420 dbms_sql.bind_variable(p_update_cursor, ':sales_order',
421 p_line_rec.sales_order);
422
423 dbms_sql.bind_variable(p_update_cursor, ':sales_order_date',
424 p_line_rec.sales_order_date);
425
426 dbms_sql.bind_variable(p_update_cursor, ':sales_order_line',
427 p_line_rec.sales_order_line);
428
429 dbms_sql.bind_variable(p_update_cursor, ':sales_order_revision',
430 p_line_rec.sales_order_revision);
431
432 dbms_sql.bind_variable(p_update_cursor, ':sales_order_source',
433 p_line_rec.sales_order_source);
434
435 dbms_sql.bind_variable(p_update_cursor, ':vat_tax_id',
436 p_line_rec.vat_tax_id);
437
438 dbms_sql.bind_variable(p_update_cursor, ':tax_exempt_flag',
439 p_line_rec.tax_exempt_flag);
440
441 dbms_sql.bind_variable(p_update_cursor, ':sales_tax_id',
442 p_line_rec.sales_tax_id);
443
444 dbms_sql.bind_variable(p_update_cursor, ':location_segment_id',
445 p_line_rec.location_segment_id);
446
447 dbms_sql.bind_variable(p_update_cursor, ':tax_exempt_number',
448 p_line_rec.tax_exempt_number);
449
450 dbms_sql.bind_variable(p_update_cursor, ':tax_exempt_reason_code',
451 p_line_rec.tax_exempt_reason_code);
452
453 dbms_sql.bind_variable(p_update_cursor, ':tax_vendor_return_code',
454 p_line_rec.tax_vendor_return_code);
455
456 dbms_sql.bind_variable(p_update_cursor, ':taxable_flag',
457 p_line_rec.taxable_flag);
458
459 dbms_sql.bind_variable(p_update_cursor, ':tax_exemption_id',
460 p_line_rec.tax_exemption_id);
461
462 dbms_sql.bind_variable(p_update_cursor, ':tax_precedence',
463 p_line_rec.tax_precedence);
464
465 dbms_sql.bind_variable(p_update_cursor, ':tax_rate',
466 p_line_rec.tax_rate);
467
468 dbms_sql.bind_variable(p_update_cursor, ':uom_code',
469 p_line_rec.uom_code);
470
471 dbms_sql.bind_variable(p_update_cursor, ':autotax',
472 p_line_rec.autotax);
473
474 dbms_sql.bind_variable(p_update_cursor, ':movement_id',
475 p_line_rec.movement_id);
476
477 dbms_sql.bind_variable(p_update_cursor, ':default_ussgl_transaction_code',
478 p_line_rec.default_ussgl_transaction_code);
479
480 dbms_sql.bind_variable(p_update_cursor, ':default_ussgl_trx_code_context',
481 p_line_rec.default_ussgl_trx_code_context);
482
483 dbms_sql.bind_variable(p_update_cursor, ':interface_line_context',
484 p_line_rec.interface_line_context);
485
486 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute1',
487 p_line_rec.interface_line_attribute1);
488
489 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute2',
490 p_line_rec.interface_line_attribute2);
491
492 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute3',
493 p_line_rec.interface_line_attribute3);
494
495 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute4',
496 p_line_rec.interface_line_attribute4);
497
498 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute5',
499 p_line_rec.interface_line_attribute5);
500
501 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute6',
502 p_line_rec.interface_line_attribute6);
503
504 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute7',
505 p_line_rec.interface_line_attribute7);
506
507 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute8',
508 p_line_rec.interface_line_attribute8);
509
510 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute9',
511 p_line_rec.interface_line_attribute9);
512
513 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute10',
514 p_line_rec.interface_line_attribute10);
515
516 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute11',
517 p_line_rec.interface_line_attribute11);
518
519 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute12',
520 p_line_rec.interface_line_attribute12);
521
522 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute13',
523 p_line_rec.interface_line_attribute13);
524
525 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute14',
526 p_line_rec.interface_line_attribute14);
527
528 dbms_sql.bind_variable(p_update_cursor, ':interface_line_attribute15',
529 p_line_rec.interface_line_attribute15);
530
531 dbms_sql.bind_variable(p_update_cursor, ':attribute_category',
532 p_line_rec.attribute_category);
533
534 dbms_sql.bind_variable(p_update_cursor, ':attribute1',
535 p_line_rec.attribute1);
536
537 dbms_sql.bind_variable(p_update_cursor, ':attribute2',
538 p_line_rec.attribute2);
539
540 dbms_sql.bind_variable(p_update_cursor, ':attribute3',
541 p_line_rec.attribute3);
542
543 dbms_sql.bind_variable(p_update_cursor, ':attribute4',
544 p_line_rec.attribute4);
545
546 dbms_sql.bind_variable(p_update_cursor, ':attribute5',
547 p_line_rec.attribute5);
548
549 dbms_sql.bind_variable(p_update_cursor, ':attribute6',
550 p_line_rec.attribute6);
551
552 dbms_sql.bind_variable(p_update_cursor, ':attribute7',
553 p_line_rec.attribute7);
554
555 dbms_sql.bind_variable(p_update_cursor, ':attribute8',
556 p_line_rec.attribute8);
557
558 dbms_sql.bind_variable(p_update_cursor, ':attribute9',
559 p_line_rec.attribute9);
560
561 dbms_sql.bind_variable(p_update_cursor, ':attribute10',
562 p_line_rec.attribute10);
563
564 dbms_sql.bind_variable(p_update_cursor, ':attribute11',
565 p_line_rec.attribute11);
566
567 dbms_sql.bind_variable(p_update_cursor, ':attribute12',
568 p_line_rec.attribute12);
569
570 dbms_sql.bind_variable(p_update_cursor, ':attribute13',
571 p_line_rec.attribute13);
572
573 dbms_sql.bind_variable(p_update_cursor, ':attribute14',
574 p_line_rec.attribute14);
575
576 dbms_sql.bind_variable(p_update_cursor, ':attribute15',
577 p_line_rec.attribute15);
578
579 dbms_sql.bind_variable(p_update_cursor, ':created_by',
580 p_line_rec.created_by);
581
582 dbms_sql.bind_variable(p_update_cursor, ':creation_date',
583 p_line_rec.creation_date);
584
585 dbms_sql.bind_variable(p_update_cursor, ':last_updated_by',
586 p_line_rec.last_updated_by);
587
588 dbms_sql.bind_variable(p_update_cursor, ':last_update_date',
589 p_line_rec.last_update_date);
590
591 dbms_sql.bind_variable(p_update_cursor, ':program_application_id',
592 p_line_rec.program_application_id);
593
594 dbms_sql.bind_variable(p_update_cursor, ':last_update_login',
595 p_line_rec.last_update_login);
596
597 dbms_sql.bind_variable(p_update_cursor, ':program_id',
598 p_line_rec.program_id);
599
600 dbms_sql.bind_variable(p_update_cursor, ':program_update_date',
601 p_line_rec.program_update_date);
602
603 dbms_sql.bind_variable(p_update_cursor, ':set_of_books_id',
604 p_line_rec.set_of_books_id);
605
606 -- Rel. 11 Changes:
607
608 dbms_sql.bind_variable(p_update_cursor, ':gross_unit_selling_price',
609 p_line_rec.gross_unit_selling_price);
610
611 dbms_sql.bind_variable(p_update_cursor, ':gross_extended_amount',
612 p_line_rec.gross_extended_amount);
613
614 dbms_sql.bind_variable(p_update_cursor, ':amount_includes_tax_flag',
615 p_line_rec.amount_includes_tax_flag);
616
617 -- For global descriptive flexfield
618
619 dbms_sql.bind_variable(p_update_cursor, ':global_attribute_category',
620 p_line_rec.global_attribute_category);
621
622 dbms_sql.bind_variable(p_update_cursor, ':global_attribute1',
623 p_line_rec.global_attribute1);
624
625 dbms_sql.bind_variable(p_update_cursor, ':global_attribute2',
626 p_line_rec.global_attribute2);
627
628 dbms_sql.bind_variable(p_update_cursor, ':global_attribute3',
629 p_line_rec.global_attribute3);
630
631 dbms_sql.bind_variable(p_update_cursor, ':global_attribute4',
632 p_line_rec.global_attribute4);
633
634 dbms_sql.bind_variable(p_update_cursor, ':global_attribute5',
635 p_line_rec.global_attribute5);
636
637 dbms_sql.bind_variable(p_update_cursor, ':global_attribute6',
638 p_line_rec.global_attribute6);
639
640 dbms_sql.bind_variable(p_update_cursor, ':global_attribute7',
641 p_line_rec.global_attribute7);
642
643 dbms_sql.bind_variable(p_update_cursor, ':global_attribute8',
644 p_line_rec.global_attribute8);
645
646 dbms_sql.bind_variable(p_update_cursor, ':global_attribute9',
647 p_line_rec.global_attribute9);
648
649 dbms_sql.bind_variable(p_update_cursor, ':global_attribute10',
650 p_line_rec.global_attribute10);
651
652 dbms_sql.bind_variable(p_update_cursor, ':global_attribute11',
653 p_line_rec.global_attribute11);
654
655 dbms_sql.bind_variable(p_update_cursor, ':global_attribute12',
656 p_line_rec.global_attribute12);
657
658 dbms_sql.bind_variable(p_update_cursor, ':global_attribute13',
659 p_line_rec.global_attribute13);
660
661 dbms_sql.bind_variable(p_update_cursor, ':global_attribute14',
662 p_line_rec.global_attribute14);
663
664 dbms_sql.bind_variable(p_update_cursor, ':global_attribute15',
665 p_line_rec.global_attribute15);
666
667 dbms_sql.bind_variable(p_update_cursor, ':global_attribute16',
668 p_line_rec.global_attribute16);
669
670 dbms_sql.bind_variable(p_update_cursor, ':global_attribute17',
671 p_line_rec.global_attribute17);
672
673 dbms_sql.bind_variable(p_update_cursor, ':global_attribute18',
674 p_line_rec.global_attribute18);
675
676 dbms_sql.bind_variable(p_update_cursor, ':global_attribute19',
677 p_line_rec.global_attribute19);
678
679 dbms_sql.bind_variable(p_update_cursor, ':global_attribute20',
680 p_line_rec.global_attribute20);
681
682 /* added for global tax engine */
683 dbms_sql.bind_variable(p_update_cursor, ':warehouse_id',
684 p_line_rec.warehouse_id);
685 dbms_sql.bind_variable(p_update_cursor, ':translated_description',
686 p_line_rec.translated_description);
687
688 /* Bug 853757 */
689 dbms_sql.bind_variable(p_update_cursor, ':taxable_amount',
690 p_line_rec.taxable_amount);
691
692 dbms_sql.bind_variable(p_update_cursor, ':extended_acctd_amount',
693 p_line_rec.extended_acctd_amount);
694
695 dbms_sql.bind_variable(p_update_cursor, ':br_ref_customer_trx_id',
696 p_line_rec.br_ref_customer_trx_id);
697
698 dbms_sql.bind_variable(p_update_cursor, ':br_ref_payment_schedule_id',
699 p_line_rec.br_ref_payment_schedule_id);
700
701 dbms_sql.bind_variable(p_update_cursor, ':br_adjustment_id',
702 p_line_rec.br_adjustment_id);
703
704 dbms_sql.bind_variable(p_update_cursor, ':wh_update_date',
705 p_line_rec.wh_update_date);
706
707 dbms_sql.bind_variable(p_update_cursor, ':payment_set_id',
708 p_line_rec.payment_set_id);
709 /* 4713671 */
710 dbms_sql.bind_variable(p_update_cursor, ':ship_to_customer_id',
711 p_line_rec.ship_to_customer_id);
712
713 dbms_sql.bind_variable(p_update_cursor, ':ship_to_site_use_id',
714 p_line_rec.ship_to_site_use_id);
715
716 dbms_sql.bind_variable(p_update_cursor, ':ship_to_contact_id',
717 p_line_rec.ship_to_contact_id);
718
719 dbms_sql.bind_variable(p_update_cursor, ':tax_classification_code',
720 p_line_rec.tax_classification_code);
721 /* 4713671 end */
722 dbms_sql.bind_variable(p_update_cursor, ':rule_end_date',
723 p_line_rec.rule_end_date);
724
725 arp_util.debug('arp_ctl_pkg.bind_line_variables()-');
726
727 EXCEPTION
728 WHEN OTHERS THEN
729 arp_util.debug('EXCEPTION: arp_ctl_pkg.bind_line_variables()');
730
731 arp_util.debug('');
732 arp_util.debug('-------- parameters for bind_line_variables() ------');
733
734 arp_util.debug('p_update_cursor = ' || p_update_cursor);
735 arp_util.debug('');
736 display_line_rec(p_line_rec);
737
738 RAISE;
739
740 END;
741
742
743 /*===========================================================================+
744 | PROCEDURE |
745 | construct_line_update_stmt |
746 | |
747 | DESCRIPTION |
748 | Copies the text of the dynamic SQL update statement into the |
749 | out paramater. The update statement does not contain a where clause |
750 | since this is the dynamic part that is added later. |
751 | |
752 | SCOPE - PRIVATE |
753 | |
754 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
755 | arp_util.debug |
756 | |
757 | ARGUMENTS : IN: |
758 | None. |
759 | OUT: |
760 | update_text - text of the update statement |
761 | |
762 | RETURNS : NONE |
763 | |
764 | NOTES |
765 | This statement only updates columns in the line record that do not |
766 | contain the dummy values that indicate that they should not be changed.|
767 | |
768 | MODIFICATION HISTORY |
769 | 06-JUN-95 Charlie Tomberg Created |
770 | |
771 | Rel. 11 Changes: |
772 | ---------------- |
773 | 07-22-97 OSTEINME added code to update three new |
774 | database columns: |
775 | - gross_unit_selling_price |
776 | - gross_extended_amount |
777 | - amount_includes_tax_flag |
778 | |
779 | 08-20-97 KTANG update global_attribute_category and |
780 | global_attribute[1-20] for global |
781 | descriptive flexfield |
782 | |
783 | 10-JAN-99 Saloni Shah added warehouse_id for global tax |
784 | engine changes |
785 | 22-MAR-99 Debbie Jancis added translated_description for MLS |
786 | |
787 | 20-MAR-2000 J Rautiainen Added BR project related columns |
788 | EXTENDED_ACCTD_AMOUNT, BR_REF_CUSTOMER_TRX_ID, |
789 | BR_REF_PAYMENT_SCHEDULE_ID and BR_ADJUSTMENT_ID |
790 | into table handlers |
791 | |
792 | 31-OCT-2000 Y Rakotonirainy Bug 1243304 : Added column wh_update_date |
793 | into the table handlers. |
794 | 04-NOV-2005 MRAYMOND 4713671 - coded ship to and classifctn fields
795 +===========================================================================*/
796
797 PROCEDURE construct_line_update_stmt( update_text OUT NOCOPY varchar2) IS
798
799 BEGIN
800 arp_util.debug('arp_ctl_pkg.construct_line_update_stmt()+');
801
802 update_text :=
803 'UPDATE ra_customer_trx_lines
804 SET customer_trx_line_id =
805 DECODE(:customer_trx_line_id,
806 :ar_number_dummy, customer_trx_line_id,
807 :customer_trx_line_id),
808 customer_trx_id =
809 DECODE(:customer_trx_id,
810 :ar_number_dummy, customer_trx_id,
811 :customer_trx_id),
812 line_number =
813 DECODE(:line_number,
814 :ar_number_dummy, line_number,
815 :line_number),
816 line_type =
817 DECODE(:line_type,
818 :ar_text_dummy, line_type,
819 :line_type),
820 quantity_credited =
821 DECODE(:quantity_credited,
822 :ar_number_dummy, quantity_credited,
823 :quantity_credited),
824 quantity_invoiced =
825 DECODE(:quantity_invoiced,
826 :ar_number_dummy, quantity_invoiced,
827 :quantity_invoiced),
828 quantity_ordered =
829 DECODE(:quantity_ordered,
830 :ar_number_dummy, quantity_ordered,
831 :quantity_ordered),
832 unit_selling_price =
833 DECODE(:unit_selling_price,
834 :ar_number_dummy, unit_selling_price,
835 :unit_selling_price),
836 unit_standard_price =
837 DECODE(:unit_standard_price,
838 :ar_number_dummy, unit_standard_price,
839 :unit_standard_price),
840 revenue_amount =
841 DECODE(:revenue_amount,
842 :ar_number_dummy,
843 /* IF the line type is LINE
844 AND the extended_amount has changed
845 THEN compute the revenue_amount based on the
846 percent of the old line amount was revenue:
847 new_revenue_amount = new_extended_amount *
848 (old_revenue_amount /
849 old_extended_amount)
850 ELSE use the old revenue_amount */
851 DECODE(
852 DECODE(
853 :line_type,
854 :ar_text_dummy, line_type,
855 :line_type
856 ) ||
857 DECODE(
858 :extended_amount,
859 :ar_number_dummy, ''Amount unchanged'',
860 null
861 ),
862 ''LINE'', arpcurr.CurrRound(
863 DECODE(extended_amount,
864 0, :extended_amount,
865 :extended_amount *
866 (
867 revenue_amount /
868 extended_amount
869 )
870 ),
871 :invoice_currency_code
872 ),
873 revenue_amount
874 ),
875 :revenue_amount),
876 extended_amount =
877 DECODE(:extended_amount,
878 :ar_number_dummy, extended_amount,
879 :extended_amount),
880 memo_line_id =
881 DECODE(:memo_line_id,
882 :ar_number_dummy, memo_line_id,
883 :memo_line_id),
884 inventory_item_id =
885 DECODE(:inventory_item_id,
886 :ar_number_dummy, inventory_item_id,
887 :inventory_item_id),
888 item_exception_rate_id =
889 DECODE(:item_exception_rate_id,
890 :ar_number_dummy, item_exception_rate_id,
891 :item_exception_rate_id),
892 description =
893 DECODE(:description,
894 :ar_text_dummy, description,
895 :description),
896 item_context =
897 DECODE(:item_context,
898 :ar_text_dummy, item_context,
899 :item_context),
900 initial_customer_trx_line_id =
901 DECODE(:initial_customer_trx_line_id,
902 :ar_number_dummy, initial_customer_trx_line_id,
903 :initial_customer_trx_line_id),
904 link_to_cust_trx_line_id =
905 DECODE(:link_to_cust_trx_line_id,
906 :ar_number_dummy, link_to_cust_trx_line_id,
907 :link_to_cust_trx_line_id),
908 previous_customer_trx_id =
909 DECODE(:previous_customer_trx_id,
910 :ar_number_dummy, previous_customer_trx_id,
911 :previous_customer_trx_id),
912 previous_customer_trx_line_id =
913 DECODE(:previous_customer_trx_line_id,
914 :ar_number_dummy, previous_customer_trx_line_id,
915 :previous_customer_trx_line_id),
916 accounting_rule_duration =
917 DECODE(:accounting_rule_duration,
918 :ar_number_dummy, accounting_rule_duration,
919 :accounting_rule_duration),
920 accounting_rule_id =
921 DECODE(:accounting_rule_id,
922 :ar_number_dummy, accounting_rule_id,
923 :accounting_rule_id),
924 rule_start_date =
925 DECODE(:rule_start_date,
926 :ar_date_dummy, rule_start_date,
927 :rule_start_date),
928 autorule_complete_flag =
929 DECODE(:autorule_complete_flag,
930 :ar_flag_dummy, autorule_complete_flag,
931 :autorule_complete_flag),
932 autorule_duration_processed =
933 DECODE(:autorule_duration_processed,
934 :ar_number_dummy, autorule_duration_processed,
935 :autorule_duration_processed),
936 reason_code =
937 DECODE(:reason_code,
938 :ar_text_dummy, reason_code,
939 :reason_code),
940 last_period_to_credit =
941 DECODE(:last_period_to_credit,
942 :ar_number_dummy, last_period_to_credit,
943 :last_period_to_credit),
944 sales_order =
945 DECODE(:sales_order,
946 :ar_text_dummy, sales_order,
947 :sales_order),
948 sales_order_date =
949 DECODE(:sales_order_date,
950 :ar_date_dummy, sales_order_date,
951 :sales_order_date),
952 sales_order_line =
953 DECODE(:sales_order_line,
954 :ar_text_dummy, sales_order_line,
955 :sales_order_line),
956 sales_order_revision =
957 DECODE(:sales_order_revision,
958 :ar_number_dummy, sales_order_revision,
959 :sales_order_revision),
960 sales_order_source =
961 DECODE(:sales_order_source,
962 :ar_text_dummy, sales_order_source,
963 :sales_order_source),
964 vat_tax_id =
965 DECODE(:vat_tax_id,
966 :ar_number_dummy, vat_tax_id,
967 :vat_tax_id),
968 tax_exempt_flag =
969 DECODE(:tax_exempt_flag,
970 :ar_flag_dummy, tax_exempt_flag,
971 :tax_exempt_flag),
972 sales_tax_id =
973 DECODE(:sales_tax_id,
974 :ar_number_dummy, sales_tax_id,
975 :sales_tax_id),
976 location_segment_id =
977 DECODE(:location_segment_id,
978 :ar_number_dummy, location_segment_id,
979 :location_segment_id),
980 tax_exempt_number =
981 DECODE(:tax_exempt_number,
982 :ar_text_dummy, tax_exempt_number,
983 :tax_exempt_number),
984 tax_exempt_reason_code =
985 DECODE(:tax_exempt_reason_code,
986 :ar_text_dummy, tax_exempt_reason_code,
987 :tax_exempt_reason_code),
988 tax_vendor_return_code =
989 DECODE(:tax_vendor_return_code,
990 :ar_text_dummy, tax_vendor_return_code,
991 :tax_vendor_return_code),
992 taxable_flag =
993 DECODE(:taxable_flag,
994 :ar_flag_dummy, taxable_flag,
995 :taxable_flag),
996 tax_exemption_id =
997 DECODE(:tax_exemption_id,
998 :ar_number_dummy, tax_exemption_id,
999 :tax_exemption_id),
1000 tax_precedence =
1001 DECODE(:tax_precedence,
1002 :ar_number_dummy, tax_precedence,
1003 :tax_precedence),
1004 tax_rate =
1005 DECODE(:tax_rate,
1006 :ar_number_dummy, tax_rate,
1007 :tax_rate),
1008 uom_code =
1009 DECODE(:uom_code,
1010 :ar_text3_dummy, uom_code,
1011 :uom_code),
1012 autotax =
1013 DECODE(:autotax,
1014 :ar_flag_dummy, autotax,
1015 :autotax),
1016 movement_id =
1017 DECODE(:movement_id,
1018 :ar_number_dummy, movement_id,
1019 :movement_id),
1020 default_ussgl_transaction_code =
1021 DECODE(:default_ussgl_transaction_code,
1022 :ar_text_dummy, default_ussgl_transaction_code,
1023 :default_ussgl_transaction_code),
1024 default_ussgl_trx_code_context =
1025 DECODE(:default_ussgl_trx_code_context,
1026 :ar_text_dummy, default_ussgl_trx_code_context,
1027 :default_ussgl_trx_code_context),
1028 interface_line_context =
1029 DECODE(:interface_line_context,
1030 :ar_text_dummy, interface_line_context,
1031 :interface_line_context),
1032 interface_line_attribute1 =
1033 DECODE(:interface_line_attribute1,
1034 :ar_text_dummy, interface_line_attribute1,
1035 :interface_line_attribute1),
1036 interface_line_attribute2 =
1037 DECODE(:interface_line_attribute2,
1038 :ar_text_dummy, interface_line_attribute2,
1039 :interface_line_attribute2),
1040 interface_line_attribute3 =
1041 DECODE(:interface_line_attribute3,
1042 :ar_text_dummy, interface_line_attribute3,
1043 :interface_line_attribute3),
1044 interface_line_attribute4 =
1045 DECODE(:interface_line_attribute4,
1046 :ar_text_dummy, interface_line_attribute4,
1047 :interface_line_attribute4),
1048 interface_line_attribute5 =
1049 DECODE(:interface_line_attribute5,
1050 :ar_text_dummy, interface_line_attribute5,
1051 :interface_line_attribute5),
1052 interface_line_attribute6 =
1053 DECODE(:interface_line_attribute6,
1054 :ar_text_dummy, interface_line_attribute6,
1055 :interface_line_attribute6),
1056 interface_line_attribute7 =
1057 DECODE(:interface_line_attribute7,
1058 :ar_text_dummy, interface_line_attribute7,
1059 :interface_line_attribute7),
1060 interface_line_attribute8 =
1061 DECODE(:interface_line_attribute8,
1062 :ar_text_dummy, interface_line_attribute8,
1063 :interface_line_attribute8),
1064 interface_line_attribute9 =
1065 DECODE(:interface_line_attribute9,
1066 :ar_text_dummy, interface_line_attribute9,
1067 :interface_line_attribute9),
1068 interface_line_attribute10 =
1069 DECODE(:interface_line_attribute10,
1070 :ar_text_dummy, interface_line_attribute10,
1071 :interface_line_attribute10),
1072 interface_line_attribute11 =
1073 DECODE(:interface_line_attribute11,
1074 :ar_text_dummy, interface_line_attribute11,
1075 :interface_line_attribute11),
1076 interface_line_attribute12 =
1077 DECODE(:interface_line_attribute12,
1078 :ar_text_dummy, interface_line_attribute12,
1079 :interface_line_attribute12),
1080 interface_line_attribute13 =
1081 DECODE(:interface_line_attribute13,
1082 :ar_text_dummy, interface_line_attribute13,
1083 :interface_line_attribute13),
1084 interface_line_attribute14 =
1085 DECODE(:interface_line_attribute14,
1086 :ar_text_dummy, interface_line_attribute14,
1087 :interface_line_attribute14),
1088 interface_line_attribute15 =
1089 DECODE(:interface_line_attribute15,
1090 :ar_text_dummy, interface_line_attribute15,
1091 :interface_line_attribute15),
1092 attribute_category =
1093 DECODE(:attribute_category,
1094 :ar_text_dummy, attribute_category,
1095 :attribute_category),
1096 attribute1 =
1097 DECODE(:attribute1,
1098 :ar_text_dummy, attribute1,
1099 :attribute1),
1100 attribute2 =
1101 DECODE(:attribute2,
1102 :ar_text_dummy, attribute2,
1103 :attribute2),
1104 attribute3 =
1105 DECODE(:attribute3,
1106 :ar_text_dummy, attribute3,
1107 :attribute3),
1108 attribute4 =
1109 DECODE(:attribute4,
1110 :ar_text_dummy, attribute4,
1111 :attribute4),
1112 attribute5 =
1113 DECODE(:attribute5,
1114 :ar_text_dummy, attribute5,
1115 :attribute5),
1116 attribute6 =
1117 DECODE(:attribute6,
1118 :ar_text_dummy, attribute6,
1119 :attribute6),
1120 attribute7 =
1121 DECODE(:attribute7,
1122 :ar_text_dummy, attribute7,
1123 :attribute7),
1124 attribute8 =
1125 DECODE(:attribute8,
1126 :ar_text_dummy, attribute8,
1127 :attribute8),
1128 attribute9 =
1129 DECODE(:attribute9,
1130 :ar_text_dummy, attribute9,
1131 :attribute9),
1132 attribute10 =
1133 DECODE(:attribute10,
1134 :ar_text_dummy, attribute10,
1135 :attribute10),
1136 attribute11 =
1137 DECODE(:attribute11,
1138 :ar_text_dummy, attribute11,
1139 :attribute11),
1140 attribute12 =
1141 DECODE(:attribute12,
1142 :ar_text_dummy, attribute12,
1143 :attribute12),
1144 attribute13 =
1145 DECODE(:attribute13,
1146 :ar_text_dummy, attribute13,
1147 :attribute13),
1148 attribute14 =
1149 DECODE(:attribute14,
1150 :ar_text_dummy, attribute14,
1151 :attribute14),
1152 attribute15 =
1153 DECODE(:attribute15,
1154 :ar_text_dummy, attribute15,
1155 :attribute15),
1156 global_attribute_category =
1157 DECODE(:global_attribute_category,
1158 :ar_text_dummy, global_attribute_category,
1159 :global_attribute_category),
1160 global_attribute1 =
1161 DECODE(:global_attribute1,
1162 :ar_text_dummy, global_attribute1,
1163 :global_attribute1),
1164 global_attribute2 =
1165 DECODE(:global_attribute2,
1166 :ar_text_dummy, global_attribute2,
1167 :global_attribute2),
1168 global_attribute3 =
1169 DECODE(:global_attribute3,
1170 :ar_text_dummy, global_attribute3,
1171 :global_attribute3),
1172 global_attribute4 =
1173 DECODE(:global_attribute4,
1174 :ar_text_dummy, global_attribute4,
1175 :global_attribute4),
1176 global_attribute5 =
1177 DECODE(:global_attribute5,
1178 :ar_text_dummy, global_attribute5,
1179 :global_attribute5),
1180 global_attribute6 =
1181 DECODE(:global_attribute6,
1182 :ar_text_dummy, global_attribute6,
1183 :global_attribute6),
1184 global_attribute7 =
1185 DECODE(:global_attribute7,
1186 :ar_text_dummy, global_attribute7,
1187 :global_attribute7),
1188 global_attribute8 =
1189 DECODE(:global_attribute8,
1190 :ar_text_dummy, global_attribute8,
1191 :global_attribute8),
1192 global_attribute9 =
1193 DECODE(:global_attribute9,
1194 :ar_text_dummy, global_attribute9,
1195 :global_attribute9),
1196 global_attribute10 =
1197 DECODE(:global_attribute10,
1198 :ar_text_dummy, global_attribute10,
1199 :global_attribute10),
1200 global_attribute11 =
1201 DECODE(:global_attribute11,
1202 :ar_text_dummy, global_attribute11,
1203 :global_attribute11),
1204 global_attribute12 =
1205 DECODE(:global_attribute12,
1206 :ar_text_dummy, global_attribute12,
1207 :global_attribute12),
1208 global_attribute13 =
1209 DECODE(:global_attribute13,
1210 :ar_text_dummy, global_attribute13,
1211 :global_attribute13),
1212 global_attribute14 =
1213 DECODE(:global_attribute14,
1214 :ar_text_dummy, global_attribute14,
1215 :global_attribute14),
1216 global_attribute15 =
1217 DECODE(:global_attribute15,
1218 :ar_text_dummy, global_attribute15,
1219 :global_attribute15),
1220 global_attribute16 =
1221 DECODE(:global_attribute16,
1222 :ar_text_dummy, global_attribute16,
1223 :global_attribute16),
1224 global_attribute17 =
1225 DECODE(:global_attribute17,
1226 :ar_text_dummy, global_attribute17,
1227 :global_attribute17),
1228 global_attribute18 =
1229 DECODE(:global_attribute18,
1230 :ar_text_dummy, global_attribute18,
1231 :global_attribute18),
1232 global_attribute19 =
1233 DECODE(:global_attribute19,
1234 :ar_text_dummy, global_attribute19,
1235 :global_attribute19),
1236 global_attribute20 =
1237 DECODE(:global_attribute20,
1238 :ar_text_dummy, global_attribute20,
1239 :global_attribute20),
1240 created_by =
1241 DECODE(:created_by,
1242 :ar_number_dummy, created_by,
1243 :created_by),
1244 creation_date =
1245 DECODE(:creation_date,
1246 :ar_date_dummy, creation_date,
1247 :creation_date),
1248 last_updated_by =
1249 DECODE(:last_updated_by,
1250 :ar_number_dummy, :pg_user_id,
1251 :last_updated_by),
1252 last_update_date =
1253 DECODE(:last_update_date,
1254 :ar_date_dummy, sysdate,
1255 :last_update_date),
1256 program_application_id =
1257 DECODE(:program_application_id,
1258 :ar_number_dummy, program_application_id,
1259 :program_application_id),
1260 last_update_login =
1261 DECODE(:last_update_login,
1262 :ar_number_dummy, nvl(:pg_conc_login_id,
1263 :pg_login_id),
1264 :last_update_login),
1265 program_id =
1266 DECODE(:program_id,
1267 :ar_number_dummy, program_id,
1268 :program_id),
1269 program_update_date =
1270 DECODE(:program_update_date,
1271 :ar_date_dummy, program_update_date,
1272 :program_update_date),
1273 set_of_books_id =
1274 DECODE(:set_of_books_id,
1275 :ar_number_dummy, set_of_books_id,
1276 :set_of_books_id),
1277 gross_extended_amount =
1278 DECODE(:gross_extended_amount,
1279 :ar_number_dummy, gross_extended_amount,
1280 :gross_extended_amount),
1281 gross_unit_selling_price =
1282 DECODE(:gross_unit_selling_price,
1283 :ar_number_dummy, gross_unit_selling_price,
1284 :gross_unit_selling_price),
1285 warehouse_id =
1286 DECODE(:warehouse_id,
1287 :ar_number_dummy, warehouse_id,
1288 :warehouse_id),
1289 translated_description =
1290 DECODE(:translated_description,
1291 :ar_text_dummy, translated_description,
1292 :translated_description),
1293 /* Bug 853757 */
1294 taxable_amount =
1295 DECODE(:taxable_amount,
1296 :ar_number_dummy, taxable_amount,
1297 :taxable_amount),
1298
1299 amount_includes_tax_flag =
1300 DECODE(:amount_includes_tax_flag,
1301 :ar_flag_dummy, amount_includes_tax_flag,
1302 :amount_includes_tax_flag),
1303
1304 extended_acctd_amount =
1305 DECODE(:extended_acctd_amount,
1306 :ar_number_dummy, extended_acctd_amount,
1307 :extended_acctd_amount),
1308 br_ref_customer_trx_id =
1309 DECODE(:br_ref_customer_trx_id,
1310 :ar_number_dummy, br_ref_customer_trx_id,
1311 :br_ref_customer_trx_id),
1312 br_ref_payment_schedule_id =
1313 DECODE(:br_ref_payment_schedule_id,
1314 :ar_number_dummy, br_ref_payment_schedule_id,
1315 :br_ref_payment_schedule_id),
1316 br_adjustment_id =
1317 DECODE(:br_adjustment_id,
1318 :ar_number_dummy, br_adjustment_id,
1319 :br_adjustment_id) ,
1320
1321 wh_update_date =
1322 DECODE(:wh_update_date,
1323 :ar_date_dummy, wh_update_date,
1324 :wh_update_date) ,
1325
1326 payment_set_id =
1327 DECODE(:payment_set_id,
1328 :ar_number_dummy, payment_set_id,
1329 :payment_set_id) ,
1330 ship_to_customer_id =
1331 DECODE(:ship_to_customer_id,
1332 :ar_number_dummy, ship_to_customer_id,
1333 :ship_to_customer_id) ,
1334 ship_to_site_use_id =
1335 DECODE(:ship_to_site_use_id,
1336 :ar_number_dummy, ship_to_site_use_id,
1337 :ship_to_site_use_id) ,
1338 ship_to_contact_id =
1339 DECODE(:ship_to_contact_id,
1340 :ar_number_dummy, ship_to_contact_id,
1341 :ship_to_contact_id),
1342 tax_classification_code =
1343 DECODE(:tax_classification_code,
1344 :ar_text_dummy, tax_classification_code,
1345 :tax_classification_code),
1346 rule_end_date =
1347 DECODE(:rule_end_date,
1348 :ar_date_dummy, rule_end_date,
1349 :rule_end_date) ';
1350
1351
1352 arp_util.debug('arp_ctl_pkg.construct_line_update_stmt()-');
1353
1354 EXCEPTION
1355 WHEN OTHERS THEN
1356 arp_util.debug('EXCEPTION: arp_ctl_pkg.construct_line_update_stmt()');
1357 RAISE;
1358
1359 END;
1360
1361 /*===========================================================================+
1362 | PROCEDURE |
1363 | generic_update |
1364 | |
1365 | DESCRIPTION |
1366 | This procedure Updates records in ra_customer_trx_lines identified by |
1367 | the where clause that is passed in as a parameter. Only those columns |
1368 | in the line record parameter that do not contain the special dummy |
1369 | values are updated. |
1370 | |
1371 | SCOPE - PRIVATE |
1372 | |
1373 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1374 | arp_util.debug |
1375 | dbms_sql.open_cursor |
1376 | dbms_sql.parse |
1377 | dbms_sql.execute |
1378 | dbms_sql.close_cursor |
1379 | |
1380 | ARGUMENTS : IN: |
1381 | p_update_cursor - identifies the cursor to use |
1382 | p_where_clause - identifies which rows to update |
1383 | p_where1 - value to bind into where clause |
1384 | p_line_type - line_type of the line |
1385 | p_currency_code - the currency code of the invoice |
1386 | p_line_rec - contains the new line values |
1387 | OUT: |
1388 | None |
1389 | |
1390 | RETURNS : NONE |
1391 | |
1392 | NOTES |
1393 | |
1394 | MODIFICATION HISTORY |
1395 | 06-JUN-95 Charlie Tomberg Created |
1396 | |
1397 +===========================================================================*/
1398
1399 PROCEDURE generic_update(p_update_cursor IN OUT NOCOPY integer,
1400 p_where_clause IN varchar2,
1401 p_where1 IN number,
1402 p_line_type IN
1403 ra_customer_trx_lines.line_type%type,
1404 p_currency_code IN
1405 fnd_currencies.currency_code%type,
1406 p_line_rec IN ra_customer_trx_lines%rowtype)
1407 IS
1408
1409 l_count number;
1410 l_update_statement varchar2(32767);
1411 --{bug#3339072 MRC trx lines
1412 ctl_array dbms_sql.number_table;
1413 --}
1414
1415 BEGIN
1416 arp_util.debug('arp_ctl_pkg.generic_update()+');
1417
1418 /*--------------------------------------------------------------+
1419 | If this update statement has not already been parsed, |
1420 | construct the statement and parse it. |
1421 | Otherwise, use the already parsed statement and rebind its |
1422 | variables. |
1423 +--------------------------------------------------------------*/
1424
1425 IF (p_update_cursor is null)
1426 THEN
1427
1428 p_update_cursor := dbms_sql.open_cursor;
1429
1430 /*---------------------------------+
1431 | Construct the update statement |
1432 +---------------------------------*/
1433
1434 arp_ctl_pkg.construct_line_update_stmt(l_update_statement);
1435
1436 l_update_statement := l_update_statement || p_where_clause ||
1437 --{BUG#3339072 MRC trx line
1438 ' RETURNING :customer_trx_line_id INTO :ctl_value ';
1439 --}
1440
1441 arp_util.debug('Update statement:');
1442 arp_util.debug('');
1443 arp_util.debug(l_update_statement);
1444 arp_util.debug('');
1445
1446 /*-----------------------------------------------+
1447 | Parse, bind, execute and close the statement |
1448 +-----------------------------------------------*/
1449
1450 dbms_sql.parse(p_update_cursor,
1451 l_update_statement,
1452 dbms_sql.v7);
1453
1454 END IF;
1455
1456 arp_ctl_pkg.bind_line_variables(p_update_cursor, p_line_rec);
1457
1458 /*-----------------------------------------+
1459 | Bind the variables in the where clause |
1460 +-----------------------------------------*/
1461
1462 dbms_sql.bind_variable(p_update_cursor, ':where_1',
1463 p_where1);
1464
1465 dbms_sql.bind_variable(p_update_cursor, ':where_line_type',
1466 p_line_type);
1467
1468 dbms_sql.bind_variable(p_update_cursor, ':invoice_currency_code',
1469 p_currency_code);
1470
1471 --{BUG#3339072
1472 dbms_sql.bind_array(p_update_cursor, ':ctl_value',
1473 ctl_array);
1474 --}
1475
1476 l_count := dbms_sql.execute(p_update_cursor);
1477
1478 arp_util.debug( to_char(l_count) || ' rows updated');
1479
1480 --{BUG#3339072
1481 dbms_sql.variable_value(p_update_cursor, ':ctl_value',
1482 ctl_array);
1483 --}
1484
1485 /*------------------------------------------------------------+
1486 | Raise the NO_DATA_FOUND exception if no rows were updated |
1487 +------------------------------------------------------------*/
1488
1489 IF (l_count = 0)
1490 THEN RAISE NO_DATA_FOUND;
1491 END IF;
1492
1493 arp_util.debug('arp_ctl_pkg.generic_update()-');
1494
1495 EXCEPTION
1496 WHEN OTHERS THEN
1497 arp_util.debug('EXCEPTION: arp_ctl_pkg.generic_update()');
1498 arp_util.debug(l_update_statement);
1499 arp_util.debug('Error at character: ' ||
1500 to_char(dbms_sql.last_error_position));
1501
1502 arp_util.debug('');
1503 arp_util.debug('-------- parameters for generic_update() ------');
1504 arp_util.debug('p_update_cursor = ' || p_update_cursor);
1505 arp_util.debug('p_where_clause = ' || p_where_clause);
1506 arp_util.debug('p_where1 = ' || p_where1);
1507 arp_util.debug('p_line_type = ' || p_line_type);
1508 arp_util.debug('p_currency_code = ' || p_currency_code);
1509 display_line_rec(p_line_rec);
1510
1511 RAISE;
1512 END;
1513
1514 /*===========================================================================+
1515 | PROCEDURE |
1516 | set_to_dummy |
1517 | |
1518 | DESCRIPTION |
1519 | This procedure initializes all columns in the parameter line record |
1520 | to the appropriate dummy value for its datatype. |
1521 | |
1522 | The dummy values are defined in the following package level constants: |
1523 | AR_TEXT_DUMMY |
1524 | AR_TEXT3_DUMMY |
1525 | AR_FLAG_DUMMY |
1526 | AR_NUMBER_DUMMY |
1527 | AR_DATE_DUMMY |
1528 | |
1529 | SCOPE - PUBLIC |
1530 | |
1531 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1532 | arp_util.debug |
1533 | |
1534 | ARGUMENTS : IN: |
1535 | None |
1536 | OUT: |
1537 | p_line_rec - The record to initialize |
1538 | |
1539 | RETURNS : NONE |
1540 | |
1541 | NOTES |
1542 | |
1543 | MODIFICATION HISTORY |
1544 | 06-JUN-95 Charlie Tomberg Created |
1545 | |
1546 | Rel. 11 Changes: |
1547 | ---------------- |
1548 | 07-22-97 OSTEINME added code to dummify three new |
1549 | database columns: |
1550 | - gross_unit_selling_price |
1551 | - gross_extended_amount |
1552 | - amount_includes_tax_flag |
1553 | |
1554 | 08-20-97 KTANG dummify global_attribute_category and|
1555 | global_attribute[1-20] for global |
1556 | descriptive flexfield |
1557 | |
1558 | |
1559 | 10-JAN-99 Saloni Shah added warehouse_id for global tax |
1560 | engine changes |
1561 | 22-MAR-99 Debbie Jancis added translated_description for MLS |
1562 | |
1563 | 20-MAR-2000 J Rautiainen Added BR project related columns |
1564 | EXTENDED_ACCTD_AMOUNT, BR_REF_CUSTOMER_TRX_ID, |
1565 | BR_REF_PAYMENT_SCHEDULE_ID and BR_ADJUSTMENT_ID |
1566 | into table handlers |
1567 | |
1568 | 31-OCT-2000 Y Rakotonirainy Bug 1243304 : Added column wh_update_date |
1569 | into the table handlers. |
1570 +===========================================================================*/
1571
1572
1573 PROCEDURE set_to_dummy( p_line_rec OUT NOCOPY ra_customer_trx_lines%rowtype) IS
1574
1575 BEGIN
1576
1577 IF PG_DEBUG in ('Y', 'C') THEN
1578 arp_util.debug('arp_ctl_pkg.set_to_dummy()+');
1579 END IF;
1580
1581 p_line_rec.customer_trx_line_id := AR_NUMBER_DUMMY;
1582 p_line_rec.customer_trx_id := AR_NUMBER_DUMMY;
1583 p_line_rec.line_number := AR_NUMBER_DUMMY;
1584 p_line_rec.line_type := AR_TEXT_DUMMY;
1585 p_line_rec.quantity_credited := AR_NUMBER_DUMMY;
1586 p_line_rec.quantity_invoiced := AR_NUMBER_DUMMY;
1587 p_line_rec.quantity_ordered := AR_NUMBER_DUMMY;
1588 p_line_rec.unit_selling_price := AR_NUMBER_DUMMY;
1589 p_line_rec.unit_standard_price := AR_NUMBER_DUMMY;
1590 p_line_rec.revenue_amount := AR_NUMBER_DUMMY;
1591 p_line_rec.extended_amount := AR_NUMBER_DUMMY;
1592 p_line_rec.memo_line_id := AR_NUMBER_DUMMY;
1593 p_line_rec.inventory_item_id := AR_NUMBER_DUMMY;
1594 p_line_rec.item_exception_rate_id := AR_NUMBER_DUMMY;
1595 p_line_rec.description := AR_TEXT_DUMMY;
1596 p_line_rec.item_context := AR_TEXT_DUMMY;
1597 p_line_rec.initial_customer_trx_line_id := AR_NUMBER_DUMMY;
1598 p_line_rec.link_to_cust_trx_line_id := AR_NUMBER_DUMMY;
1599 p_line_rec.previous_customer_trx_id := AR_NUMBER_DUMMY;
1600 p_line_rec.previous_customer_trx_line_id := AR_NUMBER_DUMMY;
1601 p_line_rec.accounting_rule_duration := AR_NUMBER_DUMMY;
1602 p_line_rec.accounting_rule_id := AR_NUMBER_DUMMY;
1603 p_line_rec.rule_start_date := AR_DATE_DUMMY;
1604 p_line_rec.autorule_complete_flag := AR_FLAG_DUMMY;
1605 p_line_rec.autorule_duration_processed := AR_NUMBER_DUMMY;
1606 p_line_rec.reason_code := AR_TEXT_DUMMY;
1607 p_line_rec.last_period_to_credit := AR_NUMBER_DUMMY;
1608 p_line_rec.sales_order := AR_TEXT_DUMMY;
1609 p_line_rec.sales_order_date := AR_DATE_DUMMY;
1610 p_line_rec.sales_order_line := AR_TEXT_DUMMY;
1611 p_line_rec.sales_order_revision := AR_NUMBER_DUMMY;
1612 p_line_rec.sales_order_source := AR_TEXT_DUMMY;
1613 p_line_rec.vat_tax_id := AR_NUMBER_DUMMY;
1614 p_line_rec.tax_exempt_flag := AR_FLAG_DUMMY;
1615 p_line_rec.sales_tax_id := AR_NUMBER_DUMMY;
1616 p_line_rec.location_segment_id := AR_NUMBER_DUMMY;
1617 p_line_rec.tax_exempt_number := AR_TEXT_DUMMY;
1618 p_line_rec.tax_exempt_reason_code := AR_TEXT_DUMMY;
1619 p_line_rec.tax_vendor_return_code := AR_TEXT_DUMMY;
1620 p_line_rec.taxable_flag := AR_FLAG_DUMMY;
1621 p_line_rec.tax_exemption_id := AR_NUMBER_DUMMY;
1622 p_line_rec.tax_precedence := AR_NUMBER_DUMMY;
1623 p_line_rec.tax_rate := AR_NUMBER_DUMMY;
1624 p_line_rec.uom_code := AR_TEXT3_DUMMY;
1625 p_line_rec.autotax := AR_FLAG_DUMMY;
1626 p_line_rec.movement_id := AR_NUMBER_DUMMY;
1627 p_line_rec.default_ussgl_transaction_code := AR_TEXT_DUMMY;
1628 p_line_rec.default_ussgl_trx_code_context := AR_TEXT_DUMMY;
1629 p_line_rec.interface_line_context := AR_TEXT_DUMMY;
1630 p_line_rec.interface_line_attribute1 := AR_TEXT_DUMMY;
1631 p_line_rec.interface_line_attribute2 := AR_TEXT_DUMMY;
1632 p_line_rec.interface_line_attribute3 := AR_TEXT_DUMMY;
1633 p_line_rec.interface_line_attribute4 := AR_TEXT_DUMMY;
1634 p_line_rec.interface_line_attribute5 := AR_TEXT_DUMMY;
1635 p_line_rec.interface_line_attribute6 := AR_TEXT_DUMMY;
1636 p_line_rec.interface_line_attribute7 := AR_TEXT_DUMMY;
1637 p_line_rec.interface_line_attribute8 := AR_TEXT_DUMMY;
1638 p_line_rec.interface_line_attribute9 := AR_TEXT_DUMMY;
1639 p_line_rec.interface_line_attribute10 := AR_TEXT_DUMMY;
1640 p_line_rec.interface_line_attribute11 := AR_TEXT_DUMMY;
1641 p_line_rec.interface_line_attribute12 := AR_TEXT_DUMMY;
1642 p_line_rec.interface_line_attribute13 := AR_TEXT_DUMMY;
1643 p_line_rec.interface_line_attribute14 := AR_TEXT_DUMMY;
1644 p_line_rec.interface_line_attribute15 := AR_TEXT_DUMMY;
1645 p_line_rec.attribute_category := AR_TEXT_DUMMY;
1646 p_line_rec.attribute1 := AR_TEXT_DUMMY;
1647 p_line_rec.attribute2 := AR_TEXT_DUMMY;
1648 p_line_rec.attribute3 := AR_TEXT_DUMMY;
1649 p_line_rec.attribute4 := AR_TEXT_DUMMY;
1650 p_line_rec.attribute5 := AR_TEXT_DUMMY;
1651 p_line_rec.attribute6 := AR_TEXT_DUMMY;
1652 p_line_rec.attribute7 := AR_TEXT_DUMMY;
1653 p_line_rec.attribute8 := AR_TEXT_DUMMY;
1654 p_line_rec.attribute9 := AR_TEXT_DUMMY;
1655 p_line_rec.attribute10 := AR_TEXT_DUMMY;
1656 p_line_rec.attribute11 := AR_TEXT_DUMMY;
1657 p_line_rec.attribute12 := AR_TEXT_DUMMY;
1658 p_line_rec.attribute13 := AR_TEXT_DUMMY;
1659 p_line_rec.attribute14 := AR_TEXT_DUMMY;
1660 p_line_rec.attribute15 := AR_TEXT_DUMMY;
1661 p_line_rec.global_attribute_category := AR_TEXT_DUMMY;
1662 p_line_rec.global_attribute1 := AR_TEXT_DUMMY;
1663 p_line_rec.global_attribute2 := AR_TEXT_DUMMY;
1664 p_line_rec.global_attribute3 := AR_TEXT_DUMMY;
1665 p_line_rec.global_attribute4 := AR_TEXT_DUMMY;
1666 p_line_rec.global_attribute5 := AR_TEXT_DUMMY;
1667 p_line_rec.global_attribute6 := AR_TEXT_DUMMY;
1668 p_line_rec.global_attribute7 := AR_TEXT_DUMMY;
1669 p_line_rec.global_attribute8 := AR_TEXT_DUMMY;
1670 p_line_rec.global_attribute9 := AR_TEXT_DUMMY;
1671 p_line_rec.global_attribute10 := AR_TEXT_DUMMY;
1672 p_line_rec.global_attribute11 := AR_TEXT_DUMMY;
1673 p_line_rec.global_attribute12 := AR_TEXT_DUMMY;
1674 p_line_rec.global_attribute13 := AR_TEXT_DUMMY;
1675 p_line_rec.global_attribute14 := AR_TEXT_DUMMY;
1676 p_line_rec.global_attribute15 := AR_TEXT_DUMMY;
1677 p_line_rec.global_attribute16 := AR_TEXT_DUMMY;
1678 p_line_rec.global_attribute17 := AR_TEXT_DUMMY;
1679 p_line_rec.global_attribute18 := AR_TEXT_DUMMY;
1680 p_line_rec.global_attribute19 := AR_TEXT_DUMMY;
1681 p_line_rec.global_attribute20 := AR_TEXT_DUMMY;
1682 p_line_rec.created_by := AR_NUMBER_DUMMY;
1683 p_line_rec.creation_date := AR_DATE_DUMMY;
1684 p_line_rec.last_updated_by := AR_NUMBER_DUMMY;
1685 p_line_rec.last_update_date := AR_DATE_DUMMY;
1686 p_line_rec.program_application_id := AR_NUMBER_DUMMY;
1687 p_line_rec.last_update_login := AR_NUMBER_DUMMY;
1688 p_line_rec.program_id := AR_NUMBER_DUMMY;
1689 p_line_rec.program_update_date := AR_DATE_DUMMY;
1690 p_line_rec.set_of_books_id := AR_NUMBER_DUMMY;
1691 p_line_rec.payment_set_id := AR_NUMBER_DUMMY;
1692
1693 -- Rel. 11 Changes:
1694
1695 p_line_rec.gross_unit_selling_price := AR_NUMBER_DUMMY;
1696 p_line_rec.gross_extended_amount := AR_NUMBER_DUMMY;
1697 p_line_rec.amount_includes_tax_flag := AR_FLAG_DUMMY;
1698
1699 -- Rel 11.5 Changes (for global tax engine)
1700 p_line_rec.warehouse_id := AR_NUMBER_DUMMY;
1701 p_line_rec.translated_description := AR_TEXT_DUMMY;
1702
1703 /* Bug 853757 */
1704 p_line_rec.taxable_amount := AR_NUMBER_DUMMY;
1705
1706 p_line_rec.extended_acctd_amount := AR_NUMBER_DUMMY;
1707 p_line_rec.br_ref_customer_trx_id := AR_NUMBER_DUMMY;
1708 p_line_rec.br_ref_payment_schedule_id := AR_NUMBER_DUMMY;
1709 p_line_rec.br_adjustment_id := AR_NUMBER_DUMMY;
1710 p_line_rec.wh_update_date := AR_DATE_DUMMY;
1711
1712 /* 4713671 */
1713 p_line_rec.ship_to_customer_id := AR_NUMBER_DUMMY;
1714 p_line_rec.ship_to_site_use_id := AR_NUMBER_DUMMY;
1715 p_line_rec.ship_to_contact_id := AR_NUMBER_DUMMY;
1716 p_line_rec.tax_classification_code := AR_TEXT_DUMMY;
1717
1718 p_line_rec.rule_end_date := AR_DATE_DUMMY;
1719 IF PG_DEBUG in ('Y', 'C') THEN
1720 arp_util.debug('arp_ctl_pkg.set_to_dummy()-');
1721 END IF;
1722
1723 EXCEPTION
1724 WHEN OTHERS THEN
1725 IF PG_DEBUG in ('Y', 'C') THEN
1726 arp_util.debug('EXCEPTION: arp_ctl_pkg.set_to_dummy()');
1727 END IF;
1728 RAISE;
1729
1730 END;
1731
1732 /*===========================================================================+
1733 | PROCEDURE |
1734 | fetch_p |
1735 | |
1736 | DESCRIPTION |
1737 | This procedure fetches a single row from ra_customer_trx_lines into a |
1738 | variable specified as a parameter based on the table's primary key, |
1739 | customer_trx__line_id. |
1740 | |
1741 | SCOPE - PUBLIC |
1742 | |
1743 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1744 | arp_util.debug |
1745 | |
1746 | ARGUMENTS : IN: |
1747 | p_customer_trx_line_id - identifies the record to fetch|
1748 | OUT: |
1749 | p_line_rec - contains the fetched record |
1750 | |
1751 | RETURNS : NONE |
1752 | |
1753 | NOTES |
1754 | |
1755 | MODIFICATION HISTORY |
1756 | 07-JUN-95 Charlie Tomberg Created |
1757 | |
1758 +===========================================================================*/
1759
1760 PROCEDURE fetch_p( p_line_rec OUT NOCOPY ra_customer_trx_lines%rowtype,
1761 p_customer_trx_line_id IN
1762 ra_customer_trx_lines.customer_trx_line_id%type )
1763 IS
1764
1765 BEGIN
1766 IF PG_DEBUG in ('Y', 'C') THEN
1767 arp_util.debug( 'arp_ctl_pkg.fetch_p()+');
1768 END IF;
1769
1770 SELECT *
1771 INTO p_line_rec
1772 FROM ra_customer_trx_lines
1773 WHERE customer_trx_line_id = p_customer_trx_line_id;
1774
1775 IF PG_DEBUG in ('Y', 'C') THEN
1776 arp_util.debug( 'arp_ctl_pkg.fetch_p()-');
1777 END IF;
1778
1779 EXCEPTION
1780 WHEN OTHERS THEN
1781 IF PG_DEBUG in ('Y', 'C') THEN
1782 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.fetch_p' );
1783 arp_util.debug( '');
1784 arp_util.debug( '-------- parameters for fetch_p() ------');
1785 arp_util.debug( 'p_customer_trx_line_id = ' || p_customer_trx_line_id);
1786 END IF;
1787
1788 RAISE;
1789 END;
1790
1791 /*===========================================================================+
1792 | PROCEDURE |
1793 | lock_p |
1794 | |
1795 | DESCRIPTION |
1796 | This procedure locks the ra_customer_trx_lines row identified by the |
1797 | p_customer_trx_line_id parameter. |
1798 | |
1799 | SCOPE - PUBLIC |
1800 | |
1801 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1802 | arp_util.debug |
1803 | |
1804 | ARGUMENTS : IN: |
1805 | p_customer_trx_line_id - identifies the row to lock |
1806 | OUT: |
1807 | None |
1808 | |
1809 | RETURNS : NONE |
1810 | |
1811 | NOTES |
1812 | |
1813 | MODIFICATION HISTORY |
1814 | 07-JUN-95 Charlie Tomberg Created |
1815 | |
1816 +===========================================================================*/
1817
1818 PROCEDURE lock_p( p_customer_trx_line_id IN
1819 ra_customer_trx_lines.customer_trx_line_id%type )
1820 IS
1821
1822 l_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%type;
1823
1824 BEGIN
1825 IF PG_DEBUG in ('Y', 'C') THEN
1826 arp_util.debug( 'arp_ctl_pkg.lock_p()+');
1827 END IF;
1828
1829
1830 SELECT customer_trx_line_id
1831 INTO l_customer_trx_line_id
1832 FROM ra_customer_trx_lines
1833 WHERE customer_trx_line_id = p_customer_trx_line_id
1834 FOR UPDATE OF customer_trx_line_id NOWAIT;
1835
1836 IF PG_DEBUG in ('Y', 'C') THEN
1837 arp_util.debug( 'arp_ctl_pkg.lock_p()-');
1838 END IF;
1839
1840 EXCEPTION
1841 WHEN OTHERS THEN
1842 IF PG_DEBUG in ('Y', 'C') THEN
1843 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.lock_p' );
1844 arp_util.debug( '');
1845 arp_util.debug( '-------- parameters for lock_p() ------');
1846 arp_util.debug( 'p_customer_trx_line_id = ' ||
1847 p_customer_trx_line_id);
1848 END IF;
1849
1850 RAISE;
1851 END;
1852
1853 /*===========================================================================+
1854 | PROCEDURE |
1855 | lock_f_ct_id |
1856 | |
1857 | DESCRIPTION |
1858 | This procedure locks the ra_customer_trx_lines rows identified by the |
1859 | p_customer_trx_id parameter. |
1860 | |
1861 | SCOPE - PUBLIC |
1862 | |
1863 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1864 | arp_util.debug |
1865 | |
1866 | ARGUMENTS : IN: |
1867 | p_customer_trx_id - identifies the rows to lock |
1868 | OUT: |
1869 | None |
1870 | |
1871 | RETURNS : NONE |
1872 | |
1873 | NOTES |
1874 | |
1875 | MODIFICATION HISTORY |
1876 | 07-JUN-95 Charlie Tomberg Created |
1877 | |
1878 +===========================================================================*/
1879
1880 PROCEDURE lock_f_ct_id( p_customer_trx_id IN
1881 ra_customer_trx.customer_trx_id%type )
1882 IS
1883
1884 CURSOR LOCK_C IS
1885 SELECT 'lock'
1886 FROM ra_customer_trx_lines
1887 WHERE customer_trx_id = p_customer_trx_id
1888 FOR UPDATE OF customer_trx_line_id NOWAIT;
1889
1890 BEGIN
1891 IF PG_DEBUG in ('Y', 'C') THEN
1892 arp_util.debug( 'arp_ctl_pkg.lock_f_ct_id()+');
1893 END IF;
1894
1895 OPEN lock_c;
1896 CLOSE lock_c;
1897
1898 IF PG_DEBUG in ('Y', 'C') THEN
1899 arp_util.debug( 'arp_ctl_pkg.lock_f_ct_id()-');
1900 END IF;
1901
1902 EXCEPTION
1903 WHEN OTHERS THEN
1904 IF PG_DEBUG in ('Y', 'C') THEN
1905 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.lock_f_ct_id' );
1906 arp_util.debug( '');
1907 arp_util.debug( '-------- parameters for lock_f_ct_id() ------');
1908 arp_util.debug( 'p_customer_trx_id = ' ||
1909 p_customer_trx_id);
1910 END IF;
1911
1912 RAISE;
1913 END;
1914
1915
1916 /*===========================================================================+
1917 | PROCEDURE |
1918 | lock_fetch_p |
1919 | |
1920 | DESCRIPTION |
1921 | This procedure locks the ra_customer_trx_lines row identified by the |
1922 | p_ra_customer_trx_line_id parameter and populates the |
1923 | p_line_rec parameter with the row that was locked |
1924 | |
1925 | SCOPE - PUBLIC |
1926 | |
1927 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1928 | arp_util.debug |
1929 | |
1930 | ARGUMENTS : IN: |
1931 | p_customer_trx_line_id - identifies the row to lock |
1932 | OUT: |
1933 | p_line_rec - contains the locked row |
1934 | |
1935 | RETURNS : NONE |
1936 | |
1937 | NOTES |
1938 | |
1939 | MODIFICATION HISTORY |
1940 | 07-JUN-95 Charlie Tomberg Created |
1941 | |
1942 +===========================================================================*/
1943
1944 PROCEDURE lock_fetch_p( p_line_rec IN OUT NOCOPY ra_customer_trx_lines%rowtype,
1945 p_customer_trx_line_id IN
1946 ra_customer_trx_lines.customer_trx_line_id%type ) IS
1947
1948 BEGIN
1949 IF PG_DEBUG in ('Y', 'C') THEN
1950 arp_util.debug( 'arp_ctl_pkg.lock_fetch_p()+');
1951 END IF;
1952
1953 SELECT *
1954 INTO p_line_rec
1955 FROM ra_customer_trx_lines
1956 WHERE customer_trx_line_id = p_customer_trx_line_id
1957 FOR UPDATE OF customer_trx_line_id NOWAIT;
1958
1959 IF PG_DEBUG in ('Y', 'C') THEN
1960 arp_util.debug( 'arp_ctl_pkg.lock_fetch_p()-');
1961 END IF;
1962
1963 EXCEPTION
1964 WHEN OTHERS THEN
1965 IF PG_DEBUG in ('Y', 'C') THEN
1966 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.lock_fetch_p' );
1967 arp_util.debug( '');
1968 arp_util.debug( '-------- parameters for lock_fetch_p() ------');
1969 arp_util.debug( 'p_customer_trx_line_id = ' ||
1970 p_customer_trx_line_id);
1971 END IF;
1972 display_line_rec(p_line_rec);
1973
1974 RAISE;
1975 END;
1976
1977 /*===========================================================================+
1978 | PROCEDURE |
1979 | lock_compare_p |
1980 | |
1981 | DESCRIPTION |
1982 | This procedure locks the ra_customer_trx_lines row identified by the |
1983 | p_customer_trx_line_id parameter only if no columns in that row have |
1984 | changed from when they were first selected in the form. |
1985 | |
1986 | SCOPE - PUBLIC |
1987 | |
1988 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
1989 | arp_util.debug |
1990 | |
1991 | ARGUMENTS : IN: |
1992 | p_customer_trx_line_id - identifies the row to lock |
1993 | p_line_rec - line record for comparison |
1994 | p_ignore_who_flag - directs system to ignore who cols |
1995 | OUT: |
1996 | None |
1997 | |
1998 | RETURNS : NONE |
1999 | |
2000 | NOTES |
2001 | |
2002 | MODIFICATION HISTORY |
2003 | 07-JUN-95 Charlie Tomberg Created |
2004 | 29-JUN-95 Charlie Tomberg Modified to use select for update |
2005 | 04-DEC-95 Martin Johnson Handle NO_DATA_FOUND exception |
2006 | |
2007 | Rel. 11 Changes: |
2008 | ---------------- |
2009 | 07-22-97 OSTEINME added code to handle three new |
2010 | database columns: |
2011 | - gross_unit_selling_price |
2012 | - gross_extended_amount |
2013 | - amount_includes_tax_flag |
2014 | |
2015 | 08-20-97 KTANG handle global_attribute_category and |
2016 | global_attribute[1-20] for global |
2017 | descriptive flexfield |
2018 | |
2019 | 10-JAN-99 Saloni Shah added warehouse_id for global tax |
2020 | engine change |
2021 | 22-MAR-99 Debbie Jancis added translated_description for MLS |
2022 | |
2023 | 20-MAR-2000 J Rautiainen Added BR project related columns |
2024 | EXTENDED_ACCTD_AMOUNT, BR_REF_CUSTOMER_TRX_ID, |
2025 | BR_REF_PAYMENT_SCHEDULE_ID and BR_ADJUSTMENT_ID |
2026 | into table handlers |
2027 | |
2028 | 31-OCT-2000 Y Rakotonirainy Bug 1243304 : Added column wh_update_date |
2029 | into the table handlers. |
2030 +===========================================================================*/
2031
2032 PROCEDURE lock_compare_p( p_line_rec IN ra_customer_trx_lines%rowtype,
2033 p_customer_trx_line_id IN
2034 ra_customer_trx_lines.customer_trx_line_id%type,
2035 p_ignore_who_flag BOOLEAN DEFAULT FALSE ) IS
2036
2037 l_new_line_rec ra_customer_trx_lines%rowtype;
2038 l_temp_line_rec ra_customer_trx_lines%rowtype;
2039 l_ignore_who_flag varchar2(2);
2040
2041 BEGIN
2042 IF PG_DEBUG in ('Y', 'C') THEN
2043 arp_util.debug( 'arp_ctl_pkg.lock_compare_p()+');
2044 END IF;
2045
2046 IF (p_ignore_who_flag = TRUE)
2047 THEN l_ignore_who_flag := 'Y';
2048 ELSE l_ignore_who_flag := 'N';
2049 END IF;
2050
2051 SELECT *
2052 INTO l_new_line_rec
2053 FROM ra_customer_trx_lines ctl
2054 WHERE ctl.customer_trx_line_id = p_customer_trx_line_id
2055 AND
2056 (
2057 NVL(ctl.customer_trx_line_id, AR_NUMBER_DUMMY) =
2058 NVL(
2059 DECODE(p_line_rec.customer_trx_line_id,
2060 AR_NUMBER_DUMMY, ctl.customer_trx_line_id,
2061 p_line_rec.customer_trx_line_id),
2062 AR_NUMBER_DUMMY
2063 )
2064 AND
2065 NVL(ctl.customer_trx_id, AR_NUMBER_DUMMY) =
2066 NVL(
2067 DECODE(p_line_rec.customer_trx_id,
2068 AR_NUMBER_DUMMY, ctl.customer_trx_id,
2069 p_line_rec.customer_trx_id),
2070 AR_NUMBER_DUMMY
2071 )
2072 AND
2073 NVL(ctl.line_number, AR_NUMBER_DUMMY) =
2074 NVL(
2075 DECODE(p_line_rec.line_number,
2076 AR_NUMBER_DUMMY, ctl.line_number,
2077 p_line_rec.line_number),
2078 AR_NUMBER_DUMMY
2079 )
2080 AND
2081 NVL(ctl.line_type, AR_TEXT_DUMMY) =
2082 NVL(
2083 DECODE(p_line_rec.line_type,
2084 AR_TEXT_DUMMY, ctl.line_type,
2085 p_line_rec.line_type),
2086 AR_TEXT_DUMMY
2087 )
2088 AND
2089 NVL(substr(ctl.quantity_credited,1,37), AR_NUMBER_DUMMY) =
2090 NVL(
2091 DECODE(p_line_rec.quantity_credited,
2092 AR_NUMBER_DUMMY, substr(ctl.quantity_credited,1,37),
2093 substr(p_line_rec.quantity_credited,1,37)),
2094 AR_NUMBER_DUMMY
2095 )
2096 AND
2097 NVL(ctl.quantity_invoiced, AR_NUMBER_DUMMY) =
2098 NVL(
2099 DECODE(p_line_rec.quantity_invoiced,
2100 AR_NUMBER_DUMMY, ctl.quantity_invoiced,
2101 p_line_rec.quantity_invoiced),
2102 AR_NUMBER_DUMMY
2103 )
2104 AND
2105 NVL(ctl.quantity_ordered, AR_NUMBER_DUMMY) =
2106 NVL(
2107 DECODE(p_line_rec.quantity_ordered,
2108 AR_NUMBER_DUMMY, ctl.quantity_ordered,
2109 p_line_rec.quantity_ordered),
2110 AR_NUMBER_DUMMY
2111 )
2112 AND
2113 NVL(ctl.unit_selling_price, AR_NUMBER_DUMMY) =
2114 NVL(
2115 DECODE(p_line_rec.unit_selling_price,
2116 AR_NUMBER_DUMMY, ctl.unit_selling_price,
2117 p_line_rec.unit_selling_price),
2118 AR_NUMBER_DUMMY
2119 )
2120 AND
2121 NVL(ctl.unit_standard_price, AR_NUMBER_DUMMY) =
2122 NVL(
2123 DECODE(p_line_rec.unit_standard_price,
2124 AR_NUMBER_DUMMY, ctl.unit_standard_price,
2125 p_line_rec.unit_standard_price),
2126 AR_NUMBER_DUMMY
2127 )
2128 AND
2129 NVL(ctl.revenue_amount, AR_NUMBER_DUMMY) =
2130 NVL(
2131 DECODE(p_line_rec.revenue_amount,
2132 AR_NUMBER_DUMMY, ctl.revenue_amount,
2133 p_line_rec.revenue_amount),
2134 AR_NUMBER_DUMMY
2135 )
2136 AND
2137 NVL(ctl.extended_amount, AR_NUMBER_DUMMY) =
2138 NVL(
2139 DECODE(p_line_rec.extended_amount,
2140 AR_NUMBER_DUMMY, ctl.extended_amount,
2141 p_line_rec.extended_amount),
2142 AR_NUMBER_DUMMY
2143 )
2144 AND
2145 NVL(ctl.memo_line_id, AR_NUMBER_DUMMY) =
2146 NVL(
2147 DECODE(p_line_rec.memo_line_id,
2148 AR_NUMBER_DUMMY, ctl.memo_line_id,
2149 p_line_rec.memo_line_id),
2150 AR_NUMBER_DUMMY
2151 )
2152 AND
2153 NVL(ctl.inventory_item_id, AR_NUMBER_DUMMY) =
2154 NVL(
2155 DECODE(p_line_rec.inventory_item_id,
2156 AR_NUMBER_DUMMY, ctl.inventory_item_id,
2157 p_line_rec.inventory_item_id),
2158 AR_NUMBER_DUMMY
2159 )
2160 AND
2161 NVL(ctl.item_exception_rate_id, AR_NUMBER_DUMMY) =
2162 NVL(
2163 DECODE(p_line_rec.item_exception_rate_id,
2164 AR_NUMBER_DUMMY, ctl.item_exception_rate_id,
2165 p_line_rec.item_exception_rate_id),
2166 AR_NUMBER_DUMMY
2167 )
2168 AND
2169 NVL(ctl.description, AR_TEXT_DUMMY) =
2170 NVL(
2171 DECODE(p_line_rec.description,
2172 AR_TEXT_DUMMY, ctl.description,
2173 p_line_rec.description),
2174 AR_TEXT_DUMMY
2175 )
2176 AND
2177 NVL(ctl.item_context, AR_TEXT_DUMMY) =
2178 NVL(
2179 DECODE(p_line_rec.item_context,
2180 AR_TEXT_DUMMY, ctl.item_context,
2181 p_line_rec.item_context),
2182 AR_TEXT_DUMMY
2183 )
2184 AND
2185 NVL(ctl.initial_customer_trx_line_id, AR_NUMBER_DUMMY) =
2186 NVL(
2187 DECODE(p_line_rec.initial_customer_trx_line_id,
2188 AR_NUMBER_DUMMY, ctl.initial_customer_trx_line_id,
2189 p_line_rec.initial_customer_trx_line_id),
2190 AR_NUMBER_DUMMY
2191 )
2192 AND
2193 NVL(ctl.link_to_cust_trx_line_id, AR_NUMBER_DUMMY) =
2194 NVL(
2195 DECODE(p_line_rec.link_to_cust_trx_line_id,
2196 AR_NUMBER_DUMMY, ctl.link_to_cust_trx_line_id,
2197 p_line_rec.link_to_cust_trx_line_id),
2198 AR_NUMBER_DUMMY
2199 )
2200 AND
2201 NVL(ctl.previous_customer_trx_id, AR_NUMBER_DUMMY) =
2202 NVL(
2203 DECODE(p_line_rec.previous_customer_trx_id,
2204 AR_NUMBER_DUMMY, ctl.previous_customer_trx_id,
2205 p_line_rec.previous_customer_trx_id),
2206 AR_NUMBER_DUMMY
2207 )
2208 AND
2209 NVL(ctl.previous_customer_trx_line_id, AR_NUMBER_DUMMY) =
2210 NVL(
2211 DECODE(p_line_rec.previous_customer_trx_line_id,
2212 AR_NUMBER_DUMMY, ctl.previous_customer_trx_line_id,
2213 p_line_rec.previous_customer_trx_line_id),
2214 AR_NUMBER_DUMMY
2215 )
2216 AND
2217 NVL(ctl.accounting_rule_duration, AR_NUMBER_DUMMY) =
2218 NVL(
2219 DECODE(p_line_rec.accounting_rule_duration,
2220 AR_NUMBER_DUMMY, ctl.accounting_rule_duration,
2221 p_line_rec.accounting_rule_duration),
2222 AR_NUMBER_DUMMY
2223 )
2224 AND
2225 NVL(ctl.accounting_rule_id, AR_NUMBER_DUMMY) =
2226 NVL(
2227 DECODE(p_line_rec.accounting_rule_id,
2228 AR_NUMBER_DUMMY, ctl.accounting_rule_id,
2229 p_line_rec.accounting_rule_id),
2230 AR_NUMBER_DUMMY
2231 )
2232 AND
2233 NVL(ctl.rule_start_date, AR_DATE_DUMMY) =
2234 NVL(
2235 DECODE(p_line_rec.rule_start_date,
2236 AR_DATE_DUMMY, ctl.rule_start_date,
2237 p_line_rec.rule_start_date),
2238 AR_DATE_DUMMY
2239 )
2240 AND
2241 NVL(ctl.autorule_complete_flag, AR_FLAG_DUMMY) =
2242 NVL(
2243 DECODE(p_line_rec.autorule_complete_flag,
2244 AR_FLAG_DUMMY, ctl.autorule_complete_flag,
2245 p_line_rec.autorule_complete_flag),
2246 AR_FLAG_DUMMY
2247 )
2248 AND
2249 NVL(ctl.autorule_duration_processed, AR_NUMBER_DUMMY) =
2250 NVL(
2251 DECODE(p_line_rec.autorule_duration_processed,
2252 AR_NUMBER_DUMMY, ctl.autorule_duration_processed,
2253 p_line_rec.autorule_duration_processed),
2254 AR_NUMBER_DUMMY
2255 )
2256 AND
2257 NVL(ctl.reason_code, AR_TEXT_DUMMY) =
2258 NVL(
2259 DECODE(p_line_rec.reason_code,
2260 AR_TEXT_DUMMY, ctl.reason_code,
2261 p_line_rec.reason_code),
2262 AR_TEXT_DUMMY
2263 )
2264 AND
2265 NVL(ctl.warehouse_id, AR_NUMBER_DUMMY) =
2266 NVL(
2267 DECODE(p_line_rec.warehouse_id,
2268 AR_NUMBER_DUMMY, ctl.warehouse_id,
2269 p_line_rec.warehouse_id),
2270 AR_NUMBER_DUMMY
2271 )
2272 AND
2273 NVL(ctl.translated_description, AR_TEXT_DUMMY) =
2274 NVL(
2275 DECODE(p_line_rec.translated_description,
2276 AR_TEXT_DUMMY, ctl.translated_description,
2277 p_line_rec.translated_description),
2278 AR_TEXT_DUMMY
2279 )
2280 AND
2281 NVL(ctl.last_period_to_credit, AR_NUMBER_DUMMY) =
2282 NVL(
2283 DECODE(p_line_rec.last_period_to_credit,
2284 AR_NUMBER_DUMMY, ctl.last_period_to_credit,
2285 p_line_rec.last_period_to_credit),
2286 AR_NUMBER_DUMMY
2287 )
2288 AND
2289 NVL(ctl.sales_order, AR_TEXT_DUMMY) =
2290 NVL(
2291 DECODE(p_line_rec.sales_order,
2292 AR_TEXT_DUMMY, ctl.sales_order,
2293 p_line_rec.sales_order),
2294 AR_TEXT_DUMMY
2295 )
2296 AND
2297 NVL(ctl.sales_order_date, AR_DATE_DUMMY) =
2298 NVL(
2299 DECODE(p_line_rec.sales_order_date,
2300 AR_DATE_DUMMY, ctl.sales_order_date,
2301 p_line_rec.sales_order_date),
2302 AR_DATE_DUMMY
2303 )
2304 AND
2305 NVL(ctl.sales_order_line, AR_TEXT_DUMMY) =
2306 NVL(
2307 DECODE(p_line_rec.sales_order_line,
2308 AR_TEXT_DUMMY, ctl.sales_order_line,
2309 p_line_rec.sales_order_line),
2310 AR_TEXT_DUMMY
2311 )
2312 AND
2313 NVL(ctl.sales_order_revision, AR_NUMBER_DUMMY) =
2314 NVL(
2315 DECODE(p_line_rec.sales_order_revision,
2316 AR_NUMBER_DUMMY, ctl.sales_order_revision,
2317 p_line_rec.sales_order_revision),
2318 AR_NUMBER_DUMMY
2319 )
2320 AND
2321 NVL(ctl.sales_order_source, AR_TEXT_DUMMY) =
2322 NVL(
2323 DECODE(p_line_rec.sales_order_source,
2324 AR_TEXT_DUMMY, ctl.sales_order_source,
2325 p_line_rec.sales_order_source),
2326 AR_TEXT_DUMMY
2327 )
2328 AND
2329 NVL(ctl.vat_tax_id, AR_NUMBER_DUMMY) =
2330 NVL(
2331 DECODE(p_line_rec.vat_tax_id,
2332 AR_NUMBER_DUMMY, ctl.vat_tax_id,
2333 p_line_rec.vat_tax_id),
2334 AR_NUMBER_DUMMY
2335 )
2336 AND
2337 NVL(ctl.tax_exempt_flag, AR_FLAG_DUMMY) =
2338 NVL(
2339 DECODE(p_line_rec.tax_exempt_flag,
2340 AR_FLAG_DUMMY, ctl.tax_exempt_flag,
2341 p_line_rec.tax_exempt_flag),
2342 AR_FLAG_DUMMY
2343 )
2344 AND
2345 NVL(ctl.sales_tax_id, AR_NUMBER_DUMMY) =
2346 NVL(
2347 DECODE(p_line_rec.sales_tax_id,
2348 AR_NUMBER_DUMMY, ctl.sales_tax_id,
2349 p_line_rec.sales_tax_id),
2350 AR_NUMBER_DUMMY
2351 )
2352 AND
2353 NVL(ctl.location_segment_id, AR_NUMBER_DUMMY) =
2354 NVL(
2355 DECODE(p_line_rec.location_segment_id,
2356 AR_NUMBER_DUMMY, ctl.location_segment_id,
2357 p_line_rec.location_segment_id),
2358 AR_NUMBER_DUMMY
2359 )
2360 AND
2361 NVL(ctl.tax_exempt_number, AR_TEXT_DUMMY) =
2362 NVL(
2363 DECODE(p_line_rec.tax_exempt_number,
2364 AR_TEXT_DUMMY, ctl.tax_exempt_number,
2365 p_line_rec.tax_exempt_number),
2366 AR_TEXT_DUMMY
2367 )
2368 AND
2369 NVL(ctl.tax_exempt_reason_code, AR_TEXT_DUMMY) =
2370 NVL(
2371 DECODE(p_line_rec.tax_exempt_reason_code,
2372 AR_TEXT_DUMMY, ctl.tax_exempt_reason_code,
2373 p_line_rec.tax_exempt_reason_code),
2374 AR_TEXT_DUMMY
2375 )
2376 AND
2377 NVL(ctl.tax_vendor_return_code, AR_TEXT_DUMMY) =
2378 NVL(
2379 DECODE(p_line_rec.tax_vendor_return_code,
2380 AR_TEXT_DUMMY, ctl.tax_vendor_return_code,
2381 p_line_rec.tax_vendor_return_code),
2382 AR_TEXT_DUMMY
2383 )
2384 AND
2385 NVL(ctl.taxable_flag, AR_FLAG_DUMMY) =
2386 NVL(
2387 DECODE(p_line_rec.taxable_flag,
2388 AR_FLAG_DUMMY, ctl.taxable_flag,
2389 p_line_rec.taxable_flag),
2390 AR_FLAG_DUMMY
2391 )
2392 )
2393 AND
2394 (
2395 NVL(ctl.tax_exemption_id, AR_NUMBER_DUMMY) =
2396 NVL(
2397 DECODE(p_line_rec.tax_exemption_id,
2398 AR_NUMBER_DUMMY, ctl.tax_exemption_id,
2399 p_line_rec.tax_exemption_id),
2400 AR_NUMBER_DUMMY
2401 )
2402 AND
2403 NVL(ctl.tax_precedence, AR_NUMBER_DUMMY) =
2404 NVL(
2405 DECODE(p_line_rec.tax_precedence,
2406 AR_NUMBER_DUMMY, ctl.tax_precedence,
2407 p_line_rec.tax_precedence),
2408 AR_NUMBER_DUMMY
2409 )
2410 AND
2411 NVL(ctl.tax_rate, AR_NUMBER_DUMMY) =
2412 NVL(
2413 DECODE(p_line_rec.tax_rate,
2414 AR_NUMBER_DUMMY, ctl.tax_rate,
2415 p_line_rec.tax_rate),
2416 AR_NUMBER_DUMMY
2417 )
2418 AND
2419 NVL(ctl.uom_code, AR_TEXT3_DUMMY) =
2420 NVL(
2421 DECODE(p_line_rec.uom_code,
2422 AR_TEXT3_DUMMY, ctl.uom_code,
2423 p_line_rec.uom_code),
2424 AR_TEXT3_DUMMY
2425 )
2426 AND
2427 NVL(ctl.autotax, AR_FLAG_DUMMY) =
2428 NVL(
2429 DECODE(p_line_rec.autotax,
2430 AR_FLAG_DUMMY, ctl.autotax,
2431 p_line_rec.autotax),
2432 AR_FLAG_DUMMY
2433 )
2434 AND
2435 NVL(ctl.movement_id, AR_NUMBER_DUMMY) =
2436 NVL(
2437 DECODE(p_line_rec.movement_id,
2438 AR_NUMBER_DUMMY, ctl.movement_id,
2439 p_line_rec.movement_id),
2440 AR_NUMBER_DUMMY
2441 )
2442 AND
2443 NVL(ctl.default_ussgl_transaction_code, AR_TEXT_DUMMY) =
2444 NVL(
2445 DECODE(p_line_rec.default_ussgl_transaction_code,
2446 AR_TEXT_DUMMY, ctl.default_ussgl_transaction_code,
2447 p_line_rec.default_ussgl_transaction_code),
2448 AR_TEXT_DUMMY
2449 )
2450 AND
2451 NVL(ctl.default_ussgl_trx_code_context, AR_TEXT_DUMMY) =
2452 NVL(
2453 DECODE(p_line_rec.default_ussgl_trx_code_context,
2454 AR_TEXT_DUMMY, ctl.default_ussgl_trx_code_context,
2455 p_line_rec.default_ussgl_trx_code_context),
2456 AR_TEXT_DUMMY
2457 )
2458 AND
2459 NVL(ctl.interface_line_context, AR_TEXT_DUMMY) =
2460 NVL(
2461 DECODE(p_line_rec.interface_line_context,
2462 AR_TEXT_DUMMY, ctl.interface_line_context,
2463 p_line_rec.interface_line_context),
2464 AR_TEXT_DUMMY
2465 )
2466 AND
2467 NVL(ctl.interface_line_attribute1, AR_TEXT_DUMMY) =
2468 NVL(
2469 DECODE(p_line_rec.interface_line_attribute1,
2470 AR_TEXT_DUMMY, ctl.interface_line_attribute1,
2471 p_line_rec.interface_line_attribute1),
2472 AR_TEXT_DUMMY
2473 )
2474 AND
2475 NVL(ctl.interface_line_attribute2, AR_TEXT_DUMMY) =
2476 NVL(
2477 DECODE(p_line_rec.interface_line_attribute2,
2478 AR_TEXT_DUMMY, ctl.interface_line_attribute2,
2479 p_line_rec.interface_line_attribute2),
2480 AR_TEXT_DUMMY
2481 )
2482 AND
2483 NVL(ctl.interface_line_attribute3, AR_TEXT_DUMMY) =
2484 NVL(
2485 DECODE(p_line_rec.interface_line_attribute3,
2486 AR_TEXT_DUMMY, ctl.interface_line_attribute3,
2487 p_line_rec.interface_line_attribute3),
2488 AR_TEXT_DUMMY
2489 )
2490 AND
2491 NVL(ctl.interface_line_attribute4, AR_TEXT_DUMMY) =
2492 NVL(
2493 DECODE(p_line_rec.interface_line_attribute4,
2494 AR_TEXT_DUMMY, ctl.interface_line_attribute4,
2495 p_line_rec.interface_line_attribute4),
2496 AR_TEXT_DUMMY
2497 )
2498 AND
2499 NVL(ctl.interface_line_attribute5, AR_TEXT_DUMMY) =
2500 NVL(
2501 DECODE(p_line_rec.interface_line_attribute5,
2502 AR_TEXT_DUMMY, ctl.interface_line_attribute5,
2503 p_line_rec.interface_line_attribute5),
2504 AR_TEXT_DUMMY
2505 )
2506 AND
2507 NVL(ctl.interface_line_attribute6, AR_TEXT_DUMMY) =
2508 NVL(
2509 DECODE(p_line_rec.interface_line_attribute6,
2510 AR_TEXT_DUMMY, ctl.interface_line_attribute6,
2511 p_line_rec.interface_line_attribute6),
2512 AR_TEXT_DUMMY
2513 )
2514 AND
2515 NVL(ctl.interface_line_attribute7, AR_TEXT_DUMMY) =
2516 NVL(
2517 DECODE(p_line_rec.interface_line_attribute7,
2518 AR_TEXT_DUMMY, ctl.interface_line_attribute7,
2519 p_line_rec.interface_line_attribute7),
2520 AR_TEXT_DUMMY
2521 )
2522 AND
2523 NVL(ctl.interface_line_attribute8, AR_TEXT_DUMMY) =
2524 NVL(
2525 DECODE(p_line_rec.interface_line_attribute8,
2526 AR_TEXT_DUMMY, ctl.interface_line_attribute8,
2527 p_line_rec.interface_line_attribute8),
2528 AR_TEXT_DUMMY
2529 )
2530 AND
2531 NVL(ctl.interface_line_attribute9, AR_TEXT_DUMMY) =
2532 NVL(
2533 DECODE(p_line_rec.interface_line_attribute9,
2534 AR_TEXT_DUMMY, ctl.interface_line_attribute9,
2535 p_line_rec.interface_line_attribute9),
2536 AR_TEXT_DUMMY
2537 )
2538 AND
2539 NVL(ctl.interface_line_attribute10, AR_TEXT_DUMMY) =
2540 NVL(
2541 DECODE(p_line_rec.interface_line_attribute10,
2542 AR_TEXT_DUMMY, ctl.interface_line_attribute10,
2543 p_line_rec.interface_line_attribute10),
2544 AR_TEXT_DUMMY
2545 )
2546 AND
2547 NVL(ctl.interface_line_attribute11, AR_TEXT_DUMMY) =
2548 NVL(
2549 DECODE(p_line_rec.interface_line_attribute11,
2550 AR_TEXT_DUMMY, ctl.interface_line_attribute11,
2551 p_line_rec.interface_line_attribute11),
2552 AR_TEXT_DUMMY
2553 )
2554 AND
2555 NVL(ctl.interface_line_attribute12, AR_TEXT_DUMMY) =
2556 NVL(
2557 DECODE(p_line_rec.interface_line_attribute12,
2558 AR_TEXT_DUMMY, ctl.interface_line_attribute12,
2559 p_line_rec.interface_line_attribute12),
2560 AR_TEXT_DUMMY
2561 )
2562 AND
2563 NVL(ctl.interface_line_attribute13, AR_TEXT_DUMMY) =
2564 NVL(
2565 DECODE(p_line_rec.interface_line_attribute13,
2566 AR_TEXT_DUMMY, ctl.interface_line_attribute13,
2567 p_line_rec.interface_line_attribute13),
2568 AR_TEXT_DUMMY
2569 )
2570 AND
2571 NVL(ctl.interface_line_attribute14, AR_TEXT_DUMMY) =
2572 NVL(
2573 DECODE(p_line_rec.interface_line_attribute14,
2574 AR_TEXT_DUMMY, ctl.interface_line_attribute14,
2575 p_line_rec.interface_line_attribute14),
2576 AR_TEXT_DUMMY
2577 )
2578 AND
2579 NVL(ctl.interface_line_attribute15, AR_TEXT_DUMMY) =
2580 NVL(
2581 DECODE(p_line_rec.interface_line_attribute15,
2582 AR_TEXT_DUMMY, ctl.interface_line_attribute15,
2583 p_line_rec.interface_line_attribute15),
2584 AR_TEXT_DUMMY
2585 )
2586 AND
2587 NVL(ctl.attribute_category, AR_TEXT_DUMMY) =
2588 NVL(
2589 DECODE(p_line_rec.attribute_category,
2590 AR_TEXT_DUMMY, ctl.attribute_category,
2591 p_line_rec.attribute_category),
2592 AR_TEXT_DUMMY
2593 )
2594 AND
2595 NVL(ctl.attribute1, AR_TEXT_DUMMY) =
2596 NVL(
2597 DECODE(p_line_rec.attribute1,
2598 AR_TEXT_DUMMY, ctl.attribute1,
2599 p_line_rec.attribute1),
2600 AR_TEXT_DUMMY
2601 )
2602 AND
2603 NVL(ctl.attribute2, AR_TEXT_DUMMY) =
2604 NVL(
2605 DECODE(p_line_rec.attribute2,
2606 AR_TEXT_DUMMY, ctl.attribute2,
2607 p_line_rec.attribute2),
2608 AR_TEXT_DUMMY
2609 )
2610 AND
2611 NVL(ctl.attribute3, AR_TEXT_DUMMY) =
2612 NVL(
2613 DECODE(p_line_rec.attribute3,
2614 AR_TEXT_DUMMY, ctl.attribute3,
2615 p_line_rec.attribute3),
2616 AR_TEXT_DUMMY
2617 )
2618 AND
2619 NVL(ctl.attribute4, AR_TEXT_DUMMY) =
2620 NVL(
2621 DECODE(p_line_rec.attribute4,
2622 AR_TEXT_DUMMY, ctl.attribute4,
2623 p_line_rec.attribute4),
2624 AR_TEXT_DUMMY
2625 )
2626 AND
2627 NVL(ctl.attribute5, AR_TEXT_DUMMY) =
2628 NVL(
2629 DECODE(p_line_rec.attribute5,
2630 AR_TEXT_DUMMY, ctl.attribute5,
2631 p_line_rec.attribute5),
2632 AR_TEXT_DUMMY
2633 )
2634 AND
2635 NVL(ctl.attribute6, AR_TEXT_DUMMY) =
2636 NVL(
2637 DECODE(p_line_rec.attribute6,
2638 AR_TEXT_DUMMY, ctl.attribute6,
2639 p_line_rec.attribute6),
2640 AR_TEXT_DUMMY
2641 )
2642 AND
2643 NVL(ctl.attribute7, AR_TEXT_DUMMY) =
2644 NVL(
2645 DECODE(p_line_rec.attribute7,
2646 AR_TEXT_DUMMY, ctl.attribute7,
2647 p_line_rec.attribute7),
2648 AR_TEXT_DUMMY
2649 )
2650 AND
2651 NVL(ctl.attribute8, AR_TEXT_DUMMY) =
2652 NVL(
2653 DECODE(p_line_rec.attribute8,
2654 AR_TEXT_DUMMY, ctl.attribute8,
2655 p_line_rec.attribute8),
2656 AR_TEXT_DUMMY
2657 )
2658 AND
2659 NVL(ctl.attribute9, AR_TEXT_DUMMY) =
2660 NVL(
2661 DECODE(p_line_rec.attribute9,
2662 AR_TEXT_DUMMY, ctl.attribute9,
2663 p_line_rec.attribute9),
2664 AR_TEXT_DUMMY
2665 )
2666 AND
2667 NVL(ctl.attribute10, AR_TEXT_DUMMY) =
2668 NVL(
2669 DECODE(p_line_rec.attribute10,
2670 AR_TEXT_DUMMY, ctl.attribute10,
2671 p_line_rec.attribute10),
2672 AR_TEXT_DUMMY
2673 )
2674 AND
2675 NVL(ctl.attribute11, AR_TEXT_DUMMY) =
2676 NVL(
2677 DECODE(p_line_rec.attribute11,
2678 AR_TEXT_DUMMY, ctl.attribute11,
2679 p_line_rec.attribute11),
2680 AR_TEXT_DUMMY
2681 )
2682 AND
2683 NVL(ctl.attribute12, AR_TEXT_DUMMY) =
2684 NVL(
2685 DECODE(p_line_rec.attribute12,
2686 AR_TEXT_DUMMY, ctl.attribute12,
2687 p_line_rec.attribute12),
2688 AR_TEXT_DUMMY
2689 )
2690 AND
2691 NVL(ctl.attribute13, AR_TEXT_DUMMY) =
2692 NVL(
2693 DECODE(p_line_rec.attribute13,
2694 AR_TEXT_DUMMY, ctl.attribute13,
2695 p_line_rec.attribute13),
2696 AR_TEXT_DUMMY
2697 )
2698 AND
2699 NVL(ctl.attribute14, AR_TEXT_DUMMY) =
2700 NVL(
2701 DECODE(p_line_rec.attribute14,
2702 AR_TEXT_DUMMY, ctl.attribute14,
2703 p_line_rec.attribute14),
2704 AR_TEXT_DUMMY
2705 )
2706 AND
2707 NVL(ctl.attribute15, AR_TEXT_DUMMY) =
2708 NVL(
2709 DECODE(p_line_rec.attribute15,
2710 AR_TEXT_DUMMY, ctl.attribute15,
2711 p_line_rec.attribute15),
2712 AR_TEXT_DUMMY
2713 )
2714 AND
2715 NVL(ctl.global_attribute_category, AR_TEXT_DUMMY) =
2716 NVL(
2717 DECODE(p_line_rec.global_attribute_category,
2718 AR_TEXT_DUMMY, ctl.global_attribute_category,
2719 p_line_rec.global_attribute_category),
2720 AR_TEXT_DUMMY
2721 )
2722 AND
2723 NVL(ctl.global_attribute1, AR_TEXT_DUMMY) =
2724 NVL(
2725 DECODE(p_line_rec.global_attribute1,
2726 AR_TEXT_DUMMY, ctl.global_attribute1,
2727 p_line_rec.global_attribute1),
2728 AR_TEXT_DUMMY
2729 )
2730 AND
2731 NVL(ctl.global_attribute2, AR_TEXT_DUMMY) =
2732 NVL(
2733 DECODE(p_line_rec.global_attribute2,
2734 AR_TEXT_DUMMY, ctl.global_attribute2,
2735 p_line_rec.global_attribute2),
2736 AR_TEXT_DUMMY
2737 )
2738 AND
2739 NVL(ctl.global_attribute3, AR_TEXT_DUMMY) =
2740 NVL(
2741 DECODE(p_line_rec.global_attribute3,
2742 AR_TEXT_DUMMY, ctl.global_attribute3,
2743 p_line_rec.global_attribute3),
2744 AR_TEXT_DUMMY
2745 )
2746 AND
2747 NVL(ctl.global_attribute4, AR_TEXT_DUMMY) =
2748 NVL(
2749 DECODE(p_line_rec.global_attribute4,
2750 AR_TEXT_DUMMY, ctl.global_attribute4,
2751 p_line_rec.global_attribute4),
2752 AR_TEXT_DUMMY
2753 )
2754 AND
2755 NVL(ctl.global_attribute5, AR_TEXT_DUMMY) =
2756 NVL(
2757 DECODE(p_line_rec.global_attribute5,
2758 AR_TEXT_DUMMY, ctl.global_attribute5,
2759 p_line_rec.global_attribute5),
2760 AR_TEXT_DUMMY
2761 )
2762 AND
2763 NVL(ctl.global_attribute6, AR_TEXT_DUMMY) =
2764 NVL(
2765 DECODE(p_line_rec.global_attribute6,
2766 AR_TEXT_DUMMY, ctl.global_attribute6,
2767 p_line_rec.global_attribute6),
2768 AR_TEXT_DUMMY
2769 )
2770 AND
2771 NVL(ctl.global_attribute7, AR_TEXT_DUMMY) =
2772 NVL(
2773 DECODE(p_line_rec.global_attribute7,
2774 AR_TEXT_DUMMY, ctl.global_attribute7,
2775 p_line_rec.global_attribute7),
2776 AR_TEXT_DUMMY
2777 )
2778 AND
2779 NVL(ctl.global_attribute8, AR_TEXT_DUMMY) =
2780 NVL(
2781 DECODE(p_line_rec.global_attribute8,
2782 AR_TEXT_DUMMY, ctl.global_attribute8,
2783 p_line_rec.global_attribute8),
2784 AR_TEXT_DUMMY
2785 )
2786 AND
2787 NVL(ctl.global_attribute9, AR_TEXT_DUMMY) =
2788 NVL(
2789 DECODE(p_line_rec.global_attribute9,
2790 AR_TEXT_DUMMY, ctl.global_attribute9,
2791 p_line_rec.global_attribute9),
2792 AR_TEXT_DUMMY
2793 )
2794 AND
2795 NVL(ctl.global_attribute10, AR_TEXT_DUMMY) =
2796 NVL(
2797 DECODE(p_line_rec.global_attribute10,
2798 AR_TEXT_DUMMY, ctl.global_attribute10,
2799 p_line_rec.global_attribute10),
2800 AR_TEXT_DUMMY
2801 )
2802 AND
2803 NVL(ctl.global_attribute11, AR_TEXT_DUMMY) =
2804 NVL(
2805 DECODE(p_line_rec.global_attribute11,
2806 AR_TEXT_DUMMY, ctl.global_attribute11,
2807 p_line_rec.global_attribute11),
2808 AR_TEXT_DUMMY
2809 )
2810 AND
2811 NVL(ctl.global_attribute12, AR_TEXT_DUMMY) =
2812 NVL(
2813 DECODE(p_line_rec.global_attribute12,
2814 AR_TEXT_DUMMY, ctl.global_attribute12,
2815 p_line_rec.global_attribute12),
2816 AR_TEXT_DUMMY
2817 )
2818 AND
2819 NVL(ctl.global_attribute13, AR_TEXT_DUMMY) =
2820 NVL(
2821 DECODE(p_line_rec.global_attribute13,
2822 AR_TEXT_DUMMY, ctl.global_attribute13,
2823 p_line_rec.global_attribute13),
2824 AR_TEXT_DUMMY
2825 )
2826 AND
2827 NVL(ctl.global_attribute14, AR_TEXT_DUMMY) =
2828 NVL(
2829 DECODE(p_line_rec.global_attribute14,
2830 AR_TEXT_DUMMY, ctl.global_attribute14,
2831 p_line_rec.global_attribute14),
2832 AR_TEXT_DUMMY
2833 )
2834 AND
2835 NVL(ctl.global_attribute15, AR_TEXT_DUMMY) =
2836 NVL(
2837 DECODE(p_line_rec.global_attribute15,
2838 AR_TEXT_DUMMY, ctl.global_attribute15,
2839 p_line_rec.global_attribute15),
2840 AR_TEXT_DUMMY
2841 )
2842 AND
2843 NVL(ctl.global_attribute16, AR_TEXT_DUMMY) =
2844 NVL(
2845 DECODE(p_line_rec.global_attribute16,
2846 AR_TEXT_DUMMY, ctl.global_attribute16,
2847 p_line_rec.global_attribute16),
2848 AR_TEXT_DUMMY
2849 )
2850 AND
2851 NVL(ctl.global_attribute17, AR_TEXT_DUMMY) =
2852 NVL(
2853 DECODE(p_line_rec.global_attribute17,
2854 AR_TEXT_DUMMY, ctl.global_attribute17,
2855 p_line_rec.global_attribute17),
2856 AR_TEXT_DUMMY
2857 )
2858 AND
2859 NVL(ctl.global_attribute18, AR_TEXT_DUMMY) =
2860 NVL(
2861 DECODE(p_line_rec.global_attribute18,
2862 AR_TEXT_DUMMY, ctl.global_attribute18,
2863 p_line_rec.global_attribute18),
2864 AR_TEXT_DUMMY
2865 )
2866 AND
2867 NVL(ctl.global_attribute19, AR_TEXT_DUMMY) =
2868 NVL(
2869 DECODE(p_line_rec.global_attribute19,
2870 AR_TEXT_DUMMY, ctl.global_attribute19,
2871 p_line_rec.global_attribute19),
2872 AR_TEXT_DUMMY
2873 )
2874 AND
2875 NVL(ctl.global_attribute20, AR_TEXT_DUMMY) =
2876 NVL(
2877 DECODE(p_line_rec.global_attribute20,
2878 AR_TEXT_DUMMY, ctl.global_attribute20,
2879 p_line_rec.global_attribute20),
2880 AR_TEXT_DUMMY
2881 )
2882 AND
2883 NVL(ctl.last_update_date, AR_DATE_DUMMY) =
2884 NVL(
2885 DECODE(l_ignore_who_flag,
2886 'Y', NVL(ctl.last_update_date, AR_DATE_DUMMY),
2887 DECODE(
2888 p_line_rec.last_update_date,
2889 AR_DATE_DUMMY, ctl.last_update_date,
2890 p_line_rec.last_update_date
2891 )
2892 ),
2893 AR_DATE_DUMMY
2894 )
2895 AND
2896 NVL(ctl.last_updated_by, AR_NUMBER_DUMMY) =
2897 NVL(
2898 DECODE(l_ignore_who_flag,
2899 'Y', NVL(ctl.last_updated_by, AR_NUMBER_DUMMY),
2900 DECODE(
2901 p_line_rec.last_updated_by,
2902 AR_NUMBER_DUMMY, ctl.last_updated_by,
2903 p_line_rec.last_updated_by
2904 )
2905 ),
2906 AR_NUMBER_DUMMY
2907 )
2908 AND
2909 NVL(ctl.creation_date, AR_DATE_DUMMY) =
2910 NVL(
2911 DECODE(l_ignore_who_flag,
2912 'Y', NVL(ctl.creation_date, AR_DATE_DUMMY),
2913 DECODE(
2914 p_line_rec.creation_date,
2915 AR_DATE_DUMMY, ctl.creation_date,
2916 p_line_rec.creation_date
2917 )
2918 ),
2919 AR_DATE_DUMMY
2920 )
2921 AND
2922 NVL(ctl.created_by, AR_NUMBER_DUMMY) =
2923 NVL(
2924 DECODE(l_ignore_who_flag,
2925 'Y', NVL(ctl.created_by, AR_NUMBER_DUMMY),
2926 DECODE(
2927 p_line_rec.created_by,
2928 AR_NUMBER_DUMMY, ctl.created_by,
2929 p_line_rec.created_by
2930 )
2931 ),
2932 AR_NUMBER_DUMMY
2933 )
2934 AND
2935 NVL(ctl.last_update_login, AR_NUMBER_DUMMY) =
2936 NVL(
2937 DECODE(l_ignore_who_flag,
2938 'Y', NVL(ctl.last_update_login, AR_NUMBER_DUMMY),
2939 DECODE(
2940 p_line_rec.last_update_login,
2941 AR_NUMBER_DUMMY, ctl.last_update_login,
2942 p_line_rec.last_update_login
2943 )
2944 ),
2945 AR_NUMBER_DUMMY
2946 )
2947 AND
2948 NVL(ctl.program_application_id, AR_NUMBER_DUMMY) =
2949 NVL(
2950 DECODE(l_ignore_who_flag,
2951 'Y', NVL(ctl.program_application_id, AR_NUMBER_DUMMY),
2952 DECODE(
2953 p_line_rec.program_application_id,
2954 AR_NUMBER_DUMMY, ctl.program_application_id,
2955 p_line_rec.program_application_id
2956 )
2957 ),
2958 AR_NUMBER_DUMMY
2959 )
2960 AND
2961 NVL(ctl.program_id, AR_NUMBER_DUMMY) =
2962 NVL(
2963 DECODE(l_ignore_who_flag,
2964 'Y', NVL(ctl.program_id, AR_NUMBER_DUMMY),
2965 DECODE(
2966 p_line_rec.program_id,
2967 AR_NUMBER_DUMMY, ctl.program_id,
2968 p_line_rec.program_id
2969 )
2970 ),
2971 AR_NUMBER_DUMMY
2972 )
2973 AND
2974 NVL(ctl.program_update_date, AR_DATE_DUMMY) =
2975 NVL(
2976 DECODE(l_ignore_who_flag,
2977 'Y', NVL(ctl.program_update_date, AR_DATE_DUMMY),
2978 DECODE(
2979 p_line_rec.program_update_date,
2980 AR_DATE_DUMMY, ctl.program_update_date,
2981 p_line_rec.program_update_date
2982 )
2983 ),
2984 AR_DATE_DUMMY
2985 )
2986 AND
2987 NVL(ctl.set_of_books_id, AR_NUMBER_DUMMY) =
2988 NVL(
2989 DECODE(p_line_rec.set_of_books_id,
2990 AR_NUMBER_DUMMY, ctl.set_of_books_id,
2991 p_line_rec.set_of_books_id),
2992 AR_NUMBER_DUMMY
2993 )
2994 /* Rel. 11 Changes: */
2995 AND
2996 NVL(ctl.gross_extended_amount, NVL(ctl.extended_amount,
2997 AR_NUMBER_DUMMY)) =
2998 NVL(
2999 DECODE(p_line_rec.gross_extended_amount,
3000 AR_NUMBER_DUMMY,
3001 NVL(ctl.gross_extended_amount,
3002 ctl.extended_amount),
3003 p_line_rec.gross_extended_amount),
3004 AR_NUMBER_DUMMY
3005 )
3006 AND
3007 NVL(ctl.gross_unit_selling_price, NVL(ctl.unit_selling_price,
3008 AR_NUMBER_DUMMY)) =
3009 NVL(
3010 DECODE(p_line_rec.gross_unit_selling_price,
3011 AR_NUMBER_DUMMY,
3012 NVL(ctl.gross_unit_selling_price,
3013 ctl.unit_selling_price),
3014 p_line_rec.gross_unit_selling_price),
3015 AR_NUMBER_DUMMY
3016 )
3017 AND
3018 NVL(ctl.amount_includes_tax_flag, AR_FLAG_DUMMY) =
3019 NVL(
3020 DECODE(p_line_rec.amount_includes_tax_flag,
3021 AR_FLAG_DUMMY, ctl.amount_includes_tax_flag,
3022 p_line_rec.amount_includes_tax_flag),
3023 AR_FLAG_DUMMY
3024 )
3025 /* Bug 853757 */
3026 AND
3027 NVL(ctl.taxable_amount, AR_NUMBER_DUMMY) =
3028 NVL(
3029 DECODE(p_line_rec.taxable_amount,
3030 AR_NUMBER_DUMMY, ctl.taxable_amount,
3031 p_line_rec.taxable_amount),
3032 AR_NUMBER_DUMMY
3033 )
3034 AND
3035 NVL(ctl.extended_acctd_amount, AR_NUMBER_DUMMY) =
3036 NVL(
3037 DECODE(p_line_rec.extended_acctd_amount,
3038 AR_NUMBER_DUMMY, ctl.extended_acctd_amount,
3039 p_line_rec.extended_acctd_amount),
3040 AR_NUMBER_DUMMY
3041 )
3042 AND
3043 NVL(ctl.br_ref_customer_trx_id, AR_NUMBER_DUMMY) =
3044 NVL(
3045 DECODE(p_line_rec.br_ref_customer_trx_id,
3046 AR_NUMBER_DUMMY, ctl.br_ref_customer_trx_id,
3047 p_line_rec.br_ref_customer_trx_id),
3048 AR_NUMBER_DUMMY
3049 )
3050 AND
3051 NVL(ctl.br_ref_payment_schedule_id, AR_NUMBER_DUMMY) =
3052 NVL(
3053 DECODE(p_line_rec.br_ref_payment_schedule_id,
3054 AR_NUMBER_DUMMY, ctl.br_ref_payment_schedule_id,
3055 p_line_rec.br_ref_payment_schedule_id),
3056 AR_NUMBER_DUMMY
3057 )
3058 AND
3059 NVL(ctl.br_adjustment_id, AR_NUMBER_DUMMY) =
3060 NVL(
3061 DECODE(p_line_rec.br_adjustment_id,
3062 AR_NUMBER_DUMMY, ctl.br_adjustment_id,
3063 p_line_rec.br_adjustment_id),
3064 AR_NUMBER_DUMMY
3065 )
3066 AND
3067 NVL(ctl.wh_update_date, AR_DATE_DUMMY) =
3068 NVL(
3069 DECODE(p_line_rec.wh_update_date,
3070 AR_DATE_DUMMY, ctl.wh_update_date,
3071 p_line_rec.wh_update_date),
3072 AR_DATE_DUMMY
3073 )
3074 AND
3075 NVL(ctl.rule_end_date, AR_DATE_DUMMY) =
3076 NVL(
3077 DECODE(p_line_rec.rule_end_date,
3078 AR_DATE_DUMMY, ctl.rule_end_date,
3079 p_line_rec.rule_end_date),
3080 AR_DATE_DUMMY
3081 )
3082 /* 4713671 */
3083 AND
3084 NVL(ctl.ship_to_customer_id, AR_NUMBER_DUMMY) =
3085 NVL(
3086 DECODE(p_line_rec.ship_to_customer_id,
3087 AR_NUMBER_DUMMY, ctl.ship_to_customer_id,
3088 p_line_rec.ship_to_customer_id),
3089 AR_NUMBER_DUMMY
3090 )
3091 AND
3092 NVL(ctl.ship_to_site_use_id, AR_NUMBER_DUMMY) =
3093 NVL(
3094 DECODE(p_line_rec.ship_to_site_use_id,
3095 AR_NUMBER_DUMMY, ctl.ship_to_site_use_id,
3096 p_line_rec.ship_to_site_use_id),
3097 AR_NUMBER_DUMMY
3098 )
3099 AND
3100 NVL(ctl.ship_to_contact_id, AR_NUMBER_DUMMY) =
3101 NVL(
3102 DECODE(p_line_rec.ship_to_contact_id,
3103 AR_NUMBER_DUMMY, ctl.ship_to_contact_id,
3104 p_line_rec.ship_to_contact_id),
3105 AR_NUMBER_DUMMY
3106 )
3107 AND
3108 NVL(ctl.tax_classification_code, AR_TEXT_DUMMY) =
3109 NVL(
3110 DECODE(p_line_rec.tax_classification_code,
3111 AR_TEXT_DUMMY, ctl.tax_classification_code,
3112 p_line_rec.tax_classification_code),
3113 AR_TEXT_DUMMY
3114 )
3115 )
3116 FOR UPDATE OF customer_trx_line_id NOWAIT;
3117
3118
3119 IF PG_DEBUG in ('Y', 'C') THEN
3120 arp_util.debug( 'arp_ctl_pkg.lock_compare_p()-');
3121 END IF;
3122
3123 EXCEPTION
3124 WHEN NO_DATA_FOUND THEN
3125 IF PG_DEBUG in ('Y', 'C') THEN
3126 arp_util.debug( '');
3127 arp_util.debug( 'p_customer_trx_line_id = ' ||
3128 p_customer_trx_line_id );
3129 arp_util.debug( '-------- new line record --------');
3130 END IF;
3131 display_line_rec( p_line_rec );
3132
3133 IF PG_DEBUG in ('Y', 'C') THEN
3134 arp_util.debug( '');
3135 arp_util.debug( '-------- old line record --------');
3136 END IF;
3137
3138 fetch_p( l_temp_line_rec,
3139 p_customer_trx_line_id );
3140
3141 display_line_rec( l_temp_line_rec );
3142
3143 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
3144 APP_EXCEPTION.Raise_Exception;
3145
3146 WHEN OTHERS THEN
3147 IF PG_DEBUG in ('Y', 'C') THEN
3148 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.lock_compare_p' );
3149 arp_util.debug( '');
3150 arp_util.debug( '-------- parameters for lock_compare_p() ------');
3151 arp_util.debug( 'p_customer_trx_line_id = ' ||
3152 p_customer_trx_line_id);
3153 arp_util.debug( 'p_ignore_who_flag = ' ||
3154 arp_trx_util.boolean_to_varchar2(p_ignore_who_flag));
3155 END IF;
3156 display_line_rec(p_line_rec);
3157
3158
3159 RAISE;
3160 END;
3161
3162 /*===========================================================================+
3163 | PROCEDURE |
3164 | delete_p |
3165 | |
3166 | DESCRIPTION |
3167 | This procedure deletes the ra_customer_trx_lines row identified by the |
3168 | p_customer_trx_line_id parameter. |
3169 | |
3170 | SCOPE - PUBLIC |
3171 | |
3172 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3173 | arp_util.debug |
3174 | |
3175 | ARGUMENTS : IN: |
3176 | p_customer_trx_line_id - identifies the row to delete |
3177 | OUT: |
3178 | None |
3179 | |
3180 | RETURNS : NONE |
3181 | |
3182 | NOTES |
3183 | |
3184 | MODIFICATION HISTORY |
3185 | 07-JUN-95 Charlie Tomberg Created |
3186 | |
3187 +===========================================================================*/
3188
3189 procedure delete_p( p_customer_trx_line_id IN
3190 ra_customer_trx_lines.customer_trx_line_id%type)
3191 IS
3192
3193
3194 BEGIN
3195
3196 IF PG_DEBUG in ('Y', 'C') THEN
3197 arp_util.debug( 'arp_ctl_pkg.delete_p()+');
3198 arp_util.debug( 'deleting ctlid: ' || p_customer_trx_line_id);
3199 END IF;
3200
3201 DELETE FROM ra_customer_trx_lines
3202 WHERE customer_trx_line_id = p_customer_trx_line_id;
3203
3204 IF ( SQL%ROWCOUNT = 0 )
3205 THEN arp_util.debug('EXCEPTION: arp_ctl_pkg.delete_p()');
3206 RAISE NO_DATA_FOUND;
3207 END IF;
3208
3209 IF PG_DEBUG in ('Y', 'C') THEN
3210 arp_util.debug( 'arp_ctl_pkg.delete_p()-');
3211 END IF;
3212
3213 EXCEPTION
3214 WHEN OTHERS THEN
3215 IF PG_DEBUG in ('Y', 'C') THEN
3216 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.delete_p()');
3217 arp_util.debug( '');
3218 arp_util.debug( '-------- parameters for delete_p() ------');
3219 arp_util.debug( 'p_customer_trx_line_id = ' ||
3220 p_customer_trx_line_id);
3221 END IF;
3222
3223 RAISE;
3224
3225 END;
3226
3227 /*===========================================================================+
3228 | PROCEDURE |
3229 | delete_f_ct_id |
3230 | |
3231 | DESCRIPTION |
3232 | This procedure deletes the ra_customer_trx_lines rows identified by |
3233 | the p_customer_trx_id parameter. |
3234 | |
3235 | SCOPE - PUBLIC |
3236 | |
3237 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3238 | arp_util.debug |
3239 | |
3240 | ARGUMENTS : IN: |
3241 | p_customer_trx_id - identifies the rows to delete |
3242 | OUT: |
3243 | None |
3244 | |
3245 | RETURNS : NONE |
3246 | |
3247 | NOTES |
3248 | |
3249 | MODIFICATION HISTORY |
3250 | 07-JUN-95 Charlie Tomberg Created |
3251 | |
3252 +===========================================================================*/
3253
3254 procedure delete_f_ct_id( p_customer_trx_id IN
3255 ra_customer_trx.customer_trx_id%type)
3256 IS
3257
3258 BEGIN
3259
3260
3261 IF PG_DEBUG in ('Y', 'C') THEN
3262 arp_util.debug( 'arp_ctl_pkg.delete_f_ct_id()+');
3263 END IF;
3264
3265 DELETE FROM ra_customer_trx_lines
3266 WHERE customer_trx_id = p_customer_trx_id;
3267
3268 IF PG_DEBUG in ('Y', 'C') THEN
3269 arp_util.debug( 'arp_ctl_pkg.delete_f_ct_id()-');
3270 END IF;
3271
3272 EXCEPTION
3273 WHEN OTHERS THEN
3274 IF PG_DEBUG in ('Y', 'C') THEN
3275 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.delete_f_ct_id()');
3276 arp_util.debug( '');
3277 arp_util.debug( '-------- parameters for delete_f_ct_id() ------');
3278 arp_util.debug( 'p_customer_trx_id = ' ||
3279 p_customer_trx_id);
3280 END IF;
3281
3282 RAISE;
3283
3284 END;
3285
3286
3287 /*===========================================================================+
3288 | PROCEDURE |
3289 | delete_f_ltctl_id |
3290 | |
3291 | DESCRIPTION |
3292 | This procedure deletes the child ra_customer_trx_lines rows identified |
3293 | by the p_link_to_cust_trx_line_id parameter. |
3294 | |
3295 | SCOPE - PUBLIC |
3296 | |
3297 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3298 | arp_util.debug |
3299 | |
3300 | ARGUMENTS : IN: |
3301 | p_link_to_cust_trx_line_id - identifies the rows to delete |
3302 | OUT: |
3303 | None |
3304 | |
3305 | RETURNS : NONE |
3306 | |
3307 | NOTES |
3308 | |
3309 | MODIFICATION HISTORY |
3310 | 25-JUN-95 Charlie Tomberg Created |
3311 | |
3312 +===========================================================================*/
3313
3314 procedure delete_f_ltctl_id( p_link_to_cust_trx_line_id IN
3315 ra_customer_trx_lines.link_to_cust_trx_line_id%type)
3316 IS
3317
3318 BEGIN
3319
3320
3321 IF PG_DEBUG in ('Y', 'C') THEN
3322 arp_util.debug( 'arp_ctl_pkg.delete_f_ltctl_id()+');
3323 END IF;
3324
3325 DELETE FROM ra_customer_trx_lines
3326 WHERE link_to_cust_trx_line_id = p_link_to_cust_trx_line_id;
3327
3328 IF PG_DEBUG in ('Y', 'C') THEN
3329 arp_util.debug( 'arp_ctl_pkg.delete_f_ltctl_id()-');
3330 END IF;
3331
3332 EXCEPTION
3333 WHEN OTHERS THEN
3334 IF PG_DEBUG in ('Y', 'C') THEN
3335 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.delete_f_ltctl_id()');
3336 arp_util.debug( '');
3337 arp_util.debug( '-------- parameters for delete_f_ltctl_id() ------');
3338 arp_util.debug( 'p_link_to_cust_trx_line_id = ' ||
3339 p_link_to_cust_trx_line_id);
3340 END IF;
3341
3342 RAISE;
3343
3344 END;
3345
3346 /*===========================================================================+
3347 | PROCEDURE |
3348 | delete_f_ct_ltctl_id_type |
3349 | |
3350 | DESCRIPTION |
3351 | This procedure deletes the child ra_customer_trx_lines rows identified |
3352 | by the p_customer_trx_id, p_link_to_cust_trx_line_id and p_line_type |
3353 | |
3354 | SCOPE - PUBLIC |
3355 | |
3356 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3357 | arp_util.debug |
3358 | |
3359 | ARGUMENTS : IN: |
3360 | p_customer_trx_id - identifies the transaction |
3361 | p_link_to_cust_trx_line_id - identifies the parent line |
3362 | p_line_type - identifies the parent line |
3363 | type |
3364 | OUT: |
3365 | None |
3366 | |
3367 | RETURNS : NONE |
3368 | |
3369 | |
3370 | NOTES |
3371 | |
3372 | MODIFICATION HISTORY |
3373 | 14-SEP-95 Subash Chadalavada Created |
3374 | |
3375 +===========================================================================*/
3376
3377 procedure delete_f_ct_ltctl_id_type(
3378 p_customer_trx_id IN
3379 ra_customer_trx.customer_trx_id%type,
3380 p_link_to_cust_trx_line_id IN
3381 ra_customer_trx_lines.link_to_cust_trx_line_id%type,
3382 p_line_type IN
3383 ra_customer_trx_lines.line_type%type DEFAULT NULL)
3384 IS
3385
3386 BEGIN
3387
3388 IF PG_DEBUG in ('Y', 'C') THEN
3389 arp_util.debug( 'arp_ctl_pkg.delete_f_ct_ltctl_id_type()+');
3390 END IF;
3391
3392 DELETE FROM ra_customer_trx_lines
3393 WHERE customer_trx_id = p_customer_trx_id
3394 AND decode(p_link_to_cust_trx_line_id,
3395 null, -99,
3396 customer_trx_line_id) = nvl(p_link_to_cust_trx_line_id, -99)
3397 AND line_type = nvl(p_line_type, line_type);
3398
3399 IF PG_DEBUG in ('Y', 'C') THEN
3400 arp_util.debug( 'arp_ctl_pkg.delete_f_ct_ltctl_id_type()-');
3401 END IF;
3402
3403 EXCEPTION
3404 WHEN OTHERS THEN
3405 IF PG_DEBUG in ('Y', 'C') THEN
3406 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.delete_f_ct_ltctl_id_type()');
3407 arp_util.debug( '');
3408 arp_util.debug( '---- parameters for delete_f_ct_ltctl_id_type() -----');
3409 arp_util.debug( 'p_customer_trx_id = ' || p_customer_trx_id);
3410 arp_util.debug( 'p_link_to_cust_trx_line_id = ' ||
3411 p_link_to_cust_trx_line_id);
3412 arp_util.debug( 'p_line_type = ' || p_line_type);
3413 END IF;
3414
3415 RAISE;
3416 END;
3417
3418
3419 /*===========================================================================+
3420 | PROCEDURE |
3421 | update_p |
3422 | |
3423 | DESCRIPTION |
3424 | This procedure updates the ra_customer_trx_lines row identified by the |
3425 | p_customer_trx_line_id parameter. |
3426 | |
3427 | SCOPE - PUBLIC |
3428 | |
3429 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3430 | arp_util.debug |
3431 | |
3432 | ARGUMENTS : IN: |
3433 | p_customer_trx_line_id - identifies the row to update |
3434 | p_line_rec - contains the new column values |
3435 | p_currency_code - transaction's currency code |
3436 | OUT: |
3437 | None |
3438 | |
3439 | RETURNS : NONE |
3440 | |
3441 | NOTES |
3442 | set_to_dummy must be called before the values in p_line_rec are |
3443 | changed and this function is called. |
3444 | |
3445 | MODIFICATION HISTORY |
3446 | 06-JUN-95 Charlie Tomberg Created |
3447 | |
3448 +===========================================================================*/
3449
3450 PROCEDURE update_p( p_line_rec IN ra_customer_trx_lines%rowtype,
3451 p_customer_trx_line_id IN
3452 ra_customer_trx_lines.customer_trx_line_id%type,
3453 p_currency_code IN fnd_currencies.currency_code%type
3454 DEFAULT NULL ) IS
3455
3456 l_currency_code fnd_currencies.currency_code%type;
3457
3458 BEGIN
3459
3460 IF PG_DEBUG in ('Y', 'C') THEN
3461 arp_util.debug( 'arp_ctl_pkg.update_p()+ ' ||
3462 to_char(sysdate, 'HH:MI:SS'));
3463 END IF;
3464
3465 /*---------------------------------------------------------------+
3466 | Get the transaction's currency code if it was not passed in |
3467 +---------------------------------------------------------------*/
3468
3469 IF (p_currency_code IS NULL)
3470 THEN
3471 SELECT ct.invoice_currency_code
3472 INTO l_currency_code
3473 FROM ra_customer_trx ct,
3474 ra_customer_trx_lines ctl
3475 WHERE ct.customer_trx_id = ctl.customer_trx_id
3476 AND ctl.customer_trx_line_id = p_customer_trx_line_id;
3477 ELSE l_currency_code := p_currency_code;
3478 END IF;
3479
3480 IF PG_DEBUG in ('Y', 'C') THEN
3481 arp_util.debug( 'using currency code: ' || l_currency_code );
3482 END IF;
3483
3484 arp_ctl_pkg.generic_update( pg_cursor1,
3485 ' WHERE customer_trx_line_id = :where_1 ' ||
3486 ' AND :where_line_type is null',
3487 p_customer_trx_line_id,
3488 null,
3489 l_currency_code,
3490 p_line_rec);
3491
3492 IF PG_DEBUG in ('Y', 'C') THEN
3493 arp_util.debug( 'arp_ctl_pkg.update_p()- ' ||
3494 to_char(sysdate, 'HH:MI:SS'));
3495 END IF;
3496
3497
3498 EXCEPTION
3499 WHEN OTHERS THEN
3500 IF PG_DEBUG in ('Y', 'C') THEN
3501 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.update_p()');
3502 arp_util.debug( '');
3503 arp_util.debug( '-------- parameters for update_p() ------');
3504 arp_util.debug( 'p_customer_trx_line_id = ' ||
3505 p_customer_trx_line_id);
3506 arp_util.debug( 'p_currency_code = ' || p_currency_code);
3507 END IF;
3508 display_line_rec(p_line_rec);
3509
3510 RAISE;
3511 END;
3512
3513 /*===========================================================================+
3514 | PROCEDURE |
3515 | update_f_ct_id |
3516 | |
3517 | DESCRIPTION |
3518 | This procedure updates the ra_customer_trx_lines rows identified by the|
3519 | p_customer_trx_id parameter. |
3520 | |
3521 | SCOPE - PUBLIC |
3522 | |
3523 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3524 | arp_util.debug |
3525 | |
3526 | ARGUMENTS : IN: |
3527 | p_customer_trx_id - identifies the rows to update |
3528 | p_line_rec - contains the new column values |
3529 | p_line_type - value is used to restrict update |
3530 | p_currency_code - transaction's currency code |
3531 | OUT: |
3532 | None |
3533 | |
3534 | RETURNS : NONE |
3535 | |
3536 | NOTES |
3537 | set_to_dummy must be called before the values in p_line_rec are |
3538 | changed and this function is called. |
3539 | |
3540 | MODIFICATION HISTORY |
3541 | 06-JUN-95 Charlie Tomberg Created |
3542 | |
3543 +===========================================================================*/
3544
3545 PROCEDURE update_f_ct_id( p_line_rec IN ra_customer_trx_lines%rowtype,
3546 p_customer_trx_id IN
3547 ra_customer_trx_lines.customer_trx_id%type,
3548 p_line_type IN
3549 ra_customer_trx_lines.line_type%type default null,
3550 p_currency_code IN fnd_currencies.currency_code%type
3551 DEFAULT NULL) IS
3552
3553 l_where varchar2(500);
3554 l_currency_code fnd_currencies.currency_code%type;
3555
3556 BEGIN
3557
3558 IF PG_DEBUG in ('Y', 'C') THEN
3559 arp_util.debug( 'arp_ctl_pkg.update_f_ct_id()+ ' ||
3560 to_char(sysdate, 'HH:MI:SS'));
3561 END IF;
3562
3563
3564
3565 /*---------------------------------------------------------------+
3566 | Get the transaction's currency code if it was not passed in |
3567 +---------------------------------------------------------------*/
3568
3569 IF (p_currency_code IS NULL)
3570 THEN
3571 SELECT ct.invoice_currency_code
3572 INTO l_currency_code
3573 FROM ra_customer_trx ct
3574 WHERE ct.customer_trx_id = p_customer_trx_id;
3575 ELSE l_currency_code := p_currency_code;
3576 END IF;
3577
3578 IF PG_DEBUG in ('Y', 'C') THEN
3579 arp_util.debug( 'using currency code: ' || l_currency_code );
3580 END IF;
3581
3582
3583 l_where := ' WHERE customer_trx_id = :where_1 ' ||
3584 'AND line_type = nvl(:where_line_type, line_type)';
3585
3586 arp_ctl_pkg.generic_update( pg_cursor2,
3587 l_where,
3588 p_customer_trx_id,
3589 p_line_type,
3590 l_currency_code,
3591 p_line_rec);
3592
3593 IF PG_DEBUG in ('Y', 'C') THEN
3594 arp_util.debug( 'arp_ctl_pkg.update_f_ct_id()- ' ||
3595 to_char(sysdate, 'HH:MI:SS'));
3596 END IF;
3597
3598
3599 EXCEPTION
3600 WHEN OTHERS THEN
3601 IF PG_DEBUG in ('Y', 'C') THEN
3602 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.update_f_ct_id()');
3603 arp_util.debug( '');
3604 arp_util.debug( '-------- parameters for update_f_ct_id() ------');
3605 arp_util.debug( 'p_customer_trx_id = ' ||
3606 p_customer_trx_id);
3607 arp_util.debug( 'p_line_type = ' || p_line_type);
3608 arp_util.debug( 'p_currency_code = ' || p_currency_code);
3609 END IF;
3610 display_line_rec(p_line_rec);
3611
3612 RAISE;
3613 END;
3614
3615
3616 /*===========================================================================+
3617 | PROCEDURE |
3618 | update_amount_f_ctl_id |
3619 | |
3620 | DESCRIPTION |
3621 | This procedure updates the amounts in a record in ra_customer_trx_lines|
3622 | The columns affected are: extended_amount, unit_selling_price, |
3623 | gross_extended_amount, and gross_unit_selling_price. |
3624 | These are adjustments made for inclusive tax amounts. |
3625 | This function is used when the amounts are gross of inclusive tax. |
3626 | Regular invoice lines should use this. Applied credit memo lines |
3627 | should use the function update_cm_amount_f_ctl_id. |
3628 | |
3629 | SCOPE - PUBLIC |
3630 | |
3631 | ARGUMENTS : IN : p_customer_trx_line_id |
3632 | p_inclusive_amt --- Inclusive tax amount |
3633 | OUT: p_new_extended_amt --- New net price |
3634 | p_new_unit_selling_price --- New unit selling price |
3635 | |
3636 | NOTES |
3637 | |
3638 | HISTORY |
3639 | 18-Aug-97 Kenichi Mizuta Created. |
3640 | 14-FEB-03 M Raymond Bug 2772387 - preventing ORA-1476 errors
3641 | when quantity invoiced is zero and
3642 | tax compounding is in use.
3643 | 20-FEB-2003 NIPATEL Bug 2772387 - Per PM inputs, will not
3644 | adjutst Unit Selling Price when quantity is
3645 | zero in update_amount_f_ctl_id
3646 | |
3647 +===========================================================================*/
3648 PROCEDURE update_amount_f_ctl_id(
3649 p_customer_trx_line_id IN Number,
3650 p_inclusive_amt IN Number,
3651 p_new_extended_amt OUT NOCOPY Number,
3652 p_new_unit_selling_price OUT NOCOPY Number,
3653 p_precision IN Number,
3654 p_min_acct_unit IN Number) IS
3655 cursor c is select
3656 quantity_invoiced,
3657 quantity_credited,
3658 extended_amount,
3659 unit_selling_price,
3660 gross_extended_amount,
3661 gross_unit_selling_price,
3662 revenue_amount
3663 from
3664 ra_customer_trx_lines
3665 where
3666 customer_trx_line_id = p_customer_trx_line_id for update;
3667 crow c%rowtype;
3668 l_extended_amount ra_customer_trx_lines.extended_amount%type;
3669 l_unit_selling_price ra_customer_trx_lines.unit_selling_price%type;
3670 l_gross_extended_amount ra_customer_trx_lines.gross_extended_amount%type;
3671 l_gross_unit_selling_price ra_customer_trx_lines.gross_unit_selling_price%type;
3672 l_revenue_amount ra_customer_trx_lines.revenue_amount%type;
3673 l_old_inclusive_amt ra_customer_trx_lines.extended_amount%type;
3674 l_qty ra_customer_trx_lines.quantity_invoiced%type;
3675 begin
3676 IF PG_DEBUG in ('Y', 'C') THEN
3677 arp_util.debug( 'arp_ctl_pkg.update_amount_f_ctl_id('
3678 || to_char(p_customer_trx_line_id) || ','
3679 || to_char(p_inclusive_amt)
3680 ||')+');
3681 END IF;
3682
3683 begin
3684 open c;
3685 fetch c into crow;
3686
3687 -- On insert old inclusive should be 0, since gross amount should be null.
3688 l_old_inclusive_amt := nvl(crow.gross_extended_amount, crow.extended_amount) - crow.extended_amount;
3689 l_extended_amount := nvl(crow.gross_extended_amount, crow.extended_amount) - p_inclusive_amt;
3690
3691 /* Bugfix 2772387: do not adjust the unit selling proce when quantity is zero */
3692
3693 l_qty := nvl(crow.quantity_invoiced, crow.quantity_credited);
3694
3695 if (l_qty = 0 OR l_qty is NULL)then
3696
3697 l_unit_selling_price := crow.unit_selling_price;
3698
3699 elsif (p_min_acct_unit is NULL) then
3700 l_unit_selling_price :=
3701 round(l_extended_amount/
3702 l_qty,
3703 p_precision);
3704 else
3705 l_unit_selling_price :=
3706 round(l_extended_amount/
3707 l_qty
3708 /p_min_acct_unit/100)
3709 *100*p_min_acct_unit;
3710 end if;
3711
3712
3713 l_gross_extended_amount :=
3714 nvl(crow.gross_extended_amount, crow.extended_amount);
3715 l_gross_unit_selling_price :=
3716 nvl(crow.gross_unit_selling_price, crow.unit_selling_price);
3717 l_revenue_amount := crow.revenue_amount +
3718 l_old_inclusive_amt - p_inclusive_amt;
3719
3720 /* 5487466 - if inclusive tax to be removed, clear
3721 gross_ columns */
3722 IF (p_inclusive_amt = 0)
3723 THEN
3724 l_gross_extended_amount := NULL;
3725 l_gross_unit_selling_price := NULL;
3726 END IF;
3727
3728 update ra_customer_trx_lines
3729 set
3730 extended_amount = l_extended_amount,
3731 unit_selling_price = l_unit_selling_price,
3732 gross_extended_amount = l_gross_extended_amount,
3733 gross_unit_selling_price = l_gross_unit_selling_price,
3734 revenue_amount = l_revenue_amount
3735 where current of c;
3736
3737
3738 close c;
3739 end;
3740
3741 p_new_extended_amt := l_extended_amount;
3742 p_new_unit_selling_price := l_unit_selling_price;
3743
3744 IF PG_DEBUG in ('Y', 'C') THEN
3745 arp_util.debug( 'arp_ctl_pkg.update_amount_f_ctl_id('
3746 || to_char(l_extended_amount) || ','
3747 || to_char(l_unit_selling_price)
3748 ||')-');
3749 END IF;
3750 exception
3751 when others then
3752 if c%isopen then
3753 close c;
3754 end if;
3755
3756 IF PG_DEBUG in ('Y', 'C') THEN
3757 arp_util.debug( 'arp_ctl_pkg.update_amount_f_ctl_id(EXCEPTION)-');
3758 END IF;
3759 raise;
3760 end update_amount_f_ctl_id;
3761
3762 /*===========================================================================+
3763 | PROCEDURE |
3764 | update_cm_amount_f_ctl_id |
3765 | |
3766 | DESCRIPTION |
3767 | This procedure updates the amounts in a record in ra_customer_trx_lines|
3768 | The columns affected are: extended_amount, unit_selling_price, |
3769 | gross_extended_amount, and gross_unit_selling_price. |
3770 | These are adjustments made for inclusive tax amounts. |
3771 | This function is used when the amounts are net of inclusive tax. |
3772 | Applied credit memo lines - use this function. |
3773 | |
3774 | SCOPE - PUBLIC |
3775 | |
3776 | ARGUMENTS : IN : p_customer_trx_line_id |
3777 | p_inclusive_amt --- Inclusive tax amount |
3778 | OUT: p_new_gross_extended_amt --- New gross price |
3779 | p_new_gross_unit_selling_price --- New gross selling pric|
3780 | |
3781 | NOTES |
3782 | |
3783 | HISTORY |
3784 | 18-Aug-97 Kenichi Mizuta Created. |
3785 | |
3786 +===========================================================================*/
3787
3788 PROCEDURE update_cm_amount_f_ctl_id(
3789 p_customer_trx_line_id IN Number,
3790 p_inclusive_amount IN Number,
3791 p_new_gross_extended_amount OUT NOCOPY Number,
3792 p_new_gross_unit_selling_price OUT NOCOPY Number,
3793 p_precision IN Number,
3794 p_min_acct_unit IN Number) IS
3795 cursor c is select
3796 cm.quantity_credited quantity_credited,
3797 cm.quantity_invoiced quantity_invoiced,
3798 cm.extended_amount extended_amount,
3799 cm.unit_selling_price unit_selling_price,
3800 cm.gross_extended_amount gross_extended_amount,
3801 cm.gross_unit_selling_price gross_unit_selling_price,
3802 cm.previous_customer_trx_line_id previous_customer_trx_line_id
3803 from
3804 ra_customer_trx_lines cm
3805 where
3806 customer_trx_line_id = p_customer_trx_line_id for update;
3807 cursor cinv(p_line_id IN number) is select
3808 inv.gross_unit_selling_price gross_unit_selling_price
3809 from
3810 ra_customer_trx_lines inv
3811 where
3812 customer_trx_line_id = p_line_id;
3813
3814 crow c%rowtype;
3815 l_extended_amount ra_customer_trx_lines.extended_amount%type;
3816 l_unit_selling_price ra_customer_trx_lines.unit_selling_price%type;
3817 l_gross_extended_amount ra_customer_trx_lines.gross_extended_amount%type;
3818 l_gross_unit_selling_price ra_customer_trx_lines.gross_unit_selling_price%type;
3819 l_revenue_amount ra_customer_trx_lines.revenue_amount%type;
3820 l_old_inclusive_amt ra_customer_trx_lines.extended_amount%type;
3821 begin
3822 IF PG_DEBUG in ('Y', 'C') THEN
3823 arp_util.debug( 'arp_ctl_pkg.update_cm_amount_f_ctl_id('
3824 || to_char(p_customer_trx_line_id) || ','
3825 || to_char(p_inclusive_amount)
3826 ||')+');
3827 END IF;
3828
3829 begin
3830 open c;
3831 fetch c into crow;
3832
3833 l_extended_amount := crow.extended_amount;
3834 if ( p_inclusive_amount is null ) OR
3835 ( p_inclusive_amount = 0 )
3836 then
3837 IF PG_DEBUG in ('Y', 'C') THEN
3838 arp_util.debug( 'arp_ctl_pkg.update_cm_amount_f_ctl_id: '||
3839 'No Inclusive Tax Amounts');
3840 END IF;
3841
3842 l_gross_extended_amount := null;
3843 l_gross_unit_selling_price := null;
3844 else
3845 IF PG_DEBUG in ('Y', 'C') THEN
3846 arp_util.debug( 'arp_ctl_pkg.update_cm_amount_f_ctl_id: '||
3847 'Inclusive Amount = '||to_char(p_inclusive_amount));
3848 arp_util.debug( 'arp_ctl_pkg.update_cm_amount_f_ctl_id: '||
3849 'Quantity = '||nvl(to_char(crow.quantity_credited), 'NULL'));
3850 arp_util.debug( 'arp_ctl_pkg.update_cm_amount_f_ctl_id: '||
3851 'Precision = '||nvl(to_char(p_precision), 'NULL'));
3852 arp_util.debug( 'arp_ctl_pkg.update_cm_amount_f_ctl_id: '||
3853 'MAU = '||nvl(to_char(p_min_acct_unit), 'NULL'));
3854 END IF;
3855
3856 l_gross_extended_amount := crow.extended_amount + p_inclusive_amount;
3857 if ( crow.quantity_credited = 0 ) then
3858 begin
3859 open cinv(crow.previous_customer_trx_line_id);
3860 fetch cinv into l_gross_unit_selling_price;
3861 close cinv;
3862 exception
3863 when others then
3864 IF PG_DEBUG in ('Y', 'C') THEN
3865 arp_util.debug( 'EXCPETION: Unable to fetch original invoice');
3866 END IF;
3867 raise;
3868 end;
3869 elsif ( crow.unit_selling_price is null ) then
3870 IF PG_DEBUG in ('Y', 'C') THEN
3871 arp_util.debug( 'arp_ctl_pkg.update_cm_amount_f_ctl_id: NO unit selling price');
3872 END IF;
3873 l_gross_unit_selling_price := null;
3874 elsif (p_min_acct_unit is null) then
3875 IF PG_DEBUG in ('Y', 'C') THEN
3876 arp_util.debug( 'arp_ctl_pkg.update_cm_amount_f_ctl_id: Rounding to precision');
3877 END IF;
3878 l_gross_unit_selling_price :=
3879 round(l_gross_extended_amount / crow.quantity_credited, p_precision);
3880 else
3881 IF PG_DEBUG in ('Y', 'C') THEN
3882 arp_util.debug( 'arp_ctl_pkg.update_cm_amount_f_ctl_id: Rounding to MAU');
3883 END IF;
3884 l_gross_unit_selling_price :=
3885 round(l_gross_extended_amount / crow.quantity_credited / p_min_acct_unit / 100)
3886 * 100 * p_min_acct_unit;
3887 end if;
3888 end if;
3889
3890 update ra_customer_trx_lines
3891 set
3892 gross_extended_amount = l_gross_extended_amount,
3893 gross_unit_selling_price = l_gross_unit_selling_price
3894 where current of c;
3895
3896 close c;
3897 end;
3898
3899 p_new_gross_extended_amount := l_gross_extended_amount;
3900 p_new_gross_unit_selling_price := l_gross_unit_selling_price;
3901
3902 IF PG_DEBUG in ('Y', 'C') THEN
3903 arp_util.debug( 'arp_ctl_pkg.update_cm_amount_f_ctl_id('
3904 || to_char(l_gross_extended_amount) || ','
3905 || to_char(l_gross_unit_selling_price)
3906 ||')-');
3907 END IF;
3908
3909 exception
3910 when others then
3911 if c%isopen then
3912 close c;
3913 end if;
3914
3915 IF PG_DEBUG in ('Y', 'C') THEN
3916 arp_util.debug( 'arp_ctl_pkg.update_cm_amount_f_ctl_id(EXCEPTION)-');
3917 END IF;
3918 raise;
3919 end update_cm_amount_f_ctl_id;
3920
3921
3922
3923 /*===========================================================================+
3924 | PROCEDURE |
3925 | insert_p |
3926 | |
3927 | DESCRIPTION |
3928 | This procedure inserts a row into ra_customer_trx_lines that contains |
3929 | the column values specified in the p_trx_rec parameter. |
3930 | |
3931 | SCOPE - PUBLIC |
3932 | |
3933 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
3934 | arp_util.debug |
3935 | arp_global.set_of_books_id |
3936 | |
3937 | ARGUMENTS : IN: |
3938 | p_line_rec - contains the new column values |
3939 | OUT: |
3940 | p_customer_trx_line_id - unique ID of the new row |
3941 | |
3942 | RETURNS : NONE |
3943 | |
3944 | NOTES |
3945 | |
3946 | MODIFICATION HISTORY |
3947 | 06-JUN-95 Charlie Tomberg Created |
3948 | |
3949 | Rel. 11 Changes: |
3950 | ---------------- |
3951 | 07-22-97 OSTEINME added code to handle three new |
3952 | database columns: |
3953 | - gross_unit_selling_price |
3954 | - gross_extended_amount |
3955 | - amount_includes_tax_flag |
3956 | |
3957 | 08-20-97 KTANG handle global_attribute_category and |
3958 | global_attribute[1-20] for global |
3959 | descriptive flexfield |
3960 | |
3961 | |
3962 | 10-JAN-99 Saloni Shah added warehouse_id for global tax |
3963 | engine change |
3964 | 17-MAR-99 Debbie Jancis added translated description for |
3965 | MLS changes |
3966 | |
3967 | 20-MAR-2000 J Rautiainen Added BR project related columns |
3968 | EXTENDED_ACCTD_AMOUNT,BR_REF_CUSTOMER_TRX_ID,|
3969 | BR_REF_PAYMENT_SCHEDULE_ID, BR_ADJUSTMENT_ID |
3970 | into table handlers |
3971 | |
3972 | 31-OCT-2000 Y Rakotonirainy Bug 1243304 : Added column wh_update_date |
3973 | into the table handlers. |
3974 | 07-Apr-2005 Debbie Sue Jancis ETAX: Added SHIP_TO Id columns to support |
3975 | ship to at the line level. Also added |
3976 | tax_Code |
3977 | 23-Dec-2005 Gyanajyothi Added Rule End date for Daily Rate Rule types
3978 | commented the changes for Bug 4410461
3979 +===========================================================================*/
3980
3981 PROCEDURE insert_p(
3982 p_line_rec IN ra_customer_trx_lines%rowtype,
3983 p_customer_trx_line_id OUT NOCOPY
3984 ra_customer_trx_lines.customer_trx_line_id%type
3985 ) IS
3986
3987
3988 l_customer_trx_line_id ra_customer_trx_lines.customer_trx_line_id%type;
3989 l_revenue_amount ra_customer_trx_lines.revenue_amount%TYPE;
3990
3991 BEGIN
3992
3993 IF PG_DEBUG in ('Y', 'C') THEN
3994 arp_util.debug( 'arp_ctl_pkg.insert_p()+');
3995 END IF;
3996
3997 p_customer_trx_line_id := '';
3998
3999 /*---------------------------*
4000 | Get the unique identifier |
4001 *---------------------------*/
4002 /* Bug 4410461 FP for 4340099: Added the If Condition below */
4003 IF p_line_rec.customer_trx_line_id is null THEN
4004 SELECT RA_CUSTOMER_TRX_LINES_S.NEXTVAL
4005 INTO l_customer_trx_line_id
4006 FROM DUAL;
4007 ELSE l_customer_trx_line_id := p_line_rec.customer_trx_line_id ;
4008 END IF ;
4009
4010
4011 /*-------------------*
4012 | Insert the record |
4013 *-------------------*/
4014
4015
4016 l_revenue_amount := p_line_rec.revenue_amount;
4017 IF p_line_rec.revenue_amount is NULL
4018 THEN
4019 IF p_line_rec.line_type not in ( 'CHARGES', 'TAX' )
4020 THEN
4021 l_revenue_amount := p_line_rec.extended_amount;
4022 END IF;
4023 END IF;
4024
4025
4026 INSERT INTO ra_customer_trx_lines
4027 (
4028 customer_trx_line_id,
4029 customer_trx_id,
4030 line_number,
4031 line_type,
4032 quantity_credited,
4033 quantity_invoiced,
4034 quantity_ordered,
4035 unit_selling_price,
4036 unit_standard_price,
4037 revenue_amount,
4038 extended_amount,
4039 memo_line_id,
4040 inventory_item_id,
4041 item_exception_rate_id,
4042 description,
4043 item_context,
4044 initial_customer_trx_line_id,
4045 link_to_cust_trx_line_id,
4046 previous_customer_trx_id,
4047 previous_customer_trx_line_id,
4048 accounting_rule_duration,
4049 accounting_rule_id,
4050 rule_start_date,
4051 autorule_complete_flag,
4052 autorule_duration_processed,
4053 reason_code,
4054 last_period_to_credit,
4055 sales_order,
4056 sales_order_date,
4057 sales_order_line,
4058 sales_order_revision,
4059 sales_order_source,
4060 vat_tax_id,
4061 tax_exempt_flag,
4062 sales_tax_id,
4063 location_segment_id,
4064 tax_exempt_number,
4065 tax_exempt_reason_code,
4066 tax_vendor_return_code,
4067 taxable_flag,
4068 tax_exemption_id,
4069 tax_precedence,
4070 tax_rate,
4071 uom_code,
4072 autotax,
4073 movement_id,
4074 default_ussgl_transaction_code,
4075 default_ussgl_trx_code_context,
4076 interface_line_context,
4077 interface_line_attribute1,
4078 interface_line_attribute2,
4079 interface_line_attribute3,
4080 interface_line_attribute4,
4081 interface_line_attribute5,
4082 interface_line_attribute6,
4083 interface_line_attribute7,
4084 interface_line_attribute8,
4085 interface_line_attribute9,
4086 interface_line_attribute10,
4087 interface_line_attribute11,
4088 interface_line_attribute12,
4089 interface_line_attribute13,
4090 interface_line_attribute14,
4091 interface_line_attribute15,
4092 attribute_category,
4093 attribute1,
4094 attribute2,
4095 attribute3,
4096 attribute4,
4097 attribute5,
4098 attribute6,
4099 attribute7,
4100 attribute8,
4101 attribute9,
4102 attribute10,
4103 attribute11,
4104 attribute12,
4105 attribute13,
4106 attribute14,
4107 attribute15,
4108 global_attribute_category,
4109 global_attribute1,
4110 global_attribute2,
4111 global_attribute3,
4112 global_attribute4,
4113 global_attribute5,
4114 global_attribute6,
4115 global_attribute7,
4116 global_attribute8,
4117 global_attribute9,
4118 global_attribute10,
4119 global_attribute11,
4120 global_attribute12,
4121 global_attribute13,
4122 global_attribute14,
4123 global_attribute15,
4124 global_attribute16,
4125 global_attribute17,
4126 global_attribute18,
4127 global_attribute19,
4128 global_attribute20,
4129 created_by,
4130 creation_date,
4131 last_updated_by,
4132 last_update_date,
4133 program_application_id,
4134 last_update_login,
4135 program_id,
4136 program_update_date,
4137 set_of_books_id,
4138 gross_unit_selling_price,
4139 gross_extended_amount,
4140 amount_includes_tax_flag,
4141 warehouse_id,
4142 translated_description,
4143 taxable_amount, /* Bug 853757 */
4144 request_id,
4145 extended_acctd_amount,
4146 br_ref_customer_trx_id,
4147 br_ref_payment_schedule_id,
4148 br_adjustment_id,
4149 wh_update_date,
4150 payment_set_id,
4151 org_id,
4152 ship_to_customer_id,
4153 ship_to_site_use_id,
4154 ship_to_contact_id,
4155 tax_classification_code,
4156 historical_flag,
4157 rule_end_date
4158 )
4159 VALUES
4160 (
4161 l_customer_trx_line_id,
4162 p_line_rec.customer_trx_id,
4163 p_line_rec.line_number,
4164 p_line_rec.line_type,
4165 p_line_rec.quantity_credited,
4166 p_line_rec.quantity_invoiced,
4167 p_line_rec.quantity_ordered,
4168 p_line_rec.unit_selling_price,
4169 p_line_rec.unit_standard_price,
4170 l_revenue_amount,
4171 p_line_rec.extended_amount,
4172 p_line_rec.memo_line_id,
4173 p_line_rec.inventory_item_id,
4174 p_line_rec.item_exception_rate_id,
4175 p_line_rec.description,
4176 p_line_rec.item_context,
4177 p_line_rec.initial_customer_trx_line_id,
4178 p_line_rec.link_to_cust_trx_line_id,
4179 p_line_rec.previous_customer_trx_id,
4180 p_line_rec.previous_customer_trx_line_id,
4181 p_line_rec.accounting_rule_duration,
4182 p_line_rec.accounting_rule_id,
4183 p_line_rec.rule_start_date,
4184 p_line_rec.autorule_complete_flag,
4185 p_line_rec.autorule_duration_processed,
4186 p_line_rec.reason_code,
4187 p_line_rec.last_period_to_credit,
4188 p_line_rec.sales_order,
4189 p_line_rec.sales_order_date,
4190 p_line_rec.sales_order_line,
4191 p_line_rec.sales_order_revision,
4192 p_line_rec.sales_order_source,
4193 p_line_rec.vat_tax_id,
4194 p_line_rec.tax_exempt_flag,
4195 p_line_rec.sales_tax_id,
4196 p_line_rec.location_segment_id,
4197 p_line_rec.tax_exempt_number,
4198 p_line_rec.tax_exempt_reason_code,
4199 p_line_rec.tax_vendor_return_code,
4200 p_line_rec.taxable_flag,
4201 p_line_rec.tax_exemption_id,
4202 p_line_rec.tax_precedence,
4203 p_line_rec.tax_rate,
4204 p_line_rec.uom_code,
4205 p_line_rec.autotax,
4206 p_line_rec.movement_id,
4207 p_line_rec.default_ussgl_transaction_code,
4208 p_line_rec.default_ussgl_trx_code_context,
4209 p_line_rec.interface_line_context,
4210 p_line_rec.interface_line_attribute1,
4211 p_line_rec.interface_line_attribute2,
4212 p_line_rec.interface_line_attribute3,
4213 p_line_rec.interface_line_attribute4,
4214 p_line_rec.interface_line_attribute5,
4215 p_line_rec.interface_line_attribute6,
4216 p_line_rec.interface_line_attribute7,
4217 p_line_rec.interface_line_attribute8,
4218 p_line_rec.interface_line_attribute9,
4219 p_line_rec.interface_line_attribute10,
4220 p_line_rec.interface_line_attribute11,
4221 p_line_rec.interface_line_attribute12,
4222 p_line_rec.interface_line_attribute13,
4223 p_line_rec.interface_line_attribute14,
4224 p_line_rec.interface_line_attribute15,
4225 p_line_rec.attribute_category,
4226 p_line_rec.attribute1,
4227 p_line_rec.attribute2,
4228 p_line_rec.attribute3,
4229 p_line_rec.attribute4,
4230 p_line_rec.attribute5,
4231 p_line_rec.attribute6,
4232 p_line_rec.attribute7,
4233 p_line_rec.attribute8,
4234 p_line_rec.attribute9,
4235 p_line_rec.attribute10,
4236 p_line_rec.attribute11,
4237 p_line_rec.attribute12,
4238 p_line_rec.attribute13,
4239 p_line_rec.attribute14,
4240 p_line_rec.attribute15,
4241 p_line_rec.global_attribute_category,
4242 p_line_rec.global_attribute1,
4243 p_line_rec.global_attribute2,
4244 p_line_rec.global_attribute3,
4245 p_line_rec.global_attribute4,
4246 p_line_rec.global_attribute5,
4247 p_line_rec.global_attribute6,
4248 p_line_rec.global_attribute7,
4249 p_line_rec.global_attribute8,
4250 p_line_rec.global_attribute9,
4251 p_line_rec.global_attribute10,
4252 p_line_rec.global_attribute11,
4253 p_line_rec.global_attribute12,
4254 p_line_rec.global_attribute13,
4255 p_line_rec.global_attribute14,
4256 p_line_rec.global_attribute15,
4257 p_line_rec.global_attribute16,
4258 p_line_rec.global_attribute17,
4259 p_line_rec.global_attribute18,
4260 p_line_rec.global_attribute19,
4261 p_line_rec.global_attribute20,
4262 pg_user_id, /* created_by */
4263 sysdate, /* creation_date */
4264 pg_user_id, /* last_updated_by */
4265 sysdate, /* last_update_date */
4266 pg_prog_appl_id, /* program_application_id */
4267 nvl(pg_conc_login_id,
4268 pg_login_id), /* last_update_login */
4269 pg_conc_program_id, /* program_id */
4270 sysdate, /* program_update_date */
4271 arp_global.set_of_books_id, /* set_of_books_id */
4272 p_line_rec.gross_unit_selling_price,
4273 p_line_rec.gross_extended_amount,
4274 p_line_rec.amount_includes_tax_flag,
4275 p_line_rec.warehouse_id,
4276 p_line_rec.translated_description,
4277 p_line_rec.taxable_amount,
4278 p_line_rec.request_id,
4279 p_line_rec.extended_acctd_amount,
4280 p_line_rec.br_ref_customer_trx_id,
4281 p_line_rec.br_ref_payment_schedule_id,
4282 p_line_rec.br_adjustment_id,
4283 p_line_rec.wh_update_date,
4284 p_line_rec.payment_set_id,
4285 arp_standard.sysparm.org_id, /* SSA changes */
4286 p_line_rec.ship_to_customer_id,
4287 p_line_rec.ship_to_site_use_id,
4288 p_line_rec.ship_to_contact_id,
4289 p_line_rec.tax_classification_code,
4290 nvl(p_line_rec.historical_flag, 'N'),
4291 p_line_rec.rule_end_date
4292 );
4293
4294 p_customer_trx_line_id := l_customer_trx_line_id;
4295
4296 IF PG_DEBUG in ('Y', 'C') THEN
4297 arp_util.debug( 'after insert: amount_includes_tax_flag = ' ||
4298 p_line_rec.amount_includes_tax_flag);
4299 arp_util.debug( 'after insert: gross_extended_amount = ' ||
4300 p_line_rec.gross_extended_amount);
4301 arp_util.debug( 'after insert: gross_unit_selling_price = ' ||
4302 p_line_rec.gross_unit_selling_price);
4303 arp_util.debug( 'arp_ctl_pkg.insert_p()-');
4304 END IF;
4305
4306 EXCEPTION
4307 WHEN OTHERS THEN
4308 IF PG_DEBUG in ('Y', 'C') THEN
4309 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.insert_p()');
4310 arp_util.debug( '');
4311 arp_util.debug( '-------- parameters for insert_p() ------');
4312 END IF;
4313 display_line_rec(p_line_rec);
4314
4315 RAISE;
4316 END;
4317
4318 /*===========================================================================+
4319 | PROCEDURE |
4320 | display_line_rec |
4321 | |
4322 | DESCRIPTION |
4323 | Displays the values of all columns except creation_date and |
4324 | last_update_date. |
4325 | |
4326 | SCOPE - PRIVATE |
4327 | |
4328 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
4329 | arp_util.debug |
4330 | |
4331 | ARGUMENTS : IN: |
4332 | p_line_rec |
4333 | OUT: |
4334 | IN/ OUT: |
4335 | |
4336 | RETURNS : NONE |
4337 | |
4338 | NOTES |
4339 | |
4340 | MODIFICATION HISTORY |
4341 | 19-JUL-95 Subash C Created |
4342 | |
4343 +===========================================================================*/
4344 PROCEDURE display_line_rec(
4345 p_line_rec IN ra_customer_trx_lines%rowtype) IS
4346 BEGIN
4347 IF PG_DEBUG in ('Y', 'C') THEN
4348 arp_util.debug( 'arp_ctl_pkg.display_line_rec()+');
4349 END IF;
4350
4351 arp_ctl_private_pkg.display_line_rec(p_line_rec);
4352
4353 IF PG_DEBUG in ('Y', 'C') THEN
4354 arp_util.debug( 'arp_ctl_pkg.display_line_rec()-');
4355 END IF;
4356 END;
4357
4358
4359 /*===========================================================================+
4360 | PROCEDURE |
4361 | display_line_p |
4362 | |
4363 | DESCRIPTION |
4364 | Displays the values of all columns except creation_date and |
4365 | last_update_date. |
4366 | |
4367 | SCOPE - PUBLIC |
4368 | |
4369 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
4370 | arp_util.debug |
4371 | |
4372 | ARGUMENTS : IN: |
4373 | p_customer_trx_line_id |
4374 | OUT: |
4375 | IN/ OUT: |
4376 | |
4377 | RETURNS : NONE |
4378 | |
4379 | NOTES |
4380 | |
4381 | MODIFICATION HISTORY |
4382 | 19-JUL-95 Subash C Created |
4383 | |
4384 +===========================================================================*/
4385 PROCEDURE display_line_p(
4386 p_customer_trx_line_id IN
4387 ra_customer_trx_lines.customer_trx_line_id%type) IS
4388 BEGIN
4389 IF PG_DEBUG in ('Y', 'C') THEN
4390 arp_util.debug( 'arp_ctl_pkg.display_line_p()+');
4391 END IF;
4392
4393 arp_ctl_private_pkg.display_line_p(p_customer_trx_line_id);
4394
4395 IF PG_DEBUG in ('Y', 'C') THEN
4396 arp_util.debug( 'arp_ctl_pkg.display_line_p()-');
4397 END IF;
4398
4399 END;
4400
4401 /*===========================================================================+
4402 | PROCEDURE |
4403 | display_line_f_lctl_id |
4404 | |
4405 | DESCRIPTION |
4406 | Displays the values of all columns except creation_date and |
4407 | last_update_date. |
4408 | |
4409 | SCOPE - PUBLIC |
4410 | |
4411 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
4412 | arp_util.debug |
4413 | |
4414 | ARGUMENTS : IN: |
4415 | p_link_to_cust_trx_line_id |
4416 | OUT: |
4417 | None |
4418 | IN/ OUT: |
4419 | None |
4420 | |
4421 | RETURNS : NONE |
4422 | |
4423 | NOTES |
4424 | |
4425 | MODIFICATION HISTORY |
4426 | 03-AUG-95 Charlie Tomberg Created |
4427 | |
4428 +===========================================================================*/
4429
4430
4431 PROCEDURE display_line_f_lctl_id( p_link_to_cust_trx_line_id IN
4432 ra_customer_trx_lines.link_to_cust_trx_line_id%type)
4433 IS
4434 BEGIN
4435
4436 IF PG_DEBUG in ('Y', 'C') THEN
4437 arp_util.debug( 'arp_ctl_pkg.display_line_f_lctl_id()+');
4438 END IF;
4439
4440 arp_ctl_private_pkg.display_line_f_lctl_id( p_link_to_cust_trx_line_id );
4441
4442 IF PG_DEBUG in ('Y', 'C') THEN
4443 arp_util.debug( 'arp_ctl_pkg.display_line_f_lctl_id()-');
4444 END IF;
4445
4446 END;
4447
4448
4449 /*===========================================================================+
4450 | PROCEDURE |
4451 | display_line_f_ct_id |
4452 | |
4453 | DESCRIPTION |
4454 | Displays the values of all columns except creation_date and |
4455 | last_update_date. |
4456 | |
4457 | SCOPE - PUBLIC |
4458 | |
4459 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
4460 | arp_util.debug |
4461 | |
4462 | ARGUMENTS : IN: |
4463 | p_customer_trx_id |
4464 | OUT: |
4465 | None |
4466 | IN/ OUT: |
4467 | None |
4468 | |
4469 | RETURNS : NONE |
4470 | |
4471 | NOTES |
4472 | |
4473 | MODIFICATION HISTORY |
4474 | 08-AUG-95 Charlie Tomberg Created |
4475 | |
4476 +===========================================================================*/
4477
4478
4479 PROCEDURE display_line_f_ct_id( p_customer_trx_id IN
4480 ra_customer_trx.customer_trx_id%type )
4481 IS
4482 BEGIN
4483
4484 IF PG_DEBUG in ('Y', 'C') THEN
4485 arp_util.debug( 'arp_ctl_pkg.display_line_f_ct_id()+');
4486 END IF;
4487
4488 arp_ctl_private_pkg.display_line_f_ct_id( p_customer_trx_id );
4489
4490 IF PG_DEBUG in ('Y', 'C') THEN
4491 arp_util.debug( 'arp_ctl_pkg.display_line_f_ct_id()-');
4492 END IF;
4493
4494 END;
4495
4496 /*===========================================================================+
4497 | PROCEDURE |
4498 | merge_line_recs |
4499 | |
4500 | DESCRIPTION |
4501 | Merges the changed columns in p_new_line_rec into the same columns |
4502 | p_old_line_rec and puts the result into p_out_line_rec. Columns that |
4503 | contain the dummy values are not changed. |
4504 | |
4505 | SCOPE - PRIVATE |
4506 | |
4507 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
4508 | arp_util.debug |
4509 | |
4510 | ARGUMENTS : IN: |
4511 | p_old_line_rec |
4512 | p_new_line_rec |
4513 | OUT: |
4514 | None |
4515 | IN/ OUT: |
4516 | p_out_line_rec |
4517 | |
4518 | RETURNS : NONE |
4519 | |
4520 | NOTES |
4521 | |
4522 | MODIFICATION HISTORY |
4523 | 17-AUG-95 Charlie Tomberg Created |
4524 | |
4525 | Rel. 11 Changes: |
4526 | ---------------- |
4527 | 07-22-97 OSTEINME added code to handle three new |
4528 | database columns: |
4529 | - gross_unit_selling_price |
4530 | - gross_extended_amount |
4531 | - amount_includes_tax_flag |
4532 | |
4533 | 08-20-97 KTANG handle global_attribute_category and |
4534 | global_attribute[1-20] for global |
4535 | descriptive flexfield |
4536 | |
4537 | 10-JAN-99 Saloni Shah added code for warehouse_id |
4538 | for global tax engine change |
4539 | |
4540 | 22-MAR-99 Debbie Jancis added translated_description for MLS |
4541 | |
4542 | 20-MAR-2000 J Rautiainen Added BR project related columns |
4543 | EXTENDED_ACCTD_AMOUNT, |
4544 | BR_REF_CUSTOMER_TRX_ID, |
4545 | BR_REF_PAYMENT_SCHEDULE_ID and |
4546 | BR_ADJUSTMENT_ID |
4547 | into table handlers |
4548 | |
4549 | 31-OCT-2000 Y Rakotonirainy Bug 1243304 : Added column |
4550 | wh_update_date into the table |
4551 | handlers. |
4552 | 20-APR-2005 Debbie Jancis ETax: added ship to id columns to |
4553 | support ship to at the line level |
4554 +===========================================================================*/
4555
4556 PROCEDURE merge_line_recs(
4557 p_old_line_rec IN ra_customer_trx_lines%rowtype,
4558 p_new_line_rec IN
4559 ra_customer_trx_lines%rowtype,
4560 p_out_line_rec IN OUT NOCOPY
4561 ra_customer_trx_lines%rowtype)
4562 IS
4563
4564 BEGIN
4565
4566 IF PG_DEBUG in ('Y', 'C') THEN
4567 arp_util.debug( 'arp_ctl_pkg.merge_line_recs()+');
4568 END IF;
4569
4570 IF (p_new_line_rec.customer_trx_line_id = AR_NUMBER_DUMMY)
4571 THEN p_out_line_rec.customer_trx_line_id :=
4572 p_old_line_rec.customer_trx_line_id;
4573 ELSE p_out_line_rec.customer_trx_line_id :=
4574 p_new_line_rec.customer_trx_line_id;
4575 END IF;
4576
4577 IF (p_new_line_rec.customer_trx_id = AR_NUMBER_DUMMY)
4578 THEN p_out_line_rec.customer_trx_id := p_old_line_rec.customer_trx_id;
4579 ELSE p_out_line_rec.customer_trx_id := p_new_line_rec.customer_trx_id;
4580 END IF;
4581
4582 IF (p_new_line_rec.line_number = AR_NUMBER_DUMMY)
4583 THEN p_out_line_rec.line_number := p_old_line_rec.line_number;
4584 ELSE p_out_line_rec.line_number := p_new_line_rec.line_number;
4585 END IF;
4586
4587 IF (p_new_line_rec.line_type = AR_TEXT_DUMMY)
4588 THEN p_out_line_rec.line_type := p_old_line_rec.line_type;
4589 ELSE p_out_line_rec.line_type := p_new_line_rec.line_type;
4590 END IF;
4591
4592 IF (p_new_line_rec.quantity_credited = AR_NUMBER_DUMMY)
4593 THEN p_out_line_rec.quantity_credited :=
4594 p_old_line_rec.quantity_credited;
4595 ELSE p_out_line_rec.quantity_credited :=
4596 p_new_line_rec.quantity_credited;
4597 END IF;
4598
4599 IF (p_new_line_rec.quantity_invoiced = AR_NUMBER_DUMMY)
4600 THEN p_out_line_rec.quantity_invoiced :=
4601 p_old_line_rec.quantity_invoiced;
4602 ELSE p_out_line_rec.quantity_invoiced :=
4603 p_new_line_rec.quantity_invoiced;
4604 END IF;
4605
4606 IF (p_new_line_rec.quantity_ordered = AR_NUMBER_DUMMY)
4607 THEN p_out_line_rec.quantity_ordered := p_old_line_rec.quantity_ordered;
4608 ELSE p_out_line_rec.quantity_ordered := p_new_line_rec.quantity_ordered;
4609 END IF;
4610
4611 IF (p_new_line_rec.unit_selling_price = AR_NUMBER_DUMMY)
4612 THEN p_out_line_rec.unit_selling_price :=
4613 p_old_line_rec.unit_selling_price;
4614 ELSE p_out_line_rec.unit_selling_price :=
4615 p_new_line_rec.unit_selling_price;
4616 END IF;
4617
4618 IF (p_new_line_rec.unit_standard_price = AR_NUMBER_DUMMY)
4619 THEN p_out_line_rec.unit_standard_price :=
4620 p_old_line_rec.unit_standard_price;
4621 ELSE p_out_line_rec.unit_standard_price :=
4622 p_new_line_rec.unit_standard_price;
4623 END IF;
4624
4625 IF (p_new_line_rec.revenue_amount = AR_NUMBER_DUMMY)
4626 THEN p_out_line_rec.revenue_amount := p_old_line_rec.revenue_amount;
4627 ELSE p_out_line_rec.revenue_amount := p_new_line_rec.revenue_amount;
4628 END IF;
4629
4630 IF (p_new_line_rec.extended_amount = AR_NUMBER_DUMMY)
4631 THEN p_out_line_rec.extended_amount := p_old_line_rec.extended_amount;
4632 ELSE p_out_line_rec.extended_amount := p_new_line_rec.extended_amount;
4633 END IF;
4634
4635 IF (p_new_line_rec.memo_line_id = AR_NUMBER_DUMMY)
4636 THEN p_out_line_rec.memo_line_id := p_old_line_rec.memo_line_id;
4637 ELSE p_out_line_rec.memo_line_id := p_new_line_rec.memo_line_id;
4638 END IF;
4639
4640 IF (p_new_line_rec.inventory_item_id = AR_NUMBER_DUMMY)
4641 THEN p_out_line_rec.inventory_item_id :=
4642 p_old_line_rec.inventory_item_id;
4643 ELSE p_out_line_rec.inventory_item_id :=
4644 p_new_line_rec.inventory_item_id;
4645 END IF;
4646
4647 IF (p_new_line_rec.item_exception_rate_id = AR_NUMBER_DUMMY)
4648 THEN p_out_line_rec.item_exception_rate_id :=
4649 p_old_line_rec.item_exception_rate_id;
4650 ELSE p_out_line_rec.item_exception_rate_id :=
4651 p_new_line_rec.item_exception_rate_id;
4652 END IF;
4653
4654 IF (p_new_line_rec.description = AR_TEXT_DUMMY)
4655 THEN p_out_line_rec.description := p_old_line_rec.description;
4656 ELSE p_out_line_rec.description := p_new_line_rec.description;
4657 END IF;
4658
4659 IF (p_new_line_rec.item_context = AR_TEXT_DUMMY)
4660 THEN p_out_line_rec.item_context := p_old_line_rec.item_context;
4661 ELSE p_out_line_rec.item_context := p_new_line_rec.item_context;
4662 END IF;
4663
4664 IF (p_new_line_rec.initial_customer_trx_line_id = AR_NUMBER_DUMMY)
4665 THEN p_out_line_rec.initial_customer_trx_line_id :=
4666 p_old_line_rec.initial_customer_trx_line_id;
4667 ELSE p_out_line_rec.initial_customer_trx_line_id :=
4668 p_new_line_rec.initial_customer_trx_line_id;
4669 END IF;
4670
4671 IF (p_new_line_rec.link_to_cust_trx_line_id = AR_NUMBER_DUMMY)
4672 THEN p_out_line_rec.link_to_cust_trx_line_id :=
4673 p_old_line_rec.link_to_cust_trx_line_id;
4674 ELSE p_out_line_rec.link_to_cust_trx_line_id :=
4675 p_new_line_rec.link_to_cust_trx_line_id;
4676 END IF;
4677
4678 IF (p_new_line_rec.previous_customer_trx_id = AR_NUMBER_DUMMY)
4679 THEN p_out_line_rec.previous_customer_trx_id :=
4680 p_old_line_rec.previous_customer_trx_id;
4681 ELSE p_out_line_rec.previous_customer_trx_id :=
4682 p_new_line_rec.previous_customer_trx_id;
4683 END IF;
4684
4685 IF (p_new_line_rec.previous_customer_trx_line_id = AR_NUMBER_DUMMY)
4686 THEN p_out_line_rec.previous_customer_trx_line_id :=
4687 p_old_line_rec.previous_customer_trx_line_id;
4688 ELSE p_out_line_rec.previous_customer_trx_line_id :=
4689 p_new_line_rec.previous_customer_trx_line_id;
4690 END IF;
4691
4692 IF (p_new_line_rec.accounting_rule_duration = AR_NUMBER_DUMMY)
4693 THEN p_out_line_rec.accounting_rule_duration :=
4694 p_old_line_rec.accounting_rule_duration;
4695 ELSE p_out_line_rec.accounting_rule_duration :=
4696 p_new_line_rec.accounting_rule_duration;
4697 END IF;
4698
4699 IF (p_new_line_rec.accounting_rule_id = AR_NUMBER_DUMMY)
4700 THEN p_out_line_rec.accounting_rule_id :=
4701 p_old_line_rec.accounting_rule_id;
4702 ELSE p_out_line_rec.accounting_rule_id :=
4703 p_new_line_rec.accounting_rule_id;
4704 END IF;
4705
4706 IF (p_new_line_rec.rule_start_date = AR_DATE_DUMMY)
4707 THEN p_out_line_rec.rule_start_date := p_old_line_rec.rule_start_date;
4708 ELSE p_out_line_rec.rule_start_date := p_new_line_rec.rule_start_date;
4709 END IF;
4710
4711 IF (p_new_line_rec.autorule_complete_flag = AR_FLAG_DUMMY)
4712 THEN p_out_line_rec.autorule_complete_flag :=
4713 p_old_line_rec.autorule_complete_flag;
4714 ELSE p_out_line_rec.autorule_complete_flag :=
4715 p_new_line_rec.autorule_complete_flag;
4716 END IF;
4717
4718 IF (p_new_line_rec.autorule_duration_processed = AR_NUMBER_DUMMY)
4719 THEN p_out_line_rec.autorule_duration_processed :=
4720 p_old_line_rec.autorule_duration_processed;
4721 ELSE p_out_line_rec.autorule_duration_processed :=
4722 p_new_line_rec.autorule_duration_processed;
4723 END IF;
4724
4725 IF (p_new_line_rec.reason_code = AR_TEXT_DUMMY)
4726 THEN p_out_line_rec.reason_code := p_old_line_rec.reason_code;
4727 ELSE p_out_line_rec.reason_code := p_new_line_rec.reason_code;
4728 END IF;
4729
4730 IF (p_new_line_rec.last_period_to_credit = AR_NUMBER_DUMMY)
4731 THEN p_out_line_rec.last_period_to_credit :=
4732 p_old_line_rec.last_period_to_credit;
4733 ELSE p_out_line_rec.last_period_to_credit :=
4734 p_new_line_rec.last_period_to_credit;
4735 END IF;
4736
4737 IF (p_new_line_rec.warehouse_id = AR_NUMBER_DUMMY)
4738 THEN p_out_line_rec.warehouse_id :=
4739 p_old_line_rec.warehouse_id;
4740 ELSE p_out_line_rec.warehouse_id :=
4741 p_new_line_rec.warehouse_id;
4742 END IF;
4743
4744 IF (p_new_line_rec.translated_description= AR_TEXT_DUMMY)
4745 THEN p_out_line_rec.translated_description :=
4746 p_old_line_rec.translated_description;
4747 ELSE p_out_line_rec.translated_description :=
4748 p_new_line_rec.translated_description;
4749 END IF;
4750
4751 IF (p_new_line_rec.sales_order = AR_TEXT_DUMMY)
4752 THEN p_out_line_rec.sales_order := p_old_line_rec.sales_order;
4753 ELSE p_out_line_rec.sales_order := p_new_line_rec.sales_order;
4754 END IF;
4755
4756 IF (p_new_line_rec.sales_order_date = AR_DATE_DUMMY)
4757 THEN p_out_line_rec.sales_order_date := p_old_line_rec.sales_order_date;
4758 ELSE p_out_line_rec.sales_order_date := p_new_line_rec.sales_order_date;
4759 END IF;
4760
4761 IF (p_new_line_rec.sales_order_line = AR_TEXT_DUMMY)
4762 THEN p_out_line_rec.sales_order_line := p_old_line_rec.sales_order_line;
4763 ELSE p_out_line_rec.sales_order_line := p_new_line_rec.sales_order_line;
4764 END IF;
4765
4766 IF (p_new_line_rec.sales_order_revision = AR_NUMBER_DUMMY)
4767 THEN p_out_line_rec.sales_order_revision :=
4768 p_old_line_rec.sales_order_revision;
4769 ELSE p_out_line_rec.sales_order_revision :=
4770 p_new_line_rec.sales_order_revision;
4771 END IF;
4772
4773 IF (p_new_line_rec.sales_order_source = AR_TEXT_DUMMY)
4774 THEN p_out_line_rec.sales_order_source :=
4775 p_old_line_rec.sales_order_source;
4776 ELSE p_out_line_rec.sales_order_source :=
4777 p_new_line_rec.sales_order_source;
4778 END IF;
4779
4780 IF (p_new_line_rec.vat_tax_id = AR_NUMBER_DUMMY)
4781 THEN p_out_line_rec.vat_tax_id := p_old_line_rec.vat_tax_id;
4782 ELSE p_out_line_rec.vat_tax_id := p_new_line_rec.vat_tax_id;
4783 END IF;
4784
4785 IF (p_new_line_rec.tax_exempt_flag = AR_FLAG_DUMMY)
4786 THEN p_out_line_rec.tax_exempt_flag := p_old_line_rec.tax_exempt_flag;
4787 ELSE p_out_line_rec.tax_exempt_flag := p_new_line_rec.tax_exempt_flag;
4788 END IF;
4789
4790 IF (p_new_line_rec.sales_tax_id = AR_NUMBER_DUMMY)
4791 THEN p_out_line_rec.sales_tax_id := p_old_line_rec.sales_tax_id;
4792 ELSE p_out_line_rec.sales_tax_id := p_new_line_rec.sales_tax_id;
4793 END IF;
4794
4795 IF (p_new_line_rec.location_segment_id = AR_NUMBER_DUMMY)
4796 THEN p_out_line_rec.location_segment_id :=
4797 p_old_line_rec.location_segment_id;
4798 ELSE p_out_line_rec.location_segment_id :=
4799 p_new_line_rec.location_segment_id;
4800 END IF;
4801
4802 IF (p_new_line_rec.tax_exempt_number = AR_TEXT_DUMMY)
4803 THEN p_out_line_rec.tax_exempt_number :=
4804 p_old_line_rec.tax_exempt_number;
4805 ELSE p_out_line_rec.tax_exempt_number :=
4806 p_new_line_rec.tax_exempt_number;
4807 END IF;
4808
4809 IF (p_new_line_rec.tax_exempt_reason_code = AR_TEXT_DUMMY)
4810 THEN p_out_line_rec.tax_exempt_reason_code :=
4811 p_old_line_rec.tax_exempt_reason_code;
4812 ELSE p_out_line_rec.tax_exempt_reason_code :=
4813 p_new_line_rec.tax_exempt_reason_code;
4814 END IF;
4815
4816 IF (p_new_line_rec.tax_vendor_return_code = AR_TEXT_DUMMY)
4817 THEN p_out_line_rec.tax_vendor_return_code :=
4818 p_old_line_rec.tax_vendor_return_code;
4819 ELSE p_out_line_rec.tax_vendor_return_code :=
4820 p_new_line_rec.tax_vendor_return_code;
4821 END IF;
4822
4823 IF (p_new_line_rec.taxable_flag = AR_FLAG_DUMMY)
4824 THEN p_out_line_rec.taxable_flag := p_old_line_rec.taxable_flag;
4825 ELSE p_out_line_rec.taxable_flag := p_new_line_rec.taxable_flag;
4826 END IF;
4827
4828 IF (p_new_line_rec.tax_exemption_id = AR_NUMBER_DUMMY)
4829 THEN p_out_line_rec.tax_exemption_id := p_old_line_rec.tax_exemption_id;
4830 ELSE p_out_line_rec.tax_exemption_id := p_new_line_rec.tax_exemption_id;
4831 END IF;
4832
4833 IF (p_new_line_rec.tax_precedence = AR_NUMBER_DUMMY)
4834 THEN p_out_line_rec.tax_precedence := p_old_line_rec.tax_precedence;
4835 ELSE p_out_line_rec.tax_precedence := p_new_line_rec.tax_precedence;
4836 END IF;
4837
4838 IF (p_new_line_rec.tax_rate = AR_NUMBER_DUMMY)
4839 THEN p_out_line_rec.tax_rate := p_old_line_rec.tax_rate;
4840 ELSE p_out_line_rec.tax_rate := p_new_line_rec.tax_rate;
4841 END IF;
4842
4843 IF (p_new_line_rec.uom_code = AR_TEXT3_DUMMY)
4844 THEN p_out_line_rec.uom_code := p_old_line_rec.uom_code;
4845 ELSE p_out_line_rec.uom_code := p_new_line_rec.uom_code;
4846 END IF;
4847
4848 IF (p_new_line_rec.autotax = AR_FLAG_DUMMY)
4849 THEN p_out_line_rec.autotax := p_old_line_rec.autotax;
4850 ELSE p_out_line_rec.autotax := p_new_line_rec.autotax;
4851 END IF;
4852
4853 IF (p_new_line_rec.movement_id = AR_NUMBER_DUMMY)
4854 THEN p_out_line_rec.movement_id := p_old_line_rec.movement_id;
4855 ELSE p_out_line_rec.movement_id := p_new_line_rec.movement_id;
4856 END IF;
4857
4858 IF (p_new_line_rec.default_ussgl_transaction_code = AR_TEXT_DUMMY)
4859 THEN p_out_line_rec.default_ussgl_transaction_code :=
4860 p_old_line_rec.default_ussgl_transaction_code;
4861 ELSE p_out_line_rec.default_ussgl_transaction_code :=
4862 p_new_line_rec.default_ussgl_transaction_code;
4863 END IF;
4864
4865 IF (p_new_line_rec.default_ussgl_trx_code_context = AR_TEXT_DUMMY)
4866 THEN p_out_line_rec.default_ussgl_trx_code_context :=
4867 p_old_line_rec.default_ussgl_trx_code_context;
4868 ELSE p_out_line_rec.default_ussgl_trx_code_context :=
4869 p_new_line_rec.default_ussgl_trx_code_context;
4870 END IF;
4871
4872 IF (p_new_line_rec.interface_line_context = AR_TEXT_DUMMY)
4873 THEN p_out_line_rec.interface_line_context :=
4874 p_old_line_rec.interface_line_context;
4875 ELSE p_out_line_rec.interface_line_context :=
4876 p_new_line_rec.interface_line_context;
4877 END IF;
4878
4879 IF (p_new_line_rec.interface_line_attribute1 = AR_TEXT_DUMMY)
4880 THEN p_out_line_rec.interface_line_attribute1 :=
4881 p_old_line_rec.interface_line_attribute1;
4882 ELSE p_out_line_rec.interface_line_attribute1 :=
4883 p_new_line_rec.interface_line_attribute1;
4884 END IF;
4885
4886 IF (p_new_line_rec.interface_line_attribute2 = AR_TEXT_DUMMY)
4887 THEN p_out_line_rec.interface_line_attribute2 :=
4888 p_old_line_rec.interface_line_attribute2;
4889 ELSE p_out_line_rec.interface_line_attribute2 :=
4890 p_new_line_rec.interface_line_attribute2;
4891 END IF;
4892
4893 IF (p_new_line_rec.interface_line_attribute3 = AR_TEXT_DUMMY)
4894 THEN p_out_line_rec.interface_line_attribute3 :=
4895 p_old_line_rec.interface_line_attribute3;
4896 ELSE p_out_line_rec.interface_line_attribute3 :=
4897 p_new_line_rec.interface_line_attribute3;
4898 END IF;
4899
4900 IF (p_new_line_rec.interface_line_attribute4 = AR_TEXT_DUMMY)
4901 THEN p_out_line_rec.interface_line_attribute4 :=
4902 p_old_line_rec.interface_line_attribute4;
4903 ELSE p_out_line_rec.interface_line_attribute4 :=
4904 p_new_line_rec.interface_line_attribute4;
4905 END IF;
4906
4907 IF (p_new_line_rec.interface_line_attribute5 = AR_TEXT_DUMMY)
4908 THEN p_out_line_rec.interface_line_attribute5 :=
4909 p_old_line_rec.interface_line_attribute5;
4910 ELSE p_out_line_rec.interface_line_attribute5 :=
4911 p_new_line_rec.interface_line_attribute5;
4912 END IF;
4913
4914 IF (p_new_line_rec.interface_line_attribute6 = AR_TEXT_DUMMY)
4915 THEN p_out_line_rec.interface_line_attribute6 :=
4916 p_old_line_rec.interface_line_attribute6;
4917 ELSE p_out_line_rec.interface_line_attribute6 :=
4918 p_new_line_rec.interface_line_attribute6;
4919 END IF;
4920
4921 IF (p_new_line_rec.interface_line_attribute7 = AR_TEXT_DUMMY)
4922 THEN p_out_line_rec.interface_line_attribute7 :=
4923 p_old_line_rec.interface_line_attribute7;
4924 ELSE p_out_line_rec.interface_line_attribute7 :=
4925 p_new_line_rec.interface_line_attribute7;
4926 END IF;
4927
4928 IF (p_new_line_rec.interface_line_attribute8 = AR_TEXT_DUMMY)
4929 THEN p_out_line_rec.interface_line_attribute8 :=
4930 p_old_line_rec.interface_line_attribute8;
4931 ELSE p_out_line_rec.interface_line_attribute8 :=
4932 p_new_line_rec.interface_line_attribute8;
4933 END IF;
4934
4935 IF (p_new_line_rec.interface_line_attribute9 = AR_TEXT_DUMMY)
4936 THEN p_out_line_rec.interface_line_attribute9 :=
4937 p_old_line_rec.interface_line_attribute9;
4938 ELSE p_out_line_rec.interface_line_attribute9 :=
4939 p_new_line_rec.interface_line_attribute9;
4940 END IF;
4941
4942 IF (p_new_line_rec.interface_line_attribute10 = AR_TEXT_DUMMY)
4943 THEN p_out_line_rec.interface_line_attribute10 :=
4944 p_old_line_rec.interface_line_attribute10;
4945 ELSE p_out_line_rec.interface_line_attribute10 :=
4946 p_new_line_rec.interface_line_attribute10;
4947 END IF;
4948
4949 IF (p_new_line_rec.interface_line_attribute11 = AR_TEXT_DUMMY)
4950 THEN p_out_line_rec.interface_line_attribute11 :=
4951 p_old_line_rec.interface_line_attribute11;
4952 ELSE p_out_line_rec.interface_line_attribute11 :=
4953 p_new_line_rec.interface_line_attribute11;
4954 END IF;
4955
4956 IF (p_new_line_rec.interface_line_attribute12 = AR_TEXT_DUMMY)
4957 THEN p_out_line_rec.interface_line_attribute12 :=
4958 p_old_line_rec.interface_line_attribute12;
4959 ELSE p_out_line_rec.interface_line_attribute12 :=
4960 p_new_line_rec.interface_line_attribute12;
4961 END IF;
4962
4963 IF (p_new_line_rec.interface_line_attribute13 = AR_TEXT_DUMMY)
4964 THEN p_out_line_rec.interface_line_attribute13 :=
4965 p_old_line_rec.interface_line_attribute13;
4966 ELSE p_out_line_rec.interface_line_attribute13 :=
4967 p_new_line_rec.interface_line_attribute13;
4968 END IF;
4969
4970 IF (p_new_line_rec.interface_line_attribute14 = AR_TEXT_DUMMY)
4971 THEN p_out_line_rec.interface_line_attribute14 :=
4972 p_old_line_rec.interface_line_attribute14;
4973 ELSE p_out_line_rec.interface_line_attribute14 :=
4974 p_new_line_rec.interface_line_attribute14;
4975 END IF;
4976
4977 IF (p_new_line_rec.interface_line_attribute15 = AR_TEXT_DUMMY)
4978 THEN p_out_line_rec.interface_line_attribute15 :=
4979 p_old_line_rec.interface_line_attribute15;
4980 ELSE p_out_line_rec.interface_line_attribute15 :=
4981 p_new_line_rec.interface_line_attribute15;
4982 END IF;
4983
4984 IF (p_new_line_rec.attribute_category = AR_TEXT_DUMMY)
4985 THEN p_out_line_rec.attribute_category :=
4986 p_old_line_rec.attribute_category;
4987 ELSE p_out_line_rec.attribute_category :=
4988 p_new_line_rec.attribute_category;
4989 END IF;
4990
4991 IF (p_new_line_rec.attribute1 = AR_TEXT_DUMMY)
4992 THEN p_out_line_rec.attribute1 := p_old_line_rec.attribute1;
4993 ELSE p_out_line_rec.attribute1 := p_new_line_rec.attribute1;
4994 END IF;
4995
4996 IF (p_new_line_rec.attribute2 = AR_TEXT_DUMMY)
4997 THEN p_out_line_rec.attribute2 := p_old_line_rec.attribute2;
4998 ELSE p_out_line_rec.attribute2 := p_new_line_rec.attribute2;
4999 END IF;
5000
5001 IF (p_new_line_rec.attribute3 = AR_TEXT_DUMMY)
5002 THEN p_out_line_rec.attribute3 := p_old_line_rec.attribute3;
5003 ELSE p_out_line_rec.attribute3 := p_new_line_rec.attribute3;
5004 END IF;
5005
5006 IF (p_new_line_rec.attribute4 = AR_TEXT_DUMMY)
5007 THEN p_out_line_rec.attribute4 := p_old_line_rec.attribute4;
5008 ELSE p_out_line_rec.attribute4 := p_new_line_rec.attribute4;
5009 END IF;
5010
5011 IF (p_new_line_rec.attribute5 = AR_TEXT_DUMMY)
5012 THEN p_out_line_rec.attribute5 := p_old_line_rec.attribute5;
5013 ELSE p_out_line_rec.attribute5 := p_new_line_rec.attribute5;
5014 END IF;
5015
5016 IF (p_new_line_rec.attribute6 = AR_TEXT_DUMMY)
5017 THEN p_out_line_rec.attribute6 := p_old_line_rec.attribute6;
5018 ELSE p_out_line_rec.attribute6 := p_new_line_rec.attribute6;
5019 END IF;
5020
5021 IF (p_new_line_rec.attribute7 = AR_TEXT_DUMMY)
5022 THEN p_out_line_rec.attribute7 := p_old_line_rec.attribute7;
5023 ELSE p_out_line_rec.attribute7 := p_new_line_rec.attribute7;
5024 END IF;
5025
5026 IF (p_new_line_rec.attribute8 = AR_TEXT_DUMMY)
5027 THEN p_out_line_rec.attribute8 := p_old_line_rec.attribute8;
5028 ELSE p_out_line_rec.attribute8 := p_new_line_rec.attribute8;
5029 END IF;
5030
5031 IF (p_new_line_rec.attribute9 = AR_TEXT_DUMMY)
5032 THEN p_out_line_rec.attribute9 := p_old_line_rec.attribute9;
5033 ELSE p_out_line_rec.attribute9 := p_new_line_rec.attribute9;
5034 END IF;
5035
5036 IF (p_new_line_rec.attribute10 = AR_TEXT_DUMMY)
5037 THEN p_out_line_rec.attribute10 := p_old_line_rec.attribute10;
5038 ELSE p_out_line_rec.attribute10 := p_new_line_rec.attribute10;
5039 END IF;
5040
5041 IF (p_new_line_rec.attribute11 = AR_TEXT_DUMMY)
5042 THEN p_out_line_rec.attribute11 := p_old_line_rec.attribute11;
5043 ELSE p_out_line_rec.attribute11 := p_new_line_rec.attribute11;
5044 END IF;
5045
5046 IF (p_new_line_rec.attribute12 = AR_TEXT_DUMMY)
5047 THEN p_out_line_rec.attribute12 := p_old_line_rec.attribute12;
5048 ELSE p_out_line_rec.attribute12 := p_new_line_rec.attribute12;
5049 END IF;
5050
5051 IF (p_new_line_rec.attribute13 = AR_TEXT_DUMMY)
5052 THEN p_out_line_rec.attribute13 := p_old_line_rec.attribute13;
5053 ELSE p_out_line_rec.attribute13 := p_new_line_rec.attribute13;
5054 END IF;
5055
5056 IF (p_new_line_rec.attribute14 = AR_TEXT_DUMMY)
5057 THEN p_out_line_rec.attribute14 := p_old_line_rec.attribute14;
5058 ELSE p_out_line_rec.attribute14 := p_new_line_rec.attribute14;
5059 END IF;
5060
5061 IF (p_new_line_rec.attribute15 = AR_TEXT_DUMMY)
5062 THEN p_out_line_rec.attribute15 := p_old_line_rec.attribute15;
5063 ELSE p_out_line_rec.attribute15 := p_new_line_rec.attribute15;
5064 END IF;
5065
5066 IF (p_new_line_rec.global_attribute_category = AR_TEXT_DUMMY)
5067 THEN p_out_line_rec.global_attribute_category :=
5068 p_old_line_rec.global_attribute_category;
5069 ELSE p_out_line_rec.global_attribute_category :=
5070 p_new_line_rec.global_attribute_category;
5071 END IF;
5072
5073 IF (p_new_line_rec.global_attribute1 = AR_TEXT_DUMMY)
5074 THEN p_out_line_rec.global_attribute1 := p_old_line_rec.global_attribute1;
5075 ELSE p_out_line_rec.global_attribute1 := p_new_line_rec.global_attribute1;
5076 END IF;
5077
5078 IF (p_new_line_rec.global_attribute2 = AR_TEXT_DUMMY)
5079 THEN p_out_line_rec.global_attribute2 := p_old_line_rec.global_attribute2;
5080 ELSE p_out_line_rec.global_attribute2 := p_new_line_rec.global_attribute2;
5081 END IF;
5082
5083 IF (p_new_line_rec.global_attribute3 = AR_TEXT_DUMMY)
5084 THEN p_out_line_rec.global_attribute3 := p_old_line_rec.global_attribute3;
5085 ELSE p_out_line_rec.global_attribute3 := p_new_line_rec.global_attribute3;
5086 END IF;
5087
5088 IF (p_new_line_rec.global_attribute4 = AR_TEXT_DUMMY)
5089 THEN p_out_line_rec.global_attribute4 := p_old_line_rec.global_attribute4;
5090 ELSE p_out_line_rec.global_attribute4 := p_new_line_rec.global_attribute4;
5091 END IF;
5092
5093 IF (p_new_line_rec.global_attribute5 = AR_TEXT_DUMMY)
5094 THEN p_out_line_rec.global_attribute5 := p_old_line_rec.global_attribute5;
5095 ELSE p_out_line_rec.global_attribute5 := p_new_line_rec.global_attribute5;
5096 END IF;
5097
5098 IF (p_new_line_rec.global_attribute6 = AR_TEXT_DUMMY)
5099 THEN p_out_line_rec.global_attribute6 := p_old_line_rec.global_attribute6;
5100 ELSE p_out_line_rec.global_attribute6 := p_new_line_rec.global_attribute6;
5101 END IF;
5102
5103 IF (p_new_line_rec.global_attribute7 = AR_TEXT_DUMMY)
5104 THEN p_out_line_rec.global_attribute7 := p_old_line_rec.global_attribute7;
5105 ELSE p_out_line_rec.global_attribute7 := p_new_line_rec.global_attribute7;
5106 END IF;
5107
5108 IF (p_new_line_rec.global_attribute8 = AR_TEXT_DUMMY)
5109 THEN p_out_line_rec.global_attribute8 := p_old_line_rec.global_attribute8;
5110 ELSE p_out_line_rec.global_attribute8 := p_new_line_rec.global_attribute8;
5111 END IF;
5112
5113 IF (p_new_line_rec.global_attribute9 = AR_TEXT_DUMMY)
5114 THEN p_out_line_rec.global_attribute9 := p_old_line_rec.global_attribute9;
5115 ELSE p_out_line_rec.global_attribute9 := p_new_line_rec.global_attribute9;
5116 END IF;
5117
5118 IF (p_new_line_rec.global_attribute10 = AR_TEXT_DUMMY)
5119 THEN p_out_line_rec.global_attribute10 := p_old_line_rec.global_attribute10;
5120 ELSE p_out_line_rec.global_attribute10 := p_new_line_rec.global_attribute10;
5121 END IF;
5122
5123 IF (p_new_line_rec.global_attribute11 = AR_TEXT_DUMMY)
5124 THEN p_out_line_rec.global_attribute11 := p_old_line_rec.global_attribute11;
5125 ELSE p_out_line_rec.global_attribute11 := p_new_line_rec.global_attribute11;
5126 END IF;
5127
5128 IF (p_new_line_rec.global_attribute12 = AR_TEXT_DUMMY)
5129 THEN p_out_line_rec.global_attribute12 := p_old_line_rec.global_attribute12;
5130 ELSE p_out_line_rec.global_attribute12 := p_new_line_rec.global_attribute12;
5131 END IF;
5132
5133 IF (p_new_line_rec.global_attribute13 = AR_TEXT_DUMMY)
5134 THEN p_out_line_rec.global_attribute13 := p_old_line_rec.global_attribute13;
5135 ELSE p_out_line_rec.global_attribute13 := p_new_line_rec.global_attribute13;
5136 END IF;
5137
5138 IF (p_new_line_rec.global_attribute14 = AR_TEXT_DUMMY)
5139 THEN p_out_line_rec.global_attribute14 := p_old_line_rec.global_attribute14;
5140 ELSE p_out_line_rec.global_attribute14 := p_new_line_rec.global_attribute14;
5141 END IF;
5142
5143 IF (p_new_line_rec.global_attribute15 = AR_TEXT_DUMMY)
5144 THEN p_out_line_rec.global_attribute15 := p_old_line_rec.global_attribute15;
5145 ELSE p_out_line_rec.global_attribute15 := p_new_line_rec.global_attribute15;
5146 END IF;
5147
5148 IF (p_new_line_rec.global_attribute16 = AR_TEXT_DUMMY)
5149 THEN p_out_line_rec.global_attribute16 := p_old_line_rec.global_attribute16;
5150 ELSE p_out_line_rec.global_attribute16 := p_new_line_rec.global_attribute16; END IF;
5151
5152 IF (p_new_line_rec.global_attribute17 = AR_TEXT_DUMMY)
5153 THEN p_out_line_rec.global_attribute17 := p_old_line_rec.global_attribute17;
5154 ELSE p_out_line_rec.global_attribute17 := p_new_line_rec.global_attribute17;
5155 END IF;
5156
5157 IF (p_new_line_rec.global_attribute18 = AR_TEXT_DUMMY)
5158 THEN p_out_line_rec.global_attribute18 := p_old_line_rec.global_attribute18;
5159 ELSE p_out_line_rec.global_attribute18 := p_new_line_rec.global_attribute18;
5160 END IF;
5161
5162 IF (p_new_line_rec.global_attribute19 = AR_TEXT_DUMMY)
5163 THEN p_out_line_rec.global_attribute19 := p_old_line_rec.global_attribute19;
5164 ELSE p_out_line_rec.global_attribute19 := p_new_line_rec.global_attribute19;
5165 END IF;
5166
5167 IF (p_new_line_rec.global_attribute20 = AR_TEXT_DUMMY)
5168 THEN p_out_line_rec.global_attribute20 := p_old_line_rec.global_attribute20;
5169 ELSE p_out_line_rec.global_attribute20 := p_new_line_rec.global_attribute20;
5170 END IF;
5171
5172 IF (p_new_line_rec.created_by = AR_NUMBER_DUMMY)
5173 THEN p_out_line_rec.created_by := p_old_line_rec.created_by;
5174 ELSE p_out_line_rec.created_by := p_new_line_rec.created_by;
5175 END IF;
5176
5177 IF (p_new_line_rec.creation_date = AR_DATE_DUMMY)
5178 THEN p_out_line_rec.creation_date := p_old_line_rec.creation_date;
5179 ELSE p_out_line_rec.creation_date := p_new_line_rec.creation_date;
5180 END IF;
5181
5182 IF (p_new_line_rec.last_updated_by = AR_NUMBER_DUMMY)
5183 THEN p_out_line_rec.last_updated_by := p_old_line_rec.last_updated_by;
5184 ELSE p_out_line_rec.last_updated_by := p_new_line_rec.last_updated_by;
5185 END IF;
5186
5187 IF (p_new_line_rec.last_update_date = AR_DATE_DUMMY)
5188 THEN p_out_line_rec.last_update_date := p_old_line_rec.last_update_date;
5189 ELSE p_out_line_rec.last_update_date := p_new_line_rec.last_update_date;
5190 END IF;
5191
5192 IF (p_new_line_rec.program_application_id = AR_NUMBER_DUMMY)
5193 THEN p_out_line_rec.program_application_id :=
5194 p_old_line_rec.program_application_id;
5195 ELSE p_out_line_rec.program_application_id :=
5196 p_new_line_rec.program_application_id;
5197 END IF;
5198
5199 IF (p_new_line_rec.last_update_login = AR_NUMBER_DUMMY)
5200 THEN p_out_line_rec.last_update_login :=
5201 p_old_line_rec.last_update_login;
5202 ELSE p_out_line_rec.last_update_login :=
5203 p_new_line_rec.last_update_login;
5204 END IF;
5205
5206 IF (p_new_line_rec.program_id = AR_NUMBER_DUMMY)
5207 THEN p_out_line_rec.program_id := p_old_line_rec.program_id;
5208 ELSE p_out_line_rec.program_id := p_new_line_rec.program_id;
5209 END IF;
5210
5211 IF (p_new_line_rec.program_update_date = AR_DATE_DUMMY)
5212 THEN p_out_line_rec.program_update_date :=
5213 p_old_line_rec.program_update_date;
5214 ELSE p_out_line_rec.program_update_date :=
5215 p_new_line_rec.program_update_date;
5216 END IF;
5217
5218 IF (p_new_line_rec.set_of_books_id = AR_NUMBER_DUMMY)
5219 THEN p_out_line_rec.set_of_books_id := p_old_line_rec.set_of_books_id;
5220 ELSE p_out_line_rec.set_of_books_id := p_new_line_rec.set_of_books_id;
5221 END IF;
5222
5223 -- Rel. 11 Changes:
5224
5225 IF (p_new_line_rec.gross_unit_selling_price = AR_NUMBER_DUMMY)
5226 THEN p_out_line_rec.gross_unit_selling_price :=
5227 p_old_line_rec.gross_unit_selling_price;
5228 ELSE p_out_line_rec.gross_unit_selling_price :=
5229 p_new_line_rec.gross_unit_selling_price;
5230 END IF;
5231
5232 IF (p_new_line_rec.gross_extended_amount = AR_NUMBER_DUMMY)
5233 THEN p_out_line_rec.gross_extended_amount :=
5234 p_old_line_rec.gross_extended_amount;
5235 ELSE p_out_line_rec.gross_extended_amount :=
5236 p_new_line_rec.gross_extended_amount;
5237 END IF;
5238
5239 IF (p_new_line_rec.amount_includes_tax_flag = AR_FLAG_DUMMY)
5240 THEN p_out_line_rec.amount_includes_tax_flag :=
5241 p_old_line_rec.amount_includes_tax_flag;
5242 ELSE p_out_line_rec.amount_includes_tax_flag :=
5243 p_new_line_rec.amount_includes_tax_flag;
5244 END IF;
5245
5246 /* Bug 853757 */
5247 IF (p_new_line_rec.taxable_amount = AR_NUMBER_DUMMY)
5248 THEN p_out_line_rec.taxable_amount :=
5249 p_old_line_rec.taxable_amount;
5250 ELSE p_out_line_rec.taxable_amount :=
5251 p_new_line_rec.taxable_amount;
5252 END IF;
5253
5254 IF (p_new_line_rec.extended_acctd_amount = AR_NUMBER_DUMMY)
5255 THEN p_out_line_rec.extended_acctd_amount :=
5256 p_old_line_rec.extended_acctd_amount;
5257 ELSE p_out_line_rec.extended_acctd_amount :=
5258 p_new_line_rec.extended_acctd_amount;
5259 END IF;
5260
5261 IF (p_new_line_rec.br_ref_customer_trx_id = AR_NUMBER_DUMMY)
5262 THEN p_out_line_rec.br_ref_customer_trx_id :=
5263 p_old_line_rec.br_ref_customer_trx_id;
5264 ELSE p_out_line_rec.br_ref_customer_trx_id :=
5265 p_new_line_rec.br_ref_customer_trx_id;
5266 END IF;
5267
5268 IF (p_new_line_rec.br_ref_payment_schedule_id = AR_NUMBER_DUMMY)
5269 THEN p_out_line_rec.br_ref_payment_schedule_id :=
5270 p_old_line_rec.br_ref_payment_schedule_id;
5271 ELSE p_out_line_rec.br_ref_payment_schedule_id :=
5272 p_new_line_rec.br_ref_payment_schedule_id;
5273 END IF;
5274
5275 IF (p_new_line_rec.br_adjustment_id = AR_NUMBER_DUMMY)
5276 THEN p_out_line_rec.br_adjustment_id :=
5277 p_old_line_rec.br_adjustment_id;
5278 ELSE p_out_line_rec.br_adjustment_id :=
5279 p_new_line_rec.br_adjustment_id;
5280 END IF;
5281
5282 IF (p_new_line_rec.wh_update_date = AR_DATE_DUMMY)
5283 THEN p_out_line_rec.wh_update_date :=
5284 p_old_line_rec.wh_update_date;
5285 ELSE p_out_line_rec.wh_update_date :=
5286 p_new_line_rec.wh_update_date;
5287 END IF;
5288
5289 IF (p_new_line_rec.payment_set_id = AR_NUMBER_DUMMY)
5290 THEN p_out_line_rec.payment_set_id := p_old_line_rec.payment_set_id;
5291 ELSE p_out_line_rec.payment_set_id := p_new_line_rec.payment_set_id;
5292 END IF;
5293
5294 /* ETax - to support ship to at line level information */
5295 IF (p_new_line_rec.ship_to_customer_id = AR_NUMBER_DUMMY) THEN
5296 p_out_line_rec.ship_to_customer_id := p_old_line_rec.ship_to_customer_id;
5297 ELSE
5298 p_out_line_rec.ship_to_customer_id := p_new_line_rec.ship_to_customer_id;
5299 END IF;
5300
5301
5302 IF (p_new_line_rec.ship_to_site_use_id = AR_NUMBER_DUMMY) THEN
5303 p_out_line_rec.ship_to_site_use_id := p_old_line_rec.ship_to_site_use_id;
5304 ELSE
5305 p_out_line_rec.ship_to_site_use_id := p_new_line_rec.ship_to_site_use_id; END IF;
5306
5307 IF (p_new_line_rec.ship_to_contact_id = AR_NUMBER_DUMMY) THEN
5308 p_out_line_rec.ship_to_contact_id := p_old_line_rec.ship_to_contact_id;
5309 ELSE
5310 p_out_line_rec.ship_to_contact_id := p_new_line_rec.ship_to_contact_id;
5311 END IF;
5312
5313 IF (p_new_line_rec.tax_classification_code = AR_TEXT_DUMMY) THEN
5314 p_out_line_rec.tax_classification_code :=
5315 p_old_line_rec.tax_classification_code;
5316 ELSE
5317 p_out_line_rec.tax_classification_code :=
5318 p_new_line_rec.tax_classification_code;
5319 END IF;
5320 IF (p_new_line_rec.rule_end_date = AR_DATE_DUMMY)
5321 THEN p_out_line_rec.rule_end_date := p_old_line_rec.rule_end_date;
5322 ELSE p_out_line_rec.rule_end_date := p_new_line_rec.rule_end_date;
5323 END IF;
5324
5325 IF PG_DEBUG in ('Y', 'C') THEN
5326 arp_util.debug( 'arp_ctl_pkg.merge_line_recs()-');
5327 END IF;
5328
5329 EXCEPTION
5330 WHEN OTHERS THEN
5331 IF PG_DEBUG in ('Y', 'C') THEN
5332 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.merge_line_recs()');
5333 arp_util.debug( '');
5334 arp_util.debug( '-------- parameters for merge_line_recs() ------');
5335 arp_util.debug( ' ---- old line record ----');
5336 END IF;
5337 display_line_rec(p_old_line_rec);
5338 IF PG_DEBUG in ('Y', 'C') THEN
5339 arp_util.debug( '');
5340 arp_util.debug( ' ---- new line record ----');
5341 END IF;
5342 display_line_rec(p_new_line_rec);
5343 IF PG_DEBUG in ('Y', 'C') THEN
5344 arp_util.debug( '');
5345 arp_util.debug( ' ---- merged line record ----');
5346 END IF;
5347 display_line_rec(p_out_line_rec);
5348 IF PG_DEBUG in ('Y', 'C') THEN
5349 arp_util.debug( '');
5350 END IF;
5351
5352 RAISE;
5353
5354 END;
5355
5356 /*===========================================================================+
5357 | FUNCTION |
5358 | insert_line_f_cm_ct_ctl_id |
5359 | |
5360 | DESCRIPTION |
5361 | This procedure creates credit memo lines for the specified line type |
5362 | |
5363 | SCOPE - PUBLIC |
5364 | |
5365 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
5366 | arp_util.debug |
5367 | |
5368 | ARGUMENTS : IN: |
5369 | None |
5370 | OUT: |
5371 | None |
5372 | |
5373 | RETURNS : NONE |
5374 | |
5375 | NOTES |
5376 | |
5377 | MODIFICATION HISTORY |
5378 | 29-AUG-95 Subash Chadalavada Created |
5379 | |
5380 | Rel. 11 Changes: |
5381 | ---------------- |
5382 | 07-22-97 OSTEINME added code to handle three new |
5383 | database columns: |
5384 | - gross_unit_selling_price |
5385 | - gross_extended_amount |
5386 | - amount_includes_tax_flag |
5387 | 08-20-97 OSTEINME changed procedure to populate |
5388 | created credit memo lines with |
5389 | amount_includes_tax_flag copied from |
5390 | invoice line |
5391 |
5392 | |
5393 | 10-JAN-99 Saloni Shah added warehouse_id for global tax |
5394 | engine changes. |
5395 | 08-Apr-03 Veena Rao Bug 2859668. Added field trans- |
5396 | lated_description. |
5397 | 26-Dec-03 Surendra Rajan Bug-3335466 Replace the zero amount |
5398 | with 1 through decode in the Quantity|
5399 | calculationugh decode in the Quantity|
5400 | 17-Feb-04 Surendra Rajan Bug-3449586 commented the amount |
5401 | checking to correct the rounding |
5402 | errors.Ref. bug-3409173 |
5403 | 13-JUN-05 Jon Beckett R12 eTax uptake - included ship to |
5404 | columns and tax classification code |
5405 | 16-AUG-05 Jon Beckett R12 eTax uptake - added p_tax_amount |
5406 | to set mode for line_det_factors |
5407 | 04-Jan-06 Surendra Rajan Bug 3658284 : Added the code to impl-|
5408 | -ement the line level rounding logic.|
5409 +===========================================================================*/
5410 PROCEDURE insert_line_f_cm_ct_ctl_id(
5411 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
5412 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%type,
5413 p_prev_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
5414 p_line_type IN ra_customer_trx_lines.line_type%type,
5415 p_line_percent IN number,
5416 p_uncredited_amount IN ra_customer_trx_lines.extended_amount%type,
5417 p_credit_amount IN ra_customer_trx_lines.extended_amount%type,
5418 p_currency_code IN fnd_currencies.currency_code%type,
5419 p_tax_amount IN ra_customer_trx_lines.extended_amount%type)
5420 IS
5421 l_rows_inserted number;
5422 --{BUG#3339072
5423 l_trx_line_array ctlrec;
5424 i NUMBER;
5425 --}
5426 l_mode VARCHAR2(30);
5427 /* Bug-3658284 */
5428 l_amt_run_total Number ;
5429 l_amt_prev_run_total Number ;
5430 l_rev_run_total Number ;
5431 l_rev_prev_run_total Number ;
5432 /* Bug Number 6790882 */
5433 l_total_tax_prorate ra_customer_trx_lines.extended_amount%type := 0;
5434 l_tax_amount ra_customer_trx_lines.extended_amount%type;
5435 l_quantity_invoiced Number ;
5436 l_round Number ;
5437 l_qnt_temp Number;
5438
5439 --bug 9125212
5440 cursor c1(c_prev_customer_trx_line_id number) is
5441 select quantity_invoiced
5442 FROM ra_customer_trx_lines
5443 WHERE customer_trx_line_id=c_prev_customer_trx_line_id;
5444 BEGIN
5445
5446 IF PG_DEBUG in ('Y', 'C') THEN
5447 arp_util.debug( 'arp_ctl_pkg.insert_line_f_cm_ct_ctl_id()+');
5448 -- MVKOLLA - Bug7113653 Added following debug messages
5449 arp_util.debug('-- Added below messages for bug7113653');
5450 arp_util.debug('p_customer_trx_id '||p_customer_trx_id);
5451 arp_util.debug('p_customer_trx_line_id '||p_customer_trx_line_id);
5452 arp_util.debug('p_prev_customer_trx_id '||p_prev_customer_trx_id);
5453 arp_util.debug('p_line_type '||p_line_type);
5454 arp_util.debug('p_line_percent '||p_line_percent);
5455 arp_util.debug('p_uncredited_amount '||p_uncredited_amount);
5456 arp_util.debug('p_credit_amount '||p_credit_amount);
5457 arp_util.debug('p_currency_code '||p_currency_code);
5458 arp_util.debug('p_tax_amount '||p_tax_amount);
5459 END IF;
5460
5461 --{BUG#3339072
5462 -- Bug 10169170, Modified the following query to add an Order By clause.
5463 SELECT ra_customer_trx_lines_s.nextval,
5464 p_customer_trx_id,
5465 pg_user_id, /* created_by */
5466 sysdate, /* creation_date */
5467 pg_user_id, /* last_updated_by */
5468 sysdate, /* last_update_date */
5469 nvl(pg_conc_login_id,
5470 pg_login_id), /* last_update_login */
5471 a.line_number,
5472 a.line_type,
5473 a.set_of_books_id,
5474 a.accounting_rule_id,
5475 a.autorule_complete_flag,
5476 a.accounting_rule_duration,
5477 a.description,
5478 a.initial_customer_trx_line_id,
5479 a.inventory_item_id,
5480 a.item_exception_rate_id,
5481 a.memo_line_id,
5482 a.reason_code,
5483 a.previous_customer_trx_id,
5484 a.previous_customer_trx_line_id,
5485 a.link_to_cust_trx_line_id,
5486 a.unit_standard_price,
5487 a.unit_selling_price,
5488 a.gross_unit_selling_price, -- Bug 7389126 KALYAN
5489 a.gross_extended_amount, -- 6882394
5490 a.original_extended_amount, -- 6882394 (original)
5491 a.original_revenue_amount,
5492 a.quantity_credited,
5493 a.quantity_invoiced,
5494 a.extended_amount,
5495 a.revenue_amount,
5496 a.sales_order,
5497 a.sales_order_date,
5498 a.sales_order_line,
5499 a.sales_order_revision,
5500 a.sales_order_source,
5501 a.tax_exemption_id,
5502 a.tax_precedence,
5503 a.tax_rate,
5504 a.uom_code,
5505 a.default_ussgl_transaction_code,
5506 a.default_ussgl_trx_code_context,
5507 a.sales_tax_id,
5508 a.location_segment_id,
5509 a.vat_tax_id,
5510 a.amount_includes_tax_flag,
5511 a.warehouse_id,
5512 a.taxable_amount,
5513 a.translated_description,
5514 a.org_id,
5515 a.ship_to_customer_id,
5516 a.ship_to_address_id,
5517 a.ship_to_site_use_id,
5518 a.ship_to_contact_id,
5519 a.tax_classification_code,
5520 a.historical_flag,
5521 a.memo_line_type
5522 BULK COLLECT INTO
5523 l_trx_line_array.customer_trx_line_id,
5524 l_trx_line_array.customer_trx_id,
5525 l_trx_line_array.created_by,
5526 l_trx_line_array.creation_date,
5527 l_trx_line_array.last_updated_by,
5528 l_trx_line_array.last_update_date,
5529 l_trx_line_array.last_update_login,
5530 l_trx_line_array.line_number,
5531 l_trx_line_array.line_type,
5532 l_trx_line_array.set_of_books_id,
5533 l_trx_line_array.accounting_rule_id,
5534 l_trx_line_array.autorule_complete_flag,
5535 l_trx_line_array.last_period_to_credit,
5536 l_trx_line_array.description,
5537 l_trx_line_array.initial_customer_trx_line_id,
5538 l_trx_line_array.inventory_item_id,
5539 l_trx_line_array.item_exception_rate_id,
5540 l_trx_line_array.memo_line_id,
5541 l_trx_line_array.reason_code,
5542 l_trx_line_array.previous_customer_trx_id,
5543 l_trx_line_array.previous_customer_trx_line_id,
5544 l_trx_line_array.link_to_cust_trx_line_id,
5545 l_trx_line_array.unit_standard_price,
5546 l_trx_line_array.unit_selling_price,
5547 l_trx_line_array.gross_unit_selling_price, -- Bug 7389126 KALYAN
5548 l_trx_line_array.gross_extended_amount, -- 6882394
5549 l_trx_line_array.original_extended_amount, -- 6882394
5550 l_trx_line_array.original_revenue_amount, -- 6882394
5551 l_trx_line_array.quantity_credited,
5552 l_trx_line_array.quantity_invoiced, -- Bug 6990227.
5553 l_trx_line_array.extended_amount,
5554 l_trx_line_array.revenue_amount,
5555 l_trx_line_array.sales_order,
5556 l_trx_line_array.sales_order_date,
5557 l_trx_line_array.sales_order_line,
5558 l_trx_line_array.sales_order_revision,
5559 l_trx_line_array.sales_order_source,
5560 l_trx_line_array.tax_exemption_id,
5561 l_trx_line_array.tax_precedence,
5562 l_trx_line_array.tax_rate,
5563 l_trx_line_array.uom_code,
5564 l_trx_line_array.default_ussgl_transaction_code,
5565 l_trx_line_array.default_ussgl_trx_code_context,
5566 l_trx_line_array.sales_tax_id,
5567 l_trx_line_array.location_segment_id,
5568 l_trx_line_array.vat_tax_id,
5569 l_trx_line_array.amount_includes_tax_flag,
5570 l_trx_line_array.warehouse_id,
5571 l_trx_line_array.taxable_amount,
5572 l_trx_line_array.translated_description,
5573 l_trx_line_array.org_id,
5574 l_trx_line_array.ship_to_customer_id,
5575 l_trx_line_array.ship_to_address_id,
5576 l_trx_line_array.ship_to_site_use_id,
5577 l_trx_line_array.ship_to_contact_id,
5578 l_trx_line_array.tax_classification_code,
5579 l_trx_line_array.historical_flag,
5580 l_trx_line_array.memo_line_type
5581 --}
5582 FROM
5583 (SELECT inv_ctl.line_number,
5584 decode(nra.line_type,
5585 'CB', 'LINE',
5586 nra.line_type) line_type, /* line_type */
5587 inv_ctl.set_of_books_id,
5588 inv_ctl.accounting_rule_id,
5589 decode(inv_ctl.accounting_rule_id,
5590 NULL, decode(inv_ctl.line_type,
5591 'TAX', 'Y',
5592 'FREIGHT', 'Y',
5593 ''),
5594 'N') autorule_complete_flag, /* autorule_complete_flag */
5595 decode(inv_ctl.line_type,
5596 'TAX', '',
5597 'FREIGHT', '',
5598 decode(cm_ct.credit_method_for_rules,
5599 'UNIT', inv_ctl.accounting_rule_duration,
5600 '')) accounting_rule_duration, /* accounting_rule_duration */
5601 inv_ctl.description,
5602 inv_ctl.initial_customer_trx_line_id,
5603 inv_ctl.inventory_item_id,
5604 inv_ctl.item_exception_rate_id,
5605 inv_ctl.memo_line_id,
5606 cm_ct.reason_code,
5607 inv_ctl.customer_trx_id previous_customer_trx_id,
5608 inv_ctl.customer_trx_line_id previous_customer_trx_line_id,
5609 cm_ctl.customer_trx_line_id link_to_cust_trx_line_id,
5610 inv_ctl.unit_standard_price,
5611 inv_ctl.unit_selling_price,
5612 inv_ctl.gross_unit_selling_price, -- Bug 7389126 KALYAN
5613 inv_ctl.gross_extended_amount, -- 6882394
5614 inv_ctl.extended_amount original_extended_amount, -- 6882394 (original)
5615 inv_ctl.revenue_amount original_revenue_amount, -- 6882394 (original)
5616 decode(inv_ctl.line_type,
5617 'TAX', '',
5618 'FREIGHT', '',
5619 /* Bug3658284 arpcurr.CurrRound( */
5620 /* Bug-3335466 - Replace the zero amount with 1 */
5621 ( decode(nra.net_amount,0,1,nra.net_amount) /
5622 decode(p_uncredited_amount,
5623 0, 1,
5624 p_uncredited_amount
5625 ) *
5626 /* Bug 852633: revert changes for bug 583790 */
5627 decode(p_credit_amount, 0, decode(nvl(p_line_percent, 0), 0, 0, -1), p_credit_amount)
5628 )
5629 /* Bug3658284 , p_currency_code) */
5630 * inv_ctl.quantity_invoiced /
5631 decode(inv_ctl.extended_amount, 0, 1,
5632 inv_ctl.extended_amount) *
5633 decode(inv_ctl.line_type,
5634 'CHARGES', '',
5635 1
5636 )
5637 ) quantity_credited, /* quantity */
5638 inv_ctl.quantity_invoiced, -- Bug 6990227
5639 /* Bug3658284 arpcurr.CurrRound( */
5640 ( nra.net_amount /
5641 decode(p_uncredited_amount,
5642 0, 1,
5643 p_uncredited_amount
5644 ) *
5645 /* Bug 852633: revert changes for bug 583790 */
5646 decode(p_credit_amount, 0, decode(nvl(p_line_percent, 0), 0, 0, -1),
5647 p_credit_amount)
5648 )
5649 /* Bug3658284 , p_currency_code) */
5650 extended_amount , /* extended_amount */
5651 decode(inv_ctl.line_type,
5652 'TAX', '',
5653 'FREIGHT', '',
5654 /* Bug3658284 arpcurr.CurrRound( */
5655 ( nra.net_amount /
5656 decode(p_uncredited_amount,
5657 0, 1,
5658 p_uncredited_amount
5659 ) *
5660 /* Bug 852633: revert changes for bug 583790 */
5661 decode(p_credit_amount, 0, decode(nvl(p_line_percent, 0), 0, 0, -1), p_credit_amount)
5662 ) *
5663 ( nvl(inv_ctl.revenue_amount,
5664 inv_ctl.extended_amount) /
5665 decode(inv_ctl.extended_amount,
5666 0, 1,
5667 inv_ctl.extended_amount)
5668 )
5669 /* Bug3658284 , p_currency_code) */
5670 * decode(inv_ctl.line_type,
5671 'CHARGES', '',
5672 1)
5673 ) revenue_amount, /* revenue_amount */
5674 decode(inv_ctl.line_type,
5675 'TAX', '',
5676 'FREIGHT', '',
5677 inv_ctl.sales_order) sales_order,
5678 decode(inv_ctl.line_type,
5679 'TAX', null,
5680 'FREIGHT', null,
5681 inv_ctl.sales_order_date) sales_order_date,
5682 decode(inv_ctl.line_type,
5683 'TAX', '',
5684 'FREIGHT', '',
5685 inv_ctl.sales_order_line) sales_order_line,
5686 decode(inv_ctl.line_type,
5687 'TAX', '',
5688 'FREIGHT', '',
5689 inv_ctl.sales_order_revision) sales_order_revision,
5690 decode(inv_ctl.line_type,
5691 'TAX', '',
5692 'FREIGHT', '',
5693 inv_ctl.sales_order_source) sales_order_source,
5694 inv_ctl.tax_exemption_id,
5695 inv_ctl.tax_precedence,
5696 inv_ctl.tax_rate,
5697 inv_ctl.uom_code,
5698 cm_ct.default_ussgl_transaction_code,
5699 cm_ct.default_ussgl_trx_code_context,
5700 inv_ctl.sales_tax_id,
5701 inv_ctl.location_segment_id,
5702 inv_ctl.vat_tax_id,
5703 inv_ctl.amount_includes_tax_flag,
5704 inv_ctl.warehouse_id,
5705 --Fix for bug 1161592
5706 cm_ctl.extended_amount taxable_amount/* Taxable_amount */
5707 , inv_ctl.translated_description
5708 ,cm_ct.org_id /* SSA changes anuj */
5709 /* R12 eTax uptake - ship to and tax columns needed */
5710 ,inv_ctl.ship_to_customer_id
5711 ,inv_ctl.ship_to_address_id
5712 ,inv_ctl.ship_to_site_use_id
5713 ,inv_ctl.ship_to_contact_id
5714 ,inv_ctl.tax_classification_code
5715 /* NULL=Y N=not historical, calc tax */
5716 /* N - New CM line is created */
5717 ,NVL(inv_ctl.historical_flag,'N') historical_flag
5718 ,inv_ctl_memo.line_type memo_line_type
5719 FROM ra_customer_trx_lines inv_ctl,
5720 ra_customer_trx_lines cm_ctl,
5721 ra_customer_trx cm_ct,
5722 ar_net_revenue_amount nra,
5723 ar_memo_lines_b inv_ctl_memo
5724 WHERE cm_ct.customer_trx_id = p_customer_trx_id
5725 AND inv_ctl.customer_trx_id = cm_ct.previous_customer_trx_id
5726 AND nra.customer_trx_id = p_prev_customer_trx_id
5727 AND nra.customer_trx_line_id = inv_ctl.customer_trx_line_id
5728 AND nra.line_type = inv_ctl.line_type
5729 AND p_customer_trx_id = cm_ctl.customer_trx_id(+)
5730 AND nvl(p_customer_trx_line_id,
5731 -99) = decode(p_customer_trx_line_id,
5732 null, -99,
5733 cm_ctl.customer_trx_line_id)
5734 AND inv_ctl.link_to_cust_trx_line_id =
5735 cm_ctl.previous_customer_trx_line_id(+)
5736 AND decode(nra.line_type, 'CB', 'LINE',
5737 'CHARGES', 'LINE',
5738 nra.line_type) = p_line_type
5739 AND inv_ctl.memo_line_id = inv_ctl_memo.memo_line_id (+)
5740 ORDER BY inv_ctl.customer_trx_line_id
5741 ) a; --Bug 10169170
5742
5743
5744 l_rows_inserted := l_trx_line_array.customer_trx_line_id.COUNT;
5745
5746 /* Bug-3658284 -Start */
5747 IF (l_trx_line_array.customer_trx_line_id.COUNT = 0) Then
5748 IF PG_DEBUG in ('Y', 'C') THEN
5749 arp_util.debug( 'arp_ctl_pkg.insert_line_f_cm_ct_ctl_id : '||' 0 row Fetch ');
5750 END IF;
5751
5752 ELSE /*-- Bug 5098922 ---*/
5753
5754 FOR i IN l_trx_line_array.customer_trx_line_id.FIRST ..
5755 l_trx_line_array.customer_trx_line_id.LAST
5756 LOOP
5757
5758 /*-------------------------------------------------------------------------+
5759 | Maintain running total amounts for Revenue amounts and accounted amounts|
5760 +-------------------------------------------------------------------------*/
5761 l_amt_run_total := Nvl(l_amt_run_total,0)
5762 + l_trx_line_array.extended_amount(i);
5763
5764 /*-------------------------------------------------------------------------------+
5765 | Adjusting the rounding amount in the next line amount - revenue for cm line |
5766 | Eg. Line 1 -> 10 * 10/100 = 1 (rounded amount) |
5767 | Line 2 -> 20 * 10/100 = 2 (rounded amount) |
5768 | -> 2 - Line 1 (rounded amount) -> 2 - 1 -> 1 |
5769 | Line ... |
5770 +-------------------------------------------------------------------------------+ */
5771 l_trx_line_array.extended_amount(i) := arpcurr.CurrRound(l_amt_run_total
5772 ,p_currency_code) -Nvl(l_amt_prev_run_total,0);
5773 /*------------------------------------------------------------------------------+
5774 | Running total for previous line amount - Revenue in currency |
5775 +------------------------------------------------------------------------------*/
5776 l_amt_prev_run_total := Nvl(l_amt_prev_run_total,0)
5777 + l_trx_line_array.extended_amount(i);
5778
5779 If l_trx_line_array.line_type(i) = 'LINE' then
5780
5781 l_rev_run_total := Nvl(l_rev_run_total,0)
5782 + l_trx_line_array.revenue_amount(i);
5783 l_trx_line_array.revenue_amount(i) := arpcurr.CurrRound(l_rev_run_total
5784 ,p_currency_code) -Nvl(l_rev_prev_run_total,0);
5785 l_rev_prev_run_total := Nvl(l_rev_prev_run_total,0)
5786 + l_trx_line_array.revenue_amount(i);
5787
5788 /* 6882394 - Handle inclusive tax if it exists and if tax is
5789 being credited at this time. */
5790 IF l_trx_line_array.gross_extended_amount(i) IS NOT NULL AND
5791 p_tax_amount IS NOT NULL
5792 THEN
5793 IF PG_DEBUG = 'Y'
5794 THEN
5795 arp_util.debug('Inclusive tax, adjusting extended/revenue amounts');
5796 arp_util.debug('customer_trx_line_id = ' ||
5797 l_trx_line_array.customer_trx_line_id(i));
5798 arp_util.debug(' original extended_amount = ' ||
5799 l_trx_line_array.extended_amount(i));
5800 arp_util.debug(' original revenue_amount = ' ||
5801 l_trx_line_array.revenue_amount(i));
5802 END IF;
5803
5804 l_trx_line_array.extended_amount(i) :=
5805 arpcurr.currround(
5806 l_trx_line_array.gross_extended_amount(i) *
5807 (l_trx_line_array.extended_amount(i) /
5808 l_trx_line_array.original_extended_amount(i)), p_currency_code);
5809
5810 l_trx_line_array.revenue_amount(i) :=
5811 arpcurr.currround(
5812 l_trx_line_array.gross_extended_amount(i) *
5813 (l_trx_line_array.revenue_amount(i) /
5814 l_trx_line_array.original_revenue_amount(i)), p_currency_code);
5815
5816 IF PG_DEBUG = 'Y'
5817 THEN
5818 arp_util.debug(' new extended_amount = ' ||
5819 l_trx_line_array.extended_amount(i));
5820 arp_util.debug(' new revenue_amount = ' ||
5821 l_trx_line_array.revenue_amount(i));
5822 END IF;
5823
5824 END IF;
5825
5826 /*-----------------------------------------------------------------------------------+
5827 | Bug 6990227 : For 100% Credit Allocation, Invoice Quantity is copied to CM Quantity
5828 +------------------------------------------------------------------------------------*/
5829 If p_line_percent = 100 then
5830 l_trx_line_array.quantity_credited(i) :=(-1) *
5831 l_trx_line_array.quantity_invoiced(i);
5832 Else
5833 /*---------------------------------------------------------------+
5834 | Quantity_credited * Unit price is equal to extended_amount |
5835 +---------------------------------------------------------------*/
5836
5837
5838
5839 If Nvl(l_trx_line_array.unit_Selling_price(i),0) <> 0 then
5840 -- bug 9125212
5841 open c1(l_trx_line_array.previous_customer_trx_line_id(i));
5842 fetch c1 into l_quantity_invoiced ;
5843 close c1;
5844 -- The below logic is used to round off the Quantity_credited upto the
5845 -- decimal place for which the original inovice's quantity_invoiced was entered
5846 -- Bug 7389126 KALYAN
5847 l_round := (length(l_quantity_invoiced)-length(round(l_quantity_invoiced,0)))-1 ;
5848 if(l_round < 2 ) then
5849 l_round := 2;
5850 end if;
5851
5852 IF PG_DEBUG in ('Y', 'C') THEN
5853 arp_util.debug('l_trx_line_array.unit_Selling_price(i):' || l_trx_line_array.unit_Selling_price(i));
5854 arp_util.debug('length(l_quantity_invoiced):'||length(l_quantity_invoiced));
5855 arp_util.debug('length(round(l_quantity_invoiced,0)):'||length(round(l_quantity_invoiced,0)));
5856 arp_util.debug('l_trx_line_array.gross_unit_Selling_price(i):'||l_trx_line_array.gross_unit_Selling_price(i));
5857 arp_util.debug('l_trx_line_array.extended_amount(i):'||l_trx_line_array.extended_amount(i));
5858 arp_util.debug('l_round:'||l_round);
5859 END IF;
5860
5861
5862 If Nvl(l_trx_line_array.gross_unit_Selling_price(i),0) <> 0 then
5863 l_qnt_temp := (l_trx_line_array.extended_amount(i) /l_trx_line_array.gross_unit_Selling_price(i) );
5864 Else
5865 l_qnt_temp := (l_trx_line_array.extended_amount(i) /l_trx_line_array.unit_Selling_price(i) );
5866 End If;
5867
5868
5869 --bug 14679910, rounding the quantity to the nearest value
5870 IF PG_DEBUG in ('Y', 'C') THEN
5871 arp_util.debug('l_qnt_temp:'||l_qnt_temp);
5872 arp_util.debug('round(l_qnt_temp,l_round):'||round(l_qnt_temp,l_round));
5873 END IF;
5874 --Bug 16293794
5875 if(l_qnt_temp <> 0 AND round(l_qnt_temp,l_round) = 0 ) then
5876 l_trx_line_array.quantity_credited(i):= NVL(round(l_qnt_temp,length(l_qnt_temp)-length(REPLACE(l_qnt_temp, '0', ''))+1),
5877 round(l_qnt_temp,l_round));
5878
5879 IF PG_DEBUG in ('Y', 'C') THEN
5880 arp_util.debug('rounding off with precision');
5881 END IF;
5882
5883 else
5884 l_trx_line_array.quantity_credited(i) := round(l_qnt_temp,l_round);
5885 end if;
5886
5887 IF PG_DEBUG in ('Y', 'C') THEN
5888 arp_util.debug('l_trx_line_array.quantity_credited(i):'||l_trx_line_array.quantity_credited(i));
5889 END IF;
5890
5891 l_qnt_temp := 0;
5892
5893 End If;
5894
5895 End if;
5896 End if;
5897 End Loop;
5898
5899 END IF; /* -- Bug 5098922 ---*/
5900 /* Bug-3658284 - End */
5901 -- MVKOLLA - Added following debug messages as part of bug7113653
5902 IF PG_DEBUG in ('Y', 'C') THEN
5903 i := l_trx_line_array.customer_trx_line_id.FIRST;
5904 WHILE l_trx_line_array.customer_trx_line_id.EXISTS(i) LOOP
5905 arp_util.debug('l_trx_line_array.customer_trx_line_id(i) '||l_trx_line_array.customer_trx_line_id(i));
5906 arp_util.debug('l_trx_line_array.previous_customer_trx_line_id(i) '||l_trx_line_array.previous_customer_trx_line_id(i));
5907 arp_util.debug('l_trx_line_array.extended_amount(i) '||l_trx_line_array.extended_amount(i));
5908 arp_util.debug('l_trx_line_array.revenue_amount(i) '||l_trx_line_array.revenue_amount(i));
5909 i := l_trx_line_array.customer_trx_line_id.NEXT(i);
5910 END LOOP;
5911 END IF;
5912
5913 --{BUG#3339072
5914 IF l_rows_inserted <> 0 THEN
5915 FORALL indx IN l_trx_line_array.customer_trx_line_id.FIRST ..
5916 l_trx_line_array.customer_trx_line_id.LAST
5917 INSERT INTO ra_customer_trx_lines
5918 ( customer_trx_line_id,
5919 customer_trx_id,
5920 created_by,
5921 creation_date,
5922 last_updated_by,
5923 last_update_date,
5924 last_update_login,
5925 line_number,
5926 line_type,
5927 set_of_books_id,
5928 accounting_rule_id,
5929 autorule_complete_flag,
5930 last_period_to_credit,
5931 description,
5932 initial_customer_trx_line_id,
5933 inventory_item_id,
5934 item_exception_rate_id,
5935 memo_line_id,
5936 reason_code,
5937 previous_customer_trx_id,
5938 previous_customer_trx_line_id,
5939 link_to_cust_trx_line_id,
5940 unit_standard_price,
5941 unit_selling_price,
5942 gross_unit_selling_price, -- Bug 7389126 KALYAN
5943 quantity_credited,
5944 extended_amount,
5945 revenue_amount,
5946 sales_order,
5947 sales_order_date,
5948 sales_order_line,
5949 sales_order_revision,
5950 sales_order_source,
5951 tax_exemption_id,
5952 tax_precedence,
5953 tax_rate,
5954 uom_code,
5955 default_ussgl_transaction_code,
5956 default_ussgl_trx_code_context,
5957 sales_tax_id,
5958 location_segment_id,
5959 vat_tax_id,
5960 amount_includes_tax_flag,
5961 warehouse_id,
5962 taxable_amount,
5963 translated_description
5964 ,org_id
5965 ,ship_to_customer_id
5966 ,ship_to_address_id
5967 ,ship_to_site_use_id
5968 ,ship_to_contact_id
5969 ,tax_classification_code
5970 ,historical_flag
5971 ) VALUES
5972 ( l_trx_line_array.customer_trx_line_id(indx),
5973 l_trx_line_array.customer_trx_id(indx),
5974 l_trx_line_array.created_by(indx),
5975 l_trx_line_array.creation_date(indx),
5976 l_trx_line_array.last_updated_by(indx),
5977 l_trx_line_array.last_update_date(indx),
5978 l_trx_line_array.last_update_login(indx),
5979 l_trx_line_array.line_number(indx),
5980 l_trx_line_array.line_type(indx),
5981 l_trx_line_array.set_of_books_id(indx),
5982 l_trx_line_array.accounting_rule_id(indx),
5983 l_trx_line_array.autorule_complete_flag(indx),
5984 l_trx_line_array.last_period_to_credit(indx),
5985 l_trx_line_array.description(indx),
5986 l_trx_line_array.initial_customer_trx_line_id(indx),
5987 l_trx_line_array.inventory_item_id(indx),
5988 l_trx_line_array.item_exception_rate_id(indx),
5989 l_trx_line_array.memo_line_id(indx),
5990 l_trx_line_array.reason_code(indx),
5991 l_trx_line_array.previous_customer_trx_id(indx),
5992 l_trx_line_array.previous_customer_trx_line_id(indx),
5993 l_trx_line_array.link_to_cust_trx_line_id(indx),
5994 l_trx_line_array.unit_standard_price(indx),
5995 l_trx_line_array.unit_selling_price(indx),
5996 l_trx_line_array.gross_unit_selling_price(indx), -- Bug 7389126 KALYAN
5997 l_trx_line_array.quantity_credited(indx),
5998 l_trx_line_array.extended_amount(indx),
5999 l_trx_line_array.revenue_amount(indx),
6000 l_trx_line_array.sales_order(indx),
6001 l_trx_line_array.sales_order_date(indx),
6002 l_trx_line_array.sales_order_line(indx),
6003 l_trx_line_array.sales_order_revision(indx),
6004 l_trx_line_array.sales_order_source(indx),
6005 l_trx_line_array.tax_exemption_id(indx),
6006 l_trx_line_array.tax_precedence(indx),
6007 l_trx_line_array.tax_rate(indx),
6008 l_trx_line_array.uom_code(indx),
6009 l_trx_line_array.default_ussgl_transaction_code(indx),
6010 l_trx_line_array.default_ussgl_trx_code_context(indx),
6011 l_trx_line_array.sales_tax_id(indx),
6012 l_trx_line_array.location_segment_id(indx),
6013 l_trx_line_array.vat_tax_id(indx),
6014 l_trx_line_array.amount_includes_tax_flag(indx),
6015 l_trx_line_array.warehouse_id(indx),
6016 l_trx_line_array.taxable_amount(indx),
6017 l_trx_line_array.translated_description(indx),
6018 l_trx_line_array.org_id(indx),
6019 l_trx_line_array.ship_to_customer_id(indx),
6020 l_trx_line_array.ship_to_address_id(indx),
6021 l_trx_line_array.ship_to_site_use_id(indx),
6022 l_trx_line_array.ship_to_contact_id(indx),
6023 l_trx_line_array.tax_classification_code(indx),
6024 l_trx_line_array.historical_flag(indx));
6025
6026 i := l_trx_line_array.customer_trx_line_id.FIRST;
6027 WHILE l_trx_line_array.customer_trx_line_id.EXISTS(i) LOOP
6028 /* R12 eTax uptake */
6029
6030 /* 5402228 - clarified use of tax for line-only scenarios. There are
6031 two to be concerned with. They are:
6032
6033 1) inv or cm with memo_line of type TAX -
6034 INSERT_NO_TAX - LINE_INFO_TAX_ONLY
6035 2) inv or cm with no tax at all
6036 INSERT_NO_TAX_EVER - RECORD_WITH_NO_TAX
6037 */
6038
6039 IF p_line_type = 'LINE' THEN
6040 IF p_tax_amount IS NULL THEN --bug6778519
6041 IF NVL(l_trx_line_array.memo_line_type(i), 'XXX') = 'TAX'
6042 THEN
6043 l_mode := 'INSERT_NO_TAX';
6044 ELSE
6045 l_mode := 'INSERT_NO_TAX_EVER';
6046 END IF;
6047 ELSIF NVL(p_credit_amount,0) = 0 THEN
6048 l_mode := 'INSERT_NO_LINE';
6049 ELSE
6050 l_mode := 'INSERT';
6051 END IF;
6052
6053 /* Bug Number 6790882 */
6054
6055 IF PG_DEBUG in ('Y', 'C') THEN
6056 arp_util.debug( 'i = ' || i);
6057 arp_util.debug( 'l_rows_inserted = ' || l_rows_inserted);
6058 arp_util.debug( 'l_mode = ' || l_mode);
6059 END IF;
6060
6061 IF l_mode = 'INSERT_NO_LINE' THEN
6062 IF i = l_rows_inserted THEN
6063 l_tax_amount := p_tax_amount - l_total_tax_prorate;
6064 ELSE
6065 l_tax_amount := calculate_prorated_tax_amount(
6066 p_mode => l_mode,
6067 p_tax_amount => p_tax_amount,
6068 p_customer_trx_id => p_customer_trx_id,
6069 p_customer_trx_line_id =>
6070 l_trx_line_array.customer_trx_line_id(i));
6071
6072 l_total_tax_prorate := l_total_tax_prorate + l_tax_amount;
6073 END IF;
6074 ELSE
6075 l_tax_amount := p_tax_amount;
6076 END IF;
6077
6078 IF PG_DEBUG in ('Y', 'C') THEN
6079 arp_util.debug( 'l_total_tax_prorate = ' || l_total_tax_prorate);
6080 arp_util.debug( 'l_tax_amount = ' || l_tax_amount);
6081 END IF;
6082
6083 ARP_ETAX_SERVICES_PKG.line_det_factors(
6084 p_customer_trx_line_id => l_trx_line_array.customer_trx_line_id(i),
6085 p_customer_trx_id => p_customer_trx_id,
6086 p_mode => l_mode,
6087 p_tax_amount => l_tax_amount) ;
6088 END IF;
6089
6090 i := l_trx_line_array.customer_trx_line_id.NEXT(i);
6091 END LOOP;
6092
6093 END IF;
6094 --}
6095
6096
6097 IF PG_DEBUG in ('Y', 'C') THEN
6098 arp_util.debug( 'arp_ctl_pkg.insert_line_f_cm_ct_ctl_id : '||
6099 to_char(l_rows_inserted)||' rows inserted');
6100 END IF;
6101
6102 /* Bug 3658284 - Remove the entire rounding update statement */
6103
6104 IF PG_DEBUG in ('Y', 'C') THEN
6105 arp_util.debug( 'arp_ctl_pkg.insert_line_f_cm_ct_ctl_id()-');
6106 END IF;
6107
6108 EXCEPTION
6109 WHEN NO_DATA_FOUND THEN
6110 return;
6111 WHEN OTHERS THEN
6112 IF PG_DEBUG in ('Y', 'C') THEN
6113 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.insert_line_f_cm_ct_ctl_id');
6114 arp_util.debug( '');
6115 arp_util.debug( 'p_customer_trx_id = '||p_customer_trx_id);
6116 arp_util.debug( 'p_customer_trx_line_id = '||p_customer_trx_line_id);
6117 arp_util.debug( 'p_prev_customer_trx_id = '||p_prev_customer_trx_id);
6118 arp_util.debug( 'p_line_type = '||p_line_type);
6119 arp_util.debug( 'p_uncredited_amount = '||p_uncredited_amount);
6120 arp_util.debug( 'p_credit_amount = '||p_credit_amount);
6121 arp_util.debug( 'p_currency_code = '||p_currency_code);
6122 END IF;
6123 RAISE;
6124
6125 END;
6126
6127
6128 /*===========================================================================+
6129 | FUNCTION |
6130 | update_line_f_cm_ctl_id |
6131 | |
6132 | DESCRIPTION |
6133 | This procedure updates credit memo lines for the specified line type |
6134 | |
6135 | SCOPE - PUBLIC |
6136 | |
6137 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
6138 | arp_util.debug |
6139 | |
6140 | ARGUMENTS : IN: |
6141 | None |
6142 | OUT: |
6143 | None |
6144 | |
6145 | RETURNS : NONE |
6146 | |
6147 | NOTES |
6148 | |
6149 | MODIFICATION HISTORY |
6150 | 29-AUG-95 Subash Chadalavada Created |
6151 | |
6152 +===========================================================================*/
6153 PROCEDURE update_line_f_cm_ctl_id(
6154 p_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
6155 p_customer_trx_line_id IN ra_customer_trx_lines.customer_trx_line_id%type,
6156 p_prev_customer_trx_id IN ra_customer_trx.customer_trx_id%type,
6157 p_line_type IN ra_customer_trx_lines.line_type%type,
6158 p_uncredited_amount IN ra_customer_trx_lines.extended_amount%type,
6159 p_credit_amount IN ra_customer_trx_lines.extended_amount%type,
6160 p_currency_code IN fnd_currencies.currency_code%type)
6161 IS
6162 l_rows_updated number;
6163 BEGIN
6164
6165 IF PG_DEBUG in ('Y', 'C') THEN
6166 arp_util.debug( 'arp_ctl_pkg.update_line_f_cm_ctl_id()+');
6167 END IF;
6168
6169 UPDATE ra_customer_trx_lines ctl
6170 SET extended_amount =
6171 (SELECT ( (nra.net_amount -
6172 decode(cm_ct.complete_flag,
6173 'Y', nvl(ctl.extended_amount, 0),
6174 0)) /
6175 decode(p_uncredited_amount,
6176 0, 1,
6177 p_uncredited_amount
6178 ) * nvl(p_credit_amount, 0)
6179 ) /* extended_amount */
6180 FROM ar_net_revenue_amount nra,
6181 ra_customer_trx cm_ct
6182 WHERE nra.customer_trx_id = p_prev_customer_trx_id
6183 AND nra.customer_trx_line_id =
6184 ctl.previous_customer_trx_line_id
6185 AND cm_ct.customer_trx_id = p_customer_trx_id)
6186 WHERE ctl.customer_trx_id = p_customer_trx_id
6187 AND ctl.link_to_cust_trx_line_id = p_customer_trx_line_id
6188 AND ctl.line_type = p_line_type;
6189
6190 IF PG_DEBUG in ('Y', 'C') THEN
6191 arp_util.debug( 'arp_ctl_pkg.update_line_f_cm_ctl_id : '||
6192 'Extended Amount :'|| SQL%ROWCOUNT||' rows updated');
6193 END IF;
6194
6195 UPDATE ra_customer_trx_lines ctl
6196 SET extended_amount = arpcurr.CurrRound (extended_amount,
6197 p_currency_code)
6198 WHERE ctl.customer_trx_id = p_customer_trx_id
6199 AND ctl.link_to_cust_trx_line_id = p_customer_trx_line_id
6200 AND ctl.line_type = p_line_type;
6201
6202 IF PG_DEBUG in ('Y', 'C') THEN
6203 arp_util.debug( 'arp_ctl_pkg.update_line_f_cm_ctl_id : '||
6204 'Currency Rounding :'|| SQL%ROWCOUNT||' rows updated');
6205 END IF;
6206
6207 /*----------------------------+
6208 | correct rounding errors |
6209 +----------------------------*/
6210
6211 IF (SQL%ROWCOUNT > 0)
6212 THEN
6213 UPDATE ra_customer_trx_lines l
6214 SET extended_amount =
6215 (SELECT l.extended_amount +
6216 (p_credit_amount - sum(l2.extended_amount))
6217 FROM ra_customer_trx_lines l2
6218 WHERE l2.customer_trx_id = l.customer_trx_id
6219 AND l2.link_to_cust_trx_line_id = p_customer_trx_line_id
6220 AND l2.line_type = p_line_type)
6221 WHERE l.customer_trx_id = p_customer_trx_id
6222 AND l.line_type = p_line_type
6223 AND l.customer_trx_line_id =
6224 (SELECT min(customer_trx_line_id)
6225 FROM ra_customer_trx_lines l3
6226 WHERE l3.customer_trx_id = p_customer_trx_id
6227 AND l3.link_to_cust_trx_line_id = p_customer_trx_line_id
6228 AND l3.line_type = p_line_type
6229 AND l3.extended_amount <> 0
6230 HAVING SUM(l3.extended_amount) <> p_credit_amount);
6231
6232 END IF;
6233
6234 IF PG_DEBUG in ('Y', 'C') THEN
6235 arp_util.debug( 'arp_ctl_pkg.update_line_f_cm_ctl_id()-');
6236 END IF;
6237
6238 EXCEPTION
6239 WHEN NO_DATA_FOUND THEN
6240 return;
6241 WHEN OTHERS THEN
6242 IF PG_DEBUG in ('Y', 'C') THEN
6243 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.update_line_f_cm_ctl_id');
6244 arp_util.debug( '');
6245 arp_util.debug( 'p_customer_trx_id = '||p_customer_trx_id);
6246 arp_util.debug( 'p_customer_trx_line_id = '||p_customer_trx_line_id);
6247 arp_util.debug( 'p_prev_customer_trx_id = '||p_prev_customer_trx_id);
6248 arp_util.debug( 'p_line_type = '||p_line_type);
6249 arp_util.debug( 'p_uncredited_amount = '||p_uncredited_amount);
6250 arp_util.debug( 'p_credit_amount = '||p_credit_amount);
6251 arp_util.debug( 'p_currency_code = '||p_currency_code);
6252 END IF;
6253 RAISE;
6254
6255 END;
6256
6257
6258 /*===========================================================================+
6259 | FUNCTION |
6260 | get_text_dummy |
6261 | |
6262 | DESCRIPTION |
6263 | This function returns the value of the AR_TEXT_DUMMY constant. |
6264 | |
6265 | SCOPE - PUBLIC |
6266 | |
6267 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
6268 | arp_util.debug |
6269 | |
6270 | ARGUMENTS : IN: |
6271 | None |
6272 | OUT: |
6273 | None |
6274 | |
6275 | RETURNS : value of AR_TEXT_DUMMY |
6276 | |
6277 | NOTES |
6278 | |
6279 | MODIFICATION HISTORY |
6280 | 09-AUG-95 Martin Johnson Created |
6281 | |
6282 +===========================================================================*/
6283
6284 FUNCTION get_text_dummy(p_null IN NUMBER DEFAULT null) RETURN varchar2 IS
6285
6286 BEGIN
6287
6288 --- arp_util.debug('arp_ctl_pkg.get_text_dummy()+');
6289
6290 --- arp_util.debug('arp_ctl_pkg.get_text_dummy()-');
6291
6292 return(AR_TEXT_DUMMY);
6293
6294 EXCEPTION
6295 WHEN OTHERS THEN
6296 IF PG_DEBUG in ('Y', 'C') THEN
6297 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.get_text_dummy()');
6298 END IF;
6299 RAISE;
6300
6301 END;
6302
6303 /*===========================================================================+
6304 | FUNCTION |
6305 | get_number_dummy |
6306 | |
6307 | DESCRIPTION |
6308 | This function returns the value of the AR_NUMBER DUMMY constant. |
6309 | |
6310 | SCOPE - PUBLIC |
6311 | |
6312 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
6313 | arp_util.debug |
6314 | |
6315 | ARGUMENTS : IN: |
6316 | None |
6317 | OUT: |
6318 | None |
6319 | |
6320 | RETURNS : value of AR_NUMBER_DUMMY |
6321 | |
6322 | NOTES |
6323 | |
6324 | MODIFICATION HISTORY |
6325 | 09-AUG-95 Martin Johnson Created |
6326 | |
6327 +===========================================================================*/
6328
6329 FUNCTION get_number_dummy(p_null IN NUMBER DEFAULT null) RETURN number IS
6330
6331 BEGIN
6332
6333 --- arp_util.debug('arp_ctl_pkg.get_number_dummy()+');
6334
6335 --- arp_util.debug('arp_ctl_pkg.get_number_dummy()-');
6336
6337 return(AR_NUMBER_DUMMY);
6338
6339 EXCEPTION
6340 WHEN OTHERS THEN
6341 IF PG_DEBUG in ('Y', 'C') THEN
6342 arp_util.debug( 'EXCEPTION: arp_ctl_pkg.get_number_dummy()');
6343 END IF;
6344 RAISE;
6345
6346 END;
6347
6348 /*---------------------------------------------+
6349 | Package initialization section. |
6350 | Sets WHO column variables for later use. |
6351 +---------------------------------------------*/
6352
6353 BEGIN
6354
6355 pg_user_id := fnd_global.user_id;
6356 pg_conc_login_id := fnd_global.conc_login_id;
6357 pg_login_id := fnd_global.login_id;
6358 pg_prog_appl_id := fnd_global.prog_appl_id;
6359 pg_conc_program_id := fnd_global.conc_program_id;
6360
6361
6362 END ARP_CTL_PKG;