select

(select name from apps.hr_operating_units where organization_id =

inc.org_id) ou_name,
(SELECT group_name FROM APPS.jtf_rs_groups_tl where group_id =
inc.owner_group_id and language='US' ) inc_group,
inc.incident_number legacy_incident_number,
csr.repair_number legacy_so_number,
(select name from apps.csd_repair_types_vl where repair_type_id =
csr.repair_type_id) service_type,
msi.segment1 item,
--msi.primary_unit_of_measure uom,
msi.primary_uom_code uom,
csr.quantity qty,
(SELECT meaning from apps.fnd_lookups fl,
apps.csd_flow_statuses_b cfs
where lookup_type = 'CSD_REPAIR_FLOW_STATUS'
AND fl.lookup_code = cfs.flow_status_code
and cfs.flow_status_id = csr.flow_status_id) SO_STATUS,
DECODE(csr.approval_status,'A','Approved') estimate_approved,
(select instance_number from apps.csi_item_instances where
instance_id =csr.customer_product_id) instance,
(SELECT decode(csr.customer_product_id, '', csr.serial_number,
cp.serial_number)
FROM apps.csi_item_instances cp where instance_id
=csr.customer_product_id)serial_num,
csr.lot_number,
csr.currency_code currency,
(select name from apps.qp_list_headers where list_header_id =
csr.price_list_header_id) default_price_list,
csr.default_po_num,
( select resource_name from apps.CS_SR_OWNERS_V where 1=1 and
resource_id = csr.resource_id
AND resource_type='RS_EMPLOYEE') SO_OWNER,
--(select project_number from apps.pa_projects_all_basic_v where
project_id = csr.project_id) so_project,
csr.problem_description service_desc,
csr.creation_date date_created,
--NULL so_service_org,
csr.resolve_by_date,
csr.promise_date,
csr.date_closed,
csr.attribute_category context_value,
-- csr.attribute1 CLEANING_RECEIVED_DATE,
-- csr.attribute2 process_fluid,
-- csr.attribute3 TROUBLESHOOT_FAILURE_ANALYSIS,
-- csr.attribute11 FLAG_NEW_UNUSED,
-- csr.attribute12 FLAG_ADV_REPLACEMENT,
-- csr.attribute14 CUSTOMER_REQ_DATE,
-- csr.attribute10 FORMAL_REPORT,
-- csr.attribute13 ORIGINAL_UNIT_SHIP_DATE,
null process_fluid,
null TROUBLESHOOT_FAILURE_ANALYSIS,
null CLEANING_RECEIVED_DATE,
null FLAG_NEW_UNUSED,
null FLAG_ADV_REPLACEMENT,
null CUSTOMER_REQ_DATE,
null FORMAL_REPORT,
null ORIGINAL_UNIT_SHIP_DATE,
DECODE(dpt.action_type,'RMA','Return',dpt.action_type) action_type,
DECODE(dpt.action_code,'CUST_PROD','Customer Item' ,dpt.action_code)
action_code,
stt.name service_activity,
(select order_number from apps.oe_order_headers_all where header_id =
edt.order_header_id) order_number,
(select line_number from apps.oe_order_lines_all where line_id =
edt.order_line_id) line_number,
dpt.prod_txn_status status,
msi.segment1 logistics_item,
(select organization_code from apps.mtl_parameters where organization_id =
edt.transaction_inventory_org) warehouse,
(select name from apps.qp_list_headers where list_header_id =
csr.price_list_header_id) price_list,
edt.quantity_required logistics_qty,
--msi.primary_unit_of_measure logistics_uom,
msi.primary_uom_code logistics_uom,
edt.after_warranty_cost price,
edt.item_revision revision,
dpt.source_serial_number ,
(select instance_number from apps.csi_item_instances where instance_id =
dpt.source_instance_id) source_instance,
dpt.non_source_serial_number ,
(select instance_number from apps.csi_item_instances where instance_id =
dpt.non_source_instance_id) non_source_instance_number,
dpt.lot_number logistics_lot_number,
dpt.sub_inventory subinv,
(select concatenated_segments from apps.mtl_item_locations_kfv where
inventory_location_id=dpt.locator_id) locator,
edt.return_reason_code,
decode(edt.line_category_code, 'RETURN', edt.installed_cp_return_by_date,
nvl(edt.new_cp_return_by_date, edt.installed_cp_return_by_date)) return_by_date,
(select contract_number from apps.okc_k_headers_b okh where okh.id =
edt.contract_id AND okh.start_date IS NOT NULL AND okh.end_date IS NOT NULL)
contract_number,
csr.default_po_num po_number,
(SELECT(decode(hca.account_number, NULL, '',(hca.account_number || ' - ' ||
hz.party_name)))
from apps.hz_cust_accounts hca,
apps.hz_parties hz
where 1=1
AND edt.invoice_to_account_id = hca.cust_account_id
AND edt.bill_to_party_id = hca.party_id
AND edt.bill_to_party_id = hz.party_id) account,
(select hl.address1||','||hl.address2||','||hl.address3||','||
hl.address4||','||hl.city||','||hl.county||','||hl.postal_code||','||
hl.province||','||hl.state||','||hl.country
FROM apps.hz_locations hl,
apps.hz_party_sites hps
WHERE 1=1
AND inc.bill_to_site_id = hps.party_site_id
AND hps.location_id = hl.location_id ) bill_to,
(select hl.address1||','||hl.address2||','||hl.address3||','||
hl.address4||','||hl.city||','||hl.county||','||hl.postal_code||','||
hl.province||','||hl.state||','||hl.country
FROM apps.hz_locations hl,
apps.hz_party_sites hps
WHERE 1=1
AND inc.ship_to_site_id = hps.party_site_id
AND hps.location_id = hl.location_id) ship_to,
msi.description item_desc,
(select name from apps.wsh_picking_rules where picking_rule_id =
dpt.picking_rule_id) picking_rule_name
from
apps.cs_incidents_all_b inc,
apps.csd_repairs csr,
apps.mtl_system_items_b msi,
apps.cs_incident_statuses incs,
apps.csd_product_transactions dpt,
apps.cs_estimate_details edt,
apps.cs_txn_billing_types sbt,
apps.cs_transaction_types_vl stt
where 1=1
--AND inc_type.language = 'US'
--AND inc.incident_type_id = inc_type.incident_type_id
--AND hca.party_id = hp.party_id
--AND inc.account_id = hca.cust_account_id
and csr.incident_id = inc.incident_id
AND csr.inventory_item_id = msi.inventory_item_id(+)
AND msi.organization_id = 85
AND inc.incident_status_id = incs.incident_status_id
AND dpt.repair_line_id = csr.repair_line_id
AND dpt.estimate_detail_id = edt.estimate_detail_id
AND sbt.txn_billing_type_id = edt.txn_billing_type_id
AND sbt.transaction_type_id = stt.transaction_type_id
--AND dpt.action_type = 'SHIP'
AND EXISTS (SELECT 1 FROM APPS.jtf_rs_groups_tl where group_id = inc.owner_group_id
and language='US' and group_name
IN ('IVS_Bake Instr',
'IVS_Baton Rouge',
'IVS_Charlotte',
'IVS_La Porte',
'IVS_Solon',
'IVS_Midland',
'IVS_Savannah',
'IVS_Kingsport',
'IVS_Eden Prairie'))
--AND NVL(incs.status_code,'OPEN') = 'OPEN'
AND NVL(incs.incident_status_id,1) NOT IN (1224,2,181,183,1462,4,182,123,124) --
(1224,2,181,183)
AND inc.org_id IN (91)
order by inc.incident_number