Uncategorized

LOGO CRM Detaylı Teklif ve Sipariş Dökümü (SQL Rapor)

ALTER VIEW [dbo].[HK_CRM_TeklifDetay] AS
SELECT
MTP._CreatedDateTime AS TeklifTarihi
,MTP.ProposalId AS TeklifNo
–,MTPP.LineNo AS SatirNo
–,MTPP.LineType AS SatirTuru
,MTPR.ProductCode AS UrunKodu
,MTPR.Description AS UrunAciklama
,MTPP.Amount AS Miktar
,CTUN.UnitCode AS Birim
–,CTUN.UnitDescription AS BirimAciklama
,(CASE
WHEN MTP.ProposalState=0 THEN ‘Devam Ediyor’
WHEN MTP.ProposalState=1 THEN ‘Satış’
WHEN MTP.ProposalState=2 THEN ‘Satış İptal’
WHEN MTP.ProposalState=3 THEN ‘Müşteri İptali’
WHEN MTP.ProposalState=4 THEN ‘Olumsuz’
END) AS Durum
,CTCN.Description AS SatirDoviz
,MTPP.ExchangeRate AS DovizKuru
,MTPP.Price AS BirimFiyat
,MTPP.VAT AS KdvOrani
,MTPP.VatValue AS KdvTutar
,MTPP.PriceDiscounted AS IndirimliFiyat
,MTPP.SubTotal AS AraToplam
,MTPP.GrandTotal AS SatirToplam
,MTPP.LC_Price AS TLFiyat
,MTPP.LC_PriceDiscounted AS TLIndirimliFiyat
,MTPP.LC_SubTotal AS TLAraToplam
,MTPP.LC_VatValue AS TLKdvTutar
,MTPP.LC_GrandTotal AS TLSatirToplam
,MTP.ProposalDescription AS TeklifAciklama
,DATEADD(DD,CONVERT(INT,MTP.ERPPaymentPlan),(MTP._CreatedDateTime)) AS Vade
,MTF.FirmTitle AS TeklifFirma
,MTF.CountryOfMainAddress AS Ulke
,STU.Caption AS TeklifiOlusturan
–,STU.UserName AS UserName
,MTC.FullName AS FirmaYetkilisi
,(CASE
WHEN CTPS.StageDescription=’Initial’ THEN ‘Hazırlandı’
WHEN CTPS.StageDescription=’Lost’ THEN ‘Kaybedildi’
WHEN CTPS.StageDescription=’Sent’ THEN ‘Gönderildi’
END) AS TeklifAsamasi
,CTCT.CurrencySign AS TeklifDovizi
–,MTP.ERPTransactionCurrency
–,MTP.ERPPaymentPlan
,ERP.Code_ AS ERPCODE
,ORF.DATE_ AS OFFERDATE
,ORF.FICHENO AS OFFERNO
,ITM.CODE AS ITMCODE
,ITM.NAME AS ITMNAME
,ORL.AMOUNT AS ORLAMOUNT
,ORL.PRICE AS ORLPRICE
, ORL.LINENET AS ORLLINENET
,ORL.TRRATE AS ORLTRRATE
, ISNULL(CUR.CURCODE,’TRY’) AS IDCURR
, ORL.PRICE/ISNULL(NULLIF(ORL.TRRATE,0),1) AS IDPRICE
FROM
MT_Proposals_Products MTPP
LEFT JOIN MT_Proposals MTP ON MTPP.RelatedProposal=MTP.Oid
LEFT JOIN MT_Firm MTF ON MTP.ProposalFirm=MTF.Oid
LEFT JOIN MT_Contact MTC ON MTP.ProposalContact=MTC.Oid
LEFT JOIN ST_User STU ON MTP._CreatedBy=STU.Oid
LEFT JOIN CT_Currency_Types CTCT ON MTP.ProposalCurrency=CTCT.Oid
LEFT JOIN CT_Proposal_Stages CTPS ON CTPS.Oid=MTP.ProposalStage
LEFT JOIN MT_Product MTPR ON MTPP.ProductOid=MTPR.Oid
LEFT JOIN CT_Units CTUN ON CTUN.Oid=MTPP.Unit
LEFT JOIN CT_Currency_Types CTCN ON MTPP.Currency=CTCN.Oid
LEFT JOIN EI_Proposal_Relations ERP ON ERP.ProposalOid=MTP.Oid
LEFT JOIN GO3.dbo.LG_021_01_ORFICHE ORF ON ORF.LOGICALREF=ERP.Ref
LEFT JOIN GO3.dbo.LG_021_01_ORFLINE ORL ON ORL.ORDFICHEREF=ORF.LOGICALREF
INNER JOIN GO3.dbo.LG_021_ITEMS ITM ON ITM.LOGICALREF=ORL.STOCKREF AND ITM.CODE=MTPR.ProductCode
LEFT JOIN GO3.dbo.L_CURRENCYLIST CUR ON CUR.CURTYPE=ORL.PRCURR
where MTPP.LineType=0

GO

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir