Items Missing Primary Location in F41021

/* Formatted on 2012/03/14 06:31 (Formatter Plus v4.8.8) */
SELECT ib.ibmcu, COUNT (*)
  FROM testdta.f4102 ib
 WHERE NOT EXISTS (
          SELECT *
            FROM testdta.f41021 li
           WHERE ib.ibitm = li.liitm AND ib.ibmcu = li.limcu
                 AND li.lipbin = 'P')
                 group by ib.ibmcu

Missing records in F5541052

SELECT ib.ibitm, ib.iblitm, ib.ibaitm, ib.ibmcu, ib.ibstkt, c2.*
  FROM proddta.f4102 ib, proddta.f554105 c2
 WHERE ib.ibitm = c2.c2itm
   AND ib.ibmcu = c2.c2mcu
   AND NOT EXISTS (
          SELECT *
            FROM proddta.f5541052 mi
           WHERE c2.c2itm = mi.miitm
             AND c2.c2mcu = mi.mimmcu
             AND c2.c2ctry = mi.mictry
             AND c2.c2fy = mi.mify
             AND c2.c2pn = mi.mipn
             AND c2.c2ledg = mi.miledg)



/* Formatted on 2012/03/15 15:13 (Formatter Plus v4.8.8) */
SELECT ib.ibitm, ib.iblitm, ib.ibaitm, ib.ibmcu, ib.ibstkt, c2.c2ctry,
       c2.c2fy, c2.c2pn, c2.c2ledg, c2.c2uncs / 10000, c2.c2pid, c2.c2user,
       c2.c2upmj, c2.c2tday, mi.misoqs / 1000, mi.miuncs / 10000,
       mi.mi$55tmls / 10000, mi.mi$55tlwc / 10000, mi.mi$55tmwc / 10000,
       mi.mi$55tlbw / 10000, mi.mi$55tmbw / 10000, mi.micmqt / 1000,
       mi.miarec / 100, mi.mistdc / 10000
  FROM proddta.f4102 ib, proddta.f554105 c2, proddta.f5541052 mi
 WHERE ib.ibitm = c2.c2itm
   AND ib.ibmcu = c2.c2mcu
   AND c2.c2itm = mi.miitm(+)
   AND c2.c2mcu = mi.mimmcu(+)
   AND c2.c2ctry = mi.mictry(+)
   AND c2.c2fy = mi.mify(+)
   AND c2.c2pn = mi.mipn(+)
   AND c2.c2ledg = mi.miledg(+)
   AND c2.c2ledg = '02'
   AND EXISTS (
          SELECT *
            FROM proddta.f4111 il
           WHERE il.ildct = 'IC' AND ib.ibitm = il.ilitm
                 AND ib.ibmcu = il.ilmcu)

Revaluation based on weighted avg cost vs actual cost

SELECT il.ilukid, il.ilitm "ITEM", il.illitm "GBL_ITM", il.ilaitm "REG_ITM",
       il.ilmcu "B/P", il.illocn "LOCN", il.illotn "LOTN", il.ildoc "DOC",
       il.ildct "DOC_TYP", il.ildgl "GL_DATE", il.ilglpt "GL_CLASS",
       il.ildoco "ORD_NO", il.ildcto "ORD_TYP", il.iltrqt / 1000 "QTY",
       il.ilpaid / 100 "EXT_AMT", c2.c2uncs / 10000 "WT_AVG_CST",
       ROUND ((il.iltrqt / 1000) * (c2.c2uncs / 10000), 2) "EXT_AMT_WT_AVG",
       ROUND (((il.iltrqt / 1000) * (c2.c2uncs / 10000)) - (il.ilpaid / 100),2) "ADJ_WT_AVG",
       (mi.miuncs + mi.mistdc) / 10000 "AC_AVG_CST",
       ROUND ((il.iltrqt / 1000) * ((mi.miuncs + mi.mistdc) / 10000),2) "EXT_AMT_AC_AVG",
       ROUND (  ((il.iltrqt / 1000) * ((mi.miuncs + mi.mistdc) / 10000))- (il.ilpaid / 100),2) "ADJ_AC_AVG"
  FROM proddta.f4111 il, proddta.f554105 c2, proddta.f5541052 mi
 WHERE il.ilmcu = c2.c2mcu
   AND il.ilitm = c2.c2itm
   AND c2.c2mcu = mi.mimmcu(+)
   AND c2.c2itm = mi.miitm(+)
   AND c2.c2fy = mi.mify(+)
   AND c2.c2pn = mi.mipn(+)
   AND c2.c2ledg = mi.miledg(+)
   AND c2.c2ctry = mi.mictry(+)
   AND c2.c2fy = 12                                             -- Fiscal Year
   AND c2.c2pn = 1                                                   -- Period
   AND c2.c2ledg = '02'
   AND c2.c2ctry = 20
   AND il.ildct IN ('IM', 'IC')
   AND il.ildgl >= 112001
   AND il.ildgl <= 112031
   AND il.iltrqt <> 0
--ORDER BY il.ilitm, il.ilmcu

-- To Check the Adj Amount created in the Cardex -- select * from proddta.F554113 where raukid =  "ILUKID""

Check DMAAIs missing in Chart of Accounts F0901 & F4095

SELECT gm.gmco, gm.gmaid, ml.mlanum, gm.gmobj, gm.gmmcu, gm.gmsub, gm.gmdl01,
       ml.mlobj, ml.mlmcu, ml.mlsub, ml.mldct, ml.mldcto, ml.mlglpt,
       ml.mlcost, gm.gmlda, gm.gmpec
  FROM cnvdta.f0901 gm, cnvdta.f4095 ml
 WHERE ml.mlco = '00205'
   AND gm.gmmcu(+) = ml.mlmcu
   AND gm.gmobj(+) = ml.mlobj
   AND gm.gmsub(+) = ml.mlsub
   AND gm.gmco(+) = ml.mlco
   AND gmaid IS NULL

Inventory Valuation with Missing Costs - F47122 & F4105

SELECT T1.MJMCU,
       T1.MJLITM,
       T1.MJAITM,
       T1.MJITM,
       T2.IMDSC1,
       T2.COLEDG,
       T1.QTY,
       T2.STD_COST
  FROM (SELECT MJMCU, MJLITM, MJAITM, MJITM, SUM(MJTRQT / 1000) AS QTY
          FROM TESTDTA.F47122
         WHERE MJEDSP = ' '
           AND MJMCU = '     2050012'
         GROUP BY MJMCU, MJLITM, MJAITM, MJITM) T1
  LEFT OUTER JOIN (SELECT COITM,
                          COLITM,
                          COAITM,
                          IMDSC1,
                          COMCU,
                          COLEDG,
                          COUNCS / 10000 AS STD_COST
                     FROM TESTDTA.F4105, TESTDTA.F4101
                    WHERE COITM = IMITM
                      AND COLEDG = '07') T2
    ON T1.MJITM = T2.COITM
   AND T1.MJMCU = T2.COMCU

WHERE -- Comment the next 3 line if you want to see Inv Valuation
    ( COLEDG IS NULL
     OR STD_COST = 0)

Count on F30026 & F4105

select current_timestamp,ieledg, iemmcu, count(distinct(IEITM)) from proddta.F30026
where
ieledg in ('02', '01', '07') and
iexsmc >0
group by ieledg, iemmcu
order by ieledg, iemmcu

----------------------------------------------------------------------------------------------


select to_char(systimestamp, 'HH24:MI:SS.FF3'),comcu,  coledg, count(*) from proddta.F4105
where coUNCS <> 0
and coledg = '01'
group by coledg, comcu order by coledg, comcu

Costed Inventory

SELECT
    trim(T2.LIMCU),
    TRIM(T1.IMLITM),
    TRIM(T1.IMAITM),
   TRIM( T1.IMDSC1),
    T3.COLEDG,
    T2.LIGLPT,
    T3.COUNCS/10000 AS COST,
    SUM(T2.LIPQOH)/1000 AS TTL_QTY
FROM
    PRODDTA.F4101 T1,
    PRODDTA.F41021 T2
LEFT OUTER JOIN
    (
        SELECT
            *
        FROM
            PRODDTA.F4105
        WHERE
            COLEDG = '07'
    )
    T3
ON
    T2.LIITM = T3.COITM
AND T2.LIMCU = T3.COMCU
WHERE
    T1.IMITM = T2. LIITM
AND T2.LIPQOH <>0
GROUP BY
    T2.LIMCU,
    T1.IMLITM,
    T1.IMAITM,
    T1.IMDSC1,
    T3.COLEDG,
    T2.LIGLPT,
    T3.COUNCS
HAVING
    SUM(T2.LIPQOH)<>0
ORDER BY
    T2.LIMCU,
    T1.IMLITM