CREATE OR REPLACE PROCEDURE P_ordervsinv(p_year_in CHAR,p_month_in CHAR)
IS
----------------------------------------------------------------------
--功能：计算定单与库存满足关系
---------$%%$%$
--*(&$---
-----------------------------------------------------------------------
p_year CHAR(4) := trim(p_year_in);
p_month CHAR(2) := trim(p_month_in);

ls_orderdate2 CHAR(8); --a订单日期
ls_orderdate8 CHAR(8); --b订单日期
li_version2 integer; --a版本号
li_version8 integer; --b版本号
li_count integer; --满足个数

BEGIN

----取对应于a当月要货 最大一版订单日期
select NVL(MAX(ORDERDATE),'') into ls_orderdate2
from CUSTOMORDERHIST_M
where SUBSTR(SHIPPEDDATE,1,4) = p_year
and SUBSTR(SHIPPEDDATE,5,2)= p_month
and S_BRANCH_ID='BM22002010100002'
and substr(ORDERDATE,1,6)=to_char(add_months(to_date(SHIPPEDDATE,'yyyymmdd') ,-1),'yyyymm');

----取对应于b当月要货 最大一版订单日期
select NVL(MAX(ORDERDATE),'') into ls_orderdate8
from CUSTOMORDERHIST_M
where SUBSTR(SHIPPEDDATE,1,4) = p_year
and SUBSTR(SHIPPEDDATE,5,2)= p_month
and S_BRANCH_ID='BM22002010100008'
and substr(ORDERDATE,1,6)=to_char(add_months(to_date(SHIPPEDDATE,'yyyymm') ,-1),'yyyymm');

--- 取a最大版本号
select NVL(MAX(N_VERSION),0) into li_version2
from CUSTOMORDERHIST_M
where SUBSTR(SHIPPEDDATE,1,4) = p_year
and SUBSTR(SHIPPEDDATE,5,2)= p_month
and ORDERDATE = ls_orderdate2
and S_BRANCH_ID='BM22002010100002';

--- 取b最大版本号
select NVL(MAX(N_VERSION),0) into li_version8
from CUSTOMORDERHIST_M
where SUBSTR(SHIPPEDDATE,1,4) = p_year
and SUBSTR(SHIPPEDDATE,5,2)= p_month
and ORDERDATE = ls_orderdate8
and S_BRANCH_ID='BM22002010100008';

--- 把a需要的定单数据插入TEMP_PRODQTY_INCITY
insert into TEMP_PRODQTY_INCITY(C_TOBACCOID,N_INSALEQTY,C_MASTERID,C_DATE, C_VERSION)
select a.TOBACCOID, a.quantity as orderquantity,b.S_BRANCH_ID,b.SHIPPEDDATE,b.N_VERSION
from CUSTOMORDERHIST_d a,CUSTOMORDERHIST_M b
where a.saleorderid = b.saleorderid
and b.ORDERDATE = ls_orderdate2
and SUBSTR(SHIPPEDDATE,1,4) = p_year
and SUBSTR(SHIPPEDDATE,5,2)= p_month
and b.S_BRANCH_ID='BM22002010100002'
and b.N_VERSION=li_version2;
--- 把b需要的定单数据插入TEMP_PRODQTY_INCITY
insert into TEMP_PRODQTY_INCITY(C_TOBACCOID,N_INSALEQTY,C_MASTERID,C_DATE, C_VERSION)
select a.TOBACCOID, a.quantity as orderquantity,b.S_BRANCH_ID,b.SHIPPEDDATE,b.N_VERSION
from CUSTOMORDERHIST_d a,CUSTOMORDERHIST_M b
where a.saleorderid = b.saleorderid
and b.ORDERDATE = ls_orderdate8
and SUBSTR(SHIPPEDDATE,1,4) = p_year
and SUBSTR(SHIPPEDDATE,5,2)= p_month
and b.S_BRANCH_ID='BM22002010100008'
and b.N_VERSION=li_version8;

-----两部门数量按日汇总插入表TEMP_DAILYINVCHK
insert into TEMP_DAILYINVCHK(c_date,C_TOBACCOID,N_INDUSTRYINV)
select C_DATE,C_TOBACCOID,sum(N_INSALEQTY)
from TEMP_PRODQTY_INCITY
group by C_DATE,C_TOBACCOID;

----把需要的库存数据写入TEMP_DAILYINVCHK
update TEMP_DAILYINVCHK c set N_BUSINESSINV =
(select sum(b.QUANTITY)
from dailyinv_m a,dailyinv_d b
where a.DAILYINVID=b.DAILYINVID
and c.c_date=a.c_date
and c.C_TOBACCOID=b.TOBACCOID
group by a.c_date,b.TOBACCOID);

----根据TEMP_DAILYINVCHK中数据判断满足度
update TEMP_DAILYINVCHK set N_LATEREDPROD = 1
where N_INDUSTRYINV <= N_BUSINESSINV;

--- 统计满足个数
update TEMP_DAILYINVCHK c set N_MZXINV =
(select NVL(count(*),0)
from TEMP_DAILYINVCHK a
where N_LATEREDPROD=1
and a.c_date=c.c_date);

--- 统计需要比较的数目
update TEMP_DAILYINVCHK c set N_XSBINV =
(select NVL(count(*),0)
from TEMP_DAILYINVCHK a
where a.c_date=c.c_date);

----统计结果
update TEMP_DAILYINVCHK set N_SAMPLEPROD = N_XSBINV - N_MZXINV;
update TEMP_DAILYINVCHK set N_INVLOSS =N_MZXINV/N_XSBINV;

--- 把统计结果写入TEMP_INVCHK
insert into TEMP_INVCHK(C_DATE,ENDTERMIQ, ENDTERMADJ,LOWQ)
select distinct c_date,N_MZXINV, N_XSBINV,N_INVLOSS
FROM TEMP_DAILYINVCHK;

END P_ordervsinv;
/