text: CREATE OR REPLACE VIEW public.vw_carton_label_lbl
  AS SELECT carton_labels.id AS carton_label_id,
      carton_labels.production_run_id,
      packhouses.plant_resource_code AS packhouse,
      lines.plant_resource_code AS line,
      carton_labels.label_name,
      farms.farm_code,
      farm_groups.farm_group_code,
      COALESCE(mkt_org_pucs.puc_code, pucs.puc_code) AS puc_code,
      pucs.gap_code,
      orchards.orchard_code,
      commodities.code AS commodity,
      commodities.description AS commodity_description,
      cultivar_groups.cultivar_group_code,
      cultivar_groups.description AS cultivar_group_description,
      cultivars.cultivar_name,
      cultivars.description AS cultivar_description,
      marketing_varieties.marketing_variety_code,
      marketing_varieties.description AS marketing_variety_description,
      COALESCE(cvv.marketing_variety_code, marketing_varieties.marketing_variety_code) AS customer_or_marketing_variety,
      COALESCE(cvv.description, marketing_varieties.description) AS customer_or_marketing_variety_desc,
      cvv.marketing_variety_code AS customer_variety_code,
      cvv.description AS customer_variety_description,
      std_fruit_size_counts.size_count_value,
      std_fruit_size_counts.size_count_interval_group,
      std_fruit_size_counts.marketing_size_range_mm,
      uoms.uom_code AS size_count_uom,
      fruit_size_references.size_reference,
      fruit_actual_counts_for_packs.actual_count_for_pack,
      COALESCE(fruit_size_references.size_reference, fruit_actual_counts_for_packs.actual_count_for_pack::text) AS size_reference_or_actual_count,
      basic_pack_codes.basic_pack_code,
      standard_pack_codes.standard_pack_code,
      standard_pack_codes.std_pack_label_code,
      standard_product_weights.nett_weight AS pack_nett_weight,
      fn_party_role_name(carton_labels.marketing_org_party_role_id) AS marketer,
      fn_party_role_delivery_address(carton_labels.marketing_org_party_role_id) AS marketer_address,
      marks.mark_code,
      inventory_codes.inventory_code,
      product_setup_templates.template_name,
      pm_boms.bom_code,
      ( SELECT array_agg(clt.treatment_code) AS array_agg
             FROM ( SELECT t.treatment_code
                     FROM treatments t
                       JOIN carton_labels cl ON t.id = ANY (cl.treatment_ids)
                    WHERE cl.id = carton_labels.id
                    ORDER BY t.treatment_code DESC) clt) AS treatments,
      carton_labels.client_size_reference,
      carton_labels.client_product_code,
      carton_labels.marketing_order_number,
      target_market_groups.target_market_group_name AS packed_tm_group,
      target_markets.target_market_name,
          CASE
              WHEN target_markets.inspection_tm THEN target_markets.target_market_name
              ELSE target_market_groups.target_market_group_name
          END AS inspection_tm,
      fn_party_role_name(product_resource_allocations.target_customer_party_role_id) AS target_customer,
      seasons.season_code,
      pm_subtypes.subtype_code,
      pm_types.pm_type_code,
      cartons_per_pallet.cartons_per_pallet,
      pm_products.product_code,
      carton_labels.pallet_number,
      carton_labels.sell_by_code,
      grades.grade_code,
      carton_labels.product_chars,
      carton_labels.pick_ref,
      carton_labels.phc,
      lines.resource_properties ->> 'gln'::text AS gln_code,
          CASE
              WHEN commodities.code::text = 'SC'::text THEN concat_ws('/'::text, fruit_size_references.size_reference, std_fruit_size_counts.size_count_interval_group)
              ELSE concat_ws('/'::text, fruit_size_references.size_reference, fruit_actual_counts_for_packs.actual_count_for_pack)
          END AS count_swap_rule,
      contract_workers.personnel_number,
      marketing_pucs.puc_code AS marketing_puc,
      registered_orchards.orchard_code AS marketing_orchard,
      carton_labels.gtin_code,
      pallet_formats.description AS pallet_format_description,
      rmt_classes.rmt_class_code,
      rmt_classes.description AS rmt_class_description,
      marketing_org.short_description AS marketing_org_short,
      marketing_org.medium_description AS marketing_org_medium,
      reg_mkt_gln.registration_code AS marketing_org_gln,
      COALESCE(target_markets.target_market_name, target_market_groups.target_market_group_name) AS tm_or_packed_tm,
      rmt_codes.rmt_code AS rmt_code,
      production_runs.run_batch_number,
      colour_percentages.colour_percentage,
      actual_cold_treatments.treatment_code AS actual_cold_treatment,
      actual_ripeness_treatments.treatment_code AS actual_ripeness_treatment,
      CASE WHEN production_runs.lot_no_date IS NULL THEN
        NULL
      ELSE
        'L ' || LPAD(EXTRACT('week' FROM production_runs.lot_no_date)::text, 2, '0') || '-' || LPAD(EXTRACT('isodow' FROM production_runs.lot_no_date)::text, 2, '0')
      END AS ogl_number
  
     FROM carton_labels
       LEFT JOIN production_runs ON production_runs.id = carton_labels.production_run_id
       LEFT JOIN product_resource_allocations ON product_resource_allocations.id = carton_labels.product_resource_allocation_id
       LEFT JOIN product_setups ON product_setups.id = product_resource_allocations.product_setup_id
       LEFT JOIN product_setup_templates ON product_setup_templates.id = product_setups.product_setup_template_id
       LEFT JOIN plant_resources packhouses ON packhouses.id = carton_labels.packhouse_resource_id
       LEFT JOIN plant_resources lines ON lines.id = carton_labels.production_line_id
       JOIN farms ON farms.id = carton_labels.farm_id
       LEFT JOIN farm_groups ON farm_groups.id = farms.farm_group_id
       JOIN pucs ON pucs.id = carton_labels.puc_id
       JOIN orchards ON orchards.id = carton_labels.orchard_id
       JOIN cultivar_groups ON cultivar_groups.id = carton_labels.cultivar_group_id
       LEFT JOIN grades ON grades.id = carton_labels.grade_id
       LEFT JOIN cultivars ON cultivars.id = carton_labels.cultivar_id
       LEFT JOIN commodities ON commodities.id = cultivar_groups.commodity_id
       JOIN marketing_varieties ON marketing_varieties.id = carton_labels.marketing_variety_id
       LEFT JOIN customer_varieties ON customer_varieties.id = carton_labels.customer_variety_id
       LEFT JOIN marketing_varieties cvv ON cvv.id = customer_varieties.variety_as_customer_variety_id
       LEFT JOIN std_fruit_size_counts ON std_fruit_size_counts.id = carton_labels.std_fruit_size_count_id
       LEFT JOIN uoms ON uoms.id = std_fruit_size_counts.uom_id
       LEFT JOIN fruit_size_references ON fruit_size_references.id = carton_labels.fruit_size_reference_id
       LEFT JOIN fruit_actual_counts_for_packs ON fruit_actual_counts_for_packs.id = carton_labels.fruit_actual_counts_for_pack_id
       JOIN basic_pack_codes ON basic_pack_codes.id = carton_labels.basic_pack_code_id
       JOIN standard_pack_codes ON standard_pack_codes.id = carton_labels.standard_pack_code_id
       JOIN marks ON marks.id = carton_labels.mark_id
       JOIN inventory_codes ON inventory_codes.id = carton_labels.inventory_code_id
       LEFT JOIN pm_boms ON pm_boms.id = carton_labels.pm_bom_id
       LEFT JOIN pm_subtypes ON pm_subtypes.id = carton_labels.pm_subtype_id
       LEFT JOIN pm_types ON pm_types.id = carton_labels.pm_type_id
       JOIN target_market_groups ON target_market_groups.id = carton_labels.packed_tm_group_id
       LEFT JOIN target_markets ON target_markets.id = carton_labels.target_market_id
       JOIN seasons ON seasons.id = carton_labels.season_id
       JOIN cartons_per_pallet ON cartons_per_pallet.id = carton_labels.cartons_per_pallet_id
       LEFT JOIN pm_products ON pm_products.id = carton_labels.fruit_sticker_pm_product_id
       JOIN pallet_formats ON pallet_formats.id = carton_labels.pallet_format_id
       LEFT JOIN standard_product_weights ON standard_product_weights.commodity_id = commodities.id AND standard_product_weights.standard_pack_id = carton_labels.standard_pack_code_id
       LEFT JOIN contract_workers ON contract_workers.id = carton_labels.contract_worker_id
       LEFT JOIN party_roles mkt_pr ON mkt_pr.id = carton_labels.marketing_org_party_role_id
       LEFT JOIN organizations marketing_org ON marketing_org.id = mkt_pr.organization_id
       LEFT JOIN registrations reg_mkt_gln ON reg_mkt_gln.party_role_id = carton_labels.marketing_org_party_role_id AND reg_mkt_gln.registration_type = 'MARKETER_GLN'
       LEFT JOIN farm_puc_orgs ON farm_puc_orgs.farm_id = carton_labels.farm_id AND farm_puc_orgs.organization_id = mkt_pr.organization_id
       LEFT JOIN pucs mkt_org_pucs ON mkt_org_pucs.id = farm_puc_orgs.puc_id
       LEFT JOIN pucs marketing_pucs ON marketing_pucs.id = carton_labels.marketing_puc_id
       LEFT JOIN registered_orchards ON registered_orchards.id = carton_labels.marketing_orchard_id
       LEFT JOIN rmt_classes ON rmt_classes.id = carton_labels.rmt_class_id
       LEFT JOIN colour_percentages ON colour_percentages.id = carton_labels.colour_percentage_id
       LEFT JOIN treatments actual_cold_treatments ON actual_cold_treatments.id = carton_labels.actual_cold_treatment_id
       LEFT JOIN treatments actual_ripeness_treatments ON actual_ripeness_treatments.id = carton_labels.actual_ripeness_treatment_id
       LEFT JOIN rmt_codes ON rmt_codes.id = carton_labels.rmt_code_id;
  