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

EBS JarSigner: Example Usages

 

What is JarSigner ?

A Custom Java JAR file needs to be signed using digital signature before it to be used for Oracle Application Server. This is to ensure file integrity and improve security. JarSigner command is used to create the signed JAR file.

This page give some examples of using JarSigner command.

JarSigner Examples:

Please substitute Password123 with your own Store Pass and Key Pass. Please substitute DEV_devappsap01 with your Middle Tier / Application Server name.

 

1) Generate Keypair for code signing certificate

 

2) Import root certificate to cacerts

 

keytool -import -alias YOUR_ALIAS -file root.crt -trustcacerts -v -keystore cacerts

 

3) Import intermediate certificate to adkeystore.dat

 

keytool -import -file intermediate.crt -trustcacerts -alias YOUR_ALIAS -keystore adkeystore.dat

 

4) Import Code Signing Cert (code signed cert would have been copied to adkeystore.crt)

 

adjkey -import -file adkeystore.crt -trustcacerts

 

5) On your User Desktop import the certificates in this order to Java

 

 
 
command -
$adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar $CONTEXT_NAME 1 CUST jarsigner -storePass Password123 -keyPass Password123
 
Error
 
ERROR: JarSigner subcommand exited with status 1
 
JarSigner standard output:
jarsigner: Certificate chain not found for: DEV_devappsap01.  DEV_devappsap01 must reference a valid KeyStore key entry containing a private key and corresponding public key certificate chain.
 
Flag
 
adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar $CONTEXT_NAME 1 CUST jarsigner -storePass <KeyStore Password> -keyPass <Key Password>
 
 
adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar $CONTEXT_NAME 1 CUST jarsigner -storePass "Password123" -keyPass "Password123"
 
 
adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar "DEV_devappsap01" 1 CUST jarsigner -storePass "Password123" -keyPass "Password123"
adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar $CONTEXT_NAME 1 CUST jarsigner -storePass "Password123" -keyPass "Password123"
 
 
 
adjava oracle.apps.ad.jri.adjmx -areas ./customprod.zip -outputFile ./customprod.jar -jar "DEV_devappsap01" 1 CUST jarsigner -storePass "<pwd>" -keyPass "<pwd>"
 
 
 
LD_LIBRARY_PATH=/appsR122/DEV/fs1/EBSapps/10.1.2/jdk/jre/lib/sparc:/appsR122/DEV/fs1/EBSapps/10.1.2/jdk/jre/lib/sparc/native_threads:/appsR122/DEV/fs1/EBSapps/appl/cz/12.0.0/bin:/appsR122/DEV/fs1/EBSapps/10.1.2/lib32:/appsR122/DEV/fs1/EBSapps/10.1.2/lib:/usr/dt/lib:/usr/openwin/lib:/appsR122/DEV/fs1/EBSapps/10.1.2/jdk/jre/lib/sparc:/appsR122/DEV/fs1/EBSapps/10.1.2/jdk/jre/lib/sparc/native_threads:/appsR122/DEV/fs1/EBSapps/appl/sht/12.0.0/lib:/appsR122/DEV/fs1/EBSapps/10.1.2/jdk/jre/lib/sparc/client
 
adjava oracle.apps.ad.jri.adjmx -areas $JAVA_TOP/customprod.zip -outputFile $JAVA_TOP/customprod.jar -jar "DEV_devappsap01" 1 CUST jarsigner -storePass "Password123" -keyPass "Password123"
 

 

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications, JarSigner, ASADMIN, ISG, Integrated SOA Gateway, Web Services, REST, RestFul, $JAVA_TOP

PL/SQL Script/Program: End Date Multiple Responsibilities

 

Business Need:

Reduce the number of EBS Responsibilities (hats) for a given user.

With Minimum Tweaking, you may achieve additional features:

• Stop a single responsibility from entire business use
• Stop people with a language from accessing one more responsibilities using EBS Standard Multi Language feature
• User has 100 responsibilities in Production. Cloning this to a development environment requires only one Operating Unit and its responsibilities

Standard End Dating of FND User Responsibilities used to work in older versions (Eg: at least 12.1.3). Now responsibility is moved to Role/Permission tables and need API calls to control this.

 

