[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*) from
(
select to_char(wor.organization_id)||inst.instance_code
FROM
WIP_OPERATION_RESOURCES wor,
WIP_OPERATIONS wo,
WIP_ENTITIES we,
BOM_DEPARTMENTS bd,
HR_ORGANIZATION_INFORMATION hoi,
GL_SETS_OF_BOOKS gsob,
WIP_DISCRETE_JOBS wdj,
WIP_REPETITIVE_SCHEDULES wrs,
EDW_LOCAL_INSTANCE inst,
MTL_SYSTEM_ITEMS msi
WHERE
wor.organization_id = wo.organization_id
and wor.wip_entity_id = wo.wip_entity_id
and wor.operation_seq_num = wo.operation_seq_num
and nvl(wor.repetitive_schedule_id,-99) = nvl(wo.repetitive_schedule_id,-99)
and wo.organization_id = bd.organization_id
and wo.department_id = bd.department_id
and wo.organization_id = we.organization_id
and wo.wip_entity_id = we.wip_entity_id
and hoi.organization_id = wor.organization_id
and gsob.set_of_books_id = hoi.ORG_INFORMATION1
and hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
and wdj.wip_entity_id (+) = wor.wip_entity_id
and wdj.organization_id (+) = wor.organization_id
and wrs.repetitive_schedule_id (+)= nvl(wor.repetitive_schedule_id,-99)
and wrs.organization_id (+) = wor.organization_id
and (wrs.status_type in (4,5,7,12) or wdj.status_type in (4,5,7,12))
and msi.organization_id = wor.organization_id
and msi.inventory_item_id = we.primary_item_id
and wor.last_update_date between
p_from_date and p_to_date
group by wor.organization_id,wor.wip_entity_id,
wor.operation_seq_num,wor.resource_id,
wor.repetitive_schedule_id,wor.activity_id,
wor.uom_code,wor.basis_type,we.wip_entity_name,
bd.department_code,
wo.first_unit_start_date,wo.first_unit_completion_date,
wo.operation_sequence_id,wo.department_id,
gsob.set_of_books_id, msi.primary_uom_code,
we.primary_item_id ,inst.instance_code
UNION ALL
select
to_char(wt.organization_id)||inst.instance_code
FROM
WIP_ENTITIES we,
WIP_TRANSACTIONS wt,
WIP_TRANSACTION_ACCOUNTS wta,
BOM_DEPARTMENTS bd,
HR_ORGANIZATION_INFORMATION hoi,
GL_SETS_OF_BOOKS gsob,
WIP_FLOW_SCHEDULES wfs,
BOM_OPERATIONAL_ROUTINGS bor,
BOM_OPERATION_SEQUENCES bos,
EDW_LOCAL_INSTANCE inst,
MTL_SYSTEM_ITEMS msi
WHERE
wt.transaction_type in (1,3)
and wfs.status = 2
and wt.wip_entity_id = wfs.wip_entity_id
and wt.organization_id = wfs.organization_id
and wt.organization_id = wta.organization_id
and wt.wip_entity_id = wta.wip_entity_id
and wt.transaction_id = wta.transaction_id
and wta.accounting_line_type = 7
and wt.wip_entity_id = we.wip_entity_id
and wt.organization_id = we.organization_id
and wt.organization_id = bd.organization_id
and wt.department_id = bd.department_id
and hoi.organization_id = wt.organization_id
and hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
and gsob.set_of_books_id = hoi.ORG_INFORMATION1
and msi.organization_id = wt.organization_id
and msi.inventory_item_id = we.primary_item_id
and wfs.organization_id = bor.organization_id
and nvl(wfs.alternate_routing_designator,-99) = nvl(bor.alternate_routing_designator,-99)
and wfs.primary_item_id = bor.assembly_item_id
and bor.routing_sequence_id = bos.routing_sequence_id
and wt.operation_seq_num = bos.operation_seq_num
and bos.operation_type = 1
and wt.last_update_date between
p_from_date and p_to_date
group by wt.organization_id, wt.wip_entity_id,
wt.operation_seq_num, wt.resource_id, wt.activity_id,
wfs.quantity_completed, wfs.date_closed,
wfs.scheduled_start_date, wfs.scheduled_completion_date,
wt.basis_type, wt.transaction_uom,wt.primary_uom,
wt.department_id,we.wip_entity_name,bd.department_code,
gsob.set_of_books_id,bos.operation_sequence_id,
msi.primary_uom_code , we.primary_item_id,inst.instance_code ) ;
SELECT
-- JOB_RSRC_PK (need to add inst.instance_code)
avg(nvl(vsize(organization_id||wip_entity_id||repetitive_schedule_id||operation_seq_num||resource_id),0)),
-- ACT_RSRC_COUNT (dummy)
avg(nvl(vsize(applied_resource_units), 0)),
-- PLN_RSRC_COUNT (dummy)
avg(nvl(vsize(applied_resource_units), 0)),
-- ACT_RSRC_QTY (dummy)
avg(nvl(vsize(applied_resource_units), 0)),
-- PLN_RSRC_QTY (dummy)
avg(nvl(vsize(applied_resource_units), 0)),
-- ACT_RSRC_VAL_B (dummy)
avg(nvl(vsize(usage_rate_or_amount), 0)),
-- PLN_RSRC_VAL_B (dummy)
avg(nvl(vsize(usage_rate_or_amount), 0)),
-- ACT_RSRC_VAL_G (dummy)
avg(nvl(vsize(usage_rate_or_amount), 0)),
-- PLN_RSRC_VAL_G (dummy)
avg(nvl(vsize(usage_rate_or_amount), 0)),
-- ACT_RSRC_USAGE
avg(nvl(vsize(applied_resource_units), 0)),
-- PLN_RSRC_USAGE
avg(nvl(vsize(usage_rate_or_amount), 0)),
-- STND_RSRC_USAGE
avg(nvl(vsize(usage_rate_or_amount), 0)),
-- ACT_RSRC_USAGE_VAL_B
avg(nvl(vsize(applied_resource_value), 0)),
-- PLN_RSRC_USAGE_VAL_B
avg(nvl(vsize(applied_resource_value), 0)),
-- ACT_RSRC_USAGE_VAL_G
avg(nvl(vsize(applied_resource_value), 0)),
-- PLN_RSRC_USAGE_VAL_G
avg(nvl(vsize(applied_resource_value), 0)),
-- EXTD_RSRC_COST (dummy)
avg(nvl(vsize(applied_resource_units), 0)),
-- OPERATION_SEQ_NUM
avg(nvl(vsize(operation_seq_num), 0)),
-- JOB_NO we.wip_entity_name
-- DEPARTMENT bd.department_code
-- ACT_STRT_DATE
-- ACT_CMPL_DATE
-- PLN_STRT_DATE
-- PLN_CMPL_DATE
-- SOB_CURRENCY_FK
-- QTY_UOM_FK same as USAGE_UOM_FK
-- INSTANCE_FK from inst
-- LOCATOR_FK need to add inst.instance_code
-- ACTIVITY_FK Need to take add inst.instance_code
avg(nvl(vsize(activity_id), 0)),
-- TRX_DATE_FK wmt.transaction_date
-- OPRN_FK wo.operation_seq_id
-- RSRC_FK
avg(nvl(vsize(resource_id), 0)),
-- ITEM_FK we.primary_item_id, add instance_code
-- USAGE_UOM_FK
avg(nvl(vsize(uom_code), 0))
FROM WIP_OPERATION_RESOURCES
WHERE last_update_date between
p_from_date and p_to_date;
SELECT
-- OPRN_FK wo.operation_seq_id (Need to add inst.instance_code)
avg(nvl(vsize(operation_sequence_id||organization_id),0))
FROM WIP_OPERATIONS
WHERE last_update_date between
p_from_date and p_to_date;
SELECT
avg(nvl(vsize(wip_entity_name), 0)),
avg(nvl(vsize(primary_item_id), 0))
FROM WIP_ENTITIES
WHERE last_update_date between
p_from_date and p_to_date;
SELECT
avg(nvl(vsize(department_code), 0))
FROM BOM_DEPARTMENTS
WHERE last_update_date between
p_from_date and p_to_date;
SELECT
avg(nvl(vsize(transaction_date), 0))
FROM WIP_MOVE_TRANSACTIONS
WHERE last_update_date between
p_from_date and p_to_date;
SELECT
avg(nvl(vsize(instance_code), 0))
FROM EDW_LOCAL_INSTANCE ;
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) ;
SELECT avg(nvl(vsize(organization_code), 0))
FROM mtl_parameters ;