[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*) INTO p_num_rows
FROM (
(SELECT
org.CO_CODE,org.ORGN_CODE,cmp.WHSE_CODE,cmp.ITEM_ID,cmp.LOT_ID,
nvl(cmp.LOCATION,'NONE'),trunc(cmp.TRANS_DATE),cmp.TRANS_UM
from ic_tran_cmp cmp,ic_loct_inv loct, sy_orgn_mst org,ic_whse_mst whse
where trunc(cmp.last_update_date) between p_from_date and p_to_date
AND loct.item_id = cmp.item_id AND loct.lot_id = cmp.lot_id
AND loct.whse_code = cmp.whse_code AND loct.location = cmp.location
AND cmp.whse_code is not null
AND whse.whse_code = loct.whse_code
AND whse.orgn_code = org.orgn_code
and cmp.whse_code = whse.whse_code
AND cmp.location is not null
GROUP BY org.CO_CODE,org.ORGN_CODE,cmp.WHSE_CODE,cmp.ITEM_ID,cmp.LOT_ID,
nvl(cmp.LOCATION,'NONE'),trunc(cmp.TRANS_DATE),cmp.TRANS_UM)
UNION ALL
( SELECT org.CO_CODE,org.ORGN_CODE,pnd.WHSE_CODE,pnd.ITEM_ID,pnd.LOT_ID,
nvl(pnd.LOCATION,'NONE'),trunc(pnd.TRANS_DATE),pnd.TRANS_UM
from ic_tran_pnd pnd ,ic_loct_inv loct , sy_orgn_mst org,ic_whse_mst whse
where trunc(pnd.last_update_date) between p_from_date and p_to_date
AND pnd.COMPLETED_IND = 1
AND loct.item_id = pnd.item_id
AND loct.lot_id = pnd.lot_id
AND loct.whse_code = pnd.whse_code
AND loct.location = pnd.location
AND whse.whse_code = loct.whse_code
AND whse.orgn_code = org.orgn_code
and pnd.whse_code = whse.whse_code
AND pnd.delete_mark = 0
GROUP BY org.CO_CODE,org.ORGN_CODE,pnd.WHSE_CODE,pnd.ITEM_ID,pnd.LOT_ID,
nvl(pnd.LOCATION,'NONE'),trunc(pnd.TRANS_DATE),pnd.TRANS_UM));
SELECT avg(nvl(vsize(tr_dtl.co_code),0)) co_code,
avg(nvl(vsize(tr_dtl.orgn_code),0)) orgn_code,
avg(nvl(vsize(tr_dtl.whse_code),0)) whse_code,
avg(nvl(vsize(tr_dtl.mtl_orgn_id),0)) mtl_org_id,
avg(nvl(vsize(tr_dtl.item_id),0)) item_id,
avg(nvl(vsize(tr_dtl.Lot_id),0)) lot_id,
avg(nvl(vsize(tr_dtl.Loct),0)) loct,
avg(nvl(vsize(tr_dtl.trx_date),0)) trx_date,
avg(nvl(vsize(tr_dtl.UOM),0)) uom,
avg(nvl(vsize(tr_dtl.item_status),0)) item_status,
avg(nvl(vsize(tr_dtl.item_type),0)) item_type,
avg(nvl(vsize(tr_dtl.commodity),0)) commodity,
avg(nvl(vsize(tr_dtl.qty),0)) trans_qty
FROM
((SELECT
org.CO_CODE co_code,org.ORGN_CODE orgn_code,cmp.WHSE_CODE whse_code,
whse.mtl_organization_id mtl_orgn_id,cmp.ITEM_ID item_id,cmp.LOT_ID lot_id,
nvl(cmp.LOCATION,'NONE') LOCT,trunc(cmp.TRANS_DATE) trx_date,cmp.TRANS_UM UOM,
iim.inactive_ind item_status,iim.inv_type item_type,iim.commodity_code commodity,
SUM(cmp.TRANS_QTY) QTY
from ic_tran_cmp cmp,
ic_loct_inv loct,
sy_orgn_mst org,
ic_whse_mst whse,
ic_item_mst iim
where trunc(cmp.last_update_date) between p_from_date and p_to_date
AND loct.item_id = cmp.item_id AND loct.lot_id = cmp.lot_id
AND loct.whse_code = cmp.whse_code AND loct.location = cmp.location
AND cmp.whse_code is not null
AND whse.whse_code = loct.whse_code
AND whse.orgn_code = org.orgn_code
and cmp.whse_code = whse.whse_code
AND cmp.location is not null
AND cmp.item_id = iim.item_id
GROUP BY org.CO_CODE,org.ORGN_CODE,cmp.WHSE_CODE,whse.mtl_organization_id,cmp.ITEM_ID,cmp.LOT_ID,
nvl(cmp.LOCATION,'NONE'),trunc(cmp.TRANS_DATE),cmp.TRANS_UM,
iim.inactive_ind,iim.inv_type,iim.commodity_code)
UNION ALL
( SELECT org.CO_CODE co_code,org.ORGN_CODE org_code ,pnd.WHSE_CODE whse_code,
whse.mtl_organization_id mtl_orgn_id,pnd.ITEM_ID item_id,pnd.LOT_ID lot_id,
nvl(pnd.LOCATION,'NONE'),trunc(pnd.TRANS_DATE) trx_date,pnd.TRANS_UM UOM,
iim.inactive_ind item_status,iim.inv_type item_type,iim.commodity_code commodity,SUM(TRANS_QTY) QTY
from ic_tran_pnd pnd ,
ic_loct_inv loct ,
sy_orgn_mst org,
ic_whse_mst whse ,
ic_item_mst iim
where trunc(pnd.last_update_date) between p_from_date and p_to_date
AND pnd.COMPLETED_IND = 1
AND pnd.item_id = iim.item_id
AND loct.item_id = pnd.item_id
AND loct.lot_id = pnd.lot_id
AND loct.whse_code = pnd.whse_code
AND loct.location = pnd.location
AND whse.whse_code = loct.whse_code
AND whse.orgn_code = org.orgn_code
and pnd.whse_code = whse.whse_code
AND pnd.delete_mark = 0
GROUP BY org.CO_CODE,org.ORGN_CODE,pnd.WHSE_CODE,whse.mtl_organization_id,pnd.ITEM_ID,pnd.LOT_ID,
nvl(pnd.LOCATION,'NONE'),trunc(pnd.TRANS_DATE),pnd.TRANS_UM,
iim.inactive_ind,iim.inv_type,iim.commodity_code)) tr_dtl;
SELECT
avg(nvl(vsize(instance_code), 0))
FROM EDW_LOCAL_INSTANCE ;
SELECT AVG(Nvl(Vsize(EDW_TIME_PKG.CAL_DAY_FK(Sysdate, SOB_ID) ),0)) date_fk,
AVG(Nvl(Vsize(EDW_TIME_PKG.CAL_DAY_TO_CAL_PERIOD_FK(Sysdate, SOB_ID) ),0)) perd_fk,
AVG(NVL(VSIZE(SOB_ID),0)) SOB_ID,
AVG(NVL(VSIZE(ORG_ID),0)) ORG_ID,
AVG(NVL(VSIZE(BASE_CURRENCY_CODE),0)) CURRENCY
FROM gl_plcy_mst;