ECC Procurement, Agreements, SQL2

Description
Columns: Ecc Spec Id, 'Po Ord L Row Id', 'Po Ord L Context', 'Po Ord L Attribute 13', 'Po Ord L Order Sequence', 'Po Ord L Concatenated Segments', Agreement Line, Line Number, Item, Item Description ...
Imported from ECC
select * from (SELECT
gt.char1 ecc_spec_id, DFV.*,
agr_line.po_line_id                  agreement_line,
agr_line.line_num                    line_number,
     (
         SELECT
             line_type
         FROM
             po_line_types_tl lt
         WHERE
             lt.line_type_id = agr_line.line_type_id
             AND lt.language  = houtl.language
     ) line_type,
     mtl_sys_item.concatenated_segments   item,
     agr_line.item_description,
     mtl_cat_kfv.concatenated_segments    category,
     agr_line.unit_meas_lookup_code       uom,
     agr_line.unit_price                  unit_price,
     DECODE( NVL(agr_line.closed_code, 'OPEN'),
               'OPEN', po_pcc_agreements_util_pvt.get_fnd_message('PO_OPEN', '201',houtl.language),
               'CLOSED FOR RECEIVING', po_pcc_agreements_util_pvt.get_fnd_message('PO_STATUS_CLOSED', '201',houtl.language),
               po_pcc_agreements_util_pvt.get_fnd_message ('PO_STATUS_CLOSED', '201',houtl.language)) line_status	,
 
     agr_attr_values.lead_time            lead_time,
     (SELECT Count(1)
                   FROM po_line_locations_all pll
                   WHERE pll.po_line_id = agr_line.po_line_id
  )  	no_price_breaks,
 
  (SELECT document_number FROM pon_auction_headers_all paha
  WHERE paha.auction_header_id= agr_line.auction_header_id) negotiation,
 /* (SELECT
                LISTAGG(poh.segment1,'|') WITHIN GROUP  (ORDER BY nvl(pla.from_header_id,pla.contract_id)) AS order_num
                  FROM
                  po_lines_all pla,
                  po_headers_all poh
                WHERE
                  nvl(pla.from_header_id,pla.contract_id) = agr_header.po_header_id
                  AND poh.po_header_id=pla.po_header_id
                  GROUP BY nvl(pla.from_header_id,pla.contract_id))*/ null AS ORDER_num,
 
  ( SELECT Sum(plla.quantity) FROM  po_lines_all pla,po_line_locations_all  plla
 WHERE  pla.from_line_id=agr_line.po_line_id
 AND plla.po_line_id=pla.po_line_id) Released_Quantity,
   ( SELECT Sum(plla.quantity_shipped) FROM  po_lines_all pla,po_line_locations_all  plla
 WHERE  pla.from_line_id=agr_line.po_line_id
 AND plla.po_line_id=pla.po_line_id) Shipped_Quantity,
  ( SELECT Sum(plla.quantity_received) FROM  po_lines_all pla,po_line_locations_all  plla
 WHERE  pla.from_line_id=agr_line.po_line_id
 AND plla.po_line_id=pla.po_line_id) Received_Quantity,
  ( SELECT Sum(plla.quantity_rejected) FROM  po_lines_all pla,po_line_locations_all  plla
 WHERE  pla.from_line_id=agr_line.po_line_id
 AND plla.po_line_id=pla.po_line_id) Rejected_Quantity,
  ( SELECT Sum(plla.quantity_billed ) FROM  po_lines_all pla,po_line_locations_all  plla
 WHERE  pla.from_line_id=agr_line.po_line_id
 AND plla.po_line_id=pla.po_line_id) Invoiced_Quantity,
  po_pcc_agreements_util_pvt.get_ga_line_amount_released(agr_line.po_line_id,agr_line.po_header_id) AS line_released_amount,
 
  (SELECT Sum(DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount_received, 0),
	                Nvl(po_line.unit_price*po_lineloc.quantity_received, 0)))
   FROM  po_lines_all po_line,
po_line_locations_all po_lineloc ,
po_headers_all po_orders
 
