需求: 发货通知单审核 ,应收款金额(统计全部币别)+发货通知单已审核未出货+已出货未开票金额+本单审核金额 <= 信用档案金额
1、应收款金额:sum(应收汇总表.期末余额) group by 币别, 通过币别汇总 应收汇总表.期末余额
2、发货通知单已审核未发货:发货通知单(审核) 下推 销售出库单
条件:
未发货库数量=发货通知单分录.数量-销售出库单分录.数量
发货通知单已审核未发货金额=未发货数量*发货通知单分录.单价
3、已出货未开票金额:发货通知单(审核) 下推 销售出库单
条件: 销售出库单分录.未核销数量 !=0 执行 销售出库单分录.未核销金额求和
4、本单审核金额:发货通知单(审核)分录.金额 求和
5、信用档案金额:信用档案.金额,条件=以【客户】和【审核】检索。
1、整体计算求和,发货通知单已出库未核销、发货通知单已审核未出库
CREATE OR REPLACE
PROCEDURE PROC_GETOTHERBILLAMOUNT_EAS(
customerIds IN VARCHAR2,
out_return out sys_refcursor
)
AS
type cur_type is ref cursor; --声明游标类型
postRequisitionSql VARCHAR2(2000);--发货通知单
postRequisitionNumber VARCHAR2(100);
postRequisitionMaterialFid VARCHAR2(200);
materialNumber VARCHAR2(200);
postRequisitionQty numeric(18,6);--发货通知单数量
postRequisitionPrice numeric(18,6);--发货通知单单价
postReqEntrySeq VARCHAR2(200);
postReqDeliveryType VARCHAR2(200);
postReqCurrencyNumber VARCHAR2(200);
postReqCustomID VARCHAR2(200);
saleIssueMaterialId VARCHAR2(200);
surplusPostRequisitionQty numeric(18,6);
surplusPostRequisitionAmount numeric(18,6);
--flag VARCHAR2(100);
saleIssueQtyAdd numeric(18,6);
unBillsAmount numeric(18,6);--未核销金额
amountPostRequisition numeric(18,6);
cj numeric(18,6);
flag number;
flagMore number;
temptable VARCHAR2(200);
indexDeclare int;
--定义销售出库返回行结构
Type s_rowType is RECORD(
SaleIssueBillNumber VARCHAR2(100),
SaleIssueSourceBillTypeID VARCHAR2(100),
BillTypeName VARCHAR2(100),
SaleIssueSourceBillNumber VARCHAR2(100),
SaleIssueMaterialID VARCHAR2(100),
SaleIssueMaterialName VARCHAR2(100),
SaleIssueQTY numeric(18,6),
SaleIssueUnWriteOffQty numeric(18,6),
SaleIssueUnWriteOffAmount numeric(18,6),
currencyNumber VARCHAR2(100),
BalanceCustomerID VARCHAR2(100),
saleIssueEntrySeq VARCHAR2(100),
saleIssueEntryOtherSeq VARCHAR2(100)
);
--再次查询销售出库单
Type t_rowType is RECORD(
SaleIssueBillNumber VARCHAR2(100),
SaleIssueSourceBillTypeID VARCHAR2(100),
SaleIssueSourceBillNumber VARCHAR2(100),
SaleIssueMaterialID VARCHAR2(200),
SaleIssueQTY numeric(18,6),
SaleIssueUnWriteOffQty numeric(18,6),
SaleIssueUnWriteOffAmount numeric(18,6),
currencyNumberOther VARCHAR2(100),
balanceCustomerIDOther VARCHAR2(100)
);
cursor f_cur IS
SELECT PostRequisition.FNUMBER as fnumber,PostRequisitionEntry.FReceiveCustomerID as receiveCustomerID,custom.fname_l2 as receiveCustomerName,
PostRequisitionEntry.FMaterialID as materialFID,PostRequisitionEntry.FQty as qty,PostRequisitionEntry.FPrice as price,Currency.fnumber as currencyNumber,material.fnumber as materialNumber,
PostRequisitionEntry.FSourceBillEntrySeq as SourceBillEntrySeq,DeliveryType.fnumber as DeliveryType
FROM T_SD_PostRequisition PostRequisition
LEFT JOIN T_SD_PostRequisitionEntry PostRequisitionEntry ON PostRequisitionEntry.FPARENTID=PostRequisition.fid
LEFT JOIN T_BD_Customer custom ON PostRequisitionEntry.FReceiveCustomerID=custom.fid
LEFT JOIN T_BD_Material material ON PostRequisitionEntry.FMaterialID=material.fid
LEFT JOIN T_BD_Currency Currency ON Currency.fid=PostRequisition.FCurrencyID
LEFT JOIN T_SCM_DeliveryType DeliveryType ON DeliveryType.fid=PostRequisitionEntry.FDeliveryTypeID;
--WHERE
--PostRequisition.fnumber='SDTZ101000005106'
--PostRequisitionEntry.FReceiveCustomerID in (SELECT DISTINCT REGEXP_SUBSTR (customerIds,'[^,]+',1,LEVEL) as token FROM DUAL CONNECT BY REGEXP_SUBSTR (customerIds,'[^,]+',1,LEVEL) IS NOT NULL)
--AND PostRequisition.FBASESTATUS='4' ORDER BY PostRequisition.FNUMBER DESC;
f_row f_cur%rowtype;
s_cur cur_type;--声明游标变量
t_cur cur_type;--声明游标变量
s_row s_rowType;--声明行类型
t_row t_rowType;
BEGIN
DBMS_OUTPUT.ENABLE(buffer_size => null);
FOR f_row IN f_cur loop
EXIT WHEN f_cur%NOTFOUND;
indexDeclare:=indexDeclare+1;
IF f_cur%NOTFOUND OR f_cur%NOTFOUND IS NULL THEN
dbms_output.put_line('asda');
ELSE
--dbms_output.put_line('---asdasdas-----tEIAAAABM+S/DAQO');
amountPostRequisition:=0;
--unBillsAmount:=0;
cj :=0;
saleIssueQtyAdd:=0;
postRequisitionNumber:=f_row.fnumber;
postRequisitionMaterialFid:=f_row.materialFID;
materialNumber :=f_row.materialNumber;
postReqEntrySeq:=f_row.SourceBillEntrySeq;
postReqDeliveryType:=f_row.DeliveryType;
postReqCurrencyNumber:=f_row.currencyNumber;
postReqCustomID :=f_row.receiveCustomerID;
postRequisitionQty :=f_row.qty;
postRequisitionPrice:=f_row.price;
flag :=1;
s_cur :=FN_GETSALEISSUESQL_EAS(postRequisitionNumber,postRequisitionMaterialFid,postReqEntrySeq,postReqDeliveryType) ;
loop
fetch s_cur into s_row;
EXIT WHEN s_cur%NOTFOUND;
IF s_cur%NOTFOUND AND s_cur%NOTFOUND IS NULL AND s_cur%rowcount=0 THEN
flag:=1;
END IF;
--dbms_output.put_line('flag'||flag||'rowcount'||s_cur%rowcount);
IF s_cur%found THEN
flag:=0;
IF s_row.currencyNumber IS NOT NULL and s_row.BalanceCustomerID IS NOT NULL and postReqCurrencyNumber IS NOT NULL and postReqCustomID IS NOT NULL THEN
IF s_row.currencyNumber=postReqCurrencyNumber and s_row.BalanceCustomerID=postReqCustomID THEN
--不存在库存调拨
saleIssueQtyAdd:=saleIssueQtyAdd+s_row.SaleIssueQTY;
-- dbms_output.put_line('【未出库 销售出库不为空】第一次查询存在销售出库单 发货通知单号'||postRequisitionNumber||'【销售出库单】'||s_row.SaleIssueBillNumber||'【物料】'||materialNumber||'【发货通知单数量】'||postRequisitionQty||'【相加之和】'||s_row.SaleIssueQTY||'【销售出库单数量】'||s_row.SaleIssueQTY||'【单价】'||postRequisitionPrice||'【金额】'||amountPostRequisition||chr(13)||chr(10));
IF s_row.SaleIssueUnWriteOffQty !=0 THEN
--未核销金额不为0
unBillsAmount:=unBillsAmount+s_row.SaleIssueUnWriteOffAmount;
END IF;
ELSE
--存在库存调拨
t_cur :=fn_getSaleIssueSqlMore_Eas(postReqCustomID,s_row.SaleIssueMaterialID,s_row.SaleIssueBillNumber,postReqCurrencyNumber,postReqEntrySeq,s_row.saleIssueEntryOtherSeq) ;
flagMore:=1;
loop
fetch t_cur into t_row;
EXIT WHEN t_cur%NOTFOUND;
IF t_cur%found then
flagMore:=0;
--计算未出库
saleIssueQtyAdd:=saleIssueQtyAdd+t_row.SaleIssueQTY;
--计算未核销金额 未核销数量不为0
IF t_row.SaleIssueUnWriteOffQty !=0 THEN
unBillsAmount:=unBillsAmount+t_row.SaleIssueUnWriteOffAmount;
END IF;
--dbms_output.put_line('【未出库】第二次查找销售出库单【**xxxxxA】发货通知单: '||postRequisitionNumber||'【销售出库单】'||s_row.SaleIssueBillNumber||'【物料】'||materialNumber||'【发货通知单数量】'||f_row.qty||'【销售出库单数量】'||s_row.SaleIssueQTY||'【相加之和】'||saleIssueQtyAdd||'【单价】'||f_row.price||'【金额】'||amountPostRequisition||'【条件行号】'||f_row.SourceBillEntrySeq||chr(13)||chr(10));
END IF;
end loop;
close t_cur;
IF flagMore=1 THEN
cj :=f_row.qty*f_row.price;
amountPostRequisition :=amountPostRequisition+cj;
--dbms_output.put_line('再次查找销售出库单为空'||postRequisitionNumber||'【销售出库单】'||s_row.SaleIssueBillNumber||'【物料】'||materialNumber||'【flagMore】:'||flagMore||chr(13)||chr(10));
END IF;
END IF;
ELSE
dbms_output.put_line('单据异常 请查明原因');
END IF;
--dbms_output.put_line('销售不为销售订单:'||s_row.SaleIssueBillNumber||'物料编码:'||s_row.SaleIssueMaterialName);
END IF;
END loop;
close s_cur;
--存在销售出库 销售出库单多张
IF flag=1 THEN
cj :=f_row.qty*f_row.price;
amountPostRequisition :=amountPostRequisition+cj;
--dbms_output.put_line('不存在销售出库【发货通知单单号】'||postRequisitionNumber||'【物料】'||materialNumber||'【金额=数量*单价】'||amountPostRequisition||'【数量】'||postRequisitionQty||'单价'||postRequisitionPrice||'【flag】:'||flag||chr(13)||chr(10));
ELSE
surplusPostRequisitionQty:=postRequisitionQty-saleIssueQtyAdd;--剩余
surplusPostRequisitionAmount:=surplusPostRequisitionQty*postRequisitionPrice;--金额
amountPostRequisition:= amountPostRequisition+surplusPostRequisitionAmount;
--dbms_output.put_line('【未出库】发货通知单:END======'||postRequisitionNumber||'【物料】'||materialNumber||'【物料ID】'||postRequisitionMaterialFid||'【发货通知单数量】'||postRequisitionQty||'【销售出库单数量】'||saleIssueQtyAdd||'【未出库数量=发货通知单-销售出库单】'||surplusPostRequisitionQty||'【单价】'||postRequisitionPrice||'【金额】'||amountPostRequisition||'【条件行号】'||postReqEntrySeq||chr(13)||chr(10));
END IF;
END IF;
/*更新金额*/
--dbms_output.put_line('输出'||amountPostRequisition||'unBillsAmount'||unBillsAmount);
IF amountPostRequisition =0 THEN
dbms_output.put_line('');
ELSE
temptable:=Fn_saveAmount_Eas(f_row.receiveCustomerID,f_row.currencyNumber,amountPostRequisition,unBillsAmount,'wuyingli','0001');
dbms_output.put_line('【临时表名】'||temptable);
END IF;
END loop;
-- open out_return for SELECT * FROM zp201806190001 WHERE loginID='wuyingli' and posNumber='0001' and customID in (SELECT DISTINCT REGEXP_SUBSTR (customerIds,'[^,]+',1,LEVEL) as token FROM DUAL CONNECT BY REGEXP_SUBSTR (customerIds,'[^,]+',1,LEVEL) IS NOT NULL);
--return out_return;
END;
RECORD:定义记录数据类型,方便存储函数返回的多列游标。声明行类型可以用record结构作为类型(s_row s_rowType;–声明行类型)
关于处理返回结果集是否为空,类似rows.size()!=0 这种,在返回0时还存在计算的,我在这边声明的flag标识是否执行过循环。
2、查询销售出库,是否已存在销售出库,送货类型【自提】【送货】
2.1、自提:不存在发运单
2.2、送货:存在发运单,如果存在调拨需要再次查询销售出库。
CREATE OR REPLACE
function fn_getSaleIssueSql_Eas(postRequisitionNumber in VARCHAR2, postRequisitionMaterialId in VARCHAR2,coreBillEntrySeq in VARCHAR2,billType in VARCHAR2)
return SYS_REFCURSOR
IS f_cur SYS_REFCURSOR;
BEGIN
--销售出库单
--存在发运单
IF billType !='CARRY' THEN
open f_cur FOR
SELECT
SaleIssueBill.fnumber as SaleIssueBillNumber,
SaleIssueEntry.FSourceBillTypeID as SaleIssueSourceBillTypeID,
BillType.fname_l2 as BillTypeName,
--CarryBillEntry.FSourceBillNumber as CarryBillSourceBillNumber,
SaleIssueEntry.FSourceBillNumber as SaleIssueSourceBillNumber,
SaleIssueEntry.FMaterialID as SaleIssueMaterialID,
Material.fname_l2 as SaleIssueMaterialName,
SaleIssueEntry.FQTY as SaleIssueQTY,
SaleIssueEntry.FUnWriteOffQty as SaleIssueUnWriteOffQty,
SaleIssueEntry.FUnWriteOffAmount as SaleIssueUnWriteOffAmount,
Currency.fnumber as currencyNumber,
SaleIssueEntry.FBalanceCustomerID as BalanceCustomerID,
SaleIssueEntry.FSaleOrderEntrySeq as saleIssueEntrySeq,
SaleIssueEntry.FSeq as saleIssueEntryOtherSeq
FROM T_IM_SaleIssueBill SaleIssueBill
LEFT JOIN T_IM_SaleIssueEntry SaleIssueEntry ON SaleIssueBill.fid=SaleIssueEntry.fparentid
LEFT JOIN T_SCM_BillType BillType ON SaleIssueEntry.FSourceBillTypeID=BillType.fid
LEFT JOIN T_BD_Material Material ON SaleIssueEntry.FMaterialID=Material.fid
LEFT JOIN T_DT_CarryBill CarryBill ON CarryBill.FNUMBER=SaleIssueEntry.FSourceBillNumber
LEFT JOIN T_DT_CarryBillEntry CarryBillEntry ON CarryBill.fid=CarryBillEntry.FPARENTID AND SaleIssueEntry.FSourceBillEntrySeq=CarryBillEntry.fseq
LEFT JOIN T_BD_Currency Currency ON Currency.fid=SaleIssueBill.FCurrencyID
WHERE CarryBillEntry.FSourceBillNumber =postRequisitionNumber and SaleIssueEntry.FMaterialID=postRequisitionMaterialId AND BillType.fnumber='190' AND SaleIssueBill.FBaseStatus='4'
AND SaleIssueEntry.FSaleOrderEntrySeq=coreBillEntrySeq;
END IF;
--不存在发运单 自提
IF billType='CARRY' THEN
open f_cur FOR
SELECT
SaleIssueBill.fnumber as SaleIssueBillNumber,
SaleIssueEntry.FSourceBillTypeID as SaleIssueSourceBillTypeID,
BillType.fname_l2 as BillTypeName,
SaleIssueEntry.FSourceBillNumber as SaleIssueSourceBillNumber,
SaleIssueEntry.FMaterialID as SaleIssueMaterialID,
Material.fname_l2 as SaleIssueMaterialName,
SaleIssueEntry.FQTY as SaleIssueQTY,
SaleIssueEntry.FUnWriteOffQty as SaleIssueUnWriteOffQty,
SaleIssueEntry.FUnWriteOffAmount as SaleIssueUnWriteOffAmount,
Currency.fnumber as currencyNumber,
SaleIssueEntry.FBalanceCustomerID as BalanceCustomerID,
SaleIssueEntry.FSaleOrderEntrySeq as saleIssueEntrySeq,
SaleIssueEntry.FSeq as saleIssueEntryOtherSeq
FROM T_IM_SaleIssueBill SaleIssueBill
LEFT JOIN T_IM_SaleIssueEntry SaleIssueEntry ON SaleIssueBill.fid=SaleIssueEntry.fparentid
LEFT JOIN T_SCM_BillType BillType ON SaleIssueEntry.FSourceBillTypeID=BillType.fid
LEFT JOIN T_BD_Material Material ON SaleIssueEntry.FMaterialID=Material.fid
LEFT JOIN T_BD_Currency Currency ON Currency.fid=SaleIssueBill.FCurrencyID
WHERE SaleIssueEntry.FSourceBillNumber =postRequisitionNumber and SaleIssueEntry.FMaterialID=postRequisitionMaterialId AND BillType.fnumber='320' AND SaleIssueBill.FBaseStatus='4'
AND SaleIssueEntry.FSaleOrderEntrySeq=coreBillEntrySeq;
END IF;
return f_cur;
END;
3、再次查询销售出库。根据销售出库单
CREATE OR REPLACE
function fn_getSaleIssueSqlMore_Eas(balanceCustomerID in VARCHAR2, materialId in VARCHAR2,SaleIssueBillNumber in VARCHAR2,currencyNumber in VARCHAR2,coreBillEntrySeq in VARCHAR2,saleIssueEntryOtherSeq in VARCHAR2)
return SYS_REFCURSOR
IS f_cur SYS_REFCURSOR;
BEGIN
open f_cur FOR
SELECT
SaleIssueBill.fnumber as SaleIssueBillNumber,
SaleIssueEntry.FSourceBillTypeID as SaleIssueSourceBillTypeID,
SaleIssueEntry.FSourceBillNumber as SaleIssueSourceBillNumber,
SaleIssueEntry.FMaterialID as SaleIssueMaterialID,
SaleIssueEntry.FQTY as SaleIssueQTY,
SaleIssueEntry.FUnWriteOffQty as SaleIssueUnWriteOffQty,
SaleIssueEntry.FUnWriteOffAmount as SaleIssueUnWriteOffAmount,
Currency.fnumber as currencyNumberOther,
SaleIssueEntry.FBalanceCustomerID as balanceCustomerIDOther
FROM T_IM_SaleIssueBill SaleIssueBill
LEFT JOIN T_IM_SaleIssueEntry SaleIssueEntry ON SaleIssueBill.fid=SaleIssueEntry.fparentid
LEFT JOIN T_SCM_BillType BillType ON SaleIssueEntry.FSourceBillTypeID=BillType.fid
LEFT JOIN T_BD_Currency Currency ON Currency.fid=SaleIssueBill.FCurrencyID
WHERE SaleIssueEntry.FBalanceCustomerID =balanceCustomerID and SaleIssueEntry.FMaterialID=materialId
AND SaleIssueBill.FBaseStatus='4'
AND SaleIssueBill.fnumber like '%'||SaleIssueBillNumber||'%'
and Currency.fnumber= currencyNumber
AND SaleIssueEntry.FSaleOrderEntrySeq=coreBillEntrySeq
AND SaleIssueEntry.FSourceBillEntrySeq=saleIssueEntryOtherSeq;
return f_cur;
END;
helperUtils:币别转换,统一转换为CNY
CREATE OR REPLACE
function fn_toSwitchCNY(postReqCurrencyNumber in VARCHAR, inputAmount in numeric)
return number is outAmount number(20,4);
convertRate numeric(18,6);
BEGIN
IF postReqCurrencyNumber ='CNY' THEN
convertRate :=1;
END IF;
IF postReqCurrencyNumber !='CNY' THEN
SELECT rate.FConvertRate INTO convertRate
FROM T_BD_ExchangeRate rate
LEFT JOIN T_BD_ExchangeAux aux ON aux.fid=rate.FExchangeAuxID
LEFT JOIN T_BD_Currency ncy ON ncy.fid=aux.FSourceCurrencyID
WHERE upper(ncy.fnumber) =upper(postReqCurrencyNumber) AND aux.FTargetCurrencyID='dfd38d11-00fd-1000-e000-1ebdc0a8100dDEB58FDC'
AND to_char(rate.FInvalidTime,'yyyy-mm-dd')>to_char(sysdate,'yyyy-MM-dd');
END IF;
outAmount:=inputAmount*convertRate;
dbms_output.put_line('输入金额'||inputAmount||'转换绿'||convertRate||'结果'||outAmount);
RETURN outAmount;
END;
4、把返回的结果集存放在临时表
CREATE OR REPLACE
function Fn_saveAmount_Eas(inCustomerId IN VARCHAR2,inCurrencyNumber IN VARCHAR2,inAmountPost in numeric,inUnBillsAmount in numeric,inLoginID in VARCHAR2,inPosNumber in VARCHAR2)
return VARCHAR2 IS resultTable VARCHAR2(200);
sqlStr VARCHAR2(2000);
intoAmountPostReq numeric(18,6);
intoUnBillsAmount numeric(18,6);
amountPostReq numeric(18,6);
unBillsAmount numeric(18,6);
rowcountOther NUMBER;
begin
--sqlStr:='create table zp201806190001(loginID varchar(200),posNumber varchar(200),timeOperate varchar(200),customID varchar(300),unBillsAmount numeric(18,6),billsAmount numeric(18,6))';
--execute immediate sqlStr;
intoAmountPostReq:=FN_TOSWITCHCNY(inCurrencyNumber,inAmountPost);--汇率转换
intoUnBillsAmount:=FN_TOSWITCHCNY(inCurrencyNumber,inUnBillsAmount);--汇率转换 \
dbms_output.put_line('【为转换】'||inAmountPost||'【币别】'||inCurrencyNumber||'【转换后金额】'||intoAmountPostReq);
execute immediate 'select count(customID) from zp201806190001 where loginID=:v1 and posNumber=:v2 and customID=:v3 ' into rowcountOther using inLoginID,inPosNumber,inCustomerId;
dbms_output.put_line('【返回行数】'||rowcountOther);
IF rowcountOther=0 THEN
execute immediate 'insert into zp201806190001 (loginID,posNumber,customID,unBillsAmount,billsAmount)values(:data1,:data2,:data3,:data4,:data5)' using inLoginID,inPosNumber,inCustomerId,intoUnBillsAmount,intoAmountPostReq;
dbms_output.put_line('临时表不存在==》新增');
ELSE
execute immediate 'select unBillsAmount,billsAmount from zp201806190001 where loginID=:v1 and posNumber=:v2 and customID=:v3 ' INTO unBillsAmount,amountPostReq using inLoginID,inPosNumber,inCustomerId;
dbms_output.put_line('true'||'【更新金额】'||amountPostReq||'【+】'||intoAmountPostReq);
amountPostReq:=amountPostReq+intoAmountPostReq;
unBillsAmount:=unBillsAmount+intoUnBillsAmount;
dbms_output.put_line('true'||'【unBillsAmount】'||unBillsAmount||'【amountPostReq】'||amountPostReq);
execute immediate 'update zp201806190001 set unBillsAmount=:in1 ,billsAmount =:in2 where loginID=:in3 and posNumber=:in4 and customID=:in5' using unBillsAmount,amountPostReq,inLoginID,inPosNumber,inCustomerId;
END IF;
resultTable:='zp201806190001';
return resultTable;
end;
最后处理完结果集把存在临时表的计算结果删除。
1、在计算已审核未出库时需要根据单据物料数量来算是否已完全出库,一张发货通知单可能存在多张销售出库单,
2、未核销数量也要求类似计算,物料数量可能存在未完全核销。
之所以写存储过程计算,一是为了学习存储过程实现计算这样逻辑,二是检查下效率问题,目前测出的速度比服务端稍微快点,不是很明显。关于优化存储过程还需要继续学习。还有就是程序可以批量检查单据问题。比如发货发多了 ?我也很奇怪,测出数据一张单子发货通知单数量小于出单数量。