Q:
Oracle migration error - help me to solve ORA-06544
ORA-06544, Oracle error messages, Oracle 10g migration, Oracle 11g migration, Oracle 10g, Oracle 11g, PL/SQL, ORA-00604, Oracle 10g error messages I migrating data from one database server connection to other server connection using database link from 10g to 11g. I am not able to understand the cause of this error and how to solve it...
On compiling the code I am getting the error
Error(1): ORA-04052: error occurred when looking up remote object DBO.INVOICE_DETAIL@LNK_DBO_12 ORA-00604: error occurred at recursive SQL level 1 ORA-06544: PL/SQL: internal error, arguments: [55916], [], [], [], [], [], [], [] ORA-06553: PLS-801: internal error
Please help me as early as possible........
On compiling the code I am getting the error
Error(1): ORA-04052: error occurred when looking up remote object DBO.INVOICE_DETAIL@LNK_DBO_12 ORA-00604: error occurred at recursive SQL level 1 ORA-06544: PL/SQL: internal error, arguments: [55916], [], [], [], [], [], [], [] ORA-06553: PLS-801: internal error
create or replace
PACKAGE BODY "DBO_MIGRATION" IS
-- lnk_dbo_12 Database Link for DBO_LIVE Current
-- lnk_dbo_13 Database Link for DBO_13
-- dd_25 Database Link for DD_25
PROCEDURE DBO_TO_DD_MIGRATION AS
CURSOR c1 IS
SELECT CLIENT_CODE, MGRP, NAME, STATUS, FILE_PATIENT, CLIENT_TYPE, EMAIL_1, EMAIL_CC, EMAIL_BCC, MERGE_DOC, PBREAK, log_username, log_password,
DELIVERY_FAX_NO, AS_PER_GROUP, TBO, STREET, AREA, CITY, STATE, ZIPCODE, COUNTRY, PHONE1
FROM client_profile@lnk_dbo_12
WHERE (client_code=mgrp OR mgrp='B') AND upper(status)='CURRENT' ORDER BY CLIENT_CODE;
c1_rec c1 % rowtype; -- (MGRP IN ('ACN','CSNAC') or CLIENT_CODE='02555')
-------- Variables for Static Values
v_ac_status dd.ac.ac_status%type := 1; -- ACCOUNT_STATUS for migrated client is default 1. (Current)
v_inv_frequency dd.ac.inv_frequency%type := 3; -- INVOICE FREQUENCY is Default 3 = Monthly
v_email varchar2(100); -- Comma Seprated EMIAL
v_asp_fk dd.asp.asp_pk%type := 1; -- ASP_FK is 1 For Cybernation Infotech
v_ac_type_fk dd.ac_types.ac_type_pk%type := 1; -- AC_TYPE_FK = 1 FOR Cybernation Infotech for Medical Transcription Service
v_entity_type_fk_ac dd.entity_types.entity_type_pk%type := 3; -- ENTITY_TYPE_FK = 3 FOR AC(GROUP) USER
v_currency_fk dd.currency.currency_pk%type := 1; -- CURRENCY_FK = 1 FOR Cybernation Infotech USA CUSTOMERS (Currency $ )
v_status dd.login_mst.status%type := 3; -- STATUS = 3 FOR Cybernation Infotech CUSTOMERS (WEB + FTP)
v_ac_pk ac.AC_PK%type := 0;
-- Static Variable Used In Insert id AC_AC_WFPARAM
v_del_send_adminbcc AC_AU_WFPARAM.del_send_adminbcc%type := 0;
v_visual_required AC_AU_WFPARAM.visual_required%type := 1;
v_send_delivery_mail_auto AC_AU_WFPARAM.send_delivery_mail_auto%type := 0;
v_steps_to_final AC_AU_WFPARAM.steps_to_final%type := 3;
-- Variable for DBO.INVOICE_DETAILS realted fields
v_trial_start_date dbo.INVOICE_DETAIL.trial_start_date@lnk_dbo_12%type;
v_method_of_payment NUMBER; --dbo.INVOICE_DETAIL.method_of_payment%type
v_email1 dbo.INVOICE_DETAIL.email1@lnk_dbo_12%type;
v_email2 dbo.INVOICE_DETAIL.email2@lnk_dbo_12%type;
v_email3 dbo.INVOICE_DETAIL.email3@lnk_dbo_12%type;
v_invoice_fax_no dbo.INVOICE_DETAIL.invoice_fax_no@lnk_dbo_12%type;
v_sendfax dbo.INVOICE_DETAIL.sendfax@lnk_dbo_12%type;
v_trial_end_date dbo.INVOICE_DETAIL.trial_end_date@lnk_dbo_12%type;
v_inv_parameters dbo.INVOICE_DETAIL.inv_parameters@lnk_dbo_12%type;
v_pasword dd.login_mst.PASSWORD%type;
v_crypto_key dd.login_mst.crypto_key%type;
v_username VARCHAR2(50);
v_password VARCHAR2(50);
v_encrypted_pass VARCHAR2(50);
v_login_id number := 0;
v_wfparam_id NUMBER;
cnt number := 0;
v_group_name_b VARCHAR2(20);
v_mgrp_b VARCHAR2(50);
BEGIN
BEGIN
INSERT INTO CURRENCY(CURRENCY_PK, ASP_FK, NAME, COUNTRY, SYMBOL, LAST_LOGIN_FK, LAST_MODIFIED, LAST_MC, LAST_IP) VALUES (1,1,'DOLLAR','USA','$',0,NULL,NULL,'127.0.0.1');
--@Change@
-- INSERT INTO AC_TYPES(AC_TYPE_PK,OWNER_FK,NAME,DESCRIPTION,LAST_LOGIN_FK,LAST_MODIFIED,LAST_MC,LAST_IP)
-- VALUES(1,1,'TEMP','TEMPORARY CREATED',NULL,NULL,NULL,NULL);
--@Change@
EXCEPTION WHEN OTHERS THEN
NULL;
END;
-- INSERT Discipline , Minute Formula and Drug
DISCILINE_MPROC(v_asp_fk);
MIN_FORMULA_MPROC(v_asp_fk);
--DRUG_MPROC(v_asp_fk);
OPEN c1;
LOOP
FETCH c1 INTO c1_rec;
EXIT WHEN c1 % NOTFOUND;
SELECT TRIAL_START_DATE, TRIAL_END_DATE, GET_PAY_METHOD_MFUN(METHOD_OF_PAYMENT), EMAIL1, EMAIL2, EMAIL3, INVOICE_FAX_NO, SENDFAX, INV_PARAMETERS
INTO v_trial_start_date, v_trial_end_date, v_method_of_payment, v_email1, v_email2, v_email3, v_invoice_fax_no, v_sendfax, v_inv_parameters
FROM invoice_detail@lnk_dbo_12 WHERE client_code = c1_rec.client_code;
-- Insert values in AC & AC_DBO
-- GET NECESSARY INFORMATION FROM PARENT TABLES ASP, AC_TYPES, CURRENCY
IF c1_rec.MGRP = 'B' THEN -- B GROUP CONDITION
v_mgrp_b := 'B_' || c1_rec.client_code;
v_ac_pk := CHECK_AC_DBO_MFUN(NULL, v_mgrp_b, v_group_name_b);
ELSE
v_ac_pk := CHECK_AC_DBO_MFUN(c1_rec.client_code, NULL, v_group_name_b);
v_group_name_b := c1_rec.client_code;
END IF;
IF v_ac_pk = 0 THEN
SELECT ac_seq.NEXTVAL INTO v_ac_pk FROM DUAL;
IF c1_rec.MGRP = 'B' THEN
v_group_name_b := GEN_B_GRP_NAME_FUN(c1_rec.NAME);
END IF;
cnt :=1;
end if;
AC_CP.UPD_INS(AC_PK_IN => v_ac_pk, ASP_FK_IN => v_asp_fk, AC_TYPE_FK_IN => v_ac_type_fk,
CURRENCY_FK_IN => v_currency_fk, AC_NAME_IN => v_group_name_b, AC_CREATE_DT_IN => v_trial_start_date,
AC_CLOSE_DT_IN => NULL, AC_STATUS_IN => v_ac_status, STREET_IN => NVL(c1_rec.street,'N/A'),
AREA_IN => c1_rec.area, CITY_IN => NVL(c1_rec.city,'N/A'), STATE_IN => NVL(c1_rec.state,'N/A') , ZIP_IN => NVL(c1_rec.zipcode,'N/A'),
COUNTRY_IN => nvl(c1_rec.country,'USA'), PHONE_IN => c1_rec.phone1, PHONE2_IN => NULL,
MOBILE_IN => NULL, FAX_IN => v_invoice_fax_no, EMAIL_IN => NULL, WEBSITE_IN => NULL,
TZ_GMT_DIFF_MIN_IN => NULL, pay_mode_fk_in => v_method_of_payment,
INV_FREQUENCY_IN => v_inv_frequency, INV_EMAIL_TO_IN => COMB_CSV_EMAIL_FUN(v_email1 || ',' ||v_email2 || ',' || v_email3),
INV_EMAIL_CC_IN => NULL,
INV_EMAIL_BCC_IN => 1, INV_SEND_ADMINBCC_IN => 1,
INV_FAX_NO_IN => v_invoice_fax_no, SEND_INV_FAX_IN => RET_01_FR_YN_MFUN(v_sendfax),
USER_NOTES_IN => NULL, SIGNUP_STEP_NO_IN => NULL,
LAST_LOGIN_FK_IN => 0, LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL,
LAST_IP_IN => '127.0.0.1', GEN_BY_IN => 1,
IGNORE_IF_NULL_IN => TRUE, HANDLE_ERROR_IN => FALSE);
IF cnt = 1 THEN
IF c1_rec.mgrp = 'B' THEN
INSERT INTO AC_DBO(AC_FK, MGRP, MGRP_B) VALUES(v_ac_pk, v_group_name_b, v_mgrp_b);
COMMIT;
ELSE
INSERT INTO AC_DBO(AC_FK, MGRP, MGRP_B) VALUES(v_ac_pk, c1_rec.client_code, NULL);
COMMIT;
END IF;
END IF;
-- INSERT INTO LOGIN_MST Generate CRYPTO KEY and encrypt passwords
cnt := UPDATE_LOGIN_AC_MFUN(c1_rec.CLIENT_CODE, v_ac_pk, v_entity_type_fk_ac);
-- INSERT INTO AC_AU_WFPARAM ( v_stat = 1 for AC and 0 for Author )
v_wfparam_id := CHECK_WFPARAM_MFUN(v_ac_pk, 1);
IF v_wfparam_id = 0 THEN
SELECT AC_AU_WFPARAM_SEQ.NEXTVAL INTO v_wfparam_id FROM DUAL;
END IF;
AC_AU_WFPARAM_CP.UPD_INS(WFPARAM_PK_IN => v_wfparam_id, AC_FK_IN => v_ac_pk, AUTHOR_FK_IN => null,
TBO_IN => c1_rec.TBO, DIFFICULTY_LEVEL_IN => nvl(GET_DIFF_LVL_MFUN(c1_rec.CLIENT_TYPE),0),
DEL_EMAIL_TO_IN => COMB_CSV_EMAIL_FUN(c1_rec.EMAIL_1 || ',' ||c1_rec.EMAIL_CC),
DEL_EMAIL_CC_IN => c1_rec.EMAIL_CC, DEL_EMAIL_BCC_IN => NULL, DEL_SEND_ADMINBCC_IN => v_del_send_adminbcc,
MERGE_PGBREAK_IN => NVL(GET_MERGE_PGBREAK_MFUN(c1_rec.MERGE_DOC,c1_rec.PBREAK),0),
PATIENT_FILENAME_IN => c1_rec.FILE_PATIENT, DEL_AS_PER_AC_IN => RET_01_FR_YN_MFUN(c1_rec.AS_PER_GROUP),
INV_AS_PER_AC_IN => GET_INV_PARAMETERS_MFUN(v_inv_parameters), DEL_FAX_NO_IN => c1_rec.DELIVERY_FAX_NO,
TRIAL_START_DT_IN => v_trial_start_date, TRIAL_END_DT_IN => v_trial_end_date,
LAST_LOGIN_FK_IN => 0, LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1', DEL_DOC_FILE_TYPE_IN => NULL,
VISUAL_REQUIRED_IN => v_visual_required, SEND_DELIVERY_MAIL_AUTO_IN => v_send_delivery_mail_auto,
STEPS_TO_FINAL_IN => v_steps_to_final, TAT_REQD_IN => NULL, ADMIN_NOTES_IN => NULL,
IGNORE_IF_NULL_IN => TRUE, HANDLE_ERROR_IN => FALSE);
--- INSERT RESPECTIVE ENTRIES OF AUTHORS
IF c1_rec.MGRP='B' THEN
AUTHOR_MPROC(v_ac_pk, NULL, c1_rec.client_code);
ELSE
AUTHOR_MPROC(v_ac_pk, c1_rec.client_code, NULl);
END IF;
COMMIT;
cnt := 0;
END LOOP;
CLOSE c1;
NULL;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('DBO_TO_DD_MIGRATION ERROR = ' || SQLERRM );
NULL;
END DBO_TO_DD_MIGRATION;
--------------------------------------------------------------------------------------------------------------------
PROCEDURE AUTHOR_MPROC(v_ac_fk IN NUMBER, v_mgrp IN VARCHAR2, v_client_b IN VARCHAR2) AS
CURSOR c2 IS
SELECT CLIENT_CODE, NAME, NAME_SUFFIX, DISCIPLINE, STATUS, FILE_PATIENT, CLIENT_TYPE, EMAIL_1, EMAIL_CC, EMAIL_BCC,
MERGE_DOC, formulaid, PBREAK, log_username, log_password, DELIVERY_FAX_NO, AS_PER_GROUP, TBO, STREET, AREA, CITY, STATE, ZIPCODE, COUNTRY, PHONE1
FROM client_profile@lnk_dbo_12
WHERE (MGRP=v_mgrp OR CLIENT_CODE = v_client_b) AND upper(status)='CURRENT' AND CLIENT_CODE!= MGRP ORDER BY NAME;
c1_rec c2 % rowtype;
v_entity_type_fk_auth dd.entity_types.entity_type_pk%type := 4; -- ENTITY_TYPE_FK = 4 FOR AUTHOR(CLIENT/DOCTOR) USER
v_asp_fk dd.asp.asp_pk%type := 1; -- ASP_FK is 1 For Cybernation Infotech
v_status dd.login_mst.status%type := 3; -- STATUS = 3 FOR Cybernation Infotech CUSTOMERS (WEB + FTP)
v_first_name dd.author.first_name%type;
v_last_name dd.author.last_name%type;
v_suffix dd.author.SUFFIX_NAME%type;
v_tmp_suffix dd.author.SUFFIX_NAME%type;
v_pasword dd.login_mst.PASSWORD%type;
v_crypto_key dd.login_mst.crypto_key%type;
v_username VARCHAR2(50);
v_password VARCHAR2(50);
v_encrypted_pass VARCHAR2(50);
v_login_id number := 0;
v_ac_status dd.ac.ac_status%type := 1;
-- Static Variable Used In Insert id AC_AC_WFPARAM
v_del_send_adminbcc AC_AU_WFPARAM.del_send_adminbcc%type := 0;
v_visual_required AC_AU_WFPARAM.visual_required%type := 1;
v_send_delivery_mail_auto AC_AU_WFPARAM.send_delivery_mail_auto%type := 0;
v_steps_to_final AC_AU_WFPARAM.steps_to_final%type := 3;
-- Variable for DBO.INVOICE_DETAILS realted fields
v_trial_start_date dbo.INVOICE_DETAIL.trial_start_date@lnk_dbo_12%type;
v_method_of_payment NUMBER; --dbo.INVOICE_DETAIL.method_of_payment%type
v_email1 dbo.INVOICE_DETAIL.email1@lnk_dbo_12%type;
v_email2 dbo.INVOICE_DETAIL.email2@lnk_dbo_12%type;
v_email3 dbo.INVOICE_DETAIL.email3@lnk_dbo_12%type;
v_invoice_fax_no dbo.INVOICE_DETAIL.invoice_fax_no@lnk_dbo_12%type;
v_sendfax dbo.INVOICE_DETAIL.sendfax@lnk_dbo_12%type;
v_trial_end_date dbo.INVOICE_DETAIL.trial_end_date@lnk_dbo_12%type;
v_inv_parameters dbo.INVOICE_DETAIL.inv_parameters@lnk_dbo_12%type;
v_access_code AUTHOR.ACCESS_CODE_FK%type;
v_server_pair NUMBER := 2;
v_author_pk number := 0;
v_wfparam_id NUMBER;
cnt number := 0;
BEGIN
OPEN c2;
LOOP
FETCH c2 INTO c1_rec;
EXIT WHEN c2 % NOTFOUND;
SELECT GET_PAY_METHOD_MFUN(METHOD_OF_PAYMENT), EMAIL1, EMAIL2, EMAIL3, INVOICE_FAX_NO, SENDFAX, INV_PARAMETERS
INTO v_method_of_payment, v_email1, v_email2, v_email3, v_invoice_fax_no, v_sendfax, v_inv_parameters
FROM invoice_detail@lnk_dbo_12 WHERE client_code = c1_rec.client_code;
IF v_client_b IS NULL THEN
select TRIAL_START_DATE, TRIAL_END_DATE into v_trial_start_date, v_trial_end_date
from invoice_detail@lnk_dbo_12 where client_code= v_mgrp;
GENERATE_AU_DR_NAME_MPROC(TRIM(SUBSTR(c1_rec.name,INSTR(c1_rec.name,'-')+1)),' ',v_first_name, v_last_name, v_suffix);
ELSE
select TRIAL_START_DATE, TRIAL_END_DATE into v_trial_start_date, v_trial_end_date
from invoice_detail@lnk_dbo_12 where client_code= v_client_b;
GENERATE_AU_DR_NAME_MPROC(c1_rec.name,' ',v_first_name, v_last_name, v_suffix);
END IF;
v_author_pk := CHECK_AUTHOR_MFUN(v_ac_fk, v_first_name, v_last_name);
IF v_author_pk = 0 THEN
SELECT AUTHOR_SEQ.NEXTVAL INTO v_author_pk FROM DUAL;
cnt := 1;
end if;
BEGIN
SELECT access_code_pk INTO v_access_code FROM access_code WHERE asp_fk = v_asp_fk AND server_pair_fk =v_server_pair AND access_code = c1_rec.client_code;
EXCEPTION
WHEN OTHERS THEN
--@@@@@@@Sohildbms_output.put_line('SELECT access_code_pk INTO v_access_code FROM access_code WHERE asp_fk = '|| v_asp_fk||' AND server_pair_fk ='||v_server_pair||' AND access_code = '||c1_rec.client_code);
DBMS_OUTPUT.PUT_LINE('ACCESS CODE ERROR = ' || SQLERRM );
NULL;
END;
IF c1_rec.NAME_SUFFIX IS NULL THEN
v_tmp_suffix := v_suffix;
ELSE
v_tmp_suffix := c1_rec.NAME_SUFFIX;
END IF;
-- INSERT INTO AUTHOR
AUTHOR_CP.UPD_INS(AUTHOR_PK_IN => v_author_pk, AC_FK_IN => v_ac_fk,
ACCESS_CODE_FK_IN => v_access_code, PREFIX_NAME_IN => NULL,
FIRST_NAME_IN => NVL(v_first_name, c1_rec.name), LAST_NAME_IN => NVL(v_last_name, c1_rec.name),
SUFFIX_NAME_IN => v_tmp_suffix, AU_CREATE_DT_IN => v_trial_start_date,
AU_CLOSE_DT_IN => NULL, AU_STATUS_IN => v_ac_status,
STREET_IN => c1_rec.STREET, AREA_IN => c1_rec.AREA,
CITY_IN => c1_rec.CITY, STATE_IN => c1_rec.STATE,
ZIP_IN => c1_rec.ZIPCODE, COUNTRY_IN => c1_rec.COUNTRY,
PHONE_IN => c1_rec.PHONE1, PHONE2_IN => NULL, MOBILE_IN => NULL,
FAX_IN => c1_rec.DELIVERY_FAX_NO, EMAIL_IN => COMB_CSV_EMAIL_FUN(c1_rec.EMAIL_1 || ',' ||c1_rec.EMAIL_CC),
DISCIPLINE_FK_IN => GET_DISCIPLINE_ID_MFUN(c1_rec.DISCIPLINE,0), TZ_GMT_DIFF_MIN_IN => NULL,
USER_NOTES_IN => NULL, LAST_LOGIN_FK_IN => 0, LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1',
IGNORE_IF_NULL_IN => TRUE, HANDLE_ERROR_IN => FALSE);
COMMIT;
-- INSERT INTO LOGIN_MST Generate CRYPTO KEY and encrypt passwords (To Be Implemented Letter)
--cnt := UPDATE_LOGIN_AU_MFUN(c1_rec.client_code, v_author_pk, v_entity_type_fk_auth);
-- INSERT INTO AUTHOR_MINUTE_FORMULA
cnt := UPDATE_AU_MIN_FORMULA_MFUN(v_author_pk, c1_rec.formulaid);
-- INSERT INTO AC_AU_WFPARAM ( v_stat = 1 for AC and 0 for Author )
v_wfparam_id := CHECK_WFPARAM_MFUN(v_author_pk, 0);
IF v_wfparam_id = 0 THEN
SELECT AC_AU_WFPARAM_SEQ.NEXTVAL INTO v_wfparam_id FROM DUAL;
END IF;
AC_AU_WFPARAM_CP.UPD_INS(WFPARAM_PK_IN => v_wfparam_id, AC_FK_IN => v_ac_fk,
AUTHOR_FK_IN => v_author_pk, TBO_IN => c1_rec.TBO,
DIFFICULTY_LEVEL_IN => nvl(GET_DIFF_LVL_MFUN(c1_rec.CLIENT_TYPE),0),
DEL_EMAIL_TO_IN => COMB_CSV_EMAIL_FUN(c1_rec.EMAIL_1 || ',' ||c1_rec.EMAIL_CC),
DEL_EMAIL_CC_IN => c1_rec.EMAIL_CC, DEL_EMAIL_BCC_IN => NULL, DEL_SEND_ADMINBCC_IN => v_del_send_adminbcc,
MERGE_PGBREAK_IN => NVL(GET_MERGE_PGBREAK_MFUN(c1_rec.MERGE_DOC,c1_rec.PBREAK),0),
PATIENT_FILENAME_IN => c1_rec.FILE_PATIENT, DEL_AS_PER_AC_IN => RET_01_FR_YN_MFUN(c1_rec.AS_PER_GROUP),
INV_AS_PER_AC_IN => GET_INV_PARAMETERS_MFUN(v_inv_parameters), DEL_FAX_NO_IN => c1_rec.DELIVERY_FAX_NO,
TRIAL_START_DT_IN => v_trial_start_date, TRIAL_END_DT_IN => v_trial_end_date,
LAST_LOGIN_FK_IN => 0, LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1', DEL_DOC_FILE_TYPE_IN => NULL,
VISUAL_REQUIRED_IN => v_visual_required, SEND_DELIVERY_MAIL_AUTO_IN => v_send_delivery_mail_auto,
STEPS_TO_FINAL_IN => v_steps_to_final, TAT_REQD_IN => NULL, ADMIN_NOTES_IN => NULL,
IGNORE_IF_NULL_IN => TRUE, HANDLE_ERROR_IN => FALSE);
COMMIT;
-- MIGRATE PATIENTS AND REF_DOC (To Be Implemented Letter)
--PATIENTS_MPROC( v_ac_fk, v_author_pk, c1_rec.client_code); (Done)
-- HOSPITAL_MPROC To Migrate Hospitals
--HOSPITAL_MPROC( v_asp_fk, v_author_pk);
COMMIT;
END LOOP;
CLOSE c2;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('AUTHOR_MPROC ERROR = ' || SQLERRM );
NULL;
END AUTHOR_MPROC;
--------------------------------------------------------------------------------------------------------------------
PROCEDURE DISCILINE_MPROC( v_asp_fk IN NUMBER) AS
CURSOR c1 IS
SELECT DISTINCT DISCIPLINENAME FROM DISCIPLINE_MST@lnk_dbo_12
WHERE DISCIPLINENAME IS NOT NULL ORDER BY DISCIPLINENAME;
c1_rec c1 % ROWTYPE;
v_cnt_dis NUMBER;
v_discipline_pk discipline.DISCIPLINE_PK%type;
BEGIN
-- Check Unique Entries in DISCIPLINE_MST
OPEN c1;
LOOP
FETCH c1 INTO c1_rec;
EXIT WHEN c1 % NOTFOUND;
SELECT COUNT(DISCIPLINE_NAME) INTO v_cnt_dis FROM DISCIPLINE WHERE DISCIPLINE_NAME=c1_rec.DISCIPLINENAME;
IF v_cnt_dis = 0 THEN
SELECT DISCIPLINE_SEQ.nextval INTO v_discipline_pk FROM DUAL;
DISCIPLINE_CP.INS(DISCIPLINE_PK_IN => v_discipline_pk,
ASP_FK_IN => v_asp_fk, DISCIPLINE_NAME_IN => c1_rec.DISCIPLINENAME,
LAST_LOGIN_FK_IN => 0, LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1',
HANDLE_ERROR_IN => FALSE);
---COMMIT;
-- ELSE
-- DBMS_OUTPUT.put_line('UPDATE');
END IF;
COMMIT;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('DISCILINE_MPROC ERROR = ' || SQLERRM );
NULL;
END DISCILINE_MPROC;
--------------------------------------------------------------------------------------------------------------------
PROCEDURE MIN_FORMULA_MPROC( v_asp_fk IN NUMBER) AS
CURSOR c1 IS
SELECT FORMULA, EXTENSION, EXT_TYPE FROM FILE_EXT_MST@lnk_dbo_12
ORDER BY FORMULAID;
c1_rec c1 % ROWTYPE;
v_cnt NUMBER;
v_minute_furmula_pk MINUTE_FORMULA.FORMULA_PK%type;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO c1_rec;
EXIT WHEN c1 % NOTFOUND;
-- Check Unique Entries in MINUTE_FORMULA
SELECT COUNT(FILE_EXTENSION) INTO v_cnt FROM MINUTE_FORMULA WHERE FILE_EXTENSION = c1_rec.EXTENSION AND FILE_TYPE_DESC=c1_rec.EXT_TYPE;
IF v_cnt = 0 THEN
SELECT MINUTE_FORMULA_SEQ.nextval INTO v_minute_furmula_pk FROM DUAL;
MINUTE_FORMULA_CP.INS(
FORMULA_PK_IN => v_minute_furmula_pk,
ASP_FK_IN => v_asp_fk,
FILE_EXTENSION_IN => c1_rec.EXTENSION,
FILE_TYPE_DESC_IN => c1_rec.EXT_TYPE,
FORMULA_IN => c1_rec.FORMULA,
LAST_LOGIN_FK_IN => 0, LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1',
HANDLE_ERROR_IN => FALSE);
/* ELSE
MINUTE_FORMULA_CP.UPD_ONECOL_PKY(
COLNAME_IN => COLNAME_IN,
NEW_VALUE_IN => c1_rec.FORMULA,
FORMULA_PK_IN => FORMULA_PK_IN,
HANDLE_ERROR_IN => FALSE);
DBMS_OUTPUT.put_line('ELSE');*/
END IF;
COMMIT;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('MIN_FORMULA_MPROC ERROR = ' || SQLERRM );
NULL;
END MIN_FORMULA_MPROC;
--------------------------------------------------------------------------------------------------------------------
FUNCTION UPDATE_LOGIN_AC_MFUN(v_client_code IN VARCHAR2, v_owner_fk IN NUMBER, v_entity_type IN NUMBER) RETURN NUMBER AS
CURSOR c1 IS
SELECT CLIENT_CODE, MGRP, NAME, EMAIL_1, EMAIL_CC, EMAIL_BCC, Log_username, log_password
FROM client_profile@lnk_dbo_12
WHERE upper(status)='CURRENT' AND CLIENT_CODE=v_client_code and (mgrp=client_code or mgrp='B') ORDER BY mgrp;
c1_rec c1 % rowtype;
v_status dd.login_mst.status%type := 3; -- STATUS = 3 FOR Cybernation Infotech CUSTOMERS (WEB + FTP)
v_pasword dd.login_mst.PASSWORD%type;
v_crypto_key dd.login_mst.crypto_key%type;
v_username VARCHAR2(50);
v_password VARCHAR2(50);
v_encrypted_pass VARCHAR2(50);
v_login_id number := 0;
flag_user number;
cnt number;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO c1_rec;
EXIT WHEN c1 % NOTFOUND;
v_login_id := CHECK_lOGIN_MST_MFUN(v_owner_fk);
-- Added For B Group Entry or c1_rec.MGRP='B'
IF c1_rec.log_username IS NULL or c1_rec.log_username='null' THEN
SELECT COUNT(*) INTO flag_user FROM client_profile@lnk_dbo_12 cp, invoice_detail@lnk_dbo_12 id WHERE upper(status)='CURRENT'
AND (cp.Log_username IS NOT NULL AND cp.Log_username!= 'null') AND id.client_code=cp.client_code
AND (cp.CLIENT_CODE=v_client_code OR MGRP = v_client_code) ORDER BY cp.mgrp;
IF flag_user > 0 THEN
BEGIN
SELECT cp.Log_username, cp.log_password INTO v_username, v_password FROM client_profile@lnk_dbo_12 cp, invoice_detail@lnk_dbo_12 id
WHERE upper(status)='CURRENT' AND id.client_code=cp.client_code
AND (cp.Log_username IS NOT NULL AND cp.Log_username!= 'null') AND (cp.CLIENT_CODE=v_client_code OR MGRP = v_client_code)
AND ROWNUM=1 ORDER BY id.TRIAL_START_DATE ;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('UPDATE_LOGIN_AC_MFUN -> Username Generation ERROR = ' || SQLERRM );
RETURN 0;
END;
ELSE
GENERATE_UNAME_PASS_MPROC(GEN_GRP_NAME_FUN(c1_rec.NAME), v_username, v_password); -- Generate Usename and Password
END IF;
ELSE
v_username := c1_rec.log_username;
v_password := c1_rec.log_password;
cnt := CHECK_lOGIN_USERNAME_MFUN(v_username);
IF cnt = 1 THEN
GENERATE_UNAME_PASS_MPROC(GEN_GRP_NAME_FUN(c1_rec.NAME), v_username, v_password);
END IF;
END IF;
IF v_login_id = 0 THEN
SELECT LOGIN_MST_SEQ.NEXTVAL INTO v_login_id FROM DUAL;
END IF;
DD_AUTH.encrypt_string(v_password, v_encrypted_pass, v_crypto_key);
LOGIN_MST_CP.UPD_INS(LOGIN_PK_IN => v_login_id , ENTITY_TYPE_FK_IN => v_entity_type,
ENTITY_OWN_FK_IN => v_owner_fk,
USERNAME_IN => v_username,
PASSWORD_IN => v_encrypted_pass,
CRYPTO_KEY_IN => v_crypto_key,
EMAIL_IN => NVL(COMB_CSV_EMAIL_FUN(c1_rec.EMAIL_1 || ',' ||c1_rec.EMAIL_CC),'N/A'),
HINT_Q_IN => 'N/A', HINT_A_IN => 'N/A', HOME_PATH_IN => NULL, HOME_PERM_IN => NULL,
PARAM_INDEX_IN => NULL, STATUS_IN => v_status, LAST_LOGIN_FK_IN => 0,
LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1',
LAST_LOGIN_IN => SYSTIMESTAMP, GEN_BY_IN => 1,
IGNORE_IF_NULL_IN => TRUE, HANDLE_ERROR_IN => FALSE);
COMMIT;
END LOOP;
CLOSE c1;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('UPDATE_LOGIN_AC_MFUN ERROR = ' || SQLERRM );
RETURN 0;
END UPDATE_LOGIN_AC_MFUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION UPDATE_LOGIN_AU_MFUN(v_client_code IN VARCHAR2, v_owner_fk IN NUMBER, v_entity_type IN NUMBER) RETURN NUMBER AS
CURSOR c1 IS
SELECT CLIENT_CODE, NAME, EMAIL_1, EMAIL_CC, EMAIL_BCC, Log_username, log_password
FROM client_profile@lnk_dbo_12
WHERE upper(status)='CURRENT' AND CLIENT_CODE=v_client_code and mgrp!=client_code ORDER BY mgrp;
c1_rec c1 % rowtype;
v_status dd.login_mst.status%type := 3; -- STATUS = 3 FOR Cybernation Infotech CUSTOMERS (WEB + FTP)
v_pasword dd.login_mst.PASSWORD%type;
v_crypto_key dd.login_mst.crypto_key%type;
v_username VARCHAR2(50);
v_password VARCHAR2(50);
v_encrypted_pass VARCHAR2(50);
v_login_id number := 0;
cnt number;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO c1_rec;
EXIT WHEN c1 % NOTFOUND;
v_login_id := CHECK_lOGIN_MST_MFUN(v_owner_fk);
IF c1_rec.log_username IS NULL or c1_rec.log_username='null' or c1_rec.log_username='NULL' THEN
GENERATE_UNAME_PASS_MPROC(GEN_GRP_NAME_FUN(c1_rec.NAME), v_username, v_password); -- Generate Usename and Password
ELSE
v_username := c1_rec.log_username;
v_password := c1_rec.log_password;
cnt := CHECK_lOGIN_USERNAME_MFUN(v_username);
IF cnt = 1 THEN
GENERATE_UNAME_PASS_MPROC(GEN_GRP_NAME_FUN(c1_rec.NAME), v_username, v_password);
END IF;
END IF;
IF v_login_id = 0 THEN
SELECT LOGIN_MST_SEQ.NEXTVAL INTO v_login_id FROM DUAL;
END IF;
DD_AUTH.encrypt_string(v_password, v_encrypted_pass, v_crypto_key);
LOGIN_MST_CP.UPD_INS(LOGIN_PK_IN => v_login_id , ENTITY_TYPE_FK_IN => v_entity_type,
ENTITY_OWN_FK_IN => v_owner_fk,
USERNAME_IN => v_username,
PASSWORD_IN => v_encrypted_pass,
CRYPTO_KEY_IN => v_crypto_key,
EMAIL_IN => NVL(COMB_CSV_EMAIL_FUN(c1_rec.EMAIL_1 || ',' ||c1_rec.EMAIL_CC),'N/A'),
HINT_Q_IN => 'N/A', HINT_A_IN => 'N/A', HOME_PATH_IN => NULL, HOME_PERM_IN => NULL,
PARAM_INDEX_IN => NULL, STATUS_IN => v_status, LAST_LOGIN_FK_IN => NULL,
LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1',
IGNORE_IF_NULL_IN => TRUE, HANDLE_ERROR_IN => FALSE);
COMMIT;
END LOOP;
CLOSE c1;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('UPDATE_LOGIN_AU_MFUN ERROR = ' || SQLERRM );
RETURN 0;
END UPDATE_LOGIN_AU_MFUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION UPDATE_AU_MIN_FORMULA_MFUN(v_author_fk IN NUMBER, v_formula_id IN NUMBER) RETURN NUMBER AS
-- Check in DD Schema for Previous AUTHOR Client Entry
v_extension dbo.file_ext_mst.extension@lnk_dbo_12%type;
v_ext_type dbo.file_ext_mst.ext_type@lnk_dbo_12%type;
v_formula_fk MINUTE_FORMULA.FORMULA_PK%type;
v_formula dbo.file_ext_mst.FORMULA@lnk_dbo_12%type;
dd_calc_type dbo.file_ext_mst.CALC_TYPE@lnk_dbo_12%type;
cnt NUMBER;
BEGIN
SELECT COUNT(AUTHOR_FK) INTO cnt FROM AU_MINUTE_FORMULA WHERE AUTHOR_FK = v_author_fk;
IF cnt = 0 THEN
SELECT FORMULA, extension, ext_type,calc_type INTO v_formula, v_extension, v_ext_type,dd_calc_type FROM file_ext_mst@lnk_dbo_12 WHERE formulaid = v_formula_id;
SELECT FORMULA_PK into v_formula_fk FROM MINUTE_FORMULA WHERE FILE_EXTENSION=v_extension AND FILE_TYPE_DESC=v_ext_type;
AU_MINUTE_FORMULA_CP.INS(FORMULA_FK_IN => v_formula_fk, AUTHOR_FK_IN => v_author_fk,
CUSTOM_FORMULA_IN => NVL(v_formula,0), LAST_LOGIN_FK_IN => 0, LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1', GET_MIN_IN => dd_calc_type, HANDLE_ERROR_IN => FALSE);
END IF;
COMMIT;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('UPDATE_AU_MIN_FORMULA_MFUN ERROR = ' || SQLERRM );
RETURN 0;
END UPDATE_AU_MIN_FORMULA_MFUN;
--------------------------------------------------------------------------------------------------------------------
PROCEDURE PATIENTS_MPROC( v_ac_fk IN NUMBER, v_author_fk IN NUMBER, v_client_code IN VARCHAR2) AS
CURSOR c1 IS
SELECT PATIENTID, FIRSTNAME, LASTNAME, CHART, DOB, SS#, DOE, REF_DOCID FROM PATIENT_MST@lnk_dbo_12 where doccode = v_client_code order by doccode;
CURSOR c2(v_ref_docid dbo.PATIENT_MST.REF_DOCID@lnk_dbo_12%type) IS
SELECT DOCID, DOCTYPEID, DOCCODE, DISCIPLINEID, FIRSTNAME, LASTNAME, DEGREE,
NATIONALITY, STREET, AREA, CITY, ZIPCODE, STATE, COUNTRY, PHONE1, PHONE2,
FAX FROM DOCTOR_MST@lnk_dbo_12 where DOCID = v_ref_docid order by docid;
c1_rec c1 % ROWTYPE;
c2_rec c2 % ROWTYPE;
v_patient_pk PATIENTS.PATIENT_PK%type;
v_ref_doc_pk REF_DOC.ref_doc_pk%type;
v_au_patient NUMBER := 0;
v_gender PATIENTS.GENDER%type := 1;
v_au_patient_pk AU_PATIENT.AUTH_PATIENT_PK%TYPE;
v_doc_name1 varchar2(50);
v_doc_name2 varchar2(50);
v_doc_name varchar2(50);
v_suffix varchar2(50);
flag_ins number := 0;
BEGIN
-- Check Unique Entries in HOSPITAL_MST
OPEN c1;
LOOP
FETCH c1 INTO c1_rec;
EXIT WHEN c1 % NOTFOUND;
BEGIN
SELECT PATIENT_PK INTO v_patient_pk FROM PATIENTS WHERE FIRST_NAME = c1_rec.FIRSTNAME AND
LAST_NAME = c1_rec.LASTNAME AND CHART = c1_rec.CHART;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.put_line('PATIENTS_MPROC ERROR --- PATIENT DOES NOT EXISTS. ' || SQLERRM);
SELECT PATIENTS_SEQ.NEXTVAL INTO v_patient_pk FROM DUAL;
END;
-- INSERT INTO PATIENTS
PATIENTS_CP.UPD_INS(PATIENT_PK_IN => v_patient_pk,
AC_FK_IN => v_ac_fk, FIRST_NAME_IN => c1_rec.FIRSTNAME,
LAST_NAME_IN => c1_rec.LASTNAME, GENDER_IN => v_gender,
DOB_IN => c1_rec.DOB, CHART_IN => c1_rec.CHART,
SSN_IN => c1_rec.SS#, STREET_IN => NULL, AREA_IN => NULL, CITY_IN => NULL,
STATE_IN => NULL, ZIP_IN => NULL, COUNTRY_IN => NULL, PHONE_IN => NULL,
PHONE2_IN => NULL, MOBILE_IN => NULL, FAX_IN => NULL,
EMAIL_IN => NULL, WEBSITE_IN => NULL, REMARKS_IN => NULL, LAST_LOGIN_FK_IN => 0,
LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1',
IGNORE_IF_NULL_IN => TRUE, HANDLE_ERROR_IN => FALSE);
COMMIT;
IF c1_rec.REF_DOCID IS NOT NULL THEN
OPEN c2(c1_rec.REF_DOCID);
LOOP
FETCH c2 INTO c2_rec;
EXIT WHEN c2 % NOTFOUND;
GENERATE_AU_DR_NAME_MPROC(c2_rec.FIRSTNAME || ' ' || c2_rec.LASTNAME,' ',v_doc_name1,v_doc_name2,v_suffix);
BEGIN
SELECT REF_DOC_PK INTO v_ref_doc_pk FROM REF_DOC WHERE FIRST_NAME = v_doc_name1 AND
LAST_NAME = v_doc_name2;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.put_line('PATIENTS_MPROC ERROR --- REF DOC DOES NOT EXISTS. ' || SQLERRM);
SELECT PATIENTS_SEQ.NEXTVAL INTO v_ref_doc_pk FROM DUAL;
END;
/* INSERT INTO REF_DOC VALUES (v_ref_doc_pk, v_ac_fk, NULL, v_doc_name1, v_doc_name2, v_suffix, c2_rec.STREET, c2_rec.AREA, c2_rec.CITY, c2_rec.STATE,
c2_rec.ZIPCODE, c2_rec.COUNTRY, c2_rec.PHONE1, c2_rec.PHONE2, NULL, NULL, NULL, NULL, 0, NULL,NULL,NULL,NULL,NULL,GET_DISCIPLINE_ID_MFUN(NULL,c2_rec.DISCIPLINEID));*/
REF_DOC_CP.UPD_INS( REF_DOC_PK_IN => v_ref_doc_pk,
AC_FK_IN => v_ac_fk, PREFIX_NAME_IN => NULL, FIRST_NAME_IN => NVL(v_doc_name1,'N/A'), LAST_NAME_IN => NVL(v_doc_name2,'N/A'),
SUFFIX_NAME_IN => v_suffix, STREET_IN => c2_rec.STREET, AREA_IN => c2_rec.AREA, CITY_IN => c2_rec.CITY, STATE_IN => c2_rec.STATE,
ZIP_IN => c2_rec.ZIPCODE, COUNTRY_IN => c2_rec.COUNTRY, PHONE_IN => c2_rec.PHONE1, PHONE2_IN => c2_rec.PHONE2,
MOBILE_IN => NULL, FAX_IN => NULL, EMAIL_IN => NULL, WEBSITE_IN => NULL,
TZ_GMT_DIFF_MIN_IN => 0, REMARKS_IN => NULL, LAST_LOGIN_FK_IN => 0, LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1',
DESCIPLINE_FK_IN => GET_DISCIPLINE_ID_MFUN(NULL,c2_rec.DISCIPLINEID),
IGNORE_IF_NULL_IN => TRUE, HANDLE_ERROR_IN => FALSE);
COMMIT;
END LOOP;
CLOSE c2;
END IF;
IF v_patient_pk > 0 THEN
-- INSERT INTO AU_PATIENTS
SELECT COUNT(AUTH_PATIENT_PK) INTO v_au_patient FROM AU_PATIENT WHERE PATIENT_FK = v_patient_pk AND AUTHOR_FK = v_author_fk;
IF v_au_patient = 0 THEN
BEGIN
SELECT AU_PATIENT_SEQ.NEXTVAL INTO v_au_patient_pk FROM DUAL;
AU_PATIENT_CP.INS(AUTH_PATIENT_PK_IN => v_au_patient_pk,
PATIENT_FK_IN => v_patient_pk, AUTHOR_FK_IN => v_author_fk,
LAST_LOGIN_FK_IN => 0, LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1',
REF_DOC_FK_IN => v_ref_doc_pk, DOE_IN => NULL, HANDLE_ERROR_IN => FALSE );
COMMIT;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('PATIENTS_MPROC - AU_PATIENT INSERT ERROR = ' || SQLERRM );
NULL;
END;
END IF;
END IF;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('PATIENTS_MPROC ERROR = ' || SQLERRM );
NULL;
END PATIENTS_MPROC;
--------------------------------------------------------------------------------------------------------------------
PROCEDURE DRUG_MPROC( v_asp_fk IN NUMBER)
AS
CURSOR c1 IS
SELECT DISTINCT DRUGNAME FROM DRUG_MST@lnk_dbo_12
WHERE DRUGNAME IS NOT NULL ORDER BY DRUGNAME;
c1_rec c1 % ROWTYPE;
v_cnt_drug NUMBER;
v_drugs_pk DRUGS.DRUG_PK%type;
BEGIN
-- Check Unique Entries in DISCIPLINE_MST
OPEN C1;
LOOP
FETCH c1 INTO c1_rec;
EXIT WHEN c1 % NOTFOUND;
SELECT COUNT(DRUG_NAME) INTO v_cnt_drug FROM DRUGS WHERE DRUG_NAME = c1_rec.DRUGNAME ;
IF v_cnt_drug = 0 THEN
SELECT DRUGS_SEQ.nextval INTO v_drugs_pk FROM DUAL;
DRUGS_CP.INS(DRUG_PK_IN => v_drugs_pk, ASP_FK_IN => v_asp_fk, DRUG_NAME_IN => c1_rec.DRUGNAME,
LAST_LOGIN_FK_IN => 0, LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1',
HANDLE_ERROR_IN => FALSE );
END IF;
COMMIT;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('DRUG_MPROC ERROR = ' || SQLERRM );
NULL;
END DRUG_MPROC;
--------------------------------------------------------------------------------------------------------------------
PROCEDURE HOSPITAL_MPROC( v_asp_fk IN NUMBER, v_author_pk IN NUMBER)
AS
CURSOR c1 IS
SELECT HNAME, ORGANIZATION, DOCCODE, STREET, CITY, ZIPCODE, STATE, COUNTRY, PHONE1, PHONE2, FAX
FROM HOSPITAL_MST@lnk_dbo_12 WHERE DOCCODE IS NOT NULL ORDER BY HNAME;
c1_rec c1 % ROWTYPE;
v_hospital_pk HOSPITALS.HOSP_PK%type;
v_cnt_hos NUMBER;
BEGIN
-- Check Unique Entries in HOSPITAL_MST
OPEN c1;
LOOP
FETCH c1 INTO c1_rec;
EXIT WHEN c1 % NOTFOUND;
--DBMS_OUTPUT.put_line(c1_rec.HNAME);
SELECT COUNT(NAME) INTO v_cnt_hos FROM HOSPITALS WHERE NAME = c1_rec.HNAME AND ASP_FK=v_asp_fk;
IF v_cnt_hos = 0 THEN
SELECT HOSPITALS_SEQ.nextval INTO v_hospital_pk FROM DUAL;
v_cnt_hos := 1;
END IF;
HOSPITALS_CP.UPD_INS( HOSP_PK_IN => v_hospital_pk,
ASP_FK_IN => v_asp_fk, NAME_IN => c1_rec.HNAME, STREET_IN => c1_rec.STREET,
AREA_IN => NULL, CITY_IN => c1_rec.CITY, STATE_IN => c1_rec.STATE,
ZIP_IN => c1_rec.ZIPcode, COUNTRY_IN => c1_rec.COUNTRY, PHONE_IN => c1_rec.PHONE1,
PHONE2_IN => c1_rec.PHONE2, MOBILE_IN => NULL, FAX_IN => c1_rec.FAX,
EMAIL_IN => NULL, WEBSITE_IN => NULL, TZ_GMT_DIFF_MIN_IN => NULL,
LAST_LOGIN_FK_IN => 0, LAST_MODIFIED_IN => NULL, LAST_MC_IN => NULL, LAST_IP_IN => '127.0.0.1',
IGNORE_IF_NULL_IN => TRUE, HANDLE_ERROR_IN => FALSE );
/* IF v_cnt_hos = 1 THEN
-- Make A grid Table For Author And Hospital
-- insert author_fk and v_hospital_fk
Dbms_output.put_line('Hospital');
END IF;*/
COMMIT;
END LOOP;
CLOSE c1;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('HOSPITAL_MPROC ERROR = ' || SQLERRM );
NULL;
END HOSPITAL_MPROC;
--------------------------------------------------------------------------------------------------------------------
FUNCTION GET_DISCIPLINE_ID_MFUN(v_dis_name IN VARCHAR2, v_displine_id in NUMBER) RETURN NUMBER AS
v_dis_id DISCIPLINE.DISCIPLINE_PK%type;
v_discipline_name DBO.DISCIPLINE_MST.DISCIPLINENAME@lnk_dbo_12%type := v_dis_name;
BEGIN
IF v_displine_id > 0 THEN
SELECT DISCIPLINENAME INTO v_discipline_name FROM DISCIPLINE_MST@lnk_dbo_12 WHERE DISCIPLINEID = v_displine_id;
END IF;
SELECT DISCIPLINE_PK INTO v_dis_id FROM DISCIPLINE WHERE DISCIPLINE_NAME=v_discipline_name;
RETURN v_dis_id;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('GET_DISCIPLINE_ID_MFUN ERROR = ' || SQLERRM );
SELECT DISCIPLINE_PK INTO v_dis_id FROM DISCIPLINE WHERE DISCIPLINE_NAME='Not Known';
RETURN v_dis_id;
END GET_DISCIPLINE_ID_MFUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION GET_PAY_METHOD_MFUN( method_of_payment IN NUMBER) RETURN NUMBER AS
temp NUMBER;
BEGIN
temp:= case method_of_payment
when 1 then 1 -- CHECK
when 2 then 2 -- Via Your Bank Account / ACH
when 3 then 3 -- Via Your Credit/Debit Card (AUTO)
when 4 then 4 -- Via Your Credit/Debit Card (MANUAL)
ELSE 1
end;
RETURN temp;
END GET_PAY_METHOD_MFUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION RET_01_FR_YN_MFUN( yn_status IN VARCHAR2) RETURN NUMBER AS
temp NUMBER;
BEGIN
IF UPPER(yn_status) = 'Y' THEN
temp := 1;
ELSE
temp := 0;
END IF;
RETURN temp;
END RET_01_FR_YN_MFUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION COMB_CSV_EMAIL_FUN( csv_email IN VARCHAR2) RETURN VARCHAR2 AS
p_del varchar2(1) := ',';
count_occur number;
l_idx number;
l_list VARCHAR2(1000);
flag number;
email VARCHAR2(1000) := csv_email || ',';
temp VARCHAR2(1000);
BEGIN
count_occur := (length(email) - nvl(length(replace(email, p_del, '')),0))/length(p_del);
FOR i IN 1 ..count_occur LOOP
l_idx := INSTR(email,p_del);
l_list := SUBSTR(email,1,l_idx-1);
if l_list is not null then
temp := temp || l_list || ',';
end if;
email := SUBSTR(email,l_idx+1,Length(email)-1);
END LOOP;
RETURN substr(temp,1,length(temp)-1);
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('COMB_CSV_EMAIL_FUN ERROR = ' || SQLERRM );
RETURN NULL;
END COMB_CSV_EMAIL_FUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION GET_DIFF_LVL_MFUN(v_client_type IN VARCHAR2) RETURN NUMBER AS
temp NUMBER := -1;
BEGIN
temp:= case upper(v_client_type)
when 'EASY' then 0
when 'AVERAGE' then 1
when 'DIFFICULT' then 2
end;
IF temp = -1 THEN
temp := NULL;
END IF;
RETURN temp;
END GET_DIFF_LVL_MFUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION GET_MERGE_PGBREAK_MFUN(v_merge_doc IN VARCHAR2, v_pbreak IN VARCHAR2) RETURN NUMBER AS
temp NUMBER := -1;
BEGIN
IF upper(v_merge_doc) = 'N' THEN
temp := 0;
ELSIF upper(v_merge_doc) = 'Y' AND upper(v_pbreak) = 'Y' THEN
temp := 1;
ELSIF upper(v_merge_doc) = 'Y' AND upper(v_pbreak) = 'N' THEN
temp := 2;
ELSE
temp := NULL;
END IF;
RETURN temp;
END GET_MERGE_PGBREAK_MFUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION GET_INV_PARAMETERS_MFUN(v_inv_para IN VARCHAR2) RETURN NUMBER AS
temp NUMBER;
BEGIN
IF v_inv_para = 'g' THEN
temp := 1;
ELSE
temp := 0;
END IF;
RETURN temp;
END GET_INV_PARAMETERS_MFUN;
--------------------------------------------------------------------------------------------------------------------
PROCEDURE GENERATE_UNAME_PASS_MPROC(v_mgrp IN VARCHAR2, v_username out NOCOPY VARCHAR2, v_password out NOCOPY VARCHAR2) as
temp_str VARCHAR2(20);
temp_num NUMBER(3);
cnt number;
BEGIN
loop
select dbms_random.value(100, 999) into temp_num from dual;
select upper(dbms_random.string('A', 2)) into temp_str from dual;
v_password := temp_str || temp_num;
v_username := v_mgrp || '_' || temp_num;
select count(LOGIN_PK) into cnt from login_mst where USERNAME = v_username;
exit when cnt = 0;
end loop;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('GENERATE_UNAME_PASS_MPROC ERROR = ' || SQLERRM );
NULL;
END GENERATE_UNAME_PASS_MPROC;
--------------------------------------------------------------------------------------------------------------------
FUNCTION CHECK_AC_DBO_MFUN( v_client_code IN VARCHAR2, v_mgrp_b IN VARCHAR2, v_mgrp OUT NOCOPY VARCHAR2) RETURN NUMBER AS
-- Check in DD Schema for Previous AC Client Entry
v_ac_id_fk ac.ac_pk%type;
BEGIN
IF v_client_code IS NULL THEN
SELECT ac_fk, mgrp into v_ac_id_fk,v_mgrp from ac_dbo where mgrp_b = v_mgrp_b;
ELSE
SELECT ac_fk, mgrp into v_ac_id_fk,v_mgrp from ac_dbo where mgrp = v_client_code;
END IF;
RETURN v_ac_id_fk;
exception
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('CHECK_AC_DBO_MFUN ERROR = ' || SQLERRM );
return 0;
END CHECK_AC_DBO_MFUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION CHECK_lOGIN_MST_MFUN( v_owner_fk IN NUMBER) RETURN NUMBER AS
-- Check in DD Schema for Previous lOGIN_MST Client Entry
v_login_fk LOGIN_MST.LOGIN_PK%TYPE;
BEGIN
SELECT LOGIN_PK INTO v_login_fk FROM LOGIN_MST WHERE ENTITY_OWN_FK = v_owner_fk;
RETURN v_login_fk;
exception
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('CHECK_lOGIN_MST_MFUN ERROR = ' || SQLERRM );
return 0;
END CHECK_lOGIN_MST_MFUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION CHECK_lOGIN_USERNAME_MFUN( v_username IN VARCHAR2) RETURN NUMBER AS
-- Check in DD Schema for Previous lOGIN_MST Client Entry
v_login_fk LOGIN_MST.LOGIN_PK%TYPE;
BEGIN
SELECT LOGIN_PK INTO v_login_fk FROM LOGIN_MST WHERE USERNAME = v_username;
RETURN v_login_fk;
exception
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('CHECK_lOGIN_USERNAME_MFUN ERROR = ' || SQLERRM );
return 0;
END CHECK_lOGIN_USERNAME_MFUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION CHECK_WFPARAM_MFUN( v_ac_au_id IN VARCHAR2, v_stat IN NUMBER) RETURN NUMBER AS
-- Check in DD Schema for Previous AC_AU_WFPARAM Client Entry
-- v_stat = 1 for AC and 0 for Author
temp NUMBER;
BEGIN
IF v_stat = 1 THEN
SELECT WFPARAM_PK into temp from AC_AU_WFPARAM where AC_FK = v_ac_au_id AND AUTHOR_FK IS NULL;
ELSE
SELECT WFPARAM_PK into temp from AC_AU_WFPARAM where AUTHOR_FK = v_ac_au_id;
END IF;
RETURN temp;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('CHECK_WFPARAM_MFUN ERROR = ' || SQLERRM );
RETURN 0;
END CHECK_WFPARAM_MFUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION CHECK_AUTHOR_MFUN(v_ac_fk IN NUMBER, v_first_name IN VARCHAR2, v_last_name IN VARCHAR2) RETURN NUMBER AS
-- Check in DD Schema for Previous AUTHOR Client Entry
v_author_pk author.author_pk%type;
BEGIN
SELECT AUTHOR_PK into v_author_pk from AUTHOR where FIRST_NAME = v_first_name AND LAST_NAME=v_last_name and AC_FK=v_ac_fk;
RETURN v_author_pk;
exception
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('CHECK_AUTHOR_MFUN ERROR = ' || SQLERRM );
return 0;
END CHECK_AUTHOR_MFUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION GEN_GRP_NAME_FUN( group_name IN VARCHAR2) RETURN VARCHAR2 AS
count_occur number;
p_del varchar2(1) := ' ';
l_idx number;
l_list varchar2(50);
temp_group_name varchar2(50) := group_name;
v_char varchar2(1);
BEGIN
count_occur := (length(temp_group_name) - nvl(length(replace(temp_group_name, p_del, '')),0))/length(p_del);
l_list := SUBSTR(temp_group_name,1,1);
FOR i IN 1 ..count_occur LOOP
l_idx := INSTR(temp_group_name,p_del);
v_char := SUBSTR(temp_group_name,l_idx+1,1);
IF ascii(v_char) >= 65 AND ascii(v_char) <= 122 THEN
l_list := l_list || v_char;
END IF;
temp_group_name := SUBSTR(temp_group_name,l_idx+1,Length(temp_group_name)-1);
END LOOP;
RETURN lower(l_list);
END GEN_GRP_NAME_FUN;
--------------------------------------------------------------------------------------------------------------------
FUNCTION GEN_B_GRP_NAME_FUN( group_name IN VARCHAR2) RETURN VARCHAR2 AS
count_occur number;
p_del varchar2(1) := ' ';
l_idx number;
l_list varchar2(50);
temp_group_name varchar2(50) := group_name;
group_stat number;
group_cnt number := 1;
BEGIN
count_occur := (length(temp_group_name) - nvl(length(replace(temp_group_name, p_del, '')),0))/length(p_del);
l_list := SUBSTR(temp_group_name,1,1);
FOR i IN 1 ..count_occur LOOP
l_idx := INSTR(temp_group_name,p_del);
l_list := l_list || SUBSTR(temp_group_name,l_idx+1,1);
temp_group_name := SUBSTR(temp_group_name,l_idx+1,Length(temp_group_name)-1);
END LOOP;
select count(AC_FK) into group_stat from AC_DBO where MGRP = l_list;
if group_stat = 1 then
loop
l_list := l_list || '' || group_cnt;
group_cnt := group_cnt + 1;
select count(AC_FK) into group_stat from AC_DBO where MGRP = l_list;
exit when group_stat = 0;
end loop;
end if;
RETURN l_list;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('GEN_B_GRP_NAME_FUN ERROR = ' || SQLERRM );
RETURN 0;
END GEN_B_GRP_NAME_FUN;
--------------------------------------------------------------------------------------------------------------------
PROCEDURE GENERATE_AU_DR_NAME_MPROC(v_string IN VARCHAR2, p_del varchar2, v_first_name out NOCOPY VARCHAR2, v_last_name out NOCOPY VARCHAR2,v_suffix out NOCOPY VARCHAR2)
AS
temp_str VARCHAR2(100);
l_list VARCHAR2(100);
count_occur number := 0;
l_idx number;
BEGIN
temp_str := v_string || p_del;
count_occur := (length(temp_str) - nvl(length(replace(temp_str, p_del, '')),0))/length(p_del);
IF count_occur > 1 THEN
l_idx := INSTR(temp_str,p_del);
v_first_name := SUBSTR(temp_str,1,l_idx-1);
v_last_name := SUBSTR(temp_str,l_idx+1,Length(temp_str)-1);
IF INSTR(v_last_name,',')>0 THEN
v_suffix := SUBSTR(v_last_name,INSTR(v_last_name,',')+1);
v_last_name := TRIM(SUBSTR(v_last_name,1, INSTR(v_last_name,',')-1));
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
--DBMS_OUTPUT.PUT_LINE('GENERATE_AU_DR_NAME_MPROC ERROR = ' || SQLERRM );
NULL;
END GENERATE_AU_DR_NAME_MPROC;
--------------------------------------------------------------------------------------------------------------------
PROCEDURE PRE_INST_MPROC
AS
max_login_mst NUMBER(3);
BEGIN
-- CHANGES RELATED TO DISCIPLINE IN DBO AND DD
update client_profile@lnk_dbo_12 set discipline='Not Known' where discipline is NULL;
commit;
update client_profile@lnk_dbo_12 set discipline='Pulmonary Disease' where discipline = 'Pulmonary';
commit;
update client_profile@lnk_dbo_12 set discipline='Orthopedic' where discipline = 'Orthopedics';
commit;
update client_profile@lnk_dbo_12 set discipline='General Surgery' where discipline = 'General Surgeon';
commit;
update client_profile@lnk_dbo_12 set discipline='Hematology/Oncology' where discipline = 'Hematology-Oncology';
commit;
update client_profile@lnk_dbo_12 set discipline='Cardiologist' where discipline = 'Cardio';
commit;
update client_profile@lnk_dbo_12 set discipline='Pediatric Neurology' where discipline = 'Pediatric Neurologist';
commit;
update client_profile@lnk_dbo_12 set name='AMG - Arnold Lincow1, D. O.' where client_code='13146';
commit;
--insert into DISCIPLINE_MST(DISCIPLINEID,DISCIPLINENAME) values(307,'Physiotherapy' );
--insert into DISCIPLINE_MST(DISCIPLINEID,DISCIPLINENAME) values(308,'Sleep Study' );
DELETE FROM AU_MINUTE_FORMULA;
commit;
--@Change@
--DELETE FROM JOBS_SYNC;
--COMMIT;
--@Change@
DELETE FROM JOBS;
commit;
EXECUTE IMMEDIATE ('DROP SEQUENCE JOBS_SEQ');
EXECUTE IMMEDIATE ('CREATE SEQUENCE DD.JOBS_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE');
DELETE FROM AC_AU_WFPARAM;
commit;
EXECUTE IMMEDIATE ('DROP SEQUENCE AC_AU_WFPARAM_SEQ');
EXECUTE IMMEDIATE ('CREATE SEQUENCE DD.AC_AU_WFPARAM_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE');
DELETE FROM AU_PATIENT;
commit;
EXECUTE IMMEDIATE ('DROP SEQUENCE AU_PATIENT_SEQ');
EXECUTE IMMEDIATE ('CREATE SEQUENCE DD.AU_PATIENT_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE');
DELETE FROM AUTHOR;
commit;
EXECUTE IMMEDIATE ('DROP SEQUENCE AUTHOR_SEQ');
EXECUTE IMMEDIATE ('CREATE SEQUENCE DD.AUTHOR_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE');
DELETE FROM PATIENTS;
commit;
EXECUTE IMMEDIATE ('DROP SEQUENCE PATIENTS_SEQ');
EXECUTE IMMEDIATE ('CREATE SEQUENCE DD.PATIENTS_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 781 CACHE 20 NOORDER NOCYCLE');
DELETE FROM LOGIN_MST WHERE ENTITY_TYPE_FK = 3;
commit;
-- GET THE MAX VALUE FROM LOGIN_MST_SEQ AND UPDATE THE START VALUE
/* EXECUTE IMMEDIATE('DROP SEQUENCE LOGIN_MST_SEQ');
EXECUTE IMMEDIATE('CREATE SEQUENCE DD.LOGIN_MST_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 15 NOCACHE NOORDER NOCYCLE');
*/
DELETE FROM REF_DOC;
commit;
EXECUTE IMMEDIATE ('DROP SEQUENCE REF_DOC_SEQ');
EXECUTE IMMEDIATE ('CREATE SEQUENCE DD.REF_DOC_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE');
DELETE FROM AC_DBO;
commit;
DELETE FROM AC;
commit;
EXECUTE IMMEDIATE ('DROP SEQUENCE AC_SEQ');
EXECUTE IMMEDIATE ('CREATE SEQUENCE DD.AC_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE');
DELETE FROM HOSPITALS;
commit;
EXECUTE IMMEDIATE ('DROP SEQUENCE HOSPITALS_SEQ');
EXECUTE IMMEDIATE ('CREATE SEQUENCE DD.HOSPITALS_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE');
DELETE FROM DRUGS;
commit;
EXECUTE IMMEDIATE ('DROP SEQUENCE HOSPITALS_SEQ');
EXECUTE IMMEDIATE ('CREATE SEQUENCE DD.HOSPITALS_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE');
DELETE FROM MINUTE_FORMULA;
commit;
EXECUTE IMMEDIATE ('DROP SEQUENCE MINUTE_FORMULA_SEQ');
EXECUTE IMMEDIATE ('CREATE SEQUENCE DD.MINUTE_FORMULA_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE');
DELETE FROM DISCIPLINE;
commit;
EXECUTE IMMEDIATE ('DROP SEQUENCE DISCIPLINE_SEQ');
EXECUTE IMMEDIATE ('CREATE SEQUENCE DD.DISCIPLINE_SEQ MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE');
DELETE FROM CURRENCY;
commit;
/* INSERT INTO CURRENCY(CURRENCY_PK, ASP_FK, NAME, COUNTRY, SYMBOL, LAST_LOGIN_FK, LAST_MODIFIED, LAST_MC, LAST_IP)
VALUES (1,1,'DOLLER','USA','$',NULL,NULL,NULL,NULL);
DELETE FROM AC_TYPES;
commit;
INSERT INTO AC_TYPES(AC_TYPE_PK,OWNER_FK,NAME,DESCRIPTION,LAST_LOGIN_FK,LAST_MODIFIED,LAST_MC,LAST_IP)
VALUES(1,1,'TEMP','TEMPORARY CREATED',NULL,NULL,NULL,NULL);*/
DBMS_OUTPUT.PUT_LINE('Pre Installation Completed Sucessfully ');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(' PRE_INST_MPROC ERROR -> ' || SQLERRM);
END PRE_INST_MPROC;
PROCEDURE INSERT_AC_TRANS_TYPE AS
V_TRANS_TYPE_PK TRANS_TYPES_TP.TRANS_TYPE_PK_t;
BEGIN
V_TRANS_TYPE_PK := TRANS_TYPES_CP.NEXT_KEY;
TRANS_TYPES_CP.INS(
TRANS_TYPE_PK_IN => V_TRANS_TYPE_PK,
ENTITY_TYPE_FK_IN => 3,
PARTICULARS_IN =>'PAID BILL OF THE MONTH' ,
TRANS_TABLE_IN => 'ENTITY_BILLS',
DEV_HINT_IN => 'bill of payable invoice',
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN => DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
V_TRANS_TYPE_PK := TRANS_TYPES_CP.NEXT_KEY;
TRANS_TYPES_CP.INS(
TRANS_TYPE_PK_IN => V_TRANS_TYPE_PK,
ENTITY_TYPE_FK_IN => 3,
PARTICULARS_IN =>'TRIAL BILL OF THE MONTH' ,
TRANS_TABLE_IN => 'ENTITY_BILLS',
DEV_HINT_IN => 'bill of trial invoice',
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN => DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
V_TRANS_TYPE_PK := TRANS_TYPES_CP.NEXT_KEY;
TRANS_TYPES_CP.INS(
TRANS_TYPE_PK_IN => V_TRANS_TYPE_PK,
ENTITY_TYPE_FK_IN => 3,
PARTICULARS_IN =>'ZERO LC BILL OF THE MONTH' ,
TRANS_TABLE_IN => 'ENTITY_BILLS',
DEV_HINT_IN => 'bill of zero line count invoice',
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN => DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
V_TRANS_TYPE_PK := TRANS_TYPES_CP.NEXT_KEY;
TRANS_TYPES_CP.INS(
TRANS_TYPE_PK_IN => V_TRANS_TYPE_PK,
ENTITY_TYPE_FK_IN => 3,
PARTICULARS_IN =>'PAID BILL S PAYMENT' ,
TRANS_TABLE_IN => 'PAY_TASK',
DEV_HINT_IN => 'payment received for the bill of payable invoice',
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN => DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
V_TRANS_TYPE_PK := TRANS_TYPES_CP.NEXT_KEY;
TRANS_TYPES_CP.INS(
TRANS_TYPE_PK_IN => V_TRANS_TYPE_PK,
ENTITY_TYPE_FK_IN => 3,
PARTICULARS_IN =>'TRIAL BILL S PAYMENT' ,
TRANS_TABLE_IN => 'PAY_TASK',
DEV_HINT_IN => 'payment received for the bill of trial invoice. This will paid as virtual credit',
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN => DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
EXCEPTION WHEN OTHERS THEN
BEGIN
q$error_manager.raise_error(SQLCODE,SQLERRM,'NOTE_TYPE',TO_CHAR(V_TRANS_TYPE_PK));
EXCEPTION WHEN OTHERS THEN
q$error_manager.trace_off;
q$error_manager.trace_on('DD_MIGRATION.INSERT_TRANS_TYPES.TRACE_ON');
q$error_manager.GET_ERROR_INFO(ERROR_INFO);
DBMS_OUTPUT.PUT_LINE('SOME ERROR OCCURE WHILE INSERTING TRANS_TYPES PLEASE CHECK ERROR LOG TABLES !!!');
END;
END;
PROCEDURE INVOICE_MIGRATION AS
CURSOR CUR_INV IS
SELECT A.SRNO,DD_AUTH.GET_AC_DBO_FK(B.CLIENT_CODE,B.MGRP) AC_FK, B.MGRP,A.PARTY,A.BILL_MONTH,A.TRAN_DATE,A.TRAN_TYPE,A.PARTICULARS,A.DEBIT,A.CREDIT,A.BALANCE,A.ENTRY_BY_USER
FROM BALANCE_INFO@LNK_DBO_12 A INNER JOIN CLIENT_PROFILE@LNK_DBO_12 B ON A.PARTY=B.CLIENT_CODE
WHERE ((B.CLIENT_CODE = B.MGRP) OR (B.MGRP='B')) AND UPPER(B.STATUS)=UPPER('Current') AND TO_CHAR(TRAN_DATE,'YYYY') >= 2008 AND PARTY='01121' ORDER BY TRAN_DATE ASC,SRNO ASC;
CURSOR CUR_ENTITY_LEDGER (V_AC_FK NUMBER ) IS
SELECT ENTITY_TYPE_FK,ENTITY_OWN_FK,LEDGER_DT,PARTICULARS,DEBIT,CREDIT,BALANCE
FROM ENTITY_LEDGER WHERE ENTITY_TYPE_FK=3 AND ENTITY_OWN_FK=V_AC_FK AND TO_CHAR(LEDGER_DT,'YYYY') >=2008;
CURSOR WI IS
WITH
D_LEDGER AS
(
SELECT A.SRNO,DD_AUTH.GET_AC_DBO_FK(B.CLIENT_CODE,B.MGRP) AC_FK, B.MGRP,A.PARTY,A.BILL_MONTH,A.TRAN_DATE,A.TRAN_TYPE,A.PARTICULARS,A.DEBIT,A.CREDIT,A.BALANCE,A.ENTRY_BY_USER
FROM BALANCE_INFO@LNK_DBO_12 A INNER JOIN CLIENT_PROFILE@LNK_DBO_12 B ON A.PARTY=B.CLIENT_CODE
WHERE ((B.CLIENT_CODE = B.MGRP) OR (B.MGRP='B')) AND UPPER(B.STATUS)=UPPER('Current') AND TO_CHAR(TRAN_DATE,'YYYY') >= 2008 and party='01121' ORDER BY TRAN_DATE ASC,SRNO ASC
)
SELECT E.SRNO, D_LEDGER.PARTY, D_LEDGER.MGRP, E.TRANS_OWN_FK,E.ENTITY_OWN_FK,E.LEDGER_DT,E.PARTICULARS,E.DEBIT,E.CREDIT,E.BALANCE ,E.LAST_MODIFIED FROM ENTITY_LEDGER E , D_LEDGER
WHERE (D_LEDGER.AC_FK = E.ENTITY_OWN_FK ) AND E.ENTITY_OWN_FK >0 AND E.ENTITY_TYPE_FK=3 AND (E.LEDGER_DT = D_LEDGER.TRAN_DATE) AND (E.PARTICULARS=D_LEDGER.PARTICULARS) AND (E.DEBIT=D_LEDGER.DEBIT )AND (E.CREDIT= D_LEDGER.CREDIT) AND (E.BALANCE=D_LEDGER.BALANCE) ORDER BY E.LAST_MODIFIED DESC;
V_ENTITY_LEDGER CUR_ENTITY_LEDGER%ROWTYPE;
V_ENTITY_OWN_FK AC_TP.AC_PK_t;
V_GRP_NAME AC_DBO_TP.MGRP_t;
V_PAY_TASK_PK PAY_TASK_TP.PAY_TASK_PK_t;
V_SRNO ENTITY_LEDGER_TP.SRNO_t;
V_ENTITY_BILL_PK ENTITY_BILLS_TP.BILL_PK_t;
V_TRANS_TYPE_PK NUMBER;
BEGIN
FOR I IN CUR_INV LOOP
BEGIN
/* IF I.MGRP ='B' THEN
V_ENTITY_OWN_FK := DBO_MIGRATION.CHECK_AC_DBO_MFUN(NULL, 'B_'||I.PARTY , V_GRP_NAME);
ELSE
V_ENTITY_OWN_FK := DBO_MIGRATION.CHECK_AC_DBO_MFUN(I.PARTY, NULL, V_GRP_NAME);
END IF; */
V_ENTITY_OWN_FK := I.AC_FK;
IF V_ENTITY_OWN_FK > 0 THEN
DBMS_OUTPUT.PUT_LINE(I.AC_FK );
OPEN CUR_ENTITY_LEDGER(I.AC_FK) ;
FETCH CUR_ENTITY_LEDGER INTO V_ENTITY_LEDGER;
DBMS_OUTPUT.PUT_LINE(CUR_ENTITY_LEDGER%ROWCOUNT);
IF CUR_ENTITY_LEDGER%ROWCOUNT <= 0 THEN
DBMS_OUTPUT.PUT_LINE('INSERT RECORD');
NULL;
IF TRIM(UPPER(I.TRAN_TYPE)) = TRIM(UPPER('DEBIT')) THEN
IF I.PARTICULARS LIKE ('Trial Period Charge - Not to Pay Bill%') THEN
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'TRIAL BILL OF THE MONTH';
ELSIF I.PARTICULARS LIKE ('%(Zero Line Count Period)%') THEN
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'ZERO LC BILL OF THE MONTH';
ELSE
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'PAID BILL OF THE MONTH';
END IF;
/* INSERTING RECORD INTO ENTITY_BILLS TABLE */
V_ENTITY_BILL_PK := ENTITY_BILLS_CP.INS(
ENTITY_TYPE_FK_IN => 3,
ENTITY_OWN_FK_IN => V_ENTITY_OWN_FK,
TRANS_TYPE_FK_IN => V_TRANS_TYPE_PK,
BILL_DT_IN => I.TRAN_DATE,
BILL_FROM_DT_IN => NVL(I.BILL_MONTH,I.TRAN_DATE),
BILL_TO_DT_IN => NVL(I.BILL_MONTH,I.TRAN_DATE),
AMOUNT_IN => I.DEBIT,
PARTICULARS_IN => I.PARTICULARS,
STATUS_IN => 2,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
COMMIT;
/*INSERTING RECORD IN ENTITY_LEDGER TABLE*/
V_SRNO := ENTITY_LEDGER_CP.NEXT_KEY;
ENTITY_LEDGER_CP.INS(
SRNO_IN => V_SRNO,
ENTITY_TYPE_FK_IN => 3,
ENTITY_OWN_FK_IN => V_ENTITY_OWN_FK,
LEDGER_DT_IN => I.TRAN_DATE,
TRANS_OWN_FK_IN => V_ENTITY_BILL_PK,
TRANS_TYPE_FK_IN => V_TRANS_TYPE_PK,
PARTICULARS_IN => I.PARTICULARS,
DEBIT_IN => I.DEBIT,
CREDIT_IN => I.CREDIT,
BALANCE_IN => I.BALANCE,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
COMMIT;
ELSIF TRIM(UPPER(I.TRAN_TYPE)) = TRIM(UPPER('CREDIT')) THEN
IF I.PARTICULARS LIKE ('Trial Period Charge%') THEN
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'TRIAL BILL S PAYMENT';
ELSE
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'PAID BILL S PAYMENT';
END IF;
/*INSERT RECORD INTO PAY_TASK TABLE */
V_PAY_TASK_PK := PAY_TASK_CP.INS(
ENTITY_TYPE_FK_IN => 3,
ENTITY_OWN_FK_IN => V_ENTITY_OWN_FK,
TRANS_TYPE_FK_IN => V_TRANS_TYPE_PK,
PAY_DT_IN => I.TRAN_DATE,
MODE_FK_IN => 1,
AMOUNT_IN => I.CREDIT,
PARTICULARS_IN => I.PARTICULARS,
STATUS_IN => 1,
YP_TASK_TYPE_IN => NULL,
PAY_TASK_FK_IN => NULL,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
COMMIT;
/*INSERTING RECORD IN ENTITY_LEDGER TABLE*/
V_SRNO := ENTITY_LEDGER_CP.NEXT_KEY;
ENTITY_LEDGER_CP.INS(
SRNO_IN => V_SRNO,
ENTITY_TYPE_FK_IN => 3,
ENTITY_OWN_FK_IN => V_ENTITY_OWN_FK,
LEDGER_DT_IN => I.TRAN_DATE,
TRANS_OWN_FK_IN => V_PAY_TASK_PK,
TRANS_TYPE_FK_IN => V_TRANS_TYPE_PK,
PARTICULARS_IN => I.PARTICULARS,
DEBIT_IN => I.DEBIT,
CREDIT_IN => I.CREDIT,
BALANCE_IN => I.BALANCE,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
COMMIT;
END IF;
END IF;
-- FOR J IN CUR_ENTITY_LEDGER(I.AC_FK) LOOP
LOOP
EXIT WHEN CUR_ENTITY_LEDGER % NOTFOUND;
DBMS_OUTPUT.PUT_LINE(I.AC_FK ||'---'||V_ENTITY_LEDGER.ENTITY_OWN_FK );
IF (I.AC_FK = V_ENTITY_LEDGER.ENTITY_OWN_FK AND V_ENTITY_LEDGER.ENTITY_TYPE_FK =3) AND (I.TRAN_DATE = V_ENTITY_LEDGER.LEDGER_DT) AND (I.PARTICULARS = V_ENTITY_LEDGER.PARTICULARS) AND (I.BALANCE=V_ENTITY_LEDGER.BALANCE) THEN
DBMS_OUTPUT.PUT_LINE('ENTRY ALREADY EXISTS ');
ELSE
DBMS_OUTPUT.PUT_LINE('PLEASE INSERT RECORD :-'||V_ENTITY_LEDGER.ENTITY_OWN_FK);
IF TRIM(UPPER(I.TRAN_TYPE)) = TRIM(UPPER('DEBIT')) THEN
IF I.PARTICULARS LIKE ('Trial Period Charge - Not to Pay Bill%') THEN
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'TRIAL BILL OF THE MONTH';
ELSIF I.PARTICULARS LIKE ('%(Zero Line Count Period)%') THEN
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'ZERO LC BILL OF THE MONTH';
ELSE
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'PAID BILL OF THE MONTH';
END IF;
/* INSERTING RECORD INTO ENTITY_BILLS TABLE */
V_ENTITY_BILL_PK := ENTITY_BILLS_CP.INS(
ENTITY_TYPE_FK_IN => 3,
ENTITY_OWN_FK_IN => V_ENTITY_OWN_FK,
TRANS_TYPE_FK_IN => V_TRANS_TYPE_PK,
BILL_DT_IN => I.TRAN_DATE,
BILL_FROM_DT_IN => NVL(I.BILL_MONTH,I.TRAN_DATE),
BILL_TO_DT_IN => NVL(I.BILL_MONTH,I.TRAN_DATE),
AMOUNT_IN => I.DEBIT,
PARTICULARS_IN => I.PARTICULARS,
STATUS_IN => 2,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
COMMIT;
/*INSERTING RECORD IN ENTITY_LEDGER TABLE*/
V_SRNO := ENTITY_LEDGER_CP.NEXT_KEY;
ENTITY_LEDGER_CP.INS(
SRNO_IN => V_SRNO,
ENTITY_TYPE_FK_IN => 3,
ENTITY_OWN_FK_IN => V_ENTITY_OWN_FK,
LEDGER_DT_IN => I.TRAN_DATE,
TRANS_OWN_FK_IN => V_ENTITY_BILL_PK,
TRANS_TYPE_FK_IN => V_TRANS_TYPE_PK,
PARTICULARS_IN => I.PARTICULARS,
DEBIT_IN => I.DEBIT,
CREDIT_IN => I.CREDIT,
BALANCE_IN => I.BALANCE,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
COMMIT;
ELSIF TRIM(UPPER(I.TRAN_TYPE)) = TRIM(UPPER('CREDIT')) THEN
IF I.PARTICULARS LIKE ('Trial Period Charge%') THEN
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'TRIAL BILL S PAYMENT';
ELSE
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'PAID BILL S PAYMENT';
END IF;
/*INSERT RECORD INTO PAY_TASK TABLE */
V_PAY_TASK_PK := PAY_TASK_CP.INS(
ENTITY_TYPE_FK_IN => 3,
ENTITY_OWN_FK_IN => V_ENTITY_OWN_FK,
TRANS_TYPE_FK_IN => V_TRANS_TYPE_PK,
PAY_DT_IN => I.TRAN_DATE,
MODE_FK_IN => 1,
AMOUNT_IN => I.CREDIT,
PARTICULARS_IN => I.PARTICULARS,
STATUS_IN => 1,
YP_TASK_TYPE_IN => NULL,
PAY_TASK_FK_IN => NULL,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
COMMIT;
/*INSERTING RECORD IN ENTITY_LEDGER TABLE*/
V_SRNO := ENTITY_LEDGER_CP.NEXT_KEY;
ENTITY_LEDGER_CP.INS(
SRNO_IN => V_SRNO,
ENTITY_TYPE_FK_IN => 3,
ENTITY_OWN_FK_IN => V_ENTITY_OWN_FK,
LEDGER_DT_IN => I.TRAN_DATE,
TRANS_OWN_FK_IN => V_PAY_TASK_PK,
TRANS_TYPE_FK_IN => V_TRANS_TYPE_PK,
PARTICULARS_IN => I.PARTICULARS,
DEBIT_IN => I.DEBIT,
CREDIT_IN => I.CREDIT,
BALANCE_IN => I.BALANCE,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
COMMIT;
END IF;
COMMIT;
END IF;
END LOOP;
CLOSE CUR_ENTITY_LEDGER;
ELSE
DBMS_OUTPUT.PUT_LINE('ENTITY_OWN_FK IS :- '||V_ENTITY_OWN_FK);
END IF;
COMMIT;
EXCEPTION WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM||'---'||I.PARTY||'--'||I.SRNO);
q$error_manager.raise_error(SQLCODE,SQLERRM,'I.PARTY',TO_CHAR(I.SRNO));
EXCEPTION WHEN OTHERS THEN
q$error_manager.trace_off;
q$error_manager.trace_on('DD_MIGRATION.INSERT_LOGIN_MST.TRACE_ON');
q$error_manager.GET_ERROR_INFO(DBO_MIGRATION.ERROR_INFO);
DBMS_OUTPUT.PUT_LINE('SOME ERROR OCCURE WHILE INSERTING LOGIN_MST TABLE DATA PLEASE CHECK ERROR LOG TABLES !!!');
END;
END;
END LOOP;
END;
PROCEDURE SYNC_DBO2DD_INV AS
CURSOR CUR_INV IS
WITH
D_LEDGER AS
(
SELECT A.SRNO,DD_AUTH.GET_AC_DBO_FK(B.CLIENT_CODE,B.MGRP) AC_FK, B.MGRP,A.PARTY,A.BILL_MONTH,A.TRAN_DATE,A.TRAN_TYPE,A.PARTICULARS,A.DEBIT,A.CREDIT,A.BALANCE,A.ENTRY_BY_USER
FROM BALANCE_INFO@LNK_DBO_12 A INNER JOIN CLIENT_PROFILE@LNK_DBO_12 B ON A.PARTY=B.CLIENT_CODE
WHERE ((B.CLIENT_CODE = B.MGRP) OR (B.MGRP='B')) AND UPPER(B.STATUS)=UPPER('Current') AND TO_CHAR(TRAN_DATE,'YYYY') >= 2008
ORDER BY TRAN_DATE ASC,SRNO ASC
)
SELECT AC_FK , TRAN_DATE , PARTICULARS, DEBIT, CREDIT, BALANCE, BILL_MONTH FROM D_LEDGER WHERE AC_FK > 0
MINUS SELECT ENTITY_OWN_FK, LEDGER_DT, PARTICULARS , DEBIT, CREDIT, BALANCE,DD.DBO_MIGRATION.GET_BILL_MONTH(ENTITY_OWN_FK,TRANS_TYPE_FK,TRANS_OWN_FK,3,LEDGER_DT ) BILL_MONTH
FROM ENTITY_LEDGER WHERE ENTITY_TYPE_FK=3;
V_ENTITY_OWN_FK AC_TP.AC_PK_t;
V_GRP_NAME AC_DBO_TP.MGRP_t;
V_PAY_TASK_PK PAY_TASK_TP.PAY_TASK_PK_t;
V_SRNO ENTITY_LEDGER_TP.SRNO_t;
V_ENTITY_BILL_PK ENTITY_BILLS_TP.BILL_PK_t;
V_TRANS_TYPE_PK NUMBER;
BEGIN
FOR I IN CUR_INV LOOP
BEGIN
IF I.AC_FK > 0 THEN
DBMS_OUTPUT.PUT_LINE(I.AC_FK );
IF I.DEBIT > 0 OR (I.DEBIT = 0 AND I.CREDIT = 0) THEN
IF I.PARTICULARS LIKE ('Trial Period Charge - Not to Pay Bill%') THEN
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'TRIAL BILL OF THE MONTH';
ELSIF I.PARTICULARS LIKE ('%(Zero Line Count Period)%') THEN
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'ZERO LC BILL OF THE MONTH';
ELSE
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'PAID BILL OF THE MONTH';
END IF;
/* INSERTING RECORD INTO ENTITY_BILLS TABLE */
V_ENTITY_BILL_PK := ENTITY_BILLS_CP.INS(
ENTITY_TYPE_FK_IN => 3,
ENTITY_OWN_FK_IN => I.AC_FK ,
TRANS_TYPE_FK_IN => V_TRANS_TYPE_PK,
BILL_DT_IN => I.TRAN_DATE,
BILL_FROM_DT_IN => NVL(I.BILL_MONTH,I.TRAN_DATE),
BILL_TO_DT_IN => NVL(I.BILL_MONTH,I.TRAN_DATE),
AMOUNT_IN => I.DEBIT,
PARTICULARS_IN => I.PARTICULARS,
STATUS_IN => 2,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
COMMIT;
/*INSERTING RECORD IN ENTITY_LEDGER TABLE*/
V_SRNO := ENTITY_LEDGER_CP.NEXT_KEY;
ENTITY_LEDGER_CP.INS(
SRNO_IN => V_SRNO,
ENTITY_TYPE_FK_IN => 3,
ENTITY_OWN_FK_IN => I.AC_FK,
LEDGER_DT_IN => I.TRAN_DATE,
TRANS_OWN_FK_IN => V_ENTITY_BILL_PK,
TRANS_TYPE_FK_IN => V_TRANS_TYPE_PK,
PARTICULARS_IN => I.PARTICULARS,
DEBIT_IN => I.DEBIT,
CREDIT_IN => I.CREDIT,
BALANCE_IN => I.BALANCE,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
COMMIT;
ELSIF I.CREDIT > 0 THEN
IF I.PARTICULARS LIKE ('Trial Period Charge%') THEN
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'TRIAL BILL S PAYMENT';
ELSE
SELECT TRANS_TYPE_PK INTO V_TRANS_TYPE_PK FROM TRANS_TYPES WHERE PARTICULARS = 'PAID BILL S PAYMENT';
END IF;
/*INSERT RECORD INTO PAY_TASK TABLE */
V_PAY_TASK_PK := PAY_TASK_CP.INS(
ENTITY_TYPE_FK_IN => 3,
ENTITY_OWN_FK_IN => I.AC_FK,
TRANS_TYPE_FK_IN => V_TRANS_TYPE_PK,
PAY_DT_IN => I.TRAN_DATE,
MODE_FK_IN => 1,
AMOUNT_IN => I.CREDIT,
PARTICULARS_IN => I.PARTICULARS,
STATUS_IN => 1,
YP_TASK_TYPE_IN => NULL,
PAY_TASK_FK_IN => NULL,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
COMMIT;
/*INSERTING RECORD IN ENTITY_LEDGER TABLE*/
V_SRNO := ENTITY_LEDGER_CP.NEXT_KEY;
ENTITY_LEDGER_CP.INS(
SRNO_IN => V_SRNO,
ENTITY_TYPE_FK_IN => 3,
ENTITY_OWN_FK_IN => I.AC_FK,
LEDGER_DT_IN => I.TRAN_DATE,
TRANS_OWN_FK_IN => V_PAY_TASK_PK,
TRANS_TYPE_FK_IN => V_TRANS_TYPE_PK,
PARTICULARS_IN => I.PARTICULARS,
DEBIT_IN => I.DEBIT,
CREDIT_IN => I.CREDIT,
BALANCE_IN => I.BALANCE,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
COMMIT;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('ENTITY_OWN_FK IS :- '||I.AC_FK);
END IF;
COMMIT;
EXCEPTION WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM||'---'||I.AC_FK||'--'||I.TRAN_DATE);
q$error_manager.raise_error(SQLCODE,SQLERRM,'I.AC_FK',TO_CHAR(I.AC_FK));
EXCEPTION WHEN OTHERS THEN
q$error_manager.trace_off;
q$error_manager.trace_on('DD_MIGRATION.INSERT_ENTITY_LEDGER.TRACE_ON');
q$error_manager.GET_ERROR_INFO(DBO_MIGRATION.ERROR_INFO);
DBMS_OUTPUT.PUT_LINE('SOME ERROR OCCURE WHILE INSERTING ENTITY_LEDGERTABLE DATA PLEASE CHECK ERROR LOG TABLES !!!');
END;
END;
END LOOP;
END SYNC_DBO2DD_INV;
PROCEDURE SYNC_DD2DBO_INV AS
CURSOR CUR_INV IS
WITH
D_LEDGER AS
(
SELECT A.SRNO,DD_AUTH.GET_AC_DBO_FK(B.CLIENT_CODE,B.MGRP) AC_FK, B.MGRP,A.PARTY,A.BILL_MONTH,A.TRAN_DATE,A.TRAN_TYPE,A.PARTICULARS,A.DEBIT,A.CREDIT,A.BALANCE,A.ENTRY_BY_USER
FROM BALANCE_INFO@LNK_DBO_13 A INNER JOIN CLIENT_PROFILE@LNK_DBO_13 B ON A.PARTY=B.CLIENT_CODE
WHERE ((B.CLIENT_CODE = B.MGRP) OR (B.MGRP='B')) AND UPPER(B.STATUS)=UPPER('Current') AND TO_CHAR(TRAN_DATE,'YYYY') >= 2008
ORDER BY TRAN_DATE ASC,SRNO ASC
) ,
E_LEDGER AS
(
SELECT ENTITY_OWN_FK, LEDGER_DT, PARTICULARS , DEBIT, CREDIT, BALANCE,DD.DBO_MIGRATION.GET_BILL_MONTH(ENTITY_OWN_FK,TRANS_TYPE_FK,TRANS_OWN_FK,3,LEDGER_DT ) BILL_MONTH
FROM ENTITY_LEDGER WHERE ENTITY_TYPE_FK=3 MINUS SELECT AC_FK , TRAN_DATE , PARTICULARS, DEBIT, CREDIT, BALANCE, BILL_MONTH FROM D_LEDGER WHERE AC_FK > 0
)
SELECT DD_AUTH.GET_AC_DBO_PARTY(ENTITY_OWN_FK) PARTY, BILL_MONTH, LEDGER_DT TRAN_DATE,PARTICULARS, DEBIT,CREDIT,BALANCE FROM E_LEDGER;
V_ENTITY_OWN_FK AC_TP.AC_PK_t;
V_GRP_NAME AC_DBO_TP.MGRP_t;
V_PAY_TASK_PK PAY_TASK_TP.PAY_TASK_PK_t;
V_SRNO ENTITY_LEDGER_TP.SRNO_t;
V_ENTITY_BILL_PK ENTITY_BILLS_TP.BILL_PK_t;
V_TRANS_TYPE_PK NUMBER;
COUNTER NUMBER;
BEGIN
COUNTER :=1;
FOR I IN CUR_INV LOOP
BEGIN
IF I.PARTY IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE(I.PARTY||'---'||COUNTER );
COUNTER := COUNTER +1;
IF I.DEBIT > 0 OR (I.DEBIT = 0 AND I.CREDIT = 0) THEN
--INSERT INTO BALANCE_INFO@LNK_DBO_13(PARTY,BILL_MONTH,TRAN_DATE,TRAN_TYPE,PARTICULARS,DEBIT,CREDIT,BALANCE,ENTRY_BY_USER) VALUES (I.PARTY,I.BILL_MONTH,I.TRAN_DATE,'debit',I.PARTICULARS,I.DEBIT,I.CREDIT,I.BALANCE,'anuj');
INSERT INTO BALANCE_INFO@LNK_DBO_13 VALUES (bal.nextval@LNK_DBO_13,I.PARTY,I.BILL_MONTH,I.TRAN_DATE,'debit',I.PARTICULARS,I.DEBIT,I.CREDIT,I.BALANCE,'anuj');
COMMIT;
ELSIF I.CREDIT > 0 THEN
INSERT INTO BALANCE_INFO@LNK_DBO_13 VALUES (bal.nextval@LNK_DBO_13,I.PARTY,I.BILL_MONTH,I.TRAN_DATE,'credit',I.PARTICULARS,I.DEBIT,I.CREDIT,I.BALANCE,'anuj');
COMMIT;
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('PARTY IS NULL :- '||I.PARTY);
END IF;
COMMIT;
EXCEPTION WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM||'---'||I.PARTY||'--'||I.TRAN_DATE);
q$error_manager.raise_error(SQLCODE,SQLERRM,'I.PARTY',TO_CHAR(I.PARTY));
EXCEPTION WHEN OTHERS THEN
q$error_manager.trace_off;
q$error_manager.trace_on('DD_MIGRATION.INSERT_BALANCE_INFO.TRACE_ON');
q$error_manager.GET_ERROR_INFO(DBO_MIGRATION.ERROR_INFO);
DBMS_OUTPUT.PUT_LINE('SOME ERROR OCCURE WHILE INSERTING BALANCE_INFO TABLE DATA PLEASE CHECK ERROR LOG TABLES !!!');
END;
END;
END LOOP;
END SYNC_DD2DBO_INV;
FUNCTION GET_BILL_MONTH (V_ENTITY_OWN_FK IN NUMBER,V_TRANS_TYPE_FK IN NUMBER,V_TRANS_OWN_FK IN NUMBER,V_ENTITY_TYPE_FK IN NUMBER,V_LEDGER_DT IN DATE)
RETURN DATE AS
V_TABLE_NM VARCHAR2(25);
V_BILL_MONTH DATE;
BEGIN
SELECT TRANS_TABLE INTO V_TABLE_NM FROM TRANS_TYPES WHERE ENTITY_TYPE_FK=V_ENTITY_TYPE_FK AND TRANS_TYPE_PK = V_TRANS_TYPE_FK;
IF V_TABLE_NM ='ENTITY_BILLS' THEN
SELECT BILL_FROM_DT INTO V_BILL_MONTH FROM ENTITY_BILLS WHERE BILL_PK= V_TRANS_OWN_FK AND TRANS_TYPE_FK=V_TRANS_TYPE_FK;
ELSIF V_TABLE_NM ='PAY_TASK' THEN
SELECT A.BILL_MONTH INTO V_BILL_MONTH FROM BALANCE_INFO@LNK_DBO_12 A INNER JOIN CLIENT_PROFILE@LNK_DBO_12 B ON A.PARTY=B.CLIENT_CODE
WHERE ((B.CLIENT_CODE = B.MGRP) OR (B.MGRP='B')) AND UPPER(B.STATUS)=UPPER('Current') AND DD_AUTH.GET_AC_DBO_FK(B.CLIENT_CODE,B.MGRP) =V_ENTITY_OWN_FK AND A.TRAN_TYPE='credit' AND TRAN_DATE = V_LEDGER_DT;
END IF;
RETURN V_BILL_MONTH;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||SQLERRM);
RETURN NULL;
END;
PROCEDURE INSERT_AC_CARDS AS
CURSOR CUR_AC_CARDS IS
SELECT C.AC_FK,B.MGRP,A.COMPANY_NAME,A.CARD_HOLDER_NAME,A.ADDRESS_LINE_1,A.ADDRESS_LINE_2,A.CITY,A.STATE,A.COUNTRY,A.ZIP,A.EMAIL,A.CCTYPE,A.EXPIRY_MONTH,A.EXPIRY_YEAR,A.CVM,A.STATUS,NVL(A.CARD_USE_PRIORITY,0) CARD_USE_PRIORITY,
A.CARD_NUMBER,A.CRYPTO_KEY ,DD_AUTH.DECRYPT_RAW(A.CARD_NUMBER,A.CRYPTO_KEY) CARD
FROM AC_PAYMENT_INFO@CLIENT1_25 A , AC_MST_DBO@CLIENT1_25 B, AC_DBO C WHERE (A.AC_ID_FK= B.AC_NO_FK ) AND (C.MGRP=B.MGRP)AND A.CARD_NUMBER IS NOT NULL AND A.CRYPTO_KEY IS NOT NULL
AND (DD_AUTH.DECRYPT_RAW(A.CARD_NUMBER,A.CRYPTO_KEY) NOT IN (SELECT DD_AUTH.DECRYPT_RAW(CARD_NUMBER,ENCRYPTION_KEY) FROM ENTITY_CARDS WHERE ENTITY_TYPE_FK=3 AND ENTITY_OWN_FK =C.AC_FK) )ORDER BY B.MGRP ;
V_CARD_PK ENTITY_CARDS_TP.CARD_PK_t;
BEGIN
FOR I IN CUR_AC_CARDS LOOP
BEGIN
V_CARD_PK := ENTITY_CARDS_CP.NEXT_KEY;
ENTITY_CARDS_CP.INS(
ENTITY_OWN_FK_IN => I.AC_FK,
ENTITY_TYPE_FK_IN => 3,
COMPANY_NAME_IN => I.COMPANY_NAME,
CARD_HOLDER_NAME_IN => I.CARD_HOLDER_NAME,
CARD_NUMBER_IN => I.CARD_NUMBER,
ENCRYPTION_KEY_IN => I.CRYPTO_KEY,
ADDRESS_LINE_1_IN => I.ADDRESS_LINE_1,
ADDRESS_LINE_2_IN => I.ADDRESS_LINE_2,
CITY_IN => I.CITY,
STATE_IN => I.STATE,
COUNTRY_IN => I.COUNTRY,
ZIP_IN => I.ZIP,
EMAIL_IN => I.EMAIL,
CCTYPE_IN => I.CCTYPE,
EXPIRY_MONTH_IN => I.EXPIRY_MONTH,
EXPIRY_YEAR_IN => I.EXPIRY_YEAR,
CVM_IN => I.CVM,
STATUS_IN =>I.STATUS,
CARD_USE_PRIORITY_IN => I.CARD_USE_PRIORITY,
LAST_LOGIN_FK_IN => DD_MIGRATION.V_LAST_LOGIN_FK,
LAST_IP_IN =>DD_MIGRATION.V_LAST_IP,
HANDLE_ERROR_IN => FALSE
);
EXCEPTION WHEN OTHERS THEN
BEGIN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM||'---'||I.MGRP);
q$error_manager.raise_error(SQLCODE,SQLERRM,'I.MGRP',TO_CHAR(I.MGRP));
EXCEPTION WHEN OTHERS THEN
q$error_manager.trace_off;
q$error_manager.trace_on('DD_MIGRATION.INSERT_LOGIN_MST.TRACE_ON');
q$error_manager.GET_ERROR_INFO(DBO_MIGRATION.ERROR_INFO);
DBMS_OUTPUT.PUT_LINE('SOME ERROR OCCURE WHILE INSERTING ENTITY_CARDS TABLE DATA PLEASE CHECK ERROR LOG TABLES !!!');
END;
END;
END LOOP;
NULL;
END;
END DBO_MIGRATION;
Please help me as early as possible........
ASKED:
Jan 9 2009 6:06 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
Make sure your 10g database has a minimum patchset level of 10.1.0.5 (for 10.1) or 10.2.0.2 (for 10.2).
Ref: Oracle® Database Readme 11g Release 1 (11.1) - PL/SQL (see section 32.5 "Interoperability Between Releases").
