s4/Stock_PerPlant.sql

SELECT
  MARC.MANDT AS Client_MANDT,
  MARC.MATNR AS MaterialNumber_MATNR,
  MARA.MaterialText_MAKTX AS MaterialText_MAKTX,
  MARA.Language_SPRAS AS Language_SPRAS,
  MARA.ProductHierarchy_PRDHA AS ProductHierarchy_PRDHA,
  MARA.BaseUnitOfMeasure_MEINS AS BaseUnitOfMeasure_MEINS,
  MARA.MaterialType_MTART AS MaterialType_MTART,
  MARA.MaterialGroup_MATKL AS MaterialGroup_MATKL,
  MARC.WERKS AS Plant_WERKS,
  T001W.Name_NAME1 AS Plant_Name,
  T001W.Region_County__REGIO AS Plant_Region,
  T001W.CountryKey_LAND1 AS Plant_Country,
  MARC.PSTAT AS MaintenanceStatus_PSTAT,
  MARC.LVORM AS FlagMaterialForDeletionAtPlantLevel_LVORM,
  MARC.BWTTY AS ValuationCategory_BWTTY,
  MARC.XCHAR AS BatchManagementIndicator__internal___XCHAR,
  MARC.MMSTA AS PlantSpecificMaterialStatus_MMSTA,
  MARC.MMSTD AS DateFromWhichThePlantSpecificMaterialStatusIsValid_MMSTD,
  MARC.MAABC AS AbcIndicator_MAABC,
  MARC.KZKRI AS Indicator_CriticalPart_KZKRI,
  MARC.EKGRP AS PurchasingGroup_EKGRP,
  MARC.AUSME AS UnitOfIssue_AUSME,
  MARC.DISPR AS Material_MrpProfile_DISPR,
  MARC.DISMM AS MrpType_DISMM,
  MARC.DISPO AS MrpController_DISPO,
  MARC.KZDIE AS Indicator_MrpControllerIsBuyer__deactivated___KZDIE,
  MARC.PLIFZ AS PlannedDeliveryTimeInDays_PLIFZ,
  MARC.WEBAZ AS GoodsReceiptProcessingTimeInDays_WEBAZ,
  MARC.PERKZ AS PeriodIndicator_PERKZ,
  MARC.AUSSS AS AssemblyScrapInPercent_AUSSS,
  MARC.DISLS AS LotSize__materialsPlanning___DISLS,
  MARC.BESKZ AS ProcurementType_BESKZ,
  MARC.SOBSL AS SpecialProcurementType_SOBSL,
  MARC.MINBE AS ReorderPoint_MINBE,
  MARC.EISBE AS SafetyStock_EISBE,
  MARC.BSTMI AS MinimumLotSize_BSTMI,
  MARC.BSTMA AS MaximumLotSize_BSTMA,
  MARC.BSTFE AS FixedLotSize_BSTFE,
  MARC.BSTRF AS RoundingValueForPurchaseOrderQuantity_BSTRF,
  MARC.MABST AS MaximumStockLevel_MABST,
  MARC.LOSFX AS OrderingCosts_LOSFX,
  MARC.SBDKZ AS DependentRequirementsIndForIndividualAndCollReqmts_SBDKZ,
  MARC.LAGPR AS StorageCostsIndicator_LAGPR,
  MARC.ALTSL AS MethodForSelectingAlternativeBillsOfMaterial_ALTSL,
  MARC.KZAUS AS DiscontinuationIndicator_KZAUS,
  MARC.AUSDT AS EffectiveOutDate_AUSDT,
  MARC.NFMAT AS FollowUpMaterial_NFMAT,
  MARC.KZBED AS IndicatorForRequirementsGrouping_KZBED,
  MARC.MISKZ AS MixedMrpIndicator_MISKZ,
  MARC.FHORI AS SchedulingMarginKeyForFloats_FHORI,
  MARC.PFREI AS Indicator_AutomaticFixingOfPlannedOrders_PFREI,
  MARC.FFREI AS ReleaseIndicatorForProductionOrders_FFREI,
  MARC.RGEKZ AS Indicator_Backflush_RGEKZ,
  MARC.FEVOR AS ProductionSupervisor_FEVOR,
  MARC.BEARZ AS ProcessingTime_BEARZ,
  MARC.RUEZT AS SetupAndTeardownTime_RUEZT,
  MARC.TRANZ AS InteroperationTime_TRANZ,
  MARC.BASMG AS BaseQuantity_BASMG,
  MARC.DZEIT AS InHouseProductionTime_DZEIT,
  MARC.MAXLZ AS MaximumStoragePeriod_MAXLZ,
  MARC.LZEIH AS UnitForMaximumStoragePeriod_LZEIH,
  MARC.KZPRO AS Indicator_WithdrawalOfStockFromProductionBin_KZPRO,
  MARC.GPMKZ AS Indicator_MaterialIncludedInRoughCutPlanning_GPMKZ,
  MARC.UEETO AS OverdeliveryToleranceLimit_UEETO,
  MARC.UEETK AS Indicator_UnlimitedOverdeliveryAllowed_UEETK,
  MARC.UNETO AS UnderdeliveryToleranceLimit_UNETO,
  MARC.WZEIT AS TotalReplenishmentLeadTime__inWorkdays___WZEIT,
  MARC.ATPKZ AS ReplacementPart_ATPKZ,
  MARC.VZUSL AS SurchargeFactorForCostInPercent_VZUSL,
  MARC.HERBL AS StateOfManufacture_HERBL,
  MARC.INSMK AS PostToInspectionStock_INSMK,
  MARC.SPROZ AS SampleForQualityInspection__inpct____deactivated___SPROZ,
  MARC.QUAZT AS QuarantinePeriod__deactivated___QUAZT,
  MARC.SSQSS AS ControlKeyForQualityManagementInProcurement_SSQSS,
  MARC.MPDAU AS MeanInspectionDuration__deactivated___MPDAU,
  MARC.KZPPV AS IndicatorForInspectionPlan__deactivated___KZPPV,
  MARC.KZDKZ AS DocumentationRequiredIndicator_KZDKZ,
  MARC.WSTGH AS ActiveSubstanceContent__deactivated___WSTGH,
  MARC.PRFRQ AS IntervalUntilNextRecurringInspection_PRFRQ,
  MARC.NKMPR AS DateAccordingToCheckSamplingInspection__deactivated___NKMPR,
  MARC.UMLMC AS StockInTransfer__plantToPlant___UMLMC,
  MARC.LADGR AS LoadingGroup_LADGR,
  MARC.XCHPF AS BatchManagementRequirementIndicator_XCHPF,
  MARC.USEQU AS QuotaArrangementUsage_USEQU,
  MARC.LGRAD AS ServiceLevel_LGRAD,
  MARC.AUFTL AS SplittingIndicator_AUFTL,
  MARC.PLVAR AS PlanVersion_PLVAR,
  MARC.OTYPE AS ObjectType_OTYPE,
  MARC.OBJID AS ObjectId_OBJID,
  MARC.MTVFP AS CheckingGroupForAvailabilityCheck_MTVFP,
  MARC.PERIV AS FiscalYearVariant_PERIV,
  MARC.KZKFK AS Indicator_TakeCorrectionFactorsIntoAccount_KZKFK,
  MARC.VRVEZ AS ShippingSetupTime_VRVEZ,
  MARC.VBAMG AS BaseQuantityForCapacityPlanningInShipping_VBAMG,
  MARC.VBEAZ AS ShippingProcessingTime_VBEAZ,
  MARC.LIZYK AS Deactivated_LIZYK,
  MARC.BWSCL AS SourceOfSupply_BWSCL,
  MARC.KAUTB AS Indicator_automaticPurchaseOrderAllowed_KAUTB,
  MARC.KORDB AS Indicator_SourceListRequirement_KORDB,
  MARC.STAWN AS CommodityCodeimportCodeNumberForForeignTrade_STAWN,
  MARC.HERKL AS CountryOfOriginOfMaterial__nonPreferentialOrigin___HERKL,
  MARC.HERKR AS RegionOfOriginOfMaterial__nonPreferentialOrigin___HERKR,
  MARC.EXPME AS UnitOfMeasureForCommodityCode__foreignTrade___EXPME,
  MARC.MTVER AS MaterialGroupExportForForeignTrade_MTVER,
  MARC.PRCTR AS ProfitCenter_PRCTR,
  MARC.TRAME AS StockInTransit_TRAME,
  MARC.MRPPP AS PpcPlanningCalendar_MRPPP,
  MARC.SAUFT AS Ind_RepetitiveMfgAllowed_SAUFT,
  MARC.FXHOR AS PlanningTimeFence_FXHOR,
  MARC.VRMOD AS ConsumptionMode_VRMOD,
  MARC.VINT1 AS ConsumptionPeriod_Backward_VINT1,
  MARC.VINT2 AS ConsumptionPeriod_Forward_VINT2,
  MARC.VERKZ AS VersionIndicator_VERKZ,
  MARC.STLAL AS AlternativeBom_STLAL,
  MARC.STLAN AS BomUsage_STLAN,
  MARC.PLNNR AS KeyForTaskListGroup_PLNNR,
  MARC.APLAL AS GroupCounter_APLAL,
  MARC.LOSGR AS LotSizeForProductCosting_LOSGR,
  MARC.SOBSK AS SpecialProcurementTypeForCosting_SOBSK,
  MARC.FRTME AS ProductionUnit_FRTME,
  MARC.LGPRO AS IssueStorageLocation_LGPRO,
  MARC.DISGR AS MrpGroup_DISGR,
  MARC.KAUSF AS ComponentScrapInPercent_KAUSF,
  MARC.QZGTP AS CertificateType_QZGTP,
  MARC.QMATV AS InspectionSetupExistsForMaterialplant_QMATV,
  MARC.TAKZT AS TaktTime_TAKZT,
  MARC.RWPRO AS RangeOfCoverageProfile_RWPRO,
  MARC.COPAM AS LocalFieldNameForCopaLinkToSop_COPAM,
  MARC.ABCIN AS PhysicalInventoryIndicatorForCycleCounting_ABCIN,
  MARC.AWSLS AS VarianceKey_AWSLS,
  MARC.SERNP AS SerialNumberProfile_SERNP,
  MARC.CUOBJ AS InternalObjectNumber_CUOBJ,
  MARC.STDPD AS ConfigurableMaterial_STDPD,
  MARC.SFEPR AS RepetitiveManufacturingProfile_SFEPR,
  MARC.XMCNG AS NegativeStocksAllowedInPlant_XMCNG,
  MARC.QSSYS AS RequiredQmSystemForVendor_QSSYS,
  MARC.LFRHY AS PlanningCycle_LFRHY,
  MARC.RDPRF AS RoundingProfile_RDPRF,
  MARC.VRBMT AS ReferenceMaterialForConsumption_VRBMT,
  MARC.VRBWK AS ReferencePlantForConsumption_VRBWK,
  MARC.VRBDT AS ToDateOfTheMaterialToBeCopiedForConsumption_VRBDT,
  MARC.VRBFK AS MultiplierForReferenceMaterialForConsumption_VRBFK,
  MARC.AUTRU AS ResetForecastModelAutomatically_AUTRU,
  MARC.PREFE AS PreferenceIndicatorInExportimport_PREFE,
  MARC.PRENC AS ExemptionCertificate_IndicatorForLegalControl_PRENC,
  MARC.PRENO AS ExemptionCertificateNumberForLegalControl_PRENO,
  MARC.PREND AS ExemptionCertificate_IssueDateOfExemptionCertificate_PREND,
  MARC.PRENE AS Indicator_VendorDeclarationExists_PRENE,
  MARC.PRENG AS ValidityDateOfVendorDeclaration_PRENG,
  MARC.ITARK AS Indicator_MilitaryGoods_ITARK,
  MARC.SERVG AS IsRServiceLevel_SERVG,
  MARC.KZKUP AS Indicator_MaterialCanBeCoProduct_KZKUP,
  MARC.STRGR AS PlanningStrategyGroup_STRGR,
  MARC.CUOBV AS InternalObjectNumberOfConfigurableMaterialForPlanning_CUOBV,
  MARC.LGFSB AS DefaultStorageLocationForExternalProcurement_LGFSB,
  MARC.SCHGT AS Indicator_BulkMaterial_SCHGT,
  MARC.CCFIX AS CcIndicatorIsFixed_CCFIX,
  MARC.EPRIO AS StockDeterminationGroup_EPRIO,
  MARC.QMATA AS MaterialAuthorizationGroupForActivitiesInQm_QMATA,
  MARC.RESVP AS PeriodOfAdjustmentForPlannedIndependentRequirements_RESVP,
  MARC.PLNTY AS TaskListType_PLNTY,
  MARC.UOMGR AS UnitOfMearsureGroup__oil_NaturalGas____UOMGR,
  MARC.UMRSL AS ConversionGroup__oil_NaturalGas____UMRSL,
  MARC.ABFAC AS AirBouyancyFactor_ABFAC,
  MARC.SFCPF AS ProductionSchedulingProfile_SFCPF,
  MARC.SHFLG AS SafetyTimeIndicator__withOrWithoutSafetyTime___SHFLG,
  MARC.SHZET AS SafetyTime__inWorkdays___SHZET,
  MARC.MDACH AS ActionControl_PlannedOrderProcessing_MDACH,
  MARC.KZECH AS DeterminationOfBatchEntryInTheProductionprocessOrder_KZECH,
  MARC.MEGRU AS UnitOfMeasureGroup_MEGRU,
  MARC.MFRGR AS MaterialFreightGroup_MFRGR,
  MARC.VKUMC AS StockTransferSalesValue__plantToPlant__ForVoMaterial_VKUMC,
  MARC.VKTRW AS TransitValueAtSalesPriceForValueOnlyMaterial_VKTRW,
  MARC.KZAGL AS Indicator_SmoothPromotionConsumption_KZAGL,
  MARC.FVIDK AS ProductionVersionToBeCosted_FVIDK,
  MARC.FXPRU AS FixedPriceCoProduct_FXPRU,
  MARC.LOGGR AS LogisticsHandlingGroupForWorkloadCalculation_LOGGR,
  MARC.FPRFM AS DistributionProfileOfMaterialInPlant_FPRFM,
  MARC.GLGMG AS TiedEmptiesStock_GLGMG,
  MARC.VKGLG AS SalesValueOfTiedEmptiesStock_VKGLG,
  MARC.INDUS AS MaterialCfopCategory_INDUS,
  MARC.MOWNR AS Cap_NumberOfCapProductsList_MOWNR,
  MARC.MOGRU AS CommonAgriculturalPolicy_CapProductsGroupForeignTrade_MOGRU,
  MARC.CASNR AS CasNumberForPharmaceuticalProductsInForeignTrade_CASNR,
  MARC.GPNUM AS ProductionStatistics_ProdcomNumberForForeignTrade_GPNUM,
  MARC.STEUC AS ControlCodeForConsumptionTaxesInForeignTrade_STEUC,
  MARC.FABKZ AS Indicator_ItemRelevantToJitDeliverySchedules_FABKZ,
  MARC.MATGR AS GroupOfMaterialsForTransitionMatrix_MATGR,
  MARC.VSPVB AS ProposedSupplyAreaInMaterialMasterRecord_VSPVB,
  MARC.DPLFS AS FairShareRule_DPLFS,
  MARC.DPLPU AS Indicator_PushDistribution_DPLPU,
  MARC.DPLHO AS DeploymentHorizonInDays_DPLHO,
  MARC.MINLS AS MinimumLotSizeForSupplyDemandMatch_MINLS,
  MARC.MAXLS AS MaximumLotSizeForSupplyDemandMatch_MAXLS,
  MARC.FIXLS AS FixedLotSizeForSupplyDemandMatch_FIXLS,
  MARC.LTINC AS LotSizeIncrementForSupplyDemandMatch_LTINC,
  MARC.COMPL AS ThisFieldIsNoLongerUsed_COMPL,
  MARC.CONVT AS ConversionTypesForProductionFigures_CONVT,
  MARC.SHPRO AS PeriodProfileForSafetyTime_SHPRO,
  MARC.AHDIS AS MrpRelevancyForDependentRequirements_AHDIS,
  MARC.DIBER AS Indicator_MrpAreaExists_DIBER,
  MARC.KZPSP AS IndicatorForCrossProjectMaterial_KZPSP,
  MARC.OCMPF AS OverallProfileForOrderChangeManagement_OCMPF,
  MARC.APOKZ AS Indicator_IsMaterialRelevantForApo_APOKZ,
  MARC.MCRUE AS MardhRecAlreadyExistsForPerBeforeLastOfMardPer_MCRUE,
  MARC.LFMON AS CurrentPeriod__postingPeriod___LFMON,
  MARC.LFGJA AS FiscalYearOfCurrentPeriod_LFGJA,
  MARC.EISLO AS MinimumSafetyStock_EISLO,
  MARC.NCOST AS DoNotCost_NCOST,
  MARC.ROTATION_DATE AS StrategyForPutawayAndStockRemoval_ROTATION_DATE,
  MARC.UCHKZ AS IndicatorForOriginalBatchManagement_UCHKZ,
  MARC.UCMAT AS ReferenceMaterialForOriginalBatches_UCMAT,
  MARC.BWESB AS ValuatedGoodsReceiptBlockedStock_BWESB,
  MARC.SGT_COVS AS SegmentationStrategy_SGT_COVS,
  MARC.SGT_STATC AS SegmentationStatus_SGT_STATC,
  MARC.SGT_SCOPE AS SegmentationStrategyScope_SGT_SCOPE,
  MARC.SGT_MRPSI AS SortStockBasedOnSegment_SGT_MRPSI,
  MARC.SGT_PRCM AS ConsumptionPriority_SGT_PRCM,
  MARC.SGT_CHINT AS DiscreteBatchNumber_SGT_CHINT,
  MARC.SGT_STK_PRT AS StockProtectionIndicator_SGT_STK_PRT,
  MARC.SGT_DEFSC AS DefaultStockSegmentValue_SGT_DEFSC,
  MARC.SGT_MRP_ATP_STATUS AS AtpmrpStatusForMaterialAndSegment_SGT_MRP_ATP_STATUS,
  MARC.SGT_MMSTD AS DateFromWhichThePlantSpecificMaterialStatusIsValid_SGT_MMSTD,
  MARC.FSH_MG_ARUN_REQ AS OrderAllocationRun_FSH_MG_ARUN_REQ,
  MARC.FSH_SEAIM AS Indicator_SeasonActiveInInventoryManagement_FSH_SEAIM,
  MARC.FSH_VAR_GROUP AS VariantGroup_FSH_VAR_GROUP,
  MARC.FSH_KZECH AS Indicator_BatchAssignmentDuringTrToToConversion_FSH_KZECH,
  MARC.FSH_CALENDAR_GROUP AS CalendarGroup_FSH_CALENDAR_GROUP,
  MARC.PPSKZ AS AdvancedPlanning_PPSKZ,
  MARC.SAPMP_TOLPRPL AS PercentageTolerancePlus_SAPMP_TOLPRPL,
  MARC.SAPMP_TOLPRMI AS PercentageToleranceMinus_SAPMP_TOLPRMI,
  MARC.VSO_R_PKGRP AS PackingGroupOfTheMaterial__vso___VSO_R_PKGRP,
  MARC.VSO_R_LANE_NUM AS LineWithinTheAutomaticPickingZone__vso___VSO_R_LANE_NUM,
  MARC.VSO_R_PAL_VEND AS MaterialNoOfThePackagingMaterialOfTheVendor__vso___VSO_R_PAL_VEND,
  MARC.VSO_R_FORK_DIR AS PickPackagingMaterialsOnlyLengthwise__vso___VSO_R_FORK_DIR,
  MARC.IUID_RELEVANT AS IuidRelevant_IUID_RELEVANT,
  MARC.IUID_TYPE AS StructureTypeOfUii_IUID_TYPE,
  MARC.UID_IEA AS ExternalAllocationOfUii_UID_IEA,
  MARC.CONS_PROCG AS ConsignmentControl_CONS_PROCG,
  MARC.GI_PR_TIME AS GoodsIssueProcessingTimeInDays_GI_PR_TIME,
  MARC.MULTIPLE_EKGRP AS PurchasingAcrossPurchasingGroup_MULTIPLE_EKGRP,
  MARC.REF_SCHEMA AS ReferenceDeterminationSchema_REF_SCHEMA,
  MARC.MIN_TROC AS MinimumTargetRangeOfCoverage_MIN_TROC,
  MARC.MAX_TROC AS MaximumTargetRangeOfCoverage_MAX_TROC,
  MARC.TARGET_STOCK AS TargetStock_TARGET_STOCK
FROM
  `{{ project_id_src }}.{{ dataset_cdc_processed_s4 }}.marc` AS MARC
INNER JOIN
  `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.MaterialsMD` AS MARA
  ON
    MARA.Client_MANDT = MARC.mandt
    AND MARA.MaterialNumber_MATNR = MARC.matnr
INNER JOIN
  `{{ project_id_tgt }}.{{ dataset_reporting_tgt }}.PlantsMD` AS T001W
  ON
    T001W.Client_MANDT = MARC.mandt
    AND T001W.Plant_WERKS = MARC.werks
    AND T001W.Language_SPRAS = MARA.Language_SPRAS