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