/****************************************************/ /* Table modifications */ /****************************************************/ /* To increase size of PA_HSC_COMMENTS to 500 for APATS table*/ Alter table APATS MODIFY PA_HSC_COMMENTS varchar2(500) ; /* Alter Column in A_WAITING_DETAILS to increase surgeon comments to 1999 characters*/ ALTER TABLE A_WAITING_DETAILS MODIFY WD_SURG_COMM VARCHAR2(1999) ; /* Alter Column in APATS to increase surgeon somments to 1999 characters*/ ALTER TABLE APATS MODIFY PA_SURG_COMM VARCHAR2(1999) ; /* add Column in WD_TA_SEQU in A_WAITING_DETAILS */ ALTER TABLE A_WAITING_DETAILS ADD WD_TA_SEQU number(9,0) ; /* add Column in SU_SUPERVISOR in ASURGN */ ALTER TABLE ASURGN ADD SU_SUPERVISOR number(3,0) ; /* add Column in OI_SURG1_SSU_SU_SEQU in AOITEM */ ALTER TABLE AOITEM ADD OI_SURG1_SSU_SU_SEQU number(9,0) ; /* add Column in OI_SURG2_SSU_SU_SEQU in AOITEM */ ALTER TABLE AOITEM ADD OI_SURG2_SSU_SU_SEQU number(9,0) ; /* add Column in OI_SURG3_SSU_SU_SEQU in AOITEM */ ALTER TABLE AOITEM ADD OI_SURG3_SSU_SU_SEQU number(9,0) ; /* add Column in OI_ANAES1_SSU_SU_SEQU in AOITEM */ ALTER TABLE AOITEM ADD OI_ANAES1_SSU_SU_SEQU number(9,0) ; /* add Column in OI_ANAES2_SSU_SU_SEQU in AOITEM */ ALTER TABLE AOITEM ADD OI_ANAES2_SSU_SU_SEQU number(9,0) ; /* add Column in OI_ANAES3_SSU_SU_SEQU in AOITEM */ ALTER TABLE AOITEM ADD OI_ANAES3_SSU_SU_SEQU number(9,0) ; /* Alter Column in APATS to increase the procedure description to 2000 characters as in WD_PLANNED_PROC_DESC of F_Waiting_Details*/ ALTER TABLE APATS MODIFY PA_OPCOMM VARCHAR2(2000) ; alter table A_Closure_type add CT_START_DATE date NULL ; alter table A_Closure_type add CT_END_DATE date NULL ; alter table ACANCEL add CN_START_DATE date NULL ; alter table ACANCEL add CN_END_DATE date NULL ; alter table A_Cancel_Group add CG_START_DATE date NULL ; alter table A_Cancel_Group add CG_END_DATE date NULL ; alter table A_Delay_Status add DES_START_DATE date NULL ; alter table A_Delay_Status add DES_END_DATE date NULL ; alter table A_Clinical_Priority add CLP_START_DATE date NULL ; alter table A_Clinical_Priority add CLP_END_DATE date NULL ; alter table A_Clinical_Priority add CLP_OP_TYPE NUMBER(3,0) default 0 not null ; alter table A_Anaesthetic add ANA_START_DATE date NULL ; alter table A_Anaesthetic add ANA_END_DATE date NULL ; alter table A_Asa add ASA_START_DATE date NULL ; alter table A_Asa add ASA_END_DATE date NULL ; alter table ACOMPLICAT add CAM_START_DATE date NULL ; alter table ACOMPLICAT add CAM_END_DATE date NULL ; alter table AMACOMPLIC add MA_START_DATE date NULL ; alter table AMACOMPLIC add MA_END_DATE date NULL ; alter table A_Unplanned add UP_START_DATE date NULL ; alter table A_Unplanned add UP_END_DATE date NULL ; alter table ASURGN add SU_START_DATE date NULL ; alter table ASURGN add SU_END_DATE date NULL ; alter table A_Intfce_Setup add IF_ENABLE_CONS_NATIONAL_CODE NUMBER(3,0) default 0 not null ; alter table A_OP_EQUIPMENT modify OPEQ_NON_CONF_REASON_SEQU NUMBER(9,0) ; alter table ADIARY add DI_CA_SEQU number(9,0) default 0 not null ; /****************************************************/ /* New tables and brief description */ /****************************************************/ execute SP_TAB_EXISTS('A_SPECIALITY_HIST','TABLE'); execute SP_TAB_EXISTS('A_SPECIALITY_HIST_S','SEQUENCE'); CREATE TABLE A_SPECIALITY_HIST( SPH_SEQU NUMBER(9,0) not null, SPH_SU_SEQU NUMBER(9,0) not null, SPH_START_DATE date null, SPH_END_DATE date null, SPH_S1_SEQU NUMBER(9,0) not null, SID_SPID NUMBER(18,0), D_ACTION VARCHAR2(1), COUNTER NUMBER(18), INSERT_DATE_TIME DATE, MACHINENAME VARCHAR2(50), PROGRAM_NAME VARCHAR2(50), NT_DOMAIN VARCHAR2(50), NT_USERNAME VARCHAR2(50) ); CREATE SEQUENCE A_SPECIALITY_HIST_S MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE; execute SP_TAB_EXISTS('A_STAFFCATEGORY_HIST','TABLE'); execute SP_TAB_EXISTS('A_STAFFCATEGORY_HIST_S','SEQUENCE'); CREATE TABLE A_STAFFCATEGORY_HIST( SCH_SEQU NUMBER(9,0) not null, SCH_SU_SEQU NUMBER(9,0) not null, SCH_START_DATE date null, SCH_END_DATE date null, SCH_SC_SEQU NUMBER(9,0) not null, SID_SPID NUMBER(18,0), D_ACTION VARCHAR2(1), COUNTER NUMBER(18), INSERT_DATE_TIME DATE, MACHINENAME VARCHAR2(50), PROGRAM_NAME VARCHAR2(50), NT_DOMAIN VARCHAR2(50), NT_USERNAME VARCHAR2(50) ); CREATE SEQUENCE A_STAFFCATEGORY_HIST_S MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE; execute SP_TAB_EXISTS('A_INTFCE_REPOS','TABLE'); execute SP_TAB_EXISTS('A_INTFCE_REPOS_S','SEQUENCE'); CREATE TABLE A_INTFCE_REPOS( IR_SEQU NUMBER(9,0) not null, IR_INTFC_NAME VARCHAR2(30) null, IR_ACTIVE NUMBER(3,0) not null, IR_CONN_PARAM_1 VARCHAR2(30) null, IR_CONN_PARAM_2 VARCHAR2(30) null, IR_CONN_PARAM_3 VARCHAR2(30) null, IR_CONN_PARAM_4 VARCHAR2(30) null, IR_CONN_PARAM_5 VARCHAR2(30) null, IR_CUSTOM_PARAM_1 NUMBER(3,0) not null, IR_NACK_RETRY NUMBER(9,0) not null, IR_RETRY_COMS NUMBER(9,0) not null, IR_RETRY_TIMEOUT NUMBER(9,0) not null, IR_CA_SEQU NUMBER(9,0) not null, IR_HL7_NAME VARCHAR2(20) null, SID_SPID NUMBER(18,0), D_ACTION VARCHAR2(1), COUNTER NUMBER(18), INSERT_DATE_TIME DATE, MACHINENAME VARCHAR2(50), PROGRAM_NAME VARCHAR2(50), NT_DOMAIN VARCHAR2(50), NT_USERNAME VARCHAR2(50) ); CREATE SEQUENCE A_INTFCE_REPOS_S MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE; execute SP_TAB_EXISTS('A_STORE_FORWARD','TABLE'); execute SP_TAB_EXISTS('A_STORE_FORWARD_S','SEQUENCE'); CREATE TABLE A_STORE_FORWARD( SAF_SEQU NUMBER(9,0) not null, SAF_PA_SEQU NUMBER(9,0) not null, SAF_OP_SEQU NUMBER(9,0) not null, SAF_REC_SEQU NUMBER(9,0) not null, SAF_DATE date null, SAF_EVENT VARCHAR2(30) null, SAF_CA_SEQU NUMBER(9,0) not null, SAF_PATIENT_NAME varchar2(140) NULL, SID_SPID NUMBER(18,0), D_ACTION VARCHAR2(1), COUNTER NUMBER(18), INSERT_DATE_TIME DATE, MACHINENAME VARCHAR2(50), PROGRAM_NAME VARCHAR2(50), NT_DOMAIN VARCHAR2(50), NT_USERNAME VARCHAR2(50) ); CREATE SEQUENCE A_STORE_FORWARD_S MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE; execute SP_TAB_EXISTS('A_ASCII_MAP','TABLE'); execute SP_TAB_EXISTS('A_ASCII_MAP_S','SEQUENCE'); CREATE TABLE A_ASCII_MAP( ASC_SEQU NUMBER(9,0) not null, ASC_DEC NUMBER(9,0) not null, ASC_MAPPED VARCHAR2(20) null, ASC_IR_SEQU NUMBER(9,0) not null, SID_SPID NUMBER(18,0), D_ACTION VARCHAR2(1), COUNTER NUMBER(18), INSERT_DATE_TIME DATE, MACHINENAME VARCHAR2(50), PROGRAM_NAME VARCHAR2(50), NT_DOMAIN VARCHAR2(50), NT_USERNAME VARCHAR2(50) ); CREATE SEQUENCE A_ASCII_MAP_S MINVALUE 1 MAXVALUE 999999999999999999 START WITH 1 INCREMENT BY 1 NOCACHE; /****************************************************/ /* Updated Sequ for New tables */ /****************************************************/ insert into F_Sequ values('A_SPECIALITY_HIST',0) ; insert into F_Sequ values('A_STAFFCATEGORY_HIST',0) ; insert into F_Sequ values('A_INTFCE_REPOS',0) ; delete from F_Sequ where SQ_TABLE='A_STORE_FORWARD' ; insert into F_Sequ values('A_STORE_FORWARD',0) ; insert into F_Sequ values('A_ASCII_MAP',0) ; /****************************************************/ /* New or modified Stored Procedures */ /****************************************************/ /*****************************************************/ /*SP_APATS is changed for PA_SURG_COMM,PA_HSC_COMMENTS fields are changed*/ /******************************************************/ CREATE OR REPLACE PROCEDURE SP_APATS IS PA_SEQN_FILLER_V NUMBER(9,0); PA_SEQN_FILLER_P NUMBER(9,0); PA_SEQN_FILLER_N VARCHAR2(50):='PA_SEQN_FILLER'; PA_SEQU_V NUMBER(9,0); PA_SEQU_P NUMBER(9,0); PA_SEQU_N VARCHAR2(50):='PA_SEQU'; PA_MRN_V VARCHAR2(12); PA_MRN_P VARCHAR2(12); PA_MRN_N VARCHAR2(50):='PA_MRN'; PA_OPER_DATE_V date; PA_OPER_DATE_P date; PA_OPER_DATE_N VARCHAR2(50):='PA_OPER_DATE'; PA_TH_CODE_V VARCHAR2(8); PA_TH_CODE_P VARCHAR2(8); PA_TH_CODE_N VARCHAR2(50):='PA_TH_CODE'; PA_CUSTODIAN_V VARCHAR2(50); PA_CUSTODIAN_P VARCHAR2(50); PA_CUSTODIAN_N VARCHAR2(50):='PA_CUSTODIAN'; PA_SESS_TYPE_V VARCHAR2(2); PA_SESS_TYPE_P VARCHAR2(2); PA_SESS_TYPE_N VARCHAR2(50):='PA_SESS_TYPE'; PA_LAST_NAME_V VARCHAR2(60); PA_LAST_NAME_P VARCHAR2(60); PA_LAST_NAME_N VARCHAR2(50):='PA_LAST_NAME'; PA_FIRST_NAME_V VARCHAR2(60); PA_FIRST_NAME_P VARCHAR2(60); PA_FIRST_NAME_N VARCHAR2(50):='PA_FIRST_NAME'; PA_DOB_V date; PA_DOB_P date; PA_DOB_N VARCHAR2(50):='PA_DOB'; PA_PIN_SEQU_V NUMBER(9,0); PA_PIN_SEQU_P NUMBER(9,0); PA_PIN_SEQU_N VARCHAR2(50):='PA_PIN_SEQU'; PA_TH_SEQU_V NUMBER(9,0); PA_TH_SEQU_P NUMBER(9,0); PA_TH_SEQU_N VARCHAR2(50):='PA_TH_SEQU'; PA_CONS_SEQU_V NUMBER(9,0); PA_CONS_SEQU_P NUMBER(9,0); PA_CONS_SEQU_N VARCHAR2(50):='PA_CONS_SEQU'; PA_SUCODE_V VARCHAR2(5); PA_SUCODE_P VARCHAR2(5); PA_SUCODE_N VARCHAR2(50):='PA_SUCODE'; PA_BIOHAZARD_V NUMBER(9,0); PA_BIOHAZARD_P NUMBER(9,0); PA_BIOHAZARD_N VARCHAR2(50):='PA_BIOHAZARD'; PA_BLOOD_V NUMBER(3,0); PA_BLOOD_P NUMBER(3,0); PA_BLOOD_N VARCHAR2(50):='PA_BLOOD'; PA_EQUIPMENT_V VARCHAR2(200); PA_EQUIPMENT_P VARCHAR2(200); PA_EQUIPMENT_N VARCHAR2(50):='PA_EQUIPMENT'; PA_AGEYR_V NUMBER(3,0); PA_AGEYR_P NUMBER(3,0); PA_AGEYR_N VARCHAR2(50):='PA_AGEYR'; PA_AGEMNTH_V NUMBER(3,0); PA_AGEMNTH_P NUMBER(3,0); PA_AGEMNTH_N VARCHAR2(50):='PA_AGEMNTH'; PA_WARD_V VARCHAR2(7); PA_WARD_P VARCHAR2(7); PA_WARD_N VARCHAR2(50):='PA_WARD'; PA_TIME_V date; PA_TIME_P date; PA_TIME_N VARCHAR2(50):='PA_TIME'; PA_EPISODE_NUM_V VARCHAR2(40); PA_EPISODE_NUM_P VARCHAR2(40); PA_EPISODE_NUM_N VARCHAR2(50):='PA_EPISODE_NUM'; PA_OPCOMM_V VARCHAR2(2000); PA_OPCOMM_P VARCHAR2(2000); PA_OPCOMM_N VARCHAR2(50):='PA_OPCOMM'; PA_ORDER_V NUMBER(9,0); PA_ORDER_P NUMBER(9,0); PA_ORDER_N VARCHAR2(50):='PA_ORDER'; PA_TRANSF_V NUMBER(9,0); PA_TRANSF_P NUMBER(9,0); PA_TRANSF_N VARCHAR2(50):='PA_TRANSF'; PA_STAT_V VARCHAR2(1); PA_STAT_P VARCHAR2(1); PA_STAT_N VARCHAR2(50):='PA_STAT'; PA_CLCODE_V VARCHAR2(5); PA_CLCODE_P VARCHAR2(5); PA_CLCODE_N VARCHAR2(50):='PA_CLCODE'; PA_ANA_SEQU_V NUMBER(9,0); PA_ANA_SEQU_P NUMBER(9,0); PA_ANA_SEQU_N VARCHAR2(50):='PA_ANA_SEQU'; PA_ANAES_TYPE_V VARCHAR2(3); PA_ANAES_TYPE_P VARCHAR2(3); PA_ANAES_TYPE_N VARCHAR2(50):='PA_ANAES_TYPE'; PA_SURG_COMM_V VARCHAR2(1999); PA_SURG_COMM_P VARCHAR2(1999); PA_SURG_COMM_N VARCHAR2(50):='PA_SURG_COMM'; PA_SURG_SPEC_V NUMBER(9,0); PA_SURG_SPEC_P NUMBER(9,0); PA_SURG_SPEC_N VARCHAR2(50):='PA_SURG_SPEC'; PA_BED_V VARCHAR2(5); PA_BED_P VARCHAR2(5); PA_BED_N VARCHAR2(50):='PA_BED'; PA_WLSEQ_V NUMBER(9,0); PA_WLSEQ_P NUMBER(9,0); PA_WLSEQ_N VARCHAR2(50):='PA_WLSEQ'; PA_SESSION_NO_V NUMBER(3,0); PA_SESSION_NO_P NUMBER(3,0); PA_SESSION_NO_N VARCHAR2(50):='PA_SESSION_NO'; PA_TOTAL_MINS_V NUMBER(9,0); PA_TOTAL_MINS_P NUMBER(9,0); PA_TOTAL_MINS_N VARCHAR2(50):='PA_TOTAL_MINS'; PA_CHANGE_OVER_V NUMBER(9,0); PA_CHANGE_OVER_P NUMBER(9,0); PA_CHANGE_OVER_N VARCHAR2(50):='PA_CHANGE_OVER'; PA_GENDER_V VARCHAR2(1); PA_GENDER_P VARCHAR2(1); PA_GENDER_N VARCHAR2(50):='PA_GENDER'; PA_SURGEON_V VARCHAR2(5); PA_SURGEON_P VARCHAR2(5); PA_SURGEON_N VARCHAR2(50):='PA_SURGEON'; PA_FILLER_01_V date; PA_FILLER_01_P date; PA_FILLER_01_N VARCHAR2(50):='PA_FILLER_01'; PA_REQ_TIME_V date; PA_REQ_TIME_P date; PA_REQ_TIME_N VARCHAR2(50):='PA_REQ_TIME'; PA_TIME_READY_V date; PA_TIME_READY_P date; PA_TIME_READY_N VARCHAR2(50):='PA_TIME_READY'; PA_VERIFIED_V NUMBER(3,0); PA_VERIFIED_P NUMBER(3,0); PA_VERIFIED_N VARCHAR2(50):='PA_VERIFIED'; PA_UNPLANNED_V NUMBER(3,0); PA_UNPLANNED_P NUMBER(3,0); PA_UNPLANNED_N VARCHAR2(50):='PA_UNPLANNED'; PA_TYPE_V VARCHAR2(1); PA_TYPE_P VARCHAR2(1); PA_TYPE_N VARCHAR2(50):='PA_TYPE'; PA_TH_SESS_SORT_V VARCHAR2(14); PA_TH_SESS_SORT_P VARCHAR2(14); PA_TH_SESS_SORT_N VARCHAR2(50):='PA_TH_SESS_SORT'; PA_EST_START_V date; PA_EST_START_P date; PA_EST_START_N VARCHAR2(50):='PA_EST_START'; PA_SU_SEQU_V NUMBER(9,0); PA_SU_SEQU_P NUMBER(9,0); PA_SU_SEQU_N VARCHAR2(50):='PA_SU_SEQU'; PA_CL_SEQU_V NUMBER(9,0); PA_CL_SEQU_P NUMBER(9,0); PA_CL_SEQU_N VARCHAR2(50):='PA_CL_SEQU'; PA_WA_SEQU_V NUMBER(9,0); PA_WA_SEQU_P NUMBER(9,0); PA_WA_SEQU_N VARCHAR2(50):='PA_WA_SEQU'; PA_S1_SEQU_V NUMBER(9,0); PA_S1_SEQU_P NUMBER(9,0); PA_S1_SEQU_N VARCHAR2(50):='PA_S1_SEQU'; PA_LOG_DATE_V date; PA_LOG_DATE_P date; PA_LOG_DATE_N VARCHAR2(50):='PA_LOG_DATE'; PA_LOG_DETAILS_FILLER_V VARCHAR2(1); PA_LOG_DETAILS_FILLER_P VARCHAR2(1); PA_LOG_DETAILS_FILLER_N VARCHAR2(50):='PA_LOG_DETAILS_FILLER'; PA_WL_SEQU_V NUMBER(9,0); PA_WL_SEQU_P NUMBER(9,0); PA_WL_SEQU_N VARCHAR2(50):='PA_WL_SEQU'; PA_AN_SEQU_V NUMBER(9,0); PA_AN_SEQU_P NUMBER(9,0); PA_AN_SEQU_N VARCHAR2(50):='PA_AN_SEQU'; PA_STREET_V VARCHAR2(60); PA_STREET_P VARCHAR2(60); PA_STREET_N VARCHAR2(50):='PA_STREET'; PA_SUBURB_V VARCHAR2(60); PA_SUBURB_P VARCHAR2(60); PA_SUBURB_N VARCHAR2(50):='PA_SUBURB'; PA_POSTCODE_V VARCHAR2(12); PA_POSTCODE_P VARCHAR2(12); PA_POSTCODE_N VARCHAR2(50):='PA_POSTCODE'; PA_HOME_PHONE_V VARCHAR2(25); PA_HOME_PHONE_P VARCHAR2(25); PA_HOME_PHONE_N VARCHAR2(50):='PA_HOME_PHONE'; PA_BUS_PHONE_V VARCHAR2(25); PA_BUS_PHONE_P VARCHAR2(25); PA_BUS_PHONE_N VARCHAR2(50):='PA_BUS_PHONE'; PA_AGDAYS_V NUMBER(3,0); PA_AGDAYS_P NUMBER(3,0); PA_AGDAYS_N VARCHAR2(50):='PA_AGDAYS'; PA_TA_SEQU_V NUMBER(9,0); PA_TA_SEQU_P NUMBER(9,0); PA_TA_SEQU_N VARCHAR2(50):='PA_TA_SEQU'; PA_CHANGE_FLAG_V NUMBER(3,0); PA_CHANGE_FLAG_P NUMBER(3,0); PA_CHANGE_FLAG_N VARCHAR2(50):='PA_CHANGE_FLAG'; PA_ASA_SEQU_V NUMBER(9,0); PA_ASA_SEQU_P NUMBER(9,0); PA_ASA_SEQU_N VARCHAR2(50):='PA_ASA_SEQU'; PA_UNUSED_4_V VARCHAR2(1); PA_UNUSED_4_P VARCHAR2(1); PA_UNUSED_4_N VARCHAR2(50):='PA_UNUSED_4'; PA_UNUSED_5_V VARCHAR2(1); PA_UNUSED_5_P VARCHAR2(1); PA_UNUSED_5_N VARCHAR2(50):='PA_UNUSED_5'; PA_UNUSED_6_V VARCHAR2(1); PA_UNUSED_6_P VARCHAR2(1); PA_UNUSED_6_N VARCHAR2(50):='PA_UNUSED_6'; PA_UNUSED_7_V VARCHAR2(1); PA_UNUSED_7_P VARCHAR2(1); PA_UNUSED_7_N VARCHAR2(50):='PA_UNUSED_7'; PA_UNUSED_8_V VARCHAR2(1); PA_UNUSED_8_P VARCHAR2(1); PA_UNUSED_8_N VARCHAR2(50):='PA_UNUSED_8'; PA_PATHOLOGY_V NUMBER(3,0); PA_PATHOLOGY_P NUMBER(3,0); PA_PATHOLOGY_N VARCHAR2(50):='PA_PATHOLOGY'; PA_XRAY_V NUMBER(3,0); PA_XRAY_P NUMBER(3,0); PA_XRAY_N VARCHAR2(50):='PA_XRAY'; PA_PATH_COMMENT_V VARCHAR2(1000); PA_PATH_COMMENT_P VARCHAR2(1000); PA_PATH_COMMENT_N VARCHAR2(50):='PA_PATH_COMMENT'; PA_XRAY_COMMENT_V VARCHAR2(1000); PA_XRAY_COMMENT_P VARCHAR2(1000); PA_XRAY_COMMENT_N VARCHAR2(50):='PA_XRAY_COMMENT'; PA_BLOOD_COMMENT_V VARCHAR2(1000); PA_BLOOD_COMMENT_P VARCHAR2(1000); PA_BLOOD_COMMENT_N VARCHAR2(50):='PA_BLOOD_COMMENT'; PA_BLOOD_NO_OF_UNITS_V NUMBER(9,0); PA_BLOOD_NO_OF_UNITS_P NUMBER(9,0); PA_BLOOD_NO_OF_UNITS_N VARCHAR2(50):='PA_BLOOD_NO_OF_UNITS'; PA_WARD_ADV_DATE_TIME_V date; PA_WARD_ADV_DATE_TIME_P date; PA_WARD_ADV_DATE_TIME_N VARCHAR2(50):='PA_WARD_ADV_DATE_TIME'; PA_FILLER_02_V date; PA_FILLER_02_P date; PA_FILLER_02_N VARCHAR2(50):='PA_FILLER_02'; PA_TRANS_V NUMBER(9,0); PA_TRANS_P NUMBER(9,0); PA_TRANS_N VARCHAR2(50):='PA_TRANS'; PA_NURSE_REQ_SEQU_V NUMBER(9,0); PA_NURSE_REQ_SEQU_P NUMBER(9,0); PA_NURSE_REQ_SEQU_N VARCHAR2(50):='PA_NURSE_REQ_SEQU'; PA_NURSE_REC_NOTIF_V VARCHAR2(25); PA_NURSE_REC_NOTIF_P VARCHAR2(25); PA_NURSE_REC_NOTIF_N VARCHAR2(50):='PA_NURSE_REC_NOTIF'; PA_OXYGEN_V NUMBER(3,0); PA_OXYGEN_P NUMBER(3,0); PA_OXYGEN_N VARCHAR2(50):='PA_OXYGEN'; PA_IV_V NUMBER(3,0); PA_IV_P NUMBER(3,0); PA_IV_N VARCHAR2(50):='PA_IV'; PA_WA_TO_SEQU_V NUMBER(9,0); PA_WA_TO_SEQU_P NUMBER(9,0); PA_WA_TO_SEQU_N VARCHAR2(50):='PA_WA_TO_SEQU'; PA_SU2_SEQU_V NUMBER(9,0); PA_SU2_SEQU_P NUMBER(9,0); PA_SU2_SEQU_N VARCHAR2(50):='PA_SU2_SEQU'; PA_SU3_SEQU_V NUMBER(9,0); PA_SU3_SEQU_P NUMBER(9,0); PA_SU3_SEQU_N VARCHAR2(50):='PA_SU3_SEQU'; PA_AN2_SEQU_V NUMBER(9,0); PA_AN2_SEQU_P NUMBER(9,0); PA_AN2_SEQU_N VARCHAR2(50):='PA_AN2_SEQU'; PA_AN3_SEQU_V NUMBER(9,0); PA_AN3_SEQU_P NUMBER(9,0); PA_AN3_SEQU_N VARCHAR2(50):='PA_AN3_SEQU'; PA_SC_NUR_SEQU_V NUMBER(9,0); PA_SC_NUR_SEQU_P NUMBER(9,0); PA_SC_NUR_SEQU_N VARCHAR2(50):='PA_SC_NUR_SEQU'; PA_AN_NUR_SEQU_V NUMBER(9,0); PA_AN_NUR_SEQU_P NUMBER(9,0); PA_AN_NUR_SEQU_N VARCHAR2(50):='PA_AN_NUR_SEQU'; PA_IN_NUR_SEQU_V NUMBER(9,0); PA_IN_NUR_SEQU_P NUMBER(9,0); PA_IN_NUR_SEQU_N VARCHAR2(50):='PA_IN_NUR_SEQU'; PA_TECH1_SEQU_V NUMBER(9,0); PA_TECH1_SEQU_P NUMBER(9,0); PA_TECH1_SEQU_N VARCHAR2(50):='PA_TECH1_SEQU'; PA_TECH2_SEQU_V NUMBER(9,0); PA_TECH2_SEQU_P NUMBER(9,0); PA_TECH2_SEQU_N VARCHAR2(50):='PA_TECH2_SEQU'; PA_UNUSED_1_V VARCHAR2(1); PA_UNUSED_1_P VARCHAR2(1); PA_UNUSED_1_N VARCHAR2(50):='PA_UNUSED_1'; PA_TIME_THEATRE_V date; PA_TIME_THEATRE_P date; PA_TIME_THEATRE_N VARCHAR2(50):='PA_TIME_THEATRE'; PA_CA_SEQU_V NUMBER(9,0); PA_CA_SEQU_P NUMBER(9,0); PA_CA_SEQU_N VARCHAR2(50):='PA_CA_SEQU'; PA_CLP_SEQU_V NUMBER(9,0); PA_CLP_SEQU_P NUMBER(9,0); PA_CLP_SEQU_N VARCHAR2(50):='PA_CLP_SEQU'; PA_BOOK_SU_SEQU_V NUMBER(9,0); PA_BOOK_SU_SEQU_P NUMBER(9,0); PA_BOOK_SU_SEQU_N VARCHAR2(50):='PA_BOOK_SU_SEQU'; PA_ADDITIONAL_V NUMBER(3,0); PA_ADDITIONAL_P NUMBER(3,0); PA_ADDITIONAL_N VARCHAR2(50):='PA_ADDITIONAL'; PA_CAM_SEQU_V NUMBER(9,0); PA_CAM_SEQU_P NUMBER(9,0); PA_CAM_SEQU_N VARCHAR2(50):='PA_CAM_SEQU'; PA_CAM_COMMENT_V VARCHAR2(200); PA_CAM_COMMENT_P VARCHAR2(200); PA_CAM_COMMENT_N VARCHAR2(50):='PA_CAM_COMMENT'; PA_ADMIT_SU_SEQU_V NUMBER(9,0); PA_ADMIT_SU_SEQU_P NUMBER(9,0); PA_ADMIT_SU_SEQU_N VARCHAR2(50):='PA_ADMIT_SU_SEQU'; PA_FLAG_V NUMBER(3,0); PA_FLAG_P NUMBER(3,0); PA_FLAG_N VARCHAR2(50):='PA_FLAG'; PA_EXT_NO_FIELD_V VARCHAR2(40); PA_EXT_NO_FIELD_P VARCHAR2(40); PA_EXT_NO_FIELD_N VARCHAR2(50):='PA_EXT_NO_FIELD'; PA_FLAG_2_V NUMBER(9,0); PA_FLAG_2_P NUMBER(9,0); PA_FLAG_2_N VARCHAR2(50):='PA_FLAG_2'; PA_ADMIT_DATE_TIME_V date; PA_ADMIT_DATE_TIME_P date; PA_ADMIT_DATE_TIME_N VARCHAR2(50):='PA_ADMIT_DATE_TIME'; PA_ACC_INDICATOR_V NUMBER(3,0); PA_ACC_INDICATOR_P NUMBER(3,0); PA_ACC_INDICATOR_N VARCHAR2(50):='PA_ACC_INDICATOR'; PA_ACC_NUMBER_V VARCHAR2(12); PA_ACC_NUMBER_P VARCHAR2(12); PA_ACC_NUMBER_N VARCHAR2(50):='PA_ACC_NUMBER'; PA_ACC_SEQU_V NUMBER(9,0); PA_ACC_SEQU_P NUMBER(9,0); PA_ACC_SEQU_N VARCHAR2(50):='PA_ACC_SEQU'; PA_MAJOR_MINOR_V NUMBER(3,0); PA_MAJOR_MINOR_P NUMBER(3,0); PA_MAJOR_MINOR_N VARCHAR2(50):='PA_MAJOR_MINOR'; PA_FIXED_TIME_V NUMBER(3,0); PA_FIXED_TIME_P NUMBER(3,0); PA_FIXED_TIME_N VARCHAR2(50):='PA_FIXED_TIME'; PA_LA_SEQU_V NUMBER(9,0); PA_LA_SEQU_P NUMBER(9,0); PA_LA_SEQU_N VARCHAR2(50):='PA_LA_SEQU'; PA_LMO_SEQU_V NUMBER(9,0); PA_LMO_SEQU_P NUMBER(9,0); PA_LMO_SEQU_N VARCHAR2(50):='PA_LMO_SEQU'; PA_REF_SEQU_V NUMBER(9,0); PA_REF_SEQU_P NUMBER(9,0); PA_REF_SEQU_N VARCHAR2(50):='PA_REF_SEQU'; PA_MS_SEQU_V NUMBER(9,0); PA_MS_SEQU_P NUMBER(9,0); PA_MS_SEQU_N VARCHAR2(50):='PA_MS_SEQU'; PA_STATE_V VARCHAR2(12); PA_STATE_P VARCHAR2(12); PA_STATE_N VARCHAR2(50):='PA_STATE'; PA_UPD_DATE_V date; PA_UPD_DATE_P date; PA_UPD_DATE_N VARCHAR2(50):='PA_UPD_DATE'; PA_OLD_MRN_V VARCHAR2(12); PA_OLD_MRN_P VARCHAR2(12); PA_OLD_MRN_N VARCHAR2(50):='PA_OLD_MRN'; PA_EST_ARR_TIME_V date; PA_EST_ARR_TIME_P date; PA_EST_ARR_TIME_N VARCHAR2(50):='PA_EST_ARR_TIME'; PA_STREET_2_V VARCHAR2(60); PA_STREET_2_P VARCHAR2(60); PA_STREET_2_N VARCHAR2(50):='PA_STREET_2'; PA_CITY_V VARCHAR2(60); PA_CITY_P VARCHAR2(60); PA_CITY_N VARCHAR2(50):='PA_CITY'; PA_SENT_FOR_DATE_TIME_V date; PA_SENT_FOR_DATE_TIME_P date; PA_SENT_FOR_DATE_TIME_N VARCHAR2(50):='PA_SENT_FOR_DATE_TIME'; PA_PORTER_LEFT_DATE_TIME_V date; PA_PORTER_LEFT_DATE_TIME_P date; PA_PORTER_LEFT_DATE_TIME_N VARCHAR2(50):='PA_PORTER_LEFT_DATE_TIME'; PA_PORTER_SEQU_V NUMBER(9,0); PA_PORTER_SEQU_P NUMBER(9,0); PA_PORTER_SEQU_N VARCHAR2(50):='PA_PORTER_SEQU'; PA_ORDER_CC_V NUMBER(9,0); PA_ORDER_CC_P NUMBER(9,0); PA_ORDER_CC_N VARCHAR2(50):='PA_ORDER_CC'; PA_CONS_CC_SEQU_V NUMBER(9,0); PA_CONS_CC_SEQU_P NUMBER(9,0); PA_CONS_CC_SEQU_N VARCHAR2(50):='PA_CONS_CC_SEQU'; PA_TH_CC_SEQU_V NUMBER(9,0); PA_TH_CC_SEQU_P NUMBER(9,0); PA_TH_CC_SEQU_N VARCHAR2(50):='PA_TH_CC_SEQU'; PA_SESSION_NO_CC_V NUMBER(3,0); PA_SESSION_NO_CC_P NUMBER(3,0); PA_SESSION_NO_CC_N VARCHAR2(50):='PA_SESSION_NO_CC'; PA_EST_START_CC_V date; PA_EST_START_CC_P date; PA_EST_START_CC_N VARCHAR2(50):='PA_EST_START_CC'; PA_S1_CC_SEQU_V NUMBER(9,0); PA_S1_CC_SEQU_P NUMBER(9,0); PA_S1_CC_SEQU_N VARCHAR2(50):='PA_S1_CC_SEQU'; PA_SU_CC_SEQU_V NUMBER(9,0); PA_SU_CC_SEQU_P NUMBER(9,0); PA_SU_CC_SEQU_N VARCHAR2(50):='PA_SU_CC_SEQU'; PA_AN_CC_SEQU_V NUMBER(9,0); PA_AN_CC_SEQU_P NUMBER(9,0); PA_AN_CC_SEQU_N VARCHAR2(50):='PA_AN_CC_SEQU'; PA_COMBINED_FLAG_V NUMBER(3,0); PA_COMBINED_FLAG_P NUMBER(3,0); PA_COMBINED_FLAG_N VARCHAR2(50):='PA_COMBINED_FLAG'; PA_FIXED_TIME_CC_V NUMBER(3,0); PA_FIXED_TIME_CC_P NUMBER(3,0); PA_FIXED_TIME_CC_N VARCHAR2(50):='PA_FIXED_TIME_CC'; PA_PICKLIST_PRINTED_V NUMBER(3,0); PA_PICKLIST_PRINTED_P NUMBER(3,0); PA_PICKLIST_PRINTED_N VARCHAR2(50):='PA_PICKLIST_PRINTED'; PA_PICKLIST_MERGED_V NUMBER(3,0); PA_PICKLIST_MERGED_P NUMBER(3,0); PA_PICKLIST_MERGED_N VARCHAR2(50):='PA_PICKLIST_MERGED'; BK_RECD_DT_TM_V date; BK_RECD_DT_TM_P date; BK_RECD_DT_TM_N VARCHAR2(50):='PA_BOOKING_RECEIVED_DATE_TIME'; PA_INITIATED_BY_NAME_V VARCHAR2(60); PA_INITIATED_BY_NAME_P VARCHAR2(60); PA_INITIATED_BY_NAME_N VARCHAR2(50):='PA_INITIATED_BY_NAME'; PA_REQ_S1_SEQU_V NUMBER(9,0); PA_REQ_S1_SEQU_P NUMBER(9,0); PA_REQ_S1_SEQU_N VARCHAR2(50):='PA_REQ_S1_SEQU'; PA_HSC_COMMENTS_V VARCHAR2(500); PA_HSC_COMMENTS_P VARCHAR2(500); PA_HSC_COMMENTS_N VARCHAR2(50):='PA_HSC_COMMENTS'; PA_SS_SEQU_V NUMBER(9,0); PA_SS_SEQU_P NUMBER(9,0); PA_SS_SEQU_N VARCHAR2(50):='PA_SS_SEQU'; PA_CC_SS_SEQU_V NUMBER(9,0); PA_CC_SS_SEQU_P NUMBER(9,0); PA_CC_SS_SEQU_N VARCHAR2(50):='PA_CC_SS_SEQU'; PA_ORDER_REASON_SEQU_V NUMBER(9,0); PA_ORDER_REASON_SEQU_P NUMBER(9,0); PA_ORDER_REASON_SEQU_N VARCHAR2(50):='PA_ORDER_REASON_SEQU'; PA_PRINTED_V NUMBER(3,0); PA_PRINTED_P NUMBER(3,0); PA_PRINTED_N VARCHAR2(50):='PA_PRINTED'; PA_PR_SEQU_V NUMBER(9,0); PA_PR_SEQU_P NUMBER(9,0); PA_PR_SEQU_N VARCHAR2(50):='PA_PR_SEQU'; PA_DELAY_COMMENTS_V VARCHAR2(400); PA_DELAY_COMMENTS_P VARCHAR2(400); PA_DELAY_COMMENTS_N VARCHAR2(50):='PA_DELAY_COMMENTS'; PA_MIDDLE_NAME_V VARCHAR2(60); PA_MIDDLE_NAME_P VARCHAR2(60); PA_MIDDLE_NAME_N VARCHAR2(50):='PA_MIDDLE_NAME'; PA_IMS_CONFIRM_SENT_V NUMBER(3,0); PA_IMS_CONFIRM_SENT_P NUMBER(3,0); PA_IMS_CONFIRM_SENT_N VARCHAR2(50):='PA_IMS_CONFIRM_SENT'; PA_SEEN_ANAES_SEQU_V NUMBER(9,0); PA_SEEN_ANAES_SEQU_P NUMBER(9,0); PA_SEEN_ANAES_SEQU_N VARCHAR2(50):='PA_SEEN_ANAES_SEQU'; PA_DIAG_TEST_COMP_SEQU_V NUMBER(9,0); PA_DIAG_TEST_COMP_SEQU_P NUMBER(9,0); PA_DIAG_TEST_COMP_SEQU_N VARCHAR2(50):='PA_DIAG_TEST_COMP_SEQU'; PA_FASTED_SEQU_V NUMBER(9,0); PA_FASTED_SEQU_P NUMBER(9,0); PA_FASTED_SEQU_N VARCHAR2(50):='PA_FASTED_SEQU'; PA_BREACH_REAS_SEQU_V NUMBER(9,0); PA_BREACH_REAS_SEQU_P NUMBER(9,0); PA_BREACH_REAS_SEQU_N VARCHAR2(50):='PA_BREACH_REAS_SEQU'; PA_IMS_BKG_REF_V VARCHAR2(10); PA_IMS_BKG_REF_P VARCHAR2(10); PA_IMS_BKG_REF_N VARCHAR2(50):='PA_IMS_BKG_REF'; LV_COUNTER NUMBER(18,0); LV_COUNTER_P NUMBER(18,0); LV_SID_SPID NUMBER(18,0); LV_D_ACTION VARCHAR2(1); LV_INSERT_DATE_TIME DATE; LV_MACHINENAME VARCHAR2(50); LV_APP_USER VARCHAR2(20); LV_PROGRAM_NAME VARCHAR2(50); LV_NT_DOMAIN VARCHAR2(50); LV_NT_USERNAME VARCHAR2(50); LV_LAST_PROCESS_SEQU NUMBER(9,0); LV_AUDIT_TABLE VARCHAR2(50); LV_AUDIT_PRIMARY_KEY_NAME VARCHAR2(100); LV_AUDIT_TEXT VARCHAR2(8000); LV_AUDT_REC_SEQU VARCHAR2(100); CURSOR C_APATS IS SELECT PA_SEQN_FILLER, PA_SEQU, PA_MRN, PA_OPER_DATE, PA_TH_CODE, PA_CUSTODIAN, PA_SESS_TYPE, PA_LAST_NAME, PA_FIRST_NAME, PA_DOB, PA_PIN_SEQU, PA_TH_SEQU, PA_CONS_SEQU, PA_SUCODE, PA_BIOHAZARD, PA_BLOOD, PA_EQUIPMENT, PA_AGEYR, PA_AGEMNTH, PA_WARD, PA_TIME, PA_EPISODE_NUM, PA_OPCOMM, PA_ORDER, PA_TRANSF, PA_STAT, PA_CLCODE, PA_ANA_SEQU, PA_ANAES_TYPE, PA_SURG_COMM, PA_SURG_SPEC, PA_BED, PA_WLSEQ, PA_SESSION_NO, PA_TOTAL_MINS, PA_CHANGE_OVER, PA_GENDER, PA_SURGEON, PA_FILLER_01, PA_REQ_TIME, PA_TIME_READY, PA_VERIFIED, PA_UNPLANNED, PA_TYPE, PA_TH_SESS_SORT, PA_EST_START, PA_SU_SEQU, PA_CL_SEQU, PA_WA_SEQU, PA_S1_SEQU, PA_LOG_DATE, PA_LOG_DETAILS_FILLER, PA_WL_SEQU, PA_AN_SEQU, PA_STREET, PA_SUBURB, PA_POSTCODE, PA_HOME_PHONE, PA_BUS_PHONE, PA_AGDAYS, PA_TA_SEQU, PA_CHANGE_FLAG, PA_ASA_SEQU, PA_UNUSED_4, PA_UNUSED_5, PA_UNUSED_6, PA_UNUSED_7, PA_UNUSED_8, PA_PATHOLOGY, PA_XRAY, PA_PATH_COMMENT, PA_XRAY_COMMENT, PA_BLOOD_COMMENT, PA_BLOOD_NO_OF_UNITS, PA_WARD_ADV_DATE_TIME, PA_FILLER_02, PA_TRANS, PA_NURSE_REQ_SEQU, PA_NURSE_REC_NOTIF, PA_OXYGEN, PA_IV, PA_WA_TO_SEQU, PA_SU2_SEQU, PA_SU3_SEQU, PA_AN2_SEQU, PA_AN3_SEQU, PA_SC_NUR_SEQU, PA_AN_NUR_SEQU, PA_IN_NUR_SEQU, PA_TECH1_SEQU, PA_TECH2_SEQU, PA_UNUSED_1, PA_TIME_THEATRE, PA_CA_SEQU, PA_CLP_SEQU, PA_BOOK_SU_SEQU, PA_ADDITIONAL, PA_CAM_SEQU, PA_CAM_COMMENT, PA_ADMIT_SU_SEQU, PA_FLAG, PA_EXT_NO_FIELD, PA_FLAG_2, PA_ADMIT_DATE_TIME, PA_ACC_INDICATOR, PA_ACC_NUMBER, PA_ACC_SEQU, PA_MAJOR_MINOR, PA_FIXED_TIME, PA_LA_SEQU, PA_LMO_SEQU, PA_REF_SEQU, PA_MS_SEQU, PA_STATE, PA_UPD_DATE, PA_OLD_MRN, PA_EST_ARR_TIME, PA_STREET_2, PA_CITY, PA_SENT_FOR_DATE_TIME, PA_PORTER_LEFT_DATE_TIME, PA_PORTER_SEQU, PA_ORDER_CC, PA_CONS_CC_SEQU, PA_TH_CC_SEQU, PA_SESSION_NO_CC, PA_EST_START_CC, PA_S1_CC_SEQU, PA_SU_CC_SEQU, PA_AN_CC_SEQU, PA_COMBINED_FLAG, PA_FIXED_TIME_CC, PA_PICKLIST_PRINTED, PA_PICKLIST_MERGED, PA_BOOKING_RECEIVED_DATE_TIME, PA_INITIATED_BY_NAME, PA_REQ_S1_SEQU, PA_HSC_COMMENTS, PA_SS_SEQU, PA_CC_SS_SEQU, PA_ORDER_REASON_SEQU, PA_PRINTED, PA_PR_SEQU, PA_DELAY_COMMENTS, PA_MIDDLE_NAME, PA_IMS_CONFIRM_SENT, PA_SEEN_ANAES_SEQU, PA_DIAG_TEST_COMP_SEQU, PA_FASTED_SEQU, PA_BREACH_REAS_SEQU, PA_IMS_BKG_REF, SID_SPID, D_ACTION, INSERT_DATE_TIME, MACHINENAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME, COUNTER FROM APATS WHERE COUNTER > (SELECT SQ_COUNT FROM F_SEQU WHERE SQ_TABLE='APATS'); BEGIN OPEN C_APATS; FETCH C_APATS INTO PA_SEQN_FILLER_V, PA_SEQU_V, PA_MRN_V, PA_OPER_DATE_V, PA_TH_CODE_V, PA_CUSTODIAN_V, PA_SESS_TYPE_V, PA_LAST_NAME_V, PA_FIRST_NAME_V, PA_DOB_V, PA_PIN_SEQU_V, PA_TH_SEQU_V, PA_CONS_SEQU_V, PA_SUCODE_V, PA_BIOHAZARD_V, PA_BLOOD_V, PA_EQUIPMENT_V, PA_AGEYR_V, PA_AGEMNTH_V, PA_WARD_V, PA_TIME_V, PA_EPISODE_NUM_V, PA_OPCOMM_V, PA_ORDER_V, PA_TRANSF_V, PA_STAT_V, PA_CLCODE_V, PA_ANA_SEQU_V, PA_ANAES_TYPE_V, PA_SURG_COMM_V, PA_SURG_SPEC_V, PA_BED_V, PA_WLSEQ_V, PA_SESSION_NO_V, PA_TOTAL_MINS_V, PA_CHANGE_OVER_V, PA_GENDER_V, PA_SURGEON_V, PA_FILLER_01_V, PA_REQ_TIME_V, PA_TIME_READY_V, PA_VERIFIED_V, PA_UNPLANNED_V, PA_TYPE_V, PA_TH_SESS_SORT_V, PA_EST_START_V, PA_SU_SEQU_V, PA_CL_SEQU_V, PA_WA_SEQU_V, PA_S1_SEQU_V, PA_LOG_DATE_V, PA_LOG_DETAILS_FILLER_V, PA_WL_SEQU_V, PA_AN_SEQU_V, PA_STREET_V, PA_SUBURB_V, PA_POSTCODE_V, PA_HOME_PHONE_V, PA_BUS_PHONE_V, PA_AGDAYS_V, PA_TA_SEQU_V, PA_CHANGE_FLAG_V, PA_ASA_SEQU_V, PA_UNUSED_4_V, PA_UNUSED_5_V, PA_UNUSED_6_V, PA_UNUSED_7_V, PA_UNUSED_8_V, PA_PATHOLOGY_V, PA_XRAY_V, PA_PATH_COMMENT_V, PA_XRAY_COMMENT_V, PA_BLOOD_COMMENT_V, PA_BLOOD_NO_OF_UNITS_V, PA_WARD_ADV_DATE_TIME_V, PA_FILLER_02_V, PA_TRANS_V, PA_NURSE_REQ_SEQU_V, PA_NURSE_REC_NOTIF_V, PA_OXYGEN_V, PA_IV_V, PA_WA_TO_SEQU_V, PA_SU2_SEQU_V, PA_SU3_SEQU_V, PA_AN2_SEQU_V, PA_AN3_SEQU_V, PA_SC_NUR_SEQU_V, PA_AN_NUR_SEQU_V, PA_IN_NUR_SEQU_V, PA_TECH1_SEQU_V, PA_TECH2_SEQU_V, PA_UNUSED_1_V, PA_TIME_THEATRE_V, PA_CA_SEQU_V, PA_CLP_SEQU_V, PA_BOOK_SU_SEQU_V, PA_ADDITIONAL_V, PA_CAM_SEQU_V, PA_CAM_COMMENT_V, PA_ADMIT_SU_SEQU_V, PA_FLAG_V, PA_EXT_NO_FIELD_V, PA_FLAG_2_V, PA_ADMIT_DATE_TIME_V, PA_ACC_INDICATOR_V, PA_ACC_NUMBER_V, PA_ACC_SEQU_V, PA_MAJOR_MINOR_V, PA_FIXED_TIME_V, PA_LA_SEQU_V, PA_LMO_SEQU_V, PA_REF_SEQU_V, PA_MS_SEQU_V, PA_STATE_V, PA_UPD_DATE_V, PA_OLD_MRN_V, PA_EST_ARR_TIME_V, PA_STREET_2_V, PA_CITY_V, PA_SENT_FOR_DATE_TIME_V, PA_PORTER_LEFT_DATE_TIME_V, PA_PORTER_SEQU_V, PA_ORDER_CC_V, PA_CONS_CC_SEQU_V, PA_TH_CC_SEQU_V, PA_SESSION_NO_CC_V, PA_EST_START_CC_V, PA_S1_CC_SEQU_V, PA_SU_CC_SEQU_V, PA_AN_CC_SEQU_V, PA_COMBINED_FLAG_V, PA_FIXED_TIME_CC_V, PA_PICKLIST_PRINTED_V, PA_PICKLIST_MERGED_V, BK_RECD_DT_TM_V, PA_INITIATED_BY_NAME_V, PA_REQ_S1_SEQU_V, PA_HSC_COMMENTS_V, PA_SS_SEQU_V, PA_CC_SS_SEQU_V, PA_ORDER_REASON_SEQU_V, PA_PRINTED_V, PA_PR_SEQU_V, PA_DELAY_COMMENTS_V, PA_MIDDLE_NAME_V, PA_IMS_CONFIRM_SENT_V, PA_SEEN_ANAES_SEQU_V, PA_DIAG_TEST_COMP_SEQU_V, PA_FASTED_SEQU_V, PA_BREACH_REAS_SEQU_V, PA_IMS_BKG_REF_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; WHILE C_APATS%FOUND LOOP LV_AUDIT_TEXT:=''; LV_AUDIT_TABLE:='FPATS'; LV_AUDIT_PRIMARY_KEY_NAME:='FPATS'||'/'||TO_CHAR(PA_SEQU_V); LV_AUDT_REC_SEQU:=TO_CHAR(PA_SEQU_V); BEGIN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE((LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND LV_INSERT_DATE_TIME<=AUD_DATE_TIME_EXIT) OR(LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND AUD_DATE_TIME_EXIT IS NULL)) AND AUD_SPID=LV_SID_SPID; EXCEPTION WHEN NO_DATA_FOUND THEN LV_APP_USER:=''; WHEN TOO_MANY_ROWS THEN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE AUD_SPID=LV_SID_SPID AND ROWNUM=1; END; BEGIN SELECT MAX(COUNTER) INTO LV_COUNTER_P FROM APATS WHERE PA_SEQU=PA_SEQU_V AND COUNTERPA_SEQN_FILLER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SEQN_FILLER_N||CHR(164)||TO_CHAR(PA_SEQN_FILLER_V); END IF; IF PA_SEQU_P<>PA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SEQU_N||CHR(164)||TO_CHAR(PA_SEQU_V); END IF; IF (PA_MRN_P IS NOT NULL AND PA_MRN_V IS NULL) OR (PA_MRN_P IS NULL AND PA_MRN_V IS NOT NULL) OR (PA_MRN_P<>PA_MRN_V) THEN IF PA_MRN_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_MRN_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_MRN_N||CHR(164)||PA_MRN_V; END IF; END IF; IF (PA_OPER_DATE_P IS NOT NULL AND PA_OPER_DATE_V IS NULL) OR (PA_OPER_DATE_P IS NULL AND PA_OPER_DATE_V IS NOT NULL) OR (PA_OPER_DATE_P<>PA_OPER_DATE_V) THEN IF PA_OPER_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_OPER_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_OPER_DATE_N||CHR(164)||TO_CHAR(PA_OPER_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (PA_TH_CODE_P IS NOT NULL AND PA_TH_CODE_V IS NULL) OR (PA_TH_CODE_P IS NULL AND PA_TH_CODE_V IS NOT NULL) OR (PA_TH_CODE_P<>PA_TH_CODE_V) THEN IF PA_TH_CODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TH_CODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TH_CODE_N||CHR(164)||PA_TH_CODE_V; END IF; END IF; IF (PA_CUSTODIAN_P IS NOT NULL AND PA_CUSTODIAN_V IS NULL) OR (PA_CUSTODIAN_P IS NULL AND PA_CUSTODIAN_V IS NOT NULL) OR (PA_CUSTODIAN_P<>PA_CUSTODIAN_V) THEN IF PA_CUSTODIAN_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CUSTODIAN_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CUSTODIAN_N||CHR(164)||PA_CUSTODIAN_V; END IF; END IF; IF (PA_SESS_TYPE_P IS NOT NULL AND PA_SESS_TYPE_V IS NULL) OR (PA_SESS_TYPE_P IS NULL AND PA_SESS_TYPE_V IS NOT NULL) OR (PA_SESS_TYPE_P<>PA_SESS_TYPE_V) THEN IF PA_SESS_TYPE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SESS_TYPE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SESS_TYPE_N||CHR(164)||PA_SESS_TYPE_V; END IF; END IF; IF (PA_LAST_NAME_P IS NOT NULL AND PA_LAST_NAME_V IS NULL) OR (PA_LAST_NAME_P IS NULL AND PA_LAST_NAME_V IS NOT NULL) OR (PA_LAST_NAME_P<>PA_LAST_NAME_V) THEN IF PA_LAST_NAME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_LAST_NAME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_LAST_NAME_N||CHR(164)||PA_LAST_NAME_V; END IF; END IF; IF (PA_FIRST_NAME_P IS NOT NULL AND PA_FIRST_NAME_V IS NULL) OR (PA_FIRST_NAME_P IS NULL AND PA_FIRST_NAME_V IS NOT NULL) OR (PA_FIRST_NAME_P<>PA_FIRST_NAME_V) THEN IF PA_FIRST_NAME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_FIRST_NAME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_FIRST_NAME_N||CHR(164)||PA_FIRST_NAME_V; END IF; END IF; IF (PA_DOB_P IS NOT NULL AND PA_DOB_V IS NULL) OR (PA_DOB_P IS NULL AND PA_DOB_V IS NOT NULL) OR (PA_DOB_P<>PA_DOB_V) THEN IF PA_DOB_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_DOB_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_DOB_N||CHR(164)||TO_CHAR(PA_DOB_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF PA_PIN_SEQU_P<>PA_PIN_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_PIN_SEQU_N||CHR(164)||TO_CHAR(PA_PIN_SEQU_V); END IF; IF PA_TH_SEQU_P<>PA_TH_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TH_SEQU_N||CHR(164)||TO_CHAR(PA_TH_SEQU_V); END IF; IF PA_CONS_SEQU_P<>PA_CONS_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CONS_SEQU_N||CHR(164)||TO_CHAR(PA_CONS_SEQU_V); END IF; IF (PA_SUCODE_P IS NOT NULL AND PA_SUCODE_V IS NULL) OR (PA_SUCODE_P IS NULL AND PA_SUCODE_V IS NOT NULL) OR (PA_SUCODE_P<>PA_SUCODE_V) THEN IF PA_SUCODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SUCODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SUCODE_N||CHR(164)||PA_SUCODE_V; END IF; END IF; IF PA_BIOHAZARD_P<>PA_BIOHAZARD_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_BIOHAZARD_N||CHR(164)||TO_CHAR(PA_BIOHAZARD_V); END IF; IF PA_BLOOD_P<>PA_BLOOD_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_BLOOD_N||CHR(164)||TO_CHAR(PA_BLOOD_V); END IF; IF (PA_EQUIPMENT_P IS NOT NULL AND PA_EQUIPMENT_V IS NULL) OR (PA_EQUIPMENT_P IS NULL AND PA_EQUIPMENT_V IS NOT NULL) OR (PA_EQUIPMENT_P<>PA_EQUIPMENT_V) THEN IF PA_EQUIPMENT_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_EQUIPMENT_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_EQUIPMENT_N||CHR(164)||PA_EQUIPMENT_V; END IF; END IF; IF PA_AGEYR_P<>PA_AGEYR_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_AGEYR_N||CHR(164)||TO_CHAR(PA_AGEYR_V); END IF; IF PA_AGEMNTH_P<>PA_AGEMNTH_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_AGEMNTH_N||CHR(164)||TO_CHAR(PA_AGEMNTH_V); END IF; IF (PA_WARD_P IS NOT NULL AND PA_WARD_V IS NULL) OR (PA_WARD_P IS NULL AND PA_WARD_V IS NOT NULL) OR (PA_WARD_P<>PA_WARD_V) THEN IF PA_WARD_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_WARD_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_WARD_N||CHR(164)||PA_WARD_V; END IF; END IF; IF (PA_TIME_P IS NOT NULL AND PA_TIME_V IS NULL) OR (PA_TIME_P IS NULL AND PA_TIME_V IS NOT NULL) OR (PA_TIME_P<>PA_TIME_V) THEN IF PA_TIME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TIME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TIME_N||CHR(164)||TO_CHAR(PA_TIME_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (PA_EPISODE_NUM_P IS NOT NULL AND PA_EPISODE_NUM_V IS NULL) OR (PA_EPISODE_NUM_P IS NULL AND PA_EPISODE_NUM_V IS NOT NULL) OR (PA_EPISODE_NUM_P<>PA_EPISODE_NUM_V) THEN IF PA_EPISODE_NUM_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_EPISODE_NUM_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_EPISODE_NUM_N||CHR(164)||PA_EPISODE_NUM_V; END IF; END IF; IF (PA_OPCOMM_P IS NOT NULL AND PA_OPCOMM_V IS NULL) OR (PA_OPCOMM_P IS NULL AND PA_OPCOMM_V IS NOT NULL) OR (PA_OPCOMM_P<>PA_OPCOMM_V) THEN IF PA_OPCOMM_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_OPCOMM_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_OPCOMM_N||CHR(164)||PA_OPCOMM_V; END IF; END IF; IF PA_ORDER_P<>PA_ORDER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ORDER_N||CHR(164)||TO_CHAR(PA_ORDER_V); END IF; IF PA_TRANSF_P<>PA_TRANSF_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TRANSF_N||CHR(164)||TO_CHAR(PA_TRANSF_V); END IF; IF (PA_STAT_P IS NOT NULL AND PA_STAT_V IS NULL) OR (PA_STAT_P IS NULL AND PA_STAT_V IS NOT NULL) OR (PA_STAT_P<>PA_STAT_V) THEN IF PA_STAT_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_STAT_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_STAT_N||CHR(164)||PA_STAT_V; END IF; END IF; IF (PA_CLCODE_P IS NOT NULL AND PA_CLCODE_V IS NULL) OR (PA_CLCODE_P IS NULL AND PA_CLCODE_V IS NOT NULL) OR (PA_CLCODE_P<>PA_CLCODE_V) THEN IF PA_CLCODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CLCODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CLCODE_N||CHR(164)||PA_CLCODE_V; END IF; END IF; IF PA_ANA_SEQU_P<>PA_ANA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ANA_SEQU_N||CHR(164)||TO_CHAR(PA_ANA_SEQU_V); END IF; IF (PA_ANAES_TYPE_P IS NOT NULL AND PA_ANAES_TYPE_V IS NULL) OR (PA_ANAES_TYPE_P IS NULL AND PA_ANAES_TYPE_V IS NOT NULL) OR (PA_ANAES_TYPE_P<>PA_ANAES_TYPE_V) THEN IF PA_ANAES_TYPE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ANAES_TYPE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ANAES_TYPE_N||CHR(164)||PA_ANAES_TYPE_V; END IF; END IF; IF (PA_SURG_COMM_P IS NOT NULL AND PA_SURG_COMM_V IS NULL) OR (PA_SURG_COMM_P IS NULL AND PA_SURG_COMM_V IS NOT NULL) OR (PA_SURG_COMM_P<>PA_SURG_COMM_V) THEN IF PA_SURG_COMM_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SURG_COMM_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SURG_COMM_N||CHR(164)||PA_SURG_COMM_V; END IF; END IF; IF PA_SURG_SPEC_P<>PA_SURG_SPEC_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SURG_SPEC_N||CHR(164)||TO_CHAR(PA_SURG_SPEC_V); END IF; IF (PA_BED_P IS NOT NULL AND PA_BED_V IS NULL) OR (PA_BED_P IS NULL AND PA_BED_V IS NOT NULL) OR (PA_BED_P<>PA_BED_V) THEN IF PA_BED_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_BED_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_BED_N||CHR(164)||PA_BED_V; END IF; END IF; IF PA_WLSEQ_P<>PA_WLSEQ_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_WLSEQ_N||CHR(164)||TO_CHAR(PA_WLSEQ_V); END IF; IF PA_SESSION_NO_P<>PA_SESSION_NO_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SESSION_NO_N||CHR(164)||TO_CHAR(PA_SESSION_NO_V); END IF; IF PA_TOTAL_MINS_P<>PA_TOTAL_MINS_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TOTAL_MINS_N||CHR(164)||TO_CHAR(PA_TOTAL_MINS_V); END IF; IF PA_CHANGE_OVER_P<>PA_CHANGE_OVER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CHANGE_OVER_N||CHR(164)||TO_CHAR(PA_CHANGE_OVER_V); END IF; IF (PA_GENDER_P IS NOT NULL AND PA_GENDER_V IS NULL) OR (PA_GENDER_P IS NULL AND PA_GENDER_V IS NOT NULL) OR (PA_GENDER_P<>PA_GENDER_V) THEN IF PA_GENDER_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_GENDER_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_GENDER_N||CHR(164)||PA_GENDER_V; END IF; END IF; IF (PA_SURGEON_P IS NOT NULL AND PA_SURGEON_V IS NULL) OR (PA_SURGEON_P IS NULL AND PA_SURGEON_V IS NOT NULL) OR (PA_SURGEON_P<>PA_SURGEON_V) THEN IF PA_SURGEON_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SURGEON_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SURGEON_N||CHR(164)||PA_SURGEON_V; END IF; END IF; IF (PA_FILLER_01_P IS NOT NULL AND PA_FILLER_01_V IS NULL) OR (PA_FILLER_01_P IS NULL AND PA_FILLER_01_V IS NOT NULL) OR (PA_FILLER_01_P<>PA_FILLER_01_V) THEN IF PA_FILLER_01_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_FILLER_01_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_FILLER_01_N||CHR(164)||TO_CHAR(PA_FILLER_01_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (PA_REQ_TIME_P IS NOT NULL AND PA_REQ_TIME_V IS NULL) OR (PA_REQ_TIME_P IS NULL AND PA_REQ_TIME_V IS NOT NULL) OR (PA_REQ_TIME_P<>PA_REQ_TIME_V) THEN IF PA_REQ_TIME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_REQ_TIME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_REQ_TIME_N||CHR(164)||TO_CHAR(PA_REQ_TIME_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (PA_TIME_READY_P IS NOT NULL AND PA_TIME_READY_V IS NULL) OR (PA_TIME_READY_P IS NULL AND PA_TIME_READY_V IS NOT NULL) OR (PA_TIME_READY_P<>PA_TIME_READY_V) THEN IF PA_TIME_READY_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TIME_READY_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TIME_READY_N||CHR(164)||TO_CHAR(PA_TIME_READY_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF PA_VERIFIED_P<>PA_VERIFIED_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_VERIFIED_N||CHR(164)||TO_CHAR(PA_VERIFIED_V); END IF; IF PA_UNPLANNED_P<>PA_UNPLANNED_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNPLANNED_N||CHR(164)||TO_CHAR(PA_UNPLANNED_V); END IF; IF (PA_TYPE_P IS NOT NULL AND PA_TYPE_V IS NULL) OR (PA_TYPE_P IS NULL AND PA_TYPE_V IS NOT NULL) OR (PA_TYPE_P<>PA_TYPE_V) THEN IF PA_TYPE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TYPE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TYPE_N||CHR(164)||PA_TYPE_V; END IF; END IF; IF (PA_TH_SESS_SORT_P IS NOT NULL AND PA_TH_SESS_SORT_V IS NULL) OR (PA_TH_SESS_SORT_P IS NULL AND PA_TH_SESS_SORT_V IS NOT NULL) OR (PA_TH_SESS_SORT_P<>PA_TH_SESS_SORT_V) THEN IF PA_TH_SESS_SORT_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TH_SESS_SORT_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TH_SESS_SORT_N||CHR(164)||PA_TH_SESS_SORT_V; END IF; END IF; IF (PA_EST_START_P IS NOT NULL AND PA_EST_START_V IS NULL) OR (PA_EST_START_P IS NULL AND PA_EST_START_V IS NOT NULL) OR (PA_EST_START_P<>PA_EST_START_V) THEN IF PA_EST_START_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_EST_START_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_EST_START_N||CHR(164)||TO_CHAR(PA_EST_START_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF PA_SU_SEQU_P<>PA_SU_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SU_SEQU_N||CHR(164)||TO_CHAR(PA_SU_SEQU_V); END IF; IF PA_CL_SEQU_P<>PA_CL_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CL_SEQU_N||CHR(164)||TO_CHAR(PA_CL_SEQU_V); END IF; IF PA_WA_SEQU_P<>PA_WA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_WA_SEQU_N||CHR(164)||TO_CHAR(PA_WA_SEQU_V); END IF; IF PA_S1_SEQU_P<>PA_S1_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_S1_SEQU_N||CHR(164)||TO_CHAR(PA_S1_SEQU_V); END IF; IF (PA_LOG_DATE_P IS NOT NULL AND PA_LOG_DATE_V IS NULL) OR (PA_LOG_DATE_P IS NULL AND PA_LOG_DATE_V IS NOT NULL) OR (PA_LOG_DATE_P<>PA_LOG_DATE_V) THEN IF PA_LOG_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_LOG_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_LOG_DATE_N||CHR(164)||TO_CHAR(PA_LOG_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (PA_LOG_DETAILS_FILLER_P IS NOT NULL AND PA_LOG_DETAILS_FILLER_V IS NULL) OR (PA_LOG_DETAILS_FILLER_P IS NULL AND PA_LOG_DETAILS_FILLER_V IS NOT NULL) OR (PA_LOG_DETAILS_FILLER_P<>PA_LOG_DETAILS_FILLER_V) THEN IF PA_LOG_DETAILS_FILLER_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_LOG_DETAILS_FILLER_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_LOG_DETAILS_FILLER_N||CHR(164)||PA_LOG_DETAILS_FILLER_V; END IF; END IF; IF PA_WL_SEQU_P<>PA_WL_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_WL_SEQU_N||CHR(164)||TO_CHAR(PA_WL_SEQU_V); END IF; IF PA_AN_SEQU_P<>PA_AN_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_AN_SEQU_N||CHR(164)||TO_CHAR(PA_AN_SEQU_V); END IF; IF (PA_STREET_P IS NOT NULL AND PA_STREET_V IS NULL) OR (PA_STREET_P IS NULL AND PA_STREET_V IS NOT NULL) OR (PA_STREET_P<>PA_STREET_V) THEN IF PA_STREET_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_STREET_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_STREET_N||CHR(164)||PA_STREET_V; END IF; END IF; IF (PA_SUBURB_P IS NOT NULL AND PA_SUBURB_V IS NULL) OR (PA_SUBURB_P IS NULL AND PA_SUBURB_V IS NOT NULL) OR (PA_SUBURB_P<>PA_SUBURB_V) THEN IF PA_SUBURB_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SUBURB_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SUBURB_N||CHR(164)||PA_SUBURB_V; END IF; END IF; IF (PA_POSTCODE_P IS NOT NULL AND PA_POSTCODE_V IS NULL) OR (PA_POSTCODE_P IS NULL AND PA_POSTCODE_V IS NOT NULL) OR (PA_POSTCODE_P<>PA_POSTCODE_V) THEN IF PA_POSTCODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_POSTCODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_POSTCODE_N||CHR(164)||PA_POSTCODE_V; END IF; END IF; IF (PA_HOME_PHONE_P IS NOT NULL AND PA_HOME_PHONE_V IS NULL) OR (PA_HOME_PHONE_P IS NULL AND PA_HOME_PHONE_V IS NOT NULL) OR (PA_HOME_PHONE_P<>PA_HOME_PHONE_V) THEN IF PA_HOME_PHONE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_HOME_PHONE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_HOME_PHONE_N||CHR(164)||PA_HOME_PHONE_V; END IF; END IF; IF (PA_BUS_PHONE_P IS NOT NULL AND PA_BUS_PHONE_V IS NULL) OR (PA_BUS_PHONE_P IS NULL AND PA_BUS_PHONE_V IS NOT NULL) OR (PA_BUS_PHONE_P<>PA_BUS_PHONE_V) THEN IF PA_BUS_PHONE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_BUS_PHONE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_BUS_PHONE_N||CHR(164)||PA_BUS_PHONE_V; END IF; END IF; IF PA_AGDAYS_P<>PA_AGDAYS_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_AGDAYS_N||CHR(164)||TO_CHAR(PA_AGDAYS_V); END IF; IF PA_TA_SEQU_P<>PA_TA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TA_SEQU_N||CHR(164)||TO_CHAR(PA_TA_SEQU_V); END IF; IF PA_CHANGE_FLAG_P<>PA_CHANGE_FLAG_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CHANGE_FLAG_N||CHR(164)||TO_CHAR(PA_CHANGE_FLAG_V); END IF; IF PA_ASA_SEQU_P<>PA_ASA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ASA_SEQU_N||CHR(164)||TO_CHAR(PA_ASA_SEQU_V); END IF; IF (PA_UNUSED_4_P IS NOT NULL AND PA_UNUSED_4_V IS NULL) OR (PA_UNUSED_4_P IS NULL AND PA_UNUSED_4_V IS NOT NULL) OR (PA_UNUSED_4_P<>PA_UNUSED_4_V) THEN IF PA_UNUSED_4_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNUSED_4_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNUSED_4_N||CHR(164)||PA_UNUSED_4_V; END IF; END IF; IF (PA_UNUSED_5_P IS NOT NULL AND PA_UNUSED_5_V IS NULL) OR (PA_UNUSED_5_P IS NULL AND PA_UNUSED_5_V IS NOT NULL) OR (PA_UNUSED_5_P<>PA_UNUSED_5_V) THEN IF PA_UNUSED_5_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNUSED_5_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNUSED_5_N||CHR(164)||PA_UNUSED_5_V; END IF; END IF; IF (PA_UNUSED_6_P IS NOT NULL AND PA_UNUSED_6_V IS NULL) OR (PA_UNUSED_6_P IS NULL AND PA_UNUSED_6_V IS NOT NULL) OR (PA_UNUSED_6_P<>PA_UNUSED_6_V) THEN IF PA_UNUSED_6_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNUSED_6_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNUSED_6_N||CHR(164)||PA_UNUSED_6_V; END IF; END IF; IF (PA_UNUSED_7_P IS NOT NULL AND PA_UNUSED_7_V IS NULL) OR (PA_UNUSED_7_P IS NULL AND PA_UNUSED_7_V IS NOT NULL) OR (PA_UNUSED_7_P<>PA_UNUSED_7_V) THEN IF PA_UNUSED_7_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNUSED_7_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNUSED_7_N||CHR(164)||PA_UNUSED_7_V; END IF; END IF; IF (PA_UNUSED_8_P IS NOT NULL AND PA_UNUSED_8_V IS NULL) OR (PA_UNUSED_8_P IS NULL AND PA_UNUSED_8_V IS NOT NULL) OR (PA_UNUSED_8_P<>PA_UNUSED_8_V) THEN IF PA_UNUSED_8_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNUSED_8_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNUSED_8_N||CHR(164)||PA_UNUSED_8_V; END IF; END IF; IF PA_PATHOLOGY_P<>PA_PATHOLOGY_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_PATHOLOGY_N||CHR(164)||TO_CHAR(PA_PATHOLOGY_V); END IF; IF PA_XRAY_P<>PA_XRAY_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_XRAY_N||CHR(164)||TO_CHAR(PA_XRAY_V); END IF; IF (PA_PATH_COMMENT_P IS NOT NULL AND PA_PATH_COMMENT_V IS NULL) OR (PA_PATH_COMMENT_P IS NULL AND PA_PATH_COMMENT_V IS NOT NULL) OR (PA_PATH_COMMENT_P<>PA_PATH_COMMENT_V) THEN IF PA_PATH_COMMENT_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_PATH_COMMENT_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_PATH_COMMENT_N||CHR(164)||PA_PATH_COMMENT_V; END IF; END IF; IF (PA_XRAY_COMMENT_P IS NOT NULL AND PA_XRAY_COMMENT_V IS NULL) OR (PA_XRAY_COMMENT_P IS NULL AND PA_XRAY_COMMENT_V IS NOT NULL) OR (PA_XRAY_COMMENT_P<>PA_XRAY_COMMENT_V) THEN IF PA_XRAY_COMMENT_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_XRAY_COMMENT_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_XRAY_COMMENT_N||CHR(164)||PA_XRAY_COMMENT_V; END IF; END IF; IF (PA_BLOOD_COMMENT_P IS NOT NULL AND PA_BLOOD_COMMENT_V IS NULL) OR (PA_BLOOD_COMMENT_P IS NULL AND PA_BLOOD_COMMENT_V IS NOT NULL) OR (PA_BLOOD_COMMENT_P<>PA_BLOOD_COMMENT_V) THEN IF PA_BLOOD_COMMENT_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_BLOOD_COMMENT_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_BLOOD_COMMENT_N||CHR(164)||PA_BLOOD_COMMENT_V; END IF; END IF; IF PA_BLOOD_NO_OF_UNITS_P<>PA_BLOOD_NO_OF_UNITS_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_BLOOD_NO_OF_UNITS_N||CHR(164)||TO_CHAR(PA_BLOOD_NO_OF_UNITS_V); END IF; IF (PA_WARD_ADV_DATE_TIME_P IS NOT NULL AND PA_WARD_ADV_DATE_TIME_V IS NULL) OR (PA_WARD_ADV_DATE_TIME_P IS NULL AND PA_WARD_ADV_DATE_TIME_V IS NOT NULL) OR (PA_WARD_ADV_DATE_TIME_P<>PA_WARD_ADV_DATE_TIME_V) THEN IF PA_WARD_ADV_DATE_TIME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_WARD_ADV_DATE_TIME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_WARD_ADV_DATE_TIME_N||CHR(164)||TO_CHAR(PA_WARD_ADV_DATE_TIME_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (PA_FILLER_02_P IS NOT NULL AND PA_FILLER_02_V IS NULL) OR (PA_FILLER_02_P IS NULL AND PA_FILLER_02_V IS NOT NULL) OR (PA_FILLER_02_P<>PA_FILLER_02_V) THEN IF PA_FILLER_02_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_FILLER_02_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_FILLER_02_N||CHR(164)||TO_CHAR(PA_FILLER_02_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF PA_TRANS_P<>PA_TRANS_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TRANS_N||CHR(164)||TO_CHAR(PA_TRANS_V); END IF; IF PA_NURSE_REQ_SEQU_P<>PA_NURSE_REQ_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_NURSE_REQ_SEQU_N||CHR(164)||TO_CHAR(PA_NURSE_REQ_SEQU_V); END IF; IF (PA_NURSE_REC_NOTIF_P IS NOT NULL AND PA_NURSE_REC_NOTIF_V IS NULL) OR (PA_NURSE_REC_NOTIF_P IS NULL AND PA_NURSE_REC_NOTIF_V IS NOT NULL) OR (PA_NURSE_REC_NOTIF_P<>PA_NURSE_REC_NOTIF_V) THEN IF PA_NURSE_REC_NOTIF_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_NURSE_REC_NOTIF_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_NURSE_REC_NOTIF_N||CHR(164)||PA_NURSE_REC_NOTIF_V; END IF; END IF; IF PA_OXYGEN_P<>PA_OXYGEN_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_OXYGEN_N||CHR(164)||TO_CHAR(PA_OXYGEN_V); END IF; IF PA_IV_P<>PA_IV_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_IV_N||CHR(164)||TO_CHAR(PA_IV_V); END IF; IF PA_WA_TO_SEQU_P<>PA_WA_TO_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_WA_TO_SEQU_N||CHR(164)||TO_CHAR(PA_WA_TO_SEQU_V); END IF; IF PA_SU2_SEQU_P<>PA_SU2_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SU2_SEQU_N||CHR(164)||TO_CHAR(PA_SU2_SEQU_V); END IF; IF PA_SU3_SEQU_P<>PA_SU3_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SU3_SEQU_N||CHR(164)||TO_CHAR(PA_SU3_SEQU_V); END IF; IF PA_AN2_SEQU_P<>PA_AN2_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_AN2_SEQU_N||CHR(164)||TO_CHAR(PA_AN2_SEQU_V); END IF; IF PA_AN3_SEQU_P<>PA_AN3_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_AN3_SEQU_N||CHR(164)||TO_CHAR(PA_AN3_SEQU_V); END IF; IF PA_SC_NUR_SEQU_P<>PA_SC_NUR_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SC_NUR_SEQU_N||CHR(164)||TO_CHAR(PA_SC_NUR_SEQU_V); END IF; IF PA_AN_NUR_SEQU_P<>PA_AN_NUR_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_AN_NUR_SEQU_N||CHR(164)||TO_CHAR(PA_AN_NUR_SEQU_V); END IF; IF PA_IN_NUR_SEQU_P<>PA_IN_NUR_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_IN_NUR_SEQU_N||CHR(164)||TO_CHAR(PA_IN_NUR_SEQU_V); END IF; IF PA_TECH1_SEQU_P<>PA_TECH1_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TECH1_SEQU_N||CHR(164)||TO_CHAR(PA_TECH1_SEQU_V); END IF; IF PA_TECH2_SEQU_P<>PA_TECH2_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TECH2_SEQU_N||CHR(164)||TO_CHAR(PA_TECH2_SEQU_V); END IF; IF (PA_UNUSED_1_P IS NOT NULL AND PA_UNUSED_1_V IS NULL) OR (PA_UNUSED_1_P IS NULL AND PA_UNUSED_1_V IS NOT NULL) OR (PA_UNUSED_1_P<>PA_UNUSED_1_V) THEN IF PA_UNUSED_1_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNUSED_1_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UNUSED_1_N||CHR(164)||PA_UNUSED_1_V; END IF; END IF; IF (PA_TIME_THEATRE_P IS NOT NULL AND PA_TIME_THEATRE_V IS NULL) OR (PA_TIME_THEATRE_P IS NULL AND PA_TIME_THEATRE_V IS NOT NULL) OR (PA_TIME_THEATRE_P<>PA_TIME_THEATRE_V) THEN IF PA_TIME_THEATRE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TIME_THEATRE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TIME_THEATRE_N||CHR(164)||TO_CHAR(PA_TIME_THEATRE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF PA_CA_SEQU_P<>PA_CA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CA_SEQU_N||CHR(164)||TO_CHAR(PA_CA_SEQU_V); END IF; IF PA_CLP_SEQU_P<>PA_CLP_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CLP_SEQU_N||CHR(164)||TO_CHAR(PA_CLP_SEQU_V); END IF; IF PA_BOOK_SU_SEQU_P<>PA_BOOK_SU_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_BOOK_SU_SEQU_N||CHR(164)||TO_CHAR(PA_BOOK_SU_SEQU_V); END IF; IF PA_ADDITIONAL_P<>PA_ADDITIONAL_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ADDITIONAL_N||CHR(164)||TO_CHAR(PA_ADDITIONAL_V); END IF; IF PA_CAM_SEQU_P<>PA_CAM_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CAM_SEQU_N||CHR(164)||TO_CHAR(PA_CAM_SEQU_V); END IF; IF (PA_CAM_COMMENT_P IS NOT NULL AND PA_CAM_COMMENT_V IS NULL) OR (PA_CAM_COMMENT_P IS NULL AND PA_CAM_COMMENT_V IS NOT NULL) OR (PA_CAM_COMMENT_P<>PA_CAM_COMMENT_V) THEN IF PA_CAM_COMMENT_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CAM_COMMENT_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CAM_COMMENT_N||CHR(164)||PA_CAM_COMMENT_V; END IF; END IF; IF PA_ADMIT_SU_SEQU_P<>PA_ADMIT_SU_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ADMIT_SU_SEQU_N||CHR(164)||TO_CHAR(PA_ADMIT_SU_SEQU_V); END IF; IF PA_FLAG_P<>PA_FLAG_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_FLAG_N||CHR(164)||TO_CHAR(PA_FLAG_V); END IF; IF (PA_EXT_NO_FIELD_P IS NOT NULL AND PA_EXT_NO_FIELD_V IS NULL) OR (PA_EXT_NO_FIELD_P IS NULL AND PA_EXT_NO_FIELD_V IS NOT NULL) OR (PA_EXT_NO_FIELD_P<>PA_EXT_NO_FIELD_V) THEN IF PA_EXT_NO_FIELD_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_EXT_NO_FIELD_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_EXT_NO_FIELD_N||CHR(164)||PA_EXT_NO_FIELD_V; END IF; END IF; IF PA_FLAG_2_P<>PA_FLAG_2_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_FLAG_2_N||CHR(164)||TO_CHAR(PA_FLAG_2_V); END IF; IF (PA_ADMIT_DATE_TIME_P IS NOT NULL AND PA_ADMIT_DATE_TIME_V IS NULL) OR (PA_ADMIT_DATE_TIME_P IS NULL AND PA_ADMIT_DATE_TIME_V IS NOT NULL) OR (PA_ADMIT_DATE_TIME_P<>PA_ADMIT_DATE_TIME_V) THEN IF PA_ADMIT_DATE_TIME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ADMIT_DATE_TIME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ADMIT_DATE_TIME_N||CHR(164)||TO_CHAR(PA_ADMIT_DATE_TIME_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF PA_ACC_INDICATOR_P<>PA_ACC_INDICATOR_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ACC_INDICATOR_N||CHR(164)||TO_CHAR(PA_ACC_INDICATOR_V); END IF; IF (PA_ACC_NUMBER_P IS NOT NULL AND PA_ACC_NUMBER_V IS NULL) OR (PA_ACC_NUMBER_P IS NULL AND PA_ACC_NUMBER_V IS NOT NULL) OR (PA_ACC_NUMBER_P<>PA_ACC_NUMBER_V) THEN IF PA_ACC_NUMBER_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ACC_NUMBER_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ACC_NUMBER_N||CHR(164)||PA_ACC_NUMBER_V; END IF; END IF; IF PA_ACC_SEQU_P<>PA_ACC_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ACC_SEQU_N||CHR(164)||TO_CHAR(PA_ACC_SEQU_V); END IF; IF PA_MAJOR_MINOR_P<>PA_MAJOR_MINOR_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_MAJOR_MINOR_N||CHR(164)||TO_CHAR(PA_MAJOR_MINOR_V); END IF; IF PA_FIXED_TIME_P<>PA_FIXED_TIME_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_FIXED_TIME_N||CHR(164)||TO_CHAR(PA_FIXED_TIME_V); END IF; IF PA_LA_SEQU_P<>PA_LA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_LA_SEQU_N||CHR(164)||TO_CHAR(PA_LA_SEQU_V); END IF; IF PA_LMO_SEQU_P<>PA_LMO_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_LMO_SEQU_N||CHR(164)||TO_CHAR(PA_LMO_SEQU_V); END IF; IF PA_REF_SEQU_P<>PA_REF_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_REF_SEQU_N||CHR(164)||TO_CHAR(PA_REF_SEQU_V); END IF; IF PA_MS_SEQU_P<>PA_MS_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_MS_SEQU_N||CHR(164)||TO_CHAR(PA_MS_SEQU_V); END IF; IF (PA_STATE_P IS NOT NULL AND PA_STATE_V IS NULL) OR (PA_STATE_P IS NULL AND PA_STATE_V IS NOT NULL) OR (PA_STATE_P<>PA_STATE_V) THEN IF PA_STATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_STATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_STATE_N||CHR(164)||PA_STATE_V; END IF; END IF; IF (PA_UPD_DATE_P IS NOT NULL AND PA_UPD_DATE_V IS NULL) OR (PA_UPD_DATE_P IS NULL AND PA_UPD_DATE_V IS NOT NULL) OR (PA_UPD_DATE_P<>PA_UPD_DATE_V) THEN IF PA_UPD_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UPD_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_UPD_DATE_N||CHR(164)||TO_CHAR(PA_UPD_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (PA_OLD_MRN_P IS NOT NULL AND PA_OLD_MRN_V IS NULL) OR (PA_OLD_MRN_P IS NULL AND PA_OLD_MRN_V IS NOT NULL) OR (PA_OLD_MRN_P<>PA_OLD_MRN_V) THEN IF PA_OLD_MRN_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_OLD_MRN_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_OLD_MRN_N||CHR(164)||PA_OLD_MRN_V; END IF; END IF; IF (PA_EST_ARR_TIME_P IS NOT NULL AND PA_EST_ARR_TIME_V IS NULL) OR (PA_EST_ARR_TIME_P IS NULL AND PA_EST_ARR_TIME_V IS NOT NULL) OR (PA_EST_ARR_TIME_P<>PA_EST_ARR_TIME_V) THEN IF PA_EST_ARR_TIME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_EST_ARR_TIME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_EST_ARR_TIME_N||CHR(164)||TO_CHAR(PA_EST_ARR_TIME_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (PA_STREET_2_P IS NOT NULL AND PA_STREET_2_V IS NULL) OR (PA_STREET_2_P IS NULL AND PA_STREET_2_V IS NOT NULL) OR (PA_STREET_2_P<>PA_STREET_2_V) THEN IF PA_STREET_2_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_STREET_2_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_STREET_2_N||CHR(164)||PA_STREET_2_V; END IF; END IF; IF (PA_CITY_P IS NOT NULL AND PA_CITY_V IS NULL) OR (PA_CITY_P IS NULL AND PA_CITY_V IS NOT NULL) OR (PA_CITY_P<>PA_CITY_V) THEN IF PA_CITY_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CITY_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CITY_N||CHR(164)||PA_CITY_V; END IF; END IF; IF (PA_SENT_FOR_DATE_TIME_P IS NOT NULL AND PA_SENT_FOR_DATE_TIME_V IS NULL) OR (PA_SENT_FOR_DATE_TIME_P IS NULL AND PA_SENT_FOR_DATE_TIME_V IS NOT NULL) OR (PA_SENT_FOR_DATE_TIME_P<>PA_SENT_FOR_DATE_TIME_V) THEN IF PA_SENT_FOR_DATE_TIME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SENT_FOR_DATE_TIME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SENT_FOR_DATE_TIME_N||CHR(164)||TO_CHAR(PA_SENT_FOR_DATE_TIME_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (PA_PORTER_LEFT_DATE_TIME_P IS NOT NULL AND PA_PORTER_LEFT_DATE_TIME_V IS NULL) OR (PA_PORTER_LEFT_DATE_TIME_P IS NULL AND PA_PORTER_LEFT_DATE_TIME_V IS NOT NULL) OR (PA_PORTER_LEFT_DATE_TIME_P<>PA_PORTER_LEFT_DATE_TIME_V) THEN IF PA_PORTER_LEFT_DATE_TIME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_PORTER_LEFT_DATE_TIME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_PORTER_LEFT_DATE_TIME_N||CHR(164)||TO_CHAR(PA_PORTER_LEFT_DATE_TIME_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF PA_PORTER_SEQU_P<>PA_PORTER_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_PORTER_SEQU_N||CHR(164)||TO_CHAR(PA_PORTER_SEQU_V); END IF; IF PA_ORDER_CC_P<>PA_ORDER_CC_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ORDER_CC_N||CHR(164)||TO_CHAR(PA_ORDER_CC_V); END IF; IF PA_CONS_CC_SEQU_P<>PA_CONS_CC_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CONS_CC_SEQU_N||CHR(164)||TO_CHAR(PA_CONS_CC_SEQU_V); END IF; IF PA_TH_CC_SEQU_P<>PA_TH_CC_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_TH_CC_SEQU_N||CHR(164)||TO_CHAR(PA_TH_CC_SEQU_V); END IF; IF PA_SESSION_NO_CC_P<>PA_SESSION_NO_CC_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SESSION_NO_CC_N||CHR(164)||TO_CHAR(PA_SESSION_NO_CC_V); END IF; IF (PA_EST_START_CC_P IS NOT NULL AND PA_EST_START_CC_V IS NULL) OR (PA_EST_START_CC_P IS NULL AND PA_EST_START_CC_V IS NOT NULL) OR (PA_EST_START_CC_P<>PA_EST_START_CC_V) THEN IF PA_EST_START_CC_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_EST_START_CC_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_EST_START_CC_N||CHR(164)||TO_CHAR(PA_EST_START_CC_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF PA_S1_CC_SEQU_P<>PA_S1_CC_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_S1_CC_SEQU_N||CHR(164)||TO_CHAR(PA_S1_CC_SEQU_V); END IF; IF PA_SU_CC_SEQU_P<>PA_SU_CC_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SU_CC_SEQU_N||CHR(164)||TO_CHAR(PA_SU_CC_SEQU_V); END IF; IF PA_AN_CC_SEQU_P<>PA_AN_CC_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_AN_CC_SEQU_N||CHR(164)||TO_CHAR(PA_AN_CC_SEQU_V); END IF; IF PA_COMBINED_FLAG_P<>PA_COMBINED_FLAG_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_COMBINED_FLAG_N||CHR(164)||TO_CHAR(PA_COMBINED_FLAG_V); END IF; IF PA_FIXED_TIME_CC_P<>PA_FIXED_TIME_CC_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_FIXED_TIME_CC_N||CHR(164)||TO_CHAR(PA_FIXED_TIME_CC_V); END IF; IF PA_PICKLIST_PRINTED_P<>PA_PICKLIST_PRINTED_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_PICKLIST_PRINTED_N||CHR(164)||TO_CHAR(PA_PICKLIST_PRINTED_V); END IF; IF PA_PICKLIST_MERGED_P<>PA_PICKLIST_MERGED_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_PICKLIST_MERGED_N||CHR(164)||TO_CHAR(PA_PICKLIST_MERGED_V); END IF; IF (BK_RECD_DT_TM_P IS NOT NULL AND BK_RECD_DT_TM_V IS NULL) OR (BK_RECD_DT_TM_P IS NULL AND BK_RECD_DT_TM_V IS NOT NULL) OR (BK_RECD_DT_TM_P<>BK_RECD_DT_TM_V) THEN IF BK_RECD_DT_TM_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||BK_RECD_DT_TM_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||BK_RECD_DT_TM_N||CHR(164)||TO_CHAR(BK_RECD_DT_TM_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (PA_INITIATED_BY_NAME_P IS NOT NULL AND PA_INITIATED_BY_NAME_V IS NULL) OR (PA_INITIATED_BY_NAME_P IS NULL AND PA_INITIATED_BY_NAME_V IS NOT NULL) OR (PA_INITIATED_BY_NAME_P<>PA_INITIATED_BY_NAME_V) THEN IF PA_INITIATED_BY_NAME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_INITIATED_BY_NAME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_INITIATED_BY_NAME_N||CHR(164)||PA_INITIATED_BY_NAME_V; END IF; END IF; IF PA_REQ_S1_SEQU_P<>PA_REQ_S1_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_REQ_S1_SEQU_N||CHR(164)||TO_CHAR(PA_REQ_S1_SEQU_V); END IF; IF (PA_HSC_COMMENTS_P IS NOT NULL AND PA_HSC_COMMENTS_V IS NULL) OR (PA_HSC_COMMENTS_P IS NULL AND PA_HSC_COMMENTS_V IS NOT NULL) OR (PA_HSC_COMMENTS_P<>PA_HSC_COMMENTS_V) THEN IF PA_HSC_COMMENTS_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_HSC_COMMENTS_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_HSC_COMMENTS_N||CHR(164)||PA_HSC_COMMENTS_V; END IF; END IF; IF PA_SS_SEQU_P<>PA_SS_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SS_SEQU_N||CHR(164)||TO_CHAR(PA_SS_SEQU_V); END IF; IF PA_CC_SS_SEQU_P<>PA_CC_SS_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_CC_SS_SEQU_N||CHR(164)||TO_CHAR(PA_CC_SS_SEQU_V); END IF; IF PA_ORDER_REASON_SEQU_P<>PA_ORDER_REASON_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_ORDER_REASON_SEQU_N||CHR(164)||TO_CHAR(PA_ORDER_REASON_SEQU_V); END IF; IF PA_PRINTED_P<>PA_PRINTED_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_PRINTED_N||CHR(164)||TO_CHAR(PA_PRINTED_V); END IF; IF PA_PR_SEQU_P<>PA_PR_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_PR_SEQU_N||CHR(164)||TO_CHAR(PA_PR_SEQU_V); END IF; IF (PA_DELAY_COMMENTS_P IS NOT NULL AND PA_DELAY_COMMENTS_V IS NULL) OR (PA_DELAY_COMMENTS_P IS NULL AND PA_DELAY_COMMENTS_V IS NOT NULL) OR (PA_DELAY_COMMENTS_P<>PA_DELAY_COMMENTS_V) THEN IF PA_DELAY_COMMENTS_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_DELAY_COMMENTS_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_DELAY_COMMENTS_N||CHR(164)||PA_DELAY_COMMENTS_V; END IF; END IF; IF (PA_MIDDLE_NAME_P IS NOT NULL AND PA_MIDDLE_NAME_V IS NULL) OR (PA_MIDDLE_NAME_P IS NULL AND PA_MIDDLE_NAME_V IS NOT NULL) OR (PA_MIDDLE_NAME_P<>PA_MIDDLE_NAME_V) THEN IF PA_MIDDLE_NAME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_MIDDLE_NAME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_MIDDLE_NAME_N||CHR(164)||PA_MIDDLE_NAME_V; END IF; END IF; IF PA_IMS_CONFIRM_SENT_P<>PA_IMS_CONFIRM_SENT_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_IMS_CONFIRM_SENT_N||CHR(164)||TO_CHAR(PA_IMS_CONFIRM_SENT_V); END IF; IF PA_SEEN_ANAES_SEQU_P<>PA_SEEN_ANAES_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_SEEN_ANAES_SEQU_N||CHR(164)||TO_CHAR(PA_SEEN_ANAES_SEQU_V); END IF; IF PA_DIAG_TEST_COMP_SEQU_P<>PA_DIAG_TEST_COMP_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_DIAG_TEST_COMP_SEQU_N||CHR(164)||TO_CHAR(PA_DIAG_TEST_COMP_SEQU_V); END IF; IF PA_FASTED_SEQU_P<>PA_FASTED_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_FASTED_SEQU_N||CHR(164)||TO_CHAR(PA_FASTED_SEQU_V); END IF; IF PA_BREACH_REAS_SEQU_P<>PA_BREACH_REAS_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_BREACH_REAS_SEQU_N||CHR(164)||TO_CHAR(PA_BREACH_REAS_SEQU_V); END IF; IF (PA_IMS_BKG_REF_P IS NOT NULL AND PA_IMS_BKG_REF_V IS NULL) OR (PA_IMS_BKG_REF_P IS NULL AND PA_IMS_BKG_REF_V IS NOT NULL) OR (PA_IMS_BKG_REF_P<>PA_IMS_BKG_REF_V) THEN IF PA_IMS_BKG_REF_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_IMS_BKG_REF_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||PA_IMS_BKG_REF_N||CHR(164)||PA_IMS_BKG_REF_V; END IF; END IF; IF LENGTH(LV_AUDIT_TEXT)>0 THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164); LV_AUDIT_TEXT:=SUBSTR(LV_AUDIT_TEXT,2,LENGTH(LV_AUDIT_TEXT)); ELSE LV_AUDIT_TEXT:='NO CHANGE'; END IF; END IF; END IF; INSERT INTO F_AUDIT_TRAIL_ENH(AUDT_SPID,AUDT_APP_USER_NAME,AUDT_USER_ACTION,AUDT_PROGRAM,AUDT_NT_MACHINE_NAME,AUDT_NT_USER_NAME,AUDT_REC_SEQU,AUDT_TABLE,AUDT_PRIMARY_KEY_NAME,AUDT_AUDIT_CHAR,AUDT_DATE_TIME)VALUES(LV_SID_SPID,LV_APP_USER,LV_D_ACTION,LV_PROGRAM_NAME,LV_MACHINENAME,LV_NT_USERNAME,LV_AUDT_REC_SEQU,LV_AUDIT_TABLE,LV_AUDIT_PRIMARY_KEY_NAME,LV_AUDIT_TEXT,LV_INSERT_DATE_TIME); UPDATE F_SEQU SET SQ_COUNT=LV_COUNTER WHERE SQ_TABLE='APATS'; FETCH C_APATS INTO PA_SEQN_FILLER_V, PA_SEQU_V, PA_MRN_V, PA_OPER_DATE_V, PA_TH_CODE_V, PA_CUSTODIAN_V, PA_SESS_TYPE_V, PA_LAST_NAME_V, PA_FIRST_NAME_V, PA_DOB_V, PA_PIN_SEQU_V, PA_TH_SEQU_V, PA_CONS_SEQU_V, PA_SUCODE_V, PA_BIOHAZARD_V, PA_BLOOD_V, PA_EQUIPMENT_V, PA_AGEYR_V, PA_AGEMNTH_V, PA_WARD_V, PA_TIME_V, PA_EPISODE_NUM_V, PA_OPCOMM_V, PA_ORDER_V, PA_TRANSF_V, PA_STAT_V, PA_CLCODE_V, PA_ANA_SEQU_V, PA_ANAES_TYPE_V, PA_SURG_COMM_V, PA_SURG_SPEC_V, PA_BED_V, PA_WLSEQ_V, PA_SESSION_NO_V, PA_TOTAL_MINS_V, PA_CHANGE_OVER_V, PA_GENDER_V, PA_SURGEON_V, PA_FILLER_01_V, PA_REQ_TIME_V, PA_TIME_READY_V, PA_VERIFIED_V, PA_UNPLANNED_V, PA_TYPE_V, PA_TH_SESS_SORT_V, PA_EST_START_V, PA_SU_SEQU_V, PA_CL_SEQU_V, PA_WA_SEQU_V, PA_S1_SEQU_V, PA_LOG_DATE_V, PA_LOG_DETAILS_FILLER_V, PA_WL_SEQU_V, PA_AN_SEQU_V, PA_STREET_V, PA_SUBURB_V, PA_POSTCODE_V, PA_HOME_PHONE_V, PA_BUS_PHONE_V, PA_AGDAYS_V, PA_TA_SEQU_V, PA_CHANGE_FLAG_V, PA_ASA_SEQU_V, PA_UNUSED_4_V, PA_UNUSED_5_V, PA_UNUSED_6_V, PA_UNUSED_7_V, PA_UNUSED_8_V, PA_PATHOLOGY_V, PA_XRAY_V, PA_PATH_COMMENT_V, PA_XRAY_COMMENT_V, PA_BLOOD_COMMENT_V, PA_BLOOD_NO_OF_UNITS_V, PA_WARD_ADV_DATE_TIME_V, PA_FILLER_02_V, PA_TRANS_V, PA_NURSE_REQ_SEQU_V, PA_NURSE_REC_NOTIF_V, PA_OXYGEN_V, PA_IV_V, PA_WA_TO_SEQU_V, PA_SU2_SEQU_V, PA_SU3_SEQU_V, PA_AN2_SEQU_V, PA_AN3_SEQU_V, PA_SC_NUR_SEQU_V, PA_AN_NUR_SEQU_V, PA_IN_NUR_SEQU_V, PA_TECH1_SEQU_V, PA_TECH2_SEQU_V, PA_UNUSED_1_V, PA_TIME_THEATRE_V, PA_CA_SEQU_V, PA_CLP_SEQU_V, PA_BOOK_SU_SEQU_V, PA_ADDITIONAL_V, PA_CAM_SEQU_V, PA_CAM_COMMENT_V, PA_ADMIT_SU_SEQU_V, PA_FLAG_V, PA_EXT_NO_FIELD_V, PA_FLAG_2_V, PA_ADMIT_DATE_TIME_V, PA_ACC_INDICATOR_V, PA_ACC_NUMBER_V, PA_ACC_SEQU_V, PA_MAJOR_MINOR_V, PA_FIXED_TIME_V, PA_LA_SEQU_V, PA_LMO_SEQU_V, PA_REF_SEQU_V, PA_MS_SEQU_V, PA_STATE_V, PA_UPD_DATE_V, PA_OLD_MRN_V, PA_EST_ARR_TIME_V, PA_STREET_2_V, PA_CITY_V, PA_SENT_FOR_DATE_TIME_V, PA_PORTER_LEFT_DATE_TIME_V, PA_PORTER_SEQU_V, PA_ORDER_CC_V, PA_CONS_CC_SEQU_V, PA_TH_CC_SEQU_V, PA_SESSION_NO_CC_V, PA_EST_START_CC_V, PA_S1_CC_SEQU_V, PA_SU_CC_SEQU_V, PA_AN_CC_SEQU_V, PA_COMBINED_FLAG_V, PA_FIXED_TIME_CC_V, PA_PICKLIST_PRINTED_V, PA_PICKLIST_MERGED_V, BK_RECD_DT_TM_V, PA_INITIATED_BY_NAME_V, PA_REQ_S1_SEQU_V, PA_HSC_COMMENTS_V, PA_SS_SEQU_V, PA_CC_SS_SEQU_V, PA_ORDER_REASON_SEQU_V, PA_PRINTED_V, PA_PR_SEQU_V, PA_DELAY_COMMENTS_V, PA_MIDDLE_NAME_V, PA_IMS_CONFIRM_SENT_V, PA_SEEN_ANAES_SEQU_V, PA_DIAG_TEST_COMP_SEQU_V, PA_FASTED_SEQU_V, PA_BREACH_REAS_SEQU_V, PA_IMS_BKG_REF_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; END LOOP; CLOSE C_APATS; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('NO DATA FOUND'); END; / /**************************************/ /* SP_A_WAITING_DETAILS is changed because WD_SURG_COMM is changed */ /**************************************/ CREATE OR REPLACE PROCEDURE SP_A_CLOSURE_TYPE IS CT_SEQN_FILLER_V NUMBER(9,0); CT_SEQN_FILLER_P NUMBER(9,0); CT_SEQN_FILLER_N VARCHAR2(50):='CT_SEQN_FILLER'; CT_CODE_V VARCHAR2(5); CT_CODE_P VARCHAR2(5); CT_CODE_N VARCHAR2(50):='CT_CODE'; CT_DESCRIPTION_V VARCHAR2(60); CT_DESCRIPTION_P VARCHAR2(60); CT_DESCRIPTION_N VARCHAR2(50):='CT_DESCRIPTION'; CT_SEQU_V NUMBER(9,0); CT_SEQU_P NUMBER(9,0); CT_SEQU_N VARCHAR2(50):='CT_SEQU'; CT_COMPLETE_CLOSE_V NUMBER(3,0); CT_COMPLETE_CLOSE_P NUMBER(3,0); CT_COMPLETE_CLOSE_N VARCHAR2(50):='CT_COMPLETE_CLOSE'; CT_LOG_DATE_V date; CT_LOG_DATE_P date; CT_LOG_DATE_N VARCHAR2(50):='CT_LOG_DATE'; CT_LOG_DETAILS_FILLER_V VARCHAR2(1); CT_LOG_DETAILS_FILLER_P VARCHAR2(1); CT_LOG_DETAILS_FILLER_N VARCHAR2(50):='CT_LOG_DETAILS_FILLER'; CT_INACTIVE_V NUMBER(3,0); CT_INACTIVE_P NUMBER(3,0); CT_INACTIVE_N VARCHAR2(50):='CT_INACTIVE'; CT_ORDER_V NUMBER(9,0); CT_ORDER_P NUMBER(9,0); CT_ORDER_N VARCHAR2(50):='CT_ORDER'; CT_CA_SEQU_V NUMBER(9,0); CT_CA_SEQU_P NUMBER(9,0); CT_CA_SEQU_N VARCHAR2(50):='CT_CA_SEQU'; CT_START_DATE_V date; CT_START_DATE_P date; CT_START_DATE_N VARCHAR2(50):='CT_START_DATE'; CT_END_DATE_V date; CT_END_DATE_P date; CT_END_DATE_N VARCHAR2(50):='CT_END_DATE'; LV_COUNTER NUMBER(18,0); LV_COUNTER_P NUMBER(18,0); LV_SID_SPID NUMBER(18,0); LV_D_ACTION VARCHAR2(1); LV_INSERT_DATE_TIME DATE; LV_MACHINENAME VARCHAR2(50); LV_APP_USER VARCHAR2(20); LV_PROGRAM_NAME VARCHAR2(50); LV_NT_DOMAIN VARCHAR2(50); LV_NT_USERNAME VARCHAR2(50); LV_LAST_PROCESS_SEQU NUMBER(9,0); LV_AUDIT_TABLE VARCHAR2(50); LV_AUDIT_PRIMARY_KEY_NAME VARCHAR2(50); LV_AUDIT_TEXT VARCHAR2(8000); LV_AUDT_REC_SEQU VARCHAR2(100); CURSOR C_A_CLOSURE_TYPE IS SELECT CT_SEQN_FILLER, CT_CODE, CT_DESCRIPTION, CT_SEQU, CT_COMPLETE_CLOSE, CT_LOG_DATE, CT_LOG_DETAILS_FILLER, CT_INACTIVE, CT_ORDER, CT_CA_SEQU, CT_START_DATE, CT_END_DATE, SID_SPID, D_ACTION, INSERT_DATE_TIME, MACHINENAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME, COUNTER FROM A_CLOSURE_TYPE WHERE COUNTER > (SELECT SQ_COUNT FROM F_SEQU WHERE SQ_TABLE='A_CLOSURE_TYPE'); BEGIN OPEN C_A_CLOSURE_TYPE; FETCH C_A_CLOSURE_TYPE INTO CT_SEQN_FILLER_V, CT_CODE_V, CT_DESCRIPTION_V, CT_SEQU_V, CT_COMPLETE_CLOSE_V, CT_LOG_DATE_V, CT_LOG_DETAILS_FILLER_V, CT_INACTIVE_V, CT_ORDER_V, CT_CA_SEQU_V, CT_START_DATE_V, CT_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; WHILE C_A_CLOSURE_TYPE%FOUND LOOP LV_AUDIT_TEXT:=''; LV_AUDIT_TABLE:='F_Closure_type'; LV_AUDIT_PRIMARY_KEY_NAME:='F_Closure_type'||'/'||TO_CHAR(CT_SEQU_V); LV_AUDT_REC_SEQU:=TO_CHAR(CT_SEQU_V); BEGIN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE((LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND LV_INSERT_DATE_TIME<=AUD_DATE_TIME_EXIT) OR(LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND AUD_DATE_TIME_EXIT IS NULL)) AND AUD_SPID=LV_SID_SPID; EXCEPTION WHEN NO_DATA_FOUND THEN LV_APP_USER:=''; WHEN TOO_MANY_ROWS THEN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE AUD_SPID=LV_SID_SPID AND ROWNUM=1; END; BEGIN SELECT MAX(COUNTER) INTO LV_COUNTER_P FROM A_CLOSURE_TYPE WHERE CT_SEQU=CT_SEQU_V AND COUNTERCT_SEQN_FILLER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_SEQN_FILLER_N||CHR(164)||TO_CHAR(CT_SEQN_FILLER_V); END IF; IF (CT_CODE_P IS NOT NULL AND CT_CODE_V IS NULL) OR (CT_CODE_P IS NULL AND CT_CODE_V IS NOT NULL) OR (CT_CODE_P<>CT_CODE_V) THEN IF CT_CODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_CODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_CODE_N||CHR(164)||CT_CODE_V; END IF; END IF; IF (CT_DESCRIPTION_P IS NOT NULL AND CT_DESCRIPTION_V IS NULL) OR (CT_DESCRIPTION_P IS NULL AND CT_DESCRIPTION_V IS NOT NULL) OR (CT_DESCRIPTION_P<>CT_DESCRIPTION_V) THEN IF CT_DESCRIPTION_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_DESCRIPTION_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_DESCRIPTION_N||CHR(164)||CT_DESCRIPTION_V; END IF; END IF; IF CT_SEQU_P<>CT_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_SEQU_N||CHR(164)||TO_CHAR(CT_SEQU_V); END IF; IF CT_COMPLETE_CLOSE_P<>CT_COMPLETE_CLOSE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_COMPLETE_CLOSE_N||CHR(164)||TO_CHAR(CT_COMPLETE_CLOSE_V); END IF; IF (CT_LOG_DATE_P IS NOT NULL AND CT_LOG_DATE_V IS NULL) OR (CT_LOG_DATE_P IS NULL AND CT_LOG_DATE_V IS NOT NULL) OR (CT_LOG_DATE_P<>CT_LOG_DATE_V) THEN IF CT_LOG_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_LOG_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_LOG_DATE_N||CHR(164)||TO_CHAR(CT_LOG_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (CT_LOG_DETAILS_FILLER_P IS NOT NULL AND CT_LOG_DETAILS_FILLER_V IS NULL) OR (CT_LOG_DETAILS_FILLER_P IS NULL AND CT_LOG_DETAILS_FILLER_V IS NOT NULL) OR (CT_LOG_DETAILS_FILLER_P<>CT_LOG_DETAILS_FILLER_V) THEN IF CT_LOG_DETAILS_FILLER_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_LOG_DETAILS_FILLER_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_LOG_DETAILS_FILLER_N||CHR(164)||CT_LOG_DETAILS_FILLER_V; END IF; END IF; IF CT_INACTIVE_P<>CT_INACTIVE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_INACTIVE_N||CHR(164)||TO_CHAR(CT_INACTIVE_V); END IF; IF CT_ORDER_P<>CT_ORDER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_ORDER_N||CHR(164)||TO_CHAR(CT_ORDER_V); END IF; IF CT_CA_SEQU_P<>CT_CA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_CA_SEQU_N||CHR(164)||TO_CHAR(CT_CA_SEQU_V); END IF; IF (CT_START_DATE_P IS NOT NULL AND CT_START_DATE_V IS NULL) OR (CT_START_DATE_P IS NULL AND CT_START_DATE_V IS NOT NULL) OR (CT_START_DATE_P<>CT_START_DATE_V) THEN IF CT_START_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_START_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_START_DATE_N||CHR(164)||TO_CHAR(CT_START_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (CT_END_DATE_P IS NOT NULL AND CT_END_DATE_V IS NULL) OR (CT_END_DATE_P IS NULL AND CT_END_DATE_V IS NOT NULL) OR (CT_END_DATE_P<>CT_END_DATE_V) THEN IF CT_END_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_END_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CT_END_DATE_N||CHR(164)||TO_CHAR(CT_END_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF LENGTH(LV_AUDIT_TEXT)>0 THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164); LV_AUDIT_TEXT:=SUBSTR(LV_AUDIT_TEXT,2,LENGTH(LV_AUDIT_TEXT)); ELSE LV_AUDIT_TEXT:='NO CHANGE'; END IF; END IF; END IF; INSERT INTO F_AUDIT_TRAIL_ENH(AUDT_SPID,AUDT_APP_USER_NAME,AUDT_USER_ACTION,AUDT_PROGRAM,AUDT_NT_MACHINE_NAME,AUDT_NT_USER_NAME,AUDT_REC_SEQU,AUDT_TABLE,AUDT_PRIMARY_KEY_NAME,AUDT_AUDIT_CHAR,AUDT_DATE_TIME)VALUES(LV_SID_SPID,LV_APP_USER,LV_D_ACTION,LV_PROGRAM_NAME,LV_MACHINENAME,LV_NT_USERNAME,LV_AUDT_REC_SEQU,LV_AUDIT_TABLE,LV_AUDIT_PRIMARY_KEY_NAME,LV_AUDIT_TEXT,TO_DATE(LV_INSERT_DATE_TIME,'DD MON YYYY HH24:MI:SS')); UPDATE F_SEQU SET SQ_COUNT=LV_COUNTER WHERE SQ_TABLE='A_CLOSURE_TYPE'; FETCH C_A_CLOSURE_TYPE INTO CT_SEQN_FILLER_V, CT_CODE_V, CT_DESCRIPTION_V, CT_SEQU_V, CT_COMPLETE_CLOSE_V, CT_LOG_DATE_V, CT_LOG_DETAILS_FILLER_V, CT_INACTIVE_V, CT_ORDER_V, CT_CA_SEQU_V, CT_START_DATE_V, CT_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; END LOOP; CLOSE C_A_CLOSURE_TYPE; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('NO DATA FOUND'); END; / CREATE OR REPLACE PROCEDURE SP_A_DELAY_STATUS IS DES_SEQN_FILLER_V NUMBER(9,0); DES_SEQN_FILLER_P NUMBER(9,0); DES_SEQN_FILLER_N VARCHAR2(50):='DES_SEQN_FILLER'; DES_CODE_V VARCHAR2(3); DES_CODE_P VARCHAR2(3); DES_CODE_N VARCHAR2(50):='DES_CODE'; DES_DESC_V VARCHAR2(60); DES_DESC_P VARCHAR2(60); DES_DESC_N VARCHAR2(50):='DES_DESC'; DES_SEQU_V NUMBER(9,0); DES_SEQU_P NUMBER(9,0); DES_SEQU_N VARCHAR2(50):='DES_SEQU'; DES_DEFAULT_V NUMBER(3,0); DES_DEFAULT_P NUMBER(3,0); DES_DEFAULT_N VARCHAR2(50):='DES_DEFAULT'; DES_LOG_DATE_V date; DES_LOG_DATE_P date; DES_LOG_DATE_N VARCHAR2(50):='DES_LOG_DATE'; DES_LOG_DETAILS_FILLER_V VARCHAR2(1); DES_LOG_DETAILS_FILLER_P VARCHAR2(1); DES_LOG_DETAILS_FILLER_N VARCHAR2(50):='DES_LOG_DETAILS_FILLER'; DES_TYPE_V NUMBER(3,0); DES_TYPE_P NUMBER(3,0); DES_TYPE_N VARCHAR2(50):='DES_TYPE'; DES_INACTIVE_V NUMBER(3,0); DES_INACTIVE_P NUMBER(3,0); DES_INACTIVE_N VARCHAR2(50):='DES_INACTIVE'; DES_DELAY_COUNTER_V NUMBER(3,0); DES_DELAY_COUNTER_P NUMBER(3,0); DES_DELAY_COUNTER_N VARCHAR2(50):='DES_DELAY_COUNTER'; DES_CA_SEQU_V NUMBER(9,0); DES_CA_SEQU_P NUMBER(9,0); DES_CA_SEQU_N VARCHAR2(50):='DES_CA_SEQU'; DES_START_DATE_V date; DES_START_DATE_P date; DES_START_DATE_N VARCHAR2(50):='DES_START_DATE'; DES_END_DATE_V date; DES_END_DATE_P date; DES_END_DATE_N VARCHAR2(50):='DES_END_DATE'; LV_COUNTER NUMBER(18,0); LV_COUNTER_P NUMBER(18,0); LV_SID_SPID NUMBER(18,0); LV_D_ACTION VARCHAR2(1); LV_INSERT_DATE_TIME DATE; LV_MACHINENAME VARCHAR2(50); LV_APP_USER VARCHAR2(20); LV_PROGRAM_NAME VARCHAR2(50); LV_NT_DOMAIN VARCHAR2(50); LV_NT_USERNAME VARCHAR2(50); LV_LAST_PROCESS_SEQU NUMBER(9,0); LV_AUDIT_TABLE VARCHAR2(50); LV_AUDIT_PRIMARY_KEY_NAME VARCHAR2(50); LV_AUDIT_TEXT VARCHAR2(8000); LV_AUDT_REC_SEQU VARCHAR2(100); CURSOR C_A_DELAY_STATUS IS SELECT DES_SEQN_FILLER, DES_CODE, DES_DESC, DES_SEQU, DES_DEFAULT, DES_LOG_DATE, DES_LOG_DETAILS_FILLER, DES_TYPE, DES_INACTIVE, DES_DELAY_COUNTER, DES_CA_SEQU, DES_START_DATE, DES_END_DATE, SID_SPID, D_ACTION, INSERT_DATE_TIME, MACHINENAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME, COUNTER FROM A_DELAY_STATUS WHERE COUNTER > (SELECT SQ_COUNT FROM F_SEQU WHERE SQ_TABLE='A_DELAY_STATUS'); BEGIN OPEN C_A_DELAY_STATUS; FETCH C_A_DELAY_STATUS INTO DES_SEQN_FILLER_V, DES_CODE_V, DES_DESC_V, DES_SEQU_V, DES_DEFAULT_V, DES_LOG_DATE_V, DES_LOG_DETAILS_FILLER_V, DES_TYPE_V, DES_INACTIVE_V, DES_DELAY_COUNTER_V, DES_CA_SEQU_V, DES_START_DATE_V, DES_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; WHILE C_A_DELAY_STATUS%FOUND LOOP LV_AUDIT_TEXT:=''; LV_AUDIT_TABLE:='F_Delay_Status'; LV_AUDIT_PRIMARY_KEY_NAME:='F_Delay_Status'||'/'||TO_CHAR(DES_SEQU_V); LV_AUDT_REC_SEQU:=TO_CHAR(DES_SEQU_V); BEGIN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE((LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND LV_INSERT_DATE_TIME<=AUD_DATE_TIME_EXIT) OR(LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND AUD_DATE_TIME_EXIT IS NULL)) AND AUD_SPID=LV_SID_SPID; EXCEPTION WHEN NO_DATA_FOUND THEN LV_APP_USER:=''; WHEN TOO_MANY_ROWS THEN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE AUD_SPID=LV_SID_SPID AND ROWNUM=1; END; BEGIN SELECT MAX(COUNTER) INTO LV_COUNTER_P FROM A_DELAY_STATUS WHERE DES_SEQU=DES_SEQU_V AND COUNTERDES_SEQN_FILLER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_SEQN_FILLER_N||CHR(164)||TO_CHAR(DES_SEQN_FILLER_V); END IF; IF (DES_CODE_P IS NOT NULL AND DES_CODE_V IS NULL) OR (DES_CODE_P IS NULL AND DES_CODE_V IS NOT NULL) OR (DES_CODE_P<>DES_CODE_V) THEN IF DES_CODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_CODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_CODE_N||CHR(164)||DES_CODE_V; END IF; END IF; IF (DES_DESC_P IS NOT NULL AND DES_DESC_V IS NULL) OR (DES_DESC_P IS NULL AND DES_DESC_V IS NOT NULL) OR (DES_DESC_P<>DES_DESC_V) THEN IF DES_DESC_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_DESC_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_DESC_N||CHR(164)||DES_DESC_V; END IF; END IF; IF DES_SEQU_P<>DES_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_SEQU_N||CHR(164)||TO_CHAR(DES_SEQU_V); END IF; IF DES_DEFAULT_P<>DES_DEFAULT_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_DEFAULT_N||CHR(164)||TO_CHAR(DES_DEFAULT_V); END IF; IF (DES_LOG_DATE_P IS NOT NULL AND DES_LOG_DATE_V IS NULL) OR (DES_LOG_DATE_P IS NULL AND DES_LOG_DATE_V IS NOT NULL) OR (DES_LOG_DATE_P<>DES_LOG_DATE_V) THEN IF DES_LOG_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_LOG_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_LOG_DATE_N||CHR(164)||TO_CHAR(DES_LOG_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (DES_LOG_DETAILS_FILLER_P IS NOT NULL AND DES_LOG_DETAILS_FILLER_V IS NULL) OR (DES_LOG_DETAILS_FILLER_P IS NULL AND DES_LOG_DETAILS_FILLER_V IS NOT NULL) OR (DES_LOG_DETAILS_FILLER_P<>DES_LOG_DETAILS_FILLER_V) THEN IF DES_LOG_DETAILS_FILLER_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_LOG_DETAILS_FILLER_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_LOG_DETAILS_FILLER_N||CHR(164)||DES_LOG_DETAILS_FILLER_V; END IF; END IF; IF DES_TYPE_P<>DES_TYPE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_TYPE_N||CHR(164)||TO_CHAR(DES_TYPE_V); END IF; IF DES_INACTIVE_P<>DES_INACTIVE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_INACTIVE_N||CHR(164)||TO_CHAR(DES_INACTIVE_V); END IF; IF DES_DELAY_COUNTER_P<>DES_DELAY_COUNTER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_DELAY_COUNTER_N||CHR(164)||TO_CHAR(DES_DELAY_COUNTER_V); END IF; IF DES_CA_SEQU_P<>DES_CA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_CA_SEQU_N||CHR(164)||TO_CHAR(DES_CA_SEQU_V); END IF; IF (DES_START_DATE_P IS NOT NULL AND DES_START_DATE_V IS NULL) OR (DES_START_DATE_P IS NULL AND DES_START_DATE_V IS NOT NULL) OR (DES_START_DATE_P<>DES_START_DATE_V) THEN IF DES_START_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_START_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_START_DATE_N||CHR(164)||TO_CHAR(DES_START_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (DES_END_DATE_P IS NOT NULL AND DES_END_DATE_V IS NULL) OR (DES_END_DATE_P IS NULL AND DES_END_DATE_V IS NOT NULL) OR (DES_END_DATE_P<>DES_END_DATE_V) THEN IF DES_END_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_END_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DES_END_DATE_N||CHR(164)||TO_CHAR(DES_END_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF LENGTH(LV_AUDIT_TEXT)>0 THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164); LV_AUDIT_TEXT:=SUBSTR(LV_AUDIT_TEXT,2,LENGTH(LV_AUDIT_TEXT)); ELSE LV_AUDIT_TEXT:='NO CHANGE'; END IF; END IF; END IF; INSERT INTO F_AUDIT_TRAIL_ENH(AUDT_SPID,AUDT_APP_USER_NAME,AUDT_USER_ACTION,AUDT_PROGRAM,AUDT_NT_MACHINE_NAME,AUDT_NT_USER_NAME,AUDT_REC_SEQU,AUDT_TABLE,AUDT_PRIMARY_KEY_NAME,AUDT_AUDIT_CHAR,AUDT_DATE_TIME)VALUES(LV_SID_SPID,LV_APP_USER,LV_D_ACTION,LV_PROGRAM_NAME,LV_MACHINENAME,LV_NT_USERNAME,LV_AUDT_REC_SEQU,LV_AUDIT_TABLE,LV_AUDIT_PRIMARY_KEY_NAME,LV_AUDIT_TEXT,TO_DATE(LV_INSERT_DATE_TIME,'DD MON YYYY HH24:MI:SS')); UPDATE F_SEQU SET SQ_COUNT=LV_COUNTER WHERE SQ_TABLE='A_DELAY_STATUS'; FETCH C_A_DELAY_STATUS INTO DES_SEQN_FILLER_V, DES_CODE_V, DES_DESC_V, DES_SEQU_V, DES_DEFAULT_V, DES_LOG_DATE_V, DES_LOG_DETAILS_FILLER_V, DES_TYPE_V, DES_INACTIVE_V, DES_DELAY_COUNTER_V, DES_CA_SEQU_V, DES_START_DATE_V, DES_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; END LOOP; CLOSE C_A_DELAY_STATUS; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('NO DATA FOUND'); END; / CREATE OR REPLACE PROCEDURE SP_A_CLINICAL_PRIORITY IS CLP_SEQN_FILLER_V NUMBER(9,0); CLP_SEQN_FILLER_P NUMBER(9,0); CLP_SEQN_FILLER_N VARCHAR2(50):='CLP_SEQN_FILLER'; CLP_CODE_V VARCHAR2(3); CLP_CODE_P VARCHAR2(3); CLP_CODE_N VARCHAR2(50):='CLP_CODE'; CLP_DESCRIPTION_V VARCHAR2(60); CLP_DESCRIPTION_P VARCHAR2(60); CLP_DESCRIPTION_N VARCHAR2(50):='CLP_DESCRIPTION'; CLP_SEQU_V NUMBER(9,0); CLP_SEQU_P NUMBER(9,0); CLP_SEQU_N VARCHAR2(50):='CLP_SEQU'; CLP_MAX_TIME_V NUMBER(9,0); CLP_MAX_TIME_P NUMBER(9,0); CLP_MAX_TIME_N VARCHAR2(50):='CLP_MAX_TIME'; CLP_LOG_DATE_V date; CLP_LOG_DATE_P date; CLP_LOG_DATE_N VARCHAR2(50):='CLP_LOG_DATE'; CLP_LOG_DETAILS_FILLER_V VARCHAR2(1); CLP_LOG_DETAILS_FILLER_P VARCHAR2(1); CLP_LOG_DETAILS_FILLER_N VARCHAR2(50):='CLP_LOG_DETAILS_FILLER'; CLP_INACTIVE_V NUMBER(3,0); CLP_INACTIVE_P NUMBER(3,0); CLP_INACTIVE_N VARCHAR2(50):='CLP_INACTIVE'; CLP_CA_SEQU_V NUMBER(9,0); CLP_CA_SEQU_P NUMBER(9,0); CLP_CA_SEQU_N VARCHAR2(50):='CLP_CA_SEQU'; CLP_START_DATE_V date; CLP_START_DATE_P date; CLP_START_DATE_N VARCHAR2(50):='CLP_START_DATE'; CLP_END_DATE_V date; CLP_END_DATE_P date; CLP_END_DATE_N VARCHAR2(50):='CLP_END_DATE'; CLP_OP_TYPE_V NUMBER(3,0); CLP_OP_TYPE_P NUMBER(3,0); CLP_OP_TYPE_N VARCHAR2(50):='CLP_OP_TYPE'; LV_COUNTER NUMBER(18,0); LV_COUNTER_P NUMBER(18,0); LV_SID_SPID NUMBER(18,0); LV_D_ACTION VARCHAR2(1); LV_INSERT_DATE_TIME DATE; LV_MACHINENAME VARCHAR2(50); LV_APP_USER VARCHAR2(20); LV_PROGRAM_NAME VARCHAR2(50); LV_NT_DOMAIN VARCHAR2(50); LV_NT_USERNAME VARCHAR2(50); LV_LAST_PROCESS_SEQU NUMBER(9,0); LV_AUDIT_TABLE VARCHAR2(50); LV_AUDIT_PRIMARY_KEY_NAME VARCHAR2(50); LV_AUDIT_TEXT VARCHAR2(8000); LV_AUDT_REC_SEQU VARCHAR2(100); CURSOR C_A_CLINICAL_PRIORITY IS SELECT CLP_SEQN_FILLER, CLP_CODE, CLP_DESCRIPTION, CLP_SEQU, CLP_MAX_TIME, CLP_LOG_DATE, CLP_LOG_DETAILS_FILLER, CLP_INACTIVE, CLP_CA_SEQU, CLP_OP_TYPE, CLP_START_DATE, CLP_END_DATE, SID_SPID, D_ACTION, INSERT_DATE_TIME, MACHINENAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME, COUNTER FROM A_CLINICAL_PRIORITY WHERE COUNTER > (SELECT SQ_COUNT FROM F_SEQU WHERE SQ_TABLE='A_CLINICAL_PRIORITY'); BEGIN OPEN C_A_CLINICAL_PRIORITY; FETCH C_A_CLINICAL_PRIORITY INTO CLP_SEQN_FILLER_V, CLP_CODE_V, CLP_DESCRIPTION_V, CLP_SEQU_V, CLP_MAX_TIME_V, CLP_LOG_DATE_V, CLP_LOG_DETAILS_FILLER_V, CLP_INACTIVE_V, CLP_CA_SEQU_V, CLP_OP_TYPE_V, CLP_START_DATE_V, CLP_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; WHILE C_A_CLINICAL_PRIORITY%FOUND LOOP LV_AUDIT_TEXT:=''; LV_AUDIT_TABLE:='F_Clinical_Priority'; LV_AUDIT_PRIMARY_KEY_NAME:='F_Clinical_Priority'||'/'||TO_CHAR(CLP_SEQU_V); LV_AUDT_REC_SEQU:=TO_CHAR(CLP_SEQU_V); BEGIN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE((LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND LV_INSERT_DATE_TIME<=AUD_DATE_TIME_EXIT) OR(LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND AUD_DATE_TIME_EXIT IS NULL)) AND AUD_SPID=LV_SID_SPID; EXCEPTION WHEN NO_DATA_FOUND THEN LV_APP_USER:=''; WHEN TOO_MANY_ROWS THEN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE AUD_SPID=LV_SID_SPID AND ROWNUM=1; END; BEGIN SELECT MAX(COUNTER) INTO LV_COUNTER_P FROM A_CLINICAL_PRIORITY WHERE CLP_SEQU=CLP_SEQU_V AND COUNTERCLP_SEQN_FILLER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_SEQN_FILLER_N||CHR(164)||TO_CHAR(CLP_SEQN_FILLER_V); END IF; IF (CLP_CODE_P IS NOT NULL AND CLP_CODE_V IS NULL) OR (CLP_CODE_P IS NULL AND CLP_CODE_V IS NOT NULL) OR (CLP_CODE_P<>CLP_CODE_V) THEN IF CLP_CODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_CODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_CODE_N||CHR(164)||CLP_CODE_V; END IF; END IF; IF (CLP_DESCRIPTION_P IS NOT NULL AND CLP_DESCRIPTION_V IS NULL) OR (CLP_DESCRIPTION_P IS NULL AND CLP_DESCRIPTION_V IS NOT NULL) OR (CLP_DESCRIPTION_P<>CLP_DESCRIPTION_V) THEN IF CLP_DESCRIPTION_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_DESCRIPTION_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_DESCRIPTION_N||CHR(164)||CLP_DESCRIPTION_V; END IF; END IF; IF CLP_SEQU_P<>CLP_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_SEQU_N||CHR(164)||TO_CHAR(CLP_SEQU_V); END IF; IF CLP_MAX_TIME_P<>CLP_MAX_TIME_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_MAX_TIME_N||CHR(164)||TO_CHAR(CLP_MAX_TIME_V); END IF; IF (CLP_LOG_DATE_P IS NOT NULL AND CLP_LOG_DATE_V IS NULL) OR (CLP_LOG_DATE_P IS NULL AND CLP_LOG_DATE_V IS NOT NULL) OR (CLP_LOG_DATE_P<>CLP_LOG_DATE_V) THEN IF CLP_LOG_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_LOG_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_LOG_DATE_N||CHR(164)||TO_CHAR(CLP_LOG_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (CLP_LOG_DETAILS_FILLER_P IS NOT NULL AND CLP_LOG_DETAILS_FILLER_V IS NULL) OR (CLP_LOG_DETAILS_FILLER_P IS NULL AND CLP_LOG_DETAILS_FILLER_V IS NOT NULL) OR (CLP_LOG_DETAILS_FILLER_P<>CLP_LOG_DETAILS_FILLER_V) THEN IF CLP_LOG_DETAILS_FILLER_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_LOG_DETAILS_FILLER_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_LOG_DETAILS_FILLER_N||CHR(164)||CLP_LOG_DETAILS_FILLER_V; END IF; END IF; IF CLP_INACTIVE_P<>CLP_INACTIVE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_INACTIVE_N||CHR(164)||TO_CHAR(CLP_INACTIVE_V); END IF; IF CLP_CA_SEQU_P<>CLP_CA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_CA_SEQU_N||CHR(164)||TO_CHAR(CLP_CA_SEQU_V); END IF; IF (CLP_START_DATE_P IS NOT NULL AND CLP_START_DATE_V IS NULL) OR (CLP_START_DATE_P IS NULL AND CLP_START_DATE_V IS NOT NULL) OR (CLP_START_DATE_P<>CLP_START_DATE_V) THEN IF CLP_START_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_START_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_START_DATE_N||CHR(164)||TO_CHAR(CLP_START_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (CLP_END_DATE_P IS NOT NULL AND CLP_END_DATE_V IS NULL) OR (CLP_END_DATE_P IS NULL AND CLP_END_DATE_V IS NOT NULL) OR (CLP_END_DATE_P<>CLP_END_DATE_V) THEN IF CLP_END_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_END_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_END_DATE_N||CHR(164)||TO_CHAR(CLP_END_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF CLP_OP_TYPE_P<>CLP_OP_TYPE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CLP_OP_TYPE_N||CHR(164)||TO_CHAR(CLP_OP_TYPE_V); END IF; IF LENGTH(LV_AUDIT_TEXT)>0 THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164); LV_AUDIT_TEXT:=SUBSTR(LV_AUDIT_TEXT,2,LENGTH(LV_AUDIT_TEXT)); ELSE LV_AUDIT_TEXT:='NO CHANGE'; END IF; END IF; END IF; INSERT INTO F_AUDIT_TRAIL_ENH(AUDT_SPID,AUDT_APP_USER_NAME,AUDT_USER_ACTION,AUDT_PROGRAM,AUDT_NT_MACHINE_NAME,AUDT_NT_USER_NAME,AUDT_REC_SEQU,AUDT_TABLE,AUDT_PRIMARY_KEY_NAME,AUDT_AUDIT_CHAR,AUDT_DATE_TIME)VALUES(LV_SID_SPID,LV_APP_USER,LV_D_ACTION,LV_PROGRAM_NAME,LV_MACHINENAME,LV_NT_USERNAME,LV_AUDT_REC_SEQU,LV_AUDIT_TABLE,LV_AUDIT_PRIMARY_KEY_NAME,LV_AUDIT_TEXT,TO_DATE(LV_INSERT_DATE_TIME,'DD MON YYYY HH24:MI:SS')); UPDATE F_SEQU SET SQ_COUNT=LV_COUNTER WHERE SQ_TABLE='A_CLINICAL_PRIORITY'; FETCH C_A_CLINICAL_PRIORITY INTO CLP_SEQN_FILLER_V, CLP_CODE_V, CLP_DESCRIPTION_V, CLP_SEQU_V, CLP_MAX_TIME_V, CLP_LOG_DATE_V, CLP_LOG_DETAILS_FILLER_V, CLP_INACTIVE_V, CLP_CA_SEQU_V, CLP_OP_TYPE_V, CLP_START_DATE_V, CLP_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; END LOOP; CLOSE C_A_CLINICAL_PRIORITY; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('NO DATA FOUND'); END; / CREATE OR REPLACE PROCEDURE SP_A_ANAESTHETIC IS ANA_SEQN_FILLER_V NUMBER(9,0); ANA_SEQN_FILLER_P NUMBER(9,0); ANA_SEQN_FILLER_N VARCHAR2(50):='ANA_SEQN_FILLER'; ANA_CODE_V VARCHAR2(6); ANA_CODE_P VARCHAR2(6); ANA_CODE_N VARCHAR2(50):='ANA_CODE'; ANA_DESCRIPTION_V VARCHAR2(60); ANA_DESCRIPTION_P VARCHAR2(60); ANA_DESCRIPTION_N VARCHAR2(50):='ANA_DESCRIPTION'; ANA_SEQU_V NUMBER(9,0); ANA_SEQU_P NUMBER(9,0); ANA_SEQU_N VARCHAR2(50):='ANA_SEQU'; ANA_LOG_DATE_V date; ANA_LOG_DATE_P date; ANA_LOG_DATE_N VARCHAR2(50):='ANA_LOG_DATE'; ANA_LOG_DETAILS_FILLER_V VARCHAR2(1); ANA_LOG_DETAILS_FILLER_P VARCHAR2(1); ANA_LOG_DETAILS_FILLER_N VARCHAR2(50):='ANA_LOG_DETAILS_FILLER'; ANA_INACTIVE_V NUMBER(3,0); ANA_INACTIVE_P NUMBER(3,0); ANA_INACTIVE_N VARCHAR2(50):='ANA_INACTIVE'; ANA_MINIMAL_TIMINGS_V NUMBER(3,0); ANA_MINIMAL_TIMINGS_P NUMBER(3,0); ANA_MINIMAL_TIMINGS_N VARCHAR2(50):='ANA_MINIMAL_TIMINGS'; ANA_CA_SEQU_V NUMBER(9,0); ANA_CA_SEQU_P NUMBER(9,0); ANA_CA_SEQU_N VARCHAR2(50):='ANA_CA_SEQU'; ANA_START_DATE_V date; ANA_START_DATE_P date; ANA_START_DATE_N VARCHAR2(50):='ANA_START_DATE'; ANA_END_DATE_V date; ANA_END_DATE_P date; ANA_END_DATE_N VARCHAR2(50):='ANA_END_DATE'; LV_COUNTER NUMBER(18,0); LV_COUNTER_P NUMBER(18,0); LV_SID_SPID NUMBER(18,0); LV_D_ACTION VARCHAR2(1); LV_INSERT_DATE_TIME DATE; LV_MACHINENAME VARCHAR2(50); LV_APP_USER VARCHAR2(20); LV_PROGRAM_NAME VARCHAR2(50); LV_NT_DOMAIN VARCHAR2(50); LV_NT_USERNAME VARCHAR2(50); LV_LAST_PROCESS_SEQU NUMBER(9,0); LV_AUDIT_TABLE VARCHAR2(50); LV_AUDIT_PRIMARY_KEY_NAME VARCHAR2(50); LV_AUDIT_TEXT VARCHAR2(8000); LV_AUDT_REC_SEQU VARCHAR2(100); CURSOR C_A_ANAESTHETIC IS SELECT ANA_SEQN_FILLER, ANA_CODE, ANA_DESCRIPTION, ANA_SEQU, ANA_LOG_DATE, ANA_LOG_DETAILS_FILLER, ANA_INACTIVE, ANA_MINIMAL_TIMINGS, ANA_CA_SEQU, ANA_START_DATE, ANA_END_DATE, SID_SPID, D_ACTION, INSERT_DATE_TIME, MACHINENAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME, COUNTER FROM A_ANAESTHETIC WHERE COUNTER > (SELECT SQ_COUNT FROM F_SEQU WHERE SQ_TABLE='A_ANAESTHETIC'); BEGIN OPEN C_A_ANAESTHETIC; FETCH C_A_ANAESTHETIC INTO ANA_SEQN_FILLER_V, ANA_CODE_V, ANA_DESCRIPTION_V, ANA_SEQU_V, ANA_LOG_DATE_V, ANA_LOG_DETAILS_FILLER_V, ANA_INACTIVE_V, ANA_MINIMAL_TIMINGS_V, ANA_CA_SEQU_V, ANA_START_DATE_V, ANA_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; WHILE C_A_ANAESTHETIC%FOUND LOOP LV_AUDIT_TEXT:=''; LV_AUDIT_TABLE:='F_Anaesthetic'; LV_AUDIT_PRIMARY_KEY_NAME:='F_Anaesthetic'||'/'||TO_CHAR(ANA_SEQU_V); LV_AUDT_REC_SEQU:=TO_CHAR(ANA_SEQU_V); BEGIN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE((LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND LV_INSERT_DATE_TIME<=AUD_DATE_TIME_EXIT) OR(LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND AUD_DATE_TIME_EXIT IS NULL)) AND AUD_SPID=LV_SID_SPID; EXCEPTION WHEN NO_DATA_FOUND THEN LV_APP_USER:=''; WHEN TOO_MANY_ROWS THEN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE AUD_SPID=LV_SID_SPID AND ROWNUM=1; END; BEGIN SELECT MAX(COUNTER) INTO LV_COUNTER_P FROM A_ANAESTHETIC WHERE ANA_SEQU=ANA_SEQU_V AND COUNTERANA_SEQN_FILLER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_SEQN_FILLER_N||CHR(164)||TO_CHAR(ANA_SEQN_FILLER_V); END IF; IF (ANA_CODE_P IS NOT NULL AND ANA_CODE_V IS NULL) OR (ANA_CODE_P IS NULL AND ANA_CODE_V IS NOT NULL) OR (ANA_CODE_P<>ANA_CODE_V) THEN IF ANA_CODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_CODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_CODE_N||CHR(164)||ANA_CODE_V; END IF; END IF; IF (ANA_DESCRIPTION_P IS NOT NULL AND ANA_DESCRIPTION_V IS NULL) OR (ANA_DESCRIPTION_P IS NULL AND ANA_DESCRIPTION_V IS NOT NULL) OR (ANA_DESCRIPTION_P<>ANA_DESCRIPTION_V) THEN IF ANA_DESCRIPTION_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_DESCRIPTION_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_DESCRIPTION_N||CHR(164)||ANA_DESCRIPTION_V; END IF; END IF; IF ANA_SEQU_P<>ANA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_SEQU_N||CHR(164)||TO_CHAR(ANA_SEQU_V); END IF; IF (ANA_LOG_DATE_P IS NOT NULL AND ANA_LOG_DATE_V IS NULL) OR (ANA_LOG_DATE_P IS NULL AND ANA_LOG_DATE_V IS NOT NULL) OR (ANA_LOG_DATE_P<>ANA_LOG_DATE_V) THEN IF ANA_LOG_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_LOG_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_LOG_DATE_N||CHR(164)||TO_CHAR(ANA_LOG_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (ANA_LOG_DETAILS_FILLER_P IS NOT NULL AND ANA_LOG_DETAILS_FILLER_V IS NULL) OR (ANA_LOG_DETAILS_FILLER_P IS NULL AND ANA_LOG_DETAILS_FILLER_V IS NOT NULL) OR (ANA_LOG_DETAILS_FILLER_P<>ANA_LOG_DETAILS_FILLER_V) THEN IF ANA_LOG_DETAILS_FILLER_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_LOG_DETAILS_FILLER_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_LOG_DETAILS_FILLER_N||CHR(164)||ANA_LOG_DETAILS_FILLER_V; END IF; END IF; IF ANA_INACTIVE_P<>ANA_INACTIVE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_INACTIVE_N||CHR(164)||TO_CHAR(ANA_INACTIVE_V); END IF; IF ANA_MINIMAL_TIMINGS_P<>ANA_MINIMAL_TIMINGS_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_MINIMAL_TIMINGS_N||CHR(164)||TO_CHAR(ANA_MINIMAL_TIMINGS_V); END IF; IF ANA_CA_SEQU_P<>ANA_CA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_CA_SEQU_N||CHR(164)||TO_CHAR(ANA_CA_SEQU_V); END IF; IF (ANA_START_DATE_P IS NOT NULL AND ANA_START_DATE_V IS NULL) OR (ANA_START_DATE_P IS NULL AND ANA_START_DATE_V IS NOT NULL) OR (ANA_START_DATE_P<>ANA_START_DATE_V) THEN IF ANA_START_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_START_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_START_DATE_N||CHR(164)||TO_CHAR(ANA_START_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (ANA_END_DATE_P IS NOT NULL AND ANA_END_DATE_V IS NULL) OR (ANA_END_DATE_P IS NULL AND ANA_END_DATE_V IS NOT NULL) OR (ANA_END_DATE_P<>ANA_END_DATE_V) THEN IF ANA_END_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_END_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ANA_END_DATE_N||CHR(164)||TO_CHAR(ANA_END_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF LENGTH(LV_AUDIT_TEXT)>0 THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164); LV_AUDIT_TEXT:=SUBSTR(LV_AUDIT_TEXT,2,LENGTH(LV_AUDIT_TEXT)); ELSE LV_AUDIT_TEXT:='NO CHANGE'; END IF; END IF; END IF; INSERT INTO F_AUDIT_TRAIL_ENH(AUDT_SPID,AUDT_APP_USER_NAME,AUDT_USER_ACTION,AUDT_PROGRAM,AUDT_NT_MACHINE_NAME,AUDT_NT_USER_NAME,AUDT_REC_SEQU,AUDT_TABLE,AUDT_PRIMARY_KEY_NAME,AUDT_AUDIT_CHAR,AUDT_DATE_TIME)VALUES(LV_SID_SPID,LV_APP_USER,LV_D_ACTION,LV_PROGRAM_NAME,LV_MACHINENAME,LV_NT_USERNAME,LV_AUDT_REC_SEQU,LV_AUDIT_TABLE,LV_AUDIT_PRIMARY_KEY_NAME,LV_AUDIT_TEXT,TO_DATE(LV_INSERT_DATE_TIME,'DD MON YYYY HH24:MI:SS')); UPDATE F_SEQU SET SQ_COUNT=LV_COUNTER WHERE SQ_TABLE='A_ANAESTHETIC'; FETCH C_A_ANAESTHETIC INTO ANA_SEQN_FILLER_V, ANA_CODE_V, ANA_DESCRIPTION_V, ANA_SEQU_V, ANA_LOG_DATE_V, ANA_LOG_DETAILS_FILLER_V, ANA_INACTIVE_V, ANA_MINIMAL_TIMINGS_V, ANA_CA_SEQU_V, ANA_START_DATE_V, ANA_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; END LOOP; CLOSE C_A_ANAESTHETIC; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('NO DATA FOUND'); END; / CREATE OR REPLACE PROCEDURE SP_A_ASA IS ASA_SEQN_FILLER_V NUMBER(9,0); ASA_SEQN_FILLER_P NUMBER(9,0); ASA_SEQN_FILLER_N VARCHAR2(50):='ASA_SEQN_FILLER'; ASA_CODE_V VARCHAR2(2); ASA_CODE_P VARCHAR2(2); ASA_CODE_N VARCHAR2(50):='ASA_CODE'; ASA_DESCRIPTION_V VARCHAR2(255); ASA_DESCRIPTION_P VARCHAR2(255); ASA_DESCRIPTION_N VARCHAR2(50):='ASA_DESCRIPTION'; ASA_SEQU_V NUMBER(9,0); ASA_SEQU_P NUMBER(9,0); ASA_SEQU_N VARCHAR2(50):='ASA_SEQU'; ASA_LOG_DATE_V date; ASA_LOG_DATE_P date; ASA_LOG_DATE_N VARCHAR2(50):='ASA_LOG_DATE'; ASA_LOG_DETAILS_FILLER_V VARCHAR2(1); ASA_LOG_DETAILS_FILLER_P VARCHAR2(1); ASA_LOG_DETAILS_FILLER_N VARCHAR2(50):='ASA_LOG_DETAILS_FILLER'; ASA_INACTIVE_V NUMBER(3,0); ASA_INACTIVE_P NUMBER(3,0); ASA_INACTIVE_N VARCHAR2(50):='ASA_INACTIVE'; ASA_CA_SEQU_V NUMBER(9,0); ASA_CA_SEQU_P NUMBER(9,0); ASA_CA_SEQU_N VARCHAR2(50):='ASA_CA_SEQU'; ASA_START_DATE_V date; ASA_START_DATE_P date; ASA_START_DATE_N VARCHAR2(50):='ASA_START_DATE'; ASA_END_DATE_V date; ASA_END_DATE_P date; ASA_END_DATE_N VARCHAR2(50):='ASA_END_DATE'; LV_COUNTER NUMBER(18,0); LV_COUNTER_P NUMBER(18,0); LV_SID_SPID NUMBER(18,0); LV_D_ACTION VARCHAR2(1); LV_INSERT_DATE_TIME DATE; LV_MACHINENAME VARCHAR2(50); LV_APP_USER VARCHAR2(20); LV_PROGRAM_NAME VARCHAR2(50); LV_NT_DOMAIN VARCHAR2(50); LV_NT_USERNAME VARCHAR2(50); LV_LAST_PROCESS_SEQU NUMBER(9,0); LV_AUDIT_TABLE VARCHAR2(50); LV_AUDIT_PRIMARY_KEY_NAME VARCHAR2(50); LV_AUDIT_TEXT VARCHAR2(8000); LV_AUDT_REC_SEQU VARCHAR2(100); CURSOR C_A_ASA IS SELECT ASA_SEQN_FILLER, ASA_CODE, ASA_DESCRIPTION, ASA_SEQU, ASA_LOG_DATE, ASA_LOG_DETAILS_FILLER, ASA_INACTIVE, ASA_CA_SEQU, ASA_START_DATE, ASA_END_DATE, SID_SPID, D_ACTION, INSERT_DATE_TIME, MACHINENAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME, COUNTER FROM A_ASA WHERE COUNTER > (SELECT SQ_COUNT FROM F_SEQU WHERE SQ_TABLE='A_ASA'); BEGIN OPEN C_A_ASA; FETCH C_A_ASA INTO ASA_SEQN_FILLER_V, ASA_CODE_V, ASA_DESCRIPTION_V, ASA_SEQU_V, ASA_LOG_DATE_V, ASA_LOG_DETAILS_FILLER_V, ASA_INACTIVE_V, ASA_CA_SEQU_V, ASA_START_DATE_V, ASA_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; WHILE C_A_ASA%FOUND LOOP LV_AUDIT_TEXT:=''; LV_AUDIT_TABLE:='F_Asa'; LV_AUDIT_PRIMARY_KEY_NAME:='F_Asa'||'/'||TO_CHAR(ASA_SEQU_V); LV_AUDT_REC_SEQU:=TO_CHAR(ASA_SEQU_V); BEGIN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE((LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND LV_INSERT_DATE_TIME<=AUD_DATE_TIME_EXIT) OR(LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND AUD_DATE_TIME_EXIT IS NULL)) AND AUD_SPID=LV_SID_SPID; EXCEPTION WHEN NO_DATA_FOUND THEN LV_APP_USER:=''; WHEN TOO_MANY_ROWS THEN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE AUD_SPID=LV_SID_SPID AND ROWNUM=1; END; BEGIN SELECT MAX(COUNTER) INTO LV_COUNTER_P FROM A_ASA WHERE ASA_SEQU=ASA_SEQU_V AND COUNTERASA_SEQN_FILLER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_SEQN_FILLER_N||CHR(164)||TO_CHAR(ASA_SEQN_FILLER_V); END IF; IF (ASA_CODE_P IS NOT NULL AND ASA_CODE_V IS NULL) OR (ASA_CODE_P IS NULL AND ASA_CODE_V IS NOT NULL) OR (ASA_CODE_P<>ASA_CODE_V) THEN IF ASA_CODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_CODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_CODE_N||CHR(164)||ASA_CODE_V; END IF; END IF; IF (ASA_DESCRIPTION_P IS NOT NULL AND ASA_DESCRIPTION_V IS NULL) OR (ASA_DESCRIPTION_P IS NULL AND ASA_DESCRIPTION_V IS NOT NULL) OR (ASA_DESCRIPTION_P<>ASA_DESCRIPTION_V) THEN IF ASA_DESCRIPTION_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_DESCRIPTION_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_DESCRIPTION_N||CHR(164)||ASA_DESCRIPTION_V; END IF; END IF; IF ASA_SEQU_P<>ASA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_SEQU_N||CHR(164)||TO_CHAR(ASA_SEQU_V); END IF; IF (ASA_LOG_DATE_P IS NOT NULL AND ASA_LOG_DATE_V IS NULL) OR (ASA_LOG_DATE_P IS NULL AND ASA_LOG_DATE_V IS NOT NULL) OR (ASA_LOG_DATE_P<>ASA_LOG_DATE_V) THEN IF ASA_LOG_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_LOG_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_LOG_DATE_N||CHR(164)||TO_CHAR(ASA_LOG_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (ASA_LOG_DETAILS_FILLER_P IS NOT NULL AND ASA_LOG_DETAILS_FILLER_V IS NULL) OR (ASA_LOG_DETAILS_FILLER_P IS NULL AND ASA_LOG_DETAILS_FILLER_V IS NOT NULL) OR (ASA_LOG_DETAILS_FILLER_P<>ASA_LOG_DETAILS_FILLER_V) THEN IF ASA_LOG_DETAILS_FILLER_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_LOG_DETAILS_FILLER_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_LOG_DETAILS_FILLER_N||CHR(164)||ASA_LOG_DETAILS_FILLER_V; END IF; END IF; IF ASA_INACTIVE_P<>ASA_INACTIVE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_INACTIVE_N||CHR(164)||TO_CHAR(ASA_INACTIVE_V); END IF; IF ASA_CA_SEQU_P<>ASA_CA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_CA_SEQU_N||CHR(164)||TO_CHAR(ASA_CA_SEQU_V); END IF; IF (ASA_START_DATE_P IS NOT NULL AND ASA_START_DATE_V IS NULL) OR (ASA_START_DATE_P IS NULL AND ASA_START_DATE_V IS NOT NULL) OR (ASA_START_DATE_P<>ASA_START_DATE_V) THEN IF ASA_START_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_START_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_START_DATE_N||CHR(164)||TO_CHAR(ASA_START_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (ASA_END_DATE_P IS NOT NULL AND ASA_END_DATE_V IS NULL) OR (ASA_END_DATE_P IS NULL AND ASA_END_DATE_V IS NOT NULL) OR (ASA_END_DATE_P<>ASA_END_DATE_V) THEN IF ASA_END_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_END_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||ASA_END_DATE_N||CHR(164)||TO_CHAR(ASA_END_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF LENGTH(LV_AUDIT_TEXT)>0 THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164); LV_AUDIT_TEXT:=SUBSTR(LV_AUDIT_TEXT,2,LENGTH(LV_AUDIT_TEXT)); ELSE LV_AUDIT_TEXT:='NO CHANGE'; END IF; END IF; END IF; INSERT INTO F_AUDIT_TRAIL_ENH(AUDT_SPID,AUDT_APP_USER_NAME,AUDT_USER_ACTION,AUDT_PROGRAM,AUDT_NT_MACHINE_NAME,AUDT_NT_USER_NAME,AUDT_REC_SEQU,AUDT_TABLE,AUDT_PRIMARY_KEY_NAME,AUDT_AUDIT_CHAR,AUDT_DATE_TIME)VALUES(LV_SID_SPID,LV_APP_USER,LV_D_ACTION,LV_PROGRAM_NAME,LV_MACHINENAME,LV_NT_USERNAME,LV_AUDT_REC_SEQU,LV_AUDIT_TABLE,LV_AUDIT_PRIMARY_KEY_NAME,LV_AUDIT_TEXT,TO_DATE(LV_INSERT_DATE_TIME,'DD MON YYYY HH24:MI:SS')); UPDATE F_SEQU SET SQ_COUNT=LV_COUNTER WHERE SQ_TABLE='A_ASA'; FETCH C_A_ASA INTO ASA_SEQN_FILLER_V, ASA_CODE_V, ASA_DESCRIPTION_V, ASA_SEQU_V, ASA_LOG_DATE_V, ASA_LOG_DETAILS_FILLER_V, ASA_INACTIVE_V, ASA_CA_SEQU_V, ASA_START_DATE_V, ASA_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; END LOOP; CLOSE C_A_ASA; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('NO DATA FOUND'); END; / CREATE OR REPLACE PROCEDURE SP_ACOMPLICAT IS CAM_SEQN_FILLER_V NUMBER(9,0); CAM_SEQN_FILLER_P NUMBER(9,0); CAM_SEQN_FILLER_N VARCHAR2(50):='CAM_SEQN_FILLER'; CAM_CODE_V VARCHAR2(5); CAM_CODE_P VARCHAR2(5); CAM_CODE_N VARCHAR2(50):='CAM_CODE'; CAM_DESC_V VARCHAR2(60); CAM_DESC_P VARCHAR2(60); CAM_DESC_N VARCHAR2(50):='CAM_DESC'; CAM_MASEQ_V NUMBER(9,0); CAM_MASEQ_P NUMBER(9,0); CAM_MASEQ_N VARCHAR2(50):='CAM_MASEQ'; CAM_SEQU_V NUMBER(9,0); CAM_SEQU_P NUMBER(9,0); CAM_SEQU_N VARCHAR2(50):='CAM_SEQU'; CAM_LOG_DATE_V date; CAM_LOG_DATE_P date; CAM_LOG_DATE_N VARCHAR2(50):='CAM_LOG_DATE'; CAM_LOG_DETAILS_FILLER_V VARCHAR2(1); CAM_LOG_DETAILS_FILLER_P VARCHAR2(1); CAM_LOG_DETAILS_FILLER_N VARCHAR2(50):='CAM_LOG_DETAILS_FILLER'; CAM_MA_SEQU_V NUMBER(9,0); CAM_MA_SEQU_P NUMBER(9,0); CAM_MA_SEQU_N VARCHAR2(50):='CAM_MA_SEQU'; CAM_INACTIVE_V NUMBER(3,0); CAM_INACTIVE_P NUMBER(3,0); CAM_INACTIVE_N VARCHAR2(50):='CAM_INACTIVE'; CAM_CA_SEQU_V NUMBER(9,0); CAM_CA_SEQU_P NUMBER(9,0); CAM_CA_SEQU_N VARCHAR2(50):='CAM_CA_SEQU'; CAM_START_DATE_V date; CAM_START_DATE_P date; CAM_START_DATE_N VARCHAR2(50):='CAM_START_DATE'; CAM_END_DATE_V date; CAM_END_DATE_P date; CAM_END_DATE_N VARCHAR2(50):='CAM_END_DATE'; LV_COUNTER NUMBER(18,0); LV_COUNTER_P NUMBER(18,0); LV_SID_SPID NUMBER(18,0); LV_D_ACTION VARCHAR2(1); LV_INSERT_DATE_TIME DATE; LV_MACHINENAME VARCHAR2(50); LV_APP_USER VARCHAR2(20); LV_PROGRAM_NAME VARCHAR2(50); LV_NT_DOMAIN VARCHAR2(50); LV_NT_USERNAME VARCHAR2(50); LV_LAST_PROCESS_SEQU NUMBER(9,0); LV_AUDIT_TABLE VARCHAR2(50); LV_AUDIT_PRIMARY_KEY_NAME VARCHAR2(50); LV_AUDIT_TEXT VARCHAR2(8000); LV_AUDT_REC_SEQU VARCHAR2(100); CURSOR C_ACOMPLICAT IS SELECT CAM_SEQN_FILLER, CAM_CODE, CAM_DESC, CAM_MASEQ, CAM_SEQU, CAM_LOG_DATE, CAM_LOG_DETAILS_FILLER, CAM_MA_SEQU, CAM_INACTIVE, CAM_CA_SEQU, CAM_START_DATE, CAM_END_DATE, SID_SPID, D_ACTION, INSERT_DATE_TIME, MACHINENAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME, COUNTER FROM ACOMPLICAT WHERE COUNTER > (SELECT SQ_COUNT FROM F_SEQU WHERE SQ_TABLE='ACOMPLICAT'); BEGIN OPEN C_ACOMPLICAT; FETCH C_ACOMPLICAT INTO CAM_SEQN_FILLER_V, CAM_CODE_V, CAM_DESC_V, CAM_MASEQ_V, CAM_SEQU_V, CAM_LOG_DATE_V, CAM_LOG_DETAILS_FILLER_V, CAM_MA_SEQU_V, CAM_INACTIVE_V, CAM_CA_SEQU_V, CAM_START_DATE_V, CAM_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; WHILE C_ACOMPLICAT%FOUND LOOP LV_AUDIT_TEXT:=''; LV_AUDIT_TABLE:='FCOMPLICAT'; LV_AUDIT_PRIMARY_KEY_NAME:='FCOMPLICAT'||'/'||TO_CHAR(CAM_SEQU_V); LV_AUDT_REC_SEQU:=TO_CHAR(CAM_SEQU_V); BEGIN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE((LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND LV_INSERT_DATE_TIME<=AUD_DATE_TIME_EXIT) OR(LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND AUD_DATE_TIME_EXIT IS NULL)) AND AUD_SPID=LV_SID_SPID; EXCEPTION WHEN NO_DATA_FOUND THEN LV_APP_USER:=''; WHEN TOO_MANY_ROWS THEN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE AUD_SPID=LV_SID_SPID AND ROWNUM=1; END; BEGIN SELECT MAX(COUNTER) INTO LV_COUNTER_P FROM ACOMPLICAT WHERE CAM_SEQU=CAM_SEQU_V AND COUNTERCAM_SEQN_FILLER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_SEQN_FILLER_N||CHR(164)||TO_CHAR(CAM_SEQN_FILLER_V); END IF; IF (CAM_CODE_P IS NOT NULL AND CAM_CODE_V IS NULL) OR (CAM_CODE_P IS NULL AND CAM_CODE_V IS NOT NULL) OR (CAM_CODE_P<>CAM_CODE_V) THEN IF CAM_CODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_CODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_CODE_N||CHR(164)||CAM_CODE_V; END IF; END IF; IF (CAM_DESC_P IS NOT NULL AND CAM_DESC_V IS NULL) OR (CAM_DESC_P IS NULL AND CAM_DESC_V IS NOT NULL) OR (CAM_DESC_P<>CAM_DESC_V) THEN IF CAM_DESC_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_DESC_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_DESC_N||CHR(164)||CAM_DESC_V; END IF; END IF; IF CAM_MASEQ_P<>CAM_MASEQ_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_MASEQ_N||CHR(164)||TO_CHAR(CAM_MASEQ_V); END IF; IF CAM_SEQU_P<>CAM_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_SEQU_N||CHR(164)||TO_CHAR(CAM_SEQU_V); END IF; IF (CAM_LOG_DATE_P IS NOT NULL AND CAM_LOG_DATE_V IS NULL) OR (CAM_LOG_DATE_P IS NULL AND CAM_LOG_DATE_V IS NOT NULL) OR (CAM_LOG_DATE_P<>CAM_LOG_DATE_V) THEN IF CAM_LOG_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_LOG_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_LOG_DATE_N||CHR(164)||TO_CHAR(CAM_LOG_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (CAM_LOG_DETAILS_FILLER_P IS NOT NULL AND CAM_LOG_DETAILS_FILLER_V IS NULL) OR (CAM_LOG_DETAILS_FILLER_P IS NULL AND CAM_LOG_DETAILS_FILLER_V IS NOT NULL) OR (CAM_LOG_DETAILS_FILLER_P<>CAM_LOG_DETAILS_FILLER_V) THEN IF CAM_LOG_DETAILS_FILLER_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_LOG_DETAILS_FILLER_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_LOG_DETAILS_FILLER_N||CHR(164)||CAM_LOG_DETAILS_FILLER_V; END IF; END IF; IF CAM_MA_SEQU_P<>CAM_MA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_MA_SEQU_N||CHR(164)||TO_CHAR(CAM_MA_SEQU_V); END IF; IF CAM_INACTIVE_P<>CAM_INACTIVE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_INACTIVE_N||CHR(164)||TO_CHAR(CAM_INACTIVE_V); END IF; IF CAM_CA_SEQU_P<>CAM_CA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_CA_SEQU_N||CHR(164)||TO_CHAR(CAM_CA_SEQU_V); END IF; IF (CAM_START_DATE_P IS NOT NULL AND CAM_START_DATE_V IS NULL) OR (CAM_START_DATE_P IS NULL AND CAM_START_DATE_V IS NOT NULL) OR (CAM_START_DATE_P<>CAM_START_DATE_V) THEN IF CAM_START_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_START_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_START_DATE_N||CHR(164)||TO_CHAR(CAM_START_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (CAM_END_DATE_P IS NOT NULL AND CAM_END_DATE_V IS NULL) OR (CAM_END_DATE_P IS NULL AND CAM_END_DATE_V IS NOT NULL) OR (CAM_END_DATE_P<>CAM_END_DATE_V) THEN IF CAM_END_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_END_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||CAM_END_DATE_N||CHR(164)||TO_CHAR(CAM_END_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF LENGTH(LV_AUDIT_TEXT)>0 THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164); LV_AUDIT_TEXT:=SUBSTR(LV_AUDIT_TEXT,2,LENGTH(LV_AUDIT_TEXT)); ELSE LV_AUDIT_TEXT:='NO CHANGE'; END IF; END IF; END IF; INSERT INTO F_AUDIT_TRAIL_ENH(AUDT_SPID,AUDT_APP_USER_NAME,AUDT_USER_ACTION,AUDT_PROGRAM,AUDT_NT_MACHINE_NAME,AUDT_NT_USER_NAME,AUDT_REC_SEQU,AUDT_TABLE,AUDT_PRIMARY_KEY_NAME,AUDT_AUDIT_CHAR,AUDT_DATE_TIME)VALUES(LV_SID_SPID,LV_APP_USER,LV_D_ACTION,LV_PROGRAM_NAME,LV_MACHINENAME,LV_NT_USERNAME,LV_AUDT_REC_SEQU,LV_AUDIT_TABLE,LV_AUDIT_PRIMARY_KEY_NAME,LV_AUDIT_TEXT,TO_DATE(LV_INSERT_DATE_TIME,'DD MON YYYY HH24:MI:SS')); UPDATE F_SEQU SET SQ_COUNT=LV_COUNTER WHERE SQ_TABLE='ACOMPLICAT'; FETCH C_ACOMPLICAT INTO CAM_SEQN_FILLER_V, CAM_CODE_V, CAM_DESC_V, CAM_MASEQ_V, CAM_SEQU_V, CAM_LOG_DATE_V, CAM_LOG_DETAILS_FILLER_V, CAM_MA_SEQU_V, CAM_INACTIVE_V, CAM_CA_SEQU_V, CAM_START_DATE_V, CAM_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; END LOOP; CLOSE C_ACOMPLICAT; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('NO DATA FOUND'); END; / CREATE OR REPLACE PROCEDURE SP_AMACOMPLIC IS MA_SEQN_FILLER_V NUMBER(9,0); MA_SEQN_FILLER_P NUMBER(9,0); MA_SEQN_FILLER_N VARCHAR2(50):='MA_SEQN_FILLER'; MA_CODE_V VARCHAR2(5); MA_CODE_P VARCHAR2(5); MA_CODE_N VARCHAR2(50):='MA_CODE'; MA_DESC_V VARCHAR2(60); MA_DESC_P VARCHAR2(60); MA_DESC_N VARCHAR2(50):='MA_DESC'; MA_DEFINITION_V VARCHAR2(400); MA_DEFINITION_P VARCHAR2(400); MA_DEFINITION_N VARCHAR2(50):='MA_DEFINITION'; MA_SEQU_V NUMBER(9,0); MA_SEQU_P NUMBER(9,0); MA_SEQU_N VARCHAR2(50):='MA_SEQU'; MA_LOG_DATE_V date; MA_LOG_DATE_P date; MA_LOG_DATE_N VARCHAR2(50):='MA_LOG_DATE'; MA_LOG_DETAILS_FILLER_V VARCHAR2(1); MA_LOG_DETAILS_FILLER_P VARCHAR2(1); MA_LOG_DETAILS_FILLER_N VARCHAR2(50):='MA_LOG_DETAILS_FILLER'; MA_INACTIVE_V NUMBER(3,0); MA_INACTIVE_P NUMBER(3,0); MA_INACTIVE_N VARCHAR2(50):='MA_INACTIVE'; MA_CA_SEQU_V NUMBER(9,0); MA_CA_SEQU_P NUMBER(9,0); MA_CA_SEQU_N VARCHAR2(50):='MA_CA_SEQU'; MA_START_DATE_V date; MA_START_DATE_P date; MA_START_DATE_N VARCHAR2(50):='MA_START_DATE'; MA_END_DATE_V date; MA_END_DATE_P date; MA_END_DATE_N VARCHAR2(50):='MA_END_DATE'; LV_COUNTER NUMBER(18,0); LV_COUNTER_P NUMBER(18,0); LV_SID_SPID NUMBER(18,0); LV_D_ACTION VARCHAR2(1); LV_INSERT_DATE_TIME DATE; LV_MACHINENAME VARCHAR2(50); LV_APP_USER VARCHAR2(20); LV_PROGRAM_NAME VARCHAR2(50); LV_NT_DOMAIN VARCHAR2(50); LV_NT_USERNAME VARCHAR2(50); LV_LAST_PROCESS_SEQU NUMBER(9,0); LV_AUDIT_TABLE VARCHAR2(50); LV_AUDIT_PRIMARY_KEY_NAME VARCHAR2(50); LV_AUDIT_TEXT VARCHAR2(8000); LV_AUDT_REC_SEQU VARCHAR2(100); CURSOR C_AMACOMPLIC IS SELECT MA_SEQN_FILLER, MA_CODE, MA_DESC, MA_DEFINITION, MA_SEQU, MA_LOG_DATE, MA_LOG_DETAILS_FILLER, MA_INACTIVE, MA_CA_SEQU, MA_START_DATE, MA_END_DATE, SID_SPID, D_ACTION, INSERT_DATE_TIME, MACHINENAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME, COUNTER FROM AMACOMPLIC WHERE COUNTER > (SELECT SQ_COUNT FROM F_SEQU WHERE SQ_TABLE='AMACOMPLIC'); BEGIN OPEN C_AMACOMPLIC; FETCH C_AMACOMPLIC INTO MA_SEQN_FILLER_V, MA_CODE_V, MA_DESC_V, MA_DEFINITION_V, MA_SEQU_V, MA_LOG_DATE_V, MA_LOG_DETAILS_FILLER_V, MA_INACTIVE_V, MA_CA_SEQU_V, MA_START_DATE_V, MA_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; WHILE C_AMACOMPLIC%FOUND LOOP LV_AUDIT_TEXT:=''; LV_AUDIT_TABLE:='FMACOMPLIC'; LV_AUDIT_PRIMARY_KEY_NAME:='FMACOMPLIC'||'/'||TO_CHAR(MA_SEQU_V); LV_AUDT_REC_SEQU:=TO_CHAR(MA_SEQU_V); BEGIN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE((LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND LV_INSERT_DATE_TIME<=AUD_DATE_TIME_EXIT) OR(LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND AUD_DATE_TIME_EXIT IS NULL)) AND AUD_SPID=LV_SID_SPID; EXCEPTION WHEN NO_DATA_FOUND THEN LV_APP_USER:=''; WHEN TOO_MANY_ROWS THEN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE AUD_SPID=LV_SID_SPID AND ROWNUM=1; END; BEGIN SELECT MAX(COUNTER) INTO LV_COUNTER_P FROM AMACOMPLIC WHERE MA_SEQU=MA_SEQU_V AND COUNTERMA_SEQN_FILLER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_SEQN_FILLER_N||CHR(164)||TO_CHAR(MA_SEQN_FILLER_V); END IF; IF (MA_CODE_P IS NOT NULL AND MA_CODE_V IS NULL) OR (MA_CODE_P IS NULL AND MA_CODE_V IS NOT NULL) OR (MA_CODE_P<>MA_CODE_V) THEN IF MA_CODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_CODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_CODE_N||CHR(164)||MA_CODE_V; END IF; END IF; IF (MA_DESC_P IS NOT NULL AND MA_DESC_V IS NULL) OR (MA_DESC_P IS NULL AND MA_DESC_V IS NOT NULL) OR (MA_DESC_P<>MA_DESC_V) THEN IF MA_DESC_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_DESC_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_DESC_N||CHR(164)||MA_DESC_V; END IF; END IF; IF (MA_DEFINITION_P IS NOT NULL AND MA_DEFINITION_V IS NULL) OR (MA_DEFINITION_P IS NULL AND MA_DEFINITION_V IS NOT NULL) OR (MA_DEFINITION_P<>MA_DEFINITION_V) THEN IF MA_DEFINITION_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_DEFINITION_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_DEFINITION_N||CHR(164)||MA_DEFINITION_V; END IF; END IF; IF MA_SEQU_P<>MA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_SEQU_N||CHR(164)||TO_CHAR(MA_SEQU_V); END IF; IF (MA_LOG_DATE_P IS NOT NULL AND MA_LOG_DATE_V IS NULL) OR (MA_LOG_DATE_P IS NULL AND MA_LOG_DATE_V IS NOT NULL) OR (MA_LOG_DATE_P<>MA_LOG_DATE_V) THEN IF MA_LOG_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_LOG_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_LOG_DATE_N||CHR(164)||TO_CHAR(MA_LOG_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (MA_LOG_DETAILS_FILLER_P IS NOT NULL AND MA_LOG_DETAILS_FILLER_V IS NULL) OR (MA_LOG_DETAILS_FILLER_P IS NULL AND MA_LOG_DETAILS_FILLER_V IS NOT NULL) OR (MA_LOG_DETAILS_FILLER_P<>MA_LOG_DETAILS_FILLER_V) THEN IF MA_LOG_DETAILS_FILLER_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_LOG_DETAILS_FILLER_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_LOG_DETAILS_FILLER_N||CHR(164)||MA_LOG_DETAILS_FILLER_V; END IF; END IF; IF MA_INACTIVE_P<>MA_INACTIVE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_INACTIVE_N||CHR(164)||TO_CHAR(MA_INACTIVE_V); END IF; IF MA_CA_SEQU_P<>MA_CA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_CA_SEQU_N||CHR(164)||TO_CHAR(MA_CA_SEQU_V); END IF; IF (MA_START_DATE_P IS NOT NULL AND MA_START_DATE_V IS NULL) OR (MA_START_DATE_P IS NULL AND MA_START_DATE_V IS NOT NULL) OR (MA_START_DATE_P<>MA_START_DATE_V) THEN IF MA_START_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_START_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_START_DATE_N||CHR(164)||TO_CHAR(MA_START_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (MA_END_DATE_P IS NOT NULL AND MA_END_DATE_V IS NULL) OR (MA_END_DATE_P IS NULL AND MA_END_DATE_V IS NOT NULL) OR (MA_END_DATE_P<>MA_END_DATE_V) THEN IF MA_END_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_END_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||MA_END_DATE_N||CHR(164)||TO_CHAR(MA_END_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF LENGTH(LV_AUDIT_TEXT)>0 THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164); LV_AUDIT_TEXT:=SUBSTR(LV_AUDIT_TEXT,2,LENGTH(LV_AUDIT_TEXT)); ELSE LV_AUDIT_TEXT:='NO CHANGE'; END IF; END IF; END IF; INSERT INTO F_AUDIT_TRAIL_ENH(AUDT_SPID,AUDT_APP_USER_NAME,AUDT_USER_ACTION,AUDT_PROGRAM,AUDT_NT_MACHINE_NAME,AUDT_NT_USER_NAME,AUDT_REC_SEQU,AUDT_TABLE,AUDT_PRIMARY_KEY_NAME,AUDT_AUDIT_CHAR,AUDT_DATE_TIME)VALUES(LV_SID_SPID,LV_APP_USER,LV_D_ACTION,LV_PROGRAM_NAME,LV_MACHINENAME,LV_NT_USERNAME,LV_AUDT_REC_SEQU,LV_AUDIT_TABLE,LV_AUDIT_PRIMARY_KEY_NAME,LV_AUDIT_TEXT,TO_DATE(LV_INSERT_DATE_TIME,'DD MON YYYY HH24:MI:SS')); UPDATE F_SEQU SET SQ_COUNT=LV_COUNTER WHERE SQ_TABLE='AMACOMPLIC'; FETCH C_AMACOMPLIC INTO MA_SEQN_FILLER_V, MA_CODE_V, MA_DESC_V, MA_DEFINITION_V, MA_SEQU_V, MA_LOG_DATE_V, MA_LOG_DETAILS_FILLER_V, MA_INACTIVE_V, MA_CA_SEQU_V, MA_START_DATE_V, MA_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; END LOOP; CLOSE C_AMACOMPLIC; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('NO DATA FOUND'); END; / CREATE OR REPLACE PROCEDURE SP_A_UNPLANNED IS UP_SEQN_FILLER_V NUMBER(9,0); UP_SEQN_FILLER_P NUMBER(9,0); UP_SEQN_FILLER_N VARCHAR2(50):='UP_SEQN_FILLER'; UP_CODE_V VARCHAR2(5); UP_CODE_P VARCHAR2(5); UP_CODE_N VARCHAR2(50):='UP_CODE'; UP_DESC_V VARCHAR2(60); UP_DESC_P VARCHAR2(60); UP_DESC_N VARCHAR2(50):='UP_DESC'; UP_SEQU_V NUMBER(9,0); UP_SEQU_P NUMBER(9,0); UP_SEQU_N VARCHAR2(50):='UP_SEQU'; UP_LOG_DATE_V date; UP_LOG_DATE_P date; UP_LOG_DATE_N VARCHAR2(50):='UP_LOG_DATE'; UP_LOG_DETAILS_FILLER_V VARCHAR2(1); UP_LOG_DETAILS_FILLER_P VARCHAR2(1); UP_LOG_DETAILS_FILLER_N VARCHAR2(50):='UP_LOG_DETAILS_FILLER'; UP_NOT_SPECIFIED_V NUMBER(3,0); UP_NOT_SPECIFIED_P NUMBER(3,0); UP_NOT_SPECIFIED_N VARCHAR2(50):='UP_NOT_SPECIFIED'; UP_INACTIVE_V NUMBER(3,0); UP_INACTIVE_P NUMBER(3,0); UP_INACTIVE_N VARCHAR2(50):='UP_INACTIVE'; UP_CA_SEQU_V NUMBER(9,0); UP_CA_SEQU_P NUMBER(9,0); UP_CA_SEQU_N VARCHAR2(50):='UP_CA_SEQU'; UP_START_DATE_V date; UP_START_DATE_P date; UP_START_DATE_N VARCHAR2(50):='UP_START_DATE'; UP_END_DATE_V date; UP_END_DATE_P date; UP_END_DATE_N VARCHAR2(50):='UP_END_DATE'; LV_COUNTER NUMBER(18,0); LV_COUNTER_P NUMBER(18,0); LV_SID_SPID NUMBER(18,0); LV_D_ACTION VARCHAR2(1); LV_INSERT_DATE_TIME DATE; LV_MACHINENAME VARCHAR2(50); LV_APP_USER VARCHAR2(20); LV_PROGRAM_NAME VARCHAR2(50); LV_NT_DOMAIN VARCHAR2(50); LV_NT_USERNAME VARCHAR2(50); LV_LAST_PROCESS_SEQU NUMBER(9,0); LV_AUDIT_TABLE VARCHAR2(50); LV_AUDIT_PRIMARY_KEY_NAME VARCHAR2(50); LV_AUDIT_TEXT VARCHAR2(8000); LV_AUDT_REC_SEQU VARCHAR2(100); CURSOR C_A_UNPLANNED IS SELECT UP_SEQN_FILLER, UP_CODE, UP_DESC, UP_SEQU, UP_LOG_DATE, UP_LOG_DETAILS_FILLER, UP_NOT_SPECIFIED, UP_INACTIVE, UP_CA_SEQU, UP_START_DATE, UP_END_DATE, SID_SPID, D_ACTION, INSERT_DATE_TIME, MACHINENAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME, COUNTER FROM A_UNPLANNED WHERE COUNTER > (SELECT SQ_COUNT FROM F_SEQU WHERE SQ_TABLE='A_UNPLANNED'); BEGIN OPEN C_A_UNPLANNED; FETCH C_A_UNPLANNED INTO UP_SEQN_FILLER_V, UP_CODE_V, UP_DESC_V, UP_SEQU_V, UP_LOG_DATE_V, UP_LOG_DETAILS_FILLER_V, UP_NOT_SPECIFIED_V, UP_INACTIVE_V, UP_CA_SEQU_V, UP_START_DATE_V, UP_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; WHILE C_A_UNPLANNED%FOUND LOOP LV_AUDIT_TEXT:=''; LV_AUDIT_TABLE:='F_Unplanned'; LV_AUDIT_PRIMARY_KEY_NAME:='F_Unplanned'||'/'||TO_CHAR(UP_SEQU_V); LV_AUDT_REC_SEQU:=TO_CHAR(UP_SEQU_V); BEGIN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE((LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND LV_INSERT_DATE_TIME<=AUD_DATE_TIME_EXIT) OR(LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND AUD_DATE_TIME_EXIT IS NULL)) AND AUD_SPID=LV_SID_SPID; EXCEPTION WHEN NO_DATA_FOUND THEN LV_APP_USER:=''; WHEN TOO_MANY_ROWS THEN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE AUD_SPID=LV_SID_SPID AND ROWNUM=1; END; BEGIN SELECT MAX(COUNTER) INTO LV_COUNTER_P FROM A_UNPLANNED WHERE UP_SEQU=UP_SEQU_V AND COUNTERUP_SEQN_FILLER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_SEQN_FILLER_N||CHR(164)||TO_CHAR(UP_SEQN_FILLER_V); END IF; IF (UP_CODE_P IS NOT NULL AND UP_CODE_V IS NULL) OR (UP_CODE_P IS NULL AND UP_CODE_V IS NOT NULL) OR (UP_CODE_P<>UP_CODE_V) THEN IF UP_CODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_CODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_CODE_N||CHR(164)||UP_CODE_V; END IF; END IF; IF (UP_DESC_P IS NOT NULL AND UP_DESC_V IS NULL) OR (UP_DESC_P IS NULL AND UP_DESC_V IS NOT NULL) OR (UP_DESC_P<>UP_DESC_V) THEN IF UP_DESC_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_DESC_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_DESC_N||CHR(164)||UP_DESC_V; END IF; END IF; IF UP_SEQU_P<>UP_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_SEQU_N||CHR(164)||TO_CHAR(UP_SEQU_V); END IF; IF (UP_LOG_DATE_P IS NOT NULL AND UP_LOG_DATE_V IS NULL) OR (UP_LOG_DATE_P IS NULL AND UP_LOG_DATE_V IS NOT NULL) OR (UP_LOG_DATE_P<>UP_LOG_DATE_V) THEN IF UP_LOG_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_LOG_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_LOG_DATE_N||CHR(164)||TO_CHAR(UP_LOG_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (UP_LOG_DETAILS_FILLER_P IS NOT NULL AND UP_LOG_DETAILS_FILLER_V IS NULL) OR (UP_LOG_DETAILS_FILLER_P IS NULL AND UP_LOG_DETAILS_FILLER_V IS NOT NULL) OR (UP_LOG_DETAILS_FILLER_P<>UP_LOG_DETAILS_FILLER_V) THEN IF UP_LOG_DETAILS_FILLER_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_LOG_DETAILS_FILLER_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_LOG_DETAILS_FILLER_N||CHR(164)||UP_LOG_DETAILS_FILLER_V; END IF; END IF; IF UP_NOT_SPECIFIED_P<>UP_NOT_SPECIFIED_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_NOT_SPECIFIED_N||CHR(164)||TO_CHAR(UP_NOT_SPECIFIED_V); END IF; IF UP_INACTIVE_P<>UP_INACTIVE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_INACTIVE_N||CHR(164)||TO_CHAR(UP_INACTIVE_V); END IF; IF UP_CA_SEQU_P<>UP_CA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_CA_SEQU_N||CHR(164)||TO_CHAR(UP_CA_SEQU_V); END IF; IF (UP_START_DATE_P IS NOT NULL AND UP_START_DATE_V IS NULL) OR (UP_START_DATE_P IS NULL AND UP_START_DATE_V IS NOT NULL) OR (UP_START_DATE_P<>UP_START_DATE_V) THEN IF UP_START_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_START_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_START_DATE_N||CHR(164)||TO_CHAR(UP_START_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (UP_END_DATE_P IS NOT NULL AND UP_END_DATE_V IS NULL) OR (UP_END_DATE_P IS NULL AND UP_END_DATE_V IS NOT NULL) OR (UP_END_DATE_P<>UP_END_DATE_V) THEN IF UP_END_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_END_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||UP_END_DATE_N||CHR(164)||TO_CHAR(UP_END_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF LENGTH(LV_AUDIT_TEXT)>0 THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164); LV_AUDIT_TEXT:=SUBSTR(LV_AUDIT_TEXT,2,LENGTH(LV_AUDIT_TEXT)); ELSE LV_AUDIT_TEXT:='NO CHANGE'; END IF; END IF; END IF; INSERT INTO F_AUDIT_TRAIL_ENH(AUDT_SPID,AUDT_APP_USER_NAME,AUDT_USER_ACTION,AUDT_PROGRAM,AUDT_NT_MACHINE_NAME,AUDT_NT_USER_NAME,AUDT_REC_SEQU,AUDT_TABLE,AUDT_PRIMARY_KEY_NAME,AUDT_AUDIT_CHAR,AUDT_DATE_TIME)VALUES(LV_SID_SPID,LV_APP_USER,LV_D_ACTION,LV_PROGRAM_NAME,LV_MACHINENAME,LV_NT_USERNAME,LV_AUDT_REC_SEQU,LV_AUDIT_TABLE,LV_AUDIT_PRIMARY_KEY_NAME,LV_AUDIT_TEXT,TO_DATE(LV_INSERT_DATE_TIME,'DD MON YYYY HH24:MI:SS')); UPDATE F_SEQU SET SQ_COUNT=LV_COUNTER WHERE SQ_TABLE='A_UNPLANNED'; FETCH C_A_UNPLANNED INTO UP_SEQN_FILLER_V, UP_CODE_V, UP_DESC_V, UP_SEQU_V, UP_LOG_DATE_V, UP_LOG_DETAILS_FILLER_V, UP_NOT_SPECIFIED_V, UP_INACTIVE_V, UP_CA_SEQU_V, UP_START_DATE_V, UP_END_DATE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; END LOOP; CLOSE C_A_UNPLANNED; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('NO DATA FOUND'); END; / CREATE OR REPLACE PROCEDURE SP_A_INTFCE_SETUP IS IF_SEQN_FILLER_V NUMBER(9,0); IF_SEQN_FILLER_P NUMBER(9,0); IF_SEQN_FILLER_N VARCHAR2(50):='IF_SEQN_FILLER'; IF_SERVER_TYPE_V VARCHAR2(8); IF_SERVER_TYPE_P VARCHAR2(8); IF_SERVER_TYPE_N VARCHAR2(50):='IF_SERVER_TYPE'; IF_HOSTNAME_V VARCHAR2(25); IF_HOSTNAME_P VARCHAR2(25); IF_HOSTNAME_N VARCHAR2(50):='IF_HOSTNAME'; IF_USER_NAME_V VARCHAR2(15); IF_USER_NAME_P VARCHAR2(15); IF_USER_NAME_N VARCHAR2(50):='IF_USER_NAME'; IF_USER_PASSWORD_V VARCHAR2(15); IF_USER_PASSWORD_P VARCHAR2(15); IF_USER_PASSWORD_N VARCHAR2(50):='IF_USER_PASSWORD'; IF_DB_VERION_V VARCHAR2(10); IF_DB_VERION_P VARCHAR2(10); IF_DB_VERION_N VARCHAR2(50):='IF_DB_VERION'; IF_INITIALISATION_V VARCHAR2(100); IF_INITIALISATION_P VARCHAR2(100); IF_INITIALISATION_N VARCHAR2(50):='IF_INITIALISATION'; IF_USE_EXT_PMI_V NUMBER(3,0); IF_USE_EXT_PMI_P NUMBER(3,0); IF_USE_EXT_PMI_N VARCHAR2(50):='IF_USE_EXT_PMI'; IF_HAS_INTFC_MENU_V VARCHAR2(30); IF_HAS_INTFC_MENU_P VARCHAR2(30); IF_HAS_INTFC_MENU_N VARCHAR2(50):='IF_HAS_INTFC_MENU'; IF_HOSPITAL_CODE_V VARCHAR2(10); IF_HOSPITAL_CODE_P VARCHAR2(10); IF_HOSPITAL_CODE_N VARCHAR2(50):='IF_HOSPITAL_CODE'; IF_READ_ONLY_V NUMBER(3,0); IF_READ_ONLY_P NUMBER(3,0); IF_READ_ONLY_N VARCHAR2(50):='IF_READ_ONLY'; IF_OUTPUT_FILE_V VARCHAR2(60); IF_OUTPUT_FILE_P VARCHAR2(60); IF_OUTPUT_FILE_N VARCHAR2(50):='IF_OUTPUT_FILE'; IF_GENERIC_FIELD_1_V NUMBER(9,0); IF_GENERIC_FIELD_1_P NUMBER(9,0); IF_GENERIC_FIELD_1_N VARCHAR2(50):='IF_GENERIC_FIELD_1'; IF_PROMPT_LOGON_V NUMBER(3,0); IF_PROMPT_LOGON_P NUMBER(3,0); IF_PROMPT_LOGON_N VARCHAR2(50):='IF_PROMPT_LOGON'; IF_SITE_FIELD_1_V VARCHAR2(40); IF_SITE_FIELD_1_P VARCHAR2(40); IF_SITE_FIELD_1_N VARCHAR2(50):='IF_SITE_FIELD_1'; IF_SITE_FIELD_2_V VARCHAR2(40); IF_SITE_FIELD_2_P VARCHAR2(40); IF_SITE_FIELD_2_N VARCHAR2(50):='IF_SITE_FIELD_2'; IF_SITE_FIELD_3_V VARCHAR2(40); IF_SITE_FIELD_3_P VARCHAR2(40); IF_SITE_FIELD_3_N VARCHAR2(50):='IF_SITE_FIELD_3'; IF_GENERIC_FIELD2_V NUMBER(3,0); IF_GENERIC_FIELD2_P NUMBER(3,0); IF_GENERIC_FIELD2_N VARCHAR2(50):='IF_GENERIC_FIELD2'; IF_INCLUDE_HAS_NEW_V NUMBER(3,0); IF_INCLUDE_HAS_NEW_P NUMBER(3,0); IF_INCLUDE_HAS_NEW_N VARCHAR2(50):='IF_INCLUDE_HAS_NEW'; IF_SEQU_V NUMBER(9,0); IF_SEQU_P NUMBER(9,0); IF_SEQU_N VARCHAR2(50):='IF_SEQU'; IF_HOSPAS_ATS_UPA_DATE_V date; IF_HOSPAS_ATS_UPA_DATE_P date; IF_HOSPAS_ATS_UPA_DATE_N VARCHAR2(50):='IF_HOSPAS_ATS_UPA_DATE'; IF_HOSPAS_ATS_UPA_STRING_V VARCHAR2(10); IF_HOSPAS_ATS_UPA_STRING_P VARCHAR2(10); IF_HOSPAS_ATS_UPA_STRING_N VARCHAR2(50):='IF_HOSPAS_ATS_UPA_STRING'; IF_GENERIC_FIELD_3_V NUMBER(3,0); IF_GENERIC_FIELD_3_P NUMBER(3,0); IF_GENERIC_FIELD_3_N VARCHAR2(50):='IF_GENERIC_FIELD_3'; IF_GENERIC_FIELD_4_V NUMBER(3,0); IF_GENERIC_FIELD_4_P NUMBER(3,0); IF_GENERIC_FIELD_4_N VARCHAR2(50):='IF_GENERIC_FIELD_4'; IF_GENERIC_FIELD_5_V NUMBER(3,0); IF_GENERIC_FIELD_5_P NUMBER(3,0); IF_GENERIC_FIELD_5_N VARCHAR2(50):='IF_GENERIC_FIELD_5'; IF_CA_SEQU_V NUMBER(9,0); IF_CA_SEQU_P NUMBER(9,0); IF_CA_SEQU_N VARCHAR2(50):='IF_CA_SEQU'; IF_AUTO_PAS_UPDATE_V NUMBER(3,0); IF_AUTO_PAS_UPDATE_P NUMBER(3,0); IF_AUTO_PAS_UPDATE_N VARCHAR2(50):='IF_AUTO_PAS_UPDATE'; IF_DISABLE_INT_PMI_SEARCH_V NUMBER(3,0); IF_DISABLE_INT_PMI_SEARCH_P NUMBER(3,0); IF_DISABLE_INT_PMI_SEARCH_N VARCHAR2(50):='IF_DISABLE_INT_PMI_SEARCH'; IF_ENABLE_CONS_NATIONAL_CODE_V NUMBER(3,0); IF_ENABLE_CONS_NATIONAL_CODE_P NUMBER(3,0); IF_ENABLE_CONS_NATIONAL_CODE_N VARCHAR2(50):='IF_ENABLE_CONS_NATIONAL_CODE'; LV_COUNTER NUMBER(18,0); LV_COUNTER_P NUMBER(18,0); LV_SID_SPID NUMBER(18,0); LV_D_ACTION VARCHAR2(1); LV_INSERT_DATE_TIME DATE; LV_MACHINENAME VARCHAR2(50); LV_APP_USER VARCHAR2(20); LV_PROGRAM_NAME VARCHAR2(50); LV_NT_DOMAIN VARCHAR2(50); LV_NT_USERNAME VARCHAR2(50); LV_LAST_PROCESS_SEQU NUMBER(9,0); LV_AUDIT_TABLE VARCHAR2(50); LV_AUDIT_PRIMARY_KEY_NAME VARCHAR2(50); LV_AUDIT_TEXT VARCHAR2(8000); LV_AUDT_REC_SEQU VARCHAR2(100); CURSOR C_A_INTFCE_SETUP IS SELECT IF_SEQN_FILLER, IF_SERVER_TYPE, IF_HOSTNAME, IF_USER_NAME, IF_USER_PASSWORD, IF_DB_VERION, IF_INITIALISATION, IF_USE_EXT_PMI, IF_HAS_INTFC_MENU, IF_HOSPITAL_CODE, IF_READ_ONLY, IF_OUTPUT_FILE, IF_GENERIC_FIELD_1, IF_PROMPT_LOGON, IF_SITE_FIELD_1, IF_SITE_FIELD_2, IF_SITE_FIELD_3, IF_GENERIC_FIELD2, IF_INCLUDE_HAS_NEW, IF_SEQU, IF_HOSPAS_ATS_UPA_DATE, IF_HOSPAS_ATS_UPA_STRING, IF_GENERIC_FIELD_3, IF_GENERIC_FIELD_4, IF_GENERIC_FIELD_5, IF_CA_SEQU, IF_AUTO_PAS_UPDATE, IF_DISABLE_INT_PMI_SEARCH, IF_ENABLE_CONS_NATIONAL_CODE, SID_SPID, D_ACTION, INSERT_DATE_TIME, MACHINENAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME, COUNTER FROM A_INTFCE_SETUP WHERE COUNTER > (SELECT SQ_COUNT FROM F_SEQU WHERE SQ_TABLE='A_INTFCE_SETUP'); BEGIN OPEN C_A_INTFCE_SETUP; FETCH C_A_INTFCE_SETUP INTO IF_SEQN_FILLER_V, IF_SERVER_TYPE_V, IF_HOSTNAME_V, IF_USER_NAME_V, IF_USER_PASSWORD_V, IF_DB_VERION_V, IF_INITIALISATION_V, IF_USE_EXT_PMI_V, IF_HAS_INTFC_MENU_V, IF_HOSPITAL_CODE_V, IF_READ_ONLY_V, IF_OUTPUT_FILE_V, IF_GENERIC_FIELD_1_V, IF_PROMPT_LOGON_V, IF_SITE_FIELD_1_V, IF_SITE_FIELD_2_V, IF_SITE_FIELD_3_V, IF_GENERIC_FIELD2_V, IF_INCLUDE_HAS_NEW_V, IF_SEQU_V, IF_HOSPAS_ATS_UPA_DATE_V, IF_HOSPAS_ATS_UPA_STRING_V, IF_GENERIC_FIELD_3_V, IF_GENERIC_FIELD_4_V, IF_GENERIC_FIELD_5_V, IF_CA_SEQU_V, IF_AUTO_PAS_UPDATE_V, IF_DISABLE_INT_PMI_SEARCH_V, IF_ENABLE_CONS_NATIONAL_CODE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; WHILE C_A_INTFCE_SETUP%FOUND LOOP LV_AUDIT_TEXT:=''; LV_AUDIT_TABLE:='F_Intfce_Setup'; LV_AUDIT_PRIMARY_KEY_NAME:='F_Intfce_Setup'||'/'||TO_CHAR(IF_SEQU_V); LV_AUDT_REC_SEQU:=TO_CHAR(IF_SEQU_V); BEGIN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE((LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND LV_INSERT_DATE_TIME<=AUD_DATE_TIME_EXIT) OR(LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND AUD_DATE_TIME_EXIT IS NULL)) AND AUD_SPID=LV_SID_SPID; EXCEPTION WHEN NO_DATA_FOUND THEN LV_APP_USER:=''; WHEN TOO_MANY_ROWS THEN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE AUD_SPID=LV_SID_SPID AND ROWNUM=1; END; BEGIN SELECT MAX(COUNTER) INTO LV_COUNTER_P FROM A_INTFCE_SETUP WHERE IF_SEQU=IF_SEQU_V AND COUNTERIF_SEQN_FILLER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_SEQN_FILLER_N||CHR(164)||TO_CHAR(IF_SEQN_FILLER_V); END IF; IF (IF_SERVER_TYPE_P IS NOT NULL AND IF_SERVER_TYPE_V IS NULL) OR (IF_SERVER_TYPE_P IS NULL AND IF_SERVER_TYPE_V IS NOT NULL) OR (IF_SERVER_TYPE_P<>IF_SERVER_TYPE_V) THEN IF IF_SERVER_TYPE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_SERVER_TYPE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_SERVER_TYPE_N||CHR(164)||IF_SERVER_TYPE_V; END IF; END IF; IF (IF_HOSTNAME_P IS NOT NULL AND IF_HOSTNAME_V IS NULL) OR (IF_HOSTNAME_P IS NULL AND IF_HOSTNAME_V IS NOT NULL) OR (IF_HOSTNAME_P<>IF_HOSTNAME_V) THEN IF IF_HOSTNAME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_HOSTNAME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_HOSTNAME_N||CHR(164)||IF_HOSTNAME_V; END IF; END IF; IF (IF_USER_NAME_P IS NOT NULL AND IF_USER_NAME_V IS NULL) OR (IF_USER_NAME_P IS NULL AND IF_USER_NAME_V IS NOT NULL) OR (IF_USER_NAME_P<>IF_USER_NAME_V) THEN IF IF_USER_NAME_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_USER_NAME_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_USER_NAME_N||CHR(164)||IF_USER_NAME_V; END IF; END IF; IF (IF_USER_PASSWORD_P IS NOT NULL AND IF_USER_PASSWORD_V IS NULL) OR (IF_USER_PASSWORD_P IS NULL AND IF_USER_PASSWORD_V IS NOT NULL) OR (IF_USER_PASSWORD_P<>IF_USER_PASSWORD_V) THEN IF IF_USER_PASSWORD_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_USER_PASSWORD_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_USER_PASSWORD_N||CHR(164)||IF_USER_PASSWORD_V; END IF; END IF; IF (IF_DB_VERION_P IS NOT NULL AND IF_DB_VERION_V IS NULL) OR (IF_DB_VERION_P IS NULL AND IF_DB_VERION_V IS NOT NULL) OR (IF_DB_VERION_P<>IF_DB_VERION_V) THEN IF IF_DB_VERION_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_DB_VERION_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_DB_VERION_N||CHR(164)||IF_DB_VERION_V; END IF; END IF; IF (IF_INITIALISATION_P IS NOT NULL AND IF_INITIALISATION_V IS NULL) OR (IF_INITIALISATION_P IS NULL AND IF_INITIALISATION_V IS NOT NULL) OR (IF_INITIALISATION_P<>IF_INITIALISATION_V) THEN IF IF_INITIALISATION_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_INITIALISATION_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_INITIALISATION_N||CHR(164)||IF_INITIALISATION_V; END IF; END IF; IF IF_USE_EXT_PMI_P<>IF_USE_EXT_PMI_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_USE_EXT_PMI_N||CHR(164)||TO_CHAR(IF_USE_EXT_PMI_V); END IF; IF (IF_HAS_INTFC_MENU_P IS NOT NULL AND IF_HAS_INTFC_MENU_V IS NULL) OR (IF_HAS_INTFC_MENU_P IS NULL AND IF_HAS_INTFC_MENU_V IS NOT NULL) OR (IF_HAS_INTFC_MENU_P<>IF_HAS_INTFC_MENU_V) THEN IF IF_HAS_INTFC_MENU_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_HAS_INTFC_MENU_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_HAS_INTFC_MENU_N||CHR(164)||IF_HAS_INTFC_MENU_V; END IF; END IF; IF (IF_HOSPITAL_CODE_P IS NOT NULL AND IF_HOSPITAL_CODE_V IS NULL) OR (IF_HOSPITAL_CODE_P IS NULL AND IF_HOSPITAL_CODE_V IS NOT NULL) OR (IF_HOSPITAL_CODE_P<>IF_HOSPITAL_CODE_V) THEN IF IF_HOSPITAL_CODE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_HOSPITAL_CODE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_HOSPITAL_CODE_N||CHR(164)||IF_HOSPITAL_CODE_V; END IF; END IF; IF IF_READ_ONLY_P<>IF_READ_ONLY_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_READ_ONLY_N||CHR(164)||TO_CHAR(IF_READ_ONLY_V); END IF; IF (IF_OUTPUT_FILE_P IS NOT NULL AND IF_OUTPUT_FILE_V IS NULL) OR (IF_OUTPUT_FILE_P IS NULL AND IF_OUTPUT_FILE_V IS NOT NULL) OR (IF_OUTPUT_FILE_P<>IF_OUTPUT_FILE_V) THEN IF IF_OUTPUT_FILE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_OUTPUT_FILE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_OUTPUT_FILE_N||CHR(164)||IF_OUTPUT_FILE_V; END IF; END IF; IF IF_GENERIC_FIELD_1_P<>IF_GENERIC_FIELD_1_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_GENERIC_FIELD_1_N||CHR(164)||TO_CHAR(IF_GENERIC_FIELD_1_V); END IF; IF IF_PROMPT_LOGON_P<>IF_PROMPT_LOGON_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_PROMPT_LOGON_N||CHR(164)||TO_CHAR(IF_PROMPT_LOGON_V); END IF; IF (IF_SITE_FIELD_1_P IS NOT NULL AND IF_SITE_FIELD_1_V IS NULL) OR (IF_SITE_FIELD_1_P IS NULL AND IF_SITE_FIELD_1_V IS NOT NULL) OR (IF_SITE_FIELD_1_P<>IF_SITE_FIELD_1_V) THEN IF IF_SITE_FIELD_1_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_SITE_FIELD_1_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_SITE_FIELD_1_N||CHR(164)||IF_SITE_FIELD_1_V; END IF; END IF; IF (IF_SITE_FIELD_2_P IS NOT NULL AND IF_SITE_FIELD_2_V IS NULL) OR (IF_SITE_FIELD_2_P IS NULL AND IF_SITE_FIELD_2_V IS NOT NULL) OR (IF_SITE_FIELD_2_P<>IF_SITE_FIELD_2_V) THEN IF IF_SITE_FIELD_2_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_SITE_FIELD_2_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_SITE_FIELD_2_N||CHR(164)||IF_SITE_FIELD_2_V; END IF; END IF; IF (IF_SITE_FIELD_3_P IS NOT NULL AND IF_SITE_FIELD_3_V IS NULL) OR (IF_SITE_FIELD_3_P IS NULL AND IF_SITE_FIELD_3_V IS NOT NULL) OR (IF_SITE_FIELD_3_P<>IF_SITE_FIELD_3_V) THEN IF IF_SITE_FIELD_3_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_SITE_FIELD_3_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_SITE_FIELD_3_N||CHR(164)||IF_SITE_FIELD_3_V; END IF; END IF; IF IF_GENERIC_FIELD2_P<>IF_GENERIC_FIELD2_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_GENERIC_FIELD2_N||CHR(164)||TO_CHAR(IF_GENERIC_FIELD2_V); END IF; IF IF_INCLUDE_HAS_NEW_P<>IF_INCLUDE_HAS_NEW_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_INCLUDE_HAS_NEW_N||CHR(164)||TO_CHAR(IF_INCLUDE_HAS_NEW_V); END IF; IF IF_SEQU_P<>IF_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_SEQU_N||CHR(164)||TO_CHAR(IF_SEQU_V); END IF; IF (IF_HOSPAS_ATS_UPA_DATE_P IS NOT NULL AND IF_HOSPAS_ATS_UPA_DATE_V IS NULL) OR (IF_HOSPAS_ATS_UPA_DATE_P IS NULL AND IF_HOSPAS_ATS_UPA_DATE_V IS NOT NULL) OR (IF_HOSPAS_ATS_UPA_DATE_P<>IF_HOSPAS_ATS_UPA_DATE_V) THEN IF IF_HOSPAS_ATS_UPA_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_HOSPAS_ATS_UPA_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_HOSPAS_ATS_UPA_DATE_N||CHR(164)||TO_CHAR(IF_HOSPAS_ATS_UPA_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (IF_HOSPAS_ATS_UPA_STRING_P IS NOT NULL AND IF_HOSPAS_ATS_UPA_STRING_V IS NULL) OR (IF_HOSPAS_ATS_UPA_STRING_P IS NULL AND IF_HOSPAS_ATS_UPA_STRING_V IS NOT NULL) OR (IF_HOSPAS_ATS_UPA_STRING_P<>IF_HOSPAS_ATS_UPA_STRING_V) THEN IF IF_HOSPAS_ATS_UPA_STRING_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_HOSPAS_ATS_UPA_STRING_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_HOSPAS_ATS_UPA_STRING_N||CHR(164)||IF_HOSPAS_ATS_UPA_STRING_V; END IF; END IF; IF IF_GENERIC_FIELD_3_P<>IF_GENERIC_FIELD_3_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_GENERIC_FIELD_3_N||CHR(164)||TO_CHAR(IF_GENERIC_FIELD_3_V); END IF; IF IF_GENERIC_FIELD_4_P<>IF_GENERIC_FIELD_4_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_GENERIC_FIELD_4_N||CHR(164)||TO_CHAR(IF_GENERIC_FIELD_4_V); END IF; IF IF_GENERIC_FIELD_5_P<>IF_GENERIC_FIELD_5_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_GENERIC_FIELD_5_N||CHR(164)||TO_CHAR(IF_GENERIC_FIELD_5_V); END IF; IF IF_CA_SEQU_P<>IF_CA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_CA_SEQU_N||CHR(164)||TO_CHAR(IF_CA_SEQU_V); END IF; IF IF_AUTO_PAS_UPDATE_P<>IF_AUTO_PAS_UPDATE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_AUTO_PAS_UPDATE_N||CHR(164)||TO_CHAR(IF_AUTO_PAS_UPDATE_V); END IF; IF IF_DISABLE_INT_PMI_SEARCH_P<>IF_DISABLE_INT_PMI_SEARCH_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_DISABLE_INT_PMI_SEARCH_N||CHR(164)||TO_CHAR(IF_DISABLE_INT_PMI_SEARCH_V); END IF; IF IF_ENABLE_CONS_NATIONAL_CODE_P<>IF_ENABLE_CONS_NATIONAL_CODE_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||IF_ENABLE_CONS_NATIONAL_CODE_N||CHR(164)||TO_CHAR(IF_ENABLE_CONS_NATIONAL_CODE_V); END IF; IF LENGTH(LV_AUDIT_TEXT)>0 THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164); LV_AUDIT_TEXT:=SUBSTR(LV_AUDIT_TEXT,2,LENGTH(LV_AUDIT_TEXT)); ELSE LV_AUDIT_TEXT:='NO CHANGE'; END IF; END IF; END IF; INSERT INTO F_AUDIT_TRAIL_ENH(AUDT_SPID,AUDT_APP_USER_NAME,AUDT_USER_ACTION,AUDT_PROGRAM,AUDT_NT_MACHINE_NAME,AUDT_NT_USER_NAME,AUDT_REC_SEQU,AUDT_TABLE,AUDT_PRIMARY_KEY_NAME,AUDT_AUDIT_CHAR,AUDT_DATE_TIME)VALUES(LV_SID_SPID,LV_APP_USER,LV_D_ACTION,LV_PROGRAM_NAME,LV_MACHINENAME,LV_NT_USERNAME,LV_AUDT_REC_SEQU,LV_AUDIT_TABLE,LV_AUDIT_PRIMARY_KEY_NAME,LV_AUDIT_TEXT,TO_DATE(LV_INSERT_DATE_TIME,'DD MON YYYY HH24:MI:SS')); UPDATE F_SEQU SET SQ_COUNT=LV_COUNTER WHERE SQ_TABLE='A_INTFCE_SETUP'; FETCH C_A_INTFCE_SETUP INTO IF_SEQN_FILLER_V, IF_SERVER_TYPE_V, IF_HOSTNAME_V, IF_USER_NAME_V, IF_USER_PASSWORD_V, IF_DB_VERION_V, IF_INITIALISATION_V, IF_USE_EXT_PMI_V, IF_HAS_INTFC_MENU_V, IF_HOSPITAL_CODE_V, IF_READ_ONLY_V, IF_OUTPUT_FILE_V, IF_GENERIC_FIELD_1_V, IF_PROMPT_LOGON_V, IF_SITE_FIELD_1_V, IF_SITE_FIELD_2_V, IF_SITE_FIELD_3_V, IF_GENERIC_FIELD2_V, IF_INCLUDE_HAS_NEW_V, IF_SEQU_V, IF_HOSPAS_ATS_UPA_DATE_V, IF_HOSPAS_ATS_UPA_STRING_V, IF_GENERIC_FIELD_3_V, IF_GENERIC_FIELD_4_V, IF_GENERIC_FIELD_5_V, IF_CA_SEQU_V, IF_AUTO_PAS_UPDATE_V, IF_DISABLE_INT_PMI_SEARCH_V, IF_ENABLE_CONS_NATIONAL_CODE_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; END LOOP; CLOSE C_A_INTFCE_SETUP; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('NO DATA FOUND'); END; / CREATE OR REPLACE PROCEDURE SP_ADIARY IS DI_SEQN_FILLER_V NUMBER(9,0); DI_SEQN_FILLER_P NUMBER(9,0); DI_SEQN_FILLER_N VARCHAR2(50):='DI_SEQN_FILLER'; DI_DATE_V date; DI_DATE_P date; DI_DATE_N VARCHAR2(50):='DI_DATE'; DI_COMMENT_V VARCHAR2(1000); DI_COMMENT_P VARCHAR2(1000); DI_COMMENT_N VARCHAR2(50):='DI_COMMENT'; DI_WHO_V VARCHAR2(5); DI_WHO_P VARCHAR2(5); DI_WHO_N VARCHAR2(50):='DI_WHO'; DI_OR_V VARCHAR2(8); DI_OR_P VARCHAR2(8); DI_OR_N VARCHAR2(50):='DI_OR'; DI_TOPIC_V VARCHAR2(45); DI_TOPIC_P VARCHAR2(45); DI_TOPIC_N VARCHAR2(50):='DI_TOPIC'; DI_TH_SEQU_V NUMBER(9,0); DI_TH_SEQU_P NUMBER(9,0); DI_TH_SEQU_N VARCHAR2(50):='DI_TH_SEQU'; DI_LOG_DATE_V date; DI_LOG_DATE_P date; DI_LOG_DATE_N VARCHAR2(50):='DI_LOG_DATE'; DI_LOG_DETAILS_FILLER_V VARCHAR2(1); DI_LOG_DETAILS_FILLER_P VARCHAR2(1); DI_LOG_DETAILS_FILLER_N VARCHAR2(50):='DI_LOG_DETAILS_FILLER'; DI_SEQU_V NUMBER(9,0); DI_SEQU_P NUMBER(9,0); DI_SEQU_N VARCHAR2(50):='DI_SEQU'; DI_CA_SEQU_V NUMBER(9,0); DI_CA_SEQU_P NUMBER(9,0); DI_CA_SEQU_N VARCHAR2(50):='DI_CA_SEQU'; LV_COUNTER NUMBER(18,0); LV_COUNTER_P NUMBER(18,0); LV_SID_SPID NUMBER(18,0); LV_D_ACTION VARCHAR2(1); LV_INSERT_DATE_TIME DATE; LV_MACHINENAME VARCHAR2(50); LV_APP_USER VARCHAR2(20); LV_PROGRAM_NAME VARCHAR2(50); LV_NT_DOMAIN VARCHAR2(50); LV_NT_USERNAME VARCHAR2(50); LV_LAST_PROCESS_SEQU NUMBER(9,0); LV_AUDIT_TABLE VARCHAR2(50); LV_AUDIT_PRIMARY_KEY_NAME VARCHAR2(100); LV_AUDIT_TEXT VARCHAR2(8000); LV_AUDT_REC_SEQU VARCHAR2(100); CURSOR C_ADIARY IS SELECT DI_SEQN_FILLER, DI_DATE, DI_COMMENT, DI_WHO, DI_OR, DI_TOPIC, DI_TH_SEQU, DI_LOG_DATE, DI_LOG_DETAILS_FILLER, DI_SEQU, DI_CA_SEQU, SID_SPID, D_ACTION, INSERT_DATE_TIME, MACHINENAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME, COUNTER FROM ADIARY WHERE COUNTER > (SELECT SQ_COUNT FROM F_SEQU WHERE SQ_TABLE='ADIARY'); BEGIN OPEN C_ADIARY; FETCH C_ADIARY INTO DI_SEQN_FILLER_V, DI_DATE_V, DI_COMMENT_V, DI_WHO_V, DI_OR_V, DI_TOPIC_V, DI_TH_SEQU_V, DI_LOG_DATE_V, DI_LOG_DETAILS_FILLER_V, DI_SEQU_V, DI_CA_SEQU_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; WHILE C_ADIARY%FOUND LOOP LV_AUDIT_TEXT:=''; LV_AUDIT_TABLE:='FDIARY'; LV_AUDIT_PRIMARY_KEY_NAME:='FDIARY'||'/'||TO_CHAR(DI_SEQU_V); LV_AUDT_REC_SEQU:=TO_CHAR(DI_SEQU_V); BEGIN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE((LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND LV_INSERT_DATE_TIME<=AUD_DATE_TIME_EXIT) OR(LV_INSERT_DATE_TIME>=AUD_DATE_TIME_START AND AUD_DATE_TIME_EXIT IS NULL)) AND AUD_SPID=LV_SID_SPID; EXCEPTION WHEN NO_DATA_FOUND THEN LV_APP_USER:=''; WHEN TOO_MANY_ROWS THEN SELECT AUD_APP_US_NAME INTO LV_APP_USER FROM F_AUDIT_LOGON_OFF WHERE AUD_SPID=LV_SID_SPID AND ROWNUM=1; END; BEGIN SELECT MAX(COUNTER) INTO LV_COUNTER_P FROM ADIARY WHERE DI_SEQU=DI_SEQU_V AND COUNTERDI_SEQN_FILLER_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_SEQN_FILLER_N||CHR(164)||TO_CHAR(DI_SEQN_FILLER_V); END IF; IF (DI_DATE_P IS NOT NULL AND DI_DATE_V IS NULL) OR (DI_DATE_P IS NULL AND DI_DATE_V IS NOT NULL) OR (DI_DATE_P<>DI_DATE_V) THEN IF DI_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_DATE_N||CHR(164)||TO_CHAR(DI_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (DI_COMMENT_P IS NOT NULL AND DI_COMMENT_V IS NULL) OR (DI_COMMENT_P IS NULL AND DI_COMMENT_V IS NOT NULL) OR (DI_COMMENT_P<>DI_COMMENT_V) THEN IF DI_COMMENT_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_COMMENT_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_COMMENT_N||CHR(164)||DI_COMMENT_V; END IF; END IF; IF (DI_WHO_P IS NOT NULL AND DI_WHO_V IS NULL) OR (DI_WHO_P IS NULL AND DI_WHO_V IS NOT NULL) OR (DI_WHO_P<>DI_WHO_V) THEN IF DI_WHO_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_WHO_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_WHO_N||CHR(164)||DI_WHO_V; END IF; END IF; IF (DI_OR_P IS NOT NULL AND DI_OR_V IS NULL) OR (DI_OR_P IS NULL AND DI_OR_V IS NOT NULL) OR (DI_OR_P<>DI_OR_V) THEN IF DI_OR_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_OR_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_OR_N||CHR(164)||DI_OR_V; END IF; END IF; IF (DI_TOPIC_P IS NOT NULL AND DI_TOPIC_V IS NULL) OR (DI_TOPIC_P IS NULL AND DI_TOPIC_V IS NOT NULL) OR (DI_TOPIC_P<>DI_TOPIC_V) THEN IF DI_TOPIC_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_TOPIC_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_TOPIC_N||CHR(164)||DI_TOPIC_V; END IF; END IF; IF DI_TH_SEQU_P<>DI_TH_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_TH_SEQU_N||CHR(164)||TO_CHAR(DI_TH_SEQU_V); END IF; IF (DI_LOG_DATE_P IS NOT NULL AND DI_LOG_DATE_V IS NULL) OR (DI_LOG_DATE_P IS NULL AND DI_LOG_DATE_V IS NOT NULL) OR (DI_LOG_DATE_P<>DI_LOG_DATE_V) THEN IF DI_LOG_DATE_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_LOG_DATE_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_LOG_DATE_N||CHR(164)||TO_CHAR(DI_LOG_DATE_V,'DD MON YYYY HH24:MI:SS'); END IF; END IF; IF (DI_LOG_DETAILS_FILLER_P IS NOT NULL AND DI_LOG_DETAILS_FILLER_V IS NULL) OR (DI_LOG_DETAILS_FILLER_P IS NULL AND DI_LOG_DETAILS_FILLER_V IS NOT NULL) OR (DI_LOG_DETAILS_FILLER_P<>DI_LOG_DETAILS_FILLER_V) THEN IF DI_LOG_DETAILS_FILLER_V IS NULL THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_LOG_DETAILS_FILLER_N||CHR(164); ELSE LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_LOG_DETAILS_FILLER_N||CHR(164)||DI_LOG_DETAILS_FILLER_V; END IF; END IF; IF DI_SEQU_P<>DI_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_SEQU_N||CHR(164)||TO_CHAR(DI_SEQU_V); END IF; IF DI_CA_SEQU_P<>DI_CA_SEQU_V THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164)||DI_CA_SEQU_N||CHR(164)||TO_CHAR(DI_CA_SEQU_V); END IF; IF LENGTH(LV_AUDIT_TEXT)>0 THEN LV_AUDIT_TEXT:=LV_AUDIT_TEXT||CHR(164); LV_AUDIT_TEXT:=SUBSTR(LV_AUDIT_TEXT,2,LENGTH(LV_AUDIT_TEXT)); ELSE LV_AUDIT_TEXT:='NO CHANGE'; END IF; END IF; END IF; INSERT INTO F_AUDIT_TRAIL_ENH(AUDT_SPID,AUDT_APP_USER_NAME,AUDT_USER_ACTION,AUDT_PROGRAM,AUDT_NT_MACHINE_NAME,AUDT_NT_USER_NAME,AUDT_REC_SEQU,AUDT_TABLE,AUDT_PRIMARY_KEY_NAME,AUDT_AUDIT_CHAR,AUDT_DATE_TIME)VALUES(LV_SID_SPID,LV_APP_USER,LV_D_ACTION,LV_PROGRAM_NAME,LV_MACHINENAME,LV_NT_USERNAME,LV_AUDT_REC_SEQU,LV_AUDIT_TABLE,LV_AUDIT_PRIMARY_KEY_NAME,LV_AUDIT_TEXT,LV_INSERT_DATE_TIME); UPDATE F_SEQU SET SQ_COUNT=LV_COUNTER WHERE SQ_TABLE='ADIARY'; FETCH C_ADIARY INTO DI_SEQN_FILLER_V, DI_DATE_V, DI_COMMENT_V, DI_WHO_V, DI_OR_V, DI_TOPIC_V, DI_TH_SEQU_V, DI_LOG_DATE_V, DI_LOG_DETAILS_FILLER_V, DI_SEQU_V, DI_CA_SEQU_V, LV_SID_SPID, LV_D_ACTION, LV_INSERT_DATE_TIME, LV_MACHINENAME, LV_PROGRAM_NAME, LV_NT_DOMAIN, LV_NT_USERNAME, LV_COUNTER; END LOOP; CLOSE C_ADIARY; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('NO DATA FOUND'); END; / /****************************************************/ /* New or modified Triggers */ /****************************************************/ /* A_Waiting_Details Trigger is changed */ CREATE OR REPLACE TRIGGER Aud_D_ASA BEFORE DELETE ON F_ASA REFERENCING OLD AS oldRow FOR EACH ROW BEGIN INSERT INTO A_ASA( ASA_SEQN_FILLER, ASA_CODE, ASA_DESCRIPTION, ASA_SEQU, ASA_LOG_DATE, ASA_LOG_DETAILS_FILLER, ASA_INACTIVE, ASA_CA_SEQU, ASA_START_DATE, ASA_END_DATE, SID_SPID, D_ACTION, INSERT_DATE_TIME, MACHINENAME, PROGRAM_NAME, NT_DOMAIN, NT_USERNAME ) VALUES( :oldRow.ASA_SEQN_FILLER, :oldRow.ASA_CODE, :oldRow.ASA_DESCRIPTION, :oldRow.ASA_SEQU, :oldRow.ASA_LOG_DATE, :oldRow.ASA_LOG_DETAILS_FILLER, :oldRow.ASA_INACTIVE, :oldRow.ASA_CA_SEQU, :oldRow.ASA_START_DATE, :oldRow.ASA_END_DATE, (SELECT DISTINCT(SID) FROM SYS.V_$MYSTAT), 'D', (SELECT SYSDATE FROM DUAL), (SELECT RTRIM(SUBSTR(TERMINAL,1,50)) FROM SYS.V_$SESSION WHERE SID = (SELECT DISTINCT(SID) FROM SYS.V_$MYSTAT)), (SELECT RTRIM(SUBSTR(PROGRAM,1,50)) FROM SYS.V_$SESSION WHERE SID = (SELECT DISTINCT(SID) FROM SYS.V_$MYSTAT)), (SELECT RTRIM(SUBSTR(MACHINE,1,50))FROM SYS.V_$SESSION WHERE SID = (SELECT DISTINCT(SID) FROM SYS.V_$MYSTAT)), (SELECT DECODE(INSTR(OSUSER,'\',1),0,RTRIM(OSUSER),SUBSTR(OSUSER,(LENGTH(OSUSER)- INSTR(REVERSE(OSUSER),'\',1,1))+2,LENGTH(OSUSER))) FROM SYS.V_$SESSION WHERE SID = (SELECT DISTINCT(SID) FROM SYS.V_$MYSTAT)) ); END; / /****************************************************/ /* New or modified Views */ /****************************************************/ /****************************************************/ /* New Indexes */ /****************************************************/ set serveroutput on size 1000000 declare cn number ; begin select count(*) into cn from user_indexes where index_name = 'IDX_OP_IN_SUITE_DATE_TIME' ; if cn <> 0 then dbms_output.put_line ('Index IDX_OP_IN_SUITE_DATE_TIME Exists'); else execute immediate 'create index IDX_OP_IN_SUITE_DATE_TIME on FOPERAT (OP_IN_SUITE_DATE_TIME)' ; end if; select count(*) into cn from user_indexes where index_name = 'IDX_REC_DISCH_DATE_TIME' ; if cn <> 0 then dbms_output.put_line ('Index IDX_REC_DISCH_DATE_TIME Exists'); else execute immediate 'create index IDX_REC_DISCH_DATE_TIME on F_Recovery (REC_DISCH_DATE_TIME)' ; end if; end ; / /****************************************************/ /* Miscellaneous SQL statements */ /****************************************************/ CREATE OR REPLACE PROCEDURE SP_EXECUTE_AUDIT_SUMM IS BEGIN SP_ACANCEL; SP_ACCITEMS; SP_ACDOPER; SP_AS1SPEC; SP_ASCAT; SP_ASITEMS; SP_ASPEC; SP_ASURGN; SP_ATHEAT; SP_ATIMETB; SP_AUNIT; SP_A_SUPPHISTORY; SP_A_SUPPINVITEMS; SP_A_SUPPLIER; SP_A_SURGEON_PREFERENCE; SP_A_SURGTEAMEXTRA; END; / /****************************************************/ commit ;