security table queries

SELECT ABALPH, FSOBNM, RLFRROLE, RLTOROLE                
FROM sy910.F00950, sy910.F95921, sy910.F0092, PRDNDTA.F0101    
WHERE FSOBNM = 'P3006' AND                                
     FSUSER = RLFRROLE AND
     RLTOROLE  = ULUSER AND          
     ULAN8 = ABAN8 ;

SELECT
  sy910.F95921.RLTOROLE "USER",
  sy910.F95921.RLFRROLE "ROLE",
  sy910.F95921.RLEFFDATE,
  sy910.F95921.RLEXPIRDATE,
  sy910.F00950.fssety,
  sy910.F00950.FSOBNM,
  trim(sy910.F00950.fsfrdv) "version",
  sy910.F00950.fssy,
  sy910.F00950.fsfmnm,
  sy910.F00950.fsinsl,
  sy910.F00950.fsrun,
  sy910.F00950.FSVWYN,
  sy910.F00950.FSA "Add",
  sy910.F00950.FSCHNG "Change",
  sy910.F00950.FSDLT "Delete",
  sy910.F00950.FSIOK "OK/Select",
  sy910.F00950.fsicpy "Copy",
  sy910.F00950.fsiupt,
  sy910.F00950.fsatn1,
  sy910.F00950.fsatn2,
  sy910.F00950.fsatn3,
  sy910.F95921.RLUPMJ,
  SY910.F95921.RLUPMT,
  TO_DATE(TO_CHAR(sy910.F00950.fsupmj+1900000),'YYYYDDD')"UPMJ",
  sy910.F00950.fsupmt,
  sy910.F0093.llll,
  sy910.F0093.llseq
FROM
  sy910.F95921,
  sy910.F00950,
  sy910.F0093
WHERE
  sy910.F00950.FSUSER       = sy910.F95921.RLFRROLE
and LLUSER                  = RLFRROLE
/*AND sy910.F95921.RLEFFDATE <= 115240
AND
  (
    sy910.F95921.RLEXPIRDATE >= 115240
  or SY910.F95921.RLEXPIRDATE = 0
  )*/
and SY910.F0093.LLLL       = 'JUSPD910'
and sy910.F95921.RLTOROLE  in ( /*'BMANDA01', 'JKAMAR01','KYADAP01', 'CSINGA01','SVULUV01', 'KBHASK01', 'SKANN01'*/ 'MSEEFR01')
and SY910.F95921.RLTOROLE not like 'STRE%'
AND SY910.F95921.RLTOROLE NOT LIKE 'TEST%'
/*AND SY910.F95921.RLFRROLE NOT IN('P:USPROCHG', 'USHRSECURE')*/
/*AND SY910.F95921.RLFRROLE IN ('USBTF2SF', 'USBTF2PLN')*/
and  sy910.F00950.FSOBNM  like '%4116%'/* in('R04431', 'R04431A', 'P48013')*/
ORDER BY
  sy910.F95921.RLFRROLE,
  sy910.F00950.FSOBNM,
  sy910.F00950.FSFRDV,
  sy910.F95921.RLTOROLE, sy910.F00950.FSSETY;
 


 select * from sy910.f95921 where rltorole in  ( 'BMANDA01', 'JKAMAR01','KYADAP01', 'CSINGA01','SVULUV01', 'KBHASK01', 'SKANN01') order by rlfrrole, rltorole;

 select * from sy910.F00950 where fsuser = 'P:DEV';

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;