DECLARE
 v_patid VARCHAR2(100);
 v_cnt number;
BEGIN
 v_patid := '&1';

-- REMOVE STALE ENTRIES FROM OUTPATIENT PHARMACY SCHEMA
DELETE FROM EXPANDED_PATIENT_INSTRUCTION_V
WHERE OUTPATIENT_MED_PRM_ID IN (SELECT OUTPATIENT_MED_PRM_ID 
                                  FROM OUTPATIENT_MED_PRM_V 
								 WHERE PATIENT_IDENTITY = v_patid);

DELETE FROM PHARMACY_INSTRUCTION_V
WHERE OUTPATIENT_MED_PRM_ID IN (SELECT OUTPATIENT_MED_PRM_ID 
                                  FROM OUTPATIENT_MED_PRM_V 
								 WHERE PATIENT_IDENTITY =  v_patid);

DELETE FROM ACTIVITY_LOG_V
WHERE OUTPATIENT_MED_PRM_ID IN (SELECT OUTPATIENT_MED_PRM_ID 
                                  FROM OUTPATIENT_MED_PRM_V 
								 WHERE PATIENT_IDENTITY =  v_patid);

DELETE FROM OTHER_PATIENT_INSTRUCTION_V
WHERE OUTPATIENT_MED_PRM_ID IN (SELECT OUTPATIENT_MED_PRM_ID 
                                  FROM OUTPATIENT_MED_PRM_V 
								 WHERE PATIENT_IDENTITY =  v_patid);

DELETE FROM PATIENT_INSTRUCTION_V
WHERE OUTPATIENT_MED_PRM_ID IN (SELECT OUTPATIENT_MED_PRM_ID 
                                  FROM OUTPATIENT_MED_PRM_V 
								 WHERE PATIENT_IDENTITY =  v_patid);

DELETE FROM MEDICATION_INSTRUCTION_V
WHERE OUTPATIENT_MED_PRM_ID IN (SELECT OUTPATIENT_MED_PRM_ID 
                                  FROM OUTPATIENT_MED_PRM_V 
								 WHERE PATIENT_IDENTITY = v_patid);

DELETE FROM OUTPPHARM_TREATMENT_FACTOR_V
WHERE OUTPATIENT_MED_PRM_ID IN (SELECT OUTPATIENT_MED_PRM_ID 
                                  FROM OUTPATIENT_MED_PRM_V 
								 WHERE PATIENT_IDENTITY = v_patid);

DELETE FROM CANCEL_COMMENT_V
WHERE OUTPATIENT_MED_PRM_ID IN (SELECT OUTPATIENT_MED_PRM_ID 
                                  FROM OUTPATIENT_MED_PRM_V 
								 WHERE PATIENT_IDENTITY = v_patid);

DELETE FROM OUTPPHARM_SIG_V
WHERE OUTPATIENT_MED_PRM_ID IN (SELECT OUTPATIENT_MED_PRM_ID 
                                  FROM OUTPATIENT_MED_PRM_V 
								 WHERE PATIENT_IDENTITY = v_patid);

DELETE FROM CURRENT_PROVIDER_COMMENT_V
WHERE OUTPATIENT_MED_DSP_ID IN (SELECT OUTPATIENT_MED_DSP_ID 
                                  FROM OUTPATIENT_MED_DSP_V
                                 WHERE OUTPATIENT_MED_PRM_ID IN (SELECT OUTPATIENT_MED_PRM_ID 
								                                   FROM OUTPATIENT_MED_PRM_V 
																  WHERE PATIENT_IDENTITY = v_patid));

DELETE FROM DISPENSE_COMMENT_V
WHERE OUTPATIENT_MED_DSP_ID IN (SELECT OUTPATIENT_MED_DSP_ID 
                                  FROM OUTPATIENT_MED_DSP_V
                                 WHERE OUTPATIENT_MED_PRM_ID IN (SELECT OUTPATIENT_MED_PRM_ID 
								                                   FROM OUTPATIENT_MED_PRM_V 
																  WHERE PATIENT_IDENTITY =  v_patid));

DELETE FROM OUTPATIENT_MED_DSP_V 
WHERE OUTPATIENT_MED_PRM_ID IN (SELECT OUTPATIENT_MED_PRM_ID 
                                  FROM OUTPATIENT_MED_PRM_V 
								 WHERE PATIENT_IDENTITY = v_patid);

DELETE FROM OUTPATIENT_MED_PRM_V 
WHERE PATIENT_IDENTITY = v_patid;

COMMIT;

END;
/