Employee Supervisor based on Position Hierarchy

select   psv.position_structure_id,
         pps.name hierarchy_name,
         curr_person.person_id curr_person_id,
         curr_person.employee_number curr_colleague_number,
           curr_person.full_name as curr_colleague_name,
           str.subordinate_position_id as curr_pos_id,
           pos.name as curr_pos_name,
           next_person.person_id supervisor_person_id,
           next_person.employee_number supervisor_collegue_num,
           next_person.full_name as supervisor_name,
           str.parent_position_id as supervisor_pos_id,
           next_pos.name as supervisor_pos_name
    FROM   per_all_people_f curr_person,--fnd_user cur_user,
           per_pos_structure_elements str,
           per_pos_structure_versions psv,
           per_all_positions pos,
           per_all_positions next_pos,
           per_all_assignments_f cur_assign,
           per_all_people_f next_person,
           per_position_structures pps,
           per_all_assignments_f next_assign
   WHERE   str.pos_structure_version_id = psv.pos_structure_version_id
           AND TRUNC (SYSDATE) BETWEEN psv.date_from
                                   AND  NVL (psv.date_to, SYSDATE)
           AND pos.position_id = str.subordinate_position_id
           AND next_pos.position_id = str.parent_position_id
           and cur_assign.position_id = pos.position_id
           and cur_assign.person_id = curr_person.person_id
           and trunc(sysdate) between curr_person.effective_start_date and curr_person.effective_end_date
           and trunc(sysdate) between cur_assign.effective_start_date and cur_assign.effective_end_date
           and trunc(sysdate) between next_person.effective_start_date and next_person.effective_end_date
           and trunc(sysdate) between next_assign.effective_start_date and next_assign.effective_end_date
           and next_assign.position_id  = next_pos.position_id
           and next_assign.person_id = next_person.person_id
           AND  psv.position_structure_id = pps.position_structure_id  

Purchase Order Status Report

CREATE OR REPLACE FORCE VIEW apps.xxaye_po_order_status_disc
AS   create or replace view XXAYE_PO_ORDER_STATUS_DISC
   as
   SELECT apps.xxaly_scm_general_pkg.get_operating_unit (pha.org_id)
                                                               operating_unit,
          pha.segment1 po_number, TRUNC (pha.creation_date) po_date,
          pll.promised_date, TO_CHAR (pll.promised_date, 'YYYY') promise_year,
          pha.authorization_status po_status, pv.vendor_name, xtm.item_code,
          xtm.description, xtm.CATEGORY category_seg1,
          xtm.sub_category cat_seg2,
          xtm.CATEGORY || '.' || xtm.sub_category CATEGORY,
          pll.quantity qty_ordered, pll.quantity_received qty_received,
          pll.quantity - pll.quantity_received bal_qty, NULL grn_num,
          NULL grn_date, NULL grn_qty,xxh.user_name created_by,null receiving_organization
     FROM apps.po_headers_all pha,
          apps.po_lines_all pla,
          apps.po_vendors pv,
          apps.po_line_locations_all pll,
          apps.xx_mtl_master_items_v xtm,
          apps.xxaly_hr_user_person_v xxh
    WHERE pha.po_header_id = pla.po_header_id
      AND pll.po_header_id(+) = pla.po_header_id
      AND pll.po_line_id(+) = pla.po_line_id
      AND pla.po_line_id NOT IN (SELECT NVL (rsl.po_line_id, 0)
                                   FROM apps.rcv_shipment_lines rsl
                                  WHERE pla.po_line_id = rsl.po_line_id)
      AND pv.vendor_id(+) = pha.vendor_id
      AND xtm.inventory_item_id(+) = pla.item_id
      AND pha.created_by = xxh.user_id      
   UNION
   SELECT apps.xxaly_scm_general_pkg.get_operating_unit (pha.org_id)
                                                               operating_unit,
          pha.segment1 po_number, TRUNC (pha.creation_date) po_date,
          pll.promised_date, TO_CHAR (pll.promised_date, 'YYYY') promise_year,
          pha.authorization_status po_status, pv.vendor_name, xtm.item_code,
          xtm.description, xtm.CATEGORY category_seg1,
          xtm.sub_category cat_seg2,
          xtm.CATEGORY || '.' || xtm.sub_category CATEGORY,
          pll.quantity qty_ordered, pll.quantity_received qty_received,
          pll.quantity - pll.quantity_received bal_qty,
          rsh.receipt_num grn_num, TRUNC (rt.transaction_date) grn_date,
          rsl.quantity_received grn_qty,xxh.user_name created_by,ood.organization_name receiving_organization
     FROM apps.po_headers_all pha,
          apps.po_lines_all pla,
          apps.rcv_transactions rt,
          apps.rcv_shipment_headers rsh,
          apps.rcv_shipment_lines rsl,
          apps.po_vendors pv,
          apps.po_line_locations_all pll,
          apps.xx_mtl_master_items_v xtm,
          apps.xxaly_hr_user_person_v xxh,
          apps.org_organization_definitions ood
    WHERE pha.po_header_id = pla.po_header_id
      AND pha.po_header_id = rt.po_header_id
      AND pla.po_line_id = rt.po_line_id
      AND rsh.shipment_header_id = rt.shipment_header_id
      AND rt.transaction_type = 'RECEIVE'
      AND pv.vendor_id = pha.vendor_id
      AND pll.po_header_id(+) = pla.po_header_id
      AND pll.po_line_id(+) = pla.po_line_id
      AND xtm.inventory_item_id(+) = pla.item_id
      AND rsh.shipment_header_id = rsl.shipment_header_id
      AND rsl.po_line_id = pla.po_line_id
      AND pha.created_by = xxh.user_id
      AND ood.organization_id = rt.organization_id ;
