CSE Assumed Loss Rate

Description
Categories: BI Publisher, Financials, Service
Application: Asset Tracking
Source: Assumed Loss Rate Report (XML)
Short Name: CSELOSDR_XML
DB package: CSE_CSELOSDR_XMLP_PKG
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
Parameter Name SQL text Validation
Item Number
 
LOV Oracle
Organization
 
LOV Oracle
Task Number
 
LOV Oracle
Project Number
 
LOV Oracle
Days Past Issue
 
Number
Report Type
 
LOV Oracle