SELECT
sm3.item_code AS item_code
, sm3.shipment AS shipment
, sm3.order_start_date AS order_start_date
, sm3.order_end_date AS order_end_date
, '99991231' AS delivery_date
, sm3.pattern_type AS pattern_type
, sm3.pattern_code AS pattern_code
, sm3.host_cycle_code AS host_cycle_code
, sm3.store_cycle_code AS store_cycle_code
, sm3.lead_time AS lead_time
<!-- 障害5680 START -->
, COALESCE(nkm7.delivery_schedule_code, '0') AS delivery_schedule_code
<!-- 障害5680 END -->
, sm3.pma_code AS pma_code
, sm3.information_category_code AS information_category_code
, sm3.subcategory_code AS subcategory_code
, sm3.vendor_code AS vendor_code
, sm3.item_name AS item_name
, sm3.quantity AS quantity
, sm3.order_unit AS order_unit
, sm3.order_limit_quantity AS order_limit_quantity
, sm3.minimum_order_quantity AS minimum_order_quantity
, sm3.recommendation_sales_price AS recommendation_sales_price
, sm3.quantity_sales_price AS quantity_sales_price
, sm3.cost_unit_price AS cost_unit_price
, sm3.gp AS gp
, CASE
WHEN tgm3.item_code IS NOT NULL
THEN '1'
ELSE '0'
END AS special_sale_price_flag
<!-- 障害5680 START -->
, COALESCE(tgm3.special_cost_unit_price, 0) AS special_cost_unit_price
, COALESCE(tgm3.special_sales_unit_price, 0) AS special_selling_price
, COALESCE(tgm3.special_gp, 0) AS special_gp
, tgm3.apply_start_date AS special_cost_unit_price_apply_start_date
, tgm3.apply_end_date AS special_cost_unit_price_apply_end_date
<!-- 障害5680 END -->
, sm3.date_type AS date_type
, sm3.monday_no_delivery AS monday_no_delivery
, sm3.tuesday_no_delivery AS tuesday_no_delivery
, sm3.wednesday_no_delivery AS wednesday_no_delivery
, sm3.thursday_no_delivery AS thursday_no_delivery
, sm3.friday_no_delivery AS friday_no_delivery
, sm3.saturday_no_delivery AS saturday_no_delivery
, sm3.sunday_no_delivery AS sunday_no_delivery
, sm3.freshness_management_type AS freshness_management_type
, sm3.delivery_freshness AS delivery_freshness
, sm3.sales_freshness AS sales_freshness
, sm3.date_quantity_unit AS date_quantity_unit
<!-- 障害5680 START -->
, COALESCE(hjm3.ob_code, '9999') AS ob_code
<!-- 障害5680 END -->
, sdmp3.recommendation_cut_date AS recommendation_cut_date
, NULL AS first_order_date
, NULL AS first_delivery_date
, '9' AS first_1_week_type
, pmam3.unprofitableline_evaluation_excluded_flag AS unprofitableline_evaluation_excluded_flag
, '0' AS item_status_type_display_change_flag
, jm3.information_category_display_order AS information_category_display_order
, 0 AS order_quantity
, '0' AS send_flag
, 0 AS confirm_delivery_plan_quantity
, 0 AS first_delivery_plan_quantity
<!-- 障害5390 発注読取非デイリー商品・陳列位置作成-v352 start -->
, CASE
WHEN jm3.adopt_setting_impossible_flag IN ('1', '2') THEN '9'
ELSE '2'
END AS adopt_type
, CASE
WHEN jm3.adopt_setting_impossible_flag IN ('1', '2') THEN '9'
ELSE '2'
END AS item_adopt_type
<!-- 障害5390 発注読取非デイリー商品・陳列位置作成-v352 end -->
, NULL AS tasting_flag
, 0 AS tentative_order_quantity
, '1' AS order_impossible_flag
, '0' AS forward_no_delivery_frag
, '9' AS new_week_type
<!-- 障害5680 START -->
, COALESCE(bkm3.unpacked_code, LPAD('',8 )) AS unpacked_code
, COALESCE(bkm3.unpacked_conversion_quantity, 0) AS unpacked_conversion_quantity
, COALESCE(bkm3.unpacked_conversion_excluded_flag, ' ') AS unpacked_conversion_excluded_flag
, COALESCE(bkm3.unpacked_code, sm3.item_code) AS unpacked_item_code
<!-- 障害5680 END -->
, '1' AS other_information_category_flag
, ksm3.not_daily_ai_order_minimum_display_quantity_input_flag
, skm3.order_method_type AS order_method_type
<!-- 障害5340 発注読取非デイリー商品・陳列位置作成-v350v351 start -->
, CASE
WHEN skm3.order_method_type IN ('04', '05', '06') THEN jm3.ai_order_use_type
WHEN skm3.order_method_type = '09' AND sm3.freshness_management_type in ('1', '2') THEN jm3.long_freshness_daily_ai_order_target_type
ELSE '0'
END AS ai_order_use_type
<!-- 障害5340 発注読取非デイリー商品・陳列位置作成-v350v351 end -->
, '0' AS tentative_order_existence_flag
FROM
<!-- 障害5680 START -->
m_date_of_item_by_pattern_${dateInformationOfItemByPatternVersion} sdmp3
LEFT OUTER JOIN m_delivery_type_${deliveryTypeVersion} nkm7
<!-- 障害5680 END -->
ON nkm7.original_store_code = #{originalStoreCode}
AND nkm7.host_cycle_code = #{hostCycleCode}
AND nkm7.apply_start_date <= #{specifyOrderDate} ::DATE
AND nkm7.apply_end_date >= #{specifyOrderDate} ::DATE
INNER JOIN m_itemWK sm3
ON sm3.item_code = sdmp3.item_code
AND sm3.pattern_type = sdmp3.pattern_type
AND sm3.pattern_code = sdmp3.pattern_code
AND sdmp3.recommendation_cut_date - INTERVAL '1 day' BETWEEN sm3.apply_start_date AND sm3.apply_end_date
AND sm3.order_end_date < #{specifyOrderDate} ::DATE
<!-- 障害5680 START -->
INNER JOIN m_pma_${pma} pmam3
<!-- 障害5680 END -->
ON pmam3.pma_code = sm3.pma_code
AND pmam3.apply_start_date <= #{specifyOrderDate} ::DATE
AND pmam3.apply_end_date >= #{specifyOrderDate} ::DATE
INNER JOIN order_reading_selection_information_categoryWK hyj3
ON hyj3.host_cycle_code = sm3.host_cycle_code
AND hyj3.store_cycle_code = sm3.store_cycle_code
AND hyj3.information_category_code = sm3.information_category_code
<!-- 障害5680 START -->
INNER JOIN m_cycle_${cycleVersion} skm3
<!-- 障害5680 END -->
ON skm3.host_cycle_code = sm3.host_cycle_code
AND skm3.store_cycle_code = sm3.store_cycle_code
AND skm3.apply_start_date <= #{specifyOrderDate} ::DATE
AND skm3.apply_end_date >= #{specifyOrderDate} ::DATE
LEFT OUTER JOIN m_order_basic_setting ksm3
ON ksm3.original_store_code = #{originalStoreCode}
<!-- 障害5680 START -->
INNER JOIN m_information_category_${informationCategoryVersion} jm3
<!-- 障害5680 END -->
ON jm3.information_category_code = sm3.information_category_code
AND jm3.apply_start_date <= #{specifyOrderDate} ::DATE
AND jm3.apply_end_date >= #{specifyOrderDate} ::DATE
LEFT OUTER JOIN m_adopt tm3
<!-- 障害5997 発注読取非デイリー商品・陳列位置作成-v386 start -->
ON tm3.original_store_code = '${originalStoreCode}'
<!-- 障害5997 発注読取非デイリー商品・陳列位置作成-v386 end -->
AND tm3.item_code = sm3.item_code
<!-- 障害5680 START -->
LEFT OUTER JOIN m_date_of_item_by_store_${dateInformationOfItemByStoreVersion} sdmk3
<!-- 障害5680 END -->
ON sdmk3.original_store_code = #{originalStoreCode}
AND sdmk3.item_code = sm3.item_code
AND sdmk3.recommendation_cut_date < #{specifyOrderDate} ::DATE
<!-- 障害5680 START -->
LEFT OUTER JOIN m_display_order_${displayOrderVersion} hjm3
<!-- 障害5680 END -->
ON hjm3.item_code = sm3.item_code
AND hjm3.apply_start_date = (
SELECT
MAX(hjmt3.apply_start_date) AS apply_start_date
FROM
<!-- 障害5680 START -->
m_display_order_${displayOrderVersion} hjmt3
<!-- 障害5680 END -->
WHERE
hjmt3.apply_start_date <= #{thisMonday} ::DATE
)
<!-- 障害5680 START -->
LEFT OUTER JOIN m_special_original_selling_price_${specialOriginalSellingPriceVersion} tgm3
<!-- 障害5680 END -->
ON tgm3.item_code = sm3.item_code
AND tgm3.pattern_type = sm3.pattern_type
AND tgm3.pattern_code = sm3.pattern_code
AND tgm3.apply_start_date <= #{specifyOrderDate} ::DATE
AND tgm3.apply_end_date >= #{specifyOrderDate} ::DATE
<!-- 障害5680 START -->
LEFT OUTER JOIN m_unpacked_conversion_${unpackedConversion} bkm3
<!-- 障害5680 END -->
ON bkm3.item_code = sm3.item_code
AND bkm3.pattern_type = sm3.pattern_type
AND bkm3.pattern_code = sm3.pattern_code
AND bkm3.apply_start_date <= #{specifyOrderDate} ::DATE
AND bkm3.apply_end_date >= #{specifyOrderDate} ::DATE
INNER JOIN order_reading_date_managementWK hhk3
ON hhk3.date_type = sm3.date_type
WHERE
(
sdmp3.delivery_schedule_code = '0'
OR sdmp3.delivery_schedule_code = nkm7.delivery_schedule_code
)
AND sdmp3.recommendation_cut_date < #{specifyOrderDate} ::DATE
AND sdmk3.item_code IS NULL 给我分析一下LEFT OUTER JOIN m_adopt tm3是不是多余的
最新发布
博客内容提及开始起航，虽信息简短，但传达出开启新征程之意。
扫一扫
2113
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
