SELECT center
	,bldg
	,QUEUE
	,ticket
	,date_issued
	,time_issued
	,first_no_answer
	,second_no_answer
	,third_no_answer
	,time_answered
	,time_finished
	,wait_time
	,CASE 
		WHEN end_of_day_status = 'FINISH'
			AND finished_by = 'USER'
			AND appt_type IS NOT NULL
			AND motk_appo_time <> trunc(motk_appo_time)
			AND trunc(motk_appo_time) = trunc(motk_time_issued)
			AND motk_time_issued < motk_appo_time
			AND QUEUE IN (
				SELECT moqu_queue
				FROM mo_queue
				WHERE moqu_adjust_waittime = 'Y'
				)
			THEN round(decode(first_no_answer, NULL, (time_answered - motk_appo_time) * 86400, (first_no_answer - motk_appo_time) * 86400))
		ELSE round(decode(first_no_answer, NULL, wait_time, (first_no_answer - motk_time_issued) * 86400))
		END adjusted_wait_time
	,service_time
	,total_time
	,ROLE || ' (' || motk_issue_machine || ')' issued_from
	,user_name || ' at ' || motk_update_machine last_action_taken_by
	,finished_by
	,end_of_day_status
	,customer_name
	,case_no_at_fdr
	,cin_at_fdr
	,ssn_at_fdr
	,decode(QUEUE, 'NCA Intake', apin_casenumber, NULL) case_no_from_intake
	,caseload
	,appt_type
	,appt_time
	,visitor_no
	,decode(apin_immediate_interview, 'Y', 'SMDAY', flag) flag
	,language_at_FDR
	,decode(ROLE, 'FDR', acm_passes, NULL) acm_passes
	,decode(ROLE, 'FDR', adv_passes, NULL) adv_passes
	,decode(ROLE, 'FDR', cca_passes, NULL) cca_passes
	,decode(ROLE, 'FDR', med_passes, NULL) med_passes
	,decode(ROLE, 'FDR', tlr_passes, NULL) tlr_passes
	,decode(ROLE, 'FDR', oth_passes, NULL) oth_passes
	,ROLE
	,motk_time_issued date_issued_sort
	,to_date(time_issued, 'hh:mi:ss AM') time_issued_sort
	,motk_appo_time appt_time_sort
	,(
		SELECT code_description ROLE
		FROM code
		WHERE code_item_name = 'MO_ROLE'
			AND code_wms_code = MOTK_ISSUE_MACHINE_ROLE
		) AS MOTK_ISSUE_MACHINE_ROLE
