Oracle EBS SQLs – Order Hold, Credit Card Details

 
Business Need:
Oracle Applications (EBS) Support, Analysis, Troubleshooting, Reports, Tracking, etc. I am using R12.2.8. But the SQLs will be applicable most of the recent versions.
Helpful to analyze Credit Card Orders with Oracle Payments (iPayments / IBY) troubleshooting.
 
Queries:
Order Details – Hold Type, Name, Codes

-- hold types for a given order
SELECT OEH.HEADER_ID, OTT.NAME ORDER_TYPE,OEH.ORDER_NUMBER,OEH.PAYMENT_TYPE_CODE,RTM.NAME PAYMENT_TERM,OOL.LINE_NUMBER,
OOL.ORDERED_ITEM,OOL.SCHEDULE_SHIP_DATE,OOL.FLOW_STATUS_CODE LINE_STATUS,
OHD.NAME,OOH.ORDER_HOLD_ID,OOH.CREATION_DATE,OOH.RELEASED_FLAG
  FROM OE_ORDER_HEADERS_ALL OEH, OE_ORDER_LINES_ALL OOL,OE_TRANSACTION_TYPES_TL OTT,
  RA_TERMS_TL RTM.,
  OE_ORDER_HOLDS_ALL OOH,
  OE_HOLD_SOURCES_ALL OHS,
  OE_HOLD_DEFINITIONS OHD
WHERE 1=1
 AND OEH.HEADER_ID=OOL.HEADER_ID
   AND OEH.HEADER_ID = 5153513
   AND OEH.ORDER_TYPE_ID=OTT.TRANSACTION_TYPE_ID
   AND OEH.PAYMENT_TERM_ID=RTM.TERM_ID
   AND OEH.HEADER_ID=OOH.HEADER_ID(+)
   AND OOH.HOLD_SOURCE_ID=OHS.HOLD_SOURCE_ID(+)
   AND OHS.HOLD_ID=OHD.HOLD_ID(+);

  
Credit Card Details for the Order

-- orders, lines, credit card details (CC tables not used anymore)
SELECT   OOHA.HEADER_ID,OOLA.LINE_NUMBER,OOLA.LINE_ID,OP.LINE_ID,  OOHA.ORDER_NUMBER,
  (SELECT HCA.ACCOUNT_NUMBER   FROM HZ_CUST_ACCOUNTS HCA
  WHERE HCA.CUST_ACCOUNT_ID=OOHA.SOLD_TO_ORG_ID  ) CUSTOMERNUMBER,
  OOHA.FLOW_STATUS_CODE "OrderStatus",  OOHA.PAYMENT_TYPE_CODE,  OOHA.CREDIT_CARD_NUMBER,
  OOLA.ORDERED_ITEM,   OOLA.ORDERED_QUANTITY,  OOLA.SHIPPED_QUANTITY,  OOLA.INVOICRD.D_QUANTITY,
  OOLA.TAX_VALUE,  (OOLA.UNIT_SELLING_PRICRD. * OOLA.ORDERED_QUANTITY) ITEMTOTALAMOUNT,
  OOLA.FLOW_STATUS_CODE "LineStatus",  IFTE.TRXN_EXTENSION_ID ,
  ICRD.CCNUMBER,   ICRD.CHNAME,  ICRD.CARD_OWNER_ID,  ICRD.MASKED_CC_NUMBER,  ICRD.CARD_ISSUER_CODE,  ICRD.EXPIRYDATE,  ICRD.INACTIVE_DATE
FROM OE_ORDER_HEADERS_ALL OOHA,  OE_ORDER_LINES_ALL OOLA,  OE_PAYMENTS OP,  IBY_FNDCPT_TX_EXTENSIONS IFTE,  IBY_PMT_INSTR_USES_ALL IPUA,  IBY_CREDITCARD  ICRD
WHERE OOHA.HEADER_ID=OOLA.HEADER_ID
--AND OOHA.PAYMENT_TYPE_CODE LIKE 'CREDIT_CARD'
AND OP.LINE_ID(+)        =OOLA.LINE_ID
AND OOHA.HEADER_ID = 5153513
AND OP.TRXN_EXTENSION_ID=IFTE.TRXN_EXTENSION_ID(+)
  --AND IFTE.TRXN_EXTENSION_ID=ITSA.INITIATOR_EXTENSION_ID
AND IFTE.INSTR_ASSIGNMENT_ID=IPUA.INSTRUMENT_PAYMENT_USE_ID(+)
AND IPUA.INSTRUMENT_ID      =ICRD.INSTRID(+);
 

 
 
 
 
