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
                 FROM
                     po_requisition_headers_all req_header,
                     po_distributions_all pda,
                     po_requisition_lines_all req_line,
                     po_req_distributions_all req_dist
                 WHERE
                     req_line.requisition_header_id = req_header.requisition_header_id
                     AND req_line.requisition_line_id = req_dist.requisition_line_id
                     AND req_dist.distribution_id = pda.req_distribution_id
                     AND pda.po_distribution_id = po_dist.po_distribution_id
            ),po_pcc_orders_util_pvt.get_fnd_message('PO_BACKED_REQUISITION','201',houtl.language)) requisition,
 
             po_dist.destination_subinventory       po_dist_dest_sub_inv,
             deliverable.deliverable_id             okc_deliverable_id,
             deliverable.deliverable_name           okc_deliverable_name,
             (
                 SELECT
                     deliverabletypes_tl.name
                 FROM
                     okc_deliverable_types_tl deliverabletypes_tl
                 WHERE
                     deliverabletypes_tl.deliverable_type_code = deliverable.deliverable_type
                     AND deliverabletypes_tl.language = houtl.language
             ) okc_deliverable_type,
             (
                 SELECT
                     busdoc_tl.name
                 FROM
                     okc_bus_doc_types_tl busdoc_tl
                 WHERE
                     deliverable.business_document_type = busdoc_tl.document_type
                     AND busdoc_tl.language = houtl.language
             ) okc_business_document_type,
             deliverable.business_document_type     okc_doc_type_code,
             deliverable.business_document_number   okc_business_doc_number,
             (
                 SELECT
                     name
                 FROM
                     okc_resp_parties_vl okc_resp_party
                 WHERE
                     okc_resp_party.resp_party_code = deliverable.responsible_party
                     AND okc_resp_party.document_type_class = 'PO'
             ) responsible_party,
             ( CASE
                 WHEN deliverable.responsible_party = 'INTERNAL_ORG' THEN (
                     SELECT
                         employee.full_name   contact_name
                     FROM
                         per_all_people_f employee
                     WHERE
                         employee.effective_start_date = employee.start_date
                         AND employee.person_id = deliverable.internal_party_contact_id
						 AND rownum<2
                 )
                 ELSE (
                     SELECT
                         hpcontact.party_name
                     FROM
                         hz_parties hpcontact
                     WHERE
                         deliverable.external_party_contact_id = hpcontact.party_id
                 )
             END ) okc_party_contact,
             deliverable.actual_due_date            due_date,
             (
                 SELECT
                     meaning
                 FROM
                     fnd_lookup_values fnd_lookup
                 WHERE
                     fnd_lookup.lookup_code = deliverable.deliverable_status
                     AND fnd_lookup.language = houtl.language
                     AND fnd_lookup.lookup_type = 'OKC_DELIVERABLE_STATUS'
                     AND fnd_lookup.language = houtl.language
             ) deliverable_status,
             deliverable.deliverable_status         deliverable_status_code,
             (select NAME
             from HR_ALL_ORGANIZATION_UNITS_TL
             where ORGANIZATION_ID  = po_dist.DESTINATION_ORGANIZATION_ID
             AND LANGUAGE = houtl.language) as destination_organization,
             DECODE(nvl(po_header.cancel_flag,'N'),'Y',PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',houtl.language),
                    PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_NO', '201',houtl.language)) CANCEL_FLAG,
             DECODE(nvl(po_header.acceptance_required_flag,'N'),'Y',PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',houtl.language),
                    PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_NO', '201',houtl.language)) acceptance_required_flag
 
         FROM
             po_headers_all po_header,
             po_doc_style_headers doc_style,
             ap_suppliers ap_supplier,
             hr_all_organization_units_tl houtl,
             po_session_gt gt,  (select "ROW_ID" "'PO_ORD_H_ROW_ID'","CONTEXT" "'PO_ORD_H_CONTEXT'","CONCATENATED_SEGMENTS" "'PO_ORD_H_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT",NULL "CONCATENATED_SEGMENTS" from PO_HEADERS_ALL )) DFV_H,  po_lines_all po_line,
             mtl_categories_kfv po_mtl_cat,
             financials_system_params_all fsp,  (select "ROW_ID" "'PO_ORD_L_ROW_ID'","CONTEXT" "'PO_ORD_L_CONTEXT'","ATTRIBUTE_13" "'PO_ORD_L_ATTRIBUTE_13'","ATTRIBUTE_3" "'PO_ORD_L_ATTRIBUTE_3'","ORDER_SEQUENCE" "'PO_ORD_L_ORDER_SEQUENCE'","CONCATENATED_SEGMENTS" "'PO_ORD_L_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT",ATTRIBUTE13 "ATTRIBUTE_13",ATTRIBUTE3 "ATTRIBUTE_3",(DECODE(ATTRIBUTE_CATEGORY,'2052',ATTRIBUTE1,NULL)) "ORDER_SEQUENCE",ATTRIBUTE13||'.'||ATTRIBUTE3||'.'||(DECODE(ATTRIBUTE_CATEGORY,'2052',ATTRIBUTE_CATEGORY||'.'||ATTRIBUTE1,NULL)) "CONCATENATED_SEGMENTS" from PO_LINES_ALL )) DFV_L,      po_line_locations_all po_lineloc,
             (select line_location_id, count(*) invoice_on_hold
              from ap_holds_all
              where creation_date >= nvl(to_date(to_char(to_timestamp(''),'DD-MON-YY HH24.MI.SS'),'DD-MON-YY HH24.MI.SS'),creation_date)
              and release_lookup_code is null
             AND hold_lookup_code in
              (select hold_lookup_code
                from ap_hold_codes
                where
                hold_type = 'MATCHING HOLD REASON'
                and NVL(inactive_date, trunc(sysdate) + 1) >= trunc(sysdate))
                group by line_location_id)  invoice_holds,   (select "ROW_ID" "'PO_ORD_LL_ROW_ID'","CONTEXT" "'PO_ORD_LL_CONTEXT'","CONCATENATED_SEGMENTS" "'PO_ORD_LL_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT",NULL "CONCATENATED_SEGMENTS" from PO_LINE_LOCATIONS_ALL )) DFV_S,  po_distributions_all po_dist,  (select "ROW_ID" "'PO_ORD_D_ROW_ID'","CONTEXT" "'PO_ORD_D_CONTEXT'","CONCATENATED_SEGMENTS" "'PO_ORD_D_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT",NULL "CONCATENATED_SEGMENTS" from PO_DISTRIBUTIONS_ALL )) DFV_D,  pa_projects_all pa_project,
             pa_tasks pa_task,
             okc_deliverables deliverable
         WHERE
             gt.key = 3332552
             AND po_header.po_header_id = gt.index_num1
             AND po_header.type_lookup_code = 'STANDARD'
             AND doc_style.style_id = po_header.style_id
             AND nvl(doc_style.clm_flag,'N') = 'N'
             AN