Compare costs between Master Branch plant (KWP) Vs other branch plants

SELECT IB.ibitm, IB.iblitm, IB.ibaitm, IB.ibmcu,
IE1.ieledg,
sum( IE1.iexscr)/10000 "Simulated Cost",
(select sum(IE3.iexscr)/10000  from uspddta.F30026 IE3 where IE3.IEITM = IE1.IEITM AND IE3.IEledg = IE1.ieledg and IE3.IEMMCU = '         KWP') "Simulated_Cost_KWP",
sum(IE1.iecsl)/10000 "Frozen_Cost",
(select sum(IE2.iecsl)/10000  from uspddta.F30026 IE2 where IE2.IEITM = IE1.IEITM AND IE2.IEledg = IE1.ieledg and IE2.IEMMCU = '         KWP') "Frozen_Cost_KWP",
(SELECT CO1.councs/10000 FROM uspddta.F4105 CO1 WHERE  CO1.COITM = IB.ibitm
AND CO1.COMCU = IB.ibmcu AND CO1.coledg = IE1.ieledg ) "std cost" ,
(SELECT CO2.councs/10000 FROM uspddta.F4105 CO2 WHERE  CO2.COITM = IB.ibitm
AND CO2.COMCU = '         KWP' AND CO2.coledg = IE1.ieledg ) "Std_Cost_KWP" ,
(select sum(lipqoh)/100000 from uspddta.F41021 LI where LI.LIITM = IB.IBITM and LI.LIMCU = IB.IBMCU ) "Inv_Onhand"
FROM uspddta.F4102 IB,uspddta.F30026 IE1
WHERE IB.ibitm = IE1.ieitm AND IB.ibmcu = IE1.iemmcu AND IE1.ieledg in('99', '07' )
AND IB.IBLITM LIKE '8%'  and IBLITM not like '8I%'
and IB.IBMCU <>  '         KWP'
GROUP BY IB.ibitm,IE1.IEITM, IB.iblitm, IB.ibaitm, IB.ibmcu, IE1.ieledg
ORder BY IB.ibitm,IE1.IEITM, IB.iblitm, IB.ibaitm, IB.ibmcu, IE1.ieledg;

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;