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