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;

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;

Integrity Check between F4111 Vs F43121 with join on F76432

SELECT ILITM,
       ILLITM,
       ILMCU,
       ILGLPT,
       ILKCOO,
       ILDOCO,
       ILDCTO,
       ILLNID,
       ILDOC,
       ILDCT,
       (SELECT PJBNOP || PJBSOP
          FROM PRODDTA.F76432
         WHERE ILKCOO = PJKCOO
           AND ILDOCO = PJDOCO
           AND ILDCTO = PJDCTO
           AND ILLNID = PJLNID
           AND PJSFXO = '000') "TransNature_Suffix",
       (SELECT TO_DATE(CONCAT('20',
                              CONCAT(SUBSTR(TO_CHAR(ILTRDJ), 2, 2),
                                     SUBSTR(TO_CHAR(ILTRDJ), 4, 3))),
                       'YYYYDDD')
          FROM DUAL) AS TRANSACTIONDATE,
       (SELECT TO_DATE(CONCAT('20',
                              CONCAT(SUBSTR(TO_CHAR(ILDGL), 2, 2),
                                     SUBSTR(TO_CHAR(ILDGL), 4, 3))),
                       'YYYYDDD')
          FROM DUAL) AS GLDATE,
       SUM(ILTRQT / 1000),
       SUM(ILPAID / 100)
  FROM PRODDTA.F4111
 WHERE ILDCT = 'OV'
   AND ILDGL >= 112153
   AND ILDGL <= 112182
--   AND ROWNUM <= 1000
   AND NOT EXISTS (SELECT 'X'
          FROM PRODDTA.F43121
         WHERE PRDOCO = ILDOCO
           AND PRDCTO = ILDCTO
           AND PRLNID = ILLNID
           AND PRDOC = ILDOC
           AND PRKCOO = ILKCOO)
 GROUP BY ILITM,
          ILLITM,
          ILMCU,
          ILGLPT,
          ILKCOO,
          ILDOCO,
          ILDCTO,
          ILLNID,
          ILDOC,
          ILDCT,
          ILTRDJ,
          ILDGL
 ORDER BY ILMCU, ILDOCO, ILDCTO;
---------------------------------------------------------------------------------------------------------
SELECT ILDCTO, COUNT(DISTINCT(ILDOC))
  FROM PRODDTA.F4111
 WHERE ILDCT = 'OV'
   AND ILDGL >= 112032
   AND ILDGL <= 112060
   AND ILKCO = '00311' --AND ROWNUM <=1000
      --   AND ILDcto = 'OP'
   AND NOT EXISTS (SELECT *
          FROM PRODDTA.F43121
         WHERE ILDOCO = PRDOCO
           AND ILDCTO = PRDCTO
              --AND ILLNID = PRLNID
           AND ILDOC = PRDOC)
 GROUP BY ILDCTO
 ORDER BY ILDCTO ASC;

Find Job Queues of all UBEs running

SELECT T1.JCPID, T0.JCJOBQUE, COUNT(*)
  FROM SVM812.F986110 T0, SVM812.F986114 T1
 WHERE T0.JCJOBNBR = T1.JCJOBNBR
   AND T0.JCEXEHOST = 'ryelxjdlabtp'
--AND T0.JCFNDFUF2 LIKE 'R31802A%'
 GROUP BY T1.JCPID, T0.JCJOBQUE
 ORDER BY T1.JCPID, T0.JCJOBQUE;

Integrity between Cardex Vs As off

