Regular Expressions 101

Save & Share

  • Regex Version: ver. 2
  • Update Regex
    ctrl+⇧+s
  • Save new Regex
    ctrl+s
  • Add to Community Library

Flavor

  • PCRE2 (PHP >=7.3)
  • PCRE (PHP <7.3)
  • ECMAScript (JavaScript)
  • Python
  • Golang
  • Java 8
  • .NET 7.0 (C#)
  • Rust
  • Regex Flavor Guide

Function

  • Match
  • Substitution
  • List
  • Unit Tests

Tools

Sponsors
There are currently no sponsors. Become a sponsor today!
An explanation of your regex will be automatically generated as you type.
Detailed match information will be displayed here automatically.
  • All Tokens
  • Common Tokens
  • General Tokens
  • Anchors
  • Meta Sequences
  • Quantifiers
  • Group Constructs
  • Character Classes
  • Flags/Modifiers
  • Substitution
  • A single character of: a, b or c
    [abc]
  • A character except: a, b or c
    [^abc]
  • A character in the range: a-z
    [a-z]
  • A character not in the range: a-z
    [^a-z]
  • A character in the range: a-z or A-Z
    [a-zA-Z]
  • Any single character
    .
  • Alternate - match either a or b
    a|b
  • Any whitespace character
    \s
  • Any non-whitespace character
    \S
  • Any digit
    \d
  • Any non-digit
    \D
  • Any word character
    \w
  • Any non-word character
    \W
  • Non-capturing group
    (?:...)
  • Capturing group
    (...)
  • Zero or one of a
    a?
  • Zero or more of a
    a*
  • One or more of a
    a+
  • Exactly 3 of a
    a{3}
  • 3 or more of a
    a{3,}
  • Between 3 and 6 of a
    a{3,6}
  • Start of string
    ^
  • End of string
    $
  • A word boundary
    \b
  • Non-word boundary
    \B

Regular Expression
No Match

/
/
gmi

Test String

Code Generator

Language

Generated Code

$re = '/\bcreate[ \t]+(view|procedure|proc)[ \t]+(.+)\n(((?![ \t]*go[ \t]*).*\n)+)[ \t]*go[ \t]*$/mi'; $str = 'set quoted_identifier on set ansi_nulls on go -------------------------------------------------------------------------------- -- NAME: AP_INVOICE -- -- PURPOSE: Represents the one to one relationship between Fin_APINVMST and -- Fin_SUBINV. No filtering done, this view includes all rows. -------------------------------------------------------------------------------- if objectproperty(object_id(\'AP_INVOICE\'), \'IsView\') = 1 drop view AP_INVOICE go create view AP_INVOICE as select a.APIMIVNM ,a.APIMVNID ,a.APIMDATE ,a.APIMTYPE ,a.APIMDOCN ,a.APIMICDT ,a.APIMUTAX ,a.APIMVOCH ,a.APIMLCRD ,a._Row APINVMST_Row ,a._APOPNINV_OwnRow ,a._APVENINV_OwnRow ,a._Ts APINVMST_Ts ,s.APIMPDID ,s.APIMYRID ,s.APIMIVST ,s.APIMSTDT ,s.APIMMERC ,s.APIMIPFL ,s.APIMFRET ,s.APIMFTFL ,s.APIMOCHG ,s.APIMOCFL ,s.APIMSTAX ,s.APIMSTFL ,s.APIMSCHG ,s.APIMDS1D ,s.APIMDS1A ,s.APIMDS2D ,s.APIMDS2A ,s.APIMDUDT ,s.APIMNRCV ,s.APIMMTST ,s.APIMCMNT ,s.APIMDISC ,s.APIMPYMT ,s.APIMPYST ,s.APIMPSDT ,s.APIMAGE ,s.APIMTAMT ,s.APIMRFLG ,s.APIMFAMT ,s.APIMFMTP ,s.APIMFMTD ,s.APIMCTRL ,s.APPAYHOW ,s.APPAYWEN ,s.APCUTOFF ,s.APDELAY ,s.APPAYAM1 ,s.APPAYDY1 ,s.APPAYAM2 ,s.APPAYDY2 ,s.APPAYAM3 ,s.APPAYDY3 ,s.APPAYAM4 ,s.APIMXVAR ,s.APIMXRAT ,s._Row SUBINV_Row ,s._INVSUB_OwnRow ,s._Ts SUBINV_Ts from Fin_APINVMST a left join Fin_SUBINV s on (s._INVSUB_OwnRow = a._Row) with check option go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[EDI_COMTHeader]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[EDI_COMTHeader] go create view EDI_COMTHeader as ( select c.CO_NUMBER ,c.CUST_ID ,c.ACK_TYPE ,c.PREV_EXT ,c.SHIP_TO ,c.DISC_1PCNT ,c.DISC_1DAYS ,c.DISC_2PCNT ,c.DISC_2DAYS ,c.NET_DAYS ,c.TRANS_VIA ,c.FOB_POINT ,c.INV_CODE ,c.FOB_CODE ,c.PO_NUMBER ,c.EDICOHeaderKey from <FSDBXXTemp, char, >.[dbo].COMTHEAD c ) go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[EDI_COMTLine]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[EDI_COMTLine] go create view EDI_COMTLine as ( select c.CO_NUMBER ,c.LN_NO ,c.ORDLN_NO ,c.SUBLN_NO ,c.LINE_TYPE ,c.ORDER_QTY ,c.RECV_QTY ,c.ITM_REV ,c.UM ,c.LN_STA ,c.LN_TYPE ,c.ITM_GLMOCO ,c.[DESC] ,c.VEND_ITEM ,c.VI_DESC ,c.VI_UM ,c.VI_UMCNVT ,c.STRT_DT ,c.PROM_DOCK ,c.UNIT_PRICE ,c.PO_NUMBER ,c.PO_LNNO ,c.EDICOLineKey from <FSDBXXTemp, char, >.[dbo].COMTLINE c ) go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[EDI_COMTText]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[EDI_COMTText] go create view EDI_COMTText as ( select c.ORDER_NO ,c.LN_NO ,c.PO_NUMBER ,c.ORDLN_NO ,c.SUBLN_NO ,c.XTXT_NO ,c.TEXT_LN1 ,c.TEXT_LN2 ,c.TEXT_LN3 ,c.TEXT_LN4 ,c.EDICOTextKey from <FSDBXXTemp, char, >.[dbo].COMTTEXT c ) go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[EDI_PACKHeader]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[EDI_PACKHeader] go create view EDI_PACKHeader as ( select p.EDI_KEY_ID ,p.SHPMNT_NO ,p.CUST_ID ,p.LST_STYLE ,p.TS_SID ,p.SHIP_DATE ,p.SHIP_TIME ,p.ARVL_DATE ,p.ARVL_TIME ,p.ADR_TY_1 ,p.ADR_NM_1 ,p.ADR_CD_1 ,p.ADR_ID_1 ,p.ST_ADDR1 ,p.ST_ADDR2 ,p.ST_CITY ,p.ST_STATE ,p.ST_ZIP ,p.ST_CNTRY ,p.ADR_TY_2 ,p.ADR_NM_2 ,p.ADR_CD_2 ,p.ADR_ID_2 ,p.SF_ADDR1 ,p.SF_ADDR2 ,p.SF_CITY ,p.SF_STATE ,p.SF_ZIP ,p.SF_CNTRY ,p.ADR_TY_3 ,p.ADR_NM_3 ,p.ADR_CD_3 ,p.ADR_ID_3 ,p.BT_ADDR1 ,p.BT_ADDR2 ,p.BT_CITY ,p.BT_STATE ,p.BT_ZIP ,p.BT_CNTRY ,p.REF_CD_1 ,p.REF_NO_1 ,p.REF_CD_2 ,p.REF_NO_2 ,p.TD3_ENO_1 ,p.TD3_SNO_1 ,p.TD5_ID_1 ,p.TD4_CLS ,p.TD4_DSC ,p.TOT_GWGT ,p.TOT_NWGT ,p.TOT_WUM ,p.TOT_PALETS ,p.TOT_VALUE ,p.TOT_FRGHT ,p.EDI_STATUS ,p.EDIPackageHeaderKey from <FSDBXXTemp, char, >.[dbo].PACKHEAD p ) go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[EDI_PACKLine]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[EDI_PACKLine] go create view EDI_PACKLine as ( select p.EDI_KEY_ID ,p.EDI_KEY_LN ,p.EDI_KEY_SL ,p.SHPMNT_NO ,p.PKGNO_STRT ,p.CUST_DESC ,p.ITEM ,p.TOTPKGS ,p.PCSPER ,p.SHP_UM ,p.UNIT_PRICE ,p.LIN_CD_1 ,p.LIN_ID_1 ,p.LIN_CD_2 ,p.LIN_ID_2 ,p.LIN_CD_3 ,p.LIN_ID_3 ,p.LIN_CD_4 ,p.LIN_ID_4 ,p.LIN_CD_5 ,p.LIN_ID_5 ,p.PO_NUMBER ,p.PO_LNNO ,p.RELS_NO ,p.CO_NUMBER ,p.CO_LN_NO ,p.NMFC_ITEM ,p.NMFC_SUB ,p.PKGNO ,p.PKG_PCD ,p.PKG_PDCD ,p.PKG_WGT ,p.PKG_WUM ,p.LS_RCSNO ,p.PLTNO ,p.PLT_PDCD ,p.MS_RCSNO ,p.IPP_TAG ,p.EDI_STATUS ,p.EDIPackageLineKey from <FSDBXXTemp, char, >.[dbo].PACKLINE p ) go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[EDI_POMTHeader]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[EDI_POMTHeader] go create view EDI_POMTHeader as ( select p.PO_NUMBER ,p.PO_TYPE ,p.BUYR ,p.VENDOR_ID ,p.VENDOR_NAME ,p.CONTACT ,p.PHONE ,p.PO_DT_ORG ,p.PO_DT_REV ,p.VEND_ADDR2 ,p.ST_NAME ,p.ST_ADDR1 ,p.ST_ADDR2 ,p.ST_CITY ,p.ST_STATE ,p.ST_COUNTRY ,p.ST_ZIP ,p.DISC_1PCNT ,p.DISC_1DAYS ,p.DISC_2PCNT ,p.DISC_2DAYS ,p.NET_DAYS ,p.TRANS_VIA ,p.FOB_POINT ,p.CONTRACT ,p.PREV_EXT ,p.RELS_NO ,p.EDIPOHeaderKey from <FSDBXXTemp, char, >.[dbo].POMTHEAD p ) go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[EDI_POMTLine]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[EDI_POMTLine] go create view EDI_POMTLine as ( select p.PO_NUMBER ,p.LN_NO ,p.ORDLN_NO ,p.SUBLN_NO ,p.LINE_TYPE ,p.ORDER_QTY ,p.RECV_QTY ,p.ITM_REV ,p.UM ,p.LN_STA ,p.LN_TYP ,p.ITM_GLMOCO ,p.[DESC] ,p.VEND_ITEM ,p.VI_DESC ,p.VI_UM ,p.VI_UMCNVT ,p.STRT_DT ,p.PROM_DOCK ,p.UNIT_PRICE ,p.HOLD_QTY ,p.LRCV_QTY ,p.LRCV_DT ,p.LRCV_TM ,p.PKLST_NO ,p.EDIPOLineKey from <FSDBXXTemp, char, >.[dbo].POMTLINE p ) go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[EDI_POMTText]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[EDI_POMTText] go create view EDI_POMTText as ( select p.ORDER_NO ,p.LN_NO ,p.PO_NUMBER ,p.ORDLN_NO ,p.SUBLN_NO ,p.XTXT_NO ,p.TEXT_LN1 ,p.TEXT_LN2 ,p.TEXT_LN3 ,p.TEXT_LN4 ,p.EDIPOTextKey from <FSDBXXTemp, char, >.[dbo].POMTTEXT p ) go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[EDI_SHIPHeader]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[EDI_SHIPHeader] go create view EDI_SHIPHeader as ( select s.SHPMNT_NO ,s.CUST_ID ,s.SHIP_TO_ID ,s.MISC1 ,s.MISC2 ,s.TOT_WEIGHT ,s.TOT_PALETS ,s.TOT_UNITS ,s.TOT_FRGHT ,s.FTOT_FRGHT ,s.PRO_NUMBER ,s.CARRIER_ID ,s.CARRIER ,s.SHIP_TO_NM ,s.SHIP_ADDR1 ,s.SHIP_ADDR2 ,s.SHIP_CITY ,s.SHIP_STATE ,s.SHIP_CNTRY ,s.SHIP_ZIP ,s.SHPMT_DATE ,s.SHPMT_REF ,s.CTRL_CURR ,s.CO_NUMBER ,s.CUST_PO_NO ,s.EDIShipmentHeaderKey from <FSDBXXTemp, char, >.[dbo].SHIPHEAD s ) go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[EDI_SHIPLine]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[EDI_SHIPLine] go create view EDI_SHIPLine as ( select s.SHPMNT_NO ,s.CO_NUMBER ,s.CUST_PO_NO ,s.ORD_WEIGHT ,s.LN_NO ,s.SHIP_QTY ,s.ORDER_QTY ,s.UNIT_PRICE ,s.FUNIT_PRIC ,s.ITEM ,s.ITEM_DESC ,s.UM ,s.UPC_CODE ,s.PACKAGE_NO ,s.PKG_TYP ,s.UNITS_PER ,s.UNIT_TOTAL ,s.EDIShipmentLineKey from <FSDBXXTemp, char, >.[dbo].SHIPLINE s ) go ------------------------------------------------------------------------------------------------------------- -- NAME: Workflow_UserData; Workflow_GroupData; Workflow_ProcessData; ENG_BOM; -- ENG_ITEM; REFDES; DefinedPhases; DefinedRules; DefinedConditions; LinkedPhaseRules; -- LinkedRuleEdits; LinkedSelections; USERVIEWS1; -- -- PURPOSE: Used for ENGM/MMAM; keep the consistency with the original Access tables. -- ------------------------------------------------------------------------------------------------------------- if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[DefinedConditions]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[DefinedConditions] go create view DefinedConditions as select f.ConditionName as ConditionName ,f.FieldName as FieldName ,f.TableName as TableName ,f.RelationshipText as RelationshipText ,f.RelationshipType as RelationshipType ,f.RelationshipCategory as RelationshipCategory ,f.ConditionValue as [Values] ,f.FieldValueTableName as FieldValueTableName ,f.FieldCaption as FieldCaption ,f.ValueCaption as ValueCaption ,f.ItemPromotionConditionRowVersion as RowVersion ,f.ItemPromotionConditionKey as ConditionKey from FS_ItemPromotionCondition f go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[DefinedPhases]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[DefinedPhases] go create view DefinedPhases as select f.PhaseName as PhaseName ,f.ItemPromotionPhaseRowVersion as RowVersion ,f.ItemPromotionPhaseKey as PhaseKey from FS_ItemPromotionPhase f go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[DefinedRules]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[DefinedRules] go create view DefinedRules as select f.RuleName as RuleName ,f.IsWarningOnly as WarningOnly ,f.ItemPromotionRuleRowVersion as RowVersion ,f.ItemPromotionRuleKey as RuleKey from FS_ItemPromotionRule f go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[ENG_BOM]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[ENG_BOM] go create view ENG_BOM as select f.PointOfUseID as PT_USE ,f.OperationSequenceNumber as SEQN ,f.RequiredQuantity as QUANTITY ,f.QuantityType as QTY_TYP ,f.ComponentType as COM_TYP ,f.InEffectivityDate as IN_EFFECT ,f.OutEffectivityDate as OUT_EFFECT ,f.InRevision as IN_REV ,f.OutRevision as OUT_REV ,f.Text as TEXT ,f.ProductionComponentItemNumber as PROD_COMPONENT ,f.LeadTimeOffsetDays as LT_OFFSET ,f.ScrapPercent as SCRAP_PCNT ,p.EngineeringItemNumber as PARENT ,c.EngineeringItemNumber as COMPONENT ,f.EngineeringBillOfMaterialKey as KEY_RECORD ,f.ParentEngineeringItemKey as ParentEngineeringItemKey ,f.ComponentEngineeringItemKey as ComponentEngineeringItemKey ,f.EngineeringBillOfMaterialRowVersion as RowVersion from FS_EngineeringItem p inner join FS_EngineeringBillOfMaterial f on p.EngineeringItemKey = f.ParentEngineeringItemKey left outer join FS_EngineeringItem c on f.ComponentEngineeringItemKey = c.EngineeringItemKey go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[ENG_ITEM]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[ENG_ITEM] go create view ENG_ITEM as select f.EngineeringItemNumber as ITEM ,f.EngineeringItemDescription as ITEM_DESC ,f.EngineeringItemUM as UM ,f.MakeBuyCode as MB ,f.DrawingNumber as DRWG ,f.ItemRevision as REV ,f.DecimalPrecision as DPC ,f.ItemLocalUnitPrice as PRICE ,f.ItemCost as COST ,f.ProductionItemNumber as PROD_ITEM ,f.PhaseDate as PHASE_DATE ,f.ItemType as ITEM_TYPE ,f.FixedLeadTimeDays as FIXED_LEAD_TIME ,f.ProposedProductionItemNumber as PROPOSED_PROD_ITEM ,f.ItemPromotionPhaseKey as PHASE ,f.ItemKey as ItemKey ,f.ItemPromotionPhaseKey as ItemPromotionPhaseKey ,f.EngineeringItemKey as EngineeringItemKey from FS_EngineeringItem f go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[LinkedPhaseRules]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[LinkedPhaseRules] go create view LinkedPhaseRules as select f.ItemPromotionPhaseKey as PhaseKey ,f.ItemPromotionRuleKey as RuleKey ,f.ItemPromotionPhaseRuleLinkRowVersion as RowVersion from FS_ItemPromotionPhaseRuleLink f go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[LinkedRuleEdits]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[LinkedRuleEdits] go create view LinkedRuleEdits as select f.ItemPromotionRuleKey as RuleKey ,f.ItemPromotionConditionKey as EditKey ,f.ItemPromotionRuleConditionLinkRowVersion as RowVersion from FS_ItemPromotionRuleConditionLink f where f.IsEditOrSelection=\'E\' go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[LinkedRuleSelections]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[LinkedRuleSelections] go create view LinkedRuleSelections as select f.ItemPromotionRuleKey as RuleKey ,f.ItemPromotionConditionKey as SelectionKey ,f.ItemPromotionRuleConditionLinkRowVersion as RowVersion from FS_ItemPromotionRuleConditionLink f where f.IsEditOrSelection=\'S\' go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[REFDES]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[REFDES] go create view REFDES as select f.ParentItemSource as PARENT_SOURCE ,f.ParentEngineeringItemNumber as PARENT ,f.PointOfUseID as PT_USE ,f.OperationSequenceNumber as SEQN ,f.ItemSource as ITEM_SOURCE ,f.EngineeringItemNumber as ITEM ,f.ReferenceDesignator as REFDES ,f.ReferenceDesignatorPreFix as RD_A ,f.ReferenceDesignatorNumber as RD_B ,f.ReferenceDesignatorText as RD_TAG ,f.InEffectivityDate as RD_IN_EFFECT ,f.OutEffectivityDate as RD_OUT_EFFECT ,f.ReferenceDesignatorError as RD_ERROR ,f.ParentEngineeringItemKey as ParentEngineeringItemKey ,f.EngineeringItemKey as EngineeringItemKey ,f.ReferenceDesignatorRowVersion as RowVersion ,f.ReferenceDesignatorKey as ReferenceDesignatorKey from FS_ReferenceDesignator f go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[USERVIEWS1]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[USERVIEWS1] go create view USERVIEWS1 as select f.StandardGridNumber as STANDARDVIEWNUMBER ,f.UserGridSource as ViewSource ,f.IsLocalOrGlobal as IsLocalOrGlobal ,f.UserGridType as ViewType ,f.UserGridName as ViewName ,f.Owner as Owner ,f.SortUserGridColumnKey as SORTCOLUMN ,f.ReportPathName as REPORTNAME ,f.RowHeight as RowHeight ,f.[Option] as OPTIONS ,f.Argument as Argument ,f.UserGridRowVersion as RowVersion ,f.UserGridKey as UserGridKey from FS_UserGrid f go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[Workflow_ProcessData]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[Workflow_ProcessData] go create view Workflow_ProcessData as select p.ProcessNumber ,pd.ActivitySequence ,pd.ActivityStatus ,pd.IsCompleted ,pd.StartDate ,pd.CompletedDate ,pd.IsNotificationMailed ,a.ActivityName ,a.ActivityDescription ,a.AllowedDays ,a.StartupMessage ,a.WorkflowActivityKey ,p.WorkflowProcessKey ,pd.WorkflowProcessDataKey from FS_WorkflowProcessData pd inner join FS_WorkflowProcess p on pd.WorkflowProcessKey = p.WorkflowProcessKey inner join FS_WorkflowActivity a on pd.WorkflowActivityKey = a.WorkflowActivityKey go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[Workflow_GroupData]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[Workflow_GroupData] go create view Workflow_GroupData as select pd.ProcessNumber ,gd.GroupStatus ,gd.IsCompleted ,gd.StartDate ,gd.CompletedDate ,g.GroupName ,g.GroupDescription ,g.IsNotificationOnly ,pd.WorkflowProcessKey ,pd.WorkflowActivityKey ,gd.WorkflowGroupKey ,gd.WorkflowGroupDataKey from FS_WorkflowGroup g inner join FS_WorkflowGroupData gd on g.WorkflowGroupKey = gd.WorkflowGroupKey inner join Workflow_ProcessData pd on gd.WorkflowProcessDataKey = pd.WorkflowProcessDataKey go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[Workflow_UserData]\') and OBJECTPROPERTY(id, N\'IsView\') = 1) drop view [dbo].[Workflow_UserData] go create view Workflow_UserData as select gd.WorkflowGroupDataKey ,gd.ProcessNumber ,ud.UserStatus ,ud.IsCompleted ,ud.StartDate ,ud.CompletedDate ,ud.Comment ,u.UserID ,u.LastName ,u.FirstName ,u.UserEmailName ,u.UserEmail ,gd.WorkflowProcessKey ,gd.WorkflowActivityKey ,gd.WorkflowGroupKey ,ud.WorkflowUserKey ,ud.WorkflowUserDataKey from FS_WorkflowUserData ud inner join FS_WorkflowUser u on ud.WorkflowUserKey = u.WorkflowUserKey inner join Workflow_GroupData gd on ud.WorkflowGroupDataKey = gd.WorkflowGroupDataKey go -------------------------------------------------------------------------------- -- NAME: FIND_CUSTOMER -- -- PURPOSE: Used for the Webui. Represents the one to one relationship -- between Mfg_CUSTMAST and Mfg_SUBCUST. Also joins Mfg_TXT using -- Mfg_DESCTXT_Lnk and Mfg_PLNRTXT_Lnk. No filtering done, this view -- includes all rows. -------------------------------------------------------------------------------- if objectproperty(object_id(\'FSFORMATDATE\'), \'IsScalarFunction\') = 1 drop function FSFORMATDATE go CREATE FUNCTION FSFORMATDATE(@aDate as datetime, @aDisplayType as char) RETURNS varchar(30) AS BEGIN RETURN case @aDisplayType WHEN \'U\' THEN CAST(DATEPART(m, @aDate) as varchar) + \'/\' + DATENAME(d, @aDate) + \'/\' + DATENAME(yyyy, @aDate) WHEN \'B\' THEN DATENAME(d, @aDate) + \'/\' + CAST(DATEPART(m, @aDate) as varchar) + \'/\' + DATENAME(yyyy, @aDate) WHEN \'S\' THEN DATENAME(yyyy, @aDate) + \'/\' + CAST(DATEPART(m, @aDate) as varchar) + \'/\' + DATENAME(d, @aDate) WHEN \'C\' THEN DATENAME(yyyy, @aDate) + \'/\' + CAST(DATEPART(m, @aDate) as varchar) + \'/\' + DATENAME(d, @aDate) WHEN \'E\' THEN DATENAME(d, @aDate) + \'/\' + CAST(DATEPART(m, @aDate) as varchar) + \'/\' + DATENAME(yyyy, @aDate) end END go if objectproperty(object_id(\'FIND_CUSTOMER\'), \'IsView\') = 1 drop view FIND_CUSTOMER go create view FIND_CUSTOMER as select c.CMAR ,c.CMACFAX ,c.CMACTCNT ,c.CMACFONE ,c.CMARACNO ,c.CMCGACNO ,c.CMFTACNO ,c.CMSACNO ,convert(nchar,LTRIM(RTRIM(str(s.CMARCUR, 14, convert(int, m.CLBODY))))) CMARCUR ,s.CMARCUR CMARCURFINDERSORT ,convert(nchar,LTRIM(RTRIM(str(s.CMARHOLD, 14, convert(int, m.CLBODY))))) CMARHOLD ,s.CMARHOLD CMARHOLDFINDERSORT ,convert(nchar,LTRIM(RTRIM(str(s.CMAR30, 14, convert(int, m.CLBODY))))) CMAR30 ,s.CMAR30 CMAR30FINDERSORT ,convert(nchar,LTRIM(RTRIM(str(s.CMAR60, 14, convert(int, m.CLBODY))))) CMAR60 ,s.CMAR60 CMAR60FINDERSORT ,convert(nchar,LTRIM(RTRIM(str(s.CMAR90, 14, convert(int, m.CLBODY))))) CMAR90 ,s.CMAR90 CMAR90FINDERSORT ,convert(nchar,LTRIM(RTRIM(str(s.CMARBAL, 14, convert(int, m.CLBODY))))) CMARBAL ,s.CMARBAL CMARBALFINDERSORT ,convert(nchar,LTRIM(RTRIM(str(s.CMUNAPCS, 14, convert(int, m.CLBODY))))) CMUNAPCS ,s.CMUNAPCS CMUNAPCSFINDERSORT ,convert(nchar,LTRIM(RTRIM(str(s.CMUNAPCR, 14, convert(int, m.CLBODY))))) CMUNAPCR ,s.CMUNAPCR CMUNAPCRFINDERSORT ,c.CMBANK1 ,c.CMBANK2 ,c.CMB2AD1 ,c.CMB2AD2 ,c.CMB2CITY ,c.CMB2CNTR ,c.CMBILLTO ,c.CMB2NAME ,c.CMB2STAT ,c.CMB2ZIP ,convert(nchar,LTRIM(RTRIM(str(s.CMBOOK, 14, convert(int, m.CLBODY))))) CMBOOK ,s.CMBOOK CMBOOKFINDERSORT ,c.CMCARR ,c.CMCOMCD ,CASE c.CMCNTCUR WHEN \'L\' THEN convert(nchar,LTRIM(RTRIM(str(s.CMLCRBAL, 14, convert(int, m.CLBODY))))) WHEN \'R\' THEN convert(nchar,LTRIM(RTRIM(str(s.CMLCRBAL, 14, convert(int, q.CURRPRE))))) WHEN \'F\' THEN convert(nchar,LTRIM(RTRIM(str(s.CMLCRBAL, 14, convert(int, q.CURRPRE))))) End As CMLCRBAL ,s.CMLCRBAL CMLCRBALFINDERSORT ,CASE c.CMCNTCUR WHEN \'L\' THEN convert(nchar,LTRIM(RTRIM(str(c.CMCREDIT, 14, convert(int, m.CLBODY))))) WHEN \'R\' THEN convert(nchar,LTRIM(RTRIM(str(c.CMCREDIT, 14, convert(int, q.CURRPRE))))) WHEN \'F\' THEN convert(nchar,LTRIM(RTRIM(str(c.CMCREDIT, 14, convert(int, q.CURRPRE))))) End As CMCREDIT ,c.CMCREDIT CMCREDITFINDERSORT ,c.CMCRAUTH ,dbo.FSFORMATDATE(c.CMCRDATE, SUBSTRING(a.CLBODY,1,1)) CMCRDATE ,c.CMCRDATE CMCRDATEFINDERSORT ,c.CMACASGN ,c.CMACREMV ,c.CMCRHOLD ,c.CMCRREP ,c.CMCSR ,c.CMCNTCUR ,c.CMCURCOD ,c.CMCUAD1 ,c.CMCUAD2 ,c.CMCUCITY ,c.CMCUCNTR ,c.CMCUSTAT ,c.CMCUZIP ,c.CMCLASS ,c.CMCLASS$2 ,c.CMCLASS$3 ,c.CMCLASS$4 ,c.CMCLASS$5 ,c.CMCLASS$6 ,c.CMCLASL ,c.CMCLASL$2 ,c.CMCONTCT ,c.CMFAX ,c.CMCUSTID ,c.CMCLVL ,c.CMCUNAME ,c.CMPHONE ,c.CMDBSIC ,dbo.FSFORMATDATE(c.CMDBDATE, SUBSTRING(a.CLBODY,1,1)) CMDBDATE ,c.CMDBDATE CMDBDATEFINDERSORT ,c.CMDBRATE ,CASE c.CMDBID WHEN \'\' THEN \'\' ELSE SUBSTRING(c.CMDBID, 1,2) + \'-\' + SUBSTRING(c.CMDBID,3,3) + \'-\' + RIGHT(c.CMDBID,4) End As CMDBID ,c.CMSHCODE ,c.CMRTCODE ,c.CMEMAIL ,c.CMENDUSR ,c.CMFOB ,c.CMFRTCD ,dbo.FSFORMATDATE(s.CMLSTINV, SUBSTRING(a.CLBODY,1,1)) CMLSTINV ,s.CMLSTINV CMLSTINVFINDERSORT ,dbo.FSFORMATDATE(s.CMLSTPMT, SUBSTRING(a.CLBODY,1,1)) CMLSTPMT ,s.CMLSTPMT CMLSTPMTFINDERSORT ,c.CMARTYPE ,c.CMPALLOC ,c.CMPARTL ,convert(nchar,LTRIM(RTRIM(str(c.CMTRADE, 7, 1)))) CMTRADE ,c.CMTRADE CMTRADEFINDERSORT ,c.CMPRNT ,c.CMPRTLNG ,c.CMPRTFMT ,c.CMPAYHOW ,c.CMREGION ,s.CMSVCHRG ,c.CMSTMTCY ,c.CMSTMTCD ,c.CMSTATUS ,c.CMSTKRM ,c.CMBIN ,convert(nchar,LTRIM(RTRIM(str(c.CMINTRST* 100, 7, 2)))) CMINTRST ,c.CMINTRST CMINTRSTFINDERSORT ,dbo.FSFORMATDATE(c.CMCADATE, SUBSTRING(a.CLBODY,1,1)) CMCADATE ,c.CMCADATE CMCADATEFINDERSORT ,c.CMCTXCER ,c.CMTXCERT ,c.CMMTXCER ,c.CMSTXCER ,c.CMCTAXCD ,c.CMFTAXCD ,c.CMMTAXCD ,c.CMSTAXCD ,c.CMEXC ,c.CMEXMPT ,c.CMEXM ,c.CMEXS ,convert(nchar,LTRIM(RTRIM(str(c.CMTRMPC1, 7, 1)))) CMTRMPC1 ,c.CMTRMPC1 CMTRMPC1FINDERSORT ,c.CMTRMDA1 ,convert(nchar,LTRIM(RTRIM(str(c.CMTRMPC2, 7, 1)))) CMTRMPC2 ,c.CMTRMPC2 CMTRMPC2FINDERSORT ,c.CMTRMDA2 ,convert(nchar,LTRIM(RTRIM(str(c.CMTRMPC3, 7, 1)))) CMTRMPC3 ,c.CMTRMPC3 CMTRMPC3FINDERSORT ,c.CMTRMDA3 ,convert(nchar,LTRIM(RTRIM(str(c.CMTRMPC4, 7, 1)))) CMTRMPC4 ,c.CMTRMPC4 CMTRMPC4FINDERSORT ,c.CMTRMDA4 ,c.CMTRMNET ,c.CMCUTOFF ,c.CMPAYWEN ,c.CMDELAY ,n.TXT1 NAME_TEXT1 ,n.TXT2 NAME_TEXT2 ,n.TXT3 NAME_TEXT3 ,n.TXT4 NAME_TEXT4 ,n.TXID NAME_TEXT ,f.TXT1 FIN_TEXT1 ,f.TXT2 FIN_TEXT2 ,f.TXT3 FIN_TEXT3 ,f.TXT4 FIN_TEXT4 ,f.TXID FIN_TEXT ,c.CMTOPLVL ,c.CMTRCLAS ,c.CMVATCUS ,convert(nchar,LTRIM(RTRIM(str(s.CMYTDPD, 14, convert(int, m.CLBODY))))) CMYTDPD ,s.CMYTDPD CMYTDPDFINDERSORT ,convert(nchar,LTRIM(RTRIM(str(s.CMYTDSLS, 14, convert(int, m.CLBODY))))) CMYTDSLS ,s.CMYTDSLS CMYTDSLSFINDERSORT ,convert(nchar,LTRIM(RTRIM(str(s.CMYTDPO, 14, convert(int, m.CLBODY))))) CMYTDPO ,s.CMYTDPO CMYTDPOFINDERSORT ,convert(nchar,LTRIM(RTRIM(str(s.CMYTDSVC, 14, convert(int, m.CLBODY))))) CMYTDSVC ,s.CMYTDSVC CMYTDSVCFINDERSORT from Mfg_CUSTMAST c left join Mfg_SUBCUST s on (s._CUSTSUB_OwnRow = c._Row) left join Mfg_DESCTXT_Lnk d on (d._OwnRec = 11 and d._OwnRow = c._Row) left join Mfg_TXT f on (f._Row = d._MbrRow) left join Mfg_PLNRTXT_Lnk p on (p._OwnRec = 11 and p._OwnRow = c._Row) left join Mfg_TXT n on (n._Row = p._MbrRow) join Mfg_PARAMETR m on m.CLTYPE = \'DN\' and m.CLID = \'PREC\' join Mfg_PARAMETR a on a.CLTYPE = \'CR\' and a.CLID = \'CF01\' LEFT OUTER JOIN dbo.Mfg_CURRDEF q ON c.CMCURCOD = q.CURRCODE with check option go -------------------------------------------------------------------------------- -- NAME: FIND_CUSTOMER_ORDER -- -- PURPOSE: Used for the Webui. Represents the one to one relationship -- between Mfg_ORDMAST and Mfg_SUBORD. Also joins Mfg_CUSTMAST and -- MFG_SHIPTO for addresses, as well as Mfg_TXT using Mfg_DESCTXT_Lnk. -- Filtered on omtype = \'C\' to retrieve only customer orders. -------------------------------------------------------------------------------- if objectproperty(object_id(\'FIND_CUSTOMER_ORDER\'), \'IsView\') = 1 drop view FIND_CUSTOMER_ORDER go create view FIND_CUSTOMER_ORDER as select o.OMBILLTO ,o.OMON ,dbo.FSFORMATDATE(b.OMDATE, SUBSTRING(p.CLBODY,1,1)) OMDATE ,b.OMDATE OMDATEFINDERSORT ,o.OMCOMCD ,o.OMBPSID ,o.OMCNTCUR ,c.CMCURCOD ,c.CMCUAD1 ,c.CMCUAD2 ,c.CMCUCITY ,c.CMCUCNTR ,c.CMCUSTAT ,c.CMCUZIP ,o.OMVCWID ,c.CMCUNAME ,o.OMCUSREF ,dbo.FSFORMATDATE(o.OMDLVYDT, SUBSTRING(p.CLBODY,1,1)) OMDLVYDT ,o.OMDLVYDT OMDLVYDTFINDERSORT ,CASE o.OMCNTCUR WHEN \'L\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMDISTOT, 14, convert(int, m.CLBODY))))) WHEN \'R\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMDISTOT, 14, convert(int, q.CURRPRE))))) WHEN \'F\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMDISTOT, 14, convert(int, q.CURRPRE))))) End As OMDISTOT ,b.OMDISTOT OMDISTOTFINDERSORT ,o.OMDSREG ,o.OMDSZONE ,o.OMENDUSR ,b.OMCURRAT ,b.OMCURRT9 ,b.OMEXOP ,b.OMEXOP9 ,dbo.FSFORMATDATE(o.OMEXPDT, SUBSTRING(p.CLBODY,1,1)) OMEXPDT ,o.OMEXPDT OMEXPDTFINDERSORT ,o.OMFOB ,CASE o.OMCNTCUR WHEN \'L\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMFRTCST, 14, convert(int, m.CLBODY))))) WHEN \'R\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMFRTCST, 14, convert(int, q.CURRPRE))))) WHEN \'F\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMFRTCST, 14, convert(int, q.CURRPRE))))) End As OMFRTCST ,b.OMFRTCST OMFRTCSTFINDERSORT ,o.OMFRTCD ,CASE o.OMCNTCUR WHEN \'L\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMGROSS, 14, convert(int, m.CLBODY))))) WHEN \'R\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMGROSS, 14, convert(int, q.CURRPRE))))) WHEN \'F\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMGROSS, 14, convert(int, q.CURRPRE))))) End As OMGROSS ,b.OMGROSS OMGROSSFINDERSORT ,o.OMPAYCD ,o.OMHOLD ,CASE o.OMCNTCUR WHEN \'L\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMBOOK, 14, convert(int, m.CLBODY))))) WHEN \'R\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMBOOK, 14, convert(int, q.CURRPRE))))) WHEN \'F\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMBOOK, 14, convert(int, q.CURRPRE))))) End As OMBOOK ,b.OMBOOK OMBOOKFINDERSORT ,o.OMPRIOR ,b.OMORDST ,convert(nchar, LTRIM(RTRIM(str(o.OMTRADE, 7, 1)))) OMTRADE ,o.OMTRADE OMTRADEFINDERSORT ,o.OMPRPOST ,o.OMREGION ,o.OMSCAT1 ,o.OMSCAT2 ,s.STAD1 ,s.STAD2 ,s.STCITY ,s.STCNTR ,o.OMSTID ,o.OMSHPCUS ,s.STNAME ,s.STSTAT ,s.STZIP ,o.OMVIA ,o.OMMLTITM ,o.OMMLTSHP ,o.OMPARITM ,o.OMTMPLFG ,convert(nchar,LTRIM(RTRIM(str(o.OMTRMPC1, 7, 1)))) OMTRMPC1 ,o.OMTRMPC1 OMTRMPC1FINDERSORT ,o.OMTRMDA1 ,convert(nchar,LTRIM(RTRIM(str(o.OMTRMPC2, 7, 1)))) OMTRMPC2 ,o.OMTRMPC2 OMTRMPC2FINDERSORT ,o.OMTRMDA2 ,convert(nchar,LTRIM(RTRIM(str(o.OMTRMPC3, 7, 1)))) OMTRMPC3 ,o.OMTRMPC3 OMTRMPC3FINDERSORT ,o.OMTRMDA3 ,convert(nchar,LTRIM(RTRIM(str(o.OMTRMPC4, 7, 1)))) OMTRMPC4 ,o.OMTRMPC4 OMTRMPC4FINDERSORT ,o.OMTRMDA4 ,o.OMTRMNET ,o.OMPAYWEN ,o.OMCUTOFF ,o.OMDELAY ,o.OMPAYHOW ,t.TXT1 ,t.TXT2 ,t.TXT3 ,t.TXT4 ,t.TXID ,CASE o.OMCNTCUR WHEN \'L\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMDOLVAL, 14, convert(int, m.CLBODY))))) WHEN \'R\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMDOLVAL, 14, convert(int, q.CURRPRE))))) WHEN \'F\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMDOLVAL, 14, convert(int, q.CURRPRE))))) End As OMDOLVAL ,b.OMDOLVAL OMDOLVALFINDERSORT ,CASE o.OMCNTCUR WHEN \'L\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMORDTOT, 14, convert(int, q.CURRPRE))))) WHEN \'R\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMORDTOT, 14, convert(int,m.CLBODY ))))) WHEN \'F\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMORDTOT, 14, convert(int, m.CLBODY))))) End As OMORDTOT ,b.OMORDTOT OMORDTOTFINDERSORT from Mfg_ORDMAST o left join Mfg_SUBORD b on (b._ORDSUB_OwnRow = o._Row) left join Mfg_CUSTMAST c on (o.OMVCWID = c.CMCUSTID) left join Mfg_SHIPTO s on (o._ORDSADD_OwnRow = s._Row) left join Mfg_DESCTXT_Lnk d on (d._OwnRec = 29 and d._OwnRow = o._Row) left join Mfg_TXT t on (t._Row = d._MbrRow) join Mfg_PARAMETR m on m.CLTYPE = \'DN\' and m.CLID = \'PREC\' join Mfg_PARAMETR p on p.CLTYPE = \'CR\' and p.CLID = \'CF01\' LEFT OUTER JOIN dbo.Mfg_CURRDEF q ON c.CMCURCOD = q.CURRCODE where o.OMTYPE = \'C\' with check option go -------------------------------------------------------------------------------- -- NAME: FIND_ITEM -- -- PURPOSE: Used for the Webui. Represents the one to one relationship -- between Mfg_ITMMAST and Mfg_SUBITEM. Also joins Mfg_LOTITM which is -- a one to one relationship with Mfg_ITMMAST. No filtering done, this -- view includes all rows. -------------------------------------------------------------------------------- if objectproperty(object_id(\'FIND_ITEM\'), \'IsView\') = 1 drop view FIND_ITEM go create view FIND_ITEM as select i.IMCGACNO ,i.IMACCTNO ,i.IMSACNO ,s.IMALLOC ,i.IMATPPOL ,i.IMPRICE ,i.IMBUYR ,i.IMCSTFAM ,s.IMCYLDAT ,i.IMDECPRE ,i.IMDEMFNC ,i.IMDRWG ,i.IMECCN ,i.IMFGROUP ,i.IMFAMILY ,i.IMFORCON ,i.IMFORCPD ,i.IMGATEWC ,i.IMGRPTCH ,i.IMDOC ,i.IMINSPCD ,i.IMPN ,i.IMCLAS1 ,i.IMCLAS2 ,i.IMCLAS3 ,i.IMCLAS4 ,i.IMCLAS5 ,i.IMCLAS6 ,i.IMCLAS7 ,i.IMCLAS8 ,i.IMDESC ,i.IMPCSPER ,i.IMPKGTYP ,i.IMREF1 ,i.IMREF2 ,i.IMLTL ,i.IMTL ,i.IMREF ,i.IMCUBEFT ,i.IMWEIGHT ,i.IMLTFXD ,i.IMLTQA ,i.IMLTVAR ,i.IMLOTDAY ,i.IMLOTMN ,i.IMLOTML ,i.IMLOTSZ ,l.IMALLPOL ,l.IMLOTPOL ,l.IMAVLDAY ,l.IMLOTNUM ,l.IMLOTDEF ,l.IMFIFOPL ,l.IMLSTLOT ,l.IMLSTSER ,l.IMLOTMSK ,l.IMMATCD ,l.IMRETEST ,l.IMSERDEF ,l.IMSERMSK ,i.IMSERIAL ,l.IMSHELF ,i.IMPOTENT ,i.IMLOTTR ,s.IMLLCD ,i.IMMKBY ,i.IMNMF ,i.IMNMFSC ,i.IMORDPOL ,i.IMPLNR ,i.IMPLNFNC ,i.IMPLNPOL ,i.IMBIN ,i.IMSTKRM ,r.PLPRDLN ,r.PLDESC ,s.IMREPLN ,dbo.FSFORMATDATE(i.IMLRPLDT, SUBSTRING(p.CLBODY,1,1)) IMLRPLDT ,i.IMLRPLDT IMLRPLDTFINDERSORT ,i.IMREV ,i.IMSS ,i.IMCORP ,i.IMCSTSU ,i.IMSTAT ,i.IMTYPE ,i.IMUM ,i.IMUPCCD ,i.IMYIELD from Mfg_ITMMAST i left join Mfg_SUBITEM s on (s._ITMSUB_OwnRow = i._Row) left join Mfg_LOTITM l on (l._ITMLOT_OwnRow = i._Row) left join Mfg_PRODLINE r on (i._PRODIT_OwnRow = r._Row) join Mfg_PARAMETR p on p.CLTYPE = \'CR\' and p.CLID = \'CF01\' with check option go -------------------------------------------------------------------------------- -- NAME: FIND_LOT -- -- PURPOSE: Used for the Webui. Basically includes all of the fields from -- Mfg_INCONTRL table. Joins to the Mfg_ITMMAST table to get the -- corresponding Item Number for this Lot. Also joins to the Mfg_LOTITM -- table to get the corresponding Lot Number Mask. -- No filtering is done, this view includes all rows. -------------------------------------------------------------------------------- if objectproperty(object_id(\'FIND_LOT\'), \'IsView\') = 1 drop view FIND_LOT go create view FIND_LOT as select c.INCTLNUM ,i.IMPN ,c.INDESC ,c.INREV ,l.IMLOTMSK ,c.INVENLOT ,c.INUSER1 ,c.INUSER2 ,c.INUSER3 ,c.INUSER4 ,c.INUSER5 ,c.INUSER6 ,c.INUSER7 ,c.INUSER8 ,c.INUSER9 ,c.INUSER10 ,c.INUSER11 ,c.INUSER12 from Mfg_INCONTRL c left join Mfg_ITMMAST i on (c._ITMINC_OwnRow = i._Row) left join Mfg_LOTITM l on (i._Row = l._ITMLOT_OwnRow) with check option go -------------------------------------------------------------------------------- -- NAME: FIND_MANUFACTURING_ORDER -- -- PURPOSE: Used for the Webui. Represents the one to one relationship -- between Mfg_ORDMAST and Mfg_SUBORD. Also joins Mfg_TXT using -- Mfg_DESCTXT_Lnk. Filtered on omtype = \'M\' to retrieve only -- manufacturing orders. -------------------------------------------------------------------------------- if objectproperty(object_id(\'FIND_MANUFACTURING_ORDER\'), \'IsView\') = 1 drop view FIND_MANUFACTURING_ORDER go create view FIND_MANUFACTURING_ORDER as select o.OMFOB ,o.OMVCWID ,o.OMON ,dbo.FSFORMATDATE(b.OMDATE, SUBSTRING(p.CLBODY,1,1)) OMDATE ,b.OMDATE OMDATEFINDERSORT ,b.OMORDST ,o.OMBPSID ,t.TXT1 ,t.TXT2 ,t.TXT3 ,t.TXT4 ,t.TXID from Mfg_ORDMAST o left join Mfg_SUBORD b on (b._ORDSUB_OwnRow = o._Row) left join Mfg_DESCTXT_Lnk d on (d._OwnRec = 29 and d._OwnRow = o._Row) join Mfg_PARAMETR p on p.CLTYPE = \'CR\' and p.CLID = \'CF01\' left join Mfg_TXT t on (t._Row = d._MbrRow) where o.OMTYPE = \'M\' with check option go -------------------------------------------------------------------------------- -- NAME: FIND_PURCHASE_ORDER -- -- PURPOSE: Used for the Webui. Represents the one to one relationship -- between Mfg_ORDMAST and Mfg_SUBORD. Also joins Mfg_CUSTMAST and -- MFG_SHIPTO for addresses, as well as Mfg_TXT using Mfg_DESCTXT_Lnk. -- Filtered on omtype = \'P\' to retrieve only purchase orders. -------------------------------------------------------------------------------- if objectproperty(object_id(\'FIND_PURCHASE_ORDER\'), \'IsView\') = 1 drop view FIND_PURCHASE_ORDER go create view FIND_PURCHASE_ORDER as select o.OMSUBTYP ,o.OMBPSID ,o.OMCNTCUR ,v.VMCURCOD ,b.OMCURRAT ,b.OMCURRT9 ,b.OMEXOP ,b.OMEXOP9 ,o.OMFOB ,o.OMPHONE ,o.OMCNTACT ,b.OMORDST ,dbo.FSFORMATDATE(o.OMORGDTE, SUBSTRING(p.CLBODY,1,1)) OMORGDTE ,o.OMORGDTE OMORGDTEFINDERSORT ,o.OMON ,o.OMREV ,o.OMPRTPO ,dbo.FSFORMATDATE(b.OMDATE, SUBSTRING(p.CLBODY,1,1)) OMDATE ,b.OMDATE OMDATEFINDERSORT ,o.OMS2AD1 ,o.OMS2AD2 ,o.OMS2CITY ,o.OMS2CNTR ,o.OMS2NAME ,o.OMS2STAT ,o.OMS2ZIP ,convert(nchar,LTRIM(RTRIM(str(o.OMTRMPC1, 7, 1)))) OMTRMPC1 ,o.OMTRMPC1 OMTRMPC1FINDERSORT ,o.OMTRMDA1 ,convert(nchar,LTRIM(RTRIM(str(o.OMTRMPC2, 7, 1)))) OMTRMPC2 ,o.OMTRMPC2 OMTRMPC2FINDERSORT ,o.OMTRMDA2 ,o.OMTRMNET ,o.OMPAYWEN ,o.OMCUTOFF ,o.OMDELAY ,o.OMPAYHOW ,t.TXT1 ,t.TXT2 ,t.TXT3 ,t.TXT4 ,t.TXID ,CASE o.OMCNTCUR WHEN \'L\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMDOLVAL, 14, convert(int, m.CLBODY))))) WHEN \'R\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMDOLVAL, 14, convert(int, m.CLBODY))))) WHEN \'F\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMDOLVAL, 14, convert(int, q.CURRPRE))))) End As OMDOLVAL ,b.OMDOLVAL OMDOLVALFINDERSORT ,CASE o.OMCNTCUR WHEN \'F\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMORDTOT, 14, convert(int, m.CLBODY))))) WHEN \'R\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMORDTOT, 14, convert(int, q.CURRPRE))))) WHEN \'L\' THEN convert(nchar, LTRIM(RTRIM(str(b.OMORDTOT, 14, convert(int, q.CURRPRE))))) End As OMORDTOT ,b.OMORDTOT OMORDTOTFINDERSORT ,o.OMVIA ,v.VMVENID ,v.VMVENAME from Mfg_ORDMAST o left join Mfg_SUBORD b on (b._ORDSUB_OwnRow = o._Row) left join Mfg_VENMAST v on (o.OMVCWID = v.VMVENID) left join Mfg_DESCTXT_Lnk d on (d._OwnRec = 29 and d._OwnRow = o._Row) left join Mfg_TXT t on (t._Row = d._MbrRow) join Mfg_PARAMETR m on m.CLTYPE = \'DN\' and m.CLID = \'PREC\' join Mfg_PARAMETR p on p.CLTYPE = \'CR\' and p.CLID = \'CF01\' LEFT OUTER JOIN dbo.Mfg_CURRDEF q ON v.VMCURCOD = q.CURRCODE where o.OMTYPE = \'P\' with check option go -------------------------------------------------------------------------------- -- NAME: FIND_VENDOR -- -- PURPOSE: Used for the Webui. Represents the one to one relationship -- between Mfg_VENMAST and Mfg_SUBVEN. Also joins Mfg_TXT using -- Mfg_DESCTXT_Lnk and Mfg_PLNRTXT_Lnk. No filtering done, -- this view includes all rows. -------------------------------------------------------------------------------- if objectproperty(object_id(\'FIND_VENDOR\'), \'IsView\') = 1 drop view FIND_VENDOR go create view FIND_VENDOR as select v.VMTXFG ,v.VMTIN ,v.VMACTCNT ,v.VMACFONE ,convert(nchar, LTRIM(RTRIM(str((s.VMCURR + s.VMFUT + s.VMFUT1 + s.VMFUT2 + s.VMFUT3), 14,convert(int, m.CLBODY))))) VMCURR ,s.VMCURR + s.VMFUT + s.VMFUT1 + s.VMFUT2 + s.VMFUT3 VMCURRFINDERSORT ,v.VMEMAIL ,convert(nchar, LTRIM(RTRIM(str(s.VMFUT, 14 , convert(int, m.CLBODY))))) VMFUT ,s.VMFUT VMFUTFINDERSORT ,convert(nchar, LTRIM(RTRIM(str(s.VMFUT1, 14 ,convert(int, m.CLBODY))))) VMFUT1 ,s.VMFUT1 VMFUT1FINDERSORT ,convert(nchar, LTRIM(RTRIM(str(s.VMFUT2, 14 ,convert(int, m.CLBODY))))) VMFUT2 ,s.VMFUT2 VMFUT2FINDERSORT ,convert(nchar, LTRIM(RTRIM(str(s.VMFUT3, 14 ,convert(int, m.CLBODY))))) VMFUT3 ,s.VMFUT3 VMFUT3FINDERSORT ,convert(nchar, LTRIM(RTRIM(str(s.VMHOLD, 14 ,convert(int, m.CLBODY))))) VMHOLD ,s.VMHOLD VMHOLDFINDERSORT ,convert(nchar, LTRIM(RTRIM(str(s.VMPAST1, 14 ,convert(int, m.CLBODY))))) VMPAST1 ,s.VMPAST1 VMPAST1FINDERSORT ,convert(nchar, LTRIM(RTRIM(str(s.VMPAST2, 14, convert(int, m.CLBODY))))) VMPAST2 ,s.VMPAST2 VMPAST2FINDERSORT ,convert(nchar, LTRIM(RTRIM(str(s.VMPAST3, 14, convert(int, m.CLBODY))))) VMPAST3 ,s.VMPAST3 VMPAST3FINDERSORT ,convert(nchar, LTRIM(RTRIM(str(s.VMCURR + s.VMFUT + s.VMFUT1 + s.VMFUT2 + s.VMFUT3 + s.VMPAST1 + s.VMPAST2 + s.VMPAST3 + VMHOLD - s.VMUNAPP, 14, convert(int, m.CLBODY))))) VMTOTAMT ,s.VMCURR + s.VMFUT + s.VMFUT1 + s.VMFUT2 + s.VMFUT3 + s.VMPAST1 + s.VMPAST2 + s.VMPAST3 + VMHOLD - s.VMUNAPP VMTOTAMTFINDERSORT ,convert(nchar, LTRIM(RTRIM(str(s.VMUNAPP, 14, convert(int, m.CLBODY))))) VMUNAPP ,s.VMUNAPP VMUNAPPFINDERSORT ,v.VMBANKAC ,v.VMBANKNM ,v.VMBANKRT ,v.VMCNTCUR ,v.VMCURCOD ,v.VMDBSIC ,dbo.FSFORMATDATE(v.VMDBDATE, SUBSTRING(a.CLBODY,1,1)) VMDBDATE ,v.VMDBDATE VMDBDATEFINDERSORT ,v.VMDBRATE ,CASE v.VMDBID WHEN \'\' THEN \'\' ELSE SUBSTRING(v.VMDBID, 1,2) + \'-\' + SUBSTRING(v.VMDBID,3,3) + \'-\' + RIGHT(v.VMDBID,5) End As VMDBID ,dbo.FSFORMATDATE(s.VMLSTINV, SUBSTRING(a.CLBODY,1,1)) VMLSTINV ,s.VMLSTINV VMLSTINVFINDERSORT ,dbo.FSFORMATDATE(v.VMLSTMNT, SUBSTRING(a.CLBODY,1,1)) VMLSTMNT ,v.VMLSTMNT VMLSTMNTFINDERSORT ,dbo.FSFORMATDATE(s.VMLSTPMT, SUBSTRING(a.CLBODY,1,1)) VMLSTPMT ,s.VMLSTPMT VMLSTPMTFINDERSORT ,dbo.FSFORMATDATE(s.VMRCPDT, SUBSTRING(a.CLBODY,1,1)) VMRCPDT ,s.VMRCPDT VMRCPDTFINDERSORT ,convert(nchar, LTRIM(RTRIM(str(s.VMORD, 14, convert(int, m.CLBODY))))) VMORD ,s.VMORD VMORDFINDERSORT ,convert(nchar, LTRIM(RTRIM(str(s.VMORD2, 14, convert(int, m.CLBODY))))) VMORD2 ,s.VMORD2 VMORD2FINDERSORT ,v.VMP2AD1 ,v.VMP2AD2 ,v.VMP2CITY ,v.VMP2CNTR ,v.VMP2NAME ,v.VMP2NAM2 ,v.VMP2STAT ,v.VMP2ZIP ,v.VMPAYCOD ,convert(nchar, LTRIM(RTRIM(str(s.VMYTDPD, 14, convert(int, m.CLBODY))))) VMYTDPD ,s.VMYTDPD VMYTDPDFINDERSORT ,convert(nchar, LTRIM(RTRIM(str (s.VMYTPD2, 14, convert(int, m.CLBODY))))) VMYTPD2 ,s.VMYTPD2 VMYTPD2FINDERSORT ,v.VMUNVAP ,v.VMVCHAP ,v.VMPPVAR ,v.VMPOINVR ,v.VMPOPRNT ,v.VMPRTLNG ,v.VMPRTFMT ,v.VMPAYHOW ,convert(nchar, LTRIM(RTRIM(str(s.VMYTDPO, 14, convert(int, m.CLBODY))))) VMYTDPO ,s.VMYTDPO VMYTDPOFINDERSORT ,dbo.FSFORMATDATE(v.VMVADATE, SUBSTRING(a.CLBODY,1,1)) VMVADATE ,v.VMVADATE VMVADATEFINDERSORT ,v.VMSTATUS ,convert(nchar, LTRIM(RTRIM(str(s.VMYTPO2, 14, convert(int, m.CLBODY))))) VMYTPO2 ,s.VMYTPO2 VMYTPO2FINDERSORT ,convert(nchar, LTRIM(RTRIM(str(v.VMTRMPC1, 7, 1)))) VMTRMPC1 ,v.VMTRMPC1 VMTRMPC1FINDERSORT ,v.VMTRMDA1 ,convert(nchar,LTRIM(RTRIM(str(v.VMTRMPC2, 7, 1)))) VMTRMPC2 ,v.VMTRMPC2 VMTRMPC2FINDERSORT ,v.VMTRMDA2 ,v.VMTRMNET ,v.VMCUTOFF ,v.VMPAYWEN ,v.VMDELAY ,t.TXT1 TEXT_LINE1 ,t.TXT2 TEXT_LINE2 ,t.TXT3 TEXT_LINE3 ,t.TXT4 TEXT_LINE4 ,t.TXID TEXT_NO ,c.TXT1 CONF_TEXT1 ,c.TXT2 CONF_TEXT2 ,c.TXT3 CONF_TEXT3 ,c.TXT4 CONF_TEXT4 ,c.TXID CONF_TEXTNO ,v.VMVEAD1 ,v.VMVEAD2 ,v.VMVECITY ,v.VMVECNTR ,v.VMVESTAT ,v.VMVEZIP ,v.VMSTAT ,v.VMCLASS ,v.VMCLASS$2 ,v.VMCLASS$3 ,v.VMCLASS$4 ,v.VMCLASS$5 ,v.VMCLASS$6 ,v.VMCLASL ,v.VMCLASL$2 ,v.VMCONTCT ,v.VMFAX ,v.VMVENID ,v.VMVENAME ,v.VMPHONE ,convert(nchar, LTRIM(RTRIM(str(s.VMYTDINV, 14, convert(int, m.CLBODY))))) VMYTDINV ,s.VMYTDINV VMYTDINVFINDERSORT from Mfg_VENMAST v left join Mfg_SUBVEN s on (s._VENSUB_OwnRow = v._Row) left join Mfg_DESCTXT_Lnk d on (d._OwnRec = 59 and d._OwnRow = v._Row) left join Mfg_TXT t on (t._Row = d._MbrRow) left join Mfg_PLNRTXT_Lnk p on (p._OwnRec = 59 and p._OwnRow = v._Row) left join Mfg_TXT c on (c._Row = p._MbrRow) join Mfg_PARAMETR m on m.CLTYPE = \'DN\' and m.CLID = \'PREC\' join Mfg_PARAMETR a on a.CLTYPE = \'CR\' and a.CLID = \'CF01\' with check option go -------------------------------------------------------------------------------- -- NAME: GL_MASTER -- -- PURPOSE: Represents the one to one relationship between Fin_GLMASTER and -- Fin_SUBMAST. No filtering done, this view includes all rows. -------------------------------------------------------------------------------- if objectproperty(object_id(\'GL_MASTER\'), \'IsView\') = 1 drop view GL_MASTER go create view GL_MASTER as select g.GMACTNO ,g.GMACTCAT ,g.GMACTYPE ,g.GMACTBAL ,g.GMPRDDB ,g.GMPRDCR ,g.GMCNETDB ,g.GMCNETCR ,g.GMPNETDB ,g.GMPNETCR ,g.GMLSTPST ,g._Row GLMASTER_Row ,g._GLORGMST_OwnRow ,g._GLVALMST_OwnRow ,g._SYSNET_OwnRow ,g._Ts GLMASTER_Ts ,s.GMCACTNO ,s.GMACTDSC ,s.GMORGDSC ,s.GMCLOFLG ,s.GMICTFLG ,s.GMBALLMT ,s.GMTRNLMT ,s.GMMDATA ,s.GMMDATA$2 ,s.GMMDATA$3 ,s.GMMDATA$4 ,s.GMMDATA$5 ,s.GMMDATA$6 ,s.GMMAMNT ,s.GMMAMNT$2 ,s.GMMAMNT$3 ,s.GMMAMNT$4 ,s.GMMAMNT$5 ,s.GMMAMNT$6 ,s.GMLSTMNT ,s.GMACTUSR ,s.GMOIAFLG ,s.GMAIFLAG ,s._Row SUBMAST_Row ,s._MASTSUB_OwnRow ,s._Ts SUBMAST_Ts from Fin_GLMASTER g left join Fin_SUBMAST s on (s._MASTSUB_OwnRow = g._Row) with check option go -------------------------------------------------------------------------------- -- NAME: ITEM_MASTER -- -- PURPOSE: Represents the one to one relationship between Mfg_ITMMAST and -- Mfg_SUBITEM. No filtering done, this view includes all rows. -------------------------------------------------------------------------------- if objectproperty(object_id(\'ITEM_MASTER\'), \'IsView\') = 1 drop view ITEM_MASTER go create view ITEM_MASTER as select i.IMPN ,i.IMDESC ,i.IMTYPE ,i.IMUM ,i.IMCLAS1 ,i.IMCLAS2 ,i.IMCLAS3 ,i.IMCLAS4 ,i.IMCLAS5 ,i.IMCLAS6 ,i.IMCLAS7 ,i.IMCLAS8 ,i.IMMKBY ,i.IMSTAT ,i.IMDRWG ,i.IMREV ,i.IMPLNR ,i.IMBUYR ,i.IMACCTNO ,i.IMCSTSU ,i.IMPRICE ,i.IMORDPOL ,i.IMYIELD ,i.IMDECPRE ,i.IMLOTSZ ,i.IMSS ,i.IMLTFXD ,i.IMLTVAR ,i.IMLTQA ,i.IMFORCON ,i.IMPLNPOL ,i.IMLOTMN ,i.IMLOTML ,i.IMLOTDAY ,i.IMSTKRM ,i.IMBIN ,i.IMCORP ,i.IMINSPCD ,i.IMATPPOL ,i.IMDEMFNC ,i.IMPLNFNC ,i.IMFORCPD ,i.IMLRPLDT ,i.IMGATEWC ,i.IMLTL ,i.IMTL ,i.IMREF ,i.IMGRPTCH ,i.IMFAMILY ,i.IMFGROUP ,i.IMNMF ,i.IMNMFSC ,i.IMWEIGHT ,i.IMCUBEFT ,i.IMSACNO ,i.IMCGACNO ,i.IMCSTFAM ,i.IMUPCCD ,i.IMPKGTYP ,i.IMPCSPER ,i.IMREF1 ,i.IMREF2 ,i.IMPLNDAT ,i.IMLOTTR ,i.IMSERIAL ,i.IMPOTENT ,i.IMFIFO ,i.IMLTOVL ,i.IMLTFXD2 ,i.IMVATPRD ,i.IMCURCOD ,i.IMFORAMT ,i.IMECCN ,i.IMDOC ,i.IMCURCTL ,i.IMCURRAT ,i.IMPLNVIS ,i.IMXRACNO ,i.IMXCACNO ,i.IMWEACNO ,i._Row ITMMAST_Row ,i._Ts ITMMAST_Ts ,s.IMLSTVEN ,s.IMLLCD ,s.IMOHQTY ,s.IMIIQTY ,s.IMHOQTY ,s.IMSHQTY ,s.IMWIPQTY ,s.IMALLOC ,s.IMREPLN ,s.IMYTDRCV ,s.IMYTDDOL ,s.IMOO ,s.IMATPQTY ,s.IMEXTWIP ,s.IMOHACT ,s.IMIIACT ,s.IMYTDISS ,s.IMYTDSHP ,s.IMCYLDAT ,s._Row SUBITEM_Row ,s._ITMSUB_OwnRow ,s._Ts SUBITEM_Ts from Mfg_ITMMAST i left join Mfg_SUBITEM s on (s._ITMSUB_OwnRow = i._Row) with check option go -------------------------------------------------------------------------------- -- NAME: ORDER_MASTER -- -- PURPOSE: Represents the one to one relationship between Mfg_ORDMAST and -- Mfg_SUBORD. No filtering done, this view includes all rows. -------------------------------------------------------------------------------- if objectproperty(object_id(\'ORDER_MASTER\'), \'IsView\') = 1 drop view ORDER_MASTER go create view ORDER_MASTER as select o.OMTYPE ,o.OMON ,o.OMVCWID ,o.OMBPSID ,o.OMVIA ,o.OMTRMPC1 ,o.OMTRMDA1 ,o.OMTRMPC2 ,o.OMTRMDA2 ,o.OMTRMNET ,o.OMTRADE ,o.OMFOB ,o.OMFRTCD ,o.OMPAYCD ,o.OMCUSREF ,o.OMS2NAME ,o.OMS2AD1 ,o.OMS2AD2 ,o.OMS2CITY ,o.OMS2STAT ,o.OMS2CNTR ,o.OMS2ZIP ,o.OMCOMCD ,o.OMSCAT1 ,o.OMSCAT2 ,o.OMEXMPT ,o.OMTXCERT ,o.OMFTAXCD ,o.OMSTAXCD ,o.OMCTAXCD ,o.OMMTAXCD ,o.OMSTID ,o.OMCLASS ,o.OMCLASS$2 ,o.OMCLASS$3 ,o.OMCLASS$4 ,o.OMCLASS$5 ,o.OMCLASS$6 ,o.OMCLASL ,o.OMCLASL$2 ,o.OMCNTCUR ,o.OMPAYHOW ,o.OMPAYWEN ,o.OMCUTOFF ,o.OMDELAY ,o.OMREV ,o.OMORGDTE ,o.OMSUBTYP ,o.OMPRTPO ,o.OMCNTACT ,o.OMPHONE ,o.OMOCURR ,o.OMOCURAT ,o.OMLTRCRD ,o.OMORGINS ,o.OMORGINN ,o.OMEDIOUT ,o.OMEDIACK ,o.OMEDIIN ,o.OMRELNO ,o.OMHOLD ,o.OMDLVYDT ,o.OMEXPDT ,o.OMENDUSR ,o.OMBILLTO ,o.OMPRIOR ,o.OMSEXMPT ,o.OMCEXMPT ,o.OMMEXMPT ,o.OMOEXMPT ,o.OMOTAXCD ,o.OMTMPLFG ,o.OMTRMPC3 ,o.OMTRMDA3 ,o.OMTRMPC4 ,o.OMTRMDA4 ,o.OMSHPCUS ,o.OMREGION ,o.OMDSZONE ,o.OMDSREG ,o.OMSTXCER ,o.OMCTXCER ,o.OMMTXCER ,o.OMOTXCER ,o.OMMLTSHP ,o.OMPARITM ,o.OMMLTITM ,o.OMDFTSTA ,o.OMPRPOST ,o._Row ORDMAST_Row ,o._CTRTORD_OwnRow ,o._ORDSADD_OwnRow ,o._SYSORDPR_OwnRec ,o._SYSTPORD_OwnRec ,o._VCORD_OwnRow ,o._VCORD_OwnRec ,o._Ts ORDMAST_Ts ,s.OMDATE ,s.OMORDST ,s.OMCLSCNT ,s.OMDOLVAL ,s.OMRCPTFG ,s.LINENUM ,s.OMMU1 ,s.OMMU2 ,s.OMDOLVL1 ,s.OMCURRAT ,s.OMTAXAMT ,s.OMFRTCST ,s.OMGROSS ,s.OMDISTOT ,s.OMCURAT2 ,s._Row SUBORD_Row ,s._ORDSUB_OwnRow ,s._Ts SUBORD_Ts from Mfg_ORDMAST o left join Mfg_SUBORD s on (s._ORDSUB_OwnRow = o._Row) with check option go if exists (select * from sysobjects where id = object_id(\'dbo.RBP_CO\') and sysstat & 0xf = 2) drop view dbo.RBP_CO GO create view RBP_CO as ( select Mfg_ORDMAST.OMTYPE ORDER_TYPE ,Mfg_ORDMAST.OMON CO_NUMBER ,Mfg_ORDMAST.OMVCWID CUST_ID ,Mfg_ORDMAST.OMBPSID CSR ,Mfg_SUBORD.OMORDST ORD_STATUS ,Mfg_SUBORD.OMDATE CO_RV_DATE ,Mfg_ORDMAST.OMSTID SHIP_TO_ID ,Mfg_SHIPTO.STNAME SHIP_TO_NM ,Mfg_SHIPTO.STAD1 SHIP_ADDR1 ,Mfg_SHIPTO.STAD2 SHIP_ADDR2 ,Mfg_SHIPTO.STCITY SHIP_CITY ,Mfg_SHIPTO.STSTAT SHIP_STATE ,Mfg_SHIPTO.STZIP SHIP_ZIP ,Mfg_SHIPTO.STCNTR SHIP_CNTRY ,Mfg_SHIPTO.STEXMPT TAX_EXEMPT ,\'C\' CUST_POTYP ,Mfg_ORDMAST.OMCUSREF CUST_PO_NO ,Mfg_ORDMAST.OMVIA SHIP_VIA ,Mfg_ORDMAST.OMFRTCD FRT_CHG_CD ,Mfg_ORDMAST.OMFOB FOB_POINT ,Mfg_ORDMAST.OMPAYCD IVC_COD ,Mfg_ORDMAST.OMTRMPC1 DISC_PCNT1 ,Mfg_ORDMAST.OMTRMDA1 DISC_DAYS1 ,Mfg_ORDMAST.OMTRMPC2 DISC_PCNT2 ,Mfg_ORDMAST.OMTRMDA2 DISC_DAYS2 ,Mfg_ORDMAST.OMTRMNET NET_DAYS ,Mfg_SUBORD.OMDOLVAL CO_AMOUNT ,Mfg_ORDMAST.OMTRADE PRICE_FCTR ,Mfg_ORDMAST.OMCOMCD COM_CODE ,Mfg_ORDMAST.OMSCAT1 SALES_CAT1 ,Mfg_ORDMAST.OMSCAT2 SALES_CAT2 ,Mfg_ORDMAST.OMEXMPT EXEMPT ,Mfg_ORDMAST.OMTXCERT CERT_NO ,Mfg_ORDMAST.OMFTAXCD ORD_TAX_CODE_F ,Mfg_ORDMAST.OMSTAXCD ORD_TAX_CODE_S ,Mfg_ORDMAST.OMCTAXCD ORD_TAX_CODE_C ,Mfg_ORDMAST.OMMTAXCD ORD_TAX_CODE_M ,Mfg_SUBORD.OMCLSCNT CLS_LN_CT ,Mfg_ORDMAST.OMHOLD CREDHOLD ,Mfg_ORDMAST.OMDLVYDT SHIPDATE ,Mfg_ORDMAST.OMEXPDT ORD_EXP_DATE ,Mfg_ORDMAST.OMENDUSR END_USER ,Mfg_ORDMAST.OMBILLTO BILLTO ,Mfg_ORDMAST.OMPRIOR PRIORITY ,Mfg_ORDMAST.OMSEXMPT S_EXEMPT ,Mfg_ORDMAST.OMCEXMPT C_EXEMPT ,Mfg_ORDMAST.OMMEXMPT M_EXEMPT ,Mfg_ORDMAST.OMOEXMPT O_EXEMPT ,Mfg_ORDMAST.OMOTAXCD O_TAX_CD ,Mfg_ORDMAST.OMTMPLFG FLG_TMPT ,Mfg_ORDMAST.OMTRMPC3 DISC_PCNT3 ,Mfg_ORDMAST.OMTRMDA3 DISC_DAYS3 ,Mfg_ORDMAST.OMTRMPC4 DISC_PCNT4 ,Mfg_ORDMAST.OMTRMDA4 DISC_DAYS4 ,Mfg_ORDMAST.OMSHPCUS SHPTO_CUST ,Mfg_ORDMAST.OMREGION REGION ,Mfg_ORDMAST.OMDSZONE DIST_ZONE ,Mfg_ORDMAST.OMDSREG DISTREGION ,Mfg_ORDMAST.OMSTXCER S_TAX_CERT ,Mfg_ORDMAST.OMCTXCER C_TAX_CERT ,Mfg_ORDMAST.OMMTXCER M_TAX_CERT ,Mfg_ORDMAST.OMOTXCER O_TAX_CERT ,Mfg_ORDMAST.OMMLTSHP MULT_SHP_O ,Mfg_ORDMAST.OMPARITM PART_SHP_O ,Mfg_ORDMAST.OMMLTITM MULT_LNSHP ,Mfg_ORDMAST.OMDFTSTA DFLT_STAT ,Mfg_ORDMAST.OMPRPOST RBP_POSTED ,Mfg_SUBORD.OMTAXAMT TOT_TAXAMT ,Mfg_SUBORD.OMFRTCST ORD_FRTCST ,Mfg_SUBORD.OMGROSS GROSS_TOTL ,Mfg_SUBORD.OMDISTOT DISC_TOTAL ,Mfg_SUBORD.OMCURAT2 ALT_CURR ,Mfg_DF.OPSEQN CO_LN_NO ,Mfg_DF.ORDTYP LN_TYPE ,Mfg_ITMMAST.IMPN ITEM ,Mfg_ITMMAST.IMDESC ITEM_DESC ,Mfg_ITMMAST.IMUM UM ,Mfg_ITMMAST.IMREV REV ,Mfg_ITMMAST.IMMKBY MB ,Mfg_ITMMAST.IMTYPE ITEM_TYPE ,Mfg_ITMMAST.IMSTAT ITEM_STAT ,Mfg_ITMMAST.IMLOTTR LT ,Mfg_ITMMAST.IMSERIAL SR ,Mfg_ITMMAST.IMDRWG DRWG ,Mfg_ITMMAST.IMCLAS1 ITEM_CLAS1 ,Mfg_ITMMAST.IMCLAS2 ITEM_CLAS2 ,Mfg_ITMMAST.IMCLAS3 ITEM_CLAS3 ,Mfg_ITMMAST.IMCLAS4 ITEM_CLAS4 ,Mfg_ITMMAST.IMCLAS5 ITEM_CLAS5 ,Mfg_ITMMAST.IMCLAS6 ITEM_CLAS6 ,Mfg_ITMMAST.IMCLAS7 ITEM_CLAS7 ,Mfg_ITMMAST.IMCLAS8 ITEM_CLAS8 ,Mfg_ITMMAST.IMNMF N_MOTR_FRT ,Mfg_ITMMAST.IMNMFSC SUB ,Mfg_ITMMAST.IMLTL ITEM_REF3 ,Mfg_ITMMAST.IMTL ITEM_REF4 ,Mfg_ITMMAST.IMREF ITEM_REF5 ,Mfg_ITMMAST.IMWEIGHT SHIP_WGHT ,Mfg_ITMMAST.IMCUBEFT SHIP_VOL ,Mfg_ITMMAST.IMPKGTYP PKG_TYPE ,Mfg_ITMMAST.IMPCSPER PCS_PER ,Mfg_ITMMAST.IMUPCCD UPC_CODE ,Mfg_ITMMAST.IMREF1 ITEM_REF1 ,Mfg_ITMMAST.IMREF2 ITEM_REF2 ,Mfg_ITMMAST.IMGRPTCH GRP_TCH_CD ,Mfg_ITMMAST.IMFAMILY FMLY_NAME ,Mfg_ITMMAST.IMFGROUP FMLY_GROUP ,Mfg_ITMMAST.IMCSTFAM COST_FMLY ,Mfg_PRICES.PQPRIQUO STD_PRICE ,Mfg_DF.QTYREQ ORDER_QTY ,Mfg_SUBDF.DFPRICE UNIT_PRICE ,Mfg_DF.REQDUEDT REQD_DATE ,Mfg_DF.INSTRTDT RQST_SHIP ,Mfg_SUBDF.DFEXPDAT EXP_DATE ,Mfg_SUBDF.DFORGSHP ORIG_DATE ,Mfg_SUBDF.DFUM CI_UM ,Mfg_SUBDF.DFCUSQTY CUST_QTY ,Mfg_SUBDF.DFCUSPRC CUST_PRICE ,Mfg_SUBDF.DFALTUM ALT_UM ,Mfg_SUBDF.DFALTQTY ALT_QTY ,Mfg_SUBDF.DFALTPRC ALT_PRICE ,Mfg_SUBDF.DFGROPRC GROSS_PRC ,Mfg_DF.ORDSTA LN_STA ,Mfg_DF.DFDMTYP DMND_TYP ,Mfg_DF.QTYISS ISSUED_QTY ,Mfg_DF.OUTNEDDT DT_LST_SHP ,Mfg_SUBDF.DFTRANLT TR_LT ,Mfg_SUBDF.DFSRCFLG SRC_TYPE ,Mfg_SUBDF.DFFTCFLG TAX_CODE_F ,Mfg_SUBDF.DFSTCFLG TAX_CODE_S ,Mfg_SUBDF.DFCTCFLG TAX_CODE_C ,Mfg_SUBDF.DFMTCFLG TAX_CODE_M ,Mfg_SUBDF.DFOTCFLG TAX_CODE_O ,Mfg_SUBDF.DFCOMCD SUBDF_COM_CODE ,Mfg_SUBDF.DFCCFLG OVR ,Mfg_SUBDF.DFTRADE PRICE_ADJ ,Mfg_SUBDF.DFPSRCE PRICE_SRCE ,Mfg_SUBDF.DFGROQTY GROSS_QTY ,Mfg_SUBDF.DFUMCNVT CI_UM_CNVT ,Mfg_SUBDF.DFALTCVT UM_CVRT ,Mfg_SUBDF.DFPARITM PART_SHP_L ,Mfg_SUBDF.DFMLTITM MULT_SHP_L ,Mfg_SUBDF.DFAUTHNO AUTH_NUM ,Mfg_DF.POUID PT_USE ,Mfg_SUBDF.DFSHQTY TOT_SHPPED ,Mfg_DF.DFCUSLIN CUS_PO_LN ,Mfg_CUSTMAST.CMCUNAME CUST_NAME ,Mfg_CUSTMAST.CMCONTCT CUST_CNTCT ,Mfg_CUSTMAST.CMPHONE CUST_PHONE ,Mfg_CUSTMAST.CMCUAD1 CUST_ADDR1 ,Mfg_CUSTMAST.CMCUAD2 CUST_ADDR2 ,Mfg_CUSTMAST.CMCUCITY CUST_CITY ,Mfg_CUSTMAST.CMCUSTAT CUST_STATE ,Mfg_CUSTMAST.CMCUCNTR CUST_CNTRY ,Mfg_CUSTMAST.CMCUZIP CUST_ZIP ,Mfg_CUSTMAST.CMFAX FAX_NUMBER ,Mfg_CUSTMAST.CMCSR CUST_CSR ,Mfg_CUSTMAST.CMACTCNT ACCT_CNTCT ,Mfg_CUSTMAST.CMACFONE ACCT_PHONE ,Mfg_CUSTMAST.CMDBID DB_REF_NO ,Mfg_CUSTMAST.CMDBSIC DB_CLASS ,Mfg_CUSTMAST.CMDBRATE DB_RATG ,Mfg_CUSTMAST.CMDBDATE DB_DATE ,Mfg_CUSTMAST.CMINTRST RATE ,Mfg_CUSTMAST.CMBANK1 BANK1_REF ,Mfg_CUSTMAST.CMBANK2 BANK2_REF ,Mfg_CUSTMAST.CMCREDIT CR_LIMIT ,Mfg_CUSTMAST.CMCRAUTH CREDIT_BY ,Mfg_CUSTMAST.CMCRDATE CR_DATE ,Mfg_CUSTMAST.CMARTYPE OPNBAL_FWD ,Mfg_CUSTMAST.CMSTMTCD STMT_PRINT ,Mfg_CUSTMAST.CMSTMTCY STMT_CYCLE ,Mfg_CUSTMAST.CMAR AR_CUST -- ,Mfg_CUSTMAST.CMFOB CUST_FOB_POINT ,Mfg_CUSTMAST.CMPARTL PARTL_SHIP ,Mfg_CUSTMAST.CMFTACNO FRGHT_ACCT ,Mfg_CUSTMAST.CMPRNT PRT ,Mfg_SHIPTO.STREGION REGN_CODE ,Mfg_CUSTMAST.CMCLASS CUST_CLAS1 ,Mfg_CUSTMAST.CMCLASS$2 CUST_CLAS2 ,Mfg_CUSTMAST.CMCLASS$3 CUST_CLAS3 ,Mfg_CUSTMAST.CMCLASS$4 CUST_CLAS4 ,Mfg_CUSTMAST.CMCLASS$5 CUST_CLAS5 ,Mfg_CUSTMAST.CMCLASS$6 CUST_CLAS6 ,Mfg_CUSTMAST.CMCLASL CUST_CLAS7 ,Mfg_CUSTMAST.CMCLASL$2 CUST_CLAS8 ,Mfg_CUSTMAST.CMSACNO SAL_ACCTNO ,Mfg_CUSTMAST.CMCGACNO COGS_AC_NO ,Mfg_CUSTMAST.CMARACNO ACCOUNT_NO ,Mfg_SUBCUST.CMYTDPO YTD_SHPMTS ,Mfg_SUBCUST.CMYTDPD YTD_PAYMTS ,Mfg_SUBCUST.CMYTDSLS YTD_SALES ,Mfg_SUBCUST.CMYTDSVC YTD_SVCHG ,Mfg_SUBCUST.CMARBAL BAL_DUE ,Mfg_SUBCUST.CMUNAPCR UNAP_CRED ,Mfg_SUBCUST.CMUNAPCS UNAP_CASH ,Mfg_SUBCUST.CMBOOK BOOKED ,Mfg_SUBCUST.CMARCUR CURR_BAL ,Mfg_SUBCUST.CMAR30 AGE_CAT_1 ,Mfg_SUBCUST.CMAR60 AGE_CAT_2 ,Mfg_SUBCUST.CMAR90 AGE_CAT_3 ,Mfg_SUBCUST.CMARHOLD HOLD_AMT ,Mfg_SUBCUST.CMLSTINV LST_IVCDAT ,Mfg_SUBCUST.CMLSTPMT LST_PAYDAT ,Mfg_SUBCUST.CMSVCHRG SVC_CHRG ,Mfg_CUSTMAST.CMCARR CARRIER ,Mfg_CUSTMAST.CMTRCLAS TRADECLASS ,Mfg_CUSTMAST.CMTOPLVL TOPLVLCUST ,Mfg_CUSTMAST.CMCLVL CUSTLVL ,Mfg_CUSTMAST.CMENDUSR ENDUSERCID ,Mfg_CUSTMAST.CMBILLTO BILLTOCID ,Mfg_CUSTMAST.CMACASGN CREDITASGN ,Mfg_CUSTMAST.CMACREMV CREDITREMV ,Mfg_CUSTMAST.CMSHCODE SHIP_RGN ,Mfg_CUSTMAST.CMRTCODE ROUTE_RGN ,Mfg_CUSTMAST.CMPALLOC PART_ALLOC ,Mfg_CUSTMAST.CMBKCUST BANK_CID ,Mfg_CUSTMAST.CMCRREP CREDIT_REP ,Mfg_CUSTMAST.CMREGION REGIONCODE ,Mfg_CUSTMAST.CMFTAXCD TAX_CODE1 ,Mfg_CUSTMAST.CMSTAXCD TAX_CODE2 ,Mfg_CUSTMAST.CMCTAXCD TAX_CODE3 ,Mfg_CUSTMAST.CMMTAXCD TAX_CODE4 ,Mfg_CUSTMAST.CMEXMPT TAX1EXMPT ,Mfg_CUSTMAST.CMEXS TAX2EXPMT ,Mfg_CUSTMAST.CMEXC TAX3EXPMT ,Mfg_CUSTMAST.CMEXM TAX4EXPMT ,Mfg_CUSTMAST.CMTXCERT TAX1CERT ,Mfg_CUSTMAST.CMSTXCER TAX2CERT ,Mfg_CUSTMAST.CMCTXCER TAX3CERT ,Mfg_CUSTMAST.CMMTXCER TAX4CERT ,Mfg_CUSTMAST.CMVATCUS VATCUSTCD ,Mfg_CUSTMAST.CMPRTFMT SHIPTOFMT ,Mfg_CUSTMAST.CMPRTLNG SHIPTOLNG ,Mfg_CUSTMAST.CMSTKRM SHIPTOSTK ,Mfg_CUSTMAST.CMBIN SHIPTOBIN ,Mfg_SUBCUST.CMARFUT TOTARDUE_L ,Mfg_SUBCUST.CMFARFUT TOTARDUE_F ,Mfg_SUBCUST.CMPENDST PEND_CASH ,Mfg_SUBCUST.CMLPENDP AR_PEND_L ,Mfg_SUBCUST.CMFPENDP AR_PEND_F ,Mfg_SUBCUST.CMLPENDI MTCHPEND_L ,Mfg_SUBCUST.CMFPENDI MTCHPEND_F ,Mfg_PRODLINE.PLPRDLN PLPRDLN ,\'C\' + Mfg_ORDMAST.OMON ORDKEY from Mfg_ORDMAST join Mfg_SUBORD on (Mfg_SUBORD._ORDSUB_OwnRow = Mfg_ORDMAST._Row) left outer join Mfg_SHIPTO on (Mfg_SHIPTO._Row = Mfg_ORDMAST._ORDSADD_OwnRow) left outer join Mfg_DFSFNSF as Mfg_DF on (Mfg_DF._ORDD_OwnRow = Mfg_ORDMAST._Row) left outer join Mfg_ITMMAST on (Mfg_ITMMAST._Row = Mfg_DF._ITMD_OwnRow) left outer join Mfg_PRICES on ((Mfg_PRICES._PRICQUOT_OwnRow = Mfg_ITMMAST._Row) and (Mfg_PRICES._PRICQUOT_OwnRec = 19)) join Mfg_SUBDF on (Mfg_SUBDF._DFSUB_OwnRow = Mfg_DF._Row) join Mfg_CUSTMAST on ((Mfg_CUSTMAST._Row = Mfg_ORDMAST._VCORD_OwnRow) and (Mfg_ORDMAST._VCORD_OwnRec = 11)) join Mfg_SUBCUST on (Mfg_SUBCUST._CUSTSUB_OwnRow = Mfg_CUSTMAST._Row) left outer join Mfg_PRODLINE on (Mfg_PRODLINE._Row = Mfg_ITMMAST._PRODIT_OwnRow) ) with check option GO -------------------------------------------------------------------------------- -- NAME: SUPPLY -- -- PURPOSE: Represents the one to one relationship between Mfg_DFSFNSF and -- Mfg_SUBSF. This includes only inventoried supplies (RECTYPE = S). -------------------------------------------------------------------------------- if objectproperty(object_id(\'SUPPLY\'), \'IsView\') = 1 drop view SUPPLY go create view SUPPLY as select s.RECTYPE ,s.QTYREQ ,s.ORDTYP ,s.ORDSTA ,s.OPSEQN ,s.POUID ,s.QTYTYP ,s.CMPLNTYP ,s.INSTRTDT ,s.OUTNEDDT ,s.REQDUEDT ,s.SORTKEY ,s.USERFD ,s.QTYRCV ,s.QTYATP ,s.REV ,s.LINENUM ,s.TAXCD ,s.ORGPRM ,s.SORTKEY2 ,s.LSTRCV ,s.SFREFTY ,s.SFREF ,s.DELOPN ,s.DELCNT ,s.MRPORDST ,s.TOPLEVEL ,s.CURLEVEL ,s.LEVELKEY ,s._Row DFSFNSF_Row ,s._ITMATP_OwnRow ,s._ITMATP_OwnRec ,s._ITMOS_OwnRow ,s._ITMOS_OwnRec ,s._ITMS_OwnRow ,s._ITMS_OwnRec ,s._ORDS_OwnRow ,s._SD_OwnRow ,s._Ts DFSFNSF_Ts ,ss.SFIIQTY ,ss.SFHOQTY ,ss.SFCOST ,ss.SFUM ,ss.SFUMCNVT ,ss.SFDOLRCV ,ss.SFCUMLAB ,ss.SFVAROH ,ss.SFCUMMAT ,ss.SFCUMOUT ,ss.SFSCRQTY ,ss.SFCLODAT ,ss.SFFIXOH ,ss.SFCLASS0 ,ss.SFCLASS1 ,ss.SFCLASS2 ,ss.SFCLASS3 ,ss.SFCLASS4 ,ss.SFCLASS5 ,ss.SFCLASL0 ,ss.SFCLASL1 ,ss.SFEXRATE ,ss.SFEXRAT9 ,ss.SFEXOP ,ss.SFEXOP9 ,ss._Row SUBSF_Row ,ss._SFSUB_OwnRow ,ss._Ts SUBSF_Ts from Mfg_DFSFNSF s left join Mfg_SUBSF ss on (ss._SFSUB_OwnRow = s._Row) where s.RECTYPE = \'S\' with check option go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[RPT_EngineeringBillOfMaterialParent]\') and objectproperty(id, N\'IsView\') = 1) drop view dbo.RPT_EngineeringBillOfMaterialParent go ------------------------------------------------------------------------------------------ -- NAME: RPT_EngineeringBillOfMaterialParent -- COPYRIGHT: Copyright ?2002, SoftBrands Manufacturing, Inc. All rights reserved. -- -- FUNCTION: The parent item information in the engineering temporary tables. ------------------------------------------------------------------------------------------ create view RPT_EngineeringBillOfMaterialParent as select p.SOURCE ItemSource ,p.ITEM ItemNumber ,p.EFF_DATE EffectivityDate ,p.EFF_REV EffectiveRevision ,p.ITEM_DESC ItemDescription ,p.REV ItemRevision ,p.UM ItemUM ,p.MB MakeBuyCode ,p.RUN_LT RunLeadTimeDays ,p.FIX_LT FixedLeadTimeDays ,p.INSP_LT InspectionLeadTimeDays ,p.PLNR Planner ,p.LT IsLotTraced ,p.PLN_POLICY PlanningPolicy ,p.DRWG DrawingNumber ,p.SOURCE2 ItemSource2 ,p.ITEM2 ItemNumber2 ,p.EFF_DATE2 EffectivityDate2 ,p.EFF_REV2 EffectiveRevision2 ,p.ITEM_DESC2 ItemDescription2 ,p.REV2 ItemRevision2 ,p.UM2 ItemUM2 ,p.MB2 MakeBuyCode2 ,p.RUN_LT2 RunLeadTimeDays2 ,p.FIX_LT2 FixedLeadTimeDays2 ,p.INSP_LT2 InspectionLeadTimeDays2 ,p.PLNR2 Planner2 ,p.LT2 IsLotTraced2 ,p.PLN_POLICY2 PlanningPolicy2 ,p.DRWG2 DrawingNumber2 ,p.EXCLUSION_STRING ExclusionString ,p.DISPLAY_STRING DisplayString ,p.STANDARD_VIEWNUMBER StandardViewNumber ,p.BUY_COSTDEF BuyCostDefinition ,p.MAKE_COSTDEF MakeCostDefinition ,p.AVAIL_QTYDEF AvailQuantityDefinition ,p.LOCATION_INCL_CODE LocationIncludeCode ,p.STOCK_BIN_DEF StockBinDefinition ,p.PROD_QTY ProductionQuantity ,p.SUMB_REPORT_NUMBER SUMBReportNumber ,p.CUM_LT CumulativeLeadTimeDays ,p.MEXP_SELECTED_LT_VALUE MEXPSelectedLeadTimeDays ,p.COST_TYPE CostType ,p.COST_CODE CostCode ,p.YIELD YieldPercentage ,p.RAW_MATL RawMaterialCost ,p.RAW_LABOR RawLaborCost ,p.RAW_VOH RawVariableOverheadCost ,p.RAW_FOH RawFixedOverheadCost ,p.ATL_MATL MaterialCostAtThisLevel ,p.ATL_LABOR LaborCostAtThisLevel ,p.ATL_FOH FixedOverheadCostAtThisLevel ,p.ATL_VOH VariableOverheadCostAtThisLevel ,p.ATL_TOTAL TotalCostAtThisLevel ,p.ROLLED_MATL RolledMaterialCost ,p.ROLLED_LABOR RolledLaborCost ,p.ROLLED_VOH RolledVariableOverheadCost ,p.ROLLED_FOH RolledFixedOverheadCost ,p.ROLLED_TOTAL RolledTotalCost ,p.ROLLED_MATL_OR RolledMaterialCostOrderRelated ,p.ROLLED_LABOR_OR RolledLaborCostOrderRelated ,p.ROLLED_VOH_OR RolledVariableOverheadCostOrderRelated ,p.ROLLED_FOH_OR RolledFixedOverheadCostOrderRelated ,p.ROLLED_TOTAL_OR RolledTotalCostOrderRelated ,p.RESMATL_FLAG ResourceMaterialFlag ,p.LOTSIZE LotSizeQuantity ,p.COMPANYNAME CompanyName ,p.USERID UserID ,p.COMPAREHIDE CompareHide ,p.REQD_EXT_COST RequiredExtendedCost ,p.CONS_EXT_COST ConsumedExtendedCost ,p.TOTL_EXT_COST TotalExtendedCost ,p.REQD_AVG_COST RequiredAverageCost ,p.CONS_AVG_COST ConsumedAverageCost ,p.TOTL_AVG_COST TotalAverageCost ,p.TOTL_ORDER_RELATED TotalOrderRelated ,p.MEXP_LABOR MEXPLaborCost ,p.MEXP_MATL MEXPMaterialCost ,p.MEXP_VOVHD MEXPVariableOverheadCost ,p.MEXP_FOVHD MEXPFixedOverheadCost ,p.MEXP_TOTCOST MEXPTotalCost ,p.STD_MATL StandardMaterialCost ,p.STD_LABOR StandardLaborCost ,p.STD_VOH StandardVariableOverheadCost ,p.STD_FOH StandardFixedOverheadCost ,p.STD_TOTAL StandardTotalCost ,p.ReportID ReportID from <FSDBXXTemp, char, >.[dbo].BMPARENT p go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[RPT_EngineeringBillOfMaterialComparison]\') and objectproperty(id, N\'IsView\') = 1) drop view dbo.RPT_EngineeringBillOfMaterialComparison go ------------------------------------------------------------------------------------------ -- NAME: RPT_EngineeringBillOfMaterialComparison -- COPYRIGHT: Copyright ?2002, SoftBrands Manufacturing, Inc. All rights reserved. -- -- FUNCTION: The BOM comparision information stored in the engineering temporary tables. ------------------------------------------------------------------------------------------ create view RPT_EngineeringBillOfMaterialComparison as select c.MATCHED IsMatched ,c.PARENT1 ParentItemNumber1 ,c.PT_USE1 PointOfUseID1 ,c.SEQN1 OperationSequenceNumber1 ,c.COMPONENT1 ComponentItemNumber1 ,c.COMPONENT_ITEMSOURCE1 ComponentItemSource1 ,c.COMP_DESC1 ComponentItemDescription1 ,c.COM_TYP1 ComponentType1 ,c.QUANTITY1 RequiredQuantity1 ,c.QTY_TYP1 QuantityType1 ,c.IN_EFFECT1 InEffectivityDate1 ,c.OUT_EFFECT1 OutEffectivityDate1 ,c.IN_REV1 InRevision1 ,c.OUT_REV1 OutRevision1 ,c.REFDES1 ReferenceDesignator1 ,c.RD_COUNT1 ReferenceDesignatorCount1 ,c.PROD_ITEM1 ProductItemNumber1 ,c.PARENT2 ParentItemNumber2 ,c.PT_USE2 PointOfUseID2 ,c.SEQN2 OperationSequenceNumber2 ,c.COMPONENT2 ComponentItemNumber2 ,c.COMPONENT_ITEMSOURCE2 ComponentItemSource2 ,c.COMP_DESC2 ComponentItemDescription2 ,c.COM_TYP2 ComponentType2 ,c.QUANTITY2 RequiredQuantity2 ,c.QTY_TYP2 QuantityType2 ,c.IN_EFFECT2 InEffectivityDate2 ,c.OUT_EFFECT2 OutEffectivityDate2 ,c.IN_REV2 InRevision2 ,c.OUT_REV2 OutRevision2 ,c.REFDES2 ReferenceDesignator2 ,c.RD_COUNT2 ReferenceDesignatorCount2 ,c.PROD_ITEM2 ProductItemNumber2 ,c.ReportID ReportID from <FSDBXXTemp, char, >.[dbo].BMCOMPARE c go if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[RPT_EngineeringMultiLevelBillOfMaterial]\') and objectproperty(id, N\'IsView\') = 1) drop view dbo.RPT_EngineeringMultiLevelBillOfMaterial go ------------------------------------------------------------------------------------------ -- NAME: RPT_EngineeringMultiLevelBillOfMaterial -- COPYRIGHT: Copyright ?2002, SoftBrands Manufacturing, Inc. All rights reserved. -- -- FUNCTION: The Multi Level Bill information stored in the engineering temporary tables. ------------------------------------------------------------------------------------------ create view RPT_EngineeringMultiLevelBillOfMaterial as select m.PROCESSING_ORDER ProcessingOrder ,m.HASLOWERLEVELS HasLowerLevels ,m.INTERNAL_COMPONENT InternalComponentItemNumber ,m.ML_LEVEL MultiLevelNumber ,m.DISPLAY_LEVEL DisplayLevel ,m.ML_KEY_RECORD ComponentItemKey ,m.INTERNALKEY InternalComponentItemKey ,m.COMPONENT_ITEMSOURCE ComponentItemSource ,m.PARENT_ITEMSOURCE ParentItemSource ,m.PARENT_BOMSOURCE ParentBOMSource ,m.EXCLUDE_FLAG ExcludeFlag ,m.ML_COMPONENT ComponentItemNumber ,m.ML_PROD_COMPONENT ProductionComponentItemNumber ,m.ML_PARENT ParentItemNumber ,m.ML_PT_USE PointOfUseID ,m.ML_SEQN OperationSequenceNumber ,m.ML_COM_TYP ComponentType ,m.ML_LT_OFFSET LeadTimeOffsetDays ,m.ML_QUANTITY RequiredQuantity ,m.ML_QTY_TYP QuantityType ,m.ML_IN_EFFECT InEffectivityDate ,m.ML_OUT_EFFECT OutEffectivityDate ,m.ML_IN_REV InRevision ,m.ML_OUT_REV OutRevision ,m.ML_SCRAP_PCNT ScrapPercent ,m.ML_MSCFGGRP ConfigurationGroup ,m.ML_MSCFGQTY ConfigurationQuantity ,m.ML_MSCFGTYP ConfigurationType ,m.ML_MSMODIND IsModuleOrNormal ,m.ML_MSTXTID TextID ,m.ML_MSSTKRM BackflushStockroom ,m.ML_MSBIN BackflushBin ,m.ML_MSCOMPMB IsScrapBackflushed ,m.ML_MSCOMPUM ComponentItemUM ,m.ML_COMPONENT_TEXT ComponentText ,m.ML_MAKEBUY MakeBuyCode ,m.ML_DESCRIPTION ComponentItemDescription ,m.ML_LEADTIME TotalLeadTimeDays ,m.ML_SUMMARYQTY SummaryQuantity ,m.ML_COSTINGQTY CostingQuantity ,m.ML_REQUIREDQTY ComponentRequiredQuantity ,m.ML_CONSUMEDQTY ConsumeQuantity ,m.ML_AVAILINV_ONHAND AvailableOnHandQuantity '; preg_match_all($re, $str, $matches, PREG_SET_ORDER, 0); // Print the entire match result var_dump($matches);

Please keep in mind that these code samples are automatically generated and are not guaranteed to work. If you find any syntax errors, feel free to submit a bug report. For a full regex reference for PHP, please visit: http://php.net/manual/en/ref.pcre.php