eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.CSF_MAINTAIN_GRP SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 70


/* Step 1 : Mark SRs (for the given p_processing_set_id) as NOT a delete candidate
            IF the SR is linked to a CMRO object
*/
    update JTF_OBJECT_PURGE_PARAM_TMP
    set
        purge_status = 'E'
    ,   purge_error_message = 'CSF:CSF_DEBRIEF_PURGE_FAILED'
    where
        processing_set_id = P_PROCESSING_SET_ID and
        object_id in
        (
            select
                lnk.subject_id
            from
                cs_incident_links lnk
            ,   JTF_OBJECT_PURGE_PARAM_TMP tmp
            where
                tmp.object_id = lnk.subject_id and
                lnk.object_type = 'AHL_UMP_EFF' and
                lnk.link_type_id = 6 and
                nvl(tmp.purge_status, 'S') <> 'E' and
                tmp.processing_set_id = P_PROCESSING_SET_ID
        )
        and nvl(purge_status, 'S') <> 'E';

Line: 98


/* Step 2: Mark SRs (for the given p_processing_set_id) as NOT a delete candidate
           IF the SR is linked to mobile FS tasks that CANNOT be deleted
*/
/*
   This is the place where we call the mobile field service task validation API to further
   mark the JTF_OBJECT_PURGE_PARAM_TMP table with a status 'E' for all those SRs which have
   mobile field service tasks that CANNOT be deleted
*/
  csm_sr_purge_pkg.Validate_MobileFSObjects(
      P_API_VERSION               => P_API_VERSION,
      P_INIT_MSG_LIST             => P_INIT_MSG_LIST,
      P_COMMIT                    => P_COMMIT,
      P_PROCESSING_SET_ID         => P_PROCESSING_SET_ID,
      P_OBJECT_TYPE               => P_OBJECT_TYPE,
      X_RETURN_STATUS             => l_return_status,
      X_MSG_COUNT                 => l_msg_count,
      X_MSG_DATA                  => l_msg_data);

Line: 130


/* Step 3: Mark SRs (for the given p_processing_set_id) as NOT a delete candidate
           IF the SR is linked to FS tasks that are in Closed/Completed/Cancelled status
*/
    update JTF_OBJECT_PURGE_PARAM_TMP
    set
        purge_status = 'E'
    ,   purge_error_message = 'CSF:CSF_DEBRIEF_PURGE_FAILED'
    where
        processing_set_id = P_PROCESSING_SET_ID and
        object_id in
        (
            select
                tmp.object_id
            from
                jtf_tasks_b jtftk
            ,   jtf_task_statuses_b jtfts
            ,   jtf_task_types_b jttp
            ,   JTF_OBJECT_PURGE_PARAM_TMP tmp
            where
                tmp.object_id = jtftk.source_object_id and
                tmp.object_type = jtftk.source_object_type_code and
                jtftk.task_status_id = jtfts.task_status_id and
                (nvl(jtfts.closed_flag,'N') <> 'Y' and
                 nvl(jtfts.completed_flag,'N') <> 'Y' and
                 nvl(jtfts.cancelled_flag,'N') <> 'Y') and
                 nvl(tmp.purge_status, 'S') <> 'E' and
                jtftk.task_type_id = jttp.task_type_id and
                jttp.rule = 'DISPATCH' and
                 tmp.processing_set_id = P_PROCESSING_SET_ID
        )
        and nvl(purge_status, 'S') <> 'E';

Line: 210


(( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 CSACTL.SCHED_REQUEST_ID FROM CSF_R_SCHED_REQUESTS
  CSACTL , CSF_R_REQUEST_TASKS CSACC , JTF_TASKS_B TSKT ,
  JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
  NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
  AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
  CSACC.TASK_ID AND CSACC.SCHED_REQUEST_ID = CSACTL.SCHED_REQUEST_ID )
  UNION ALL(SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
  CSACTL.PARENT_REQUEST_ID FROM CSF_R_SCHED_REQUESTS
  CSACTL , CSF_R_REQUEST_TASKS CSACC , JTF_TASKS_B TSKT ,
  JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
  NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
  AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
  CSACC.TASK_ID AND CSACC.SCHED_REQUEST_ID = CSACTL.SCHED_REQUEST_ID));

Line: 251


