[Home] [Help]
136: -- Description
137: -- This procedure accepts the above list of parameters to identify the SRs
138: -- that need to be purged. It constructs a WHERE clause out of these
139: -- parameters after validating them. This WHERE clause is appended to a
140: -- query on the table CS_INCIDENTS_ALL_B and result of this query is
141: -- inserted into a staging table CS_INCIDENTS_PURGE_STAGING after which
142: -- the rows are divided among the number of worker concurrent programs
143: -- using a formula 'mod(rownum - 1,
144: -- the child concurrent requests are launched and the SRs are purged. This
156: /*#
157: * This procedure accepts the above list of parameters to identify the SRs that
158: * need to be purged. It constructs a WHERE clause out of these parameters after
159: * validating them. This WHERE clause is appended to a query on the table
160: * CS_INCIDENTS_ALL_B and result of this query is inserted into a staging table
161: * CS_INCIDENTS_PURGE_STAGING after which the rows are divided among the number
162: * of worker concurrent programs using a formula 'mod(rownum - 1,
163: *
164: * launched and the SRs are purged. This procedure waits for all the child
2677: count(*)
2678: INTO
2679: l_sr_rows
2680: FROM
2681: cs_incidents_all_b b
2682: WHERE
2683: NOT EXISTS
2684: (
2685: SELECT
2740: INTO
2741: l_sr_contacts
2742: FROM
2743: cs_hz_sr_contact_points cp
2744: , cs_incidents_all_b b
2745: WHERE
2746: NOT EXISTS
2747: (
2748: SELECT
2804: INTO
2805: l_sr_cont_attrs
2806: FROM
2807: cs_sr_contacts_ext ex
2808: , cs_incidents_all_b b
2809: WHERE
2810: NOT EXISTS
2811: (
2812: SELECT
2870: INTO
2871: l_sr_links
2872: FROM
2873: cs_incident_links l
2874: , cs_incidents_all_b b
2875: WHERE
2876: (
2877: l.subject_id = b.incident_id
2878: AND l.subject_type = 'SR'
2939: INTO
2940: l_sr_msgs
2941: FROM
2942: cs_messages msg
2943: , cs_incidents_all_b b
2944: WHERE
2945: msg.source_object_int_id = b.incident_id
2946: AND msg.source_object_type_code = 'INC'
2947: AND NOT EXISTS
3004: INTO
3005: l_sr_kb_links
3006: FROM
3007: cs_kb_set_links k
3008: , cs_incidents_all_b b
3009: WHERE
3010: k.object_code = 'SR'
3011: AND k.other_id = b.incident_id
3012: AND b.status_flag = 'C'
3069: INTO
3070: l_sr_estimates
3071: FROM
3072: cs_estimate_details es
3073: , cs_incidents_all_b b
3074: WHERE
3075: b.incident_id = es.incident_id
3076: AND b.status_flag = 'C'
3077: AND NOT EXISTS
3134: INTO
3135: l_sr_tasks
3136: FROM
3137: jtf_tasks_b j
3138: , cs_incidents_all_b b
3139: WHERE
3140: b.incident_id = j.source_object_id
3141: AND j.source_object_type_code = 'SR'
3142: AND b.status_flag = 'C'
3199: INTO
3200: l_sr_notes
3201: FROM
3202: jtf_notes_b j
3203: , cs_incidents_all_b b
3204: WHERE
3205: b.incident_id = j.source_object_id
3206: AND j.source_object_code = 'SR'
3207: AND b.status_flag = 'C'
3264: INTO
3265: l_sr_activities
3266: FROM
3267: jtf_ih_activities j
3268: , cs_incidents_all_b b
3269: WHERE
3270: b.incident_id = j.doc_id
3271: AND j.doc_ref = 'SR'
3272: AND b.status_flag = 'C'
3330: INTO
3331: l_sr_attachs
3332: FROM
3333: fnd_attached_documents d
3334: , cs_incidents_all_b b
3335: WHERE
3336: b.incident_id = d.pk1_value
3337: AND d.entity_name = 'CS_INCIDENTS'
3338: AND b.status_flag = 'C'
3396: INTO
3397: l_sr_work_items
3398: FROM
3399: ieu_uwqm_items u
3400: , cs_incidents_all_b b
3401: WHERE
3402: b.incident_id = u.workitem_pk_id
3403: AND u.workitem_obj_code = 'SR'
3404: AND b.status_flag = 'C'
3462: INTO
3463: l_sr_audit_1
3464: FROM
3465: cs_incidents_ext_audit a
3466: , cs_incidents_all_b b
3467: WHERE
3468: b.incident_id = a.incident_id
3469: AND b.status_flag = 'C'
3470: AND NOT EXISTS
3528: INTO
3529: l_sr_audit_2
3530: FROM
3531: cs_sr_contacts_ext_audit a
3532: , cs_incidents_all_b b
3533: WHERE
3534: b.incident_id = a.incident_id
3535: AND b.status_flag = 'C'
3536: AND NOT EXISTS
3594: INTO
3595: l_sr_audit_3
3596: FROM
3597: cs_incidents_audit_tl a
3598: , cs_incidents_all_b b
3599: WHERE
3600: b.incident_id = a.incident_id
3601: AND b.status_flag = 'C'
3602: AND NOT EXISTS
3659: INTO
3660: l_sr_audit_4
3661: FROM
3662: cs_incidents_audit_b a
3663: , cs_incidents_all_b b
3664: WHERE
3665: b.incident_id = a.incident_id
3666: AND b.status_flag = 'C'
3667: AND NOT EXISTS
3724: INTO
3725: l_sr_attr_1
3726: FROM
3727: cug_incidnt_attr_vals_b a
3728: , cs_incidents_all_b b
3729: WHERE
3730: b.incident_id = a.incident_id
3731: AND b.status_flag = 'C'
3732: AND NOT EXISTS
3789: INTO
3790: l_sr_attr_2
3791: FROM
3792: cs_incidents_ext a
3793: , cs_incidents_all_b b
3794: WHERE
3795: b.incident_id = a.incident_id
3796: AND b.status_flag = 'C'
3797: AND NOT EXISTS
6686: || ' creation_date DESC '
6687: || ' , incident_id DESC '
6688: || ' ) AS group_row_num '
6689: || ' FROM '
6690: || ' cs_incidents_all_b basetbl '
6691: || ' WHERE '
6692: || l_where_clause
6693: || ' ) inner '
6694: || ' WHERE '
6700:
6701: THEN
6702: l_sql_statement := l_sql_statement
6703: || ' FROM '
6704: || ' cs_incidents_all_b basetbl '
6705: || ' WHERE '
6706: || l_where_clause;
6707: END IF;
6708:
7047: , t.summary summary
7048: , s.purge_error_message purge_error_message
7049: FROM
7050: cs_incidents_purge_staging s
7051: , cs_incidents_all_b b
7052: , cs_incidents_all_tl t
7053: , mtl_system_items_b i
7054: , hz_parties p
7055: WHERE