SELECT ILITM, ILLITM, ILMCU, ILLOCN, ILLOTN, ILGLPT, INNQ06, SUM(ILTRQT)
  FROM PRODDTA.F4111, PRODDTA.F41112
 WHERE ILITM = INITM
   AND ILMCU = INMCU
   AND ILLOCN = INLOCN
   AND ILLOTN = INLOTN
   AND ILGLPT = INGLPT
   AND INDCT = 'BF'
   AND INCTRY = 20
   AND INFY = 12
   AND ILIPCD = 'Y'
   --AND ILITM = 405239
   AND ILMCU = '     3110082'
   AND ILDGL >= 112153
   AND ILDGL <= 112182
   AND ILUKID >= 9257279 --AND ROWNUM <=100000
 GROUP BY ILITM, ILLITM, ILMCU, ILLOCN, ILLOTN, ILGLPT, INNQ06
 HAVING SUM(iltrqt) <> innq06;

Insert SF Locations based on Primary Location

INSERT INTO TESTDTA.F41021 T3
  (SELECT T2.LIITM,
          T2.LIMCU,
          'SF',
          ' ',
          'S',
          T2.LIGLPT,
          ' ',
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,
          0,0,
          0,
          T2.LIURRF,
          T2.LIURAT,
          T2.LIURCD,
          T2.LIJOBN,
          'SF_INS',
          T2.LIUPMJ,
          T2.LIUSER,
          T2.LITDAY,
          T2.LIURDT,
          0,0,0,0,0,0,0,0,0,0,0,0,0,
          T2.LICHDF,
          T2.LIWPDF,
          T2.LICFGSID
     FROM TESTDTA.F41021 T2
    WHERE T2.LIMCU = '     2050012' AND T2.LIPBIN = 'P' AND NOT EXISTS (SELECT * FROM testdta.F41021 T1 WHERE T2.LIITM = T1.LIITM AND T2.LIMCU = T1.LIMCU AND T1.LILOCN = 'SF') AND ROWNUM <1000);

Linetypes not in UDC 55|LY

/* Formatted on 2012/04/11 11:05 (Formatter Plus v4.8.8) */
SELECT distinct prlnty
  FROM proddta.f43121, proddta.f76432
 WHERE prdoco = pjdoco
   AND prdcto = pjdcto
   AND prkcoo = pjkcoo
   AND prlnid = pjlnid
   AND prsfxo = pjsfxo
   AND prmatc IN ('1', '2')
   and not exists (select * from prodctl.F0005 where drsy = '55' and drrt = 'LY' and trim(drky) = (trim(prlnty)))

Transaction Nature not in UDC 55|FA

SELECT *
  FROM proddta.f43121, proddta.f76432
 WHERE prdoco = pjdoco
   AND prdcto = pjdcto
   AND prkcoo = pjkcoo
   AND prlnid = pjlnid
   AND prsfxo = pjsfxo
   AND prmatc IN ('1', '2')
   and not exists (select * from prodctl.F0005 where drsy = '55' and drrt = 'FA' and trim(drky) = (trim(pjbnop)||trim(pjbsop)))

NO OV and Missing base closing period Cost

