[Home] [Help]
1: PACKAGE BODY arp_maintain_ps2 AS
2: /* $Header: ARTEMP2B.pls 120.33 2011/05/26 09:13:20 jiachi ship $ */
3: PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
4:
5: /* =======================================================================
319: l_amount NUMBER;
320:
321:
322: BEGIN
323: print_fcn_label2('arp_maintain_ps2.distribute_amount()+' );
324:
325: DEBUG( ' Total = ' || p_total, MSG_LEVEL_DEBUG);
326:
327: FOR i IN 0..p_count - 1 LOOP
350:
351: END LOOP;
352:
353:
354: print_fcn_label2('arp_maintain_ps2.distribute_amount()-' );
355:
356: EXCEPTION
357: WHEN OTHERS THEN
358: debug( 'EXCEPTION: arp_maintain_ps2.distribute_amount()',
354: print_fcn_label2('arp_maintain_ps2.distribute_amount()-' );
355:
356: EXCEPTION
357: WHEN OTHERS THEN
358: debug( 'EXCEPTION: arp_maintain_ps2.distribute_amount()',
359: MSG_LEVEL_BASIC );
360: RAISE;
361: END distribute_amount;
362:
379: l_amount NUMBER;
380:
381:
382: BEGIN
383: print_fcn_label2('arp_maintain_ps2.compute_acctd_amount()+' );
384:
385:
386: FOR i IN 0..p_count - 1 LOOP
387:
410:
411: END LOOP;
412:
413:
414: print_fcn_label2('arp_maintain_ps2.compute_acctd_amount()-' );
415:
416: EXCEPTION
417: WHEN OTHERS THEN
418: debug( 'EXCEPTION: arp_maintain_ps2.compute_acctd_amount()',
414: print_fcn_label2('arp_maintain_ps2.compute_acctd_amount()-' );
415:
416: EXCEPTION
417: WHEN OTHERS THEN
418: debug( 'EXCEPTION: arp_maintain_ps2.compute_acctd_amount()',
419: MSG_LEVEL_BASIC );
420: RAISE;
421: END compute_acctd_amount;
422:
456:
457:
458: BEGIN
459:
460: print_fcn_label( 'arp_maintain_ps2.build_ips_sql()+' );
461:
462: ------------------------------------------------
463: -- Select sql
464: ------------------------------------------------
718: debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
719: RAISE;
720: END;
721:
722: print_fcn_label( 'arp_maintain_ps2.build_ips_sql()-' );
723:
724:
725: EXCEPTION
726: WHEN OTHERS THEN
723:
724:
725: EXCEPTION
726: WHEN OTHERS THEN
727: debug( 'EXCEPTION: arp_maintain_ps2.build_ips_sql()',
728: MSG_LEVEL_BASIC );
729:
730: RAISE;
731: END build_ips_sql;
737: p_select_rec IN select_ips_rec_type ) IS
738:
739: BEGIN
740:
741: print_fcn_label2( 'arp_maintain_ps2.define_ips_select_columns()+' );
742:
743: dbms_sql.define_column( p_select_c, 1, p_select_rec.customer_trx_id );
744: dbms_sql.define_column( p_select_c, 2, p_select_rec.total_line_amount );
745: dbms_sql.define_column( p_select_c, 3, p_select_rec.total_tax_amount );
772: p_select_rec.total_charges_amount );
773: dbms_sql.define_column( p_select_c, 25,p_select_rec.reversed_cash_receipt_id); /*Bug3328690 */
774:
775:
776: print_fcn_label2( 'arp_maintain_ps2.define_ips_select_columns()-' );
777:
778: EXCEPTION
779: WHEN OTHERS THEN
780: debug('EXCEPTION: arp_maintain_ps2.define_ips_select_columns()',
776: print_fcn_label2( 'arp_maintain_ps2.define_ips_select_columns()-' );
777:
778: EXCEPTION
779: WHEN OTHERS THEN
780: debug('EXCEPTION: arp_maintain_ps2.define_ips_select_columns()',
781: MSG_LEVEL_BASIC);
782: RAISE;
783: END define_ips_select_columns;
784:
787: PROCEDURE get_ips_column_values( p_select_c IN INTEGER,
788: p_select_rec IN OUT NOCOPY select_ips_rec_type ) IS
789: /* Bug 460927 - Modified IN to IN OUT in the above line - Oracle 8 */
790: BEGIN
791: print_fcn_label2( 'arp_maintain_ps2.get_ips_column_values()+' );
792:
793: dbms_sql.column_value( p_select_c, 1, p_select_rec.customer_trx_id );
794: dbms_sql.column_value( p_select_c, 2, p_select_rec.total_line_amount );
795: dbms_sql.column_value( p_select_c, 3, p_select_rec.total_tax_amount );
820: dbms_sql.column_value( p_select_c, 23, p_select_rec.rec_acctd_amount );
821: dbms_sql.column_value( p_select_c, 24, p_select_rec.total_charges_amount );
822: dbms_sql.column_value( p_select_c, 25, p_select_rec.reversed_cash_receipt_id); /*Bug3328690 */
823:
824: print_fcn_label2( 'arp_maintain_ps2.get_ips_column_values()-' );
825: EXCEPTION
826: WHEN OTHERS THEN
827: debug('EXCEPTION: arp_maintain_ps2.get_ips_column_values()',
828: MSG_LEVEL_BASIC);
823:
824: print_fcn_label2( 'arp_maintain_ps2.get_ips_column_values()-' );
825: EXCEPTION
826: WHEN OTHERS THEN
827: debug('EXCEPTION: arp_maintain_ps2.get_ips_column_values()',
828: MSG_LEVEL_BASIC);
829: RAISE;
830: END get_ips_column_values;
831:
853: ----------------------------------------------------------------------------
854: PROCEDURE dump_ips_select_rec( p_select_rec IN select_ips_rec_type ) IS
855: BEGIN
856:
857: print_fcn_label2( 'arp_maintain_ps2.dump_ips_select_rec()+' );
858:
859: debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
860: debug( ' customer_trx_id='
861: || p_select_rec.customer_trx_id, MSG_LEVEL_DEBUG );
907: || p_select_rec.percent, MSG_LEVEL_DEBUG );
908: debug( ' due_date='
909: || p_select_rec.due_date, MSG_LEVEL_DEBUG );
910:
911: print_fcn_label2( 'arp_maintain_ps2.dump_ips_select_rec()-' );
912:
913: EXCEPTION
914: WHEN OTHERS THEN
915: debug( 'EXCEPTION: arp_maintain_ps2.dump_ips_select_rec()',
911: print_fcn_label2( 'arp_maintain_ps2.dump_ips_select_rec()-' );
912:
913: EXCEPTION
914: WHEN OTHERS THEN
915: debug( 'EXCEPTION: arp_maintain_ps2.dump_ips_select_rec()',
916: MSG_LEVEL_BASIC );
917: RAISE;
918: END dump_ips_select_rec;
919:
945: sqlstmt VARCHAR2(254);
946: l_return_value_jl NUMBER;
947: /* BR Sped Project */
948: BEGIN
949: print_fcn_label2('arp_maintain_ps2.process_ips_data()+' );
950:
951:
952: --------------------------------------------------------------------
953: -- Distribute line amount
1223:
1224: END LOOP;
1225:
1226:
1227: print_fcn_label2('arp_maintain_ps2.process_ips_data()-' );
1228:
1229: EXCEPTION
1230: WHEN OTHERS THEN
1231: debug( 'EXCEPTION: arp_maintain_ps2.process_ips_data()',
1227: print_fcn_label2('arp_maintain_ps2.process_ips_data()-' );
1228:
1229: EXCEPTION
1230: WHEN OTHERS THEN
1231: debug( 'EXCEPTION: arp_maintain_ps2.process_ips_data()',
1232: MSG_LEVEL_BASIC );
1233: RAISE;
1234: END process_ips_data;
1235:
1262:
1263: PROCEDURE load_tables( p_select_rec IN select_ips_rec_type ) IS
1264:
1265: BEGIN
1266: print_fcn_label2('arp_maintain_ps2.load_tables()+' );
1267:
1268: l_terms_sequence_num_t( l_table_index ) :=
1269: p_select_rec.term_sequence_num;
1270: l_percent_t( l_table_index ) := p_select_rec.percent;
1270: l_percent_t( l_table_index ) := p_select_rec.percent;
1271: l_due_date_t( l_table_index ) := p_select_rec.due_date;
1272: l_table_index := l_table_index + 1;
1273:
1274: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
1275:
1276: EXCEPTION
1277: WHEN OTHERS THEN
1278: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
1274: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
1275:
1276: EXCEPTION
1277: WHEN OTHERS THEN
1278: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
1279: MSG_LEVEL_BASIC );
1280: RAISE;
1281: END load_tables;
1282:
1283:
1284: PROCEDURE clear_tables IS
1285:
1286: BEGIN
1287: print_fcn_label2('arp_maintain_ps2.clear_tables()+' );
1288:
1289:
1290: l_line_amount_t := null_number_t;
1291: l_tax_amount_t := null_number_t;
1298: l_due_date_t := null_date_t;
1299:
1300: l_table_index := 0;
1301:
1302: print_fcn_label2('arp_maintain_ps2.clear_tables()-' );
1303:
1304: EXCEPTION
1305: WHEN OTHERS THEN
1306: debug( 'EXCEPTION: arp_maintain_ps2.clear_tables()',
1302: print_fcn_label2('arp_maintain_ps2.clear_tables()-' );
1303:
1304: EXCEPTION
1305: WHEN OTHERS THEN
1306: debug( 'EXCEPTION: arp_maintain_ps2.clear_tables()',
1307: MSG_LEVEL_BASIC );
1308: RAISE;
1309: END clear_tables;
1310:
1309: END clear_tables;
1310:
1311: BEGIN
1312:
1313: print_fcn_label( 'arp_maintain_ps2.insert_inv_ps_private()+' );
1314:
1315: --
1316: clear_tables;
1317: --
1438: RAISE;
1439:
1440: END;
1441:
1442: print_fcn_label( 'arp_maintain_ps2.insert_inv_ps_private()-' );
1443:
1444: EXCEPTION
1445: WHEN OTHERS THEN
1446: debug( 'EXCEPTION: arp_maintain_ps2.insert_inv_ps_private()',
1442: print_fcn_label( 'arp_maintain_ps2.insert_inv_ps_private()-' );
1443:
1444: EXCEPTION
1445: WHEN OTHERS THEN
1446: debug( 'EXCEPTION: arp_maintain_ps2.insert_inv_ps_private()',
1447: MSG_LEVEL_BASIC );
1448: RAISE;
1449:
1450: END insert_inv_ps_private;
1491:
1492:
1493: BEGIN
1494:
1495: print_fcn_label( 'arp_maintain_ps2.build_ira_sql()+' );
1496:
1497: ------------------------------------------------
1498: -- Select sql
1499: ------------------------------------------------
1920: debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
1921: RAISE;
1922: END;
1923:
1924: print_fcn_label( 'arp_maintain_ps2.build_ira_sql()-' );
1925:
1926:
1927: EXCEPTION
1928: WHEN OTHERS THEN
1925:
1926:
1927: EXCEPTION
1928: WHEN OTHERS THEN
1929: debug( 'EXCEPTION: arp_maintain_ps2.build_ira_sql()',
1930: MSG_LEVEL_BASIC );
1931:
1932: RAISE;
1933: END build_ira_sql;
1939: p_select_rec IN select_ira_rec_type ) IS
1940:
1941: BEGIN
1942:
1943: print_fcn_label2( 'arp_maintain_ps2.define_ira_select_columns()+' );
1944:
1945: dbms_sql.define_column( p_select_c, 1, p_select_rec.trx_date );
1946: dbms_sql.define_column( p_select_c, 2, p_select_rec.customer_id );
1947: dbms_sql.define_column( p_select_c, 3, p_select_rec.cust_trx_type_id );
1991: dbms_sql.define_column( p_select_c, 35,
1992: p_select_rec.inv_charges_remaining );
1993:
1994:
1995: print_fcn_label2( 'arp_maintain_ps2.define_ira_select_columns()-' );
1996:
1997: EXCEPTION
1998: WHEN OTHERS THEN
1999: debug('EXCEPTION: arp_maintain_ps2.define_ira_select_columns()',
1995: print_fcn_label2( 'arp_maintain_ps2.define_ira_select_columns()-' );
1996:
1997: EXCEPTION
1998: WHEN OTHERS THEN
1999: debug('EXCEPTION: arp_maintain_ps2.define_ira_select_columns()',
2000: MSG_LEVEL_BASIC);
2001: RAISE;
2002: END define_ira_select_columns;
2003:
2006: PROCEDURE get_ira_column_values( p_select_c IN INTEGER,
2007: p_select_rec IN OUT NOCOPY select_ira_rec_type ) IS
2008: /* Bug 460927 - Modified IN to IN OUT oin the above line - oracle 8 */
2009: BEGIN
2010: print_fcn_label2( 'arp_maintain_ps2.get_ira_column_values()+' );
2011:
2012: dbms_sql.column_value( p_select_c, 1, p_select_rec.trx_date );
2013: dbms_sql.column_value( p_select_c, 2, p_select_rec.customer_id );
2014: dbms_sql.column_value( p_select_c, 3, p_select_rec.cust_trx_type_id );
2058: dbms_sql.column_value( p_select_c, 35,
2059: p_select_rec.inv_charges_remaining );
2060:
2061:
2062: print_fcn_label2( 'arp_maintain_ps2.get_ira_column_values()-' );
2063: EXCEPTION
2064: WHEN OTHERS THEN
2065: debug('EXCEPTION: arp_maintain_ps2.get_ira_column_values()',
2066: MSG_LEVEL_BASIC);
2061:
2062: print_fcn_label2( 'arp_maintain_ps2.get_ira_column_values()-' );
2063: EXCEPTION
2064: WHEN OTHERS THEN
2065: debug('EXCEPTION: arp_maintain_ps2.get_ira_column_values()',
2066: MSG_LEVEL_BASIC);
2067: RAISE;
2068: END get_ira_column_values;
2069:
2091: ----------------------------------------------------------------------------
2092: PROCEDURE dump_ira_select_rec( p_select_rec IN select_ira_rec_type ) IS
2093: BEGIN
2094:
2095: print_fcn_label2( 'arp_maintain_ps2.dump_ira_select_rec()+' );
2096:
2097: debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
2098: debug( ' customer_trx_id='
2099: || p_select_rec.customer_trx_id, MSG_LEVEL_DEBUG );
2165: || p_select_rec.inv_charges_remaining, MSG_LEVEL_DEBUG );
2166: debug( ' inv_amount_credited='
2167: || p_select_rec.inv_amount_credited, MSG_LEVEL_DEBUG );
2168:
2169: print_fcn_label2( 'arp_maintain_ps2.dump_ira_select_rec()-' );
2170:
2171: EXCEPTION
2172: WHEN OTHERS THEN
2173: debug( 'EXCEPTION: arp_maintain_ps2.dump_ira_select_rec()',
2169: print_fcn_label2( 'arp_maintain_ps2.dump_ira_select_rec()-' );
2170:
2171: EXCEPTION
2172: WHEN OTHERS THEN
2173: debug( 'EXCEPTION: arp_maintain_ps2.dump_ira_select_rec()',
2174: MSG_LEVEL_BASIC );
2175: RAISE;
2176: END dump_ira_select_rec;
2177:
2196: l_index NUMBER;
2197: l_high_index NUMBER;
2198:
2199: BEGIN
2200: print_fcn_label2('arp_maintain_ps2.distribute_fifo_lifo()+' );
2201:
2202: debug( ' p_direction='||p_direction, MSG_LEVEL_DEBUG );
2203: debug( ' p_start_index='||p_start_index, MSG_LEVEL_DEBUG );
2204: debug( ' p_end_index='||p_end_index, MSG_LEVEL_DEBUG );
2295:
2296: END IF;
2297:
2298:
2299: print_fcn_label2('arp_maintain_ps2.distribute_fifo_lifo()-' );
2300:
2301: EXCEPTION
2302: WHEN OTHERS THEN
2303: debug( 'EXCEPTION: arp_maintain_ps2.distribute_fifo_lifo()',
2299: print_fcn_label2('arp_maintain_ps2.distribute_fifo_lifo()-' );
2300:
2301: EXCEPTION
2302: WHEN OTHERS THEN
2303: debug( 'EXCEPTION: arp_maintain_ps2.distribute_fifo_lifo()',
2304: MSG_LEVEL_BASIC );
2305: RAISE;
2306:
2307: END distribute_fifo_lifo;
2382: END compute_percents;
2383: -----------------------------------------------------------------------
2384:
2385: BEGIN
2386: print_fcn_label2('arp_maintain_ps2.distribute_prorate()+' );
2387:
2388:
2389: debug( ' p_cm_amount='||p_cm_amount, MSG_LEVEL_DEBUG );
2390:
2447: p_inv_rem_t( i ) := p_inv_rem_t( i ) + p_cm_applied_amt_t( i );
2448: END LOOP;
2449:
2450:
2451: print_fcn_label2('arp_maintain_ps2.distribute_prorate()-' );
2452:
2453: EXCEPTION
2454: WHEN OTHERS THEN
2455: debug( 'EXCEPTION: arp_maintain_ps2.distribute_prorate()',
2451: print_fcn_label2('arp_maintain_ps2.distribute_prorate()-' );
2452:
2453: EXCEPTION
2454: WHEN OTHERS THEN
2455: debug( 'EXCEPTION: arp_maintain_ps2.distribute_prorate()',
2456: MSG_LEVEL_BASIC );
2457: RAISE;
2458:
2459: END distribute_prorate;
2509:
2510: l_ussgl_transaction_code ar_receivable_applications.ussgl_transaction_code%type;
2511:
2512: BEGIN
2513: print_fcn_label2('arp_maintain_ps2.process_ira_data()+' );
2514:
2515: debug( ' checking credit method', MSG_LEVEL_DEBUG );
2516:
2517: IF( p_select_rec.credit_method in (FIFO, LIFO) ) THEN
3128: END LOOP;
3129:
3130:
3131:
3132: print_fcn_label2('arp_maintain_ps2.process_ira_data()-' );
3133:
3134: EXCEPTION
3135: WHEN OTHERS THEN
3136: debug( 'EXCEPTION: arp_maintain_ps2.process_ira_data()',
3132: print_fcn_label2('arp_maintain_ps2.process_ira_data()-' );
3133:
3134: EXCEPTION
3135: WHEN OTHERS THEN
3136: debug( 'EXCEPTION: arp_maintain_ps2.process_ira_data()',
3137: MSG_LEVEL_BASIC );
3138: RAISE;
3139: END process_ira_data;
3140:
3183: -----------------------------------------------------------------------
3184: PROCEDURE load_tables( p_select_rec IN select_ira_rec_type ) IS
3185:
3186: BEGIN
3187: print_fcn_label2('arp_maintain_ps2.load_tables()+' );
3188:
3189: l_inv_ps_id_t( l_table_index ) :=
3190: p_select_rec.inv_payment_schedule_id;
3191: l_inv_amount_due_rem_t( l_table_index ) :=
3204: p_select_rec.inv_amount_credited;
3205:
3206: l_table_index := l_table_index + 1;
3207:
3208: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
3209:
3210: EXCEPTION
3211: WHEN OTHERS THEN
3212: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
3208: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
3209:
3210: EXCEPTION
3211: WHEN OTHERS THEN
3212: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
3213: MSG_LEVEL_BASIC );
3214: RAISE;
3215: END load_tables;
3216:
3218: -----------------------------------------------------------------------
3219: PROCEDURE clear_cm_tables IS
3220:
3221: BEGIN
3222: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()+' );
3223:
3224: l_line_applied_t := null_number_t;
3225: l_tax_applied_t := null_number_t;
3226: l_freight_applied_t := null_number_t;
3227: l_charges_applied_t := null_number_t;
3228: l_acctd_amt_applied_from_t := null_number_t;
3229: l_acctd_amt_applied_to_t := null_number_t;
3230:
3231: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
3232:
3233: EXCEPTION
3234: WHEN OTHERS THEN
3235: debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
3231: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
3232:
3233: EXCEPTION
3234: WHEN OTHERS THEN
3235: debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
3236: MSG_LEVEL_BASIC );
3237: RAISE;
3238: END clear_cm_tables;
3239:
3241: -----------------------------------------------------------------------
3242: PROCEDURE clear_all_tables IS
3243:
3244: BEGIN
3245: print_fcn_label2('arp_maintain_ps2.clear_all_tables()+' );
3246:
3247: l_inv_ps_id_t := null_id_t;
3248: l_inv_amount_due_rem_t := null_number_t;
3249: l_inv_acctd_amt_due_rem_t := null_number_t;
3256: clear_cm_tables;
3257:
3258: l_table_index := 0;
3259:
3260: print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
3261:
3262: EXCEPTION
3263: WHEN OTHERS THEN
3264: debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
3260: print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
3261:
3262: EXCEPTION
3263: WHEN OTHERS THEN
3264: debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
3265: MSG_LEVEL_BASIC );
3266: RAISE;
3267: END clear_all_tables;
3268:
3294:
3295:
3296: BEGIN
3297:
3298: print_fcn_label( 'arp_maintain_ps2.insert_cm_ps_private()+' );
3299:
3300: --
3301: clear_all_tables;
3302: --
3486: RAISE;
3487:
3488: END;
3489:
3490: print_fcn_label( 'arp_maintain_ps2.insert_cm_ps_private()-' );
3491:
3492: EXCEPTION
3493: WHEN OTHERS THEN
3494: debug( 'EXCEPTION: arp_maintain_ps2.insert_cm_ps_private()',
3490: print_fcn_label( 'arp_maintain_ps2.insert_cm_ps_private()-' );
3491:
3492: EXCEPTION
3493: WHEN OTHERS THEN
3494: debug( 'EXCEPTION: arp_maintain_ps2.insert_cm_ps_private()',
3495: MSG_LEVEL_BASIC );
3496: RAISE;
3497:
3498: END insert_cm_ps_private;
3539:
3540:
3541: BEGIN
3542:
3543: print_fcn_label( 'arp_maintain_ps2.build_ups_sql()+' );
3544:
3545: ------------------------------------------------
3546: -- Select sql
3547: ------------------------------------------------
3857: debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
3858: RAISE;
3859: END;
3860:
3861: print_fcn_label( 'arp_maintain_ps2.build_ups_sql()-' );
3862:
3863:
3864: EXCEPTION
3865: WHEN OTHERS THEN
3862:
3863:
3864: EXCEPTION
3865: WHEN OTHERS THEN
3866: debug( 'EXCEPTION: arp_maintain_ps2.build_ups_sql()',
3867: MSG_LEVEL_BASIC );
3868:
3869: RAISE;
3870: END build_ups_sql;
3876: p_select_rec IN select_ups_rec_type ) IS
3877:
3878: BEGIN
3879:
3880: print_fcn_label2( 'arp_maintain_ps2.define_ups_select_columns()+' );
3881:
3882: dbms_sql.define_column( p_select_c, 1, p_select_rec.set_of_books_id );
3883: dbms_sql.define_column( p_select_c, 2, p_select_rec.total_line_amount );
3884: dbms_sql.define_column( p_select_c, 3, p_select_rec.gl_date );
3920: dbms_sql.define_column( p_select_c, 29, p_select_rec.freight_remaining );
3921: dbms_sql.define_column( p_select_c, 30, p_select_rec.total_tax_amount );
3922: dbms_sql.define_column( p_select_c, 31, p_select_rec.total_freight_amount );
3923:
3924: print_fcn_label2( 'arp_maintain_ps2.define_ups_select_columns()-' );
3925:
3926: EXCEPTION
3927: WHEN OTHERS THEN
3928: debug('EXCEPTION: arp_maintain_ps2.define_ups_select_columns()',
3924: print_fcn_label2( 'arp_maintain_ps2.define_ups_select_columns()-' );
3925:
3926: EXCEPTION
3927: WHEN OTHERS THEN
3928: debug('EXCEPTION: arp_maintain_ps2.define_ups_select_columns()',
3929: MSG_LEVEL_BASIC);
3930: RAISE;
3931: END define_ups_select_columns;
3932:
3935: PROCEDURE get_ups_column_values( p_select_c IN INTEGER,
3936: p_select_rec IN OUT NOCOPY select_ups_rec_type ) IS
3937: /* Bug 460927 - Changed IN to IN OUT in the above line - Oracle 8 */
3938: BEGIN
3939: print_fcn_label2( 'arp_maintain_ps2.get_ups_column_values()+' );
3940:
3941: dbms_sql.column_value( p_select_c, 1, p_select_rec.set_of_books_id );
3942: dbms_sql.column_value( p_select_c, 2, p_select_rec.total_line_amount );
3943: dbms_sql.column_value( p_select_c, 3, p_select_rec.gl_date );
3972: dbms_sql.column_value( p_select_c, 29, p_select_rec.freight_remaining );
3973: dbms_sql.column_value( p_select_c, 30, p_select_rec.total_tax_amount );
3974: dbms_sql.column_value( p_select_c, 31, p_select_rec.total_freight_amount );
3975:
3976: print_fcn_label2( 'arp_maintain_ps2.get_ups_column_values()-' );
3977: EXCEPTION
3978: WHEN OTHERS THEN
3979: debug('EXCEPTION: arp_maintain_ps2.get_ups_column_values()',
3980: MSG_LEVEL_BASIC);
3975:
3976: print_fcn_label2( 'arp_maintain_ps2.get_ups_column_values()-' );
3977: EXCEPTION
3978: WHEN OTHERS THEN
3979: debug('EXCEPTION: arp_maintain_ps2.get_ups_column_values()',
3980: MSG_LEVEL_BASIC);
3981: RAISE;
3982: END get_ups_column_values;
3983:
4005: ----------------------------------------------------------------------------
4006: PROCEDURE dump_ups_select_rec( p_select_rec IN select_ups_rec_type ) IS
4007: BEGIN
4008:
4009: print_fcn_label2( 'arp_maintain_ps2.dump_ups_select_rec()+' );
4010:
4011: debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
4012: debug( ' set_of_books_id='
4013: || p_select_rec.set_of_books_id, MSG_LEVEL_DEBUG );
4066: || p_select_rec.freight_remaining, MSG_LEVEL_DEBUG );
4067: debug( ' adjustment_type='
4068: || p_select_rec.adjustment_type, MSG_LEVEL_DEBUG );
4069:
4070: print_fcn_label2( 'arp_maintain_ps2.dump_ups_select_rec()-' );
4071:
4072: EXCEPTION
4073: WHEN OTHERS THEN
4074: debug( 'EXCEPTION: arp_maintain_ps2.dump_ups_select_rec()',
4070: print_fcn_label2( 'arp_maintain_ps2.dump_ups_select_rec()-' );
4071:
4072: EXCEPTION
4073: WHEN OTHERS THEN
4074: debug( 'EXCEPTION: arp_maintain_ps2.dump_ups_select_rec()',
4075: MSG_LEVEL_BASIC );
4076: RAISE;
4077: END dump_ups_select_rec;
4078:
4128: l_tax_percent_t number_table_type;
4129: l_frt_percent_t number_table_type;
4130:
4131: BEGIN
4132: print_fcn_label2('arp_maintain_ps2.process_ups_data()+' );
4133:
4134: --------------------------------------------------------------------
4135: -- Find commitment balance
4136: --------------------------------------------------------------------
4653: END LOOP;
4654:
4655:
4656:
4657: print_fcn_label2('arp_maintain_ps2.process_ups_data()-' );
4658:
4659: EXCEPTION
4660: WHEN OTHERS THEN
4661: debug( 'EXCEPTION: arp_maintain_ps2.process_ups_data()',
4657: print_fcn_label2('arp_maintain_ps2.process_ups_data()-' );
4658:
4659: EXCEPTION
4660: WHEN OTHERS THEN
4661: debug( 'EXCEPTION: arp_maintain_ps2.process_ups_data()',
4662: MSG_LEVEL_BASIC );
4663: RAISE;
4664: END process_ups_data;
4665:
4711: -----------------------------------------------------------------------
4712: PROCEDURE load_tables( p_select_rec IN select_ups_rec_type ) IS
4713:
4714: BEGIN
4715: print_fcn_label2('arp_maintain_ps2.load_tables()+' );
4716:
4717: /* DEBUG */
4718: -- arp_standard.enable_file_debug('/sqlcom/out/omvispt3','ARTEMP2B.log');
4719:
4736: l_percent_t( l_table_index ) := p_select_rec.percent;
4737:
4738: l_table_index := l_table_index + 1;
4739:
4740: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
4741:
4742: EXCEPTION
4743: WHEN OTHERS THEN
4744: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
4740: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
4741:
4742: EXCEPTION
4743: WHEN OTHERS THEN
4744: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
4745: MSG_LEVEL_BASIC );
4746: RAISE;
4747: END load_tables;
4748:
4750: -----------------------------------------------------------------------
4751: PROCEDURE clear_inv_tables IS
4752:
4753: BEGIN
4754: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()+' );
4755:
4756: l_adj_amount_t := null_number_t;
4757: l_line_adj_t := null_number_t;
4758: l_tax_adj_t := null_number_t;
4758: l_tax_adj_t := null_number_t;
4759: l_frt_adj_t := null_number_t;
4760: l_acctd_adj_amount_t := null_number_t;
4761:
4762: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
4763:
4764: EXCEPTION
4765: WHEN OTHERS THEN
4766: debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
4762: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
4763:
4764: EXCEPTION
4765: WHEN OTHERS THEN
4766: debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
4767: MSG_LEVEL_BASIC );
4768: RAISE;
4769: END clear_inv_tables;
4770:
4772: -----------------------------------------------------------------------
4773: PROCEDURE clear_all_tables IS
4774:
4775: BEGIN
4776: print_fcn_label2('arp_maintain_ps2.clear_all_tables()+' );
4777:
4778:
4779:
4780: l_ps_id_t := null_id_t;
4789: clear_inv_tables;
4790:
4791: l_table_index := 0;
4792:
4793: print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
4794:
4795: EXCEPTION
4796: WHEN OTHERS THEN
4797: debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
4793: print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
4794:
4795: EXCEPTION
4796: WHEN OTHERS THEN
4797: debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
4798: MSG_LEVEL_BASIC );
4799: RAISE;
4800: END clear_all_tables;
4801:
4827:
4828:
4829: BEGIN
4830:
4831: print_fcn_label( 'arp_maintain_ps2.insert_child_adj_private()+' );
4832:
4833: --
4834: clear_all_tables;
4835: --
5022: RAISE;
5023:
5024: END;
5025:
5026: print_fcn_label( 'arp_maintain_ps2.insert_child_adj_private()-' );
5027:
5028: EXCEPTION
5029: WHEN OTHERS THEN
5030: debug( 'EXCEPTION: arp_maintain_ps2.insert_child_adj_private()',
5026: print_fcn_label( 'arp_maintain_ps2.insert_child_adj_private()-' );
5027:
5028: EXCEPTION
5029: WHEN OTHERS THEN
5030: debug( 'EXCEPTION: arp_maintain_ps2.insert_child_adj_private()',
5031: MSG_LEVEL_BASIC );
5032: RAISE;
5033:
5034: END insert_child_adj_private;
5087:
5088:
5089: BEGIN
5090:
5091: print_fcn_label( 'arp_maintain_ps2.build_iad_sql()+' );
5092:
5093: ------------------------------------------------
5094: -- Select sql
5095: ------------------------------------------------
5440: debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
5441: RAISE;
5442: END;
5443:
5444: print_fcn_label( 'arp_maintain_ps2.build_iad_sql()-' );
5445:
5446:
5447: EXCEPTION
5448: WHEN OTHERS THEN
5445:
5446:
5447: EXCEPTION
5448: WHEN OTHERS THEN
5449: debug( 'EXCEPTION: arp_maintain_ps2.build_iad_sql()',
5450: MSG_LEVEL_BASIC );
5451:
5452: RAISE;
5453: END build_iad_sql;
5459: p_select_rec IN select_iad_rec_type ) IS
5460:
5461: BEGIN
5462:
5463: print_fcn_label2( 'arp_maintain_ps2.define_iad_select_columns()+' );
5464:
5465: dbms_sql.define_column( p_select_c, 1, p_select_rec.set_of_books_id );
5466: dbms_sql.define_column( p_select_c, 2, p_select_rec.trx_date );
5467: dbms_sql.define_column( p_select_c, 3, p_select_rec.gl_date );
5507: dbms_sql.define_column( p_select_c, 35, p_select_rec.ps_freight_remaining);
5508: dbms_sql.define_column( p_select_c, 36, p_select_rec.ps_tax_original);
5509: dbms_sql.define_column( p_select_c, 37, p_select_rec.ps_freight_remaining);
5510:
5511: print_fcn_label2( 'arp_maintain_ps2.define_iad_select_columns()-' );
5512:
5513: EXCEPTION
5514: WHEN OTHERS THEN
5515: debug('EXCEPTION: arp_maintain_ps2.define_iad_select_columns()',
5511: print_fcn_label2( 'arp_maintain_ps2.define_iad_select_columns()-' );
5512:
5513: EXCEPTION
5514: WHEN OTHERS THEN
5515: debug('EXCEPTION: arp_maintain_ps2.define_iad_select_columns()',
5516: MSG_LEVEL_BASIC);
5517: RAISE;
5518: END define_iad_select_columns;
5519:
5522: PROCEDURE get_iad_column_values( p_select_c IN INTEGER,
5523: p_select_rec IN OUT NOCOPY select_iad_rec_type ) IS
5524: /* Bug 460927 - Changed IN to IN OUT in the above line - Oracle 8 */
5525: BEGIN
5526: print_fcn_label2( 'arp_maintain_ps2.get_iad_column_values()+' );
5527:
5528: dbms_sql.column_value( p_select_c, 1, p_select_rec.set_of_books_id );
5529: dbms_sql.column_value( p_select_c, 2, p_select_rec.trx_date );
5530: dbms_sql.column_value( p_select_c, 3, p_select_rec.gl_date );
5566: dbms_sql.column_value( p_select_c, 35, p_select_rec.ps_freight_remaining);
5567: dbms_sql.column_value( p_select_c, 36, p_select_rec.ps_tax_original);
5568: dbms_sql.column_value( p_select_c, 37, p_select_rec.ps_freight_original);
5569:
5570: print_fcn_label2( 'arp_maintain_ps2.get_iad_column_values()-' );
5571: EXCEPTION
5572: WHEN OTHERS THEN
5573: debug('EXCEPTION: arp_maintain_ps2.get_iad_column_values()',
5574: MSG_LEVEL_BASIC);
5569:
5570: print_fcn_label2( 'arp_maintain_ps2.get_iad_column_values()-' );
5571: EXCEPTION
5572: WHEN OTHERS THEN
5573: debug('EXCEPTION: arp_maintain_ps2.get_iad_column_values()',
5574: MSG_LEVEL_BASIC);
5575: RAISE;
5576: END get_iad_column_values;
5577:
5599: ----------------------------------------------------------------------------
5600: PROCEDURE dump_iad_select_rec( p_select_rec IN select_iad_rec_type ) IS
5601: BEGIN
5602:
5603: print_fcn_label2( 'arp_maintain_ps2.dump_iad_select_rec()+' );
5604:
5605: debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
5606: debug( ' set_of_books_id='
5607: || p_select_rec.set_of_books_id, MSG_LEVEL_DEBUG );
5659: debug( ' allocate_tax_freight='
5660: || p_select_rec.allocate_tax_freight, MSG_LEVEL_DEBUG);
5661:
5662:
5663: print_fcn_label2( 'arp_maintain_ps2.dump_iad_select_rec()-' );
5664:
5665: EXCEPTION
5666: WHEN OTHERS THEN
5667: debug( 'EXCEPTION: arp_maintain_ps2.dump_iad_select_rec()',
5663: print_fcn_label2( 'arp_maintain_ps2.dump_iad_select_rec()-' );
5664:
5665: EXCEPTION
5666: WHEN OTHERS THEN
5667: debug( 'EXCEPTION: arp_maintain_ps2.dump_iad_select_rec()',
5668: MSG_LEVEL_BASIC );
5669: RAISE;
5670: END dump_iad_select_rec;
5671:
5761:
5762: END reverse_adjustments;
5763:
5764: BEGIN
5765: print_fcn_label2('arp_maintain_ps2.process_iad_data()+' );
5766:
5767: /* MODULE FLOW (post 1483656 - TAX/FREIGHT modification)
5768:
5769: 1. LOOP to accumulate total PS remaining, total PS original,
6477: END LOOP;
6478:
6479:
6480:
6481: print_fcn_label2('arp_maintain_ps2.process_iad_data()-' );
6482:
6483: EXCEPTION
6484: WHEN OTHERS THEN
6485: debug( 'EXCEPTION: arp_maintain_ps2.process_iad_data()',
6481: print_fcn_label2('arp_maintain_ps2.process_iad_data()-' );
6482:
6483: EXCEPTION
6484: WHEN OTHERS THEN
6485: debug( 'EXCEPTION: arp_maintain_ps2.process_iad_data()',
6486: MSG_LEVEL_BASIC );
6487: RAISE;
6488: END process_iad_data;
6489:
6554: -----------------------------------------------------------------------
6555: PROCEDURE load_tables( p_select_rec IN select_iad_rec_type ) IS
6556:
6557: BEGIN
6558: print_fcn_label2('arp_maintain_ps2.load_tables()+' );
6559:
6560: l_ps_id_t( l_table_index ) := p_select_rec.payment_schedule_id;
6561: l_ps_amount_due_rem_t( l_table_index ) :=
6562: p_select_rec.ps_amount_due_remaining;
6586: p_select_rec.inv_frt_adj;
6587:
6588: l_table_index := l_table_index + 1;
6589:
6590: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
6591:
6592: EXCEPTION
6593: WHEN OTHERS THEN
6594: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
6590: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
6591:
6592: EXCEPTION
6593: WHEN OTHERS THEN
6594: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
6595: MSG_LEVEL_BASIC );
6596: RAISE;
6597: END load_tables;
6598:
6600: -----------------------------------------------------------------------
6601: PROCEDURE clear_cm_tables IS
6602:
6603: BEGIN
6604: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()+' );
6605:
6606: l_adj_amount_t := null_number_t;
6607: l_line_adj_t := null_number_t;
6608: l_tax_adj_t := null_number_t;
6609: l_frt_adj_t := null_number_t;
6610:
6611: l_acctd_adj_amount_t := null_number_t;
6612:
6613: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
6614:
6615: EXCEPTION
6616: WHEN OTHERS THEN
6617: debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
6613: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
6614:
6615: EXCEPTION
6616: WHEN OTHERS THEN
6617: debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
6618: MSG_LEVEL_BASIC );
6619: RAISE;
6620: END clear_cm_tables;
6621:
6623: -----------------------------------------------------------------------
6624: PROCEDURE clear_all_tables IS
6625:
6626: BEGIN
6627: print_fcn_label2('arp_maintain_ps2.clear_all_tables()+' );
6628:
6629:
6630:
6631: l_ps_id_t := null_id_t;
6646: clear_cm_tables;
6647:
6648: l_table_index := 0;
6649:
6650: print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
6651:
6652: EXCEPTION
6653: WHEN OTHERS THEN
6654: debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
6650: print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
6651:
6652: EXCEPTION
6653: WHEN OTHERS THEN
6654: debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
6655: MSG_LEVEL_BASIC );
6656: RAISE;
6657: END clear_all_tables;
6658:
6693:
6694:
6695: BEGIN
6696:
6697: print_fcn_label( 'arp_maintain_ps2.insert_cm_child_adj_private()+' );
6698:
6699: --
6700: clear_all_tables;
6701: --
6929: RAISE;
6930:
6931: END;
6932:
6933: print_fcn_label( 'arp_maintain_ps2.insert_cm_child_adj_private()-' );
6934:
6935: EXCEPTION
6936: WHEN OTHERS THEN
6937: debug( 'EXCEPTION: arp_maintain_ps2.insert_cm_child_adj_private()',
6933: print_fcn_label( 'arp_maintain_ps2.insert_cm_child_adj_private()-' );
6934:
6935: EXCEPTION
6936: WHEN OTHERS THEN
6937: debug( 'EXCEPTION: arp_maintain_ps2.insert_cm_child_adj_private()',
6938: MSG_LEVEL_BASIC );
6939: RAISE;
6940:
6941: END insert_cm_child_adj_private;
7029: IS
7030:
7031: BEGIN
7032:
7033: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_inv_ps()+' );
7034:
7035: build_ips_sql( system_info,
7036: profile_info,
7037: ips_select_c,
7056: profile_info,
7057: p_request_id,
7058: null);
7059:
7060: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_inv_ps()-' );
7061:
7062: END;
7063:
7064: ---------------------------------------------------------------------------
7079: IS
7080:
7081: BEGIN
7082:
7083: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_ps()+' );
7084:
7085: build_ira_sql( system_info,
7086: profile_info,
7087: ira_select_c,
7104:
7105:
7106: insert_cm_ps_private(system_info, profile_info, p_request_id);
7107:
7108: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_ps()-' );
7109:
7110: END;
7111: ---------------------------------------------------------------------------
7112: PROCEDURE test_insert_child_adj( p_customer_trx_id BINARY_INTEGER )
7126: IS
7127:
7128: BEGIN
7129:
7130: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_child_adj()+' );
7131:
7132: build_ups_sql( system_info,
7133: profile_info,
7134: ups_select_c,
7150:
7151:
7152: insert_child_adj_private(system_info, profile_info, p_request_id);
7153:
7154: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_child_adj()-' );
7155:
7156: END;
7157:
7158: ---------------------------------------------------------------------------
7173: IS
7174:
7175: BEGIN
7176:
7177: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_child_adj()+' );
7178:
7179: build_iad_sql( system_info,
7180: profile_info,
7181: iad_select_c,
7197:
7198:
7199: insert_cm_child_adj_private(system_info, profile_info, p_request_id);
7200:
7201: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_child_adj()-' );
7202:
7203: END;
7204:
7205: ---------------------------------------------------------------------------
7208: --
7209: PROCEDURE init IS
7210: BEGIN
7211:
7212: print_fcn_label( 'arp_maintain_ps2.constructor()+' );
7213:
7214: DECLARE
7215:
7216: l_result boolean;
7232:
7233: END;
7234:
7235:
7236: print_fcn_label( 'arp_maintain_ps2.constructor()-' );
7237:
7238:
7239: EXCEPTION
7240: WHEN OTHERS THEN
7237:
7238:
7239: EXCEPTION
7240: WHEN OTHERS THEN
7241: debug('EXCEPTION: arp_maintain_ps2.constructor()');
7242: debug(SQLERRM);
7243: RAISE;
7244: END init;
7245:
7244: END init;
7245:
7246: BEGIN
7247: init;
7248: END arp_maintain_ps2;