SELECT (SELECT DISTINCT organization_name
FROM org_organization_definitions
WHERE organization_id = pa.carrying_out_organization_id) org_name
,(SELECT DISTINCT organization_code
FROM org_organization_definitions
WHERE organization_id = pa.carrying_out_organization_id) org_code
,pa.project_id
,pa.org_id
,pa.NAME
,pa.long_name
,pa.segment1
,pa.project_status_code
,pab.burdened_cost budget_cost
,pa.created_by
,pa.project_type
,pa.start_date
,pa.completion_date
,pa.closed_date
,pa.carrying_out_organization_id
,NVL((SELECT DISTINCT ppf.first_name || ' ' || ppf.last_name
FROM pa_project_players p
,per_all_people_f ppf
WHERE ppf.person_id = p.person_id
AND project_role_type = 'PROJECT MANAGER'
AND p.project_id = pa.project_id), 'Not Defined') key_member
,pt.task_id
,pt.task_number
,pt.attribute1 task_status
,pt.task_name
,pt.description
,pab.budget_version_id
,trunc(pab.cur_base_date) approval_date
,(SELECT SUM(burdened_cost)
FROM pafv_budget_lines
WHERE task_id = pt.task_id
AND budget_version_id = pab.budget_version_id
GROUP BY task_id) task_budget
,pt.top_task_id
,pt.wbs_level
,pt.start_date
,pt.completion_date
,pod.DESTINATION_TYPE_CODE
,
-- pod.PO_DISTRIBUTION_ID,
(SELECT item_id
FROM po_lines_all
WHERE po_line_id = pod.po_line_id) item
,(SELECT description
FROM mtl_system_items_kfv k
WHERE inventory_item_id =
(SELECT item_id
FROM po_lines_all
WHERE po_line_id = pod.po_line_id)
AND organization_id = pa.carrying_out_organization_id) item_desc
,(SELECT concatenated_segments
FROM mtl_system_items_kfv k
WHERE inventory_item_id =
(SELECT item_id
FROM po_lines_all
WHERE po_line_id = pod.po_line_id)
AND organization_id = pa.carrying_out_organization_id) item_code
,(SELECT pha.segment1
FROM po_headers_all pha
WHERE pha.po_header_id = pod.po_header_id) po_num
,(SELECT pv.vendor_name
FROM po_headers_all pha
,po_vendors pv
WHERE pv.vendor_id = pha.vendor_id
AND pha.po_header_id = pod.po_header_id) vendor_name
,(SELECT pha.currency_code
FROM po_headers_all pha
WHERE pha.po_header_id = pod.po_header_id) Currency_Code
,(SELECT trunc(pha.creation_date)
FROM po_headers_all pha
WHERE pha.po_header_id = pod.po_header_id) po_date
,(SELECT quantity
FROM po_lines_all
WHERE po_line_id = pod.po_line_id) PO_QTY
,(SELECT unit_price
FROM po_lines_all
WHERE po_line_id = pod.po_line_id) PO_unit_price
,(SELECT (pl.unit_price * pl.quantity * nvl(ph.rate, 1))
FROM po_lines_all pl
,po_headers_all ph
WHERE ph.po_header_id = pl.po_header_id
AND pl.po_line_id = pod.po_line_id) PO_commit_cost
,(SELECT DISTINCT receipt_num
FROM rcv_shipment_headers rsh
,rcv_transactions rcv
,po_lines_all pla
WHERE rsh.shipment_header_id = rcv.shipment_header_id
AND rcv.po_line_id = pla.po_line_id
AND PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
AND transaction_id = rcv1.transaction_id) rec_num
,(SELECT DISTINCT trunc(transaction_date)
FROM rcv_transactions rcv
WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
AND transaction_type = 'DELIVER'
AND rownum = 1) Rec_Date
,(SELECT SUM((decode(transaction_type, 'DELIVER', quantity,
'RETURN TO VENDOR', (quantity * (-1)))))
FROM rcv_transactions rcv
WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
AND shipment_line_id = rcv1.shipment_line_id
AND transaction_type IN ('DELIVER', 'RETURN TO VENDOR')) rec_qty
,(SELECT DISTINCT po_unit_price
FROM rcv_transactions rcv
WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
AND transaction_type = 'DELIVER') Rec_Price
,((SELECT SUM((decode(transaction_type, 'DELIVER', quantity,
'RETURN TO VENDOR', (quantity * (-1)))))
FROM rcv_transactions rcv
WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
AND shipment_line_id = rcv1.shipment_line_id
AND transaction_type IN ('DELIVER', 'RETURN TO VENDOR')) *
(SELECT DISTINCT po_unit_price
FROM rcv_transactions rcv
WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
AND transaction_type = 'DELIVER')) Rec_Val
,NULL transfered_project_from_other
,NULL transfered_qty_from_other
,NULL transfered_date_from_other
,NULL transfered_cost_from_other
,NULL transfer_project_to_other
,NULL transfer_qty_to_other
,NULL transfer_cost_to_other
,NULL transfer_date_to_other
,NULL issued_qty
,NULL issued_date
,NULL issued_cost
,NULL issued_ref_no
,DECODE(pod.DESTINATION_TYPE_CODE, 'EXPENSE',
((SELECT SUM((decode(transaction_type, 'DELIVER', quantity,
'RETURN TO VENDOR', (quantity * (-1)))))
FROM rcv_transactions rcv
WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
AND transaction_type IN ('DELIVER', 'RETURN TO VENDOR')) *
(SELECT DISTINCT po_unit_price
FROM rcv_transactions rcv
WHERE PO_DISTRIBUTION_ID = pod.PO_DISTRIBUTION_ID
AND transaction_type = 'DELIVER'))) expense_loaded_to_project
,NULL to_project_id
,NULL transaction_quantity
,NULL transaction_type
,NULL subinventory_code
,NULL locator_id
,NULL location
,(SELECT DISTINCT uom_code
FROM mtl_units_of_measure_tl uo
,po_lines_all pl
WHERE pl.UNIT_MEAS_LOOKUP_CODE = uo.unit_of_measure
AND po_line_id = pod.po_line_id) transaction_uom
,(SELECT pha.creation_date
FROM po_headers_all pha
WHERE pha.po_header_id = pod.po_header_id) trans_date
,NULL per_unit_cost
FROM pa_projects_all pa
,pa_tasks pt
,pa_budget_versions_draft_v pab
,pa_budget_types ty
,po_distributions_all pod
,rcv_transactions rcv1
WHERE pa.project_id = pt.project_id
AND pa.project_id = pab.project_id
AND ty.budget_type_code = pab.budget_type_code
AND pod.task_id(+) = pt.task_id
AND pod.task_id = rcv1.task_id(+)
AND pod.po_distribution_id = rcv1.po_distribution_id(+)
AND pa.org_id = :p_org_id
AND pa.segment1 = :p_from_segment
/* AND pa.project_status_code = NVL (:p_status, pa.project_status_code)
AND pa.segment1 BETWEEN NVL (:p_from_segment, pa.segment1)
AND NVL (:p_to_segment, pa.segment1)
AND TRUNC (pa.start_date) BETWEEN NVL
(TRUNC (TO_DATE (:p_from_start_date,
'DD-MON-RRRR'
)
),
TRUNC (pa.start_date)
)
AND NVL (TRUNC (TO_DATE (:p_to_start_date,
'DD-MON-RRRR'
)
),
TRUNC (pa.start_date)
)
AND pab.burdened_cost > = NVL (:p_cost, pab.burdened_cost) */
UNION
SELECT (SELECT DISTINCT organization_name
FROM org_organization_definitions
WHERE organization_id = pa.carrying_out_organization_id) org_name
,(SELECT DISTINCT organization_code
FROM org_organization_definitions
WHERE organization_id = pa.carrying_out_organization_id) org_code
,pa.project_id
,pa.org_id
,pa.NAME
,pa.long_name
,pa.segment1
,pa.project_status_code
,pab.burdened_cost budget_cost
,pa.created_by
,pa.project_type
,pa.start_date
,pa.completion_date
,pa.closed_date
,pa.carrying_out_organization_id
,NVL((SELECT DISTINCT ppf.first_name || ' ' || ppf.last_name
FROM pa_project_players p
,per_all_people_f ppf
WHERE ppf.person_id = p.person_id
AND project_role_type = 'PROJECT MANAGER'
AND p.project_id = pa.project_id), 'Not Defined') key_member
,pt.task_id
,pt.task_number
,pt.attribute1 task_status
,pt.task_name
,pt.description
,pab.budget_version_id
,trunc(pab.cur_base_date) approval_date
,(SELECT SUM(burdened_cost)
FROM pafv_budget_lines
WHERE task_id = pt.task_id
AND budget_version_id = pab.budget_version_id
GROUP BY task_id) task_budget
,pt.top_task_id
,pt.wbs_level
,pt.start_date
,pt.completion_date
,NULL destination_type_code
,mmt.inventory_item_id item
,(SELECT description
FROM mtl_system_items_kfv k
WHERE inventory_item_id = mmt.inventory_item_id
AND organization_id = pa.carrying_out_organization_id) item_desc
,(SELECT concatenated_segments
FROM mtl_system_items_kfv k
WHERE inventory_item_id = mmt.inventory_item_id
AND organization_id = pa.carrying_out_organization_id) item_code
,NULL po_num
,NULL vendor_name
,NULL Currency_Code
,NULL po_date
,NULL po_qty
,NULL po_unit_price
,NULL po_commit_cost
,NULL rec_num
,NULL rec_date
,NULL rec_qty
,NULL rec_price
,NULL rec_val
,CASE
WHEN mmt.transaction_quantity > 0 THEN
decode(mmt.transaction_type_id, 66,
(SELECT DISTINCT segment1
FROM pa_projects_all
WHERE project_id = mmt.to_project_id), 67,
(SELECT DISTINCT segment1
FROM pa_projects_all
WHERE project_id = mmt.to_project_id), 68,
(SELECT DISTINCT segment1
FROM pa_projects_all
WHERE project_id = mmt.to_project_id))
END AS transfered_project_from_other
,CASE
WHEN mmt.transaction_quantity > 0 THEN
DECODE(mmt.transaction_type_id, 66, mmt.transaction_quantity, 67,
mmt.transaction_quantity, 68, mmt.transaction_quantity)
END AS transfered_QTY_from_other
,CASE
WHEN mmt.transaction_quantity > 0 THEN
DECODE(mmt.transaction_type_id, 66, TRUNC(mmt.transaction_date), 67,
TRUNC(mmt.transaction_date), 68, TRUNC(mmt.transaction_date))
END AS transfered_DATE_from_other
,CASE
WHEN mmt.transaction_quantity > 0 THEN
DECODE(mmt.transaction_type_id, 66,
(mmt.transaction_quantity * mmt.actual_cost), 67,
(mmt.transaction_quantity * mmt.actual_cost), 68,
(mmt.transaction_quantity * mmt.actual_cost))
END AS transfered_cost_from_other
,CASE
WHEN mmt.transaction_quantity < 0 THEN
decode(mmt.transaction_type_id, 66,
(SELECT DISTINCT segment1
FROM pa_projects_all
WHERE project_id = mmt.to_project_id), 67,
(SELECT DISTINCT segment1
FROM pa_projects_all
WHERE project_id = mmt.to_project_id), 68,
(SELECT DISTINCT segment1
FROM pa_projects_all
WHERE project_id = mmt.to_project_id))
END AS transfer_project_to_other
,CASE
WHEN mmt.transaction_quantity < 0 THEN
DECODE(mmt.transaction_type_id, 66, mmt.transaction_quantity, 67,
mmt.transaction_quantity, 68, mmt.transaction_quantity)
END AS transfer_qty_to_other
,CASE
WHEN mmt.transaction_quantity < 0 THEN
DECODE(mmt.transaction_type_id, 66,
(mmt.transaction_quantity * mmt.actual_cost), 67,
(mmt.transaction_quantity * mmt.actual_cost), 68,
(mmt.transaction_quantity * mmt.actual_cost))
END AS transfer_cost_to_other
,CASE
WHEN mmt.transaction_quantity < 0 THEN
DECODE(mmt.transaction_type_id, 66, TRUNC(mmt.transaction_date), 67,
TRUNC(mmt.transaction_date), 68, TRUNC(mmt.transaction_date))
END AS transfer_DATE_to_other
,DECODE(mmt.transaction_type_id, 120, mmt.transaction_quantity, 35,
mmt.transaction_quantity, 63, mmt.transaction_quantity) issued_qty
,DECODE(mmt.transaction_type_id, 120, TRUNC(mmt.transaction_date), 35,
TRUNC(mmt.transaction_date), 63, TRUNC(mmt.transaction_date)) issued_date
,DECODE(mmt.transaction_type_id, 120,
(mmt.transaction_quantity * mmt.actual_cost * (-1)), 35,
(mmt.transaction_quantity * mmt.actual_cost * (-1)), 63,
(mmt.transaction_quantity * mmt.actual_cost * (-1))) issued_cost
,DECODE(mmt.transaction_type_id, 120,
(SELECT DISTINCT request_number
FROM mtl_txn_request_headers
WHERE header_id = mmt.transaction_source_id), 63,
(SELECT DISTINCT request_number
FROM mtl_txn_request_headers
WHERE header_id = mmt.transaction_source_id), 35,
(SELECT DISTINCT wip_entity_name
FROM wip_entities
WHERE wip_entity_id = mmt.transaction_source_id)) Issued_Ref_No
,NULL expense_loaded_to_project
,mmt.to_project_id
,mmt.transaction_quantity
,(SELECT description
FROM mtl_transaction_types
WHERE transaction_type_id = mmt.transaction_type_id
AND mmt.transaction_action_id = transaction_action_id
AND transaction_source_type_id = mmt.transaction_source_type_id) transaction_type
,mmt.subinventory_code
,mmt.locator_id
,(SELECT segment1 || '.' || segment2 || '.' || segment3
FROM mtl_item_locations
WHERE inventory_location_id = mmt.locator_id
AND organization_id = pa.carrying_out_organization_id) LOCATION
,mmt.transaction_uom
,mmt.transaction_date trans_date
,ROUND(NVL(mmt.transaction_cost, mmt.actual_cost), 2) per_unit_COST
FROM mtl_material_transactions mmt
,pa_projects_all pa
,pa_tasks pt
,po_lines_all pll
,pa_budget_versions_draft_v pab
,mtl_txn_request_lines mtxn
,pa_budget_types ty
WHERE mmt.transaction_set_id = mtxn.transaction_header_id(+)
AND pa.project_id = mmt.project_id(+)
AND pt.project_id = pa.project_id
AND pa.project_id = pab.project_id
AND ty.budget_type_code = pab.budget_type_code
AND pt.task_id = mmt.task_id
AND mmt.transaction_source_id = pll.po_header_id(+)
AND ty.plan_type = 'BUDGET'
AND mmt.inventory_item_id = pll.item_id(+)
AND mmt.transaction_type_id NOT IN (18, 36)
AND pa.org_id = :p_org_id
AND pa.segment1 = :p_from_segment
/* AND pa.project_status_code = NVL (:p_status, pa.project_status_code)
AND pa.segment1 BETWEEN NVL (:p_from_segment, pa.segment1)
AND NVL (:p_to_segment, pa.segment1)
AND TRUNC (pa.start_date) BETWEEN NVL
(TRUNC (TO_DATE (:p_from_start_date,
'DD-MON-RRRR'
)
),
TRUNC (pa.start_date)
)
AND NVL (TRUNC (TO_DATE (:p_to_start_date,
'DD-MON-RRRR'
)
),
TRUNC (pa.start_date)
)
AND pab.burdened_cost > = NVL (:p_cost, pab.burdened_cost)*/
ORDER BY item
,trans_date