Microsoft Dynamics GP - SQL View for Unposted(SOP10200) & Posted(SOP30300) SOP Documents Details

SQL View to join Unposted(SOP10200) & Posted(SOP30300) SOP Documents Details for Microsoft Dynamics GP as one table as view.

For Dynamics GP 2018 R2

/****** Object: View [dbo].[Hyou_VW_SOP10200&SOP30300] Script Date: 15/07/2021 11:42:58 AM ******/

SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO





ALTER VIEW [dbo].[Hyou_VW_SOP10200&SOP30300]

AS

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

-- Hyou_VW_SOP10200&SOP30300

-- Created Jun 30, 2021 by Hussain Ali Al Yousefh

-- For updates see hussainalyousef.wordpress.com

-- SQL View for Unposted(SOP10200) & Posted(SOP30300) SOP Documents Details in Dynamics GP

-- For Dynamics GP 2018 R2

-- ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

SELECT 'Unposted' [Documents_Status],'SOP10200' [Table],SOPTYPE,SOPNUMBE,LNITMSEQ,CMPNTSEQ,ITEMNMBR,ITEMDESC,NONINVEN,DROPSHIP,UOFM,LOCNCODE,UNITCOST,ORUNTCST,UNITPRCE,ORUNTPRC,XTNDPRCE,OXTNDPRC,REMPRICE,OREPRICE,EXTDCOST,OREXTCST,MRKDNAMT,ORMRKDAM,MRKDNPCT,MRKDNTYP,INVINDX,CSLSINDX

,SLSINDX,MKDNINDX,RTNSINDX,INUSINDX,INSRINDX,DMGDINDX,ITMTSHID,IVITMTXB,BKTSLSAM,ORBKTSLS,TAXAMNT,ORTAXAMT,TXBTXAMT,OTAXTAMT,BSIVCTTL,TRDISAMT,ORTDISAM,DISCSALE,ORDAVSLS,QUANTITY,ATYALLOC,QTYINSVC,QTYINUSE,QTYDMGED,QTYRTRND,QTYONHND,QTYCANCE,QTYCANOT

,[QTYONPO],QTYORDER,QTYPRBAC,QTYPRBOO,QTYPRINV,QTYPRORD,QTYPRVRECVD,QTYRECVD,QTYREMAI,QTYREMBO,QTYTBAOR,QTYTOINV,QTYTORDR,QTYFULFI,QTYSLCTD,QTYBSUOM,EXTQTYAL,EXTQTYSEL,ReqShipDate,FUFILDAT,ACTLSHIP,SHIPMTHD,SALSTERR,SLPRSNID,PRCLEVEL,COMMNTID,BRKFLD1

,BRKFLD2,BRKFLD3,CURRNIDX,TRXSORCE,SOPLNERR,'' [DOCNCORR],ORGSEQNM,ITEMCODE,PURCHSTAT,DECPLQTY,DECPLCUR,ODECPLCU,[QTYTOSHP],[XFRSHDOC],EXCEPTIONALDEMAND,TAXSCHID,TXSCHSRC,PRSTADCD,ShipToName,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,[STATE]

,ZIPCODE,CCode,COUNTRY,PHONE1,PHONE2,PHONE3,FAXNUMBR,Flags,[BackoutTradeDisc],[OrigBackoutTradeDisc],[GPSFOINTEGRATIONID],[INTEGRATIONSOURCE],[INTEGRATIONID],CONTNBR,CONTLNSEQNBR,CONTSTARTDTE,CONTENDDTE,CONTITEMNBR,CONTSERIALNBR

,[BULKPICKPRNT],[INDPICKPRNT],[ISLINEINTRA],[SOFULFILLMENTBIN],[MULTIPLEBINS],Print_Phone_NumberGB,DEX_ROW_TS,DEX_ROW_ID

FROM SOP10200

UNION ALL

SELECT 'Posted','SOP30300',SOPTYPE,SOPNUMBE,LNITMSEQ,CMPNTSEQ,ITEMNMBR,ITEMDESC,NONINVEN,DROPSHIP,UOFM,LOCNCODE,UNITCOST,ORUNTCST,UNITPRCE,ORUNTPRC,XTNDPRCE,OXTNDPRC,REMPRICE,OREPRICE,EXTDCOST,OREXTCST,MRKDNAMT,ORMRKDAM,MRKDNPCT,MRKDNTYP,INVINDX,CSLSINDX

,SLSINDX,MKDNINDX,RTNSINDX,INUSINDX,INSRINDX,DMGDINDX,ITMTSHID,IVITMTXB,BKTSLSAM,ORBKTSLS,TAXAMNT,ORTAXAMT,TXBTXAMT,OTAXTAMT,BSIVCTTL,TRDISAMT,ORTDISAM,DISCSALE,ORDAVSLS,QUANTITY,ATYALLOC,QTYINSVC,QTYINUSE,QTYDMGED,QTYRTRND,QTYONHND,QTYCANCE,QTYCANOT

,0.00000 [QTYONPO],QTYORDER,QTYPRBAC,QTYPRBOO,QTYPRINV,QTYPRORD,QTYPRVRECVD,QTYRECVD,QTYREMAI,QTYREMBO,QTYTBAOR,QTYTOINV,QTYTORDR,QTYFULFI,QTYSLCTD,QTYBSUOM,EXTQTYAL,EXTQTYSEL,ReqShipDate,FUFILDAT,ACTLSHIP,SHIPMTHD,SALSTERR,SLPRSNID,PRCLEVEL,COMMNTID

,BRKFLD1,BRKFLD2,BRKFLD3,CURRNIDX,TRXSORCE,SOPLNERR,[DOCNCORR],ORGSEQNM,ITEMCODE,PURCHSTAT,DECPLQTY,DECPLCUR,ODECPLCU,0.00000 [QTYTOSHP],0 [XFRSHDOC],EXCEPTIONALDEMAND,TAXSCHID,TXSCHSRC,PRSTADCD,ShipToName,CNTCPRSN,ADDRESS1,ADDRESS2,ADDRESS3,CITY,[STATE]

,ZIPCODE,CCode,COUNTRY,PHONE1,PHONE2,PHONE3,FAXNUMBR,Flags,0.00000 [BackoutTradeDisc],0.00000 [OrigBackoutTradeDisc],'' [GPSFOINTEGRATIONID],0 [INTEGRATIONSOURCE],'' [INTEGRATIONID],CONTNBR,CONTLNSEQNBR,CONTSTARTDTE,CONTENDDTE,CONTITEMNBR,CONTSERIALNBR

,0 [BULKPICKPRNT],0 [INDPICKPRNT],0 [ISLINEINTRA],'' [SOFULFILLMENTBIN],0 [MULTIPLEBINS],Print_Phone_NumberGB,DEX_ROW_TS,DEX_ROW_ID

FROM SOP30300



-- add permissions for DYNGRP

GO

GRANT SELECT ON [dbo].[Hyou_VW_SOP10200&SOP30300] TO [DYNGRP]

GO