Query: Oracle Employee Supervisor Hierarchy

This Query is about linking Employee record to their immediate Supervisor and their Supervisor's. This come handy mostly in the case of Workflow Approval process where we have to see who this notification should be going for approval.

SELECT LEVEL, a.*
FROM (SELECT a.full_name employee_name,
supervisor_id,
c.full_name supervisor_name
FROM per_people_f a, per_assignments_f b, per_people_f c
WHERE 1 = 1
AND a.effective_end_date > SYSDATE
AND c.effective_end_date > SYSDATE
AND a.person_id = b.person_id
AND b.effective_end_date > SYSDATE
AND c.person_id = b.supervisor_id) a
START WITH employee_name LIKE '%' || :employee_name || '%'
CONNECT BY PRIOR supervisor_name = employee_name
ORDER BY 1

Query: Link Between PO and Requisition

This Query is a link between PO Requisition and Purchase Order. It will the list of all those PO's which are created via Requisition.

 SELECT prh.segment1 pr_number,  
  ph.segment1 po_number,  
  ph.comments po_comments,  
  ph.ship_to_location_id,  
  ph.bill_to_location_id,  
  ph.terms_id,  
  ph.currency_code,  
  ph.revision_num,  
  he.full_name buyer,  
  ph.type_lookup_code po_document_type,  
  prh.description pr_description,  
  prh.authorization_status pr_status,  
  prh.type_lookup_code,  
  prl.line_num req_line_num,  
  pl.line_num,  
  pll.shipment_num,  
  msi.segment1 item_name,  
  mc.segment1 || '.' || mc.segment2 category_code,  
  prl.destination_context,  
  prl.suggested_vendor_name,  
  oodt.organization_code receving_org,  
  prl.need_by_date,  
  gcct.segment1  
  || '.'  
  || gcct.segment2  
  || '.'  
  || gcct.segment3  
  || '.'  
  || gcct.segment4  
  || '.'  
  || gcct.segment5  
  || '.'  
  || gcct.segment6  
  || '.'  
  || gcct.segment7  
  || '.'  
  || gcct.segment8 gl_code_combination,  
  prl.quantity req_qty,  
  prl.unit_price,  
  pl.unit_price,  
  pl.list_price_per_unit,  
  pl.unit_meas_lookup_code uom,  
  pd.quantity_ordered po_qty,  
  pd.quantity_delivered,  
  pd.quantity_billed,  
  pd.quantity_cancelled,  
  pd.amount_billed,  
  pd.amount_delivered,  
  pd.amount_cancelled,  
  pll.taxable_flag,  
  ph.closed_code po_status,  
  pl.closed_code line_status,  
  pll.closed_code line_location_status,  
  prl.destination_context  
  FROM po_requisition_headers prh,  
  po_requisition_lines prl,  
  po_req_distributions prd,  
  gl_code_combinations gcct,  
  po_distributions pd,  
  po_line_locations pll,  
  po_headers ph,  
  po_lines pl,  
  mtl_system_items_b msi,  
  org_organization_definitions oodt,  
  mtl_categories mc,  
  hr_employees he  
  WHERE 1 = 1  
  AND prh.requisition_header_id = prl.requisition_header_id  
  AND prd.requisition_line_id = prl.requisition_line_id  
  AND pd.req_distribution_id(+) = prd.distribution_id  
  AND ph.po_header_id = pl.po_header_id  
  AND pl.po_line_id = pll.po_line_id  
  AND pl.po_header_id = pll.po_header_id  
  AND pd.po_header_id = ph.po_header_id  
  AND pd.po_line_id = pl.po_line_id  
  AND pd.line_location_id = pll.line_location_id  
  AND msi.inventory_item_id(+) = prl.item_id  
  AND msi.organization_id(+) = prl.destination_organization_id  
  AND oodt.organization_id = prl.destination_organization_id  
  AND gcct.code_combination_id = prd.code_combination_id  
  AND prl.category_id = mc.category_id  
  AND prh.segment1 = NVL (:requisition_name, prh.segment1)  
  AND he.employee_id = ph.agent_id  
  AND ph.segment1 = NVL (:po_number, ph.segment1)  
  AND prh.type_lookup_code ='PURCHASE'  
  AND ph.authorization_status =  
  DECODE (:po_authorization_status,  
  'A', 'APPROVED',  
  'R', 'REQUIRES REAPPROVAL',  
  'P', 'IN PROCESS',  
  'I', 'INCOMPLETE',  
  'X', 'REJECTED',  
  ph.authorization_status  
  )  
  AND prh.authorization_status =  
  DECODE (:pr_status,  
  'A', 'APPROVED',  
  'P', 'IN PROCESS',  
  'I', 'INCOMPLETE',  
  prh.authorization_status  
  )  
  AND prl.destination_context =  
  DECODE (:req_line_type,  
  'I', 'INVENTORY',  
  'E', 'EXPENSE',  
  prl.destination_context  
  )  
  AND NVL (ph.closed_code, 'OPEN') =  
  DECODE (:po_status,  
  'O', 'OPEN',  
  'C', 'CLOSED',  
  'F', 'FINALLY CLOSED',  
  NVL (ph.closed_code, 'OPEN')  
  )  
  AND NVL (pl.closed_code, 'OPEN') =  
  DECODE (:line_status,  
  'O', 'OPEN',  
  'C', 'CLOSED',  
  'F', 'FINALLY CLOSED',  
  NVL (pl.closed_code, 'OPEN')  
  )  
  AND NVL (pll.closed_code, 'OPEN') =  
  DECODE (:line_location_status,  
  'O', 'OPEN',  
  'C', 'CLOSED',  
  'F', 'FINALLY CLOSED',  
  NVL (pll.closed_code, 'OPEN')  
  )  
  AND prh.creation_date > SYSDATE - NVL (:months, 12) * 30  
 ORDER BY prh.segment1, prl.line_num, pl.line_num, pll.shipment_num