Tuesday, December 18, 2012

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;

Thursday, December 13, 2012

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>'