[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
(SELECT 1
FROM org_access o
WHERE o.responsibility_id = fnd_global.resp_id
AND o.resp_application_id = fnd_global.resp_appl_id
AND o.organization_id = f.organization_id)
OR EXISTS
(SELECT 1
FROM mtl_parameters org
WHERE org.organization_id = f.organization_id
AND NOT EXISTS
(SELECT 1
FROM org_access ora
WHERE org.organization_id = ora.organization_id)))';
SELECT 0 ISC_ATTRIBUTE_1,
0 ISC_ATTRIBUTE_2,
0 ISC_ATTRIBUTE_3,
0 ISC_ATTRIBUTE_4,
0 ISC_MEASURE_1,
0 ISC_MEASURE_2,
0 ISC_MEASURE_3,
0 ISC_MEASURE_4,
0 ISC_MEASURE_5,
0 ISC_MEASURE_6,
0 ISC_MEASURE_7,
0 ISC_MEASURE_8,
0 ISC_MEASURE_9,
0 ISC_MEASURE_10,
0 ISC_MEASURE_11
FROM dual
WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
SELECT decode(a.type, ''ITEM'', items.value,
''RESOURCE'', res.value,
''TRANSPORT'', items.value,
''UNASSIGNED'', null) ISC_ATTRIBUTE_1,
type.meaning ISC_ATTRIBUTE_2,
decode(a.type, ''ITEM'', sp.value,
''RESOURCE'', org.name,
''TRANSPORT'', org.name,
''UNASSIGNED'', null) ISC_ATTRIBUTE_3,
decode(a.type, ''ITEM'', sps.value,
''RESOURCE'', resd.value,
''TRANSPORT'', null,
''UNASSIGNED'', null) ISC_ATTRIBUTE_4,
ISC_MEASURE_1, ISC_MEASURE_2, ISC_MEASURE_3, ISC_MEASURE_4, ISC_MEASURE_5,
ISC_MEASURE_6, ISC_MEASURE_7, ISC_MEASURE_8, ISC_MEASURE_9, ISC_MEASURE_10,
ISC_MEASURE_11
FROM (select (rank() over (&ORDER_BY_CLAUSE nulls last, r_item_id, r_item_org_id, r_resource_id, r_supplier_id,
r_supplier_site_id, r_org_id, r_department_id)) - 1 rnk,
r_item_id,
r_item_org_id,
r_supplier_id,
r_supplier_site_id,
r_resource_id,
r_org_id,
r_department_id,
type,
ISC_MEASURE_1, ISC_MEASURE_2, ISC_MEASURE_3, ISC_MEASURE_4, ISC_MEASURE_5,
ISC_MEASURE_6, ISC_MEASURE_7, ISC_MEASURE_8, ISC_MEASURE_9, ISC_MEASURE_10,
ISC_MEASURE_11
FROM (SELECT c.r_item_id,
c.r_item_org_id,
c.r_supplier_id,
c.r_supplier_site_id,
c.r_resource_id,
c.r_org_id,
c.r_department_id,
c.type,
c.rev_sf ISC_MEASURE_1,
c.comp_rev_sf ISC_MEASURE_2,
c.rev_sf - c.comp_rev_sf ISC_MEASURE_3,
sum(c.rev_sf) over () ISC_MEASURE_4,
sum(c.rev_sf) over () - sum(c.comp_rev_sf) over () ISC_MEASURE_5,
c.rev_sf
/ decode(sum(c.rev_sf) over (), 0, null,
sum(c.rev_sf) over ())
* 100 ISC_MEASURE_6,
sum(c.rev_sf) over ()
/ decode(sum(c.rev_sf) over (), 0, null,
sum(c.rev_sf) over ())
* 100 ISC_MEASURE_7,
c.rev_sf - c.cost_sf ISC_MEASURE_8,
sum(c.rev_sf) over () - sum(c.cost_sf) over () ISC_MEASURE_9,
(c.rev_sf - c.cost_sf)
/ decode(c.rev_sf, 0, null,
c.rev_sf)
* 100 ISC_MEASURE_10,
(sum(c.rev_sf) over () - sum(c.cost_sf) over ())
/ decode(sum(rev_sf) over (), 0, null,
sum(rev_sf) over ())
* 100 ISC_MEASURE_11
FROM (select r_item_id,
r_item_org_id,
r_supplier_id,
r_supplier_site_id,
r_resource_id,
r_org_id,
r_department_id,
type,
sum(rev_sf) REV_SF,
sum(cost_sf) COST_SF,
sum(comp_rev_sf) COMP_REV_SF,
sum(comp_cost_sf) COMP_COST_SF
FROM (SELECT decode(f.reason_type, 1, f.r_item_id, 3, f.r_item_id, -1, null) r_item_id,
decode(f.reason_type, 1, f.r_org_id, 3, f.r_org_id, -1, null) r_item_org_id,
decode(f.reason_type, 1, f.r_supplier_id, 3, null, -1, null) r_supplier_id,
decode(f.reason_type, 1, f.r_supplier_site_id, 3, null, -1, null) r_supplier_site_id,
null r_resource_id,
null r_org_id,
null r_department_id,
decode(f.reason_type, 1, ''ITEM'', 3, ''TRANSPORT'', -1, ''UNASSIGNED'') type,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
f.rev_shortfall'||l_curr_suffix||', 0) rev_sf,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
f.cost_shortfall'||l_curr_suffix||', 0) cost_sf,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
f.rev_shortfall'||l_curr_suffix||', 0) comp_rev_sf,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
f.cost_shortfall'||l_curr_suffix||', 0) comp_cost_sf
FROM ISC_DBI_SHORTFALL_SNAPSHOTS f'||l_item_from||'
WHERE f.start_date = :ISC_CUR_START
AND f.period_type_id = :ISC_PERIOD_TYPE_ID
AND f.reason_type in (1,3,-1)
AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
'||l_org_where||l_item_where||l_sup_where||'
UNION ALL
SELECT null r_item_id,
null r_item_org_id,
null r_supplier_id,
null r_supplier_site_id,
f.r_resource_id r_resource_id,
f.r_org_id r_org_id,
f.r_department_id r_department_id,
''RESOURCE'' type,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
f.rev_shortfall'||l_curr_suffix||', 0) rev_sf,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
f.cost_shortfall'||l_curr_suffix||', 0) cost_sf,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
f.rev_shortfall'||l_curr_suffix||', 0) comp_rev_sf,
decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
f.cost_shortfall'||l_curr_suffix||', 0) comp_cost_sf
FROM ISC_DBI_PM_0004_MV f
WHERE f.start_date = :ISC_CUR_START
AND f.period_type_id = :ISC_PERIOD_TYPE_ID
AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
'||l_org_where||l_res_where||l_res_org_where||')
GROUP BY
r_item_id,
r_item_org_id,
r_supplier_id,
r_supplier_site_id,
r_resource_id,
r_org_id,
r_department_id,
type) c)) a,
HR_ALL_ORGANIZATION_UNITS_TL org,
ENI_ITEM_ORG_V items,
POA_SUPPLIERS_V sp,
POA_SUPPLIER_SITES_V sps,
ENI_RESOURCE_V res,
ENI_RESOURCE_DEPARTMENT_V resd,
FND_LOOKUPS type
WHERE org.organization_id (+)= a.r_org_id
AND org.language (+)= :ISC_LANG
AND items.inventory_item_id(+) = a.r_item_id
AND items.organization_id(+) = a.r_item_org_id
AND sp.id (+)= a.r_supplier_id
AND sps.id (+)= a.r_supplier_site_id
AND res.id (+)= to_char(a.r_resource_id)
AND resd.id (+)= to_char(a.r_department_id)
AND type.lookup_type = ''ISC_DBI_PLAN_PRS_REASON_TYPE''
AND type.lookup_code = a.type
AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
ORDER BY rnk';