WITH un_hatchu_taisho_item_data_json(
get_information
) AS (
VALUES (
#{unHatchuTaishoItemDataJson} ::JSON
)
),
un_hatchu_taisho_item_list AS (
SELECT
json_array_elements(get_information ::json) ->> 'item_code' ::VARCHAR AS item_code
,json_array_elements(get_information ::json) ->> 'shipment' ::VARCHAR AS shipment
,json_array_elements(get_information ::json) ->> 'first_delivery_date' ::VARCHAR AS first_delivery_date
,json_array_elements(get_information ::json) ->> 'delivery_date' ::VARCHAR AS delivery_date
,json_array_elements(get_information ::json) ->> 'pattern_type' ::VARCHAR AS pattern_type
,json_array_elements(get_information ::json) ->> 'pattern_code' ::VARCHAR AS pattern_code
,json_array_elements(get_information ::json) ->> 'delivery_schedule_code' ::VARCHAR AS delivery_schedule_code
,json_array_elements(get_information ::json) ->> 'host_cycle_code' ::VARCHAR AS host_cycle_code
,json_array_elements(get_information ::json) ->> 'store_cycle_code' ::VARCHAR AS store_cycle_code
,json_array_elements(get_information ::json) ->> 'lead_time' ::VARCHAR AS lead_time
,json_array_elements(get_information ::json) ->> 'new_week_type' ::VARCHAR AS new_week_type
FROM
un_hatchu_taisho_item_data_json
)
</if>
<if test='unHatchuTaishoItemDataJson == "{}"' >
un_hatchu_taisho_item_list AS (
SELECT
NULL AS item_code
,NULL AS shipment
,NULL AS first_delivery_date
,NULL AS delivery_date
,NULL AS pattern_type
,NULL AS pattern_code
,NULL AS delivery_schedule_code
,NULL AS host_cycle_code
,NULL AS store_cycle_code
,NULL AS lead_time
,NULL AS new_week_type
)
</if>
, next_tentative_order_dateWK AS (
SELECT
hhas.item_code AS item_code
, hhas.shipment AS shipment
, hhas.first_delivery_date :: DATE AS first_delivery_date
, hhas.delivery_date :: DATE AS delivery_date
, MIN(hsm.order_date) :: DATE AS cost_unit_item_next_plan_order_date
, MIN(hsm2.order_date) :: DATE AS forward_tilt_item_next_plan_order_date
, MAX(hhas.pattern_type) AS pattern_type
, MAX(hhas.pattern_code) AS pattern_code
FROM
un_hatchu_taisho_item_list hhas
INNER JOIN m_order_schedule hsm
ON hsm.pattern_type = hhas.pattern_type
AND hsm.pattern_code = hhas.pattern_code
AND hsm.delivery_schedule_code = hhas.delivery_schedule_code
AND hsm.order_date > #{specifyOrderDate} :: DATE
AND hsm.host_cycle_code = hhas.host_cycle_code
AND hsm.store_cycle_code = hhas.store_cycle_code
AND hsm.lead_time = hhas.lead_time
AND hsm.version = #{orderScheduleVersion}
LEFT OUTER JOIN m_order_schedule hsm2
ON hsm2.pattern_type = hhas.pattern_type
AND hsm2.pattern_code = hhas.pattern_code
AND hsm2.delivery_schedule_code = hhas.delivery_schedule_code
AND hsm2.order_date > #{specifyOrderDate} :: DATE
AND hsm2.host_cycle_code = hhas.host_cycle_code
AND hsm2.store_cycle_code = hhas.store_cycle_code
AND hsm2.lead_time = hhas.lead_time
AND hsm2.version = #{orderScheduleVersion}
AND hhas.first_delivery_date IS NOT NULL
AND hsm2.delivery_date > hhas.first_delivery_date :: DATE
WHERE
hhas.new_week_type <> '9'
GROUP BY
hhas.item_code
, hhas.shipment
, hhas.first_delivery_date
, hhas.delivery_date
)
,next_order_dateWK AS (
SELECT
jph.item_code AS item_code
, jph.shipment AS shipment
, jph.first_delivery_date AS first_delivery_date
, jph.delivery_date AS delivery_date
, CASE
WHEN jph.cost_unit_item_next_plan_order_date >= #{nextWeekMonday}:: DATE
THEN MIN(hsm3.order_date)
ELSE jph.cost_unit_item_next_plan_order_date
END AS cost_unit_item_next_order_date
, CASE
WHEN jph.forward_tilt_item_next_plan_order_date >= #{nextWeekMonday}:: DATE
THEN MIN(hsm4.order_date)
ELSE jph.forward_tilt_item_next_plan_order_date
END AS forward_tilt_item_next_order_date
, MAX(jph.pattern_type) AS pattern_type
, MAX(jph.pattern_code) AS pattern_code
FROM
next_tentative_order_dateWK jph
INNER JOIN m_item sm
ON sm.item_code = jph.item_code
AND sm.shipment = jph.shipment
AND sm.pattern_type = jph.pattern_type
AND sm.pattern_code = jph.pattern_code
AND sm.apply_start_date <= jph.cost_unit_item_next_plan_order_date
AND sm.version = #{itemVersion}
AND sm.apply_end_date >= jph.cost_unit_item_next_plan_order_date
AND sm.order_start_date <= jph.cost_unit_item_next_plan_order_date
AND sm.order_end_date >= jph.cost_unit_item_next_plan_order_date
INNER JOIN m_pattern pm
ON pm.original_store_code = #{originalStoreCode}
AND pm.pattern_type = sm.pattern_type
AND pm.apply_start_date <= jph.cost_unit_item_next_plan_order_date
AND pm.pattern_code = sm.pattern_code
AND pm.version = #{patternVersion}
AND pm.apply_end_date >= jph.cost_unit_item_next_plan_order_date
LEFT OUTER JOIN m_order_schedule hsm3
ON hsm3.pattern_type = sm.pattern_type
AND hsm3.pattern_code = sm.pattern_code
AND (
hsm3.delivery_schedule_code = '0'
OR hsm3.delivery_schedule_code IN (
SELECT
nkm.delivery_schedule_code
FROM
m_delivery_type nkm
WHERE
nkm.original_store_code = #{originalStoreCode}
AND nkm.host_cycle_code = #{hostCycleCode}
AND nkm.apply_start_date <= jph.cost_unit_item_next_plan_order_date
AND nkm.version = #{deliveryTypeVersion}
AND nkm.apply_end_date >= jph.cost_unit_item_next_plan_order_date
)
)
AND hsm3.order_date >= #{nextWeekMonday} :: DATE
AND hsm3.host_cycle_code = sm.host_cycle_code
AND hsm3.store_cycle_code = sm.store_cycle_code
AND hsm3.lead_time = sm.lead_time
AND hsm3.version = #{orderScheduleVersion}
LEFT OUTER JOIN m_item sm2
ON sm2.item_code = jph.item_code
AND sm2.shipment = jph.shipment
AND sm2.pattern_type = jph.pattern_type
AND sm2.pattern_code = jph.pattern_code
AND sm2.apply_start_date <= jph.forward_tilt_item_next_plan_order_date
AND sm2.version = #{itemVersion}
AND sm2.apply_end_date >= jph.forward_tilt_item_next_plan_order_date
AND sm2.order_start_date <= jph.forward_tilt_item_next_plan_order_date
AND sm2.order_end_date >= jph.forward_tilt_item_next_plan_order_date
LEFT OUTER JOIN m_pattern pm2
ON pm2.original_store_code = #{originalStoreCode}
AND pm2.pattern_type = sm2.pattern_type
AND pm2.apply_start_date <= jph.forward_tilt_item_next_plan_order_date
AND pm2.pattern_code = sm2.pattern_code
AND pm2.version = #{patternVersion}
AND pm2.apply_end_date >= jph.forward_tilt_item_next_plan_order_date
LEFT OUTER JOIN m_order_schedule hsm4
ON hsm4.pattern_type = sm.pattern_type
AND hsm4.pattern_code = sm.pattern_code
AND (
hsm4.delivery_schedule_code = '0'
OR hsm4.delivery_schedule_code IN (
SELECT
nkm2.delivery_schedule_code
FROM
m_delivery_type nkm2
WHERE
nkm2.original_store_code = #{originalStoreCode}
AND nkm2.host_cycle_code = #{hostCycleCode}
AND nkm2.apply_start_date <= jph.forward_tilt_item_next_plan_order_date
AND nkm2.version = #{deliveryTypeVersion}
AND nkm2.apply_end_date >= jph.forward_tilt_item_next_plan_order_date
)
)
AND hsm4.order_date >= #{nextWeekMonday} :: DATE
AND hsm4.delivery_date > jph.first_delivery_date
AND hsm4.host_cycle_code = sm2.host_cycle_code
AND hsm4.store_cycle_code = sm2.store_cycle_code
AND hsm4.lead_time = sm2.lead_time
AND hsm4.version = #{orderScheduleVersion}
GROUP BY
jph.item_code
, jph.shipment
, jph.first_delivery_date
, jph.delivery_date
, jph.cost_unit_item_next_plan_order_date
, jph.forward_tilt_item_next_plan_order_date
)
SELECT
jh.item_code AS item_code
, jh.shipment AS shipment
, hsm5.delivery_date AS cost_unit_item_next_delivery_date
, hsm6.delivery_date AS forward_tilt_item_next_delivery_date
, CASE
WHEN hsm5.delivery_date IS NOT NULL
THEN hsm5.delivery_date ::date - INTERVAL '1 day'
ELSE jh.delivery_date
END AS sum_end_date
FROM
next_order_dateWK jh
INNER JOIN m_item sm3
ON sm3.item_code = jh.item_code
AND sm3.shipment = jh.shipment
AND sm3.pattern_type = jh.pattern_type
AND sm3.pattern_code = jh.pattern_code
AND sm3.apply_start_date <= jh.cost_unit_item_next_order_date
AND sm3.version = #{itemVersion}
AND sm3.apply_end_date >= jh.cost_unit_item_next_order_date
AND sm3.order_start_date <= jh.cost_unit_item_next_order_date
AND sm3.order_end_date >= jh.cost_unit_item_next_order_date
INNER JOIN m_pattern pm3
ON pm3.original_store_code = #{originalStoreCode}
AND pm3.pattern_type = sm3.pattern_type
AND pm3.apply_start_date <= jh.cost_unit_item_next_order_date
AND pm3.version = #{patternVersion}
AND pm3.pattern_code = sm3.pattern_code
AND pm3.apply_end_date >= jh.cost_unit_item_next_order_date
LEFT OUTER JOIN m_order_schedule hsm5
ON hsm5.pattern_type = sm3.pattern_type
AND hsm5.pattern_code = sm3.pattern_code
AND (
hsm5.delivery_schedule_code = '0'
OR hsm5.delivery_schedule_code IN (
SELECT
nkm7.delivery_schedule_code
FROM
m_delivery_type nkm7
WHERE
nkm7.original_store_code = #{originalStoreCode}
AND nkm7.host_cycle_code = #{hostCycleCode}
AND nkm7.apply_start_date <= jh.cost_unit_item_next_order_date
AND nkm7.version = #{deliveryTypeVersion}
AND nkm7.apply_end_date >= jh.cost_unit_item_next_order_date
)
)
AND hsm5.order_date = jh.cost_unit_item_next_order_date
AND hsm5.host_cycle_code = sm3.host_cycle_code
AND hsm5.store_cycle_code = sm3.store_cycle_code
AND hsm5.lead_time = sm3.lead_time
AND hsm5.version = #{orderScheduleVersion}
LEFT OUTER JOIN m_item sm4
ON sm4.item_code = jh.item_code
AND sm4.shipment = jh.shipment
AND sm4.pattern_type = jh.pattern_type
AND sm4.pattern_code = jh.pattern_code
AND sm4.apply_start_date <= jh.forward_tilt_item_next_order_date
AND sm4.version = #{itemVersion}
AND sm4.apply_end_date >= jh.forward_tilt_item_next_order_date
AND sm4.order_start_date <= jh.forward_tilt_item_next_order_date
AND sm4.order_end_date >= jh.forward_tilt_item_next_order_date
LEFT OUTER JOIN m_pattern pm4
ON pm4.original_store_code = #{originalStoreCode}
AND pm4.pattern_type = sm4.pattern_type
AND pm4.apply_start_date <= jh.forward_tilt_item_next_order_date
AND pm4.version = #{patternVersion}
AND pm4.pattern_code = sm4.pattern_code
AND pm4.apply_end_date >= jh.forward_tilt_item_next_order_date
LEFT OUTER JOIN m_order_schedule hsm6
ON hsm6.pattern_type = sm4.pattern_type
AND hsm6.pattern_code = sm4.pattern_code
AND (
hsm6.delivery_schedule_code = '0'
OR hsm6.delivery_schedule_code IN (
SELECT
nkm4.delivery_schedule_code
FROM
m_delivery_type nkm4
WHERE
nkm4.original_store_code = #{originalStoreCode}
AND nkm4.host_cycle_code = #{hostCycleCode}
AND nkm4.apply_start_date <= jh.forward_tilt_item_next_order_date
AND nkm4.version = #{deliveryTypeVersion}
AND nkm4.apply_end_date >= jh.forward_tilt_item_next_order_date
)
)
AND hsm6.order_date = jh.forward_tilt_item_next_order_date
AND hsm6.delivery_date > jh.first_delivery_date
AND hsm6.host_cycle_code = sm4.host_cycle_code
AND hsm6.store_cycle_code = sm4.store_cycle_code
AND hsm6.lead_time = sm4.lead_time
AND hsm6.version = #{orderScheduleVersion}
GROUP BY
jh.item_code
,jh.shipment
,hsm5.delivery_date
,hsm6.delivery_date
,jh.delivery_date 判断一下 LEFT OUTER JOIN m_order_schedule hsm2 最后用到了什么地方
扫一扫
121
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
