APPS.AR_TRX_SUMMARY_PKG dependencies on DBMS_SQL

Line 724: v_customer_id DBMS_SQL.NUMBER_TABLE;

720: v_cursor1 NUMBER;
721: v_cursor2 NUMBER;
722: v_BatchSize INTEGER := 1000;
723: v_NumRows INTEGER;
724: v_customer_id DBMS_SQL.NUMBER_TABLE;
725: v_site_use_id DBMS_SQL.NUMBER_TABLE;
726: v_currency_code DBMS_SQL.VARCHAR2_TABLE;
727: v_trx_date DBMS_SQL.DATE_TABLE;
728: v_amount DBMS_SQL.NUMBER_TABLE;

Line 725: v_site_use_id DBMS_SQL.NUMBER_TABLE;

721: v_cursor2 NUMBER;
722: v_BatchSize INTEGER := 1000;
723: v_NumRows INTEGER;
724: v_customer_id DBMS_SQL.NUMBER_TABLE;
725: v_site_use_id DBMS_SQL.NUMBER_TABLE;
726: v_currency_code DBMS_SQL.VARCHAR2_TABLE;
727: v_trx_date DBMS_SQL.DATE_TABLE;
728: v_amount DBMS_SQL.NUMBER_TABLE;
729: v_cust_trx_id DBMS_SQL.NUMBER_TABLE;

Line 726: v_currency_code DBMS_SQL.VARCHAR2_TABLE;

722: v_BatchSize INTEGER := 1000;
723: v_NumRows INTEGER;
724: v_customer_id DBMS_SQL.NUMBER_TABLE;
725: v_site_use_id DBMS_SQL.NUMBER_TABLE;
726: v_currency_code DBMS_SQL.VARCHAR2_TABLE;
727: v_trx_date DBMS_SQL.DATE_TABLE;
728: v_amount DBMS_SQL.NUMBER_TABLE;
729: v_cust_trx_id DBMS_SQL.NUMBER_TABLE;
730: v_return_code INTEGER;

Line 727: v_trx_date DBMS_SQL.DATE_TABLE;

723: v_NumRows INTEGER;
724: v_customer_id DBMS_SQL.NUMBER_TABLE;
725: v_site_use_id DBMS_SQL.NUMBER_TABLE;
726: v_currency_code DBMS_SQL.VARCHAR2_TABLE;
727: v_trx_date DBMS_SQL.DATE_TABLE;
728: v_amount DBMS_SQL.NUMBER_TABLE;
729: v_cust_trx_id DBMS_SQL.NUMBER_TABLE;
730: v_return_code INTEGER;
731: text_select VARCHAR2(4000);

Line 728: v_amount DBMS_SQL.NUMBER_TABLE;

724: v_customer_id DBMS_SQL.NUMBER_TABLE;
725: v_site_use_id DBMS_SQL.NUMBER_TABLE;
726: v_currency_code DBMS_SQL.VARCHAR2_TABLE;
727: v_trx_date DBMS_SQL.DATE_TABLE;
728: v_amount DBMS_SQL.NUMBER_TABLE;
729: v_cust_trx_id DBMS_SQL.NUMBER_TABLE;
730: v_return_code INTEGER;
731: text_select VARCHAR2(4000);
732: text_update VARCHAR2(4000);

Line 729: v_cust_trx_id DBMS_SQL.NUMBER_TABLE;

725: v_site_use_id DBMS_SQL.NUMBER_TABLE;
726: v_currency_code DBMS_SQL.VARCHAR2_TABLE;
727: v_trx_date DBMS_SQL.DATE_TABLE;
728: v_amount DBMS_SQL.NUMBER_TABLE;
729: v_cust_trx_id DBMS_SQL.NUMBER_TABLE;
730: v_return_code INTEGER;
731: text_select VARCHAR2(4000);
732: text_update VARCHAR2(4000);
733: begin

Line 771: v_cursor1 := dbms_sql.open_cursor;

767: and SITE_USE_ID = :site_use_id
768: and CURRENCY = :currency_code
769: and AS_OF_DATE = :trx_date';
770:
771: v_cursor1 := dbms_sql.open_cursor;
772: v_cursor2 := dbms_sql.open_cursor;
773:
774: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
775: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);

