Transaction Nature not in UDC 55|FA

SELECT *
  FROM proddta.f43121, proddta.f76432
 WHERE prdoco = pjdoco
   AND prdcto = pjdcto
   AND prkcoo = pjkcoo
   AND prlnid = pjlnid
   AND prsfxo = pjsfxo
   AND prmatc IN ('1', '2')
   and not exists (select * from prodctl.F0005 where drsy = '55' and drrt = 'FA' and trim(drky) = (trim(pjbnop)||trim(pjbsop)))

NO OV and Missing base closing period Cost

SELECT initm, inlitm, inaitm, c0mmcu, SUM(incmqt+innq01+innq02)/1000 FROM qadta.F41112, qadta.F554103
WHERE inlitm IN ( 'F241121', 'F214230', 'F212437', 'F212436', 'F212434', 'F212431', 'F212418', 'F212416', 'F211726', 'F211546', 'F211142', 'F210736', 'F204126', 'F364418', 'F365363', 'F364047', 'F371114', 'F367663', 'F367660', 'F374825', 'F377294', 'F366303', 'F381175', 'F354645', 'F361475', 'F364099', 'F364092', 'F364091', 'F364102', 'F364098', 'F364051', 'F370191', 'F194833', 'F368877', 'F374831', 'F374829', 'F374828', 'F374827', 'F374826', 'F374825', 'F374824', 'F374823', 'F374821', 'F374820', 'F378363', 'F375729', 'F186007', 'F185200', 'F216748', 'F216361', 'F213715', 'F282245', 'F218505', 'F352104', 'F339158', 'F339014', 'F299115', 'F230749', 'F224454', 'F223621', 'F221874', 'F221830', 'S9512', 'F212437', 'F212430', 'F212415', 'F212413', 'F211181', 'F210690', 'F194219', 'F204148', 'F370189', 'F194818', 'F368873', 'F373999', 'F375729', 'F203040', 'F376324', 'F380404', 'F380435', 'F381175', 'F203052', 'F193923', 'F212452', 'F212443', 'F212436', 'F212428', 'F166127', 'F186521', 'F192831', 'F361271', 'F204253', 'F375732', 'F375729', 'F236959', 'F277126', 'F342680', 'F348463', 'F290383', 'F367525', 'F203037')
AND infy = 12
AND inmcu = c0mcu
AND NOT EXISTS (SELECT * FROM qadta.F554105 WHERE c2pn = 2 AND c2fy = 12 AND initm = c2itm AND inmcu = c2mcu)
AND NOT EXISTS (SELECT * FROM qadta.F4111 WHERE ildct = 'OV' AND initm = ilitm AND inmcu = ilmcu AND ildgl <= 112060 )
GROUP BY initm, inlitm, inaitm, c0mmcu
HAVING SUM(incmqt+innq01+innq02)/1000 <> 0

Items with Inv in F41112 and missing Base closing period costs

SELECT   indct, infy, inctry, initm, inlitm, inaitm, inmcu, inglpt,
         (SUM (innq01 + innq02)) / 1000
    FROM proddta.f41112
   WHERE infy = 12
     AND inctry = 20
     AND indct = 'BF'
     AND NOT EXISTS (
            SELECT *
              FROM proddta.f554105
             WHERE initm = c2itm
               AND inmcu = c2mcu
               AND c2ctry = 20
               AND c2fy = 12
               AND c2pn = 2
               AND c2ledg = '02'
               AND c2uncs <> 0)
GROUP BY indct, infy, inctry, initm, inlitm, inaitm, inmcu, inglpt
  HAVING SUM (innq01 + innq02) <> 0;

Copy F41021 from other Branch Plant

INSERT INTO testdta.F41021
(SELECT LIITM,'     2050405' ,LILOCN,LILOTN,LIPBIN,LIGLPT,LILOTS,LILRCJ,LIPQOH,LIPBCK,LIPREQ,LIQWBO,LIOT1P,LIOT2P,LIOT1A,LIHCOM,LIPCOM,LIFCOM,LIFUN1,LIQOWO,LIQTTR,LIQTIN,LIQONL,LIQTRI,LIQTRO,LINCDJ,LIQTY1,LIQTY2,LIURAB,LIURRF,LIURAT,LIURCD,LIJOBN,'C58_2B3',LIUPMJ,LIUSER,LITDAY,LIURDT,LIQTO1,LIQTO2,LIHCMS,LIPJCM,LIPJDM,LISCMS,LISIBW,LISOBW,LISQOH,LISQWO,LISREQ,LISWHC,LISWSC,LICHDF,LIWPDF,LICFGSID FROM testdta.F41021 WHERE  limcu = '     2050012' );

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""