SELECT initm, inlitm, inaitm, c0mmcu, SUM(incmqt+innq01+innq02)/1000 FROM qadta.F41112, qadta.F554103
WHERE inlitm IN ( 'F241121', 'F214230', 'F212437', 'F212436', 'F212434', 'F212431', 'F212418', 'F212416', 'F211726', 'F211546', 'F211142', 'F210736', 'F204126', 'F364418', 'F365363', 'F364047', 'F371114', 'F367663', 'F367660', 'F374825', 'F377294', 'F366303', 'F381175', 'F354645', 'F361475', 'F364099', 'F364092', 'F364091', 'F364102', 'F364098', 'F364051', 'F370191', 'F194833', 'F368877', 'F374831', 'F374829', 'F374828', 'F374827', 'F374826', 'F374825', 'F374824', 'F374823', 'F374821', 'F374820', 'F378363', 'F375729', 'F186007', 'F185200', 'F216748', 'F216361', 'F213715', 'F282245', 'F218505', 'F352104', 'F339158', 'F339014', 'F299115', 'F230749', 'F224454', 'F223621', 'F221874', 'F221830', 'S9512', 'F212437', 'F212430', 'F212415', 'F212413', 'F211181', 'F210690', 'F194219', 'F204148', 'F370189', 'F194818', 'F368873', 'F373999', 'F375729', 'F203040', 'F376324', 'F380404', 'F380435', 'F381175', 'F203052', 'F193923', 'F212452', 'F212443', 'F212436', 'F212428', 'F166127', 'F186521', 'F192831', 'F361271', 'F204253', 'F375732', 'F375729', 'F236959', 'F277126', 'F342680', 'F348463', 'F290383', 'F367525', 'F203037')
AND infy = 12
AND inmcu = c0mcu
AND NOT EXISTS (SELECT * FROM qadta.F554105 WHERE c2pn = 2 AND c2fy = 12 AND initm = c2itm AND inmcu = c2mcu)
AND NOT EXISTS (SELECT * FROM qadta.F4111 WHERE ildct = 'OV' AND initm = ilitm AND inmcu = ilmcu AND ildgl <= 112060 )
GROUP BY initm, inlitm, inaitm, c0mmcu
HAVING SUM(incmqt+innq01+innq02)/1000 <> 0

Items with Inv in F41112 and missing Base closing period costs

SELECT   indct, infy, inctry, initm, inlitm, inaitm, inmcu, inglpt,
         (SUM (innq01 + innq02)) / 1000
    FROM proddta.f41112
   WHERE infy = 12
     AND inctry = 20
     AND indct = 'BF'
     AND NOT EXISTS (
            SELECT *
              FROM proddta.f554105
             WHERE initm = c2itm
               AND inmcu = c2mcu
               AND c2ctry = 20
               AND c2fy = 12
               AND c2pn = 2
               AND c2ledg = '02'
               AND c2uncs <> 0)
GROUP BY indct, infy, inctry, initm, inlitm, inaitm, inmcu, inglpt
  HAVING SUM (innq01 + innq02) <> 0;

Copy F41021 from other Branch Plant

INSERT INTO testdta.F41021
(SELECT LIITM,'     2050405' ,LILOCN,LILOTN,LIPBIN,LIGLPT,LILOTS,LILRCJ,LIPQOH,LIPBCK,LIPREQ,LIQWBO,LIOT1P,LIOT2P,LIOT1A,LIHCOM,LIPCOM,LIFCOM,LIFUN1,LIQOWO,LIQTTR,LIQTIN,LIQONL,LIQTRI,LIQTRO,LINCDJ,LIQTY1,LIQTY2,LIURAB,LIURRF,LIURAT,LIURCD,LIJOBN,'C58_2B3',LIUPMJ,LIUSER,LITDAY,LIURDT,LIQTO1,LIQTO2,LIHCMS,LIPJCM,LIPJDM,LISCMS,LISIBW,LISOBW,LISQOH,LISQWO,LISREQ,LISWHC,LISWSC,LICHDF,LIWPDF,LICFGSID FROM testdta.F41021 WHERE  limcu = '     2050012' );

Items Missing Primary Location in F41021

/* Formatted on 2012/03/14 06:31 (Formatter Plus v4.8.8) */
SELECT ib.ibmcu, COUNT (*)
  FROM testdta.f4102 ib
 WHERE NOT EXISTS (
          SELECT *
            FROM testdta.f41021 li
           WHERE ib.ibitm = li.liitm AND ib.ibmcu = li.limcu
                 AND li.lipbin = 'P')
                 group by ib.ibmcu

Missing records in F5541052

SELECT ib.ibitm, ib.iblitm, ib.ibaitm, ib.ibmcu, ib.ibstkt, c2.*
  FROM proddta.f4102 ib, proddta.f554105 c2
 WHERE ib.ibitm = c2.c2itm
   AND ib.ibmcu = c2.c2mcu
   AND NOT EXISTS (
          SELECT *
            FROM proddta.f5541052 mi
           WHERE c2.c2itm = mi.miitm
             AND c2.c2mcu = mi.mimmcu
             AND c2.c2ctry = mi.mictry
             AND c2.c2fy = mi.mify
             AND c2.c2pn = mi.mipn
             AND c2.c2ledg = mi.miledg)



