compare as off with cardex for FY 15 transactions

-- compare as off with cardex for FY 15 transactions
SELECT ILITM "Short Item", ILLITM "Item", ILMCU "Branch", ILLOCN "Location", ILLOTN "Lot Number", ILGLPT "GL Class", ibglpt "item gl class", IMUOM1 "Primary UOM" , ILTRUM "Transaction UOM",
SUM(ILTRQT)/100000 "Ledger Qty", (SUM(ILTRQT)/100000)/(nvl((SELECT umconv/10000000 FROM uspddta.F41002 where umitm = ilitm and umum = imuom1 and umrum = iltrum),1)) "CS Conv",
(SELECT  ( t2.incmqt+t2.innq01+t2.innq02+t2.innq03+t2.innq04+t2.innq05+t2.innq06+t2.innq07+t2.innq08+t2.innq09+t2.innq10+t2.innq11+t2.innq12+t2.innq13)  /100000 FROM uspddta.f41112 t2
WHERE t2.infy = 15 AND t2.inctry = 20 AND t2.indct = 'BF' AND t2.initm = ilitm AND t2.inmcu = ilmcu AND t2.inlocn = illocn AND t2.inlotn = illotn AND t2.inglpt = ilglpt ) "As of Qty FY15"
FROM uspddta.f4111, uspddta.f4101, uspddta.f4102
WHERE /*ilitm IN(173345) AND*/ imitm = ilitm AND ibitm = ilitm AND ibmcu = ilmcu
AND ildgl <= 116182 /* ILMCU LIKE '          B%'  /*and illocn = 'HOLD'*/
GROUP BY ILITM, ILLITM, ILMCU, ILLOCN, ILLOTN, ILGLPT,IBGLPT,  ILTRUM, IMUOM1;

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;