代码之家 › 专栏 › 技术社区 › Mohammed Mustafa

SBO表格关系(AR INVOICE-INCOMING付款)

sapb1 crystal-reports join sql

1

Mohammed Mustafa · 技术社区 · 9 年前

我正在进行一个查询,该查询将传入付款详细信息,包括付款方式、付款方式的详细信息,然后是来自相关应收发票的一些UDF,以及来自与AR invoice中的UDF相关的对象的某些UDF,

现在每次我运行查询时,它都没有显示结果。

我肯定这里有我遗漏的或不正确的东西,但到目前为止找不到。

如果有人能帮助我,我会感激的

以下是查询:

SELECT T1.[baseAbs] AS INVOICENO, T0.[DocDate],t0.[trsfrdate],t0.[trsfrref], T0.[CardName],T0.[Doctotal],T4.[VoucherNum] ,
T0.[Comments], T1.[DocNum] AS PAYMENTNO, T2.[Phone1],
T0.[CashSum], T0.[CreditSum], T0.[CheckSum], T0.[TrsfrSum],
T3.[DueDate] AS CHECKDATE, T3.[CheckNum] AS CHECKNO, T3.[Details] AS MAYBEBANKNAME
, t5.[U_UnitCode],t5.[U_Type],t7.[WhsName],t7.[city] ,
t8.U_FloorNo
FROM ORCT T0  
inner JOIN RCT2 T1 ON T0.[DocEntry]  = T1.[DocNum]
inner JOIN OINV T5 ON T5.[docnum] =T1.[BaseAbs]
INNER JOIN RCT1 T3 ON T0.[DocNum] = T3.[DocNum]
INNER JOIN RCT3 T4 ON T0.[DocNum] = T4.[DocNum]
INNER JOIN OCRD T2 ON T0.[CardCode] = T2.[CardCode]
INNER JOIN INV1 T6 ON T5.[DocEntry] = T6.[DocEntry]
INNER JOIN OWHS T7 ON T6.[WhsCode] = T7.[WhsCode]
INNER JOIN [dbo].[@AUND] T8 ON T5.[U_UnitCode] = T8.[Code]

2 回复 | 直到 4 年前

1

2

Alexander Abakumov 8 年前

查询现在工作正常,问题是内部连接,应该将其替换为左连接, 这是固定的:

select T0.DocNum as 'Payment Number',T0.DocDate 'Payment Date',T0.CardCode,
T0.CardName 'Customer Name',T1.BankCode 'Bankcode',T3.BankName 'Bank Name', T2.Phone1 ,
T0.CreditSum,
T0.CashSum,
T0.TrsfrSum,
t0.CheckSum,
t1.CheckNum as 'Check Number',
t1.DueDate as 'check date',
t6.VoucherNum as 'Voucher Number',
t0.TrsfrRef as 'Transfer No',
t0.TrsfrDate AS 'Transfer Date',
ousr.USER_code as 'user code',
 T5.DocNum, t11.U_P_BuildingName as 'Building Name',
CASE when T5.DocNum is null then 'On Account' else 'Paid For Invoice' END AS 'Payment Status',
CASE when T5.DocStatus = 'O' then 'Open' else 'Closed' END AS ' Invoice Status',
T4.SumApplied as 'Amount Paid on Invoice',T9.U_FloorNo,T5.U_UnitCode,T5.U_Type,
t0.DocTotal as 'Payment Total',t5.DocTotal as'Invoice Total' , t8.City,
t0.Comments as'Remarks'
from ORCT T0
left join rct1 T1 on T0.DocNum=T1.DocNum
left join ocrd T2 on T2.CardCode=T0.CardCode
left outer join ODSC T3 on T3.BankCode=T0.BankCode
left join RCT2 T4 on T0.DocNum = T4.DocNum
left join RCT3 T6 on T0.DocNum = T6.DocNum
left join OINV T5 on T4.DocEntry = T5.DocEntry and T5.ObjType = T4.InvType
left join oitm t11 on t5.u_unitcode = t11.ItemCode
LEFT JOIN OWHS T8 ON T11.U_P_BuildingNum  = T8.WhsCode
LEFT JOIN [dbo].[@AUND] T9 ON T5.[U_UnitCode] = T9.[Code]
 INNER JOIN  OSLP T10  ON T5.[SlpCode] = T10.[SlpCode] 
inner join ousr on ousr.USERID = t0.usersign
where 
T4.InvType <> '14' and T0.[Canceled] = 'N'  and t0.docnum=200001

2

0

Overhed 9 年前

我认为您的查询有一些问题,首先是从ORCT到RCT2的连接。

我在过去创建过类似的查询,这里有一个我知道有效的,也许你可以用它来调整你的查询。首先,您肯定需要将许多内部连接调整为外部连接,因为Payments和它的父业务对象(如Invoices)之间的许多关系非常松散,可能并不总是适用。

请注意,下面的查询专门查找RCT2表中的发票(这是传入付款对象的“行”部分),因此J002.InvType=13条件。

SELECT *
FROM [ORCT] J001
LEFT OUTER JOIN [RCT2] J002 ON J002.DocNum = J001.DocNum AND J002.InvType = 13
LEFT OUTER JOIN [OINV] J003 ON J003.DocEntry = J002.DocEntry
LEFT OUTER JOIN [OACT] J004 ON J004.AcctCode = J001.CashAcct      
LEFT OUTER JOIN [RCT1] J005 ON J005.DocNum = J001.DocNum