/* Formatted on 2012/03/15 15:13 (Formatter Plus v4.8.8) */
SELECT ib.ibitm, ib.iblitm, ib.ibaitm, ib.ibmcu, ib.ibstkt, c2.c2ctry,
       c2.c2fy, c2.c2pn, c2.c2ledg, c2.c2uncs / 10000, c2.c2pid, c2.c2user,
       c2.c2upmj, c2.c2tday, mi.misoqs / 1000, mi.miuncs / 10000,
       mi.mi$55tmls / 10000, mi.mi$55tlwc / 10000, mi.mi$55tmwc / 10000,
       mi.mi$55tlbw / 10000, mi.mi$55tmbw / 10000, mi.micmqt / 1000,
       mi.miarec / 100, mi.mistdc / 10000
  FROM proddta.f4102 ib, proddta.f554105 c2, proddta.f5541052 mi
 WHERE ib.ibitm = c2.c2itm
   AND ib.ibmcu = c2.c2mcu
   AND c2.c2itm = mi.miitm(+)
   AND c2.c2mcu = mi.mimmcu(+)
   AND c2.c2ctry = mi.mictry(+)
   AND c2.c2fy = mi.mify(+)
   AND c2.c2pn = mi.mipn(+)
   AND c2.c2ledg = mi.miledg(+)
   AND c2.c2ledg = '02'
   AND EXISTS (
          SELECT *
            FROM proddta.f4111 il
           WHERE il.ildct = 'IC' AND ib.ibitm = il.ilitm
                 AND ib.ibmcu = il.ilmcu)

Revaluation based on weighted avg cost vs actual cost

SELECT il.ilukid, il.ilitm "ITEM", il.illitm "GBL_ITM", il.ilaitm "REG_ITM",
       il.ilmcu "B/P", il.illocn "LOCN", il.illotn "LOTN", il.ildoc "DOC",
       il.ildct "DOC_TYP", il.ildgl "GL_DATE", il.ilglpt "GL_CLASS",
       il.ildoco "ORD_NO", il.ildcto "ORD_TYP", il.iltrqt / 1000 "QTY",
       il.ilpaid / 100 "EXT_AMT", c2.c2uncs / 10000 "WT_AVG_CST",
       ROUND ((il.iltrqt / 1000) * (c2.c2uncs / 10000), 2) "EXT_AMT_WT_AVG",
       ROUND (((il.iltrqt / 1000) * (c2.c2uncs / 10000)) - (il.ilpaid / 100),2) "ADJ_WT_AVG",
       (mi.miuncs + mi.mistdc) / 10000 "AC_AVG_CST",
       ROUND ((il.iltrqt / 1000) * ((mi.miuncs + mi.mistdc) / 10000),2) "EXT_AMT_AC_AVG",
       ROUND (  ((il.iltrqt / 1000) * ((mi.miuncs + mi.mistdc) / 10000))- (il.ilpaid / 100),2) "ADJ_AC_AVG"
  FROM proddta.f4111 il, proddta.f554105 c2, proddta.f5541052 mi
 WHERE il.ilmcu = c2.c2mcu
   AND il.ilitm = c2.c2itm
   AND c2.c2mcu = mi.mimmcu(+)
   AND c2.c2itm = mi.miitm(+)
   AND c2.c2fy = mi.mify(+)
   AND c2.c2pn = mi.mipn(+)
   AND c2.c2ledg = mi.miledg(+)
   AND c2.c2ctry = mi.mictry(+)
   AND c2.c2fy = 12                                             -- Fiscal Year
   AND c2.c2pn = 1                                                   -- Period
   AND c2.c2ledg = '02'
   AND c2.c2ctry = 20
   AND il.ildct IN ('IM', 'IC')
   AND il.ildgl >= 112001
   AND il.ildgl <= 112031
   AND il.iltrqt <> 0
