SELECT
	sal.asn_line_id,
	sal.display_asn_line_num,
	sal.asn_header_id,
	sah.asn_num AS delivery_order_no
FROM
	gsp_prd_view.sinv_asn_line sal
	LEFT JOIN gsp_prd_view.sodr_po_line_location spll ON sal.po_line_location_id = spll.po_line_location_id
	LEFT JOIN (
		SELECT
			* 
		FROM
			gsp_prd_view.sodr_po_header DST__0 
		WHERE
			DST__0.attribute_bigint2 IN (
				SELECT
					hual1.data_id 
				FROM
					gsp_prd_view.hiam_user_authority hua1
					LEFT JOIN gsp_prd_view.hiam_user_authority_line hual1 ON hua1.authority_id = hual1.authority_id 
				WHERE
					hua1.tenant_id = 10191 
					AND hua1.user_id = 24967 
					AND hua1.authority_type_code = 'UNIT' 
			) 
			OR EXISTS (
				SELECT
					1 
				FROM
					gsp_prd_view.hiam_user_authority hua1 
				WHERE
					hua1.tenant_id = 10191 
					AND hua1.user_id = 24967 
					AND hua1.authority_type_code = 'UNIT' 
					AND hua1.include_all_flag = 1 
			) 
			AND 1 = 1 
		) sph ON spll.po_header_id = sph.po_header_id
	LEFT JOIN (
		SELECT
			* 
		FROM
			gsp_prd_view.sinv_asn_header DST__1 
		WHERE
			DST__1.attribute_bigint4 IN (
				SELECT
					hual1.data_id 
				FROM
					gsp_prd_view.hiam_user_authority hua1
					LEFT JOIN gsp_prd_view.hiam_user_authority_line hual1 ON hua1.authority_id = hual1.authority_id 
				WHERE
					hua1.tenant_id = 10191 
					AND hua1.user_id = 24967 
					AND hua1.authority_type_code = 'UNIT' 
			) 
			OR EXISTS (
				SELECT
					1 
				FROM
					gsp_prd_view.hiam_user_authority hua1 
				WHERE
					hua1.tenant_id = 10191 
					AND hua1.user_id = 24967 
					AND hua1.authority_type_code = 'UNIT' 
					AND hua1.include_all_flag = 1 
			) 
			AND EXISTS (
				SELECT
					sfscr.store_id 
				FROM
					gsp_prd_view.hiam_user_authority hua1
					LEFT JOIN gsp_prd_view.hiam_user_authority_line hual1 ON hua1.authority_id = hual1.authority_id
					LEFT JOIN gsp_prd_view.scux_fr_store_create_record sfscr ON sfscr.unit_id = hual1.data_id 
				WHERE
					hua1.tenant_id = 10191 
					AND hua1.user_id = 24967 
					AND hua1.authority_type_code = 'UNIT' 
					AND sfscr.store_id = DST__1.attribute_bigint3 
					LIMIT 1 
			) 
	) sah ON sal.asn_header_id = sah.asn_header_id
	LEFT JOIN gsp_prd_view.sodr_po_line spl ON spll.po_line_id = spl.po_line_id
	LEFT JOIN gsp_prd_view.sslm_ext_supplier_site sess ON sah.supplier_site_id = sess.supplier_site_id
	LEFT JOIN gsp_prd_view.hpfm_inv_organization hio ON sah.inv_organization_id = hio.organization_id
	LEFT JOIN gsp_prd_view.hpfm_purchase_agent hpa ON sal.agent_id = hpa.purchase_agent_id
	LEFT JOIN gsp_prd_view.sslm_external_supplier ses ON sah.supplier_id = ses.supplier_id
	LEFT JOIN gsp_prd_view.smdm_item_partner_rel hipr ON sal.item_id = hipr.partner_item_id AND sal.tenant_id = hipr.tenant_id AND sah.company_id = hipr.partner_company_id
	LEFT JOIN gsp_prd_view.smdm_item hi ON hipr.item_id = hi.item_id
	LEFT JOIN gsp_prd_view.smdm_item hil ON hil.item_id = sal.item_id
	LEFT JOIN gsp_prd_view.smdm_item_tl hilt ON hil.item_id = hilt.item_id AND hilt.lang = 'en_US'
	LEFT JOIN gsp_prd_view.hpfm_company hc ON sah.company_id = hc.company_id
	LEFT JOIN gsp_prd_view.hpfm_company hcy ON sah.supplier_company_id = hcy.company_id
	LEFT JOIN ( SELECT MAX( version_number ) version_number, company_id FROM gsp_prd_view.spfm_company_basic WHERE process_status = 'COMPLETE' GROUP BY company_id ) shc_max ON shc_max.company_id = hcy.source_key
	LEFT JOIN gsp_prd_view.spfm_company_basic scb ON scb.company_id = shc_max.company_id AND scb.version_number = shc_max.version_number
	LEFT JOIN gsp_prd_view.spfm_company_basic_tl scbt ON scbt.company_basic_id = scb.company_basic_id AND scbt.lang = 'en_US'
	LEFT JOIN gsp_prd_view.hpfm_purchase_organization hpo ON hpo.purchase_org_id = sah.purchase_org_id
	LEFT JOIN ( SELECT * FROM gsp_prd_view.sprm_pr_line DST__2 WHERE 1 = 1 ) sprl ON spl.pr_line_id = sprl.pr_line_id
	LEFT JOIN (
		SELECT
			* 
		FROM
			gsp_prd_view.sprm_pr_header DST__3 
		WHERE
			DST__3.attribute_bigint2 IN (
			SELECT
				hual1.data_id 
			FROM
				gsp_prd_view.hiam_user_authority hua1
				LEFT JOIN gsp_prd_view.hiam_user_authority_line hual1 ON hua1.authority_id = hual1.authority_id 
			WHERE
				hua1.tenant_id = 10191 
				AND hua1.user_id = 24967 
				AND hua1.authority_type_code = 'UNIT' 
			) 
			OR EXISTS (
			SELECT
				1 
			FROM
				gsp_prd_view.hiam_user_authority hua1 
			WHERE
				hua1.tenant_id = 10191 
				AND hua1.user_id = 24967 
				AND hua1.authority_type_code = 'UNIT' 
				AND hua1.include_all_flag = 1 
			) 
	) sprh ON sprl.pr_header_id = sprh.pr_header_id
	LEFT JOIN gsp_prd_view.scux_fr_mdm_store sums ON sums.store_id = sah.attribute_bigint3
	LEFT JOIN gsp_prd_view.hpfm_region_tl hrt ON hrt.region_id = sums.district_id AND hrt.lang = 'en_US'
	LEFT JOIN gsp_prd_view.hpfm_region_tl hrtp ON hrtp.region_id = sums.province_id AND hrtp.lang = 'en_US'
	LEFT JOIN gsp_prd_view.hpfm_country hco ON hco.country_code = sums.country_code
	LEFT JOIN gsp_prd_view.hpfm_country_tl hcot ON hco.country_id = hcot.country_id AND hcot.lang = 'en_US'
	LEFT JOIN gsp_prd_view.smdm_uom su ON sal.uom_id = su.uom_id
	LEFT JOIN gsp_prd_view.smdm_uom whu ON sal.weight_uom_id = whu.uom_id
	LEFT JOIN (
		SELECT
			* 
		FROM
			gsp_prd_view.scux_fr_packing_header DST__4 
		WHERE
			EXISTS (
				SELECT
					hu.unit_code AS store_code 
				FROM
					gsp_prd_view.hiam_user_authority hua
					JOIN gsp_prd_view.hiam_user_authority_line hual ON hual.authority_id = hua.authority_id
					JOIN gsp_prd_view.hpfm_unit hu ON hu.unit_id = hual.data_id AND hu.unit_type_code = 'Store' 
				WHERE
					hua.user_id = 24967 
					AND hua.authority_type_code = 'unit' 
					AND hu.unit_code = DST__4.store_code 
			) 
	) sfph ON sfph.packing_header_id = sah.attribute_bigint8
	LEFT JOIN gsp_prd_view.scux_fr_tracking_report_do sftrd ON sftrd.tenant_id = sah.tenant_id AND sftrd.do_id = sah.asn_header_id
	LEFT JOIN gsp_prd_view.scux_fr_tracking_report sftr ON sftrd.report_id = sftr.report_id
	LEFT JOIN gsp_prd_view.sodr_order_type sot ON sot.order_type_id = sph.po_type_id 
WHERE
	sah.tenant_id = 10191
	AND sal.creation_date >= '2024-04-18 15:00:00'
	AND sah.attribute_datetime1 >= '2024-06-30 15:00:00' 
	AND sah.attribute_datetime1 <= '2024-07-31 14:59:59'
ORDER BY
	sah.asn_header_id DESC 
LIMIT 10