Identify Reval Records by DCTO

SELECT ilitm, illitm, ilaitm, ilmcu, illocn, illotn, ilkco, ildoc, ildct, ilicu, ildgl, ilglpt, iltrex, ilrcd, iluncs, ilpaid, ilukid  FROM  proddta.F4111, proddta.F554114
WHERE
ilukid = pcukid AND
pcev07 = 'A' AND
--ildgl >= 111244 AND
ildgl = 111273 AND
--ilitm = 300000 AND
pcuk01 IN (SELECT DISTINCT(pcuk01) FROM proddta.F554114, proddta.F554113 WHERE
rauk01 = pcuk01 AND pcev07 = 'D' AND
radcto = 'O7')

Load F5541052 based on F554105

INSERT INTO proddta.F5541052
(SELECT c2ctry, c2fy, c2pn, c2ledg, c2itm, c2litm, c2aitm, c2mcu, c2locn, c2lotn, 0,c2uncs, 0, 0, 0, 0, 0, 0, ' ', 0, 0, 0, 'SQLSYNC', 'SQLFIX', 'SQL', 'SQL', 111273, 63700  FROM proddta.F554105 WHERE
c2litm = 'C2118220' AND
c2pn = 7 AND
NOT EXISTS (SELECT * FROM proddta.F5541052 WHERE c2itm = miitm AND c2mcu = mimmcu AND c2pn = mipn AND c2ctry = mictry AND c2fy = mify))

Items with Cost Differences & Inventory between Branch plants

SELECT TRIM(A.COLITM) AS GLOBAL_ITEM#,
       TRIM(A.COAITM) AS LEGACY_ITEM#,
       TRIM(A.COLEDG) AS LEDG,
        A.COMCU "SOURCE BP",
       (A.COUNCS / 10000) "SOURCE COST",
        A1.IBSTKT,
        B.COMCU "COMPARED BP",
        (B.COUNCS / 10000) "COMPARED COST",
        B1.IBSTKT
         FROM PRODDTA.F4105 A, PRODDTA.F4102 A1, PRODDTA.F4105 B, PRODDTA.F4102 B1
        WHERE A.COITM = B.COITM
   AND A.COLEDG = B.COLEDG
   AND A.COITM = A1.IBITM
   AND A.COMCU = A1.IBMCU
   AND B.COITM = B1.IBITM
   AND B.COMCU = B1.IBMCU
   AND A.COLEDG = 'FY'
   AND A.COUNCS <>  B.COUNCS
   AND A.COMCU = '     3100011'
   AND B.COMCU like  '     310%'
   AND A.COUNCS <>  B.COUNCS
   AND EXISTS (SELECT *
          FROM PRODDTA.F41021
         WHERE B.COITM = LIITM
           AND B.COMCU = LIMCU
           AND LIPQOH <> 0)

Inventory Valuation between Ledger Types (ITM/MCU)

--WITH  COSTING TAG TABLE--

SELECT T1.LIITM,
       T2.IBLITM,
       T2.IBAITM,
       T1.LIMCU,
       SUM(T1.LIPQOH) / 1000 "ON HAND QTY",
       T4.COUNCS / 10000 "FY STD COST",
       (SUM(T1.LIPQOH) / 1000) * (T4.COUNCS / 10000) "FY INV VALUE",
       T5.COUNCS / 10000 "07 STD COST",
       (SUM(T1.LIPQOH) / 1000) * (T5.COUNCS / 10000) "07 INV VALUE",
       ((SUM(T1.LIPQOH) / 1000) * (T4.COUNCS / 10000)) -
       ((SUM(T1.LIPQOH) / 1000) * (T5.COUNCS / 10000)) "DIFF IN VALUATION"
  FROM PRODDTA.F41021  T1,
       PRODDTA.F4101   T3,
       PRODDTA.F4102   T2,
       PRODDTA.F4105   T5,
       PRODDTA.F550011 T6,
       PRODDTA.F4105   T4
 WHERE T1.LIPQOH <> 0
   AND T3.IMITM = T1.LIITM
   AND T2.IBITM = T1.LIITM
   AND T2.IBMCU = T1.LIMCU
   AND T4.COITM(+) = T1.LIITM
   AND T4.COMCU(+) = T1.LIMCU
   AND T4.COLEDG(+) = 'FY'
   AND T5.COITM = T1.LIITM
   AND T5.COMCU = T1.LIMCU
   AND T5.COLEDG = '07'
   AND T6.CTITM = T1.LIITM
   AND T6.CTMCU = T1.LIMCU
   AND T6.CTFCFLAG = '7'
 GROUP BY T1.LIITM,
          T2.IBLITM,
          T2.IBAITM,
          T1.LIMCU,
          T4.COUNCS,
          T5.COUNCS
 ORDER BY T1.LIITM, T1.LIMCU

