Views

[Former Member]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*select u_Tor, u_IssDt, * from oinv where docdate = '04/11/2011'*/

ALTER view [dbo].[ExciseDetailHeader]

as

SELECT ShipTo.Address, OINV.Address AS ShipAddr, ShipTo.U_CERange, ShipTo.U_CEDivis, ShipTo.U_CEComRate, ShipTo.U_BECC, ShipTo.U_CERegNo, ShipTo.U_BVAT,

ShipTo.U_BCST, BillTo.Address AS BillToAddress, OINV.Address2, BillTo.U_CERegNo AS BillCERegNo, BillTo.U_BECC AS BillECC, BillTo.U_BVAT AS BillVAT,

BillTo.U_BCST AS BillCST, NNM1.SeriesName, OINV.DocNum, OINV.DocDate, NFN1.SeqName, OINV.Serial, OINV.U_LRN, OINV.U_LRD, OINV.U_Delivery, OINV.U_FR,

OINV.U_TRANS_1, OINV.U_MOT, OINV.U_VE, OINV.U_PN, OINV.U_NoOfPack, OINV.U_TWEIGHT, OINV.DocTotal, CASE WHEN len(oinv.doctime)

= 3 THEN LEFT(OINV.Doctime, 1) + ':' + RIGHT(OINV.Doctime, 2) ELSE LEFT(OINV.Doctime, 2) + ':' + RIGHT(OINV.Doctime, 2) END AS DateOfRemoval, OINV.NumAtCard,

dbo.ufn_numtowords(OINV.DocTotal) AS Totalinwords, OCTG.PymntGroup, OINV.DiscSum, OINV.Discprcnt, OINV.Comments

FROM OINV INNER JOIN

CRD1 AS ShipTo ON OINV.ShipToCode = ShipTo.Address AND OINV.CardCode = ShipTo.CardCode INNER JOIN

CRD1 AS BillTo ON OINV.PayToCode = BillTo.Address AND OINV.CardCode = BillTo.CardCode INNER JOIN

NNM1 ON OINV.Series = NNM1.Series INNER JOIN

NFN1 ON OINV.SeqCode = NFN1.SeqCode INNER JOIN

OCTG ON OINV.GroupNum = OCTG.GroupNum

WHERE (BillTo.AdresType = 'B') AND (ShipTo.AdresType = 'S')

--AND (OINV.DocNum = 12100719)

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

[Former Member]

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/WHERE (ExciseDetail_DNAR.DocNum = '12203476')/

ALTER VIEW [dbo].[ExciseInvoiceReport]

AS

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

-- ,dbo.ExciseDetail_DNAR.QTY

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

GO

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

[Former Member]

How to join above two views?