查询:具有合并的销售订单和交货的发票

2020-09-08 20:04发布

         点击此处--->   EasySAP.com群内免费提供SAP练习系统(在群公告中)

加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)


专家

我需要创建一个查询,其中列出所有带有相关销售订单和交货的发票(如果有链接)。 这需要包括一些头数据。 我所没有的就是正确加入并给出错误的关联SO和交付:

您的帮助将不胜感激。

选择区别
 ISNULL(SalesFromDel.DocNum,SalesFromInv.DocNum)AS'OrderNumber',
                         ISNULL(SalesFromDel.DocTotal,SalesFromInv.DocTotal)AS'OrderAmount',
 ISNULL(SalesFromDel.DocStatus,SalesFromInv.DocStatus)AS'OrderStatus',
 ISNULL(SalesFromDel.DocDate,SalesFromInv.DocDate)AS'OrderDate',
 --SalesFromDel.DocStatus'OrderStatus',
                         --SalesFromDel.DocDate'OrderDate',
                         Invoice.DocNum'InvoiceNumber',
                         Invoice.DocTotal'InvoiceAmount',
                         Invoice.DocDate'InvoiceDate',
                         Invoice.DocStatus'InvoiceStatus',
                         Delivery.DocNum'DeliveryNumber',
                         Delivery.DocDate'DeliveryDate',
                         Delivery.DocStatus'DeliveryStatus',
                         Delivery.DocTotal'交付金额'


                 从OINV发票INNERJOIN INV1 T0 on Invoice.DocEntry = T0.DocEntry
                         左联接ODLN T0.BaseEntry上的交货= Delivery.DocEntry
                         左联接DLN1 T2 ON Delivery.DocEntry = T2.DocEntry
                         ----与交货相关的销售订单
 左联接RDR1 T3在T2.BaseEntry = T3.DocEntry和T2.BaseLine = T3.LineNum
                         左联接ORDR SalesFromDel开启T3.DocEntry = SalesFromDel.DocEntry
 ----销售订单链接到发票
 左联接RDR1 T4接通T0.BaseEntry = T4.DocEntry和T0.BaseLine = T4.LineNum
 左联接ORDR SalesFromInv ON T4.DocEntry = SalesFromInv.DocEntry


                 其中T0.BaseType = 15或T0.BaseType = 17或T0.BaseType = 23或T0.BaseType = -1或T0.BaseType = 13
                         AND T0.BaseEntry不为空
                         AND T0.BaseEntry> 0
                         AND Invoice.DocDate> ='1/1/2019'

 按Invoice.DocNum DESC排序;
 

         点击此处--->   EasySAP.com群内免费提供SAP练习系统(在群公告中)

加入QQ群:457200227(SAP S4 HANA技术交流) 群内免费提供SAP练习系统(在群公告中)


专家

我需要创建一个查询,其中列出所有带有相关销售订单和交货的发票(如果有链接)。 这需要包括一些头数据。 我所没有的就是正确加入并给出错误的关联SO和交付:

您的帮助将不胜感激。

选择区别
 ISNULL(SalesFromDel.DocNum,SalesFromInv.DocNum)AS'OrderNumber',
                         ISNULL(SalesFromDel.DocTotal,SalesFromInv.DocTotal)AS'OrderAmount',
 ISNULL(SalesFromDel.DocStatus,SalesFromInv.DocStatus)AS'OrderStatus',
 ISNULL(SalesFromDel.DocDate,SalesFromInv.DocDate)AS'OrderDate',
 --SalesFromDel.DocStatus'OrderStatus',
                         --SalesFromDel.DocDate'OrderDate',
                         Invoice.DocNum'InvoiceNumber',
                         Invoice.DocTotal'InvoiceAmount',
                         Invoice.DocDate'InvoiceDate',
                         Invoice.DocStatus'InvoiceStatus',
                         Delivery.DocNum'DeliveryNumber',
                         Delivery.DocDate'DeliveryDate',
                         Delivery.DocStatus'DeliveryStatus',
                         Delivery.DocTotal'交付金额'


                 从OINV发票INNERJOIN INV1 T0 on Invoice.DocEntry = T0.DocEntry
                         左联接ODLN T0.BaseEntry上的交货= Delivery.DocEntry
                         左联接DLN1 T2 ON Delivery.DocEntry = T2.DocEntry
                         ----与交货相关的销售订单
 左联接RDR1 T3在T2.BaseEntry = T3.DocEntry和T2.BaseLine = T3.LineNum
                         左联接ORDR SalesFromDel开启T3.DocEntry = SalesFromDel.DocEntry
 ----销售订单链接到发票
 左联接RDR1 T4接通T0.BaseEntry = T4.DocEntry和T0.BaseLine = T4.LineNum
 左联接ORDR SalesFromInv ON T4.DocEntry = SalesFromInv.DocEntry


                 其中T0.BaseType = 15或T0.BaseType = 17或T0.BaseType = 23或T0.BaseType = -1或T0.BaseType = 13
                         AND T0.BaseEntry不为空
                         AND T0.BaseEntry> 0
                         AND Invoice.DocDate> ='1/1/2019'

 按Invoice.DocNum DESC排序;
 
