Various Purchasing SQLs in Oracle Apps

Requisition Lines linked to a Project

 SELECT   prha.segment1 req
       , prha.wf_item_key
       , prla.line_num
       , prla.unit_price
       , prla.quantity
       , prha.creation_date
       , prha.authorization_status
       , pap.segment1
       , pat.task_number
       , haout.NAME exp_org
       , prla.suggested_vendor_name supplier
       , prla.suggested_vendor_location site
       , gcc.segment2 ibs_service
       , gcc.segment3 ibs_account
       , gcc.segment4 ibs_centre
       , fu.description created_by
    FROM po.po_requisition_lines_all prla
       , po.po_requisition_headers_all prha
       , po.po_req_distributions_all prda
       , gl.gl_code_combinations gcc
       , apps.pa_tasks pat
       , apps.pa_projects_all pap
       , hr.hr_all_organization_units_tl haout
       , applsys.fnd_user fu
   WHERE prha.requisition_header_id = prla.requisition_header_id
     AND prla.requisition_line_id = prda.requisition_line_id
     AND fu.user_id = prha.created_by
     AND gcc.code_combination_id = prda.code_combination_id
     AND pap.project_id = prda.project_id
     AND pap.org_id = prda.org_id
     AND pat.task_id = prda.task_id
     AND haout.organization_id = prda.expenditure_organization_id
ORDER BY prha.creation_date
       , prha.segment1
       , prla.line_num DESC;

Documents with a header and no lines

SELECT pha.segment1 po_number
  FROM po.po_headers_all pha
 WHERE pha.po_header_id NOT IN(SELECT pha.po_header_id
                                 FROM po.po_lines_all pla
                                WHERE pha.po_header_id = pla.po_header_id)
   AND pha.type_lookup_code = 'QUOTATION'

  REQ HEADERS WITH ZERO LINES

 
SELECT prha.segment1
     , prha.creation_date
     , prha.authorization_status
     , prha.type_lookup_code
     , (SELECT COUNT(*)
          FROM po.po_requisition_lines_all prla
         WHERE prla.requisition_header_id = prha.requisition_header_id)
                                                                      line_ct
     , fu.description
     , fu.user_name
  FROM po.po_requisition_headers_all prha
     , po.po_requisition_lines_all prla
     , applsys.fnd_user fu
 WHERE prha.created_by = fu.user_id
   AND prha.requisition_header_id = prla.requisition_header_id(+)
   AND (SELECT COUNT(*)
          FROM po.po_requisition_lines_all prla
         WHERE prla.requisition_header_id = prha.requisition_header_id) = 0
   AND prha.authorization_status NOT IN('CANCELLED', 'SYSTEM_SAVED');

Catalogue vs. Non Catalogue

SELECT   TO_CHAR(prha.creation_date, 'RRRR-MM') the_month
       , COUNT(DISTINCT prha.requisition_header_id) req_count
       , COUNT(prla.requisition_line_id) line_count
       , SUM(prla.unit_price * prla.quantity) total_value
       , CASE
            WHEN prla.catalog_type IN('CATALOG', 'EXTERNAL')
               THEN 'CATALOG'
            WHEN prla.catalog_type = 'NONCATALOG'
             OR prla.catalog_type IS NULL
               THEN 'NON_CATALOG'
            ELSE 'OTHER'
         END line_type
    FROM po.po_requisition_lines_all prla
       , po.po_requisition_headers_all prha
   WHERE prha.requisition_header_id = prla.requisition_header_id
     AND prha.authorization_status = 'APPROVED'
     AND prha.creation_date >= '01-JAN-2010'
GROUP BY CASE
            WHEN prla.catalog_type IN('CATALOG', 'EXTERNAL') THEN 'CATALOG'
            WHEN prla.catalog_type = 'NONCATALOG' OR prla.catalog_type IS NULL THEN 'NON_CATALOG'
            ELSE 'OTHER'
         END
       , TO_CHAR(prha.creation_date, 'RRRR-MM')
       ORDER BY 1,3,2;

POs linked to multiple requisitions

SELECT   pha.segment1 po
       , pha.creation_date
       , COUNT(DISTINCT prha.preparer_id) ct
    FROM po.po_requisition_headers_all prha
       , po.po_requisition_lines_all prla
       , po.po_line_locations_all plla
       , po.po_lines_all pla
       , po.po_headers_all pha
   WHERE prha.requisition_header_id = prla.requisition_header_id
     AND plla.line_location_id = prla.line_location_id
     AND plla.po_header_id = pha.po_header_id
     AND pla.po_line_id = plla.po_line_id
     AND pha.creation_date >= '01-APR-2009'
  HAVING COUNT(DISTINCT prha.preparer_id) > 1
GROUP BY pha.segment1
       , pha.creation_date;