SELECT DISTINCT jtv.task_id, csivs.creation_date, hou.NAME "Operating Unit",
TO_CHAR (csivs.creation_date,
'DD-MM-YYYY HH24:MI:SSAM'
) "Date & Time",
hpp.person_name "Call logged by", csstss.NAME "Type of Call",
csisb.NAME "SR Status", csivs.incident_number "SR Number",
hcp.phone_number, jtv.attribute8 part_replace,
jtv.attribute9 service_charge,
jtv.attribute10 service_charge_in_rs,
(SELECT user_name
FROM fnd_user
WHERE user_id = csivs.last_updated_by) modify_by,
(SELECT user_name
FROM fnd_user
WHERE user_id = csivs.created_by) created_by,
hz.party_name "Customer Name",
(SELECT city
FROM hz_locations
WHERE location_id =
(SELECT a.location_id
FROM hz_party_sites a,
cs_incidents_all_b b
WHERE a.party_site_id = b.incident_location_id
AND b.incident_number = csivs.incident_number))
LOCATION,
(SELECT address1
|| ','
|| address2
|| ' , '
|| address3
|| ' '
|| city
|| ' '
|| state
FROM hz_locations
WHERE location_id =
(SELECT a.location_id
FROM hz_party_sites a, cs_incidents_all_b b
WHERE a.party_site_id = b.incident_location_id
AND b.incident_number = csivs.incident_number))
"Customer Address",
(SELECT DISTINCT segment1
FROM mtl_system_items_b
WHERE inventory_item_id =
csivs.inventory_item_id
AND organization_id = csivs.inv_organization_id)
"Item Name",
(SELECT DISTINCT description
FROM mtl_system_items_b
WHERE inventory_item_id =
csivs.inventory_item_id
AND organization_id = csivs.inv_organization_id)
"Item Description",
cii.serial_number "Serial Number",
csivs1.summary "Problem Summary",
csivs.problem_code "Problem Codes",
jtv.task_number "Task Number",
TO_CHAR (jtv.creation_date,
'DD-MM-YYYY HH24:MI:SSAM'
) "Task Date",
jttl.NAME "Task Type", jtl.task_name "Subject",
jtstl.NAME "Status", jnv.notes "Task Notes",
TO_CHAR
(jtv.planned_start_date,
'DD-MM-YYYY HH24:MI:SSAM'
) "Planned Start Date and Time",
TO_CHAR
(jtv.planned_end_date,
'DD-MM-YYYY HH24:MI:SSAM'
) "Planned Date End and Time",
TO_CHAR
(jtv.scheduled_start_date,
'DD-MM-YYYY HH24:MI:SSAM'
) "Scheduled Start Date and Time",
TO_CHAR
(jtv.scheduled_end_date,
'DD-MM-YYYY HH24:MI:SSAM'
) "Scheduled End Date and Time",
TO_CHAR
(jtv.actual_start_date,
'DD-MM-YYYY HH24:MI:SSAM'
) "Actual Start Date and Time",
TO_CHAR (jtv.actual_end_date,
'DD-MM-YYYY HH24:MI:SSAM'
) "Actual End Date and Time",
DECODE (jtv.task_number,
xx_mrt1_no (csivs.incident_id), xx_mrt1
(csivs.incident_id),
NULL
) "MTR1",
DECODE (jtv.task_number,
xx_mrt2_no (csivs.incident_id), xx_mrt2
(csivs.incident_id),
NULL
) "MTR2",
jtv.attribute1 "Report Signed By",
jtv.attribute2 "Contact No", jtv.attribute3 "Report Number",
jtv.attribute4 "Report Status",
jtv.attribute5 "Customer Remark",
jtv.attribute6 "Engineer Remark",
jtptl.description "Priority",
TO_CHAR (okh.start_date,
'DD-MM-YYYY HH24:MI:SSAM'
) "Warranty Start Date",
TO_CHAR (okh.end_date,
'DD-MM-YYYY HH24:MI:SSAM'
) "Warranty End date"
FROM cs_incidents_b_sec csivs,
hz_parties hz,
hr_operating_units hou,
cs_incident_statuses_tl csisb,
cs_incident_types_tl csstss,
jtf_tasks_b jtv,
jtf_task_types_tl jttl,
jtf_tasks_tl jtl,
jtf_task_statuses_tl jtstl,
jtf_task_priorities_tl jtptl,
jtf_notes_vl jnv,
jtf_task_all_assignments jtaa,
jtf_rs_resource_extns jrd,
csi_item_instances cii,
cs_incidents_all_tl csivs1,
cs_hz_sr_contact_points chsc,
hz_person_profiles hpp,
okc_k_headers_all_b okh,
hz_party_sites hzp,
hz_locations hzl,
fnd_user fs,
hz_contact_points hcp
WHERE TRUNC (csivs.creation_date)
BETWEEN NVL (:xx_dat_from, TRUNC (csivs.creation_date))
AND NVL (:xx_dat_to, TRUNC (csivs.creation_date))
AND hou.organization_id = NVL (:xx_og, hou.organization_id)
AND hz.party_id = NVL (:xx_pn, hz.party_id)
AND csivs.incident_number = NVL (:xx_in, csivs.incident_number)
AND (csivs.created_by = :xx_owner OR :xx_owner IS NULL)
AND (cii.serial_number = (:xx_sno) OR (:xx_sno IS NULL))
AND (csivs.inventory_item_id = :xx_iid OR :xx_iid IS NULL)
AND csivs.incident_type_id =
NVL (:xx_it_id, csivs.incident_type_id)
AND csivs.incident_status_id =
NVL (:xx_it_ty, csivs.incident_status_id)
AND (csivs.problem_code = :xx_pc OR :xx_pc IS NULL)
AND ( jtv.task_priority_id =
NVL (:xx_t_prio, jtv.task_priority_id)
OR :xx_t_prio IS NULL
)
AND ( jtv.task_type_id = NVL (:xx_t_type, jtv.task_type_id)
OR :xx_t_type IS NULL
)
AND ( jtv.task_status_id =
NVL (:xx_t_status, jtv.task_status_id)
OR :xx_t_status IS NULL
)
AND ( jtf_task_utl.get_owner (jtv.owner_type_code, jtv.owner_id) =
:xx_j_owner
OR :xx_j_owner IS NULL
)
AND (jrd.resource_id = :xx_ass OR :xx_ass IS NULL)
AND ( TRUNC (jtv.scheduled_start_date) =
NVL (:xx_sc_from, TRUNC (jtv.scheduled_start_date))
OR :xx_sc_from IS NULL
)
AND ( TRUNC (jtv.scheduled_end_date) =
NVL (:xx_sc_to, TRUNC (jtv.scheduled_end_date))
OR :xx_sc_to IS NULL
)
AND csivs.customer_id = hz.party_id
AND csivs.org_id = hou.organization_id
AND csivs.incident_status_id = csisb.incident_status_id
AND csivs.incident_type_id = csstss.incident_type_id
AND jtv.source_object_id(+) = csivs.incident_id
AND jtv.task_type_id = jttl.task_type_id(+)
AND jtv.task_id = jtl.task_id(+)
AND jtv.task_status_id = jtstl.task_status_id(+)
AND jtv.task_priority_id = jtptl.task_priority_id(+)
AND jtv.task_number = jnv.source_number(+)
AND jtv.task_id = jtaa.task_id(+)
AND ( jtaa.assignee_role IN ('ASSIGNEE', 'OWNER')
OR jtaa.assignee_role IS NULL
)
AND jtaa.resource_id = jrd.resource_id(+)
AND csivs.incident_id = csivs1.incident_id
AND csivs1.LANGUAGE = USERENV ('LANG')
AND csivs.customer_product_id = cii.instance_id(+)
AND csivs.contract_id = okh.ID(+)
AND chsc.incident_id(+) = csivs.incident_id
AND (chsc.primary_flag = 'Y' OR chsc.primary_flag IS NULL)
AND (chsc.party_id - 1) = hpp.party_id(+)
AND hzp.party_site_id(+) = csivs.ship_to_site_id
AND hzp.location_id = hzl.location_id(+)
AND hcp.contact_point_id(+) = chsc.contact_point_id
AND csivs.created_by = fs.user_id
ORDER BY csivs.creation_date, jtv.task_id
No comments:
Post a Comment