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;

F4111 Vs F30026 broken down by Cost Element

SELECT T0.IBITM,
       T0.IBLITM,
       T0.IBAITM,
       IM.IMDSC1,
       T0.IBMCU,
       T0.IBSRP6,
       T0.IBPRP5,
       T0.IBSTKT,
       T1.ILITM,
       T1.ILMCU,
       T1.ILMMCU,
       T1.ILDOC,
       T1.ILDCT,
       T1.ILDGL,
       T1.ILGLPT,
       T1.ILDCTO,
       T1.ILDOCO,
       T1.ILKCOO,
       T3.IECOST,
       T3.IECSL / 10000 "H1_Cost",
       T1.ILUNCS / 10000 "Unit_Cost",
       T1.ILTRQT / 1000 "Quantity",
       T1.ILPAID / 100 "Amount",
       T1.ILUKID,
       T1.ILAID,
       T1.ILAN8,
       T1.ILSHAN
  FROM PRODDTA.F4101  IM,
       PRODDTA.F4102  T0,
       PRODDTA.F4111  T1,
       PRODDTA.F30026 T3
 WHERE T1.ILDCT = 'OV'
   AND T1.ILDCTO = 'ON'
   AND T1.ILMCU < '     1000998'
   AND T1.ILGLPT <> 'IN61'
   AND T1.ILTRQT <> 0
   AND T1.ILDGL >= 111092
   AND T1.ILDGL <= 112092
   AND ROWNUM <= 10 --Comment this line if you want all records
   AND IM.IMITM = T0.IBITM
   AND T0.IBITM = T1.ILITM
   AND T0.IBMCU = T1.ILMCU
   AND IEITM = T0.IBITM
   AND IEMMCU = '     1000999'
   AND IELEDG = 'H1'
   AND IELOCN = ' '
   AND IELOTN = ' '
 ORDER BY T0.IBITM ASC;

Reversals in Current month Vs Receipts in Prior Month

SELECT T1.PRITM,
       T1.PRLITM,
       T1.PRMCU,
       T1.PRDOCO,
       T1.PRDCTO,
       T1.PRLNID,
       T1.PRSFXO,
       T1.PRDOC,
       T1.PRDCT,
       T1.PRGLC,
       T2.PJBNOP,
       T2.PJBSOP,
       (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.F76432 T2
 WHERE T1.PRMATC = 1
   AND T1.PRDCT = 'OV'
   AND T1.PRDGL >= 112153
   AND T1.PRDGL <= 112182
   AND T1.PRDOCO = T2.PJDOCO
   AND T1.PRDCTO = T2.PJDCTO
   AND T1.PRLNID = T2.PJLNID
   AND T1.PRDOCO = 138019
   AND EXISTS (SELECT 'X'
          FROM PRODDTA.F43121 T3
         WHERE T1.PRDOCO = T3.PRDOCO
           AND T1.PRDCTO = T3.PRDCTO
           AND T1.PRLNID = T3.PRLNID
           AND T3.PRMATC = 4
           AND T3.PRDGL >= 112183
           AND T3.PRDGL <= 112213)
 GROUP BY T1.PRITM,
          T1.PRLITM,
          T1.PRMCU,
          T1.PRDOCO,
          T1.PRDCTO,
          T1.PRDOC,
          T1.PRDCT,
          T1.PRGLC,
          T1.PRTRDJ,
          T1.PRDGL,
          T1.PRLNID,
          T1.PRSFXO,
          T2.PJBNOP,
          T2.PJBSOP
 ORDER BY T1.PRMCU, T1.PRDOCO, T1.PRDCTO;


SELECT T1.PRITM,
       T1.PRLITM,
       T1.PRMCU,
       T1.PRDOCO,
       T1.PRDCTO,
       T1.PRLNID,
       T1.PRSFXO,
       T1.PRDOC,
       T1.PRDCT,
       T1.PRGLC,
       (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
         WHERE T1.PRDOCO = 138019
           AND T1.PRDCTO = 'OP'
           AND T1.PRMATC = 4
           AND T1.PRDGL >= 112183
           AND T1.PRDGL <= 112213
            GROUP BY T1.PRITM,
          T1.PRLITM,
          T1.PRMCU,
          T1.PRDOCO,
          T1.PRDCTO,
          T1.PRDOC,
          T1.PRDCT,
          T1.PRGLC,
          T1.PRTRDJ,
          T1.PRDGL,
          T1.PRLNID,
          T1.PRSFXO;

Compare Cardex Transactions with 07 Vs H1 Costs

SELECT T0.IBITM,
       T0.IBLITM,
       T0.IBAITM,
       T0.IBMCU,
       T0.IBSRP6,
       T0.IBPRP5,
       T0.IBSTKT,
       T1.ILITM,
       T1.ILMCU,
       T1.ILMMCU,
       T1.ILDOC,
       T1.ILDCT,
       T1.ILDGL,
       T1.ILGLPT,
       T1.ILDCTO,
       T1.ILDOCO,
       T1.ILKCOO,
       T1.ILTRQT / 1000,
       T1.ILPAID / 100,
       T1.ILUKID,
       T1.ILAID,
       T1.ILAN8,
       T1.ILSHAN,
       (SELECT SUM(IECSL / 10000)
          FROM PRODDTA.F30026
         WHERE IEITM = T0.IBITM
           AND IEMMCU = '     1000999'
           AND IELEDG = '07'
           AND IELOCN = ' '
           AND IELOTN = ' '
         GROUP BY IEITM, IEMMCU, IELEDG, IELOCN, IELOTN) "07_COST",
       (SELECT SUM(IECSL / 10000)
          FROM PRODDTA.F30026
         WHERE IEITM = T0.IBITM
           AND IEMMCU = '     1000999'
           AND IELEDG = 'H1'
           AND IELOCN = ' '
           AND IELOTN = ' '
         GROUP BY IEITM, IEMMCU, IELEDG, IELOCN, IELOTN) "H1_COST"
  FROM PRODDTA.F4102 T0, PRODDTA.F4111 T1
 WHERE ((T1.ILDCT = 'OV' AND T1.ILDCTO = 'ON' AND T1.ILMCU < '     1000998' AND
       T1.ILGLPT <> 'IN61' AND T1.ILTRQT <> 0) AND
       (T1.ILDGL >= 111092 AND T1.ILDGL <= 112092))
   AND ROWNUM <= 10
   AND (T0.IBITM = T1.ILITM AND T0.IBMCU = T1.ILMCU)
 ORDER BY T0.IBITM ASC;