Nebim V3 – Çeki Listesi Almak- V20.4

Merhaba Nebim V3 yeni gelen özellik paket detayı ile giriş yapılmış irsaliye ve ya faturalarda koli numarası bazlı çeki listesi almak için aşağıdaki raporu kullanabilirsiniz.

1-Paket Detayı giriş alanı

2- Aşağıdaki sorguyu SQL management studio üzerinde çalıştırıp Ihracat Faturaları kontrol Listesine Gelmesini Sağlıyoruz.

-- =============================================
-- 23.11.2020 11:02:29
-- 20.4.20286.1
-- Auto Generated by NebimV3ERP.exe
-- =============================================

USE NebimV3Master
GO

DECLARE @QueryID Char20 = '9155a712f7324312919e'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @V3ReportFileName Char100 = ISNULL((SELECT V3ReportFileName FROM bsQueryMaster WITH(NOLOCK) WHERE QueryID = @QueryID), N'Invoice_ES-CEKI-LISTESI.def.repx')
DECLARE @PivotFileName Char100 = ISNULL((SELECT PivotFileName FROM bsQueryMaster WITH(NOLOCK) WHERE QueryID = @QueryID), N'Invoice_ES.def2.pvt')
DECLARE @GridFileName Char100 = ISNULL((SELECT GridFileName FROM bsQueryMaster WITH(NOLOCK) WHERE QueryID = @QueryID), N'Invoice_ES.def2.grd')
DECLARE @SortOrder int = ISNULL((SELECT SortOrder FROM bsQueryMaster WITH(NOLOCK) WHERE QueryID = @QueryID), 0)
DECLARE @ViewTypeCode tinyint = ISNULL((SELECT ViewTypeCode FROM bsQueryMaster WITH(NOLOCK) WHERE QueryID = @QueryID), 1)
DECLARE @Description Char200 = ISNULL((SELECT Description FROM bsQueryMaster WITH(NOLOCK) WHERE QueryID = @QueryID), N'İhracat Faturaları Kontrol Listesi (Tek Sorguda)')
DECLARE @FromReportServer bit = ISNULL((SELECT FromReportServer FROM bsQueryMaster WITH(NOLOCK) WHERE QueryID = @QueryID), 0)
DECLARE @AdvancedQueryOption Char1000 = ISNULL((SELECT AdvancedQueryOption FROM bsQueryMaster WITH(NOLOCK) WHERE QueryID = @QueryID), N'')
DECLARE @DateColumnNamesMinMaxValueNotControl varchar(max)= ISNULL((SELECT DateColumnNamesMinMaxValueNotControl FROM bsQueryMaster WITH(NOLOCK) WHERE QueryID = @QueryID), N'')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @V3QueryDatabase AS DatabaseNames
INSERT INTO @V3QueryDatabase
SELECT DatabaseName FROM bsQueryDatabase WHERE QueryID = @QueryID
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @V3QueryReportDatabase AS DatabaseReportServers
INSERT INTO @V3QueryReportDatabase
SELECT DatabaseName, ReportServerCode FROM prQueryReportDatabase WHERE QueryID = @QueryID
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE @V3QueryMasterOtherLang AS ReportDescriptionsForOtherLang /*Varsayilan Degerleri Belirlemek Icin*/
INSERT INTO @V3QueryMasterOtherLang
SELECT LangCode = N'TR', VisibleName = N'CEKI_LISTESI_NSH', Description = N'İhracat Faturaları Kontrol Listesi (Tek Sorguda)'

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DELETE prQueryReportDatabase WHERE QueryID = @QueryID
DELETE bsQueryDatabase WHERE QueryID = @QueryID
DELETE bsQueryDetail WHERE QueryID = @QueryID
DELETE bsQueryChild WHERE QueryID = @QueryID
DELETE prQueryMasterDescription WHERE QueryID = @QueryID
DELETE bsQueryMaster WHERE QueryID = @QueryID
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO bsQueryMaster ( QueryID , ProgramName , VisibleName , SortOrder , QueryTypeCode , Description, DateColumnNamesMinMaxValueNotControl ,AdvancedQueryOption, ViewTypeCode , V3ReportFileName , PivotFileName , GridFileName , IsUnchangeable , FromReportServer
, DefaultFilterCols , ParameteredFields , NotBeFilteredFields , CanReportOtherCompanies , MasterDataTableQuery)
SELECT QueryID = @QueryID
, ProgramName = N'ListInvoice_ES'
, VisibleName = N'CEKI_LISTESI_NSH'
, SortOrder = @SortOrder
, QueryTypeCode = 2 /*1 Search, 2 Report, 3 Wizard*/
, Description = @Description
, DateColumnNamesMinMaxValueNotControl = @DateColumnNamesMinMaxValueNotControl
, AdvancedQueryOption = @AdvancedQueryOption
, ViewTypeCode = @ViewTypeCode
, V3ReportFileName = @V3ReportFileName
, PivotFileName = @PivotFileName
, GridFileName = @GridFileName
, IsUnchangeable = 0
, FromReportServer = 0
, DefaultFilterCols = N'InvoiceNumber'
, ParameteredFields = N'
@StartDate,System.DateTime,FirstDayOfCurrentMonth
;@EndDate,System.DateTime,v3Today
;InvoiceNumber,System.String,NULL'
, NotBeFilteredFields = N'VatRate
,PCTRate
,TDisRate1
,TDisRate2
,TDisRate3
,TDisRate4
,TDisRate5
,LDisRate1
,LDisRate2
,LDisRate3
,LDisRate4
,LDisRate5
,PriceCurrencyCode
,PriceExchangeRate
,Price
,Doc_Price
,Doc_LDiscount1
,Doc_LDiscount2
,Doc_LDiscount3
,Doc_LDiscount4
,Doc_LDiscount5
,Doc_LDiscountTotal
,Doc_TDiscount1
,Doc_TDiscount2
,Doc_TDiscount3
,Doc_TDiscount4
,Doc_TDiscount5
,Doc_TDiscountTotal
,Doc_Pct
,Doc_Vat
,Doc_NetAmount
,Loc_CurrencyCode
,Loc_ExchangeRate
,Loc_Price
,Loc_Amount
,Loc_LDiscount1
,Loc_LDiscount2
,Loc_LDiscount3
,Loc_LDiscount4
,Loc_LDiscount5
,Loc_LDiscountTotal
,Loc_TDiscount1
,Loc_TDiscount2
,Loc_TDiscount3
,Loc_TDiscount4
,Loc_TDiscount5
,Loc_TDiscountTotal
,Loc_Pct
,Loc_Vat
,Loc_NetAmount
,Loc_VatDeducation
,Doc_VatDeducation
,ProductAtt01Desc
,ProductAtt02Desc
,ProductAtt03Desc
,ProductAtt04Desc
,ProductAtt05Desc
,ProductAtt06Desc
,ProductAtt07Desc
,ProductAtt08Desc
,ProductAtt09Desc
,ProductAtt10Desc
,ProductAtt11Desc
,ProductAtt12Desc
,ProductAtt13Desc
,ProductAtt14Desc
,ProductAtt15Desc
,CompanyCode'
, CanReportOtherCompanies = 0
, MasterDataTableQuery =
N'
select

