SELECT DISTINCT
get_dec_val(od.POLICY_NUMBER) || ', ' || TO_CHAR(TO_DATE(get_dec_val(od.GAURANTOR_DOB), 'YYYYMMDD'), 'MM/DD/YYYY')  as IDENTIFIER,
to_char(to_date(get_dec_val(od.PATIENT_DOB_NEW), 'DD/MM/YYYY'), 'MM/DD/YYYY') as patient_dob,
NVL(od.GAURANTOR_RELATIONSHIP, 'Self') as patient_relationship,
o.ACCESSION_W_PREFIX,
to_char(to_date(get_dec_val(o.COLLECTDT_NEW),'DD-MON-YYYY'),'MM/DD/YYYY') as collection_date,
od.POLICY_NUMBER,
b3.INSURANCE_TYPE,
NVL(i.LAB_NPI, b3.BOX_33_LAB_NPI) AS NPI,
NVL(i.LAB_NAME, b3.BOX_33_LAB_NAME) AS LAB_NAME,
NVL(i.LAB_ADDRESS, b3.BOX_33_LAB_ADDRESS) AS LAB_ADDRESS,
NVL(i.LAB_ADDRESS_2, b3.box_33_lab_address_2) AS LAB_ADDRESS_2,
NVL(i.CITY, b3.BOX_33_CITY) AS city,
NVL(i.STATE, b3.BOX_33_STATE) AS state,
od.PHYSICIAN_NAME

FROM
hv_user.hv_labgen_orders o

LEFT JOIN hv_user.hv_labgen_ord_det_denorm od
ON o.ACCESSION_W_PREFIX = od.ACCESSION_W_PREFIX

LEFT JOIN hv_user.hv_labgen_ordered_tests ot
ON o.SOURCE_CLINIC = ot.SOURCE_CLINIC
AND o.RECNUM = ot.ORDER_RECNUM

LEFT JOIN hv_user.CMD_TEST_TYPE_REFERENCE cttr
ON od.TEST_TYPE_NAME = cttr.CIQ_TEST_TYPE

LEFT JOIN hv_user.CMD_PAYER_REFERENCE pr
ON od.INSURANCE_RECNUM = pr.LABGEN_PAYER_ID
AND SUBSTR(o.ACCESSION_W_PREFIX, 1, 3) = pr.ACCESSION_NUMBER_STARTS_WITH

LEFT JOIN hv_user.cmd_box33_reference b3
ON (
	(SUBSTR(o.ACCESSION_W_PREFIX, 1, 3) = b3.ACCESSION_NUMBER_STARTS_WITH )
	AND (NVL(ot.REFERENCE_LABNAME, 'NULLVALUE') = b3.SENDOUT_LAB )
	AND (b3.SALES_GROUP = (CASE WHEN SUBSTR(od.SALES_GROUP, 1, 3) in ('QMP', 'MCH', 'Pre') THEN 'W2' ELSE '1099' END))
	AND (b3.TEST_TYPE = (case when cttr.B33_TEST_TYPE = 'PGX' then 'PGx' ELSE NVL(cttr.B33_TEST_TYPE, 'Blood') END) )  /* Issue with null values? */
	AND (b3.INSURANCE_TYPE = pr.INSURANCE_TYPE) )

LEFT JOIN hv_user.cmd_ins_form i
ON od.POLICY_NUMBER = i.POLICY_NUMBER

--WHERE b3.INSURANCE_TYPE in ('BCBS', 'United Healthcare', 'Workman''s Comp')
WHERE b3.BOX_33_LAB_NAME in ('BCBS FORM', 'UHC RULES TABLE','WORKMANS COMP FORM')
AND to_date(od.FINAL_DATE,'DD-MON-YY') >= sysdate - 60


ORDER BY NPI DESC, IDENTIFIER ASC;