Line 772: v_cursor2 := dbms_sql.open_cursor;

768: and CURRENCY = :currency_code
769: and AS_OF_DATE = :trx_date';
770:
771: v_cursor1 := dbms_sql.open_cursor;
772: v_cursor2 := dbms_sql.open_cursor;
773:
774: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
775: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
776:

Line 774: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);

770:
771: v_cursor1 := dbms_sql.open_cursor;
772: v_cursor2 := dbms_sql.open_cursor;
773:
774: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
775: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
776:
777: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
778: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);

Line 775: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);

771: v_cursor1 := dbms_sql.open_cursor;
772: v_cursor2 := dbms_sql.open_cursor;
773:
774: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
775: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
776:
777: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
778: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
779: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);

Line 777: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);

773:
774: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
775: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
776:
777: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
778: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
779: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
780: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
781: dbms_sql.define_array(v_cursor1,5,v_amount,v_BatchSize,1);

Line 778: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);

774: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
775: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
776:
777: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
778: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
779: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
780: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
781: dbms_sql.define_array(v_cursor1,5,v_amount,v_BatchSize,1);
782: dbms_sql.define_array(v_cursor1,6,v_cust_trx_id,v_BatchSize,1);

Line 779: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);

775: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
776:
777: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
778: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
779: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
780: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
781: dbms_sql.define_array(v_cursor1,5,v_amount,v_BatchSize,1);
782: dbms_sql.define_array(v_cursor1,6,v_cust_trx_id,v_BatchSize,1);
783:

Line 780: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);

776:
777: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
778: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
779: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
780: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
781: dbms_sql.define_array(v_cursor1,5,v_amount,v_BatchSize,1);
782: dbms_sql.define_array(v_cursor1,6,v_cust_trx_id,v_BatchSize,1);
783:
784: v_return_code := dbms_sql.execute(v_cursor1);

Line 781: dbms_sql.define_array(v_cursor1,5,v_amount,v_BatchSize,1);

777: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
778: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
779: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
780: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
781: dbms_sql.define_array(v_cursor1,5,v_amount,v_BatchSize,1);
782: dbms_sql.define_array(v_cursor1,6,v_cust_trx_id,v_BatchSize,1);
783:
784: v_return_code := dbms_sql.execute(v_cursor1);
785:

Line 782: dbms_sql.define_array(v_cursor1,6,v_cust_trx_id,v_BatchSize,1);

778: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
779: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
780: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
781: dbms_sql.define_array(v_cursor1,5,v_amount,v_BatchSize,1);
782: dbms_sql.define_array(v_cursor1,6,v_cust_trx_id,v_BatchSize,1);
783:
784: v_return_code := dbms_sql.execute(v_cursor1);
785:
786: --This is the fetch loop. Each call to FETCH_ROWS will retrive v_BatchSize

Line 784: v_return_code := dbms_sql.execute(v_cursor1);

780: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
781: dbms_sql.define_array(v_cursor1,5,v_amount,v_BatchSize,1);
782: dbms_sql.define_array(v_cursor1,6,v_cust_trx_id,v_BatchSize,1);
783:
784: v_return_code := dbms_sql.execute(v_cursor1);
785:
786: --This is the fetch loop. Each call to FETCH_ROWS will retrive v_BatchSize
787: --rows of data. The loop is over when FETCH_ROWS returns a value< v_BatchSize.
788:

Line 798: v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);

794: v_trx_date.delete;
795: v_cust_trx_id.delete;
796: v_amount.delete;
797:
798: v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
799: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
800: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
801: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
802: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);

Line 799: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);

795: v_cust_trx_id.delete;
796: v_amount.delete;
797:
798: v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
799: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
800: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
801: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
802: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
803: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_amount);

Line 800: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);

796: v_amount.delete;
797:
798: v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
799: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
800: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
801: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
802: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
803: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_amount);
804: DBMS_SQL.COLUMN_VALUE(v_cursor1,6,v_cust_trx_id);

Line 801: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);

797:
798: v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
799: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
800: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
801: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
802: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
803: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_amount);
804: DBMS_SQL.COLUMN_VALUE(v_cursor1,6,v_cust_trx_id);
805:

