Archives

Work in Process (RPWIP) and Inventory Evaluation (RPIVAL) Reports

As I have time I will share the SQL queries derived from the more popular Made2Manage reports. After you have the SQL query, you can display it with any program you want such as Crystal Reports, ODBC Spreadsheets, or SQL Server Reporting Services.

Use the queries with caution and make sure that you always test them accuracy.

Work in Process:


SELECT JOM.fcompany,
       JOM.fjobno,
       fopen_dt,
       fddue_date AS fdue_date,
       JOM.fac,
       JOM.fpartno,
       JOM.fpartrev,
       JOM.fcudrev,
       INM.fluseudrev,
       JOM.fprodcl,
       JOM.fstatus,
       JOM.fsono,
       JOM.fkey,
       Joitem.fgroup,
       fquantity,
       (JOP.fsetuphrs + JOP.fprodhrs) AS fnEstHrs,
       isnull (csrHrs.fnActHrs, 0.00) AS fnacthrs,
       (sorels.fshipbook + sorels.fshipbuy + sorels.fshipmake) AS fshipqty,
       (fmatlact + ftoolact - fmatlinv) AS fnmatlcost,
       (fsetupact + flabact - flabinv) AS fnlabrcost,
       (fovhdact - fovhdinv) AS fnovhdcost,
       (fothract - fothrinv) AS fnothrcost,
       (fsubact - fsubinv) AS fnsubccost,
       space (18) AS fshipkey,
       JOM.fglacct AS facctnum,
       JOM.fcus_id AS fcustno,
       (  fmatlact
        + ftoolact
        - fmatlinv
        + fsetupact
        + flabact
        - flabinv
        + fovhdact
        - fovhdinv
        + fothract
        - fothrinv
        + fsubact
        - fsubinv)
          AS fntotcost,
       ISNULL (CASE
                  WHEN fluseudrev = 1 THEN INM.fcudrev
                  ELSE JOM.fpartrev
               END,
               JOM.fPartRev
       )
          AS fcdisprev
  FROM                   jomast JOM
                      INNER JOIN
                         sorels
                      ON (sorels.fsono + sorels.finumber + sorels.frelease) =
                            (JOM.fsono + JOM.fkey)
                   INNER JOIN
                      jopact JOPA
                   ON JOM.fjobno = JOPA.fjobno
                INNER JOIN
                   Jopest JOP
                ON JOM.fjobno = JOP.fjobno
             INNER JOIN
                joitem
             ON JOM.fjobno = joitem.fjobno
          LEFT JOIN
             Inmast INM
          ON     JOM.fpartno = INM.fpartno
             AND JOM.fpartrev = INM.frev
             AND JOM.fac = INM.fac
       LEFT JOIN
          (SELECT LAD.fjobno,
                  SUM(CASE
                         WHEN flabtype = 'P'
                         THEN
                            ROUND (fchrglab / 60.00, 4)
                         ELSE
                            ROUND (datediff (mi, fsdatetime, fedatetime)
                                   / 60.00,
                                   4
                            )
                      END)
                     AS fnActHrs
             FROM LaDetail LAD
           GROUP BY LAD.fJobno) csrHrs
       ON csrHrs.fJobNo = JOM.fJobNo
 WHERE     JOM.ftype = 'C'
       AND  fmatlact
          + ftoolact
          - fmatlinv
          + fsetupact
          + flabact
          - flabinv
          + fovhdact
          - fovhdinv
          + fothract
          - fothrinv
          + fsubact
          - fsubinv <> 0
       AND abs(  fmatlact
               + ftoolact
               - fmatlinv
               + fsetupact
               + flabact
               - flabinv
               + fovhdact
               - fovhdinv
               + fothract
               - fothrinv
               + fsubact
               - fsubinv) > .015
