[Home] [Help]
272: DELETE /*+ index(jnc) */ FROM JTF_NOTE_CONTEXTS JNC
273: WHERE
274: JTF_NOTE_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
275: JTFNL.JTF_NOTE_ID FROM JTF_NOTE_CONTEXTS JTFNL ,
276: JTF_NOTES_B JTFNB , CSF_DEBRIEF_HEADERS DBFH , JTF_TASK_ASSIGNMENTS TSKA ,
277: JTF_TASKS_B TSKT , JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE
278: TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND
279: TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID AND TMP.OBJECT_TYPE =
280: TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID = TSKA.TASK_ID AND
287: DELETE /*+ index(jnt)*/ FROM JTF_NOTES_TL JNT
288: WHERE
289: JTF_NOTE_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
290: JTFNB.JTF_NOTE_ID FROM JTF_NOTES_B JTFNB ,
291: CSF_DEBRIEF_HEADERS DBFH , JTF_TASK_ASSIGNMENTS TSKA , JTF_TASKS_B TSKT ,
292: JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
293: NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
294: AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
295: TSKA.TASK_ID AND TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID AND
300: DELETE /*+ index(jnb)*/ FROM JTF_NOTES_B JNB
301: WHERE
302: JTF_NOTE_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
303: JTFNB.JTF_NOTE_ID FROM JTF_NOTES_B JTFNB ,
304: CSF_DEBRIEF_HEADERS DBFH , JTF_TASK_ASSIGNMENTS TSKA , JTF_TASKS_B TSKT ,
305: JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
306: NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
307: AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
308: TSKA.TASK_ID AND TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID AND
323: ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10)*/
324: DBFL.DEBRIEF_LINE_ID
325: FROM CSF_DEBRIEF_LINES DBFL ,
326: CSF_DEBRIEF_HEADERS DBFH ,
327: JTF_TASK_ASSIGNMENTS TSKA ,
328: JTF_TASKS_B TSKT ,
329: JTF_OBJECT_PURGE_PARAM_TMP TMP
330: WHERE TMP.PROCESSING_SET_ID = p_processing_set_id
331: AND NVL ( TMP.PURGE_STATUS , 'S' ) <> 'E'
344: DELETE /*+ index(cdh) */ FROM CSF_DEBRIEF_HEADERS CDH
345: WHERE
346: DEBRIEF_HEADER_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
347: DBFH.DEBRIEF_HEADER_ID FROM CSF_DEBRIEF_HEADERS
348: DBFH , JTF_TASK_ASSIGNMENTS TSKA , JTF_TASKS_B TSKT ,
349: JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
350: NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
351: AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
352: TSKA.TASK_ID AND TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID );
608: from
609: cac_sr_object_capacity cac
610: , JTF_TASKS_B tskt
611: , JTF_OBJECT_PURGE_PARAM_TMP tmp
612: , jtf_Task_assignments jtt
613: , jtf_task_statuses_b jtfts
614: WHERE
615: tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
616: nvl(tmp.purge_status, 'S') <> 'E' and
623: nvl(jtfts.completed_flag,'N') = 'Y' or
624: nvl(jtfts.cancelled_flag,'N') = 'Y') and
625: cac.end_date_time < sysdate and
626: not exists (select 1 from
627: jtf_Task_assignments jtts, jtf_task_statuses_b jtsts
628: where
629: jtts.object_capacity_id = cac.object_capacity_id and
630: jtts.task_id <> jtt.task_id and
631: jtt.assignment_status_id = jtfts.task_status_id and
643: SUM(NVL(csf_util_pvt.convert_to_minutes(
644: ta.sched_travel_duration
645: , ta.sched_travel_duration_uom
646: ), 0)) /(24*60)
647: FROM jtf_task_assignments ta
648: WHERE ta.object_capacity_id = cac.object_capacity_id
649: )
650: where cac.start_date_time > sysdate and
651: cac.object_capacity_id in
653: from
654: cac_sr_object_capacity cac
655: , JTF_TASKS_B tskt
656: , JTF_OBJECT_PURGE_PARAM_TMP tmp
657: , jtf_Task_assignments jtt
658: , jtf_task_statuses_b jtfts
659: WHERE
660: tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
661: nvl(tmp.purge_status, 'S') <> 'E' and