SELECT r.last_name, r.first_name, r.external_id, wipunion.emplyhomedepart, m.departcode, nbi.customer, cst.dsti_proj_ref, nbi.project_name, wipunion.STATUS, wipunion.TRANSCLASS, wipunion.COMPANY_CODE, wipunion.PROJECT_CODE, wipunion.RESOURCE_CODE, wipunion.CHARGE_CODE, chg.prname, wipunion.EXTERNAL_ID, wipunion.QUANTITY, (wipunion.Quantity/7.5) as Days, wipunion.TRANSDATE, wipunion.DEPARTCODE
FROM
(SELECT STATUS, TRANSCLASS, EMPLYHOMEDEPART, COMPANY_CODE, PROJECT_CODE, RESOURCE_CODE, CHARGE_CODE, EXTERNAL_ID, QUANTITY, TRANSDATE, DEPARTCODE
FROM NIKU.PPA_WIP
WHERE RESOURCE_CODE in (select r.unique_name from NIKU.srm_resources r, NIKU.pac_mnt_resources d WHERE r.UNIQUE_NAME = d.resource_code AND
d.departcode in ('UK1205', 'UK1206', 'UK1209', 'UK1211', 'UK1212', 'UK1213', 'UK1214', 'UK1216', 'UK1217', 'UK1218') OR (d.departcode IN ('UK1000', 'UK1104', 'UK1105', 'UK1106') AND SUBSTR(r.unique_name, 1, 3) IN ('THA', 'AUS', 'BOS')))
UNION
SELECT 0, TRANSCLASS, EMPLYHOMEDEPART, COMPANY_CODE, PROJECT_CODE, RESOURCE_CODE, CHARGE_CODE, EXTERNAL_ID, QUANTITY, TRANSDATE, DEPARTCODE
FROM NIKU.PPA_TRANSCONTROL
WHERE RESOURCE_CODE in (select r.unique_name from NIKU.srm_resources r, NIKU.pac_mnt_resources d WHERE r.UNIQUE_NAME = d.resource_code AND
d.departcode in ('UK1205', 'UK1206', 'UK1209', 'UK1211', 'UK1212', 'UK1213', 'UK1214', 'UK1216', 'UK1217', 'UK1218') OR (d.departcode IN ('UK1000', 'UK1104', 'UK1105', 'UK1106') AND SUBSTR(r.unique_name, 1, 3) IN ('THA', 'AUS', 'BOS')))) wipunion,
NIKU.pac_mnt_resources m,
NIKU.srm_resources r,
NIKU.nbi_project_current_facts nbi,
NIKU.odf_ca_project cst,
NIKU.prchargecode chg
WHERE wipunion.project_code = nbi.project_code AND
wipunion.resource_code = m.resource_code AND
cst.id = nbi.project_ID AND
wipunion.charge_code = chg.prexternalid AND
r.UNIQUE_NAME = m.resource_code