Benefits:

Ease of use and less memory usage

Program / PL/SQL Script:

SET SERVEROUTPUT ON;
 
DECLARE
       CURSOR c1
       IS
    SELECT fu.user_name,
        fa.application_short_name,
        frt.responsibility_name,
        fr.responsibility_key,
        fsg.security_group_key
    FROM fnd_user_resp_groups_all ful,
        fnd_user fu,
        fnd_responsibility_tl frt,
        fnd_responsibility fr,
        fnd_security_groups fsg,
        fnd_application fa
    WHERE fu.user_id = ful.user_id
    AND frt.responsibility_id = ful.responsibility_id
    AND fr.responsibility_id = frt.responsibility_id
    AND fsg.security_group_id = ful.security_group_id
    AND fa.application_id = ful.responsibility_application_id
    AND frt.language = 'US'
    AND fu.user_name IN ('<user-name>')        -- change user name appropriately
                           -- remove user name, if this is applicable to all users
    AND frt.responsibility_name like 'XX%';    -- change resp name appropriately
 
BEGIN
 
       FOR i IN c1
       LOOP
             BEGIN
                    fnd_user_pkg.delresp (username => i.user_name,
                           resp_app => i.application_short_name,
                           resp_key => i.responsibility_key,
                           security_group => i.security_group_key);
                    COMMIT;
                    DBMS_OUTPUT.put_line ('User: '||i.user_name||'Resp: '||i.responsibility_name || ' has been End Dated Successfully !!!');
             EXCEPTION
                    WHEN OTHERS THEN
                           DBMS_OUTPUT.put_line (
                                 'Inner Exception: '
                                 || ' - '
                                 || i.responsibility_key
                                 || ' - '
                                 || SQLERRM);
             END;
       END LOOP;
 
EXCEPTION
WHEN OTHERS THEN
       DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
 
END;
/
 
COMMIT;
 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications

FND, AOL, Request Group, Responsibility, Security, Access Control, Hat

ISG Error: java.lang.SecurityException: User: ASADMIN~~, failed to be authenticated

 Symptom/Problem/Error:

Responsibility: Integrated SOA Gateway (ISG) Administrator

Path: Search for a Service, Publish or Unpublish

The request fails with error message “java.lang.SecurityException: User: ASADMIN~~DEV, failed to be authenticated.”

We use the EBS Release 12.2.8 and REST web services by ISG. However, this error can occur in multiple R12.2 versions. Also both SOAP and REST calls can have the same issue.

This happens when you access a new instance after cloning.

 

Cause:

Two possible causes to this issue:

1.    Error with EBS Standard Functionality: Make sure the Patch 27949145:R12.OWF.C is applied in the instance

2.    Standard ISG Configuration files are created or updated properly

 

Solution:

Step 1: Apply Patch 27949145:R12.OWF.C.

Step 2: If the issue is not resolved by the Patch, make sure to review and correct ISG Configuration files. Here are all the configuration files:

• $INST_TOP/soa/isgagent.properties

• $JAVA_TOP/oracle/apps/fnd/txk/util/isg_contextfile.properties

• $RUN_BASE/EBSapps/comn/clone/jlib/java/oracle/apps/fnd/txk/util/isg_contextfile.properties

• $RUN_BASE/EBSapps/comn/adopclone_<context>/jlib/java/oracle/apps/fnd/txk/util/isg_contextfile.properties

Review User Name, Password and Instance UI Links in these files. Probably they are not updated after cloned from Production.

Keywords:

Oracle EBS, SOAP, REST, SecurityException, ASADMIN, failed to be authenticated, Web Service, R12, R12.2.8, ISG, Integrated SOA Gateway, SOAP UI, WS

EBS REST WS Error: java.lang.SecurityException: User: ASADMIN~~, failed to be authenticated.

 

Scenario:

Typically the error comes when accessing/deploying/undeploying a web service in a test instance after cloning.

We use R12.2.8. Error can happen in any version of R12 Integrated SOA Gateway (ISG) Web Services.

  

Cause:

The ASADMIN can not login to create/deploy web service

Solution:

Here are the possible causes:

• Ø ASADMIN password is not reset after cloning from production instances

