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'

RI Transactions that were NOT revalued - Missing Avg Cost

SELECT *
  FROM PRODDTA.F4111
 WHERE ILDGL >= 111274
   AND ILDGL <= 111304
   AND ILDCT = 'RI'
AND ilkco = '00311'
   AND ILDCTO IN ('S7',
                  'S9',
                  'SA',
                  'SE',
                  'SF',
                  'SH',
                  'SJ',
                  'SN',
                  'SQ',
                  'ST',
                  'SU',
                  'SV',
                  'SW',
                  'SX',
                  'V1',
                  'V2',
                  'V4',
                  'V7',
                  'V8',
                  'V9',
                  'VA',
                  'VF',
                  'VJ',
                  'VM',
                  'VN',
                  'VQ',
                  'VR',
                  'VS',
                  'VT',
                  'VU',
                  'VV',
                  'VX',
                  'VY')
   AND NOT EXISTS
 (SELECT * FROM PRODDTA.F554113 WHERE RAUKID = ILUKID)
 AND NOT EXISTS
 (SELECT * FROM proddta.F554105 WHERE c2ctry = 20 AND c2fy = 11 AND c2pn = 10 AND c2ledg = '02' AND c2itm = ilitm AND c2mcu = ilmcu)

RI Transactions that were NOT revalued

SELECT * FROM proddta.F4111 WHERE ildgl >= 111274 AND
ildgl <= 111304 AND
ildct = 'RI' AND
ildcto IN ('S7','S9','SA','SE','SF','SH','SJ','SN','SQ','ST','SU','SV','SW','SX','V1','V2','V4','V7','V8','V9','VA','VF','VJ','VM','VN','VQ','VR','VS','VT','VU','VV','VX','VY') AND
NOT EXISTS (SELECT * FROM proddta.F554113 WHERE raukid = ilukid)

Update STKT to M when we have activity

update ${JDE_SCHEMA}.F4102 set ibstkt = 'M'
WHERE ibstkt = 'P'
and exists (SELECT 'X'
            FROM proddta.F4111
            WHERE ilmcu = ibmcu
            AND ilitm = ibitm
            AND ildct = 'IC');