ECC Procurement, Purchase Orders

select
x.*
from
(
SELECT * FROM
     (
         SELECT  /*+ leading(gt) */
             gt.char1                               ecc_spec_id,  DFV_H.*,   po_header.po_header_id,
             po_header.segment1                     order_number,
             po_header.revision_num                 po_revision,
             /*po_pcc_orders_util_pvt.get_po_status(po_header.po_header_id,houtl.language) po_status,*/
             (SELECT plc_sta.meaning
             FROM fnd_lookup_values plc_sta
             WHERE plc_sta.lookup_code = decode(po_header.approved_flag, 'R', po_header.approved_flag,nvl(po_header.authorization_status,'INCOMPLETE'))
             and    plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE')
             AND   plc_sta.LANGUAGE = houtl.language)
             ||
             decode(po_header.closed_code, 'OPEN', '', '', '',
				     ', '||decode(nvl(po_header.closed_code, 'OPEN'), 'OPEN', NULL,
             (SELECT plc_clo.meaning FROM fnd_lookup_values plc_clo WHERE plc_clo.lookup_code = nvl(po_header.closed_code, 'OPEN')
              and    plc_clo.lookup_type = 'DOCUMENT STATE'
              AND    plc_clo.LANGUAGE = houtl.language)
              ))
              ||
              decode(nvl(po_header.cancel_flag, 'N'), 'N', '', '', '',
			        ', '||decode(po_header.cancel_flag,'Y',
              (SELECT plc_can.meaning FROM fnd_lookup_values plc_can WHERE plc_can.lookup_code ='CANCELLED'
               AND plc_can.lookup_type = 'DOCUMENT STATE' AND plc_can.LANGUAGE = houtl.language ), NULL))
               ||
               decode(nvl(po_header.frozen_flag, 'N'), 'N', '', '', '',
				       ', '||decode(po_header.frozen_flag,'Y', (SELECT plc_fro.meaning FROM fnd_lookup_values plc_fro WHERE plc_fro.lookup_code ='FROZEN'
               AND plc_fro.lookup_type = 'DOCUMENT STATE' AND     plc_fro.LANGUAGE = houtl.language ), NULL))
               ||
               decode(nvl(po_header.user_hold_flag, 'N'), 'N', '', '', '',
				       ', '||decode(po_header.user_hold_flag,'Y', (SELECT plc_hld.meaning FROM fnd_lookup_values plc_hld WHERE plc_hld.lookup_code ='ON HOLD'
               AND plc_hld.lookup_type = 'DOCUMENT STATE' AND     plc_hld.LANGUAGE = houtl.language ), NULL))
               ||
               decode(Nvl(gt.char5,'N'), 'N', '', '', '',
				       ', '||(SELECT flv.meaning
               FROM fnd_lookup_values flv
               WHERE flv.lookup_type = 'DOCUMENT STATE'
               AND flv.lookup_code = 'RESERVED'
               AND flv.LANGUAGE = houtl.language)) po_status,
     /*po_headers_sv3.get_po_status(po_header.po_header_id) po_status,*/
             doc_style.style_name                   po_document_style,
             (
                 SELECT
                     emp.full_name
                 FROM
                     per_all_people_f emp
                 WHERE
                     emp.person_id = po_header.agent_id
                     AND emp.effective_start_date = (
                         SELECT
                             MAX(c.effective_start_date)
                         FROM
                             per_all_people_f c
                         WHERE
                             emp.person_id = c.person_id)
				AND rownum < 2
             ) po_buyer_name,
             ap_supplier.vendor_name                po_supplier_name,
             po_header.vendor_id                    po_supplier_id,
             (
                 SELECT
                     apst.vendor_site_code
                 FROM
                     ap_supplier_sites_all apst
                 WHERE
                     apst.vendor_site_id = po_header.vendor_site_id
             ) po_supplier_site,
             (
                 SELECT
                     pvc.first_name
                     || ' '
                     || pvc.last_name
                 FROM
                     po_vendor_contacts pvc
                 WHERE
                     pvc.vendor_contact_id = po_header.vendor_contact_id
                     AND pvc.vendor_site_id = po_header.vendor_site_id
             ) po_supplier_contact,
             po_header.currency_code                po_currency,
             ( CASE
                 WHEN po_header.closed_code = 'FINALLY CLOSED'  THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_FINALLY_CLOSED'
                ,'201',houtl.language)
                 WHEN po_header.closed_code = 'CLOSED'         THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_CLOSED','201',houtl.
                 language)
                 WHEN po_header.cancel_flag = 'Y'              THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_CANCELED','201',houtl.language
                 )
                 WHEN po_header.user_hold_flag = 'Y'  THEN
                    po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_ON_HOLD','201',houtl.language
                 )
                 WHEN po_header.authorization_status IN (
                     'REJECTED'
                 ) THEN (
                     SELECT
                         flv.meaning
                     FROM
                         fnd_lookup_values flv
                     WHERE
                         flv.lookup_type = 'AUTHORIZATION STATUS'
                         AND flv.lookup_code = 'REJECTED'
                         AND flv.language = houtl.language
                 )
                 WHEN nvl(po_header.authorization_status,'INCOMPLETE') IN (
                     'INCOMPLETE'
                 ) THEN (
                     SELECT
                         flv.meaning
                     FROM
                         fnd_lookup_values flv
                     WHERE
                         flv.lookup_type = 'AUTHORIZATION STATUS'
                         AND flv.lookup_code = 'INCOMPLETE'
                         AND flv.language = houtl.language
                 )
                 WHEN nvl(po_header.authorization_status,'INCOMPLETE') = 'APPROVED' and nvl(po_header.pending_signature_flag,'N') = 'Y' and nvl(po_header.acceptance_required_flag, 'N') <> 'P'
                 and not exists (select 1 from   PO_ACCEPTANCES
                      where  po_header_id = po_header.po_header_id and revision_num = po_header.revision_num
                      and    po_release_id       is null and    po_line_location_id is null
                      and    accepting_party  = 'S' and accepted_flag    = 'Y' and signature_flag   = 'Y'	)
                THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_REQ_SIGN','201',houtl.language)
                 WHEN nvl(po_header.authorization_status,'INCOMPLETE') = 'APPROVED' and Nvl(po_header.acceptance_required_flag,'N') IN ('Y','D')
                      and NOT EXISTS (SELECT 1 FROM po_acceptances poa WHERE poa.po_header_id=po_header.po_header_id AND poa.revision_num=po_header.revision_num)
                 THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_SUPP_ACK','201',houtl.language)
		/* WHEN EXISTS (SELECT 1
					  FROM po_distributions_all pda
					  WHERE pda.po_header_id = po_header.po_header_id
					  AND failed_funds_lookup_code like 'F%')
			  THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_PEND_FUNDS','201',houtl.language) */
                 WHEN po_header.authorization_status IN (
                     'PRE-APPROVED'
                 ) THEN (
                     SELECT
                         flv.meaning
                     FROM
                         fnd_lookup_values flv
                     WHERE
                         flv.lookup_type = 'AUTHORIZATION STATUS'
                         AND flv.lookup_code = 'PRE-APPROVED'
                         AND flv.language = houtl.language
                 )
                 WHEN EXISTS (
                     SELECT
                         1
                     FROM
                         po_change_requests
                     WHERE
                         document_header_id = po_header.po_header_id
                         AND request_status = 'PENDING'
                 ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_PEND_CHG_REQ','201',houtl.language)
                 WHEN po_header.authorization_status IN (
                     'IN PROCESS'
                 ) THEN (
                     SELECT
                         flv.meaning
                     FROM
                         fnd_lookup_values flv
                     WHERE
                         flv.lookup_type = 'AUTHORIZATION STATUS'
                         AND flv.lookup_code = 'IN PROCESS'
                         AND flv.language = houtl.language
                 )
                 WHEN po_header.authorization_status = 'APPROVED'
                      AND EXISTS (
                     SELECT
                         1
                     FROM
                         po_line_locations_all plla
                     WHERE
                         plla.need_by_date IS NOT NULL
                         AND plla.po_header_id = po_header.po_header_id
                         AND nvl(plla.promised_date,plla.need_by_date) < SYSDATE
                         AND plla.po_header_id = po_header.po_header_id
                         AND ( nvl( (plla.quantity - plla.quantity_cancelled),0) - nvl(plla.quantity_received,0) ) > 0
                 ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_HAS_OVERDUE_SHIP','201',houtl.language)
                 WHEN EXISTS (
                     SELECT
                         1
                     FROM
                         po_line_locations_all plla
                     WHERE
                         plla.po_header_id = po_header.po_header_id
                         AND plla.quantity_rejected > 0
                 ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_HAS_REJECTIONS','201',houtl.language)
                 WHEN po_header.authorization_status = 'APPROVED'
                      AND EXISTS (
                     SELECT
                         1
                     FROM
                         po_lines_all pla,
                         po_lines_all agrla,
                         po_line_locations_all plla,
                         po_attribute_values pav
                     WHERE
                         plla.po_line_id = pla.po_line_id
                         AND pla.from_line_id = agrla.po_line_id (+)
                         AND agrla.po_line_id = pav.po_line_id (+)
                         AND plla.po_header_id = po_header.po_header_id
                         AND nvl(plla.promised_date,plla.need_by_date) - SYSDATE < pav.lead_time
                 ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_DELAY_ANTICIPATE','201',houtl.language)
                 WHEN po_header.frozen_flag = 'Y'               THEN po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_FROZEN','201',houtl.language
                 )
		/*WHEN exists (select 1 from po_buyer_actions WHERE Trunc(Nvl(completion_date,SYSDATE ))- Trunc(TARGET_DATE)> 0
					 and status  <> 'CLOSED'  and entity_type = 'PO_HEADER' and entity_id = po_header.po_header_id)
			THEN 'Overdue Notes Exist' */
 
			WHEN EXISTS(
                 SELECT
                         1
                       FROM
                         po_acceptances pac
                  WHERE
                  pac.accepted_flag = 'N'
                          AND pac.po_header_id = po_header.po_header_id) THEN (
                     SELECT
                         flv.meaning
                     FROM
                         fnd_lookup_values flv
                     WHERE
                         lookup_type = 'AUTHORIZATION STATUS'
                         AND lookup_code = 'REJECTED'
                         AND flv.language = houtl.language
                 )
 
                 ELSE (
                     SELECT
                         flv.meaning
                     FROM
                         fnd_lookup_values flv
                     WHERE
                         lookup_type = 'AUTHORIZATION STATUS'
                         AND lookup_code = po_header.authorization_status
                         AND flv.language = houtl.language
                 )
             END ) po_sub_status,
             po_header.creation_date                po_creation_date,
             po_header.approved_date                po_approved_date,
             po_header.submit_date                  po_submit_date,
             houtl.name                             po_ou_name,
             ( CASE
                 WHEN EXISTS (
                     SELECT
                         1
                     FROM
                         po_change_requests
                     WHERE
                         document_header_id = po_header.po_header_id
                         AND request_status = 'PENDING'
                 ) THEN 'Yes'
                 ELSE 'No'
             END ) po_open_change_requests,
             (
                 SELECT
                     apt.name
                 FROM
                     ap_terms apt
                 WHERE
                     apt.term_id = po_header.terms_id
             ) po_payment_terms,
             houtl.language,
             gt.num6                                po_ordered_amount,
             gt.num7                                po_received_amount,
             gt.num8                                po_invoiced_amount,
             DECODE(po_header.type_lookup_code,'STANDARD', (CASE
                 WHEN nvl(po_header.authorization_status,'INCOMPLETE') IN(
                     'REQUIRES REAPPROVAL'
                 )
                      OR EXISTS(
                     SELECT
                         1
                     FROM
                         po_change_requests
                     WHERE
                         document_header_id = po_header.po_header_id
                         AND request_status = 'PENDING'
                 ) THEN 'Yes'
                 ELSE 'No'
             END),NULL) orders_to_watch,
             po_header.comments                     po_description,
             DECODE(gt.char5,'Y', (
                 SELECT
                     polc.displayed_field
                 FROM
                     po_lookup_codes polc
                 WHERE
                     polc.lookup_type = 'DOCUMENT STATE'
                     AND polc.lookup_code = 'RESERVED'
             ), (
                 SELECT
                     polc.displayed_field
                 FROM
                     po_lookup_codes polc
                 WHERE
                     polc.lookup_type = 'DOCUMENT STATE'
                     AND polc.lookup_code = 'NOT RESERVED'
             ) ) po_reservation_status,
             ( CASE
                 WHEN ( po_header.closed_code IN (
                     'CLOSED',
                     'FINALLY CLOSED'
                 )
                        OR po_header.cancel_flag LIKE 'Y' ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_CLOSED_STATUS','201'
                       ,houtl.language)
                 WHEN ( nvl(po_header.authorization_status,'INCOMPLETE') IN (
                     'INCOMPLETE',
                     'REQUIRES REAPPROVAL',
                     'REJECTED'
                 ) ) THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_DRAFT_STATUS','201',houtl.language)
                 WHEN ( po_header.authorization_status IN (
                     'IN PROCESS',
                     'PRE-APPROVED'
                 )
                        OR ((nvl(po_header.authorization_status,'INCOMPLETE') = 'APPROVED' and nvl(po_header.pending_signature_flag,'N') = 'Y' and nvl(po_header.acceptance_required_flag, 'N') <> 'P'
              and not exists (select 1 from   PO_ACCEPTANCES
                      where  po_header_id = po_header.po_header_id and revision_num = po_header.revision_num
                      and    po_release_id       is null and    po_line_location_id is null
                      and    accepting_party  = 'S' and accepted_flag    = 'Y' and signature_flag   = 'Y'	))
              or (nvl(po_header.authorization_status,'INCOMPLETE') = 'APPROVED' and Nvl(po_header.acceptance_required_flag,'N') IN ('Y','D')
                      and NOT EXISTS (SELECT 1 FROM po_acceptances poa WHERE poa.po_header_id=po_header.po_header_id AND poa.revision_num=po_header.revision_num)))
         )  THEN po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_INPROC_STATUS','201',houtl.language)
                 ELSE po_pcc_orders_util_pvt.get_fnd_message('PO_PCC_OPEN_STATUS','201',houtl.language)
             END ) po_group_status,
             CASE
                 WHEN ( po_header.currency_code IS NULL
                        OR gt.char2 = po_header.currency_code ) THEN gt.num6
                 ELSE nvl(po_header.rate,1) * gt.num6
             END AS ordered_amount_func,
             CASE
                 WHEN ( po_header.currency_code IS NULL
                        OR gt.char2 = po_header.currency_code ) THEN gt.num7
                 ELSE nvl(po_header.rate,1) * gt.num7
             END AS received_amount_func,
             CASE
                 WHEN ( po_header.currency_code IS NULL
                        OR gt.char2 = po_header.currency_code ) THEN gt.num8
                 ELSE nvl(po_header.rate,1) * gt.num8
             END AS invoiced_amount_func,
             gt.num9                                AS cycle_time,
             gt.char3                               doc_authorized_user_ids,
             gt.char4                               security_level_code,
             po_header.org_id                       org_id,  DFV_L.*,  po_line.po_line_id                     po_line_id,
             po_line.line_num                       po_line_num,
             (
                 SELECT
                     line_type
                 FROM
                     po_line_types_tl lt
                 WHERE
                     lt.line_type_id = po_line.line_type_id
                     AND lt.language = houtl.language
             ) po_line_type,
             (
                 SELECT
                     mtl_sys_item.concatenated_segments
                 FROM
                     mtl_system_items_b_kfv mtl_sys_item
                 WHERE
                     nvl(nvl(mtl_sys_item.organization_id,fsp.inventory_organization_id),-99) = nvl(fsp.inventory_organization_id
                    ,-99)
                     AND po_line.item_id = mtl_sys_item.inventory_item_id (+)
             ) po_item_number,
             po_line.item_description               po_item_description,
             po_mtl_cat.concatenated_segments       po_item_category,
             po_line.quantity                       po_line_quantity,
             po_line.unit_meas_lookup_code          po_uom,
             po_line.unit_price                     po_line_price,
             DECODE(nvl(po_line.closed_code,'OPEN'),'OPEN',po_pcc_orders_util_pvt.get_fnd_message('PO_OPEN','201',houtl.language)
            ,'CLOSED FOR RECEIVING',po_pcc_orders_util_pvt.get_fnd_message('PO_STATUS_CLOSED','201',houtl.language),po_pcc_orders_util_pvt
             .get_fnd_message('PO_STATUS_CLOSED','201',houtl.language) ) po_line_status,
             gt.char2                               po_func_currency,
             (
                 SELECT
                     neg_header.document_number
                 FROM
                     pon_auction_headers_all neg_header
                 WHERE
                     po_line.auction_header_id = neg_header.auction_header_id
             ) neg_number,
             DECODE(po_line.matching_basis,'AMOUNT',po_line.amount, (po_line.quantity * nvl(po_line.unit_price,0) ) ) po_line_amount
            ,
             po_line.note_to_vendor                 note_to_supplier,
             (
                 SELECT
                     segment1
                 FROM
                     po_headers_all
                 WHERE
                     po_header_id = nvl(po_line.from_header_id,po_line.contract_id)
             ) agreement,
             nvl(po_line.from_header_id,po_line.contract_id) source_header_id,
             po_line.from_line_id                   source_line_id,  DFV_S.*,  po_lineloc.line_location_id            po_lineloc_id,
             po_lineloc.shipment_num                po_shipment_num,
             po_lineloc.quantity                    po_ship_ordered_quantity,
             /*(
                 SELECT
                     location_code
                 FROM
                     po_ship_to_loc_org_v
                 WHERE
                     location_id = po_lineloc.ship_to_location_id
             ) po_ship_to_location,*/
            (CASE WHEN po_lineloc.DROP_SHIP_FLAG = 'Y' then
                    (select address1||'-'||city
                    from hz_locations
                    where location_id = po_lineloc.ship_to_location_id)
            else (select location_code
                    from hr_locations_all
                    where location_id = po_lineloc.ship_to_location_id)
             end) po_ship_to_location,
             DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount,0),nvl(po_lineloc.price_override * (po_lineloc.quantity
             - po_lineloc.quantity_cancelled),0) ) shipment_amount,
             po_lineloc.quantity_received           po_ship_received_quantity,
             po_lineloc.quantity_rejected           po_ship_rejected_quantity,
             po_lineloc.quantity_billed             po_ship_invoiced_quantity,
             po_lineloc.quantity_cancelled          po_ship_cancelled_quantity,
             po_lineloc.quantity_shipped            po_ship_shipped_quantity,
             DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount,0),nvl(po_lineloc.price_override * (po_lineloc.quantity
             - po_lineloc.quantity_cancelled),0) ) po_ship_ordered_amount,
             DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount_shipped,0),nvl(po_lineloc.price_override * po_lineloc
             .quantity_shipped,0) ) po_ship_shipped_amount,
             DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount_received,0),nvl(po_lineloc.price_override * po_lineloc
             .quantity_received,0) ) po_ship_received_amount,
             DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount_rejected,0),nvl(po_lineloc.price_override * po_lineloc
             .quantity_rejected,0) ) po_ship_rejected_amount,
             DECODE(po_lineloc.matching_basis,'AMOUNT',nvl(po_lineloc.amount_billed,0),nvl(po_lineloc.price_override * po_lineloc
             .quantity_billed,0) ) po_ship_invoiced_amount,
             po_lineloc.price_override              po_unit_price,
             po_lineloc.need_by_date                po_need_by_date,
             po_lineloc.promised_date               po_promised_date,
             ( CASE
                 WHEN ( nvl(po_lineloc.promised_date,po_lineloc.need_by_date) IS NOT NULL
                        AND nvl(po_lineloc.promised_date,po_lineloc.need_by_date) < SYSDATE )
                      AND po_lineloc.approved_flag = 'Y'
                      AND ( nvl( (po_lineloc.quantity - po_lineloc.quantity_cancelled),0) - nvl(po_lineloc.quantity_received,0) )
                      > 0 THEN 'Y'
                 ELSE 'N'
             END ) overdue_flag,
             (
                 SELECT
                     nvl(plla.promised_date,plla.need_by_date)
                 FROM
                     po_line_locations_all plla
                 WHERE
                     plla.line_location_id = po_lineloc.line_location_id
                     AND ( nvl(plla.promised_date,plla.need_by_date) IS NOT NULL )
                     AND plla.approved_flag = 'Y'
                     AND DECODE(plla.matching_basis,'QUANTITY', (nvl( (plla.quantity - plla.quantity_cancelled),0) - nvl(plla.quantity_received
                    ,0) ), (nvl( (plla.amount - plla.amount_cancelled),0) - nvl(plla.amount_received,0) ) ) > 0
             ) need_by_date_open_shipment,
             invoice_holds.invoice_on_hold invoice_on_hold,   DFV_D.*,  po_dist.po_distribution_id             po_dist_id,
             po_dist.distribution_num               po_dist_num,
             (
                 SELECT
                     flv.meaning
                 FROM
                     fnd_lookup_values flv
                 WHERE
                     flv.lookup_type = 'DESTINATION TYPE'
                     AND flv.lookup_code = po_dist.destination_type_code
                     AND flv.language = houtl.language
             ) po_dist_destination_type,
             (
                 SELECT
                     concatenated_segments
                 FROM
                     gl_code_combinations_kfv gl_code_kfv
                 WHERE
                     code_combination_id = po_dist.code_combination_id
             ) po_dist_charge_account,
             pa_project.name                        project_name,
             pa_project.segment1                    project_number,
             pa_task.task_name                      task_name,
             pa_task.task_number                    task_number,
             po_dist.expenditure_type               po_expenditure_type,
             houtl.name                             PO_EXPENDITURE_ORG,
             po_dist.expenditure_item_date          po_expenditure_item_date,
             (
                 SELECT
                     full_name
                 FROM
                     hr_employees hr_emp
                 WHERE
                     hr_emp.employee_id = po_dist.deliver_to_person_id
                     and rownum <2
             ) po_dist_requester,
 
             nvl(  (
                 SELECT
                     req_header.segment1