POXML error in oracle r12

[Mohammed Hamed]

Hello Erman,

I am facing issues with the POXML some time is showing the delivery message as [ ]

Please help me on this workflow mailer is running

[ErmanArslansOracleBlog]

Okay. This is documented -> "Delivery Message [] - all empty / blank / no value / null"

Purchase Order from EBS via XML Gateway to OSN is Remaining in XML Gateway with Message Generation SUCCESS but Message Delivery blank (Doc ID 2061374.1)

Please check the MOS note above. There is some symptopms there (ANO and encyrption..), and there is patch for the solution.. Please review the doc and consider taking the necessary actions documented there.

Patch 14659883:R12.TXK.B - OTA (OAFM) IS USING OJDBC14DMS.JAR INSTEAD OF APPSBORG.ZIP FOR JDBC CONNECTIONS

[Mohammed Hamed]

Thanks i did apply the patch, but some it is showing invalid certs too..

[ErmanArslansOracleBlog]

This is related with the wrong SSL setup.
Please review the following MOS note:

xML Gateway Outbound PO Errors With Filenotfoundexception On Cwallett.Sso (Doc ID 730495.1)

[Mohammed Hamed]

Thanks.

I did that earlier itself and made the necessary changes but still get that error either [] or invalid certs.

And the changes we did is enabled the tls on our 12.1.3 instance and it is in our development.

[Mohammed Hamed]

Showing this in the error log.

Just tried getting the log by running the sql mentioned in your blog.
https://ermanarslan.blogspot.com/2014/08/ebs-122-notification-mailer.html

[SVC-GSM-WFMLRSVC-168700-10010 : oracle.apps.fnd.cp.gsc.Logger.start(WorkflowContext, int)]:Logger has been successfully started -> Logger{mLog=[$Header: AppsLog.java 120.4.12010000.8 2016/
08/12 17:31:28 rsantis ship $ @5263318 {oracle.apps.fnd.profiles.ExtendedProfileStore@a96fe4}],mUniqueId=SVC-GSM-WFMLRSVC-168700-10010,mLevel=1}
[Mar 31, 2021 10:20:49 AM PDT]:1617211249812:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1570283275:32729:1617211237409:19:ERROR:[SVC
-GSM-WFMLRSVC-168700 : oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(BusinessEvent)]:Error in handling component event, oracle.apps.fnd.cp.gsc.SvcComponent.start, for compone
nt 10010
[Mar 31, 2021 10:20:49 AM PDT]:1617211249813:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1570283275:32729:1617211237409:19:ERROR:[SVC
-GSM-WFMLRSVC-168700 : oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(BusinessEvent)]:java.lang.RuntimeException: Connection refused (Connection refused)
        at oracle.apps.fnd.wf.mailer.MailerUtils.isSocketConnectableNew(MailerUtils.java:298)
        at oracle.apps.fnd.wf.mailer.SMTPUtils.isValidOutbound(SMTPUtils.java:521)
        at oracle.apps.fnd.wf.mailer.Mailer.validateParameterValues(Mailer.java:1303)
        at oracle.apps.fnd.cp.gsc.SvcComponent.performValidateParameterValues(SvcComponent.java:233)
        at oracle.apps.fnd.cp.gsc.SvcComponent.start(SvcComponent.java:314)
        at oracle.apps.fnd.cp.gsc.SvcComponentContainer.handleComponentEvent(SvcComponentContainer.java:2223)
        at oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(SvcComponentContainer.java:301)
        at oracle.apps.fnd.wf.bes.DispatchThread.run(DispatchThread.java:57)

