Approvals – Approval Workflow Errors – Purchase Orders and Requisitions

Sections:

  1. All Active Errors – Via Notifications Table
  2. Purchase Orders With In Process / Pre-Approved With No Open Workflows
  3. In-Process / Pre-Approved POs With Workflow Errors
  4. In-Process / Pre-Approved Reqs With Workflow Errors

All Active Errors – Via Notifications Table

-- ##############################################################################
--      IDENTIFIES ALL ACTIVE ERRORS - VIA NOTIFICATIONS TABLE
-- ############################################################################*/

SELECT   pha.segment1
       , wi.item_type
       , wi.item_key
       , wi.begin_date
       , wi.parent_item_type
       , wi.parent_item_key
       , wn.subject
    FROM applsys.wf_items wi
    JOIN applsys.wf_items wi2        ON wi.parent_item_type = wi2.item_type
    JOIN applsys.wf_notifications wn ON SUBSTR(wn.CONTEXT, 1, INSTR(wn.CONTEXT, ':', INSTR(wn.CONTEXT, ':', 1) + 1) - 1) = wi.item_type || ':' || wi.item_key
    JOIN po.po_headers_all pha       ON wi.parent_item_key = pha.wf_item_key
                                    AND wi.parent_item_key = wi2.item_key
   WHERE wi2.end_date IS NULL
     AND wi.end_date IS NULL
     AND wi.item_type IN('POERROR', 'WFERROR')
ORDER BY 3 DESC;

Purchase Orders With In Process / Pre-Approved With No Open Workflows

-- ##############################################################################
--      PURCHASE ORDERS WITH IN PROCESS / PRE-APPROVED WITH NO OPEN WORKFLOWS 
-- ############################################################################*/

    SELECT DISTINCT 
           ph.segment1
         , ph.comments
         , pav.agent_name buyer
         , pv.vendor_name
         , pcr.ref_po_num
      FROM po.po_headers_all ph
      JOIN apps.po_agents_v pav      ON ph.agent_id =  pav.agent_id
      JOIN apps.po_vendors pv        ON ph.vendor_id = pv.vendor_id
 LEFT JOIN po.po_change_requests pcr ON ph.segment1 =  pcr.ref_po_num
     WHERE ph.authorization_status IN('IN PROCESS', 'PRE-APPROVED')
       AND NOT EXISTS(
               SELECT 'wf exists'
                 FROM applsys.wf_items wi
                WHERE wi.item_type = ph.wf_item_type
                  AND wi.item_key = ph.wf_item_key
                  AND wi.end_date IS NULL)
           ORDER BY 1;

In-Process / Pre-Approved POs With Workflow Errors

-- ##############################################################################
--      IN-PROCESS / PRE-APPROVED POS WITH WORKFLOW ERRORS
-- ############################################################################*/

  SELECT ac.NAME activity
       , ac.display_name "Activity Display Name"
       , ias.activity_result_code RESULT
       , ias.error_name
       , ias.error_message
       , ias.error_stack
       , ias.item_type
       , ias.begin_date
       , pha.wf_item_key "PO wf_item_key"
       , pha.wf_item_type "PO wf_item_type"
       , pha.segment1 "PO_NUM"
       , pav.agent_name buyer
       , pha.creation_date
       , pha.authorization_status
       , pha.revision_num
       , pha.comments "PO_DESCRIPTION"
       , papf.full_name hr_full_name
       , haout.NAME organization_name
       , hlat.location_code
       , wi.end_date
       , bus_gp.NAME bus_gp
    FROM apps.wf_item_activity_statuses ias
    JOIN apps.wf_process_activities pa          ON ias.process_activity =  pa.instance_id
    JOIN apps.wf_activities_vl ac               ON pa.activity_item_type = ac.item_type
    JOIN apps.wf_activities_vl ap               ON pa.process_item_type =  ap.item_type
    JOIN apps.wf_items i                        ON i.item_key =            ias.item_key
    JOIN apps.po_headers_all pha                ON pha.wf_item_key =       ias.item_key
    JOIN applsys.fnd_user us                    ON pha.created_by =        us.user_id
    JOIN hr.per_all_people_f papf               ON us.employee_id =        papf.person_id 
    JOIN hr.per_all_assignments_f paaf          ON papf.person_id =        paaf.person_id 
    JOIN hr.hr_all_organization_units_tl haout  ON paaf.organization_id =  haout.organization_id
    JOIN hr.hr_locations_all_tl hlat            ON paaf.location_id =      hlat.location_id
    JOIN applsys.wf_items wi                    ON wi.item_type =          pha.wf_item_type
    JOIN apps.po_agents_v pav                   ON pha.agent_id = pav.agent_id
    JOIN hr.hr_all_organization_units_tl bus_gp ON pha.org_id = bus_gp.organization_id
                                               AND pa.activity_name =      ac.NAME
                                               AND pa.process_name =       ap.NAME
                                               AND pa.process_version =    ap.VERSION
                                               AND wi.item_key =           pha.wf_item_key
                                               AND i.begin_date <          NVL(ac.end_date, i.begin_date + 1)
                                               AND SYSDATE BETWEEN         papf.effective_start_date AND papf.effective_end_date
                                               AND SYSDATE BETWEEN         paaf.effective_start_date AND paaf.effective_end_date
   WHERE ias.item_type = 'POAPPRV'
     AND ias.activity_status = 'ERROR'
     AND i.item_type = 'POAPPRV'
     AND paaf.primary_flag = 'Y'
     AND paaf.assignment_type = 'E'
     AND ias.error_stack IS NOT NULL 
     AND pha.authorization_status IN('IN PROCESS', 'PRE-APPROVED')
--     AND ac.display_name = 'Does Approver Have Authority?'
     AND pha.creation_date > '01-SEP-2011'
ORDER BY pha.creation_date DESC;

In-Process / Pre-Approved Reqs With Workflow Errors

-- ##############################################################################
--      IN-PROCESS / PRE-APPROVED REQS WITH WORKFLOW ERRORS
-- ############################################################################*/