FROM (
	SELECT motk_bldg_id bldg
		,motk_cntr_id center
		,motk_queue QUEUE
		,motk_tkt_no ticket
		,motk_time_issued
		,to_char(motk_time_issued, 'mm/dd/yyyy') date_issued
		,to_char(motk_time_issued, 'hh:mi:ss AM') time_issued
		,motk_time_answered time_answered
		,motk_time_finished time_finished
		,motk_wait_time wait_time
		,motk_activity_time service_time
		,motk_total_time total_time
		,motk_status end_of_day_status
		,decode(motk_finisher, 'NO ANS', '3rd NoAnswer', NULL, 'NO ONE', motk_finisher) finished_by
		,motk_issue_machine
		,motk_update_machine
		,motk_update_user
		,motk_first_name || ' ' || motk_last_name customer_name
		,motk_caseload caseload
		,motk_appo_code appt_type
		,motk_appo_time
		,decode(motk_appo_time, trunc(motk_appo_time), to_char(motk_appo_time, 'mm/dd/yyyy'), to_char(motk_appo_time, 'mm/dd/yyyy hh:mi AM')) appt_time
		,motk_visit_id visitor_no
		,motk_flag flag
		,motk_link_id
		,motk_language
		,MOTK_ISSUE_MACHINE_ROLE
	FROM mo_ticket_history
	WHERE motk_time_issued BETWEEN :ar_start
			AND :ar_end
	)
	,(
		SELECT mona_bldg_id
			,mona_cntr_id
			,mona_queue
			,mona_tkt_no
			,mona_time_issued
			,mona_count no_answer_count
			,mona_time_noanswered first_no_answer
		FROM mo_noanswer_history
		WHERE mona_count = 1
			AND mona_time_issued BETWEEN :ar_start
				AND :ar_end
		) a
	,(
		SELECT mona_bldg_id
			,mona_cntr_id
			,mona_queue
			,mona_tkt_no
			,mona_time_issued
			,mona_count no_answer_count
			,mona_time_noanswered second_no_answer
		FROM mo_noanswer_history
		WHERE mona_count = 2
			AND mona_time_issued BETWEEN :ar_start
				AND :ar_end
		) b
	,(
		SELECT mona_bldg_id
			,mona_cntr_id
			,mona_queue
			,mona_tkt_no
			,mona_time_issued
			,mona_count no_answer_count
			,mona_time_noanswered third_no_answer
		FROM mo_noanswer_history
		WHERE mona_count = 3
			AND mona_time_issued BETWEEN :ar_start
				AND :ar_end
		) c
	,(
		SELECT movi_bldg_id
			,movi_visit_id
			,movi_case_no case_no_at_fdr
			,movi_cin cin_at_fdr
			,movi_ssn ssn_at_fdr
		FROM mo_visitor_history
		)
	,(
		SELECT centerid
			,apin_applid
			,apin_casenumber
			,apin_immediate_interview
		FROM fs_location
			,fs_application_intake
		WHERE apin_locid = locid
		)
	,(
		SELECT mcfg_machine_name
			,mcfg_role
		FROM comp_cfg
		)
	,(
		SELECT code_wms_code
			,code_description ROLE
		FROM code
		WHERE code_item_name = 'MO_ROLE'
		)
	,(
		SELECT code_pos_code
			,code_description language_at_FDR
		FROM code
		WHERE code_item_name = 'CLVL_LANGUAGE'
		)
	,(
		SELECT pers_id
			,pers_first_name || ' ' || pers_last_name user_name
		FROM personnel
		WHERE (
				pers_id
				,pers_seq_no
				) IN (
				SELECT pers_id
					,max(pers_seq_no)
				FROM personnel
				GROUP BY pers_id
				)
		)
	,(
		SELECT mops_visit_id
			,sum(mops_count) acm_passes
		FROM mo_pass_history
		WHERE mops_visitor_type = '002'
			AND (
				mops_visit_id
				,mops_bldg_id
				) IN (
				SELECT DISTINCT motk_visit_id
					,motk_bldg_id
				FROM mo_ticket_history
				WHERE motk_time_issued BETWEEN :ar_start
						AND :ar_end
				)
		GROUP BY mops_visit_id
		) d
	,(
		SELECT mops_visit_id
			,sum(mops_count) adv_passes
		FROM mo_pass_history
		WHERE mops_visitor_type = '003'
			AND (
				mops_visit_id
				,mops_bldg_id
				) IN (
				SELECT DISTINCT motk_visit_id
					,motk_bldg_id
				FROM mo_ticket_history
				WHERE motk_time_issued BETWEEN :ar_start
						AND :ar_end
				)
		GROUP BY mops_visit_id
		) e
	,(
		SELECT mops_visit_id
			,sum(mops_count) cca_passes
		FROM mo_pass_history
		WHERE mops_visitor_type = '004'
			AND (
				mops_visit_id
				,mops_bldg_id
				) IN (
				SELECT DISTINCT motk_visit_id
					,motk_bldg_id
				FROM mo_ticket_history
				WHERE motk_time_issued BETWEEN :ar_start
						AND :ar_end
				)
		GROUP BY mops_visit_id
		) f
	,(
		SELECT mops_visit_id
			,sum(mops_count) med_passes
		FROM mo_pass_history
		WHERE mops_visitor_type = '005'
			AND (
				mops_visit_id
				,mops_bldg_id
				) IN (
				SELECT DISTINCT motk_visit_id
					,motk_bldg_id
				FROM mo_ticket_history
				WHERE motk_time_issued BETWEEN :ar_start
						AND :ar_end
				)
		GROUP BY mops_visit_id
		) g
	,(
		SELECT mops_visit_id
			,sum(mops_count) tlr_passes
		FROM mo_pass_history
		WHERE mops_visitor_type = '006'
			AND (
				mops_visit_id
				,mops_bldg_id
				) IN (
				SELECT DISTINCT motk_visit_id
					,motk_bldg_id
				FROM mo_ticket_history
				WHERE motk_time_issued BETWEEN :ar_start
						AND :ar_end
				)
		GROUP BY mops_visit_id
		) h
	,(
		SELECT mops_visit_id
			,sum(mops_count) oth_passes
		FROM mo_pass_history
		WHERE mops_visitor_type = '999'
			AND (
				mops_visit_id
				,mops_bldg_id
				) IN (
				SELECT DISTINCT motk_visit_id
					,motk_bldg_id
				FROM mo_ticket_history
				WHERE motk_time_issued BETWEEN :ar_start
						AND :ar_end
				)
		GROUP BY mops_visit_id
		) i
WHERE bldg = a.mona_bldg_id(+)
	AND center = a.mona_cntr_id(+)
	AND QUEUE = a.mona_queue(+)
	AND ticket = a.mona_tkt_no(+)
	AND motk_time_issued = a.mona_time_issued(+)
	AND bldg = b.mona_bldg_id(+)
	AND center = b.mona_cntr_id(+)
	AND QUEUE = b.mona_queue(+)
	AND ticket = b.mona_tkt_no(+)
	AND motk_time_issued = b.mona_time_issued(+)
	AND bldg = c.mona_bldg_id(+)
	AND center = c.mona_cntr_id(+)
	AND QUEUE = c.mona_queue(+)
	AND ticket = c.mona_tkt_no(+)
	AND motk_time_issued = c.mona_time_issued(+)
	AND visitor_no = movi_visit_id(+)
	AND motk_issue_machine = mcfg_machine_name(+)
	AND mcfg_role = code_wms_code(+)
	AND motk_language = code_pos_code(+)
	AND motk_update_user = pers_id(+)
	AND center = centerid(+)
	AND to_number(motk_link_id) = apin_applid(+)
	AND visitor_no = d.mops_visit_id(+)
	AND visitor_no = e.mops_visit_id(+)
	AND visitor_no = f.mops_visit_id(+)
	AND visitor_no = g.mops_visit_id(+)
	AND visitor_no = h.mops_visit_id(+)
	AND visitor_no = i.mops_visit_id(+)
	AND end_of_day_status = 'FINISH'
	AND finished_by = 'USER'
ORDER BY visitor_no
	,motk_time_issued
	,ticket
	,center
	,QUEUE