Copy_RC_Rx_Get Drugs Dictionary (modified - EN)

SELECT DISTINCT 
    I.id, I.sctid, I.name, I.name_original,
    MI.id, MI.form, I_1.dosage, I_1.is_primary,
    MED.container, MED.package_qty, MED.package_min_qty
FROM innms I 												-- 1st level: Innm  
	INNER JOIN ingredients I_1								-- Ingredients for INNM_DOSAGE
		ON I_1.innm_child_id = I.id
	INNER JOIN medications MI								-- 2nd level: Medication (type = INNM_DOSAGE)
		ON MI.type == INNM_DOSAGE
			AND MI.id == I_1.parent_id	
	INNER JOIN ingredients I_2								-- Ingredients for INNM_DOSAGE
		ON I_2.medication_child_id = MI.id
			AND	I_2.is_primary == TRUE
	INNER JOIN medications MED								-- 3rd level: Medication (type = BRAND) 
		ON MED.type == BRAND 
			AND MED.id == I_2.parent_id
	LEFT JOIN program_medications PM						-- optional filter by medical_program
    	on MED.id = pm.medication_id 
			AND PM.is_active = TRUE
  	LEFT JOIN medical_programs MP
    	on PM.medical_program_id = MP.id 
			AND MP.is_active = TRUE
WHERE I.is_active == TRUE 
	AND MI.is_active == TRUE 
	AND MED.is_active == TRUE
	AND (I.id = $.innm_id OR $.innm_id IS NULL)
	AND (I.sctid = $.innm_sctid OR $.innm_sctid IS NULL)
	AND (I.name = $.innm_name OR $.innm_name IS NULL)
	AND (MI.id = $.innm_dosage_id OR $.innm_dosage_id IS NULL)
	AND (MED.code_atc = $.medication_code_atc OR $.medication_code_atc IS NULL)
	AND (MI.name = $.innm_dosage_name OR $.innm_dosage_name IS NULL)
	AND (MI.form = $.innm_dosage_form OR $.innm_dosage_form IS NULL)
 	AND (MP.id = $.medical_program_id OR $.medical_program_id IS NULL)
	AND (MI.mr_blank_type = $.mr_form_type OR $.mr_blank_type IS NULL)
	AND (MI.dosage_form_is_dosed = $.dosage_form_is_dosed OR $.dosage_form_is_dosed IS NULL)