JJ_MATCH_MONTH计算hklj出货单对应的入库明细

CREATE OR REPLACE PROCEDURE JJ_MATCH_MONTH(
p_ym IN VARCHAR2, -- 'YYYY-MM'
p_item IN VARCHAR2 DEFAULT NULL, -- NULL=整月；指定则只跑该料号
p_cnt IN NUMBER DEFAULT 15 -- 该料号入库单据数上限（2^cnt 限流）
) IS
v_ym VARCHAR2(7) := p_ym;
v_item VARCHAR2(60) := p_item;
v_it VARCHAR2(60);
v_err VARCHAR2(4000);
BEGIN
DELETE FROM tmp_result WHERE 年月 = v_ym AND (v_item IS NULL OR 料号 = v_item);
DELETE FROM tmp_result_log WHERE 年月 = v_ym AND (v_item IS NULL OR 料号 = v_item);
COMMIT;

FOR it IN (
SELECT DISTINCT 料号 FROM (
SELECT ogb.ogb04 AS 料号
FROM ogb_file ogb, oga_file oga
WHERE ogb.ogb01 = oga.oga01
AND oga.oga09 = '2' AND oga.ogaconf <> 'X' AND oga.ogapost = 'Y'
AND oga.oga04 NOT LIKE '%LJ%' AND oga.oga04 NOT LIKE '%L-%'
AND TO_CHAR(oga.oga02,'YYYY-MM') = v_ym
AND (v_item IS NULL OR ogb.ogb04 = v_item)
UNION
SELECT rvv.rvv31 AS 料号
FROM rvv_file rvv, rvu_file rvu
WHERE rvv.rvv01 = rvu.rvu01
AND rvu.rvu08 = 'REG' AND rvu.rvu20 = 'N' AND rvu.rvuconf = 'Y'
AND rvu.rvu04 NOT LIKE '%LJ%'
AND (rvu.rvu04 NOT LIKE '%L-%' OR rvu.rvu04 = 'L-CA02')
AND rvv.rvv31 NOT LIKE 'X%'
AND TO_CHAR(rvu.rvu03,'YYYY-MM') = v_ym
AND (v_item IS NULL OR rvv.rvv31 = v_item)
)
) LOOP
v_it := it.料号;
BEGIN
INSERT INTO tmp_result (
年月, 出货日期, 料号, 出货币别, 出货客户, 客户简称,
出货数量, 出货单价, 出货未税金额, 出货含税金额,
本月平均单价, 成本金额, 出货明细,
入库厂商, 厂商简称, 入库数量, 入库未税单价, 入库含税单价,
未税金额, 含税金额, 入库明细, 数量差异)
WITH recv0 AS (
SELECT TO_CHAR(rvu.rvu03,'YYYY-MM') AS 年月,
rvv.rvv31 AS 料号,
rvv.rvv01 AS 入库单号,
MAX(rvu.rvu04) AS 入库厂商,
MAX(rvu.rvu05) AS 厂商简称,
SUM(rvv.rvv17) AS 入库数量,
SUM(rvv.rvv39) AS 入库未税金额,
SUM(rvv.rvv39t) AS 入库含税金额
FROM rvv_file rvv
JOIN rvu_file rvu ON rvv.rvv01 = rvu.rvu01
WHERE rvu.rvu08 = 'REG' AND rvu.rvu20 = 'N' AND rvu.rvuconf = 'Y'
AND rvu.rvu04 NOT LIKE '%LJ%'
AND (rvu.rvu04 NOT LIKE '%L-%' OR rvu.rvu04 = 'L-CA02')
AND rvv.rvv31 NOT LIKE 'X%'
AND TO_CHAR(rvu.rvu03,'YYYY-MM') = v_ym
AND rvv.rvv31 = v_it
GROUP BY TO_CHAR(rvu.rvu03,'YYYY-MM'), rvv.rvv31, rvv.rvv01
),
line0 AS (
SELECT TO_CHAR(rvu.rvu03,'YYYY-MM') AS 年月,
rvv.rvv31 AS 料号, rvv.rvv01 AS 入库单号, rvv.rvv02 AS 项次,
rvu.rvu04 AS 入库厂商, rvu.rvu05 AS 厂商简称,
rvv.rvv17 AS 入库数量,
rvv.rvv38 AS 入库未税单价, rvv.rvv38t AS 入库含税单价,
rvv.rvv39 AS 入库未税金额, rvv.rvv39t AS 入库含税金额
FROM rvv_file rvv
JOIN rvu_file rvu ON rvv.rvv01 = rvu.rvu01
WHERE rvu.rvu08 = 'REG' AND rvu.rvu20 = 'N' AND rvu.rvuconf = 'Y'
AND rvu.rvu04 NOT LIKE '%LJ%'
AND (rvu.rvu04 NOT LIKE '%L-%' OR rvu.rvu04 = 'L-CA02')
AND rvv.rvv31 NOT LIKE 'X%'
AND TO_CHAR(rvu.rvu03,'YYYY-MM') = v_ym
AND rvv.rvv31 = v_it
),
ship0 AS (
SELECT TO_CHAR(oga.oga02,'YYYY-MM') AS 年月,
MAX(oga.oga02) AS 出货日期,
ogb.ogb04 AS 料号,
oga.oga04 AS 出货客户,
MAX(occ.occ02) AS 客户简称,
MAX(oga.oga23) AS 出货币别,
ogb.ogb13 AS 出货单价,
SUM(ogb.ogb12) AS 出货数量,
SUM(ogb.ogb14) AS 出货未税金额,
SUM(ogb.ogb14t) AS 出货含税金额
FROM ogb_file ogb
JOIN oga_file oga ON ogb.ogb01 = oga.oga01
LEFT JOIN occ_file occ ON occ.occ01 = oga.oga04
WHERE oga.oga09 = '2' AND oga.ogaconf <> 'X' AND oga.ogapost = 'Y'
AND oga.oga04 NOT LIKE '%LJ%' AND oga.oga04 NOT LIKE '%L-%'
AND TO_CHAR(oga.oga02,'YYYY-MM') = v_ym
AND ogb.ogb04 = v_it
GROUP BY TO_CHAR(oga.oga02,'YYYY-MM'), ogb.ogb04, oga.oga04, ogb.ogb13
),
ship AS (
SELECT 年月, MAX(出货日期) AS 出货日期, 料号,
出货客户, 客户简称, 出货币别, 出货单价,
SUM(出货数量) AS 出货数量,
SUM(出货未税金额) AS 出货未税金额,
SUM(出货含税金额) AS 出货含税金额,
ROW_NUMBER() OVER (PARTITION BY 年月, 料号
ORDER BY 出货客户, 出货单价) AS g
FROM ship0
GROUP BY 年月, 料号, 出货客户, 客户简称, 出货币别, 出货单价
),
ship_max AS (
SELECT 年月, 料号,
MAX(出货数量) AS maxqty,
COUNT() AS gn,
SUM(出货数量) AS totqty
FROM ship GROUP BY 年月, 料号
),
pool0 AS (
SELECT p.年月, p.料号, p.入库单号, p.入库厂商, p.厂商简称,
p.入库数量, p.入库未税金额, p.入库含税金额,
ROW_NUMBER() OVER (PARTITION BY p.年月, p.料号
ORDER BY p.入库单号) - 1 AS bit,
COUNT() OVER (PARTITION BY p.年月, p.料号) AS cnt
FROM recv0 p
JOIN ship_max m ON m.年月 = p.年月 AND m.料号 = p.料号
WHERE p.入库数量 <= m.maxqty
),
pool AS (SELECT * FROM pool0 WHERE cnt <= p_cnt),
grp AS (SELECT 年月, 料号, MAX(cnt) AS cnt FROM pool GROUP BY 年月, 料号),
lim AS (SELECT NVL(MAX(cnt),0) AS maxcnt FROM grp),
nums(s) AS (
SELECT 0 FROM DUAL
UNION ALL
SELECT n.s + 1 FROM nums n, lim l WHERE n.s < POWER(2, l.maxcnt) - 1
),
subset AS (
SELECT p.年月, p.料号, n.s AS s, SUM(p.入库数量) AS 数量
FROM nums n
JOIN pool p ON BITAND(n.s, POWER(2, p.bit)) > 0
JOIN grp g ON g.年月 = p.年月 AND g.料号 = p.料号
WHERE n.s > 0 AND n.s < POWER(2, g.cnt)
GROUP BY p.年月, p.料号, n.s
HAVING SUM(p.入库数量) IN (SELECT s2.出货数量 FROM ship s2
WHERE s2.年月 = p.年月 AND s2.料号 = p.料号)
),
alloc(年月, 料号, g, s, used_mask, path) AS (
SELECT m.年月, m.料号, 0, 0, 0, CAST('0' AS VARCHAR2(4000))
FROM ship_max m
UNION ALL
SELECT a.年月, a.料号, a.g + 1, su.s, a.used_mask + su.s,
a.path || '-' || TO_CHAR(su.s)
FROM alloc a
JOIN subset su ON su.年月 = a.年月 AND su.料号 = a.料号
AND BITAND(a.used_mask, su.s) = 0
JOIN ship s3 ON s3.年月 = a.年月 AND s3.料号 = a.料号
AND s3.g = a.g + 1 AND s3.出货数量 = su.数量
),
win AS (
SELECT * FROM (
SELECT a.年月, a.料号, a.path,
ROW_NUMBER() OVER (PARTITION BY a.年月, a.料号
ORDER BY a.path) AS rn
FROM alloc a
JOIN ship_max m ON m.年月 = a.年月 AND m.料号 = a.料号 AND a.g = m.gn
) WHERE rn = 1
),
alloc_one AS (
SELECT a.年月, a.料号, a.g, a.s
FROM alloc a
JOIN win w ON w.年月 = a.年月 AND w.料号 = a.料号 AND w.path = a.path
WHERE a.g > 0
),
alloc_dtl AS (
SELECT ao.年月, ao.料号, ao.g,
p.入库单号, p.入库数量, p.入库厂商, p.厂商简称,
p.入库未税金额, p.入库含税金额
FROM alloc_one ao
JOIN pool p ON p.年月 = ao.年月 AND p.料号 = ao.料号
AND BITAND(ao.s, POWER(2, p.bit)) > 0
),
recv_base AS (
SELECT d.年月, d.料号, d.g,
MAX(d.入库厂商) AS 入库厂商,
MAX(d.厂商简称) AS 厂商简称,
SUM(d.入库数量) AS 入库数量,
SUM(d.入库未税金额) AS 入库未税金额,
SUM(d.入库含税金额) AS 入库含税金额,
LISTAGG(l.入库单号 || '-' || TO_CHAR(l.项次) || '(' ||
TO_CHAR(l.入库数量) || ') ', ' ')
WITHIN GROUP (ORDER BY l.入库单号, l.项次) AS 入库明细
FROM alloc_dtl d
JOIN line0 l ON l.年月 = d.年月 AND l.料号 = d.料号
AND l.入库单号 = d.入库单号
GROUP BY d.年月, d.料号, d.g
),
recv AS (
SELECT rb.年月, rb.料号, rb.g, rb.入库厂商, rb.厂商简称,
rb.入库数量, rb.入库未税金额, rb.入库含税金额, rb.入库明细,
CASE WHEN rb.入库数量 <> 0
THEN rb.入库未税金额 / rb.入库数量 ELSE 0 END AS 入库未税单价,
CASE WHEN rb.入库数量 <> 0
THEN rb.入库含税金额 / rb.入库数量 ELSE 0 END AS 入库含税单价
FROM recv_base rb
),
recv_all AS (
SELECT p.年月, p.料号,
MAX(p.入库厂商) AS 入库厂商,
MAX(p.厂商简称) AS 厂商简称,
SUM(p.入库数量) AS 入库数量,
SUM(p.入库未税金额) AS 入库未税金额,
SUM(p.入库含税金额) AS 入库含税金额,
CASE WHEN SUM(p.入库数量) <> 0
THEN SUM(p.入库未税金额)/SUM(p.入库数量) ELSE 0 END AS 入库未税单价,
CASE WHEN SUM(p.入库数量) <> 0
THEN SUM(p.入库含税金额)/SUM(p.入库数量) ELSE 0 END AS 入库含税单价,
LISTAGG(l.入库单号 || '-' || TO_CHAR(l.项次) || '(' ||
TO_CHAR(l.入库数量) || ') ', ' ')
WITHIN GROUP (ORDER BY l.入库单号, l.项次) AS 入库明细
FROM recv0 p
LEFT JOIN line0 l ON l.年月 = p.年月 AND l.料号 = p.料号
AND l.入库单号 = p.入库单号
GROUP BY p.年月, p.料号
),
ship_dtl AS (
SELECT t.年月, t.料号, t.出货客户, t.出货单价,
LISTAGG(t.出货单号 || '-' || TO_CHAR(t.项次) || '(' ||
TO_CHAR(t.出货数量) || ') ', ' ')
WITHIN GROUP (ORDER BY t.出货单号, t.项次) AS 出货明细
FROM (
SELECT TO_CHAR(oga.oga02,'YYYY-MM') AS 年月,
ogb.ogb04 AS 料号, oga.oga04 AS 出货客户,
ogb.ogb13 AS 出货单价, ogb.ogb01 AS 出货单号,
ogb.ogb03 AS 项次, ogb.ogb12 AS 出货数量
FROM ogb_file ogb
JOIN oga_file oga ON ogb.ogb01 = oga.oga01
WHERE oga.oga09 = '2' AND oga.ogaconf <> 'X' AND oga.ogapost = 'Y'
AND oga.oga04 NOT LIKE '%LJ%' AND oga.oga04 NOT LIKE '%L-%'
AND TO_CHAR(oga.oga02,'YYYY-MM') = v_ym
AND ogb.ogb04 = v_it
) t
GROUP BY t.年月, t.料号, t.出货客户, t.出货单价
),
cost0 AS (
SELECT TRIM(ccc01) AS 料号,
TO_CHAR(ccc02) || '-' || LPAD(TO_CHAR(ccc03),2,'0') AS 年月,
MAX(ccc23) AS 本月平均单价
FROM ccc_file
WHERE ccc02 = TO_NUMBER(SUBSTR(v_ym,1,4))
AND ccc03 = TO_NUMBER(SUBSTR(v_ym,6,2))
AND TRIM(ccc01) = v_it
GROUP BY TRIM(ccc01), TO_CHAR(ccc02) || '-' || LPAD(TO_CHAR(ccc03),2,'0')
)
SELECT s.年月,
s.出货日期,
s.料号,
s.出货币别,
s.出货客户,
s.客户简称,
s.出货数量,
s.出货单价,
s.出货未税金额,
s.出货含税金额,
NVL(c.本月平均单价,0) AS 本月平均单价,
NVL(c.本月平均单价,0) * s.出货数量 AS 成本金额,
NVL(sd.出货明细,'') AS 出货明细,
NVL(r.入库厂商, ra.入库厂商) AS 入库厂商,
NVL(r.厂商简称, ra.厂商简称) AS 厂商简称,
NVL(r.入库数量, NVL(ra.入库数量,0)) AS 入库数量,
NVL(r.入库未税单价, NVL(ra.入库未税单价,0)) AS 入库未税单价,
NVL(r.入库含税单价, NVL(ra.入库含税单价,0)) AS 入库含税单价,
NVL(r.入库未税金额, NVL(ra.入库未税金额,0)) AS 未税金额,
NVL(r.入库含税金额, NVL(ra.入库含税金额,0)) AS 含税金额,
NVL(r.入库明细, NVL(ra.入库明细,'')) AS 入库明细,
NVL(r.入库数量, NVL(ra.入库数量,0)) - s.出货数量 AS 数量差异
FROM ship s
LEFT JOIN recv r ON r.年月 = s.年月 AND r.料号 = s.料号 AND r.g = s.g
LEFT JOIN recv_all ra ON ra.年月 = s.年月 AND ra.料号 = s.料号
LEFT JOIN cost0 c ON c.年月 = s.年月 AND c.料号 = s.料号
LEFT JOIN ship_dtl sd ON sd.年月 = s.年月 AND sd.料号 = s.料号
AND sd.出货客户 = s.出货客户
AND sd.出货单价 = s.出货单价;
COMMIT;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
v_err := SQLERRM;
INSERT INTO tmp_result_log(年月, 料号, 错误讯息) VALUES (v_ym, v_it, v_err);
COMMIT;
END;
END LOOP;
END JJ_MATCH_MONTH;

-- 1. HKLJ 连线，全选上面这段执行 → 应显示 Procedure created.

-- 2. 确认 VALID
SELECT status FROM all_objects
WHERE owner='HKLJ' AND object_name='JJ_MATCH_MONTH';

-- 3. 重算 9 月
BEGIN hklj.JJ_MATCH_MONTH('2026-09'); END;
/

-- 4. 验证
SELECT COUNT(*) FROM hklj.tmp_result WHERE 年月='2026-09';
SELECT * FROM hklj.tmp_result_log WHERE 年月='2026-09';