-- Sales Tables
SELECT 'Sales Order Releases' AS [Type]
     , SOR.FSONO
     , SOR.FENUMBER
     , SOR.FRELEASE
     , SOR.FPARTNO
     , SOM.FCOMPANY
     , SOM.FCUSTNO
     , SOR.FDUEDATE
     , SOM.FSTATUS
FROM SORELS SOR
     LEFT OUTER JOIN SOITEM SOI
       ON SOI.FSONO = SOR.FSONO
AND       SOI.FINUMBER = SOR.FINUMBER
     LEFT OUTER JOIN SOMAST SOM
       ON SOM.FSONO = SOR.FSONO
     LEFT OUTER JOIN SLCDPM SLC
       ON SLC.FCUSTNO = SOM.FCUSTNO
WHERE SOI.FSONO IS NULL
OR     SOM.FSONO IS NULL
OR     SLC.FCUSTNO IS NULL
-------------------------------------------
SELECT 'Sales Order Items' AS [Type]
     , SOI.FENUMBER
     , SOI.FPARTNO
     , SOM.FCOMPANY
     , SOM.FCUSTNO
     , SOI.FDUEDATE
     , SOM.FSTATUS
FROM SOITEM SOI
     LEFT OUTER JOIN SOMAST SOM
       ON SOM.FSONO = SOI.FSONO
     LEFT OUTER JOIN SLCDPM SLC
       ON SLC.FCUSTNO = SOM.FCUSTNO
WHERE SOM.FSONO IS NULL
OR     SLC.FCUSTNO IS NULL
-------------------------------------------
SELECT 'Sales Order Master' AS [Type]
     , SOM.FCOMPANY
     , SOM.FCUSTNO
     , SOM.FSTAtus
     , SOM.FORDERDATE
FROM SOMAST SOM LEFT OUTER JOIN SLCDPM SLC ON SLC.FCUSTNO = SOM.FCUSTNO
WHERE SLC.FCUSTNO IS NULL
--Quotes
SELECT 'Quote Items' AS [Type]
     , qtitem.FQUOTENO
     , qtitem.FENUMBER
     , qtitem.FPARTNO
     , qtitem.FCTPDATE
     , qtmast.FCUSTNO
     , qtmast.fcompany
FROM qtitem
     LEFT OUTER JOIN QTMAST
       ON qtmast.fquoteno = qtitem.FQUOTENO
     LEFT OUTER JOIN slcdpm
       ON slcdpm.FCUSTNO = qtmast.FCUSTNO
WHERE qtmast.FQUOTENO IS NULL
OR     slcdpm.FCUSTNO IS NULL
-------------------------------------------
SELECT 'Quote Master' AS [Type]
     , qtmast.FCUSTNO
     , qtmast.fcompany
     , qtmast.FQUOTEDATE
FROM qtmast LEFT OUTER JOIN slcdpm ON slcdpm.FCUSTNO = qtmast.FCUSTNO
WHERE slcdpm.FCUSTNO IS NULL
--Shipping  **** No SCLDPM link.
SELECT 'Shipping' AS [Type]
     , shitem.FSHIPNO
     , shitem.FITEMNO
     , shitem.FPARTNO
     , shitem.FSOKEY
FROM shitem LEFT OUTER JOIN SHMAST ON shmast.fshipno = shitem.fshipno
WHERE shmast.FSHIPNO IS NULL
--Accts Receivable Tables
SELECT 'Accts Recv Items' AS [Type]
     , aritem.FCUSTNO
     , aritem.FCINVOICE
     , aritem.FPARTNO
     , aritem.FSHIPKEY
     , aritem.FSOKEY
     , armast.FDGLDATE
FROM aritem
     LEFT OUTER JOIN armast
       ON aritem.FCINVOICE = armast.FCINVOICE
     LEFT OUTER JOIN slcdpm
       ON slcdpm.FCUSTNO = aritem.FCUSTNO
WHERE armast.FCINVOICE IS NULL
OR     slcdpm.FCUSTNO IS NULL
-------------------------------------
SELECT 'Accts Recv Master' AS [Type]
     , armast.FCUSTNO
     , armast.FCCOMPANY
     , armast.FCINVOICE
     , armast.FCSTATUS
     , armast.FDGLDATE
FROM armast LEFT OUTER JOIN SLCDPM ON slcdpm.FCUSTNO = armast.FCUSTNO
WHERE slcdpm.FCUSTNO IS NULL
--Accts Payable Tables
SELECT 'Accts Payable Items' AS [Type]
     , apitem.FCINVKEY
     , apitem.FPARTNO
     , apitem.FCSHIPKEY
     , apitem.FCSTATUS
FROM apitem
     LEFT OUTER JOIN apmast
       ON apitem.FCINVKEY = (apmast.fvendno + apmast.fcinvoice)
     LEFT OUTER JOIN APVEND
       ON APVEND.FVENDNO = apmAST.FVENDNO
