DIY steps - oaf personalization page error resolve

 

This page gives a few easy DIY (Do It Yourself) steps to resolve Oracle Application Framework (OAF) personalization page

Problem:

- OAF personalization done

- Page gives error and not able to recover

 

 

Solution:

 

1. Identify personalization

 

SELECT PATH.PATH_DOCID PERZ_DOC_ID,

jdr_mds_internal.getdocumentname(PATH.PATH_DOCID) PERZ_DOC_PATH, PATH.*

FROM JDR_PATHS PATH

WHERE PATH.PATH_DOCID IN

(SELECT DISTINCT COMP_DOCID FROM JDR_COMPONENTS

WHERE COMP_SEQ = 0 AND COMP_ELEMENT = 'customization'

AND COMP_ID IS NULL)

and path.creation_date > sysdate-.04

ORDER BY PERZ_DOC_PATH;

 

97016 /oracle/apps/iby/fundcapture/transaction/request/webui/customizations/site/0/AuthorizationsSearchPG     AuthorizationsSearchPG

 

 

2. Delete customization

 

 

 begin 

   jdr_utils.deleteDocument('/oracle/apps/iby/fundcapture/transaction/request/webui/customizations/site/0/AuthorizationsSearchPG'); 

 end; 

/

 

commit;

 

 

3. Bounce OA CORE Server

 

 

{ echo Password123 ; }| admanagedsrvctl.sh abort oacore_server1 @-nopromptmsg

{ echo Password123; }| admanagedsrvctl.sh start oacore_server1 @-nopromptmsg

 

The changes normally do not require bouncing or clearing cache. But if the steps fails to resolve the error, try after bouncing middle tier.

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications, OAF, OA Framework, Personalization, Customization, HTML UI Page, page.xml

PL/SQL: XML Parsing CyberSource Response Example

 

PL/SQL: XML Parsing CyberSource Response Example

 

Business Need:

Just an example of XML Parsing within PL/SQL.

This can be used to parse any XML within PL/SQL. Make sure APEX JSON is installed in database.

Note: We are using Oracle 12.1.0.0 database version. Some objects and parser are not available in older versions of Oracle database.

 

Program / PL/SQL Script:

set serveroutput on;
     -- Insert data into staging table
    DECLARE
             xAuthRespXML XMLType;
             V_AUTH_RESP_XML VARCHAR2(5000);
             sAuthResponseNameSpace  VARCHAR2(5000);
             v_item_count NUMBER;
       BEGIN
 
-- Substitute your XML Text here
V_AUTH_RESP_XML := '<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
       <soap:Header>
             <wsse:Security xmlns:wsse="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
                    <wsu:Timestamp xmlns:wsu="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd" wsu:Id="Timestamp-1489510174">
                           <wsu:Created>2017-07-23T04:27:08.680Z</wsu:Created>
                    </wsu:Timestamp>
             </wsse:Security>
       </soap:Header>
       <soap:Body>
             <c:replyMessage xmlns:c="urn:schemas-cybersource-com:transaction-data-1.165">
                    <c:merchantReferenceCode>EbsONT122052844.27</c:merchantReferenceCode>
                    <c:requestID>5954784279986442603008</c:requestID>
                    <c:decision>ACCEPT</c:decision>
                    <c:reasonCode>100</c:reasonCode>
             <c:requestToken>Axj/7wSTQs87kNkwJAoAABsZLnsZc6HPnObVOMojhEbUIoBURwiNqEVpBK6PcDMhk0ky9GLDX7uBOTQs87kNkwJAoAAAXAhY</c:requestToken>
                    <c:purchaseTotals>
                           <c:currency>USD</c:currency>
                    </c:purchaseTotals>
                    <c:ccAuthReply>
                           <c:reasonCode>100</c:reasonCode>
                           <c:amount>1907.11</c:amount>
                           <c:authorizationCode>831000</c:authorizationCode>
                           <c:avsCode>Y</c:avsCode>
                           <c:avsCodeRaw>Y</c:avsCodeRaw>
                           <c:authorizedDateTime>2017-07-23T04:27:08Z</c:authorizedDateTime>
                           <c:processorResponse>00</c:processorResponse>
                           <c:reconciliationID>KO1KNCON9ZSF</c:reconciliationID>
                    <c:authRecord>0110322000000E10000200000000000019071107230427080517684B4F314B4E434F4E395A53463833313030303030000159004400103232415050524F56414C0022313457303136313530373033383032303934473036340006564943524120</c:authRecord>
                    <c:paymentNetworkTransactionID>016150703802094</c:paymentNetworkTransactionID>
                    </c:ccAuthReply>
                    <c:decisionEarlyReply>
                           <c:reasonCode>100</c:reasonCode>
                           <c:rcode>1</c:rcode>
                           <c:activeProfileReply/>
                    </c:decisionEarlyReply>
                    <c:card>
                           <c:cardType>001</c:cardType>
                    </c:card>
                    <c:acquirerMerchantNumber>000225130042990</c:acquirerMerchantNumber>
                    <c:pos>
                           <c:terminalID>06011497</c:terminalID>
                    </c:pos>
             </c:replyMessage>
       </soap:Body>
</soap:Envelope>';
 
               dbms_output.put_line('Before FOR loop through Customer XML');
 
               xAuthRespXML :=  XMLType(V_AUTH_RESP_XML);
               sAuthResponseNameSpace := ' xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:c="urn:schemas-cybersource-com:transaction-data-1.141"';
 
               v_item_count := 0;
               FOR r IN (
                    SELECT
                           ExtractValue(Value(p),'/c:replyMessage/c:merchantReferenceCode/text()', sAuthResponseNameSpace) as merchantReferenceCode
                    FROM   TABLE(XMLSequence(Extract(xAuthRespXML,'/soap:Envelope/soap:Body/c:replyMessage', sAuthResponseNameSpace))) p
                    ) LOOP
 
                    v_item_count := v_item_count + 1;
                    dbms_output.put_line('CustomerXML Loop Number: ' || v_item_count || '; merchantReferenceCode:' || r.merchantReferenceCode);
 
 
 
               END LOOP;
               dbms_output.put_line('After FOR loop through Customer XML');
 
 
 
 
 
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Exception while inserting Customer Data to Staging table; SQLCODE:' || SQLCODE || '; SQLERRM:' || SQLERRM);
    END;
/
 
 

 

Keywords:

Oracle EBS, R12, R12.2.8, Oracle Applications

XML, PL/SQL, Parser, APEX JSON, Patch

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