[code=SQL]
CREATE OR REPLACE PROCEDURE CHINA.sp_make_ap030_temp (
in_start_day IN DATE, -- 起始日
in_end_day IN DATE, --结束日
in_so_id IN NUMBER, --电视台,0为全部，单独计算写入SO_ID
in_etr_id IN VARCHAR2 --生成者ID
)
IS
BEGIN
MERGE INTO tb_ap030 p
USING (SELECT a.good_id, a.so_id, a.form_fr_date, a.form_end_date,
a.form_mis, NVL (b.qty, 0) qty, NVL (b.amt, 0) amt,
ROUND (NVL (b.amt, 0) - NVL (b.qty, 0) * c.tax_cost,
2
) profit
FROM tb_ag001 c,
(SELECT DISTINCT b.good_id, a.so_id, a.form_fr_date,
a.form_end_date, a.form_mis
FROM tb_ab010 a, tb_ab060 b
WHERE a.title_id = b.title_id
AND a.form_end_date IS NOT NULL
/*AND a.title_cd <> '40'*/
and a.so_id<>'1000023'
AND a.so_id =
DECODE (in_so_id,
0, a.so_id,
in_so_id
)
AND a.form_fr_date BETWEEN in_start_day
AND in_end_day
- 0 / 12) a,
(SELECT b.good_id, b.so_id, b.form_fr_date,
SUM (a.ord_qty) qty, SUM (a.ord_amt) amt
FROM tb_aj002 a,
(SELECT a.good_id, a.so_id, a.form_fr_date,
a.form_fr_date - 1 / 24 start_time,
a.form_end_date + 1 / 24 end_time
FROM (SELECT DISTINCT b.good_id, a.so_id,
a.form_fr_date,
a.form_end_date
FROM tb_ab010 a, tb_ab060 b
WHERE a.title_id = b.title_id
AND a.form_end_date IS NOT NULL
and a.so_id<>'1000023'
AND a.so_id= DECODE (in_so_id,
0, a.so_id,
in_so_id
)
AND a.form_fr_date
BETWEEN in_start_day
AND in_end_day
- 0 / 12) a) b
WHERE a.good_id = b.good_id
AND a.so_id = b.so_id
AND a.medi_lclss_id = 1 AND a.so_id =
DECODE (in_so_id,
0, a.so_id,
in_so_id)
AND a.chg_yn = 'N'
AND a.ord_stat_cd < '90'
AND a.gift_cd IS NULL
AND a.outgo_site_no IN ('C01', 'C02', 'C03','C51')
AND a.etr_date BETWEEN b.start_time AND b.end_time
AND a.etr_date BETWEEN in_start_day AND in_end_day
GROUP BY b.good_id,b.so_id, b.form_fr_date,
b.end_time) b
WHERE a.good_id = b.good_id(+)
AND a.so_id = b.so_id(+)
AND a.good_id = c.good_id
AND a.form_fr_date = b.form_fr_date(+)) q
ON ( p.good_id = q.good_id AND p.so_id = q.so_id AND p.form_fr_date = q.form_fr_date)
WHEN MATCHED THEN
UPDATE
SET p.qty = q.qty, p.amt = q.amt, p.profit = q.profit,
p.mdf_id = in_etr_id, p.mdf_date = SYSDATE
WHEN NOT MATCHED THEN
INSERT
VALUES (q.good_id, q.so_id, q.form_fr_date, q.form_end_date,
q.form_mis, q.qty, q.amt, q.profit, in_etr_id, SYSDATE,
in_etr_id, SYSDATE,0,0,0);
MERGE INTO tb_ap030 p
USING (SELECT a.good_id, a.so_id, a.form_fr_date, a.form_end_date,
a.form_mis, NVL (b.qty, 0) qty, NVL (b.amt, 0) amt,
ROUND (NVL (b.amt, 0) - NVL (b.qty, 0) * c.tax_cost,
2
) profit
FROM tb_ag001 c,
(SELECT DISTINCT b.good_id, a.so_id, a.form_fr_date,
a.form_end_date, a.form_mis
FROM tb_ab010 a, tb_ab060 b
WHERE a.title_id = b.title_id
AND a.form_end_date IS NOT NULL
/*AND a.title_cd <> '40'*/
and a.so_id<>'1000023'
AND a.so_id =
DECODE (in_so_id,
0, a.so_id,
in_so_id
)
AND a.form_fr_date BETWEEN in_start_day
AND in_end_day
- 0 / 12) a,
(SELECT b.good_id, b.so_id, b.form_fr_date,
SUM (a.ord_qty) qty, SUM (a.ord_amt) amt
FROM tb_aj002 a,
(SELECT a.good_id, a.so_id, a.form_fr_date,
a.form_fr_date start_time,
a.form_end_date end_time
FROM (SELECT DISTINCT b.good_id, a.so_id,
a.form_fr_date,
a.form_end_date
FROM tb_ab010 a, tb_ab060 b
WHERE a.title_id = b.title_id
AND a.form_end_date IS NOT NULL
/*AND a.title_cd <> '40'*/
and a.so_id<>'1000023'
AND a.so_id =
DECODE (in_so_id,
0, a.so_id,
in_so_id
)
AND a.form_fr_date
BETWEEN in_start_day
AND in_end_day
- 0 / 12) a) b
WHERE a.good_id = b.good_id
AND a.so_id = b.so_id
AND a.medi_lclss_id = 1
AND a.so_id =
DECODE (in_so_id,
0, a.so_id,
in_so_id
)
AND a.chg_yn = 'N'
AND a.ord_stat_cd < '90'
AND a.gift_cd IS NULL
AND a.outgo_site_no IN ('C01', 'C02', 'C03','C51')
AND a.etr_date BETWEEN b.start_time AND b.end_time
AND a.etr_date BETWEEN in_start_day AND in_end_day
GROUP BY b.good_id,
b.so_id,
b.form_fr_date,
b.end_time) b
WHERE a.good_id = b.good_id(+)
AND a.so_id = b.so_id(+)
AND a.good_id = c.good_id
AND a.form_fr_date = b.form_fr_date(+)) q
ON ( p.good_id = q.good_id
AND p.so_id = q.so_id
AND p.form_fr_date = q.form_fr_date)
WHEN MATCHED THEN
UPDATE
SET p.now_qty = q.qty, p.now_amt = q.amt, p.now_profit = q.profit,
p.mdf_id = in_etr_id, p.mdf_date = SYSDATE
WHEN NOT MATCHED THEN
INSERT
VALUES (q.good_id, q.so_id, q.form_fr_date, q.form_end_date,
q.form_mis, 0, 0, 0, in_etr_id, SYSDATE,
in_etr_id, SYSDATE,q.qty,q.amt,q.profit);
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
RAISE;
END sp_make_ap030_temp;
code]
这是完整的，里面两个MERGE单独执行没问题的
我们也查了下
原本是有个SCH_JOB在每天上班时间的每小时的15分和45分各跑一次，发现8号13：45跑的正常的，之后14：15就无响应了，而据我们DBA说当时没什么操作，只是早上清了个归档日志，时间点也对不起来
