using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace MMDAL.Query.Item { public static class ItemQB { // ── DIM* SelectList queries — GB4 parity: Item.svc/DIMItemBrandSelectList, DIMItemCategorySelectList, // DIMItemGroupSelectList, DIMItemModelSelectList, DIMItemSubCategorySelectList. // Denormalized reporting/analytics dropdown source (DIMITEMCLASS) — NOT the live master tables // (MITEMBRAND/MITEMCATEGORY/MITEMGROUP/MMODEL/MITEMSUBCATEGORY) used by the existing // GetSelectListItemBrand/Category/Group/Model/SubCategory endpoints. Unpaged SELECT DISTINCT. public const string GET_DIM_ITEMBRAND_SELECTLIST = @" SELECT DISTINCT dimitemclass.ITEMBRANDID AS Id, dimitemclass.ITEMBRANDCODE AS Code, dimitemclass.ITEMBRANDNAME AS Name FROM DIMITEMCLASS dimitemclass WHERE 1=1"; public const string GET_DIM_ITEMCATEGORY_SELECTLIST = @" SELECT DISTINCT dimitemclass.ITEMCATEGORYID AS Id, dimitemclass.ITEMCATEGORYCODE AS Code, dimitemclass.ITEMCATEGORYNAME AS Name FROM DIMITEMCLASS dimitemclass WHERE 1=1"; public const string GET_DIM_ITEMGROUP_SELECTLIST = @" SELECT DISTINCT dimitemclass.ITEMGROUPID AS Id, dimitemclass.ITEMGROUPCODE AS Code, dimitemclass.ITEMGROUPNAME AS Name FROM DIMITEMCLASS dimitemclass WHERE 1=1"; public const string GET_DIM_ITEMMODEL_SELECTLIST = @" SELECT DISTINCT dimitemclass.MODELID AS Id, dimitemclass.MODELCODE AS Code, dimitemclass.MODELNAME AS Name FROM DIMITEMCLASS dimitemclass WHERE 1=1"; public const string GET_DIM_ITEMSUBCATEGORY_SELECTLIST = @" SELECT DISTINCT dimitemclass.ITEMSUBCATEGORYID AS Id, dimitemclass.ITEMSUBCATEGORYCODE AS Code, dimitemclass.ITEMSUBCATEGORYNAME AS Name FROM DIMITEMCLASS dimitemclass WHERE 1=1"; public const string ITEMMASTER_REPORT = @"SELECT MITEM.ITEMID AS ItemId, MITEM.ITEMCODE AS ItemCode, MITEM.ITEMNAME AS ItemName, MITEM.ITEMSHORTNAME AS ItemShortName, MITEM.ITEMDESCRIPTION AS ItemDescription, MITEM.CODECATEGORYID AS CodeCategoryId, MITEM.CATEGORYID AS CategoryId, MITEM.SUBCATEGORYID AS SubCategoryId, MITEM.ITEMGROUPID AS ItemGroupId, MITEM.ITEMBRANDID AS ItemBrandId, MITEM.MODELID AS ModelId, MITEM.PRICECATEGORYID AS PriceCategoryId, MITEM.MATERIALGROUPID AS MaterialGroupId, MITEM.PROCESSGROUPID AS ProcessGroupId, MITEM.MATERIALACCOUNTID AS MaterialAccountId, MITEM.ISSALEABLEITEM AS IsSaleableItem, MITEM.ISACTIVITY AS IsActivity, MITEM.INTRODUCEDDATE AS IntroducedDate, MITEM.ITEMORTEMPLATE AS ItemOrTemplate, MITEM.DRAWINGNUMBER AS DrawingNumber, MITEM.CATALOGUENUMBER AS CatalogueNumber, MITEM.PARAMETERSETID AS ParameterSetId, MITEM.ISITEMREVISED AS IsItemRevised, MITEM.ITEMREVISEDFROM AS ItemRevisedFrom, MITEM.ITEMREVISEDTO AS ItemRevisedTo, MITEM.ISTRANSACTABLE AS IsTransactable, MITEM.ISPURCHASABLE AS IsPurchasable, MITEM.ISINTERNALORDER AS IsInternalOrder, MITEM.ISBUYBACK AS IsBuyBack, MITEM.ISVARIABLESIZEITEM AS IsVariableSizeItem, MITEM.ISSPARE AS IsSpare, MITEM.ISBUYWITHOUTORDER AS IsBuyWithoutOrder, MITEM.BOMTYPE AS BomType, MITEM.ISSTOCKPOSTING AS IsStockPosting, MITEM.ISBATCHSTOCK AS IsBatchStock, MITEM.ISUNIQUEBATCH AS IsUniqueBatch, MITEM.STOCKVALUATIONTYPE AS StockValuationType, MITEM.TOLLERANCETYPE AS TolleranceType, MITEM.TOLLERANCEVALUE AS TolleranceValue, MITEM.ISSKUTRACKINGAPPLICABLE AS IsSkuTrackingApplicable, MITEM.DEFAULTSKUID AS DefaultSkuId, MITEM.ISSTYLEAPPLICABLE AS IsStyleApplicable, MITEM.ISCOLORAPPLICABLE AS IsColorApplicable, MITEM.ISSIZEAPPLICABLE AS IsSizeApplicable, MITEM.ISPROCESSAPPLICABLE AS IsProcessApplicable, MITEM.ISMAKEAPPLICABLE AS IsMakeApplicable, MITEM.ISPRICEPOINTAPPLICABLE AS IsPricePointApplicable, MITEM.ISGRADEAPPLICABLE AS IsGradeApplicable, MITEM.ISSEASONAPPLICABLE AS IsSeasonApplicable, MITEM.PURCHASEUOMID AS PurchaseUomId, MITEM.STOCKUOMID AS StockUomId, MITEM.CONVERSIONFACTOR AS ConversionFactor, MITEM.UOMSETID AS UomSetId, MITEM.ISPACKINGSDETAILSREQUIRED AS IsPackingsDetailsRequired, MITEM.PACKSETID AS PackSetId, MITEM.DEFAULTPACKID AS DefaultPackId, MITEM.CASECOUNT AS CaseCount, MITEM.INSPECTIONTYPE AS InspectionType, MITEM.QUALITYPARAMETERSETID AS QualityParameterSetId, MITEM.ISEXPIRYTRACKINGREQUIRED AS IsExpiryTrackingRequired, MITEM.LIFEINDAYS AS LifeInDays, MITEM.RATETYPE AS RateType, MITEM.PRICEFORTYPE AS PriceForType, MITEM.LINKITEMID AS LinkItemId, MITEM.LINKVARIATION AS LinkVariation, MITEM.PRICINGREFERENCENUMBER AS PricingReferenceNumber, MITEM.ISDISCOUNTAPPLICABLE AS IsDiscountApplicable, MITEM.MAXIMUMDISCOUNTPERCENTAGE AS MaximumDiscountPercentage, MITEM.ISFREEAPPLICABLE AS IsFreeApplicable, MITEM.MRPRATE AS MrpRate, MITEM.ISSERVICEAPPLICABLE AS IsServiceApplicable, MITEM.ISWARRANTYAPPLICABLE AS IsWarrantyApplicable, MITEM.WARRANTYPERIOD AS WarrantyPeriod, MITEM.ISPOSTTOCUSTOMERPRODUCT AS IsPostToCustomerProduct, MITEM.ISINSTALLATIONTRACKINGREQUIRED AS IsInstallationTrackingRequired, MITEM.ISDEFECTIVETOBERETURNED AS IsDefectiveToBeReturned, MITEM.HEIGHT AS Height, MITEM.WEIGHT AS Weight, MITEM.LENGTH AS Length, MITEM.BREATH AS Breath, MITEM.THICKNESS AS Thickness, MITEM.ISDUTYAPPLICABLE AS IsDutyApplicable, MITEM.ISCENVATAPPLICABLE AS IsCenvatApplicable, MITEM.DUTYTABLEID AS DutyTableId, MITEM.TAXTABLEID AS TaxTableId, MITEM.ISTAXTYPECHANGEABLE AS IsTaxTypeChangeable, MITEM.VATCLAIMTYPE AS VatClaimType, MITEM.MASTERPLANMETHOD AS MasterPlanMethod, MITEM.MRPPLANMETHOD AS MrpPlanMethod, MITEM.LEADTIME AS LeadTime, MITEM.REVIEWPERIOD AS ReviewPeriod, MITEM.MINIMUMORDERQUANTITY AS MinimumOrderQuantity, MITEM.MINLEVEL AS MinLevel, MITEM.MAXLEVEL AS MaxLevel, MITEM.LOTSIZE AS LotSize, MITEM.ROUTINGID AS RoutingId, MITEM.IsExciseApplicable AS IsExciseApplicable, MITEM.PURCHASERATE AS PurchaseRate, MITEM.PURCHASERATEINCLUSIVE AS PurchaseRateInclusive, MITEM.PURCHASECOSTNLC AS PurchaseCostNlc, MITEM.SALESRATE AS SalesRate, MITEM.SALESRATEINCLUSIVE AS SalesRateInclusive, MITEM.PLANNERID AS PlannerId, MITEM.PROCUREMENTINCHARGEID AS ProcurementInchargeId, MITEM.PRODUCTIONNATURE AS ProductionNature, MITEM.ISMAKEORBUY AS IsMakeOrBuy, MITEM.ISMANUFACTURABLE AS IsManufacturable, MITEM.ISBULKISSUEITEM AS IsBulkIssueItem, MITEM.SHRINKAGEPERCENTAGE AS ShrinkagePercentage, MITEM.COMPONENTYIELD AS ComponentYield, MITEM.PREPROCESSINGLEADTIME AS PreProcessingLeadTime, MITEM.PROCESSINGLEADTIME AS ProcessingLeadTime, MITEM.POSTPROCESSINGLEADTIME AS PostProcessingLeadTime, MITEM.LEADTIMELOTSIZE AS LeadTimeLotSize, MITEM.FIXEDLEADTIME AS FixedLeadTime, MITEM.VARIABLELEADTIME AS VariableLeadTime, MITEM.VARIABLEQUANTITY AS VariableQuantity, MITEM.MANUFACTURINGLEADTIME AS ManufacturingLeadTime, MITEM.LEADTIMEOFFSET AS LeadTimeOffset, MITEM.BOMYIELDPERCENTAGE AS BomYieldPercentage, MITEM.SAFETYSTOCKMETHOD AS SafetyStockMethod, MITEM.SAFETYDAYS AS SafetyDays, MITEM.MRPPLANNEDPERCENTAGE AS MrpPlannedPercentage, MITEM.LOTMETHOD AS LotMethod, MITEM.INVENTORYMETHOD AS InventoryMethod, MITEM.LOWESTLEVELINBOM AS LowestLevelInBom, MITEM.ISBINTRACKINGREQUIRED AS IsBinTrackingRequired, MITEM.DUTYTARIFFID AS DutyTariffId, MITEM.SORTORDER AS SortOrder, MITEM.VERSION AS Version, MITEM.STATUS AS Status, MITEM.CREATEDBYID AS CreatedById, MITEM.CREATEDON AS CreatedOn, MITEM.MODIFIEDBYID AS ModifiedById, MITEM.MODIFIEDON AS ModifiedOn, MITEM.DEFAULTCMSID AS DefaultCmsId, MITEM.VATCOMMODITYID AS VatCommodityId, MITEM.RAWMATERIALITEMID AS RawMaterialItemId, MITEM.RAWMATERIALSKUID AS RawMaterialSkuId, MITEM.RAWMATERIALQUANTITY AS RawMaterialQuantity, MITEM.NESTINGPLANID AS NestingPlanId, MITEM.SOURCETYPE AS SourceType, MITEM.PURCHASECURRENCYID AS PurchaseCurrencyId, MITEM.LASTPURCHASECURRENCYID AS LastPurchaseCurrencyId, MITEM.LASTPURCHASERATE AS LastPurchaseRate, MITEM.ITEMTYPEID AS ItemTypeId, MITEM.DUTYFORMTYPEID AS DutyFormTypeId, MITEM.PLANNINGGROUPID AS PlanningGroupId, MITEM.ISASSET AS IsAsset, MITEM.ASSETTYPEID AS AssetTypeId, MITEM.ASSETLINKTYPE AS AssetLinkType, MITEM.ASSETBATCHLINKTYPE AS AssetBatchLinkType, MITEM.ASSETID AS AssetId, MITEM.GSTTYPE AS GstType, MITEM.GSTTARIFFID AS GstTariffId, MITEM.ITCTYPE AS ItcType, MITEM.GSTTABLEID AS GstTableId, MITEM.TENANTID AS TenantId, MITEM.SIZESETDETAILID AS SizeSetDetailId, MITEM.COLORID AS ColorId, MITEM.BOMTOLLERANCEVALUE AS BomTolleranceValue, MITEM.CALLSETID AS CallSetId, MITEM.SAMPLESPERCENTAGE AS SamplesPercentage, MITEM.QUALITYPLANREFERENCENO AS QualityPlanReferenceNo, MITEM.DEFAULTBOMID AS DefaultBomId, MITEM.CYCLECLASSID AS CycleClassId, MITEM.RESERVATIONTYPE AS ReservationType, MITEM.ACTIVITYID AS ActivityId, MITEM.ISEWTYPE AS IsEwType, MITEM.EWCATEGORYID AS EwCategoryId, MITEM.EWPRICEFROM AS EwPriceFrom, MITEM.EWPRICETO AS EwPriceTo, MITEM.COMPREHENSIVEMONTHS AS ComprehensiveMonths, MITEM.NONCOMPREHENSIVEMONTHS AS NonComprehensiveMonths, MITEM.EWVENDORID AS EwVendorId, MITEM.MAXMONTHS AS MaxMonths, MITEM.ISEWOWN AS IsEwOwn, MITEM.ISPREPAID AS IsPrepaid, MITEM.NOOFPERIODS AS NoOfPeriods, MITEM.DEFERALPLANID AS DeferalPlanId, MITEM.SIZESETID AS SizeSetId, MITEM.MEASURETYPEID AS MeasureTypeId, MITEM.SKUSETTINGID AS SkuSettingId, MITEM.SUPPLYGROUPID AS SupplyGroupId, MITEM.REVISEDFROMITEMID AS RevisedFromItemId, MITEM.REVISIONNUMBER AS RevisionNumber, MITEM.THUMBNAIL AS Thumbnail FROM MITEM MITEM WHERE 1 = 1"; public const string ITEMMASTER_REPORT_COUNT = @"SELECT TOP 500 TEVENTLOG.EVENTTEXT AS EventText , TEVENTLOG.LINK AS Link, TEVENTLOG.SOURCEID AS SourceId, TEVENTLOG.USERID AS UserId, TEVENTLOG.TAGS AS Tags, TEVENTLOG.EVENTVALUE AS EventValue, TEVENTLOG.DATA AS Data, TEVENTLOG.GEO AS Geo, TEVENTLOG.EVENTTYPEID AS EventTypeId, TEVENTLOG.EVENTCLASSID AS EventClassId, TEVENTLOG.LOGBOOKID AS LogBookId, TEVENTLOG.DATAID AS DataId, TEVENTLOG.TIMESTAMP AS Timestamp, TEVENTLOG.EVENTCHANNELID AS EventChannelId, TEVENTLOG.OUID AS Ouid, TEVENTLOG.MACHINEIP AS MachineIp, TEVENTLOG.MODEOFWORKING AS ModeOfWorking, TEVENTLOG.LOGINEVENTLOGID AS LoginEventLogId, TEVENTLOG.EVENTLOGID AS EventLogId FROM TEVENTLOG TEVENTLOG WHERE 1 = 1 "; public const string GET_Item_SQL = @" WITH PagedItem AS ( SELECT MI.ITEMID AS ItemPicklistId, MI.ITEMCODE AS ItemPicklistCode, MI.ITEMNAME AS ItemPicklistName, MI.ITEMSHORTNAME AS ItemPicklistShortName, MI.PurchaseUOMId AS PurchaseUOMId, MI.DefaultSKUId AS DefaultSKUId, ROW_NUMBER() OVER (ORDER BY MI.ITEMID) AS RowNum FROM MITEM MI ) SELECT ItemPicklistId, ItemPicklistCode, ItemPicklistName, ItemPicklistShortName, PurchaseUOMId, DefaultSKUId FROM PagedItem WHERE RowNum BETWEEN (@firstnumber + 1) AND (@firstnumber + @maxresult);"; // Inner SELECT — CriteriaDTO conditions are appended to WHERE 1=1 before this is wrapped in a CTE public const string GET_SELECTLIST_ITEM_INNER = @" SELECT I.ITEMID AS Id, I.ITEMCODE AS Code, I.ITEMNAME AS Name, I.ITEMSHORTNAME AS ShortName, I.THUMBNAIL AS ImageUrl, I.STOCKUOMID AS StockUOMId, U.UOMCODE AS StockUOMCode, U.UOMNAME AS StockUOMName, I.ITEMDESCRIPTION AS ItemDescription, I.CATALOGUENUMBER AS AliasCatalogueNumber, I.DRAWINGNUMBER AS AliasDrawingNumber, I.ITEMGROUPID AS AliasItemGroupId, IG.ITEMGROUPCODE AS AliasItemGroupCode, IG.ITEMGROUPNAME AS AliasItemGroupName, I.DEFAULTBOMID AS DefaultBomId, I.ISSTYLEAPPLICABLE AS IsStyleApplicable, I.ISCOLORAPPLICABLE AS IsColorApplicable, I.ISSIZEAPPLICABLE AS IsSizeApplicable, I.ISPROCESSAPPLICABLE AS IsProcessApplicable, I.ISMAKEAPPLICABLE AS IsMakeApplicable, I.ISPRICEPOINTAPPLICABLE AS IsPricePointApplicable, I.ISGRADEAPPLICABLE AS IsGradeApplicable, I.ISSEASONAPPLICABLE AS IsSeasonApplicable, I.ISMAKEORBUY AS IsMakeOrBuy, I.DEFAULTSKUID AS DefaultSkuId, I.ISSKUTRACKINGAPPLICABLE AS IsSkuTrackingApplicable, I.THUMBNAIL AS ThumbNail, S.SKUCODE AS DefaultSkuCode, S.SKUNAME AS DefaultSkuName, I.LENGTH AS Length, I.MATERIALGROUPID AS MaterialGroupId, MG.MATERIALGROUPCODE AS MaterialGroupCode, MG.MATERIALGROUPNAME AS MaterialGroupName, ROW_NUMBER() OVER (ORDER BY I.ITEMID) AS RowNum FROM MITEM I LEFT JOIN MUOM U ON U.UOMID = I.STOCKUOMID LEFT JOIN MITEMGROUP IG ON IG.ITEMGROUPID = I.ITEMGROUPID LEFT JOIN MSKU S ON S.SKUID = I.DEFAULTSKUID LEFT JOIN MMATERIALGROUP MG ON MG.MATERIALGROUPID = I.MATERIALGROUPID WHERE 1=1"; // Outer SELECT — appended after WITH PagedItem AS ({inner with criteria applied}) public const string GET_SELECTLIST_ITEM_OUTER = @" SELECT Id, Code, Name, ShortName, ImageUrl, StockUOMId, StockUOMCode, StockUOMName, ItemDescription, AliasCatalogueNumber, AliasDrawingNumber, AliasItemGroupId, AliasItemGroupCode, AliasItemGroupName, DefaultBomId, IsStyleApplicable, IsColorApplicable, IsSizeApplicable, IsProcessApplicable, IsMakeApplicable, IsPricePointApplicable, IsGradeApplicable, IsSeasonApplicable, IsMakeOrBuy, DefaultSkuId, IsSkuTrackingApplicable, ThumbNail, DefaultSkuCode, DefaultSkuName, Length, MaterialGroupId, MaterialGroupCode, MaterialGroupName FROM PagedItem WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string GET_SELECT_LIST_PRODUCT_FAMILY = @" SELECT I.ITEMID AS Id, I.ITEMCODE AS Code, I.ITEMNAME AS Name, I.MODELID AS ModelId, I.ITEMSHORTNAME AS ShortName, I.THUMBNAIL AS ImageUrl, I.STOCKUOMID AS StockUOMId, U.UOMCODE AS StockUOMCode, U.UOMNAME AS StockUOMName, I.ITEMDESCRIPTION AS ItemDescription, I.CATALOGUENUMBER AS AliasCatalogueNumber, I.DRAWINGNUMBER AS AliasDrawingNumber, I.ITEMGROUPID AS AliasItemGroupId, IG.ITEMGROUPCODE AS AliasItemGroupCode, IG.ITEMGROUPNAME AS AliasItemGroupName, I.DEFAULTBOMID AS DefaultBomId, I.ISSTYLEAPPLICABLE AS IsStyleApplicable, I.ISCOLORAPPLICABLE AS IsColorApplicable, I.ISSIZEAPPLICABLE AS IsSizeApplicable, I.ISPROCESSAPPLICABLE AS IsProcessApplicable, I.ISMAKEAPPLICABLE AS IsMakeApplicable, I.ISPRICEPOINTAPPLICABLE AS IsPricePointApplicable, I.ISGRADEAPPLICABLE AS IsGradeApplicable, I.ISSEASONAPPLICABLE AS IsSeasonApplicable, I.ISMAKEORBUY AS IsMakeOrBuy, I.DEFAULTSKUID AS DefaultSkuId, I.ISSKUTRACKINGAPPLICABLE AS IsSkuTrackingApplicable, I.THUMBNAIL AS ThumbNail, S.SKUCODE AS DefaultSkuCode, S.SKUNAME AS DefaultSkuName, I.LENGTH AS Length, I.MATERIALGROUPID AS MaterialGroupId, MG.MATERIALGROUPCODE AS MaterialGroupCode, MG.MATERIALGROUPNAME AS MaterialGroupName, MO.MODELCODE AS ModelCode, MO.MODELNAME AS ModelName FROM MITEM I LEFT JOIN MUOM U ON U.UOMID = I.STOCKUOMID LEFT JOIN MITEMGROUP IG ON IG.ITEMGROUPID = I.ITEMGROUPID LEFT JOIN MSKU S ON S.SKUID = I.DEFAULTSKUID LEFT JOIN MMATERIALGROUP MG ON MG.MATERIALGROUPID = I.MATERIALGROUPID LEFT JOIN MMODEL MO ON MO.MODELID = I.MODELID WHERE I.TENANTID = @TenantId {DYNAMIC_WHERE} ORDER BY I.ITEMNAME ASC"; public const string SAVE_ITEM = @"INSERT INTO MITEM ( ITEMID, ITEMCODE, ITEMNAME, ITEMSHORTNAME, ITEMDESCRIPTION, CODECATEGORYID, CATEGORYID, SUBCATEGORYID, ITEMGROUPID, ITEMBRANDID, MODELID, PRICECATEGORYID, MATERIALGROUPID, PROCESSGROUPID, MATERIALACCOUNTID, ISSALEABLEITEM, ISACTIVITY, INTRODUCEDDATE, ITEMORTEMPLATE, DRAWINGNUMBER, CATALOGUENUMBER, PARAMETERSETID, ISITEMREVISED, ITEMREVISEDFROM, ITEMREVISEDTO, ISTRANSACTABLE, ISPURCHASABLE, ISINTERNALORDER, ISBUYBACK, ISVARIABLESIZEITEM, ISSPARE, ISBUYWITHOUTORDER, BOMTYPE, ISSTOCKPOSTING, ISBATCHSTOCK, ISUNIQUEBATCH, STOCKVALUATIONTYPE, TOLLERANCETYPE, TOLLERANCEVALUE, ISSKUTRACKINGAPPLICABLE, DEFAULTSKUID, ISSTYLEAPPLICABLE, ISCOLORAPPLICABLE, ISSIZEAPPLICABLE, ISPROCESSAPPLICABLE, ISMAKEAPPLICABLE, ISPRICEPOINTAPPLICABLE, ISGRADEAPPLICABLE, ISSEASONAPPLICABLE, PURCHASEUOMID, STOCKUOMID, CONVERSIONFACTOR, UOMSETID, ISPACKINGSDETAILSREQUIRED, PACKSETID, DEFAULTPACKID, CASECOUNT, INSPECTIONTYPE, QUALITYPARAMETERSETID, ISEXPIRYTRACKINGREQUIRED, LIFEINDAYS, RATETYPE, PRICEFORTYPE, LINKITEMID, LINKVARIATION, PRICINGREFERENCENUMBER, ISDISCOUNTAPPLICABLE, MAXIMUMDISCOUNTPERCENTAGE, ISFREEAPPLICABLE, MRPRATE, ISSERVICEAPPLICABLE, ISWARRANTYAPPLICABLE, WARRANTYPERIOD, ISPOSTTOCUSTOMERPRODUCT, ISINSTALLATIONTRACKINGREQUIRED, ISDEFECTIVETOBERETURNED, HEIGHT, WEIGHT, LENGTH, BREATH, THICKNESS, ISDUTYAPPLICABLE, ISCENVATAPPLICABLE, DUTYTABLEID, TAXTABLEID, ISTAXTYPECHANGEABLE, VATCLAIMTYPE, MASTERPLANMETHOD, MRPPLANMETHOD, LEADTIME, REVIEWPERIOD, MINIMUMORDERQUANTITY, MINLEVEL, MAXLEVEL, LOTSIZE, ROUTINGID, ISEXCISEAPPLICABLE, PURCHASERATE, PURCHASERATEINCLUSIVE, PURCHASECOSTNLC, SALESRATE, SALESRATEINCLUSIVE, PLANNERID, PROCUREMENTINCHARGEID, PRODUCTIONNATURE, ISMAKEORBUY, ISMANUFACTURABLE, ISBULKISSUEITEM, SHRINKAGEPERCENTAGE, COMPONENTYIELD, PREPROCESSINGLEADTIME, PROCESSINGLEADTIME, POSTPROCESSINGLEADTIME, LEADTIMELOTSIZE, FIXEDLEADTIME, VARIABLELEADTIME, VARIABLEQUANTITY, MANUFACTURINGLEADTIME, LEADTIMEOFFSET, BOMYIELDPERCENTAGE, SAFETYSTOCKMETHOD, SAFETYDAYS, MRPPLANNEDPERCENTAGE, LOTMETHOD, INVENTORYMETHOD, LOWESTLEVELINBOM, ISBINTRACKINGREQUIRED, DUTYTARIFFID, SORTORDER, VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, DEFAULTCMSID, VATCOMMODITYID, RAWMATERIALITEMID, RAWMATERIALSKUID, RAWMATERIALQUANTITY, NESTINGPLANID, SOURCETYPE, PURCHASECURRENCYID, LASTPURCHASECURRENCYID, LASTPURCHASERATE, ITEMTYPEID, DUTYFORMTYPEID, PLANNINGGROUPID, ISASSET, ASSETTYPEID, ASSETLINKTYPE, ASSETBATCHLINKTYPE, ASSETID, GSTTYPE, GSTTARIFFID, ITCTYPE, GSTTABLEID, TENANTID, SIZESETDETAILID, COLORID, BOMTOLLERANCEVALUE, CALLSETID, SAMPLESPERCENTAGE, QUALITYPLANREFERENCENO, DEFAULTBOMID, CYCLECLASSID, RESERVATIONTYPE, ACTIVITYID, ISEWTYPE, EWCATEGORYID, EWPRICEFROM, EWPRICETO, COMPREHENSIVEMONTHS, NONCOMPREHENSIVEMONTHS, EWVENDORID, MAXMONTHS, ISEWOWN, ISPREPAID, NOOFPERIODS, DEFERALPLANID, SIZESETID, MEASURETYPEID, SKUSETTINGID, SUPPLYGROUPID, REVISEDFROMITEMID, REVISIONNUMBER, THUMBNAIL, DRAWINGREVISIONNUMBER, SHAPEID, MATERIALID, D1, D2, D3, D4, D5, DISPLAYD1, DISPLAYD2, DISPLAYD3, DISPLAYD4, DISPLAYD5, D1DISPLAYUNITID, D2DISPLAYUNITID, D3DISPLAYUNITID, D4DISPLAYUNITID, D5DISPLAYUNITID, STANDARDLENGTHBASE, WEIGHTPERMETER, VOLUMEPERMETER ) VALUES ( @ItemId, @ItemCode, @ItemName, @ItemShortName, @ItemDescription, @CodeCategoryId, @ItemCategoryId, @SubCategoryId, @ItemGroupId, @ItemBrandId, @ModelId, @PriceCategoryId, @MaterialGroupId, @ProcessGroupId, @MaterialAccountId, @ItemIsSaleableItem, @ItemIsActivity, @ItemIntroducedDate, @ItemItemorTemplate, @ItemDrawingNumber, @ItemCatalogueNumber, @ParameterSetId, @ItemIsItemRevised, @ItemItemRevisedFrom, @ItemItemRevisedTo, @ItemIsTransactable, @ItemIsPurchasable, @ItemIsInternalOrder, @ItemIsBuyBack, @ItemIsVariablesizeItem, @ItemIsSpare, @ItemIsBuyWithoutOrder, @ItemBOMType, @ItemIsStockPosting, @ItemIsBatchStock, @ItemIsUniqueBatch, @ItemStockValuationType, @ItemTolleranceType, @ItemTolleranceValue, @ItemIsSKUTrackingapplicable, @DefaultSkuId, @ItemIsStyleApplicable, @ItemIsColorApplicable, @ItemIsSizeApplicable, @ItemIsProcessApplicable, @ItemIsMakeApplicable, @ItemIsPricePointApplicable, @ItemIsGradeApplicable, @ItemIsSeasonApplicable, @PurchaseUOMId, @StockUOMId, @ItemConversionFactor, @UOMSetId, @ItemIsPackingsDetailsRequired, @PackSetId, @DefaultPackId, @ItemCaseCount, @ItemInspectionType, @QualityParameterSetId, @ItemIsExpirytrackingRequired, @ItemLifeinDays, @ItemRateType, @ItemPriceForType, @LinkItemId, @ItemLinkVariation, @ItemPricingReferenceNumber, @ItemIsDiscountApplicable, @ItemMaximumDiscountPercentage, @ItemIsFreeApplicable, @ItemMRPRate, @ItemIsServiceApplicable, @ItemIsWarrantyApplicable, @ItemWarrantyPeriod, @ItemIsPostToCustomerProduct, @ItemIsInstallationTrackingRequired, @ItemIsDefectiveToBeReturned, @ItemHeight, @ItemWeight, @ItemLength, @ItemBreath, @ItemThickness, @ItemIsDutyApplicable, @ItemIsCENVATApplicable, @DutyTableId, @TaxTableId, @ItemIsTaxTypeChangeable, @ItemVATClaimType, @ItemMasterPlanMethod, @ItemMRPPlanMethod, @ItemLeadTime, @ItemReviewPeriod, @ItemMinimumOrderQuantity, @ItemMinLevel, @ItemMaxLevel, @ItemLotSize, @RoutingId, @ItemIsExciseApplicable, @ItemPurchaseRate, @ItemPurchaseRateInclusive, @ItemPurchaseCostNLC, @ItemSalesRate, @ItemSalesRateInclusive, @PlannerId, @ProcurmentInchargeId, @ItemProductionNature, @ItemIsMakeOrBuy, @ItemIsManufacturable, @ItemIsBulkIssueItem, @ItemShrinkagePercentage, @ItemComponentYield, @ItemPreProcessingLeadTime, @ItemProcessingLeadTime, @ItemPostProcessingLeadTime, @ItemLeadTimeLotSize, @ItemFixedLeadTime, @ItemVariableLeadTime, @ItemVariableQuantity, @ItemManufacturingLeadTime, @ItemLeadTimeOffSet, @ItemBOMYieldPercentage, @ItemSafetyStockMethod, @ItemSafetyDays, @ItemMRPPlannedPercentage, @ItemLotMethod, @ItemInventoryMethod, @ItemLowestLevelInBOM, @ItemIsBinTrackingRequired, @DutyTariffId, @ItemSortOrder, @ItemVersion, @ItemStatus, @ItemCreatedById, @ItemCreatedOn, @ItemModifiedById, @ItemModifiedOn, @ItemDefaultCmsId, @VatCommodityId, @RawMaterialItemId, @RawMaterialSkuId, @RawMaterialQuantity, @NestingPlanId, @ItemSourceType, @PurchaseCurrencyId, @LastPurchaseCurrencyId, @ItemLastPurchaseRate, @ItemTypeId, @DutyFormTypeId, @PlanningGroupId, @ItemIsAsset, @AssetTypeId, @ItemAssetLinkType, @ItemAssetBatchLinkType, @AssetId, @ItemGSTType, @GSTTariffId, @ItemITCType, @GstTableId, @TenantId, @SizeSetDetailId, @ColorId, @ItemBOMTolleranceValue, @CallSetId, @ItemSamplesPercentage, @ItemQualityPlanReferenceNo, @DefaultBOMId, @CycleClassId, @ItemReservationType, @ActivityId, @ItemIsEWType, @EWCategoryId, @ItemEWPriceFrom, @ItemEWPriceTo, @ItemComprehensiveMonths, @ItemNonComprehensiveMonths, @EWVendorId, @ItemMaxMonths, @ItemIsEWOwn, @ItemIsPrepaid, @ItemNoOfPeriods, @DeferalPlanId, @SizeSetId, @MeasureTypeId, @SkuSettingId, @SupplyGroupId, @RevisedFromItemId, @ItemRevisionNumber, @ItemThumbNail, @ItemDrawingRevisionNumber, @ShapeId, @MaterialId, @ItemD1, @ItemD2, @ItemD3, @ItemD4, @ItemD5, @ItemDisplayD1, @ItemDisplayD2, @ItemDisplayD3, @ItemDisplayD4, @ItemDisplayD5, @D1DisplayUnitId, @D2DisplayUnitId, @D3DisplayUnitId, @D4DisplayUnitId, @D5DisplayUnitId, @ItemStandardLengthBase, @ItemWeightPerMeter, @ItemVolumePerMeter );"; public const string UPDATE_ITEM = @"UPDATE MITEM SET ITEMID = @ItemId, ITEMCODE = @ItemCode, ITEMNAME = @ItemName, ITEMSHORTNAME = @ItemShortName, ITEMDESCRIPTION = @ItemDescription, CODECATEGORYID = @CodeCategoryId, CATEGORYID = @ItemCategoryId, SUBCATEGORYID = @SubCategoryId, ITEMGROUPID = @ItemGroupId, ITEMBRANDID = @ItemBrandId, MODELID = @ModelId, PRICECATEGORYID = @PriceCategoryId, MATERIALGROUPID = @MaterialGroupId, PROCESSGROUPID = @ProcessGroupId, MATERIALACCOUNTID = @MaterialAccountId, ISSALEABLEITEM = @ItemIsSaleableItem, ISACTIVITY = @ItemIsActivity, INTRODUCEDDATE = @ItemIntroducedDate, ITEMORTEMPLATE = @ItemItemOrTemplate, DRAWINGNUMBER = @ItemDrawingNumber, CATALOGUENUMBER = @ItemCatalogueNumber, PARAMETERSETID = @ParameterSetId, ISITEMREVISED = @ItemIsItemRevised, ITEMREVISEDFROM = @ItemItemRevisedFrom, ITEMREVISEDTO = @ItemItemRevisedTo, ISTRANSACTABLE = @ItemIsTransactable, ISPURCHASABLE = @ItemIsPurchasable, ISINTERNALORDER = @ItemIsInternalOrder, ISBUYBACK = @ItemIsBuyBack, ISVARIABLESIZEITEM = @ItemIsVariablesizeItem, ISSPARE = @ItemIsSpare, ISBUYWITHOUTORDER = @ItemIsBuyWithoutOrder, BOMTYPE = @ItemBOMType, ISSTOCKPOSTING = @ItemIsStockPosting, ISBATCHSTOCK = @ItemIsBatchStock, ISUNIQUEBATCH = @ItemIsUniqueBatch, STOCKVALUATIONTYPE = @ItemStockValuationType, TOLLERANCETYPE = @ItemTolleranceType, TOLLERANCEVALUE = @ItemTolleranceValue, ISSKUTRACKINGAPPLICABLE = @ItemIsSKUTrackingapplicable, DEFAULTSKUID = @DefaultSkuId, ISSTYLEAPPLICABLE = @ItemIsStyleApplicable, ISCOLORAPPLICABLE = @ItemIsColorApplicable, ISSIZEAPPLICABLE = @ItemIsSizeApplicable, ISPROCESSAPPLICABLE = @ItemIsProcessApplicable, ISMAKEAPPLICABLE = @ItemIsMakeApplicable, ISPRICEPOINTAPPLICABLE = @ItemIsPricePointApplicable, ISGRADEAPPLICABLE = @ItemIsGradeApplicable, ISSEASONAPPLICABLE = @ItemIsSeasonApplicable, PURCHASEUOMID = @PurchaseUOMId, STOCKUOMID = @StockUOMId, CONVERSIONFACTOR = @ItemConversionFactor, UOMSETID = @UOMSetId, ISPACKINGSDETAILSREQUIRED = @ItemIsPackingsDetailsRequired, PACKSETID = @PackSetId, DEFAULTPACKID = @DefaultPackId, CASECOUNT = @ItemCaseCount, INSPECTIONTYPE = @ItemInspectionType, QUALITYPARAMETERSETID = @QualityParameterSetId, ISEXPIRYTRACKINGREQUIRED = @ItemIsExpirytrackingRequired, LIFEINDAYS = @ItemLifeinDays, RATETYPE = @ItemRateType, PRICEFORTYPE = @ItemPriceForType, LINKITEMID = @LinkItemId, LINKVARIATION = @ItemLinkVariation, PRICINGREFERENCENUMBER = @ItemPricingReferenceNumber, ISDISCOUNTAPPLICABLE = @ItemIsDiscountApplicable, MAXIMUMDISCOUNTPERCENTAGE = @ItemMaximumDiscountPercentage, ISFREEAPPLICABLE = @ItemIsFreeApplicable, MRPRATE = @ItemMRPRate, ISSERVICEAPPLICABLE = @ItemIsServiceApplicable, ISWARRANTYAPPLICABLE = @ItemIsWarrantyApplicable, WARRANTYPERIOD = @ItemWarrantyPeriod, ISPOSTTOCUSTOMERPRODUCT = @ItemIsPostToCustomerProduct, ISINSTALLATIONTRACKINGREQUIRED = @ItemIsInstallationTrackingRequired, ISDEFECTIVETOBERETURNED = @ItemIsDefectiveToBeReturned, HEIGHT = @ItemHeight, WEIGHT = @ItemWeight, LENGTH = @ItemLength, BREATH = @ItemBreath, THICKNESS = @ItemThickness, ISDUTYAPPLICABLE = @ItemIsDutyApplicable, ISCENVATAPPLICABLE = @ItemIsCENVATApplicable, DUTYTABLEID = @DutyTableId, TAXTABLEID = @TaxTableId, ISTAXTYPECHANGEABLE = @ItemIsTaxTypeChangeable, VATCLAIMTYPE = @ItemVATClaimType, MASTERPLANMETHOD = @ItemMasterPlanMethod, MRPPLANMETHOD = @ItemMRPPlanMethod, LEADTIME = @ItemLeadTime, REVIEWPERIOD = @ItemReviewPeriod, MINIMUMORDERQUANTITY = @ItemMinimumOrderQuantity, MINLEVEL = @ItemMinLevel, MAXLEVEL = @ItemMaxLevel, LOTSIZE = @ItemLotSize, ROUTINGID = @RoutingId, ISEXCISEAPPLICABLE = @ItemIsExciseApplicable, PURCHASERATE = @ItemPurchaseRate, PURCHASERATEINCLUSIVE = @ItemPurchaseRateInclusive, PURCHASECOSTNLC = @ItemPurchaseCostNLC, SALESRATE = @ItemSalesRate, SALESRATEINCLUSIVE = @ItemSalesRateInclusive, PLANNERID = @PlannerId, PROCUREMENTINCHARGEID = @ProcurmentInchargeId, PRODUCTIONNATURE = @ItemProductionNature, ISMAKEORBUY = @ItemIsMakeOrBuy, ISMANUFACTURABLE = @ItemIsManufacturable, ISBULKISSUEITEM = @ItemIsBulkIssueItem, SHRINKAGEPERCENTAGE = @ItemShrinkagePercentage, COMPONENTYIELD = @ItemComponentYield, PREPROCESSINGLEADTIME = @PreProcessingLeadTime, PROCESSINGLEADTIME = @ItemPreProcessingLeadTime, POSTPROCESSINGLEADTIME = @ItemPostProcessingLeadTime, LEADTIMELOTSIZE = @ItemLeadTimeLotSize, FIXEDLEADTIME = @ItemFixedLeadTime, VARIABLELEADTIME = @ItemVariableLeadTime, VARIABLEQUANTITY = @ItemVariableQuantity, MANUFACTURINGLEADTIME = @ItemManufacturingLeadTime, LEADTIMEOFFSET = @ItemLeadTimeOffSet, BOMYIELDPERCENTAGE = @ItemBOMYieldPercentage, SAFETYSTOCKMETHOD = @ItemSafetyStockMethod, SAFETYDAYS = @ItemSafetyDays, MRPPLANNEDPERCENTAGE = @ItemMRPPlannedPercentage, LOTMETHOD = @ItemLotMethod, INVENTORYMETHOD = @ItemInventoryMethod, LOWESTLEVELINBOM = @ItemLowestLevelInBOM, ISBINTRACKINGREQUIRED = @ItemIsBinTrackingRequired, DUTYTARIFFID = @DutyTariffId, SORTORDER = @ItemSortOrder, VERSION = @ItemVersion, STATUS = @ItemStatus, MODIFIEDBYID = @ItemModifiedById, MODIFIEDON = @ItemModifiedOn, DEFAULTCMSID = @ItemDefaultCmsId, VATCOMMODITYID = @VatCommodityId, RAWMATERIALITEMID = @RawMaterialItemId, RAWMATERIALSKUID = @RawMaterialSKUId, RAWMATERIALQUANTITY = @RawMaterialQuantity, NESTINGPLANID = @NestingPlanId, SOURCETYPE = @ItemSourceType, PURCHASECURRENCYID = @PurchaseCurrencyId, LASTPURCHASECURRENCYID = @LastPurchaseCurrencyId, LASTPURCHASERATE = @ItemLastPurchaseRate, ITEMTYPEID = @ItemTypeId, DUTYFORMTYPEID = @DutyFormTypeId, PLANNINGGROUPID = @PlanningGroupId, ISASSET = @ItemIsAsset, ASSETTYPEID = @AssetTypeId, ASSETLINKTYPE = @ItemAssetLinkType, ASSETBATCHLINKTYPE = @ItemAssetBatchLinkType, ASSETID = @AssetId, GSTTYPE = @ItemGSTType, GSTTARIFFID = @GSTTariffId, ITCTYPE = @ItemITCType, GSTTABLEID = @GSTTableId, TENANTID = @TenantId, SIZESETDETAILID = @SizeSetDetailId, COLORID = @ColorId, BOMTOLLERANCEVALUE = @ItemBOMTolleranceValue, CALLSETID = @CallSetId, SAMPLESPERCENTAGE = @ItemSamplesPercentage, QUALITYPLANREFERENCENO = @ItemQualityPlanReferenceNo, DEFAULTBOMID = @DefaultBOMId, CYCLECLASSID = @CycleClassId, RESERVATIONTYPE = @ItemReservationType, ACTIVITYID = @ActivityId, ISEWTYPE = @ItemIsEWType, EWCATEGORYID = @EWCategoryId, EWPRICEFROM = @ItemEWPriceFrom, EWPRICETO = @ItemEWPriceTo, COMPREHENSIVEMONTHS = @ItemComprehensiveMonths, NONCOMPREHENSIVEMONTHS = @ItemNonComprehensiveMonths, EWVENDORID = @EWVendorId, MAXMONTHS = @ItemMaxMonths, ISEWOWN = @ItemIsEWOwn, ISPREPAID = @ItemIsPrepaid, NOOFPERIODS = @ItemNoOfPeriods, DEFERALPLANID = @DeferalPlanId, SIZESETID = @SizeSetId, MEASURETYPEID = @MeasureTypeId, SKUSETTINGID = @SKUSettingId, SUPPLYGROUPID = @SupplyGroupId, REVISEDFROMITEMID = @RevisedFromItemId, REVISIONNUMBER = @ItemRevisionNumber, THUMBNAIL = @ItemThumbNail, DRAWINGREVISIONNUMBER = @ItemDrawingRevisionNumber, SHAPEID = @ShapeId, MATERIALID = @MaterialId, D1 = @ItemD1, D2 = @ItemD2, D3 = @ItemD3, D4 = @ItemD4, D5 = @ItemD5, DISPLAYD1 = @ItemDisplayD1, DISPLAYD2 = @ItemDisplayD2, DISPLAYD3 = @ItemDisplayD3, DISPLAYD4 = @ItemDisplayD4, DISPLAYD5 = @ItemDisplayD5, D1DISPLAYUNITID = @D1DisplayUnitId, D2DISPLAYUNITID = @D2DisplayUnitId, D3DISPLAYUNITID = @D3DisplayUnitId, D4DISPLAYUNITID = @D4DisplayUnitId, D5DISPLAYUNITID = @D5DisplayUnitId, STANDARDLENGTHBASE = @ItemStandardLengthBase, WEIGHTPERMETER = @ItemWeightPerMeter, VOLUMEPERMETER = @ItemVolumePerMeter WHERE ITEMID = @ItemId;"; public const string GET_ITEM = @"SELECT MI.ITEMID AS ItemId, MI.ITEMCODE AS ItemCode, MI.ITEMNAME AS ItemName, MI.ITEMSHORTNAME AS ItemShortName, MI.ITEMDESCRIPTION AS ItemDescription, MI.CODECATEGORYID AS CodeCategoryId, CD.CATEGORYCODE AS CodeCategoryCategoryCode, CD.CATEGORYNAME AS CodeCategoryCategoryName, CD.GENERATIONTYPE AS CodeDefineGenerationType, MI.CATEGORYID AS ItemCategoryId, CAT.ITEMCATEGORYCODE AS ItemCategoryCode, CAT.ITEMCATEGORYNAME AS ItemCategoryName, MI.SUBCATEGORYID AS SubCategoryId, SC.ITEMSUBCATEGORYCODE AS SubCategoryCode, SC.ITEMSUBCATEGORYNAME AS SubCategoryName, MI.ITEMGROUPID AS ItemGroupId, IG.ITEMGROUPCODE AS ItemGroupCode, IG.ITEMGROUPNAME AS ItemGroupName, MI.ITEMBRANDID AS ItemBrandId, IB.ITEMBRANDCODE AS ItemBrandCode, IB.ITEMBRANDNAME AS ItemBrandName, MI.MODELID AS ModelId, MD.MODELCODE AS ModelCode, MD.MODELNAME AS ModelName, MI.PRICECATEGORYID AS PriceCategoryId, PCAT.PRICECATEGORYCODE AS PriceCategoryCode, PCAT.PRICECATEGORYNAME AS PriceCategoryName, MI.MATERIALGROUPID AS MaterialGroupId, MG.MATERIALGROUPCODE AS MaterialGroupCode, MG.MATERIALGROUPNAME AS MaterialGroupName, MI.PROCESSGROUPID AS ProcessGroupId, PGP.PROCESSGROUPCODE AS ProcessGroupCode, PGP.PROCESSGROUPNAME AS ProcessGroupName, MI.MATERIALACCOUNTID AS MaterialAccountId, MA.MATERIALACCOUNTCODE AS MaterialAccountCode, MA.MATERIALACCOUNTNAME AS MaterialAccountName, MI.ISSALEABLEITEM AS ItemIsSaleableItem, MI.ISACTIVITY AS ItemIsActivity, MI.INTRODUCEDDATE AS ItemIntroducedDate, MI.ITEMORTEMPLATE AS ItemItemOrTemplate, MI.DRAWINGNUMBER AS ItemDrawingNumber, MI.CATALOGUENUMBER AS ItemCatalogueNumber, MI.PARAMETERSETID AS ParameterSetId, PS.PARAMETERSETCODE AS ParameterSetCode, PS.PARAMETERSETNAME AS ParameterSetName, MI.ISITEMREVISED AS ItemIsItemRevised, MI.ITEMREVISEDFROM AS ItemItemRevisedFrom, MI.ITEMREVISEDTO AS ItemItemRevisedTo, MI.ISTRANSACTABLE AS ItemIsTransactable, MI.ISPURCHASABLE AS ItemIsPurchasable, MI.ISINTERNALORDER AS ItemIsInternalOrder, MI.ISBUYBACK AS ItemIsBuyBack, MI.ISVARIABLESIZEITEM AS ItemIsVariablesizeItem, MI.ISSPARE AS ItemIsSpare, MI.ISBUYWITHOUTORDER AS ItemIsBuyWithoutOrder, MI.BOMTYPE AS ItemBOMType, MI.ISSTOCKPOSTING AS ItemIsStockPosting, MI.ISBATCHSTOCK AS ItemIsBatchStock, MI.ISUNIQUEBATCH AS ItemIsUniqueBatch, MI.STOCKVALUATIONTYPE AS ItemStockValuationType, MI.TOLLERANCETYPE AS ItemTolleranceType, MI.TOLLERANCEVALUE AS ItemTolleranceValue, MI.ISSKUTRACKINGAPPLICABLE AS ItemIsSKUTrackingapplicable, MI.DEFAULTSKUID AS DefaultSKUId, SKU.SKUCODE AS DefaultSKUCode, SKU.SKUNAME AS DefaultSKUName, MI.ISSTYLEAPPLICABLE AS ItemIsStyleApplicable, MI.ISCOLORAPPLICABLE AS ItemIsColorApplicable, MI.ISSIZEAPPLICABLE AS ItemIsSizeApplicable, MI.ISPROCESSAPPLICABLE AS ItemIsProcessApplicable, MI.ISMAKEAPPLICABLE AS ItemIsMakeApplicable, MI.ISPRICEPOINTAPPLICABLE AS ItemIsPricePointApplicable, MI.ISGRADEAPPLICABLE AS ItemIsGradeApplicable, MI.ISSEASONAPPLICABLE AS ItemIsSeasonApplicable, MI.PURCHASEUOMID AS PurchaseUOMId, PU.UOMCODE AS PurchaseUOMCode, PU.UOMNAME AS PurchaseUOMName, MI.STOCKUOMID AS StockUOMId, SU.UOMCODE AS StockUOMCode, SU.UOMNAME AS StockUOMName, MI.CONVERSIONFACTOR AS ItemConversionFactor, MI.UOMSETID AS UOMSetId, US.UOMSETCODE AS UOMSetCode, US.UOMSETNAME AS UOMSetName, MI.ISPACKINGSDETAILSREQUIRED AS ItemIsPackingsDetailsRequired, MI.PACKSETID AS PackSetId, PKS.PACKSETCODE AS PackSetCode, PKS.PACKSETNAME AS PackSetName, MI.DEFAULTPACKID AS DefaultPackId, PK.PACKCODE AS DefaultPackCode, PK.PACKNAME AS DefaultPackName, MI.CASECOUNT AS ItemCaseCount, MI.INSPECTIONTYPE AS ItemInspectionType, MI.QUALITYPARAMETERSETID AS QualityParameterSetId, QPS.PARAMETERSETCODE AS QualityParameterSetCode, QPS.PARAMETERSETNAME AS QualityParameterSetName, MI.ISEXPIRYTRACKINGREQUIRED AS ItemIsExpirytrackingRequired, MI.LIFEINDAYS AS ItemLifeinDays, MI.RATETYPE AS ItemRateType, MI.PRICEFORTYPE AS ItemPriceForType, MI.LINKITEMID AS LinkItemId, LI.ITEMCODE AS LinkItemCode, LI.ITEMNAME AS LinkItemName, MI.LINKVARIATION AS ItemLinkVariation, MI.PRICINGREFERENCENUMBER AS ItemPricingReferenceNumber, MI.ISDISCOUNTAPPLICABLE AS ItemIsDiscountApplicable, MI.MAXIMUMDISCOUNTPERCENTAGE AS ItemMaximumDiscountPercentage, MI.ISFREEAPPLICABLE AS ItemIsFreeApplicable, MI.MRPRATE AS ItemMRPRate, MI.ISSERVICEAPPLICABLE AS ItemIsServiceApplicable, MI.ISWARRANTYAPPLICABLE AS ItemIsWarrantyApplicable, MI.WARRANTYPERIOD AS ItemWarrantyPeriod, MI.ISPOSTTOCUSTOMERPRODUCT AS ItemIsPostToCustomerProduct, MI.ISINSTALLATIONTRACKINGREQUIRED AS ItemIsInstallationTrackingRequired, MI.ISDEFECTIVETOBERETURNED AS ItemIsDefectiveToBeReturned, MI.HEIGHT AS ItemHeight, MI.WEIGHT AS ItemWeight, MI.LENGTH AS ItemLength, MI.BREATH AS ItemBreath, MI.THICKNESS AS ItemThickness, MI.ISDUTYAPPLICABLE AS ItemIsDutyApplicable, MI.ISCENVATAPPLICABLE AS ItemIsCENVATApplicable, MI.DUTYTABLEID AS DutyTableId, DT.DUTYTABLECODE AS DutyTableCode, DT.DUTYTABLENAME AS DutyTableName, MI.TAXTABLEID AS TaxTableId, TT.TAXTABLECODE AS TaxTableCode, TT.TAXTABLENAME AS TaxTableName, MI.ISTAXTYPECHANGEABLE AS ItemIsTaxTypeChangeable, MI.VATCLAIMTYPE AS ItemVATClaimType, MI.MASTERPLANMETHOD AS ItemMasterPlanMethod, MI.MRPPLANMETHOD AS ItemMRPPlanMethod, MI.LEADTIME AS ItemLeadTime, MI.REVIEWPERIOD AS ItemReviewPeriod, MI.MINIMUMORDERQUANTITY AS ItemMinimumOrderQuantity, MI.MINLEVEL AS ItemMinLevel, MI.MAXLEVEL AS ItemMaxLevel, MI.LOTSIZE AS ItemLotSize, MI.ROUTINGID AS RoutingId, RT.ROUTINGCODE AS RoutingCode, RT.ROUTINGNAME AS RoutingName, MI.PURCHASERATE AS ItemPurchaseRate, MI.PURCHASERATEINCLUSIVE AS ItemPurchaseRateInclusive, MI.PURCHASECOSTNLC AS ItemPurchaseCostNLC, MI.SALESRATE AS ItemSalesRate, MI.SALESRATEINCLUSIVE AS ItemSalesRateInclusive, MI.PLANNERID AS PlannerId, PL.EMPLOYEECODE AS PlannerCode, PL.EMPLOYEENAME AS PlannerName, MI.PROCUREMENTINCHARGEID AS ProcurmentInchargeId, PI.EMPLOYEECODE AS ProcurmentInchargeCode, PI.EMPLOYEENAME AS ProcurmentInchargeName, MI.PRODUCTIONNATURE AS ItemProductionNature, MI.ISMAKEORBUY AS ItemIsMakeOrBuy, MI.ISMANUFACTURABLE AS ItemIsManufacturable, MI.ISBULKISSUEITEM AS ItemIsBulkIssueItem, MI.SHRINKAGEPERCENTAGE AS ItemShrinkagePercentage, MI.COMPONENTYIELD AS ItemComponentYield, MI.PREPROCESSINGLEADTIME AS ItemPreProcessingLeadTime, MI.PROCESSINGLEADTIME AS ItemProcessingLeadTime, MI.POSTPROCESSINGLEADTIME AS ItemPostProcessingLeadTime, MI.LEADTIMELOTSIZE AS ItemLeadTimeLotSize, MI.FIXEDLEADTIME AS ItemFixedLeadTime, MI.VARIABLELEADTIME AS ItemVariableLeadTime, MI.VARIABLEQUANTITY AS ItemVariableQuantity, MI.MANUFACTURINGLEADTIME AS ItemManufacturingLeadTime, MI.LEADTIMEOFFSET AS ItemLeadTimeOffSet, MI.BOMYIELDPERCENTAGE AS ItemBOMYieldPercentage, MI.SAFETYSTOCKMETHOD AS ItemSafetyStockMethod, MI.SAFETYDAYS AS ItemSafetyDays, MI.MRPPLANNEDPERCENTAGE AS ItemMRPPlannedPercentage, MI.LOTMETHOD AS ItemLotMethod, MI.INVENTORYMETHOD AS ItemInventoryMethod, MI.LOWESTLEVELINBOM AS ItemLowestLevelInBOM, MI.ISBINTRACKINGREQUIRED AS ItemIsBinTrackingRequired, MI.DUTYTARIFFID AS DutyTariffId, MDU.DUTYTARIFFCODE AS DutyTariffCode, MDU.DUTYTARIFFNAME AS DutyTariffName, MI.SORTORDER AS ItemSortOrder, MI.VERSION AS ItemVersion, MI.STATUS AS ItemStatus, MI.CREATEDBYID AS ItemCreatedById, MI.CREATEDON AS ItemCreatedOn, MI.MODIFIEDBYID AS ItemModifiedById, MI.MODIFIEDON AS ItemModifiedOn, MI.DEFAULTCMSID AS ItemDefaultCmsId, MI.VATCOMMODITYID AS VatCommodityId, MGC.GCMCODE AS VatCommodityCode, MGC.GCMNAME AS VatCommodityName, MI.RAWMATERIALITEMID AS RawMaterialItemId, MRAW.ITEMCODE AS RawMaterialItemCode, MRAW.ITEMNAME AS RawMaterialItemName, MI.RAWMATERIALSKUID AS RawMaterialSKUId, MRSK.SKUCODE AS RawMaterialSKUCode, MRSK.SKUNAME AS RawMaterialSKUName, MI.RAWMATERIALQUANTITY AS RawMaterialQuantity, MI.NESTINGPLANID AS NestingPlanId, TNEST.NESTINGPLANCODE AS NestingPlanCode, MI.SOURCETYPE AS ItemSourceType, MI.PURCHASECURRENCYID AS PurchaseCurrencyId, PC.CURRENCYCODE AS PurchaseCurrencyCode, PC.CURRENCYNAME AS PurchaseCurrencyName, MI.LASTPURCHASECURRENCYID AS LastPurchaseCurrencyId, LPC.CURRENCYCODE AS LastPurchaseCurrencyCode, LPC.CURRENCYNAME AS LastPurchaseCurrencyName, MI.LASTPURCHASERATE AS ItemLastPurchaseRate, MI.ITEMTYPEID AS ItemTypeId, GM.GCMCODE AS ItemTypeCode, GM.GCMNAME AS ItemTypeName, MI.DUTYFORMTYPEID AS DutyFormTypeId, mduty.GCMCODE AS DutyFormTypeCode, mduty.GCMNAME AS DutyFormTypeName , MI.PLANNINGGROUPID AS PlanningGroupId, mplan.GCMCODE AS PlanningGroupCode, mplan.GCMNAME AS PlanningGroupName , MI.ISASSET AS ItemIsAsset, MI.ASSETTYPEID AS AssetTypeId, mssty.ASSETTYPECODE AS AssetTypeCode, mssty.ASSETTYPENAME AS AssetTypeName, MI.ASSETLINKTYPE AS ItemAssetLinkType, MI.ASSETBATCHLINKTYPE AS ItemAssetBatchLinkType, MI.ASSETID AS AssetId, masst.ASSETCODE AS AssetCode, masst.ASSETNAME AS AssetName, MI.GSTTYPE AS ItemGSTType, MI.GSTTARIFFID AS GSTTariffId, mtarr.DUTYTARIFFCODE AS GSTTariffCode, mtarr.DUTYTARIFFNAME AS GSTTariffName, MI.ITCTYPE AS ItemITCType, MI.GSTTABLEID AS GSTTableId, mtuy.DUTYTABLECODE AS GSTTableCode, mtuy.DUTYTABLENAME AS GSTTableName, MI.TENANTID AS TenantId, MI.SIZESETDETAILID AS SizeSetDetailId, MI.COLORID AS ColorId, MCOL.COLORCODE AS ColorCode, MCOL.COLORNAME AS ColorName, MI.BOMTOLLERANCEVALUE AS ItemBOMTolleranceValue, MI.CALLSETID AS CallSetId, MCSET.CALLSETCODE AS CallSetCode, MCSET.CALLSETNAME AS CallSetName, MI.SAMPLESPERCENTAGE AS ItemSamplesPercentage, MI.QUALITYPLANREFERENCENO AS ItemQualityPlanReferenceNo, MI.DEFAULTBOMID AS DefaultBOMId, MBO.BOMCODE AS DefaultBOMCode, MBO.BOMNAME AS DefaultBOMName, MI.CYCLECLASSID AS CycleClassId, CC.CYCLECLASS AS CycleClassCode, CC.DESCRIPTION AS CycleClassName, MI.RESERVATIONTYPE AS ItemReservationType, MI.ACTIVITYID AS ActivityId, MACT.ACTIVITYCODE AS ActivityCode, MACT.ACTIVITYNAME AS ActivityName, MI.ISEWTYPE AS ItemIsEWType, MI.EWCATEGORYID AS EWCategoryId, MITEC.ITEMCATEGORYCODE AS EWCategoryCode, MITEC.ITEMCATEGORYNAME AS EWCategoryName, MI.EWPRICEFROM AS ItemEWPriceFrom, MI.EWPRICETO AS ItemEWPriceTo, MI.COMPREHENSIVEMONTHS AS ItemComprehensiveMonths, MI.NONCOMPREHENSIVEMONTHS AS ItemNonComprehensiveMonths, MI.EWVENDORID AS EWVendorId, MPAR.PARTYCODE AS EWVendorCode, MPAR.PARTYNAME AS EWVendorName, MI.MAXMONTHS AS ItemMaxMonths, MI.ISEWOWN AS ItemIsEWOwn, MI.ISPREPAID AS ItemIsPrepaid, MI.NOOFPERIODS AS ItemNoOfPeriods, MI.DEFERALPLANID AS DeferalPlanId, MDE.DEFERALPLANCODE AS DeferalPlanCode, MDE.DEFERALPLANNAME AS DeferalPlanName, MI.SIZESETID AS SizeSetId, MSI.SIZESETCODE AS SizeSetCode, MSI.SIZESETNAME AS SizeSetName, MI.MEASURETYPEID AS MeasureTypeId, MME.MEASURETYPECODE AS MeasureTypeCode, MME.MEASURETYPENAME AS MeasureTypeName, MI.SKUSETTINGID AS SKUSettingId, MSK.SKUSETTINGCODE AS SKUSettingCode, MSK.SKUSETTINGNAME AS SKUSettingName, MI.SUPPLYGROUPID AS SupplyGroupId, MS.SUPPLYGROUPCODE AS SupplyGroupCode, MS.SUPPLYGROUPNAME AS SupplyGroupName, MI.REVISEDFROMITEMID AS RevisedFromItemId, RI.ITEMCODE AS RevisedFromItemCode, RI.ITEMNAME AS RevisedFromItemName, MI.REVISIONNUMBER AS ItemRevisionNumber, MI.THUMBNAIL AS ItemThumbNail, MI.DRAWINGREVISIONNUMBER AS ItemDrawingRevisionNumber, MI.SHAPEID AS ShapeId, SH.SHAPECODE AS ShapeCode, SH.SHAPENAME AS ShapeName, MI.MATERIALID AS MaterialId, MT.MATERIALCODE AS MaterialCode, MT.MATERIALNAME AS MaterialName, MI.D1 AS ItemD1, MI.D2 AS ItemD2, MI.D3 AS ItemD3, MI.D4 AS ItemD4, MI.D5 AS ItemD5, MI.DISPLAYD1 AS ItemDisplayD1, MI.DISPLAYD2 AS ItemDisplayD2, MI.DISPLAYD3 AS ItemDisplayD3, MI.DISPLAYD4 AS ItemDisplayD4, MI.DISPLAYD5 AS ItemDisplayD5, MI.D1DISPLAYUNITID AS D1DisplayUnitId, U1.UOMCODE AS D1DisplayUnitCode, U1.UOMNAME AS D1DisplayUnitName, MI.D2DISPLAYUNITID AS D2DisplayUnitId, U2.UOMCODE AS D2DisplayUnitCode, U2.UOMNAME AS D2DisplayUnitName, MI.D3DISPLAYUNITID AS D3DisplayUnitId, U3.UOMCODE AS D3DisplayUnitCode, U3.UOMNAME AS D3DisplayUnitName, MI.D4DISPLAYUNITID AS D4DisplayUnitId, U4.UOMCODE AS D4DisplayUnitCode, U4.UOMNAME AS D4DisplayUnitName, MI.D5DISPLAYUNITID AS D5DisplayUnitId, U5.UOMCODE AS D5DisplayUnitCode, U5.UOMNAME AS D5DisplayUnitName, MI.STANDARDLENGTHBASE AS ItemStandardLengthBase, MI.WEIGHTPERMETER AS ItemWeightPerMeter, MI.VOLUMEPERMETER AS ItemVolumePerMeter FROM MITEM MI LEFT JOIN MCURRENCY PC ON PC.CURRENCYID = MI.PURCHASECURRENCYID LEFT JOIN MCURRENCY LPC ON LPC.CURRENCYID = MI.LASTPURCHASECURRENCYID LEFT JOIN MCODEDEFINE CD ON CD.CODEDEFINEID = MI.CODECATEGORYID LEFT JOIN MITEMCATEGORY CAT ON CAT.ITEMCATEGORYID = MI.CATEGORYID LEFT JOIN MITEMSUBCATEGORY SC ON SC.ITEMSUBCATEGORYID = MI.SUBCATEGORYID LEFT JOIN MITEMGROUP IG ON IG.ITEMGROUPID = MI.ITEMGROUPID LEFT JOIN MITEMBRAND IB ON IB.ITEMBRANDID = MI.ITEMBRANDID LEFT JOIN MMODEL MD ON MD.MODELID = MI.MODELID LEFT JOIN TNESTINGPLAN TNEST ON TNEST.NESTINGPLANID = MI.NESTINGPLANID LEFT JOIN MPARTY MPAR ON MPAR.PARTYID = MI.EWVENDORID LEFT JOIN MACTIVITY MACT ON MACT.ACTIVITYID = MI.ACTIVITYID LEFT JOIN MBOM MBO ON MBO.BOMID = MI.DEFAULTBOMID LEFT JOIN MCALLSET MCSET ON MCSET.CALLSETID = MI.CALLSETID LEFT JOIN MCOLOR MCOL ON MCOL.COLORID = MI.COLORID LEFT JOIN MGCM GM ON GM.GCMID = MI.ITEMTYPEID LEFT JOIN MGCM mduty ON mduty.GCMID = MI.DUTYFORMTYPEID LEFT JOIN MGCM mplan ON mplan.GCMID = MI.PLANNINGGROUPID LEFT JOIN MASSETTYPE mssty ON mssty.ASSETTYPEID = MI.ASSETTYPEID LEFT JOIN MASSET masst ON masst.ASSETID = MI.ASSETID LEFT JOIN MDUTYTARIFF mtarr ON mtarr.DUTYTARIFFID = MI.GSTTARIFFID LEFT JOIN MDUTYTABLE mtuy ON mtuy.DUTYTABLEID = MI.GSTTABLEID LEFT JOIN MITEM RI ON RI.ITEMID = MI.REVISEDFROMITEMID LEFT JOIN MSUPPLYGROUP MS ON MS.SUPPLYGROUPID = MI.SUPPLYGROUPID LEFT JOIN MSKUSETTING MSK ON MSK.SKUSETTINGID = MI.SKUSETTINGID LEFT JOIN MDUTYTARIFF MDU ON MDU.DUTYTARIFFID = MI.DUTYTARIFFID LEFT JOIN MMEASURETYPE MME ON MME.MEASURETYPEID = MI.MEASURETYPEID LEFT JOIN MSIZESET MSI ON MSI.SIZESETID = MI.SIZESETID LEFT JOIN MGCM MGC ON MGC.GCMID = MI.VATCOMMODITYID LEFT JOIN MITEM MRAW ON MRAW.ITEMID = MI.RAWMATERIALITEMID LEFT JOIN MSKU MRSK ON MRSK.SKUID = MI.RAWMATERIALSKUID LEFT JOIN MDEFERALPLAN MDE ON MDE.DEFERALPLANID = MI.DEFERALPLANID LEFT JOIN MITEMCATEGORY MITEC ON MITEC.ITEMCATEGORYID = MI.CATEGORYID LEFT JOIN MPRICECATEGORY PCAT ON PCAT.PRICECATEGORYID = MI.PRICECATEGORYID LEFT JOIN MMATERIALGROUP MG ON MG.MATERIALGROUPID = MI.MATERIALGROUPID LEFT JOIN MPROCESSGROUP PGP ON PGP.PROCESSGROUPID = MI.PROCESSGROUPID LEFT JOIN MMATERIALACCOUNT MA ON MA.MATERIALACCOUNTID = MI.MATERIALACCOUNTID LEFT JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = MI.PARAMETERSETID LEFT JOIN MSKU SKU ON SKU.SKUID = MI.DEFAULTSKUID LEFT JOIN MUOM PU ON PU.UOMID = MI.PURCHASEUOMID LEFT JOIN MUOM SU ON SU.UOMID = MI.STOCKUOMID LEFT JOIN MUOMSET US ON US.UOMSETID = MI.UOMSETID LEFT JOIN MPACKSET PKS ON PKS.PACKSETID = MI.PACKSETID LEFT JOIN MPACK PK ON PK.PACKID = MI.DEFAULTPACKID LEFT JOIN MPARAMETERSET QPS ON QPS.PARAMETERSETID = MI.QUALITYPARAMETERSETID LEFT JOIN MITEM LI ON LI.ITEMID = MI.LINKITEMID LEFT JOIN MTAXTABLE TT ON TT.TAXTABLEID = MI.TAXTABLEID LEFT JOIN MDUTYTABLE DT ON DT.DUTYTABLEID = MI.DUTYTABLEID LEFT JOIN MROUTING RT ON RT.ROUTINGID = MI.ROUTINGID LEFT JOIN MEMPLOYEE PL ON PL.EMPLOYEEID = MI.PLANNERID LEFT JOIN MEMPLOYEE PI ON PI.EMPLOYEEID = MI.PROCUREMENTINCHARGEID LEFT JOIN MCYCLECLASS CC ON CC.CYCLECLASSID = MI.CYCLECLASSID LEFT JOIN MSHAPE SH ON SH.SHAPEID = MI.SHAPEID LEFT JOIN MMATERIAL MT ON MT.MATERIALID = MI.MATERIALID LEFT JOIN MUOM U1 ON U1.UOMID = MI.D1DISPLAYUNITID LEFT JOIN MUOM U2 ON U2.UOMID = MI.D2DISPLAYUNITID LEFT JOIN MUOM U3 ON U3.UOMID = MI.D3DISPLAYUNITID LEFT JOIN MUOM U4 ON U4.UOMID = MI.D4DISPLAYUNITID LEFT JOIN MUOM U5 ON U5.UOMID = MI.D5DISPLAYUNITID WHERE 1 = 1 and MI.ITEMID = @ItemId;"; // ── GetItemDetails (GB4 migration: /mms/Item.svc/ItemDetails) ───────────── public const string GET_ITEM_DETAILS = @" SELECT MI.ITEMID AS ItemId, MI.ITEMCODE AS ItemCode, MI.ITEMNAME AS ItemName, MI.ITEMDESCRIPTION AS ItemDescription, MI.ISSTOCKPOSTING AS IsStockPosting, MI.ISBATCHSTOCK AS ItemIsBatchStock, MI.ISUNIQUEBATCH AS ItemIsUniqueBatch, MI.ISPACKINGSDETAILSREQUIRED AS ItemIsPackingsDetailsRequired, MI.ISFREEAPPLICABLE AS ItemIsFreeApplicable, MI.ISSKUTRACKINGAPPLICABLE AS ItemIsSKUApplicable, MI.ISEXPIRYTRACKINGREQUIRED AS ItemIsExpirytrackingRequired, MI.TOLLERANCETYPE AS ItemTolleranceType, MI.TOLLERANCEVALUE AS ItemTolleranceValue, MI.PURCHASERATE AS ItemRate, MI.PURCHASECOSTNLC AS ItemPurchaseCost, MI.RATETYPE AS ItemRateType, MI.LEADTIME AS ItemLeadTime, MI.WEIGHT AS ItemWeight, MI.MAXIMUMDISCOUNTPERCENTAGE AS MaxDiscPercentage, MI.QUALITYPARAMETERSETID AS QualityParameterSetId, MI.DEFAULTCMSID AS ItemDefaultCmsId, MI.TAXTABLEID AS ItemTaxTableId, MI.DUTYTABLEID AS ItemDutyTableId, MI.GSTTABLEID AS GSTTableId, MI.GSTTARIFFID AS GSTTariffId, -- UOM (purchase / transaction UOM) MI.PURCHASEUOMID AS UOMId, PU.UOMCODE AS UOMCode, PU.UOMNAME AS UOMName, PU.NOOFDECIMALS AS UOMNoOfDecimals, -- Rate-per-UOM (same as purchase UOM in GB4) MI.PURCHASEUOMID AS RatePerUOMId, PU.UOMCODE AS RatePerUOMCode, PU.UOMNAME AS RatePerUOMName, -- Stock UOM MI.STOCKUOMID AS StockUOMId, SU.UOMCODE AS StockUOMCode, SU.UOMNAME AS StockUOMName, SU.NOOFDECIMALS AS StockUOMNoOfDecimals, MI.UOMSETID AS ItemUOMSetId, -- Default SKU MI.DEFAULTSKUID AS ItemDefaultSKUId, SKU.SKUNAME AS ItemDefaultSKUName, -- Default Pack MI.DEFAULTPACKID AS ItemPackId, PK.PACKNAME AS ItemPackName, PK.CONVERSIONTYPE AS ItemPackConversiontype, PK.CONVERSIONFACTOR AS ItemPackConversionFactor, -- Material Account MI.MATERIALACCOUNTID AS MaterialAccountId, MA.MATERIALACCOUNTCODE AS MaterialAccountCode, MA.MATERIALACCOUNTNAME AS MaterialAccountName, -- Purchase Currency MI.PURCHASECURRENCYID AS CurrencyId, CUR.CURRENCYCODE AS CurrencyCode, CUR.CURRENCYNAME AS CurrencyName, -- Price Category MI.PRICECATEGORYID AS ItemPriceCategoryId, PC.PRICECATEGORYNAME AS ItemPriceCategoryName, -- Stock (from TSTOCKPOSITION, filtered by context params) ISNULL(SP.QUANTITY, 0) AS ItemStock, -- Store context (resolved from @storeid param) ISNULL(S.STOREID, -1) AS StoreId, ISNULL(S.STORECODE, '') AS StoreCode, ISNULL(S.STORENAME, '') AS StoreName, -- SKU context (resolved from @skuid param) ISNULL(RSKU.SKUID, -1) AS StockSkuId, ISNULL(RSKU.SKUCODE, '') AS StockSkuCode, ISNULL(RSKU.SKUNAME, '') AS StockSkuName, -- OU context (resolved from @ouid param) ISNULL(OU.OUID, -1) AS StockOuId, ISNULL(OU.ORGANIZATIONUNITCODE, '') AS StockOuCode, ISNULL(OU.ORGANIZATIONUNITNAME, '') AS StockOuName FROM MITEM MI LEFT JOIN MUOM PU ON PU.UOMID = MI.PURCHASEUOMID LEFT JOIN MUOM SU ON SU.UOMID = MI.STOCKUOMID LEFT JOIN MSKU SKU ON SKU.SKUID = MI.DEFAULTSKUID LEFT JOIN MPACK PK ON PK.PACKID = MI.DEFAULTPACKID LEFT JOIN MMATERIALACCOUNT MA ON MA.MATERIALACCOUNTID = MI.MATERIALACCOUNTID LEFT JOIN MCURRENCY CUR ON CUR.CURRENCYID = MI.PURCHASECURRENCYID LEFT JOIN MPRICECATEGORY PC ON PC.PRICECATEGORYID = MI.PRICECATEGORYID -- Stock position filtered by context params LEFT JOIN TSTOCKPOSITION SP ON SP.ITEMID = MI.ITEMID AND SP.OUID = @ouid AND SP.STOREID = @storeid AND SP.SKUID = ISNULL(NULLIF(@skuid, -1), MI.DEFAULTSKUID) -- Store / SKU / OU for display names LEFT JOIN MSTORE S ON S.STOREID = @storeid LEFT JOIN MSKU RSKU ON RSKU.SKUID = NULLIF(@skuid, -1) LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = @ouid WHERE MI.ITEMID = @itemid;"; // ── GetItemDetails via CriteriaDTO (GB4 migration: ItemDAL.GetNewItemDetailsFromTransaction) ── // Full transaction-context item lookup: rate (item-party contract), tax/duty/GST table ids, // account resolution by transaction Type, item-other descriptive fields, BizDimension L1-L5, // packing-pending qty. Item-party rate resolution uses GB5's own established pattern // (MITEMPARTY/MITEMPARTYDETAIL + BizTransactionClass.ModuleId — see MMDocumentQB.GET_MM_DOCUMENT_ITEM_PARTY) // instead of GB4's VITEMPARTY view + numeric-Type CASE. public const string GET_ITEM_DETAILS_FOR_TRANSACTION = @" SELECT MI.ITEMID AS ItemId, MI.ITEMCODE AS ItemCode, MI.ITEMNAME AS ItemName, MI.ITEMDESCRIPTION AS ItemDescription, MI.ISSTOCKPOSTING AS IsStockPosting, ISNULL(IOU.ISBATCHSTOCK, MI.ISBATCHSTOCK) AS ItemIsBatchStock, ISNULL(IOU.ISUNIQUEBATCH, MI.ISUNIQUEBATCH) AS ItemIsUniqueBatch, ISNULL(IOU.ISPACKINGSDETAILSREQUIRED, MI.ISPACKINGSDETAILSREQUIRED) AS ItemIsPackingsDetailsRequired, MI.ISFREEAPPLICABLE AS ItemIsFreeApplicable, MI.ISSKUTRACKINGAPPLICABLE AS ItemIsSKUApplicable, MI.ISEXPIRYTRACKINGREQUIRED AS ItemIsExpirytrackingRequired, MI.TOLLERANCETYPE AS ItemTolleranceType, MI.TOLLERANCEVALUE AS ItemTolleranceValue, MI.PURCHASECOSTNLC AS ItemPurchaseCost, ISNULL(IOU.RATETYPE, MI.RATETYPE) AS ItemRateType, ISNULL(IOU.LEADTIME, MI.LEADTIME) AS ItemLeadTime, MI.WEIGHT AS ItemWeight, MI.MAXIMUMDISCOUNTPERCENTAGE AS MaxDiscPercentage, MI.QUALITYPARAMETERSETID AS QualityParameterSetId, MI.DEFAULTCMSID AS ItemDefaultCmsId, ISNULL(IOU.TAXTABLEID, MI.TAXTABLEID) AS ItemTaxTableId, ISNULL(IOU.DUTYTABLEID, MI.DUTYTABLEID) AS ItemDutyTableId, ISNULL(IOU.GSTTABLEID, MI.GSTTABLEID) AS GSTTableId, MI.GSTTARIFFID AS GSTTariffId, TT.FORMRECEIVED AS FormReceived, TT.FORMISSUED AS FormIssued, ISNULL(TT.ISFORMRECEIVED, 0) AS IsFormReceived, ISNULL(TT.ISFORMISSUED, 0) AS IsFormIssued, ISNULL(IOU.ISNONTRANSACTED, 0) AS IsNonTransacted, MI.PACKSETID AS PackSetId, MI.SIZESETID AS SizeSetId, MI.ISPREPAID AS IsPrepaid, MI.NOOFPERIODS AS NoOfPeriods, MI.DEFERALPLANID AS DeferalPlanId, MI.PURCHASEUOMID AS UOMId, PU.UOMCODE AS UOMCode, PU.UOMNAME AS UOMName, PU.NOOFDECIMALS AS UOMNoOfDecimals, MI.STOCKUOMID AS StockUOMId, SU.UOMCODE AS StockUOMCode, SU.UOMNAME AS StockUOMName, SU.NOOFDECIMALS AS StockUOMNoOfDecimals, MI.UOMSETID AS ItemUOMSetId, MI.DEFAULTSKUID AS ItemDefaultSKUId, SKU.SKUNAME AS ItemDefaultSKUName, SKU.NETWEIGHT AS SKUNetWeight, MI.DEFAULTPACKID AS ItemPackId, PK.PACKNAME AS ItemPackName, PK.CONVERSIONTYPE AS ItemPackConversiontype, PK.CONVERSIONFACTOR AS ItemPackConversionFactor, MI.MATERIALACCOUNTID AS MaterialAccountId, MA.MATERIALACCOUNTCODE AS MaterialAccountCode, MA.MATERIALACCOUNTNAME AS MaterialAccountName, MI.PURCHASECURRENCYID AS CurrencyId, CUR.CURRENCYCODE AS CurrencyCode, CUR.CURRENCYNAME AS CurrencyName, MI.PRICECATEGORYID AS ItemPriceCategoryId, PC.PRICECATEGORYNAME AS ItemPriceCategoryName, -- Item-other descriptive fields ISNULL(MIC.ITEMCATEGORYNAME, '') AS ItemCategoryName, ISNULL(MISC.ITEMSUBCATEGORYNAME, '') AS ItemSubCategoryName, ISNULL(MMOD.MODELNAME, '') AS ItemCount, ISNULL(MIB.ITEMBRANDNAME, '') AS ItemContent, ISNULL(MSS.SIZESETNAME, '') AS ItemConstruction, ISNULL(MI.DRAWINGNUMBER, '') AS ItemWeave, ISNULL(MI.BREATH, 0) AS ItemWidth, ISNULL(MCOL.COLORNAME, '') AS ItemColour, ISNULL(MI.THICKNESS, 0) AS ItemGSM, ISNULL(MIG.ITEMGROUPNAME, '') AS ItemFinish, -- Account resolution: MaterialAccountDetail row matching the transaction's numeric Type CASE WHEN TT.TYPE IN (1,6) THEN MAD.SALESACCOUNTID WHEN TT.TYPE IN (2,8) THEN MAD.PURCHASEACCOUNTID WHEN TT.TYPE IN (3,7) THEN MAD.SALESRETURNACCOUNTID WHEN TT.TYPE IN (4,9) THEN MAD.PURCHASERETURNACCOUNTID END AS ItemAccountId, CASE WHEN TT.TYPE IN (1,6) THEN SAC.ACCOUNTCODE WHEN TT.TYPE IN (2,8) THEN PAC.ACCOUNTCODE WHEN TT.TYPE IN (3,7) THEN SRAC.ACCOUNTCODE WHEN TT.TYPE IN (4,9) THEN PRAC.ACCOUNTCODE END AS ItemAccountCode, CASE WHEN TT.TYPE IN (1,6) THEN SAC.ACCOUNTNAME WHEN TT.TYPE IN (2,8) THEN PAC.ACCOUNTNAME WHEN TT.TYPE IN (3,7) THEN SRAC.ACCOUNTNAME WHEN TT.TYPE IN (4,9) THEN PRAC.ACCOUNTNAME END AS ItemAccountName, CASE WHEN TT.TYPE IN (1,6) THEN SAC.BILLALLOCATIONTYPE WHEN TT.TYPE IN (2,8) THEN PAC.BILLALLOCATIONTYPE WHEN TT.TYPE IN (3,7) THEN SRAC.BILLALLOCATIONTYPE WHEN TT.TYPE IN (4,9) THEN PRAC.BILLALLOCATIONTYPE END AS ItemAccountBillAllocationType, CASE WHEN TT.TYPE IN (1,6) THEN SAC.ACCOUNTGROUPID WHEN TT.TYPE IN (2,8) THEN PAC.ACCOUNTGROUPID WHEN TT.TYPE IN (3,7) THEN SRAC.ACCOUNTGROUPID WHEN TT.TYPE IN (4,9) THEN PRAC.ACCOUNTGROUPID END AS ItemAccountGroupId, CONSAC.ACCOUNTID AS ConsumptionItemAccountId, CONSAC.ACCOUNTNAME AS ConsumptionItemAccountName, -- Stock (TSTOCKPOSITION). GetItemDetails always calls with no specific document/ -- allocation context, so the reservation-for/reserved-others split (which FNRESERVATIONSTOCKPOSITION -- would compute from TRESERVATION) is always 0 here — open/available quantity both reduce -- to Quantity - ReservedQuantity, matching that function's own formula for this call pattern. -- FNRESERVATIONSTOCKPOSITION itself is currently broken on this DB (references TRESERVATION -- columns RESERVEDFROMTYPE/BALANCEQUANTITY that no longer exist on that table), so we compute -- these directly from TSTOCKPOSITION instead of calling it. ISNULL(SP.QUANTITY, 0) AS ItemStock, ISNULL(SP.REJECTEDQUANTITY, 0) AS ItemRejectedStock, ISNULL(SP.REWORKQUANTITY, 0) AS ItemReworkStock, ISNULL(SP.OTHERQUANTITY, 0) AS ItemOtherStock, ISNULL(SP.QUANTITY, 0) AS StockPositionTotalQuantity, ISNULL(SP.RESERVEDQUANTITY, 0) AS StockPositionReservedQuantity, CAST(0 AS NUMERIC(18,4)) AS StockPositionReservedFor, CAST(0 AS NUMERIC(18,4)) AS StockPositionReservedOthers, ISNULL(SP.QUANTITY, 0) - ISNULL(SP.RESERVEDQUANTITY, 0) AS StockPositionOpenQuantity, ISNULL(SP.QUANTITY, 0) - ISNULL(SP.RESERVEDQUANTITY, 0) AS StockPositionAvailableQuantity, ISNULL(S.STOREID, -1) AS StoreId, ISNULL(S.STORECODE, '') AS StoreCode, ISNULL(S.STORENAME, '') AS StoreName, ISNULL(RSKU.SKUID, -1) AS StockSkuId, ISNULL(RSKU.SKUCODE, '') AS StockSkuCode, ISNULL(RSKU.SKUNAME, '') AS StockSkuName, ISNULL(OU.OUID, -1) AS StockOuId, ISNULL(OU.ORGANIZATIONUNITCODE, '') AS StockOuCode, ISNULL(OU.ORGANIZATIONUNITNAME, '') AS StockOuName, ISNULL(PACKNUM.GOODQUANTITY, 0) AS PackingPendingQuantity, ISNULL(BDIM.L1Id, -1) AS L1Id, ISNULL(BDIM.L1Code, '') AS L1Code, ISNULL(BDIM.L1Name, '') AS L1Name, ISNULL(BDIM.L2Id, -1) AS L2Id, ISNULL(BDIM.L2Code, '') AS L2Code, ISNULL(BDIM.L2Name, '') AS L2Name, ISNULL(BDIM.L3Id, -1) AS L3Id, ISNULL(BDIM.L3Code, '') AS L3Code, ISNULL(BDIM.L3Name, '') AS L3Name, ISNULL(BDIM.L4Id, -1) AS L4Id, ISNULL(BDIM.L4Code, '') AS L4Code, ISNULL(BDIM.L4Name, '') AS L4Name, ISNULL(BDIM.L5Id, -1) AS L5Id, ISNULL(BDIM.L5Code, '') AS L5Code, ISNULL(BDIM.L5Name, '') AS L5Name, -- Item-party rate (MITEMPARTY/MITEMPARTYDETAIL; ItemPartyType derived from BizTransactionClass.ModuleId) ISNULL(VIPD.RATE, 0) AS ItemRate, ISNULL(VIP.MODEOFDESPATCH, 2) AS ModeOfPayment, ISNULL(VIPD.ITEMPARTYDETAILID, 0) AS ItemPartyDetailId, ISNULL(VIPD.RATEPERUOMID, MI.PURCHASEUOMID) AS RatePerUOMId, ISNULL(RPU.UOMCODE, PU.UOMCODE) AS RatePerUOMCode, ISNULL(RPU.UOMNAME, PU.UOMNAME) AS RatePerUOMName, ISNULL(PRMH.PARTYREFERENCENUMBER, '') AS PartyReferenceNumber, PRMH.PARTYREFERENCEDATE AS PartyReferenceDate FROM MITEM MI LEFT JOIN MITEMOU IOU ON IOU.ITEMID = MI.ITEMID AND IOU.OUID = @ouid LEFT JOIN MUOM PU ON PU.UOMID = MI.PURCHASEUOMID LEFT JOIN MUOM SU ON SU.UOMID = MI.STOCKUOMID LEFT JOIN MSKU SKU ON SKU.SKUID = MI.DEFAULTSKUID LEFT JOIN MPACK PK ON PK.PACKID = MI.DEFAULTPACKID LEFT JOIN MMATERIALACCOUNT MA ON MA.MATERIALACCOUNTID = MI.MATERIALACCOUNTID LEFT JOIN MCURRENCY CUR ON CUR.CURRENCYID = MI.PURCHASECURRENCYID LEFT JOIN MPRICECATEGORY PC ON PC.PRICECATEGORYID = MI.PRICECATEGORYID LEFT JOIN MITEMCATEGORY MIC ON MIC.ITEMCATEGORYID = MI.CATEGORYID LEFT JOIN MITEMSUBCATEGORY MISC ON MISC.ITEMSUBCATEGORYID = MI.SUBCATEGORYID LEFT JOIN MCOLOR MCOL ON MCOL.COLORID = MI.COLORID LEFT JOIN MMODEL MMOD ON MMOD.MODELID = MI.MODELID LEFT JOIN MITEMBRAND MIB ON MIB.ITEMBRANDID = MI.ITEMBRANDID LEFT JOIN MITEMGROUP MIG ON MIG.ITEMGROUPID = MI.ITEMGROUPID LEFT JOIN MSIZESET MSS ON MSS.SIZESETID = MI.SIZESETID LEFT JOIN MTRANSACTIONTYPE TT ON TT.TRANSACTIONTYPEID = ( SELECT TOP 1 T2.TRANSACTIONTYPEID FROM MTRANSACTIONTYPE T2 WHERE T2.TYPE = @type ORDER BY T2.TRANSACTIONTYPEID ) LEFT JOIN MMATERIALACCOUNTDETAIL MAD ON MAD.MATERIALACCOUNTID = MI.MATERIALACCOUNTID AND MAD.TRANSACTIONTYPEID = TT.TRANSACTIONTYPEID LEFT JOIN MACCOUNT SAC ON SAC.ACCOUNTID = MAD.SALESACCOUNTID LEFT JOIN MACCOUNT PAC ON PAC.ACCOUNTID = MAD.PURCHASEACCOUNTID LEFT JOIN MACCOUNT SRAC ON SRAC.ACCOUNTID = MAD.SALESRETURNACCOUNTID LEFT JOIN MACCOUNT PRAC ON PRAC.ACCOUNTID = MAD.PURCHASERETURNACCOUNTID LEFT JOIN MACCOUNT CONSAC ON CONSAC.ACCOUNTID = MA.CONSUMPTIONACCOUNTID LEFT JOIN TSTOCKPOSITION SP ON SP.ITEMID = MI.ITEMID AND SP.OUID = @ouid AND SP.STOREID = @storeid AND SP.SKUID = ISNULL(NULLIF(@skuid, -1), MI.DEFAULTSKUID) LEFT JOIN MSTORE S ON S.STOREID = @storeid LEFT JOIN MSKU RSKU ON RSKU.SKUID = NULLIF(@skuid, -1) LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = @ouid OUTER APPLY ( SELECT SUM(PD.GOODQUANTITY) AS GOODQUANTITY FROM TPACKNUMBER PN INNER JOIN TPACKDETAIL PD ON PN.PACKNUMBERID = PD.PACKNUMBERID WHERE PN.STATUS = 'Received' AND PN.ITEMID = MI.ITEMID AND PN.SKUID = ISNULL(NULLIF(@skuid, -1), MI.DEFAULTSKUID) AND PN.OUID = @ouid ) PACKNUM OUTER APPLY ( SELECT V1.BIZDIMENSIONVALUEID AS L1Id, V1.BIZDIMENSIONVALUECODE AS L1Code, V1.BIZDIMENSIONVALUENAME AS L1Name, V2.BIZDIMENSIONVALUEID AS L2Id, V2.BIZDIMENSIONVALUECODE AS L2Code, V2.BIZDIMENSIONVALUENAME AS L2Name, V3.BIZDIMENSIONVALUEID AS L3Id, V3.BIZDIMENSIONVALUECODE AS L3Code, V3.BIZDIMENSIONVALUENAME AS L3Name, V4.BIZDIMENSIONVALUEID AS L4Id, V4.BIZDIMENSIONVALUECODE AS L4Code, V4.BIZDIMENSIONVALUENAME AS L4Name, V5.BIZDIMENSIONVALUEID AS L5Id, V5.BIZDIMENSIONVALUECODE AS L5Code, V5.BIZDIMENSIONVALUENAME AS L5Name FROM MSTORE SS INNER JOIN MBIZDIMENSIONVALUESET DVS ON DVS.BIZDIMENSIONVALUESETID = SS.DEFAULTBIZDIMENSIONVALUESETID INNER JOIN MBIZDIMENSIONVALUE V1 ON V1.BIZDIMENSIONVALUEID = DVS.V1 INNER JOIN MBIZDIMENSIONVALUE V2 ON V2.BIZDIMENSIONVALUEID = DVS.V2 INNER JOIN MBIZDIMENSIONVALUE V3 ON V3.BIZDIMENSIONVALUEID = DVS.V3 INNER JOIN MBIZDIMENSIONVALUE V4 ON V4.BIZDIMENSIONVALUEID = DVS.V4 INNER JOIN MBIZDIMENSIONVALUE V5 ON V5.BIZDIMENSIONVALUEID = DVS.V5 WHERE SS.STOREID = @storeid ) BDIM LEFT JOIN MBIZTRANSACTIONCLASS BC ON BC.BIZTRANSACTIONCLASSID = @bizclassid LEFT JOIN MITEMPARTY VIP ON VIP.ITEMID = MI.ITEMID AND VIP.PARTYID = @partyid AND VIP.SKUID = ISNULL(NULLIF(@skuid, -1), MI.DEFAULTSKUID) AND VIP.ITEMPARTYTYPE = CASE BC.MODULEID WHEN -1899999995 THEN 0 WHEN -1899999994 THEN 1 WHEN -1399999999 THEN 2 ELSE 3 END LEFT JOIN MITEMPARTYDETAIL VIPD ON VIPD.ITEMPARTYID = VIP.ITEMPARTYID AND VIPD.EFFECTIVEFROM <= @fromdate AND VIPD.EFFECTIVETO >= @todate LEFT JOIN MUOM RPU ON RPU.UOMID = VIPD.RATEPERUOMID LEFT JOIN TMMDETAIL IPDOC ON IPDOC.DOCUMENTDETAILID = VIPD.DOCUMENTDETAILID LEFT JOIN TMMHEAD PRMH ON PRMH.DOCUMENTID = IPDOC.DOCUMENTID WHERE MI.ITEMID = @itemid;"; // ── GST tariff code lookup (by Item.GSTTariffId) ───────────────────────────── public const string GET_ITEM_DETAILS_GST_TARIFF_CODE = @" SELECT DUTYTARIFFCODE AS ItemPicklistCode FROM MDUTYTARIFF WHERE TARIFFTYPE = 1 AND DUTYTARIFFID = @gsttariffid;"; // ── Tax table detail resolution by TaxTableId + TransactionTypeId + period ─── public const string GET_ITEM_DETAILS_TAX_TABLE = @" SELECT TD.TAXTABLEDETAILID AS TaxTableDetailId, TD.SLNO AS TaxTableDetailSlNo, TD.TAXTABLEID AS TaxTableId, TT.TAXTABLECODE AS TaxTableCode, TT.TAXTABLENAME AS TaxTableName, TD.TRANSACTIONTYPEID AS TransactionTypeId, TTT.TRANSACTIONTYPECODE AS TransactionTypeCode, TTT.TRANSACTIONTYPENAME AS TransactionTypeName FROM MTAXTABLEDETAIL TD INNER JOIN MTAXTABLE TT ON TT.TAXTABLEID = TD.TAXTABLEID INNER JOIN MTAXTABLEPERIOD TP ON TP.TAXTABLEID = TT.TAXTABLEID INNER JOIN MTRANSACTIONTYPE TTT ON TTT.TRANSACTIONTYPEID = TD.TRANSACTIONTYPEID WHERE TD.TRANSACTIONTYPEID = @transactiontypeid AND TD.TAXTABLEID = @taxtableid AND TP.WITHEFFECTFROM <= @fromdate AND TP.WITHEFFECTTO >= @todate;"; // ── Duty table detail resolution by DutyTableId + TransactionTypeId ────────── // Reused for the GST duty table variant too (GB4 reuses the same query keyed on GSTTableId). public const string GET_ITEM_DETAILS_DUTY_TABLE = @" SELECT A.DUTYTABLEID AS DutyTableId, A.DUTYTABLECODE AS DutyTableCode, A.DUTYTABLENAME AS DutyTableName, B.DUTYTABLEDETAILID AS DutyTableDetailId, B.SLNO AS DutyTableDetailSlNo, B.TRANSACTIONTYPEID AS TransactionTypeId, C.TRANSACTIONTYPECODE AS TransactionTypeCode, C.TRANSACTIONTYPENAME AS TransactionTypeName FROM MDUTYTABLE A INNER JOIN MDUTYTABLEDETAIL B ON A.DUTYTABLEID = B.DUTYTABLEID INNER JOIN MTRANSACTIONTYPE C ON B.TRANSACTIONTYPEID = C.TRANSACTIONTYPEID WHERE B.TRANSACTIONTYPEID = @transactiontypeid AND A.DUTYTABLEID = @dutytableid;"; // ── Price list resolution (current rate; base query — date/category/tag filters appended in code) ── public const string GET_ITEM_DETAILS_PRICE_LIST_BASE = @" SELECT B.PRICELISTDETAILID AS PriceListDetailId, A.PRICELISTID AS PriceListId, A.ISSLAB AS TempIsSlab, A.EFFECTIVEFROM AS TempEffectiveFrom, B.SLNO AS PriceListDetailSlNo, B.PRICELISTTYPEID AS PriceListTypeId, D.PRICELISTTYPECODE AS PriceListTypeCode, D.PRICELISTTYPENAME AS PriceListTypeName, CAST(A.MRPRATE AS FLOAT) AS PriceListMRPRate, CAST(B.RATE AS FLOAT) AS PriceListDetailRate, B.CHARGESID AS ChargesId, E.CHARGECODE AS ChargesCode, E.CHARGENAME AS ChargesName, CAST(B.AMOUNTPLUS AS FLOAT) AS PriceListDetailAmountPlus, CAST(B.AMOUNTMINUS AS FLOAT) AS PriceListDetailAmountMinus, CAST(B.NETRATE AS FLOAT) AS PriceListDetailNetRate, B.REMARKS AS PriceListDetailRemarks, CAST(B.DISCOUNTPERCENT AS FLOAT) AS PriceListDetailDiscountPercent, CAST(B.DISCOUNTAMOUNT AS FLOAT) AS PriceListDetailDiscountAmount, C.PAYMENTTERMID AS PaymentTermId, PY.PAYMENTTERMNAME AS PaymentTermName FROM MPRICELIST A INNER JOIN MPRICELISTDETAIL B ON A.PRICELISTID = B.PRICELISTID INNER JOIN MPARTYPRICECATEGORYDETAIL C ON C.PARTYPRICECATEGORYID = @partypricecategoryid INNER JOIN MPRICELISTTYPE D ON B.PRICELISTTYPEID = D.PRICELISTTYPEID INNER JOIN MCHARGE E ON B.CHARGESID = E.CHARGEID INNER JOIN MPAYMENTTERM PY ON C.PAYMENTTERMID = PY.PAYMENTTERMID WHERE A.ITEMID = @itemid"; // ── Product-cost override (PriceCosting=1) ─────────────────────────────────── public const string GET_ITEM_DETAILS_PRODUCT_COST = @" SELECT TOP 1 CAST(TOTALCOST AS FLOAT) AS ProductCostTotalCost FROM MPRODUCTCOST WHERE ITEMID = @itemid AND SKUID = @skuid ORDER BY FROMDATE DESC;"; // ── Bin lookup by item + store ──────────────────────────────────────────────── public const string GET_ITEM_DETAILS_BIN = @" SELECT A.BINID AS BinId, G.BINNUMBER AS BinNumber FROM MITEMSTOCK A INNER JOIN MBIN G ON A.BINID = G.BINID WHERE A.ITEMID = @itemid AND A.STOREID = @storeid;"; // ── Discount-price adjustment (ItemRateType = 2, slab pricing) ─────────────── public const string GET_ITEM_DETAILS_DISCOUNT_PRICE = @" SELECT DISCOUNTPERCENTAGE AS DiscountPriceDiscountPercentage FROM MDISCOUNTPRICE WHERE PARTYPRICECATEGORYID = @partypricecategoryid AND PRODUCTPRICECATEGORYID = @itempricecategoryid AND OUID = @ouid;"; // ── Fallback rate from MITEMOU when no item-party/price-list rate resolved ─── public const string GET_ITEM_DETAILS_ITEMOU_RATE = @" SELECT CAST(SALESRATE AS FLOAT) AS ItemOUSalesRate, CAST(LASTPURCHASERATE AS FLOAT) AS ItemOULastPurchaseRate FROM MITEMOU WHERE ITEMID = @itemid AND OUID = @ouid;"; // ── BizTransactionClass.StockValueAffecting lookup (average-cost override gate) ── public const string GET_ITEM_DETAILS_BIZCLASS_STOCKVALUEAFFECTING = @" SELECT STOCKVALUEAFFECTING FROM MBIZTRANSACTIONCLASS WHERE BIZTRANSACTIONCLASSID = @bizclassid;"; // ── Average cost override (costed BizClass) ────────────────────────────────── public const string GET_ITEM_DETAILS_AVERAGE_COST = @" SELECT AVERAGECOST FROM TSTOCKPOSITION WHERE OUID = @ouid AND ITEMID = @itemid AND SKUID = @skuid AND STOREID = @storeid;"; // ── Item-party charges by ItemPartyDetailId ─────────────────────────────────── public const string GET_ITEM_DETAILS_PARTY_CHARGES = @" SELECT A.ITEMPARTYCHARGESID AS ItemPartyChargesId, A.ITEMPARTYDETAILID AS ItemPartyDetailId, A.SLNO AS ItemPartyChargesSlNo, A.FORMULAID AS FormulaId, C.FORMULACODE AS FormulaCode, C.FORMULANAME AS FormulaName, C.EXPRESSION AS FormulaExpression, D.OPERATIONTYPE AS ChargeDetailOperationType, CAST(A.INPUTVALUE AS FLOAT) AS ItemPartyChargesInputValue, CAST(A.CALCULATEDVALUE AS FLOAT) AS ItemPartyChargesCalculatedValue, CAST(A.AMOUNTPLUS AS FLOAT) AS ItemPartyChargesAmountPlus, CAST(A.AMOUNTMINUS AS FLOAT) AS ItemPartyChargesAmountMinus, A.REMARKS AS ItemPartyChargesRemarks FROM MITEMPARTYCHARGES A INNER JOIN MFORMULA C ON A.FORMULAID = C.FORMULAID INNER JOIN MCHARGEDETAIL D ON A.FORMULAID = D.FORMULAID WHERE A.ITEMPARTYDETAILID = @itempartydetailid;"; // ── Item comments (generic TCOMMENT, OBJECTTYPEID = -1899999573 for Item) ───── public const string GET_ITEM_DETAILS_COMMENTS = @" SELECT COMMENTTEXT FROM TCOMMENT WHERE COMMENTSTATUS = 0 AND SECURITYLEVEL = 0 AND OBJECTTYPEID = -1899999573 AND OBJECTID = @itemid;"; // ── Barcode lookup (pack/lot/quantity resolved from a scanned barcode) ──────── public const string GET_ITEM_DETAILS_BARCODE = @" SELECT A.PACKID AS PackId, C.PACKNAME AS PackName, A.LOTID AS LotId, B.LOTNUMBER AS LotNumber, A.PACKQUANTITY AS PackQuantity, A.QUANTITY AS Quantity, CAST(D.CONVERSIONFACTOR AS FLOAT) AS ConversionFactor FROM MBARCODE A INNER JOIN TLOT B ON A.LOTID = B.LOTID INNER JOIN MPACK C ON C.PACKID = A.PACKID INNER JOIN MPACKSETDETAIL D ON D.PACKID = C.PACKID WHERE A.BARCODEID = @barcodeid;"; // ── BOM production qty for the item (as a finished/produced item) ───────────── public const string GET_ITEM_DETAILS_BOM_PRODUCTION_QTY = @" SELECT BOMID AS ItemPicklistId, PRODUCTIONQTY AS MaxLevel, BOMCODE AS ItemPicklistCode, BOMNAME AS ItemPicklistName, VERSIONNAME AS ItemPicklistShortName FROM MBOM WHERE PRODUCTIONITEMID = @itemid AND TODATE >= @todate AND FROMDATE <= @fromdate;"; // ── GetItemDetailsQualityParameter ─────────────────────────────────────── // Removable clause for GET_ITEMDETAILS_QUALITY: when fromrouting == 0, // the DAL strips this string so ALL parameter sets are considered, not just // the item's quality parameter set. // ── GET_ITEMDETAILS_QUALITY_PARAMSET_CONDITION ────────────────────────────── public const string GET_ITEMDETAILS_QUALITY_PARAMSET_CONDITION = "AND paramset.PARAMETERSETID = it.QUALITYPARAMETERSETID"; public const string GET_ITEMDETAILS_QUALITY = @" SELECT itd.ITEMDETAILID AS ItemDetailId, itd.ITEMID AS ItemId, it.ITEMCODE AS ItemCode, it.ITEMNAME AS ItemName, it.ITEMSHORTNAME AS ItemShortName, paramset.SLNO AS ItemDetailSlNo, itd.PARAMETERID AS ParameterId, parameter.PARAMETERCODE AS ParameterCode, parameter.PARAMETERNAME AS ParameterName, parameter.PARAMETERDATATYPE AS ParameterDataType, CAST(parameter.MINVALUE AS FLOAT) AS ParameterMinValue, CAST(parameter.MAXVALUE AS FLOAT) AS ParameterMaxValue, parameter.LOVTYPEID AS ParameterLovTypeId, parameter.UOMID AS UOMId, uom.UOMCODE AS UOMCode, uom.UOMNAME AS UOMName, CAST(itd.MINIMUM AS FLOAT) AS ItemDetailMinimum, CAST(itd.MAXIMUM AS FLOAT) AS ItemDetailMaximum, itd.INPUTTEXT AS ItemDetailInputText, itd.INPUTDATE AS ItemDetailInputDate, itd.INPUTID AS InputId, lov.LOVCODE AS InputCode, lov.LOVNAME AS InputName, lov.SCORE AS Score, CAST(itd.INPUTVALUE AS FLOAT) AS ItemDetailInputValue, CAST(itd.FINALVALUE AS FLOAT) AS ItemDetailFinalValue, paramset.REJECTIONTYPE AS RejectionType, itd.STANDARDTEXT AS ItemDetailStandardText, itd.TYPE AS ItemDetailType, CAST(itd.TOLLERANCEPLUSPERCENT AS FLOAT) AS ItemDetailTollerancePlusPercent, CAST(itd.TOLLERANCEMINUSPERCENT AS FLOAT) AS ItemDetailTolleranceMinusPercent, itd.SKUID AS SKUId, sku.SKUCODE AS SKUCode, sku.SKUNAME AS SKUName FROM MITEMDETAIL itd JOIN MITEM it ON it.ITEMID = itd.ITEMID JOIN MPARAMETERSETDETAIL paramset ON paramset.PARAMETERID = itd.PARAMETERID JOIN MPARAMETER parameter ON parameter.PARAMETERID = paramset.PARAMETERID JOIN MUOM uom ON uom.UOMID = parameter.UOMID LEFT JOIN MLOV lov ON lov.LOVID = itd.INPUTID LEFT JOIN MSKU sku ON sku.SKUID = itd.SKUID WHERE itd.TYPE = 1 AND paramset.PARAMETERSETID = it.QUALITYPARAMETERSETID"; // ── GET_ITEMDETAILS_QUALITYPARAMETERS ─────────────────────────────────────── // No structural changes needed — was already correct after TINDENTDETAIL data fix public const string GET_ITEMDETAILS_QUALITYPARAMETERS = @" SELECT itd.ITEMDETAILID AS ItemDetailId, itd.ITEMID AS ItemId, it.ITEMCODE AS ItemCode, it.ITEMNAME AS ItemName, it.ITEMSHORTNAME AS ItemShortName, paramsetdet.SLNO AS ItemDetailSlNo, itd.PARAMETERID AS ParameterId, parameter.PARAMETERCODE AS ParameterCode, parameter.PARAMETERNAME AS ParameterName, parameter.PARAMETERDATATYPE AS ParameterDataType, CAST(parameter.MINVALUE AS FLOAT) AS ParameterMinValue, CAST(parameter.MAXVALUE AS FLOAT) AS ParameterMaxValue, parameter.LOVTYPEID AS ParameterLovTypeId, parameter.UOMID AS UOMId, uom.UOMCODE AS UOMCode, uom.UOMNAME AS UOMName, CAST(itd.MINIMUM AS FLOAT) AS ItemDetailMinimum, CAST(itd.MAXIMUM AS FLOAT) AS ItemDetailMaximum, itd.INPUTTEXT AS ItemDetailInputText, itd.INPUTDATE AS ItemDetailInputDate, itd.INPUTID AS InputId, lov.LOVCODE AS InputCode, lov.LOVNAME AS InputName, lov.SCORE AS Score, CAST(itd.INPUTVALUE AS FLOAT) AS ItemDetailInputValue, CAST(itd.FINALVALUE AS FLOAT) AS ItemDetailFinalValue, paramsetdet.REJECTIONTYPE AS RejectionType, itd.STANDARDTEXT AS ItemDetailStandardText, itd.TYPE AS ItemDetailType, CAST(itd.TOLLERANCEPLUSPERCENT AS FLOAT) AS ItemDetailTollerancePlusPercent, CAST(itd.TOLLERANCEMINUSPERCENT AS FLOAT) AS ItemDetailTolleranceMinusPercent, itd.SKUID AS SKUId, sku.SKUCODE AS SKUCode, sku.SKUNAME AS SKUName FROM TINDENTDETAIL indt JOIN MITEM it ON it.ITEMID = indt.ITEMID JOIN MROUTINGVERSIONDETAIL rverde ON rverde.ROUTINGDETAILID = indt.ROUTINGDETAILID JOIN MROUTINGVERSION rver ON rver.ROUTINGVERSIONID = rverde.ROUTINGVERSIONID JOIN MPARAMETERSETDETAIL paramsetdet ON paramsetdet.PARAMETERSETID = rverde.PARAMETERSETID JOIN MPARAMETER parameter ON parameter.PARAMETERID = paramsetdet.PARAMETERID JOIN MITEMDETAIL itd ON itd.ITEMID = indt.ITEMID AND itd.SKUID = indt.SKUID AND itd.PARAMETERID = parameter.PARAMETERID AND itd.TYPE = 1 JOIN MUOM uom ON uom.UOMID = parameter.UOMID LEFT JOIN MLOV lov ON lov.LOVID = itd.INPUTID LEFT JOIN MSKU sku ON sku.SKUID = itd.SKUID WHERE 1 = 1"; // ── GET_ITEMDETAILS_QUALITYPARAMETERS_FOR_ROUTINGS ────────────────────────── // FIX 2: rou.ITEMID = itd.ITEMID → rou.ROUTINGID = it.ROUTINGID (was always empty) public const string GET_ITEMDETAILS_QUALITYPARAMETERS_FOR_ROUTINGS = @" SELECT itd.ITEMDETAILID AS ItemDetailId, itd.ITEMID AS ItemId, it.ITEMCODE AS ItemCode, it.ITEMNAME AS ItemName, it.ITEMSHORTNAME AS ItemShortName, paramsetdet.SLNO AS ItemDetailSlNo, itd.PARAMETERID AS ParameterId, parameter.PARAMETERCODE AS ParameterCode, parameter.PARAMETERNAME AS ParameterName, parameter.PARAMETERDATATYPE AS ParameterDataType, CAST(parameter.MINVALUE AS FLOAT) AS ParameterMinValue, CAST(parameter.MAXVALUE AS FLOAT) AS ParameterMaxValue, parameter.LOVTYPEID AS ParameterLovTypeId, parameter.UOMID AS UOMId, uom.UOMCODE AS UOMCode, uom.UOMNAME AS UOMName, CAST(itd.MINIMUM AS FLOAT) AS ItemDetailMinimum, CAST(itd.MAXIMUM AS FLOAT) AS ItemDetailMaximum, itd.INPUTTEXT AS ItemDetailInputText, itd.INPUTDATE AS ItemDetailInputDate, itd.INPUTID AS InputId, lov.LOVCODE AS InputCode, lov.LOVNAME AS InputName, lov.SCORE AS Score, CAST(itd.INPUTVALUE AS FLOAT) AS ItemDetailInputValue, CAST(itd.FINALVALUE AS FLOAT) AS ItemDetailFinalValue, paramsetdet.REJECTIONTYPE AS RejectionType, itd.STANDARDTEXT AS ItemDetailStandardText, itd.TYPE AS ItemDetailType, CAST(itd.TOLLERANCEPLUSPERCENT AS FLOAT) AS ItemDetailTollerancePlusPercent, CAST(itd.TOLLERANCEMINUSPERCENT AS FLOAT) AS ItemDetailTolleranceMinusPercent, itd.SKUID AS SKUId, sku.SKUCODE AS SKUCode, sku.SKUNAME AS SKUName FROM MITEMDETAIL itd JOIN MITEM it ON it.ITEMID = itd.ITEMID JOIN MROUTING rou ON rou.ROUTINGID = it.ROUTINGID JOIN MROUTINGVERSION rver ON rver.ROUTINGID = rou.ROUTINGID JOIN MROUTINGVERSIONDETAIL rverde ON rverde.ROUTINGVERSIONID = rver.ROUTINGVERSIONID AND rverde.OUTPUTSKUID = itd.SKUID JOIN MPARAMETERSET paramset ON paramset.PARAMETERSETID = rverde.PARAMETERSETID JOIN MPARAMETERSETDETAIL paramsetdet ON paramsetdet.PARAMETERSETID = paramset.PARAMETERSETID JOIN MPARAMETER parameter ON parameter.PARAMETERID = paramsetdet.PARAMETERID AND parameter.PARAMETERID = itd.PARAMETERID JOIN MUOM uom ON uom.UOMID = parameter.UOMID LEFT JOIN MLOV lov ON lov.LOVID = itd.INPUTID LEFT JOIN MSKU sku ON sku.SKUID = itd.SKUID WHERE itd.TYPE = 1"; // GB4 parity: PooledItemBLL.GetItemSelectlistFromOU (Item.svc/GetItemSelectlistFromOU). // GB4 built this from the ItemOU HQL entity filtered by LoginDTO.WorkOUId; GB5 equivalent // joins MITEMOU to MITEM and filters by the same OU. Criteria columns mirror // ApplyItemSelectlistFromOU's CriteriaWithQueryField map (Id/Code/Name/Status/ItemType/BOMType/IsStockPosting/IsMakeOrBuy). public const string GET_ITEM_SELECTLIST_OU_INNER = @" SELECT ROW_NUMBER() OVER (ORDER BY MI.ITEMCODE) AS RowNum, MI.ITEMID AS ItemPicklistId, MI.ITEMCODE AS ItemPicklistCode, MI.ITEMNAME AS ItemPicklistName FROM MITEMOU IOU JOIN MITEM MI ON MI.ITEMID = IOU.ITEMID WHERE IOU.OUID = @ouid"; public const string GET_ITEM_SELECTLIST_OU_OUTER = @" SELECT ItemPicklistId, ItemPicklistCode, ItemPicklistName FROM PagedItemOU WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; // ── GetItemBasedOnDocument (GB4 parity: ItemBLL.GetItemBasedOnDocument) ──────── // // GB4 source: MMBLL.Item.ItemBLL.GetItemBasedOnDocument. Two shapes selected by the // "Type" criteria attribute: // Type 0 (Regular) — GET_ITEM_PICKLIST_DOCUMENT_WISE: every TMMDETAIL line // for the given DocumentId, joined to MITEM/MUOM. // Type 1 (Costing Detail) — a two-step query: first collect TCOSTINGDETAIL.OBJECTID // rows already costed for the document (OBJECTTYPEID // -1899997854, TCOSTING.CREATEDON >= TMMHEAD.MODIFIEDON) // into a temp set, then return only the TMMDETAIL lines // whose DOCUMENTDETAILID is NOT in that set — i.e. lines // still awaiting costing. // DocumentId is mandatory (GB4 throws MethodNotAllowedException when absent); Name is // an optional code/name LIKE filter appended by the DAL after criteria are applied. // RowNum + firstnumber/maxresult replaces GB4's NHibernate // ExceuteSelectQuerySameSession(Sql, LoginDTO, FirstNumber, MaxResult) paging. public const string GET_ITEM_PICKLIST_DOCUMENT_WISE_INNER = @" SELECT a.DOCUMENTDETAILID AS DocumentDetailId, b.ITEMID AS Id, b.ITEMCODE AS Code, a.PRODUCTDESCRIPTION AS Name, a.GOODQUANTITY AS Quantity, d.UOMID AS StockUOMId, d.UOMCODE AS StockUOMCode, d.UOMNAME AS StockUOMName, ROW_NUMBER() OVER (ORDER BY a.DOCUMENTDETAILID) AS RowNum FROM TMMDETAIL a JOIN MITEM b ON a.ITEMID = b.ITEMID JOIN MUOM d ON b.STOCKUOMID = d.UOMID WHERE 1=1"; public const string GET_ITEM_PICKLIST_DOCUMENT_WISE_COSTING_INNER = @" SELECT a.DOCUMENTDETAILID AS DocumentDetailId, b.ITEMID AS Id, b.ITEMCODE AS Code, a.PRODUCTDESCRIPTION AS Name, a.GOODQUANTITY AS Quantity, d.UOMID AS StockUOMId, d.UOMCODE AS StockUOMCode, d.UOMNAME AS StockUOMName, ROW_NUMBER() OVER (ORDER BY a.DOCUMENTDETAILID) AS RowNum FROM TMMDETAIL a JOIN MITEM b ON a.ITEMID = b.ITEMID JOIN MUOM d ON CASE WHEN a.TRANSACTIONUOMID = -1 THEN b.STOCKUOMID ELSE a.TRANSACTIONUOMID END = d.UOMID WHERE a.DOCUMENTDETAILID NOT IN ( SELECT b2.OBJECTID FROM TCOSTING a2 JOIN TCOSTINGDETAIL b2 ON a2.COSTINGID = b2.COSTINGID JOIN TMMDETAIL c2 ON b2.OBJECTID = c2.DOCUMENTDETAILID JOIN TMMHEAD d2 ON c2.DOCUMENTID = d2.DOCUMENTID WHERE b2.OBJECTTYPEID = -1899997854 AND a2.CREATEDON >= d2.MODIFIEDON AND d2.DOCUMENTID = @documentid )"; public const string GET_ITEM_PICKLIST_DOCUMENT_WISE_OUTER = @" SELECT DocumentDetailId, Id, Code, Name, Quantity, StockUOMId, StockUOMCode, StockUOMName FROM PagedItemDocument WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; // ── DeleteItem (GB4 migration: DELETE /mms/Item.svc/?ItemId={ItemId}) ───────── // // GB4 source: ItemBLL.DeleteItem — six sequential DELETEs inside one // GenericNhibernateLazyLoadingFrameDAL transaction, preceded in Item.svc.cs by // WhereUsedDAL.WhereUsed(ItemId, typeof(Item), Login, "'mitemaddon','mitemdetail'"). // // WhereUsed was a generic NHibernate-mapping-driven referencing-row scan (skipped when // LoginDTO.DatabaseType == 1, i.e. Oracle). GB5 has no equivalent metadata reflection, so // the guard is migrated as an explicit count over the transaction tables GB4's own // ItemBLL.DeleteItemWhereUsed checks for this same cascade: TSTOCKLEDGER, TMMDETAIL, // TSTOCKPOSITION. // // Indexes required: // MITEM: PK_MITEM (ITEMID) — existing // MITEMDETAIL: IX_MITEMDETAIL_ITEMID (ITEMID) // MITEMOU: IX_MITEMOU_ITEMID (ITEMID) // MITEMADDON: IX_MITEMADDON_ITEMID (ITEMID) // MITEMALIAS: IX_MITEMALIAS_ITEMID (ITEMID) — already noted in ItemAliasQB // MITEMSTOCK: IX_MITEMSTOCK_ITEMID (ITEMID) // TSTOCKLEDGER: IX_TSTOCKLEDGER_ITEMID (ITEMID, TENANTID) // TMMDETAIL: IX_TMMDETAIL_ITEMID (ITEMID) // TSTOCKPOSITION: IX_TSTOCKPOSITION_ITEMID (ITEMID) // Tenant-scoped existence probe. MITEM is the only table in this cascade that carries // TENANTID, so this is what confines the whole delete to the caller's tenant — the child // DELETEs below are keyed on ITEMID alone (see DeleteItem in ItemDAL). public const string GET_ITEM_EXISTS = @" SELECT COUNT(1) FROM MITEM WHERE ITEMID = @ItemId AND TENANTID = @TenantId;"; // WhereUsed part 1 of 2 — transaction tables, checked explicitly. // These are the three GB4's own ItemBLL.DeleteItemWhereUsed checks. They are listed by // hand rather than left to the FK scan below because transaction tables in this schema do // not reliably declare a foreign key back to MITEM, so metadata alone would miss them. // TMMDETAIL and TSTOCKPOSITION have no TENANTID column (tenant isolation for them comes // from the physical database) — only TSTOCKLEDGER is filtered. public const string GET_ITEM_TRANSACTION_USAGE_COUNT = @" SELECT (SELECT COUNT(1) FROM TSTOCKLEDGER WHERE ITEMID = @ItemId AND TENANTID = @TenantId) + (SELECT COUNT(1) FROM TMMDETAIL WHERE ITEMID = @ItemId) + (SELECT COUNT(1) FROM TSTOCKPOSITION WHERE ITEMID = @ItemId);"; // WhereUsed part 2 of 2 — every declared foreign key into MITEM.ITEMID, minus the five // child tables this delete cascades. GB4's WhereUsed walked NHibernate mappings to get // this list; GB5 reads it from the live catalog instead, so tables added later are // covered without touching this code. // // Without this, a referenced item fails on raw SQL error 547 mid-transaction (e.g. // FKPRODUCTCOSTID_ITEMID on MPRODUCTCOST) instead of being refused up front. // ItemDAL.GetItemBlockingReferences turns these rows into EXISTS probes. public const string GET_ITEM_REFERENCING_TABLES = @" SELECT s.name AS SchemaName, t.name AS TableName, c.name AS ColumnName FROM sys.foreign_key_columns fkc JOIN sys.tables t ON t.object_id = fkc.parent_object_id JOIN sys.schemas s ON s.schema_id = t.schema_id JOIN sys.columns c ON c.object_id = fkc.parent_object_id AND c.column_id = fkc.parent_column_id JOIN sys.tables rt ON rt.object_id = fkc.referenced_object_id JOIN sys.columns rc ON rc.object_id = fkc.referenced_object_id AND rc.column_id = fkc.referenced_column_id WHERE rt.name = 'MITEM' AND rc.name = 'ITEMID' AND t.name NOT IN ('MITEMDETAIL', 'MITEMOU', 'MITEMADDON', 'MITEMALIAS', 'MITEMSTOCK') GROUP BY s.name, t.name, c.name ORDER BY t.name;"; // Cascade, child-before-parent — same six statements and same order as GB4. public const string DELETE_ITEM_DETAIL = @"DELETE FROM MITEMDETAIL WHERE ITEMID = @ItemId;"; public const string DELETE_ITEM_OU = @"DELETE FROM MITEMOU WHERE ITEMID = @ItemId;"; public const string DELETE_ITEM_ADDON = @"DELETE FROM MITEMADDON WHERE ITEMID = @ItemId;"; public const string DELETE_ITEM_ALIAS = @"DELETE FROM MITEMALIAS WHERE ITEMID = @ItemId;"; public const string DELETE_ITEM_STOCK = @"DELETE FROM MITEMSTOCK WHERE ITEMID = @ItemId;"; public const string DELETE_ITEM = @" DELETE FROM MITEM WHERE ITEMID = @ItemId AND TENANTID = @TenantId;"; } }