Update SQLs for DMAAI Changes

update proddta.F4095 set mlmcu = '    

=CONCATENATE(A542,H542,"' , mlobj = '",I542,"' , mlsub= '",J542,"' where mlanum = ",B542," and mlco = '",C542,"' and mldcto = '",D542,"' and mldct = '",E542,"' and mlglpt = '",F542,"' and mlcost = '",G542,"';")

Open Work Orders by Order Type in each Branch Plant

--OPEN WORK ORDER HEADER RECORDS THAT HAD ACTIVITY (STATUS <> 99)--
SELECT WADCTO,
       WADOCO,
       WACO,
       WAMCU,
       WASRST,
       WAPPFG,
       WADRQJ,
       WAUORG,
       WASOQS,
       (WAUORG - WASOQS) " QTY INCOMPLETE",
       WASOCN
  FROM PRODDTA.F4801
 WHERE WASRST <> '99'
   AND (WASOQS <> 0 OR WASOCN <> 0 OR EXISTS
        (SELECT *
           FROM PRODDTA.F3111
          WHERE WADOCO = WMDOCO
            AND WADCTO = WMDCTO
            AND WMTRQT <> 0) OR EXISTS
        (SELECT *
           FROM PRODDTA.F3112
          WHERE WADOCO = WLDOCO
            AND WADCTO = WLDCTO
            AND (WLLABA <> 0 OR WLSETA <> 0 OR WLMACA <> 0)))
 ORDER BY WADCTO, WACO, WAMCU, WASRST, WAPPFG

----------------------------------------------------------------------------------------
--WORK ORDERS THAT HAD ACTIVITY--



SELECT WADCTO,
       WADOCO,
       WACO,
       WAMCU,
       WASRST,
       WAPPFG,
       WASOQS,
       WASOCN,
       WMTRQT,
       WLLABA,
       WLSETA,
       WLMACA
  FROM PRODDTA.F4801, PRODDTA.F3111, PRODDTA.F3112
 WHERE WADOCO = WMDOCO
   AND WADCTO = WMDCTO
   AND WADOCO = WLDOCO
   AND WADCTO = WLDCTO AND (WASOQS <> 0 OR
       WASOCN <> 0 OR
       WMTRQT <> 0 OR
       WLLABA <> 0 OR
       WLSETA <> 0 OR
       WLMACA <> 0)
 ORDER BY WADCTO, WACO, WAMCU, WASRST, WAPPFG

_____________________________________________________________________
--WORK ORDER HEADER RECORDS THAT HAD ACTIVITY--

SELECT WADCTO,
       WADOCO,
       WACO,
       WAMCU,
       WASRST,
       WAPPFG,
       WADRQJ,
       WAUORG,
       WASOQS,
       (WAUORG - WASOQS) " QTY INCOMPLETE",
       WASOCN
  FROM PRODDTA.F4801
 WHERE WASOQS <> 0
    OR WASOCN <> 0
    OR EXISTS (SELECT *
          FROM PRODDTA.F3111
         WHERE WADOCO = WMDOCO
           AND WADCTO = WMDCTO
           AND WMTRQT <> 0)
    OR EXISTS
 (SELECT *
          FROM PRODDTA.F3112
         WHERE WADOCO = WLDOCO
           AND WADCTO = WLDCTO
           AND (WLLABA <> 0 OR WLSETA <> 0 OR WLMACA <> 0))
 ORDER BY WADCTO, WACO, WAMCU, WASRST, WAPPFG

Inventory Valuation - Compare between Ledger Types (ITM/MCU/GLPT/LOCN)

--WITH  COSTING TAG TABLE--

