on 06-06-2011 9:46 AM
Experts,
How can we find basereference for Delivery from OINV and INV1 table?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
You can find baserefnum from field :- NumAtCard from OINV table
and from field :- BaseAtCard from INV1
Thanks
Rahul
Try this Query :
SELECT distinct OINV.DocNum, OINV.DocDate, OINV.CardCode, OINV.CardName, INV1.BaseRef
FROM OINV LEFT OUTER JOIN
INV1 ON OINV.DocEntry = INV1.DocEntry
--
Ashish
SELECT dbo.ExciseDetail_DNAR.DocEntry, dbo.ExciseDetail_DNAR.DocNum, dbo.ExciseDetail_DNAR.DocLine,
(CASE WHEN isnull([@CBS_DUTY1].U_moditem, 'N') = 'N' THEN [@CBS_DUTY1].U_ITEMCODE ELSE [@CBS_DUTY1].U_moditem END) AS Code,
dbo.ExciseDetail_DNAR.ItemName, dbo.ExciseDetail_DNAR.DistNumber, dbo.ExciseDetail_DNAR.LotNumber, dbo.ExciseDetail_DNAR.MnfSerial,
dbo.[@CBS_DUTY1].U_QUANTITY, dbo.ExciseDetail_DNAR.U_RG_No, dbo.ExciseDetail_DNAR.ChapterID, dbo.ExciseDetail_DNAR.U_SID,
dbo.ExciseDetail_DNAR.QtyReceived, dbo.ExciseDetail_DNAR.U_STAV, dbo.ExciseDetail_DNAR.U_UAV, dbo.ExciseDetail_DNAR.U_ED,
dbo.ExciseDetail_DNAR.U_MIN, dbo.ExciseDetail_DNAR.U_MID, dbo.ExciseDetail_DNAR.U_RGN, dbo.ExciseDetail_DNAR.U_MIQ,
dbo.ExciseDetail_DNAR.U_EC, dbo.ExciseDetail_DNAR.U_HS, dbo.[@CBS_DUTY1].U_CVD, dbo.[@CBS_DUTY1].U_EDNCESS,
dbo.[@CBS_DUTY1].U_SHEce, dbo.ExciseDetail_DNAR.TotalDuty, dbo.ExciseDetail_DNAR.TotalEC, dbo.ExciseDetail_DNAR.TotalHS,
dbo.ExciseDetail_DNAR.TotalCVD, dbo.ExciseDetail_DNAR.U_SUEC, dbo.ExciseDetail_DNAR.U_SUHS, dbo.ExciseDetail_DNAR.U_SUCVD,
dbo.ExciseDetail_DNAR.U_ITMGRP, dbo.ExciseDetail_DNAR.Dscription, dbo.ExciseDetail_DNAR.PriceBefDi, dbo.ExciseDetail_DNAR.DiscPrcnt,
dbo.ExciseDetail_DNAR.LineTotal, dbo.ExciseDetail_DNAR.U_LN, RIGHT(dbo.NNM1.SeriesName, 3) AS DNSeries, dbo.ODLN.DocNum AS DNDetails,
dbo.[@CBS_DUTY1].U_CVDU, dbo.[@CBS_DUTY1].U_ITEMDES, dbo.[@CBS_DUTY1].U_PRICE, dbo.[@CBS_DUTY1].U_ASS,
dbo.[@CBS_DUTY1].U_SPer, dbo.[@CBS_DUTY1].U_EPer, dbo.[@CBS_DUTY1].U_BASICRA, dbo.[@CBS_DUTY1].U_CPer,
dbo.[@CBS_DUTY1].U_TAmnt, dbo.[@CBS_DUTY1].U_OTHERS, dbo.[@CBS_DUTY1].U_EDPUnit, dbo.[@CBS_DUTY1].U_SHPUnit,
dbo.[@CBS_DUTY1].U_ADCPUnit, dbo.[@CBS_DUTY1].U_CVDPer, dbo.ExciseDetail_DNAR.FACODE
FROM dbo.ORDR RIGHT OUTER JOIN
dbo.ExciseDetail_DNAR INNER JOIN
dbo.ODLN ON dbo.ExciseDetail_DNAR.DocEntry = dbo.ODLN.DocEntry AND dbo.ExciseDetail_DNAR.DocNum = dbo.ODLN.DocNum INNER JOIN
dbo.NNM1 ON dbo.ODLN.Series = dbo.NNM1.Series INNER JOIN
dbo.[@CBS_DUTY1] ON dbo.ExciseDetail_DNAR.DistNumber = dbo.[@CBS_DUTY1].U_batch AND
dbo.ExciseDetail_DNAR.DocNum = dbo.[@CBS_DUTY1].U_ARDOC ON dbo.ORDR.ObjType = dbo.ExciseDetail_DNAR.BaseType AND
dbo.ORDR.DocEntry = dbo.ExciseDetail_DNAR.BaseEntry
where ExciseDetail_DNAR.DocNum in
(select T1.BaseRef from OINV T0 INNER JOIN INV1 T1 on T0.DocEntry = T1.DocEntry
where T0.DocNum = '12105744')
in this query I want to incoporate INV1.Quantity field...But cant
HI
Where you want to incorporate the "inv1.Quantity" in that query?
Please tell me...
This is a view created...Likewise more viewes are there. I am giving details of all for clear understanding...
No: 1.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
ALTER View [dbo].[ReturnDN]
as
SELECT OITL.BaseEntry AS Docentry, OITL.BaseType AS Doctype, OITL.ItemCode, ITL1.Quantity, OBTN.DistNumber
FROM OITL INNER JOIN
ITL1 ON OITL.LogEntry = ITL1.LogEntry INNER JOIN
OBTW ON ITL1.ItemCode = OBTW.ItemCode AND ITL1.MdAbsEntry = OBTW.MdAbsEntry AND OITL.LocCode = OBTW.WhsCode INNER JOIN
OBTN ON ITL1.MdAbsEntry = OBTN.AbsEntry
WHERE (OITL.DocType = 16) AND (OITL.basetype = 15)-- and oitl.baseentry = 132297
SET ANSI_NULLS OFF
SET QUOTED_IDENTIFIER OFF
No.: 2
ALTER VIEW [dbo].[ExciseDTBase]
AS
SELECT dbo.OITL.DocEntry, dbo.OITL.DocNum, dbo.OITL.DocLine, dbo.OITL.BaseEntry, dbo.OITL.BaseType, dbo.OITL.ItemCode, dbo.OITL.ItemName,
dbo.OBTN.DistNumber, dbo.OBTN.LotNumber, dbo.OBTN.MnfSerial, - dbo.ITL1.Quantity AS Quantity, dbo.OBTN.U_RG_No, dbo.OCHP.ChapterID,
dbo.OBTN.U_SID, CASE WHEN OBTN.U_UAV = 0 THEN 0 ELSE OBTN.U_STAV / OBTN.U_UAV END AS QtyReceived, dbo.OBTN.U_STAV,
dbo.OBTN.U_UAV, dbo.OBTN.U_ED, dbo.OBTN.U_MIN, dbo.OBTN.U_MID, dbo.OBTN.U_RGN, dbo.OBTN.U_MIQ, dbo.OBTN.U_EC, dbo.OBTN.U_HS,
dbo.OBTN.U_CVD, dbo.OBTN.U_STED, dbo.OBTN.U_SUED, dbo.OBTN.U_SUEC, dbo.OBTN.U_SUHS, dbo.OBTN.U_SUCVD, dbo.INV1.U_ITMGRP,
dbo.INV1.Dscription, dbo.INV1.PriceBefDi, dbo.INV1.DiscPrcnt, dbo.INV1.LineTotal, dbo.INV1.Price,
(SELECT MAX(U_FACode) AS Expr1
FROM dbo.SuppFAcode
WHERE (address = dbo.OBTN.U_SOS)) AS FACODE, dbo.OBTN.U_SOS, dbo.OITB.U_LN
-- ,(SELECT (INV1.Quantity)
-- FROM dbo.Invoice_Qty
-- ) AS QTY
FROM dbo.OITL INNER JOIN
dbo.ITL1 ON dbo.OITL.LogEntry = dbo.ITL1.LogEntry AND dbo.OITL.ItemCode = dbo.ITL1.ItemCode INNER JOIN
dbo.OBTN ON dbo.ITL1.ItemCode = dbo.OBTN.ItemCode AND dbo.ITL1.SysNumber = dbo.OBTN.SysNumber INNER JOIN
dbo.OITM ON dbo.OITL.ItemCode = dbo.OITM.ItemCode INNER JOIN
dbo.OCHP ON dbo.OITM.ChapterID = dbo.OCHP.AbsEntry INNER JOIN
dbo.INV1 ON dbo.OITL.DocEntry = dbo.INV1.BaseEntry AND dbo.OITL.DocType = dbo.INV1.BaseType AND
dbo.OITL.DocLine = dbo.INV1.BaseLine INNER JOIN
dbo.OITB ON dbo.OITM.ItmsGrpCod = dbo.OITB.ItmsGrpCod
No.: 3
/and ExciseDTBase.DocNum = 12203475/
ALTER VIEW [dbo].[ExciseDetail_DNAR]
SELECT dbo.ExciseDTBase.DocEntry, dbo.ExciseDTBase.DocNum, dbo.ExciseDTBase.BaseEntry, dbo.ExciseDTBase.BaseType, dbo.ExciseDTBase.DocLine,
dbo.ExciseDTBase.ItemCode, dbo.ExciseDTBase.ItemName, dbo.ExciseDTBase.DistNumber, dbo.ExciseDTBase.LotNumber,
dbo.ExciseDTBase.MnfSerial, CASE WHEN ReturnDn.Quantity IS NULL
THEN ExciseDTBase.Quantity ELSE (ExciseDTBase.Quantity - ReturnDn.Quantity) END AS Quantity, dbo.ExciseDTBase.U_RG_No,
dbo.ExciseDTBase.ChapterID, dbo.ExciseDTBase.U_SID, dbo.ExciseDTBase.QtyReceived, dbo.ExciseDTBase.U_STAV, dbo.ExciseDTBase.U_UAV,
dbo.ExciseDTBase.U_ED, dbo.ExciseDTBase.U_MIN, dbo.ExciseDTBase.U_MID, dbo.ExciseDTBase.U_RGN, dbo.ExciseDTBase.U_MIQ,
dbo.ExciseDTBase.U_EC, dbo.ExciseDTBase.U_HS, dbo.ExciseDTBase.U_CVD, dbo.ExciseDTBase.U_STED, dbo.ExciseDTBase.U_SUED,
(dbo.ExciseDTBase.Quantity - ISNULL(dbo.ReturnDN.Quantity, 0)) * dbo.ExciseDTBase.U_SUED AS TotalDuty,
(dbo.ExciseDTBase.Quantity - ISNULL(dbo.ReturnDN.Quantity, 0)) * dbo.ExciseDTBase.U_SUEC AS TotalEC,
(dbo.ExciseDTBase.Quantity - ISNULL(dbo.ReturnDN.Quantity, 0)) * dbo.ExciseDTBase.U_SUHS AS TotalHS,
(dbo.ExciseDTBase.Quantity - ISNULL(dbo.ReturnDN.Quantity, 0)) * dbo.ExciseDTBase.U_SUCVD AS TotalCVD, dbo.ExciseDTBase.U_SUEC,
dbo.ExciseDTBase.U_SUHS, dbo.ExciseDTBase.U_SUCVD, dbo.ExciseDTBase.U_ITMGRP, dbo.ExciseDTBase.Dscription,
dbo.ExciseDTBase.PriceBefDi, dbo.ExciseDTBase.DiscPrcnt, (dbo.ExciseDTBase.Quantity - ISNULL(dbo.ReturnDN.Quantity, 0))
dbo.ExciseDTBase.Price AS LineTotal, dbo.ExciseDTBase.U_LN, dbo.ExciseDTBase.FACODE
-- ,dbo.ExciseDTBase.QTY
FROM dbo.ExciseDTBase LEFT OUTER JOIN
dbo.ReturnDN ON dbo.ExciseDTBase.DocEntry = dbo.ReturnDN.Docentry AND dbo.ExciseDTBase.ItemCode = dbo.ReturnDN.ItemCode AND
dbo.ExciseDTBase.DistNumber = dbo.ReturnDN.DistNumber
WHERE ((CASE WHEN ReturnDn.Quantity IS NULL THEN ExciseDTBase.Quantity ELSE (ExciseDTBase.Quantity - ReturnDn.Quantity) END) <> 0)
No. 4
/WHERE (ExciseDetail_DNAR.DocNum = '12203476')/
ALTER VIEW [dbo].[ExciseInvoiceReport]
-- ,dbo.ExciseDetail_DNAR.QTY
HI Rupa ,
Please elaborate your need . Which data do you want to retrieve from the system.
Ashish Ranjan
Hi Rupa ,
The Field is :
Inv1.Baseref .
Try this
Edited by: ASHISH RANJAN on Jun 6, 2011 2:26 PM
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.