/* Delete Logic
1      Fetch the incident ids associated to the processing set id (i/p parameter) which are marked for purge
1.1	If no incident ids (for the given processing set id) require purging, Return Success
2	Fetch Field Service tasks belonging to the incident ids which are purge candidates
3	Fetch debrief headers that belong to the fetched tasks
4	Fetch debrief lines that belong to the fetched debrief headers
5	Fetch debrief notes that belong to the fetched debrief headers
6	Fetch parts requirement headers that belong to the fetched tasks
7	Fetch parts requirement lines that belong to the fetched parts requirement headers
8	Fetch parts requirement line details that belong to the fetched parts requirement headers
9	Fetch required skills that are associated to the fetched tasks
10  Fetch Schedule Requests
11  Fetch Resource Results
12  Fetch Messages and Message Tokens
13	Delete contents fetched from step 3 - step 12
14	Return Success if delete operation is successful
15	Return Failure if delete operation fails
*/


/* Step 1 - Delete relevant debrief notes and note contexts */
DELETE /*+ index(jnc) */ FROM JTF_NOTE_CONTEXTS JNC
WHERE
 JTF_NOTE_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 JTFNL.JTF_NOTE_ID FROM JTF_NOTE_CONTEXTS JTFNL ,
  JTF_NOTES_B JTFNB , CSF_DEBRIEF_HEADERS DBFH , JTF_TASK_ASSIGNMENTS TSKA ,
  JTF_TASKS_B TSKT , JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE
  TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND
  TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID AND TMP.OBJECT_TYPE =
  TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID = TSKA.TASK_ID AND
  TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID AND
  DBFH.DEBRIEF_HEADER_ID = JTFNB.SOURCE_OBJECT_ID AND
  JTFNB.SOURCE_OBJECT_CODE = 'SD' AND JTFNB.JTF_NOTE_ID = JTFNL.JTF_NOTE_ID );

Line: 287


DELETE /*+ index(jnt)*/ FROM JTF_NOTES_TL JNT
WHERE
 JTF_NOTE_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 JTFNB.JTF_NOTE_ID FROM JTF_NOTES_B JTFNB ,
  CSF_DEBRIEF_HEADERS DBFH , JTF_TASK_ASSIGNMENTS TSKA , JTF_TASKS_B TSKT ,
  JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
  NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
  AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
  TSKA.TASK_ID AND TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID AND
  DBFH.DEBRIEF_HEADER_ID = JTFNB.SOURCE_OBJECT_ID AND
  JTFNB.SOURCE_OBJECT_CODE = 'SD' );

Line: 300


DELETE /*+ index(jnb)*/ FROM JTF_NOTES_B JNB
WHERE
 JTF_NOTE_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 JTFNB.JTF_NOTE_ID FROM JTF_NOTES_B JTFNB ,
  CSF_DEBRIEF_HEADERS DBFH , JTF_TASK_ASSIGNMENTS TSKA , JTF_TASKS_B TSKT ,
  JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
  NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
  AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
  TSKA.TASK_ID AND TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID AND
  DBFH.DEBRIEF_HEADER_ID = JTFNB.SOURCE_OBJECT_ID AND
  JTFNB.SOURCE_OBJECT_CODE = 'SD' );

Line: 313


FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task notes and note related objs ');

Line: 316


/* Step 2 - Delete relevant debrief lines and debrief line related objects:
   1) CSF_DEBRIEF_LINES
*/

DELETE /*+ index (cdl)*/
FROM CSF_DEBRIEF_LINES CDL
WHERE DEBRIEF_LINE_ID IN
            ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10)*/
              DBFL.DEBRIEF_LINE_ID
              FROM CSF_DEBRIEF_LINES DBFL ,
                   CSF_DEBRIEF_HEADERS DBFH ,
                   JTF_TASK_ASSIGNMENTS TSKA ,
                   JTF_TASKS_B TSKT ,
                   JTF_OBJECT_PURGE_PARAM_TMP TMP
              WHERE TMP.PROCESSING_SET_ID = p_processing_set_id
                AND NVL ( TMP.PURGE_STATUS , 'S' ) <> 'E'
                AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
                AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE
                AND TSKT.TASK_ID = TSKA.TASK_ID
                AND TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID
                AND DBFH.DEBRIEF_HEADER_ID = DBFL.DEBRIEF_HEADER_ID );

Line: 339


FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task debriefed line details ');

Line: 343


/* Step 3 - Delete relevant debrief headers*/
DELETE /*+ index(cdh) */ FROM CSF_DEBRIEF_HEADERS CDH
WHERE
 DEBRIEF_HEADER_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 DBFH.DEBRIEF_HEADER_ID FROM CSF_DEBRIEF_HEADERS
  DBFH , JTF_TASK_ASSIGNMENTS TSKA , JTF_TASKS_B TSKT ,
  JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
  NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
  AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
  TSKA.TASK_ID AND TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID );

Line: 355


FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task debrief header details ');