/

To Check for the available onhand For An Item in a Given Organization Using Inv_Quantity_Tree_Pub API

/*To Check for the available onhand For An Item in a Given Organization Using Inv_Quantity_Tree_Pub API
Inv_Quantity_Tree_Pub API can be used for querying the available Onhand in a given subinventory or organization.
The output of the API inv_quantity_tree_pub.query_quantities will show the Total Available Onhand , Reservations , Suggestions and the Actual Onhand that can be Transacted.
*/
-- R12 - INV - Sample Script to Get Onhand Using INV_Quantity_Tree_PUB API:
DECLARE
l_api_return_status VARCHAR2 (1);
l_qty_oh NUMBER;
l_qty_res_oh NUMBER;
l_qty_res NUMBER;
l_qty_sug NUMBER;
l_qty_att NUMBER;
l_qty_atr NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
v_item VARCHAR2 (250) ;
v_org VARCHAR2 (10) ;
Cursor c_item_info is
SELECT concatenated_segments item, msi.inventory_item_id,msi.organization_id, mp.organization_code
FROM mtl_system_items_kfv msi, mtl_parameters mp
WHERE concatenated_segments = :v_item
AND msi.organization_id = mp.organization_id
AND mp.organization_code = :v_org;
BEGIN
inv_quantity_tree_grp.clear_quantity_cache;
DBMS_OUTPUT.put_line ('Transaction Mode');
For i in c_item_info
LOOP
DBMS_OUTPUT.put_line ('Extracting the Onhand For the Item ===========> ' i.item);
DBMS_OUTPUT.put_line ('Extracting the Onhand For the Organization ======> ' i.organization_code);
apps.inv_quantity_tree_pub.query_quantities
(p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => l_api_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_organization_id => i.organization_id,
p_inventory_item_id => i.inventory_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => NULL,
p_locator_id => NULL,
x_qoh => l_qty_oh,
x_rqoh => l_qty_res_oh,
x_qr => l_qty_res,
x_qs => l_qty_sug,
x_att => l_qty_att,
x_atr => l_qty_atr);
DBMS_OUTPUT.put_line ('Quantity on hand ======================> ' TO_CHAR (l_qty_oh));
DBMS_OUTPUT.put_line ('Reservable quantity on hand ===============> ' TO_CHAR (l_qty_res_oh));
DBMS_OUTPUT.put_line ('Quantity reserved =====================> ' TO_CHAR (l_qty_res));
DBMS_OUTPUT.put_line ('Quantity suggested ====================> ' TO_CHAR (l_qty_sug));
DBMS_OUTPUT.put_line ('Quantity Available To Transact ==============> ' TO_CHAR (l_qty_att));
DBMS_OUTPUT.put_line ('Quantity Available To Reserve ==============> ' TO_CHAR (l_qty_atr));
END LOOP;
END;
/*
DBMS OUTPUT:
Transaction Mode
Extracting the Onhand For the Item =========> ELXV350ELL121MH12D^NIPPONSCC
Extracting the Onhand For the Organization ====> A66
Quantity on hand =======================> 3400
Reservable quantity on hand ===============> 3400
Quantity reserved =======================> 1000
Quantity suggested ======================> 0
Quantity Available To Transact==============> 2400
Quantity Available To Reserve ==============> 2400*/

Employee Person View for Reports

CREATE OR REPLACE VIEW xx_hr_user_person (user_id,
user_name,
person_id,
employee_number,
full_name,
email_address
)
AS
SELECT fu.user_id, fu.user_name, papf.person_id, papf.employee_number,
papf.full_name, papf.email_address
FROM per_all_people_f papf, fnd_user fu
WHERE papf.person_id = fu.employee_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date