WHERE apmast.IDENTITY_COLUMN IS NULL
OR     APVEND.FVENDNO IS NULL
-------------------------------------
SELECT 'Accts Payable Master' AS [Type]
     , apmast.FVENDNO
     , apmast.FCCOMPANY
     , apmast.FCSTATUS
     , apmast.FINVDATE
FROM apmast LEFT OUTER JOIN APVEND ON APVEND.FVENDNO = apmAST.FVENDNO
WHERE APVEND.FVENDNO IS NULL
--General Ledger
SELECT 'General Ledger' AS [Type]
     , GLITEM.fcacctnum
     , dbo.glitem.FDDATE
     , dbo.glmast.FCTYPE
FROM GLITEM LEFT OUTER JOIN glmast ON glmast.fcacctnum = glitem.FCACCTNUM
WHERE glmast.FCACCTNUM IS NULL
--Journal Entry Tables
SELECT 'Journal Entry' AS [Type], gljeitem.fcentry, gljeitem.fnorder
FROM gljeitem LEFT OUTER JOIN gljemast ON gljemast.fcentry = gljeitem.fcentry
WHERE gljemast.fcentry IS NULL
--Job Tables
SELECT 'Jobs' AS [Type]
     , joitem.FJOBNO
     , joitem.FPARTNO
     , joitem.FDUEDATE
FROM joitem LEFT OUTER JOIN jomast ON jomast.FJOBNO = joitem.FJOBNO
WHERE jomast.FJOBNO IS NULL
--Receiving Tables
SELECT 'Receiving Items' AS [Type]
     , rcitem.FRECEIVER
     , rcitem.FITEMNO
     , rcitem.FVENDNO
     , rcitem.FSOkey
     , rcitem.FJOKEY
     , rcitem.FPARTNO
FROM rcitem LEFT OUTER JOIN rcmast ON rcmast.FRECEIVER = rcitem.FRECEIVER
WHERE rcmast.FRECEIVER IS NULL
--Bill of Lading Tables
SELECT 'Bill of Lading' AS [Type], *
FROM BOLITEM LEFT OUTER JOIN BOLMAST ON bolmast.FCBOLNO = bolitem.FCBOLNO
WHERE bolmast.FCBOLNO IS NULL
--Purchasing Tables
SELECT 'Purchase Items' AS [Type]
     , poitem.FPONO
     , poitem.FITEMNO
     , poitem.FPARTNO
     , poitem.FSOKEY
     , poitem.FORGPDATE
FROM poitem LEFT OUTER JOIN pomast ON pomast.FPONO = poitem.FPONO
WHERE pomast.FPONO IS NULL
-------------------
SELECT 'Purchase Master' AS [Type]
     , pomast.FPONO
     , pomast.FVENDNO
     , pomast.FCOMPANY
     , pomast.FSTATUS
     , pomast.FORDDATE
FROM pomast LEFT OUTER JOIN apvend ON pomast.FVENDNO = apvend.FVENDNO
WHERE apvend.FVENDNO IS NULL
--RFQ Tables
SELECT 'RFQ' AS [Type], poqitm.fpartno, poqitm.fcpartrev
FROM   poqitm
     LEFT OUTER JOIN
       POQREC
     ON POQREC.fpartno + POQREC.frev + POQREC.fac =
          poqitm.fpartno + poqitm.fcpartrev + poqitm.fac
WHERE poqrec.IDENTITY_COLUMN IS NULL
--Inventory Vendors
SELECT 'Inventory Vendors' AS [Type], invend.FPARTNO, invend.FPARTREV
FROM   INVEND
     LEFT OUTER JOIN
       INMAST
     ON (inmast.FPARTNO + inmast.FREV + inmast.FAC) =
          (invend.FPARTNO + invend.FPARTREV + invend.FAC)
WHERE inmast.IDENTITY_COLUMN IS NULL
--Current Revisions
SELECT 'Current Rev' AS [Type], INVCUR.fcpartno, INVCUR.fcpartrev
FROM   INVCUR
     LEFT OUTER JOIN
       INMAST
     ON (INVCUR.fcpartno + INVCUR.fcpartrev + INVCUR.fac) =
          (inmast.FPARTNO + inmast.FREV + inmast.fac)
WHERE inmast.FPARTNO IS NULL
--Part Cross Reference
SELECT 'Part Cross Ref' AS [Type], incros.FPARTNO, incros.FCPARTREV
FROM   incros
     LEFT OUTER JOIN
       inmast
     ON (incros.fpartno + incros.fcpartrev + incros.fac) =
          (inmast.FPARTNO + inmast.FREV + inmast.fac)
WHERE inmast.FPARTNO IS NULL
-- Bills of Material
SELECT 'BOMs' AS [Type], INBOMS.fparent, INBOMS.fparentrev
FROM   INBOMS
     LEFT OUTER JOIN
       INBOMM
     ON (INBOMS.fparent + INBOMS.fparentrev + INBOMS.pfacilityid) =
          (INBOMM.fpartno + INBOMM.fcpartrev + INBOMM.facilityid)
WHERE inboms.fparent IS NULL