Line: 358


/* Step 4 - Delete relevant requirement line details */
	DELETE /*+ index(crld) */ FROM CSP_REQ_LINE_DETAILS crld
	WHERE
		req_line_detail_id in
		(
		SELECT /*+ cardinality(TMP, 10) */
			csprdt.req_line_detail_id
		FROM
		    CSP_REQ_LINE_DETAILS csprdt
	    ,	CSP_REQUIREMENT_LINES csprl
 	    ,   CSP_REQUIREMENT_HEADERS csprh
		,   JTF_TASKS_B tskt
		,   JTF_OBJECT_PURGE_PARAM_TMP tmp
		WHERE
		    tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
			nvl(tmp.purge_status, 'S') <> 'E' and
		    tmp.object_id = tskt.source_object_id and
		    tmp.object_type = tskt.source_object_type_code and
		    tskt.task_id = csprh.task_id and
		    csprh.requirement_header_id = csprl.requirement_header_id and
		    csprl.requirement_line_id = csprdt.requirement_line_id
		);

Line: 382


/* Step 5 - Delete relevant requirement lines */
	DELETE /*+ index(crl) */ FROM CSP_REQUIREMENT_LINES crl
	WHERE
		requirement_line_id in
		(
		SELECT /*+ cardinality(TMP, 10) */
			csprl.requirement_line_id
		FROM
		 	CSP_REQUIREMENT_LINES csprl
 	    ,   CSP_REQUIREMENT_HEADERS csprh
		,   JTF_TASKS_B tskt
		,   JTF_OBJECT_PURGE_PARAM_TMP tmp
		WHERE
		    tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
			nvl(tmp.purge_status, 'S') <> 'E' and
		    tmp.object_id = tskt.source_object_id and
		    tmp.object_type = tskt.source_object_type_code and
		    tskt.task_id = csprh.task_id and
		    csprh.requirement_header_id = csprl.requirement_header_id
		);

Line: 405


/* Step 6 - Delete relevant requirement headers */
	DELETE /*+ index(crh) */ FROM CSP_REQUIREMENT_HEADERS crh
	WHERE
		requirement_header_id in
		(
		SELECT /*+ cardinality(TMP, 10) */
			csprh.requirement_header_id
		FROM
 	        CSP_REQUIREMENT_HEADERS csprh
		,   JTF_TASKS_B tskt
		,   JTF_OBJECT_PURGE_PARAM_TMP tmp
		WHERE
		    tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
			nvl(tmp.purge_status, 'S') <> 'E' and
		    tmp.object_id = tskt.source_object_id and
		    tmp.object_type = tskt.source_object_type_code and
		    tskt.task_id = csprh.task_id
		);

Line: 426


FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task requirement headers and lines ');

Line: 429


/* Step 7 - Delete relevant required skills */
DELETE /*+ index(crsb) */ FROM CSF_REQUIRED_SKILLS_B CRSB
WHERE
 REQUIRED_SKILL_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 CSFSK.REQUIRED_SKILL_ID FROM CSF_REQUIRED_SKILLS_B
  CSFSK , JTF_TASKS_B TSKT , JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE
  TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND
  TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID AND TMP.OBJECT_TYPE =
  TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID = CSFSK.HAS_SKILL_ID AND
  CSFSK.HAS_SKILL_TYPE = 'TASK' );

Line: 441


FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task required skills ');

Line: 444


/* Step 8 - Delete Access Hours */
        DELETE /*+ index(caht) */ FROM CSF_ACCESS_HOURS_TL caht
        WHERE
                access_hour_id in
                (
                SELECT /*+ LEADING(TMP) no_unnest no_semijoin cardinality(TMP,
10) */
                        csacc.access_hour_id
                FROM
                CSF_ACCESS_HOURS_B csacc
                ,   JTF_TASKS_B tskt
                ,   JTF_OBJECT_PURGE_PARAM_TMP tmp
                WHERE
                    tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
                        nvl(tmp.purge_status, 'S') <> 'E' and
                    tmp.object_id = tskt.source_object_id and
                    tmp.object_type = tskt.source_object_type_code and
                    tskt.task_id = csacc.task_id
                );

Line: 464


	DELETE /*+ index(cahb) */ FROM CSF_ACCESS_HOURS_B cahb
	WHERE
		task_id in
		(
		SELECT /*+ cardinality(TMP, 10) */
			csacc.task_id
		FROM
 	        CSF_ACCESS_HOURS_B csacc
		,   JTF_TASKS_B tskt
		,   JTF_OBJECT_PURGE_PARAM_TMP tmp
		WHERE
		    tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
			nvl(tmp.purge_status, 'S') <> 'E' and
		    tmp.object_id = tskt.source_object_id and
		    tmp.object_type = tskt.source_object_type_code and
		    tskt.task_id = csacc.task_id
		);

