== Physical Plan ==
AdaptiveSparkPlan (34)
+- == Final Plan ==
DeserializeToObject (32)
+- TableCacheQueryStage (31), Statistics(sizeInBytes=16.2 KiB, rowCount=24)
+- InMemoryTableScan (1)
+- InMemoryRelation (2)
+- AdaptiveSparkPlan (30)
+- Project (29)
+- BroadcastHashJoin LeftOuter BuildRight (28)
:- InMemoryTableScan (3)
: +- InMemoryRelation (4)
: +- AdaptiveSparkPlan (18)
: +- Project (17)
: +- BroadcastHashJoin Inner BuildLeft (16)
: :- BroadcastExchange (11)
: : +- Project (10)
: : +- Filter (9)
: : +- InMemoryTableScan (5)
: : +- InMemoryRelation (6)
: : +- * Project (8)
: : +- * Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] (7)
: +- Exchange (15)
: +- Project (14)
: +- Project (13)
: +- Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1] (12)
+- BroadcastExchange (27)
+- Project (26)
+- Filter (25)
+- InMemoryTableScan (19)
+- InMemoryRelation (20)
+- AdaptiveSparkPlan (24)
+- Project (23)
+- BroadcastHashJoin Inner BuildLeft (22)
:- BroadcastExchange (21)
: +- Project (10)
: +- Filter (9)
: +- InMemoryTableScan (5)
: +- InMemoryRelation (6)
: +- * Project (8)
: +- * Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] (7)
+- Exchange (15)
+- Project (14)
+- Project (13)
+- Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1] (12)
+- == Initial Plan ==
DeserializeToObject (33)
+- InMemoryTableScan (1)
+- InMemoryRelation (2)
+- AdaptiveSparkPlan (30)
+- Project (29)
+- BroadcastHashJoin LeftOuter BuildRight (28)
:- InMemoryTableScan (3)
: +- InMemoryRelation (4)
: +- AdaptiveSparkPlan (18)
: +- Project (17)
: +- BroadcastHashJoin Inner BuildLeft (16)
: :- BroadcastExchange (11)
: : +- Project (10)
: : +- Filter (9)
: : +- InMemoryTableScan (5)
: : +- InMemoryRelation (6)
: : +- * Project (8)
: : +- * Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] (7)
: +- Exchange (15)
: +- Project (14)
: +- Project (13)
: +- Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1] (12)
+- BroadcastExchange (27)
+- Project (26)
+- Filter (25)
+- InMemoryTableScan (19)
+- InMemoryRelation (20)
+- AdaptiveSparkPlan (24)
+- Project (23)
+- BroadcastHashJoin Inner BuildLeft (22)
:- BroadcastExchange (21)
: +- Project (10)
: +- Filter (9)
: +- InMemoryTableScan (5)
: +- InMemoryRelation (6)
: +- * Project (8)
: +- * Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] (7)
+- Exchange (15)
+- Project (14)
+- Project (13)
+- Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1] (12)
(1) InMemoryTableScan
Output [52]: [NetworkID#18085020, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085467, revision_version#18069038, config_name#18085479, parent_launch_config_ref#18072153, Child_Node#18085481, Parent_id#18085482, Levels#18072181, tier#18072156, node_status#18072154, data_state#18072155, product_name#18085835, product_type#18085838, reference_code#18072157, SupplierName#18085840, SupplierCity#18085841, SupplierState#18085842, SupplierCountry#18085895, SupplierAddress#18085897, SupplierLat#18085898, SupplierLng#18085899, Supplier_declaration_RefID#18085900, supplier_type#18072177, supplier_id#18085901, supplier_first_created_date#18085902, supplier_declared_company#18087363, preferrd_supplier_id#18087364, preferrd_supplier_name#18087365, dedup_reference_id#18087367, DirectIndirectSupplier#18087368, Direct_Supplier_Name#18087370, SupplierUID#18087372, nominated_node#18072178, child_network_id#18087374, child_network_status#18087375, ParentSupplierName#18087376, ParentSupplierCity#18087379, ParentSupplierState#18087381, ParentSupplierCountry#18087384, ParentSupplierAddress#18087386, ParentSupplierLat#18087389, ParentSupplierLng#18087392, submission_status#18087395, is_latest#18088128, is_latest_submitted#18088131, source#18088134, source_type#18072158, node_update_ts#18088137, is_unknown_supplier#18073668, update_ts#18088404]
Arguments: [NetworkID#18085020, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085467, revision_version#18069038, config_name#18085479, parent_launch_config_ref#18072153, Child_Node#18085481, Parent_id#18085482, Levels#18072181, tier#18072156, node_status#18072154, data_state#18072155, product_name#18085835, product_type#18085838, reference_code#18072157, SupplierName#18085840, SupplierCity#18085841, SupplierState#18085842, SupplierCountry#18085895, SupplierAddress#18085897, SupplierLat#18085898, SupplierLng#18085899, Supplier_declaration_RefID#18085900, ... 28 more fields]
(2) InMemoryRelation
Arguments: [NetworkID#18085020, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085467, revision_version#18069038, config_name#18085479, parent_launch_config_ref#18072153, Child_Node#18085481, Parent_id#18085482, Levels#18072181, tier#18072156, node_status#18072154, data_state#18072155, product_name#18085835, product_type#18085838, reference_code#18072157, SupplierName#18085840, SupplierCity#18085841, SupplierState#18085842, SupplierCountry#18085895, SupplierAddress#18085897, SupplierLat#18085898, SupplierLng#18085899, Supplier_declaration_RefID#18085900, ... 28 more fields], CachedRDDBuilder(org.apache.spark.sql.execution.columnar.DefaultCachedBatchSerializer@730ae8bf,StorageLevel(disk, memory, deserialized, 1 replicas),AdaptiveSparkPlan isFinalPlan=true
+- == Final Plan ==
*(2) Project [summary__NetworkID#18079169 AS NetworkID#18085020, brand_context_id#18069035, company_id#18069036, coalesce(launch_config_ref#18069037, ) AS launch_config_ref#18085467, revision_version#18069038, CASE WHEN (trim(regexp_replace(regexp_replace(summary__config_name#18078599, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(summary__config_name#18078599, , , 1), {2,}, , 1), None) END AS config_name#18085479, parent_launch_config_ref#18072153, _id#18072147 AS Child_Node#18085481, parent_id#18072152 AS Parent_id#18085482, Levels#18072181, tier#18072156, node_status#18072154, data_state#18072155, resolved_product_name#18072487 AS product_name#18085835, CASE WHEN (trim(regexp_replace(regexp_replace(product_name#18072159, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(product_name#18072159, , , 1), {2,}, , 1), None) END AS product_type#18085838, reference_code#18072157, resolved_supplier_display_name#18073870 AS SupplierName#18085840, resolved_supplier_city#18072713 AS SupplierCity#18085841, resolved_supplier_state#18072893 AS SupplierState#18085842, resolved_supplier_country#18073080 AS SupplierCountry#18085895, resolved_supplier_address#18073368 AS SupplierAddress#18085897, data_address_lat#18072173 AS SupplierLat#18085898, data_address_lng#18072174 AS SupplierLng#18085899, resolved_supplier_ref#18073566 AS Supplier_declaration_RefID#18085900, ... 28 more fields]
+- *(2) BroadcastHashJoin [parent_id#18072152, brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [_p_id#18081610, _p_brand#18081681, _p_company#18081682, _p_launch#18081683, _p_revision#18081684], LeftOuter, BuildRight, false
:- TableCacheQueryStage 0
: +- InMemoryTableScan [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__submission_status#18078605, summary__is_latest#18078750, summary__source#18079131, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173, _id#18072147, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, data_address_lat#18072173, data_address_lng#18072174, supplier_type#18072177, nominated_node#18072178, ... 17 more fields]
: +- InMemoryRelation [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields], StorageLevel(disk, memory, deserialized, 1 replicas)
: +- AdaptiveSparkPlan isFinalPlan=false
: +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields]
: +- BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151], Inner, BuildLeft, false
: :- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, smallint, true]),false), [plan_id=4617506]
: : +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18078599, config_version#18069041 AS summary__config_version#18078601, entity_id#18069042 AS summary__entity_id#18078603, submission_status#18069043 AS summary__submission_status#18078605, is_latest#18069044 AS summary__is_latest#18078750, is_editable#18069045 AS summary__is_editable#18078752, launch_date#18069046 AS summary__launch_date#18078754, expiry_date#18069047 AS summary__expiry_date#18078756, submitted_date#18069048 AS summary__submitted_date#18078757, create_ts#18069049 AS summary__create_ts#18079123, update_ts#18069050 AS summary__update_ts#18079125, create_date#18069051 AS summary__create_date#18079127, update_date#18069052 AS summary__update_date#18079129, source#18069053 AS summary__source#18079131, recurrence_count#18069054 AS summary__recurrence_count#18079133, priority#18069055 AS summary__priority#18079135, remarks#18069056 AS summary__remarks#18079137, latest_source#18069057 AS summary__latest_source#18079165, launch_request_name#18069058 AS summary__launch_request_name#18079167, NetworkID#18069039 AS summary__NetworkID#18079169, ... 2 more fields]
: : +- Filter ((((NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465) AND isnotnull(brand_context_id#18069035)) AND isnotnull(company_id#18069036)) AND isnotnull(launch_config_ref#18069037)) AND isnotnull(revision_version#18069038))
: : +- InMemoryTableScan [NetworkID#18069039, brand_context_id#18069035, company_id#18069036, config_name#18069040, config_version#18069041, create_date#18069051, create_ts#18069049, direct_supplier_name#18069091, entity_id#18069042, expiry_date#18069047, is_editable#18069045, is_latest#18069044, latest_source#18069057, launch_config_ref#18069037, launch_date#18069046, launch_request_name#18069058, priority#18069055, recurrence_count#18069054, remarks#18069056, revision_version#18069038, source#18069053, submission_status#18069043, submitted_date#18069048, supplier_uid#18069060, ... 2 more fields], [NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465), isnotnull(brand_context_id#18069035), isnotnull(company_id#18069036), isnotnull(launch_config_ref#18069037), isnotnull(revision_version#18069038)]
: : +- InMemoryRelation [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields], StorageLevel(disk, memory, deserialized, 1 replicas)
: : +- *(1) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields]
: : +- *(1) Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [brand_context_id#18069035,company_id#18069036,launch_config_ref#18069037,revision_version#18069038,NetworkID#18069039,config_name#18069040,config_version#18069041,entity_id#18069042,submission_status#18069043,is_latest#18069044,is_editable#18069045,launch_date#18069046,expiry_date#18069047,submitted_date#18069048,create_ts#18069049,update_ts#18069050,create_date#18069051,update_date#18069052,source#18069053,recurrence_count#18069054,priority#18069055,remarks#18069056,latest_source#18069057,launch_request_name#18069058,... 4 more fields] PushedFilters: [], ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smalli...
: +- Exchange hashpartitioning(brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, 5), REPARTITION_BY_NUM, [plan_id=4617354]
: +- Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 27 more fields]
: +- Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 26 more fields]
: +- Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1] [_id#18072147,brand_context_id#18072148,company_id#18072149,launch_config_ref#18072150,revision_version#18072151,parent_id#18072152,parent_launch_config_ref#18072153,node_status#18072154,data_state#18072155,tier#18072156,reference_code#18072157,source_type#18072158,product_name#18072159,product_alias_name#18072160,supplier_dec_name#18072161,supplier_dec_city#18072162,supplier_dec_state#18072163,supplier_dec_country#18072164,supplier_dec_address#18072165,supplier_dec__address_line2#18072166,data_supplier_name#18072167,data_address_city#18072168,data_address_state#18072169,data_address_country#18072170,... 18 more fields] PushedFilters: [*IsNotNull(brand_context_id), *IsNotNull(company_id), *IsNotNull(launch_config_ref), *IsNotNull(..., ReadSchema: struct<_id:string,brand_context_id:string,company_id:string,launch_config_ref:string,revision_ver...
+- BroadcastQueryStage 2
+- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, string, true], input[4, smallint, true]),false), [plan_id=4618173]
+- *(1) Project [_id#18081902 AS _p_id#18081610, brand_context_id#18081873 AS _p_brand#18081681, company_id#18081874 AS _p_company#18081682, launch_config_ref#18081875 AS _p_launch#18081683, revision_version#18081876 AS _p_revision#18081684, resolved_supplier_display_name#18073870 AS _p_display_name#18081686, node_status#18081909 AS _p_node_status#18081687, data_state#18081910 AS _p_data_state#18081688, resolved_supplier_city#18072713 AS _p_city#18081689, resolved_supplier_state#18072893 AS _p_state#18081691, resolved_supplier_country#18073080 AS _p_country#18081693, resolved_supplier_address#18073368 AS _p_address#18081695, data_address_lat#18081928 AS _p_lat#18081697, data_address_lng#18081929 AS _p_lng#18081699]
+- *(1) Filter ((((isnotnull(_id#18081902) AND isnotnull(brand_context_id#18081873)) AND isnotnull(company_id#18081874)) AND isnotnull(launch_config_ref#18081875)) AND isnotnull(revision_version#18081876))
+- TableCacheQueryStage 1
+- InMemoryTableScan [_id#18081902, brand_context_id#18081873, company_id#18081874, data_address_lat#18081928, data_address_lng#18081929, data_state#18081910, launch_config_ref#18081875, node_status#18081909, resolved_supplier_address#18073368, resolved_supplier_city#18072713, resolved_supplier_country#18073080, resolved_supplier_display_name#18073870, resolved_supplier_state#18072893, revision_version#18081876], [isnotnull(_id#18081902), isnotnull(brand_context_id#18081873), isnotnull(company_id#18081874), isnotnull(launch_config_ref#18081875), isnotnull(revision_version#18081876)]
+- InMemoryRelation [brand_context_id#18081873, company_id#18081874, launch_config_ref#18081875, revision_version#18081876, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields], StorageLevel(disk, memory, deserialized, 1 replicas)
+- AdaptiveSparkPlan isFinalPlan=false
+- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields]
+- BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151], Inner, BuildLeft, false
:- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, smallint, true]),false), [plan_id=4617510]
: +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18078599, config_version#18069041 AS summary__config_version#18078601, entity_id#18069042 AS summary__entity_id#18078603, submission_status#18069043 AS summary__submission_status#18078605, is_latest#18069044 AS summary__is_latest#18078750, is_editable#18069045 AS summary__is_editable#18078752, launch_date#18069046 AS summary__launch_date#18078754, expiry_date#18069047 AS summary__expiry_date#18078756, submitted_date#18069048 AS summary__submitted_date#18078757, create_ts#18069049 AS summary__create_ts#18079123, update_ts#18069050 AS summary__update_ts#18079125, create_date#18069051 AS summary__create_date#18079127, update_date#18069052 AS summary__update_date#18079129, source#18069053 AS summary__source#18079131, recurrence_count#18069054 AS summary__recurrence_count#18079133, priority#18069055 AS summary__priority#18079135, remarks#18069056 AS summary__remarks#18079137, latest_source#18069057 AS summary__latest_source#18079165, launch_request_name#18069058 AS summary__launch_request_name#18079167, NetworkID#18069039 AS summary__NetworkID#18079169, ... 2 more fields]
: +- Filter ((((NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465) AND isnotnull(brand_context_id#18069035)) AND isnotnull(company_id#18069036)) AND isnotnull(launch_config_ref#18069037)) AND isnotnull(revision_version#18069038))
: +- InMemoryTableScan [NetworkID#18069039, brand_context_id#18069035, company_id#18069036, config_name#18069040, config_version#18069041, create_date#18069051, create_ts#18069049, direct_supplier_name#18069091, entity_id#18069042, expiry_date#18069047, is_editable#18069045, is_latest#18069044, latest_source#18069057, launch_config_ref#18069037, launch_date#18069046, launch_request_name#18069058, priority#18069055, recurrence_count#18069054, remarks#18069056, revision_version#18069038, source#18069053, submission_status#18069043, submitted_date#18069048, supplier_uid#18069060, ... 2 more fields], [NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465), isnotnull(brand_context_id#18069035), isnotnull(company_id#18069036), isnotnull(launch_config_ref#18069037), isnotnull(revision_version#18069038)]
: +- InMemoryRelation [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields], StorageLevel(disk, memory, deserialized, 1 replicas)
: +- *(1) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields]
: +- *(1) Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [brand_context_id#18069035,company_id#18069036,launch_config_ref#18069037,revision_version#18069038,NetworkID#18069039,config_name#18069040,config_version#18069041,entity_id#18069042,submission_status#18069043,is_latest#18069044,is_editable#18069045,launch_date#18069046,expiry_date#18069047,submitted_date#18069048,create_ts#18069049,update_ts#18069050,create_date#18069051,update_date#18069052,source#18069053,recurrence_count#18069054,priority#18069055,remarks#18069056,latest_source#18069057,launch_request_name#18069058,... 4 more fields] PushedFilters: [], ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smalli...
+- Exchange hashpartitioning(brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, 5), REPARTITION_BY_NUM, [plan_id=4617354]
+- Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 27 more fields]
+- Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 26 more fields]
+- Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1] [_id#18072147,brand_context_id#18072148,company_id#18072149,launch_config_ref#18072150,revision_version#18072151,parent_id#18072152,parent_launch_config_ref#18072153,node_status#18072154,data_state#18072155,tier#18072156,reference_code#18072157,source_type#18072158,product_name#18072159,product_alias_name#18072160,supplier_dec_name#18072161,supplier_dec_city#18072162,supplier_dec_state#18072163,supplier_dec_country#18072164,supplier_dec_address#18072165,supplier_dec__address_line2#18072166,data_supplier_name#18072167,data_address_city#18072168,data_address_state#18072169,data_address_country#18072170,... 18 more fields] PushedFilters: [*IsNotNull(brand_context_id), *IsNotNull(company_id), *IsNotNull(launch_config_ref), *IsNotNull(..., ReadSchema: struct<_id:string,brand_context_id:string,company_id:string,launch_config_ref:string,revision_ver...
+- == Initial Plan ==
Project [summary__NetworkID#18079169 AS NetworkID#18085020, brand_context_id#18069035, company_id#18069036, coalesce(launch_config_ref#18069037, ) AS launch_config_ref#18085467, revision_version#18069038, CASE WHEN (trim(regexp_replace(regexp_replace(summary__config_name#18078599, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(summary__config_name#18078599, , , 1), {2,}, , 1), None) END AS config_name#18085479, parent_launch_config_ref#18072153, _id#18072147 AS Child_Node#18085481, parent_id#18072152 AS Parent_id#18085482, Levels#18072181, tier#18072156, node_status#18072154, data_state#18072155, resolved_product_name#18072487 AS product_name#18085835, CASE WHEN (trim(regexp_replace(regexp_replace(product_name#18072159, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(product_name#18072159, , , 1), {2,}, , 1), None) END AS product_type#18085838, reference_code#18072157, resolved_supplier_display_name#18073870 AS SupplierName#18085840, resolved_supplier_city#18072713 AS SupplierCity#18085841, resolved_supplier_state#18072893 AS SupplierState#18085842, resolved_supplier_country#18073080 AS SupplierCountry#18085895, resolved_supplier_address#18073368 AS SupplierAddress#18085897, data_address_lat#18072173 AS SupplierLat#18085898, data_address_lng#18072174 AS SupplierLng#18085899, resolved_supplier_ref#18073566 AS Supplier_declaration_RefID#18085900, ... 28 more fields]
+- BroadcastHashJoin [parent_id#18072152, brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [_p_id#18081610, _p_brand#18081681, _p_company#18081682, _p_launch#18081683, _p_revision#18081684], LeftOuter, BuildRight, false
:- InMemoryTableScan [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__submission_status#18078605, summary__is_latest#18078750, summary__source#18079131, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173, _id#18072147, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, data_address_lat#18072173, data_address_lng#18072174, supplier_type#18072177, nominated_node#18072178, ... 17 more fields]
: +- InMemoryRelation [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields], StorageLevel(disk, memory, deserialized, 1 replicas)
: +- AdaptiveSparkPlan isFinalPlan=false
: +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields]
: +- BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151], Inner, BuildLeft, false
: :- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, smallint, true]),false), [plan_id=4617506]
: : +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18078599, config_version#18069041 AS summary__config_version#18078601, entity_id#18069042 AS summary__entity_id#18078603, submission_status#18069043 AS summary__submission_status#18078605, is_latest#18069044 AS summary__is_latest#18078750, is_editable#18069045 AS summary__is_editable#18078752, launch_date#18069046 AS summary__launch_date#18078754, expiry_date#18069047 AS summary__expiry_date#18078756, submitted_date#18069048 AS summary__submitted_date#18078757, create_ts#18069049 AS summary__create_ts#18079123, update_ts#18069050 AS summary__update_ts#18079125, create_date#18069051 AS summary__create_date#18079127, update_date#18069052 AS summary__update_date#18079129, source#18069053 AS summary__source#18079131, recurrence_count#18069054 AS summary__recurrence_count#18079133, priority#18069055 AS summary__priority#18079135, remarks#18069056 AS summary__remarks#18079137, latest_source#18069057 AS summary__latest_source#18079165, launch_request_name#18069058 AS summary__launch_request_name#18079167, NetworkID#18069039 AS summary__NetworkID#18079169, ... 2 more fields]
: : +- Filter ((((NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465) AND isnotnull(brand_context_id#18069035)) AND isnotnull(company_id#18069036)) AND isnotnull(launch_config_ref#18069037)) AND isnotnull(revision_version#18069038))
: : +- InMemoryTableScan [NetworkID#18069039, brand_context_id#18069035, company_id#18069036, config_name#18069040, config_version#18069041, create_date#18069051, create_ts#18069049, direct_supplier_name#18069091, entity_id#18069042, expiry_date#18069047, is_editable#18069045, is_latest#18069044, latest_source#18069057, launch_config_ref#18069037, launch_date#18069046, launch_request_name#18069058, priority#18069055, recurrence_count#18069054, remarks#18069056, revision_version#18069038, source#18069053, submission_status#18069043, submitted_date#18069048, supplier_uid#18069060, ... 2 more fields], [NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465), isnotnull(brand_context_id#18069035), isnotnull(company_id#18069036), isnotnull(launch_config_ref#18069037), isnotnull(revision_version#18069038)]
: : +- InMemoryRelation [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields], StorageLevel(disk, memory, deserialized, 1 replicas)
: : +- *(1) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields]
: : +- *(1) Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [brand_context_id#18069035,company_id#18069036,launch_config_ref#18069037,revision_version#18069038,NetworkID#18069039,config_name#18069040,config_version#18069041,entity_id#18069042,submission_status#18069043,is_latest#18069044,is_editable#18069045,launch_date#18069046,expiry_date#18069047,submitted_date#18069048,create_ts#18069049,update_ts#18069050,create_date#18069051,update_date#18069052,source#18069053,recurrence_count#18069054,priority#18069055,remarks#18069056,latest_source#18069057,launch_request_name#18069058,... 4 more fields] PushedFilters: [], ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smalli...
: +- Exchange hashpartitioning(brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, 5), REPARTITION_BY_NUM, [plan_id=4617354]
: +- Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 27 more fields]
: +- Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 26 more fields]
: +- Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1] [_id#18072147,brand_context_id#18072148,company_id#18072149,launch_config_ref#18072150,revision_version#18072151,parent_id#18072152,parent_launch_config_ref#18072153,node_status#18072154,data_state#18072155,tier#18072156,reference_code#18072157,source_type#18072158,product_name#18072159,product_alias_name#18072160,supplier_dec_name#18072161,supplier_dec_city#18072162,supplier_dec_state#18072163,supplier_dec_country#18072164,supplier_dec_address#18072165,supplier_dec__address_line2#18072166,data_supplier_name#18072167,data_address_city#18072168,data_address_state#18072169,data_address_country#18072170,... 18 more fields] PushedFilters: [*IsNotNull(brand_context_id), *IsNotNull(company_id), *IsNotNull(launch_config_ref), *IsNotNull(..., ReadSchema: struct<_id:string,brand_context_id:string,company_id:string,launch_config_ref:string,revision_ver...
+- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, string, true], input[4, smallint, true]),false), [plan_id=4617502]
+- Project [_id#18081902 AS _p_id#18081610, brand_context_id#18081873 AS _p_brand#18081681, company_id#18081874 AS _p_company#18081682, launch_config_ref#18081875 AS _p_launch#18081683, revision_version#18081876 AS _p_revision#18081684, resolved_supplier_display_name#18073870 AS _p_display_name#18081686, node_status#18081909 AS _p_node_status#18081687, data_state#18081910 AS _p_data_state#18081688, resolved_supplier_city#18072713 AS _p_city#18081689, resolved_supplier_state#18072893 AS _p_state#18081691, resolved_supplier_country#18073080 AS _p_country#18081693, resolved_supplier_address#18073368 AS _p_address#18081695, data_address_lat#18081928 AS _p_lat#18081697, data_address_lng#18081929 AS _p_lng#18081699]
+- Filter ((((isnotnull(_id#18081902) AND isnotnull(brand_context_id#18081873)) AND isnotnull(company_id#18081874)) AND isnotnull(launch_config_ref#18081875)) AND isnotnull(revision_version#18081876))
+- InMemoryTableScan [_id#18081902, brand_context_id#18081873, company_id#18081874, data_address_lat#18081928, data_address_lng#18081929, data_state#18081910, launch_config_ref#18081875, node_status#18081909, resolved_supplier_address#18073368, resolved_supplier_city#18072713, resolved_supplier_country#18073080, resolved_supplier_display_name#18073870, resolved_supplier_state#18072893, revision_version#18081876], [isnotnull(_id#18081902), isnotnull(brand_context_id#18081873), isnotnull(company_id#18081874), isnotnull(launch_config_ref#18081875), isnotnull(revision_version#18081876)]
+- InMemoryRelation [brand_context_id#18081873, company_id#18081874, launch_config_ref#18081875, revision_version#18081876, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields], StorageLevel(disk, memory, deserialized, 1 replicas)
+- AdaptiveSparkPlan isFinalPlan=false
+- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields]
+- BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151], Inner, BuildLeft, false
:- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, smallint, true]),false), [plan_id=4617510]
: +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18078599, config_version#18069041 AS summary__config_version#18078601, entity_id#18069042 AS summary__entity_id#18078603, submission_status#18069043 AS summary__submission_status#18078605, is_latest#18069044 AS summary__is_latest#18078750, is_editable#18069045 AS summary__is_editable#18078752, launch_date#18069046 AS summary__launch_date#18078754, expiry_date#18069047 AS summary__expiry_date#18078756, submitted_date#18069048 AS summary__submitted_date#18078757, create_ts#18069049 AS summary__create_ts#18079123, update_ts#18069050 AS summary__update_ts#18079125, create_date#18069051 AS summary__create_date#18079127, update_date#18069052 AS summary__update_date#18079129, source#18069053 AS summary__source#18079131, recurrence_count#18069054 AS summary__recurrence_count#18079133, priority#18069055 AS summary__priority#18079135, remarks#18069056 AS summary__remarks#18079137, latest_source#18069057 AS summary__latest_source#18079165, launch_request_name#18069058 AS summary__launch_request_name#18079167, NetworkID#18069039 AS summary__NetworkID#18079169, ... 2 more fields]
: +- Filter ((((NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465) AND isnotnull(brand_context_id#18069035)) AND isnotnull(company_id#18069036)) AND isnotnull(launch_config_ref#18069037)) AND isnotnull(revision_version#18069038))
: +- InMemoryTableScan [NetworkID#18069039, brand_context_id#18069035, company_id#18069036, config_name#18069040, config_version#18069041, create_date#18069051, create_ts#18069049, direct_supplier_name#18069091, entity_id#18069042, expiry_date#18069047, is_editable#18069045, is_latest#18069044, latest_source#18069057, launch_config_ref#18069037, launch_date#18069046, launch_request_name#18069058, priority#18069055, recurrence_count#18069054, remarks#18069056, revision_version#18069038, source#18069053, submission_status#18069043, submitted_date#18069048, supplier_uid#18069060, ... 2 more fields], [NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465), isnotnull(brand_context_id#18069035), isnotnull(company_id#18069036), isnotnull(launch_config_ref#18069037), isnotnull(revision_version#18069038)]
: +- InMemoryRelation [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields], StorageLevel(disk, memory, deserialized, 1 replicas)
: +- *(1) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields]
: +- *(1) Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [brand_context_id#18069035,company_id#18069036,launch_config_ref#18069037,revision_version#18069038,NetworkID#18069039,config_name#18069040,config_version#18069041,entity_id#18069042,submission_status#18069043,is_latest#18069044,is_editable#18069045,launch_date#18069046,expiry_date#18069047,submitted_date#18069048,create_ts#18069049,update_ts#18069050,create_date#18069051,update_date#18069052,source#18069053,recurrence_count#18069054,priority#18069055,remarks#18069056,latest_source#18069057,launch_request_name#18069058,... 4 more fields] PushedFilters: [], ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smalli...
+- Exchange hashpartitioning(brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, 5), REPARTITION_BY_NUM, [plan_id=4617354]
+- Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 27 more fields]
+- Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 26 more fields]
+- Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1] [_id#18072147,brand_context_id#18072148,company_id#18072149,launch_config_ref#18072150,revision_version#18072151,parent_id#18072152,parent_launch_config_ref#18072153,node_status#18072154,data_state#18072155,tier#18072156,reference_code#18072157,source_type#18072158,product_name#18072159,product_alias_name#18072160,supplier_dec_name#18072161,supplier_dec_city#18072162,supplier_dec_state#18072163,supplier_dec_country#18072164,supplier_dec_address#18072165,supplier_dec__address_line2#18072166,data_supplier_name#18072167,data_address_city#18072168,data_address_state#18072169,data_address_country#18072170,... 18 more fields] PushedFilters: [*IsNotNull(brand_context_id), *IsNotNull(company_id), *IsNotNull(launch_config_ref), *IsNotNull(..., ReadSchema: struct<_id:string,brand_context_id:string,company_id:string,launch_config_ref:string,revision_ver...
,None)
(3) InMemoryTableScan
Output [41]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__submission_status#18078605, summary__is_latest#18078750, summary__source#18079131, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173, _id#18072147, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, data_address_lat#18072173, data_address_lng#18072174, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188, resolved_product_name#18072487, resolved_supplier_city#18072713, resolved_supplier_state#18072893, resolved_supplier_country#18073080, resolved_supplier_address#18073368, resolved_supplier_ref#18073566, is_unknown_supplier#18073668, resolved_supplier_display_name#18073870]
Arguments: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__submission_status#18078605, summary__is_latest#18078750, summary__source#18079131, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173, _id#18072147, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, data_address_lat#18072173, data_address_lng#18072174, supplier_type#18072177, nominated_node#18072178, ... 17 more fields]
(4) InMemoryRelation
Arguments: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields], CachedRDDBuilder(org.apache.spark.sql.execution.columnar.DefaultCachedBatchSerializer@730ae8bf,StorageLevel(disk, memory, deserialized, 1 replicas),AdaptiveSparkPlan isFinalPlan=true
+- == Final Plan ==
*(3) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields]
+- *(3) BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151], Inner, BuildLeft, false
:- BroadcastQueryStage 2
: +- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, smallint, true]),false), [plan_id=4617896]
: +- *(2) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18078599, config_version#18069041 AS summary__config_version#18078601, entity_id#18069042 AS summary__entity_id#18078603, submission_status#18069043 AS summary__submission_status#18078605, is_latest#18069044 AS summary__is_latest#18078750, is_editable#18069045 AS summary__is_editable#18078752, launch_date#18069046 AS summary__launch_date#18078754, expiry_date#18069047 AS summary__expiry_date#18078756, submitted_date#18069048 AS summary__submitted_date#18078757, create_ts#18069049 AS summary__create_ts#18079123, update_ts#18069050 AS summary__update_ts#18079125, create_date#18069051 AS summary__create_date#18079127, update_date#18069052 AS summary__update_date#18079129, source#18069053 AS summary__source#18079131, recurrence_count#18069054 AS summary__recurrence_count#18079133, priority#18069055 AS summary__priority#18079135, remarks#18069056 AS summary__remarks#18079137, latest_source#18069057 AS summary__latest_source#18079165, launch_request_name#18069058 AS summary__launch_request_name#18079167, NetworkID#18069039 AS summary__NetworkID#18079169, ... 2 more fields]
: +- *(2) Filter ((((NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465) AND isnotnull(brand_context_id#18069035)) AND isnotnull(company_id#18069036)) AND isnotnull(launch_config_ref#18069037)) AND isnotnull(revision_version#18069038))
: +- TableCacheQueryStage 0
: +- InMemoryTableScan [NetworkID#18069039, brand_context_id#18069035, company_id#18069036, config_name#18069040, config_version#18069041, create_date#18069051, create_ts#18069049, direct_supplier_name#18069091, entity_id#18069042, expiry_date#18069047, is_editable#18069045, is_latest#18069044, latest_source#18069057, launch_config_ref#18069037, launch_date#18069046, launch_request_name#18069058, priority#18069055, recurrence_count#18069054, remarks#18069056, revision_version#18069038, source#18069053, submission_status#18069043, submitted_date#18069048, supplier_uid#18069060, ... 2 more fields], [NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465), isnotnull(brand_context_id#18069035), isnotnull(company_id#18069036), isnotnull(launch_config_ref#18069037), isnotnull(revision_version#18069038)]
: +- InMemoryRelation [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields], StorageLevel(disk, memory, deserialized, 1 replicas)
: +- *(1) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields]
: +- *(1) Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [brand_context_id#18069035,company_id#18069036,launch_config_ref#18069037,revision_version#18069038,NetworkID#18069039,config_name#18069040,config_version#18069041,entity_id#18069042,submission_status#18069043,is_latest#18069044,is_editable#18069045,launch_date#18069046,expiry_date#18069047,submitted_date#18069048,create_ts#18069049,update_ts#18069050,create_date#18069051,update_date#18069052,source#18069053,recurrence_count#18069054,priority#18069055,remarks#18069056,latest_source#18069057,launch_request_name#18069058,... 4 more fields] PushedFilters: [], ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smalli...
+- ShuffleQueryStage 1
+- Exchange hashpartitioning(brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, 5), REPARTITION_BY_NUM, [plan_id=4617830]
+- *(1) Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 27 more fields]
+- *(1) Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 26 more fields]
+- *(1) Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1] [_id#18072147,brand_context_id#18072148,company_id#18072149,launch_config_ref#18072150,revision_version#18072151,parent_id#18072152,parent_launch_config_ref#18072153,node_status#18072154,data_state#18072155,tier#18072156,reference_code#18072157,source_type#18072158,product_name#18072159,product_alias_name#18072160,supplier_dec_name#18072161,supplier_dec_city#18072162,supplier_dec_state#18072163,supplier_dec_country#18072164,supplier_dec_address#18072165,supplier_dec__address_line2#18072166,data_supplier_name#18072167,data_address_city#18072168,data_address_state#18072169,data_address_country#18072170,... 18 more fields] PushedFilters: [*IsNotNull(brand_context_id), *IsNotNull(company_id), *IsNotNull(launch_config_ref), *IsNotNull(..., ReadSchema: struct<_id:string,brand_context_id:string,company_id:string,launch_config_ref:string,revision_ver...
+- == Initial Plan ==
Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields]
+- BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151], Inner, BuildLeft, false
:- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, smallint, true]),false), [plan_id=4617358]
: +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18078599, config_version#18069041 AS summary__config_version#18078601, entity_id#18069042 AS summary__entity_id#18078603, submission_status#18069043 AS summary__submission_status#18078605, is_latest#18069044 AS summary__is_latest#18078750, is_editable#18069045 AS summary__is_editable#18078752, launch_date#18069046 AS summary__launch_date#18078754, expiry_date#18069047 AS summary__expiry_date#18078756, submitted_date#18069048 AS summary__submitted_date#18078757, create_ts#18069049 AS summary__create_ts#18079123, update_ts#18069050 AS summary__update_ts#18079125, create_date#18069051 AS summary__create_date#18079127, update_date#18069052 AS summary__update_date#18079129, source#18069053 AS summary__source#18079131, recurrence_count#18069054 AS summary__recurrence_count#18079133, priority#18069055 AS summary__priority#18079135, remarks#18069056 AS summary__remarks#18079137, latest_source#18069057 AS summary__latest_source#18079165, launch_request_name#18069058 AS summary__launch_request_name#18079167, NetworkID#18069039 AS summary__NetworkID#18079169, ... 2 more fields]
: +- Filter ((((NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465) AND isnotnull(brand_context_id#18069035)) AND isnotnull(company_id#18069036)) AND isnotnull(launch_config_ref#18069037)) AND isnotnull(revision_version#18069038))
: +- InMemoryTableScan [NetworkID#18069039, brand_context_id#18069035, company_id#18069036, config_name#18069040, config_version#18069041, create_date#18069051, create_ts#18069049, direct_supplier_name#18069091, entity_id#18069042, expiry_date#18069047, is_editable#18069045, is_latest#18069044, latest_source#18069057, launch_config_ref#18069037, launch_date#18069046, launch_request_name#18069058, priority#18069055, recurrence_count#18069054, remarks#18069056, revision_version#18069038, source#18069053, submission_status#18069043, submitted_date#18069048, supplier_uid#18069060, ... 2 more fields], [NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465), isnotnull(brand_context_id#18069035), isnotnull(company_id#18069036), isnotnull(launch_config_ref#18069037), isnotnull(revision_version#18069038)]
: +- InMemoryRelation [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields], StorageLevel(disk, memory, deserialized, 1 replicas)
: +- *(1) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields]
: +- *(1) Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [brand_context_id#18069035,company_id#18069036,launch_config_ref#18069037,revision_version#18069038,NetworkID#18069039,config_name#18069040,config_version#18069041,entity_id#18069042,submission_status#18069043,is_latest#18069044,is_editable#18069045,launch_date#18069046,expiry_date#18069047,submitted_date#18069048,create_ts#18069049,update_ts#18069050,create_date#18069051,update_date#18069052,source#18069053,recurrence_count#18069054,priority#18069055,remarks#18069056,latest_source#18069057,launch_request_name#18069058,... 4 more fields] PushedFilters: [], ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smalli...
+- Exchange hashpartitioning(brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, 5), REPARTITION_BY_NUM, [plan_id=4617354]
+- Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 27 more fields]
+- Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 26 more fields]
+- Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1] [_id#18072147,brand_context_id#18072148,company_id#18072149,launch_config_ref#18072150,revision_version#18072151,parent_id#18072152,parent_launch_config_ref#18072153,node_status#18072154,data_state#18072155,tier#18072156,reference_code#18072157,source_type#18072158,product_name#18072159,product_alias_name#18072160,supplier_dec_name#18072161,supplier_dec_city#18072162,supplier_dec_state#18072163,supplier_dec_country#18072164,supplier_dec_address#18072165,supplier_dec__address_line2#18072166,data_supplier_name#18072167,data_address_city#18072168,data_address_state#18072169,data_address_country#18072170,... 18 more fields] PushedFilters: [*IsNotNull(brand_context_id), *IsNotNull(company_id), *IsNotNull(launch_config_ref), *IsNotNull(..., ReadSchema: struct<_id:string,brand_context_id:string,company_id:string,launch_config_ref:string,revision_ver...
,None)
(5) InMemoryTableScan
Output [26]: [NetworkID#18069039, brand_context_id#18069035, company_id#18069036, config_name#18069040, config_version#18069041, create_date#18069051, create_ts#18069049, direct_supplier_name#18069091, entity_id#18069042, expiry_date#18069047, is_editable#18069045, is_latest#18069044, latest_source#18069057, launch_config_ref#18069037, launch_date#18069046, launch_request_name#18069058, priority#18069055, recurrence_count#18069054, remarks#18069056, revision_version#18069038, source#18069053, submission_status#18069043, submitted_date#18069048, supplier_uid#18069060, update_date#18069052, update_ts#18069050]
Arguments: [NetworkID#18069039, brand_context_id#18069035, company_id#18069036, config_name#18069040, config_version#18069041, create_date#18069051, create_ts#18069049, direct_supplier_name#18069091, entity_id#18069042, expiry_date#18069047, is_editable#18069045, is_latest#18069044, latest_source#18069057, launch_config_ref#18069037, launch_date#18069046, launch_request_name#18069058, priority#18069055, recurrence_count#18069054, remarks#18069056, revision_version#18069038, source#18069053, submission_status#18069043, submitted_date#18069048, supplier_uid#18069060, ... 2 more fields], [NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465), isnotnull(brand_context_id#18069035), isnotnull(company_id#18069036), isnotnull(launch_config_ref#18069037), isnotnull(revision_version#18069038)]
(6) InMemoryRelation
Arguments: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields], CachedRDDBuilder(org.apache.spark.sql.execution.columnar.DefaultCachedBatchSerializer@730ae8bf,StorageLevel(disk, memory, deserialized, 1 replicas),*(1) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields]
+- *(1) Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [brand_context_id#18069035,company_id#18069036,launch_config_ref#18069037,revision_version#18069038,NetworkID#18069039,config_name#18069040,config_version#18069041,entity_id#18069042,submission_status#18069043,is_latest#18069044,is_editable#18069045,launch_date#18069046,expiry_date#18069047,submitted_date#18069048,create_ts#18069049,update_ts#18069050,create_date#18069051,update_date#18069052,source#18069053,recurrence_count#18069054,priority#18069055,remarks#18069056,latest_source#18069057,launch_request_name#18069058,... 4 more fields] PushedFilters: [], ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smalli...
,None)
(7) Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [codegen id : 1]
Output [28]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, direct_supplier_name#18069059, supplier_uid#18069060, ou_division#18069061, ou_location#18069062]
ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smallint,NetworkID:string,config_name:string,config_version:boolean,entity_id:string,submission_status:string,is_latest:boolean,is_editable:boolean,launch_date:string,expiry_date:string,submitted_date:string,create_ts:timestamp,update_ts:timestamp,create_date:date,update_date:date,source:string,recurrence_count:int,priority:string,remarks:string,latest_source:string,launch_request_name:string,direct_supplier_name:string,supplier_uid:string,ou_division:string,ou_location:string>
(8) Project [codegen id : 1]
Output [28]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, CASE WHEN (trim(regexp_replace(regexp_replace(direct_supplier_name#18069059, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(direct_supplier_name#18069059, , , 1), {2,}, , 1), None) END AS direct_supplier_name#18069091, supplier_uid#18069060, ou_division#18069061, ou_location#18069062]
Input [28]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, direct_supplier_name#18069059, supplier_uid#18069060, ou_division#18069061, ou_location#18069062]
(9) Filter
Input [26]: [NetworkID#18069039, brand_context_id#18069035, company_id#18069036, config_name#18069040, config_version#18069041, create_date#18069051, create_ts#18069049, direct_supplier_name#18069091, entity_id#18069042, expiry_date#18069047, is_editable#18069045, is_latest#18069044, latest_source#18069057, launch_config_ref#18069037, launch_date#18069046, launch_request_name#18069058, priority#18069055, recurrence_count#18069054, remarks#18069056, revision_version#18069038, source#18069053, submission_status#18069043, submitted_date#18069048, supplier_uid#18069060, update_date#18069052, update_ts#18069050]
Condition : ((((NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465) AND isnotnull(brand_context_id#18069035)) AND isnotnull(company_id#18069036)) AND isnotnull(launch_config_ref#18069037)) AND isnotnull(revision_version#18069038))
(10) Project
Output [26]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18078599, config_version#18069041 AS summary__config_version#18078601, entity_id#18069042 AS summary__entity_id#18078603, submission_status#18069043 AS summary__submission_status#18078605, is_latest#18069044 AS summary__is_latest#18078750, is_editable#18069045 AS summary__is_editable#18078752, launch_date#18069046 AS summary__launch_date#18078754, expiry_date#18069047 AS summary__expiry_date#18078756, submitted_date#18069048 AS summary__submitted_date#18078757, create_ts#18069049 AS summary__create_ts#18079123, update_ts#18069050 AS summary__update_ts#18079125, create_date#18069051 AS summary__create_date#18079127, update_date#18069052 AS summary__update_date#18079129, source#18069053 AS summary__source#18079131, recurrence_count#18069054 AS summary__recurrence_count#18079133, priority#18069055 AS summary__priority#18079135, remarks#18069056 AS summary__remarks#18079137, latest_source#18069057 AS summary__latest_source#18079165, launch_request_name#18069058 AS summary__launch_request_name#18079167, NetworkID#18069039 AS summary__NetworkID#18079169, direct_supplier_name#18069091 AS summary__direct_supplier_name#18079170, supplier_uid#18069060 AS summary__supplier_uid#18079173]
Input [26]: [NetworkID#18069039, brand_context_id#18069035, company_id#18069036, config_name#18069040, config_version#18069041, create_date#18069051, create_ts#18069049, direct_supplier_name#18069091, entity_id#18069042, expiry_date#18069047, is_editable#18069045, is_latest#18069044, latest_source#18069057, launch_config_ref#18069037, launch_date#18069046, launch_request_name#18069058, priority#18069055, recurrence_count#18069054, remarks#18069056, revision_version#18069038, source#18069053, submission_status#18069043, submitted_date#18069048, supplier_uid#18069060, update_date#18069052, update_ts#18069050]
(11) BroadcastExchange
Input [26]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173]
Arguments: HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, smallint, true]),false), [plan_id=4617506]
(12) Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1]
Output [42]: [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, data_address_line1#18072171, data_address_line2#18072172, data_address_lat#18072173, data_address_lng#18072174, derived_supplier_dec_ref#18072175, data_supplier_default_ref#18072176, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, key_values#18072180, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188]
PushedFilters: [*IsNotNull(brand_context_id), *IsNotNull(company_id), *IsNotNull(launch_config_ref), *IsNotNull(revision_version)]
ReadSchema: struct<_id:string,brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smallint,parent_id:string,parent_launch_config_ref:string,node_status:smallint,data_state:string,tier:smallint,reference_code:string,source_type:string,product_name:string,product_alias_name:string,supplier_dec_name:string,supplier_dec_city:string,supplier_dec_state:string,supplier_dec_country:string,supplier_dec_address:string,supplier_dec__address_line2:string,data_supplier_name:string,data_address_city:string,data_address_state:string,data_address_country:string,data_address_line1:string,data_address_line2:string,data_address_lat:string,data_address_lng:string,derived_supplier_dec_ref:string,data_supplier_default_ref:string,supplier_type:string,nominated_node:boolean,update_ts:timestamp,key_values:string,Levels:int,decl_source:string,decl_supplier_id:string,decl_supplier_create_date:date,decl_supplier_company_id:string,decl_preferrd_supplier_id:string,decl_preferrd_supplier_name:string,decl_dedup_reference_id:string>
(13) Project
Output [50]: [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, data_address_line1#18072171, data_address_line2#18072172, data_address_lat#18072173, data_address_lng#18072174, derived_supplier_dec_ref#18072175, data_supplier_default_ref#18072176, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, key_values#18072180, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188, coalesce(CASE WHEN (trim(regexp_replace(regexp_replace(product_alias_name#18072160, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(product_alias_name#18072160, , , 1), {2,}, , 1), None) END, CASE WHEN (trim(regexp_replace(regexp_replace(product_name#18072159, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(product_name#18072159, , , 1), {2,}, , 1), None) END) AS resolved_product_name#18072487, CASE WHEN (trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_supplier_name#18072167) OR (data_supplier_name#18072167 = )) THEN null ELSE data_supplier_name#18072167 END, CASE WHEN (isnull(supplier_dec_name#18072161) OR (supplier_dec_name#18072161 = )) THEN null ELSE supplier_dec_name#18072161 END), , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_supplier_name#18072167) OR (data_supplier_name#18072167 = )) THEN null ELSE data_supplier_name#18072167 END, CASE WHEN (isnull(supplier_dec_name#18072161) OR (supplier_dec_name#18072161 = )) THEN null ELSE supplier_dec_name#18072161 END), , , 1), {2,}, , 1), None) END AS resolved_supplier_name#18072579, CASE WHEN (trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_address_city#18072168) OR (data_address_city#18072168 = )) THEN null ELSE data_address_city#18072168 END, CASE WHEN (isnull(supplier_dec_city#18072162) OR (supplier_dec_city#18072162 = )) THEN null ELSE supplier_dec_city#18072162 END), , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_address_city#18072168) OR (data_address_city#18072168 = )) THEN null ELSE data_address_city#18072168 END, CASE WHEN (isnull(supplier_dec_city#18072162) OR (supplier_dec_city#18072162 = )) THEN null ELSE supplier_dec_city#18072162 END), , , 1), {2,}, , 1), None) END AS resolved_supplier_city#18072713, CASE WHEN (trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_address_state#18072169) OR (data_address_state#18072169 = )) THEN null ELSE data_address_state#18072169 END, CASE WHEN (isnull(supplier_dec_state#18072163) OR (supplier_dec_state#18072163 = )) THEN null ELSE supplier_dec_state#18072163 END), , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_address_state#18072169) OR (data_address_state#18072169 = )) THEN null ELSE data_address_state#18072169 END, CASE WHEN (isnull(supplier_dec_state#18072163) OR (supplier_dec_state#18072163 = )) THEN null ELSE supplier_dec_state#18072163 END), , , 1), {2,}, , 1), None) END AS resolved_supplier_state#18072893, CASE WHEN (trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_address_country#18072170) OR (data_address_country#18072170 = )) THEN null ELSE data_address_country#18072170 END, CASE WHEN (isnull(supplier_dec_country#18072164) OR (supplier_dec_country#18072164 = )) THEN null ELSE supplier_dec_country#18072164 END), , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_address_country#18072170) OR (data_address_country#18072170 = )) THEN null ELSE data_address_country#18072170 END, CASE WHEN (isnull(supplier_dec_country#18072164) OR (supplier_dec_country#18072164 = )) THEN null ELSE supplier_dec_country#18072164 END), , , 1), {2,}, , 1), None) END AS resolved_supplier_country#18073080, CASE WHEN (trim(regexp_replace(regexp_replace(CASE WHEN NOT (coalesce(CASE WHEN (isnull(supplier_dec__address_line2#18072166) OR (supplier_dec__address_line2#18072166 = )) THEN null ELSE supplier_dec__address_line2#18072166 END, CASE WHEN (isnull(data_address_line2#18072172) OR (data_address_line2#18072172 = )) THEN null ELSE data_address_line2#18072172 END, ) = ) THEN concat(coalesce(CASE WHEN (isnull(supplier_dec_address#18072165) OR (supplier_dec_address#18072165 = )) THEN null ELSE supplier_dec_address#18072165 END, CASE WHEN (isnull(data_address_line1#18072171) OR (data_address_line1#18072171 = )) THEN null ELSE data_address_line1#18072171 END, ), , , coalesce(CASE WHEN (isnull(supplier_dec__address_line2#18072166) OR (supplier_dec__address_line2#18072166 = )) THEN null ELSE supplier_dec__address_line2#18072166 END, CASE WHEN (isnull(data_address_line2#18072172) OR (data_address_line2#18072172 = )) THEN null ELSE data_address_line2#18072172 END, )) ELSE coalesce(CASE WHEN (isnull(supplier_dec_address#18072165) OR (supplier_dec_address#18072165 = )) THEN null ELSE supplier_dec_address#18072165 END, CASE WHEN (isnull(data_address_line1#18072171) OR (data_address_line1#18072171 = )) THEN null ELSE data_address_line1#18072171 END, ) END, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(CASE WHEN NOT (coalesce(CASE WHEN (isnull(supplier_dec__address_line2#18072166) OR (supplier_dec__address_line2#18072166 = )) THEN null ELSE supplier_dec__address_line2#18072166 END, CASE WHEN (isnull(data_address_line2#18072172) OR (data_address_line2#18072172 = )) THEN null ELSE data_address_line2#18072172 END, ) = ) THEN concat(coalesce(CASE WHEN (isnull(supplier_dec_address#18072165) OR (supplier_dec_address#18072165 = )) THEN null ELSE supplier_dec_address#18072165 END, CASE WHEN (isnull(data_address_line1#18072171) OR (data_address_line1#18072171 = )) THEN null ELSE data_address_line1#18072171 END, ), , , coalesce(CASE WHEN (isnull(supplier_dec__address_line2#18072166) OR (supplier_dec__address_line2#18072166 = )) THEN null ELSE supplier_dec__address_line2#18072166 END, CASE WHEN (isnull(data_address_line2#18072172) OR (data_address_line2#18072172 = )) THEN null ELSE data_address_line2#18072172 END, )) ELSE coalesce(CASE WHEN (isnull(supplier_dec_address#18072165) OR (supplier_dec_address#18072165 = )) THEN null ELSE supplier_dec_address#18072165 END, CASE WHEN (isnull(data_address_line1#18072171) OR (data_address_line1#18072171 = )) THEN null ELSE data_address_line1#18072171 END, ) END, , , 1), {2,}, , 1), None) END AS resolved_supplier_address#18073368, coalesce(CASE WHEN (isnull(derived_supplier_dec_ref#18072175) OR (derived_supplier_dec_ref#18072175 = )) THEN null ELSE derived_supplier_dec_ref#18072175 END, CASE WHEN (isnull(data_supplier_default_ref#18072176) OR (data_supplier_default_ref#18072176 = )) THEN null ELSE data_supplier_default_ref#18072176 END) AS resolved_supplier_ref#18073566, (isnull(derived_supplier_dec_ref#18072175) AND coalesce((data_supplier_name#18072167 = Supplier Unknown), false)) AS is_unknown_supplier#18073668]
Input [42]: [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, data_address_line1#18072171, data_address_line2#18072172, data_address_lat#18072173, data_address_lng#18072174, derived_supplier_dec_ref#18072175, data_supplier_default_ref#18072176, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, key_values#18072180, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188]
(14) Project
Output [51]: [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, data_address_line1#18072171, data_address_line2#18072172, data_address_lat#18072173, data_address_lng#18072174, derived_supplier_dec_ref#18072175, data_supplier_default_ref#18072176, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, key_values#18072180, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188, resolved_product_name#18072487, resolved_supplier_name#18072579, resolved_supplier_city#18072713, resolved_supplier_state#18072893, resolved_supplier_country#18073080, resolved_supplier_address#18073368, resolved_supplier_ref#18073566, is_unknown_supplier#18073668, CASE WHEN (isnull(resolved_supplier_name#18072579) AND coalesce((data_state#18072155 = Not Applicable), false)) THEN Not Applicable WHEN (isnull(resolved_supplier_name#18072579) AND coalesce((node_status#18072154 = 30), false)) THEN Supplier Unknown ELSE resolved_supplier_name#18072579 END AS resolved_supplier_display_name#18073870]
Input [50]: [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, data_address_line1#18072171, data_address_line2#18072172, data_address_lat#18072173, data_address_lng#18072174, derived_supplier_dec_ref#18072175, data_supplier_default_ref#18072176, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, key_values#18072180, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188, resolved_product_name#18072487, resolved_supplier_name#18072579, resolved_supplier_city#18072713, resolved_supplier_state#18072893, resolved_supplier_country#18073080, resolved_supplier_address#18073368, resolved_supplier_ref#18073566, is_unknown_supplier#18073668]
(15) Exchange
Input [51]: [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, data_address_line1#18072171, data_address_line2#18072172, data_address_lat#18072173, data_address_lng#18072174, derived_supplier_dec_ref#18072175, data_supplier_default_ref#18072176, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, key_values#18072180, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188, resolved_product_name#18072487, resolved_supplier_name#18072579, resolved_supplier_city#18072713, resolved_supplier_state#18072893, resolved_supplier_country#18073080, resolved_supplier_address#18073368, resolved_supplier_ref#18073566, is_unknown_supplier#18073668, resolved_supplier_display_name#18073870]
Arguments: hashpartitioning(brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, 5), REPARTITION_BY_NUM, [plan_id=4617354]
(16) BroadcastHashJoin
Left keys [4]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038]
Right keys [4]: [brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151]
Join type: Inner
Join condition: None
(17) Project
Output [73]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173, _id#18072147, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, data_address_line1#18072171, data_address_line2#18072172, data_address_lat#18072173, data_address_lng#18072174, derived_supplier_dec_ref#18072175, data_supplier_default_ref#18072176, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, key_values#18072180, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188, resolved_product_name#18072487, resolved_supplier_name#18072579, resolved_supplier_city#18072713, resolved_supplier_state#18072893, resolved_supplier_country#18073080, resolved_supplier_address#18073368, resolved_supplier_ref#18073566, is_unknown_supplier#18073668, resolved_supplier_display_name#18073870]
Input [77]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173, _id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, data_address_line1#18072171, data_address_line2#18072172, data_address_lat#18072173, data_address_lng#18072174, derived_supplier_dec_ref#18072175, data_supplier_default_ref#18072176, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, key_values#18072180, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188, resolved_product_name#18072487, resolved_supplier_name#18072579, resolved_supplier_city#18072713, resolved_supplier_state#18072893, resolved_supplier_country#18073080, resolved_supplier_address#18073368, resolved_supplier_ref#18073566, is_unknown_supplier#18073668, resolved_supplier_display_name#18073870]
(18) AdaptiveSparkPlan
Output [73]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173, _id#18072147, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, data_address_line1#18072171, data_address_line2#18072172, data_address_lat#18072173, data_address_lng#18072174, derived_supplier_dec_ref#18072175, data_supplier_default_ref#18072176, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, key_values#18072180, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188, resolved_product_name#18072487, resolved_supplier_name#18072579, resolved_supplier_city#18072713, resolved_supplier_state#18072893, resolved_supplier_country#18073080, resolved_supplier_address#18073368, resolved_supplier_ref#18073566, is_unknown_supplier#18073668, resolved_supplier_display_name#18073870]
Arguments: isFinalPlan=false
(19) InMemoryTableScan
Output [14]: [_id#18081902, brand_context_id#18081873, company_id#18081874, data_address_lat#18081928, data_address_lng#18081929, data_state#18081910, launch_config_ref#18081875, node_status#18081909, resolved_supplier_address#18073368, resolved_supplier_city#18072713, resolved_supplier_country#18073080, resolved_supplier_display_name#18073870, resolved_supplier_state#18072893, revision_version#18081876]
Arguments: [_id#18081902, brand_context_id#18081873, company_id#18081874, data_address_lat#18081928, data_address_lng#18081929, data_state#18081910, launch_config_ref#18081875, node_status#18081909, resolved_supplier_address#18073368, resolved_supplier_city#18072713, resolved_supplier_country#18073080, resolved_supplier_display_name#18073870, resolved_supplier_state#18072893, revision_version#18081876], [isnotnull(_id#18081902), isnotnull(brand_context_id#18081873), isnotnull(company_id#18081874), isnotnull(launch_config_ref#18081875), isnotnull(revision_version#18081876)]
(20) InMemoryRelation
Arguments: [brand_context_id#18081873, company_id#18081874, launch_config_ref#18081875, revision_version#18081876, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields], CachedRDDBuilder(org.apache.spark.sql.execution.columnar.DefaultCachedBatchSerializer@730ae8bf,StorageLevel(disk, memory, deserialized, 1 replicas),AdaptiveSparkPlan isFinalPlan=true
+- == Final Plan ==
*(3) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields]
+- *(3) BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151], Inner, BuildLeft, false
:- BroadcastQueryStage 2
: +- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, smallint, true]),false), [plan_id=4617896]
: +- *(2) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18078599, config_version#18069041 AS summary__config_version#18078601, entity_id#18069042 AS summary__entity_id#18078603, submission_status#18069043 AS summary__submission_status#18078605, is_latest#18069044 AS summary__is_latest#18078750, is_editable#18069045 AS summary__is_editable#18078752, launch_date#18069046 AS summary__launch_date#18078754, expiry_date#18069047 AS summary__expiry_date#18078756, submitted_date#18069048 AS summary__submitted_date#18078757, create_ts#18069049 AS summary__create_ts#18079123, update_ts#18069050 AS summary__update_ts#18079125, create_date#18069051 AS summary__create_date#18079127, update_date#18069052 AS summary__update_date#18079129, source#18069053 AS summary__source#18079131, recurrence_count#18069054 AS summary__recurrence_count#18079133, priority#18069055 AS summary__priority#18079135, remarks#18069056 AS summary__remarks#18079137, latest_source#18069057 AS summary__latest_source#18079165, launch_request_name#18069058 AS summary__launch_request_name#18079167, NetworkID#18069039 AS summary__NetworkID#18079169, ... 2 more fields]
: +- *(2) Filter ((((NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465) AND isnotnull(brand_context_id#18069035)) AND isnotnull(company_id#18069036)) AND isnotnull(launch_config_ref#18069037)) AND isnotnull(revision_version#18069038))
: +- TableCacheQueryStage 0
: +- InMemoryTableScan [NetworkID#18069039, brand_context_id#18069035, company_id#18069036, config_name#18069040, config_version#18069041, create_date#18069051, create_ts#18069049, direct_supplier_name#18069091, entity_id#18069042, expiry_date#18069047, is_editable#18069045, is_latest#18069044, latest_source#18069057, launch_config_ref#18069037, launch_date#18069046, launch_request_name#18069058, priority#18069055, recurrence_count#18069054, remarks#18069056, revision_version#18069038, source#18069053, submission_status#18069043, submitted_date#18069048, supplier_uid#18069060, ... 2 more fields], [NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465), isnotnull(brand_context_id#18069035), isnotnull(company_id#18069036), isnotnull(launch_config_ref#18069037), isnotnull(revision_version#18069038)]
: +- InMemoryRelation [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields], StorageLevel(disk, memory, deserialized, 1 replicas)
: +- *(1) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields]
: +- *(1) Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [brand_context_id#18069035,company_id#18069036,launch_config_ref#18069037,revision_version#18069038,NetworkID#18069039,config_name#18069040,config_version#18069041,entity_id#18069042,submission_status#18069043,is_latest#18069044,is_editable#18069045,launch_date#18069046,expiry_date#18069047,submitted_date#18069048,create_ts#18069049,update_ts#18069050,create_date#18069051,update_date#18069052,source#18069053,recurrence_count#18069054,priority#18069055,remarks#18069056,latest_source#18069057,launch_request_name#18069058,... 4 more fields] PushedFilters: [], ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smalli...
+- ShuffleQueryStage 1
+- Exchange hashpartitioning(brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, 5), REPARTITION_BY_NUM, [plan_id=4617830]
+- *(1) Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 27 more fields]
+- *(1) Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 26 more fields]
+- *(1) Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1] [_id#18072147,brand_context_id#18072148,company_id#18072149,launch_config_ref#18072150,revision_version#18072151,parent_id#18072152,parent_launch_config_ref#18072153,node_status#18072154,data_state#18072155,tier#18072156,reference_code#18072157,source_type#18072158,product_name#18072159,product_alias_name#18072160,supplier_dec_name#18072161,supplier_dec_city#18072162,supplier_dec_state#18072163,supplier_dec_country#18072164,supplier_dec_address#18072165,supplier_dec__address_line2#18072166,data_supplier_name#18072167,data_address_city#18072168,data_address_state#18072169,data_address_country#18072170,... 18 more fields] PushedFilters: [*IsNotNull(brand_context_id), *IsNotNull(company_id), *IsNotNull(launch_config_ref), *IsNotNull(..., ReadSchema: struct<_id:string,brand_context_id:string,company_id:string,launch_config_ref:string,revision_ver...
+- == Initial Plan ==
Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, ... 49 more fields]
+- BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151], Inner, BuildLeft, false
:- BroadcastExchange HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, smallint, true]),false), [plan_id=4617358]
: +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18078599, config_version#18069041 AS summary__config_version#18078601, entity_id#18069042 AS summary__entity_id#18078603, submission_status#18069043 AS summary__submission_status#18078605, is_latest#18069044 AS summary__is_latest#18078750, is_editable#18069045 AS summary__is_editable#18078752, launch_date#18069046 AS summary__launch_date#18078754, expiry_date#18069047 AS summary__expiry_date#18078756, submitted_date#18069048 AS summary__submitted_date#18078757, create_ts#18069049 AS summary__create_ts#18079123, update_ts#18069050 AS summary__update_ts#18079125, create_date#18069051 AS summary__create_date#18079127, update_date#18069052 AS summary__update_date#18079129, source#18069053 AS summary__source#18079131, recurrence_count#18069054 AS summary__recurrence_count#18079133, priority#18069055 AS summary__priority#18079135, remarks#18069056 AS summary__remarks#18079137, latest_source#18069057 AS summary__latest_source#18079165, launch_request_name#18069058 AS summary__launch_request_name#18079167, NetworkID#18069039 AS summary__NetworkID#18079169, ... 2 more fields]
: +- Filter ((((NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465) AND isnotnull(brand_context_id#18069035)) AND isnotnull(company_id#18069036)) AND isnotnull(launch_config_ref#18069037)) AND isnotnull(revision_version#18069038))
: +- InMemoryTableScan [NetworkID#18069039, brand_context_id#18069035, company_id#18069036, config_name#18069040, config_version#18069041, create_date#18069051, create_ts#18069049, direct_supplier_name#18069091, entity_id#18069042, expiry_date#18069047, is_editable#18069045, is_latest#18069044, latest_source#18069057, launch_config_ref#18069037, launch_date#18069046, launch_request_name#18069058, priority#18069055, recurrence_count#18069054, remarks#18069056, revision_version#18069038, source#18069053, submission_status#18069043, submitted_date#18069048, supplier_uid#18069060, ... 2 more fields], [NetworkID#18069039 IN (860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A,A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948,A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D,BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08,C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465), isnotnull(brand_context_id#18069035), isnotnull(company_id#18069036), isnotnull(launch_config_ref#18069037), isnotnull(revision_version#18069038)]
: +- InMemoryRelation [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields], StorageLevel(disk, memory, deserialized, 1 replicas)
: +- *(1) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, NetworkID#18069039, config_name#18069040, config_version#18069041, entity_id#18069042, submission_status#18069043, is_latest#18069044, is_editable#18069045, launch_date#18069046, expiry_date#18069047, submitted_date#18069048, create_ts#18069049, update_ts#18069050, create_date#18069051, update_date#18069052, source#18069053, recurrence_count#18069054, priority#18069055, remarks#18069056, latest_source#18069057, launch_request_name#18069058, ... 4 more fields]
: +- *(1) Scan JDBCRelation((
SELECT
rs.brand_context_id, rs.company_id, rs.launch_config_ref, rs.revision_version,
rs.networkId AS NetworkID,
rs.config_name, rs.config_version, rs.entity_id, rs.submission_status, rs.is_latest,
rs.is_editable, rs.launch_date, rs.expiry_date, rs.submitted_date, rs.create_ts, rs.update_ts,
rs.create_date, rs.update_date, rs.source, rs.recurrence_count, rs.priority, rs.remarks,
rs.latest_source, rs.launch_request_name,
prof.profile_supplier_name AS direct_supplier_name,
prof.profile_supplierUID AS supplier_uid,
ou.ou_division, ou.ou_location
FROM dbo.Datahub_SCM_Network_Load_Queue inc
INNER JOIN dbo.dimscm_response_summary rs
ON rs.networkId = inc.NetworkID
OUTER APPLY (
SELECT TOP 1 p.profile_supplier_name, p.profile_supplierUID
FROM dbo.dimsupplier_profilemerge p
WHERE p.brandid = inc.brand_context_id
AND p.supplier_id = inc.company_id
ORDER BY p.profile_supplierUID
) prof
LEFT JOIN (
SELECT
d.networkId,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_division' THEN d.ou_attribute_value END, ',') AS ou_division,
STRING_AGG(CASE WHEN d.ou_attribute_id = 'ou_location' THEN d.ou_attribute_value END, ',') AS ou_location
FROM (
SELECT DISTINCT oa.networkId, oa.ou_attribute_id, oa.ou_attribute_value
FROM dbo.Datahub_SCM_Network_Load_Queue q
INNER JOIN dbo.dimscm_response_summary_ou_attribute oa
ON oa.networkId = q.NetworkID
WHERE q.status = 'PENDING'
AND oa.ou_attribute_id IN ('ou_division', 'ou_location')
) d
GROUP BY d.networkId
) ou
ON ou.networkId = rs.networkId
WHERE inc.status = 'PENDING'
AND (rs.source IS NULL OR rs.source <> 'PO_TRACING')
AND (rs.submission_status IS NULL OR rs.submission_status NOT IN ('AUTO_CLOSED'))
) AS _t) [numPartitions=1] [brand_context_id#18069035,company_id#18069036,launch_config_ref#18069037,revision_version#18069038,NetworkID#18069039,config_name#18069040,config_version#18069041,entity_id#18069042,submission_status#18069043,is_latest#18069044,is_editable#18069045,launch_date#18069046,expiry_date#18069047,submitted_date#18069048,create_ts#18069049,update_ts#18069050,create_date#18069051,update_date#18069052,source#18069053,recurrence_count#18069054,priority#18069055,remarks#18069056,latest_source#18069057,launch_request_name#18069058,... 4 more fields] PushedFilters: [], ReadSchema: struct<brand_context_id:string,company_id:string,launch_config_ref:string,revision_version:smalli...
+- Exchange hashpartitioning(brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, 5), REPARTITION_BY_NUM, [plan_id=4617354]
+- Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 27 more fields]
+- Project [_id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, ... 26 more fields]
+- Scan JDBCRelation((
SELECT
pn._id, pn.brand_context_id, pn.company_id, pn.launch_config_ref, pn.revision_version,
pn.parent_id, pn.parent_launch_config_ref, pn.node_status, pn.data_state, pn.tier,
pn.reference_code, pn.source_type, pn.product_name, pn.product_alias_name,
pn.supplier_dec_name, pn.supplier_dec_city, pn.supplier_dec_state, pn.supplier_dec_country,
pn.supplier_dec_address, pn.supplier_dec__address_line2,
pn.data_supplier_name, pn.data_address_city, pn.data_address_state, pn.data_address_country,
pn.data_address_line1, pn.data_address_line2, pn.data_address_lat, pn.data_address_lng,
pn.derived_supplier_dec_ref,
pn.data_supplier_default_ref, pn.supplier_type, pn.nominated_node, pn.update_ts, pn.key_values,
CAST(COALESCE(pn.levels, 0) AS INT) AS Levels,
ds.source AS decl_source, ds.supplier_id AS decl_supplier_id,
TRY_CONVERT(date, ds.create_ts) AS decl_supplier_create_date, ds.company_id AS decl_supplier_company_id,
sdm.preferrd_supplier_id AS decl_preferrd_supplier_id,
sdm.preferrd_supplier_name AS decl_preferrd_supplier_name,
sdm.reference_id AS decl_dedup_reference_id
FROM dbo.dimscm_product_node_response pn
LEFT JOIN dbo.dimsupplier_declaration ds
ON ds._id = pn.derived_supplier_dec_ref
AND ds.brand_context_id = pn.brand_context_id
LEFT JOIN dbo.dim_compute_supplier_dedup_mapping sdm
ON sdm.brand_context_id = ds.brand_context_id
AND sdm.duplicate_entities_entity_id = ds._id
WHERE (pn.networkId IN ('860884679CA3C80F17904897D3A9D466E39FE47E59185F9CAA3BAB59E11C6A7A', 'A16DB822158053E97574C361955A7B5C1001B46DC0196D28610F8316CEF51948', 'A37E7DE563DE4B74B164922D53423CFB7C15022DE823CA7FF38258935261DD9D', 'BDB94B9C0FD73D12DC195F2D1B6355BD0472A6168FC2F7FF059FC7D6A5F76F08', 'C3B178AAA1892E3A4D27510B74CEB1FEC2D7F6362DDD59FEE288754BF3B46465'))
) AS _t) [numPartitions=1] [_id#18072147,brand_context_id#18072148,company_id#18072149,launch_config_ref#18072150,revision_version#18072151,parent_id#18072152,parent_launch_config_ref#18072153,node_status#18072154,data_state#18072155,tier#18072156,reference_code#18072157,source_type#18072158,product_name#18072159,product_alias_name#18072160,supplier_dec_name#18072161,supplier_dec_city#18072162,supplier_dec_state#18072163,supplier_dec_country#18072164,supplier_dec_address#18072165,supplier_dec__address_line2#18072166,data_supplier_name#18072167,data_address_city#18072168,data_address_state#18072169,data_address_country#18072170,... 18 more fields] PushedFilters: [*IsNotNull(brand_context_id), *IsNotNull(company_id), *IsNotNull(launch_config_ref), *IsNotNull(..., ReadSchema: struct<_id:string,brand_context_id:string,company_id:string,launch_config_ref:string,revision_ver...
,None)
(21) BroadcastExchange
Input [26]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173]
Arguments: HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, smallint, true]),false), [plan_id=4617510]
(22) BroadcastHashJoin
Left keys [4]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038]
Right keys [4]: [brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151]
Join type: Inner
Join condition: None
(23) Project
Output [73]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173, _id#18072147, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, data_address_line1#18072171, data_address_line2#18072172, data_address_lat#18072173, data_address_lng#18072174, derived_supplier_dec_ref#18072175, data_supplier_default_ref#18072176, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, key_values#18072180, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188, resolved_product_name#18072487, resolved_supplier_name#18072579, resolved_supplier_city#18072713, resolved_supplier_state#18072893, resolved_supplier_country#18073080, resolved_supplier_address#18073368, resolved_supplier_ref#18073566, is_unknown_supplier#18073668, resolved_supplier_display_name#18073870]
Input [77]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173, _id#18072147, brand_context_id#18072148, company_id#18072149, launch_config_ref#18072150, revision_version#18072151, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, data_address_line1#18072171, data_address_line2#18072172, data_address_lat#18072173, data_address_lng#18072174, derived_supplier_dec_ref#18072175, data_supplier_default_ref#18072176, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, key_values#18072180, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188, resolved_product_name#18072487, resolved_supplier_name#18072579, resolved_supplier_city#18072713, resolved_supplier_state#18072893, resolved_supplier_country#18073080, resolved_supplier_address#18073368, resolved_supplier_ref#18073566, is_unknown_supplier#18073668, resolved_supplier_display_name#18073870]
(24) AdaptiveSparkPlan
Output [73]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__config_version#18078601, summary__entity_id#18078603, summary__submission_status#18078605, summary__is_latest#18078750, summary__is_editable#18078752, summary__launch_date#18078754, summary__expiry_date#18078756, summary__submitted_date#18078757, summary__create_ts#18079123, summary__update_ts#18079125, summary__create_date#18079127, summary__update_date#18079129, summary__source#18079131, summary__recurrence_count#18079133, summary__priority#18079135, summary__remarks#18079137, summary__latest_source#18079165, summary__launch_request_name#18079167, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173, _id#18072147, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, product_alias_name#18072160, supplier_dec_name#18072161, supplier_dec_city#18072162, supplier_dec_state#18072163, supplier_dec_country#18072164, supplier_dec_address#18072165, supplier_dec__address_line2#18072166, data_supplier_name#18072167, data_address_city#18072168, data_address_state#18072169, data_address_country#18072170, data_address_line1#18072171, data_address_line2#18072172, data_address_lat#18072173, data_address_lng#18072174, derived_supplier_dec_ref#18072175, data_supplier_default_ref#18072176, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, key_values#18072180, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188, resolved_product_name#18072487, resolved_supplier_name#18072579, resolved_supplier_city#18072713, resolved_supplier_state#18072893, resolved_supplier_country#18073080, resolved_supplier_address#18073368, resolved_supplier_ref#18073566, is_unknown_supplier#18073668, resolved_supplier_display_name#18073870]
Arguments: isFinalPlan=false
(25) Filter
Input [14]: [_id#18081902, brand_context_id#18081873, company_id#18081874, data_address_lat#18081928, data_address_lng#18081929, data_state#18081910, launch_config_ref#18081875, node_status#18081909, resolved_supplier_address#18073368, resolved_supplier_city#18072713, resolved_supplier_country#18073080, resolved_supplier_display_name#18073870, resolved_supplier_state#18072893, revision_version#18081876]
Condition : ((((isnotnull(_id#18081902) AND isnotnull(brand_context_id#18081873)) AND isnotnull(company_id#18081874)) AND isnotnull(launch_config_ref#18081875)) AND isnotnull(revision_version#18081876))
(26) Project
Output [14]: [_id#18081902 AS _p_id#18081610, brand_context_id#18081873 AS _p_brand#18081681, company_id#18081874 AS _p_company#18081682, launch_config_ref#18081875 AS _p_launch#18081683, revision_version#18081876 AS _p_revision#18081684, resolved_supplier_display_name#18073870 AS _p_display_name#18081686, node_status#18081909 AS _p_node_status#18081687, data_state#18081910 AS _p_data_state#18081688, resolved_supplier_city#18072713 AS _p_city#18081689, resolved_supplier_state#18072893 AS _p_state#18081691, resolved_supplier_country#18073080 AS _p_country#18081693, resolved_supplier_address#18073368 AS _p_address#18081695, data_address_lat#18081928 AS _p_lat#18081697, data_address_lng#18081929 AS _p_lng#18081699]
Input [14]: [_id#18081902, brand_context_id#18081873, company_id#18081874, data_address_lat#18081928, data_address_lng#18081929, data_state#18081910, launch_config_ref#18081875, node_status#18081909, resolved_supplier_address#18073368, resolved_supplier_city#18072713, resolved_supplier_country#18073080, resolved_supplier_display_name#18073870, resolved_supplier_state#18072893, revision_version#18081876]
(27) BroadcastExchange
Input [14]: [_p_id#18081610, _p_brand#18081681, _p_company#18081682, _p_launch#18081683, _p_revision#18081684, _p_display_name#18081686, _p_node_status#18081687, _p_data_state#18081688, _p_city#18081689, _p_state#18081691, _p_country#18081693, _p_address#18081695, _p_lat#18081697, _p_lng#18081699]
Arguments: HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, string, true], input[4, smallint, true]),false), [plan_id=4618392]
(28) BroadcastHashJoin
Left keys [5]: [parent_id#18072152, brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038]
Right keys [5]: [_p_id#18081610, _p_brand#18081681, _p_company#18081682, _p_launch#18081683, _p_revision#18081684]
Join type: LeftOuter
Join condition: None
(29) Project
Output [52]: [summary__NetworkID#18079169 AS NetworkID#18085020, brand_context_id#18069035, company_id#18069036, coalesce(launch_config_ref#18069037, ) AS launch_config_ref#18085467, revision_version#18069038, CASE WHEN (trim(regexp_replace(regexp_replace(summary__config_name#18078599, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(summary__config_name#18078599, , , 1), {2,}, , 1), None) END AS config_name#18085479, parent_launch_config_ref#18072153, _id#18072147 AS Child_Node#18085481, parent_id#18072152 AS Parent_id#18085482, Levels#18072181, tier#18072156, node_status#18072154, data_state#18072155, resolved_product_name#18072487 AS product_name#18085835, CASE WHEN (trim(regexp_replace(regexp_replace(product_name#18072159, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(product_name#18072159, , , 1), {2,}, , 1), None) END AS product_type#18085838, reference_code#18072157, resolved_supplier_display_name#18073870 AS SupplierName#18085840, resolved_supplier_city#18072713 AS SupplierCity#18085841, resolved_supplier_state#18072893 AS SupplierState#18085842, resolved_supplier_country#18073080 AS SupplierCountry#18085895, resolved_supplier_address#18073368 AS SupplierAddress#18085897, data_address_lat#18072173 AS SupplierLat#18085898, data_address_lng#18072174 AS SupplierLng#18085899, resolved_supplier_ref#18073566 AS Supplier_declaration_RefID#18085900, supplier_type#18072177, decl_supplier_id#18072183 AS supplier_id#18085901, decl_supplier_create_date#18072184 AS supplier_first_created_date#18085902, decl_supplier_company_id#18072185 AS supplier_declared_company#18087363, decl_preferrd_supplier_id#18072186 AS preferrd_supplier_id#18087364, decl_preferrd_supplier_name#18072187 AS preferrd_supplier_name#18087365, decl_dedup_reference_id#18072188 AS dedup_reference_id#18087367, CASE WHEN is_unknown_supplier#18073668 THEN InDirect WHEN (decl_source#18072182 = PRIMARY) THEN Direct ELSE InDirect END AS DirectIndirectSupplier#18087368, summary__direct_supplier_name#18079170 AS Direct_Supplier_Name#18087370, summary__supplier_uid#18079173 AS SupplierUID#18087372, nominated_node#18072178, CASE WHEN nominated_node#18072178 THEN Yet to complete ELSE Not applicable END AS child_network_id#18087374, CASE WHEN nominated_node#18072178 THEN Pending ELSE Not applicable END AS child_network_status#18087375, CASE WHEN isnotnull(_p_display_name#18081686) THEN _p_display_name#18081686 WHEN (_p_data_state#18081688 = Not Applicable) THEN Not Applicable WHEN (_p_node_status#18081687 = 30) THEN Supplier Unknown ELSE Not Applicable END AS ParentSupplierName#18087376, _p_city#18081689 AS ParentSupplierCity#18087379, _p_state#18081691 AS ParentSupplierState#18087381, _p_country#18081693 AS ParentSupplierCountry#18087384, _p_address#18081695 AS ParentSupplierAddress#18087386, _p_lat#18081697 AS ParentSupplierLat#18087389, _p_lng#18081699 AS ParentSupplierLng#18087392, summary__submission_status#18078605 AS submission_status#18087395, summary__is_latest#18078750 AS is_latest#18088128, false AS is_latest_submitted#18088131, summary__source#18079131 AS source#18088134, source_type#18072158, update_ts#18072179 AS node_update_ts#18088137, is_unknown_supplier#18073668, 2026-10-20 07:30:06.152599 AS update_ts#18088404]
Input [55]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18078599, summary__submission_status#18078605, summary__is_latest#18078750, summary__source#18079131, summary__NetworkID#18079169, summary__direct_supplier_name#18079170, summary__supplier_uid#18079173, _id#18072147, parent_id#18072152, parent_launch_config_ref#18072153, node_status#18072154, data_state#18072155, tier#18072156, reference_code#18072157, source_type#18072158, product_name#18072159, data_address_lat#18072173, data_address_lng#18072174, supplier_type#18072177, nominated_node#18072178, update_ts#18072179, Levels#18072181, decl_source#18072182, decl_supplier_id#18072183, decl_supplier_create_date#18072184, decl_supplier_company_id#18072185, decl_preferrd_supplier_id#18072186, decl_preferrd_supplier_name#18072187, decl_dedup_reference_id#18072188, resolved_product_name#18072487, resolved_supplier_city#18072713, resolved_supplier_state#18072893, resolved_supplier_country#18073080, resolved_supplier_address#18073368, resolved_supplier_ref#18073566, is_unknown_supplier#18073668, resolved_supplier_display_name#18073870, _p_id#18081610, _p_brand#18081681, _p_company#18081682, _p_launch#18081683, _p_revision#18081684, _p_display_name#18081686, _p_node_status#18081687, _p_data_state#18081688, _p_city#18081689, _p_state#18081691, _p_country#18081693, _p_address#18081695, _p_lat#18081697, _p_lng#18081699]
(30) AdaptiveSparkPlan
Output [52]: [NetworkID#18085020, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085467, revision_version#18069038, config_name#18085479, parent_launch_config_ref#18072153, Child_Node#18085481, Parent_id#18085482, Levels#18072181, tier#18072156, node_status#18072154, data_state#18072155, product_name#18085835, product_type#18085838, reference_code#18072157, SupplierName#18085840, SupplierCity#18085841, SupplierState#18085842, SupplierCountry#18085895, SupplierAddress#18085897, SupplierLat#18085898, SupplierLng#18085899, Supplier_declaration_RefID#18085900, supplier_type#18072177, supplier_id#18085901, supplier_first_created_date#18085902, supplier_declared_company#18087363, preferrd_supplier_id#18087364, preferrd_supplier_name#18087365, dedup_reference_id#18087367, DirectIndirectSupplier#18087368, Direct_Supplier_Name#18087370, SupplierUID#18087372, nominated_node#18072178, child_network_id#18087374, child_network_status#18087375, ParentSupplierName#18087376, ParentSupplierCity#18087379, ParentSupplierState#18087381, ParentSupplierCountry#18087384, ParentSupplierAddress#18087386, ParentSupplierLat#18087389, ParentSupplierLng#18087392, submission_status#18087395, is_latest#18088128, is_latest_submitted#18088131, source#18088134, source_type#18072158, node_update_ts#18088137, is_unknown_supplier#18073668, update_ts#18088404]
Arguments: isFinalPlan=false
(31) TableCacheQueryStage
Output [52]: [NetworkID#18085020, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085467, revision_version#18069038, config_name#18085479, parent_launch_config_ref#18072153, Child_Node#18085481, Parent_id#18085482, Levels#18072181, tier#18072156, node_status#18072154, data_state#18072155, product_name#18085835, product_type#18085838, reference_code#18072157, SupplierName#18085840, SupplierCity#18085841, SupplierState#18085842, SupplierCountry#18085895, SupplierAddress#18085897, SupplierLat#18085898, SupplierLng#18085899, Supplier_declaration_RefID#18085900, supplier_type#18072177, supplier_id#18085901, supplier_first_created_date#18085902, supplier_declared_company#18087363, preferrd_supplier_id#18087364, preferrd_supplier_name#18087365, dedup_reference_id#18087367, DirectIndirectSupplier#18087368, Direct_Supplier_Name#18087370, SupplierUID#18087372, nominated_node#18072178, child_network_id#18087374, child_network_status#18087375, ParentSupplierName#18087376, ParentSupplierCity#18087379, ParentSupplierState#18087381, ParentSupplierCountry#18087384, ParentSupplierAddress#18087386, ParentSupplierLat#18087389, ParentSupplierLng#18087392, submission_status#18087395, is_latest#18088128, is_latest_submitted#18088131, source#18088134, source_type#18072158, node_update_ts#18088137, is_unknown_supplier#18073668, update_ts#18088404]
Arguments: 0
(32) DeserializeToObject
Input [52]: [NetworkID#18085020, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085467, revision_version#18069038, config_name#18085479, parent_launch_config_ref#18072153, Child_Node#18085481, Parent_id#18085482, Levels#18072181, tier#18072156, node_status#18072154, data_state#18072155, product_name#18085835, product_type#18085838, reference_code#18072157, SupplierName#18085840, SupplierCity#18085841, SupplierState#18085842, SupplierCountry#18085895, SupplierAddress#18085897, SupplierLat#18085898, SupplierLng#18085899, Supplier_declaration_RefID#18085900, supplier_type#18072177, supplier_id#18085901, supplier_first_created_date#18085902, supplier_declared_company#18087363, preferrd_supplier_id#18087364, preferrd_supplier_name#18087365, dedup_reference_id#18087367, DirectIndirectSupplier#18087368, Direct_Supplier_Name#18087370, SupplierUID#18087372, nominated_node#18072178, child_network_id#18087374, child_network_status#18087375, ParentSupplierName#18087376, ParentSupplierCity#18087379, ParentSupplierState#18087381, ParentSupplierCountry#18087384, ParentSupplierAddress#18087386, ParentSupplierLat#18087389, ParentSupplierLng#18087392, submission_status#18087395, is_latest#18088128, is_latest_submitted#18088131, source#18088134, source_type#18072158, node_update_ts#18088137, is_unknown_supplier#18073668, update_ts#18088404]
Arguments: createexternalrow(NetworkID#18085020.toString, brand_context_id#18069035.toString, company_id#18069036.toString, launch_config_ref#18085467.toString, staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, revision_version#18069038, true, false, true), config_name#18085479.toString, parent_launch_config_ref#18072153.toString, Child_Node#18085481.toString, Parent_id#18085482.toString, staticinvoke(class java.lang.Integer, ObjectType(class java.lang.Integer), valueOf, Levels#18072181, true, false, true), staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, tier#18072156, true, false, true), staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, node_status#18072154, true, false, true), data_state#18072155.toString, product_name#18085835.toString, product_type#18085838.toString, reference_code#18072157.toString, SupplierName#18085840.toString, SupplierCity#18085841.toString, SupplierState#18085842.toString, SupplierCountry#18085895.toString, SupplierAddress#18085897.toString, SupplierLat#18085898.toString, SupplierLng#18085899.toString, Supplier_declaration_RefID#18085900.toString, ... 80 more fields), obj#18114972: org.apache.spark.sql.Row
(33) DeserializeToObject
Input [52]: [NetworkID#18085020, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085467, revision_version#18069038, config_name#18085479, parent_launch_config_ref#18072153, Child_Node#18085481, Parent_id#18085482, Levels#18072181, tier#18072156, node_status#18072154, data_state#18072155, product_name#18085835, product_type#18085838, reference_code#18072157, SupplierName#18085840, SupplierCity#18085841, SupplierState#18085842, SupplierCountry#18085895, SupplierAddress#18085897, SupplierLat#18085898, SupplierLng#18085899, Supplier_declaration_RefID#18085900, supplier_type#18072177, supplier_id#18085901, supplier_first_created_date#18085902, supplier_declared_company#18087363, preferrd_supplier_id#18087364, preferrd_supplier_name#18087365, dedup_reference_id#18087367, DirectIndirectSupplier#18087368, Direct_Supplier_Name#18087370, SupplierUID#18087372, nominated_node#18072178, child_network_id#18087374, child_network_status#18087375, ParentSupplierName#18087376, ParentSupplierCity#18087379, ParentSupplierState#18087381, ParentSupplierCountry#18087384, ParentSupplierAddress#18087386, ParentSupplierLat#18087389, ParentSupplierLng#18087392, submission_status#18087395, is_latest#18088128, is_latest_submitted#18088131, source#18088134, source_type#18072158, node_update_ts#18088137, is_unknown_supplier#18073668, update_ts#18088404]
Arguments: createexternalrow(NetworkID#18085020.toString, brand_context_id#18069035.toString, company_id#18069036.toString, launch_config_ref#18085467.toString, staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, revision_version#18069038, true, false, true), config_name#18085479.toString, parent_launch_config_ref#18072153.toString, Child_Node#18085481.toString, Parent_id#18085482.toString, staticinvoke(class java.lang.Integer, ObjectType(class java.lang.Integer), valueOf, Levels#18072181, true, false, true), staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, tier#18072156, true, false, true), staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, node_status#18072154, true, false, true), data_state#18072155.toString, product_name#18085835.toString, product_type#18085838.toString, reference_code#18072157.toString, SupplierName#18085840.toString, SupplierCity#18085841.toString, SupplierState#18085842.toString, SupplierCountry#18085895.toString, SupplierAddress#18085897.toString, SupplierLat#18085898.toString, SupplierLng#18085899.toString, Supplier_declaration_RefID#18085900.toString, ... 80 more fields), obj#18114972: org.apache.spark.sql.Row
(34) AdaptiveSparkPlan
Output [1]: [obj#18114972]
Arguments: isFinalPlan=true