Keywords:
Oracle EBS, R12, R12.2.8, OM, OE, ONT, IBY, Oracle Payments, iPayments, Order Management, Shipping, Ship Confirm, Query, SQL, SQLPLUS

Oracle EBS SQLs – Delivery, Back Order Statuses

 

Business Need:

Oracle Applications (EBS) Support, Analysis, Troubleshooting, Reports, Tracking, etc. I am using R12.2.8. But the SQLs will be applicable most of the recent versions.

Inventory, Products, Items, Onhand Quantity, etc. – Analysis and Support.

 

Queries:

Inventory/Warehouse Items/Products, Counts

-- qry sql - mtl onhand inventory items, counts
SELECT   ITEM.INVENTORY_ITEM_ID AS ITEM_ID, ITEM.SEGMENT1 ITEM_CODE, ITEM.DESCRIPTION ITEM_DESCRIPTION,
    NVL(SUM(ON_HAND.ON_HAND),0) ON_HAND
FROM MTL_SYSTEM_ITEMS_B ITEM, MTL_ONHAND_TOTAL_MWB_V ON_HAND
WHERE ITEM.ORGANIZATION_ID = 340  --ORG ID
AND ITEM.WEB_STATUS = 'PUBLISHED'
AND ITEM.ORDERABLE_ON_WEB_FLAG = 'Y'
AND ITEM.SEGMENT1 LIKE '3%'
AND ITEM.ORGANIZATION_ID = ON_HAND.ORGANIZATION_ID(+)  --ORG ID
AND ITEM.INVENTORY_ITEM_ID = ON_HAND.INVENTORY_ITEM_ID(+)
--AND item.inventory_item_id = 4231
--AND on_hand.subinventory_code = 'SEA'  --sub inventory
--AND item.segment1 = 'XXXXX' ---item name
GROUP BY ITEM.INVENTORY_ITEM_ID, ITEM.SEGMENT1, ITEM.DESCRIPTION
ORDER BY 4 DESC;
 

 

Product Counts, Organization/Warehouse Code

-- onhand by item status, inventory org code
SELECT   item.organization_id,
    (select ood.organization_code from org_organization_definitions ood where ood.organization_id = item.organization_id) as org_code,
    INVENTORY_ITEM_STATUS_CODE, count(distinct  item.inventory_item_id) as item_count, SUM (on_hand.on_hand) on_hand
    FROM mtl_system_items_b item, mtl_onhand_total_mwb_v on_hand
   WHERE item.organization_id in (23, 290, 25, 270, 28, 24, 34, 102, 103, 106, 107, 111, 112, 115, 136, 196, 36)  --ORG ID
   --AND exists (select 1 from mtl_system_items_b item4 where item4.organization_id = 332 and item4.inventory_item_id = item.inventory_item_id )
  AND item.organization_id = on_hand.organization_id
  AND item.inventory_item_id = on_hand.inventory_item_id
  --AND item.inventory_item_id = 4231
     --AND on_hand.subinventory_code = 'SEA'  --sub inventory
     --AND item.segment1 = 'XXXXX' ---item name
GROUP BY item.organization_id, INVENTORY_ITEM_STATUS_CODE;

 

 

 

Inventory Item Counts by Item Status

-- item counts by status
SELECT INVENTORY_ITEM_STATUS_CODE, (select ood.organization_code from org_organization_definitions ood where ood.organization_id = msi.organization_id) as org_code
    , ORGANIZATION_ID, COUNT(*) as total_items, sum(decode(INVENTORY_ITEM_STATUS_CODE, 'Active', 1, 0)) as active_items
FROM APPS.MTL_SYSTEM_ITEMS_VL msi
where organization_id in (28, 332)
--and inventory_item_id in (1613, 1645, 3443248, 47961, 3443228, 1389, 5151, 3443235, 7041, 3098, 1244, 9334, 7589, 3443225, 7701, 3443227)
GROUP BY ORGANIZATION_ID, INVENTORY_ITEM_STATUS_CODE
ORDER BY ORGANIZATION_ID ASC;
 
 

  

Keywords:

Oracle EBS, R12, R12.2.8, OM, OE, ONT, WSH, INV, Inventory, Warehouse, Onhand, Demand, Availability, Order Management, Shipping, Ship Confirm, Query, SQL, SQLPLUS

Oracle EBS SQLs – Delivery, Back Order Statuses

 

