[Home] [Help]
1: PACKAGE BODY arp_maintain_ps2 AS
2: /* $Header: ARTEMP2B.pls 120.26.12010000.4 2008/11/20 11:52:59 npanchak 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: ------------------------------------------------
710: debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
711: RAISE;
712: END;
713:
714: print_fcn_label( 'arp_maintain_ps2.build_ips_sql()-' );
715:
716:
717: EXCEPTION
718: WHEN OTHERS THEN
715:
716:
717: EXCEPTION
718: WHEN OTHERS THEN
719: debug( 'EXCEPTION: arp_maintain_ps2.build_ips_sql()',
720: MSG_LEVEL_BASIC );
721:
722: RAISE;
723: END build_ips_sql;
729: p_select_rec IN select_ips_rec_type ) IS
730:
731: BEGIN
732:
733: print_fcn_label2( 'arp_maintain_ps2.define_ips_select_columns()+' );
734:
735: dbms_sql.define_column( p_select_c, 1, p_select_rec.customer_trx_id );
736: dbms_sql.define_column( p_select_c, 2, p_select_rec.total_line_amount );
737: dbms_sql.define_column( p_select_c, 3, p_select_rec.total_tax_amount );
764: p_select_rec.total_charges_amount );
765: dbms_sql.define_column( p_select_c, 25,p_select_rec.reversed_cash_receipt_id); /*Bug3328690 */
766:
767:
768: print_fcn_label2( 'arp_maintain_ps2.define_ips_select_columns()-' );
769:
770: EXCEPTION
771: WHEN OTHERS THEN
772: debug('EXCEPTION: arp_maintain_ps2.define_ips_select_columns()',
768: print_fcn_label2( 'arp_maintain_ps2.define_ips_select_columns()-' );
769:
770: EXCEPTION
771: WHEN OTHERS THEN
772: debug('EXCEPTION: arp_maintain_ps2.define_ips_select_columns()',
773: MSG_LEVEL_BASIC);
774: RAISE;
775: END define_ips_select_columns;
776:
779: PROCEDURE get_ips_column_values( p_select_c IN INTEGER,
780: p_select_rec IN OUT NOCOPY select_ips_rec_type ) IS
781: /* Bug 460927 - Modified IN to IN OUT in the above line - Oracle 8 */
782: BEGIN
783: print_fcn_label2( 'arp_maintain_ps2.get_ips_column_values()+' );
784:
785: dbms_sql.column_value( p_select_c, 1, p_select_rec.customer_trx_id );
786: dbms_sql.column_value( p_select_c, 2, p_select_rec.total_line_amount );
787: dbms_sql.column_value( p_select_c, 3, p_select_rec.total_tax_amount );
812: dbms_sql.column_value( p_select_c, 23, p_select_rec.rec_acctd_amount );
813: dbms_sql.column_value( p_select_c, 24, p_select_rec.total_charges_amount );
814: dbms_sql.column_value( p_select_c, 25, p_select_rec.reversed_cash_receipt_id); /*Bug3328690 */
815:
816: print_fcn_label2( 'arp_maintain_ps2.get_ips_column_values()-' );
817: EXCEPTION
818: WHEN OTHERS THEN
819: debug('EXCEPTION: arp_maintain_ps2.get_ips_column_values()',
820: MSG_LEVEL_BASIC);
815:
816: print_fcn_label2( 'arp_maintain_ps2.get_ips_column_values()-' );
817: EXCEPTION
818: WHEN OTHERS THEN
819: debug('EXCEPTION: arp_maintain_ps2.get_ips_column_values()',
820: MSG_LEVEL_BASIC);
821: RAISE;
822: END get_ips_column_values;
823:
845: ----------------------------------------------------------------------------
846: PROCEDURE dump_ips_select_rec( p_select_rec IN select_ips_rec_type ) IS
847: BEGIN
848:
849: print_fcn_label2( 'arp_maintain_ps2.dump_ips_select_rec()+' );
850:
851: debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
852: debug( ' customer_trx_id='
853: || p_select_rec.customer_trx_id, MSG_LEVEL_DEBUG );
899: || p_select_rec.percent, MSG_LEVEL_DEBUG );
900: debug( ' due_date='
901: || p_select_rec.due_date, MSG_LEVEL_DEBUG );
902:
903: print_fcn_label2( 'arp_maintain_ps2.dump_ips_select_rec()-' );
904:
905: EXCEPTION
906: WHEN OTHERS THEN
907: debug( 'EXCEPTION: arp_maintain_ps2.dump_ips_select_rec()',
903: print_fcn_label2( 'arp_maintain_ps2.dump_ips_select_rec()-' );
904:
905: EXCEPTION
906: WHEN OTHERS THEN
907: debug( 'EXCEPTION: arp_maintain_ps2.dump_ips_select_rec()',
908: MSG_LEVEL_BASIC );
909: RAISE;
910: END dump_ips_select_rec;
911:
937: sqlstmt VARCHAR2(254);
938: l_return_value_jl NUMBER;
939: /* BR Sped Project */
940: BEGIN
941: print_fcn_label2('arp_maintain_ps2.process_ips_data()+' );
942:
943:
944: --------------------------------------------------------------------
945: -- Distribute line amount
1215:
1216: END LOOP;
1217:
1218:
1219: print_fcn_label2('arp_maintain_ps2.process_ips_data()-' );
1220:
1221: EXCEPTION
1222: WHEN OTHERS THEN
1223: debug( 'EXCEPTION: arp_maintain_ps2.process_ips_data()',
1219: print_fcn_label2('arp_maintain_ps2.process_ips_data()-' );
1220:
1221: EXCEPTION
1222: WHEN OTHERS THEN
1223: debug( 'EXCEPTION: arp_maintain_ps2.process_ips_data()',
1224: MSG_LEVEL_BASIC );
1225: RAISE;
1226: END process_ips_data;
1227:
1254:
1255: PROCEDURE load_tables( p_select_rec IN select_ips_rec_type ) IS
1256:
1257: BEGIN
1258: print_fcn_label2('arp_maintain_ps2.load_tables()+' );
1259:
1260: l_terms_sequence_num_t( l_table_index ) :=
1261: p_select_rec.term_sequence_num;
1262: l_percent_t( l_table_index ) := p_select_rec.percent;
1262: l_percent_t( l_table_index ) := p_select_rec.percent;
1263: l_due_date_t( l_table_index ) := p_select_rec.due_date;
1264: l_table_index := l_table_index + 1;
1265:
1266: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
1267:
1268: EXCEPTION
1269: WHEN OTHERS THEN
1270: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
1266: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
1267:
1268: EXCEPTION
1269: WHEN OTHERS THEN
1270: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
1271: MSG_LEVEL_BASIC );
1272: RAISE;
1273: END load_tables;
1274:
1275:
1276: PROCEDURE clear_tables IS
1277:
1278: BEGIN
1279: print_fcn_label2('arp_maintain_ps2.clear_tables()+' );
1280:
1281:
1282: l_line_amount_t := null_number_t;
1283: l_tax_amount_t := null_number_t;
1290: l_due_date_t := null_date_t;
1291:
1292: l_table_index := 0;
1293:
1294: print_fcn_label2('arp_maintain_ps2.clear_tables()-' );
1295:
1296: EXCEPTION
1297: WHEN OTHERS THEN
1298: debug( 'EXCEPTION: arp_maintain_ps2.clear_tables()',
1294: print_fcn_label2('arp_maintain_ps2.clear_tables()-' );
1295:
1296: EXCEPTION
1297: WHEN OTHERS THEN
1298: debug( 'EXCEPTION: arp_maintain_ps2.clear_tables()',
1299: MSG_LEVEL_BASIC );
1300: RAISE;
1301: END clear_tables;
1302:
1301: END clear_tables;
1302:
1303: BEGIN
1304:
1305: print_fcn_label( 'arp_maintain_ps2.insert_inv_ps_private()+' );
1306:
1307: --
1308: clear_tables;
1309: --
1430: RAISE;
1431:
1432: END;
1433:
1434: print_fcn_label( 'arp_maintain_ps2.insert_inv_ps_private()-' );
1435:
1436: EXCEPTION
1437: WHEN OTHERS THEN
1438: debug( 'EXCEPTION: arp_maintain_ps2.insert_inv_ps_private()',
1434: print_fcn_label( 'arp_maintain_ps2.insert_inv_ps_private()-' );
1435:
1436: EXCEPTION
1437: WHEN OTHERS THEN
1438: debug( 'EXCEPTION: arp_maintain_ps2.insert_inv_ps_private()',
1439: MSG_LEVEL_BASIC );
1440: RAISE;
1441:
1442: END insert_inv_ps_private;
1483:
1484:
1485: BEGIN
1486:
1487: print_fcn_label( 'arp_maintain_ps2.build_ira_sql()+' );
1488:
1489: ------------------------------------------------
1490: -- Select sql
1491: ------------------------------------------------
1912: debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
1913: RAISE;
1914: END;
1915:
1916: print_fcn_label( 'arp_maintain_ps2.build_ira_sql()-' );
1917:
1918:
1919: EXCEPTION
1920: WHEN OTHERS THEN
1917:
1918:
1919: EXCEPTION
1920: WHEN OTHERS THEN
1921: debug( 'EXCEPTION: arp_maintain_ps2.build_ira_sql()',
1922: MSG_LEVEL_BASIC );
1923:
1924: RAISE;
1925: END build_ira_sql;
1931: p_select_rec IN select_ira_rec_type ) IS
1932:
1933: BEGIN
1934:
1935: print_fcn_label2( 'arp_maintain_ps2.define_ira_select_columns()+' );
1936:
1937: dbms_sql.define_column( p_select_c, 1, p_select_rec.trx_date );
1938: dbms_sql.define_column( p_select_c, 2, p_select_rec.customer_id );
1939: dbms_sql.define_column( p_select_c, 3, p_select_rec.cust_trx_type_id );
1983: dbms_sql.define_column( p_select_c, 35,
1984: p_select_rec.inv_charges_remaining );
1985:
1986:
1987: print_fcn_label2( 'arp_maintain_ps2.define_ira_select_columns()-' );
1988:
1989: EXCEPTION
1990: WHEN OTHERS THEN
1991: debug('EXCEPTION: arp_maintain_ps2.define_ira_select_columns()',
1987: print_fcn_label2( 'arp_maintain_ps2.define_ira_select_columns()-' );
1988:
1989: EXCEPTION
1990: WHEN OTHERS THEN
1991: debug('EXCEPTION: arp_maintain_ps2.define_ira_select_columns()',
1992: MSG_LEVEL_BASIC);
1993: RAISE;
1994: END define_ira_select_columns;
1995:
1998: PROCEDURE get_ira_column_values( p_select_c IN INTEGER,
1999: p_select_rec IN OUT NOCOPY select_ira_rec_type ) IS
2000: /* Bug 460927 - Modified IN to IN OUT oin the above line - oracle 8 */
2001: BEGIN
2002: print_fcn_label2( 'arp_maintain_ps2.get_ira_column_values()+' );
2003:
2004: dbms_sql.column_value( p_select_c, 1, p_select_rec.trx_date );
2005: dbms_sql.column_value( p_select_c, 2, p_select_rec.customer_id );
2006: dbms_sql.column_value( p_select_c, 3, p_select_rec.cust_trx_type_id );
2050: dbms_sql.column_value( p_select_c, 35,
2051: p_select_rec.inv_charges_remaining );
2052:
2053:
2054: print_fcn_label2( 'arp_maintain_ps2.get_ira_column_values()-' );
2055: EXCEPTION
2056: WHEN OTHERS THEN
2057: debug('EXCEPTION: arp_maintain_ps2.get_ira_column_values()',
2058: MSG_LEVEL_BASIC);
2053:
2054: print_fcn_label2( 'arp_maintain_ps2.get_ira_column_values()-' );
2055: EXCEPTION
2056: WHEN OTHERS THEN
2057: debug('EXCEPTION: arp_maintain_ps2.get_ira_column_values()',
2058: MSG_LEVEL_BASIC);
2059: RAISE;
2060: END get_ira_column_values;
2061:
2083: ----------------------------------------------------------------------------
2084: PROCEDURE dump_ira_select_rec( p_select_rec IN select_ira_rec_type ) IS
2085: BEGIN
2086:
2087: print_fcn_label2( 'arp_maintain_ps2.dump_ira_select_rec()+' );
2088:
2089: debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
2090: debug( ' customer_trx_id='
2091: || p_select_rec.customer_trx_id, MSG_LEVEL_DEBUG );
2157: || p_select_rec.inv_charges_remaining, MSG_LEVEL_DEBUG );
2158: debug( ' inv_amount_credited='
2159: || p_select_rec.inv_amount_credited, MSG_LEVEL_DEBUG );
2160:
2161: print_fcn_label2( 'arp_maintain_ps2.dump_ira_select_rec()-' );
2162:
2163: EXCEPTION
2164: WHEN OTHERS THEN
2165: debug( 'EXCEPTION: arp_maintain_ps2.dump_ira_select_rec()',
2161: print_fcn_label2( 'arp_maintain_ps2.dump_ira_select_rec()-' );
2162:
2163: EXCEPTION
2164: WHEN OTHERS THEN
2165: debug( 'EXCEPTION: arp_maintain_ps2.dump_ira_select_rec()',
2166: MSG_LEVEL_BASIC );
2167: RAISE;
2168: END dump_ira_select_rec;
2169:
2188: l_index NUMBER;
2189: l_high_index NUMBER;
2190:
2191: BEGIN
2192: print_fcn_label2('arp_maintain_ps2.distribute_fifo_lifo()+' );
2193:
2194: debug( ' p_direction='||p_direction, MSG_LEVEL_DEBUG );
2195: debug( ' p_start_index='||p_start_index, MSG_LEVEL_DEBUG );
2196: debug( ' p_end_index='||p_end_index, MSG_LEVEL_DEBUG );
2287:
2288: END IF;
2289:
2290:
2291: print_fcn_label2('arp_maintain_ps2.distribute_fifo_lifo()-' );
2292:
2293: EXCEPTION
2294: WHEN OTHERS THEN
2295: debug( 'EXCEPTION: arp_maintain_ps2.distribute_fifo_lifo()',
2291: print_fcn_label2('arp_maintain_ps2.distribute_fifo_lifo()-' );
2292:
2293: EXCEPTION
2294: WHEN OTHERS THEN
2295: debug( 'EXCEPTION: arp_maintain_ps2.distribute_fifo_lifo()',
2296: MSG_LEVEL_BASIC );
2297: RAISE;
2298:
2299: END distribute_fifo_lifo;
2374: END compute_percents;
2375: -----------------------------------------------------------------------
2376:
2377: BEGIN
2378: print_fcn_label2('arp_maintain_ps2.distribute_prorate()+' );
2379:
2380:
2381: debug( ' p_cm_amount='||p_cm_amount, MSG_LEVEL_DEBUG );
2382:
2439: p_inv_rem_t( i ) := p_inv_rem_t( i ) + p_cm_applied_amt_t( i );
2440: END LOOP;
2441:
2442:
2443: print_fcn_label2('arp_maintain_ps2.distribute_prorate()-' );
2444:
2445: EXCEPTION
2446: WHEN OTHERS THEN
2447: debug( 'EXCEPTION: arp_maintain_ps2.distribute_prorate()',
2443: print_fcn_label2('arp_maintain_ps2.distribute_prorate()-' );
2444:
2445: EXCEPTION
2446: WHEN OTHERS THEN
2447: debug( 'EXCEPTION: arp_maintain_ps2.distribute_prorate()',
2448: MSG_LEVEL_BASIC );
2449: RAISE;
2450:
2451: END distribute_prorate;
2501:
2502: l_ussgl_transaction_code ar_receivable_applications.ussgl_transaction_code%type;
2503:
2504: BEGIN
2505: print_fcn_label2('arp_maintain_ps2.process_ira_data()+' );
2506:
2507: debug( ' checking credit method', MSG_LEVEL_DEBUG );
2508:
2509: IF( p_select_rec.credit_method in (FIFO, LIFO) ) THEN
3120: END LOOP;
3121:
3122:
3123:
3124: print_fcn_label2('arp_maintain_ps2.process_ira_data()-' );
3125:
3126: EXCEPTION
3127: WHEN OTHERS THEN
3128: debug( 'EXCEPTION: arp_maintain_ps2.process_ira_data()',
3124: print_fcn_label2('arp_maintain_ps2.process_ira_data()-' );
3125:
3126: EXCEPTION
3127: WHEN OTHERS THEN
3128: debug( 'EXCEPTION: arp_maintain_ps2.process_ira_data()',
3129: MSG_LEVEL_BASIC );
3130: RAISE;
3131: END process_ira_data;
3132:
3175: -----------------------------------------------------------------------
3176: PROCEDURE load_tables( p_select_rec IN select_ira_rec_type ) IS
3177:
3178: BEGIN
3179: print_fcn_label2('arp_maintain_ps2.load_tables()+' );
3180:
3181: l_inv_ps_id_t( l_table_index ) :=
3182: p_select_rec.inv_payment_schedule_id;
3183: l_inv_amount_due_rem_t( l_table_index ) :=
3196: p_select_rec.inv_amount_credited;
3197:
3198: l_table_index := l_table_index + 1;
3199:
3200: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
3201:
3202: EXCEPTION
3203: WHEN OTHERS THEN
3204: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
3200: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
3201:
3202: EXCEPTION
3203: WHEN OTHERS THEN
3204: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
3205: MSG_LEVEL_BASIC );
3206: RAISE;
3207: END load_tables;
3208:
3210: -----------------------------------------------------------------------
3211: PROCEDURE clear_cm_tables IS
3212:
3213: BEGIN
3214: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()+' );
3215:
3216: l_line_applied_t := null_number_t;
3217: l_tax_applied_t := null_number_t;
3218: l_freight_applied_t := null_number_t;
3219: l_charges_applied_t := null_number_t;
3220: l_acctd_amt_applied_from_t := null_number_t;
3221: l_acctd_amt_applied_to_t := null_number_t;
3222:
3223: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
3224:
3225: EXCEPTION
3226: WHEN OTHERS THEN
3227: debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
3223: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
3224:
3225: EXCEPTION
3226: WHEN OTHERS THEN
3227: debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
3228: MSG_LEVEL_BASIC );
3229: RAISE;
3230: END clear_cm_tables;
3231:
3233: -----------------------------------------------------------------------
3234: PROCEDURE clear_all_tables IS
3235:
3236: BEGIN
3237: print_fcn_label2('arp_maintain_ps2.clear_all_tables()+' );
3238:
3239: l_inv_ps_id_t := null_id_t;
3240: l_inv_amount_due_rem_t := null_number_t;
3241: l_inv_acctd_amt_due_rem_t := null_number_t;
3248: clear_cm_tables;
3249:
3250: l_table_index := 0;
3251:
3252: print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
3253:
3254: EXCEPTION
3255: WHEN OTHERS THEN
3256: debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
3252: print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
3253:
3254: EXCEPTION
3255: WHEN OTHERS THEN
3256: debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
3257: MSG_LEVEL_BASIC );
3258: RAISE;
3259: END clear_all_tables;
3260:
3286:
3287:
3288: BEGIN
3289:
3290: print_fcn_label( 'arp_maintain_ps2.insert_cm_ps_private()+' );
3291:
3292: --
3293: clear_all_tables;
3294: --
3478: RAISE;
3479:
3480: END;
3481:
3482: print_fcn_label( 'arp_maintain_ps2.insert_cm_ps_private()-' );
3483:
3484: EXCEPTION
3485: WHEN OTHERS THEN
3486: debug( 'EXCEPTION: arp_maintain_ps2.insert_cm_ps_private()',
3482: print_fcn_label( 'arp_maintain_ps2.insert_cm_ps_private()-' );
3483:
3484: EXCEPTION
3485: WHEN OTHERS THEN
3486: debug( 'EXCEPTION: arp_maintain_ps2.insert_cm_ps_private()',
3487: MSG_LEVEL_BASIC );
3488: RAISE;
3489:
3490: END insert_cm_ps_private;
3531:
3532:
3533: BEGIN
3534:
3535: print_fcn_label( 'arp_maintain_ps2.build_ups_sql()+' );
3536:
3537: ------------------------------------------------
3538: -- Select sql
3539: ------------------------------------------------
3848: debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
3849: RAISE;
3850: END;
3851:
3852: print_fcn_label( 'arp_maintain_ps2.build_ups_sql()-' );
3853:
3854:
3855: EXCEPTION
3856: WHEN OTHERS THEN
3853:
3854:
3855: EXCEPTION
3856: WHEN OTHERS THEN
3857: debug( 'EXCEPTION: arp_maintain_ps2.build_ups_sql()',
3858: MSG_LEVEL_BASIC );
3859:
3860: RAISE;
3861: END build_ups_sql;
3867: p_select_rec IN select_ups_rec_type ) IS
3868:
3869: BEGIN
3870:
3871: print_fcn_label2( 'arp_maintain_ps2.define_ups_select_columns()+' );
3872:
3873: dbms_sql.define_column( p_select_c, 1, p_select_rec.set_of_books_id );
3874: dbms_sql.define_column( p_select_c, 2, p_select_rec.total_line_amount );
3875: dbms_sql.define_column( p_select_c, 3, p_select_rec.gl_date );
3911: dbms_sql.define_column( p_select_c, 29, p_select_rec.freight_remaining );
3912: dbms_sql.define_column( p_select_c, 30, p_select_rec.total_tax_amount );
3913: dbms_sql.define_column( p_select_c, 31, p_select_rec.total_freight_amount );
3914:
3915: print_fcn_label2( 'arp_maintain_ps2.define_ups_select_columns()-' );
3916:
3917: EXCEPTION
3918: WHEN OTHERS THEN
3919: debug('EXCEPTION: arp_maintain_ps2.define_ups_select_columns()',
3915: print_fcn_label2( 'arp_maintain_ps2.define_ups_select_columns()-' );
3916:
3917: EXCEPTION
3918: WHEN OTHERS THEN
3919: debug('EXCEPTION: arp_maintain_ps2.define_ups_select_columns()',
3920: MSG_LEVEL_BASIC);
3921: RAISE;
3922: END define_ups_select_columns;
3923:
3926: PROCEDURE get_ups_column_values( p_select_c IN INTEGER,
3927: p_select_rec IN OUT NOCOPY select_ups_rec_type ) IS
3928: /* Bug 460927 - Changed IN to IN OUT in the above line - Oracle 8 */
3929: BEGIN
3930: print_fcn_label2( 'arp_maintain_ps2.get_ups_column_values()+' );
3931:
3932: dbms_sql.column_value( p_select_c, 1, p_select_rec.set_of_books_id );
3933: dbms_sql.column_value( p_select_c, 2, p_select_rec.total_line_amount );
3934: dbms_sql.column_value( p_select_c, 3, p_select_rec.gl_date );
3963: dbms_sql.column_value( p_select_c, 29, p_select_rec.freight_remaining );
3964: dbms_sql.column_value( p_select_c, 30, p_select_rec.total_tax_amount );
3965: dbms_sql.column_value( p_select_c, 31, p_select_rec.total_freight_amount );
3966:
3967: print_fcn_label2( 'arp_maintain_ps2.get_ups_column_values()-' );
3968: EXCEPTION
3969: WHEN OTHERS THEN
3970: debug('EXCEPTION: arp_maintain_ps2.get_ups_column_values()',
3971: MSG_LEVEL_BASIC);
3966:
3967: print_fcn_label2( 'arp_maintain_ps2.get_ups_column_values()-' );
3968: EXCEPTION
3969: WHEN OTHERS THEN
3970: debug('EXCEPTION: arp_maintain_ps2.get_ups_column_values()',
3971: MSG_LEVEL_BASIC);
3972: RAISE;
3973: END get_ups_column_values;
3974:
3996: ----------------------------------------------------------------------------
3997: PROCEDURE dump_ups_select_rec( p_select_rec IN select_ups_rec_type ) IS
3998: BEGIN
3999:
4000: print_fcn_label2( 'arp_maintain_ps2.dump_ups_select_rec()+' );
4001:
4002: debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
4003: debug( ' set_of_books_id='
4004: || p_select_rec.set_of_books_id, MSG_LEVEL_DEBUG );
4057: || p_select_rec.freight_remaining, MSG_LEVEL_DEBUG );
4058: debug( ' adjustment_type='
4059: || p_select_rec.adjustment_type, MSG_LEVEL_DEBUG );
4060:
4061: print_fcn_label2( 'arp_maintain_ps2.dump_ups_select_rec()-' );
4062:
4063: EXCEPTION
4064: WHEN OTHERS THEN
4065: debug( 'EXCEPTION: arp_maintain_ps2.dump_ups_select_rec()',
4061: print_fcn_label2( 'arp_maintain_ps2.dump_ups_select_rec()-' );
4062:
4063: EXCEPTION
4064: WHEN OTHERS THEN
4065: debug( 'EXCEPTION: arp_maintain_ps2.dump_ups_select_rec()',
4066: MSG_LEVEL_BASIC );
4067: RAISE;
4068: END dump_ups_select_rec;
4069:
4119: l_tax_percent_t number_table_type;
4120: l_frt_percent_t number_table_type;
4121:
4122: BEGIN
4123: print_fcn_label2('arp_maintain_ps2.process_ups_data()+' );
4124:
4125: --------------------------------------------------------------------
4126: -- Find commitment balance
4127: --------------------------------------------------------------------
4644: END LOOP;
4645:
4646:
4647:
4648: print_fcn_label2('arp_maintain_ps2.process_ups_data()-' );
4649:
4650: EXCEPTION
4651: WHEN OTHERS THEN
4652: debug( 'EXCEPTION: arp_maintain_ps2.process_ups_data()',
4648: print_fcn_label2('arp_maintain_ps2.process_ups_data()-' );
4649:
4650: EXCEPTION
4651: WHEN OTHERS THEN
4652: debug( 'EXCEPTION: arp_maintain_ps2.process_ups_data()',
4653: MSG_LEVEL_BASIC );
4654: RAISE;
4655: END process_ups_data;
4656:
4702: -----------------------------------------------------------------------
4703: PROCEDURE load_tables( p_select_rec IN select_ups_rec_type ) IS
4704:
4705: BEGIN
4706: print_fcn_label2('arp_maintain_ps2.load_tables()+' );
4707:
4708: /* DEBUG */
4709: -- arp_standard.enable_file_debug('/sqlcom/out/omvispt3','ARTEMP2B.log');
4710:
4727: l_percent_t( l_table_index ) := p_select_rec.percent;
4728:
4729: l_table_index := l_table_index + 1;
4730:
4731: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
4732:
4733: EXCEPTION
4734: WHEN OTHERS THEN
4735: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
4731: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
4732:
4733: EXCEPTION
4734: WHEN OTHERS THEN
4735: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
4736: MSG_LEVEL_BASIC );
4737: RAISE;
4738: END load_tables;
4739:
4741: -----------------------------------------------------------------------
4742: PROCEDURE clear_inv_tables IS
4743:
4744: BEGIN
4745: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()+' );
4746:
4747: l_adj_amount_t := null_number_t;
4748: l_line_adj_t := null_number_t;
4749: l_tax_adj_t := null_number_t;
4749: l_tax_adj_t := null_number_t;
4750: l_frt_adj_t := null_number_t;
4751: l_acctd_adj_amount_t := null_number_t;
4752:
4753: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
4754:
4755: EXCEPTION
4756: WHEN OTHERS THEN
4757: debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
4753: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
4754:
4755: EXCEPTION
4756: WHEN OTHERS THEN
4757: debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
4758: MSG_LEVEL_BASIC );
4759: RAISE;
4760: END clear_inv_tables;
4761:
4763: -----------------------------------------------------------------------
4764: PROCEDURE clear_all_tables IS
4765:
4766: BEGIN
4767: print_fcn_label2('arp_maintain_ps2.clear_all_tables()+' );
4768:
4769:
4770:
4771: l_ps_id_t := null_id_t;
4780: clear_inv_tables;
4781:
4782: l_table_index := 0;
4783:
4784: print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
4785:
4786: EXCEPTION
4787: WHEN OTHERS THEN
4788: debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
4784: print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
4785:
4786: EXCEPTION
4787: WHEN OTHERS THEN
4788: debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
4789: MSG_LEVEL_BASIC );
4790: RAISE;
4791: END clear_all_tables;
4792:
4818:
4819:
4820: BEGIN
4821:
4822: print_fcn_label( 'arp_maintain_ps2.insert_child_adj_private()+' );
4823:
4824: --
4825: clear_all_tables;
4826: --
5013: RAISE;
5014:
5015: END;
5016:
5017: print_fcn_label( 'arp_maintain_ps2.insert_child_adj_private()-' );
5018:
5019: EXCEPTION
5020: WHEN OTHERS THEN
5021: debug( 'EXCEPTION: arp_maintain_ps2.insert_child_adj_private()',
5017: print_fcn_label( 'arp_maintain_ps2.insert_child_adj_private()-' );
5018:
5019: EXCEPTION
5020: WHEN OTHERS THEN
5021: debug( 'EXCEPTION: arp_maintain_ps2.insert_child_adj_private()',
5022: MSG_LEVEL_BASIC );
5023: RAISE;
5024:
5025: END insert_child_adj_private;
5078:
5079:
5080: BEGIN
5081:
5082: print_fcn_label( 'arp_maintain_ps2.build_iad_sql()+' );
5083:
5084: ------------------------------------------------
5085: -- Select sql
5086: ------------------------------------------------
5431: debug( 'EXCEPTION: Error parsing stmts', MSG_LEVEL_BASIC );
5432: RAISE;
5433: END;
5434:
5435: print_fcn_label( 'arp_maintain_ps2.build_iad_sql()-' );
5436:
5437:
5438: EXCEPTION
5439: WHEN OTHERS THEN
5436:
5437:
5438: EXCEPTION
5439: WHEN OTHERS THEN
5440: debug( 'EXCEPTION: arp_maintain_ps2.build_iad_sql()',
5441: MSG_LEVEL_BASIC );
5442:
5443: RAISE;
5444: END build_iad_sql;
5450: p_select_rec IN select_iad_rec_type ) IS
5451:
5452: BEGIN
5453:
5454: print_fcn_label2( 'arp_maintain_ps2.define_iad_select_columns()+' );
5455:
5456: dbms_sql.define_column( p_select_c, 1, p_select_rec.set_of_books_id );
5457: dbms_sql.define_column( p_select_c, 2, p_select_rec.trx_date );
5458: dbms_sql.define_column( p_select_c, 3, p_select_rec.gl_date );
5498: dbms_sql.define_column( p_select_c, 35, p_select_rec.ps_freight_remaining);
5499: dbms_sql.define_column( p_select_c, 36, p_select_rec.ps_tax_original);
5500: dbms_sql.define_column( p_select_c, 37, p_select_rec.ps_freight_remaining);
5501:
5502: print_fcn_label2( 'arp_maintain_ps2.define_iad_select_columns()-' );
5503:
5504: EXCEPTION
5505: WHEN OTHERS THEN
5506: debug('EXCEPTION: arp_maintain_ps2.define_iad_select_columns()',
5502: print_fcn_label2( 'arp_maintain_ps2.define_iad_select_columns()-' );
5503:
5504: EXCEPTION
5505: WHEN OTHERS THEN
5506: debug('EXCEPTION: arp_maintain_ps2.define_iad_select_columns()',
5507: MSG_LEVEL_BASIC);
5508: RAISE;
5509: END define_iad_select_columns;
5510:
5513: PROCEDURE get_iad_column_values( p_select_c IN INTEGER,
5514: p_select_rec IN OUT NOCOPY select_iad_rec_type ) IS
5515: /* Bug 460927 - Changed IN to IN OUT in the above line - Oracle 8 */
5516: BEGIN
5517: print_fcn_label2( 'arp_maintain_ps2.get_iad_column_values()+' );
5518:
5519: dbms_sql.column_value( p_select_c, 1, p_select_rec.set_of_books_id );
5520: dbms_sql.column_value( p_select_c, 2, p_select_rec.trx_date );
5521: dbms_sql.column_value( p_select_c, 3, p_select_rec.gl_date );
5557: dbms_sql.column_value( p_select_c, 35, p_select_rec.ps_freight_remaining);
5558: dbms_sql.column_value( p_select_c, 36, p_select_rec.ps_tax_original);
5559: dbms_sql.column_value( p_select_c, 37, p_select_rec.ps_freight_original);
5560:
5561: print_fcn_label2( 'arp_maintain_ps2.get_iad_column_values()-' );
5562: EXCEPTION
5563: WHEN OTHERS THEN
5564: debug('EXCEPTION: arp_maintain_ps2.get_iad_column_values()',
5565: MSG_LEVEL_BASIC);
5560:
5561: print_fcn_label2( 'arp_maintain_ps2.get_iad_column_values()-' );
5562: EXCEPTION
5563: WHEN OTHERS THEN
5564: debug('EXCEPTION: arp_maintain_ps2.get_iad_column_values()',
5565: MSG_LEVEL_BASIC);
5566: RAISE;
5567: END get_iad_column_values;
5568:
5590: ----------------------------------------------------------------------------
5591: PROCEDURE dump_iad_select_rec( p_select_rec IN select_iad_rec_type ) IS
5592: BEGIN
5593:
5594: print_fcn_label2( 'arp_maintain_ps2.dump_iad_select_rec()+' );
5595:
5596: debug( ' Dumping select record: ', MSG_LEVEL_DEBUG );
5597: debug( ' set_of_books_id='
5598: || p_select_rec.set_of_books_id, MSG_LEVEL_DEBUG );
5650: debug( ' allocate_tax_freight='
5651: || p_select_rec.allocate_tax_freight, MSG_LEVEL_DEBUG);
5652:
5653:
5654: print_fcn_label2( 'arp_maintain_ps2.dump_iad_select_rec()-' );
5655:
5656: EXCEPTION
5657: WHEN OTHERS THEN
5658: debug( 'EXCEPTION: arp_maintain_ps2.dump_iad_select_rec()',
5654: print_fcn_label2( 'arp_maintain_ps2.dump_iad_select_rec()-' );
5655:
5656: EXCEPTION
5657: WHEN OTHERS THEN
5658: debug( 'EXCEPTION: arp_maintain_ps2.dump_iad_select_rec()',
5659: MSG_LEVEL_BASIC );
5660: RAISE;
5661: END dump_iad_select_rec;
5662:
5752:
5753: END reverse_adjustments;
5754:
5755: BEGIN
5756: print_fcn_label2('arp_maintain_ps2.process_iad_data()+' );
5757:
5758: /* MODULE FLOW (post 1483656 - TAX/FREIGHT modification)
5759:
5760: 1. LOOP to accumulate total PS remaining, total PS original,
6468: END LOOP;
6469:
6470:
6471:
6472: print_fcn_label2('arp_maintain_ps2.process_iad_data()-' );
6473:
6474: EXCEPTION
6475: WHEN OTHERS THEN
6476: debug( 'EXCEPTION: arp_maintain_ps2.process_iad_data()',
6472: print_fcn_label2('arp_maintain_ps2.process_iad_data()-' );
6473:
6474: EXCEPTION
6475: WHEN OTHERS THEN
6476: debug( 'EXCEPTION: arp_maintain_ps2.process_iad_data()',
6477: MSG_LEVEL_BASIC );
6478: RAISE;
6479: END process_iad_data;
6480:
6545: -----------------------------------------------------------------------
6546: PROCEDURE load_tables( p_select_rec IN select_iad_rec_type ) IS
6547:
6548: BEGIN
6549: print_fcn_label2('arp_maintain_ps2.load_tables()+' );
6550:
6551: l_ps_id_t( l_table_index ) := p_select_rec.payment_schedule_id;
6552: l_ps_amount_due_rem_t( l_table_index ) :=
6553: p_select_rec.ps_amount_due_remaining;
6577: p_select_rec.inv_frt_adj;
6578:
6579: l_table_index := l_table_index + 1;
6580:
6581: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
6582:
6583: EXCEPTION
6584: WHEN OTHERS THEN
6585: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
6581: print_fcn_label2('arp_maintain_ps2.load_tables()-' );
6582:
6583: EXCEPTION
6584: WHEN OTHERS THEN
6585: debug( 'EXCEPTION: arp_maintain_ps2.load_tables()',
6586: MSG_LEVEL_BASIC );
6587: RAISE;
6588: END load_tables;
6589:
6591: -----------------------------------------------------------------------
6592: PROCEDURE clear_cm_tables IS
6593:
6594: BEGIN
6595: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()+' );
6596:
6597: l_adj_amount_t := null_number_t;
6598: l_line_adj_t := null_number_t;
6599: l_tax_adj_t := null_number_t;
6600: l_frt_adj_t := null_number_t;
6601:
6602: l_acctd_adj_amount_t := null_number_t;
6603:
6604: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
6605:
6606: EXCEPTION
6607: WHEN OTHERS THEN
6608: debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
6604: print_fcn_label2('arp_maintain_ps2.clear_cm_tables()-' );
6605:
6606: EXCEPTION
6607: WHEN OTHERS THEN
6608: debug( 'EXCEPTION: arp_maintain_ps2.clear_cm_tables()',
6609: MSG_LEVEL_BASIC );
6610: RAISE;
6611: END clear_cm_tables;
6612:
6614: -----------------------------------------------------------------------
6615: PROCEDURE clear_all_tables IS
6616:
6617: BEGIN
6618: print_fcn_label2('arp_maintain_ps2.clear_all_tables()+' );
6619:
6620:
6621:
6622: l_ps_id_t := null_id_t;
6637: clear_cm_tables;
6638:
6639: l_table_index := 0;
6640:
6641: print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
6642:
6643: EXCEPTION
6644: WHEN OTHERS THEN
6645: debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
6641: print_fcn_label2('arp_maintain_ps2.clear_all_tables()-' );
6642:
6643: EXCEPTION
6644: WHEN OTHERS THEN
6645: debug( 'EXCEPTION: arp_maintain_ps2.clear_all_tables()',
6646: MSG_LEVEL_BASIC );
6647: RAISE;
6648: END clear_all_tables;
6649:
6684:
6685:
6686: BEGIN
6687:
6688: print_fcn_label( 'arp_maintain_ps2.insert_cm_child_adj_private()+' );
6689:
6690: --
6691: clear_all_tables;
6692: --
6920: RAISE;
6921:
6922: END;
6923:
6924: print_fcn_label( 'arp_maintain_ps2.insert_cm_child_adj_private()-' );
6925:
6926: EXCEPTION
6927: WHEN OTHERS THEN
6928: debug( 'EXCEPTION: arp_maintain_ps2.insert_cm_child_adj_private()',
6924: print_fcn_label( 'arp_maintain_ps2.insert_cm_child_adj_private()-' );
6925:
6926: EXCEPTION
6927: WHEN OTHERS THEN
6928: debug( 'EXCEPTION: arp_maintain_ps2.insert_cm_child_adj_private()',
6929: MSG_LEVEL_BASIC );
6930: RAISE;
6931:
6932: END insert_cm_child_adj_private;
7020: IS
7021:
7022: BEGIN
7023:
7024: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_inv_ps()+' );
7025:
7026: build_ips_sql( system_info,
7027: profile_info,
7028: ips_select_c,
7047: profile_info,
7048: p_request_id,
7049: null);
7050:
7051: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_inv_ps()-' );
7052:
7053: END;
7054:
7055: ---------------------------------------------------------------------------
7070: IS
7071:
7072: BEGIN
7073:
7074: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_ps()+' );
7075:
7076: build_ira_sql( system_info,
7077: profile_info,
7078: ira_select_c,
7095:
7096:
7097: insert_cm_ps_private(system_info, profile_info, p_request_id);
7098:
7099: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_ps()-' );
7100:
7101: END;
7102: ---------------------------------------------------------------------------
7103: PROCEDURE test_insert_child_adj( p_customer_trx_id BINARY_INTEGER )
7117: IS
7118:
7119: BEGIN
7120:
7121: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_child_adj()+' );
7122:
7123: build_ups_sql( system_info,
7124: profile_info,
7125: ups_select_c,
7141:
7142:
7143: insert_child_adj_private(system_info, profile_info, p_request_id);
7144:
7145: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_child_adj()-' );
7146:
7147: END;
7148:
7149: ---------------------------------------------------------------------------
7164: IS
7165:
7166: BEGIN
7167:
7168: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_child_adj()+' );
7169:
7170: build_iad_sql( system_info,
7171: profile_info,
7172: iad_select_c,
7188:
7189:
7190: insert_cm_child_adj_private(system_info, profile_info, p_request_id);
7191:
7192: print_fcn_label( 'arp_maintain_ps2.test_ai_insert_cm_child_adj()-' );
7193:
7194: END;
7195:
7196: ---------------------------------------------------------------------------
7199: --
7200: PROCEDURE init IS
7201: BEGIN
7202:
7203: print_fcn_label( 'arp_maintain_ps2.constructor()+' );
7204:
7205: DECLARE
7206:
7207: l_result boolean;
7223:
7224: END;
7225:
7226:
7227: print_fcn_label( 'arp_maintain_ps2.constructor()-' );
7228:
7229:
7230: EXCEPTION
7231: WHEN OTHERS THEN
7228:
7229:
7230: EXCEPTION
7231: WHEN OTHERS THEN
7232: debug('EXCEPTION: arp_maintain_ps2.constructor()');
7233: debug(SQLERRM);
7234: RAISE;
7235: END init;
7236:
7235: END init;
7236:
7237: BEGIN
7238: init;
7239: END arp_maintain_ps2;