UNION ALL
SELECT JOM.fcompany,
       JOM.fjobno,
       fopen_dt,
       fddue_date AS fdue_date,
       JOM.fac,
       JOM.fpartno,
       JOM.fpartrev,
       JOM.fcudrev,
       INM.fluseudrev,
       JOM.fprodcl,
       JOM.fstatus,
       JOM.fsono,
       JOM.fkey,
       Joitem.fgroup,
       fquantity,
       JOP.fsetuphrs + JOP.fprodhrs AS fnEstHrs,
       isnull (csrHrs.fnActHrs, 0.0) AS fnacthrs,
       0.00000 AS fshipqty,
       (fmatlact + ftoolact - fmatlinv) AS fnmatlcost,
       (fsetupact + flabact - flabinv) AS fnlabrcost,
       (fovhdact - fovhdinv) AS fnovhdcost,
       (fothract - fothrinv) AS fnothrcost,
       (fsubact - fsubinv) AS fnsubccost,
       space (18) AS fshipkey,
       JOM.fglacct AS facctnum,
       JOM.fcus_id AS fcustno,
       (  fmatlact
        + ftoolact
        - fmatlinv
        + fsetupact
        + flabact
        - flabinv
        + fovhdact
        - fovhdinv
        + fothract
        - fothrinv
        + fsubact
        - fsubinv)
          AS fntotcost,
       ISNULL (CASE
                  WHEN fluseudrev = 1 THEN INM.fcudrev
                  ELSE JOM.fpartrev
               END,
               JOM.fPartRev
       )
          AS fcdisprev
  FROM                jomast JOM
                   INNER JOIN
                      jopact JOPA
                   ON JOM.fjobno = JOPA.fjobno
                INNER JOIN
                   Jopest JOP
                ON JOM.fjobno = JOP.fjobno
             LEFT JOIN
                Inmast INM
             ON     JOM.fpartno = INM.fpartno
                AND JOM.fpartrev = INM.frev
                AND JOM.fac = INM.fac
          INNER JOIN
             joitem
          ON JOM.fjobno = joitem.fjobno
       LEFT JOIN
          (SELECT LAD.fjobno,
                  SUM(CASE
                         WHEN flabtype = 'P'
                         THEN
                            ROUND (fchrglab / 60.00, 4)
                         ELSE
                            ROUND (datediff (mi, fsdatetime, fedatetime)
                                   / 60.00,
                                   4
                            )
                      END)
                     AS fnActHrs
             FROM LaDetail LAD
           GROUP BY LAD.fJobno) csrHrs
       ON csrHrs.fJobNo = JOM.fJobNo
 WHERE     JOM.ftype = 'I'
       AND JOM.fitype <> '2'
       AND  fmatlact
          + ftoolact
          - fmatlinv
          + fsetupact
          + flabact
          - flabinv
          + fovhdact
          - fovhdinv
          + fothract
          - fothrinv
          + fsubact
          - fsubinv <> 0
       AND abs(  fmatlact
               + ftoolact
               - fmatlinv
               + fsetupact
               + flabact
               - flabinv
               + fovhdact
               - fovhdinv
               + fothract
               - fothrinv
               + fsubact
               - fsubinv) > .015
ORDER BY JOM.fPartNo

Inventory Evaluation


-- Keep in mind that the cost columns are per piece.  

SELECT INO.Fac AS [Facility],
       INO.fpartno AS [Part No],
       INO.fpartrev AS [Part Rev],
       INO.fcudrev AS [fcudrev],
       CASE
          WHEN INM.fluseudrev = 1 THEN INO.fcudrev
          ELSE INO.fpartrev
       END
          AS [fcdisprev],
       INO.fbinno AS [Bin No],
       INO.flocation AS [Location],
       InnerQry.fonhand AS [Qty On Hand],
       INO.flot AS [Lot],
       INM.fdescript AS [Part Description],
       INM.flastrcpt AS [Date Last Recvd],
       INM.fmeasure AS [Unit of Measure],
       INM.fstdcost AS [Std Cost],
       INM.f2totcost AS [Total Cost (AVG)],
       INM.flastcost AS [Last Act Cost],
       INM.fgroup AS [Group Code],
       INM.fprodcl AS [Prod Class],
       INM.favgcost AS [Average Cost],
       0.000000 AS [REF COST]
  FROM       dbo.inonhd INO
          INNER JOIN
             dbo.inmast INM
          ON (INM.Fac = INO.Fac)
             AND ( (INM.fpartno = INO.fpartno)
                  AND (INM.fRev = INO.fpartrev))
       JOIN
          (SELECT INO.Fac AS IQ_Fac,
                  INO.fpartno AS IQ_fpartno,
                  INO.fpartrev AS IQ_fpartrev,
                  INO.fbinno AS IQ_fbinno,
                  INO.flocation AS IQ_flocation,
                  SUM (INO.fonhand) AS fonhand,
                  MAX (INO.Identity_Column) AS INOI_C,
                  MAX (INM.Identity_Column) AS INMI_C
             FROM    dbo.inonhd INO
                  INNER JOIN
                     dbo.inmast INM
                  ON (INM.Fac = INO.Fac)
                     AND ( (INM.fpartno = INO.fpartno)
                          AND (INM.fRev = INO.fpartrev))
           
           GROUP BY INO.Fac,
                    INO.fpartno,
                    INO.fpartrev,
                    INO.fbinno,
                    INO.flocation) AS InnerQry
       ON INO.Identity_Column = InnerQry.INOI_C
          AND INM.Identity_Column = InnerQry.INMI_C
 
ORDER BY INM.FAC, INM.FPARTNO, INM.FREV

Related posts:

3 comments to Work in Process (RPWIP) and Inventory Evaluation (RPIVAL) Reports

  • Andrew

    Thanks for posting these. What is the best way to extract these from M2M?

    • Jason Amonett

      Did anyone answer? I think I am looking for the exact same thing. I want to replicate the systems standard reports but in my access database I have built. I am getting very close but its not tying exactly and I know this information would let me do this.

  • Jason Amonett

    Can anyone help me with the tables/fields that make up the standard reports in made2manage like AR/AP aging so I can replicate in my database.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>