SELECT convert(varchar, cs.CustomerUpdatedTs, 6) AS Date,
card.Name_ID AS First_name,


card.No_ID AS ID_NUMBHER,
card.Additional_DOB             AS DOB,
card.Home_Ph         AS Home_PHONE,
card.Mobile_Ph       AS Mobile_Phone,
kr.kode_kurir        AS Cheker_Name,
card.CC_HSBC_Level   AS Card_type,
convert(varchar,pod.pod_date, 6)        AS Tanggal_pod,
card.Email           AS Email_Addres,
card.Additional_Name_ID AS ADDIONAL,
convert(varchar,pod.pod_code, 6)          AS GHI_CODE,
cs.SF_ID             AS Sales_Force_ID,
0 AS STATUS,
IIF(card.Additional_Name_ID IS NULL, 0, 1) AS STATUS_2


FROM t_gn_customer cs
LEFT JOIN t_gn_pod pod ON cs.CustomerId=pod.customer_id
LEFT JOIN t_gn_app_card card ON card.Customer_ID=cs.CustomerId
LEFT JOIN t_lk_kurir kr ON kr.id_kurir=pod.name_kurir
WHERE cs.StatusCourier = 'Send to CCR'
AND cs.out_region=0

UNION

SELECT convert(varchar, cs.CustomerUpdatedTs, 6) AS Date,
card.Additional_Name_ID AS First_name,




 card.Additional_No_ID AS ID_NUMBHER,
card.Additional_DOB             AS DOB,
'-' AS          Home_PHONE,
card.Additional_Mobile_Ph       AS Mobile_Phone,
kr.kode_kurir        AS Cheker_Name,
card.CC_HSBC_Level   AS Card_type,
 convert(varchar,pod.pod_date, 6)         AS Tanggal_pod,
card.Email           AS Email_Addres,
card.Additional_Name_ID AS ADDIONAL,
convert(varchar,pod.pod_code, 6)          AS GHI_CODE,
cs.SF_ID             AS Sales_Force_ID,
1 AS STATUS,
IIF(card.Additional_Name_ID IS NULL, 0, 1) AS STATUS_2


FROM t_gn_customer cs
LEFT JOIN t_gn_pod pod ON cs.CustomerId=pod.customer_id
LEFT JOIN t_gn_app_card card ON card.Customer_ID=cs.CustomerId
LEFT JOIN t_lk_kurir kr ON kr.id_kurir=pod.name_kurir
WHERE cs.StatusCourier = 'Send to CCR' AND card.Additional_Name_ID != '' AND card.Additional_No_ID !=''
AND cs.out_region=0