Line: 484


FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task access hours details ');

Line: 488


/* Step 9 - Delete Plan Options */
DELETE /*+ index(crpo) */ FROM CSF_R_PLAN_OPTIONS CRPO
WHERE
 PLAN_OPTION_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 CSACTL.PLAN_OPTION_ID FROM CSF_R_PLAN_OPTIONS
  CSACTL , CSF_R_PLAN_OPTION_TASKS CSACC , JTF_TASKS_B TSKT ,
  JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
  NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
  AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
  CSACC.TASK_ID AND CSACC.PLAN_OPTION_ID = CSACTL.PLAN_OPTION_ID );

Line: 500


DELETE /*+ index(crpot) */ FROM CSF_R_PLAN_OPTION_TASKS CRPOT
WHERE
 TASK_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 CSACC.TASK_ID FROM CSF_R_PLAN_OPTION_TASKS CSACC , JTF_TASKS_B TSKT ,
   JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
  NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
  AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
  CSACC.TASK_ID );

Line: 511


FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task plan options ');

Line: 514


/* Step 10 - Delete Resource Results */
DELETE /*+ index(crso) */ FROM CSF_R_SPARES_OPTIONS CRSO
WHERE
 RESOURCE_RESULT_ID IN (SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 CSACTL.RESOURCE_RESULT_ID FROM CSF_R_SPARES_OPTIONS
  CSACTL , CSF_R_RESOURCE_RESULTS CSRR ,CSF_R_REQUEST_TASKS
  CSACC, JTF_TASKS_B TSKT ,JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE
  TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
  NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
  AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
  CSACC.TASK_ID AND CSACC.REQUEST_TASK_ID=CSRR.REQUEST_TASK_ID
  AND CSRR.RESOURCE_RESULT_ID =CSACTL.RESOURCE_RESULT_ID);

Line: 528


DELETE /*+ index(crrr) */ FROM CSF_R_RESOURCE_RESULTS CRRR
WHERE
 REQUEST_TASK_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 CSACC.REQUEST_TASK_ID FROM CSF_R_RESOURCE_RESULTS CSRR ,CSF_R_REQUEST_TASKS
  CSACC , JTF_TASKS_B TSKT , JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE
  TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND
  TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID AND TMP.OBJECT_TYPE =
  TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID = CSACC.TASK_ID AND
  CSACC.REQUEST_TASK_ID=CSRR.REQUEST_TASK_ID);

Line: 540


FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task resource results ');

Line: 550


/* Step 11 - Delete Messages and Message Tokens */
--FORALL i in l_tab_sched_req_id.first..l_tab_sched_req_id.last
DELETE /*+ index(crmt) */ FROM CSF_R_MESSAGE_TOKENS CRMT
WHERE
 MESSAGE_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 CSACTL.MESSAGE_ID FROM CSF_R_MESSAGE_TOKENS CSACTL ,
   CSF_R_MESSAGES CSRM, CSF_R_REQUEST_TASKS CSACC , CSF_R_SCHED_REQUESTS CSCHR
  WHERE CSCHR.SCHED_REQUEST_ID IN (SELECT T.COLUMN_VALUE FROM TABLE(CAST(L_TAB_SCHED_REQ_ID AS JTF_NUMBER_TABLE)) T) AND
  --WHERE CSCHR.SCHED_REQUEST_ID = l_tab_sched_req_id(i) AND
  CSCHR.SCHED_REQUEST_ID = CSACC.SCHED_REQUEST_ID AND
  CSACC.REQUEST_TASK_ID=CSRM.REQUEST_TASK_ID AND
  CSRM.MESSAGE_ID = CSACTL.MESSAGE_ID );

Line: 564


DELETE /*+ index(crm) */ FROM CSF_R_MESSAGES CRM
WHERE
 REQUEST_TASK_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 CSACC.REQUEST_TASK_ID FROM CSF_R_MESSAGES CSRM ,
 CSF_R_REQUEST_TASKS CSACC , CSF_R_SCHED_REQUESTS CSCHR
  WHERE CSCHR.SCHED_REQUEST_ID IN (SELECT T.COLUMN_VALUE FROM TABLE(CAST(L_TAB_SCHED_REQ_ID AS JTF_NUMBER_TABLE)) T) AND
  --WHERE CSCHR.SCHED_REQUEST_ID = l_tab_sched_req_id(i) AND
  CSCHR.SCHED_REQUEST_ID = CSACC.SCHED_REQUEST_ID AND
  CSACC.REQUEST_TASK_ID=CSRM.REQUEST_TASK_ID);