--ORDER BY il.ilitm, il.ilmcu

-- To Check the Adj Amount created in the Cardex -- select * from proddta.F554113 where raukid =  "ILUKID""

Check DMAAIs missing in Chart of Accounts F0901 & F4095

SELECT gm.gmco, gm.gmaid, ml.mlanum, gm.gmobj, gm.gmmcu, gm.gmsub, gm.gmdl01,
       ml.mlobj, ml.mlmcu, ml.mlsub, ml.mldct, ml.mldcto, ml.mlglpt,
       ml.mlcost, gm.gmlda, gm.gmpec
  FROM cnvdta.f0901 gm, cnvdta.f4095 ml
 WHERE ml.mlco = '00205'
   AND gm.gmmcu(+) = ml.mlmcu
   AND gm.gmobj(+) = ml.mlobj
   AND gm.gmsub(+) = ml.mlsub
   AND gm.gmco(+) = ml.mlco
   AND gmaid IS NULL

Inventory Valuation with Missing Costs - F47122 & F4105

SELECT T1.MJMCU,
       T1.MJLITM,
       T1.MJAITM,
       T1.MJITM,
       T2.IMDSC1,
       T2.COLEDG,
       T1.QTY,
       T2.STD_COST
  FROM (SELECT MJMCU, MJLITM, MJAITM, MJITM, SUM(MJTRQT / 1000) AS QTY
          FROM TESTDTA.F47122
         WHERE MJEDSP = ' '
           AND MJMCU = '     2050012'
         GROUP BY MJMCU, MJLITM, MJAITM, MJITM) T1
  LEFT OUTER JOIN (SELECT COITM,
                          COLITM,
                          COAITM,
                          IMDSC1,
                          COMCU,
                          COLEDG,
                          COUNCS / 10000 AS STD_COST
                     FROM TESTDTA.F4105, TESTDTA.F4101
                    WHERE COITM = IMITM
                      AND COLEDG = '07') T2
    ON T1.MJITM = T2.COITM
   AND T1.MJMCU = T2.COMCU

WHERE -- Comment the next 3 line if you want to see Inv Valuation
    ( COLEDG IS NULL
     OR STD_COST = 0)

Count on F30026 & F4105

select current_timestamp,ieledg, iemmcu, count(distinct(IEITM)) from proddta.F30026
where
ieledg in ('02', '01', '07') and
iexsmc >0
group by ieledg, iemmcu
order by ieledg, iemmcu

----------------------------------------------------------------------------------------------


select to_char(systimestamp, 'HH24:MI:SS.FF3'),comcu,  coledg, count(*) from proddta.F4105
where coUNCS <> 0
and coledg = '01'
group by coledg, comcu order by coledg, comcu

Costed Inventory

SELECT
    trim(T2.LIMCU),
    TRIM(T1.IMLITM),
    TRIM(T1.IMAITM),
   TRIM( T1.IMDSC1),
    T3.COLEDG,
    T2.LIGLPT,
    T3.COUNCS/10000 AS COST,
    SUM(T2.LIPQOH)/1000 AS TTL_QTY
FROM
    PRODDTA.F4101 T1,
    PRODDTA.F41021 T2
LEFT OUTER JOIN
    (
        SELECT
            *
        FROM
            PRODDTA.F4105
        WHERE
            COLEDG = '07'
    )
    T3
ON
    T2.LIITM = T3.COITM
AND T2.LIMCU = T3.COMCU
WHERE
    T1.IMITM = T2. LIITM
AND T2.LIPQOH <>0
GROUP BY
    T2.LIMCU,
    T1.IMLITM,
    T1.IMAITM,
    T1.IMDSC1,
    T3.COLEDG,
    T2.LIGLPT,
    T3.COUNCS
