| Subject: | Extract Sales transactions from JIWA Database | | Summary: | This script will extract sales transactions including Credit notes from JIWA database. The WHERE clause shows the Links between the core tables required.
SELECT
Convert(varchar(12),SO_History.RecordDate,102) AS InvoiceDate,
'AC:' + DB_MAIN.AccountNo,
'INV:' + SO_Main.InvoiceNo,
so_main.OrderNo,
so_main.SOReference,
'PNo:' + SO_Lines.PartNo,
SO_Lines.Description,
SO_Lines.ClassDescription,
SO_Lines.Cat1Description,
CASE WHEN CreditNote = 0 THEN SO_Lines.QuantityThisDel ELSE SO_Lines.QuantityThisDel * -1 END AS Quantity,
(CASE WHEN QuantityThisDel <> 0 THEN ROUND(SO_Lines.LineCost / SO_Lines.QuantityThisDel,2) ELSE 0 END) AS UnitCost,
SO_Lines.ItemPrice AS UnitSellPrice,
CASE WHEN CreditNote = 0 THEN SO_Lines.TaxToCharge ELSE SO_Lines.TaxToCharge * -1 END AS LineTax,
CASE WHEN CreditNote = 0 THEN SO_Lines.CurrentLineTotal ELSE SO_Lines.CurrentLineTotal * -1 END AS LineTotal,
SO_Lines.LineCost
FROM SO_Main
JOIN SO_History ON (SO_History.InvoiceID = SO_Main.InvoiceID)
JOIN SO_Lines ON (SO_Lines.InvoiceHistoryID = SO_History.InvoiceHistoryID)
JOIN DB_MAIN ON (SO_Main.DebtorID = DB_MAIN.DEBTORID)
LEFT JOIN IN_Main ON (IN_Main.InventoryID = SO_Lines.InventoryID)
LEFT JOIN IN_Category4 ON (IN_Category4.Category4ID = IN_Main.Catagory4ID)
LEFT JOIN IN_Category5 ON (IN_Category5.Category5ID = IN_Main.Catagory5ID)
WHERE (SO_Main.Status <> 0 OR SO_History.HistoryNo < SO_Main.CurrentHistoryNo)
AND SO_Lines.QuantityThisDel <> 0
AND Not(BillWhenComplete= 2 and SO_Main.Status = 1) --As per bug 6995
| | Audit Notes: | |
|