Verify Begining Balances in Item As Off F41112

SELECT T1.INITM,
       T1.INLITM,
       T1.INAITM,
       T1.INMCU,
       ((SUM(T1.INCMQT) + SUM(T1.INNQ01) + SUM(T1.INNQ02) + SUM(T1.INNQ03) +
       SUM(T1.INNQ04) + SUM(T1.INNQ05) + SUM(T1.INNQ06) + SUM(T1.INNQ07) +
       SUM(T1.INNQ08) + SUM(T1.INNQ09) + SUM(T1.INNQ10) + SUM(T1.INNQ11) +
       SUM(T1.INNQ12)) / 1000) "11 BALANCE_QTY",
       SUM(T2.INCMQT / 1000) "12 Begining Balance"
  FROM PRODDTA.F41112 T1, PRODDTA.F41112 T2
 WHERE T1.INDCT = 'BF'
   AND T1.INFY = 11
   AND T1.INCTRY = 20
--   AND T1.INMCU = '     3100011'
  -- AND T1.INLITM IN ('F216922', 'F354448')
   AND T1.INITM = T2.INITM
   AND T1.INMCU = T2.INMCU
   AND T1.INDCT = T2.INDCT
   AND T1.INCTRY = T2.INCTRY
   AND T1.INLOCN = T2.INLOCN
   AND T1.INLOTN = T2.INLOTN
   AND T1.INGLPT = T2.INGLPT
   AND T2.INFY = 12
 GROUP BY T1.INITM, T1.INLITM, T1.INAITM, T1.INMCU

HAVING (((SUM(T1.INCMQT) + SUM(T1.INNQ01) + SUM(T1.INNQ02) + SUM(T1.INNQ03) +
       SUM(T1.INNQ04) + SUM(T1.INNQ05) + SUM(T1.INNQ06) + SUM(T1.INNQ07) +
       SUM(T1.INNQ08) + SUM(T1.INNQ09) + SUM(T1.INNQ10) + SUM(T1.INNQ11) +
       SUM(T1.INNQ12)) / 1000))<>
       (SUM(T2.INCMQT / 1000))

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