DBA Jobs and Errors and Stuff

-- ##############################################################################
--      DBA JOBS, ERRORS, STUFF
-- ##############################################################################

-- jobs
select * from sys.dba_jobs;
select * from sys.dba_jobs_running;
select * from sys.dba_scheduler_jobs;
select * from sys.dba_scheduler_running_jobs;
select * from applsys.fnd_tables where table_name = 'DBA_JOBS';

-- objects
SELECT * FROM all_objects WHERE object_name = 'AP_INVOICES_UTILITY_PKG';
SELECT object_type,owner||'.'||object_name FROM dba_objects WHERE status='INVALID';
SELECT text FROM dba_source WHERE name = 'AP_INVOICES_UTILITY_PKG' AND line = 2;

-- errors
SELECT line, text FROM dba_errors WHERE name = 'AP_INVOICES_UTILITY_PKG';
SELECT * FROM user_errors;
SELECT * FROM dba_errors;

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;

Inventory Transactions – Costing Errors

Sometimes the costing of an Inventory Transaction can error (costed_flag = ‘E’). From my experience, when the errors happen, other transactions that need to be costed stack up behind the errored transactions and will not be costed either.

When I encountered this issue, I wrote some SQL to help find out more details about the errored transactions. Sometimes we needed datafixes to clear the errors, provided by Oracle via Service Requests.

-- ##############################################################################
--      INVENTORY TRANSACTIONS - COSTING ERRORS - BASIC DETAILS
-- ##############################################################################

-- error details with creation date, created by, transaction type etc.

  SELECT mmt.transaction_id tx_id
       , mmt.organization_id org_id
       , mtt.transaction_type_name tx_type
       , msib.segment1 item
       , msib.creation_date item_cr_date
       , mmt.request_id
       , mmt.costed_flag
       , mmt.last_update_date upd_date
       , mmt.creation_date cr_date
       , fu.description cr_by
       , mtt.transaction_type_name tx_type
    FROM inv.mtl_material_transactions mmt
    JOIN inv.mtl_system_items_b msib   ON mmt.inventory_item_id =   msib.inventory_item_id
                                      AND mmt.organization_id =     msib.organization_id 
    JOIN inv.mtl_transaction_types mtt ON mmt.transaction_type_id = mtt.transaction_type_id 
    JOIN applsys.fnd_user fu           ON mmt.created_by =          fu.user_id 
   WHERE 1 = 1
--     AND TRUNC(mmt.last_update_date) = '02-DEC-2013'
--     AND mmt.last_update_date BETWEEN TO_DATE('02-DEC-2013 16:09:00', 'DD-MON-YYYY HH24:MI:SS') AND TO_DATE('02-DEC-2013 16:14:00', 'DD-MON-YYYY HH24:MI:SS')
--     AND mmt.error_code = 'CST_INVALID_ACCT_ALIAS'
     AND mmt.costed_flag = 'E'
--     AND mmt.organization_id = 84
ORDER BY mmt.organization_id;

-- ##############################################################################
--      A MORE LIMITED SELECT
-- ##############################################################################

SELECT mmt.*
     , fu.description
  FROM apps.mtl_material_transactions mmt
  JOIN applsys.fnd_user fu           ON mmt.created_by =          fu.user_id 
  JOIN inv.mtl_transaction_types mtt ON mmt.transaction_type_id = mtt.transaction_type_id 
 WHERE mmt.organization_id = 84
--   AND mmt.creation_date > '04-NOV-2013'
   AND costed_flag = 'E';


-- ##############################################################################
--      ERROR COUNTING 1
-- ##############################################################################

  SELECT COUNT (costed_flag) total
       , costed_flag cflag
       , SUBSTR (ERROR_CODE
               , 1
               , 40)
            code
       , SUBSTR (error_explanation
               , 1
               , 100)
            explan
    FROM apps.mtl_material_transactions
  HAVING costed_flag IN ('E', 'N')
GROUP BY costed_flag
       , ERROR_CODE
       , error_explanation;

-- ##############################################################################
--      ERROR COUNTING 2
-- ##############################################################################

  SELECT mtt.transaction_type_name tx_type
       , SUM((mmt.primary_quantity) * mmt.actual_cost) chg
       , COUNT(*) ct
       , count(distinct mmt.source_project_id) proj_ct
    FROM inv.mtl_material_transactions mmt
    JOIN inv.mtl_system_items_b msib      ON mmt.inventory_item_id =   msib.inventory_item_id
                                         AND mmt.organization_id =     msib.organization_id 
    JOIN inv.mtl_transaction_types mtt    ON mmt.transaction_type_id = mtt.transaction_type_id 
    JOIN inv.mtl_transaction_accounts mta ON mmt.transaction_id =      mta.transaction_id 
    JOIN applsys.fnd_user fu              ON mmt.created_by =          fu.user_id
   WHERE 1 = 1
     AND mmt.creation_date >= '01-NOV-2013'
     AND mmt.creation_date < '10-NOV-2013'
--     AND mmt.costed_flag IN ('E','N')
     AND mmt.organization_id = 84
GROUP BY mtt.transaction_type_name
ORDER BY mmt.organization_id;