R12 PO LINE

select pha.segment1 PO_Number
       ,pla.line_num "Line Number"
       ,pla.closed_code
       ,plla.shipment_num "Shipment Number"
       ,plt.line_type "Line Type"
       ,hou.name Operating_Unit_Code
      ,msib.segment1 "ITEM NAME"
       ,pla.item_revision "ITEM REVISION"
       ,mic.description  "ITEM CATEGORY"
       ,pla.item_description "ITEM DESCRIPTION"
       ,pla.unit_meas_lookup_code UOM
       ,pla.quantity
       ,pla.unit_price
       --,pla.vendor_item
       ,pha.currency_code
       ,plla.promised_date
       ,plla.need_by_date
       ,pla.LIST_PRICE_PER_UNIT
      ,pla.note_to_vendor
       ,plla.note_to_receiver
       ,pla.line_num "Line Number"
       ,pla.closed_code
       ,plla.shipment_num "Shipment Number"
       ,plt.line_type "Line Type"
       ,hou.name Operating_Unit_Code
      ,msib.segment1 "ITEM NAME"
       ,pla.item_revision "ITEM REVISION"
       ,mic.description  "ITEM CATEGORY"
       ,pla.item_description "ITEM DESCRIPTION"
       ,pla.unit_meas_lookup_code UOM
       ,pla.quantity
       ,pla.unit_price
       ,pha.currency_code
       ,plla.promised_date
       ,plla.need_by_date
       ,pla.LIST_PRICE_PER_UNIT
      ,pla.note_to_vendor
       ,plla.note_to_receiver
       ,(pla.unit_price*pla.quantity) Amt
       ,pda.expenditure_type
       ,
(select name from apps.hr_all_organization_units where organization_id=plla.ship_to_organization_id) ship_to_org_id,
(select location_code from apps.hr_locations where location_id=plla.ship_to_location_id) Ship_to_location,
(select name from apps.hr_all_organization_units where organization_id=pda.expenditure_organization_id) EXPENDITURE_ORGANIZATION,
(select gcc.CONCATENATED_SEGMENTS from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id =pda.code_combination_id ) charge_accounts ,
(select gcc.CONCATENATED_SEGMENTS from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id =pda.accrual_account_id ) accrual_accounts
--PHA.closed_code,
--pha.authorization_status
       --,pla.need_by_date
        from apps.po_lines_all pla,
        apps.po_headers_all pha,
        apps.po_line_types plt,
        apps.mtl_system_items_b msib,
        apps.mtl_categories_tl mic,
        hr_operating_units hou,
        apps.po_distributions_all pda,
        apps.po_line_locations_all plla
--apps.pa_tasks pt,
  --gl_code_combinations_kfv gcc
where --pha.segment1='111670179' and
 pha.po_header_id=pla.po_header_id
and pha.po_header_id=pda.po_header_id
and pla.po_line_id=pda.po_line_id
  and pha.closed_code='OPEN'
   and pha.AUTHORIZATION_STATUS ='APPROVED'
   and pha.AUTHORIZATION_STATUS !='Rejected'
  and rtrim(LTRIM(pha.closed_code)) not in( 'Finally Closed','Closed')
  and plt.line_type_id=pla.line_type_id(+)
  and pla.item_id=msib.inventory_item_id(+)
  --and pha.org_id=msib.organization_id
  and pla.category_id= mic.category_id
  and pha.org_id=hou.organization_id
  and pla.org_id=hou.organization_id
  and pha.po_header_id=plla.po_header_id
  and pla.po_line_id=plla.po_line_id
  order by pha.segment1
   ,(pla.unit_price*pla.quantity) Amt
       ,pda.expenditure_type
       , --api.invoice_num,
(select name from apps.hr_all_organization_units where organization_id=plla.ship_to_organization_id) ship_to_org_id,
(select location_code from apps.hr_locations where location_id=plla.ship_to_location_id) Ship_to_location,
(select name from apps.hr_all_organization_units where organization_id=pda.expenditure_organization_id) EXPENDITURE_ORGANIZATION,
(select gcc.CONCATENATED_SEGMENTS from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id =pda.code_combination_id ) charge_accounts ,
(select gcc.CONCATENATED_SEGMENTS from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id =pda.accrual_account_id ) accrual_accounts
--PHA.closed_code,
--pha.authorization_status
       --,pla.need_by_date
        from apps.po_lines_all pla,
        apps.po_headers_all pha,
        apps.po_line_types plt,
        apps.mtl_system_items_b msib,
        apps.mtl_categories_tl mic,
        hr_operating_units hou,
        apps.po_distributions_all pda,
        apps.po_line_locations_all plla
--apps.pa_tasks pt,
  --gl_code_combinations_kfv gcc
where --pha.segment1='111670179' and
 pha.po_header_id=pla.po_header_id
and pha.po_header_id=pda.po_header_id
and pla.po_line_id=pda.po_line_id
  and pha.closed_code='OPEN'
   and pha.AUTHORIZATION_STATUS ='APPROVED'
   and pha.AUTHORIZATION_STATUS !='Rejected'
  and rtrim(LTRIM(pha.closed_code)) not in( 'Finally Closed','Closed')
  and plt.line_type_id=pla.line_type_id(+)
  and pla.item_id=msib.inventory_item_id(+)
  --and pha.org_id=msib.organization_id
  and pla.category_id= mic.category_id
  and pha.org_id=hou.organization_id
  and pla.org_id=hou.organization_id
  and pha.po_header_id=plla.po_header_id
  and pla.po_line_id=plla.po_line_id
  order by pha.segment1