Standard Cost Restatement Inv On Hand - F30026 Vs F4105 07 Vs FY

SELECT IBITM,
       IBLITM,
       IBAITM,
       IBMCU,
       IBGLPT,
       LIGLPT,
       SUM(LIPQOH) / 1000,
       NVL((SELECT SUM(IECSL) / 10000
             FROM PRODDTA.F30026
            WHERE IBITM = IEITM
              AND IBMCU = IEMMCU
              AND IELEDG = '07'
              AND IELOCN = ' '
              AND IELOTN = ' '),
           0) "F30026_07_COST",
       NVL((SELECT SUM(IECSL) / 10000
             FROM PRODDTA.F30026
            WHERE IBITM = IEITM
              AND IBMCU = IEMMCU
              AND IELEDG = 'FY'
              AND IELOCN = ' '
              AND IELOTN = ' '),
           0) "F30026_FY_COST",
       NVL((SELECT SUM(COUNCS) / 10000
             FROM PRODDTA.F4105
            WHERE IBITM = COITM
              AND IBMCU = COMCU
              AND COLEDG = '07'
              AND COLOCN = ' '
              AND COLOTN = ' '),
           0) "F4105_07_COST",
       NVL((SELECT SUM(COUNCS) / 10000
             FROM PRODDTA.F4105
            WHERE IBITM = COITM
              AND IBMCU = COMCU
              AND COLEDG = 'FY'
              AND COLOCN = ' '
              AND COLOTN = ' '),
           0) "F4105_FY_COST"
  FROM PRODDTA.F4102, PRODDTA.F41021
 WHERE LIPQOH <> 0
   AND IBITM = LIITM
   AND IBMCU = LIMCU
 GROUP BY IBITM, IBLITM, IBAITM, IBMCU, IBGLPT, LIGLPT
HAVING SUM(LIPQOH) <> 0;

Find SID

select sid, osuser, machine, b.username, b.module, b.process, executions, sql_text
from v$process a, v$session b, v$sqlarea c
where b.sql_address=c.address
and b.status='ACTIVE'
and b.type not in ('BACKGROUND')
and b.paddr= a.addr
and a.addr = b.paddr
and b.sql_id = c.sql_id
and b.process = '<wsj process id>' 

Asof Integrity Check with Cardex - AMOUNTs

SELECT

INITM,INMCU,

SUM(INCUMA + INAN01 + INAN02 + INAN03 + INAN04 + INAN05 + INAN06 +INAN07 + INAN08 + INAN09 + INAN10 + INAN11 + INAN12 + INAN13 +
INAN14) /
100 "Asof_Amount",(
SELECT SUM(ILPAID) / 100FROM PRODDTA.F4111
WHERE INITM = ILITM
AND INMCU = ILMCU
AND ILDGL >= 112001AND ILIPCD = 'Y'GROUP BY ILITM, ILMCU) "Cardex_AMT"
FROM PRODDTA.F41112
WHERE INITM IN (422012, 424742)
AND INDCT = 'BF'AND INCTRY = 20AND INFY = 12GROUP BY INITM, INMCU;

SOA with JD Edwards Real Time Events

This article provides basic steps to configure a Real time event in JDE and consume in Oracle SOA server.

http://gauravguptax.blogspot.com/2012/08/soa-with-jd-edwards-real-time-events.html?goback=%2Egde_114133_member_154503430

Cost Changes

SELECT T0.CMITM,
       T0.CMLITM,
       T0.CMAITM,
       T0.CMMCU,
       T0.CMSRCCNTMJ,
       T0.CMSRCCNTMI,
       T1.IEITM,
       T1.IELITM,
       T1.IEAITM,
       T1.IEMMCU,
       T1.IELOCN,
       T1.IELOTN,
       T1.IELEDG,
       T1.IECOST,
       T1.IELOTG,
       T1.IESTDC,
       T1.IEXSMC,
       T1.IECSL,
       T1.IEXSCR,
       T1.IESCTC,
       T1.IEXSFC,
       T1.IESTFC,
       T1.IEXSF,
       T1.IERATS,
       T1.IEXSRC,
       T1.IERTSD,
       T1.IEXSR,
       T1.IEPFLG,
       T1.IEUSER,
       T1.IEPID,
       T1.IEJOBN,
       T1.IEUPMJ,
       T1.IETDAY,
       T1.IEOPSQ,
       T1.IEMCUL,
       T1.IEWMCU,
       T1.IELDA,
       T1.IETBM,
       T1.IEACQ,
       T1.IEF,
       T2.COITM,
       T2.COLITM,
       T2.COAITM,
       T2.COMCU,
       T2.COLOCN,
       T2.COLOTN,
       T2.COLOTG,
       T2.COLEDG,
       T2.COUNCS,
       T2.COCSPO,
       T2.COCSIN,
       T2.COURCD,
       T2.COURDT,
       T2.COURAT,
       T2.COURAB,
       T2.COURRF,
       T2.COUSER,
       T2.COPID,
       T2.COJOBN,
       T2.COUPMJ,
       T2.COTDAY,
       T2.COCCFL,
       T2.COCRCS,
       T2.COOSTC,
       T2.COSTOC,
       T3.MCMCU,
       T3.MCSTYL,
       T3.MCCO
  FROM TESTDTA.F554101T T0,
       TESTDTA.F30026   T1,
       TESTDTA.F4105    T2,
       TESTDTA.F0006    T3
 WHERE ((T1.IEUPMJ >= 112160 AND T1.IELEDG = '07' AND T1.IECOST = 'A1' AND
       T3.MCCO = '00100' OR T3.MCSTYL = 'BP' AND T3.MCSTYL = 'VP'))
   AND (T0.CMITM = T1.IEITM AND T0.CMMCU = T1.IEMMCU AND
       T1.IEITM = T2.COITM AND T1.IEMMCU = T2.COMCU AND
       T1.IELEDG = T2.COLEDG AND T0.CMMCU = T3.MCMCU)
 ORDER BY T2.COITM ASC, T2.COMCU ASC