Line 802: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);

798: v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
799: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
800: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
801: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
802: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
803: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_amount);
804: DBMS_SQL.COLUMN_VALUE(v_cursor1,6,v_cust_trx_id);
805:
806: --The special case of v_NumRows = 0 needs to be checked here. This

Line 803: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_amount);

799: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
800: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
801: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
802: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
803: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_amount);
804: DBMS_SQL.COLUMN_VALUE(v_cursor1,6,v_cust_trx_id);
805:
806: --The special case of v_NumRows = 0 needs to be checked here. This
807: --means that the previous fetch returned all the remaining rows and

Line 804: DBMS_SQL.COLUMN_VALUE(v_cursor1,6,v_cust_trx_id);

800: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
801: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
802: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
803: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_amount);
804: DBMS_SQL.COLUMN_VALUE(v_cursor1,6,v_cust_trx_id);
805:
806: --The special case of v_NumRows = 0 needs to be checked here. This
807: --means that the previous fetch returned all the remaining rows and
808: --therefore we are done with the loop.

Line 817: DBMS_SQL.BIND_ARRAY(v_cursor2,':amount',v_amount);

813:
814: --Use BIND_ARRAYS to specify the input variables for the insert.
815: --only elements 1..V_NumRows will be used.
816:
817: DBMS_SQL.BIND_ARRAY(v_cursor2,':amount',v_amount);
818: DBMS_SQL.BIND_ARRAY(v_cursor2,':cust_trx_id',v_cust_trx_id);
819: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
820: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
821: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);

Line 818: DBMS_SQL.BIND_ARRAY(v_cursor2,':cust_trx_id',v_cust_trx_id);

814: --Use BIND_ARRAYS to specify the input variables for the insert.
815: --only elements 1..V_NumRows will be used.
816:
817: DBMS_SQL.BIND_ARRAY(v_cursor2,':amount',v_amount);
818: DBMS_SQL.BIND_ARRAY(v_cursor2,':cust_trx_id',v_cust_trx_id);
819: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
820: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
821: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
822: DBMS_SQL.BIND_ARRAY(v_cursor2,':trx_date',v_trx_date);

Line 819: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);

815: --only elements 1..V_NumRows will be used.
816:
817: DBMS_SQL.BIND_ARRAY(v_cursor2,':amount',v_amount);
818: DBMS_SQL.BIND_ARRAY(v_cursor2,':cust_trx_id',v_cust_trx_id);
819: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
820: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
821: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
822: DBMS_SQL.BIND_ARRAY(v_cursor2,':trx_date',v_trx_date);
823:

Line 820: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);

816:
817: DBMS_SQL.BIND_ARRAY(v_cursor2,':amount',v_amount);
818: DBMS_SQL.BIND_ARRAY(v_cursor2,':cust_trx_id',v_cust_trx_id);
819: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
820: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
821: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
822: DBMS_SQL.BIND_ARRAY(v_cursor2,':trx_date',v_trx_date);
823:
824: v_return_code := DBMS_SQL.EXECUTE(v_cursor2);

Line 821: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);

817: DBMS_SQL.BIND_ARRAY(v_cursor2,':amount',v_amount);
818: DBMS_SQL.BIND_ARRAY(v_cursor2,':cust_trx_id',v_cust_trx_id);
819: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
820: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
821: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
822: DBMS_SQL.BIND_ARRAY(v_cursor2,':trx_date',v_trx_date);
823:
824: v_return_code := DBMS_SQL.EXECUTE(v_cursor2);
825:

Line 822: DBMS_SQL.BIND_ARRAY(v_cursor2,':trx_date',v_trx_date);

818: DBMS_SQL.BIND_ARRAY(v_cursor2,':cust_trx_id',v_cust_trx_id);
819: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
820: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
821: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
822: DBMS_SQL.BIND_ARRAY(v_cursor2,':trx_date',v_trx_date);
823:
824: v_return_code := DBMS_SQL.EXECUTE(v_cursor2);
825:
826: EXIT WHEN v_NumRows < v_BatchSize;

Line 824: v_return_code := DBMS_SQL.EXECUTE(v_cursor2);

820: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
821: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
822: DBMS_SQL.BIND_ARRAY(v_cursor2,':trx_date',v_trx_date);
823:
824: v_return_code := DBMS_SQL.EXECUTE(v_cursor2);
825:
826: EXIT WHEN v_NumRows < v_BatchSize;
827:
828: END LOOP;

Line 830: DBMS_SQL.CLOSE_CURSOR(v_cursor1);

826: EXIT WHEN v_NumRows < v_BatchSize;
827:
828: END LOOP;
829: COMMIT;
830: DBMS_SQL.CLOSE_CURSOR(v_cursor1);
831: DBMS_SQL.CLOSE_CURSOR(v_cursor2);
832:
833: end;
834:

Line 831: DBMS_SQL.CLOSE_CURSOR(v_cursor2);

827:
828: END LOOP;
829: COMMIT;
830: DBMS_SQL.CLOSE_CURSOR(v_cursor1);
831: DBMS_SQL.CLOSE_CURSOR(v_cursor2);
832:
833: end;
834:
835:

Line 848: v_customer_id DBMS_SQL.NUMBER_TABLE;

844: v_cursor1 NUMBER;
845: v_cursor2 NUMBER;
846: v_BatchSize INTEGER := 1000;
847: v_NumRows INTEGER;
848: v_customer_id DBMS_SQL.NUMBER_TABLE;
849: v_site_use_id DBMS_SQL.NUMBER_TABLE;
850: v_currency_code DBMS_SQL.VARCHAR2_TABLE;
851: v_trx_date DBMS_SQL.DATE_TABLE;
852: v_cum_balance DBMS_SQL.NUMBER_TABLE;

Line 849: v_site_use_id DBMS_SQL.NUMBER_TABLE;

845: v_cursor2 NUMBER;
846: v_BatchSize INTEGER := 1000;
847: v_NumRows INTEGER;
848: v_customer_id DBMS_SQL.NUMBER_TABLE;
849: v_site_use_id DBMS_SQL.NUMBER_TABLE;
850: v_currency_code DBMS_SQL.VARCHAR2_TABLE;
851: v_trx_date DBMS_SQL.DATE_TABLE;
852: v_cum_balance DBMS_SQL.NUMBER_TABLE;
853: v_return_code INTEGER;

Line 850: v_currency_code DBMS_SQL.VARCHAR2_TABLE;

846: v_BatchSize INTEGER := 1000;
847: v_NumRows INTEGER;
848: v_customer_id DBMS_SQL.NUMBER_TABLE;
849: v_site_use_id DBMS_SQL.NUMBER_TABLE;
850: v_currency_code DBMS_SQL.VARCHAR2_TABLE;
851: v_trx_date DBMS_SQL.DATE_TABLE;
852: v_cum_balance DBMS_SQL.NUMBER_TABLE;
853: v_return_code INTEGER;
854: text_select VARCHAR2(4000);

Line 851: v_trx_date DBMS_SQL.DATE_TABLE;

847: v_NumRows INTEGER;
848: v_customer_id DBMS_SQL.NUMBER_TABLE;
849: v_site_use_id DBMS_SQL.NUMBER_TABLE;
850: v_currency_code DBMS_SQL.VARCHAR2_TABLE;
851: v_trx_date DBMS_SQL.DATE_TABLE;
852: v_cum_balance DBMS_SQL.NUMBER_TABLE;
853: v_return_code INTEGER;
854: text_select VARCHAR2(4000);
855: text_update VARCHAR2(4000);

Line 852: v_cum_balance DBMS_SQL.NUMBER_TABLE;

848: v_customer_id DBMS_SQL.NUMBER_TABLE;
849: v_site_use_id DBMS_SQL.NUMBER_TABLE;
850: v_currency_code DBMS_SQL.VARCHAR2_TABLE;
851: v_trx_date DBMS_SQL.DATE_TABLE;
852: v_cum_balance DBMS_SQL.NUMBER_TABLE;
853: v_return_code INTEGER;
854: text_select VARCHAR2(4000);
855: text_update VARCHAR2(4000);
856: begin

Line 952: v_cursor1 := dbms_sql.open_cursor;

948: and SITE_USE_ID = :site_use_id
949: and CURRENCY = :currency_code
950: and AS_OF_DATE = :as_of_date';
951:
952: v_cursor1 := dbms_sql.open_cursor;
953: v_cursor2 := dbms_sql.open_cursor;
954:
955: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
956: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);

Line 953: v_cursor2 := dbms_sql.open_cursor;

949: and CURRENCY = :currency_code
950: and AS_OF_DATE = :as_of_date';
951:
952: v_cursor1 := dbms_sql.open_cursor;
953: v_cursor2 := dbms_sql.open_cursor;
954:
955: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
956: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
957:

Line 955: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);

951:
952: v_cursor1 := dbms_sql.open_cursor;
953: v_cursor2 := dbms_sql.open_cursor;
954:
955: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
956: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
957:
958: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
959: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);

Line 956: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);

952: v_cursor1 := dbms_sql.open_cursor;
953: v_cursor2 := dbms_sql.open_cursor;
954:
955: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
956: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
957:
958: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
959: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
960: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);

Line 958: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);

954:
955: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
956: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
957:
958: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
959: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
960: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
961: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
962: dbms_sql.define_array(v_cursor1,5,v_cum_balance,v_BatchSize,1);

Line 959: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);

955: dbms_sql.parse(v_cursor1,text_select,DBMS_SQL.V7);
956: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
957:
958: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
959: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
960: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
961: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
962: dbms_sql.define_array(v_cursor1,5,v_cum_balance,v_BatchSize,1);
963:

Line 960: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);

956: dbms_sql.parse(v_cursor2,text_update,DBMS_SQL.V7);
957:
958: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
959: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
960: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
961: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
962: dbms_sql.define_array(v_cursor1,5,v_cum_balance,v_BatchSize,1);
963:
964: v_return_code := dbms_sql.execute(v_cursor1);

Line 961: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);

957:
958: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
959: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
960: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
961: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
962: dbms_sql.define_array(v_cursor1,5,v_cum_balance,v_BatchSize,1);
963:
964: v_return_code := dbms_sql.execute(v_cursor1);
965:

Line 962: dbms_sql.define_array(v_cursor1,5,v_cum_balance,v_BatchSize,1);

958: dbms_sql.define_array(v_cursor1,1,v_customer_id,v_BatchSize,1);
959: dbms_sql.define_array(v_cursor1,2,v_site_use_id,v_BatchSize,1);
960: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
961: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
962: dbms_sql.define_array(v_cursor1,5,v_cum_balance,v_BatchSize,1);
963:
964: v_return_code := dbms_sql.execute(v_cursor1);
965:
966: --This is the fetch loop. Each call to FETCH_ROWS will retrive v_BatchSize

Line 964: v_return_code := dbms_sql.execute(v_cursor1);

960: dbms_sql.define_array(v_cursor1,3,v_currency_code,v_BatchSize,1);
961: dbms_sql.define_array(v_cursor1,4,v_trx_date,v_BatchSize,1);
962: dbms_sql.define_array(v_cursor1,5,v_cum_balance,v_BatchSize,1);
963:
964: v_return_code := dbms_sql.execute(v_cursor1);
965:
966: --This is the fetch loop. Each call to FETCH_ROWS will retrive v_BatchSize
967: --rows of data. The loop is over when FETCH_ROWS returns a value< v_BatchSize.
968:

Line 977: v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);

973: v_currency_code.delete;
974: v_trx_date.delete;
975: v_cum_balance.delete;
976:
977: v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
978: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
979: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
980: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
981: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);

Line 978: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);

974: v_trx_date.delete;
975: v_cum_balance.delete;
976:
977: v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
978: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
979: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
980: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
981: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
982: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_cum_balance);

Line 979: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);

975: v_cum_balance.delete;
976:
977: v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
978: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
979: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
980: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
981: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
982: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_cum_balance);
983:

Line 980: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);

976:
977: v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
978: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
979: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
980: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
981: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
982: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_cum_balance);
983:
984: --The special case of v_NumRows = 0 needs to be checked here. This

Line 981: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);

977: v_NumRows := DBMS_SQL.FETCH_ROWS(v_cursor1);
978: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
979: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
980: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
981: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
982: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_cum_balance);
983:
984: --The special case of v_NumRows = 0 needs to be checked here. This
985: --means that the previous fetch returned all the remaining rows and

Line 982: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_cum_balance);

978: DBMS_SQL.COLUMN_VALUE(v_cursor1,1,v_customer_id);
979: DBMS_SQL.COLUMN_VALUE(v_cursor1,2,v_site_use_id);
980: DBMS_SQL.COLUMN_VALUE(v_cursor1,3,v_currency_code);
981: DBMS_SQL.COLUMN_VALUE(v_cursor1,4,v_trx_date);
982: DBMS_SQL.COLUMN_VALUE(v_cursor1,5,v_cum_balance);
983:
984: --The special case of v_NumRows = 0 needs to be checked here. This
985: --means that the previous fetch returned all the remaining rows and
986: --therefore we are done with the loop.

Line 995: DBMS_SQL.BIND_ARRAY(v_cursor2,':cum_balance',v_cum_balance);

991:
992: --Use BIND_ARRAYS to specify the input variables for the insert.
993: --only elements 1..V_NumRows will be used.
994:
995: DBMS_SQL.BIND_ARRAY(v_cursor2,':cum_balance',v_cum_balance);
996: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
997: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
998: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
999: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);

Line 996: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);

992: --Use BIND_ARRAYS to specify the input variables for the insert.
993: --only elements 1..V_NumRows will be used.
994:
995: DBMS_SQL.BIND_ARRAY(v_cursor2,':cum_balance',v_cum_balance);
996: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
997: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
998: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
999: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
1000: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);

Line 997: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);

993: --only elements 1..V_NumRows will be used.
994:
995: DBMS_SQL.BIND_ARRAY(v_cursor2,':cum_balance',v_cum_balance);
996: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
997: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
998: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
999: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
1000: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
1001:

Line 998: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);

994:
995: DBMS_SQL.BIND_ARRAY(v_cursor2,':cum_balance',v_cum_balance);
996: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
997: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
998: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
999: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
1000: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
1001:
1002: v_return_code := DBMS_SQL.EXECUTE(v_cursor2);

Line 999: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);

995: DBMS_SQL.BIND_ARRAY(v_cursor2,':cum_balance',v_cum_balance);
996: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
997: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
998: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
999: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
1000: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
1001:
1002: v_return_code := DBMS_SQL.EXECUTE(v_cursor2);
1003:

Line 1000: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);

996: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
997: DBMS_SQL.BIND_ARRAY(v_cursor2,':customer_id',v_customer_id);
998: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
999: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
1000: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
1001:
1002: v_return_code := DBMS_SQL.EXECUTE(v_cursor2);
1003:
1004: EXIT WHEN v_NumRows < v_BatchSize;

Line 1002: v_return_code := DBMS_SQL.EXECUTE(v_cursor2);

998: DBMS_SQL.BIND_ARRAY(v_cursor2,':site_use_id',v_site_use_id);
999: DBMS_SQL.BIND_ARRAY(v_cursor2,':currency_code',v_currency_code);
1000: DBMS_SQL.BIND_ARRAY(v_cursor2,':as_of_date',v_trx_date);
1001:
1002: v_return_code := DBMS_SQL.EXECUTE(v_cursor2);
1003:
1004: EXIT WHEN v_NumRows < v_BatchSize;
1005: COMMIT;
1006: END LOOP;

Line 1008: DBMS_SQL.CLOSE_CURSOR(v_cursor1);

1004: EXIT WHEN v_NumRows < v_BatchSize;
1005: COMMIT;
1006: END LOOP;
1007: COMMIT;
1008: DBMS_SQL.CLOSE_CURSOR(v_cursor1);
1009: DBMS_SQL.CLOSE_CURSOR(v_cursor2);
1010:
1011: end;
1012:

Line 1009: DBMS_SQL.CLOSE_CURSOR(v_cursor2);

1005: COMMIT;
1006: END LOOP;
1007: COMMIT;
1008: DBMS_SQL.CLOSE_CURSOR(v_cursor1);
1009: DBMS_SQL.CLOSE_CURSOR(v_cursor2);
1010:
1011: end;
1012:
1013: END IF; --is credit management installed