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);

Find PPV Transactions

SELECT
T1.ILLITM,
T1.ILAITM,
T1.ILMCU,
T1.ILLOCN,
T1.ILLOTN,
T1.ILKCO,
T1.ILDOC,
T1.ILDCT,
T1.ILDGL,
T1.ILGLPT,
T1.ILDCTO,
T1.ILDOCO,
T1.ILPAID,
T2.CFDGJ,
T2.CFANI,
T2.CFICU

TESTDTA.F554199 T2

T1.ILUKID,FROM TESTDTA.F4111 T1,WHERE T1.ILUKID = T2.CFUKIDAND T2.CFDGL >= 111274AND T2.CFDGL <= 111304AND t1.ilmcu = ' 3100011'

Compare Exchange Rates on Receipts Vs Exchange Rates table

SELECT T1.PRMATC,
       T1.PRAN8,
       T1.PRKCOO,
       T1.PRDOCO,
       T1.PRDCTO,
       T1.PRKCO,
       T1.PRDOC,
       T1.PRDCT,
       T1.PRSFXO,
       T1.PRLNID,
       T1.PRNLIN,
       T1.PRALIN,
       T1.PRITM,
       T1.PRLITM,
       T1.PRAITM,
       T1.PRMCU,
       T1.PRGLC,
       (SELECT TO_CHAR(TO_DATE(T1.PRDGL + 1900000, 'YYYYDDD'), 'YYYY-MM-DD')
          FROM DUAL) AS GL_DATE,
       (SELECT TO_CHAR(TO_DATE(T1.PRRCDJ + 1900000, 'YYYYDDD'), 'YYYY-MM-DD')
          FROM DUAL) AS RECEIPT_DATE,
       T1.PRCRCD,
       T1.PRBCRC,
       T1.PRCRR,
       (SELECT CXCRR
          FROM PRODDTA.F0015 T2
         WHERE T1.PRRCDJ >= T2.CXEFT
           AND T1.PRCRCD = T2.CXCRCD
           AND T1.PRBCRC = T2.CXCRDC
           AND ROWNUM < 2) "Exchange Rate",
       T1.PRAREC / 100,
       T1.PRFREC / 100
  FROM PRODDTA.F43121 T1
 WHERE T1.PRCRCD <> T1.PRBCRC
   AND T1.PRBCRC <> ' '
   AND T1.PRMATC = 1
 --  AND T1.PRDOCO = 22169
 ORDER BY T1.PRRCDJ

Compare Costs between Cost Methods

SELECT
trim(a.COMCU) as BP,
trim(a.COLITM) as GLOBAL_ITEM#,
trim(a.coaitm) AS LEGACY_ITEM#,
(a.COUNCS/10000) "01",
b.councs/10000 as "02",
c.councs/10000 as "07"
FROM qaDTA.F4105 a, qadta.f4105 b, qadta.f4105 c
WHERE a.colitm = b.colitm
and a.COmcu = b.comcu
and a.colitm = c.colitm
and a.comcu = c.comcu
and b.colitm = c.colitm
and b.comcu = c.comcu
and a.coledg = '01'
and b.coledg = '02'
and c.coledg = '07'
and a.comcu = '     3110082'

Compare Costs between Branch Plants

SELECT TRIM(A.COLITM) AS GLOBAL_ITEM#,
       TRIM(A.COAITM) AS LEGACY_ITEM#,
TRIM(A.COLEDG) AS LEDG,
       (A.COUNCS / 10000) "3110022",
       (B.COUNCS / 10000)  "3110032",
       (C.COUNCS / 10000)  "3110042"
  FROM PRODDTA.F4105 A, PRODDTA.F4105 B, PRODDTA.F4105 C
 WHERE A.COITM = B.COITM
   AND A.COITM = C.COITM
   AND A.COLEDG = B.COLEDG
   AND A.COLEDG = C.COLEDG
   AND A.COLEDG = '07'
   AND A.COMCU = '     3110022'
   AND B.COMCU = '     3110032'
   AND C.COMCU = '     3110042'