Query for Oracle Projects and Budget Details

 Query to Find Project Details , PO/Receipts/Move order/Consumption


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