== Physical Plan ==
AdaptiveSparkPlan (34)
+- == Final Plan ==
DeserializeToObject (32)
+- TableCacheQueryStage (31), Statistics(sizeInBytes=64.2 KiB, rowCount=91)
+- 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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) 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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) 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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) 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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) AS _t) [numPartitions=1] (12)
(1) InMemoryTableScan
Output [52]: [NetworkID#18085833, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085834, revision_version#18069038, config_name#18085836, parent_launch_config_ref#18072280, Child_Node#18085837, Parent_id#18085839, Levels#18072308, tier#18072283, node_status#18072281, data_state#18072282, product_name#18085896, product_type#18087378, reference_code#18072284, SupplierName#18087383, SupplierCity#18087387, SupplierState#18087390, SupplierCountry#18087393, SupplierAddress#18087396, SupplierLat#18088129, SupplierLng#18088132, Supplier_declaration_RefID#18088135, supplier_type#18072304, supplier_id#18088138, supplier_first_created_date#18088139, supplier_declared_company#18088405, preferrd_supplier_id#18088407, preferrd_supplier_name#18088409, dedup_reference_id#18088411, DirectIndirectSupplier#18088412, Direct_Supplier_Name#18088414, SupplierUID#18088415, nominated_node#18072305, child_network_id#18088416, child_network_status#18088417, ParentSupplierName#18088418, ParentSupplierCity#18088419, ParentSupplierState#18088421, ParentSupplierCountry#18088422, ParentSupplierAddress#18088423, ParentSupplierLat#18088425, ParentSupplierLng#18088427, submission_status#18088429, is_latest#18088430, is_latest_submitted#18088431, source#18088433, source_type#18072285, node_update_ts#18088436, is_unknown_supplier#18073972, update_ts#18088788]
Arguments: [NetworkID#18085833, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085834, revision_version#18069038, config_name#18085836, parent_launch_config_ref#18072280, Child_Node#18085837, Parent_id#18085839, Levels#18072308, tier#18072283, node_status#18072281, data_state#18072282, product_name#18085896, product_type#18087378, reference_code#18072284, SupplierName#18087383, SupplierCity#18087387, SupplierState#18087390, SupplierCountry#18087393, SupplierAddress#18087396, SupplierLat#18088129, SupplierLng#18088132, Supplier_declaration_RefID#18088135, ... 28 more fields]
(2) InMemoryRelation
Arguments: [NetworkID#18085833, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085834, revision_version#18069038, config_name#18085836, parent_launch_config_ref#18072280, Child_Node#18085837, Parent_id#18085839, Levels#18072308, tier#18072283, node_status#18072281, data_state#18072282, product_name#18085896, product_type#18087378, reference_code#18072284, SupplierName#18087383, SupplierCity#18087387, SupplierState#18087390, SupplierCountry#18087393, SupplierAddress#18087396, SupplierLat#18088129, SupplierLng#18088132, Supplier_declaration_RefID#18088135, ... 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#18079731 AS NetworkID#18085833, brand_context_id#18069035, company_id#18069036, coalesce(launch_config_ref#18069037, ) AS launch_config_ref#18085834, revision_version#18069038, CASE WHEN (trim(regexp_replace(regexp_replace(summary__config_name#18079175, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(summary__config_name#18079175, , , 1), {2,}, , 1), None) END AS config_name#18085836, parent_launch_config_ref#18072280, _id#18072274 AS Child_Node#18085837, parent_id#18072279 AS Parent_id#18085839, Levels#18072308, tier#18072283, node_status#18072281, data_state#18072282, resolved_product_name#18072532 AS product_name#18085896, CASE WHEN (trim(regexp_replace(regexp_replace(product_name#18072286, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(product_name#18072286, , , 1), {2,}, , 1), None) END AS product_type#18087378, reference_code#18072284, resolved_supplier_display_name#18074074 AS SupplierName#18087383, resolved_supplier_city#18072895 AS SupplierCity#18087387, resolved_supplier_state#18073081 AS SupplierState#18087390, resolved_supplier_country#18073271 AS SupplierCountry#18087393, resolved_supplier_address#18073667 AS SupplierAddress#18087396, data_address_lat#18072300 AS SupplierLat#18088129, data_address_lng#18072301 AS SupplierLng#18088132, resolved_supplier_ref#18073869 AS Supplier_declaration_RefID#18088135, ... 28 more fields]
+- *(2) BroadcastHashJoin [parent_id#18072279, brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [_p_id#18082488, _p_brand#18082490, _p_company#18082491, _p_launch#18082493, _p_revision#18082494], LeftOuter, BuildRight, false
:- TableCacheQueryStage 0
: +- InMemoryTableScan [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18079175, summary__submission_status#18079205, summary__is_latest#18079206, summary__source#18079302, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013, _id#18072274, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, data_address_lat#18072300, data_address_lng#18072301, supplier_type#18072304, nominated_node#18072305, ... 17 more fields]
: +- InMemoryRelation [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 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#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 49 more fields]
: +- BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278], 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=4617537]
: : +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18079175, config_version#18069041 AS summary__config_version#18079177, entity_id#18069042 AS summary__entity_id#18079178, submission_status#18069043 AS summary__submission_status#18079205, is_latest#18069044 AS summary__is_latest#18079206, is_editable#18069045 AS summary__is_editable#18079221, launch_date#18069046 AS summary__launch_date#18079295, expiry_date#18069047 AS summary__expiry_date#18079296, submitted_date#18069048 AS summary__submitted_date#18079297, create_ts#18069049 AS summary__create_ts#18079298, update_ts#18069050 AS summary__update_ts#18079299, create_date#18069051 AS summary__create_date#18079300, update_date#18069052 AS summary__update_date#18079301, source#18069053 AS summary__source#18079302, recurrence_count#18069054 AS summary__recurrence_count#18079303, priority#18069055 AS summary__priority#18079514, remarks#18069056 AS summary__remarks#18079655, latest_source#18069057 AS summary__latest_source#18079656, launch_request_name#18069058 AS summary__launch_request_name#18079657, NetworkID#18069039 AS summary__NetworkID#18079731, ... 2 more fields]
: : +- Filter ((((NetworkID#18069039 IN (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2) 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 (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2), 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#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, 5), REPARTITION_BY_NUM, [plan_id=4617390]
: +- Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 27 more fields]
: +- Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) AS _t) [numPartitions=1] [_id#18072274,brand_context_id#18072275,company_id#18072276,launch_config_ref#18072277,revision_version#18072278,parent_id#18072279,parent_launch_config_ref#18072280,node_status#18072281,data_state#18072282,tier#18072283,reference_code#18072284,source_type#18072285,product_name#18072286,product_alias_name#18072287,supplier_dec_name#18072288,supplier_dec_city#18072289,supplier_dec_state#18072290,supplier_dec_country#18072291,supplier_dec_address#18072292,supplier_dec__address_line2#18072293,data_supplier_name#18072294,data_address_city#18072295,data_address_state#18072296,data_address_country#18072297,... 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=4618914]
+- *(1) Project [_id#18082549 AS _p_id#18082488, brand_context_id#18082521 AS _p_brand#18082490, company_id#18082522 AS _p_company#18082491, launch_config_ref#18082523 AS _p_launch#18082493, revision_version#18082524 AS _p_revision#18082494, resolved_supplier_display_name#18074074 AS _p_display_name#18082495, node_status#18082556 AS _p_node_status#18082497, data_state#18082557 AS _p_data_state#18082498, resolved_supplier_city#18072895 AS _p_city#18082499, resolved_supplier_state#18073081 AS _p_state#18082500, resolved_supplier_country#18073271 AS _p_country#18082501, resolved_supplier_address#18073667 AS _p_address#18082502, data_address_lat#18082575 AS _p_lat#18082503, data_address_lng#18082576 AS _p_lng#18082504]
+- *(1) Filter ((((isnotnull(_id#18082549) AND isnotnull(brand_context_id#18082521)) AND isnotnull(company_id#18082522)) AND isnotnull(launch_config_ref#18082523)) AND isnotnull(revision_version#18082524))
+- TableCacheQueryStage 1
+- InMemoryTableScan [_id#18082549, brand_context_id#18082521, company_id#18082522, data_address_lat#18082575, data_address_lng#18082576, data_state#18082557, launch_config_ref#18082523, node_status#18082556, resolved_supplier_address#18073667, resolved_supplier_city#18072895, resolved_supplier_country#18073271, resolved_supplier_display_name#18074074, resolved_supplier_state#18073081, revision_version#18082524], [isnotnull(_id#18082549), isnotnull(brand_context_id#18082521), isnotnull(company_id#18082522), isnotnull(launch_config_ref#18082523), isnotnull(revision_version#18082524)]
+- InMemoryRelation [brand_context_id#18082521, company_id#18082522, launch_config_ref#18082523, revision_version#18082524, summary__config_name#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 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#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 49 more fields]
+- BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278], 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=4617544]
: +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18079175, config_version#18069041 AS summary__config_version#18079177, entity_id#18069042 AS summary__entity_id#18079178, submission_status#18069043 AS summary__submission_status#18079205, is_latest#18069044 AS summary__is_latest#18079206, is_editable#18069045 AS summary__is_editable#18079221, launch_date#18069046 AS summary__launch_date#18079295, expiry_date#18069047 AS summary__expiry_date#18079296, submitted_date#18069048 AS summary__submitted_date#18079297, create_ts#18069049 AS summary__create_ts#18079298, update_ts#18069050 AS summary__update_ts#18079299, create_date#18069051 AS summary__create_date#18079300, update_date#18069052 AS summary__update_date#18079301, source#18069053 AS summary__source#18079302, recurrence_count#18069054 AS summary__recurrence_count#18079303, priority#18069055 AS summary__priority#18079514, remarks#18069056 AS summary__remarks#18079655, latest_source#18069057 AS summary__latest_source#18079656, launch_request_name#18069058 AS summary__launch_request_name#18079657, NetworkID#18069039 AS summary__NetworkID#18079731, ... 2 more fields]
: +- Filter ((((NetworkID#18069039 IN (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2) 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 (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2), 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#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, 5), REPARTITION_BY_NUM, [plan_id=4617390]
+- Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 27 more fields]
+- Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) AS _t) [numPartitions=1] [_id#18072274,brand_context_id#18072275,company_id#18072276,launch_config_ref#18072277,revision_version#18072278,parent_id#18072279,parent_launch_config_ref#18072280,node_status#18072281,data_state#18072282,tier#18072283,reference_code#18072284,source_type#18072285,product_name#18072286,product_alias_name#18072287,supplier_dec_name#18072288,supplier_dec_city#18072289,supplier_dec_state#18072290,supplier_dec_country#18072291,supplier_dec_address#18072292,supplier_dec__address_line2#18072293,data_supplier_name#18072294,data_address_city#18072295,data_address_state#18072296,data_address_country#18072297,... 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#18079731 AS NetworkID#18085833, brand_context_id#18069035, company_id#18069036, coalesce(launch_config_ref#18069037, ) AS launch_config_ref#18085834, revision_version#18069038, CASE WHEN (trim(regexp_replace(regexp_replace(summary__config_name#18079175, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(summary__config_name#18079175, , , 1), {2,}, , 1), None) END AS config_name#18085836, parent_launch_config_ref#18072280, _id#18072274 AS Child_Node#18085837, parent_id#18072279 AS Parent_id#18085839, Levels#18072308, tier#18072283, node_status#18072281, data_state#18072282, resolved_product_name#18072532 AS product_name#18085896, CASE WHEN (trim(regexp_replace(regexp_replace(product_name#18072286, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(product_name#18072286, , , 1), {2,}, , 1), None) END AS product_type#18087378, reference_code#18072284, resolved_supplier_display_name#18074074 AS SupplierName#18087383, resolved_supplier_city#18072895 AS SupplierCity#18087387, resolved_supplier_state#18073081 AS SupplierState#18087390, resolved_supplier_country#18073271 AS SupplierCountry#18087393, resolved_supplier_address#18073667 AS SupplierAddress#18087396, data_address_lat#18072300 AS SupplierLat#18088129, data_address_lng#18072301 AS SupplierLng#18088132, resolved_supplier_ref#18073869 AS Supplier_declaration_RefID#18088135, ... 28 more fields]
+- BroadcastHashJoin [parent_id#18072279, brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [_p_id#18082488, _p_brand#18082490, _p_company#18082491, _p_launch#18082493, _p_revision#18082494], LeftOuter, BuildRight, false
:- InMemoryTableScan [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18079175, summary__submission_status#18079205, summary__is_latest#18079206, summary__source#18079302, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013, _id#18072274, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, data_address_lat#18072300, data_address_lng#18072301, supplier_type#18072304, nominated_node#18072305, ... 17 more fields]
: +- InMemoryRelation [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 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#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 49 more fields]
: +- BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278], 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=4617537]
: : +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18079175, config_version#18069041 AS summary__config_version#18079177, entity_id#18069042 AS summary__entity_id#18079178, submission_status#18069043 AS summary__submission_status#18079205, is_latest#18069044 AS summary__is_latest#18079206, is_editable#18069045 AS summary__is_editable#18079221, launch_date#18069046 AS summary__launch_date#18079295, expiry_date#18069047 AS summary__expiry_date#18079296, submitted_date#18069048 AS summary__submitted_date#18079297, create_ts#18069049 AS summary__create_ts#18079298, update_ts#18069050 AS summary__update_ts#18079299, create_date#18069051 AS summary__create_date#18079300, update_date#18069052 AS summary__update_date#18079301, source#18069053 AS summary__source#18079302, recurrence_count#18069054 AS summary__recurrence_count#18079303, priority#18069055 AS summary__priority#18079514, remarks#18069056 AS summary__remarks#18079655, latest_source#18069057 AS summary__latest_source#18079656, launch_request_name#18069058 AS summary__launch_request_name#18079657, NetworkID#18069039 AS summary__NetworkID#18079731, ... 2 more fields]
: : +- Filter ((((NetworkID#18069039 IN (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2) 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 (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2), 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#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, 5), REPARTITION_BY_NUM, [plan_id=4617390]
: +- Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 27 more fields]
: +- Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) AS _t) [numPartitions=1] [_id#18072274,brand_context_id#18072275,company_id#18072276,launch_config_ref#18072277,revision_version#18072278,parent_id#18072279,parent_launch_config_ref#18072280,node_status#18072281,data_state#18072282,tier#18072283,reference_code#18072284,source_type#18072285,product_name#18072286,product_alias_name#18072287,supplier_dec_name#18072288,supplier_dec_city#18072289,supplier_dec_state#18072290,supplier_dec_country#18072291,supplier_dec_address#18072292,supplier_dec__address_line2#18072293,data_supplier_name#18072294,data_address_city#18072295,data_address_state#18072296,data_address_country#18072297,... 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=4617533]
+- Project [_id#18082549 AS _p_id#18082488, brand_context_id#18082521 AS _p_brand#18082490, company_id#18082522 AS _p_company#18082491, launch_config_ref#18082523 AS _p_launch#18082493, revision_version#18082524 AS _p_revision#18082494, resolved_supplier_display_name#18074074 AS _p_display_name#18082495, node_status#18082556 AS _p_node_status#18082497, data_state#18082557 AS _p_data_state#18082498, resolved_supplier_city#18072895 AS _p_city#18082499, resolved_supplier_state#18073081 AS _p_state#18082500, resolved_supplier_country#18073271 AS _p_country#18082501, resolved_supplier_address#18073667 AS _p_address#18082502, data_address_lat#18082575 AS _p_lat#18082503, data_address_lng#18082576 AS _p_lng#18082504]
+- Filter ((((isnotnull(_id#18082549) AND isnotnull(brand_context_id#18082521)) AND isnotnull(company_id#18082522)) AND isnotnull(launch_config_ref#18082523)) AND isnotnull(revision_version#18082524))
+- InMemoryTableScan [_id#18082549, brand_context_id#18082521, company_id#18082522, data_address_lat#18082575, data_address_lng#18082576, data_state#18082557, launch_config_ref#18082523, node_status#18082556, resolved_supplier_address#18073667, resolved_supplier_city#18072895, resolved_supplier_country#18073271, resolved_supplier_display_name#18074074, resolved_supplier_state#18073081, revision_version#18082524], [isnotnull(_id#18082549), isnotnull(brand_context_id#18082521), isnotnull(company_id#18082522), isnotnull(launch_config_ref#18082523), isnotnull(revision_version#18082524)]
+- InMemoryRelation [brand_context_id#18082521, company_id#18082522, launch_config_ref#18082523, revision_version#18082524, summary__config_name#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 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#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 49 more fields]
+- BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278], 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=4617544]
: +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18079175, config_version#18069041 AS summary__config_version#18079177, entity_id#18069042 AS summary__entity_id#18079178, submission_status#18069043 AS summary__submission_status#18079205, is_latest#18069044 AS summary__is_latest#18079206, is_editable#18069045 AS summary__is_editable#18079221, launch_date#18069046 AS summary__launch_date#18079295, expiry_date#18069047 AS summary__expiry_date#18079296, submitted_date#18069048 AS summary__submitted_date#18079297, create_ts#18069049 AS summary__create_ts#18079298, update_ts#18069050 AS summary__update_ts#18079299, create_date#18069051 AS summary__create_date#18079300, update_date#18069052 AS summary__update_date#18079301, source#18069053 AS summary__source#18079302, recurrence_count#18069054 AS summary__recurrence_count#18079303, priority#18069055 AS summary__priority#18079514, remarks#18069056 AS summary__remarks#18079655, latest_source#18069057 AS summary__latest_source#18079656, launch_request_name#18069058 AS summary__launch_request_name#18079657, NetworkID#18069039 AS summary__NetworkID#18079731, ... 2 more fields]
: +- Filter ((((NetworkID#18069039 IN (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2) 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 (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2), 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#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, 5), REPARTITION_BY_NUM, [plan_id=4617390]
+- Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 27 more fields]
+- Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) AS _t) [numPartitions=1] [_id#18072274,brand_context_id#18072275,company_id#18072276,launch_config_ref#18072277,revision_version#18072278,parent_id#18072279,parent_launch_config_ref#18072280,node_status#18072281,data_state#18072282,tier#18072283,reference_code#18072284,source_type#18072285,product_name#18072286,product_alias_name#18072287,supplier_dec_name#18072288,supplier_dec_city#18072289,supplier_dec_state#18072290,supplier_dec_country#18072291,supplier_dec_address#18072292,supplier_dec__address_line2#18072293,data_supplier_name#18072294,data_address_city#18072295,data_address_state#18072296,data_address_country#18072297,... 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#18079175, summary__submission_status#18079205, summary__is_latest#18079206, summary__source#18079302, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013, _id#18072274, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, data_address_lat#18072300, data_address_lng#18072301, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315, resolved_product_name#18072532, resolved_supplier_city#18072895, resolved_supplier_state#18073081, resolved_supplier_country#18073271, resolved_supplier_address#18073667, resolved_supplier_ref#18073869, is_unknown_supplier#18073972, resolved_supplier_display_name#18074074]
Arguments: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18079175, summary__submission_status#18079205, summary__is_latest#18079206, summary__source#18079302, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013, _id#18072274, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, data_address_lat#18072300, data_address_lng#18072301, supplier_type#18072304, nominated_node#18072305, ... 17 more fields]
(4) InMemoryRelation
Arguments: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 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#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 49 more fields]
+- *(3) BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278], 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=4617865]
: +- *(2) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18079175, config_version#18069041 AS summary__config_version#18079177, entity_id#18069042 AS summary__entity_id#18079178, submission_status#18069043 AS summary__submission_status#18079205, is_latest#18069044 AS summary__is_latest#18079206, is_editable#18069045 AS summary__is_editable#18079221, launch_date#18069046 AS summary__launch_date#18079295, expiry_date#18069047 AS summary__expiry_date#18079296, submitted_date#18069048 AS summary__submitted_date#18079297, create_ts#18069049 AS summary__create_ts#18079298, update_ts#18069050 AS summary__update_ts#18079299, create_date#18069051 AS summary__create_date#18079300, update_date#18069052 AS summary__update_date#18079301, source#18069053 AS summary__source#18079302, recurrence_count#18069054 AS summary__recurrence_count#18079303, priority#18069055 AS summary__priority#18079514, remarks#18069056 AS summary__remarks#18079655, latest_source#18069057 AS summary__latest_source#18079656, launch_request_name#18069058 AS summary__launch_request_name#18079657, NetworkID#18069039 AS summary__NetworkID#18079731, ... 2 more fields]
: +- *(2) Filter ((((NetworkID#18069039 IN (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2) 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 (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2), 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#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, 5), REPARTITION_BY_NUM, [plan_id=4617831]
+- *(1) Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 27 more fields]
+- *(1) Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) AS _t) [numPartitions=1] [_id#18072274,brand_context_id#18072275,company_id#18072276,launch_config_ref#18072277,revision_version#18072278,parent_id#18072279,parent_launch_config_ref#18072280,node_status#18072281,data_state#18072282,tier#18072283,reference_code#18072284,source_type#18072285,product_name#18072286,product_alias_name#18072287,supplier_dec_name#18072288,supplier_dec_city#18072289,supplier_dec_state#18072290,supplier_dec_country#18072291,supplier_dec_address#18072292,supplier_dec__address_line2#18072293,data_supplier_name#18072294,data_address_city#18072295,data_address_state#18072296,data_address_country#18072297,... 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#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 49 more fields]
+- BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278], 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=4617394]
: +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18079175, config_version#18069041 AS summary__config_version#18079177, entity_id#18069042 AS summary__entity_id#18079178, submission_status#18069043 AS summary__submission_status#18079205, is_latest#18069044 AS summary__is_latest#18079206, is_editable#18069045 AS summary__is_editable#18079221, launch_date#18069046 AS summary__launch_date#18079295, expiry_date#18069047 AS summary__expiry_date#18079296, submitted_date#18069048 AS summary__submitted_date#18079297, create_ts#18069049 AS summary__create_ts#18079298, update_ts#18069050 AS summary__update_ts#18079299, create_date#18069051 AS summary__create_date#18079300, update_date#18069052 AS summary__update_date#18079301, source#18069053 AS summary__source#18079302, recurrence_count#18069054 AS summary__recurrence_count#18079303, priority#18069055 AS summary__priority#18079514, remarks#18069056 AS summary__remarks#18079655, latest_source#18069057 AS summary__latest_source#18079656, launch_request_name#18069058 AS summary__launch_request_name#18079657, NetworkID#18069039 AS summary__NetworkID#18079731, ... 2 more fields]
: +- Filter ((((NetworkID#18069039 IN (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2) 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 (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2), 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#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, 5), REPARTITION_BY_NUM, [plan_id=4617390]
+- Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 27 more fields]
+- Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) AS _t) [numPartitions=1] [_id#18072274,brand_context_id#18072275,company_id#18072276,launch_config_ref#18072277,revision_version#18072278,parent_id#18072279,parent_launch_config_ref#18072280,node_status#18072281,data_state#18072282,tier#18072283,reference_code#18072284,source_type#18072285,product_name#18072286,product_alias_name#18072287,supplier_dec_name#18072288,supplier_dec_city#18072289,supplier_dec_state#18072290,supplier_dec_country#18072291,supplier_dec_address#18072292,supplier_dec__address_line2#18072293,data_supplier_name#18072294,data_address_city#18072295,data_address_state#18072296,data_address_country#18072297,... 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 (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2), 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 (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2) 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#18079175, config_version#18069041 AS summary__config_version#18079177, entity_id#18069042 AS summary__entity_id#18079178, submission_status#18069043 AS summary__submission_status#18079205, is_latest#18069044 AS summary__is_latest#18079206, is_editable#18069045 AS summary__is_editable#18079221, launch_date#18069046 AS summary__launch_date#18079295, expiry_date#18069047 AS summary__expiry_date#18079296, submitted_date#18069048 AS summary__submitted_date#18079297, create_ts#18069049 AS summary__create_ts#18079298, update_ts#18069050 AS summary__update_ts#18079299, create_date#18069051 AS summary__create_date#18079300, update_date#18069052 AS summary__update_date#18079301, source#18069053 AS summary__source#18079302, recurrence_count#18069054 AS summary__recurrence_count#18079303, priority#18069055 AS summary__priority#18079514, remarks#18069056 AS summary__remarks#18079655, latest_source#18069057 AS summary__latest_source#18079656, launch_request_name#18069058 AS summary__launch_request_name#18079657, NetworkID#18069039 AS summary__NetworkID#18079731, direct_supplier_name#18069091 AS summary__direct_supplier_name#18079732, supplier_uid#18069060 AS summary__supplier_uid#18080013]
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#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013]
Arguments: HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, smallint, true]),false), [plan_id=4617537]
(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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) AS _t) [numPartitions=1]
Output [42]: [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, data_address_line1#18072298, data_address_line2#18072299, data_address_lat#18072300, data_address_lng#18072301, derived_supplier_dec_ref#18072302, data_supplier_default_ref#18072303, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, key_values#18072307, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315]
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#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, data_address_line1#18072298, data_address_line2#18072299, data_address_lat#18072300, data_address_lng#18072301, derived_supplier_dec_ref#18072302, data_supplier_default_ref#18072303, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, key_values#18072307, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315, coalesce(CASE WHEN (trim(regexp_replace(regexp_replace(product_alias_name#18072287, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(product_alias_name#18072287, , , 1), {2,}, , 1), None) END, CASE WHEN (trim(regexp_replace(regexp_replace(product_name#18072286, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(product_name#18072286, , , 1), {2,}, , 1), None) END) AS resolved_product_name#18072532, CASE WHEN (trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_supplier_name#18072294) OR (data_supplier_name#18072294 = )) THEN null ELSE data_supplier_name#18072294 END, CASE WHEN (isnull(supplier_dec_name#18072288) OR (supplier_dec_name#18072288 = )) THEN null ELSE supplier_dec_name#18072288 END), , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_supplier_name#18072294) OR (data_supplier_name#18072294 = )) THEN null ELSE data_supplier_name#18072294 END, CASE WHEN (isnull(supplier_dec_name#18072288) OR (supplier_dec_name#18072288 = )) THEN null ELSE supplier_dec_name#18072288 END), , , 1), {2,}, , 1), None) END AS resolved_supplier_name#18072711, CASE WHEN (trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_address_city#18072295) OR (data_address_city#18072295 = )) THEN null ELSE data_address_city#18072295 END, CASE WHEN (isnull(supplier_dec_city#18072289) OR (supplier_dec_city#18072289 = )) THEN null ELSE supplier_dec_city#18072289 END), , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_address_city#18072295) OR (data_address_city#18072295 = )) THEN null ELSE data_address_city#18072295 END, CASE WHEN (isnull(supplier_dec_city#18072289) OR (supplier_dec_city#18072289 = )) THEN null ELSE supplier_dec_city#18072289 END), , , 1), {2,}, , 1), None) END AS resolved_supplier_city#18072895, CASE WHEN (trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_address_state#18072296) OR (data_address_state#18072296 = )) THEN null ELSE data_address_state#18072296 END, CASE WHEN (isnull(supplier_dec_state#18072290) OR (supplier_dec_state#18072290 = )) THEN null ELSE supplier_dec_state#18072290 END), , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_address_state#18072296) OR (data_address_state#18072296 = )) THEN null ELSE data_address_state#18072296 END, CASE WHEN (isnull(supplier_dec_state#18072290) OR (supplier_dec_state#18072290 = )) THEN null ELSE supplier_dec_state#18072290 END), , , 1), {2,}, , 1), None) END AS resolved_supplier_state#18073081, CASE WHEN (trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_address_country#18072297) OR (data_address_country#18072297 = )) THEN null ELSE data_address_country#18072297 END, CASE WHEN (isnull(supplier_dec_country#18072291) OR (supplier_dec_country#18072291 = )) THEN null ELSE supplier_dec_country#18072291 END), , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(coalesce(CASE WHEN (isnull(data_address_country#18072297) OR (data_address_country#18072297 = )) THEN null ELSE data_address_country#18072297 END, CASE WHEN (isnull(supplier_dec_country#18072291) OR (supplier_dec_country#18072291 = )) THEN null ELSE supplier_dec_country#18072291 END), , , 1), {2,}, , 1), None) END AS resolved_supplier_country#18073271, CASE WHEN (trim(regexp_replace(regexp_replace(CASE WHEN NOT (coalesce(CASE WHEN (isnull(supplier_dec__address_line2#18072293) OR (supplier_dec__address_line2#18072293 = )) THEN null ELSE supplier_dec__address_line2#18072293 END, CASE WHEN (isnull(data_address_line2#18072299) OR (data_address_line2#18072299 = )) THEN null ELSE data_address_line2#18072299 END, ) = ) THEN concat(coalesce(CASE WHEN (isnull(supplier_dec_address#18072292) OR (supplier_dec_address#18072292 = )) THEN null ELSE supplier_dec_address#18072292 END, CASE WHEN (isnull(data_address_line1#18072298) OR (data_address_line1#18072298 = )) THEN null ELSE data_address_line1#18072298 END, ), , , coalesce(CASE WHEN (isnull(supplier_dec__address_line2#18072293) OR (supplier_dec__address_line2#18072293 = )) THEN null ELSE supplier_dec__address_line2#18072293 END, CASE WHEN (isnull(data_address_line2#18072299) OR (data_address_line2#18072299 = )) THEN null ELSE data_address_line2#18072299 END, )) ELSE coalesce(CASE WHEN (isnull(supplier_dec_address#18072292) OR (supplier_dec_address#18072292 = )) THEN null ELSE supplier_dec_address#18072292 END, CASE WHEN (isnull(data_address_line1#18072298) OR (data_address_line1#18072298 = )) THEN null ELSE data_address_line1#18072298 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#18072293) OR (supplier_dec__address_line2#18072293 = )) THEN null ELSE supplier_dec__address_line2#18072293 END, CASE WHEN (isnull(data_address_line2#18072299) OR (data_address_line2#18072299 = )) THEN null ELSE data_address_line2#18072299 END, ) = ) THEN concat(coalesce(CASE WHEN (isnull(supplier_dec_address#18072292) OR (supplier_dec_address#18072292 = )) THEN null ELSE supplier_dec_address#18072292 END, CASE WHEN (isnull(data_address_line1#18072298) OR (data_address_line1#18072298 = )) THEN null ELSE data_address_line1#18072298 END, ), , , coalesce(CASE WHEN (isnull(supplier_dec__address_line2#18072293) OR (supplier_dec__address_line2#18072293 = )) THEN null ELSE supplier_dec__address_line2#18072293 END, CASE WHEN (isnull(data_address_line2#18072299) OR (data_address_line2#18072299 = )) THEN null ELSE data_address_line2#18072299 END, )) ELSE coalesce(CASE WHEN (isnull(supplier_dec_address#18072292) OR (supplier_dec_address#18072292 = )) THEN null ELSE supplier_dec_address#18072292 END, CASE WHEN (isnull(data_address_line1#18072298) OR (data_address_line1#18072298 = )) THEN null ELSE data_address_line1#18072298 END, ) END, , , 1), {2,}, , 1), None) END AS resolved_supplier_address#18073667, coalesce(CASE WHEN (isnull(derived_supplier_dec_ref#18072302) OR (derived_supplier_dec_ref#18072302 = )) THEN null ELSE derived_supplier_dec_ref#18072302 END, CASE WHEN (isnull(data_supplier_default_ref#18072303) OR (data_supplier_default_ref#18072303 = )) THEN null ELSE data_supplier_default_ref#18072303 END) AS resolved_supplier_ref#18073869, (isnull(derived_supplier_dec_ref#18072302) AND coalesce((data_supplier_name#18072294 = Supplier Unknown), false)) AS is_unknown_supplier#18073972]
Input [42]: [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, data_address_line1#18072298, data_address_line2#18072299, data_address_lat#18072300, data_address_lng#18072301, derived_supplier_dec_ref#18072302, data_supplier_default_ref#18072303, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, key_values#18072307, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315]
(14) Project
Output [51]: [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, data_address_line1#18072298, data_address_line2#18072299, data_address_lat#18072300, data_address_lng#18072301, derived_supplier_dec_ref#18072302, data_supplier_default_ref#18072303, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, key_values#18072307, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315, resolved_product_name#18072532, resolved_supplier_name#18072711, resolved_supplier_city#18072895, resolved_supplier_state#18073081, resolved_supplier_country#18073271, resolved_supplier_address#18073667, resolved_supplier_ref#18073869, is_unknown_supplier#18073972, CASE WHEN (isnull(resolved_supplier_name#18072711) AND coalesce((data_state#18072282 = Not Applicable), false)) THEN Not Applicable WHEN (isnull(resolved_supplier_name#18072711) AND coalesce((node_status#18072281 = 30), false)) THEN Supplier Unknown ELSE resolved_supplier_name#18072711 END AS resolved_supplier_display_name#18074074]
Input [50]: [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, data_address_line1#18072298, data_address_line2#18072299, data_address_lat#18072300, data_address_lng#18072301, derived_supplier_dec_ref#18072302, data_supplier_default_ref#18072303, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, key_values#18072307, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315, resolved_product_name#18072532, resolved_supplier_name#18072711, resolved_supplier_city#18072895, resolved_supplier_state#18073081, resolved_supplier_country#18073271, resolved_supplier_address#18073667, resolved_supplier_ref#18073869, is_unknown_supplier#18073972]
(15) Exchange
Input [51]: [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, data_address_line1#18072298, data_address_line2#18072299, data_address_lat#18072300, data_address_lng#18072301, derived_supplier_dec_ref#18072302, data_supplier_default_ref#18072303, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, key_values#18072307, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315, resolved_product_name#18072532, resolved_supplier_name#18072711, resolved_supplier_city#18072895, resolved_supplier_state#18073081, resolved_supplier_country#18073271, resolved_supplier_address#18073667, resolved_supplier_ref#18073869, is_unknown_supplier#18073972, resolved_supplier_display_name#18074074]
Arguments: hashpartitioning(brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, 5), REPARTITION_BY_NUM, [plan_id=4617390]
(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#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278]
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#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013, _id#18072274, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, data_address_line1#18072298, data_address_line2#18072299, data_address_lat#18072300, data_address_lng#18072301, derived_supplier_dec_ref#18072302, data_supplier_default_ref#18072303, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, key_values#18072307, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315, resolved_product_name#18072532, resolved_supplier_name#18072711, resolved_supplier_city#18072895, resolved_supplier_state#18073081, resolved_supplier_country#18073271, resolved_supplier_address#18073667, resolved_supplier_ref#18073869, is_unknown_supplier#18073972, resolved_supplier_display_name#18074074]
Input [77]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013, _id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, data_address_line1#18072298, data_address_line2#18072299, data_address_lat#18072300, data_address_lng#18072301, derived_supplier_dec_ref#18072302, data_supplier_default_ref#18072303, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, key_values#18072307, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315, resolved_product_name#18072532, resolved_supplier_name#18072711, resolved_supplier_city#18072895, resolved_supplier_state#18073081, resolved_supplier_country#18073271, resolved_supplier_address#18073667, resolved_supplier_ref#18073869, is_unknown_supplier#18073972, resolved_supplier_display_name#18074074]
(18) AdaptiveSparkPlan
Output [73]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013, _id#18072274, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, data_address_line1#18072298, data_address_line2#18072299, data_address_lat#18072300, data_address_lng#18072301, derived_supplier_dec_ref#18072302, data_supplier_default_ref#18072303, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, key_values#18072307, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315, resolved_product_name#18072532, resolved_supplier_name#18072711, resolved_supplier_city#18072895, resolved_supplier_state#18073081, resolved_supplier_country#18073271, resolved_supplier_address#18073667, resolved_supplier_ref#18073869, is_unknown_supplier#18073972, resolved_supplier_display_name#18074074]
Arguments: isFinalPlan=false
(19) InMemoryTableScan
Output [14]: [_id#18082549, brand_context_id#18082521, company_id#18082522, data_address_lat#18082575, data_address_lng#18082576, data_state#18082557, launch_config_ref#18082523, node_status#18082556, resolved_supplier_address#18073667, resolved_supplier_city#18072895, resolved_supplier_country#18073271, resolved_supplier_display_name#18074074, resolved_supplier_state#18073081, revision_version#18082524]
Arguments: [_id#18082549, brand_context_id#18082521, company_id#18082522, data_address_lat#18082575, data_address_lng#18082576, data_state#18082557, launch_config_ref#18082523, node_status#18082556, resolved_supplier_address#18073667, resolved_supplier_city#18072895, resolved_supplier_country#18073271, resolved_supplier_display_name#18074074, resolved_supplier_state#18073081, revision_version#18082524], [isnotnull(_id#18082549), isnotnull(brand_context_id#18082521), isnotnull(company_id#18082522), isnotnull(launch_config_ref#18082523), isnotnull(revision_version#18082524)]
(20) InMemoryRelation
Arguments: [brand_context_id#18082521, company_id#18082522, launch_config_ref#18082523, revision_version#18082524, summary__config_name#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 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#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 49 more fields]
+- *(3) BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278], 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=4617865]
: +- *(2) Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18079175, config_version#18069041 AS summary__config_version#18079177, entity_id#18069042 AS summary__entity_id#18079178, submission_status#18069043 AS summary__submission_status#18079205, is_latest#18069044 AS summary__is_latest#18079206, is_editable#18069045 AS summary__is_editable#18079221, launch_date#18069046 AS summary__launch_date#18079295, expiry_date#18069047 AS summary__expiry_date#18079296, submitted_date#18069048 AS summary__submitted_date#18079297, create_ts#18069049 AS summary__create_ts#18079298, update_ts#18069050 AS summary__update_ts#18079299, create_date#18069051 AS summary__create_date#18079300, update_date#18069052 AS summary__update_date#18079301, source#18069053 AS summary__source#18079302, recurrence_count#18069054 AS summary__recurrence_count#18079303, priority#18069055 AS summary__priority#18079514, remarks#18069056 AS summary__remarks#18079655, latest_source#18069057 AS summary__latest_source#18079656, launch_request_name#18069058 AS summary__launch_request_name#18079657, NetworkID#18069039 AS summary__NetworkID#18079731, ... 2 more fields]
: +- *(2) Filter ((((NetworkID#18069039 IN (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2) 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 (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2), 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#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, 5), REPARTITION_BY_NUM, [plan_id=4617831]
+- *(1) Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 27 more fields]
+- *(1) Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) AS _t) [numPartitions=1] [_id#18072274,brand_context_id#18072275,company_id#18072276,launch_config_ref#18072277,revision_version#18072278,parent_id#18072279,parent_launch_config_ref#18072280,node_status#18072281,data_state#18072282,tier#18072283,reference_code#18072284,source_type#18072285,product_name#18072286,product_alias_name#18072287,supplier_dec_name#18072288,supplier_dec_city#18072289,supplier_dec_state#18072290,supplier_dec_country#18072291,supplier_dec_address#18072292,supplier_dec__address_line2#18072293,data_supplier_name#18072294,data_address_city#18072295,data_address_state#18072296,data_address_country#18072297,... 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#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, ... 49 more fields]
+- BroadcastHashJoin [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038], [brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278], 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=4617394]
: +- Project [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, config_name#18069040 AS summary__config_name#18079175, config_version#18069041 AS summary__config_version#18079177, entity_id#18069042 AS summary__entity_id#18079178, submission_status#18069043 AS summary__submission_status#18079205, is_latest#18069044 AS summary__is_latest#18079206, is_editable#18069045 AS summary__is_editable#18079221, launch_date#18069046 AS summary__launch_date#18079295, expiry_date#18069047 AS summary__expiry_date#18079296, submitted_date#18069048 AS summary__submitted_date#18079297, create_ts#18069049 AS summary__create_ts#18079298, update_ts#18069050 AS summary__update_ts#18079299, create_date#18069051 AS summary__create_date#18079300, update_date#18069052 AS summary__update_date#18079301, source#18069053 AS summary__source#18079302, recurrence_count#18069054 AS summary__recurrence_count#18079303, priority#18069055 AS summary__priority#18079514, remarks#18069056 AS summary__remarks#18079655, latest_source#18069057 AS summary__latest_source#18079656, launch_request_name#18069058 AS summary__launch_request_name#18079657, NetworkID#18069039 AS summary__NetworkID#18079731, ... 2 more fields]
: +- Filter ((((NetworkID#18069039 IN (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2) 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 (771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC,7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1,92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B,A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D,AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2), 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#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, 5), REPARTITION_BY_NUM, [plan_id=4617390]
+- Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 27 more fields]
+- Project [_id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, ... 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 ('771B598233F4F483B3A7B70672D43B94003BCC86C5E482841D43AFFC0C7895AC', '7A955BA28CC83E11DC178E7E2718D604DB7C2B71E9B99819C801DEEBDEBA2AA1', '92E8C8A13844E64075D547467A757705DD55B83762EA62ADBCBE28EB778F965B', 'A2A9E9C449997C986ACA673B3CCFF865C70F521FCEFE33CA926A746780C00A1D', 'AC77DA4D002E19A40A457097A09C9B1E28412740B0C8CEE8427E07B9CB7DC9D2'))
) AS _t) [numPartitions=1] [_id#18072274,brand_context_id#18072275,company_id#18072276,launch_config_ref#18072277,revision_version#18072278,parent_id#18072279,parent_launch_config_ref#18072280,node_status#18072281,data_state#18072282,tier#18072283,reference_code#18072284,source_type#18072285,product_name#18072286,product_alias_name#18072287,supplier_dec_name#18072288,supplier_dec_city#18072289,supplier_dec_state#18072290,supplier_dec_country#18072291,supplier_dec_address#18072292,supplier_dec__address_line2#18072293,data_supplier_name#18072294,data_address_city#18072295,data_address_state#18072296,data_address_country#18072297,... 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#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013]
Arguments: HashedRelationBroadcastMode(List(input[0, string, true], input[1, string, true], input[2, string, true], input[3, smallint, true]),false), [plan_id=4617544]
(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#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278]
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#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013, _id#18072274, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, data_address_line1#18072298, data_address_line2#18072299, data_address_lat#18072300, data_address_lng#18072301, derived_supplier_dec_ref#18072302, data_supplier_default_ref#18072303, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, key_values#18072307, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315, resolved_product_name#18072532, resolved_supplier_name#18072711, resolved_supplier_city#18072895, resolved_supplier_state#18073081, resolved_supplier_country#18073271, resolved_supplier_address#18073667, resolved_supplier_ref#18073869, is_unknown_supplier#18073972, resolved_supplier_display_name#18074074]
Input [77]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013, _id#18072274, brand_context_id#18072275, company_id#18072276, launch_config_ref#18072277, revision_version#18072278, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, data_address_line1#18072298, data_address_line2#18072299, data_address_lat#18072300, data_address_lng#18072301, derived_supplier_dec_ref#18072302, data_supplier_default_ref#18072303, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, key_values#18072307, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315, resolved_product_name#18072532, resolved_supplier_name#18072711, resolved_supplier_city#18072895, resolved_supplier_state#18073081, resolved_supplier_country#18073271, resolved_supplier_address#18073667, resolved_supplier_ref#18073869, is_unknown_supplier#18073972, resolved_supplier_display_name#18074074]
(24) AdaptiveSparkPlan
Output [73]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18079175, summary__config_version#18079177, summary__entity_id#18079178, summary__submission_status#18079205, summary__is_latest#18079206, summary__is_editable#18079221, summary__launch_date#18079295, summary__expiry_date#18079296, summary__submitted_date#18079297, summary__create_ts#18079298, summary__update_ts#18079299, summary__create_date#18079300, summary__update_date#18079301, summary__source#18079302, summary__recurrence_count#18079303, summary__priority#18079514, summary__remarks#18079655, summary__latest_source#18079656, summary__launch_request_name#18079657, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013, _id#18072274, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, product_alias_name#18072287, supplier_dec_name#18072288, supplier_dec_city#18072289, supplier_dec_state#18072290, supplier_dec_country#18072291, supplier_dec_address#18072292, supplier_dec__address_line2#18072293, data_supplier_name#18072294, data_address_city#18072295, data_address_state#18072296, data_address_country#18072297, data_address_line1#18072298, data_address_line2#18072299, data_address_lat#18072300, data_address_lng#18072301, derived_supplier_dec_ref#18072302, data_supplier_default_ref#18072303, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, key_values#18072307, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315, resolved_product_name#18072532, resolved_supplier_name#18072711, resolved_supplier_city#18072895, resolved_supplier_state#18073081, resolved_supplier_country#18073271, resolved_supplier_address#18073667, resolved_supplier_ref#18073869, is_unknown_supplier#18073972, resolved_supplier_display_name#18074074]
Arguments: isFinalPlan=false
(25) Filter
Input [14]: [_id#18082549, brand_context_id#18082521, company_id#18082522, data_address_lat#18082575, data_address_lng#18082576, data_state#18082557, launch_config_ref#18082523, node_status#18082556, resolved_supplier_address#18073667, resolved_supplier_city#18072895, resolved_supplier_country#18073271, resolved_supplier_display_name#18074074, resolved_supplier_state#18073081, revision_version#18082524]
Condition : ((((isnotnull(_id#18082549) AND isnotnull(brand_context_id#18082521)) AND isnotnull(company_id#18082522)) AND isnotnull(launch_config_ref#18082523)) AND isnotnull(revision_version#18082524))
(26) Project
Output [14]: [_id#18082549 AS _p_id#18082488, brand_context_id#18082521 AS _p_brand#18082490, company_id#18082522 AS _p_company#18082491, launch_config_ref#18082523 AS _p_launch#18082493, revision_version#18082524 AS _p_revision#18082494, resolved_supplier_display_name#18074074 AS _p_display_name#18082495, node_status#18082556 AS _p_node_status#18082497, data_state#18082557 AS _p_data_state#18082498, resolved_supplier_city#18072895 AS _p_city#18082499, resolved_supplier_state#18073081 AS _p_state#18082500, resolved_supplier_country#18073271 AS _p_country#18082501, resolved_supplier_address#18073667 AS _p_address#18082502, data_address_lat#18082575 AS _p_lat#18082503, data_address_lng#18082576 AS _p_lng#18082504]
Input [14]: [_id#18082549, brand_context_id#18082521, company_id#18082522, data_address_lat#18082575, data_address_lng#18082576, data_state#18082557, launch_config_ref#18082523, node_status#18082556, resolved_supplier_address#18073667, resolved_supplier_city#18072895, resolved_supplier_country#18073271, resolved_supplier_display_name#18074074, resolved_supplier_state#18073081, revision_version#18082524]
(27) BroadcastExchange
Input [14]: [_p_id#18082488, _p_brand#18082490, _p_company#18082491, _p_launch#18082493, _p_revision#18082494, _p_display_name#18082495, _p_node_status#18082497, _p_data_state#18082498, _p_city#18082499, _p_state#18082500, _p_country#18082501, _p_address#18082502, _p_lat#18082503, _p_lng#18082504]
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=4618999]
(28) BroadcastHashJoin
Left keys [5]: [parent_id#18072279, brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038]
Right keys [5]: [_p_id#18082488, _p_brand#18082490, _p_company#18082491, _p_launch#18082493, _p_revision#18082494]
Join type: LeftOuter
Join condition: None
(29) Project
Output [52]: [summary__NetworkID#18079731 AS NetworkID#18085833, brand_context_id#18069035, company_id#18069036, coalesce(launch_config_ref#18069037, ) AS launch_config_ref#18085834, revision_version#18069038, CASE WHEN (trim(regexp_replace(regexp_replace(summary__config_name#18079175, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(summary__config_name#18079175, , , 1), {2,}, , 1), None) END AS config_name#18085836, parent_launch_config_ref#18072280, _id#18072274 AS Child_Node#18085837, parent_id#18072279 AS Parent_id#18085839, Levels#18072308, tier#18072283, node_status#18072281, data_state#18072282, resolved_product_name#18072532 AS product_name#18085896, CASE WHEN (trim(regexp_replace(regexp_replace(product_name#18072286, , , 1), {2,}, , 1), None) = ) THEN null ELSE trim(regexp_replace(regexp_replace(product_name#18072286, , , 1), {2,}, , 1), None) END AS product_type#18087378, reference_code#18072284, resolved_supplier_display_name#18074074 AS SupplierName#18087383, resolved_supplier_city#18072895 AS SupplierCity#18087387, resolved_supplier_state#18073081 AS SupplierState#18087390, resolved_supplier_country#18073271 AS SupplierCountry#18087393, resolved_supplier_address#18073667 AS SupplierAddress#18087396, data_address_lat#18072300 AS SupplierLat#18088129, data_address_lng#18072301 AS SupplierLng#18088132, resolved_supplier_ref#18073869 AS Supplier_declaration_RefID#18088135, supplier_type#18072304, decl_supplier_id#18072310 AS supplier_id#18088138, decl_supplier_create_date#18072311 AS supplier_first_created_date#18088139, decl_supplier_company_id#18072312 AS supplier_declared_company#18088405, decl_preferrd_supplier_id#18072313 AS preferrd_supplier_id#18088407, decl_preferrd_supplier_name#18072314 AS preferrd_supplier_name#18088409, decl_dedup_reference_id#18072315 AS dedup_reference_id#18088411, CASE WHEN is_unknown_supplier#18073972 THEN InDirect WHEN (decl_source#18072309 = PRIMARY) THEN Direct ELSE InDirect END AS DirectIndirectSupplier#18088412, summary__direct_supplier_name#18079732 AS Direct_Supplier_Name#18088414, summary__supplier_uid#18080013 AS SupplierUID#18088415, nominated_node#18072305, CASE WHEN nominated_node#18072305 THEN Yet to complete ELSE Not applicable END AS child_network_id#18088416, CASE WHEN nominated_node#18072305 THEN Pending ELSE Not applicable END AS child_network_status#18088417, CASE WHEN isnotnull(_p_display_name#18082495) THEN _p_display_name#18082495 WHEN (_p_data_state#18082498 = Not Applicable) THEN Not Applicable WHEN (_p_node_status#18082497 = 30) THEN Supplier Unknown ELSE Not Applicable END AS ParentSupplierName#18088418, _p_city#18082499 AS ParentSupplierCity#18088419, _p_state#18082500 AS ParentSupplierState#18088421, _p_country#18082501 AS ParentSupplierCountry#18088422, _p_address#18082502 AS ParentSupplierAddress#18088423, _p_lat#18082503 AS ParentSupplierLat#18088425, _p_lng#18082504 AS ParentSupplierLng#18088427, summary__submission_status#18079205 AS submission_status#18088429, summary__is_latest#18079206 AS is_latest#18088430, false AS is_latest_submitted#18088431, summary__source#18079302 AS source#18088433, source_type#18072285, update_ts#18072306 AS node_update_ts#18088436, is_unknown_supplier#18073972, 2026-10-20 07:30:06.152599 AS update_ts#18088788]
Input [55]: [brand_context_id#18069035, company_id#18069036, launch_config_ref#18069037, revision_version#18069038, summary__config_name#18079175, summary__submission_status#18079205, summary__is_latest#18079206, summary__source#18079302, summary__NetworkID#18079731, summary__direct_supplier_name#18079732, summary__supplier_uid#18080013, _id#18072274, parent_id#18072279, parent_launch_config_ref#18072280, node_status#18072281, data_state#18072282, tier#18072283, reference_code#18072284, source_type#18072285, product_name#18072286, data_address_lat#18072300, data_address_lng#18072301, supplier_type#18072304, nominated_node#18072305, update_ts#18072306, Levels#18072308, decl_source#18072309, decl_supplier_id#18072310, decl_supplier_create_date#18072311, decl_supplier_company_id#18072312, decl_preferrd_supplier_id#18072313, decl_preferrd_supplier_name#18072314, decl_dedup_reference_id#18072315, resolved_product_name#18072532, resolved_supplier_city#18072895, resolved_supplier_state#18073081, resolved_supplier_country#18073271, resolved_supplier_address#18073667, resolved_supplier_ref#18073869, is_unknown_supplier#18073972, resolved_supplier_display_name#18074074, _p_id#18082488, _p_brand#18082490, _p_company#18082491, _p_launch#18082493, _p_revision#18082494, _p_display_name#18082495, _p_node_status#18082497, _p_data_state#18082498, _p_city#18082499, _p_state#18082500, _p_country#18082501, _p_address#18082502, _p_lat#18082503, _p_lng#18082504]
(30) AdaptiveSparkPlan
Output [52]: [NetworkID#18085833, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085834, revision_version#18069038, config_name#18085836, parent_launch_config_ref#18072280, Child_Node#18085837, Parent_id#18085839, Levels#18072308, tier#18072283, node_status#18072281, data_state#18072282, product_name#18085896, product_type#18087378, reference_code#18072284, SupplierName#18087383, SupplierCity#18087387, SupplierState#18087390, SupplierCountry#18087393, SupplierAddress#18087396, SupplierLat#18088129, SupplierLng#18088132, Supplier_declaration_RefID#18088135, supplier_type#18072304, supplier_id#18088138, supplier_first_created_date#18088139, supplier_declared_company#18088405, preferrd_supplier_id#18088407, preferrd_supplier_name#18088409, dedup_reference_id#18088411, DirectIndirectSupplier#18088412, Direct_Supplier_Name#18088414, SupplierUID#18088415, nominated_node#18072305, child_network_id#18088416, child_network_status#18088417, ParentSupplierName#18088418, ParentSupplierCity#18088419, ParentSupplierState#18088421, ParentSupplierCountry#18088422, ParentSupplierAddress#18088423, ParentSupplierLat#18088425, ParentSupplierLng#18088427, submission_status#18088429, is_latest#18088430, is_latest_submitted#18088431, source#18088433, source_type#18072285, node_update_ts#18088436, is_unknown_supplier#18073972, update_ts#18088788]
Arguments: isFinalPlan=false
(31) TableCacheQueryStage
Output [52]: [NetworkID#18085833, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085834, revision_version#18069038, config_name#18085836, parent_launch_config_ref#18072280, Child_Node#18085837, Parent_id#18085839, Levels#18072308, tier#18072283, node_status#18072281, data_state#18072282, product_name#18085896, product_type#18087378, reference_code#18072284, SupplierName#18087383, SupplierCity#18087387, SupplierState#18087390, SupplierCountry#18087393, SupplierAddress#18087396, SupplierLat#18088129, SupplierLng#18088132, Supplier_declaration_RefID#18088135, supplier_type#18072304, supplier_id#18088138, supplier_first_created_date#18088139, supplier_declared_company#18088405, preferrd_supplier_id#18088407, preferrd_supplier_name#18088409, dedup_reference_id#18088411, DirectIndirectSupplier#18088412, Direct_Supplier_Name#18088414, SupplierUID#18088415, nominated_node#18072305, child_network_id#18088416, child_network_status#18088417, ParentSupplierName#18088418, ParentSupplierCity#18088419, ParentSupplierState#18088421, ParentSupplierCountry#18088422, ParentSupplierAddress#18088423, ParentSupplierLat#18088425, ParentSupplierLng#18088427, submission_status#18088429, is_latest#18088430, is_latest_submitted#18088431, source#18088433, source_type#18072285, node_update_ts#18088436, is_unknown_supplier#18073972, update_ts#18088788]
Arguments: 0
(32) DeserializeToObject
Input [52]: [NetworkID#18085833, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085834, revision_version#18069038, config_name#18085836, parent_launch_config_ref#18072280, Child_Node#18085837, Parent_id#18085839, Levels#18072308, tier#18072283, node_status#18072281, data_state#18072282, product_name#18085896, product_type#18087378, reference_code#18072284, SupplierName#18087383, SupplierCity#18087387, SupplierState#18087390, SupplierCountry#18087393, SupplierAddress#18087396, SupplierLat#18088129, SupplierLng#18088132, Supplier_declaration_RefID#18088135, supplier_type#18072304, supplier_id#18088138, supplier_first_created_date#18088139, supplier_declared_company#18088405, preferrd_supplier_id#18088407, preferrd_supplier_name#18088409, dedup_reference_id#18088411, DirectIndirectSupplier#18088412, Direct_Supplier_Name#18088414, SupplierUID#18088415, nominated_node#18072305, child_network_id#18088416, child_network_status#18088417, ParentSupplierName#18088418, ParentSupplierCity#18088419, ParentSupplierState#18088421, ParentSupplierCountry#18088422, ParentSupplierAddress#18088423, ParentSupplierLat#18088425, ParentSupplierLng#18088427, submission_status#18088429, is_latest#18088430, is_latest_submitted#18088431, source#18088433, source_type#18072285, node_update_ts#18088436, is_unknown_supplier#18073972, update_ts#18088788]
Arguments: createexternalrow(NetworkID#18085833.toString, brand_context_id#18069035.toString, company_id#18069036.toString, launch_config_ref#18085834.toString, staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, revision_version#18069038, true, false, true), config_name#18085836.toString, parent_launch_config_ref#18072280.toString, Child_Node#18085837.toString, Parent_id#18085839.toString, staticinvoke(class java.lang.Integer, ObjectType(class java.lang.Integer), valueOf, Levels#18072308, true, false, true), staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, tier#18072283, true, false, true), staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, node_status#18072281, true, false, true), data_state#18072282.toString, product_name#18085896.toString, product_type#18087378.toString, reference_code#18072284.toString, SupplierName#18087383.toString, SupplierCity#18087387.toString, SupplierState#18087390.toString, SupplierCountry#18087393.toString, SupplierAddress#18087396.toString, SupplierLat#18088129.toString, SupplierLng#18088132.toString, Supplier_declaration_RefID#18088135.toString, ... 80 more fields), obj#18127776: org.apache.spark.sql.Row
(33) DeserializeToObject
Input [52]: [NetworkID#18085833, brand_context_id#18069035, company_id#18069036, launch_config_ref#18085834, revision_version#18069038, config_name#18085836, parent_launch_config_ref#18072280, Child_Node#18085837, Parent_id#18085839, Levels#18072308, tier#18072283, node_status#18072281, data_state#18072282, product_name#18085896, product_type#18087378, reference_code#18072284, SupplierName#18087383, SupplierCity#18087387, SupplierState#18087390, SupplierCountry#18087393, SupplierAddress#18087396, SupplierLat#18088129, SupplierLng#18088132, Supplier_declaration_RefID#18088135, supplier_type#18072304, supplier_id#18088138, supplier_first_created_date#18088139, supplier_declared_company#18088405, preferrd_supplier_id#18088407, preferrd_supplier_name#18088409, dedup_reference_id#18088411, DirectIndirectSupplier#18088412, Direct_Supplier_Name#18088414, SupplierUID#18088415, nominated_node#18072305, child_network_id#18088416, child_network_status#18088417, ParentSupplierName#18088418, ParentSupplierCity#18088419, ParentSupplierState#18088421, ParentSupplierCountry#18088422, ParentSupplierAddress#18088423, ParentSupplierLat#18088425, ParentSupplierLng#18088427, submission_status#18088429, is_latest#18088430, is_latest_submitted#18088431, source#18088433, source_type#18072285, node_update_ts#18088436, is_unknown_supplier#18073972, update_ts#18088788]
Arguments: createexternalrow(NetworkID#18085833.toString, brand_context_id#18069035.toString, company_id#18069036.toString, launch_config_ref#18085834.toString, staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, revision_version#18069038, true, false, true), config_name#18085836.toString, parent_launch_config_ref#18072280.toString, Child_Node#18085837.toString, Parent_id#18085839.toString, staticinvoke(class java.lang.Integer, ObjectType(class java.lang.Integer), valueOf, Levels#18072308, true, false, true), staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, tier#18072283, true, false, true), staticinvoke(class java.lang.Short, ObjectType(class java.lang.Short), valueOf, node_status#18072281, true, false, true), data_state#18072282.toString, product_name#18085896.toString, product_type#18087378.toString, reference_code#18072284.toString, SupplierName#18087383.toString, SupplierCity#18087387.toString, SupplierState#18087390.toString, SupplierCountry#18087393.toString, SupplierAddress#18087396.toString, SupplierLat#18088129.toString, SupplierLng#18088132.toString, Supplier_declaration_RefID#18088135.toString, ... 80 more fields), obj#18127776: org.apache.spark.sql.Row
(34) AdaptiveSparkPlan
Output [1]: [obj#18127776]
Arguments: isFinalPlan=true