1.SELECT qyv.*,
get_cop_person_group_name(fresp_person_id) groupName,
get_person_last_name(fcperson_id) as cperson
FROM (select ROW_NUMBER() OVER(PARTITION BY 1 order by dit.fsys_sq DESC) rn,
dit.fsys_sq,
dit.fpar_sys_sq,
dit.for_line_id,
cust.party_name,
cust.cust_grade_by,
cust.location_code,
head.fproject_name,
MS.SEGMENT1 FITEM_CODE,
dit.fitem_id,
dit.fstep_code_line,
dit.fresp_person_id,
dit.fcperson_id,
substr(dit.fcreate_date, 1, 10) fcreate_date
from cop_dit_line dit,
MTL_SYSTEM_ITEMS_B MS,
cop_cop_or_header head,
cop_customer_header_v cust
where dit.fpar_sys_sq = head.fsys_sq(+)
and dit.fstep_code_line not in (9)
and head.fcust_party_id = cust.cust_party_id(+)
and dit.fitem_id = MS.INVENTORY_ITEM_ID(+)
AND MS.ORGANIZATION_ID = 84
and dit.for_line_id not in
(select pick.or_line_id from cop_opportunity_picking pick)
and cust_grade_by in ('C0', 'C1', 'C2')
and TRUNC(SYSDATE) -
TRUNC(TO_DATE(decode(dit.FCREATE_DATE,
'',
to_char(sysdate - 10,
'YYYY-MM-DD HH24:MI:SS'),
dit.FCREATE_DATE),
'YYYY-MM-DD HH24:MI:SS')) <= 8) qyv
where qyv.rn > 0
AND qyv.rn <= 50;
