JDE clean up of payment tables when supplier hold code causes orphaned records

select * from uspddta.F04571;

select * from uspddta.F04572;

select * from uspddta.F04573;

select * from uspddta.F0411 where rppst = '#';

UPDATE uspddta.F0411
SET rppst = 'A'
WHERE
rppst = '#' and exists(select * from uspddta.F04573 where rpkco = kikco and rpdoc = kidoc and rpdct = kidct and rpsfx = kisfx and kihdc = 27252);

DELETE uspddta.F04573 WHERE kihdc = 27252;
DELETE uspddta.F04572 WHERE NOT EXISTS (SELECT * FROM uspddta.F04573 WHERE  kihdc = kkhdc AND kickc = kkckc);

DELETE uspddta.F04571 where not exists (select * from uspddta.F04572 where  kkhdc = khhdc);

simulated not equal to frozen detailed by cost element

SELECT ibitm, iblitm, ibaitm, ibmcu, ibstkt,ibacq/100000 "ACQ", ieledg "LEDG", iecost "COST",iexsmc/10000 "SIM NET", iexscr/10000 "SIM TOT", iestdc/10000 "FRZ NET", iecsl/10000 "FRZ TOT",
(select COUNCS/10000 from USPDDTA.F4105 where  COITM = IBITM
and COMCU = IBMCU and COLEDG = IELEDG ) "std cost"  
FROM uspddta.F4102,uspddta.F30026 WHERE ibitm = ieitm AND ibmcu = iemmcu AND ieledg in( '07' ,'05') AND iexscr <> iecsl and ibstkt <> 'O'
ORDER BY ibitm, ibmcu, ieledg, iecost;

simulated not equal to frozen summarized

SELECT ibitm, iblitm, ibaitm, ibmcu, ibstkt,ibacq/100000 "ACQ",
(SELECT sum(lipqoh)/100000 FROM uspddta.F41021 LI WHERE LI.LIITM = IBITM AND LI.LIMCU = IBMCU ) "Inv_Onhand",
ieledg "Ledger Type", sum(iexsmc)/10000,sum( iexscr)/10000, sum(iestdc)/10000, sum(iecsl)/10000,
(SELECT councs/10000 FROM uspddta.F4105 WHERE  COITM = ibitm
AND COMCU = ibmcu and coledg = ieledg ) "std cost"
from uspddta.F4102,uspddta.F30026
where IBITM = IEITM and IBMCU = IEMMCU and IELEDG in( '07' ,'05') and IBSTKT <> 'O'
GROUP BY ibitm, iblitm, ibaitm, ibmcu,ibstkt,ibacq, ieledg
HAVING (sum(iexscr) <> sum(iecsl) OR sum(iecsl) <> (SELECT councs FROM uspddta.F4105 WHERE  COITM = ibitm
and COMCU = IBMCU and COLEDG = IELEDG )) ;

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;