Business Need:

Oracle Applications (EBS) Support, Analysis, Troubleshooting, Reports, Tracking, etc. I am using R12.2.8. But the SQLs will be applicable most of the recent versions.

Order Entry, Shipping, Delivery, pricing, etc. – Analysis and Support.

 

Queries:

Order Number, Line & Delivery Status

-- order lines with delivery status
SELECT OL.HEADER_ID, OL.LINE_ID, OL.LINE_NUMBER, OL.FLOW_STATUS_CODE, OL.ORDERED_QUANTITY, WDL.PICKED_QUANTITY, OL.UNIT_SELLING_PRICE, OL.TAX_VALUE, WDL.RELEASED_STATUS_NAME
FROM OE_ORDER_LINES_ALL OL, APPS.WSH_DELIVERY_DETAILS WDD, WSH_DELIVERABLES_V WDL
WHERE 1=1--OL.FLOW_STATUS_CODE NOT IN ('ENTERED', 'CLOSED')
AND WDL.DELIVERY_DETAIL_ID(+) = WDD.DELIVERY_DETAIL_ID
AND OL.HEADER_ID = WDD.SOURCE_HEADER_ID(+)
AND OL.LINE_ID = WDD.SOURCE_LINE_ID(+)
AND WDL.SOURCE_HEADER_ID(+) = OL.HEADER_ID
--AND WDL.RELEASED_STATUS <> 'B'
AND OL.HEADER_ID = 5154002;                    -- Replace Order Header ID
 

Orders & Deliverable Amounts

-- get auth amount - total of all deliverable amounts of the order
SELECT SUM(LINE_AMOUNTS)
FROM (SELECT GREATEST(0,NVL(WDL.PICKED_QUANTITY, OL.ORDERED_QUANTITY)*OL.UNIT_SELLING_PRICE) LINE_AMOUNTS
    FROM OE_ORDER_LINES_ALL OL, APPS.WSH_DELIVERY_DETAILS WDD, WSH_DELIVERABLES_V WDL
    WHERE OL.FLOW_STATUS_CODE NOT IN ('ENTERED', 'CLOSED')
    AND WDL.DELIVERY_DETAIL_ID(+) = WDD.DELIVERY_DETAIL_ID
    AND OL.HEADER_ID = WDD.SOURCE_HEADER_ID(+)
    AND OL.LINE_ID = WDD.SOURCE_LINE_ID(+)
    AND WDL.RELEASED_STATUS <> 'B'
    AND OL.HEADER_ID = 5154002                 -- Replace Order Header ID
    UNION
    SELECT NVL(WDL.PICKED_QUANTITY, OL.ORDERED_QUANTITY)*OL.TAX_VALUE/OL.ORDERED_QUANTITY TAX_AMOUNTS
    FROM OE_ORDER_LINES_ALL OL, APPS.WSH_DELIVERY_DETAILS WDD, WSH_DELIVERABLES_V WDL
    WHERE OL.FLOW_STATUS_CODE NOT IN ('ENTERED', 'CLOSED')
    AND WDL.DELIVERY_DETAIL_ID(+) = WDD.DELIVERY_DETAIL_ID
    AND OL.HEADER_ID = WDD.SOURCE_HEADER_ID(+)
    AND OL.LINE_ID = WDD.SOURCE_LINE_ID(+)
    AND WDL.RELEASED_STATUS <> 'B'
    AND OL.HEADER_ID = 5154002);               -- Replace Order Header ID
 

  

Keywords:

Oracle EBS, R12, R12.2.8, OM, OE, ONT, WSH, Order Management, Shipping, Ship Confirm, Query, SQL, SQLPLUS

Date Conversion Examples: Oracle Date <=> XML ISO8601 Format

Introduction:

Some examples of Date Conversion from Oracle PL/SQL to and from XML Standard Date (ISO8601 format).

 

Examples:

Convert XML Date to Oracle Date Format:

-- CONVERT XML DATE TO ORACLE PL/SQL
SELECT TO_DATE('2017-08-24T12:11:06Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
FROM DUAL;

 

Convert XML Date to Oracle System Timestamp Format:

SELECT TO_TIMESTAMP('1985-02-07T00:00:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
FROM DUAL;
 
SELECT TO_TIMESTAMP('2017-08-24T12:11:06Z', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
FROM DUAL;
 
 

Convert Oracle Date to XML ISO8601 Format:

-- CONVERT ORACLE DATE TO XML ISO8601 FORMAT
SELECT
    TO_CHAR(
        SYSTIMESTAMP AT TIME ZONE 'UTC',
        'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"'
    )
FROM DUAL;
 

  

Convert Oracle Date to XML ISO8601 Format with Time Zone Details:

SELECT SYSDATE,
TO_CHAR((FROM_TZ(TO_TIMESTAMP(TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS PM'), 'YYYY-MM-DD HH:MI:SS PM') ,'AMERICA/NEW_YORK') AT TIME ZONE 'UTC'),'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"')
"ISO8601"
FROM DUAL

 

  

Keywords:

Date Conversion, Format, Timestamp, Systimestamp, XML Date, ISO8601, Date Function, PL/SQL, Query, SQL, SQLPLUS

 

WIP Tables & Random SQLs – Work In Progress, Discrete Job Tables

 

Business Case:

Some random SQLs related to WIP (Work In Progress/Process) module functionalities. Useful for analyzing and troubleshooting issues related to WIP Job failures, stuck jobs, on-screen errors, etc.

 

Tables:

Major Tables:

WIP_DISCRETE_JOBS
WIP_LINES
WIP_OPERATIONS
WIP_ENTITIES
WIP_TRANSACTIONS

 

Other Useful Tables:

WIP_MOVE_TRANSACTIONS
WIP_DISCRETE_JOBS
WIP_REPETITIVE_SCHEDULES
WIP_REQUIREMENT_OPERATIONS WRO - COMPONENT TABLE

 

Interface Tables:

WIP_MOVE_TXN_INTERFACE
WIP_COST_TXN_INTERFACE
WIP_JOB_SHEDULE_INTERFACE
WIP_JOB_DTLS_INTERFACE

 

There are many more tables in Oracle EBS WIP Module. Only the major ones are listed here. For details, please check Oracle eTRM.

 

SQLs:

WIP Job – Header Level Details:

SELECT WE.WIP_ENTITY_ID,      -- SOURCE HEADER REFERENCE ID IN INSTALL BASE
      WE.WIP_ENTITY_NAME,        -- SOURCE HEADER REFERENCE NUMBER IN INSTALL BASE
      WE.CREATION_DATE AS WE_CREATION_DATE,
      DECODE(DSRJ.JOB_TYPE,1,'STANDARD',2,'NON_STANDARD',DSRJ.JOB_TYPE) JOB_TYPE,
      MSIB.SEGMENT1 ASSEMBLY_ITEM_NAME,
      MSIB.DESCRIPTION,
      DSRJ.CLASS_CODE,
      LU1.MEANING STATUS,
      DSRJ.SCHEDULED_START_DATE SCHEDULE_START_DATE,
      DSRJ.SCHEDULED_COMPLETION_DATE SCHEDULE_END_DATE ,
      DSRJ.START_QUANTITY,
      DSRJ.NET_QUANTITY,
      LU2.MEANING WIP_SUPPLY_TYPE,
      DSRJ.COMPLETION_SUBINVENTORY,
      BD.DEPARTMENT_CODE
FROM WIP_ENTITIES WE,
      WIP_DISCRETE_JOBS DSRJ,
      MTL_SYSTEM_ITEMS_B MSIB ,
      MFG_LOOKUPS LU1,
      MFG_LOOKUPS LU2,
      WIP_OPERATIONS WO,
      BOM_DEPARTMENTS BD,
      BOM_RESOURCES BR,
      WIP_OPERATION_RESOURCES WOR
WHERE WE.WIP_ENTITY_NAME='281019'         -- WIP JOB Number
AND WE.WIP_ENTITY_ID = DSRJ.WIP_ENTITY_ID
AND LU1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND LU1.LOOKUP_CODE = DSRJ.STATUS_TYPE
AND LU2.LOOKUP_TYPE = 'WIP_SUPPLY'
AND LU2.LOOKUP_CODE = DSRJ.WIP_SUPPLY_TYPE
AND WE.PRIMARY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND WE.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
AND WE.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WO.ORGANIZATION_ID
AND BR.RESOURCE_ID = WOR.RESOURCE_ID
AND WOR.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
AND WOR.ORGANIZATION_ID=WE.ORGANIZATION_ID;

  

WIP Job – Header Level Details, Simpler Query: Same as above, but only required/basic columns selected.

SELECT  WE.PRIMARY_ITEM_ID AS FINAL_PRODUCT, WE.WIP_ENTITY_NAME,
 DECODE(DSRJ.JOB_TYPE,1,'STANDARD',2,'NON_STANDARD',DSRJ.JOB_TYPE) JOB_TYPE,
 DSRJ.CLASS_CODE,
 DSRJ.SCHEDULED_START_DATE SCHEDULE_START_DATE,
 DSRJ.SCHEDULED_COMPLETION_DATE SCHEDULE_END_DATE ,
 DSRJ.START_QUANTITY,
 DSRJ.NET_QUANTITY,
    DSRJ.COMPLETION_SUBINVENTORY
FROM WIP_ENTITIES WE,
WIP_DISCRETE_JOBS DSRJ, 
        WIP_OPERATION_RESOURCES WOR
WHERE WE.WIP_ENTITY_NAME='281019'         -- WIP JOB Number
AND WE.WIP_ENTITY_ID = DSRJ.WIP_ENTITY_ID
 AND WOR.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
 AND WOR.ORGANIZATION_ID=WE.ORGANIZATION_ID;

  

WIP Job Details – Components and Final Item Details:

SELECT  WE.PRIMARY_ITEM_ID AS FINAL_PRODUCT, WRO.INVENTORY_ITEM_ID AS COMPONENT_ITEM_ID, WE.WIP_ENTITY_NAME,
 DECODE(DSRJ.JOB_TYPE,1,'STANDARD',2,'NON_STANDARD',DSRJ.JOB_TYPE) JOB_TYPE,
 DSRJ.CLASS_CODE,
 DSRJ.SCHEDULED_START_DATE SCHEDULE_START_DATE,
 DSRJ.SCHEDULED_COMPLETION_DATE SCHEDULE_END_DATE ,
 DSRJ.START_QUANTITY,
 DSRJ.NET_QUANTITY,
    DSRJ.COMPLETION_SUBINVENTORY
FROM WIP_ENTITIES WE,
WIP_DISCRETE_JOBS DSRJ, 
       WIP_REQUIREMENT_OPERATIONS WRO,
        WIP_OPERATION_RESOURCES WOR
WHERE WE.WIP_ENTITY_NAME='281019'         -- WIP JOB Number
AND WE.WIP_ENTITY_ID = DSRJ.WIP_ENTITY_ID
AND WE.WIP_ENTITY_ID=WRO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WRO.ORGANIZATION_ID
 AND WOR.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
 AND WOR.ORGANIZATION_ID=WE.ORGANIZATION_ID;
 

  

WIP Job Details – Many QIP Job Related Details:

-- Query with many useful WIP Job Related Details
SELECT WE.WIP_ENTITY_NAME,
 DECODE(DSRJ.JOB_TYPE,1,'STANDARD',2,'NON_STANDARD',DSRJ.JOB_TYPE)JOB_TYPE,
 MSIB.SEGMENT1 ASSEMBLY_ITEM_NAME,
 MSIB.DESCRIPTION,
 DSRJ.CLASS_CODE,
 LU1.MEANING STATUS,
 MMT.TRANSACTION_UOM UOM,
 DSRJ.SCHEDULED_START_DATE SCHEDULE_START_DATE,
 DSRJ.SCHEDULED_COMPLETION_DATE SCHEDULE_END_DATE ,
 DSRJ.START_QUANTITY,
 DSRJ.NET_QUANTITY,
 LU2.MEANING WIP_SUPPLY_TYPE,
    DSRJ.COMPLETION_SUBINVENTORY,
    MSIV.CONCATENATED_SEGMENTS,
    BD.DEPARTMENT_CODE
FROM WIP_ENTITIES WE,
WIP_DISCRETE_JOBS DSRJ,        
MTL_SYSTEM_ITEMS_B MSIB ,
MFG_LOOKUPS LU1,
MFG_LOOKUPS LU2,
MTL_MATERIAL_TRANSACTIONS MMT,
 MTL_SYSTEM_ITEMS_VL MSIV,
       WIP_REQUIREMENT_OPERATIONS WRO,
        WIP_OPERATIONS WO,
      BOM_DEPARTMENTS BD,
        BOM_RESOURCES BR,
        WIP_OPERATION_RESOURCES WOR
WHERE WE.WIP_ENTITY_NAME='3401881'
AND WE.WIP_ENTITY_ID = DSRJ.WIP_ENTITY_ID
AND WE.PRIMARY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
AND LU1.LOOKUP_TYPE = 'WIP_JOB_STATUS'
AND LU1.LOOKUP_CODE = DSRJ.STATUS_TYPE
AND LU2.LOOKUP_TYPE = 'WIP_SUPPLY'
AND LU2.LOOKUP_CODE = DSRJ.WIP_SUPPLY_TYPE
AND WE.PRIMARY_ITEM_ID=MMT.INVENTORY_ITEM_ID
AND WE.WIP_ENTITY_ID=WRO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WRO.ORGANIZATION_ID
AND  MSIV.INVENTORY_ITEM_ID = WRO.INVENTORY_ITEM_ID
AND MSIV.ORGANIZATION_ID = WRO.ORGANIZATION_ID
AND BD.DEPARTMENT_ID = WO.DEPARTMENT_ID
AND WE.WIP_ENTITY_ID=WO.WIP_ENTITY_ID
AND WE.ORGANIZATION_ID=WO.ORGANIZATION_ID
 AND BR.RESOURCE_ID = WOR.RESOURCE_ID
 AND WOR.WIP_ENTITY_ID=WE.WIP_ENTITY_ID
 AND WOR.ORGANIZATION_ID=WE.ORGANIZATION_ID;
  

Keywords:

WIP, Work in Progress, Work In Process, Discrete Manufacturing, EBS, R12, R12.2.6, PL/SQL, Query, SQL, SQLPLUS

 

Oracle EBS – Workflow Background SQLs

 

Business Need:

Some random useful SQLs/Queries related to Oracle Applications (EBS) concurrent programs, schedules, workflow background programs, requisition import process, etc.

 

Queries:

All Concurrent Jobs ran more than 10 times during previous day

-- all concurrent job executions during a single day, more than 10
SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-2.1 AND SYSDATE-1.1
--AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
HAVING COUNT(*) > 9
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;
 

Get name of above Concurrent Program ID

SELECT *
FROM APPS.FND_CONCURRENT_PROGRAMS_VL CP
--WHERE USER_CONCURRENT_PROGRAM_NAME LIKE 'XX%%%';          -- use name from above SQL
WHERE CONCURRENT_PROGRAM_NAME LIKE 'XX%%%';
 

 

Get more details on frequent program – Eg: Workflow Background

-- qry sql - notes - check concurrent program running prod wfbg
-- WFBG & ReqImport Only, last 2.4 hrs ~around 72 should be good
SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-.104 AND SYSDATE+.004
AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;
 

Requests by a specific user

-- Programs ran by <USER> during past 15 minutes
SELECT CR.REQUEST_ID, CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, TO_CHAR(CR.REQUEST_DATE, 'Mon/dd hh24miss') REQUEST_DATE, CR.REQUESTED_BY
  , CR.PHASE_CODE, DECODE(CR.PHASE_CODE, 'C', 'Completed', 'P', 'Pending', 'zOthers') AS PHASE_NAME
  , CR.STATUS_CODE, DECODE(CR.STATUS_CODE, 'C', 'Normal', 'I', 'Scheduled', 'zOthers') AS STATUS_NAME
  , CR.LOGFILE_NAME, CR.OUTFILE_NAME, CR.ARGUMENT_TEXT
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-.004 AND SYSDATE+.004
-- AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = 1151              -- use user id of the given user
ORDER BY CR.REQUEST_DATE DESC;
 

All Requests during last 24 hours

-- Conc Requests ran during past 24 hours
SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-1.004 AND SYSDATE+.004
-- AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;
 

Similar to above

SELECT CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME, COUNT(*) AS REQUESTS
FROM APPS.FND_CONCURRENT_REQUESTS CR, APPS.FND_CONCURRENT_PROGRAMS_VL CP, APPS.FND_USER US
WHERE CR.REQUEST_DATE BETWEEN SYSDATE-1.004 AND SYSDATE+.004
 AND CR.CONCURRENT_PROGRAM_ID IN (36888, 32353)
--AND CR.REQUESTED_BY = 1151
AND CR.CONCURRENT_PROGRAM_ID = CP.CONCURRENT_PROGRAM_ID
AND CR.REQUESTED_BY = US.USER_ID
GROUP BY CR.CONCURRENT_PROGRAM_ID, CP.USER_CONCURRENT_PROGRAM_NAME, CR.REQUESTED_BY, US.USER_NAME
ORDER BY REQUESTS DESC, CR.CONCURRENT_PROGRAM_ID ASC;

  

Keywords:

Oracle EBS, R12, R12.2.8, Workflow, Background, Concurrent Programs, Concurrent Jobs, Executions, WFBG, FND, Query, SQL, SQLPLUS