Sales back Order Query

[Former Member]

Hi All

I am having an issue with a query im doing for a customer for sales back orders.

The customer does not want to use the current back order report in Business one.

What they essentially want is to see only the items on back order where that  sales order already has a delivery to it.

They do not want to see sales order that havent been delivered at all.

This is what i have so far.

it does pick up items where there is a balance to be delivered...however, it does not pick up the lines that have not been delivered at all

select T0.Docnum as "Sales Order Num", CAST(T0.DocDate as DATE) as "Sales Order Date", T1.ItemCode, T1.Dscription, T1.Quantity, T1.OpenQty from ORDR T0 inner join RDR1 T1 on T0.DocEntry = T1.DocEntry

where T1.TargetType <> '-1' and T1.LineStatus <> 'C'

Not to sure what I am missing.

Your help will be appreciated.

Thank you

Jerusha

[Former Member · Accepted solution]

I was thinking of having a parameter to be set to true if any of the lines have a target entry...if that is true..select all the open lines..

Would that work?

[kothandaraman_nagarajan · Accepted solution]

Hi,

Try this query:

SELECT T0.[DocNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty] FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocNum] in ( select  T1.[BaseRef] from DLN1 t1) and  T1.[LineStatus]  = 'o' GROUP BY T0.[DocNum], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T1.[Quantity], T1.[OpenQty]

Thanks & Regards,

Nagarajan

[Former Member · Accepted solution]

Hi,

I was too looking for such a query and built one based on this discussion.

Given below,

But I found that my query reports the balance quantity correctly BUT does not report the balance amount correctly. I found that balance amount being reported is same as the original order amount.

I checked the raw table in query builder insie SAP B1, and found that the table RDR1 shows full amount as outstanding.

I am using Ver 9.10 (9.10.150 PL 05).

Is it some bug in SAP B1? Or I am not referring  to correct field while querying?

Any comment/help will be highly appreciated.

Regards

Anil

SELECT	T0.[docentry],
	T0.[CardCode],
	T0.[CardName],
	T0.[Ref1],
	T0.[DocDate],
	T0.[DocDueDate],
	T0.[NumAtCard],
	T0.[U_BPRefDate],
	T0.[DocTotal],
	T1.[U_CustPOSrNo],
	T1.[ItemCode],
	T1.[OrigItem],
	T1.[U_IT1],
	T2.[SWW],
	T2.[SUPPCATNUM],
	T1.[VendorNum],
	T1.[U_bpcatext],
	T1.[SubCatNum],
	T3.[FIRMNAME],
	T1.[Dscription],
	T1.[Quantity],
	T1.[UseBaseUn],
	T1.[PriceBefDi],
	T1.[DiscPrcnt],
	T1.[LineTotal],
	T1.[OpenQty],
	T1.[OpenSum],
	T1.[TaxCode],
	T1.[VatSum],
	(T1.[OpenSum]+T1.[VatSum]) as 'Gross'

FROM ORDR t0

inner join rdr1 t1 on t1.docentry = t0.docentry

INNER JOIN OITM T2 ON T2.ITEMCODE = T1.ITEMCODE

INNER JOIN OMRC T3 ON T3.FIRMCODE = T2.FIRMCODE

WHERE T1.OPENQTY > 0

ORDER BY T0.CARDCODE, T0.[U_BPRefDate]

[kothandaraman_nagarajan · Accepted solution]

Hi,

Please create new discussion for above issue.

Thank you

[Former Member]

Thank you Nagrajan and Gordon..

It works great

Regards

Jerusha

[Former Member]

Hi

You may try:

select T0.Docnum as 'Sales Order Num', T0.DocDate as 'Sales Order Date', T1.ItemCode, T1.Dscription, T1.Quantity, T1.OpenQty

from ORDR T0 inner join RDR1 T1 on T0.DocEntry = T1.DocEntry

where T1.LineStatus = 'O' and T0.DocEntry in (SELECT baseEntry FROM DLN1)

OR

select T0.Docnum as 'Sales Order Num', T0.DocDate as 'Sales Order Date', T1.ItemCode, T1.Dscription, T1.Quantity, T1.OpenQty

from ORDR T0 inner join RDR1 T1 on T0.DocEntry = T1.DocEntry

where T1.OpenQty != 0 and T0.DocEntry in (SELECT baseEntry FROM DLN1)

Thanks,

Gordon

[kothandaraman_nagarajan]

Hi,

Try this query:

SELECT T0.[CardName] as CustName,T0.[DocNum] as SO#,T1.[Project] as Job#,   T1.[ItemCode] as Item#, T1.[Dscription] as PartName, T1.[Quantity] as OrderedQty, T1.[ShipDate] as DelDate,T1.[DelivrdQty] as DelQty, T1.[OpenQty] as BalQty FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[LineStatus]  = 'o' and  T1.[Quantity] <> T1.[DelivrdQty] and  T1.[DelivrdQty] <> 0 order by T0.[CardName]

Thanks & Regards,

Nagarajan

[Former Member]

Hi Nagarajan

Thank you for this...Its still doesnt pick up the lines from the sales orders that hasnt been delivered at all..i.e, isnt a partial delivery.. so that full amount still have to be delivered.

That line has a targetype of -1..

[kothandaraman_nagarajan]

Hi,

Do you need open sales order lines with partially delivered items?

Check this thread whether meets your requirement http://scn.sap.com/thread/3541905

Thanks & Regards,

Nagarajan

[Former Member]

Hi

I need the query to pick up the sales orders with items that have a partial delivery as well as the items on the sales order that havent been delivered at all. However it should not pick up sales orders where no delivery notes has been added at all..i.e no target document for any of the lines

eg

Sales order 1

ItemCode     Qty     Delivered

Item 1           10         2

Item 2           10       10

Item 3           10        0

Sales order 2

ItemCode     Qty     Delivered

Item 1           10        0

Item 2           10        0

Item 3           10        0

so the query should only pick up sales order 1 with lines item 1 and item 3.

Hope this makes more sense..

Thank you for your help so far.

Jerusha