SELECT
--  EXTRACTVALUE(tpp.xml_data, '/*/PBS_LI_TEXT') tpp_li_text
--, EXTRACTVALUE(tpp.xml_data, '/*/PBS_LI_TEXT') tpp_li_text

 count(*)--bpld.pl_id, tpp.pc_id, mpp.pc_id, pr.pr_id, EXTRACTVALUE(pr.xml_data, '/*/PR_TYPE'), CASE WHEN (mpp.pc_id != EXTRACTVALUE(pr.xml_data, '/*/MPP_LIST/MPP[1]/MPP_ID') AND EXTRACTVALUE(pr.xml_data, '/*/PR_TYPE') != 'INFUS') THEN 'ERROR' END mpp_pr_match
FROM pbsmgr.xview_pbs_product_listing bpld
JOIN pbsmgr.pbs_concept_details tpp
    ON tpp.pc_id = bpld.pbs_concept_id
    AND tpp.pbs_concept_type = 'TPP'

JOIN pbsmgr.pbs_concept_details mpp
    ON EXTRACTVALUE(tpp.xml_data, '/*/PBS_MPP_CONCEPT') = mpp.pc_id
    AND mpp.pbs_concept_type = 'MPP'
    AND bpld.start_datetime >= mpp.start_datetime AND NVL(bpld.end_datetime, dt.high) <= NVL(mpp.end_datetime, dt.high)

LEFT JOIN pbsmgr.pbs_prescribing_rule_details pr
    ON pr.pr_id = bpld.pbs_pr_id
    AND (
      (bpld.start_datetime >= pr.start_datetime AND NVL(bpld.end_datetime, dt.high) <= NVL(pr.end_datetime, dt.high))
      OR
      (pr.start_datetime BETWEEN bpld.start_datetime AND NVL(bpld.end_datetime, dt.high) AND (bpld.start_datetime <= pr.start_datetime AND NVL(bpld.end_datetime, dt.high) >= NVL(pr.end_datetime, dt.high))) --OR pr.end_datetime BETWEEN  bpld.start_datetime AND NVL(bpld.end_datetime, dt.high))
    )

--TPP/MPP ON MPP_PCID AND LIST_DATE



--LEFT JOIN pbsmgr.pbs_prescribing_rule_details pr
--  ON pr.pr_id = bpld.pbs_pr_id
--  AND pr.status_control IS NOT NULL
WHERE bpld.status_control IS NOT NULL
--ORDER BY mpp_pr_match NULLS LAST
--ORDER BY pr.pr_id NULLS FIRST, 1, 2, 3