tpShipmentLinePickingDetails.PackageNumber
,ProductFilterWithDescription.ProductHierarchyLevel02
,AllInvoices.ItemCode
,ProductFilterWithDescription.ProductDescription
,ProductFilterWithDescription.ProductAtt03Desc
, ColorDescription = ISNULL((SELECT ColorDescription FROM cdColorDesc WITH(NOLOCK) WHERE cdColorDesc.ColorCode = AllInvoices.ColorCode AND cdColorDesc.LangCode = ''TR''), SPACE(0))
, CustomerDescription = ISNULL((SELECT CurrAccDescription FROM cdCurrAccDesc WITH(NOLOCK) WHERE cdCurrAccDesc.CurrAccTypeCode = AllInvoices.CurrAccTypeCode AND cdCurrAccDesc.CurrAccCode = AllInvoices.CurrAccCode AND cdCurrAccDesc.LangCode = ''TR''), SPACE(0))
,AllInvoices.ItemDim1Code
,AllInvoices.Qty1
,AllInvoices.Doc_Price
,AllInvoices.Doc_Amount
,PackedWeight
from AllInvoices

Left Join tpShipmentLinePickingDetails On tpShipmentLinePickingDetails.ShipmentLineID = AllInvoices.ShipmentLineID
Left Join ProductFilterWithDescription(''TR'') ON ProductFilterWithDescription.ProductCode = AllInvoices.ItemCode
where AllInvoices.ProcessCode = ''ES''
and {InvoiceNumber}
'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DELETE prQueryAvailableView WHERE QueryID = @QueryID
INSERT INTO prQueryAvailableView (QueryID , ViewTypeCode)
SELECT @QueryID , ViewTypeCode
FROM bsViewType WITH(NOLOCK) WHERE ViewTypeCode IN (3)
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO bsQueryDatabase(QueryID , DatabaseName)
SELECT @QueryID , DatabaseName FROM @V3QueryDatabase
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO prQueryReportDatabase(QueryID , DatabaseName , ReportServerCode)
SELECT @QueryID , DatabaseName , ReportServerCode FROM @V3QueryReportDatabase
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO prQueryMasterDescription (QueryID, LangCode , VisibleName , Description)
SELECT QueryID = @QueryID , LangCode , VisibleName , Description FROM @V3QueryMasterOtherLang AS V3QueryMasterOtherLang
WHERE EXISTS (SELECT * FROM bsLanguage WHERE V3QueryMasterOtherLang.LangCode = bsLanguage.LangCode)
GO

Sonuç:

CEKI-LISTESI-SQL+ REPX