As of Std Cost vs Item Std Cost

SELECT INMCU "BRANCH_PLANT",
       INLITM "GLOBAL_CODE",
       INAITM "LEGACY_CODE",
       (SELECT IMDSC1 FROM PRODDTA.F4101 WHERE INITM = IMITM) "ITEM_DESCRIPTION",
       INGLPT "GL_CATEGORY",
       INLOCN "LOCATION",
       INLOTN "LOT",
       INNQ08 / 1000 "QUANTITY",
       INAN08 / 100 "AMOUNT",
       (INAN08 / 100) / (INNQ08 / 1000) "AS_OF_STD_COST",
       (SELECT COUNCS / 10000
          FROM PRODDTA.F4105
         WHERE INITM = COITM
           AND INMCU = COMCU
           AND COLEDG = '07'
           AND COLOCN = ' '
           AND COLOTN = ' ') "ITEM_STD_COST"
  FROM PRODDTA.F41112
 WHERE INDCT = 'BF'
   AND INFY = 12
   AND INCTRY = 20
   AND INMCU LIKE '     205%'
   AND INNQ08 <> 0;

Transaction Nature & Suffix F7611B Vs F76432 Integrity

SELECT T1.PRITM,
       T1.PRLITM,
       T1.PRMCU,
       T1.PRKCOO,
       T1.PRDOCO,
       T1.PRDCTO,
       T1.PRLNID,
       T1.PRSFXO,
       T1.PRDOC,
       T1.PRDCT,
       T1.PRGLC,
       (SELECT T2.PJBNOP || T2.PJBSOP
          FROM PRODDTA.F76432 T2
         WHERE T1.PRDOCO = T2.PJDOCO
           AND T1.PRDCTO = T2.PJDCTO
           AND T1.PRLNID = T2.PJLNID
           AND T1.PRKCOO = T2.PJKCOO
           AND T1.PRSFXO = T2.PJSFXO) "F76432_Trans_Nat_Sfx",
       (T3.FDBNOP || T3.FDBSOP) "F7611B_Trans_Nat_Sfx",
       (SELECT TO_DATE(CONCAT('20',
                              CONCAT(SUBSTR(TO_CHAR(T1.PRTRDJ), 2, 2),
                                     SUBSTR(TO_CHAR(T1.PRTRDJ), 4, 3))),
                       'YYYYDDD')
          FROM DUAL) AS TRANSACTIONDATE,
       (SELECT TO_DATE(CONCAT('20',
                              CONCAT(SUBSTR(TO_CHAR(T1.PRDGL), 2, 2),
                                     SUBSTR(TO_CHAR(T1.PRDGL), 4, 3))),
                       'YYYYDDD')
          FROM DUAL) AS GLDATE,
       SUM(T1.PRQTYS / 1000),
       SUM(T1.PRAREC / 100)
  FROM PRODDTA.F43121 T1, PRODDTA.F7611B T3
 WHERE T1.PRMATC = 1
   AND T1.PRDCT = 'OV'
   AND T1.PRDGL >= 112153
   AND T1.PRDGL <= 112182
   AND T1.PRDOCO = 138019
   AND T1.PRDOCO = T3.FDDOCO
   AND T1.PRDCTO = T3.FDPDCT
   AND T1.PRKCOO = T3.FDKCOO
   AND T1.PRLNID = T3.FDLNID
   AND T1.PRSFXO = T3.FDSFXO
   AND T1.PRDCT = T3.FDDCTO
   AND T1.PRDOC = T3.FDDOC
 GROUP BY T1.PRITM,
          T1.PRLITM,
          T1.PRMCU,
          T1.PRKCOO,
          T1.PRDOCO,
          T1.PRDCTO,
          T1.PRDOC,
          T1.PRDCT,
          T1.PRGLC,
          T1.PRTRDJ,
          T1.PRDGL,
          T1.PRLNID,
          T1.PRSFXO,
          T3.FDBNOP,
          T3.FDBSOP
 ORDER BY T1.PRMCU, T1.PRDOCO, T1.PRDCTO;