CSI Customer Products Summary (9)

SELECT
      hca.account_number 
   ,  hzp.party_name 
   ,  hl.city install_location 
   ,  (hl.address1 || DECODE( hl.address1, '', '', DECODE(hl.address2, '', '', CHR(44) || CHR(32))) || hl.address2) install_address1 
   ,  (hl.address3 || DECODE( hl.address3, '', '', DECODE( hl.address4, '', '', CHR(44)||CHR(32))) || hl.address4) install_address2 
   ,  SUBSTR( hl.city || DECODE( hl.city, '', '', DECODE(hl.state|| hl.country || hl.postal_code, '', '', CHR(44) || CHR(32))) || hl.state ||CHR(32)||CHR(32) || hl.postal_code|| CHR(32) || CHR (32) || hl.country, 1, 220) install_address3 
   ,  &P_FLEX_ITEM_CODE C_FLEX_ITEM_CODE  
   ,  ccpa.serial_number
   ,  ccpa.unit_of_measure
   ,  ccpa.quantity
   ,  ccps.name status 
   ,  msi.description 
   ,  ccpa.instance_number
   ,  ccpa.inventory_revision
   ,  cs.name system
   ,  oh.order_number
   ,  oh.cust_po_number, 
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flex_item_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') C_FLEX_ITEM_DISP
FROM
      csi_item_instances  ccpa ,
      hz_cust_accounts hca ,
      hz_parties hzp ,
      hz_party_sites hps ,
      hz_locations hl ,
      csi_instance_statuses ccps,
      mtl_system_items msi ,
      csi_systems_v cs ,
      csi_lookups cl1 ,
      oe_order_headers_all oh,
      oe_order_lines_all ol
WHERE 
      ccpa.inventory_item_id = msi.inventory_item_id
  and msi.organization_id = :p_organization_id
  and ccpa.owner_party_account_id = hca.cust_account_id
  and hca.party_id = hzp.party_id
  and ccpa.install_location_id = hps.party_site_id (+)
  and hps.location_id = hl.location_id (+)
  and ccpa.instance_status_id = ccps.instance_status_id
  and ccpa.system_id = cs.system_id (+)
  and ccpa.instance_type_code = cl1.lookup_code (+)
  and cl1.lookup_type (+) = 'CSI_INST_TYPE_CODE'
  and ccpa.last_oe_order_line_id = ol.line_id (+)
  and ol.header_id = oh.header_id (+)
&LP_CUSTOMER_RANGE
&LP_ITEM_RANGE
&LP_INSTALL_LOCATION
&LP_CP_STATUS
and &P_ITEM_WHERE ORDER BY 1 ASC,2 ASC,3 ASC,4 ASC,5 ASC,6 ASC