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

EBS Custom Servlet Error: Error 404-- server has not found anything matching the Request-URI

 

Scenario:

Add a new custom servlet to EBS. Try to access the servlet from UI (Oracle Applications Front-End). The screen displays error below:

Error 404--Not Found
From RFC 2068 Hypertext Transfer Protocol -- HTTP/1.1:
10.4.5 404 Not Found
The server has not found anything matching the Request-URI. No indication is given of whether the condition is temporary or permanent.

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

 

 

Cause:

The Custom Servlet needs to be mapped to Web.XML File.

In Orion based servers (12.2.8 or higher), the Web.xml file will get overwritten by Custom Tmp file contents during Middle Tier bouncing. In this cases the custom/ oacore_web_xml_FMW.tmp file needs to be mapped.

Relevant Folders:

Web.xml à $OA_HTML/WEB-INF

          Eg: /appsR122/DEV/fs2/FMW_Home/Oracle_EBS-app1/applications/oacore/html/WEB-INF

oacore_web_xml_FMW.tmp à $RUN_BASE/EBSapps/appl/fnd/12.0.0/admin/template/custom

          Eg: /appsR122/DEV/fs2/EBSapps/appl/fnd/12.0.0/admin/template/custom

 

Note: Create custom folder, if it is not present

Another error (no permissions to resource) is caused due to missing mapping for the resource. This is done by Resource Import command. It is mentioned in another post.

Solution:

1.    Create new mapping for the Servlet in oacore_web_xml_FMW.tmp file:

<!-- XXABC Entry for Custom Payment Configurations to Cyber Source - Begin -->
 
    <servlet-mapping>
    <servlet-name>XxabcLoopBackServletCys</servlet-name>
    <url-pattern>/oramipp_cys/*</url-pattern>
    </servlet-mapping>
 
     <servlet>
     <servlet-name>XxabcLoopBackServletCys</servlet-name>
     <servlet-class>xxabc.oracle.apps.iby.bep.loop.XxabcLoopBackServletCys</servlet-class>
       <init-param>
         <param-name>debug</param-name>
         <param-value>true</param-value>
       </init-param>
    </servlet>
    
<!-- XXABC Entry for Custom Payment Configurations to Cyber Source - End -->
2.    Bounce middle tier

3.    Make sure web.xml is updated and the mapping is present in web.xml file

 

Keywords:

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

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