ECC Procurement, Requisitions, SQL1

Description
Columns: Ecc Spec Id, 'Po Req H Row Id', 'Po Req H Context', 'Po Req H Concatenated Segments', 'Po Req L Row Id', 'Po Req L Context', 'Po Req L Concatenated Segments', 'Po Req D Row Id', 'Po Req D Context Value', 'Po Req D Concatenated Segments' ...
Imported from ECC
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.*, DFV_D.*,
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_end_date IS NULL )
OR ( emp.effective_end_date =
  (SELECT Max(c.effective_end_date)
  FROM   per_all_people_f c
  WHERE  emp.person_id = c.person_id
) ) )),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_end_date IS NULL )
  OR ( emp.effective_end_date =
    (SELECT Max(c.effective_end_date)
    FROM   per_all_people_f c
    WHERE  emp.person_id = c.person_id
  ) ) )
  ) 	preparer	,
por_view_reqs_pkg.get_req_total(req_header.requisition_header_id) 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) )), 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 req_line.urgent_flag = '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) ) )
    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_end_date IS NULL )
	OR ( emp.effective_end_date =
		(SELECT Max(c.effective_end_date)
		FROM   per_all_people_f c
		WHERE  emp.person_id = c.person_id))
)) 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_end_date IS NULL )
        OR ( emp.effective_end_date =
                  (SELECT Max(c.effective_end_date)
                  FROM   per_all_people_f c
                  WHERE  emp.person_id = c.person_id  )
			)
	)
) 	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	,
req_line.urgent_flag urgent_flag,
req_line.last_update_date req_last_upd_date,
Decode(req_header.type_lookup_code,
                  'INTERNAL',TO_CHAR(OE_ORDER_IMPORT_INTEROP_PUB.Get_Order_Number(PSP.order_source_id,req_line.requisition_header_id,req_line.requisition_line_id)),
                    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,
  DECODE (po_pcc_requisitions_util_pvt.is_fully_reserved('REQUISITION','LINE',req_line.requisition_line_id),'Y',
	           (SELECT meaning
               FROM fnd_lookup_values POLC
               WHERE POLC.lookup_type = 'DOCUMENT STATE'
               AND POLC.lookup_code = 'RESERVED'
               AND LANGUAGE = status_lkup.language),
			   (SELECT meaning
               FROM fnd_lookup_values POLC
               WHERE POLC.lookup_type = 'DOCUMENT STATE'
               AND POLC.lookup_code = 'NOT RESERVED'
               AND LANGUAGE = status_lkup.language)) 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
 
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 PSP,
 (select 'PO_REQ_H_ROW_ID','PO_REQ_H_CONTEXT','PO_REQ_H_CONCATENATED_SEGMENTS' from dual where 1=2  union select ROWIDTOCHAR(ROW_ID),CONTEXT,CONCATENATED_SEGMENTS from PO_REQUISITION_HEADERS_ALL_DFV) DFV_H, (select 'PO_REQ_L_ROW_ID','PO_REQ_L_CONTEXT','PO_REQ_L_CONCATENATED_SEGMENTS' from dual where 1=2  union select ROWIDTOCHAR(ROW_ID),CONTEXT,CONCATENATED_SEGMENTS from PO_REQUISITION_LINES_ALL3_DFV) DFV_L, (select 'PO_REQ_D_ROW_ID','PO_REQ_D_CONTEXT_VALUE','PO_REQ_D_CONCATENATED_SEGMENTS' from dual where 1=2  union select ROWIDTOCHAR(ROW_ID),CONTEXT_VALUE,CONCATENATED_SEGMENTS from PO_REQ_DISTRIBUTIONS_ALL1_DFV) DFV_D
WHERE
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      PSP.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 req_header.rowid = dfv_h."'PO_REQ_H_ROW_ID'" (+)
AND req_line.rowid = dfv_l."'PO_REQ_L_ROW_ID'" (+)
AND req_dist.rowid = dfv_d."'PO_REQ_D_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
                                     FOR status_language IN ('US' "US"))
Download
Blitz Report In Action
Blitz Report™

Blitz Report™ provides multiple benefits: