CREATE OR REPLACE PROCEDURE mig_sourceprocresult AS CURSOR cur_sourceprocresult IS SELECT sprid,creationdate,mutationdate,spaid,spuid,participantid,participantgroup,oldparticipantgroup,oldstartdate,oldparticipantid,batchrunid,sprstatus,paidflag,elapsdays,errorfilecount,startdate,enddate,filename,opsbank_ii_ref FROM sourceprocresult spr, legacy_mapping_items l WHERE spr.spuid = l.legacy_ref AND legacy_table_name='SOURCEPROCUNIT' AND legacy_column_name='SPU_ID' AND opsbank_ii_table_name='PARCEL_UNITS' AND opsbank_ii_column_name='PARCEL_UNIT_ID' AND spaid IN(2,6,68,71,75,77,79,82,84,125,129,135,137,164,165,174,177,182,185,196,200,206,210,216,220,233,235,278,336,338,776,778,780,782,784,797,799,870,873,876,879,882,885,888,894,898,902,905,908,910,913,916,919,921,924,927,928,929,930,932,934,936,941,944) ORDER BY sprid; v_sprid obi_sourceprocresult.sprid%TYPE; v_prev_sprid obi_sourceprocresult.sprid%TYPE; v_creationdate obi_sourceprocresult.creationdate%TYPE; v_mutationdate obi_sourceprocresult.mutationdate%TYPE; v_spaid obi_sourceprocresult.spaid%TYPE; v_spuid obi_sourceprocresult.spuid%TYPE; v_prev_spuid obi_sourceprocresult.spuid%TYPE; v_participantid obi_sourceprocresult.participantid%TYPE; v_participantgroup obi_sourceprocresult.participantgroup%TYPE; v_participantgroup2 obi_sourceprocresult.participantgroup%TYPE; v_participantgroup4 obi_sourceprocresult.participantgroup%TYPE; v_oldparticipantgroup obi_sourceprocresult.oldparticipantgroup%TYPE; v_oldstartdate obi_sourceprocresult.oldstartdate%TYPE; v_oldparticipantid obi_sourceprocresult.oldparticipantid%TYPE; v_batchrunid obi_sourceprocresult.batchrunid%TYPE; v_sprstatus obi_sourceprocresult.sprstatus%TYPE; v_paidflag obi_sourceprocresult.paidflag%TYPE; v_elapsdays obi_sourceprocresult.elapsdays%TYPE; v_errorfilecount obi_sourceprocresult.errorfilecount%TYPE; v_startdate obi_sourceprocresult.startdate%TYPE; v_enddate obi_sourceprocresult.enddate%TYPE; v_filename obi_sourceprocresult.filename%TYPE; v_supplier_prcsg_order_id supplier_processing_orders.supplier_prcsg_order_id%TYPE; v_parcel_unit_id supplier_processing_orders.parcel_unit_id%TYPE; v_processing_order_date supplier_processing_orders.processing_order_date%TYPE; v_prcsg_order_complete_ind supplier_processing_orders.prcsg_order_complete_ind%TYPE; -- v_processing_order_due_date supplier_processing_orders.processing_order_due_date%TYPE; v_party_supl_prcsg_order_id supplier_processing_orders.party_supplier_prcsg_order_id%TYPE; v_supl_prcsg_order_type VARCHAR2(100); v_input_prcsg_delivery_id supplier_processing_deliveries.input_prcsg_delivery_id%TYPE; v_supl_delivery_type_clid supplier_processing_deliveries.supplier_delivery_type_cl_id%TYPE; v_input_prcsg_delivery_date supplier_processing_deliveries.SUPPLIER_PRCSG_DELIVERY_DATE%TYPE; v_suplr_prcsg_order_act_sno suplr_prcsg_order_activities.suplr_prcsg_order_actvy_seqno%TYPE; v_input_activity_id suplr_prcsg_order_activities.input_activity_id%TYPE; v_processing_activity_desc suplr_prcsg_order_activities.processing_activity_desc%TYPE; -- v_prcsg_order_complete_ind supplier_procssing_orders.prcsg_order_complete_ind%TYPE; v_efctv_to_date parties.efctv_to_date%TYPE; /* Change the default value */ v_efctv_from_date parties.efctv_from_date%TYPE := '01-JAN-1700'; v_creation_date legacy_mapping_items.creation_date%TYPE:=SYSDATE; v_legacy_mapping_item_id legacy_mapping_items.legacy_mapping_item_id%TYPE; v_created_by_user_id legacy_mapping_items.created_by_user_id%TYPE :=1; /* Default migration user */ v_last_update_by_user_id legacy_mapping_items.last_update_by_user_id%type := 1; v_mutation_date legacy_mapping_items.last_update_date%TYPE; v_start_date DATE; v_end_date DATE; v_country_id countries.country_id%TYPE; v_counter NUMBER:=0; v_counter2 NUMBER:=0; v_counter_commit NUMBER:=0; v_counter5 NUMBER:=0; v_table_name VARCHAR2(100) :=' Default value no_table'; exception1 EXCEPTION; BEGIN DBMS_OUTPUT.PUT_LINE('Migration of SourceProcResult started..'); EXECUTE IMMEDIATE 'ALTER SESSION SET cursor_sharing=SIMILAR'; EXECUTE IMMEDIATE 'insert into sourceprocresult SELECT * FROM obi_sourceprocresult'; EXECUTE IMMEDIATE 'CREATE INDEX spr_sprid ON sourceprocresult(sprid)'; EXECUTE IMMEDIATE 'CREATE INDEX spr_spuid ON sourceprocresult(spuid)'; EXECUTE IMMEDIATE 'CREATE BITMAP INDEX spa_spuid ON sourceprocresult(spaid)'; v_table_name:='Stats generation'; DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'OB2',DEGREE=>8); v_supl_delivery_type_clid:=1182; SELECT count(1) INTO v_counter FROM sourceprocresult spr, legacy_mapping_items l WHERE spr.spuid = l.legacy_ref AND legacy_table_name='SOURCEPROCUNIT' AND legacy_column_name='SPU_ID' AND opsbank_ii_table_name='PARCEL_UNITS' AND opsbank_ii_column_name='PARCEL_UNIT_ID' AND spaid IN(2,6,68,71,75,77,79,82,84,125,129,135,137,164,165,174,177,182,185,196,200,206,210,216,220,233,235,278,336,338,776,778,780,782,784,797,799,870,873,876,879,882,885,888,894,898,902,905,908,910,913,916,919,921,924,927,928,929,930,932,934,936,941,944); IF v_counter = 0 THEN RAISE exception1; END IF; OPEN cur_sourceprocresult; LOOP FETCH cur_sourceprocresult INTO v_sprid,v_creationdate,v_mutationdate,v_spaid,v_spuid,v_participantid,v_participantgroup,v_oldparticipantgroup,v_oldstartdate,v_oldparticipantid,v_batchrunid,v_sprstatus,v_paidflag,v_elapsdays,v_errorfilecount,v_startdate,v_enddate,v_filename,v_parcel_unit_id; EXIT WHEN cur_sourceprocresult%NOTFOUND; /* Change the date format of v_creationdate , v_mutationdate and v_changedate to Oracle Date format */ /* STEP 1 */ /* Insert record in supplier processing orders for unique value of spuid and participantgroup */ v_party_supl_prcsg_order_id:=NULL; v_input_activity_id:=NULL; v_creation_date:= to_date(v_creationdate,'YYYYMMDD'); v_mutation_date:= to_date(v_mutationdate,'YYYYMMDD'); IF v_startdate <> ' ' THEN v_start_date := to_date(v_startdate,'YYYYMMDD'); ELSE v_start_date:=NULL; END IF; IF v_enddate <> ' ' THEN v_end_date := to_date(v_enddate,'YYYYMMDD'); ELSE v_end_date:=NULL; END IF; SELECT INPUT_PROCESSING_ORDERS_SEQ.NEXTVAL INTO v_supplier_prcsg_order_id FROM DUAL; IF v_sprstatus = 'S' THEN v_prcsg_order_complete_ind:='Y'; ELSE v_prcsg_order_complete_ind:='N'; END IF; v_table_name:='Assign input_activity_id ..'; IF v_spaid = 2 OR v_spaid = 71 OR v_spaid =77 OR v_spaid = 82 OR v_spaid = 125 OR v_spaid = 135 OR v_spaid = 164 OR v_spaid = 174 OR v_spaid = 182 OR v_spaid = 196 OR v_spaid = 206 OR v_spaid = 216 OR v_spaid = 235 OR v_spaid = 336 OR v_spaid = 778 OR v_spaid = 782 OR v_spaid = 799 OR v_spaid = 870 OR v_spaid = 882 OR v_spaid = 888 OR v_spaid = 894 OR v_spaid = 898 THEN v_input_activity_id:=1; ELSIF v_spaid = 6 OR v_spaid = 75 OR v_spaid = 79 OR v_spaid = 84 OR v_spaid = 129 OR v_spaid = 137 OR v_spaid =165 OR v_spaid = 177 OR v_spaid = 185 OR v_spaid = 200 OR v_spaid = 210 OR v_spaid = 220 OR v_spaid = 905 THEN v_input_activity_id:=8; ELSIF v_spaid = 919 OR v_spaid = 233 THEN v_input_activity_id:=9; ELSIF v_spaid = 913 OR v_spaid = 944 THEN v_input_activity_id:=10; ELSIF v_spaid = 916 THEN v_input_activity_id:=11; ELSIF v_spaid = 68 THEN v_input_activity_id:=14; ELSIF v_spaid = 338 THEN v_input_activity_id:=15; ELSIF v_spaid = 902 THEN v_input_activity_id:=16; ELSIF v_spaid = 908 THEN v_input_activity_id:=17; ELSIF v_spaid = 928 OR v_spaid = 929 THEN v_input_activity_id:=23; ELSIF v_spaid = 927 THEN v_input_activity_id:=24; ELSIF v_spaid = 932 OR v_spaid = 934 OR v_spaid = 910 OR v_spaid = 921 THEN v_input_activity_id:=25; ELSIF v_spaid = 936 THEN v_input_activity_id:=26; ELSIF v_spaid = 278 THEN v_input_activity_id:=27; ELSIF v_spaid = 885 OR v_spaid = 776 THEN v_input_activity_id:=28; ELSIF v_spaid = 941 THEN v_input_activity_id:=29; ELSIF v_spaid = 924 THEN v_input_activity_id:=30; ELSIF v_spaid = 780 OR v_spaid = 784 THEN v_input_activity_id:=31; ELSIF v_spaid = 797 OR v_spaid = 873 OR v_spaid = 879 THEN v_input_activity_id:=32; ELSIF v_spaid = 876 THEN v_input_activity_id:=33; END IF; v_table_name:='SELECT obi_sourceprocresult...'; /* SELECT COUNT(1) INTO v_counter from sourceprocresult outer where sprid = (select max(to_number(sprid)) from sourceprocresult inner where spuid = v_spuid and to_number(sprid) < v_sprid); */ select COUNT(1) INTO v_counter from sourceprocresult outer, ( select max(sprid) sprid from sourceprocresult where spuid = v_spuid and sprid < v_sprid ) inner where inner.sprid = outer.sprid; v_party_supl_prcsg_order_id:=NULL; IF v_counter = 0 THEN v_party_supl_prcsg_order_id:=13; ELSE v_table_name:='SELECT sourceprocresult 2 ....'; /* select outer.participantgroup INTO v_participantgroup2 from sourceprocresult outer where sprid = (select max(sprid) from sourceprocresult inner where spuid = v_spuid and sprid < v_sprid); */ select outer.participantgroup INTO v_participantgroup2 from sourceprocresult outer, ( select max(sprid) sprid from sourceprocresult where spuid = v_spuid and sprid < v_sprid ) inner where outer.sprid = inner.sprid; IF v_participantgroup2 = ' ' THEN /* select outer.participantid INTO v_participantgroup2 from sourceprocresult outer where sprid = (select max(sprid) from sourceprocresult inner where spuid = v_spuid and sprid < v_sprid); */ select outer.participantid INTO v_participantgroup2 from sourceprocresult outer, ( select max(sprid) sprid from sourceprocresult where spuid = v_spuid and sprid < v_sprid ) inner where outer.sprid = inner.sprid; END IF; v_table_name:='SELECT Supplier Processing order type'; SELECT sp.code_item_name INTO v_supl_prcsg_order_type FROM input_activities ia, supplier_prcsg_order_types sp where ia.supplier_prcsg_order_type_clid = sp.cl_item_id AND ia.input_activity_id = v_input_activity_id; v_table_name:='Assign Participantgroup '; IF v_participantgroup2 IS NOT NULL AND v_participantgroup2 <> ' ' THEN IF to_number(v_participantgroup2) = 1 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=1; ELSIF to_number(v_participantgroup2) = 1 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=2; ELSIF to_number(v_participantgroup2) = 2 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=1; ELSIF to_number(v_participantgroup2) = 2 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=2; ELSIF to_number(v_participantgroup2) = 3 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=1; ELSIF to_number(v_participantgroup2) = 3 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=2; ELSIF to_number(v_participantgroup2) = 4 AND v_supl_prcsg_order_type = 'SSN' THEN v_party_supl_prcsg_order_id:=7; ELSIF to_number(v_participantgroup2) = 4 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=8; ELSIF to_number(v_participantgroup2) = 4 AND v_supl_prcsg_order_type = 'CLO' THEN v_party_supl_prcsg_order_id:=9; ELSIF to_number(v_participantgroup2) = 4 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=10; ELSIF to_number(v_participantgroup2) = 8 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=11; ELSIF to_number(v_participantgroup2) = 8 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=12; ELSIF to_number(v_participantgroup2) = 40 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=13; ELSIF to_number(v_participantgroup2) = 40 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=14; ELSIF to_number(v_participantgroup2) = 63 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=1; ELSIF to_number(v_participantgroup2) = 63 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=2; ELSIF to_number(v_participantgroup2) = 68 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=17; ELSIF to_number(v_participantgroup2) = 68 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=18; ELSIF to_number(v_participantgroup2) = 86 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=1; ELSIF to_number(v_participantgroup2) = 86 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=2; ELSIF to_number(v_participantgroup2) = 94 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=21; ELSIF to_number(v_participantgroup2) = 94 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=22; ELSIF to_number(v_participantgroup2) = 97 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=23; ELSIF to_number(v_participantgroup2) = 97 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=24; ELSIF to_number(v_participantgroup2) = 100 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=21; ELSIF to_number(v_participantgroup2) = 100 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=22; ELSIF to_number(v_participantgroup2) = 148 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=27; ELSIF to_number(v_participantgroup2) = 148 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=28; ELSIF to_number(v_participantgroup2) = 165 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=29; ELSIF to_number(v_participantgroup2) = 165 AND v_supl_prcsg_order_type = 'CLO' THEN v_party_supl_prcsg_order_id:=30; ELSIF to_number(v_participantgroup2) = 165 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=31; ELSIF to_number(v_participantgroup2) = 170 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=32; ELSIF to_number(v_participantgroup2) = 170 AND v_supl_prcsg_order_type = 'CLO' THEN v_party_supl_prcsg_order_id:=33; ELSIF to_number(v_participantgroup2) = 171 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=34; ELSIF to_number(v_participantgroup2) = 171 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=35; ELSIF to_number(v_participantgroup2) = 176 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=36; ELSIF to_number(v_participantgroup2) = 176 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=37; ELSIF to_number(v_participantgroup2) = 179 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=38; ELSIF to_number(v_participantgroup2) = 179 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=39; ELSIF to_number(v_participantgroup2) = 180 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=40; ELSIF to_number(v_participantgroup2) = 180 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=41; ELSIF to_number(v_participantgroup2) = 192 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=42; ELSIF to_number(v_participantgroup2) = 192 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=43; ELSIF to_number(v_participantgroup2) = 194 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=44; ELSIF to_number(v_participantgroup2) = 194 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=45; ELSIF to_number(v_participantgroup2) = 196 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=46; ELSIF to_number(v_participantgroup2) = 196 AND v_supl_prcsg_order_type = 'CLO' THEN v_party_supl_prcsg_order_id:=47; ELSIF to_number(v_participantgroup2) = 197 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=48; ELSIF to_number(v_participantgroup2) = 197 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=49; ELSIF to_number(v_participantgroup2) = 198 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=50; ELSIF to_number(v_participantgroup2) = 198 AND v_supl_prcsg_order_type = 'IO' THEN v_party_supl_prcsg_order_id:=51; ELSIF to_number(v_participantgroup2) = 199 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=52; ELSIF to_number(v_participantgroup2) = 199 AND v_supl_prcsg_order_type = 'CLO' THEN v_party_supl_prcsg_order_id:=53; ELSIF to_number(v_participantgroup2) = 200 AND v_supl_prcsg_order_type = 'CO' THEN v_party_supl_prcsg_order_id:=54; ELSIF to_number(v_participantgroup2) = 200 AND v_supl_prcsg_order_type = 'CLO' THEN v_party_supl_prcsg_order_id:=55; END IF; END IF; END IF; IF v_party_supl_prcsg_order_id IS NULL THEN v_party_supl_prcsg_order_id:= 56; END IF; v_table_name:='INSERT supplier_processing_orders 1...'; INSERT INTO supplier_processing_orders(supplier_prcsg_order_id,parcel_unit_id,processing_order_date,prcsg_order_complete_ind,party_supplier_prcsg_order_id,creation_date,created_by_user_id,last_update_date,last_update_by_user_id) VALUES(v_supplier_prcsg_order_id,v_parcel_unit_id,v_start_date,v_prcsg_order_complete_ind,v_party_supl_prcsg_order_id,v_creation_date,v_created_by_user_id,v_mutation_date,v_last_update_by_user_id); SELECT input_processing_deliver_seq.NEXTVAL INTO v_input_prcsg_delivery_id FROM DUAL; v_table_name:='INSERT supplier_processing_deliveries 1...'; INSERT INTO supplier_processing_deliveries (input_prcsg_delivery_id,supplier_prcsg_order_id,SUPPLIER_PRCSG_DELIVERY_DATE,supplier_delivery_type_cl_id,creation_date,created_by_user_id,last_update_date,last_update_by_user_id) VALUES(v_input_prcsg_delivery_id,v_supplier_prcsg_order_id,v_end_date,v_supl_delivery_type_clid,v_creation_date,v_created_by_user_id,v_mutation_date,v_last_update_by_user_id); /* STEP 2 */ IF v_prev_sprid <> v_sprid THEN v_counter:=0; END IF; v_counter:= v_counter + 1; v_suplr_prcsg_order_act_sno:=v_counter; v_table_name:='INSERT suplr_prcsg_order_activities 1...'; INSERT INTO suplr_prcsg_order_activities(supplier_prcsg_order_id,suplr_prcsg_order_actvy_seqno,input_activity_id,creation_date,created_by_user_id,last_update_date,last_update_by_user_id) VALUES(v_supplier_prcsg_order_id,v_suplr_prcsg_order_act_sno,v_input_activity_id,v_creation_date,v_created_by_user_id,v_mutation_date,v_last_update_by_user_id); /* Insert record in the Legacy table */ SELECT legacy_mapping_items_seq.NEXTVAL INTO v_legacy_mapping_item_id FROM DUAL; /*STEP 3 */ v_table_name:='INSERT legacy_mapping_items 1...'; INSERT INTO legacy_mapping_items(legacy_mapping_item_id,legacy_system_name,legacy_table_name,legacy_column_name,legacy_ref,opsbank_ii_table_name,opsbank_ii_column_name,opsbank_ii_ref,mapping_comments_text,creation_date,created_by_user_id,last_update_date,last_update_by_user_id) VALUES(v_legacy_mapping_item_id,'OPSBANK I','SOURCEPROCRESULT','SPRID',TO_CHAR(v_sprid),'SUPPLIER_PROCESSING_ORDERS','SUPPLIER_PRCSG_ORDER_ID@@SUPLR_PRCSG_ORDER_ACTVY_SEQNO',TO_CHAR(v_supplier_prcsg_order_id)||'@@'||TO_CHAR(v_suplr_prcsg_order_act_sno),'sprid column of SOURCEPROCRESULT table is mapped with supplier_prcsg_order_id and suplr_prcsg_order_actvy_seqno of SUPPLIER_PROCESSING_ORDERS table during migration',SYSDATE,v_created_by_user_id,SYSDATE,v_last_update_by_user_id); /*STEP 4 */ IF v_filename <> ' ' THEN v_table_name:='INSERT electronic_files 1...'; INSERT INTO electronic_files(electronic_file_id,input_prcsg_delivery_id,electronic_file_name,creation_date,created_by_user_id,last_update_date,last_update_by_user_id) VALUES(electronic_files_seq.NEXTVAL,v_input_prcsg_delivery_id,v_filename,v_creation_date,v_created_by_user_id,v_mutation_date,v_last_update_by_user_id); END IF; IF v_counter_commit = 10000 THEN COMMIT; v_counter_commit:=0; END IF; v_counter_commit:=v_counter_commit + 1; -- END IF; v_prev_sprid := v_sprid; END LOOP; CLOSE cur_sourceprocresult; INSERT INTO obii_registry(obii_reg_key,obii_reg_value) values ('Migration of SOURCEPROCRESULT completed successfully...',to_char(SYSDATE,'DD-MON-YYYY::HH24:MI:SS')); -- INSERT INTO obii_registry(obii_reg_key,obii_reg_key_type,obii_reg_value) VALUES('mig_sourceprocresult','VERSION','0.10'); COMMIT; DBMS_OUTPUT.PUT_LINE('Migration of SOURCEPROCRESULT is completed successfully......'); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||'No data found in the table :'||v_table_name||' for sprid '||v_sprid); DBMS_OUTPUT.PUT_LINE('Script terminated unsuccessfully ........'); WHEN exception1 THEN DBMS_OUTPUT.PUT_LINE('No data found for the cursor...'); DBMS_OUTPUT.PUT_LINE('Script terminated unsuccessfully ........'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLCODE||'table :'||v_table_name||' for sprid '||v_sprid ||' ErrorMSG : '||SQLERRM); DBMS_OUTPUT.PUT_LINE('Script terminated unsuccessfully ........'); END mig_sourceprocresult; /