SELECT T1.LIITM,
       T2.IBLITM,
       T2.IBAITM,
       T1.LIMCU,
       T1.LILOCN,
       T1.LIGLPT,
       SUM(T1.LIPQOH) / 1000 "ON HAND QTY",
       T4.COUNCS / 10000 "FY STD COST",
       (SUM(T1.LIPQOH) / 1000) * (T4.COUNCS / 10000) "FY INV VALUE",
       T5.COUNCS / 10000 "07 STD COST",
       (SUM(T1.LIPQOH) / 1000) * (T5.COUNCS / 10000) "07 INV VALUE",
       ((SUM(T1.LIPQOH) / 1000) * (T4.COUNCS / 10000)) -
       ((SUM(T1.LIPQOH) / 1000) * (T5.COUNCS / 10000)) "DIFF IN VALUATION"
  FROM PRODDTA.F41021  T1,
       PRODDTA.F4101   T3,
       PRODDTA.F4102   T2,
       PRODDTA.F4105   T5,
       PRODDTA.F550011 T6,
       PRODDTA.F4105   T4
 WHERE T1.LIPQOH <> 0
   AND T3.IMITM = T1.LIITM
   AND T2.IBITM = T1.LIITM
   AND T2.IBMCU = T1.LIMCU
   AND T4.COITM(+) = T1.LIITM
   AND T4.COMCU(+) = T1.LIMCU
   AND T4.COLEDG(+) = 'FY'
   AND T5.COITM = T1.LIITM
   AND T5.COMCU = T1.LIMCU
   AND T5.COLEDG = '07'
   AND T6.CTITM = T1.LIITM
   AND T6.CTMCU = T1.LIMCU
   AND T6.CTFCFLAG = '7'
 GROUP BY T1.LIITM,
          T2.IBLITM,
          T2.IBAITM,
          T1.LIMCU,
          T1.LILOCN,
          T1.LIGLPT,
          T4.COUNCS,
          T5.COUNCS
 ORDER BY T1.LIITM, T1.LIMCU, LILOCN

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

--WITH OUT COSTING TAG TABLE--


SELECT T1.LIITM,
       T2.IBLITM,
       T2.IBAITM,
       T1.LIMCU,
       T1.LILOCN,
       T1.LIGLPT,
       SUM(T1.LIPQOH) / 1000 "ON HAND QTY",
       T4.COUNCS / 10000 "FY STD COST",
       (SUM(T1.LIPQOH) / 1000) * (T4.COUNCS / 10000) "FY INV VALUE",
       T5.COUNCS / 10000 "07 STD COST",
       (SUM(T1.LIPQOH) / 1000) * (T5.COUNCS / 10000) "07 INV VALUE",
       ((SUM(T1.LIPQOH) / 1000) * (T4.COUNCS / 10000)) -
       ((SUM(T1.LIPQOH) / 1000) * (T5.COUNCS / 10000)) "DIFF IN VALUATION"
  FROM PRODDTA.F41021  T1,
       PRODDTA.F4101   T3,
       PRODDTA.F4102   T2,
       PRODDTA.F4105   T4,
       PRODDTA.F4105   T5,
       PRODDTA.F550011 T6
 WHERE T1.LIPQOH <> 0
   AND T3.IMITM = T1.LIITM
   AND T2.IBITM = T1.LIITM
   AND T2.IBMCU = T1.LIMCU
   AND T4.COITM = T1.LIITM
   AND T4.COMCU = T1.LIMCU
   AND T4.COLEDG = 'FY'
   AND T5.COITM = T1.LIITM
   AND T5.COMCU = T1.LIMCU
   AND T5.COLEDG = '07'
   AND T6.CTITM = T1.LIITM
   AND T6.CTMCU = T1.LIMCU
   AND T6.CTFCFLAG = '7'
 GROUP BY T1.LIITM,
          T2.IBLITM,
          T2.IBAITM,
          T1.LIMCU,
          T1.LILOCN,
          T1.LIGLPT,
          T4.COUNCS,
          T5.COUNCS
 ORDER BY T1.LIITM, T1.LIMCU

Inventory Valuation

SELECT T1.LIITM,
       T2.IBLITM,
       T2.IBAITM,
       T1.LIMCU,
       T1.LILOCN,
       T1.LIGLPT,
       SUM(T1.LIPQOH) / 1000 "ON HAND QTY",
       T4.COUNCS / 10000 "FY STD COST",
       (SUM(T1.LIPQOH) / 1000) * (T4.COUNCS / 10000) "FY INV VALUE"
  FROM PRODDTA.F41021 T1,
       PRODDTA.F4101  T3,
       PRODDTA.F4102  T2,
       PRODDTA.F4105  T4
 WHERE T1.LIPQOH <> 0
   AND T3.IMITM = T1.LIITM
   AND T2.IBITM = T1.LIITM
   AND T2.IBMCU = T1.LIMCU
   AND T4.COITM = T1.LIITM
   AND T4.COMCU = T1.LIMCU
   AND T4.COLEDG = 'FY'
 GROUP BY T1.LIITM,
          T2.IBLITM,
          T2.IBAITM,
          T1.LIMCU,
          T1.LILOCN,
          T1.LIGLPT,
          T4.COUNCS
 ORDER BY T1.LIITM, T1.LIMCU