SELECT   ac.NAME activity
       , ac.display_name "Activity Display Name"
       , ias.activity_result_code RESULT
       , ias.error_name
       , ias.error_message
       , ias.error_stack
       , ias.item_type
       , prha.segment1 || ' - ' || TRUNC(prha.creation_date) req_date
       , ias.begin_date
       , fu.description created_by
       , prha.wf_item_key "REQ wf_item_key"
       , prha.wf_item_type "REQ wf_item_type"
       , prha.creation_date
       , prha.authorization_status
       , prha.description
       , wi.end_date
    FROM apps.wf_item_activity_statuses ias
    JOIN apps.wf_process_activities pa        ON ias.process_activity =  pa.instance_id
    JOIN apps.wf_activities_vl ac             ON pa.activity_item_type = ac.item_type
    JOIN apps.wf_activities_vl ap             ON pa.process_item_type =  ap.item_type
    JOIN apps.wf_items i                      ON i.item_key =            ias.item_key
    JOIN apps.po_requisition_headers_all prha ON prha.wf_item_key =      ias.item_key
    JOIN applsys.wf_items wi                  ON wi.item_type =          prha.wf_item_type
    JOIN applsys.fnd_user fu                  ON prha.created_by =       fu.user_id
                                             AND pa.activity_name =      ac.NAME
                                             AND pa.process_name =       ap.NAME
                                             AND pa.process_version =    ap.VERSION
                                             AND i.begin_date >=         ac.begin_date
                                             AND i.begin_date <          NVL(ac.end_date, i.begin_date + 1)
                                             AND wi.item_key =           prha.wf_item_key
   WHERE ias.item_type = 'REQAPPRV'
     AND ias.error_stack IS NOT NULL
     AND ias.activity_status = 'ERROR'
     AND i.item_type = 'REQAPPRV'
     AND prha.authorization_status IN('IN PROCESS', 'PRE-APPROVED')
     AND prha.creation_date > '31-MAR-2009'
ORDER BY prha.creation_date DESC;

Approvals – Approval History – Purchase Orders and Requisitions

-- ##############################################################################
--     REQUISITION ACTION HISTORY
-- ##############################################################################

  SELECT prha.segment1 req
       , prha.authorization_status req_status
       , prha.creation_date req_created
       , pah.sequence_num seq
       , pah.action_date date_
       , pah.object_revision_num rev
       , pah.action_code
       , papf.full_name performed_by
       , pah.note
    FROM po.po_action_history pah
    JOIN po.po_requisition_headers_all prha ON object_id =       prha.requisition_header_id
    JOIN applsys.fnd_user fu                ON pah.employee_id = fu.employee_id
    JOIN hr.per_all_people_f papf           ON fu.employee_id =  papf.person_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
   WHERE pah.object_type_code = 'REQUISITION'
     AND prha.segment1 = '12345678'
ORDER BY prha.segment1
       , pah.sequence_num DESC;

-- ##############################################################################
--        PO APPROVAL HISTORY 
-- ##############################################################################

  SELECT pha.segment1 po
       , pha.authorization_status po_status
       , pha.creation_date po_created
       , pah.sequence_num seq
       , pah.action_date date_
       , pah.action_code
       , papf.full_name performed_by
       , pah.note
    FROM po.po_action_history pah 
    JOIN po.po_headers_all pha    ON object_id =       pha.po_header_id
    JOIN applsys.fnd_user fu      ON pah.employee_id = fu.employee_id
    JOIN hr.per_all_people_f papf ON fu.employee_id =  papf.person_id AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
   WHERE pah.object_type_code = 'PO'
     AND pha.segment1 = 'PO123456'
ORDER BY pha.segment1
       , pah.sequence_num DESC;

-- ##############################################################################
--        REQUISITION CANCELLATIONS
-- ##############################################################################

  SELECT pah.creation_date
       , prha.creation_date req_date
       , prha.authorization_status
       , prha.segment1
       , prha.cancel_flag
       , prha.interface_source_code
       , prha.wf_item_key
       , prha.approved_date
       , prha.created_by
       , pah.created_by
    FROM po.po_action_history pah
    JOIN po.po_requisition_headers_all prha ON object_id = prha.requisition_header_id
   WHERE pah.object_type_code = 'REQUISITION'
     AND pah.action_code = 'CANCEL'
     AND pah.creation_date > '01-OCT-2013'
     AND pah.creation_date < '01-NOV-2013'
     AND prha.approved_date IS NULL
     AND prha.created_by <> pah.created_by
ORDER BY prha.segment1 desc;
     
SELECT   NVL(TO_CHAR(extract(YEAR FROM prha.creation_date)),'TOTAL') CREATION_YEAR
       , SUM(1) total
    FROM po.po_action_history pah
    JOIN po.po_requisition_headers_all prha ON object_id = prha.requisition_header_id
   WHERE pah.object_type_code = 'REQUISITION'
     AND pah.action_code = 'CANCEL'
     AND prha.authorization_status = 'CANCELLED'
     AND prha.approved_date IS NULL
     AND prha.created_by = pah.created_by
GROUP BY rollup(extract(YEAR FROM prha.creation_date));

SELECT   NVL(TO_CHAR(extract(YEAR FROM prha.creation_date)),'TOTAL') CREATION_YEAR
       , SUM(1) total
    FROM po.po_requisition_headers_all prha
GROUP BY rollup(extract(YEAR FROM prha.creation_date));

Approvals – Approval Groups

-- ##############################################################################
--      PURCHASING APPROVAL GROUPS
-- ##############################################################################

        select pcga.control_group_name
             , pcga.DESCRIPTION control_group_description
             , flv1.meaning
             , flv2.meaning
             , pcr.amount_limit
             , case when pcr.object_code = 'ACCOUNT_RANGE' then
                    pcr.segment1_low || '.' || pcr.segment2_low || '.' || pcr.segment3_low || '.' || pcr.segment4_low || '.' || pcr.segment5_low
                    else NULL
               end low_value
             , case when pcr.object_code = 'ACCOUNT_RANGE' then
                    pcr.segment1_high || '.' || pcr.segment2_high || '.' || pcr.segment3_high || '.' || pcr.segment4_high || '.' || pcr.segment5_high
                    else NULL
               end high_value
          from po_control_groups_all pcga 
             , po.po_control_rules pcr
             , fnd_lookup_values flv1
             , fnd_lookup_values flv2
         where pcga.control_group_id = pcr.control_group_id
           and pcr.object_code = flv1.lookup_code
           and pcr.rule_type_code = flv2.lookup_code
           and flv1.lookup_type = 'CONTROLLED_OBJECT'
           and flv2.lookup_type = 'CONTROL_TYPE'
           and pcr.object_code = 'DOCUMENT_TOTAL'
      order by pcga.control_group_name
             , flv1.meaning desc;

iProcurement – Active Shopping Cart Error

