SELECT ptd.person_id,
ptd.date_day,
ptd.work_code_id,
p.vo_person_category_id,
ptd.is_locked,
'0201' AS company,
TO_CHAR(pd.date_day,'MM') AS MONTH,
'1' AS order_number,
LPAD(p.company_nr,6,'0') AS person_number,
'1' AS treatment_code,
'3' AS TYPE,
wc.short_code AS bonus_code,
SUM(p.transport_allowance) AS euro_value,
LPAD('0',42,'0') AS blank
FROM pd_trf_day ptd, pd_day pd, person p, work_code wc
WHERE ptd.date_day = pd.date_day
AND ptd.person_id = pd.person_id
AND pd.person_id = p.person_id
AND ptd.work_code_id = wc.work_code_id
AND wc.short_code = '043'
AND wc.vehicle_owner_id = 101393
--and wc.work_code_std_category_id = 1
AND pd.day_journey_role IS NULL
AND p.person_status_id = 0
GROUP BY ptd.person_id,ptd.date_day,ptd.work_code_id,p.vo_person_category_id,ptd.is_locked,LPAD(p.company_nr,6,'0'),TO_CHAR(pd.date_day,'MM'),wc.short_code,'0201','1','1','3',LPAD('0',42,'0')
HAVING SUM(p.transport_allowance) > 0
UNION
SELECT ptd.person_id,
ptd.date_day,
ptd.work_code_id,
p.vo_person_category_id,
ptd.is_locked,
'0201' AS company,
TO_CHAR(pd.date_day,'MM') AS MONTH,
'1' AS order_number,
LPAD(p.company_nr,6,'0') AS person_number,
'1' AS treatment_code,
'3' AS TYPE,
wc.short_code AS bonus_code,
14 * COUNT(CASE WHEN (SELECT COUNT(*) FROM pd_trf_day WHERE date_day = ptd.date_day AND person_id = ptd.person_id AND work_code_id = 104519) > 0 THEN 1 END) AS euro_value,
LPAD('0',42,'0') AS blank
FROM pd_trf_day ptd, pd_day pd, person p, work_code wc
WHERE ptd.date_day = pd.date_day
AND ptd.person_id = pd.person_id
AND pd.person_id = p.person_id
AND ptd.work_code_id = wc.work_code_id
AND wc.short_code = '030'
--and wc.work_code_std_category_id = 1
AND wc.vehicle_owner_id = 101393
AND p.person_status_id = 0
GROUP BY ptd.person_id, ptd.date_day, ptd.work_code_id,p.vo_person_category_id,ptd.is_locked,LPAD(p.company_nr,6,'0'),TO_CHAR(pd.date_day,'MM'),wc.short_code,'0201','1','1','3',LPAD('0',42,'0')
HAVING COUNT(CASE WHEN ptd.work_code_id = 104519 THEN 1 END) > 0;