Check ASADMIN user can login with the previous password from instance front-end link

Make sure the ASADMIN user has required permissions and responsibilities based on business requirements

ADASMIN is associated with proper MOAC (Multi Org Access Control) defined with User or Responsibility

• Ø ASADMIN user or responsibility is inactivated, locked or end dated
• Ø ISG Setup files are not properly configured

o   isgagent.properties

o   isg_contextfile.properties

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications, ASADMIN, ISG, Integrated SOA Gateway, Web Services, REST, RestFul

Basic SQLs – Oracle EBS ISG – SOAP, REST Web Services

 

Business Need:

Miscellaneous SQLs for analysis and troubleshooting of Oracle EBS module ISG (Integrated SOA Gateway) hosted SOAP and REST based web services.

 

Queries:

All WS/APIs/Services – Return both SOAP and REST

-- all SOA web services available in the system
SELECT *
FROM APPS.FND_SOA_SERVICES
--WHERE CREATION_DATE > SYSDATE - 1.6;
WHERE WSDL_URL LIKE '%_XXOCM_%';
 

All REST / RESTful Web Services

-- all RESTful web services deployed in an instance
SELECT SERVICE_ALIAS, WSDL_URL, CLASS_ID, TO_CHAR(CREATION_DATE,'YYYY-MON-DD') AS CREATED_DATE
FROM APPS.FND_SOA_SERVICES FSS
--WHERE CREATION_DATE > SYSDATE - 1.6;
WHERE WSDL_URL LIKE '%%'
--AND WSDL_URL LIKE '%/rest/%'   -- get only RESTful Web Services
ORDER BY SERVICE_ALIAS;
 

All Services, more details

-- web services, class, internal program, type - details
--SELECT FSS.*, TO_CHAR(FSS.CREATION_DATE,'YYYY-MON-DD') AS CREATED_DATE, ICL.*
SELECT FSS.SERVICE_ALIAS, FSS.WSDL_URL, FSS.CLASS_ID, TO_CHAR(FSS.CREATION_DATE,'YYYY-MON-DD') AS CREATED_DATE,
    ICL.CLASS_NAME, ICL.IREP_NAME, ICL.CLASS_TYPE, ICL.PRODUCT_CODE, ICL.SOURCE_FILE_PATH, ICL.SOURCE_FILE_NAME, ICL.DISPLAY_NAME
FROM APPS.FND_SOA_SERVICES FSS, FND_IREP_CLASSES_VL ICL
--WHERE CREATION_DATE > SYSDATE - 1.6;
WHERE FSS.WSDL_URL LIKE '%%'
AND FSS.CLASS_ID = ICL.CLASS_ID(+)
--AND WSDL_URL LIKE '%/rest/%'   -- get only RESTful Web Services
ORDER BY FSS.SERVICE_ALIAS;
 

All SQLs from a single DB Schema

-- all sqls from a single db user
SELECT  S.MODULE, SQL_TEXT , S.EXECUTIONS   
FROM SYS.V_$SQL S, SYS.ALL_USERS U
WHERE S.PARSING_USER_ID=U.USER_ID
AND (s.MODULE='agt:isg:oracle.apps.fnd.isg.common.util.IRepAcce')
--and s.serial = '40903'
ORDER BY S.LAST_LOAD_TIME;
 
 

Other Important ISG/SOA Related tables

FND_SOA_ATTACHMENT
FND_SOA_BODY_PIECE
FND_SOA_JMS_IN
FND_SOA_JMS_OUT
FND_SOA_LOG_CATEGORY
FND_SOA_LOG_SERVICE_DETAILS
FND_SOA_REQUEST
FND_SOA_RESPONSE
FND_SOA_RESPONSE_METHOD
FND_SOA_RUNTIME_ERROR
FND_SOA_SERVICES
FND_SOA_SERVICE_DT_ERRORS
FND_SOA_SERVICE_OPERATIONS
FND_SOA_SERVICE_POLICIES
 

 

 

Keywords:

Oracle EBS, R12, R12.2.8, ISG, Integrated SOA Gateway, APPLSYS, FND, SOAP, REST, XML, JSON, SOAPUI, WS, Web Services, API, Query, SQL, SQLPLUS