Sometimes we’d get helpdesk calls where a user added an item to the shopping cart in iProc, but the line was added to an existing requisition instead of being added to the cart.

This SQL used to be able to find the problem records. We needed a datafix from Oracle to resolve the issue – I don’t have the details about the fix unfortunately.

-- ##############################################################################
--        ACTIVE SHOPPING CART ISSUE
-- ##############################################################################
SELECT   h.requisition_header_id req_header_id
       , h.last_update_date
       , h.segment1 req_num
       , h.creation_date
       , h.authorization_status
       , u.user_name
       , u.description
    FROM po.po_requisition_headers_all h
    JOIN hr.per_all_people_f p ON p.person_id = h.preparer_id
    JOIN applsys.fnd_user u    ON u.employee_id = p.person_id
     AND SYSDATE BETWEEN p.effective_start_date AND NVL(p.effective_end_date, SYSDATE + 1)
   WHERE h.active_shopping_cart_flag = 'Y'
     AND h.authorization_status = 'CANCELLED'
ORDER BY h.creation_date DESC;

Requisitions and Purchase Orders – Details

Sections:

  1. Requisition and PO Details Combined – Line Details
  2. Requisition to PO to Invoice
  3. Requisition to PO – to AP Invoice Join

Requisition and PO Details Combined – Line Details

-- ##############################################################################
--      REQUISITION AND PO DETAILS COMBINED _ LINE DETAILS
-- ##############################################################################

      SELECT  DISTINCT -- distinct to get rid of extra ap invoice distributions as not needed here
              prha.segment1 req_no
            , prha.requisition_header_id
            , prha.creation_date req_created
            , fu_req.description req_created_by
            , prla.line_num req_line
            , templ.template_name line_type
            , prha.interface_source_code
            , CASE
                -- ----------------------------------------------- PUNCHOUT
              WHEN prla.catalog_type = 'EXTERNAL'
              AND prla.catalog_source = 'EXTERNAL'
              AND prla.source_type_code = 'VENDOR' THEN 'PUNCHOUT'
                 -- -----------------------------------------------INTERNAL CATALOGUE
              WHEN prla.catalog_type = 'CATALOG'
              AND prla.catalog_source = 'INTERNAL'
              AND prla.source_type_code = 'VENDOR' THEN 'LOCAL_CATALOGUE'
                 -- ----------------------------------------------- NON CATALOGUE
              WHEN prla.catalog_type = 'NONCATALOG'
              AND prla.catalog_source = 'INTERNAL'
              AND prla.source_type_code = 'VENDOR' THEN 'NONCAT'
                 ELSE 'Other'
              END order_type
            , prla.unit_price
            , prla.quantity
            , prla.item_description
            , prla.source_type_code
            , hla_req_line_ship_to.location_code req_ship_to
            , '------> PO_HEADER'
            , pha.segment1 po_no
            , pha.po_header_id
            , pha.creation_date po_creation_date
            , fu_po.description po_created_by
            , pv.vendor_name supp_name_on_po
            , pvsa.vendor_site_code supp_site
            , pha.document_creation_method      
            , hla_po_ship_to.location_code po_header_ship_to
            , hla_po_bill_to.location_code po_header_bill_to
            , '------> PO_LINES'
            , pla.line_num po_line
            , pla.unit_price
            , pla.quantity
            , pla.item_description
            , '------> PO_DISTRIBUTIONS'
            , pda.distribution_num
            , pda.quantity_ordered distrib_qty_ordered
            , ppa.segment1 project
            , gcc.concatenated_segments code_combination
            , gcc.enabled_flag
            , '------> AP_INVOICES'
            , aia.invoice_id
            , aia.doc_sequence_value
         FROM po.po_headers_all pha
         JOIN po.po_lines_all pla                      ON pha.po_header_id =            pla.po_header_id
         JOIN po.po_distributions_all pda              ON pda.po_line_id =              pla.po_line_id
         JOIN ap.ap_suppliers pv                       ON pha.vendor_id =               pv.vendor_id
         JOIN ap.ap_supplier_sites_all pvsa            ON pha.vendor_site_id =          pvsa.vendor_site_id          AND pv.vendor_id =                pvsa.vendor_id
         JOIN po.po_line_locations_all plla            ON plla.po_line_id =             pla.po_line_id               AND plla.po_header_id =           pha.po_header_id AND pda.line_location_id =        plla.line_location_id
         JOIN po.po_requisition_lines_all prla         ON plla.line_location_id =       prla.line_location_id
         JOIN po.po_requisition_headers_all prha       ON prha.requisition_header_id =  prla.requisition_header_id 
         JOIN po.po_req_distributions_all prda         ON prla.requisition_line_id =    prda.requisition_line_id     AND prda.distribution_id =        pda.req_distribution_id
         JOIN applsys.fnd_user fu_req                  ON prha.created_by =             fu_req.user_id
         JOIN applsys.fnd_user fu_po                   ON pha.created_by =              fu_po.user_id
    LEFT JOIN icx.por_noncat_templates_all_tl templ    ON prla.NONCAT_TEMPLATE_ID =     templ.template_id
    LEFT JOIN pa.pa_projects_all ppa                   ON pda.project_id =              ppa.project_id
         JOIN hr.hr_locations_all hla_req_line_ship_to ON prla.deliver_to_location_id = hla_req_line_ship_to.location_id
         JOIN hr.hr_locations_all hla_po_ship_to       ON pha.ship_to_location_id =     hla_po_ship_to.location_id
         JOIN hr.hr_locations_all hla_po_bill_to       ON pha.bill_to_location_id =     hla_po_bill_to.location_id
         JOIN gl_code_combinations_kfv gcc             ON pda.code_combination_id =     gcc.code_combination_id
    LEFT JOIN ap.ap_invoice_distributions_all aida     ON pda.po_distribution_id =      aida.po_distribution_id
    LEFT JOIN ap.ap_invoices_all aia                   ON aia.invoice_id =              aida.invoice_id
        WHERE 1 = 1
          AND prha.creation_date > '18-JUL-2016'