Update LITM and AITM in F30026 from F4102

UPDATE BFDTA.F30026 A
   SET IELITM =
       (SELECT IBLITM
          FROM BFDTA.F4102 B
         WHERE A.IEITM = B.IBITM
           AND A.IEMMCU = B.IBMCU
           AND A.IELITM = ' '),
       IEAITM =
       (SELECT IBAITM
          FROM BFDTA.F4102 B
         WHERE A.IEITM = B.IBITM
           AND A.IEMMCU = B.IBMCU
           AND A.IEAITM = ' ')
 WHERE A.IELITM = ' '
   AND A.IEAITM = ' '

Standard Cost - Behind the Scenes

• Standard cost is comprised of two key tables:  the cost ledger (F4105) and the cost components table (F30026)
• The cost components table contains the material, labor, outside processing, over head and extras components that make up the standard cost.  This table also contains both the simulated and frozen values for these components.
• The cost ledger table contains the sum total of these components.
• Did you know that shop floor control transactions such as material issues and work order completions hit the cardex with the standard (07) cost in the cost ledger (F4105) while the journal entries that are written for these transactions are updated with the cost from the cost components (F30026) table?
• How can this impact you?
• If these two tables get out of sync, then it can cause an integrity issue between the Cardex (F4111) and the General Ledger (F0911) making it very difficult to reconcile inventory at month end.
• What can you do to prevent this?
• Never update the standard (07) cost in the cost ledger (F4105) manually.  Always use the simulated and frozen cost programs UBE's to update the standard cost.
• There is a processing option that can secure this field from being updated manually.

Compare Costs between F30026 Vs F4105

SELECT IEITM,
       IELITM,
       IEAITM,
       IEMMCU,
       IELEDG,
       CTCCFLAG,
       SUM(IECSL) / 10000 AS F30026_COST,
       COUNCS / 10000 AS F4105_COST
  FROM BFDTA.F30026, BFDTA.F4105, BFDTA.F550011
 WHERE IEITM = COITM
   AND IEMMCU = COMCU
   AND IELEDG = COLEDG
   AND IEITM = CTITM
   AND IEMMCU = CTMCU
   AND CTCCFLAG = '7'
   AND IELEDG = '07'
 GROUP BY IEITM, IELITM, IEAITM, IEMMCU, IELEDG, COUNCS, CTCCFLAG
HAVING(SUM(IECSL)) / 10000 <> COUNCS / 10000
 ORDER BY IEMMCU, IEAITM

________________________________________________________________________



SELECT IEITM,
       IELITM,
       IEAITM,
       IEMMCU,
       IELEDG,
       SUM(IECSL) / 10000 AS F30026_COST,
       COUNCS / 10000 AS F4105_COST
  FROM BFDTA.F30026, BFDTA.F4105
 WHERE IEITM = COITM
   AND IEMMCU = COMCU
   AND IELEDG = COLEDG
   AND IELEDG = '07'
 GROUP BY IEITM, IELITM, IEAITM, IEMMCU, IELEDG, COUNCS
HAVING(SUM(IECSL)) / 10000 <> COUNCS / 10000
 ORDER BY IEMMCU, IEAITM

Find *OP items in TPM Branches but not in Interlagos

SELECT *
  FROM PRODDTA.F4102 T1, PRODDTA.F4101 T3
 WHERE T1.IBITM = T3.IMITM
   AND T1.IBMCU LIKE '     310%4' --TPM Branch Plants end with a 4
   AND T1.IBLITM LIKE '%*OP%' -- *OP Items
   AND NOT EXISTS (SELECT *
          FROM PRODDTA.F4102 T2
         WHERE T2.IBMCU = '     3100011'
           AND T2.IBLITM LIKE '%*OP%'
           AND T1.IBITM = T2.IBITM)
 ORDER BY IBUPMJ ASC

Check NEGINV Location for all Branch Plants

SELECT *
  FROM PRODDTA.F0006
 WHERE MCSTYL = 'BP'
   AND MCCO IN ('00310', '00311')
   AND NOT EXISTS (SELECT *
          FROM PRODDTA.F4100
         WHERE LMLOCN = 'NEGINV'
           AND LMMCU = MCMCU);