HAVING
    SUM(T2.LIPQOH)<>0
ORDER BY
    T2.LIMCU,
    T1.IMLITM

JDE Developer Positions

I have urgent opening for JD Edwards IT Developer @ CT

Duration Full time
Skills: JD Edwards One World development, Payroll & HR, SQL
If interested send your resume to ashish.kumar@two95intl.com

__________________________________________________________________________________

Applications Support Specialist/JD Edwards E1 Developer needed for Greater NYC area company. 5+ years Functional Expertise Required - $100K+

Manufacturing/Distribution company based in the Greater NYC area is seeking an experienced Technical Applications Support Specialist to join their team. The ideal candidate will have 5+ years experience as a Business Analyst and also possess very heavy JD Edwards EDI development experience, perhaps as a Developer within JD Edwards EnterpriseOne, or E1, environments. The experience must be within an implementation or process improvement level; not just as an end-user. The role will also require the individual to provide training to end users. This individual will serve a multi-purpose role of Business Analyst and Developer with a focus on EDI, although not exclusively. Email me at TH@NielsenStaffing.com or call            631-582-4010       x18. Immediate hire. Referrals welcome.

_____________________________________________________________________________________

Looking for a JD Edwards Developer for a permanent position in Cleveland, Ohio. Email me if you or someone you know may be interested: mbrace@murtechconsulting.com

_____________________________________________________________________________________

JD Edwards EnterpriseOne Technical Developer Consultant

Terillium is an Oracle Platinum Partner, achieving the highest partner level possible within the Oracle Community. Specializing in reselling and implementing JD Edwards solutions and Oracle’s E-Business Suite: Terillium enables companies to develop a competitive edge by utilizing technology and process improvements. Our clients are Mid-Market corporations that are growing and have complex business requirements, yet need a rapid, lower-cost business system solution. Our years of experience, proven success, and focus on client satisfaction ensure a streamlined process and cost effective solution.

We are looking for talented industry and consulting professionals to join with our core team. Our focus is directed at EnterpriseOne and eBusiness implementations and upgrades. Our consultants are required to assist clients in developing new business processes and use Oracle’s product to support these processes.

We have a requirement for a Senior EnterpriseOne Developer that has EnterpriseOne implementation experience and working knowledge in the following:

Skills Required
Strong skill set in JD Edwards EnterpriseOne Toolset and experience with interactive application development (FDA), batch application development (RDA), table conversion development and Business Function Development (NER/ C). Skills must also include: tables/indices, business views, media objects, versions, data dictionary, udc’s, task views and OMW. Must have experience designing, developing and testing reports, interfaces, data conversions and enhancements. Excellent technical problem solving skills combined with the ability to debug operational and system issues. Ability to communicate ideas in both technical and user-friendly language. Prefer those candidates with experience withBI Publisher, workflow, portal development, business services, financial report writing, UPK, EDI and RPG.

Additional Requirements:
Bachelor’s degree, Masters preferred
Consulting Experience
Multiple E1 implementations
8.12/9.0 Experience is a plus
Travel is required
Midwest Geographic Residence Preferred


Please visit http://www.terillium.com/ for more information.

__________________________________________________________________________________________

Job Description

Six months or greater contract to hire opportunity working for afortune 500 company in Texas

The JD Edwards Applications Developer must be able to applycritical thinking to analyze and diagnose customer issues related to JDEsystem, facilitate and engage in discussions with clients and team members todiscuss potential solutions, and implement solutions that are most appropriatein a timely and quality manner.

Requirements:

• 5+ years of experience in software development out of which atleast 1 year should be in JDE EnterpriseOne environment
• Must have experience providing support in a JD Edwards environmentthat is highly customized (not just out of the box)
• Must have experience in formal methodologies in technology systemsimplementations and software development including Agile methodologies
• Must be able to create/review and approve functionaldesign documents and technical design documents
• Must have strong knowledge and experience in JDE OMW development toolsand processes to create UBEs, applications and business functions
• Experience in estimating projects, development effortsand implementation efforts
• Business knowledge and experience providing day-to-day productionsupport for issues
• Mustbe able to provide timely and complete responses to customer requests in bothwritten and verbal manner
• Mustprovide accurate and consistent status reports to clients and otherstakeholders
• Proficientin MS Office Word, Excel, Outlook and PowerPoint
• Mustbe able to travel to various client locations when necessary

Desired Skills & Experience

Preferred:

• Knowledgeand experience with business intelligence systems
• Familiaritywith the functional and technical aspects of manufacturing, logistics and/ordistribution modules of JDE EnterpriseOne 9.0

Skills:

• Interact with IT and business leadership team and lead such meetingswhen necessary
• Musthave the ability to multi-task and organize effectively to meet client servicelevel expectations
• Adept and proactive at problem solving and conflict resolution
• Demonstrates strong analytical skills and develops solutions toproblems independently and through facilitated team discussions
• Abilityto actively participate in team and client meetings, andlead meetings as needed
• Motivated and shows initiative
• Workswell independently and within a team
• Excellentwritten and verbal communication skills
• Possessesa professional demeanor
• Greatorganizational, time management and problem solving skills
• Prioritizesworkload, meets deadlines and manages competing priorities under pressure
• Adaptto different client work environments
• Workin a fast paced environment

Education:

• Bachelor’s degree (business, engineering or related fieldrecommended); knowledge of theories, principles and concepts typically acquiredthrough completion of a bachelor’s degree.

Company Description

Smartbridge, LLC is a dynamic technology consulting company with a steady growth serving its clients for over 8 years. It’s located in Houston, TX with clients and associates located across Texas and other states. Smartbridge provides enterprise technology strategies, systems integrations and mobile applications to deliver new capabilities to its clients, both in the public and private sectors. Smartbridge offers an entrepreneurial work environment with a technically skilled and disciplined team that has high customer satisfaction ratings.

Additional Information

Posted:

January 31, 2012

Type:

Full-time

Experience:

Mid-Senior level

Functions:

Information Technology 

Industries:

Management Consulting 

Compensation:

DOE

Job ID:

2483401

____________________________________________________________________________

JDE Technical Developer needed for 6-8 weeks in TN call or email rachel@r-e-s.com             720-420-6257      

_______________________________________________________________________________________

JDE Senior programmer 

Contact hilda@systemsr.com or call 770-475-7997x1

____________________________________________________________________________________

Doug

I am currently seeking a Senior JD EDWARDS ENTERPRISEONE DEVELOPER. Permanent position for a growing and very reputable client in the Northwest!

Minimum 7 years of experience with JD Edwards EnterpriseOne development tools .
Experience and/or knowledge of .Net, VB.Net, C, C++, MS SharePoint would be a plus.

Benefits: Base pay 95k -110k plus bonus! Excellent benefits, profit sharing, health, etc.

I am their primary recruiter so I have an excellent chance of getting you or any of my candidates an interview. I will be setting up interview next week!

Please send your resume to: dwintz@wincostaffing.com or call me ASAP if you or anyone you know would be interested? I will be more than happy to provide additional details when we speak!

Thanks,

Doug Wintz
            (561)616-7386      

__________________________________________________________________________

JDEdwards EnterpriseOne and JDEdwards World Opportunities

Contact Lucy Marquardt at Tri-S Recruiters – lucym@trisrecruiters.com /            630.325.7373       - Forward a copy of your updated resume today. Please include salary requirements to lucym@trisrecruiters.com or via phone at             630-325-7373      . Our firm has over 20 years of IT recruiting experience and has focused on JDEdwards candidates and clients for the past 9 years.