Line: 575


FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task messages and message tokens ');

Line: 578


/* Step 12 - Delete Request Task */
--FORALL i in l_tab_sched_req_id.first..l_tab_sched_req_id.last
DELETE /*+ index(crrt) */ FROM CSF_R_REQUEST_TASKS CRRT
WHERE
 REQUEST_TASK_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
 CSACC.REQUEST_TASK_ID FROM CSF_R_REQUEST_TASKS
  CSACC , CSF_R_SCHED_REQUESTS CSCHR
  WHERE CSCHR.SCHED_REQUEST_ID IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(l_tab_sched_req_id AS JTF_NUMBER_TABLE)) t) AND
  --WHERE CSCHR.SCHED_REQUEST_ID = l_tab_sched_req_id(i) AND
  CSCHR.SCHED_REQUEST_ID = CSACC.SCHED_REQUEST_ID);

Line: 591


FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task scheduled requests tasks');

Line: 594


/* Step 13 - Delete Schedule Requests */
--FORALL i in l_tab_sched_req_id.first..l_tab_sched_req_id.last
DELETE /*+ index(crsr) */ FROM CSF_R_SCHED_REQUESTS CRSR
WHERE
 SCHED_REQUEST_ID IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(l_tab_sched_req_id AS JTF_NUMBER_TABLE)) t);

Line: 602


FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task scheduled requests ');

Line: 605


delete cac_sr_object_capacity
    where object_capacity_id in
    (select cac.object_capacity_id
        from
        cac_sr_object_capacity cac
        ,   JTF_TASKS_B tskt
        ,   JTF_OBJECT_PURGE_PARAM_TMP tmp
        ,   jtf_Task_assignments jtt
        ,   jtf_task_statuses_b jtfts
        WHERE
            tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
            nvl(tmp.purge_status, 'S') <> 'E' and
            tmp.object_id = tskt.source_object_id and
            tmp.object_type = tskt.source_object_type_code and
            tskt.task_id = jtt.TASK_ID and
            jtt.object_capacity_id = cac.object_capacity_id and
            jtt.assignment_status_id = jtfts.task_status_id  and
            (nvl(jtfts.closed_flag,'N') = 'Y' or
             nvl(jtfts.completed_flag,'N') = 'Y' or
             nvl(jtfts.cancelled_flag,'N') = 'Y') and
			 cac.end_date_time < sysdate and
            not exists (select 1 from
                        jtf_Task_assignments jtts, jtf_task_statuses_b jtsts
                        where
                        jtts.object_capacity_id = cac.object_capacity_id and
                        jtts.task_id <> jtt.task_id and
                        jtt.assignment_status_id = jtfts.task_status_id  and
                        (nvl(jtfts.closed_flag,'N') <> 'Y' and
                         nvl(jtfts.completed_flag,'N') <> 'Y' and
                         nvl(jtfts.cancelled_flag,'N') <> 'Y'))

            );

Line: 640


update cac_sr_object_capacity cac set cac.available_hours =
 ( SELECT (cac.END_DATE_TIME - cac.START_DATE_TIME) -
              SUM(ta.booking_end_date - ta.booking_start_date) -
              SUM(NVL(csf_util_pvt.convert_to_minutes(
                      ta.sched_travel_duration
                     , ta.sched_travel_duration_uom
                      ), 0)) /(24*60)
         FROM jtf_task_assignments ta
         WHERE ta.object_capacity_id = cac.object_capacity_id
  )
 where cac.start_date_time > sysdate and
 cac.object_capacity_id in
 (select cac.object_capacity_id
        from
        cac_sr_object_capacity cac
        ,   JTF_TASKS_B tskt
        ,   JTF_OBJECT_PURGE_PARAM_TMP tmp
        ,   jtf_Task_assignments jtt
        ,   jtf_task_statuses_b jtfts
        WHERE
            tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
            nvl(tmp.purge_status, 'S') <> 'E' and
            tmp.object_id = tskt.source_object_id and
            tmp.object_type = tskt.source_object_type_code and
            tskt.task_id = jtt.TASK_ID and
            jtt.object_capacity_id = cac.object_capacity_id and
            jtt.assignment_status_id = jtfts.task_status_id  and
            (nvl(jtfts.closed_flag,'N') = 'Y' or
             nvl(jtfts.completed_flag,'N') = 'Y' or
             nvl(jtfts.cancelled_flag,'N') = 'Y'));

Line: 672


FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS capacities ');