CSE Assumed Loss Rate

select
 cidv.INVENTORY_ITEM_ID               INVENTORY_ITEM_ID,
 cidv.INVENTORY_REVISION            REVISION,
 substr(msi.description,1,45)             ITEM_DESCRIPTION,
  msi.concatenated_segments           INVENTORY_ITEM,
 cidv.SERIAL_NUMBER                      SERIAL_NUMBER,
 cidv.LOT_NUMBER                            LOT_NUMBER,
 cidv.QUANTITY                                QUANTITY,
 cidv.INV_ORGANIZATION_ID          ORGANIZATION_ID,
 cidv.PA_PROJECT_ID                       PROJECT_ID,
 cidv.PA_PROJECT_TASK_ID           TASK_ID,
 to_char(cidv.TRANSACTION_DATE,'dd-mon-yy')          TRANSACTION_DATE,
 cidv.SOURCE_TRANSACTION_DATE     SOURCE_TRANSACTION_DATE,
 ppf.person_id                                     PERSON_ID,
 ppf.full_name                                     FULL_NAME,
 houv.name                                         ORGANIZATION_NAME,
 pt.task_number                                 TASK_NUMBER,
 ppa.segment1                                    PROJECT_NUMBER
from
 per_people_f                    ppf,
 hr_organization_units_v houv,
 pa_tasks                          pt,
 pa_projects_all                ppa,
 mtl_system_items_kfv   msi,
 csi_inst_txn_details_v   cidv
where
  houv.name = nvl(:p_organization_name,houv.name)            AND
  ppa.segment1 = nvl(:p_project_number, ppa.segment1)       AND
  pt.task_number =  nvl(:p_task_number, pt.task_number)     AND
  msi.concatenated_segments = 
       nvl(:p_inv_item, msi.concatenated_segments)                 AND 
  cidv.inv_master_organization_id = houv.organization_id                   AND      
  ppf.person_id(+)        = cidv.transacted_by                            AND
  cidv.inventory_item_id = msi.inventory_item_id                   AND
  cidv.inv_master_organization_id = msi.organization_id                    AND
  cidv.pa_project_id = ppa.project_id                                        AND
  cidv.pa_project_task_id = pt.task_id                                     AND 
  ppa.project_id = pt.project_id                                                 AND
  cidv.instance_usage_code = 'IN_PROCESS'                          AND
  to_number(sysdate - cidv.transaction_date) <= :p_day
  ORDER BY 17 ASC,16 ASC,15 ASC,3 ASC,4 ASC