WHERE
po_line.from_line_id= agr_line.po_line_id
AND po_orders.po_header_id = po_line.po_header_id
and po_line.po_line_id = po_lineloc.po_line_id)  line_received_amount,
 
 
(SELECT Sum(DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount_billed, 0),
	                Nvl(po_line.unit_price*po_lineloc.quantity_billed, 0)))
   FROM  po_lines_all po_line,
po_line_locations_all po_lineloc ,
po_headers_all po_orders
 
WHERE
po_line.from_line_id= agr_line.po_line_id
AND po_orders.po_header_id = po_line.po_header_id
and po_line.po_line_id = po_lineloc.po_line_id)  line_invoiced_amount,
 
(SELECT Sum(DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount_shipped, 0),
	                    Nvl(po_line.unit_price*po_lineloc.quantity_shipped, 0)))
   FROM  po_lines_all po_line,
po_line_locations_all po_lineloc ,
po_headers_all po_orders
 
WHERE
po_line.from_line_id= agr_line.po_line_id
AND po_orders.po_header_id = po_line.po_header_id
and po_line.po_line_id = po_lineloc.po_line_id)  line_shipped_amount,
 
 
     (
         SELECT
             DECODE(flv.lookup_code,'APPROVED', (CASE
                 WHEN agr_header.closed_code = 'CLOSED'         THEN 'PO_STATUS_CLOSED'
                 WHEN agr_header.closed_code = 'FINALLY CLOSED' THEN 'PO_STATUS_FINALLY_CLOSED'
                 WHEN nvl(agr_header.cancel_flag,'N') = 'Y'    THEN 'PO_STATUS_CANCELED'
                 WHEN nvl(agr_header.frozen_flag,'N') = 'Y'    THEN 'PO_STATUS_FROZEN'
                 WHEN nvl(agr_header.user_hold_flag,'N') = 'Y' THEN 'PO_STATUS_ON_HOLD'
                 ELSE flv.lookup_code
             END),flv.lookup_code)
         FROM
             fnd_lookup_values_vl flv
         WHERE
             nvl(agr_header.authorization_status,'INCOMPLETE') = flv.lookup_code
             AND flv.lookup_type = 'AUTHORIZATION STATUS'
             AND agr_header.po_header_id IS NOT NULL
     ) agreement_status_code,
 
     houtl.language ,
     agr_line.vendor_product_num          SUPPLIER_ITEM_NUM
 
FROM
po_headers_all agr_header,
po_lines_all agr_line,
po_attribute_values agr_attr_values,
mtl_categories_kfv mtl_cat_kfv,
mtl_system_items_b_kfv mtl_sys_item,
financials_system_params_all fsp ,
hr_all_organization_units_tl houtl,
po_session_gt gt, (select 'PO_ORD_L_ROW_ID','PO_ORD_L_CONTEXT','PO_ORD_L_ATTRIBUTE_13','PO_ORD_L_ORDER_SEQUENCE','PO_ORD_L_CONCATENATED_SEGMENTS' from dual where 1=2  union select ROWIDTOCHAR(ROW_ID),CONTEXT,ATTRIBUTE_13,ORDER_SEQUENCE,CONCATENATED_SEGMENTS from PO_LINES_ALL_DFV) DFV
 
 
WHERE
 
gt.KEY=3131017
AND agr_header.po_header_id=gt.num1
AND agr_line.po_line_id=gt.num2
AND agr_line.po_line_id = agr_attr_values.po_line_id (+)
AND agr_line.category_id = mtl_cat_kfv.category_id (+)
AND agr_line.item_id = mtl_sys_item.inventory_item_id (+)
AND agr_header.org_id = fsp.org_id (+)
AND nvl(nvl(mtl_sys_item.organization_id,fsp.inventory_organization_id),-99) = nvl(fsp.inventory_organization_id,-99)
AND houtl.organization_id (+) = agr_header.org_id
AND houtl.LANGUAGE IN ('US')
and agr_line.rowid = dfv."'PO_ORD_L_ROW_ID'" (+)
)
PIVOT (max(line_type) as line_type ,
       max(line_status) as line_status
 for LANGUAGE in ('US' "US"))