SQLize Online / PHPize Online  /  SQLtest Online

/* * Get Purchasing data that purchasing supplier equal to Main_supplier * */ with main_supplier as ( select item_no , case when upper(Purchasing_data.Purchase_California_Prop_65_Org_Label_Cancer) = 'YES' then TRUE else FALSE end as california_prop65_orange_label , case when upper(Purchasing_data.Purchase_California_Prop_65_Wht_Label_Reproductive_Harm) = 'YES' then TRUE else FALSE end as california_prop65_white_label , coalesce(safe_cast(Purchasing_data.Purchase_Ship_Pack_Qty as int64), 0) as ship_pack_qty , coalesce(safe_cast(Purchasing_data.Purchase_Supplier_Lead_Time as int64), 0) as supplier_lead_time , Purchasing_data.Purchase_Unit_of_Measure as uom , Purchasing_data.Purchase_Supplier_Minimum_Order_Qty as supplier_min_order_qty , Purchasing_data.Purchase_Supplier_Stock_No as supplier_stock_no from product_dm.p360, unnest(Purchasing_data) as Purchasing_data where Purchasing_data.Purchase_supplier = Main_supplier ) /* * Get the data with most recent validity period, tied to certain sale price type * */ , map_data as ( select item_no , safe_cast(array(select Selling_prices.sales_price from unnest(p360.Selling_prices) as Selling_prices where Selling_prices.Sales_Price_type = 'MAP' and current_date() between sales_valid_from and sales_valid_until )[safe_offset(0)]as float64) as map , array(select Selling_prices.Sales_Price_Modified_Date from unnest(p360.Selling_prices) as Selling_prices where Selling_prices.Sales_Price_type = 'MAP' and current_date() between sales_valid_from and sales_valid_until )[safe_offset(0)] as map_dt , safe_cast(array(select Selling_prices.sales_price from unnest(p360.Selling_prices) as Selling_prices where Selling_prices.Sales_Price_type = 'MSRP' and current_date() between sales_valid_from and sales_valid_until )[safe_offset(0)] as float64) as msrp_price , array(select Selling_prices.Sales_Price_Modified_Date from unnest(p360.Selling_prices) as Selling_prices where Selling_prices.Sales_Price_type = 'MSRP' and current_date() between sales_valid_from and sales_valid_until )[safe_offset(0)] as msrp_dt , array(select Selling_prices.Sales_Price_Modified_Date from unnest(p360.Selling_prices) as Selling_prices where Selling_prices.Sales_Price_type = 'Zoro Price' and current_date() between sales_valid_from and sales_valid_until )[safe_offset(0)] as sales_price_modified_dt , safe_cast(array(select Purchase_prices.Purchase_Price from unnest(p360.Purchase_prices) as Purchase_prices where Purchase_prices.purchase_price_type = 'Invoice Cost' and current_date() between Purchase_prices.Purchase_Valid_from and Purchase_prices.Purchase_Valid_until -- add this logic to cover SKUs with multi-sourcing supplier and main_supplier = Purchase_Supplier )[safe_offset(0)] as float64) as cpir_invoice_cost , safe_cast(array(select Purchase_prices.Purchase_Price_Modified_Date from unnest(p360.Purchase_prices) as Purchase_prices where Purchase_prices.purchase_price_type = 'Invoice Cost' and current_date() between Purchase_prices.Purchase_Valid_from and Purchase_prices.Purchase_Valid_until and main_supplier = Purchase_Supplier )[safe_offset(0)] as datetime) as cpir_invoice_cost_modified_dt , safe_cast(array(select Selling_prices.Sales_Price from unnest(p360.Selling_prices) as Selling_prices where Selling_prices.Sales_Price_type = 'Open Web' and current_date() between Selling_prices.sales_valid_from and Selling_prices.sales_valid_until )[safe_offset(0)] as float64) as gis_web_price # The sales price should only sourced from the effective_price node. The else condition # is for backward compatibility for SKUs has not yet been updated with effective price. , case when safe_cast(ep.sales_price as numeric) > 0 then cast(ep.sales_price as numeric) else safe_cast(array(select Selling_prices.Sales_Price from unnest(p360.Selling_prices) as Selling_prices where Selling_prices.Sales_Price_type = 'Zoro Price' and current_date() between sales_valid_from and sales_valid_until )[safe_offset(0)] as numeric) end as price , ep.price_type_comment , ep.promotion_reason_type , safe_cast(array(select Selling_prices.Sales_Price from unnest(p360.Selling_prices) as Selling_prices where Selling_prices.Sales_Price_type = 'Promotion Sell Price' and current_date() between Selling_prices.sales_valid_from and Selling_prices.sales_valid_until )[safe_offset(0)] as numeric) as promotion_sell_price , case when length(ifnull(ep.price_type_comment, '')) > 0 then ep.price_type_comment else p360.price_comment end as final_price_comment , safe_cast(array(select Selling_prices.Sales_Price from unnest(p360.Selling_prices) as Selling_prices where Selling_prices.Sales_Price_type = 'Zoro Price' and current_date() between sales_valid_from and sales_valid_until )[safe_offset(0)] as numeric) as zoro_price from product_dm.p360 left join unnest(Effective_price) as ep ) /* * Get pricer_first_effective_dt which is the sales_valid_from for the * most recent sales price modified date for sales price type 'Open Web' */ , pricer_date as ( select item_no , safe_cast(selling_prices.sales_valid_from as date) as pricer_first_effective_dt , row_number() over (partition by item_no order by Selling_prices.sales_price_modified_date desc) as r from product_dm.p360, unnest(Selling_prices ) as Selling_prices where Selling_prices.sales_price_type = 'Open Web' ) , first_pricer_date as ( select * except(r) from pricer_date where r = 1 ) , zoro_level as ( select zorono , array(select pcp.code from unnest(primarycategorypaths) as pcp) as zoro_level_codes , array(select pcp.name from unnest(primarycategorypaths) as pcp) as zoro_level_names from product_dm.product_cache ) , cpir_level as ( select item_no , array(select cpir.code from unnest(Grainger_Category) as cpir) as cpir_level_codes , array(select cpir.name from unnest(Grainger_Category) as cpir) as cpir_level_names from product_dm.p360 ), /* Deriving sales_price change history from zdp_dm.d_pricing_history*/ price_history AS ( SELECT product_key, MAX(CASE WHEN rn = 1 THEN start_dt END) AS current_price_change_date, MAX(CASE WHEN rn = 2 THEN sales_price END) AS last_price, MAX(CASE WHEN rn = 2 THEN start_dt END) AS last_price_change_date, MAX(CASE WHEN rn = 3 THEN sales_price END) AS second_last_price, MAX(CASE WHEN rn = 3 THEN start_dt END) AS second_last_price_change_date FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY product_key ORDER BY start_dt DESC) AS rn FROM ( select product_key, sales_price, start_dt, LEAD(sales_price) over (partition by product_key order by start_dt desc) as price_in_previous_row from zdp_dm.d_pricing_history) where sales_price <> price_in_previous_row or price_in_previous_row is null) GROUP BY product_key ) -- , only_from_p360 as ( select p360.item_no as product_key , case when p360.circle_e_indicator = 'Yes' then 'Y' when p360.circle_e_indicator = 'No' then 'N' else p360.circle_e_indicator end as circle_e_indicator , pc.countryOfOrigin as country_of_origin , case when p360.CPSC_Certificate_Flag = 'Yes' then 'Y' when p360.CPSC_Certificate_Flag = 'No' then 'N' else p360.CPSC_Certificate_Flag end as cpsc_certificate_flag , it.CREATED as created_dt , p360.eccn , p360.eccn_suffix , p360.for_export_flag , p360.brand_name as grainger_brand_name , safe_cast(p360.brand_number as int64) as grainger_brand_number , p360.harmonization_code , p360.hazardous_material_indicator , case when p360.is_discontinued is null then false else p360.is_discontinued end as is_discontinued , case when p360.is_ltl is null then false else p360.is_ltl end as is_ltl , case when p360.is_motor_compliant is null then false else p360.is_motor_compliant end as is_motor_compliant , case when p360.is_public is null then false else p360.is_public end as is_public , case when p360.is_restricted is null then false else p360.is_restricted end as is_restricted , CAST(ROUND(it.item_id, 0) AS INT64) AS line_item_key , p360.low_lead_compliance_level , p360.manufacturer_name , safe_cast(p360.manufacturer_number as INT64) as manufacturer_number , p360.Manufacturer_Model_Number as mfr_model_no , upper(p360.msds_indicator) = 'YES' as msds_indicator , safe_cast(p360.not_for_export_reason_code as INT64) as not_for_export_reason_code , p360.Package_quantity as package_qty , upper(p360.Does_this_product_contain_a_refrigerant) = 'YES' as refrigerant_flag , p360.relationship_manager_code , p360.restricted_sale_item_code , p360.restricted_states , p360.sales_status , safe_cast(p360.sell_pack_height as NUMERIC) as sell_pack_height , safe_cast(p360.sell_pack_length as NUMERIC) as sell_pack_length , p360.sell_pack_qty , safe_cast(p360.sell_pack_weight as NUMERIC) as sell_pack_weight , safe_cast(p360.sell_pack_width as NUMERIC) as sell_pack_width , safe_cast(p360.ship_pack_height as NUMERIC) as ship_pack_height , safe_cast(p360.ship_pack_length as NUMERIC) as ship_pack_length , safe_cast(p360.ship_pack_weight as NUMERIC) as ship_pack_weight , safe_cast(p360.ship_pack_width as NUMERIC) as ship_pack_width , case when Main_supplier = 'Grainger' then p360.Ship_Quantity else array(select Purchasing_data.Purchase_Supplier_Minimum_Order_Qty from unnest(Purchasing_data) as Purchasing_data where Purchase_supplier != 'Grainger')[safe_offset(0)] end as ship_qty , p360.shop_frequency , p360.variant_attributes , p360.variant_label , CAST(ROUND(it.vendor_id, 0) AS INT64) AS vendor_key , v.name AS vendor_number , v.full_name AS vendor_name , coalesce(safe_cast(Zoro_Min_Order_Quantity_MOQ as int64), 0) as Zoro_Min_Order_Quantity_MOQ , coalesce(safe_cast(Zoro_Pricing_Min_Order_Quantity_MOQ as int64), 0) as Zoro_Pricing_Min_Order_Quantity_MOQ , p360.item_no as zoro_no , p360.relationship_manager_code in ('L15', 'L60') AS zoro_only_sku , pd.california_prop65_orange_label , pd.california_prop65_white_label , safe_cast(md.cpir_invoice_cost as NUMERIC) as cpir_invoice_cost , p360.Parent_Number as gis_parent_no , safe_cast(md.gis_web_price as NUMERIC) as gis_web_price , safe_cast(array_to_string(array(select safe_cast(max(Selling_prices.Sales_valid_from) as string) from unnest(p360.Selling_prices) as Selling_prices where Selling_prices.Sales_Price_type = 'Open Web'), '') as date) as gis_web_price_effective_dt , safe_cast(p360.Last_change_in_version as TIMESTAMP)as last_modified_dt , array_to_string(array(select Selling_prices.Sales_MAP_Type from unnest(p360.Selling_prices) as selling_prices where Selling_prices.Sales_MAP_Type != '' ), '') as map_type , p360.pmcode as pm_code , p360.price_comment , p360.hygiene as price_hygiene , p360.source_and_competitor_price as price_tactic_name , case when md.sales_price_modified_dt != '' then cast(PARSE_DATETIME('%m/%d/%Y %I:%M %p', md.sales_price_modified_dt) as TIMESTAMP) else null end as sales_price_modified_dt , pd.ship_pack_qty , array_to_string(array((select extended_logistics.Extended_Supplier_Product_Title from unnest(p360.extended_logistics) as extended_logistics)), '') as short_description , safe_cast(pd.supplier_min_order_qty as int64) as supplier_min_order_qty ,case when p360.Main_supplier = 'Grainger' then p360.Grainger_Supplier_Name else Main_supplier end as supplier_name , pc.supplierno as supplier_no , pd.supplier_stock_no , subBrand_trade_name as trade_name , case when p360.upc_code != '' then p360.upc_code else p360.grainger_upc_code end as upc_code , coalesce(ARRAY_TO_STRING(ARRAY( SELECT case when not coalesce(Extended_Logistics.Extended_zoro_com_Merchandising_Title, '') = '' then Extended_Logistics.Extended_zoro_com_Merchandising_Title else case when not coalesce(language.Lang_Zoro_com_Formula_Title, '') = '' then language.Lang_Zoro_com_Formula_Title else case when not coalesce(language.Lang_Zoro_com_Product_Title, '') = '' then language.Lang_Zoro_com_Product_Title else Extended_Logistics.Extended_Supplier_Product_Title end end end FROM UNNEST(p360.Extended_Logistics) Extended_Logistics , unnest(p360.language) as language where Extended_Logistics.Extended_Supplier_customer = Main_supplier ORDER BY 1), ''), '') as web_title , pc.heroImage as zoro_image_name , array_to_string(array(select Language.Lang_Comment from unnest(Language) as Language), '') as comment , safe_cast(cp.cpir_level_codes[safe_offset(0)] as int64) as cpir_level_one_id , cp.cpir_level_names[safe_offset(0)] as cpir_level_one_name , safe_cast(cp.cpir_level_codes[safe_offset(1)] as int64) as cpir_level_two_id , cp.cpir_level_names[safe_offset(1)] as cpir_level_two_name , safe_cast(cp.cpir_level_codes[safe_offset(2)] as int64) as cpir_level_three_id , cp.cpir_level_names[safe_offset(2)] as cpir_level_three_name , pc.energyGuideLabel as energy_guide_label , array_to_string(array(select Asset_Name from unnest(p360.File_Attachments) as file_attachments where asset_type = 'Primary Image'), '') as grainger_image_name , array_to_string(array(select Supplier_Relationship.supplier_item_number from unnest(Supplier_Relationship) as Supplier_Relationship where Supplier_Relationship.supplier = 'Grainger'), '') as grainger_no , array_to_string(array(select Language.lang_product_description from unnest(Language) as Language), '') as long_description , safe_cast(md.map as NUMERIC) as map , case when md.map_dt != '' then cast(PARSE_DATETIME('%m/%d/%Y %I:%M %p', md.map_dt) as date) else null end as map_dt , safe_cast(md.msrp_price as NUMERIC) as msrp_price , case when md.msrp_dt != '' then cast(PARSE_DATETIME('%m/%d/%Y %I:%M %p', md.msrp_dt) as date) else null end as msrp_dt , fpd.pricer_first_effective_dt , pd.supplier_lead_time , md.price sales_price , case when lower(pd.uom) = 'each' then 'EA' when lower(pd.uom) = 'pack' then 'PK' when lower(pd.uom) = 'pair' then 'PR' else '' end as uom , p360.brand_name as zoro_brand_name , zl.zoro_level_codes[safe_offset(0)] as zoro_level_zero_code , zl.zoro_level_names[safe_offset(0)] as zoro_level_zero_name , zl.zoro_level_codes[safe_offset(1)] as zoro_level_one_code , zl.zoro_level_names[safe_offset(1)] as zoro_level_one_name , zl.zoro_level_codes[safe_offset(2)] as zoro_level_two_code , zl.zoro_level_names[safe_offset(2)] as zoro_level_two_name , zl.zoro_level_codes[safe_offset(3)] as zoro_level_three_code , zl.zoro_level_names[safe_offset(3)] as zoro_level_three_name , zl.zoro_level_codes[safe_offset(4)] as zoro_level_four_code , zl.zoro_level_names[safe_offset(4)] as zoro_level_four_name , zl.zoro_level_codes[safe_offset(5)] as zoro_level_five_code , zl.zoro_level_names[safe_offset(5)] as zoro_level_five_name , zl.zoro_level_codes[safe_offset(6)] as zoro_level_six_code , zl.zoro_level_names[safe_offset(6)] as zoro_level_six_name , zl.zoro_level_codes[safe_offset(7)] as zoro_level_seven_code , zl.zoro_level_names[safe_offset(7)] as zoro_level_seven_name , zl.zoro_level_codes[safe_offset(array_length(zoro_level_codes) -1)] as zoro_level_end_node_code , zl.zoro_level_names[safe_offset(array_length(zoro_level_names) -1)] as zoro_level_end_node_name , (coalesce(p360.do_not_export, 'No') = 'Yes') as do_not_sell , coalesce((select attributes.attribute_name from unnest(p360.attributes) as attributes where attributes.attribute_name = 'Closeout') = 'Closeout', false) as closeout , p360.sku_type , array_to_string(array(select Lang_Zoro_com_Formula_Title from unnest(Language) as Language), '') as zoro_formula_title , array_to_string(array(select Lang_Zoro_com_product_Title from unnest(Language) as Language), '') as zoro_product_title , array_to_string(array(select Extended_Zoro_com_Merchandising_Title from unnest(Extended_Logistics) as Extended_Logistics where Extended_Supplier_customer = Main_supplier), '') as zoro_merchandising_title , array_to_string(array(select Extended_Supplier_Product_Title from unnest(Extended_Logistics) as Extended_Logistics where Extended_Supplier_customer = Main_supplier), '') as supplier_product_title , '' as default_title_tag , '' as default_meta_description , '' as custom_title_tag , '' as custom_meta_description , p360.freightTierCost , case when safe_cast(p360.Zoro_Lead_Time as int64) is null and pd.supplier_lead_time = 0 and safe_cast(p360.Additional_Lead_Time as int64) is null then 30 when safe_cast(p360.Zoro_Lead_Time as int64) is not null then coalesce(safe_cast(p360.Zoro_Lead_Time as int64), 0) + coalesce(safe_cast(p360.Additional_Lead_Time as int64), 0) when safe_cast(p360.Zoro_Lead_Time as int64) is null then coalesce(safe_cast(pd.supplier_lead_time as int64), 0) + coalesce(safe_cast(p360.Additional_Lead_Time as int64), 0) end as customer_lead_time , md.cpir_invoice_cost_modified_dt , pc.erpId , md.price_type_comment , md.promotion_reason_type , md.promotion_sell_price , md.final_price_comment , md.zoro_price from product_dm.p360 p360 left join product_dm.product_cache as pc on p360.item_no = pc.zorono left join netsuite_ods.items as it on p360.item_no = it.item_extid left join netsuite_ods.vendors v ON (it.vendor_id = v.vendor_id) left join main_supplier as pd on p360.item_no = pd.item_no left join first_pricer_date as fpd on p360.item_no = fpd.item_no left join zoro_level zl on p360.item_no = zl.zorono left join map_data md on p360.item_no = md.item_no left join cpir_level as cp on p360.item_no = cp.item_no where p360.item_no is not null and pc.zorono is null ) /* Using PC as driving table to have d_product populated for matching SKUs between PC and P360 */ , att_from_pc as ( select a.zorono as product_key , comment , countryOfOrigin as country_of_origin , CPSCCertificateFlag as cpsc_certificate_flag , energyGuideLabel as energy_guide_label , graingerBrandName as grainger_brand_name , graingerBrandNumber as grainger_brand_number , safe_cast(a.map as numeric) as map , uom , title as web_title , californiaProp65OrangeLabel as california_prop65_orange_label , californiaProp65WhiteLabel as california_prop65_white_label , circleEIndicator as circle_e_indicator , eccn , ECCNSuffix as eccn_suffix , forExportFlag as for_export_flag , harmonizationCode as harmonization_code , hazardousMaterialIndicator as hazardous_material_indicator , isDiscontinued as is_discontinued , isLTL as is_ltl , isMotorCompliant as is_motor_compliant , isPublic as is_public , isRestricted as is_restricted , lightingFactLabel as lighting_fact_label , longDescription as long_description , lowLeadComplianceLevel as low_lead_compliance_level , manufacturerName as manufacturer_name , safe_cast(manufacturerNumber as INT64) as manufacturer_number , notForExportReasonCode as not_for_export_reason_code , cast(packageQty as int64) as package_qty , refrigerantFlag as refrigerant_flag , restrictedSaleItemCode as restricted_sale_item_code , restrictedStates as restricted_states -- pull this from p360 only , md.price as sales_price , salesStatus as sales_status , safe_cast(sellPackHeight as NUMERIC) as sell_pack_height , safe_cast(sellPackLength as NUMERIC) as sell_pack_length , sellPackQty as sell_pack_qty , safe_cast(sellPackWeight as NUMERIC) as sell_pack_weight , safe_cast(sellPackWidth as NUMERIC) as sell_pack_width , safe_cast(shipPackHeight as NUMERIC) as ship_pack_height , safe_cast(shipPackLength as NUMERIC) as ship_pack_length , shipPackQty as ship_pack_qty , safe_cast(shipPackWeight as NUMERIC) as ship_pack_weight , safe_cast(shipPackWidth as NUMERIC) as ship_pack_width , shipQty as ship_qty , shortDescription as short_description , supplierMinOrderQty as supplier_min_order_qty , supplierName as supplier_name , supplierNo as supplier_no , supplierStockNo as supplier_stock_no , tradeName as trade_name , UPCCode as upc_code , array_to_string(array(select variantattributes from unnest(variantattributes) as variantattributes), '|') as variant_attributes , variantLabel as variant_label , a.brand as zoro_brand_name , heroImage as zoro_image_name , zoroMinOrderQty as zoro_min_order_qty , a.zorono as zoro_no , zl.zoro_level_codes[safe_offset(0)] as zoro_level_zero_code , zl.zoro_level_names[safe_offset(0)] as zoro_level_zero_name , zl.zoro_level_codes[safe_offset(1)] as zoro_level_one_code , zl.zoro_level_names[safe_offset(1)] as zoro_level_one_name , zl.zoro_level_codes[safe_offset(2)] as zoro_level_two_code , zl.zoro_level_names[safe_offset(2)] as zoro_level_two_name , zl.zoro_level_codes[safe_offset(3)] as zoro_level_three_code , zl.zoro_level_names[safe_offset(3)] as zoro_level_three_name , zl.zoro_level_codes[safe_offset(4)] as zoro_level_four_code , zl.zoro_level_names[safe_offset(4)] as zoro_level_four_name , zl.zoro_level_codes[safe_offset(5)] as zoro_level_five_code , zl.zoro_level_names[safe_offset(5)] as zoro_level_five_name , zl.zoro_level_codes[safe_offset(6)] as zoro_level_six_code , zl.zoro_level_names[safe_offset(6)] as zoro_level_six_name , zl.zoro_level_codes[safe_offset(7)] as zoro_level_seven_code , zl.zoro_level_names[safe_offset(7)] as zoro_level_seven_name , zl.zoro_level_codes[safe_offset(array_length(zoro_level_codes) -1)] as zoro_level_end_node_code , zl.zoro_level_names[safe_offset(array_length(zoro_level_names) -1)] as zoro_level_end_node_name , a.zorono , it.CREATED as created_dt , CAST(ROUND(it.vendor_id, 0) AS INT64) AS vendor_key , v.name AS vendor_number , v.full_name AS vendor_name , CAST(ROUND(it.item_id, 0) AS INT64) AS line_item_key , concat(coalesce(INITCAP(a.brand), ' ') , ' ', coalesce(a.GISMnfModelNo, ' ') , ' $', coalesce(cast(round(a.price, 2) as string), ' '), ' ' , coalesce(a.title, ' '), ' | Zoro.com') as default_title_tag , concat('Order ', INITCAP(coalesce(a.brand, ' ')), ' ', coalesce(a.title, ' '), ', ' , coalesce(a.GISMnfModelNo, ' ') , ' at Zoro.com. Great prices & free shipping on orders over $50 when you sign in or sign up for an account.' ) as default_meta_description , a.SEOTitleTag as custom_title_tag , a.SEOMetaDescription as custom_meta_description , a.freightTierCost , case when a.zoroleadtime is null and a.leadTime is null and a.additionalleadtime is null then 30 when a.zoroleadtime is not null then coalesce(a.zoroleadtime, 0) + coalesce(a.additionalleadtime, 0) when a.zoroleadtime is null then coalesce(a.leadTime, 0) + coalesce(a.additionalleadtime, 0) end as customer_lead_time , a.erpId , md.price_type_comment , md.promotion_reason_type , md.promotion_sell_price , md.final_price_comment , md.zoro_price from product_dm.product_cache a join zoro_level zl on a.zorono = zl.zorono left join map_data md on a.zorono = md.item_no left join netsuite_ods.items as it on a.zorono = it.item_extid left join netsuite_ods.vendors v ON (it.vendor_id = v.vendor_id) ) , att_from_p360 as ( select p360.item_no , safe_cast(p360.Last_change_in_version as TIMESTAMP)as last_modified_dt , safe_cast(md.cpir_invoice_cost as NUMERIC) as cpir_invoice_cost , safe_cast(cp.cpir_level_codes[safe_offset(0)] as int64) as cpir_level_one_id , cp.cpir_level_names[safe_offset(0)] as cpir_level_one_name , safe_cast(cp.cpir_level_codes[safe_offset(1)] as int64) as cpir_level_two_id , cp.cpir_level_names[safe_offset(1)] as cpir_level_two_name , safe_cast(cp.cpir_level_codes[safe_offset(2)] as int64) as cpir_level_three_id , cp.cpir_level_names[safe_offset(2)] as cpir_level_three_name , p360.Parent_Number as gis_parent_no , safe_cast(md.gis_web_price as NUMERIC) as gis_web_price , safe_cast(array_to_string(array(select safe_cast(max(Selling_prices.Sales_valid_from) as string) from unnest(p360.Selling_prices) as Selling_prices where Selling_prices.Sales_Price_type = 'Open Web'), '') as date) as gis_web_price_effective_dt , array_to_string(array(select Asset_Name from unnest(p360.File_Attachments) as file_attachments where asset_type = 'Primary Image'), '') as grainger_image_name , array_to_string(array(select Supplier_Relationship.supplier_item_number from unnest(Supplier_Relationship) as Supplier_Relationship where Supplier_Relationship.supplier = 'Grainger'), '') as grainger_no , case when md.map_dt != '' then cast(PARSE_DATETIME('%m/%d/%Y %I:%M %p', md.map_dt) as date) else null end as map_dt , array_to_string(array(select Selling_prices.Sales_MAP_Type from unnest(p360.Selling_prices) as selling_prices where Selling_prices.Sales_MAP_Type != '' ), '') as map_type , p360.Manufacturer_Model_Number as mfr_model_no , upper(p360.msds_indicator) = 'YES' as msds_indicator , p360.pmcode as pm_code , p360.price_comment , p360.hygiene as price_hygiene , p360.source_and_competitor_price as price_tactic_name , case when md.sales_price_modified_dt != '' then cast(PARSE_DATETIME('%m/%d/%Y %I:%M %p', md.sales_price_modified_dt) as TIMESTAMP) else null end as sales_price_modified_dt , p360.relationship_manager_code , p360.shop_frequency , pd.supplier_lead_time , p360.relationship_manager_code in ('L15', 'L60') AS zoro_only_sku , safe_cast(md.msrp_price as NUMERIC) as msrp_price , case when md.msrp_dt != '' then cast(PARSE_DATETIME('%m/%d/%Y %I:%M %p', md.msrp_dt) as date) else null end as msrp_dt , fpd.pricer_first_effective_dt , coalesce(safe_cast(Zoro_Min_Order_Quantity_MOQ as int64), 0) as Zoro_Min_Order_Quantity_MOQ , coalesce(safe_cast(Zoro_Pricing_Min_Order_Quantity_MOQ as int64), 0) as Zoro_Pricing_Min_Order_Quantity_MOQ , p360.sku_type , array_to_string(array(select Lang_Zoro_com_Formula_Title from unnest(Language) as Language), '') as zoro_formula_title , array_to_string(array(select Lang_Zoro_com_product_Title from unnest(Language) as Language), '') as zoro_product_title , array_to_string(array(select Extended_Zoro_com_Merchandising_Title from unnest(Extended_Logistics) as Extended_Logistics where Extended_Supplier_customer = Main_supplier), '') as zoro_merchandising_title , array_to_string(array(select Extended_Supplier_Product_Title from unnest(Extended_Logistics) as Extended_Logistics where Extended_Supplier_customer = Main_supplier), '') as supplier_product_title , (coalesce(p360.do_not_export, 'No') = 'Yes') as do_not_sell , coalesce((select attributes.attribute_name from unnest(p360.attributes) as attributes where attributes.attribute_name = 'Closeout') = 'Closeout', false) as closeout , case when safe_cast(p360.Zoro_Lead_Time as int64) is null and pd.supplier_lead_time = 0 and safe_cast(p360.Additional_Lead_Time as int64) is null then 30 when safe_cast(p360.Zoro_Lead_Time as int64) is not null then coalesce(safe_cast(p360.Zoro_Lead_Time as int64), 0) + coalesce(safe_cast(p360.Additional_Lead_Time as int64), 0) when safe_cast(p360.Zoro_Lead_Time as int64) is null then coalesce(safe_cast(pd.supplier_lead_time as int64), 0) + coalesce(safe_cast(p360.Additional_Lead_Time as int64), 0) end as customer_lead_time , md.cpir_invoice_cost_modified_dt from product_dm.p360 p360 left join map_data md on p360.item_no = md.item_no left join main_supplier as pd on p360.item_no = pd.item_no left join first_pricer_date as fpd on p360.item_no = fpd.item_no left join cpir_level cp on p360.item_no = cp.item_no ) -- , combine_pc_p360 as ( select a.* except(customer_lead_time), b.* from att_from_pc a left join att_from_p360 b on a.zorono = b.item_no ) -- , new_product as ( select product_key, vendor_key, line_item_key, zoro_no, grainger_no , mfr_model_no, supplier_stock_no, upc_code, short_description, web_title , long_description, zoro_brand_name, grainger_brand_name, grainger_brand_number, supplier_name , supplier_no, vendor_number, vendor_name, manufacturer_name, manufacturer_number , package_qty, uom, sales_status, pm_code, relationship_manager_code , grainger_image_name, zoro_image_name, gis_parent_no, trade_name, is_discontinued , is_public, is_restricted, is_ltl, zoro_level_one_name, zoro_level_one_code , zoro_level_two_name, zoro_level_two_code, zoro_level_three_name, zoro_level_three_code, cpir_level_three_name , cpir_level_three_id, supplier_lead_time, supplier_min_order_qty, ship_qty, sell_pack_qty , sell_pack_height, sell_pack_length, sell_pack_width, sell_pack_weight, ship_pack_qty , ship_pack_height, ship_pack_length, ship_pack_width, ship_pack_weight, sales_price , sales_price_modified_dt, price_comment, price_tactic_name, price_hygiene, shop_frequency , map, map_dt, map_type, gis_web_price, gis_web_price_effective_dt , cpir_invoice_cost, pricer_first_effective_dt, country_of_origin, for_export_flag, not_for_export_reason_code , eccn, eccn_suffix, harmonization_code, california_prop65_orange_label, california_prop65_white_label , hazardous_material_indicator, msds_indicator, restricted_states, restricted_sale_item_code, low_lead_compliance_level , circle_e_indicator, energy_guide_label, cpsc_certificate_flag, refrigerant_flag, is_motor_compliant , variant_label, variant_attributes, comment, last_modified_dt, created_dt , zoro_only_sku, msrp_dt, msrp_price, do_not_sell, closeout , sku_type, zoro_formula_title, zoro_product_title, zoro_merchandising_title, supplier_product_title , Zoro_Min_Order_Quantity_MOQ, Zoro_Pricing_Min_Order_Quantity_MOQ , default_title_tag, default_meta_description, custom_title_tag, custom_meta_description , cpir_level_one_id, cpir_level_one_name, cpir_level_two_id, cpir_level_two_name , zoro_level_zero_name, zoro_level_zero_code, zoro_level_four_name, zoro_level_four_code , zoro_level_five_name, zoro_level_five_code, zoro_level_six_name, zoro_level_six_code , zoro_level_seven_name, zoro_level_seven_code , freightTierCost , zoro_level_end_node_code, zoro_level_end_node_name , customer_lead_time, cpir_invoice_cost_modified_dt, erpId , price_type_comment, promotion_reason_type, promotion_sell_price, final_price_comment , zoro_price from only_from_p360 union all select product_key, vendor_key, line_item_key, zoro_no, grainger_no , mfr_model_no, supplier_stock_no, upc_code, short_description, web_title , long_description, zoro_brand_name, grainger_brand_name, grainger_brand_number, supplier_name , supplier_no, vendor_number, vendor_name, manufacturer_name, manufacturer_number , package_qty, uom, sales_status, pm_code, relationship_manager_code , grainger_image_name, zoro_image_name, gis_parent_no, trade_name, is_discontinued , is_public, is_restricted, is_ltl, zoro_level_one_name, zoro_level_one_code , zoro_level_two_name, zoro_level_two_code, zoro_level_three_name, zoro_level_three_code, cpir_level_three_name , cpir_level_three_id, supplier_lead_time, supplier_min_order_qty, ship_qty, sell_pack_qty , sell_pack_height, sell_pack_length, sell_pack_width, sell_pack_weight, ship_pack_qty , ship_pack_height, ship_pack_length, ship_pack_width, ship_pack_weight, sales_price , sales_price_modified_dt, price_comment, price_tactic_name, price_hygiene, shop_frequency , map, map_dt, map_type, gis_web_price, gis_web_price_effective_dt , cpir_invoice_cost, pricer_first_effective_dt, country_of_origin , for_export_flag, safe_cast(not_for_export_reason_code as int64) as not_for_export_reason_code , eccn, eccn_suffix, harmonization_code, california_prop65_orange_label, california_prop65_white_label , hazardous_material_indicator, msds_indicator, restricted_states, restricted_sale_item_code, low_lead_compliance_level , circle_e_indicator, energy_guide_label, cpsc_certificate_flag, upper(refrigerant_flag) = 'Y', is_motor_compliant , variant_label, variant_attributes, comment, last_modified_dt, created_dt , zoro_only_sku, msrp_dt, msrp_price, do_not_sell, closeout , sku_type, zoro_formula_title, zoro_product_title, zoro_merchandising_title, supplier_product_title , Zoro_Min_Order_Quantity_MOQ, Zoro_Pricing_Min_Order_Quantity_MOQ , default_title_tag, default_meta_description, custom_title_tag, custom_meta_description , cpir_level_one_id, cpir_level_one_name, cpir_level_two_id, cpir_level_two_name , zoro_level_zero_name, zoro_level_zero_code, zoro_level_four_name, zoro_level_four_code , zoro_level_five_name, zoro_level_five_code, zoro_level_six_name, zoro_level_six_code , zoro_level_seven_name, zoro_level_seven_code , freightTierCost , zoro_level_end_node_code, zoro_level_end_node_name , customer_lead_time, cpir_invoice_cost_modified_dt, erpId , price_type_comment, promotion_reason_type, promotion_sell_price, final_price_comment , zoro_price from combine_pc_p360 ) -- put this part to after combine everything , add_zoro_min_order_qty as ( select p.*, ph.* except(product_key, current_price_change_date), date_diff(current_date, extract(date from sales_price_modified_dt), DAY) AS days_at_current_price, SAFE_SUBTRACT(sales_price, last_price) AS last_price_change_amount, date_diff(extract(date from sales_price_modified_dt), last_price_change_date, DAY) AS days_at_previous_price, SAFE_SUBTRACT(last_price, second_last_price) AS second_last_price_change_amount, greatest(Zoro_Min_Order_Quantity_MOQ, Zoro_Pricing_Min_Order_Quantity_MOQ, coalesce(ship_qty, 0)) as zoro_min_order_qty from new_product p left join price_history ph on p.product_key = ph.product_key ) -- , keep_order as ( select product_key , vendor_key , line_item_key , zoro_no , if(grainger_no = '', null, grainger_no) as grainger_no , if(mfr_model_no = '', null, mfr_model_no) as mfr_model_no , if(supplier_stock_no = '', null, supplier_stock_no) as supplier_stock_no , if(upc_code = '', null, upc_code) as upc_code , if(short_description = '', null, short_description) as short_description , if(web_title = '', null, web_title) as web_title , if(long_description = '', null, long_description) as long_description , if(zoro_brand_name = '', null, zoro_brand_name) as zoro_brand_name , if(grainger_brand_name = '', null, grainger_brand_name) as grainger_brand_name , grainger_brand_number , if(supplier_name = '', null, supplier_name) as supplier_name , supplier_no , if(vendor_number = '', null, vendor_number) as vendor_number , if(vendor_name = '', null, vendor_name) as vendor_name , if(manufacturer_name = '', null, manufacturer_name) as manufacturer_name , manufacturer_number , package_qty , if(uom = '', null, uom) as uom , if(sales_status = '', null, sales_status) as sales_status , if(pm_code = '', null, pm_code) as pm_code , if(relationship_manager_code = '', null, relationship_manager_code) as relationship_manager_code , if(grainger_image_name = '', null, grainger_image_name) as grainger_image_name , if(zoro_image_name = '', null, zoro_image_name) as zoro_image_name , if(gis_parent_no = '', null, gis_parent_no) as gis_parent_no , if(trade_name = '', null, trade_name) as trade_name , is_discontinued , is_public , is_restricted , is_ltl , if(zoro_level_zero_name = '', null, zoro_level_zero_name) as zoro_level_zero_name , zoro_level_zero_code , if(zoro_level_one_name = '', null, zoro_level_one_name) as zoro_level_one_name , zoro_level_one_code , if(zoro_level_two_name = '', null, zoro_level_two_name) as zoro_level_two_name , zoro_level_two_code , if(zoro_level_three_name = '', null, zoro_level_three_name) as zoro_level_three_name , zoro_level_three_code , if(zoro_level_four_name = '', null, zoro_level_four_name) as zoro_level_four_name , zoro_level_four_code , if(zoro_level_five_name = '', null, zoro_level_five_name) as zoro_level_five_name , zoro_level_five_code , if(zoro_level_six_name = '', null, zoro_level_six_name) as zoro_level_six_name , zoro_level_six_code , if(zoro_level_seven_name = '', null, zoro_level_seven_name) as zoro_level_seven_name , zoro_level_seven_code , zoro_level_end_node_code, zoro_level_end_node_name , if(cpir_level_one_name = '', null, cpir_level_one_name) as cpir_level_one_name , cpir_level_one_id , if(cpir_level_two_name = '', null, cpir_level_two_name) as cpir_level_two_name , cpir_level_two_id , if(cpir_level_three_name = '', null, cpir_level_three_name) as cpir_level_three_name , cpir_level_three_id , supplier_lead_time , supplier_min_order_qty , zoro_min_order_qty , ship_qty , sell_pack_qty , sell_pack_height , sell_pack_length , sell_pack_width , sell_pack_weight , ship_pack_qty , ship_pack_height , ship_pack_length , ship_pack_width , ship_pack_weight , sales_price , sales_price_modified_dt , if(price_comment = '', null, price_comment) as price_comment , if(price_tactic_name = '', null, price_tactic_name) as price_tactic_name , if(price_hygiene = '', null, price_hygiene) as price_hygiene , if(shop_frequency = '', null, shop_frequency) as shop_frequency , map , map_dt , if(map_type = '', null, map_type) as map_type , gis_web_price , gis_web_price_effective_dt , cpir_invoice_cost , pricer_first_effective_dt , if(country_of_origin = '', null, country_of_origin) as country_of_origin , if(for_export_flag = '', null, for_export_flag) as for_export_flag , not_for_export_reason_code , if(eccn = '', null, eccn) as eccn , if(eccn_suffix = '', null, eccn_suffix) as eccn_suffix , if(harmonization_code = '', null, harmonization_code) as harmonization_code , california_prop65_orange_label , california_prop65_white_label , if(hazardous_material_indicator = '', null, hazardous_material_indicator) as hazardous_material_indicator , msds_indicator , if(restricted_states = '', null, restricted_states) as restricted_states , if(restricted_sale_item_code = '', null, restricted_sale_item_code) as restricted_sale_item_code , if(low_lead_compliance_level = '', null, low_lead_compliance_level) as low_lead_compliance_level , if(circle_e_indicator = '', null, circle_e_indicator) as circle_e_indicator , if(energy_guide_label = '', null, energy_guide_label) as energy_guide_label , if(cpsc_certificate_flag = '', null, cpsc_certificate_flag) as cpsc_certificate_flag , refrigerant_flag , is_motor_compliant , if(variant_label = '', null, variant_label) as variant_label , if(variant_attributes = '', null, variant_attributes) as variant_attributes , if(comment = '', null, comment) as comment , last_modified_dt , created_dt , zoro_only_sku , last_price_change_date , last_price , second_last_price_change_date , second_last_price , days_at_current_price , last_price_change_amount , days_at_previous_price , second_last_price_change_amount , msrp_dt , msrp_price , do_not_sell , closeout , if(sku_type = '', null, sku_type) as sku_type , if(zoro_formula_title = '', null, zoro_formula_title) as zoro_formula_title , if(zoro_product_Title = '', null, zoro_product_title) as zoro_product_title , if(zoro_merchandising_title = '', null, zoro_merchandising_title) as zoro_merchandising_title , if(supplier_product_title= '', null, supplier_product_title) as supplier_product_title , Zoro_Min_Order_Quantity_MOQ , Zoro_Pricing_Min_Order_Quantity_MOQ , default_title_tag , default_meta_description , custom_title_tag , custom_meta_description , freightTierCost , customer_lead_time , cpir_invoice_cost_modified_dt , erpId , price_type_comment , promotion_reason_type , promotion_sell_price , final_price_comment , zoro_price from add_zoro_min_order_qty ) select * except(Zoro_Min_Order_Quantity_MOQ, Zoro_Pricing_Min_Order_Quantity_MOQ) from keep_order -- none of the item should have null sales price where sales_price is not null ;