--          AND pha.segment1 IN ('PO123456)
--          AND pha.creation_date BETWEEN '01-JAN-2012' AND '03-JAN-2013'
--          AND prha.segment1 = '12345678'
     ORDER BY pha.creation_date
            , pha.segment1
            , pla.line_num
            , pda.distribution_num;

Requisition to PO to Invoice

-- ##############################################################################
--      REQ TO PO TO RECEIPT TO INVOICE
-- ##############################################################################

SELECT prha.segment1 req
     , pha.segment1 po
     , aia.invoice_num
     , aia.doc_sequence_value inv_voucher
     , rsh.receipt_num receipt     
     , pha.document_creation_method
     , fu.description requisitioner
     , hla.location_code req_location
     , pv.segment1 supp_no
     , pv.vendor_name supp_name
     , pvsa.vendor_site_code supp_site
     , pv.vendor_type_lookup_code vendor_classification
     , pha.creation_date po_creation_date
     , aia.invoice_date
     , '-----> AP INV DISTRIBUTIONS'
     , aida.distribution_line_number
     , aida.invoice_distribution_id
     , aida.line_type_lookup_code
     , aida.quantity_invoiced
     , aida.amount
  FROM po.po_headers_all pha
     , po.po_lines_all pla
     , po.po_distributions_all pda
     , ap.ap_suppliers pv
     , ap.ap_supplier_sites_all pvsa
     , applsys.fnd_user fu
     , po.po_requisition_lines_all prla
     , po.po_requisition_headers_all prha
     , po.po_req_distributions_all prda
     , po.po_line_locations_all plla
     , hr.hr_locations_all hla
     , po.rcv_transactions rt
     , po.rcv_shipment_headers rsh
     , ap.ap_invoices_all aia
     , ap.ap_invoice_distributions_all aida
 WHERE pha.po_header_id = pla.po_header_id
   AND pla.po_line_id = pda.po_line_id
   AND pha.vendor_id = pv.vendor_id
   AND pha.vendor_site_id = pvsa.vendor_site_id
   AND pv.vendor_id = pvsa.vendor_id
   AND prha.created_by = fu.user_id
   AND prha.requisition_header_id = prla.requisition_header_id
   AND prla.line_location_id = plla.line_location_id
   AND prla.requisition_line_id = prda.requisition_line_id
   AND prla.deliver_to_location_id = hla.location_id
   AND prda.distribution_id = pda.req_distribution_id
   AND pda.line_location_id = plla.line_location_id
   AND pda.po_distribution_id = aida.po_distribution_id
   AND aia.invoice_id = aida.invoice_id   
   AND pda.po_distribution_id = rt.po_distribution_id
   AND rt.shipment_header_id = rsh.shipment_header_id
   AND rt.transaction_type = 'RECEIVE'
   AND prha.creation_date > '01-JUN-2016';

Requisition to PO – to AP Invoice Join

-- ##############################################################################
--      REQ TO PO TO RECEIPT - NO AP INVOICE JOIN
-- ##############################################################################

	SELECT ppa.segment1 project
		 , prha.segment1 req_num
		 , prha.creation_date req_date
		 , prha.authorization_status req_approval_status
		 , prha.closed_code req_closed_status
		 , pha.segment1 po_num
		 , pha.creation_date po_date
		 , pha.authorization_status po_approval_status
		 , pha.closed_code po_closed_status
		 , prla.closed_code line_status
		 , pav.agent_name po_buyer
		 , prla.line_num line
		 , prda.distribution_num req_dist_num
		 , prda.req_line_quantity
		 , prla.unit_meas_lookup_code uom
		 , prla.unit_price price
		 , prla.quantity qty
		 , rsh.receipt_num
		 , rt.transaction_type
		 , rsh.creation_date rcpt_date
		 , rt.quantity qty_received
		 , pda.quantity_billed qty_billed
		 , (pda.quantity_billed * prla.unit_price) amt_billed
		 , mcb.segment1 || '.' || mcb.segment2 purchase_category
		 , prla.suggested_vendor_product_code catalogue_code
		 , TO_CHAR(prla.need_by_date, 'DD-MON-RRRR') need_by_date
		 , prla.note_to_agent note_to_buyer
		 , prla.note_to_vendor note_to_supplier
		 , prla.suggested_vendor_name supplier
		 , prla.suggested_vendor_location site
	  FROM po.po_requisition_headers_all prha
		 , po.po_requisition_lines_all prla
		 , po.po_req_distributions_all prda
		 , po.po_line_locations_all plla
		 , po.po_lines_all pla
		 , po.po_headers_all pha
		 , po.po_distributions_all pda
		 , hr.hr_locations_all_tl hlat
		 , inv.mtl_categories_b mcb
		 , gl.gl_code_combinations gcc
		 , pa.pa_projects_all ppa
		 , apps.po_agents_v pav
		 , po.rcv_transactions rt
		 , po.rcv_shipment_headers rsh
		 , po.rcv_shipment_lines rsl
	 WHERE prha.requisition_header_id = prla.requisition_header_id
	   AND plla.line_location_id = prla.line_location_id
	   AND pla.po_line_id = plla.po_line_id
	   AND pha.po_header_id = plla.po_header_id
	   AND pla.po_line_id = pda.po_line_id
	   AND pda.line_location_id = plla.line_location_id
	   AND prla.deliver_to_location_id = hlat.location_id
	   AND prla.requisition_line_id = prda.requisition_line_id
	   AND gcc.code_combination_id = prda.code_combination_id
	   AND mcb.category_id = prla.category_id
	   AND prda.project_id = ppa.project_id(+)
	   AND pha.agent_id = pav.agent_id
	   AND pda.po_distribution_id = rt.po_distribution_id
	   AND rt.shipment_header_id = rsh.shipment_header_id
	   AND rt.shipment_line_id = rsl.shipment_line_id
	   AND rt.transaction_type = 'RECEIVE'
	   AND prha.creation_date > '01-JUN-2016'
  ORDER BY prha.segment1 DESC
         , prla.line_num;

Requisitions and Purchase Orders – SQL Join

-- ##############################################################################
--      REQUISITION HEADERS
-- ##############################################################################

  SELECT prha.segment1 requisition
       , prha.creation_date
       , prha.authorization_status
    FROM po.po_requisition_headers_all prha
   WHERE prha.creation_date > '06-JUL-2015'
ORDER BY prha.creation_date DESC;

-- ##############################################################################
--      REQUISITION HEADERS AND LINES
-- ##############################################################################

  SELECT prha.segment1 requisition
       , prha.creation_date
       , prha.authorization_status
       , prla.line_num
       , prla.unit_price
       , prla.quantity
    FROM po.po_requisition_headers_all prha
    JOIN po.po_requisition_lines_all prla ON prha.requisition_header_id = prla.requisition_header_id
   WHERE prha.creation_date > '06-JUL-2015'
ORDER BY prha.creation_date DESC
       , prha.segment1
       , prla.line_num;

-- ##############################################################################
--      REQUISITION HEADERS AND LINES AND LINE_LOCATIONS_ALL
-- ##############################################################################

-- line_locations_all contains details used to join to the PO tables

  SELECT prha.segment1 requisition
       , prha.creation_date
       , prha.authorization_status
       , prla.line_num
       , prla.unit_price
       , prla.quantity
       , plla.po_header_id
       , plla.po_line_id
    FROM po.po_requisition_headers_all prha
    JOIN po.po_requisition_lines_all prla ON prha.requisition_header_id = prla.requisition_header_id
    JOIN po.po_line_locations_all plla    ON prla.line_location_id =      plla.line_location_id
   WHERE prha.creation_date > '06-JUL-2015'
ORDER BY prha.creation_date DESC
       , prha.segment1
       , prla.line_num;

-- ##############################################################################
--      REQUISITION HEADERS AND LINES AND LINE_LOCATIONS_ALL, JOINED THROUGH TO PO HEADERS AND LINES
-- ##############################################################################

-- left joined to po tables so can get REQ details which are not linked to PO

      SELECT prha.segment1 requisition, prha.requisition_header_id
           , prha.creation_date
           , prha.authorization_status
           , prla.line_num
           , prla.unit_price
           , prla.quantity
           , prla.suggested_vendor_name
           , prla.suggested_vendor_location
           , prla.vendor_id
           , prla.vendor_site_id
           , pha.segment1 po
           , pha.po_header_id
           , pha.authorization_status
           , pla.line_num po_line_number
           , pla.po_line_id
           , plla.line_location_id
        FROM po.po_requisition_headers_all prha
        JOIN po.po_requisition_lines_all prla ON prha.requisition_header_id = prla.requisition_header_id
   LEFT JOIN po.po_line_locations_all plla    ON prla.line_location_id =      plla.line_location_id
   LEFT JOIN po.po_lines_all pla              ON plla.po_line_id =            pla.po_line_id
   LEFT JOIN po.po_headers_all pha            ON plla.po_header_id =          pha.po_header_id
       WHERE 1 = 1
--         AND prha.creation_date > '04-JAN-2016'
         AND pha.creation_date BETWEEN '01-JAN-2014' AND '03-JAN-2014'
--         AND pha.segment1 = 1425154
--         AND prha.segment1 = '1457562'
    --     AND pha.po_header_id = 1225614
    ORDER BY prha.creation_date DESC
           , prha.segment1
           , prla.line_num;

-- Including PO value

      SELECT prha.segment1 requisition
           , prha.requisition_header_id
           , prha.creation_date
           , pha.segment1
           , pha.po_header_id
           , pha.creation_date
           , pv.vendor_name
           , sum(pla.quantity*pla.unit_price) po_value
        FROM po.po_requisition_headers_all prha
        JOIN po.po_requisition_lines_all prla ON prha.requisition_header_id = prla.requisition_header_id
   LEFT JOIN po.po_line_locations_all plla    ON prla.line_location_id =      plla.line_location_id
   LEFT JOIN po.po_lines_all pla              ON plla.po_line_id =            pla.po_line_id
   LEFT JOIN po.po_headers_all pha            ON plla.po_header_id =          pha.po_header_id
        join ap.ap_suppliers pv on pha.vendor_id = pv.vendor_id 
       WHERE 1 = 1
--         AND prha.creation_date BETWEEN '01-JUL-2015' AND '03-JUL-2015'
--         AND pha.creation_date BETWEEN '01-JUL-2015' AND '03-JUL-2015' 
         AND prha.segment1 = 'PO 1234567'
    group by prha.segment1
           , pha.segment1
           , pv.vendor_name
           , pha.po_header_id
           , prha.requisition_header_id
           , pha.creation_date
           , prha.creation_date
    order by 4;

Requisitions – Basic Details

Sections:

  1. Requisition Header Details
  2. Requisition Header – with Line Details
  3. Requisition Header – with Line and Distribution Details

Requisition Header Details

-- ##############################################################################
--      REQUISITION HEADER DETAILS
-- ##############################################################################

  SELECT prha.segment1 requisition
       , prha.creation_date
       , prha.description
       , prha.authorization_status
       , prha.approved_date
       , prha.cancel_flag
       , prha.interface_source_code
       , prha.creation_date
       , prha.wf_item_key
       , prha.wf_item_type
       , fu.description created_by
    FROM po.po_requisition_headers_all prha
    JOIN applsys.fnd_user fu ON prha.created_by = fu.user_id
   WHERE 1 = 1
--     AND prha.creation_date > '01-MAY-2015'
--     AND prha.interface_source_code = 'INV'
--     AND prha.segment1 = '12345678'
     AND prha.creation_date > '01-APR-2017'
ORDER BY 2 DESC;

Requisition Header – with Line Details

-- ##############################################################################
--      REQUISITION HEADER WITH LINE DETAILS
-- ##############################################################################

      SELECT prha.segment1 po
           , prha.requisition_header_id
           , prha.creation_date
           , prha.last_update_date
           , fu.description created_by
           , prha.authorization_status
           , prha.approved_date
           , prla.line_num
           , templ.template_name line_type
           , prha.interface_source_code
           , CASE
                -- ----------------------------------------------- PUNCHOUT
             WHEN prla.catalog_type = 'EXTERNAL'
             AND prla.catalog_source = 'EXTERNAL'
             AND prla.source_type_code = 'VENDOR' THEN 'PUNCHOUT'
                -- -----------------------------------------------INTERNAL CATALOGUE
             WHEN prla.catalog_type = 'CATALOG'
             AND prla.catalog_source = 'INTERNAL'
             AND prla.source_type_code = 'VENDOR' THEN 'LOCAL_CATALOGUE'
                -- ----------------------------------------------- NON CATALOGUE
             WHEN prla.catalog_type = 'NONCATALOG'
             AND prla.catalog_source = 'INTERNAL'
             AND prla.source_type_code = 'VENDOR' THEN 'NONCAT'
                ELSE 'Other'
             END order_type
           , prla.item_description
           , prla.quantity
           , prla.unit_price
           , prla.new_supplier_flag 
           , prla.suggested_vendor_name , prla.suggested_vendor_location
           , pv.vendor_name supplier
           , pvsa.vendor_site_code site
           , mcb.segment1 || '.' || mcb.segment2 purchase_category
           , hlat_ship_line.description line_ship_to
           , prha.interface_source_code
           , '##############'
           , zlv.*
        FROM po.po_requisition_headers_all prha
        JOIN po.po_requisition_lines_all prla      ON prha.requisition_header_id =  prla.requisition_header_id
        LEFT JOIN po.po_line_locations_all plla    ON plla.line_location_id =       prla.line_location_id
        JOIN inv.mtl_categories_b mcb              ON mcb.category_id =             prla.category_id
   LEFT JOIN icx.por_noncat_templates_all_tl templ ON prla.noncat_template_id =    templ.template_id
   LEFT JOIN ap.ap_suppliers pv                    ON prla.vendor_id =              pv.vendor_id
   LEFT JOIN ap.ap_supplier_sites_all pvsa         ON prla.vendor_site_id =         pvsa.vendor_site_id
        JOIN applsys.fnd_user fu                   ON prha.created_by =             fu.user_id
        JOIN hr.hr_locations_all_tl hlat_ship_line ON prla.deliver_to_location_id = hlat_ship_line.location_id
   LEFT JOIN apps.zx_lines_v zlv ON prha.requisition_header_id = zlv.trx_id AND prla.requisition_line_id = zlv.trx_line_id
       WHERE 1 = 1
         AND prha.creation_date > '11-NOV-2015'
         AND prha.segment1 IN ('12345678', '12345679')
    ORDER BY prha.creation_date
           , prha.segment1
           , prla.line_num;

SELECT * FROM apps.zx_lines_v zlv WHERE zlv.trx_id = 3341135;

Requisition Header – with Line and Distribution Details

-- ##############################################################################
--      REQUISITION HEADER WITH LINE DETAILS AND DISTRIBUTIONS
-- ##############################################################################

      SELECT prha.segment1 po
           , prha.creation_date
           , fu.description created_by
           , prha.authorization_status
           , prha.approved_date
           , prla.line_num
           , templ.template_name line_type
           , prha.interface_source_code
           , CASE
                -- ----------------------------------------------- PUNCHOUT
             WHEN prla.catalog_type = 'EXTERNAL'
             AND prla.catalog_source = 'EXTERNAL'
             AND prla.source_type_code = 'VENDOR' THEN 'PUNCHOUT'
                -- -----------------------------------------------INTERNAL CATALOGUE
             WHEN prla.catalog_type = 'CATALOG'
             AND prla.catalog_source = 'INTERNAL'
             AND prla.source_type_code = 'VENDOR' THEN 'LOCAL_CATALOGUE'
                -- ----------------------------------------------- NON CATALOGUE
             WHEN prla.catalog_type = 'NONCATALOG'
             AND prla.catalog_source = 'INTERNAL'
             AND prla.source_type_code = 'VENDOR' THEN 'NONCAT'
                ELSE 'Other'
             END order_type
           , prla.item_description
           , prla.quantity
           , prla.unit_price
           , pv.vendor_name supplier
           , pvsa.vendor_site_code site
           , mcb.segment1 || '.' || mcb.segment2 purchase_category
           , hlat_ship_line.description line_ship_to
           , prda.distribution_num
           , prda.allocation_value
           , prda.allocation_type
           , gcc.concatenated_segments code_combination
           , ppa.segment1 project
           , pt.task_number
           , prda.expenditure_type exp_type
        FROM po.po_requisition_headers_all prha
        JOIN po.po_requisition_lines_all prla      ON prha.requisition_header_id =  prla.requisition_header_id
        JOIN po.po_req_distributions_all prda      ON prla.requisition_line_id =    prda.requisition_line_id
        JOIN inv.mtl_categories_b mcb              ON mcb.category_id =             prla.category_id
        JOIN ap.ap_suppliers pv                    ON prla.vendor_id =              pv.vendor_id
        JOIN ap.ap_supplier_sites_all pvsa         ON prla.vendor_site_id =         pvsa.vendor_site_id
        JOIN applsys.fnd_user fu                   ON prha.created_by =             fu.user_id
        JOIN hr.hr_locations_all_tl hlat_ship_line ON prla.deliver_to_location_id = hlat_ship_line.location_id
        JOIN gl_code_combinations_kfv gcc          ON pda.code_combination_id =     gcc.code_combination_id
   LEFT JOIN icx.por_noncat_templates_all_tl templ ON prla.noncat_template_id =    templ.template_id
   LEFT JOIN pa.pa_projects_all ppa                ON prda.project_id =             ppa.project_id
   LEFT JOIN pa.pa_tasks pt                        ON prda.task_id =                pt.task_id                
       WHERE 1 = 1
         AND prha.segment1 = '12345678'
--         AND prha.creation_date BETWEEN '17-OCT-2015' AND '19-OCT-2015'
--         AND prha.authorization_status = 'APPROVED'
    ORDER BY prha.creation_date
           , prha.segment1
           , prla.line_num
           , prda.distribution_num;

Purchase Orders – Value, Ordered, Receipted, Billed

I wrote this SQL when I was looking for POs to test against. I was trying to find POs which met certain criteria – for example – approved, but not receipted. Or approved, receipted, but not invoiced.

I don’t think it’s perfect, it’s a bit of a hack job, but I found it useful.

-- ##############################################################################
--      PURCHASE ORDERS - SUMMARY VIEW
-- ##############################################################################

    SELECT pha.segment1 po
         , pha.po_header_id
         , pha.creation_date
         , pv.vendor_name supplier
         , pvsa.vendor_site_code site
         , SUM(pla.unit_price * pla.quantity) total_value
         , SUM(pla.quantity) total_ordered
         , SUM(plla.quantity_received) total_receipted
         , SUM(plla.quantity_billed) total_billed
         , COUNT(DISTINCT pla.po_line_id) line_count
         , (select count(*) from po_distributions_all where po_header_id = pha.po_header_id) dist_ct
      FROM po.po_headers_all pha
      JOIN ap.ap_suppliers pv on pha.vendor_id = pv.vendor_id
      JOIN ap.ap_supplier_sites_all pvsa ON pha.vendor_site_id = PVSA.VENDOR_SITE_ID AND pvsa.vendor_id = pv.vendor_id
      JOIN po.po_lines_all pla ON pha.po_header_id = pla.po_header_id
      JOIN po.po_line_locations_all plla ON plla.po_line_id = pla.po_line_id
     WHERE pha.authorization_status = 'APPROVED'
       AND pha.creation_date BETWEEN '01-JAN-2017' AND '01-NOV-2017'
       AND pla.LINE_TYPE_ID = 1020
       AND pla.closed_code = 'OPEN' -- line not closed or cancelled
       AND pvsa.pay_on_code IS NULL -- supplier is not set to pay on receipt
       AND (select distinct 'y' from po_distributions_all where po_header_id = pha.po_header_id AND project_id IS NULL) = 'y' -- not matched to project
--       AND pha.segment1 = 'PO123456'
    HAVING SUM(pla.unit_price * pla.quantity) > 1
       AND COUNT(DISTINCT pla.closed_code) = 1 -- all lines open
       AND COUNT(DISTINCT pla.po_line_id) = 1 -- single line
       AND SUM(plla.quantity_received) = 0 -- not receipted
       AND SUM(plla.quantity_billed) = 0
  GROUP BY pha.po_header_id
         , pha.creation_date
         , pha.segment1
--         , PHA.CLOSED_CODE
--         , PHA.CANCEL_FLAG
         , pv.vendor_name
         , pvsa.vendor_site_code
  ORDER BY 5 DESC;

Purchase Orders – Basic Details

Sections:

  1. PO Header Details
  2. PO Header – with Line Details
  3. PO Header – with Line and Distribution Details
  4. PO Header – with Line, Distribution and Amount Details

PO Header Details

-- ##############################################################################
--      PO HEADER DETAILS
-- ##############################################################################

  SELECT pha.segment1 po
       , pha.creation_date
       , fu.description created_by
       , pha.authorization_status
       , pha.approved_flag
       , pha.approved_date
       , pha.type_lookup_code
       , pha.document_creation_method
       , pv.vendor_name supplier
       , pvsa.vendor_site_code site
       , pha.attribute1
       , pha.attribute2
       , pha.attribute6
       , hlat_bill.description header_bill_to
       , hlat_ship.description header_ship_to
    FROM po.po_headers_all pha
    JOIN ap.ap_suppliers pv               ON pha.vendor_id =           pv.vendor_id
    JOIN ap.ap_supplier_sites_all pvsa    ON pha.vendor_site_id =      pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id
    JOIN applsys.fnd_user fu              ON pha.created_by =          fu.user_id
    JOIN hr.hr_locations_all_tl hlat_bill ON pha.ship_to_location_id = hlat_bill.location_id
    JOIN hr.hr_locations_all_tl hlat_ship ON pha.bill_to_location_id = hlat_ship.location_id
   WHERE 1 = 1
     AND pha.creation_date > '18-JUL-2016'
--     AND pha.authorization_status = 'INCOMPLETE'
--     AND pvsa.vendor_site_code = 'BLUECHEEZ'
ORDER BY pha.creation_date DESC;

PO Header – with Line Details

-- ##############################################################################
--      PO HEADER WITH LINE DETAILS
-- ##############################################################################

  SELECT pha.segment1 po
       , pha.creation_date
       , fu.description created_by
       , pha.authorization_status
       , pha.type_lookup_code
       , pha.document_creation_method
       , pla.line_num
       , pla.item_description
       , pla.quantity
       , pla.unit_price
       , pla.creation_date line_created
       , pv.vendor_name supplier
       , pvsa.vendor_site_code site
       , mcb.segment1 || '.' || mcb.segment2 purchase_category
       , hlat_bill.description header_bill_to
       , hlat_ship.description header_ship_to
       , hlat_ship_line.description line_ship_to
    FROM po.po_headers_all pha
    JOIN po.po_lines_all pla                   ON pha.po_header_id =         pla.po_header_id
    JOIN po.po_line_locations_all plla         ON plla.po_line_id =          pla.po_line_id AND plla.po_header_id = pha.po_header_id
    JOIN inv.mtl_categories_b mcb              ON mcb.category_id =          pla.category_id
    JOIN ap.ap_suppliers pv                    ON pha.vendor_id =            pv.vendor_id
    JOIN ap.ap_supplier_sites_all pvsa         ON pha.vendor_site_id =       pvsa.vendor_site_id AND pv.vendor_id =pvsa.vendor_id
    JOIN applsys.fnd_user fu                   ON pha.created_by =           fu.user_id
    JOIN hr.hr_locations_all_tl hlat_bill      ON pha.ship_to_location_id =  hlat_bill.location_id
    JOIN hr.hr_locations_all_tl hlat_ship      ON pha.bill_to_location_id =  hlat_ship.location_id
    JOIN hr.hr_locations_all_tl hlat_ship_line ON plla.ship_to_location_id = hlat_ship_line.location_id
   WHERE 1 = 1
--     AND pha.creation_date > '06-JUL-2015'
     AND pha.segment1 = 'PO123456'
ORDER BY pha.creation_date
       , pha.segment1
       , pla.line_num;

PO Header – with Line and Distribution Details

-- ##############################################################################
--      PO HEADER WITH LINE AND DISTRIBUTION DETAILS
-- ##############################################################################

      SELECT pha.segment1 po
           , pha.creation_date
           , fu.description created_by
           , pha.authorization_status
           , pha.type_lookup_code
           , pha.document_creation_method
           , pla.line_num
           , pla.item_description
           , pla.quantity
           , pla.unit_price
           , pv.vendor_name supplier
           , pvsa.vendor_site_code site
           , mcb.segment1 || '.' || mcb.segment2 purchase_category
           , hlat_bill.description header_bill_to
           , hlat_ship.description header_ship_to
           , hlat_ship_line.description line_ship_to
           , pda.distribution_num
           , pda.quantity_ordered
           , gcc.concatenated_segments code_combination
           , ppa.segment1 project
        FROM po.po_headers_all pha
        JOIN po.po_lines_all pla                   ON pha.po_header_id =         pla.po_header_id
        JOIN po.po_distributions_all pda           ON pla.po_line_id =           pda.po_line_id AND pda.po_header_id =  pha.po_header_id
        JOIN po.po_line_locations_all plla         ON plla.po_line_id =          pla.po_line_id AND plla.po_header_id = pha.po_header_id
        JOIN inv.mtl_categories_b mcb              ON mcb.category_id =          pla.category_id
        JOIN ap.ap_suppliers pv                    ON pha.vendor_id =            pv.vendor_id
        JOIN ap.ap_supplier_sites_all pvsa         ON pha.vendor_site_id =       pvsa.vendor_site_id AND pv.vendor_id = pvsa.vendor_id
        JOIN applsys.fnd_user fu                   ON pha.created_by =           fu.user_id
        JOIN hr.hr_locations_all_tl hlat_bill      ON pha.ship_to_location_id =  hlat_bill.location_id
        JOIN hr.hr_locations_all_tl hlat_ship      ON pha.bill_to_location_id =  hlat_ship.location_id
        JOIN hr.hr_locations_all_tl hlat_ship_line ON plla.ship_to_location_id = hlat_ship_line.location_id
        JOIN gl_code_combinations_kfv gcc          ON pda.code_combination_id =  gcc.code_combination_id
   LEFT JOIN pa.pa_projects_all ppa                ON pda.project_id =           ppa.project_id
       WHERE pha.creation_date > '10-JUN-2016'
    ORDER BY pha.creation_date
           , pha.segment1
           , pla.line_num
           , pda.distribution_num;

PO Header – with Line, Distribution and Amount Details

-- ##############################################################################
--      PO HEADER WITH LINE, DISTRIBUTION AND AMOUNT DETAILS
-- ##############################################################################

      SELECT  pha.segment1 document_number
            , TRUNC(pha.creation_date) order_date
            , DECODE (pha.authorization_status, 'APPROVED', 'Approved', 'CANCELLED', 'Cancelled', 'IN PROCESS', 'In Process', 'INCOMPLETE', 'Incomplete', 'PRE-APPROVED', 'Pre-Approved', 'REJECTED', 'Rejected', 'REQUIRES REAPPROVAL', 'Requires Reapproval', 'RETURNED', 'Returned', NULL) authorization_status
            , DECODE (pha.closed_code, 'APPROVED', 'Approved', 'AWAITING PRINTING', 'Awaiting Printing', 'CANCELLED', 'Cancelled', 'CLOSED', 'Closed', 'CLOSED FOR INVOICE', 'Closed For Invoice', 'CLOSED FOR RECEIVING', 'Closed For Receiving', 'FINALLY CLOSED', 'Finally Closed', 'FROZEN', 'Frozen', 'IN PROCESS', 'In Process', 'INCOMPLETE', 'Incomplete', 'NOT ON HOLD', 'Not On Hold', 'NOT RESERVED', 'Not Reserved', 'ON HOLD', 'On Hold', 'OPEN', 'Open', 'PRE-APPROVED', 'Pre-Approved', 'PRINTED', 'Printed', 'REJECTED', 'Rejected', 'REQUIRES REAPPROVAL', 'Requires Reapproval', 'RESERVED', 'Reserved', 'RETURNED', 'Returned', 'UNFROZEN', 'Not Frozen', NULL, 'Not Fully Receipted') closure_status
            , papf.full_name buyer_name
            , fcv.currency_code currency_code
            , (NVL(pha.rate, 1) * pla.unit_price) currency_unit_price
            , pla.line_num
            , pla.item_description
            , INITCAP (plla.closed_code) ship_status
            , fu.description
            , loc.location_code deliver_to_loc
            , mtl_cat.segment1 || '.' || mtl_cat.segment2 category
            , ppa.segment1 project
            , gcc.concatenated_segments code_combination
            , pda.quantity_ordered * (NVL (pha.rate, 1) * pla.unit_price) order_gbp_amount
            , (pda.quantity_delivered - pda.quantity_cancelled)  * (NVL(pha.rate, 1) * pla.unit_price) value_gbp_received
            , (apps.po_inq_sv.get_active_enc_amount (NVL (pda.rate, 1), pda.encumbered_amount, plla.shipment_type, pda.po_distribution_id)) active_encumb
            , PSA_AP_BC_PVT.Get_PO_Reversed_Encumb_Amount( pda.po_distribution_id,TO_DATE('01/JAN/2008'),TO_DATE('31/DEC/2015'),NULL) reversal_amount
            , pda.encumbered_amount-PSA_AP_BC_PVT.Get_PO_Reversed_Encumb_Amount( pda.po_distribution_id,TO_DATE('01/JAN/2008'),TO_DATE('31/DEC/2015'),NULL) main_encumbered_amount
         FROM po.po_headers_all pha
         JOIN po.po_lines_all pla           ON pha.po_header_id =            pla.po_header_id
         JOIN po.po_distributions_all pda   ON pda.po_line_id =              pla.po_line_id            AND pda.po_header_id = pha.po_header_id
         JOIN po.po_line_locations_all plla ON plla.po_header_id =           pha.po_header_id          AND plla.po_line_id = pla.po_line_id AND plla.po_line_id = pda.po_line_id
         join gl_code_combinations_kfv gcc  ON pda.code_combination_id =     gcc.code_combination_id
         JOIN hr.per_all_people_f papf      ON pha.agent_id =                papf.person_id            AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date
         JOIN hr.hr_locations_all_tl loc    ON pda.deliver_to_location_id =  loc.location_id
         JOIN apps.mtl_categories mtl_cat   ON pla.category_id =             mtl_cat.category_id
         JOIN ap.ap_suppliers pv            ON pha.vendor_id =               pv.vendor_id
         JOIN ap.ap_supplier_sites_all pvsa ON pha.vendor_site_id =          pvsa.vendor_site_id       AND pv.vendor_id = pvsa.vendor_id
         JOIN applsys.fnd_user fu           ON pda.deliver_to_person_id =    fu.employee_id
         JOIN apps.fnd_currencies_vl fcv    ON pha.currency_code =           fcv.currency_code
    LEFT JOIN pa.pa_projects_all ppa        ON ppa.project_id =              pda.project_id
        WHERE 1 = 1
    --      AND pha.segment1 IN ('PO123456')
          AND pha.creation_date > '01-JUL-2015'
          AND 1 = 1
 ORDER BY pha.creation_date DESC;

Project Transaction Sources

-- ##############################################################################
--      TRANSACTION SOURCES
-- ##############################################################################

  SELECT pts.user_transaction_source
       , pts.transaction_source
       , pts.batch_size
       , pts.purgeable_flag
       , pts.allow_adjustments_flag
       , pts.gl_accounted_flag
       , pts.allow_duplicate_reference_flag
       , pts.modify_interface_flag
       , pts.creation_date
       , fu1.user_name created_by
       , pts.last_update_date
       , fu2.user_name updated_by
    FROM pa.pa_transaction_sources pts
    JOIN applsys.fnd_user fu1 ON pts.created_by =      fu1.user_id
    JOIN applsys.fnd_user fu2 ON pts.last_updated_by = fu2.user_id
--   WHERE lower(pts.user_transaction_source) like '%web%'
ORDER BY 2;