[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select sum(cnt)
from
(
Select count(*) cnt
FROM
WIP_ENTITIES EN, WIP_DISCRETE_JOBS DI
WHERE
DI.STATUS_TYPE IN (4,5,7,12) AND '_SEC:di.organization_id' IS NOT NULL AND
DI.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND DI.ORGANIZATION_ID = EN.ORGANIZATION_ID
and en.last_update_date between p_from_date and p_to_date
union
Select count(*) cnt
FROM
WIP_ENTITIES EN, WIP_REPETITIVE_SCHEDULES RE
WHERE
RE.STATUS_TYPE IN (4,5,7,12) AND '_SEC:re.organization_id' IS NOT NULL AND
RE.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND RE.ORGANIZATION_ID = EN.ORGANIZATION_ID
and en.last_update_date between p_from_date and p_to_date
union
Select count(*) cnt
FROM
WIP_ENTITIES EN, WIP_FLOW_SCHEDULES FL
WHERE
FL.STATUS = 2 AND '_SEC:fl.organization_id' IS NOT NULL AND
FL.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND FL.ORGANIZATION_ID = EN.ORGANIZATION_ID
and en.last_update_date between p_from_date and p_to_date
);
x_LAST_UPDATE_DATE NUMBER ;
SELECT
avg(nvl(vsize(EN.WIP_ENTITY_NAME), 0)), -- JOB_NO
avg(nvl(vsize(EN.WIP_ENTITY_ID || '-'), 0)), -- JOB_ID
3*avg(nvl(vsize(EN.ORGANIZATION_ID), 0)), -- ORG_ID, Used three times in the stg table
2*avg(nvl(vsize(EN.PRIMARY_ITEM_ID), 0)), -- ITEM_ORG, Used twice in the stg table
avg(nvl(vsize(EN.ENTITY_TYPE), 0)), -- ENTITY_TYPE
avg(nvl(vsize(EN.CREATION_DATE), 0)), -- CREATION_DATE
avg(nvl(vsize(EN.LAST_UPDATE_DATE), 0)) , -- LAST_UPDATE_DATE
avg(nvl(vsize(TO_CHAR(EN.PRIMARY_ITEM_ID)), 0)) -- ROUTING
FROM WIP_ENTITIES EN;
SELECT
avg(nvl(vsize(DI.QUANTITY_COMPLETED), 0)), -- ACT_OUT_QTY,
avg(nvl(vsize(DI.SCHEDULED_COMPLETION_DATE), 0)), -- PLN_CMPL_DATE,
avg(nvl(vsize(DI.START_QUANTITY), 0)), -- PLN_OUT_QTY ,
avg(nvl(vsize(DI.SCHEDULED_START_DATE), 0)), -- ACT_STRT_DATE,
avg(nvl(vsize(DI.SCHEDULED_START_DATE), 0)), -- PLN_STRT_DATE,
avg(nvl(vsize(DI.date_closed), 0)), -- ACT_CMPL_DATE,
avg(nvl(vsize(DECODE(DI.STATUS_TYPE,7,DI.DATE_COMPLETED,NULL)), 0)), -- ACT_CNCL_DATE,
avg(nvl(vsize(DI.LINE_ID), 0)), -- PRD_LINE_FK,
avg(nvl(vsize(DI.ROUTING_REVISION), 0)) -- ROUTING_REVISION,
FROM WIP_DISCRETE_JOBS DI
WHERE
DI.STATUS_TYPE IN (4,5,7,12) AND '_SEC:di.organization_id' IS NOT NULL;
SELECT
avg(nvl(vsize(RE.QUANTITY_COMPLETED), 0)), -- ACT_OUT_QTY,
avg(nvl(vsize(RE.LAST_UNIT_COMPLETION_DATE), 0)), -- PLN_CMPL_DATE,
avg(nvl(vsize('-' || RE.REPETITIVE_SCHEDULE_ID), 0)), -- JOB_ID,
avg(nvl(vsize(RE.DAILY_PRODUCTION_RATE * RE.PROCESSING_WORK_DAYS), 0)), -- PLN_OUT_QTY,
avg(nvl(vsize(RE.FIRST_UNIT_START_DATE), 0)), -- ACT_STRT_DATE,
avg(nvl(vsize(RE.FIRST_UNIT_START_DATE), 0)), -- PLN_STRT_DATE,
avg(nvl(vsize(NVL(RE.DATE_CLOSED,RE.last_unit_completion_date)), 0)), -- ACT_CMPL_DATE,
avg(nvl(vsize(DECODE(RE.STATUS_TYPE,7,RE.DATE_CLOSED,NULL)), 0)), -- ACT_CNCL_DATE,
avg(nvl(vsize(RE.LINE_ID), 0)), -- PRD_LINE_FK,
avg(nvl(vsize(RE.ROUTING_REVISION), 0)) -- ROUTING_REVISION
FROM
WIP_REPETITIVE_SCHEDULES RE
WHERE
RE.STATUS_TYPE IN (4,5,7,12) AND '_SEC:re.organization_id' IS NOT NULL;
SELECT
avg(nvl(vsize(FL.QUANTITY_COMPLETED), 0)), -- ACT_OUT_QTY,
avg(nvl(vsize(FL.SCHEDULED_COMPLETION_DATE), 0)), -- PLN_CMPL_DATE,
avg(nvl(vsize(FL.PLANNED_QUANTITY), 0)), -- PLN_OUT_QTY ,
avg(nvl(vsize(FL.SCHEDULED_START_DATE), 0)), -- ACT_STRT_DATE,
avg(nvl(vsize(FL.SCHEDULED_START_DATE), 0)), -- PLN_STRT_DATE,
avg(nvl(vsize(NVL(FL.DATE_CLOSED,FL.scheduled_completion_date)), 0)), -- ACT_CMPL_DATE,
avg(nvl(vsize(FL.LINE_ID), 0)), -- PRD_LINE_FK,
avg(nvl(vsize(FL.ROUTING_REVISION), 0)) -- ROUTING_REVISION,
FROM
WIP_FLOW_SCHEDULES FL
WHERE
FL.STATUS = 2 AND '_SEC:fl.organization_id' IS NOT NULL;
SELECT
avg(nvl(vsize(ML1.MEANING), 0)) -- JOB_STATUS_MFG_MODE
FROM MFG_LOOKUPS ML1
WHERE
ML1.LOOKUP_TYPE = 'WIP_JOB_STATUS' OR ML1.LOOKUP_TYPE = 'WIP_ENTITY' ;
SELECT
avg(nvl(vsize(NVL(WPB.TL_RESOURCE_IN,0) + NVL(WPB.TL_OVERHEAD_IN,0) + NVL(WPB.TL_OUTSIDE_PROCESSING_IN,0) +
NVL(WPB.PL_MATERIAL_IN,0) + NVL(WPB.PL_MATERIAL_OVERHEAD_IN,0) + NVL(WPB.PL_RESOURCE_IN,0) + NVL(WPB.PL_OVERHEAD_IN,0) +
NVL(WPB.PL_OUTSIDE_PROCESSING_IN,0) + NVL(WPB.TL_SCRAP_IN,0)), 0)) -- ACT_INP_VAL
FROM WIP_PERIOD_BALANCES WPB;
SELECT
-- ACT_MTL_INP_VAL, PLN_MTL_INP_VAl, ACT_BPR_VAL, PLN_BPR_VAl, ACT_OUT_VAL, AVG_ACT_UNIT_CMPL_CST (used twice: STD_VAL_B, PLN_OUT_VAL_B), ACT_SCR_VAL
avg(nvl(vsize(MMT.PRIMARY_QUANTITY), 0))
FROM
MTL_MATERIAL_TRANSACTIONS MMT;
SELECT
avg(nvl(vsize((NVL(DI.DATE_COMPLETED,DI.date_closed) - DI.SCHEDULED_START_DATE)), 0)) -- ACT_JOB_TIME AND PLN_JOB_TIME
FROM
WIP_DISCRETE_JOBS DI;
SELECT
avg(nvl(vsize((NVL(RE.DATE_CLOSED,RE.last_unit_completion_date) - RE.FIRST_UNIT_START_DATE)), 0)) -- ACT_JOB_TIME AND PLN_JOB_TIME
FROM
WIP_REPETITIVE_SCHEDULES RE;
SELECT
avg(nvl(vsize((NVL(FL.DATE_CLOSED, FL.scheduled_completion_date) - FL.SCHEDULED_START_DATE)), 0)) -- ACT_JOB_TIME AND PLN_JOB_TIME
FROM
WIP_FLOW_SCHEDULES FL;
SELECT
avg(nvl(vsize(instance_code), 0))
FROM EDW_LOCAL_INSTANCE ;
SELECT
avg(nvl(vsize(mp.organization_code), 0))
FROM mtl_parameters mp ;
Select count(*) cnt
FROM
WIP_ENTITIES EN, WIP_DISCRETE_JOBS DI
WHERE
DI.STATUS_TYPE IN (4,5,7,12) AND '_SEC:di.organization_id' IS NOT NULL AND
DI.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND DI.ORGANIZATION_ID = EN.ORGANIZATION_ID ;
Select count(*) cnt
FROM
WIP_ENTITIES EN, WIP_REPETITIVE_SCHEDULES RE
WHERE
RE.STATUS_TYPE IN (4,5,7,12) AND '_SEC:re.organization_id' IS NOT NULL AND
RE.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND RE.ORGANIZATION_ID = EN.ORGANIZATION_ID ;
Select count(*) cnt
FROM
WIP_ENTITIES EN, WIP_FLOW_SCHEDULES FL
WHERE
FL.STATUS = 2 AND '_SEC:fl.organization_id' IS NOT NULL AND
FL.WIP_ENTITY_ID = EN.WIP_ENTITY_ID AND FL.ORGANIZATION_ID = EN.ORGANIZATION_ID;
SELECT
avg(nvl(vsize(MSI.FULL_LEAD_TIME), 0))
FROM
MTL_SYSTEM_ITEMS MSI;
SELECT
avg(nvl(vsize(transaction_date), 0))
FROM WIP_MOVE_TRANSACTIONS;
SELECT avg(nvl(vsize(gsob.currency_code), 0))
FROM hr_all_organization_units hou,
hr_organization_information hoi,
gl_sets_of_books gsob
WHERE hou.organization_id = hoi.organization_id
AND ( hoi.org_information_context || '') ='Accounting Information'
AND hoi.org_information1 = to_char(gsob.set_of_books_id) ;
x_LAST_UPDATE_DATE ,
x_ROUTING;
ceil(x_LAST_UPDATE_DATE + 1) +
ceil(x_LAST_UPDATE_DATE + 1) +
ceil(x_ROUTING + 1) ;