INCORRECT GL CLASS IN ITEM AS OFF

select * from USPDDTA.F41112 where INDCT = 'BF' and INFY = 15 and INCTRY = 20 and INMCU like '         FS%' and
(INCMQT+INNQ01+INNQ02+INNQ03+INNQ04+INNQ05+INNQ06+INNQ07+INNQ08+INNQ09+INNQ10+INNQ11+INNQ12+INNQ13+innq14) <> 0 and
not exists (select * from USPDDTA.F4102 where IBITM = INITM and IBMCU = INMCU and IBGLPT = INGLPT)
and INGLPT not like 'FG%'
and exists (select * from uspddta.F4102 T1 where T1.ibitm = initm and T1.ibmcu = inmcu and T1.ibglpt like 'FG%')
order by initm, inmcu,inlocn, inlotn, inglpt;

Last item used - SLOB

--Last item used in branch
select LIMCU "Branch Plant", TRIM(IBLITM) "Item Number", (select IMDSC1 from USPDDTA.F4101 where LIITM = IMITM) "Description",
IBSRP3 "prod line", IBGLPT "GL Class", TRIM(LILOCN) "Location", TRIM(LILOTN) "Lot Number",LIPQOH/100000 "Quantity on Hand",
TO_DATE(TO_CHAR(IODLEJ+1900000),'YYYYDDD') "Lot Effectivity Date",  IOLOTS "Lot Status Code",
(select TO_DATE(TO_CHAR(max(C2.ILTRDJ)+1900000),'YYYYDDD') from USPDDTA.F4111 C2
   where C2.ILITM = IBITM and C2.ILMCU = IBMCU and C2.ILDCT in ('IM', 'RI','OV')
   /*and C2.ilmcu in ('         FSI', '         FSO', '         FST', '         FSX')*/)  "Last Item b/p used Tran Date"
from USPDDTA.F41021, USPDDTA.F4108, USPDDTA.F4102
where LIITM = IOITM(+) and LIMCU = IOMCU(+) and LILOTN = IOLOTN(+) and LIITM = IBITM and LIMCU = IBMCU
and iodlej <=115215 and iodlej <> 0
and LIPQOH > 0
and LIMCU in ('         FSI', '         FSO', '         FST', '         FSX')
and not exists (select * from USPDDTA.F4111 C1 where C1.ILITM = LIITM and C1.ILMCU = LIMCU and C1.ILDCT in ('IM', 'RI','OV') and C1.ILTRDJ > 115215)
order by IBMCU, IBLITM, LILOCN, IOLOTN;


--Last item used
select LIMCU "Branch Plant", TRIM(IBLITM) "Item Number", (select IMDSC1 from USPDDTA.F4101 where LIITM = IMITM) "Description",
IBSRP3 "prod line", IBGLPT "GL Class", TRIM(LILOCN) "Location", TRIM(LILOTN) "Lot Number",LIPQOH/100000 "Quantity on Hand",
TO_DATE(TO_CHAR(IODLEJ+1900000),'YYYYDDD') "Lot Effectivity Date",  IOLOTS "Lot Status Code",
(select TO_DATE(TO_CHAR(max(C2.ILTRDJ)+1900000),'YYYYDDD') from USPDDTA.F4111 C2
   where C2.ILITM = IBITM and C2.ILDCT in ('IM', 'RI','OV')
   and C2.ilmcu in ('         FSI', '         FSO', '         FST', '         FSX'))  "Last Item used Tran Date"
from USPDDTA.F41021, USPDDTA.F4108, USPDDTA.F4102
where LIITM = IOITM(+) and LIMCU = IOMCU(+) and LILOTN = IOLOTN(+) and LIITM = IBITM and LIMCU = IBMCU
and iodlej <=115215
and LIPQOH > 0
and LIMCU in ('         FSI', '         FSO', '         FST', '         FSX')
and not exists (select * from USPDDTA.F4111 C1 where C1.ILITM = LIITM and C1.ILMCU in ('         FSI', '         FSO', '         FST', '         FSX') and C1.ILDCT in ('IM', 'RI','OV') and C1.ILTRDJ > 115215)
order by IBMCU, IBLITM, LILOCN, IOLOTN;

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;