WITH
tmp17 as (
SELECT DISTINCT
jk. *
FROM
m_number_of_information_category jk
INNER JOIN
m_pattern pt
ON jk.pattern_type = pt.pattern_type
AND jk.pattern_code = pt.pattern_code
AND pt.version = $1
AND pt.original_store_code = $2
AND TO_DATE($3, 'YYYY-MM-DD') BETWEEN pt.apply_start_date AND pt.apply_end_date
WHERE
jk.version = $4
AND TO_DATE($5, 'YYYY-MM-DD') BETWEEN jk.apply_start_date AND jk.apply_end_date
AND ( jk.license_code = '00'
OR jk.license_code IN (
$6
,
$7
,
$8
,
$9
,
$10
,
$11
,
$12
,
$13
,
$14
,
$15
,
$16
,
$17
,
$18
,
$19
,
$20
,
$21
,
$22
,
$23
,
$24
,
$25
)
)
AND jk.reading_item_quantity > 0
AND jk.specific_item_flag = '0'
UNION
SELECT DISTINCT
jk. *
FROM
m_number_of_information_category jk
INNER JOIN
m_pattern pt
ON jk.pattern_type = pt.pattern_type
AND jk.pattern_code = pt.pattern_code
AND pt.version = $26
AND pt.original_store_code = $27
AND TO_DATE($28, 'YYYY-MM-DD') BETWEEN pt.apply_start_date AND pt.apply_end_date
INNER JOIN
m_item sm
ON jk.pattern_type = sm.pattern_type
AND jk.pattern_code = sm.pattern_code
AND jk.host_cycle_code = sm.host_cycle_code
AND jk.store_cycle_code = sm.store_cycle_code
AND jk.information_category_code = sm.information_category_code
AND sm.version = $29
AND TO_DATE($30, 'YYYY-MM-DD') BETWEEN sm.apply_start_date AND sm.apply_end_date
AND sm.specific_item_type IN ('1', 'G')
INNER JOIN
m_item_by_specific_store_recommendation sr
ON pt.original_store_code = sr.original_store_code
AND sm.item_code = sr.item_code
AND sr.version = $31
AND TO_DATE($32, 'YYYY-MM-DD') BETWEEN sr.apply_start_date AND sr.apply_end_date
WHERE
jk.version = $33
AND TO_DATE($34, 'YYYY-MM-DD') BETWEEN jk.apply_start_date AND jk.apply_end_date
AND ( jk.license_code = '00'
OR jk.license_code IN (
$35
,
$36
,
$37
,
$38
,
$39
,
$40
,
$41
,
$42
,
$43
,
$44
,
$45
,
$46
,
$47
,
$48
,
$49
,
$50
,
$51
,
$52
,
$53
,
$54
)
)
AND jk.reading_item_quantity > 0
AND jk.specific_item_flag = '1'
)
,
tmp3 as (
SELECT
hg.host_cycle_code
,hg.store_cycle_code
,SUM(hg.reading_item_quantity) AS reading_item_quantity
,sc.store_cycle_name
,sc.cycle_display_order
,sc.order_method_type
,pt.original_store_code
,hg.pattern_type
,hg.pattern_code
FROM
m_number_of_order_group hg
INNER JOIN
m_pattern pt
ON hg.pattern_type = pt.pattern_type
AND hg.pattern_code = pt.pattern_code
AND pt.version = $55
AND pt.original_store_code = $56
AND TO_DATE($57, 'YYYY-MM-DD') BETWEEN pt.apply_start_date AND pt.apply_end_date
INNER JOIN
m_cycle sc
ON hg.host_cycle_code = sc.host_cycle_code
AND hg.store_cycle_code = sc.store_cycle_code
AND sc.version = $58
AND sc.order_method_type IN ('02','03','04','05','06','07','09','10')
AND TO_DATE($59, 'YYYY-MM-DD') BETWEEN sc.apply_start_date AND sc.apply_end_date
WHERE hg.version = $60
AND TO_DATE($61, 'YYYY-MM-DD') BETWEEN hg.apply_start_date AND hg.apply_end_date
AND hg.specific_item_flag = '0'
AND ( hg.license_code = '00'
OR hg.license_code IN (
$62
,
$63
,
$64
,
$65
,
$66
,
$67
,
$68
,
$69
,
$70
,
$71
,
$72
,
$73
,
$74
,
$75
,
$76
,
$77
,
$78
,
$79
,
$80
,
$81
)
)
GROUP BY
hg.host_cycle_code
, hg.store_cycle_code
, sc.store_cycle_name
, sc.cycle_display_order
, sc.order_method_type
, pt.original_store_code
, hg.pattern_type
, hg.pattern_code
UNION
SELECT
hg.host_cycle_code,
hg.store_cycle_code,
SUM(hg.reading_item_quantity) AS reading_item_quantity,
sc.store_cycle_name,
sc.cycle_display_order,
sc.order_method_type,
pt.original_store_code,
hg.pattern_type,
hg.pattern_code
FROM
m_number_of_order_group hg
INNER JOIN
m_pattern pt
ON hg.pattern_type = pt.pattern_type
AND hg.pattern_code = pt.pattern_code
AND pt.version = $82
AND pt.original_store_code = $83
AND TO_DATE($84, 'YYYY-MM-DD') BETWEEN pt.apply_start_date AND pt.apply_end_date
INNER JOIN
m_cycle sc
ON hg.host_cycle_code = sc.host_cycle_code
AND hg.store_cycle_code = sc.store_cycle_code
AND sc.version = $85
AND sc.order_method_type IN ('02', '03', '04', '05', '06', '07', '09', '10')
AND TO_DATE($86, 'YYYY-MM-DD') BETWEEN sc.apply_start_date AND sc.apply_end_date
INNER JOIN
m_item sm
ON hg.pattern_type = sm.pattern_type
AND hg.pattern_code = sm.pattern_code
AND hg.host_cycle_code = sm.host_cycle_code
AND hg.store_cycle_code = sm.store_cycle_code
AND sm.version = $87
AND TO_DATE($88, 'YYYY-MM-DD') BETWEEN sm.apply_start_date AND sm.apply_end_date
AND sm.specific_item_type IN ('1', 'G')
INNER JOIN
m_item_by_specific_store_recommendation sr
ON pt.original_store_code = sr.original_store_code
AND sm.item_code = sr.item_code
AND sr.version = $89
AND TO_DATE($90, 'YYYY-MM-DD') BETWEEN sr.apply_start_date AND sr.apply_end_date
WHERE
hg.version = $91
AND TO_DATE($92, 'YYYY-MM-DD') BETWEEN hg.apply_start_date AND hg.apply_end_date
AND hg.specific_item_flag = '1'
AND ( hg.license_code = '00'
OR hg.license_code IN (
$93
,
$94
,
$95
,
$96
,
$97
,
$98
,
$99
,
$100
,
$101
,
$102
,
$103
,
$104
,
$105
,
$106
,
$107
,
$108
,
$109
,
$110
,
$111
,
$112
)
)
GROUP BY
hg.host_cycle_code,
hg.store_cycle_code,
sc.store_cycle_name,
sc.cycle_display_order,
sc.order_method_type,
pt.original_store_code,
hg.pattern_type,
hg.pattern_code
)
,
tmp4 as (
SELECT DISTINCT tmp3.host_cycle_code
,tmp3.store_cycle_code
,tmp3.store_cycle_name
,tmp3.cycle_display_order
,tmp3.order_method_type
,tmp3.original_store_code
FROM tmp3
INNER JOIN m_order_schedule hs
ON tmp3.pattern_type = hs.pattern_type
AND tmp3.pattern_code = hs.pattern_code
AND tmp3.host_cycle_code = hs.host_cycle_code
AND tmp3.store_cycle_code = hs.store_cycle_code
AND hs.version = $113
AND hs.order_date = TO_DATE($114, 'YYYY-MM-DD')
LEFT OUTER JOIN m_delivery_type nk
ON hs.host_cycle_code = nk.host_cycle_code
AND hs.delivery_schedule_code = nk.delivery_schedule_code
AND nk.version = $115
AND nk.original_store_code = $116
AND TO_DATE($117, 'YYYY-MM-DD') BETWEEN nk.apply_start_date AND nk.apply_end_date
WHERE tmp3.reading_item_quantity > 0
AND (hs.delivery_schedule_code = '0' OR (hs.delivery_schedule_code != '0'
AND nk.delivery_schedule_code IS NOT NULL))
ORDER BY tmp3.cycle_display_order ASC)
,
tmp5 as (
SELECT DISTINCT
tmp4.original_store_code
,tmp4.host_cycle_code
,tmp4.store_cycle_code
,(CASE WHEN COALESCE(hk.setting_order_action_flag, '0') = '1' THEN '1'
ELSE '0' END) AS setting_order_action_flag
,(CASE WHEN tmp4.order_method_type IN ('09','10') AND
COALESCE(hk.daily_recommendation_quantity_of_stock_distribution_flag,'0') = '1' AND
COALESCE(hk.daily_ai_order_action_flag,'0') = '1' THEN '1'
WHEN tmp4.order_method_type IN ('04','05','06')
AND COALESCE(hk.not_daily_ai_order_action_flag,'0') = '1'
THEN '1' ELSE '0' END) AS ai_order_action_flag
FROM tmp4
LEFT OUTER JOIN m_order_basic_setting hk
ON tmp4.original_store_code = hk.original_store_code
WHERE tmp4.order_method_type IN ('04','05','06','09','10'))
,
tmp6 as (
SELECT DISTINCT
tmp4.host_cycle_code
,tmp4.store_cycle_code
,jt.information_category_code
,jm.information_category_name
,jt.staff_code
,MIN(COALESCE(ik.group_number, ip.group_number)) AS reading_number
,MIN(COALESCE(ik.setting_order, ip.setting_order)) AS setting_order
FROM
m_staff_by_information_order jt
INNER JOIN
tmp4
ON jt.original_store_code = tmp4.original_store_code
AND jt.host_cycle_code = tmp4.host_cycle_code
AND jt.store_cycle_code = tmp4.store_cycle_code
LEFT OUTER JOIN
m_information_category jm
ON jm.version = $118
AND jt.information_category_code = jm.information_category_code
AND TO_DATE($119, 'YYYY-MM-DD') BETWEEN jm.apply_start_date AND jm.apply_end_date
INNER JOIN
tmp17 jk
ON tmp4.host_cycle_code = jk.host_cycle_code
AND tmp4.store_cycle_code = jk.store_cycle_code
AND jt.information_category_code = jk.information_category_code
INNER JOIN
m_reading_number_by_pattern ip
ON ip.pattern_type = jk.pattern_type
AND ip.pattern_code = jk.pattern_code
AND jt.host_cycle_code = ip.host_cycle_code
AND jt.store_cycle_code = ip.store_cycle_code
AND jt.information_category_code = ip.information_category_code
AND ip.version = $120
AND TO_DATE ($121, 'YYYY-MM-DD' ) BETWEEN ip.apply_start_date AND ip.apply_end_date
LEFT OUTER JOIN
m_reading_number_by_store ik
ON jt.host_cycle_code = ik.host_cycle_code
AND jt.store_cycle_code = ik.store_cycle_code
AND jt.information_category_code = ik.information_category_code
AND ik.original_store_code = $122
AND TO_DATE ($123, 'YYYY-MM-DD' ) BETWEEN ik.apply_start_date AND ik.apply_end_date
GROUP BY
tmp4.host_cycle_code,
tmp4.store_cycle_code,
jt.information_category_code,
jm.information_category_name,
jt.staff_code
)
,
tmp7 as (
SELECT tmp4.host_cycle_code
,tmp4.store_cycle_code
,st.information_category_code
,jm.information_category_name
,jm.information_category_display_order
,st.staff_code
FROM
m_staff_by_other_order st
INNER JOIN
tmp4
ON st.original_store_code = tmp4.original_store_code
AND st.host_cycle_code = tmp4.host_cycle_code
AND st.store_cycle_code = tmp4.store_cycle_code
LEFT OUTER JOIN
m_information_category jm
ON jm.version = $124
AND st.information_category_code = jm.information_category_code
AND TO_DATE($125, 'YYYY-MM-DD') BETWEEN jm.apply_start_date AND jm.apply_end_date
INNER JOIN
tmp17 jk
ON tmp4.host_cycle_code = jk.host_cycle_code
AND tmp4.store_cycle_code = jk.store_cycle_code
AND st.information_category_code = jk.information_category_code
)
,
tmp8 as (
SELECT tmp4.host_cycle_code
,tmp4.store_cycle_code
,gt.gondola_number
,gm.information_category_code
,jm.information_category_name
,gt.staff_code
FROM
m_staff_by_gondola_order gt
INNER JOIN
m_number_of_gondola gm
ON gt.original_store_code = gm.original_store_code
AND gt.host_cycle_code = gm.host_cycle_code
AND gt.store_cycle_code = gm.store_cycle_code
AND gt.gondola_number = gm.gondola_number
AND gm.version = $126
AND TO_DATE($127, 'YYYY-MM-DD') BETWEEN gm.apply_start_date AND gm.apply_end_date
AND gm.reading_item_quantity > 0
INNER JOIN
tmp4
ON gt.original_store_code = tmp4.original_store_code
AND gt.host_cycle_code = tmp4.host_cycle_code
AND gt.store_cycle_code = tmp4.store_cycle_code
LEFT OUTER JOIN
m_information_category jm
ON jm.version = $128
AND gm.information_category_code = jm.information_category_code
AND TO_DATE($129, 'YYYY-MM-DD') BETWEEN jm.apply_start_date AND jm.apply_end_date
WHERE ( gm.license_code = '00'
OR gm.license_code IN (
$130
,
$131
,
$132
,
$133
,
$134
,
$135
,
$136
,
$137
,
$138
,
$139
,
$140
,
$141
,
$142
,
$143
,
$144
,
$145
,
$146
,
$147
,
$148
,
$149
)
)
)
,
tmp9 as (
SELECT tmp8.host_cycle_code
,tmp8.store_cycle_code
,tmp8.gondola_number
,tmp8.information_category_code
,tmp8.staff_code
,tt.information_category_code AS display_input_information_category_code
FROM
tmp8
LEFT OUTER JOIN
tmp6
ON tmp8.host_cycle_code = tmp6.host_cycle_code
AND tmp8.store_cycle_code = tmp6.store_cycle_code
AND tmp8.information_category_code = tmp6.information_category_code
AND tmp8.staff_code = tmp6.staff_code
LEFT OUTER JOIN
m_specific_display_order tt
ON tmp8.gondola_number = tt.display_gondola
AND tmp8.information_category_code = tt.information_category_code
AND tt.original_store_code = $150
WHERE tmp6.information_category_code IS NULL)
,
tmp10 as (
SELECT tmp6.host_cycle_code
,tmp6.store_cycle_code
,tmp6.information_category_code
,tmp6.staff_code
FROM
tmp6
UNION
SELECT tmp7.host_cycle_code
,tmp7.store_cycle_code
,tmp7.information_category_code
,tmp7.staff_code
FROM
tmp7
UNION
SELECT DISTINCT
tmp9.host_cycle_code
,tmp9.store_cycle_code
,tmp9.information_category_code
,tmp9.staff_code
FROM tmp9
WHERE
tmp9.display_input_information_category_code IS NOT NULL)
,
tmp11 as(
SELECT DISTINCT
tmp9.host_cycle_code
,tmp9.store_cycle_code
,tmp9.staff_code
,tj.item_code
FROM
tmp9
INNER JOIN
m_item sm
ON tmp9.host_cycle_code = sm.host_cycle_code
AND tmp9.store_cycle_code = sm.store_cycle_code
AND tmp9.information_category_code = sm.information_category_code
AND sm.version = $151
AND TO_DATE($152, 'YYYY-MM-DD') BETWEEN sm.apply_start_date AND sm.apply_end_date
INNER JOIN
m_pattern pt
ON sm.pattern_type = pt.pattern_type
AND sm.pattern_code = pt.pattern_code
AND pt.version = $153
AND pt.original_store_code = $154
AND TO_DATE($155, 'YYYY-MM-DD') BETWEEN pt.apply_start_date AND pt.apply_end_date
INNER JOIN
m_display tj
ON sm.item_code = tj.item_code
AND tj.original_store_code = $156
AND tmp9.gondola_number = tj.display_gondola
WHERE tmp9.display_input_information_category_code IS NULL)
,
tmp12 as(
SELECT tmp10.host_cycle_code
,tmp10.store_cycle_code
,tmp10.staff_code
,SUM(hz1.input_packed_quantity) AS order_total_quantity
,SUM(hz1.input_amount) AS order_total_amount
,MAX(hz1.order_done_display_flag) AS order_situation
FROM
s_order hz1
INNER JOIN
tmp10
ON hz1.host_cycle_code = tmp10.host_cycle_code
AND hz1.store_cycle_code = tmp10.store_cycle_code
AND hz1.information_category_code = tmp10.information_category_code
AND hz1.original_store_code = '181013'
AND hz1.order_date = TO_DATE($157, 'YYYY-MM-DD')
GROUP BY
tmp10.host_cycle_code, tmp10.store_cycle_code, tmp10.staff_code)
,
tmp13 as (
SELECT tmp11.host_cycle_code
,tmp11.store_cycle_code
,tmp11.staff_code
,SUM(hz2.input_packed_quantity) AS order_total_quantity
,SUM(hz2.input_amount) AS order_total_amount
,MAX(hz2.order_done_display_flag) AS order_situation
FROM
s_order hz2
INNER JOIN
tmp11
ON hz2.item_code = tmp11.item_code
AND hz2.original_store_code = '181013'
AND hz2.order_date = TO_DATE($158, 'YYYY-MM-DD')
GROUP BY
tmp11.host_cycle_code, tmp11.store_cycle_code, tmp11.staff_code)
,
tmp18 as (
SELECT tmp6.host_cycle_code
,tmp6.store_cycle_code
,tmp6.information_category_code
,tmp6.staff_code
FROM
tmp6
UNION
SELECT tmp7.host_cycle_code
,tmp7.store_cycle_code
,tmp7.information_category_code
,tmp7.staff_code
FROM
tmp7
UNION
SELECT DISTINCT
tmp9.host_cycle_code
,tmp9.store_cycle_code
,tmp9.information_category_code
,tmp9.staff_code
FROM tmp9)
,
tmp14 as (
SELECT DISTINCT
tmp4.host_cycle_code
,tmp4.store_cycle_code
FROM
tmp4
WHERE
NOT EXISTS (SELECT 1
FROM tmp18
WHERE tmp18.host_cycle_code = tmp4.host_cycle_code
AND tmp18.store_cycle_code = tmp4.store_cycle_code))
,
tmp15 as (
SELECT
tmp14.host_cycle_code
,tmp14.store_cycle_code
,NULL AS staff_code
,SUM(hz3.input_packed_quantity) AS order_total_quantity
,SUM(hz3.input_amount) AS order_total_amount
,MAX(hz3.order_done_display_flag) AS order_situation
FROM
s_order hz3
INNER JOIN
tmp14
ON hz3.host_cycle_code = tmp14.host_cycle_code
AND hz3.store_cycle_code = tmp14.store_cycle_code
AND hz3.original_store_code = '181013'
AND hz3.order_date = TO_DATE($159, 'YYYY-MM-DD')
GROUP BY
tmp14.host_cycle_code,tmp14.store_cycle_code)
,
tmp16 as (
SELECT
tmp16_tmp.host_cycle_code
, tmp16_tmp.store_cycle_code
, tmp16_tmp.staff_code
, SUM(tmp16_tmp.order_total_quantity) as order_total_quantity
, SUM(tmp16_tmp.order_total_amount) as order_total_amount
, MAX(tmp16_tmp.order_situation) as order_situation
FROM
(
SELECT
tmp12.host_cycle_code
, tmp12.store_cycle_code
, tmp12.staff_code
, tmp12.order_total_quantity
, tmp12.order_total_amount
, tmp12.order_situation
FROM
tmp12
UNION
SELECT
tmp13.host_cycle_code
, tmp13.store_cycle_code
, tmp13.staff_code
, tmp13.order_total_quantity
, tmp13.order_total_amount
, tmp13.order_situation
FROM
tmp13
UNION
SELECT
tmp15.host_cycle_code
, tmp15.store_cycle_code
, tmp15.staff_code
, tmp15.order_total_quantity
, tmp15.order_total_amount
, tmp15.order_situation
FROM
tmp15
) AS tmp16_tmp
GROUP BY
tmp16_tmp.host_cycle_code
, tmp16_tmp.store_cycle_code
, tmp16_tmp.staff_code
)
SELECT DISTINCT
tmp18.staff_code AS order_staff_code
,tmp4.store_cycle_name AS order_group_name
,tmp4.host_cycle_code
,tmp4.store_cycle_code
,COALESCE(tmp16.order_situation, '0') AS order_situation
,COALESCE(tmp16.order_total_quantity, 0) AS order_total_quantity
,COALESCE(tmp16.order_total_amount, 0) AS order_total_amount
,(CASE WHEN rz.order_way = '1' AND tmp5.setting_order_action_flag = '1' THEN '1'
WHEN rz.order_way = '2' AND tmp5.ai_order_action_flag = '1' THEN '2'
ELSE '0'
END
) AS order_way_first
,('1' || COALESCE(tmp5.setting_order_action_flag, '0')
|| COALESCE(tmp5.ai_order_action_flag, '0')) AS selection_permission_order_way
,CASE
WHEN tmp4.order_method_type = '07' THEN '0'
ELSE COALESCE(rz.displayorder, '0')
END AS sort_method_first
,('1' || '1' || '1') AS selection_permission_sort_method
,(CASE WHEN rz.reading_method IS NOT NULL
AND rz.reading_method = '0'
AND ici_tmp.information_category_code_information_list IS NOT NULL
THEN '0'
WHEN rz.reading_method IS NOT NULL
AND rz.reading_method = '1'
AND icg_tmp.gondola_number_list IS NOT NULL
THEN '1'
WHEN yt.reading_method IS NOT NULL
THEN (CASE WHEN yt.reading_method = '1'
THEN '0'
WHEN yt.reading_method = '2'
THEN '1'
END)
WHEN ici_tmp.information_category_code_information_list IS NOT NULL
AND icg_tmp.gondola_number_list IS NULL
THEN '0'
WHEN ici_tmp.information_category_code_information_list IS NULL
AND icg_tmp.gondola_number_list IS NOT NULL
THEN '1'
WHEN tmp4.order_method_type IN ('04', '05', '06', '09') THEN '1'
ELSE '0' END) AS reading_method_first
,('1') || (CASE
WHEN tmp4.order_method_type IN ('04', '05', '06', '09')
THEN '1'
ELSE '0' END) AS selection_permission_reading_method
,ici_tmp.information_category_code_information_list
,ici_tmp.information_category_name_information_list
,ici_tmp.reading_number_information_list
,ici_tmp.setting_order_information_list
,icg_tmp.information_category_code_gondola_list
,icg_tmp.information_category_name_gondola_list
,icg_tmp.information_category_display_order_list
,icg_tmp.gondola_number_list
,icg_tmp.other_information_category_flag_list
,icg_tmp.unregistered_information_category_flag_list
,tmp4.order_method_type
,tmp4.cycle_display_order
FROM
tmp4
LEFT OUTER JOIN
tmp18
ON tmp18.host_cycle_code = tmp4.host_cycle_code
AND tmp18.store_cycle_code = tmp4.store_cycle_code
LEFT OUTER JOIN
m_staff_by_reading_method yt
ON tmp4.host_cycle_code = yt.host_cycle_code
AND tmp4.store_cycle_code = yt.store_cycle_code
AND yt.original_store_code = $160
AND yt.staff_code = tmp18.staff_code
LEFT OUTER JOIN
tmp16
ON tmp4.host_cycle_code = tmp16.host_cycle_code
AND tmp4.store_cycle_code = tmp16.store_cycle_code
AND (CASE WHEN tmp16.staff_code IS NULL THEN tmp18.staff_code IS NULL
ELSE tmp18.staff_code = tmp16.staff_code
END
)
LEFT OUTER JOIN
s_cycle_select_history rz
ON tmp4.host_cycle_code = rz.host_cycle_code
AND tmp4.store_cycle_code = rz.store_cycle_code
AND (
CASE
WHEN
rz.staff_code = '0000000000000' THEN tmp18.staff_code IS NULL
ELSE
tmp18.staff_code = rz.staff_code
END
)
AND rz.original_store_code = $161
AND rz.login_employee_code = $162
LEFT OUTER JOIN
tmp5
ON tmp4.host_cycle_code = tmp5.host_cycle_code
AND tmp4.store_cycle_code = tmp5.store_cycle_code
AND tmp5.original_store_code = $163
LEFT OUTER JOIN
(
SELECT
ici.host_cycle_code
, ici.store_cycle_code
, array_agg(ici.information_category_code) AS information_category_code_information_list
, array_agg(ici.information_category_name) AS information_category_name_information_list
, array_agg(ici.reading_number) AS reading_number_information_list
, array_agg(ici.setting_order) AS setting_order_information_list
, ici.staff_code
FROM (
SELECT
tmp6.host_cycle_code
, tmp6.store_cycle_code
, tmp6.information_category_code
, tmp6.information_category_name
, tmp6.staff_code
, tmp6.reading_number AS reading_number
, tmp6.setting_order AS setting_order
FROM
tmp6
UNION
SELECT DISTINCT
jk.host_cycle_code
, jk.store_cycle_code
, jk.information_category_code
, jm.information_category_name
, t6_tmp.staff_code
, MIN(COALESCE(ik.group_number, ip.group_number)) AS reading_number
, MIN(COALESCE(ik.setting_order, ip.setting_order)) AS setting_order
FROM
tmp17 jk
LEFT OUTER JOIN
m_staff_by_information_order jt
ON jk.host_cycle_code = jt.host_cycle_code
AND jk.store_cycle_code = jt.store_cycle_code
AND jk.information_category_code = jt.information_category_code
AND jt.original_store_code = $164
LEFT OUTER JOIN
m_information_category jm
ON jm.version = $165
AND jk.information_category_code = jm.information_category_code
AND TO_DATE($166, 'YYYY-MM-DD') BETWEEN jm.apply_start_date AND jm.apply_end_date
LEFT OUTER JOIN
(
SELECT DISTINCT
tmp6.host_cycle_code
, tmp6.store_cycle_code
, tmp6.staff_code
FROM
tmp6) t6_tmp
ON jk.host_cycle_code = t6_tmp.host_cycle_code
AND jk.store_cycle_code = t6_tmp.store_cycle_code
INNER JOIN
m_reading_number_by_pattern ip
ON ip.pattern_type = jk.pattern_type
AND ip.pattern_code = jk.pattern_code
AND jk.host_cycle_code = ip.host_cycle_code
AND jk.store_cycle_code = ip.store_cycle_code
AND jk.information_category_code = ip.information_category_code
AND ip.version = $167
AND TO_DATE($168, 'YYYY-MM-DD') BETWEEN ip.apply_start_date AND ip.apply_end_date
LEFT OUTER JOIN
m_reading_number_by_store ik
ON jk.host_cycle_code = ik.host_cycle_code
AND jk.store_cycle_code = ik.store_cycle_code
AND jk.information_category_code = ik.information_category_code
AND ik.original_store_code = $169
AND TO_DATE($170, 'YYYY-MM-DD') BETWEEN ik.apply_start_date AND ik.apply_end_date
WHERE
jt.information_category_code IS NULL
GROUP BY
jk.host_cycle_code,
jk.store_cycle_code,
jk.information_category_code,
jm.information_category_name,
t6_tmp.staff_code
UNION
SELECT DISTINCT
jk.host_cycle_code,
jk.store_cycle_code,
jk.information_category_code,
jm.information_category_name,
tmp18.staff_code,
MIN(COALESCE(ik.group_number, ip.group_number)) AS reading_number,
MIN(COALESCE(ik.setting_order, ip.setting_order)) AS setting_order
FROM
tmp17 jk
INNER JOIN
tmp18
ON
tmp18.host_cycle_code = jk.host_cycle_code
AND tmp18.store_cycle_code = jk.store_cycle_code
LEFT OUTER JOIN
m_information_category jm
ON
jm.version = $171
AND jk.information_category_code = jm.information_category_code
AND TO_DATE($172, 'YYYY-MM-DD') BETWEEN jm.apply_start_date AND jm.apply_end_date
INNER JOIN
m_reading_number_by_pattern ip
ON
ip.pattern_type = jk.pattern_type
AND ip.pattern_code = jk.pattern_code
AND jk.host_cycle_code = ip.host_cycle_code
AND jk.store_cycle_code = ip.store_cycle_code
AND jk.information_category_code = ip.information_category_code
AND ip.version = $173
AND TO_DATE($174, 'YYYY-MM-DD') BETWEEN ip.apply_start_date AND ip.apply_end_date
LEFT OUTER JOIN
m_reading_number_by_store ik
ON
jk.host_cycle_code = ik.host_cycle_code
AND jk.store_cycle_code = ik.store_cycle_code
AND jk.information_category_code = ik.information_category_code
AND ik.original_store_code = $175
AND TO_DATE($176, 'YYYY-MM-DD') BETWEEN ik.apply_start_date AND ik.apply_end_date
LEFT OUTER JOIN
m_staff_by_information_order jt
ON
jt.original_store_code = $177
AND jt.host_cycle_code = jk.host_cycle_code
AND jt.store_cycle_code = jk.store_cycle_code
AND jt.information_category_code = jk.information_category_code
WHERE
jt.staff_code IS NULL
GROUP BY
jk.host_cycle_code,
jk.store_cycle_code,
jk.information_category_code,
jm.information_category_name,
tmp18.staff_code
) ici
GROUP BY
ici.host_cycle_code
, ici.store_cycle_code
, ici.staff_code
) ici_tmp
ON tmp4.host_cycle_code = ici_tmp.host_cycle_code
AND tmp4.store_cycle_code = ici_tmp.store_cycle_code
AND
(
tmp18.staff_code = ici_tmp.staff_code
OR
(tmp18.staff_code IS NULL AND ici_tmp.staff_code IS NULL)
)
LEFT OUTER JOIN (
SELECT icg.host_cycle_code
, icg.store_cycle_code
, array_agg(icg.information_category_code) AS information_category_code_gondola_list
, array_agg(icg.information_category_name) AS information_category_name_gondola_list
, icg.staff_code
, array_agg(icg.information_category_display_order) AS information_category_display_order_list
, array_agg(icg.gondola_number) AS gondola_number_list
, array_agg(icg.other_information_category_flag) AS other_information_category_flag_list
, array_agg(icg.unregistered_information_category_flag) AS unregistered_information_category_flag_list
FROM (
SELECT
tmp8.host_cycle_code
, tmp8.store_cycle_code
, tmp8.information_category_code
, tmp8.information_category_name
, 0 AS information_category_display_order
, tmp8.staff_code
, tmp8.gondola_number AS gondola_number
, '0' AS other_information_category_flag
, '0' AS unregistered_information_category_flag
FROM
tmp8
UNION
SELECT
tmp7.host_cycle_code
, tmp7.store_cycle_code
, tmp7.information_category_code
, tmp7.information_category_name
, tmp7.information_category_display_order AS information_category_display_order
, tmp7.staff_code
, 0 AS gondola_number
, '1' AS other_information_category_flag
, CASE WHEN
gk.information_category_code IS NULL
THEN
'1'
ELSE
'0'
END AS unregistered_information_category_flag
FROM
tmp7
LEFT OUTER JOIN
m_number_of_gondola gk
ON
gk.version = $178
AND gk.original_store_code = $179
AND TO_DATE($180, 'YYYY-MM-DD') BETWEEN gk.apply_start_date AND gk.apply_end_date
AND tmp7.host_cycle_code = gk.host_cycle_code
AND tmp7.store_cycle_code = gk.store_cycle_code
AND tmp7.information_category_code = gk.information_category_code
AND ( gk.license_code = '00'
OR gk.license_code IN (
$181
,
$182
,
$183
,
$184
,
$185
,
$186
,
$187
,
$188
,
$189
,
$190
,
$191
,
$192
,
$193
,
$194
,
$195
,
$196
,
$197
,
$198
,
$199
,
$200
)
)
AND gk.reading_item_quantity > 0
UNION
SELECT DISTINCT
gk.host_cycle_code
, gk.store_cycle_code
, gk.information_category_code
, jm.information_category_name
, jm.information_category_display_order AS information_category_display_order
, t7_t8_tmp1.staff_code
, gk.gondola_number AS gondola_number
, '0' AS other_information_category_flag
, '0' AS unregistered_information_category_flag
FROM
m_number_of_gondola gk
LEFT OUTER JOIN
m_staff_by_gondola_order gt
ON gk.host_cycle_code = gt.host_cycle_code
AND gk.store_cycle_code = gt.store_cycle_code
AND gk.gondola_number = gt.gondola_number
AND gt.original_store_code = $201
LEFT OUTER JOIN
m_staff_by_other_order st
ON gk.host_cycle_code = st.host_cycle_code
AND gk.store_cycle_code = st.store_cycle_code
AND gk.information_category_code = st.information_category_code
AND st.original_store_code = $202
LEFT OUTER JOIN
m_information_category jm
ON jm.version = $203
AND gk.information_category_code = jm.information_category_code
AND TO_DATE($204, 'YYYY-MM-DD') BETWEEN jm.apply_start_date AND jm.apply_end_date
LEFT OUTER JOIN (
SELECT DISTINCT
tmp7.host_cycle_code
, tmp7.store_cycle_code
, tmp7.staff_code
FROM
tmp7
UNION
SELECT DISTINCT
tmp8.host_cycle_code
, tmp8.store_cycle_code
, tmp8.staff_code
FROM
tmp8
) t7_t8_tmp1
ON gk.host_cycle_code = t7_t8_tmp1.host_cycle_code
AND gk.store_cycle_code = t7_t8_tmp1.store_cycle_code
WHERE
gk.version = $205
AND gk.original_store_code = $206
AND TO_DATE($207, 'YYYY-MM-DD') BETWEEN gk.apply_start_date AND
gk.apply_end_date
AND ( gk.license_code = '00'
OR gk.license_code IN (
$208
,
$209
,
$210
,
$211
,
$212
,
$213
,
$214
,
$215
,
$216
,
$217
,
$218
,
$219
,
$220
,
$221
,
$222
,
$223
,
$224
,
$225
,
$226
,
$227
)
)
AND gk.reading_item_quantity > 0
AND gt.gondola_number IS NULL
AND st.information_category_code IS NULL
UNION
SELECT DISTINCT
jk.host_cycle_code
, jk.store_cycle_code
, jk.information_category_code
, jm.information_category_name
, jm.information_category_display_order AS information_category_display_order
, t7_t8_tmp2.staff_code
, 0 AS gondola_number
, '0' AS other_information_category_flag
, '1' AS unregistered_information_category_flag
FROM
tmp17 jk
LEFT OUTER JOIN
m_number_of_gondola gk
ON jk.host_cycle_code = gk.host_cycle_code
AND jk.store_cycle_code = gk.store_cycle_code
AND jk.information_category_code = gk.information_category_code
AND gk.version = $228
AND gk.original_store_code = $229
AND TO_DATE($230, 'YYYY-MM-DD') BETWEEN gk.apply_start_date AND
gk.apply_end_date
AND ( gk.license_code = '00'
OR gk.license_code IN (
$231
,
$232
,
$233
,
$234
,
$235
,
$236
,
$237
,
$238
,
$239
,
$240
,
$241
,
$242
,
$243
,
$244
,
$245
,
$246
,
$247
,
$248
,
$249
,
$250
)
)
AND gk.reading_item_quantity > 0
LEFT OUTER JOIN
m_staff_by_other_order st
ON jk.host_cycle_code = st.host_cycle_code
AND jk.store_cycle_code = st.store_cycle_code
AND jk.information_category_code = st.information_category_code
AND st.original_store_code = $251
LEFT OUTER JOIN
m_information_category jm
ON jm.version = $252
AND jk.information_category_code = jm.information_category_code
AND TO_DATE($253, 'YYYY-MM-DD') BETWEEN jm.apply_start_date AND jm.apply_end_date
LEFT OUTER JOIN (
SELECT DISTINCT
tmp7.host_cycle_code
, tmp7.store_cycle_code
, tmp7.staff_code
FROM
tmp7
UNION
SELECT DISTINCT
tmp8.host_cycle_code
, tmp8.store_cycle_code
, tmp8.staff_code
FROM
tmp8) t7_t8_tmp2
ON jk.host_cycle_code = t7_t8_tmp2.host_cycle_code
AND jk.store_cycle_code = t7_t8_tmp2.store_cycle_code
WHERE
gk.information_category_code IS NULL
AND st.information_category_code IS NULL
UNION
SELECT DISTINCT
gk.host_cycle_code,
gk.store_cycle_code,
gk.information_category_code,
jm.information_category_name,
jm.information_category_display_order,
tmp18.staff_code,
gk.gondola_number,
'0' AS other_information_category_flag,
'0' AS unregistered_information_category_flag
FROM
m_number_of_gondola gk
INNER JOIN
tmp18
ON
tmp18.host_cycle_code = gk.host_cycle_code
AND tmp18.store_cycle_code = gk.store_cycle_code
LEFT OUTER JOIN
m_staff_by_gondola_order gt
ON
gk.host_cycle_code = gt.host_cycle_code
AND gk.store_cycle_code = gt.store_cycle_code
AND gk.gondola_number = gt.gondola_number
AND gt.original_store_code = $254
LEFT OUTER JOIN
m_staff_by_other_order st
ON
gk.host_cycle_code = st.host_cycle_code
AND gk.store_cycle_code = st.store_cycle_code
AND gk.information_category_code = st.information_category_code
AND st.original_store_code = $255
LEFT OUTER JOIN
m_information_category jm
ON
jm.version = $256
AND gk.information_category_code = jm.information_category_code
AND TO_DATE($257, 'YYYY-MM-DD') BETWEEN jm.apply_start_date AND jm.apply_end_date
WHERE
gk.version = $258
AND gk.original_store_code = $259
AND TO_DATE($260, 'YYYY-MM-DD') BETWEEN gk.apply_start_date AND gk.apply_end_date
AND ( gk.license_code = '00'
OR gk.license_code IN (
$261
,
$262
,
$263
,
$264
,
$265
,
$266
,
$267
,
$268
,
$269
,
$270
,
$271
,
$272
,
$273
,
$274
,
$275
,
$276
,
$277
,
$278
,
$279
,
$280
)
)
AND gk.reading_item_quantity > 0
AND gt.gondola_number IS NULL
AND st.information_category_code IS NULL
UNION
SELECT DISTINCT
jk.host_cycle_code,
jk.store_cycle_code,
jk.information_category_code,
jm.information_category_name,
jm.information_category_display_order,
tmp18.staff_code,
0 AS gondola_number,
'0' AS other_information_category_flag,
'1' AS unregistered_information_category_flag
FROM
tmp17 jk
INNER JOIN
tmp18
ON
tmp18.host_cycle_code = jk.host_cycle_code
AND tmp18.store_cycle_code = jk.store_cycle_code
LEFT OUTER JOIN
m_number_of_gondola gk
ON
jk.host_cycle_code = gk.host_cycle_code
AND jk.store_cycle_code = gk.store_cycle_code
AND jk.information_category_code = gk.information_category_code
AND gk.version = $281
AND gk.original_store_code = $282
AND TO_DATE($283, 'YYYY-MM-DD') BETWEEN gk.apply_start_date AND gk.apply_end_date
AND ( gk.license_code = '00'
OR gk.license_code IN (
$284
,
$285
,
$286
,
$287
,
$288
,
$289
,
$290
,
$291
,
$292
,
$293
,
$294
,
$295
,
$296
,
$297
,
$298
,
$299
,
$300
,
$301
,
$302
,
$303
)
)
AND gk.reading_item_quantity > 0
LEFT OUTER JOIN
m_staff_by_other_order st
ON
jk.host_cycle_code = st.host_cycle_code
AND jk.store_cycle_code = st.store_cycle_code
AND jk.information_category_code = st.information_category_code
AND st.original_store_code = $304
LEFT OUTER JOIN
m_information_category jm
ON
jm.version = $305
AND jk.information_category_code = jm.information_category_code
AND TO_DATE($306, 'YYYY-MM-DD') BETWEEN jm.apply_start_date AND jm.apply_end_date
WHERE
gk.information_category_code IS NULL
AND st.information_category_code IS NULL
) icg
GROUP BY
icg.host_cycle_code
, icg.store_cycle_code
, icg.staff_code
) icg_tmp
ON tmp4.host_cycle_code = icg_tmp.host_cycle_code
AND tmp4.store_cycle_code = icg_tmp.store_cycle_code
AND tmp4.order_method_type IN ('04', '05', '06', '09')
AND
(
tmp18.staff_code = icg_tmp.staff_code
OR
(tmp18.staff_code IS NULL AND icg_tmp.staff_code IS NULL)
)
ORDER BY
tmp18.staff_code ASC, tmp4.cycle_display_order ASC 能不能合并tmp17
最新发布