SR SELECT cib.incident_id, cib.incident_number incident_number, cisl.NAME incident_status, cisl.description incident_status_description, csev.NAME cs_severity, csev.description cs_serverity_des, /* TrackIT 141852 */ csurg.NAME cs_urgency, csurg.description cs_urgency_des, /* TrackIT 141852 */ cib.problem_description incident_description, citl.summary incident_summary, cib.last_update_date last_update_date, cib.last_updated_by last_updated_by, cib.incident_date incident_date, cib.incident_occurred_date incident_occurred_date, cib.incident_resolved_date incident_resolved_date, cib.creation_date incident_creation_date, cib.close_date incident_close_date, cib.open_flag open_flag, cib.problem_code problem_code, look1.description problem_code_description, /* TrackIT 123370 */ cib.current_serial_number current_serial_number, cib.resolution_code resolution_code, look2.description resolution_code_description, /* TrackIT 123370 */ cib.web_entry_flag web_entry_flag, cib.status_flag status_flag, ssp.attribute2 survey_flag, msi.segment1 product, cib.product_name product_name, msi.description product_description, sr_group.group_name owning_group, sr_eng.resource_name support_engineer, cib.sr_creation_channel sr_creation_channel, u.user_name logged_person, ttl.NAME incident_type, ttl.description incident_type_description, hp.party_number customer_number, hca.account_number customer_account, hp.party_name customer_name, (SELECT 'party_address' FROM DUAL ) address_type, hp.address1 address1, hp.address2 address2, hp.address3 address3, hp.address4 address4, hp.city city, hp.state state, hp.postal_code postal_code, hp.country country, cib.site_id site_id, cib.customer_site_id customer_site_id, cii.external_reference configuration, cii.serial_number serial_number, sr_party.sub_party_name contact_name,
cgcv.phone_country_code phone_country_code, cgcv.phone_area_code phone_area_code, cgcv.phone_number phone_number, cgcv.email_address email_address, cia.creation_date group_assigned_date, --Added for trackit# 213714 cib.incident_attribute_1 software_version, --Raj-added for TrackIT# 248845 nuan_cs_notes(cib.incident_id) consolidated_notes -- Raj-added for TrackIt#2 36960 FROM fnd_user u, cs_hz_sr_contact_points cp, csi_item_instances cii, hz_parties hp, hz_cust_accounts hca, cs_incident_types_tl ttl, mtl_system_items_b msi, cs_incident_statuses_tl cisl, cs_incidents_all_tl citl, cs_incidents_all_b cib, --Added for trackit# 213714 cs_incidents_audit_b cia, jtf.jtf_rs_groups_tl sr_group, jtf.jtf_rs_resource_extns_tl sr_eng, apps.csc_hz_parties_self_v sr_party, apps.xxcust_csc_gs_contacts_v cgcv, hz_parties ssp, cs_lookups look1, /* TrackIT 123370 */ cs_lookups look2, /* TrackIT 123370 */ cs_incident_severities_tl csev, /* TrackIT 141852 */ cs_incident_urgencies_tl csurg /* TrackIT 141852 */ WHERE 1 = 1 -AND cib.org_id = 539 --Commented for Trackit 166662 -AND cib.incident_number = '100907330' --'100796 748' AND cib.incident_id = citl.incident_id AND citl.LANGUAGE = 'US' AND cib.incident_status_id = cisl.incident_status_id AND cisl.LANGUAGE = 'US' AND cib.incident_type_id = ttl.incident_type_id AND ttl.LANGUAGE = 'US' AND cib.inventory_item_id = msi.inventory_item_id(+) AND cib.inv_organization_id = msi.organization_id(+) AND cib.customer_id = hp.party_id AND cib.account_id = hca.cust_account_id(+) -- AND hca.party_id = hp.party_id(+) /*Commented for Trackit #279155*/ AND cib.customer_product_id = cii.instance_id(+) AND cib.incident_id = cp.incident_id(+) AND cp.party_id = cgcv.party_id(+) AND cp.party_id = sr_party.party_id(+) AND cp.primary_flag = 'Y' AND cib.created_by = u.user_id AND ssp.party_id(+) = cp.party_id AND sr_eng.resource_id(+) = cib.incident_owner_id AND sr_group.GROUP_ID(+) = cib.owner_group_id AND cib.problem_code = look1.lookup_code(+) /* TrackIT 123370 */ AND look1.lookup_type(+) = 'REQUEST_PROBLEM_CODE'
/* TrackIT 123370 */ AND cib.resolution_code = look2.lookup_code(+) /* TrackIT 123370 */ AND look2.lookup_type(+) = 'REQUEST_RESOLUTION_CODE' /* TrackIT 123370 */ AND cib.incident_severity_id = csev.incident_severity_id(+) /* TrackIT 141852 */ AND cib.incident_urgency_id = csurg.incident_urgency_id(+) /* TrackIT 141852 */ /* Added for trackit# 213714 */ AND cia.incident_id = cib.incident_id AND cia.incident_audit_id = (SELECT MAX (incident_audit_id) FROM cs_incidents_audit_b cia1 WHERE cia1.incident_id = cib.incident_id AND cia1.GROUP_ID = cib.owner_group_id AND cia1.updated_entity_code = 'SR_HEADER' AND cia1.change_group_flag = 'Y' ) /* end of code for trackit# 213714 */ SR Charges SELECT estdtl.estimate_detail_id, estdtl.last_update_date, fndu1.description last_update_by, estdtl.creation_date, fndu2.description created_by, estdtl.last_update_login, estdtl.original_source_code, estdtl.original_source_id, lk1.meaning, estdtl.source_code, estdtl.source_id, estdtl.incident_id, inc.customer_id party_id, inc.account_id, estdtl.line_number, mtl.description mtl_item, estdtl.serial_number, estdtl.quantity_required, estdtl.unit_of_measure_code, estdtl.selling_price, estdtl.after_warranty_cost, estdtl.func_curr_aft_warr_cost, inc.org_id, estdtl.coverage_bill_rate_id, estdtl.est_tax_amount, estdtl.tax_code, estdtl.business_process_id, bp.NAME business_process_name, bp.order_type_id, ttt.NAME ttype, tbt.billing_type material_billing_flag, lk2.meaning material_billing_desc, estdtl.txn_billing_type_id, ttb.revision_flag, estdtl.customer_product_id, estdtl.order_header_id, estdtl.order_line_id, estdtl.installed_cp_return_by_date, estdtl.new_cp_return_by_date, estdtl.interface_to_oe_flag, estdtl.rollup_flag, estdtl.add_to_order_flag, estdtl.return_reason_code, estdtl.price_list_header_id, estdtl.invoice_to_org_id bill_to, estdtl.ship_to_org_id ship_to, estdtl.line_type_id, estdtl.line_category_code, alty.NAME line_type_name, asop.NAME price_list_name, estdtl.currency_code, estdtl.conversion_rate, estdtl.conversion_type_code, estdtl.conversion_rate_date, asoh.order_number order_num, asoh.ordered_date order_date, asol.line_number order_line_num, SUBSTR ( hl1.address1 ' ' hl1.address2 ' ' hl1.address3 ' ' hl1.address4, 1, 220
) bill_to_address, hl1.city bcity, hl1.state bstate, hl1.country bcountry, hl1.postal_code bpostal_code, SUBSTR ( hl2.address1 ' ' hl2.address2 ' ' hl2.address3 ' ' hl2.address4, 1, 220 ) ship_to_address, hl2.city scity, hl2.state sstate, hl2.country scountry, hl2.postal_code spostal_code, okh.contract_number, estdtl.coverage_txn_group_id, okc.NAME coverage, estdtl.coverage_id, estdtl.contract_id, estdtl.exception_coverage_used, okbr.rate_name rate_type, estdtl.purchase_order_num cust_po_number, estdtl.object_version_number, estdtl.no_charge_flag, estdtl.org_id, estdtl.item_revision, estdtl.transaction_inventory_org, estdtl.transaction_sub_inventory, estdtl.charge_line_type, estdtl.bill_to_contact_id, estdtl.ship_to_contact_id, estdtl.invoice_to_account_id, estdtl.ship_to_account_id, estdtl.list_price, estdtl.contract_discount_amount, estdtl.bill_to_party_id, estdtl.ship_to_party_id, estdtl.transaction_type_id, estdtl.generated_by_bca_engine_flag, estdtl.activity_start_date_time, estdtl.activity_end_date_time, estdtl.submit_restriction_message, estdtl.submit_error_message, estdtl.submit_from_system, estdtl.line_submitted, DECODE (estdtl.charge_line_type, 'ESTIMATE', 'NEW', 'IN_PROGRESS', 'NEW', DECODE (NVL (estdtl.order_line_id, -999), -999, DECODE (NVL (estdtl.submit_error_message, 'xxx'), 'xxx', DECODE (NVL (estdtl.submit_restriction_message, 'xxx' ), 'xxx', 'NEW', 'FOR_REVIEW' ), 'IN_ERROR' ), 'SUBMITTED' ) ) line_status, DECODE (estdtl.charge_line_type, 'ESTIMATE', estdtl.after_warranty_cost, 0 ) total_estimate, DECODE (estdtl.charge_line_type, 'ESTIMATE', 0, DECODE (NVL (estdtl.order_line_id, -999), -999, estdtl.after_warranty_cost, 0 ) ) total_unsubmitted,
DECODE (estdtl.charge_line_type, 'ESTIMATE', 0, DECODE (NVL (estdtl.order_line_id, -999), -999, 0, estdtl.after_warranty_cost ) ) total_submitted, lk3.meaning line_status_meaning FROM qp_list_headers_tl asop, oe_order_headers_all asoh, oe_order_lines_all asol, oe_transaction_types_tl alty, hz_party_sites hp1, hz_party_sites hp2, hz_locations hl1, hz_locations hl2, okc_k_headers_b okh, okc_k_lines_tl okc, oks_ent_bill_rates_v okbr, cs_incidents_all_b inc, cs_lookups lk1, cs_lookups lk2, cs_transaction_types_tl ttt, cs_transaction_types_b ttb, cs_txn_billing_types tbt, cs_business_processes bp, mtl_system_items mtl, cs_estimate_details estdtl, fnd_user fndu1, fnd_user fndu2, cs_txn_billing_oetxn_all ctboa, cs_lookups lk3 WHERE estdtl.incident_id = inc.incident_id --AND estdtl.incident_id = 642844 AND estdtl.txn_billing_type_id = ctboa.txn_billing_type_id(+) AND estdtl.org_id = ctboa.org_id(+) AND estdtl.last_updated_by = fndu1.user_id AND estdtl.created_by = fndu2.user_id AND ctboa.line_type_id = alty.transaction_type_id(+) AND alty.LANGUAGE(+) = USERENV ('LANG') AND estdtl.contract_id = okh.ID(+) AND okh.start_date(+) IS NOT NULL AND okh.end_date(+) IS NOT NULL AND estdtl.coverage_id = okc.ID(+) AND okc.LANGUAGE(+) = USERENV ('LANG') AND estdtl.coverage_bill_rate_id = okbr.bill_rate_id(+) AND estdtl.business_process_id = bp.business_process_id AND estdtl.txn_billing_type_id = tbt.txn_billing_type_id(+) AND tbt.transaction_type_id = ttb.transaction_type_id(+) AND estdtl.transaction_type_id = ttt.transaction_type_id(+) AND ttt.LANGUAGE(+) = USERENV ('LANG') AND estdtl.inventory_item_id = mtl.inventory_item_id(+) AND mtl.organization_id(+) = cs_std.get_item_valdn_orgzn_id AND estdtl.price_list_header_id = asop.list_header_id(+) AND asop.LANGUAGE(+) = USERENV ('LANG') AND estdtl.invoice_to_org_id = hp1.party_site_id(+) AND estdtl.ship_to_org_id = hp2.party_site_id(+) AND hp1.location_id = hl1.location_id(+) AND hp2.location_id = hl2.location_id(+) AND estdtl.order_header_id = asoh.header_id(+)
AND AND AND AND AND AND AND
estdtl.order_line_id = asol.line_id(+) estdtl.original_source_code = lk1.lookup_code(+) lk1.lookup_type(+) = 'ESTIMATE_SOURCE' lk2.lookup_type(+) = 'MTL_SERVICE_BILLABLE_FLAG' lk2.lookup_code(+) = tbt.billing_type lk3.lookup_type(+) = 'CS_CHG_LINE_STATUS' lk3.lookup_code(+) = DECODE (estdtl.charge_line_type, 'ESTIMATE', 'NEW', 'IN_PROGRESS', 'NEW', DECODE (NVL (estdtl.order_line_id, -999.0), -999.0, DECODE (NVL (estdtl.submit_error_message, 'xxx'), 'xxx', DECODE (NVL (estdtl.submit_restriction_message, 'xxx' ), 'xxx', 'NEW', 'FOR_REVIEW' ), 'IN_ERROR' ), 'SUBMITTED' ) )
SR Details SELECT cib.sr_creation_channel sr_creation_channel, jrgt.group_name owning_group, jrre.resource_name support_engineer, ttl.NAME incident_type, cib.incident_id, cib.incident_number incident_number, cib.last_update_date last_update_date, cib.creation_date incident_creation_date, cisl.NAME incident_status, citl.summary incident_summary, hp.party_name customer_name, csev.NAME cs_severity, ROUND (( time_calc1.moved_to_assigned_date - time_calc1.incident_creation_date ), 2 ) moved_to_assigned_status, ROUND (DECODE (time_calc1.moved_from_assigned_date, TO_DATE ('01/01/1900', 'mm/dd/yyyy'), 0, ( time_calc1.moved_from_assigned_date - time_calc1.moved_to_assigned_date ) ), 2 ) moved_from_assigned_days FROM (SELECT time_calc.incident_id, time_calc.incident_creation_date, MAX (time_calc.moved_to_assigned_date ) moved_to_assigned_date, MAX
(time_calc.moved_from_assigned_date ) moved_from_assigned_date FROM (SELECT cib.incident_id, cib.creation_date incident_creation_date, cia.creation_date moved_to_assigned_date, TO_DATE ('01/01/1900', 'mm/dd/yyyy' ) moved_from_assigned_date FROM cs_incidents_all_b cib, cs_incidents_audit_b cia WHERE 1 = 1 AND cib.sr_creation_channel = 'WEB' AND cia.incident_id(+) = cib.incident_id AND cia.old_incident_status_id = 1 AND cia.incident_status_id <> 1 UNION ALL SELECT cib.incident_id, cib.creation_date incident_creation_date, TO_DATE ('01/01/1900', 'mm/dd/yyyy' ) moved_to_assigned_date, cia.creation_date moved_from_assigned_date FROM cs_incidents_all_b cib, cs_incidents_audit_b cia WHERE 1 = 1 AND cib.sr_creation_channel = 'WEB' AND cia.incident_id(+) = cib.incident_id AND cia.old_incident_status_id = 103 AND cia.incident_status_id <> 103) time_calc GROUP BY time_calc.incident_id, time_calc.incident_creation_date) tim e_calc1,
WHERE AND AND AND AND AND AND AND AND AND AND AND
cs_incidents_all_b cib, cs_incidents_all_tl citl, cs_incident_statuses_tl cisl, cs_incident_types_tl ttl, cs_incident_severities_tl csev, jtf.jtf_rs_groups_tl jrgt, jtf.jtf_rs_resource_extns_tl jrre, hz_parties hp cib.incident_id = time_calc1.incident_id cib.incident_id = citl.incident_id citl.LANGUAGE = 'US' cib.incident_status_id = cisl.incident_status_id cisl.LANGUAGE = 'US' cib.incident_type_id = ttl.incident_type_id ttl.LANGUAGE = 'US' cib.customer_id = hp.party_id jrre.resource_id(+) = cib.incident_owner_id jrgt.GROUP_ID(+) = cib.owner_group_id cib.incident_severity_id = csev.incident_severity_id(+) cisl.NAME <> 'Solved'
SR Log Notes SELECT ciab.incident_number , TO_CHAR (ciab.incident_date, 'DD-MON-YYYY HH24:MI: SS') reported, citt.NAME type, cist.NAME Status, cisvt.NAME severity, jrgt.group_name group_name, jrret.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number,
hca.account_number , hp.email_address , mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description ,
FROM
WHERE AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND
'Audit- Severity' source_type, ext_user.source_name ' (' fu.user_name ')' user_name, TO_CHAR (audi.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, 'Severity:' '-------->' NVL (severity1.NAME, ' ') ' --------> ' severity2.NAME "Logs And Notes" fnd_user fu, jtf_rs_resource_extns ext_user, cs_incidents_audit_b audi, cs_lookups lkp, cs_incident_severities_tl severity1, cs_incident_severities_tl severity2, cs_incidents_all_b ciab , cs_incident_types_b citb, cs_incident_types_tl citt, cs_incident_statuses_b cisb, cs_incident_statuses_tl cist, cs_incident_severities_b cisvb, cs_incident_severities_tl cisvt, jtf_rs_groups_b jrgb, jtf_rs_groups_tl jrgt, jtf_rs_resource_extns jrre, jtf_rs_resource_extns_tl jrret, hz_parties hp, hz_cust_accounts hca, mtl_categories mc, mtl_system_items_b msi, mtl_parameters mp 1=1 -- audi.incident_id = :p_incident_id ext_user.user_id = fu.user_id fu.user_id = audi.last_updated_by lkp.lookup_type = 'CS_SR_CHILD_ENTITY_CODE' lkp.lookup_code = 'SR_HEADER' severity1.incident_severity_id(+) = audi.old_incident_severity_id (severity1.LANGUAGE = USERENV ('LANG') OR severity1.LANGUAGE IS NULL) severity2.incident_severity_id(+) = audi.incident_severity_id (severity2.LANGUAGE = USERENV ('LANG') OR severity2.LANGUAGE IS NULL) audi.change_incident_severity_flag = 'Y' audi.incident_id = ciab.incident_id ciab.incident_type_id = citb.incident_type_id citb.incident_type_id = citt.incident_type_id ciab.incident_type_id = citt.incident_type_id ciab.incident_status_id = cisb.incident_status_id cisb.incident_status_id = cist.incident_status_id ciab.incident_status_id = cist.incident_status_id ciab.incident_severity_id = cisvb.incident_severity_id cisvb.incident_severity_id = cisvt.incident_severity_id ciab.incident_severity_id = cisvt.incident_severity_id ciab.owner_group_id = jrgb.GROUP_ID jrgb.GROUP_ID = jrgt.GROUP_ID ciab.owner_group_id = jrgt.GROUP_ID ciab.incident_owner_id = jrre.resource_id(+) jrre.resource_id = jrret.resource_id(+) (ciab.incident_owner_id=jrret.resource_id or jrret.resource_id is null) ciab.caller_type = hp.party_type
AND ciab.customer_id = hp.party_id AND ciab.account_id = hca.cust_account_id AND ciab.category_id = mc.category_id(+) AND ciab.inventory_item_id = msi.inventory_item_id(+) AND msi.organization_id = mp.organization_id(+) AND (mp.organization_code = 'MLB' OR mp.organization_code IS NULL) UNION ALL SELECT ciab.incident_number , TO_CHAR (ciab.incident_date, 'DD-MON-YYYY HH24:MI: SS') reported, citt.NAME type, cist.NAME Status, cisvt.NAME severity, jrgt.group_name group_name, jrret.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number, hca.account_number , hp.email_address , mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description , 'Audit- Type' source_type, ext_user.source_name ' (' fu.user_name ')' user_name, TO_CHAR (audi.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, 'Type:' '-------->' NVL (type1.NAME, ' ') ' --------> ' type2.NAME "Logs And Notes" FROM fnd_user fu, jtf_rs_resource_extns ext_user, cs_incidents_audit_b audi, cs_lookups lkp, cs_incident_types_tl type1, cs_incident_types_tl type2, cs_incidents_all_b ciab, --cs_incidents_all_b ciab1, cs_incident_types_b citb, cs_incident_types_tl citt, cs_incident_statuses_b cisb, cs_incident_statuses_tl cist, cs_incident_severities_b cisvb, cs_incident_severities_tl cisvt, jtf_rs_groups_b jrgb, jtf_rs_groups_tl jrgt, jtf_rs_resource_extns jrre, jtf_rs_resource_extns_tl jrret, hz_parties hp, hz_cust_accounts hca, mtl_categories mc, mtl_system_items_b msi, mtl_parameters mp WHERE 1=1 -- audi.incident_id = :p_incident_id AND ext_user.user_id = fu.user_id AND fu.user_id = audi.last_updated_by AND lkp.lookup_type = 'CS_SR_CHILD_ENTITY_CODE' AND lkp.lookup_code = 'SR_HEADER' AND type1.incident_type_id(+) = audi.old_incident_type_id AND (type1.LANGUAGE = USERENV ('LANG') OR type1.LANGUAGE IS NULL) AND type2.incident_type_id(+) = audi.incident_type_id AND (type2.LANGUAGE = USERENV ('LANG') OR type2.LANGUAGE IS NULL) AND audi.change_incident_type_flag = 'Y' AND audi.incident_id = ciab.incident_id AND ciab.incident_type_id = citb.incident_type_id AND citb.incident_type_id = citt.incident_type_id
AND ciab.incident_type_id = citt.incident_type_id AND ciab.incident_status_id = cisb.incident_status_id AND cisb.incident_status_id = cist.incident_status_id AND ciab.incident_status_id = cist.incident_status_id AND ciab.incident_severity_id = cisvb.incident_severity_id AND cisvb.incident_severity_id = cisvt.incident_severity_id AND ciab.incident_severity_id = cisvt.incident_severity_id AND ciab.owner_group_id = jrgb.GROUP_ID AND jrgb.GROUP_ID = jrgt.GROUP_ID AND ciab.owner_group_id = jrgt.GROUP_ID AND ciab.incident_owner_id = jrre.resource_id(+) AND jrre.resource_id = jrret.resource_id(+) AND (ciab.incident_owner_id=jrret.resource_id or jrret.resource_id is null) AND ciab.caller_type = hp.party_type AND ciab.customer_id = hp.party_id --and hp.party_id=hca1.cust_account_id AND ciab.account_id = hca.cust_account_id AND ciab.category_id = mc.category_id(+) AND ciab.inventory_item_id = msi.inventory_item_id(+) AND msi.organization_id = mp.organization_id(+) AND (mp.organization_code = 'MLB' OR mp.organization_code IS NULL) UNION ALL SELECT ciab.incident_number , TO_CHAR (ciab.incident_date, 'DD-MON-YYYY HH24:MI: SS') reported, citt.NAME type, cist.NAME Status, cisvt.NAME severity, jrgt.group_name group_name, jrret.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number, hca.account_number , hp.email_address , mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description , 'Audit- Status' source_type, ext_user.source_name ' (' fu.user_name ')' user_name, TO_CHAR (audi.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, 'Status:' '-------->' NVL (status1.NAME, ' ') ' --------> ' status2.NAME "Logs And Notes" FROM fnd_user fu, jtf_rs_resource_extns ext_user, cs_incidents_audit_b audi, cs_lookups lkp, cs_incident_statuses_tl status1, cs_incident_statuses_tl status2, cs_incidents_all_b ciab, --cs_incidents_all_b ciab1, cs_incident_types_b citb, cs_incident_types_tl citt, cs_incident_statuses_b cisb, cs_incident_statuses_tl cist, cs_incident_severities_b cisvb, cs_incident_severities_tl cisvt, jtf_rs_groups_b jrgb, jtf_rs_groups_tl jrgt, jtf_rs_resource_extns jrre, jtf_rs_resource_extns_tl jrret, hz_parties hp, hz_cust_accounts hca, mtl_categories mc, mtl_system_items_b msi,
mtl_parameters mp WHERE 1=1 -- audi.incident_id = :p_incident_id AND ext_user.user_id = fu.user_id AND fu.user_id = audi.last_updated_by AND lkp.lookup_type = 'CS_SR_CHILD_ENTITY_CODE' AND lkp.lookup_code = 'SR_HEADER' AND status1.incident_status_id(+) = audi.old_incident_status_id AND (status1.LANGUAGE = USERENV ('LANG') OR status1.LANGUAGE IS NULL) AND status2.incident_status_id(+) = audi.incident_status_id AND (status2.LANGUAGE = USERENV ('LANG') OR status2.LANGUAGE IS NULL) AND audi.change_incident_status_flag = 'Y' AND audi.incident_id = ciab.incident_id AND ciab.incident_type_id = citb.incident_type_id AND citb.incident_type_id = citt.incident_type_id AND ciab.incident_type_id = citt.incident_type_id AND ciab.incident_status_id = cisb.incident_status_id AND cisb.incident_status_id = cist.incident_status_id AND ciab.incident_status_id = cist.incident_status_id AND ciab.incident_severity_id = cisvb.incident_severity_id AND cisvb.incident_severity_id = cisvt.incident_severity_id AND ciab.incident_severity_id = cisvt.incident_severity_id AND ciab.owner_group_id = jrgb.GROUP_ID AND jrgb.GROUP_ID = jrgt.GROUP_ID AND ciab.owner_group_id = jrgt.GROUP_ID AND ciab.incident_owner_id = jrre.resource_id(+) AND jrre.resource_id = jrret.resource_id(+) AND (ciab.incident_owner_id=jrret.resource_id or jrret.resource_id is null) AND ciab.caller_type = hp.party_type AND ciab.customer_id = hp.party_id --and hp.party_id=hca1.cust_account_id AND ciab.account_id = hca.cust_account_id AND ciab.category_id = mc.category_id(+) AND ciab.inventory_item_id = msi.inventory_item_id(+) AND msi.organization_id = mp.organization_id(+) AND (mp.organization_code = 'MLB' OR mp.organization_code IS NULL) UNION ALL SELECT ciab.incident_number , TO_CHAR (ciab.incident_date, 'DD-MON-YYYY HH24:MI: SS') reported, citt.NAME type, cist.NAME Status, cisvt.NAME severity, jrgt.group_name group_name, jrret.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number, hca.account_number , hp.email_address , mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description , 'Audit- Urgency' source_type, ext_user.source_name ' (' fu.user_name ')' user_name, TO_CHAR (audi.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, 'Urgency:' '-------->' NVL (urgency1.NAME, ' ') ' --------> ' urgency2.NAME "Logs And Notes" FROM fnd_user fu, jtf_rs_resource_extns ext_user, cs_incidents_audit_b audi, cs_lookups lkp, cs_incident_urgencies_tl urgency1, cs_incident_urgencies_tl urgency2, cs_incidents_all_b ciab, --cs_incidents_all_b ciab1,
cs_incident_types_b citb, cs_incident_types_tl citt, cs_incident_statuses_b cisb, cs_incident_statuses_tl cist, cs_incident_severities_b cisvb, cs_incident_severities_tl cisvt, jtf_rs_groups_b jrgb, jtf_rs_groups_tl jrgt, jtf_rs_resource_extns jrre, jtf_rs_resource_extns_tl jrret, hz_parties hp, hz_cust_accounts hca, mtl_categories mc, mtl_system_items_b msi, mtl_parameters mp WHERE 1=1 -- audi.incident_id = :p_incident_id AND ext_user.user_id = fu.user_id AND fu.user_id = audi.last_updated_by AND lkp.lookup_type = 'CS_SR_CHILD_ENTITY_CODE' AND lkp.lookup_code = 'SR_HEADER' AND urgency1.incident_urgency_id(+) = audi.old_incident_urgency_id AND (urgency1.LANGUAGE = USERENV ('LANG') OR urgency1.LANGUAGE IS NULL) AND urgency2.incident_urgency_id(+) = audi.incident_urgency_id AND (urgency2.LANGUAGE = USERENV ('LANG') OR urgency2.LANGUAGE IS NULL) AND audi.change_incident_urgency_flag = 'Y' AND audi.incident_id = ciab.incident_id AND ciab.incident_type_id = citb.incident_type_id AND citb.incident_type_id = citt.incident_type_id AND ciab.incident_type_id = citt.incident_type_id AND ciab.incident_status_id = cisb.incident_status_id AND cisb.incident_status_id = cist.incident_status_id AND ciab.incident_status_id = cist.incident_status_id AND ciab.incident_severity_id = cisvb.incident_severity_id AND cisvb.incident_severity_id = cisvt.incident_severity_id AND ciab.incident_severity_id = cisvt.incident_severity_id AND ciab.owner_group_id = jrgb.GROUP_ID AND jrgb.GROUP_ID = jrgt.GROUP_ID AND ciab.owner_group_id = jrgt.GROUP_ID AND ciab.incident_owner_id = jrre.resource_id(+) AND jrre.resource_id = jrret.resource_id(+) AND (ciab.incident_owner_id=jrret.resource_id or jrret.resource_id is null) AND ciab.caller_type = hp.party_type AND ciab.customer_id = hp.party_id --and hp.party_id=hca1.cust_account_id AND ciab.account_id = hca.cust_account_id AND ciab.category_id = mc.category_id(+) AND ciab.inventory_item_id = msi.inventory_item_id(+) AND msi.organization_id = mp.organization_id(+) AND (mp.organization_code = 'MLB' OR mp.organization_code IS NULL) UNION ALL SELECT ciab.incident_number , TO_CHAR (ciab.incident_date, 'DD-MON-YYYY HH24:MI: SS') reported, citt.NAME type, cist.NAME Status, cisvt.NAME severity, jrgt.group_name group_name, jrret.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number, hca.account_number , hp.email_address , mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description , 'Audit- Group' source_type, ext_user.source_name ' (' fu.user_name ')' user_name,
FROM
WHERE AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND
TO_CHAR (audi.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, 'Group:' '-------->' NVL (DECODE (audi.old_group_type, 'RS_TEAM', team1.team_name, 'RS_GROUP', grp1.group_name ), ' ' ) ' --------> ' DECODE (audi.group_type, 'RS_TEAM', team2.team_name, 'RS_GROUP', grp2.group_name ) "Logs And Notes" fnd_user fu, jtf_rs_resource_extns ext_user, cs_incidents_audit_b audi, cs_lookups lkp, jtf_rs_teams_tl team1, jtf_rs_groups_tl grp1, jtf_rs_teams_tl team2, jtf_rs_groups_tl grp2, cs_incidents_all_b ciab, --cs_incidents_all_b ciab1, cs_incident_types_b citb, cs_incident_types_tl citt, cs_incident_statuses_b cisb, cs_incident_statuses_tl cist, cs_incident_severities_b cisvb, cs_incident_severities_tl cisvt, jtf_rs_groups_b jrgb, jtf_rs_groups_tl jrgt, jtf_rs_resource_extns jrre, jtf_rs_resource_extns_tl jrret, hz_parties hp, hz_cust_accounts hca, mtl_categories mc, mtl_system_items_b msi, mtl_parameters mp 1=1 -- audi.incident_id = :p_incident_id ext_user.user_id = fu.user_id fu.user_id = audi.last_updated_by lkp.lookup_type = 'CS_SR_CHILD_ENTITY_CODE' lkp.lookup_code = 'SR_HEADER' team1.team_id(+) = audi.old_group_id (team1.LANGUAGE = USERENV ('LANG') OR team1.LANGUAGE IS NULL) grp1.GROUP_ID(+) = audi.old_group_id (grp1.LANGUAGE = USERENV ('LANG') OR grp1.LANGUAGE IS NULL) team2.team_id(+) = audi.GROUP_ID (team2.LANGUAGE = USERENV ('LANG') OR team2.LANGUAGE IS NULL) grp2.GROUP_ID(+) = audi.GROUP_ID (grp2.LANGUAGE = USERENV ('LANG') OR grp2.LANGUAGE IS NULL) audi.change_group_flag = 'Y' audi.incident_id = ciab.incident_id ciab.incident_type_id = citb.incident_type_id citb.incident_type_id = citt.incident_type_id ciab.incident_type_id = citt.incident_type_id ciab.incident_status_id = cisb.incident_status_id cisb.incident_status_id = cist.incident_status_id ciab.incident_status_id = cist.incident_status_id
AND ciab.incident_severity_id = cisvb.incident_severity_id AND cisvb.incident_severity_id = cisvt.incident_severity_id AND ciab.incident_severity_id = cisvt.incident_severity_id AND ciab.owner_group_id = jrgb.GROUP_ID AND jrgb.GROUP_ID = jrgt.GROUP_ID AND ciab.owner_group_id = jrgt.GROUP_ID AND ciab.incident_owner_id = jrre.resource_id(+) AND jrre.resource_id = jrret.resource_id(+) AND (ciab.incident_owner_id=jrret.resource_id or jrret.resource_id is null) AND ciab.caller_type = hp.party_type AND ciab.customer_id = hp.party_id --and hp.party_id=hca1.cust_account_id AND ciab.account_id = hca.cust_account_id AND ciab.category_id = mc.category_id(+) AND ciab.inventory_item_id = msi.inventory_item_id(+) AND msi.organization_id = mp.organization_id(+) AND (mp.organization_code = 'MLB' OR mp.organization_code IS NULL) UNION ALL SELECT ciab.incident_number , TO_CHAR (ciab.incident_date, 'DD-MON-YYYY HH24:MI: SS') reported, citt.NAME type, cist.NAME Status, cisvt.NAME severity, jrgt.group_name group_name, jrret.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number, hca.account_number , hp.email_address , mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description , 'Audit- Group Type' source_type, ext_user.source_name ' (' fu.user_name ')' user_name, TO_CHAR (audi.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, 'Group Type:' '-------->' NVL (obj3.NAME, ' ') ' --------> ' obj4.NAME "Logs And Notes" FROM fnd_user fu, jtf_rs_resource_extns ext_user, cs_incidents_audit_b audi, cs_lookups lkp, jtf_objects_tl obj3, jtf_objects_tl obj4, cs_incidents_all_b ciab, --cs_incidents_all_b ciab1, cs_incident_types_b citb, cs_incident_types_tl citt, cs_incident_statuses_b cisb, cs_incident_statuses_tl cist, cs_incident_severities_b cisvb, cs_incident_severities_tl cisvt, jtf_rs_groups_b jrgb, jtf_rs_groups_tl jrgt, jtf_rs_resource_extns jrre, jtf_rs_resource_extns_tl jrret, hz_parties hp, hz_cust_accounts hca, mtl_categories mc, mtl_system_items_b msi, mtl_parameters mp WHERE 1=1 -- audi.incident_id = :p_incident_id AND ext_user.user_id = fu.user_id AND fu.user_id = audi.last_updated_by
AND lkp.lookup_type = 'CS_SR_CHILD_ENTITY_CODE' AND lkp.lookup_code = 'SR_HEADER' AND obj3.object_code(+) = audi.old_group_type AND (obj3.LANGUAGE = USERENV ('LANG') OR obj3.LANGUAGE IS NULL) AND obj4.object_code(+) = audi.group_type AND (obj4.LANGUAGE = USERENV ('LANG') OR obj4.LANGUAGE IS NULL) AND audi.change_group_type_flag = 'Y' AND audi.incident_id = ciab.incident_id AND ciab.incident_type_id = citb.incident_type_id AND citb.incident_type_id = citt.incident_type_id AND ciab.incident_type_id = citt.incident_type_id AND ciab.incident_status_id = cisb.incident_status_id AND cisb.incident_status_id = cist.incident_status_id AND ciab.incident_status_id = cist.incident_status_id AND ciab.incident_severity_id = cisvb.incident_severity_id AND cisvb.incident_severity_id = cisvt.incident_severity_id AND ciab.incident_severity_id = cisvt.incident_severity_id AND ciab.owner_group_id = jrgb.GROUP_ID AND jrgb.GROUP_ID = jrgt.GROUP_ID AND ciab.owner_group_id = jrgt.GROUP_ID AND ciab.incident_owner_id = jrre.resource_id(+) AND jrre.resource_id = jrret.resource_id(+) AND (ciab.incident_owner_id=jrret.resource_id or jrret.resource_id is null) AND ciab.caller_type = hp.party_type AND ciab.customer_id = hp.party_id --and hp.party_id=hca1.cust_account_id AND ciab.account_id = hca.cust_account_id AND ciab.category_id = mc.category_id(+) AND ciab.inventory_item_id = msi.inventory_item_id(+) AND msi.organization_id = mp.organization_id(+) AND (mp.organization_code = 'MLB' OR mp.organization_code IS NULL) UNION ALL SELECT ciab.incident_number , TO_CHAR (ciab.incident_date, 'DD-MON-YYYY HH24:MI: SS') reported, citt.NAME type, cist.NAME Status, cisvt.NAME severity, jrgt.group_name group_name, jrret.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number, hca.account_number , hp.email_address , mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description , 'Audit- Owner' source_type, ext_user.source_name ' (' fu.user_name ')' user_name, TO_CHAR (audi.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, 'Owner:' '-------->' NVL (DECODE (audi.old_resource_type, 'RS_EMPLOYEE', ext1.source_last_name ' ' ext1.source_first_name ), ' ' ) ' --------> ' DECODE (audi.resource_type, 'RS_EMPLOYEE', ext2.source_last_name ' ' ext2.source_first_name ) "Logs And Notes" FROM fnd_user fu, jtf_rs_resource_extns ext_user, cs_incidents_audit_b audi, cs_lookups lkp,
jtf_rs_resource_extns ext1, jtf_rs_resource_extns ext2, cs_incidents_all_b ciab, --cs_incidents_all_b ciab1, cs_incident_types_b citb, cs_incident_types_tl citt, cs_incident_statuses_b cisb, cs_incident_statuses_tl cist, cs_incident_severities_b cisvb, cs_incident_severities_tl cisvt, jtf_rs_groups_b jrgb, jtf_rs_groups_tl jrgt, jtf_rs_resource_extns jrre, jtf_rs_resource_extns_tl jrret, hz_parties hp, hz_cust_accounts hca, mtl_categories mc, mtl_system_items_b msi, mtl_parameters mp WHERE 1=1 -- audi.incident_id = :p_incident_id AND ext_user.user_id = fu.user_id AND fu.user_id = audi.last_updated_by AND lkp.lookup_type = 'CS_SR_CHILD_ENTITY_CODE' AND lkp.lookup_code = 'SR_HEADER' AND ext1.resource_id(+) = audi.old_incident_owner_id AND ext2.resource_id(+) = audi.incident_owner_id AND audi.change_incident_owner_flag = 'Y' AND audi.incident_id = ciab.incident_id AND ciab.incident_type_id = citb.incident_type_id AND citb.incident_type_id = citt.incident_type_id AND ciab.incident_type_id = citt.incident_type_id AND ciab.incident_status_id = cisb.incident_status_id AND cisb.incident_status_id = cist.incident_status_id AND ciab.incident_status_id = cist.incident_status_id AND ciab.incident_severity_id = cisvb.incident_severity_id AND cisvb.incident_severity_id = cisvt.incident_severity_id AND ciab.incident_severity_id = cisvt.incident_severity_id AND ciab.owner_group_id = jrgb.GROUP_ID AND jrgb.GROUP_ID = jrgt.GROUP_ID AND ciab.owner_group_id = jrgt.GROUP_ID AND ciab.incident_owner_id = jrre.resource_id(+) AND jrre.resource_id = jrret.resource_id(+) AND (ciab.incident_owner_id=jrret.resource_id or jrret.resource_id is null) AND ciab.caller_type = hp.party_type AND ciab.customer_id = hp.party_id --and hp.party_id=hca1.cust_account_id AND ciab.account_id = hca.cust_account_id AND ciab.category_id = mc.category_id(+) AND ciab.inventory_item_id = msi.inventory_item_id(+) AND msi.organization_id = mp.organization_id(+) AND (mp.organization_code = 'MLB' OR mp.organization_code IS NULL) UNION ALL SELECT ciab.incident_number , TO_CHAR (ciab.incident_date, 'DD-MON-YYYY HH24:MI: SS') reported, citt.NAME type, cist.NAME Status, cisvt.NAME severity, jrgt.group_name group_name, jrret.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number, hca.account_number , hp.email_address , mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description
, 'Audit- Owner Type' source_type, ext_user.source_name ' (' fu.user_name ')' user_name, TO_CHAR (audi.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, 'Owner Type:' '-------->' NVL (obj1.NAME, ' ') ' --------> ' obj2.NAME "Logs And Notes" FROM fnd_user fu, jtf_rs_resource_extns ext_user, cs_incidents_audit_b audi, cs_lookups lkp, jtf_objects_tl obj1, jtf_objects_tl obj2, cs_incidents_all_b ciab, --cs_incidents_all_b ciab1, cs_incident_types_b citb, cs_incident_types_tl citt, cs_incident_statuses_b cisb, cs_incident_statuses_tl cist, cs_incident_severities_b cisvb, cs_incident_severities_tl cisvt, jtf_rs_groups_b jrgb, jtf_rs_groups_tl jrgt, jtf_rs_resource_extns jrre, jtf_rs_resource_extns_tl jrret, hz_parties hp, hz_cust_accounts hca, mtl_categories mc, mtl_system_items_b msi, mtl_parameters mp WHERE 1=1 -- audi.incident_id = :p_incident_id AND ext_user.user_id = fu.user_id AND fu.user_id = audi.last_updated_by AND lkp.lookup_type = 'CS_SR_CHILD_ENTITY_CODE' AND lkp.lookup_code = 'SR_HEADER' AND obj1.object_code(+) = audi.old_resource_type AND (obj1.LANGUAGE = USERENV ('LANG') OR obj1.LANGUAGE IS NULL) AND obj2.object_code(+) = audi.resource_type AND (obj2.LANGUAGE = USERENV ('LANG') OR obj2.LANGUAGE IS NULL) AND audi.change_resource_type_flag = 'Y' AND audi.incident_id = ciab.incident_id AND ciab.incident_type_id = citb.incident_type_id AND citb.incident_type_id = citt.incident_type_id AND ciab.incident_type_id = citt.incident_type_id AND ciab.incident_status_id = cisb.incident_status_id AND cisb.incident_status_id = cist.incident_status_id AND ciab.incident_status_id = cist.incident_status_id AND ciab.incident_severity_id = cisvb.incident_severity_id AND cisvb.incident_severity_id = cisvt.incident_severity_id AND ciab.incident_severity_id = cisvt.incident_severity_id AND ciab.owner_group_id = jrgb.GROUP_ID AND jrgb.GROUP_ID = jrgt.GROUP_ID AND ciab.owner_group_id = jrgt.GROUP_ID AND ciab.incident_owner_id = jrre.resource_id(+) AND jrre.resource_id = jrret.resource_id(+) AND (ciab.incident_owner_id=jrret.resource_id or jrret.resource_id is null) AND ciab.caller_type = hp.party_type AND ciab.customer_id = hp.party_id --and hp.party_id=hca1.cust_account_id
AND ciab.account_id = hca.cust_account_id AND ciab.category_id = mc.category_id(+) AND ciab.inventory_item_id = msi.inventory_item_id(+) AND msi.organization_id = mp.organization_id(+) AND (mp.organization_code = 'MLB' OR mp.organization_code IS NULL) UNION ALL SELECT ciab.incident_number , TO_CHAR (ciab.incident_date, 'DD-MON-YYYY HH24:MI: SS') reported, citt.NAME type, cist.NAME Status, cisvt.NAME severity, jrgt.group_name group_name, jrret.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number, hca.account_number , hp.email_address , mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description , 'Audit- Resolution Date' source_type, ext_user.source_name ' (' fu.user_name ')' user_name, TO_CHAR (audi.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, 'Resolution Date:' '-------->' NVL (audi.old_expected_resolution_date, ' ') ' --------> ' audi.expected_resolution_date "Logs And Notes" FROM fnd_user fu, jtf_rs_resource_extns ext_user, cs_incidents_audit_b audi, cs_lookups lkp, cs_incidents_all_b ciab, --cs_incidents_all_b ciab1, cs_incident_types_b citb, cs_incident_types_tl citt, cs_incident_statuses_b cisb, cs_incident_statuses_tl cist, cs_incident_severities_b cisvb, cs_incident_severities_tl cisvt, jtf_rs_groups_b jrgb, jtf_rs_groups_tl jrgt, jtf_rs_resource_extns jrre, jtf_rs_resource_extns_tl jrret, hz_parties hp, hz_cust_accounts hca, mtl_categories mc, mtl_system_items_b msi, mtl_parameters mp WHERE 1=1 -- audi.incident_id = :p_incident_id AND ext_user.user_id = fu.user_id AND fu.user_id = audi.last_updated_by AND lkp.lookup_type = 'CS_SR_CHILD_ENTITY_CODE' AND lkp.lookup_code = 'SR_HEADER' AND audi.change_resolution_flag = 'Y' AND audi.incident_id = ciab.incident_id AND ciab.incident_type_id = citb.incident_type_id AND citb.incident_type_id = citt.incident_type_id AND ciab.incident_type_id = citt.incident_type_id AND ciab.incident_status_id = cisb.incident_status_id AND cisb.incident_status_id = cist.incident_status_id AND ciab.incident_status_id = cist.incident_status_id AND ciab.incident_severity_id = cisvb.incident_severity_id AND cisvb.incident_severity_id = cisvt.incident_severity_id AND ciab.incident_severity_id = cisvt.incident_severity_id AND ciab.owner_group_id = jrgb.GROUP_ID
AND jrgb.GROUP_ID = jrgt.GROUP_ID AND ciab.owner_group_id = jrgt.GROUP_ID AND ciab.incident_owner_id = jrre.resource_id(+) AND jrre.resource_id = jrret.resource_id(+) AND (ciab.incident_owner_id=jrret.resource_id or jrret.resource_id is null) AND ciab.caller_type = hp.party_type AND ciab.customer_id = hp.party_id --and hp.party_id=hca1.cust_account_id AND ciab.account_id = hca.cust_account_id AND ciab.category_id = mc.category_id(+) AND ciab.inventory_item_id = msi.inventory_item_id(+) AND msi.organization_id = mp.organization_id(+) AND (mp.organization_code = 'MLB' OR mp.organization_code IS NULL) UNION ALL SELECT csi.incident_number, TO_CHAR (csi.incident_date, 'DD-MON-YYYY HH24:MI:SS') reported, csit.NAME TYPE, csis.NAME status, cssv.NAME severity, jrg.group_name group_name, jrre.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number, hca.account_number, hp.email_address, mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description , 'Note' source_type, ext_user.source_name ' (' fnd.user_name ')' user_name, TO_CHAR (jtf.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, 'Notes: 'jtf.note_type_meaning ': ' jtf.notes "Logs And Notes " FROM jtf_notes_vl jtf, fnd_user fnd, jtf_rs_resource_extns ext_user, cs_incidents_all_b csi, cs_incident_types_tl csit, cs_incident_statuses_tl csis, cs_incident_severities_tl cssv, jtf_rs_groups_tl jrg, jtf_rs_resource_extns_tl jrre, hz_parties hp, hz_cust_accounts hca, mtl_categories mc, mtl_system_items_b msi, mtl_parameters mp WHERE source_object_code = 'SR' AND 1=1 -- source_object_id = :p_incident_id AND ext_user.user_id(+) = fnd.user_id AND jtf.created_by = fnd.user_id AND ( jtf.note_status != 'P') AND source_object_id = csi.incident_id AND csi.incident_type_id = csit.incident_type_id AND csi.incident_status_id = csis.incident_status_id AND csi.incident_severity_id = cssv.incident_severity_id AND csi.owner_group_id = jrg.GROUP_ID(+) AND csi.incident_owner_id = jrre.resource_id(+) AND csi.customer_id = hp.party_id AND csi.account_id = hca.cust_account_id AND csi.category_id = mc.category_id(+) AND csi.inventory_item_id = msi.inventory_item_id(+) AND msi.organization_id = mp.organization_id(+) AND (mp.organization_code = 'MLB' OR mp.organization_code IS NULL) union all SELECT csi.incident_number, TO_CHAR (csi.incident_date, 'DD-MON-YYYY HH24:MI:SS') reported, csit.NAME TYPE, csis.NAME status, cssv.NAME severity,
jrg.group_name group_name, jrre.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number, hca.account_number, hp.email_address, mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description , 'Soln' source_type, ext_user.source_name ' (' fnd.user_name ')' user_name, TO_CHAR (cskbl.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, 'Knowlwdge: 'ext_user.source_name ' (' fnd.user_name ') ' csks.set_number ' ' cskst.NAME ': ' csks.NAME "Logs And Notes" FROM cs_kb_sets_vl csks, cs_kb_set_links cskbl, cs_lookups csl, cs_kb_set_types_tl cskst, jtf_rs_resource_extns ext_user, fnd_user fnd, cs_incidents_all_b csi, cs_incident_types_tl csit, cs_incident_statuses_tl csis, cs_incident_severities_tl cssv, jtf_rs_groups_tl jrg, jtf_rs_resource_extns_tl jrre, hz_parties hp, hz_cust_accounts hca, mtl_categories mc, mtl_system_items_b msi, mtl_parameters mp WHERE cskst.set_type_id = csks.set_type_id AND cskbl.set_id = csks.set_id AND csks.viewable_version_flag = 'Y' AND csl.lookup_code(+) = cskbl.link_type AND csl.lookup_type(+) = 'CS_KB_LINK_TYPE' AND cskbl.created_by = fnd.user_id AND ext_user.user_id = fnd.user_id AND cskbl.other_id = csi.incident_id(+) AND csi.incident_type_id = csit.incident_type_id(+) AND csi.incident_status_id = csis.incident_status_id(+) AND csi.incident_severity_id = cssv.incident_severity_id(+) AND csi.owner_group_id = jrg.GROUP_ID(+) AND csi.incident_owner_id = jrre.resource_id(+) AND csi.customer_id = hp.party_id AND csi.account_id = hca.cust_account_id AND csi.category_id = mc.category_id(+) AND csi.inventory_item_id = msi.inventory_item_id(+) AND msi.organization_id = mp.organization_id(+) AND (mp.organization_code = 'MLB' OR mp.organization_code IS NULL) AND 1=1 -- cskbl.other_id=:p_incident_id union all SELECT csi.incident_number, TO_CHAR (csi.incident_date, 'DD-MON-YYYY HH24:MI:SS') reported, csit.NAME TYPE, csis.NAME status, cssv.NAME severity, jrg.group_name group_name, jrre.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number, hca.account_number, hp.email_address, mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description , 'Activity' source_type, ext_user.source_name ' (' fnd.user_name ')' user_name, TO_CHAR (jtf.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, act.short_description "Logs And Notes"
FROM jtf_ih_activities jtf, jtf_ih_actions_tl act, jtf_rs_resource_extns ext_user, fnd_user fnd, cs_incidents_all_b csi, cs_incident_types_tl csit, cs_incident_statuses_tl csis, cs_incident_severities_tl cssv, jtf_rs_groups_tl jrg, jtf_rs_resource_extns_tl jrre, hz_parties hp, hz_cust_accounts hca, mtl_categories mc, mtl_system_items_b msi, mtl_parameters mp WHERE doc_ref = 'SR' AND ext_user.user_id = fnd.user_id AND 1=1 -- doc_id = :p_incident_id AND jtf.action_id = act.action_id AND act.LANGUAGE = USERENV ('LANG') AND jtf.created_by = fnd.user_id AND doc_id = csi.incident_id AND csi.incident_type_id = csit.incident_type_id AND csi.incident_status_id = csis.incident_status_id AND csi.incident_severity_id = cssv.incident_severity_id AND csi.owner_group_id = jrg.GROUP_ID(+) AND csi.incident_owner_id = jrre.resource_id(+) AND csi.customer_id = hp.party_id AND csi.account_id = hca.cust_account_id AND csi.category_id = mc.category_id(+) AND csi.inventory_item_id = msi.inventory_item_id(+) AND msi.organization_id = mp.organization_id(+) AND (mp.organization_code = 'MLB' OR mp.organization_code IS NULL) union all SELECT csi.incident_number, TO_CHAR (csi.incident_date, 'DD-MON-YYYY HH24:MI:SS') reported, csit.NAME TYPE, csis.NAME status, cssv.NAME severity, jrg.group_name group_name, jrre.resource_name owner, hp.party_type customer_type, hp.party_id , hp.party_name customer_name, hp.party_number customer_number, hca.account_number, hp.email_address, mc.segment1 CATEGORY, msi.segment1 item, msi.description item_description , 'Task' source_type, ext_user.source_name ' (' fnd.user_name ')' user_name, TO_CHAR (jtf.creation_date, 'DD-MON-YYYY HH24:MI:SS') creation_date, typ.NAME ': ' sts.NAME ': ' jtf.description "Logs And Notes" FROM jtf_tasks_vl jtf, fnd_user fnd, jtf_rs_resource_extns ext_user, jtf_task_types_vl typ, jtf_task_statuses_vl sts, cs_incidents_all_b csi, cs_incident_types_tl csit, cs_incident_statuses_tl csis, cs_incident_severities_tl cssv, jtf_rs_groups_tl jrg, jtf_rs_resource_extns_tl jrre, hz_parties hp, hz_cust_accounts hca, mtl_categories mc,
WHERE AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND AND
mtl_system_items_b msi, mtl_parameters mp source_object_type_code = 'SR' 1=1 -- source_object_id = :p_incident_id ext_user.user_id = fnd.user_id jtf.created_by = fnd.user_id typ.task_type_id = jtf.task_type_id sts.task_status_id = jtf.task_status_id source_object_id = csi.incident_id csi.incident_type_id = csit.incident_type_id csi.incident_status_id = csis.incident_status_id csi.incident_severity_id = cssv.incident_severity_id csi.owner_group_id = jrg.GROUP_ID(+) csi.incident_owner_id = jrre.resource_id(+) csi.customer_id = hp.party_id csi.account_id = hca.cust_account_id csi.category_id = mc.category_id(+) csi.inventory_item_id = msi.inventory_item_id(+) msi.organization_id = mp.organization_id(+) (mp.organization_code = 'MLB' OR mp.organization_code IS NULL)
