Reports2017-11-18T12:27:27+00:00

OKC Contract Lines Summary

Description
Categories: Application, Enginatics, Service
Summary of okc line style hierarchies, the jtf objects linked to each line level and the active and overall count of contract lines by status for each line type.
This is useful for developers to see how the oracle contracts line data is structured and how the link to external objects, e.g. installed base or counters for service contracts works

select
ocv.meaning class,
osv.meaning category,
osv.code category_code,
lpad(' ',2*(olsv.level_-1))||olsv.level_ level_,
lpad(' ',2*(olsv.level_-1))||olsv.lty_code lty_code,
olss.jtot_object_code,
jov.name object_name,
jov.from_table,
jov.where_clause,
lpad(' ',2*(olsv.level_-1))||olsv.id lse_id,
olsv.path,
oklb.active,
oklb.total,
&status_columns
from
(
select
level level_,
olsv.name,
olsv.lty_code,
olsv.id,
sys_connect_by_path (olsv.id,'->') path,
connect_by_root olsv.id root_id
from
okc_line_styles_v olsv
connect by
prior olsv.id=olsv.lse_parent_id
start with
olsv.lse_parent_id is null
) olsv,
okc_subclass_top_line ostl,
okc_subclasses_v osv,
okc_classes_v ocv,
(select olss.* from okc_line_style_sources olss where sysdate between olss.start_date and nvl(olss.end_date,sysdate)) olss,
jtf_objects_vl jov,
(
select distinct
okhab.scs_code,
oklb.lse_id,
count(case when sysdate between nvl(oklb.start_date,sysdate) and nvl(oklb.end_date,sysdate) then 1 end) over (partition by okhab.scs_code, oklb.lse_id) active,
count(*) over (partition by okhab.scs_code, oklb.lse_id) total
from
okc_k_lines_b oklb,
okc_k_headers_all_b okhab
where
oklb.dnz_chr_id=okhab.id
) oklb,
(
select
y.*
from
(
select
count(*) count,
oklb.lse_id,
osv.meaning
from
okc_k_lines_b oklb,
okc_statuses_b osb,
okc_statuses_v osv
where
oklb.sts_code=osb.code and
osb.ste_code=osv.code
group by
oklb.lse_id,
osv.meaning
order by
count(*) desc
) x
pivot (
sum(x.count)
for meaning in (
&status_pivot
)
) y
) z
where
olsv.root_id=ostl.lse_id(+) and
ostl.scs_code=osv.code(+) and
osv.cls_code=ocv.code(+) and
olsv.id=olss.lse_id(+) and
olss.jtot_object_code=jov.object_code(+) and
ostl.scs_code=oklb.scs_code and
olsv.id=oklb.lse_id and
olsv.id=z.lse_id(+)
order by
osv.code,
olsv.path

Parameter Name SQL text Validation
Pivot Clause
select
''''||x.meaning||''''||' '||lower(x.meaning)||decode(rownum,max(rownum) over (),null,',') text
from
(
select
osv.meaning
from
okc_k_lines_b oklb,
okc_statuses_b osb,
okc_statuses_v osv
where
oklb.sts_code=osb.code and
osb.ste_code=osv.code
group by
osv.meaning
order by
count(*) desc
) x
LOV
Pivot Clause
select
'z.'||lower(x.meaning)||decode(rownum,max(rownum) over (),null,',') text
from
(
select
osv.meaning
from
okc_k_lines_b oklb,
okc_statuses_b osb,
okc_statuses_v osv
where
oklb.sts_code=osb.code and
osb.ste_code=osv.code
group by
osv.meaning
order by
count(*) desc
) x