[Mar 31, 2021 10:20:49 AM PDT]:1617211249813:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1570283275:32729:1617211237409:19:UNEXPECTED
:[SVC-GSM-WFMLRSVC-168700 : oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(BusinessEvent)]:An unexpected RuntimeException or other Throwable occurred -> java.lang.RuntimeExcep
tion: Connection refused (Connection refused)
[Mar 31, 2021 10:20:49 AM PDT]:1617211249814:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1570283275:32729:1617211237409:19:EXCEPTION:
[SVC-GSM-WFMLRSVC-168700 : oracle.apps.fnd.cp.gsc.SvcComponentStateMachine.updateComponentStatus(Logger, Connection, int, String, String)]:BEGIN (Logger{mLog=[$Header: AppsLog.java 120.4.12
010000.8 2016/08/12 17:31:28 rsantis ship $ @4532387 {oracle.apps.fnd.profiles.ExtendedProfileStore@6f1ad0}],mUniqueId=SVC-GSM-WFMLRSVC-168700,mLevel=4}, oracle.jdbc.driver.T4CConnection@18
f67fc, 10010, STOPPED_ERROR, Could not handle the control event -> java.lang.RuntimeException: Connection refused (Connection refused))
[Mar 31, 2021 10:21:27 AM PDT]:1617211287494:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[ComponentMonitor,5,main]:1570283275:32729:1617210987180:2:EXCEPTION:[SVC
-GSM-WFMLRSVC-168700 : oracle.apps.fnd.cp.gsc.SvcComponentMonitor.startAutomaticComponents()]:Starting automatic component 10010
[Mar 31, 2021 10:21:37 AM PDT]:1617211297442:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1570283275:32729:1617211297442:20:EXCEPTION:
[SVC-GSM-WFMLRSVC-168700 : oracle.apps.fnd.cp.gsc.SvcComponentContainer.onBusinessEvent(BusinessEvent)]:(BusinessEvent{name=oracle.apps.fnd.cp.gsc.SvcComponent.start, key=SVC:31-MAR-2021, p
riority=50, correlationId=null, sendDate=Wed Mar 31 10:21:27 PDT 2021, receiveDate=null, From Agent:  , To Agent:  , Last Subscription=  , Error Message=null, Error Stack=null, CONTAINER_TY
PE=GSM, CONTAINER_PROCESS_ID=168700, COMPONENT_ID=10010, BES_PAYLOAD_OBJECT=false, BES_FROM_AGENT=[e-mail]})
[Mar 31, 2021 10:21:47 AM PDT]:1617211307449:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1570283275:32729:1617211297442:20:EXCEPTION:
[SVC-GSM-WFMLRSVC-168700 : oracle.apps.fnd.cp.gsc.SvcComponentContainer.handleComponentEvent(int, String, String)]:Successfully retrieved component details from the database
[Mar 31, 2021 10:21:47 AM PDT]:1617211307466:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[BES Dispatch Thread,5,main]:1570283275:32729:1617211297442:20:EXCEPTION:
[SVC-GSM-WFMLRSVC-168700-10010 : oracle.apps.fnd.cp.gsc.Logger.start(WorkflowContext, int)]:Logger has been successfully started -> Logger{mLog=[$Header: AppsLog.java 120.4.12010000.8 2016/
08/12 17:31:28 rsantis ship $ @30343338 {oracle.apps.fnd.profiles.ExtendedProfileStore@134b16b}],mUniqueId=SVC-GSM-WFMLRSVC-168700-10010,mLevel=1}

[ErmanArslansOracleBlog]

Is your SMTP configuration of Workflow mailer correct? (host,port etc..)
Because you are failing with connection refused.

[Mohammed Hamed]

The port and server is working fine.
       
Document ID 430023:11:0:104
               
Internal Transaction Type PO
               
Internal Transaction Subtype PRO
               
Party ID 17237
               
Party Type Supplier
               
Trading Partner Name GOLD STAR FOODS
               
Site Name PO BOX 4328 ONTARIO91761
               
URL Sent To https://l8f5lgen5f.execute-api.us-west-1.amazonaws.com/dev
               
Generation Date 01-Apr-2021 06:52:43
               
Generation Status Success
               
Generation Message Message created and enqueued for delivery.
               
Module Name
               
Delivery Date 01-Apr-2021 11:39:58
               
Delivery Status Error
               
Delivery Message Invalid CACert File.
               
Retry Date
               
Retry Status
               
Retry Message




some log says this.

[Apr 1, 2021 10:05:59 AM PDT]:1617296759897:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[outboundThreadGroup1,5,outboundThreadGroup]:1570283275:81015:161729262278
3:17:EXCEPTION:[fnd.wf.bes.PLSQLQueueHandler]:dequeue() :Defer the Read of CLOB reference of Event Data
[Apr 1, 2021 10:05:59 AM PDT]:1617296759898:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[outboundThreadGroup1,5,outboundThreadGroup]:1570283275:81015:161729262278
3:17:EXCEPTION:[fnd.wf.bes.PLSQLQueueHandler]:dequeue() :Dequeued the following BusinessEvent -> BusinessEvent{name=oracle.apps.wf.notification.send, key=8156228, priority=50, correlationId
=null, sendDate=Thu Apr 01 10:05:11 PDT 2021, receiveDate=Thu Apr 01 10:05:59 PDT 2021, From Agent:Agent{agent=WF_NOTIFICATION_OUT, system=DV2.SAUSD.US}, To Agent:  , Last Subscription=90FF
557CD883457B87435359EB7F9046, Error Message=null, Error Stack=null, OBJECT_NAME=oracle.apps.fnd.wf.worklist.server.AllNotificationsVO, GROUP_ID=8156228, ROLE=022235, CHANGE_TYPE=INSERT, #CU
RRENT_PHASE=100, ID_TYPE=PK, PK_NAME_1=NOTIFICATION_ID, PK_NAME_2=LANGUAGE, #MSG_DQ_ATTEMPTS=0, NOTIFICATION_ID=8156228, PK_VALUE_1=8156228, #MSG_ID=BEECC98D2DE540CCE0500F0ABE170BCF, BES_PA
YLOAD_OBJECT=false, Q_CORRELATION_ID=WFTESTS:OAFWK_MSG, PK_VALUE_2=US}
[Apr 1, 2021 10:05:59 AM PDT]:1617296759905:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[outboundThreadGroup1,5,outboundThreadGroup]:1570283275:81015:161729262278
3:17:EXCEPTION:[SVC-GSM-WFMLRSVC-168820-10010 : oracle.apps.fnd.wf.mailer.NotificationFormatter.getNtfAttrValue(int, String)]:Exception occurred while getting notification attribute ->java.
sql.SQLException: ORA-20002: 3203: Attribute '#WFM_HTMLAGENT' does not exist for notification '8156228'.
ORA-06512: at "APPS.WF_NOTIFICATION", line 2359
ORA-06512: at line 1

