Query for Oracle Projects and Budget Details

/* Formatted on 10/3/2016 8:21:54 PM (QP5 v5.114.809.3010) */
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
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
ORDER BY   item, trans_date