create or replace TRIGGER "PIDS_ADMIN_PH2"."A_CLINIC_PRODUCT_LOCN_LOG" AFTER INSERT OR DELETE OR UPDATE ON "T_CLINIC_PRODUCT_BATCH_LOCN" REFERENCING FOR EACH ROW DECLARE
Time_now T_CLINIC_PRODUCT_LOCN_LOG.LOG_TIME%TYPE;
Terminal T_CLINIC_PRODUCT_LOCN_LOG.LOG_USER%TYPE;
NewValue T_CLINIC_PRODUCT_LOCN_LOG.BEFORE%TYPE;
OldValue T_CLINIC_PRODUCT_LOCN_LOG.AFTER%TYPE;
N_KeyVal T_CLINIC_PRODUCT_LOCN_LOG.KEY%TYPE;
O_KeyVal T_CLINIC_PRODUCT_LOCN_LOG.KEY%TYPE;
BEGIN
NewValue := '';
OldValue := '';
Time_now := SYSDATE;
Terminal := USERENV('TERMINAL');
N_KeyVal := 'UUID=' || NVL(:new.UUID, '');
O_KeyVal := 'UUID=' || NVL(:old.UUID, '');
IF INSERTING THEN
NewValue := 'UUID=' || nvl(:new.UUID,'') || ',' || chr(10)
|| 'CLINIC_PRODUCT_BATCH_ID='||nvl(:new.CLINIC_PRODUCT_BATCH_ID,'') || ',' || chr(10)
|| 'PREPACKING_ID='||nvl(:new.PREPACKING_ID,'') || ',' || chr(10)
|| 'LOCATION_ID='||nvl(:new.LOCATION_ID,'') || ',' || chr(10)
|| 'NO_UNITS='||nvl(:new.NO_UNITS,'') || ',' || chr(10)
|| 'NO_UNITS_ON_STOCK_TAKE_DATE='||nvl(:new.NO_UNITS_ON_STOCK_TAKE_DATE,'') || ',' || chr(10)
|| 'STOCK_TAKE_DATE='||nvl(:new.STOCK_TAKE_DATE,'') || ',' || chr(10)
|| 'UPDATED_ID='||nvl(:new.UPDATED_ID,'') || ',' || chr(10)
|| 'UPDATED_DATE='||nvl(:new.UPDATED_DATE,'') || ',' || chr(10)
|| 'CREATED_ID='||nvl(:new.CREATED_ID,'') || ',' || chr(10)
|| 'CREATED_DATE='||nvl(:new.CREATED_DATE,'') || ',' || chr(10)
|| 'ACTIVE_STATUS='||nvl(:new.ACTIVE_STATUS,'') || ',' || chr(10)
;
INSERT INTO T_CLINIC_PRODUCT_LOCN_LOG (LOG_ID, LOG_TABLE, LOG_TIME, LOG_USER, TYPE, BEFORE, AFTER, KEY)
VALUES (S_CLINIC_PRODUCT_LOCN_LOG.NEXTVAL, 'T_CLINIC_PRODUCT_BATCH_LOCN', Time_now, nvl(:new.UPDATED_ID, Terminal), 'I', NULL, SUBSTR(NewValue,1,length(NewValue)-1), N_KeyVal);
END;