[GC 35605K->18747K(63360K), 0.0025790 secs]
[Apr 1, 2021 10:06:06 AM PDT]:1617296766862:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[outboundThreadGroup1,5,outboundThreadGroup]:1570283275:81015:161729262278
3:17:EXCEPTION:[fnd.wf.bes.PLSQLQueueHandler]:dequeue() :Defer the Read of CLOB reference of Event Data
[Apr 1, 2021 10:06:06 AM PDT]:1617296766863:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[outboundThreadGroup1,5,outboundThreadGroup]:1570283275:81015:161729262278
3:17:EXCEPTION:[fnd.wf.bes.PLSQLQueueHandler]:dequeue() :Dequeued the following BusinessEvent -> BusinessEvent{name=oracle.apps.wf.notification.send, key=8156229, priority=50, correlationId
=null, sendDate=Thu Apr 01 10:05:11 PDT 2021, receiveDate=Thu Apr 01 10:06:06 PDT 2021, From Agent:Agent{agent=WF_NOTIFICATION_OUT, system=DV2.SAUSD.US}, To Agent:  , Last Subscription=90FF
557CD883457B87435359EB7F9046, Error Message=null, Error Stack=null, OBJECT_NAME=oracle.apps.fnd.wf.worklist.server.AllNotificationsVO, GROUP_ID=8156229, ROLE=022235, CHANGE_TYPE=INSERT, #CU
RRENT_PHASE=100, ID_TYPE=PK, PK_NAME_1=NOTIFICATION_ID, PK_NAME_2=LANGUAGE, #MSG_DQ_ATTEMPTS=0, NOTIFICATION_ID=8156229, PK_VALUE_1=8156229, #MSG_ID=BEECC98D2DE640CCE0500F0ABE170BCF, BES_PA
YLOAD_OBJECT=false, Q_CORRELATION_ID=WFTESTS:PLSQL_MSG, PK_VALUE_2=US}
[Apr 1, 2021 10:06:06 AM PDT]:1617296766867:-1:-1:ndevapp.sausd.us:10.15.23.188:-1:-1:1:20420:SYSADMIN(0):-1:Thread[outboundThreadGroup1,5,outboundThreadGroup]:1570283275:81015:161729262278
3:17:EXCEPTION:[SVC-GSM-WFMLRSVC-168820-10010 : oracle.apps.fnd.wf.mailer.NotificationFormatter.getNtfAttrValue(int, String)]:Exception occurred while getting notification attribute ->java.
sql.SQLException: ORA-20002: 3203: Attribute '#WFM_HTMLAGENT' does not exist for notification '8156229'.
ORA-06512: at "APPS.WF_NOTIFICATION", line 2359
ORA-06512: at line 1

[ErmanArslansOracleBlog]

That  #WFM_HTMLAGENT-related error is related with the workflow setup.
If you want to override agent, set parameter #WFM_HTMLAGENT...

Note, #WFM_HTMLAGENT - Define this attribute for a message to specify the base URL that identifies the HTML web agent that handles HTML notification responses.  The default URL is derived from the Workflow Web Agent specified in the Global Preferences web page in standalone Oracle Workflow, or from the Applications Web Agent (APPS_WEB_AGENT) profile option...

So you can check these.. However; your actual problem seems to be "Invalid CACert File."
This is related with ssl setup.. Check oc4j.properties file, you may have a typo there.. Or the cwallet.sso may not exist in the location specified in  oc4j.properties.

Please check the following MOS notes about these errors:

XML Gateway Outbound PO Errors With Filenotfoundexception On Cwallett.Sso (Doc ID 730495.1)
OSN PO Failing With Delivery (Doc ID 2647989.1)
Requisition Approval Fails Under Calls to Package PO_WF_UTIL_PKG.SetItemAttrText (Doc ID 984580.1)
Forgot Password Email URL for Supplier Wrongly Pointing to Internal Server (Doc ID 782247.1)
Overriding Workflow Notification Attachment URL Using #WFM_HTMLAGENT Is Not Working (Doc ID 2732253.1)