set serveroutput on
DECLARE
 v_patid varchar2(100);
 v_cnt number;
begin
 v_patid := '&1';

-- Remove stale entries from Lab schema
DELETE FROM CHEMRSLT_COMMENT_V
WHERE CHEMRSLT_COMMENT_EVENT_ID IN (
     SELECT CHEMRSLT_COMMENT_EVENT_ID 
       FROM CHEMRSLT_COMMENT_EVENT_V
      WHERE CHEMISTRY_RESULT_ID IN ( SELECT CHEMISTRY_RESULT_ID 
		                               FROM CHEMISTRY_RESULT_V
                                      WHERE LAB_TEST_ID IN ( SELECT LAB_TEST_ID 
			                                                   FROM LAB_TEST_V
                                                              WHERE LAB_TEST_PRM_ID IN ( SELECT LAB_TEST_PRM_ID 
																					       FROM LAB_TEST_PRM_V 
																					      WHERE PATIENT_IDENTITY = v_patid))));

DELETE FROM CHEMRSLT_COMMENT_EVENT_V
WHERE CHEMISTRY_RESULT_ID IN (
     SELECT CHEMISTRY_RESULT_ID 
	   FROM CHEMISTRY_RESULT_V
      WHERE LAB_TEST_ID IN ( SELECT LAB_TEST_ID 
	                           FROM LAB_TEST_V
                              WHERE LAB_TEST_PRM_ID IN ( SELECT LAB_TEST_PRM_ID 
							                               FROM LAB_TEST_PRM_V
														  WHERE PATIENT_IDENTITY = v_patid)));

DELETE FROM LAB_OBSERVATION_METHOD_V
WHERE CHEMISTRY_RESULT_ID IN ( SELECT CHEMISTRY_RESULT_ID 
                                 FROM CHEMISTRY_RESULT_V
                                WHERE LAB_TEST_ID IN ( SELECT LAB_TEST_ID 
								                         FROM LAB_TEST_V
                                                        WHERE LAB_TEST_PRM_ID IN ( SELECT LAB_TEST_PRM_ID 
														                             FROM LAB_TEST_PRM_V
																					WHERE PATIENT_IDENTITY = v_patid)));

DELETE FROM CHEMISTRY_RESULT_V
WHERE LAB_TEST_ID IN ( SELECT LAB_TEST_ID 
                         FROM LAB_TEST_V
                        WHERE LAB_TEST_PRM_ID IN ( SELECT LAB_TEST_PRM_ID 
						                             FROM LAB_TEST_PRM_V
													WHERE PATIENT_IDENTITY = v_patid));

DELETE FROM LAB_COMMENT_V 
WHERE LAB_COMMENT_EVENT_ID IN ( SELECT LAB_COMMENT_EVENT_ID 
                                  FROM LAB_COMMENT_EVENT_V
                                 WHERE LAB_TEST_PRM_ID IN ( SELECT LAB_TEST_PRM_ID 
								                              FROM LAB_TEST_PRM_V
															 WHERE PATIENT_IDENTITY = v_patid));

DELETE FROM LAB_COMMENT_EVENT_V
WHERE LAB_TEST_PRM_ID IN ( SELECT LAB_TEST_PRM_ID 
                             FROM LAB_TEST_PRM_V
							WHERE PATIENT_IDENTITY = v_patid);

DELETE FROM LAB_TEST_V
WHERE LAB_TEST_PRM_ID IN ( SELECT LAB_TEST_PRM_ID 
                             FROM LAB_TEST_PRM_V
							WHERE PATIENT_IDENTITY = v_patid);

DELETE FROM LAB_TEST_PRM_V WHERE PATIENT_IDENTITY = v_patid;

DELETE FROM LAB_PATIENT_VISIT_V
WHERE LAB_PATIENT_VISIT_ID NOT IN ( SELECT LAB_PATIENT_VISIT_ID FROM LAB_TEST_PRM_V );

COMMIT;

END;
/