--Reemplace el signo # por el ID del proyecto (tabla ASDK_PROJECTS) a eliminar sus casos

DELETE from asdk_hist_modify 
       where HIMO_ACTION_HIST_ID in (Select Achi_id From Asdk_action_hist 
                                     Where ACHI_ITEM_ID in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #
                                                            union all Select Serv_id From Asdk_service_call Where Serv_fl_int_project_id = #
                                                            union all Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #
                                                            union all Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #));
DELETE from asdk_router_hist
       where ROHI_ACTION_HIST_ID in (Select Achi_id From Asdk_action_hist 
                                     Where ACHI_ITEM_ID in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #
                                                            union all Select Serv_id From Asdk_service_call Where Serv_fl_int_project_id = #
                                                            union all Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #
                                                            union all Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #));
DELETE from asdk_action_hist Where ACHI_ITEM_ID in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #
                                                    union all Select Serv_id From Asdk_service_call Where Serv_fl_int_project_id = #
                                                    union all Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #
                                                    union all Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #);

DELETE from ASDK_HIST_FILE_MARKS Where FL_INT_ID_CASE in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #
                                                    union all Select Serv_id From Asdk_service_call Where Serv_fl_int_project_id = #
                                                    union all Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #
                                                    union all Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #);

DELETE from ASDK_HIST_FILES Where hifi_item_id in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #
                                                    union all Select Serv_id From Asdk_service_call Where Serv_fl_int_project_id = #
                                                    union all Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #
                                                    union all Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #);

DELETE from asdk_hist_notes Where HINO_ITEM_ID in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #
                                                   union all Select Serv_id From Asdk_service_call Where Serv_fl_int_project_id = #
                                                   union all Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #
                                                   union all Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #);

DELETE from asdk_rules_executed_hist where CASEID in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #
                                                      union all Select Serv_id From Asdk_service_call Where Serv_fl_int_project_id = #
                                                      union all Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #
                                                      union all Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #);
DELETE from asdk_rules_executed where IDCASO in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #
                                                 union all Select Serv_id From Asdk_service_call Where Serv_fl_int_project_id = #
                                                 union all Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #
                                                 union all Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #);
Delete from ASDK_SURVEY_CASES Where SURV_CASE_ID in (Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #) or SURV_QUESTION_ID in (Select SURV_ID From ASDK_SURVEY_QUESTION Where SURV_PROJECT_ID = #);
Delete from ASDK_SURVEY_CASES Where SURV_CASE_ID in (Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #) or SURV_QUESTION_ID in (Select SURV_ID From ASDK_SURVEY_QUESTION Where SURV_PROJECT_ID = #);
Delete from ASDK_SURVEY_CASES Where SURV_CASE_ID in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #) or SURV_QUESTION_ID in (Select SURV_ID From ASDK_SURVEY_QUESTION Where SURV_PROJECT_ID = #);
Delete from ASDK_SURVEY_CASES Where SURV_CASE_ID in (Select serv_id From Asdk_service_call Where Serv_fl_int_project_id = #) or SURV_QUESTION_ID in (Select SURV_ID From ASDK_SURVEY_QUESTION Where SURV_PROJECT_ID = #);

DELETE FROM ASDK_TIMES_DATA Where tida_case_id in (Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #);
DELETE FROM ASDK_TIMES_DATA Where tida_case_id in (Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #);
DELETE FROM ASDK_TIMES_DATA Where tida_case_id in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #);
DELETE FROM ASDK_TIMES_DATA Where tida_case_id in (Select serv_id From Asdk_service_call Where Serv_fl_int_project_id = #);

DELETE FROM AFW_SURVEY_ANSWERS WHERE Token IN ((SELECT TOKEN FROM AFW_SURVEY_USER WHERE ConceptItemId IN (SELECT serv_id FROM ASDK_SERVICE_CALL WHERE serv_fl_int_project_id = #)
				UNION ALL SELECT TOKEN FROM AFW_SURVEY_USER WHERE ConceptItemId IN (SELECT inci_id FROM ASDK_INCIDENT WHERE inci_fl_int_project_id = #)
				UNION ALL SELECT TOKEN FROM AFW_SURVEY_USER WHERE ConceptItemId IN (SELECT chan_id FROM ASDK_CHANGE WHERE chan_fl_int_project_id = #)
				UNION ALL SELECT TOKEN FROM AFW_SURVEY_USER WHERE ConceptItemId IN (SELECT prob_id FROM ASDK_PROBLEM WHERE prob_fl_int_project_id = #)));

DELETE FROM ASDK_SURVEY_LIST_TOKENS WHERE survey_token IN ((SELECT TOKEN FROM AFW_SURVEY_USER WHERE ConceptItemId IN (SELECT serv_id FROM ASDK_SERVICE_CALL WHERE serv_fl_int_project_id = #)
				UNION ALL SELECT TOKEN FROM AFW_SURVEY_USER WHERE ConceptItemId IN (SELECT inci_id FROM ASDK_INCIDENT WHERE inci_fl_int_project_id = #)
				UNION ALL SELECT TOKEN FROM AFW_SURVEY_USER WHERE ConceptItemId IN (SELECT chan_id FROM ASDK_CHANGE WHERE chan_fl_int_project_id = #)
				UNION ALL SELECT TOKEN FROM AFW_SURVEY_USER WHERE ConceptItemId IN (SELECT prob_id FROM ASDK_PROBLEM WHERE prob_fl_int_project_id = #)));

DELETE FROM AFW_SURVEY_USER WHERE ConceptItemId IN (SELECT serv_id FROM ASDK_SERVICE_CALL WHERE serv_fl_int_project_id = #);
DELETE FROM AFW_SURVEY_USER WHERE ConceptItemId IN (SELECT inci_id FROM ASDK_INCIDENT WHERE inci_fl_int_project_id = #);
DELETE FROM AFW_SURVEY_USER WHERE ConceptItemId IN (SELECT chan_id FROM ASDK_CHANGE WHERE chan_fl_int_project_id = #);
DELETE FROM AFW_SURVEY_USER WHERE ConceptItemId IN (SELECT prob_id FROM ASDK_PROBLEM WHERE prob_fl_int_project_id = #);

DELETE from ASDK_INCIDENT_SERVICE_CALL where inch_serv_id in (Select serv_id From ASDK_SERVICE_CALL Where serv_fl_int_project_id = # /*ID del proyecto*/);
DELETE from ASDK_SERVICE_CALL_ARTICLE where serv_id in (Select serv_id From ASDK_SERVICE_CALL Where serv_fl_int_project_id = # /*ID del proyecto*/);
DELETE from ASDK_SERVICE_CALL_CHANGE where inch_serv_id in (Select serv_id From ASDK_SERVICE_CALL Where serv_fl_int_project_id = # /*ID del proyecto*/);
DELETE from ASDK_SERVICE_CALL_CI where serv_serv_id in (Select serv_id From ASDK_SERVICE_CALL Where serv_fl_int_project_id = # /*ID del proyecto*/);
DELETE from ASDK_SERVICE_CALL_PROBLEM where inpr_serv_id in (Select serv_id From ASDK_SERVICE_CALL Where serv_fl_int_project_id = # /*ID del proyecto*/);
DELETE from ASDK_SERVICE_CALL_SELF where inse_serv_parent_id in (Select serv_id From ASDK_SERVICE_CALL Where serv_fl_int_project_id = # /*ID del proyecto*/);
DELETE from ASDK_SERVICE_CALL_SELF where inse_serv_child_id in (Select serv_id From ASDK_SERVICE_CALL Where serv_fl_int_project_id = # /*ID del proyecto*/);

DELETE from asdk_incident_change where INCH_INCI_ID in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = # /*ID del proyecto*/);
DELETE from asdk_incident_problem where INPR_INCI_ID in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #);
DELETE from asdk_incident_self where INSE_INCI_PARENT_ID in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #);
DELETE from asdk_incident_ci where INCI_INCI_ID in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #);
DELETE from asdk_incident_article where INCI_ID in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #);

DELETE from asdk_problem_change where PRCH_PROB_ID in (Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #);
DELETE from asdk_problem_ci where PRCI_PROB_ID in (Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #);
DELETE from asdk_problem_self where PRSE_PROB_PARENT_ID in (Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #);
DELETE from asdk_problem_article where FL_INT_ID_PROBLEM in (Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #);

DELETE from asdk_change_ci where CHCI_CHAN_ID in (Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #);
DELETE from asdk_change_self where PRCH_CHAN_PARENT_ID in (Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #);
DELETE from asdk_change_task where chan_id in (Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #);
DELETE from asdk_change_article where FL_INT_ID_CHANGE in (Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #);

Delete From ASDK_PRECASE_CHANGE where prin_prec_id in (Select prec_id From ASDK_PRECASE Where prec_project_id = #);
Delete From ASDK_PRECASE_INCIDENT where prin_prec_id in (Select prec_id From ASDK_PRECASE Where prec_project_id = #);
Delete From ASDK_PRECASE_SERV_CALL where prin_prec_id in (Select prec_id From ASDK_PRECASE Where prec_project_id = #);
Delete From ASDK_PRECASE Where prec_project_id = #;

DELETE from asdk_change where CHAN_ID in (Select Chan_id From Asdk_change Where Chan_fl_int_project_id = #);
DELETE from asdk_problem where Prob_id in (Select Prob_id From Asdk_problem Where Prob_fl_int_project_id = #);
DELETE from asdk_incident where Inci_id in (Select Inci_id From Asdk_incident Where Inci_fl_int_project_id = #);
DELETE from ASDK_SERVICECALL_TASK where serv_id in (Select serv_id From Asdk_service_call Where serv_fl_int_project_id = #);
DELETE from ASDK_SERVICE_CALL where SERV_id in (Select serv_id From Asdk_service_call Where serv_fl_int_project_id = #);

UPDATE ASDK_PROJECTS SET fl_int_incident_id = 0 Where fl_int_id = #;
UPDATE ASDK_PROJECTS SET fl_int_problem_id = 0 Where fl_int_id = #;
UPDATE ASDK_PROJECTS SET fl_int_change_id = 0 Where fl_int_id = #;
UPDATE ASDK_PROJECTS SET fl_int_service_call_id = 0 Where fl_int_id = #;

UPDATE ASDK_PROJECT_NEXTID SET fl_int_nextid  = 0 WHERE fl_int_project = #;

/* Si se eliminaron todos los casos de todos los proyectos, ejecutar esta sentencia:
UPDATE CONFIGURACION SET Ultimoconsec = 0;
*/
--Commit; --si es Oracle