付费偷看设置
发送
2条回答
三十六小时_GS
1楼 · 2020-09-08 20:56.采纳回答

嗨,

尝试以下操作:

选择DISTINCT ISNULL(SalesFromDel.DocNum,SalesFromInv.DocNum)AS'OrderNumber',
     ISNULL(SalesFromDel.DocTotal,SalesFromInv.DocTotal)AS'OrderAmount',
     ISNULL(SalesFromDel.DocStatus,SalesFromInv.DocStatus)AS'OrderStatus',
     ISNULL(SalesFromDel.DocDate,SalesFromInv.DocDate)AS'OrderDate',
     --SalesFromDel.DocStatus'OrderStatus',
     --SalesFromDel.DocDate'OrderDate',
     Invoice.DocNum'InvoiceNumber',Invoice.DocTotal'InvoiceAmount',Invoice.DocDate'InvoiceDate',
     Invoice.DocStatus'InvoiceStatus',Delivery.DocNum'DeliveryNumber',Delivery.DocDate'DeliveryDate',
     Delivery.DocStatus'DeliveryStatus',Delivery.DocTotal'DeliveryAmount'
 来自OINV发票
      内联INV1 T0 ON Invoice.DocEntry = T0.DocEntry
      左联接DLN1 T2接通T0.BaseEntry = T2.DocEntry和T0.BaseLine = T2.LineNum和T0.BaseType = 15
 左联接ODLN Delivery on Delivery.DocEntry = T2.DocEntry
      ----与交货相关的销售订单
      左联接RDR1 T3在T2.BaseEntry = T3.DocEntry和T2.BaseLine = T3.LineNum和T2.BaseType = 17上
      左联接ORDR SalesFromDel在T3.DocEntry = SalesFromDel.DocEntry
      ----销售订单链接到发票
      左联接RDR1 T4上T0.BaseEntry = T4.DocEntry和T0.BaseLine = T4.LineNum和T0.BaseType = 17
      左联接ORDR SalesFromInv上T4.DocEntry = SalesFromInv.DocEntry
 在哪里T0.BaseType = 15或T0.BaseType = 17或T0.BaseType = 23或T0.BaseType = -1或T0.BaseType = 13
                                                                               AND T0.BaseEntry不为空
                                                                               AND T0.BaseEntry> 0
                                                                               AND Invoice.DocDate> ='1/1/2019'
 按Invoice.DocNum DESC排序;
 

问候,

Bala

代楠1984
2楼-- · 2020-09-08 20:52

我也尝试过此操作,但是在添加销售订单总计时遇到了问题:

选择区别
 inv1.DocEntry作为" InvEntry",
 oinv.DocNum为" InvDocNum",
 nnm1.SeriesName,
 oinv.DocTotal为" InvTotal",


 案件
 当inv1.BaseType = 15时,则inv1.BaseEntry
 否则为空
 作为交付结束


 案件
 当inv1.BaseType = 17时,则inv1.BaseEntry
 当inv1.BaseType = 15且dln1.BaseType = 17时,则dln1.BaseEntry
 否则为空
 以SalesOrder结尾,


 inv1.BaseType,
 inv1.BaseEntry


 从
 inv1内部连接oinv ON inv1。[DocEntry] = oinv。[DocEntry]
 inv1.BaseEntry = dln1.DocEntry上的左外部联接dln1
 内部联接nnm1 ON oinv.Series = nnm1.Series
 dln1.BaseEntry = rdr1.DocEntry上的左外部联接rdr1
 -或inv1.BaseEntry = rdr1.DocEntry
 左外部连接ordr ON rdr1。[DocEntry] = ordr。[DocEntry]


 按发票DESC排序;
 

一周热门 更多>