ECC Procurement, Requisitions

select
x.*
from
(
select * from (
SELECT
req_header.requisition_header_id || '-' || req_line.requisition_line_id || '-' || req_dist.distribution_Id  AS ecc_spec_id,  DFV_H.*,  DFV_L.*,  req_header.requisition_header_id requisition_header_id,
req_header.segment1 requisition,
req_header.description description,
CASE
  WHEN req_header.closed_code = 'CLOSED'
  THEN fnd_message.Get_string('PO', 'PO_STATUS_CLOSED')
  WHEN req_header.closed_code = 'FINALLY CLOSED'
  THEN fnd_message.Get_string('PO', 'PO_STATUS_FINALLY_CLOSED')
  WHEN NVL(req_header.cancel_flag, 'N') = 'Y'
  THEN fnd_message.Get_string('PO', 'PO_STATUS_CANCELED')
  ELSE status_lkup.meaning
END  status,
status_lkup.lookup_code requisition_status_code,
status_lkup.LANGUAGE status_language,
nvl((SELECT emp.full_name FROM per_all_people_f emp WHERE emp.person_id = req_line.suggested_buyer_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_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_BUYER_UNASSIGNED', '201',status_lkup.language)) buyer_name,
req_header.preparer_id,
( SELECT emp.full_name
  FROM   per_all_people_f emp
  WHERE  emp.person_id = req_header.preparer_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
  ) 	preparer	,
/*por_view_reqs_pkg.get_req_total(req_header.requisition_header_id) amount	,*/
 ( select nvl(SUM(decode(prl.matching_basis, 'AMOUNT', prl.amount,
prl.unit_price * (prl.quantity - nvl(prl.quantity_cancelled,0)))),0)
 
    from
      po_requisition_lines_all prl
    where
      prl.requisition_header_id = req_header.requisition_header_id
      and nvl(prl.modified_by_agent_flag, 'N') = 'N'
      and nvl(prl.cancel_flag, 'N') = 'N') amount,
(SELECT sob.currency_code
FROM gl_sets_of_books sob,financials_system_params_all fsp
WHERE sob.set_of_books_id = fsp.set_of_books_id
AND fsp.org_id = req_header.org_id)
AS currency , -- functional currency
(SELECT description
  FROM   fnd_lookup_values
  WHERE  lookup_type = 'REQUISITION TYPE'
  AND lookup_code = req_header.type_lookup_code
  AND LANGUAGE = status_lkup.LANGUAGE
) 	requisition_type	,
req_header.creation_date 	creation_date	,
req_header.approved_date 	approved_date	,
EXTRACT( YEAR FROM approved_date ) approved_date_year,
To_Char(approved_date,'Month') approved_date_month,
To_Char(NULL) approved_date_range,
(SELECT houtl.name
  FROM   hr_all_organization_units_tl houtl
  WHERE  houtl.organization_id (+) = req_header.org_id
  AND houtl.LANGUAGE = status_lkup.LANGUAGE
) 	operating_unit	,
  req_header.org_id org_id,
  req_line.requisition_line_id requisition_line_id,
req_line.item_id,
req_line.line_num 	line_number,
  mtl_sys_item.concatenated_segments item,
req_line.item_description item_description,
req_line.category_id,
req_mtl_cat.concatenated_segments category,
req_line.quantity quantity,
req_line.quantity - req_line.quantity_cancelled requested_quantity,
req_line.need_by_date need_by_date,
(req_line.need_by_date -
         nvl(((SELECT nvl(lead_time,0)
          FROM icx_cat_attribute_values
          WHERE po_line_id = (SELECT po_line_id
                              FROM po_lines_all
                              WHERE po_header_id = req_line.BLANKET_PO_HEADER_ID
                              AND line_num = req_line.BLANKET_PO_LINE_NUM
                              AND ROWNUM =1)
		  AND rownum<2)), 0)) order_by_date,
EXTRACT( YEAR FROM need_by_date ) need_by_date_year,
To_Char(need_by_date,'Month') need_by_date_month,
NVL(req_line.REQS_IN_POOL_FLAG,'N') reqs_in_pool_flag,
 
req_line.unit_meas_lookup_code uom_code,
req_line.unit_meas_lookup_code uom,
req_line.unit_price unit_price,
req_line.line_type_id,
(SELECT line_type
FROM po_line_types_tl
WHERE line_type_id = req_line.line_type_id
AND LANGUAGE = status_lkup.language)	line_type	,
req_line.matching_basis,
req_line.item_revision  item_revision	,
  DECODE(req_line.matching_basis,
        'AMOUNT', req_line.amount,
		(req_line.quantity-NVL(req_line.quantity_cancelled,0)) * req_line.unit_price
  ) 	line_amount	,
    Nvl2(req_line.currency_code,req_line.currency_code,
    (SELECT sob.currency_code
    FROM gl_sets_of_books sob,financials_system_params_all fsp
    WHERE sob.set_of_books_id = fsp.set_of_books_id
    AND fsp.org_id = req_header.org_id)) line_currency,
(SELECT gsb.currency_code
  FROM   financials_system_params_all fsp,
        gl_sets_of_books gsb
  WHERE  fsp.set_of_books_id = gsb.set_of_books_id
  AND fsp.org_id = req_line.org_id
) 	line_functional_currency	,
(CASE WHEN req_line.MODIFIED_BY_AGENT_FLAG = 'Y' THEN 0
    WHEN req_line.currency_code IS NOT NULL AND req_line.matching_basis = 'AMOUNT'  THEN Nvl(req_line.currency_amount, req_line.amount)
    WHEN req_line.currency_code IS NOT NULL AND req_line.matching_basis <> 'AMOUNT'
     THEN (Nvl(req_line.currency_unit_price,req_line.unit_price) * (req_line.quantity - nvl(req_line.quantity_cancelled,0)))
    WHEN req_line.matching_basis = 'AMOUNT' THEN req_line.amount
    ELSE
          req_line.unit_price *(req_line.quantity - nvl(req_line.quantity_cancelled,0))
  END) 	line_currency_amount	,
  req_header.cancel_flag,
req_line.cancel_flag line_cancel_flag,
  req_header.closed_code,
req_line.closed_code line_closed_code,
req_line.modified_by_agent_flag,
( CASE
  WHEN req_line.cancel_flag='Y'
    THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQ_CANCELLED', '201',status_lkup.language)
  WHEN nvl(req_line.urgent_flag, 'N') = 'Y' AND req_line.REQS_IN_POOL_FLAG='Y'
    THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQLINE_URGENT', '201',status_lkup.language)
  WHEN  req_line.REQS_IN_POOL_FLAG='Y' AND Trunc(req_line.need_by_date) <
            Trunc(SYSDATE + (SELECT nvl(lead_time,0)
                            FROM icx_cat_attribute_values
                            WHERE po_line_id = (SELECT po_line_id
                                                FROM po_lines_all
                                                WHERE po_header_id = req_line.BLANKET_PO_HEADER_ID
                                                AND line_num = req_line.BLANKET_PO_LINE_NUM
                                                AND ROWNUM =1)
							AND rownum<2))
    THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQLINE_LATE2', '201',status_lkup.language)
  WHEN req_line.REQS_IN_POOL_FLAG='Y' AND req_line.need_by_date <= SYSDATE
    THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQLINE_LATE', '201',status_lkup.language)
  WHEN req_line.reqs_in_pool_flag = 'Y'
    THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQLINE_POOL', '201',status_lkup.language)
  WHEN req_line.at_sourcing_flag = 'Y'
    THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQLINE_NEG', '201',status_lkup.language)
    WHEN (SELECT Count(1)
      FROM
      po_distributions_all pod
      WHERE pod.REQ_DISTRIBUTION_ID IN (SELECT distribution_Id FROM po_req_distributions_all WHERE requisition_line_id = req_line.requisition_line_id)
      AND ROWNUM < 2) > 0
    THEN po_pcc_agreements_util_pvt.get_fnd_message ('PO_PCC_REQLINE_ORDER', '201',status_lkup.language)
 
  ELSE
    (SELECT meaning
    FROM   fnd_lookup_values
    WHERE  lookup_type = 'AUTHORIZATION STATUS'
    AND lookup_code = req_header.authorization_status
    AND LANGUAGE = status_lkup.language)
  END
)  line_status	,
req_line.to_person_id requester_id,
( SELECT emp.full_name
	FROM   per_all_people_f emp
	WHERE  emp.person_id = req_line.to_person_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
) requester,
(SELECT SUM(Nvl(prd1.recoverable_tax,0))
  FROM   po_requisition_lines_all prl1,
        po_req_distributions_all prd1
  WHERE  prd1.requisition_line_id = req_line.requisition_line_id
        AND prl1.requisition_line_id = prd1.requisition_line_id
        AND Nvl(prl1.cancel_flag, 'N') = 'N'
        AND Nvl(prl1.modified_by_agent_flag, 'N') = 'N'
)	recoverable_tax	,
(SELECT SUM(Nvl(prd1.nonrecoverable_tax,0))
  FROM   po_requisition_lines_all prl1,
        po_req_distributions_all prd1
  WHERE  prd1.requisition_line_id = req_line.requisition_line_id
        AND prl1.requisition_line_id = prd1.requisition_line_id
        AND Nvl(prl1.cancel_flag, 'N') = 'N'
        AND Nvl(prl1.modified_by_agent_flag, 'N') = 'N'
) 	non_recoverable_tax	,
req_line.suggested_buyer_id,
( SELECT emp.full_name
  FROM   per_all_people_f emp
  WHERE  emp.person_id =req_line.suggested_buyer_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
) 	suggested_buyer	,
req_line.note_to_agent note_to_buyer,
req_line.suggested_vendor_name suggested_supplier	,
(SELECT description
  FROM   hr_locations_all_tl
  WHERE  location_id = req_line.deliver_to_location_id
        AND LANGUAGE = status_lkup.language
) 	deliver_to_location	,
req_line.deliver_to_location_id,
(SELECT LOCATION_CODE FROM HR_LOCATIONS_ALL_TL WHERE location_id = req_line.deliver_to_location_id
and language = status_lkup.language)  ship_to_location,
req_line.destination_type_code,
(SELECT meaning
  FROM   fnd_lookup_values
  WHERE  lookup_type = 'DESTINATION TYPE'
  AND lookup_code = req_line.destination_type_code
  AND LANGUAGE = status_lkup.language
)  destination_type	,
nvl(req_line.urgent_flag, 'N') urgent_flag,
req_line.last_update_date req_last_upd_date,
Decode(req_header.type_lookup_code,
                  'INTERNAL',
                  (SELECT to_char(oe1.order_number)
                    FROM oe_order_headers_all oe1
                    WHERE oe1.order_source_id = 10
                    AND oe1.orig_sys_document_ref = req_header.segment1
                    AND oe1.source_document_id = req_header.requisition_header_id
                    AND rownum=1),
                    po_v.segment1) AS purchase_order,
po_v.po_header_id,
req_line.blanket_po_header_id,
(SELECT segment1 FROM po_headers_all WHERE po_header_id = req_line.blanket_po_header_id) AS agreement,
(SELECT document_number FROM pon_auction_Headers_all WHERE auction_header_id = req_line.auction_header_id) AS negotiation,
 
To_Char(NULL) next_action_owner,
To_Char(NULL) next_action_type,
  case
	           when prl_reserved_status.unreserved_count=0 and  prl_reserved_status.reserved_count>0 then
 
             (SELECT meaning
               FROM fnd_lookup_values POLC
               WHERE POLC.lookup_type = 'DOCUMENT STATE'
               AND POLC.lookup_code = 'RESERVED'
               AND LANGUAGE = status_lkup.language)
			   else
         (SELECT meaning
               FROM fnd_lookup_values POLC
               WHERE POLC.lookup_type = 'DOCUMENT STATE'
               AND POLC.lookup_code = 'NOT RESERVED'
               AND LANGUAGE = status_lkup.language) end AS reservation_status,
 
req_dist.distribution_id distribution_id,
req_header.segment1 || ', ' || req_line.line_Num requisition_line,
req_dist.distribution_num distribution_number,
req_dist.req_line_quantity distribution_quantity,
req_dist.code_combination_id,
(SELECT  concatenated_segments
  FROM gl_code_combinations_kfv  gl_code_kfv
  WHERE code_combination_id = req_dist.code_combination_id
)   	charge_account	,
req_dist.expenditure_type expenditure_type,
(SELECT name
FROM hr_all_organization_units_tl
WHERE organization_id=req_dist.expenditure_organization_id
AND LANGUAGE = status_lkup.language) expenditure_org,
req_dist.expenditure_item_date expenditure_item_date,
 
pa_project.name project ,
pa_project.project_id,
pa_project.project_currency_code project_currency_code,
pa_project.segment1 project_number,
pa_task.task_number task_number,
pa_task.task_id,
To_Char(NULL) project_status,
DECODE(req_line.requisition_line_id,NULL,NULL,pa_task.task_name ) task_name,
DECODE(nvl(req_header.contractor_requisition_flag,'N'),'Y',PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_YES', '201',status_lkup.language),
        PO_PON_ECC_UTIL_PVT.get_fnd_message ('PO_NO', '201',status_lkup.language)) contractor_requisition_flag,
(SELECT name
FROM hr_all_organization_units_tl
WHERE organization_id=req_line.destination_organization_id
AND LANGUAGE = status_lkup.language) destination_organization
 
 
FROM
 
po_requisition_headers_all req_header,
po_requisition_lines_all req_line,
po_req_distributions_all req_dist,
pa_projects_all pa_project,
pa_tasks  pa_task,
mtl_system_items_b_kfv mtl_sys_item,
ap_suppliers ap_supplier,
mtl_categories_kfv req_mtl_cat,
fnd_lookup_values status_lkup,
(SELECT poh.segment1,poh.po_header_id, pod.REQ_DISTRIBUTION_ID
FROM
po_distributions_all pod,
po_headers_all poh
WHERE pod.po_header_id = poh.po_header_id
AND poh.type_lookup_code = 'STANDARD'
) po_v,
ORG_ORGANIZATION_DEFINITIONS OOD,
PO_SYSTEM_PARAMETERS_ALL PSP1,
(SELECT
     PRL1.requisition_line_id,
     COUNT(   DECODE(  PRD1.prevent_encumbrance_flag
                     ,  'Y', NULL
                     ,  DECODE(  PRD1.encumbered_flag
                              ,  'Y', 'Y',  NULL ) ) )  reserved_count,
 
      COUNT(   DECODE(  PRD1.prevent_encumbrance_flag
                     ,  'Y', NULL
                     ,  DECODE(  PRD1.encumbered_flag
                              ,  'Y', NULL,  'N')))  unreserved_count,
 
     COUNT(   DECODE(  PRD1.prevent_encumbrance_flag
                     ,  'Y', 'Y',  NULL)) prevented_count
 
 
 
   FROM
      PO_REQ_DISTRIBUTIONS_ALL PRD1
   ,  PO_REQUISITION_LINES_ALL PRL1
 
 
   WHERE PRL1.requisition_line_id = PRD1.requisition_line_id
   AND   NVL(PRL1.cancel_flag,'N') <> 'Y'
   AND   NVL(PRL1.closed_code,'OPEN') <> 'FINALLY CLOSED'
   GROUP BY PRL1.requisition_line_id)prl_reserved_status ,(select "ROW_ID" "'PO_REQ_H_ROW_ID'","CONTEXT" "'PO_REQ_H_CONTEXT'","CONCATENATED_SEGMENTS" "'PO_REQ_H_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT",NULL "CONCATENATED_SEGMENTS" from PO_REQUISITION_HEADERS_ALL )) DFV_H  ,(select "ROW_ID" "'PO_REQ_L_ROW_ID'","CONTEXT" "'PO_REQ_L_CONTEXT'","CONCATENATED_SEGMENTS" "'PO_REQ_L_CONCATENATED_SEGMENTS'" from ( select ROWID "ROW_ID",ATTRIBUTE_CATEGORY "CONTEXT",NULL "CONCATENATED_SEGMENTS" from PO_REQUISITION_LINES_ALL )) DFV_L  WHERE
req_header.creation_date >= nvl(to_date(to_char(to_timestamp(''),'DD-MON-YY HH24.MI.SS'),'DD-MON-YY HH24.MI.SS'),req_header.creation_date)
and req_header.authorization_status = 'APPROVED'
and nvl(req_header.closed_code,'NO') <> 'FINALLY CLOSED'
and NVL(req_header.cancel_flag, 'N') <> 'Y'
and nvl(req_header.federal_flag,'N') = 'N'
AND req_header.requisition_header_id = req_line.requisition_header_id
and nvl(req_line.cancel_flag,'N') <> 'Y'
AND Nvl(req_line.modified_by_agent_flag,'N') <> 'Y'
AND Nvl(req_line.closed_code,'NO') NOT IN ('CLOSED','FINALLY CLOSED')
AND req_line.requisition_line_id = req_dist.requisition_line_id(+)
and req_dist.task_id  = pa_task.task_id (+)
and req_dist.project_id = 	pa_task.project_id (+)
and pa_task.project_id = pa_project.project_id(+)
AND NVL(req_header.authorization_status,'INCOMPLETE') = status_lkup.lookup_code
AND status_lkup.lookup_type = 'AUTHORIZATION STATUS'
AND status_lkup.LANGUAGE IN ('US')
 
and req_line.vendor_id = ap_supplier.vendor_id(+)
and req_line.category_id = req_mtl_cat.category_id(+) --req line category to req mtl category
and req_line.item_id = mtl_sys_item.inventory_item_id(+) --req line item to mtl system inv item
and req_line.destination_organization_id = mtl_sys_item.organization_id(+)
AND po_v.REQ_DISTRIBUTION_ID(+) = req_dist.distribution_id
AND      OOD.ORGANIZATION_ID(+) = req_line.SOURCE_ORGANIZATION_ID
AND      PSP1.ORG_ID(+) = OOD.OPERATING_UNIT
/*AND (nvl(req_header.CONTRACTOR_REQUISITION_FLAG,'N')='N'
or (req_header.AUTHORIZATION_STATUS='APPROVED'  and
req_header.CONTRACTOR_STATUS='ASSIGNED'))*/
AND prl_reserved_status.requisition_line_id(+) =  req_line.requisition_line_id  
AND req_header.rowid = dfv_h."'PO_REQ_H_ROW_ID'" (+)  
AND req_line.rowid = dfv_l."'PO_REQ_L_ROW_ID'" (+)  ) pivot(Max(status) AS status,
                                     Max(operating_unit) AS operating_unit,
                                     Max(buyer_name) AS buyer_name,
                                     Max(line_type) AS line_type,
                                     Max(line_status) AS line_status,
                                     Max(requisition_type) AS requisition_type,
                                     Max(deliver_to_location) AS deliver_to_location,
                                     Max(ship_to_location) AS ship_to_location,
                                     Max(destination_type) AS destination_type,
                                     Max(expenditure_org) AS expenditure_org,
                                     Max(reservation_status) AS reservation_status,
                                     Max(contractor_requisition_flag) AS contractor_requisition_flag,
                                     Max(destination_organization) AS destination_organization
                                     FOR status_language IN ('US' "US")) 
) x
where
2=2