T3单据新打印数据顺序错乱
T3单据新打印数据顺序错乱
魏雯
【问题现象】
业务单据使用新打印，存货是按照存货编码顺序录入的，但是新预览时表体存货顺序错乱，见下图：
【问题分析】
查看演示账套999账套新打印正常，没有表体错乱现象，软件前台没有新打印表体顺序设置功能节点，一般新打印顺序是按照录入顺序，不会错乱，考虑是存储过程异常导致的。
【解决方案】
备份好账套数据，使用事件跟踪器查询正常账套在新打印预览时的存储过程，在查询分析器中针对问题账套执行即可。以下以采购订单新打印表体顺序错乱为例：
1.备份好账套数据，进入软件，打开采购订单，点击打印-新设计，将新打印模板打开，使用事件跟踪器查询正常账套在打开新打印模板时的存储过程：
2. 进入查询分析器执行查看存储过程的命令：sp_helptext newprint_PurOrder_body
3.在查询分析器中针对问题账套执行以下语句：
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--采购订单
alter PROCEDURE [dbo].[NewPrint_PurOrder_body]
@parm varchar(100)
AS
begin
SELECT PO_Podetails.ID, PO_Podetails.cPOID, PO_Podetails.cInvCode, Inventory.cInvName, Inventory.cInvAddCode,
Inventory.iSafeNum,Inventory.iInvSPrice,Inventory.iInvSCost,Inventory.iInvWeight,Inventory.iVolume,Inventory.iInvRCost,
Inventory.iInvBatch,Inventory.iInvAdvance,Inventory.cQuality,Inventory.iInvLSCost,Inventory.iInvMPCost,Inventory.iInvNCost,
Inventory.iLowSum,Inventory.itOPSum,Inventory.cPosName,inventory.cVenName,
Inventory.cInvStd, PO_Podetails.iQuotedPrice, PO_Podetails.iQuantity, PO_Podetails.iNum, PO_Podetails.iUnitPrice,
PO_Podetails.iMoney, PO_Podetails.iTax, PO_Podetails.iSum, PO_Podetails.iDisCount, PO_Podetails.iNatUnitPrice,
PO_Podetails.iNatMoney, PO_Podetails.iNatTax, PO_Podetails.iNatSum, PO_Podetails.iNatDisCount, PO_Podetails.dArriveDate,
PO_Podetails.cFree2, PO_Podetails.cFree1, Inventory.cInvM_Unit, Inventory.cInvA_Unit,
(CASE WHEN (Po_podetails.iNum=0 Or po_podetails.iNum IS NULL) THEN null ELSE po_podetails.iInvExchRate END) AS iInvExchRate,
Inventory.cInvDefine1,Inventory.cInvDefine2,Inventory.cInvDefine3,PO_PoDetails.cDefine22,PO_PoDetails.cDefine23,
PO_PoDetails.cDefine24,PO_PoDetails.cDefine25,PO_PoDetails.cDefine26,PO_PoDetails.cDefine27,PO_PoDetails.iPerTaxRate,
PO_Podetails.cItemCode,PO_Podetails.cItemName,PO_Podetails.cItem_class,fItem.cItem_Name,PO_Podetails.PPCIds
,po_podetails.iTaxUnitPrice,po_podetails.iTaxNatUnitPrice
FROM
PO_Podetails LEFT JOIN inventory_pos_ven as inventory ON PO_Podetails.cInvCode = Inventory.cInvCode
LEFT JOIN fItem ON PO_Podetails.cItem_Class=fItem.cItem_Class
where PO_Podetails.cPOID = @parm order by PO_Podetails.ID
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
PS：语句中红色字体是需要自己在执行语句时加入修改的，“order by PO_Podetails.ID”的意思是按照录入顺序自动排序，也可以根据自己的个性化要求进行排序，比如根据存货编码排序加“order by cInvCode”。
附件：以下附各单据的存储过程
模块
单据
存储过程
采购
采购订单
采购订单
Create PROCEDURE [dbo].[NewPrint_PurOrder_body]
@parm varchar(100)
AS
begin
SELECT PO_Podetails.ID, PO_Podetails.cPOID, PO_Podetails.cInvCode, Inventory.cInvName, Inventory.cInvAddCode,
Inventory.iSafeNum,Inventory.iInvSPrice,Inventory.iInvSCost,Inventory.iInvWeight,Inventory.iVolume,Inventory.iInvRCost,
Inventory.iInvBatch,Inventory.iInvAdvance,Inventory.cQuality,Inventory.iInvLSCost,Inventory.iInvMPCost,Inventory.iInvNCost,
Inventory.iLowSum,Inventory.itOPSum,Inventory.cPosName,inventory.cVenName,
Inventory.cInvStd, PO_Podetails.iQuotedPrice, PO_Podetails.iQuantity, PO_Podetails.iNum, PO_Podetails.iUnitPrice,
PO_Podetails.iMoney, PO_Podetails.iTax, PO_Podetails.iSum, PO_Podetails.iDisCount, PO_Podetails.iNatUnitPrice,
PO_Podetails.iNatMoney, PO_Podetails.iNatTax, PO_Podetails.iNatSum, PO_Podetails.iNatDisCount, PO_Podetails.dArriveDate,
PO_Podetails.cFree2, PO_Podetails.cFree1, Inventory.cInvM_Unit, Inventory.cInvA_Unit,
(CASE WHEN (Po_podetails.iNum=0 Or po_podetails.iNum IS NULL) THEN null ELSE po_podetails.iInvExchRate END) AS iInvExchRate,
Inventory.cInvDefine1,Inventory.cInvDefine2,Inventory.cInvDefine3,PO_PoDetails.cDefine22,PO_PoDetails.cDefine23,
PO_PoDetails.cDefine24,PO_PoDetails.cDefine25,PO_PoDetails.cDefine26,PO_PoDetails.cDefine27,PO_PoDetails.iPerTaxRate,
PO_Podetails.cItemCode,PO_Podetails.cItemName,PO_Podetails.cItem_class,fItem.cItem_Name,PO_Podetails.PPCIds
,po_podetails.iTaxUnitPrice,po_podetails.iTaxNatUnitPrice
FROM
PO_Podetails LEFT JOIN inventory_pos_ven as inventory ON PO_Podetails.cInvCode = Inventory.cInvCode
LEFT JOIN fItem ON PO_Podetails.cItem_Class=fItem.cItem_Class
where PO_Podetails.cPOID = @parm order by PO_Podetails.ID
end
采购入库单
create PROCEDURE [dbo].[NewPrint_VoucherRD_Title]
@parm varchar(100)
AS
begin
select
(case when rdrecord.bRdFlag=1 then '入库单-收' else '出库单-发' end)as bRdFlag,rdrecord.cAccounter,
rdrecord.cVouchType,rdrecord.cBusType,rdrecord.cSource,rdrecord.cBusCode as cBillCode,rdrecord.cWhCode,rdrecord.cCode,rdrecord.cRdCode,
rdrecord.cDepCode,rdrecord.cPersonCode,rdrecord.cPTCode,rdrecord.cVenCode,rdrecord.cOrderCode,rdrecord.cARVCode,
rdrecord.cBillCode as cBusCode,rdrecord.cMemo,rdrecord.cMaker,rdrecord.cDefine1,rdrecord.cDefine2,rdrecord.cDefine3,rdrecord.cDefine8,
rdrecord.cDefine9,rdrecord.cDefine10,rdrecord.cDefine5,rdrecord.ID,rdrecord.cDefine7,rdrecord.cDefine6,rdrecord.cDefine4,
rdrecord.dVeriDate,rdrecord.dDate,rdrecord.cHandler,
vendor.dVenDevDate,vendor.cVenLPerson,vendor.cVenAddress,vendor.cVenPostCode,vendor.cVenRegCode,vendor.cVenBank,
vendor.cVenAccount,vendor.cVenPhone,vendor.cVenFax,vendor.cVenEmail,vendor.cVenPerson,vendor.cVenBP,vendor.cVenHand,
vendor.cVenIAddress,vendor.cVenIType,vendor.cVenWhCode,vendor.cVenAbbName,
Warehouse.cWhName,Rd_Style.cRdName,Department.cDepName,Person.cPersonName,PurchaseType.cPTName,vendor.cVenDefine1,vendor.cVenDefine2,vendor.cVenDefine3
,rdrecord.cZQCode,rdrecord.dZQDate,AccountPeriod.APName
from
(((((Rdrecord left join Warehouse on Rdrecord.cWhCode=Warehouse.cWhCode)left join Rd_Style on Rdrecord.cRdCode=Rd_Style.cRdCode)left join Department on Rdrecord.cDepCode=Department.cDepCode)left join Person on Rdrecord.cPersonCode=Person.cPersonCode)left
join PurchaseType on Rdrecord.cPTCode=PurchaseType.cPTCode)left join Vendor on Rdrecord.cVenCode=Vendor.cVenCode
left join AccountPeriod on Rdrecord.cZQCode = AccountPeriod.APCode
where rdrecord.ID=@parm
end
采购普通发票
CREATE PROCEDURE [dbo].[NewPrint_pPurBill_Body]
@parm varchar(100)
AS
begin
select
PurBillVouchs.iExMoney,PurBillVouchs.iNLostMoney,PurBillVouchs.iOriTotal,PurBillVouchs.iTotal,
(case when PurBillVouchs.bExBill=1 then '费用' else '非费用'end)as bExBill,PurBillVouchs.cDebitHead,PurBillVouchs.cTaxHead,
PurBillVouchs.cFree1,PurBillVouchs.cFree2,PurBillVouchs.cDefine22,PurBillVouchs.cDefine23,PurBillVouchs.cDefine24,
PurBillVouchs.cDefine25,PurBillVouchs.cInvCode,PurBillVouchs.cItemName,PurBillVouchs.cItemCode,PurBillVouchs.cItem_class,
PurBillVouchs.cNLostType,PurBillVouchs.iPosID,PurBillVouchs.ID,PurBillVouchs.PBVID,PurBillVouchs.iPBVQuantity,
PurBillVouchs.iNum,PurBillVouchs.iOriCost,
--PurBillVouchs.iOriMoney,
(PurBillVouchs.iOriMoney + PurBillVouchs.iOriTaxPrice) as iOriMoney,
PurBillVouchs.iLostQuan,PurBillVouchs.iTaxPrice,
(case when PurBillVouchs.iNum=0 then null else PurBillVouchs.iNum end ) as iNum,
PurBillVouchs.iOriTaxPrice,
PurBillVouchs.cOriCode,
--PurBillVouchs.iOriSum,
PurBillVouchs.iCost,
--PurBillVouchs.iMoney,
(PurBillVouchs.iMoney + PurBillVouchs.iTaxPrice) as iMoney,
PurBillVouchs.cDefine26,
PurBillVouchs.iNLostQuan,PurBillVouchs.mNLostTax,PurBillVouchs.cDefine27,PurBillVouchs.iTaxRate,PurBillVouchs.dSignDate,
PurBillVouchs.dInDate,
(case when Inventory.bInvType=1 then '是' else '否' end)as bInvType,
(case when Inventory.bService=1 then '应税劳务' else '非应税劳务' end)as bService,
(case when Inventory.bInvBatch=1 then '是' else '否' end)as bInvBatch,
inventory.cInvA_Unit,inventory.cInvStd,inventory.cInvCCode,inventory.cInvM_Unit,inventory.cQuality,inventory.iInvWeight,
inventory.iVolume,inventory.iInvRCost,inventory.iInvSPrice,inventory.iInvSCost,inventory.iInvLSCost,inventory.iInvNCost,
inventory.iInvAdvance,inventory.iInvBatch,inventory.iSafeNum,inventory.iTopSum,inventory.iLowSum,inventory.iInvMPCost,
inventory.cInvAddCode,inventory.cInvName,
(case when (PurBillVouchs.iNum=0 or PurBillVouchs.iNum is null) then null else PurBillVouchs.iInvExchRate end)as iInvExchRate,
inventory.cPosName,inventory.cVenName,fitem.cItem_Name
from (PurBillVouchs left join inventory_pos_ven as inventory on PurBillVouchs.cInvCode=Inventory.cInvCode)
left join Fitem on PurBillVouchs.cItem_class=Fitem.cItem_class
where PurBillVouchs.PBVID=@parm order by PurBillVouchs.ID
end
if not exists(select 1 from reportinfos where CardNumber = '25' and Name = 'cOriCode' and cardsection = 'B')
begin
insert into reportinfos values('25','原始发票号','cOriCode','B')
end
采购专用发票
CREATE PROCEDURE [dbo].[NewPrint_PurBill_Title]
@parm varchar(100)
AS
begin
select
(case when PurBillVouch.bNegative=1 then '是' else '否' end)as bNegative,
(case when PurBillVouch.bOriginal=1 then '是' else '否' end)as bOriginal,
(case when PurBillVouch.bFirst=1 then '是' else '否' end)as bFirst,
(case when PurBillVouch.bPayment=1 then '是' else '否' end)as bPayment,
PurBillVouch.cDefine1,PurBillVouch.cBusType,PurBillVouch.cPBVMaker,PurBillVouch.cPBVVerifier,PurBillVouch.cDefine2,
PurBillVouch.cDefine4,PurBillVouch.cDefine5,PurBillVouch.cDefine7,PurBillVouch.cDefine8,
CASE PurBillVouch.cPBVBillType WHEN '01' THEN '专用发票' WHEN '02' THEN '普通发票' WHEN '03' THEN '普通运费发票'
WHEN '04' THEN '废旧物资收购凭证' WHEN '05' THEN '农副产品收购凭证' WHEN '06' THEN '其他票据' WHEN '07' THEN '专用运费发票'
END AS cPBVBillType,
PurBillVouch.cPBVCode,PurBillVouch.cPTCode,PurBillVouch.cVenCode,PurBillVouch.cUnitCode,PurBillVouch.cDepCode,
PurBillVouch.cPersonCode,PurBillVouch.cPayCode,PurBillVouch.cexch_name,PurBillVouch.cPBVMemo,PurBillVouch.cOrderCode,
PurBillVouch.cInCode,PurBillVouch.PBVID,PurBillVouch.iPBVTaxRate,PurBillVouch.cExchRate,PurBillVouch.cDefine9,
PurBillVouch.cDefine6,PurBillVouch.cDefine3,PurBillVouch.cDefine10,PurBillVouch.dSDate,PurBillVouch.dPBVDate,PurBillVouch.dVerifyDate,
Vendor.cVenAbbName as cVenAbbName_1,Vendor_D.cVenAbbName as cVenAbbName,Vendor.dVenDevDate,Vendor.cVenDefine1,Vendor.cVenDefine2,Vendor.cVenDefine3,Vendor.cVenLPerson,Vendor.cVenAddress,Vendor.cVenPostCode,
Vendor.cVenRegCode,Vendor.cVenBank,Vendor.cVenAccount,Vendor.cVenPhone,Vendor.cVenFax,Vendor.cVenEmail,Vendor.cVenPerson,
Vendor.cVenBP,Vendor.cVenHand,Vendor.cVenIAddress,Vendor.cVenIType,Vendor.cVenWhCode,
PurchaseType.cPTName,Department.cDepName,Person.cPersonName,PayCondition.cPayName,foreigncurrency.cexch_code
,PurBillVouch.cZQCode,PurBillVouch.dZQDate,AccountPeriod.APName
from
((((((PurBillVouch left join PurchaseType on PurBillVouch.cPTCode=PurchaseType.cPTCode)left join Department on PurBillVouch.cDepCode=Department.cDepCode)left join Person on PurBillVouch.cPersonCode=Person.cPersonCode)left join Paycondition on PurBillVouch
.cPayCode=PayCondition.cPayCode)left join foreigncurrency on PurBillVouch.cexch_name=foreigncurrency.cexch_name)left join Vendor on PurBillVouch.cVenCode=Vendor.cVenCode)left join Vendor as Vendor_D on PurBillVouch.cUnitCode=Vendor_D.cVenCode
left join AccountPeriod on PurBillVouch.cZQCode = AccountPeriod.APCode
where PurBillVouch.PBVID=@parm
end
采购普通运费发票
--- 采购运费发票
CREATE PROCEDURE [dbo].[NewPrint_yPurBill_Body]
@parm varchar(100)
AS
begin
select
Purbillvouchs.iExMoney,Purbillvouchs.iNLostMoney,Purbillvouchs.iOriTotal,Purbillvouchs.iTotal,
(case when PurBillVouchs.bExBill=1 then '费用' else '非费用'end)as bExBill,
Purbillvouchs.cDebitHead,Purbillvouchs.cTaxHead,Purbillvouchs.cFree1,Purbillvouchs.cFree2,Purbillvouchs.cDefine22,
Purbillvouchs.cDefine23,Purbillvouchs.cDefine24,Purbillvouchs.cDefine25,Purbillvouchs.cInvCode,Purbillvouchs.cItemName,
Purbillvouchs.cItemCode,Purbillvouchs.cItem_class,Purbillvouchs.cNLostType,Purbillvouchs.iPosID,Purbillvouchs.ID,
Purbillvouchs.PBVID,Purbillvouchs.iPBVQuantity,(case when PurBillVouchs.iNum=0 then null else Purbillvouchs.iNum end ) as iNum,Purbillvouchs.iOriCost,Purbillvouchs.iOriMoney,
Purbillvouchs.iLostQuan,Purbillvouchs.iTaxPrice,Purbillvouchs.iSum,Purbillvouchs.iOriTaxPrice,Purbillvouchs.iOriSum,
Purbillvouchs.iCost,Purbillvouchs.iMoney,Purbillvouchs.cDefine26,Purbillvouchs.iNLostQuan,Purbillvouchs.mNLostTax,
Purbillvouchs.cDefine27,Purbillvouchs.iTaxRate,Purbillvouchs.dSignDate,Purbillvouchs.dInDate,
(case when Inventory.bInvType=1 then '是' else '否' end)as bInvType,
(case when Inventory.bService=1 then '应税劳务' else '非应税劳务' end)as bService,
(case when Inventory.bInvBatch=1 then '是' else '否' end)as bInvBatch,
inventory.cQuality,inventory.iInvWeight,inventory.iVolume,inventory.iInvRCost,inventory.iInvSPrice,inventory.iInvSCost,
inventory.cInvA_Unit,inventory.cInvCCode,inventory.cInvM_Unit,inventory.cInvStd,inventory.cInvAddCode,inventory.cInvName,
inventory.iInvLSCost,inventory.iInvNCost,inventory.iInvAdvance,inventory.iInvBatch,inventory.iSafeNum,inventory.iTopSum,
inventory.iLowSum,inventory.iInvMPCost,inventory.cPosName,inventory.cVenName,
(case when (PurBillVouchs.iNum=0 or PurBillVouchs.iNum is null) then null else (PurBillVouchs.iInvExchRate) end)as iInvExchRate,fitem.cItem_Name
from
(PurBillVouchs left join inventory_pos_ven as inventory on PurBillVouchs.cInvCode=Inventory.cInvCode)left join Fitem on PurBillVouchs.cItem_class=Fitem.cItem_class
where PurBillVouchs.PBVID=@parm order by Purbillvouchs.ID
end
采购专用运费发票
CREATE PROCEDURE [dbo].[NewPrint_ZyPurBill_Body]
@parm varchar(100)
AS
begin
select
Purbillvouchs.iExMoney,Purbillvouchs.iNLostMoney,Purbillvouchs.iOriTotal,Purbillvouchs.iTotal,
(case when PurBillVouchs.bExBill=1 then '费用' else '非费用'end)as bExBill,
Purbillvouchs.cDebitHead,Purbillvouchs.cTaxHead,Purbillvouchs.cFree1,Purbillvouchs.cFree2,Purbillvouchs.cDefine22,
Purbillvouchs.cDefine23,Purbillvouchs.cDefine24,Purbillvouchs.cDefine25,Purbillvouchs.cInvCode,Purbillvouchs.cItemName,
Purbillvouchs.cItemCode,Purbillvouchs.cItem_class,Purbillvouchs.cNLostType,Purbillvouchs.iPosID,Purbillvouchs.ID,
Purbillvouchs.PBVID,Purbillvouchs.iPBVQuantity,(case when PurBillVouchs.iNum=0 then null else Purbillvouchs.iNum end ) as iNum,Purbillvouchs.iOriCost,Purbillvouchs.iOriMoney,
Purbillvouchs.iLostQuan,Purbillvouchs.iTaxPrice,Purbillvouchs.iSum,Purbillvouchs.iOriTaxPrice,Purbillvouchs.iOriSum,
Purbillvouchs.iCost,Purbillvouchs.iMoney,Purbillvouchs.cDefine26,Purbillvouchs.iNLostQuan,Purbillvouchs.mNLostTax,
Purbillvouchs.cDefine27,Purbillvouchs.iTaxRate,Purbillvouchs.dSignDate,Purbillvouchs.dInDate,
(case when Inventory.bInvType=1 then '是' else '否' end)as bInvType,
(case when Inventory.bService=1 then '应税劳务' else '非应税劳务' end)as bService,
(case when Inventory.bInvBatch=1 then '是' else '否' end)as bInvBatch,
inventory.cQuality,inventory.iInvWeight,inventory.iVolume,inventory.iInvRCost,inventory.iInvSPrice,inventory.iInvSCost,
inventory.cInvA_Unit,inventory.cInvCCode,inventory.cInvM_Unit,inventory.cInvStd,inventory.cInvAddCode,inventory.cInvName,
inventory.iInvLSCost,inventory.iInvNCost,inventory.iInvAdvance,inventory.iInvBatch,inventory.iSafeNum,inventory.iTopSum,
inventory.iLowSum,inventory.iInvMPCost,inventory.cPosName,inventory.cVenName,
(case when (PurBillVouchs.iNum=0 or PurBillVouchs.iNum is null) then null else (PurBillVouchs.iInvExchRate) end)as iInvExchRate,fitem.cItem_Name
from
(PurBillVouchs left join inventory_pos_ven as inventory on PurBillVouchs.cInvCode=Inventory.cInvCode)left join Fitem on PurBillVouchs.cItem_class=Fitem.cItem_class
where PurBillVouchs.PBVID=@parm order by Purbillvouchs.ID
end
采购普通发票（红字）
CREATE PROCEDURE [dbo].[NewPrint_pPurBill_Body]
@parm varchar(100)
AS
begin
select
PurBillVouchs.iExMoney,PurBillVouchs.iNLostMoney,PurBillVouchs.iOriTotal,PurBillVouchs.iTotal,
(case when PurBillVouchs.bExBill=1 then '费用' else '非费用'end)as bExBill,PurBillVouchs.cDebitHead,PurBillVouchs.cTaxHead,
PurBillVouchs.cFree1,PurBillVouchs.cFree2,PurBillVouchs.cDefine22,PurBillVouchs.cDefine23,PurBillVouchs.cDefine24,
PurBillVouchs.cDefine25,PurBillVouchs.cInvCode,PurBillVouchs.cItemName,PurBillVouchs.cItemCode,PurBillVouchs.cItem_class,
PurBillVouchs.cNLostType,PurBillVouchs.iPosID,PurBillVouchs.ID,PurBillVouchs.PBVID,PurBillVouchs.iPBVQuantity,
PurBillVouchs.iNum,PurBillVouchs.iOriCost,
--PurBillVouchs.iOriMoney,
(PurBillVouchs.iOriMoney + PurBillVouchs.iOriTaxPrice) as iOriMoney,
PurBillVouchs.iLostQuan,PurBillVouchs.iTaxPrice,
(case when PurBillVouchs.iNum=0 then null else PurBillVouchs.iNum end ) as iNum,
PurBillVouchs.iOriTaxPrice,
PurBillVouchs.cOriCode,
--PurBillVouchs.iOriSum,
PurBillVouchs.iCost,
--PurBillVouchs.iMoney,
(PurBillVouchs.iMoney + PurBillVouchs.iTaxPrice) as iMoney,
PurBillVouchs.cDefine26,
PurBillVouchs.iNLostQuan,PurBillVouchs.mNLostTax,PurBillVouchs.cDefine27,PurBillVouchs.iTaxRate,PurBillVouchs.dSignDate,
PurBillVouchs.dInDate,
(case when Inventory.bInvType=1 then '是' else '否' end)as bInvType,
(case when Inventory.bService=1 then '应税劳务' else '非应税劳务' end)as bService,
(case when Inventory.bInvBatch=1 then '是' else '否' end)as bInvBatch,
inventory.cInvA_Unit,inventory.cInvStd,inventory.cInvCCode,inventory.cInvM_Unit,inventory.cQuality,inventory.iInvWeight,
inventory.iVolume,inventory.iInvRCost,inventory.iInvSPrice,inventory.iInvSCost,inventory.iInvLSCost,inventory.iInvNCost,
inventory.iInvAdvance,inventory.iInvBatch,inventory.iSafeNum,inventory.iTopSum,inventory.iLowSum,inventory.iInvMPCost,
inventory.cInvAddCode,inventory.cInvName,
(case when (PurBillVouchs.iNum=0 or PurBillVouchs.iNum is null) then null else PurBillVouchs.iInvExchRate end)as iInvExchRate,
inventory.cPosName,inventory.cVenName,fitem.cItem_Name
from (PurBillVouchs left join inventory_pos_ven as inventory on PurBillVouchs.cInvCode=Inventory.cInvCode)
left join Fitem on PurBillVouchs.cItem_class=Fitem.cItem_class
where PurBillVouchs.PBVID=@parm order by PurBillVouchs.ID
end
if not exists(select 1 from reportinfos where CardNumber = '25' and Name = 'cOriCode' and cardsection = 'B')
begin
insert into reportinfos values('25','原始发票号','cOriCode','B')
end
采购专用发票（红字）
CREATE PROCEDURE [dbo].[NewPrint_PurBill_Title]
@parm varchar(100)
AS
begin
select
(case when PurBillVouch.bNegative=1 then '是' else '否' end)as bNegative,
(case when PurBillVouch.bOriginal=1 then '是' else '否' end)as bOriginal,
(case when PurBillVouch.bFirst=1 then '是' else '否' end)as bFirst,
(case when PurBillVouch.bPayment=1 then '是' else '否' end)as bPayment,
PurBillVouch.cDefine1,PurBillVouch.cBusType,PurBillVouch.cPBVMaker,PurBillVouch.cPBVVerifier,PurBillVouch.cDefine2,
PurBillVouch.cDefine4,PurBillVouch.cDefine5,PurBillVouch.cDefine7,PurBillVouch.cDefine8,
CASE PurBillVouch.cPBVBillType WHEN '01' THEN '专用发票' WHEN '02' THEN '普通发票' WHEN '03' THEN '普通运费发票'
WHEN '04' THEN '废旧物资收购凭证' WHEN '05' THEN '农副产品收购凭证' WHEN '06' THEN '其他票据' WHEN '07' THEN '专用运费发票'
END AS cPBVBillType,
PurBillVouch.cPBVCode,PurBillVouch.cPTCode,PurBillVouch.cVenCode,PurBillVouch.cUnitCode,PurBillVouch.cDepCode,
PurBillVouch.cPersonCode,PurBillVouch.cPayCode,PurBillVouch.cexch_name,PurBillVouch.cPBVMemo,PurBillVouch.cOrderCode,
PurBillVouch.cInCode,PurBillVouch.PBVID,PurBillVouch.iPBVTaxRate,PurBillVouch.cExchRate,PurBillVouch.cDefine9,
PurBillVouch.cDefine6,PurBillVouch.cDefine3,PurBillVouch.cDefine10,PurBillVouch.dSDate,PurBillVouch.dPBVDate,PurBillVouch.dVerifyDate,
Vendor.cVenAbbName as cVenAbbName_1,Vendor_D.cVenAbbName as cVenAbbName,Vendor.dVenDevDate,Vendor.cVenDefine1,Vendor.cVenDefine2,Vendor.cVenDefine3,Vendor.cVenLPerson,Vendor.cVenAddress,Vendor.cVenPostCode,
Vendor.cVenRegCode,Vendor.cVenBank,Vendor.cVenAccount,Vendor.cVenPhone,Vendor.cVenFax,Vendor.cVenEmail,Vendor.cVenPerson,
Vendor.cVenBP,Vendor.cVenHand,Vendor.cVenIAddress,Vendor.cVenIType,Vendor.cVenWhCode,
PurchaseType.cPTName,Department.cDepName,Person.cPersonName,PayCondition.cPayName,foreigncurrency.cexch_code
,PurBillVouch.cZQCode,PurBillVouch.dZQDate,AccountPeriod.APName
from
((((((PurBillVouch left join PurchaseType on PurBillVouch.cPTCode=PurchaseType.cPTCode)left join Department on PurBillVouch.cDepCode=Department.cDepCode)left join Person on PurBillVouch.cPersonCode=Person.cPersonCode)left join Paycondition on PurBillVouch
.cPayCode=PayCondition.cPayCode)left join foreigncurrency on PurBillVouch.cexch_name=foreigncurrency.cexch_name)left join Vendor on PurBillVouch.cVenCode=Vendor.cVenCode)left join Vendor as Vendor_D on PurBillVouch.cUnitCode=Vendor_D.cVenCode
left join AccountPeriod on PurBillVouch.cZQCode = AccountPeriod.APCode
where PurBillVouch.PBVID=@parm
end
采购普通运费发票（红字）
--- 采购运费发票
CREATE PROCEDURE [dbo].[NewPrint_yPurBill_Body]
@parm varchar(100)
AS
begin
select
Purbillvouchs.iExMoney,Purbillvouchs.iNLostMoney,Purbillvouchs.iOriTotal,Purbillvouchs.iTotal,
(case when PurBillVouchs.bExBill=1 then '费用' else '非费用'end)as bExBill,
Purbillvouchs.cDebitHead,Purbillvouchs.cTaxHead,Purbillvouchs.cFree1,Purbillvouchs.cFree2,Purbillvouchs.cDefine22,
Purbillvouchs.cDefine23,Purbillvouchs.cDefine24,Purbillvouchs.cDefine25,Purbillvouchs.cInvCode,Purbillvouchs.cItemName,
Purbillvouchs.cItemCode,Purbillvouchs.cItem_class,Purbillvouchs.cNLostType,Purbillvouchs.iPosID,Purbillvouchs.ID,
Purbillvouchs.PBVID,Purbillvouchs.iPBVQuantity,(case when PurBillVouchs.iNum=0 then null else Purbillvouchs.iNum end ) as iNum,Purbillvouchs.iOriCost,Purbillvouchs.iOriMoney,
Purbillvouchs.iLostQuan,Purbillvouchs.iTaxPrice,Purbillvouchs.iSum,Purbillvouchs.iOriTaxPrice,Purbillvouchs.iOriSum,
Purbillvouchs.iCost,Purbillvouchs.iMoney,Purbillvouchs.cDefine26,Purbillvouchs.iNLostQuan,Purbillvouchs.mNLostTax,
Purbillvouchs.cDefine27,Purbillvouchs.iTaxRate,Purbillvouchs.dSignDate,Purbillvouchs.dInDate,
(case when Inventory.bInvType=1 then '是' else '否' end)as bInvType,
(case when Inventory.bService=1 then '应税劳务' else '非应税劳务' end)as bService,
(case when Inventory.bInvBatch=1 then '是' else '否' end)as bInvBatch,
inventory.cQuality,inventory.iInvWeight,inventory.iVolume,inventory.iInvRCost,inventory.iInvSPrice,inventory.iInvSCost,
inventory.cInvA_Unit,inventory.cInvCCode,inventory.cInvM_Unit,inventory.cInvStd,inventory.cInvAddCode,inventory.cInvName,
inventory.iInvLSCost,inventory.iInvNCost,inventory.iInvAdvance,inventory.iInvBatch,inventory.iSafeNum,inventory.iTopSum,
inventory.iLowSum,inventory.iInvMPCost,inventory.cPosName,inventory.cVenName,
(case when (PurBillVouchs.iNum=0 or PurBillVouchs.iNum is null) then null else (PurBillVouchs.iInvExchRate) end)as iInvExchRate,fitem.cItem_Name
from
(PurBillVouchs left join inventory_pos_ven as inventory on PurBillVouchs.cInvCode=Inventory.cInvCode)left join Fitem on PurBillVouchs.cItem_class=Fitem.cItem_class
where PurBillVouchs.PBVID=@parm order by Purbillvouchs.ID
end
采购专用运费发票（红字）
CREATE PROCEDURE [dbo].[NewPrint_ZyPurBill_Body]
@parm varchar(100)
AS
begin
select
Purbillvouchs.iExMoney,Purbillvouchs.iNLostMoney,Purbillvouchs.iOriTotal,Purbillvouchs.iTotal,
(case when PurBillVouchs.bExBill=1 then '费用' else '非费用'end)as bExBill,
Purbillvouchs.cDebitHead,Purbillvouchs.cTaxHead,Purbillvouchs.cFree1,Purbillvouchs.cFree2,Purbillvouchs.cDefine22,
Purbillvouchs.cDefine23,Purbillvouchs.cDefine24,Purbillvouchs.cDefine25,Purbillvouchs.cInvCode,Purbillvouchs.cItemName,
Purbillvouchs.cItemCode,Purbillvouchs.cItem_class,Purbillvouchs.cNLostType,Purbillvouchs.iPosID,Purbillvouchs.ID,
Purbillvouchs.PBVID,Purbillvouchs.iPBVQuantity,(case when PurBillVouchs.iNum=0 then null else Purbillvouchs.iNum end ) as iNum,Purbillvouchs.iOriCost,Purbillvouchs.iOriMoney,
Purbillvouchs.iLostQuan,Purbillvouchs.iTaxPrice,Purbillvouchs.iSum,Purbillvouchs.iOriTaxPrice,Purbillvouchs.iOriSum,
Purbillvouchs.iCost,Purbillvouchs.iMoney,Purbillvouchs.cDefine26,Purbillvouchs.iNLostQuan,Purbillvouchs.mNLostTax,
Purbillvouchs.cDefine27,Purbillvouchs.iTaxRate,Purbillvouchs.dSignDate,Purbillvouchs.dInDate,
(case when Inventory.bInvType=1 then '是' else '否' end)as bInvType,
(case when Inventory.bService=1 then '应税劳务' else '非应税劳务' end)as bService,
(case when Inventory.bInvBatch=1 then '是' else '否' end)as bInvBatch,
inventory.cQuality,inventory.iInvWeight,inventory.iVolume,inventory.iInvRCost,inventory.iInvSPrice,inventory.iInvSCost,
inventory.cInvA_Unit,inventory.cInvCCode,inventory.cInvM_Unit,inventory.cInvStd,inventory.cInvAddCode,inventory.cInvName,
inventory.iInvLSCost,inventory.iInvNCost,inventory.iInvAdvance,inventory.iInvBatch,inventory.iSafeNum,inventory.iTopSum,
inventory.iLowSum,inventory.iInvMPCost,inventory.cPosName,inventory.cVenName,
(case when (PurBillVouchs.iNum=0 or PurBillVouchs.iNum is null) then null else (PurBillVouchs.iInvExchRate) end)as iInvExchRate,fitem.cItem_Name
from
(PurBillVouchs left join inventory_pos_ven as inventory on PurBillVouchs.cInvCode=Inventory.cInvCode)left join Fitem on PurBillVouchs.cItem_class=Fitem.cItem_class
where PurBillVouchs.PBVID=@parm order by Purbillvouchs.ID
end
销售
销售订单
销售订单
CREATE PROCEDURE [dbo].[NewPrint_SaleOrder_Body]
@parm varchar(100)
AS
begin
select
Inventory.cInvDefine1,Inventory.cInvDefine2,Inventory.cInvDefine3,Inventory.cInvName,Inventory.cInvStd,Inventory.cInvM_Unit,
Inventory.cInvA_Unit,(CASE WHEN (SO_SODetails.iNum=0 Or SO_SODetails.iNum IS NULL) THEN null ELSE SO_SODetails.iInvExchRate END) AS iInvExchRate,
Inventory.cInvAddCode,Inventory.bService,Inventory.iInvLSCost,Inventory.bFree1,
Inventory.bInvType,Inventory.bFree2,Inventory.cQuality,Inventory.iInvWeight,Inventory.iVolume,Inventory.iInvRCost,
Inventory.iInvSPrice,Inventory.iInvSCost,Inventory.iInvLSCost,Inventory.iInvNCost,Inventory.iInvAdvance,Inventory.iInvBatch,
Inventory.iSafeNum,Inventory.iTopSum,Inventory.iLowSum,Inventory.iInvMPCost,
Inventory.cPosName,Inventory.cVenName,SO_SODetails.AutoID,SO_SODetails.cSOCode,SO_SODetails.cInvCode,SO_SODetails.iQuantity,
SO_SODetails.iNum,
SO_SODetails.iTaxUnitPrice,SO_SODetails.iQuotedPrice,SO_SODetails.iUnitPrice,SO_SODetails.iMoney,SO_SODetails.iTaxRate,
SO_SODetails.iTax,SO_SODetails.iSum,SO_SODetails.iNatUnitPrice,SO_SODetails.iNatMoney,SO_SODetails.iNatTax,
SO_SODetails.iNatSum,SO_SODetails.iNatDiscount,SO_SODetails.iDiscount,SO_SODetails.iFHQuantity,SO_SODetails.iFHNum,
SO_SODetails.iFHMoney,SO_SODetails.iKPQuantity,SO_SODetails.iKPNum,SO_SODetails.iKPMoney,SO_SODetails.dPreDate,
SO_SODetails.cMemo,SO_SODetails.KL,SO_SODetails.cFree1,SO_SODetails.cItemCode,SO_SODetails.cFree2,SO_SODetails.cItem_class,
SO_SODetails.KL2,SO_SODetails.cDefine22,SO_SODetails.cDefine23,SO_SODetails.cDefine24,SO_SODetails.cDefine25,
SO_SODetails.cDefine26,SO_SODetails.cDefine27,SO_SODetails.iSOsID,SO_SODetails.cItemName,SO_SODetails.cItem_CName
from
SO_SODetails left join inventory_pos_ven as inventory ON SO_SODetails.cInvCode = Inventory.cInvCode
where SO_SODetails.cSOCode=@parm order by SO_SODetails.AutoID
end
发货单
CREATE PROCEDURE [dbo].[NewPrint_Dispvch_Title]
@parm varchar(100)
AS
begin
select
customer.cCusAbbName,customer.cCusName,customer.cCusPhone,customer.cCusOAddress,customer.cCusOType,customer.cCusPayCond,
customer.iCusCreLine,customer.cCusPerson,customer.cCusPostCode,customer.iCusCreDate,customer.cCusCreGrade,customer.iARMoney,
customer.cCusDepart,customer.cCusPPerson,customer.dCusDevDate,customer.cCusLPerson,customer.cCusRegCode,customer.cCusBank,
customer.cCusAccount,customer.cCusEmail,customer.cCusAddress,customer.cCusPostCode,customer.cCusPhone,customer.cCusFax,
customer.cCusBP,customer.cCusHand,customer.cCusPerson,customer.cCusOAddress,customer.cCusOType,customer.cCusWhCode,
customer.cCusDefine1,customer.cCusDefine2,customer.cCusDefine3,
dispatchlist.cDLCode,dispatchlist.cVouchType,dispatchlist.cSTCode,dispatchlist.dDate,dispatchlist.cDepCode,
dispatchlist.cPersonCode,dispatchlist.cSBVCode,dispatchlist.cSOCode,dispatchlist.cCusCode,dispatchlist.cPayCode,
dispatchlist.cSCCode,dispatchlist.cShipAddress,dispatchlist.cexch_name,dispatchlist.iExchRate,dispatchlist.iTaxRate,
dispatchlist.cMemo,dispatchlist.cDefine1,dispatchlist.cDefine2,(case when dispatchlist.bReturnFlag=1 then '退货' else '发货' end) as bReturnFlag,dispatchlist.DLID,dispatchlist.cVerifier,dispatchlist.cMaker,
(case when dispatchlist.bFirst=1 then '是' else '否' end) as bFist,
dispatchlist.cDefine3,dispatchlist.cDefine4,dispatchlist.cDefine5,dispatchlist.cDefine6,dispatchlist.cDefine7,
dispatchlist.cDefine8,dispatchlist.cDefine9,dispatchlist.cDefine10,dispatchlist.SBVID,
(case when dispatchlist.iSale=1 then '是' else '否' end) as iSale,
(case when dispatchlist.bDisFlag=1 then '是' else '否' end) as bDisFlag,
VouchType.cVouchName,SaleType.cSTName,Department.cDepName,Person.cPersonName,ShippingChoice.cSCName,PayCondition.cPayName
,dispatchlist.cZQCode,dispatchlist.dZQDate,AccountPeriod.APName,Customer.cCusDefine1,Customer.cCusDefine2,Customer.cCusDefine3,
dispatchlist.iPrnNum+1 as iPrnNum
from
((((((dispatchlist left join customer on dispatchlist.cCusCode=customer.cCusCode)left join VouchType on dispatchlist.cVouchType
=VouchType.cVouchType)left join SaleType on dispatchlist.cSTCode=SaleType.cSTCode)left join Department on dispatchlist.cDepCode=Department.cDepCode)left join Person on dispatchlist.cPersonCode=Person.cPersonCode)left join PayCondition on dispatchlist.cPay
Code=PayCondition.cPayCode)left join ShippingChoice on dispatchlist.cSCCode=ShippingChoice.cSCCode
left join AccountPeriod on dispatchlist.cZQCode = AccountPeriod.APCode
where dispatchlist.DLID=@parm
end
销售退货单
CREATE PROCEDURE [dbo].[NewPrint_ReDispatchList_Body]
@parm varchar(100)
AS
begin
declare @PrintKinds varchar(100)
set @PrintKinds = ''
declare @All_Name varchar(50)
declare @FDCondition varchar(100)
declare @FDValue varchar(100)
declare @IsHengDa varchar(5)
declare @strSQL varchar(8000)
declare @HengDaSQL varchar(1000)
declare @HengDaSQL1 varchar(1000) --打印清单和其它打印的折扣横打中'折扣额'位置不一样
declare @MaxAutoID varchar(5)
if CHARINDEX('||',@parm) > 0
begin
set @PrintKinds = SUBSTRING(@parm,CHARINDEX('||',@parm)+2,len(@parm)-CHARINDEX('||',@parm))
set @parm = SUBSTRING(@parm,1,CHARINDEX('||',@parm)-1)
end
if CHARINDEX('/bDiscount',@PrintKinds) > 0
begin
set @IsHengDa = 'True'
set @PrintKinds = SUBSTRING(@PrintKinds,1,CHARINDEX('/bDiscount',@PrintKinds)-1)
end
if @PrintKinds = ''
begin
set @PrintKinds = 'Nothing'
set @FDCondition = ''
end
if CHARINDEX('All',@PrintKinds) > 0
begin
set @All_Name = SUBSTRING(@PrintKinds,CHARINDEX(',',@PrintKinds)+1,len(@PrintKinds)-CHARINDEX(',',@PrintKinds))
set @PrintKinds = 'All'
end
if CHARINDEX('Nothing',@PrintKinds) >0
if CHARINDEX(',',@PrintKinds) >0
begin
set @FDCondition = SUBSTRING(@PrintKinds,CHARINDEX(',',@PrintKinds)+1,(CHARINDEX('?',@PrintKinds)-CHARINDEX(',',@PrintKinds))-1)
set @FDValue = SUBSTRING(@PrintKinds,CHARINDEX('?',@PrintKinds)+1,len(@PrintKinds)-CHARINDEX('?',@PrintKinds))
set @PrintKinds = 'Nothing'
end
set @MaxAutoID = (select max(autoid) from dispatchlists where DLID= @parm) +1
set @HengDaSQL = ' select ((select max(autoid)from dispatchlists where DLID= ' + @parm + '))+1 as autoid ,'''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''',
'''',-sum(iDiscount),null,'''',null,-(case when sum(iNatSum) = 0 then 0 else sum(iNatDiscount)-sum(iNatTax)*sum(iNatDiscount)/sum(iNatSum) end),-(case when sum(iNatSum) = 0 then 0 else sum(iNatTax)*sum(iNatDiscount)/sum(iNatSum) end),'''',-sum(iNatDis
count),'''','''','''','''',null,null,null,-(case when sum(iSum) = 0 then 0 else sum(iDiscount)-sum(iTax)*sum(iDiscount)/sum(iSum) end) ,-(case when sum(iSum) = 0 then 0 else sum(iTax)*sum(iDiscount)/sum(iSum) end),'''',null,
null,'''',null,'''','''','''','''','''','''','''','''','''','''','''',null,null,null,null,'''','''',
'''','''','''','''','''',''折扣额'','''', '''' ,'''','''','''','''','''','''',null,'''','''','''','''','''',
'''','''','''','''' from Dispatchlists left join inventory_pos_ven as inventory ON dispatchlists.cInvCode=Inventory.cInvCode
where DLID = ' + @parm
set @HengDaSQL1 = ' select ((select max(autoid)from dispatchlists where DLID= ' + @parm + '))+1 as autoid ,'''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''','''',
'''',null,-(case when sum(iNatSum) = 0 then 0 else sum(iNatDiscount)-sum(iNatTax)*sum(iNatDiscount)/sum(iNatSum) end),'''','''',-(case when sum(iNatSum) = 0 then 0 else sum(iNatTax)*sum(iNatDiscount)/sum(iNatSum) end),-sum(iNatDiscount),null,-sum(iDis
count),'''','''','''','''',null,null,null,-(case when sum(iSum) = 0 then 0 else sum(iDiscount)-sum(iTax)*sum(iDiscount)/sum(iSum) end),-(case when sum(iSum) = 0 then 0 else sum(iTax)*sum(iDiscount)/sum(iSum) end),'''',null,
'''',null,'''','''',null,'''','''','''','''','''','''','''','''','''',null,null,null,null,null,'''',
'''','''','''','''',null,'''','''', ''折扣额'' ,'''','''','''','''','''','''','''','''','''','''','''','''',
'''','''','''','''' from Dispatchlists left join inventory_pos_ven as inventory ON dispatchlists.cInvCode=Inventory.cInvCode
where DLID = ' + @parm
if @PrintKinds = 'Nothing'
begin
set @strSQL =
'select
Dispatchlists.AutoID,Dispatchlists.iBatch,(case when Dispatchlists.bSettleAll=1 then ''完毕'' else ''未完毕'' end)as bSettleAll,
Dispatchlists.cDefine22,Dispatchlists.cBatch,Dispatchlists.cItemCode,Dispatchlists.cItem_class,Dispatchlists.cFree1,
Dispatchlists.cFree2,Dispatchlists.cWhCode,Dispatchlists.cInvCode,Dispatchlists.cDefine23,Dispatchlists.cItemName,
Dispatchlists.cItem_CName,Dispatchlists.cDefine24,Dispatchlists.cDefine25,Dispatchlists.cVenAbbName,Dispatchlists.iDLsID,
Dispatchlists.iSOsID,Dispatchlists.DLID,Dispatchlists.iCorID,'
if @IsHengDa = 'true'
set @strSQL = @strSQL + '(dispatchlists.iSum + dispatchlists.iDiscount) as iSum ,'
else
set @strSQL = @strSQL + 'dispatchlists.iSum,'
set @strSQL = @strSQL + 'Dispatchlists.iNatUnitPrice,Dispatchlists.KL,'
if @IsHengDa = 'true'
set @strSQL = @strSQL + ' null as iNatDiscount,'
else
set @strSQL = @strSQL + 'Dispatchlists.iNatDiscount,'
set @strSQL = @strSQL + '
Dispatchlists.iNatMoney,Dispatchlists.iNatTax,Dispatchlists.KL2,Dispatchlists.iNatSum,
(Case when Dispatchlists.iTb=0 then Dispatchlists.iNum else TBNum end ) as iNum,
(Case when Dispatchlists.iTb=0 then Dispatchlists.iQuantity else TBQuantity end) as iQuantity,
Dispatchlists.iSettleNum,Dispatchlists.iSettleQuantity,'
if @IsHengDa = 'true'
set @strSQL = @strSQL + 'case when (case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) = 0 then 0
else dispatchlists.iTaxUnitPrice + dispatchlists.iDiscount /(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) end as iTaxUnitPrice ,'
else
set @strSQL = @strSQL + 'dispatchlists.iTaxUnitPrice,'
set @strSQL = @strSQL + ' Dispatchlists.iQuotedPrice,'
if @IsHengDa = 'true'
set @strSQL = @strSQL + 'dispatchlists.iQuotedPrice as iUnitPrice,
(dispatchlists.iQuotedPrice)*(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) as iMoney,
(dispatchlists.iSum + dispatchlists.iDiscount)-(dispatchlists.iQuotedPrice)*(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) as iTax,
null as iDiscount,'
else
set @strSQL = @strSQL + 'dispatchlists.iUnitPrice ,
dispatchlists.iMoney ,
dispatchlists.iTax,
Dispatchlists.iDiscount,'
set @strSQL = @strSQL + '
Dispatchlists.iTaxRate,Dispatchlists.fSalePrice,
(Case when Dispatchlists.iTb=0 then ''正常'' else ''退补'' end ) as iTB,Dispatchlists.fSaleCost,
Dispatchlists.cDefine26,Dispatchlists.cDefine27,Dispatchlists.dVDate,
(case when Inventory.bService=1 then ''应税劳务'' else ''非应税劳务'' end)as bService,
(case when Inventory.bInvBatch=1 then ''是'' else ''否'' end)as bInvBatch,
(case when Inventory.bInvType=1 then ''是'' else ''否'' end)as bInvType,
Inventory.bFree1,Inventory.bFree2,Inventory.cQuality,Inventory.iInvWeight,Inventory.iVolume,Inventory.iInvRCost,
Inventory.iInvSPrice,Inventory.iInvSCost,Inventory.iInvNCost,Inventory.iInvAdvance,Inventory.iInvBatch,Inventory.iSafeNum,
Inventory.iTopSum,Inventory.iLowSum,Inventory.iInvMPCost,Inventory.cInvAddCode,Inventory.cInvName,Inventory.cInvStd,
Inventory.cInvM_Unit,Inventory.cInvA_Unit,Inventory.cInvDefine1,Inventory.cInvDefine2,Inventory.cInvDefine3,
Dispatchlists.iInvExchRate,Inventory.iVolume,Inventory.iInvLSCost,Inventory.iInvWeight,Inventory.cPosName,Inventory.cVenName,
Warehouse.cWhName,SO_SODetails.cSOCode,Warehouse.cWhValueStyle,dispatchlists.fOutQuantity,dispatchlists.fOutNum,DispatchLists.cMemo
from
(((dispatchlists left join inventory_pos_ven as inventory ON dispatchlists.cInvCode=Inventory.cInvCode)
left join Dispatchlist on dispatchlists.DLID=DispatchList.DLID)left join Warehouse on dispatchlists.cWhCode=Warehouse.cWhCode) left join SO_SODetails ON SO_SODetails.iSOsID = DispatchLists.iSOsID'
if @FDCondition = 'cWhName_cInvCCode'
set @strSQL = @strSQL +
' where dispatchlists.DLID=' + @parm + ' and dispatchlists.cWhCode + ''-'' + inventory.cInvCCode = ''' + @FDValue + ''''
else if @FDCondition = 'cWhName'
set @strSQL = @strSQL +
' where dispatchlists.DLID=' + @parm + ' and dispatchlists.cWhCode = ''' + @FDValue + ''''
else if @FDCondition = 'cInvCCode'
set @strSQL = @strSQL +
' where dispatchlists.DLID=' + @parm + ' and inventory.cInvCCode = ''' + @FDValue + ''''
else
set @strSQL = @strSQL +
' where dispatchlists.DLID=' + @parm
if @IsHengDa = 'True'
set @strSQL = @strSQL + 'union ' + @HengDaSQL
if @FDCondition = 'cWhName_cInvCCode'
set @strSQL = @strSQL + ' and dispatchlists.cWhCode + ''-'' + inventory.cInvCCode = ''' + @FDValue + ''''
else if @FDCondition = 'cWhName'
set @strSQL = @strSQL + ' and dispatchlists.cWhCode = ''' + @FDValue + ''''
else if @FDCondition = 'cInvCCode'
set @strSQL = @strSQL + ' and inventory.cInvCCode = ''' + @FDValue + ''''
set @strSQL = @strSQL + ' order by Dispatchlists.AutoID'
end
else if @PrintKinds = 'All'
begin
set @strSQL =
'select
max(dispatchlists.AutoID) as AutoID,
max(dispatchlists.iBatch) as iBatch,
case when count(distinct dispatchlists.bSettleAll)>1 then ''''
else (case when max(cast(dispatchlists.bSettleAll as int)) = 1 then ''完毕'' else ''未完毕'' end) end as bSettleAll,
max(dispatchlists.cDefine22) as cDefine22,
max(dispatchlists.cItemCode) as cItemCode,
max(dispatchlists.cBatch) as cBatch,
max(dispatchlists.cItem_class) as cItem_class,
case when count(distinct dispatchlists.cFree1) > 1 then ''''
else max(dispatchlists.cFree1) end as cFree1,
case when count(distinct dispatchlists.cFree2) > 1 then ''''
else max(dispatchlists.cFree2) end as cFree2,
max(dispatchlists.cWhCode) as cWhCode,
case when count(distinct dispatchlists.cInvCode) >1 then ''''
else max(dispatchlists.cInvCode) end as cInvCode,
max(dispatchlists.cDefine23) as cDefine23,
max(dispatchlists.cDefine24) as cDefine24,
max(dispatchlists.cItemName) as cItemName,
max(dispatchlists.cDefine25) as cDefine25,
max(dispatchlists.cItem_CName) as cItem_CName,
max(dispatchlists.cVenAbbName) as cVenAbbName,
max(dispatchlists.iSOsID) as iSOsID,
max(dispatchlists.iDLsID) as iDLsID,
max(dispatchlists.DLID) as DLID,
max(dispatchlists.iCorID) as iCorID,
case when sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) = 0 then 0
else sum(dispatchlists.iNatUnitPrice)/sum((case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end )) end as iNatUnitPrice,
sum(dispatchlists.iNatMoney) as iNatMoney,
max(dispatchlists.KL) as KL,
max(dispatchlists.KL2) as KL2,
sum(dispatchlists.iNatTax) as iNatTax,
sum(dispatchlists.iNatSum) as iNatSum,'
if @IsHengDa = 'true'
set @strSQL = @strSQL + ' null as iNatDiscount,
(sum(dispatchlists.iSum) + sum(dispatchlists.iDiscount)) as iSum ,'
else
set @strSQL = @strSQL + 'sum(dispatchlists.iNatDiscount) as iNatDiscount,sum(dispatchlists.iSum) as iSum,'
set @strSQL = @strSQL + '
sum(case when iTB=0 then dispatchlists.iNum else dispatchlists.TBNum end) as iNum ,
sum((case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end )) as iQuantity,
sum(dispatchlists.iSettleNum) as iSettleNum,
sum(dispatchlists.iSettleQuantity) as iSettleQuantity,
sum(dispatchlists.iQuotedPrice) as iQuotedPrice,'
--含税单价.无税单价.无税金额.税额四个的处理
if @IsHengDa = 'true'
set @strSQL = @strSQL + 'case when sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) = 0 then 0
else (sum(dispatchlists.iSum) + sum(dispatchlists.iDiscount))/sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) end as iTaxUnitPrice,
case when sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) = 0 then 0
else sum((dispatchlists.iSum + dispatchlists.iDiscount)/( 1 + dispatchlists.iTaxRate/100))/sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end )end as iUnitPrice,
sum((dispatchlists.iSum + dispatchlists.iDiscount)/( 1 + dispatchlists.iTaxRate/100)) as iMoney,
sum(dispatchlists.iSum) + sum(dispatchlists.iDiscount) - sum((dispatchlists.iSum + dispatchlists.iDiscount)/( 1 + dispatchlists.iTaxRate/100)) as iTax ,
null as iDiscount,'
else
set @strSQL = @strSQL + 'case when sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) = 0 then 0
else sum(dispatchlists.iSum)/sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) end as iTaxUnitPrice,
case when sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) = 0 then 0
else sum(dispatchlists.iSum)/sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) end as iUnitPrice,
sum(dispatchlists.iMoney) as iMoney ,
sum(dispatchlists.iTax) as iTax,
sum(dispatchlists.iDiscount) as iDiscount,'
set @strSQL = @strSQL + '
case when count(distinct dispatchlists.iTaxRate) > 1 then '''' else max(dispatchlists.iTaxRate)end as iTaxRate,
case when count(distinct dispatchlists.iTb)>1 then ''''
else (case when max(cast(dispatchlists.iTb as int)) = 1 then ''正常'' else ''退补'' end) end as iTB,
sum(dispatchlists.fSalePrice) as fSalePrice,
max(dispatchlists.cDefine26) as cDefine26,
max(dispatchlists.cDefine27) as cDefine27,
max(dispatchlists.fSalePrice) as fSaleCost,
max(dispatchlists.dVDate) as dVDate,
case when count (distinct inventory.bService)>1 then ''''
else (case when max(cast(inventory.bService as int)) = 1 then ''应税劳务'' else ''非应税劳务'' end) end as bService ,
case when count (distinct inventory.bInvBatch)>1 then ''''
else (case when (max(cast(inventory.bInvBatch as int)))=1 then ''是'' else ''否'' end) end as bInvBatch ,
case when (count(distinct inventory.bFree1)) >1 then '''' else max(cast(inventory.bFree1 as varchar(10))) end as bFree1,
case when count(distinct binvtype)>1 then ''是''
else '''' end as bInvType,
case when (count(distinct inventory.bFree2)) >1 then '''' else max(cast(inventory.bFree2 as varchar(10))) end as bFree2,
max(inventory.cQuality) as cQuality,max(inventory.iInvWeight) as iInvWeight,max(inventory.iVolume) as iVolume,max(inventory.iInvRCost) as iInvRCost,
max(inventory.iInvSPrice) as iInvSPrice,max(inventory.iInvSCost) as iInvSCost,max(inventory.iInvLSCost) as iInvLSCost,max(inventory.iInvNCost) as iInvNCost,max(inventory.iInvAdvance) as iInvAdvance,
max(inventory.iInvBatch) as iInvBatch,
max(inventory.iSafeNum) as iSafeNum,max(inventory.iTopSum) as iTopSum,max(inventory.iLowSum) as iLowSum,max(inventory.iInvMPCost) as iInvMPCost,max(inventory.cInvAddCode) as cInvAddCode,max(inventory.cInvDefine1) as cInvDefine1,
--货物名称特殊处理
case when '''+ @All_Name+ ''' = '''' then
case when (select count(distinct inventory.cInvName) from dispatchlists left join inventory_pos_ven as inventory ON dispatchlists.cInvCode=Inventory.cInvCode
where dispatchlists.DLID= ' + @parm + ') > 1 then '''' else max(inventory.cInvName) end
else ''' + @All_Name + ''' end as cInvName,
case when (select count(distinct inventory.cInvStd) from dispatchlists left join inventory_pos_ven as inventory ON dispatchlists.cInvCode=Inventory.cInvCode
left join Dispatchlist on dispatchlists.DLID=DispatchList.DLID where dispatchlists.DLID= ' + @parm + ') >1 then ''''
else max(inventory.cInvStd) end as cInvStd ,
case when count(distinct inventory.cInvM_Unit) > 1 then '''' else max(inventory.cInvM_Unit) end as cInvM_Unit,
case when count(distinct inventory.cInvA_Unit) > 1 then '''' else max(inventory.cInvA_Unit) end as cInvA_Unit,
max(inventory.cInvDefine2) as cInvDefine2,max(inventory.cInvDefine3) as cInvDefine3,
max(dispatchlists.iInvExchRate) AS iInvExchRate ,max(inventory.iVolume) as iVolume,
max(inventory.iInvWeight) as iInvWeight,max(inventory.cPosName) as cPosName,
max(inventory.cVenName) as cVenName,
case when count(distinct dispatchlists.cWhCode)>1 then ''''
else max(Warehouse.cWhName) end as cWhName, --仓库
max(SO_SODetails.cSOCode) as cSOCode,max(Warehouse.cWhValueStyle) as cWhValueStyle,
max(dispatchlists.fOutQuantity) as fOutQuantity,max(dispatchlists.fOutNum) as fOutNum,max(dispatchlists.cMemo) as cMemo
from
(((dispatchlists left join inventory_pos_ven as inventory ON dispatchlists.cInvCode=Inventory.cInvCode)
left join Dispatchlist on dispatchlists.DLID=DispatchList.DLID)left join Warehouse on dispatchlists.cWhCode=Warehouse.cWhCode)
left JOIN SO_SODetails ON DispatchLists.iSOsID=SO_SODetails.iSOsID
where dispatchlists.DLID= ' + @parm
if @IsHengDa = 'True'
begin
set @strSQL = @strSQL + ' union ' + @HengDaSQL1
end
end
else
begin
set @strSQL =
'select
max(dispatchlists.AutoID) as AutoID,max(dispatchlists.iBatch) as iBatch,
case when count(distinct dispatchlists.bSettleAll) >1 then ''''
else (case when max(cast(dispatchlists.bSettleAll as int)) = 1 then ''完毕'' else ''未完毕'' end) end as bSettleAll,
max(dispatchlists.cDefine22) as cDefine22,max(dispatchlists.cItemCode) as cItemCode,
max(dispatchlists.cBatch) as cBatch,max(dispatchlists.cItem_class) as cItem_class,
case when count(distinct dispatchlists.cFree1) > 1 then ''''
else max(dispatchlists.cFree1) end as cFree1,
case when count(distinct dispatchlists.cFree2) > 1 then ''''
else max(dispatchlists.cFree2) end as cFree2,
max(dispatchlists.cWhCode) as cWhCode,max(dispatchlists.cInvCode) as cInvCode,
max(dispatchlists.cDefine23) as cDefine23,max(dispatchlists.cDefine24) as cDefine24,
max(dispatchlists.cItemName) as cItemName,max(dispatchlists.cDefine25) as cDefine25,
max(dispatchlists.cItem_CName) as cItem_CName,max(dispatchlists.cVenAbbName) as cVenAbbName,
max(dispatchlists.iSOsID) as iSOsID,max(dispatchlists.iDLsID) as iDLsID,
max(dispatchlists.DLID) as DLID,max(dispatchlists.iCorID) as iCorID,
case when sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) = 0 then 0
else sum(dispatchlists.iNatUnitPrice)/sum((case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end )) end as iNatUnitPrice,
max(dispatchlists.iNatMoney) as iNatMoney,
max(dispatchlists.KL) as KL,max(dispatchlists.KL2) as KL2,
sum(dispatchlists.iNatTax) as iNatTax,sum(dispatchlists.iNatSum) as iNatSum,'
if @IsHengDa = 'true'
set @strSQL = @strSQL + 'null as iNatDiscount,(sum(dispatchlists.iSum) + sum(dispatchlists.iDiscount)) as iSum ,'
else
set @strSQL = @strSQL + 'sum(dispatchlists.iNatDiscount) as iNatDiscount,sum(dispatchlists.iSum) as iSum,'
set @strSQL = @strSQL + '
sum(case when iTB=0 then dispatchlists.iNum else dispatchlists.TBNum end) as iNum ,
sum((case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end )) as iQuantity,
sum(dispatchlists.iSettleNum) as iSettleNum,sum(dispatchlists.iSettleQuantity) as iSettleQuantity,
max(dispatchlists.iQuotedPrice) as iQuotedPrice,'
--含税单价.无税单价.无税金额.税额四个的处理
if @IsHengDa = 'true'
set @strSQL = @strSQL + 'case when sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) = 0 then 0
else (sum(dispatchlists.iSum) + sum(dispatchlists.iDiscount))/sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) end as iTaxUnitPrice,
case when sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) = 0 then 0
else sum((dispatchlists.iSum + dispatchlists.iDiscount)/( 1 + dispatchlists.iTaxRate/100))/sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end )end as iUnitPrice,
sum((dispatchlists.iSum + dispatchlists.iDiscount)/( 1 + dispatchlists.iTaxRate/100)) as iMoney,
sum(dispatchlists.iSum) + sum(dispatchlists.iDiscount) - sum((dispatchlists.iSum + dispatchlists.iDiscount)/( 1 + dispatchlists.iTaxRate/100)) as iTax ,
null as iDiscount,'
else
set @strSQL = @strSQL + 'case when sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) = 0 then 0
else sum(dispatchlists.iSum)/sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) end as iTaxUnitPrice,
case when sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) = 0 then 0
else sum(dispatchlists.iSum)/sum(case when iTB=0 then dispatchlists.iQuantity else dispatchlists.TBQuantity end ) end as iUnitPrice,
sum(dispatchlists.iMoney) as iMoney ,
sum(dispatchlists.iTax) as iTax,
sum(dispatchlists.iDiscount) as iDiscount,'
set @strSQL = @strSQL + '
case when count(distinct dispatchlists.iTaxRate) > 1 then '''' else max(dispatchlists.iTaxRate)end as iTaxRate,
case when count(distinct dispatchlists.iTb)>1 then ''''
else (case when max(cast(dispatchlists.iTb as int)) = 1 then ''正常'' else ''退补'' end) end as iTB,
sum(dispatchlists.fSalePrice) as fSalePrice,
max(dispatchlists.cDefine26) as cDefine26,max(dispatchlists.cDefine27) as cDefine27,
max(dispatchlists.fSalePrice) as fSaleCost,
max(dispatchlists.dVDate) as dVDate,
case when count (distinct inventory.bService) >1 then ''''
else (case when max(cast(inventory.bService as int)) = 1 then ''应税劳务'' else ''非应税劳务'' end) end as bService ,
case when count (distinct inventory.bInvBatch) >1 then ''''
else (case when (max(cast(inventory.bInvBatch as int)))=1 then ''是'' else ''否'' end) end as bInvBatch ,
case when (count(distinct inventory.bFree1)) >1 then '''' else max(cast(inventory.bFree1 as varchar(10))) end as bFree1,
case when count(distinct binvtype ) >1 then ''是''
else '''' end as bInvType,
case when (count(distinct inventory.bFree2)) >1 then '''' else max(cast(inventory.bFree2 as varchar(10))) end as bFree2,
max(inventory.cQuality) as cQuality,max(inventory.iInvWeight) as iInvWeight,max(inventory.iVolume) as iVolume,max(inventory.iInvRCost) as iInvRCost,
max(inventory.iInvSPrice) as iInvSPrice,max(inventory.iInvSCost) as iInvSCost,max(inventory.iInvLSCost) as iInvLSCost,max(inventory.iInvNCost) as iInvNCost,max(inventory.iInvAdvance) as iInvAdvance,
max(inventory.iInvBatch) as iInvBatch,
max(inventory.iSafeNum) as iSafeNum,max(inventory.iTopSum) as iTopSum,max(inventory.iLowSum) as iLowSum,max(inventory.iInvMPCost) as iInvMPCost,max(inventory.cInvAddCode) as cInvAddCode,max(inventory.cInvDefine1) as cInvDefine1,'
if @PrintKinds = 'CargoKinds'
set @strSQL = @strSQL + ' max(InventoryClass.cInvCName) as cInvName, '
else
set @strSQL = @strSQL + ' max(inventory.cInvName) as cInvName, '
set @strSQL = @strSQL +
'case when count(distinct inventory.cInvStd) >1 then '''' else max(inventory.cInvStd) end as cInvStd ,
case when count(distinct inventory.cInvM_Unit) > 1 then '''' else max(inventory.cInvM_Unit) end as cInvM_Unit,
case when count(distinct inventory.cInvA_Unit) > 1 then '''' else max(inventory.cInvA_Unit) end as cInvA_Unit,
max(inventory.cInvDefine2) as cInvDefine2,max(inventory.cInvDefine3) as cInvDefine3,
case when count(distinct dispatchlists.iInvExchRate)>1 then ''''
else max(dispatchlists.iInvExchRate) end as iInvExchRate,
max(inventory.iVolume) as iVolume,max(inventory.iInvWeight) as iInvWeight,max(inventory.cPosName) as cPosName,max(inventory.cVenName) as cVenName,
case when count(distinct dispatchlists.cWhCode) >1 then '''' else max(Warehouse.cWhName)end as cWhName,
max(SO_SODetails.cSOCode) as cSOCode,max(Warehouse.cWhValueStyle) as cWhValueStyle,max(dispatchlists.fOutQuantity) as fOutQuantity,max(dispatchlists.fOutNum) as fOutNum,max(dispatchlists.cMemo) as cMemo
from
(((dispatchlists left join inventory_pos_ven as inventory ON dispatchlists.cInvCode=Inventory.cInvCode)
left join Dispatchlist on dispatchlists.DLID=DispatchList.DLID)left join Warehouse on dispatchlists.cWhCode=Warehouse.cWhCode)
left JOIN SO_SODetails ON DispatchLists.iSOsID=SO_SODetails.iSOsID '
if @PrintKinds = 'CargoName_Price'
set @strSQL = @strSQL +' where dispatchlists.DLID= ' + @parm + '
group by dispatchlists.cInvCode , dispatchlists.iUnitPrice'
else if @PrintKinds = 'CargoKinds'
set @strSQL = @strSQL +' left join InventoryClass on InventoryClass.cInvCCode = Inventory.cInvCCode
where dispatchlists.DLID= ' + @parm + '
group by inventory.cInvCCode'
else if @PrintKinds = 'CargoCode_Free'
set @strSQL = @strSQL +' where dispatchlists.DLID= ' + @parm + '
group by dispatchlists.cFree1,dispatchlists.cFree2,dispatchlists.cInvCode'
if @IsHengDa = 'True'
begin
set @strSQL = @strSQL + ' union ' + @HengDaSQL1
end
set @strSQL = @strSQL + ' order by max( Dispatchlists.AutoID) '
end
end
exec (@strSQL)
--合并文件：C:\bak\Tong10.5Plus1\BZB\Do\合并脚本\script\UFDATA\PatchScript\DATA_ALTER_AR_客户发货到期应收款对帐单.sql
--文件时间：2009-10-15 10:01:22
--合并时间：2011-01-12 10:11:46
-----------菜单中增加 客户发货到期应收款对帐单 jyw
--if not exists ( select * from UFSystem..UA_SysMenu where cMenuId = 'AR050307')
--INSERT INTO UFSystem..UA_SysMenu(cPosition, cMenuId, cMenuName, cSubId, iLevel, bVisible, cMemo)
--VALUES('0011-0015-0007','AR050307','客户发货到期应收款对帐单','SA',3,1,NULL)
--GO
-----------客户发货到期应收款对帐单 权限预置表
--if not exists ( select * from UFSystem..UA_Auth where cAuth_Id = 'AR050307')
--insert into UFSystem..UA_Auth (cAuth_Id,cAuth_Name)
--values('AR050307','客户发货到期应收款对帐单')
--GO
-----------工作台增加按钮
--delete from ufsystem..ua_UserFlow where cKey = 'AR050307'
--declare @iorder int
--select @iorder = max(iorder) + 1 from UFSystem..Ua_SysFlow where cSysID = 'SA'
--if not exists(select * from UFSystem..UA_SysFlow where cSysID = 'SA' and cKey = 'AR050307' )
--INSERT INTO UFSystem..UA_SysFlow(cType, cSysID, cKey, ccaption, bDefault, cTitle, bvisible, iorder, cEntType)
--VALUES('FOOT','SA','AR050307','客户发货到期应收款对帐单',1,NULL,0,@iorder,NULL)
--GO
---------------------报表查询定义
IF NOT EXISTS(SELECT * FROM Rpt_GlbDEF WHERE SystemID = 'SA' and name = '客户发货到期应收款对帐单')
INSERT INTO Rpt_GlbDEF( SystemID, Name, Title, DatabaseEx, WhereEx, Predicate, Type, Relation, Note, Cols, Section, Fields, DataSource, BaseTable, IsBaseTable, IsControlCreate, IsBase, GroupDefine)
VALUES('SA','客户发货到期应收款对帐单','客户发货到期应收款对帐单',NULL,NULL,NULL,42,'Heron',NULL,0,0,0,NULL,NULL,0,0,1,NULL)
销售普通发票
CREATE PROCEDURE [dbo].[NewPrint_SaleBillVouchP_Title]
@parm varchar(100)
AS
begin
select
SaleBillVouch.SBVID,
(case when SaleBillVouch.bReturnFlag=1 then '负发票' else '正发票' end)as bReturnFlag,
(case when SaleBillVouch.bFirst=1 then '期初' else '不是期初' end)as bFirst,
(case when SaleBillVouch.bDisFlag=1 then '是' else '否' end)as bDisFlag,
(case when SaleBillVouch.iDisp=1 then '先开发货单' else '先开发票' end)as iDisp,
SaleBillVouch.cInvalider,SaleBillVouch.cDefine9,SaleBillVouch.cBCode,SaleBillVouch.cDefine10,SaleBillVouch.cexch_name,
SaleBillVouch.cDefine8,SaleBillVouch.cBusType,SaleBillVouch.cMemo,SaleBillVouch.cMaker,SaleBillVouch.cVerifier,
SaleBillVouch.citem_class,SaleBillVouch.citemcode,SaleBillVouch.cBillVer,SaleBillVouch.cPersonCode,SaleBillVouch.cSBVCode,
SaleBillVouch.cVouchType,SaleBillVouch.cSTCode,SaleBillVouch.cDepCode,SaleBillVouch.cCusCode,SaleBillVouch.cPayCode,
SaleBillVouch.cSOCode,SaleBillVouch.cDLCode,SaleBillVouch.cDefine1,SaleBillVouch.cDefine2,SaleBillVouch.cAccounter,
SaleBillVouch.cDefine3,SaleBillVouch.cChecker,SaleBillVouch.cDefine5,SaleBillVouch.cDefine7,SaleBillVouch.iTaxRate,
SaleBillVouch.iExchRate,SaleBillVouch.cDefine4,SaleBillVouch.cDefine6,SaleBillVouch.dDate,SaleBillVouch.dVerifyDate,
customer.cCusAbbName,customer.cCusAddress,customer.cCusPhone,customer.cCusFax,customer.cCusBank,customer.cCusAccount,
customer.cCusOAddress,customer.cCusPayCond,customer.cCusOType,customer.cCusName,customer.cCusRegCode,customer.iCusCreDate,
customer.cCusCreGrade,customer.iARMoney,customer.cCusDepart,customer.cCusPPerson,customer.dCusDevDate,customer.cCusLPerson,
customer.cCusRegCode,customer.cCusBank,customer.cCusAccount,customer.cCusEmail,customer.cCusAddress,customer.cCusPostCode,
customer.cCusPhone,customer.cCusFax,customer.cCusBP,customer.cCusHand,customer.cCusPerson,customer.cCusOAddress,
customer.cCusOType,customer.cCusWhCode,customer.iCusCreLine,
SaleType.cSTName,Department.cDepName,Person.cPersonName,PayCondition.cPayName,Bank.cBName,Bank.cBAccount
,SaleBillVouch.cZQCode,SaleBillVouch.dZQDate,AccountPeriod.APName,Customer.cCusDefine1,Customer.cCusDefine2,Customer.cCusDefine3,
salebillvouch.iPrnNum +1 as iPrnNum
from
(((((SaleBillVouch left join Customer on SaleBillVouch.cCusCode=Customer.cCusCode)left join SaleType on SaleBillVouch.cSTCode=SaleType.cSTCode)left join Department on SaleBillVouch.cDepCode=Department.cDepCode)left join Person on SaleBillVouch.cPersonCode
=Person.cPersonCode)left join PayCondition on SaleBillVouch.cPayCode=PayCondition.cPayCode)left join Bank on SaleBillVouch.cBCode=Bank.cBCode
left join AccountPeriod on SaleBillVouch.cZQCode = AccountPeriod.APCode
where SaleBillVouch.SBVID=@parm
end
销售专用发票
CREATE PROCEDURE [dbo].[NewPrint_SaleBillVouchZ_Title]
@parm varchar(100)
AS
begin
select
(case when SaleBillVouch.iDisp=1 then '先开发货单' else '先开发票' end)as iDisp,SaleBillVouch.SBVID,
(case when SaleBillVouch.bReturnFlag=1 then '负发票' else '正发票' end)as bReturnFlag,
(case when SaleBillVouch.bFirst=1 then '期初' else '不是期初' end)as bFirst,
(case when SaleBillVouch.bDisFlag=1 then '是' else '否' end)as bDisFlag,
SaleBillVouch.cDefine10,SaleBillVouch.cInvalider,SaleBillVouch.cDefine8,SaleBillVouch.cBCode,SaleBillVouch.cDefine9,
SaleBillVouch.cexch_name,SaleBillVouch.cBusType,SaleBillVouch.cMemo,SaleBillVouch.cChecker,SaleBillVouch.cMaker,
SaleBillVouch.cVerifier,SaleBillVouch.citem_class,SaleBillVouch.citemcode,SaleBillVouch.cBillVer,SaleBillVouch.cSBVCode,
SaleBillVouch.cPayCode,SaleBillVouch.cPersonCode,SaleBillVouch.cSTCode,SaleBillVouch.cDepCode,SaleBillVouch.cCusCode,
SaleBillVouch.cVouchType,SaleBillVouch.cSOCode,SaleBillVouch.cDLCode,SaleBillVouch.cDefine1,SaleBillVouch.cDefine2,
SaleBillVouch.cAccounter,SaleBillVouch.cDefine3,SaleBillVouch.cDefine5,SaleBillVouch.cDefine7,SaleBillVouch.iTaxRate,
SaleBillVouch.iExchRate,SaleBillVouch.cDefine6,SaleBillVouch.cDefine4,SaleBillVouch.dDate,SaleBillVouch.dVerifyDate,
customer.cCusAbbName,customer.cCusAddress,customer.cCusPhone,customer.cCusFax,customer.cCusBank,customer.cCusAccount,
customer.cCusOAddress,customer.cCusPayCond,customer.cCusOType,customer.cCusName,customer.cCusRegCode,customer.iCusCreDate,
customer.cCusCreGrade,customer.iARMoney,customer.cCusDepart,customer.cCusPPerson,customer.dCusDevDate,customer.cCusLPerson,
customer.cCusRegCode,customer.cCusBank,customer.cCusAccount,customer.cCusEmail,customer.cCusAddress,customer.cCusPostCode,
customer.cCusPhone,customer.cCusFax,customer.cCusBP,customer.cCusHand,customer.cCusPerson,customer.cCusOAddress,
customer.cCusOType,customer.cCusWhCode,customer.iCusCreLine,SaleType.cSTName,Department.cDepName,Person.cPersonName,
PayCondition.cPayName,Bank.cBName,Bank.cBAccount
,SaleBillVouch.cZQCode,SaleBillVouch.dZQDate,AccountPeriod.APName,Customer.cCusDefine1,Customer.cCusDefine2,Customer.cCusDefine3,
SaleBillVouch.iPrnNum +1 as iPrnNum
from
(((((SaleBillVouch left join customer on SaleBillVouch.cCusCode=customer.cCusCode)left join SaleType on SaleBillVouch.cSTCode=SaleType.cSTCode)left join Department on SaleBillVouch.cDepCode=Department.cDepCode)left join Person on SaleBillVouch.cPersonCode
=Person.cPersonCode)left join PayCondition on SaleBillVouch.cPayCode=PayCondition.cPayCode)left join Bank on SaleBillVouch.cBCode=Bank.cBCode
left join AccountPeriod on SaleBillVouch.cZQCode = AccountPeriod.APCode
where SaleBillVouch.SBVID=@parm
end
销售普通发票（红字）
CREATE PROCEDURE [dbo].[NewPrint_SaleBillVouchP_Title]
@parm varchar(100)
AS
begin
select
SaleBillVouch.SBVID,
(case when SaleBillVouch.bReturnFlag=1 then '负发票' else '正发票' end)as bReturnFlag,
(case when SaleBillVouch.bFirst=1 then '期初' else '不是期初' end)as bFirst,
(case when SaleBillVouch.bDisFlag=1 then '是' else '否' end)as bDisFlag,
(case when SaleBillVouch.iDisp=1 then '先开发货单' else '先开发票' end)as iDisp,
SaleBillVouch.cInvalider,SaleBillVouch.cDefine9,SaleBillVouch.cBCode,SaleBillVouch.cDefine10,SaleBillVouch.cexch_name,
SaleBillVouch.cDefine8,SaleBillVouch.cBusType,SaleBillVouch.cMemo,SaleBillVouch.cMaker,SaleBillVouch.cVerifier,
SaleBillVouch.citem_class,SaleBillVouch.citemcode,SaleBillVouch.cBillVer,SaleBillVouch.cPersonCode,SaleBillVouch.cSBVCode,
SaleBillVouch.cVouchType,SaleBillVouch.cSTCode,SaleBillVouch.cDepCode,SaleBillVouch.cCusCode,SaleBillVouch.cPayCode,
SaleBillVouch.cSOCode,SaleBillVouch.cDLCode,SaleBillVouch.cDefine1,SaleBillVouch.cDefine2,SaleBillVouch.cAccounter,
SaleBillVouch.cDefine3,SaleBillVouch.cChecker,SaleBillVouch.cDefine5,SaleBillVouch.cDefine7,SaleBillVouch.iTaxRate,
SaleBillVouch.iExchRate,SaleBillVouch.cDefine4,SaleBillVouch.cDefine6,SaleBillVouch.dDate,SaleBillVouch.dVerifyDate,
customer.cCusAbbName,customer.cCusAddress,customer.cCusPhone,customer.cCusFax,customer.cCusBank,customer.cCusAccount,
customer.cCusOAddress,customer.cCusPayCond,customer.cCusOType,customer.cCusName,customer.cCusRegCode,customer.iCusCreDate,
customer.cCusCreGrade,customer.iARMoney,customer.cCusDepart,customer.cCusPPerson,customer.dCusDevDate,customer.cCusLPerson,
customer.cCusRegCode,customer.cCusBank,customer.cCusAccount,customer.cCusEmail,customer.cCusAddress,customer.cCusPostCode,
customer.cCusPhone,customer.cCusFax,customer.cCusBP,customer.cCusHand,customer.cCusPerson,customer.cCusOAddress,
customer.cCusOType,customer.cCusWhCode,customer.iCusCreLine,
SaleType.cSTName,Department.cDepName,Person.cPersonName,PayCondition.cPayName,Bank.cBName,Bank.cBAccount
,SaleBillVouch.cZQCode,SaleBillVouch.dZQDate,AccountPeriod.APName,Customer.cCusDefine1,Customer.cCusDefine2,Customer.cCusDefine3,
salebillvouch.iPrnNum +1 as iPrnNum
from
(((((SaleBillVouch left join Customer on SaleBillVouch.cCusCode=Customer.cCusCode)left join SaleType on SaleBillVouch.cSTCode=SaleType.cSTCode)left join Department on SaleBillVouch.cDepCode=Department.cDepCode)left join Person on SaleBillVouch.cPersonCode
=Person.cPersonCode)left join PayCondition on SaleBillVouch.cPayCode=PayCondition.cPayCode)left join Bank on SaleBillVouch.cBCode=Bank.cBCode
left join AccountPeriod on SaleBillVouch.cZQCode = AccountPeriod.APCode
where SaleBillVouch.SBVID=@parm
end
销售专用发票（红字）
CREATE PROCEDURE [dbo].[NewPrint_SaleBillVouchZ_Title]
@parm varchar(100)
AS
begin
select
(case when SaleBillVouch.iDisp=1 then '先开发货单' else '先开发票' end)as iDisp,SaleBillVouch.SBVID,
(case when SaleBillVouch.bReturnFlag=1 then '负发票' else '正发票' end)as bReturnFlag,
(case when SaleBillVouch.bFirst=1 then '期初' else '不是期初' end)as bFirst,
(case when SaleBillVouch.bDisFlag=1 then '是' else '否' end)as bDisFlag,
SaleBillVouch.cDefine10,SaleBillVouch.cInvalider,SaleBillVouch.cDefine8,SaleBillVouch.cBCode,SaleBillVouch.cDefine9,
SaleBillVouch.cexch_name,SaleBillVouch.cBusType,SaleBillVouch.cMemo,SaleBillVouch.cChecker,SaleBillVouch.cMaker,
SaleBillVouch.cVerifier,SaleBillVouch.citem_class,SaleBillVouch.citemcode,SaleBillVouch.cBillVer,SaleBillVouch.cSBVCode,
SaleBillVouch.cPayCode,SaleBillVouch.cPersonCode,SaleBillVouch.cSTCode,SaleBillVouch.cDepCode,SaleBillVouch.cCusCode,
SaleBillVouch.cVouchType,SaleBillVouch.cSOCode,SaleBillVouch.cDLCode,SaleBillVouch.cDefine1,SaleBillVouch.cDefine2,
SaleBillVouch.cAccounter,SaleBillVouch.cDefine3,SaleBillVouch.cDefine5,SaleBillVouch.cDefine7,SaleBillVouch.iTaxRate,
SaleBillVouch.iExchRate,SaleBillVouch.cDefine6,SaleBillVouch.cDefine4,SaleBillVouch.dDate,SaleBillVouch.dVerifyDate,
customer.cCusAbbName,customer.cCusAddress,customer.cCusPhone,customer.cCusFax,customer.cCusBank,customer.cCusAccount,
customer.cCusOAddress,customer.cCusPayCond,customer.cCusOType,customer.cCusName,customer.cCusRegCode,customer.iCusCreDate,
customer.cCusCreGrade,customer.iARMoney,customer.cCusDepart,customer.cCusPPerson,customer.dCusDevDate,customer.cCusLPerson,
customer.cCusRegCode,customer.cCusBank,customer.cCusAccount,customer.cCusEmail,customer.cCusAddress,customer.cCusPostCode,
customer.cCusPhone,customer.cCusFax,customer.cCusBP,customer.cCusHand,customer.cCusPerson,customer.cCusOAddress,
customer.cCusOType,customer.cCusWhCode,customer.iCusCreLine,SaleType.cSTName,Department.cDepName,Person.cPersonName,
PayCondition.cPayName,Bank.cBName,Bank.cBAccount
,SaleBillVouch.cZQCode,SaleBillVouch.dZQDate,AccountPeriod.APName,Customer.cCusDefine1,Customer.cCusDefine2,Customer.cCusDefine3,
SaleBillVouch.iPrnNum +1 as iPrnNum
from
(((((SaleBillVouch left join customer on SaleBillVouch.cCusCode=customer.cCusCode)left join SaleType on SaleBillVouch.cSTCode=SaleType.cSTCode)left join Department on SaleBillVouch.cDepCode=Department.cDepCode)left join Person on SaleBillVouch.cPersonCode
=Person.cPersonCode)left join PayCondition on SaleBillVouch.cPayCode=PayCondition.cPayCode)left join Bank on SaleBillVouch.cBCode=Bank.cBCode
left join AccountPeriod on SaleBillVouch.cZQCode = AccountPeriod.APCode
where SaleBillVouch.SBVID=@parm
end
库存
产成品入库单
CREATE PROCEDURE [dbo].[NewPrint_RecordInVouch_Title]
@parm varchar(100)
AS
begin
select
(case when Rdrecord.bRdFlag=1 then '收' else '不收' end)as bRdFlag,
Rdrecord.cVouchType,Rdrecord.cWhCode,Rdrecord.cCode,Rdrecord.cRdCode,Rdrecord.cDepCode,Rdrecord.cPersonCode,Rdrecord.cBusCode,
Rdrecord.cProBatch,Rdrecord.cHandler,Rdrecord.cMemo,Rdrecord.cAccounter,Rdrecord.cMaker,Rdrecord.cDefine1,Rdrecord.cDefine2,
Rdrecord.cDefine3,Rdrecord.cDefine8,Rdrecord.cDefine9,Rdrecord.cDefine10,Rdrecord.cSource,Rdrecord.cBusType,
Rdrecord.cDefine5,
Rdrecord.ID,Rdrecord.cDefine7,Rdrecord.cDefine6,Rdrecord.cDefine4,Rdrecord.dVeriDate,Rdrecord.dDate,
Warehouse.cWhName,Department.cDepName,Person.cPersonName,Rd_Style.cRdName,RdRecord.iPrnNum + 1 as iPrnNum
from
(((Rdrecord left join Warehouse on Rdrecord.cWhCode=Warehouse.cWhCode)left join Department on Rdrecord.cDepCode=Department.cDepCode)left join Person on Rdrecord.cPersonCode=Person.cPersonCode)left join Rd_Style on Rdrecord.cRdCode=Rd_Style.cRdCode
where Rdrecord.ID=@parm
end
--合并文件：C:\bak\Tong10.5Plus1\BZB\Do\合并脚本\script\UFDATA\PatchScript\DATA_ALTER_SA_应收明细帐查询.sql
--文件时间：2009-10-15 10:27:12
--合并时间：2011-01-12 10:11:46
--删除模板中数据
delete from gl_myoutput where cauth = 'AR_UfGrid_mx' and csqrmode = 0
其他入库单
--- 其他入库单
CREATE PROCEDURE [dbo].[newprint_OtherInVouch_Body]
@parm varchar(100)
AS
begin
select
Inventory.cInvAddCode,Inventory.cInvName,Inventory.cInvStd,Inventory.cInvA_Unit,Inventory.cInvM_Unit,Inventory.cReplaceItem,
Inventory.cInvDefine1,Inventory.cInvDefine2,Inventory.cInvDefine3,Inventory.cPosName as cPosName_Inv,Inventory.cVenName,
Inventory.iInvSPrice,Inventory.iInvSCost,Inventory.iInvNCost,Inventory.iInvLSCost,Inventory.iInvMPCost,Inventory.cVenCode,
Inventory.iTopSum,Inventory.iLowSum,Inventory.iSafeNum,Inventory.iInvWeight,Inventory.iVolume,Inventory.cQuality,
(case when (rdrecords.iNum=0 or rdrecords.iNum is null)then null else rdrecords.iInvExchRate end)as iInvExchRate,
rdrecords.cInvCode,rdrecords.cFree1,rdrecords.cFree2,rdrecords.cBatch,rdrecords.cVouchCode,
rdrecords.cdefine22,rdrecords.cdefine23,rdrecords.cdefine24,rdrecords.cdefine25,rdrecords.cItemCode,rdrecords.cName,
rdrecords.cItem_class,rdrecords.cItemCName,rdrecords.AutoID,rdrecords.ID,rdrecords.iNum,rdrecords.iQuantity,
rdrecords.iUnitCost,rdrecords.iSoutQuantity,rdrecords.iPrice,rdrecords.iPUnitCost,rdrecords.iPPrice,rdrecords.cdefine26,
rdrecords.cdefine27,rdrecords.iTrIds,rdrecords.iSoutNum,rdrecords.dVDate,
Position.cPosName
from
(rdrecords left join inventory_pos_ven as inventory ON rdrecords.cInvCode = Inventory.cInvCode)left join Position on
rdrecords.cPosition=Position.cPosCode
where Rdrecords.ID=@parm order by rdrecords.AutoID
end
材料出库单
CREATE PROCEDURE [dbo].[NewPrint_RecordOutVouch_Title]
@parm varchar(100)
AS
begin
select
(case when Rdrecord.bRdFlag=1 then '发' else '不发' end)as bRdFlag,
Rdrecord.cVouchType,Rdrecord.cWhCode,Rdrecord.cBusCode,Rdrecord.cCode,Rdrecord.cRdCode,Rdrecord.cDepCode,Rdrecord.cPersonCode,Rdrecord.cProBatch,Rdrecord.cHandler,Rdrecord.cMemo,Rdrecord.cAccounter,Rdrecord.cMaker,Rdrecord.cDefine1,Rdrecord.cDefine2,
Rdrecord.cDefine3,Rdrecord.cDefine8,Rdrecord.cDefine9,Rdrecord.cDefine10,Rdrecord.cSource,Rdrecord.cBusType,Rdrecord.cDefine5,Rdrecord.ID,Rdrecord.cDefine7,Rdrecord.cDefine6,Rdrecord.cDefine4,Rdrecord.dVeriDate,Rdrecord.dDate,
Warehouse.cWhName,
Department.cDepName,
Person.cPersonName,
Rd_Style.cRdName,Rdrecord.iPrnNum + 1 as iPrnNum
from
(((Rdrecord left join Warehouse on Rdrecord.cWhCode=Warehouse.cWhCode)left join Department on Rdrecord.cDepCode=Department.cDepCode)left join Person on Rdrecord.cPersonCode=Person.cPersonCode)left join Rd_Style on Rdrecord.cRdCode=Rd_Style.cRdCode
where Rdrecord.ID=@parm
end
其它出库单
CREATE PROCEDURE NewPrint_OtherOutVouch_Title
@parm varchar(100)
AS
begin
select
(case when Rdrecord.bRdFlag=1 then '发' else '不发' end)as bRdFlag,
Rdrecord.cVouchType,Rdrecord.cBusType,Rdrecord.cBusCode,Rdrecord.cWhCode,Rdrecord.cCode,Rdrecord.cRdCode,Rdrecord.cDepCode,
Rdrecord.cPersonCode,Rdrecord.cCusCode,Rdrecord.cHandler,Rdrecord.cMemo,Rdrecord.cAccounter,Rdrecord.cMaker,
Rdrecord.cDefine1,Rdrecord.cDefine2,Rdrecord.cDefine3,Rdrecord.cDefine8,Rdrecord.cDefine9,Rdrecord.cDefine10,
Rdrecord.cSource,
Rdrecord.cDefine5,Rdrecord.ID,Rdrecord.cDefine7,Rdrecord.cDefine6,Rdrecord.cDefine4,Rdrecord.dVeriDate,Rdrecord.dDate,
VouchType.cVouchName,Warehouse.cWhName,Rd_Style.cRdName,Department.cDepName,Person.cPersonName,Customer.cCusAbbName
from
(((((Rdrecord left join VouchType on Rdrecord.cVouchType=VouchType.cVouchType)left join Warehouse on Rdrecord.cWhCode=Warehouse.cWhCode)left join Rd_Style on Rdrecord.cRdCode=Rd_Style.cRdCode)left join Department on Rdrecord.cDepCode=Department.cDepCode)l
eft join Person on Rdrecord.cPersonCode=Person.cPersonCode)left join Customer on Rdrecord.cCusCode=Customer.cCusCode
where Rdrecord.ID=@parm
end
调拨单
CREATE PROCEDURE NewPrint_TransVouch_Title
@parm varchar(100)
AS
begin
select
transvouch.cTVCode,transvouch.cODepCode,transvouch.cIDepCode,transvouch.cIRdCode,transvouch.cORdCode,transvouch.cTVMemo,
transvouch.cPersonCode,transvouch.cDefine1,transvouch.cDefine2,transvouch.cDefine3,transvouch.cDefine9,transvouch.cDefine8,
transvouch.cDefine10,transvouch.cAccounter,transvouch.cMaker,transvouch.cOWhCode,transvouch.cIWhCode,transvouch.cDefine5,
transvouch.cDefine7,transvouch.cDefine6,transvouch.cDefine4,transvouch.dTVDate,
Person.cPersonName,Department_IN.cDepName as cDepName,Department_OUT.CdepName as cDepName_1,Rd_Style_In.cRdName as cRdName,Rd_Style_Out.CRdName as cRdName_1,
Warehouse_In.cWhName as cWhName_1,Warehouse_Out.CWhName as cWhName
from
((((((transvouch left join Person on transvouch.cPersonCode=Person.cPersonCode)left join Department as Department_IN on transvouch.cIDepCode=Department_IN.cDepCode)left join Department as Department_OUT on transvouch.cODepCode=Department_OUT.cDepCode)left
join Rd_Style as Rd_Style_In on transvouch.cIRdCode=Rd_Style_In.cRdCode)left join Rd_Style as Rd_Style_Out on transvouch.cORdCode=Rd_Style_Out.cRdCode)left join Warehouse as Warehouse_In on transvouch.cIWhCode=Warehouse_In.cWhCode)left join Warehouse as
Warehouse_Out on transvouch.cOWhCode=Warehouse_Out.cWhCode
where transvouch.cTVCode=@parm
end
盘点单
--- 盘点单
CREATE PROCEDURE [dbo].[NewPrint_CheckVouch_Body]
@parm varchar(100)
AS
begin
select
checkvouchs.cInvCode,checkvouchs.cFree1,checkvouchs.cFree2,checkvouchs.cCVBatch,checkvouchs.cCVReason,checkvouchs.cCVCode,
checkvouchs.cdefine22,checkvouchs.cdefine23,checkvouchs.cdefine24,checkvouchs.cdefine25,
checkvouchs.cItemCode,checkvouchs.cName,checkvouchs.cItem_class,checkvouchs.cItemCName,checkvouchs.RdsID,checkvouchs.iCVCNum,
checkvouchs.iCVCQuantity,(case when checkvouchs.iJhdj is null then 0 else checkvouchs.iJhdj end) as iJhdj,
checkvouchs.iJhJe,checkvouchs.iCVNum,checkvouchs.iSjDJ,
checkvouchs.iCVQuantity,checkvouchs.iSjJe,checkvouchs.cdefine27,checkvouchs.cdefine26,checkvouchs.dDisDate,
Inventory.cInvAddCode,Inventory.cInvName,Inventory.cInvStd,Inventory.cInvM_Unit,Inventory.cReplaceItem,Inventory.cInvDefine1,
Inventory.cInvDefine2,Inventory.cInvDefine3,Inventory.cInvA_Unit,Inventory.cPosName as cPosName_Inv,
Inventory.iInvSPrice,Inventory.iInvSCost,Inventory.iInvNCost,Inventory.iInvLSCost,Inventory.iInvMPCost,Inventory.cVenCode,
Inventory.iTopSum,Inventory.iLowSum,Inventory.iSafeNum,Inventory.iInvWeight,Inventory.iVolume,Inventory.cQuality,
(CASE WHEN (checkvouchs.iCVCNum=0 Or checkvouchs.iCVCNum IS NULL) THEN null ELSE checkvouchs.iInvExchRate END) AS iInvExchRate,
Position.cPosName
from
(checkvouchs left join inventory_pos_ven as inventory on checkvouchs.cInvCode=Inventory.cInvCode)left join Position on checkvouchs.cPosition=Position.cPosCode
where checkvouchs.cCVCode=@parm order by checkvouchs.AutoID
end
核算
入库调整单
CREATE PROCEDURE NewPrint_JustInVouch_Title
@parm varchar(100)
AS
begin
select
(case when JustInVouch.bRdFlag=1 then '收' else '不收' end)as bRdFlag,
JustInVouch.cHandler,JustInVouch.cAccounter,JustInVouch.cMaker,JustInVouch.cJVMemo,JustInVouch.cJVDefine1,
JustInVouch.cJVDefine2,JustInVouch.cJVDefine3,JustInVouch.cJVCode,JustInVouch.cVouchType,JustInVouch.cWhCode,
JustInVouch.cUnitCode,
JustInVouch.cDepCode,JustInVouch.cPersonCode,JustInVouch.cRdCode,JustInVouch.cDefine1,JustInVouch.cDefine2,
JustInVouch.cDefine3,JustInVouch.cDefine8,JustInVouch.cDefine9,JustInVouch.cDefine10,JustInVouch.cDefine5,
JustInVouch.cDefine7,JustInVouch.cDefine6,JustInVouch.cDefine4,JustInVouch.dJVDate,
Warehouse.cWhName,Vendor.cVenAbbName,Department.cDepName,Rd_Style.cRdName,Person_Operation.cPersonName as cPersonName,
Person_cHandler.cPersonName as cPersonName_1
from
(((((JustInVouch left join Warehouse on JustInVouch.cWhCode=Warehouse.cWhCode)left join Vendor on JustInVouch.cUnitCode=Vendor.cVenCode)left join Department on JustInVouch.cDepCode=Department.cDepCode)left join Rd_Style on JustInVouch.cRdCode=Rd_Style.cRd
Code)left join Person as Person_Operation on JustInVouch.cPersonCode=Person_Operation.cPersonCode)left join Person as Person_cHandler on JustInVouch.cHandler=Person_cHandler.cPersonCode
where JustInVouch.cJVCode=@parm
end
出库调整单
Create PROCEDURE NewPrint_JustOutVouch_Body
@parm varchar(100)
AS
begin
select
JustInVouchs.cJVCode,JustInVouchs.cInvCode,JustInVouchs.cFree1,JustInVouchs.cFree2,JustInVouchs.cObjCode,JustInVouchs.cName,
JustInVouchs.cDefine22,JustInVouchs.cDefine23,JustInVouchs.cDefine24,JustInVouchs.cDefine25,JustInVouchs.cItemCName,
JustInVouchs.cItemCode,JustInVouchs.cItem_class,JustInVouchs.cDefine27,JustInVouchs.cDefine26,JustInVouchs.AutoID,
JustInVouchs.iJVPrice,Inventory.cInvName,Inventory.cInvStd,
Inventory.iTopSum,Inventory.iLowSum,Inventory.iSafeNum,Inventory.iInvWeight,Inventory.iVolume,Inventory.cQuality,
Inventory.cInvAddCode,Inventory.cInvDefine1,Inventory.cInvDefine2,Inventory.cInvDefine3,Inventory.cPosName as cPosName_Inv
from
JustInVouchs left join inventory_pos_ven as inventory on JustInVouchs.cInvCode=Inventory.cInvCode
where JustInVouchs.cJVCode=@parm
end
作者：畅捷服务社区 | 时间：2018年10月09日 16:04
对我有用
没有帮助